PostgreSQL job workers: retries, crashes, tenant context

A background worker in a multi-tenant SaaS does something no web request does: it serves every tenant in turn, on the same database connection, for days. Three things go wrong there that a test with one tenant never shows. The tenant context of one job outlives it and is still set for the next. A job that fails is never counted as failed, so it is retried forever. And a worker that dies halfway through a job leaves that job either running for nobody, or back in the queue to kill the next worker.

We measured all three with a Python worker on psycopg 3 and its connection pool. With the pool’s defaults, a job that succeeded handed its tenant to the next job — 20,000 invoices of the wrong customer — while a job that failed did not. A poison job in the most common queue design was picked up 10 times out of 10 and never counted, and the job behind it never ran. And a job that outlived its lease ran twice; a fencing check kept the late first run out of the database, but not out of the world.

The setup

PostgreSQL 17.10 in a throwaway container, default settings. Python 3.12 with psycopg 3.3.6 and psycopg_pool 3.3.3. Measured on 3 October 2026.

• 100 tenants and 103,707 invoices, floor(20000 / tenant) each, so tenant 1 has 20,000, tenant 2 has 10,000 and tenant 3 has 6,666.
• Row-level security forced on invoices, with the tenant in a session variable: using (tenant_id = nullif(current_setting('app.tenant', true), '')::int). The worker logs in as an ordinary role, not the table owner.
• A jobs table with status, attempts, max_attempts (3), locked_until and last_error, and a job_runs table (job_id, worker, attempt, started, finished) in which every job writes one row when it starts, so we can count executions.

Does tenant context leak from one job to the next?

Yes — but only after a job that succeeds. A pool of one connection ran three jobs for tenants 1, 2 and 3. Each job set the tenant with set_config('app.tenant', …, false), the session-level form, and counted invoices. After each job we asked the pool for a connection, set nothing, and counted again. The middle column comes from a second run in which the job for tenant 2 fails; the other columns were the same in both runs.

The difference between the first two rows is the transaction. psycopg does not run in autocommit by default, so the job’s statements, the set_config included, sit in one transaction. When the job leaves the with pool.connection() block the pool applies the normal context behaviour — “commit/rollback the transaction in case of success/error”, in the words of the psycopg documentation. A commit keeps a session-level setting; a rollback undoes it. So the failing job for tenant 2 left nothing behind, but the context of the last job that succeeded, tenant 1, was still there. In autocommit there is no transaction to roll back, and the failing job leaked too.

That makes this leak hard to find. It does not appear when something goes wrong; it appears when everything goes right, on the next job, for a different customer.

The third row is the fix: set_config('app.tenant', …, true) — or SET LOCAL — inside the job’s transaction. The setting ends with the transaction, whatever the outcome. It is the same advice we measured behind PgBouncer in PgBouncer and RLS: tenant context, SET LOCAL, shared plans; a worker’s pool hands the same server session to one job after another, just as a pooler in transaction mode does.

What does resetting the connection cost?

If you would rather clean up after every job than trust every job to use the local form, psycopg_pool takes a reset callback. We timed 2,000 jobs per variant — one set_config and one query each, the first 200 left out — and ran the series twice. The last variant ran 300 jobs, so its median covers 100:

Two of these rows are traps.

DISCARD ALL breaks psycopg’s prepared statements. psycopg prepares a query automatically once it has run more than prepare_threshold times on a connection. DISCARD ALL deallocates every prepared statement on the server, psycopg does not know that, and the next execution fails: prepared statement "_pg3_0" does not exist, at job 7 in both runs. Switching preparation off (prepare_threshold=None) makes it work. Our callback switched the connection to autocommit for the DISCARD ALL, because that command cannot run inside a transaction block.

A reset without a commit silently costs a connection per job. The pool’s documentation says the connection “must be left in idle state, otherwise it is discarded”. Without autocommit, RESET app.tenant opens a transaction, so the pool threw the connection away after every job and opened a new one: 301 connections for 300 jobs and 8.9 ms per job instead of 0.6. It looks correct — no context is left behind, because no connection is left behind — and the only trace is a warning in the log.

The cheapest correct option was not to reset at all and set the tenant locally: 0.35 ms against 0.33 for the leaking version.

Why is a failing job never counted?

Because the counter is in the transaction that fails. The most common queue design claims, works and finishes in one transaction:

begin;
select id, tenant_id from jobs
  where status = 'queued' order by id limit 1
  for update skip locked;
-- set the tenant, do the work
update jobs set status = 'done' where id = $1;
commit;

It is attractive because the row lock is the claim: if the worker dies, the lock goes with it and the job is free again. But when the work raises an error, the rollback undoes everything in the transaction, including any attempts = attempts + 1 you put there. We queued one job that always fails (select 1/0) followed by an ordinary one, and let one worker dequeue ten times:

Without a handler the queue stopped: the job that always fails is also the oldest, so order by id hands it out again every time, and the job behind it waited. Even the row in job_runs that the poison job wrote when it started was rolled back, so nothing in the database showed that it had ever run. With an error handler that opens a new transaction to count the attempt, the job went to dead after three tries and the queue moved on.

What happens when the worker process dies mid-job?

The error handler only runs if the worker is still alive. We replaced the poison job by one that ends the Python process (os._exit(1)) in the middle of the work, and started a fresh worker after each crash — straight away in the one-transaction design, which has no lease, and 1.1 seconds later in the lease design:

In the one-transaction design PostgreSQL rolled back the dead worker’s transaction, so the job was queued again with attempts 0 and the next worker took it and died as well — every worker, every time. A job that crashes the process, rather than raising an error, is never counted in this design; a worker killed for running out of memory would skip the handler in the same way.

The second design commits the claim first, in its own transaction:

