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

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


Solution 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