[icebraining]

At least in PG, a regular view must calculate everything at read time, which might mean a lot of duplicated calculations, and a materialized view has to be refreshed "manually", it doesn't auto-update piecemeal when the underlying data changes.

[durkie]

I've had pretty good luck with using table triggers to update materialized views and make everything "automatic". A little more work up front, but pretty easy to forget about it once it's in place.

[icebraining]

A REFRESH of a materialized view still requires full re-computation of all values, no? It's better than regular views if you have more reads than writes, but still quite wasteful.

[durkie]

You are right. I've been dealing with this personally with PostGIS and storing large geometries. Ideally you split the geometries up in to many smaller geometries (using a built-in function), and it's much faster to calculate intersections, contained lengths, etc using those instead.

Many places online recommend storing this collection of divided geometries as a materialized view, but I recently had to move it to a separate real table because inserting a single new record would take 15 minutes to update the view (on an RDS 4xlarge database). It could at least update concurrently, so other reads didn't block, but now that the the divided geometries are stored in a separate table I can add new records in under 5 seconds usually.

[pmart123]

I believe so. I would say it’s more useful for rollups or aggregations where real-time isn’t necessary.