In PostgreSQL, what does an UPDATE do to a row under MVCC, and why does a long-running transaction make that expensive?

An update is an insert plus a tombstone

PostgreSQL never modifies a row in place. An UPDATE writes a complete new version of the tuple, stamps it with the current transaction id in xmin, and stamps the previous version's xmax with the same id to mark the point at which it stopped being current. A DELETE does only the second half. So both statements grow the table, and the space belonging to the superseded version is not free — it is occupied by a row that any transaction with an older snapshot is still entitled to read.

This is what makes readers never block writers, and it is the reason the design is worth the cost. The cost has three parts, and being able to enumerate them is most of the answer.

First, write amplification. A new tuple version is a full copy of the row, even if you changed one boolean. Second, index maintenance: because indexes point at physical tuple locations, a new version normally needs a new entry in every index on the table. The exception is a heap-only tuple update, which applies when no indexed column changed and the new version fits on the same page; then the old version points to the new one and the indexes are left alone. That exception is why "which columns does this statement touch" is a performance question and not just a correctness one, and why an unnecessary index on a hot, frequently-updated column is more expensive than it looks. Third, the old versions have to be found and reclaimed later, by vacuum.

Vacuum's actual constraint

Autovacuum wakes up, notices a table has accumulated dead tuples past autovacuum_vacuum_threshold plus autovacuum_vacuum_scale_factor of the live row count — fifty rows plus twenty percent, by default — and vacuums it. It marks the space inside each page as reusable for future inserts, which is why a healthy table reaches a steady size rather than growing for ever. It does not usually return space to the operating system; only VACUUM FULL, which rewrites the table under an ACCESS EXCLUSIVE lock, or an online rewrite tool, will shrink the file.

The critical part is what vacuum is not allowed to do. It can only remove a dead tuple once no snapshot in the system could still see it. That boundary is a single transaction id, the oldest snapshot anyone holds, and every table in the cluster is judged against it. If one session opened a transaction two hours ago, then every row version superseded in the last two hours must be retained, everywhere — not only in the tables that session touched.

This is the mechanism candidates miss, and the failure mode it produces is genuinely confusing in production: autovacuum is running, it is running frequently, it is consuming IO, and n_dead_tup climbs anyway. Nothing is misconfigured. Vacuum is doing exactly as much work as it is permitted to do, which is none, and the table is growing because inserts and updates cannot reuse space that has not been released. The result is bloat, and bloat is not merely wasted disk: the same number of live rows now spans more pages, so sequential scans read more, the buffer cache holds proportionally less useful data, and index scans lose the benefit of the visibility map, which is what allows an index-only scan to skip the heap at all.

A long-running transaction does not have to be doing anything. A session that ran one SELECT and then sat in idle in transaction while an application waited on an HTTP call holds a snapshot just as firmly as one running a six-hour report. So do two things that are not sessions at all: a replication slot whose consumer has stopped reading, and a standby with hot_standby_feedback enabled that is itself running a long query. Both pin the primary's horizon, and both are commonly overlooked because nobody is looking at pg_stat_activity for them.

Finding it

-- Oldest transactions first. backend_xmin is what is pinning vacuum;
-- a state of 'idle in transaction' is the one people are surprised by.
SELECT pid, state, backend_xmin,
       now() - xact_start AS xact_age,
       left(query, 60) AS query
FROM   pg_stat_activity
WHERE  xact_start IS NOT NULL
ORDER  BY xact_start;

Cross-check the effect on the tables themselves, and check the slots, because a forgotten slot from a cancelled logical replication setup will hold the horizon indefinitely with no session to blame:

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM   pg_stat_user_tables
ORDER  BY n_dead_tup DESC
LIMIT  10;

SELECT slot_name, active, xmin, catalog_xmin FROM pg_replication_slots;

idle_in_transaction_session_timeout is the blunt preventive control, and setting it to something on the order of a minute for application roles is normal practice. statement_timeout does not help here, because the session's problem is the open transaction rather than any individual statement.

Why this ends at wraparound

Transaction ids are 32-bit, and visibility is determined by comparing them modulo that space, so only about two billion ids ahead of the current one are usable. Rows older than that would appear to be in the future and become invisible, which would be silent data loss. Vacuum prevents it by freezing: marking sufficiently old tuples as unconditionally visible so their original xmin no longer matters. Freezing is therefore not optional maintenance, it is the mechanism that keeps the id space recyclable, and autovacuum_freeze_max_age, 200 million by default, is the point at which an anti-wraparound autovacuum is launched on a table whether or not its dead-tuple counters justify one.

Now join it up. Freezing cannot advance past the same horizon that vacuum cannot cross. A transaction left open for days on a busy cluster blocks freezing as well as cleanup, so age(datfrozenxid) climbs steadily. PostgreSQL escalates as headroom shrinks, and if it runs out it stops accepting commands that would consume a new transaction id, requiring a manual vacuum before the database will accept writes again. Since version 14 there is also a failsafe: once a table passes vacuum_failsafe_age, autovacuum abandons cost-based delays and skips index vacuuming to finish the freeze as quickly as it can. That is a symptom worth recognising, because it means you are close enough to the limit that the database has stopped optimising for anything else.

-- Headroom per database. Watch the trend, not the absolute number.
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;

Wraparound incidents are almost never caused by autovacuum being too slow. They are caused by something holding the snapshot horizon still for days while autovacuum runs faithfully and reclaims nothing, so the first question to ask is never "is autovacuum tuned" but "what is the oldest open transaction".