kakudooo docs

PostgreSQL で Index を貼る時の作法

PostgreSQL を使っている運用中のサービスで、SQL のパフォーマンスチューニングをしています。その際にチューニングの一環でテーブルに Index を作成しました。 いくつか考慮したことがあったので、整理しておくことにします。

例については、通常の B-Tree index を操作する場合を想定します。

書き込みのあるテーブルには CONCURRENTLY オプションを使用する

対象のテーブルが以下のような場合

• 高頻度で書き込みが行われる
• 書き込みのタイミングを運用側で制御できない

Index 操作に対して CONCURRENTRY オプションを設定することが推奨されます。

PostgreSQL では Index の作成時にテーブルへの書き込みに対してロックが取られます(他のトランザクションでの読み取りは可能)。そのため、テーブルに対する挿入、更新、削除の同時実行を防止するロックを取らずにインデックスを作成する CONCURRENTRY オプションが用意されています。 仕組みについては、こちらを参照。

strong_migraions gem でもテーブルへの index 作成時には CONCURRENTRY オプションを使用することが推奨されています。

※いずれもトランザクションブロック(BEGIN; ... COMMIT;)外で実行すること

index の作成

CREATE INDEX CONCURRENTLY title_idx ON books (title);

index の削除

DROP INDEX CONCURRENTLY title_idx;

参考: https://www.postgresql.org/docs/current/sql-dropindex.html

ただし、CONCURRENTRY オプションには以下のような注意点もあります。

• テーブルのスキャンを 2 回実行する必要がある、またインデックスの変更や使用する可能性のある既存のトランザクションがすべて終了するまで待機する必要がある
  • → 通常の INDEX 操作に比べて CPU 負荷や I/O 負荷がかかる
• テーブルのスキャン中にデッドロックや unique index の unique violation が発生した場合に CREATE INDEX 自体は失敗するが、そこまでに作成された INDEX が残ってしまう
  • → クエリの実行時には不完全な index は無視されるが、更新時に余計な index があるとオーバーヘッドになってしまう

参考: https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY

そのため、CONCURRENTRY オプションとうまく付き合う必要があります。 以下にその観点を挙げておきます。

CONCURRENTRY オプション使用有無の判断

対象テーブルの特性を確認します。

• 高頻度で書き込みが行われる
• 書き込みのタイミングを運用側で制御できない

上記の場合は CONCURRENTRY オプションを使用します。

逆に、マスタデータを扱うテーブルや書き込みが運用側で制御できるようなテーブルは CONCURRENTRY オプションを使用しないことで、上記の注意点について考慮する必要がなくなります。

失敗時の index の再構成

INDEX の作成に失敗した場合は、REINDEX INDEX を実行して無効な index を削除します。

REINDEX INDEX CONCURRENTLY title_idx;

参考: https://www.postgresql.org/docs/current/sql-reindex.html

ActiveRecord の Migration ファイルではどのように定義するか？

ActiveRecord の Migration ファイルで index 操作を行うサンプルです。

CONCURRENTRY オプションあり

index の作成

class AddTitleIndexToBooks < ActiveRecord::Migration[8.0]
  disable_ddl_transaction!

  def change
    add_index :books, :title, algorithm: :concurrently
  end
end

index の削除

class RemoveTitleIndexFromBooks < ActiveRecord::Migration[8.0]
  disable_ddl_transaction!

  def change
    remove_index :books, :title, algorithm: :concurrently
  end
end

CONCURRENTRY オプションなし

index の作成

class AddTitleIndexToBooks < ActiveRecord::Migration[8.0]
  def change
    add_index :books, :title
  end
end

index の削除

class RemoveTitleIndexFromBooks < ActiveRecord::Migration[8.0]
  def change
    remove_index :books, :title
  end
end