System Design Zero to Architect : Part 7: Databases: Data Models, Schema Design, Indexes, Query Plans, Transactions, Isolation, MVCC and Backups

Season 1: Foundations · Part 7 · Prerequisites: Parts 1 to 6 (storage in Part 4, REST in Part 6) · Reading time: about 95 minutes

Part 6 ended with an API that creates orders, paginates histories and retries safely. Every one of those behaviors rests on a promise made by the database underneath: that an order is stored once, that a half-finished write never becomes visible, and that two requests touching the same row do not corrupt each other. This post explains how a database keeps those promises, and where each promise stops. Every experiment here ran against a real PostgreSQL 16 server (and SQLite for one comparison), and the appendix shows how to reproduce them.

This post covers all of the database fundamentals in one place, so it is long. It has four movements: shape (data models, schema, normalization), speed (joins, indexes, query plans), correctness (transactions, isolation, MVCC, locks) and survival (backups and point-in-time recovery). Read it in sittings if you need to. The cheat sheet at the end condenses all of it onto one page.

Six incidents, all about stored data

FoodRush now has a million orders. (The incidents below are illustrative. Each follows a pattern teams meet in production, and the experiments later in this post measure how large each effect can be.)

1. A server is restarted in the middle of a refund. The customer’s wallet was credited, but the matching debit on the restaurant’s balance never happened.
2. A restaurant changes its name. Support finds the old name still printed on tens of thousands of historical orders, and some orders show the new name.
3. The order list screen makes 101 database calls to render 100 orders, and it is fast on a laptop and slow in production.
4. Two couriers go off shift at the same moment. Each request checked “at least one courier must remain on duty”, saw one other courier, and signed off. Nobody is on duty.
5. Looking up a customer’s orders takes over 100 ms and gets slower every month, although the query is trivial.
6. Someone runs DELETE FROM orders with a mistaken condition and half the table is gone. The last backup is from last night.

Each has a standard remedy: transactions, normalization, set-based queries, the right isolation level, an index, and point-in-time recovery. The sections below build each one from first principles.

1. Why not just use files?

Part 4 showed how data reaches a disk. A file is the simplest place to put it. The trouble starts when one logical change needs two physical writes.

Experiment. Move 10 units from account A to account B (the total must stay 200). In each of 200 trials, a child process was killed between the two writes in about half of the runs. The same scenario ran three ways.

With files, every kill that landed between the writes left 10 units created from nothing. With a transaction, the engine either applies both changes or neither, because it records the intent in a log first and can redo or undo it on restart.

A database is more than storage. It is storage plus four things a file does not give you: a way to group changes atomically, a log that survives crashes, rules that reject bad data, and a way to let many clients work at once without trampling each other. The rest of this post is about those four things.

2. What is inside a database engine

Figure 1 is the map. A statement flows left to right:

1. Parser. Checks the syntax and turns text into a tree.
2. Planner (optimizer). Chooses how to execute: which indexes, which join algorithm, which order. It estimates the cost of each option from statistics about the data.
3. Executor. Runs the chosen plan, pulling rows through the tree.
4. Buffer pool. Data lives on disk in fixed-size pages (8 KB in PostgreSQL). Pages the executor touches are cached in memory. A modified page is marked dirty and written back later, in batches.
5. Write-ahead log (WAL). Before a change is considered committed, a record describing it is appended to a sequential log and flushed to stable storage. Data pages can then be written lazily, because the log is enough to rebuild them after a crash.

The split between random page writes to the data files and sequential appends to the log is the central design idea. Sequential appends are fast on every kind of storage (Part 4), and they are all a commit has to wait for.

3. Data models

A data model is the way the database lets you describe and query data. The five in common use:

A sensible default. Start with a relational database unless a specific access pattern or scale requirement proves otherwise. It gives you constraints, transactions and flexible queries, which are expensive to retrofit later. The measurements in section 18 show that the relational shape is not slower for key lookups, so the usual reason to leave it is scale or a shape that genuinely does not fit.

4. The relational model

A relation is a table: a set of rows, each with the same typed columns. The model stays small because a handful of constraints do the heavy lifting:

• Primary key. Uniquely identifies a row. No two rows share it and it is never null.
• Foreign key. A column that must match an existing row in another table. You cannot create an order for a customer that does not exist, and you cannot delete a restaurant that still has orders (unless you say how to cascade).
• Unique. No duplicates in this column or combination of columns, such as customers.email.
• Not null. The value must be present.
• Check. An arbitrary rule on a row, such as total_cents >= 0 or status IN ('PLACED', 'PREPARING', 'PICKED_UP', 'DELIVERED').

