pg_ivm 插件为云数据库 PostgreSQL 版提供了增量视图维护（IVM）功能。
增量视图维护（IVM）是一种使物化视图保持最新状态的方法，其中仅计算增量更改并将其应用于视图，而不是像 REFRESH MATERIALIZED VIEW 那样从头开始重新计算内容。当仅更改视图的一小部分时，IVM 可以比重新计算更有效地更新物化视图。
pg_ivm 插件仅支持在 PostgreSQL 13 及以上版本的实例中使用。如需使用该插件，请注意在创建实例时选择合适的实例版本。
创建插件
CREATE EXTENSION pg_ivm;
删除插件
DROP EXTENSION pg_ivm;
我们将支持 IVM 的物化视图称为增量可维护物化视图 (IMMV)。如需创建 IMMV，必须使用表名称和视图定义语句调用 create_immv 函数。例如：
testdb=> CREATE TABLE mv_base_a (i int, j int); CREATE TABLE testdb=> INSERT INTO mv_base_a VALUES testdb-> (1,10), testdb-> (2,20), testdb-> (3,30), testdb-> (4,40), testdb-> (5,50); INSERT 0 5 testdb=> CREATE TABLE mv_base_b (i int, k int); CREATE TABLE testdb=> INSERT INTO mv_base_b VALUES testdb-> (1,101), testdb-> (2,102), testdb-> (3,103), testdb-> (4,104); INSERT 0 4 -- 创建immv testdb=> SELECT create_immv('mv_ivm_1', 'SELECT i,j,k FROM mv_base_a a INNER JOIN mv_base_b b USING(i)'); NOTICE: could not create an index on immv "mv_ivm_1" automatically DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause. HINT: Create an index on the immv for efficient incremental maintenance. create_immv ------------- 4 (1 row) testdb=> SELECT * FROM mv_ivm_1 ORDER BY 1,2,3; i | j | k ---+----+----- 1 | 10 | 101 2 | 20 | 102 3 | 30 | 103 4 | 40 | 104 (4 rows) testdb=> BEGIN; BEGIN testdb=*> INSERT INTO mv_base_b VALUES(5,105); INSERT 0 1 testdb=*> SELECT * FROM mv_ivm_1 ORDER BY 1,2,3; --insert数据自动更新到immv i | j | k ---+----+----- 1 | 10 | 101 2 | 20 | 102 3 | 30 | 103 4 | 40 | 104 5 | 50 | 105 (5 rows) testdb=*> UPDATE mv_base_a SET j = 0 WHERE i = 1; UPDATE 1 testdb=*> SELECT * FROM mv_ivm_1 ORDER BY 1,2,3; --update数据自动更新到immv i | j | k ---+----+----- 1 | 0 | 101 2 | 20 | 102 3 | 30 | 103 4 | 40 | 104 5 | 50 | 105 (5 rows) testdb=*> DELETE FROM mv_base_b WHERE (i,k) = (5,105); DELETE 1 testdb=*> SELECT * FROM mv_ivm_1 ORDER BY 1,2,3; --delete数据自动更新到immv i | j | k ---+----+----- 1 | 0 | 101 2 | 20 | 102 3 | 30 | 103 4 | 40 | 104 (4 rows) testdb=> ROLLBACK; ROLLBACK
pg_ivm_immv，用于存储 IMMV 信息。
| 参数 | 类型 | 描述 |
|---|---|---|
| immvrelid | regclass | IMMV 的 OID。 |
| viewdef | text | 视图定义的解析树（这是反编译的重建，而不是命令的原始文本，用 get_immv_def 来进行获取原始文本）。 |
| ispopulated | bool | 如果 IMMV 当前已填充，则为 True。 |
testdb=> SELECT immvrelid,get_immv_def(immvrelid),ispopulated FROM pg_ivm_immv ORDER BY 1; immvrelid | get_immv_def | ispopulated -----------+----------------------------------+------------- mv_ivm_1 | SELECT a.i, +| t | a.j, +| | b.k +| | FROM (mv_base_a a +| | JOIN mv_base_b b USING (i)) | mv | SELECT i +| t | FROM t | (2 rows)
使用 create_immv 函数创建 IMMV。create_immv 函数的使用示例如下：
create_immv(immv_name text, view_definition text) RETURNS bigint
create_immv 定义查询的新 IMMV。创建名为 immv_name 的表，并执行由 view_definition 指定的查询并将其用于填充 IMMV。查询存储在 pg_ivm_immv 中，以便稍后在增量视图维护时刷新它。create_immv 返回创建的 IMMV 中的行数。
testdb=> CREATE TABLE t (i int PRIMARY KEY); CREATE TABLE testdb=> INSERT INTO t SELECT generate_series(1, 100); INSERT 0 100 testdb=> SELECT create_immv('mv', 'SELECT * FROM t'); NOTICE: created index "mv_index" on immv "mv" create_immv ------------- 100 (1 row)
使用 refresh_immv 函数刷新 IMMV。refresh_immv 函数的使用示例如下：
refresh_immv(immv_name text, with_data bool) RETURNS bigint
refresh_immv 函数可以完全替换 IMMV 的内容，就像 REFRESH MATERIALIZED VIEW 命令对物化视图所做的那样。要执行此功能，您必须是 IMMV 的 owner（使用 PostgreSQL 16 或更早版本）或对 IMMV 具有 MAINTAIN 权限（使用 PostgreSQL 17 或更高版本）。执行 refresh_immv 函数后，IMMV 中的旧内容将被丢弃。with_data 标志对应于 REFRESH MATERIALIZED VIEW 命令的 WITH [NO] DATA 选项。
注意
如果您使用 PostgreSQL 17 或更高版本，则在运行 refresh_immv 时，search_path 会暂时更改为 pg_catalog、pg_temp。
testdb=> SELECT immvrelid, ispopulated FROM pg_ivm_immv where immvrelid='mv'::regclass; immvrelid | ispopulated -----------+------------- mv | t (1 row) testdb=> SELECT refresh_immv('mv', true); -- Refresh IMMV with data refresh_immv -------------- 100 (1 row) testdb=> SELECT immvrelid, ispopulated FROM pg_ivm_immv where immvrelid='mv'::regclass; immvrelid | ispopulated -----------+------------- mv | t (1 row) testdb=> INSERT INTO t VALUES(101); INSERT 0 1 testdb=> SELECT i FROM mv ORDER BY 1 desc limit 1; -- INSERT原表数据自动更新到immv i ----- 101 (1 row) testdb=> SELECT refresh_immv('mv', false); -- Make IMMV unpopulated refresh_immv -------------- 0 (1 row) testdb=> SELECT immvrelid, ispopulated FROM pg_ivm_immv where immvrelid='mv'::regclass; immvrelid | ispopulated -----------+------------- mv | f (1 row) testdb=> SELECT i FROM mv ORDER BY 1 desc limit 1; -- 因为IMMV被unpopulated，所以不会有数据 i --- (0 rows) testdb=> INSERT INTO t VALUES(102); INSERT 0 1 testdb=> SELECT i FROM mv ORDER BY 1 desc limit 1; -- 因为IMMV被unpopulated，INSERT不会更新到 i --- (0 rows) testdb=> SELECT refresh_immv('mv', true); -- Refresh the IMMV and make it populated. refresh_immv -------------- 102 (1 row) testdb=> SELECT i FROM mv ORDER BY 1 desc limit 1; -- IMMV中数据恢复正常 i ----- 102 (1 row) testdb=> INSERT INTO t VALUES(103); INSERT 0 1 testdb=> SELECT i FROM mv ORDER BY 1 desc limit 1; -- INSERT原表数据自动更新到immv i ----- 103 (1 row) testdb=>
get_immv_def 函数用于重建 IMMV 的底层 SELECT 命令。get_immv_def 函数的使用示例如下：
get_immv_def(immv regclass) RETURNS text
testdb=> SELECT immvrelid, get_immv_def(immvrelid) FROM pg_ivm_immv ORDER BY 1; immvrelid | get_immv_def -----------+---------------------------------- mv_ivm_1 | SELECT a.i, + | a.j, + | b.k + | FROM (mv_base_a a + | JOIN mv_base_b b USING (i)) mv | SELECT i + | FROM t (2 rows)
支持的聚合函数包括 count、sum、avg、min 和 max。目前仅支持内置聚合函数，无法使用用户定义的聚合。
请注意：
对于 min 或 max，当从基表中删除包含当前最小值或最大值的元组时，可能会根据受影响的组从基表重新计算新值。因此，更新包含这些函数的 IMMV 可能需要很长时间。
在 IMMV 中对实数 (float4) 类型或双精度 (float8) 类型使用 sum 或 avg 是不安全的，因为由于这些类型的精度有限，IMMV 中的聚合值可能与从基表计算的结果不同。要避免此问题，请改用 numeric 类型。
如果有包含 GROUP BY 子句，则 GROUP BY 中指定的表达式必须出现在目标列表中。这就是识别 IMMV 中要更新的元组的方式。这些属性用作在 IMMV 中搜索元组的扫描键，因此需要对它们进行索引以实现高效的 IVM。
支持在 FROM 字句中使用 EXISTS 的子查询和简单子查询，不支持非“AND”的和目标列表中的 EXISTS 子查询。EXISTS 子查询仅在 WHERE 中受支持，但在目标列表中不受支持。
如果 EXISTS 包含引用外部查询中表中的列，则这些列必须包含在目标列表中。不支持包含聚合函数或 DISTINCT 的子查询。
支持简单 CTE（WITH 查询），不支持包含聚合函数或 DISTINCT 的 WITH 查询。
不允许递归查询（WITH RECURSIVE）。也不允许未引用的 CTE，即在视图定义查询中，CTE 必须至少被引用一次。
IMMV 的定义查询中允许使用 DISTINCT。假设在包含重复元组的基表上使用 DISTINCT 定义的 IMMV。当从基表中删除元组时，当且仅当元组数量变为零时，才会删除视图中的元组。此外，当将元组插入基表时，只有当相同的元组不存在于视图中时，才会将元组插入视图中。
当基表被 truncate 时，如果视图定义查询不包含没有 GROUP BY 子句的聚合，则 IMMV 也会被截断，内容将变为空。没有 GROUP BY 子句的聚合视图始终只有一行。因此，在这种情况下，如果基表被截断，则 IMMV 只会被刷新，而不会被截断。
更多信息，请参见官方文档。