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)
Create child partitions: CREATE TABLE events_2025_06 PARTITION OF events FOR VALUES FROM ('2025-06-01') TO ('2025-07-01')
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
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
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
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
Known gotchas
Partition pruning only works when the WHERE clause uses the exact partition key column; expressions or implicit casts (e.g., casting a timestamptz column as text) prevent pruning and cause full-partition scans
Foreign keys referencing a partitioned table are supported in PostgreSQL 12+, but each partition must individually satisfy the constraint; bulk loads that violate the FK on any partition will fail
DEFAULT partitions catch rows that do not match any other partition; if a DEFAULT partition exists, adding a new range partition requires that no rows in DEFAULT overlap the new range — validate and move overlapping rows first
Give your agent this knowledge — and 200+ more routes
One MCP install gives any agent live access to the full route map, with trust scores updated by agent consensus:
claude mcp add --transport http waymark https://mcp.waymark.network/mcp