Vertical and horizontal partitioning
Two ways to cut up data that has grown too large, and why spreading it over machines is the last resort.
5 minute lesson
Two ways to cut a table
Replication copies data. It does not make any one copy smaller. When a table, or the writes to it, outgrow what one machine handles, the data itself has to be divided. That is partitioning, and a table can be cut in two directions.
- VerticalSplit the columnsdifferent kinds of data go to different places
- HorizontalSplit the rowsthe same kind of data is divided by a key
Vertical: by what the data is
- One databaseusers and orders
- Two databasesusers | orders
- By feature: users in one database and orders in another, each on its own machine.
- Within a table: large, rarely read columns, such as a profile text, move to a table of their own. The rows that are read all the time stay small.
A team moves all tables of its product catalogue out of the main database into a database of their own, on another machine. The other tables stay.
What kind of partitioning is that?
- Vertical: a different kind of data moves out
- Horizontal: rows are divided up by a key
- Replication: the catalogue is copied over
- Sharding: one table is spread over machines
Show the answer
- Vertical: a different kind of data moves out
Whole tables, a whole kind of data, move to another place. No table is divided, and nothing exists twice. That is a vertical split. A horizontal split would keep one table and distribute its rows, for instance products A to M on one machine and N to Z on another.
Vertical: different data to different places. Horizontal: the rows of one table divided by a key.
Horizontal: by which row it is
Horizontal partitioning keeps the table's shape and divides its rows by a key: orders of 2024 here, orders of 2025 there. It comes in two strengths.
- Inside one databasePartitions of a tablesmaller indexes, and old data is dropped in an instant
- Across machinesShardsmore capacity for writes, and every cost below
Each machine that holds a part is a shard. Sharding is the only way to scale writes beyond one machine. A query, a join or a transaction that touches two shards becomes slow, limited or impossible.
A log table is partitioned by month inside one database. Rows older than a year have to go, which is 400 million rows every month.
What is the cheapest way to remove them?
- Drop the partition of the month that has expired
- Delete the rows one by one with a filter on date
- Copy the newer rows into a new and empty table
- Move the old rows to a follower and keep them
Show the answer
- Drop the partition of the month that has expired
A partition is a table of its own underneath. Dropping it removes a month of rows in one quick step, without touching them one by one. A DELETE of 400 million rows would write for hours and leave dead versions for clean-up. This is one of the main reasons to partition by time, even on a single machine.
Dropping a partition removes its rows in an instant. Partition by time to expire old data cheaply.
Shard last
- A bigger machine, and better queries and indexes.
- Followers for the reads, and a cache in front.
- Vertical splits: move whole features to their own database.
- Only then: shard the table that still does not fit.
What is horizontal partitioning, and what changes when the parts are put on separate machines?
Show the answer
The rows of a table are divided by a key, and every part has the same columns. Inside one database this keeps indexes small and lets old data be dropped quickly. Spread over machines it is sharding, which also adds capacity for writes. In exchange, queries, joins and transactions that cross shards become slow or impossible, which is why sharding comes last.
Recap
- Vertical partitioning separates different kinds of data. Horizontal partitioning divides rows by a key.
- Partitions inside one database are cheap. Shards on several machines scale writes and cost joins and transactions.
- Shard last: after a bigger machine, read replicas, caching and vertical splits.