Postgres as a Queue with SKIP LOCKED
A background job lands in your jobs table. Two workers wake up at the same time, both run SELECT * FROM jobs WHERE status = 'pending' LIMIT 1, and both get row 42. Both send the same welcome email. The customer gets two copies, your open-rate metrics lie, and you spend an afternoon explaining to support why a “queue” delivered everything twice.
You reach for Kafka. Stop. If your throughput is in the hundreds or low thousands of jobs per second and you already run Postgres, you do not need another moving part. One locking clause, FOR UPDATE SKIP LOCKED, turns an ordinary table into a safe concurrent queue where every worker claims its own rows and no two workers ever touch the same job. Here is the race, the fix, and the exact point where you should stop and buy a real broker instead.
The naive claim is a race
The instinct is to read a pending row, then mark it done:
-- Worker A and Worker B both run this at the same instant
SELECT id, payload FROM jobs WHERE status = 'pending' ORDER BY id LIMIT 1;
-- both get id = 42
UPDATE jobs SET status = 'processing' WHERE id = 42;A plain SELECT takes no row lock that blocks another reader. Both workers see row 42 as pending, both run the UPDATE, and both proceed to process it. The second UPDATE just rewrites the same status = 'processing' value, harmless at the SQL level, but by then both workers already hold payload in memory and both do the work. You have double-processed.
Wrapping the read in SELECT ... FOR UPDATE (without SKIP LOCKED) fixes correctness but destroys throughput. Worker B now waits for Worker A’s transaction to commit before it can even see row 42, so your pool of workers serializes into a single-file line. That is the default locking behavior: a waiter blocks until the transaction holding the row lock ends. Not a queue, a bottleneck.
FOR UPDATE SKIP LOCKED: claim without blocking
Postgres gives you a third option. The SELECT documentation puts it plainly: “With SKIP LOCKED, any selected rows that cannot be immediately locked are skipped. Skipping locked rows provides an inconsistent view of the data, so this is not suitable for general purpose work, but can be used to avoid lock contention with multiple consumers accessing a queue-like table.”
That last clause is the entire trick. An “inconsistent view” is exactly what you want for a queue: each worker should see only the rows nobody else is holding.
The claim query becomes:
UPDATE jobs
SET status = 'processing', locked_at = now()
WHERE id = (
SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY created_at
FOR UPDATE SKIP LOCKED
LIMIT 1
)
RETURNING id, payload;The inner SELECT orders by age, locks the first pending row it can grab, and skips any row another transaction has already locked. The docs are explicit that locking stops once LIMIT is satisfied, so Worker B does not wait on row 42; it silently steps over it and locks row 43. RETURNING hands you the claimed payload in the same round trip. Ten workers running this concurrently get ten different rows, every time, with no coordination.
Put the lock predicate to work with a partial index so the scan stays cheap as the table grows:
CREATE INDEX jobs_pending_idx
ON jobs (created_at)
WHERE status = 'pending';A full worker loop
Here is a complete worker in Node using pg. It claims one job, processes it, and finalizes, all inside one transaction so the lock is held for exactly as long as the work takes.
import pg from "pg";
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
async function claimAndRun() {
const client = await pool.connect();
try {
await client.query("BEGIN");
const { rows } = await client.query(`
UPDATE jobs
SET status = 'processing', locked_at = now(), attempts = attempts + 1
WHERE id = (
SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY created_at
FOR UPDATE SKIP LOCKED
LIMIT 1
)
RETURNING id, payload;
`);
if (rows.length === 0) {
await client.query("COMMIT");
return false; // queue empty
}
const job = rows[0];
await doWork(job.payload);
// success: remove it so the table stays small
await client.query("DELETE FROM jobs WHERE id = $1", [job.id]);
await client.query("COMMIT");
return true;
} catch (err) {
await client.query("ROLLBACK"); // lock released, job returns to 'pending'
throw err;
} finally {
client.release();
}
}
async function workerLoop() {
for (;;) {
const didWork = await claimAndRun().catch((e) => {
console.error("job failed", e);
return false;
});
if (!didWork) await new Promise((r) => setTimeout(r, 1000)); // idle backoff
}
}
workerLoop();The transaction boundary is the safety net. While the row is locked, no other worker can claim it. If doWork throws, ROLLBACK releases the lock and undoes the status = 'processing' write, so the job is pending again and another worker retries it. On success you DELETE the row, which keeps the table and the partial index small. If you need an audit trail, UPDATE ... SET status = 'done' instead and sweep completed rows on a schedule.
Many workers, zero coordination
The flow when several workers hit the queue at once:
sequenceDiagram
participant A as Worker A
participant B as Worker B
participant DB as Postgres
A->>DB: BEGIN; claim (FOR UPDATE SKIP LOCKED)
DB-->>A: row 42 (locked by A)
B->>DB: BEGIN; claim (FOR UPDATE SKIP LOCKED)
Note over DB: row 42 is locked, skip it
DB-->>B: row 43 (locked by B)
A->>DB: DELETE 42; COMMIT
B->>DB: DELETE 43; COMMIT
No worker waits on another. Scaling is “run more workers” until you saturate the database. Each worker holds one connection for the length of one job, so mind your pool size; a queue like this is a classic case for PgBouncer in front of Postgres so a fleet of workers does not exhaust max_connections.
Visibility timeout and stuck jobs
The transaction-scoped lock protects against contention, but not against a worker that claims a job and then crashes. On crash the connection drops, Postgres aborts and rolls back the transaction, and the row-level lock releases, so the row reverts to pending automatically. The nastier case is a worker that hangs: the row sits in processing forever, invisible to the status = 'pending' predicate.
That is what locked_at and attempts are for. Run a reaper that resurrects jobs stuck too long:
UPDATE jobs
SET status = 'pending', locked_at = NULL
WHERE status = 'processing'
AND locked_at < now() - interval '5 minutes'
AND attempts < 5;Anything past attempts >= 5 should move to a dead state for inspection rather than looping forever. This is the same discipline that keeps any worker honest, and it is why your background jobs are lying when you skip it. Because a job can run more than once, every handler must be idempotent, the exact guarantee behind idempotency keys that prevent double charges.
When this is enough, and when it is not
A Postgres queue is the right call when the producer already writes to the same database, which is precisely the shape of the outbox pattern for reliable events: you insert the job in the same transaction as your business write, so the enqueue is atomic with the state change. It comfortably drives real workloads. This is the boring, reliable machinery behind sending 10 million notifications with a simple Node.js job worker.
Reach for Kafka, SQS, or RabbitMQ when you need one of the things Postgres does not give you cheaply: sustained throughput past what a single primary can handle, fan-out where one event feeds many independent consumer groups, log retention and replay, or partition-ordered streams. A SELECT ... FOR UPDATE SKIP LOCKED queue is single-consumer-per-message by design. If your answer to “how do I scale this” is “shard the queue table,” you have already outgrown it.
Takeaway
You do not start with Kafka. You start with the database you already run. FOR UPDATE SKIP LOCKED gives you exactly-one-worker-per-job claiming with no external broker, no new failure domain, and transactional enqueue for free. Add locked_at plus a reaper for stuck jobs, make handlers idempotent, and put a pooler in front. Graduate to a real broker the day you genuinely need fan-out or throughput past a single Postgres, not one day sooner.