Find high dead_tuples

#sql #postgres #database

Find High Dead_tuple for vacuum

SELECT
    schemaname,
    relname,
    n_dead_tup AS dead_tuples,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM
    pg_stat_all_tables
WHERE
    n_dead_tup > 1000  
    OR (last_vacuum IS NULL AND last_autovacuum IS NULL)
    OR last_autovacuum < now() - interval '1 day' 
ORDER BY
    n_dead_tup DESC;