一、物化视图简介

与常规视图不同，常规视图是每次访问时运行查询的虚拟表，而物化视图是预先计算的数据库表，用于存储查询结果，并且仅在直接刷新查询结果时进行更新。与在基础表上执行等价查询相比，物化视图可以显著提高查询速度，尤其是对于更复杂的查询。

物化视图通常用于以下场景：查询性能至关重要，频繁对大型复杂数据集执行查询，并且要查询的数据不会频繁更改。在查询数据需要频繁计算以进行报告或分析的多个聚集、联表等操作的情况下，您可以使用预计算的物化视图有效地提高性能并缩短执行时间。

创建物化视图的语法：

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] view_name
[(column_name [, ...])][ USING method ]AS query
[ WITH [ NO ] DATA ]

Hydra列存扩展也支持物化视图，结合Hydra扩展（columnar表）可以创建基于列存的物化视图。

CREATE MATERIALIZED VIEW sales_summary
  USING columnar
ASSELECT    date_trunc('month', order_date)AS month,    product_id,    SUM(quantity)AS total_quantity,    SUM(quantity * price)AS total_sales
FROM    sales
GROUPBY    date_trunc('month', order_date),    product_id;

Incremental View Maintenance (IVM)是一种使物化视图保持最新的方法，其中只计算增量更改并将其应用于视图，而不是REFRESH MATERIALIZED VIEW那样从头开始重新计算内容。当只更改视图的一小部分时，IVM可以比重新计算更高效地更新物化视图。

关于物化视图的更新，有两种方法：立即更新和延迟更新。立即更新是在修改物化视图的基表的同一事务中更新物化视图。延迟更新是在事务提交后更新物化视图，比如，当要访问物化视图时，使用REFRESH MATERIALIZED VIEW命令刷新物化视图，或在后台定期更新。pg_ivm提供了一种即时维护物化视图的方法，当修改基表时，物化视图在AFTER触发器中立即更新。

物化视图的最佳实践：

刷新物化视图：刷新物化视图的频率和方法是影响视图整体有效性的关键因素。由于刷新物化视图可能是一项资源密集型操作，因此它更适合不频繁更新的数据。
监视磁盘空间和性能：物化视图可能会占用大量磁盘空间，因此必须监视它们的大小并删除未使用的视图以释放空间。您还应该监视它们的刷新性能，以确保它们不会对整体性能产生负面影响。
添加索引以优化您的查询：物化视图作为常规表存储在PostgreSQL中，从而提供了一个明显的优势。这意味着它们可以充分利用索引技术的优势，从而提高大数据集的性能和处理效率。

二、pg_ivm安装

git clone https://github.com/sraoss/pg_ivm.git
cd pg_ivm
makemake install

三、pg_ivm使用

使用pg_ivm前，需要先创建扩展

CREATE EXTENSION IF NOT EXISTS pg_ivm;

我们将支持IVM的物化视图称为Incrementally Maintainable Materialized View (IMMV)。创建IMMV必须调用 create_immv 函数，该函数需要提供物化视图名、物化视图定义查询两个参数，举例如下：

SELECT create_immv('sales_test','SELECT * FROM sales');

示例创建一个名为sales_test的物化视图，视图的定义查询为：SELECT * FROM sales，对应的创建普通物化视图的语句如下：

CREATE MATERIALIZED VIEW sales_test ASSELECT*FROM sales;

IMMV被创建后，会自动创建一些触发器，以便物化视图的基表被修改后立即更新物化视图的内容。

postgres=# SELECT create_immv('m','SELECT * FROM t0');NOTICE:  could notcreate 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
---123(3 rows)postgres=# INSERTINTO t0 VALUES(4);INSERT01postgres=# SELECT*FROM m;-- automatically updated i
---1234(4 rows)

四、IMMV与columna列存

通过设置缺省表访问方法，可以创建使用columna列存的 IMMV。如果基表也使用了columna列存，在创建IMMV前需要临时禁止并行：

SET default_table_access_method ='columnar';SET max_parallel_workers =1;SELECT create_immv('sales_test','SELECT * FROM sales');

IMMV创建成功后，恢复缺省设置。 (这里的设置是会话级的，因此不会影响其他连接，中止会话并创建新会话也会恢复缺省设置。)

SET max_parallel_workers = DEFAULT;SET default_table_access_method = DEFAULT;

五、常用函数

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的所有者才能执行此函数。原有的内容将被清除。

with_data标志对应REFRESH MATERIALIZED VIEW 命令的WITH [NO] DATA 选项。如果with_data为true，执行备份查询以提供新数据，并且如果IMMV未填充，则创建用于维护视图的触发器。此外，如果可能，还会为IMMV创建唯一的索引。如果with_data为false，则不会生成新数据，IMMV将变为未填充，并且触发器将从IMMV中删除。请注意，未填充的IMMV仍然是可扫描的，尽管结果为空。后续可能会更改此行为，以便在扫描未填充的IMMV时引发错误。

get_immv_def

get_immv_def 重建生成IMMV的SELECT命令的文本。 (这是反编译重建，而不是命令的原始文本。)

get_immv_def(immv regclass) RETURNS text

IMMV 元数据视图

pg_ivm_immv 存贮 IMMV 信息。

名称	类型	描述
immvrelid	regclass	IMMV OID
viewdef	text	视图定义语句
ispopulated	bool	如果当前需要填充视图数据则为true

六、更多例子

通常，IMMVs比 REFRESH MATERIALIZED VIEW 更新的更快，但代价是更新基表会更慢，原因是IVVM为了更新物化视图在基表上使用了触发器。

假设定义了一个普通的物化视图：

test=# CREATE MATERIALIZED VIEW mv_normal ASSELECT a.aid, b.bid, a.abalance, b.bbalanceFROM pgbench_accounts a JOIN pgbench_branches b USING(bid);SELECT10000000

在这个物化视图的基表上更新一个元组很快，但使用 REFRESH MATERIALIZED VIEW 命令刷新视图需要很长时间：

test=# UPDATE pgbench_accounts SET abalance =1000WHERE aid =1;UPDATE1Time: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)

在IMMV物化视图的基表上更新一个元组比使用REFRESH MATERIALIZED VIEW 命令创建的普通物化视图的基表上更新元组要花费更多的时间，但是物化视图的内容自动更新，比使用REFRESH MATERIALIZED VIEW 快很多。

test=# UPDATE pgbench_accounts SET abalance =1234WHERE aid =1;UPDATE1Time:15.448 ms
test=# SELECT*FROM immv WHERE aid =1; aid | bid | abalance | bbalance
-----+-----+----------+----------1|1|1234|0(1 row)

在IMMV上创建适当的索引对提高IVM的性能是有帮助的，因为我们需要在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 =9876WHERE aid =1;UPDATE1Time:3224.741 ms (00:03.225)

七、支持的视图定义和限制

目前，IMMV的视图定义可包含inner joins、DISTINCT子句、一些内建的聚合函数、在 FROM 子句中的简单子查询、简单的 CTE (WITH 查询)。支持包括自联接在内的内联接，但不支持外联接。支持的聚合函数有：count、sum、avg、min、max。在视图定义中不能使用其余的聚合函数、包含聚合陈函数或 DISTINCT 子句的子查询、不在 FROM 子句中的子查询、窗口函数、HAVING、 ORDER BY、 LIMIT/OFFSET、 UNION/INTERSECT/EXCEPT、 DISTINCT ON、 TABLESAMPLE、VALUES、FOR UPDATE/SHARE 。

基表必须是普通表，不能使用视图、物化视图、继承父表、分区表、分区、外部表。

目标列表不能包含以 __ivm_开头的系统列。

不支持逻辑复制，也就是说，即使修改发布方节点上的基表，这些基表上定义的订阅方节点上的IMMV也不会更新。

八、限制

聚合函数

支持的聚合函数有： count, sum, avg, min, and max。目前，仅支持内置聚合函数，不能使用用户定义的聚合。

当包含聚合函数的IMMV被创建，一些名称以__ivm 开头的列将自动添加到目标列表中。__ivm_count__包含在每个组中聚合的元组数量。此外，为了维护聚合值，为聚合值的每一列添加了多个额外的列。比如，添加类似__ivm_count_avg__ 和 __ivm_sum_avg__ 的列名以维护平均值。当基表被修改，使用保存在IMMV额外列里的旧聚合值增量计算新的聚合值。

有关 min 或者 max需要注意，当从基表中删除包含当前最小值或最大值的元组时，可以从受影响组的基表重计算新值。因此，更新包含这些功能的IMMV可能需要很长时间。

别外需要注意的是，在IMMV中使用对real (float4) 类型或double precision (float8) 类型使用sum or avg 是不安全的，因为由于这些类型的精度有限，IMMV中的聚合值可能与从基表计算的结果不一致。为避免此类问题，使用numeric 类型替代。

聚合限制

如果我们有 GROUP BY 子句，在 GROUP BY中指定的表达式必须出现在目标列表中。这就是如何标识要在IMMV中更新的元组。这些属性用作在IMMV中搜索元组的扫描键，因此高效的IVM必须要有索引。

目标列表不能含有包含聚合的表达式。

子查询

支持在 FROM 子句里的简单子查询。

子查询限制

仅支持 FROM 子句里的子查询。不支持目标列表或WHERE子句中的子查询。

不支持包含聚合函数或 DISTINCT 的子查询。

CTE

支持简单CTEs (WITH queries)。

CTEs限制

不支持包含聚合函数或DISTINCT 的WITH 查询。

不允许递归查询 (WITH RECURSIVE) 。也不允许使用未引用的CTE，即在视图定义查询中必须至少引用一次CTE。

DISTINCT

IMMV的定义查询支持DISTINCT 。假设一个带DISTINCT的IMMV定义的基表包含有重复元组。当从基表中删除元组时，当且仅当元组的重复数变为零时，视图中的元组才被删除。此外，当元组被插入到基表中时，只有在视图中不存在相同的元组时，元组才会被插入到视图中。

物理上，使用 DISTINCT定义的IMMV在消除重复项后包含元组，每个元组的多重性存储在一个名为__ivm_count__的额外列中，该列是在创建此类IMMV时添加的。

TRUNCATE

当基表被truncate时，如果视图定义查询不包含不带GROUP BY子句的聚合，则IMMV也被截断，并且内容变为空。没有GROUP BY子句的聚合视图始终有一行。因此，在这种情况下，如果基表被truncate，IMMV只会被刷新，而不是被截断。

并发事务

假设在两个基表上定义了IMMV，并且每个表在不同的并发事务中被同时修改。在先被提交的事务中，IMMV可被更新，因为这里只需要考虑发生在本事务中的改变。另一方面，为了正确更新在稍后提交事务里的IMMV，我们需要知道发生在每个事务里的变化。因此，在读提交模式下修改基表后，IMMV立即持有ExclusiveLock，以确保在前一个事务提交后在后一个事务中更新IMMV。在 REPEATABLE READ 或者 SERIALIZABLE 模式，如果获取锁失败，则会立即引发错误，因为其他事务中发生的任何更改在这些模式下都不可见，并且IMMV在这种情况下无法正确更新。当然也有例外：如果IMMV只有一个基表并且不使用DISTINCT或者GROUP BY，并且表是被 INSERT 修改的，IMMV持有的锁是 RowExclusiveLock。

行级安全

如果某些基表具有行级安全策略，则对物化视图所有者不可见的行将从结果中排除。此外，当以增量方式维护视图时，也会排除此类行。但是，如果在创建物化视图后定义了新策略或更改了策略，则新策略将不会应用于视图内容。要应用新策略，您需要重新创建IMMV。

如何禁止或启用立即维护

当我们想要使IMMV保持最新并且不经常修改基表或者仅修改基表的一小部分时，IVM是有效的。由于即时维护的开销，当频繁修改基表时，IVM不起作用。此外，当修改基表的很大一部分或将大量数据插入基表时，IVM是无效的，维护成本可能比从头开始刷新更大。

当遇到此类情形时，我们可以在修改基表前使用 refesh_immv 函数并带上 with_data = false 禁止立即维护。等基表修改完成后，调用 refresh_immv 并带上with_data = true 刷新物化视图并启用立即维护。

PostgreSQL物化视图增量更新扩展 -- pg_ivm