PostgreSQLのレコード数が多いテーブルへRailsで外部キー制約のあるマイグレーション実行する際の注意点

背景

レコード数の多いテーブルにRailsでマイグレーションを実行する場合は、何かと気を付けることが多いです。例えば、NULLを許可しないカラムを追加して、デフォルト値を設定したと言う場合、デフォルト値の設定に時間がかかるので、カラム追加した後値を設定し、NOT NULL制約をつけるといった形でマイグレーションを複数かに分けて実施すると言う方法をとることがあります。

今回、参照制約（外部キー制約、FOREIGN KEY）のあるカラムを５億レコードほどあるテーブルに追加しようとしたところ、いくつかの示唆を得られたので、備忘録も兼ねて記事にしたいと思います。¹

なお、RailsとPostgreSQLのバージョンは以下になります。

Rails: 8.0.2
PostgreSQL: 16

外部キー制約のあるカラムを追加するadd_reference

既存のテーブルに他テーブルを参照するカラムを追加する際、Railsでは、add_referenceを用いて実装します。add_referenceというのは内部では、以下の３つの処理を行っています。

1. カラム追加
2. 追加したカラムにインデックスをはる
3. 追加したカラムに外部キー制約をはる

説明のため、以下の様な伝票(slips)および伝票明細(slip_details)テーブルで考えます。伝票および伝票明細は、それぞれ親子関係を持つことができ、parent_slip_idよびparent_slip_detail_idカラムで参照することとします。

まず、何も考えずマイグレーションファイルを実装するとこの様な形になると思います。自己参照になっているので foreign_keyのところが少し複雑な形をしていますが、slip_idカラムに参照制約のあるparent_slip_id カラムを追加しているだけです（parent_slip_detailも同様です）

class AddColumnsToSlipAndSlipDetail < ActiveRecord::Migration[8.0]
  def change
    add_reference :slips, :parent_slip, 
                  foreign_key: { to_table: :slips }, null: true
    add_reference :slip_details, :parent_slip_detail,
                  foreign_key: { to_table: :slip_details }, null: true
  end
end

このマイグレーション実行するとRailsは、以下の6つのDDLを発行します。

 1: ALTER TABLE "slips" ADD "parent_slip_id" bigint 
 2: CREATE INDEX "index_slips_on_parent_slip_id" ON "slips" ("parent_slip_id")
 3: ALTER TABLE "slips" ADD CONSTRAINT "fk_rails_05t00152v6" FOREIGN KEY ("parent_slip_id") REFERENCES "slip_s" ("slip_id")
 
 4: ALTER TABLE "slip_details" ADD "parent_slip_detail_id" bigint 
 5: CREATE INDEX "index_slip_details_on_parent_slip_detail_id" ON "slip_details" ("parent_slip_detail_id")
 6: ALTER TABLE "slip_details" ADD CONSTRAINT "fk_rails_02c00675a9" FOREIGN KEY ("parent_slip_detail_id") REFERENCES "slip_details" ("slip_detail_id")

また、Railsは、DDLトランザクションをはったうえで、これらの処理を行います。

PostgreSQLでは、ALTER TABLEが実行される場合、多くの場合ACCESS EXCLUSIVEロックという占有ロックを獲得して他セッションによるテーブル参照もブロックします。

つまり、トランザクションがはられた上で、1のslipsテーブルに対するACCESS EXCLUSIVEロックがはられ、2、3の処理を行った後、4、5の処理に移ります。4では、slip_detailsテーブルに対してACCESS EXCLUSIVEロックがはられます。

ここで、slip_detailsテーブルのレコード数が多く、5のslip_detailsテーブルにインデックスをはる処理に30分ほど時間がかかってしまいました。この間はトランザクションがはられており、ACCESS EXCLUSIVEロックがはられているので、slipsテーブルやslip_detailsテーブルに対する他のセッションによる参照がブロックされます。当然更新もできません。ちなみに、CREATE INDEXでも対象テーブルへの書き込み（挿入、更新、削除）がロックされます。

30分も参照ができないとなるとサービス提供上大きな支障が出ます。今回は開発環境で、幸いなことに誰も触っていない時間帯だったため、大きな問題にはなりませんでしたが、これが本番で行ったとするとゾッとします。

インデックスをはるのに時間がかかるのならば、インデックスをはるのは別で行えばいいのではないかと思われます。Railsのadd_referenceには、インデックスをはらないオプションを指定することができます。また、インデックスを貼る際には、CONCURRENTLYオプションをつけることができ、この場合は、書き込みのロックをかけずにインデックスをはることができます（時間がかかるなどのデメリットはあります）。CONCURRENTLYオプションはトランザクション内で使うことができないため、disable_ddl_transaction!を宣言してDDLトランザクションをはらないようにします。

class AddColumnsToSlipAndSlipDetail < ActiveRecord::Migration[8.0]
  disable_ddl_transaction!
  
  def change
    add_reference :slips, :parent_slip, 
                  foreign_key: { to_table: :slips }, null: true, index: false
    add_reference :slip_details, :parent_slip_detail,
                  foreign_key: { to_table: :slip_details }, null: true
    add_index :slips, :parent_slip_id, algorithm: :concurrently
    add_index :slip_details, :parent_slip_detail_id, algorithm: :concurrently
  end
end

しかし、この方法では、引き続き、マイグレーションに時間がかかってしまいます。なぜならば、index: falseオプションつきのadd_referenceを実行すると、前述のように、カラム追加と外部キー制約の追加が行われ、外部キー制約をつける際に時間がかかってしまうからです。index: trueのadd_referenceの処理を観察すると外部キー制約を貼る前にインデックスを貼っている理由はここにあります。外部キー制約をつける際にもインデックスがある場合は、インデックスを利用してくれるので、処理が早くなるのです。

add_referenceを分割して実施し、CONCURRENTLYでインデックスを貼る

上記の問題を解決するためには、トランザクションを貼らずにカラムを追加して、そのカラムに外部キー制約を貼りたい場合は、add_referenceを利用せずに、カラム追加(add_column)、インデックス生成(add_index)、外部キー制約付与(add_foreign_key)を別々に行う方が良いでしょう。

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

  def change
    add_column :slips, :parent_slip_id
    add_index :slips, :parent_slip_id, algorithm: :concurrently
    add_foreign_key :slips, :slips, column: :parent_slip_id, primary_key: :slip_id

    add_column :slip_details, :parent_slip_detail_id
    add_index :slip_details, :parent_slip_detail_id, algorithm: :concurrently
    add_foreign_key :slip_details, :slip_details, column: :parent_slip_detail_id, primary_key: :slip_detail_id
  end
end

なお、マイグレーションの実行をデプロイの中で実施している場合は、数十分もマイグレーションに時間がかかってしまうのは、デプロイとして健康的ではありません。その場合、デプロイを実施する前にマイグレーションを行っておくことと良いでしょう。マイグレーションファイルをサーバー上に手動で配置し、db:migrateコマンドを実行するなどします（やり方は、各々の環境に合わせて行ってください）。

1. このマイグレーションは、開発環境で実施したものです。クラウド上にある開発環境（ローカルで開発するための環境ではなく、PdMやQAなど非開発者に動作を確認してもらう環境）には以前負荷試験のために大量のレコードを保存しておいたため、本番適用する前に把握することができました。 ↩