Rails + PostgreSQL の CREATE INDEX CONCURRENTLY を使ってみた

この記事はユニファAdvent Calendar 2023の13日目の記事です。

こんにちは。プロダクトエンジニアリング部の横山です。今年5月にユニファに入社してからあっという間に半年経ち、ブログ初投稿になります！

今回は、以前テーブルにインデックスを追加するにあたり、postgreSQL の CREATE INDEX CONCURRENTLY を使ってみたので、そのことについて書いていきます。

CREATE INDEX CONCURRENTLY とは？

詳しくはこちら：PostgresSQL ドキュメントCREATE INDEX

通常インデックス作成時は、処理が開始〜終了するまで対象テーブルへの書き込み（挿入、更新、削除）がロックされます。データ量の多いテーブル、特にユーザから頻繁に書き込みがされるテーブルでは、ロックされてしまうと思わぬエラーに繋がる可能性もあるため、ロックされることはなるべく控えたいですよね。

そこで、PostgreSQL の CREATE INDEX では CONCURRENTLY というオプションを使うとロックされずにインデックスを作成することができます。

メリット

テーブルがロックされずにインデックスの作成が可能
Rails のマイグレーションファイルにオプション指定するだけなので実行が簡単

デメリット

通常よりも処理に時間がかかる（+ 負荷が上がる）
テーブルスキャン中に問題が発生すると「無効な」インデックスが残ってしまう

実行方法

上記のメリットで上げているように実行方法はとても簡単で、マイグレーションファイルに algorithm: :concurrently オプションを指定するだけです。
（詳しくはこちら：ActiveRecord::ConnectionAdapters::SchemaStatements - Ruby on Rails API）

例えば、users テーブルの user_id にインデックスを追加する場合は以下です。

class AddIndexUserIdToUsers < ActiveRecord::Migration
  disable_ddl_transaction!

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

※ disable_ddl_transaction! も必須
ActiveRecord::ConnectionAdapters::SchemaStatements - Ruby on Rails API でConcurrently adding an index is not supported in a transaction.と記載があるようにトランザクション下ではサポートされていないため、それを無効にするものです。

検証

本番環境での実行前の確認として、デメリットである「通常よりも処理に時間がかかる」という点、つまり CONCURRENTLY を使用した場合、どれくらい処理に時間がかかるのか？を検証していきます。

検証では、PostgreSQL 上で CREATE INDEX コマンドを使用し、 CONCURRENTLY オプションを「使用しない場合」と「使用した場合」の実行時間を比較してみました。

条件

レコード数：本番環境でインデックスを追加したいおよそ6000万件
users テーブルの user_id にインデックスを追加する

実行結果は以下です。

SELECT count(users.*) FROM users;
  count   
----------
 60000000
(1 row)

CONCURRENTLY を「使用しない場合」

CREATE INDEX "index_users_on_user_id" ON users(user_id);
CREATE INDEX
Time: 25242.315 ms (00:25.242)

CONCURRENTLY を「使用した場合」

CREATE INDEX CONCURRENTLY "index_users_on_user_id" ON users(user_id);
CREATE INDEX
Time: 40542.969 ms (00:40.543)

結果としては、

使用しない場合：およそ 25s
使用した場合：およそ 40s

6000万件のデータに対しては、 CONCURRENTLY を使用した場合の処理時間は、使用しない場合に比べて + およそ 15s でした。

この結果から、CONCURRENTLY を使用したとしても処理時間が10倍や100倍になるわけではなく、今回は本番環境に対しての実行時間としても許容範囲ということで、本番環境でのインデックス追加にもCONCURRENTLY オプションを使用しました！

無効なインデックスについて

問題なく処理が完了すれば良いのですが、もう一つのデメリットである

テーブルスキャン中に問題が発生すると「無効な」インデックスが残ってしまうこと

とは、どのような状態でしょうか？

こちらが発生する条件として以下を設定しました。

条件

重複データのあるカラムにユニークインデックスを追加する
users テーブルの name （データに重複あり）にユニークインデックスを追加

実行結果は以下です。

PostgreSQL 上で実行した場合

CREATE UNIQUE INDEX CONCURRENTLY ON users(name);
ERROR:  could not create unique index "users_name_idx"
DETAIL:  Key (name)=(sample_name) is duplicated.
CONTEXT:  parallel worker

このようにエラーメッセージが返ってきたため作成したインデックスを確認してみると、

\d "users_name_idx"
       Index "public.users_name_idx"
 Column |          Type          | Key? | Definition 
--------+------------------------+------+------------
name | character varying(255) | yes  | name
unique, btree, for table "public.users", invalid

invalid （無効）として報告されるインデックスがテーブル上には残っていました。

rails db:migrate で実行した場合

-- add_index(:users, :name, {:unique=>true, :algorithm=>:concurrently})
rails aborted!
StandardError: An error has occurred, all later migrations canceled:

PG::UniqueViolation: ERROR:  could not create unique index "index_users_on_name"
DETAIL:  Key (name)=(sample_name) is duplicated.
/usr/src/app/db/migrate/20231200000000_add_test_index.rb:6:in `change'

Caused by:
ActiveRecord::RecordNotUnique: PG::UniqueViolation: ERROR:  could not create unique index "index_users_on_name"
DETAIL:  Key (name=(sample_name) is duplicated.
/usr/src/app/db/migrate/20231200000000_add_test_index.rb:6:in `change'

Caused by:
PG::UniqueViolation: ERROR:  could not create unique index "index_users_on_name"
DETAIL:  Key (name)=(sample_name) is duplicated.
/usr/src/app/db/migrate/20231200000000_add_test_index.rb:6:in `change'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)
root@24c8036f87c9:/usr/src/app#

途中でマイグレーションがエラーとなりました。作成したインデックスの存在確認をしてみると

[1] pry(main)> User.connection.index_exists?(:users, :name)
=> true

インデックス自体は存在確認できたものの、マイグレーション自体は失敗したものとなります。重複データを削除した後、マイグレーションを再度実行してみましたが、以下のようなエラーが返ってきました。

-- add_index(:users, :name, {:unique=>true, :algorithm=>:concurrently})
rails aborted!
StandardError: An error has occurred, all later migrations canceled:

PG::DuplicateTable: ERROR:  relation "index_users_on_name" already exists
/usr/src/app/db/migrate/20231200000000_add_test_index.rb:6:in `change'

結果として、もし処理中にエラーが発生した場合には、上記のように無効なインデックスが残ってしまいます。また、これらを解消するには、無効なインデックスを削除し、再度インデックスを作成する必要があるかと思われます。

まとめ

もちろん、インデックスの数、テーブルの構造やデータ量によって処理時間や負荷は変わるため、メリットデメリットを考慮し使用は検討すべきなので、また今後インデックス追加する際には、処理時間や負荷など改めて検証したいと思いました。

以上になります、ここまでお読みいただきありがとうございました。

ユニファでは、一緒に働いてくれる仲間を募集しています！
詳細は、以下募集要項からご確認ください。

unifa-e.com

ユニファ開発者ブログ

ユニファ株式会社プロダクトデベロップメント本部メンバーによるブログです。

Rails + PostgreSQL の CREATE INDEX CONCURRENTLY を使ってみた

CREATE INDEX CONCURRENTLY とは？

実行方法

検証

無効なインデックスについて

まとめ