Partitioning has a way of making a team feel safe before it makes anything fast. We split the events table by month, finished the migration, congratulated ourselves, and watched the dashboard stay exactly as slow as before.
That's when the uncomfortable truth showed up: partitions only help when PostgreSQL can avoid most of them. If the planner can't prove which partitions a query needs, it reads them all, and you've added complexity for no speed.
Pruning needs the partition key in the query
Our table was partitioned by created_at, but the slow dashboard query filtered by user_id and never mentioned created_at. With nothing to prune on, the planner scanned every monthly partition. Partitioning hadn't failed; we just weren't giving it the one thing it needs.
The tell is right there in the plan: count the partitions under the Append node. A handful means pruning worked. All of them means it didn't.
-- Prunes to one partition: created_at is in the predicate
EXPLAIN SELECT * FROM events
WHERE created_at >= '2026-06-01' AND created_at < '2026-07-01';
-- Prunes nothing: no partition-key predicate
EXPLAIN SELECT * FROM events WHERE user_id = 42;Design queries and keys together
Once I understood that, the fix was to make the common queries carry the partition key. Sometimes that meant adding a date range the application already knew but wasn't sending. Sometimes it meant rethinking whether created_at was even the right partition key for how we actually queried the data.
Partitioning is a deal you make with your query patterns. If the two don't agree, the planner can't help you.
Don't partition before you need to
The other lesson was humility about reaching for partitioning at all. A table with tens of millions of rows and a good index often doesn't need it. Partitioning earns its keep for retention, maintenance windows, and pruning on a key you genuinely filter by — and at the coarsest granularity that still serves those goals.
- Put the partition key in the WHERE clause so the planner can prune.
- Check the Append node: few partitions good, all partitions bad.
- Choose a partition key that matches how you actually query.
- Prefer coarse partitions; don't partition pre-emptively.
What I keep coming back to
None of this is clever. Name the failure mode, capture the before number, change one thing, and compare the same signal afterward. The boring version of database work is the version that actually holds up at 2 AM.