update jobs
   set status = 'running', attempts = attempts + 1,
       locked_until = clock_timestamp() + interval '1 second'
 where id = (select id from jobs
              where (status = 'queued'
                     or (status = 'running' and locked_until < clock_timestamp()))
                and attempts < max_attempts
              order by id limit 1
              for update skip locked)
returning id, tenant_id, attempts;

The attempt is counted before the work starts, so a crash cannot undo it. After a crash the job is invisible until its lease expires, then claimed again. After three crashes it was no longer eligible and the queue moved on. But it stayed running with attempts 3 and an expired lease — nothing in this query marks it dead. That needs a sweep, for example update jobs set status = 'dead' where status = 'running' and locked_until < now() and attempts >= max_attempts — a suggestion; we did not run it in this measurement.

What if a job takes longer than its lease?

Then a second worker takes it while the first is still busy. Lease 2 seconds, a job that takes 3, a second worker polling every 100 ms:

Fencing — finishing the job only if the row still carries the attempt number this worker claimed — did what it promises: the first worker’s update matched no row, it rolled back its transaction, and its job_runs row disappeared with it. But both workers did three seconds of work. Fencing keeps a late worker’s writes out of the database; an e-mail it sent or an API it called has happened twice. Only the heartbeat, a separate connection extending locked_until every half second while the job runs (itself only for the attempt it claimed), prevented the second execution. A lease is a guess at the longest job; a heartbeat replaces that guess, but not the fence. A worker that hangs but stays alive keeps sending heartbeats and its job is never taken over, so a heartbeat needs a maximum runtime next to it, and a heartbeat that arrives late still leaves the fence as the last line.

What does a job that holds its transaction cost?

A slow job can hold a transaction open for as long as it runs, and an open transaction can stop VACUUM from removing row versions that became dead after it started. We let one 20-second job run in five ways while a second worker processed 5,000 short jobs, then ran VACUUM (VERBOSE) jobs:

What held VACUUM back was not the lease or its absence but a long transaction that had written something — and SELECT … FOR UPDATE counts, because locking a row writes to it. A transaction that only read, under the default READ COMMITTED and idle between statements, held nothing back here. So the lease design only avoids this if the long part of the work runs outside a transaction, or in short ones.

Twenty seconds and 5,000 jobs is small, and in throughput it was not visible here (6.6 s against 5.5–5.6 s). So we let it run longer: 100,000 queued jobs, one worker taking them one by one with the shortest possible dequeue (delete … where id = (select … for update skip locked) returning id), and the median dequeue time per block of 10,000 jobs while another session held a transaction open. The last three rows come from runs of 60,000 jobs:

With the horizon held, each block of 10,000 was slower than the last, in a straight line: after 100,000 jobs a dequeue took 8.3 ms instead of 0.7, for a queue that was by then almost empty. EXPLAIN (ANALYZE, BUFFERS) on the dequeue after 50,000 jobs shows why: 654 buffers and 4.1 ms with the transaction open, 42 buffers and 0.05 ms without. The index scan still has to walk past the entries of every job already taken, because while an older transaction is still open, PostgreSQL may not treat those rows as dead. The design with status = 'done' instead of delete and a partial index on queued jobs gave the same curve, ending at 8.34 ms. A REPEATABLE READ transaction that only reads — the shape of a long report or a pg_dump — did the same as one with a transaction ID, because it keeps its snapshot between statements. After the transaction ended, the next block’s median was back at 0.69 ms.

We measured only the jobs table; VACUUM’s horizon applies to the whole database, so a job that writes and then works for an hour would hold back the cleanup of every table that changed in that hour. PostgreSQL’s idle_in_transaction_session_timeout can end a session that sits idle in a transaction, but it is off by default. PlanetScale measured the same mechanism at a larger scale, with analytics queries as the long transactions, in keeping a Postgres queue healthy.

Should the jobs table have row-level security?

Not with the same policy as the tenant’s data. With the tenant policy on jobs too, a worker without a tenant context saw 0 of 100 jobs and the claim query returned nothing — no error, an apparently empty queue. A worker that serves every tenant has to see every job before it knows which tenant to become. Keep the queue table out of the tenant policy, or give the worker a role for the claim that bypasses it, and run the work under the tenant with set_config(…, true).

What we would build

For jobs of a few hundred milliseconds that only write to the database, one transaction with an error handler is simpler and good enough — with two limits we measured: a failed job goes straight back to the front of the queue, so retries come back to back, and a job that kills the process still kills every worker. For anything that calls the outside world, takes long, or might kill the process, claim with a lease, count at the claim, send a heartbeat and fence the finish. In both designs the tenant is set locally, in the transaction, every time.

What we did not measure

A worker host that vanishes from the network instead of exiting: PostgreSQL then only notices when TCP keepalives give up, and tcp_keepalives_idle defaults to the operating system’s value; until then a one-transaction job stays locked. Backoff between retries, more than two workers, other drivers and poolers, and fairness between tenants — that is in job queues per tenant in PostgreSQL: the noisy neighbour. The timings come from one machine and are for comparison, not for capacity planning.

These are the parts of a multi-tenant system that are cheap to decide early and expensive to change later; the rest of that list is in multi-tenant SaaS on PostgreSQL: six decisions.

Sources: the psycopg 3 documentation on the connection pool (the reset callback and the context behaviour of connection()) and on prepared statements (prepare_threshold), and the PostgreSQL 17 documentation on connection settings (tcp_keepalives_idle) and client connection defaults (idle_in_transaction_session_timeout), all read on 3 October 2026, and Keeping a Postgres queue healthy by Simeon Griggs (PlanetScale, 10 April 2026), read on 4 October 2026. All measurements are our own, on PostgreSQL 17.10, on 3 and 4 October 2026.