Exercises: Relational Databases for Data Engineering

Most of these need the Kestrel database running. If you have not done Part D of Chapter 5's exercises, do that first — nothing here works without it.

Difficulty: ⭐ warm-up · ⭐⭐ standard · ⭐⭐⭐ deeper. Solutions: daggered (†) and odd-numbered problems are in appendices/answers-to-selected.md.

Part A — Warm-ups ⭐

7.1 † Name the six consequences of being a guest in someone else's database (§7.1). Which one surprises people?

7.2 A page is 8 KB and rows are stored whole. Explain in two sentences why SELECT SUM(net_revenue_cents) FROM order_items is slow on a row store even with an index on that column.

7.3 † Name the three access paths a PostgreSQL planner can choose and say what selectivity each suits.

7.4 Why can a read-only query degrade a production database under MVCC? Answer without using the word "lock."

7.5 † State the two acceptable types for currency and when you would choose each. Then state the three costs of integer cents.

7.6 What does TIMESTAMP WITH TIME ZONE actually store? Name the seasonal bug that TIMESTAMP WITHOUT TIME ZONE produces.

7.7 † Why does UUID v4 hurt insert performance where UUID v7 does not?

7.8 Which replication mode does CDC require, and what wal_level does it need?

Part B — Standard ⭐⭐

7.9 Run the 🧪 Try It from §7.2 against your Kestrel database. Record the access path and execution time for each of the three queries. Then run the third with SET enable_seqscan = off and report by what factor the planner's choice beat the forced index scan. Explain the result in terms of random versus sequential I/O.

7.10 † An index exists on orders (customer_id, placed_at). For each query, say whether the index can be used, fully or partially, and why: (a) WHERE customer_id = 8841 (b) WHERE placed_at >= '2025-11-01' (c) WHERE customer_id = 8841 AND placed_at >= '2025-11-01' (d) WHERE customer_id IN (8841, 9002) AND placed_at >= '2025-11-01' (e) WHERE lower(status) = 'paid' AND customer_id = 8841

7.11 Run EXPLAIN (ANALYZE, BUFFERS) on the §7.6 query against your database. Apply the four-line checklist. Report: execution time, the largest actual time node, the estimate-to-actual ratio on that node, and the total bytes read from disk. Then propose one change and measure whether it helped.

7.12 † Compute the bloat from a bad extract, as §7.7's 💸 callout does, for different parameters: a 90-minute unchunked extract during a period of 1.2 million row updates per hour, average row width 210 bytes. How many dead tuples, and how much dead space? Then compute the same figure for the chunked version at 15-minute chunks, and state what the ratio is not — that is, what the chunking does not fix.

7.13 Write the query that finds every money-typed column in a database that is not integer or numeric. Run it against Kestrel. Then extend it to also flag columns whose names suggest money (price, amount, total, revenue, cost) but do not end in _cents — those are the ones with an unstated unit.

7.14 † §7.3 lists five defenses against long-transaction bloat and notes that defense 3 has a trap. Read the PostgreSQL documentation on hot_standby_feedback and write two paragraphs: what it does when on, what it does when off, and which setting you would choose for a replica used only for analytical extracts. State what you give up either way.

7.15 Take the extract function in §7.7 and identify all six load-bearing properties in the code. For each, write the one-sentence failure that occurs if you remove it, and name the chapter section where that failure is described.

Part C — Deeper ⭐⭐⭐

7.16 The max_slot_wal_keep_size setting trades a broken CDC stream for a protected disk. Design the full operational response: what monitoring, what thresholds, what the runbook says when the slot is invalidated, and how long a re-snapshot of Kestrel's orders table would take. Then decide the setting's value and defend the number.

7.17 † §7.4 says JSONB becomes a trap when used to avoid deciding on a schema. Design a check that detects this: given a table with a JSONB column, produce a report of which keys appear, in what fraction of rows, with what value types. Then state the threshold at which you would promote a key to a column, and defend it.

7.18 MySQL clusters the table on the primary key; PostgreSQL does not. Work through what this means for an extract that ranges over updated_at on a table whose primary key is an auto-increment integer, on each engine. Which is faster, why, and what would you do differently on each?

7.19 † Construct the case against integer cents. Find a real scenario where NUMERIC is the better choice, and one where a float genuinely is acceptable. Then state the rule you would give a team, given that the rule has to be followed by people who will not read your reasoning.

Part D — The Kestrel Platform ⭐⭐⭐

7.20 — Increment 7: the source database.

(a) Run python platform/seed/seed_kestrel.py --scale small. Time it.

(b) Verify against the frozen figures rather than against a feeling. Run all four verification queries from the 🧱 callout in §7.10 and record the results. The seasonal curve should show a November/December spike; the skew query should show one customer far above the rest; the _cents query should return zero rows.

(c) Write platform/seed/verify_seed.sql containing those four queries plus at least four more of your own — things you would want to be true about a source database before building on it. Ideas: no orders with zero lines, no negative quantities, every order_items.order_id present in orders, no placed_at in the future.

(d) Run it. At least one of your checks should fail, because the seed deliberately includes dirty rows. Record which, and resist fixing them — Chapter 23 needs them.

7.21 † Add platform/ingest/batch/extract_postgres.py, implementing the §7.7 pattern in full. It must have all six load-bearing properties, a --dry-run flag that prints the ranges it would read without reading them, and a --chunk-hours argument.

Then do the thing that makes it real: run it against the replica while a write load is running, and measure n_dead_tup on orders before and after, chunked and unchunked. Report the difference. Chapter 13 extends this file; Chapter 24 schedules it.

Reflection

A. §7.1 frames the source database relationship as being a guest. Have you been on the other side — owning a system someone else extracted from? What would you have wanted them to do differently?

B. The MVCC bloat failure is caused by a read-only query and is genuinely counter-intuitive. What other "safe" operations in your experience turned out to have non-obvious costs? What would have made the cost visible earlier?