CREATE TABLE orders (
  id            bigint PRIMARY KEY,
  customer_id   bigint NOT NULL REFERENCES customers(id),
  restaurant_id bigint NOT NULL REFERENCES restaurants(id),
  courier_id    bigint REFERENCES couriers(id),
  status        text   NOT NULL CHECK (status IN ('PLACED','PREPARING','PICKED_UP','DELIVERED')),
  total_cents   int    NOT NULL CHECK (total_cents >= 0),
  created_at    timestamptz NOT NULL DEFAULT now()
);

Constraints belong in the database even when the application also validates. Applications have bugs, there is often more than one application, and people run one-off scripts. A constraint is the single rule that every writer must obey.

Snapshotting on purpose. order_items.price_cents copies the price from the menu at the time of the order. That looks like duplication but it is correct: the price a customer paid is a historical fact, and it must not change when the menu changes. Normalization (next section) removes accidental duplication, not facts that happen to repeat.

5. Normalization

Normalization is a procedure for removing redundancy so that each fact is stored once. Start with one wide table:

order_id | customer_name | restaurant_name | restaurant_city | items
1001     | Asha          | Spice Route     | Chennai         | "pizza, naan, lassi"

• First normal form (1NF). Every cell holds one atomic value. The items list becomes one row per item, keyed by (order_id, line_no).
• Second normal form (2NF). Every non-key column depends on the whole key, not part of it. An item’s name depends on the item, not on the order, so it moves to menu_items.
• Third normal form (3NF). Every non-key column depends on the key and nothing else. restaurant_city depends on the restaurant, which depends on the order, so the city belongs to restaurants.

What it prevents are three anomalies:

• Update anomaly. Renaming a restaurant means changing many rows, and missing one leaves two names for the same restaurant.
• Insert anomaly. You cannot record a restaurant until it has an order.
• Delete anomaly. Deleting the last order erases everything you knew about the restaurant.

What copying actually costs

Experiment. One million orders across 2,000 restaurants, one restaurant owning 10 percent of the orders (100,000 rows). Two schemas: normalized (orders reference a restaurant row) and copied (restaurant name, address, city and phone repeated in every order row).

Renaming took about 1,281 times more work when the name was copied. And when the update was stopped halfway (50,000 of 100,000 rows), the same restaurant had 2 different names in the table. A transaction would prevent that visible split, but it would not make 100,000 writes any cheaper.

Reads tell the other half of the story: the join was within roughly 20 percent of the copied version, because a join on an indexed key against a small table is cheap. That is why the guidance is to normalize first and denormalize on evidence. Denormalization is a valid tool, for example a precomputed total or a copied display name on a hot read path, but it moves the cost from reads to every write and to every correction.

6. Joins and the life of a query

A join combines rows from two tables on a condition. The engine has three main strategies, and the planner picks by estimated cost:

EXPLAIN shows the plan the planner chose, and EXPLAIN (ANALYZE) runs it and adds actual rows and time per node:

EXPLAIN (ANALYZE)
SELECT o.id, r.name
FROM orders o JOIN restaurants r ON r.id = o.restaurant_id
WHERE o.status = 'PLACED';

When estimated and actual row counts differ by a large factor, the planner is working from stale statistics, and running ANALYZE usually fixes it. Indexes and plan reading are the next four sections.

7. Indexes: how a B-tree makes lookups cheap

Without an index, finding a customer’s orders means reading every row of the table. An index is a separate, sorted structure that maps column values to row locations. The default and most common kind is the B-tree:

• Pages are 8 KB. The root page points to internal pages, which point to leaf pages.
• Leaf pages hold the keys in sorted order, each with a pointer to the table row (the “heap” row). Leaves are linked, so a range or an ORDER BY is a walk along the leaf level.
• A lookup reads one page per level, then follows the pointer to the row.
• Inserts keep the order by splitting full pages, which is why indexes cost something on writes (section 8).

A B-tree serves =, <, >, BETWEEN, ORDER BY and prefix matches such as LIKE 'abc%'. It cannot serve LIKE '%abc' or a function of the column such as lower(email), unless you create an index on that same expression.

Measured. The experiment table had 1,000,000 orders, stored in random physical order, in a 149 MB table.

