Databases & Data Storage for System Design

What this is: the storage half of a system-design interview, at the depth a staff engineer is expected to reason at. Best practices, the failure modes that actually bite in production, and — in depth — how to decide which kind of store to reach for. Code is Spring Boot; app-side snippets are shown in Kotlin and Java side by side, while SQL/config that is language-neutral is shown once. This guide ties into the sibling guides in this folder: CQRS, saga, inbox/outbox, CDC, and event sourcing.

The mental model (the one idea everything derives from)#

A database is a set of trade-offs frozen into a data structure. You don't pick a database; you pick which problems you're willing to have. And you pick them by starting from the access pattern, not from the data.

Everything below is a corollary of that sentence. Three consequences worth internalizing before anything else:

1. There is no "best" database — only "best for this access pattern at this scale under these consistency requirements." The junior answer is "use Postgres" or "use Mongo because it scales." The staff answer is "here are the read and write patterns, here's the consistency requirement per operation, here's the scale in QPS and dataset size, and therefore this store." You are choosing a point in a trade-off space, not a product.

2. Every storage decision trades three things against each other: consistency, availability/latency, and cost (money + operational). CAP and PACELC formalize part of this, normalization-vs-denormalization is another face of it, and the B-tree-vs-LSM choice is the same tension one layer down. When you "solve" a storage problem you are almost always moving the pain somewhere else — from write path to read path, from latency to staleness, from the database to the application, from money to complexity. Naming where you moved the pain is what makes you sound senior.

3. Model the queries and the failure modes first; the schema and the store fall out. Relational modeling starts from the entities and normalizes; NoSQL modeling starts from the queries and denormalizes. But both are downstream of "what will read and write this data, how often, and what happens when a node dies mid-write." If you can enumerate the top 5 queries by volume and the top 3 failure modes, the storage choice is usually forced.

A useful interview mantra: "Access pattern → data model → storage engine → topology → failure handling." Walk that chain out loud and you will never be caught flat-footed.

1. The taxonomy of data stores — what each is, and the physics underneath#

Before "when to use which" you need a crisp map of the categories and — critically — the data structure underneath each, because the data structure is what determines the trade-offs. Two structures dominate and it's worth understanding them cold, because half of all storage reasoning reduces to "B-tree or LSM-tree?"

The two engines that underlie almost everything#

B-tree / B+tree (Postgres, MySQL/InnoDB, most RDBMSs, many document stores). Data lives in fixed-size pages in a balanced tree kept sorted by key. Reads are O(log n) and predictable. Updates are in-place: find the page, mutate it, write it back (plus a write-ahead-log entry for durability).

• Optimized for reads and range scans; a point lookup or range scan touches few pages.
• Write cost: each logical write can cause a page write + a WAL write (write amplification), and random I/O when pages are scattered.
• Mature, great for mixed read/write OLTP, strong secondary-index support.

LSM-tree (Log-Structured Merge tree) (Cassandra, ScyllaDB, RocksDB, LevelDB, HBase, and the storage engine inside many "NoSQL" and time-series stores). Writes go to an in-memory sorted structure (memtable) + an append-only commit log, then get flushed to immutable on-disk SSTables. Background compaction merges SSTables and drops tombstones.

• Optimized for writes: writes are sequential appends → very high write throughput, great for write-heavy and SSD/flash.
• Read cost: a read may have to check the memtable + several SSTables; mitigated by bloom filters (probabilistic "is this key definitely not here?") and block caches, but tail-latency reads are worse than a B-tree's.
• Compaction is background work that reclaims space and keeps reads fast, but it consumes I/O and CPU and can cause latency spikes ("compaction storms") if under-provisioned.
• Better compression and better write amplification characteristics at scale.

Interview one-liner: "B-tree trades write amplification for read predictability; LSM trades read amplification for write throughput. If the workload is write-heavy or append-mostly, LSM; if it's read-heavy OLTP with rich queries, B-tree."

The categories#

*ClickHouse is columnar OLAP but is very commonly used as a time-series store; the lines blur.

Two things to notice. First, the "SQL vs NoSQL" split is the least useful axis — it lumps together stores with wildly different physics. The useful axes are data structure (B-tree/LSM/inverted/columnar), distribution model (single-node / leader-follower / leaderless), and consistency guarantee. Second, the boundaries are porous: Postgres can do JSON documents (jsonb), full-text search (tsvector), key-value, geospatial (PostGIS), and vectors (pgvector). That is exactly why "just start with Postgres" is a defensible default (§8).

2. The theory a staff engineer has to wield fluently#

You will be judged not on reciting definitions but on using them to justify a decision. Here is the minimum you must own.

2.1 ACID — and what each letter actually protects#

• Atomicity — a transaction is all-or-nothing. Failure rolls back partial work. (Implemented via WAL/undo logs.)
• Consistency (the confusing one) — a transaction moves the DB from one valid state to another, respecting declared constraints (FKs, uniqueness, checks). This is not the "C" in CAP. Say that out loud in an interview; conflating them is a classic tell.
• Isolation — concurrent transactions don't step on each other; the degree is the isolation level (§2.4).
• Durability — once committed, it survives crashes (fsync'd WAL, replication).

ACID is a property of transactions within one store. The moment your write spans two stores (DB + Kafka, DB + cache, two shards), ACID no longer covers you — that's the dual-write problem (§6) and the reason outbox/CDC/saga exist.

2.2 CAP — stated precisely (most people state it wrong)#

CAP: when a network partition (P) occurs between nodes, a distributed system must choose between Consistency (C = linearizability) and Availability (A = every request to a non-failed node gets a non-error response).

The common misstatements to avoid:

• CAP is not "pick 2 of 3." Partitions are not optional — networks fail — so P is a given. The real choice is C or A, during a partition. When there's no partition you get both.
• The "C" in CAP is linearizability, a replication/recency guarantee, not ACID's "C."
• "CA systems" (like a single-node RDBMS) just means "not distributed, so partitions aren't in scope" — not a magic third option.

So systems are CP (refuse writes / error during partition to stay consistent — e.g., a strongly-consistent config store, ZooKeeper/etcd, Spanner) or AP (keep serving, allow divergence, reconcile later — e.g., Dynamo-style stores, Cassandra with low quorums).

2.3 PACELC — the extension that actually matters day-to-day#

CAP only says something during a partition, which is rare. PACELC captures the normal case:

If Partition (P) then trade Availability (A) vs Consistency (C); Else (E, the common case) trade Latency (L) vs Consistency (C).

This is the more useful lens because even with no partition, strong consistency costs latency — every strongly-consistent read/write may need a quorum round-trip or leader hop. Examples (defaults, all tunable):

• Spanner / CockroachDB: PC/EC — consistent even at a latency cost (Spanner uses TrueTime + synchronized clocks).
• Cassandra / DynamoDB (eventual): PA/EL — stay available and low-latency, accept staleness.
• MongoDB: leader-based, favors consistency — Abadi's canonical label is PA/EC, though majority read+write concern pushes it toward PC/EC in practice (a minority primary can't ack a majority write).

How to sound senior: "It's not just CAP — even when the network is healthy, PACELC says I'm trading latency for consistency on every read. For the shopping cart I'll take EL (fast, eventually consistent); for the payment ledger I'll take EC (consistent, pay the latency)."

2.4 Isolation levels and the anomalies they prevent (know this cold)#

Isolation level is the single most common concurrency question and the source of the nastiest production bugs. The SQL standard defines four levels by which anomalies they forbid:

The anomalies, concretely:

• Dirty read — you read another txn's uncommitted write that later rolls back.
• Non-repeatable read — you read a row twice in one txn and get different values (someone committed an update between).
• Phantom — you re-run a range query in one txn and new rows appear (someone inserted matching rows).
• Lost update — two txns read a value, both increment, one overwrites the other. balance = balance + 100 done twice, one is lost.
• Write skew — two txns read an overlapping set, each writes a disjoint part, and together they violate an invariant no single txn violated. Classic: two doctors each check "at least one other is on-call" and both go off-call. SI does not prevent this; only Serializable does.

The version-dependence that trips people up (flag this in interviews):

• PostgreSQL default is Read Committed. Its "Repeatable Read" is actually Snapshot Isolation (prevents phantoms too), and its "Serializable" is SSI (Serializable Snapshot Isolation) — it detects write-skew and aborts one txn with a serialization failure, so you must be prepared to retry.
• MySQL/InnoDB default is Repeatable Read, and via next-key (gap) locking it prevents phantoms for locking reads in practice — a stronger-than-standard RR.
• Oracle and MySQL "Serializable" are implemented differently; don't assume portability of concurrency behavior across engines.

These five levels are not a clean total order — a favorite staff trap. The SQL standard names levels by which phenomena they forbid, but the original locking definition (Berenson et al., cited in the lineage) and real MVCC engines disagree precisely on Repeatable Read. Under locking RR, long-held read locks actually prevent lost update and write skew while still allowing phantoms — so locking-RR and Snapshot Isolation are incomparable: SI forbids phantoms but permits write skew; locking-RR is the exact reverse. The table above takes the practical MVCC view (MySQL/Postgres), where you should assume RR does not save you from lost update or write skew and reach for SELECT … FOR UPDATE, @Version, or Serializable. Don't present these as one strictly-increasing hierarchy in an interview.

The practical rule: know your engine's default and whether your invariant is vulnerable to lost update (needs SELECT … FOR UPDATE, an optimistic @Version, or an atomic UPDATE … SET x = x + 1) or write skew (needs Serializable or an explicit lock / materializing the conflict). We fix both in §5 and §7.

2.5 Consistency models across replicas (a separate axis from isolation)#

Isolation is about concurrent transactions; replication consistency is about what a reader sees across replicas. Staff engineers keep these separate. From strongest to weakest:

• Linearizable (strong): every read sees the most recent committed write, as if there were one copy. Costs latency/availability. Needed for locks, leader election, uniqueness, "is this seat taken?"
• Sequential / causal: operations respect a global or causal order; causal ("reads reflect writes that causally preceded them") is often the sweet spot — cheaper than linearizable, avoids the worst anomalies (e.g., seeing a reply before the message).
• Read-your-writes (RYW): you always see your own writes (even if others lag). The single most common expectation users have.
• Monotonic reads: you never see time go backwards (no reading newer then older).
• Eventual: replicas converge if writes stop; until then, stale reads. Cheapest, highest availability.

The trap: "eventual consistency" is often fine for the system but violates read-your-writes for the user ("I posted a comment and it vanished"). The fix is usually to route a user's post-write reads to the leader/their own session, or to read from a cache you just updated — not to make the whole system strongly consistent.

3. Data modeling & indexing — where the query performance is actually won or lost#

3.1 Normalize for correctness, denormalize for reads — and know which world you're in#

• Relational modeling starts from entities and normalizes (3NF): each fact in one place, no update anomalies, joins reassemble data at read time. You pay at read time (joins) to stay clean at write time.
• NoSQL / aggregate modeling starts from queries and denormalizes: store the data shaped like the read, duplicate freely, no joins. You pay at write time (fan-out updates, possible inconsistency) to be fast at read time.

Neither is "better." The decision is: do you have a few well-known high-volume queries (denormalize/NoSQL) or many ad-hoc evolving queries (normalize/relational)? This is the same read-vs-write tension as B-tree-vs-LSM, one layer up. And it's why DynamoDB single-table design exists: you model the access patterns first, then design one table whose partition/sort keys serve all of them — the antithesis of "design the entities, figure out queries later." (See the CQRS guide: CQRS is exactly keeping a normalized write model and a denormalized read model at the same time.)

3.2 Indexes — the 80% of query performance nobody models upfront#

An index is a secondary data structure (usually a B-tree) that turns an O(n) scan into an O(log n) lookup — at the cost of extra storage and slower writes (every write must update every affected index). Core concepts to have crisp:

• Composite index column order matters. An index on (tenant_id, created_at) serves WHERE tenant_id = ? ORDER BY created_at and WHERE tenant_id = ?, but not WHERE created_at = ? alone — this is the leftmost-prefix rule. Order columns by: equality-filter columns first, then the range/sort column.
• Covering index — if the index contains every column the query needs (via included columns), the engine answers from the index alone and never touches the table ("index-only scan"). Huge win for hot read paths.
• Selectivity / cardinality — indexing a low-cardinality column (status with 3 values) is often useless; the planner will scan anyway. Index high-selectivity predicates.
• Partial index — CREATE INDEX ... WHERE status = 'ACTIVE' indexes only the rows you query, smaller and faster.
• Write cost — every index is write amplification. Ten indexes on a hot write table can dominate write latency. Audit and drop unused indexes.
• Read the plan. EXPLAIN (ANALYZE, BUFFERS) in Postgres is the single most valuable debugging skill. "Seq Scan" on a big table in a hot path = missing/likely-unusable index.

3.3 Keys and identifiers — a decision with scaling consequences#

• Auto-increment BIGINT — compact, sequential (great for B-tree locality), but leaks volume, is a single-node counter (a bottleneck when sharded), and is guessable.
• UUIDv4 (random) — globally unique, generatable client-side (no round-trip), but random inserts destroy B-tree locality → page splits and write amplification, and it's wide (16 bytes) which bloats every secondary index.
• UUIDv7 / ULID (time-ordered) — the modern default: globally unique and time-ordered / k-sortable (the high bits are a timestamp, so inserts stay roughly sequential without a central counter). Not strictly monotonic — two IDs in the same millisecond from different generators aren't ordered (ULID has an optional monotonic mode within one generator) — but that's enough to preserve B-tree insert locality. Prefer these for distributed systems.
• Snowflake IDs (timestamp + machine + sequence) — sortable, distributed, 64-bit; what Twitter/Discord use for high-volume IDs.

Interview beat: choosing UUIDv7 over UUIDv4 "to preserve index locality and avoid page splits on a write-heavy table" is a small detail that signals you've operated a database under load, not just designed one on a whiteboard.

4. Scaling reads — replication and caching (and the consistency bugs they smuggle in)#

Reads usually outnumber writes 10:1 to 1000:1, so read scaling comes first. Every technique here buys throughput with staleness — name the staleness.

4.1 Replication topologies#

• Single-leader (primary/replica) — all writes go to the leader, which streams its WAL to read replicas. This is Postgres/MySQL's native model and the default answer. Reads scale horizontally by adding replicas. The catch: replication lag. An async replica is milliseconds-to-seconds behind; a read there can miss a write that already "succeeded" on the leader → read-your-writes violations.
  • Sync replication (leader waits for replica ack) removes lag-induced staleness but adds write latency and a liveness risk (a slow/dead replica can stall writes). Usually you run semi-sync (ack from at least one replica).
• Multi-leader — multiple leaders accept writes (often one per region). Great for multi-region write latency and offline clients, but write conflicts are now possible and must be resolved: last-write-wins (lossy), version vectors, CRDTs, or app-level merge. Reach for this only when you truly need multi-region writes.
• Leaderless (Dynamo-style: Cassandra, DynamoDB, Riak) — any replica takes writes; consistency is tuned with quorums. With N replicas, a write to W and a read from R, R + W > N guarantees the read set overlaps the latest write set (near-strong consistency). W=N, R=1 = fast reads/slow writes; W=1, R=N = the reverse; W=R=quorum (e.g., N=3, W=R=2) is the balanced default. Read repair and hinted handoff heal divergence. Note quorum overlap gives recency, not full linearizability, without extra machinery.

Interview trap: "just add read replicas" is only half an answer. The follow-up is "and how do you handle a user reading their own just-written data from a lagging replica?" Good answers: route that user's reads to the leader for a short window, pin the session to the leader after a write, read from a write-through cache, or use the store's causal-consistency/"read-your-writes" token if it has one.

Spring Boot pattern — route writes to the primary and reads to replicas with @Transactional(readOnly = true) plus a routing DataSource:

Kotlin

Java

(Two production caveats. (1) Timing: AbstractRoutingDataSource resolves its target when the connection is acquired — which, with eager acquisition or a plain DataSourceTransactionManager, happens at method entry before the body sets the ThreadLocal, silently sending "replica" reads to the primary. Fix by wrapping the router in a LazyConnectionDataSourceProxy so the physical connection is deferred to the first statement, or by deriving the key inside determineCurrentLookupKey() from TransactionSynchronizationManager.isCurrentTransactionReadOnly() instead of a hand-managed ThreadLocal. (2) Cleanliness: move the role-setting into an AOP aspect or custom annotation rather than touching RoutingContext by hand.)

4.2 Caching — the highest-leverage and most bug-prone tool#

Caching converts an expensive/consistent read into a cheap/possibly-stale one. Know the four strategies and their failure modes:

The two hard problems (both in §7 with fixes): cache invalidation ("the second hard thing in CS") and stampede/thundering herd (a hot key expires and 10,000 requests hit the DB at once).

Cache-aside with Spring's cache abstraction over Redis:

Kotlin

Java

Config note (language-neutral): set a TTL and a max size so the cache can't grow unbounded or serve infinitely-stale data — e.g., Redis entryTtl(Duration.ofMinutes(10)), plus a small random jitter on TTL to avoid synchronized mass-expiry (a stampede cause).

4.3 Other read-scaling tools#

• Materialized views — precompute an expensive join/aggregate; refresh on a schedule or via triggers/CDC. This is CQRS's read model in database form.
• CDN / edge cache — for cacheable HTTP responses and blobs; move reads off your infra entirely.
• Denormalized read replicas via CDC — stream changes (Debezium) into Elasticsearch/Redis/a read store shaped for the query. (See the CDC guide.)

5. Scaling writes — partitioning, sharding, and the shard-key decision#

Replicas don't scale writes — every replica must apply every write. When the write volume or dataset outgrows one node, you partition (shard): split the data across nodes so each holds a slice.

5.1 Vertical first, then horizontal#

Exhaust the cheap options before sharding, because sharding is a one-way door that costs you joins and transactions:

1. Vertical scaling — bigger box. Boring, effective, buys years. Do this first.
2. Read replicas (§4) — offload reads.
3. Separate workloads — move analytics off the OLTP box, archive cold data, split a monolith DB by bounded context (see the DDD guide).
4. Only then shard. Sharding adds permanent complexity; treat it as a last resort, not a flex.

5.2 Partitioning strategies#

Consistent hashing (with virtual nodes) is the key idea for hash sharding: adding/removing a node only remaps 1/N of keys instead of nearly all of them. This is how Cassandra/DynamoDB rebalance without a full reshuffle.

5.3 Choosing the shard/partition key — the highest-stakes decision#

The shard key determines your ceiling. A good key is:

• High cardinality — many distinct values so data spreads.
• Uniform access — no single value takes a disproportionate share of traffic (avoid hot partitions / celebrity problem: sharding a social graph by celebrity_id melts one node).
• Query-aligned — the key is present in your hot queries, so most queries hit one shard. Cross-shard queries require scatter-gather (fan out to all shards, merge) — slow, and they lose ordering/pagination guarantees.
• Rarely changed — moving a row between shards is expensive.

Classic examples: shard by user_id if queries are user-scoped; a chat app might shard by channel_id; a multi-tenant SaaS often shards by tenant_id (with special handling for whale tenants). Composite keys (e.g., tenant_id + entity_id) spread a hot tenant across shards.

What breaks after you shard: cross-shard joins (do them in the app or denormalize), cross-shard transactions (need 2PC or sagas — §6), secondary indexes (local index = scatter-gather reads; global index = distributed write), unique constraints across shards (need a global uniqueness service), and ORDER BY … LIMIT (scatter-gather + merge). Say these costs out loud; that's the senior move. Or: use a distributed SQL store (Spanner/CockroachDB/Vitess) that handles sharding for you — trading dollars/latency for not hand-rolling it.

5.4 The lost-update fix: optimistic locking with @Version#

The most common write-correctness bug at Read Committed (Postgres default) is the lost update: two requests read an entity, both mutate it, the second save clobbers the first. JPA's optimistic locking solves it with a version column — the UPDATE includes WHERE version = :expected; if another txn already bumped it, zero rows match and Hibernate throws OptimisticLockException.

Kotlin

Java

Optimistic vs pessimistic: use optimistic (@Version) when conflicts are rare — cheaper, no held locks, but you must handle retries. Use pessimistic (SELECT … FOR UPDATE, JPA @Lock(PESSIMISTIC_WRITE)) when conflicts are frequent or retries are expensive — it serializes access at the cost of held locks and deadlock risk. For a simple counter, an atomic UPDATE … SET n = n + 1 beats both (no read-modify-write race at all). For write skew, neither @Version nor a single-row lock is enough — you need Serializable isolation or to lock/materialize the thing the invariant is about.

6. Consistency across stores — the dual-write problem (where most real systems break)#

ACID ends at one database's boundary. The instant a single operation must update two systems — DB + Kafka, DB + cache, DB + search index, two microservice databases — you have the dual-write problem: there is no shared transaction, so a crash between write #1 and write #2 leaves the systems inconsistent. This is the backend distributed-systems bug, and interviewers probe it constantly.

Why not just use 2PC (two-phase commit)? A coordinator asks all participants to prepare, then commit. It gives atomicity but: it's blocking (if the coordinator dies after prepare, participants hold locks indefinitely), it kills availability (any participant down = no commit), it scales poorly, and many modern stores (Kafka, most NoSQL) don't support it well. So at scale, 2PC is usually the wrong answer — knowing why is the point.

The patterns that replace it (each has a full guide in this folder):

• Transactional outbox — write your business row and an "event to publish" row in the same local transaction (so it's atomic), then a separate relay publishes the outbox rows to Kafka. No dual write — just one DB transaction plus an at-least-once relay. (→ inbox/outbox guide.)
• Change Data Capture (CDC) — don't write events at all; tail the DB's WAL (Debezium) and turn committed changes into an event stream. The log is the source of truth. (→ CDC guide.)
• Saga — a multi-step business transaction across services becomes a sequence of local transactions, each with a compensating action to undo on failure. Orchestrated (a coordinator drives) or choreographed (services react to events). Trades atomicity for eventual consistency + explicit rollback. (→ saga guide.)
• Idempotency + at-least-once — since exactly-once delivery is largely a myth across a network, make consumers idempotent (dedupe on a business key / event id) so redelivery is safe. This underpins all of the above.
• Event sourcing — store the events as the source of truth and derive state; there's no dual write because state and event are the same thing. (→ event-sourcing guide.)

The senior framing: "I won't do a dual write. I'll make the two updates atomic through one store — outbox in the same transaction, or CDC off the WAL — and make the downstream consumer idempotent. Exactly-once is a delivery fiction; at-least-once + idempotent processing is the real guarantee."

7. Operational best practices — the stuff that separates "designed it" from "ran it"#

Staff engineers get asked "how would you operate this?" These are the practices that signal real experience.

Schema migrations must be backward-compatible (expand/contract). You cannot atomically deploy code + schema across a fleet. Never rename/drop a column in one step. Use expand → migrate → contract: (1) add the new nullable column/table (old code ignores it), (2) deploy code that writes both and backfills, (3) switch reads to new, (4) stop writing old, (5) drop old — each step independently deployable and reversible. Add indexes concurrently (CREATE INDEX CONCURRENTLY in Postgres) so you don't lock the table. Tools: Flyway/Liquibase for versioned, ordered, checksummed migrations.

Connection pooling is not optional. Each Postgres connection is a backend process (~MBs of RAM); a few hundred exhausts the server. Cap the app pool (HikariCP) and, at scale, front the DB with a pooler (PgBouncer in transaction mode). A p99 latency spike under load with idle CPU is almost always pool exhaustion or lock waits, not the query.

Backups you haven't restored are not backups. Automated snapshots + WAL archiving for point-in-time recovery (PITR), and periodically restore-test them. Know your RPO (how much data you can lose) and RTO (how long to recover), and design backups to meet them.

Bound your data. Every table grows forever unless you decide otherwise. Plan retention/TTL, partition by time (Postgres declarative partitioning) so you can drop old partitions cheaply, and archive cold data to object storage. Unbounded growth is a slow-motion outage.

Observability that a DBA would recognize: track p50/p95/p99 latency (averages hide the pain), slow-query logs, replication lag, connection pool saturation, lock waits/deadlocks, cache hit ratio, and disk/IOPS headroom. pg_stat_statements to find the queries that actually cost you.

Security & compliance: encrypt in transit (TLS) and at rest; least-privilege DB roles (the app doesn't need DROP TABLE); never store secrets in the schema; know where PII lives for GDPR/CCPA deletion; audit access. Column/field-level encryption or tokenization for the most sensitive fields.

Multi-tenancy is a design axis: shared table with tenant_id (cheapest, noisy-neighbor + blast-radius risk), schema-per-tenant (middle), or database-per-tenant (strongest isolation, operationally heavy). Pick per your isolation and scale needs.

8. When to use which type of database — in depth#

This is the section interviewers are really testing. The wrong instinct is to memorize "use X for Y." The right instinct is a decision procedure you run out loud, followed by knowing each store's sweet spot and — just as important — its anti-patterns.

8.1 The decision procedure (say this out loud)#

Ask, in order:

1. What are the top few queries by volume, and what shape are they? Point lookup by key? Range/scan? Multi-hop relationship? Full-text? Aggregation over billions of rows? The dominant access pattern is the strongest signal.
2. What's the consistency requirement — per operation? Money/inventory/uniqueness/locks → strong. Feed/analytics/recommendations/counters → eventual is fine. It's rarely uniform across the app.
3. What's the scale? Dataset size, write QPS, read QPS, growth rate. Single node handles far more than people think (a well-tuned Postgres does tens of thousands of TPS). Don't design for Google's scale on a startup's data.
4. What's the data shape? Flat rows? Nested aggregates? A graph? Time-stamped points? Large binaries? Documents/text?
5. What are the failure/latency requirements? Multi-region? Offline-capable? p99 budget? Data residency?

Then apply the defaults heuristic:

Start with a single relational database (Postgres). Add a specialized store only when a specific access pattern provably outgrows it — and say what pattern that is. Postgres alone covers relational, JSON documents (jsonb), key-value, full-text (tsvector), geospatial (PostGIS), vectors (pgvector), queues (SKIP LOCKED), and time-series (partitioning/TimescaleDB) up to surprisingly large scale. "Boring tech" is a feature: fewer moving parts, one consistency model, one thing to operate. Reach for a second store when you can name the pattern Postgres can't serve — "full-text relevance ranking across 100M docs," "1M writes/sec of telemetry," "6-hop graph traversal," "petabyte analytical scans." Polyglot persistence is powerful but each new store is a permanent operational + consistency tax (now you have a dual-write problem, §6).

8.2 Store-by-store: reach for it when… / avoid when…#

Relational (Postgres, MySQL) — the default.

• Reach for it when: you have entities with relationships; you need ACID transactions, joins, constraints, and ad-hoc/evolving queries; consistency matters; scale is "normal" (which is most systems). Financial data, orders, users, inventory, anything with invariants.
• Avoid / augment when: you need >single-node write throughput that vertical scaling + read replicas can't meet; truly schemaless high-variance data; specialized access (full-text at scale, graph traversal, columnar analytics).
• Postgres vs MySQL: Postgres for richer features (types, jsonb, extensions, SSI serializable, partial/expression indexes); MySQL for the huge ecosystem and simple read-heavy web workloads. Either is a fine default.

Distributed SQL / NewSQL (Spanner, CockroachDB, YugabyteDB, TiDB, Vitess) — relational that scales horizontally.

• Reach for it when: you've genuinely outgrown single-node writes but still need SQL + strong consistency + transactions, especially multi-region. It hides sharding from you.
• Avoid when: single-node Postgres would do (you'll pay latency for consensus you don't need), or your team can't operate it. Don't reach here to look sophisticated.

Key-value (Redis, DynamoDB) — fastest possible get/put by known key.

• Reach for it when: access is always by primary key with no ad-hoc queries; you need extreme throughput and low latency; sessions, caches, feature flags, rate-limit counters, leaderboards (Redis sorted sets), shopping carts. DynamoDB when you want KV at massive scale with managed ops and predictable single-digit-ms latency and you can design keys around every access pattern up front.
• Avoid when: you need ad-hoc queries, joins, or you can't predict your access patterns — a KV store punishes "query by a non-key attribute" (forces scans or secondary-index sprawl). Redis as a primary store only with persistence/replication configured and the dataset fitting in RAM (it's memory-first).

Document (MongoDB, Couchbase, Firestore) — nested aggregates, flexible schema.

• Reach for it when: data is naturally a self-contained document (a product with variants, a CMS article, a user profile with nested prefs); the schema varies per record or evolves fast; reads/writes are mostly whole-document by id; you want to avoid joins by embedding. Good developer velocity early.
• Avoid when: you have many-to-many relationships and rich cross-document queries/transactions (you'll reinvent joins in app code); you need strong multi-document consistency broadly (modern MongoDB has multi-doc transactions, but leaning on them signals you may have wanted relational). "Schemaless" becomes "schema-in-application-code" — the schema doesn't disappear, it just stops being enforced.

Wide-column (Cassandra, ScyllaDB, HBase, Bigtable) — write-heavy, massive scale, tunable consistency.

• Reach for it when: enormous write throughput and horizontal scale with no single point of failure; time-series-ish or event data; you can model the table per query (query-first, one table per access pattern) and pick a good partition key; AP/multi-region availability with tunable quorum. Messaging, IoT, activity feeds, telemetry at hyperscale.
• Avoid when: you need ad-hoc queries, joins, or strong transactional consistency; you can't predict access patterns (Cassandra is unforgiving of unplanned queries — no efficient non-key filtering, no joins); your data volume doesn't justify the operational weight. This is a "you'll know when you need it" store; using it prematurely is a classic over-engineering tell.

Graph (Neo4j, Neptune) — relationships are the query.

• Reach for it when: the value is in multi-hop traversal — "friends of friends," fraud rings, recommendation paths, dependency/knowledge graphs, access-control hierarchies — where the same query in SQL is a pile of recursive self-joins that blow up. Graph DBs use index-free adjacency so traversal cost is proportional to the subgraph touched, not the total data size.
• Avoid when: relationships are shallow (1–2 hops — SQL joins are fine) or the workload is really aggregations/scans. A graph DB as your primary transactional store for non-graph workloads is a mismatch.

Search / inverted index (Elasticsearch, OpenSearch) — full-text and relevance.

• Reach for it when: full-text search, relevance ranking, fuzzy/typo-tolerant matching, faceted filtering, log/observability search. Almost always a secondary store fed by CDC/outbox from your system of record — not your source of truth (it's near-real-time and not designed for transactional durability).
• Avoid when: you just need LIKE '%x%' on a small table (Postgres full-text or a trigram index suffices) or you'd be treating it as your primary DB.

Time-series (TimescaleDB, InfluxDB, Prometheus) & columnar OLAP (ClickHouse, BigQuery, Snowflake, Redshift) — timestamped writes and/or big scans/aggregations.

• Reach for TSDB when: high-volume timestamped data with time-range queries, downsampling, retention/rollups — metrics, IoT, monitoring.
• Reach for columnar/OLAP when: analytical queries scan/aggregate huge row counts over few columns (dashboards, BI, ad-hoc analytics). Columnar storage + compression makes SUM(x) GROUP BY y over a billion rows fast; row stores (OLTP) are the wrong tool for this and vice versa.
• Avoid when: you need OLTP point updates/transactions (columnar stores are append/scan-optimized, poor at single-row updates). Keep OLTP and OLAP separate — don't run heavy analytics on your transactional primary; replicate/ETL/CDC into the analytical store.

Object / blob (S3, GCS) — large binaries, cheaply, durably.

• Reach for it when: images, video, backups, ML datasets, static assets, data-lake files. Store the blob in S3 and the metadata + URL in your database — never stuff large binaries into a relational row.
• Avoid when: you need low-latency random access, queries over content, or transactional semantics.

Append-only log (Kafka) — ordered event stream / backbone.

• Reach for it when: decoupling producers/consumers, event-driven architecture, buffering spikes, replay, stream processing, and as the transport for outbox/CDC. It's a log, not a database — great for "stream of events," not for "look up current state by key" (that's a KV/relational job, often a Kafka consumer materializing state into one). (→ Kafka guide.)

Vector (pgvector, Pinecone, Milvus) — semantic / nearest-neighbor search.

• Reach for it when: similarity search over embeddings — RAG, semantic search, recommendations, dedup. pgvector first if you're already on Postgres and at modest scale; a dedicated vector DB when you need billions of vectors with low-latency ANN.
• Avoid when: exact keyword match is what you actually need (that's a search index or SQL), or scale is tiny (a library/array is fine).

8.3 Worked mini-examples (pattern → choice)#

• "Design a URL shortener." Access is point lookup by short-code → KV/Redis hot path in front of a durable store; the mapping table itself fits fine in Postgres. Reads ≫ writes → cache aggressively. Global uniqueness of codes → a counter/Snowflake id or check-and-set.
• "Design a news feed." Fan-out on write (push to followers' feeds) → precomputed per-user feed in Redis/Cassandra; source posts in relational; the celebrity problem forces hybrid fan-out (push for normal users, pull for celebrities).
• "Design a payments/ledger system." Invariants + audit → relational with Serializable (or distributed SQL for scale), append-only ledger table, idempotency keys, outbox for downstream. Never eventual consistency on money.
• "Design product search for e-commerce." Relevance + facets → Elasticsearch as a read model fed by CDC from the relational system of record; carts in Redis; orders in relational. Polyglot, with the source of truth clearly named.
• "Design a metrics/monitoring platform." High-cardinality timestamped writes + range queries + downsampling → time-series/columnar (TimescaleDB/ClickHouse/Prometheus), not OLTP.

9. Problems & how to solve them (Problem → Root cause → Fix)#

These are the failures that actually happen — the selection mistakes and the runtime pathologies. Each is a Problem → Root cause → Fix triple.

Selection-time problems#

Chose NoSQL "to scale," now drowning in app-side joins.

• Root cause: picked a store by hype/schema-flexibility without modeling the queries; the data is actually relational (many-to-many, ad-hoc queries).
• Fix: choose by access pattern, not buzzword. If queries are relational, use relational. Migrating back is expensive — this is why "start with Postgres" is the safe default. If already committed, add a relational read model via CDC or accept denormalized duplication with a reconciliation job.

Premature sharding / premature distributed store.

• Root cause: designed for hypothetical Google-scale on startup data; took the one-way door of sharding and lost joins/transactions for load a single node would have handled.
• Fix: vertical scale + read replicas first; measure before sharding. If you truly need scale-out SQL, prefer a managed distributed SQL store over hand-rolled sharding.

Analytics queries crushing the OLTP database.

• Root cause: running heavy GROUP BY/scans on the transactional primary; row-store + OLTP tuning is wrong for analytics, and the load starves live traffic.
• Fix: separate OLTP and OLAP. Replicate/CDC/ETL into a columnar store (ClickHouse/BigQuery/Snowflake) or at minimum a dedicated read replica for analysts.

Large binaries in the relational database.

• Root cause: storing images/files as BYTEA/BLOB bloats the table, wrecks cache locality, and slows backups.
• Fix: blob in object storage (S3), metadata + URL in the DB.

Consistency & concurrency problems#

Lost update (two writers, one wins silently).

• Root cause: read-modify-write at Read Committed with no concurrency control.
• Fix: optimistic @Version (retry on conflict), pessimistic SELECT … FOR UPDATE, or atomic UPDATE … SET x = x + 1. (§5.4)

Write skew (invariant violated by two "valid" transactions).

• Root cause: Snapshot Isolation doesn't detect disjoint writes that jointly break an invariant.
• Fix: Serializable isolation (Postgres SSI — and handle serialization-failure retries), or materialize/lock the resource the invariant is about.

Read-your-writes violation ("I saved it and it disappeared").

• Root cause: reading from an async replica or a stale cache that lags the leader.
• Fix: route post-write reads to the leader for a window, pin the session, read from a write-through cache, or use the store's causal token. (§4.1)

Dual-write inconsistency (DB updated, Kafka/cache/search not — or vice versa).

• Root cause: two systems, no shared transaction, crash in between.
• Fix: transactional outbox or CDC + idempotent consumers; never a naive dual write. Avoid 2PC at scale. (§6)

Non-idempotent consumer double-applies a redelivered message.

• Root cause: at-least-once delivery + side effects that aren't idempotent (charged twice).
• Fix: dedupe on event id / business key (inbox table, unique constraint), make handlers idempotent. (→ inbox/outbox guide)

Runtime / performance problems#

N+1 queries (1 query for the list, N for each item).

• Root cause: lazy loading / ORM iterating associations; a page issues hundreds of small queries.
• Fix: batch with a join / JOIN FETCH / @EntityGraph / IN (…) batch; enable ORM batch fetching. Watch for it with query logging.

Missing index → full table scan on a hot path.

• Root cause: predicate/sort column not indexed, or a composite index whose leftmost-prefix doesn't match the query.
• Fix: EXPLAIN ANALYZE, add the right (composite/covering/partial) index; verify the planner uses it. (§3.2)

Hot partition / hot shard (one node melts, others idle).

• Root cause: low-cardinality or skewed shard key, or a monotonic key routing all writes to one partition (the celebrity/noisy-neighbor problem).
• Fix: higher-cardinality or composite shard key; salt/bucket hot keys; split whales onto dedicated capacity; consistent hashing for rebalancing. (§5.3)

Cache stampede / thundering herd (hot key expires, DB gets hammered).

• Root cause: many concurrent requests miss the cache simultaneously and all hit the DB.
• Fix: single-flight / request coalescing (one loader fills the cache, others wait), lock-and-refresh, TTL jitter, probabilistic early recomputation, or serve-stale-while-revalidate.

Cache invalidation staleness.

• Root cause: DB updated, cache entry not evicted/updated; readers see old data.
• Fix: update/evict on write (@CachePut/@CacheEvict), short TTLs as a backstop, or event-driven invalidation via CDC. Accept that some staleness window is inherent — bound it.

Connection pool exhaustion (latency spikes, idle CPU).

• Root cause: more concurrent DB work than pool connections; often long transactions or a slow query holding connections.
• Fix: size HikariCP correctly, add PgBouncer, shorten transactions, move slow work off the request path, fix the slow query. (§7)

Deadlocks / lock contention.

• Root cause: transactions acquire locks in inconsistent order, or hold them too long.
• Fix: consistent lock ordering, shorter transactions, lower isolation where safe, retry on deadlock, reduce hot-row contention (sharded counters).

Unbounded table growth → slow queries and failing backups.

• Root cause: no retention; the table grows forever.
• Fix: time-partitioning + drop old partitions, TTL/archival to cold storage, scheduled purges. (§7)

Deep offset pagination (OFFSET 1000000 gets slow).

• Root cause: the DB still scans and discards all skipped rows.
• Fix: keyset/cursor pagination — WHERE (created_at, id) < (:lastTs, :lastId) ORDER BY created_at DESC, id DESC LIMIT n. Constant-time regardless of depth. (Interviewers love this one.)

Split brain (two nodes both think they're leader).

• Root cause: a network partition with no quorum/fencing; both sides accept writes and diverge.
• Fix: quorum-based leader election (Raft/Paxos), fencing tokens, R + W > N; require a majority to be writable (this is the CP choice — you sacrifice availability of the minority side).

Compaction storms / write amplification (LSM stores).

• Root cause: write volume outpaces compaction; read latency and disk balloon.
• Fix: provision I/O headroom, tune the compaction strategy to the workload (leveled vs size-tiered), watch pending compactions as a first-class metric.

Keyset pagination — the fix for deep-offset slowness, in Spring Data:

Kotlin

Java

A dedicated index (tenant_id, created_at DESC, id DESC) — with id as the tie-breaker, and distinct from the partial/covering index in §3.2 — makes this an index range scan whose cost is independent of how deep you page, unlike OFFSET, which scans and throws away everything before it. (@Param bindings are shown explicitly; Spring Boot 3 compiles with -parameters/-java-parameters so they can be inferred, but naming them is the robust habit.)

10. Interview framing — how to sound senior on storage#

Lead with access patterns, not products. The instant someone says "design X," your first move is "let me enumerate the read and write patterns and the consistency requirements," then let the store fall out. Naming the product first is the junior tell.

Quantify. "Reads dominate ~100:1, dataset ~500 GB, ~2k write TPS, p99 < 50 ms" turns a vibe into a decision. Estimate even if you have to assume — it shows you know scale drives the choice.

State the trade-off you're accepting, and where you moved the pain. "I'll denormalize into a read model — faster reads, but now I own keeping it in sync, so I'll feed it via CDC and make it idempotent." Every good storage answer ends with "…and the cost of that is ___."

Default to boring, justify exotic. "I'd start with Postgres — it covers relational, JSON, full-text, and geo up to real scale. I'd only add Cassandra/Elasticsearch/etc. when [this specific pattern] provably outgrows it." This reads as experience, not timidity.

Separate the two "consistencies." Distinguish ACID-C (constraints) from CAP-C (linearizability), and isolation (concurrent txns) from replication consistency (across replicas). Interviewers listen for whether you blur these.

Own the distributed-data story. When data spans services/stores, immediately reach for outbox/CDC/saga + idempotency and explain why not 2PC. This is where staff candidates separate from senior.

Right-size consistency per operation. "Payments: serializable. Cart: read-your-writes. Recommendations: eventual." Refusing to apply one global consistency level is a maturity signal.

Common wrong answers / traps to avoid:

• "NoSQL scales, SQL doesn't." (False — distributed SQL exists, and a single Postgres scales further than most systems ever need.)
• "We'll use MongoDB because it's schemaless." (The schema moves into your code; it doesn't vanish.)
• "Just add read replicas." (…and read-your-writes? and write scaling?)
• "Two-phase commit." (Blocking, availability-killing; usually the wrong tool at scale.)
• "Cache it." (Which strategy? invalidation? stampede? staleness bound?)
• "Exactly-once delivery." (Largely a myth across a network — say at-least-once + idempotent.)
• Reaching for Cassandra/Kafka/graph to look sophisticated when the data and scale don't call for it.

11. Cheat-sheets#

Database type → when / avoid / structure

Isolation level → anomalies allowed (✅ = can still happen)

¹ Standard RR allows phantoms; MySQL/InnoDB's gap locking prevents them for locking reads; Postgres "Repeatable Read" is SI and prevents them.

Consistency models (strong → weak): linearizable → sequential → causal → read-your-writes → monotonic reads → eventual.

Scaling ladder: vertical → read replicas → cache → separate workloads (OLTP/OLAP, by context) → shard / distributed SQL. Go down only as far as the data forces you.

Caching strategies: cache-aside (default) · read-through · write-through · write-behind. Hard parts: invalidation + stampede.

Sharding keys: high cardinality · uniform access · query-aligned (single-shard hits) · stable. Enemies: hot partitions, cross-shard joins/txns.

Cross-store consistency: outbox / CDC / saga + idempotency. Not 2PC. Not "exactly-once."

Quorum: R + W > N for read/write overlap. N=3, W=R=2 is the balanced default.

12. Self-test (say the answers out loud)#

1. What does the "C" in ACID mean, and how is it different from the "C" in CAP?
2. State CAP precisely. Why is "pick 2 of 3" wrong? What does PACELC add, and why is the "else" branch the one that matters most days?
3. Your invariant is "an account balance may never go negative." Which anomaly threatens it under concurrent withdrawals, at what isolation level, and what are three different fixes?
4. Explain write skew with an example. Why doesn't Snapshot Isolation prevent it? What does?
5. A user posts a comment and it vanishes on refresh. What's the likely storage cause and three ways to fix it?
6. You "just added read replicas." What did that solve, what did it not solve, and what new bug did it introduce?
7. B-tree vs LSM-tree: which for a write-heavy telemetry ingester, which for read-heavy OLTP, and why in terms of amplification?
8. You must publish an event to Kafka whenever you write an order. Why is the naive dual write wrong, why not 2PC, and what do you do instead?
9. Given a multi-tenant SaaS where one tenant is 50× the others, how do you choose a shard key, and what breaks after you shard?
10. Walk through choosing storage for: a payments ledger, a product search page, a real-time metrics dashboard, and a social feed. Name the store and the consistency level for each.
11. Why is OFFSET 1000000 LIMIT 20 slow, and what's the constant-time alternative?
12. When would you not reach for Postgres, and what's the specific access pattern that forces the switch?

Lineage & sources#

The stable fundamentals here — ACID, CAP (Brewer 2000; Gilbert & Lynch's 2002 proof), PACELC (Abadi 2012), isolation levels and anomalies (the SQL standard + Berenson et al. "A Critique of ANSI SQL Isolation Levels," 1995), snapshot isolation and write skew (both introduced in Berenson et al. above; Fekete et al. 2005 later showed how to make SI serializable — the basis for Postgres's SSI), Dynamo-style leaderless replication (Amazon Dynamo paper, 2007), LSM-trees (O'Neil et al., 1996), and consistency models — are covered in depth in Martin Kleppmann's Designing Data-Intensive Applications, the single best source for this material. Engine-specific behavior (Postgres default Read Committed, its Repeatable Read = SI and Serializable = SSI; MySQL/InnoDB default Repeatable Read with gap locking) is version-dependent — confirm against the docs for your exact version before quoting it in a design review. Product capabilities evolve fast (managed distributed SQL, vector support in Postgres, etc.); treat named-product specifics as "true as of writing, verify for your version."

This guide connects to the others in this folder: CQRS (split read/write models), saga (cross-service transactions), inbox/outbox & CDC (the dual-write fix), event sourcing (the log as source of truth), Kafka/EDA (the event backbone), and DDD (bounded contexts → one database per context).