Partition a large PostgreSQL table with declarative range partitioning

Documented steps

1. Create the parent: CREATE TABLE measurement (city_id int NOT NULL, logdate date NOT NULL, peaktemp int) PARTITION BY RANGE (logdate);
2. Create partitions with bounds (upper bound exclusive, so adjacent partitions share a boundary value): CREATE TABLE measurement_y2026m01 PARTITION OF measurement FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
3. Optionally add a catch-all: CREATE TABLE measurement_default PARTITION OF measurement DEFAULT; — rows matching no partition otherwise fail to insert.
4. Create indexes once on the parent: CREATE INDEX ON measurement (logdate); — the index is automatically created on every existing and future partition.
5. Attach a pre-loaded table as a partition: ALTER TABLE measurement ATTACH PARTITION staging_2026_03 FOR VALUES FROM ('2026-03-01') TO ('2026-04-01'); — first add a CHECK constraint on the staging table matching those bounds to skip the validation scan.
6. Archive old data cheaply: ALTER TABLE measurement DETACH PARTITION measurement_y2026m01 CONCURRENTLY; then dump/drop the detached table.
7. Unique and primary key constraints on a partitioned table must include all partition key columns, e.g. PRIMARY KEY (city_id, logdate).
8. Official docs: https://www.postgresql.org/docs/current/ddl-partitioning.html