Overview of Incremental Materialized Views with pg_ivm in PostgreSQL

Materialized views are a powerful feature in PostgreSQL. They let you store the results of a query physically on disk, so you can query them quickly without recalculating everything. However, there’s one major downside: materialized views do not update themselves automatically. Any changes in the base tables require you to run REFRESH MATERIALIZED VIEW, which recomputes the whole dataset. For large views, this can be time-consuming and disruptive.

This is where pg_ivm (Incremental View Maintenance for PostgreSQL) comes in. It’s an extension that allows materialized views to be updated incrementally. Instead of recalculating the entire view, pg_ivm only applies the changes caused by INSERT, UPDATE, and DELETE operations on the base tables.

Why pg_ivm?

Imagine you’re maintaining a materialized view of daily sales totals. Every time you add a new row to the sales table, you shouldn’t need to recompute all previous totals. With pg_ivm, only the new row’s impact is calculated and applied, making refreshes nearly instantaneous.

Benefits include:

• Faster updates: Only changed rows are processed.
• Always up-to-date views: No need to schedule full refreshes.
• Less system load: Incremental updates put less stress on the database compared to full recomputation.

Installing pg_ivm

Download and Build:

# Clone the repository
git clone https://github.com/sraoss/pg_ivm.git
cd pg_ivm
   
# Build and install
make install

Enable the Extension:

CREATE EXTENSION pg_ivm;

OR

If you’ve built PostgreSQL from source and have the pg_ivm extension included:

cd contrib/pg_ivm
make PG_CONFIG=/path/to/pg_config install

Enable the extension in your database:

CREATE EXTENSION pg_ivm;

Creating an Incrementally Maintained Materialized View

Unlike regular materialized views, you don’t use CREATE MATERIALIZED VIEW. Instead, pg_ivm provides a function pgivm.create_immv().

Example:

-- Base table
CREATE TABLE sales (
    id serial PRIMARY KEY,
    product text,
    amount int
);
-- Incrementally maintained materialized view
SELECT pgivm.create_immv('mv_sales_summary', $$
  SELECT product, SUM(amount) AS total_amount, COUNT(*) AS total_sales
  FROM sales
  GROUP BY product
$$);

Now, whenever you insert or update rows in sales, the view mv_sales_summary is automatically updated.

Verifying the Behavior

Insert some rows:

INSERT INTO sales (product, amount) VALUES ('Laptop', 1200), ('Phone', 800);

Query the incremental materialized view:

SELECT * FROM mv_sales_summary;

Output:

product | total_amount | total_sales
---------+--------------+-------------
 Laptop  |         1200 |           1
 Phone   |          800 |           1

Add more data:

INSERT INTO sales (product, amount) VALUES ('Laptop', 1000);

Query again:

product | total_amount | total_sales
---------+--------------+-------------
 Laptop  |         2200 |           2
 Phone   |          800 |           1

The view has been updated automatically — no manual REFRESH required.

Checking the Triggers

pg_ivm installs triggers on the base tables to keep IMMVs updated. You can check them:

SELECT tgname, tgfoid::regprocedure
FROM pg_trigger
WHERE tgrelid = 'sales'::regclass;

Refreshing an IMMV

If you need a manual refresh (similar to REFRESH MATERIALIZED VIEW):

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

Delete the Materialized View

When you call create_immv function, a table of the name immv_name is created

-- Dropping an IMMV
   DROP TABLE mv_sales_summary;

Limitations

pg_ivm is powerful, but it doesn’t support everything yet:

• No WINDOW functions
• No UNION, INTERSECT, or EXCEPT
• No LIMIT/OFFSET
• No HAVING clauses in many cases
• Only simple aggregates supported (SUM, COUNT, MIN, MAX, AVG)
• No support for IMMVs based on partitioned tables or foreign tables

For supported use cases (simple aggregates, joins, filters), it’s a game-changer.

pg_ivm makes materialized views practical for real-time reporting in PostgreSQL. By incrementally maintaining views with triggers, you can have always-up-to-date summaries without expensive full refreshes. While it doesn’t support all SQL features yet, it’s an excellent option for dashboards, analytics, and workloads where data freshness matters.

If you’re already struggling with slow materialized view refreshes, pg_ivm might be exactly what you need.