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.