News for hackers. Stuff that matters.
Re:EventReduce: An algorithm to optimize database queries th... by pgt on Thursday April 16, 2020 @07:42AM (#22888396)
Materialize exists to efficiently solve the view maintenance problem: https://materialize.io/
Re:EventReduce: An algorithm to optimize database queries th... by fahrradflucht on Thursday April 16, 2020 @08:14AM (#22888579)
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

Re:EventReduce: An algorithm to optimize database queries th... by scott_s on Thursday April 16, 2020 @09:29AM (#22889119)
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+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.

Re:EventReduce: An algorithm to optimize database queries th... by j-pb on Thursday April 16, 2020 @09:41AM (#22889230)
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?

Re:EventReduce: An algorithm to optimize database queries th... by pgt on Thursday April 16, 2020 @10:13AM (#22889547)
It should be able to aggregate across multiple tables, but it might need intermediate views. Can you give an example?
Re:EventReduce: An algorithm to optimize database queries th... by j-pb on Thursday April 16, 2020 @11:04AM (#22890108)
"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.

Re:EventReduce: An algorithm to optimize database queries th... by hinkley on Monday April 20, 2020 @03:13PM (#22927257)
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.

Re:EventReduce: An algorithm to optimize database queries th... by eventreduce on Thursday April 16, 2020 @07:48AM (#22888431)
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.