PostgreSQL 성능/특성 정리: 플래너/통계/VACUUM/MVCC로 느린 쿼리 잡기

PostgreSQL 성능/특성 정리: 플래너/통계/VACUUM/MVCC로 느린 쿼리 잡기

이 글은 PostgreSQL를 성능/운영 관점에서 길게 정리한 개인 노트입니다. 목표는 "지금 느린 이유"를 증거로 좁히고, 재발을 줄이는 체크리스트를 갖추는 것입니다.

전제: 성능은 DB만으로 결정되지 않습니다. 쿼리 패턴, 데이터 분포, 인덱스/스키마, 애플리케이션 트랜잭션 경계, 인프라(IO/네트워크)가 함께 결정합니다. 그래서 이 글은 '성능 모델(어디서 시간이 쓰이나) -> 설계/쿼리 -> 설정 -> 운영' 순서로 정리합니다.

PostgreSQL은 '통계(플래너) + MVCC + VACUUM(가비지 회수)'가 성능을 좌우합니다. 느린 쿼리 원인을 고립시키는 순서를 잡는 게 핵심입니다.

이 DB를 언제 선택하나

정답은 항상 케이스 바이 케이스지만, 선택을 빠르게 만들기 위해 기준을 먼저 둡니다. 아래에 해당이 많을수록 이 DB를 고려할 만합니다.

- 일관된 OLTP(주문/결제/계정) + 복잡한 조인/서브쿼리를 같이 처리해야 할 때
- 데이터 무결성(제약조건)과 확장 기능(인덱스/타입/확장)이 중요한 서비스
- 튜닝/관측(통계, 실행 계획, wait/IO)을 근거 기반으로 돌리고 싶을 때

성능 모델: 어디서 시간이 쓰이나

PostgreSQL의 성능은 크게 3갈래로 나뉩니다. 1) 플래너가 통계를 기반으로 좋은 실행 계획을 고르느냐, 2) 인덱스/IO 경로가 그 계획을 뒷받침하느냐, 3) MVCC로 생기는 dead tuple을 VACUUM이 제때 회수하느냐.

MVCC는 읽기/쓰기를 잘 분리해 주지만, 업데이트/삭제가 많으면 테이블/인덱스가 불어나고(bloat) 통계도 틀어져 실행 계획이 갑자기 나빠질 수 있습니다. 그래서 Postgres 운영은 튜닝만큼이나 정리(autovacuum)와 통계(analyze)가 반복 작업입니다.

또 하나의 현실은 메모리 배분입니다. shared_buffers, work_mem, maintenance_work_mem, max_connections가 서로 영향을 주기 때문에, 특정 값만 키우면 다른 병목이 튀어나올 수 있습니다.

핵심 용어 빠른 사전

성능/장애 분석에서 자주 쓰는 단어는 팀마다 다르게 이해되기 쉽습니다. 용어를 짧게 정의하고, 어떤 지표/증상과 연결되는지 같이 적어둡니다.

용어	설명	실무에서 연결되는 것
MVCC	다중 버전 동시성 제어(읽기/쓰기 충돌을 줄임)	업데이트/삭제가 많으면 dead tuple/bloat로 성능이 서서히 악화
dead tuple	MVCC로 남는 '죽은 행' 버전	VACUUM 지연, 인덱스/테이블 bloat, 계획 악화
VACUUM	dead tuple 회수/가시성 맵 갱신	테이블이 점점 느려질 때 1순위 점검 대상
ANALYZE/통계	플래너가 계획을 고르기 위한 데이터 분포 정보	rows 추정이 틀리면 계획이 갑자기 나빠짐
WAL	쓰기 내구성/복구를 위한 로그	체크포인트/IO 스파이크, 복제 대역폭과 직결
장시간 트랜잭션	오래 열린 트랜잭션	VACUUM 회수 방해 -> bloat 악화, 락/대기 증가

옵션/핵심 요소(3~6)

자주 부딪히는 핵심 요소를 표로 먼저 고정해 두면, 장애/튜닝 때 팀 커뮤니케이션이 빨라집니다.

항목	의미	언제 쓰는지(실무 상황)
EXPLAIN (ANALYZE, BUFFERS)	실행 계획 + 실제 실행/버퍼/IO를 함께 본다	느린 쿼리를 추측이 아니라 증거로 해부할 때
VACUUM (ANALYZE)	dead tuple 회수 + 통계 갱신	업데이트/삭제가 많은 테이블이 점점 느려질 때
autovacuum	자동 VACUUM/ANALYZE	야간 배치만으로는 정리가 따라가지 못할 때
work_mem	정렬/해시 조인 작업 메모리	Sort/Hash가 디스크로 spill 될 때
shared_buffers	Postgres 내부 버퍼 캐시	읽기 IO가 많은데 메모리 여유가 있을 때
max_connections	동시 세션 상한	연결 폭증으로 컨텍스트 스위칭/메모리 병목일 때

실무 튜닝 포인트(설정/옵션별)

설정에는 정답이 없습니다. 워크로드(읽기/쓰기/동시성/데이터 크기) 기준으로 관측하고 조정합니다. 아래는 실무에서 가장 자주 만지는 레버를 '항목별'로 정리한 것입니다.

shared_buffers
의미: Postgres 버퍼 캐시 크기
언제 만지나: 버퍼 히트율이 낮고 메모리 여유가 있을 때
주의/트레이드오프: 과도하게 키우면 OS 캐시와 경쟁하고 체크포인트/쓰기 패턴이 나빠질 수 있음

effective_cache_size
의미: 플래너의 캐시 추정치 힌트
언제 만지나: 플래너가 인덱스를 과소평가하는 것 같을 때
주의/트레이드오프: 실제 캐시를 늘리는 게 아니라 추정치라 과대평가하면 나쁜 계획을 고를 수 있음

work_mem
의미: Sort/Hash 작업 메모리(노드/세션 단위)
언제 만지나: EXPLAIN에 external merge/spill이 보일 때
주의/트레이드오프: 동시 쿼리가 많으면 메모리 폭발로 이어질 수 있음

maintenance_work_mem
의미: VACUUM/CREATE INDEX 작업 메모리
언제 만지나: 대형 인덱스 생성/재작성, VACUUM이 느릴 때
주의/트레이드오프: 동시 유지보수 작업 시 메모리 경합 가능

autovacuum_vacuum_scale_factor
의미: 변경 비율에 따른 VACUUM 트리거
언제 만지나: 큰 테이블에서 bloat가 쌓일 때
주의/트레이드오프: 너무 낮추면 autovacuum이 과도하게 돌며 IO를 흔들 수 있음

autovacuum_analyze_scale_factor
의미: 통계(ANALYZE) 트리거 비율
언제 만지나: 카디널리티가 급변하는 테이블에서 계획이 흔들릴 때
주의/트레이드오프: 너무 낮추면 analyze가 잦아져 부하

checkpoint_timeout
의미: 체크포인트 간격
언제 만지나: 체크포인트 지연 스파이크가 보일 때
주의/트레이드오프: 너무 늘리면 복구 시간이 길어지고 WAL가 커질 수 있음

max_wal_size
의미: WAL 최대 크기(체크포인트 유발)
언제 만지나: 쓰기 부하에서 체크포인트 빈도를 줄이고 싶을 때
주의/트레이드오프: 디스크 여유/복제/백업 대역폭 고려 필요

random_page_cost
의미: 플래너의 랜덤 IO 비용 힌트
언제 만지나: SSD인데도 Seq Scan만 선택되는 경우
주의/트레이드오프: 근거 없이 낮추면 인덱스 스캔 남발로 느려질 수 있음

max_parallel_workers_per_gather
의미: 병렬 쿼리 작업자 수
언제 만지나: 대용량 스캔/집계가 느리고 CPU가 남을 때
주의/트레이드오프: 작업자 증가가 CPU/메모리 소모로 직결

쿼리/스키마 설계에서 성능이 갈리는 지점

Postgres에서 인덱스를 만들었는데도 느리다는 이야기의 상당수는 통계가 틀리거나, 인덱스가 쿼리 형태를 제대로 못 받쳐주는 경우입니다. 조건식에 함수/캐스팅이 들어가면 인덱스가 무력화되기 쉬우니 표현식 인덱스나 생성 컬럼을 고려합니다.

복합 인덱스는 좌측(prefix)부터 효력이 생깁니다. (a, b, c)는 a 조건이 없으면 기대만큼 못 쓰는 경우가 많습니다. 자주 쓰는 필터/정렬 패턴을 먼저 정리한 뒤 인덱스를 설계하는 게 빠릅니다.

-- 계획/실행을 같이 본다
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 42 AND created_at >= now() - interval '7 days' ORDER BY created_at DESC LIMIT 50;

-- 대량 적재 후 통계 갱신
ANALYZE orders;

-- 부분 인덱스 예시(최근 데이터만 자주 본다면)
CREATE INDEX CONCURRENTLY idx_orders_user_recent ON orders (user_id, created_at DESC) WHERE created_at >= date_trunc('day', now()) - interval '30 days';

-- 표현식 인덱스 예시
CREATE INDEX CONCURRENTLY idx_users_email_lower ON users ((lower(email)));

진단 명령/쿼리 모음(바로 실행용)

장애 때는 문서 찾을 시간이 없습니다. 아래는 현장에서 자주 쓰는 '첫 5분' 진단 명령/쿼리만 모은 것입니다. 환경/권한에 따라 일부는 제한될 수 있습니다.

-- 1) 현재 어떤 세션이 오래 잡혀 있는지
SELECT pid, usename, state, wait_event_type, wait_event, now() - query_start AS running, left(query, 120) AS q FROM pg_stat_activity WHERE state <> 'idle' ORDER BY running DESC;

-- 2) 테이블/인덱스 bloat가 의심되면(단, 정확한 bloat 측정은 별도 확장/스크립트가 필요할 수 있음)
SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20;

-- 3) 느린 쿼리 1개는 반드시 EXPLAIN (ANALYZE, BUFFERS)로 증거를 남긴다
EXPLAIN (ANALYZE, BUFFERS) SELECT 1;

자주 하는 실수(운영/튜닝)

실수 체크리스트:
- autovacuum 기본값을 그대로 두고, 업데이트/삭제가 많은 대형 테이블을 방치한다
- 장시간 트랜잭션(특히 배치/리포트)을 운영에 그대로 올려 VACUUM을 막는다
- work_mem을 무작정 키워 동시 쿼리에서 메모리 폭발을 만든다
- 통계(ANALYZE)가 낡았는데도 인덱스만 더 만든다
- 부분 인덱스/표현식 인덱스 같은 '쿼리 형태에 맞춘 인덱스'를 고려하지 않는다
- max_connections를 늘려서 문제를 숨기고, 커넥션 풀링을 도입하지 않는다

운영/모니터링 체크리스트

운영에서는 1) 느린 쿼리 상위(총시간/호출수), 2) autovacuum가 밀리는 테이블, 3) WAL 생성량/체크포인트 빈도, 4) 락 대기(DDL/장시간 트랜잭션), 5) 디스크 IO 지연을 함께 봅니다.

특히 장시간 트랜잭션은 VACUUM의 회수를 막아 bloat를 악화시킵니다. 조용히 느려지는 서비스는 장시간 트랜잭션부터 의심하는 게 빠른 편입니다.

공통 체크리스트:
- 느린 쿼리 top N을 지속 수집(시간/호출수/총시간)
- 지연(p95/p99)과 오류율을 애플리케이션 지표와 함께 본다
- 캐시/메모리/디스크 IO 중 무엇이 병목인지 먼저 분리한다
- 복제/HA를 쓰면 일관성 요구가 높은 기능을 분리한다
- 튜닝/변경 후에는 동일 조건으로 재측정해 회귀를 막는다

트러블슈팅 루틴(순서 고정)

장애 때는 선택지가 너무 많아서 흔히 길을 잃습니다. 아래 순서를 팀 표준으로 정해두면, 원인 추적이 빨라지고 '감으로 설정만 만지는' 일을 줄일 수 있습니다.

1) 느린 쿼리 1개를 고른다(빈도/총시간 기준)
2) EXPLAIN (ANALYZE, BUFFERS)로 계획/IO를 본다
3) 인덱스/통계(ANALYZE) 문제인지 먼저 판별
4) row estimate가 틀리면 통계/컬럼 분포/확장 통계를 점검
5) dead tuple/bloat가 의심되면 VACUUM 상태/장시간 트랜잭션 확인
6) 체크포인트/IO 스파이크면 WAL/체크포인트 설정과 디스크를 같이 본다
7) 변경 후 동일 쿼리를 재측정하고, 회귀 방지를 위해 알람을 추가한다

문제 상황(정확히 1개)

상황 -> 업데이트/삭제가 많은 테이블에서 시간이 지날수록 같은 쿼리가 점점 느려지고, 인덱스도 있는데 Seq Scan이 늘어난다.
원인 -> autovacuum/analyze가 변경량을 따라가지 못해 dead tuple과 bloat가 누적되고 통계가 낡아 플래너 추정이 틀어졌다.
해결 -> 문제 테이블에 VACUUM (ANALYZE)를 수행하고, autovacuum 관련 scale factor를 테이블 특성에 맞게 조정하며, 장시간 트랜잭션을 제거/분리한다.
예방 팁 -> 대형 테이블별 autovacuum 튜닝(테이블 단위 설정 포함)과 장시간 트랜잭션/락 대기 알람을 운영 규칙으로 둔다.

참고/출처

정확한 동작/버전별 차이는 공식 문서를 기준으로 확인합니다. 실무에서는 버전 차이가 곧 성능/장애 차이입니다.

• PostgreSQL Documentation
• PostgreSQL: EXPLAIN
• PostgreSQL: VACUUM