Follow-up:
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. This resulted in the increased json storage size we were seeing.
I have been able to reproduce the increased storage size on RDS Postgres and using my local docker instance of postgres 15.5 by changing the local default_toast_compression value in postgresql.conf.
I have attached the test script we use to create a table, insert some test records and a query to test the JSON data size on disk.
On 12/15/23 12:43, Sean Flaherty wrote:
> We did a little more digging on our side, which I apologize for not
> doing beforehand.
>
> We wrote a script to create a simple table with a jsonb column, inserted
> some json data into the column and then used *pg_column_size* to test
> the size of the jsonb data column.
>
> We ran the script against local docker versions of postgres 14.8 and
> 15.5 and saw that the data in the jsonb column was the same size.
>
> We then ran the script on AWS RDS Postgres and saw a size difference for
> the same json data between versions 14.8 and 15.4 (we also saw the
> larger size on RDS postgres 15.5 and 16.1).
This points at RDS Postgres being the issue. Since that is behind the
AWS curtain, you will need to contact AWS technical support for
information on what changed.
>
> *Version* *size (kb)* *notes *
> RDS Postgres-14.8 1587 (the same size we were seeing locally)
> RDS Postgres-15.4 2112
>
>
> Thank you for your prompt responses and assistance.
>
> If you are curious, I can share the script we used with you.
>
>
> Kind regards,
> Sean
--
Adrian Klaver
adrian.klaver@aklaver.com