This is being worked on if I recall:
Re:Lesser Known Postgres Features
by paulddraper on Tuesday August 29, 2023 @02:42PM
(#37312058)
"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
Nice! I did this "by hand" for a dozen views a few years back, fiddly but rewarding; if I have to do it again ...
We'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
Re:Lesser Known Postgres Features
by atomically on Tuesday August 29, 2023 @09:33PM
(#37316565)
Check out https://readyset.io as well β itβs an incremental view maintenance system
(the same like in Oracle - 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)