pg_ivm

pg_ivm : incremental view maintenance on PostgreSQL

Overview

Packages

Source

pig build pkg pg_ivm;		# build deb

Install

Make sure PGDG and PIGSTY repo available:

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

Install this extension with pig:

pig install pg_ivm;		# install via package name, for the active PG version

pig install pg_ivm -v 18;   # install for PG 18
pig install pg_ivm -v 17;   # install for PG 17
pig install pg_ivm -v 16;   # install for PG 16
pig install pg_ivm -v 15;   # install for PG 15
pig install pg_ivm -v 14;   # install for PG 14

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'pg_ivm';

Create this extension with:

CREATE EXTENSION pg_ivm;

Usage

Sources: README, release 1.14

pg_ivm provides immediate Incremental View Maintenance for PostgreSQL materialized views. Instead of recomputing the whole view, it applies deltas in AFTER triggers and stores metadata in the pgivm schema.

CREATE EXTENSION pg_ivm;

Required Setup

Upstream says pg_ivm should be preloaded so IMMVs are maintained correctly:

shared_preload_libraries = 'pg_ivm'
session_preload_libraries = 'pg_ivm'

The current README says the extension is compatible with PostgreSQL 13 through 18, and the latest GitHub release is 1.14 dated March 31, 2026.

Main Functions

• pgivm.create_immv(name, query) creates an incrementally maintainable materialized view (IMMV), its maintenance triggers, and a unique index when possible.
• pgivm.refresh_immv(name, with_data) fully refreshes the IMMV and can disable or re-enable maintenance.
• pgivm.get_immv_def(regclass) reconstructs the stored SELECT definition.
• pgivm.pg_ivm_immv stores IMMV metadata including immvrelid, viewdef, ispopulated, and lastivmupdate.

Common Patterns

Create an IMMV:

SELECT pgivm.create_immv(
  'immv_agg',
  'SELECT bid, count(*), sum(abalance), avg(abalance)
   FROM pgbench_accounts JOIN pgbench_branches USING(bid)
   GROUP BY bid'
);

Query the maintained result after base-table changes:

UPDATE pgbench_accounts SET abalance = abalance + 1000 WHERE aid = 4112345;
SELECT * FROM immv_agg WHERE bid = 42;

Inspect or refresh IMMVs:

SELECT immvrelid AS immv, pgivm.get_immv_def(immvrelid)
FROM pgivm.pg_ivm_immv;

SELECT pgivm.refresh_immv('immv_agg', true);

Pause maintenance for bulk work, then rebuild:

SELECT pgivm.refresh_immv('myview', false);
-- bulk changes
SELECT pgivm.refresh_immv('myview', true);

Caveats

• Upstream only supports a restricted subset of view definitions: joins, DISTINCT, simple subqueries/CTEs, and built-in aggregates count, sum, avg, min, and max.
• Unsupported constructs include HAVING, window functions, ORDER BY, LIMIT/OFFSET, UNION/INTERSECT/EXCEPT, DISTINCT ON, and user-defined aggregates.
• Efficient maintenance depends on having a suitable unique index; create_immv creates one automatically only when the definition allows it.