Implement declarative table partitioning and ensure partition pruning in PostgreSQL

Steps

1. Create the parent table with PARTITION BY RANGE (or LIST or HASH): CREATE TABLE events (id BIGINT, ts TIMESTAMPTZ, payload JSONB) PARTITION BY RANGE (ts)
2. Create child partitions: CREATE TABLE events_2025_06 PARTITION OF events FOR VALUES FROM ('2025-06-01') TO ('2025-07-01')
3. Create indexes on each partition; in PostgreSQL 11+ indexes on the parent are automatically created on new partitions, but existing partitions must be indexed manually
4. Verify partition pruning is active (enable_partition_pruning defaults to on): run EXPLAIN on a query with a WHERE clause on the partition key and confirm only relevant partitions appear in the plan
5. Automate partition creation with pg_partman or a scheduled function; set up a rolling drop policy to detach and drop old partitions with ALTER TABLE events DETACH PARTITION events_old
6. Use ATTACH PARTITION to add pre-populated partitions with zero downtime; run ANALYZE on the new partition before attaching to give the planner accurate statistics