A user updates their profile and immediately sees the old value. What happened, and how do you fix it?

The write went to the primary and the subsequent read was served by a replica that had not yet applied it - replication lag. The fix is read-your-writes consistency, and there are four standard approaches: route reads to the primary for a short window after a user writes, pin that user's session to the primary, have the client pass the log position it wrote at and wait for a replica to catch up, or make the UI optimistic and render the value it just submitted. Route-to-primary-after-write is the usual pragmatic choice. Where the stale read comes from: User - Submits a profile update, then the page reloads and issues a read a few hundred milliseconds later.. App server - Sends writes to the primary and load-balances reads across replicas - the standard read-scaling setup, and the source of the anomaly.. Primary - accepts writes - Commits the write and acknowledges immediately. With asynchronous replication it does not wait for any replica.. Replica A - lag 40 ms - Has applied the change. A read here would be correct.. Replica B - lag 900 ms - Has not applied it yet - perhaps it is busy with a long analytical query. A read here returns the old value, and the user reports a bug. The four fixes, in order of how often they are the right answer Approach | How it works | Cost. Read from primary after write | For N seconds after a user's write, route that user's reads to the primary | Extra primary load; needs a per-user timestamp, usually in a cookie or Redis. Monotonic / sticky routing | Pin a session to one replica so it never goes backwards in time | Fixes monotonic reads, not read-your-writes; uneven replica load. Log-position token | Client receives the LSN/GTID it wrote at and passes it on reads; replica waits or is chosen | Most precise, but requires client and driver

How do you shard a database, and how do you choose the shard key?

Split data across independent databases by a shard key. Range partitioning keeps ranges together - great for range scans, terrible for hotspots, since recent data all lands on one shard. Hash partitioning distributes evenly but destroys range queries. Directory partitioning uses an explicit lookup table, giving full flexibility at the cost of an extra hop and a component that must not become a bottleneck. Choose a key that is present on almost every query, has high cardinality, and distributes evenly - and accept that queries not containing the key must scatter-gather across every shard. Strategy | How | Pros | Cons. Range | Shard by key ranges (A–F, G–M …) or by time | Efficient range scans; easy to add a shard at the end | Hotspots - today's data all lands on one shard. Hash | hash(key) mod N, or a consistent-hash ring | Even distribution; simple | No range scans; naive mod means resharding moves nearly everything. Directory | Explicit key → shard lookup table | Total flexibility; rebalance individual keys | Extra hop; the directory must be HA and cached. Geographic | Shard by region | Data residency compliance; low local latency | Very uneven load; cross-region queries are painful Choosing the shard key: four tests 1 · Is it on most queries?: If the key is absent, the query must hit every shard and merge results. A scatter-gather is as slow as its slowest shard and scales badly. For a social app user_id usually passes; created_at usually does not.. 2 · Is cardinality high enough?: Sharding by country gives you roughly 200 possible values with wildly uneven populations. You cannot have more shards than distinct key values, and you cannot split a single value across shards.. 3 · Does it distribute evenly?: Check the actual distribution, not the theoretical one. A cel

Database Replication and Sharding Explained

Intermediatereplicationconsistencyread-your-writesAsked at Meta, GitHub

30-second answer

The write went to the primary and the subsequent read was served by a replica that had not yet applied it - replication lag. The fix is read-your-writes consistency, and there are four standard approaches: route reads to the primary for a short window after a user writes, pin that user's session to the primary, have the client pass the log position it wrote at and wait for a replica to catch up, or make the UI optimistic and render the value it just submitted. Route-to-primary-after-write is the usual pragmatic choice.

Where the stale read comes from

The write and the read take different paths. Nothing is broken - this is asynchronous replication behaving exactly as designed.

ClientServiceDatastoreTap a box for detail

Flow

UserApp server
App serverPrimary(write)
PrimaryReplica A(replicate)
PrimaryReplica B(replicate)
App serverReplica B(read (stale!))

The four fixes, in order of how often they are the right answer

Approach	How it works	Cost
Read from primary after write	For N seconds after a user's write, route that user's reads to the primary	Extra primary load; needs a per-user timestamp, usually in a cookie or Redis
Monotonic / sticky routing	Pin a session to one replica so it never goes backwards in time	Fixes monotonic reads, not read-your-writes; uneven replica load
Log-position token	Client receives the LSN/GTID it wrote at and passes it on reads; replica waits or is chosen	Most precise, but requires client and driver support
Optimistic UI	Render the submitted value locally without re-reading	Free and effective for the common case; diverges if the write actually failed

pythonRead-your-writes with a short primary window

WRITE_WINDOW = 10  # seconds - comfortably above observed p99 replication lag

