The Problem Every Indexer Hits Eventually
A blockchain indexer starts life as one process reading one block at a time. (The full architecture of the one I work on is in the case study Indexing a Blockchain in Real Time, at Scale.) Then a backfill comes along: two million historical blocks to catch up on. One worker would take days, so you start ten.
Now the ten workers need to split the work. Worker 3 takes blocks 0 to 9,999, worker 7 takes 10,000 to 19,999, and no two workers should be indexing the same range at the same time. When a worker crashes halfway through its range, someone else has to pick that range up.
The reflex is to reach for a queue: Redis with BullMQ, SQS, RabbitMQ. That's one more service to deploy, monitor, secure and pay for, and one more place for state to drift out of sync with your database.
Before you add any of that, look at the database you already run. PostgreSQL has had a feature built for exactly this since version 9.5: FOR UPDATE SKIP LOCKED. With one table and one query, ten workers can claim different jobs concurrently without waiting on each other, and leases let another worker retry a job when its owner crashes.
This post covers how it works, the traps that hurt in production (stale workers, long transactions and table bloat), how to watch the queue, and the point where you really should move to a dedicated queue.
The Jobs Table
Start with the table. Each row is one unit of work, here a range of blocks to index.
CREATE TABLE jobs (
id bigserial PRIMARY KEY,
kind text NOT NULL,
payload jsonb NOT NULL,
status text NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending', 'running', 'done', 'failed')),
run_at timestamptz NOT NULL DEFAULT now(),
attempts int NOT NULL DEFAULT 0,
locked_by text,
claim_token uuid,
locked_until timestamptz,
created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO jobs (kind, payload)
SELECT 'index_blocks',
jsonb_build_object('from', g, 'to', g + 9999)
FROM generate_series(0, 1999999, 10000) AS g;
That's 200 jobs. Now ten workers need to take them one at a time, safely. (claim_token will matter later, when we deal with workers that come back from the dead.)
Three Ways to Get It Wrong
Attempt 1: Read, then update
SELECT id FROM jobs WHERE status = 'pending' ORDER BY run_at, id LIMIT 1;
-- application code holds the id
UPDATE jobs SET status = 'running' WHERE id = $1;
Two workers run the SELECT at the same moment, both see job 42, and both update it. Nothing in the second statement checks that the job is still pending, so both workers index the same blocks.
In production: duplicate rows, unique constraint violations in the indexed tables, and a backfill that does more work than it needs to. You might add AND status = 'pending' to the update and check the affected row count, which closes the race, but now the loser has to retry, and under load most workers spend their time losing.
Attempt 2: Lock the row with FOR UPDATE
BEGIN;
SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY run_at, id
LIMIT 1
FOR UPDATE;
-- ... mark it running, commit ...
This one is correct: only one transaction can hold the row lock. (How row locks and advisory locks work underneath is covered in the Locking Internals module of PostgreSQL In-Depth.) The problem is what the other nine workers do. They all run the same query on the same data, so they all reach the same first row and wait for its lock.
When the lock is released, PostgreSQL rechecks the row against the query's conditions. Its status is now running, so the waiting worker can't have it and moves on to the next pending row. But the other eight waiters do the same thing at the same moment, so they pile onto the second row together, and the line forms again.
In production: you scale from one worker to ten and throughput barely moves. With this query pattern, adding workers mostly adds lock contention, not throughput. Workers spend their time waiting in a line for rows someone else is about to take, instead of claiming independent jobs.
Attempt 3: Advisory locks
pg_try_advisory_lock(id) can work, but you manage the lock yourself, session-level locks survive a connection being returned to the pool, and a lock call in the WHERE clause can lock rows the LIMIT later throws away. It's a sharp tool for a problem that already has a built-in answer.
SKIP LOCKED: Walk Past the Plates Someone Is Holding
Picture a buffet. With plain FOR UPDATE, everyone queues behind the first person and waits for them to finish choosing. With SKIP LOCKED, if someone already has their hand on a plate, you take the next one. Nobody waits and nobody grabs the same plate.
That's exactly what the clause does. When the query hits a row that another transaction has locked, it doesn't wait. It skips the row and keeps scanning until it has found enough rows it can lock.
Here's the claim query. It picks a job, locks it, marks it running and returns it, all in one statement:
WITH next AS (
SELECT id
FROM jobs
WHERE status = 'pending'
AND run_at <= now()
ORDER BY run_at, id
LIMIT 1
FOR UPDATE SKIP LOCKED
)
UPDATE jobs j
SET status = 'running',
attempts = j.attempts + 1,
locked_by = $1,
claim_token = gen_random_uuid(),
locked_until = now() + interval '5 minutes'
FROM next
WHERE j.id = next.id
RETURNING j.id, j.kind, j.payload, j.claim_token;
Run it from ten workers at once and each gets a different job. Raise the LIMIT to claim a batch of ten in one round trip. id in the ORDER BY is a tie-breaker, so jobs scheduled for the same instant come out in a predictable order.
One thing to know: SKIP LOCKED deliberately gives you an inconsistent view of the table, because it pretends locked rows don't exist. That's exactly right for a queue and exactly wrong for almost anything else, such as reports, balances or counts. Keep it on queue tables. The PostgreSQL docs on the locking clause say the same.
The First Trap: Holding the Transaction Open
The tempting way to run a job looks like this:
BEGIN
claim job with SKIP LOCKED
index 10,000 blocks <- minutes
mark job done
COMMIT
It feels elegant. If the worker crashes, the transaction rolls back and the row lock is released, so the job becomes claimable again with no extra code.
It's also one of the most expensive patterns you can run against PostgreSQL:
- Every worker pins a database connection for the whole job. Ten workers means ten connections sitting idle in a transaction for minutes. With a small pool, the rest of your application starts waiting for connections.
- Long transactions hold back vacuum. MVCC is time travel for rows: old row versions stay around as long as any running transaction might still need to see them. This transaction has already written something (the claim), which gave it a transaction ID. Until it ends, vacuum can't remove row versions that became dead after that ID was assigned, because Postgres has to treat the transaction as one that might still need them. A read-only transaction holds vacuum back the same way while it keeps an old snapshot, for example under
REPEATABLE READ. The effect reaches every table in the database, not just the jobs table. Your hottest tables bloat while the job runs. - Idle-in-transaction timeouts will kill it. If you've set
idle_in_transaction_session_timeout(and you should have), long jobs get killed partway through.
The fix is to claim and commit straight away, then run the job outside any transaction. The claim query above does exactly that: it's a single auto-committed statement. The row is no longer locked, but status = 'running' keeps other workers away from it.
Leases Replace Locks
Once the job runs outside a transaction, a crashed worker no longer releases anything automatically. That's what locked_until is for. It's a lease: the worker owns the job until that time, and then the job is up for grabs again.
Why a worker ID isn't enough
Picture this sequence:
- Worker A claims job 42.
- Worker A stalls in a long garbage-collection pause, or loses its network for a minute.
- Its lease expires, and the job goes back to pending.
- Worker A's process restarts with the same worker ID and claims job 42 again (or worker B claims it).
- The old, stalled execution of worker A wakes up and marks the job done.
If ownership checks only compare locked_by, step 5 succeeds whenever the new claim carries the same worker ID, and a stale execution completes a claim that belongs to someone else. That's why the claim query generates a fresh claim_token for every claim. The worker ID says who is working on the job. The token says which claim this is.
A long job extends its lease as it goes (a heartbeat), and only the current claim can do that:
UPDATE jobs
SET locked_until = now() + interval '5 minutes'
WHERE id = $1
AND claim_token = $2
AND status = 'running';
Completion checks the same token:
UPDATE jobs
SET status = 'done',
locked_by = NULL,
claim_token = NULL,
locked_until = NULL
WHERE id = $1
AND claim_token = $2
AND status = 'running'
RETURNING id;
If no row comes back, the claim was lost: another worker now owns the job, or the reaper took it back. The worker must stop processing and must not treat the job as completed. That doesn't undo side effects it has already performed, which is why those need to be idempotent (more on that below). If a heartbeat returns no row, the worker should stop working on the job right away.
A reaper, which can be a cron job or any worker on a timer, takes back expired leases. It clears the token, so no stale execution can match it afterwards. It also pushes the retry into the future with exponential backoff and some jitter, so a job that fails because a downstream service is down doesn't get retried in lockstep with every other failing job (the Timeouts, Retries, and Backoff module explains why jitter matters):
UPDATE jobs
SET status = CASE WHEN attempts >= 5 THEN 'failed' ELSE 'pending' END,
run_at = now() + make_interval(
secs => least(
3600.0,
30.0 * power(2.0, least(attempts, 20)) * (0.5 + random())
)
),
locked_by = NULL,
claim_token = NULL,
locked_until = NULL
WHERE status = 'running'
AND locked_until < now();
The cap applies after the jitter, so no retry waits longer than an hour. Because the claim query increments attempts when a job is claimed, the first failed attempt waits 30 to 90 seconds, the second 60 to 180, and so on until the one-hour cap. Use the same backoff when a worker catches an error and gives the job back on purpose. If many leases can expire at once (say, a whole worker fleet restarts), reap in bounded batches with the same SKIP LOCKED pattern instead of one big update, so recovery doesn't arrive as a burst of WAL and dead tuples.
Note what actually takes a job away from a worker here. An expired lease only makes the job eligible for recovery. The reaper is what revokes the claim, by clearing its token. So a worker that finishes just after its lease expires, before the reaper has run, can still complete the job, and that's fine: nobody else has claimed it yet. Once the reaper clears the token, the old claim can never match again.
At-least-once, not exactly-once
A job can still run more than once. A worker can finish the work and crash just before marking it done. The lease expires and another worker runs it again. That's not a bug in this design. Every queue that redelivers work behaves this way, including SQS and BullMQ. The queue gives you at-least-once processing, not exactly-once.
The claim token protects the queue row. It can't stop a stale worker from sending an email or calling a webhook after it has lost its lease. Checking the token right before the side effect makes the window smaller but doesn't close it. Closing it completely needs the receiving system to reject stale writers, which is what fencing tokens do (see Distributed Locks and Coordination). Idempotency has to cover the job's real side effects: for the indexer, that means writing indexed rows with INSERT ... ON CONFLICT DO NOTHING or an upsert, so indexing the same block range twice gives the same result. For external calls, use idempotency keys or deduplication on the receiving side. I covered the pattern in Idempotency Keys: How to Make Retries Safe in Distributed Systems.
The Second Trap: The Queue Table Bloats Itself
A queue table is the worst-case workload for MVCC. Every job is inserted, updated to running, updated again (sometimes several times, for heartbeats) and then marked done or deleted. In PostgreSQL an UPDATE doesn't change a row in place. It writes a new version and leaves the old one behind as a dead tuple for vacuum to clean up.
So a table that only ever holds a few hundred pending jobs can contain hundreds of thousands of dead row versions. The production symptom is subtle: the claim query slowly gets slower, even though the queue is short. Each claim has to step over dead index entries and dead heap tuples to find the live pending rows at the front of the queue.
Three things keep it under control.
1. A partial index on exactly what the claim query reads
CREATE INDEX jobs_claimable_idx
ON jobs (run_at, id)
WHERE status = 'pending';
It matches the claim query's filter and its ORDER BY run_at, id, so the claim reads the front of the index directly. Because it only covers pending jobs, it's far smaller than an index over the whole table.
It is not automatically tiny, though. When a job moves from pending to running, its old index entry doesn't vanish. It stays in the index as a dead entry until vacuum removes it. (Why B-tree entries outlive the rows they point to is covered in How a B-Tree Really Reads, Inserts, Updates and Deletes.) Under heavy churn the index fills up with entries for jobs that were claimed long ago, even when only a handful are pending right now. The partial index makes the claim efficient. It doesn't replace vacuum.
2. Keep finished jobs out of the hot table
Don't let done rows build up in the table you claim from. Either delete them when they finish, or move them to a history table on a schedule. If you need to keep history for weeks, partition the history table by time and drop old partitions. Dropping a partition removes its data as a catalog and file operation, without creating a dead tuple per row, while DELETE of a million rows creates a million dead tuples for vacuum to process. It isn't free: a plain detach or drop takes an ACCESS EXCLUSIVE lock on the parent table. That mode conflicts with everything, even plain reads, so while the request waits for running queries to finish, every new query on that table queues behind it. If that matters, use ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY first. It takes a weaker lock, but it can't run inside a transaction block or when the table has a default partition, and if it's interrupted you have to finish it with DETACH PARTITION ... FINALIZE. The Advanced Partitioning module covers partition maintenance with pg_partman, and Scaling a Blockchain Database From a Single Partition to 4.6 TB shows the same idea on a real indexer.
3. Tell autovacuum this table is special
Autovacuum's default trigger is a fraction of the table's estimated size, tuned for ordinary tables. A queue table has a small live set and a high churn rate, so give it a fixed trigger instead:
ALTER TABLE jobs SET (
autovacuum_vacuum_scale_factor = 0,
autovacuum_vacuum_threshold = 1000
);
That means: vacuum after roughly every 1,000 dead tuples, however big the table is. The 1,000 is a starting point, not a recommendation for every system. Pick it from the table's update rate, how long a vacuum takes, and how much bloat you can tolerate.
Leave vacuum throttling (autovacuum_vacuum_cost_delay) at its default to begin with. Turning it off lets vacuum run flat out, and on a database that's already busy with indexing traffic, that I/O competes with your real queries. If vacuum can't keep up, look at how long it runs, the cost limits, your spare I/O and the competing workload before you touch throttling. The routine vacuuming docs cover these settings in depth, the Autovacuum module walks through the settings people most often get wrong, and I wrote about what autovacuum actually does in Every Postgres Write Triggers Five Background Processes.
And remember the first trap: none of this works if a long-running transaction is holding back vacuum. Bloat and long transactions are the same problem seen from two sides.
Watching the Queue in Production
A Postgres-backed queue needs watching at two levels.
Queue health:
- Pending and running job counts.
- Age of the oldest pending job (the number your users actually feel).
- Job duration, retry rate and failure rate.
- Expired leases per hour, which tells you about crashing or stalling workers.
- Claim query latency.
Database health:
- Dead tuple trend and autovacuum frequency for the jobs table, from
pg_stat_user_tables.n_dead_tupis an estimate, andlast_autovacuumalone doesn't prove vacuum is keeping up, so watch the trend alongside claim latency. - Connection usage.
- WAL volume and replication lag. Queue churn is all writes, and on an indexer that can push replicas behind (see Taming PostgreSQL Replication Lag in Real-Time Blockchain Indexers).
- Long-running transactions and sessions stuck idle in transaction, from
pg_stat_activity.
A growing queue doesn't automatically mean Postgres is the bottleneck. Jobs can back up because an external API is slow or because you don't have enough workers. The age of the oldest job and the job duration, read together, tell you which.
Stop Polling: LISTEN and NOTIFY
Idle workers need to find out when new work arrives. Running the claim query every 100 milliseconds from ten workers is a lot of wasted queries on an empty queue. Running it every 10 seconds adds up to 10 seconds of latency.
PostgreSQL has a built-in wake-up signal. Workers listen on a channel:
LISTEN jobs_ready;
Whatever enqueues work sends a notification in the same transaction:
BEGIN;
INSERT INTO jobs (kind, payload) VALUES ('index_blocks', '{"from": 2000000, "to": 2009999}');
NOTIFY jobs_ready;
COMMIT;
Notifications are delivered only when the transaction commits, so a worker never wakes up for a job it can't see yet. Treat the notification as a hint to run the claim query, not as the job itself. The job lives in the table. The notification is just a tap on the shoulder.
Three things to know before you rely on it:
- Notifications aren't a durable delivery mechanism. A session that isn't connected and listening when the notification is sent never gets it. A worker restarting during a deploy will miss some. Keep a slow poll, every 30 seconds or so, as a safety net.
- LISTEN needs a dedicated session. It doesn't work through PgBouncer in transaction pooling mode, because your next query may run on a different server connection. Give each worker one direct connection for listening. More on why in Connection Pooling in the Serverless Era: Five Failure Modes.
- NOTIFY serializes part of the commit. A transaction that has sent a notification takes a global lock near the end of its commit, so transactions that call
NOTIFYqueue up behind each other at that step. Commits that don't notify aren't affected by this particular bottleneck. At low rates you'll never notice. If you enqueue thousands of jobs per second, send one notification per batch, not one per row.
See the docs for LISTEN and NOTIFY for the exact delivery rules.
The Feature No Queue Service Can Give You
There's one advantage that's easy to miss: you can enqueue a job in the same transaction as the business data that caused it.
BEGIN;
INSERT INTO blocks (height, hash, ...) VALUES (...);
INSERT INTO jobs (kind, payload) VALUES ('index_events', '{"height": 2000123}');
COMMIT;
If the transaction commits, both rows become visible together. If it rolls back, neither is written. There's no window where the block exists without its job.
With Redis or SQS you'd write to the database and then publish to the queue, and if the process dies between the two, you either lose the job or queue work for data that was never committed. That's the dual-write problem. A transactional outbox solves it by recording the event in the same transaction as the business data and publishing it to another system afterwards. An outbox and a jobs table are built the same way, a row written atomically with your data, but they differ in purpose: an outbox holds events waiting to be delivered somewhere else, while a jobs table holds work your own workers execute. I covered the outbox in detail in The Dual-Write Problem.
When You Really Do Need a Queue Service
Postgres as a queue isn't free and it isn't unlimited. Move to something dedicated when:
- Queue traffic starts hurting application traffic. Every claim, heartbeat, retry and status change is a write on your primary. Each one produces WAL and dead tuples, and each one competes with your application for CPU, disk, connections and vacuum. There's no universal jobs-per-second number where Postgres stops being a good queue: a job that's claimed once and completed once costs far less than one that heartbeats every 30 seconds and retries three times. Benchmark with your real payloads, batch sizes and heartbeat interval, and watch queue latency, application query latency and replication lag together.
- You need fan-out. One event consumed independently by five different services is pub/sub. Kafka, Redis Streams or SNS-to-SQS fit that shape. A jobs table doesn't.
- You need a replayable log. If consumers have to re-read last Tuesday's events, you want a retained log like Kafka, not a table you're constantly deleting from.
- You need strict ordering.
SKIP LOCKEDhands out jobs roughly in order, but job 2 can finish before job 1. If order per key matters (all events for one account, in sequence), you have to design for it, and a partitioned log does that more naturally. - The work crosses service boundaries. If the producer and consumer don't share a database, a shared jobs table becomes a coupling you'll regret.
For an indexer backfill, background emails, webhook deliveries, report generation, or most of the jobs a typical backend runs, none of these apply.
If you do outgrow it, the System Design In-Depth modules on Message Queues, Event Streaming with Kafka and Background Jobs and Scheduling cover what to move to, and BullMQ Internals shows how the most common Redis queue does the same claim-and-lease dance with Redis data structures.
The Checklist
- Claim jobs with
FOR UPDATE SKIP LOCKED, ordered byrun_at, id. - Commit straight after claiming. Never hold a transaction open for the whole job.
- Use leases with a fresh claim token per claim, and check the token on every heartbeat and completion.
- Retry with backoff and jitter, and cap the attempts.
- Make the job's side effects idempotent. The queue is at-least-once.
- Index the claim query with a matching partial index.
- Keep finished jobs out of the hot table and tune autovacuum from observed churn.
- Watch the oldest pending job's age, expired leases and dead tuples.
- Keep a polling fallback when you use
LISTENandNOTIFY. - Benchmark before you move to a dedicated queue.
The Takeaway
A queue is a table where many readers each need to take a different row. PostgreSQL ships with the exact primitive for that. FOR UPDATE SKIP LOCKED lets parallel workers claim different jobs without waiting on each other. Leases with claim tokens take care of crashed and stalled workers. A partial index plus vacuum settings tuned to the table keep the claim fast. And transactional enqueue gives you a guarantee that an external queue can't.
Start with the database you already run. Add a queue service when you can name the limit you hit, not before.