PostgreSQL 내부 완전 정복 — MVCC, VACUUM, WAL, Query Planner, Index, Partitioning, pgvector까지 (2025)

"Postgres is not a fashion. It's a philosophy." — Tom Lane (PostgreSQL major contributor, 20+ years)

2024년 Stack Overflow Developer Survey에서 PostgreSQL이 처음으로 MySQL을 제치고 1위를 차지했다. 이 반전은 우연이 아니다. 지난 10년간 Postgres는 "JSON을 MongoDB보다 잘 다루고, 벡터 검색을 Pinecone보다 잘하며, 분석도 ClickHouse 수준으로 한다"는 괴물로 진화했다.

PostgreSQL은 1986년 UC Berkeley의 Michael Stonebraker가 시작한 POSTGRES 프로젝트에서 출발했다. 1996년 SQL 지원을 더하면서 PostgreSQL이 되었고, 이후 30년간 ACID 완벽성, 확장성(extensibility), 표준 준수를 철학으로 삼아왔다. 이 글은 Postgres를 "그냥 쓴다"에서 "내부를 이해하고 튜닝한다"로 넘어가려는 사람을 위한 지도다.

1. MVCC — PostgreSQL의 심장

MVCC가 왜 혁명적이었나

전통적 DB는 읽기 잠금을 썼다: 트랜잭션 A가 읽는 동안 B는 쓸 수 없다. 이는 OLTP 성능 킬러였다.

MVCC(Multi-Version Concurrency Control)는 "각 트랜잭션에게 그 시점의 스냅샷을 보여준다"는 아이디어. 읽기와 쓰기가 서로 막지 않는다.

"Readers don't block writers, writers don't block readers."

Oracle vs PostgreSQL 구현의 차이

Oracle: Undo Segment에 이전 버전 저장, 현재 버전은 주 테이블에. PostgreSQL: 모든 버전을 테이블에 저장, dead tuple은 VACUUM으로 청소.

PostgreSQL의 접근은 단순하지만 대가가 있다: 테이블이 필연적으로 부풀어 오른다. 이것이 VACUUM의 운명이다.

Tuple의 속성 — xmin, xmax

PostgreSQL의 각 row(tuple)에는 숨겨진 시스템 컬럼이 있다:

t_xmin   — 이 tuple을 만든 트랜잭션 ID
t_xmax   — 이 tuple을 삭제/업데이트한 트랜잭션 ID (0이면 아직 살아있음)
t_cmin   — 같은 트랜잭션 내 커맨드 순번

트랜잭션은 자신의 snapshot(시작 시 활성 XID 집합)을 기준으로 다음을 판단:

• t_xmin < MyXid 이고 t_xmin이 커밋되었고 t_xmax가 없거나 미커밋 → 보인다
• 그 외 → 안 보인다

이렇게 잠금 없이 읽기 일관성이 보장된다.

UPDATE의 진실 — "제자리 갱신"은 없다

UPDATE users SET name = 'Alice' WHERE id = 1;

실제로는:

1. 기존 tuple의 t_xmax에 현재 트랜잭션 ID 기록 (논리적 삭제)
2. 새 tuple 삽입, t_xmin = 현재 TX ID
3. 관련 인덱스도 모두 새 행 포인터 추가 (HOT update 예외 있음)

이 때문에 PostgreSQL의 UPDATE는 INSERT 급 비용이고, write amplification 문제를 낳는다. HOT(Heap-Only Tuple)는 인덱스 키가 변하지 않을 때 같은 페이지에 넣어 인덱스 갱신을 피한다.

2. VACUUM — 빠질 수 없는 숙명

VACUUM이 하는 일

1. Dead tuple 재활용 — 테이블/인덱스 공간 회수
2. Visibility Map 갱신 — Index-Only Scan 가능 영역 기록
3. Free Space Map 갱신 — INSERT가 쓸 공간 추적
4. XID Wraparound 방지 — XID를 frozen 상태로 마킹 (후술)
5. 통계 수집 (ANALYZE와 함께)

VACUUM FULL vs VACUUM

• VACUUM — 공간을 재사용 가능하게 표시, 테이블 크기는 유지 (이후 INSERT에 활용)
• VACUUM FULL — 테이블 재작성, 크기 축소, AccessExclusiveLock 잡음 (서비스 정지 수준)

프로덕션에서는 pg_repack 익스텐션으로 온라인 리팩 가능.

XID Wraparound — 32비트의 저주

PostgreSQL의 트랜잭션 ID는 32비트(약 42억). 소진되면 이전/이후를 구분할 수 없어 DB가 읽기 전용으로 전환.

방지책:

• VACUUM이 오래된 tuple을 FrozenXID로 마킹 → 영원히 보이도록 고정
• autovacuum_freeze_max_age (기본 2억) 넘으면 강제 vacuum freeze 발동

2020년대 들어 대형 서비스에서 wraparound 장애가 종종 보고되었고, PostgreSQL 17부터 64비트 XID 논의가 본격화. 18에서도 아직 32비트.

autovacuum 튜닝 — 대부분 이걸 못 해서 문제

기본값은 작은 DB 기준. 수천만 row 테이블에서는:

autovacuum_vacuum_scale_factor = 0.02  # 기본 0.2 → 2%로
autovacuum_naptime = 15s               # 기본 1min → 더 자주
autovacuum_max_workers = 6             # CPU에 맞춰
autovacuum_vacuum_cost_limit = 2000   # 기본 200, I/O 허용량 ↑

Dead tuple 비율 모니터링이 핵심:

SELECT relname, n_dead_tup, n_live_tup, 
       round(100.0 * n_dead_tup / NULLIF(n_live_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables ORDER BY dead_pct DESC NULLS LAST;

3. WAL — Write-Ahead Log의 우아함

WAL의 원칙

"데이터 파일을 변경하기 전에, 변경 사항을 로그에 먼저 기록한다."

이 한 줄의 규칙이:

• 장애 복구(crash recovery)
• 복제(replication)
• Point-in-Time Recovery(PITR)
• Logical Decoding

모든 기능의 기반이다.

WAL 쓰기 흐름

1. 트랜잭션이 데이터 변경
2. WAL 레코드를 WAL 버퍼에 쓰기
3. COMMIT 시 WAL을 디스크에 fsync
4. 데이터 파일 자체는 나중에 CHECKPOINT가 처리

즉, 커밋 시 실제 데이터 페이지는 디스크에 없어도 된다. WAL만 영속이면 복구 가능.

CHECKPOINT

• 주기적으로 dirty page를 디스크에 flush
• 너무 자주 → I/O 폭발
• 너무 드물게 → WAL 크기 증가, 복구 시간 ↑
• checkpoint_timeout (기본 5min)과 max_wal_size (기본 1GB)로 제어

복제 — Physical vs Logical

Physical Replication (Streaming):

• WAL을 그대로 Standby에 전송
• 바이트 단위 복제 → 완전 동일
• 전체 클러스터 단위
• 동기/비동기/쿼럼 복제 가능

Logical Replication (10 이상):

• WAL을 디코딩해서 논리적 SQL 변경사항으로 변환
• 테이블/컬럼 단위 선택
• 다른 버전 PG 간 복제 가능
• Upgrade/마이그레이션에 필수 (앞 글 "Zero-Downtime DB Migration" 참조)

동기 복제 설정

synchronous_commit = on
synchronous_standby_names = 'FIRST 2 (replica1, replica2, replica3)'

3개 중 2개 ACK 기다림. 한 replica 장애에도 커밋 지속.

4. Query Planner — "왜 같은 쿼리가 갑자기 느려질까"

3단계 — Parse → Rewrite → Plan → Execute

1. Parser: SQL → AST
2. Rewriter: 뷰 펼치기, 룰 적용
3. Planner: 여러 실행 계획 중 비용 최소 선택 ← 핵심
4. Executor: 실행

비용 모델

Planner는 각 오퍼레이션에 "비용 단위"를 매긴다:

• seq_page_cost = 1.0 (순차 디스크 읽기)
• random_page_cost = 4.0 (랜덤 읽기)
• cpu_tuple_cost = 0.01
• cpu_index_tuple_cost = 0.005
• cpu_operator_cost = 0.0025

SSD라면 random_page_cost = 1.1 정도로 낮추는 게 거의 항상 이득.

통계가 전부다

Planner는 pg_statistic의 통계로 카디널리티를 추정:

• n_distinct — 고유값 수 추정
• most_common_vals — 상위 빈도값
• histogram_bounds — 분포

ANALYZE가 안 돌면 통계가 낡아서 Planner가 헛발질한다.

EXPLAIN ANALYZE 읽기

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT * FROM orders WHERE user_id = 123 AND status = 'paid';

Index Scan using idx_user_status on orders  
  (cost=0.43..125.67 rows=12 width=80) 
  (actual time=0.045..0.312 rows=15 loops=1)
  Index Cond: ((user_id = 123) AND (status = 'paid'::text))
  Buffers: shared hit=8 read=0

읽는 순서:

1. Node 타입: Index Scan, Seq Scan, Hash Join, Merge Join, Nested Loop
2. cost: 추정 시작-종료
3. rows: 추정 vs 실제 — 10배 이상 차이 나면 통계 문제
4. Buffers: hit(캐시)/read(디스크) — 캐시 효율
5. loops: Nested Loop 내부 반복 수

공통 문제 패턴

5. Index — 6가지 타입을 분간하라

B-Tree — 기본값이자 왕

• 범용: =, <, >, BETWEEN, ORDER BY
• 다중 컬럼 인덱스는 왼쪽부터 사용 가능
• PostgreSQL 12부터 중복 제거로 크기 30%+ 감소

Hash

• =만 지원, 범위 불가
• 10에서 WAL 로그 추가되어 비로소 crash-safe
• 실제로는 거의 안 씀 (B-Tree가 대부분 더 나음)

GiST — Generalized Search Tree

• 기하학(PostGIS), 범위 타입, 전문 검색
• 플러그인 가능한 구조 — 자기 자료형에 맞는 인덱스 작성 가능
• 예: CREATE INDEX ON events USING GIST (during) (tsrange)

GIN — Generalized Inverted Index

• 다치 값(배열, JSONB, tsvector) 최적
• 각 "토큰"이 몇 번 문서에 나오는지 역인덱스
• JSONB 인덱스의 기본값
• 쓰기 비용 큼, fastupdate로 완화

BRIN — Block Range Index

• 물리적으로 정렬된 거대 테이블(시계열, 로그) 전용
• 각 블록 범위의 min/max만 저장 → 극도로 작음
• 10TB 시계열에 BRIN 100MB도 안 됨
• 조건: 데이터가 insert 순서대로 정렬되어 있어야 함

HNSW (pgvector) — 벡터 검색의 표준

• PostgreSQL 자체는 아니고 pgvector 익스텐션 (0.5.0+)
• Hierarchical Navigable Small World 그래프
• ANN(Approximate Nearest Neighbor) 검색
• 2024년부터 사실상 RAG의 표준

CREATE EXTENSION vector;
CREATE TABLE items (id bigint, embedding vector(1536));
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops) 
  WITH (m = 16, ef_construction = 64);
SELECT id FROM items ORDER BY embedding <=> '[...]'::vector LIMIT 10;

인덱스 선택 가이드

Partial & Expression Index

-- 조건부 인덱스
CREATE INDEX ON orders (user_id) WHERE status = 'paid';

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

-- Covering index (PG 11+)
CREATE INDEX ON orders (user_id) INCLUDE (total, created_at);

Partial은 인덱스 크기를 극적으로 줄인다.

6. Partitioning — 관계형 DB의 샤딩

Declarative Partitioning — PG 10부터

CREATE TABLE events (
  id bigserial,
  user_id bigint,
  occurred_at timestamp
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_2026_04 PARTITION OF events
  FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

파티셔닝 전략

• RANGE — 시간, 범위값 (가장 흔함)
• LIST — 카테고리 (country, region)
• HASH — 균등 분산 (샤딩)

파티션 프루닝

WHERE 절이 파티션 키를 참조하면 관련 파티션만 스캔.

WHERE occurred_at >= '2026-04-15' 
  → events_2026_04 파티션만 스캔

자동 파티션 관리 — pg_partman

수동으로 매달 파티션을 만드는 건 자살 행위. pg_partman이 자동 생성/삭제.

Citus — Postgres를 수평 샤딩

2019년 Microsoft가 인수. 여러 Postgres 노드에 distributed table을 만들어:

• 샤딩 키로 자동 분배
• 분산 쿼리 실행
• 병렬 INSERT/SELECT

2024년에는 Citus가 Azure Cosmos DB for PostgreSQL의 엔진으로 자리. 자체 운영도 가능.

7. 연결 관리 — 왜 Postgres는 연결이 비싼가

프로세스 기반 모델

PostgreSQL은 연결당 프로세스 모델(스레드 아님). 장점:

• 격리 — 한 연결 크래시가 전체에 영향 없음
• 안정성 — 수십 년 검증

단점:

• 메모리 비용 — 연결당 10-20MB
• 연결 생성 비용 — 수 ms
• 1000+ 연결은 과부하

pgBouncer — 연결 풀링의 표준

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
pool_mode = transaction
default_pool_size = 20
max_client_conn = 1000

pool_mode:

• session — 클라이언트 연결 = 서버 연결 (1:1, 풀 의미 없음)
• transaction — 트랜잭션 동안만 유지 (가장 흔함)
• statement — 쿼리 하나당 (매우 제한적)

Transaction Mode 주의사항

• Prepared Statement 비호환 — PG 17+ 에서 track_planner_stats 개선
• SET LOCAL 만 허용, SET은 안 됨
• LISTEN/NOTIFY 불가
• 애플리케이션 드라이버 설정 필요 (예: PreparedStatementCacheSize=0)

PgCat & Supavisor

• PgCat — Rust로 쓴 pgBouncer 대안, 샤딩 지원
• Supavisor — Supabase가 만든 Elixir 기반, 수백만 연결

경험칙

• 풀 크기 = core_count * 2 + spindle_count (~PostgreSQL 위키)
• 보통 20-50 정도가 스위트 스팟
• 더 늘려도 throughput 안 늘고 latency만 증가

8. JSONB — "Postgres로 다 된다"의 진실

JSON vs JSONB

거의 항상 JSONB를 쓴다.

GIN 인덱스 전략

-- 범용 (크고 유연)
CREATE INDEX ON docs USING GIN (data);

-- 경로 연산자 (`@>`)만 (더 작음)
CREATE INDEX ON docs USING GIN (data jsonb_path_ops);

jsonb_path_ops가 30% 가량 작고 @> 질의에 충분. 다른 연산자(?, ?|, ?&)는 범용만 지원.

MongoDB 대체 가능한가?

• 스키마 유연성: JSONB가 근접
• 성능: 작은 데이터는 유사, 거대 문서는 MongoDB 우위
• 집계: Postgres의 SQL이 훨씬 강력
• Transaction: Postgres 승 (Mongo 4.0+로 따라왔지만 제한)
• 쓰기 확장: MongoDB의 샤딩이 더 성숙 (Citus 있지만)

결론: 규모가 극단적이지 않으면 Postgres로 "문서 + 관계 + 분석"을 한 DB에서 해결 가능.

9. AI 시대의 Postgres — pgvector와 pg_duckdb

pgvector (2023년 이후 폭발)

• 2021년 Andrew Kane가 만듦
• 2023년 HNSW 추가로 프로덕션 가능
• 2024년 pgvectorscale(Timescale)로 성능 10배 개선
• 2025년에는 Supabase, Neon, RDS 모두 기본 탑재

DiskANN & Binary Quantization

• DiskANN 인덱스(pgvectorscale) — 메모리보다 큰 벡터셋을 디스크에서 검색
• Binary Quantization — float32 → 1bit, 메모리 32배 감소

pg_duckdb (2024년 말)

DuckDB를 PostgreSQL 내부에 임베드. 분석 쿼리만 DuckDB로 넘김.

SELECT duckdb.query('
  SELECT date_trunc(''hour'', ts), count(*) 
  FROM read_parquet(''s3://logs/*.parquet'')
  GROUP BY 1
');

OLTP는 Postgres, OLAP은 DuckDB — 한 DB에서. 2025년 HTAP 전쟁의 다크호스.

AI 네이티브 PG 생태

• Neon — 서버리스, 브랜칭, copy-on-write
• Supabase — Postgres + pgvector + Realtime + Auth 번들
• Timescale — 시계열 + pgvector + AI 최적화
• MotherDuck + DuckDB — OLAP 분리

10. PostgreSQL 18 (2025) 신기능

AIO — Asynchronous I/O

• 지금까지는 동기 I/O 기반
• 18부터 io_uring(Linux) 지원 → 30-50% 빠른 순차 스캔
• 자동 read-ahead 개선

Direct I/O

• OS 페이지 캐시 우회, PG가 직접 관리 (shared_buffers 더 크게)
• io_direct = data 옵션

UUIDv7 네이티브 지원

• 기존 UUIDv4는 랜덤 → 인덱스 파편화
• UUIDv7은 시간 정렬 → B-Tree 친화적
• gen_uuid_v7() 빌트인

Logical Replication 개선

• DDL 복제 (테이블 생성까지 복제)
• 양방향 복제 기반 마련 (BDR-like)

Skip Scan

• 다중 컬럼 인덱스에서 앞 컬럼 조건 없어도 활용
• "Loose Index Scan" — Oracle/MySQL에는 있었던 기능

11. 모니터링 — 꼭 봐야 할 지표

pg_stat_statements

모든 쿼리의 누적 통계. 가장 중요한 익스텐션.

SELECT query, calls, total_exec_time/1000 AS total_sec, 
       mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 20;

핵심 지표

Long Running Transaction의 저주

오래 열린 트랜잭션은:

• VACUUM을 막음 (그 TX가 볼 수 있는 tuple은 청소 불가)
• dead tuple 폭증 → 테이블 부풀음
• idle_in_transaction_session_timeout 설정으로 방어

12. 안티패턴 TOP 10

1. ORM의 N+1 쿼리 — EXPLAIN 보고 놀라지 말고 INCLUDE/JOIN
2. BEGIN 후 아이들 — 장시간 방치, VACUUM 블록
3. 많은 짧은 연결 — pgBouncer 없이 직결
4. ANALYZE 안 함 — 통계 낡으면 Planner 헛발
5. VACUUM FULL을 운영 중 — AccessExclusive 락, 서비스 중단
6. 모든 컬럼 인덱싱 — 쓰기 속도 추락, 인덱스 유지비 ↑
7. Prepared Statement + pgBouncer transaction mode — 비호환
8. UUIDv4 PK 남용 — 인덱스 파편화 (→ UUIDv7)
9. SELECT * — Planner 최적화 기회 상실
10. 거대 JSONB 전체 UPDATE — TOAST 재작성 폭발

13. Postgres를 현명하게 쓰는 체크리스트

• autovacuum 튜닝 — scale_factor, cost_limit 조정
• pg_stat_statements 상시 활성화
• ANALYZE 주기 확인, 통계 타겟 조정 가능
• pgBouncer(transaction mode) 배포
• random_page_cost SSD 기준으로 조정
• work_mem 신중히 상향 (연결당 곱해짐)
• shared_buffers RAM의 25%
• Replication lag 모니터링 — slot 쌓이는 것 확인
• Long-running TX 타임아웃 설정
• pg_stat_user_tables dead_pct 알람
• WAL 보관/아카이빙 — PITR 가능하게
• 주요 쿼리 EXPLAIN ANALYZE 베이스라인 저장

마치며 — "Postgres로 다 된다"의 참뜻

"Postgres everything" 밈은 사실이다. OLTP, OLAP(pg_duckdb), 벡터 검색(pgvector), 시계열(Timescale), 지리정보(PostGIS), 그래프(Apache AGE), 전문 검색(GIN), 심지어 메시지 큐(pg_later)까지.

하지만 이것은 "아무 워크로드나 Postgres에 밀어넣어라"는 뜻이 아니다. **"한 DB로 대부분의 일을 할 수 있어서, 운영 복잡성을 줄일 수 있다"**는 뜻이다. 극한 규모(수십 TB OLAP, 초당 100만 이벤트)에서는 전용 시스템이 여전히 필요하다.

Postgres를 다룬다는 건 MVCC의 tuple 모델, VACUUM의 비용, WAL의 역할, Planner의 통계 의존성을 이해하는 것이다. 이걸 모르고 "왜 느려졌지?"를 디버그하는 건 지도 없이 동굴 탐험하는 일이다.

다음 글 예고 — Elasticsearch/OpenSearch와 검색의 과학

Postgres가 "구조화된 세계의 왕"이라면, Elasticsearch/OpenSearch는 "비구조화된 텍스트의 왕"이다. 다음 글에서는:

• Inverted Index의 본질 — 왜 GIN보다 Lucene이 빠른가
• Lucene 내부 — Segment, Commit, Merge 정책
• BM25 vs TF-IDF — 점수 매기기의 수학
• Sharding & Replication — primary/replica, routing
• Ingest 파이프라인 — Logstash, Beats, OpenTelemetry → ES
• Query DSL의 숲 — bool, match, term, function_score
• Vector Search in ES — kNN, HNSW, hybrid retrieval
• 2021년 라이선스 사태 — Elastic vs AWS, OpenSearch 포크
• Operational pain — Heap, GC, split brain, circuit breakers
• Hybrid Search & RAG — BM25 + Vector = 답

검색의 역사와 미래를 한 번에 훑는 여정.

"The first rule of Postgres: never fight the Planner. Give it good statistics, and it will give you good plans. The second rule: know where the bodies are buried — MVCC, VACUUM, WAL. Everything else is commentary." — Bruce Momjian (PostgreSQL Global Development Group)