Strategies for optimizing materialized views for speed and timeliness

Since PostgreSQL 9.3 introduced materialized views, it’s been a feature that many of us use to power dashboards, pre-compute information, or execute common queries in a much faster manner. There are some challenges though. If your database often gets updated, keeping your materialized views up-to-date can be difficult. In the case of real-time dashboards, timeliness and speed are both important requirements. In this talk, I will share some strategies to keep your materialized views up-to-date in PostgreSQL and how to use REFRESH MATERIALIZED VIEW efficiently to fit your use case. Some questions that my talk answers: – How can you make sure that the materialized view is always up-to-date? – How can you avoid read-locks while refreshing the view? – How can you minimize refresh periods? I will explore these questions further and show some tactics to optimize 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.