Module 11 · Lesson 6 of 15Free

Postgres as a job queue: SKIP LOCKED

Level

Intermediate

Topic

Queues, background jobs and event streams

Reading time

19 min

Kartly has 4 receipt workers, but they all select the same waiting job. One customer receives duplicate receipts. Other customers receive nothing yet.

How can PostgreSQL give each worker different work, then recover that work when a worker disappears?

Kartly now runs separate services. This lesson stays inside the orders service and its PostgreSQL database. We will build a small queue for receipt jobs, not a shared database for every service.

All worker counts, timings and limits below are illustrative choices for this lesson.

Workers need different waiting rows

Start with a table containing unfinished jobs. A worker selects the earliest job, sends its receipt, then marks the job complete.

The first problem appears before any worker crashes. Ordinary reads do not reserve work. Two workers can read the same ready row before either changes it.

Adding FOR UPDATE locks the selected row against competing updates. Adding SKIP LOCKED tells another claimant to skip unavailable rows instead of waiting for them.^{1SELECTPostgreSQL Global Development Group, PostgreSQL 18 Documentation}

Imagine the jobs contain receipt requests for three different orders. There is no business reason to delay the second receipt behind the first.

This differs from an account balance query. Skipping a locked balance row would hide information the calculation needs. PostgreSQL explicitly describes SKIP LOCKED as unsuitable for general-purpose reads but useful for queue-like tables.^{1SELECTPostgreSQL Global Development Group, PostgreSQL 18 Documentation}

This solves competition during claiming. It does not yet explain who owns the job after the claim transaction ends.

Commit ownership before doing work

The simplest locking design keeps a transaction open while sending the receipt. The row stays locked until the worker commits.

That combines two lifetimes: a database operation and a network request. A slow receipt provider leaves the transaction open. Long-lived transactions also create operational problems, including retained locks and table bloat.^{2Transactions managementPsycopg contributors, Psycopg 3 documentation}

Instead, record a lease: permission to attempt a job until a stored deadline. Commit that record before contacting the provider.

A row lock protects the short ownership change. The lease records ownership after the lock is gone.

Our illustrative lease lasts one minute. Each attempt also receives a fresh random ownership token.

The token answers “which attempt owns this row?” The deadline answers “when may recovery take it away?”

The provider can outlive a worker’s lease. That fact will determine our delivery guarantee.

Store the queue beside orders

The orders service owns this table. Each job needs only an order reference, not a copy of the whole order.

CREATE TABLE receipt_jobs (
  id           UUID PRIMARY KEY,
  order_id     TEXT NOT NULL UNIQUE,
  state        TEXT NOT NULL DEFAULT 'ready'
               CHECK (state IN ('ready', 'running', 'done', 'failed')),
  run_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  attempts     INTEGER NOT NULL DEFAULT 0 CHECK (attempts >= 0),
  lease_token  UUID,
  lease_until  TIMESTAMPTZ,
  last_error   TEXT,
  CHECK ((state = 'running') = (lease_token IS NOT NULL)),
  CHECK ((lease_token IS NULL) = (lease_until IS NULL))
);

CREATE INDEX receipt_jobs_ready
  ON receipt_jobs (run_at, id)
  WHERE state = 'ready';

CREATE INDEX receipt_jobs_expired
  ON receipt_jobs (lease_until, id)
  WHERE state = 'running';

The ready index serves “find the earliest due jobs”. The expiry index serves “find abandoned attempts”. Both exclude unrelated states; Indexes explains the underlying lookup mechanism.

The unique order_id represents a product rule: one receipt job per order. It prevents duplicate enqueueing, not duplicate delivery. If Kartly later supports deliberate resends, those need a separate operation identity.

Enqueue within the order transaction

After inserting the order, the orders API executes this statement on the same connection, before committing. The application supplies the job UUID.

INSERT INTO receipt_jobs (id, order_id)
VALUES ($1, $2)
ON CONFLICT (order_id) DO NOTHING;

The write path is:

1. Begin the order transaction.
2. Save the order.
3. Insert its receipt job.
4. Commit both changes.
5. Return the order response.

The job becomes durable with the order, using Transactions and ACID guarantees. There is no separate publish step that a process crash can interrupt.

This boundary matters. If jobs live in another database, one local transaction cannot cover both writes. Use a transactional outbox when publishing outside that boundary.

Next, workers need to turn a ready row into a durable claim without exposing a gap between selection and ownership.

Claim and record in one statement

The worker supplies a fresh UUID as $1 for each claim attempt.

WITH candidate AS (
  SELECT id
  FROM receipt_jobs
  WHERE state = 'ready'
    AND run_at <= statement_timestamp()
    AND attempts < 5
  ORDER BY run_at, id
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
UPDATE receipt_jobs AS j
SET state = 'running',
    attempts = j.attempts + 1,
    lease_token = $1,
    lease_until = statement_timestamp() + interval '1 minute'
FROM candidate AS c
WHERE j.id = c.id
RETURNING j.id, j.order_id, j.lease_token, j.attempts;

Run this as a short, standalone transaction on the primary database. Under autocommit, the statement is that transaction. Otherwise, explicitly commit before starting delivery.

Here is the claim path:

1. Find a due ready row.
2. Lock it, skipping rows unavailable to this claimant.
3. Set its running state, deadline and token.
4. Increment the attempt count.
5. Return the claimed job and commit.
6. Contact the receipt provider outside the transaction.

The locking clause belongs inside candidate. A locking clause outside a referenced WITH query does not automatically lock that query’s rows.^{1SELECTPostgreSQL Global Development Group, PostgreSQL 18 Documentation}

After commit, another worker can inspect the row, but cannot claim it through this query: its state is now running.

An empty result means “no eligible unlocked row was found”. It does not prove the table contains no waiting jobs. Other transactions may currently hold their locks.

A worker must also distinguish a confirmed commit from a broken connection during commit. With an uncertain result, do not send immediately. Reconnect and check the stored token and unexpired lease, or leave recovery to handle the attempt.

Selection is now safe against simultaneous claimants. Completion still needs protection against a worker that returns late.

A stale worker cannot acknowledge

Suppose A claims a job and then pauses. Its lease expires. Recovery reschedules the job, and B claims it with a new token.

A resumes after B starts. An acknowledgement using only the job ID could mark B’s attempt complete.

The worker therefore acknowledges using both the job ID and its attempt token. It also requires an unexpired lease.

The following implementations belong to the receipt worker. They require Psycopg 3 in Python, or pg and its TypeScript declarations in TypeScript.

import psycopg

def acknowledge(dsn: str, job_id: str, token: str) -> bool:
    sql = """
        UPDATE receipt_jobs
        SET state = 'done', lease_token = NULL, lease_until = NULL
        WHERE id = %s
          AND state = 'running'
          AND lease_token = %s
          AND lease_until > statement_timestamp()
        RETURNING id
    """
    with psycopg.connect(dsn, autocommit=True) as conn:
        with conn.cursor() as cursor:
            cursor.execute(sql, (job_id, token))
            return cursor.fetchone() is not None

import type { Pool } from "pg";

export async function acknowledge(
  pool: Pool,
  jobId: string,
  token: string,
): Promise<boolean> {
  const result = await pool.query(
    `UPDATE receipt_jobs
     SET state = 'done', lease_token = NULL, lease_until = NULL
     WHERE id = $1
       AND state = 'running'
       AND lease_token = $2
       AND lease_until > statement_timestamp()
     RETURNING id`,
    [jobId, token],
  );
  return result.rowCount === 1;
}

These examples use one statement per acknowledgement. Psycopg autocommit avoids leaving an implicit transaction open.^{2Transactions managementPsycopg contributors, Psycopg 3 documentation} For multi-statement transactions in node-postgres, reserve one client; do not distribute statements through pool.query.^{3Transactionsnode-postgres contributors, node-postgres documentation}

A false result means this attempt could not complete the row. Log the job ID and token. Do not overwrite ownership or create a replacement job.

This protects queue state, not the provider. A stale worker may already have sent its receipt. Use the same provider-side idempotency key, such as receipt:<order_id>, across every attempt, if the provider supports that contract.

The attempt token changes on each claim. The delivery key must remain stable. Idempotency: making retries safe covers the receiving side.

If the provider cannot suppress duplicates, Kartly must accept possible duplicate receipts or add provider-specific reconciliation. A row lock cannot remove that uncertainty.

Recover attempts without losing ownership

A recovery worker periodically finds expired running jobs. It uses the same locking pattern, in bounded batches.

WITH expired AS (
  SELECT id
  FROM receipt_jobs
  WHERE state = 'running'
    AND lease_until <= statement_timestamp()
  ORDER BY lease_until, id
  FOR UPDATE SKIP LOCKED
  LIMIT 100
)
UPDATE receipt_jobs AS j
SET state = CASE WHEN j.attempts >= 5 THEN 'failed' ELSE 'ready' END,
    run_at = statement_timestamp() + interval '30 seconds',
    lease_token = NULL,
    lease_until = NULL,
    last_error = 'lease expired'
FROM expired AS e
WHERE j.id = e.id;

Commit each batch promptly. Clearing the token invalidates the old attempt. A later claim assigns a new token.

The fixed retry delay keeps this example small. Production retry timing should spread attempts and respect provider limits.

A known delivery failure can reschedule immediately through a token-guarded update. Use the same state and token checks as acknowledgement. Choose ready or failed from the attempt count, and clear both lease fields.

For an unknown outcome, such as a provider timeout, keep the stable delivery key. Retrying with a new delivery identity would defeat duplicate protection.

This design permits repeated attempts, including overlapping execution after expiry. Its guarantees fit the boundaries discussed in Delivery guarantees, not an unconditional exactly-once promise.

The attempt limit also matters: jobs can reach failed without successful delivery. Kartly needs a visible failed-job review and recovery process.

More workers can burden checkout

For this scenario, suppose the receipt burst reaches 40 jobs per second. A delivery occupies one worker slot for an estimated 200 ms.

Do not reserve a database connection for each network delivery. Release it after the claim and borrow another for acknowledgement.

Claim only as many jobs as you can start promptly. If a worker reserves a large batch, later jobs may expire before execution begins.

Keep polling bounded, and spread polling times. Maintain completed-job retention and monitor table cleanup. This table changes state repeatedly; its operational cost is not just stored payload size.

Retain the PostgreSQL queue when local atomic enqueueing and shared operations are valuable. Consider a separate broker when queue work needs an independent failure boundary or harms order latency.

Moving the queue into another PostgreSQL database also isolates some load, but loses the shared order transaction. That is a trade, not a free upgrade.

Where this shows up

• Design: a notification service: independent deliveries need claims, retry state and duplicate protection.
• Design: an e-commerce checkout: local background work can be recorded with the business transaction.

The next problem is detecting delayed receipts before customers complain; Queue lag turns waiting time into an operational signal.

Common mistakes

• Committing a selection before recording ownership. The lock disappears without a durable claim. Select and update in the same transaction.
• Treating a lease as exclusive execution. Expiry allows recovery even if the original worker is still alive.
• Acknowledging by job ID alone. An old attempt could complete a newer attempt’s row.
• Deleting every trace of completed jobs immediately. Removing the unique order reference also removes this table’s enqueue deduplication history.
• Adding workers without checking dependencies. More delivery slots can overload the provider or compete with order queries.

Check your understanding

Summary