tirana

tirana

How to split data into equal parts properly to process them evenly?

Let’s say:

  • I have 1 millions of records in a DB and I want to process them daily via something like Oban.
  • there’s an Oban job which is executed hourly
  • an Oban job is able to process around 70k-200k hourly
  • it can’t be known in advance how many it’ll process each tiime

The key here is to process the records evenly, that is, simply running one job per 24 hours to have it process all the 1 mln of records at once, one by one, won’t do for me.

How would I split the records into parts and also memorize how many of them have been processed so far, daily? Meaning, in an easy manner.

I could create a DB table with a counter → id, date_time, counter
But it appears to be an overkill.

Won’t there be a better way?

Most Liked

benwilson512

benwilson512

Author of Craft GraphQL APIs in Elixir with Absinthe

Great question! The basic principle we’re gonna use here is remainder math. At a basic level, you could spawn two jobs, one of which handled records that had even numbered ids, and another job that handled odd numbered ids. If you want to do more than just two jobs you can extend this concept by using the modulus operator %.

If you want to do this on a cron, my recommendation is to basically do a two phase job enqueuing process.

Phase 1: A regular Oban job that is configured to run hourly. That job spawns say 10 jobs to do the actual work, and it gives each job a number between 1 and 10 in the args, as well as the total number (for easier config)

for i <- 1..total do
PartitionJob.new(%{partition: i, total: total) |> Oban.insert!
end

Phase 2: The partition job runs, and it takes the arg given to it and queries your table of records.

SomeSchema
|> where([t], fragment("? % ? = ?", t.id, ^args["total"], ^args["partition"]))
|> Repo.all

And there you go! If a job is given say partition 7 then it will get all rows where schema.id % 10 = 7 which will be roughly 1 10th of the rows.

The main upside to this approach is that you have a ton of control over the transactional characteristics because all of the relevant info ends up in the DB. It also handles node failure or cluster size changes reliably due to Oban unique jobs.

Eiji

Eiji

Then why not using something similar, but in memory like :ets table?

Eiji

Eiji

Is it a good practice? I have no idea about oban, but in worst case it could take even 100% of rows as long as there is possibility to call Repo.delete/1 on SomeSchema and 90% rows are deleted. Of course nobody expects deletion of 90% rows in real case - I wrote that just to show the problem by providing an exaggerated example.

What may be worth to mention is that all rows matching schema.id % 10 = 7 could be in worst case all first or all last rows, so sort call with such code may have an unexpected behaviour.

If possible I would advice to use ROW_NUMBER() function which should solve be better solution for PostgreSQL database. Regardless of how your data looks, how much is deleted and finally how you sort them it would always work exactly the same way. For more information please take a look at:

  1. Window Functions documentation
  2. PostgreSQL ROW_NUMBER Function tutorial

Last Post!

benwilson512

benwilson512

Author of Craft GraphQL APIs in Elixir with Absinthe

If you’re using auto incrementing keys then it is more or less sequential. If you’re splitting into N partitions, you would need a delete pattern that favored one specific value of N more than others. As I said in the earlier post it’s trivial to just go query your database to see if that is happening in your case: select id % 10, count(id) from table group by 1.

@billylanchantin is dead on with the uuid answer. Basically what we’re using % here for is a kind of “consistent hash” which reliably turns a given input value into one of a smaller set of output values. % is a very efficient way of doing this for integers, but there are other consistent hashing functions that work on other values too.

Where Next?

Popular in Questions Top

JeremM34
Hello, how can I check the Phoenix version ? Thanks !
New
PeterCarter
There are pre-rolled solutions for other frameworks that do work. However, Phoenix does not seem to have these. Have people had good expe...
New
nsuchy
Hi. I’ve noticed that Windows Powershell has it’s own IEX command and you cannot access Elixir’s IEX due to the conflict. This isn’t a cr...
New
joeerl
Hello again - after a longish gap I’ve decided I really must dig into Elixir and see what’s been happening here - so I have a few questio...
New
fireproofsocks
Forgive me if this is obvious, but how does one delete a database record WITHOUT selecting it first? Ecto.Repo — Ecto v3.14.0 has exampl...
New
fayddelight
I tried installing elixir 1.11.2 erlang 23.3.4 via asdf in my zsh shell. Enabled the versions locally and globally. When I list them ...
New
JorisKok
I have a server on AWS, and was running a load test using artillery. When looking at the Phoenix dashboard I see the Ports going to 100% ...
New

Other popular topics Top

lanycrost
Hi everyone! I need implement if…else if…else condition from my elixir code, and anymore of this control flow structures not work proper...
New
aadeshere1
I have a another noob question about loop. Since elixir is immutable, while loop is not directly possible. total = 10 while total != 0 ...
New
joeerl
Hello again - after a longish gap I’ve decided I really must dig into Elixir and see what’s been happening here - so I have a few questio...
New
AstonJ
Seen any cool LiveView demos, sample apps or examples? Please post them here! :003:
New
JorisKok
I have a server on AWS, and was running a load test using artillery. When looking at the Phoenix dashboard I see the Ports going to 100% ...
New
jason.o
In the code below, if the create action is not set to accept “extra_key” as an input, it errors out with a message shown above. Is there ...
New

We're in Beta

About us Mission Statement