cloudparts

pg_ivm

A PostgreSQL extension that keeps materialized views up to date inside the same transaction that changes their base tables, by computing and applying only the changed rows instead of rerunning the query.

What it is
Incremental view maintenance
Type
Open source
Made by
IVM Development Group (SRA OSS)
License
PostgreSQL License
Facts checked
24 October 2026
Website Source code

What it is

Plain PostgreSQL materialized views go stale: REFRESH MATERIALIZED VIEW reruns the whole query and replaces the contents. pg_ivm adds incrementally maintainable materialized views (IMMVs). You create one with pgivm.create_immv('name', 'SELECT …'); the extension creates a table, fills it, records the query and puts AFTER triggers on every base table. When a statement inserts, updates or deletes base rows, the triggers read the changed rows from transition tables, compute how the view’s result changes, and apply that to the IMMV before the transaction commits. This is immediate maintenance: a query after the change, in the same transaction, already sees the new view contents.

The algorithm counts. Each row of the view keeps a hidden __ivm_count__ with the number of ways it was derived, so a delete only removes a view row when its count reaches zero, which is how DISTINCT and joins stay correct. Aggregates keep helper columns too: avg is maintained from a hidden sum and count. A view can use inner and outer joins (outer joins only on simple equality), DISTINCT, count, sum, avg, min and max, simple subqueries in FROM, EXISTS subqueries and simple CTEs. It can’t use window functions, HAVING, ORDER BY, LIMIT, UNION and other set operations, recursive CTEs, user-defined aggregates, or views, partitioned or foreign tables as base tables. An index on the IMMV is essential, since each change has to find the view rows it affects; pg_ivm creates a unique index automatically when it can derive a key. Version 1.17 (October 2026) supports PostgreSQL 13 to 19.

Why teams pick it

  • Views are always current within the writing transaction, with no refresh job or separate streaming system
  • Updating one base row costs milliseconds instead of the seconds or minutes of a full REFRESH MATERIALIZED VIEW on a large join
  • Pure SQL inside PostgreSQL: IMMVs are ordinary tables that can be indexed and queried
  • Packaged in the PostgreSQL yum repository, and supports PostgreSQL 13 through 19

Watch out for

  • Every write to a base table pays for maintaining its views, and concurrent writers to different base tables of one view are serialised by a lock on the view
  • A restricted SQL subset: no window functions, HAVING, ORDER BY/LIMIT, set operations, recursive CTEs or aggregates other than count, sum, avg, min and max
  • Bulk loads or changes to a large share of a table can cost more than a full refresh; the docs suggest turning maintenance off with refresh_immv(…, false) and refreshing afterwards
  • IMMVs on a logical-replication subscriber don’t update when the publisher changes base tables, and their metadata needs a separate dump script for pg_dump and pg_upgrade

Specs

Compare with alternatives →
Query language
SQL subset: joins, DISTINCT, count, sum, avg, min, max, simple subqueries and CTEs
Consistency
Views updated inside the transaction that changes their base tables
Ways to run it
PostgreSQL extension (PostgreSQL 13 to 19)

Alternatives

What all incremental view maintenance have in common →Compare side by side →
FelderaOpen sourceAn incremental view maintenance engine in Rust: you write SQL tables and views, and it keeps every view up to date by processing only the changes, based on the award-winning DBSP theory.RisingWaveOpen sourceA streaming database in Rust that speaks the PostgreSQL protocol: you write materialized views in SQL, and it keeps them up to date as events arrive, with all state in object storage.ReadysetCommercialA SQL cache that speaks the PostgreSQL and MySQL wire protocols, sits between the app and the database, and keeps cached query results fresh by reading the database’s replication stream instead of waiting for a TTL.

How it fits with other technologies

An incrementally maintained aggregate over a join (pgbench tables, from the pg_ivm README) · sql

CREATE EXTENSION pg_ivm;   -- and add pg_ivm to shared_preload_libraries

SELECT pgivm.create_immv('immv_agg',
  'SELECT bid, count(*), sum(abalance), avg(abalance)
   FROM pgbench_accounts JOIN pgbench_branches USING (bid)
   GROUP BY bid');
-- NOTICE:  created index "immv_agg_index" on immv "immv_agg"

-- the AFTER triggers update bid 42's row in the same transaction
UPDATE pgbench_accounts SET abalance = abalance + 1000
WHERE aid = 4112345 AND bid = 42;

SELECT bid, count, sum, avg FROM immv_agg WHERE bid = 42;

See it in a real stack

No architecture uses pg_ivm yet. Drop it into the playground and wire it up yourself.Open playground →
Where it came from2013–2026

Incremental view maintenance was proposed for PostgreSQL itself at the PGCon 2013 developer meeting, and the first patch for core was submitted in 2019, using AFTER triggers with transition tables (available since PostgreSQL 10) and the counting algorithm. It was not committed. Since the code was public, users asked whether they could use IVM on the PostgreSQL versions they already ran.

So the IVM Development Group at SRA OSS, whose contributors include Yugo Nagata, Takuma Hoshiai and Tatsuo Ishii, packaged the work as an extension. pg_ivm 1.0 came out in 2022 for PostgreSQL 14, with selection, projection, joins and DISTINCT; aggregates followed in 1.1, subqueries in 1.2, CTEs in 1.5, EXISTS in 1.6 and outer joins in 1.13 (October 2025). A 2023 release fixed CVE-2023-22847, where IMMVs could expose rows hidden by row-level security. In 2026 releases added support for restoring IMMVs after pg_dump and pg_upgrade, and for PostgreSQL 19.

Why it’s built this way

Immediate maintenance in AFTER triggers
Updating the view in the same transaction as the base table keeps it always consistent with the data, with no change log or background job to build; the price is slower writes.
The counting algorithm with hidden columns
Storing how many times each view row was derived, and the sums behind each average, lets deletes and updates be applied correctly without rescanning base tables.
An extension instead of waiting for core
The core patch was large and slow to review; an extension let people use IVM on existing PostgreSQL versions while the core proposal continued.

Timeline

  1. 2013

    IVM proposed for PostgreSQL core at the PGCon developer meeting

  2. 2019

    First IVM patch for PostgreSQL core submitted

  3. 2022

    pg_ivm 1.0 for PostgreSQL 14; aggregates in 1.1

  4. 2023

    Security fix for row-level security (CVE-2023-22847); EXISTS subqueries

  5. 2025

    Outer join support in 1.13 (October)

  6. 2026

    Restore after pg_dump and pg_upgrade; PostgreSQL 19 support; 1.17 in October

Sources5 · facts checked 24 October 2026

Improve this page on GitHub