Postgres 큐를 건강하게 유지하기

• Postgres에서 job queue를 운영할 때 발생하는 고질적 문제인 dead tuple 누적과 이에 따른 테이블 bloat, 성능 저하의 원인과 해결 방법 정리
• 큐 테이블은 대부분의 행이 삽입-읽기-삭제로 짧게 순환되므로 크기는 일정하지만 누적 처리량이 매우 큼
• Postgres의 MVCC 구조상 삭제된 행은 즉시 제거되지 않고 dead tuple로 남아 정리되어야 하며, autovacuum이 이를 담당함
• 장기 실행 트랜잭션이나 겹쳐 실행되는 분석 쿼리가 MVCC horizon을 고정시키면 autovacuum이 dead tuple을 정리하지 못해 큐 성능이 저하됨
• PlanetScale의 Traffic Control(Insights 확장) 기능이 쿼리 클래스별 리소스 제한으로 이 문제를 해결하는 실용적 수단으로 제시됨

---

큐 워크로드의 특성

• 큐 테이블의 고유한 특징은 대부분의 행이 일시적(transient) 이라는 점 — 삽입되고, 한 번 읽히고, 삭제됨
• 테이블 크기는 거의 일정하지만 누적 처리량은 막대함
• Postgres에 job queue를 두는 주요 이점은 job 상태와 다른 DB 로직을 동일 트랜잭션으로 동기화 가능
  • job이 실패하면 전체 트랜잭션이 롤백
  • 외부 큐 서비스를 쓰면 애플리케이션의 트랜잭션 상태와 동기 맞추는 작업이 복잡해짐

예시 큐 테이블과 워커 동작

• 본문에서 제시한 기본 스키마

CREATE TABLE jobs ( id BIGSERIAL PRIMARY KEY, run_at TIMESTAMPTZ DEFAULT now(), status TEXT DEFAULT 'pending', payload JSONB ); CREATE INDEX idx_jobs_fetch ON jobs (run_at) WHERE status = 'pending';

• 워커는 트랜잭션을 열고 가장 오래된 pending job을 FOR UPDATE SKIP LOCKED로 잠가 중복 처리 방지
• 작업 성공 시 DELETE 후 COMMIT, 실패 시 롤백되어 해당 행은 다시 다른 워커에게 보이게 됨
• 이 트랜잭션은 가능한 한 짧게 유지해야 함 — 오래 열려 있을수록 vacuum을 막기 때문 (본문 예시는 서브밀리초 워커 기준)

성능 자체가 문제가 아님

• Postgres는 대규모 job queue를 처리할 수 있음이 이미 문서화되어 있으며, 능력 자체는 문제되지 않음
• 진짜 문제는 같은 DB에서 경쟁하는 다른 워크로드들과의 공존
• 큐 테이블의 건강은 자체 설정뿐 아니라 같은 Postgres 인스턴스에서 돌아가는 모든 트랜잭션의 동작에 좌우됨
• 본문은 프라이머리에서의 경쟁 쿼리 트래픽에 초점 (복제본·replication slot 영향은 별도)

진짜 문제: dead tuple 정리

• Postgres는 MVCC로 동일 행의 여러 버전을 유지함 — 삭제된 행은 즉시 제거되지 않고 “삭제 표시” 후 새 트랜잭션에 비가시 상태로 남음
• 이렇게 남은 행이 dead tuple이며 vacuum 연산으로 정리됨
• dead tuple은 SELECT 결과에 나오지 않지만 여전히 비용을 발생시킴
  • Sequential scan: 실행기가 heap page에서 dead tuple을 읽고 가시성 확인 후 버림
  • Index scan(큐에서 ORDER BY run_at LIMIT 1로 사용하는 방식): B-tree 인덱스가 dead tuple 참조를 누적해, 더 이상 보이지 않는 행을 가리키는 항목까지 훑게 됨
• 각 dead index entry는 추가 I/O를 유발, 애플리케이션에서는 보이지 않지만 dead tuple 수에 따라 비용이 크게 증가
• 정리 주기는 autovacuum_naptime(기본 1분), 실행 여부는 autovacuum_vacuum_threshold와 autovacuum_vacuum_scale_factor에 따라 결정됨

dead tuple 내부 메커니즘

• 행 메타데이터 3가지가 핵심
  • ctid: heap 내 튜플의 물리적 위치 (page, offset)
  • xmin: 이 행을 삽입한 트랜잭션 ID (XID)
  • xmax: 이 행을 삭제/잠근 트랜잭션 ID, 0이면 삭제 표시 없음
• pending 3건 조회 시에도 실행기는 이전에 삭제된 6건의 dead tuple을 모두 스캔한 후 3건만 반환하는 상황이 발생 가능
• 인덱스 역시 leaf entry가 dead인 heap tuple을 가리키는 경우, scan 과정에서 낭비되는 작업이 누적됨
• DB가 dead tuple 생성 속도보다 정리 속도가 느려지면 실패 궤도로 진입
• 잘 튜닝된 Postgres 클러스터는 초당 수만 건의 큐 처리량 감당 가능

autovacuum이 무력해지는 상황

• autovacuum이 dead tuple 정리에 실패하는 주요 원인
  • 특정 테이블 lock이 cleanup 차단
  • 부적절한 autovacuum 설정
  • 가장 흔하게는 활성 트랜잭션이 dead tuple 회수를 막는 경우
• Postgres는 활성 트랜잭션에 아직 보일 수 있는 dead tuple은 vacuum하지 않음
  • 가장 오래된 활성 트랜잭션이 cutoff를 설정 → MVCC horizon
  • 해당 트랜잭션이 끝날 때까지, 그 스냅샷 이후의 모든 dead tuple이 유지됨
• 2분짜리 단일 트랜잭션 하나가 2분 동안 horizon을 고정
• 동일한 실패 패턴이 겹치는 중간 길이 쿼리들로도 발생
  • 예: 40초짜리 분석 쿼리 3개를 20초 간격으로 엇갈려 실행하면, 개별 쿼리는 타임아웃되지 않지만 항상 하나는 활성 상태여서 horizon이 전진하지 못함
• “Just use Postgres” 철학으로 여러 워크로드를 한 DB에 두면, 빠른 job 처리 자체가 아니라 겹치는 느린 쿼리 때문에 dead tuple 정리가 밀리는 것이 문제

기존 도구와 한계

• autovacuum 튜닝 옵션: autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limit
• 장기 실행 쿼리 제한용 타임아웃
  • statement_timeout (Postgres 7.3): 지정 시간을 넘는 개별 SQL 문을 종료
  • idle_in_transaction_session_timeout (9.6): 트랜잭션 내부에서 유휴 상태가 지속되는 세션 종료
  • transaction_timeout (17.0): 활성·비활성 트랜잭션이 지정 시간 초과 시 종료
• 이 타임아웃들은 단일 쿼리 실행 시간만 겨냥, 동시성·실행 비용은 제한하지 못해 MVCC horizon을 지속적으로 고정하는 워크로드 차단에는 부적합
• 필요한 것은 트래픽 클래스별 구분, 고우선 워크로드는 그대로 두고 저우선 워크로드의 리소스 사용률만 조절하는 수단

Database Traffic Control™

• PlanetScale이 개발한 Insights 확장의 일부로 PlanetScale Postgres 전용 기능
• 개별 쿼리 성능과 리소스 사용량을 세밀하게 제어할 때 사용
• Resource Budget으로 대상 쿼리에 리소스 한도 부여 → 초과 시 차단 가능
• 해결 전략은 겹치는 느린 쿼리의 동시 실행 수와 빈도를 제한, autovacuum이 적절한 속도로 dead tuple을 정리할 여유 확보
• 차단된 쿼리는 영구 거부가 아니라 재시도되도록 애플리케이션의 retry 로직이 필수
• 전체 작업량은 유지하면서 수행 속도를 평탄화하는 접근

데모 구성과 배경

• 이 글의 착안점은 2015년 Brandur Leach의 블로그 "Postgres Job Queues & Failure By MVCC"
  • Postgres 기반 job queue의 치명적 실패 모드를 기록
  • 닫히지 않은 트랜잭션이 MVCC horizon을 고정해 cleanup을 막는 현상을 실증하는 테스트 벤치 포함
• 원 테스트 벤치는 brandur/que-degradation-test로 공개되어 있음

문제 재현 (Postgres 18 기준)

• 원 테스트는 Ruby + Que gem v0.x + Postgres 9.4 기반
• 저자는 SQL 레벨 동작만 격리 검증하기 위해 TypeScript + Bun으로 재작성
• Que와 동일한 recursive CTE 패턴, 동일 schema, producer rate, work duration, worker 수, long-runner 패턴 유지
• PlanetScale PS-5 클러스터에서 실행 (월 $5부터 시작)
• 결과: 가시적이지만 관리 가능한 수준의 성능 저하
  • 원 테스트는 15분 내 DB를 death spiral로 몰아넣었으나, PS-5에서는 15분간 워커 큐를 0 근처로 유지
  • 그러나 dead tuple이 선형적으로 증가해, 시간이 더 지나면 동일 문제가 재발할 조짐
  • B-tree 인덱스 정리 개선(버전 churn에 대한 bottom-up deletion, scan 기반 dead index tuple 제거 등) 덕에 완화되었지만 제거된 것은 아님

개선 시도: SKIP LOCKED + 배치 처리

• 2015년에 없던 2가지 현대적 개선
  1. FOR UPDATE SKIP LOCKED — recursive CTE 전체를 단일 SELECT 로 대체, 다른 워커가 잠근 행은 건너뜀
  2. Batch processing(트랜잭션당 10 jobs) — 한 번의 lock acquisition으로 10개 처리, 인덱스 스캔 비용을 분산
• 조건 동일: worker 8, producer 50 jobs/sec, work 10ms, long-runner 45초 후 시작
• 주요 결과

• degradation 곡선은 거의 동일 — 두 방식 모두 같은 B-tree 인덱스를 스캔하고 같은 dead tuple을 만나기 때문
• throughput 차이는 lock 전략이 아닌 테스트 설계 영향 (CTE 워커는 producer보다 빠르게 job을 잡아감, batch 워커는 큐를 비우고 backoff sleep)
• 결론: 10년 전 15분 만에 DB를 죽이던 큐 설계가 이제는 더 오래 버티지만, 근본 문제는 남아 있음 — 500 jobs/sec로 올리면 문제는 더 빨리 재현

Traffic Control로 해결

• Resource Budget이 제공하는 제어 수단
  • Server share & burst limit: 서버 리소스 비율과 소비 속도
  • Per-query limit: 서버 사용량 기준 초 단위로 쿼리 실행 가능 시간
  • Maximum concurrent workers: 가용 워커 프로세스 대비 비율
• 쿼리 대상 지정은 주로 SQLCommenter 태그의 메타데이터로 수행 (예: action=analytics)
• idle_in_transaction_session_timeout으로 잡히는 long-runner 대신, 더 현실적인 시나리오인 활성 작업 중인 겹치는 분석 쿼리들로 degradation을 유발 (세션 타임아웃으로는 못 잡는 케이스)
• action=analytics 쿼리의 Maximum concurrent workers를 1 워커(max_worker_processes의 25%)로 제한 → 분석 쿼리 1개만 동시 실행
• 15분 창 안에서 death spiral을 유도하기 위해 producer를 800 jobs/sec로 증가
• EC2에서 동일 PlanetScale DB 대상으로 "enhanced" 워크로드 2회 실행
  • 800 jobs/sec
  • 120초짜리 분석 쿼리 3개 동시 실행, 계속 겹치도록 엇갈려 배치
  • 15분 지속
• 결과 비교

• Traffic Control은 특정 워크로드의 동시성을 직접 제한, autovacuum 튜닝·타임아웃으로는 불가능했던 제어를 제공
• 분석 리포트는 용량이 허용하는 범위에서 계속 실행되어 15분 동안 15건 완료, 큐는 내내 건강한 상태 유지

정리

• Postgres 기반 큐의 MVCC dead tuple 문제는 2015년의 유물이 아님
• 현대 Postgres는 B-tree 개선과 SKIP LOCKED 로 상당한 여유를 제공하지만 근본 메커니즘은 동일
  • VACUUM이 dead tuple을 정리하지 못하면 누적됨
  • 장기 실행·겹치는 트랜잭션이 MVCC horizon을 고정하면 VACUUM이 정리하지 못함
• “Just use Postgres”로 큐·분석·앱 로직을 한 DB에 두는 환경에서 이는 이론적 위험이 아닌 평상시의 운영 조건
• 위험한 형태는 극적인 크래시가 아니라 조용히 열화되는 균형 상태 — lock time이 서서히 늘고, job이 느려지며, 알람은 울리지 않음
• Postgres의 타임아웃 도구는 워크로드 클래스 구분이나 동시성 제한이 불가능함
• 큐를 다른 워크로드와 같이 돌린다면, 가장 효과적인 조치는 VACUUM이 따라갈 수 있도록 보장하는 것이며, Traffic Control이 이를 단순화함