When MySQL operators first hear about PostgreSQL's VACUUM, the reaction is usually some mix of confusion and alarm: the database needs a background process to clean up after ordinary DELETEs and UPDATEs, and if that process falls behind, tables bloat and, in the pathological case, the cluster protects itself against transaction ID wraparound? Coming from InnoDB, where nothing like OPTIMIZE TABLE is ever urgent, that sounds like a design flaw. It is not; it is a different placement of the same MVCC cost. But it does mean the maintenance discipline you carry into the migration has to change, because on PostgreSQL the cleanup loop is not optional.
Having operated both, here is the honest comparison: InnoDB's model is lower-maintenance in the common case, PostgreSQL's model is more transparent and more tunable, and both punish you for long-running transactions.
Two different answers to old row versions
Both engines implement multi-version concurrency control, so both must keep old row versions around while some transaction might still need them, and both must eventually discard those versions. Where they put them differs completely.
InnoDB updates rows in place inside the clustered index and writes the previous version to undo logs, a separate structure. Readers needing an older snapshot reconstruct it from undo. Purge threads discard undo records once no transaction can need them. The table itself, therefore, does not fill up with dead rows; version cleanup pressure shows up in undo growth instead, visible as the history list length. When purge falls behind, typically because a long-running transaction pins old versions, undo tablespaces balloon and reads that traverse long version chains get slower.
PostgreSQL writes a whole new row version into the heap on every UPDATE and marks the old one dead; DELETEs likewise leave dead tuples in place. Readers pick the visible version by transaction visibility rules, no reconstruction needed. The cost is that dead tuples occupy heap and index space until VACUUM removes them. Vacuum does not usually shrink the file; it records reclaimed space in the free space map for reuse by future writes, only truncating empty pages at the very end of the table. Bloat, in PostgreSQL vocabulary, is the gap between the space a table occupies and the space its live rows need. HOT updates soften this: when no indexed column changes and the page has room, the new version lands on the same page without touching indexes at all, which is why fillfactor below 100 is a real tuning lever for update-heavy tables.
What OPTIMIZE TABLE actually does
InnoDB tables still fragment: DELETEs leave gaps inside pages, and freed extents stay inside the .ibd tablespace file rather than returning to the filesystem, visible as data_free in the tables view.
SELECT table_schema,
table_name,
ROUND(data_length / 1024 / 1024) AS data_mb,
ROUND(index_length / 1024 / 1024) AS index_mb,
ROUND(data_free / 1024 / 1024) AS free_mb
FROM information_schema.tables
WHERE engine = 'InnoDB'
ORDER BY data_free DESC
LIMIT 15;OPTIMIZE TABLE on InnoDB maps to ALTER TABLE ... FORCE: a full table rebuild that rewrites the clustered index and all secondary indexes compactly and returns the reclaimed space to the OS (with innodb_file_per_table, the default). It runs as online DDL, permitting concurrent DML for most of the operation with brief locks at the edges, but it still rewrites the whole table, costing I/O, temporary disk, and replication lag. The important cultural fact: most InnoDB tables never need it. Free space inside the tablespace gets reused, and the steady state is fine. Rebuilds are for after a mass purge or for reclaiming disk, not a scheduled ritual. The urgent InnoDB metric is instead purge lag, visible as trx_rseg_history_len in information_schema.innodb_metrics.
What VACUUM does, and what it does not
Plain VACUUM on PostgreSQL removes dead tuples from heap and indexes, updates the free space map and visibility map, freezes old tuples to defend against transaction ID wraparound, and runs without blocking reads or writes. It is closer to InnoDB's purge threads than to OPTIMIZE TABLE, and that is the key mapping: VACUUM is the ongoing version cleanup, not the rebuild. What it does not do is return most space to the filesystem or rewrite tables compactly. VACUUM FULL does, but it takes an ACCESS EXCLUSIVE lock for the entire rewrite, blocking everything; on a production table of any size it is an outage, and I treat it as a last resort.
The universal shared enemy deserves its own sentence: a single transaction (or an abandoned replication slot, or a stale prepared transaction) held open for hours prevents cleanup in both engines. On InnoDB it grows the history list; on PostgreSQL it pins the xmin horizon so VACUUM cannot remove otherwise-dead tuples anywhere in the database. Idle-in-transaction sessions are a sev-2 in waiting on either engine.
Autovacuum: tune it, do not fight it
Autovacuum is the daemon that runs VACUUM and ANALYZE automatically, triggered per table when dead tuples exceed a threshold, by default roughly 20 percent of the table plus 50 rows. That default betrays large tables: 20 percent of 500 million rows is 100 million dead tuples before autovacuum bothers. Production tuning is mostly per-table storage parameters, lowering autovacuum_vacuum_scale_factor to 0.02 or an absolute threshold on the biggest tables, plus raising autovacuum_vacuum_cost_limit so workers can actually keep pace, and autovacuum_max_workers with the understanding that the cost budget is shared among them. Recent releases keep improving this machinery; PostgreSQL 17 notably rebuilt vacuum's dead-tuple memory structure, cutting memory use and lifting the old 1 GB ceiling, so vacuums of huge tables need fewer index passes. Monitoring the loop matters more than any single setting.
SELECT schemaname,
relname,
n_live_tup,
n_dead_tup,
round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1)
AS dead_pct,
last_autovacuum,
autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 15;Rising n_dead_tup with a stale last_autovacuum is the canonical early-warning signature. pg_stat_progress_vacuum shows currently running vacuums, and the age of the oldest transaction belongs on the same dashboard.
When you actually need a rebuild: pg_repack
When a PostgreSQL table has already bloated badly, say after a mass delete, VACUUM alone will not shrink it, and VACUUM FULL locks it. The standard answer is pg_repack, an extension that rebuilds a table online: it copies rows into a new table, tracks concurrent changes via triggers, swaps the tables, and needs only brief locks at the start and end. It is the closest functional equivalent to InnoDB's online OPTIMIZE TABLE, with the caveats that it is an external tool requiring installation, roughly double the table's disk space during the rebuild, and care around the final swap under heavy write load. For index-only bloat, built-in REINDEX CONCURRENTLY covers the common case without any extension.
Maintenance scheduling across the migration
The MySQL maintenance calendar is mostly event-driven: watch history list length continuously, rebuild specific tables after mass purges, and otherwise leave InnoDB alone. The PostgreSQL calendar is continuous: autovacuum runs all the time and your job is to verify it keeps up, with per-table tuning for the outliers, scheduled manual VACUUM ANALYZE only for special cases like right after bulk loads, and pg_repack reserved for recovery from accumulated bloat rather than routine use. Plan for it in capacity, too: vacuum consumes I/O, and on undersized storage autovacuum and the workload fight each other, a failure mode covered in the PostgreSQL sizing guide. Teams running their own hardware should also fold vacuum monitoring into their base telemetry from day one; the self-hosted PostgreSQL monitoring guide lists the signals. The mindset change is the whole migration in miniature: InnoDB asks you to intervene rarely and trust the engine; PostgreSQL asks you to supervise a visible process and gives you every knob to do it.
How MonPG helps once you run PostgreSQL
MonPG monitors PostgreSQL only, and vacuum health is one of the places continuous monitoring pays for itself fastest, because bloat incidents are slow-motion: the evidence accumulates for weeks before the symptom arrives. MonPG tracks dead tuple growth, autovacuum activity and lag per table, bloat estimates, the xmin horizon, and long-running transactions in one place, and correlates them with query latency, so "this table bloated, cache efficiency dropped, and this query family slowed down" becomes one visible chain instead of three disconnected graphs. For a team arriving from InnoDB, it is effectively the history-list-length dashboard you are used to, rebuilt for PostgreSQL's version of the problem. The PostgreSQL monitoring guide shows where vacuum evidence sits in the overall baseline.