The height is the key fact. With about 364 entries per page, three levels address roughly 48 million keys and four levels about 18 billion (computed from the measured fan-out). Height grows with the logarithm of the row count, so lookups stay nearly flat as data grows. That is also why the (created_at, id) index in Part 6 made keyset pagination cost the same at any depth.

8. When the planner uses an index, and what an index costs

An index is not always the fastest way to read. Selectivity, the fraction of rows a query returns, decides it. Reading many rows through an index means many scattered page reads, while a sequential scan reads pages in order.

Experiment. SELECT sum(total_cents) FROM orders WHERE k <= X over 1,000,000 rows stored in random order, with the planner restricted to each access path in turn. The table was fully cached (warm), so these numbers favor random access more than a cold disk would.

Three observations:

• Below about 1 percent, an index wins by a large factor. At 10 rows the index scan was more than 300 times faster.
• A plain index scan degrades fastest. Each row costs a separate, scattered page visit, so at 50 percent it was five times slower than the sequential scan.
• The bitmap scan sits in between. It collects the matching row locations first, sorts them by page, and reads each page once. The planner’s choice is made from estimates, and it is not always right: at 10 percent it picked the bitmap scan (about 170 ms measured) when the sequential scan was faster (122 ms).

On a cold disk, random reads cost more than sequential ones (Part 4), so the sequential scan would win even earlier. That is an expectation from the storage numbers, not something measured here.

The cost of an index

Every index is a second copy of part of your data that must be kept in order. Experiment. Insert 300,000 rows into tables with 0, 1, 3 and 5 secondary indexes.

Five indexes made inserts 3.5 times slower and the table 64 percent larger. Updates of an indexed column pay the same price. The rule is to index what your queries filter, join and sort on, and to drop indexes nothing uses.

9. Composite and covering indexes

A composite (multi-column) index sorts rows by the first column, then by the second within it, and so on. Consider the FoodRush query:

SELECT id, total_cents FROM orders
WHERE restaurant_id = 77 AND status = 'PLACED'
ORDER BY created_at DESC LIMIT 20;

Experiment. The same query, on 1,000,000 orders stored in random order, with three index designs.

Times for the two indexed rows are the average over 300 random restaurants. The page counts come from one EXPLAIN (ANALYZE, BUFFERS) of restaurant 77. The single-column index finds the restaurant's 500 rows but must visit each one, filter by status and sort. The composite index delivers exactly the 20 wanted rows already in order, which is 32 times faster here, at the price of a larger index.

Column order and the leftmost prefix

A composite index can serve a query that filters its first column (or first several columns). It cannot efficiently serve a query that skips them. Experiment. Count orders in the last hour (3,565 rows) with two index designs.

The rule of thumb: put equality columns first, then the range or sort column. Design each composite index around the queries that will use it.

Covering indexes and index-only scans

If an index contains every column a query needs, the engine never visits the table. PostgreSQL lets you add non-searchable payload columns with INCLUDE:

CREATE INDEX ON orders (customer_id) INCLUDE (status, total_cents);

Experiment. SELECT status, total_cents WHERE customer_id = ? averaged over 300 customers.

The index-only scan touched a fifth of the pages and was 1.5 times faster on the server, with no table visits (heap fetches: 0). It needs the table to be vacuumed recently, because the engine must know which rows are visible without looking at the table. End to end, from the application, the gain is smaller than the server numbers suggest, because the network round trip dominates a 0.05 ms query.

10. Reading EXPLAIN (ANALYZE) and fixing slow queries

EXPLAIN prints the plan the planner chose without running it. EXPLAIN (ANALYZE, BUFFERS) runs the query and adds what actually happened. This is the plan for the composite-index query above:

Limit  (cost=0.42..82.09 rows=20 width=16) (actual time=0.014..0.030 rows=20 loops=1)
  Buffers: shared hit=23
  ->  Index Scan using ix_rsc on ix  (cost=0.42..257.68 rows=63 width=16) (actual time=0.014..0.027 rows=20 loops=1)
        Index Cond: ((restaurant_id = 77) AND (status = 'PLACED'::text))
        Buffers: shared hit=23
Planning Time: 0.056 ms
Execution Time: 0.044 ms

How to read it:

• Plan tree. Indented nodes feed their parent. Read from the innermost node outward.
• cost=a..b. The planner's estimate in arbitrary units: a before the first row, b for all rows. Use it to compare plans, not as a time.
• rows=63 versus actual rows=20. Estimated versus real. Large gaps (10 times or more) are the most common sign of a bad plan.
• actual time=a..b. Milliseconds to the first row and to the last row, per loop.
• loops=N. How many times the node ran. Multiply the per-loop time by it.
• Buffers: shared hit/read. Pages found in memory versus read from disk. This is the best measure of how much data a node touched.

Stale statistics

The planner estimates from statistics about each table. If the data changes a lot after statistics were gathered, estimates can be wildly off. Experiment. A table was analyzed with 1,000 rows, then 2,000,000 rows were added without re-analyzing. The query joins it to a 200,000-row table.

The same SQL, data and indexes ran 5.5 times faster once the estimate was right. Autovacuum re-analyzes tables automatically, but a bulk load can outrun it, so run ANALYZE after large imports.

A checklist for a slow query:

1. Run EXPLAIN (ANALYZE, BUFFERS) on the real query with realistic parameters.
2. Compare estimated and actual rows at each node. A big gap means stale statistics or correlated columns.
3. Look for a sequential scan on a large table that returns few rows. That usually means a missing or unusable index.
4. Look for a sort that spills to disk, or a nested loop with a huge loops count.
5. Change one thing (an index, a rewritten condition, fresh statistics) and measure again.

11. The N+1 query problem

The most common performance bug in applications that use an ORM is loading a list and then loading a related row for each item, one query at a time:

SELECT * FROM orders LIMIT 100;               -- 1 query
SELECT name FROM restaurants WHERE id = ?;    -- repeated 100 times

There are three shapes: N+1 (101 queries), a single join (1 query) and a batch that fetches the related rows with one IN or = ANY query (2 queries).

Measured on the same machine (median of 15 runs, local socket): N+1 took 16.8 ms, the join 0.91 ms and the batch 1.48 ms. That is already 18 times slower. The gap widens with distance, because each query costs a full network round trip (Part 5). The table below adds a round-trip time per query to the measured figures. These rows are modeled, not measured:

The lesson is to count queries per request, not only milliseconds per query. Most ORMs offer eager loading or batched loading; turn on query logging in development and look for repeated statements.

12. Transactions and ACID

A transaction is a group of statements treated as one unit:

BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 'A';
UPDATE accounts SET balance = balance + 10 WHERE id = 'B';
COMMIT;

ACID names what a transaction promises:

• Atomicity. All of it or none of it. A failure anywhere undoes the partial work.
• Consistency. The declared rules (keys, constraints) hold before and after. This is the odd one out: the database enforces the rules you declare, but only you define what a valid state is.
• Isolation. Concurrent transactions do not interfere, to a degree chosen by the isolation level (section 14).
• Durability. Once COMMIT returns, the change survives a crash.

The commit path and what durability costs

On commit the engine appends a commit record to the WAL and calls fsync to force it to stable storage. Only then does it tell the client the transaction is committed. The data pages themselves are flushed later in batches, because after a crash the log can rebuild them.

On this host, one fsync had a median of 0.25 ms. (This is a shared sandbox disk. A network disk or a spinning disk is slower, so the ratios transfer and the absolute numbers do not.)

Two ways to pay less per row follow directly. Batching many rows into one transaction amortizes one fsync over many rows (7 times faster at 100 rows per commit). Turning off the wait (synchronous_commit = off) also helps (3 times faster here), but it accepts that a crash can lose the last few acknowledged commits, a trade that is never acceptable for payments.

Group commit is the third way, and it needs no change in the application. When many clients commit at the same moment, the engine flushes the log once for all of them.

Throughput grew 3.4 times from 1 to 64 clients because concurrent commits shared flushes. (The 1-client figure here comes from a separate run from the 1,262 above, so expect a few percent of run-to-run difference.)

13. Concurrency anomalies, reproduced

Isolation is the hardest of the four properties, so this section reproduces each classic problem rather than only defining it. Every result comes from two real connections in PostgreSQL 16, orchestrated step by step.

• Dirty read. Transaction 2 reads a value transaction 1 has written but not committed.
• Non-repeatable read. A transaction reads the same row twice and gets different values because another transaction committed in between.
• Phantom read. A transaction runs the same query twice and the second time a new matching row appears.
• Lost update. Two transactions read a value, each computes a new value from it, and the second write overwrites the first.
• Write skew. Two transactions each read overlapping data, each make a decision that is valid alone, and write to different rows, together breaking a rule.

