Under the Hood: Read Replicas vs. Horizontal Sharding
Scaling a relational database follows a predictable operational progression as traffic and data volume expand beyond single-host physical limits:
The Progression of Relational Scaling
Replication Lag & Read-Your-Own-Writes
Adding read replicas offloads read volume. However, because replication is asynchronous, replicas lag behind the primary by tens or hundreds of milliseconds. If a user updates their profile and immediately refreshes the page, routing the read to a replica returns old data.
Read-Your-Own-Writes Pattern: The application tracks the user's latest mutation timestamp. If now() - last_write < replication_lag_threshold (e.g., 2 seconds; a heuristic, see the LSN-based version below), route the read to the primary database; otherwise, route to read replicas.
Horizontal Sharding Challenges
- Cross-Shard Joins: Joining data across shards requires distributed scatter-gather queries in application memory, destroying performance.
- Two-Phase Commit (2PC): Atomic multi-shard transactions add at least one extra network round trip and a forced log write per participant, hold locks for that whole window, and block participants if the coordinator fails.
- Re-Sharding: Doubling shards requires moving terabytes of data over live production traffic without write downtime.
Worked example: when does each rung stop working?
Suppose an app has 30,000 reads/s, 3,000 writes/s, 4 TB of data growing 150 GB/month, and a primary on 32 vCPUs.
- Vertical: moving to 96 vCPUs and 768 GB of RAM keeps the hot set cached. That buys perhaps 2-3x on reads, less on writes if WAL fsync or lock contention is the limit.
- Replicas: 4 replicas at about 10k reads/s each absorb reads easily. Writes are unchanged, because every replica must replay all 3,000 writes/s.
- Functional split: moving the analytics/events tables (60% of writes) to their own cluster or a columnar store leaves the primary at about 1,200 writes/s.
- Sharding: needed only if single-primary writes, storage (for example over 10-20 TB per node, where backups and restores take too long), or blast radius become the limit.
Trade-off table
| Technique | Scales | You lose | Typical operational cost |
|---|---|---|---|
| Vertical scaling | Everything, up to hardware limits | Nothing, except cost and one big failure domain | Low |
| Read replicas | Reads | Read-your-writes on replicas | Low-medium: lag monitoring, routing |
| Caching | Hot reads | Freshness; invalidation complexity | Medium |
| Table partitioning (same node) | Maintenance: vacuum, index size, retention drops | Some cross-partition uniqueness | Low-medium |
| Functional split | Writes per domain | Cross-domain joins and transactions | Medium |
| Horizontal sharding | Writes and storage, near-linearly | Cross-shard joins, global constraints, simple transactions | High: routing, resharding, schema changes on N shards |
Read-your-writes, done precisely
A time threshold ("route to the primary for 2 s after a write") is a heuristic. A precise version records the primary's WAL position after the write (pg_current_wal_lsn()) in the user's session, and reads from a replica only if its pg_last_wal_replay_lsn() is at or beyond that position. MySQL offers the equivalent with GTIDs (WAIT_FOR_EXECUTED_GTID_SET).
How real companies climbed the ladder
- Figma: vertical partitioning by table groups first, then horizontal sharding with a custom proxy (DBProxy), keeping Postgres.
- Notion: sharded Postgres by workspace ID into 480 logical shards on 32 physical hosts, later expanded to 96.
- Shopify: "pods" that each hold a set of shops in their own MySQL, and moves shops between pods to fix hotspots.
- Instagram: thousands of logical shards mapped onto fewer Postgres servers, with IDs embedding the shard number.