✍️ 필사 모드: PostgreSQL 내부 구조 완벽 가이드 — MVCC, WAL, Vacuum, Heap, B-tree, Query Planner 모든 것 (2025)

들어가며 — 왜 또 Postgres인가

백엔드 엔지니어라면 누구나 PostgreSQL을 쓴다. SELECT를 짜고, 인덱스를 만들고, EXPLAIN을 읽고, 가끔 autovacuum이 밀릴 때 한숨을 쉰다. 하지만 대부분은 그 밑에서 무슨 일이 일어나는지 모른다. 왜 UPDATE가 새 row를 만드는가? 왜 long-running transaction 하나가 디스크를 채우는가? 왜 autovacuum이 필요한가? 왜 index-only scan이 항상 가능하지는 않은가?

이 질문들은 전부 하나의 단어로 수렴한다: MVCC. PostgreSQL의 모든 설계 결정은 Multi-Version Concurrency Control에서 파생되며, 이 모델을 이해하면 Postgres의 모든 "이상한 동작"이 논리적으로 설명된다.

이 글은 Postgres의 모든 내부 메커니즘을 1,500줄에 걸쳐 다룬다. 프로세스 아키텍처, 페이지 레이아웃, tuple 헤더의 바이트 구조, xmin/xmax 가시성 규칙, WAL의 LSN과 체크포인트, Vacuum의 Visibility Map과 freeze, B-tree의 leaf page 구조, GIN/GiST/BRIN의 사용처, Query Planner의 cost 모델, shared_buffers와 페이지 캐시, streaming/logical replication까지. 각 절은 독립적이므로 필요한 부분만 읽어도 된다.

이 글은 DuckDB 내부 구조 글과 짝을 이룬다. DuckDB가 OLAP의 내부를 보여주었다면, 이 글은 OLTP의 내부를 보여준다. 같은 "database"라는 단어가 얼마나 다른 시스템을 가리킬 수 있는지 느껴보자.

1. PostgreSQL의 프로세스 아키텍처

1.1 왜 프로세스인가, 왜 스레드가 아닌가

MySQL은 스레드 기반이다. 연결마다 스레드 하나. PostgreSQL은 프로세스 기반이다. 연결마다 프로세스 하나. 이 결정은 1980년대 초 Berkeley Postgres 시절에 내려졌고, 지금까지 유지되고 있다.

왜 프로세스인가? 역사적으로는 스레드가 모든 OS에 잘 지원되지 않던 시절의 선택이었다. 현실적으로는 **격리(isolation)**가 핵심이다. 한 백엔드가 segfault로 죽어도 다른 백엔드는 영향받지 않는다. 메모리 관리 버그가 전파되지 않는다. PostgreSQL의 안정성 명성은 이 구조에 많이 빚지고 있다.

단점도 명확하다. 프로세스 fork는 스레드 생성보다 무겁다. 그래서 수만 개의 동시 연결이 있으면 PgBouncer 같은 커넥션 풀러가 거의 필수다.

1.2 프로세스 가족

Postgres 서버를 시작하면 여러 프로세스가 뜬다.

• postmaster (postgres main process): 최상위 부모. 연결을 받아 fork하고 자식 프로세스를 관리.
• backend: 클라이언트 연결마다 하나. SQL 파싱, 플래닝, 실행을 담당.
• background worker: CREATE EXTENSION 등으로 등록된 사용자 정의 백그라운드 작업자.
• autovacuum launcher + workers: autovacuum 오케스트레이션.
• checkpointer: 주기적으로 dirty page를 디스크에 flush.
• background writer (bgwriter): shared_buffers의 dirty page를 OS 페이지 캐시로 천천히 내보냄.
• walwriter: WAL 버퍼를 디스크에 flush.
• logical replication launcher + workers: logical replication 처리.
• stats collector (또는 Postgres 15+의 stat shared memory): 통계 수집.

다음 명령으로 한눈에 볼 수 있다 (Postgres가 뜬 상태에서):

ps -ef | grep postgres | grep -v grep

출력 예시:

postgres  1234  1234  postgres: checkpointer
postgres  1235  1234  postgres: background writer
postgres  1236  1234  postgres: walwriter
postgres  1237  1234  postgres: autovacuum launcher
postgres  1238  1234  postgres: logical replication launcher
postgres  2000  1234  postgres: myapp myuser 10.0.0.5(54321) idle

마지막 줄이 사용자 연결의 backend 프로세스다. postgres: 뒤에 DB 이름, 사용자, 클라이언트 IP/포트, 상태가 보인다. 운영 중 idle in transaction 같은 문자열이 보이면 long-running tx다 — 의심해야 한다.

1.3 공유 메모리 (shared memory)

프로세스들이 데이터를 어떻게 공유할까? POSIX shared memory다. postmaster 시작 시 큰 공유 메모리 영역을 할당하고, fork된 자식들이 이 메모리를 매핑한다.

공유 메모리에는 이런 것들이 들어간다:

• shared_buffers: 페이지 캐시 (기본 128MB, 프로덕션에서는 RAM의 25%).
• WAL buffers: 아직 디스크에 flush되지 않은 WAL 레코드 (기본 -1 → shared_buffers의 1/32).
• CLOG (Commit Log): 각 트랜잭션의 상태 (COMMITTED / ABORTED / IN_PROGRESS).
• Lock table: 테이블/row 잠금.
• Procarray: 현재 활성 트랜잭션 목록.

