Thread: JDBC compressed stream

    Hi, I'm developing a java client application that uses JDBC to access a
PostGIS/PostgreSQL database. I'm working with PostgreSQL JDBC driver Type 4
(http://jdbc.postgresql.org/) and I need to compress the output streamdata
from database queries.

    The problem can't be resolved making a better WHERE clause, because I
have to received a great amount of GIS information, and this info can be
highly compressed. Moreover, this data can't be stored compressed in the
database, so, an external compressor is needed.

   Anybody can explain me if it is possible to compress a JDBC datastream
??? Is this a PostgreSQL JDBC driver issue??? or a new function must be
added to PostgreSQL???

    Last question --> Anyone knows any other way to achieve compression
with other kind of solution???

    Thanks very much... Javier

Javier wrote:
>
>    Hi, I'm developing a java client application that uses JDBC to access
> a PostGIS/PostgreSQL database. I'm working with PostgreSQL JDBC driver
> Type 4 (http://jdbc.postgresql.org/) and I need to compress the output
> streamdata from database queries.
>
>    The problem can't be resolved making a better WHERE clause, because I
> have to received a great amount of GIS information, and this info can be
> highly compressed. Moreover, this data can't be stored compressed in the
> database, so, an external compressor is needed.
>
>   Anybody can explain me if it is possible to compress a JDBC datastream
> ??? Is this a PostgreSQL JDBC driver issue??? or a new function must be
> added to PostgreSQL???
>
>    Last question --> Anyone knows any other way to achieve compression
> with other kind of solution???

you could add a C function to the Postgres server engine which provides the
compression on a field by field basis, then have a corresponding Java function
to decompress the results after you recieve them.

You would use this function on a select statement something like...

    SELECT field1, fieldcompress(field2) AS field2,
        fieldcompress(field3) AS field3 FROM tablename
        WHERE ...;

These sorts of user C functions can be added to the database server at runtime,
you compile the C to a .so shared object file (.DLL on Windows), and load it
into the database server with a CREATE FUNCTION statement.
See http://www.postgresql.org/docs/8.0/static/xfunc-c.html for how you develop,
install, and use these sorts of functions.

otherwise, AFAIK, you'd have to design a new compressed data protocol for
pgsql, implement it in the postgres postmaster, and implement it in the jdbc
client library (as well as pgsql.so for the client applications that aren't
written in Java).

Since you specifically asked about other solutions than the JDBC driver
itself -- we use a middle tier which makes the JDBC requests.  Clients
communicate with the middle tier through non-persistent queue-based JMS
(developed in-house) which can compress the message body.  This works
well for us.  If you run this middle tier on the database server, the
non-compressed data doesn't go "over the wire".

Alternatively, it would probably be less than a week's work to write a
JDBC driver which just forwarded the requests to a process which used
the postgres JDBC driver to hit the database.  You would essentially
forward the requests and responses, with compression around those
portions which would benefit.

Just a couple quick ideas.  Maybe it'll get you thinking in a direction
that will work even better for you.

-Kevin


>>> Javier <jgagis@gmail.com> 06/17/05 11:26 AM >>>

. . .
    Last question --> Anyone knows any other way to achieve compression
with other kind of solution???
. . .

Javier wrote:
>
>    Hi, I'm developing a java client application that uses JDBC to access
> a PostGIS/PostgreSQL database. I'm working with PostgreSQL JDBC driver
> Type 4 (http://jdbc.postgresql.org/) and I need to compress the output
> streamdata from database queries.
>
>    The problem can't be resolved making a better WHERE clause, because I
> have to received a great amount of GIS information, and this info can be
> highly compressed. Moreover, this data can't be stored compressed in the
> database, so, an external compressor is needed.
>
>   Anybody can explain me if it is possible to compress a JDBC datastream
> ??? Is this a PostgreSQL JDBC driver issue??? or a new function must be
> added to PostgreSQL???

I'm slightly confused about what you're trying to do here.

Are you talking about compressing the protocol stream between the server
and client? This would be a win if the bottleneck is the speed of the
network between server and client. If so, the protocol doesn't directly
support it but there was some discussion on -hackers about a pluggable
stream filter API that could be used for compression:
http://archives.postgresql.org/pgsql-hackers/2005-04/msg00792.php.

Alternatively, since the connection to the DB server is just a TCP
connection, you could write a small server that accepts connections
locally, compresses data, and forwards it over a separate TCP connection
to an equivalent server that decompresses the data and sends it on to
the server. A quick&dirty way of doing that is using ssh's
port-tunnelling options over a compressed ssh connection, something like
this:

clienthost$ ssh -N -C -L 5432:serverhost:5432 user@serverhost

(then point the JDBC driver at 'clienthost')

If your concern is about the volume of data that the JDBC layer is
processing, then compression at the protocol level isn't going to help.
You'll need to do the transformation to a more compact form on the
server side (e.g. via an appropriate PL or C function) and be ready to
receive that form in your application.

-O