용량 계획과 비대화 감사

예측할 항목

디스크 증가량

SELECT pg_size_pretty(pg_database_size(current_database()));
SELECT pg_size_pretty(pg_total_relation_size(t.oid)) AS total,
       relname
FROM pg_class t
WHERE relkind = 'r'
ORDER BY pg_total_relation_size(t.oid) DESC LIMIT 20;

테이블별 증가량 추적

INSERT INTO size_history (ts, tablename, size_bytes)
SELECT NOW(), relname, pg_total_relation_size(oid)
FROM pg_class WHERE relkind = 'r';

IOPS 추정

SELECT relname, seq_tup_read, idx_tup_fetch,
       seq_tup_read + idx_tup_fetch AS total_reads
FROM pg_stat_user_tables
ORDER BY total_reads DESC LIMIT 20;

RAM 크기 설정

팽창 감사

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup, 0), 2) AS dead_ratio,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

인덱스 팽창

CREATE EXTENSION pgstattuple;

SELECT relname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size,
       (pgstatindex(indexrelid::regclass)).leaf_fragmentation
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC LIMIT 20;

사용하지 않는 인덱스

SELECT schemaname, relname, indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

연결 감사

SELECT datname, usename, application_name, state, COUNT(*)
FROM pg_stat_activity
GROUP BY 1,2,3,4 ORDER BY 5 DESC;

장시간 실행되는 트랜잭션

SELECT pid, state, xact_start, NOW() - xact_start AS xact_age, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_age DESC NULLS LAST LIMIT 20;

WAL과 아카이브

SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0')) AS total_wal_generated;

장애 조치를 위한 계획

요약

빠른 확인