pg_ivm

Overview

deb takeover by pgdg since 2026-01

Version

Build

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

pig build pkg pg_ivm         # build 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:

• Install
• pig
• dnf
• apt

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: 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.