Postgres ID가 번호를 건너뛰는 이유와 가져오기 후 "duplicate key" 오류를 고치는 방법

Postgres ID에 관해 반복해서 나오는 질문이 두 가지 있습니다. "왜 ID가 5에서 34로 건너뛰었나요?"와 "데이터를 가져온 직후 왜 모든 삽입이 duplicate key value violates unique constraint \"..._pkey\"로 실패하나요?"입니다. 둘 다 같은 객체, 즉 serial 또는 identity 열 뒤에 있는 시퀀스에서 비롯됩니다. 시퀀스는 번호가 실제로 쓰이는지 신경 쓰지 않고 번호를 나눠 주므로, 빈 번호가 생기는 것은 정상입니다. 또한 시퀀스는 자기가 직접 나눠 준 번호만 알기 때문에, 명시적인 ID와 함께 적재한 행은 시퀀스를 뒤처지게 만듭니다. 이 글은 각 종류의 빈 번호를 재현하고, 가져오기 오류를 테이블 하나에 대해, 그다음 모든 테이블에 대해 한 번에 고치고, integer ID가 바닥나면 어떻게 되는지 보여 주며, 실제로 필요할 수 있는 단 한 곳을 위해 빈 번호 없는 번호 매기기를 만듭니다. 아래의 모든 문장과 출력은 Relyt One이 실행하는 메이저 버전인 로컬 PostgreSQL 17.11에서 나온 것입니다.

PostgreSQL 문서는 이 점을 직설적으로 말합니다. nextval이 반환한 값은 "is not reclaimed for re-use if the calling transaction later aborts"(호출한 트랜잭션이 나중에 중단되더라도 재사용을 위해 회수되지 않는다)이며, "sequence objects cannot be used to obtain 'gapless' sequences."(시퀀스 객체로는 '빈 번호 없는' 순번을 얻을 수 없다.) 이것은 의도된 절충입니다. 롤백된 트랜잭션이 번호를 돌려줄 수 있다면, 번호를 요청하는 다른 모든 트랜잭션은 그 트랜잭션이 번호를 돌려주는지 지켜보며 기다려야 합니다. 실제로 빈 번호가 어떻게 생기는지 보겠습니다.

CREATE TABLE orders (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE
);

INSERT INTO orders (email) VALUES ('a@example.com');           -- 1을 가져감

BEGIN;
INSERT INTO orders (email) VALUES ('b@example.com');           -- 2를 가져감
ROLLBACK;                                                      -- 2는 사라짐

INSERT INTO orders (email) VALUES ('c@example.com');           -- 3을 가져감
INSERT INTO orders (email) VALUES ('a@example.com')
  ON CONFLICT (email) DO NOTHING;                              -- 4를 소모함
INSERT INTO orders (email) VALUES ('a@example.com');           -- 5를 소모하고 실패함
INSERT INTO orders (email) VALUES ('d@example.com');           -- 6을 가져감

SELECT id, email FROM orders ORDER BY id;

 id |     email
----+---------------
  1 | a@example.com
  3 | c@example.com
  6 | d@example.com
(3 rows)

행 세 개에 빈 번호 세 개입니다. 롤백된 삽입은 2를 가져갔습니다. 고유 email 제약 조건에서 실패한 삽입은 실패하기 전에 5를 가져갔습니다. Postgres는 제약 조건을 검사하기 전에 기본 ID를 포함한 행 전체를 만들기 때문입니다. ON CONFLICT DO NOTHING은 같은 이유로 아무것도 삽입하지 않고 4를 가져갔습니다. 문서에도 명시되어 있습니다. INSERT ... ON CONFLICT는 행을 계산하며, 여기에는 "including doing any required nextval calls, before detecting any conflict."(충돌을 감지하기 전에 필요한 nextval 호출을 수행하는 것까지 포함된다.) 애플리케이션이 같은 행을 계속 upsert한다면, 시퀀스는 행 수보다 훨씬 빠르게 올라갈 수 있습니다. (upsert는 INSERT ... ON CONFLICT vs MERGE에서 자세히 다뤘습니다.)

