pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Increased storage size of jsonb in pg15
18+ messages / 5 participants
[nested] [flat]

* Increased storage size of jsonb in pg15
@ 2023-12-13 22:07  Sean Flaherty <sflaherty@grndwork.com>
  0 siblings, 1 reply; 18+ messages in thread

From: Sean Flaherty @ 2023-12-13 22:07 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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 written to the TOAST table.

I can't find any information online, is anyone aware of what would cause
this change in storage size for essentially the same data?

Kind regards,
Sean

^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: Increased storage size of jsonb in pg15
@ 2023-12-13 22:21  Adrian Klaver <adrian.klaver@aklaver.com>
  parent: Sean Flaherty <sflaherty@grndwork.com>
  0 siblings, 1 reply; 18+ messages in thread

From: Adrian Klaver @ 2023-12-13 22:21 UTC (permalink / raw)
  To: Sean Flaherty <sflaherty@grndwork.com>; pgsql-general@lists.postgresql.org

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 written to the TOAST table.

More information needed:

1) Table definition.

2) An example of reported size for the 14.? and 15.5 cases.

3) Differences in the database attributes between the two instances.

4) Was this done on same machine? If not how are the machines different?

5) How the upgrade was done?


> 
> I can't find any information online, is anyone aware of what would cause 
> this change in storage size for essentially the same data?
> 
> Kind regards,
> Sean

-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: Increased storage size of jsonb in pg15
@ 2023-12-13 23:49  Sean Flaherty <sflaherty@grndwork.com>
  parent: Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 1 reply; 18+ messages in thread

From: Sean Flaherty @ 2023-12-13 23:49 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org

More information needed:

1) Table definition.

\d "DataRecords"
                Partitioned table "public.DataRecords"
   Column   |            Type             | Collation | Nullable |
Default
------------+-----------------------------+-----------+----------+-------------
 filename   | character varying(255)      |           | not null |
 timestamp  | timestamp without time zone |           | not null |
 data       | jsonb                       |           | not null |
'{}'::jsonb
 created_at | timestamp with time zone    |           | not null |
 updated_at | timestamp with time zone    |           | not null |
Partition key: RANGE ("timestamp")
Indexes:
    "DataRecords_pkey" PRIMARY KEY, btree (filename, "timestamp")
    "DataRecords_filename_updated_at_idx" btree (filename, updated_at)
Foreign-key constraints:
    "DataRecords_filename_fkey" FOREIGN KEY (filename) REFERENCES
"DataFiles"(filename) ON UPDATE CASCADE ON DELETE CASCADE
Number of partitions: 97 (Use \d+ to list them.)


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 upgrade (upgrade indicated in yellow):


*file* *hourly_timestamp* *filename_bytes* *timestamp_bytes* *data_filesize*
*created_at_bytes* *updated_at_bytes*
datafile.dat 2023-10-19 12:00:00 23 8 1682 8 8
datafile.dat 2023-10-19 13:00:00 23 8 1687 8 8
datafile.dat 2023-10-19 14:00:00 23 8 1685 8 8
datafile.dat 2023-10-19 15:00:00 23 8 1668 8 8
datafile.dat 2023-10-19 16:00:00 23 8 2155 8 8
datafile.dat 2023-10-19 17:00:00 23 8 2178 8 8
datafile.dat 2023-10-19 18:00:00 23 8 2199 8 8
datafile.dat 2023-10-19 19:00:00 23 8 2187 8 8
datafile.dat 2023-10-19 20:00:00 23 8 2180 8 8
datafile.dat 2023-10-19 21:00:00 23 8 2176 8 8
datafile.dat 2023-10-19 22:00:00 23 8 2053 8 8
datafile.dat 2023-10-19 23:00:00 23 8 2043 8 8
3) Differences in the database attributes between the two instances.

  No differences.

4) Was this done on same machine? If not how are the machines different?

  Same AWS RDS Postgres instance.

