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.
Speakers: Attila Tóth