Postgres as the Timer Wheel: Scheduling Workflows Without a Message Broker

A workflow engine has to wake work up at the right moment. The instinct is to reach for a broker with delayed delivery, or to hold an in-memory timer wheel and rebuild it on restart. Both are more machinery than the problem needs.

A scheduled workflow is a row with a `next_fire_at` timestamp. "What is due?" is a range scan. A million waiting workflows consume zero engine memory — they are a million rows, and the database already knows how to find the ones whose timestamp has passed. Restart the process and nothing is lost, because nothing was in memory to lose.

The hard part is not finding due rows. It is letting several engine nodes claim from the same table concurrently without any two of them claiming the same row, without a distributed lock, and without one busy tenant starving everyone else.

`FOR UPDATE SKIP LOCKED` is the primitive that makes this work. It locks the rows it returns and silently skips rows another transaction has already locked. Two nodes running the same query at the same instant get disjoint result sets, with no coordination between them and no lock table of your own.

WITH locked AS (
    SELECT *
    FROM task_instances
    WHERE (next_fire_at IS NULL OR next_fire_at <= $1)
      AND state = 'scheduled'
    ORDER BY priority DESC, next_fire_at ASC NULLS FIRST
    LIMIT $4                          -- over-select
    FOR UPDATE SKIP LOCKED
), ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY tenant_id
               ORDER BY priority DESC, next_fire_at ASC NULLS FIRST
           ) AS rn
    FROM locked
)
SELECT * FROM ranked
WHERE rn <= $3                        -- max rows per tenant
ORDER BY priority DESC, next_fire_at ASC NULLS FIRST
LIMIT $2

The supporting index is partial, which is what keeps it small enough to stay useful. Only scheduled rows are ever claimed, so completed and failed instances — eventually the overwhelming majority of the table — never enter the index at all:

CREATE INDEX idx_instances_fire
    ON task_instances (next_fire_at)
    WHERE state = 'scheduled';

The first version of that query did the ranking and the locking in one subselect. It looked cleaner and it was wrong.

PostgreSQL does not permit a locking clause in a query containing a window function. The documented reason is that `FOR UPDATE` requires returned rows to be clearly identifiable with individual table rows, and window functions break that correspondence. Depending on the exact shape, you get an error — or, worse, a plan whose locking behaviour is not what you assumed.

That split creates a second problem. If the inner CTE locks exactly `limit` rows and one tenant's backlog fills all of them, the per-tenant cap trims most of them away and the caller gets a nearly empty batch — a fairness rule starving the scheduler it was meant to protect. Hence the over-select: lock `limit * max_per_tenant` rows so that after trimming there are still enough distinct-tenant rows to fill the batch. The multiply saturates, because a pathological configuration should degrade rather than overflow.

The `next_fire_at IS NULL` branch is a smaller lesson with the same shape. A scheduled row with no explicit fire time is due immediately. The SQLite backend treated it that way; the Postgres query originally did not, so null-fire rows sat in the table forever, invisible. Two backends behind one trait need conformance tests asserting identical semantics, not just identical signatures — a type system will happily let two implementations disagree about what "due" means.

Claiming rows is the easy half. The naive next move is to loop over claimed instances and, for each one, fetch its pending signals and its completed block IDs. With a batch of 200 that is 400 round trips per tick, and at a 100ms tick the database spends its life answering the same two questions.

So the tick fetches both sets for the entire batch in two queries and distributes them in memory. It is an obvious optimisation that is easy to skip while getting correctness right, and it is usually worth more than anything you will do to the claim query itself.

The related win is executing every ready step for an instance within one claim cycle instead of one step per tick. A five-step workflow with no delays completes in a single claim rather than five ticks — a 500ms latency floor becomes roughly one. Nothing about the persistence model changes; the engine just stops artificially returning to the scheduler between steps that were both ready.

A claimed instance is marked Running. If the node holding it dies, the instance stays Running forever and its work is never finished. The standard fix is a reaper that finds instances Running for longer than some threshold and returns them to Scheduled.

That fix has a well-known failure mode. A step that legitimately takes longer than the threshold — a large export, a slow provider, a human approval — looks exactly like a dead node. The reaper re-dispatches it while the original node is still working, and now two nodes are executing the same step against the same external system.

The distinction the reaper needs is not "how long has this been running" but "is the node that owns it still alive." So while a step is in flight, the owning node periodically touches the instance's `updated_at`. A slow-but-healthy step keeps refreshing its lease; a dead node stops. The reaper then only recovers instances whose owner actually died, and long-running work is never pulled out from under a live process.

This is the same insight as the lock problem in any leased system: the timeout is not evidence of death, it is evidence of silence. If you want to detect death, make the live case produce a signal.

A subtle failure this design invites: the tick loop panics and dies while the HTTP server keeps serving. The API accepts new workflows cheerfully, returns 200s, and nothing ever executes. Every dashboard is green. Work accumulates silently until someone notices that a scheduled report never arrived.

GET /health/live   -> always 200 (the process exists)

GET /health/ready  -> 200 only if:
                        - the database is reachable, AND
                        - the engine tick loop is alive
                      503 otherwise, so the orchestrator pulls the pod
                      instead of leaving a zombie API accepting work
                      it will never execute.

Whenever a process has a background loop doing the real work and a request surface answering health checks, they must not be able to disagree about whether the process is healthy. Assert the loop.

Postgres-as-queue works extremely well up to a point, and it is worth being precise about where that point is rather than discovering it in production.

• **Claim throughput has a ceiling.** Every claim is a write transaction. Tens of thousands per second on one primary is achievable with tuning; hundreds of thousands is a different architecture. Measure your own workload rather than trusting anyone's headline number, including mine.
• **Dead tuples accumulate.** A high-churn queue table generates enormous update volume. Autovacuum needs per-table tuning or the index bloats and the range scan degrades. This is the failure people actually hit, and it arrives weeks after launch.
• **Long transactions are poison.** Holding a claim transaction open during a slow HTTP call pins the snapshot horizon and blocks vacuum across the database. Claim, commit, then execute.
• **Fan-out is not free.** A broker distributing one message to many consumers is doing something Postgres has to emulate with more rows and more writes.
• **Sub-millisecond scheduling is out of scope.** A 100ms tick means up to 100ms of scheduling latency. That is fine for business workflows and wrong for trading.

What you get in exchange is worth stating plainly, because it is why the trade is usually right: your queue is in the same transaction as your data. Enqueueing a job and updating a row is atomic — no outbox, no dual-write gap, no reconciliation between the broker's idea of reality and the database's. That single property removes an entire category of bug that broker-based designs spend real effort managing.

For a workflow engine, that trade is almost always correct. Business processes are measured in seconds and days, not microseconds, and correctness across a crash matters far more than scheduling precision. If your workload genuinely outgrows it, the migration is a storage-layer change behind a trait — not a rewrite — which is itself a reason to start here.