PolarDB for PostgreSQL のリアルタイムマテリアライズドビュー - PolarDB

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

前提条件

PolarDB for PostgreSQL (Oracle 互換) クラスタで、次のいずれかのエンジンバージョンが実行されている必要があります。

PolarDB for PostgreSQL (Oracle 互換) 2.0 (リビジョンバージョン 2.0.14.11.0 以降)

説明

次の文を実行して、PolarDB for PostgreSQL (Oracle 互換) クラスタのマイナーエンジンバージョンをクエリできます。

SHOW polar_version;

背景情報

一般的なビューとは異なり、マテリアライズドビューはクエリ結果を格納できます。複雑なクエリシナリオでは、マテリアライズドビューを使用してクエリ結果を保存することで、クエリ効率を大幅に向上させることができます。ただし、マテリアライズドビューのデータは、ベーステーブルのデータによって変化することはありません。つまり、マテリアライズドビューを使用してクエリを実行する場合、常に最新のデータを取得できるとは限りません。

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

リアルタイムマテリアライズドビューは、文レベルの更新をサポートしています。ベーステーブルで DML 文 (INSERT、DELETE、UPDATE) が実行されると、マテリアライズドビューのデータが自動的に更新され、ベーステーブルのデータとの整合性が維持されます。
リアルタイムマテリアライズドビューは、ベーステーブルの増分データを最大限に活用します。マテリアライズドビューをリフレッシュすると、マテリアライズドビューのすべてのデータがクエリされます。マテリアライズドビューを頻繁にリフレッシュするよりも、リアルタイムマテリアライズドビューの方がパフォーマンスが向上します。
リアルタイムマテリアライズドビューは、クエリのパフォーマンスを大幅に向上させ、ベーステーブルとのデータ整合性を確保できます。

用語

ベーステーブル: マテリアライズドビューの定義で使用される一般的なテーブル。
デルタ: ベーステーブルのデータが変更されたときに追加または削除されるデータのコレクション (マテリアライズドビューのデータと比較)。
リフレッシュ: マテリアライズドビューを維持して、マテリアライズドビューのデータが、ビュー定義に基づいて現在のベーステーブルをクエリすることによって取得されたデータと一致するようにします。
デルタの適用: リアルタイムマテリアライズドビューから計算された増分データを挿入または削除して、リアルタイムマテリアライズドビューとベーステーブル間のデータ整合性を維持します。

制限事項

リアルタイムマテリアライズドビューには、次の制限があります。

ベーステーブルは、パーティションテーブル、継承テーブル、または列ストアテーブルではなく、一般的なテーブルである必要があります。
INNER JOIN のみがサポートされています。その他の JOIN タイプはサポートされていません。
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 のみです。

パフォーマンスの低下

リアルタイムマテリアライズドビューはクエリのパフォーマンスを大幅に向上させますが、ベーステーブルの書き込みパフォーマンスに大きな影響を与えます。読み取り操作の数の方が書き込み操作の数よりも多い場合は、リアルタイムマテリアライズドビューを使用することをお勧めします。
リアルタイムマテリアライズドビューがベーステーブルの書き込みパフォーマンスに与える影響は、ビュー定義、書き込み負荷、ベーステーブルの構造、インデックスなどの要因によって異なります。本番環境でリアルタイムマテリアライズドビューを作成する前に、まずテスト環境でリアルタイムマテリアライズドビューがベーステーブルの書き込みパフォーマンスに与える影響をテストすることをお勧めします。書き込みパフォーマンスが要件を満たしている場合は、本番環境でリアルタイムマテリアライズドビューを使用できます。
リアルタイムマテリアライズドビューのコストを削減するには、次の方法を使用できます。
- 同じベーステーブルに少数のリアルタイムマテリアライズドビューを作成します。
- データをベーステーブルにバッチ書き込みします。たとえば、COPY 文または INSERT INTO SELECT 文を実行して、データをバッチインポートできます。
- すべてのベーステーブルのプライマリキーを作成し、リアルタイムマテリアライズドビューの定義の射影された列にすべてのベーステーブルのプライマリキーを含めます。

仕組み

[リアルタイムマテリアライズドビューの作成]
- マテリアライズドビューのクエリを書き直して、リアルタイムマテリアライズドビューの維持に必要な非表示の列を計算します。
- リアルタイムマテリアライズドビューをリフレッシュするためのトリガーをベーステーブルに作成します。
- 指定された条件が満たされたときにリアルタイムマテリアライズドビューに一意なインデックスを作成して、デルタリフレッシュを高速化します。
[リアルタイムマテリアライズドビューのリフレッシュ]
- ベーステーブルのデータ変更によってトリガーがアクティブになります。
- トリガーを使用して、ベーステーブルから増分データを取得します。
- 現在のベーステーブルの定義と増分データに基づいて、リアルタイムマテリアライズドビューのデルタを計算します。
- 計算された増分データをリアルタイムマテリアライズドビューに適用して、デルタリフレッシュを実装します。
[リアルタイムマテリアライズドビューの削除]
- リアルタイムマテリアライズドビューのベーステーブルからデルタリフレッシュトリガーを削除します。
- リアルタイムマテリアライズドビューを削除します。

使用上の注意

[前提条件]
リアルタイムマテリアライズドビューを使用するには、データベースに polar_ivm 拡張機能を作成します。
```
CREATE EXTENSION polar_ivm WITH SCHEMA pg_catalog;
```

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

CREATE MATERIALIZED VIEW table_name[ (column_name [, ...] ) ]
[ BUILD DEFERRED|IMMEDIATE ]
REFRESH FAST
ON COMMIT
AS query

次の表にパラメーターを示します。

パラメーター	説明
table_name	作成するリアルタイムマテリアライズドビューの名前。スキーマ修飾できます。
column_name	リアルタイムマテリアライズドビューの列の名前。列名を指定しない場合、マテリアライズドビューは AS 句で指定されたクエリの結果セットの列名を使用します。
BUILD DEFERRED	ビューを作成しますが、データの設定は後で行います (最初の更新時など)。ビューをクエリしてもエラーは報告されません。ただし、ビューで `REFRESH MATERIALIZED VIEW` 文を実行するまで、データは返されません。
BUILD IMMEDIATE	ビューの作成後すぐにデータを設定します。これはデフォルトのオプションです。
query	結果がマテリアライズドビューに格納される SQL クエリ。SELECT、TABLE、または VALUES 文を指定できます。クエリは安全で制限された操作で実行されます。

[リアルタイムマテリアライズドビューの増分リフレッシュ]
```
REFRESH MATERIALIZED VIEW table_name
```
説明
- table_name: リフレッシュするリアルタイムマテリアライズドビューの名前。
- BUILD IMMEDIATE パラメーターを使用して作成されたリアルタイムマテリアライズドビューを手動でリフレッシュする必要はありません。
- BUILD DEFERRED パラメーターを使用して作成されたリアルタイムマテリアライズドビューをリフレッシュすると、ビュー定義に基づいてビューにデータが設定され、ベーステーブルに対する後続の変更がリアルタイムでビューに更新されます。

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

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

次の表にパラメーターを示します。

パラメーター	説明
IF EXISTS	リアルタイムマテリアライズドビューが存在しない場合、エラーではなくプロンプトが返されます。
table_name	削除するリアルタイムマテリアライズドビューの名前。スキーマ修飾できます。
CASCADE	ビューに依存するオブジェクト (他のマテリアライズドビューや通常のビューなど) と、自動的に削除されたオブジェクトに依存するすべてのオブジェクトを自動的に削除します。
RESTRICT	オブジェクトがリアルタイムマテリアライズドビューに依存している場合、ビューは削除されません。デフォルトでは、このパラメーターが使用されます。

例

polar_ivm 拡張機能をインストールします。

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;
```