14. Isolation levels

The SQL standard defines four levels, from weakest to strongest: read uncommitted, read committed, repeatable read and serializable. PostgreSQL treats read uncommitted as read committed, and its default is read committed.

Three observations from the table:

• The default is weaker than most people assume. Under read committed, four of the five anomalies occurred.
• Repeatable read is not serializable. PostgreSQL’s repeatable read uses snapshot isolation. It prevented phantoms (stronger than the standard requires) and lost updates, but write skew still happened. Incident 4, the two couriers going off shift, is exactly this.
• Serializable works by aborting. Instead of blocking, PostgreSQL detects a dangerous pattern and aborts one transaction with SQLSTATE 40001. The application must be prepared to retry the whole transaction. A serializable level without a retry loop is a bug.

Other engines differ in which anomalies each level allows and how they enforce them, so always check the documentation of the engine you run.

15. Avoiding lost updates

The classic race: both transactions read v = 10, both compute 11, both write 11. One increment vanished and nothing reported an error.

Experiment. 8 threads each performed 250 increments on a single row (2,000 expected), five different ways, under read committed unless noted.

The unguarded version lost 1,712 of 2,000 increments, silently. The remedies, in order of preference:

1. Push the arithmetic into the database with a single atomic UPDATE ... SET v = v + 1. It is the fastest and the simplest, and it needs no retries.
2. Lock the row with SELECT ... FOR UPDATE when the new value depends on logic that cannot be one expression. Other writers wait their turn.
3. Optimistic concurrency with a version column (UPDATE ... WHERE id = ? AND ver = ?). It takes no locks and detects conflicts, at the price of retries under contention. This is the database form of the If-Match header from Part 6. It is the best fit when conflicts are rare, as when one person edits a profile, and the worst fit for a hot counter, as the 9,225 retries show.
4. Serializable with retries when the invariant spans several rows and is hard to express another way.

A write skew such as the courier rule needs one of: serializable isolation, locking the rows the decision depends on (SELECT ... FOR UPDATE on the on-duty couriers), or a constraint that makes the rule enforceable by the database.

16. MVCC: how readers avoid blocking writers

PostgreSQL (and many other engines) implement isolation with multi-version concurrency control. An update does not overwrite a row. It writes a new version and marks the old one as ended. Each version records the transaction that created it and, when relevant, the one that ended it.

We can see it directly. A row at physical position (0,1) created by transaction 910 was updated once. The new version sat at (4,97), created by transaction 911.

Each transaction reads from a snapshot: it sees only versions committed before it began. So:

• Readers never take locks and writers never wait for readers. Measured: updating a row while another transaction held a snapshot reader open took 2.4 ms. Updating a row while another transaction held it with SELECT ... FOR UPDATE for 500 ms took 504 ms, because the writer had to wait for the lock.
• Old versions pile up. They must stay until no snapshot can still see them.

The cost: bloat

Experiment. A table of 1,000 rows (40 KiB). Every row was updated 50 times.

The table still held 1,000 live rows, but 49 dead versions per row inflated it 40 times. Plain VACUUM marks dead space as reusable without shrinking the file, and VACUUM FULL rewrites the table, at the price of an exclusive lock. In production, autovacuum runs in the background. The practical rules: keep transactions short (a long-open transaction stops vacuum from removing versions it might still need), and avoid patterns that update the same rows constantly.

17. Locks and deadlocks

When transactions lock rows, they can form a cycle. Transaction 1 locks row 1 and then wants row 2, while transaction 2 locks row 2 and then wants row 1. Neither can proceed.

Experiment. Two transactions each updated two rows, in opposite order and then in the same order.

The engine does not look for a cycle immediately. It waits deadlock_timeout (1 second by default) and then checks. It aborts one victim with SQLSTATE 40P01, and the other continues. The abort is a signal that the client must retry the transaction.

How to avoid deadlocks:

• Acquire locks in one global order, for example lowest id first. The same-order run above never deadlocked.
• Keep transactions short, with no user input or network calls inside one.
• Set lock_timeout so a wait cannot last forever.
• Handle 40P01 and 40001 by retrying the whole transaction.

18. Document or relational?

Experiment. 200,000 orders with 4 items each, stored as a JSON document per order (PostgreSQL jsonb) and as two relational tables (orders and order_items), both with primary keys.

