Re: libpq compression

Hello!
I have noticed that psql --help lack -Z|--compression option.
Also it would be nice to have option like --compression-level in psql
and pgbench.

On 03/30/2018 03:53 PM, Konstantin Knizhnik 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.
> Below are some results:
>
> Compression ratio (raw->compressed):
>
>
> libz (level=1)
> libzstd (level=1)
> pgbench -i -s 10
> 16997209->2536330
> 16997209->268077
> pgbench -t 100000 -S
> 6289036->1523862
> 6600338<-900293
> 6288933->1777400
> 6600338<-1000318
>
>
> There is no mistyping: libzstd compress COPY data about 10 times
> better than libz, with wonderful compression ratio 63.
>
> Influence on execution time is minimal (I have tested local
> configuration when client and server are at the same host):
>
>
> no compression
> libz (level=1)
> libzstd (level=1)
> pgbench -i -s 10
> 1.552
> 1.572
> 1.611
> pgbench -t 100000 -S
> 4.482
> 4.926
> 4.877
>
> --
> Konstantin Knizhnik
> Postgres Professional:http://www.postgrespro.com
> The Russian Postgres Company

--
Grigory Smolkin
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company