PostgreSQL 增量物化视图插件 - pg_ivm incremental materialized view maintenance

作者

digoal

日期

2022-04-20

标签

PostgreSQL , ivm , immv , 物化视图 , materialized view

https://yugonagata-pgsql.blogspot.com/2022/04/pgivm-postgresql-extension-providing.html

https://github.com/sraoss/pgsql-ivm/

增量物化视图做成了插件, 插件目前支持PG 14版本, 功能有一定的限制, 不支持聚合物化视图, 是内核内置版本的子集. 但是内核一直未接收这个feature, 作为插件放出来可以先体验起来.
增量物化视图会增加一定的写RT, 但是好处是实时更新了物化结果了.

As I introduced in the past posts ([1], [2], [3]), we have proposed to implement Incremental View Maintenance (IVM) support in PostgreSQL core. This project is aiming to add the new feature into PostgreSQL in future, but not to make a tool that enables the current PostgreSQL to use IVM. However, since publishing the code to GitHub repository, we have received several questions of whether we can use our IVM feature in the current PostgreSQL versions. So, for meeting this demand, we decided to launch a new project, that is named pg_ivm.

pg_ivm is the extension module that provides Incremental View Maintenance (IVM) feature for PostgreSQL, and this enables you to use IVM with the current PostgreSQL version!

This post describes what is pg_ivm and how to use it. The alpha version of pg_ivm 1.0 is released for public testing at the end of last month. So, you can try pg_ivm in the same way as explained in this post, and it would be so appreciated if you would give us any feedback for pg_ivm.

The installation is same as the other extension modules.

First, execute make install under in the module directory.

$ cd pg_ivm  
$ make install USE_PGXS=1  
复制

If you installed PostgreSQL from rpm or deb, you will need the devel package (for example, postgresql14-devel or postgresql-server-dev-14). Set the PG_CONFIG variable (make PG_CONFIG=...) in case you want to install pg_ivm to a non-default PostgreSQL.

Then, execute CREATE EXTENSION under the super user.

CREATE EXTENSION pg_ivm;  
复制

We call a materialized view that are maintained incrementally as an Incrementally Maintainable Materialized View (IMMV).

In our proposal for the PostgreSQL core ([4] ), an IMMV is a special type of materialized view and you can create one by executing CREATE INCREMENTAL MATERIALIZED VIEW command. On the other hand, in the pg_ivm extension, an IMMV is a special type of table and you can create one by calling the function create_immv.

For example, if you want to create IMMV with the same definition of a materialized view defined as;

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

call create_immv as below;

test=# SELECT create_immv('immv(aid, bid, abalance, bbalance)',                     
                   '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)  
复制

This function has two arguments of text and returns the number of rows in IMMV. The first is the name of the IMMV with the optional column names, and the second is the view definition query.

The created IMMV is updated automatically and incrementally when its base table is modified. For example, after updating a row in pgbench_accounts, immv is automatically updated like this;

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)  
复制

It took about only 15 ms, whereas REFRESH of the materialized view with the same definition required 20 sec.

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

In this post, I explained the pg_ivm extension module that provides Incremental View Maintenance (IVM) feature for PostgreSQL.

It is still in alpha release stage and the official release is planed in this month. The first release will support only sub-set of features implemented in the original project, and aggregates will not be supported, for example. Also, it is compatible with only PostgreSQL 14 for now. After confirming that they works without problems, we are going to support the remaining features and other PostgreSQL versions.

期望 PostgreSQL 增加什么功能?

PolarDB for PostgreSQL云原生分布式开源数据库

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.