R09 · Week 9 Recall & Drill

Weekly revision · Week 9 · Covers 5 sessions from Mon–Fri.

Sessions covered

• S041 — Indexes — B-Tree Intuition, When to Add
• S042 — Transactions & ACID — Isolation Levels, MVCC
• S043 — Query Planning — EXPLAIN, Execution Plans, Tuning
• S044 — NoSQL Landscape — KV, Document, Column, Graph
• S045 — Data Modelling — Dimensional, Data Vault, OBT

90-min structure

Blank-page reconstruction · 30 min

S041 · Indexes

1. Explain the B-tree mental model — shallow, wide, sorted — and why lookup depth grows so slowly with table size.
2. Name three query patterns that render an index useless even though it exists.
3. You have a composite index on two columns. Which queries use all of it, part of it, or none of it?

Gotcha you probably forgot: every index you add is a tax on every write. Each insert, update, and delete must maintain every index on the table, and the indexes also compete for cache with the table itself. An unused index is not free — it is pure cost. Audit for indexes that are never chosen by the planner and drop them.

S042 · Transactions & ACID

1. Write out what each letter of ACID promises, with one concrete bug each prevents.
2. Order the four anomalies by severity and name the lowest isolation level that stops each.
3. Explain multi-version concurrency control in one sentence, and say why readers need not block writers under it.

Gotcha you probably forgot: the read-then-write pattern is unsafe at the common default isolation level. Reading a balance, checking it in application code, and then writing a decrement leaves a window where another transaction does the same, and both succeed. The fixes are to do the check inside the write itself as a conditional update, to take an explicit row lock when selecting, or to raise isolation and handle the serialisation failure with a retry.

S043 · Query Planning

1. State the difference between asking for a plan and asking for a plan with actual execution statistics.
2. You see a node where the estimate and the actual row count differ by orders of magnitude. What is wrong, and what do you do?
3. Explain why wrapping an indexed column in a function defeats the index, and write the sargable rewrite.

Gotcha you probably forgot: the cost figure in a plan is a unitless number for comparing candidate plans, not a time measurement. Reading it as milliseconds leads people to "optimise" a plan whose cost went down while its wall-clock time went up. The numbers that matter are actual rows and actual time, which only appear when you run the analysing form.

S044 · NoSQL Landscape

1. Name the four families and one canonical store for each.
2. For each family, say in one sentence what it gives up to gain what.
3. Someone says "we need NoSQL because we have a lot of users". Write the three follow-up questions you ask.

Gotcha you probably forgot: a wide-column store demands query-first modelling — you design one table per access pattern, duplicating data across them, because the storage engine can only efficiently serve queries that match the partition and clustering key. Designing entities first and hoping to query them flexibly later is exactly the relational habit that fails hardest here.

S045 · Dimensional Modelling

1. Define grain, and give a concrete example of a grain-mixing bug and what it does to a total.
2. Explain the slowly-changing dimension pattern that preserves history, in one sentence.
3. Say what makes one-big-table a defensible choice on a columnar warehouse.

Gotcha you probably forgot: not every measure can be summed across every dimension. A quantity is fully additive; a balance or headcount is additive across some dimensions but not across time; and a ratio or percentage is not additive at all — summing it produces a meaningless number, and averaging it produces the average-of-averages bug. Classify every measure before anyone builds a dashboard on it.

Hands-on drill · 30 min

Task: measure when an index actually pays, then reproduce a lost update with real concurrent transactions.

Step 1 — build a table with two very different distributions (8 min)

mkdir -p ~/projects/w9-drill && cd ~/projects/w9-drill

# build.py
import random
import sqlite3
 
con = sqlite3.connect("drill.db")
con.executescript("""
DROP TABLE IF EXISTS events;
CREATE TABLE events (
  id       INTEGER PRIMARY KEY,
  user_id  INTEGER NOT NULL,   -- high cardinality: very selective
  status   TEXT    NOT NULL,   -- low cardinality: barely selective
  payload  TEXT    NOT NULL
);
""")
 
rng = random.Random(0)
rows = [
    (i, rng.randint(1, 200_000), "ok" if rng.random() < 0.95 else "error", "x" * 60)
    for i in range(500_000)
]
con.executemany("INSERT INTO events VALUES (?,?,?,?)", rows)
con.commit()
print("rows:", con.execute("SELECT count(*) FROM events").fetchone()[0])

Step 2 — prove the selectivity rule (12 min)

# selectivity.py
import sqlite3
import time
 
