Replication and Sharding

Replication copies data to more nodes for read scale and failover; sharding splits data across nodes so each holds a part. Replicas bring replication lag; shards need a good shard key and make cross-shard queries harder.

One database server eventually runs out of CPU, memory, connections or disk. Two techniques push the limit: replicate (copies) and shard (split).

flowchart LR
  APP["App"] -->|"writes"| P["Primary"]
  P -->|"async replication"| R1["Read replica 1"]
  P -->|"async replication"| R2["Read replica 2"]
  APP -->|"reads"| R1
  APP -->|"reads"| R2
  APP -->|"customer 0–4999"| S1["Shard A"]
  APP -->|"customer 5000–9999"| S2["Shard B"]
  %% step APP: The application decides where each query goes.
  %% step P: All writes go to one primary, which streams changes to replicas.
  %% step R1: Replicas serve reads, but may be a little behind the primary.
  %% step S1: Sharding splits rows by a shard key, so each shard holds part of the data.
  %% step S2: Each shard is its own primary (often with its own replicas).

Replication

Type	How	Trade-off
Asynchronous	Primary commits, then ships changes	Fast writes; replicas lag; failover can lose the last seconds
Synchronous	Commit waits for at least one replica	No data loss on failover; higher write latency
Multi-primary	Several nodes accept writes	Write scale across regions; conflict resolution needed

Read replicas scale reads, not writes. Replication lag means a user can write and then not see the change when the next read hits a replica. Fixes: read-your-writes from the primary for a short time, or route that user's reads to the primary.

A standby (e.g. RDS Multi-AZ) is a replica used for failover, not for reads.

Sharding (partitioning)

Split one big table across databases by a shard key.

Strategy	Example	Watch out
Range	customer_id 0–4,999 → A	Hotspots on the newest range
Hash	hash(customer_id) % shards or a ring	Range queries need all shards
Directory / lookup	Table maps tenant → shard	The directory is a dependency
Geography	EU users → EU shard	Uneven sizes; compliance benefit

Good shard key: high cardinality, even traffic, and most queries include it (so they hit one shard). customer_id is usually good for a shop; country is usually bad (a few huge countries).

Costs of sharding

Cross-shard joins and transactions become application work; rebalancing when a shard fills up; operations ×N. Delay it: indexes, caching, read replicas and vertical scaling go a long way. Managed options (Aurora Limitless, Vitess, Citus, DynamoDB, Spanner) hide much of the work.

Traps: sending writes to a replica; ignoring lag in "save then reload" flows; sharding by a low-cardinality key; sharding before trying an index and a cache.