Replication and sharding: how to scale a database

Scale a database in steps: optimise queries and indexes first, then get a bigger server, then move reads to replicas, and only as a last resort split data into shards. Most products can live on the first three steps for years, while sharding sharply complicates development and operations.

Here is how replication works, its pitfalls, what partitioning and sharding are and when to move to them.

When does the database become the bottleneck?

The database becomes the bottleneck when query times, CPU and disk load on the database server grow and the connection queue cannot keep up. Before scaling, check whether the problem has a cheaper fix.

The order of steps:

1. Query optimisation. Slow queries without indexes, N+1 problems and unnecessary reads are common causes of load; see PostgreSQL indexes.
2. Caching. Put frequently read data in a cache — see caching web applications.
3. Connection pooling. Thousands of connections from the application overload the database more than the queries do.
4. Vertical scaling. More CPU, memory and fast disks — the simplest step.
5. Read replicas. When most of the load is reads.
6. Sharding. When writes cannot keep up or data does not fit on one server.

How does replication work?

Replication copies changes from the primary database server to one or more additional ones. The primary accepts writes; replicas receive a stream of changes and apply it. Reads can be served by replicas, offloading the primary.

Task	How replication helps
Read load	Reports, catalogues and search read from replicas
Fault tolerance	If the primary fails, a replica is promoted
Analytics	Analysts' heavy queries do not affect users
Maintenance	Upgrades are done one server at a time, without downtime

Photo: Unsplash

What problems does replication lag cause?

A replica lags behind the primary by milliseconds — and by seconds under load. If a user saves their profile and the next page reads from a replica, they may see old data and think the save failed.

How to deal with it:

• after a write, read that user's data from the primary for a short time;
• run critical checks — stock, balances, permissions — only on the primary;
• monitor replication lag and take a lagging replica out of rotation;
• do not use replicas inside transactions that later write data.

// Laravel: after a write within the request, reads go to the primary
'mysql' => [
    'read'   => ['host' => ['replica-1', 'replica-2']],
    'write'  => ['host' => ['primary']],
    'sticky' => true,
],

What are partitioning and sharding?

Partitioning splits a large table into parts on one server — by month, for example. Sharding distributes data across different servers — by customer ID, for example. Partitioning barely affects the application; sharding affects it a lot.

What sharding complicates:

• queries and reports across all data must be gathered from several servers;
• transactions across shards are practically unavailable;
• rebalancing data when adding a shard is a complex project in itself;
• schema migrations, backups and monitoring multiply by the number of shards.

A good natural split is by customer in B2B SaaS, where customers' data barely overlaps. See multi-tenancy in SaaS.

When should you move to sharding?

Move to sharding when one server cannot handle the write load even after optimisation and maximum vertical scaling, or when the data does not fit on the largest available server. For most products that moment never comes.

Before deciding:

• gather statistics on what loads the database — reads or writes;
• check whether partitioning and archiving old data would help;
• consider moving analytics to a separate store — see ClickHouse for analytics;
• choose a shard key that sends almost every query to a single shard.

In short

• Scale in steps: queries, cache, a bigger server, replicas, shards.
• Replicas offload reads and add fault tolerance, but lag behind the primary.
• Read critical data from the primary, especially right after a write.
• Partitioning splits a table within a server; sharding splits data across servers.
• Sharding is a last resort most products never need.