본문으로 건너뛰기
wooncloud

PostgreSQL 대형 테이블에 순번 컬럼 추가, 재작성 없이 기존 행을 생성 순으로 채번하기

BIGSERIAL 한 줄은 테이블 전체를 다시 쓰고 서비스를 멈춥니다. 짧은 잠금으로 경계를 정하고 배치로 기존 행을 채번해 무중단으로 순번 컬럼을 붙인 과정과 함정을 정리했습니다.

·8 min read·— views··
#postgresql#migration#sequence#zero-downtime

PK 가 nanoid 같은 무작위 문자열인 테이블에 순번 컬럼을 추가할 일이 생겼습니다. 외부 배치가 WHERE id BETWEEN :start AND :end 처럼 숫자 범위로 나눠 읽게 하려는 거였죠.

조건은 세 가지였습니다. 기존 행은 생성 순으로 1..N 을 받고, 새 행은 N+1 부터 이어 받고, 서비스는 멈추지 않는다.

BIGSERIAL 한 줄이면 안 될까요?

가장 먼저 떠오르는 건 ALTER TABLE t ADD COLUMN seq_no BIGSERIAL 입니다. GENERATED … AS IDENTITY 나 NOT NULL DEFAULT nextval(...) 도 마찬가지고요.

그런데 이건 테이블 전체를 다시 씁니다. 휘발성 DEFAULT 는 행마다 값을 계산해야 해서, 그동안 ACCESS EXCLUSIVE 잠금이 걸리고 읽기와 쓰기가 모두 멈춥니다.

게다가 번호가 물리 순서로 매겨집니다. UPDATE 를 거친 행은 힙의 다른 위치로 옮겨 가 있으니 결과가 생성 순이 아닙니다.

또 하나, 새 행과 번호가 겹치지 않으려면 기존 행이 몇 개인지 정확히 알아야 합니다. 그런데 세는 동안에도 INSERT 가 들어옵니다.

경계는 짧은 잠금 한 번으로 확정합니다

잠금은 딱 한 번, 짧게만 잡습니다. DO 블록 하나 안에서 LOCK TABLE … IN ACCESS EXCLUSIVE MODE 를 잡고, count(*) 로 N 을 세고, setval(seq, N+1, false) 를 호출한 뒤, nullable 컬럼을 추가하고 SET DEFAULT nextval(seq) 를 겁니다.

nullable 컬럼 추가와 SET DEFAULT 는 카탈로그만 바꾸므로 재작성이 없습니다. 이 뒤로 들어오는 새 행은 N+1 부터 번호를 받고, 기존 N 행만 NULL 로 남습니다.

기존 행 번호는 채번표로 매깁니다. row_number() OVER (ORDER BY created_at, id) 로 1..N 을 미리 적어 두고, 동률 시각은 PK 로 가릅니다.

그다음 채번표 앞쪽부터 대상 행을 FOR UPDATE SKIP LOCKED 로 잡아 번호를 기록하고, 기록한 것만 채번표에서 지웁니다. 한 문장 CTE 로 묶으면 실패했을 때 함께 롤백됩니다. 사용자가 잡고 있는 행은 기다리지 않고 건너뛰니, 다건 삭제 같은 작업과 교착하지 않습니다.

마지막으로 제약은 뒤에서 가볍게 겁니다. CREATE UNIQUE INDEX CONCURRENTLY 를 만들고, CHECK (col IS NOT NULL) NOT VALID 를 VALIDATE 한 다음 SET NOT NULL 을 합니다. PG12 이상은 검증된 CHECK 가 있으면 SET NOT NULL 이 테이블을 다시 훑지 않습니다.

DO $$
DECLARE n bigint;
BEGIN
  PERFORM set_config('lock_timeout', '3s', true);
  LOCK TABLE items IN ACCESS EXCLUSIVE MODE;
  SELECT count(*) INTO n FROM items;
  CREATE SEQUENCE items_seq_no_seq;
  PERFORM setval('items_seq_no_seq', n + 1, false);
  ALTER TABLE items ADD COLUMN seq_no BIGINT;                         -- 재작성 없음
  ALTER TABLE items ALTER COLUMN seq_no SET DEFAULT nextval('items_seq_no_seq');
  ALTER SEQUENCE items_seq_no_seq OWNED BY items.seq_no;
END $$;
 
CREATE TABLE items_seq_map AS
SELECT id, row_number() OVER (ORDER BY created_at, id) AS seq_no
  FROM items WHERE seq_no IS NULL;
 
-- 0행이 될 때까지 반복
WITH picked AS (
  SELECT m.id, m.seq_no FROM items_seq_map m JOIN items i ON i.id = m.id
   WHERE i.seq_no IS NULL ORDER BY m.seq_no LIMIT 5000
     FOR UPDATE OF i SKIP LOCKED
), done AS (
  UPDATE items i SET seq_no = p.seq_no FROM picked p WHERE i.id = p.id RETURNING i.id
)
DELETE FROM items_seq_map m USING done WHERE m.id = done.id;

겪을 만한 함정들

약한 잠금에서 시작해 올리면 안 됩니다. SHARE ROW EXCLUSIVE 로 쓰기만 막고 세다가 ADD COLUMN 에서 ACCESS EXCLUSIVE 로 올리면, "SELECT 는 끝냈고 INSERT 는 아직"인 트랜잭션과 교착합니다. 처음부터 강한 잠금을 잡으면 PG 가 대기 큐를 재정렬해 교착을 풀어 줍니다.

스냅샷이 잠금보다 먼저 고정되면 count 가 모자랍니다. 세션이 REPEATABLE READ 면 잠금을 기다리는 동안 커밋된 행이 count 에서 빠지고 번호가 겹칩니다. 그래서 DO 안에서 current_setting('transaction_isolation') 을 확인합니다.

시퀀스 권한도 챙겨야 합니다. 앱 롤이 테이블 소유자가 아니면 DEFAULT 의 nextval 이 permission denied for sequence 로 모든 INSERT 를 깨뜨립니다. 시퀀스 소유자를 테이블 소유자로 맞추고, 테이블 INSERT 권한이 있는 롤(aclexplode(relacl))에 USAGE, SELECT 를 같은 트랜잭션에서 줍니다.

스키마 파일의 가드는 한 문장이어야 합니다. "행이 있으면 RAISE" 하는 DO 뒤에 ADD COLUMN … NOT NULL DEFAULT nextval 을 별도 문장으로 두면, psql -f(기본 ON_ERROR_STOP off)는 예외 뒤에도 다음 문장을 실행합니다. 가드가 막으려던 재작성이 그대로 일어나는 거죠. 판정과 생성을 같은 DO 안에 넣어야 합니다.

마지막으로 순번은 커밋 순서가 아닙니다. 번호는 INSERT 시점에 할당되므로, max(seq_no) 를 증분 워터마크로 쓰면 늦게 커밋된 작은 번호를 영구히 놓칩니다. 증분 소비자는 겹침 창을 둬야 합니다.

사실 BIGSERIAL 한 줄이면 끝날 것 같았던 작업이 이렇게 길어질 줄은 몰랐습니다. 대형 테이블이라면 한 번쯤 짚고 갈 만한 내용이라 메모 겸 남겨 둡니다.

참고