Database Fundamentals

"We'd use a database" is never the answer — the interviewer wants to know which kind, and what specifically it buys the design. This lesson is the handful of database concepts that come up often enough to be worth having crisp, not just familiar.

SQL vs. NoSQL

The honest framing: SQL is the safer default when the data has real relationships and the write volume doesn't demand horizontal scale from day one. Reach for NoSQL when the access pattern is simple and known in advance (a key-value lookup, a time-ordered log) and horizontal write scale matters more than flexible querying.

Indexing

An index turns a lookup on a column from a full table scan into something closer to O(log n). It isn't free: every index has to be updated on every write to the indexed column, and a table with five indexes on a write-heavy path is paying that cost five times per write. The interview-relevant version of this: naming which column to index and why — usually whatever the most common WHERE clause filters on — is worth more than saying "we'd add an index" generically.

Normalization vs. denormalization

A normalized schema stores each fact once and joins to assemble a view — no duplication, but every read pays a join cost. A denormalized schema copies data across rows to avoid the join — cheaper reads, at the cost of having to update every copy when the source fact changes. The News Feed case study's fan-out-on-write approach is denormalization in practice: a post's content gets copied into every follower's feed rather than joined at read time, specifically because that read path is by far the hottest one in the system.

Replication

Every "add a read replica" line assumed elsewhere in this track means something specific: a leader accepts all writes, and one or more followers copy those writes and serve reads. This is what buys the redundancy Reliability names, and it's also what "replica" means every time Consistency and CAP uses the word.

Synchronous vs. asynchronous. A synchronous replica acknowledges a write before the leader confirms it to the client — the write is guaranteed to survive losing the leader, at the cost of write latency bounded by the slowest synchronous replica. An asynchronous replica acknowledges after the fact — writes are fast, but a leader crash right after acknowledging a client can lose the most recent writes that hadn't reached the replica yet. Most systems pick asynchronous for most replicas and, at most, one synchronous replica for the specific data that can't tolerate that loss window (Postgres's synchronous_commit is configurable per-transaction, not just per-cluster, for exactly this reason).

Replication lag is the delay between a write landing on the leader and that write becoming visible on a follower. It's the concrete mechanism behind eventual consistency — a read served by a lagging follower can return a stale value even though the leader itself is fully up to date, which is exactly the gap DynamoDB's eventually-consistent read option is naming explicitly rather than hiding.

Failover. When a leader dies, something has to promote a follower to the new leader — either an operator, or, in production systems, an automated process that detects the failure and promotes whichever replica has the most up-to-date data. Naming this step explicitly matters: a design that just says "the replica takes over" glosses over how the system agrees on which replica, and what happens to writes that were in flight when the old leader died.

Transactions and ACID

A transaction groups several writes so they either all succeed or all fail together — the property How to Prepare already named as "what a transaction actually buys you": Postgres won't let a payment get marked "captured" while the matching order row fails to insert, even mid-crash. ACID names the four guarantees a real transactional database provides — Atomicity (all-or-nothing), Consistency (a transaction moves the database from one valid state to another), Isolation (concurrent transactions don't see each other's half-finished work), Durability (once committed, a crash can't undo it). Most NoSQL stores relax one or more of these — usually isolation or cross-row atomicity — in exchange for horizontal write scale, which is the actual trade-off being made every time someone says "we'd use DynamoDB instead of Postgres here."