PostgreSQL의 운영 문제(테이블이 계속 커진다, 쿼리가 갑자기 느려진다, 쓰기가 멈췄다)는 대부분 네 가지 설계에서 나온다. 행을 덮어쓰지 않는다(MVCC), 그래서 청소가 필요하다(VACUUM), 변경은 로그에 먼저 쓴다(WAL), 계획은 통계로 고른다(플래너). 아래 기준은 PostgreSQL 18(2026년 10월 현재 최신 정식 버전)이다.
MVCC: UPDATE는 새 버전 쓰기
MVCC(Multiversion Concurrency Control)에서 UPDATE는 기존 행을 고치지 않고 새 행 버전(튜플)을 쓰고, 옛 버전에는 "이 트랜잭션부터는 안 보임" 표시를 한다. DELETE도 표시만 한다. 각 튜플에는 숨은 시스템 컬럼이 있다.
xmin: 이 버전을 만든 트랜잭션 ID(XID)xmax: 이 버전을 지운(또는 대체한) 트랜잭션 ID. 살아 있으면 0
쿼리는 시작할 때 찍은 스냅샷 기준으로 "만든 트랜잭션이 커밋됐고 내게 과거인가, 지운 트랜잭션은 아직인가"를 따져 버전을 고른다. 그래서 읽기는 쓰기를 막지 않고 쓰기도 읽기를 막지 않는다. 대가로 아무도 보지 않는 옛 버전(dead tuple)이 테이블에 쌓인다. 격리 수준과 트랜잭션 일반은 ACID를 본다.
VACUUM: 옛 버전 치우기
일반 VACUUM은 dead tuple 자리를 "재사용 가능"으로 표시한다. 테이블 파일을 줄여 OS에 돌려주지는 않는다(끝부분 페이지가 통째로 비면 예외). 평소 서비스와 함께 돌 수 있다. VACUUM FULL은 테이블을 새로 써서 공간을 돌려주지만 그동안 읽기까지 막는 잠금을 잡는다.
- autovacuum은 dead tuple이
50 + 행 수 × 0.2(기본값)를 넘으면 그 테이블을 청소한다. 큰 테이블은 너무 늦게 돌기 쉬워서 테이블별로autovacuum_vacuum_scale_factor를 낮추곤 한다. PostgreSQL 18은 이 계산값에 상한(autovacuum_vacuum_max_threshold, 기본 1억)을 추가했다 - 청소를 막는 것: 오래 열린 트랜잭션(특히 트랜잭션을 연 채 노는
idle in transaction세션), 준비된 트랜잭션, 버려진 복제 슬롯. 이들이 볼 수도 있는 버전은 지울 수 없다.pg_stat_activity,pg_replication_slots로 찾고,idle_in_transaction_session_timeout으로 막는다 - HOT(Heap-Only Tuple) 업데이트: 인덱스에 걸린 컬럼을 바꾸지 않고 같은 페이지에 빈자리가 있으면 인덱스를 건드리지 않고 끝난다.
fillfactor를 낮춰 자리를 남겨 두면 비율이 올라간다
XID 랩어라운드(wraparound)
XID는 32비트 카운터를 원형으로 쓴다. 어떤 XID에서 보든 약 20억 개는 과거, 20억 개는 미래다. 오래된 튜플의 xmin을 그대로 두면 언젠가 "미래"로 뒤집혀 멀쩡한 행이 안 보이게 된다. 그래서 VACUUM은 충분히 오래된 튜플을 동결(freeze)해 "모든 트랜잭션에 보이는 과거"로 표시한다.
- 테이블의 가장 오래된 미동결 XID 나이가
autovacuum_freeze_max_age(기본 2억)를 넘으면 autovacuum을 꺼 두었어도 랩어라운드 방지 VACUUM이 강제로 돈다 - 나이가
vacuum_failsafe_age(기본 16억)를 넘으면 VACUUM이 속도 제한을 풀고 인덱스 정리 같은 필수 아닌 작업을 건너뛰어 동결부터 끝낸다 - 랩어라운드까지 4천만 개가 남으면 경고, 300만 개 밑이면 새 XID 할당을 거부해 쓰기가 멈춘다
SELECT datname, age(datfrozenxid) FROM pg_database;로 미리 지켜본다. 갑자기 오는 사고가 아니라 몇 주 전부터 숫자로 보인다
WAL과 커밋의 의미
WAL(Write-Ahead Logging)의 규칙은 하나다. 데이터 파일의 페이지를 디스크에 쓰기 전에, 그 변경을 기록한 로그가 먼저 디스크에 있어야 한다. 커밋은 WAL을 디스크에 내려쓴(flush) 시점에 성공을 돌려준다. 데이터 파일 반영은 나중에 체크포인트가 몰아서 한다.
- 커밋 성공 = "데이터 파일에 들어갔다"가 아니라 "로그에 남았으니 장애 뒤에도 재생(redo)해 복구할 수 있다"
- 흩어진 페이지 쓰기 대신 순차 로그 쓰기만 기다리므로 커밋이 빠르다
- 체크포인트는 기본 5분(
checkpoint_timeout) 또는 WAL이max_wal_size(기본 1GB)만큼 쌓이면 일어난다. 장애 복구는 마지막 체크포인트부터 WAL을 재생한다 - 같은 WAL이 복제와 시점 복구(PITR, Point-In-Time Recovery)에도 쓰인다
플래너와 통계
플래너는 비용 기반이고, 비용은 예상 행 수에서 나온다. 예상 행 수는 ANALYZE가 표본을 떠서 만든 컬럼 통계(pg_stats의 자주 나오는 값 목록 MCV(Most Common Values)와 히스토그램)로 계산한다. 통계가 낡으면 계획이 엉뚱하게 바뀐다. 처방은 대개 ANALYZE, 컬럼별 통계 목표 상향(SET STATISTICS), 상관된 컬럼에 대한 확장 통계(CREATE STATISTICS)다.
- 핵심 배포판에는 Oracle식 옵티마이저 힌트가 없다. 의도적인 선택이고, 필요하면
pg_hint_plan같은 확장을 쓴다 - "실행 계획 캐시가 없다"는 말은 반만 맞다. 일반 쿼리는 매번 계획하지만, prepared statement는 몇 번 실행한 뒤 파라미터와 무관한 일반 계획(generic plan)을 재사용할 수 있다(
plan_cache_mode)
EXPLAIN에서 볼 신호
EXPLAIN (ANALYZE, BUFFERS)는 쿼리를 실제로 실행해 예상과 실측을 나란히 보여 준다. PostgreSQL 18부터는 ANALYZE만 붙여도 버퍼 정보가 함께 나온다.
| 보이는 것 | 의심할 것 |
|---|---|
rows=100인데 actual rows=500000 | 통계 문제. 잘못된 계획 대부분의 뿌리 |
Sort Method: external merge Disk | 정렬이 work_mem을 넘어 디스크로 넘침 |
Nested Loop 안쪽의 큰 loops | 행 수를 적게 예측해 조인 방식을 잘못 고름 |
shared read가 hit보다 훨씬 큼 | 캐시에 없는 페이지를 디스크에서 대량으로 읽음 |
인덱스 구조는 B-tree와 B+tree, 인덱스를 걸지 말지의 판단은 DB 인덱스와 트레이드오프, 읽기 분산은 읽기 전용 복제본를 본다.
출처: PostgreSQL 18 — MVCC Introduction · System Columns · Routine Vacuuming · Automatic Vacuuming 설정 · WAL Introduction · Statistics Used by the Planner · PREPARE · Using EXPLAIN · PostgreSQL 18 Release Notes · PostgreSQL Wiki — OptimizerHintsDiscussion