Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

EventReduce: An algorithm to optimize database queries that run multiple times

github.com

Post reply on HN
Print view View original

71–80 of 87 posts

Re: EventReduce: An algorithm to optimize database queries that run multiple times

#71

Post by scott_s »

eventreduce wrote: post #70
scott_s wrote: post #27

Earlier quoted context omitted.

What is that difference?

I recommend the video "Real-Time Databases Explained: Why Meteor, RethinkDB, Parse & Firebase Don't Scale" https://www.youtube.com/watch?v=HiQgQ88AdYo

That does not answer what differentiates your solution. I work on steaming systems. I am aware of the spectrum of online, latency aware data processing. But what I can tell from your solution is that the changes are coming from the database itself. Since, as I understand it, the database is the still the source of all data, I don’t see why your solution is any faster than continuous queries in a database.

Re: EventReduce: An algorithm to optimize database queries that run multiple times

#72

Post by danpalmer »

eventreduce wrote: post #68
danpalmer wrote: post #67

Earlier quoted context omitted.

My guess would be that if you're at a scale where you're thinking about these sorts of things, you are also at a scale where you're running on multiple machines. How does EventReduce share writes across the cluster?

EventReduce is an algorithm and not a database-wrapper. It will not care about your writes or if your database layer is a cluster and so also not affect them.

Sorry I wasn't clear in my original post.

I'm thinking about the application layer. If you have an application that writes data to a table, it's typical to run multiple instances of that application to support scale and reliability requirements.

If I send a write to one instance, how does it communicate and synchronise that write with the other application instances?

I ask because this can be a tricky thing to do, especially when consensus is required, as consensus algorithms such as Raft/Paxos require a number of network roundtrips which will introduce latency, and actually account for much of that latency in the database examples given in some cases.

Re: EventReduce: An algorithm to optimize database queries that run multiple times

#73

Post by anarazel »

LunaSea wrote: post #43
theandrewbailey wrote: post #39

Earlier quoted context omitted.

Unless there's some weird edge case that I'm not aware of, Postgres will execute what it's planner tells it to. Passing a query to EXPLAIN will show the plan.

Query plans are based on database statistics. Explain will calculate the query plan for a query. Explain analyze will calculate the query plan, run the query and compare the query plan expectations to reality. However, if statistics change, so does the query plan. So if you run a query then run the same query again with explain analyze, you don't have a guarantee of getting the same information back. And since explai…

> However, if statistics change, so does the query plan. So if you run a query then run the same query again with explain analyze, you don't have a guarantee of getting the same information back. And since explain analyze doesn't return the query results you are obligated to run two separated queries.

If that's a problem you can use auto_explain.

Re: EventReduce: An algorithm to optimize database queries that run multiple times

#74

Post by eventreduce »

danpalmer wrote: post #72
eventreduce wrote: post #68

Earlier quoted context omitted.

EventReduce is an algorithm and not a database-wrapper. It will not care about your writes or if your database layer is a cluster and so also not affect them.

Sorry I wasn't clear in my original post. I'm thinking about the application layer. If you have an application that writes data to a table, it's typical to run multiple instances of that application to support scale and reliability requirements. If I send a write to one instance, how does it communicate and synchronise that write with the other application instances? I ask because this can be a tricky thing to do, es…

EventReduce is a simple algorithm. It does not care or affect how you handle propagation of writes or how you handle your events, transactions or conflicts.

See it as a simple function that can do oldResults+event=newResults like shown in the big image on top of the readme.

Re: EventReduce: An algorithm to optimize database queries that run multiple times

#75

Post by eventreduce »

truth_seeker wrote: post #69
eventreduce wrote: post #59

Earlier quoted context omitted.

Yes I used it. I actually know it really well. I also did performance comparisons with mongodb and mongodbs change stream and cursors. What I posted here is just an algorithm. You could now compare it to mongodb (a product) and say it is a "more flexible solution" but I do not see the point in directly comparing it simply based on the documentation of both.

>> Yes I used it. I actually know it really well. I also did performance comparisons with mongodb and mongodbs change stream and cursors. Can you share the link for the code and data in Database against which you are querying to prove your claim ?

No and I also do not want to "claim" something. Feel free to do your own tests.

Re: EventReduce: An algorithm to optimize database queries that run multiple times

#76

Post by danpalmer »

eventreduce wrote: post #74
danpalmer wrote: post #72

Earlier quoted context omitted.

Sorry I wasn't clear in my original post. I'm thinking about the application layer. If you have an application that writes data to a table, it's typical to run multiple instances of that application to support scale and reliability requirements. If I send a write to one instance, how does it communicate and synchronise that write with the other application instances? I ask because this can be a tricky thing to do, es…

EventReduce is a simple algorithm. It does not care or affect how you handle propagation of writes or how you handle your events, transactions or conflicts. See it as a simple function that can do oldResults+event=newResults like shown in the big image on top of the readme.

This means then that if you run multiple application servers, which most do, that you’ll need to implement a data distribution mechanism of some sort.

I must admit, with limitations like this I’m struggling to figure out the use cases for this.

Edit: so I guess this is easier using the change subscriptions you mention in other comments. That does mean many subscribers, but hopefully that’s minimal load. This has the trade-off that it’s now eventually consistent, but I suppose that’s not a problem for many high read applications.

I’m still feeling like this could be solved in a simpler way with just simple data structures and a pub sub mechanism. Now I think of it, we do a similar thing with Redis for one service, and a custom Python server/pipeline in another, but we’ve never felt the need for this sort of thing.

Do you have more details about specific applications/use cases, and why this is better than alternatives?

Re: EventReduce: An algorithm to optimize database queries that run multiple times

#77

Post by Zaheer »

The goal of this is to reduce DB queries? Why not just queue up / batch writes? What benefits does this provide over application side batching of the event.

EvenReduce assumes there are no other systems interacting with the DB state (by using the old state that the current system saw). If there are no other systems, simple batching would work fine.

Re: EventReduce: An algorithm to optimize database queries that run multiple times

#78

Post by cryptonector »

How does this work for complex queries with sub-queries, LEFT OUTER JOINs, LATERAL JOINs, aggregation (DISTINCT/GROUP BY), window functions, CTEs, RECURSIVE CTEs?

I've a radically different approach: can the queries in question as VIEWs, materialize them, use triggers to update materializations where you can write those triggers easily and the updates are quick, or schedule an update where they're not.

If your RDBMS is very good about pushing WHERE constraints into VIEWs, and depending on how complex a VIEW query is, you might be able to make the update automatic by just querying the materialization's underlying VIEW with appropriate WHERE constraints from the ROWs being INSERTed/UPDATEd/DELETEd. You can tell which VIEWs might suitable for this by checking that the TABLE whose row the trigger is running for is a "top-level" table source for the VIEW's query: meaning a table source that's either the left side of a top-level LEFT JOIN, or either side of an INNER JOIN. If you can run a query on the VIEW with a timeout then you can just do that in the trigger and mark the materialization as needing an update if the query is too slow. Lastly, a scheduled or NOTIFYed job can run to perform any slower updates to a materialization.

Re: EventReduce: An algorithm to optimize database queries that run multiple times

#79

Post by eventreduce »

Zaheer wrote: post #77

The goal of this is to reduce DB queries? Why not just queue up / batch writes? What benefits does this provide over application side batching of the event. EvenReduce assumes there are no other systems interacting with the DB state (by using the old state that the current system saw). If there are no other systems, simple batching would work fine.

All your assumptions are wrong. Please read the other comments here or at least the readme of the repository. I will happily answer all ongoing questions you have afterwards.

Re: EventReduce: An algorithm to optimize database queries that run multiple times

#80

Post by eventreduce »

danpalmer wrote: post #76
eventreduce wrote: post #74

Earlier quoted context omitted.

EventReduce is a simple algorithm. It does not care or affect how you handle propagation of writes or how you handle your events, transactions or conflicts. See it as a simple function that can do oldResults+event=newResults like shown in the big image on top of the readme.

This means then that if you run multiple application servers, which most do, that you’ll need to implement a data distribution mechanism of some sort. I must admit, with limitations like this I’m struggling to figure out the use cases for this. Edit: so I guess this is easier using the change subscriptions you mention in other comments. That does mean many subscribers, but hopefully that’s minimal load. This has the…

I think the best example for why this is useful is described by david glasser at his talk about the oplog driver used in meteor.js https://www.youtube.com/watch?v=_dzX_LEbZyI
Post reply on HN