"vacuum" 태그로 연결된 3개 게시물개의 게시물이 있습니다.

DBA가 아닌 개발자에게 checkpoint 스파이크를 설명해 본 적 있다면 그 난감함을 알아요. WAL이 뭔지부터 시작해야 하고, dirty page가 왜 쌓이는지, max_wal_size가 왜 그 시점을 정하는지 순서대로 쌓아야 해요. 그림을 그려도 정적이라 "시간에 따라 이게 몰린다"는 감각이 전달되지 않아요.

PGSimCity는 그 문제를 정면으로 노립니다. PostgreSQL 내부를 탐험 가능한 3D 도시로 만들어 놓고, 시간을 흘려보내며 그 안에서 무슨 일이 벌어지는지 보여줍니다. 만든 사람은 postgres.ai의 Nikolay Samokhvalov(니콜라이 사모흐발로프)입니다.

브라우저에서 바로 열립니다. nikolays.github.io/PGSimCity에 접속하면 설치 없이 도시가 뜹니다. WebGL2를 지원하는 브라우저가 필요합니다.

저장소를 직접 빌드해 봤습니다​

소개 글을 쓰려면 실제로 돌려 보는 게 맞습니다. 클론해서 테스트와 빌드를 통과시켜 보고 확인한 사실을 먼저 적습니다.

git clone https://github.com/NikolayS/PGSimCity.git
cd PGSimCity
npm install
npm run typecheck   # 통과, 오류 없음
npm test            # 48개 파일, 360개 테스트 통과 (10.4초)
npm run build       # 2.69초
npm run dev         # localhost:5173

README에는 테스트가 234개로 적혀 있는데 실측은 360개였습니다. 최근 커밋이 확인 시점과 같은 날짜였으니 문서가 코드를 못 따라가는 중입니다. 그만큼 활발하다는 뜻으로 읽으면 됩니다.

의존성이 얇은 게 눈에 띕니다. 번들 런타임 의존성이 Three.js 하나뿐이고, 시뮬레이션 코드는 Three.js를 import하지 않습니다. 시뮬레이션과 렌더링이 SimState에서만 만나는 구조라, 도식 없이도 시뮬레이션 로직만 따로 테스트할 수 있습니다. 360개 테스트가 붙어 있는 이유가 여기에 있습니다.

도시가 표현하는 것​

지구가 PostgreSQL의 구성요소에 대응합니다.

• 클라이언트 영역: 들어오는 connection
• backend 구역: connection 하나당 프로세스 하나, 각자의 private memory
• buffer pool: shared_buffers를 샘플링한 프레임 격자
• 스토리지: heap 파일, B-tree, TOAST
• WAL 지구: 쓰기가 디스크에 먼저 도착하는 경로
• 유지보수 구역: checkpointer, autovacuum launcher와 worker, background writer
• replication 구역: standby와 WAL receiver

색이 상태를 나타냅니다. WAL은 호박색, dirty page는 빨강, 청소 작업은 보라색입니다. 도시를 내려다보다가 빨간 블록이 넓어지면 dirty page가 쌓이는 중이라는 걸 눈으로 압니다.

소스를 검사하다 예상 밖의 지구를 하나 발견했습니다. src/world/continuity.ts에 백업과 복구 쪽 오브젝트가 따로 들어 있습니다.

archive.gate      archive 소유권 경계
timeline.yard     timeline 전환 조차장
object.store      오브젝트 스토리지
backup.vault      백업 금고
recovery.clock    recovery_target_time
restore.winch     restore_command

PITR을 도시 구조물로 옮겨 놓은 것입니다. recovery_target_time이 시계로, restore_command가 권양기로 서 있습니다. Barman이나 pgBackRest로 PITR을 설명할 때 "timeline이 갈라진다"는 말이 잘 안 통했다면 이 조차장을 보여주는 편이 빠르겠습니다.

투어 14장​

T를 누르면 안내 투어가 시작됩니다. 순서가 곧 커리큘럼입니다.

1. 클라이언트가 접속한다
2. connection 하나에 프로세스 하나
3. 쿼리가 plan이 된다
4. 페이지 읽기, 캐시
5. 페이지란 실제로 무엇인가
6. 쓰기: 페이지보다 WAL이 먼저 디스크로
7. commit, 그리고 fsync의 값
8. checkpoint, 그리고 스파이크
9. MVCC: update는 시체를 남긴다
10. autovacuum이 치운다
11. vacuum이 못 치울 때: horizon
12. standby로 streaming
13. lag, 그리고 네 개의 LSN
14. 도시 전체를 다시

저는 9장에서 11장까지가 한 묶음으로 붙어 있는 게 좋아요. update가 옛 버전을 남기고, autovacuum이 그걸 치우고, 그런데 horizon 때문에 못 치우는 경우까지 세 장으로 이어집니다. 이 순서를 말로 설명하면 늘 세 번째에서 막히는데, 도시에서는 vacuum worker가 왔다 갔는데 빨간 블록이 그대로 남아 있는 장면으로 보여줍니다.

13장의 "네 개의 LSN"은 sent_lsn, write_lsn, flush_lsn, replay_lsn입니다. replication lag을 볼 때 어느 단계에서 밀리는지 구분해야 하는데, 이 넷의 간격을 도시 안 거리로 보여주는 방식입니다.

시나리오 13종​

투어와 별도로 상황을 직접 재현하는 시나리오가 있습니다. 소스에서 확인한 목록입니다.

각 시나리오는 knob 값 묶음과 시간축 해설(beats)로 되어 있습니다. xmin-horizon 하나를 열어 보면 이런 식입니다.

knobs: tps 900, writeRatio 0.8, updateRatio 0.9,
       longRunningXact: true, autovacuum: true, ...

beats:
  0초   누군가 BEGIN을 입력했다
  14초  horizon이 얼어붙었다
  30초  autovacuum은 여전히 돈다
  48초  worker가 헛도는 것을 본다
  66초  브레이크 없는 bloat
  86초  어디를 봐야 하나
  108초 풀어 준다
  126초 그리고 예방한다

30초 지점 해설이 정확합니다. launcher는 여전히 worker를 보내고, worker는 테이블까지 가서 heap 전체를 스캔하고 I/O를 태우는데 회수하는 건 거의 없습니다. 모니터링에는 vacuum이 도는 것으로 보이고 테이블은 반대를 말합니다. 86초 지점에서는 pg_stat_activity를 state = 'idle in transaction'으로 걸러 xact_start 순으로 정렬하라고 알려주고, 버려진 replication slot과 hot_standby_feedback이 켜진 standby의 장기 쿼리도 같은 메커니즘이라고 덧붙입니다.

이 대목은 어제 쓴 MVCC 비용 비교 글에서 직접 실험으로 확인한 내용과 그대로 겹칩니다. VACUUM VERBOSE가 0 removed, 400000 are dead but not yet removable을 뱉는 장면, 범인이 pg_stat_activity에만 있는 게 아니라 replication slot과 prepared transaction에도 있다는 지점까지 같습니다. 실측으로 확인한 것을 시각 자료로 다시 설명할 수 있게 됐습니다.

모델인가 에뮬레이터인가​

저자는 이게 PostgreSQL 모델이고 에뮬레이터가 아니라고 밝힙니다. PostgreSQL 소스가 돌아가지 않고, 숫자는 사람이 눈으로 따라갈 수 있게 축척을 조정했습니다. buffer pool도 shared_buffers 전체가 아니라 1,024개 프레임 샘플입니다.

다만 빌드 결과를 보다가 청크 하나가 눈에 걸렸습니다.

dist/assets/real-postgres-runtime-BHGmqr-J.js   539.32 kB

소스를 열어 보니 @electric-sql/pglite를 import하고 pglite.wasm, initdb.wasm을 함께 번들합니다. PGlite는 PostgreSQL을 WASM으로 컴파일한 것입니다.

import { PGlite } from '@electric-sql/pglite'
import initdbWasmUrl from '.../pglite/dist/initdb.wasm?url'
import pgliteWasmUrl from '.../pglite/dist/pglite.wasm?url'

즉 구분이 필요합니다. 도시 시뮬레이션은 모델이지만, Query Lab 쪽은 브라우저 안에서 진짜 PostgreSQL을 띄워 실제 실행 계획을 받아 옵니다. accounts, orders, events, sessions 테이블에 인덱스까지 붙인 시드 스키마가 코드에 들어 있습니다. 도시의 움직임은 모형이고, 쿼리 실습은 실물입니다.

이 조합이 영리합니다. 실제 PostgreSQL을 8KB 페이지 단위로 시각화하면 사람 눈에는 아무것도 안 보입니다. 그래서 보여주는 층은 축척을 조정한 모델로 두고, 정확성이 중요한 실행 계획은 실물 엔진에 맡겼습니다.

한계와 쓸 자리​

버전이 0.12.0입니다. 저자도 0.x 초기 단계임을 명시하고, PostgreSQL 정확성은 문서와 소스로 3라운드 검토했다고 밝힙니다. 기억에 의존해 만들지 않았다는 뜻이고, 그래도 프로덕션 판단 근거로 쓸 물건은 아닙니다. 축척을 조정한 숫자를 실제 튜닝 값으로 옮기면 안 됩니다.

쓸 자리는 분명합니다. 신입 교육 첫 주에 개념 지도를 잡아 주는 용도, 장애 회고에서 "이때 이런 일이 있었다"를 비개발자에게 설명하는 용도, DB를 운영해 본 적 없는 개발자에게 idle_in_transaction_session_timeout이 왜 필수인지 납득시키는 용도입니다. 마지막 항목은 특히 말로 하면 잔소리로 들리는데, worker가 헛도는 장면을 2분간 같이 보면 설명이 끝납니다.

라이선스는 Apache-2.0이라 사내 교육 자료로 가져다 쓰기에도 걸림이 없습니다. 정적 번들이니 npm run build 결과를 사내 어디든 올려 둘 수 있습니다. 다만 PostgreSQL은 PostgreSQL Community Association of Canada의 상표이고 이 프로젝트는 공식 지원을 받지 않는 독립 교육 프로젝트라는 점, 그리고 이름과 달리 Electronic Arts와 무관하다는 점은 저자가 직접 밝혀 둔 대로 함께 알려 주는 게 좋겠습니다.

참고​

• PGSimCity 저장소 (Apache-2.0, Nikolay Samokhvalov)
• 라이브 데모
• PGlite 브라우저에서 도는 PostgreSQL WASM 빌드
• GIGAZINE 소개 기사

이 블로그의 관련 글로는 MVCC 비용은 어디에 숨었나와 PostgreSQL 18의 autovacuum_worker_slots가 있어요.

PostgreSQL을 오래 운영하면 vacuum이 미워지는 날이 와요. dead tuple이 안 줄고, autovacuum은 계속 도는데 테이블은 커지고, VACUUM FULL은 락 때문에 걸 수 없어요. 그럴 때 검색하면 나오는 글이 CMU Andy Pavlo(앤디 파블로)의 The Part of PostgreSQL We Hate the Most와 Uber의 2016년 MySQL 전환기예요. 둘 다 틀린 말을 하지는 않아요.

그런데 이 비판들에는 공통으로 빠진 게 있습니다. 바로 비교 대상입니다. PostgreSQL의 MVCC가 나쁘다면, 나쁘지 않은 MVCC는 어디에 있는지 궁금해집니다.

이 질문을 정면으로 다룬 글이 최근 나왔습니다. boringSQL의 Radim Marek(라딤 마렉)이 쓴 PostgreSQL's MVCC is bad. So is everyone else's.입니다. 이 글은 그 논의를 출발점으로 삼되, 원문에 없는 두 가지를 채웁니다. 하나는 Docker에 PostgreSQL 18.4를 띄워 직접 재본 수치이고, 다른 하나는 엔진별로 같은 문제를 진단하는 쿼리입니다.

모든 MVCC 구현이 답해야 하는 네 가지 질문​

원문이 제시한 프레임이 좋아서 그대로 빌려 옵니다. 어떤 엔진이든 다중 버전을 구현하려면 네 가지를 결정해야 합니다.

1. 옛 버전을 어디에 두는가
2. 버전 체인은 어느 방향을 가리키는가
3. 인덱스는 무엇을 가리키는가
4. 누가, 언제 치우는가

PostgreSQL의 답은 이렇습니다. 옛 버전은 테이블 안에 두고, 체인은 과거에서 미래로 향하고, 인덱스는 물리 위치(ctid)를 가리키며, 청소는 백그라운드 프로세스가 나중에 합니다.

네 번째 질문이 특히 중요합니다. 청소를 나중에 하면 쓰레기가 눈에 보이고, 청소를 트랜잭션이 직접 하면 그 트랜잭션이 느려집니다. 어느 쪽도 공짜가 아닙니다.

PostgreSQL이 치르는 값을 직접 재봤다​

논의를 수치 없이 하면 감상이 됩니다. Docker에 PostgreSQL 18.4를 띄우고 직접 측정했습니다. 프로덕션 서버가 아닌 노트북 위 컨테이너라서, 절대값보다 비율을 보면 됩니다.

docker run -d --name mvcclab -e POSTGRES_PASSWORD=lab postgres:18

테이블은 100만 행이고, 컬럼은 id(PK)와 텍스트 4개, last_seen 타임스탬프로 구성했습니다. 이 중 10만 행을 UPDATE하면서 생성되는 WAL 양과 HOT update 비율을 측정했습니다. 측정 전마다 CHECKPOINT를 실행해 full page write 조건을 맞췄습니다.

write amplification은 인덱스 개수가 아니라 페이지 여유가 결정한다​

두 번째 줄이 흔히 인용되는 그 비용입니다. 인덱스 컬럼을 건드리지도 않았는데 보조 인덱스 4개를 붙였다는 이유만으로 WAL이 116MB에서 218MB로 뜁니다. 새 tuple이 다른 페이지에 만들어지면 ctid가 바뀌고, 모든 인덱스가 그 새 위치를 따라가야 하기 때문입니다.

흥미로운 건 세 번째 줄입니다. 인덱스 4개를 그대로 둔 채 fillfactor만 70으로 낮추면 WAL이 81MB로 떨어집니다. 인덱스가 아예 없는 첫 번째 줄(116MB)보다도 적습니다. 페이지에 30% 여유가 생기니 새 버전이 같은 페이지 안에 들어가고, HOT update 조건이 성립해 인덱스를 아예 건드리지 않습니다. 원문 실험에서는 HOT 비율이 66%였는데, 이 실험은 행이 작아 100%가 나왔습니다.

정리하면 write amplification을 좌우하는 진짜 변수는 같은 페이지에 새 버전이 들어갈 자리가 남아 있느냐입니다. 인덱스를 줄이는 것보다 fillfactor를 조정하는 편이 대개 현실적입니다. 대신 heap이 73MB에서 104MB로 커집니다. 디스크를 미리 내주고 WAL과 인덱스 갱신을 아끼는 거래입니다.

네 번째 줄은 이 거래가 통하지 않는 경우입니다. 인덱스가 걸린 컬럼 자체를 바꾸면 HOT 조건이 깨지므로 fillfactor를 아무리 낮춰도 소용없습니다. 인덱스 크기도 107MB에서 129MB로 함께 부풉니다.

같은 UPDATE를 fillfactor 70 테이블에 네 번 반복해도 HOT 비율은 100%를 유지했고 WAL은 90MB 근처에서 평평했습니다. heap도 104MB에서 늘지 않았습니다. 여유 공간이 소진되어 HOT이 깨지는 지점은 이 워크로드에서는 오지 않았습니다.

bloat는 커밋했을 때와 롤백했을 때가 다르다​

원문에도 있는 실험인데, 실제로 돌려 보니 원문이 다루지 않은 갈래가 나왔습니다.

100만 행을 UPDATE한 뒤 ROLLBACK합니다.

BEGIN;
UPDATE t SET last_seen = now();  -- 2847 ms
ROLLBACK;                        -- 1.0 ms

롤백은 1밀리초에 끝납니다. PostgreSQL은 롤백할 때 되돌릴 게 없습니다. 새로 쓴 tuple을 "이 트랜잭션은 실패했다"고 표시하면 그만입니다. 상수 시간입니다. 그런데 테이블은 89MB에서 178MB로 두 배가 됐고 dead tuple이 100만 개 생겼습니다. 커밋하지 않은 작업이 디스크를 두 배로 쓴 것입니다.

여기서 갈립니다.

ROLLBACK 케이스:  89MB → 178MB → VACUUM →  89MB
COMMIT  케이스:  89MB → 178MB → VACUUM → 178MB

롤백한 경우에는 일반 VACUUM만으로 파일이 89MB로 돌아갑니다. 롤백으로 죽은 tuple은 전부 테이블 뒤쪽에 새로 붙은 것들이라 연속 구간을 이루고, vacuum이 꼬리를 잘라 OS에 반납하기 때문입니다.

커밋한 경우는 다릅니다. 죽는 건 테이블 앞쪽 여기저기에 흩어진 옛 버전입니다. vacuum은 그 자리를 재사용 가능하게 표시할 뿐 파일을 줄이지 못합니다. 178MB를 회수하려면 VACUUM FULL이 필요하고, 그건 ACCESS EXCLUSIVE 락을 잡습니다.

다만 이 178MB가 무한히 자라지는 않습니다. 같은 UPDATE를 두 번, 세 번 반복하고 매번 vacuum을 돌려도 파일은 178MB에서 멈췄습니다. vacuum이 표시해 둔 자리를 다음 UPDATE가 재사용하기 때문입니다. 전면 갱신 워크로드에서 bloat는 대략 두 배 지점으로 수렴합니다. 흔히 걱정하는 "방치하면 무한정 커진다"는 vacuum이 제때 못 도는 경우의 이야기이지, MVCC 구조 자체의 결론은 아닙니다.

열어 둔 트랜잭션 하나가 정리를 통째로 막는다​

PostgreSQL의 실패 모드 중 운영에서 가장 자주 만나는 것입니다. 다른 세션에서 트랜잭션을 열어 놓고 방치한 상태로 dead tuple을 만든 뒤 VACUUM VERBOSE를 실행했습니다.

tuples: 0 removed, 600000 remain, 400000 are dead but not yet removable
removable cutoff: 861, which was 2 XIDs old when operation ended

40만 개가 죽었는데 하나도 회수되지 않았습니다. dead but not yet removable, 이 문구가 나오면 원인은 거의 항상 누군가 오래 붙들고 있는 스냅샷입니다. 그 트랜잭션을 종료하고 다시 실행하면 이렇게 바뀝니다.

tuples: 400000 removed, 200000 remain, 0 are dead but not yet removable
index scan needed: 6897 pages from table (66.67% of total) had 400000 dead item identifiers removed

주의할 건 이 트랜잭션이 문제의 테이블을 건드리지 않아도 상관없다는 점입니다. backend_xmin을 잡고 있는 한 데이터베이스 전체의 정리가 그 지점에서 멈춥니다. 점심 먹으러 가면서 커밋하지 않고 자리를 뜬 세션 하나가 무관한 테이블의 vacuum을 막습니다.

다른 엔진은 이 비용을 어디로 보냈나​

여기까지가 PostgreSQL이 내는 청구서입니다. 다른 엔진이라고 비용을 피하지는 못합니다. 비용이 없는 게 아니라 다른 항목으로 냅니다.

Oracle과 InnoDB는 옛 버전을 테이블 밖 undo 영역에 둡니다. 테이블은 행마다 한 버전만 유지하니 깔끔하고, 보조 인덱스는 물리 위치 대신 논리 키를 가리키므로 위치 변경에 따라올 필요가 없습니다. vacuum도 없고 freeze 의식도 없습니다. 대신 세 가지가 따라옵니다. 우선 롤백이 정직하게 비쌉니다. PostgreSQL이 1밀리초에 끝낸 100만 행 롤백을 undo 방식은 한 건씩 되돌려야 합니다. 읽기도 비싸집니다. 옛 스냅샷을 보려면 undo 체인을 거슬러 올라가 행을 재구성해야 합니다. 그리고 오래 도는 읽기 쿼리가 undo를 소진하면 그 쿼리를 죽입니다. Oracle에서 20년 넘게 DBA를 괴롭혀 온 ORA-01555: snapshot too old가 바로 그것입니다.

이 대비가 중요합니다. PostgreSQL은 오래된 reader를 위해 쓰레기를 쌓아 두고 견딥니다. Oracle은 쓰레기를 정리하고 reader를 죽입니다. 어느 쪽이 나은지는 워크로드가 정합니다.

SQL Server는 기본값이 아예 MVCC가 아닙니다. 잠금으로 격리를 만들고, RCSI를 켜야 행 버전 관리가 시작됩니다. 그때 옛 버전은 tempdb의 version store로 갑니다. 문제는 tempdb가 인스턴스 전체 공용이라는 점입니다. 데이터베이스 하나에서 오래 열린 스냅샷이 tempdb를 부풀리면 같은 인스턴스의 다른 데이터베이스까지 함께 멈춥니다. 폭발 반경이 데이터베이스 경계를 넘습니다. Microsoft가 2019년 ADR을 내놓으며 버전을 사용자 데이터베이스로 되돌린 이유가 이것이고, 그 과정에서 얻으려 한 상수 시간 abort는 PostgreSQL이 처음부터 갖고 있던 성질입니다.

MongoDB의 WiredTiger는 버전을 메모리 캐시에 델타로 들고 있다가 캐시를 넘치면 WiredTigerHS.wt 히스토리 저장소로 흘려보냅니다. 디스크의 dead tuple은 없지만 캐시 압력이 대신 옵니다. eviction이 따라가지 못하면 애플리케이션 스레드가 직접 eviction 작업을 떠맡아 전체 노드가 느려집니다.

CockroachDB나 YugabyteDB 같은 LSM 계열은 버전을 타임스탬프가 붙은 키로 저장하고 compaction으로 정리합니다. "vacuum이 없다"고 홍보하지만 compaction이 곧 vacuum입니다. 대신 GC 윈도를 넘긴 읽기는 실패합니다. CockroachDB의 gc.ttlseconds 기본값은 오랫동안 25시간이었는데 스케줄 백업이 들어온 뒤 4시간으로 낮아졌으니, 버전에 따라 창이 얼마나 좁은지 확인하고 써야 합니다. 삭제가 많은 테이블에서 tombstone이 쌓여 빈 테이블 스캔이 점점 느려지는 것도 이 계열의 특징입니다.

Kubernetes를 쓰면 매일 만지는 etcd도 사실상 같은 구조입니다. (key, revision)으로 버전을 쌓고 수동 compaction으로 회수합니다. 회수된 revision을 다시 읽으려 하면 etcdserver: mvcc: required revision has been compacted가 나오고, 백엔드 쿼터 2GB를 넘기면 제어 평면 쓰기가 거부됩니다. defrag는 VACUUM FULL과 같은 자리에 있는 의식입니다.

같은 사고, 엔진별로 다른 진단 쿼리​

위 표의 마지막 열은 결국 하나의 사건입니다. 누군가 트랜잭션을 오래 열어 둔 것입니다. 엔진마다 증상과 확인 방법이 다를 뿐입니다.

PostgreSQL에서는 backend_xmin을 봅니다. 아래 쿼리는 위 실험에서 실제로 범인을 찾는 데 사용한 것입니다.

SELECT pid, state,
       backend_xmin,
       age(backend_xmin)   AS xid_age,
       now() - xact_start  AS tx_age,
       left(query, 60)     AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

주의할 게 있습니다. 범인이 pg_stat_activity에만 있는 게 아닙니다. 비활성 replication slot과 준비된 트랜잭션도 똑같이 xmin을 붙듭니다. 셋을 같이 확인해야 합니다.

-- 비활성 replication slot
SELECT slot_name, slot_type, active, xmin, catalog_xmin
FROM pg_replication_slots
WHERE NOT active OR xmin IS NOT NULL;

-- 잊혀진 2PC 트랜잭션
SELECT gid, prepared, owner FROM pg_prepared_xacts;

MySQL InnoDB에서 같은 증상은 history list length로 나타납니다.

SHOW ENGINE INNODB STATUS\G
-- TRANSACTIONS 절의 "History list length" 값을 확인한다

SELECT trx_id, trx_state,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_sec,
       trx_mysql_thread_id, LEFT(trx_query, 60) AS query
FROM information_schema.innodb_trx
ORDER BY trx_started;

history list length가 계속 늘면 purge가 밀리고 있다는 뜻이고, 원인은 PostgreSQL과 똑같이 오래 열린 트랜잭션입니다. 격리 수준을 READ COMMITTED로 낮추면 InnoDB가 유지해야 할 히스토리가 줄어 완화되는 경우가 있습니다.

Oracle에서는 undo 사용량과 retention을 봅니다.

SELECT begin_time, undoblks, maxquerylen, ssolderrcnt
FROM   v$undostat
ORDER  BY begin_time DESC
FETCH  FIRST 12 ROWS ONLY;

ssolderrcnt가 ORA-01555 발생 횟수이고 maxquerylen이 가장 오래 돈 쿼리의 길이입니다. 이 둘이 함께 오르면 undo 보존 기간과 실제 쿼리 시간이 어긋나고 있다는 신호입니다.

SQL Server에서는 tempdb version store를 봅니다.

SELECT DB_NAME(database_id) AS db,
       reserved_page_count,
       reserved_space_kb / 1024 AS reserved_mb
FROM   sys.dm_tran_version_store_space_usage
ORDER  BY reserved_space_kb DESC;

이 값이 계속 오르면서 줄지 않으면 어딘가 스냅샷이 붙들려 있습니다. 인스턴스 공용 자원이므로 다른 데이터베이스 담당자도 같이 곤란해진다는 점에서 PostgreSQL보다 대응이 급합니다.

PostgreSQL 쿼리 세 개는 위 실험 환경에서 직접 실행해 확인했고, 나머지 엔진은 벤더 문서를 근거로 정리했습니다. 엔진을 옮겨도 물어야 할 질문은 하나로 같습니다. 지금 가장 오래 열려 있는 트랜잭션은 무엇이고, 그것이 무엇을 붙들고 있는지 물어야 합니다.

PostgreSQL만 하지 않는 선택 하나​

목록을 늘어놓다 보면 PostgreSQL이 특히 나빠 보이지만, 하나는 짚어 둘 만합니다. 위에 나열한 엔진 대부분은 어느 시점에 reader를 죽입니다. Oracle은 ORA-01555, WiredTiger는 타임스탬프 기반 거부, LSM 계열은 GC 윈도 초과, etcd는 compacted revision. FoundationDB는 아예 트랜잭션 수명을 5초로 강제합니다.

PostgreSQL은 기본 설정에서 읽기 쿼리를 취소하지 않습니다. 대신 그 쿼리가 볼지도 모르는 쓰레기를 계속 들고 있습니다. 결함으로 볼 일은 아닙니다. 선택입니다.

실제로 PostgreSQL도 한 번은 반대편을 시도했습니다. 9.6에 들어온 old_snapshot_threshold가 그것으로, 설정한 시간이 지나면 옛 스냅샷을 무효화하고 snapshot too old 오류를 던졌습니다. 그런데 이 기능은 vacuum이 아직 보이는 행을 지워 버릴 수 있는 정합성 문제를 안고 있었고, 고칠 계획이 서지 않은 채 시간이 흐르다 PostgreSQL 17에서 제거됐습니다. 더 나은 구현이 나오면 다시 들어올 여지는 남겨 뒀습니다.

즉 PostgreSQL은 다른 엔진의 실패 모드를 흉내 냈다가, 제대로 못 하겠으면 안 하는 쪽을 택했습니다.

남은 문제와 지금 진행 중인 것​

32비트 XID는 PostgreSQL만의 짐이 맞습니다. InnoDB의 DB_TRX_ID는 6바이트, 즉 48비트입니다. Oracle SCN도 원래 48비트였는데 12.2.0.1부터 compatible을 12.2로 올리면 상한이 2^63으로 넓어집니다. PostgreSQL은 여전히 32비트라 주기적으로 freeze를 돌려야 하고, 몇 달 동안 아무도 건드리지 않은 페이지까지 다시 써야 합니다. 2015년 Sentry의 wraparound 장애처럼 크게 터진 사례도 있습니다.

64비트 XID 패치는 Postgres Professional을 중심으로 수년째 논의 중이고 대부분의 상용 fork에는 이미 들어가 있지만, 본류에는 아직 없습니다. table access method 계층에서 처리해야 한다는 방향에는 합의가 있는 상태입니다.

저장 엔진 자체를 바꾸려는 시도도 이어집니다. zheap은 사실상 멈췄고, 지금 가장 활발한 것은 Supabase가 인수한 OrioleDB입니다. undo 기반으로 heap을 대체하는 extension이고 2026년 현재 퍼블릭 베타입니다. 벤치마크에서 최대 5.5배를 주장하지만 프로덕션 사용은 권장하지 않습니다.

당장 손에 잡히는 개선은 vacuum 쪽에서 옵니다. PostgreSQL 18은 autovacuum_worker_slots와 autovacuum_max_workers를 분리해 재시작 없이 worker 수를 조정하게 만들었고, 19에서는 autovacuum이 카탈로그 순서 대신 테이블별 우선순위 점수로 대상을 고르도록 바뀝니다. pg_stat_autovacuum_scores 뷰와 가중치 파라미터가 함께 들어옵니다. 청소 자체를 없애지는 못해도, 언제 무엇부터 치울지는 계속 정교해지는 중입니다.

정리​

MVCC 비용은 보존됩니다. 없앤 엔진은 없고, 어디로 보낼지만 다릅니다. PostgreSQL은 테이블 안에 쌓아 두고 나중에 치우는 쪽을 골랐습니다. 그 대가가 bloat와 vacuum 튜닝이고, 그 대신 얻은 것이 상수 시간 롤백과 취소당하지 않는 읽기 쿼리입니다.

그래서 "PostgreSQL의 MVCC는 나쁘다"는 문장은 절반만 맞습니다. 정확히 말하면 PostgreSQL은 시끄럽게 실패하고 수동으로 튜닝해야 하는 조합을 골랐습니다. undo 계열은 조용히 실패하고 자동으로 정리하지만, 실패할 때는 사용자 쿼리를 죽입니다.

다음에 누가 어떤 데이터베이스가 다중 버전 문제를 해결했다고 하면 네 가지를 물어보면 됩니다. 옛 버전은 어디에 사는지, 체인은 어느 방향인지, 인덱스는 무엇을 가리키는지, 누가 언제 치우는지 물으면 됩니다. 그리고 마지막으로 하나 더 묻습니다. 누군가 트랜잭션을 열어 놓고 점심을 먹으러 가면 무슨 일이 생기는지 묻습니다.

참고​

• PostgreSQL's MVCC is bad. So is everyone else's. (boringSQL, Radim Marek)이 글의 출발점이 된 원문입니다. 네 가지 질문 프레임과 엔진 비교 구성을 여기서 빌렸습니다
• The Part of PostgreSQL We Hate the Most (Andy Pavlo, CMU)
• Yes, PostgreSQL Has Problems. But We're Sticking With It. 위 글의 후속
• Why Uber Engineering Switched from Postgres to MySQL (Uber, 2016)과 Robert Haas의 반론
• PostgreSQL 17 릴리스 노트 old_snapshot_threshold 제거
• sys.dm_tran_version_store_space_usage (Microsoft Learn)
• Chasing a Hung MySQL Transaction: InnoDB History Length Strikes Back (Percona)
• OrioleDB

이 블로그의 관련 글로는 PostgreSQL 18의 autovacuum_worker_slots와 PostgreSQL은 왜 이건 아직 못 할까가 있어요.

PostgreSQL 18에서 autovacuum_max_workers가 드디어 SIGHUP로 받아지는 파라미터가 됐어요. 정확히는 한 파라미터가 둘로 쪼개졌어요 — 시작 시 한 번 예약하는 autovacuum_worker_slots(재시작 필요)와 그 범위 안에서 자유롭게 움직이는 autovacuum_max_workers(reload만으로 적용)예요. 운영자가 vacuum 부하를 보다가 worker 수를 늘리려고 maintenance window를 잡아야 했던 시대가 끝났어요.

이 글은 The Build의 Christophe Pettus가 "All Your GUCs in a Row" 시리즈에서 다룬 autovacuum_worker_slots 편을 한국어로 풀고, 함정 한두 가지를 더 짚습니다.

왜 max_workers는 재시작 파라미터였나​

PostgreSQL의 autovacuum launcher는 postmaster가 띄우는 백그라운드 프로세스입니다. launcher가 깨운 worker는 별도 프로세스로 동작하는데, 이 worker들이 자리 잡을 shared memory 구조는 postmaster가 시작될 때 한 번에 예약됩니다. autovacuum_max_workers가 그 크기를 결정했고, 따라서 변경하려면 재시작이 필요했습니다.

이 설정이 운영에서 만든 압박은 두 가지입니다.

첫째, 초기 과다 provisioning. "혹시 모르니 worker 16개 정도 확보해두자"는 식의 보수적 설정이 일반적이었습니다. 평소엔 절반도 안 쓰면서 shared memory를 점유합니다.

둘째, 워크로드 변동 대응 실패. 새 큰 테이블 N개를 한꺼번에 적재하는 야간 배치를 추가하면 vacuum 부담이 갑자기 커집니다. worker를 5개에서 10개로 늘리고 싶어도 그건 다음 maintenance window에서나 가능했습니다. 그동안 dead tuple은 쌓이고, bloat는 자라고, query latency는 흔들립니다.

"The cost of being wrong is a SIGHUP, not an outage." — Christophe Pettus, The Build

PostgreSQL 18은 이 비용을 SIGHUP 한 번으로 줄였습니다.

PG18의 worker_slots와 max_workers 분리​

원래 하나였던 GUC가 둘로 쪼개집니다.

• autovacuum_worker_slots: postmaster가 시작할 때 예약할 shared memory worker slot의 개수이며 재시작 파라미터입니다.
• autovacuum_max_workers: 위 slot 범위 안에서 실제로 동시에 깨어 있을 수 있는 worker의 상한이며 SIGHUP로 받아집니다.

쉽게 말하면 worker_slots는 주차장 크기, max_workers는 지금 동시에 받을 차의 수입니다. 주차장은 미리 지어둬야 하지만, 진입 제한은 그때그때 바꾸면 됩니다.

Before / After​

기본값이 worker_slots = 16으로 잡혀 있는 점에 주목할 만합니다. "어차피 사후에 늘리지 못 하니 처음부터 넉넉히 잡아두자"는 의도된 권장입니다.

구조도​

worker_slots는 부팅 때 한 번 결정되고, max_workers는 SIGHUP로 그때그때 위아래로 움직입니다.

동작 메커니즘​

세 가지를 짚어두면 충분합니다.

1. slot 예약은 postmaster 시작 시 한 번: worker_slots = 16이면 shared memory에 16개의 worker 자리가 잡힙니다. 이 크기는 실행 중 바꿀 수 없습니다.
2. max_workers > slots는 자동 capping: worker_slots = 16인데 max_workers = 20으로 reload하면 PostgreSQL은 16으로 capping하고 서버 로그에 경고를 남깁니다. 에러가 아니어서 reload는 성공하지만, 의도대로 동작하지 않는다는 신호입니다.
3. max_workers는 SIGHUP로 즉시 반영: pg_reload_conf() 한 번이면 다음 worker 사이클부터 새 상한이 적용됩니다.

maintenance window 없는 운영 튜닝 흐름​

새 흐름은 단순합니다.

설치할 때 worker_slots는 "최악의 상황에서 필요할 worker 수"에 맞춰 넉넉히 잡습니다. 16이면 대부분 충분하며, shared memory 비용은 worker 1개당 수 KB 수준이라 부담이 작습니다.

평상시에는 max_workers를 보수적으로 설정합니다(예: 3~5). vacuum이 늦지 않으면 그대로 유지합니다. 큰 테이블 적재, partition 증가, dead tuple 누적이 보이면 postgresql.conf에서 max_workers를 올리고 pg_reload_conf()를 실행합니다. 다음 vacuum 사이클부터 worker가 늘어납니다. 부하가 진정되면 다시 max_workers를 내리고 reload하며, shared memory는 그대로 두면 됩니다.

이 흐름에서 중요한 변화는 부하 증가에 대응하는 3번째 단계가 SIGHUP라는 점입니다. vacuum 부하를 더 받기 위해 데이터베이스를 내리고 다시 띄울 필요가 없습니다.

함정과 주의사항​

크게 셋입니다.

첫째, worker_slots의 hard ceiling. 설치 시점에 worker_slots를 짜게 잡으면 사후에 그 위로 올릴 수 없습니다. 16이 적당해 보여도, partition 1만 개에 야간 배치까지 도는 환경이라면 32로 잡아두는 편이 낫습니다. shared memory 예약 비용이 vacuum 지연 비용보다 훨씬 쌉니다.

둘째, worker_slots를 거꾸로 줄이고 싶을 때. 재시작이 필요합니다. "지금 max_workers = 5로 줄어들었으니 slot도 8 정도면 충분하지 않을까"는 자연스러운 생각이지만, 그 효과는 다음 재시작 때만 봅니다. 평소에는 그냥 두고, 다른 재시작 사유가 생겼을 때 함께 조정하는 게 실무적입니다.

셋째, parallel autovacuum과의 관계. PostgreSQL 18에서는 같은 시기에 autovacuum_max_parallel_workers도 들어왔습니다. 한 vacuum이 인덱스 정리를 위해 추가로 끌어쓰는 worker는 별도 카운팅이라, 동시에 도는 worker 수를 셀 때는 둘을 함께 봐야 합니다. 자세한 내용은 같은 저자의 "Parallel Autovacuum: It's Not About The CPU"에 잘 정리돼 있습니다.

넷째, max_workers를 올릴 때의 메모리 곱셈. worker_slots가 차지하는 shared memory는 slot 1개당 대략 5~20KB 수준입니다 — PGPROC 슬롯, PgBackendStatus 엔트리, LWLock 같은 자투리를 합산한 값입니다. worker_slots = 16으로 잡아도 총 수백 KB 안쪽이라 셋업 부담은 거의 없습니다. 하지만 slot이 깨어나서 실제 vacuum을 도는 동안에는 별도 비용이 붙습니다. autovacuum_work_mem(미설정 시 maintenance_work_mem, 기본 64MB)이 worker 프로세스의 private memory로 잡힙니다. max_workers = 16으로 올린 상태에서 16개가 동시에 도는 순간 OS RSS에 약 1GB가 추가된다는 뜻입니다. 정리하면 worker_slots는 넉넉히 키워도 무방하지만, max_workers는 RAM과 maintenance_work_mem의 곱셈을 같이 보고 결정합니다. "주차장은 크게, 동시 진입은 천천히"가 기준입니다.

maintenance window라는 비용​

OLTP 클러스터를 운영해 봤다면 maintenance window의 무게를 압니다. 사용자에게 공지를 띄우고, 야간 새벽 시간대를 잡고, 운영자 두세 명이 대기하는 비용입니다. autovacuum_max_workers 하나 늘리겠다고 그 비용을 다 치르기는 어렵습니다. 그래서 한참을 견딥니다 — dead tuple이 쌓이고, 큰 테이블이 bloat로 1.5배쯤 부풀고, query plan이 흔들리기 시작한 다음에야 다음 정기 점검에 끼워 넣습니다. PostgreSQL 18의 분리는 이 견디는 구간이 사라진다는 뜻입니다. dead tuple 알람이 뜨면 그날 안에 worker를 두세 개 더 풀어 vacuum을 따라잡게 한 뒤, 부하가 가라앉으면 다시 줄여둡니다. 운영자 입장에서는 vacuum 튜닝이 처음으로 "회의 없이 가능한 일"이 됐습니다.

정리​

PostgreSQL 18은 "worker 풀은 사전에 크게, 사용량은 그때그때"라는 평범한 운영 패턴을 vacuum에도 들여왔어요. 작은 변화 같지만 운영 자동화 관점에서는 큰 차이예요. 한밤중 dead tuple 누적 알림에 더 이상 maintenance window를 잡지 않아도 돼요.

참고 자료​

• All Your GUCs in a Row: autovacuum_worker_slots (The Build)
• Parallel Autovacuum: It's Not About The CPU (The Build)
• Do not change autovacuum age settings (The Build, 2019)
• PostgreSQL 18 Release Notes
• Routine Vacuuming (PostgreSQL Documentation)
• PostgreSQL 18의 비동기 I/O (이 블로그)
• PostgreSQL 19의 wal_level: 동적 floor 값으로