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.
- Clients who need the uncompressed json (and who will handle the uncompression themselves)
- A Service that serves multiple clients. I am trying to protect the service's memory. The service never needs to see the uncompressed json.
- 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.