Postgres로 작업 큐 만들기 — SELECT … FOR UPDATE SKIP LOCKED 실전 가이드

왜 데이터베이스를 큐로 쓰나

가장 흔한 실수는 이렇다. 주문을 DB에 저장한 뒤, 별도로 RabbitMQ에 "이메일 보내기" 메시지를 넣는다. 그런데 DB 커밋은 성공했는데 브로커 전송이 실패하면? 혹은 브로커엔 들어갔는데 DB 트랜잭션이 롤백되면? 이 이중 쓰기(dual write) 문제는 분산 시스템의 고전적 함정이고, 이를 제대로 풀려면 outbox 패턴이나 2단계 커밋 같은 추가 복잡도가 필요하다.

DB를 큐로 쓰면 이 문제가 통째로 사라진다. 잡을 넣는 INSERT INTO jobs …가 비즈니스 데이터를 바꾸는 것과 동일한 트랜잭션 안에서 일어나기 때문이다. 주문이 커밋되면 잡도 반드시 있고, 롤백되면 잡도 없다. 원자성이 공짜로 따라온다.

여기에 운영상 이점이 더해진다. 큐의 상태를 SELECT 한 번으로 들여다볼 수 있고, 실패한 잡을 SQL로 재큐잉할 수 있으며, 백업·복제·모니터링을 이미 있는 DB 인프라로 처리한다. 새로운 미들웨어를 배우고, 배포하고, 죽지 않게 지키는 운영 부담이 없다.

SELECT … FOR UPDATE의 문제와 SKIP LOCKED의 원리

여러 워커가 동시에 큐에서 잡을 하나씩 집어가야 한다. 순진하게 SELECT … FOR UPDATE LIMIT 1을 쓰면 무슨 일이 벌어질까? 워커 A가 첫 행에 락을 건 순간, 워커 B의 동일 쿼리는 그 락이 풀릴 때까지 블로킹된다. 결국 워커들이 같은 행을 두고 줄을 서서, 병렬 처리가 아니라 사실상 직렬 처리가 된다.

SKIP LOCKED는 이 동작을 뒤집는다. 이름 그대로, 이미 다른 트랜잭션이 락을 건 행은 건너뛰고 잠기지 않은 다음 행을 즉시 반환한다. 워커 A가 행 1을 잡으면, 워커 B는 기다리지 않고 행 2를 잡는다. 블로킹이 없으니 워커 수만큼 처리량이 선형에 가깝게 늘어난다.

중요한 건 이게 단순한 성능 트릭이 아니라 정확성 보장이라는 점이다. 행 락은 트랜잭션 격리 수준과 무관하게 물리적으로 작동하므로, 두 워커가 같은 잡을 동시에 클레임하는 일이 원천적으로 불가능하다. 애플리케이션 레벨의 어설픈 락이나 UPDATE … WHERE status='pending'의 낙관적 경합 재시도 없이도 exactly-once 클레임이 성립한다.

잡 테이블 스키마와 상태 머신

큐의 심장은 테이블 하나다. 상태 머신은 단순하게 유지하는 게 좋다: queued → running → (succeeded | failed). 실패가 재시도 한도를 넘으면 dead로 보낸다. 아래 DDL은 프로덕션에서 바로 쓸 수 있는 최소이자 충분한 형태다.

CREATE TABLE jobs (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  queue        text        NOT NULL DEFAULT 'default',
  kind         text        NOT NULL,              -- 잡 종류(핸들러 라우팅)
  payload      jsonb       NOT NULL DEFAULT '{}',
  status       text        NOT NULL DEFAULT 'queued',
  priority     int         NOT NULL DEFAULT 0,     -- 클수록 먼저
  run_at       timestamptz NOT NULL DEFAULT now(), -- 예약·백오프의 기준
  attempts     int         NOT NULL DEFAULT 0,
  max_attempts int         NOT NULL DEFAULT 20,
  locked_at    timestamptz,                        -- 가시성 타임아웃 기준
  last_error   text,
  created_at   timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT jobs_status_chk
    CHECK (status IN ('queued','running','succeeded','failed','dead'))
);

-- 클레임 쿼리를 위한 부분 인덱스: 대기 중인 잡만 인덱싱한다.
-- 처리 완료된 수백만 행이 인덱스를 오염시키지 않게 하는 핵심.
CREATE INDEX jobs_claim_idx
  ON jobs (queue, priority DESC, run_at)
  WHERE status = 'queued';