공유 메모리 접근은 LWLock (Lightweight Lock) 으로 보호된다. LWLock은 세마포어처럼 동작하지만 더 가볍고 spin + sleep 전략을 섞어 쓴다. Postgres의 확장성 문제 대부분은 LWLock contention이다 — 특히 WALInsertLock, BufferContent, ProcArrayLock.

2. 페이지와 Heap Tuple — 바이트 수준에서

2.1 왜 페이지인가

Postgres는 데이터를 페이지(page) 단위로 관리한다. 페이지는 8KB (컴파일 타임 상수 BLCKSZ)다. 모든 디스크 I/O는 페이지 단위로 일어나고, shared_buffers도 페이지 단위로 캐시한다.

왜 8KB인가? 역사적 선택이다. 디스크 섹터(512B)의 배수면서, OS 페이지(4KB)의 배수면서, 한 페이지에 여러 row를 담을 만큼 충분히 크다. 다른 DB (Oracle: 8KB 기본, MySQL InnoDB: 16KB)도 비슷한 범위다.

2.2 페이지 레이아웃

8KB 페이지 하나의 구조는 다음과 같다:

+---------------------------------+  offset 0
| PageHeaderData (24 bytes)       |
+---------------------------------+
| ItemIdData[] (line pointers)    |  앞에서 자라남
| ...                             |
+- - - - - - - - - - - - - - - - -+
|                                 |
|         (free space)            |
|                                 |
+- - - - - - - - - - - - - - - - -+
| ...                             |
| HeapTupleData (actual tuples)   |  뒤에서 자라남
+---------------------------------+  offset 8192
| (optional) Special space        |
+---------------------------------+

핵심 아이디어: line pointer는 앞에서, tuple은 뒤에서 자란다. 가운데가 free space다. 이 구조의 이점은 tuple의 실제 위치가 바뀌어도 line pointer의 인덱스 (OffsetNumber)는 유지된다는 것이다. 그래서 외부에서 tuple을 참조할 때 (block_number, offset_number) 쌍 — 즉 ctid — 을 쓸 수 있다.

2.3 PageHeader

typedef struct PageHeaderData {
    PageXLogRecPtr pd_lsn;          // 이 페이지를 수정한 마지막 WAL의 LSN
    uint16         pd_checksum;     // 선택적 체크섬
    uint16         pd_flags;        // 페이지 플래그
    LocationIndex  pd_lower;        // free space 시작 offset
    LocationIndex  pd_upper;        // free space 끝 offset
    LocationIndex  pd_special;      // special space 시작
    uint16         pd_pagesize_version;
    TransactionId  pd_prune_xid;    // 이 페이지의 prune 후보 xid
    ItemIdData     pd_linp[];       // flexible array — line pointers
} PageHeaderData;

pd_lsn이 특히 흥미롭다. 페이지마다 "이 페이지가 마지막으로 수정된 WAL 위치"를 기록한다. 덕분에 crash recovery 시 "이 페이지는 이미 해당 WAL 이후 상태다" 같은 판단이 가능하다. 이는 WAL rule의 구현체다: WAL은 대응되는 데이터 페이지가 디스크에 쓰이기 전에 먼저 flush되어야 한다.

2.4 HeapTuple 헤더 — MVCC의 심장

이제 진짜 흥미로운 부분. tuple 하나의 구조는:

typedef struct HeapTupleHeaderData {
    union {
        HeapTupleFields  t_heap;    // MVCC 필드
        DatumTupleFields t_datum;
    } t_choice;

    ItemPointerData t_ctid;         // 다음 버전의 ctid (self 또는 new)
    uint16          t_infomask2;    // 속성 수 + 플래그
    uint16          t_infomask;     // 플래그 비트
    uint8           t_hoff;         // 사용자 데이터 시작 offset
    bits8           t_bits[];       // NULL bitmap
} HeapTupleHeaderData;

typedef struct HeapTupleFields {
    TransactionId t_xmin;           // 이 tuple을 만든 tx
    TransactionId t_xmax;           // 이 tuple을 삭제/업데이트한 tx
    union {
        CommandId  t_cid;           // cmin/cmax
        TransactionId t_xvac;       // old-style VACUUM FULL에서 사용
    } t_field3;
} HeapTupleFields;

xmin, xmax, ctid, t_infomask. 이 네 개가 MVCC의 전부다. 다음 절에서 하나하나 파헤친다.

3. MVCC — Postgres가 세상을 보는 방식

3.1 근본 아이디어

MVCC (Multi-Version Concurrency Control)의 근본 명제: UPDATE는 row를 변경하지 않는다. 새 버전을 만든다.

예를 보자. users 테이블에 id=1, name='Alice'가 있다. 누군가 UPDATE users SET name='Bob' WHERE id=1을 실행한다. 무슨 일이 벌어지는가?

1. 기존 tuple (Alice)의 xmax에 현재 tx id를 쓴다. "나는 tx N에서 삭제됨."
2. 같은 페이지 (또는 다른 페이지)에 새 tuple (Bob)을 만든다. xmin = N.
3. 기존 tuple의 ctid를 새 tuple 위치로 업데이트.

이제 페이지에는 두 버전의 row가 존재한다. 어느 것을 봐야 하는가? 그건 읽는 트랜잭션의 스냅샷에 따라 다르다.

3.2 스냅샷 (Snapshot)

스냅샷은 "이 트랜잭션이 볼 수 있는 세계"다. 구체적으로는 네 개의 값:

• xmin (snapshot xmin): 이 tx보다 작은 (더 오래된) 모든 커밋된 tx는 가시성 보장.
• xmax (snapshot xmax): 이 tx보다 크거나 같은 tx는 불가시.
• xip_list: xmin과 xmax 사이에서 아직 실행 중인 tx 목록 (Procarray에서 긁어옴).
• my xid: 자신의 tx id.

가시성 규칙은 대략 이렇다 (HeapTupleSatisfiesMVCC를 요약):

tuple의 xmin이 커밋됐는가?
  NO → tuple은 보이지 않는다 (aborted or in-progress).
  YES:
    tuple의 xmin이 내 스냅샷의 xmin보다 작은가?
      YES → 볼 수 있음.
      NO:
        tuple의 xmin이 내 스냅샷의 xmax보다 크거나 같은가?
          YES → 볼 수 없음.
          NO → xip_list에 xmin이 있으면 보지 못함, 없으면 봄.

보인다면:
  tuple의 xmax가 없거나 aborted면 → 유효.
  tuple의 xmax가 커밋됐고 내 스냅샷 기준으로 보이면 → 이미 삭제됨, 안 보임.
  tuple의 xmax가 내 스냅샷 기준 불가시면 → 아직 유효.

이 검사는 src/backend/utils/time/snapmgr.c와 src/backend/access/heap/heapam_visibility.c에 있다. Postgres 소스 코드에서 가장 자주 실행되는 함수 중 하나다.

3.3 커밋 로그 (CLOG)

"tx가 커밋됐는가?"를 어떻게 아는가? CLOG (Commit Log) 를 본다. 각 tx id마다 2비트:

• 00: IN_PROGRESS
• 01: COMMITTED
• 10: ABORTED
• 11: SUB_COMMITTED (서브트랜잭션 커밋 대기)

4억 개의 tx마다 1GB의 CLOG가 필요하다. 실제로는 최근 것만 디스크에 있고 오래된 것은 freeze 후 삭제된다 (아래 Vacuum 절 참조).

3.4 격리 수준 (Isolation Level)

Postgres가 지원하는 격리 수준은:

• Read Uncommitted: 실제로는 Read Committed와 동일.
• Read Committed (기본): 각 문장마다 새 스냅샷.
• Repeatable Read: 트랜잭션 시작 시 하나의 스냅샷을 고정. Postgres의 Repeatable Read는 사실 Snapshot Isolation이다.
• Serializable: SSI (Serializable Snapshot Isolation) — 2PL 없이 직렬화 가능성 보장.

대부분 앱은 Read Committed로 충분하지만, 재무/회계같은 곳은 Serializable을 권한다. SSI는 read/write dependency를 추적하여 rw-antidependency cycle이 생기면 tx 하나를 abort한다.

3.5 긴 트랜잭션의 재앙

이제 왜 "long-running transaction이 재앙"인지 보인다. 긴 tx는 오래된 xmin을 보유한다. Postgres는 "이 xmin보다 오래된 tuple 버전은 아무도 안 본다"라고 판단해야 Vacuum이 가능하다. 긴 tx는 이 판단을 막는다. 그래서 dead tuple이 쌓이고, 테이블이 부풀고, 인덱스가 비효율적이 된다.

실제로 Postgres 운영자가 가장 자주 외치는 말: "누가 idle in transaction을 남겼는가?"

SELECT pid, age(backend_xid), state, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY backend_xid;

4. WAL — Write-Ahead Log

4.1 WAL의 약속

WAL rule: 데이터 페이지를 디스크에 쓰기 전에, 그 변경을 설명하는 WAL 레코드를 먼저 디스크에 써야 한다.

이 간단한 규칙이 ACID의 D (Durability)를 보장한다. 크래시가 나도 WAL을 다시 읽어 데이터를 재구성할 수 있다.

WAL 없으면 이런 일이 벌어진다. UPDATE가 페이지 두 개를 수정한다 (원래 페이지 + 인덱스 페이지). 첫 페이지는 디스크에 갔고 두 번째는 아직 buffer에 있다. 이 때 크래시. 디스크는 half-updated 상태. 데이터 무결성 파탄.

4.2 LSN (Log Sequence Number)

WAL의 모든 레코드는 LSN으로 주소 지정된다. LSN은 단조 증가하는 64비트 값이고, WAL 파일 내의 바이트 오프셋으로 해석된다.

WAL 파일은 기본 16MB 세그먼트로 잘려 pg_wal/ 디렉토리에 저장된다. 파일 이름은 000000010000000000000001 같은 hex 문자열 — timeline + LSN 상위 비트.

LSN은 Postgres 내부의 여러 곳에서 등장한다:

• 페이지 헤더의 pd_lsn (이 페이지를 수정한 마지막 WAL)
• replication slot의 restart_lsn (replica가 소비 중인 LSN)
• pg_current_wal_lsn() 함수
• backup의 시작/끝 표시

SELECT pg_current_wal_lsn();  -- e.g. 0/1A3F2B8

4.3 WAL 레코드의 구조

각 WAL 레코드는 헤더 + 페이로드:

+--------------------------------+
| XLogRecord header              |
|   - xl_tot_len (총 길이)       |
|   - xl_xid (트랜잭션 id)       |
|   - xl_prev (이전 레코드 LSN)  |
|   - xl_info (op code)          |
|   - xl_rmid (resource manager) |
|   - xl_crc (체크섬)            |
+--------------------------------+
| block refs (수정된 페이지 참조)|
+--------------------------------+
| rmgr-specific data             |
+--------------------------------+

rmgr (Resource Manager) 개념이 핵심이다. WAL은 통합 로그지만, 각 레코드는 "어느 서브시스템이 해석하는가"에 따라 분류된다. RM_HEAP_ID, RM_BTREE_ID, RM_XLOG_ID, RM_STANDBY_ID 등. 각 rmgr은 redo 함수를 제공해 crash recovery 시 호출된다.

