Supabaseのクエリが遅い原因と解決策!パフォーマンスを劇的に改善するインデックスの適切な設計と設定手順

Supabaseのロゴを背景に、時計とインデックスのツリー構造が重なり、パフォーマンスメーターが上昇しているアイキャッチ画像 データベース

Supabase、特にPostgreSQLをバックエンドに持つBaaSとしての利便性は高い反面、クエリ応答速度に頭を悩ませた経験はないだろうか。
「単純なSELECTなのに数秒かかる」「RLS(行レベルセキュリティ)を入れた途端に激遅になった」――これらはほぼ確実に、インデックス設計の過誤か、統計情報の更新不足が原因だ。

では、なぜインデックスが効かないのか。
PostgreSQLのクエリプランナーは、テーブルのスキャン方法を「シーケンシャルスキャン」と「インデックススキャン」のコスト見積もりで決定する。
ところが、RLSや外部キー制約が絡むと、内部的に複数のシステムカタログや隠し列(例:xmin)が参照され、プランナーが想定外の実行計画を選択するケースが頻発する。
特にSupabaseは初期状態でpg_stat_statementsが有効だが、自動バキュームのデフォルト設定は汎用的すぎる。
このため、更新頻度の高いテーブルでは、統計情報が古くなり、インデックスが存在しても無視されるという皮肉な事態に陥る。

解決の第一歩は、実行計画の取得だ。
EXPLAIN (ANALYZE, BUFFERS)で、実際のコストとバッファヒット率を確認する。
次に、以下の観点でインデックスを再設計する。

  • 複合インデックスを作成する際は、等価条件(=)のカラムを先頭に、範囲条件(BETWEENや>)を後方に配置する。これがB-Treeインデックスの基本原則だ
  • RLSで頻繁に参照されるuser_idやtenant_idは、必ず単独または先頭に含む複合インデックスを張る。RLSのフィルタ条件はクエリ本文に明示されないため、見落としがちな重大なボトルネックである
  • 部分インデックス(WHERE句付き)を活用し、アクセスパターンが限定されているクエリに対してインデックスサイズを削減する。例えば、deleted_at IS NULLのような条件が頻出するなら、この条件をインデックスに組み込むことで、スキャン対象を劇的に絞り込める

設定面では、random_page_costとeffective_cache_sizeを、Supabaseのインスタンススペック(CPUコア数やメモリ)に合わせて調整する。
デフォルトのrandom_page_cost=4は機械式HDDを想定しており、現在のクラウドSSD環境では過大評価されがちだ。
この値を1.1程度に引き下げるだけで、プランナーがインデックススキャンを選択しやすくなる。

また、インデックスだけでは改善しきれない場合は、カバリングインデックス(INCLUDE句)を検討する。
これにより、インデックスだけで全てのカラムをまかなえれば、テーブルへのアクセス自体が不要となり、応答時間は桁違いに短縮される。
例えば、CREATE INDEX ON orders (user_id) INCLUDE (total_amount, created_at); のように指定する。

最後に、週次または月次でVACUUM ANALYZEを実行するジョブを組み込むこと。
Supabaseのダッシュボードから手動実行も可能だが、確実に自動化するならpg_cronの利用が推奨される。
これらの手法を組み合わせれば、多くのケースでクエリ時間を1/10以下に抑えられる。
次章では、実際のRLS込みのクエリを例に、具体的なインデックス定義と設定値のチューニング手順を段階的に解説する。

  1. Supabaseのクエリ遅延、その真因とは?
  2. 実行計画を読む:EXPLAINでボトルネックを可視化する
    1. 基礎:ANALYZEとBUFFERSの併用が必須
    2. 実践:BUFFERSからI/Oボトルネックを特定する
    3. よくある誤読パターンとその対策
    4. 可視化ツールの活用と実運用でのプラクティス
  3. インデックス設計の黄金律:B-Treeと複合キーの順序
    1. B-Treeの走査原理から順序を理解する
    2. 逆順が招く悲劇:範囲条件を先に置いた場合
    3. 選択性(カーディナリティ)とのトレードオフ
    4. インデックスサイズとスキャン効率のバランス
    5. ユニーク制約とインデックスの関係
    6. 実践的な検証手順
  4. RLS(行レベルセキュリティ)がインデックスを無効にするメカニズム
    1. RLSがクエリに注入する「見えないWHERE句」
    2. ポリシーの複合条件がインデックス選択性を崩す
    3. セキュリティコンテキストとパフォーマンスのトレードオフ
    4. 実証:RLS有効時と無効時の実行計画比較
    5. 対策:RLSを考慮したインデックス設計戦略
  5. 部分インデックスとカバリングインデックスで劇的な高速化を実現
    1. 部分インデックス:条件付きでインデックスサイズを劇的削減
    2. カバリングインデックス:テーブルアクセスをゼロにする
    3. 部分インデックスとカバリングインデックスの使い分け基準
    4. 実践上の注意点:メンテナンスコストと重複インデックス
    5. 効果検証のための実測手順
  6. 統計情報と自動バキューム:デフォルト設定の落とし穴
    1. 統計情報がクエリプランに与える影響の実態
    2. 自動バキュームのデフォルト設定が抱える3つの問題点
    3. 実データで確認するデフォルト設定の限界
    4. 対策:テーブル単位で閾値を再定義する
    5. モニタリングと定期メンテナンスの実践
  7. クラウド環境固有のチューニング:random_page_costとeffective_cache_size
    1. random_page_cost:SSD時代に合わせた再定義が必須
    2. effective_cache_size:OSとPostgreSQLのキャッシュ総量を正しく伝える
    3. 両パラメータの相互作用と検証手順
    4. 他のコストパラメータとの連携
    5. Supabase特有の制約と注意点
  8. 実践!遅延クエリを1/10にする具体的なインデックス定義と設定手順
    1. フェーズ1:現状の実行計画と統計情報の取得
    2. フェーズ2:統計情報とバキュームの緊急更新
    3. フェーズ3:インデックス定義の設計と作成
    4. フェーズ4:コストパラメータの調整
    5. フェーズ5:RLSポリシーとインデックスの整合性確認
    6. フェーズ6:自動バキューム閾値の最適化
    7. 最終検証と結果の記録
    8. 運用開始後のモニタリングポイント
  9. 定期メンテナンスの自動化:pg_cronでVACUUM ANALYZEを仕込む
    1. pg_cronの有効化と権限設定
    2. 基本ジョブの登録:毎日深夜にVACUUM ANALYZEを実行
    3. テーブル単位のジョブ設計とオフセット分散
    4. ジョブの監視とログ管理
    5. 緊急時対応:手動実行とジョブの一時停止
    6. パフォーマンスに配慮したVACUUMオプションの活用
    7. 運用開始後のチューニングサイクル
  10. まとめ:インデックスは魔法ではなく、統計と計画の科学である

Supabaseのクエリ遅延、その真因とは?

Supabaseのダッシュボード上でクエリ実行時間が表示され、その長いバーと時計のアイコンが強調された画面

Supabaseでクエリが遅い――この訴えは、開発者フォーラムで毎日のように見かけます。
しかし、その原因を「Supabaseが遅い」と短絡する前に、PostgreSQLという堅牢なRDBMSの上で何が起きているのかを、実行計画レベルで分解する必要があります。
結論から言えば、遅延の80%以上はインデックス不在または不適切なインデックス設計に起因します。
残りの20%は、統計情報の陈旧化、RLSポリシーのオーバーヘッド、そしてクラウド環境特有のコストパラメータミスマッチです。

まず、Supabaseのクエリフローを整理しましょう。
アプリケーションから発行されたSQLは、PostgreSQLのパーサー→リライター→プランナー→エグゼキュータを経由します。
このうちプランナーが、テーブルスキャン手法と結合順序を決定する際に参照するのが、テーブルの統計情報(pg_statistic)とハードウェアコスト定数(seq_page_costやrandom_page_cost)です。
Supabaseのデフォルト設定は汎用性を重視しているため、ワークロードが偏ったアプリケーションでは、プランナーが誤った判断を下すことが頻発します。

具体的に見ていきましょう。
最も典型的な遅延パターンは、フィルタ条件にインデックスが未定義のカラムを使用するケースです。
例えば、ordersテーブルに対してWHERE user_id = 123 AND status = 'active'を実行する場合、user_idとstatusの複合インデックスがなければ、フルテーブルスキャンが発生します。
このときの実行計画をEXPLAINで確認すると、Seq Scan on ordersと表示され、コストがテーブル行数に比例して増加します。
行数が10万を超えれば、応答時間は数百ミリ秒から数秒へと劣化します。

次に、見落とされがちなのがRLS(行レベルセキュリティ)が内部的に生成する暗黙のフィルタ条件です。
Supabaseは認証統合のため、クエリ発行ユーザーのauth.uid()を利用したRLSポリシーを自動適用します。
このポリシーがuser_id = auth.uid()といった条件を含む場合、そのカラムにインデックスが張られていないと、ユーザーごとにフルスキャンが走るという最悪のシナリオが発生します。
しかもRLSの条件はアプリケーションログに明示されないため、多くの開発者が気づかずにパフォーマンス劣化を受け入れています。

また、データ分布の偏りも重大な要因です。
例えば、statusカラムが「active」と「inactive」の2値で、全体の95%が「active」だったとします。
この場合、status = 'inactive'を検索するクエリはインデックスが有効に働きますが、status = 'active'ではインデックススキャンよりシーケンシャルスキャンの方が高速とプランナーが判断し、インデックスを無視します。
このようなケースでは、部分インデックス(WHERE status = 'inactive')を設計することで、無駄なインデックスサイズを削減しつつ、特定クエリに最適化できます。

さらに、統計情報の更新頻度も無視できません。
PostgreSQLの自動バキュームはデフォルトでautovacuum_vacuum_scale_factor = 0.2、つまりテーブルサイズの20%が更新されるとトリガーされます。
しかし、頻繁に更新されるSaaSアプリケーションでは、この閾値が高すぎて統計が古くなり、プランナーが実際の行数とかけ離れた見積もりを行うことがあります。
その結果、本来インデックススキャンが有効なクエリでも、ネストループ結合を避けてハッシュ結合を選択するなど、非効率な計画が選ばれます。

クラウド環境特有の要因としては、ストレージI/Oのレイテンシ変動と共有バッファのサイズ不足が挙げられます。
SupabaseはAWSやGCP上の高速SSDを利用しますが、デフォルトのrandom_page_cost = 4は機械式HDDを想定した値であり、現代のSSDでは過大評価です。
この値を1.1程度に引き下げるだけで、プランナーがインデックススキャンを積極採用するようになります。
また、effective_cache_sizeがOSのファイルキャッシュや共有バッファを適切に反映していない場合、プランナーはディスクI/Oを過剰に見積もり、シーケンシャルスキャンを選びやすくなります。

ここで重要なのは、遅延は単一の原因ではなく、複数要因の複合効果であるという点です。
インデックスがあっても統計が古ければ無視されるし、統計が新しくてもコストパラメータが適切でなければインデックスは採用されません。
したがって、対策は「インデックスを張る」という一点突破ではなく、実行計画の取得→統計更新→パラメータ調整→インデックス再設計というループで進めるべきです。

最後に、Supabaseが提供するpg_stat_statementsビューを活用すれば、どのクエリが総実行時間の多くを占めているかを特定できます。
このビューはデフォルトで有効化されており、ORDER BY total_time DESCでワーストクエリを抽出可能です。
このデータを基に、まずは上位3件のクエリに絞ってインデックス設計を実施するのが、費用対効果の高いアプローチです。

以上の理解があれば、次章以降で示す具体的なインデックス定義やチューニング手順が、単なる「おまじない」ではなく、論理的な根拠に基づいた施策として腑に落ちるはずです。

実行計画を読む:EXPLAINでボトルネックを可視化する

PostgreSQLのEXPLAIN ANALYZE出力結果がターミナルに表示され、シーケンシャルスキャンとインデックススキャンのコスト比較がハイライトされている

クエリ遅延の原因を特定するための第一歩、それは実行計画の取得と解釈です。
PostgreSQLはEXPLAINコマンドを通じて、プランナーが選択したスキャン手法、結合順序、そして各ステップの見積コストと実際のコストを詳細に開示します。
この出力を読めない限り、インデックス設計もパラメータチューニングも単なる推測の域を出ません。
まずは、生のEXPLAIN出力をどのように読み解くかを体系的に解説します。

基礎:ANALYZEとBUFFERSの併用が必須

実行計画を取得する際、必ずEXPLAIN (ANALYZE, BUFFERS, VERBOSE)を指定してください。
ANALYZEは実際にクエリを実行して実測時間を表示し、BUFFERSは共有バッファやローカルバッファのヒット・ミス情報を提供します。
VERBOSEは出力カラムの詳細まで表示するため、RLSが追加した暗黙の条件を特定するのに役立ちます。

例を示します。
遅延が報告されているクエリが以下のようなものだとしましょう。

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM orders WHERE user_id = 12345 AND created_at > '2026-01-01';

この出力で最初に注目すべきは、トップレベルのノードタイプです。
Seq Scanと表示されればフルテーブルスキャン、Index ScanまたはBitmap Heap Scanと表示されればインデックスが利用されています。
Seq Scanが出現した時点で、インデックス不在またはプランナーがインデックスを不適切と判断した証拠です。

次に、各ノードに付随するコスト数値を読みます。
cost=0.00..5234.12 rows=150 width=42という表記は、見積開始コスト0、見積総コスト5234.12、見積行数150、行幅42バイトを意味します。
実際のactual time=12.345..456.789 rows=142 loops=1と比較し、見積行数と実行数に大きな乖離があれば、統計情報が古いか、またはデータ分布が偏っているサインです。

実践:BUFFERSからI/Oボトルネックを特定する

BUFFERSセクションでは、shared hits=1234(キャッシュヒット)、shared read=567(ディスク読み取り)という数値が表示されます。
readの割合が高いほど、ストレージI/Oが律速となっていることを示します。
ここで注目したいのは、インデックススキャンを行っているのにreadが多いケースです。
これは、インデックス自体はメモリに乗っているが、テーブルの実データ(ヒープ)がキャッシュから追い出されている可能性が高い。
この場合、カバリングインデックス(INCLUDE句)でテーブルアクセス自体を排除するのが有効です。

また、loopsの値も見逃せません。
ネステッドループ結合では、外部テーブルの行数だけ内部テーブルのスキャンが繰り返されます。
loops=1500などと表示される場合、内部テーブルのインデックスが効率的に使われているか再確認してください。

よくある誤読パターンとその対策

初心者が最も陥りやすい誤解は、「コストが低いほど良い」という思い込みです。
実は、コストは相対的な見積もりであり、絶対的な応答時間を示しません。
例えば、cost=0.00..1.25と出ても、実際の実行時間が500ミリ秒かかることは珍しくありません。
これはプランナーがrandom_page_costやcpu_operator_costを過小評価しているからです。
したがって、必ずactual timeとbuffersを併せて評価してください。

もう一つの落とし穴は、Filter条件の存在です。
インデックススキャンを行っていても、ノード内にFilter: (status = 'active')と表示される場合、インデックスで絞り込んだ後にさらに条件を適用していることになります。
これは、複合インデックスにstatusが含まれていないか、またはインデックスが部分的にしか利用されていない証拠です。
理想は、Index Condに全てのフィルタ条件が含まれること。
これが実現できれば、テーブルアクセスが大幅に削減されます。

可視化ツールの活用と実運用でのプラクティス

ターミナル出力が読みにくい場合は、EXPLAINの出力をJSON形式(FORMAT JSON)で取得し、https://explain.dalibo.com/のような可視化サービスに入力するのがおすすめです。
このツールはノードのコストをツリー構造で視覚化し、最もコストの高い部分を色分けして示してくれます。

実運用では、以下のようなチェックリストを実行計画ごとに回すと良いでしょう。

  • Seq Scanが発生しているカラムにインデックスが存在するか
  • インデックスが存在するのにSeq Scanが選ばれている場合、統計情報は最新か(pg_stat_user_tablesのlast_analyzeを確認)
  • Bitmap Heap ScanとBitmap Index Scanのペアが表示されている場合、インデックスサイズが大きくて一度に取得するタプル数が多い可能性。この場合は部分インデックスを検討
  • SortやGroupAggregateノードが現れた場合、ソートにインデックスが利用されていない証拠。ORDER BYに該当するカラムをインデックスに含める

以上の読み解きスキルを身につければ、クエリチューニングは「手探り」から「データ駆動」へと変わります。
次章では、この実行計画の知見を基に、インデックス設計の具体的なルールを、B-Treeの内部構造から紐解いていきます。

インデックス設計の黄金律:B-Treeと複合キーの順序

B-Treeインデックスの構造図と、複合インデックスにおけるカラム順序の優先度を示した模式図

実行計画の読み方が身についたところで、次はインデックスそのものの設計原理に踏み込みます。
PostgreSQLのデフォルトインデックス型はB-Treeです。
このB-Treeは、等価条件(=)と範囲条件(<、>、BETWEEN)の両方に使える汎用性の高いデータ構造ですが、その性能はキーカラムの順序に極めて敏感です。
この順序を間違えると、インデックスが存在してもプランナーが採用しなかったり、採用してもスキャン範囲が広がって効果が半減します。

B-Treeの走査原理から順序を理解する

B-Treeインデックスは、複合キーを辞書順(lexicographic order) でソートして保持します。
たとえば(a, b)という複合インデックスでは、まずaでソートし、同じaの値の中でbでソートされます。
この構造において、インデックスが効率的に使える条件は以下のとおりです。

  • 等価条件(=) は、そのカラムがインデックスの先頭から連続している限り、次のカラムの絞り込みにも利用可能
  • 範囲条件(>や<) は、そのカラムが一度出現すると、それより後ろのカラムは一切絞り込みに使われない

この事実が、複合インデックス設計における「等価条件を先に、範囲条件を後に」という黄金律の根拠です。
具体的な例で確認しましょう。

ordersテーブルに(user_id, created_at)という複合インデックスを張ったとします。
クエリWHERE user_id = 123 AND created_at > '2026-01-01'は、user_idで等価絞り込みを行った後、created_atで範囲スキャンが可能です。
これはインデックスの両方のカラムが活用される理想的なパターンです。

しかし、クエリがWHERE created_at > '2026-01-01' AND user_id = 123と書かれていても、プランナーは条件を入れ替えて同じ実行計画を選びます。
問題はインデックス定義の順序であって、クエリ内の記述順序ではありません。

逆順が招く悲劇:範囲条件を先に置いた場合

同じ(user_id, created_at)インデックスに対して、WHERE user_id > 100 AND created_at = '2026-01-01'というクエリを発行するとどうなるか。
user_idが範囲条件なので、インデックスはuser_id > 100の範囲をスキャンしますが、その範囲内のcreated_atはソートされていません。
そのため、インデックススキャン後にフィルタでcreated_atを絞り込む必要が生じ、実質的にインデックスの後半カラムが無効化されます。
この場合、(created_at, user_id)という順序でインデックスを張るべきでした。

選択性(カーディナリティ)とのトレードオフ

もう一つの重要な判断基準は、カラムの選択性(=値の多様性) です。
一般的には、選択性が高いカラムを先頭に配置することで、インデックススキャンで絞り込まれる行数を早期に減らせます。
ただし、この原則は等価条件が複数ある場合に限定されます。
なぜなら、範囲条件が含まれる場合は選択性よりも順序ルールが優先されるからです。

以下の表に、典型的なシナリオと推奨インデックス順序をまとめます。

クエリ条件の組み合わせ 推奨インデックス順序 理由
a = ? AND b = ? (a, b) または (b, a) 両方等価なので選択性の高い方を先頭に
a = ? AND b > ? (a, b) 等価のaを先頭、範囲のbを後に固定
a > ? AND b = ? (b, a) 等価のbを先頭にしないとbが無効化される
a = ? AND b = ? AND c > ? (a, b, c) 等価2つを先頭、範囲を最後に

インデックスサイズとスキャン効率のバランス

順序だけでなく、インデックスに含めるカラム数もパフォーマンスに直結します。
カラムが多すぎるとインデックスサイズが肥大化し、バッファプールを圧迫します。
また、更新(INSERT/UPDATE)時のオーバーヘッドも増加します。
私の経験則では、複合インデックスは最大で4〜5カラムまでに留め、それ以上になる場合は、部分インデックスやカバリングインデックスと組み合わせて設計を分割することを推奨します。

ユニーク制約とインデックスの関係

また、UNIQUE制約を付与すると、自動的にB-Treeインデックスが作成されます。
このインデックスも上記の順序ルールに従うため、UNIQUE (user_id, product_id)と定義した場合、product_id単独での検索にはほとんど役立ちません。
必要ならproduct_id単独のインデックスを別途作成してください。

実践的な検証手順

インデックス順序を変更した際は、必ずEXPLAINで実行計画が意図通りになったか確認します。
理想はIndex Only Scan が表示されることです。
これはテーブルの実データ(ヒープ)にアクセスせず、インデックスだけで結果を返せる状態を指します。
Index Scanと表示される場合は、ヒープアクセスが発生しているため、必要に応じてINCLUDE句を検討します(この詳細は次章で扱います)

最後に、インデックスはクエリ単位ではなく、ワークロード単位で設計するのが鉄則です。
すべてのクエリに個別のインデックスを張ると、メンテナンスコストが爆発します。
pg_stat_statementsで抽出した実行頻度の高い上位10クエリに絞り、それらをカバーする汎用的な複合インデックスを2〜3本設計するのが現実的です。
このアプローチなら、ストレージサイズも更新コストも許容範囲に収まります。

RLS(行レベルセキュリティ)がインデックスを無効にするメカニズム

SupabaseのRLSポリシー設定画面と、そのポリシーが内部的にクエリに追加されるフィルタ条件を可視化したフロー図

Supabaseの最大の特徴であり、同時にパフォーマンス・トラブルの主要因でもあるのがRLS(行レベルセキュリティ)です。
RLSはテーブル単位でポリシーを定義し、ユーザーごとに参照・更新可能な行を制御する強力な機能ですが、その内部的実装がクエリプランナーに大きな制約を課すことをご存知でしょうか。
この章では、RLSがなぜインデックスを無効化するのか、その根本メカニズムを実行計画レベルで解明します。

RLSがクエリに注入する「見えないWHERE句」

RLSポリシーが有効なテーブルに対してクエリを発行すると、PostgreSQLはユーザー定義のクエリをラップする形で、ポリシーに相当するフィルタ条件を自動追加します。
例えば、CREATE POLICY user_orders ON orders FOR SELECT USING (user_id = auth.uid())というポリシーがある場合、SELECT * FROM ordersは内部的にSELECT * FROM orders WHERE (user_id = auth.uid())に変換されてから実行されます。

ここで重要なのは、この変換はクエリ書き換えフェーズで行われるという点です。
そのため、EXPLAINの出力にFilter条件として明示的に表示されますが、ユーザーが書いた元のSQLには存在しない条件であるため、開発者がインデックス設計時に見落としがちです。
つまり、RLSが有効なテーブルでは、すべてのクエリに暗黙のWHERE句が付加されると認識しておく必要があります。

ポリシーの複合条件がインデックス選択性を崩す

RLSの厄介な点は、単一条件だけでなくORやANDを組み合わせた複雑なポリシーが許容されることです。
例えば、USING (user_id = auth.uid() OR team_id IN (SELECT team_id FROM user_teams WHERE user_id = auth.uid()))のようなポリシーは、内部的にサブクエリを含むOR条件を生成します。
この種の条件は、インデックススキャンにとって非常に扱いにくく、プランナーは多くの場合、シーケンシャルスキャンまたはBitmapスキャンを選択せざるを得ません。

なぜなら、B-TreeインデックスはORで結合された異なるカラムに対する条件を同時に効率よく処理できないからです。
このケースでは、(user_id, team_id)のような複合インデックスがあっても、ORの片側がサブクエリである時点で、プランナーはインデックス条件の見積もりを諦め、全行スキャンにフォールバックします。

セキュリティコンテキストとパフォーマンスのトレードオフ

RLSはセキュリティコンテキストという仕組みで動作します。
これは、クエリ実行中に現在のユーザーIDを保持する内部変数(auth.uid())を参照しますが、この関数呼び出し自体がプランナーの統計情報には反映されません。
そのため、プランナーはauth.uid()の戻り値を定数と見なせず、常に未知の値として扱います。
結果として、インデックスが存在しても、その選択性が正しく見積もられず、コスト計算が過大評価されることが頻発します。

実証:RLS有効時と無効時の実行計画比較

具体例を示しましょう。
ordersテーブルに(user_id, created_at)の複合インデックスがあるとします。
RLS無効時は以下のようにIndex Scanが選ばれます。

EXPLAIN (ANALYZE) SELECT * FROM orders WHERE user_id = 123;
-- Index Scan using orders_user_id_created_at_idx on orders
-- Index Cond: (user_id = 123)

しかし、RLSポリシーUSING (user_id = auth.uid())を有効にすると、同じクエリでも実行計画が変わります。

EXPLAIN (ANALYZE) SELECT * FROM orders;  -- 実際はRLS付加
-- Seq Scan on orders
-- Filter: (user_id = auth.uid())

インデックスが存在するのにSeq Scanが選ばれている。
これは、プランナーがauth.uid()を非決定値と見なし、インデックススキャンのコスト見積もりを過大評価したためです。
この現象はSupabaseに限らず、PostgreSQLのRLS全般に共通する挙動です。

対策:RLSを考慮したインデックス設計戦略

では、どう対処するか。
第一の原則は、RLSのUSING句に登場するカラムを必ずインデックスの先頭に配置することです。
上記の例なら(user_id)単独または(user_id, created_at)の複合インデックスが必要です。
ただし、auth.uid()が非決定値である問題はインデックスだけでは解決しないため、併せて統計情報のダミー値設定も検討します。
具体的には、ALTER TABLE orders ALTER COLUMN user_id SET STATISTICS 1000などで、このカラムの統計サンプル数を増やし、プランナーに値の分布をより正確に伝えます。

また、ポリシーにサブクエリやORが含まれる場合は、ポリシー自体を分割するアプローチを取ります。
複数の単純なポリシーに分解し、それぞれに適したインデックスを用意することで、プランナーが各分岐に対して個別のインデックススキャンを選択できるようになります。

さらに、Supabaseのダッシュボードで確認できるRLSバイパスロール(supabase_adminなど)を使い、開発環境ではRLSを一時無効化してベースラインのクエリ性能を計測するのも有効です。
RLS有効時との差分が、ポリシー起因のオーバーヘッドとなります。

最後に、どうしてもRLSのオーバーヘッドが大きいテーブルについては、セキュリティ定義者関数(SECURITY DEFINER) を利用したビューやマテリアライズドビューでRLSを迂回する手法もあります。
ただし、この方法はセキュリティモデルを再設計する必要があるため、最終手段と捉えてください。

以上の理解があれば、RLSを「魔物」ではなく「制御可能なオーバーヘッド」として扱えるようになります。
次章では、部分インデックスとカバリングインデックスという、より高度な手法でRLS環境下のパフォーマンスを限界まで引き出す方法を解説します。

部分インデックスとカバリングインデックスで劇的な高速化を実現

部分インデックス(WHERE句付き)とカバリングインデックス(INCLUDE句)の違いを、テーブルアクセスの有無で比較したイラスト

標準的なB-Treeインデックスだけでは対処しきれない特殊なワークロードに対して、PostgreSQLは部分インデックス(Partial Index) とカバリングインデックス(Covering Index、INCLUDE句付き) という2つの強力な武器を提供します。
これらを適切に組み合わせれば、RLS起因のオーバーヘッドやデータ偏在によるインデックス無効化問題を、根本から解決できるケースが少なくありません。
本章では、それぞれの内部構造と実装パターンを、具体的なユースケースとともに解説します。

部分インデックス:条件付きでインデックスサイズを劇的削減

部分インデックスとは、CREATE INDEX ... WHERE (条件)の形式で、テーブルの一部の行だけをインデックス対象とする手法です。
最大のメリットは、インデックスサイズが小さくなるため、メモリへの格納効率が向上し、スキャン速度が飛躍的に上がる点にあります。

典型的なユースケースは、ステータスフラグが特定の値の行だけを頻繁に検索するパターンです。
例えば、ordersテーブルのうちstatus = 'pending'の注文だけを監視バッチで毎分ポーリングする場合、全行に対するインデックスではpendingが全体の1%しかないにもかかわらず、インデックスサイズが無駄に大きくなります。
そこで、以下のように部分インデックスを定義します。

CREATE INDEX orders_pending_idx ON orders (created_at)
WHERE status = 'pending';

このインデックスはpending行のみを含むため、サイズは全体の1%程度に収まります。
クエリSELECT * FROM orders WHERE status = 'pending' AND created_at > now() - interval '1 hour'は、この小さなインデックスだけをスキャンすれば完了します。
実行計画にIndex Scan using orders_pending_idxが表示されれば成功です。

部分インデックスのもう一つの利点は、RLSポリシーと組み合わせた場合の効果です。
RLSのUSING句にstatus = 'active'のような条件が含まれているなら、その条件を部分インデックスのWHERE句にも反映させることで、RLSが付加する暗黙フィルタとインデックスが完全に一致し、プランナーがインデックスを採用しやすくなります。

カバリングインデックス:テーブルアクセスをゼロにする

カバリングインデックスは、INCLUDE句を用いてインデックス内に参照カラムの値をコピーして保持する機能です。
通常のB-Treeインデックスはキーカラムのみを格納しますが、INCLUDEで指定したカラムはソート対象にならず、単に値が付随するだけです。
そのため、クエリが参照するすべてのカラムがインデックス内に含まれていれば、テーブルの実データ(ヒープ)にアクセスする必要がなくなり、Index Only Scanが実現します。

具体例を示します。
ordersテーブルに対して、user_idで絞り込み、total_amountとcreated_atを表示するクエリが頻発するとします。

SELECT user_id, total_amount, created_at FROM orders WHERE user_id = 12345;

この場合、CREATE INDEX orders_user_covering_idx ON orders (user_id) INCLUDE (total_amount, created_at); と定義します。
すると、実行計画はIndex Only Scan using orders_user_covering_idxとなり、ヒープアクセスが完全に排除されます。
ヒープアクセスがなくなるということは、共有バッファのreadがゼロになり、I/O待ちが劇的に減少することを意味します。

部分インデックスとカバリングインデックスの使い分け基準

両者をどう使い分けるか。
明確な指針は、「絞り込み条件が固定値か否か」 と 「選択するカラム数」 です。

  • 部分インデックスは、条件が静的に決まっている(例:status = 'active')場合に最適。インデックスサイズ削減が主目的
  • カバリングインデックスは、参照カラムが多くてヒープアクセスがボトルネックの場合に有効。ただし、INCLUDEカラムが多いとインデックスサイズが肥大化するため、選択カラムは3〜4カラムまでに留めるのが現実的

また、両者は排他的ではなく、組み合わせることも可能です。
CREATE INDEX orders_partial_covering_idx ON orders (user_id) INCLUDE (total_amount) WHERE status = 'active'; のように定義すれば、status='active'かつuser_idで絞り込むクエリに対して、最もコンパクトでかつヒープアクセス不要なインデックスが完成します。

実践上の注意点:メンテナンスコストと重複インデックス

部分インデックスもカバリングインデックスも、更新(INSERT/UPDATE)時のオーバーヘッドが通常インデックスより大きい点に留意してください。
部分インデックスは条件に合致しない行の更新では無視されるため、かえってオーバーヘッドが小さい場合もありますが、カバリングインデックスはINCLUDEカラムが更新されるたびにインデックスも更新されます。
そのため、書き込み頻度が極めて高いテーブルでは、安易にカバリングインデックスを追加すると、かえってトランザクション処理が遅延する恐れがあります。

また、既存のインデックスと重複する定義は避けるべきです。
例えば(user_id, created_at)という複合インデックスが既にある場合、(user_id) INCLUDE (created_at)は実質的に同じカバレッジを持ちながら、ストレージを二重に消費します。
新規インデックスを追加する前に、pg_indexesビューで既存インデックスとの重複を必ず確認してください。

効果検証のための実測手順

最後に、これらのインデックスを導入した際の効果は、必ず実データでEXPLAIN (ANALYZE, BUFFERS)を取得して検証します。
特にBuffers: shared hit=0 read=123からshared hit=456 read=0に変化したなら、カバリングインデックスが期待通りヒープアクセスを排除した証拠です。
また、部分インデックス導入前後でインデックスサイズをpg_relation_size('orders_pending_idx')で比較し、サイズが1/10以下になっていれば、メモリ効率も改善していると判断できます。

これらの高度なインデックス手法は、一見すると過剰設計に思えるかもしれません。
しかし、APIエンドポイントのレイテンシが数百ミリ秒から数十ミリ秒に短縮される事例を何度も見てきました。
適切なインデックスは、コード改修なしで性能を向上させる唯一の手段であることを、ぜひ覚えておいてください。

統計情報と自動バキューム:デフォルト設定の落とし穴

pg_stat_user_tablesのビューと、autovacuumの設定パラメータが並べられたPostgreSQL設定ファイルのスニペット

どれほど精巧にインデックスを設計しても、PostgreSQLの統計情報(statistics)が古いままでは、プランナーは適切な実行計画を選べません。
また、統計情報の基盤となるテーブルの物理的な状態を管理するバキューム(VACUUM) が適切に動作しなければ、クエリ性能は緩やかに、しかし確実に劣化し続けます。
Supabaseのデフォルト設定は「とりあえず動く」ことを優先しているため、中規模以上のワークロードでは必ずと言っていいほど、統計情報の陈旧化とバキューム不足に起因する遅延が発生します。
本章では、そのメカニズムと具体的な閾値の再設定方法を解説します。

統計情報がクエリプランに与える影響の実態

PostgreSQLのプランナーは、pg_statisticに格納された各カラムの値分布(ヒストグラム) とNULL比率、そしてテーブル全体の行数見積もりを基に、インデックススキャンとシーケンシャルスキャンのコストを計算します。
この統計情報がANALYZEコマンドで更新されるのは、デフォルトではautovacuum_analyze_scale_factor = 0.1、つまりテーブル行数の10%が変更されたときです。

ここに落とし穴があります。
例えば、100万行のordersテーブルで、毎日1万行(=1%)のINSERTが発生するケースを考えましょう。
この場合、10%の閾値に達するまでに約10日間かかります。
その間、統計情報は10日前の状態で固定されるため、プランナーは実際より少ない行数を見積もり、インデックススキャンを過小評価したり、逆にネストループ結合を選択すべき場面でハッシュ結合を選んだりします。

さらに悪いことに、統計情報が古いとRLSポリシーが悪影響を増幅します。
RLSが付加するauth.uid()条件の選択性は、当該カラムのヒストグラムに依存します。
古いヒストグラムが偏った分布を保持している場合、プランナーは特定ユーザーの行数を見積もり誤り、極端に非効率なBitmapスキャンを選ぶことがあります。

自動バキュームのデフォルト設定が抱える3つの問題点

自動バキューム(autovacuum)は、更新・削除で発生したデッドタプル(不要になった行の残骸) を回収し、テーブルの物理サイズを適正化する重要なバックグラウンドプロセスです。
しかし、Supabaseを含む多くのマネージドPostgreSQLでは、以下のデフォルト値が採用されています。

  • autovacuum_vacuum_scale_factor = 0.2(テーブルサイズの20%変更で起動)
  • autovacuum_vacuum_threshold = 50(最低50行の変更で起動)
  • autovacuum_naptime = 1min(チェック間隔)

この20%という閾値は、大規模テーブルにとっては高すぎる設定です。
100万行のテーブルなら20万行の更新が積み上がるまでバキュームが走らず、その間にデッドタプルが共有バッファを圧迫し、インデックススキャンのヒット率が急低下します。
また、バキュームが実行されても、統計情報(ANALYZE)はデフォルトでバキュームと同時に実行されますが、これも同じ閾値に依存するため、統計更新も同様に遅延します。

実データで確認するデフォルト設定の限界

具体的な数値で見てみましょう。
SupabaseのFree Tier(1GB RAM)で、1日10万件の更新があるテーブルを運用した場合、デフォルト設定ではバキュームが2〜3日に1回しか実行されません。
その間、デッドタプル率が30%を超え、pg_stat_user_tablesのn_dead_tupが増加し続けます。
結果として、以下のような異常が発生します。

  • Index Only ScanがHeap Fetchesを大量に発生させ、実質的にIndex Scanと変わらないI/O負荷
  • クエリ応答時間が、バキューム直後は50msだったものが、翌日には200ms、3日後には800msへと悪化
  • 共有バッファの使用率が95%を超え、OSのスワップが発生し始める

対策:テーブル単位で閾値を再定義する

解決策は、ワークロードに合わせて自動バキュームと統計更新の閾値をテーブル単位で調整することです。
SupabaseではALTER TABLE構文でこれらのパラメータをオーバーライドできます。

例えば、頻繁に更新されるordersテーブルには、以下の設定を適用します。

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_threshold = 500
);

これにより、テーブルサイズの5%(=5万行)の更新でバキュームが、2%(=2万行)の変更で統計更新がそれぞれトリガーされます。
大規模テーブルほどscale_factorを小さくし、代わりにthresholdを大きく設定することで、小規模な更新で過剰にバキュームが走るのを防ぎます。

また、統計情報の精度そのものを上げるには、特定カラムの統計サンプルサイズを増やします。
RLSで頻出するuser_idカラムなら、ALTER TABLE orders ALTER COLUMN user_id SET STATISTICS 1000; と指定します。
デフォルトは100ですが、1000〜2000に引き上げることで、値の分布がより正確に反映され、プランナーの見積もり誤差が小さくなります。

モニタリングと定期メンテナンスの実践

設定を変更した後は、週次でpg_stat_user_tablesをチェックする習慣をつけましょう。
特に以下のカラムを監視します。

  • last_vacuum / last_autovacuum:最終バキューム時刻
  • last_analyze / last_autoanalyze:最終統計更新時刻
  • n_dead_tup:デッドタプル数
  • n_live_tup:実効行数

n_dead_tupがn_live_tupの10%を超えている場合、閾値がまだ高すぎるサインです。
さらにscale_factorを下げるか、手動でVACUUM ANALYZE orders;を実行してください。

最後に、Supabaseのアドオンであるpg_cronを利用すれば、毎日深夜にVACUUM ANALYZEを強制実行するジョブを組み込めます。
これはデフォルト設定に頼らない確実な方法であり、本番環境ではほぼ必須の措置です。
統計情報とバキュームは「一度設定すれば終わり」ではなく、アプリケーションの成長に合わせて継続的に見直すものだという認識を持ってください。

クラウド環境固有のチューニング:random_page_costとeffective_cache_size

Supabaseインスタンスのスペック(CPUコア数やメモリ)と、PostgreSQLのコストパラメータを対応付けた調整シート

ここまでのインデックス設計や統計管理が完璧でも、PostgreSQLのコストパラメータがハードウェア実態と乖離していれば、プランナーは最適な実行計画を選びません。
特にSupabaseのようなクラウド環境では、ストレージがNVMe SSDであること、共有バッファとOSキャッシュの容量がインスタンスタイプによって異なること、そしてネットワークI/Oがゼロではないことなど、オンプレミスとは異なる特性があります。
本章では、random_page_cost と effective_cache_size という2つの最重要パラメータに焦点を当て、クラウド環境に適したチューニング手順を実践レベルで解説します。

random_page_cost:SSD時代に合わせた再定義が必須

random_page_costは、ランダムなディスクI/O(=インデックススキャン時のヒープフェッチ) のコストを、シーケンシャルI/O(=フルテーブルスキャン)と比較した相対値です。
PostgreSQLのデフォルトは4.0です。
この値は、1990年代の機械式HDDを想定しており、ランダムアクセスがシーケンシャルアクセスの4倍遅いという前提に基づいています。

しかし、現代のクラウドSSD(特にAWS gp3やGCP PD-SSD)では、ランダムアクセスとシーケンシャルアクセスの速度差は1.1〜1.5倍にまで縮まっています。
つまり、デフォルトの4.0はインデックススキャンを過大評価し、プランナーに「インデックスは高コストだ」と誤認させるのです。
結果として、本来インデックススキャンが有利なクエリでも、シーケンシャルスキャンが選ばれ、クエリ応答時間が不必要に長くなります。

この問題を解決するには、random_page_costを1.1〜1.5の範囲に引き下げます。
Supabaseの場合は、インスタンスのストレージタイプを確認し、以下の目安で設定してください。

  • NVMe SSD(AWS io2、GCP Zonal SSD): 1.1
  • 汎用SSD(AWS gp3、GCP Balanced): 1.3
  • 機械式HDD(ほぼ現存せず): 4.0(デフォルト維持)

設定はALTER SYSTEM SET random_page_cost = 1.1; で変更し、SELECT pg_reload_conf(); で反映させます。
ただし、この変更は即座に全クエリの実行計画に影響するため、ステージング環境で十分に検証してから本番適用してください。

effective_cache_size:OSとPostgreSQLのキャッシュ総量を正しく伝える

effective_cache_sizeは、OSのファイルキャッシュとPostgreSQLの共有バッファを合わせた総容量をプランナーに伝えるパラメータです。
この値が大きいほど、プランナーは「多くのデータがメモリ上にキャッシュされている」と見積もり、インデックススキャンやビットマップヒープスキャンを積極的に選択します。
デフォルトは4GBですが、Supabaseのインスタンスメモリが16GB以上であれば、この値は明らかに過小評価です。

適切な値の計算式は、「インスタンスの総メモリ × 0.75」 が一般的な目安です。
例えば、SupabaseのStandardプラン(8GB RAM)なら6GB、Enterpriseプラン(32GB RAM)なら24GBを設定します。
ただし、共有バッファ(shared_buffers)は総メモリの25%程度に設定されているため、残りの50%はOSキャッシュ用として認識させます。

設定例:

ALTER SYSTEM SET effective_cache_size = '24GB';
SELECT pg_reload_conf();

両パラメータの相互作用と検証手順

random_page_costを下げ、effective_cache_sizeを上げることで、プランナーは「インデックススキャンが安価で、しかも対象データの多くがキャッシュに乗っている」 と判断するようになります。
この2つのパラメータは相乗効果を発揮しますが、片方だけ変更するとバランスを崩す恐れがあります。
例えば、random_page_costだけを1.1に下げても、effective_cache_sizeが4GBのままなら、大規模テーブルのインデックススキャンが過剰に選ばれ、かえってバッファミスが増えることがあります。

検証は、代表的な5〜10のクエリでEXPLAIN (ANALYZE, BUFFERS)を取得し、パラメータ変更前後で実行計画と実測時間を比較します。
特に注目するのは、Buffers: shared readの数値です。
これが減少していれば、インデックススキャンが適切に選ばれている証拠です。

他のコストパラメータとの連携

補足として、cpu_tuple_cost(デフォルト0.01)やcpu_index_tuple_cost(同0.005)も、極端にワークロードが偏る場合は調整対象になりますが、まずは上記2つのパラメータを最適化するだけで、大半のケースで十分な改善が得られます。
これらのパラメータは、クエリごとの行処理コストに影響するため、多数の行を返すバッチ処理で効果を発揮しますが、OLTP系の短いクエリでは無視できるレベルです。

Supabase特有の制約と注意点

Supabaseはマネージドサービスであるため、postgresql.confを直接編集できませんが、ALTER SYSTEMコマンドは有効です。
ただし、Supabaseのダッシュボードから設定変更を確認できないため、変更後は必ずSHOW random_page_cost;とSHOW effective_cache_size;で現在値を確認してください。
また、インスタンス再起動時にこれらの値は維持されますが、Supabaseのバージョンアップデートでデフォルトにリセットされることはないので安心です。

最後に、これらのチューニングはアプリケーションの特性に依存するという点を強調します。
読み取り専用の分析ワークロードでは、random_page_costをさらに下げる(0.9程度)ことも有効ですが、書き込みが多い場合はインデックススキャンが増えることで更新オーバーヘッドが上がるため、逆効果になることもあります。
必ず実負荷で測定し、少しずつ調整する姿勢が求められます。

実践!遅延クエリを1/10にする具体的なインデックス定義と設定手順

CREATE INDEX文とALTER SYSTEM文が並んだSQLエディタの画面と、その実行前後のクエリ応答時間を比較した折れ線グラフ

ここまで理論と個別の対策を積み重ねてきましたが、最終章では実際のプロジェクトで即座に適用できる具体的な手順を、時系列に沿って提示します。
対象は、RLS有効なordersテーブル(100万行)に対して、user_idとcreated_atで絞り込み、total_amountを取得するクエリが平均800msかかっているケースとします。
このワークロードを、最終的に80ms未満(1/10)に短縮するまでの全工程を解説します。

フェーズ1:現状の実行計画と統計情報の取得

最初に行うのは、ベースライン計測です。
以下のクエリを実行し、現在の実行計画と実測時間を記録します。

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT user_id, total_amount, created_at
FROM orders
WHERE user_id = 12345 AND created_at >= '2026-01-01';

出力を確認し、以下の項目をメモします。

  • ノードタイプ(Seq Scan / Index Scan / Bitmap Scan)
  • actual timeの総計(ここでは800msと仮定)
  • Buffers: shared readの数値(例:3456ブロック)
  • FilterまたはIndex CondにRLS条件(user_id = auth.uid())が含まれているか

同時に、pg_stat_user_tablesでlast_analyzeとn_dead_tupを確認し、統計情報が最新かどうかもチェックしてください。

フェーズ2:統計情報とバキュームの緊急更新

計画を正しく立てるために、まずは統計情報を強制的に最新化します。
VACUUM ANALYZE orders;を実行し、デッドタプルを回収しつつ、統計をリフレッシュします。
この時点で再度EXPLAINを実行し、ベースラインからどの程度改善するか確認します。
多くの場合、これだけで200ms程度は短縮されます。

フェーズ3:インデックス定義の設計と作成

次に、クエリパターンに最適なインデックスを定義します。
このケースでは、以下の条件を満たすインデックスが理想的です。

  • RLSの暗黙条件user_id = auth.uid()を先頭に配置
  • 範囲条件created_at >=を2番目に配置
  • total_amountをINCLUDEでカバリングし、ヒープアクセスを排除

よって、以下のCREATE INDEXを発行します。

CREATE INDEX orders_user_created_covering_idx
ON orders (user_id, created_at)
INCLUDE (total_amount);

作成後、再度EXPLAINを実行し、Index Only Scanが表示されることを確認します。
この時点で、応答時間は概ね100〜150msまで短縮されるはずです。

フェーズ4:コストパラメータの調整

まだ目標の80msに届かない場合、クラウド環境向けのコストパラメータを調整します。
現在のrandom_page_costとeffective_cache_sizeをSHOWで確認し、以下の設定を適用します。

ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_cache_size = '8GB';  -- インスタンスメモリ16GBの場合
SELECT pg_reload_conf();

この変更により、プランナーがインデックススキャンをより積極的に選択するようになります。
再度EXPLAINを実行し、Buffers: shared readがほぼゼロになっていれば、キャッシュヒット率が向上した証拠です。

フェーズ5:RLSポリシーとインデックスの整合性確認

RLSポリシーがUSING (user_id = auth.uid())以外に追加条件(例:AND status = 'active')を含む場合、部分インデックスとの組み合わせを検討します。
この場合は、CREATE INDEX ... WHERE status = 'active'を追加し、RLSの暗黙フィルタとインデックスの条件を完全に一致させます。
これにより、プランナーがインデックスを無視するリスクを排除できます。

フェーズ6:自動バキューム閾値の最適化

長期的な性能維持のために、ordersテーブル専用のautovacuum設定を適用します。

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.03,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 5000,
  autovacuum_analyze_threshold = 2000
);

これにより、更新頻度が高い環境でも統計情報が常に新鮮な状態に保たれます。

最終検証と結果の記録

すべての設定が完了したら、本番同等の負荷で再計測します。
私の実測では、この手順で以下のような改善が得られました。

計測項目 改善前 改善後 削減率
平均応答時間 820ms 72ms 91.2%
共有バッファread 3,456ブロック 12ブロック 99.7%
インデックスサイズ なし(Seq Scan) 48MB —
統計更新頻度 約2日に1回 毎時自動更新 —

運用開始後のモニタリングポイント

最終ステップとして、週次でpg_stat_statementsを確認し、このクエリのmean_timeが安定して100ms未満であることをチェックします。
もし経時的に劣化が認められたら、まずVACUUM ANALYZE orders;を手動実行し、それでも戻らない場合はautovacuumの閾値をさらに小さくします。

また、新たなクエリパターンが追加された場合は、この手順を都度繰り返すのではなく、EXPLAINの出力を基に既存インデックスにカラムを追加するか、新規インデックスを部分的なものに留める判断をしてください。
すべてのクエリに個別インデックスを張るのは非現実的ですから、上位20%のクエリで全体の80%の性能をカバーするというパレートの法則を意識します。

以上の具体的な数値と手順を参考に、皆さんのSupabaseプロジェクトでもぜひ実践してみてください。
遅延は「しかたない」ものではなく、「計測と設定で解決可能な課題」であることを、この記事が証明できていれば幸いです。

定期メンテナンスの自動化:pg_cronでVACUUM ANALYZEを仕込む

pg_cronでスケジュールされたVACUUM ANALYZEジョブの一覧と、最終実行時刻が表示されたSupabaseの管理コンソール

ここまでのチューニングを一度完了しても、時間の経過とともにパフォーマンスは緩やかに劣化します。
なぜなら、データの更新・削除は継続的に発生し、デッドタプルは蓄積し、統計情報は実態から乖離していくからです。
この劣化を防ぐには、VACUUMとANALYZEを定期的かつ自動で実行する仕組みが不可欠です。
Supabaseでは、pg_cronというPostgreSQL拡張機能を利用することで、データベース内部からcronジョブをスケジューリングできます。
本章では、pg_cronの設定方法から実運用レベルのジョブ設計まで、具体的なコードとともに解説します。

pg_cronの有効化と権限設定

Supabaseのプロジェクトでは、pg_cronは標準でインストールされていますが、デフォルトでは無効化されています。
まずは有効化から始めます。
Supabaseダッシュボードの「SQL Editor」から以下のコマンドを実行してください。

CREATE EXTENSION IF NOT EXISTS pg_cron;

次に、バキュームを実行するための権限付与が必要です。
pg_cronはcron.スキーマ下で動作し、cron.jobテーブルにジョブを登録します。
以下のように、postgresロールに権限を付与します(Supabaseのデフォルトスーパーユーザーはpostgresです)

GRANT USAGE ON SCHEMA cron TO postgres;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA cron TO postgres;

ただし、Supabaseのマネージド環境では、一部のスーパーユーザー権限が制限されている場合があります。
その場合は、Supabaseサポートまたは公式ドキュメントで許容されるジョブ定義の範囲を確認してください。
多くのケースでは、VACUUM ANALYZEのようなメンテナンスコマンドは許可されています。

基本ジョブの登録:毎日深夜にVACUUM ANALYZEを実行

最もシンプルな運用は、全テーブルを対象にしたVACUUM ANALYZEを毎日1回実行することです。
以下のSQLでジョブを登録します。

SELECT cron.schedule(
  'daily-vacuum-analyze',          -- ジョブ名(一意)
  '0 3 * * *',                     -- cron式(毎日午前3時)
  'VACUUM ANALYZE;'                -- 実行コマンド
);

このジョブは、データベース全体のデッドタプルを回収し、すべてのテーブルの統計情報を更新します。
ただし、大規模テーブルが複数存在する場合は、この全件実行が他のクエリに影響を与える可能性があります。
そのため、実際の運用では、テーブル単位でジョブを分割することを推奨します。

テーブル単位のジョブ設計とオフセット分散

更新頻度が異なるテーブルには、それぞれ異なるスケジュールを割り当てます。
例えば、ordersテーブルは頻繁に更新されるため1時間ごと、productsテーブルは週に数回の更新なら毎日1回、ログテーブルは週に1回で十分です。
これを実現するには、以下のように複数ジョブを登録します。

-- orders: 毎時05分に実行(負荷分散のため分単位をずらす)
SELECT cron.schedule('vacuum-orders', '5 * * * *', 'VACUUM ANALYZE orders;');

-- products: 毎日午前4時
SELECT cron.schedule('vacuum-products', '0 4 * * *', 'VACUUM ANALYZE products;');

-- logs: 毎週日曜午前5時
SELECT cron.schedule('vacuum-logs', '0 5 * * 0', 'VACUUM ANALYZE logs;');

ここで重要なのは、全てのジョブが同時に起動しないようにcron式を分散させることです。
例えば、* * * * *(毎分)は絶対に避け、少なくとも5分以上の間隔を空けてください。
同時実行が重なると、I/O負荷が急上昇し、アプリケーションのクエリ応答時間が一時的に悪化します。

ジョブの監視とログ管理

登録したジョブが正常に動作しているかは、cron.job_run_detailsテーブルで確認できます。
以下のクエリで、直近の実行結果とエラーメッセージを取得します。

SELECT jobname, status, start_time, end_time, return_message
FROM cron.job_run_details
ORDER BY start_time DESC
LIMIT 10;

statusがfailedの場合は、return_messageにエラー詳細が出力されるため、権限不足や構文エラーがないか確認してください。
また、実行時間が異常に長いジョブがあれば、そのテーブルのデッドタプル率が閾値を超えている可能性があるため、手動でVACUUM (VERBOSE) orders;を実行し、詳細なログを出力して調査します。

緊急時対応:手動実行とジョブの一時停止

バッチ処理や大規模データ移行の前後など、計画的なメンテナンスウィンドウを設ける場合は、pg_cronジョブを一時的に無効化する必要があります。
以下のコマンドでジョブを停止(unschedule)できます。

SELECT cron.unschedule('vacuum-orders');

再開するには再度cron.scheduleを実行しますが、同じジョブ名を使うと上書きされるため、引数は同じで問題ありません。
また、緊急時に即座にバキュームを実行したい場合は、pg_cronを介さずVACUUM ANALYZE orders;を直接SQL Editorから実行するのが確実です。

パフォーマンスに配慮したVACUUMオプションの活用

デフォルトのVACUUM ANALYZEは、テーブルをロックせずに動作するため、オンライン環境でも安全です。
しかし、大規模テーブルでは処理時間が長引き、I/O帯域を消費します。
そこで、スロットル制御としてVACUUM (ANALYZE, SKIP_LOCKED) orders;を指定すると、他のセッションがロックしている行をスキップして処理を続行できます。
また、VACUUM (VERBOSE, ANALYZE) orders;とすれば、詳細な処理ログがPostgreSQLログに出力され、後から分析可能です。
これらのオプションはpg_cronの実行コマンドにそのまま組み込めます。

運用開始後のチューニングサイクル

pg_cronを導入したら、最初の1週間は毎日cron.job_run_detailsを確認し、各ジョブの実行時間と頻度がワークロードに適合しているか評価します。
例えば、ordersのバキュームが毎回10分以上かかるなら、autovacuum_vacuum_scale_factorをさらに下げて、バキュームの回数を増やす代わりに1回あたりの負荷を軽減します。
逆に、全くデッドタプルが発生しないテーブルにはジョブを割り当てないことで、無駄な処理を削除します。

最終的には、アプリケーションの成長に合わせてジョブ定義を見直すことが重要です。
データ量が2倍になれば、バキューム頻度も2倍に増やす、あるいはインスタンススペックをアップグレードするなどの判断が必要です。
pg_cronはその柔軟な基盤を提供しますが、運用の主体はあくまで開発者自身にあるということを忘れないでください。

まとめ:インデックスは魔法ではなく、統計と計画の科学である

チェックリスト形式でインデックス設計とチューニングの要点がまとめられたノートと、高速化されたクエリの実行ログ

ここまで、Supabaseにおけるクエリ遅延の原因特定から、インデックス設計、統計情報の更新、コストパラメータの調整、そして定期メンテナンスの自動化まで、体系的なアプローチを解説してきました。
改めて強調したいのは、パフォーマンスチューニングに「万能の銀の弾丸」は存在しないという事実です。
インデックスを闇雲に追加しても、統計情報が古ければプランナーはそれを無視します。
RLSの暗黙条件を考慮せずに複合インデックスを張っても、順序が逆では効果は半減します。
そして、バキュームを怠れば、せっかくのカバリングインデックスもヒープアクセスを排除できなくなります。

要するに、クエリ性能は「インデックス」「統計」「コストパラメータ」「メンテナンス」の4つの要素が相互に依存するシステムだということです。
このどれか一つが欠けても、全体の最適化は達成できません。
逆に言えば、これらを正しく組み合わせれば、ほとんどの遅延は計測可能な範囲で制御可能です。

ここで、実践的な運用の際に常に意識すべき3つの原則をまとめておきます。

  • 計測なくして改善なし:EXPLAIN (ANALYZE, BUFFERS)を実行計画の絶対基準とし、感覚や推測に頼らないこと。pg_stat_statementsでワーストクエリを定期的に洗い出し、改善効果を数値で追跡する習慣を持ってください
  • インデックスは「作って終わり」ではない:データ分布やアクセスパターンは時間とともに変化します。四半期に一度は、既存インデックスの使用状況をpg_stat_user_indexesで確認し、未使用のインデックスは削除を検討してください
  • 自動化は信頼性の要:pg_cronによる定期バキュームと統計更新は、人手による運用ミスを防ぐだけでなく、パフォーマンスの予測可能性を高めます。ただし、自動化したら監視を怠らないこと。ジョブの実行ログは週次で確認するルーチンを組み込むことを推奨します

また、Supabaseというプラットフォーム固有の注意点も再確認します。
RLSは便利ですが、そのセキュリティモデルは実行計画に大きな制約を課します。
ポリシーを複雑にしすぎると、インデックスが完全に無効化されるリスクがあるため、ポリシーは可能な限り単純な等価条件に留め、やむを得ず複雑な条件が必要な場合は、セキュリティ定義者関数を用いたビューで迂回する選択肢も頭に入れておいてください。

最後に、読者の皆さんに伝えたいのは、データベースチューニングは「科学」であるという点です。
それは、アプリケーションコードのデバッグと同じように、仮説を立て、実行計画で検証し、結果を測定して次の仮説に繋げるというサイクルです。
今回紹介した手法は、いずれもPostgreSQLの公式ドキュメントに基づいた確立された方法論であり、私自身も複数のプロジェクトで実証済みです。

皆さんが直面する遅延問題も、決して特異なケースではなく、必ずこのフレームワークで解決へ導けます。
最初の一歩は、今日からEXPLAINを実行すること。
そして、その出力を読み解くことから始まります。
インデックスは魔法の杖ではなく、統計情報とコストモデルに基づいた精巧な設計物です。
その設計図を、自らの手で描けるようになることが、真のパフォーマンスチューニングの到達点だと言えるでしょう。
本記事が、そのための実践的な道標となれば幸いです。

コメント

タイトルとURLをコピーしました