---
title: "PostgreSQL 대형 테이블에 순번 컬럼 추가, 재작성 없이 기존 행을 생성 순으로 채번하기"
description: "BIGSERIAL 한 줄은 테이블 전체를 다시 쓰고 서비스를 멈춥니다. 짧은 잠금으로 경계를 정하고 배치로 기존 행을 채번해 무중단으로 순번 컬럼을 붙인 과정과 함정을 정리했습니다."
date: 2026-10-29
updated: 2026-10-29T02:00:48.779Z
tags: [postgresql, migration, sequence, zero-downtime]
canonical: https://blog.wooncloud.com/posts/postgres-add-sequence-column-large-table
---

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` 잠금이 걸리고 읽기와 쓰기가 모두 멈춥니다.

![BIGSERIAL 한 줄은 테이블 전체를 재작성하며 긴 ACCESS EXCLUSIVE 잠금을 부르지만, 짧은 잠금으로 경계만 정하고 배치로 채우면 서비스가 멈추지 않는다.](/images/posts/postgres-add-sequence-column-large-table/10558e48-38e4-4c0d-a30a-949272b69fa9.webp)

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

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

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

![BIGSERIAL 한 줄은 전체 재작성과 긴 ACCESS EXCLUSIVE 잠금을 부르지만, 짧은 잠금으로 경계를 정하고 nullable 컬럼을 추가한 뒤 배치로 번호를 채우면 서비스를 멈추지 않고 순번을 붙일 수 있다.](/images/posts/postgres-add-sequence-column-large-table/6a1fd529-0cc4-43e8-9fb1-968f5bd47c58.svg)

잠금은 딱 한 번, 짧게만 잡습니다. `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` 이 테이블을 다시 훑지 않습니다.

```sql
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 한 줄이면 끝날 것 같았던 작업이 이렇게 길어질 줄은 몰랐습니다. 대형 테이블이라면 한 번쯤 짚고 갈 만한 내용이라 메모 겸 남겨 둡니다.

## 참고

- PostgreSQL ALTER TABLE (Notes 절, 휘발성 DEFAULT 는 재작성, SET NOT NULL 과 검증된 CHECK): https://www.postgresql.org/docs/current/sql-altertable.html
- PostgreSQL Explicit Locking: https://www.postgresql.org/docs/current/explicit-locking.html
- PostgreSQL CREATE INDEX CONCURRENTLY: https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY
- PostgreSQL SELECT, SKIP LOCKED: https://www.postgresql.org/docs/current/sql-select.html#SQL-FOR-UPDATE-SHARE
- psql ON_ERROR_STOP: https://www.postgresql.org/docs/current/app-psql.html