Postgres Job Row Durability: A Backstop Sweep That Doesn't Double the Writes

The most expensive class of production bug is the one where nothing appears broken. An analysis pipeline in our citation-verification product had been happily finishing runs, writing verdicts, and rendering reports for months — except that once a week or so, a customer would email to ask where the report they had opened yesterday had gone. It wasn't in the sidepanel. It wasn't in the admin view. The job_citations row was there, verdicts and all; the analysis_runs row that the UI reads from was not. This is the writeup of the Postgres backstop sweep pattern that fixed it — and of the two follow-up commits that had to land before the sweep was safe to run alongside itself during a Coolify rolling deploy.

The bug and the fix both live inside Acurio, our citation-verification product for academic theses (the repo name is zoterohero). The relevant tables are analysis_jobs — the work queue, one row per uploaded thesis, holding the extracted document body and the per-citation verdicts — and analysis_runs — the human-facing artifact the UI reads to render a completed report. Every finished job has one run. Or is supposed to.

The One-Writer Design That Lost Nine Runs

The original design was small and elegant. analysis_runs had exactly one production writer: POST /api/analysis-runs, called by the browser tab as soon as its poll saw the job flip to complete. The client shaped the payload, sent it up, the row appeared, and the sidepanel populated. One writer. No coordination problem. Simple.

The failure mode was subtle enough that it hid for months. The write only happens if a tab is still watching when the job finishes. A tab that has been closed, reloaded, navigated to another run, or backgrounded past the poll's exponential-backoff ceiling doesn't send that POST. The job is still fully finished — its verdicts are on disk, its document body is on disk — but the artifact row the UI reads never appears. A snapshot of production found nine finished runs with five hundred sixty-five judged citations sitting in job_citations, invisible to the sidepanel past a twenty-four-hour trim on doc_html, and invisible to the internal /admin/runs dashboard past its sixty-minute in-flight window. From the user's side, the report had rendered fine at the time — and then quietly stopped existing.

The obvious fix is to add a second writer. The worker just finalized the job; it already has the shaped payload in memory; it can write the row itself. We shipped that. The nine ghost runs stopped happening. What started happening instead was a different problem, one that only shows up when you're already at production scale: every ordinary run now wrote that row twice, the worker's write and the client's save POST milliseconds later, in a table whose row carries doc_html — the extracted thesis body, several megabytes for a long PhD. Postgres stores oversized columns out-of-line in TOAST, so each of those writes fans out into TOAST-chunk inserts, the primary-key index gets a fresh tuple, and the previous tuple becomes garbage that autovacuum has to sweep later. All to cover a case that only happens when nobody is watching.

The Backstop Sweep Pattern

The pattern that resolved it is one we've used elsewhere in the same file for claim protocols, and it generalizes cleanly. Instead of writing eagerly and paying the cost on the common path, defer the write and let a periodic sweep pick up only the rows that need rescuing.

Concretely: the worker no longer writes the row at finalize. It writes the verdicts to job_citations, marks the job terminal, and stops. A new sweepUnpersistedRuns function runs on the existing thirty-second resume-sweeper tick and looks for terminal jobs that still hold their document and their verdicts, whose analysis_runs row is missing, and which nothing has touched for two minutes. The two-minute grace is the load-bearing constant: a browser tab that is present writes the row within a tick or two of the job going terminal, so the grace window keeps the dominant path — tab open, run completes — at exactly one write. Anything still missing after two minutes had no browser behind it, and the sweep records it. The judge and the ops-diagnosis hooks moved with it, because they used to fire on the finalize write and would otherwise never fire for these.

SELECT j.id, j.run_id
  FROM analysis_jobs j
  LEFT JOIN analysis_runs r ON r.id = j.run_id
 WHERE j.status IN ('complete', 'failed')
   AND j.deleted_at IS NULL
   AND j.doc_html IS NOT NULL
   AND j.results IS NOT NULL
   AND j.results <> '{}'::jsonb
   AND j.updated_at < NOW() - ($1::int * INTERVAL '1 second')
   AND r.id IS NULL
 ORDER BY j.updated_at ASC
 LIMIT $2
 FOR UPDATE OF j SKIP LOCKED