nextval마다 WAL 레코드를 쓰면 느리므로, Postgres는 시퀀스를 앞당겨 기록합니다. 레코드를 쓸 때 32만큼 앞선 값을 기록하고, 그 사이의 번호는 다시 기록하지 않고 나눠 줍니다. 이 상수는 서버 소스의 SEQ_LOG_VALS이며, 그 옆의 주석은 이렇게 말합니다. "In the event of crash we can lose (skip over) as many values as we pre-logged."(장애가 발생하면 미리 기록한 만큼의 값을 잃을(건너뛸) 수 있다.)

직접 확인할 수 있습니다. log_cnt는 미리 기록된 값이 몇 개 남았는지를 나타냅니다.

CREATE TABLE events (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  note text
);
INSERT INTO events (note) SELECT 'before crash' FROM generate_series(1, 5);
SELECT last_value, log_cnt FROM events_id_seq;

 last_value | log_cnt
------------+---------
          5 |      28
(1 row)

이제 정상 종료 없이 서버를 강제로 중단하고(pg_ctl stop -m immediate), 다시 시작한 다음, 행을 하나 더 삽입합니다.

LOG:  database system was not properly shut down; automatic recovery in progress

INSERT INTO events (note) VALUES ('after crash') RETURNING id;

 id
----
 34
(1 row)

복구가 WAL을 재생했고, WAL에는 33이 기록되어 있었으므로 다음 ID는 34입니다. 6부터 33까지의 번호는 결코 사용되지 않았습니다. Postgres는 각 체크포인트 이후 첫 nextval에서도 32개 값을 담은 새 레코드를 쓰므로, 잃는 번호의 개수는 장애가 그 구간의 어디에서 일어나는지에 따라 달라집니다. 32는 캐시에 더해지는 값입니다. CACHE 20인 시퀀스(다음 절)는 20 + 32개 값을 앞당겨 기록하고, 그만큼 잃을 수 있습니다.

정상 종료는 실제 상태를 디스크에 쓰며, 저희 테스트에서는 일반적인 재시작 후 다음 번호부터 이어졌습니다. 이는 저희가 직접 중지한 일반 Postgres 서버였습니다. 관리형 서비스에서 재시작이 어떻게 일어나는지는 서비스에 달려 있으므로, 모든 재시작을 번호가 건너뛸 수 있는 상황으로 취급하세요.

CACHE 20으로 만든 시퀀스는 각 세션에 번호를 20개씩 줍니다. 세션이 사용하지 않은 번호는 연결이 끊길 때 사라집니다.

CREATE SEQUENCE ticket_seq CACHE 20;
SELECT nextval('ticket_seq');    -- 세션 1이 1..20을 예약함
\c
SELECT nextval('ticket_seq');    -- 세션 2가 21..40을 예약함

 nextval
---------
       1
(1 row)

You are now connected to database "postgres" as user "postgres".
 nextval
---------
      21
(1 row)

캐시를 사용하면 서로 다른 세션의 번호가 더 이상 시간 순서대로 나눠지지 않는다고 문서는 경고합니다. 세션 A가 1을 받고, 그다음 세션 B가 21을 받고, 그 이후에야 A가 2를 받습니다. 기본값은 CACHE 1이며, 따로 바꾸지 않는 한 serial과 identity 열은 이 값을 사용합니다.

이 빈 번호들은 어느 것도 버그가 아니며, 어느 것도 고칠 필요가 없습니다. 고쳐야 할 것은 생각입니다. ID는 "이 행은 저 행과 다르다"를 뜻할 뿐, "이것은 6번째 주문이다"나 "주문이 6개 있다"를 뜻하지 않습니다.

