English for Postgres Partitioning

“Just partition the table” sounds like a single action, but it involves a partition key choice, a partitioning strategy, and an understanding of pruning — get any of those wrong and the partitioning adds overhead instead of removing it.

Key Vocabulary

Partition key — the column, or set of columns, whose value determines which partition a given row belongs to, chosen because queries and writes are predictably grouped by that value. “We picked created_at as the partition key because almost every query filters by date range — a partition key that doesn’t match the actual query pattern won’t give us any pruning benefit.”

Partitioning strategy — the method used to divide rows among partitions, most commonly range (based on value ranges, like dates), list (based on discrete values, like region codes), or hash (based on a hash of the key, for even distribution). “Range partitioning by month makes sense for time-series data like this, but if we partitioned by customer ID instead, a hash strategy would distribute the write load more evenly than a list strategy would.”

Partition pruning — the query planner’s ability to skip scanning partitions that can’t contain relevant rows based on the query’s filter conditions, the main performance benefit partitioning is meant to deliver. “That query isn’t getting any faster after partitioning because it’s not filtering on the partition key at all — without a filter Postgres can use for pruning, it still has to scan every partition.”

Partition maintenance — the ongoing operational work of creating new partitions ahead of time and dropping or archiving old ones, usually automated, since a full table won’t gracefully handle unbounded partition growth on its own. “Partitioning didn’t fail — partition maintenance did. Nobody set up the job to create next month’s partition, so inserts started failing once we hit the boundary of the last one that existed.”

Attach/detach partition — the operations for adding an existing table as a partition of a partitioned table, or removing one, both of which can be done without rewriting the underlying data, unlike a full table restructure. “We don’t need to migrate this data manually — we can attach the existing table as a partition directly, which is a metadata-only operation and avoids a slow, blocking rewrite.”

Common Phrases

• “What’s the partition key here, and does it actually match our query patterns?”
• “Is this range, list, or hash partitioning, and why that strategy?”
• “Is the query actually getting partition pruning, or is it scanning every partition anyway?”
• “Who owns partition maintenance — is new partition creation automated?”
• “Can we attach this as a partition, or does it need a full data migration?”

Example Sentences

Choosing a partition key in a design review: “I’d push back on partitioning by status — statuses aren’t stable over a row’s lifetime, so rows would need to migrate between partitions constantly. created_at is a better partition key here because it’s immutable and matches how we query.”

Diagnosing a pruning failure: “The query planner isn’t pruning any partitions on this query, even though the table’s partitioned by date — the filter uses a function on the date column instead of a direct comparison, and the planner can’t reason through that to prune partitions.”

Flagging a maintenance gap: “Partitioning this table solves the query performance problem, but only if we also set up partition maintenance — otherwise we’ll be back here in three months when inserts start failing because no partition exists for the current date range.”

Professional Tips

• Choose the partition key based on actual query filter patterns, not just a column that seems like a natural grouping — a mismatched key means partitioning adds complexity without delivering pruning benefits.
• State the partitioning strategy explicitly (range, list, or hash) in design discussions — each has different trade-offs for write distribution and query performance, and the choice should be deliberate, not default.
• Verify partition pruning is actually happening with EXPLAIN, don’t assume it — a query that doesn’t filter directly on the partition key can silently scan every partition, erasing the performance benefit.
• Plan partition maintenance before partitioning goes live, not after — an unmaintained partitioned table fails in a very specific and disruptive way when no partition exists for new data.

Practice Exercise

1. Explain how to choose a good partition key for a table.
2. Describe the difference between range, list, and hash partitioning strategies.
3. Write a sentence explaining why partition pruning might not occur even on a partitioned table.