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
已提问 5 个月前319 查看次数
1 回答
0
已接受的回答

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
已回答 5 个月前

您未登录。 登录 发布回答。

一个好的回答可以清楚地解答问题和提供建设性反馈,并能促进提问者的职业发展。

回答问题的准则

相关内容