Skip to content

Replication vs Sharding

1. Decision

Choose Database Replication to duplicate identical copies of data across multiple nodes for high availability, fault tolerance, and read-throughput scaling; choose Database Sharding to split data horizontally into distinct partitions across multiple nodes to scale write-throughput and storage capacity beyond single-node limits.

2. 30-Second Answer

Replication and Sharding solve fundamentally different database scaling challenges. Replication copies the entire dataset across multiple server instances (Primary-Replica or Multi-Primary). Every node holds a full copy of the data, scaling read capacity, providing instant failover redundancy, and insulating against hardware loss. However, replication does not scale write capacity or total storage size (all writes must execute on the primary). Sharding partitions data horizontally across distinct database nodes based on a Shard Key (e.g., user_id mod N). Each shard node owns a unique subset of data. Sharding scales write throughput and storage capacity linearly, but introduces complex scatter-gather queries, cross-shard transactions, and rebalancing overhead. High-scale databases combine both: Sharded cluster where each shard is a Replicated set.

3. Decision Matrix

Option Best For Avoid When Latency Scalability Consistency Operational Complexity
Replication High read-to-write ratio (>10:1), high availability, instant failover protection, dataset fits on single disk Write QPS exceeding single primary node limits (>20k write QPS), multi-terabyte datasets Low Read (<5ms from replicas); Write bound by Primary WAL Scales Read throughput linearly; Write & Storage bounded by single node Strong (Primary); Eventual (Replicas via replication lag) Low to Medium
Sharding High write throughput (>50k write QPS), datasets exceeding single-node disk capacity (>2TB) Small datasets (<500GB), frequent cross-table joins without common partition key Low for single-shard key queries; High for multi-shard scatter-gather queries Scales Write throughput and Storage capacity linearly Strong within single shard; Eventual/2PC across shards High (Shard key selection, rebalancing, cross-shard joins)

4. Selection Rules

Requirement Best Choice Rationale / Example
Scale Read Throughput (90% Reads) Replication Add 5 Read Replicas to handle 100,000 QPS of read traffic
Scale Write Throughput (>50,000 Write QPS) Sharding Distribute incoming writes across 10 distinct primary shards by user_id
High Availability & Zero Data Loss Failover Replication Automatic primary failover (Patroni / Orchestrator) promotes replica to primary
Total Storage Exceeds Single Disk (>5TB) Sharding Split 50TB dataset into 25 shards of 2TB each
Combined Scale (Internet Scale) Sharded + Replicated 10 Shard nodes, where each Shard node has 2 Read Replicas (30 total DB nodes)

5. Tradeoffs

  1. Full Dataset Duplication vs Data Partitioning
  2. Gain (Replication): Every node can answer any query; simple single-node query planner.
  3. Lose (Replication): Storage cost multiplied by replica count; primary write bottleneck remains.
  4. Read Scaling vs Write & Storage Scaling
  5. Gain (Replication): Scales reads linearly by adding cheap replica nodes.
  6. Lose (Replication): Primary node must execute 100% of all write operations.
  7. Linear Write Scale vs Cross-Shard Query Penalty
  8. Gain (Sharding): Write capacity scales linearly by adding more shard nodes.
  9. Lose (Sharding): Queries missing the Shard Key must execute scatter-gather calls across all shards.
  10. Instant Failover Redundancy vs Complex Shard Rebalancing
  11. Gain (Replication): Promotes standby node to primary in seconds during hardware crashes.
  12. Lose (Sharding): Adding new shards requires resharding algorithms (Consistent Hashing) and data migration.
  13. ACID Transactions Simplicity vs Two-Phase Commit Overhead
  14. Gain (Replication): Full ACID support on primary node.
  15. Lose (Sharding): Cross-shard transactions require 2PC protocols, causing severe latency spikes.

