10 Ecto Query Patterns for N+1 and Beyond

TL;DR: Most Ecto N+1 posts are the same tutorial wearing a different hat — here’s preload, here’s why loops are bad, the end. What’s missing is the decision layer: which pattern for which shape of data, and why. The spine is one rule: separate queries (preload) for has_many, a join for belongs_to — because one avoids row-explosion and the other avoids an extra round trip you don’t need. The other nine patterns are what you reach for once that rule stops being enough: filtered preloads, nested trees, composable query modules, Dataloader when you don’t control the query shape, subqueries for “latest N per group,” denormalizing instead of joining, and raw SQL when Ecto’s DSL is fighting you harder than the problem is worth.

The gap every N+1 post skips

Search “Ecto N+1” and you’ll get a dozen competent walkthroughs of the same three moves: don’t call Repo.preload inside a loop, use preload: in your query, watch your LiveView handle_event callbacks for the same mistake. Scout Monitoring published a solid, current version of that post in April 2026, and it’s worth reading if you need the mechanics — preload variants, LiveView-specific N+1s, Oban worker N+1s, all covered well. I’m not rewriting it.

What none of these posts give you is the thing you actually need six months into a codebase: a rule for which pattern applies to which shape of query, made before you’re staring at a slow endpoint in production. Junior engineers on my teams don’t struggle with “what is preload” — they struggle with “why did the senior engineer just tell me to undo the preload I added and write a join instead.” That’s a judgment call, and nobody writes the judgment call down. So here it is, as ten decisions instead of ten syntax examples.

The Iron Law: separate queries for has_many, join for belongs_to

Start here, because everything else is an exception to this, and the exceptions make no sense until the rule is explicit.

A belongs_to association is one row to one row. Joining a post to its author doesn’t multiply anything — you get exactly as many result rows as you had posts, just with the author’s columns tacked on. There’s no cost to paying for that in the same round trip, and doing it in two queries is pure waste: an extra network hop for data you were always going to need in a 1:1 shape.

from p in Post,
  join: u in assoc(p, :author),
  preload: [author: u]

A has_many is the opposite shape. Join a post to its ten comments and Postgres gives you back ten rows — the post’s columns repeated ten times, once per comment. Do that across a page of fifty posts with variable comment counts and you’re now shipping a multiplied, duplicated result set over the wire just to reassemble it back into the tree Ecto was going to build for you anyway. That’s why plain preload: (no join) exists: it issues a second query — WHERE post_id IN (...) — and stitches the results together in memory. One extra round trip, zero row multiplication.

from p in Post, preload: [:comments]

Ecto’s own docs are explicit about the tradeoff: a plain preload: fetches posts, then issues a separate query for comments and associates them in a second pass; a join-based preload does it in one query but duplicates every joined row. The rule is simply picking the cheaper cost for the shape you have — an extra round trip for has_many, versus wasted bytes for belongs_to. Most of the N+1s I’ve had to unwind in Phoenix codebases weren’t from missing preloads at all — they were from a has_many joined the way you’d correctly join a belongs_to, quietly duplicating rows nobody looked at closely enough to notice.

Pattern 1–2: the split isn’t a style preference, it’s physics

Patterns one and two are the Iron Law above, stated as decisions you make at write time, not fixes you make at debug time:

1. Association is belongs_to (or has_one)? Join and preload off the join binding. Same query, no duplication, no extra round trip.
2. Association is has_many (or many_to_many)? Plain preload:, no join. Accept the second query; refuse the duplicated rows.

If you only ever apply these two, you’ve already fixed the overwhelming majority of the N+1s that show up in a code review.

Pattern 3: filtered has_many preloads need the join back

The Iron Law has an escape hatch, and this is it. Sometimes you need a has_many preload but only the matching rows — published comments, not all of them. Plain preload: can’t filter; it fetches the full association. For that you go back to the join, but you preload off the join binding instead of selecting its columns directly:

from p in Post,
  join: c in assoc(p, :comments),
  where: c.published_at > p.updated_at,
  preload: [comments: c]

Ecto still does the “one query, then a stitching pass” trick here — it fetches posts and comments together but reassembles them into the tree structure, so you get the ergonomics of preload: with the filtering power of a join. This is the pattern that trips people up going the other direction: they see a join and assume it’s always the row-explosion problem. It’s the unfiltered, unpreloaded join that’s the problem — a filtered join feeding preload: is fine, because you’re back to one row set that Ecto explicitly reshapes for you.

Pattern 4: compose the whole tree in one preload, not N calls

If you need posts, their comments, and each comment’s author, don’t preload posts, then loop and preload comments, then loop again for authors. Ecto’s preload: takes a nested keyword structure and resolves the entire tree in one composed call, dispatching one query per level of the tree — not per row:

from p in Post,
  preload: [comments: :author]

That’s still N+1-shaped in the strict sense — N levels means N queries — but N is the depth of your association graph, which is small and fixed, not the row count, which isn’t. That distinction is the whole point: N+1 isn’t inherently the enemy, N+1 per row is.

Pattern 5–6: composable query modules beat query spaghetti

Once a schema has more than two or three call sites, you’ll be tempted to write the same where/join/preload combination slightly differently in each one. Curiosum’s write-up on resource-scoped query modules is the cleanest version of the fix I’ve seen, and it’s worth adopting wholesale rather than reinventing a worse copy: one module per resource, small composable functions, piped together at the call site.

defmodule Blog.PostQuery do
  import Ecto.Query

  def base, do: from(p in Post, as: :post)

  def published(query \\ base()) do
    where(query, [post: p], p.published)
  end

  def with_author(query \\ base()) do
    join(query, :inner, [post: p],
      u in assoc(p, :author), as: :author
    )
  end
end

