How to refresh a materialized view incrementally in PostgreSQL i.e. only for the data that is new or has changed?

Question

How to implement fast refresh materialized view postgresql How to update only new changes to materialized view from base tables

Okubo

Refresh materalized view incrementally in PostgreSQL – Denominator

Answer 1

I've been following this new development for a while hoping it would be finalized and added to Postgresl 14: https://wiki.postgresql.org/wiki/Incremental_View_Maintenance.

Here is a blog post with some performance numbers: https://yugonagata-pgsql.blogspot.com/2021/04/implementing-incremental-view_61.html

Here is part III with links to github and dockerhub: https://yugonagata-pgsql.blogspot.com/2021/06/implementing-incremental-view.html

Github source code: https://github.com/sraoss/pgsql-ivm

Docker image for testing purposes: https://hub.docker.com/r/yugonagata/postgresql-ivm

EDIT 2022-03-24: seems this has been moved to version 16, see Postgresql Incremental Materialized View Maintenance (IVM) challenges, why delay it again to targetversion 16?

Dustproof