Partitioning

Why it matters

A table with five billion rows is slow to index, slow to vacuum, impossible to delete from efficiently, and its indexes no longer fit in memory. Partitioning splits it into manageable pieces while the application still sees one table. It is the step that keeps a single database useful long after a table has outgrown the naive layout, and it is the step people confuse with sharding, which is a far bigger commitment.

The idea

Partitioning divides a table into physical pieces (partitions) according to a rule on a column, within one database instance. Queries that include the partition column touch only the relevant partitions (pruning); maintenance operates per partition; old partitions are dropped in one metadata operation.

Strategy	Rule	Fits	Watch for
Range	Rows fall into ranges of a value: one partition per month of created_at	Time-ordered data, retention by age, queries by time window	All inserts land in the newest partition (fine, but it is the hot one)
List	Explicit values map to partitions: region = 'eu' | 'us' | 'apac'	Data with a small set of natural groups; regulatory separation	Uneven groups; a new value needs a new partition
Hash	hash(key) mod N decides the partition	Spreading a large table evenly when there is no natural range	No pruning for range queries; changing N means rewriting
Composite	Range then hash, or list then range	Time-partitioned data that also needs spreading	Complexity; many small partitions

What partitioning buys

Pruning: a query with the partition key reads a fraction of the data and uses a fraction-sized index. Cheap retention: dropping a month is instant and leaves no bloat, while deleting a month of rows from an unpartitioned table takes hours and fragments everything. Maintenance locality: vacuum, reindex and statistics run per partition, and the hot partition stays small enough to live in memory. Archival: an old partition can be detached and moved to cheaper storage.

What it does not buy

More write capacity: all partitions live on the same instance, share the same CPU, memory, disk and write-ahead log. Partitioning is about organising data, not about adding machines. When one instance cannot absorb the writes, the answer is replication for reads and sharding for writes, both later in this module.

Pitfalls

Queries that omit the partition key scan every partition, often slower than the unpartitioned table because each partition's index is consulted separately. Unique constraints must include the partition key, which complicates "email must be unique" on a table partitioned by date. Too many partitions (tens of thousands) slow planning. Hash partitioning spreads writes evenly but defeats pruning for ranges, so pick it only when there is no natural range.

Vertical partitioning

A different cut: splitting a wide table's columns into two tables, the hot, small columns in one and the rarely read large ones (a JSON blob, a description) in another, so the hot table stays compact and cache-friendly. Same instance, same data, better locality for the common query.

Trade-offs

Partitioning buys pruning, instant retention and manageable maintenance for very large tables, and pays with constraints on uniqueness, planning overhead and queries that must carry the partition key to benefit. It extends the life of a single database by a lot and adds no capacity for writes, which is both its strength (low risk) and its limit.

In an interview

Distinguish it from sharding out loud: "The events table is range-partitioned by month inside Postgres: queries carry a time range so they prune to one or two partitions, retention drops the oldest partition nightly, and indexes stay small. That is organisation within one instance; if write volume outgrows the instance we shard by tenant across instances, which is a separate step." Interviewers reward the distinction because it is so often blurred.