問題の状況
PostgreSQLでserialを使って作った主キーが、ある日ERROR: integer out of rangeやnextval: reached maximum value of sequenceでINSERTを拒否します。どちらも連番の枯渇ですが、原因は別物なので対処も分かれます。しかもbigserialへ直そうとした段階で、列の型を変えたのにエラーが止まらない、ALTER TABLEが終わらずサービスが停止した、という二次的な障害が起こりやすい箇所です。
この記事は要約版です。枯渇の切り分けクエリ4ステップ、停止時間を抑える新列+トリガ方式の移行手順、identity列との使い分けの判断基準は、ブログの完全版にまとめています: PostgreSQL bigserialの上限と移行手順
結論
serialとbigserialはPostgreSQLのデータ型ではありません。CREATE TABLE時に、列をintegerまたはbigintで作り、シーケンスを作り、デフォルト値にnextval()を設定して列の所有物にする、という3つの処理を展開する擬似型です。そのため\dで見えるのはintegerとdefault nextval('orders_id_seq'::regclass)だけ。この構造を把握していないと「bigserialに変えたはずなのに直らない」状態から抜け出せません。
エラーメッセージの読み分けが最初の分岐点になります。integer out of rangeは列がintegerのままシーケンスが2147483647超を返した状態で、列の型変更が必要です。nextval: reached maximum value of sequenceはシーケンス自体の上限に到達した状態で、こちらはPostgreSQL 10以降でserialが作るシーケンスがAS integerになったために出ます。9.6以前から移行してきたデータベースではシーケンスがbigint範囲のままのこともあるため、まずエラー文を確認してください。duplicate key value violates unique constraintは枯渇とは別で、シーケンス現在値がmax(id)より小さいケースです。
対処は、列をbigintに変えることと、シーケンスをALTER SEQUENCE ... AS bigintにすることの2点に集約されます。ただしintegerからbigintへの変更は物理フォーマットが変わるため、テーブル全体の書き換えとACCESS EXCLUSIVEロックが発生します。ロック待ちが1件できると後続のSELECTも並んで止まるので、lock_timeoutを設定して取得できなければ中止する設計にします。停止できないシステムでは、bigintの新列を追加し、トリガで同期しながらバッチで埋め、最後に短時間で入れ替える流れを採ります。新規設計ならbigint GENERATED BY DEFAULT AS IDENTITYが第一候補です。
まず流す確認クエリ
PostgreSQL 10以降なら、シーケンスの使用率を1クエリで一覧できます。
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;
data_typeがintegerなら、列をbigintにしてもシーケンス側で止まります。権限のないシーケンスや未使用のシーケンスはlast_valueがNULLになるため、漏れなく確認したい場合は権限にも注意してください。
完全版で扱っている内容
- 原因の切り分け4ステップ。
pg_get_serial_sequenceで対象列を列挙するクエリ、pg_dependのdeptypeでserialとidentity列を区別する方法、外部キーの子列やビュー、アプリ側のint32まで含めた影響範囲の洗い出し -
ALTER TABLE ... TYPE bigintで発生するテーブル書き換えの内訳。追加ディスク、インデックス再構築、FK再検証、ロックキューによる全体停止と、lock_timeoutを入れた実行例 - 停止時間を抑える切り替え手順の全体。新列追加からトリガ同期、バッチUPDATEとautovacuumのペース調整、
NOT VALIDなCHECK経由のNOT NULL付与、CREATE UNIQUE INDEX CONCURRENTLY、主キー張り替えまで -
bigserialとidentity列の比較表と、明示挿入の制御でALWAYSとBY DEFAULTを選び分ける基準。既存のbigserialをidentity列へ移行するSQLとsetvalの注意点 - 再発防止のチェックリストと監視の実装。使用率70%で警告、85%で対応着手といったしきい値の設定例、
pg_dumpやリストアでシーケンス現在値がずれる例外ケースと復元後チェック