For a primary-key fetch the two shapes are equivalent: one index probe either way. The document form used 1.36 times the space because every row repeats its key names. The real difference appears elsewhere, in queries across orders (“all orders containing this item”) and in enforcing rules across entities, where the relational form has indexes and constraints designed for the job and the document form must scan or maintain extra structures. This is not a general verdict on document databases, which are built around scale and distribution features this single-node experiment does not exercise. It does show that “documents are faster” is not a reason by itself.

Most real systems use more than one store: a relational database as the system of record, plus a cache, a search index or a wide-column store for specific access patterns. The decision guide in Figure 15 maps each pattern to a model.

19. Backups: logical versus physical

Replication protects against a machine failing. It does not protect against a mistaken DELETE, a bad deploy or a corrupted table, because those changes replicate too. Only a backup lets you return to an earlier state. Two kinds exist.

Logical backup. pg_dump writes the contents of one database as SQL or as an archive that can rebuild it. It is portable across versions and platforms and can restore one table. It can only restore the moment the dump started, and a restore must rebuild every index.

Physical backup. pg_basebackup copies the data files of the whole cluster, and the server keeps archiving its write-ahead log (section 12). A restore copies the files back and then replays log records forward to any chosen moment. It is tied to the same major version, and covers all databases in the cluster.

Experiment. A 180 MB database holding 1,000,000 orders (the server also held 1.95 GB of other test data).

The two kinds cover different scopes (one database against the whole cluster), so their sizes are not comparable. Compression trades dump time for size (2.4 times slower, 4 times smaller). Restore time grows with data size and with the number of indexes.

20. Point-in-time recovery, RPO and RTO

The write-ahead log from section 12 records every change. If you keep every log segment since a base backup, you can rebuild the database as it was at any moment in that span. This is point-in-time recovery (PITR).

Experiment. This reproduces incident 6.

1. Take a logical dump and a base backup of the 1,000,000-order database, with WAL archiving on.
2. Insert another 100,000 orders. The table now holds 1,100,000. Note the time.
3. Three seconds later, run DELETE FROM orders WHERE id % 2 = 0. The table now holds 550,000 rows.
4. Insert 50 more orders, and force the log to be archived.
5. Recover two ways: restore the dump, and restore the base backup with replay to a time just after step 2 (recovery_target_time).

Point-in-time recovery returned the table to exactly its state before the mistaken delete. The 50 orders inserted after the accident were correctly absent, because they came after the chosen moment. Recovery took 5.5 seconds end to end: copying the base backup, replaying 218 MB of archived log, and promoting the server.

RPO and RTO

Two numbers describe a recovery plan:

• RPO (recovery point objective): how much recent data you can afford to lose. A plan that relies on a nightly dump has an RPO of up to 24 hours. Continuous archiving shrinks it to the archiving interval, seconds to minutes.
• RTO (recovery time objective): how long the service can be down while you recover. Here it was 5.5 seconds for a small database. It grows with data size, so measure it on production-sized data.

A backup you have never restored is a hope, not a backup. Schedule regular restore tests, store backups in a different failure domain (another region or account), keep them encrypted, and monitor that archiving is working, since a silent gap in the log breaks recovery after that point.

In the wild

• PostgreSQL, MySQL and SQL Server are the default stores of record for most transactional products. They differ in isolation behavior, so a retry loop and explicit locking rules matter more than the brand.
• Payment systems typically require serializable behavior or explicit row locking for balance changes, with idempotency keys (Part 6) on top.
• Document databases are common for catalogs and content, where an entity is read and written whole.
• Wide-column stores (Cassandra-style) are used for high-volume event and time-series writes, where queries are designed up front.
• Managed database services offer automated base backups, WAL archiving and point-in-time restore as a feature. The mechanics are the ones in sections 19 and 20, so you still need to know your RPO and RTO.

Pitfalls

