A materialized view is a view that stores results of SELECT query in the database, and this enables you to get the query results quickly comparing to executing the query every time. On the other hand, when a table underlying the view is updated, the data in the materialized view gets out of date, so you need to update the materialized view.
REFRESH MATERIALIZED VIEW command as a way to update materialized views . This command refreshes the materialized view by re-running the query.
However, this is not very efficient because it recalculates all the data. If only a small part of the table is updated and only a part of the materialized view can be updated, this must be more efficient than recalculation.
This method is called Incremental View Maintenance (IVM). However, this feature is not implemented in the current PostgreSQL. So we are developing this feature in PostgreSQL and proposing it to the development community.
In this article, I will explain the IVM feature under development.
Note that the feature descried in this article is about the “under development” function, and not available in the current PostgreSQL . If you have any comments or requests for this feature, please give us your feedback!
The proposed IVM feature allows materialized views to be updated automatically and incrementally just after a underlying table is modified. Because this is an incremental update, it is faster than running a regular
REFRESH. And, of course, you don’t have to write trigger functions for your self.
Currently, the SQL queries used to define views are
SELECT ... FROM ... WHERE ..., joins (inner joins, outer joins, self-joins), some built-in aggregate functions (count, sum, avg, min, max),
GROUP BY clause,
DISTINCT clause, simple sub-queries in
FROM clause, simple CTEs (
WITH clauses), and
EXISTS sub-queries in
WHEREclause, where “simple” means that a sub-query or CTE does not include aggregates, outer joins, or
DISTINCT. The view can contain multiple tuples with the same content (duplicate tuple support).
On the other hand, aggregates of other than the above (such as user-defined aggregate), complex subqueries or CTEs, window functions,
OFFSET, set operations (
INTERSECT) are not supported.
Creating a View
CREATE INCREMENTAL MATERIALIZED VIEW command to create a materialized view that is updated automatically and incrementally . This
INCREMENTAL is the new keyword added to
CREATE MATERIALIZED VIEW in this implementation.
You can also check the syntax with the
postgres=# \h CREATE MATERIALIZED VIEW Command: CREATE MATERIALIZED VIEW Description: define a new materialized view Syntax: CREATE [ INCREMENTAL ] MATERIALIZED VIEW [ IF NOT EXISTS ] table_name [ (column_name [, ...] ) [ USING method ] [ WITH ( storage_parameter [= value] [, ... ] ) ] [ TABLESPACE tablespace_name ] AS query [ WITH [ NO ] DATA ]
For example, we can create a materialized view using tables of pgbench as follows. Here, the scale factor is 100.
test=# CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm AS SELECT a.aid, b.bid, abalance, bbalance FROM pgbench_accounts a JOIN pgbench_branches b ON a.bid = b.bid: NOTICE: created index "mv_ivm_index" on materialized view "mv_ivm" SELECT 10000000
This materialized view was created with IVM support because
INCREMENTAL option was specified. In addition, an index was automatically created on this materialized view that is necessary for efficient IVM. These can be confirmed by
\d meta command of
psql command. We can see in the result “Incremental view maintenance: yes” and an unique index defined on
bid, which are primary key columns of the base tables.
test=# \d+ mv_ivm Materialized view "public.mv_ivm" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Descrip tion ----------+---------+-----------+----------+---------+---------+-------------+--------------+-------- ----- aid | integer | | | | plain | | | bid | integer | | | | plain | | | abalance | integer | | | | plain | | | bbalance | integer | | | | plain | | | Indexes: "mv_ivm_index" UNIQUE, btree (aid, bid) View definition: SELECT a.aid, b.bid, a.abalance, b.bbalance FROM pgbench_accounts a JOIN pgbench_branches b ON a.bid = b.bid; Access method: heap Incremental view maintenance: yes
Updating a View Incrementally
Let’s see the effect of IVM. First, when I executed
REFRESH command on this view, it took about 33 seconds.
test=# REFRESH MATERIALIZED VIEW mv_ivm ; REFRESH MATERIALIZED VIEW Time: 32863.279 ms (00:32.863)
On the other hand, when I updated a row in
pgbench_accounts, it took about 16ms, which was more than 1000 times faster than the
REFRESH command. In addition, of course, the contents of the view was updated correctly.
test=# SELECT * FROM mv_ivm WHERE aid = 1; aid | bid | abalance | bbalance -----+-----+----------+---------- 1 | 1 | 100 | 1000 (1 row) Time: 1.583 ms test=# UPDATE pgbench_accounts SET abalance = 11111 WHERE aid = 1; UPDATE 1 Time: 16.202 ms test=# SELECT * FROM mv_ivm WHERE aid = 1; aid | bid | abalance | bbalance -----+-----+----------+---------- 1 | 1 | 11111 | 1000 (1 row) Time: 1.465 ms
Next, let’s create a materialized view on a aggregate query . Here,
pgbench's scale factor is 1000.
test2=# CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm2 AS SELECT bid, count(abalance), sum(abalance), avg(abalance) FROM pgbench_accounts GROUP BY bid; NOTICE: created index "mv_ivm2_index" on materialized view "mv_ivm2" SELECT 1000
It took more than 40 seconds for
REFRESH of this view .
test2=# REFRESH MATERIALIZED VIEW mv_ivm2; REFRESH MATERIALIZED VIEW Time: 42427.666 ms (00:42.428)
On the other hand, when I updated a row in the table, the view was automatically updated due to the IVM function. It took about 18ms, which is more than 2000 times faster than
test2=# SELECT * FROM mv_ivm2 WHERE bid = 1; bid | count | sum | avg -----+--------+------+------------------------ 1 | 100000 | 1000 | 0.01000000000000000000 (1 row) Time: 2.199 ms test2=# UPDATE pgbench_accounts SET abalance = abalance + 5000 WHERE aid = 1; UPDATE 1 Time: 17.951 ms test2=# SELECT * FROM mv_ivm2 WHERE bid = 1; bid | count | sum | avg -----+--------+------+------------------------ 1 | 100000 | 6000 | 0.06000000000000000000 (1 row) Time: 2.152 ms
In this article, I showed that the proposed IVM feature enables materialized views to be updated automatically and more rapidly than the
REFRESH command. In the next post, I will explain the performance of IVM in more detailed.