1

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.

1

2 Answers 2

0

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:

1
  • It was the lack of forced re-compression that was the issue. Thank you!
    – user295232
    Commented Jul 9 at 1:01
0

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.

1
  • Thank you (I'd upvote if I had enough reputation)!
    – user295232
    Commented Jul 9 at 1:02

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.