FOSDEM 2022
/
Schedule
/
Events
/
Developer rooms
/
PostgreSQL
/
Automatically refresh materialized views in PostgreSQL

Automatically refresh materialized views in PostgreSQL

Tactics to make refreshing a painless process

Track: PostgreSQL devroom
Room: D.postgresql
Day: Sunday
Start: 13:30
End: 14:00
Video with Q&A: D.postgresql
Video only: D.postgresql
Chat: Join the conversation!

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.