I have included your suggestion to document any changes to the default Postgres settings to the Amazon RDS for PostgreSQL updates page in our ticket with AWS.
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 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.
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 >