Postgresql – Compression function in Postgres

compressionjdbcpostgresql

Using Java & JDBC I am storing Strings into a JSONB column in Postgres. When inserting this works great.

When retrieving values, I am running low on memory in Java because I am retrieving larger quantities and each json/string is about 1MB. What I would like to do is write a query like this:

SELECT compress( myJsonColumn ) FROM myTable WHERE ...

I have looked around a bit for a compress method in Postgres, but haven't found one. Any pointers would be appreciated. Thanks.

Clarification: Just to be clear, I don't want to compress the data in the database. I think that Postgres will handle this gracefully, my concern is mostly java-heap-space.

I have three components in my architecture.

  1. Clients who need the uncompressed json (and who will handle the uncompression themselves)
  2. A Service that serves multiple clients. I am trying to protect the service's memory. The service never needs to see the uncompressed json.
  3. The DB. I want to store the json in a jsonb field, not as binary so that I can write new queries against the data later if required.

enter image description here

Best Answer

I've recently had the occasion to use something like this:

CREATE FUNCTION gzip(text) RETURNS bytea
    LANGUAGE plperlu IMMUTABLE STRICT COST 100000 PARALLEL SAFE
    AS $_X$
    use Compress::Zlib;
    my $x = Compress::Zlib::memGzip($_[0]);
    return encode_bytea($x);
$_X$;

But note that bytea is itself transmitted in an escaped form which will enlarge the size. With any luck, JDBC implementation might unescape them on the fly as they are read into memory, and not store the entire result set in the raw form. Sorry, I don't use JDBC enough to know.

Can't you just use a cursor to process one row at a time and then discard it, rather than reading all rows into memory?

Related Question