PostgreSQL-物化视图进阶指南：使用pg_ivm实现增量刷新

原创数据蛙 All China Database Union 2024-10-30

668

概述

物化视图（Materialized View）在PostgreSQL中是一种重要的数据库对象，能够大幅度提升查询性能，因而在生产环境中会被经常用到。物化视图是将查询结果存储在磁盘上，形成一个静态的表，从而避免每次查询时都重新计算数据。以下是将介绍关于物化视图的创建与使用，并使用 pg_ivm 插件进行增量刷新。

常用场景：对于执行成本较高且更新频率较低的查询，可以优先考虑使用物化视图，比较适合在数据量大、查询较慢的情况下使用，比如大数据统计和汇总报表，再通过合理的刷新策略（如增量刷新）和索引优化，可以实现高效的数据查询与处理。

创建物化视图

在 psql 中查看创建物化视图的相关语法：

postgres=# \h CREATE MATERIALIZED VIEW
Command:     CREATE MATERIALIZED VIEW
Description: define a new materialized view
Syntax:
CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name
    [ (column_name [, ...] ) ]
    [ USING method ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    AS query
    [ WITH [ NO ] DATA ]
复制

根据已有的表创建一个物化视图表，创建表的时间不加载数据。

postgres=# create materialized view mv_list_table as select product_type,count(1) as cnt from test_list_table group by product_type with no data;
CREATE MATERIALIZED VIEW
Time: 298.247 ms

postgres=# select * from mv_list_table ;
ERROR:  materialized view "mv_list_table" has not been populated
HINT:  Use the REFRESH MATERIALIZED VIEW command.
Time: 0.875 ms
复制

刚创建的物化视图使用的是 with no data，所以要刷新物化视图之后才能正常查询数据出来，执行以下是刷新物化视图的方法：

postgres=# refresh materialized view mv_list_table ;
REFRESH MATERIALIZED VIEW
Time: 1052.043 ms (00:01.052)

postgres=# select * from mv_list_table ;
 product_type |   cnt
--------------+---------
 A            | 1000000
 B            | 1000000
 C            | 1000000
 D            | 1000000
(4 rows)

Time: 0.887 ms

# 不使用物化视图
postgres=# select product_type,count(1) as cnt from test_list_table group by product_type;
 product_type |   cnt
--------------+---------
 A            | 1000000
 B            | 1000000
 C            | 1000000
 D            | 1000000
(4 rows)

Time: 533.630 ms

postgres=# insert into test_list_table values(generate_series(4000001,5000000),'B');
INSERT 0 1000000
Time: 6220.258 ms (00:06.220)

postgres=# select product_type,count(1) as cnt from test_list_table group by product_type;
 product_type |   cnt
--------------+---------
 A            | 1000000
 B            | 2000000
 C            | 1000000
 D            | 1000000
(4 rows)

Time: 1088.660 ms (00:01.089)

postgres=# select * from mv_list_table ;
 product_type |   cnt
--------------+---------
 A            | 1000000
 B            | 1000000
 C            | 1000000
 D            | 1000000
(4 rows)

Time: 0.503 ms

postgres=# refresh materialized view mv_list_table ;
REFRESH MATERIALIZED VIEW
Time: 1198.456 ms (00:01.198)
postgres=# select * from mv_list_table ;
 product_type |   cnt
--------------+---------
 A            | 1000000
 B            | 2000000
 C            | 1000000
 D            | 1000000
(4 rows)

Time: 0.648 ms
复制

从以上的执行时间来看，相同的逻辑脚本查询，使用物化视图后执行效率高很多。如再增加一些测试，由于物化视图的数据不会自动更新，需要通过手动或定时刷新物化视图来进行数据同步。

物化视图刷新操作有两种模式：

默认模式：在刷新时会锁定视图，使之在刷新期间不可用

CONCURRENTLY 模式：允许并发刷新，刷新期间物化视图仍然可查询。使用此模式的前提是物化视图需要有唯一索引。

由于当前表没有创建唯一索引，所以执行会出现以下错误。

postgres=# refresh materialized view concurrently mv_list_table ;
ERROR:  cannot refresh materialized view "public.mv_list_table" concurrently
HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view.
Time: 1.358 ms
复制

由于原生的PostgreSQL 物化视图的刷新每次是采用全量刷新，对于数据量大的表的刷新时间会比较长，因而可采用 pg_ivm 的扩展插件进行增量刷新，只处理发生更改的数据，减少了计算和存储开销，可以大大提高物化视图的刷新效率。以下将介绍 pg_ivm 的安装与如何进行增量刷新。

安装 pg_ivm

上传安装包到相应目录，解压 pg_ivm-1.9.tar.gz 包，再通过 make 与 make install 命令进行安装。安装步骤如下：

[root@pc001 soft]# chown -R postgres:postgres pg_ivm-1.9.tar.gz
[root@pc001 soft]# su - postgres
[postgres@pc001 ~]$ cd /soft/
[postgres@pc001 soft]$ tar -zxvf pg_ivm-1.9.tar.gz

[postgres@pc001 pg_ivm-1.9]$ make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -I. -I./ -I/usr/postgres/16.1/include/server -I/usr/postgres/16.1/include/internal  -D_GNU_SOURCE   -c -o createas.o createas.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -I. -I./ -I/usr/postgres/16.1/include/server -I/usr/postgres/16.1/include/internal  -D_GNU_SOURCE   -c -o matview.o matview.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -I. -I./ -I/usr/postgres/16.1/include/server -I/usr/postgres/16.1/include/internal  -D_GNU_SOURCE   -c -o pg_ivm.o pg_ivm.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -I. -I./ -I/usr/postgres/16.1/include/server -I/usr/postgres/16.1/include/internal  -D_GNU_SOURCE   -c -o ruleutils.o ruleutils.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -I. -I./ -I/usr/postgres/16.1/include/server -I/usr/postgres/16.1/include/internal  -D_GNU_SOURCE   -c -o subselect.o subselect.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -shared -o pg_ivm.so  createas.o matview.o pg_ivm.o ruleutils.o subselect.o -L/usr/postgres/16.1/lib    -Wl,--as-needed -Wl,-rpath,'/usr/postgres/16.1/lib',--enable-new-dtags -fvisibility=hidden
[postgres@pc001 pg_ivm-1.9]$ make install
/usr/bin/mkdir -p '/usr/postgres/16.1/lib'
/usr/bin/mkdir -p '/usr/postgres/16.1/share/extension'
/usr/bin/mkdir -p '/usr/postgres/16.1/share/extension'
/usr/bin/install -c -m 755  pg_ivm.so '/usr/postgres/16.1/lib/pg_ivm.so'
/usr/bin/install -c -m 644 .//pg_ivm.control '/usr/postgres/16.1/share/extension/'
/usr/bin/install -c -m 644 .//pg_ivm--1.0.sql .//pg_ivm--1.0--1.1.sql .//pg_ivm--1.1--1.2.sql .//pg_ivm--1.2--1.3.sql .//pg_ivm--1.3--1.4.sql .//pg_ivm--1.4--1.5.sql .//pg_ivm--1.5--1.6.sql .//pg_ivm--1.6--1.7.sql .//pg_ivm--1.7--1.8.sql .//pg_ivm--1.8--1.9.sql  '/usr/postgres/16.1/share/extension/'
复制

安装成后可看到 /usr/postgres/16.1/lib 目录下 pg_ivm.so 文件。执行以下创建语句可成功创建插件 pg_ivm，由于创建增量物理视图时，会自动创建相关的触发器，所以新增数据后，不需要手动刷新也能准实时查询出新增后的记录，如下图显示：

postgres=# CREATE EXTENSION pg_ivm;
CREATE EXTENSION

postgres=# SELECT create_immv('imv_table', 'select product_type,count(1) as cnt from test_table group by product_type');
NOTICE:  created index "imv_table_index" on immv "imv_table"
 create_immv
-------------
           4
(1 row)

postgres=# select * from imv_table ;
 product_type |   cnt   | __ivm_count__
--------------+---------+---------------
 A            | 1000000 |       1000000
 B            | 2000000 |       2000000
 C            | 1000000 |       1000000
 D            | 1000000 |       1000000
(4 rows)

postgres=# insert into test_table values(generate_series(5000000,5000030),'A');
INSERT 0 31
postgres=# select * from imv_table ;
 product_type |   cnt   | __ivm_count__
--------------+---------+---------------
 B            | 2000000 |       2000000
 C            | 1000000 |       1000000
 D            | 1000000 |       1000000
 A            | 1000031 |       1000031
(4 rows)
复制

总结

pg_ivm 扩展为PostgreSQL带来了增量物化视图的功能，为高性能和高实时性的场景提供了极大便利。但在选择使用 pg_ivm 时，也会存在一些局限性，如：支持包括自连接在内的内连接，但不支持外连接。支持的聚集函数有count， sum, avg， min和max，子句中的子查询、窗口函数、HAVING、ORDER BY 都不支持。基本表也必须是简单表，视图、物化视图、继承父表、分区表、分区和外部表不能使用。在前面的 test_list_table 是分区表，导致创建 pg_ivm 物化视图时出现错误，后来将分区表换成普通表就可以。