エムスリーテックブログ

エムスリー(m3)のエンジニア・開発メンバーによる技術ブログです

巨大テーブルにインデックスを追加したい、Flywayで

PostgreSQL

エムスリーエンジニアリンググループGMで、データ基盤チームの木田です。 この記事はデータ基盤チーム & Unit9（エビデンス創出プロダクトチーム） ブログリレー3日目の記事です。前回は橋口さんの データ整備の「曖昧さ」に立ち向かう、ドメインエキスパートと協業するための実践的Tipsでした。

本日は、既存のPostgreSQLのさるテーブル (レコード数 100億超) に対して、差分データをSQLで抽出してBigQueryに連携するための索引追加をしたときの経験を元に、PostgreSQLの巨大テーブルに対してFlywayを使用してインデックスを安全に追加する方法についていくつかのテクニックを紹介します。

背景

データソースのシステムは Kotlin + Spring Boot で構築されており、データベースは Amazon Aurora (PostgreSQL)、マイグレーションには Flyway が採用されていました。このシステムからBigQueryにデータ連携する対象テーブルのうちいくつかは非常にサイズが大きく、今回の要件に対しては適切なインデックスが存在しなかったためインデックスの追加が必要になりました。

今回のやりたかった事をざっくり図にしたもの

理想を言えば設計時にパーティショニングやインデックスの計画を立てておくのが望ましいですが、本稿では既存テーブルへの対応に焦点を当てて対応した内容を紹介します。

ナイーブな実装の問題点

まずはこのような単純なマイグレーションを考えてみましょう。

CREATE INDEX idx_column_name ON large_table(column_name);

このSQL自体は正しく動作しますが、巨大テーブルの場合にはいくつかの問題点が想定されます。

  • インデックス作成に非常に長い時間を要する可能性がある
  • インデックス作成中はテーブルにロックがかかり、書き込みがブロックされる
  • サービスの中核となるテーブルの場合、停止メンテナンスなしでは実質的に実行不可能

このため、テスト環境でうまくいったからといっても本番環境での実行には慎重な対応が必要です。

CREATE INDEX CONCURRENTLYの使用

PostgreSQLには、テーブルへの書き込みをブロックせずにインデックスを作成するCREATE INDEX CONCURRENTLYという機能があります。

CREATE INDEX CONCURRENTLY idx_column_name ON large_table(column_name);

CREATE INDEX CONCURRENTLYは通常のインデックス作成が1回のテーブルスキャンで済むところを2回実行することで、テーブルに対する書き込みをブロックせずにインデックスを構築します。

PostgreSQL supports building indexes without locking out writes. This method is invoked by specifying the CONCURRENTLY option of CREATE INDEX. When this option is used, PostgreSQL must perform two scans of the table...1

この仕組みによって、テーブルのロック時間を最小限に抑えながらインデックス作成ができるため、ロック時間が心配であればCREATE INDEX CONCURRENTLYの使用を積極検討すべきでしょう。

より詳細な動作としては次のような流れになります。

  1. インデックスを「無効(invalid)」状態でシステムカタログに登録する
  2. 1回目のテーブルスキャンを実行してインデックスを構築する
  3. 再び既存トランザクションの終了を待機し、2回目のテーブルスキャンを実行して、インデックスを最新状態に更新する
  4. 古いスナップショットを持つトランザクションの終了を待ち、インデックスを「有効(valid)」としてマークする

この多段階の処理により、テーブルへの書き込みをブロックせずにインデックスを構築できます。 一方でテーブルスキャンが２回実行されるのでトータルの計算コスト、I/Oや実行時間は増加します。また、エラー発生時に不完全なINVALIDなインデックスが残るため、リカバリー時は削除してから再実行する必要があります。

Flywayから実行する時の注意点

CREATE INDEX CONCURRENTLYはトランザクション内での実行ができません。Flywayからマイグレーションを実行する時、デフォルトではトランザクション内で実行されるため、以下のようなエラーが発生します。

ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block

Another difference is that a regular CREATE INDEX command can be performed within a transaction block, but CREATE INDEX CONCURRENTLY cannot.2

そこでFlywayの設定で、該当のマイグレーションスクリプトがトランザクション外で実行されるようにします。 Flywayの設定で以下の2つの設定をfalseにします。

flyway.postgresql.transactional.lock=false
flyway.executeInTransaction=false

Flyway全体の設定を変更するのではなく、マイグレーションファイルごとに制御したい場合は、Script Configuration機能3を利用します。 この方法を使うと、executeInTransaction についてはマイグレーションファイルごとに制御できるため、特定のマイグレーションのみをトランザクション外で実行できます。

例えば対象のマイグレーションファイル名が V2__create_index_concurrently.sql の場合、同じディレクトリに V2__create_index_concurrently.sql.conf という名前で以下の内容のファイルを作成します。

executeInTransaction=false

余談として、今回の構成ではSpring bootのFlyway拡張を使用していました。バージョンの組み合わせによっては、設定が正しく反映されません。この問題は、対応バージョンへの更新で解決しました。

タイムアウトへの対処

CREATE INDEX CONCURRENTLY を実行すると、インデックス作成処理そのものは他のトランザクションをブロックせずに行われますが、DDLの実行自体は同期処理的に行われます。つまり、Flywayの場合ですとマイグレーションプロセスはインデックス作成が完了するまで待機します。このため、

  • JDBCのタイムアウト設定 (socketTimeoutなど) を必要に応じて延長する
  • AWS LambdaなどのFaaSでマイグレーションを実行している場合、実行時間制限に注意する (必要に応じて別の実行環境の検討が必要になります)

といった点に注意が必要です。

一時領域不足

上記対策をとってなおうまくいかないケースとして、一時領域の不足がありました。大規模テーブルのCREATE INDEX時は work_memに収まらないことが多いです。work_mem に収まらない中間データは一時ファイルに書き出されます。Aurora PostgreSQLの場合、インスタンスの一時領域が不足すると以下のようなエラーが発生します。

ERROR: could not write to temporary file: No space left on device

Auroraの ドキュメント でも触れられている通り、一時領域のサイズはインスタンスクラスごとに決まっており、スケールアップしたり、検討するかあるいはインデックスサイズ自体を抑える工夫が必要になってきます。

インデックスサイズを抑える工夫

インデックスサイズを小さくするための工夫として今回2つの方法を試しました。

ブロックレンジインデックス (BRIN) の利用

BRIN（Block Range Index）インデックスは、あまり使う機会が多くありませんが、以下のような特徴を持つインデックス形式です。

BRIN indexes (a shorthand for Block Range INdexes) store summaries about the values stored in consecutive physical block ranges of a table. Thus, they are most effective for columns whose values are well-correlated with the physical order of the table rows.4

CREATE INDEX CONCURRENTLY idx_brin_created_time
  ON large_table
  USING BRIN(created_time);

特に何かのログのような追記型のテーブルでは、連番のIDやタイムスタンプ列と物理的な並び順が一致しやすくなります。所定期間のデータを一括抽出してBigqueryに連携するような今回のユースケースでは、この特性を活かせます。

BRINインデックスはB-Treeインデックスと比較して、サイズを小さく抑えられ、作成時間も短くて済みます。実際の事例では、B-Treeインデックスと比較して100分の1以下のサイズになりました。

インデックス対象の列とデータの物理的な並び順が一致してるかどうかは、pg_statsに対して以下のようなクエリで確認できます。 相関係数が1に近ければ、BRINインデックスが有効に機能する可能性が高いです。

SELECT
  correlation
FROM
  pg_stats
WHERE
  tablename = 'large_table'
  AND attname = 'created_time';

部分インデックスの利用

部分インデックス（Partial Index）は、インデックスを必要な行のみに限定することで、インデックスサイズを削減し、クエリ性能の向上やテーブル更新操作の高速化が期待できます。例えば、巨大なテーブルだけれどもクエリしたいのは直近更新されたデータだけ、という場合には以下のような部分インデックスを定義することでインデックスサイズを抑えられます。

A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index).5

CREATE INDEX CONCURRENTLY idx_updated_time
  ON large_table(column_name)
  WHERE updated_time >= '2025-01-01 00:00:00';

ただし、クエリのWHERE句に部分インデックスで定義された条件が含まれていないとインデックスが利用されないことに注意が必要です。 更新が頻繁にかかってBRINの適用が難しいケースでは部分Indexを作るという手でサイズの抑制が見込めます。

まとめ

Flywayを用いた巨大なテーブルに対するインデックス追加時の考慮点や、インデックスサイズを抑える工夫について紹介しました。 インデックス追加時の参考になれば幸いです。

We are hiring!

エムスリーでは、データベースを扱うサービスの開発・運用を支えるエンジニアを募集中です。少しでもご興味をお持ちの方は、以下ページよりカジュアル面談等にお申し込みください！

jobs.m3.com

  1. https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY↩
  2. https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY↩
  3. https://documentation.red-gate.com/flyway/reference/script-configuration↩
  4. https://www.postgresql.org/docs/current/indexes-types.html#INDEXES-TYPES-BRIN↩
  5. https://www.postgresql.org/docs/current/indexes-partial.html↩