ビューは、テーブルのように読める保存済みのクエリです: CREATE VIEW name AS SELECT ...。データを保存しないので、読むたびにクエリが実行され、常に最新の行が見えます。マテリアライズドビュー CREATE MATERIALIZED VIEW name AS SELECT ... は結果をディスクに保存し、REFRESH MATERIALIZED VIEW name を実行したときだけ変わります。
ビューを作った後に挿入した注文も結果に入っています (ben の 90)。ビューのクエリが SELECT の時点で実行されたからです。ビューは、繰り返し使う結合や絞り込みに名前を付けたり、見せるべきでない列をユーザーから隠したり、テーブルが変わっても古いクエリが動くようにしたりするのに使います。
ビューを変更、削除する
CREATE OR REPLACE VIEW はクエリをその場で入れ替えます。新しいクエリは同じ列を、同じ名前と型、同じ順序で返す必要があり、追加できるのは末尾の新しい列だけです。それ以外の変更は、ビューを削除して作り直します。
CREATE OR REPLACE VIEW paid_totals AS
SELECT customer, sum(amount) AS total, count(*) AS orders
FROM orders WHERE paid GROUP BY customer;
DROP VIEW paid_totals;
DROP VIEW IF EXISTS paid_totals CASCADE; -- also drops views built on it
ビューを通して挿入、更新する
1つのテーブルに対するビューは、GROUP BY、DISTINCT、集約関数、ウィンドウ関数、LIMIT、集合演算がなければ自動的に更新可能になります。そのビューへの INSERT、UPDATE、DELETE はテーブルを変更します。WITH CHECK OPTION を付けると、変更後にビューで見えなくなる行を拒否します。
どちらの変更も products に届きました。ビューを通して (4, 'shelf', false) を挿入すると、ビューに表示されない行なので new row violates check option for view "active_products" で失敗します。更新可能でないビューでは、INSTEAD OF トリガーで書き込み時の動作を定義できます。
マテリアライズドビューを作成、リフレッシュする
マテリアライズドビューはクエリを1回実行して行を保存します。読み込みはテーブルと同じくらい速いですが、新しいデータはリフレッシュするまで現れません。
リフレッシュ前の ana はまだ 120 で、リフレッシュ後は 180 です。REFRESH はクエリ全体を実行し直し、保存した行を置き換えます。マテリアライズドビューは、計算が重く、数分から数時間古くても構わない結果に使います。ダッシュボード、レポート、検索用の集計などです。
CREATE MATERIALIZED VIEW ... WITH NO DATA は空の状態で作成し、最初にリフレッシュするまでは読むと失敗します。
読み込みを止めずにリフレッシュする
通常の REFRESH はマテリアライズドビューをロックするので、それを読むクエリはリフレッシュが終わるまで待たされます。REFRESH MATERIALIZED VIEW CONCURRENTLY は新しい結果を別に作り、差分だけを反映するので、読み込みは続けられます。マテリアライズドビューに、普通の列名に対する WHERE なしのユニークインデックスが必要です。
ユニークインデックスがないと、リフレッシュは作成を促すヒント付きで失敗します。変わる行が少ないときは CONCURRENTLY のほうが通常のリフレッシュより速いこともあります。一度もデータを入れていないマテリアライズドビューには使えません。どちらの方法でも、1つのマテリアライズドビューのリフレッシュは一度に1つしか実行されません。ほかのインデックスはテーブルと同じように使えるので、クエリで絞り込む列にインデックスを作っておきます。
定期的にリフレッシュする
PostgreSQL はマテリアライズドビューを自動ではリフレッシュしません。psql を呼ぶ cron や、インストールされていれば pg_cron 拡張などのスケジューラーからリフレッシュを実行します。
# crontab: every 15 minutes
*/15 * * * * psql "$DATABASE_URL" -c 'REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals'
SELECT cron.schedule('refresh-totals', '*/15 * * * *',
'REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals');
ビューとマテリアライズドビューの違い
| ビュー | マテリアライズドビュー | |
|---|---|---|
| 行を保存する | しない | する |
| データの新しさ | 常に最新 | 最後のリフレッシュ時点 |
| 読み込みの速さ | クエリと同じ | テーブルと同じ |
| インデックス | 作れない (テーブルのインデックスを使う) | 作れる |
| 書き込み | 単純なビューなら可能 | 不可 |
ストレージパラメーターやテーブル空間など、残りのオプションは CREATE MATERIALIZED VIEW のリファレンス に載っています。
よくある質問
PostgreSQL のマテリアライズドビューは自動で更新されますか?
されません。最後の REFRESH MATERIALIZED VIEW (または作成時) の結果を、もう一度リフレッシュするまで保持します。リフレッシュは cron、pg_cron 拡張、またはアプリケーションから定期的に実行します。
ビューはクエリを直接実行するより速いですか?
速くありません。通常のビューはデータを保存せず、PostgreSQL はビューを読むクエリの中に展開するので、クエリを書き下したときと同じ速さで動きます。作業を省けるのはマテリアライズドビューだけで、その代わりにデータが古くなります。
マテリアライズドビューにインデックスを作れますか?
作れます。マテリアライズドビューはテーブルと同じように保存されるので、CREATE INDEX が使えます。REFRESH MATERIALIZED VIEW CONCURRENTLY にはユニークインデックスが必要です。
ビューとマテリアライズドビューを一覧するには?
psql では \dv でビューを、\dm でマテリアライズドビューを一覧できます。SQL では pg_views と pg_matviews を検索します。