3 ms·

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 maintenan

by TOMDM 3y ago

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.

Izkata 3y ago
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)
- TOMDM 3y ago
  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.
  - physicles 3y ago
    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?
- salojoo 3y ago
  https://github.com/sraoss/pg_ivm https://github.com/sraoss/pg_ivm
serpix 3y ago
https://github.com/sraoss/pg_ivm https://github.com/sraoss/pg_ivm does what you need
- pgwhalen 3y ago
  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.
dbacar 3y ago
Did you used this in production, how do you rate it?
iTokio 3y ago
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.
- benjaminwootton 3y ago
  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.
  - pgwhalen 3y ago
    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.
- shpongled 3y ago
  I believe differential data flow techniques (Materialize, Noria) enable this kind of stuff
zozbot234 3y ago
An "incremental view" is just an index over some custom query. So you're pretty much asking for improvements in Postgres' index support.
- kingraoul 3y ago
  He means a materialized view, I believe.
antifa 3y ago
MERGE was interested in 15, if that's helpful: https://www.postgresql.org/docs/current/sql-merge.html https://www.postgresql.org/docs/current/sql-merge.html

by TOMDM 3y ago

Izkata 3y ago

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)

TOMDM 3y ago
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.
- physicles 3y ago
  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?
salojoo 3y ago
https://github.com/sraoss/pg_ivm https://github.com/sraoss/pg_ivm

serpix 3y ago

https://github.com/sraoss/pg_ivm https://github.com/sraoss/pg_ivm does what you need

pgwhalen 3y ago
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.

dbacar 3y ago

Did you used this in production, how do you rate it?

iTokio 3y ago

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.

benjaminwootton 3y ago
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.
- pgwhalen 3y ago
  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.
shpongled 3y ago
I believe differential data flow techniques (Materialize, Noria) enable this kind of stuff

zozbot234 3y ago

An "incremental view" is just an index over some custom query. So you're pretty much asking for improvements in Postgres' index support.

kingraoul 3y ago
He means a materialized view, I believe.

antifa 3y ago

MERGE was interested in 15, if that's helpful: https://www.postgresql.org/docs/current/sql-merge.html https://www.postgresql.org/docs/current/sql-merge.html