Chapter 7: Database Selection, Replication, and Sharding at a Systems Level

Why this matters

The companion Database book covers relational design, replication mechanics, and sharding strategies in full depth — this chapter's job is narrower and specifically systems-level: given a whole system's requirements (Chapters 3-4), which database decisions actually matter for the overall architecture, and how do they interact with the other components already covered in this book (caching, load balancing)?

Selecting a database category from the systems-design vantage point

[BEGINNER] Directly applying the companion Database book's Chapter 1 framework, but now driven by the specific non-functional requirements elicited in Chapter 3: strong transactional/consistency needs point toward relational (PostgreSQL); flexible, per-record-varying structure points toward document stores (MongoDB); simple, extremely fast key-based lookups point toward key-value stores (Redis); massive write throughput with known access patterns points toward wide-column stores. The systems-design-specific addition: a single system often legitimately uses more than one database category simultaneously (polyglot persistence, Database book Chapter 1) — a social media system might use a relational database for user accounts and billing (needs transactions), a document store for post content (flexible, evolving structure), and a key-value store for session/cache data (Chapter 5) — each chosen for the specific sub-problem's specific access pattern, not one database serving the entire system uniformly.

Replication's systems-level role: read scaling and availability

[SENIOR INSIGHT] From a systems-design vantage point, replication (companion Database book, Chapter 10) primarily serves two purposes worth naming explicitly in a design conversation: read scaling (routing read traffic across multiple replicas, directly complementing Chapter 6's load balancing, but at the database layer specifically) and availability (a replica can be promoted if the primary fails, avoiding a single point of failure at the data layer). The systems-level trade-off to state explicitly: read replicas introduce replication lag (Database book, Chapter 10), meaning a read immediately following a write might not reflect that write yet if routed to a lagging replica — a real consistency cost that must be weighed against the read-scaling benefit, and which directly connects to Chapter 10 of this book's CAP theorem discussion.

Primary (accepts all writes)
    |
    +--replicates--> Replica 1 (serves read traffic)
    +--replicates--> Replica 2 (serves read traffic)
    +--replicates--> Replica 3 (serves read traffic, or standby for failover)

Read traffic spread across replicas (Chapter 6-style load balancing,
applied at the database layer) -- write traffic all goes to the primary.

Sharding's systems-level role: write throughput and total data volume

[BEGINNER] Sharding (companion Database book, Chapter 11) becomes a systems-design topic specifically when Chapter 4's capacity estimation reveals that a single database node's write throughput or total storage capacity is a genuine, real constraint — not before. The systems-level question to answer explicitly: what should the shard key be, and does it align with this specific system's dominant query pattern (Database book, Chapter 11's core lesson) — this decision, made at the systems design stage, has consequences for nearly every other part of the system that queries the sharded data.

Sequencing the decision: don't reach for sharding before it's justified

[IN PRACTICE] A very common system design interview mistake: proposing sharding as part of an initial architecture before establishing (via Chapter 4's estimation) that a single, well-provisioned database server genuinely can't handle the estimated load. A modern, well-provisioned relational database server can handle a surprisingly large amount of traffic and data before sharding is genuinely necessary — proposing it prematurely, without the estimation to justify it, signals pattern-matching ("this is a 'systems design' question, so I should mention sharding") rather than genuine, evidence-based reasoning, exactly the same premature-complexity anti-pattern the companion Design Patterns book's Chapter 13 covers as "pattern fever," here applied to system architecture instead of code-level design patterns.

Weak: "I'd shard the database from the start to make sure it scales."
      (No connection to actual estimated load -- reflexive, not justified.)

Strong: "Based on our earlier estimate of ~7,000 req/s peak and roughly
        36TB of data over 3 years, a single well-provisioned PostgreSQL
        instance with read replicas can likely handle this comfortably
        -- I wouldn't reach for sharding unless our estimates were
        significantly higher, or unless we later observe the actual
        load exceeding what a single primary can handle."

Common mistakes

1. Choosing one database category for an entire system uniformly, missing legitimate opportunities for polyglot persistence where different sub-problems have genuinely different access patterns.
2. Not naming replication lag as a real consistency cost when proposing read replicas for scaling, glossing over a trade-off that directly matters for the system's actual correctness under specific conditions.
3. Proposing sharding reflexively, without connecting it to actual estimated load (Chapter 4) that a single, well-provisioned database genuinely couldn't handle — a common, easily-avoided premature-complexity signal in system design conversations.
4. Choosing a shard key without connecting it to the system's actual dominant query pattern, risking the exact hot-shard or scatter-gather problems the companion Database book's Chapter 11 covers in depth.

Discussion / interview questions

1. "Would you always use a single database technology for an entire system, or could you use more than one?" Strong answer: Polyglot persistence — using different database categories for different sub-problems within one system, each chosen for its specific access pattern (e.g., relational for transactional account data, document store for flexible content, key-value for caching/sessions) — is often the right answer, not a single uniform choice.

2. "What's the trade-off of using read replicas to scale database reads?" Strong answer: Read scaling and improved availability, at the cost of replication lag — a read routed to a lagging replica might not reflect a very recent write, a real consistency cost that needs to be weighed against the scaling benefit and connects directly to the CAP theorem's consistency-availability trade-off.

3. "When would you propose sharding a database as part of a system design, and when would you explicitly avoid it?" Strong answer: Only once capacity estimation (Chapter 4) shows a single, well-provisioned database node genuinely can't handle the estimated write throughput or storage volume — proposing it reflexively, without this justification, is a premature-complexity signal rather than evidence-based reasoning.

4. "How would you choose a shard key for a system you're designing?" Strong answer: Base it on the system's actual dominant query pattern (per the companion Database book's Chapter 11) — a shard key that doesn't align with how the system is actually queried risks hot shards (uneven load) or scatter-gather queries (needing to check every shard), regardless of how reasonable the key seems in isolation.

Chapter summary / cheat sheet

• Select a database category (or combination, via polyglot persistence) based on the system's specific non-functional requirements and access patterns per sub-problem, not one uniform choice for the whole system.
• Replication serves read scaling and availability at the systems level, at the real cost of replication lag — a consistency trade-off worth naming explicitly.
• Only propose sharding once capacity estimation (Chapter 4) shows it's genuinely justified by estimated load a single, well-provisioned database node can't handle — proposing it reflexively is a premature-complexity red flag.
• A shard key choice should align with the system's actual dominant query pattern — this decision has consequences for nearly every other part of the system that queries the sharded data.

---