[dragonwriter]

> It has a feature named materialized views, but it isn't close to being the real feature that MSSQL calls it.

MSSQL doesn't have a featured called materialized views, it has a feature called indexed views which happens to implement a fairly robust version of the general DB pattern called materialized view.

PG has less mature materialized views (which are called materialized views) starting from 9.3, with additional features in 9.4, and which can be expected to mature further over at least the 9.5 and 9.6 cycles, from what I've seen various places. Certainly, SQL server is ahead on materialized views, but its not an "SQL server has them, Postgres doesn't" kind of thing.

[glogla]

SQL server can't do materialized views that refresh on demand, while postgres can't do materialized views that refresh automatically.

Personally, I saw materialized views that refresh on demand used much more than materialized views that refresh automatically, even though Oracle can be both.

[MichaelGG]

Well I can't think of a scenario where I want a single-updated materialized (or indexed) views. Because I can already do that by creating a table from a SELECT statement. So I don't see the point or use of what PG has implemented. I asked in IRC and got the same answer. It's like saying a read-only table supports updates cause you can drop it and rebuild it with the new data.

When would anyone really need PG's version of this feature over creating a table from a query? Out of all the usages of "I need an indexed/materialized view", PG covers essentially none of them, at least that I can think of.

[dragonwriter]

Anything you can do with materialized/indexed views (including most conceivable update models) you can do with the right combination of creating tables with SELECT statements and the right triggers to rebuild and/or update the tables.

What specialized syntax for materialized views and related configuration provides is some combination of clarity of intent, developer convenience, performance improvements over the most naïve update implementations, and additional information for the planner to use to optimize queries.

Postgres current level of support for named-as-such MVs provides basically the first two benefits for certain situations (and a little bit of the third in certain situations via REFRESH MATERIALIZED VIEW CONCURRENTLY.)