4 ms·
I'd probably love to have the "REFRESH ON COMMIT"-feature for materialized views, to refresh (only the) impacted rows automatically whenever rows of the source
by zepearl 3y ago
I'd probably love to have the "REFRESH ON COMMIT"-feature for materialized views, to refresh (only the) impacted rows automatically whenever rows of the source table(s) change :)
(the same like in Oracle - https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-MATERIALIZED-VIEW.html https://docs.oracle.com/en/database/oracle/oracle-database/1... - but I would not need the fancy "query rewrite" feature that make SQLs use the matView even if they don't explicitly mention it in their SQL)
- sseagull 3y agoThis is being worked on if I recall: https://github.com/sraoss/pg_ivm https://github.com/sraoss/pg_ivm
- paulddraper 3y ago"Incremental view maintenance" Lots and lots of theory about how to do this correctly. I authored a project to do this with triggers. https://github.com/rivethealth/denorm https://github.com/rivethealth/denorm
- jjgreen 3y agoNice! I did this "by hand" for a dozen views a few years back, fiddly but rewarding; if I have to do it again ...
- janee 3y agoWe've had success using DBT incremental models for this. Deletes aren't supported though, but for our use case that's fine and do a nighly refresh on the whole. Otherwise the best alternative would be something like https://materialize.com https://materialize.com
- atomically 3y agoCheck out https://readyset.io https://readyset.io as well — it’s an incremental view maintenance system