データベースインデックスの追加

インデックスはデータベースのクエリを高速化するために使用できますが、どのような場合に新しいインデックスを追加すべきでしょうか？従来、この質問に対する答えは、データのフィルタリングや結合に使用するすべての列にインデックスを追加することでした。例えば、以下のクエリを考えてみましょう：

SELECT *
FROM projects
WHERE user_id = 2;

ここでは、user_id カラムでフィルタリングを行っています。開発者は、このカラムにインデックスを付けることを決定するかもしれません。

このような場合、このカラムにインデックスを付けることになるかもしれません。テーブルにデータを書き込むと、既存のインデックスも更新しなければなりません。インデックスが多ければ多いほど、これは遅くなる可能性があります。インデックスは、インデックス付けされるデータの量やインデックスの種類にもよりますが、かなりのディスク容量を消費します。例えば、PostgreSQLはGIN インデックスを提供しており、通常のB-treeインデックスではインデックス付けできない特定のデータ型にインデックスを付けることができます。しかし、これらのインデックスは一般的にB-treeインデックスに比べてより多くのデータを必要とし、更新速度も遅くなります。

これらのことから、新しいインデックスを追加する際には、以下の点を考慮することが重要です：

  1. 新しいクエリはできるだけ多くの既存のインデックスを再利用しますか？
  2. インデックスを使用した方がテーブルの行を繰り返し処理するよりも高速になるだけのデータがありますか？
  3. インデックスのメンテナーのオーバーヘッドは、クエリの時間短縮に見合うものですか？

状況によっては、インデックスは必要ないかもしれません：

  • テーブルのサイズが小さく（1,000 レコード以下）、指数関数的に大きくなることが予想されない場合です。
  • 既存のインデックスで十分な行がフィルタリングされています。
  • インデックスが追加された後のクエリ時間の短縮はそれほど大きくありません。

さらに、ワイドインデックスはクエリの全てのフィルタ条件にマッチする必要はありません。インデックス検索が十分に小さい選択性を持つように、十分なカラムをカバーする必要があるだけです。

クエリの再利用

最初のステップは、クエリができるだけ多くの既存のインデックスを再利用するようにすることです。例えば、以下のクエリを考えてみましょう：

SELECT *
FROM todos
WHERE user_id = 123
AND state = 'open';

このuser_id カラムには user_idすでにインデックスがuser_id ありますが、 user_idこのuser_id state カラムには state user_idインデックスがないとします。user_id インデックスがないため、state このクエリのパフォーマンスは悪いと思うかも stateしれません。実際には、インデックスが user_id十分な行をフィルタリングuser_id できれば、このクエリは問題なく実行 user_idできます。

インデックスが再利用されているかどうかを判断する最善の方法は、EXPLAIN ANALYZE を使用してクエリを実行することです。結合されたテーブルやフィルタリングに使用される列によっては、インデックスを追加してもあまり変わらないことがわかるかもしれません。

要するに

  1. できるだけ多くの既存のインデックスを再利用するようにクエリを書いてください。
  2. EXPLAIN ANALYZE を使ってクエリを実行し、出力を調べて最も理想的なクエリを見つけます。

データサイズ

特に小さなテーブルでは、通常のシーケンススキャン（すべての行を反復処理する）の方が高速であっても、データベースはインデックスを使用しない場合があります。

テーブルが大きくなることが予想され、クエリが多くの行をフィルタリングする必要がある場合は、インデックスの追加を検討してください。テーブルのサイズが小さい（<1,000 レコード）場合や、既存のインデックスですでに十分な行がフィルタリングされている場合は、インデックスを追加_したくないかも_しれません。

メンテナンスのオーバーヘッド

インデックスはテーブル書き込みの度に更新しなければなりません。PostgreSQLの場合、テーブルにデータが書き込まれる度に既存の_全ての_インデックスが更新されます。その結果、同じテーブルに多くのインデックスがあると、書き込みが遅くなります。したがって、クエリの性能と余分なインデックスのメンテナーのオーバーヘッドのバランスを取ることが重要です。

例えば、インデックスを追加するとSELECTの時間が5ミリ秒短くなりますが、INSERT/UPDATE/DELETEの時間が10ミリ秒長くなるとします。この場合、新しいインデックスの価値はないかもしれません。SELECTの時間が短縮され、INSERT/UPDATE/DELETEの時間が影響を受けない場合、新しいインデックスの価値は高くなります。

