실행계획은 하드웨어에서 어떻게 도는가: 페이지, 버퍼, work_mem
계획의 한 줄이 실제로는 디스크와 메모리에서 무슨 일인지 — 8KB 페이지, 저장 계층의 지연 시간, shared_buffers와 work_mem의 역할 분담, 노드별 메모리 사용과 초과 시 동작을 정리한다.
앞 글에서 실행계획을 표기법으로 읽는 법을 정리했다. 이 글은 그 아래층 — 계획의 한 줄이 실행될 때 물리적으로 무슨 일이 일어나는가를 다룬다. Seq Scan은 디스크의 어디를 어떻게 읽고, Sort가 쓰는 메모리는 어느 구역에서 나오며, 그 구역이 모자라면 무엇이 무너지는지.
모든 것은 8KB 페이지다
PostgreSQL이 디스크와 주고받는 최소 단위는 행이 아니라 페이지(블록) 이고, 기본 크기는 8KB다. 행 하나를 읽으려 해도 그 행이 든 페이지를 통째로 올린다. 계획에 찍히는 Buffers와 Heap Blocks가 전부 이 페이지의 개수다.
저장 계층 — 한 칸 내려갈 때마다 자릿수가 바뀐다
계획의 비용이 왜 I/O 중심으로 짜여 있는지는 이 그래프 하나로 설명된다. CPU가 메모리에서 값을 꺼내는 시간과 디스크에서 페이지를 가져오는 시간은 자릿수가 다르다.
블록 하나가 올라오는 경로
노드가 페이지를 요구하면 아래 순서로 찾는다. 위쪽에서 찾으면 아래로 안 내려간다.
hit가 줄여 주는 건 I/O 비용이지 읽어야 하는 블록 수가 아니다. 블록을 hit 하려면 버퍼 해시 테이블을 뒤지고 핀을 걸고 락을 잡고 그 8KB 안에서 튜플을 하나씩 꺼내 조건을 판정한다. read=0이어도 블록 수가 수만이면 그 CPU 비용이 그대로 쿼리 시간이 된다. 블록 수 자체를 줄이는 건 인덱스의 일이다.
RAM 안의 두 구역 — 공유 캐시와 사설 작업대
PostgreSQL이 쓰는 메모리는 성격이 완전히 다른 두 덩어리로 나뉜다. 튜닝에서 이 둘을 섞어 생각하면 반드시 사고가 난다.
| shared_buffers | work_mem | |
|---|---|---|
| 범위 | 서버 전체 공유 | 연산 노드 하나당 |
| 담는 것 | 테이블·인덱스 페이지 사본 | 정렬·해시·비트맵 중간 결과 |
| 확보 시점 | 서버 기동 시 한 번 | 쿼리 실행 중 필요할 때 |
| 넘칠 때 | 오래된 페이지를 밀어냄 (clock sweep) | 디스크로 흘리거나 정밀도를 버림 |
| 총량 | 설정값 = 총량 | 설정값 × 노드 × 커넥션 |
| 일반적 설정 | 물리 메모리의 25% 내외 | 4~64MB, 무거운 쿼리만 세션 단위로 상향 |
노드마다 쓰는 메모리가 다르다
모든 노드가 work_mem을 쓰는 게 아니다. 어떤 노드는 스트리밍으로 흘려보내고, 어떤 노드는 입력을 다 모아야 결과를 낼 수 있다. 후자를 블로킹 노드라 하고, work_mem을 쓰는 건 이쪽이다.
| 노드 | 메모리 성격 | work_mem 사용 |
|---|---|---|
| Seq Scan | 링 버퍼(기본 256KB)만 순환 사용 — 큰 테이블이 캐시를 밀어내지 않게 | 안 씀 |
| Index Scan | 페이지 단위 랜덤 접근 | 안 씀 |
| Nested Loop | 한 행씩 흘려보냄 | 안 씀 |
| Sort | 입력을 다 모아야 첫 행이 나온다 | 씀 |
| Hash / Hash Join | 해시 테이블을 통째로 들고 있어야 한다 | 씀 |
| Bitmap Index Scan | 위치 비트맵을 모은다 | 씀 |
| HashAggregate | 그룹별 상태를 들고 있어야 한다 | 씀 |
Seq Scan이 링 버퍼를 쓴다는 게 중요하다. 큰 테이블을 통째로 훑어도shared_buffers전체를 자기 페이지로 덮어쓰지 않는다. 순차 스캔 한 번이 캐시를 초토화하는 사고를 막는 장치다.
work_mem을 넘기면 노드마다 다르게 무너진다
여기가 이 글의 핵심이다. 한도를 넘겼을 때 모두 디스크로 가는 게 아니다. 노드마다 대응이 다르고, 계획에 남는 흔적도 다르다.
정리하면 이렇다.
| 노드 | 한도 초과 시 동작 | 계획에 남는 흔적 |
|---|---|---|
| Sort | 디스크 임시 파일로 스필 | Sort Method: external merge Disk: N kB |
| Hash Join | 배치로 분할해 여러 번 처리 | Batches: N (N > 1) |
| HashAggregate | 배치로 분할 (PG13 이상) | Disk Usage: N kB |
| Bitmap Index Scan | 페이지 단위 lossy로 강등 | Heap Blocks: lossy=N, Rows Removed by Index Recheck |
work_mem은 서버 예산이 아니라 노드당 한도다
그래서 이 값을 전역으로 올리는 게 위험하다. 한 쿼리가 Sort 2개 + HashJoin 1개 + Bitmap 1개를 돌리면 그 쿼리 하나가 work_mem × 4를 쓸 수 있고, 여기에 동시 커넥션 수가 곱해진다.
실무 원칙은 하나로 정리된다. 전역은 보수적으로 두고(4~64MB), 무거운 배치·리포트 쿼리에서만 세션 단위로 올린다.
1
SET work_mem = '256MB'; -- 이 세션에서만
배치 전용 커넥션에서는 정당한 패턴이고, 동시성 높은 사용자 API 커넥션에서는 위험하다.
순차 I/O와 랜덤 I/O
random_page_cost = 4.0이라는 기본값의 근거가 여기 있다. 회전 디스크에서 순차 읽기는 헤드를 움직이지 않지만, 랜덤 읽기는 매번 탐색과 회전 대기가 붙는다. SSD에서는 이 격차가 크게 줄어든다.
| 매체 | 순차 | 랜덤 | 실무상 random_page_cost |
|---|---|---|---|
| HDD | 빠름 | 탐색 + 회전 대기 (~10ms) | 4.0 (기본값의 전제) |
| SATA SSD | 빠름 | 탐색 없음, 약간 느림 | 1.5 ~ 2.0 |
| NVMe SSD | 매우 빠름 | 거의 차이 없음 | 1.0 ~ 1.5 |
이 값이 SSD 환경에서 4.0으로 남아 있으면 옵티마이저는 인덱스 스캔(랜덤 접근)을 실제보다 비싸게 평가해 Seq Scan을 과도하게 선호한다. 계획이 자꾸 인덱스를 안 타면 인덱스를 의심하기 전에 이 파라미터를 먼저 본다.
Bitmap Heap Scan이 존재하는 이유도 같은 맥락이다. 인덱스로 찾은 위치를 페이지 번호 순으로 정렬해서 읽으면, 흩어진 랜덤 접근이 순차에 가까운 접근으로 바뀐다. 선택도가 중간일 때 Index Scan보다 Bitmap이 이기는 지점이 여기다.
쓰기는 어디로 가나
읽기만 보면 절반이다. UPDATE 한 번은 이렇게 움직인다.
- 대상 페이지를
shared_buffers로 올린다 - 메모리 위에서 새 버전 튜플을 쓰고, 그 페이지를 더티로 표시한다 (
Buffers: shared dirtied=N) - 변경 내역을 WAL에 먼저 기록한다 — 커밋 시점에 디스크로 내려가는 건 데이터 파일이 아니라 이쪽이다
- 더티 페이지는 나중에 체크포인터·백그라운드 라이터가 데이터 파일에 반영한다
즉 커밋은 WAL만 기다리고, 데이터 파일 쓰기는 뒤로 미뤄진다. 랜덤한 위치의 페이지 여러 개를 그때그때 디스크에 쓰는 대신, 순차적인 WAL 하나만 확실히 쓰는 구조다. 앞 절의 “순차가 랜덤보다 싸다”가 쓰기 경로 설계에도 그대로 적용돼 있는 셈이다.
면접 한 줄 Q&A
- Q. PostgreSQL이 디스크와 주고받는 최소 단위는? A. 8KB 페이지(블록)다. 행 하나가 필요해도 그 행이 든 페이지를 통째로 올린다. 계획의
Buffers와Heap Blocks가 전부 이 단위다. - Q.
shared_buffers와work_mem의 차이는? A.shared_buffers는 모든 커넥션이 공유하는 페이지 캐시로 기동 시 한 번 잡히고 설정값이 곧 총량이다.work_mem은 정렬·해시·비트맵의 작업 공간으로 연산 노드마다 잡히므로 총량이 설정값 × 노드 수 × 커넥션 수가 된다. - Q.
Buffers: shared hit과read의 차이는? A.hit은shared_buffers에서 바로 찾은 블록,read는 거기 없어서 OS 캐시나 디스크까지 내려간 블록이다.read도 실제로는 OS 페이지 캐시에서 왔을 수 있다. - Q.
work_mem이 모자라면 무슨 일이 생기나? A. 노드마다 다르다. Sort는 디스크로 스필(external merge), Hash Join은 배치 분할(Batches > 1), Bitmap Index Scan은 페이지 단위 lossy로 강등된다. 원인은 하나인데 증상 이름이 다 다르다. - Q.
work_mem을 전역으로 올리면 왜 위험한가? A. 노드당 한도라서 한 쿼리 안에서도 노드 수만큼, 그리고 동시 커넥션 수만큼 곱해진다. 전역 상향이 OOM으로 가는 가장 흔한 경로다. - Q. SSD를 쓰는데 인덱스를 잘 안 타면? A.
random_page_cost기본값 4.0을 의심한다. 회전 디스크 전제라 SSD에서는 과대평가이고, 그 결과 옵티마이저가 Seq Scan을 과도하게 선호한다. 보통 1.1~2.0으로 낮춘다. - Q. 커밋할 때 데이터 파일이 디스크에 써지나? A. 아니다. 커밋이 기다리는 건 WAL이다. 더티 페이지는 체크포인터·백그라운드 라이터가 나중에 반영한다. 랜덤 쓰기를 순차 쓰기로 바꾸는 설계다.