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

本文涉及的产品
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
简介: PostgreSQL不支持物化视图增量更新,需要定期执行REFRESH MATERIALIZED VIEW命令刷新物化视图。Incremental View Maintenance (IVM)是一种使物化视图保持最新的方法,其中只计算增量更改并将其应用于视图,而不是REFRESH MATERIALIZED VIEW那样从头开始重新计算内容。当只更改视图的一小部分时,IVM可以比重新计算更高效地更新物化视图。

一、物化视图简介

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

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

创建物化视图的语法:

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 子句中的子查询、窗口函数、HAVINGORDER BYLIMIT/OFFSETUNION/INTERSECT/EXCEPTDISTINCT ONTABLESAMPLEVALUESFOR 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限制

不支持包含聚合函数或DISTINCTWITH 查询。

不允许递归查询 (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 刷新物化视图并启用立即维护。


相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
7月前
|
存储 关系型数据库 数据库
postgresql|数据库|提升查询性能的物化视图解析
postgresql|数据库|提升查询性能的物化视图解析
706 0
|
存储 NoSQL 关系型数据库
PostgreSQL列存扩展hydra简单测试
Hydra是一款PostgreSQL的扩展,为PostgreSQL增加了列存引擎,使得PostgreSQL的olap性能大幅提升,本文介绍Hydra基本的使用方法。
|
3月前
|
SQL 关系型数据库 C语言
PostgreSQL SQL扩展 ---- C语言函数(三)
可以用C(或者与C兼容,比如C++)语言编写用户自定义函数(User-defined functions)。这些函数被编译到动态可加载目标文件(也称为共享库)中并被守护进程加载到服务中。“C语言函数”与“内部函数”的区别就在于动态加载这个特性,二者的实际编码约定本质上是相同的(因此,标准的内部函数库为用户自定义C语言函数提供了丰富的示例代码)
|
6月前
|
运维 Cloud Native 关系型数据库
云原生数据仓库AnalyticDB产品使用合集之PostgreSQL版是否直接支持实时物化视图
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
132 3
|
关系型数据库 PostgreSQL
PostgreSQL pg_orphaned扩展
由于种种原因,PostgreSQL可能会产生一些孤儿文件,这些文件会占用磁盘空间,手工查找费时费力还容易出错,pg_orphaned扩展很好的解决了这个问题。
|
6月前
|
SQL 关系型数据库 PostgreSQL
【sql】PostgreSQL物化视图表使用案例
【sql】PostgreSQL物化视图表使用案例
55 0
|
SQL 关系型数据库 Go
《增强你的PostgreSQL:最佳扩展和插件推荐》
《增强你的PostgreSQL:最佳扩展和插件推荐》
983 0
|
SQL Oracle 关系型数据库
物化视图(Oracle与PostgreSQL对比)
物化视图(Oracle与PostgreSQL对比)
|
7月前
|
SQL 运维 关系型数据库
基于AnalyticDB PostgreSQL的实时物化视图研发实践
AnalyticDB PostgreSQL版提供了实时物化视图功能,相较于普通(非实时)物化视图,实时物化视图无需手动调用刷新命令,即可实现数据更新时自动同步刷新物化视图。当基表发生变化时,构建在基表上的实时物化视图将会自动更新。AnalyticDB PostgreSQL企业数据智能平台是构建数据智能的全流程平台,提供可视化实时任务开发 + 实时数据洞察,让您轻松平移离线任务,使用SQL和简单配置即可完成整个实时数仓的搭建。
144004 8
|
7月前
|
SQL 关系型数据库 C语言
PostgreSQL【应用 03】Docker部署的PostgreSQL扩展SQL之C语言函数(编写、编译、载入)计算向量余弦距离实例分享
PostgreSQL【应用 03】Docker部署的PostgreSQL扩展SQL之C语言函数(编写、编译、载入)计算向量余弦距离实例分享
99 0