Vacuum at the Page Level
https://boringsql.com/posts/vacuum-at-the-page-level/
radimm · 3 days ago
2 comments
https://boringsql.com/posts/vacuum-at-the-page-level/
radimm · 3 days ago
2 comments
haeseong · 3 days ago
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.
radimm · 3 days ago
Thank you! And you are right. Many people forgot about idle-in-transaction backends.
anarazel · 6 hours ago
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.
uhoh-itsmaciek · 6 hours ago
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 .
radimm · 5 hours ago
OP here - correct, I guess I should clarify soon.