{"id":784,"date":"2026-10-16T05:01:41","date_gmt":"2026-10-16T05:01:41","guid":{"rendered":"https:\/\/abrarqasim.com\/blog\/postgres-materialized-view-auto-refresh-pg-ivm-1-16-the-index-i-would-check-first\/"},"modified":"2026-10-16T05:01:41","modified_gmt":"2026-10-16T05:01:41","slug":"postgres-materialized-view-auto-refresh-pg-ivm-1-16-the-index-i-would-check-first","status":"publish","type":"post","link":"https:\/\/abrarqasim.com\/blog\/postgres-materialized-view-auto-refresh-pg-ivm-1-16-the-index-i-would-check-first\/","title":{"rendered":"Postgres Materialized View Auto Refresh with pg_ivm 1.16"},"content":{"rendered":"

Okay, confession: for a long time my answer to “the dashboard is stale” was a cron job that ran REFRESH MATERIALIZED VIEW<\/code> every five minutes and a shrug. It works. It also rebuilds the entire view every time, even when one row changed since the last run. If you’ve ever watched that refresh crawl on a big table and wondered why Postgres can’t just apply the one change, this post is for you.<\/p>\n

Short version: it can, with an extension called pg_ivm. It keeps a materialized view current inside the same transaction that changes the base table, using triggers instead of a scheduled rebuild. The project shipped version 1.16<\/a> this month with PostgreSQL 19 support. I read the whole README<\/a> rather than the release note, because the restrictions section is where this extension is won or lost. I haven’t put it in front of a client database yet, so everything below is what the docs promise and what I’d test.<\/p>\n

The problem with REFRESH MATERIALIZED VIEW<\/h2>\n

A normal materialized view is a snapshot. You create it from a query, Postgres stores the result as a table, and it stays frozen until you run REFRESH MATERIALIZED VIEW<\/code><\/a>. That command recomputes the full query and swaps in the result. With the CONCURRENTLY<\/code> option readers aren’t blocked, but the work is still a full recompute.<\/p>\n

The pg_ivm README has a timing example that shows the gap well. These are the project’s numbers on a pgbench dataset with 10 million account rows, not mine, so treat them as the shape of the thing rather than a promise for your hardware. Updating one account row took about 9 ms. Refreshing the plain materialized view afterwards took 20.5 seconds. That is the whole reason the polling approach feels wrong: one row changed, and you paid for ten million.<\/p>\n

The usual workaround is what I did. Refresh on a timer, accept stale reads, and pick the timer by arguing with whoever owns the dashboard. Postgres has no built-in alternative, because incremental maintenance of arbitrary queries is a hard problem. That’s the gap pg_ivm fills.<\/p>\n

What pg_ivm does differently<\/h2>\n

The README calls the result an Incrementally Maintainable Materialized View, or IMMV. You create one with a function instead of CREATE MATERIALIZED VIEW<\/code>:<\/p>\n

-- before: a plain materialized view, refreshed by cron\nCREATE MATERIALIZED VIEW mv_normal AS\n  SELECT a.aid, b.bid, a.abalance, b.bbalance\n  FROM pgbench_accounts a JOIN pgbench_branches b USING (bid);\n\n-- after: an incrementally maintained one\nSELECT pgivm.create_immv('immv',\n  'SELECT a.aid, b.bid, a.abalance, b.bbalance\n   FROM pgbench_accounts a JOIN pgbench_branches b USING (bid)');\n<\/code><\/pre>\n
Behind that function call the extension creates triggers on the base tables. When you INSERT<\/code>, UPDATE<\/code> or DELETE<\/code> a row, an AFTER<\/code> trigger works out which rows in the view are affected and patches only those. The docs call this immediate maintenance, because the view is updated in the same transaction as the change. There is no window where a reader sees the base table and the view disagree.<\/p>\n
In the README’s example, the single-row update on the base table took about 15 ms with the IMMV in place, up from 9 ms without it. So you pay roughly six extra milliseconds on every write to get a view that is always current and never needs a 20-second refresh. Whether that trade is good depends entirely on how often the base table changes, which I’ll get to.<\/p>\n
The extension supports PostgreSQL 13 through 19. Installation is make install<\/code> plus CREATE EXTENSION pg_ivm;<\/code>, and the docs recommend adding it to shared_preload_libraries<\/code> or session_preload_libraries<\/code> so maintenance works correctly. That needs a restart, so plan it for a maintenance window.<\/p>\n
The index you must not forget<\/h2>\n
This is the part of the README I’d highlight with a marker. Incremental updates need to find the right row in the view quickly, and without an index on the view that lookup is a sequential scan. The README shows it directly. With the automatically created index in place, a single-row update took 15 ms. After dropping that index, the same update took about 3.2 seconds.<\/p>\n
create_immv<\/code> tries to build a unique index for you when it can derive a key from the query. A GROUP BY<\/code> gives it the grouped columns, a DISTINCT<\/code> gives it all the columns, and otherwise it looks for all the primary key columns of the base tables in the select list. When it can’t find one, it prints a notice telling you to create an index yourself. Please read that notice. If you ignore it, every write to a base table will quietly get slower, and you’ll blame the wrong thing.<\/p>\n
The practical rule I’d follow: include the primary keys of your base tables in the select list, check \\d<\/code> on the new view straight after creating it, and confirm an index exists before you call it done.<\/p>\n
Aggregates work, with one slow case<\/h2>\n
Dashboards are mostly aggregates, so this matters. The README shows a GROUP BY<\/code> view with count<\/code>, sum<\/code> and avg<\/code>, where updating one account row took about 17 ms and the aggregated numbers for that branch were correct immediately afterwards. The supported aggregates are count<\/code>, sum<\/code>, avg<\/code>, min<\/code> and max<\/code>, and only built-in ones.<\/p>\n
Two details deserve attention. First, min<\/code> and max<\/code>. If you delete the row that currently holds the minimum or maximum, Postgres may have to recompute that group from the base tables, and the docs warn that this can take a long time. So a view full of max()<\/code> columns on a table where rows get deleted is the wrong candidate.<\/p>\n
Second, floating point. The docs say using sum<\/code> or avg<\/code> on real<\/code> or double precision<\/code> columns is unsafe, because the incrementally maintained value can drift from what a fresh calculation would give. Use numeric<\/code> instead. Money columns should be numeric<\/code> already, but I’d audit anything else that feeds an IMMV.<\/p>\n
The extension also adds hidden columns named like __ivm_count__<\/code> to aggregate views, which it uses to maintain the numbers. Don’t select them in your own queries, and don’t be surprised when you see them in \\d<\/code>.<\/p>\n
Restrictions that will bite you<\/h2>\n
The supported SQL is a subset. Inner and outer joins, DISTINCT<\/code>, simple subqueries in FROM<\/code>, EXISTS<\/code> and simple CTEs are fine. Window functions, HAVING<\/code>, ORDER BY<\/code>, LIMIT<\/code>, UNION<\/code> and friends are not allowed. The base tables must be plain tables, which rules out views, partitioned tables and foreign tables. If your biggest table is partitioned, that alone may end the experiment, and I’d rather find that out on day one.<\/p>\n
Outer joins have extra rules: only a simple equijoin, and no aggregates in the same view. Logical replication isn’t supported either, so an IMMV on a subscriber won’t update when the publisher’s base table changes.<\/p>\n
Concurrency is the one I’d read twice. Under READ COMMITTED<\/code>, the extension takes an ExclusiveLock<\/code> on the view right after a base table changes, so two transactions touching the same IMMV are serialised. Under REPEATABLE READ<\/code> or SERIALIZABLE<\/code>, it raises an error instead of waiting if it can’t get the lock. That’s a correctness choice and a sensible one, but it means a hot table feeding an IMMV can become a queue. The README also says plainly that this is a poor fit when the base table is written to frequently or in large batches.<\/p>\n
Bulk loads, dumps and upgrades<\/h2>\n
The README gives a clean escape hatch for big writes. Call pgivm.refresh_immv('name', false)<\/code> to drop the triggers, load your data, then call it again with true<\/code> to rebuild and re-enable maintenance. For a nightly import that’s a normal pattern, and I’d script it.<\/p>\n
One more operational detail: the extension’s own metadata isn’t included in pg_dump<\/code> output. Before a dump or pg_upgrade<\/code> you run the bundled pg_ivm_dump_metadata<\/code> script, and after the restore you run the SQL it produced. If you skip that step, the view’s data comes back as an ordinary table with no triggers, and it quietly stops updating. I’d add that to the backup runbook before the first IMMV goes live. If you are still on the basics, my Postgres tuning notes<\/a> cover the parameters I actually touch, and this extension is another reason to do a real restore drill.<\/p>\n
When I’d use it and when I wouldn’t<\/h2>\n
I’d reach for pg_ivm when a view joins a few tables, the base tables change at a modest rate, readers need fresh numbers, and a 20-second rebuild is not acceptable. A customer-facing totals page is the textbook case. I’d keep a cron-refreshed materialized view when the data is allowed to be a few minutes old, the base tables take heavy writes, or the query uses features the extension doesn’t support. Plain old stale-but-cheap is a perfectly good answer for a lot of internal reporting.<\/p>\n
Try it this week<\/h2>\n
Pick one materialized view you currently refresh on a timer and check it against the restriction list above: no window functions, plain base tables, only the five aggregates. If it passes, restore a copy of the database onto a scratch server, install the extension, create the IMMV with a primary key in the select list, and time your real write workload against it. Compare the extra write latency with what your refresh job costs today. If you’d like to see the kind of client database work this sits inside, my portfolio<\/a> is the place to start.<\/p>\n","protected":false},"excerpt":{"rendered":"
pg_ivm keeps a Postgres materialized view current without cron refreshes. What the README says about indexes, aggregates, locks and pg_dump, and what I’d test first.<\/p>\n","protected":false},"author":2,"featured_media":783,"comment_status":"","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"rank_math_title":"","rank_math_description":"pg_ivm keeps a Postgres materialized view current without cron refreshes. What the README says about indexes, aggregates, locks and pg_dump, and what I'd test first.","rank_math_focus_keyword":"postgres materialized view auto refresh","rank_math_canonical_url":"","rank_math_robots":"","footnotes":""},"categories":[302],"tags":[178,862,19,863,177],"class_list":["post-784","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-devops","tag-database","tag-materialized-views","tag-performance","tag-pg-ivm","tag-postgres"],"_links":{"self":[{"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/posts\/784","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/comments?post=784"}],"version-history":[{"count":0,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/posts\/784\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/media\/783"}],"wp:attachment":[{"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/media?parent=784"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/categories?post=784"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/tags?post=784"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}