Two guards that turned out to matter. The sweep is detached from the tick, because judging a recorded run is an LLM call that can outlast the thirty seconds that started it, and lease recovery running on the same tick must not queue behind a network round trip. The sweep also carries an in-flight boolean so it never stacks on itself — if a slow LLM call is still returning when the next tick fires, the second sweep skips and the first one finishes. The work is idempotent because the row insertion is upserted on primary key, so nothing is lost by skipping.

The lesson that generalizes is one we hinted at when we split the analyzer out of Next.js into a Bun worker: a durable background system almost always has two paths, one that is fast and expected and one that is slow and unlikely. Putting both on the same code path costs you the throughput of the fast path. Splitting them lets you optimize each for the shape of its own traffic. The client-writes-then-sweep pattern is the row-durability version of the same trade-off.

SKIP LOCKED, Oldest First, Drain-Aware

Shipping the sweep to production surfaced the second problem within a day. Coolify's rolling redeploy briefly runs the old and new containers side by side while the health check on the new one stabilizes — normal, invisible, deliberate. Both containers boot the resume sweeper. Both containers scan for missing rows every thirty seconds. Both containers pick the same missing row. Both upsert it. The sweep, whose whole reason to exist is to remove the double write, was quietly recreating one during every deploy.

The fix is the same idiom every other sweep in the file already uses, and one we've written up before in the context of durable Postgres queues: FOR UPDATE OF j SKIP LOCKED. The official Postgres semantics are exactly what you want here — a row that another transaction has locked is silently skipped rather than blocked on, so two concurrent sweepers claim disjoint rows without a distributed lock and without knowing the other exists. It is one line of SQL, and it is the difference between "a sweep that works" and "a sweep that survives the deploy that made you write it."

Two smaller changes went with it. The scan sort flipped from newest-first to oldest-first, because the constant that governs whether a row can still be recovered is the twenty-four-hour doc_html trim, and the job that has waited longest is the one closest to losing the body the sweep needs to write. A backlog is exactly the situation where order matters, and a newer job gets its turn on the next tick either way. The sweep also registered itself as in-flight work so SIGTERM — Coolify's shutdown signal — drains it instead of cutting between the moment a row is recorded and the moment the judge fires against it. That specific race would leave a run row that exists but has never been graded, and no later sweep looks at it again because the row-missing filter no longer matches.

The composition here rhymes with the debounced-autosave races we wrote up two weeks ago, where a flush() primitive and an in-flight save had to compose without racing each other. The rule is the same at both ends of the stack: any code path that might trigger a write to a resource needs to know whether another writer has that resource in flight. In the browser you track a promise in a ref. In the database you take a row lock. The mechanism is different; the discipline is identical.

Three Rules for a Two-Writer Row

Three rules survive this rewrite, and they generalize to any table with a happy-path writer and a durability-path writer:

Pick who wins. The moment a row has two possible writers, add a nullable column that records which one wrote it — saved_by in our case, client or worker — and encode a precedence rule at every write site. A client save always outranks a worker save, a later job may replace an earlier worker row, a legacy NULL row counts as client. Making the tiebreaker explicit at the schema level costs one migration and prevents a decade of "which one is authoritative" arguments in review.

Give the fast path a grace window. A backstop that fires immediately is not a backstop — it is a second writer competing with the first. Pick a grace long enough that the happy path always completes inside it (two minutes was ~50× the observed client-save latency for us) and short enough that a browserless job doesn't get its doc_html trimmed before rescue.

SKIP LOCKED anywhere two containers might race, and drain the sweep with them. A rolling deploy is the cheapest way to prove your assumptions about concurrency. If your sweep would misbehave with two of itself running for thirty seconds, it will misbehave every deploy. FOR UPDATE ... SKIP LOCKED is one line; the alternative is a distributed lock service you did not want to run. And if the sweep does follow-up work on what it recorded, mark it as in-flight so SIGTERM waits for it — otherwise the recorded-but-not-graded state is a bug no later sweep looks at.

The finished-run accounting is quiet again. Nine ghost runs became zero, and the write cost of the common path went from two large tuples to one. If you are running a background pipeline on a single-container Coolify or Fly deploy and starting to wonder whether the in-process writer is enough, the answer is almost certainly plus a backstop sweep — not replace with a two-writer scheme.

If you are wiring up a durable background job pipeline in Postgres — or already have one that is losing rows in ways nobody has quite pinned down — book a free AI Potenzial-Check, or read our Next.js worker split with pg_notify writeup for the process-split half of the same "durability under redeploy" theme.