Vacuum at the Page Level
boringsql.com
Vacuum at the Page Level
1–6 of 6 posts
Re: Vacuum at the Page Level
#2The 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.
Re: Vacuum at the Page Level
#3The 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_…
Thank you! And you are right. Many people forgot about idle-in-transaction backends.
Re: Vacuum at the Page Level
#4The 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_…
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.
Re: Vacuum at the Page Level
#5Really 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 .
Re: Vacuum at the Page Level
#6Really 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 .
OP here - correct, I guess I should clarify soon.