5) How the upgrade was done?

  Using the AWS RDS upgrade.



On Wed, Dec 13, 2023 at 3:21 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

> 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 written to the TOAST table.
>
> More information needed:
>
> 1) Table definition.
>
> 2) An example of reported size for the 14.? and 15.5 cases.
>
> 3) Differences in the database attributes between the two instances.
>
> 4) Was this done on same machine? If not how are the machines different?
>
> 5) How the upgrade was done?
>
>
> >
> > I can't find any information online, is anyone aware of what would cause
> > this change in storage size for essentially the same data?
> >
> > Kind regards,
> > Sean
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>

^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: Increased storage size of jsonb in pg15
@ 2023-12-13 23:56  Adrian Klaver <adrian.klaver@aklaver.com>
  parent: Sean Flaherty <sflaherty@grndwork.com>
  0 siblings, 1 reply; 18+ messages in thread

From: Adrian Klaver @ 2023-12-13 23:56 UTC (permalink / raw)
  To: Sean Flaherty <sflaherty@grndwork.com>; +Cc: pgsql-general@lists.postgresql.org

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 upgrade (upgrade indicated in yellow):

What is producing datafile.dat and how?

> 
> *file*	*hourly_timestamp*	*filename_bytes*	*timestamp_bytes* 
> *data_filesize*	*created_at_bytes*	*updated_at_bytes*
> datafile.dat	2023-10-19 12:00:00	23	8	1682	8	8
> datafile.dat	2023-10-19 13:00:00	23	8	1687	8	8
> datafile.dat	2023-10-19 14:00:00	23	8	1685	8	8
> datafile.dat	2023-10-19 15:00:00	23	8	1668	8	8
> datafile.dat	2023-10-19 16:00:00	23	8	2155	8	8
> datafile.dat	2023-10-19 17:00:00	23	8	2178	8	8
> datafile.dat	2023-10-19 18:00:00	23	8	2199	8	8
> datafile.dat	2023-10-19 19:00:00	23	8	2187	8	8
> datafile.dat	2023-10-19 20:00:00	23	8	2180	8	8
> datafile.dat	2023-10-19 21:00:00	23	8	2176	8	8
> datafile.dat	2023-10-19 22:00:00	23	8	2053	8	8
> datafile.dat	2023-10-19 23:00:00	23	8	2043	8	8

-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: Increased storage size of jsonb in pg15
@ 2023-12-14 14:48  Sean Flaherty <sflaherty@grndwork.com>
  parent: Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 2 replies; 18+ messages in thread

From: Sean Flaherty @ 2023-12-14 14:48 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org

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
<https://github.com/brianc/node-postgres; 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 underlying database upgrade.


On Wed, Dec 13, 2023 at 4:56 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

> 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 upgrade (upgrade indicated in yellow):
>
> What is producing datafile.dat and how?
>
> >
> > *file*        *hourly_timestamp*      *filename_bytes*
> *timestamp_bytes*
> > *data_filesize*       *created_at_bytes*      *updated_at_bytes*
> > datafile.dat  2023-10-19 12:00:00     23      8       1682    8       8
> > datafile.dat  2023-10-19 13:00:00     23      8       1687    8       8
> > datafile.dat  2023-10-19 14:00:00     23      8       1685    8       8
> > datafile.dat  2023-10-19 15:00:00     23      8       1668    8       8
> > datafile.dat  2023-10-19 16:00:00     23      8       2155    8       8
> > datafile.dat  2023-10-19 17:00:00     23      8       2178    8       8
> > datafile.dat  2023-10-19 18:00:00     23      8       2199    8       8
> > datafile.dat  2023-10-19 19:00:00     23      8       2187    8       8
> > datafile.dat  2023-10-19 20:00:00     23      8       2180    8       8
> > datafile.dat  2023-10-19 21:00:00     23      8       2176    8       8
> > datafile.dat  2023-10-19 22:00:00     23      8       2053    8       8
> > datafile.dat  2023-10-19 23:00:00     23      8       2043    8       8
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>

