One Open Transaction Is Bloating Your Whole Database
Postgres and MySQL can't clean up past the oldest open snapshot. Here's how to find the transaction holding it, and which idle sessions are actually guilty.

You run VACUUM (VERBOSE) on a busy table and it comes back with a line like this:
tuples: 0 removed, 20001 remain, 20000 are dead but not yet removable
Vacuum ran. It found the dead rows. It wasn't allowed to remove any of them.
That output is from a scratch Postgres 16 table with one live row, updated 20,000 times while a different session sat idle in a REPEATABLE READ transaction. The table grew from 1 page to 89 pages (712 kB) for a single row. The idle session never touched it.
If you're seeing this on a real database, the table isn't your problem. Something is holding the xmin horizon, and until it lets go, no amount of autovacuum tuning will help. This post covers how that works on Postgres and MySQL, which open transactions are actually guilty (fewer than you'd think on Postgres, more than you'd think on MySQL), and the queries to find them.
TL;DR
- MVCC databases keep old row versions until no open snapshot could need them. The oldest open snapshot is the floor.
- Cleanup (VACUUM in Postgres, purge in InnoDB) can't go below the floor, on any table in the database.
- The cost of a stuck floor is time open × write rate elsewhere. The holder itself does nothing, so slow-query monitoring never sees it.
- On Postgres, an idle READ COMMITTED session that only read is not holding the floor. One that wrote, or runs REPEATABLE READ, is.
- On MySQL (REPEATABLE READ by default), a single SELECT in an open transaction is enough.
- Monitor transaction age, not query duration.
How MVCC creates the floor
When you update a row, Postgres and InnoDB don't overwrite it. They write a new version and keep the old one so that readers with older snapshots still see consistent data. Postgres stores old versions as dead tuples in the table; InnoDB stores them in undo logs.
A version can be removed only when every open snapshot is newer than it. One mental model makes this stick: every change adds a page to a notebook, every reader holds a bookmark, and the cleaner can only tear out pages that come before every bookmark.

Three consequences:
- Database-wide, not per table. The holder can be reading
userswhilejobsbloats. - Damage = time open × write rate. Ten idle minutes on a hot queue table is far worse than ten hours on a quiet database.
- Small, hot tables suffer first. Queues, sessions, counters and rate-limit tables rewrite the same rows constantly.
Which open transactions actually hold the floor
This is where most advice ("kill everything idle in transaction") is too blunt. Same test as above, four variations, Postgres 16:
| Second session | backend_xid |
backend_xmin |
Table after 20,000 updates | Dead, not removable |
|---|---|---|---|---|
| None | 1 page | 0 | ||
idle in transaction after one SELECT, READ COMMITTED |
null | null | 1 page | 0 |
idle in transaction after one SELECT, REPEATABLE READ |
null | set | 89 pages | 20,000 |
idle in transaction after one INSERT, READ COMMITTED |
set | null | 89 pages | 20,000 |
Why:
- READ COMMITTED takes a snapshot per statement and releases it when the statement finishes. Between statements, a read-only transaction holds no snapshot.
- REPEATABLE READ keeps one snapshot for the whole transaction.
- A transaction that wrote has its own transaction ID still in progress, and every snapshot must treat it as such.
MySQL flips the default case. InnoDB runs REPEATABLE READ by default, the read view is created at the first consistent read, and it lives until commit. The MySQL manual states that long-running transactions grow the history list even when they're read-only, and lists mysqldump --single-transaction and a SELECT after disabling autocommit as examples.
On Postgres,
idle in transactionmeans suspicious.backend_xidorbackend_xminbeing set means guilty.
One caveat: a read-only Postgres transaction that isn't blocking vacuum still holds an AccessShareLock on what it read, which will block an ALTER TABLE. Different outage, same leaked transaction.
Reproduce it yourself:
-- Session 1
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM pg_class; -- leave this open
-- Session 2
CREATE TABLE q (id int PRIMARY KEY, n int);
INSERT INTO q VALUES (1, 0);
DO $$ BEGIN
FOR i IN 1..20000 LOOP UPDATE q SET n = n + 1 WHERE id = 1; END LOOP;
END $$;
VACUUM (VERBOSE) q;
SELECT pg_size_pretty(pg_relation_size('q')); -- 712 kB
-- Session 1: ROLLBACK; then VACUUM q again in session 2
After the rollback, vacuum removes all 20,000 versions, but the file stays at 712 kB. Plain VACUUM makes space reusable; it rarely returns it to the OS. Ending the holder stops the growth. It doesn't undo it.
Why monitoring misses it

The holder shows up as idle in transaction in Postgres or Sleep in MySQL. It has no running query, no query duration and no lock waits. Meanwhile the slow-query log fills with innocent queries stepping over dead versions. Percona documented the MySQL version of this: SELECTs got steadily slower as the InnoDB history list grew, and the process list didn't show the hung transaction's queries.
Six common holders in application code
1. Network calls inside a transaction
DB::transaction(function () use ($order, $payload) {
$order->update(['status' => 'charging']); // wrote: pins the floor
$charge = Http::timeout(30)->post($this->paymentUrl, $payload);
$order->update(['status' => 'paid', 'charge_id' => $charge['id']]);
});
During a provider slowdown, every in-flight request holds the floor. It also holds a pool connection, which is how this turns into connection pool exhaustion.
2. Implicit transactions from your driver
PEP 249 requires Python DB-API drivers to start with auto-commit off. psycopg2, PyMySQL and others open a transaction on the first query and keep it until commit()/rollback().
conn = psycopg2.connect(DSN)
cur = conn.cursor()
cur.execute("SELECT value FROM settings WHERE key = 'batch_size'")
job = queue.get() # blocks for minutes: transaction is still open
Harmless to vacuum on Postgres READ COMMITTED (it still holds locks). Holds purge on MySQL.
3. Manual-commit SQL consoles
A developer checks a row in a GUI client with auto-commit off and goes to lunch. MySQL's default wait_timeout is 28,800 seconds.
4. Dumps and long reports
pg_dump and mysqldump --single-transaction read from one snapshot for their entire run. So does a long analytics query.
5. hot_standby_feedback
Off by default. When on, replica queries hold the floor on the primary in exchange for fewer "canceling statement due to conflict with recovery" errors. If you route reads to replicas, check this setting.
6. Survivors of restarts
Abandoned replication slots and orphaned prepared transactions persist across restarts. If bloat returns right after a reboot, check these first.
Find the holder
Postgres, sessions ranked by snapshot age:
SELECT pid, usename, application_name, state,
now() - xact_start AS xact_age,
backend_xid, backend_xmin,
left(query, 60) AS last_query
FROM pg_stat_activity
WHERE (backend_xid IS NOT NULL OR backend_xmin IS NOT NULL)
AND pid <> pg_backend_pid()
ORDER BY greatest(age(backend_xid), age(backend_xmin)) DESC NULLS LAST
LIMIT 5;
Postgres, holders that aren't sessions:
SELECT slot_name, active, xmin, catalog_xmin FROM pg_replication_slots;
SELECT gid, prepared, owner FROM pg_prepared_xacts;
Run the session query on replicas with feedback enabled too.
MySQL:
SELECT t.trx_mysql_thread_id AS thread_id, t.trx_started,
TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) AS trx_age_s,
p.user, p.host, p.command, p.time AS idle_s
FROM information_schema.innodb_trx t
JOIN information_schema.processlist p ON p.id = t.trx_mysql_thread_id
ORDER BY t.trx_started
LIMIT 5;
SHOW ENGINE INNODB STATUS; -- TRANSACTIONS section: "History list length"
command = Sleep plus an old trx_started is your holder. In Postgres, the query column shows the last statement the session ran, usually the line just before the slow work.
Cap it and fix the code

