Database sharding & replication

High cardinality, an even spread of data and traffic, and present in most queries so they hit a single shard. user_id or tenant_id are common. Avoid monotonically increasing keys with range sharding (all new writes hit one shard) and keys with a few huge values.

Sharding splits the data, so each machine holds a different part and write capacity grows. Replication copies the same data to several machines for availability and read scaling. Production systems usually do both: every shard has replicas.

Range-based (by key ranges such as dates or IDs; good for range queries, risks hot spots), hash-based (spreads load evenly, poor for ranges), directory-based (a lookup service maps keys to shards; flexible but adds a dependency), and geo-based (by region for latency and data residency).

Avoid them on hot paths by choosing a shard key that matches the main access pattern. When needed, scatter-gather in parallel and merge results, maintain denormalised or secondary indexes (global indexes or a search engine), or precompute aggregates.

Use many logical shards mapped to fewer physical nodes so you move logical shards rather than rehashing; or double-write to old and new layouts, backfill, verify, switch reads, then stop old writes. Consistent hashing limits how much data moves.

Keys and nodes sit on a hash ring, and each key belongs to the next node clockwise. Adding a node takes over only the keys between it and its neighbour, about 1/N of the data, instead of reshuffling everything as hash mod N would. Virtual nodes even out the load.

A shard receiving far more traffic than others, often due to a celebrity key or sequential IDs in range sharding. Fix by splitting the shard, salting hot keys across shards, caching hot data, or choosing a higher-cardinality shard key.

Use globally unique IDs like UUIDv7, Snowflake-style IDs (timestamp, machine ID and sequence) or ID ranges per shard, rather than per-database auto-increment, which would collide.

Cross-shard transactions need two-phase commit or a distributed database, which is slower and complex. Prefer designs where a transaction touches one shard (shard by tenant or user) and use sagas for multi-shard workflows.

Synchronous replication waits for replicas before confirming, so no data is lost on failover but writes are slower. Asynchronous replication is faster but recent writes can be lost if the primary fails. Semi-synchronous waits for at least one replica.

Each shard has a primary and replicas. A failure detector or consensus system promotes a replica, updates routing (via a proxy or service discovery), and fences the old primary so it cannot accept writes. Clients retry briefly during the switch.

It receives queries, determines which shard holds the data from the shard key, forwards the query, and merges results for multi-shard queries, hiding sharding from the application (Vitess, Citus, MongoDB mongos).

Each tenant's data lives on one shard, so queries and transactions stay local and noisy tenants can be moved to dedicated shards. Very large tenants may need their own shards or further splitting.

Choose the key that matches the main access path and spreads load: author ID keeps a user's posts together for profile pages, while a hash of post ID spreads writes evenly. Check that hot users will not create hot shards and that the common queries hit one shard.

Keep a separate lookup table or service keyed by the unique value that maps to the owning record, insert into it first with its own uniqueness guarantee, then write the record. Alternatively shard by that value so uniqueness is checked within one shard.

The delay before a replica applies the primary's changes. Route reads that need fresh data to the primary (or use session stickiness after a write), monitor lag, and avoid replicas for consistency-critical reads.

Leader-follower has one writable primary per shard, which is simple. Multi-leader accepts writes in several places (often regions), lowering write latency but requiring conflict resolution for concurrent updates.

In leaderless systems like Cassandra, a write goes to N replicas and succeeds after W acknowledgements; reads query R replicas. If R + W > N, reads overlap with the latest write, giving tunable consistency.

When vertical scaling, read replicas, caching, archiving old data, better indexes or partitioning within one database still work. Sharding adds lasting operational and application complexity.

Partitioning splits a table into parts within one database server (for example by month), helping maintenance and query pruning. Sharding spreads data across servers to scale capacity.

Back up each shard with point-in-time recovery, coordinate snapshots when cross-shard consistency matters, test restores regularly and keep the shard map versioned with backups.

Automate them with a tool that applies the same migration to every shard, make migrations backward compatible, roll out gradually and track which shards are on which version.

Each physical node owns many points on the hash ring, so load spreads evenly and adding or removing a node moves small pieces of data from many nodes rather than a big chunk from one.

Query each shard for its top N by the sort key, merge them in the router, and return the global top N with a cursor holding the last sort value. Avoid OFFSET pagination, which forces every shard to read and skip more rows as pages go deeper.

An index on a column other than the shard key. A local index lives on each shard, so a lookup must query all shards; a global index is itself sharded by the indexed value, so lookups are fast but writes update two places and can drift without careful consistency.

Copy the tenant's data to the new shard while capturing changes, replay the change stream until caught up, briefly pause writes for that tenant, switch the directory entry to the new shard, resume, then delete the old copy after verification.

Do not run them on the shards. Stream changes (CDC) into a warehouse or OLAP store such as BigQuery, Snowflake or ClickHouse, where full scans and joins across all data are cheap, and keep the OLTP shards for short transactional queries.

Track per-shard QPS, latency, storage, CPU, replication lag and error rates, and watch for imbalance between shards so hot spots are found early.

Distributed SQL databases like CockroachDB, YugabyteDB, Spanner and TiDB, MongoDB with sharded clusters, Cassandra and DynamoDB partition automatically; Citus and Vitess add sharding to PostgreSQL and MySQL.

Estimate data size and throughput growth for several years, divide by what one node handles comfortably, and create more logical shards than nodes so you can rebalance without resharding.

A guarantee that after a user writes, their own subsequent reads see that write, even if other users see it later. Implement by reading from the primary for a short time after a write, or tracking replication positions.

