今更ながらMATERIALIZED VIEW(マテリアライズドビュー)について学ぶ

アプリケーションにおいて高負荷になりやすい処理の一つにレポート・データ集計があります。
リアルタイム性が求められる場合もあれば、ある時点までに集計されたデータを見れればOKなど、要件はさまざまですが、一般的にはデータベースに対して複雑なクエリや複数テーブルに対しての結合を行う必要があります。そのためデータ量に依存しやすく、リリース当初は問題なくレポートが見れていたのに、サービスの運用期間が長くなるとレポートが見えなくなったという話をよく聞きます。

この問題への対処方法の1つとして、データのキャッシュ化が挙げられます。
リアルタイム性は失われてしまいますが、ある時点までの集計がされているデータを用意しておけば、都度、集計を行う必要がないため結果を表示するまでの時間を大幅に短縮することができます。

MATERIALIZED VIEWによるキャッシュ化

キャッシュ化をアプリケーション側で実装しても良いですが、実はデータベースにはMATERIALIZED VIEW(マテリアライズドビュー)という、SQLの実行結果をキャッシュする機能が提供されています。通常のビューとは異なり、SQLの実行結果を保持してくれるため、あたかも1つのテーブルのように扱うことができます。

PostgreSQLにおけるマテリアライズドビューはビューのようにルールシステムを使用しますが、あたかもテーブルであるかのような形態で結果を保持します。

マテリアライズドビュー

しかしながら名前に「ビュー」が含まれていることからも分かるように通常のテーブルとは異なります。
マテリアライズドビューは直接の更新(INSERT・UPDATE・DELETE)はできずREFRESHによる最新状態への更新のみがサポートされています。

マテリアライズドビューの定義

ユーザーが投稿した記事の一覧を管理する3つのテーブルを用意しました。
user_postsは中間テーブルであり、頻繁にusersとpostsを結合したデータを参照したいです。

SELECT
  user_posts.user_id,
  user_posts.post_id,
  users.name AS user_name,
  posts.title AS post_title,
  posts.body AS post_body
FROM
  user_posts
  JOIN users ON user_posts.user_id = users.user_id
  JOIN posts ON user_posts.post_id = posts.post_id
;

このSQLの実行結果をキャッシュ化するために、マテリアライズドビューを作成してみます。
CREATE MATERIALIZED VIEWを実行することで、簡単に作ることができました。

CREATE MATERIALIZED VIEW join_table AS (
  SELECT
    user_posts.user_id,
    user_posts.post_id,
    users.name AS user_name,
    posts.title AS post_title,
    posts.body AS post_body
  FROM
    user_posts
    JOIN users ON user_posts.user_id = users.user_id
    JOIN posts ON user_posts.post_id = posts.post_id
);

先ほども書いたようにテーブルではないため、当然、インデックスなどは持っていません。

postgres=# \d join_table
                Materialized view "public.join_table"
   Column   |          Type          | Collation | Nullable | Default 
------------+------------------------+-----------+----------+---------
 user_id    | integer                |           |          | 
 post_id    | integer                |           |          | 
 user_name  | character varying(30)  |           |          | 
 post_title | character varying(100) |           |          | 
 post_body  | text

差分の確認

先ほど定義したテーブルに100人のユーザー、300件の記事を作成して、1人のユーザーが3つの記事をもつ状態を用意します。この時点では作成したマテリアライズドビューには追加されたデータは反映されておらず、明示的に更新を行う必要があります。

postgres=# SELECT COUNT(*) FROM join_table;
 count 
-------
        0
(1 row)

マテリアライズドビューを最新の状態に更新するにはREFRESH MATERIALIZED VIEWを実行すれば良いです。
更新後に再度、データ数を確認すると300件になっていることが分かります。逆を言えば明示的な更新が行われるまでマテリアライズドビューは影響を受けないため、データ数が急増した場合などに突然、参照できなくなるということは考えにくいということです。

postgres=# REFRESH MATERIALIZED VIEW join_table;
REFRESH MATERIALIZED VIEW

postgres=# SELECT COUNT(*) FROM join_table;
 count 
-------
   300
(1 row)

vs. 既存のテーブルからの取得

マテリアライズドビューを定義したことで、どれだけ高速化されるのか比較してみました。
データ量の増加に伴って、通常のクエリ実行とマテリアライズドビューからのデータ取得の実行時間に差が開いていくことが分かります。

この結果からマテリアライズドビューの有効性が分かりました。
ただし、気をつける必要があるのはマテリアライズドビューからのデータ取得は全てSeq Scanになるという点です。
今回のようなデータ量がどんどんと増えていくケースにはIOコストが高くなる可能性があります。

Seq Scan on join_table  (cost=0.00..8.00 rows=300 width=85) (actual time=0.011..0.044 rows=300 loops=1)
 Planning Time: 0.291 ms
 Execution Time: 0.073 ms