More information needed:
1) Table definition.
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):
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?
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@xxxxxxxxxxx> 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@xxxxxxxxxxx
