Every MVCC database has to answer the same question: when a row is updated, where does the old version go, and when is it safe to throw away? InnoDB and PostgreSQL give opposite answers, and those answers determine how each engine suffers when someone leaves a transaction open for six hours. If you are migrating from MySQL to PostgreSQL, this is one of the most important operational translations to get right, because the failure you already know how to spot on InnoDB looks completely different on PostgreSQL — and PostgreSQL adds a hard deadline that InnoDB simply does not have.
The blunt version: InnoDB hides old versions in undo logs and degrades gradually and mostly invisibly. PostgreSQL leaves old versions in the table itself, degrades visibly as bloat, and, in the extreme case, escalates to transaction ID wraparound protection. Both engines are fine when transactions are short. Neither forgives a forgotten open transaction, and PostgreSQL forgives it less.
Two answers to the same MVCC question
When InnoDB updates a row, it modifies the row in place in the clustered index and writes the previous version to the undo log. Readers with older snapshots reconstruct old versions by walking undo chains backward. Background purge threads delete undo records once no active read view could need them. The table stays compact; the history lives off to the side.
When PostgreSQL updates a row, it writes a complete new tuple version into the heap and marks the old one with the ID of the transaction that superseded it. Readers just pick the version visible to their snapshot; there is nothing to reconstruct. The cost is deferred: dead tuples sit in the table until VACUUM — usually autovacuum — removes them, and vacuum may only remove tuples older than the oldest transaction snapshot still alive, the xmin horizon. Old-version cleanup is a property of the table itself, which is why PostgreSQL people talk about bloat and MySQL people talk about purge lag: same problem, different address.
InnoDB: purge lag and the history list
The InnoDB failure mode is history accumulation. A long-running transaction — or a long consistent read at REPEATABLE READ, which holds its read view for the duration — prevents purge from discarding undo records that its snapshot might still need. The visible metric is History list length in the TRANSACTIONS section of SHOW ENGINE INNODB STATUS, also exposed as trx_rseg_history_len in information_schema.innodb_metrics. Left unchecked, it means undo tablespace growth and slowly degrading reads, because queries with old snapshots walk longer and longer version chains, and delete-marked records linger in indexes. Performance erodes; nothing refuses to run.
-- MySQL: watch purge lag and find old transactions
SELECT count FROM information_schema.innodb_metrics
WHERE name = 'trx_rseg_history_len';
SELECT trx_id, trx_started,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS open_seconds
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 10;Most MySQL shops learn to alert on history list length after their first bad week with it. The number that counts as "bad" is workload-dependent, so trend it and alert on sustained growth rather than a magic threshold. The usual culprits are familiar: a reporting query against the primary instead of a replica, a batch job that opens one transaction for a million-row loop, or an ORM session left open across a human-scale workflow. Those same culprits follow you to PostgreSQL — only the damage they do changes shape.
PostgreSQL: dead tuples and the xmin horizon
The PostgreSQL failure mode is retention in place. A transaction that stays open pins the xmin horizon; every vacuum on every table in the database can then remove nothing newer than that horizon, no matter how aggressively autovacuum is tuned. Dead tuples accumulate, tables and indexes grow, sequential scans read pages full of invisible rows, and the planner's statistics drift. Importantly, an idle-in-transaction session — an app that ran BEGIN and went to sleep — is just as damaging as a genuinely long query. And long transactions are only one way to hold the horizon: abandoned replication slots, hot_standby_feedback from a lagging replica, and forgotten prepared transactions all pin it too, and all deserve monitoring on PostgreSQL.
-- Dead tuple pressure per table
SELECT relname, n_dead_tup, n_live_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 15;
-- Who is holding the xmin horizon back?
SELECT pid, state, age(backend_xmin) AS xmin_age,
now() - xact_start AS xact_duration,
left(query, 80) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 10;The wraparound cliff
Here is the part with no InnoDB equivalent. PostgreSQL transaction IDs are 32-bit counters compared in a circular space, so an old xid must be frozen before the counter laps it — roughly two billion transactions later. Autovacuum handles freezing routinely, forcing an aggressive anti-wraparound vacuum once a table's oldest unfrozen xid passes autovacuum_freeze_max_age (200 million by default). Modern versions add a failsafe (vacuum_failsafe_age) that drops throttling to finish faster. If freezing still cannot complete — typically because something pins the horizon for a very long time — PostgreSQL will eventually refuse new write transactions to protect data. Real wraparound outages are rare and always preceded by weeks of visible warnings, but "the database stopped accepting writes" is a headline failure mode, so monitor age(datfrozenxid) per database and alert long before the thresholds. On InnoDB, the analogous worst case is a huge undo tablespace and slow queries; annoying, not existential. This asymmetry is the single biggest mindset change for a MySQL DBA adopting PostgreSQL.
How each engine degrades, side by side
Same trigger, different curves. On InnoDB: history list climbs, undo space grows, reads with old snapshots slow down gradually; kill the offending transaction and purge catches up quietly, with no lasting damage beyond undo tablespace size. On PostgreSQL: dead tuples climb, tables and indexes bloat on disk, scans slow down; kill the offending transaction and vacuum can finally remove the backlog — but the bloat already baked into the files does not shrink on its own. Plain VACUUM makes the space reusable, while returning it to the operating system takes VACUUM FULL or a reorganization tool, both of which are disruptive operations you schedule, not run casually. In short: InnoDB's damage is mostly self-healing, PostgreSQL's leaves scar tissue. The PostgreSQL discipline that follows is simple: keep transactions short, set idle_in_transaction_session_timeout, watch the horizon, and let autovacuum run — it is the immune system, not overhead to be tuned away.
What to watch on each side
On MySQL while you still run it: history list length trend, oldest transaction age from innodb_trx, and undo tablespace size. On PostgreSQL after the move: n_dead_tup and autovacuum recency per table, oldest xmin age across pg_stat_activity, replication slots, and prepared transactions, age(datfrozenxid) per database, and long or idle-in-transaction sessions. The vacuum signals are also early-warning indicators for query performance, because a bloated hot table shows up first as a slow query family — which is why the vacuum view and query history belong in the same dashboard rather than separate tools.
How MonPG helps once you are on PostgreSQL
MonPG monitors PostgreSQL only, and MVCC hygiene is core to what it watches. It tracks dead tuples, autovacuum activity, and table growth over time so bloat is a trend line instead of a surprise; it surfaces long transactions, idle-in-transaction sessions, and xmin horizon holders — the root causes — next to the symptoms; and it keeps xid age visible so wraparound pressure is a dashboard item, not a 3 a.m. discovery. Because it also retains query history, you can see the moment a bloating table starts dragging a query family, which is usually the first business-visible symptom. The PostgreSQL monitoring guide covers the full vacuum and transaction signal set. Learn the new failure shape before it happens to you; it is very observable if you are looking.