Re: libpq compression - Mailing list pgsql-hackers

> On 30 March 2018 at 14:53, Konstantin Knizhnik <k.knizhnik@postgrespro.ru> wrote:

> Hi hackers,

> One of our customers was managed to improve speed about 10 times by using SSL compression for the system where client and servers are located in different geographical regions

> and query results are very large because of JSON columns. Them actually do not need encryption, just compression.

> I expect that it is not the only case where compression of libpq protocol can be useful. Please notice that Postgres replication is also using libpq protocol.

>

> Taken in account that vulnerability was found in SSL compression and so SSLComppression is considered to be deprecated and insecure (http://www.postgresql-archive.org/disable-SSL-compression-td6010072.html), it will be nice to have some alternative mechanism of reducing  libpq traffic.

>

> I have implemented some prototype implementation of it (patch is attached).

> To use zstd compression, Postgres should be configured with --with-zstd. Otherwise compression will use zlib unless it is disabled by --without-zlib option.

> I have added compression=on/off parameter to connection string and -Z option to psql and pgbench utilities.

I'm a bit confused why there was no reply to this. I mean, it wasn't sent on

1st April, the patch still can be applied on top of the master branch and looks

like it even works.

I assume the main concern her is that it's implemented in a rather not

extensible way. Also, if I understand correctly, it compresses the data stream

in both direction server <-> client, not sure if there is any value in

compressing what a client sends to a server. But still I'm wondering why it

didn't start at least a discussion about how it can be implemented. Do I miss

something?

Implementation of libpq compression will be included in next release of PgProEE.
Looks like community is not so interested in this patch. Frankly speaking I do not understand why.
Compression of libpq traffic can significantly increase speed of:
1. COPY
2. Replication (both streaming and logical)
3. Queries returning large results sets (for example JSON) through slow connections.

It is possible to compress libpq traffic using SSL compression.  But SSL compression is unsafe and deteriorated feature.

Yes, this patch is not extensible: it can use either zlib either zstd. Unfortunately internal Postgres compression pglz doesn't provide streaming API.
May be it is good idea to combine it with Ildus patch (custom compression methods): https://commitfest.postgresql.org/18/1294/
In this case it will be possible to use any custom compression algorithm. But we need to design and implement streaming API for pglz and other compressors.

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company