After doing some experimentation, it appears as though COMPRESSION lz4 doesn't have any effect on JSONB columns (whereas it does for JSON, and TEXT). Is that indeed the case? If so, why is that?
I could dive into the Postgresql mailing lists and/or source code to try and find the answer, but hoping someone who already knows the answer can chime in.
I assume that you are talking about
ALTER TABLE ... ALTER COLUMN ... SET COMPRESSION lz4
That will certainly have an effect, but only if the table row you insert is big enough that PostgreSQL starts compressing the jsonb value. This threshold is set to a quarter of the available space in an 8kB page, so it is slightly less than 2kB.
Does LZ4 compression have an effect on JSONB columns?
Basically, the data type jsonb can be compressed:
SELECT typstorage FROM pg_type WHERE typname = 'jsonb'; -- 'x'
See:
But that only happens to newly inserted column values that exceed the size threshold pointed out by Laurenz, and only if the source is not an unchanged, already compressed column in the same DB. Postgres does not decompress and re-compress existing values, even when previously compressed with a different algorithm. The algorithm is remembered for every individual compressed value.
Changing a compressed value triggers re-compression, then with the current default algorithm for the column. (A no-op operation triggers it, too.)
Related:
