Part 12 · Applied & interviews

Production Systems Atlas: Where Real Databases Sit in the Design Space

The signpost lesson (26) closed Part 11 by mapping the DDIA 2nd edition's expanded scope back onto the mechanisms this track built. This lesson opens the applied part by turning those mechanisms into a coordinate system, not a catalog. We use the axes a data system varies along: the storage engine (append log, B-tree, LSM, columnar — lessons 03/04), the replication model (single-leader, multi-leader, leaderless quorum — 08/09), the partitioning scheme (hash vs range — 11), the transaction and isolation story (13), the consistency model (eventual through linearizable — 16), and whether the system is built around an ordered durable log (19). Every real database is a point in that space: a specific bundle of choices, optimized for one access pattern and paying for it everywhere else. This lesson places the systems you will actually be asked about — PostgreSQL, MySQL, Cassandra, DynamoDB, Riak, Bigtable, HBase, Kafka, Pulsar, Kinesis, Redis, Elasticsearch, OpenSearch, Spark, Flink — at their coordinates, says what each optimizes and what it gives up, and ends with the rule that turns the atlas into a decision: choose by the access pattern plus the invariant you must hold.

1 · The axes, and the master map

To place a system you score it on the same six axes every time. These are exactly the axes the track built, so each is a link back to where it was derived, not a new idea:

Scored that way, the seven families fall out cleanly. Here is the whole map; the rest of the lesson defends each row.

Read the table as a map of trade-offs, not a ranking. Moving down the rows you trade away multi-object transactions and joins for write throughput and availability; moving from hash to range partitioning you trade even key spread for ordered scans; the last two rows are not stores at all — search is a derived index over a real system of record, and Spark/Flink are compute that reads and writes the others. The next sections give each family its coordinates and the one-sentence "optimizes / gives up".

2 · Relational OLTP: PostgreSQL vs MySQL

The relational family is the default system of record: a B-tree storage engine (lesson 03) for fast point reads and in-place updates, a heap or clustered layout for rows, single-leader replication (lesson 08) so every write passes through one node and inherits a total order for free, and — the feature that defines the family — multi-object ACID transactions with real isolation levels (lesson 13), up to serializable. This is the only family in the atlas where you can hold an invariant that spans several rows ("debit one account, credit another, and never let the sum drift") inside one atomic, isolated transaction.

Optimizes: correctness over rich relational data — joins, multi-row invariants, and the strongest isolation in the atlas. Gives up: horizontal write scale. One leader is a write bottleneck and a single point of failure; scaling writes means application-level sharding or a distributed-SQL layer (Spanner/CockroachDB/Vitess), which buys scale by adding consensus (lesson 17) and giving up some of the single-node simplicity. The practical split between the two: PostgreSQL leans toward feature richness and true serializability via SSI; MySQL/InnoDB leans toward a clustered-index layout that makes PK lookups and PK-ordered scans very fast. Both are the right default when the data has invariants you cannot afford to break — and both expose their replication log as the CDC source the rest of the track's derived-data pipelines (lesson 20) feed from.

3 · The Dynamo lineage: Cassandra, DynamoDB, Riak

Amazon's 2007 Dynamo paper defined this family, and Cassandra, DynamoDB, and Riak are its descendants. The coordinates are the opposite corner of the space from relational: an LSM-tree engine (lesson 04) that turns every write into a cheap sequential append plus background compaction, so write throughput is enormous; leaderless quorum replication (lesson 09) where the client writes to w of n replicas and reads from r, sized by the condition w + r > n when you need fresh reads; hash partitioning via consistent hashing (lesson 11) for even spread and cheap rebalancing; and only single-row (single-item) atomicity — no multi-row transactions in the classic design.

Optimizes: write throughput, predictable point/range-within-partition lookups, and multi-region availability with no failover step (there is no leader to fail over — lesson 09). Gives up: joins, multi-row transactions, and ad-hoc queries — you can only query efficiently along the partition and clustering keys you chose up front, and choosing them wrong means a full scan or a redesign. The default eventual consistency also means a read can miss a just-acked write unless you pay for a quorum read. The classic interview tell: if the prompt says "10× write volume, key-based lookups, must survive a region outage," this family fits; if it says "report joining three entities with a balance that must always reconcile," it does not.