pg_waldump 명령으로 실제 WAL을 볼 수 있다:

rmgr: Heap  len: 54  tx: 742  lsn: 0/1A3F2B8  desc: INSERT off 3
rmgr: Btree len: 72  tx: 742  lsn: 0/1A3F2F0  desc: INSERT_LEAF off 5
rmgr: Transaction len: 34  tx: 742  lsn: 0/1A3F340  desc: COMMIT 2026-04-15 ...

이 세 줄은 "tx 742가 heap에 INSERT하고, btree 인덱스에 INSERT_LEAF하고, COMMIT했다"를 의미한다.

4.4 Full-Page Writes

torn page 문제: OS/디스크가 페이지를 원자적으로 쓰지 못한다. 8KB 페이지를 쓰는 도중 전원이 나가면 앞 4KB는 새 내용, 뒤 4KB는 옛 내용이 될 수 있다.

해결책: 체크포인트 이후 페이지가 처음 수정될 때 full-page image를 WAL에 넣는다. redo 시 이 전체 이미지로 덮어쓰면 된다. 약간 비싸지만 안전하다.

full_page_writes = on (기본값)이다. 끄면 안 된다. 단, 파일시스템이 atomic write를 보장하는 ZFS 같은 경우는 예외.

4.5 체크포인트 (Checkpoint)

WAL만 있으면 복구가 가능하다. 그런데 왜 데이터 페이지를 flush하는가? 복구 시간을 짧게 하기 위해서다. WAL이 100GB이면 복구에 오래 걸린다. 체크포인트는 "이 LSN까지의 모든 변경은 데이터 파일에 반영됐음"을 보장한다. 복구는 그 LSN부터 시작하면 된다.

체크포인트 동안:

1. 현재 redo pointer (체크포인트 시작 시의 LSN)를 기록.
2. shared_buffers의 모든 dirty 페이지를 OS로 내보냄.
3. fsync로 OS 캐시를 flush.
4. pg_control 파일에 체크포인트 LSN 기록.
5. redo pointer 이전의 WAL 파일은 재활용 가능.

체크포인트는 I/O 폭주를 유발한다. 그래서 checkpoint_completion_target = 0.9 (기본 0.9)로 체크포인트 시간을 다음 체크포인트까지의 90%로 늘려 I/O를 분산시킨다.

관련 파라미터:

checkpoint_timeout = 5min       # 시간 기반 트리거
max_wal_size = 1GB              # 크기 기반 트리거
min_wal_size = 80MB             # WAL 재활용 하한
checkpoint_completion_target = 0.9

프로덕션에서는 max_wal_size를 10GB–100GB 수준으로 키워 체크포인트 빈도를 낮추는 게 일반적이다.

5. Vacuum — 그림자 속의 정원사

5.1 왜 Vacuum이 필요한가

MVCC 때문에 dead tuple이 쌓인다. UPDATE 한 번 → dead tuple 하나. 10만 번 업데이트된 테이블은 dead tuple이 가득 차 있다. Vacuum은 이 dead tuple을 청소한다.

더 중요한 일도 한다: xid wraparound 방지.

5.2 XID Wraparound — 32비트의 저주

PostgreSQL의 tx id는 32비트다. 약 42억. 초당 1천 개 tx 실행하면 약 50일에 소진. 이후 어떻게 되는가?

Wraparound. xid가 0으로 돌아간다. 그런데 "오래된 tx인지 새 tx인지" 비교는 modular arithmetic으로 한다 — 과거와 미래를 각각 21억 개의 윈도우로 나눈다. Wraparound가 일어나면 "먼 과거의 커밋된 tx"가 갑자기 "먼 미래의 미래 tx"로 보인다. 데이터 대참사.

방지책: freeze. 충분히 오래된 tuple의 xmin을 FrozenTransactionId (2)로 교체한다. 이 특수 값은 "모든 현재 tx에게 보임"을 의미한다. xid 값 자체가 비교에서 제외된다.

Vacuum이 freeze를 담당한다. autovacuum_freeze_max_age = 200_000_000 (2억). 한 테이블에서 가장 오래된 xid와 현재 xid의 차이가 이 값을 넘으면, 어떤 상황에서도 aggressive vacuum이 시작된다 — 세상의 누구도 이 vacuum을 막을 수 없다.

이 wraparound autovacuum이 발동하면 시스템이 느려지고, 심한 경우 wraparound를 막지 못하면 Postgres는 스스로 READ ONLY 모드로 들어간다. 이 상황이 벌어지면 다음 명령을 본다:

WARNING: database "foo" must be vacuumed within 10000000 transactions
HINT: To avoid a database shutdown, execute a database-wide VACUUM in that database.

5.3 Visibility Map

Vacuum이 매번 모든 페이지를 읽을 수는 없다. Visibility Map (VM) 은 각 페이지마다 2비트를 관리한다:

• ALL_VISIBLE: 이 페이지의 모든 tuple이 모든 현재 tx에게 보임 (dead tuple 없음).
• ALL_FROZEN: 모든 tuple이 frozen 상태.

ALL_VISIBLE 페이지는 Vacuum이 건너뛴다. 또한 index-only scan에도 사용된다 — 인덱스에서 찾은 tuple이 visible인지 확인하려면 원래 heap을 봐야 하는데, VM이 ALL_VISIBLE이면 생략 가능.

VM 파일은 테이블당 <oid>_vm으로 저장된다. 테이블의 0.025% 크기 정도다.

5.4 HOT (Heap-Only Tuple) Update

UPDATE는 비싸다. 새 heap tuple을 만들고, 모든 인덱스에 새 entry를 추가해야 한다. 10개 인덱스가 있는 테이블은 UPDATE 한 번에 11번의 쓰기.

HOT update는 최적화다. 조건:

1. 업데이트가 인덱스 컬럼을 건드리지 않음.
2. 같은 페이지에 새 tuple을 담을 공간이 있음.

이 조건이 맞으면:

• 새 tuple을 같은 페이지에 만든다.
• 인덱스를 업데이트하지 않는다.
• 기존 tuple의 ctid가 새 tuple을 가리킨다. 인덱스는 옛 tuple을 계속 가리키지만, 페이지 내 ctid 체인을 따라 새 tuple에 도달.

HOT chain이 길어지면 성능이 떨어진다. Vacuum은 HOT chain을 끊고 dead tuple을 제거하는 prune을 같이 수행한다. prune은 full vacuum보다 가볍다 — autovacuum이 도달하기 전에 정상 쿼리 경로에서도 수행된다.

SELECT schemaname, relname, n_tup_upd, n_tup_hot_upd,
  round(n_tup_hot_upd::numeric / NULLIF(n_tup_upd, 0) * 100, 2) AS hot_pct
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC LIMIT 10;

HOT 비율이 50% 이상이면 건강하다. 10% 미만이면 UPDATE가 인덱스 컬럼을 건드리거나 페이지 fill factor 튜닝이 필요하다는 신호.

5.5 Autovacuum 튜닝

Autovacuum은 통계 기반으로 트리거된다. 테이블의 dead tuple 비율이 threshold를 넘으면 실행.

autovacuum_vacuum_threshold = 50          # 최소 row 수
autovacuum_vacuum_scale_factor = 0.2      # 테이블 크기의 20%

# trigger = threshold + scale_factor * reltuples

100만 row 테이블은 20만 dead tuple이 쌓여야 vacuum. 큰 테이블에서는 너무 늦다. 프로덕션에서는:

ALTER TABLE large_table SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.02
);

이런 식으로 특정 테이블만 더 공격적으로 설정한다.

또 하나 중요한 파라미터: autovacuum_vacuum_cost_limit. autovacuum이 I/O를 얼마나 쓸 수 있는지. 기본 200. SSD 환경에서는 2000–10000까지 올려도 문제없다.

6. B-tree 인덱스 — 가장 흔한 자료구조

6.1 왜 B-tree인가

디스크 기반 인덱스에서 B-tree (정확히는 Postgres의 B+tree 변형)가 지배적인 이유:

1. 높이가 낮다: 수십억 row도 3–4 레벨.
2. 범위 스캔 친화적: leaf가 linked list.
3. 순차/역순 모두 효율적.
4. 동시성 친화적: Lehman-Yao 알고리즘으로 높은 동시성.

6.2 B-tree 페이지 구조

PostgreSQL B-tree 페이지에는 두 종류:

• internal page: 자식 페이지 포인터만.
• leaf page: 실제 key + ctid (heap tuple 참조).

leaf page의 entry 구조:

+------------------+
| IndexTupleData   |
|   - t_tid (ctid) |
|   - t_info       |
+------------------+
| key data         |
+------------------+

t_tid가 heap tuple의 (block, offset)을 가리킨다. 인덱스 스캔은 이 ctid를 따라 heap으로 간다. 이 단계를 heap fetch라고 부른다.

6.3 Lehman-Yao B-link tree

Postgres의 B-tree는 전통적인 B-tree와 다르다. 각 페이지에 right-link 포인터가 있다. 왜?

동시성 때문이다. 한 스레드가 페이지 P를 읽고 있는데 다른 스레드가 P를 분할한다면? 읽던 스레드가 찾고 있던 key가 새로 생긴 페이지 P'로 이동했을 수 있다. Lehman-Yao는 P에 "P'로 가는 링크"를 남긴다. 읽던 스레드는 key가 자기 범위를 벗어나는 걸 감지하면 right-link를 따라가면 된다.

덕분에 Postgres B-tree는 lock을 매우 짧게만 잡는다 — 한 번에 한두 페이지. 고동시성에서도 잘 확장된다.

6.4 Duplicate Key와 Bloat

MVCC 때문에 dead tuple은 인덱스에도 남는다. 동일 key가 여러 번 나타날 수 있다. 이것이 index bloat의 원인.

Postgres 12부터 deduplication 기능이 추가됐다. 같은 key의 여러 TID를 하나의 posting list로 묶어 공간 절약. B-tree가 GIN처럼 동작한다.

-- Postgres 12+에서 기본 활성화
CREATE INDEX idx_name ON t (col) WITH (deduplicate_items = on);

중복이 많은 컬럼 (예: 성별, 상태 플래그)에서 인덱스 크기가 2–10배 줄어든다.

6.5 Index Bloat 진단

-- pgstattuple extension 필요
SELECT * FROM pgstatindex('idx_name');

avg_leaf_density가 50% 미만이면 bloat 심각. 해법은 REINDEX CONCURRENTLY:

REINDEX INDEX CONCURRENTLY idx_name;

Postgres 12+에서 concurrently 옵션이 추가됐다. 쓰기를 막지 않고 인덱스 재구축.

7. GIN, GiST, BRIN, Hash — 특수 인덱스들

7.1 GIN (Generalized Inverted Index)

역색인. 배열, JSONB, 전문 검색(tsvector)에 쓴다. 각 요소가 key, 그 요소를 포함한 row들이 value.

CREATE INDEX idx_tags ON articles USING GIN (tags);
-- tags는 text[] 타입

SELECT * FROM articles WHERE tags @> ARRAY['postgres', 'mvcc'];

GIN의 내부는 "main tree (key 검색용 B-tree) + 각 key 아래의 posting tree (ctid 목록)". posting tree도 B-tree.

GIN은 삽입이 느리다 (각 요소가 따로 인덱싱되므로). 해결책: fastupdate. 새 entry를 일단 pending list에 쌓고, 주기적으로 main index에 병합.

7.2 GiST (Generalized Search Tree)

다차원/공간 인덱스. PostGIS의 geometry, 범위 타입 (int4range, tsrange), 전문 검색도 지원.

GiST는 프레임워크다. 사용자가 consistent, union, compress, penalty, picksplit 같은 메서드를 정의하면 인덱스 구조는 자동 생성. 덕분에 임의의 데이터 타입에 인덱스 붙일 수 있다.

CREATE INDEX idx_geom ON places USING GIST (geom);
-- geom는 PostGIS geometry

SELECT * FROM places
WHERE ST_DWithin(geom, 'POINT(-74 40)', 1000);

7.3 BRIN (Block Range Index)

블록 범위 인덱스. 각 블록 범위 (기본 128블록 = 1MB)의 요약 (min/max 등)을 저장.

초대형 테이블에서 물리적 순서가 데이터 값과 상관관계가 있을 때 매우 효과적. 로그 테이블, 시계열 데이터가 전형.

CREATE INDEX idx_created ON events USING BRIN (created_at);

10억 row 테이블에 B-tree는 수십GB, BRIN은 수MB. 단, 정확한 검색은 못 한다 — 후보 블록을 좁힐 뿐, 여전히 블록 내 scan 필요.

7.4 Hash

Postgres 10 이전에는 WAL 지원 없어 사용 권장하지 않았다. 10부터 WAL 지원, 사용 가능. 단 B-tree 대비 이점이 크지 않아 거의 안 쓰인다.

7.5 어떤 인덱스를 언제

8. Query Planner — SQL의 번역가

8.1 Planner 파이프라인

SQL text
  ↓ Lexer/Parser
raw parse tree
  ↓ Analyzer (rewriter 포함)
Query (resolved references)
  ↓ Planner
Plan tree (Path → Plan)
  ↓ Executor
결과

8.2 비용 기반 최적화

Postgres는 cost-based optimizer다. 각 plan마다 cost를 추정하고 최소 cost를 선택.

cost는 두 숫자: (startup_cost, total_cost). 단위는 "디스크 페이지 하나 순차 읽기"다. 기본 상수들:

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 정도로 낮춘다. 기본값이 HDD를 가정한 것.

8.3 통계 (Statistics)

planner는 통계에 의존한다:

• reltuples: 테이블의 row 수 추정.
• relpages: 페이지 수.
• n_distinct: 컬럼의 distinct 값 수.
• histogram: 값 분포.
• most common values (MCV): 자주 나오는 값과 빈도.
• correlation: 물리적 순서와 논리적 순서의 상관.

ANALYZE 명령이 이 통계를 갱신한다. autoanalyze가 주기적으로 실행한다 (기본: row 10% 변경 시).

통계가 낡으면 plan이 최악이 된다. 운영 중 갑자기 느려진 쿼리가 있으면 우선 의심할 것:

SELECT relname, n_live_tup, n_dead_tup, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'your_table';

8.4 Join Algorithms

• Nested Loop: 외부 루프의 각 row마다 내부 테이블 스캔. 작은 집합에 적합.
• Hash Join: 한 쪽으로 hash table 만들고 다른 쪽 scan하며 probe. 중간 크기 조인에 최적.
• Merge Join: 양쪽 정렬된 상태에서 merge. 이미 정렬됐거나 정렬 비용이 싸면 선택.

planner는 이 셋을 추정 비용으로 비교한다. EXPLAIN의 Nested Loop, Hash Join, Merge Join이 그것.

8.5 Genetic Query Optimizer (GEQO)

12개 이상 테이블 조인하면 plan 조합이 폭발한다. exhaustive search 대신 유전 알고리즘으로 준최적해 찾음.

geqo_threshold = 12    # 몇 개 relation부터 GEQO 사용
geqo_effort = 5        # 1-10, 높을수록 품질 vs 시간

OLTP에서는 거의 건드릴 필요 없다. 복잡한 BI 쿼리에서는 geqo_effort를 올리거나 join_collapse_limit를 조정.

8.6 EXPLAIN 읽는 법

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, FORMAT TEXT)
SELECT u.name, COUNT(o.id)
FROM users u LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2026-01-01'
GROUP BY u.name;

출력을 읽을 때 볼 것:

1. rows 예상 vs 실제: 큰 차이가 나면 통계 문제.
2. loops: nested loop 깊은 곳에서 loops가 크면 재앙.
3. Buffers: shared hit vs read. read가 많으면 cache miss — 워밍업 필요하거나 메모리 부족.
4. Filter 아래의 Rows Removed: 인덱스가 못 잡아준 row 수. 너무 크면 인덱스 후보.
5. Heap Fetches (index-only scan): VM이 ALL_VISIBLE이 아니면 이 값이 0이 아니다 → VACUUM 필요.

9. Shared Buffers와 OS 페이지 캐시 — 이중 캐싱

9.1 구조

Postgres는 자체 버퍼 캐시 (shared_buffers)를 관리한다. 동시에 Linux는 페이지 캐시를 관리한다. 같은 페이지가 양쪽에 있을 수 있다 — double buffering.

왜 이중인가? 다른 DB (Oracle, DB2)는 O_DIRECT로 OS 페이지 캐시를 우회한다. Postgres는 역사적 이유로 우회하지 않는다. "OS가 잘 관리하니 맡기자"라는 철학.

9.2 Buffer Pool 관리

shared_buffers는 고정 개수의 슬롯. 각 슬롯은 한 페이지를 담음. 페이지 교체는 Clock-Sweep 알고리즘:

• 각 버퍼에 usage count (0–5).
• 사용되면 증가. 단, 최대 5.
• 교체 대상 찾기: 포인터를 시계처럼 돌며 usage count를 감소. 0이 되면 evict.

pinned buffer 는 교체 대상에서 제외. 쿼리 실행 중 tuple을 가리키는 pin이 있다.

9.3 shared_buffers 얼마로?

통념: RAM의 25%. 극단은 추천하지 않는다:

• 너무 작음 → cache miss 많음, OS 페이지 캐시에 의존.
• 너무 큼 → double buffering 낭비, 체크포인트 시 flush 대폭.

RAM 64GB 서버면 shared_buffers = 16GB가 출발점.

9.4 효과 측정

SELECT
  sum(heap_blks_hit) AS hit,
  sum(heap_blks_read) AS read,
  round(sum(heap_blks_hit)::numeric / NULLIF(sum(heap_blks_hit + heap_blks_read), 0) * 100, 2) AS hit_ratio
FROM pg_statio_user_tables;

hit_ratio가 99% 미만이면 메모리 부족 신호. 단, 이 값은 OS 페이지 캐시를 포함하지 않는다 — 실제 디스크 I/O는 더 적을 수 있다.

9.5 pg_buffercache

CREATE EXTENSION pg_buffercache;

SELECT c.relname, count(*) AS buffers
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
GROUP BY c.relname
ORDER BY 2 DESC LIMIT 10;

어느 테이블이 버퍼를 얼마나 쓰는지 본다. hot 테이블을 찾는 데 유용.

10. Replication — 한 서버에서 여러 서버로

10.1 Streaming Replication

primary의 WAL을 replica가 네트워크로 받아 적용. 가장 일반적인 방식.

설정:

# postgresql.conf (primary)
wal_level = replica
max_wal_senders = 10

# pg_hba.conf
host replication replicator 10.0.0.0/24 md5

# replica 초기화
pg_basebackup -h primary -U replicator -D /var/lib/postgres/data -R -P

pg_basebackup 은 primary의 full copy + WAL을 뜬다. -R은 recovery 설정을 자동 생성. replica를 시작하면 primary에 붙어 WAL을 스트리밍한다.

10.2 Synchronous vs Asynchronous

• asynchronous (기본): primary가 커밋 → 즉시 응답. replica는 나중에 받음. 빠르지만 primary 크래시 시 데이터 유실 가능.
• synchronous: primary가 커밋 → replica의 receipt/flush/apply를 기다리고 응답. 느리지만 0 유실.

설정:

synchronous_commit = on
synchronous_standby_names = 'FIRST 1 (replica1, replica2)'

"replica1/2 중 가장 먼저 응답한 하나를 기다림". FIRST 2면 두 replica를 기다림.

synchronous_commit는 세션별로도 설정 가능. 중요한 트랜잭션만 synchronous로 할 수 있다.

10.3 Logical Replication

WAL 스트리밍이 physical이라면 logical은 논리적 변경 (INSERT/UPDATE/DELETE 문 단위)을 주고받는다. 같은 Postgres 버전이 아니어도 됨. 선택적 테이블 복제 가능.

-- primary
CREATE PUBLICATION mypub FOR TABLE users, orders;

-- replica (다른 DB)
CREATE SUBSCRIPTION mysub
CONNECTION 'host=primary user=repl password=...'
PUBLICATION mypub;

내부적으로는 logical decoding을 쓴다. WAL을 읽어 "INSERT into users(1, 'Alice')" 같은 논리적 표현으로 변환. Debezium 등 CDC 도구가 이걸 활용한다.

10.4 Replication Slot

replication slot은 primary가 "이 replica/consumer가 아직 못 읽은 WAL"을 기억하는 메커니즘. slot이 활성 상태인 동안은 primary가 그 WAL을 지우지 못한다.

양날의 검:

• 장점: replica가 잠깐 끊겨도 WAL 유실 없음.
• 단점: replica가 영원히 죽었는데 slot이 남아있으면 primary의 pg_wal/ 디렉토리가 무한 성장.

운영 중 pg_wal이 터질 듯 커지면 제일 먼저:

SELECT slot_name, active, pg_size_pretty(
  pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
) AS lag
FROM pg_replication_slots;

inactive인데 lag가 큰 slot이 있으면 pg_drop_replication_slot('slot_name') 후보.

10.5 Failover

primary 다운 시 replica를 승격:

SELECT pg_promote();

수동으로는 간단하지만, 자동화는 어렵다 — split-brain을 막으려면 quorum 기반 상태 판단이 필요. Patroni, repmgr, pg_auto_failover 같은 도구를 쓴다.

Patroni는 etcd/Consul에 리더십을 기록한다. primary가 주기적으로 갱신, 실패하면 다른 노드가 takeover. Kubernetes 위에 Postgres 올리면 거의 Patroni + CNPG 조합.

11. 파티셔닝 (Partitioning)

11.1 왜 파티셔닝

10억 row 테이블에서:

• 인덱스가 거대해져 캐싱 어려움.
• Vacuum이 영원히 걸림.
• 오래된 데이터 삭제가 DELETE로는 비현실적.

파티셔닝: 하나의 논리적 테이블을 여러 물리적 테이블로 쪼개기.

11.2 선언적 파티셔닝 (Postgres 10+)

CREATE TABLE events (
  id bigserial,
  user_id int,
  created_at timestamp NOT NULL,
  data jsonb
) PARTITION BY RANGE (created_at);

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

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

created_at에 WHERE 조건을 걸면 planner가 partition pruning — 관련 파티션만 스캔.

11.3 오래된 데이터 drop

월별 파티션이면:

DROP TABLE events_2025_04;  -- 1년 전 데이터 즉시 삭제

테라바이트 삭제를 초 단위로. DELETE로는 몇 시간씩 걸릴 작업.

11.4 주의점

• Primary key는 파티션 키를 포함해야 한다.
• Unique constraint는 파티션 단위로만 보장. 전역 unique는 별도 작업 필요.
• ALTER TABLE ATTACH PARTITION CONCURRENTLY (Postgres 14+)로 락 최소화.
• 파티션 개수가 많으면 planner overhead. 100개까지는 괜찮음, 1000개는 문제.

자동 파티션 관리는 pg_partman extension이 표준.

12. 실전 운영 체크리스트

12.1 부팅 시 설정

# postgresql.conf
shared_buffers = 16GB              # RAM 25%
work_mem = 32MB                    # 쿼리당 sort/hash 메모리
maintenance_work_mem = 2GB         # VACUUM, CREATE INDEX
effective_cache_size = 48GB        # planner가 참고 — OS 캐시 포함 추정
random_page_cost = 1.1             # SSD
effective_io_concurrency = 200     # SSD
wal_compression = on
wal_level = replica
max_wal_size = 16GB
checkpoint_completion_target = 0.9
autovacuum_vacuum_cost_limit = 2000
log_min_duration_statement = 1000  # 1초 넘는 쿼리 로깅
log_lock_waits = on

12.2 필수 extension

CREATE EXTENSION pg_stat_statements;  -- 쿼리별 통계
CREATE EXTENSION pgstattuple;          -- bloat 측정
CREATE EXTENSION auto_explain;         -- 느린 쿼리 EXPLAIN 자동 로깅

12.3 모니터링 쿼리

Top slow queries:

SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Bloat 체크:

SELECT schemaname, relname,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  n_dead_tup, n_live_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

사용되지 않는 인덱스:

SELECT schemaname, relname, indexrelname, idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname NOT IN ('pg_catalog', 'pg_toast')
ORDER BY pg_relation_size(indexrelid) DESC;

락 대기:

SELECT blocked.pid AS blocked_pid,
       blocked.query AS blocked_query,
       blocking.pid AS blocking_pid,
       blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.state = 'active';

12.4 안티 패턴

• SELECT * FROM t WHERE LOWER(col) = 'x': LOWER(col) 인덱스가 없으면 풀 스캔. CREATE INDEX ON t (LOWER(col)) 또는 CITEXT 타입.
• long-running transaction: autovacuum 차단. 배치는 잘게 쪼개기.
• UPDATE로 많은 row 수정: dead tuple 폭발. CREATE TABLE new AS SELECT 후 swap을 고려.
• 무의식적 NOT IN with NULLable: NOT IN 은 NULL 있으면 전체 false. NOT EXISTS 쓰기.
• OFFSET 1000000: 큰 offset은 그 앞의 row를 모두 읽어야 함. keyset pagination ("seek method") 쓰기.

13. 미래 — Postgres 17/18 이후

• Asynchronous I/O: Postgres 17에서 실험적, 18에서 안정화 방향. io_uring 활용.
• Direct I/O: O_DIRECT 지원 진전. double buffering 해소.
• 64-bit xid: 오랜 숙원. Wraparound 문제 영구 해결. 진행 중.
• Incremental Materialized View: Postgres 18 후보. 전체 REFRESH 없이 증분 갱신.
• Logical replication의 DDL 지원: 현재는 DDL 수동 동기화 필요. 자동화 예정.

맺음 — 코끼리를 타는 법

Postgres를 한마디로 요약하면 **"MVCC를 극단적으로 일관되게 적용한 시스템"**이다. UPDATE가 새 row를 만들고, dead tuple이 쌓이고, Vacuum이 청소하고, Freeze가 xid wraparound를 막는다. 이 한 줄짜리 서사를 다면적으로 풀어낸 게 이 글이다.

운영자로서 가장 중요한 세 가지 본능을 정리한다:

1. Long-running transaction을 두려워하라. 모든 재앙의 시작이다.
2. Autovacuum을 신뢰하되 감시하라. 기본값은 대부분 작업에 불충분하다.
3. EXPLAIN ANALYZE를 읽을 수 있어야 한다. 통계가 낡으면 plan이 망가진다.

이 세 가지를 본능적으로 체크하게 되면, 대부분의 Postgres 이슈는 30분 안에 잡힌다.

다음 글은 B-tree vs LSM — Postgres와 대척점에 있는 RocksDB/Cassandra/ScyllaDB의 세계를 다룬다. 같은 "데이터베이스"라는 단어가 얼마나 다른 설계 철학을 품을 수 있는지, 그리고 각 모델이 어떤 워크로드에서 승리하는지를 보인다.

코끼리는 길들일 수 있다. 그 뼈대를 이해하면.