Increased storage size of json data in jsonb columns using RDS Postgres > 15

0

Hello,

Not sure if this is the correct place to file this issue but we are seeing a ~20% increase in the storage size of our jsonb data since upgrading from Postgres 14.8 to 15.5.

We have tested and are not seeing the issue on the docker versions of Postgres.

Here is a quick way to setup and reproduce:

CREATE TABLE IF NOT EXISTS "DataRecords" (
    filename character varying(255) COLLATE pg_catalog."default" NOT NULL,
    "timestamp" timestamp without time zone NOT NULL,
    data jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    CONSTRAINT "DataRecords_pkey" PRIMARY KEY (filename, "timestamp")
);

DO LANGUAGE plpgsql $$
  DECLARE
    start_timestamp timestamp without time zone := '2023-12-01 00:00:00';
    end_timestamp timestamp without time zone := '2023-12-01 01:00:00';
  BEGIN
    WHILE start_timestamp < end_timestamp LOOP
      EXECUTE 'INSERT INTO "DataRecords" (filename,timestamp,data,created_at,updated_at) VALUES (''test_file.dat'',' || quote_literal(to_char(start_timestamp,'YYYY-MM-DD HH24:MI:SS')) || ',''{<json data goes here>}'',NOW(),NOW()) ON CONFLICT (filename,timestamp) DO UPDATE SET data = excluded.data, updated_at = NOW()';
      start_timestamp := start_timestamp + '1 minute'::INTERVAL;
    END LOOP;
  END;
$$;

SELECT max(pg_column_size(data)) FROM "DataRecords";

Please let me know if this should get posted somewhere differently.

Kind regards, Sean

  • We have opened up a support ticket with AWS regarding this issue. They have been able to reproduce the issue. Currently, waiting on additional information from them.

Sean
asked 4 months ago270 views
1 Answer
0
Accepted Answer

Resolution from the AWS support ticket:

The change in storage size of jsonb data type columns is caused by different toast compression methods. Since Postgres 15, default toast compression method used in RDS is 'lz4', while RDS Postgres 14 uses 'pglz'.

If you want to use a different compression method for individual columns, you can specify the compression method by setting the COMPRESSION column option in 'CREATE TABLE' or 'ALTER TABLE' commands [1].

Alternatively, you could modify your parameter group [2] to use a different default toast compression method [3] by modifying the parameter “default_toast_compression”.

Sean
answered 4 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions