Vacuum Freeze [ PSQL]

-<Req:

pfw_oltp=# SELECT freez

pfw_oltp-# ,greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as max

pfw_oltp-# ,ROUND(100*(greatest(age(c.relfrozenxid),age(t.relfrozenxid)))::float/freez::float) AS perc

pfw_oltp-# FROM pg_class c

pfw_oltp-# LEFT JOIN pg_class t ON c.reltoastrelid = t.oid

pfw_oltp-# JOIN (SELECT setting AS freez FROM pg_settings WHERE name = ‘autovacuum_freeze_max_age’) AS foo ON (true)

pfw_oltp-# WHERE c.relkind = ‘r’ order by greatest(age(c.relfrozenxid),age(t.relfrozenxid)) desc limit 1;

->Res:

  freez   |    max    | perc

———–+———–+——

200000000 | 188290452 |   94

Lister les 50 premières tables qui ont besoin d’un vacuum Freeze quand elles contiennent des lignes vieilles de un million de lignes (pour info le vacuum se fait automatiquement par défaut quand la limite de deux millions est atteinte) :

SELECT n.nspname||’.’||c.oid::regclass as table_name , greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age, pg_size_pretty(pg_table_size(c.oid)) as table_size FROM pg_class c LEFT JOIN pg_class t ON c.reltoastrelid = t.oid join pg_namespace n on c.relnamespace=n.oid JOIN (SELECT setting AS freez FROM pg_settings WHERE name = ‘autovacuum_freeze_max_age’) AS foo ON (true) WHERE c.relkind = ‘r’ and pg_table_size(c.oid) >= 0 AND greatest(age(c.relfrozenxid),age(t.relfrozenxid)) >= ‘100000000’ order by greatest(age(c.relfrozenxid),age(t.relfrozenxid)) desc limit 50;