이것은 실제 버그이며, 보통 이전 작업 후 첫날에 나타납니다. 한 데이터베이스에서 테이블을 내보내 다른 데이터베이스에 적재했는데, 애플리케이션의 첫 삽입이 실패합니다. 전체 상황을 세 문장으로 재현하면 다음과 같습니다.

CREATE TABLE customers (
  id   serial PRIMARY KEY,
  name text NOT NULL
);

-- CSV 가져오기, ORM 시드, 다른 데이터베이스에서의 복사가 하는 일:
COPY customers (id, name) FROM stdin;
1	Ada
2	Grace
3	Edsger
\.

INSERT INTO customers (name) VALUES ('Barbara');

ERROR:  duplicate key value violates unique constraint "customers_pkey"
DETAIL:  Key (id)=(1) already exists.

행이 자체 ID를 가지고 있으므로 COPY는 nextval을 한 번도 호출하지 않았습니다. 시퀀스는 아직 아무것도 나눠 주지 않았다고 생각하고 새 행에 1을 주며, 기본 키가 이를 거부합니다. identity 열도 똑같이 동작합니다. COPY는 GENERATED ALWAYS 열에도 입력의 ID를 그대로 쓰며("like the INSERT option OVERRIDING SYSTEM VALUE", INSERT의 OVERRIDING SYSTEM VALUE 옵션처럼), 시퀀스는 움직이지 않습니다.

혼란을 더하는 세부 사항이 있습니다. 실패한 삽입도 매번 번호를 하나씩 소모합니다. 세 번 재시도하면 네 번째 삽입은 성공합니다. 시퀀스가 마침내 가져온 ID들을 지나쳤기 때문입니다. 그래서 행 세 개를 가져온 개발 환경에서는 오류가 가끔 "저절로 고쳐지고", 행 300만 개를 가져온 운영 환경에서는 그렇지 않습니다.

시퀀스를 테이블의 가장 큰 ID로 이동합니다. pg_get_serial_sequence는 serial 또는 identity 열 뒤에 있는 시퀀스를 찾아 주므로, 시퀀스 이름을 추측할 필요가 없습니다.

SELECT pg_get_serial_sequence('customers', 'id');
SELECT setval(pg_get_serial_sequence('customers', 'id'), max(id)) FROM customers;
INSERT INTO customers (name) VALUES ('Barbara') RETURNING id;

 pg_get_serial_sequence
-------------------------
 public.customers_id_seq
(1 row)

 setval
--------
      3
(1 row)

 id
----
  4
(1 row)

이 한 줄짜리에는 두 가지 문제가 있습니다. 빈 테이블에서는 max(id)가 NULL이고, 인수가 NULL인 setval은 NULL을 반환하며 아무 일도 하지 않습니다(경고도 없습니다). 그리고 시퀀스의 기본 최솟값이 1이므로 setval(seq, 0)은 실패합니다.

CREATE TABLE audit_log (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY);
SELECT setval(pg_get_serial_sequence('audit_log', 'id'), max(id)) FROM audit_log;
SELECT setval(pg_get_serial_sequence('audit_log', 'id'), 0);

 setval
--------

(1 row)

ERROR:  setval: value 0 is out of bounds for sequence "audit_log_id_seq" (1..9223372036854775807)

빈 테이블에서도 동작하는 형태는 setval(seq, COALESCE(max(id), 0) + 1, false)입니다. false는 "다음 nextval이 정확히 이 값을 반환한다"는 뜻이므로, 빈 테이블은 1부터 시작합니다.

또한 setval은 트랜잭션의 영향을 받지 않는다는 점에 유의하세요. 문서에 따르면 그 변경은 "immediately visible to other transactions, and [is] not undone if the calling transaction rolls back."(다른 트랜잭션에 즉시 보이며, 호출한 트랜잭션이 롤백되어도 취소되지 않는다.)

