Ya, it’s postgres
rm-rf-etc
benwilson512
Author of Craft GraphQL APIs in Elixir with Absinthe
Excellent! So before we do any fancy Elixir things, your current code should really try to use INSERT ON CONFLICT which allows you to try to insert a value and, if one already exists, conditionally update it. Your stored procedure is basically inserting a summation row, but it first checks if one exists, and then adds to it. I feel like the native version is likely to perform much better.
200k over 2 hours is ~30 updates a second, which really ought to be doable in the DB.
I don’t want to discourage you from an Elixir solution but
will work fine until there is netsplit. Phoenix’s distributed tooling works great because it (aside from Presence) handles distributed communication but not distributed state. Distributed state is much, much harder because you have to deal with split brain, things coming apart and coming back together. Presence handles it, and it required some very fancy cutting edge stuff to make it work.
I still think there are solutions that the Elixir version could help with, depending on whether these sums can be slightly out of date or not, but all of those are going to involve choosing when to do a database update, not try to do the sums in Elixir.
2
rm-rf-etc
Probably it’s an ID in the URL of the client. In the rails code, it’s present in the context of wherever this function is being called.
0
benwilson512
Author of Craft GraphQL APIs in Elixir with Absinthe
OK. I’m not super familiar with how Rails and stored procedures work so I’m a bit lost on that point. Regardless, I’d still check out on conflict.
To explore this from another angle though, how important is it that these updates are exactly up to date? Can they be delayed by a few dozen seconds?
0
rm-rf-etc
Ya, definitely. EDIT: Actually, it might be important to not be delayed. I think we need to prevent future events if the sum hits a limit.
0
benwilson512
Author of Craft GraphQL APIs in Elixir with Absinthe
Cool, so at a minimum, you could replace the per-request summation with an update query that happens on an interval. You could also try to track which entries in your other table that you are trying to sum have been handled before or not.
I suppose my basic point here is this: This feels to me more like a database design problem, not a language problem. You are trying to sum rows in a database, the only way to do so and guarantee zero error is to do the summation in the database. The method ya’ll are doing right now is definitely inefficient, and I’m not surprised there are scaling issues. But before you break out a new language to solve your problem, I’d explore other options with the tools you have.
Elixir is awesome, and it can help with a lot or problems. At the end of the day though as long as the problem statement is “I want the values in these rows in table X to represent the totals of rows found in table Y” your database design and interaction pattern is going to matter far, far more than the language.
EDIT: In response to your edit: Try INSERT ON CONFLICT. In general my point about this being a database design issue remains important, particularly if you use those sums to make further database decisions.
0
rm-rf-etc
Thanks @benwilson512. This SO answer indicates that the code we’re using probably came from “the manual”.
Does the INSERT ON CONFLICT syntax still support IF found THEN RETURN; END IF;?
0
benwilson512
Author of Craft GraphQL APIs in Elixir with Absinthe
It makes that syntax irrelevant. The postgres documentation on it is very good: https://www.postgresqltutorial.com/postgresql-upsert/
EDIT: The SO link you provided in fact shows ON CONFLICT there in the top answer.
1
rm-rf-etc
Yup, I see it. I shared this link with the team.
I think I was confused what the IF found does. I think I understand now.
0