PostgreSQL: Disk Spill

※ PostgreSQL: Disk Spill.

 

안녕하세요. 듀스트림입니다.

 

이번 포스팅은 대용량 쿼리의 성능을 좌우하는 핵심 요소 중 하나인 디스크 스필에 관한 내용입니다.

---

1. 디스크 스필?

Disk Spill은 PostgreSQL에서 쿼리를 실행하는 중 특정 연산(예: 정렬, 해시 조인, 집계 등)이 할당된 메모리(work_mem)를 초과할 경우, 그 초과된 데이터를 디스크(임시 파일)에 저장하여 처리하는 방식을 말합니다.

 

항목	설명
발생 조건	연산 중 메모리 사용량이 work_mem 초과 시
적용 대상	정렬(Sort), 해시 조인(Hash Join), 해시 집계(Hash Aggregate) 등
성능 영향	디스크 I/O 발생으로 쿼리 속도 저하 가능
감지 방법	EXPLAIN (ANALYZE) 결과 또는 log_temp_files 로그로 확인 가능

---

2. 디스크 스필이 필요한 이유

PostgreSQL은 하나의 연산자(예: 정렬, 조인 등)마다 사용할 수 있는 메모리(work_mem) 양이 제한되어 있습니다.

이 제한을 넘으면 다음과 같은 문제가 발생할 수 있습니다:

• 무한 메모리 사용 → 시스템 메모리 고갈 (Out Of Memory)
• 전체 쿼리 실패

이를 방지하기 위해 PostgreSQL은 중간 결과나 연산 데이터를 디스크로 내보내(Spill) 계산을 이어갑니다. (일종의 성능 대비 안정성 확보 전략입니다.)

---

3. work_mem과 디스크 스필

work_mem은 PostgreSQL에서 정렬, 해시 조인, 해시 집계 등의 연산 시 한 연산자(operator)당 허용되는 메모리 크기를 설정하는 파라미터입니다.

 

이 한도를 초과하면 PostgreSQL은 디스크에 임시 파일을 생성(temp file)하여 데이터를 저장하며, 이를 디스크 스필(disk spill)이라고 합니다.

 

19.4. Resource Consumption

---

4. PostgreSQL의 메모리 구조와 디스크 스필 처리

PostgreSQL은 실행 중 필요한 메모리를 단순 malloc() 방식이 아닌 자체 구현한 메모리 컨텍스트(MemoryContext) 시스템을 통해 정교하게 관리합니다. 이 구조는 성능과 안정성, 특히 스필 발생 시 처리 방식에 매우 중요한 기반이 됩니다.

---

PostgreSQL의 메모리 컨텍스트 구조

PostgreSQL의 메모리는 계층적 메모리 컨텍스트(MemoryContext)로 관리됩니다.

 

핵심 개념

개념	설명
MemoryContext	메모리 영역을 추상화한 구조체. 하위 컨텍스트를 가질 수 있음
AllocSetContext	PostgreSQL에서 가장 널리 쓰이는 기본 컨텍스트 구현체
MemoryContextAlloc()	메모리 컨텍스트에서 메모리를 할당하는 함수
ResetMemoryContext()	컨텍스트 내부의 모든 메모리를 한 번에 해제

 

계층 구조 예시

TopMemoryContext
├── PortalContext
│   ├── ExecutorState
│   │   ├── ExprContext (per-tuple)
│   │   └── AggContext, HashContext 등 (spill candidates)

• 관련 소스:
  • aset.c - MemoryContext 구현
  • nodes/memnodes.h

---

5. 디스크 스필이 발생하는 연산자 및 조건

정렬 (Sort)

• 조건:
  
  • 정렬 대상 데이터가 work_mem보다 작을 경우 → 메모리 내 정렬 (QuickSort 등)
  • 정렬 대상 데이터가 work_mem 초과할 경우 → 디스크에 spill → 외부 병합 정렬(External Merge Sort) 수행
• 흐름도:

정렬 시작
   ↓
정렬 대상 데이터 크기 측정
   ↓
work_mem 초과?
   ├─ No → 메모리 내 정렬 완료
   └─ Yes → 디스크 spill → 병합 정렬 수행

-- 예시
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM large_table ORDER BY created_at;

-- 플랜 예시
Sort Method: external merge  Disk: 5120kB

• 관련 소스 코드: nodeSort.c
  • ExecSort() 내부에서 메모리 한도 체크 및 tuplestore_puttuple() 호출 시 spill 발생

---

해시 조인 (Hash Join)

• 조건:
  
  • inner table이 work_mem 이내 → 전체를 해시 테이블로 메모리 내 구축
  • inner table이 work_mem 초과 → 디스크 spill → 여러 개의 배치(batch)로 나눠 조인 수행
• 흐름도:

Hash Join 시작
   ↓
inner relation 읽어 해시 테이블 구축 시도
   ↓
work_mem 초과?
   ├─ No → 메모리 내 해시 조인 수행
   └─ Yes → 해시 테이블을 디스크로 spill → 배치별로 조인 반복

• 관련 소스 코드: nodeHash.c
  • ExecHashIncreaseNumBatches()에서 spill 처리

-- 예시
SELECT * FROM a JOIN b ON a.id = b.id;

---

해시 집계 (Hash Aggregate)

• 조건:
  
  • 그룹 수가 적고 work_mem 내 → 메모리 내 해시 테이블로 집계 수행
  • 그룹 수가 많고 work_mem 초과 → 스필된 튜플을 디스크에 저장 후, 나중에 재집계
• 흐름도:

Hash Aggregate 시작
   ↓
입력 튜플을 해시 테이블에 삽입
   ↓
work_mem 초과?
   ├─ No → 메모리 내에서 집계 완료
   └─ Yes → 튜플을 spill → 나중에 다시 읽어서 집계 수행

• 관련 소스 코드: nodeAgg.c
  • ExecAgg 함수 내부 hashagg_spill_tuple() 호출

---

요약

연산자	spill 발생 조건	처리 방식
Sort	정렬 대상 크기 > work_mem	외부 병합 정렬
Hash Join	inner relation > work_mem	배치(batch)로 나누어 디스크에서 조인
Hash Agg	그룹 수 많아 work_mem 초과	spill → 다시 읽어 aggregate 재수행

---

6. 디스크 스필이 발생하지 않는 경우

대부분의 Plan 노드 및 표현식 처리(예: ExprContext)는 spill 로직이 없고, 단순 AllocSetContext에서 메모리를 할당합니다.

• 메모리 초과 시 PostgreSQL 프로세스는 MemoryContextAlloc() 단계에서 OOM 오류로 쿼리를 종료합니다.
• 일반적으로는 튜플 단위로 메모리 재사용(ResetExprContext)되므로 누수 없이 동작하지만, 예외 처리 없이 큰 데이터가 들어오면 위험합니다.

---

ExprContext 기반 평가

• 표현식 평가 (e.g. WHERE, CASE, 함수 호출 등)에서 사용되는 메모리 컨텍스트는 ExprContext입니다.
• 이 컨텍스트는 디스크 스필 없이 메모리를 즉시 할당/해제합니다.
• ExprContext는 대부분의 PlanState에 종속적으로 생성됩니다 (ExecInitExprContext() 함수에서 초기화).
• JIT이 활성화된 경우 LLVM을 통한 컴파일된 경로(llvmjit_expr.c)로 우회되기도 합니다..
• 관련 소스 코드: execnodes.h
  • ExprContext 구조체 정의
  • 표현식 평가에 필요한 per-tuple memory context, per-query context, scan context 등을 포함
  • ExprContext는 튜플 단위로 실행 중 할당되는 일시적인 메모리를 위한 구조
• 관련 소스 코드: execExpr.c
  • ExprContext를 이용한 표현식 평가 로직 구현
  • ExecInterpExpr() 또는 JIT 컴파일 경로에서 표현식을 실행
  • ecxt_per_tuple_memory를 이용해 임시 연산 결과를 저장하고 평가 후 자동 초기화
• 주요 함수 예시: static Datum ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull);
  • ExprState->steps 배열에 저장된 ExprEvalStep들을 순차적으로 해석 실행
    
    • 각 스텝은 하나의 평가 연산을 의미 (예: 상수, 변수, 함수 호출, 산술 연산 등)
    • 일종의 바이트코드 해석 방식
  • ExprContext->ecxt_per_tuple_memory를 사용하여 각 튜플 평가마다 메모리 임시 할당
    
    • 이 컨텍스트는 매 row마다 초기화되므로 메모리 누수가 없음
    • 디스크 스필은 발생하지 않음
  • work_mem의 적용 대상이 아니므로, 초과 시에는 메모리 스필이 아닌 OOM 오류 발생
  • 관련 소스 코드: execExprInterp.c

---

디스크 스필을 지원하지 않는 Plan 노드

연산자 유형	Spill 여부	설명
SeqScan, IndexScan	❌	Row-by-row로 상위 노드에 바로 전달. Buffer는 사용되지만 spill 구조는 없음.
Nested Loop Join	❌	자체적으로 spill 미지원. 단, inner node가 spill하는 경우는 가능.
ValuesScan, FunctionScan	❌	계획 시점에 미리 알려진 정적/소량 데이터. 메모리 적게 사용.
Gather, Gather Merge	⚠️ 가능성 있음	자체적으로 spill은 없음. 하위 노드에서 spill 또는 tuplestore 사용 가능.
ForeignScan (FDW)	⚠️ 구현체 의존	PostgreSQL core에서 spill 로직 없음. FDW 구현에 따라 tuplestore 사용 가능성 존재. 대부분 미지원.

