pg_ivm: a PostgreSQL extension providing Incremental View Maintenance feature

Introduction

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.

Installation

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;

How to Use

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)

Summary

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.