Partitioning and sharding a database: when and how to choose

Partitioning a table means splitting it into pieces inside a single database; sharding means spreading the data over several servers. This lesson is for anyone who administers a relational database and wonders whether a growing table should be split. It first shows what really makes a table unmanageable, then the three possible answers in order of cost, the three partitioning axes and what sharding imposes. The running example remains the incident tracker of a server fleet, which grows every day. Sharding appears in it for what it is: a last resort. It applies to every engine, PostgreSQL as well as MySQL.

What you will learn

• Identify what makes a table a problem, beyond its size
• Order by cost scaling up the machine, spreading reads and splitting the data
• Choose a partitioning axis: range, list or hash
• Say what partitioning brings, and what it does not
• Explain why the shard key of a sharding is hard to undo
• Choose between index, partitioning, replicas, cache and sharding on a given case

What are the prerequisites, and where is the SQL written?

You know what a table is, and you have followed Data modeling, whose incident tracker this lesson takes up. Knowing how to read an execution plan helps: Indexes and EXPLAIN (in French) gives the measure of it. This lesson writes no SQL syntax: it teaches how to decide. The hands-on tasks belong to the PostgreSQL and MySQL (in French) sub-trainings.

When does a big table become a problem?

A table becomes a problem when an operation no longer fits in its window, or when its indexes no longer fit in memory, never because of a row count alone. An index search over ten million rows answers in a few milliseconds; a few million rows without a suitable index do not. Imagine 50,000 incidents per day: the table reaches 18 million rows in a year and 36 million in two years, a volume any engine absorbs. Four symptoms should alert you:

• the working set, the share of data and indexes read all the time, exceeds RAM, and every read goes to disk;
• the purge of incidents older than two years deletes millions of rows one by one, which locks rows, writes each deletion to the transaction log and leaves space to reclaim;
• maintenance (backup, index rebuild, verification) lasts in proportion to the volume and ends up overflowing the night;
• writes slow down, because each insertion updates every index.

Before splitting, measure. A missing index gives the same slowness as a table that is too big and is fixed in a minute: it is the first check, together with the execution plan of the slow query.

Scale up the machine, spread the reads or split the data?

There are three answers to a saturating database, and they are tried in order of increasing complexity cost. The first gives more resources to the same machine, the second spreads reads over copies of the database, the read replicas, the third splits the data itself. The foundation is the distinction between vertical and horizontal scaling in Availability, durability and scaling (in French): the database scales vertically first, because spreading it touches the consistency of its data.

Answer	What it solves	What it does not solve	Complexity added
Scale up the machine (more memory, faster disks)	working set too big, maintenance too long	purge, ceiling of the biggest machine	low: a planned operation
Spread reads (read replicas)	heavy reports that hinder writes	volume, writes	medium: replication lag to tolerate
Split the data (partitioning, then sharding)	purge (partitioning); volume and writes beyond one machine (sharding)	queries that do not filter on the splitting column	high, and growing

Swipe to see more

A read replica serves reads while the original absorbs writes. It is covered in Replicating, backing up and failing over a database (in French), which you only need to know here as a possible answer. The received idea that relational "does not scale" is outdated: native partitioning, replicas, Citus (an extension that distributes PostgreSQL) and Vitess (a sharding layer for MySQL) exist. Each step nevertheless adds complexity, hence the order.

What is table partitioning?

Partitioning splits a table into pieces called partitions, inside a single database and on a single server, without the application seeing it. The application still queries the incidents table; the engine stores each row in the right partition according to the value of a column, the partition key, and chooses by itself where to read. PostgreSQL speaks of declarative partitioning (MySQL, of user-defined partitioning): you describe the splitting rule, and the engine enforces it. Each partition has its own indexes, therefore smaller than a single index over the whole table.

Which axis to choose: range, list or hash?

The axis is chosen from the queries and the purge, not from the size. The axis is the rule that distributes the values of the partition key among the partitions. Three axes are common in both engines, and the table compares them on the incident fleet.

Axis	Principle	Fleet example	Fits when	Trap
Range	a range of values per partition	one month of opening dates per partition	dated data, purge by age	future partitions must be created before they are needed
List	enumerated values per partition	one site or region per partition	queries always filtered by that criterion	a new value without a partition makes the insertion fail
Hash	a computation on the key spreads the rows	the server identifier	balancing the volume without a natural criterion	no purge by partition, no useful range query

Swipe to see more

For incidents, range on the opening date is the obvious choice: you mostly query recent data, and you purge by age. Hash only seeks to spread evenly, at the price of the purge by partition and of range queries.

What does partitioning bring, and what does it not?

Partitioning brings two precise gains, fast purge and pruning, and it brings neither spreading over several machines nor the speeding up of every query. The first gain: deleting the incidents of an old month amounts to detaching or dropping a whole partition, an operation on the structure and not on the rows, far faster than deleting millions of rows one by one. The second: when a query filters on the partition key, the engine ignores the other partitions, this is partition pruning, and reads one month rather than two years.

Here is what it does not bring and the limits that are often discovered too late:

• a query that does not filter on the key scans all partitions, and can become slower than before;
• everything stays on one machine: same processor, same disks, same point of failure;
• uniqueness constraints, primary key included, must include the partition key (rule of both engines; MySQL exempts only a table with no unique key at all);
• MySQL 8.4 with InnoDB forbids any foreign key on a partitioned table, whether it carries one or is referenced;
• a large number of partitions makes query planning heavier: PostgreSQL states that it handles up to a few thousand partitions, provided pruning leaves only a few of them, and MySQL caps at 8192 partitions per table, subpartitions included.

What is sharding, and why does it change everything?

Sharding spreads the rows of a table over several servers: each server, or shard, hosts a slice of the data and works as a complete database. The shard key decides which row goes to which shard. A routing layer (Vitess for MySQL, Citus for PostgreSQL, or the application code) sends each query to the right shard. Everything that was local becomes a conversation between machines across the network: that is what changes everything.

In the diagram below, look at partitioning at the top: the application sees only one table, stored in three partitions inside a single server. At the bottom, sharding: a router reads the key and directs the query to one database server out of three, each keeping the incidents of a slice of the monitored machines ("Servers 1 to 500", and so on). The border between the two is the machine.

Source · SVG

Esc to close, + and − to zoom, scroll to move around. 100 % − Fit + Close

Why is the shard key a structuring decision?

The shard key fixes which queries stay local to a shard and which cross all the servers: you choose it once, and change it at great cost. For incidents, three choices give three different lives:

• the identifier of the monitored machine keeps "the incidents of web-01" on a single shard, but "all incidents open today" queries every shard;
• the opening date makes ranges easy, but sends all the writes of the moment to the same shard, a hotspot that saturates while the others sleep;
• the hash of the incident identifier balances the volume, but serves no range query.

Changing the key imposes a resharding: redistributing the data between servers, in production, while the application keeps writing. Tools like Vitess equip it, they do not make it free. And every query of the application was written with the key in mind: changing it amounts to rewriting part of the code.

What do queries and transactions spanning several shards cost?

Any operation that touches several shards costs a network round trip per shard, and waits for the slowest. Four costs come back:

• a query without the key is sent to all shards, then the results are gathered: its duration is that of the slowest shard;
• a join between data from different shards brings the rows back across the network, or requires copying the small reference tables everywhere;
• uniqueness and foreign keys are no longer checked automatically at the scale of the database: referential integrity becomes in practice the application's business;
• a transaction over two shards requires a two-phase commit protocol (the shards first vote, then commit together), slow and fragile if the coordinator goes down; otherwise you accept eventual consistency, presented in Synchronous, asynchronous, eventual consistency: cloud patterns (in French).

On top comes the operating cost: N servers to back up, monitor and update, and a backup that must remain consistent from one shard to the other. Hence the rule: you shard only when a single machine, already scaled up, partitioned and backed by replicas, is no longer enough, typically for writes or volume. Replicas only relieve reads.

Can a cache replace splitting the data?

No: a cache speeds up repeated reads of the same data, but relieves neither writes, nor the purge, nor the volume, and it can serve stale data. It keeps in memory, often in Redis or Valkey, the result of the most frequent queries. It helps a dashboard viewed a thousand times a minute; it does not solve a database saturated with inserts. Cache-aside, lifetimes (TTL), invalidation and the failures of a cold cache are covered in Cache: what it speeds up, what it makes wrong, what it breaks (in French), a reference page that is not repeated here.

How to choose: which symptom, which answer?

You choose by starting from the measured symptom, never from the fashionable technique, and you move up a step only when the previous one is exhausted. The table lists common cases, roughly from the simplest to the heaviest.

Measured symptom	First answer	Why not heavier
"The incidents of a server" display slowly	a suitable index, checked with EXPLAIN	the plan shows the cause before any splitting
Too many simultaneous connections on the database	a connection pool (an intermediary that reuses a few open connections) like PgBouncer	the bottleneck is the number of sessions, not the data
The working set exceeds memory	a bigger machine	a single operation, no rewriting
The same dashboard is read constantly	a cache	the data changes little, the read repeats
Monday's reports hinder incident handling	a read replica for the reports	the problem is reading, not volume
The monthly purge takes hours	range partitioning on the date	detaching or dropping a partition replaces millions of row deletions
Very large series of dated measurements	a time-series extension like TimescaleDB	it automates partitioning by time
Writes exceed what the biggest machine absorbs	sharding	none of the previous answers holds any more

Swipe to see more

PgBouncer, TimescaleDB, Vitess, Redis and Valkey have their entry in the site's tool catalog (in French). Keep this order: measure, index, scale up the machine, spread the reads, partition, and only then shard. A skipped step is paid for in complexity that nothing justified.

Key takeaways

• A table is a problem by what it prevents (purge, memory, maintenance, writes), not by its row count: measure first.
• The three answers, by increasing complexity: scale up the machine, spread reads with replicas, split the data.
• Partitioning splits a table inside a single database, transparently for the application; the common axes are range, list and hash.
• Its benefits are purge by partition and pruning; it brings neither spreading over several machines, nor speed-up of queries that do not filter on the key.
• Sharding spreads the data over several servers: it is a last resort, which changes operations and code.
• The shard key is a structuring decision: changing it requires a resharding in production.
• Queries without the key, joins and transactions across shards cost a lot; global uniqueness and foreign keys become in practice the application's business.
• A cache relieves repeated reads but solves neither writes nor size, and may serve stale data.

Knowledge check

Eight questions check what makes a big table a problem, the order in which remedies are tried, the choice of a partitioning axis and what partitioning does not bring. They also cover the shard key of a sharding, the cost of a query without the key, the role of a cache and the choice of a replica for a given symptom.

Knowledge check

Check what you have learned with this interactive quiz

8 questions

8 min

70% to pass

How it works

• The timer starts when you click Start
• Multiple choice, true/false and short answer questions
• You can move between questions
• Detailed results are shown at the end

Start the quiz

Starts the quiz and the timer

×

See in the glossary →