PostgreSQL の生成カラムで管理画面のフィルタを高速化

管理画面が急に遅く・煩雑になる理由

管理画面はだいたいシンプルに始まります：テーブル、いくつかのフィルタ、"新着順" のソート。そこから現実的な要望が増えていきます。サポートは名前・メール・電話で検索したいと言い、営業は「最終アクティビティ順」、経理は「滞納残高」を求めます。要求が増えるたびに条件や結合、追加計算が増えます。

多くの管理リストが遅くなる理由は同じです：クリックするたびにクエリが変わること。フィルタやソートが多いと、データベースは一致を判断する前に各行の値を計算しなければならず、多くの行を走査する羽目になります。

転換点になりやすいのは、WHERE や ORDER BY に式が増えていく場合です。単純な列ではなく lower(email) や date_trunc('day', last_seen_at)、複数のステータスを一つの“バケット”にマッピングする CASE 文などでフィルタすると、単に遅くなるだけでなく、SQLが読みにくく、インデックスが効きにくく、間違いやすくなります。

煩雑な管理用SQLはたいてい次の繰り返しパターンから生まれます：

• 複数のフィールドを異なるルールでチェックする「検索」入力
• 派生値（フルネーム、優先度スコア、「最後の有意イベント」）でのソート
• 画面ごとにコピーされた業務ルール（有効/無効、支払済み/滞納）
• あちこちに散らばる小さなヘルパー調整（trim, lower, coalesce）
• 同じ計算値がリスト、フィルタ、ソートで何度も使われる

チームはこれをアプリ層で隠そうとしがちです：動的クエリビルダ、条件付き結合、コードでの事前計算など。これは動くこともありますが、UIとDBにロジックが分散するため、遅いクエリのデバッグがつらくなります。

目標はシンプルです：読みやすいまま速いクエリ。計算値が管理画面で何度も出てくるなら、PostgreSQL の生成カラムでルールを一箇所にまとめ、なおかつデータベースに最適化させるのが有効です。

生成カラムを平易に説明する

生成カラムは、他の列から値が計算される通常のテーブル列です。値を手で書き込む必要はなく、PostgreSQL があなたの定義した式で値を埋めます。

PostgreSQL の生成カラムは保存（STORED）されます。行が挿入または更新されるときに値が計算され、他の列と同様にディスクに保存されます。管理画面では通常これが望ましい振る舞いです：読み取りが速く、計算された値にインデックスを付けられます。

クエリの中で毎回同じ計算をするのとは違います。WHERE lower(email) = lower($1) を何度も書いたり、last_name || ', ' || first_name でソートしたりすると、そのたびにコストを払いますし、SQL が騒がしくなります。生成カラムはその繰り返し計算をテーブル定義に移します。クエリはシンプルになり、結果はどこでも一貫します。

ソースデータが変わると、PostgreSQL はその行の生成値を自動で更新します。アプリ側で同期を気にする必要はありません。

便利な考え方：

• 式を一度定義する。
• PostgreSQL が書き込み時に計算する。
• クエリは通常の列として読む。
• 保存されるのでインデックスを付けられる。

あとで式を変えるときはスキーマ変更になります。既存行が新しい式に合わせて更新されるので、マイグレーションの計画を立ててください。

フィルタやソートで役立つ用途

生成カラムは、値が他列から常に導出され、かつその値で頻繁にフィルタやソートをする場合に威力を発揮します。ワンオフのレポートにはあまり向きません。

実際に使う検索向けフィールド

管理画面の検索は純粋な全文検索であることは稀です。ユーザーは大文字小文字の違い、余分な空白などを気にします。正規化された「検索キー」を生成して保存しておけば、WHERE は読みやすくなり、画面間で挙動も揃います。

候補は結合されたフルネーム（小文字化・トリム済み）、空白を潰したクリーンな文字列、複数列から導出したステータスラベルなどです。

例えば lower(trim(first_name || ' ' || last_name)) を何度も書く代わりに、full_name_key を一度生成してそれでフィルタする、といった具合です。

人が期待する並びに合うソートキー

ソートでは計算フィールドの効果が早く出ることが多いです。ソートは多くの行で式を評価させるためです。

一般的なソートキーは、数値ランク（プランの階層を 1,2,3 にマッピング）、2つのタイムスタンプの最大値のような「最新アクティビティ」タイムスタンプ、テキストとして正しく並ぶようにパディングされたコードなどです。

ソートキーが普通のインデックス付き列なら、ORDER BY はずっと安くなります。

すばやいフィルタ用の派生フラグ

管理者は「未読あり」「滞納あり」などのチェックボックスを好みます。ロジックが安定していて行データのみで決まるなら、生成カラムの boolean として持つと良いでしょう。

例：unread_count > 0 から導く has_unread、due_date < now() と paid_at is null から導く is_overdue などを生成カラムにすると、UI フィルタは単純な条件に変換できます。

生成カラム、インデックス、他オプションの選び方

管理画面は高速なフィルタ、速いソート、そして数ヶ月後でも読みやすいSQLが必要です。決断は「計算はテーブルに置くか、インデックスに置くか、ビューにするか、アプリにするか」です。

生成カラムは、その値を実際の列のように扱いたい場合に適しています：参照しやすく、SELECT に見え、フィルタを追加する際に忘れにくい。通常のインデックスとも相性が良いです。

式インデックスはテーブル定義を変えずに速く追加できるため便利です。速度を重視し、SQL の見た目をあまり気にしないなら十分なことが多いです。欠点は可読性と、プランナが式を完全一致でマッチさせる必要がある点です。

ビューは複数テーブルを結合した「形」を共有したい場合に有用です。しかし複雑なビューは高コストな処理を隠してしまい、デバッグ箇所が増えます。

トリガーは通常列を同期させられますが、可動部が増えるため注意が必要です。バルク更新が遅くなったり、トラブルシューティング時に見落とされやすくなります。

場合によってはアプリで値を持つのが最良です。ユーザーがその値を編集する場合や、式が業務判断で頻繁に変わる場合は、明示的に扱うほうが分かりやすいことがあります。

簡単な選び方ガイド：

• 行データのみを基にした安定した式で、読みやすいクエリが欲しいなら生成カラム。\n- ひとつの特定フィルタだけ速くしたいなら式インデックスで素早く対応。\n- 多くの結合を含む再利用可能な形が欲しいならビューを検討。\n- テーブル間のロジックや副作用が必要ならまずアプリロジック、最後の手段としてトリガー。

ステップバイステップ：生成カラムを追加してクエリで使う

まずはUI上で遅さを体感する1つの管理リストクエリを選びます。画面で使われるフィルタとソートをメモして、そのクエリだけ改善しましょう。

繰り返し行われている計算を取り除くフィールドを選び、snake_case で分かりやすい名前を付けます。

1) 生成カラム（STORED）を追加

ALTER TABLE customers
ADD COLUMN full_name_key text
GENERATED ALWAYS AS (
  lower(concat_ws(' ', last_name, first_name))
) STORED;

インデックスを追加する前に実際の行で検証してください：

SELECT id, first_name, last_name, full_name_key
FROM customers
ORDER BY id DESC
LIMIT 5;

出力が期待と違うなら今のうちに式を直してください。STORED は挿入と更新のたびに PostgreSQL が値を保つことを意味します。

2) 管理画面に合ったインデックスを追加

画面がステータスでフィルタし名前でソートするなら、次のようなインデックスを作ります：

CREATE INDEX customers_status_full_name_key_idx
ON customers (status, full_name_key);

3) 管理クエリを新しい列を使うように更新

以前は煩雑な ORDER BY だったものが、明快になります：

SELECT id, status, first_name, last_name
FROM customers
WHERE status = 'active'
ORDER BY full_name_key ASC
LIMIT 50 OFFSET 0;

生成カラムは、毎日人がフィルタやソートに使う部分に使いましょう。珍しい画面向けには不要です。

実際の画面に合ったインデックスパターン

管理画面は繰り返し行動が決まっています：いくつかの列でフィルタ、ひとつの列でソート、ページング。最善策は「全部にインデックスを貼る」ことではなく、「最も頻繁に使われるクエリの形に合わせてインデックスを作る」ことです。

実用的ルール：最も一般的なフィルタ列を先に置き、最も一般的なソート列を最後に置きます。マルチテナントなら workspace_id が先頭に来ることが多いです：(workspace_id, status, created_at) のように。

テキスト検索は別問題です。多くの検索ボックスが ILIKE '%term%' になりがちで、基本的な btree インデックスでは高速化が難しいです。正規化したヘルパー列（小文字化、トリム、連結）を検索対象にするパターンは有効です。UI がプレフィックス検索（term%）を使えるなら、正規化列の btree インデックスが効きます。contains 検索（%term%）が必要なら、UI の振る舞いを見直すか、検索対象を絞る工夫をしてください。

またインデックスを追加する前に選択性を確認してください。行の95%が同じ値を持つような列（例：status = 'active'）は単独ではあまり役に立ちません。より選択性の高い列と組み合わせるか、少数ケース用に部分インデックスを検討してください。

現実的な例：速さを維持する顧客管理リスト

典型的な顧客管理ページを想像してください：検索ボックス、いくつかのフィルタ（非アクティブ、残高レンジ）、ソート可能な「最終閲覧」列。時間が経つと SQL は LOWER(), TRIM(), COALESCE(), 日付計算, CASE ブロックで煩雑になります。

繰り返し現れる式を生成カラムに押し込むと、速く読みやすく保てます。

テーブルと生成カラムの例

customers テーブルに name, email, last_seen, balance があるとします。次の3つの計算フィールドを追加します：

• search_key: 単純な検索用に正規化されたテキスト
• is_inactive: 日付ロジックを繰り返さない boolean
• balance_bucket: セグメント用のラベル

ALTER TABLE customers
  ADD COLUMN search_key text
    GENERATED ALWAYS AS (
      lower(trim(coalesce(name, ''))) || ' ' || lower(trim(coalesce(email, '')))
    ) STORED,
  ADD COLUMN is_inactive boolean
    GENERATED ALWAYS AS (
      last_seen IS NULL OR last_seen < (now() - interval '90 days')
    ) STORED,
  ADD COLUMN balance_bucket text
    GENERATED ALWAYS AS (
      CASE
        WHEN balance < 0 THEN 'negative'
        WHEN balance < 100 THEN '0-99'
        WHEN balance < 500 THEN '100-499'
        ELSE '500+'
      END
    ) STORED;

これで管理クエリは UI に合わせた読みやすいものになります。

読みやすいフィルタとソートの例

「非アクティブ顧客を最新活動順で」なら：

SELECT id, name, email, last_seen, balance
FROM customers
WHERE is_inactive = true
ORDER BY last_seen DESC NULLS LAST
LIMIT 50;

基本的な検索は：

SELECT id, name, email, last_seen, balance
FROM customers
WHERE search_key LIKE '%' || lower(trim($1)) || '%'
ORDER BY last_seen DESC NULLS LAST
LIMIT 50;

本当の利点は一貫性です。同じフィールドが複数画面でロジックなしに使えます：

• 顧客リストの検索は search_key を使う
• 「非アクティブ」タブは is_inactive を使う
• 残高フィルタは balance_bucket を使う

よくある失敗と罠

生成カラムは簡単な勝利に見えますが、画面のフィルタ・ソートと合致し、適切なインデックスを追加した場合にのみ効果を発揮します。

よくあるミス：

• インデックスなしで速くなると思い込むこと。計算値も大規模なフィルタやソートにはインデックスが必要です。
• 1つのフィールドにロジックを詰め込みすぎること。生成カラムが小さなプログラム化すると信頼されなくなります。短く明確に名付けましょう。
• 非イミュータブルな関数を使うこと。保存された生成カラムの式はイミュータブルである必要があるため、now() や random() は不適切か許可されないことが多いです。
• 書き込みコストを無視すること。挿入・更新で計算値を保つ必要があるため、インポートや連携が遅くなる可能性があります。
• 似たような列を増やしてしまうこと。標準化して一つか二つのパターンにまとめる方が良いです。

もし管理リストが contains 検索（ILIKE '%ann%'）を多用しているなら、生成カラムだけでは改善されないことが多いです。しかし日常的な「フィルタとソート」のワークホースクエリには、生成カラムと適切なインデックスでパフォーマンスが安定することが多いです。

リリース前の簡単チェックリスト

変更を本番に出す前に、計算値・クエリ・インデックスが揃っているか確認してください。

• 式が安定していて一言で説明できるか。\n- クエリが実際に WHERE や ORDER BY で生成カラムを使っているか。\n- インデックスが本番での利用に合っているか。\n- 既存ロジックとエッジケース（NULL、空文字、変なスペース、大文字小文字混在）で比較したか。\n- テーブルが多書き込みであれば書き込み性能もテストしたか（インポート、バックグラウンド更新、連携）。

次の一手：自分の管理画面に適用する

影響が大きく日常的に使われる2〜3画面（注文、顧客、チケットなど）から始めます。遅いと感じる箇所（日付範囲フィルタ、「最終活動」でのソート、結合名検索、ステータスラベルでのフィルタ）をメモし、再利用できる短い計算フィールドセットを標準化してください。

導入計画（計測しやすく元に戻しやすいもの）：

• 生成カラムを分かりやすい名前で追加する。\n- 既存ロジックを短期間並行運用して置き換えの安全性を担保する。\n- 主要なフィルタ・ソートに合ったインデックスを追加する。\n- 画面側のクエリを新しい列に切り替える。\n- 変更前後でクエリ時間と走査行数を測定し、改善を確認したら古い回避策を削除する。

内部管理ツールを AppMaster（ブランド名）で作っているなら、これらの計算フィールドは共有データモデルにうまくはまり、データベース側にルールを置いてUIは単純なフィールド名を参照できるようになります。