Compressing a json-shaped postgres response during transport

Question

We have an application that has a complex sql code generation scheme, that outputs queries of the form:

SELECT
 coalesce(json_agg("foo"), '[]') AS "foo"
FROM
 (
 SELECT
 row_to_json( ...
 ) AS "foo"
 ...

My goal is to reduce the bandwidth used between our server and database, without significantly altering code generation ( which would be a huge effort).

To give an example of what I'm looking for, one thing I tried was to see if making the top level return type jsonb made any difference, but it does not. In both cases I see the same sizes, and textual data sent over the socket:

$ strace -e trace=network -s 100 psql -f query.sql ...
recvfrom(4, "T0円0円0円35円0円1円root0円0円0円0円0円0円0円0円0円0円r377円377円377円377円377円377円0円0円D0円6円214円N0円1円0円6円214円D[{\"Tracks\":[{\"Composer\":\"Angus Young, Malcolm Young, Brian "..., 16384, 0, NULL, NULL) = 16384
recvfrom(4, ", {\"Composer\":\"Frank Zappa\",\"MediaTypeId\":1,\"Milliseconds\":234553,\"PlaylistTracks\":[{\"TrackId\":351,\""..., 507934, 0, NULL, NULL) = 62224
recvfrom(4, "Track\":{\"AlbumId\":104}}]}, {\"Composer\":null,\"MediaTypeId\":1,\"Milliseconds\":494602,\"PlaylistTracks\":["..., 445710, 0, NULL, NULL) = 65536

I should say that our application uses bindings to libpq to query the database.

So are there some other simple strategies I can use? for example is there a type equivalent to Json that I can cast to, and which would be trivial to parse back into Json on the server? Is there some hidden compression scheme that I missed? I'm not able to add any extensions to the server.

I would also be interested in suggestions for literally compressing at the transport layer, but from my research that doesn't really seem possible, unless I wanted to use a VPN or some other compressing proxy. I'm not interested in "don’t do this" answers that would involve significant rework of query generation

• Laurenz Albe: I wouldn't worry. JSON is pretty compact anyway.
• jberryman: json_agg is inherently very not compact

Answer 1

There was a compression option at the SSL layer, but it was removed due to security concerns. Postgresql does not have built-in tools for compressing network traffic, and it cannot be an extension (an extension cannot change the protocol).

There is also no built-in compression function available from SQL. I can't think of anything suitable for select xxx_encode(json_agg(...)) that would reduce the amount of data transferred. pl/perl or pl/python are unlikely to be available in your database.

jsonb is indeed converted to text format during data transfer (even when binary format was requested). This was done on purpose, as it leaves the opportunity to change the internal jsonb format without breaking compatibility with applications.