autovacuum queue

autovacuum_queue.sql

SELECT
    n.nspname,
    c.relname,
    c.oid AS relid,
    c.reltuples,
    s.n_dead_tup,
    s.n_mod_since_analyze,
    COALESCE(
        (SELECT split_part(x, '=', 2) FROM unnest(c.reloptions) q (x) WHERE x ~ '^autovacuum_analyze_scale_factor=' ),
        current_setting('autovacuum_analyze_scale_factor')
    )::float8 AS autovacuum_analyze_scale_factor,
    COALESCE(
        (SELECT split_part(x, '=', 2) FROM unnest(c.reloptions) q (x) WHERE x ~ '^autovacuum_analyze_threshold=' ),
        current_setting('autovacuum_analyze_threshold')
    )::float8 AS autovacuum_analyze_threshold,
    COALESCE(
        (SELECT split_part(x, '=', 2) FROM unnest(c.reloptions) q (x) WHERE x ~ '^autovacuum_vacuum_scale_factor=' ),
        current_setting('autovacuum_vacuum_scale_factor')
    )::float8 AS autovacuum_vacuum_scale_factor,
    COALESCE(
        (SELECT split_part(x, '=', 2) FROM unnest(c.reloptions) q (x) WHERE x ~ '^autovacuum_vacuum_threshold=' ),
        current_setting('autovacuum_vacuum_threshold')
    )::float8 AS autovacuum_vacuum_threshold
FROM
    pg_class c
    JOIN pg_namespace n ON c.relnamespace = n.oid
    LEFT OUTER JOIN pg_stat_all_tables s ON c.oid = s.relid
WHERE c.relkind = 'r'
ORDER BY n.nspname, c.relname;


CREATE VIEW autovacuum_queue AS
WITH s AS (
    SELECT
    n.nspname,
    c.relname,
    c.oid AS relid,
    c.reltuples,
    s.n_dead_tup,
    s.n_mod_since_analyze,
    COALESCE(
        (SELECT split_part(x, '=', 2) FROM unnest(c.reloptions) q (x) WHERE x ~ '^autovacuum_analyze_scale_factor=' ),
        current_setting('autovacuum_analyze_scale_factor')
    )::float8 AS analyze_factor,
    COALESCE(
        (SELECT split_part(x, '=', 2) FROM unnest(c.reloptions) q (x) WHERE x ~ '^autovacuum_analyze_threshold=' ),
        current_setting('autovacuum_analyze_threshold')
    )::float8 AS analyze_threshold,
    COALESCE(
        (SELECT split_part(x, '=', 2) FROM unnest(c.reloptions) q (x) WHERE x ~ '^autovacuum_vacuum_scale_factor=' ),
        current_setting('autovacuum_vacuum_scale_factor')
    )::float8 AS vacuum_factor,
    COALESCE(
        (SELECT split_part(x, '=', 2) FROM unnest(c.reloptions) q (x) WHERE x ~ '^autovacuum_vacuum_threshold=' ),
        current_setting('autovacuum_vacuum_threshold')
    )::float8 AS vacuum_threshold
    FROM
        pg_class c
        JOIN pg_namespace n ON c.relnamespace = n.oid
        LEFT OUTER JOIN pg_stat_all_tables s ON c.oid = s.relid
    WHERE
        c.relkind = 'r'
), tt AS (
    SELECT
        nspname,
        relname,
        relid,
        n_dead_tup,
        n_mod_since_analyze,
        reltuples * vacuum_factor + vacuum_threshold AS v_threshold,
        reltuples * analyze_factor + analyze_threshold AS a_threshold
    FROM
        s
)
SELECT
    nspname,
    relname,
    relid,
    CASE WHEN n_dead_tup > v_threshold THEN 'VACUUM' ELSE '' END AS do_vacuum,
    CASE WHEN n_mod_since_analyze > a_threshold THEN 'ANALYZE' ELSE '' END AS do_analyze
FROM
    tt
WHERE
    n_dead_tup > v_threshold OR
    n_mod_since_analyze > a_threshold;

 
SELECT *,
    pg_relation_size(relid),
    pg_total_relation_size(relid)
FROM autovacuum_queue
    ORDER BY pg_total_relation_size(relid) DESC