Database Design at Scale

Concept#

Application servers are the easy tier to scale horizontally, as covered in the Scalability topic, making them stateless is the whole trick. Databases are the hard tier, precisely because a database's entire job is to hold state. You can't just spin up three "copies" of a database and round-robin writes across them the way you would with a stateless API server, a write to one copy has to somehow become visible, consistently, everywhere that data might be read from next. Scaling a database means picking a deliberate strategy for exactly this problem, and the right strategy depends heavily on whether your bottleneck is reads, writes, or raw dataset size.

Read replicas: scaling reads by copying data, not splitting it#

The most common first move is read replication: designate one database as the primary (or "leader"), which handles all writes, and create one or more replicas (or "followers") that continuously receive a copy of every change the primary makes. Read traffic gets distributed across the replicas (often via a load balancer, echoing the mechanism from the Scalability topic), while all writes still go to the single primary.

// App reads and writes to ONE database instance
// Reads (90% of traffic) and writes (10%) compete for the same resources

This works well specifically because most real applications are read-heavy, far more requests read data than write it (think of how many times a product page gets viewed versus how many times its price changes). Adding replicas lets you scale read capacity nearly linearly by adding more replicas, without touching how writes work at all.

The cost is replication lag: because replicas receive changes after the primary commits them, there's a small window (often milliseconds, sometimes longer under load) where a replica's data is slightly behind the primary's. This means a client that writes data to the primary and immediately reads it back from a replica might not see their own write yet, a real, observable inconsistency that has to be deliberately designed around (see the CAP Theorem topic for the broader consistency tradeoff this reflects).

Primary (writes)  ──replicates──▶  Replica 1 (reads)
                  ──replicates──▶  Replica 2 (reads)
                  ──replicates──▶  Replica 3 (reads)
 
Read-heavy traffic scales by adding replicas.
Write traffic is UNCHANGED, still bottlenecked by the single primary.

Sharding: scaling writes and dataset size by splitting data#

Read replicas don't help if the bottleneck is write throughput (the single primary is still the only place writes land) or if the dataset itself is simply too large to fit on one machine, no matter how big that machine is. The fix for both is sharding (also called horizontal partitioning): split the dataset itself across multiple independent database instances ("shards"), where each shard holds a different subset of the data, commonly partitioned by a shard key (e.g. userId), so all of a given user's data lives on one specific shard, determined by something like hash(userId) % numberOfShards.

Shard 1: users with hash(userId) % 3 == 0
Shard 2: users with hash(userId) % 3 == 1
Shard 3: users with hash(userId) % 3 == 2
 
Each shard is a FULLY INDEPENDENT database, its own writes, its
own reads, its own capacity. Total write throughput and total
storage capacity now scale with the NUMBER OF SHARDS, not the
capacity of one machine.

Sharding is powerful but introduces real costs that read replication doesn't: a query that needs data from multiple shards (e.g. "find all orders across all users placed today") can no longer be a single simple query, it has to fan out to every shard and merge results in the application layer, which is both slower and more complex than querying one database. Rebalancing shards (adding a new shard as the dataset grows) is also a genuinely hard operational problem, because it usually means physically moving a large volume of data between shards without downtime. Because of this, sharding is generally reached for only once read replication and other single-primary optimizations are no longer enough, it's a bigger architectural commitment, not a default starting point.

Indexing: making individual queries fast before scaling out at all#

Before reaching for replicas or shards at all, the highest-leverage, lowest-cost lever is usually a correct index. A database index is a separate, sorted data structure (commonly a B-tree) that lets the database find rows matching a query condition without scanning every row in the table:

-- Without an index on `email`, this query does a FULL TABLE SCAN, -- checking every single row's email column, one by one.
SELECT * FROM users WHERE email = 'alice@example.com';
 
-- With an index:
CREATE INDEX idx_users_email ON users(email);
-- Now the same query does an INDEX LOOKUP, roughly O(log n) instead
-- of O(n), because the index is a sorted structure the database can
-- binary-search, rather than a linear scan of every row.