SEO · Content Systems · Publishing Infrastructure

How to Reserve Jobs Safely From a PostgreSQL Queue

Learn how to reserve jobs safely from a PostgreSQL queue with transactional locking, SKIP LOCKED, lease expiry, collision prevention, and recovery patterns for content pipelines.

Explore SEO systems →

If two workers can see the same job, you do not have a queue yet — you have a race condition with a table behind it.

The safe way to reserve jobs from PostgreSQL is to make reservation part of a transaction, lock the row while you claim it, and commit only after the job has moved into a reserved state. That sounds simple until you add concurrency, retries, worker crashes, and the fact that your SEO pipeline may be running hundreds of generation or validation tasks at once. Once that happens, the important question is not “how do I fetch the next job?” but “how do I ensure exactly one worker owns it long enough to process it, and how do I recover when that worker disappears?”

In a content pipeline, that reservation boundary matters because the same topic can otherwise be generated twice, validated twice, or published twice. If you are already modeling lifecycle states carefully, the reservation step should fit into that same state machine; the queue article on content generation queue transitions is the right companion reading for that piece of the design.

The reservation pattern that actually holds up

A PostgreSQL-backed queue usually needs three things:

  1. A way to pick the next eligible job.
  2. A way to prevent another worker from taking the same job.
  3. A way to release or recover the job if the worker fails.

The most common mistake is reading a pending job in one statement and updating it in a second statement. Between those two statements, another worker can read the same row. That is the collision.

Instead, reserve with a single transactional claim. In PostgreSQL, that usually means selecting rows FOR UPDATE SKIP LOCKED, then updating the chosen row inside the same transaction.

A simplified pattern looks like this:

BEGIN;

WITH next_job AS (
  SELECT id
  FROM content_jobs
  WHERE status = 'pending'
    AND run_at <= NOW()
  ORDER BY priority DESC, created_at ASC
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
UPDATE content_jobs j
SET status = 'reserved',
    reserved_at = NOW(),
    reserved_by = $1,
    reservation_expires_at = NOW() + INTERVAL '15 minutes'
FROM next_job
WHERE j.id = next_job.id
RETURNING j.*;

COMMIT;

This pattern works because the row lock is acquired before another worker can reserve the same row. SKIP LOCKED keeps other workers from waiting behind a locked row; they simply move on to the next available row. That is usually what you want in a queue, because queue throughput is more important than fairness at the row level.

Why SKIP LOCKED is useful, and where it can surprise you

SKIP LOCKED reduces worker collisions, but it also changes the shape of the system.

You are trading strict ordering for concurrency. If the oldest job is locked, a newer job may be claimed first. For SEO content pipelines, that is often fine for generation, formatting, or validation jobs. It may be less acceptable for workflows where order is semantically important, such as publishing a chain of pages that must be released in sequence.

If you need stronger ordering, you can still use locks, but you may also need to partition the queue by site, content type, or dependency group. That way each worker pool competes within a smaller lane rather than across the entire queue.

The tradeoff is straightforward: the broader the queue, the more parallelism you get; the narrower the queue, the easier it is to keep dependencies clean.

If you are designing the table shape at the same time, it helps to think of reservation columns as operational state, not incidental metadata. The schema article on database tables for an SEO publishing pipeline covers how these fields fit with the rest of the content model.

What a reservation should store

A reserved job should record enough information to answer three questions later:

  • Who claimed it?
  • When was it claimed?
  • When should the claim expire?

At minimum, that usually means reserved_by, reserved_at, and reservation_expires_at. In practice, teams often also store a reservation_token or lease_id. That token helps prevent a stale worker from completing a job after the reservation has been reassigned.

For example, a worker can include its lease token when writing the final result. If the token no longer matches the active reservation, the completion update should fail. That is a simple guard against “zombie” workers that come back after a network pause or process freeze and try to write into a job they no longer own.

This matters in content systems because stale completions can be worse than duplicates. A generation job that finally returns after timing out may overwrite a newer draft, reintroduce an outdated title, or trigger a publish step for a page that already moved on.

Collisions are not just duplicate claims

Worker collisions show up in a few forms:

  • Two workers reserve the same pending job.
  • A worker reserves a job that has already expired and been reclaimed.
  • A worker updates a job after another process has already marked it failed or completed.
  • A job is selected again because the queue lacks a strong eligibility filter.

The first problem is solved with transactional locking. The second and third are solved with a lease and a version check. The fourth is solved with careful queue predicates.

A useful pattern is to make every write conditional on the job still being in the expected state:

UPDATE content_jobs
SET status = 'generated',
    output_json = $2,
    completed_at = NOW()
WHERE id = $1
  AND status = 'reserved'
  AND reservation_token = $3;

That single WHERE clause is doing a lot of work. It prevents late writes, double-completion, and accidental state regression.

Recovery is part of reservation design, not an afterthought

A reservation system is incomplete unless you define what happens when a worker dies mid-job.

There are three common recovery paths:

  • Lease expiry and requeue: if reservation_expires_at passes, another worker can reclaim the job.
  • Explicit failure: if the worker detects a permanent error, it marks the job failed and releases the reservation.
  • Manual repair: if the job is malformed or blocked by bad input, it moves into a repair state instead of looping forever.

Lease expiry is the simplest recovery mechanism, but it needs care. If the lease is too short, slow jobs will be reclaimed prematurely. If it is too long, crashed jobs will sit blocked longer than necessary. For SEO content generation, the right TTL depends on the slowest realistic task in that lane, not on the average task.

A practical approach is to renew the lease while the worker is still alive. The worker can heartbeat every few minutes and extend reservation_expires_at. That keeps long-running jobs from being stolen while still allowing abandoned jobs to recover.

When the system detects expired reservations, a reaper process can return them to pending or move them to a retryable state. If you already distinguish retryable and terminal failures, the recovery path should match that model. The companion article on retrying failed AI content generation jobs safely is useful here because the same failure semantics apply whether the job is generation, validation, or enrichment.

A reservation loop that fits SEO pipelines

For a content pipeline, the worker loop usually looks like this:

  1. Begin a transaction.
  2. Reserve one eligible job with FOR UPDATE SKIP LOCKED.
  3. Commit immediately after the reservation is written.
  4. Process the job outside the transaction.
  5. Write completion, failure, or retry status using the reservation token.

That separation matters. You do not want to hold a database transaction open while calling an external model, generating internal links, or waiting on downstream checks. Long transactions make contention worse and can block unrelated work.

The transaction should be short and boring. The expensive part should happen after the claim is committed.

That design also makes observability easier. A job in reserved with no heartbeat is probably stuck. A job that keeps getting reserved and failing quickly is probably malformed. A job that never leaves pending may be starved by the selection query or hidden behind a filter bug.

If you need a broader picture of how reservation fits into the rest of the publishing system, the article on how to build an SEO publishing workflow for a growing SaaS company shows where queueing, validation, and publication boundaries usually sit.

The practical rule

Use PostgreSQL as a queue only if reservation is treated like a lease, not a casual update. Lock rows in the same transaction that claims them. Keep the transaction short. Record enough state to detect stale workers. Make completion conditional on the active reservation. And define recovery before production traffic forces the issue.

That combination is usually what separates a queue that works in a demo from one that can survive real SEO publishing volume.

← Back to SEO Infrastructure