DE
Beta-Übersetzung

Two workers picked up the same job

A select-then-update queue hands the same row to two workers. One statement with FOR UPDATE SKIP LOCKED claims a job atomically, and a lock expiry keeps a dead worker from pinning it forever.

You added a second worker to clear a backlog and the duplicates started immediately: the same welcome email twice, the same invoice generated twice, the same webhook delivered twice. The logs show one job id claimed by two processes in the same second:

worker-1  claim job 8421 email.welcome
worker-2  claim job 8421 email.welcome

With one worker the bug was invisible. With two it is constant, because the queue never actually claimed anything.

Why select-then-update hands out the same row

The usual first version is two statements:

select id, type, payload from jobs
 where status = 'pending' and run_at <= now()
 order by run_at limit 1;

update jobs set status = 'running' where id = $1;

A plain select takes no row lock. Between the two statements any other worker runs the same select and reads the same row, because it is still pending. Both then run the update, and the second one happily overwrites a status that is already running.

Wrapping both statements in a transaction does not help. Transactions give you atomicity and isolation of writes; in the default read committed isolation level a select without for update still reads a snapshot and still blocks nobody.

Why FOR UPDATE alone is the wrong fix

Adding for update makes it correct and unusable:

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

Worker 2 now waits on the row that worker 1 locked. When worker 1 commits, worker 2 re-evaluates the row, sees status = 'running', and gets nothing back — correct, but it spent the whole time blocked on a row it was never going to get. With n workers you have serialised the queue: every worker queues up behind the same first row. Throughput does not improve when you add workers; it gets worse.

The fix: claim in one statement with SKIP LOCKED

skip locked tells Postgres to ignore rows that are already locked by someone else instead of waiting for them. Combined with a CTE, the claim becomes a single atomic statement — each worker gets a different row, nobody blocks:

with claimed as (
  select id
    from jobs
   where status = 'pending'
     and run_at <= now()
   order by run_at
     for update skip locked
   limit $1                      -- batch size, e.g. 4
)
update jobs j
   set status       = 'running',
       attempt      = j.attempt + 1,
       locked_until = now() + interval '5 minutes',
       locked_by    = $2         -- worker id, for debugging
  from claimed c
 where j.id = c.id
returning j.id, j.type, j.payload, j.attempt;

There is no gap for a second worker to slip into: the rows are locked by the select and updated by the same statement, in the same transaction. Ask for four jobs with eight workers running and each worker gets four different jobs.

Give it an index that matches the predicate, or the claim will scan the whole table once the queue grows:

create index jobs_pending_idx on jobs (run_at) where status = 'pending';

A partial index is the right shape here: it only contains the rows the claim query looks at, so it stays small even with millions of finished jobs in the table.

When the worker dies mid-job

A claimed job is running with nobody running it. Without a timeout it stays that way forever. That is what locked_until is for — a reaper puts expired rows back:

update jobs
   set status = 'pending',
       run_at = now()
 where status = 'running'
   and locked_until < now();

One detail decides whether this is a safety net or an infinite loop: an expired lock must count as an attempt. The claim statement above already incremented attempt, so a job that reliably kills its worker — out of memory, a segfault in a native dependency — runs out of attempts and is marked failed instead of being reclaimed forever by every new worker you start.

update jobs
   set status     = 'failed',
       last_error = 'lock expired'
 where status = 'running'
   and locked_until < now()
   and attempt >= max_attempts;

Retries that back off

Retrying a failing job immediately turns a broken third-party API into a tight loop against it. Push run_at into the future, growing with the attempt count and capped:

update jobs
   set status     = 'pending',
       last_error = $2,
       run_at     = now() + least(
                      interval '1 hour',
                      interval '1 second' * power(2, attempt)
                    )
 where id = $1;

Attempt 1 waits two seconds, attempt 5 waits about half a minute, attempt 12 and beyond waits an hour.

Handlers still have to be idempotent

SKIP LOCKED removes concurrent double-claims. It does not make delivery exactly-once, and nothing in a queue can: a worker that finishes the work and dies before committing the done status will have its lock expire and the job will run again. Plan for at-least-once. Carry an id in the payload and make the write conditional, instead of appending blindly:

await db.query(
  `insert into emails (user_id, kind) values ($1, 'welcome')
   on conflict (user_id, kind) do nothing`,
  [userId],
);

Keep payloads small — ids and a handful of scalars — and load the current data inside the handler. A payload serialised an hour ago is a snapshot of an hour ago, and a payload is also the worst place to keep a secret, since it sits in a table and shows up in every debugging query.

Enqueueing the same job once

The "send a digest when this user's cart changes" job should not pile up ten rows if the cart changed ten times. A unique partial index on a dedupe key is enough:

create unique index jobs_dedupe_idx on jobs (dedupe_key)
  where status in ('pending', 'running');

Enqueue with on conflict do nothing and the second attempt is a no-op while one is still pending, and allowed again once the first finished.

Serverless: no worker to run

On Vercel, Netlify or Workers there is no long-running process to loop in. Expose an authenticated endpoint that drains due jobs and let the platform's cron call it every minute:

// app/api/cron/route.ts
export async function GET(req: Request) {
  if (req.headers.get('authorization') !== `Bearer ${process.env.CRON_SECRET}`) {
    return new Response('unauthorized', { status: 401 });
  }
  const ran = await runDue({ budgetMs: 50_000 });
  return Response.json({ ran });
}

Two cron invocations overlapping is harmless — that is the whole point of SKIP LOCKED, the second one simply claims different rows. Keep a time budget below the platform's request limit so the function returns instead of being killed mid-job, and never expose the endpoint without the secret: it is a remote "run arbitrary queued work now" button.

If you would rather not maintain this

@core/jobs is this queue as source code in your repo: FOR UPDATE SKIP LOCKED claims, exponential backoff, timeouts with AbortSignal, dedupe keys, cron schedules with timezones, and the serverless cron endpoint above. It lives on the Postgres you already have — no Redis, no extra service.

genpm add @core/jobs

Jobs are defined with a Zod-validated payload, so enqueue is type-checked against the handler:

export const sendWelcome = defineJob(
  'email.welcome',
  z.object({ userId: z.string() }),
  async ({ userId }, { signal }) => { /* … */ },
);

await sendWelcome.enqueue({ userId }, { dedupeKey: `welcome:${userId}` });

It installs into src/lib/jobs, with adapters for Hono and Next.js. Without the CLI installed: npx -y @genpm-net/cli add @core/jobs.