Re: libpq compression

From: Grigory Smolkin <g(dot)smolkin(at)postgrespro(dot)ru>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Cc: Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>
Subject: Re: libpq compression
Date: 2018-05-16 15:09:03
Message-ID: dbf9035f-7f7b-b053-cc2a-52e5147d4557@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2018-05-16 15:11:17 Re: Allow COPY's 'text' format to output a header
Previous Message Tom Lane 2018-05-16 15:00:28 Re: Should total_pages be calculated after partition pruning and constraint exclusion?