4 ms·

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

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.

djk447 4y ago
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.
Tostino 4y ago
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.
Nelkins 4y ago
I think this is what you're looking for: https://wiki.postgresql.org/wiki/Incremental_View_Maintenance https://wiki.postgresql.org/wiki/Incremental_View_Maintenanc... https://yugonagata-pgsql.blogspot.com/2022/05/pgivm-v10-released.html https://yugonagata-pgsql.blogspot.com/2022/05/pgivm-v10-rele...
- aidos 4y ago
  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-and-restriction https://github.com/sraoss/pg_ivm/#supported-view-definitions...
code_biologist 4y ago
https://materialize.com/ https://materialize.com/ is billed to be that. That behavior is not trivial to implement.
- ignoramous 4y ago
  See also: readyset.io based on Noria: https://news.ycombinator.com/item?id=30922082 https://news.ycombinator.com/item?id=30922082

djk447 4y ago

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.

Tostino 4y ago

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.

Nelkins 4y ago

I think this is what you're looking for: https://wiki.postgresql.org/wiki/Incremental_View_Maintenance https://wiki.postgresql.org/wiki/Incremental_View_Maintenanc... https://yugonagata-pgsql.blogspot.com/2022/05/pgivm-v10-released.html https://yugonagata-pgsql.blogspot.com/2022/05/pgivm-v10-rele...

aidos 4y ago
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-and-restriction https://github.com/sraoss/pg_ivm/#supported-view-definitions...

code_biologist 4y ago

https://materialize.com/ https://materialize.com/ is billed to be that. That behavior is not trivial to implement.

ignoramous 4y ago
See also: readyset.io based on Noria: https://news.ycombinator.com/item?id=30922082 https://news.ycombinator.com/item?id=30922082