대량 가져오기 후에는 모든 테이블에 이 작업을 하고 싶을 것입니다. 이 쿼리는 serial 또는 identity 열에 속한 모든 시퀀스를 찾아 각각에 대한 setval을 만들고, psql의 \gexec가 생성된 문장을 실행합니다. 각 setval은 시퀀스를 앞으로만 이동합니다. max(id) + 1과 시퀀스가 다음에 나눠 줄 값 중 더 큰 쪽을 택합니다. 이미 사용 중인 데이터베이스에서는 이것이 중요합니다. 테이블의 최신 행들을 삭제했다면 max(id) + 1이 시퀀스보다 작을 수 있고, 시퀀스를 뒤로 옮기면 이미 한 번 사용되었고 다른 곳에서 아직 참조하고 있을지도 모르는 ID를 다시 나눠 주게 됩니다.

SELECT format(
         'SELECT setval(%1$L, GREATEST('
         'COALESCE((SELECT max(%2$I) FROM %3$I.%4$I), 0) + 1, '
         '(SELECT last_value + is_called::int FROM %1$s)), false);',
         s.oid::regclass, a.attname, n.nspname, t.relname)
FROM pg_class s
JOIN pg_depend d    ON d.classid = 'pg_class'::regclass
                   AND d.objid = s.oid
                   AND d.refclassid = 'pg_class'::regclass
                   AND d.deptype IN ('a', 'i')   -- serial / identity
JOIN pg_class t     ON t.oid = d.refobjid
JOIN pg_namespace n ON n.oid = t.relnamespace
JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = d.refobjsubid
WHERE s.relkind = 'S'
ORDER BY 1
\gexec

저희 테스트 데이터베이스에서는 테이블마다 문장 하나를 생성해 실행했습니다. 그중 두 개, 비어 있는 audit_log와 가져온 customers를 보면 다음과 같습니다(출력 일부 생략).

SELECT setval('audit_log_id_seq', GREATEST(COALESCE((SELECT max(id) FROM public.audit_log), 0) + 1, (SELECT last_value + is_called::int FROM audit_log_id_seq)), false);
 setval
--------
      1
(1 row)

SELECT setval('customers_id_seq', GREATEST(COALESCE((SELECT max(id) FROM public.customers), 0) + 1, (SELECT last_value + is_called::int FROM customers_id_seq)), false);
 setval
--------
      5
(1 row)

가져오기가 끝난 뒤, 트래픽을 받기 전에 실행하세요. max(id)와 setval 사이에 삽입된 행은 여전히 충돌할 수 있습니다. 시퀀스 자신의 다음 값이 GREATEST에 포함되므로, 사용자 지정 MINVALUE나 START가 1보다 큰 시퀀스는 빈 테이블에서도 실패하지 않고 그 자리에 머뭅니다. \gexec는 오류가 나도 계속 진행하므로, 첫 실패에서 나머지를 멈추고 싶다면 \set ON_ERROR_STOP on과 함께 실행하세요. 이 쿼리가 다루지 않는 경우가 두 가지 있습니다. 거꾸로 세는 시퀀스(음수 INCREMENT)에는 min과 LEAST가 필요합니다. 그리고 OWNED BY 없이 DEFAULT nextval('some_seq')로만 열에서 사용하는 시퀀스는 그 열이 소유하지 않습니다. pg_get_serial_sequence는 이런 시퀀스에 대해 NULL을 반환하며, 위 쿼리는 이를 건너뜁니다. 이런 시퀀스는 직접 고치세요.

각 행은 pg_dump 17.11로 확인했습니다. DEFAULT nextval(...)의 경우는 문서가 설명합니다. -t를 사용하면 "pg_dump makes no attempt to dump any other database objects that the selected table(s) might depend upon."(pg_dump는 선택한 테이블이 의존할 수 있는 다른 데이터베이스 객체를 덤프하려고 시도하지 않는다.)

