リアルタイムマテリアライズドビューの機能 - PolarDB - Alibaba Cloud ドキュメントセンター

このトピックでは、PolarDB for PostgreSQL のリアルタイムマテリアライズドビュー機能について説明します。

適用範囲

PolarDB for PostgreSQL は、次のバージョンをサポートしています。

PostgreSQL 14 (マイナーエンジンバージョン 2.0.14.8.11.0 以降)
PostgreSQL 11 (マイナーエンジンバージョン 2.0.11.9.27.0 以降)

説明

コンソールでマイナーエンジンバージョンを表示するか、SHOW polardb_version; 文を実行することで確認できます。ご利用のクラスターのマイナーエンジンバージョンが要件を満たしていない場合は、マイナーエンジンバージョンをアップグレードできます。

背景情報

標準ビューとは異なり、マテリアライズドビューはクエリ結果を直接格納します。複雑なクエリシナリオでは、これによりクエリ効率が大幅に向上します。ただし、マテリアライズドビューのデータは、そのベーステーブルのデータが変更されても自動的に更新されません。これは、マテリアライズドビューに対するクエリが常に最新のデータを返すとは限らないことを意味します。

この問題に対処するため、PolarDB はリアルタイムマテリアライズドビューを導入しました。標準のマテリアライズドビューと比較して、リアルタイムマテリアライズドビューには次の利点があります。

リアルタイムマテリアライズドビューは、文レベルの更新をサポートしています。ベーステーブルに対して挿入、削除、更新などの DML 操作を実行すると、文の完了後にリアルタイムマテリアライズドビューのデータが自動的に更新されます。これにより、リアルタイムマテリアライズドビューとベーステーブル間のデータ整合性が保証されます。
リアルタイムマテリアライズドビューは、ベーステーブルからの増分データを活用します。リアルタイムマテリアライズドビューをリフレッシュする際、ビューの完全なクエリは不要です。これにより、標準のマテリアライズドビューで必要とされる頻繁な完全更新よりも優れたパフォーマンスを提供します。
リアルタイムマテリアライズドビューは、クエリ結果がベーステーブルのデータと一致することを保証しつつ、クエリパフォーマンスを大幅に向上させることができます。

用語集

ベーステーブル：マテリアライズドビューの定義で使用される標準テーブル。
差分：ベーステーブルで発生する増分的な変更 (追加または削除) のコレクション。
リフレッシュ：マテリアライズドビューのデータがベーステーブルの現在のデータと一致するように更新するメンテナンス操作。
差分の適用：計算された増分データ (差分) をリアルタイムマテリアライズドビューに適用して、ベーステーブルとのデータ整合性を維持するプロセス。

制限事項

リアルタイムマテリアライズドビューのビュー定義には、次の制限が適用されます。

ベーステーブルは標準テーブルである必要があります。パーティションテーブルや継承テーブルは使用できません。
IMMUTABLE 関数のみがサポートされています。
単純なクエリ、射影、DISTINCT、および一部の集計関数を含むビュー定義のみがサポートされています。サブクエリ、[NOT] EXISTS、[NOT] IN、LIMIT、HAVING、DISTINCT ON、WITH (CTE)、ORDER BY、ウィンドウ関数、GROUPING SETS、CUBE、ROLLUP、UNION、INTERSECT、または EXCEPT などの複雑なクエリを含むビュー定義はサポートされていません。
GROUP BY 句を使用する場合、GROUP BY 句で指定された列も射影に含める必要があります。
サポートされている組み込み集計関数は、MIN、MAX、SUM、AVG、および COUNT のみです。
INNER JOIN を含むビュー定義はサポートされています。
OUTER JOIN (LEFT JOIN, RIGHT JOIN, and FULL JOIN を含む) を含むビュー定義はサポートされています。ただし、次の制限が適用されます。
説明
この機能は、マイナーエンジンバージョン 2.0.14.18.37.0 以降でのみサポートされています。
- OUTER JOIN と DISTINCT は一緒に使用できません。
- OUTER JOIN と GROUP BY は一緒に使用できません。
- OUTER JOIN と集計関数は一緒に使用できません。
- FROM 句には、OUTER JOIN とカンマ区切りのテーブルリストの両方を含めることはできません。
- OUTER JOIN クエリは、単純な等価結合条件のみをサポートします。AND/OR 演算子を持つ条件や、等価結合の両側が同じテーブルの列を参照する条件はサポートしていません。
- クエリに OUTER JOIN が含まれる場合、射影には JOIN 条件で使用されるすべての列を含める必要があります。
ベーステーブルにリアルタイムマテリアライズドビューを作成した後、ベーステーブルには次の DDL 制限が適用されます。
- CASCADE オプションを指定した場合にのみ、ベーステーブルに対して DROP TABLE コマンドを実行できます。
- ベーステーブルに対して ALTER TABLE コマンドを実行する際、マテリアライズドビューによって参照されている列を削除または変更することはできません。

