Materialized views that are updated efficiently when new rows are added or rows updated would be a really great feature to have in core Postgres. This certainly would be useful outside of timeseries data.

I think this is what you're looking for:

https://wiki.postgresql.org/wiki/Incremental_View_Maintenanc...

https://yugonagata-pgsql.blogspot.com/2022/05/pgivm-v10-rele...

aidos

Interesting. I check back on the wiki page every couple of years to see what progress is being made.

The basic idea seems to be to track the primary keys of the base tables in the incremental view and then use triggers to update those rows when source rows are updated.

The meat of the project is over here for anyone that’s interested - in particular this section about the limitations is pretty interesting (and expected).

https://github.com/sraoss/pg_ivm/#supported-view-definitions...