work_mem — 정렬과 해시가 디스크로 새는 순간

work_mem은 PostgreSQL이 정렬(ORDER BY), 해시(해시 조인·해시 집계), 비트맵 등 한 번의 쿼리 연산 하나가 메모리에서 쓸 수 있는 최대 용량입니다. 이 한도를 넘는 순간 연산은 결과를 디스크의 임시 파일로 흘려보내는데(이걸 스필, spill이라 합니다), 이때부터 쿼리가 급격히 느려집니다. 기본값은 4MB로, 요즘 서버 기준으론 대개 너무 작습니다.

솔직히 처음엔 work_mem을 그냥 "메모리 크게 주면 빨라지는 값" 정도로 알았습니다. 그러다 이걸 무턱대고 크게 잡았다가 서버 메모리가 터지는 걸 보고서야 이 값의 진짜 무서움을 배웠습니다.

왜 "연결당"이 아니라 "연산당"인가

가장 흔한 오해가 여기 있습니다. work_mem은 쿼리 하나당도, 연결 하나당도 아닙니다. 쿼리 계획 안에 정렬이 3개, 해시가 2개 있으면 각각이 최대 work_mem까지 따로 쓸 수 있습니다. 게다가 병렬 쿼리라면 워커마다 또 곱해집니다. 그래서 실제 최대 메모리는 대략 이렇게 폭발합니다.

이론상 최대 ≈ work_mem × (계획 내 메모리 연산 수) × (병렬 워커 수) × (동시 실행 쿼리 수)

work_mem을 256MB로 잡고 무거운 분석 쿼리를 동시에 여럿 돌리면, 이 곱셈이 순식간에 서버 물리 메모리를 넘겨 OOM으로 이어집니다. 그래서 "전역으로 크게"가 아니라 "필요한 세션·쿼리에만 크게"가 원칙입니다.

스필이 일어나는지 EXPLAIN으로 확인

추측 금지. EXPLAIN (ANALYZE, BUFFERS)가 정확히 알려줍니다.

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, count(*)
FROM orders
GROUP BY customer_id
ORDER BY count(*) DESC;
-- 문제 신호를 이런 줄에서 찾는다:
--   Sort Method: external merge  Disk: 42112kB      ← 정렬이 디스크로 스필
--   Sort Method: quicksort  Memory: 2048kB          ← 이건 메모리 내 처리(좋음)
--   Batches: 8  Memory Usage: ...                    ← 해시가 여러 배치로 쪼개짐(스필)

Sort Method: external merge Disk: ...가 보이면 그 정렬은 지금 디스크를 긁고 있는 겁니다. 이때 그 세션에서만 work_mem을 올려 다시 실행해 보면, 같은 줄이 quicksort Memory: ...로 바뀌며 실행 시간이 확 줄어드는 걸 볼 수 있습니다. 이게 튜닝의 즉효 확인법입니다.

안전하게 올리는 법 — 전역 대신 국소

가장 안전한 접근은 전역 postgresql.conf는 보수적으로 두고(예: 16~64MB), 무거운 배치·리포트 쿼리에서만 세션 단위로 크게 잡는 것입니다.

-- 이 세션에서만 크게 (다른 연결엔 영향 없음)
SET work_mem = '256MB';
-- ... 무거운 정렬/집계 쿼리 실행 ...
RESET work_mem;
-- 특정 역할·DB에만 기본값 지정
ALTER ROLE report_worker SET work_mem = '256MB';

스필이 무조건 나쁜 건 아니다

오해를 하나 풀자면, 디스크 스필은 안전장치이지 버그가 아닙니다. PostgreSQL은 메모리가 부족하면 죽는 대신 디스크로 우아하게 넘어갑니다. 진짜 문제는 "자주, 크게" 스필하는 핫한 쿼리입니다. 어쩌다 한 번 도는 배치가 조금 스필하는 건 그냥 두는 게 낫습니다. 모든 스필을 없애려고 work_mem을 올리다 OOM을 부르는 게 더 나쁜 결과입니다.

자주 묻는 질문

work_mem을 얼마로 잡아야 하나요?

단일 정답은 없습니다. 대략 (가용 메모리의 25% ÷ 예상 동시 연산 수)를 출발점으로 잡고, 실제 무거운 쿼리를 EXPLAIN ANALYZE로 보며 스필이 사라지는 최소값으로 조정하세요. 전역은 낮게, 특정 쿼리는 세션에서 크게가 정석입니다.

hash_mem_multiplier는 뭔가요?

해시 기반 연산(해시 조인·해시 집계)에만 work_mem에 곱해지는 배수입니다. 기본 2.0이면 해시 연산은 work_mem의 2배까지 씁니다. 해시가 자주 스필하면 이 값을 올리는 게 work_mem 전체를 올리는 것보다 국소적이라 안전할 수 있습니다.

정렬이 느린데 work_mem을 올려도 그대로예요.

정렬이 이미 메모리 내(quicksort)라면 병목은 다른 곳입니다. 인덱스로 정렬 자체를 없애거나(ORDER BY에 맞는 인덱스), 데이터 양·조인 순서를 봐야 합니다. work_mem은 스필이 있을 때만 효과가 큽니다.

임시 파일이 어디에 쌓이는지 볼 수 있나요?

log_temp_files = 0으로 두면 임시 파일이 생길 때마다 로그에 크기가 남습니다. pg_stat_database의 temp_files·temp_bytes로도 누적 추이를 볼 수 있어, 어떤 시점에 스필이 심한지 추적하기 좋습니다.