def route_read(user_id):
    last_write = redis.get(f"lw:{user_id}")
    if last_write and time.time() - float(last_write) < WRITE_WINDOW:
        return primary                      # this user only, for a short time
    return pick_replica()

def on_write(user_id):
    redis.setex(f"lw:{user_id}", WRITE_WINDOW, time.time())

# Choose WRITE_WINDOW from measured p99 lag, and alert if lag ever exceeds it -
# otherwise the guarantee silently stops holding.

Synchronous, semi-synchronous, asynchronous

Mode	Primary waits for	Data loss on primary failure	Write latency
Asynchronous	Nothing	Possible - unreplicated commits are lost	Lowest
Semi-synchronous	At least one replica to acknowledge	Very unlikely	+1 network round trip
Synchronous (all)	Every replica	None	Slowest, and one slow replica stalls all writes

Semi-synchronous is the usual production compromise: durable enough to survive losing the primary, without letting the slowest replica dictate write latency.

Say these points

Stale reads after a write are replication lag, not a bug.
Read-your-writes via a short primary-read window is the common pragmatic fix.
Distinguish read-your-writes, monotonic reads and consistent prefix by name.
Alert on replication lag and fail back to the primary when it exceeds your window.
Semi-synchronous replication is the usual durability/latency compromise.

Avoid these mistakes

Sending all reads to replicas without considering the write path.
Assuming lag is always milliseconds.
Using sticky replica routing and calling it read-your-writes.
Failing over to a replica that is behind, and losing acknowledged writes.

Expect these follow-ups

The primary dies with 3 seconds of unreplicated writes. What do you do?
How do you run a schema migration without spiking replication lag?
How does multi-leader replication change this picture?

AdvancedshardingpartitioningscalingAsked at Uber, Discord

30-second answer

Split data across independent databases by a shard key. Range partitioning keeps ranges together - great for range scans, terrible for hotspots, since recent data all lands on one shard. Hash partitioning distributes evenly but destroys range queries. Directory partitioning uses an explicit lookup table, giving full flexibility at the cost of an extra hop and a component that must not become a bottleneck. Choose a key that is present on almost every query, has high cardinality, and distributes evenly - and accept that queries not containing the key must scatter-gather across every shard.

Strategy	How	Pros	Cons
Range	Shard by key ranges (A–F, G–M …) or by time	Efficient range scans; easy to add a shard at the end	Hotspots - today's data all lands on one shard
Hash	`hash(key) mod N`, or a consistent-hash ring	Even distribution; simple	No range scans; naive mod means resharding moves nearly everything
Directory	Explicit key → shard lookup table	Total flexibility; rebalance individual keys	Extra hop; the directory must be HA and cached
Geographic	Shard by region	Data residency compliance; low local latency	Very uneven load; cross-region queries are painful

Choosing the shard key: four tests

1 · Is it on most queries?
If the key is absent, the query must hit every shard and merge results. A scatter-gather is as slow as its slowest shard and scales badly. For a social app user_id usually passes; created_at usually does not.
2 · Is cardinality high enough?
Sharding by country gives you roughly 200 possible values with wildly uneven populations. You cannot have more shards than distinct key values, and you cannot split a single value across shards.
3 · Does it distribute evenly?
Check the actual distribution, not the theoretical one. A celebrity with 100 million followers will make one shard 50× hotter than the rest no matter how good your hash is.
4 · Does it keep related data together?
If a transaction must touch two rows atomically, they should be on the same shard. Cross-shard transactions require two-phase commit or a saga, both of which you would rather avoid.

What you lose by sharding

Cross-shard joins - gone. You join in the application, or denormalise so you do not have to.
Cross-shard transactions - need 2PC or a saga; both are far more complex than a local transaction.
Global uniqueness - an auto-increment per shard collides. Use UUIDv7, Snowflake IDs or a central range allocator.
Global secondary indexes - a lookup by a non-shard-key column must scatter, or be maintained as a separate index shard.
Simple aggregate queries - COUNT(*) becomes a fan-out and merge.
Operational simplicity - schema migrations, backups and restores now run N times, and must be consistent across N.

Resharding without downtime

1 · Use logical shards from day one
Map keys to, say, 1,024 logical shards, then map logical shards to physical servers. Growing means moving logical shards between servers - no rehashing of keys, ever. This single decision is what makes later growth boring.
2 · Dual-write and backfill
Start writing to both old and new locations, backfill history in the background, and verify the copies match before switching any reads.
3 · Cut over reads gradually
Move 1%, then 10%, then 100% of reads, comparing results between old and new as you go.
4 · Stop dual-writing and reclaim
Only after reads have run cleanly on the new location for long enough to trust it - and keep the old copy until you are certain.