News for hackers. Stuff that matters.
Re:Lesser Known Postgres Features by zepearl on Tuesday August 29, 2023 @01:14PM (#37310916)
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/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)

Re:Lesser Known Postgres Features by sseagull on Tuesday August 29, 2023 @01:22PM (#37311032)
This is being worked on if I recall:

https://github.com/sraoss/pg_ivm

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

Re:Lesser Known Postgres Features by jjgreen on Tuesday August 29, 2023 @03:00PM (#37312288)
Nice! I did this "by hand" for a dozen views a few years back, fiddly but rewarding; if I have to do it again ...
Re:Lesser Known Postgres Features by janee on Tuesday August 29, 2023 @08:59PM (#37316302)
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