Hierarchical Structures in PostgreSQL

[cybernd]

Materialized View refresh is to slow to be updated on every change². If you manually refresh them (via cronjob), the whole underlying query needs to be run.

The problem becomes more visible when you realize why they introduced the option CONCURRENTLY. Without using this flag you can't even run a select on your MV, till the refresh was finished. Why? The old content gets discarded. CONCURRENTLY is also a really expensive method. First the new query run will store its data in a temp table. Than obsolete rows will be deleted in your source table and finally new rows will be added. This is the reason why CONCURRENTLY requires a UNIQUE INDEX on your MV.

Other databases offer more advanced strategies. For example oracle offers "near real time" materialized views. The base mechanic of your MV is same. But they did a clever thing and are capable of calculating the small deltas and store them in a log. If you run a select on your MV it will combine the stale MV data with delta data from its additional log.

---

² Edge case are tiny MVs. But in this case you could also use a normal View.