Go to Qiita Advent Calendar 2024 Top
LoginSignup
17
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

PostgreSQLAdvent Calendar 2020

Day 14
@yugo-n(Yugo Nagata)

PostgreSQLのマテリアライズドビューの自動＆高速リフレッシュ機能を開発中

Last updated at Posted at 2020-12-14

本記事は「PostgreSQL Advent Calendar 2020」の 14日目です。

#はじめに

マテリアライズド・ビューを定義すると、クエリの実行結果をデータベース内に保存することでビューに対するレスポンスタイムを向上させることができます。その一方で、実テーブルが更新されると保存されている結果が古くなってしまうため、その都度マテリアライズド・ビューを最新化する必要が出てきます。

現在のPostgreSQLでは、マテリアライズド・ビューを最新化する方法としてREFRESH MATERIALIZED VIEWコマンドが提供されています。このコマンドを実行するとクエリが再実行され、マテリアライズド・ビューが最新の状態に更新されます。

しかし、この方法ではすべてのデータが再計算されてしまうため、あまり効率的とは言えません。もしテーブルの更新がごく一部で、マテリアライズド・ビューの一部分のみを更新できるとすれば、こちらの方が効率がよいはずです。

このような方法は、**増分ビューメンテナンス（Incremental View Maintenance, IVM）**と呼ばれています。しかし、この機能は現在のPostgreSQLには実装されていません1。そこで、私達はPostgreSQLにこの機能を開発し、開発コミュニティに提案中です！

ということで、この記事では現在開発中のIVM機能について紹介します。

なお、本記事の内容は「開発中」の機能についてであり、現在のPostgreSQLで利用可能な機能ではありません。この記事を読んで何か意見や要望などがありましたら、是非フィードバックをお願いいします。今後の開発や議論に役立たせていただきたいと思います。

#機能

###概要

現在提案中のIVM機能を使うと、テーブルを更新した直後に、そのテーブルを使って定義されているマテリアライズド・ビューを自動的に増分更新することが可能になります。増分更新なので、通常のREFRESHを実行するよりも高速です。また、もちろんユーザが自前でトリガ関数を書く必要はありません。

現在の所、ビュー定義に使用されるSQLクエリとして、通常のSELECT ... FROM ... WHERE ...の他、結合（内部結合、外部結合、自己結合）、一部の組み込み集約関数（count, sum, avg, min, max）、GROUP BY句、DISTINCT句、FROM句内の単純な2サブクエリ、単純なCTE（WITH句）、EXISTS サブクエリに対応しています。ビューには同じ内容のタプルが複数含まれていても構いません（重複タプルのサポート）。

一方で、上記以外の集約（ユーザ定義集約など）、複雑なサブクエリやCTE、ウィンドウ関数、ORDER BY、LIMIT, OFFSET, 集合演算（UNION、EXCEPT, INTERSECT）には対応していません。

###ビューの作成

自動で増分更新を行うマテリアライズド・ビューを作成するには、CREATE INCREMENTAL MATERIALIZED VIEWコマンドを使用します。このINCREMENTALというのは本実装でCREATE MATERIALIZED VIEWコマンドに新しく追加されたキーワードです3。新しい構文はpsqlコマンドでも確認することができます。

postgres=# \h CREATE MATERIALIZED VIEW 
Command:     CREATE MATERIALIZED VIEW
Description: define a new materialized view
Syntax:
CREATE [ INCREMENTAL ] MATERIALIZED VIEW [ IF NOT EXISTS ] table_name
    [ (column_name [, ...] ) 
    [ USING method ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    AS query
    [ WITH [ NO ] DATA ]

実際に簡単なビューを作成してみましょう。以下のように、pgbenchのテーブルを使用して以下のマテリアライズド・ビューを作成します。なお、ここではpgbenchのスケールファクタは100としました。

test=# CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm AS
        SELECT aid, bid, abalance, bbalance
        FROM pgbench_accounts JOIN pgbench_branches USING (bid);
SELECT 10000000

INCREMENTALオプションを付けているので、このマテリアライズド・ビューは増分更新の対象となります。このことはpsqlの\dメタコマンドで「Incremental view maintenance: yes」と表示されることからも確認できます。

test=# \d+ mv_ivm 
                             Materialized view "public.mv_ivm"
  Column  |  Type   | Collation | Nullable | Default | Storage | Stats target | 
Description 
----------+---------+-----------+----------+---------+---------+--------------+-
------------
 aid      | integer |           |          |         | plain   |              | 
 bid      | integer |           |          |         | plain   |              | 
 abalance | integer |           |          |         | plain   |              | 
 bbalance | integer |           |          |         | plain   |              | 
View definition:
 SELECT pgbench_accounts.aid,
    pgbench_accounts.bid,
    pgbench_accounts.abalance,
    pgbench_branches.bbalance
   FROM pgbench_accounts
     JOIN pgbench_branches USING (bid);
Access method: heap
Incremental view maintenance: yes

性能

先ほど作成したマテリアライズド・ビューを使ってIVMの効果をみてみましょう。まずREFRESHコマンドをこのビューに対して実行すると、およそ21秒くらいかかりました。

test=# REFRESH MATERIALIZED VIEW mv_ivm ;
REFRESH MATERIALIZED VIEW
Time: 21456.284 ms (00:21.456)

次に、ビューの増分更新にかかる時間を見ていきます。事前準備として、ビューに以下のインデックスを作成しておきます。ここでカラム aid, bid はそれぞれテーブル pgbench_accounts, pgbench_branches の主キーで、これらの組み合わせはマテリアライズド・ビューの中のタプルを一意に特定できるインデックスとなっています。

test=# CREATE INDEX ON mv_ivm (aid, bid);
CREATE INDEX

その後、pgbench_accounts を１行更新します。ここでは、aid = 1 のタプルの abalance を 11111 に変更してみました。所要時間は 15ms ほどで、REFRESHに比べると1000倍以上高速となっています。ビューの内容も正しく更新されています。

test=# SELECT * FROM mv_ivm WHERE aid = 1;
 aid | bid | abalance | bbalance 
-----+-----+----------+----------
   1 |   1 |      100 |     1000
(1 row)

Time: 1.583 ms
test=# UPDATE pgbench_accounts SET abalance = 11111 WHERE aid = 1;
UPDATE 1
Time: 15.647 ms
test=# SELECT * FROM mv_ivm WHERE aid = 1;
 aid | bid | abalance | bbalance 
-----+-----+----------+----------
   1 |   1 |    11111 |     1000
(1 row)

Time: 1.465 ms

ここでマテリアライズド・ビューに作成しておいたインデックスを削除してから、もう一度pgbench_accountsを更新してみます。

test=# DROP INDEX mv_ivm_aid_bid_idx ;
DROP INDEX
Time: 62.826 ms
test=# UPDATE pgbench_accounts SET abalance = 22222 WHERE aid = 1;
UPDATE 1
Time: 3355.529 ms (00:03.356)

今度は3秒ほどかかってしまいました。REFRESHコマンドの実行よりは高速ではありますが、インデックスがある時に比べると大幅に遅くなっています。このことはIVMを効率的に利用するためにはマテリアライズド・ビューに適切なインデックスが作成されている必要があることを示しています。その理由については後ほど説明します。

集約の場合

次に集約を行うビューによる例を紹介します。以下のマテリアライズド・ビューを作成します。今回はpgbenchのスケールファクタは1000を使用しています。

test2=# CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm2 AS
        SELECT bid, count(abalance), sum(abalance), avg(abalance)
        FROM pgbench_accounts GROUP BY bid;
SELECT 1000

このビューに対するREFRESHは80秒以上かかっています。

test2=# REFRESH MATERIALIZED VIEW mv_ivm2;
REFRESH MATERIALIZED VIEW
Time: 84594.766 ms (01:24.595)

一方、テーブルのタプルを１行更新し、同時にIVM機能によりビューも更新された場合の所要時間は19msほどで、今回は4000倍以上高速という結果になりました。

test2=# SELECT * FROM mv_ivm2 WHERE bid = 1;
 bid | count  | sum  |          avg           
-----+--------+------+------------------------
   1 | 100000 | 1000 | 0.01000000000000000000
(1 row)

Time: 2.199 ms
test2=# UPDATE pgbench_accounts SET abalance = abalance + 5000 WHERE aid = 1;
UPDATE 1
Time: 19.062 ms
test2=# SELECT * FROM mv_ivm2 WHERE bid = 1;
 bid | count  | sum  |          avg           
-----+--------+------+------------------------
   1 | 100000 | 6000 | 0.06000000000000000000
(1 row)

Time: 2.152 ms

TPC-H Q01

ここまではpgenchのテーブルを使った結果でしたが、より実践的な例として最後にTPC-Hベンチマークを使った結果を紹介します。TPC-Hの１番目のクエリ（Q01）は以下のようなもので、大きな１つのテーブルの集約となっています。

select
	l_returnflag,
	l_linestatus,
	sum(l_quantity) as sum_qty,
	sum(l_extendedprice) as sum_base_price,
	sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
	sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
	avg(l_quantity) as avg_qty,
	avg(l_extendedprice) as avg_price,
	avg(l_discount) as avg_disc,
	count(*) as count_order
from
	lineitem
where
	l_shipdate <= date '1998-12-01' - interval '78' day
group by
	l_returnflag,
	l_linestatus

スケールファクタが1のとき、このSELECTクエリを実行すると所要時間は11秒程かかりましたが、このクエリで定義されるマテリアライズド・ビューを作成した場合、ビューへのアクセスは3msほどでした。この「高速なレスポンスタイム」はマテリアライズド・ビュー本来の効果です。

一方、このビューのREFRESHには24秒程かかかります。それに対し、IVMを利用してテーブルの１行を更新とビューの最新化を同時に行ったところ、わずか22msで完了しました。

操作 所要時間
TPC-H Q01 のクエリをSELECT 11424.255ms
マテリアライズド・ビューに対するSELECT 3.128ms
マテリアライズド・ビューをREFRESH 24135.419ms
テーブルを１行更新 + IVMによるビュー更新 22.315ms

実は現在の実装ではビュー定義で使用できるクエリの制限のため、TPC-Hベンチマークのクエリの半数以下（22個中9個）しかサポートできないのですが、上記のようにサポート可能なクエリについては効果があることが確認できました。

###更新性能への影響

IVM機能によりマテリアライズド・ビューの最新化が自動的かつ高速に可能となりますが、当然ながらテーブルの更新性能には影響があります。これまでに説明したどのケースでも、テーブルの１行を更新するのに20ms前後の時間を要していますが、ビューの増分更新が無い場合、このテーブルの１行の更新は通常10ms未満で完了しています。したがって、今回のケースで少なくとも２倍程度はテーブルの更新性能が低下していることがわかります。また、同じテーブルが複数の増分メンテンナンスを行うマテリアライズド・ビューで使用されている場合には、そのビューの数だけ更新性能が低下してきます。更に、ビューを更新するたびにビューに対して排他ロック4を取得するために、複数トランザクションにおける同時更新では性能が大きく劣化します。

こういった事情からテーブルの更新が大量に発生する状況では現在の実装のIVMは不向きと言えます。ただし、テーブルの更新が少ないが、いざ更新された時には最新のクエリの結果をすぐに欲しい、といった状況では有用なものとなるでしょう。

#実装の概要

最後にIVMの実装ではどのようなことを行っているのか、その概要を簡単に説明します。

###IVMのイメージ

まず例を使って、IVMの処理のイメージを説明します。

以下のようなテーブルRとSを自然結合したビューVがあるとします。

IVMのイメージ（１）

このとき以下のようにテーブルRが更新され、(1, one, I) が (1, first, I) に変わったとします。図中で∇RはRから削除されたタプル、ΔRはRに挿入されたタプルを表しています。これらのRに発生した差分と、もう１つのテーブルS、そしてビュー定義を用いると、今度はビューに発生する差分∇VとΔVを計算することができます。

IVMのイメージ（２）

最後にビューの差分∇V、ΔVをビューVに適用することで、ビューの増分更新ができます。

IVMのイメージ（３）

どうでしょうか。IVMのイメージがつきましたでしょうか？

###ビューの増分メンテナンスの流れ

ビューの増分メンテナンスは以下の３つのステップで行われます。

  1. テーブルに発生した差分の抽出
  2. ビューに発生する差分の計算
  3. ビュー差分をビューに適用

IVMの流れ

各ステップでは以下のような処理が行われています。

####テーブル差分の抽出

テーブルに発生した差分の抽出には、文レベルのAFTERトリガと遷移表を使用しています。

AFTERトリガはCREATE INCREMENTAL MATERIALIZED VIEWコマンドでビューを定義したときに、その定義クエリに含まれるテーブルに対して自動的に作成されます。対象は INSERT, DELETE, UPDATE コマンドで、TRUNCATEコマンドについては現状では非対応です。

遷移表（Transition Table）はAFTERトリガの機能の１つで、これを使うとテーブルに発生した変化をトリガ関数の中からテーブルのように参照することができます。「テーブルから削除されたタプル」「テーブルに挿入されたタプル」を含む２つの遷移表が存在し、それぞれ「IVMのイメージ」の説明で出てきた∇R、ΔRに該当します。

####ビュー差分の計算

ビューに発生する差分は「IVMのイメージ」で説明しように、テーブル差分とビュー定義クエリを使用して計算しています。具体的には、ビューを定義するクエリを書き換えて、更新のあったテーブルを遷移表の内容と置き換えています5。この書き換え後のクエリを実行することで、ビューの差分を求めることができます。ここで求まったビュー差分は、∇V,ΔVに該当します。

####ビュー差分の適用

計算されたビュー差分はSQLクエリを使ってマテリアライズド・ビューに適用されます。具体的には、DELETE文をつかって削除すべきタプル（∇Vに含まれるタプル）をマテリアライズド・ビューから検索し削除してから、挿入すべきタプル（ΔVに含まれるタプル）をマテリアライズド・ビューに挿入します6。

性能について説明した時に「効率的なIVMのためには、マテリアライズド・ビューに適切なインデックスが必要」という話があったのを覚えてますでしょうか。これは、ビューから削除すべきタプルを検索する性能を向上させるためです。インデックスが無いとビューの中のすべてのタプルを順スキャンして削除対象を探すことになるため効率が悪くなります。

#まとめ

本記事では現在PostgreSQLで開発中のマテリアライズド・ビューを高速に最新化する機能、増分ビューメンテナンス（Incremental View Maintenance, IVM）について紹介しました。

この実装のパッチはPostgreSQLの開発コミュニティに提案して議論されている最中です。開発コミュニティのメーリングリストpgsql-hackersのImplementing Incremental View Maintenanceという件名のスレッドが議論の場です。実装の議論についてはwikiにまとめられています。

また、開発はGitHubで行われおり、レポジトリはこちらです。さらに、ちょっと動かしてみようかな、という人のためにDockerイメージもDocker Hubに公開してあります。是非動かしてみて、どのような形でもよいのでフィードバックを頂ければ幸いです。

###今後の予定

今の所PostgreSQL次期バージョンの14に入れてもらうことを目標として、議論中にもらったコメントに基づいて改良を続けています。現時点では、マテリアライズド・ビュー上のインデックスの作成または提案を自動で行う機能と、ビュー更新時に取得する排他ロックを最低限にする最適化を検討中です。

将来的にはビュー定義でサポートできるクエリの制限の緩和や、テーブルの更新と同時にビューを更新するのではなく、コマンドやビューの参照時などテーブルの更新と別のタイミングでビューを更新7できるようにしたいなぁ、などと考えています。

###最後に

皆様のフィードバックお待ちしております！

明日は@yamatattsuさんです。

  1. Oracle DB では増分リフレッシュ（または高速リフレッシュ）という名前でサポートされています。 ↩

  2. ここで単純とは、集約、外部結合、DISTINCTをクエリに含まないことを指しています。 ↩

  3. この構文はまだ開発中の暫定的なもので、この先の開発の過程で変更される可能性があります。 ↩

  4. これはビュー定義に含まれる複数のテーブルが、それぞれ別々トランザクションで更新されたときに、ビューの内容が不正に更新されてしまうのを避けるためです。 ↩

  5. クエリを書き換えるとありますが、実際にはSQLで書かれたクエリ文字列を書き換えるのではなく、クエリをパースした結果の内部表現（Query構造体）を書き換えています。 ↩

  6. 集約を含むビューの場合は、差分を適用する時に「ビュー中の集約値をUPDATEで更新する」という別の処理を行っていますが、この記事ではその詳細は解説しません。 ↩

  7. この方法は遅延メンテナンス（Deferred Maintenance）と呼ばれ、テーブル更新性能にあたえる影響が少ないです。一方で現状の実装で提供しているように、テーブル更新と同じトランザクションでビューを更新する方法は即時メンテナンス（Immediate Maintenance）と呼ばれています。 ↩

17
6
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
17
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?