News for hackers. Stuff that matters.
Re:Postgres is eating the database world by TOMDM on Friday March 15, 2024 @01:38AM (#39712373)
Postgres is such a great tool.

The feature I'd love to see added that has been kicking around the mailing list for ages now would be incremental view maintenance.

Being able to keep moderately complex analysis workloads fresh in realtime would be such a boon.

Re:Postgres is eating the database world by iTokio on Friday March 15, 2024 @02:26AM (#39712591)
Is this feature even possible? How can the database unravel a complex queries with derived data to minimal updates from its normalized parts?

The easy way is to rebuild everything if any “from table” as been modified.

The manual way is to create triggers that perform the minimal updates.

Re:Postgres is eating the database world by benjaminwootton on Friday March 15, 2024 @02:30AM (#39712608)
There are lots of projects who have managed to achieve this.

Streaming frameworks like Kafka Streams and Flink have incrementally updating tables in memory.

Materialize is built around the concept with a Postgres compatible API.

ClickHouse materialized views act like insert triggers which update when the base table is updated.

Re:Postgres is eating the database world by pgwhalen on Wednesday March 20, 2024 @03:48PM (#39771204)
I'm not familiar with ClickHouse materialized views, but the other tech you list (as I roughly understand them) seem more concerned with streaming SQL, which is a related but different end user experience from incrementally refreshed materialized views.
Re:Postgres is eating the database world by shpongled on Friday March 15, 2024 @02:33AM (#39712625)
I believe differential data flow techniques (Materialize, Noria) enable this kind of stuff
Re:Postgres is eating the database world by serpix on Friday March 15, 2024 @02:20AM (#39712562)
Re:Postgres is eating the database world by pgwhalen on Wednesday March 20, 2024 @03:44PM (#39771162)
pg_ivm has a ton of restrictions though (as do other PG incremental view refresh mechanisms, like timescale hypertables).

That's why I assume it's not in vanilla postgres: adding the future with so many caveats would not make for a great experience for the full breadth of postgres users.

Re:Postgres is eating the database world by Izkata on Friday March 15, 2024 @02:01AM (#39712475)
Materialized views with pg_cron to refresh them? (Or even just cron and your usual interface, if you don't want to install something extra)
Re:Postgres is eating the database world by TOMDM on Friday March 15, 2024 @02:12AM (#39712519)
This solves the same problem, just not as well, the idea of an incrementaly maintained view is that only the update needs to be computed, so a count will increment or decrement as rows are inserted or deleted.

It means complex views that could take minutes or hours to calculate from scratch can be kept fresh in realtime.

Re:Postgres is eating the database world by physicles on Friday March 15, 2024 @10:36AM (#39716240)
I’ve built a few of these in snowflake for time series data (with dbt, which is fine but I don’t love it). My feeling so far is that the incremental bits are kinda fiddly and often domain-specific. Is it possible to define a single “shape” that would solve >90% of these incremental refresh scenarios?
Re:Postgres is eating the database world by salojoo on Friday March 15, 2024 @02:20AM (#39712564)
Re:Postgres is eating the database world by dbacar on Friday March 15, 2024 @02:25AM (#39712586)
Did you used this in production, how do you rate it?
Re:Postgres is eating the database world by zozbot234 on Friday March 15, 2024 @11:11AM (#39716642)
An "incremental view" is just an index over some custom query. So you're pretty much asking for improvements in Postgres' index support.
Re:Postgres is eating the database world by kingraoul on Friday March 15, 2024 @05:35PM (#39720973)
He means a materialized view, I believe.
Re:Postgres is eating the database world by antifa on Sunday March 17, 2024 @11:56AM (#39735314)
MERGE was interested in 15, if that's helpful: https://www.postgresql.org/docs/current/sql-merge.html