[PostgreSQL+Laravel]パーティションテーブルにCREATE INDEX CONCURRENTLYしたい

はじめに

PostgreSQLを利用しているサービスにおいて、インデックスを作成する場合はCONCURRENTLYオプションを指定して、書き込みのロックを回避する方法は良く使われているかと思いますが、パーティションテーブルはCONCURRENTLYをサポートおらず、どうしたものかと少しハマったので解決方法をメモします。

解決方法

https://www.postgresql.jp/document/16/html/sql-createindex.html#:~:text=今の所パーティションテーブルのインデックスの同時作成はサポートされていません。 しかし、パーティションテーブルへの書き込みをロックしている時間を短くするために、各パーティション上のインデックスを個別に同時作成してから最後にパーティションインデックスを非同時的に作成することはできます。 この場合、パーティションインデックスの作成はメタデータのみの操作になります。

各パーティションに対して個別にCONCURRENTLYオプションを指定してインデックスを作成した後で、元のテーブルに対してCONCURRENTLYオプション無しでインデックスを作成すれば良いらしい。
厳密には元のテーブルに対してインデックスを作成したタイミングでロックがかかるが、メタデータの更新のみなのですぐに終わるそう。

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration {

    public $withinTransaction = false;

    /**
     * Run the migrations.
     */
    public function up(): void
    {
        // ロック回避のため、先に各パーティションに対してインデックスを作成する
        $partitions = ['sample_table_1', 'sample_table_2', 'sample_table_3'];
        foreach ($partitions as $partition) {
            DB::statement("CREATE INDEX CONCURRENTLY {$partition}_col_idx ON {$partition}(col);");
        }

        // 元となるテーブルにインデックスを作成する
        DB::statement('CREATE INDEX sample_table_col_idx ON sample_table(col);');
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::table('sample_table', function (Blueprint $table) {
            $table->dropIndex('sample_table_col_idx');
        });
    }
};

まとめ

厳密には少しの間だけロックはかかりますが、普通にインデックスを作成する時よりもロック時間を大幅に減らすことが出来そうです。
ただCONCURRENTLYオプションを使うとテーブルを2回スキャンするので、その分普通のインデックス作成よりも時間がかかるというデメリットもあります。
メリット/デメリットを考慮の上検討してみてください。