con = sqlite3.connect("drill.db")
 
 
def timed(label, sql, params=()):
    con.execute("ANALYZE")
    t0 = time.perf_counter()
    n = con.execute(sql, params).fetchone()[0]
    dt = time.perf_counter() - t0
    plan = con.execute("EXPLAIN QUERY PLAN " + sql, params).fetchall()
    used = "INDEX" if any("USING INDEX" in str(r) for r in plan) else "SCAN"
    print(f"{label:<34} {dt*1000:8.2f} ms  rows={n:<8} plan={used}")
 
 
print("--- before any index ---")
timed("selective (one user)",   "SELECT count(*) FROM events WHERE user_id = ?", (1234,))
timed("unselective (status ok)", "SELECT count(*) FROM events WHERE status = 'ok'")
 
con.execute("CREATE INDEX idx_user   ON events(user_id)")
con.execute("CREATE INDEX idx_status ON events(status)")
con.commit()
 
print("--- after both indexes ---")
timed("selective (one user)",   "SELECT count(*) FROM events WHERE user_id = ?", (1234,))
timed("unselective (status ok)", "SELECT count(*) FROM events WHERE status = 'ok'")
timed("rare value (status error)", "SELECT count(*) FROM events WHERE status = 'error'")
 
print("--- function on indexed column defeats it ---")
timed("sargable", "SELECT count(*) FROM events WHERE user_id = ?", (1234,))
timed("non-sargable", "SELECT count(*) FROM events WHERE user_id + 0 = ?", (1234,))

Expected outcome: the selective lookup improves dramatically once its index exists, dropping from a full scan to a near-instant seek. The high-frequency status value improves far less or not at all, because matching most of the table means the index adds a level of indirection without eliminating work — that is the selectivity rule, measured. The rare status value benefits much more than the common one from the same index, which is the point people miss: selectivity is a property of the query's value, not only of the column. And the arithmetic-wrapped predicate falls back to a scan even though the column is indexed, because the engine cannot match a computed expression against stored column values.

Step 3 — reproduce a lost update (10 min)

# lostupdate.py
import sqlite3
import threading
import time
 
con = sqlite3.connect("drill.db")
con.executescript("""
DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER NOT NULL);
INSERT INTO accounts VALUES (1, 1000);
""")
con.commit()
con.close()
 
 
def unsafe_withdraw(amount, delay):
    """Read, decide in Python, then write. The classic broken shape."""
    c = sqlite3.connect("drill.db", timeout=10)
    bal = c.execute("SELECT balance FROM accounts WHERE id = 1").fetchone()[0]
    time.sleep(delay)                       # widen the window on purpose
    if bal >= amount:
        c.execute("UPDATE accounts SET balance = ? WHERE id = 1", (bal - amount,))
        c.commit()
    c.close()
 
 
threads = [threading.Thread(target=unsafe_withdraw, args=(600, 0.2)) for _ in range(2)]
for t in threads:
    t.start()
for t in threads:
    t.join()
 
c = sqlite3.connect("drill.db")
print("balance after two concurrent 600 withdrawals:",
      c.execute("SELECT balance FROM accounts WHERE id = 1").fetchone()[0])

Expected outcome: both withdrawals of 600 succeed against a starting balance of 1000, which is impossible if the rule "never go below zero" were actually enforced. The final balance reflects only one of the two decrements, because both transactions read the same starting value before either wrote. Now rewrite it so the check lives inside the write:

UPDATE accounts SET balance = balance - 600 WHERE id = 1 AND balance >= 600;

Expected outcome: with the conditional update, the second withdrawal affects zero rows — check the affected row count and treat zero as a rejection. The lesson to write down is that the fix was not a bigger lock; it was moving the decision from application memory into the statement the database evaluates atomically.

Gap analysis + next week preview · 10 min

• Did the unselective query improve as much as you expected from its index? If you predicted a large speedup, the selectivity rule has not yet replaced "index the WHERE column" in your instincts.
• Did the lost update reproduce on the first attempt? If not, widen the sleep — the anomaly is real regardless, and seeing it once is worth more than reading about it ten times.
• Can you name the anomaly each isolation level still permits, from memory? That table is the reference you will actually reach for in design reviews.

Next week (S046–S050) moves from databases into data engineering: batch versus streaming as a mental model; Spark's execution model with jobs, stages, and shuffles; Kafka with topics, partitions, and consumer groups; stream processing with watermarks, windows, and delivery guarantees; and orchestration with directed acyclic graphs, retries, and backfills. The partitioning and grain thinking from this week is exactly what determines whether a Spark job shuffles or not.

Part of the 6-month evergreen learning plan.