운영 트러블슈팅 패턴

장애는 대부분 예고 없이 오지 않는다

운영 중인 PostgreSQL에서 발생하는 장애의 대부분은 서서히 쌓인 신호를 미리 잡으면 예방할 수 있다. 락 경합, 테이블 비대화(bloat), 트랜잭션 ID 고갈, 커넥션 폭주, 복제 지연은 하루아침에 터지는 것이 아니다. 이 챕터는 DBA가 가장 자주 맞닥뜨리는 다섯 가지 패턴과 그 진단·해소 방법을 다룬다.

패턴 1: 락 경합(Lock Contention)

증상

• 쿼리가 무한 대기하거나 애플리케이션 타임아웃이 증가한다.
• pg_stat_activity에서 state = 'active'인데 실제 진행이 없다.

진단

-- 블로킹 체인 한눈에 보기
SELECT
    blocking.pid            AS blocking_pid,
    blocking.query          AS blocking_query,
    blocked.pid             AS blocked_pid,
    blocked.query           AS blocked_query,
    blocked.wait_event_type,
    blocked.wait_event
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks kl ON kl.transactionid = bl.transactionid AND kl.granted
JOIN pg_stat_activity blocking ON blocking.pid = kl.pid
WHERE blocked.pid <> blocking.pid;

-- 어떤 락을 누가 보유하는지 상세 조회
SELECT
    l.relation::regclass AS tbl,
    l.locktype,
    l.mode,
    l.granted,
    a.pid,
    a.state,
    left(a.query, 80) AS query,
    now() - a.query_start AS elapsed
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.relation IS NOT NULL
ORDER BY elapsed DESC NULLS LAST;

해소

-- 블로킹 프로세스에 취소 신호 (진행 중인 트랜잭션 롤백)
SELECT pg_cancel_backend(<blocking_pid>);

-- 강제 종료 (pg_cancel_backend로 해결되지 않을 때)
SELECT pg_terminate_backend(<blocking_pid>);

예방

• DDL(ALTER TABLE, DROP INDEX) 은 트래픽이 낮은 시간대에 실행하거나 CONCURRENTLY 옵션을 사용한다.
• lock_timeout을 설정해 장기 대기를 허용하지 않는다.
• idle in transaction 세션이 쌓이지 않도록 애플리케이션 커넥션 타임아웃을 설정한다.

-- 세션 수준 lock timeout 설정 (운영 DDL 수행 시)
SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN note TEXT;

패턴 2: 테이블 비대화(Bloat)와 VACUUM

원리

PostgreSQL의 MVCC는 UPDATE·DELETE 시 기존 행을 dead tuple로 표시하고 새 버전을 삽입한다. VACUUM이 dead tuple을 회수하지 않으면 테이블 파일이 커지고(bloat), 시퀀셜 스캔 시 더 많은 블록을 읽어 쿼리가 느려진다.

-- bloat가 심한 테이블 조회
SELECT
    relname            AS tbl,
    n_dead_tup,
    n_live_tup,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY dead_pct DESC;

autovacuum 튜닝

autovacuum의 기본 설정은 소규모 데이터베이스 기준이다. 쓰기가 많은 테이블은 테이블별로 임계값을 낮춰야 한다.

-- 특정 테이블 autovacuum 임계값 조정 (쓰기 많은 대형 테이블)
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,   -- 기본 0.2 → 1%로 낮춤
    autovacuum_analyze_scale_factor = 0.005, -- 기본 0.1
    autovacuum_vacuum_cost_delay = 2         -- 기본 2ms; 0이면 최속
);

VACUUM FULL vs pg_repack

# pg_repack으로 온라인 bloat 제거
pg_repack -h localhost -U postgres -d mydb -t orders

VACUUM FULL은 프로덕션 운영 시간에 절대 사용하지 않는다. 테이블 크기에 따라 수 시간 동안 배타 락을 보유해 서비스를 중단시킨다.

패턴 3: 트랜잭션 ID 고갈(Wraparound)

원리

PostgreSQL의 트랜잭션 ID(XID)는 32비트 순환 카운터다. 전체 40억 개를 다 쓰면 과거 트랜잭션이 미래로 보여 데이터 가시성이 깨진다. PostgreSQL은 이를 막기 위해 임계값(autovacuum_freeze_max_age, 기본 2억)에 도달한 테이블에 강제 autovacuum(anti-wraparound vacuum) 을 실행한다.

위험 수준 모니터링

-- XID age가 높은 테이블 (2억 근접 시 경보)
SELECT
    relname,
    pg_size_pretty(pg_total_relation_size(oid)) AS size,
    age(relfrozenxid)                            AS xid_age,
    2000000000 - age(relfrozenxid)               AS txids_remaining
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 20;

