Comments on: Overcoming VACUUM WRAPAROUND

By: Eddie

Eddie — Fri, 28 Apr 2023 18:47:24 +0000

In reply to Robert Bernier. Thanks for the reply. I'm not sure that I understand the premise. Autovacuum does process TOAST tables independently as seen in pg_stat_activity and pg_stat_progress_vacuum and I can run a manual vacuum on the TOAST table as mentioned earlier. pg_class.relfrozenxid of the main table and its associated entry for the TOAST appear to be maintained separately. Until recently, I thought of the TOAST as an adjunct to the main table where all management was through the main table. I'm trying to clarify some DO's and DON'Ts. Thanks

By: Robert Bernier

Robert Bernier — Thu, 27 Apr 2023 14:20:41 +0000

In reply to Eddie. Hi, > Is there any reason to associate the TOAST table age to the main table? Hi, I hope I can answer in a manner that will satisfy your question. Creating something like a column of type text or bytea in a regular table automatically creates the TOASTED table as the column at some point becomes a reference pointer to the values which are in the toasted table and not in the table i.e. there's a relationship between the two. Because vacuum cannot operate on TOAST tables alone, as they are not relations, one has no choice but to vacuum the table itself that the toast is related. At least that's the current architecture of postgres. Alternatively you can always vacuum the column itself in the table. Hope this helps.

By: Eddie

Eddie — Mon, 24 Apr 2023 04:03:58 +0000

Is there any reason to associate the TOAST table age to the main table? A manual vacuum of the main table will also vacuum the TOAST table but not the reverse. if just trying to vacuum the oldest tables, is there any reason to not just vacuum the ones that are the oldest even if it’s a TOAST table? Vacuuming the main table may be wasting resources if only the TOAST table has an old relfrozenxid.

I recently found myself facing the wraparound situation and grabbed a list of oldest tables and started vacuum threads. When I looked at the list I realized they were all TOAST tables. Still trying to figure out why autovacuum was leaving those alone but there didn’t seem to be any problem manually vacuuming the TOAST directly and it progressed faster.

I have not yet gotten to play with PostgreSQL 13+ and I see there are plenty of changes in this area.

Thanks for the article!