4 · The Bigtable lineage: Bigtable, HBase

Google's Bigtable (and its open-source clone HBase on HDFS) shares the LSM engine with Dynamo but flips two axes. Storage is the same LSM SSTable design (lesson 04) — sorted string tables flushed from an in-memory memtable and merged by compaction — but partitioning is range, not hash (lesson 11): rows are kept sorted by row key and split into contiguous tablets (Bigtable) or regions (HBase). And replication is not leaderless: a single master (coordinated via Chubby/ZooKeeper, lesson 17) assigns tablets to servers, giving strong, single-row consistency rather than tunable quorums.

Optimizes: ordered scans over an enormous sorted key space — "give me every event for this user between these two timestamps" reads one contiguous tablet, which hash partitioning cannot do. Strong per-row consistency, wide sparse columns, and time-series layouts all fall out of the sorted-range design. Gives up: even load when keys are sequential (monotonic keys create the hot-tail problem the diagram shows — the standard fix is to salt or hash-prefix the key, trading the clean scan for spread), and anything beyond single-row atomicity. No joins, no multi-row transactions. This is the family for wide-column, scan-heavy, time-ordered data where the access pattern is a range over a sorted key.

5 · Logs, memory, search, and compute

Kafka vs Pulsar (and Kinesis): the partitioned durable log

A log system is not a database you query by key — it is the ordered durable log itself (lessons 03, 19) exposed as a product: an append-only sequence, partitioned for throughput (lesson 11), replicated, and replayable. Within one partition there is a total order (a monotonic offset); across partitions there is none, which is why the partition key is the ordering contract. This is the event backbone and CDC transport the whole derived-data story (lesson 20) runs on.

Optimizes: durable, ordered, replayable event transport at high throughput — the substrate for CDC, stream processing, and event sourcing. Gives up: random-access reads and queries by content — you consume sequentially by offset, not "find the record where x = y." Pulsar's broker/segment split is its main differentiator from Kafka: stateless brokers make scaling and rebalancing cheaper at the cost of an extra moving part (BookKeeper). Kinesis is the managed AWS analog — same partitioned-log idea, less to operate, AWS-shaped limits.

Redis: in-memory data structures

Redis keeps its dataset in RAM, which is why a point lookup is sub-millisecond and why it is the default cache, rate limiter, and leaderboard. It is not just a key-value cache — it serves data structures (strings, hashes, sorted sets, streams) with atomic operations on a single thread, so each command is atomic without locks. Durability is optional and WAL-shaped (lesson 03): AOF (append-only file — every write command logged, like a WAL) and/or RDB (periodic point-in-time snapshots); you trade fsync frequency against durability. Replication is single-leader async (lesson 08), so a primary can ack a write and die before the replica sees it — Redis is not a linearizable system of record. Redis Cluster partitions by 16384 hash slots (lesson 11) mapped to nodes. Optimizes: latency and atomic per-key operations. Gives up: durability guarantees and dataset size bounded by RAM and budget; treat it as a fast derived layer in front of a real store, not the source of truth.

Elasticsearch / OpenSearch: the inverted index

Search engines (built on Apache Lucene) store an inverted index — term → list of documents containing it — in immutable segments that are written once and merged, behaving like an LSM-tree (lesson 04): writes append new segments, background merges compact them, and a document is only near-real-time visible (after a refresh, typically ~1 s), not instantly. Data is sharded by hashing the document id, each shard is a primary with replicas (lessons 08/11). The load-bearing point for an interview: Elasticsearch is not a system of record. It is a derived full-text/analytics index over a copy of data whose source of truth lives in Postgres/MySQL/Kafka — populated by CDC (lesson 20), and rebuildable from that source if a shard is lost. Optimizes: full-text relevance ranking and aggregations over large document sets. Gives up: being authoritative — no real transactions, eventual visibility, and a refresh/merge cost on every write.

Spark vs Flink: compute, not storage

The last family stores nothing of its own; Spark and Flink are processing engines that read from and write to the systems above. Both partition work by key (the shuffle/keyBy — lesson 18) and both achieve exactly-once effects via checkpointing plus idempotent sinks (lesson 19). The difference is the execution model:

