Rails: 巨大テーブルへのインデックス追加でパフォーマンス低下を避ける方法（翻訳）

Rails: 巨大テーブルへのインデックス追加でパフォーマンス低下を避ける方法（翻訳）

成功したアプリケーションでは、一部のテーブル（usersテーブルなど）がかなり肥大化することがあります。ご興味がおありでしたら、データベースのパフォーマンスを定期的にチェックしてみましょう。メトリクスで遅いクエリが見つかったら、インデックスを付け忘れている可能性が高いでしょう。

🔗 DBエンジンの現状をチェックしよう

現代のデータベースなら、ほとんどの場合非同期かつ非ブロッキング方式でインデックスを作成可能ですが、そのデータベースのルールにどんな例外があるかをもれなく理解しておくことをおすすめします。PostgreSQLまたはMySQLのドキュメントに目を通しておくことを強くおすすめします。
なお、SQLiteについてはドキュメントに詳しく書かれていないのでわかりませんが、チャットAIとのやりとりが何らかのヒントになるかもしれません。

🔗 インデックス追加で何が問題になるか

ご存知のとおり、CREATE INDEXステートメントを実行した場合、適切なアルゴリズムが使われていればデータベースで非同期に処理されます。つまり、実行中に読み取り、書き込み、更新はブロックされません。

Railsアプリケーションの場合、ActiveRecord::Migrationクラスやその周辺機能をすべて利用するRubyプロセス経由で、デプロイ中にマイグレーションを実行するのが普通です。

以下のようなデータベーススキーマがあるとしましょう。

ActiveRecord::Schema[7.1].define(version: 2024_06_13_121701) do
  create_table "users", force: :cascade do |t|
    t.string "email"
    t.boolean "active"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end
end

アカウントが有効でない全ユーザーを手っ取り早く知るために以下を実行するとします。

User.where(active: false)

ユーザー数が数十万〜数億人の場合、フルテーブルスキャンを実行するとデータベースのパフォーマンスが著しく悪化する可能性があります。フルテーブルスキャンが実行されるのは、利用可能なインデックスがデータベースになく、検索条件を満たすかどうかを全行でチェックしなければならない場合です。

以下は特記しない限りPostgreSQLを例に取ります。

🔗 特に工夫しなかった場合

それではインデックスを追加しましょう。

➜  trololo git:(master) bin/rails g migration AddIndexOnActiveUsers
      invoke  active_record
      create    db/migrate/20240613121751_add_index_on_active_users.rb

実装は以下のとおりです。

# PostgreSQLの場合
class AddIndexOnActiveUsers < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!

  def change
    add_index :users, :active, algorithm: :concurrently
  end
end

# MySQLの場合
class AddIndexOnActiveUsers < ActiveRecord::Migration[7.1]
  def change
    add_index :users, :active, algorithm: :inplace
  end
end

それでは実行してみましょう。

➜  trololo git:(master) bin/rails db:migrate
== 20240613121751 AddIndexOnActiveUsers: migrating ============================
-- add_index(:users, :active)
   -> 0.0013s
== 20240613121751 AddIndexOnActiveUsers: migrated (0.0013s) ===================

0.0013sはかなり速い方ですが、手元の開発用コンピュータではこれでよくても、production環境のセットアップでは不十分です。テーブルが十分巨大なら、この0.0013sは6〜7桁増加します。

➜  trololo git:(master) bin/rails db:migrate
== 20240613121751 AddIndexOnActiveUsers: migrating ============================
-- add_index(:users, :active)
   -> 13000.4928s
== 20240613121751 AddIndexOnActiveUsers: migrated (0.0013s) ===================

皆さんの代わりに計算すると、1300.4928sは21分40.49秒となりますが、もっと遅くなる可能性すらあります（私がこのことに気づいた経緯は聞かないでください）。

マイグレーションはいつか終了しますが、その間アプリケーションに他のデプロイを行えなくなってしまいます。以下のさまざまな理由から、このような事態は容認されないでしょう。

• マイグレーションが完了するまで、production環境に他の変更を一切リリースできなくなってしまう
• 何らかの問題によって修正プログラムを緊急でデプロイしなければならなくなっても、マイグレーションが終わるまでデプロイできなくなる
• デプロイ先の環境でプロセスマネージャから出力されるまでの実行時間に制約が設けられている（5分以内など）と、マイグレーションが完了する前に強制終了されてしまう

🔗 ではどうすればよいか？

単にマイグレーションの本体にunless Rails.env.production?も書いておけば、RAILS_ENV=productionではマイグレーションを実行しなくなります。

class AddIndexOnActiveUsers < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!

  def change
    add_index :users, :active, algorithm: :concurrently, if_not_exists: true unless Rails.env.production?
  end
end

これで、production環境でこのマイグレーション実行されてもデータベースに一切影響しなくなります。このとき、schema.rbまたはstructure.sqlのどちらを利用していても自動調整され、schema_migrationsテーブルにも必要なエントリが出現し、ローカルのdevelopment環境とtest環境も同様に調整されます。

しかし「それならproductionのインデックスはどうやって作成するの？」と疑問に思うでしょう。これはなかなかよい質問です。production環境で必要なのは、（マイグレーションではなく）Railsランナーまたはrakeタスクでインデックスを作成することです。状況に応じて利用可能な方法を選べます。

🔗 1: bin/rails consoleで実行する場合

# PostgreSQLの場合
ActiveRecord::Migration.add_index :users, :active, algorithm: :concurrently, if_not_exists: true

# MySQLの場合
ActiveRecord::Migration.add_index :users, :active, algorithm: :inplace, if_not_exists: true

🔗 2: 同じことをbin/rails runnerで実行する場合

# PostgreSQLの場合
bin/rails r "ActiveRecord::Migration.add_index :users, :active, algorithm: :concurrently, if_not_exists: true"

#MySQLの場合
bin/rails r "ActiveRecord::Migration.add_index :users, :active, algorithm: :inplace, if_not_exists: true"

🔗 3: rakeタスクを実装する（本命）

タスクを書くとリポジトリにコミットせざるを得なくなるので、実行の履歴が失われずに済むというメリットもあります。

# PostgreSQLの場合
namespace :indexes do
  task add_index_on_active_users: :environment do
    ActiveRecord::Migration.add_index :users, :active, algorithm: :concurrently, if_not_exists: true
  end
end

# MySQLの場合
namespace :indexes do
  task add_index_on_active_users: :environment do
    ActiveRecord::Migration.add_index :users, :active, algorithm: :inplace, if_not_exists: true
  end
end

上をbin/rails indexes:add_index_on_active_usersで実行します。

最後のオプション３は、GrafanaやDatalogなどのログツールで実行結果を手軽にログ出力できるというメリットもあります。

namespace :indexes do
  task add_index_on_active_users: :environment do
    Rails.logger.info("task indexes:add_index_on_active_users started")

    ActiveRecord::Migration.add_index :users, :active, algorithm: :concurrently, if_not_exists: true

    Rails.logger.info("task indexes:add_index_on_active_users finished")
  end
end

🔗 細かな注意点

注意深い人なら、if_not_exists: trueというフラグが使われていることに気づいたことでしょう。私たちがこのフラグを指定する理由は、冪等性を重視しているためです。つまりこのタスクを別の誰かがうっかり実行しても何も起きなくなります。代わりにActiveRecord::StatementInvalidを表示したい場合は、このフラグをスキップして構いません。

上述したように、インデックス作成時に適切なアルゴリズムを指定する必要もあります。PostgreSQLの場合はalgorithm: :concurrently、MySQLの場合はalgorithm: inplaceを指定しなければなりません。

PostgreSQLにはもう1つ奇妙な点があります。

この他に、通常のCREATE INDEXコマンドはトランザクションブロック内で実行させることができますが、CREATE INDEX CONCURRENTLYは実行させることができないという相違点があります。
CREATE INDEX -- www.postgresql.jpより

Railsでは、個別のマイグレーションをトランザクションでラップします。この振る舞いを無効にするには、マイグレーションにdisable_ddl_transaction!を書く必要があります。さもないと、以下のエラーが発生します。

bin/rails aborted!
StandardError: An error has occurred, this and all later migrations canceled: (StandardError)

PG::ActiveSqlTransaction: ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block
/Users/fidel/code/fidel/trololo/db/migrate/20240613121751_add_index_on_active_users.rb:5:in `change'

Caused by:
ActiveRecord::StatementInvalid: PG::ActiveSqlTransaction: ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block (ActiveRecord::StatementInvalid)
/Users/fidel/code/fidel/trololo/db/migrate/20240613121751_add_index_on_active_users.rb:5:in `change'

Caused by:
PG::ActiveSqlTransaction: ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block (PG::ActiveSqlTransaction)
/Users/fidel/code/fidel/trololo/db/migrate/20240613121751_add_index_on_active_users.rb:5:in `change'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)

ただしこれは、production環境でカスタムスクリプトやrakeタスクを実行する場合は問題になりません。

関連記事

Rails: Active Recordモデルのカラムを安全に削除する（翻訳）

Rails: データベーススキーマをダウンタイムなしで変更する（翻訳）