PostgreSQL 운영에서 설정이 먼저인가, 진단이 먼저인가

PostgreSQL을 설치하고 기본값 그대로 운영하는 팀이 있다. shared_buffers = 128MB, work_mem = 4MB, max_connections = 100. 64GB RAM 서버에서. 이 설정이 얼마나 큰 손실인지 알려면, 설정 하나하나가 어떤 내부 동작을 통제하는지부터 이해해야 한다. 그리고 문제가 생겼을 때 — 갑자기 느려졌을 때, 테이블이 폭증했을 때, 연결이 터졌을 때 — 어디서부터 손을 대야 하는가?

메모리 설정의 구조

PostgreSQL의 메모리 설정은 세 개의 독립된 층으로 나뉜다.

첫 번째는 shared_buffers. 모든 백엔드 프로세스가 공유하는 페이지 캐시다. 데이터를 읽을 때 먼저 여기를 확인하고, 없으면 OS Page Cache, 그 다음이 디스크 순이다. 권장값이 RAM의 25%인 이유는 PostgreSQL이 O_DIRECT를 지원하지 않아 OS Page Cache와 이중으로 캐시가 쌓이기 때문이다. 50%를 넘기면 OS가 메모리 부족으로 페이지 스왑을 시작한다.

두 번째는 effective_cache_size. 실제 메모리를 할당하지 않는다. 플래너에게 “이만큼의 캐시가 있다고 가정하라”는 힌트만 준다. 이 값이 크면 플래너가 더 적극적으로 Index Scan을 선택하고, 작으면 Sequential Scan으로 기울어진다. RAM의 75%가 권장값이다.

세 번째는 work_mem. 정렬, 해시 조인, 집계에서 각 실행 노드마다 할당된다. 위험한 이유는 곱셈이다. 100개 동시 연결 × 쿼리당 평균 3개 정렬 노드 × work_mem = 최대 메모리 요구량. 전역값으로 크게 설정하면 OOM이 현실이 된다. 분석 쿼리가 필요할 때는 세션 수준에서 일시적으로 올리고 반드시 RESET work_mem으로 돌려놓는다.

WAL 쪽에서는 checkpoint_completion_target = 0.9가 가장 즉각적인 효과를 낸다. 기본값 0.5는 Checkpoint 간격의 절반 시간에 Dirty Page를 집중적으로 쓴다는 의미다. I/O 스파이크가 그 시점에 몰린다. 0.9로 바꾸면 90% 시간에 걸쳐 분산되어 쿼리 지연이 눈에 띄게 줄어든다.

문제를 숫자로 보는 도구들

설정이 맞더라도 실제 병목은 어디서 오는지 모르면 손을 쓸 수 없다. PostgreSQL은 세 개의 뷰로 거의 모든 진단을 커버한다.

pg_stat_statements는 누적 쿼리 통계다. total_exec_time DESC로 정렬하면 “이 서비스에서 가장 많은 DB 시간을 소비한 쿼리”가 바로 나온다. 애플리케이션 로그에서 “이 쿼리가 2초 걸렸다”는 정보와 달리, 하루에 10만 번 실행되어 총 83분을 소비한 쿼리를 찾아낸다. shared_preload_libraries = 'pg_stat_statements'를 설정하고 재시작한 뒤 CREATE EXTENSION으로 활성화한다.

pg_stat_activity는 현재 실행 중인 프로세스 상태를 보여준다. state = 'idle in transaction'이 오래 유지되는 연결이 있다면 즉시 주목해야 한다. 트랜잭션이 열려있지만 아무 것도 하지 않는 상태 — 이 연결이 VACUUM을 차단하고 Lock을 보유하며 연결 풀을 점유한다.

pg_locks와 pg_blocking_pids()의 조합은 잠금 대기 체인을 SQL로 시각화한다. ALTER TABLE 하나가 AccessExclusiveLock을 잡지 못하고 대기 중이면 그 뒤로 오는 모든 SELECT와 UPDATE도 줄을 선다. 연결 수백 개가 순식간에 쌓이는 그 시나리오의 원인은 항상 이 체인에서 나온다.

-- 잠금 대기 체인 한 번에 파악
SELECT
    waiting.pid AS waiting_pid,
    left(w.query, 60) AS waiting_query,
    blocking.pid AS blocking_pid,
    left(b.query, 60) AS blocking_query
FROM pg_stat_activity w
JOIN pg_stat_activity b ON b.pid = ANY(pg_blocking_pids(w.pid))
WHERE w.wait_event_type = 'Lock';

인덱스는 적을수록 쓰기가 빠르다

