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, "T\0\0\0\35\0\1root\0\0\0\0\0\0\0\0\0\0r\377\377\377\377\377\377\0\0D\0\6\214N\0\1\0\6\214D[{\"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

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.