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) Best regards, Arne Segtnan