1. Files or ad hoc scripts for multi-step state changes. 94 of 200 crash trials broke.
2. Constraints only in the application. The second writer and the one-off script bypass them.
3. Copying data “for speed” without measurement. +63% space and 1,281 times the work per rename here.
4. N+1 queries. Fine on a laptop, 2 seconds across regions in the model.
5. Assuming the default isolation level is safe. Under read committed, four of five anomalies occurred.
6. Read-modify-write in application code. 1,712 of 2,000 increments lost without an error.
7. No retry loop around serializable transactions or deadlock aborts.
8. Long-running transactions. They hold locks, hold back vacuum and inflate tables.
9. Inconsistent lock order. The recipe for deadlocks.
10. Turning off fsync or synchronous commit for data you cannot afford to lose.
11. Choosing a database by brand rather than by access pattern.
12. Treating “consistent” in ACID as automatic. The database enforces only the rules you declare.
13. Indexing every column. Five indexes made inserts 3.5 times slower.
14. Composite indexes in the wrong column order. 26.3 ms against 0.66 ms for the same query.
15. Trusting the planner without checking estimated versus actual rows. Stale statistics cost 5.5 times here.
16. Backups that were never restored, or backups stored next to the database they protect.
17. Relying on replication as a backup. A mistaken delete replicates instantly.

Interview lens

• “What is ACID?” Name each property and its mechanism: atomicity via the log and undo, consistency via constraints, isolation via MVCC and locks, durability via write-ahead logging and fsync.
• “Two users book the last seat. How do you prevent a double booking?” A unique constraint or a conditional atomic update (UPDATE ... WHERE seats > 0), SELECT ... FOR UPDATE, or serializable with retries. Explain why read-then-write fails.
• “Explain isolation levels with an example.” Walk the five anomalies, say which level prevents each, and note the PostgreSQL specifics: repeatable read still allows write skew.
• “When would you denormalize?” When measurement shows a read path is too slow and the join cannot be fixed with an index or a cache, and the copied data is cheap to keep consistent. State the write cost.
• “What is MVCC?” Row versions and snapshots, readers not blocking writers, dead versions and vacuum.
• “How would you speed up a slow query?” Run EXPLAIN (ANALYZE, BUFFERS), compare estimated and actual rows, check for a sequential scan on a big table, add or reorder an index (equality columns first), refresh statistics, and measure again. Mention the write cost of indexes.
• “How do indexes work, and what do they cost?” B-tree: sorted pages, height grows logarithmically, 3 reads for a million keys. Cost: space, slower writes, and no help on low-selectivity queries.
• “How do you recover from an accidental delete?” Point-in-time recovery from a base backup plus archived WAL. Define RPO and RTO, and test restores.
• “SQL or NoSQL?” Start from access patterns, consistency needs and scale, not from brands. Relational is the default, and the other models serve specific shapes.

What FoodRush changes

• PostgreSQL is the system of record for orders, payments, customers, restaurants and couriers, with foreign keys and check constraints on every table.
• Normalized to third normal form, with deliberate snapshots (order_items.price_cents) for historical facts.
• No N+1: list endpoints use a join or a batched second query, and development logging flags repeated statements.
• Default read committed, with three explicit exceptions: balance and payment changes use SELECT ... FOR UPDATE in a fixed lock order; the courier on-duty rule uses serializable with a retry loop; counters use a single atomic UPDATE.
• Every transaction is short and wrapped in a retry for 40001 and 40P01, up to a small attempt limit.
• lock_timeout and statement_timeout set so no query waits forever.
• Autovacuum monitored, and alerts on the oldest open transaction.
• Writes in batches for imports and bulk status updates.
• Idempotency keys from Part 6 stored in a table inside the same transaction as the order they protect.
• Indexes designed per query: (customer_id, created_at DESC) for order history, (restaurant_id, status, created_at DESC) for the restaurant dashboard, and a covering index for the hot customer lookup. Unused indexes are reviewed quarterly.
• ANALYZE after every bulk import, and slow queries (over 100 ms) logged with their plans.
• Backups: a nightly logical dump for portability, a weekly base backup plus continuous WAL archiving to another region for point-in-time recovery, a target RPO of under 5 minutes, and a restore drill every month on production-sized data.

Cheat sheet

Check your understanding

1. A transfer updates two rows. The process dies after the first update. What does the database do on restart, and why can it?
2. Why is copying a restaurant’s name into every order row a problem when the restaurant renames, even though reads are not slower?
3. A page makes one query for 100 orders and then one per order for the restaurant. What is this called, and name two fixes.
4. Why does COMMIT have to wait for an fsync, and how do batching and group commit reduce the cost per row?
5. Under read committed, two transactions each read v = 10 and write v + 1. What is the final value, and name two ways to make it 12.
6. Why does PostgreSQL’s repeatable read still allow write skew, and what does serializable do about it?
7. What does MVCC let a reader do that a lock-based design would not, and what does it cost?
8. Two transactions deadlock. What does the engine do, and what must the application do?
9. A table has 1,000 live rows but is 40 times larger than expected. What happened and how is it fixed?
10. When is a document store a better fit than a relational one, and what did the experiment show about key lookups?
11. Why does a B-tree lookup on a million-row table need only 3 page reads, and why does a table ten times larger rarely need a fourth?
12. A query returns 10 percent of a table. Why might the planner prefer a bitmap scan or a sequential scan over a plain index scan?
13. You have (restaurant_id, created_at). Will it speed up WHERE created_at > now() - interval '1 hour'? Why or why not?
14. What does an index-only scan avoid, and what must be true for it to work?
15. EXPLAIN ANALYZE shows rows=1 estimated and 2,000,000 actual. What is the likely cause and the fix?
16. A mistaken DELETE ran at 14:03. You have a nightly dump from 02:00 and a base backup with continuous WAL archiving. How do you recover with the least data loss, and what are your RPO and RTO?

Answers

1. It reads the write-ahead log: committed work is redone, unfinished work is undone. The log was flushed to stable storage before the change was acknowledged, so it records enough to rebuild a consistent state.
2. A rename must update every copy, which cost 100,000 row writes against 1 in the experiment, and an interrupted update left 2 names for one restaurant. The read-time saving was only about 20 percent.
3. The N+1 query problem. Fix with a join, or a batched query (WHERE id = ANY(...)), or the ORM's eager loading.
4. The log must be on stable storage before the client is told the change is durable, otherwise a crash could lose an acknowledged commit. Batching spreads one fsync over many rows (7 times faster at 100 rows per commit), and group commit lets concurrent clients share one flush (3.4 times more commits per second at 64 clients).
5. 11, because both computed from the stale value and one write overwrote the other. Use SET v = v + 1 in one statement, SELECT ... FOR UPDATE, a version check with retry, or serializable with retry.
6. Repeatable read gives each transaction a snapshot, and write skew writes to different rows, so no single row conflict is detected. Serializable tracks read-write dependencies and aborts one of the transactions with error 40001, which the application retries.
7. Readers neither lock nor wait for writers (a 2.4 ms update with a snapshot reader open, against 504 ms when the row was locked). The cost is dead row versions that must be vacuumed, and bloat when updates are heavy or transactions are long.
8. It waits deadlock_timeout, detects the cycle, aborts one victim with error 40P01 and lets the other finish. The application must retry the aborted transaction, and avoid recurrence by locking in a consistent order.
9. Heavy updates created many dead row versions (MVCC). Plain vacuum makes the space reusable, VACUUM FULL rewrites the table to shrink it, and autovacuum tuning plus shorter transactions prevent it.
10. When an entity is always read and written whole, and cross-entity queries and constraints are rare. In the experiment, fetching one order by key took 0.165 ms as a document and 0.174 ms as a join, so key lookups alone do not justify leaving relational.
11. Each page holds about 364 keys, so the tree fans out by that factor at every level: 3 levels cover about 48 million keys and 4 levels about 18 billion. Ten times more rows adds at most one level.
12. A plain index scan visits a scattered heap page per row, which is costly at high row counts. A bitmap scan sorts the locations and reads each page once, and a sequential scan reads everything in order, which is cheapest when most of the table is needed. At 10 percent here the sequential scan took 122 ms, the bitmap scan 157 ms and the index scan 249 ms.
13. No. The filter is on the second column, so the index cannot be searched by it. Here it scanned the whole index (26.3 ms). An index with created_at first took 0.66 ms.
14. It avoids visiting the table rows. The index must contain every column the query needs (for example through INCLUDE), and the table must have been vacuumed recently so the engine knows which rows are visible.
15. The statistics are stale, typically after a bulk load. Run ANALYZE. In the experiment that changed a nested loop with 2,000,000 probes into a hash join and cut the time from 3,454 ms to 625 ms.
16. Restore the base backup and replay the archived WAL with recovery_target_time set to just before 14:03. The RPO is close to zero (up to the archiving interval) against a 12-hour loss from the dump. The RTO is the copy plus replay time, which was 5.5 seconds for 218 MB of WAL in the experiment, so measure it on production-sized data.

What comes next

Part 8 moves to caching, which sits in front of everything built so far. It covers why caches work (the locality of Part 2 applied to systems), cache-aside, write-through and write-behind patterns, TTLs and invalidation, cache stampedes, and Redis as the common implementation. The measurements will compare database reads with cache hits, and show what a stale cache costs.