Partitioning
Splitting a table by range, list, or hash — within one instance or across them.
Level: intermediate
What is it?
Partitioning splits one logical table or index into smaller physical partitions according to a key or rule such as range, list, or hash. In many systems the partitions remain within one database instance, unlike sharding across independent nodes.
Why does it matter?
Partitioning can improve maintenance, data lifecycle operations, pruning for selective queries, and manageability of large tables. It does not guarantee faster queries and can complicate unique constraints and partition-key choice.
Mental model
A partitioning rule sends each row to a physical subset. If a query filters on the partition key, the database may skip irrelevant partitions; otherwise it may need to visit many or all of them.
How it works
- Range partitioning groups values such as dates into intervals and can simplify dropping old data.
- Hash partitioning distributes keys across partitions and can reduce skew when the hash distribution is suitable.
- List partitioning maps values to named partitions, often for regions or categories.
- Partition pruning works when the planner can relate query predicates to the partition bounds.
- Partitioning and sharding are distinct: partitioned data may still belong to one DBMS instance and failure domain.
Steps
- Choose a partition key aligned with data lifecycle or query predicates.
- Define bounds/list/hash rules and create representative test partitions.
- Check whether important queries prune partitions in their execution plans.
- Plan indexes, constraints, data movement, and maintenance for growing partitions.
Real-world usage
- Partitioning large event tables by date.
- Dropping expired data by partition rather than row-by-row deletion.
- Reducing scan scope for frequently filtered queries.
Common mistakes
- Partitioning by a key that creates an empty/overloaded hot partition.
- Assuming every query will prune partitions automatically.
- Confusing table partitioning with distribution across multiple independent servers.
Interview questions
What is partition pruning?
The optimizer excludes partitions that cannot contain rows matching the query predicates and partition bounds.
How does partitioning differ from sharding?
Partitioning often divides a table within one database instance; sharding distributes partitions or key ranges across separate nodes or instances.