pg_ivm

Overview

Preload in every writing session; restore_immv(replace=true) needs superuser.

Version

Build

You can build the RPM / DEB packages for pg_ivm using pig build:

pig build pkg pg_ivm         # build RPM / DEB packages

Install

You can install pg_ivm directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install pg_ivm;          # Install for current active PG version

pig ext install -y pg_ivm -v 18  # PG 18
pig ext install -y pg_ivm -v 17  # PG 17
pig ext install -y pg_ivm -v 16  # PG 16
pig ext install -y pg_ivm -v 15  # PG 15
pig ext install -y pg_ivm -v 14  # PG 14

dnf install -y pg_ivm_18       # PG 18
dnf install -y pg_ivm_17       # PG 17
dnf install -y pg_ivm_16       # PG 16
dnf install -y pg_ivm_15       # PG 15
dnf install -y pg_ivm_14       # PG 14

apt install -y postgresql-18-pg-ivm   # PG 18
apt install -y postgresql-17-pg-ivm   # PG 17
apt install -y postgresql-16-pg-ivm   # PG 16
apt install -y postgresql-15-pg-ivm   # PG 15
apt install -y postgresql-14-pg-ivm   # PG 14

Preload:

shared_preload_libraries = 'pg_ivm';

Create Extension:

CREATE EXTENSION pg_ivm;

Usage

Sources:

• Official v1.17 README
• v1.17 release notes
• 1.16 to 1.17 upgrade SQL
• pg_ivm_dump_metadata utility

pg_ivm provides immediate incremental view maintenance for PostgreSQL. An Incrementally Maintainable Materialized View (IMMV) is stored as a table with triggers and metadata in the pgivm schema; base-table changes update the IMMV inside the same transaction instead of recomputing the complete query.

Enable and Create an IMMV

Load the library for every session that can modify an IMMV’s base tables. A cluster-wide setup requires a restart:

shared_preload_libraries = 'pg_ivm'

session_preload_libraries = 'pg_ivm' is also supported when managed consistently for all relevant sessions.

CREATE EXTENSION pg_ivm;

CREATE TABLE accounts (
    account_id bigint PRIMARY KEY,
    branch_id integer NOT NULL,
    balance numeric NOT NULL
);
INSERT INTO accounts VALUES (42, 1, 1000);

SELECT pgivm.create_immv(
    'account_totals',
    'SELECT branch_id, count(*) AS accounts, sum(balance) AS balance
     FROM accounts
     GROUP BY branch_id'
);

UPDATE accounts
SET balance = balance + 100
WHERE account_id = 42;

SELECT * FROM account_totals;

Manage and Inspect IMMVs

• pgivm.create_immv(name, query): creates and populates an IMMV, returning its row count.
• pgivm.refresh_immv(name, with_data): fully rebuilds the IMMV; false disables maintenance until a later populated refresh.
• pgivm.get_immv_def(regclass): returns the stored view definition.
• pgivm.restore_immv(name, query, populate, replace): reconstructs metadata, triggers, and indexes for an existing IMMV table; 1.17 adds the optional replacement flag.
• pgivm.get_create_immv_commands() and pgivm.get_restore_immv_commands(): emit SQL for rebuilding IMMVs or restoring their metadata.

Version 1.15 includes a helper for dump or pg_upgrade workflows:

pg_ivm_dump_metadata -d application > pg_ivm_metadata.sql

The script emits pgivm.restore_immv() calls. Restore the table data first, then execute the saved metadata SQL so incremental maintenance resumes without recreating the tables.

Restrictions and Operational Caveats

• Supported definitions include selected joins, DISTINCT, simple subqueries/CTEs, and built-in count, sum, avg, min, and max aggregates. Unsupported constructs include HAVING, window functions, ORDER BY, LIMIT/OFFSET, set operations, DISTINCT ON, and user-defined aggregates.
• Efficient maintenance depends on a suitable unique index. create_immv() creates one automatically only when the definition supplies usable grouping, distinct, or base-table primary-key columns.
• Creation and refresh take AccessExclusiveLock. Upstream warns about consistency risks for creation under REPEATABLE READ or SERIALIZABLE; use READ COMMITTED or refresh afterward.
• restore_immv() fails on an already registered relation unless replace = true; that option requires a superuser. Without replacement the caller must own the IMMV. A table-definition mismatch still fails.
• Version 1.15 also fixes incorrect maintenance after repeated trigger-driven modifications and a v1.14 outer-join maintenance crash.

Upgrade to 1.16

Version 1.16 adds PostgreSQL 19 support and fixes a crash when maintaining and dropping an IMMV in one transaction, maintenance involving columns without default equality operators, large OID handling, and maintenance lock-release timing. Install matching files and ensure modifying sessions load the new library, then run:

ALTER EXTENSION pg_ivm UPDATE TO '1.16';

Upgrade to 1.17

Upstream supports PostgreSQL 13–19. Version 1.17 fixes the restored IMMV population flag, preventing duplicate maintenance triggers after a later refresh; it also improves automatic unique-index derivation, rejects set-returning target lists and fixes index-creation crashes with LATERAL subqueries.

Install matching files, ensure modifying sessions use the new library, then update each database:

ALTER EXTENSION pg_ivm UPDATE TO '1.17';

For a PostgreSQL major upgrade, put the new option first when exporting metadata before the upgrade:

pg_ivm_dump_metadata --pg-upgrade application > pg_ivm_metadata.sql

The generated restore calls replace the old catalog entries left by the major upgrade. Run the saved SQL after completing the upgrade, as a superuser. The IMMV tables and their data must already be present; replacement is not a substitute for restoring the data.