^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: Increased storage size of jsonb in pg15
@ 2023-12-14 16:16  Adrian Klaver <adrian.klaver@aklaver.com>
  parent: Sean Flaherty <sflaherty@grndwork.com>
  1 sibling, 0 replies; 18+ messages in thread

From: Adrian Klaver @ 2023-12-14 16:16 UTC (permalink / raw)
  To: Sean Flaherty <sflaherty@grndwork.com>; +Cc: pgsql-general@lists.postgresql.org

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 
> <https://github.com/brianc/node-postgres; version "8.8.0" to create the 
> json objects and insert the data records as jsonb data.

Now I am not understanding.

1) In your OP you mentioned checking size of the column storage using 
pg_column_size, yet what you show for increase in size are datafile.dat.

2) So how is datafile.dat related to this issue?

3) Show how you are determining that the storage in the database has 
increased in size.

> 
> None of the upload process changed during the underlying database upgrade.
> 
> 
> On Wed, Dec 13, 2023 at 4:56 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     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 upgrade (upgrade indicated in yellow):
> 
>     What is producing datafile.dat and how?
> 
>      >
>      > *file*        *hourly_timestamp*      *filename_bytes*       
>     *timestamp_bytes*
>      > *data_filesize*       *created_at_bytes*      *updated_at_bytes*
>      > datafile.dat  2023-10-19 12:00:00     23      8       1682    8 
>           8
>      > datafile.dat  2023-10-19 13:00:00     23      8       1687    8 
>           8
>      > datafile.dat  2023-10-19 14:00:00     23      8       1685    8 
>           8
>      > datafile.dat  2023-10-19 15:00:00     23      8       1668    8 
>           8
>      > datafile.dat  2023-10-19 16:00:00     23      8       2155    8 
>           8
>      > datafile.dat  2023-10-19 17:00:00     23      8       2178    8 
>           8
>      > datafile.dat  2023-10-19 18:00:00     23      8       2199    8 
>           8
>      > datafile.dat  2023-10-19 19:00:00     23      8       2187    8 
>           8
>      > datafile.dat  2023-10-19 20:00:00     23      8       2180    8 
>           8
>      > datafile.dat  2023-10-19 21:00:00     23      8       2176    8 
>           8
>      > datafile.dat  2023-10-19 22:00:00     23      8       2053    8 
>           8
>      > datafile.dat  2023-10-19 23:00:00     23      8       2043    8 
>           8
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: Increased storage size of jsonb in pg15
@ 2023-12-14 16:46  David G. Johnston <david.g.johnston@gmail.com>
  parent: Sean Flaherty <sflaherty@grndwork.com>
  1 sibling, 1 reply; 18+ messages in thread

From: David G. Johnston @ 2023-12-14 16:46 UTC (permalink / raw)
  To: Sean Flaherty <sflaherty@grndwork.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org

On Thu, Dec 14, 2023 at 7:48 AM Sean Flaherty <sflaherty@grndwork.com>
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
> <https://github.com/brianc/node-postgres; 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 underlying database upgrade.
>

Basic debugging requires the existence of a self-contained reproducer.  In
this case ideally one that only uses psql and some static (already
processed) data files, and that is known to produce the observed behaviors
on non-RDS PostgreSQL.

David J.

^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: Increased storage size of jsonb in pg15
@ 2023-12-15 20:43  Sean Flaherty <sflaherty@grndwork.com>
  parent: David G. Johnston <david.g.johnston@gmail.com>
  0 siblings, 1 reply; 18+ messages in thread

From: Sean Flaherty @ 2023-12-15 20:43 UTC (permalink / raw)
  To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org

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).

*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


On Thu, Dec 14, 2023 at 9:47 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:

> On Thu, Dec 14, 2023 at 7:48 AM Sean Flaherty <sflaherty@grndwork.com>
> 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
>> <https://github.com/brianc/node-postgres; 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 underlying database upgrade.
>>
>
> Basic debugging requires the existence of a self-contained reproducer.  In
> this case ideally one that only uses psql and some static (already
> processed) data files, and that is known to produce the observed behaviors
> on non-RDS PostgreSQL.
>
> David J.
>
>

^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: Increased storage size of jsonb in pg15
@ 2023-12-15 20:56  Adrian Klaver <adrian.klaver@aklaver.com>
  parent: Sean Flaherty <sflaherty@grndwork.com>
  0 siblings, 1 reply; 18+ messages in thread

From: Adrian Klaver @ 2023-12-15 20:56 UTC (permalink / raw)
  To: Sean Flaherty <sflaherty@grndwork.com>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

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






^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: Increased storage size of jsonb in pg15
@ 2023-12-28 17:13  Sean Flaherty <sflaherty@grndwork.com>
  parent: Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 1 reply; 18+ messages in thread

From: Sean Flaherty @ 2023-12-28 17:13 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-general@lists.postgresql.org

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.


Kind regards,
Sean


On Fri, Dec 15, 2023 at 1:56 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

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

Attachments:

  jsonDataStorageTestScript.zip (application/zip, 1.3K) download

^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: Increased storage size of jsonb in pg15
@ 2023-12-28 18:23  Adrian Klaver <adrian.klaver@aklaver.com>
  parent: Sean Flaherty <sflaherty@grndwork.com>
  0 siblings, 1 reply; 18+ messages in thread

From: Adrian Klaver @ 2023-12-28 18:23 UTC (permalink / raw)
  To: Sean Flaherty <sflaherty@grndwork.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-general@lists.postgresql.org

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 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.

I can confirm I see the same results using Postgres 16 installed from 
the PGDG repo on Ubuntu 22.04. That the lz4 data size is greater then 
the pglz data size.

> 
> 
> Kind regards,
> Sean
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: Increased storage size of jsonb in pg15
@ 2023-12-28 20:41  Sean Flaherty <sflaherty@grndwork.com>
  parent: Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 1 reply; 18+ messages in thread

From: Sean Flaherty @ 2023-12-28 20:41 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-general@lists.postgresql.org

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 <adrian.klaver@aklaver.com>
wrote:

> 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 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.
>
> I can confirm I see the same results using Postgres 16 installed from
> the PGDG repo on Ubuntu 22.04. That the lz4 data size is greater then
> the pglz data size.
>
> >
> >
> > Kind regards,
> > Sean
> >
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>

^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: Increased storage size of jsonb in pg15
@ 2023-12-28 20:47  Adrian Klaver <adrian.klaver@aklaver.com>
  parent: Sean Flaherty <sflaherty@grndwork.com>
  0 siblings, 1 reply; 18+ messages in thread

From: Adrian Klaver @ 2023-12-28 20:47 UTC (permalink / raw)
  To: Sean Flaherty <sflaherty@grndwork.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-general@lists.postgresql.org

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.

> 
> On Thu, Dec 28, 2023 at 11:23 AM Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     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 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.
> 
>     I can confirm I see the same results using Postgres 16 installed from
>     the PGDG repo on Ubuntu 22.04. That the lz4 data size is greater then
>     the pglz data size.
> 
>      >
>      >
>      > Kind regards,
>      > Sean
>      >
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: Increased storage size of jsonb in pg15
@ 2023-12-29 10:05  Junwang Zhao <zhjwpku@gmail.com>
  parent: Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 1 reply; 18+ messages in thread

From: Junwang Zhao @ 2023-12-29 10:05 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Sean Flaherty <sflaherty@grndwork.com>; David G. Johnston <david.g.johnston@gmail.com>; pgsql-general@lists.postgresql.org

On Fri, Dec 29, 2023 at 4:47 AM Adrian Klaver <adrian.klaver@aklaver.com> 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 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.

