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
