MySQL や PostgreSQL でトリガーベースの実体化ビューを後から追加する方法 (もしくは無停止での CREATE INDEX)

• 読み込み＞書き込みなデータベースだと、実体化ビュー (materialized view) を使って読み込み速度を上げるってのは有効な手法

• ちなみに MySQL や PostgreSQL だと実体化ビューはトリガーを使って書く *1

• では、トリガーベースの実体化ビューを後から追加した場合に、どうやって既存データを新しいビューに反映させるのか。

• UPDATE トリガを、ビューの側に対応するデータがない場合は INSERT トリガと同様の動作をするように実装すればいい (典型的には REPLACE INTO 文を使う)。ビューの初期データ充填は

UPDATE src_table SET id=id;

• MySQL だと CREATE INDEX CONCURRENTLY がないから副インデックス作成はスレーブでやったりする*2けど、上の UPDATE を LIMIT つきで回すことで、ビューをインクリメンタルに生成することができる (なので、インデックスを追加するかわりに実体化ビューを作るという手もある)

• Incline が生成するトリガは上の要件を満たすようになった
  • レポジトリは http://github.com/kazuho/incline へ移転しました

*1:分離レベルは SERIALIZABLE, PostgreSQL なら SELECT FOR UPDATE じゃないといろいろ問題かも

*2:その場合にフェイルオーバーさせるのが面倒とか言うけど、副インデックスってマスターには不要な場合がほとんどじゃないのかなと思ってる