Re: Increased storage size of jsonb in pg15

On 12/29/23 07:21, Sean Flaherty wrote:
> 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
jobdescribing 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.

Yes, that would be useful.

Also per this:

"Working with AWS, we found that starting in RDS Postgres 15, the
default_toast_compression parameter is set to use lz4 compression 
instead of pglz."

there is a discussion to be had with AWS about the advisability of 
changing defaults without testing what that does to the end user or 
notifying the end user.


> 
> On Fri, Dec 29, 2023 at 7:23 AM Tom Lane <tgl@sss.pgh.pa.us 
> <mailto:tgl@sss.pgh.pa.us>> wrote:
> 
>     Junwang Zhao <zhjwpku@gmail.com <mailto:zhjwpku@gmail.com>> writes:
>      > On Fri, Dec 29, 2023 at 4:47 AM Adrian Klaver
>     <adrian.klaver@aklaver.com <mailto:adrian.klaver@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
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com