[d125q]

Care to share some examples where SQL Server's indexed views would shine?

In my eyes they're similar to triggers, which incur a high performance overhead in OLTP systems and are shunned by developers. In OLAP systems custom ETL code will likely outperform them.

[aljgz]

Indexed views are much faster than trying to achieve the same result with triggers. Triggers have serious concurrency limitations, and you do recalculations even when the fields you depend on are not touched.

Indexed views are not much worse than indexes. Of course, when they refer to other tables there are underlying data lookups, but in our experience when we moved from triggers to indexed views, large scale data ingestion went way faster.

Where we used it: While revamping a large scale sales program, we stored the warehouse in/out in one table, and several things like current stock were calculated using indexed views.

Bonus: Using Snapshot concurrency control, you can do many things concurrently, and only when they both updates to a certain product in the same store you'll get the second transaction failing (which could be retried on the backend).

The fact that they are completely in-sync with your data is amazing.

[switchbak]

PG has had incremental view maintenance on the horizon for many years. I expect it to remain on the horizon for a long time.

What you're describing is amazing, and I wish I had it available to us. We've hand rolled far too many triggers to achieve the same thing, with all the expected problems you'd assume. I'm sure it could be abused/misused, but a batteries-included approach like that would be huge.

[munk-a]

If you are hand-rolling them then I might suggest https://github.com/trickle-labs/pg-trickle#pg_trickle which at least reduces the labor involved in trying to get it up and running and appears to address a lot of the correctness concerns I've traditionally had (like a sync not firing correctly due to a mid-operation outage).

[branko_d]

In OLTP, it's very difficult to guarantee correctness with triggers (very easy to have a race condition in concurrent environment). On a flip side, materialized views tend to lock more than you'd expect, especially when aggregates are involved.

The sweet spot is if you have a read-mostly database and use SNAPSHOT transaction isolation for the readers (which is SQL Server's implementation of MVCC). That way, writers may still block writers, but writers can never block readers, even when indexed views are being maintained.

Another neat trick is to "abuse" indexed views as multi-table CHECKs. The idea is to make a JOIN that would produce duplicated rows (and fail the indexed view's key) if some multi-table condition is not met.

[mickeyp]

These things exist to eliminate the risk of ever serving stale information from a materialised view. I.e., their benefit is political/reputational as much as they are technical in the sense that they save you effort like remembering to invalidate a MV after an ingest operation.

Stale MV is a thing you only ever burn your fingers on once. Like how "It's not DNS" is a common meme in networking.