Fl4m3Ph03n1x

Fl4m3Ph03n1x

Ecto multiple streams in 1 transaction

Background

PS: the following situation describes an hypothetical scenario, where I own a company that sells things to customers.

I have an Ecto query that is so big, that my machine cannot handle it. With billions of results returned, there is probably not enough RAM in the world that can handle it.

The solution here (or so my research indicates) is to use streams. Streams were made for potentially infinite sets of results, which would fit my use case.

https://hexdocs.pm/ecto/Ecto.Repo.html#c:stream/2

Problem

So lets imagine that I want to delete All users that bought a given item. Maybe that item was not really legal in their country, and now me, the poor guy in IT, has to fix things so the world doesn’t come down crashing.

Naive way:

item_id = "123asdasd123"

purchase_ids =
      Purchases
      |> where([p], p.item_id == ^item_id)
      |> select([p], p.id)
      |> Repo.all()

Users
    |> where([u], u.purchase_id in ^purchase_ids)
    |> Repo.delete_all()

This is the naive way. I call it naive, because of 2 issues:

  • We have so many purchases, that the machine’s memory will overflow (looking at purchase_ids query)
  • purchase_ids will likely have more than 100K ids, so the second query (where we delete things) will fail as it hits Postgres parameters limit of 32K: https://stackoverflow.com/a/42251312/1337392

What can I say, our product is highly addictive and very well priced!
Our customers simply cant get enough of it. Don’t know why. Nope. No reason comes to mind. None at all.

With these problems in mind, I cannot help my customers and grow my empire, I mean, little home owned business.

I did find this possible solution:

Stream way:

item_id = "123asdasd123"

purchase_ids =
      Purchases
      |> where([p], p.item_id == ^item_id)
      |> select([p], p.id)

stream = Repo.stream(purchase_ids)

Repo.transacion(fn -> 
  ids = Enum.to_list(stream)

  Users
    |> where([u], u.purchase_id in ^ids)
    |> Repo.delete_all()
end)

Questions

However, I am not convinced this will work:

  • I am using Enum.to_list and saving everything into a variable, placing everything into memory again. So I am not gaining any advantage by using Repo.stream.
  • I still have too many ids for my Repo.delete_all to work without blowing up

I guess the one advantage here is that this now a transaction, so either everything goes or nothing goes.

So, the following questions arise:

  • How do I properly make use of streams in this scenario?
  • Can I delete items by streaming parameters (ids) or do I have to manually batch them?
  • Can I stream ids to Repo.delete_all ?

Marked As Solved

Fl4m3Ph03n1x

Fl4m3Ph03n1x

Every question post created here creates an entry in a dedicated thread in the official forum iirc. Nonetheless, I still post my questions in both places. And when I find an answer, I add it to both places as well.

I do this mainly for visibility, both for the community, and for the question itself, although the later one is less impactful due to the mentioned DevChat thread the official forum has.

Solutions

In regards to the question, there are two possible solutions.

One suggested by benwilson:

query = from u in Users,
  join: p in assoc(u, :purchase),
  where: p.item_id == ^item_id

Repo.delete_all(query)

And the other by Aleksei Matiushkin:

Repo.transacion(fn ->
  max_rows = 500

  purchase_ids
  |> Repo.stream(max_rows: max_rows)
  |> Stream.chunk_every(max_rows)
  |> Stream.each(fn ids ->
     Users
     |> where([u], u.purchase_id in ^ids)
     |> Repo.delete_all()
  end)
  |> Stream.run()
end, timeout: :infinity)

My pick

The first solution is great, but it requires the User Schema to have a belongs_to :purchase, Purchase definition in its schema. Unfortunately for me, this was a deal breaker, since changing any schemas in the project where I am working in is either not allowed or would result in a lengthy approval process.

So I went with the second solution that is self contained. It requires no changes to any schemas and it can work with the data as is.

Also Liked

jaeyson

jaeyson

hi @Fl4m3Ph03n1x, this might be spammy but, have you tried to ask this via ElixirForums or slack? so other people can see this.

Popular Backend topics Top

pillaiindu
Cross posting from HashNode. A friend of mine is creating Uber-like app for a small company with 200 to 1000 cars. The app will operate ...
New
gagan7995
API 4 Path: /user/following/ Method: GET Description: Returns the list of all names of people whom the user follows Response [ { ...
New
bsek43
Hello everyone, I’ve started learning Elixir and Phoenix few months ago and while I mostly grasped Elixir’s functional concepts and Phoe...
New
Fl4m3Ph03n1x
Background I am now trying Gradual type checking, as a consequence I am giving a shot to Gradient. As I see it, this is an alternative to...
New
Fl4m3Ph03n1x
Background I am trying to up my Functional Programming (FP) skills and one of the things that newcomers first learn in FP is the Option T...
New
Fl4m3Ph03n1x
Background I am trying to encode a structure into json format using the Jason library. However, this is not working as expected. Code L...
New
AstonJ
If when trying to create (or recreate) your dev db with rails db:create you are getting: PG::ConnectionBad: connection to server on soc...
New
Fl4m3Ph03n1x
Background I have to queries that return a colossal amount of data on their own. I cannot use Repo.all as doing so would materialize thes...
New
sona11
I studied very basic PHP (I believe). After that, I feel like I’ve gotten a handle on the language. My dream is to work as a web develope...
New
pillaiindu
Currently reading the book “Programming Phoenix LiveView”. At the end of the Chapter 1, I’m trying to solve the guess game. If the user ...
New

Other popular topics Top

AstonJ
A thread that every forum needs! Simply post a link to a track on YouTube (or SoundCloud or Vimeo amongst others!) on a separate line an...
New
AstonJ
poll poll Be sure to check out @Dusty’s article posted here: An Introduction to Alternative Keyboard Layouts It’s one of the best write-...
New
Exadra37
I am asking for any distro that only has the bare-bones to be able to get a shell in the server and then just install the packages as we ...
New
foxtrottwist
A few weeks ago I started using Warp a terminal written in rust. Though in it’s current state of development there are a few caveats (tab...
New
mafinar
This is going to be a long an frequently posted thread. While talking to a friend of mine who has taken data structure and algorithm cou...
New
First poster: joeb
The File System Access API with Origin Private File System. WebKit supports new API that makes it possible for web apps to create, open,...
New
PragmaticBookshelf
Author Spotlight Erin Dees @undees Welcome to our new author spotlight! We had the pleasure of chatting with Erin Dees, co-author of ...
New
First poster: bot
Large Language Models like ChatGPT say The Darnedest Things. The Errors They MakeWhy We Need to Document Them, and What We Have Decided ...
New
PragmaticBookshelf
Author Spotlight: Peter Ullrich @PJUllrich Data is at the core of every business, but it is useless if nobody can access and analyze ...
New
AstonJ
This is cool! DEEPSEEK-V3 ON M4 MAC: BLAZING FAST INFERENCE ON APPLE SILICON We just witnessed something incredible: the largest open-s...
New