5 ms·

Materialize exists to efficiently solve the view maintenance problem: https://materialize.io/ https://materialize.io/

by pgt 7y ago

eventreduce 7y ago
Thanks for pointing that out. I never heard of materialize.io but I will dive into it. On the first glance it looks like materialize is more like a full product while EventReduce is just something that you use on top of your existing solutions.
fahrradflucht 7y ago
There is also noria [0] which is basically a SQL database with integrated materialized views with memcached like perf. - But I think it is more in a research state. [0]: https://github.com/mit-pdos/noria https://github.com/mit-pdos/noria
- scott_s 7y ago
  Any database which offers what are called "continuous queries" also solves this problem. I'm glad the ACM Digital Library is open right now, because I can just point to: https://dl.acm.org/action/doSearch?AllField=continuous+queries https://dl.acm.org/action/doSearch?AllField=continuous+queri... The work in general purpose streaming grew out of the continuous query work in the early 2000s. Marrying truly general purpose streaming with transactional databases is not a solved problem (as opposed to transactional databases providing streams), but the notion of continuously running database queries has been around for a long time.
j-pb 7y ago
They also allow for arbitrary joins. Basically every valid SQL query can be turned into a stream by Materialize which is pretty amazing. This algorithm is limited to single table aggregates I think?
- pgt 7y ago
  It should be able to aggregate across multiple tables, but it might need intermediate views. Can you give an example?
  - j-pb 7y ago
    "EventReduce can be used with relational databases but not on relational queries that run over multiple tables/collections. (you can use views as workarround so that you can query over only one table). In theory Event-Reduce could also be used for realational queries but I did not need this for now. Also it takes about one week on an average machine to run all optimizations, and having more state functions looks like an NP problem." And this doesn't even take into account recursive queries. It's to bad that the EventReduce algorithm doesn't come with a paper, I'd love to compare the two. If EventReduce needs joins to be delegated to views then it doesn't solve the really hard parts of view maintenance. Interleaving nested joins and aggregates also wouldn't be possible, because the aggregation and join live in separate worlds. And a lot of queries actually have that property. I guess you could push down all joins into a single megatable and then group-by the living hell out of stuff to get back the structure but that would create an O(insane) cartesian product from your database. Materialize on the other hand is basically the holy grail of view maintenance. It supports ANSI SQL, which is a lot, and it's using the culminated work on differential dataflow and timely dataflow. Also it's written in Rust. My prediction is in 10 years we'll all be using it in our backends and frontends. You'll stream live updates from your sql database to your frontent where it will completely replace React and Js with Rust and a local db that has the UI as a maintained view.
hinkley 6y ago
It seems to me that I want heterogeneous replicas of my databases. Partial indexes on this copy, full indexed on this one, no deletes on this one. But a simpler solution might be to have a standard WAL format, and use that as a basis for having replicas of the system of record be a first class citizen in my data center instead of something we all cobble together or pay for.

Materialize exists to efficiently solve the view maintenance problem: https://materialize.io/ https://materialize.io/

by pgt 7y ago

Materialize exists to efficiently solve the view maintenance problem: https://materialize.io/ https://materialize.io/

Thanks for pointing that out. I never heard of materialize.io but I will dive into it. On the first glance it looks like materialize is more like a full product while EventReduce is just something that you use on top of your existing solutions.

fahrradflucht 7y ago

There is also noria [0] which is basically a SQL database with integrated materialized views with memcached like perf. - But I think it is more in a research state. [0]: https://github.com/mit-pdos/noria https://github.com/mit-pdos/noria

scott_s 7y ago
Any database which offers what are called "continuous queries" also solves this problem. I'm glad the ACM Digital Library is open right now, because I can just point to: https://dl.acm.org/action/doSearch?AllField=continuous+queries https://dl.acm.org/action/doSearch?AllField=continuous+queri... The work in general purpose streaming grew out of the continuous query work in the early 2000s. Marrying truly general purpose streaming with transactional databases is not a solved problem (as opposed to transactional databases providing streams), but the notion of continuously running database queries has been around for a long time.

j-pb 7y ago

They also allow for arbitrary joins. Basically every valid SQL query can be turned into a stream by Materialize which is pretty amazing. This algorithm is limited to single table aggregates I think?

pgt 7y ago
It should be able to aggregate across multiple tables, but it might need intermediate views. Can you give an example?
- j-pb 7y ago
  "EventReduce can be used with relational databases but not on relational queries that run over multiple tables/collections. (you can use views as workarround so that you can query over only one table). In theory Event-Reduce could also be used for realational queries but I did not need this for now. Also it takes about one week on an average machine to run all optimizations, and having more state functions looks like an NP problem." And this doesn't even take into account recursive queries. It's to bad that the EventReduce algorithm doesn't come with a paper, I'd love to compare the two. If EventReduce needs joins to be delegated to views then it doesn't solve the really hard parts of view maintenance. Interleaving nested joins and aggregates also wouldn't be possible, because the aggregation and join live in separate worlds. And a lot of queries actually have that property. I guess you could push down all joins into a single megatable and then group-by the living hell out of stuff to get back the structure but that would create an O(insane) cartesian product from your database. Materialize on the other hand is basically the holy grail of view maintenance. It supports ANSI SQL, which is a lot, and it's using the culminated work on differential dataflow and timely dataflow. Also it's written in Rust. My prediction is in 10 years we'll all be using it in our backends and frontends. You'll stream live updates from your sql database to your frontent where it will completely replace React and Js with Rust and a local db that has the UI as a maintained view.

hinkley 6y ago

It seems to me that I want heterogeneous replicas of my databases. Partial indexes on this copy, full indexed on this one, no deletes on this one. But a simpler solution might be to have a standard WAL format, and use that as a basis for having replicas of the system of record be a first class citizen in my data center instead of something we all cobble together or pay for.