pg_ivm is an extension module for PostgreSQL 14 that provides an Incremental View Maintenance (IVM) feature.That means that materialized views are updated immediately after a base table is modified.

pg_ivm gives answer to the age old problem of when to refresh the materialized view. As a refresher or reminder, a materialized view versus a normal view is that the former keeps a copy of the data referenced to speed up querying since you actually query that copy, while the latter performs a live query every time.

That might be great, but not ideal as you have to decide when to refresh that copy in order to avoid querying stale data.Until now in Postgres this could be done when running the command REFRESH MATERIALIZED VIEW. But again, when is the right time to run it, given the performance aspect of the time it takes for the operation to complete?

To remedy this issue, pg_ivm, which is an third-party open source extension to Postgres v14, creates incremental materialized views which are refreshed automatically when the base table gets updated.

As a practical demonstration, the makers of the extension, Japanese IVM Development Group, provide a simple example  on the extension's Github repo where they first do a select query on the base table and the view, and then they update the base table and show that the view has been updated too.

To create an IMMV, you have to call create_immv function with a relation name and a view definition query. For example:

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

creates an IMMV with name 'myview' defined as 'SELECT * FROM mytab'.

This is corresponding to the following command to create a normal materialized view:

CREATE MATERIALIZED VIEW myview AS SELECT * FROM mytab;

When an IMMV is created, some triggers are automatically created so that the view's contents are immediately updated when its base tables are modified.

There are some limitations of what you can do with them, however:

Currently, IMMV's view definition can contain inner joins, and DISTINCT clause. Inner joins including self-join are supported, but outer joins are not supported. Aggregates, sub-queries, CTEs, window functions, LIMIT/OFFSET, UNION/INTERSECT/EXCEPT, DISTINCT ON, TABLEAMPLE, VALUES, and FOR UPDATE/SHARE can not be used in view definition.

The base tables must be simple. Views, materialized views, inheritance parent tables, partitioned tables, partitions, and foreign tables can not be used.

The targetlist cannot contain system columns, columns whose name starts with __ivm_.

Logical replication is not supported, that is, even when a base table at a publisher node is modified, IMMVs at subscriber nodes defined on these base tables are not updated.

When the TRUNCATE command is executed on a base table, nothing is changed on the IMMV.

As far as benchmarking goes, they cite a simple update to a base table of 10 million records which is performed immediately, but requires then manually running REFRESH MATERIALIZED VIEW which comes with a penalty of 20575.721 ms, with the IMMV being updated immediately incurring a small cost of 15.448 ms for the base table  being updated.That small cost is because an extra index on the base table is created.

pg_ivm, as said, is available on Postgres 14 without being backward compatible.

To wrap it up, extensions like this add utility to Postgres,  showcase its flexibility and add value for its users. Well it's Postgres after all.

More Information

 pg_ivm on Github

Related Articles

The PostgresSQL Transition Guide Helps You Make The Switch

Move Over To PostgreSQL With Babelfish and MangoDB

PostgreSQL 14 Is Here - A Look At Its Past And Future

A Deep Dive Into PostgreSQL Indexes

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Comments

Make a Comment or View Existing Comments Using Disqus

or email your comment to: comments@i-programmer.info