EXPLAIN-style reasoning on your real queries
Six queries you should be able to plan in your head — set-once updates, claims, CTEs, HNSW.
Know your own system
The sequel to Day 29: your actual queries, planned. Practice narrating each in EXPLAIN terms.
1. The conditional update (zoth state machine).
UPDATE access_requests SET status='ACTIVE', expires_at=$4 WHERE id=$1 AND status=$2
Plan: index scan on PK for the row → recheck status=$2 on the current row version → update. MVCC note: the update writes a new row version; concurrent readers see the old one; a concurrent competing UPDATE blocks on the row lock, then re-evaluates its WHERE against the new version → 0 rows affected → your code translates that to ErrConflict (409). That is exactly how EvalRowLevelLock semantics work — the loser's predicate re-check fails silently. This is the mechanism behind your entire concurrency story; understand it at this depth.
2. Set-once topic update (traverse).
UPDATE problem SET topic=$2 WHERE id=$1 AND topic IS NULL
Same mechanism, different purpose: it's an idempotency claim. Two AI jobs racing to classify: one wins (1 row), the other's predicate re-check fails (0 rows) → skippedExisting. No explicit lock, no transaction ceremony — the WHERE clause is the mutex.
3. The outbox claim (zoth).
SELECT ... WHERE published_at IS NULL ORDER BY created_at LIMIT $1 FOR UPDATE SKIP LOCKED
Plan: partial index scan (unpublished only, created_at order) → FOR UPDATE locks matched rows; SKIP LOCKED skips rows already locked by a competing relay — two relay replicas process disjoint batches without blocking each other. Then the claim commits published_at = NOW() for exactly the claimed IDs. If the process dies before commit, locks release, rows stay NULL — safe retry. The pattern's power: no coordination service, the lock is the claim. (Day 62 compares with traverse's version.)
4. Recursive manager CTE (zoth).
Planner treats the recursion as repeated index loops: seed (PK lookup) → iterate (idx_users_manager_id lookup per level) → stop at NULL manager or level 25. Cost: ~25 index probes worst case, microseconds. If org charts grew huge you'd consider a materialized path (path text like /t/manager1/manager2/) maintained on write — tradeoff: denormalized ancestor path, but O(1) prefix queries. Know when to make that trade: when the chain query appears on a hot path (yours is per approval — human-paced, so CTE wins).
5. Vector similarity (zoth).
SELECT ... ORDER BY embedding <=> $2::vector LIMIT $3 with HNSW cosine index — approximate graph traversal; recall tunable; the crucial detail: tenant scoping in the WHERE (tenant_id=$1) interacts with ANN indexes — Postgres applies the filter during/after traversal, which can degrade recall if the filter is selective (visiting candidates that get filtered out). The production fix at scale: partition by tenant or pre-filter. At your scale: irrelevant; knowing the interaction exists = senior.
6. Keyset pagination (traverse inbox).
WHERE user_id=$1 AND id < $cursor ORDER BY id DESC LIMIT 20 — index on (user_id, id) gives a backward range scan: seek to cursor, read 20, stop. vs OFFSET 10000: reads and discards 10000 rows. The stable-under-inserts property: new rows have higher ids and can't shift pages below the cursor.
The habit to build
Before an interview: pick your five hottest queries and write, from memory, (a) the indexes serving them, (b) the plan shape, (c) one way each could degrade at 100× data. This is the difference between "I used Postgres" and "I operate Postgres."
Bulletproof your resume
Say these answers aloud before moving on
These are the follow-ups an interviewer will actually ask about your projects.
Explain how your conditional update behaves under a race, at the row-lock level.
Two transactions try to flip the same request. T1's UPDATE takes the row lock, writes a new row version (status=ACTIVE), commits. T2's UPDATE was blocked on the same row; when T1 commits, T2 re-evaluates its WHERE against the new version — status no longer equals its expected value — so it updates 0 rows and reports rowsAffected=0. My code maps that to ErrConflict/409: 'state changed concurrently; reload and retry.' No locks held across requests, no retry storms — the database's predicate re-check IS the concurrency control. Every state transition in zoth runs through this one mechanism.
Why does SKIP LOCKED matter for your outbox relay?
With FOR UPDATE alone, two relay replicas would serialize — replica 2 blocks until replica 1 commits, then re-scans and finds the batch claimed. With SKIP LOCKED, replica 2 skips the locked rows and claims the NEXT unpublished batch: both replicas drain disjoint work in parallel with zero coordination. It's the same primitive Postgres SKIP LOCKED work queues (and my traverse notification drain) are built on — the queue IS the table.
When would you maintain a materialized manager path instead of the recursive CTE?
When chain lookups become hot and deep: a path column ('/root/m1/m2/user') makes ancestor queries an index prefix match — O(1) — at the cost of rewriting descendants' paths on every manager change (an O(subtree) write). Approval routing is human-paced today, so the CTE's microseconds are fine and the path's write amplification is pure loss. The rule: denormalize read paths only when the read frequency dwarfs the write frequency of the data it copies.