pg_ivm

pg_ivm模块为PostgreSQL提供增量视图维护（IVM）功能。
该扩展兼容PostgreSQL 13、14、15、16和17版本。

描述

增量视图维护（Incremental View Maintenance, IVM） 是一种保持物化视图最新的方法，它仅计算和应用增量变化，而不是像REFRESH MATERIALIZED VIEW那样从头开始重新计算视图的内容。当视图中只有小部分发生变化时，IVM比重新计算更高效地更新物化视图。

关于视图维护的时机，有两种方法：即时维护和延迟维护。在即时维护中，视图在修改其基础表的同一事务中更新。在延迟维护中，视图在事务提交后更新，例如，当视图被访问时，响应如 REFRESH MATERIALIZED VIEW的用户命令，或在后台定期更新等。pg_ivm提供了一种即时维护方式，在基础表修改时，物化视图会在 AFTER 触发器中立即更新。

我们将支持IVM的物化视图称为增量可维护物化视图（Incrementally Maintainable Materialized View, IMMV）。要创建IMMV，您需要调用create_immv函数，并提供一个关系名和视图定义查询。例如：

SELECT create_immv('myview', 'SELECT * FROM mytab');

创建一个名为'myview'的IMMV，定义为SELECT * FROM mytab。这相当于以下命令，用于创建一个普通的物化视图：

CREATE MATERIALIZED VIEW myview AS SELECT * FROM mytab;

当IMMV被创建时，系统会自动创建一些触发器，以确保当基础表被修改时，视图的内容会立即更新。

postgres=# SELECT create_immv('m', 'SELECT * FROM t0');
NOTICE:  could not create an index on immv "m" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv 
-------------
           3
(1 row)

postgres=# SELECT * FROM m;
 i
---
 1
 2
 3
(3 rows)

postgres=# INSERT INTO t0 VALUES (4);
INSERT 0 1
postgres=# SELECT * FROM m; -- automatically updated
 i
---
 1
 2
 3
 4
(4 rows)

请注意，如果您使用的是PostgreSQL 17或更高版本，在IMMV的自动维护过程中，search_path会临时更改为pg_catalog, pg_temp。

安装

要安装pg_ivm，请在模块目录中执行以下命令：

make install

如果您是通过rpm或deb安装PostgreSQL，您需要安装开发包（例如，postgresql14-devel 或 postgresql-server-dev-14）。

重要提示： 如果您希望在非默认或自定义构建的PostgreSQL上使用pg_ivm，不要忘记设置 PG_CONFIG 变量（make PG_CONFIG=...）或将 pg_config 命令的路径添加到 PATH 中。更多信息请参见此处。

然后执行CREATE EXTENSION命令。

CREATE EXTENSION pg_ivm;

RPM包和yum源

pg_ivm的RPM包可以从PostgreSQL yum repository获取。有关详细信息，请参阅 instruction 。请注意，我们不是该yum仓库的维护者，仓库中的pg_ivm RPM包可能并不总是最新版本。

对象

安装pg_ivm后，以下对象将被创建：

函数

create_immv

使用create_immv函数来创建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中的行数。

当IMMV被创建时，系统会自动创建一些触发器，确保当基础表被修改时，视图的内容能够立即更新。此外，如果可能，系统还会自动在IMMV上创建一个唯一索引。如果视图定义查询包含GROUP BY子句，则会在GROUP BY表达式的列上创建唯一索引。如果视图包含DISTINCT子句，则会在目标列表中的所有列上创建唯一索引。否则，如果IMMV的目标列表中包含其基础表的所有主键属性，则会在这些属性上创建唯一索引。在其他情况下，则不会创建索引。

refresh_immv

使用refresh_immv函数来更新IMMV。

refresh_immv(immv_name text, with_data bool) RETURNS bigint

refresh_immv完全替换IMMV的内容，类似于REFRESH MATERIALIZED VIEW命令对物化视图的作用。要执行此函数，您必须是IMMV的所有者（对于PostgreSQL 16或更早版本），或者在IMMV上拥有 MAINTAIN 权限（对于PostgreSQL 17或更高版本）。旧的内容将被丢弃。

with_data标志对应于REFRESH MATERIALIZED VIEW命令的WITH [NO] DATA选项。如果with_data为 true，则会执行基础查询以提供新的数据，如果IMMV为空，则会创建用于维护视图的触发器。此外，如果可能并且视图还没有唯一索引，则会为IMMV创建唯一索引。如果with_data为 false，则不会生成新数据，IMMV将变为空视图，且触发器将从IMMV中删除。请注意，尽管IMMV为空，但仍然可以扫描它。未来的行为可能会发生变化，当扫描一个空的IMMV时会抛出错误。

请注意，如果您使用的是PostgreSQL 17或更高版本，在执行refresh_immv时，search_path会临时更改为pg_catalog, pg_temp。

get_immv_def

get_immv_def用于重建IMMV的基础 SELECT 命令。（这是一种反编译重建，并非命令的原始文本。）

get_immv_def(immv regclass) RETURNS text

IMMV metadata catalog

catalogpg_ivm_immv存储IMMV信息.

例子

一般来说，IMMVs 提供比REFRESH MATERIALIZED VIEW更快的更新速度，但代价是基础表的更新会变得较慢。基础表更新变慢是因为会触发相应的触发器，每次修改语句都会在触发器中更新IMMV。

例如，假设有一个普通的物化视图，定义如下：

test=# CREATE MATERIALIZED VIEW mv_normal AS
        SELECT a.aid, b.bid, a.abalance, b.bbalance
        FROM pgbench_accounts a JOIN pgbench_branches b USING(bid);
SELECT 10000000

更新此物化视图基表中的元组速度很快，但在此视图上执行REFRESH MATERIALIZED VIEW命令却需要很长时间。

test=# UPDATE pgbench_accounts SET abalance = 1000 WHERE aid = 1;
UPDATE 1
Time: 9.052 ms

test=# REFRESH MATERIALIZED VIEW mv_normal ;
REFRESH MATERIALIZED VIEW
Time: 20575.721 ms (00:20.576)

另一方面，在使用如下相同的视图定义创建IMMV之后：

test=# SELECT create_immv('immv',
        'SELECT a.aid, b.bid, a.abalance, b.bbalance
         FROM pgbench_accounts a JOIN pgbench_branches b USING(bid)');
NOTICE:  created index "immv_index" on immv "immv"
 create_immv 
-------------
    10000000
(1 row)

更新基表中的一个元组所花费的时间比普通视图要长，但其内容会自动更新，而且这一更新过程比REFRESH MATERIALIZED VIEW命令要快。

test=# UPDATE pgbench_accounts SET abalance = 1234 WHERE aid = 1;
UPDATE 1
Time: 15.448 ms

test=# SELECT * FROM immv WHERE aid = 1;
 aid | bid | abalance | bbalance 
-----+-----+----------+----------
   1 |   1 |     1234 |        0
(1 row)

为实现高效的IVM，在IMMV上建立合适的索引是很有必要的，因为我们需要在IMMV中查找要更新的元组。如果没有索引，那将会耗费很长时间。
因此，当通过create_immv函数创建IMMV时，若有可能会自动在其上创建一个唯一索引。如果视图定义查询包含GROUP BY子句，就会在GROUP BY表达式涉及的列上创建一个唯一索引。而且，如果视图有DISTINCT子句，会在目标列表的所有列上创建一个唯一索引。否则，如果IMMV在其目标列表中包含了基表的所有主键属性，就会在这些属性上创建一个唯一索引。在其他情况下，则不会创建索引。
在前面的示例中，会在immv的aid和bid列上创建一个名为immv_index的唯一索引，这使得视图能够快速更新。删除这个索引会使更新视图花费更长的时间。

test=# DROP INDEX immv_index;
DROP INDEX

test=# UPDATE pgbench_accounts SET abalance = 9876 WHERE aid = 1;
UPDATE 1
Time: 3224.741 ms (00:03.225)

视图支持的定义和限制

目前，IMMV的视图定义可以包含内连接（inner joins）、DISTINCT 子句、一些内置聚合函数、FROM子句中的简单子查询、EXISTS 子查询以及简单的公共表达式CTE（即 WITH查询）。支持包括自连接（self-join）在内的内连接，但不支持外连接。支持的聚合函数有 count、sum、avg、min 和 max。其他聚合函数、包含聚合函数或 DISTINCT子句的子查询、FROM子句之外的子查询、窗口函数、HAVING子句、ORDER BY子句、LIMIT/OFFSET、UNION/INTERSECT/EXCEPT、DISTINCT ON、TABLESAMPLE、VALUES以及 FOR UPDATE/SHARE均不能在视图定义中使用。

基表必须是普通表。视图、物化视图、继承父表、分区表、分区以及外部表均不能使用。
任何系统列都不能包含在视图定义查询中。
目标列表中不能包含名称以__ivm_开头的列。
视图的目标列表中所使用的数据类型必须具有用于访问方法btree的默认操作符类。例如，json、xml或point类型不能出现在目标列表中。
不支持逻辑复制，也就是说，即便发布者节点处的基表被修改了，基于这些基表定义的订阅者节点处的IMMV也不会更新。

注意事项

聚合函数

支持的聚合函数有count（计数）、sum（求和）、avg（求平均）、min（求最小值）以及 max（求最大值）。目前，仅支持内置的聚合函数，用户自定义的聚合函数无法使用。
当创建一个包含聚合函数的IMMV时，一些名称以__ivm开头的额外列会自动添加到目标列表中。__ivm_count__列包含了每个分组中聚合的元组数。此外，为了维护聚合值，会针对每个聚合值所在的列添加不止一个额外列。例如，会添加诸如__ivm_count_avg__和__ivm_sum_avg__这样的列来维护平均值。当基表被修改时，新的聚合值会利用旧的聚合值以及IMMV中存储的相关额外列的值来增量计算。
需要注意的是，对于min（求最小值）或max（求最大值）函数而言，当从基表中删除包含当前最小值或最大值的元组时，可能需要根据受影响的分组从基表中重新计算新的值。因此，更新包含这些函数的IMMV可能会耗费较长时间。
同样需要注意的是，在IMMV中对real（float4）类型或double precision（float8）类型使用sum（求和）或avg（求平均）函数是不安全的，因为由于这些类型的精度有限，IMMV中的聚合值可能会与从基表计算得出的结果不一致。为避免这个问题，请改用numeric类型。

聚合函数的限制

如果有GROUP BY子句，在GROUP BY中指定的表达式必须出现在目标列表中。这是识别IMMV中要更新的元组的方式。这些属性被用作在IMMV中查找元组的扫描键，因此为实现高效的IVM，需要在它们上面创建索引。
目标列表不能包含其中含有聚合函数的表达式。

子查询

FROM子句中的简单子查询以及WHERE子句中的EXISTS子查询是受支持的。

子查询的限制

支持使用EXISTS的子查询以及FROM子句中的简单子查询。带有AND之外条件的EXISTS子查询以及目标列表中的子查询不受支持。EXISTS子查询仅在WHERE子句中受支持，在目标列表中则不受支持。
如果EXISTS包含引用了外层查询中表的列的列，那么这些列必须包含在目标列表中。
包含聚合函数或DISTINCT的子查询不受支持。

CTE

支持简单的CTE(WITH语句)。

CTE的限制

包含聚合函数或DISTINCT的WITH查询不受支持。
递归查询（WITH RECURSIVE）是不被允许的。未被引用的公共表达式（CTE）也是不被允许的，也就是说，在视图定义查询中，一个公共表达式（CTE）必须至少被引用一次。

DISTINCT

在IMMV的定义查询中允许使用DISTINCT（去重）。假设基于一个包含重复元组的基表使用DISTINCT定义了一个IMMV。当从基表中删除元组时，当且仅当该元组的重复次数变为零时，视图中的相应元组才会被删除。而且，当向基表中插入元组时，只有当相同元组在视图中尚不存在时，才会将该元组插入到视图中。
从物理层面来讲，使用DISTINCT定义的IMMV在去除重复元组后包含相应元组，并且每个元组的重复次数会存储在一个名为__ivm_count__的额外列中，该列是在创建此类IMMV时添加的。

TRUNCATE

当对一个基表执行truncate操作时，如果视图定义查询不包含不带GROUP BY子句的聚合函数，那么IMMV也会被截断，其内容将变为空。不带GROUP BY子句的聚合视图始终只有一行数据。因此，在这类情况下，如果对基表进行截断操作，IMMV会直接进行刷新，而不是被截断。

并行事务

假设基于两个基表定义了一个IMMV，且每个表在不同的并发事务中同时被修改。在率先提交的事务中，IMMV可以仅考虑该事务中发生的变更来进行更新。另一方面，为了在稍后提交的事务中正确地更新IMMV，我们需要知晓两个事务中发生的变更情况。出于这个原因，在读已提交（READ COMMITTED）模式下，一旦基表被修改，就会立即对IMMV持有排他锁（ExclusiveLock），以确保在前一个事务提交后，IMMV能在后一个事务中得到更新。在可重复读（REPEATABLE READ）或可串行化（SERIALIZABLE）模式下，如果锁获取失败，会立即引发错误，因为在这些模式下，其他事务中发生的任何变更都是不可见的，在这种情况下IMMV无法被正确更新。不过，作为一种例外情况，如果IMMV只有一个基表，并且不使用DISTINCT或GROUP BY，且该表是通过INSERT语句进行修改的，那么对IMMV持有的锁则为行排他锁（RowExclusiveLock）。

行级安全

如果某些基表设有行级安全策略，那么对于物化视图所有者不可见的行将会从结果中排除。此外，在对视图进行增量维护时，此类行同样也会被排除。不过，如果在创建物化视图之后定义了新的策略或者对已有策略进行了更改，新策略将不会应用到视图内容上。要应用新策略，就需要重新创建IMMV。

如何启动即时维护

当我们希望IMMV保持最新状态，且基表只有一小部分不常被修改时，即时IVM是有效的。由于即时维护存在开销，当基表频繁被修改时，IVM就没那么有效了。而且，当基表的大部分内容被修改或者有大量数据插入到基表中时，即时IVM也没什么效果，并且其维护成本可能会比从头刷新视图的成本还要高。
在这种情况下，我们可以使用refresh_immv函数，并将with_data = false，以便在修改基表之前禁用即时维护。在对基表进行修改之后，再使用with_data = true来调用refresh_immv函数，从而刷新视图数据并启用即时维护。

作者

IVM开发小组

• https://github.com/yugo-n
• https://github.com/thoshiai

License

PostgreSQL License

Copyright

• Portions Copyright (c) 1996-2022, PostgreSQL Global Development Group
• Portions Copyright (c) 2022, IVM Development Group