- Postgresのスキーママイグレーションは、ロック・テーブル再書き込み・レプリケーション遅延が本番障害につながる可能性があり、大規模なOLTP環境では特にリスクが高い
- リスクは、
DEFAULTとNOT NULLの同時追加、CONCURRENTLYなしのインデックス作成、即時のカラム削除、安全でない型変更、検証なしの外部キー追加のような、全表スキャンと長時間ロックを引き起こす作業に集中している
- PostgreSQL 11以降では一部のカラム追加コストは下がったが、インデックスは
CREATE INDEX CONCURRENTLY、外部キーはNOT VALIDの後にVALIDATE CONSTRAINTのような、本番影響を抑える手順が必要
- 大量変更は小さなバッチに分け、読み取りレプリカ・レプリケーション遅延・依存オブジェクト・既存アプリケーションインスタンスがそのカラムを参照しているかどうかまで含めて確認すべき
- 本番規模のデータで事前テストを行い、破壊的な作業は多段階デプロイと検証済みのロールバック計画を用意してから進めるべき
スキーママイグレーションの前提
- ここでいうDBマイグレーションはDBMSの移行ではなく、DBスキーマ変更を意味する
- 対象となる変更には3つの性質がある
- 各変更に固有の識別子と自動化された適用手順があるバージョン管理された変更
- 本番適用後は修正せず、新しい変更だけを追加する不変の変更
- データベーススキーマが段階的に進化する増分変更
- 焦点はモバイル・WebアプリケーションのようなOLTPのユースケースであり、1秒を超えるクエリ実行は通常遅すぎるとみなされる
- 小規模なデータベースと低いアクティビティ量では一部の問題は表面化しにくいが、およそ10TiB規模で毎秒10⁴〜10⁵トランザクションの負荷では、ほとんどの問題が発生しうる
- Database Lab Engine はシン・クローンを使って開発とテストに利用され、10TiBのデータベースを10秒以内にクローンして、スキーマ変更のリスクをデプロイ前に確認できる
- GitLab Migration Style Guide は、多数のPostgresスキーマ変更を自動デプロイした経験をまとめた参考資料である
カラム追加とテーブル再書き込み
DEFAULTとNOT NULLを同時に持つカラム追加は、旧バージョンのPostgreSQLで特に危険
- PostgreSQL 11以前では、テーブル全体の再書き込みが必要
- 大きなテーブルでは数時間から数日かかることがあり、その間は書き込みロックが発生する
- 危険な例は次のとおり
ALTER TABLE users ADD COLUMN status text DEFAULT 'active' NOT NULL;
- より安全な手順は、カラム追加・データ更新・制約追加を分ける方法
- まず
NOT NULLなしでカラムを追加する
- 必要なら既存行を更新する
- その後
NOT NULL制約を追加する
ALTER TABLE users ADD COLUMN status text DEFAULT 'active';
-- UPDATE users SET status = 'active' WHERE status IS NULL;
ALTER TABLE users ALTER COLUMN status SET NOT NULL;
- PostgreSQL 11以降では、非volatileな
DEFAULT値を持つカラム追加はもはやテーブル再書き込みを必要としない
インデックス作成と外部キー追加
CONCURRENTLYなしでインデックスを作成すると、通常のインデックス作成はテーブルに排他ロックを取得する
- インデックス作成が終わるまで、すべての書き込みと一部の読み取りがブロックされる可能性がある
- 危険な例は次のとおり
CREATE INDEX idx_users_email ON users(email);
- 本番運用中は
CREATE INDEX CONCURRENTLYの利用がより安全
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
CONCURRENTLYには制約がある
- より長くかかるが、テーブルアクセスを止めない
- トランザクションブロック内では使えない
- 失敗すると、削除が必要な無効インデックスが残ることがある
- 大きなテーブルに外部キー制約を直接追加すると、既存データ検証のために全表スキャンが発生し、長時間ロックを引き起こす
- より安全な手順は、まず
NOT VALIDで制約を追加し、その後トラフィックの少ない時間帯に検証する方法
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user_id
FOREIGN KEY (user_id) REFERENCES users(id)
NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user_id;
カラム削除と型変更
- 本番環境でカラムを即座に削除すると、アプリケーションコードがまだそのカラムを参照している場合にアプリケーションエラーが発生する可能性がある
- カラム削除は多段階で進めるべき
- そのカラムを使わないアプリケーションコードを先にデプロイする
- 古いアプリケーションインスタンスがすべて置き換わるまで待つ
- 別のマイグレーションでカラムを削除する
- カラム型の変更はテーブル再書き込みや互換性問題を生む可能性がある
- ダウンタイム、データ損失、アプリケーションエラーにつながりうる
- 問題になりうる例は次のとおり
ALTER TABLE users ALTER COLUMN id TYPE bigint;
ALTER TABLE users ALTER COLUMN email TYPE varchar(100);
integerからbigintへの変更では、新しいカラムを使った多段階手順が必要varcharの長さを短くする場合は、先にデータを確認し、その変更が本当に必要かを検討すべき
大量変更、レプリケーション、依存オブジェクト
- 1つのトランザクションで過剰な量のデータを変更するマイグレーションは避けるべき
- ロック競合とメモリ使用量が増える
- 問題発生時の復旧時間が長くなる
- レプリケーション遅延が大きくなる可能性がある
- 大規模なデータマイグレーションは、小さなバッチに分けるほうが安全
- マイグレーションが読み取りレプリカとレプリケーション遅延に与える影響も合わせて確認すべき
- 大きなマイグレーションは顕著なレプリケーション遅延を生むことがある
- 読み取りレプリカの性能に影響する可能性がある
- 変更対象のカラムやテーブルに依存するオブジェクトも確認が必要
- ビュー、関数、トリガーなどの依存オブジェクトを見落とすと、連鎖的な障害や追加の手動介入が必要になることがある
テストとロールバック計画
- 小さな開発用データセットだけでマイグレーションをテストしても、大規模データセットの性能特性を確認しにくい
- 本番規模データのクローンでテストすべきであり、Database Lab Engineのようなツールを利用できる
- 問題発生時にマイグレーションを巻き戻す方法がなければ、本番障害が長期的なダウンタイムにつながる可能性がある
- 特に破壊的な作業には、検証済みのロールバック計画が必要
- 安全なスキーマ変更の基本は次のとおり
- 本番規模データでテストする
- 危険な作業には多段階アプローチを使う
CONCURRENTLYやNOT VALIDのようなPostgreSQL機能を活用する- 性能とレプリケーション影響を監視する
- 常にロールバック計画を準備する
1件のコメント
Hacker News の意見
Postgres は本当に好きだが、この記事の大半は避けられるもので、注意に値する内容だと思う。ただし Postgres の最悪なところはロール管理だと思っている。
機能は強力なので、うまく使えれば素晴らしいはずだが、実際に動くようにする過程は黒魔術のように感じる。インターフェースのあちこちが、期待どおりに動くのか分からない難解な呪文のようで、これほど重要なものを管理するにはひどい方式だ。
この部分のマニュアルも薄く、狭いユースケースでだいたいどう動くべきかを示す程度にとどまっている。期待どおりにいかなければ、試行錯誤で何を間違えたのか探すしかなく、正しいやり方はいまだにつかめない。複雑なユーザー権限を持つ DB をマイグレーションしようとすると本当に苦労する。
1か月ほどかけて cookbook を書くべきだと感じた。たとえ1人でも、それを読んで泣きながら眠りにつかずに済むなら価値があるはずだ。
テーブルで
SELECTするには Database のCONNECT、Schema のUSAGEが必要で、Schema の所有者には暗黙的に付与される。Table のSELECTも必要で、テーブル所有者には暗黙的に付与される。権限を見るには、
grantee=privilege-abbreviation[]/grantor:形式の ACL エントリを理解する必要がある。Database の権限は\l+、Schema の権限は\dn+、Table の権限は\dp+で確認できる。権限の一覧は here にある。たとえば
user=arwdDxt/postgresは、postgresロールがユーザーにすべての権限を与えている状態だ。あるオブジェクトの
grantee列が空なら、デフォルトの所有者権限、つまりすべての権限を意味する場合もあれば、存在するすべてのロールであるPUBLICロールへの権限を意味する場合もある。例は=r/postgresだ。publicSchema を使うとさらに混乱する。Schema にCREATE権限があるため、データを参照する同じユーザーでテーブルを作ると、所有者権限がデフォルトで付いてすぐに参照できてしまう。Postgres のロールに関する cookbook を本気で書いて Kickstarter のようなものを始めるなら、真っ先に支援する人の1人になると思う。
それでもいったん動き出すと本当に魔法のようで、関連する仕組み自体は意外にもかなり単純だった。
本番環境で Schema マイグレーションを走らせるなら、
lock_timeoutを使うべきだ。外部キーのあるテーブルの削除や外部キーの削除のように、見た目には無害でテストではほぼ即時に終わる変更でも、トラフィックの多い本番 DB では既存のトランザクションや autovacuum のせいでロック競合に遭遇し得る。
その
ALTERは最初のトランザクションのロックを待ちながらACCESS EXCLUSIVEロックを取ることになり、そうするとロックされたテーブルへのクエリがすべてブロックされる。ある程度の規模で Postgres を運用していれば、こうした競合は時間の問題だ。
lock_timeoutを設定しておけば、ほかのすべてのクエリをブロックしたまま待つ代わりに、制限時間を過ぎるとマイグレーションが失敗する。statement_timeoutはロック待ち時間まで含むため、忙しいテーブルへの影響をよりよく見積もれる。タイムアウトを5秒に設定すれば、総停止時間が最大5秒だと分かり、その後のトランザクションは継続する。
lock_timeoutだけを使うと、ロックを取得した後の作業にどれだけ時間がかかるか制御できず、同時トラフィック次第で速くも遅くもなり得る。クエリを解析して、どの種類のロックを取るのか教えてくれる良い方法があるのか気になる。確信が持てないときは、いつもドキュメントを読み直すやり方でやってきた。
ACCESS EXCLUSIVEロックを取得して待っているのではなく、ロックキューのために待っているのだと理解していた。ALTERはACCESS EXCLUSIVEより低いロックが解放されるのを待っている状態だ。ALTERが永久に実行されない可能性もある。そのテーブルに十分なトラフィックがあれば起こり得る。こうした場合、アプリが復旧可能なら、
ALTERを妨げているほかの進行中クエリを kill するのが最善だと思う。Fly.io の Safe Migrations in Ecto ガイドを、週に何度も参照している。Ecto は Elixir の DB アダプターだ。
デフォルトのマイグレーションで十分か、それとももっと複雑な手順が必要かを素早く確認するのに非常に役立つ参考資料だ。
https://fly.io/phoenix-files/safe-ecto-migrations/
初心者のころ、Postgres のインデックスでいちばん驚いたのは、UNIQUE インデックスが追加のロックによって同時実行クエリの結果に影響し得ることだった
INSERT INTO foo (bar) (SELECT max(bar) + 1 FROM foo);のようなクエリは、デフォルトモードで同時に実行すると重複したbar値を挿入できてしまう。あるトランザクションが、別のトランザクションによって作られた新しい最大値を見られないことがあるためUNIQUE インデックスを追加すると「負けた」トランザクションが制約違反エラーを受け取るように思えるが、実際には両方のトランザクションが成功し、競合状態もなくなる
=# INSERT INTO foo (bar) (SELECT max(bar) + 1 FROM foo);ERROR: duplicate key value violates unique constraint "foo_bar_idx"DETAIL: Key (bar)=(2) already exists.CONCURRENTLYで作成し、検査されていない UNIQUE 制約を作成すれば、無停止で実現できるその制約は新しい
INSERT/UPDATEにだけ適用される。その後、制約に対してVALIDATEを実行すれば、完全な UNIQUE 制約になるよくあることだという点には同意するが、問題は Postgres というよりソフトウェア開発全般にある
こうした落とし穴があるため、無停止スキーママイグレーションの自動化を目指して Reshape [0] を作った
すべての問題を回避できるとは言えないが、それを目標にした新しい製品を作っている。この領域、特に Postgres に関心があるなら連絡してほしい: fabian@reshapedb.com
[0] https://github.com/fabianlindfors/reshape
よく見るもう一つのミスは、テーブルを複製するときにインデックスを入れ忘れることだ
CREATE TABLE SELECT * FROM WHERE <>はそのようには動作しない。バックアップテーブルを作ったり、大量削除をしようとしたりするときに、人々はよくこうするDB バックアップと WAL から復元しなくて済むように、使うことはないだろうが、すぐそこに存在するデータのコピーが欲しいだけだ。インデックスを作るのはサーバー時間とディスク容量の無駄である
物事がこじれたり本当に必要になったりしたら、後でそのインデックスを作ればよい
「Case 2.
IF [NOT] EXISTSの誤用」の部分は、良い誤用例を示していないそして実際にはそう使うのが正しい。きれいで単純で、隠れた落とし穴もない。テーブルが数個しかないなら、スキーママイグレーションツールは過剰な負担だ
悪いデータに絆創膏を貼っても問題は解決せず、隠れるだけだ。問題の種類によっては、後になって予想外の形で、最悪のタイミングで噴き出すことがある
この場合の「悪いデータ」とは、存在すべき、または存在すべきでないのに逆に存在しているテーブル、カラム、ビューのことだ。なぜまだ存在してはいけないテーブルが存在しているのか？ 削除に失敗したのか？ 既存テーブルのスキーマは正しいのか？ 同じマイグレーションが誤って 2 回実行されたのか？
各マイグレーション後のスキーマは正確な状態でなければならない。マイグレーションに
IF [NOT] EXISTSが入っているなら、前のマイグレーション後にスキーマが正確な状態で残らなかったということだ。スキーマの状態を確信できないのはよくない既に存在するテーブルのカラムが、マイグレーションで作ろうとしているものと異なっていたらどうするのか？
IF EXISTSはマイグレーションを成功させてしまうが、スキーマは悪い状態のまま残る。こういう場合は、マイグレーションが早く失敗するほうがよいint4を代理主キーとして使う部分への細かい指摘重要なのはテーブルサイズではなくインデックスサイズではないか？ テーブルサイズにはすでに 23 バイトのヘッダーとアライメント用パディングがあるので、4 バイトの差は大きな影響がない。しかし、インデックスをより多くメモリに載せられるなら利点があるかもしれない。インデックスエントリには 8 バイトのヘッダーがある
また、例に出ている 10 億行は
int4の最大値にかなり近くて不安だそれでも記事は素晴らしい
それは、ディスク上の 8KB ページが RAM 上では 8KB より大きくなることもあるという意味か？
テーブル行データの作業メモリにだけ影響するように思える。それでも重要ではある。特に Postgres は行がランダムな順序なので、範囲クエリの局所性がひどい。ただし決定的な洞察というほどではないと思う
DB 関連の問題からおおむね守られてきた開発者だ。Django の中では、マイグレーションの作成、モデルテーブルの作成、ORM でのクエリは分かるが、内部で起きている多くのことは黒魔術のように感じる
これから会社を始めるにあたり、こうした問題に直面して一人で解決しなければならないのではないかと不安だ。開発環境で何をすべきか学ぶには、どうアプローチすればよいだろう？
Postgres は好きだが、組み込みのバッチ更新/削除方法がない点は本当に嫌いだ
いちばん腹立たしい部分で、壁にぶつかるたびに、ほぼ毎月バッチ処理を書く羽目になる