Per-role timeouts on Postgres (not server-wide, or you'll kill backups and migrations):
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE app_user SET transaction_timeout = '5min'; -- PostgreSQL 17+
Both terminate the session, so confirm your pool reconnects cleanly. Derive the values from your own slowest legitimate transactions.
MySQL has no built-in idle-transaction timeout. MariaDB has idle_transaction_timeout (10.3+) and Percona Server has kill_idle_transaction. On stock MySQL, run a watchdog that ends connections whose transaction age passes your limit.
Move slow work out of the transaction:
$order->update(['status' => 'charging']); // commits immediately
$charge = Http::timeout(30) // no transaction open
->withHeaders(['Idempotency-Key' => $order->charge_key])
->post($this->paymentUrl, $payload);
$order->update(['status' => 'paid', 'charge_id' => $charge['id']]);
A crash between steps is now possible, which is what the idempotency key is for. If you need the status change and an outgoing message to stay consistent, read why there's no safe order for two writes.
Python readers and workers: conn.autocommit = True, or commit before blocking.
Alert on age: oldest transaction age past your limit; on MySQL, a history list length that keeps climbing. A count of idle sessions is the wrong signal: one old transaction matters more than twenty short ones.
Test it: on staging, open a console, start a transaction, run one SELECT (REPEATABLE READ on Postgres), and walk away for twenty minutes. If nothing pages you and no timeout fires, production has the same gap.
Key Takeaways
- The oldest open snapshot is the cleanup floor for the whole database. One idle session can bloat tables it never reads.
- The damage scales with how long it stays open and how hard everything else writes.
- On Postgres, check
backend_xidandbackend_xmin, not juststate. On MySQL, any open transaction that has read something holds purge. - Common holders: network calls inside transactions, implicit driver transactions, manual-commit consoles, dumps, replica feedback, abandoned slots and prepared transactions.
- Use per-role timeouts, keep slow work outside transactions, and alert on transaction age.
- Ending the holder stops the bloat from growing. It doesn't shrink what already grew.
FAQ
What does "idle in transaction" mean in PostgreSQL?
The session has an open transaction but isn't running a statement. It ran BEGIN (or its driver did implicitly), executed something, and hasn't committed or rolled back. It may or may not be holding the xmin horizon: check backend_xid and backend_xmin in pg_stat_activity.
Why is VACUUM not removing dead tuples?
Because they're newer than the oldest snapshot something still holds. VACUUM (VERBOSE) reports them as "dead but not yet removable." Look for long or idle transactions, replica queries with hot_standby_feedback on, replication slots, and prepared transactions. Tuning autovacuum won't help until the holder is gone.
Does a read-only transaction block vacuum or purge?
On Postgres under READ COMMITTED, an idle read-only transaction usually doesn't hold the horizon between statements. Under REPEATABLE READ, or with an open cursor, it does. On MySQL InnoDB, a read-only transaction under the default REPEATABLE READ holds its read view from the first SELECT until commit, which blocks purge.
What is the InnoDB history list length and why does it grow?
It's the number of undo log records for committed transactions that purge hasn't removed yet. It grows when purge can't keep up with writes or, most often, when a long-running transaction still needs old row versions. As it grows, reads walk longer undo chains and get slower. You'll find it in SHOW ENGINE INNODB STATUS.
What should idle_in_transaction_session_timeout be set to?
There's no universal number. Set it per application role, a little above your slowest legitimate pause inside a transaction (often well under a minute for web requests), and give backup, migration and reporting roles separate settings. It terminates the whole session, so make sure your connection pool handles that.
Will VACUUM shrink the table after I end the long transaction?
Usually not much. Plain VACUUM removes the dead versions and makes the space reusable for new rows, but it only returns space to the operating system in limited cases. To actually shrink a badly bloated table you need a rewrite, such as VACUUM FULL (which takes an exclusive lock) or an online tool like pg_repack.
Related reading
- Your Database Is Fine. Your App Is Dying Anyway. (connection pool exhaustion)
- Your Worker Read the Past. Then It Saved It. (replica lag)
- Your Database Transaction Didn't Save You From This Bug (lost updates under MVCC)
- Why Your API Charges People Twice (idempotency keys)
- Start Here: A Plain-English Map
The bottom line
The transaction slowing your database down usually isn't doing anything. It's just old. Stop ranking queries by how long they run and start ranking transactions by how long they've been open.
Stop hunting for the slowest query. Find the oldest transaction.
Adam Jaber is a software engineer who writes Simply Explained: complex topics, made simple. No jargon, no hype. Get one of these in your inbox each week: Simply Explained on Substack.
About the author
- I'm a senior software engineer. I write Simply Explained, a plain-English breakdown of how AI and software actually work, and the quiet ways they break: AI fixes that hide bugs, agents that pick the wrong tool, health checks that take down healthy servers. One symptom, one analogy, one fix per piece. New here? Start with the pinned Start Here guide, or get one breakdown a week by email.
Comments
Join the discussionNo comments yet. Be the first to comment.




