Ermittlung, für welche Tabellen derzeit eine Selbstbereinigung nötig ist

Häufig benötigen eine oder zwei Tabellen eine Bereinigung. Tabellen, deren relfrozenxid-Wert größer als die Anzahl von Transaktionen in autovacuum_freeze_max_age ist, sind stets Ziel der Selbstbereinigung. Wenn andernfalls die Anzahl der Tupeln, die seit dem letzten VACUUM-Befehl veraltet sind, den Bereinigungsschwellenwert überschreitet, wird die Tabelle bereinigt.

Der Selbstbereinigungsschwellenwert ist definiert als:

Vacuum-threshold = vacuum-base-threshold + vacuum-scale-factor * number-of-tuples

wo das vacuum base threshold istautovacuum_vacuum_threshold, das vacuum scale factor ist autovacuum_vacuum_scale_factor und das number of tuples istpg_class.reltuples.

Führen Sie während der Herstellung der Verbindung mit Ihrer Datenbank die folgende Abfrage aus, um eine Liste der Tabellen anzuzeigen, für die Selbstbereinigungsfunktion eine Bereinigung als notwendig betrachtet.

WITH vbt AS (SELECT setting AS autovacuum_vacuum_threshold FROM 
pg_settings WHERE name = 'autovacuum_vacuum_threshold'),
vsf AS (SELECT setting AS autovacuum_vacuum_scale_factor FROM 
pg_settings WHERE name = 'autovacuum_vacuum_scale_factor'), 
fma AS (SELECT setting AS autovacuum_freeze_max_age FROM pg_settings WHERE name = 'autovacuum_freeze_max_age'),
sto AS (select opt_oid, split_part(setting, '=', 1) as param,
split_part(setting, '=', 2) as value from (select oid opt_oid, unnest(reloptions) setting from pg_class) opt)
SELECT '"'||ns.nspname||'"."'||c.relname||'"' as relation,
pg_size_pretty(pg_table_size(c.oid)) as table_size,
age(relfrozenxid) as xid_age,
coalesce(cfma.value::float, autovacuum_freeze_max_age::float) autovacuum_freeze_max_age,
(coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) +
coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples)
AS autovacuum_vacuum_tuples, n_dead_tup as dead_tuples FROM
pg_class c join pg_namespace ns on ns.oid = c.relnamespace 
join pg_stat_all_tables stat on stat.relid = c.oid join vbt on (1=1) join vsf on (1=1) join fma on (1=1)
left join sto cvbt on cvbt.param = 'autovacuum_vacuum_threshold' and c.oid = cvbt.opt_oid 
left join sto cvsf on cvsf.param = 'autovacuum_vacuum_scale_factor' and c.oid = cvsf.opt_oid
left join sto cfma on cfma.param = 'autovacuum_freeze_max_age' and c.oid = cfma.opt_oid
WHERE c.relkind = 'r' and nspname <> 'pg_catalog'
AND (age(relfrozenxid) >= coalesce(cfma.value::float, autovacuum_freeze_max_age::float)
OR coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) + 
coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * 
c.reltuples <= n_dead_tup)
ORDER BY age(relfrozenxid) DESC LIMIT 50;