6. Interview Counter Questions

  1. Why doesn't adding Read Replicas scale write throughput in a database?
  2. How does a database sharded by user_id handle a query searching by email_address?
  3. What is the Scatter-Gather problem in sharded database architectures?
  4. How do you combine Replication and Sharding in a production topology?
  5. What is the impact of choosing a poor Shard Key (e.g., created_at timestamp)?
  6. How does Replication Lag cause stale read anomalies in Primary-Replica setups?
  7. What are Cross-Shard Transactions and why are they avoided in high-scale System Design?
  8. How does Consistent Hashing minimize data movement during shard cluster expansion?
  9. When would you use a Distributed SQL database (YugabyteDB / CockroachDB) instead of manual application sharding?
  10. How do synchronous vs asynchronous replication models affect write latency and durability?

7. Gold Standard Answers

  1. In Primary-Replica replication, all write operations must still be processed by the single primary node (and replicated to all replicas). Replicas only offload SELECT read queries.
  2. Searching by email_address (not the shard key) forces a Scatter-Gather query: the app/proxy queries every single shard in parallel. Mitigate by maintaining a secondary global lookup table mapping email \(\rightarrow\) user_id.
  3. Scatter-Gather occurs when a query lacks the shard key, forcing the query router to issue requests to all \(N\) shards and merge results. Latency is bounded by the slowest single shard (p99 tail latency amplification).
  4. Deploy a grid topology: Shard the dataset into \(N\) logical shards based on Hash Key. For each shard, deploy 1 Primary node and 2 Read Replicas (using Patroni/etcd for replication and HA).
  5. A monotonically increasing key like created_at routes 100% of new writes to the newest single shard, creating a severe write hotspot while older shards sit idle.
  6. Asynchronous replication introduces a delay (milliseconds to seconds) between primary commit and replica update. If a user writes to primary and immediately reads from a lagging replica, they observe stale data.
  7. Cross-shard transactions span multiple physical nodes, requiring 2-Phase Commit (2PC) locks that block execution, increase latency by 5x-10x, and risk coordinator failure deadlocks.
  8. Consistent Hashing maps keys and shard nodes to a logical \(2^{32}\) hash ring. Adding a new shard node only moves \(\frac{1}{N}\) of the data from adjacent nodes rather than rehashing 100% of the dataset.
  9. Manual application sharding requires writing complex routing, scatter-gather, and migration logic in app code. Distributed SQL natively handles auto-sharding, distributed transactions, and rebalancing at the storage engine level.
  10. Synchronous: Primary waits for replica disk ACK before returning to client (Zero data loss, higher write latency). Asynchronous: Primary returns success immediately after local WAL write (Sub-millisecond latency, risk of data loss on crash).

8. Common Mistakes

  • Adding Read Replicas to solve a database write throughput bottleneck (Replicas do NOT scale writes!).
  • Sharding a database without selecting a high-cardinality, evenly-distributed Shard Key.
  • Attempting to perform multi-table JOINs across sharded tables without co-locating related data on the same shard.
  • Running Scatter-Gather queries in high-frequency user-facing latency-critical API paths.
  • Forgetting that every Read Replica adds replication stream bandwidth load to the primary node.

9. Keywords

Replication, Sharding, Primary-Replica, Multi-Primary, Shard Key, Scatter-Gather, Cross-Shard Transaction, 2PC, Consistent Hashing, Hotspotting, Replication Lag, Patroni, Distributed SQL, CockroachDB.

10. Cheat Sheet

  • Core Difference: Replication = Copies entire dataset across nodes (Scales READS & High Availability); Sharding = Splits dataset horizontally across nodes (Scales WRITES & Storage).
  • Replication Limitation: Replicas do NOT scale write capacity. All writes hit the Primary node.
  • Sharding Rule: Always query using the Shard Key to target a single node. Avoid Scatter-Gather queries across all shards.
  • Hotspot Defense: Never use timestamps or low-cardinality fields as Shard Keys. Use high-cardinality keys like user_id or uuid.
  • Ideal Topology: Sharded + Replicated (Data split into N shards; each shard replicated across 3 nodes for HA).
  • Secondary Lookups: Maintain a global index table (or Redis cache) mapping secondary_attribute \(\rightarrow\) shard_key to avoid scatter-gather scans.

11. Related Decisions