What is a materialized view, and when is it worth the complexity?
A materialised view is a query whose answer the database stores, so that you read the stored answer instead of working it out again.
So when is it worth the complexity? Four conditions.
The first one is that the query is expensive. If the query is a quick lookup then the materialised view saves nothing, and you have added a thing to maintain.
The second one is that it is run far more often than the data changes. A report that is read every minute and where the data changes once a day is the perfect case.
The third one is that the answer is allowed to be a little out of date. This is the deal that you are making. A materialised view is only as fresh as the last refresh, and if that is not acceptable then this is the wrong tool.
The fourth one is that the answer is large enough to be worth storing. A materialised view that is smaller than the data it came from is not worth the disk.
And there are two costs that you have to accept. The refresh takes time and it takes locks, and the storage is a second copy of the data that has to be kept in step.
The next two questions in this topic show the manual version and the safe refresh.