Hi,
We have an application currently running on PostgreSQL 12, partitioned as shown below. Database size is approx. 1.4TB. We are now in the process of upgrading the application, using PostgreSQL 15. In the new version, there is an optional step to implement 'primary key' on history table.
In the documentation, there is no information/procedure describing how to do this on a partitioned PostgreSQL database.
There are two questions related to this:
1: Is partitioning considered best options to deal with cleanup of large chunks of historical data in PostgreSQL 15?
2: Based on findings so far, it seems like the solution is to:
- create script to create temporary tables for main table and partitions (identical to original tables)
- add primary key and all other required dependencies to the temporary tables
- copy the data back to main table (not each partition)
Does this seem correct? Any obvious pitfalls? Anyone else who has experienced similar cases?
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 40;
relname | relpages
----------------------------+----------
events events_1
events
events_pkey
event_recovery_2
housekeeper
event_recovery_pkey
event_recovery_1
items
sessions_pkey
event_recovery
sessions_1
housekeeper_pkey
trends_uint_p2023_02
history_uint_p2023_08_17
trends_uint_p2023_06
trends_uint_p2023_01
trends_uint_p2023_07
history_uint_p2023_08_18
history_uint_p2023_08_19
alerts
history_uint_p2023_08_20
history_uint_p2023_08_16
history_uint_p2023_08_17_1
history_uint_p2023_08_15
history_uint_p2023_08_19_1
history_uint_p2023_08_18_1
history_uint_p2023_08_20_1
history_uint_p2023_08_16_1
trends_uint_p2023_08
trends_uint_p2023_05
trends_uint_p2023_02_1
history_uint_p2023_08_10
history_uint_p2023_08_15_1
history_uint_p2023_08_08
trends_uint_p2023_01_1
history_uint_p2023_08_09
trends_uint_p2023_06_1
history_uint_p2023_08_12
history_uint_p2023_08_13
(40 rows)
relname | relpages
----------------------------+----------
events events_1
events
events_pkey
event_recovery_2
housekeeper
event_recovery_pkey
event_recovery_1
items
sessions_pkey
event_recovery
sessions_1
housekeeper_pkey
trends_uint_p2023_02
history_uint_p2023_08_17
trends_uint_p2023_06
trends_uint_p2023_01
trends_uint_p2023_07
history_uint_p2023_08_18
history_uint_p2023_08_19
alerts
history_uint_p2023_08_20
history_uint_p2023_08_16
history_uint_p2023_08_17_1
history_uint_p2023_08_15
history_uint_p2023_08_19_1
history_uint_p2023_08_18_1
history_uint_p2023_08_20_1
history_uint_p2023_08_16_1
trends_uint_p2023_08
trends_uint_p2023_05
trends_uint_p2023_02_1
history_uint_p2023_08_10
history_uint_p2023_08_15_1
history_uint_p2023_08_08
trends_uint_p2023_01_1
history_uint_p2023_08_09
trends_uint_p2023_06_1
history_uint_p2023_08_12
history_uint_p2023_08_13
(40 rows)
Best regards,
Arne Segtnan
