How to design a Reactive Database (Live Queries)

What a strong answer covers

• Records what each query actually read, at run time, not what a developer declares.
• Only queries whose read ranges contain a written key are re-run.
• Cannot miss a dependency, because the record comes from the reads themselves.
• Names the cost: storing read sets for every live query, and matching every write against them.Supporting

Tradeoffs

What the fix has to do

• Edits change keys that were returned, so they match; inserts add keys that were not, so they do not.
• Record the scanned range (from, to), including the empty parts of it.
• Match each written key against the recorded ranges with an interval check.
• Notes that a delete or a move out of the range must also match, by its old key.Supporting

What a strong answer covers

• Every query runs against a snapshot at a known timestamp.
• Results are tagged with that timestamp.
• The client applies updates only when all its subscriptions reach the same timestamp.
• Requires a store that can read as of a recent timestamp (multiversion).Supporting

What a strong answer covers

• Each saw four tasks in its own snapshot and wrote a different row, so snapshot isolation let all three commit (write skew).
• Validating the read set at commit detects that what a mutation read has changed.
• The losing mutations re-run on fresh data and reach the right answer.
• Notes the cost: a heavily contended range causes repeated retries.Supporting

Tradeoffs

Reference implementation

const inRange = (r: Range, index: string, key: string) => r.index === index && key >= r.from && key < r.to;

function touches(r: Range, w: Write) {
  return inRange(r, w.index, w.key) || (w.oldKey !== undefined && inRange(r, w.index, w.oldKey));
}

export function tryCommit(snapshotTs: number, readSet: Range[], writes: Write[]) {
  // This function is the only writer of lastTs and recent, so check-then-append is atomic.
  const oldestHeld = recent[0]?.ts ?? lastTs;
  if (snapshotTs < oldestHeld - 1) return { ok: false as const }; // too old to validate: re-run

  for (const commit of recent) {
    if (commit.ts <= snapshotTs) continue;
    for (const w of commit.writes) {
      if (readSet.some((r) => touches(r, w))) return { ok: false as const };
    }
  }

  const ts = ++lastTs;
  recent.push({ ts, writes });
  return { ok: true as const, ts };
}

What a strong answer covers

• Checks only commits after the mutation's snapshot timestamp.
• Detects a conflict when a written key, or a moved row's old key, falls inside a read range.
• Assigns a new, increasing timestamp and records the commit only when there is no conflict.
• Makes the check and the append one atomic step (a single committer, or a lock).Supporting
• Refuses (forces a retry) when the snapshot is older than the commits still held.Supporting

What a strong answer covers

• Results can be shared when the query, its arguments and its timestamp are the same and it does not depend on who is asking.
• Bursts of invalidations collapse into one run for the newest timestamp.
• What remains is fan-out of the result to 100,000 connections, spread across sync servers.
• Queries that depend on the viewer (permissions, 'my tasks') cannot be shared directly.Supporting

In this order

1. 1Reconnect and re-authenticate the WebSocket
2. 2Resend the queued mutations in order, each with its ID; the server skips any ID it already committed
3. 3Re-subscribe to every query the screen needs
4. 4The server runs them at one timestamp that includes the replayed mutations, and sends the results tagged with it
5. 5The client drops its optimistic changes that are now included and shows the new results all at once

Reference answer

What a strong answer covers

• A change notification says a row changed, not which queries it affects; mapping that needs read sets or hand-written rules.
• Re-fetching queries separately brings back the mixed-moment problem unless they share a snapshot.
• The same read sets give serializable mutations; the alternative still needs its own answer to write skew.
• Concedes that the simpler route is right for apps with few writes, coarse invalidation or tolerant users, and costs far less to build.