Sharding and Replication
Sharding rationale, shard-key selection, hot keys, shard routing and rebalancing, cross-shard operations, replication, and the SQL versus NoSQL choice.
10 questions
JuniorTheoryVery commonWhat does database replication do, and how do primary and replica split the work?
What does database replication do, and how do primary and replica split the work?
A primary accepts all writes; replica nodes copy its data and serve reads for read scaling. If the primary dies, failover promotes a replica. Async replication is fast but lets replicas lag and serve stale reads; sync stays consistent but slower.
Common mistakes
- ✗Thinking replication shards data — it copies the full dataset to every node; sharding is the separate splitting concern.
- ✗Assuming a
replicaread is always current — async lag means read-after-write can return stale data. - ✗Forgetting failover — without promoting a
replica, losing theprimarytakes writes down entirely.
Follow-up questions
- →When would you accept async replication lag over the cost of sync replication?
- →How does a read-your-own-writes requirement change which node you read from?
JuniorTheoryVery commonWhy shard a database across multiple servers instead of running one large instance?
Why shard a database across multiple servers instead of running one large instance?
You shard to raise write throughput, spread CPU load across machines, geo-distribute data closer to users, and hold more volume than a single server can store. Each shard always runs with replicas for durability and read scaling.
Common mistakes
- ✗Thinking sharding is only about storage size and ignoring write throughput, CPU, and geo-locality as equally valid drivers.
- ✗Believing a shard is a single point of failure — forgetting that every shard itself runs with replicas for durability.
- ✗Sharding too early on a dataset one Postgres node could still serve, taking on cross-shard complexity for no gain.
Follow-up questions
- →How does adding replicas to a shard differ from sharding itself?
- →What signals tell you it's actually time to shard rather than scale up?
MiddleTheoryCommonWhat makes a good shard key, and what goes wrong with a bad one?
What makes a good shard key, and what goes wrong with a bad one?
A good shard key has high cardinality (user_id is good, gender is bad) so rows spread across many shards, distributes load evenly so no single shard runs hot, and matches common access patterns so most queries route to one shard. A bad key creates hot shards or forces an expensive cross-shard query on nearly every request.
Common mistakes
- ✗Picking a low-cardinality column like a boolean or status flag as the shard key
- ✗Assuming any unique key spreads load evenly regardless of access patterns
- ✗Ignoring query patterns so common reads fan out across every shard
Follow-up questions
- →How would you detect that a chosen shard key has produced a hot shard?
- →When a query cannot use the shard key, what makes it so expensive?
MiddleTheoryCommonWhen and how do you rebalance shards, and what makes a rebalance cheap?
When and how do you rebalance shards, and what makes a rebalance cheap?
Rebalance when a shard grows too large, runs hot, the cluster grows or shrinks, or the shard key changes. Move ranges to other nodes with minimal downtime, and use consistent hashing so an added or removed node moves only a fraction of the data.
Common mistakes
- ✗Rebalancing every shard at once with a global reshuffle, taking the whole dataset offline instead of moving one range at a time.
- ✗Using modulo-of-node-count hashing, so growing the cluster by one node relocates almost every key instead of a fraction.
- ✗Treating a hot shard as a sizing problem and only adding capacity, never splitting the hot range or salting the key.
Follow-up questions
- →How does consistent hashing cut the amount of moved data versus modulo hashing?
- →How do you keep reads correct while a range is mid-migration between two shards?
MiddleTheoryCommonWhat read-consistency problem does async replication cause, and how do you fix it?
What read-consistency problem does async replication cause, and how do you fix it?
Async replication acks a write on the primary before replicas apply it: fast, but it causes replication lag, so a read right after a write can hit a stale replica (read-after-write anomaly). Fix it by reading-your-writes from the primary or sync replication.
Common mistakes
- ✗Thinking async replication is always strongly consistent and that replicas instantly reflect every committed write.
- ✗Believing more replicas reduce replication lag, when extra replicas add read capacity but do not make a single replica fresher.
- ✗Confusing replication lag with failover, assuming promoting a replica fixes a read-after-write miss.
Follow-up questions
- →How would you implement the consistency technique read-your-writes for a user who just posted a comment?
- →When is the consistency model bounded staleness on replicas acceptable instead of reading the primary?
MiddleTheoryCommonHow does a sharded system route a query to the shard that holds its data?
How does a sharded system route a query to the shard that holds its data?
The shard key resolves to a target through a DSN connection string, a proxy holding the shard map, or a coordinator that plans and forwards the query, like the routing component Citus for Postgres. The trade-offs are extra latency per hop and the router as a bottleneck and single point of failure.
Common mistakes
- ✗Assuming every query carries the shard key, so cross-shard lookups by a non-key column never need a fan-out.
- ✗Treating the coordinator as free, ignoring the extra hop latency and that it becomes a bottleneck and SPOF.
- ✗Believing a smart client driver removes all routing cost, when the mapping must still live and stay in sync somewhere.
Follow-up questions
- →How does a stale shard map on a proxy or driver cause a query to hit the wrong shard?
- →When would you prefer a coordinator like Citus over routing in the client driver?
JuniorTheoryOccasionalHow do you decide between a SQL and a NoSQL database for a service?
How do you decide between a SQL and a NoSQL database for a service?
Choose by access patterns and consistency, not hype. SQL is relational with ACID, joins, a rigid schema, and strong consistency; NoSQL (document, key-value, wide-column) gives a flexible schema and easy horizontal scaling, but often eventual consistency. Default to Postgres; pick NoSQL on a real need.
Common mistakes
- ✗Treating NoSQL as a strictly faster, drop-in replacement for SQL rather than a different data model
- ✗Assuming every NoSQL store gives strong consistency like a relational database does
- ✗Choosing the database by hype instead of by access patterns and consistency needs
Follow-up questions
- →When does a real requirement justify reaching for NoSQL over Postgres?
- →Which NoSQL family fits key-value lookups versus rich document queries?
JuniorTheoryOccasionalWhat is the difference between partitioning and sharding?
What is the difference between partitioning and sharding?
Partitioning splits ONE table into chunks — by range, list, or hash — inside a SINGLE database instance, so the planner skips irrelevant partitions and scans less. Sharding splits data across MULTIPLE instances to grow past what one server can hold.
Common mistakes
- ✗Saying partitioning and sharding are the same thing, when one stays on a single instance
- ✗Claiming partitioning spreads data across many servers — that is sharding, not partitioning
- ✗Forgetting that a partitioned table still lives in one database, so it shares its resource limits
Follow-up questions
- →When does range partitioning beat hash partitioning for time-series data?
- →At what scale does a single partitioned instance stop coping and force a shard?
MiddleDesignOccasionalYour product runs on Postgres sharded by tenant_id. Analytics queries — cross-shard aggregations and dashboard rollups — are scanning many shards and now regress the latency of customer-facing OLTP transactions, especially at peak. The analytics team also wants near-real-time dashboards (seconds to a minute behind), not yesterday's batch. Design a separation so that heavy analytical load never touches the transactional path. Constraints:
- No OLTP latency regression on the sharded Postgres under analytical load.
- Dashboards must be near-real-time, not a once-a-day batch.
- Raw historical data must still be retained cheaply for ad-hoc reprocessing.
Specify how changes leave the OLTP store, where analytical queries actually run, and how the raw and BI layers fit in.
Your product runs on Postgres sharded by tenant_id. Analytics queries — cross-shard aggregations and dashboard rollups — are scanning many shards and now regress the latency of customer-facing OLTP transactions, especially at peak. The analytics team also wants near-real-time dashboards (seconds to a minute behind), not yesterday's batch. Design a separation so that heavy analytical load never touches the transactional path. Constraints:
- No OLTP latency regression on the sharded Postgres under analytical load.
- Dashboards must be near-real-time, not a once-a-day batch.
- Raw historical data must still be retained cheaply for ad-hoc reprocessing.
Specify how changes leave the OLTP store, where analytical queries actually run, and how the raw and BI layers fit in.
Keep OLTP and OLAP apart. Leave the sharded Postgres for transactions and stream its changes via CDC or domain events through Kafka to a stream processor that lands them in ClickHouse for analytics, an S3/parquet datalake, and a Snowflake warehouse feeding BI. Cross-shard aggregation moves off the transactional path, so OLTP latency holds and dashboards stay near-real-time.
Common mistakes
- ✗Pointing analytics queries straight at the OLTP shards, so heavy aggregations regress transaction latency under load
- ✗Treating read replicas as an OLAP store — they replay the same row-oriented OLTP schema, not a columnar analytics engine
- ✗Skipping the streaming layer and exporting shards in a nightly batch, which kills the near-real-time dashboard requirement
Follow-up questions
- →How does change data capture (CDC) pull changes off Postgres without adding load to the transactional path?
- →Why is the columnar OLAP store ClickHouse a better fit for cross-shard aggregation than the OLTP shards?
SeniorDesignOccasionalA marketplace orders table has outgrown one Postgres instance; design its sharding given that the workload is write-heavy, both buyers and sellers query their own orders, daily revenue reports run across all data, and a few viral sellers drive disproportionate traffic.
A marketplace orders table has outgrown one Postgres instance; design its sharding given that the workload is write-heavy, both buyers and sellers query their own orders, daily revenue reports run across all data, and a few viral sellers drive disproportionate traffic.
Shard on a high-cardinality key matching the dominant path (seller_id), route writes through a coordinator or proxy like Citus, and absorb hot sellers by salting or dedicating a shard. Serve buyer reads and daily revenue from a denormalized read model or fan-out-plus-merge, not live cross-shard joins.
Common mistakes
- ✗Picking a low-cardinality shard key like order_status, which packs all traffic onto a handful of hot shards.
- ✗Running live cross-shard joins for buyer lookups and revenue reports instead of a denormalized read model.
- ✗Forgetting hot sellers exist, so a single viral seller saturates one shard while the rest sit idle.
Follow-up questions
- →How would you rebalance when one seller's shard outgrows the rest?
- →Where does replication fit once each shard is in place?
