들어가며 — 왜 또 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 내부 구조 글](./2026-04-15-duckdb-internals-vectorized-morsel-driven-columnar-deep-dive-guide-2025)과 짝을 이룬다. 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 어떤 인덱스를 언제

| 사용처 | 인덱스 |

|--------|--------|

| equality / range (int, string) | B-tree |

| 배열 contains, JSONB path | GIN |

| 전문 검색 (tsvector) | GIN (또는 GiST) |

| 공간/다차원 | GiST |

| 시계열 / append-only | BRIN |

| equality only, 매우 큰 데이터 | Hash (B-tree도 괜찮음) |

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의 세계를 다룬다. 같은 "데이터베이스"라는 단어가 얼마나 다른 설계 철학을 품을 수 있는지, 그리고 각 모델이 어떤 워크로드에서 승리하는지를 보인다.

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