Re: JDBC compressed stream

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