Find and Fix N+1 Queries Using AppSignal for a Phoenix App in Elixir

N+1 queries are a frequent issue in complex applications built with Elixir and Phoenix. These queries can silently degrade application performance, often going unnoticed until they've compounded into a significant problem.

They can substantially increase web page load times, as each additional query adds overhead to a database, consuming more time and resources. That's why it's crucial to detect and resolve N+1 queries to optimize production systems. It's not just about maintaining a seamless user experience; it's also about ensuring you maintain your Elixir application's scalability and efficiency.

Using AppSignal, you can identify N+1 queries in Phoenix applications, alongside a comprehensive suite of tools to monitor, diagnose, and rectify performance bottlenecks in Elixir projects.

Let's explore how N+1 queries happen, their impact on performance, and some strategies to detect and fix them in your Elixir application.

Understanding And Fixing N+1 Queries in Phoenix for Elixir

N+1 queries occur when an application retrieves a single database record, followed by an additional query for each related record.
In Phoenix applications, N+1 queries often emerge in the context of complex relationships and Ecto associations.

Even small mistakes in Ecto queries can lead to cascading N+1 issues, resulting in multiple round trips to a database, each fetching only a small amount of data.

Some common examples include:

1. Using Repo.all without a proper preload clause (this will not fetch associated records in the same query).
2. In GraphQL, when using Absinthe with Elixir, N+1 query issues can arise when resolving fields that require data from associated records.

Check out Avoiding N+1 Queries for Absinthe for an overview of this topic.

A Simple Example Using Ecto

For instance, consider a blog application where each post has many comments. A naive Ecto query might retrieve all posts and then run a separate query for each post's comments. This results in one query for the posts (N) and one query for each post's comments (+1), which is highly inefficient.

# Fetching posts without preloading comments
posts = Repo.all(Post)

# This will cause N+1 queries, as for each post, comments are fetched separately
posts |> Enum.each(fn post ->
  comments = Repo.all(from c in Comment, where: c.post_id == ^post.id)
  IO.inspect(comments)
end)

The above might sound like a contrived example — experienced developers are quite unlikely to do this. But it is meant to serve as a simplified scenario of how N+1 queries can creep into systems: any query inside a loop is a suspect. N+1 queries are usually hidden in plain sight, for example, behind a context function like Blog.list_comments(post_id: post.id) inside the loop.

Next, let's discuss a complex scenario.

Complex Situations Leading to N+1 Queries

Imagine a blog application displaying a feed of all recent posts and the count of comments (or likes) for each post.

A sample implementation could look like this:

# Load posts from subscribed authors
posts = Repo.all(Post)

# Load comments count per post
for post <- posts do
  comments_count = Repo.one(from c in Comment, where: c.post_id == ^post.id, select: count(c.id))
  render("post.html", post: post, comments_count: comments_count)
end

Here, the comments_count query causes an N+1 problem because we fetch each individual post's count. One might argue that we can just add comments to the preloads and be done with it. While that solves the N+1 query, it loads too much unnecessary data (we don't need all the comments yet, only the total number of comments) and might actually hurt loading times.

A better solution is to join the tables and use select to load the comments count within the posts query:

posts_with_counts =
  from(p in Post,
    left_join: c in Comment, on: c.post_id == p.id,
    group_by: [p.id],
    select: %{p | comments_count: count(c.id)}
  )
  |> Repo.all()

for post <- posts_with_counts do
  render("post.html", post: post, comments_count: post.comments_count)
end

To avoid and detect N+1 queries, developers must be vigilant when writing Ecto queries and templates.
Using preload correctly to fetch all necessary records in a single database query is essential.

Detecting N+1 Queries By Their Impact

Understanding the data access patterns of your application can help you anticipate and prevent N+1 issues before they arise.
One way to identify N+1 queries is to observe and analyze common bottlenecks in your app, including:

