3

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

8
  • 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, 2017 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. Aug 26, 2017 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.
    – user1822
    Aug 26, 2017 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. Aug 26, 2017 at 14:19
  • @EvanCarroll - Updated question to answer your question. Aug 28, 2017 at 14:43

1 Answer 1

5

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?

3
  • 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" Aug 26, 2017 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, 2017 at 17:47
  • 1
    It is not multithreaded. It uses separate processes, not threads, to achieve parallelism.
    – jjanes
    Aug 26, 2017 at 18:00

Your Answer

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

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