Details
Trusting Postgres to store data is one thing; trusting it to coordinate work is another. This talk walks through the design we settled on — commit the durable fact inside the transaction, fire side effects after commit, and rebuild the job queue from committed rows so the chain is always the source of truth — and the real failure modes we hit getting there: an advisory-lock self-deadlock that Postgres's own detector couldn't see (half the wait cycle lived in the application), pg-boss semantics that didn't mean what we assumed, and MVCC read-then-write races. For each: the reproduction, the root cause, and the structural guard that keeps it from recurring. Because AI agents wrote and tested much of this, the talk also covers where that helped, where it quietly didn't, and the testing discipline we adopted as a result.
What you'll take away:
- A durable coordinator pattern: commit-in-transaction, side-effects-after-commit, queue reconstructable from committed rows
- Concrete concurrency failure modes on Postgres — advisory-lock scope, pg-boss singleton/priority semantics, MVCC visibility — and how to design them out
- Why the structural guard, not the one-off fix, is what makes correctness stick
- What building and testing with AI agents got right, and what needed guardrails
