Some PostgreSQL maintenance queries

maintenance.sql

--- Get the names of databases and the age of their oldest unfrozen transaction ID (datfrozenxid)

SELECT datname, age(datfrozenxid)
FROM pg_database
ORDER BY age(datfrozenxid) desc
limit 20;

---  Get list of regular tables, including their size, age of the oldest unfrozen transaction ID (xid_age),
--   number of dead tuples, and timestamps for the last manual and automatic VACUUM and ANALYZE operations.
SELECT ns.nspname                                AS schema_name,
       cls.relname                               AS table_name,
       cls.relkind                               as type,
     --  pg_size_pretty(pg_relation_size(cls.oid)) AS table_size,
       age(cls.relfrozenxid)                     AS xid_age,
       stat.n_dead_tup                           AS dead_tuples,
       stat.last_vacuum,
       stat.last_autovacuum,
       stat.last_analyze,
       stat.last_autoanalyze
FROM pg_stat_user_tables stat
         JOIN pg_class cls ON stat.relid = cls.oid
         JOIN pg_namespace ns ON cls.relnamespace = ns.oid
where cls.relkind = 'r'
--order by pg_relation_size(cls.oid) desc;


--- Retrieves detailed information about active VACUUM processes,
SELECT act.pid                                                                                     as pid,
       usename                                                                                     as user,
       state                                                                                       as state,
       current_timestamp - xact_start                                                              AS xact_runtime,
       query                                                                                       as query,
       phase                                                                                       as phase,
       wait_event                                                                                  as event,
       round(prog.heap_blks_scanned::float / nullif(prog.heap_blks_total::float, 0) * 100) || ' %' as heap_scan_prog,
       prog.heap_blks_scanned                                                                      as blocks_scanned,
       prog.heap_blks_total                                                                        as blocks_total,
       prog.heap_blks_vacuumed                                                                     as blocks_vacuumed,
       prog.index_vacuum_count                                                                     as index_vacuum_count,
       prog.num_dead_tuples                                                                        as dead_tuples_collected,
       prog.max_dead_tuples                                                                        as max_dead_tuples
FROM pg_stat_activity act
         left join pg_stat_progress_vacuum prog on act.pid = prog.pid
WHERE upper(query) LIKE '%VACUUM%'
ORDER BY xact_start;


-- Retrieves information on tables, including schema and name, age of the oldest unfrozen transaction ID autovacuum
-- parameters, and statistics on dead tuples, filtered by certain autovacuum threshold conditions.

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;


--- Retrieves statistics for non-unique indexes in a PostgreSQL database that are not used for enforcing constraints
-- and do not contain any expression-based columns. 
SELECT s.schemaname,
       s.relname                                      AS tablename,
       s.indexrelname                                 AS indexname,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
       idx_scan,
       idx_tup_fetch,
       idx_tup_read
FROM pg_catalog.pg_stat_user_indexes s
         JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
WHERE 1 = 1
  --  and s.idx_scan = 0      -- has never been scanned
  AND 0 <> ALL (i.indkey) -- no index column is an expression
  AND NOT i.indisunique   -- is not a UNIQUE index
  AND NOT EXISTS -- does not enforce a constraint
    (SELECT 1
     FROM pg_catalog.pg_constraint c
     WHERE c.conindid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;



-- Show blocked querys 

SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_query,
    blocking_activity.query AS blocking_query,
    blocked_activity.application_name AS blocked_application,
    blocking_activity.application_name AS blocking_application
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;