여기서 WHERE status = 'queued' 부분 인덱스가 성능의 절반을 책임진다. 큐 테이블은 완료된 잡이 계속 쌓이는데, 클레임 쿼리가 관심 있는 건 오직 대기 중인 행뿐이다. 부분 인덱스는 인덱스 크기를 작게 유지해 스캔을 빠르게 하고, 완료 잡의 상태 변경이 인덱스를 갱신하지 않게 해 쓰기 비용도 줄인다.

원자적 클레임 쿼리

이제 핵심이다. 워커가 잡 하나(또는 배치)를 원자적으로 집어 오는 쿼리. CTE 안에서 SELECT … FOR UPDATE SKIP LOCKED로 후보를 잠그고, 곧바로 UPDATE로 상태를 running으로 바꾼 뒤 RETURNING으로 워커에게 돌려준다. 전부 한 문장, 한 트랜잭션이다.

-- 워커가 잡을 클레임하는 단일 원자적 쿼리
WITH picked AS (
  SELECT id
  FROM jobs
  WHERE status = 'queued'
    AND queue = 'default'
    AND run_at <= now()          -- 예약/백오프된 잡은 아직 제외
  ORDER BY priority DESC, run_at -- 우선순위 → 오래된 순
  FOR UPDATE SKIP LOCKED
  LIMIT 1                        -- 배치로 당기려면 여기를 늘린다
)
UPDATE jobs j
SET status    = 'running',
    locked_at = now(),
    attempts  = attempts + 1
FROM picked
WHERE j.id = picked.id
RETURNING j.id, j.kind, j.payload, j.attempts, j.max_attempts;

이 쿼리가 반환하는 행을 워커가 받아 처리한다. 성공하면 UPDATE jobs SET status='succeeded' WHERE id=$1, 혹은 아예 DELETE한다(완료 잡을 보관하지 않을 거라면 삭제가 테이블 비대화를 막는 가장 깔끔한 방법이다).

가시성 타임아웃 — 워커가 죽으면?

워커가 잡을 running으로 바꾸고 처리하던 중 프로세스가 죽으면(OOM, 배포 중 SIGKILL, 노드 장애) 그 잡은 영원히 running에 갇힌다. 아무도 다시 집어가지 않는 좀비 잡이 된다. 해법은 가시성 타임아웃(visibility timeout)이다: locked_at이 일정 시간보다 오래된 running 잡은 워커가 죽은 것으로 간주하고 다시 큐로 되돌린다.

-- 스위퍼: 죽은 워커가 붙잡고 있던 잡을 회수한다.
-- 주기적으로(예: 1분마다) 별도 워커나 pg_cron으로 실행.
UPDATE jobs
SET status = 'queued',
    locked_at = NULL,
    last_error = 'reclaimed: visibility timeout'
WHERE status = 'running'
  AND locked_at < now() - interval '5 minutes';

타임아웃 값은 가장 긴 정상 처리 시간보다 넉넉히 크게 잡아야 한다. 너무 짧으면 아직 정상 처리 중인 잡을 중복 실행하게 된다. 여기서 드러나는 근본 진실 하나: 이런 큐는 at-least-once 전달이다. 워커가 작업을 끝내고 상태를 커밋하기 직전에 죽으면, 그 잡은 반드시 다시 실행된다. 따라서 잡 핸들러는 반드시 멱등(idempotent)하게 설계해야 한다. 결제·이메일 같은 부수효과는 멱등 키로 방어하라.

재시도, 지수 백오프, dead letter

잡이 실패하면 곧바로 재시도하는 대신 점점 간격을 늘려 재시도한다(지수 백오프). 외부 API가 잠깐 죽었을 때 즉시 재시도 폭탄을 던지면 상황만 악화시킨다. run_at을 미래로 밀어 두면 클레임 쿼리의 run_at <= now() 조건이 자연스럽게 그 잡을 백오프 시간 동안 숨겨 준다. 별도 지연 큐가 필요 없다.

-- 잡 실패 처리: 재시도 여지가 있으면 백오프, 없으면 dead letter
UPDATE jobs
SET status = CASE
      WHEN attempts >= max_attempts THEN 'dead'
      ELSE 'queued'
    END,
    locked_at = NULL,
    last_error = $2,
    -- 지수 백오프 + 지터: 2^attempts 초, 최대 1시간, ±랜덤
    run_at = now() + least(
      interval '1 second' * power(2, attempts),
      interval '1 hour'
    ) * (0.5 + random() * 0.5)
WHERE id = $1;

지터(jitter)가 왜 필요한가? 배포 실패로 1000개 잡이 동시에 실패하면, 지터 없는 백오프는 그 1000개를 정확히 같은 시각에 재시도하게 만든다. 이 thundering herd가 방금 회복한 서비스를 다시 쓰러뜨린다. 각 잡의 재시도 시각에 랜덤을 섞으면 부하가 시간축에 고르게 퍼진다.

재시도 한도를 넘긴 잡은 dead 상태로 격리한다. 이게 dead letter다. 죽은 잡은 자동 재시도되지 않고 사람이 들여다볼 대상으로 남는다. 원인을 고친 뒤에는 SQL 한 줄로 되살릴 수 있다.

-- dead letter 재큐잉: 원인을 고친 뒤 특정 종류만 되살리기
UPDATE jobs
SET status = 'queued', attempts = 0, run_at = now(), last_error = NULL
WHERE status = 'dead' AND kind = 'send_invoice_email';

폴링 vs LISTEN/NOTIFY

워커는 새 잡이 있는지 어떻게 알까? 가장 단순한 건 폴링이다. 워커가 매 N밀리초마다 클레임 쿼리를 던지고, 잡이 없으면 잠깐 자고 다시 시도한다. 부분 인덱스 덕에 빈 큐 폴링은 매우 싸다(인덱스 끝만 훑는다). 대부분의 시스템엔 이 정도로 충분하다.

폴링의 단점은 지연-부하 트레이드오프다. 폴링 간격을 짧게 하면 지연은 줄지만 빈 쿼리가 늘고, 길게 하면 반대다. 이를 개선하려 Postgres의 LISTEN/NOTIFY를 얹을 수 있다. 잡을 넣을 때 NOTIFY로 워커를 깨우면, 유휴 시엔 폴링을 멈추고 알림이 오면 즉시 반응한다.

-- 프로듀서: 잡 삽입 후 채널에 알림
INSERT INTO jobs (kind, payload) VALUES ('resize_image', '{"id": 42}');
NOTIFY jobs_default;   -- 또는 트리거로 자동화

-- 워커: 알림 대기(드라이버 API로 LISTEN 후 이벤트 콜백)
LISTEN jobs_default;

처리량 한계, 인덱스, VACUUM 이슈

이 패턴의 진짜 적은 경합이 아니라 테이블 비대화(bloat)다. 잡을 끊임없이 INSERT하고 UPDATE/DELETE하면 죽은 튜플(dead tuple)이 빠르게 쌓인다. Postgres의 MVCC는 UPDATE를 "기존 행을 죽은 것으로 표시 + 새 버전 삽입"으로 처리하므로, 고회전 큐 테이블은 실제 살아있는 행 수에 비해 물리적으로 훨씬 커진다. VACUUM이 이 죽은 튜플을 회수하지 못하고 뒤처지면 클레임 쿼리가 죽은 튜플 무덤을 헤치며 느려진다.

• 완료 잡은 보관하지 말고 DELETE하라(또는 별도 아카이브 테이블로 이동). 큐 테이블은 작게 유지하는 게 전부다.
• 이 테이블만 autovacuum을 공격적으로 튜닝하라: autovacuum_vacuum_scale_factor를 0.01처럼 낮추고 autovacuum_vacuum_cost_delay를 줄여 더 자주 돌게 한다.
• 긴 트랜잭션·유휴 트랜잭션을 죽여라. 오래된 스냅숏 하나가 전체 테이블의 VACUUM을 막는다. idle_in_transaction_session_timeout을 설정하라.
• 파티셔닝이나 완료 잡을 담는 별도 아카이브 테이블로 핫 테이블을 얇게 유지하면 인덱스와 VACUUM 모두 유리하다.

