Database scaling

How read replicas go stale

Reads scale out with replicas; writes still land on one primary (scaling-basics covers that lever). What senior interviews probe is the consistency you traded away. Default replication is asynchronous: the primary commits and acks the client before any replica applies the change, so a replica trails by milliseconds to seconds. Read right after a write and you can break read-your-writes consistency: a user edits their profile, the next page load shows the old value.

Synchronous replication makes the primary wait for a replica to acknowledge before it acks the client, buying consistency with write latency. The trap is what acknowledge means here. MySQL semi-sync acks once the replica has the event in its relay log, not once it has applied it, so a semi-sync replica can still serve a stale read. Postgres only closes that gap at synchronous_commit = remote_apply:

synchronous_commit = remote_apply       # standby has APPLIED before primary acks
synchronous_standby_names = 'FIRST 1 (r1, r2)'

When full sync is too expensive, cheaper read-your-writes fixes exist: route a user's reads to the primary for a few seconds after their write, pin a session to one replica, or record the write's log position (LSN, GTID) and wait for the replica to reach it.

Choosing a shard key

Sharding spreads writes across independent nodes. Partitioning splits one table inside a single node; do not conflate them. The shard key decides everything downstream.

Wrong "Shard by an auto-increment id or a timestamp so rows spread out." Monotonic keys pile every new write onto the newest shard, so one shard runs hot while the rest sit idle. Hashing the key spreads writes evenly but scatters adjacent keys, so range scans fan out. Range sharding keeps scans local and reinvites the hotspot. Directory sharding puts a lookup table between key and shard for flexible rebalancing, paid for with a lookup on the critical path that must stay highly available.

The criterion that matters is your access pattern. Pick a key with high cardinality and even distribution that co-locates the rows your hot queries read together, so the common read stays on one shard.

The cross-shard tax

Any query that filters on something other than the shard key fans out to every shard and merges the results, a scatter-gather. Ordering, pagination, and aggregates across shards are where the correctness bugs live: OFFSET does not push down per shard, so naive paging returns the wrong rows. Cross-shard transactions are worse: two-phase commit blocks on a coordinator that can die mid-commit, so teams design to keep a transaction inside one shard or fall back to sagas.

This is why sharding is a last resort. Exhaust indexing, caching, read replicas, and vertical headroom first. Sharding costs you cross-shard joins, global unique constraints, referential integrity, and simple schema migrations, permanently.