-- 데이터베이스 전체 XID age
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age DESC;

age가 1억 5천만 을 넘으면 즉시 해당 테이블에 수동 VACUUM을 실행한다.

-- 특정 테이블 강제 freeze
VACUUM FREEZE VERBOSE orders;

긴급 대응

PostgreSQL이 XID 한계에 도달하면 데이터베이스를 읽기 전용 모드로 전환하고 다음 메시지를 기록한다:

WARNING: database "mydb" must be vacuumed within 1000000 transactions

이 메시지가 나타나면 즉시 다음 순서로 처리한다:

1. autovacuum을 비활성화하지 않는다(이미 최우선으로 실행 중이다).
2. VACUUM FREEZE를 수동으로 실행해 가속한다.
3. 모든 오래된 준비된 트랜잭션(pg_prepared_xacts)과 복제 슬롯을 정리한다.

-- 오래된 준비된 트랜잭션 확인
SELECT gid, prepared, age(transaction) FROM pg_prepared_xacts ORDER BY age DESC;

-- 사용되지 않는 복제 슬롯 (XID를 붙잡음)
SELECT slot_name, xmin, catalog_xmin FROM pg_replication_slots;

패턴 4: 커넥션 고갈

증상

애플리케이션에서 FATAL: sorry, too many clients already 오류가 발생한다.

진단

-- 현재 연결 현황
SELECT
    state,
    count(*) AS cnt,
    max(now() - state_change) AS max_idle
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
GROUP BY state
ORDER BY cnt DESC;

-- max_connections 대비 사용률
SELECT
    count(*) AS used,
    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS limit,
    round(100.0 * count(*) / (SELECT setting::int FROM pg_settings WHERE name = 'max_connections'), 1) AS pct
FROM pg_stat_activity;

state = 'idle in transaction' 이 다수면 애플리케이션이 트랜잭션을 열고 방치하는 것이다.

해소

-- idle in transaction 5분 이상 세션 강제 종료
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND state_change < now() - INTERVAL '5 minutes';

# postgresql.conf: 비정상 idle 세션 자동 정리
idle_in_transaction_session_timeout = '10min'
statement_timeout = '30min'

근본 해결은 PgBouncer 도입이다. 애플리케이션 연결은 PgBouncer가 받고, PgBouncer가 PostgreSQL에 작은 수의 실제 연결을 유지한다.

패턴 5: 복제 지연(Replication Lag)

-- Primary에서: 복제 슬롯별 지연 확인
SELECT
    slot_name,
    active,
    pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS lag_bytes,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag_human
FROM pg_replication_slots;

-- pg_stat_replication으로 스탠바이 지연 조회
SELECT
    application_name,
    state,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication;

지연 원인과 대응:

주요 운영 뷰 정리

트러블슈팅 시 공통 원칙

1. 먼저 읽고 나서 바꾼다. EXPLAIN (ANALYZE, BUFFERS), pg_stat_activity, pg_locks로 상황을 파악한 뒤 조치한다.
2. VACUUM FULL은 운영 시간에 쓰지 않는다. 배타 락이 걸린다. bloat 제거는 pg_repack을 쓴다.
3. 복제 슬롯을 방치하지 않는다. 미사용 복제 슬롯은 WAL 파일을 붙잡아 pg_wal/ 디렉터리를 꽉 채운다.
4. XID age 모니터링을 알람에 포함한다. age > 150,000,000이 되기 전에 VACUUM이 완료돼야 한다.
5. idle in transaction을 허용하지 않는다. idle_in_transaction_session_timeout을 설정하고 애플리케이션 커넥션 풀 설정을 점검한다.

References

• https://www.postgresql.org/docs/current/monitoring-stats.html
• https://www.postgresql.org/docs/current/explicit-locking.html
• https://www.postgresql.org/docs/current/routine-vacuuming.html
• https://www.postgresql.org/docs/current/transaction-id-wraparound.html
• https://www.postgresql.org/docs/current/warm-standby.html
• https://www.enterprisedb.com/blog/troubleshooting-stuck-vacuums
• https://www.cybertec-postgresql.com/en/autovacuum-wraparound-protection-in-postgresql/
• https://www.crunchydata.com/blog/managing-transaction-id-wraparound-in-postgresql
• https://www.jusdb.com/blog/postgresql-vacuum-tuning-a-comprehensive-guide
• https://oneuptime.com/blog/post/2026-01-21-postgresql-lock-troubleshooting/view
• https://runebook.dev/en/docs/postgresql/app-vacuumdb
• https://medium.com/@ratandeep.saha/how-we-resolved-postgres-database-bloating-850d25ed3f2c