What this usually means
Ecto's preload mechanism is lazy by default: if you load a list of posts and then access post.author.name in a template, Ecto fires one query per post to fetch the author. This is the classic N+1. The root cause is almost always that the developer assumed preload happens automatically or they used preload in the wrong place (e.g., after pagination instead of before). But there's a subtler variant: using Repo.preload on individual items in a loop instead of bulk preloading. Another common pitfall is using Ecto's assoc/2 inside a map comprehension without a preload, which triggers a separate query for each iteration.
The first ten minutes — establish facts before touching code.
- 1Run your endpoint with Ecto Logging enabled: `config :my_app, :ecto_repos, [MyApp.Repo], loggers: [Ecto.LogEntry]`
- 2Count the number of identical SELECT queries after the initial list query — if count = list length + 1, it's N+1
- 3Use `Repo.aggregate(:count, :id)` on the query to see estimated rows vs actual rows fetched
- 4Check for `preload` calls inside `Enum.map` or `for` comprehensions — that's a red flag
- 5Profile with `telemetry` and look for `[:my_app, :repo, :query]` events with high `query_time` and `num_queries`
The specific files, logs, configs, and dashboards that usually own this bug.
- searchconfig.exs: check `loggers` setting for Ecto.LogEntry
- searchPhoenix template files: look for `@post.author.name` or similar association traversals
- searchController actions: inspect the query pipeline for missing `|> Repo.preload(...)`
- searchContext modules: check functions that return lists for preload usage
- searchView helpers: any function that calls `Repo.preload` on a single struct inside a loop
- searchEcto schema definitions: verify associations are defined with proper `:foreign_key` and `:references`
- searchtest/support/factory.ex: factories often skip preloads, masking N+1 in tests
Practical causes, not theory. These are the things you will actually find.
- warningForgetting to preload an association that is accessed in a template
- warningCalling `Repo.preload` inside a loop (e.g., `Enum.map(posts, &Repo.preload(&1, [:comments]))`)
- warningUsing `Ecto.build_assoc` or `assoc/2` without a preload in a comprehension
- warningPreloading after pagination: `posts |> paginate |> Repo.preload(:comments)` — the pagination already fetched the IDs
- warningNested preloads missing: preloading `:comments` but not `comments: :user`
- warningUsing `Repo.all` without `preload` then accessing associations in serializers
- warningSchema with `has_many` but no `preload` in the query, then iterating over children in view
Concrete fix directions. Pick the one that matches your root cause.
- buildAdd `Repo.preload(:association)` to the initial query: `from p in Post, preload: [:comments]`
- buildUse `Repo.preload(entries, [:assoc1, assoc2: :nested])` on the list after fetching, but outside loops
- buildSwitch to `join` + `preload` for complex cases where you need both filter and load: `from p in Post, join: c in assoc(p, :comments), preload: [comments: c]`
- buildFor polymorphic associations, use `Repo.preload(entries, [comments: {query, :user}])` with a custom query
- buildCache preloaded data in a map keyed by parent ID to avoid duplicate loads
- buildUse `Ecto.Query.with_named_binding` to avoid preload conflicts in complex joins
A fix you cannot prove is a guess. Close the loop.
- verifiedRun the same endpoint with DBLogger enabled and confirm the number of queries equals 1 + number of preloaded associations
- verifiedCompare response time before and after fix: should drop from >1s to <100ms for 100 records
- verifiedCheck `Repo.aggregate(:count, :id)` on the final query shows only the expected rows
- verifiedUse `telemetry` metrics: `[:my_app, :repo, :query]` count should be constant regardless of list size
- verifiedWrite a test that asserts `assert [post1, post2] |> Repo.preload(:comments) |> length == 2` and check DB queries via `Ecto.Adapters.SQL.Sandbox`
Things that make this bug worse or harder to find.
- warningCalling `Repo.preload` inside a transaction inside a loop — this can cause deadlocks
- warningPreloading everything blindly: only preload associations that are actually used
- warningUsing `preload` with `:where` clause that filters out children, causing unexpected nil associations
- warningAssuming `preload` works with `select_merge` — they can conflict and cause missing data
- warningForgetting to `|> Repo.preload(...)` after `Repo.insert_all` — new records need explicit preload
The 10x Slowdown After Adding 'Author Name' to Post Listing
Timeline
- 09:15Deploy new feature: show author name on post listing page
- 09:30PagerDuty alert: p99 response time for /api/posts jumps from 80ms to 2.3s
- 09:35Check NewRelic: database calls per request spiked from 2 to 52
- 09:40Enable Ecto Logging in staging: see 50 identical SELECT * FROM users WHERE id = ?
- 09:45Identify culprit: post index template accesses @post.author.name
- 09:50Find that PostController index action uses Repo.all(Post) without preload(:author)
- 10:00Add preload to query: Post |> Repo.preload(:author) |> Repo.all
- 10:05Deploy fix, p99 drops back to 85ms, alert clears
We had a simple Post listing endpoint that returned 50 posts. It was fast — 80ms p99. Then a product manager asked us to show the author's full name instead of just the ID. I made a quick change in the template: from <%= @post.author_id %> to <%= @post.author.name %>. Deployed. Within 15 minutes, PagerDuty lit up. The endpoint was timing out for some users.
I jumped into NewRelic and saw the database call count per request had gone from 2 to 52 — one initial query for posts, then one for each post to fetch the author. That's the classic N+1. I checked the controller: def index do posts = Repo.all(Post); render(..., posts: posts). No preload. The template accessed @post.author.name, which triggers Ecto's lazy loading.
The fix was one line: added |> Repo.preload(:author) to the query. I also added a test that asserts the number of database queries stays constant using Ecto.Adapters.SQL.Sandbox. Deployed again, and p99 dropped back to 85ms. The lesson: never trust lazy loading in production; always preload associations you'll access in views or serializers.
Root cause
Missing Ecto preload for the author association in the Post index query, causing lazy loading per post.
The fix
Added `|> Repo.preload(:author)` to the query pipeline in PostController index action.
The lesson
Always preload associations that will be accessed in views or serializers. Use DBLogger or telemetry to catch N+1 before they hit production.
When you call `Repo.preload(posts, [:comments])`, Ecto inspects the schema to find the `:comments` association. It then collects all parent IDs from the `posts` list and executes a single query: `SELECT * FROM comments WHERE post_id IN (1,2,3,...)`. The results are partitioned by `post_id` and assigned to the corresponding `%Post{}` struct's `:comments` field. This is efficient because it reduces N+1 queries to just 2.
But if you instead access `post.comments` without preloading, Ecto fires a separate query for each post. This lazy loading is intentional for cases where you might not need the association, but it's a trap in lists. The key insight: preload works on lists of structs, not on individual structs in a loop. Calling `Repo.preload(post, :comments)` inside `Enum.map` is the same as lazy loading — it issues one query per iteration.
Ecto's built-in logger can be configured to print every query. In `config.exs`, set `config :my_app, MyApp.Repo, loggers: [Ecto.LogEntry]`. Then run your endpoint and look for repeated identical SELECTs. The pattern is: one initial query (e.g., `SELECT * FROM posts LIMIT 50`), followed by N queries of the form `SELECT * FROM comments WHERE post_id = $1`. If N equals the number of posts, that's an N+1.
For production, use Telemetry. Ecto emits `[:my_app, :repo, :query]` events. Attach a handler that counts queries per request using `:telemetry.attach/4`. You can also use libraries like `telemetry_metrics_statsd` to send to Datadog. Set an alert when the number of queries per request exceeds a threshold (e.g., >10). This catches N+1 even if you don't notice it during development.
Sometimes you need to preload with conditions. For example, preload only recent comments: `Repo.preload(posts, [comments: from(c in Comment, where: c.inserted_at > ^one_day_ago)])`. This executes a single query with the where clause. You can also preload nested associations: `Repo.preload(posts, [comments: :user])` loads comments and their authors in two queries total (one for comments, one for users).
Be careful with `:through` associations. Preloading `:authors` through a `:comments` association can cause multiple queries if not structured correctly. Use `Repo.preload(posts, [comments: :user])` and then access `post.comments |> Enum.map(& &1.user)` — that's still only 2 queries. Avoid accessing `post.authors` directly if it's defined as `has_many :authors, through: [:comments, :user]` because that triggers lazy loading per post.
The best way to prevent N+1 is to write tests that assert on query count. Use `Ecto.Adapters.SQL.Sandbox` with `assert_num_queries` from `ExUnit`. Example: `test "index returns posts with authors" do assert_num_queries 2, fn -> conn = get(conn, "/posts"); assert html_response(conn, 200) end end`. This ensures the endpoint only makes 2 queries: one for posts, one for authors.
Alternatively, you can use a library like `assert_queries` or `DBConnection` hooks. The key is to set a baseline and fail if the count exceeds it. I've seen teams add this to CI pipelines and catch regressions immediately. Without tests, N+1 often sneaks back in when someone refactors a view or adds a new association.
Frequently asked questions
What's the difference between `preload` and `join` in Ecto?
`preload` fetches associated data in separate queries after the main query, which is efficient for reads when you don't need to filter on the association. `join` performs a SQL JOIN, which can be used to filter or aggregate on the associated table. For example, if you want only posts that have comments, use `join`; if you want to display comments alongside posts, use `preload`. You can also combine them: `from p in Post, join: c in assoc(p, :comments), preload: [comments: c]` to join-filter and preload in one query.
Can I preload associations after calling `Repo.all`?
Yes, you can call `Repo.preload(posts, [:comments])` after fetching posts. This is useful when you need to conditionally preload based on some logic. However, preloading after pagination is still efficient as long as you preload the entire list at once, not one by one. Avoid calling `Repo.preload` inside loops—always preload the full list.
Why does my test pass but N+1 happens in production?
Tests often use factories that preload associations automatically, or they access fewer records. Also, test databases are small, so performance differences aren't noticeable. Ensure your tests explicitly set up data with multiple parent records and use `assert_num_queries` to catch N+1. Additionally, check that your factory does not preload associations by default.
How do I debug N+1 in a Phoenix LiveView?
LiveViews often fetch data in `mount/3` or `handle_params/3`. Use `DBLogger` in development and watch for repeated queries as you navigate or interact. The same rules apply: preload associations when you fetch the initial list. If you're loading more items via pagination, preload again after the new fetch. Use `telemetry` to monitor query counts in production.
Is there a tool to automatically detect N+1 queries in Ecto?
Yes, several libraries exist: `decorator` with `Ecto.Query` instrumentation, `ex_rated` for rate limiting, and custom telemetry handlers. The most popular is `Ecto.Adapters.SQL.Sandbox` with `assert_num_queries` for testing. For runtime, you can use `telemetry` to count queries per request and trigger alerts. A simple approach is to grep logs for repeated SELECT patterns.