The byte by byte view makes the failure mode people run into easier to explain. VACUUM can never remove a tuple whose xmax is newer than the oldest snapshot still open, so one forgotten idle transaction or an abandoned replication slot pins that horizon and every autovacuum pass does a full scan while reclaiming almost nothing. When a table keeps bloating even though autovacuum looks healthy, backend_xmin in pg_stat_activity and xmin in pg_replication_slots are the first two places to check.
Small addendum: Unless the whole table is in that state (or even newer), vacuum shouldn't do a full table scan, but only process not yet vacuumed parts of the table. There are some exceptions to that (evenly randomly updated tables, very small tables), but they shouldn't apply in a case like this.
Really nice explanation! One note: pg_repack is an extension, but Postgres 19 will get a built-in REPACK command: https://www.postgresql.org/docs/19/sql-repack.html .
The byte by byte view makes the failure mode people run into easier to explain. VACUUM can never remove a tuple whose xmax is newer than the oldest snapshot still open, so one forgotten idle transaction or an abandoned replication slot pins that horizon and every autovacuum pass does a full scan while reclaiming almost nothing. When a table keeps bloating even though autovacuum looks healthy, backend_xmin in pg_stat_activity and xmin in pg_replication_slots are the first two places to check.
Small addendum: Unless the whole table is in that state (or even newer), vacuum shouldn't do a full table scan, but only process not yet vacuumed parts of the table. There are some exceptions to that (evenly randomly updated tables, very small tables), but they shouldn't apply in a case like this.
Thank you! And you are right. Many people forgot about idle-in-transaction backends.