PostgreSQL Vacuum 수행 필요 대상 추출 스크립트

PostgreSQL Vacuum 수행 필요 대상 추출 스크립트

---

- Vacuum threshold(autovacuum 임계값) = vacuum base threshold + vacuum scale factor * number of tuples

WITH avt 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) as autovacuum_freeze_max_age

      , (coalesce(cavt.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 avt 

        on (1=1) join vsf 

        on (1=1) join fma 

        on (1=1)

        left join sto cavt 

        on cavt.param = 'autovacuum_vacuum_threshold' 

        and c.oid = cavt.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(cavt.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;