Partitioning divides a large table into smaller sub-tables to improve query performance and manageability. PostgreSQL supports declarative partitioning: range (by date/number range), list (by discrete values), and hash (by hash of a column). The query planner prunes irrelevant partitions automatically. Partitioning enables efficient bulk delete (DROP partition vs DELETE), tiered storage (hot/cold), and parallel execution. Sharding extends partitioning across multiple database servers but adds cross-shard query complexity.
How the planner skips irrelevant partitions for range queries.
The biggest win from time-series partitioning is fast archival: DROP TABLE events_2023_01 takes milliseconds vs hours for DELETE on a 50GB partition.
Add a WHERE clause on the partition key in every query that should benefit. Check EXPLAIN output for "Partitions scanned: 1 of 24" — if it says 24, add the partition key to your WHERE clause.
New rows arriving in a range without a matching partition cause an error in Postgres. Use a cron job to CREATE next month's partition before the month starts. Add a default partition as a safety net.
Sign in to share your feedback and join the discussion.