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

  • 2
    Sounds like an X-Y problem to me. What are you planning to do with compressed strings? You'll still have to uncompress them to parse/display/whatever. – mustaccio Aug 25 '17 at 20:33
  • I'm voting to close this because it's unclear what you're asking. How does compressing json to have it uncompressed by Java save you space on the heap? I agree with @mustaccio here. – Evan Carroll Aug 26 '17 at 2:53
  • If you are concerned about heap space, you could do the compression in Java. The intermediate objects from the ResultSet will be collected pretty quickly anyways. – a_horse_with_no_name Aug 26 '17 at 8:52
  • 1
    Check out github.com/grahamedgecombe/pgflate but also see first if using a server-side cursor to fetch one row at a time wouldn't better address the memory problem. – Daniel Vérité Aug 26 '17 at 14:19
  • @EvanCarroll - Updated question to answer your question. – sixtyfootersdude Aug 28 '17 at 14:43

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

    AS $_X$
    use Compress::Zlib;
    my $x = Compress::Zlib::memGzip($_[0]);
    return encode_bytea($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?

| improve this answer | |
  • That's not thread safe, and probably shouldn't be marked so. Compress::Zlib uses Compress::Raw::Zlib which has an open bug about thread safety. I don't see the benefit of this at all: if you're trying to save on transit use a vpn with compression. If you're trying to save space in the DB -- it's toasted automagically after 2kb. And, I don't see how having to uncompress this with zlib will result in less space on Java's heap (from the OP) looking to save "mostly java-heap-space" – Evan Carroll Aug 26 '17 at 3:00
  • PARALLEL SAFE is not about thread safety, as postgresql is not multi-threaded. If you read multiple rows as compressed data into memory, and then uncompress them one at a time in one Java and discard, it will use less memory. – jjanes Aug 26 '17 at 17:47
  • 1
    It is not multithreaded. It uses separate processes, not threads, to achieve parallelism. – jjanes Aug 26 '17 at 18:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.