0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

PostgreSQLのserial枯渇とbigintへの移行で詰まらない進め方

0
Posted at

問題の状況

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やリストアでシーケンス現在値がずれる例外ケースと復元後チェック

PostgreSQL bigserialの上限と移行手順

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?