Phoenix |> Ecto.preload (can be) EVIL 😱!
This is the second post of a series on different topics about Phoenix development, you can access part one here.
Today we're going to talk about how using Ecto's `preload` can be somewhat evil. This is a learning I stumbled upon when suddenly a query that was running fine started killing our servers 😱 — so lets hop into it.
If you're not familiar with preloads…
They are that amazing and useful thing Ecto provides so that we can load associations into our structs, here is a simple example:
```Post
|> where([p], p.id in ^post_ids
|> preload([:comments, :user])
|> Repo.all()```
This will fetch all the posts inside post_ids and preload all the associated comments and user. That's all good, and I love how Ecto makes it so explicit — other languages will do preloads whenever you try to access an association like post.comments — but what is the underlying cost of this?
First cost: the query itself
The first cost should be fairly obvious, you'll end up with a heavier query to load all the comments. This is something that you should be mindful about, but I'm guessing most developers are and usually does not represent a problem by itself.
Second (and sometimes hidden) cost: holding complex structs in memory
Ok, so now you have all your posts and comments loaded, right? Maybe you want to display comments, or even just a post with the first few comments… So why is it important to keep whatever you load minimal? Let's look into a real example. This is a (very simple) query that translates to complex structs:
So what happened there? Our server memory just jumped from ~70mb to ~200mb! Like I said, this was a very simple query in our production servers that actually ended up killing the server (we were experimenting with # of machines vs size, and the available memory was very low, but that’s another topic, for another time…) — the first time we saw this, we were so confused. Our whole DB was less than 50mb, how come 1 query could spike that much memory? … and then it hit us, this was probably the underlying cost of holding complex structures (lists of structs that have many associations preloaded).
Although the focus of the article is more about Phoenix, this is a problem that could potentially happen in any framework/language. The lesson learned here is that just like when writing functions, we should keep query’s and structs minimal, just enough to perform the given job.
How to fix it?
You probably already guessed it. It’s as simple as using a select statement in our query, check this example from https://elixirforum.com/t/selecting-fields-in-preloaded-joins-ecto/994/3:
```post_query =
from p in Post, where: p.id == ^id,
join: c in assoc(p, :comments),
join: u in assoc(c, :user),
select: %{id: p.id, title: p.title, comments: p.comments, name: u.name}```
Note that here what's being optimized is the user and not the comments — we're only saving the user name in our final struct. To get better results you might need to tweak your queries and find out what complex struct is the culprit of the memory increase.
Another interesting thing to keep in mind is that in this case we're using a join and not preloads — in some cases it'll not be acceptable to use joins at all , and for those you should either write a separate query or use fragments to get the desired end result. I have not yet found a way (only using ecto) to use preloads and select fields in a nested association, hence they can be dangerous.
Cool! So let’s now see how our optimized query looks like:
Much better, right? From ~200mb to only ~20mb… Aaaand that’s it for our second article! Next time we can talk about the famous GenServers and how we used it to keep track of unique ephemeral data in our app. Know a better way to avoid this? Any other optimizations? I'd love to hear! Meanwhile, see you next time!