Database sharding and replication interview questions

Sharding by the key the hot query filters on puts a conversation on one shard, so the read hits one node.

More shards of message_id makes the fan-out wider. created_at puts all new messages on the newest shard. sender_id still spreads one conversation across every participant's shard.

One key always hashes to one shard, however many shards you have. Salting the hot key spreads its writes, and reads gather the sub-keys or come from cache.

Adding shards moves other keys, not the one hot key. Range sharding still keeps a single user_id on one shard. More replicas can help reads but not writes to one key.

Monotonic keys make the newest range a single write hotspot. Prefix with a well-spread key (for example source_id) and keep time as the second part.

Recent events are on one shard, the opposite of fan-out. Time ranges are exactly what range sharding is good at. Splits happen as ranges grow, not per insert.

A key stays only if hash mod 4 equals hash mod 5, which is true for 4 of every 20 residues, so about 80% move. Consistent hashing moves about 1/5 instead.

About 20% is the consistent hashing figure. 25% confuses it with the old shard count. With mod N the placement of existing keys changes as soon as N changes.

This load fits a single node well. Sharding adds cross-shard queries, transactions and rebalancing for no gain here, while the actual problem is a few slow queries.

Sharding early pays the complexity cost years ahead. Changing database type does not fix bad queries. Splitting by table breaks joins and does not address the slow queries.

The replica has not applied the write yet. Routing the writer's reads to the primary for a while (or until the replica passes the write's log position) gives read-your-writes without moving all reads.

A cache can also hold the old value and is another lag source. More replicas spread load but each still lags. A TTL does nothing for replication lag.

With async replication the primary acknowledges commit before the standby has the WAL. Anything in that gap is gone after promotion.

Durable on the primary's disk does not help when that disk is lost. Open transactions are lost in any setup, the surprise is committed ones. The standby has applied WAL continuously, not only up to the base backup.

Each commit waits for the sync standby's confirmation, so with no standby commits block. That is why setups list two or more candidates or use quorum.

Postgres does not fall back to async on its own. Continuing without confirmation would break the guarantee. The primary keeps running, its commits just wait.

Different replicas lag by different amounts, so random routing can step back in time. Sticking a session to one replica gives monotonic reads.

Queuing writes does not change which replica you read. Synchronous replication slows every commit and, in Postgres, only makes the write visible on the standby with remote_apply. Sharding does not touch read routing.

Replicas scale reads. Every write still runs on the one primary. For write load you reduce writes, batch them, scale up, or partition and shard.

Single-leader replicas cannot accept writes. Synchronous replication adds latency to writes. A load balancer cannot send writes to a read-only node.