Comment by zozbot234
11 hours ago
It would be nice to see out-of-the-box support in PostgreSQL for what's known as incremental view maintenance. It's very much an index in that it gets updated automatically when the underlying data changes, but it supports that for arbitrary views - not just special-cased like ordinary database indexes.
A hard problem, especially wrt to transactions on a moving target.
From memory, handful of projects just dedicated to this dimension of databases: Noria, Materialize, Apache Flink, GCP's Continuous Queries, Apache Spark Streaming Tables, Delta Tables, ClickHouse streaming tables, TimescaleDB, ksqlDB, StreamSQL; and dozens more probably. IIRC, since this is about postgres, there is recently created extension trying to deal with this: pg_ivm
If you have timeseries data TimescaleDB has this with continuous aggregates