“인덱스는 많을수록 좋다”는 오해가 실무에서 꾸준히 등장한다. 현실은 정반대다. 사용하지 않는 인덱스는 모든 INSERT/UPDATE/DELETE마다 갱신 비용을 추가하고, HOT Update 기회를 빼앗으며, 디스크 공간을 낭비한다.

pg_stat_user_indexes.idx_scan = 0인 인덱스가 있다면 미사용이다. 단, 판단 기간이 충분해야 한다. 분기 보고서 쿼리에만 쓰이는 인덱스는 3개월 내내 idx_scan = 0일 수 있다. 최소 2주, 이상적으로 한 분기 이상의 통계가 쌓인 뒤 확인한다.

인덱스 Bloat도 방치하면 문제다. 대량 DELETE 이후 인덱스 파일 크기는 줄지 않는다. pgstatindex()의 avg_leaf_density가 50% 미만이면 REINDEX가 필요하다. 운영 중에는 반드시 REINDEX INDEX CONCURRENTLY를 써야 한다. 일반 REINDEX는 AccessExclusiveLock을 잡는다.

Long Transaction이 VACUUM을 막는 경로

운영 중 테이블이 갑자기 수십 GB로 불어났다면 첫 번째 용의자는 Long Running Transaction이다.

VACUUM이 Dead Tuple을 회수하려면 그 튜플의 t_xmax가 현재 모든 활성 트랜잭션의 xmin 최솟값(OldestXmin)보다 작아야 한다. 8시간 전에 시작된 트랜잭션이 하나 살아있다면 OldestXmin이 8시간 전 XID에 고정된다. 그 이후 생성된 Dead Tuple은 전부 회수 불가 — VACUUM을 아무리 돌려도 크기가 줄지 않는다.

-- OldestXmin을 낮추고 있는 연결 찾기
SELECT pid, now() - xact_start AS duration,
       age(backend_xmin) AS xmin_age, state, left(query, 80)
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

xmin_age가 수백만을 넘는 연결이 있다면 pg_terminate_backend(pid)로 종료한 뒤 VACUUM을 다시 돌린다. 재발 방지는 설정으로 한다: idle_in_transaction_session_timeout = '10min'.

DDL이 유발하는 잠금 폭탄도 같은 맥락이다. lock_timeout = '5s'를 설정하면 DDL이 Lock을 5초 안에 얻지 못하면 대기하는 대신 오류를 반환한다. 그 뒤로 밀려드는 쿼리들이 연쇄 대기에 빠지는 상황을 막는다.

Spring과 연결되는 지점

애플리케이션 레이어에서 가장 자주 틀리는 것은 HikariCP maximumPoolSize다. 너무 작으면 connectionTimeout 예외가, 너무 크면 PostgreSQL 연결 폭발이 발생한다. Percona의 경험 공식은 core_count × 2 + effective_spindle_count다. 4코어 SSD 서버라면 9개가 출발점이다. PgBouncer를 앞에 두고 있다면 HikariCP는 5~10으로 작게 유지하고 PgBouncer가 수천 클라이언트를 수십 PostgreSQL 연결로 압축하도록 한다.

PgBouncer Transaction Mode를 쓸 때는 JDBC URL에 prepareThreshold=0을 추가해야 한다. Prepared Statement를 세션 수준에서 캐시하는 동작이 Transaction Mode와 충돌하기 때문이다.

JSONB와 배열 컬럼을 JPA로 다룰 때는 Hibernate Types 라이브러리가 필요하다. 네이티브 쿼리로 @>, ? 연산자를 직접 쓰는 경우가 많다. Flyway를 쓸 때 CREATE INDEX CONCURRENTLY는 트랜잭션 블록 안에서 실행할 수 없으므로 Java Migration에서 canExecuteInTransaction() = false를 선언해야 한다.

정리

• shared_buffers = RAM × 0.25, checkpoint_completion_target = 0.9가 설치 직후 적용해야 할 두 가지 설정이다.
• 병목 진단 순서: pg_stat_statements로 누적 슬로우 쿼리 → pg_stat_activity로 현재 상태 → pg_blocking_pids()로 잠금 체인.
• 인덱스는 idx_scan = 0 기준으로 주기적으로 정리한다. Bloat은 pgstatindex()로 측정하고 REINDEX CONCURRENTLY로 해소한다.
• Long Transaction이 VACUUM을 차단하는 경로를 이해하면, 테이블 폭증 문제의 90%는 원인을 바로 찾을 수 있다.
• HikariCP maximumPoolSize는 코어 수 기반으로 계산하고, PgBouncer와 함께 쓸 때는 5~10으로 시작한다.

다음 글에서는 EXPLAIN ANALYZE의 각 노드를 읽는 방법과 실행 계획이 예상과 다를 때 플래너 통계를 어떻게 교정하는지 추적한다.