Section 7: Four Ways the Index Can Lie - Deep Dive: The Write Path & Keeping the Index True

Executive Summary: The interviewer hands you the wheel, and you take it straight to the write path: not because 1,400 writes/s is hard, but because every lie the tag page could tell starts here. We work four walls, each with real SQL: the get-or-create race on a brand-new tag name, the single-shard transaction that keeps assignment, count and outbox in lockstep, the cache fill race that can hide Dana's tag for 10 minutes, and content that moves, locks down or gets trashed underneath us. The lesson: an index you don't own the source for can only stay true if every write is idempotent, every derived value comes from what actually changed, and every external fact carries a version.

The architecture is on the board. The interviewer caps the marker: "Where do you want to go deeper?"

You: "The write path. The table is easy, about 22 writes a second per shard. But the reverse index, the counts, the events and the forward cache are all derived from it. If a write double-counts, leaves a stale cache entry or misses a move in Jira, the index starts lying, and Section 8's funnel faithfully serves the lie."

Wall One: The Get-or-Create Race

Dana's PUT /v1/content/jira:issue:10042/tags/Checkout-Outage names a tag, not an id. The service normalizes the name first (NFKC, full case fold, whitespace collapsed, ≤ 64 characters), so "Checkout-Outage" becomes checkout-outage. Then it has to resolve that name to a tag_id, creating the tag if it doesn't exist. At 14:05, mid-incident, Priya types the same new name moments after Dana does.

Interviewer: "Just SELECT, and INSERT if it's missing."

You: "Both of them SELECT, both see nothing, and both INSERT. The unique constraint on (tenant_id, normalized_name) saves the data, but the loser gets a constraint violation and a 500 in the middle of an outage. I'd rather make the race a non-event."

-- Autocommit, before BEGIN: each statement commits on its own.
-- 1. Fast path: almost every PUT names an existing tag.
SELECT tag_id, state, alias_of
  FROM tag
 WHERE tenant_id = $tenant AND normalized_name = 'checkout-outage';

-- 2. Miss: try to create. tag_id comes from the tenant's own counter,
--    not a shard-wide sequence, so the tenant can move shards safely.
WITH id AS (
  UPDATE tenant_meta SET next_tag_id = next_tag_id + 1
   WHERE tenant_id = $tenant
  RETURNING next_tag_id - 1 AS tag_id
)
INSERT INTO tag (tenant_id, tag_id, normalized_name, display_name, state, created_by, created_at)
SELECT $tenant, id.tag_id, 'checkout-outage', 'Checkout-Outage', 'active', $user, now()
  FROM id
ON CONFLICT (tenant_id, normalized_name) DO NOTHING
RETURNING tag_id, state, alias_of;

-- 3. No row returned: someone else won. Read their row.
SELECT tag_id, state, alias_of
  FROM tag
 WHERE tenant_id = $tenant AND normalized_name = 'checkout-outage';

Get-or-create runs before BEGIN, in autocommit, so the winner's row commits the moment it exists. Two details carry the weight. First, when ON CONFLICT meets a row whose inserter hasn't committed yet, Postgres waits for that transaction to finish. So by the time step 2 returns nothing, the winner has committed, and step 3, a fresh statement under READ COMMITTED with a fresh snapshot, sees the row. Second, steps 2 and 3 have to stay separate statements. The tempting one-liner (a CTE that inserts, then UNION ALL a SELECT from tag) runs on one snapshot taken before the winner committed: the conflict check sees the winner's row, the fallback SELECT doesn't, and you get zero rows back. Whatever state comes back, the transaction re-checks it under a lock, as Wall Two shows.

Quick check before moving on: