Implementing partitioning in PostgreSQL can significantly improve the performance of your database
1. Deleting Records Older Than 18 Months
Create the Parent Table: Define your main table as partitioned by range on the timestamp column.
CREATE TABLE your_table ( id SERIAL PRIMARY KEY, timestamp TIMESTAMPTZ NOT NULL, account_id INT, user_id INT, -- other columns ) PARTITION BY RANGE (timestamp);
That's invalid syntax (as of PG 15):
psql (15.7) Type "help" for help.
dba=# dba=# CREATE TABLE your_table ( dba(# id SERIAL PRIMARY KEY, dba(# timestamp TIMESTAMPTZ NOT NULL, dba(# account_id INT, dba(# user_id INT dba(# ) PARTITION BY RANGE (timestamp); ERROR: unique constraint on partitioned table must include all partitioning columns DETAIL: PRIMARY KEY constraint on table "your_table" lacks column "timestamp" which is part of the partition key.