"PostgreSQL Table Partitioning by Date Range: A Practical Guide"
"postgres partitioning by date range with native declarative partitioning: monthly range partitions on created_at, EXPLAIN partition pruning, PK gotchas, and migrating existing tables without downtime."
Your events table just crossed 40 million rows, the nightly analytics queries crawl, and you're wondering whether it's time to try postgres partitioning by date range . Native declarative partitioning splits one logical table into monthly (or daily) physical partitions on created_at , so queries that filter on recent dates only scan the partitions they need — and dropping last year's data becomes a metadata operation instead of a week-long DELETE . This guide shows the full recipe: declaring the partitioned table, creating range partitions, proving pruning works with EXPLAIN , and the gotchas that break production migrations. Last updated: October 4, 2026 What you'll learn When partitioning actually helps — and when an index is the better answer Declaring a partitioned table with PARTITION BY RANGE on created_at Creating monthly range partitions with FOR VALUES FROM ... TO ... Proving partition pruning works using EXPLAIN The golden rule: your primary key must include the partition key Migrating an existing non-partitioned table with ATTACH PARTITION Postgres Partitioning by Date Range: What Native Partitioning Actually Is Partitioning doesn't make Postgres faster at reading one row. What it does is let the planner skip entire partitions when your WHERE clause filters on the partition key — that's called *partition pruning*. If a query asks for the last 7 days and your table has 60 monthly partitions, Postgres reads 2 of them and ignores the rest. The wins are: faster time-range scans, cheap old-data retention ( DROP / DETACH a partition instead of deleting rows), and smaller indexes per partition so maintenance is cheaper. Do not partition just because a table feels big. Rule of thumb: partitioning pays off at tens of millions of rows (or…