# pg_ivm for PostgreSQL: Immediate Incremental View Maintenance

> pg_ivm maintains selected PostgreSQL materialized views through AFTER triggers and incremental changes. It suits workloads needing current results, with write overhead, index requirements, locking, and licensing terms to evaluate.

**sraoss/pg_ivm** — IVM (Incremental View Maintenance) implementation as a PostgreSQL extension

- Repository: https://github.com/sraoss/pg_ivm
- Stars: 1,509 · Forks: 55
- Language: C
- License: NOASSERTION
- Published: 2026-10-16 · Updated: 2026-10-16 · Language: en
- Canonical page: https://hysenlabs.com/projects/sraoss-pg-ivm

## The pg_ivm Maintenance Problem

pg_ivm addresses the cost of rebuilding a PostgreSQL materialized view after a source change. A standard `REFRESH MATERIALIZED VIEW` recomputes the view contents from the defining query. The README says pg_ivm instead computes and applies incremental changes, which can be more efficient when only a small portion of the result has changed. PostgreSQL administrators, application developers, and data engineers may use it when stored query results must reflect base-table modifications without waiting for a separate refresh command.

The project calls its result an Incrementally Maintainable Materialized View, or IMMV. An IMMV starts as a table populated from a supplied query, but pg_ivm records that query and attaches the machinery required for subsequent maintenance. This differs from a normally refreshed materialized view because the IMMV is updated within the transaction that modifies a base table rather than being deliberately stale until the next refresh.

That immediacy has a cost. Base-table writes can trigger view work before the transaction completes. pg_ivm is therefore most relevant when freshness and incremental update cost matter more than minimizing every source-table write. The README gives no workload thresholds or comparative measurements, so it does not support a claim about a particular production speedup.

## Immediate Maintenance Through PostgreSQL Triggers

pg_ivm uses AFTER triggers on the relevant base tables. After an `INSERT`, `UPDATE`, or `DELETE` modifies source data, PostgreSQL invokes the trigger logic, and the extension changes the IMMV in the same transaction. This differs from deferred maintenance, where committed changes are processed later through access, a user command, or periodic background work.

The query is not merely attached as a comment. `pgivm.create_immv` stores it in `pg_ivm_immv` so pg_ivm can perform later incremental maintenance. The function also creates triggers and attempts to create a suitable unique index. The README says automatic index creation can derive keys from a `GROUP BY` clause, all target-list columns for a `DISTINCT` query, or primary-key attributes exposed by the target list.

A successful query may still produce an IMMV without the index needed for efficient maintenance. In the documented example, PostgreSQL emits a notice saying that no unique key could be derived and advises creating an index on the IMMV. That warning identifies a concrete condition under which later incremental work may be inefficient, so index selection belongs in IMMV creation rather than optional tuning after deployment.

## Installing pg_ivm and Enabling Its Runtime

The README installs pg_ivm from the module directory with `make install`. PostgreSQL installations obtained through RPM or Debian packaging may also require the matching development package. The Makefile uses `PG_CONFIG` or resolves `pg_config`, so custom PostgreSQL builds require the correct executable.

Run this command in the module directory:

```bash
make install
```

The README says to set `PG_CONFIG` or place the correct `pg_config` in `PATH` for a non-default PostgreSQL build. The extension binary must match the server installation and supported PostgreSQL version. The project states support for PostgreSQL 13 through 19.

After installing the files, create the database extension:

```sql
CREATE EXTENSION pg_ivm;
```

The README requires pg_ivm in either `shared_preload_libraries` or `session_preload_libraries`. The documented choices are:

```text
shared_preload_libraries = 'pg_ivm'
session_preload_libraries = 'pg_ivm'
```

A deployment chooses one entry according to its loading requirements. After changing `postgresql.conf`, the README instructs the administrator to restart PostgreSQL. Omitting that step leaves a mismatch between the installed extension and the documented runtime configuration.

## Creating and Checking the First IMMV

The simplest documented use is to create an IMMV over one table. With a table named `t0`, register its query like this:

```sql
SELECT pgivm.create_immv('m', 'SELECT * FROM t0');
```

The function creates a relation named `m`, populates it from `SELECT * FROM t0`, stores the definition, and returns the initial row count. The README shows that this operation may emit a notice when no unique key can be derived. If it does, the warning explicitly directs the user to create an index on the IMMV.

A normal query reads the IMMV:

```sql
SELECT * FROM m;
```

The README example shows three existing rows. After inserting a row into the base table, querying `m` again shows the new value without an explicit refresh:

```sql
INSERT INTO t0 VALUES (4);
SELECT * FROM m;
```

This demonstrates the transaction model, not production performance. An initial deployment check should also confirm that the intended query was registered and that the expected unique index exists. The README does not provide a portable inspection command for those checks, so an invented command would be misleading.

## Creation Locks and Isolation Constraints

`pgivm.create_immv` acquires an `AccessExclusiveLock` on the new IMMV, but the README describes a race that the lock does not fully remove. A concurrent transaction can incrementally update the view and commit after initial population but before pg_ivm finishes acquiring its lock. Under `REPEATABLE READ` or `SERIALIZABLE`, the resulting contents can be inconsistent.

The extension cannot detect this condition during creation. Its warning recommends running creation under `READ COMMITTED`, or invoking `refresh_immv` afterward to make the contents consistent. This is a meaningful operational limitation because the selected isolation level does not provide the expected guarantee for this creation pattern.

On PostgreSQL 17 and later, both automatic maintenance and `create_immv` temporarily use a `search_path` of `pg_catalog, pg_temp`. Applications should not rely on unqualified names in IMMV definitions if their normal session path differs from that temporary setting. The README does not document a rollback procedure for failed IMMV creation, so it would be incorrect to imply one.

## restore_immv, Upgrades, and Maintenance Cost

pg_ivm provides `pgivm.restore_immv` for an existing IMMV table. Unlike `create_immv`, it does not recreate the table. It restores the metadata, triggers, and indexes required by pg_ivm. With the default `populate` value of false, existing contents are retained on the assumption that they are correct. Setting `populate` to true regenerates them from the view definition.

The `replace` option controls whether an existing `pgivm.pg_ivm_immv` entry may be replaced. Its default is false. Replacing an entry requires superuser privileges, while the caller otherwise must own the IMMV. The README associates this function with `pg_upgrade`, making it relevant when preserving extension metadata during a PostgreSQL upgrade.

The repository is not archived, and its last push was on 2026-10-15. Recent releases include v1.16 on 2026-09-30, v1.15 on 2026-06-30, and v1.14 on 2026-03-31. These dates show recent project activity, but they do not remove upgrade work. The Makefile contains sequential upgrade scripts through `pg_ivm--1.16--1.17.sql`, so administrators still need the correct upgrade path for their installed and target versions.

The LICENSE permits use, copying, modification and redistribution subject to retaining the specified copyright and disclaimer notices in copies. Review its exact terms before use. The README also notes that RPM packages in the PostgreSQL yum repository may not always contain the latest pg_ivm version, so package version verification belongs in the upgrade plan.

## Trigger Costs and the Refresh Alternative

The main alternative is PostgreSQL's normal materialized-view refresh model. A conventional materialized view is populated with `CREATE MATERIALIZED VIEW`, and `REFRESH MATERIALIZED VIEW` recomputes its contents from the defining query. That approach moves maintenance away from each base-table transaction and gives the operator a clear refresh point. Its cost is full recomputation rather than pg_ivm's documented incremental changes.

pg_ivm addresses the opposite trade-off. It is appropriate when results must update immediately and changed data represents a small part of the view. Conventional refresh is more direct when frequent full recomputation is acceptable, when writes must avoid trigger work, or when deferred freshness is acceptable. pg_ivm is the wrong choice when those requirements dominate because immediate maintenance places update processing in the source transaction.

Automatic unique-index creation is another decision boundary. pg_ivm can infer useful keys for some `GROUP BY`, `DISTINCT`, and primary-key-based queries, but the documented warning proves that inference does not cover every case. A deployment that cannot add the required IMMV index sacrifices the intended efficiency of incremental maintenance. Full recomputation may remain clearer for small views or workloads in which nearly every row changes.

## Conclusion

pg_ivm fits PostgreSQL teams that need IMMVs updated in the base-table transaction and can manage trigger overhead, suitable query definitions, and required indexes. It is less suitable for deferred refresh, workloads that change large portions of every result. Verify PostgreSQL version support, automatic index creation, preload configuration, query compatibility, and the LICENSE file first.

## FAQ

### Which PostgreSQL versions does pg_ivm support?

The README states that pg_ivm supports PostgreSQL 13 through 19.

### How does pg_ivm keep its materialized views up to date?

pg_ivm creates AFTER triggers on the relevant base tables and applies incremental changes in the same transaction as a base-table modification.

### What should I do if pg_ivm cannot create a unique index automatically?

The README advises creating an index on the IMMV for efficient incremental maintenance. It reports the condition when no unique key can be derived from the query.

## Sources

- [Issues](https://github.com/sraoss/pg_ivm/issues)
- [README](https://github.com/sraoss/pg_ivm/blob/main/README.md)
- [Releases](https://github.com/sraoss/pg_ivm/releases)
- [sraoss/pg_ivm on GitHub](https://github.com/sraoss/pg_ivm)

---

Hysen Labs editorial analysis, written from the project's own repository and release notes. Cite the canonical page: https://hysenlabs.com/projects/sraoss-pg-ivm