pg_ivm

What pg_ivm does

pg_ivm introduces the concept of an Incrementally Maintainable Materialized View (IMMV). When you create an IMMV using the create_immv() function, pg_ivm executes the query to populate a real table, then installs triggers on every base table referenced in the query definition. From that point on, any DML statement (INSERT, UPDATE, DELETE) on those base tables fires the triggers, which compute the delta and apply it to the IMMV within the same transaction.

This is fundamentally different from standard materialized views, which are static snapshots that require an explicit REFRESH MATERIALIZED VIEW command. With pg_ivm, the IMMV is always consistent with the base tables — there is no staleness window. The tradeoff is that every write to a base table pays a maintenance cost at write time rather than deferring it to a bulk refresh. Zero staleness is not free — the bill simply arrives at a different time.

When to use pg_ivm

pg_ivm is most effective in specific scenarios:

• Low-frequency writes, high-frequency reads — when the base tables change infrequently but the aggregated or joined result is queried often, keeping the IMMV current avoids repeated expensive queries
• Small changesets on large tables — if each transaction modifies a handful of rows in a million-row table, computing the delta is far cheaper than recomputing the entire view
• Near-real-time dashboards — when a scheduled refresh every few minutes is too stale and you need the materialized data to reflect the latest committed writes
• Replacing manual trigger-based caching — if you already maintain summary tables with hand-written triggers, pg_ivm can replace that boilerplate with a single function call

pg_ivm is not a good fit for high-throughput write workloads where many rows change per second, or for queries that use features outside its supported subset (outer joins, window functions, etc.). One must know the cut of one's cloth before committing to the pattern.

Installation and setup

pg_ivm is a third-party extension — it does not ship with PostgreSQL. You need to install it from source or via a package manager. Once the shared library is available, enabling it is straightforward.

-- 1. Install the extension
CREATE EXTENSION pg_ivm;

-- 2. Optionally add to postgresql.conf for automatic IMMV maintenance
-- across all sessions (requires restart):
-- shared_preload_libraries = 'pg_ivm'
--
-- Or load per-session without a restart:
-- session_preload_libraries = 'pg_ivm'

Adding pg_ivm to shared_preload_libraries or session_preload_libraries is recommended to ensure that IMMV maintenance triggers fire correctly in all sessions. Without it, a session that has not loaded the library could modify base tables without updating the associated IMMVs.

Creating an IMMV

The primary function is pgivm.create_immv(), which takes a view name and a query definition. It executes the query, stores the result in a table, and sets up triggers for incremental maintenance.

-- Create an Incrementally Maintainable Materialized View (IMMV)
-- Returns the number of rows populated
SELECT pgivm.create_immv(
  'order_totals',
  'SELECT customer_id, count(*) AS order_count, sum(amount) AS total_amount
   FROM orders
   GROUP BY customer_id'
);

-- The IMMV is now a real table that updates automatically
-- when rows are inserted, updated, or deleted in the orders table
SELECT * FROM order_totals WHERE customer_id = 42;

IMMVs with joins

pg_ivm supports inner joins, including self-joins. Triggers are installed on all base tables in the join, so changes to any of them update the IMMV.

-- IMMVs support inner joins
SELECT pgivm.create_immv(
  'customer_order_summary',
  'SELECT c.id, c.name, count(*) AS order_count, sum(o.amount) AS total_spent
   FROM customers c
   JOIN orders o ON o.customer_id = c.id
   GROUP BY c.id, c.name'
);

-- Query it like any table — it stays current as orders or customers change
SELECT name, order_count, total_spent
FROM customer_order_summary
ORDER BY total_spent DESC
LIMIT 10;

Supported query features

IMMV definitions support a subset of SQL — a well-appointed subset, but a subset nonetheless. Understanding the boundaries is important before committing to pg_ivm.

Management functions

Beyond create_immv(), pg_ivm provides functions for inspecting and managing IMMVs.

-- Full refresh (recomputes from scratch, like REFRESH MATERIALIZED VIEW)
SELECT pgivm.refresh_immv('order_totals', true);

-- Unpopulate the IMMV (empties it and disables incremental maintenance)
SELECT pgivm.refresh_immv('order_totals', false);

-- Retrieve the view definition for an IMMV
SELECT pgivm.get_immv_def('order_totals'::regclass);

-- List all IMMVs and their definitions
SELECT * FROM pgivm.immvs;

-- Drop an IMMV (it is a table, so use DROP TABLE)
DROP TABLE order_totals;

Because IMMVs are ordinary tables, you drop them with DROP TABLE, not DROP MATERIALIZED VIEW. The triggers and catalog entries are cleaned up automatically. A tidy departure, which I appreciate.

Cloud availability

pg_ivm is a third-party extension, so cloud availability varies. On self-managed PostgreSQL (including EC2, GCE, or bare metal), you can install it from source. It supports PostgreSQL 13 through 18.

How Gold Lapel relates

If you'll permit me a moment of professional interest — pg_ivm and Gold Lapel attend to the same problem. Expensive queries recomputing the same aggregations and joins, over and over, as though the household had no memory of having done the work already.

pg_ivm is the bespoke tailor's approach. You identify the query, write the IMMV definition by hand, accept the constraints of the supported SQL subset, and pay the trigger cost on every write. For a small, well-understood wardrobe of queries on low-write tables, the fit is excellent. Precise. Immediate. No staleness whatsoever.

Gold Lapel takes a rather different view of the matter. Working at the proxy level, it observes your query traffic and identifies which patterns would benefit from materialization — without being told. It creates and manages materialized views on your behalf, refreshes them on a schedule that adapts to your data's rate of change, and imposes no cost on writes to the base tables. No trigger overhead. No query restrictions. No manual view definitions to maintain.

I should be honest: these are not competing approaches so much as different philosophies. pg_ivm gives you zero staleness on specific queries you have selected, at the cost of write overhead and SQL restrictions. Gold Lapel gives you broad, automatic materialization across your entire workload, at the cost of a brief staleness window between refreshes. If your situation demands the former, pg_ivm is fine work. If you would prefer the household to manage itself — that is rather more our department.