postgres_fdw는 remote SQL 결과를 row-by-row로 리턴함.
file_fdw, oracle_fdw 등 대부분은 spill을 위한 특별한 구조 없음.
일부 FDW는 tuplestore_puttupleslot()을 통해 spill 유사 동작 가능 (개발자 구현 필요).

---

메모리 초과 시 동작

항목	설명
메모리 컨텍스트	AllocSetContext 기반 ExprContext->ecxt_per_tuple_memory
디스크 스필 유무	❌ Spill 처리 로직 없음
초과 시 동작	MemoryContextAlloc()에서 OOM 발생 → ERROR: out of memory
스필 여부 판단 기준	work_mem 체크 후 spill-capable 연산자에서만 수행됨 (예: Sort, Hash Join 등)

 

---

7. 정리

• ExprContext 기반 표현식은 디스크 스필 대상이 아닙니다. (메모리 내 평가)
• 대부분의 Plan 노드는 Spill을 지원하지 않으며, spill은 Sort, Hash, Agg에 한정됩니다.
• FDW 및 병렬처리(Gather)는 자체 Spill 미지원. 하위 노드에서의 Spill 가능성만 존재합니다.
• PostgreSQL 메모리 관리는 계층형 MemoryContext 구조로 이루어집니다.

 

요약 표

구분	설명
Spill 지원 노드	Sort, Hash Join, Hash Agg 등 → work_mem 초과 시 자동 spill
Spill 불가 노드	Nested Loop, SeqScan, ExprContext 등 → 초과 시 OOM
메모리 할당 방식	MemoryContextAlloc()으로 할당, 계층 구조로 관리
메모리 해제 방식	ResetMemoryContext(), MemoryContextDelete()로 일괄 해제
Spill 구현 위치	tuplestore.c, nodeSort.c, nodeHash.c, nodeAgg.c 등

---

8. 디스크 스필 체크 방법

PostgreSQL에서 정렬(Sort), 해시(Hash Join, Hash Aggregate) 등 work_mem을 초과하는 연산자들이 디스크에 데이터를 임시 저장할 때 발생하는 스필(spill)을 체크하고 분석하는 방법은 다음과 같습니다.

 

EXPLAIN (ANALYZE, BUFFERS) 사용

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM large_table ORDER BY column_name;

확인 포인트:

Sort Method: external merge  Disk: 5120kB

• Sort Method 또는 HashAgg 등과 함께 Disk: 용량이 나오면 실제 디스크 I/O 발생 확인 가능
• BUFFERS 옵션은 디스크 I/O 통계까지 함께 제공

---

log_temp_files 설정

log_temp_files = 0  # 0 이상이면 임시 파일 생성 시 로그 출력

• PostgreSQL이 생성하는 임시 파일(temp file)을 로그에 기록
• work_mem 초과로 디스크 스필이 발생하면, 임시 파일이 생성되고 로그로 출력됨

예시 로그

LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp1234.0", size 153600kB

• log_min_messages = info 이상으로 설정되어 있어야 로그 출력됨
• 실제 운영 환경에서는 log_temp_files = 1024-2048 등으로 적절히 조정하여 불필요한 로그 폭주 방지 (디스크 I/O 많을 경우 로그량 증가)

---

pg_stat_statements + temp_blks_written 활용

• pg_stat_statements 확장 뷰는 쿼리별 누적 실행 통계를 제공합니다.
  • temp_blks_written 컬럼을 통해 디스크에 쓰여진 임시 블록 수 확인 가능
  • 쿼리별 파라미터는 normalizing되어 있어 구체적인 조건 파악은 어려움

SELECT query, temp_blks_written
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 10;

---

요약 표

 

방법	실시간	정확도	쿼리 특정 가능	운영 환경 적합성
EXPLAIN (ANALYZE)	❌ (수동)	⭐⭐⭐⭐	✅	개발/튜닝에 적합
log_temp_files	✅	⭐⭐⭐	❌	운영 로그 기반 감지
pg_stat_statements	✅	⭐⭐⭐⭐	파라미터 일반화됨	운영 분석용
pg_stat_io (v16 이상)	✅	⭐⭐⭐	✅ (경량 통계)	최신 버전에서만

---

9. 튜닝 전략

시나리오	튜닝 포인트
잦은 정렬 스필	work_mem 상향 조정
해시 조인에서 batch 과다 발생	hash_mem_multiplier 활용
전체 쿼리 OOM	max_parallel_workers, parallel_tuple_cost 재조정
임시 파일 크기 지속 증가	log_temp_files, 쿼리 최적화

---

디스크 스필은 메모리 사용량을 초과했을 때 성능을 보장하기 위한 중요한 메커니즘입니다.
다만, 모든 연산자가 디스크 스필을 지원하는 것은 아니며, 연산자 특성과 쿼리 구조를 정확히 이해하는 것이 성능 튜닝의 핵심입니다.

 

오늘은 여기까지~