Partitioning
Splitting a large table or dataset into smaller pieces — horizontally by rows or vertically by columns — to improve manageability and performance, often within a single system.
By Ratish Jain · Updated
Definition
Partitioning is the act of dividing a large logical table or dataset into smaller, more manageable pieces called partitions. It comes in two flavors: horizontal partitioning splits by rows (each partition holds a subset of rows), and vertical partitioning splits by columns (each partition holds a subset of columns).
Partitioning is the general concept; sharding is specifically horizontal partitioning where the partitions live on separate nodes.
Why It Exists
Very large tables become slow and unwieldy: index maintenance is costly, full scans are expensive, and dropping old data means huge DELETE statements. Partitioning lets the engine work on one small partition instead of the whole table.
Vertical partitioning exists for a different reason: separating hot, frequently-read columns from large, rarely-accessed ones (like blobs or text) keeps the hot rows compact so more of them fit in a memory page and the cache.
How It Works
Horizontal partitioning uses a partition strategy: range (by date or ID bucket), hash (even distribution), or list (by discrete values like region). The database routes each row to a partition based on the partition column, and queries with that column in the predicate skip irrelevant partitions — this is partition pruning.
Vertical partitioning splits a wide table into narrow ones joined by a shared primary key. Crucially, partitioning can be local (all partitions on one server, as in Postgres declarative partitioning) or distributed across nodes, at which point it becomes sharding.
What It's Used For
- Time-series tables partitioned by day or month for cheap archival and pruning.
- Dropping expired data instantly by detaching an old partition instead of deleting rows.
- Separating rarely-read large columns (images, JSON blobs) from hot columns.
- Improving query performance via partition pruning on the partition key.
- Acting as the first step toward sharding when a single node still suffices.
Tradeoffs
- Faster queries and index maintenance on smaller partitions.
- Cheap bulk data lifecycle — attach/detach partitions instantly.
- Vertical splits keep hot rows compact and cache-friendly.
- Queries not filtered on the partition key must scan every partition.
- Poorly chosen partition boundaries cause skew and hot partitions.
- Vertical partitioning adds joins to reassemble full rows.
- Local partitioning alone does not scale beyond one server's capacity.
When to Use / Avoid
- Use when
- A table is large, queries filter on a natural key (time, region), or you need cheap rolling data retention.
- Avoid when
- The table is small, or most queries don't include the partition key and would scan all partitions anyway.
- Real-world
- Postgres declarative partitioning by month; TimescaleDB chunks; MySQL RANGE partitioning for log tables.
Interview Questions
Partitioning is the general idea of splitting a dataset into pieces; it can be local to one node. Sharding is horizontal partitioning where those pieces (shards) are distributed across separate machines to scale beyond one node. All sharding is partitioning, but not all partitioning is sharding.
When a table has a mix of small hot columns and large cold columns (blobs, long text). Splitting the cold columns into a separate table keeps the hot rows small and dense, so more fit per page and in cache, speeding up the common queries at the cost of a join for the rare full-row read.
- →Partitioning splits a dataset: horizontal by rows, vertical by columns.
- →Strategies: range, hash, and list; queries benefit from partition pruning.
- →Can be local (one node) or distributed — distributed horizontal partitioning is sharding.
- →Vertical partitioning separates hot from cold columns for cache efficiency.
- →Great for data lifecycle: detach old partitions instead of mass deletes.
Related
Everything on Archprimer is connected — follow the graph.