How To Do A Preload Good

Avoiding N+1 queries with Ecto's preload

Related Listening

Posted on 2024-11-15

I recently had this exchange on Twitter about using Ecto’s Repo.preload and I wanted to describe the way that we handle this at Appcues. Obviously everyone has their opinions, but this has served us very well for years, and we’ve never posted anything about it!

Hopefully this can help somebody out there.

The Problem

So, if you do something like Repo.get(queryable) |> Repo.preload(:association), you’re going to get a query for the original record, and then a query for each of the associated records. This is the classic N+1 query problem, and it’s not good.

How do you solve it? More functions!

The Solution

def get_thing(id, opts \\ []) do
  from(t in Thing, where: t.id == ^id)
  |> preload(opts[:preload])
  |> Repo.one()
end

defp preload(query), do: preload(query, true)
defp preload(query, nil), do: query

defp preload(query, true) do
  from q in query, 
    left_join: t in assoc(q, :thing),
    left_join: s in assoc(t, :sub_thing),
    preload: [
      thing: {t, [sub_thing: s]}
    ],
    order_by: [asc: t.index]
]
end

defp preload(query, preloads) do
  from q in query, preload: ^preloads
end

Edit: I made a mistake here originally. Thanks to @AtomKirk for pointing it out!

What I had originally was:

def preload(query, true) do
  from q in query, preload: [
    :association,
    other_assoc: [:sub_assoc]
  ]
end

However, this is incorrect. The correct way to do this is to use the left_join with assoc functions.

What I described originally was how Ash does it. In Ash, you’d do something like:

Ash.get!(Thing, id, load: [:association, other_assoc: [:sub_assoc]])

For a more explicit example, check out this code here