Re: Need guidance on partioning

[Ron Johnson <ronljohnsonjr@xxxxxxxxx>]

On Wed, May 22, 2024 at 8:50 AM Muhammad Salahuddin Manzoor <salahuddin.m@xxxxxxxxxxx> wrote:

Dear Srinivasan,

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.