Postgresでよく起きるDBスキーマ変更のミス

Here is a summary of the common database schema change mistakes, translated and structured in Korean:

同時実行性に関するミス

• ロック取得の失敗
• 一度に多すぎる行を更新する
• 排他ロックを取得した後、トランザクションを長時間開いたままにする

手順の正確性に関するミス - 論理的な問題

• 想定外のスキーマ差異
• スキーマとアプリコードの不一致
• 想定外のデータ

その他のミス

• statement_timeout に到達する
• 将来的に増大しうるテーブルで 4 バイト整数の主キーを使う
• VACUUM の動作と bloat のリスクを無視する

Case 1. スキーマの不一致

• 開発/テスト環境では動作したが、QA/Staging/Production では失敗した
• 原因を特定した上で、ワークフローの改善によって解決すべき

Case 2. IF [NOT] EXISTS の誤用

• スキーマ不一致エラーを IF NOT EXISTS で握りつぶそうとしてはいけない
• 問題の根本原因を把握して解決すべき

Case 3. statement_timeout に到達

• すべての変更を大容量データでテストして事前に把握すること

Case 4. 無制限な大規模変更

• 1 つのトランザクションであまりに多くの行を変更すると、他のトランザクションに影響する
• Checkpointer のチューニングが不十分だと WAL データが過剰に生成される
• ディスク書き込みの飽和により、全体的な性能低下が発生する可能性がある
• VACUUM / bloat の問題が発生する可能性がある
• バッチに分割して処理し、VACUUM を管理すること

Case 5. 排他ロック取得後にトランザクション内で待機

• BEGIN / ALTER TABLE / COMMIT の間に別の作業をすると、ロックが長時間維持される
• 排他ロックを取得した後は、可能な限り速やかにトランザクションを終えるべき

Case 6. DDL + 大量 DML を含むトランザクション

• DDL 段階で取得したロックが DML 段階まで長時間維持される
• DDL と DML は別々のトランザクション / マイグレーション段階に分離すること

Case 7. 排他ロック取得待ちによる他セッションのブロッキング

• autovacuum が wraparound 防止モードのときは DDL に yield しない
• ロック取得待ちの間に SELECT までブロックされる
• lock_timeout を低めに設定し、リトライロジックを作ること

Case 8. FK 作成時の注意点

• 大きなテーブルで FK を作成すると、referenced テーブルのスキャンで時間がかかる
• not valid オプションで FK を定義した後、別トランザクションで validate する

Case 9. FK 削除時の注意点

• 2 つのテーブルのロックが必要なため、lock_timeout のリトライロジックが必要

Case 10. CHECK 制約追加時の注意点

• テーブル全体のスキャンが発生するため、FK と同様の 2 段階アプローチを使う

Case 11. NOT NULL 追加時の注意点

• Postgres 11 未満では、新しいカラムに NOT NULL を追加するとテーブルスキャンが発生する
• Postgres 11 以降では、NOT NULL DEFAULT カラムの追加で解決できる
• Postgres 12 以降では、CHECK 制約の追加によって NOT NULL を設定できる

Case 12. カラムのデータ型変更時の注意点

• テーブル全体の再書き込みが発生する可能性がある
• 新しいカラムを追加した後、トリガーでデータをコピーするアプローチが必要

Case 13. CREATE INDEX 時の注意点

• OLTP では CREATE INDEX CONCURRENTLY を使うべき
• ユニークインデックスの作成に失敗した場合は、invalid インデックスのクリーンアップが必要

Case 14. DROP INDEX 時の注意点

• ロック取得の問題があるため、DROP INDEX CONCURRENLTY を使う

Case 15. オブジェクト名変更時の注意点

• アプリコードと DB スキーマの不一致を避けるため、デプロイ順序の調整が必要

Case 16. DEFAULT 値のあるカラムを追加

• PG 11 より前では、テーブル全体の再書き込みが発生する
• PG 11 以降では、DEFAULT 値のあるカラム追加が高速化された

Case 17. CREATE INDEX CONCURRENTLY 失敗時の残存物の処理

• 失敗すると invalid インデックスが残るため、再試行前にクリーンアップが必要

Case 18. 大きなテーブルで 4 バイト整数の主キーを使う

• int8 を使うべき。ほとんどのフレームワークはすでに int8 を使っている。

推奨事項

• 現実的なデータサイズでテストする
• 排他ロックの保持時間を確認する
• デプロイ自動化を改善する
• 他の人から学び、知識を共有する

GN⁺の意見

この記事は、実際の DB スキーマ変更時に遭遇しうるさまざまなミスや注意点をうまく整理しています。特に排他ロックに関連する問題が多く言及されていますが、これは大規模データベースであるほど、より深刻な問題を引き起こしうる事項です。

開発者が見落としがちな FK、NOT NULL、インデックスなどを扱う際の注意点についても、具体的にわかりやすく説明されています。Postgres のバージョンごとの改善点を理解して活用することも役立ちそうです。

何より、現実的なデータサイズで徹底的にテストし、デプロイ自動化を改善していくことが、スキーマ変更のリスクを最小化するうえで重要だという点に共感します。テストやデプロイ自動化のために Database Lab Engine のようなツールを活用してみるのもよいでしょう。

こうした有用なヒントを共有してくれる技術ブログ記事がもっと増えるとよいですね。このような情報が広く共有されるほど、データベースを扱う開発者の力量向上に確実に役立つはずです。