Question: query that shows if the tables might need analyze and vacuum

Question

query that shows if the tables might need analyze and vacuum

Answers 0
Added at 2016-12-27 17:12
Tags
Question

i have this doubt I loking for a query that returns if my tables from given schema need analyze and vacuum before wraparound wich can not happen, so far i got this query:

SELECT
pt.schemaname || '.' || pt.relname AS TABLE,
case 
when ((pt.n_tup_del + pt.n_tup_upd) > pgs_threshold.setting::int + (pgs_scale.setting::float * pc.reltuples)) = 't' then 'YES'
when ((pt.n_tup_del + pt.n_tup_upd) > pgs_threshold.setting::int + (pgs_scale.setting::float * pc.reltuples)) = 'f' then 'NO'
end
AS VACUUM
FROM pg_class pc JOIN pg_stat_all_tables pt ON pc.relname = pt.relname
             CROSS JOIN pg_settings pgs_threshold
             CROSS JOIN pg_settings pgs_scale
WHERE pt.schemaname in ('schema','public')
AND pgs_threshold.name = 'autovacuum_vacuum_threshold'
AND pgs_scale.name = 'autovacuum_vacuum_scale_factor'
order by 2;

I'm looking for an output like this:

table | need analyze(y/n) | vacuum wraparound(y/n) | vacuum(already got on query above)

the "vacuum wraparound(y/n)" field is when the XID is close to reach the maximum value

if anyone can help me, I appreciate this.

Answers to

query that shows if the tables might need analyze and vacuum

Source Show
◀ Wstecz