News for hackers. Stuff that matters.
Re:How we made data aggregation on PostgreSQL better and faster by fabian2k on Tuesday June 21, 2022 @10:31AM (#31824368)
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.
Re:How we made data aggregation on PostgreSQL better and faster by Nelkins on Tuesday June 21, 2022 @11:54AM (#31825381)
Re:How we made data aggregation on PostgreSQL better and faster by aidos on Tuesday June 21, 2022 @02:50PM (#31827577)
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...

Re:How we made data aggregation on PostgreSQL better and faster by djk447 on Tuesday June 21, 2022 @10:40AM (#31824459)
NB - Timescale person here. Totally true! It's also a much harder problem :) One of the things that we try to focus on at Timescale is figuring out how we can simplify problems based on the specific needs of time-series data. Postgres has to solve things for very general cases, and sometimes that just is much harder. And then they often won't work all that well for time-series, because they're not all that optimized for them.
Re:How we made data aggregation on PostgreSQL better and faster by code_biologist on Tuesday June 21, 2022 @02:50PM (#31827575)
https://materialize.com/ is billed to be that. That behavior is not trivial to implement.
Re:How we made data aggregation on PostgreSQL better and faster by ignoramous on Tuesday June 21, 2022 @03:35PM (#31828170)
See also: readyset.io based on Noria: https://news.ycombinator.com/item?id=30922082
Re:How we made data aggregation on PostgreSQL better and faster by Tostino on Tuesday June 21, 2022 @11:43AM (#31825229)
Yeah, that's been one of my most wished-for features throughout the years. There is an extension providing some limited support for this, but it's far from what would be usable for any project where I have that need. Just too limited in what it supports. I need complicated queries, aggregates, etc.