#database #question Is there anything other than MSSQL which supports *synchronized* materialized views ?
I want to have a view on a table, computing it will be slow so I don't want it recomputed every SELECT, however it is not ok for it to be desynchronized from the underlying data, and when I issue a COMMIT, I want the table and all views to be atomically updated together.
Ok so I found an answer, apparently Oracle supports this via CREATE MATERIALIZED VIEW UPDATE FAST ON COMMIT. The UPDATE FAST makes it use logs to update the view rather than rebuilding from scratch every time and the ON COMMIT makes it update whenever the underlying tables are edited.
I suppose I should have expected the big red monster to have this feature...
Not sure how I missed this until now https://www.altinity.com/blog/clickhouse-materialized-views-illuminated-part-1 BRB installing clickhouse...
@cjd I don't know, but I've boosted it for greater reach.
@cjd it's possible to do with triggers in postgres afaik
@cjd upon rereading, this is absolutely doable in postgres with triggers. I did exactly this even.
@piecritic I'm assuming that the base table is big enough that a REFRESH MATERIALIZED VIEW on each update will become a performance bottleneck and that the materialized views themselves are fairly complicated queries such as sums over subsets, creating a high risk that manual bookkeeping with triggers will go out of sync.
@cjd for me, eventually consistent was enough. If you need it to be absolutely atomic, i would assume manual bookkeeping in some way is the only sane option.
@piecritic Apparently (I have not really verified), MSSQL supports this use case.
It is impossible to say without knowing the particulars of your case, but what you describe in https://mastodon.social/@cjd/103241205824051891 a priori is perfectly doable in #Postgresql (no, you wouldn't be rebuilding the materialised view from scratch on every commit, just adding/deleting/updating rows as necessary).
Server run by the main developers of the project It is not focused on any particular niche interest - everyone is welcome as long as you follow our code of conduct!