(文档)第131讲:物化视图管理利器 ——pg_ivm插件

pg_ivm概述

Pg_ivm扩展是Postgresql数据库的一个插件，增量视图维护(IVM)是一种使物化视图保持最新状态的方法，该方法仅计算并应用视图中的增量更改，而不是像REFRESH MATERIALIZED VIEW那样从头开始重新计算内容。当只有视图的一小部分发生更改时，IVM可以比重新计算更高效地更新物化视图。

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

pg_ivm触发器介绍

pg_ivm提供了一种立即维护方式，即当基表被修改时，物化视图会在AFTER触发器中立即更新。

Pg_ivm触发器列表：

pgivm."IVM_immediate_before"()

pgivm."IVM_immediate_maintenance"()

pgivm."IVM_prevent_immv_change"()

pg_ivm函数介绍

pg_ivm安装

1、下载

https://github.com/sraoss/pg_ivm

2、安装

cd pg_ivm-main

make install

3、修改配置文件postgresql.conf

shared_preload_libraries = ‘pg_ivm'

4、安装插件

CREATE EXTENSION IF NOT EXISTS pg_ivm;

Pg_ivm使用技巧

• 创建IMMV(Incrementally Maintainable Materialized View )

1、创建IMMV

SELECT pgivm.create_immv('emp_mv', 'SELECT * FROM emp');

2、更新基表

INSERT INTO emp (empno,ename,deptno) VALUES (1122,’CUUG’,20);

3、查看物化视图，验证是否更新

SELECT * FROM emp_mv;

注意：如果基表中包含有主键约束，那么immv视图也会自动创建索引，如果没有索引，immv视图在维护时会占比较长的时间。

另外在维护immv时，会把search_path临时切换为pg_catalog, pg_temp。

传统物化视图VS增量物化视图

• 传统物化视图更新

1、创建物化视图

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

2、更新基表

UPDATE pgbench_accounts SET abalance = 1000 WHERE aid = 1;

Time: 9.052 ms

3、刷新物化视图，注意所需时间

test=# REFRESH MATERIALIZED VIEW mv_normal ;

REFRESH MATERIALIZED VIEW

Time: 20575.721 ms (00:20.576)

• IMMV更新

1、创建IMMV

test=# SELECT pgivm.create_immv('immv',

'SELECT a.aid, b.bid, a.abalance, b.bbalance

FROM pgbench_accounts a JOIN pgbench_branches b USING(bid)');

2、更新基表

UPDATE pgbench_accounts SET abalance = 1234 WHERE aid = 1;

Time: 15.448 ms

3、查看物化视图是否已经更新

test=# SELECT * FROM immv WHERE aid = 1;

aid | bid | abalance | bbalance

-----+-----+----------+----------

1 | 1 | 1234 | 0

IMMV与索引

为了实现高效的IVM，需要在IMMV上建立适当的索引，因为我们需要查找IMMV中需要更新的元组。如果没有索引，将会耗费大量时间。因此，当通过create_immv函数创建IMMV时，如果可能的话，会自动为其创建一个唯一索引。如果视图定义查询中包含GROUP BY子句，则会对GROUP BY表达式中的列创建唯一索引。

此外，如果视图包含DISTINCT子句，则会对目标列表中的所有列创建唯一索引。否则，如果IMMV包含目标列表中其基表的所有主键属性，则会对这些属性创建唯一索引。在其他情况下，不会创建索引。

在前面的示例中，我们在"immv"表的aid和bid列上创建了一个唯一索引"immv_index"，这使得视图的更新速度得以提升。删除此索引会导致视图更新所需的时间变长。

带有聚合函数的IMMV

支持的聚合函数有count、sum、avg、min和max。目前，仅支持内置聚合函数，无法使用用户定义的聚合函数。

当创建包含聚合的IMMV时，目标列表中会自动添加一些名称以__ivm开头的额外列。__ivm_count__包含每个组中聚合的元组数量。此外，为了维护聚合值，还会为每个聚合值列添加多个额外列。例如，为了维护平均值，会添加名为__ivm_count_avg__和__ivm_sum_avg__的列。

当基础表被修改时，将使用旧的聚合值和IMMV中存储的相关额外列的值来增量计算新的聚合值。请注意，对于最小值或最大值，当从基表中删除包含当前最小值或最大值的元组时，可以根据受影响的组从基表重新计算新值。因此，更新包含这些函数的IMMV可能需要很长时间

• 聚合函数的支持

1、创建IMMV

test=# SELECT pgivm.create_immv('immv_agg',

'SELECT bid, count(*), sum(abalance), avg(abalance)

FROM pgbench_accounts JOIN pgbench_branches USING(bid) GROUP BY bid');

2、查看修改前的值

test=# SELECT bid, count, sum, avg FROM immv_agg WHERE bid = 42;

bid | count | sum | avg

-----+--------+-------+------------------------

42 | 100000 | 38774 | 0.38774000000000000000

(1 row)

Time: 3.123 ms

3、更新表

test=# UPDATE pgbench_accounts SET abalance = abalance + 1000 WHERE aid = 4112345 AND bid = 42;

4、IMMV数据自动更新

test=# SELECT bid, count, sum, avg FROM immv_agg WHERE bid = 42;

bid | count | sum | avg

-----+--------+-------+------------------------

42 | 100000 | 39774 | 0.39774000000000000000

(1 row)

Time: 1.987 ms

IMMV维护

1、删除IMMV

DROP TABLE immv;

2、IMMV改名

ALTER TABLE immv_agg RENAME TO immv_agg2;

IMMV所支持的操作

PostgreSQL中文社区认证

CUUG与工信部人才交流中心合作，推出PostgreSQL初/中/高级证书培训考证服务，证书中明确指定适用于信息技术应用创新人才岗位能力评定要求。