---
title: "Databases and SQL"
book: "Research, Data and Risk Platforms"
subject: quant
language: en
chapter: 25
exercises: 8
source: https://one-course.com/books/quant/15/en/chapter/25-databases-and-sql
---

# Chapter 25 — Databases and SQL

A regulator asks for the firm’s positions report exactly as it stood at 18:00 last Tuesday. On Wednesday morning operations had corrected Monday’s and Tuesday’s errors — quantities keyed ten times too large, trades that never happened, trades booked a day late — and they had corrected them in place. The positions table now gives the right answer for Tuesday evening, and nobody can say what answer the firm gave on Tuesday evening. This chapter builds the firm’s trade database so that both answers stay available: relational tables with keys, writes in transactions that do not lose updates, indexes chosen by reading [query plans](#def-pl-databases-and-sql-index), and a second time axis that records what the database believed and when.

## 25.1 The relational model for trades and positions

**Definition 25.1 (Relational model, primary key, foreign key).**

The *relational model*, proposed by Codd in 1970, stores data as relations — tables of rows with named, typed columns — manipulated by operations on whole relations (selection, projection, join), independently of how rows are stored. A *primary key* is a set of columns whose values identify one row; a *foreign key* is a set of columns whose values must appear as the primary key of a row in another table.

**Definition 25.2 (Normal form).**

A schema is in a *normal form* when each fact is stored once: every non-key column depends on the key, the whole key and nothing but the key (third normal form), so that an instrument’s currency, say, lives in the instruments table and not on every trade.

The chapter’s schema (`firm.tradedb`) has three tables: instruments, accounts and trades, each trade pointing to its account and its instrument by [foreign keys](#def-pl-databases-and-sql-relational) that the database enforces — a trade for an account that does not exist is refused, not stored. Positions are not a table: they are a query over trades, so they cannot disagree with them. Schema changes are numbered migrations, applied once each and recorded in the database, so that every copy of the database can say which schema it has.

```python
MIGRATIONS = [
    (1, """
CREATE TABLE instruments (id INTEGER PRIMARY KEY, symbol TEXT NOT NULL UNIQUE,
  currency TEXT NOT NULL);
CREATE TABLE accounts (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);
CREATE TABLE trades (
  trade_id TEXT NOT NULL,
  account_id INTEGER NOT NULL REFERENCES accounts(id),
  instrument_id INTEGER NOT NULL REFERENCES instruments(id),
  qty INTEGER NOT NULL, price REAL NOT NULL,
  valid_from INTEGER NOT NULL, valid_to INTEGER NOT NULL,
  sys_from INTEGER NOT NULL, sys_to INTEGER NOT NULL,
  PRIMARY KEY (trade_id, sys_from));
"""),
    (2, "CREATE TABLE audit (at INTEGER NOT NULL, trade_id TEXT NOT NULL, what TEXT);"),
]
```

***Listing 25.1.** The schema as migrations: primary keys, foreign keys, and a trades table with two time intervals per row. code/firm/tradedb/firm_tradedb.py*

![The chapter’s schema. Instruments and accounts are referenced by trades through enforced foreign keys; the trades table’s primary key is the trade identifier with the start of its system time, because a corrected trade has several versions.](https://one-course.com/images/onecourse/chapters/quant-15/pl-databases-and-sql/fig-612a9355661a.svg)

***Figure 25.1.** The chapter’s schema. Instruments and accounts are referenced by trades through enforced [foreign keys](#def-pl-databases-and-sql-relational); the trades table’s [primary key](#def-pl-databases-and-sql-relational) is the trade identifier with the start of its [system time](#def-pl-databases-and-sql-system), because a corrected trade has several versions.*

## 25.2 Transactions and isolation

**Definition 25.3 (ACID transaction).**

An *ACID transaction* is a group of reads and writes that is atomic (all or nothing), consistent (it takes the database from one valid state to another), isolated (concurrent transactions do not see each other’s partial work) and durable (once committed, it survives a crash).

**Definition 25.4 (Isolation level, write-ahead log).**

An *isolation level* states which anomalies of concurrency a database allows between transactions, from read uncommitted to serializable, under which the result is that of some serial order. A *write-ahead log* records each change before it is applied to the database’s main file, so that a crash can be recovered by replaying or discarding it: in SQLite’s WAL mode "the original content is preserved in the database file and the changes are appended into a separate WAL file".

The anomaly that costs a trading firm money is the lost update, which Berenson and his co-authors define as a transaction reading an item, another updating it, and the first then writing a value computed from its earlier read, so that the second’s update is lost. Two gateways each add 100 shares to the same position: each reads the position, adds its fill, and writes the result. SQLite is serializable — it "implements serializable transactions by actually serializing the writes" — but only within a transaction; an application that reads in one statement and writes in another has made two transactions, and the database cannot protect it.

```python
def _step(c, mode, k, seen, w):
    if mode == "one statement":
        if k == 0:
            c.execute("UPDATE pos SET q = q + 100 WHERE k = 'x'")
        return
    if k == 0:
        if mode == "transaction":
            c.execute("BEGIN IMMEDIATE")
        seen[w] = c.execute("SELECT q FROM pos WHERE k = 'x'").fetchone()[0]
    else:
        c.execute("UPDATE pos SET q = ? WHERE k = 'x'", (seen[w] + 100,))
        if mode == "transaction":
            c.execute("COMMIT")
```

***Listing 25.2.** One writer’s two steps in each of three modes: read then write as two transactions, the same inside one transaction taken at BEGIN, or one statement that reads and writes. code/platforms/25-databases-and-sql/python/pl_tradedb.py*

Over 1 000 random interleavings of the two writers’ steps, the read-then-write application loses an update 636 times, close to the two thirds of interleavings in which both reads come before a write. Inside a transaction taken at `BEGIN IMMEDIATE`, the second writer is refused until the first commits and then retries: no update is lost. A single `UPDATE pos SET q = q + 100` does the read and the write in one statement and loses nothing either. The rule: send an increment to the database as an increment, or let the read and the write share one transaction, with a retry.

## 25.3 Indexes and query plans

**Definition 25.5 (B-tree index, query plan).**

A *B-tree index* keeps a copy of chosen columns of a table in a balanced sorted tree with pointers to the rows, so that rows with given values or in a range are found in logarithmic time instead of by reading the table. A *query plan* is the sequence of operations the database chooses to answer a query — which tables it scans, which indexes it searches, how it joins and groups — and can be displayed before the query runs.

Three queries run over the week’s 500 008 trades: a point query (one account’s position in one instrument as of Tuesday 18:00), the report (every account’s positions as of Tuesday 18:00, joined to the account and instrument names), and an analytical query (the week’s traded value by instrument). `EXPLAIN QUERY PLAN` shows each one reading the whole trades table (`SCAN trades`); the chapter’s advisor lists such scans, and the question for each is whether an index would select few enough rows to be worth it.

![Measured query times over 500 008 trades (log scale; one core, in memory, median of five runs, on an otherwise idle machine). The index on (account, instrument) takes the point query from 13.7 ms to 0.01 ms; the index on valid time barely helps any query, because Tuesday 18:00 still selects two days of five; DuckDB runs the report 15 times and the analytical query 53 times faster than SQLite without any index. Data: bench_tradedb.py, measured_queries.csv.](https://one-course.com/images/onecourse/chapters/quant-15/pl-databases-and-sql/fig-35b28878c946.svg)

***Figure 25.2.** Measured query times over 500 008 trades (log scale; one core, in memory, median of five runs, on an otherwise idle machine). The index on (account, instrument) takes the point query from 13.7 ms to 0.01 ms; the index on valid time barely helps any query, because Tuesday 18:00 still selects two days of five; DuckDB runs the report 15 times and the analytical query 53 times faster than SQLite without any index. Data: `bench_tradedb.py`, `measured_queries.csv`.*

The measurements ([Figure 25.2](#fig-pl-databases-and-sql-queries)) give the rule of thumb its numbers. An index pays when it selects a small part of the table: the point query reads about fifty rows of 500 008 through the (account, instrument) index, a gain of three orders of magnitude. An index on valid time selects 40% of the rows for Tuesday’s report and saves almost nothing, and no index helps a query that must read every row. Each index also costs every insert its own write, so the advisor’s list is a list of candidates, not of indexes to create.

## 25.4 Time in the database: system time and time travel

Book 7, chapter 3, stored data with two times, the valid time a value describes and the knowledge time at which it became available, and made research reproducible with them. A trade database needs the same two axes for a slightly different reason.

**Definition 25.6 (System time, time-travel query).**

The *system time* of a row version is the interval during which the database held it as current: from the transaction that inserted it to the transaction that superseded it. A *time-travel query* asks for the rows valid at one time as the database held them at another, by filtering on both intervals.

[System time](#def-pl-databases-and-sql-system) differs from Book 7’s knowledge time in one respect: it is when the firm’s database recorded a fact, which the database stamps itself and nobody can backdate, where knowledge time is when the fact became available, which may be earlier and is supplied by the data. SQL:2011 standardised both axes: transaction time through system-versioned tables, valid time through application-time periods, and queries of the form `FOR SYSTEM_TIME AS OF`.

```python
def correct(conn, trade_id: str, changes: dict, sys_time: int) -> None:
    """Close what we believed and record what we now believe; nothing is updated in place
    except the closing of the old version's system time."""
    old = dict(zip(COLS.split(", "), _current(conn, trade_id), strict=True))
    conn.execute("UPDATE trades SET sys_to = ? WHERE trade_id = ? AND sys_to = ?",
                 (sys_time, trade_id, END))
    new = {**old, **changes, "sys_from": sys_time, "sys_to": END}
    conn.execute(f"INSERT INTO trades ({COLS}) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
                 tuple(new[c] for c in COLS.split(", ")))
    conn.execute("INSERT INTO audit VALUES (?, ?, ?)",
                 (sys_time, trade_id, repr(sorted(changes))))
```

***Listing 25.3.** A correction closes the old version’s system time and inserts the corrected version: nothing that was believed is overwritten. code/firm/tradedb/firm_tradedb.py*

![One corrected trade in the two times: executed on Monday at 12:00, keyed as 700 shares, corrected to 70 on Wednesday at 10:00. Each version is a rectangle, true from Monday in valid time (up) and current for an interval of system time (across). Tuesday’s report as it was reads the point (valid Tuesday 18:00, system Tuesday 18:00) and finds 700; the corrected report reads (Tuesday 18:00, Friday) and finds 70.](https://one-course.com/images/onecourse/chapters/quant-15/pl-databases-and-sql/fig-48a70e9fcde0.svg)

***Figure 25.3.** One corrected trade in the two times: executed on Monday at 12:00, keyed as 700 shares, corrected to 70 on Wednesday at 10:00. Each version is a rectangle, true from Monday in valid time (up) and current for an interval of [system time](#def-pl-databases-and-sql-system) (across). Tuesday’s report as it was reads the point (valid Tuesday 18:00, system Tuesday 18:00) and finds 700; the corrected report reads (Tuesday 18:00, Friday) and finds 70.*

```python
AS_OF = f"""SELECT {COLS} FROM trades
WHERE valid_from <= :v AND :v < valid_to AND sys_from <= :s AND :s < sys_to"""

POSITIONS = """SELECT a.name, i.symbol, SUM(t.qty)
FROM trades t JOIN accounts a ON a.id = t.account_id
  JOIN instruments i ON i.id = t.instrument_id
WHERE t.valid_from <= :v AND :v < t.valid_to AND t.sys_from <= :s AND :s < t.sys_to
GROUP BY a.name, i.symbol"""


def as_of(conn, valid: int, system: int) -> list:
    return conn.execute(AS_OF, {"v": valid, "s": system}).fetchall()


def positions(conn, valid: int, system: int) -> dict:
    """Positions from the trades true at `valid`, as the database knew them at `system`."""
    return {(a, s): q for a, s, q in conn.execute(POSITIONS, {"v": valid, "s": system})}
```

***Listing 25.4.** Time travel: the trades valid at `:v` as held at `:s`, and the positions computed from them. code/firm/tradedb/firm_tradedb.py*

The week of the model has 500 008 trades. On Wednesday at 10:00 operations correct 30 quantities keyed ten times too large, cancel 10 trades that never happened, and book 8 of Tuesday’s trades that had been missed; the table then holds 500 048 row versions, forty more than trades. Tuesday’s 18:00 report, as it was, reads valid time Tuesday 18:00 at [system time](#def-pl-databases-and-sql-system) Tuesday 18:00; the corrected report reads the same valid time at [system time](#def-pl-databases-and-sql-system) Friday evening. The two differ on 48 of the 10 000 account and instrument positions — one per correction ([Figure 25.4](#fig-pl-databases-and-sql-diff)) — and the regulator gets the first, with the second and the audit table explaining every difference. The table corrected in place can only produce the second.

![Tuesday’s 18:00 positions report, corrected minus as it was reported, for the 48 account and instrument positions that differ: quantity corrections (red) take nine tenths of the trade out of the position, cancellations (black) the whole trade, and late bookings (blue) add one. The other 9 952 positions agree. Data: fig_tradedb.py.](https://one-course.com/images/onecourse/chapters/quant-15/pl-databases-and-sql/fig-75e4ad97e471.svg)

***Figure 25.4.** Tuesday’s 18:00 positions report, corrected minus as it was reported, for the 48 account and instrument positions that differ: quantity corrections (red) take nine tenths of the trade out of the position, cancellations (black) the whole trade, and late bookings (blue) add one. The other 9 952 positions agree. Data: `fig_tradedb.py`.*

## 25.5 Transactional and analytical workloads

**Definition 25.7 (Online transaction processing, online analytical processing).**

*Online transaction processing* (OLTP) is a workload of many small transactions that read and write a few rows each by key — booking trades, updating positions. *Online analytical processing* (OLAP) is a workload of few large queries that read many rows and few columns to aggregate them — traded value by instrument, P&L by desk over a year.

SQLite is a row store built for the first; DuckDB, the [embedded database](https://one-course.com/books/quant/15/en/chapter/5-time-series-databases-and-the-alternatives#def-pl-time-series-databases-and-the-alternatives-embedded) of chapter [5](https://one-course.com/books/quant/15/en/chapter/5-time-series-databases-and-the-alternatives#ch-pl-time-series-databases-and-the-alternatives), is a columnar engine with vectorised execution built for the second. On the same SQL and the same data — the chapter checks that every answer is equal — DuckDB runs the report in 9.8 ms against SQLite’s 150 and the analytical query in 2.8 ms against 148, with one thread and no index. SQLite with the right index still wins the point query by far (0.01 ms against 1.0). The firm keeps its trades in a transactional store, where the keys, the transactions and the [system time](#def-pl-databases-and-sql-system) are, and gives research and reporting a columnar copy, refreshed from the transactional one and never written by anyone else.

**As of September 2026 — Isolation in SQLite and temporal SQL.**

SQLite’s documentation states that its transactions are serializable, implemented by serializing the writes (except shared-cache connections with `read_uncommitted`), and that in write-ahead-log mode the changes are appended to a separate WAL file while the database file keeps the original content. The SQL:2011 standard provides transaction time through system-versioned tables and valid time through application-time period tables, as described by Kulkarni and Michels (2012).

## 25.6 Tutorial: Tuesday at 18:00

**Goal.** Store a week of trades bitemporally, correct them, reproduce a past report, and measure what indexes and engines change. **End state:** Figures [25.4](#fig-pl-databases-and-sql-diff) and [25.2](#fig-pl-databases-and-sql-queries).

1. **Schema** : `firm_tradedb.connect()` and `migrate` ; try inserting a trade for an unknown account.
2. **The week** : `pl_tradedb.load(conn)` ; the corrections of Wednesday 10:00.
3. **Reports** : `positions(conn, TUE_18, TUE_18)` and `positions(conn, TUE_18, NOW)` ; the rows that differ.
4. **Lost updates** : `lost_updates(mode)` for the three modes.
5. **Plans and times** : `plan` and `advise` for each query; `bench_tradedb.py` .

**What to change next.** Add an index on (valid_from, account_id) and read the report’s plan again; copy the tables to DuckDB with a system-time filter so that the columnar copy can travel in time too.

## 25.7 Build: the trade database

**Purpose.** One transactional store of the firm’s trades that enforces its keys, never loses an update, and can reproduce any report as it was produced.

**Interface.** `connect`, `migrate`, `book`, `correct`, `cancel`, `as_of`, `positions`, `transact`, `plan`, `advise`, `to_duckdb`.

**Rules.** [Foreign keys](#def-pl-databases-and-sql-relational) on; schema only through migrations; corrections close and insert, never update values; every correction audited; read-modify-write only inside a transaction, or as one statement; indexes chosen from plans and measured.

**Acceptance tests.** `code/firm/tradedb/tests/`: keys enforced; a correction, a cancellation and a late booking seen as they were and as corrected; a locked writer retried and a failing transaction rolled back; the advisor before and after an index; the same answer in DuckDB.

**Stretch.** Valid-time corrections that split a row’s interval; system-versioned tables in a database that has them; a columnar copy refreshed by change capture.

Sources and further reading

- E. F. Codd, *A Relational Model of Data for Large Shared Data Banks* , Communications of the ACM 13(6), 1970.
- H. Berenson, P. Bernstein, J. Gray, J. Melton, E. O’Neil and P. O’Neil, *A Critique of ANSI SQL Isolation Levels* , SIGMOD 1995.
- K. Kulkarni and J.-E. Michels, *Temporal Features in SQL:2011* , SIGMOD Record 41(3), 2012.
- SQLite documentation: *Isolation in SQLite* ; *Write-Ahead Logging* .
- One Quant Book 7, chapter 3 (valid time, knowledge time, bitemporal data).

## 25.8 Exercises

**Exercise 25.1 ★.**

Why are positions a query over trades rather than a table of their own?

**Solution of Exercise 25.1.**

A positions table is a second copy of what the trades say, and two copies can disagree; a query over trades cannot. When the query is too slow, a positions table can be kept as a cache, but it is rebuilt from the trades and never corrected by itself.

**Exercise 25.2 ★.**

Why is the trades table’s [primary key](#def-pl-databases-and-sql-relational) (trade identifier, start of [system time](#def-pl-databases-and-sql-system)) and not the trade identifier alone?

**Solution of Exercise 25.2.**

Because a corrected trade has several versions, one per system-time interval: the trade identifier repeats, and the start of [system time](#def-pl-databases-and-sql-system) tells the versions apart.

**Exercise 25.3 ★.**

How many row versions does the table hold after Wednesday, and why forty more than trades?

**Solution of Exercise 25.3.**

500 048 versions for 500 008 trades: each of the 30 quantity corrections and 10 cancellations closes a version and adds one; the 8 late bookings are new trades with one version each.

**Exercise 25.4 ★★.**

Why does the read-then-write application lose an update in about two thirds of the interleavings?

**Solution of Exercise 25.4.**

Of the six orders of two reads and two writes that keep each writer’s read before its write, four put both reads before either write; then both writers compute from the same starting value and the second write overwrites the first. The model’s 636 of 1 000 is close to four sixths.

**Exercise 25.5 ★★.**

Why does the index on valid time barely help the positions report?

**Solution of Exercise 25.5.**

Tuesday 18:00 in valid time selects every trade of Monday and Tuesday, two days of five or 40% of the rows; reading them through the index costs about as much as scanning the table.

**Exercise 25.6 ★★.**

A late trade for Tuesday is booked on Wednesday. Which of Tuesday’s two reports shows it, and why?

**Solution of Exercise 25.6.**

Only the corrected report: the late trade is valid from Tuesday but its [system time](#def-pl-databases-and-sql-system) starts on Wednesday, so on Tuesday at 18:00 the database did not hold it.

**Exercise 25.7 ★★★.**

*Coding.* Write the query that lists every trade whose Tuesday 18:00 version differs from its current version, with both values.

**Solution of Exercise 25.7.**

Join the trades table to itself on the trade identifier: one side restricted to the versions current at [system time](#def-pl-databases-and-sql-system) Tuesday 18:00, the other to the versions current now, keeping pairs whose quantity or valid-time end differ. On the model’s week it returns 40 trades: the 30 corrected quantities and the 10 cancellations (query `CHANGED` in `pl_tradedb.py`).

**Exercise 25.8 ★★★.**

*Find the flaw.* "We keep an audit table of every change, so we can always rebuild what the positions table said on any day."

**Solution of Exercise 25.8.**

An audit table records the changes the application chose to log, in its own format; rebuilding a past state means replaying them backwards without error, including changes made outside the application (a script, a manual fix). With [system time](#def-pl-databases-and-sql-system) the past states are the data itself, queried like the present, and nothing can change them.

## 25.9 Problem: Tuesday at 18:00

**Problem 25.1.**

Weekend problem — Tuesday at 18:00

The chapter’s week of trades, its corrections, its writers and its queries.

**Part I — The schema.**

1. What do the primary and [foreign keys](#def-pl-databases-and-sql-relational) of the schema guarantee?
2. What does [normal form](#def-pl-databases-and-sql-normal) keep off the trades table?
3. Why are schema changes migrations?
4. What does each row of the trades table carry beyond the trade?
5. How is a trade corrected, and how is it cancelled?

**Part II — Concurrency.**

6. What is a lost update, in the terms of the 1995 critique?
7. Why does SQLite’s serializable isolation not prevent it for the read-then-write application?
8. How many updates are lost in 1 000 interleavings under each mode?
9. What does `BEGIN IMMEDIATE` change?
10. When is one statement enough?

**Part III — Plans and engines.**

11. What does the plan of each query show without an index?
12. What does each index do to each query?
13. How do SQLite and DuckDB compare on each query?
14. Why does an index cost something even when it is not used?
15. Which store should research query?

**Part IV — The verdict.**

16. State the *named result* : the positions report reproduced as it stood at 18:00 on the Tuesday against the corrected report, the rows that differ, and the query-time gain from each index.
17. How does [system time](#def-pl-databases-and-sql-system) differ from Book 7’s knowledge time?
18. What does the table corrected in place lose?
19. What would you ask of any table a report is produced from?
20. In one sentence: what must a database remember besides the right answer?

**Solution of Problem 25.1.**

1. That each instrument, account and trade version is identified once, and that no trade refers to an account or instrument that does not exist.
2. Facts about instruments and accounts (currency, names): they live once in their own tables.
3. So that every copy of the database applies the same changes once, in order, and can say which schema it has.
4. A valid-time interval and a system-time interval.
5. Corrected: the current version’s [system time](#def-pl-databases-and-sql-system) is closed and the corrected version inserted. Cancelled: the same, with an empty valid-time interval.
6. T1 reads an item, T2 updates it, and T1 writes a value computed from its earlier read and commits: T2’s update is lost.
7. Because its read and its write are separate transactions: each is serializable, but the pair is not one transaction.
8. 636 read-then-write; none inside a transaction; none as one statement.
9. It takes the write lock at the start, so the second writer is refused until the first commits and then retries with the new value.
10. When the new value is a function of the old one that SQL can express, such as an increment.
11. A scan of the whole trades table for all three.
12. The (account, instrument) index takes the point query from 13.7 ms to 0.01 ms and leaves the others unchanged; the valid-time index barely changes any.
13. DuckDB: report 9.8 ms against 150, analytical query 2.8 ms against 148, point query 1.0 ms against 13.7 without index and 0.01 with it.
14. Every insert and every correction must update it too, and it takes space.
15. A columnar copy (DuckDB here), refreshed from the transactional store.
16. **Named result.** Tuesday’s 18:00 positions report is reproduced exactly from the bitemporal table (valid Tuesday 18:00, system Tuesday 18:00) and differs from the corrected report (system Friday) on 48 of 10 000 positions, one per correction: 30 quantities, 10 cancellations and 8 late bookings. The (account, instrument) index speeds the point query about a thousandfold (13.7 ms to 0.01 ms); the valid-time index gains almost nothing on any query.
17. [System time](#def-pl-databases-and-sql-system) is stamped by the database when it records a fact and cannot be backdated; knowledge time is when the fact became available, supplied with the data.
18. What the database believed before each correction, and so every report produced before it.
19. That it can answer as of any past [system time](#def-pl-databases-and-sql-system) .
20. What it believed, and when.

## 25.10 Interview questions

**Interview question 25.1 ★ developer.**

What is a transaction, and what do the four letters of ACID mean?

**Solution of Interview question 25.1.**

A group of reads and writes applied as a unit: atomic (all or nothing), consistent (valid state to valid state), isolated (no partial work seen by others) and durable (committed means it survives a crash).

*What the interviewer is looking for: All four, with what each protects.*

**Interview question 25.2 ★★ developer.**

Two services update the same position at the same time. How can an update be lost, and how do you prevent it?

**Solution of Interview question 25.2.**

Both read the old value and write their own sum, and one write overwrites the other. Send increments as one statement, or do the read and the write in one transaction that takes the write lock at the start, with a retry.

*What the interviewer is looking for: Read-modify-write in one transaction.*

**Interview question 25.3 ★★ developer.**

A query is slow. How do you decide whether an index will help?

**Solution of Interview question 25.3.**

Read the plan: a full scan of a large table for a query that selects few rows wants an index on the filtered columns; a query that reads most rows does not. Measure before and after, and count the cost on writes.

*What the interviewer is looking for: Selectivity, plan, measurement.*

**Interview question 25.4 ★★ developer.**

A regulator asks for a report exactly as you produced it three months ago. How do you design for that?

**Solution of Interview question 25.4.**

Keep [system time](#def-pl-databases-and-sql-system): corrections close versions and insert new ones, never update; produce reports from queries as of a [system time](#def-pl-databases-and-sql-system), and record the [system time](#def-pl-databases-and-sql-system) each report used.

*What the interviewer is looking for: Bitemporal tables, never update in place.*

**Interview question 25.5 ★★★ developer.**

Design the storage of a trading firm’s trades and positions for booking, reporting and research.

**Solution of Interview question 25.5.**

A transactional store with keys, migrations and bitemporal trades for booking; positions as queries or rebuilt caches; a columnar copy for reporting and research, refreshed from it; indexes chosen from plans; every report stamped with its [system time](#def-pl-databases-and-sql-system).

*What the interviewer is looking for: OLTP store of record, OLAP copy, time travel.*