Search Postgresql Archives

Re: Increased storage size of jsonb in pg15

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxx <mailto:tgl@xxxxxxxxxxxxx>> wrote:

    Junwang Zhao <zhjwpku@xxxxxxxxx <mailto:zhjwpku@xxxxxxxxx>> writes:
     > On Fri, Dec 29, 2023 at 4:47 AM Adrian Klaver
    <adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> 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@xxxxxxxxxxx






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux