PostgreSQLのマテリアライズドビューは、クエリの結果セットを物理テーブルとして事前に計算して保存することにより、クエリのパフォーマンスを向上させる強力なメカニズムを提供します。このチュートリアルでは、実際の例として DVDレンタルデータベース を使用して、PostgreSQLでマテリアライズドビューを作成する方法を説明します。
マテリアライズドビューについて
マテリアライズドビューは、物理テーブルとして保存されるクエリの結果セットのスナップショットです。仮想で、参照される度に基になるクエリを実行する通常のビューとは異なり、マテリアライズドビューはデータを保持するため、定期的な更新を犠牲にしてクエリのパフォーマンスを高速化できます。
マテリアライズドビューは、クエリの実行頻度に比べて基になるデータの変更が少ないシナリオで特に役立ちます。そのため、レポート作成、データウェアハウジング、リアルタイムデータが厳密な要件ではない状況などのシナリオに最適です。s
DVDレンタルデータベースのセットアップ
マテリアライズドビューに入る前に、DVDレンタルデータベースをセットアップしましょう。これは、人気のあるMySQL用のsakilaサンプルデータベースのPostgreSQLバージョンです。DVDレンタルデータベースは、公式のPostgreSQLチュートリアルページ（ PostgreSQLサンプルデータベース ）からダウンロードできます。
データベースファイルはZIP形式（dvdrental.zip）であるため、サンプルデータベースをPostgreSQLデータベースサーバーにロードする前に、データベースファイルをdvdrental.tarに抽出する必要があります。.tarファイルを解凍したら、"dvdrental"という新しいデータベースを作成し、pg_restoreコマンドを実行して .tarファイルの内容からdvdrentalデータベースを作成します：
pg_restore -U postgres -d dvdrental D:\sampledb\postgres\dvdrental.tar
上記のパスを、システム上で抽出されたdvdrental.tarを指すパスに置き換えます。
詳細なインストール手順は こちら でご覧いただけます。
Creating a Materialized View
各映画カテゴリによって生み出された総収益を示すマテリアライズドビューを作成するとします。ステップバイステップガイドは次のとおりです：
- PostgreSQLデータベースに接続します：
- 次のDMLステートメントを使用してマテリアライズドビューを作成します：
CREATE MATERIALIZED VIEW mv_category_revenue AS
SELECT
c.name AS category,
SUM(p.amount) AS total_revenue
FROM
category c
JOIN film_category fc ON c.category_id = fc.category_id
JOIN film f ON fc.film_id = f.film_id
JOIN inventory i ON f.film_id = i.film_id
JOIN rental r ON i.inventory_id = r.inventory_id
JOIN payment p ON r.rental_id = p.rental_id
GROUP BY
c.name;この例では、DVDレンタルデータベースの複数のテーブルを結合して、各映画カテゴリの総収益を計算します。
Navicat For PostgreSQL （または Navicat Premium ）16の場合：
- "マテリアライズドビュー"ボタンをクリックしてマテリアライズドビューオブジェクトリストを表示し、オブジェクトツールバーの"+ 新規マテリアライズドビュー"をクリックしてビューデザイナーを開きます。
- 上記のステートメントのSELECT部分を定義エディタに入力します：
- プレビューボタンをクリックして、ステートメントが期待どおりに機能することを確認できます：
- 保存ボタンをクリックして、新しいマテリアライズドビューを作成します。マテリアライズドビュー名の入力を求めるダイアログが表示されます。上記のCREATE MATERIALIZED VIEWステートメントと同じように、これを"mv_category_revenue"と呼びましょう：
- ダイアログの保存ボタンをクリックすると、Navicatは新しいマテリアライズドビューの名前を”無題”から指定した名前に変更します。また、新しいマテリアライズドビューが左側のナビゲーションウィンドウのマテリアライズドビューに追加されます：
結論
PostgreSQLのマテリアライズドビューは、リアルタイムデータが重要ではないシナリオでクエリパフォーマンスを最適化するための貴重なツールです。マテリアライズドビューは、複雑なクエリの結果を事前に計算して保存することで、分析タスクやレポートタスクの応答時間を大幅に短縮できます。このチュートリアルでは、DVDレンタルデータベースのマテリアライズドビューを作成する方法を学び、現実世界のシナリオでの実用的なアプリケーションを紹介しました。