현실적인 처리량 상한은 어디쯤일까. 잘 튜닝한 단일 Postgres는 초당 수천 건의 클레임을 무리 없이 소화한다. 워커를 늘려도 SKIP LOCKED 덕에 경합으로 무너지지 않는다. 하지만 초당 수만~수십만 건의 지속 처리량이 필요하다면, 매 잡마다 발생하는 행 UPDATE와 VACUUM 압력이 병목이 된다. 그 지점이 전용 큐를 고민할 신호다.

언제 전용 큐(SQS·RabbitMQ·Kafka)로 넘어갈까

Postgres 큐는 훌륭한 출발점이지 만능이 아니다. 다음 신호가 보이면 전용 인프라를 진지하게 검토하라.

핵심 판단 기준은 이렇다. 잡이 데이터베이스 트랜잭션과 강하게 엮여 있고, 처리량이 초당 수천 건 이하이며, 운영 단순함이 소중하다면 Postgres 큐가 정답이다. 반대로 스트리밍·이벤트 소싱·초고처리량·팬아웃 라우팅이 본질적 요구라면 그것을 위해 만들어진 도구를 써라. 성급하게 Kafka부터 도입해 운영 복잡도를 떠안는 것보다, Postgres로 시작해 실제 한계에 부딪혔을 때 옮기는 편이 거의 항상 낫다.

자주 묻는 질문

SKIP LOCKED를 쓰면 잡을 정확히 한 번만 처리하나요?

클레임 자체는 정확히 한 번입니다(두 워커가 같은 잡을 동시에 running으로 바꿀 수 없습니다). 하지만 전체 시스템은 at-least-once입니다. 워커가 작업을 끝내고 성공 상태를 커밋하기 전에 죽으면, 가시성 타임아웃 후 그 잡이 다시 실행됩니다. 따라서 잡 핸들러는 반드시 멱등하게 만들고, 외부 부수효과에는 멱등 키를 두세요.

완료된 잡은 지워야 하나요, 남겨야 하나요?

고회전 큐라면 DELETE를 강력히 권합니다. 완료 잡을 같은 테이블에 쌓아 두면 테이블·인덱스가 비대해지고 VACUUM 부담이 커집니다. 감사(audit)나 재실행 이력이 필요하면, 완료 시점에 별도 jobs_archive 테이블로 옮기고 핫 큐 테이블은 얇게 유지하세요.

run_at은 예약 실행과 백오프에 어떻게 동시에 쓰이나요?

둘 다 "이 시각 전에는 이 잡을 보이지 않게 한다"는 같은 메커니즘입니다. 클레임 쿼리의 run_at <= now() 조건이 미래 시각의 잡을 자동으로 걸러 줍니다. 예약 잡은 삽입 시 run_at을 미래로 설정하고, 백오프는 실패 시 run_at을 미래로 미룹니다. 별도의 지연 큐나 스케줄러 테이블이 필요 없습니다.

LISTEN/NOTIFY만 쓰고 폴링은 없애도 되나요?

권장하지 않습니다. NOTIFY는 신뢰성 보장이 없어(리스너가 없을 때 온 알림은 유실되고, run_at으로 예약된 잡이나 스위퍼가 되돌린 잡은 알림을 발생시키지 않음) 알림만 믿으면 잡이 영영 처리되지 않을 수 있습니다. NOTIFY로 지연을 줄이되, 몇 초 간격의 느린 폴링을 안전망으로 반드시 병행하세요.

워커를 몇 개까지 늘릴 수 있나요?

SKIP LOCKED는 워커가 늘어도 서로 다른 행을 집어가므로 락 경합으로 무너지지 않습니다. 실제 한계는 워커 수보다 DB 커넥션 수와 매 클레임마다 발생하는 쓰기·VACUUM 압력입니다. 커넥션은 PgBouncer 같은 풀러로 관리하고, 처리량이 초당 수천을 넘어 지속적으로 필요해지면 전용 큐를 검토할 시점입니다.

트랜잭션 안에서 잡을 넣었는데 롤백되면요?

그게 바로 이 패턴의 가장 큰 장점입니다. 잡 INSERT가 비즈니스 트랜잭션과 같은 트랜잭션에 있으면, 롤백 시 잡도 함께 사라집니다. "주문은 취소됐는데 확인 이메일 잡은 살아남는" 이중 쓰기 불일치가 구조적으로 불가능합니다. 외부 브로커로는 outbox 패턴 없이 얻기 어려운 보장입니다.