>
> >
> > On Thu, Dec 28, 2023 at 11:23 AM Adrian Klaver
> > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
> >
> >     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 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.
> >
> >     I can confirm I see the same results using Postgres 16 installed from
> >     the PGDG repo on Ubuntu 22.04. That the lz4 data size is greater then
> >     the pglz data size.
> >
> >      >
> >      >
> >      > Kind regards,
> >      > Sean
> >      >
> >
> >
> >     --
> >     Adrian Klaver
> >     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> >
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>
>


-- 
Regards
Junwang Zhao





^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: Increased storage size of jsonb in pg15
@ 2023-12-29 14:22  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Junwang Zhao <zhjwpku@gmail.com>
  0 siblings, 1 reply; 18+ messages in thread

From: Tom Lane @ 2023-12-29 14:22 UTC (permalink / raw)
  To: Junwang Zhao <zhjwpku@gmail.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; Sean Flaherty <sflaherty@grndwork.com>; David G. Johnston <david.g.johnston@gmail.com>; pgsql-general@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: Increased storage size of jsonb in pg15
@ 2023-12-29 15:21  Sean Flaherty <sflaherty@grndwork.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 18+ messages in thread

From: Sean Flaherty @ 2023-12-29 15:21 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Junwang Zhao <zhjwpku@gmail.com>; Adrian Klaver <adrian.klaver@aklaver.com>; David G. Johnston <david.g.johnston@gmail.com>; pgsql-general@lists.postgresql.org

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.

On Fri, Dec 29, 2023 at 7:23 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

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

^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: Increased storage size of jsonb in pg15
@ 2023-12-29 16:43  Adrian Klaver <adrian.klaver@aklaver.com>
  parent: Sean Flaherty <sflaherty@grndwork.com>
  0 siblings, 1 reply; 18+ messages in thread

From: Adrian Klaver @ 2023-12-29 16:43 UTC (permalink / raw)
  To: Sean Flaherty <sflaherty@grndwork.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Junwang Zhao <zhjwpku@gmail.com>; David G. Johnston <david.g.johnston@gmail.com>; pgsql-general@lists.postgresql.org

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> ... 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
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: Increased storage size of jsonb in pg15
@ 2023-12-29 17:23  Sean Flaherty <sflaherty@grndwork.com>
  parent: Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 0 replies; 18+ messages in thread

From: Sean Flaherty @ 2023-12-29 17:23 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Junwang Zhao <zhjwpku@gmail.com>; David G. Johnston <david.g.johnston@gmail.com>; pgsql-general@lists.postgresql.org

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.klaver@aklaver.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@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
> >
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>

^ permalink  raw  reply  [nested|flat] 18+ messages in thread


end of thread, other threads:[~2023-12-29 17:23 UTC | newest]

Thread overview: 18+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-12-13 22:07 Increased storage size of jsonb in pg15 Sean Flaherty <sflaherty@grndwork.com>
2023-12-13 22:21 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-12-13 23:49   ` Sean Flaherty <sflaherty@grndwork.com>
2023-12-13 23:56     ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-12-14 14:48       ` Sean Flaherty <sflaherty@grndwork.com>
2023-12-14 16:16         ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-12-14 16:46         ` David G. Johnston <david.g.johnston@gmail.com>
2023-12-15 20:43           ` Sean Flaherty <sflaherty@grndwork.com>
2023-12-15 20:56             ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-12-28 17:13               ` Sean Flaherty <sflaherty@grndwork.com>
2023-12-28 18:23                 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-12-28 20:41                   ` Sean Flaherty <sflaherty@grndwork.com>
2023-12-28 20:47                     ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-12-29 10:05                       ` Junwang Zhao <zhjwpku@gmail.com>
2023-12-29 14:22                         ` Tom Lane <tgl@sss.pgh.pa.us>
2023-12-29 15:21                           ` Sean Flaherty <sflaherty@grndwork.com>
2023-12-29 16:43                             ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-12-29 17:23                               ` Sean Flaherty <sflaherty@grndwork.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox