pg_ivm

pg_ivm The pg_ivm module provides Incremental View Maintenance (IVM) feature for PostgreSQL. The extension supports PostgreSQL 13-19. Description Incremental View Maintenance (IVM) is a way to make materialized views up-to-date in which only incremental changes are computed and applied on views rather than recomputing the contents from scratch as REFRESH MATERIALIZED VIEW does. IVM can update materialized views more efficiently than recomputation when only small parts of the view are changed. There are two approaches with regard to timing of view maintenance: immediate and deferred. In immediate maintenance, views are updated in the same transaction that its base table is modified. In deferred maintenance, views are updated after the transaction is committed, for example, when the view is accessed, as a response to user command like REFRESH MATERIALIZED VIEW, or periodically in background, and so on. pg_ivm provides a kind of immediate maintenance, in which materialized views are updated immediately in AFTER triggers when a base table is modified. We call a materialized view supporting IVM an Incrementally Maintainable Materialized View (IMMV). To create IMMV, you have to call create_immv function with a relation name and a view definition query. For example: SELECT pgivm.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. postgres=# SELECT pgivm.create_immv('m', 'SELECT * FROM t0'); NOTICE: could not create an index on immv "m" automatically DETAIL: No unique key could be derived from the view definition. HINT: Create an index on the immv for efficient incremental maintenance. 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; -- automatically updated i --- 1 2 3 4 (4 rows) Note that if you use PostgreSQL 17 or later, during automatic maintenance of an IMMV, the search_path is temporarily changed to pg_catalog, pg_temp. Installation To install pg_ivm, execute this in the module's directory: make install If you installed PostgreSQL from rpm or deb, you will need the devel package (for example, postgresql14-devel or postgresql-server-dev-14). Important: Don't forget to set the PG_CONFIG variable (make PG_CONFIG=...) or the PATH to the pg_config command in case you want to use pg_ivm on a non-default or custom build of PostgreSQL. Read more here. Once installed, execute CREATE EXTENSION comand. CREATE EXTENSION pg_ivm; Configuration To ensure pg_ivm maintains IMMVs correctly, add it to either shared_preload_libraries or session_preload_libraries in postgresql.conf: # Add pg_ivm to preload libraries shared_preload_libraries = 'pg_ivm' # OR session_preload_libraries = 'pg_ivm' After making this change, restart PostgreSQL for the configuration to take effect. RPM packages and yum repository RPM packages of pg_ivm are available from the PostgreSQL yum repository. See the instruction for details. Note that we are not the maintainer of this yum repository and RPMs for pg_ivm in it may be not always latest. Objects When pg_ivm is installed, the 'pgivm' schema is created, along with the following objects within this schema. Functions create_immv Use create_immv function to create IMMV. pgivm.create_immv(immv text, view_definition text) RETURNS bigint create_immv defines a new IMMV of a query. A table of the name immv_name is created and a query specified by view_definition is executed and used to populate the IMMV. The query is stored in pg_ivm_immv, so that it can be refreshed later upon incremental view maintenance. create_immv returns the number of rows in the created IMMV. 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. In addition, a unique index is created on the IMMV automatically if unique keys can be derived from the view definition. For example, if the view definition query has a GROUP BY clause, a unique index is created on the columns of GROUP BY expressions. Also, if the view has DISTINCT clause, a unique index is created on all columns in the target list. Otherwise, if the IMMV contains all primary key attributes of its base tables in the target list, a unique index is created on these attributes. create_immv acquires an AccessExclusiveLock on the view. However, even if we are able to acquire the lock, a concurrent transaction may have already incrementally updated and committed the view before we can acquire it. In the REPEATABLE READ or SERIALIZABLE isolation levels, this could lead to an inconsistent state of the view. Unfortunately, this situation cannot be detected during the creation of the view. As a result, create_immv raises a warning in these isolation levels, suggesting that the command be used in READ COMMITTED or that refresh_immv be executed afterward to make the view's contents remain consistent. Note that if you use PostgreSQL 17 or later, while create_immv is running, the search_path is temporarily changed to pg_catalog, pg_temp. restore_immv Use restore_immv function to restore pg_ivm metadata for an existing IMMV. pgivm.restore_immv(immv text, view_definition text, populate boolean DEFAULT false, replace boolean DEFAULT false) RETURNS void Unlike create_immv(), this function assumes that the IMMV table already exists. It recreates the metadata, triggers, and indexes required by pg_ivm without recreating the IMMV table itself. If populate is false (default), existing table contents are preserved, assuming that the table already contains correct data. If populate is true,