PostgreSQL SKIP LOCKED work queues: claiming, leases, heartbeats, and poison messages

Introduction

Your team needs a background job queue. Someone proposes Redis or a managed broker. Someone else points out you already run PostgreSQL for everything else and asks: can we just use the database?

The answer is yes—if you design for concurrent workers, crash recovery, and poison messages instead of treating a jobs table like a naive FIFO list. The pattern that makes this work in production is SELECT … FOR UPDATE SKIP LOCKED: workers claim rows without blocking each other, and Postgres row locks provide the coordination primitive.

I have shipped this pattern on consulting engagements where teams wanted one fewer moving part in the stack, or where job volume was moderate enough that a dedicated queue was operational overhead without proportional benefit. It is not a universal replacement for Kafka or SQS at millions of messages per hour—but for many APIs, webhooks, email sends, and report generation pipelines, a well-designed Postgres queue is durable, observable, and surprisingly capable.

This article explains the mechanics, the lease model that prevents stuck work after worker crashes, and the operational edges that separate a demo queue from something you can run on-call.

Why not a simple SELECT and UPDATE?

The naive approach:

1. SELECT id FROM jobs WHERE status = 'pending' ORDER BY created_at LIMIT 1
2. Process the job
3. UPDATE jobs SET status = 'done' WHERE id = $1

With multiple workers, two processes can read the same row before either updates it. You get duplicate processing unless you add locking.

SELECT … FOR UPDATE serializes claimers: the first worker locks the row; the second blocks until the lock releases. Under load, workers spend time waiting on each other instead of doing useful work.

SELECT … FOR UPDATE SKIP LOCKED changes the behavior: if a row is already locked, Postgres skips it and returns the next available row. Workers dequeue in parallel without a central coordinator. That single clause is the foundation of Postgres-native job queues.

Schema design: what belongs in the jobs table

A production queue row typically carries:

Column	Purpose
id	Primary key (UUID or bigint)
queue	Logical queue name for multi-tenant or priority separation
payload	JSONB job body
status	pending, processing, completed, failed, dead
attempts	Retry counter
max_attempts	Poison threshold
run_at	Scheduled execution time (delayed jobs)
locked_at	When a worker claimed the row
locked_by	Worker identity (hostname + pid, or lease token)
lease_expires_at	Heartbeat deadline; stale claims become reclaimable
last_error	Last failure message for debugging
created_at / updated_at	Audit and ordering

Indexes matter:

• Partial index on (queue, run_at) WHERE status = 'pending' keeps claim queries fast as completed rows accumulate.
• Consider partitioning or archival for old completed rows so the hot index stays small—this pairs well with retention jobs.

Keep payloads small. Store large blobs in object storage and reference them by key in payload. A queue table that holds 5 MB PDFs per row will bloat autovacuum and slow every claim.

Claiming jobs: the core query

The claim operation should be atomic: select a candidate and mark it processing in one transaction.

