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 page in our ticket with AWS. On Fri, Dec 29, 2023 at 9:43 AM Adrian Klave

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, h

Re: Increased storage size of jsonb in pg15

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 t

Re: Increased storage size of jsonb in pg15

Junwang Zhao writes: > On Fri, Dec 29, 2023 at 4:47 AM Adrian Klaver > 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

Re: Increased storage size of jsonb in pg15

On Fri, Dec 29, 2023 at 4:47 AM Adrian Klaver wrote: > > On 12/28/23 12:41, Sean Flaherty wrote: > > I'm rather new to the mailing list, are there any additional steps I > > should take (i.e. posting to pgsql-hackers, etc.)? > > > > For what purpose? You are seeing differences in compression strat

Re: Increased storage size of jsonb in pg15

On 12/28/23 12:41, Sean Flaherty wrote: I'm rather new to the mailing list, are there any additional steps I should take (i.e. posting to pgsql-hackers, etc.)? For what purpose? You are seeing differences in compression strategies between lz4 and pglz. The 'fix' would be to go back to pglz.

Re: Increased storage size of jsonb in pg15

I'm rather new to the mailing list, are there any additional steps I should take (i.e. posting to pgsql-hackers, etc.)? On Thu, Dec 28, 2023 at 11:23 AM Adrian Klaver wrote: > On 12/28/23 09:13, Sean Flaherty wrote: > > Follow-up: > > Working with AWS, we found that starting in RDS Postgres 15,

Re: Increased storage size of jsonb in pg15

On 12/28/23 09:13, Sean Flaherty wrote: 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 reprodu

Re: Increased storage size of jsonb in pg15

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

Re: Increased storage size of jsonb in pg15

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 da

Re: Increased storage size of jsonb in pg15

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 d

Re: Increased storage size of jsonb in pg15

On Thu, Dec 14, 2023 at 7:48 AM Sean Flaherty wrote: > We have a process that runs once an hour to read the .dat file in csv > format then a node script using the pg package > version "8.8.0" to create the > json objects and insert the data records as jso

Re: Increased storage size of jsonb in pg15

On 12/14/23 06:48, Sean Flaherty wrote: We have a process that runs once an hour to read the .dat file in csv format then a node script using the pg package version "8.8.0" to create the json objects and insert the data records as jsonb data. Now I am

Re: Increased storage size of jsonb in pg15

We have a process that runs once an hour to read the .dat file in csv format then a node script using the pg package version "8.8.0" to create the json objects and insert the data records as jsonb data. None of the upload process changed during the underly

Re: Increased storage size of jsonb in pg15

On 12/13/23 15:49, Sean Flaherty wrote: More information needed: 2) An example of reported size for the 14.? and 15.5 cases.   Since upgrading from 14.8 to 15.5, the jsonb data that was previously written in 14.8 is reporting a smaller size than the same hourly data written after the upgr

Re: Increased storage size of jsonb in pg15

More information needed: 1) Table definition. \d "DataRecords" Partitioned table "public.DataRecords" Column |Type | Collation | Nullable | Default +-+---+--+- filename | character

Re: Increased storage size of jsonb in pg15

On 12/13/23 14:07, Sean Flaherty wrote: Hello, We are seeing an increase in the reported storage size of our jsonb columns (using pg_column_size) since upgrading to Postgres 15.5 from version 14.  The increase in size is enough to push us over the size threshold and more of our data to get wr