Re:Using PostgreSQL’s JSONB for NoSQL (2019) [video]
by fabian2k on Sunday September 05, 2021 @09:05AM
(#28424045)
In Postgres you would generally use JSONB columns, those are stored as TOASTs and are automatically compressed. That compression isn't very good, but there is some work on adding other compression algorithms there.
Re:Using PostgreSQL’s JSONB for NoSQL (2019) [video]
by tpetry on Sunday September 05, 2021 @10:18AM
(#28424529)
PostgreSQL 14 will add lz4 compression for toast data. But if i remember correctly the first ~700 bytes are always stored inline and only the rest is compressed. But i could be wrong on this.
One trick I learned along the way is to apply gzip to your JSON columns. For us, we got over 70% reduction in column size and query performance actually increased. I presume because it is faster to read fewer IO blocks than more, esp when doing table scans (which you shouldnt but they happen).
The only caveat with this is if you want to query against something in the JSON, but I think this is where the balancing act comes into play regarding what is in (compressed) JSON and what is in a column.