What we found is that using lz4 compression on JSONB data is 20-25% larger
on disk than pglz. We are running a production workload that is storing
jsonb data with a focus read performance. The documented increase in write
speed wasn't a large benefit, however, the increase in storage size moved
the bulk of our data into TOAST and off the JSON performance cliff ("2-10×
slower queries") described by Evan
<https://www.evanjones.ca/postgres-large-json-performance.html> was
impactful.
This
<https://www.postgresql.fastware.com/blog/what-is-the-new-lz4-toast-compression-in-postgresql-14>
article
does a nice job describing the differences between pglz and lz4 compression
for different data but does not include json or jsonb.
I believe validation of our numbers and additional documentation on the
trade-offs in compression types would be very useful.
On Fri, Dec 29, 2023 at 7:23 AM Tom Lane <t...@sss.pgh.pa.us> wrote:
> Junwang Zhao <zhjw...@gmail.com> writes:
> > On Fri, Dec 29, 2023 at 4:47 AM Adrian Klaver <adrian.kla...@aklaver.com>
> wrote:
> >> For what purpose? You are seeing differences in compression strategies
> >> between lz4 and pglz. The 'fix' would be to go back to pglz.
>
> > Agreed, lz4 is known for its high compression speed, but lower
> > compression ratio, this is the trade off one should bear in mind.
>
> I don't know if we can make any blanket statements like that, but
> if we can, shouldn't there be some advice in the manual? AFAICS,
> right now there's exactly zip about why you should choose one over
> the other.
>
> regards, tom lane
>
