Extension for Incremental Leer Repairs (IVM) Characteristic for PostgreSQL

The pg_ivm module provides Incremental Leer Repairs (IVM) characteristic for PostgreSQL.

The extension is compatible with PostgreSQL 14.

Description

Incremental Leer Repairs (IVM) is a sort to compose materialized views up-to-date whereby handiest incremental modifications are computed and utilized on views rather than recomputing the contents from scratch as REFRESH MATERIALIZED VIEW does. IVM can update materialized views extra efficiently than recomputation when handiest small substances of the admire are changed.

There are two approaches with regard to timing of admire repairs: rapid and deferred. In rapid repairs, views are as a lot as this point within the identical transaction that its unsuitable desk is modified. In deferred repairs, views are as a lot as this point after the transaction is committed, as an instance, when the admire is accessed, as a response to user sing be pleased REFRESH MATERIALIZED VIEW, or periodically in background, and so forth. pg_ivm provides a roughly rapid repairs, whereby materialized views are as a lot as this point at as soon as in AFTER triggers when a unsuitable desk is modified.

We name a materialized admire supporting IVM an Incrementally Maintainable Materialized Leer (IMMV). To compose IMMV, you wish to name create_immv feature with a relation title and a admire definition inquire. To illustrate:

SELECT create_immv('myview', 'SELECT FROM mytab');

creates an IMMV with title ‘myview’ outlined as ‘SELECT FROM mytab’. Here’s same to the next sing to compose a celebrated materialized admire;

CREATE MATERIALIZED VIEW myview AS SELECT * FROM mytab;

When an IMMV is created, some triggers are robotically created in sigh that the admire’s contents are at as soon as as a lot as this point when its unsuitable tables are modified.

postgres=# SELECT create_immv('m', 'SELECT FROM t0');
NOTICE:  might maybe per chance maybe no longer compose an index on immv "m" robotically
DETAIL:  This map list doesn't have the whole indispensable key columns, or this admire doesn't beget DISTINCT clause.
HINT:  Construct an index on the immv for efficient incremental repairs.
 create_immv 
-------------
           3
(1 row)

postgres=# SELECT FROM m;
 i
---
 1
 2
 3
(3 rows)

postgres=# INSERT INTO t0 VALUES (4);
INSERT 0 1
postgres=# SELECT FROM m; -- robotically as a lot as this point
 i
---
 1
 2
 3
 4
(4 rows)

Installation

To set up pg_ivm, compose this within the module’s itemizing:

If you installed PostgreSQL from rpm or deb, you are going to need the devel kit (as an instance, postgresql14-devel or postgresql-server-dev-14).

Valuable: Manufacture no longer neglect to quandary the PG_CONFIG variable (compose PG_CONFIG=...) must it’s essential take a look at pg_ivm on a non-default or custom create of PostgreSQL. Learn extra here.

And, compose CREATE EXTENSION comand.

Objects

When pg_ivm is installed, the next objects are created.

IMMV creation feature

Utilize create_immv feature to creae IMMV.

create_immv(immv_name text, view_definition text) RETURNS bigint

create_immv outlined a fresh IMMV of a inquire. A desk of the title immv_name is created and a inquire specified by view_definition is executed and feeble to populate the IMMV. The inquire is saved in pg_ivm_immv, in sigh that it must always also be refreshed later upon incremental admire repairs. create_immv returns the choice of rows within the creaetd IMMV.

When an IMMV is created, some triggers are robotically created in sigh that the admire’s contents are at as soon as as a lot as this point when its unsuitable tables are modified. As smartly as, a various index is created on the IMMV robotically if conceivable. If the IMMV incorporates all indispensable key attritubes of its unsuitable tables within the map list, a various index is created on these attritubes. Or, if the admire has DISTINCT clause, a various index is created on all columns within the map list. In diverse cases, no index is created.

IMMV metadata catalog

The catalog pg_ivm_immv stores IMMV files.

Example

In frequent, IMMVs allow sooner updates than REFRESH MATERIALIZED VIEW on the price of slower updates to their unsuitable tables. Replace of unsuitable tables is slower because triggers will be invoked and the IMMV is as a lot as this point in triggers per modification commentary.

To illustrate, bellow a celebrated materialized admire outlined as under:

take a look at=# CREATE MATERIALIZED VIEW mv_normal AS
        SELECT a.help, b.mumble, a.abalance, b.bbalance
        FROM pgbench_accounts a JOIN pgbench_branches b USING(mumble);
SELECT 10000000

Updating a tuple in a unsuitable desk of this materialized admire is lickety-split but the REFRESH MATERIALIZED VIEW sing on this admire takes a actually prolonged time:

take a look at=# UPDATE pgbench_accounts SET abalance=1000 WHERE help=1;
UPDATE 1
Time: 9.052 ms

take a look at=# REFRESH MATERIALIZED VIEW mv_normal ;
REFRESH MATERIALIZED VIEW
Time: 20575.721 ms (00: 20.576)

On the diverse hand, after developing IMMV with the identical admire definition as under:

take a look at=# SELECT create_immv('immv',
        'SELECT a.help, b.mumble, a.abalance, b.bbalance
         FROM pgbench_accounts a JOIN pgbench_branches b USING(mumble)');
NOTICE:  created index "immv_index" on immv "immv"
 create_immv 
-------------
    10000000
(1 row)

updating a tuple in a unsuitable desk takes extra than the celebrated admire, but its advise material is as a lot as this point robotically and here’s sooner than the REFRESH MATERIALIZED VIEW sing.

take a look at=# UPDATE pgbench_accounts SET abalance=1234 WHERE help=1;
UPDATE 1
Time: 15.448 ms

take a look at=# SELECT FROM immv WHERE help=1;
 help | mumble | abalance | bbalance 
-----+-----+----------+----------
   1 |   1 |     1234 |        0
(1 row)

An applicable indexe on IMMV is indispensable for efficient IVM because we wish to looks to be for tuples to be as a lot as this point in IMMV. If there don’t seem like any indexes, this might maybe occasionally per chance maybe seize a actually prolonged time.

Subsequently, when an IMMV is created by the create_immv feature, a various index is created on it robotically if conceivable. If the IMMV incorporates all indispensable key attritubes of its unsuitable tables within the map list, a various index is created on these attritubes. Also, if the admire has DISTINCT clause, a various index is created on all columns within the map list. In diverse cases, no index is created.

Within the outdated example, a various index “immv_index” is created on help and mumble columns of “immv”, and this permits the lickety-split update of the admire. Dropping this index compose updating the admire seize a loger time.

take a look at=# DROP INDEX immv_index;
DROP INDEX

take a look at=# UPDATE pgbench_accounts SET abalance=9876 WHERE help=1;
UPDATE 1
Time: 3224.741 ms (00: 03.225)

Supported Leer Definitions and Restriction

Currently, IMMV’s admire definition can beget internal joins, and DISTINCT clause. Inside joins at the side of self-be half of are supported, but outer joins are no longer supported. Aggregates, sub-quereis, CTEs, window functions, LIMIT/OFFSET, UNION/INTERSECT/EXCEPT, DISTINCT ON, TABLEAMPLE, VALUES, and FOR UPDATE/SHARE can no longer be feeble in admire definition.

The unsuitable tables must always be straightforward. Views, materialized views, inheritance parent tables, partitioned tables, partitions, and foreign tables can no longer be feeble.

The targetlist can no longer beget machine columns, columns whose title begins with __ivm_.

Logical replication isn’t any longer supported, that is, even when a unsuitable desk at a publisher node is modified, IMMVs at subscriber nodes outlined on these unsuitable tables are no longer as a lot as this point.

When the TRUNCATE sing is executed on a unsuitable desk, nothing is changed on the IMMV.

Notes

DISTINCT

DISTINCT is allowed in IMMV’s definition queries. Mutter an IMMV outlined with DISTINCT on a unsuitable desk containing reproduction tuples. When tuples are deleted from the unsuitable desk, a tuple within the admire is deleted if and handiest if the multiplicity of the tuple becomes zero. Furthermore, when tuples are inserted into the unsuitable desk, a tuple is inserted into the admire handiest if the identical tuple doesn’t exist already in it.

Bodily, an IMMV outlined with DISTINCT incorporates tuples after eradicating duplicates, and the multiplicity of every and each tuple is saved in an additional column named __ivm_count__ that is added when such IMMV is created.

Concurrent Transactions

Mutter an IMMV is printed on two unsuitable tables and each desk used to be modified in diverse a concurrent transaction simultaneously. Within the transaction which used to be committed first, the IMMV might maybe per chance even be as a lot as this point brooding about handiest the alternate which took arena on this transaction. On the diverse hand, in repeat to update the IMMV wisely within the transaction which used to be committed later, we wish to know the modifications occurred in each and each transactions. For this cause, ExclusiveLock is held on an IMMV at as soon as after a unsuitable desk is modified in READ COMMITTED mode to make certain that that the IMMV is as a lot as this point within the latter transaction after the extinct transaction is committed. In REPEATABLE READ or SERIALIZABLE mode, an error is raised at as soon as if lock acquisition fails because any modifications which occurred in diverse transactions are no longer be viewed in these modes and IMMV can no longer be as a lot as this point wisely in such eventualities. Alternatively, as an exception if the IMMV has handiest one unsuitable desk, the lock held on the IMMV is RowExclusiveLock.

Row Stage Safety

If some unsuitable tables have row level security policy, rows which can per chance maybe be no longer viewed to the materialized admire’s proprietor are excluded from the consequence. As smartly as, such rows are excluded as smartly when views are incrementally maintained. Alternatively, if a fresh policy is printed or policies are changed after the materialized admire used to be created, the fresh policy might maybe per chance maybe also no longer be utilized to the admire contents. To prepare the fresh policy, you wish to recreate IMMV.

Authors

IVM Trend Neighborhood

• https://github.com/yugo-n
• https://github.com/thoshiai

License

PostgreSQL License

Copyright

• Portions Copyright (c) 1996-2022, PostgreSQL World Trend Neighborhood
• Portions Copyright (c) 2022, IVM Trend Neighborhood