WITH candidate AS (
  SELECT id
  FROM jobs
  WHERE queue = $1
    AND status = 'pending'
    AND run_at <= now()
  ORDER BY run_at ASC, id ASC
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
UPDATE jobs j
SET
  status = 'processing',
  locked_at = now(),
  locked_by = $2,
  lease_expires_at = now() + interval '30 seconds',
  attempts = j.attempts + 1,
  updated_at = now()
FROM candidate c
WHERE j.id = c.id
RETURNING j.*;

Key properties:

• ORDER BY run_at, id gives FIFO-ish ordering with deterministic tie-breaking.
• attempts increments at claim time, not at completion—so a crash mid-processing still counts as an attempt when the lease expires and another worker reclaims.
• RETURNING gives the worker everything it needs without a second round trip.

Run this inside a short transaction. Do not hold the transaction open while processing the job.

Leases, heartbeats, and crash recovery

A worker can die after claiming a job. Without a lease, that row stays processing forever—a silent stall.

Lease model:

1. On claim, set lease_expires_at = now() + lease_duration.
2. While processing, the worker renews the lease periodically (heartbeat).
3. A separate reaper (or the claim query itself) treats rows where status = 'processing' AND lease_expires_at < now() as reclaimable—either reset to pending or claim directly.

Heartbeat renewal:

UPDATE jobs
SET lease_expires_at = now() + interval '30 seconds',
    updated_at = now()
WHERE id = $1
  AND locked_by = $2
  AND status = 'processing';

If rowCount === 0, the worker lost the lease—another process reclaimed the job. Stop processing and treat the work as abandoned. Continuing anyway risks duplicate side effects.

Lease duration should exceed your p99 processing time but not be so long that crash recovery is slow. Thirty to sixty seconds is a common starting point for sub-minute jobs; long-running jobs need longer leases and more frequent heartbeats.

Reclaiming expired leases

Before claiming new pending work, workers can reset stale claims:

UPDATE jobs
SET status = 'pending',
    locked_at = NULL,
    locked_by = NULL,
    lease_expires_at = NULL,
    updated_at = now()
WHERE status = 'processing'
  AND lease_expires_at < now()
  AND attempts < max_attempts;

Rows that exceeded max_attempts during reclaim cycles should move to failed or dead instead of looping forever.

Completing, failing, and poison messages

Success:

UPDATE jobs
SET status = 'completed',
    locked_at = NULL,
    locked_by = NULL,
    lease_expires_at = NULL,
    updated_at = now()
WHERE id = $1
  AND locked_by = $2
  AND status = 'processing';

Retryable failure (transient error—dependency timeout, rate limit):

UPDATE jobs
SET status = 'pending',
    run_at = now() + interval '1 minute' * power(2, least(attempts, 6)),
    locked_at = NULL,
    locked_by = NULL,
    lease_expires_at = NULL,
    last_error = $3,
    updated_at = now()
WHERE id = $1
  AND locked_by = $2
  AND status = 'processing'
  AND attempts < max_attempts;

Exponential backoff on run_at prevents hammering a broken dependency. Cap the exponent so delays do not reach days unless you intend that.

Poison message (permanent failure—bad schema, logic bug):

UPDATE jobs
SET status = 'dead',
    locked_at = NULL,
    locked_by = NULL,
    lease_expires_at = NULL,
    last_error = $3,
    updated_at = now()
WHERE id = $1
  AND locked_by = $2
  AND status = 'processing';

A dead status is your in-table DLQ. Pair it with alerts on dead row count, a dashboard, and a manual or scripted redrive path after fixing the bug. The dead-letter queue article on this blog covers redrive semantics in more depth; the same principles apply whether the sink is a separate topic or a status column.

Idempotency is non-optional. Lease expiry means the same job can run twice. Side effects (charges, emails, webhooks) must be guarded by idempotency keys or natural keys in your domain tables.

Trade-offs: Postgres queues vs dedicated brokers

When Postgres queues work well:

• Moderate throughput (hundreds to low thousands of jobs per second per queue, depending on hardware and query shape)
• Teams already expert in Postgres operations
• Strong transactional coupling: enqueue in the same transaction as business writes (outbox-style)
• Jobs need SQL visibility for support and debugging

When to reach for Redis, SQS, or Kafka:

• Very high fan-out or throughput where polling a table becomes the bottleneck
• Built-in delay queues, FIFO groups, or partition ordering without custom SQL
• Push-based delivery instead of worker polling
• Multi-region replication as a first-class feature

Polling imposes load. Mitigations: LISTEN/NOTIFY to wake workers (with backoff fallback), adaptive poll intervals, and right-sized connection pools. Do not run fifty workers each polling every 100 ms on a single small RDS instance.

Practical example: Node.js worker loop

The following worker demonstrates claim, heartbeat, processing, and completion with lease awareness. It uses pg with explicit transactions for claims and status updates.

import { Pool, PoolClient } from "pg";
import { randomUUID } from "node:crypto";
import { hostname } from "node:os";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const WORKER_ID = `${hostname()}:${process.pid}:${randomUUID().slice(0, 8)}`;
const QUEUE = "emails";
const LEASE_SECONDS = 30;

type Job = {
  id: string;
  payload: { to: string; template: string };
  attempts: number;
  max_attempts: number;
};

async function withClient<T>(fn: (c: PoolClient) => Promise<T>): Promise<T> {
  const client = await pool.connect();
  try {
    return await fn(client);
  } finally {
    client.release();
  }
}

async function reclaimExpired(client: PoolClient): Promise<void> {
  await client.query(
    `UPDATE jobs
     SET status = 'pending',
         locked_at = NULL,
         locked_by = NULL,
         lease_expires_at = NULL,
         updated_at = now()
     WHERE queue = $1
       AND status = 'processing'
       AND lease_expires_at < now()
       AND attempts < max_attempts`,
    [QUEUE]
  );
}

async function claimJob(client: PoolClient): Promise<Job | null> {
  await reclaimExpired(client);
  const { rows } = await client.query<Job>(
    `WITH candidate AS (
       SELECT id
       FROM jobs
       WHERE queue = $1
         AND status = 'pending'
         AND run_at <= now()
       ORDER BY run_at ASC, id ASC
       FOR UPDATE SKIP LOCKED
       LIMIT 1
     )
     UPDATE jobs j
     SET status = 'processing',
         locked_at = now(),
         locked_by = $2,
         lease_expires_at = now() + make_interval(secs => $3),
         attempts = j.attempts + 1,
         updated_at = now()
     FROM candidate c
     WHERE j.id = c.id
     RETURNING j.id, j.payload, j.attempts, j.max_attempts`,
    [QUEUE, WORKER_ID, LEASE_SECONDS]
  );
  return rows[0] ?? null;
}

async function renewLease(jobId: string): Promise<boolean> {
  const { rowCount } = await pool.query(
    `UPDATE jobs
     SET lease_expires_at = now() + make_interval(secs => $1),
         updated_at = now()
     WHERE id = $2 AND locked_by = $3 AND status = 'processing'`,
    [LEASE_SECONDS, jobId, WORKER_ID]
  );
  return (rowCount ?? 0) > 0;
}

async function completeJob(jobId: string): Promise<void> {
  await pool.query(
    `UPDATE jobs
     SET status = 'completed',
         locked_at = NULL,
         locked_by = NULL,
         lease_expires_at = NULL,
         updated_at = now()
     WHERE id = $1 AND locked_by = $2 AND status = 'processing'`,
    [jobId, WORKER_ID]
  );
}

async function failJob(jobId: string, error: string, retryable: boolean): Promise<void> {
  if (retryable) {
    await pool.query(
      `UPDATE jobs
       SET status = 'pending',
           run_at = now() + interval '30 seconds',
           locked_at = NULL,
           locked_by = NULL,
           lease_expires_at = NULL,
           last_error = $3,
           updated_at = now()
       WHERE id = $1 AND locked_by = $2 AND status = 'processing'
         AND attempts < max_attempts`,
      [jobId, WORKER_ID, error]
    );
  } else {
    await pool.query(
      `UPDATE jobs
       SET status = 'dead',
           locked_at = NULL,
           locked_by = NULL,
           lease_expires_at = NULL,
           last_error = $3,
           updated_at = now()
       WHERE id = $1 AND locked_by = $2 AND status = 'processing'`,
      [jobId, WORKER_ID, error]
    );
  }
}

async function processJob(job: Job): Promise<void> {
  // Idempotent side effect: e.g. INSERT ... ON CONFLICT DO NOTHING on idempotency key
  console.log(`Sending ${job.payload.template} to ${job.payload.to}`);
}

async function runWorker(): Promise<void> {
  for (;;) {
    const job = await withClient(claimJob);
    if (!job) {
      await new Promise((r) => setTimeout(r, 500));
      continue;
    }

    const heartbeat = setInterval(() => {
      void renewLease(job.id).then((ok) => {
        if (!ok) clearInterval(heartbeat);
      });
    }, (LEASE_SECONDS / 2) * 1000);

    try {
      await processJob(job);
      await completeJob(job.id);
    } catch (err) {
      const message = err instanceof Error ? err.message : String(err);
      const retryable = message.includes("timeout") || message.includes("503");
      await failJob(job.id, message, retryable);
    } finally {
      clearInterval(heartbeat);
    }
  }
}

runWorker().catch(console.error);

Enqueue from your API in the same transaction as domain writes when consistency matters:

INSERT INTO jobs (queue, payload, status, run_at, max_attempts)
VALUES ('emails', '{"to":"user@example.com","template":"welcome"}', 'pending', now(), 5);

For cross-service publishing, combine with the transactional outbox pattern so you never commit business state without the corresponding job row.

Common mistakes and pitfalls

• No lease or heartbeat. Crashed workers leave jobs in processing until manual intervention.
• Processing inside the claim transaction. Long transactions hold row locks and block other workers from claiming unrelated rows on the same page (and inflate bloat).
• Ignoring lost-lease detection. Continuing after renewLease fails causes duplicate emails, double charges, or duplicate webhooks.
• Unbounded pending poll rate. Fifty workers polling every 50 ms can dominate CPU and IOPS; use backoff and optional NOTIFY.
• No partial index on pending. Claim latency degrades linearly as millions of completed rows share the table.
• Treating all failures as retryable. Poison messages spin until max_attempts, wasting resources; classify permanent errors early.
• Assuming FIFO across priorities. If you need strict priority tiers, use separate queues or a priority column with careful ordering—not a single undifferentiated stream.
• Skipping idempotency because "Postgres is transactional." Leases and retries mean at-least-once delivery; exactly-once side effects require application-level deduplication.

Conclusion

FOR UPDATE SKIP LOCKED turns PostgreSQL into a credible work queue for many production workloads: workers claim in parallel, row locks coordinate without a separate broker, and leases recover from crashes. The implementation details—heartbeat renewal, exponential backoff, poison handling, and idempotent processors—determine whether the queue survives its first incident.

For teams building scalable backends with minimal operational surface area, a Postgres queue paired with solid observability (claim latency, queue depth by status, dead job rate, lease expiry count) often outperforms introducing Redis "just for jobs" when volume does not justify it. When throughput or delivery semantics outgrow the pattern, you will know from metrics—not from hope—and can migrate with the job schema and idempotency keys already in place.