📺 Prefer to watch? 90-second YouTube Short · 💬 Telegram
Originally published on software-engineer-blog.com.
Here is a real row from a real database.
pid | application_name | state | duration
-----+------------------+---------------------+-----------
2841 | worker-07 | idle in transaction | 00:40:12
One worker. One open connection. State: idle in transaction. Forty minutes and twelve seconds.
The job it was running finished long ago. Nothing in the code has crashed. There is no error in any log file. But two things are going wrong at the same time:
- New connections are starting to be refused.
- The cleanup job inside the database is running, and freeing nothing. Not in one table. In every table of that database.
This post is about how an ordinary background job ends up here, and how to write one that never can.
Two sentences carry the whole thing:
- A job is a row in a table, not a function call.
- A connection is not a transaction.
If you only remember those two, you will already write better workers than most teams.
Every number and every error string below was measured against a real PostgreSQL 16.14 and SQLAlchemy 2.0.52. One of them contradicts what almost everybody says. I will show you that one.
First, the picture that kills the confusion
Think of a bank with a long counter.
On the left, a person is sitting at it. The bank has a fixed number of seats. Taking one costs exactly one seat. It costs one seat whether you are writing on a form, waiting for a phone call, or finished ten minutes ago and simply stayed. When every seat is taken, the next person who walks in is turned away.
Now look at the right. Same counter, same seat. But this time a large ledger book is lying wide open. An open page means: not signed off yet. And while any page is open, the clerk behind cannot file old paper away, because your open page might still refer to it. One open page holds up the filing for the whole bank.
- The seat is a connection: one open line between your program and the database.
- The open page is a transaction: a group of changes the database treats as one unit.
Your worker takes both. Only one of them is cheap.
The system: four boxes
Four boxes, and nothing else appears for the rest of this post.
- The browser — one person, one tab. They upload a large file, and later they ask: is it ready yet?
- Your API server — one uvicorn process. It must answer in milliseconds, so it must not do slow work.
-
Postgres — one server, with one table called
jobs, one row for each piece of work. - The worker — a separate Python process. Thirty of them, doing the slow work.
Now the lines. The browser talks to the API server and gets an answer back. The API server writes the job row into Postgres and reads it back later. The worker claims a row and updates it.
Now look at what is missing. There is no line between the browser and the worker. They never speak. Everything they share, they share through one row.
Where almost every codebase starts
It is only one line.
@router.post("/uploads")
def create_upload(file: UploadFile, bg: BackgroundTasks,
db: Session = Depends(get_db)):
row = Upload(name=file.filename, status="converting")
db.add(row)
db.commit()
bg.add_task(convert, row.id) # one line. the request returns now.
return {"id": row.id, "status": "converting"}
A file comes in. We insert a row that says converting, and we commit it. Then the line everybody writes: bg.add_task(convert, row.id). And we return straight away.
The user gets an answer in about forty milliseconds. The conversion carries on afterwards. It really does look like it solved the problem.
Now look again — not at what the code does, but at where it runs.
A BackgroundTask has no worker. The slow work is running inside the API server, the one box whose whole job is to answer fast. There is no arrow to the worker, because there is no worker.
And now three ordinary things end the story badly:
- The process runs out of memory, and Linux kills it.
- You deploy, and the old process is replaced in the middle of a file.
- The machine simply reboots.
In all three cases the work is gone. What is left behind is a row that still says converting, forever, with nothing anywhere knowing that a job was lost.
The work lived inside a process whose whole purpose is to be replaceable.
A job is a row
Here is the sentence everything else follows from. A job is a row. Not a function call.
A function call lives inside one process. When that process ends, the call is gone, and there is nothing left to look at.
A row lives in the database:
- It outlives the request that created it.
- It outlives the worker, because if that worker dies, another one can pick the same row up again.
- It outlives your deploy, because a deploy replaces processes. It does not touch rows.
So the rule is simple. Write the work down first. Only then do it.
The table is the whole queue
CREATE TABLE jobs (
id bigserial PRIMARY KEY,
kind text NOT NULL,
payload jsonb NOT NULL,
status text NOT NULL DEFAULT 'queued',
attempts int NOT NULL DEFAULT 0,
run_after timestamptz NOT NULL DEFAULT now(),
locked_by text,
heartbeat timestamptz
);
CREATE INDEX jobs_ready ON jobs (run_after)
WHERE status = 'queued';
Read the columns:
-
kindsays what work this is. -
payloadis JSON, and carries the arguments. -
statusstarts atqueued. -
attemptslets you stop a job that keeps failing. -
run_afteris a timestamp, so a job can be scheduled for later — and so a retry can wait before trying again. -
locked_bysays which worker owns this row right now. -
heartbeatsays when that worker last proved it was still alive.
Then one index. Notice the word WHERE at the end. That is a partial index: it only covers the rows that are still queued. That matters, because a jobs table fills up with finished rows very quickly, and you do not want the lookup to get slower every day.
The one thing a broker structurally cannot do
A broker is a separate server whose only job is to hold a list of work — RabbitMQ, or Celery with Redis behind it. We are not using one. Look at what that buys you.
def create_upload(db: Session, name: str) -> int:
with db.begin(): # ONE transaction opens here
row = Upload(name=name, status="converting")
db.add(row)
db.flush() # the database gives us row.id
db.add(Job(kind="convert",
payload={"upload_id": row.id}))
# the commit happened on that line. both rows, or neither.
return row.id
One transaction. We insert the upload row, we flush so the database gives us the id, then we insert the job row using that id. The commit happens when the block ends. Both rows, or neither.
Now think about what happens with a broker instead. You have two systems:
- The database can commit while the message to the broker is lost. Now you have an upload nobody will ever convert.
- Or the message can be sent while the database rolls back. Now a worker picks up a job for a row that does not exist.
Both of those are real bugs that real teams spend real weeks on. Put the job in the same table and the same transaction, and neither one can happen. It is not that they are unlikely. They are impossible.
Thirty workers, one table
How do thirty workers not collide? With one keyword.
-- app/worker/claim.py — handed to SQLAlchemy text()
UPDATE jobs SET status = 'running', locked_by = :me,
heartbeat = now()
WHERE id = (
SELECT id FROM jobs
WHERE status = 'queued' AND run_after <= now()
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 1
)
RETURNING id, kind, payload;
Read the sub-query from the bottom up:
-
LIMIT 1— take a single row. -
FOR UPDATE— lock that row, so no other worker can take it. -
SKIP LOCKED— and this is the important part. Without it, a worker that finds a locked row waits for it. With it, the worker steps over that row and takes the next free one.
I ran this with two workers starting at the same moment on a real Postgres:
| Setup | Worker A took | Worker B took | Worker B waited |
|---|---|---|---|
FOR UPDATE SKIP LOCKED |
ids 1, 2 | ids 3, 4 | 1.12 ms |
FOR UPDATE only |
ids 1, 2 | blocked until A committed | 3.035 s |
With SKIP LOCKED, worker B stepped straight over A's two rows and was working in about one millisecond.
Without it, worker B blocked for 3.035 seconds — exactly how long worker A held its transaction. While it waited, pg_stat_activity showed it as wait_event_type = 'Lock', wait_event = 'transactionid'. It was not doing anything useful. It was queueing behind a row it was never going to get, because once A committed status = 'running', READ COMMITTED re-checked the WHERE clause and those rows no longer matched.
In production that hold is not three seconds. It is the length of the job.
So do you even need a broker?
People argue about this with the wrong measurement. They ask how long the job runs. That is not the axis.
The real question is: who owns the status of this job?
| Put it in a table when… | Reach for a broker when… |
|---|---|
| A user can ask "is my file ready yet?" | Nobody ever asks about one individual job |
| You need to show progress, or the reason something failed | You are sending one event out to many consumers |
| The job must survive the queue itself restarting | The volume is high and each message is tiny |
| Volume is thousands a day, not millions a minute | Fan-out and routing are the actual product |
In the left column, the status is domain data. It belongs in a table you can query, join and index.
Both are correct answers — to different questions. And when the table genuinely runs out of room, the next step is usually not a broker. It is PgBouncer in transaction mode.
The trap: a connection is not a transaction
Now the reason this post exists.
A connection is the seat at the counter. Your worker holds one so it can talk to the database at all. A transaction is the page lying open on that counter, not signed off yet.
Here is the problem. Your worker takes both, at the same moment, without anybody asking it to. One of them costs a seat. The other one costs the whole building.
Take them one at a time. Expensive one first.
Child one: the invisible one
Here is what people usually say:
A connection that is
idle in transactionblocks VACUUM.
I measured it. That sentence is wrong.
VACUUM is the cleanup job inside Postgres. When you update a row, the old version is left behind as a dead tuple, and VACUUM is what reclaims that space. It can only reclaim a dead version that no open transaction could still need to see. The oldest thing any open transaction might still need is called the xmin horizon.
So the question is not "is a transaction open?" It is "is this transaction holding the horizon back?"
I built a table with 200,000 dead tuples, opened one blocking session in nine different states, and ran VACUUM (VERBOSE) on a table that blocker had never touched. Here is what actually happened:
| The blocking session | Holds a snapshot / XID? | Dead tuples reclaimed? |
|---|---|---|
| Control — no long transaction open | no | ✅ reclaimed |
READ COMMITTED, bare BEGIN;, no statement |
no | ✅ reclaimed |
READ COMMITTED, SELECT only, then idle |
no | ✅ reclaimed |
READ COMMITTED, UPDATE, then idle (your worker)
|
XID | ❌ nothing freed |
| READ COMMITTED, statement still in flight | snapshot | ❌ nothing freed |
| READ COMMITTED, open cursor, then idle | snapshot | ❌ nothing freed |
REPEATABLE READ, SELECT only, then idle |
snapshot | ❌ nothing freed |
SERIALIZABLE, SELECT only, then idle |
snapshot | ❌ nothing freed |
| REPEATABLE READ, but in a different database | snapshot | ✅ reclaimed |
Read row three again. A READ COMMITTED session that only read something and then sat idle for twenty seconds had all 200,000 dead tuples reclaimed normally. The folklore version of the rule is simply not true.
Two things actually pin the horizon:
- Holding a snapshot — that means REPEATABLE READ, SERIALIZABLE, an open cursor, or being in the middle of a statement.
- Holding an XID — a transaction id, which Postgres hands out the moment a transaction writes anything.
Now look at what your worker did to claim its job. It ran UPDATE jobs SET status = 'running'. That is a write. So it holds a transaction id for the entire length of the conversion.
-- the worker: it claimed a job, and the claim was a WRITE.
BEGIN;
UPDATE jobs SET status = 'running' WHERE id = 1;
-- ... and now it converts a file for forty minutes ...
-- meanwhile, on a table this worker has never touched:
VACUUM (VERBOSE) events;
-- tuples: 0 removed, 400000 remain, 200000 are dead
-- but not yet removable
-- removable cutoff: 783 <-- the transaction id of that worker
That output is copied from the real run. removable cutoff: 783 was exactly that worker's backend_xid. Nothing was freed — on a table it had never opened.
Two limits are worth being precise about:
- It is database-wide, not cluster-wide. The last row of the table shows it: the same blocker sitting in a different database did not hold this one back.
- The damage stops the moment the transaction commits. The horizon is released immediately; it is not a permanent debt.
Child two: connections multiply
This one is cheaper on its own. It multiplies.
Count the connections one busy worker holds:
- one for the loop that claims jobs,
- one for the heartbeat that says it is alive,
- one inside the handler that is doing the work.
Three. Thirty workers, doing nothing clever, on a completely ordinary day: ninety.
And how many can you have? A stock Postgres sets max_connections = 100 and keeps superuser_reserved_connections = 3 back for the administrator. So an ordinary application role gets 97.
Your API server has a connection pool of its own on top of that. A pool is a small set of connections opened once and reused. SQLAlchemy's defaults are pool_size=5 and max_overflow=10 — that is 15 per process, so two API processes can want thirty.
Ninety plus thirty is one hundred and twenty. You have ninety-seven. Somebody is refused.
Nothing in that arithmetic is a bug or a leak. That is the default configuration, running exactly as designed.
And here is a detail almost nobody expects. The error your application sees is not the famous one:
FATAL: remaining connection slots are reserved for roles
with the SUPERUSER attribute
sorry, too many clients already is what a superuser sees. An ordinary application role hits the message above, three connections earlier.
The box that hears it first is the API server — because the workers already took their seats and are sitting on them, while the API server is still asking for new ones. The workers broke nothing. They just arrived first.
Two problems, one query
And the obvious version of that query is wrong.
Most people look for sessions where backend_xmin IS NOT NULL. That misses your worker completely, because a READ COMMITTED transaction that wrote something sets a backend_xid and leaves backend_xmin empty.
Combine the two columns and you catch every case:
SELECT pid, application_name, state,
age(COALESCE(backend_xmin, backend_xid)) AS holding_back_by,
now() - xact_start AS open_for
FROM pg_stat_activity
WHERE datname = current_database()
AND COALESCE(backend_xmin, backend_xid) IS NOT NULL
ORDER BY holding_back_by DESC;
Read the results like this:
- A session that only read, then went idle: no transaction id, nothing held back. Harmless.
- A session that wrote a row and then went idle — exactly your worker: it has a transaction id, and it pins the cleanup.
- A session using REPEATABLE READ, or holding an open cursor: it holds a snapshot, so it pins the cleanup too.
One query, and it tells you which of the two problems you actually have.
The fix is a shape, not a setting
def run_one() -> bool:
with session() as s: # session 1 — the claim
job = claim(s) # short. milliseconds.
if job is None:
return False
job_id = job.id # snapshot the fields we need
payload = dict(job.payload) # BEFORE the session goes away
# ---- nothing held here: no session, no connection, no transaction ----
result = convert(payload) # minutes. hours. it does not matter.
with session() as s: # session 2 — record the outcome
s.execute(FINISH, {"id": job_id, "result": result})
return True
Session one: claim the job. This part is short — a few milliseconds — and it commits and closes immediately.
Then, the important line: nothing is held here. No session. No connection. No transaction. The conversion can take five minutes or five hours, and the database does not care, because the database does not know it is happening.
Session two: write the result, and close.
That is the entire pattern. Claim fast, close, work holding nothing, then record.
And copying job.id and job.payload out before the block ends is not tidiness. SQLAlchemy's Session runs with expire_on_commit=True by default, which marks every attribute on that object as out of date when the transaction commits. Read job.payload after the block and you get a DetachedInstanceError — there is no session left to load it from. (Note the precise cause: it is expire_on_commit, not close() on its own. A session closed without committing leaves already-loaded attributes readable.)
One setting left
engine = create_engine(
DATABASE_URL,
pool_size=5, # the default. PER PROCESS.
max_overflow=10, # the default. PER PROCESS.
pool_pre_ping=True, # NOT the default. turn it on.
pool_recycle=1800, # and retire a connection after 30 minutes
)
pool_size=5 and max_overflow=10 are the defaults, and both are per process. pool_recycle=1800 retires a connection after thirty minutes. And pool_pre_ping is off by default. Turn it on.
Here is why it is correctness, not tidiness.
A pooled connection can be closed underneath you while it sits idle — by a network device, by a database restart, by a timeout. Without pre-ping, the next time your code uses it you get a sqlalchemy.exc.OperationalError (SQLAlchemy error code e3q8, wrapping a psycopg2.OperationalError). It is not an InterfaceError, which is what most people write their except clause against.
Now think about when that happens. Your worker has just finished a forty-minute conversion and is about to write down that it succeeded. The write fails. The job is recorded as failed, and the sweeper runs the whole thing again.
With pool_pre_ping=True, the pool quietly tests the connection on checkout, throws it away, and opens a fresh one. I measured it: the backend process id simply changes, and your code never notices.
So, all of it
Because a job is a row, it survives the request, the worker and the deploy. Many workers share it with FOR UPDATE SKIP LOCKED. It commits together with the business row, so it can never be lost or invented. And a user can simply ask the table whether it is ready. You get a queue, and you did not add a service.
Because a connection is not a transaction, a worker that wrote something and then went idle stops the cleanup everywhere in that database. Connections are cheap one by one and expensive together. Use COALESCE(backend_xmin, backend_xid) to find them.
So: claim, close, do the work holding nothing, then record.
Two settings are worth turning on today:
-
pool_pre_ping=Trueon the engine. -
idle_in_transaction_session_timeouton Postgres, which kills a session that has been sitting on an open transaction for too long.
And if that joined a few things up — go and look at pg_stat_activity on your own database right now.
The full walk-through, with every measurement on screen, is the 15-minute episode.
Top comments (0)