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

Create Extension:

CREATE EXTENSION pg_ivm;

Usage

pg_ivm: Incremental View Maintenance for PostgreSQL

The pg_ivm extension provides Incremental View Maintenance (IVM), updating materialized views by applying only incremental changes rather than recomputing from scratch. Views are updated immediately in AFTER triggers when base tables are modified.

CREATE EXTENSION pg_ivm;

Configuration

Add pg_ivm to preload libraries for correct maintenance:

shared_preload_libraries = 'pg_ivm'

Functions

create_immv

pgivm.create_immv(immv_name text, view_definition text) RETURNS bigint

Creates an Incrementally Maintainable Materialized View (IMMV). Triggers are automatically created to keep the view updated. A unique index is created automatically if possible.

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

refresh_immv

pgivm.refresh_immv(immv_name text, with_data bool) RETURNS bigint

Completely replaces IMMV contents. With with_data = false, the IMMV becomes unpopulated and triggers are dropped. With with_data = true, triggers and indexes are recreated.

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

get_immv_def

pgivm.get_immv_def(immv regclass) RETURNS text

Returns the reconstructed SELECT command for an IMMV.

IMMV Metadata Catalog

The pgivm.pg_ivm_immv catalog stores IMMV information:

Examples

Create an IMMV with aggregates:

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

Updates to base tables are reflected automatically:

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

List all IMMVs:

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

Drop an IMMV with DROP TABLE:

DROP TABLE myview;

Disable/Enable Maintenance

Disable immediate maintenance before bulk modifications, then refresh:

SELECT pgivm.refresh_immv('myview', false);   -- disable
-- ... bulk modifications ...
SELECT pgivm.refresh_immv('myview', true);    -- refresh and re-enable

Supported Query Features

• Inner and outer joins (including self-join)
• DISTINCT clause
• Aggregate functions: count, sum, avg, min, max
• Simple subqueries in FROM clause
• EXISTS subqueries in WHERE clause
• Simple CTEs (WITH queries)
• GROUP BY clause