Efficiently updating materialized views

by  |  16-Jan-2020 00:29

Had the devs identified an efficient way of doing so, the materialized view would probably be updating automatically and concurrently.

Generate prepopulated views over the data in one or more data stores when the data isn't ideally formatted for required query operations.

efficiently updating materialized views-79

Lucero Del Alba talks about using Postgre SQL's materialized views to process large amounts of data without slowing down your database in this new Write Stuff article, showing how to efficiently create, access, update, and delete them.

In this article, we'll learn how to cache result sets for queries that require heavy processing — as in the case of performing aggregate functions (SUM, AVG, etc) and JOINs over millions of records - so that the results will be processed just once and stored as a materialized view for quick retrieval later.

materialized views in Postgres 9.3 have a severe limitation consisting in using an exclusive lock when refreshing it.

This basically blocks any attempts to read a materialized view while it is being refreshed with new data from its parent relations command.

In general, if you're performing operations on records that are unlikely to be changed once inserted in the DB and where no new records for that group or period are likely to be introduced, then you have a perfect scenario for applying the techniques we'll cover here.

Community Discussion