[CapriciousCptl]

As someone who basically sticks everything possible into Postgres, this is interesting! Streaming tools don't automatically cache things you need? I guess it's about time they do! Postgres, for instance, has a robust LRU mechanism that deals with OLTP quite competently. OLAP too if your indices are thought-out.

Also, although built-in materialized views don't allow partial updates in Postgres, you can get a similar thing with normal tables and triggers. Hashrocket discussed that strategy here-- https://hashrocket.com/blog/posts/materialized-view-strategi... .

[nchammas]

Of the traditional RDBMSs, I believe Oracle has the most comprehensive support for materialized views, including for incremental refreshes [0].

As early as 2004, developers using Oracle were figuring out how to express complex constraints declaratively (i.e. without using application code or database triggers) by enforcing them on materialized views [1].

It's quite impressive, but this level of sophistication in what materialized views can do and how they are used does not seem to have spread far beyond Oracle.

[0]: https://docs.oracle.com/database/121/DWHSG/refresh.htm#DWHSG...

[1]: https://tonyandrews.blogspot.com/2004/10/enforcing-complex-c...

[kthejoker2]

SQL Server has indexed views, which is "real time" refresh since it's a separate clustered index that's written to at the same time as the base tables.

https://www.sqlshack.com/sql-server-indexed-views/

[nchammas]

Do you know how sophisticated SQL Server is about updating indexed views? How granular are the locks when, for example, an indexed aggregate is updated? That will have a big impact on write performance when there are many concurrent updates.

A long time ago, I tried to use SQL Server indexed views to maintain a bank account balances table based on transaction history [0].

I forget what I ended up doing, but I remember that one of the downsides of using indexed views was that they didn't support any constraints. There are many restrictions on what you can and can't put in a SQL Server indexed view [1].

In this regard, I think Oracle has a more mature materialized view offering, though I personally haven't used Oracle much and don't know how well their materialized views work in practice.

[0]: https://dba.stackexchange.com/q/5608/2660

[1]: https://docs.microsoft.com/en-us/sql/relational-databases/vi...

[kthejoker2]

Well, indexed views aren't materialized views per se, they are a tradeoff between maintenance and deterministic performance.

A materialized view is nothing more than a snapshot cache, a one time ETL job. So it can abide by any constraints and is completely untethered from the data that created it. So you have to create your own maintenance cycle, including schema validation and any dynamic / non-deterministic aspects of the MV.

An indexed view is modified just like the clustered index of any tablr object upon which it depends, as an affected "partition" of the DML. That's what the SCHEMABINDING keyword is for, binding the view to any DML statements of its underlying base table(s).

So no need to maintain it at all, at the expense of conforming to a fairly rigid set of constraints to ensure that maintenance is ..umm ..maintainable.

In practice most views' logic are perfectly simpatico with the constraints of an indexed view - the tradeoff is write performance vs the "cache hit" of your view.

I do way more OLAP/HTAP engineering in my day job so indexed views are less common vs. Columnstores, but indexed views are a highly underutilized feature of SQL Server.

[gfody]

if sql server can't incrementally maintain your view then it doesn't let you index it. that's why there are so many restrictions but on the flip side if you manage to workaround the restrictions then decent update performance is assured.

[trollied]

As an ex-Oracle DBA, I really do miss native materialized view support in the likes of Postgres. They are /so/ powerful. People love to bash Oracle, but this is a good example of a feature that the open source DBs haven't got close to yet.