Hi All,
PostgreSQL v11.19-1PGDG.rhel7 (upgrading to 12+ is so far only talk)
CentOS 7.9
I see various recommendation for queries to return the tables most in need of a
vacuum that exclude the TOAST tables with something like:
...AND n.nspname NOT IN ('pg_toast')
My real issue is autovacuum not keeping up in spite of seeming to have the
resources to do so. My question at the moment is whether it is best to include
TOAST tables when scripting a manual vacuum of oldest tables. I'm currently
querying for a list of oldest X number of tables and feeding to 5-20 "threads"
and monitoring resources. if it's in pg_toast namespace (which is all of them),
I execute a vacuum freeze on the main table. Repeating this as necessary. All
are TOAST and they belong to sets of tables that are created over a day and
never updated after. These tables are months old. I've asked the developers to
initiate vacuums so at the moment I'm more interested in understanding best
practice in this scenario.
If I understand correctly, autovacuum handles tables and their associated TOAST
tables separately but a manual vacuum will also vacuum the TOAST. When manually
vacuuming does it matter whether it's the main table or TOAST?
I'm not where I have access but I this is close to what I'm using. I've toggled
between using limit and relfrozedxid greater than X. I want to use the least
resource intensive combination of query and vacuum.
SELECT c.oid::regclass, age(c.relfrozenxid), o.relname, o.relnamespace
FROM pg_class c
LEFT JOIN pg_class o on (c.relfilenode != 0 AND c.relfilenode =
o.reltoastrelid)
JOIN pg_namespace n on c.relnamespace = n.oid
WHERE c.relkind IN ('r', 't', 'p')
AND n.nspname IN ('public','pg_toast')
AND age(c.relfrozenxid) > ${max_age}
ORDER BY 2 DESC
I've posted before about these same systems. It'll get to age(datfrozenxid) >
2,000,000,000 and is not able to keep up until I get it back down to under
~600000000. Then it starts humming along as if I "cleared" something.
I appreciate any advice.
Thanks
Senor
