🧱Partitioning overview
One logical table, many physical segments
Partitioning splits storage and maintenance units while keeping a single relation for applications, think faster archival, partition pruning on predicates, and smaller vacuum or rebuild scopes. The wrong partition key looks like a normal table that mysteriously scans everything forever.
Declarative range partitions (PostgreSQL)
CREATE TABLE events (
event_id bigserial,
occurred_at timestamptz not null,
payload jsonb not null
) PARTITION BY RANGE (occurred_at);| status |
|---|
| CREATE TABLE (partitioned) |
ðŸ§Common strategies
Match the key to retention and query shape.
| Strategy | Good when |
|---|---|
| Range (often time) | Event logs, telemetry, finance periods, drop or detach old months cleanly. |
| Hash | Even spread when there is no natural time predicate; watch cross-partition aggregates. |
| List | Multi-tenant by region, org, or bounded enum, explicit buckets you name in DDL. |
Partitioning that does not prune
- Partition dimension misaligned with common filters, optimizer touches every child.
- Thousands of tiny partitions, catalog bloat and planner overhead.
- Hot controllers or metadata locks from cross-partition write storms.
Align keys with WHERE and lifecycle
- Validate elimination with
EXPLAINon representative workloads. - Automate creation and archival; document global vs local index behavior for your engine.
- Rebalance when tenant skew shifts which partition owns most rows.
Physical detail
Some engines support partition-wise joins or attach/detach for instant swaps, read vendor notes before relying on a single migration trick across Postgres, MySQL, and cloud warehouses.
TL;DR
Partitioning splits one logical table into physical segments (by range, list, or hash) so maintenance, pruning, and parallelism can target subsets. Queries must include the partition key (or constraints) to prune, otherwise every child is scanned. It is not a magic performance switch: wrong key choice hurts worse than a single monolithic table.