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