That’s pattern five: extract the filters, not the whole query, into named functions on a module scoped to the schema. Each function takes and returns a queryable, so they compose with the pipe operator instead of copy-pasting where clauses across contexts.

Pattern six is where the preload decision above actually lives once you’ve adopted this: preload at the tail of the pipeline, in the context boundary function, never inside the composable query functions themselves.

Blog.PostQuery.published()
|> Blog.PostQuery.with_author()
|> preload([post: p, author: u], author: u)
|> Repo.all()

Keeping preload out of the composable functions means published/1 and with_author/1 stay reusable in contexts that don’t want the association loaded at all — a count query, an existence check, an admin export with different fields. Bake the preload into the module function and every caller pays for a join they might not need.

Pattern 7: Dataloader, when you don’t own the query shape

Everything above assumes you control the query at the call site. You don’t, in two common cases: an Absinthe GraphQL resolver, where the client picks which fields (and therefore which associations) get requested per query; and a LiveView tree of independent function components, each rendering off the same list without knowing what its siblings already loaded.

Dataloader.Ecto solves this by batching within a single request, deferring actual execution until every resolver or component has had a chance to register what it needs:

source = Dataloader.Ecto.new(MyApp.Repo)

loader =
  Dataloader.new()
  |> Dataloader.add_source(:db, source)
  |> Dataloader.load_many(
    :db, :comments, posts
  )
  |> Dataloader.run()

Dataloader.get_many(loader, :db, :comments, posts)

The decision rule: reach for Dataloader when the caller doesn’t control the query, not as a default replacement for preload:. If you own the function signature, plain composable Ecto is simpler to read, test, and debug. Dataloader earns its complexity specifically at the boundary where multiple independent callers need to share one batched fetch without knowing about each other.

Pattern 8: subqueries and lateral joins for “latest N per group”

The pattern that plain preload genuinely cannot express: “the three most recent comments per post,” not all of them. This is where a correlated subquery with parent_as and a lateral join is the honest tool, not a workaround:

recent_comments =
  from c in Comment,
    where: c.post_id == parent_as(:post).id,
    order_by: [desc: c.inserted_at],
    limit: 3

from p in Post, as: :post,
  inner_lateral_join: c in subquery(recent_comments),
  preload: [comments: c]

This is genuinely a different problem from the has_many-vs-belongs_to split — it’s not about avoiding row explosion, it’s about pushing a per-row limit down into the database instead of over-fetching everything and truncating it in Elixir. If you find yourself writing Enum.take/2 on a preloaded association after the fact, this pattern is almost always the fix.

Pattern 9: denormalize before you optimize the join

Not every N+1-shaped problem should be solved with a smarter query. If the only thing a view needs from a has_many is a count — comment count, order count, active-session count — you don’t need to load the association at all, filtered or otherwise. A counter cache column, updated by the same transaction that inserts the child row, turns an association load into a column read:

from p in Post, select: p.comments_count

This is the pattern people skip because it feels like giving up on “doing it properly” in Ecto. It’s the opposite — it’s recognizing that the fastest query is the one you don’t run. If the number is read far more often than the underlying rows are actually displayed, a maintained aggregate column beats every join pattern above it on both latency and plan complexity.

Pattern 10: raw SQL is not a defeat

Sometimes the query you need — a window function, a recursive CTE, a LATERAL with logic Ecto’s DSL doesn’t model cleanly — is more honestly expressed as SQL than as a wall of fragment/1 calls dressed up to look like Elixir. I’ve written the specifics of when and how elsewhere on executing raw SQL with Ecto; the short version here is the decision rule, not the syntax: if you’re spending more time fighting the query DSL into producing the SQL you already know you want than you’d spend just writing that SQL, stop fighting it. Ecto.Adapters.SQL.query/4 behind a named function in your context module is not a lesser pattern than the nine above it — it’s the one that admits Ecto’s query DSL, like any DSL, has an edge, and past that edge the honest move is the tool built for the job.

The two you’ll hit but shouldn’t fix here

Two contexts turn ordinary N+1 mistakes into ones that are easy to miss in code review, because the loop isn’t visible in the query itself — it’s in the surrounding architecture. I’ve written each up on its own, since the fix is specific to the context rather than to Ecto:

• LiveView. A handle_event or a function component that reloads an association per row, per event, instead of batching before assign. The patterns for avoiding this live in 12 Phoenix LiveView Patterns for Production.
• Oban. A worker that preloads inside a per-item loop across a batch job, instead of preloading the batch once before iterating. Covered in Oban as a Durable AI Agent Runtime in Elixir.

Both are the same Iron Law from the top of this post, applied somewhere the query isn’t the first thing you read.

Pick the rule, not the recipe

If you take one thing from this: stop treating N+1 fixes as a single recipe you paste in wherever a slow query shows up. has_many gets a separate query, belongs_to gets a join — that’s the rule that should be automatic. Everything past it — filtered joins, nested preloads, composable query modules, Dataloader, lateral subqueries, denormalized counters, raw SQL — is a decision about which cost you’re willing to pay for a specific shape of data, made deliberately, not discovered in a slow-query log three weeks after ship. Ecto rewards that kind of upfront judgment more than almost any other part of a Phoenix codebase; it’s one of the reasons the language holds up well past prototype scale, a case I’ve made in full in Why We’d Pick Elixir for an AI Startup Backend. If you’re assembling the rest of your toolchain around it, 15 Elixir Libraries I Reach For in 2026 covers where Dataloader and its neighbors fit into a production stack.

If you’re staffing or auditing an Elixir/Phoenix team and query discipline like this isn’t yet a shared, written-down convention, that’s exactly the kind of gap a fractional engineering lead closes early rather than after the slow-query alerts start. Happy to talk through what that looks like.