パフォーマンスへの影響

リアルタイムマテリアライズドビューはクエリパフォーマンスを大幅に向上させますが、ベーステーブルの書き込みパフォーマンスに大きな影響を与えます。書き込み操作よりも読み取り操作が頻繁に行われるシナリオでリアルタイムマテリアライズドビューを使用してください。
リアルタイムマテリアライズドビューがベーステーブルの書き込みパフォーマンスに与える影響は、ビュー定義、書き込みペイロード、ベーステーブルの構造やインデックスなどの要因によって異なります。本番環境でリアルタイムマテリアライズドビューを作成する前に、ステージング環境でベーステーブルの書き込みパフォーマンスをテストしてください。書き込みパフォーマンスが要件を満たす場合にのみ、本番環境でリアルタイムマテリアライズドビューを使用してください。
次の方法で、リアルタイムマテリアライズドビューのメンテナンスコストを削減できます。
- 同じベーステーブルに過度に多くのリアルタイムマテリアライズドビューを作成しないようにします。
- ベーステーブルへのデータ書き込みはバッチで行います。たとえば、COPY または INSERT INTO SELECT コマンドを使用してデータをバッチでインポートします。
- 各ベーステーブルにはプライマリキーがあり、それはリアルタイムマテリアライズドビューの射影列に含まれている必要があります。

仕組み

リアルタイムマテリアライズドビューの作成
- システムはマテリアライズドビューのクエリを書き換え、メンテナンスに必要な非表示列を計算します。
- 増分更新を有効にするために、ベーステーブルにトリガーが作成されます。
- 特定の条件が満たされると、増分更新を高速化するためにリアルタイムマテリアライズドビューに一意のインデックスが作成されます。
リアルタイムマテリアライズドビューの増分更新
- ベーステーブルのデータ変更により、対応するトリガーが起動します。
- トリガーはベーステーブルから増分データを取得します。
- リアルタイムマテリアライズドビューの差分は、その定義とベーステーブルからの増分データに基づいて計算されます。
- 計算された差分がリアルタイムマテリアライズドビューに適用され、増分更新が完了します。
リアルタイムマテリアライズドビューの削除
- ベーステーブルからリアルタイムマテリアライズドビューの増分更新トリガーを削除します。
- リアルタイムマテリアライズドビュー自体を削除します。

使用ガイド

事前準備
リアルタイムマテリアライズドビューを使用したいデータベースで、polar_ivm 拡張機能を作成します。
```
CREATE EXTENSION polar_ivm WITH SCHEMA pg_catalog;
```

リアルタイムマテリアライズドビューの作成

CREATE MATERIALIZED VIEW table_name[ (column_name [, ...] ) ]
REFRESH FAST
ON COMMIT
AS query
[ WITH [ NO ] DATA ]

パラメーターの説明

パラメータ	説明
table_name	作成するリアルタイムマテリアライズドビューの名前。名前はスキーマ修飾が可能です。
column_name	新しいマテリアライズドビューの列名。列名を指定しない場合、名前はクエリの出力列名から取得されます。
WITH DATA	デフォルトのオプションです。完全なリアルタイムマテリアライズドビューを即座に作成します。
WITH NO DATA	リアルタイムマテリアライズドビューの構造のみを作成します。ビューにはデータが含まれず、リアルタイムで更新されません。このビューをクエリすると、ビューに対して `REFRESH MATERIALIZED VIEW` コマンドを実行するまでエラーが返されます。
query	リアルタイムマテリアライズドビューのビュー定義。SELECT、TABLE、または VALUES コマンドが使用できます。このクエリはセキュリティ制限のある操作で実行されます。

リアルタイムマテリアライズドビューの増分更新
```
REFRESH MATERIALIZED VIEW table_name
```
説明
注：
- table_name：リフレッシュするリアルタイムマテリアライズドビューの名前。名前はスキーマ修飾が可能です。
- WITH DATA で作成されたリアルタイムマテリアライズドビューは、データ整合性が自動的に維持されるため、手動リフレッシュは不要です。このようなビューに対する手動リフレッシュ操作はスキップされます。
- WITH NO DATA で作成されたリアルタイムマテリアライズドビューをリフレッシュすると、ビューはその定義に基づいてデータが投入され、その後のベーステーブルへの変更に対してリアルタイムリフレッシュが有効になります。

リアルタイムマテリアライズドビューの削除

DROP MATERIALIZED VIEW [ IF EXISTS ] table_name [, ...] [ CASCADE | RESTRICT ]

パラメーターの説明：

パラメーター	説明
IF EXISTS	リアルタイムマテリアライズドビューが存在しない場合でもエラーをスローしません。代わりに通知が発行されます。
table_name	削除するリアルタイムマテリアライズドビューの名前。名前はスキーマ修飾が可能です。
CASCADE	リアルタイムマテリアライズドビューに依存するオブジェクト (他のマテリアライズドビューや通常のビューなど) を自動的に削除し、さらにそれらのオブジェクトに依存するすべてのオブジェクトを削除します。
RESTRICT	リアルタイムマテリアライズドビューに依存するオブジェクトがある場合、削除を拒否します。これがデフォルトです。

例

集計関数を使用したリアルタイムマテリアライズドビュー

リアルタイムマテリアライズドビューに必要な拡張機能を作成します。
```
CREATE EXTENSION IF NOT EXISTS polar_ivm WITH SCHEMA pg_catalog ;
```

ベーステーブルを作成し、初期データをインポートします。

CREATE TABLE t( a INT, b VARCHAR);
INSERT INTO t VALUES
  (1,'a'),
  (2,'b'),
  (3,'c'),
  (4,'d'),
  (5,'e');

リアルタイムマテリアライズドビューを作成します。

CREATE MATERIALIZED VIEW mv
REFRESH FAST
ON COMMIT
AS
SELECT max(a),min(a),b FROM t GROUP BY b;

ベーステーブルに対して DML 操作を実行します。
- リアルタイムマテリアライズドビューのデータをクエリします。
```
SELECT * FROM mv ORDER BY b;
```
  次の結果が返されます。
```
 max | min | b
-----+-----+---
   1 |   1 | a
   2 |   2 | b
   3 |   3 | c
   4 |   4 | d
   5 |   5 | e
(5 rows)
```
  この結果は、リアルタイムマテリアライズドビューのデータがベーステーブルのデータと一致していることを示しています。
- ベーステーブルに新しいデータを挿入し、その後リアルタイムマテリアライズドビューのデータをクエリします。
```
INSERT INTO t VALUES(6,'f');

SELECT * FROM mv ORDER BY b;
```
  次の結果が返されます。
```
 max | min | b
-----+-----+---
   1 |   1 | a
   2 |   2 | b
   3 |   3 | c
   4 |   4 | d
   5 |   5 | e
   6 |   6 | f
(6 rows)
```
  この結果は、リアルタイムマテリアライズドビューのデータがベーステーブルのデータと一致していることを示しています。
- ベーステーブルからデータを削除し、その後リアルタイムマテリアライズドビューのデータをクエリします。
```
DELETE FROM t WHERE a = 2;

SELECT * FROM mv ORDER BY b;
```
  次の結果が返されます。
```
 max | min | b
-----+-----+---
   1 |   1 | a
   3 |   3 | c
   4 |   4 | d
   5 |   5 | e
   6 |   6 | f
(5 rows)
```
  この結果は、リアルタイムマテリアライズドビューのデータがベーステーブルのデータと一致していることを示しています。