未使用インデックスの検索

未使用のインデックスを確認するには、以下のクエリを実行します：

SELECT relname as table_name, indexrelname as index_name, idx_scan, idx_tup_read, idx_tup_fetch, pg_size_pretty(pg_relation_size(indexrelname::regclass))
FROM pg_stat_all_indexes
WHERE schemaname = 'public'
AND idx_scan = 0
AND idx_tup_read = 0
AND idx_tup_fetch = 0
ORDER BY pg_relation_size(indexrelname::regclass) desc;

このクエリは、一度も使用されていない全てのインデックスを含むリストを出力し、インデックスのサイズの降順でソートします。このクエリは、既存のインデックスがまだ必要かどうかを判断するのに役立ちます。様々な列の意味についての詳細は、https://www.postgresql.org/docs/current/monitoring-stats.htmlを参照してください。

インデックスが本番環境でまだ使用されているかどうかを調べるには、Thanosを使用してください：

sum by (type)(rate(pg_stat_user_indexes_idx_scan{env="gprd", indexrelname="INSERT INDEX NAME HERE"}[30d]))

クエリ出力はデータベースの実際の使用状況に依存するため、以下のような要因の影響を受ける可能性があります：

  • 特定のクエリが実行されず、特定のインデックスを使用できない場合。
  • PostgreSQLがインデックススキャンではなくシーケンススキャンを使用することになる、データが少ないテーブルがあります。

このデータは、データ量が多く、GitLabの機能を可能な限り使用している、頻繁に使用されるデータベースに対してのみ信頼できるものです。

インデックスの命名に必要な条件

複雑な定義を持つインデックスは、マイグレーションメソッドの暗黙の命名動作に頼るのではなく、 明示的に命名する必要があります。つまり、以下のオプションの1つ以上を使用して作成されたインデックスには、明示的な名前引数を与えなければなりません：

  • where
  • using
  • order
  • length
  • type
  • opclass

インデックス名の考慮点

制約の命名規則のページをご覧ください。

明示的な名前が必要な理由

Railsはデータベースにとらわれないため、すべてのインデックスに必要なオプションであるテーブル名とカラム名のみからインデックス名を生成します。たとえば、マイグレーションで次の2つのインデックスが作成されたとします：

def up
  add_index :my_table, :my_column

  add_index :my_table, :my_column, where: 'my_column IS NOT NULL'
end

Railsは両方のインデックスに同じ名前を生成してしまうため、2つ目のインデックスの作成は失敗するでしょう。

この名前のイシューは、index_exists? メソッドの動作によってさらに複雑になります。このメソッドは比較を行う際に、テーブル名、カラム名、インデックスの一意性指定のみを考慮します。考えてみてください：

def up
  unless index_exists?(:my_table, :my_column, where: 'my_column IS NOT NULL')
    add_index :my_table, :my_column, where: 'my_column IS NOT NULL'
  end
end

:my_table と:my_column にインデックスが存在する場合、index_exists? の呼び出しは真を返し、インデックス作成はバイパスされます。

add_concurrent_index ヘルパーは、存在するテーブルにインデックスを作成するために必要なものです。このヘルパーはトランザクションマイグレーション内部では使用できないため、インデックスが既に存在するかどうかを検出する組み込みのチェック機能があります。一致するイベントが見つかった場合、インデックスの作成はスキップされます。明示的な名前の引数がないと、Railsはindex_exists? 、必要なインデックスが正しく作成されないという偽陽性を返す可能性があります。特定のタイプのインデックスに対して常に名前を要求することで、エラーの可能性を大幅に減らすことができます。

インデックスの存在のテスト

名前によってインデックスの存在をテストする最も簡単な方法はindex_name_exists? メソッドを使用することですが、index_exists? メソッドは name オプションを付けて使用することもできます。例えば

class MyMigration < Gitlab::Database::Migration[2.1]
  INDEX_NAME = 'index_name'

  def up
    # an index must be conditionally created due to schema inconsistency
    unless index_exists?(:table_name, :column_name, name: INDEX_NAME)
      add_index :table_name, :column_name, name: INDEX_NAME
    end
  end

  def down
    # no op
  end
end

add_concurrent_index 、remove_concurrent_index 、remove_concurrent_index_by_name のような同時実行インデックスヘルパーは、すでに内部で存在チェックを実行していることに注意してください。

一時インデックス

インデックスが一時的にしか必要とされない場合があります。

例えば、マイグレーションにおいて、テーブルの列が条件付きで更新される場合があります。クエリ・パフォーマンス・ガイドラインでどのカラムが更新されなければならないかをクエリするために、他の方法では使用されないインデックスが必要になります。

このような場合、一時インデックスを検討してください。一時インデックスを指定するには

  1. インデックス名の前にtmp_ を付け、命名規則に従います。
  2. 次の（または将来の）マイルストーンでインデックスを削除するためのフォローアップイシューを作成してください。
  3. 削除イシューについてマイグレーションにコメントを追加してください。

一時的なマイグレーションは次のようになります：

INDEX_NAME = 'tmp_index_projects_on_owner_where_emails_disabled'

def up
  # Temporary index to be removed in 13.9 https://gitlab.com/gitlab-org/gitlab/-/issues/1234
  add_concurrent_index :projects, :creator_id, where: 'emails_disabled = false', name: INDEX_NAME
end

def down
  remove_concurrent_index_by_name :projects, INDEX_NAME
end

バッチバックグランドマイグレーション前の新しいインデックスの分析

バッチバックグラウンドマイグレーションをサポートするためにインデックスを追加する必要があることがあります。一般的には、デプロイ後のマイグレーションを2つ作成します：

  1. 新しいインデックス（多くの場合は一時インデックス）を追加します。
  2. バッチバックグランドマイグレーションをキューに入れます。

ほとんどの場合、追加の作業は必要ありません。新しいインデックスが作成され、バッチドバックグラウンドマイグレーションをキューに入れ実行する際に期待通りに使用されます。

しかし、式インデックスは、作成時に新しいインデックスの統計情報を生成しません。Autovacuumは最終的に実行さANALYZEれ、新しいインデックスが使用されるように統計情報を更新 ANALYZEします。上記のバックグラウンドマイグレーションシナリオのように、インデックスが作成された直後に必要な場合のみ、明示的にANALYZE実行 ANALYZEします。

インデックス作成後にANALYZE をトリガするには、インデックス作成マイグレーションを更新してテーブルを分析します：

# in db/post_migrate/

INDEX_NAME = 'tmp_index_projects_on_owner_and_lower_name_where_emails_disabled'
TABLE = :projects

disable_ddl_transaction!

def up
  add_concurrent_index TABLE, '(creator_id, lower(name))', where: 'emails_disabled = false', name: INDEX_NAME

  connection.execute("ANALYZE #{TABLE}")
end

ANALYZE はデプロイ後のマイグレーションでのみ実行し、大きなテーブルを対象にすべきではありません。大きなテーブルでこの動作が必要な場合は、#database Slack チャンネルで支援を求めてください。

パーティショニングされたテーブルのインデックス

パーティショニングされたテーブルにインデックスを同時に 作成することはできません。ホット・システムでのサービス中断を避けるため、CONCURRENTLY を使用する必要があります。

パーティショニングされたテーブルにインデックスを作成するには、データベース・チームが提供するadd_concurrent_partitioned_index を使用してください。

内部では、add_concurrent_partitioned_index：

  1. CONCURRENTLYを使用して、各パーティションにインデックスを作成します。
  2. 親テーブルにインデックスを作成します。

Railsマイグレーションの例：

# in db/post_migrate/

class AddIndexToPartitionedTable < Gitlab::Database::Migration[2.1]
  include Gitlab::Database::PartitioningMigrationHelpers

  disable_ddl_transaction!

  TABLE_NAME = :table_name
  COLUMN_NAMES = [:partition_id, :id]
  INDEX_NAME = :index_name

  def up
    add_concurrent_partitioned_index(TABLE_NAME, COLUMN_NAMES, name: INDEX_NAME)
  end

  def down
    remove_concurrent_partitioned_index_by_name(TABLE_NAME, INDEX_NAME)
  end
end

インデックスの非同期作成

非常に大きなテーブルの場合、インデックス作成を管理することは困難です。add_concurrent_index は通常のトラフィックをブロックしない方法でインデックスを作成しますが、それでもインデックス作成が何時間も実行されると問題が発生します。autovacuum のような必要なデータベースオペレーションは実行できず、GitLab.com ではデプロイプロセスはインデックス作成の終了を待ってブロックされます。

GitLab.comへの影響を抑えるために、週末の時間帯にインデックスを非同期で作成するプロセスが存在します。一般的にトラフィックが少なく、デプロイも少ないため、インデックス作成はより低いリスクレベルで進めることができます。

影響の少ない時間帯にインデックス作成をスケジュール

  1. インデックスの作成をスケジュールします。
  2. MR がデプロイされ、インデックスが本番環境に存在することを確認します。
  3. 同期的にインデックスを作成するマイグレーションを追加します。

インデックスの作成スケジュール

  1. 非同期作成のためにインデックスを準備するデプロイ後のマイグレーションを含むマージリクエストを作成します。
  2. 後続のイシューを作成し、同期的にインデックスを作成するマイグレーションを追加します。
  3. 非同期インデックスを準備するマージリクエストに、フォローアップ課題についてのコメントを追加します。

非同期インデックスヘルパーを使用してインデックスを作成する例を以下のブロックに示します。このマイグレーションでは、postgres_async_indexes テーブルにインデックス名と定義を入力します。週末に実行されるプロセスはこのテーブルからインデックスを取り出し、作成を試みます。

# in db/post_migrate/

INDEX_NAME = 'index_ci_builds_on_some_column'

# TODO: Index to be created synchronously in https://gitlab.com/gitlab-org/gitlab/-/issues/XXXXX
def up
  prepare_async_index :ci_builds, :some_column, name: INDEX_NAME
end

def down
  unprepare_async_index :ci_builds, :some_column, name: INDEX_NAME
end

MR がデプロイされ、本番環境にインデックスが存在することを確認します。

  1. ChatOpsを使用して、デプロイ後のマイグレーションがGitLab.comで実行されたことを/chatops run auto_deploy status <merge_sha> 。出力がdb/gprd を返す場合、デプロイ後のマイグレーションは本番データベースで実行されています。詳細については、デプロイ後のマイグレーションがGitLab.comで実行されたかどうかを判断する方法を参照してください。
  2. 非同期で作成されたインデックスの場合は、週末にインデックスを作成できるよう、次の週まで待ちましょう。
  3. Database Labを使用して、作成が成功したかどうかを確認します。出力がインデックスがinvalid であることを示していないことを確認してください。

同期的にインデックスを作成するマイグレーションを追加します。

本番データベースにインデックスが存在することが確認された後、同期的にインデックスを追加する2番目のマージリクエストを作成します。この二番目のマージリクエストでは、スキーマの変更を更新してstructure.sql にコミットする必要があります。同期マイグレーションは GitLab.com 上では失敗となりますが、他のインストールでは期待通りにマイグレーションを追加する必要があります。以下のブロックは、先ほどの非同期の例で二回目のマイグレーションを作成する方法を示しています。

警告: add_concurrent_index 。インデックスが作成される前に2つ目のマイグレーションがデプロイされた場合、インデックスは2つ目のマイグレーションが実行されたときに同期的に作成されます。

# in db/post_migrate/

INDEX_NAME = 'index_ci_builds_on_some_column'

disable_ddl_transaction!

def up
  add_concurrent_index :ci_builds, :some_column, name: INDEX_NAME
end

def down
  remove_concurrent_index_by_name :ci_builds, INDEX_NAME
end

データベースのインデックス変更をローカルでテスト

マージリクエストを作成する前に、データベースインデックスの変更をローカルでテストする必要があります。

非同期に作成されたインデックスの検証

インデックスを作成するための変更をテストするために、ローカル環境で非同期インデックスヘルパーを使用します：

  1. RailsコンソールでFeature.enable(:database_async_index_creation) 、Feature.enable(:database_reindexing) 、機能フラグを有効にします。
  2. bundle exec rails db:migrate を実行して、postgres_async_indexes テーブルにエントリを作成します。
  3. bundle exec rails gitlab:db:reindex を実行し、インデックスが非同期に作成されるようにします。
  4. インデックスを確認するには、GDKコマンドgdk psql を使用してPostgreSQLコンソールを開き、コマンド\d <index_name> を実行して、新しく作成したインデックスが存在することを確認してください。

インデックスの非同期削除

非常に大きなテーブルの場合、インデックスの破棄を管理するのは困難です。remove_concurrent_index は通常のトラフィックをブロックしない方法でインデックスを削除しますが、それでもインデックスの破棄が何時間も続くと問題が生じます。autovacuum のような必要なデータベースオペレーションは実行できず、GitLab.com のデプロイプロセスはインデックス破棄の終了を待つ間ブロックされます。

GitLab.comへの影響を抑えるには、週末の時間帯に非同期でインデックスを削除する以下のプロセスを使用します。一般的にトラフィックが少なく、デプロイも少ないため、インデックスの破棄はより低いリスクレベルで進めることができます。

  1. 削除するインデックスをスケジュールします。
  2. MR がデプロイされ、インデックスが本番環境に存在することを確認します。
  3. 同期的にインデックスを破棄するマイグレーションを追加します。

削除するインデックスのスケジュール

  1. デプロイ後のマイグレーションを含むマージリクエストを作成し、インデックスを非同期に破棄する準備をします。
  2. 後続のイシューを作成し、インデックスを同期的に破棄するマイグレーションを追加します。
  3. 非同期インデックス削除を準備するマージリクエストに、フォローアップ課題についてのコメントを追加します。

例えば、非同期インデックスヘルパーを使用してインデックスを破棄する場合：

# in db/post_migrate/

INDEX_NAME = 'index_ci_builds_on_some_column'

# TODO: Index to be destroyed synchronously in https://gitlab.com/gitlab-org/gitlab/-/issues/XXXXX
def up
  prepare_async_index_removal :ci_builds, :some_column, name: INDEX_NAME
end

def down
  unprepare_async_index :ci_builds, :some_column, name: INDEX_NAME
end

このマイグレーションはインデックス名と定義をpostgres_async_indexes テーブルに入力します。週末に実行されるプロセスはこのテーブルからインデックスを取り出し、削除しようとします。

マージリクエストを作成する前に、データベースインデックスの変更をローカルでテストする必要があります。テストの出力をマージリクエストの説明に含めてください。

MR がデプロイされ、インデックスが本番環境に存在しなくなったことを確認します。

  1. ChatOpsを使用して、デプロイ後のマイグレーションがGitLab.comで実行されたことを/chatops run auto_deploy status <merge_sha> 。出力がdb/gprd を返す場合、デプロイ後のマイグレーションは本番データベースで実行されています。詳細については、デプロイ後のマイグレーションがGitLab.comで実行されたかどうかを判断する方法を参照してください。
  2. インデックスが非同期で削除された場合は、週末に削除できるように次の週まで待ちましょう。
  3. Database Lab を使用して、削除が成功したかどうかを確認します。DatabaseLabは、削除されたインデックスを見つけようとしてエラーをレポーターするはずです。そうでない場合、インデックスはまだ存在している可能性があります。

同期的にインデックスを破棄するマイグレーションを追加します。

本番データベースにインデックスが存在しないことを確認したら、同期的にインデックスを削除する2番目のマージリクエストを作成します。この2番目のマージリクエストでは、スキーマの変更を更新してstructure.sql にコミットする必要があります。同期マイグレーションはGitLab.com上ではno-opになりますが、他のインストールでは期待通りにマイグレーションを追加する必要があります。たとえば、先ほどの非同期の例で二回目のマイグレーションを作成する場合です：

警告: remove_concurrent_index_by_name で2つ目のマイグレーションをマージする前に、本番環境にインデックスがもう存在しないことを確認してください。インデックスが破棄される前に2つ目のマイグレーションがデプロイされた場合、インデックスは2つ目のマイグレーションが実行されたときに同期的に破棄されます。

# in db/post_migrate/

INDEX_NAME = 'index_ci_builds_on_some_column'

disable_ddl_transaction!

def up
  remove_concurrent_index_by_name :ci_builds, name: INDEX_NAME
end

def down
  add_concurrent_index :ci_builds, :some_column, name: INDEX_NAME
end

非同期に削除されたインデックスの検証

インデックスを削除するための変更をテストするには、ローカル環境で非同期インデックスヘルパーを使用します：

  1. RailsコンソールでFeature.enable(:database_reindexing) 、機能フラグを有効にします。
  2. bundle exec rails db:migrate を実行すると、postgres_async_indexes テーブルにエントリが作成されるはずです。
  3. bundle exec rails gitlab:db:reindex を実行し、インデックスを非同期に破棄します。
  4. インデックスを確認するには、GDKコマンドgdk psql を使用してPostgreSQLコンソールを開き、\d <index_name> を実行して、破棄されたインデックスがもはや存在しないことを確認してください。