Two nodes both believe they are primary and accept conflicting writes, usually after a network partition. It is prevented with a consensus or quorum-based election, fencing tokens or STONITH so the old primary cannot write, and clients that follow the elected leader.

1. AIt needs a global lock
2. BIt is slow to compute
3. CAlmost every key moves to a different shardCorrect answer
4. DIt cannot handle strings

Why: Changing N changes the result for most keys, so nearly all data must move.

1. ANo sharding
2. BHash-based shardingCorrect answer
3. CRange-based sharding
4. DGeo-based sharding

Why: Neighbouring keys land on different shards.

1. AThe main access patternCorrect answer
2. BThe table name
3. CThe server's IP
4. DAlphabetical order

Why: Keeps hot queries on one shard.

1. ATo save disk
2. BTo avoid replication
3. CTo speed up writes only
4. DTo rebalance by moving shards instead of rehashingCorrect answer

Why: Growth later means moving shards, not rehashing.

1. ACount orders across all users for yesterdayCorrect answer
2. BUpdate one user's profile
3. CInsert a new order for a user
4. DFetch one user's orders

Why: A query without the shard key must fan out to every shard and merge the results.

1. AReplication
2. BIndexes
3. CNew sequential IDs all hit the last rangeCorrect answer
4. DHashing

Why: The newest range takes all writes.

1. ARow numbers
2. BTimestamps in seconds
3. CSnowflake IDs or UUIDv7Correct answer
4. DPer-database auto-increment

Why: Globally unique without coordination.

1. ALarger backups
2. BLosing recent writes on failoverCorrect answer
3. CSlower writes
4. DNo replicas

Why: Replicas may lag behind.

1. ACompression
2. BFencingCorrect answer
3. CCaching
4. DSharding

Why: Fencing avoids a split-brain cluster.

1. AVitessCorrect answer
2. BRedis
3. CKafka
4. DNginx

Why: Used at YouTube scale.

1. ATenants in Redis
2. BData in the browser
3. CEach tenant's transactions on one shardCorrect answer
4. DAll tenants on every shard

Why: Local queries and transactions.

1. AClient-side checks only
2. BA longer TTL
3. CA lookup table keyed by emailCorrect answer
4. DA local index on each shard

Why: One place must own the uniqueness decision.

1. AEvery shard reads and skips more rowsCorrect answer
2. BIt uses too little memory
3. CIt skips the router
4. DIt needs a CDN

Why: Cursor pagination with the last sort value avoids it.

1. AMore write capacity
2. BFailover and read scaling for that shardCorrect answer
3. CAutomatic cross-shard joins
4. DSmaller indexes

Why: The replica holds a copy of the shard's data, ready to take over or serve reads.

1. AEventual inconsistency
2. BReads that overlap the latest writeCorrect answer
3. CFaster writes
4. DNo replication

Why: This gives tunable consistency.

1. ARemoves indexes
2. BStays within one database serverCorrect answer
3. CUses many servers
4. DNeeds Kafka

Why: Sharding spreads across servers.

1. AEven load and smaller data movesCorrect answer
2. BEncryption
3. CQuery syntax
4. DBackups

Why: Each node owns many ring points.

1. AConflict resolutionCorrect answer
2. BA single region
3. CNo writes
4. DSynchronous commits only

Why: Concurrent writes can conflict.

1. AWhen writes exceed one node
2. BNever
3. CWhen replicas, caching or indexes still sufficeCorrect answer
4. DWhen data is huge

Why: It adds lasting complexity.

1. AIgnoring consistency
2. BSagas instead of distributed transactionsCorrect answer
3. CGlobal locks
4. DManual SQL

Why: Each step stays local.

1. AA query router or proxyCorrect answer
2. BThe CDN
3. CThe browser
4. DThe message queue

Why: Hides sharding from the app.

1. AWrites are delayed
2. BReads are cached forever
3. CA user sees their own writes immediatelyCorrect answer
4. DAll users see writes instantly

Why: Often read from the primary after writing.

1. ANeed no index
2. BGo to the cache only
3. CMust query every shardCorrect answer
4. DHit exactly one shard

Why: Each shard indexes only its own rows.

1. AIn a warehouse fed by change data captureCorrect answer
2. BOn the busiest OLTP shard
3. CIn the browser
4. DIn the load balancer

Why: Analytics scans would slow transactional traffic.

1. ASwitching its directory entry after catch-upCorrect answer
2. BDeleting the tenant first
3. CRenaming the database
4. DRestarting every client

Why: The directory points requests at the tenant's new home.

1. AA shard is empty
2. BReplication is synchronous
3. CAn index is rebuilt
4. DTwo nodes both act as primaryCorrect answer

Why: Conflicting writes then diverge the data.

1. AExcel
2. BA single MySQL server
3. CCockroachDBCorrect answer
4. DSQLite

Why: It is a distributed SQL database.

1. AQPS, latency, storage and lagCorrect answer
2. BOnly total users
3. COnly logos
4. DNothing

Why: Detect imbalance early.

1. ANever
2. BAutomated and backward compatibleCorrect answer
3. CBy hand on each shard
4. DOnly on one shard

Why: Track versions per shard.

1. ANo replicas
2. BThe client
3. CAt least one replicaCorrect answer
4. DAll replicas

Why: A middle ground between sync and async.

1. AA lookup service mapping keys to shardsCorrect answer
2. BA hash function only
3. CDates only
4. DRandom placement

Why: Flexible but adds a dependency.

1. ACounting all posts
2. BFull-text search across users
3. CLoading one user's profile postsCorrect answer
4. DA global trending feed

Why: All of one author's posts live on one shard.