Automatically refresh materialized views in PostgreSQL

Since PostgreSQL 9.3 introduced materialized views, it’s been an important feature that many users leverage to power dashboards, pre-compute information, or execute common queries in a much faster manner.

On the flip side, if your database often gets updated, keeping your materialized views up-to-date can be challenging. Especially when working with time-series data where timeliness is often an important requirement. In this talk, I will share some tactics to keep your materialized views up-to-date in PostgreSQL and how to use REFRESH MATERIALIZED VIEW efficiently to fit your use case.

Let’s say you have a real-time dashboard fueled by a PostgreSQL materialized view. How can you make sure that this materialized view is always up-to-date, hence the dashboard is showing up-to-date information as well? How can you avoid read-locks while refreshing the view or minimize refresh periods? These are some of the questions many users ask when starting to work with PostgreSQL materialized views.

In this talk, I will explore these questions further and show some tactics on how you can optimize the process of refreshing your PostgreSQL materialized views and keeping them maintained. I will dissect the REFRESH MATERIALIZED VIEW command and its usage and show additional tips on how you can make refreshing materialized views as painless as possible.

(This recording is from FOSDEM 2022 PostgreSQL Devroom)