Posted inデータベース

PostgreSQLマテリアライズドビュー完全マスター：集計クエリを高速化し、テーブルロックなしでCONCURRENTLY更新する方法

Database tutorial - IT technology blog
Database tutorial - IT technology blog

クイックスタート（5分）：重いクエリを初めてキャッシュする

分析用ダッシュボードのために数百万行ものデータをスキャンすると、データベースのパフォーマンスは容易に低下します。社内ポータルを開くたびに注文総数、顧客生涯価値（LTV）、粗利益などを再計算していると、データベースのCPU使用率はあっという間に100%に達し、エンドユーザーは真っ白な画面の前で待たされることになります。

標準的なSQLビューではこのボトルネックを解消できません。通常のビューは単なる保存されたクエリアライアスに過ぎず、PostgreSQLは呼び出されるたびにクエリツリー全体を再評価します。一方、マテリアライズドビューはクエリを一度実行し、その生の結果を物理的なヒープページとしてディスクに書き込みます。そのため、通常のテーブルとまったく同様に事前計算済みのデータセットを読み出すことができます。

標準的なトランザクションテーブルを使ってテストしてみましょう。

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    amount NUMERIC(10, 2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 100万件のテスト用トランザクションを挿入
INSERT INTO orders (customer_id, amount, status, created_at)
SELECT
    (random() * 5000)::INT,
    (random() * 500 + 10)::NUMERIC(10, 2),
    (ARRAY['completed', 'pending', 'cancelled'])[floor(random() * 3 + 1)],
    NOW() - (random() * interval '90 days')
FROM generate_series(1, 1000000);

ディスクの速度やバッファキャッシュのウォームアップ状況にもよりますが、100万行全体に対する集計クエリには通常400ms〜900msほどかかります。このオーバーヘッドは、事前計算した合計値をマテリアライズドビューに保存することで完全に排除できます。

CREATE MATERIALIZED VIEW mv_customer_sales_summary AS
SELECT
    customer_id,
    COUNT(id) AS total_orders,
    SUM(amount) AS total_spent,
    MAX(created_at) AS last_order_date
FROM orders
WHERE status = 'completed'
GROUP BY customer_id;

これで、マテリアライズドビューへの問い合わせは極めてシンプルになります。

SELECT * FROM mv_customer_sales_summary WHERE total_spent > 5000;

クエリのレイテンシは即座に680msから1.8msへと激減します。PostgreSQLは65MBのordersテーブルをフルスキャンする必要がなくなり、事前集計済みのわずか200KBのスナップショットから直接データを読み出すためです。

詳細解説：マテリアライズドビューがアプリケーションキャッシュより優れている理由

データベース管理者の間では、集計メトリクスをRedisにキャッシュすべきか、それともPostgreSQL内で直接事前計算すべきかという議論が頻繁に交わされます。Redisはマイクロ秒単位の高速なキーバリュールックアップを提供しますが、その代償としてアプリケーション層の複雑さが大幅に増加します。

問題点：レポートツールによるデータベース接続の枯渇

朝のスタンドアップミーティングで、40人のオペレーションリーダーが一斉に管理ダッシュボードを開く場面を想像してください。アクセスごとに注文、配送ログ、返金テーブルにまたがるマルチテーブル結合がトリガーされます。通常のSQLビューを使用している場合、PostgreSQLはリクエストごとに並列クエリワーカーを立ち上げます。メモリはハッシュテーブルで満杯になり、クエリキューは滞留し、クライアント接続プールはあっという間に上限に達してしまいます。

トレードオフ：外部キーバリューストア vs データベースエンジン内の永続化

Redisのような外部キャッシュ層はポイントルックアップには適していますが、集計データのフィルタリングとなるとアーキテクチャ上の摩擦が生じます。例えば、アナリストが獲得日で高額顧客を絞り込み、生涯購入額でソートしたい場合はどうでしょうか。Redisを使用している場合、巨大なJSONブロブをアプリケーションメモリに取得するか、複雑なセカンダリインデックスを自前で維持管理しなければなりません。

マテリアライズドビューはこのギャップを巧みに埋めてくれます。リレーショナルクエリの柔軟性を維持したまま、重複する重い集計処理を完全に排除できるのです。

集計データに対するネイティブインデックスの作成

マテリアライズドビューは具体的な行データをディスク上に保持するため、通常のPostgreSQLテーブルと同様にインデックスを作成できます。通常のビューにはインデックスを作成できません。

CREATE INDEX idx_mv_customer_spent 
ON mv_customer_sales_summary(total_spent DESC);

インデックスを作成すれば、購入額上位100名の取得はフルテーブルスキャンではなくインデックススキャン（Index Scan）で処理されます。50万件の集計済み顧客データセットにおいて、クエリ実行時間は45msから0.4msへと短縮されます。

REFRESH CONCURRENTLYによるダウンタイムゼロの更新

マテリアライズドビューは静的なスナップショットです。ordersテーブルに新しい行が追加されても、ビューの内容は自動的には最新化されません。ビューのリフレッシュ自体は簡単です。

REFRESH MATERIALIZED VIEW mv_customer_sales_summary;

しかし、稼働中の本番環境でこのデフォルトコマンドを実行すると、深刻な問題を引き起こす可能性があります。

デフォルトリフレッシュによるロックのペナルティ

基本的なREFRESH MATERIALIZED VIEWコマンドは、テーブルにACCESS EXCLUSIVEロックを取得します。このロックは、再計算が完了するまでビューに対するすべての後続のSELECTクエリをブロックします。元テーブルのスキャンに45秒かかる場合、その間にキューイングされたすべてのユーザーリクエストは停滞し、HTTP 504 Gateway Timeoutエラーを招くことになります。

ゼロダウンタイムの解決策：CONCURRENTLYリフレッシュ

PostgreSQLにはノンブロッキングな代替手段としてREFRESH MATERIALIZED VIEW CONCURRENTLYが用意されています。ビューをロックする代わりに、PostgreSQLは一時テーブルに最新のデータセットを構築します。その後、差分（diff）を計算し、EXCLUSIVEロックを用いて既存のビューに直接変更を適用します。これにより、実行中のSELECTクエリを一切妨げることなく更新が完了します。

このモードを利用するには、PostgreSQLの要件として、WHERE句を持たない（部分インデックスではない）非NULLカラムに対する一意（UNIQUE）インデックスが事前に定義されている必要があります。

-- ステップ1: 部分インデックスではない一意インデックスを作成
CREATE UNIQUE INDEX idx_mv_customer_id_unique 
ON mv_customer_sales_summary(customer_id);

-- ステップ2: 読み取りをブロックせずにビューをリフレッシュ
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_customer_sales_summary;

リフレッシュ処理中、アクセス中のトラフィックは古いスナップショットを問題なく読み取ることができます。差分のコミットが完了すると、新規クエリはトランザクションの失敗やワーカースレッドのブロックなしで、即座に最新のレコードを参照できます。

定期リフレッシュの自動化

PostgreSQLには、標準で自動スケジューラーが付属していません。通常、定期更新は外部ジョブワーカー、crontab、またはpg_cronエクステンションを利用して処理します。本番環境向けのcron設定例を以下に示します。

# 読み取りをブロックせずに15分ごとに顧客サマリーをリフレッシュ
*/15 * * * * psql -U dbuser -d production_db -c "REFRESH MATERIALIZED VIEW CONCURRENTLY mv_customer_sales_summary;" >> /var/log/pg_refresh.log 2>&1

データベースエンジン内でスケジュールを完結させたい場合は、pg_cronをインストールし、ネイティブSQLを使ってリフレッシュをスケジュールします。

SELECT cron.schedule('refresh_customer_sales',
    '*/15 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_customer_sales_summary'
);

本番ワークロード運用の実践的なヒント

  • 一時的なディスク容量の急増に備える： CONCURRENTLYリフレッシュ中、PostgreSQLは差分を適用する前にビュー全体の最新データをバックグラウンドで一時的に構築します。処理中は、ビューの通常サイズの約2.5倍のディスク空き容量を確保しておく必要があります。
  • 実行速度とロック要件のバランスを見極める： CONCURRENTLYリフレッシュは標準リフレッシュに比べて約1.5倍〜2倍の時間がかかり、書き込みI/Oも増加します。読み取りトラフィックが発生しないオフピーク時のメンテナンスウィンドウで履歴データを更新する場合は、処理が早く終わる標準リフレッシュの利用を検討してください。
  • WITH NO DATAの落とし穴に注意： マイグレーション時の長時間のロックを回避するため、WITH NO DATAを付与して作成することがあります。ただし、データが投入されていないビューは読み取り不可（unreadable）としてマークされます。さらに、空のビューに対してCONCURRENTLYリフレッシュを実行することはできません。初回は必ず標準リフレッシュを実行する必要があります。
-- マイグレーションスクリプトでの即時デプロイ（行データは投入されない）
CREATE MATERIALIZED VIEW mv_customer_sales_summary AS
SELECT
    customer_id,
    COUNT(id) AS total_orders,
    SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
WITH NO DATA;

-- 初回データ投入が必須（CONCURRENTLYではなく標準リフレッシュ）：
REFRESH MATERIALIZED VIEW mv_customer_sales_summary;

-- これ以降の更新はCONCURRENTLYを安全に使用可能
CREATE UNIQUE INDEX idx_mv_customer_id_unique ON mv_customer_sales_summary(customer_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_customer_sales_summary;
  • EXPLAIN (ANALYZE, BUFFERS) で実行計画を監査する： 常にベースラインと最適化後のクエリをプロファイリングしてください。Buffers: shared hitのカウントを確認し、PostgreSQLが数百万行の元データをシーケンシャルスキャンする代わりに、共有バッファから直接データを取得できているかを検証しましょう。
Share:
Tags: