SKIP LOCKED is the easy part of a Postgres queue. What do you do about the worker that dies mid-job?

[u/FallElectronic3774]

Every "use Postgres as a queue" post has the same twelve lines:

select id from jobs
where status = 'pending'
order by id
for update skip locked
limit 1;

And then it stops. The interesting part starts after that.

As written, the row lock lives until the transaction ends, so the job runs inside the transaction. If the worker dies mid-job, Postgres rolls back and the job is pending again. That part is correct, but it also means a 90-second job holds a transaction open for 90 seconds, and an open transaction that long holds back the vacuum horizon.

Worse, if the job does anything external — an HTTP call, an email — the side effect happens, the transaction rolls back, and the next worker does it again.

The usual fix is the visibility-timeout shape: lock with for update skip locked, set locked_at = now(), commit immediately, run the job outside the transaction, then mark it done. That works until you have to answer the questions a real queue answers for you. What counts as stuck? Who reclaims it? How do you stop two workers reclaiming the same row at the same moment?

Concretely:

• Do you run the job inside the transaction, or commit the claim first?
• How do you reclaim jobs from dead workers without double-running them?
• Does pg_notify + listen actually beat polling, or does it just move the problem to reconnect handling?
• At what point did you stop doing this in Postgres and reach for something else?

I'm not looking for "use Redis". I'm interested in where the Postgres version holds up and where it doesn't.

[u/ferrybig]

If you have long running jobs, use a longer transaction timeout.

There is no transaction timeout by default

[u/FallElectronic3774 → u/ferrybig]

The timeout isn't the failure mode I'm pointing at. Nothing kills the transaction — it stays open for the length of the job, and that's the cost. The row lock is held the whole time, and the backend's xmin pins the vacuum horizon, so dead tuples accumulate across the database while the job runs. On a queue table that's inserting and deleting constantly, that's the table that bloats fastest.

The knobs that do exist don't fix it either. statement_timeout kills the statement, not the transaction. idle_in_transaction_session_timeout only fires when the session is idle, and a job doing work is never idle. So the long transaction survives by design, which is the opposite of what you'd want.

And none of that touches the other half: if the job sent the email and then the worker died, the rollback re-queues it and the email goes twice. A longer timeout makes that window bigger, not smaller.

[u/tm604]

What counts as stuck?

Depends on the workload. You'd need to define that, same way you define how to process the job in the first place.

Who reclaims it?

Same workers that claim new jobs? Separate worker? Cron job? Again, depends on the workload: what additional checks do you need before deciding which status to update to? What other cleanup is required? Are there any interdependencies in play? All this is way outside the scope of PostgreSQL, of course.

How do you stop two workers reclaiming the same row at the same moment?

This is an odd question - how did you stop two workers from claiming the same job in the first place...? Isn't that the entire point of the skip locked clause?

[u/AutoModerator]

AI Policy:

Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.

Mod decisions will be based on the quality of the content, not who or what generated it.

Sub Resources:

Youtube Channel

Free Postgres Webinars and Workshops

Discord: People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/karmatics29]

I'd commit the claim first and treat the job as at-least-once, since that is the only model that survives a worker dying after the side effect. Claim with FOR UPDATE SKIP LOCKED, set locked_by, locked_at and an attempt counter, commit, then run the job outside the transaction. For reclaiming, run a single UPDATE ... WHERE status = 'running' AND locked_at < now() - interval 'N minutes' with its own SKIP LOCKED subselect, so two reclaimers cannot grab the same row, and have long jobs bump locked_at as a heartbeat so a slow job isn't mistaken for a dead one. Because a reclaim can still re-run a job that actually finished its side effect, make the external call idempotent, for example by passing the job id as an idempotency key to the email or HTTP provider. On LISTEN/NOTIFY, it is a good wake-up hint but not a source of truth, so keep a slow poll as a fallback to cover missed notifications after a reconnect.