Postgres の ID が番号を飛ばす理由と、インポート後の「duplicate key」エラーの直し方

Postgres の ID については、2 つの質問が何度も繰り返されます。「ID が 5 から 34 に飛んだのはなぜ？」と「データをインポートした直後から、すべての挿入が duplicate key value violates unique constraint \"..._pkey\" で失敗するのはなぜ？」です。どちらも同じオブジェクトが原因です。serial 列や ID 列の裏にあるシーケンスです。シーケンスは、番号が使われるかどうかを気にせずに番号を払い出すので、欠番が生じるのは正常です。また、シーケンスが知っているのは自分で払い出した番号だけなので、明示的な ID 付きで読み込んだ行があると、シーケンスは取り残されます。この記事では、欠番の種類ごとにそれを再現し、インポートのエラーをまず 1 つのテーブルについて、次にすべてのテーブルについて一度に修正し、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)

3 行に対して、欠番が 3 つあります。ロールバックされた挿入が 2 を取りました。一意な email 制約で失敗した挿入は、失敗する前に 5 を取りました。Postgres は制約をチェックする前に、デフォルトの ID を含めた行全体を組み立てるからです。ON CONFLICT DO NOTHING も同じ理由で、何も挿入せずに 4 を取りました。ドキュメントにも明記されています。INSERT ... ON CONFLICT は行を計算し、その際 "including doing any required nextval calls, before detecting any conflict."（競合を検出する前に、必要な nextval 呼び出しも行う）。アプリが同じ行を何度もアップサートしていると、シーケンスは行数よりずっと速く増えることがあります。（アップサートについては INSERT ... ON CONFLICT と 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）、再起動してから、もう 1 行挿入します。

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 列と ID 列はこれを使います。

これらの欠番はどれもバグではなく、どれも修正の必要はありません。直すべきは考え方です。ID が示すのは「この行はあの行とは別物だ」ということであり、「これは 6 番目の注文だ」や「注文は 6 件ある」ではありません。

こちらは本物のバグで、たいていは移行の翌日に現れます。あるデータベースからテーブルをエクスポートし、別のデータベースに読み込んだところ、アプリの最初の挿入が失敗するのです。3 つのステートメントで全体を再現できます。

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 を渡し、主キーがそれを拒否します。ID 列も同じ動作です。COPY は入力にある ID を GENERATED ALWAYS の列にさえ書き込み（"like the INSERT option OVERRIDING SYSTEM VALUE"、つまり INSERT の OVERRIDING SYSTEM VALUE オプションと同様に）、シーケンスは動きません。

話をややこしくする点が 1 つあります。失敗した挿入も番号を 1 つ消費します。3 回リトライすると 4 回目の挿入は成功します。シーケンスがようやくインポートされた ID を通り越したからです。開発環境（インポートしたのは 3 行）ではこのエラーが「勝手に直る」のに、本番環境（インポートしたのは 300 万行）では直らないのはこのためです。

シーケンスをテーブル内の最大の ID まで進めます。pg_get_serial_sequence は serial 列や ID 列の裏にあるシーケンスを見つけてくれるので、名前を推測する必要はありません。

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)

この 1 行には落とし穴が 2 つあります。空のテーブルでは 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 列または ID 列に属するすべてのシーケンスを見つけて、それぞれに対する 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

私たちのテスト用データベースでは、テーブルごとに 1 つのステートメントが生成・実行されました。そのうち 2 つ、空の 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 を付けて実行してください。このクエリが対応しないケースが 2 つあります。減少していくシーケンス（負の 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 で止まります。多いように聞こえますが、イベントを記録するテーブルや一括でアップサートするテーブルが、行を保存するよりも速く番号を消費するようになると話は別です（上の欠番を参照）。終わりはこうなります。

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)

ticket_seq が 40 になっている点に注目してください。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 です。ID 列は依然としてシーケンスであり、ロールバックで 1 を失ったからです。両方を残しておきましょう。ID 列は主キーとして、欠番のない番号は業務上のフィールドとして使います。

代償は同時実行性です。カウンター行を保持できるのは、一度に 1 つのトランザクションだけです。私たちのテストでは、あるセッションがこの行を 5 秒間保持し、約 1 秒後に開始した 2 つ目のセッションの UPDATE は、最初のセッションがコミットするまで待たされました。

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

UPDATE 1
Time: 3943.462 ms (00:03.943)

ですから、これらのトランザクションは短く保ってください。番号は最後、COMMIT の直前に取り、保持している間は他のサービスを呼び出さないようにします。年ごと（あるいは系列ごと、テナントごと）にカウンター行を 1 つずつ用意すれば、待ちも分散できます。REPEATABLE READ や SERIALIZABLE では、2 つ目のトランザクションはやはり待たされますが、最初のトランザクションがコミットすると 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 コミュニティ Wiki の「Don't Do This」のページも同じことを述べています。serial について "For new applications, identity columns should be used instead"（新しいアプリケーションでは、代わりに ID 列を使うべきだ）。欠番とインポートについてこの記事で述べたことは、どちらにも当てはまります。違うのはインポートに対する保護だけで、上で示したとおり、COPY はいずれにせよそれを迂回します。

インポートのバグは、直すのは簡単ですが、見落としやすいものです。データが入った後の最初の挿入でしか現れないからです。ユーザーより先に、その挿入をテストしましょう。Relyt One では、ブランチは親のある時点から始まる新しい履歴の流れで、ページを copy-on-write で共有し、独自のコンピュートを持ちます。エクスポートしたデータをブランチに読み込み、すべてのシーケンスを修正するクエリを実行し、各テーブルに 1 行ずつ挿入し、pg_sequences で上限に近いものがないかを確認します。ブランチへの書き込みは親に影響しないので、ブランチのシーケンスは独立して進みます。何か問題が起きたら、ブランチを削除してやり直せば済みます。

Relyt One は PostgreSQL 17 を実行しており、この記事の SQL に拡張機能は必要ありません。\c と \gexec の行は psql のコマンドです。また、クラッシュのテストには自分で停止できる Postgres サーバーが必要なので、それはローカルで実行してください。

無料で始める