- ベーステーブルのデータを更新し、その後リアルタイムマテリアライズドビューのデータをクエリします。
```
UPDATE t SET a = a + 1;

SELECT * FROM mv ORDER BY b;
```
  次の結果が返されます。
```
 max | min | b
-----+-----+---
   2 |   2 | a
   4 |   4 | c
   5 |   5 | d
   6 |   6 | e
   7 |   7 | f
(5 rows)
```
  この結果は、リアルタイムマテリアライズドビューのデータがベーステーブルのデータと一致していることを示しています。
リアルタイムマテリアライズドビューを削除します。
```
DROP MATERIALIZED VIEW mv;
```

OUTER JOIN を使用したリアルタイムマテリアライズドビュー

説明

この機能は、マイナーエンジンバージョン 2.0.14.18.37.0 以降でのみサポートされています。

リアルタイムマテリアライズドビューに必要な拡張機能を作成します。
```
CREATE EXTENSION IF NOT EXISTS polar_ivm WITH SCHEMA pg_catalog;
```

ベーステーブルを作成し、初期データをインポートします。

CREATE TABLE t1(a INT, b VARCHAR);
INSERT INTO t1 VALUES
  (1,'a'),
  (2,'b'),
  (3,'c'),
  (4,'d'),
  (5,'e');

CREATE TABLE t2(a INT, c VARCHAR);
INSERT INTO t2 VALUES
  (1,'e'),
  (2,'f');

リアルタイムマテリアライズドビューを作成します。

CREATE MATERIALIZED VIEW mv
REFRESH FAST
ON COMMIT AS
SELECT t1.a as t1a, t1.b,
       t2.a as t2a, t2.c
FROM t1 LEFT JOIN t2
ON t1.a = t2.a;

ベーステーブルに対して DML 操作を実行します。
- リアルタイムマテリアライズドビューのデータをクエリします。
```
SELECT * FROM mv ORDER BY b;
```
  次の結果が返されます。
```
 t1a | b | t2a | c
-----+---+-----+---
   1 | a |   1 | e
   2 | b |   2 | f
   3 | c |     |
   4 | d |     |
   5 | e |     |
```
  この結果は、リアルタイムマテリアライズドビューのデータがベーステーブルのデータと一致していることを示しています。
- ベーステーブルに新しいデータを挿入し、その後リアルタイムマテリアライズドビューのデータをクエリします。
```
INSERT INTO t2 VALUES(3,'g');

SELECT * FROM mv ORDER BY b;
```
  次の結果が返されます。
```
 t1a | b | t2a | c 
-----+---+-----+---
   1 | a |   1 | e
   2 | b |   2 | f
   3 | c |   3 | g
   4 | d |     | 
   5 | e |     | 
```
  この結果は、リアルタイムマテリアライズドビューのデータがベーステーブルのデータと一致していることを示しています。
- ベーステーブルからデータを削除し、その後リアルタイムマテリアライズドビューのデータをクエリします。
```
DELETE FROM t1 WHERE a in (1, 4);

SELECT * FROM mv ORDER BY b;
```
  次の結果が返されます。
```
 t1a | b | t2a | c
-----+---+-----+---
   2 | b |   2 | f
   3 | c |   3 | g
   5 | e |     |
```
  この結果は、リアルタイムマテリアライズドビューのデータがベーステーブルのデータと一致していることを示しています。
- ベーステーブルのデータを更新し、その後リアルタイムマテリアライズドビューのデータをクエリします。
```
UPDATE t2 SET a = a + 1;

SELECT * FROM mv ORDER BY b;
```
  次の結果が返されます。
```
 t1a | b | t2a | c
-----+---+-----+---
   2 | b |   2 | e
   3 | c |   3 | f
   5 | e |     |
```
  この結果は、リアルタイムマテリアライズドビューのデータがベーステーブルのデータと一致していることを示しています。
リアルタイムマテリアライズドビューを削除します。
```
DROP MATERIALIZED VIEW mv;
```