Re: 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).

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(at)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