serial은 integer 열과 integer 시퀀스를 만들므로 2,147,483,647에서 멈춥니다. 많아 보이지만, 이벤트를 기록하거나 대량으로 upsert하는 테이블이 행을 저장하는 속도보다 빠르게 번호를 소모하면(위의 빈 번호 참고) 이야기가 달라집니다. 끝에 도달하면 다음과 같습니다.

CREATE TABLE clicks (id serial PRIMARY KEY);
SELECT setval('clicks_id_seq', 2147483646);     -- 데모를 위해 빨리 감기
INSERT INTO clicks DEFAULT VALUES RETURNING id;
INSERT INTO clicks DEFAULT VALUES RETURNING id;

     id
------------
 2147483647
(1 row)

ERROR:  nextval: reached maximum value of sequence "clicks_id_seq" (2147483647)

그런 일이 일어나기 전에 얼마나 가까운지 확인하세요. pg_sequences는 모든 시퀀스를 타입 및 한도와 함께 보여 줍니다.

SELECT schemaname, sequencename, data_type, last_value, max_value,
       round(100.0 * last_value / max_value, 2) AS pct_used
FROM pg_sequences
ORDER BY pct_used DESC NULLS LAST, sequencename
LIMIT 3;

 schemaname |   sequencename   | data_type | last_value |      max_value      | pct_used
------------+------------------+-----------+------------+---------------------+----------
 public     | clicks_id_seq    | integer   | 2147483647 |          2147483647 |   100.00
 public     | ticket_seq       | bigint    |         40 | 9223372036854775807 |     0.00
 public     | audit_log_id_seq | bigint    |            | 9223372036854775807 |
(3 rows)

40에 있는 ticket_seq를 보세요. CACHE 20에서는 last_value에 세션이 예약했지만 아직 사용하지 않은 번호가 포함됩니다. 그리고 setval(..., false) 이후 아직 번호를 하나도 나눠 주지 않은 시퀀스는 last_value가 NULL입니다. 방금 위의 해결 방법이 건드린 시퀀스들이 그렇습니다. 이런 시퀀스는 맨 뒤에 정렬됩니다.

이 쿼리는 시퀀스 자체의 한도만 압니다. 시퀀스는 bigint인데 열이 아직 integer라면, 쿼리는 거의 0%를 보여 주는데도 열이 먼저 실패합니다. 이것이 바로 다음 함정입니다.

열이 여전히 integer이므로, 시퀀스만 바꾸는 것으로는 충분하지 않습니다.

ALTER SEQUENCE clicks_id_seq AS bigint;
INSERT INTO clicks DEFAULT VALUES RETURNING id;

ERROR:  integer out of range

열도 ALTER TABLE clicks ALTER COLUMN id TYPE bigint로 바꿔야 합니다. 큰 테이블에서 이 문장은 ACCESS EXCLUSIVE 잠금을 잡은 채 테이블 전체를 다시 쓰며, 실행되는 동안 읽기와 쓰기를 모두 막습니다. 테이블을 잠그는 마이그레이션에 관한 글에서 바로 이 변경을 측정했습니다. 저렴한 해결책은 첫날에 하는 것입니다. ID에는 bigint를 사용하세요.

업무상 구멍 없는 번호가 필요할 때가 있으며, 송장 번호가 대표적인 예입니다. 시퀀스로는 할 수 없으므로, 삽입과 같은 트랜잭션에서 업데이트하는 행을 사용합니다. 기본 READ COMMITTED 격리 수준에서는 행 잠금 때문에 동시 트랜잭션들이 차례로 실행되고, 롤백하면 송장과 함께 증가분도 취소됩니다.

CREATE TABLE invoice_counters (
  year    int PRIMARY KEY,
  last_no int NOT NULL
);
INSERT INTO invoice_counters VALUES (2026, 0);

CREATE TABLE invoices (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  year   int NOT NULL,
  no     int NOT NULL,
  amount numeric NOT NULL,
  UNIQUE (year, no)
);

BEGIN;
WITH n AS (
  UPDATE invoice_counters SET last_no = last_no + 1
  WHERE year = 2026
  RETURNING last_no
)
INSERT INTO invoices (year, no, amount)
SELECT 2026, last_no, 120 FROM n
RETURNING id, year, no;
ROLLBACK;

같은 트랜잭션을 ROLLBACK 대신 COMMIT으로 다시 실행합니다.

 id | year | no
----+------+----
  2 | 2026 |  1
(1 row)

롤백이 카운터를 취소했으므로 송장 번호는 다시 1입니다. id는 2인데, identity 열은 여전히 시퀀스이고 롤백으로 1을 잃었기 때문입니다. 둘 다 유지하세요. identity는 기본 키로, 빈 번호 없는 번호는 업무용 필드로 씁니다.

대가는 동시성입니다. 카운터 행은 한 번에 한 트랜잭션만 잡을 수 있습니다. 저희 테스트에서 한 세션이 이 행을 5초 동안 잡고 있었고, 약 1초 뒤에 시작한 두 번째 세션의 UPDATE는 첫 번째 세션이 커밋할 때까지 기다렸습니다.

 last_no
---------
       3
(1 row)

UPDATE 1
Time: 3943.462 ms (00:03.943)

따라서 이런 트랜잭션은 짧게 유지하세요. 번호는 COMMIT 직전, 마지막에 가져오고, 번호를 잡고 있는 동안 다른 서비스를 호출하지 마세요. 연도별(또는 시리즈별, 테넌트별)로 카운터 행을 하나씩 두면 대기도 분산됩니다. REPEATABLE READ나 SERIALIZABLE에서도 두 번째 트랜잭션은 여전히 기다리지만, 첫 번째 트랜잭션이 커밋되면 could not serialize access due to concurrent update로 실패하므로 코드에서 재시도해야 합니다.

새 테이블에는 bigint GENERATED ALWAYS AS IDENTITY를 사용하세요. SQL 표준 문법이고, 열과 그 시퀀스가 하나로 묶이며, GENERATED ALWAYS는 실수로 넣은 명시적 ID를 거부합니다.

ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

PostgreSQL 커뮤니티 위키의 "Don't Do This" 페이지도 같은 말을 합니다. serial 대신 "For new applications, identity columns should be used instead"(새 애플리케이션에서는 identity 열을 대신 사용해야 한다). 이 글에서 빈 번호와 가져오기에 관해 말한 모든 내용은 둘 다에 적용됩니다. 다른 점은 가져오기 보호뿐이며, 위에서 본 것처럼 COPY는 어차피 그 보호를 우회합니다.

가져오기 버그는 고치기 쉽지만 놓치기도 쉽습니다. 데이터가 들어간 뒤 첫 삽입에서만 나타나기 때문입니다. 사용자보다 먼저 그 삽입을 테스트하세요. Relyt One에서 브랜치는 특정 시점의 부모에서 시작하는 새로운 이력 줄기로, 부모와 페이지를 copy-on-write로 공유하며 자체 컴퓨트를 가집니다. 내보낸 데이터를 브랜치에 적재하고, 모든 시퀀스를 고치는 쿼리를 실행하고, 테이블마다 행을 하나씩 삽입한 다음, pg_sequences에서 한도에 가까운 것이 있는지 살펴보세요. 브랜치에서의 쓰기는 부모에 영향을 주지 않으므로 브랜치의 시퀀스는 독립적으로 움직입니다. 문제가 생기면 브랜치를 삭제하고 다시 시작하면 됩니다.

Relyt One은 PostgreSQL 17을 실행하며, 이 글의 SQL에는 확장이 필요하지 않습니다. \c와 \gexec 줄은 psql 명령이고, 장애 테스트에는 직접 중지할 수 있는 Postgres 서버가 필요하므로 그 테스트는 로컬에서 실행하세요.

무료로 시작하기