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
preguntada hace 5 meses303 visualizaciones
1 Respuesta
0
Respuesta aceptada

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
respondido hace 5 meses

No has iniciado sesión. Iniciar sesión para publicar una respuesta.

Una buena respuesta responde claramente a la pregunta, proporciona comentarios constructivos y fomenta el crecimiento profesional en la persona que hace la pregunta.

Pautas para responder preguntas