18

Is there any internal method in postgresql to enable data compression over psql connection over tcp socket.

I can also use some other method that are not internal to postgresql, such as ssh compression or any other vpn compression. However, this may increase complexity.

Regards,

Improve this question
3
  • 1
    Tuning your queries to return only what is needed likely would be a simpler approach. Commented Aug 28, 2011 at 1:58
  • This would be great for jdbc as well. ETL using java usually requires lots of data (and thus lots of network traffic). Commented Jan 21, 2015 at 14:07
  • It can be worth it, though. On our mixed replication traffic SSL compression reduces traffic volume by almost 70% and packet count by a half. If you're running a busy server with limited transfer allocation, that's a savings you can take to the bank. Note that this is in addition to the option for compressed WAL. Commented Nov 7, 2018 at 22:56

2 Answers 2

Reset to default
6

It seems that starting with postgresql 9.2 there is an sslcompression option that defaults to true. However it requires that the used openssl library is build with zlib support to work. For instance the openssl binaries in the windows enterprise DB installers do not have this support. Also both sides have ofcourse to support it!

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This is getting harder thanks to CRIME. You have to not only set sslcompression=1 in the connection string (or use environment variable PGSSLCOMPRESSION=1), but also compile OpenSSL with zlib-dynamic or zlib options (not no-zlib) and (for OpenSSL 1.1.1) set Compression=On and MaxProtocol=TLSv1.2 in the /etc/ssl/openssl.cnf (for Debian, anyway). This is because TLS 1.3 cuts out zlib compression and requires that clients send a TLS 1.3 ClientHello with no compression method - if the server downgrades to TLS 1.2 it won't have compression, it only does so if the client sends a TLS 1.2 ClientHello.
3

When I was asking the same question about two months ago, the only answer was: use SSH. However I haven't checked that as we managed to split column into two and download about 200kB per query instead of 20MB and it was enough.

We had big rows in a table (with JSON inside). Your problem can be slightly different. Maybe you are getting rows you don't need? I've seen that too many times: get all rows from a table, sort in application, then get the first 10... when it is enough to use order by and limit.

Improve this answer

2 Comments

ssh is not handy for the purpose. For implementing ssh compression we have to have a ssh tunnel pinned to localhost:someport and we have to show the software this virtual port as postgresql port. can you suggest some other way? It is hard to understand why such compression is not developed if the protocol is tcp based and deals with large amounts of data.
You can call psql on a remote machine like this: ssh -t psql

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.