ここ最近のテーブル変更の挙動
MySQLやPostgreSQLの進化によって過去のプラクティスが通用しなくなり、新しいプラクティスを学ぶ必要があります。 特に大量のデータを持つテーブルへのカラムの追加のプラクティスはWeb上でも古いプラクティスが多く見られる。 ここでは過去のプラクティスを見つつ、現在の変更によってプラクティスがどう変わったのか紹介しようと思います。
MySQLのテーブル変更
MySQL 5.6のテーブル変更は元のテーブルの一時的なコピーを作成することで行われていました。 大きな流れとしては次のとおりです。
- 変更対象のテーブルをロックします。（条件にもよるが）書き込みはロックされ読み取りは許可されます
- 新しいテーブル定義を使って一時的なテーブルを作成します
- 古いテーブルから新しいテーブルへデータをコピーします
- 元のテーブルを削除したあと、新しいテーブルの名前を変更します
- 新しいテーブルへブロックしていた更新処理をリダイレクトします
この仕様のため、MySQL 5.6のテーブル変更時間はデータ量の増加に依存して増えていきます。 テーブル変更に時間がかかることによって書き込みが長時間ロックされてしまう問題がありました。 そのため、数100万レコードのテーブル変更では次のようなプラクティスが生まれました。
- 新しいテーブルを別名で作成する
- 古いテーブルから新しいテーブルにデータを移す。（場合によってはトリガーなども使われる
- 古いテーブル削除し、新しいテーブルの名前を変更する
これはMySQLが内部でやっていることを別テーブルで行い、データ転送を行なっている間の書き込みロックを避ける狙いがありました。
MySQL 5.7、8.0ではこのテーブルコピーによるテーブル変更だけでなく、別の選択肢を持てるようになりました。
MySQL 5.7では新たにINPLACEアルゴリズムが、MySQL 8.0ではさらにINSTANTアルゴリズムが追加されました。
ここではMySQL 8.0を対象とした話をします。 MySQL 8.0ではテーブル変更を実行した際、変更内容によって次の３つのうちのいずれかの動作をします。
- COPY: MySQL 5.6と同様の挙動をします。つまり、新しい定義のテーブルを作成し、データコピー後にテーブルの差し替えをします。
- INPLACE: この操作が選ばれた場合、テーブルデータのコピーは回避されますが、テーブルが適切に再構築される可能性があります。操作の準備フェーズおよび実行フェーズでは、テーブルに対する排他的メタデータロックが短時間で取得される場合があります。
- INSTANT: この操作の場合、データディクショナリ内のメタデータのみが変更されます。テーブルデータは影響を受けず、操作が即時に行われます。
INPLACEが実行されるものの代表としては次のものがあります。
- テーブルの名前の変更
- セカンダリインデックスの追加
- カラム名の変更
- カラムのデフォルト値の変更
また、INSTANTが実行されるものの代表としては次のものがあります。
- カラムの追加
- カラムのデフォルト値の追加または削除
- インデックスタイプの変更
MySQL 8.0ではデフォルトの動作としてINSTANT → INPLACE → COPYの順に選択するため、自動的に最適なテーブル変更アルゴリズムを選択します。
この仕様のため、カラム追加やカラム名変更はデータ量に関わらず高速に行われます。
過去行われていたテーブル変更時に別テーブルを作成するプラクティスは、カラム追加には不要となり、COPYのアルゴリズムが選ばれるカラム削除等に限定されます。
PostgreSQLのテーブル変更
Postgresqlはテーブルの変更時にはACCESS EXCLUSIVEロックという強力なロックを獲得するものの、
テーブルのカラムの情報をpg_attributeカタログで管理しているため、カラムの追加は高速に行われます。
一方、PostgreSQLは追記型アーキテクチャであり、既存の列のデータ型を更新するなどデータの更新が発生した場合、
更新後のすべてのデータが追加する必要があり、長時間ロックする恐れがあります。
この挙動はPostgresqlのバージョンによっても異なります。
Postgresql 10以前の場合、Default句を追加する際にデータの更新が必要なため、データ量に依存した処理時間がかかります。
そしてこの処理中はACCESS EXCLUSIVEロックを行なっているため、読み書きができずMySQLよりも厄介な状況になります。
これはPostgresql 11で改善しており、pg_attributeカタログにデフォルト値を持つようになったため、メタデータの変更のみで対応できるようになりました。
そのため、過去行われていたNOT NULL制約とDefault句を持つカラムを追加する際に、先にカラムを追加し、後から制約をつけるプラクティスは現在では不要になりました。
一方、外部キーや検査制約を追加する際は制約を検証するためにテーブルスキャンが発生し、データ量に依存した処理時間がかかります。
公式ドキュメントではこの問題への対策として、まずデータ検証しないNOT VALIDをつけて制約を追加し、後から検証をする方法を提案しています。
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) NOT VALID;
ALTER TABLE distributors VALIDATE CONSTRAINT distfk;（MySQLのデフォルトの動作では制約の検証はカラム追加時には行われず、テーブルからの読取り時に検証し、カラムの警告またはエラーが報告される可能性があります。 ）
まとめ
MySQLとPostgreSQLも日々進化しており、過去のプラクティスが不要になっているケースがあります。
MySQLのCOPYアルゴリズムやPostgreSQLのテーブルスキャンなどデータ量に依存するテーブル変更を避けることで、
どのようなデータ量であっても単純なALTER TABLEで対処できることがわかりました。