PostgreSQL bigserialの上限と移行手順

目次
  1. 結論:原因の大半は「serial のまま int4 上限」か「bigserial を型だと誤解している」こと
  2. 原因の切り分け:確認しやすい順に4ステップ
  3. serial から bigint(bigserial 相当)へ移行する手順
  4. bigserial と identity 列(GENERATED AS IDENTITY)の使い分け
  5. よくある質問
  6. 再発防止:設計時のチェックリストと監視の入れ方
  7. さらに学ぶには
このテーマのまとめPostgreSQLが遅い・詰まる原因|VACUUMと実行計画、ロック
本記事にはプロモーション(広告)が含まれています。

PostgreSQLで serial を使って作った主キーが、ある日突然 ERROR: integer out of range でINSERTを拒否し始める。あるいは nextval: reached maximum value of sequence が出る。どちらも連番の枯渇ですが、原因が違うので対処も違います。そしてこの状況で bigserial に直そうとすると、「列の型を変えただけで直らない」「ALTER TABLEが終わらずサービスが止まった」という二次災害に遭いやすい。

この記事では、枯渇の切り分けクエリ、serial から bigint への移行手順(ロックとテーブル書き換えを含む)、新規設計で bigserial と identity 列のどちらを選ぶかの判断基準をまとめます。

結論:原因の大半は「serial のまま int4 上限」か「bigserial を型だと誤解している」こと

前提として、serial も bigserial もPostgreSQLのデータ型ではありません。擬似型であり、CREATE TABLE 時に次の3つを展開するマクロです。

  1. 列を integer(serial)または bigint(bigserial)で作る
  2. シーケンスを作る
  3. 列のデフォルト値に nextval('...') を設定し、シーケンスを列の所有物にする(OWNED BY)

そのため \d でテーブルを見ても serial という表示は出ません。出るのは integer と default nextval('orders_id_seq'::regclass) です。ここを理解していないと、「bigserialに変えたはずなのに直らない」という事故が起きます。

枯渇時に出るエラーは主に3種類で、それぞれ意味が違います。

エラーメッセージ 意味 対処
integer out of range 列が integer のまま、シーケンスが2147483647超の値を返した 列の型を bigint に変える
nextval: reached maximum value of sequence "..." (2147483647) シーケンス自体の上限(AS integer)に到達 列の型変更+ALTER SEQUENCE ... AS bigint
duplicate key value violates unique constraint シーケンス現在値が既存データの最大値より小さい setval で追いつかせる(枯渇とは別問題)

2番目のエラーが出るのはPostgreSQL 10以降です。10でシーケンスにデータ型(AS smallint | integer | bigint)の概念が入り、serial で作られたシーケンスは AS integer、つまり max_value = 2147483647 になりました。9.6以前のシーケンスは常にbigint範囲だったため、シーケンスは2147483648を平然と返し、列への代入時に integer out of range で落ちます。古いバージョンからアップグレードしてきたデータベースでは後者のままのケースもあるので、まずエラーメッセージを確認してから対処を決めてください。

原因の切り分け:確認しやすい順に4ステップ

順番は①シーケンス残量→②列の実データ型→③所有関係とidentityの有無→④参照している子テーブル・ビュー。①②で現状を把握し、③④で移行の影響範囲が決まります。

シーケンスの現在値と上限を1クエリで確認する

PostgreSQL 10以降なら pg_sequences ビューで全シーケンスを一覧できます。

SELECT schemaname,
       sequencename,
       data_type,
       last_value,
       max_value,
       round(last_value::numeric / max_value * 100, 2) AS used_pct
FROM pg_sequences
WHERE last_value IS NOT NULL
ORDER BY used_pct DESC;

読み方には注意点があります。last_value は最後に払い出した値です。cache を1より大きくしていると、セッションが予約した分だけ先に進んで見えます。権限(SELECT/USAGE)を持たないシーケンスや、まだ一度も nextval されていないシーケンスは last_value がNULLになるため、アプリ所有のシーケンスを漏れなく見たい場合はスーパーユーザーか pg_read_all_stats 相当の権限で実行してください。data_type が integer なら、列を bigint にしてもシーケンス側で止まるので、ここも併せて直します。

9.6以前では pg_sequences がないため、pg_class からシーケンスを列挙し、個別に SELECT last_value, max_value FROM seq_name; を実行します。

枯渇までの猶予は消費速度で判断します。last_value を日次でどこかのテーブルに記録し、1日あたりの増分で割れば残り日数が出ます。中間テーブルやログテーブルは行を削除してもシーケンスが戻らないため、行数が少ないのに枯渇が近いという状態が普通に起こります。

列の実データ型とシーケンスの所有関係を確認する(pg_get_serial_sequence / pg_depend)

次に、どの列がどのシーケンスを使っているかを確定させます。

SELECT c.relname        AS table_name,
       a.attname        AS column_name,
       format_type(a.atttypid, a.atttypmod) AS column_type,
       a.attidentity    AS identity_kind,   -- '' / 'd'(BY DEFAULT) / 'a'(ALWAYS)
       pg_get_serial_sequence(format('%I.%I', n.nspname, c.relname), a.attname) AS seq
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum > 0 AND NOT a.attisdropped
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog','information_schema')
  AND pg_get_serial_sequence(format('%I.%I', n.nspname, c.relname), a.attname) IS NOT NULL
ORDER BY 1, 2;

column_type が integer で seq が埋まっている行が移行対象です。identity_kind が d か a なら identity 列なので、ALTER SEQUENCE ... AS bigint を使わず列の型変更側で扱います(詳細は後述)。

所有関係を直接見たいときは pg_depend を使います。deptype = 'a'(auto)が serial のシーケンス、'i'(internal)が identity 列のシーケンスです。

SELECT s.relname AS sequence_name, t.relname AS table_name, a.attname, d.deptype
FROM pg_depend d
JOIN pg_class s ON s.oid = d.objid AND s.relkind = 'S'
JOIN pg_class t ON t.oid = d.refobjid
JOIN pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
WHERE d.deptype IN ('a','i');

最後に型変更の影響範囲を洗い出します。見落としやすいのは4か所です。外部キーの子側の列は、親を bigint にしても子が integer のままなら、将来の値でFK挿入が失敗します。information_schema.constraint_column_usage やFK定義から子列を列挙し、同時に変更してください。対象列を参照するビューやマテリアライズドビューがあると ALTER TABLE ... TYPE は cannot alter type of a column used by a view or rule で失敗するため、定義を退避して削除・再作成します。関数・ストアド内の変数型や、integer を引数に取る関数シグネチャも対象です。アプリ側もJavaの int、Goの int32、protobufの int32 など、DBを直してもアプリ側で溢れる箇所が残ります。

serial から bigint(bigserial 相当)へ移行する手順

繰り返しますが bigserial という型に「変更」はできません。やることは、列の型を bigint にすることと、シーケンスを AS bigint にすることの2点です。

-- 1. 列の型変更(テーブル書き換えが走る)
ALTER TABLE orders ALTER COLUMN id TYPE bigint;

-- 2. シーケンスの上限を広げる(PostgreSQL 10以降)
ALTER SEQUENCE orders_id_seq AS bigint;

バージョンによっては列の型変更にシーケンスの型が追従する実装もありますが、依存しないほうが安全です。実行後に pg_sequences の data_type と max_value を確認してください。

ALTER TABLE … TYPE bigint が発生させるテーブル書き換えとロック

varchar(10) → varchar(20) のようなバイナリ互換の拡張と違い、integer → bigint は物理フォーマットが変わるため、必ずテーブル全体の書き換え(rewrite)になります。ACCESS EXCLUSIVE ロックを取得するのでSELECTすら待たされます。新しい relfilenode に全行をコピーするため、元テーブルとほぼ同サイズの追加ディスクが必要です。さらにPKを含む全インデックスを再構築し、対象列を参照するFK制約は再検証されます。

さらに厄介なのがロックキューです。ACCESS EXCLUSIVE 待ちのトランザクションが1つできると、その後ろに並んだSELECTも全部ブロックされます。長時間の参照クエリが1本走っているだけで、ALTER TABLE 自体が始まる前にサービス全体が詰まる、というのが典型的な事故パターン。短時間メンテナンスで実施する場合でも、lock_timeout を入れて「取れなければ諦める」設計にします。

BEGIN;
SET LOCAL lock_timeout = '5s';
ALTER TABLE orders ALTER COLUMN id TYPE bigint;
ALTER SEQUENCE orders_id_seq AS bigint;
COMMIT;

所要時間は行数・行幅・インデックス数・ストレージ性能に強く依存します。見積りは、本番と同規模のデータを復元した検証環境で実測してください。数千万行規模でインデックスが複数あれば分単位、億行なら時間単位を覚悟して計画します。

停止時間を抑える進め方:新列+トリガで切り替える

停止できない場合は、bigint の新列を作って同期しながら埋め、最後に短時間で入れ替えます。流れは次の通りです。

-- (1) 新列追加(デフォルトなしなら PostgreSQL 11 以降はメタデータ更新のみで即完了)
ALTER TABLE orders ADD COLUMN id_new bigint;

-- (2) 以降の書き込みを同期するトリガ
CREATE FUNCTION orders_sync_id() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.id_new := NEW.id;
  RETURN NEW;
END $$;

CREATE TRIGGER trg_orders_sync_id
  BEFORE INSERT OR UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION orders_sync_id();
-- (3) 既存行をバッチで埋める(1万〜数万行ずつ、間隔を置く)
UPDATE orders SET id_new = id
WHERE id_new IS NULL
  AND id BETWEEN 1 AND 10000;

バッチ分割は必須です。一括UPDATEは長時間トランザクションになり、デッドタプルが大量発生してテーブルが膨張します。実行中は pg_stat_progress_vacuum やテーブルサイズを監視し、autovacuumが追いつく速度にペースを調整してください。膨張と待機の見方はPostgreSQLが遅い・詰まる原因|VACUUMと実行計画、ロックで整理しています。

-- (4) NOT NULL を安全に付ける(PostgreSQL 12 以降)
ALTER TABLE orders ADD CONSTRAINT orders_id_new_nn CHECK (id_new IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_id_new_nn;  -- SHARE UPDATE EXCLUSIVE
ALTER TABLE orders ALTER COLUMN id_new SET NOT NULL;      -- 12以降はCHECKを根拠に即完了

-- (5) 置き換え用のユニークインデックスをオンラインで作る
CREATE UNIQUE INDEX CONCURRENTLY orders_pkey_new ON orders (id_new);
-- (6) 短時間トランザクションで入れ替え
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders DROP CONSTRAINT orders_pkey;
ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_pkey_new;
ALTER TABLE orders RENAME COLUMN id TO id_old;
ALTER TABLE orders RENAME COLUMN id_new TO id;
ALTER TABLE orders ALTER COLUMN id SET DEFAULT nextval('orders_id_seq');
ALTER SEQUENCE orders_id_seq AS bigint OWNED BY orders.id;
DROP TRIGGER trg_orders_sync_id ON orders;
COMMIT;

主キーを張り替えると、それを参照するFKも削除されます。子テーブルのFKはいったん削除し、子列を bigint にしてから NOT VALID で再作成し、VALIDATE CONSTRAINT で検証する流れです。子テーブルが多いシステムでは、手順全体のスクリプト化とリハーサルが欠かせません。

不要になった id_old は、落ち着いてから ALTER TABLE orders DROP COLUMN id_old; で削除します。メタデータ操作のみで、領域は後続のVACUUMで回収されます。

テーブル数が多い、手順が複雑すぎるという場合は、論理レプリケーションで型を変えた新テーブルへ同期してから切り替える、あるいはAurora/RDSのBlue/Green Deploymentsを使う選択肢もあります。採れる手段とダウンタイム許容時間を先に突き合わせて決めてください。

この記事のテーマをさらに学べる本

内部構造やテーブル設計、モニタリングまで体系的に解説しており、移行計画の判断材料を補えます。

PostgreSQL実践入門──アーキテクチャ、運用監視、性能改善
PostgreSQL実践入門──アーキテクチャ、運用監視、性能改善堀口 恭太郎/細谷 柚子/渡 佑也/山田 達朗/白石 裕輝/須賀 啓敏
楽天ブックスで詳しく見る Yahoo!ショッピングで見る

bigserial と identity 列(GENERATED AS IDENTITY)の使い分け

新規設計なら、PostgreSQL 10以降では bigint GENERATED BY DEFAULT AS IDENTITY(または ALWAYS)が第一候補です。理由は機能差にあります。

-- 旧来の書き方
CREATE TABLE orders (id bigserial PRIMARY KEY, ...);

-- 標準SQL準拠の書き方
CREATE TABLE orders (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  ...
);
観点 bigserial identity 列
規格 PostgreSQL独自 標準SQL(Oracle 12c以降、DB2等と共通)
明示的な値の挿入 常に可能。シーケンスは進まないため後で重複エラーの原因になる ALWAYS なら拒否(OVERRIDING SYSTEM VALUE 指定時のみ許可)
NOT NULL 自動では付かない(serial は暗黙にNOT NULL付与されるが、設計意図が見えにくい) 定義上必ずNOT NULL
列削除時のシーケンス 所有関係(deptype ‘a’)により一緒に削除 internal依存で一緒に削除
表示 \d では integer + nextval デフォルト。意図が読み取りにくい \d に generated by default as identity と出る

実務でいちばん差が出るのは明示挿入の制御です。serial ではバッチ処理やデータ移行でIDを直接指定でき、その分シーケンスが進まないため、後から duplicate key value violates unique constraint が出ます。「IDはDBが決める」という契約を守らせたいなら GENERATED ALWAYS AS IDENTITY が確実です。データ移行でIDを維持したまま投入する運用が残るなら BY DEFAULT を選びます。

一方で bigserial を選ぶ理由が残る場面もあります。古いORMやマイグレーションツールが identity 列を解釈できない、あるいは既存テーブルと記法を揃えたい場合です。また、複数テーブルで1つのシーケンスを共有して識別子をグローバルに一意にしたい要件では、identity 列が列専用のシーケンスを持つため、素のシーケンスと DEFAULT nextval() を自分で定義するほうが素直でしょう。

Oracleからの移行では、NUMBER 列+シーケンス+トリガの構成を identity 列に置き換えるのが定番です。型マッピングやシーケンス周りの差異はOracle PostgreSQL移行の注意点|症状別の切り分けと対処も合わせて確認してください。

よくある質問

bigserial の最大値はいくつ?

bigint の上限と同じ9223372036854775807(2の63乗−1)です。serial(integer)の2147483647と比べると約43億倍で、毎秒1万件を払い出し続けても現実的な運用期間で枯渇しません。代償は1行あたり4バイト増と、インデックスサイズの増加です。行幅が小さいテーブルではアライメントの都合で見た目以上に増えることもありますが、枯渇時の移行コストと比べれば、迷ったときは bigint を選ぶほうが合理的です。

シーケンスの欠番(飛び番)は防げる?

シーケンスの仕様上、防げません。nextval はトランザクションの外で値を進めるため、ロールバックしても戻りません。cache の値だけセッションが先に予約するので、セッション終了で未使用分は捨てられます。クラッシュ時はWALへの遅延書き込み(既定では一定個数ごとにログ)のため、まとまった数の値が飛ぶことがあります。

請求書番号のように欠番を許さない要件であれば、採番用テーブルの行を UPDATE ... RETURNING でロックして払い出す方式にします。ただし採番が直列化されるため、同時実行性能は大きく落ちます。業務要件として本当に必要かを先に確認してください。

bigserial から identity 列に移行できる?

できます。テーブル書き換えは不要で、メタデータ操作のみです(ACCESS EXCLUSIVE ロックは一瞬かかります)。

-- 既存のシーケンスを切り離す
ALTER TABLE orders ALTER COLUMN id DROP DEFAULT;
ALTER SEQUENCE orders_id_seq OWNED BY NONE;

-- identity 化(列は NOT NULL である必要がある)
ALTER TABLE orders ALTER COLUMN id SET NOT NULL;
ALTER TABLE orders ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;

-- 新しく作られたシーケンスの現在値を既存データに合わせる
SELECT setval(pg_get_serial_sequence('public.orders','id'),
              (SELECT coalesce(max(id), 0) FROM public.orders));

-- 旧シーケンスを削除
DROP SEQUENCE orders_id_seq;

setval を忘れると新しいシーケンスが1から払い出し、重複キーエラーになります。切り替え後に nextval 相当の値と max(id) を必ず突き合わせてください。

pg_dump / リストアでシーケンスの現在値は引き継がれる?

通常の pg_dump(スキーマ+データ)では、シーケンスごとに setval(...) が出力されるため現在値は引き継がれます。問題になりやすいのは例外ケースです。-t でテーブル単位に絞ったダンプや、スキーマとデータを分割して一部だけ復元した場合、シーケンスの setval が含まれない、または実行されないことがあります。アプリ側のCSVインポートなどで、pg_dump を使わずIDを明示投入した場合も同様です。

結果としてシーケンス現在値が max(id) より小さい状態になり、INSERT時に重複キーエラーが出ます。復元後の定型チェックとして次を流しておくと安全です。

SELECT c.relname AS table_name, a.attname,
       pg_sequence_last_value(s.oid) AS seq_last_value
FROM pg_depend d
JOIN pg_class s ON s.oid = d.objid AND s.relkind = 'S'
JOIN pg_class c ON c.oid = d.refobjid
JOIN pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
WHERE d.deptype IN ('a','i');

ズレていれば setval(pg_get_serial_sequence('schema.table','id'), (SELECT max(id) FROM schema.table)) で合わせます。復元直後は統計情報の扱いも論点になるので、Auroraのスナップショット復元についてはAuroraスナップショット復元と統計情報|ANALYZEは必要かも参考になります。

再発防止:設計時のチェックリストと監視の入れ方

設計時は、主キーの連番を既定で bigint にします。integer を選ぶのは、行数の上限が業務的に確定している場合だけ。新規は GENERATED BY DEFAULT AS IDENTITY を使い、アプリが値を渡さないなら ALWAYS にします。外部キーの子列も親と同じ型に揃えてください。行を削除してもシーケンスは戻らないので、削除前提の中間テーブルやキューテーブルは消費が速い前提で型を決めます。

監視は pg_sequences の使用率をメトリクス化し、しきい値(70%で警告、85%で対応着手など)でアラートを出します。

SELECT schemaname || '.' || sequencename AS seq,
       round(last_value::numeric / max_value * 100, 2) AS used_pct
FROM pg_sequences
WHERE last_value IS NOT NULL
  AND last_value::numeric / max_value > 0.70;

使用率と併せて last_value を日次で履歴保存しておくと、増分から枯渇予測日が出せます。RDS/Auroraならこのクエリの結果をCloudWatchのカスタムメトリクスに送る、オンプレならZabbix/Prometheusのexporterに載せるのが実装としては簡単です。

運用面では、型変更の手順書をFK・ビュー・アプリ修正まで含めてテーブル単位で用意しておきます。本番相当データを復元した環境で ALTER TABLE ... TYPE bigint の所要時間とディスク増加量を実測しておくことも必要です。使用率が低いうちに移行計画を決めておけば、停止時間の短い手段を選べます。

OSS-DB Silver 対策テキスト(OSS教科書)

PostgreSQLの仕組みを体系的に学ぶなら、OSS-DB Silverの公式対応テキストが近道です。資格を取らなくても基礎固めに役立ちます。

OSS教科書 OSS-DB Silver Ver.3.0対応 (EXAMPRESS) [ 福岡 博 ]

OSS教科書 OSS-DB Silver Ver.3.0対応 (EXAMPRESS) [ 福岡 博 ]

さらに学ぶには

シーケンスやロックの挙動は、MVCC・WAL・実行計画といった内部構造を理解していると判断が速くなります。体系的に学ぶ順番は次の記事で整理しています。