1. Increased Load Times: Each query takes time to execute, and when hundreds or thousands of them run sequentially, the cumulative effect leads to noticeable delays in content rendering.
2. Database Strain: Databases are designed to handle multiple queries efficiently, but an influx of unnecessary queries can strain the system, leading to slower response times for all users.
3. Server Resource Drain: A server's resources are finite, and handling a multitude of queries consumes more CPU and memory, potentially affecting other operations and leading to resource exhaustion.
4. Scalability Issues: As a user base grows, inefficient N+1 queries can prevent an application from scaling smoothly, requiring more hardware resources to handle the same workload.
5. Cost Implications: Increased server load and database usage can lead to higher operational costs, especially in cloud-based environments where resources are metered.

However, it is difficult to spot small differences in performance and then pinpoint the parts of your application that have caused such degradation.

The next sections will show how AppSignal can help you detect and fix N+1 queries, ensuring your application runs smoothly and efficiently.

Detecting N+1 Queries in Phoenix for Elixir Using AppSignal

Before detecting N+1 queries, we need to set up AppSignal in your Phoenix application. You can sign up for a free 30-day trial of AppSignal.

AppSignal provides an Elixir package that integrates seamlessly with Phoenix applications. The AppSignal for Elixir setup process involves adding the Elixir package to your project's dependencies and configuring it with your AppSignal Push API key. Once installed, AppSignal starts monitoring your application's performance, including database query patterns.

Analyzing Elixir Data with AppSignal

AppSignal's instrumentation for Phoenix and Ecto is designed to provide detailed insights into your application's database interactions. It automatically tracks database query times, helping you spot inefficiencies. With AppSignal's instrumentation in place, detecting N+1 queries becomes a matter of analyzing the collected data.

Use Slow Queries to Find N+1 Queries

AppSignal's Slow Queries dashboard breaks down web requests, showing the slowest queries and potential bottlenecks. A series of similar queries within a single web request is a strong indicator of an N+1 query problem.

For example, if you see repeated queries fetching comments for different posts while a blog page is being rendered, you've likely encountered an N+1 issue. AppSignal provides context, including specific database calls, making it easier to pinpoint the exact location in your code where the issue originated.

We can see that the first query had almost 95% impact on all our queries executed through Ecto.
In a real-world app, this number might be lower, as there can be hundreds of unique queries. However, any outlier that's highlighted here is a great candidate for analysis.

💡 Note: The "impact" of a query on an application is based on its usage compared to other queries.

Another metric to consider here is the high throughput of 720.
Throughput is the total number of queries that were executed in a specified time window.

Let's check out the full details of the query:

SELECT c0."id", c0."body", c0."post_id", c0."user_id", c0."inserted_at", c0."updated_at", c0."post_id" FROM "comments" AS c0 WHERE (c0."post_id" = $1) ORDER BY c0."post_id"

Since the query is targeting a single post, it is quite likely an N+1 query.

Trace N+1 Queries with the Event Timeline

AppSignal's Event Timeline is also particularly useful for finding and tracing N+1 queries.
It visualizes a sequence of database calls during a web request, allowing you to identify the hallmark successive database queries that characterize N+1 issues.

Here's an example event timeline for a page that has an N+1 query issue:

We can see that many quick queries are being performed.
Hovering over query.ecto also shows the exact query that was performed to help us pinpoint which query is the culprit.

Another good indication of a possible issue is when the ecto time of an event is abnormally high.
For example, in this case, we can see that Ecto was the major time-hog:

Addressing Detected N+1 Queries

Once you've identified an N+1 query with AppSignal, the next step is to address it.
This typically involves optimizing your Ecto queries to preload associated records.

AppSignal's detailed metrics allow you to compare performance before and after the changes, giving you concrete feedback on the impact of your optimizations.

And that's it!

Wrapping Up

Whether you're a seasoned developer or new to Phoenix, understanding the nuances of N+1 queries and leveraging AppSignal's capabilities will empower you to build faster, more reliable applications.

Remember, the key to maintaining a performant application is not just fixing issues as they arise, but continuously monitoring and improving your codebase with the help of powerful tools like AppSignal.

Happy coding!

P.S. If you'd like to read Elixir Alchemy posts as soon as they get off the press, subscribe to our Elixir Alchemy newsletter and never miss a single post!