PostgreSQL Vacuum 관련 쿼리 간단 모음

PostgreSQL를 사용하는 프로젝트에서 DevOps 업무를 맡았을 때 자주 사용했던 쿼리들을 간단히 정리해보았다.

 

-- t_product 테이블의 Auto VACUUM 설정을 비활성화
ALTER TABLE public.t_product SET (autovacuum_enabled = false);


-- 가장 오래된 20개의 테이블들의 목록 조회
SELECT
c.oid::regclass AS table_name,
greatest(age(c.relfrozenxid), age(t.relfrozenxid)) AS age, 
pg_size_pretty(pg_table_size(c.oid)) AS table_size
FROM pg_class c LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind = 'r'
ORDER BY 2 DESC LIMIT 20;


-- 현재 데이터베이스에서의 VACUUM 상태정보 조회하기
SELECT datname, usename, pid, CURRENT_TIMESTAMP - xact_start AS xact_runtime, query
FROM pg_stat_activity
WHERE upper(query) LIKE '%VACUUM%'
ORDER BY xact_start;


-- 수동으로 VACUUM을 실행.
-- VACUUM VERBOSE는 VACUUM이 실행되는 동안에도 진행 상황을 보여준다.
-- 따라서, 이 쿼리를 통해 auto vacuum 지연이 발생하는 이유 등을 직관적으로 확인할 수도 있게 된다.
vacuum VERBOSE public.t_product;


-- Block된 프로세스들에 대한 정보를 조회
SELECT
   COALESCE(((blockingl.relation)::regclass)::text,
   blockingl.locktype) AS locked_item, (now() - blockeda.query_start) AS waiting_duration,
   blockeda.pid AS blocked_pid,
   blockeda.query AS blocked_query,
   blockedl.mode AS blocked_mode,
   blockinga.pid AS blocking_pid,
   blockinga.query AS blocking_query,
   blockingl.mode AS blocking_mode
FROM (((pg_locks blockedl
   JOIN pg_stat_activity blockeda ON ((blockedl.pid = blockeda.pid)))
   JOIN pg_locks blockingl ON ((((blockingl.transactionid = blockedl.transactionid)
      OR ((blockingl.relation = blockedl.relation)
      AND (blockingl.locktype = blockedl.locktype)))
      AND (blockedl.pid <> blockingl.pid))))
   JOIN pg_stat_activity blockinga ON (((blockingl.pid = blockinga.pid)
      AND (blockinga.datid = blockeda.datid))))
WHERE ((NOT blockedl.granted) AND (blockinga.datname = current_database()));



-- 일반적으로 Auto Vacuum 대상은 "dead tuple"이 20% 이상인 경우로 선정된다.
-- 즉, dead tuple의 비율이 20% 이상인 테이블을 찾는 방법을 알아야 한다.
SELECT
relname AS TableName,
n_live_tup AS LiveTuples,
n_dead_tup AS DeadTuples,
n_dead_tup / n_live_tup AS ratio,
last_autovacuum AS Autovacuum,
last_autoanalyze AS Autoanalyze,
*
FROM pg_stat_user_tables
WHERE n_dead_tup > 0;


-- 현재 DB내의 dead tuple들의 정보를 조회
SELECT
    n.nspname AS schema_name,
    c.relname AS table_name,
    pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as total_tuple,
    pg_stat_get_live_tuples(c.oid) AS live_tuple,
    pg_stat_get_dead_tuples(c.oid) AS dead_tupple,
    round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,
    round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,
    pg_size_pretty(pg_total_relation_size(c.oid)) as total_relation_size,
    pg_size_pretty(pg_relation_size(c.oid)) as relation_size
FROM pg_class AS c
JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
WHERE pg_stat_get_live_tuples(c.oid) > 0
AND c.relname NOT LIKE 'pg_%'
ORDER BY dead_tupple DESC;


-- 5분 이상 지속되고 있는 쿼리를 강제로 종료시키는 쿼리
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state IN ('active')
AND (now() - query_start) > interval '5 minutes' 
AND wait_event IN ('MultiXactOffsetControlLock', 'multixact_offset', 'SLRURead');