Designing Scalable Databases for Interviews

When a system design interview turns to the database, the interviewer stops testing whether you know technology names and starts testing whether you understand trade-offs. Scaling a database is a sequence of deliberate compromises — between consistency and availability, read speed and write speed, simplicity and scale. Here's how to reason through each one out loud.

The one idea to internalise: you don't scale a database, you scale access patterns. Every scaling technique — indexing, replication, partitioning — is an answer to a specific query shape under a specific load. Ask about the reads and writes before proposing anything.

Data modeling: start from the queries

In application development you're taught to normalise first. In interview design, invert it: list the access patterns, then model the data to serve them. Designing a social feed? The hot path is "show user X the latest posts from accounts they follow" — so you model for that read, perhaps with a precomputed fan-out table, rather than storing a pristine normalised schema and computing the feed on every request.

This is also where SQL versus NoSQL actually gets decided. Choose relational when entities have real relationships and you need transactions across them — payments, inventory. Choose a wide-column or document store when access is mostly key-based lookups of self-contained records at huge scale. The wrong answer is choosing based on fashion; the right answer always cites the access pattern.

Indexing: the cheapest scaling lever

An index is a sorted side-structure that turns a table scan into a lookup. In interviews, three facts carry most of the weight:

• Indexes make reads fast and writes slower — every write updates every index on the table.
• A composite index (a, b) serves queries filtering on a or a AND b, but not on b alone — column order matters.
• If a query is slow, the first question is always "what index supports this?" not "what database should we switch to?"

Mentioning that you'd verify an index with a query plan is a small detail that reads as real experience.

Replication: copies for reads and survival

Replication means keeping copies of your data on multiple machines, and it buys two different things. Read scaling: replicas serve read traffic while the primary handles writes. Durability and availability: if the primary dies, a replica gets promoted. The trade-off you must name is replication lag — with asynchronous replication, a user can write data and then read a stale copy. Interview-friendly mitigations: read-your-own-writes via the primary, or synchronous replication for data where staleness is unacceptable (and accept the write latency).

Partitioning: when one machine isn't enough

Sharding splits data across machines by some key. The decisions, in order:

• Shard key. Pick the key most queries filter on — user ID for user-centric apps. A bad shard key turns every query into a scatter-gather across all shards.
• Distribution. Hash-based spreads evenly but kills range queries; range-based keeps ranges local but risks hotspots.
• What breaks. Cross-shard joins and transactions get painful or impossible. Say this out loud — acknowledging the cost is the answer, not avoiding the topic.

And the classic follow-up: what happens when one shard gets hot? Consistent hashing, virtual shards, or splitting the hot shard — any one of these, explained briefly, is enough.

Consistency trade-offs: CAP, said like a human

When a network partition happens, you choose between consistency (refuse to answer rather than answer wrong) and availability (answer with what you have). CP systems — think strongly consistent stores — sacrifice availability during partitions. AP systems — think Dynamo-style stores — keep serving and reconcile later with eventual consistency. The interview move isn't reciting definitions; it's tying the choice to the product: "money balances must be right, so CP here; a like-count can converge later, so AP is fine there." For the wider architecture context these decisions sit inside, see system design fundamentals and the cheat sheet.

Common mistakes

• Sharding too early. Indexing, caching, and read replicas solve most scale problems. Sharding is the last resort because of its operational cost.
• Ignoring the write path. Everyone designs for reads. Interviewers probe writes: bursts, ordering, idempotency.
• Magic consistency. Claiming strong consistency and high availability during partitions. Pick one and defend it.
• No back-of-envelope math. "Millions of users" is not a number. Storage per record times record count, read/write ratio — rough figures justify every decision above.

FAQ

Should I always propose sharding in interviews? No — propose it only when your own estimates say a single primary can't handle the load, and say so explicitly. That restraint is what's being scored.

How deep on internals like B-trees and LSM trees? One level: B-trees suit read-heavy relational workloads, LSM trees suit write-heavy workloads. Knowing the sentence matters more than the internals.