Optimizes (Spark): a single engine for heavy batch and tolerable-latency streaming, with a huge ecosystem. Optimizes (Flink): genuine low-latency, event-time-correct streaming with first-class large state. Both give up: being a store — they need a durable source (Kafka, a lake) and a real sink, and their correctness guarantees only hold end-to-end if the sink is idempotent (lesson 20).

6 · The rule: choose by access pattern + the invariant

The atlas exists to be collapsed into one decision. Do not start from a product ("we use Postgres" / "let's put it in Mongo"). Start from two questions, in this order:

Access pattern picks the engine; the invariant picks the consistency. A "user profile read by id, edited rarely, must never lose a concurrent edit" is a point lookup (LSM/hash) with a no-data-loss invariant (version vectors + siblings, lesson 09) — Riak/DynamoDB territory. A "ledger with a balance that must reconcile" is relational and single-leader, full stop, regardless of volume — and if volume forces sharding, you reach for distributed SQL that keeps the invariant via consensus (lesson 17), not for a Dynamo store that abandons it. A "search box over products" is an inverted index derived from the relational source of truth via CDC (lesson 20) — never the source itself.

Checkpoint exercise

Where this points next

The atlas tells you where a system sits when it is healthy. But interviews and on-call both live in the moments when it is not: the leader that died before its last write replicated, the cache key that expired and stampeded the database, the CDC consumer that fell hours behind. Lesson 28 turns each axis of this atlas into a failure timeline — a time-ordered ASCII trace of what actually happens, who the actors are, and what the user sees — and pairs it with the quantitative drills (Little's Law latency budgets, partition sizing, compaction bandwidth, replication-lag and CDC-backlog arithmetic) that let you put real numbers on a design instead of hand-waving "it scales." The atlas is the map; lesson 28 is what happens when the map meets a fault.

Interview prompts

• Place Cassandra and PostgreSQL on every axis and contrast what each gives up. (§2, §3 — Postgres: B-tree + single-leader + multi-object ACID, gives up write scale; Cassandra: LSM + leaderless quorum + hash partitioning + single-row atomicity, gives up joins and multi-row transactions. Both expose a replication log as a CDC source.)
• Bigtable and Cassandra both use LSM storage — why pick one over the other? (§3, §4 — partitioning. Cassandra hashes keys for even spread and point lookups; Bigtable range-partitions so ordered scans over a sorted key are cheap. Choose Bigtable for "all events for X between two times," Cassandra for even-spread key lookups; mind Bigtable's hot-tail on monotonic keys.)
• Why is Elasticsearch not a system of record, and how should it be fed? (§5 — near-real-time eventual visibility, no real transactions, and a derived inverted index that can be lost and rebuilt; populate it from the relational/Kafka source of truth via CDC (20), never dual-write.)
• What does Redis actually guarantee about durability and consistency? (§5 — in-memory with optional AOF (WAL-like) and/or RDB snapshots; single-leader async replication, so an acked write can be lost on failover. Single-key ops are atomic on its single thread, but it is not a multi-key linearizable source of truth — use it as a cache.)
• Contrast Kafka and Pulsar; where does Kinesis fit? (§5 — both are partitioned, replicated, replayable logs with per-partition order. Kafka couples broker and storage; Pulsar splits stateless brokers from BookKeeper storage for cheaper independent scaling/rebalancing. Kinesis is the managed AWS equivalent of the same log idea.)
• Spark vs Flink for a streaming pipeline? (§5 — Spark Structured Streaming is micro-batch (sub-second to seconds, simpler, one engine for batch too); Flink is true per-event streaming (ms latency, first-class large keyed state, event-time windows). Both reach exactly-once only with checkpointing plus an idempotent sink (19/20).)
• State the rule for choosing a data store and give a worked example. (§6 — access pattern picks the engine/partitioning; the hardest invariant picks the replication/consistency. A reconciling ledger ⇒ relational single-leader regardless of volume; a 2 M writes/s sensor range-scan ⇒ Bigtable/HBase; a search box ⇒ Elasticsearch derived via CDC. Never pick by brand familiarity.)