Re: Increased storage size of jsonb in pg15

I have included your suggestion to document any changes to the default
Postgres settings to the Amazon RDS for PostgreSQL updates
<https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-versions.html>
page in our ticket with AWS.

On Fri, Dec 29, 2023 at 9:43 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

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