Optimizing N+1 queries in Laravel with Eloquent

Optimizing N+1 Queries in Laravel with Eloquent

Laravel's Eloquent ORM is one of the most elegant and expressive tools available to PHP developers. Its fluent API and active record implementation allow rapid application development without sacrificing readability. However, this elegance comes with a hidden cost that can cripple application performance at scale: the N+1 query problem. This post examines how to identify, diagnose, and eliminate N+1 queries in Laravel applications using PostgreSQL as our database backend.

Understanding the N+1 Query Problem

The N+1 query problem occurs when your application executes one query to retrieve a set of records, then executes an additional query for each record to fetch related data. If you retrieve 100 posts, you might execute 1 query for the posts and 100 additional queries for their authors. This pattern scales linearly and becomes catastrophic as dataset sizes grow.

The root cause lies in Eloquent's lazy loading behavior. When you access a relationship as a property, Eloquent fetches the related data on demand if it has not already been loaded. This convenience becomes a performance trap in loops.

Consider this seemingly innocent controller method:

public function index()
{
    $posts = Post::all();

    return view('posts.index', [
        'posts' => $posts,
    ]);
}

And the corresponding Blade template:

@foreach ($posts as $post)
    <article>
        <h2>{{ $post->title }}</h2>
        <p>By {{ $post->author->name }}</p>
    </article>
@endforeach

Assuming Post belongsTo User (as author), this code executes one query for posts and one query per post for the author. With 100 posts, that is 101 queries. With 10,000 posts, your database connection pool will grind to a halt.

Diagnosing N+1 Queries in Development

Laravel provides excellent debugging tools to catch these issues before they reach production. The most straightforward approach is enabling query logging and examining the output.

In your AppServiceProvider, add Laravel's built-in N+1 detection during local development:

use Illuminate\Database\Eloquent\Model;

public function boot()
{
    Model::preventLazyLoading(! app()->isProduction());
}

This throws a LazyLoadingViolationException when lazy loading occurs outside a closure or callback, making violations impossible to ignore during development. For existing applications with extensive lazy loading, start with logging rather than exceptions:

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

DB::listen(function ($query) {
    Log::info($query->sql, $query->bindings);
});

Laravel Telescope and the debugbar package provide visual query analysis that makes N+1 patterns immediately obvious. With Telescope, navigate to the Queries tab and look for repeated similar queries with only the bound parameter changing.

For PostgreSQL specifically, you can also query pg_stat_statements in production to identify frequently executed similar queries:

SELECT query, calls, mean_time, total_time
FROM pg_stat_statements
WHERE query LIKE '%SELECT * FROM "users" WHERE "id" =%'
ORDER BY calls DESC
LIMIT 10;

Eager Loading with with()

Eloquent's primary defense against N+1 queries is eager loading. The with() method instructs Eloquent to fetch all related records in a single additional query, using a WHERE IN clause.

Refactoring our earlier example:

public function index()
{
    $posts = Post::with('author')->get();

    return view('posts.index', [
        'posts' => $posts,
    ]);
}

This reduces our query count from 101 to exactly 2:

select * from "posts";

select * from "users" where "id" in (1, 2, 3, 4, 5, ...);

PostgreSQL handles large IN clauses efficiently, though extremely large sets may benefit from alternative approaches discussed later.

You can eager load multiple relationships simultaneously:

$posts = Post::with(['author', 'category', 'tags'])->get();

And you can eager load nested relationships using dot notation:

$posts = Post::with('author.profile', 'author.teams')->get();

Constrained Eager Loading

Sometimes you need related data with additional conditions. Eloquent supports closure-based constraints on eager loaded relationships:

$posts = Post::with(['comments' => function ($query) {
    $query->where('approved', true)
          ->orderBy('created_at', 'desc')
          ->limit(5);
}])->get();

Be cautious with limits on eager loaded relationships. The constraint applies to the entire relationship query, not per-parent. The example above fetches only 5 comments total, not 5 comments per post. For per-parent limiting, you need different strategies.

The N+1 Query Problem in Complex Scenarios

Real applications rarely involve simple one-level relationships. Consider a forum application with users, posts, comments, and nested replies. The temptation to access deeply nested relationships in templates creates exponential query growth.

@foreach ($posts as $post)
    <h2>{{ $post->title }}</h2>
    <p>By {{ $post->author->name }}</p>
    
    @foreach ($post->comments as $comment)
        <div class="comment">
            <p>{{ $comment->body }}</p>
            <p>By {{ $comment->author->name }}</p>
            
            @foreach ($comment->replies as $reply)
                <p>{{ $reply->body }} by {{ $reply->author->name }}</p>
            @endforeach
        </div>
    @endforeach
@endforeach

Without eager loading, this template generates queries proportional to posts × comments × replies. The solution requires comprehensive eager loading:

$posts = Post::with([
    'author',
    'comments.author',
    'comments.replies.author'
])->get();

Even with eager loading, be mindful of memory consumption. Loading thousands of nested relationships into memory simultaneously can exhaust PHP's memory limit. Pagination is essential for large datasets.

Lazy Eager Loading

Sometimes you cannot eager load at query time because relationship needs depend on runtime conditions. Eloquent provides load() for lazy eager loading after the initial query:

$posts = Post::all();

if ($request->boolean('include_authors')) {
    $posts->load('author');
}

This executes the same efficient query structure as with(), but deferred until the condition is evaluated. The collection method loadMissing() is particularly useful when you are uncertain whether relationships are already loaded:

$posts->loadMissing('author.profile');

Query Optimization and PostgreSQL-Specific Techniques

Eager loading solves the N+1 problem but introduces its own challenges. Two queries with large result sets may be slower than a properly optimized join, particularly with complex WHERE conditions or ordering.

For read-heavy relationships, consider using join() for single-query execution:

$posts = Post::select('posts.*', 'users.name as author_name')
    ->join('users', 'posts.author_id', '=', 'users.id')
    ->get();

This requires careful column selection to avoid ambiguity and loses Eloquent's relationship conveniences, but can be significantly faster for simple data retrieval.

PostgreSQL's LATERAL JOINs provide elegant solutions for per-row limits that eager loading cannot handle. Eloquent does not natively support LATERAL, but you can use query builder or raw expressions:

$posts = Post::query()
    ->select('posts.*')
    ->with(['latest_comments' => function ($query) {
        $query->select('comments.*')
            ->from('comments')
            ->whereColumn('comments.post_id', 'posts.id')
            ->orderBy('created_at', 'desc')
            ->limit(3);
    }])
    ->get();

For true LATERAL optimization with PostgreSQL, use DB::raw or a package like treschelet/laravel-postgres-extended:

$posts = Post::query()
    ->select('posts.*')
    ->selectSub(function ($query) {
        $query->selectRaw('json_agg(c.*)')
            ->from('comments as c')
            ->whereColumn('c.post_id', 'posts.id')
            ->orderBy('c.created_at', 'desc')
            ->limit(5);
    }, 'recent_comments')
    ->get();

API Resources and the N+1 Problem

API development with Laravel's API Resources layer presents unique challenges. Each resource transformation can trigger hidden queries if relationships are accessed conditionally.

class PostResource extends JsonResource
{
    public function toArray($request)
    {
        return [
            'id' => $this->id,
            'title' => $this->title,
            'author' => new UserResource($this->whenLoaded('author')),
            'comment_count' => $this->comments_count,
        ];
    }
}

Always use whenLoaded() to avoid implicit lazy loading. Additionally, leverage withCount() for aggregate data without loading entire relationships:

$posts = Post::with('author')
    ->withCount('comments')
    ->get();

This generates efficient COUNT queries without loading comment collections into memory.

Caching Strategies

For truly static or slowly changing relationships, consider caching at the application level. Laravel's Cache facade with Redis or Memcached eliminates database queries entirely:

$posts = Cache::remember('posts.with.authors', 3600, function () {
    return Post::with('author')->get();
});

Alternatively, use Laravel's model caching packages like genealabs/laravel-model-caching for transparent relationship caching with cache invalidation on model updates.

Production Monitoring and Continuous Prevention

Preventing N+1 queries requires ongoing vigilance. Implement automated detection in your testing pipeline:

public function test_posts_index_does_not_cause_n_plus_one()
{
    Post::factory()->count(10)->create();

    DB::enableQueryLog();

    $this->getJson('/api/posts');

    $queries = DB::getQueryLog();

    $selectCount = collect($queries)->filter(
        fn ($q) => str_starts_with($q['query'], 'select')
    )->count();

    $this->assertLessThanOrEqual(3, $selectCount);
}

Services like Laravel Telescope, New Relic, or Blackfire.io provide production query profiling. Set alerts for query count anomalies relative to endpoint traffic.

Conclusion

The N+1 query problem is one of the most common performance issues in Laravel applications, yet it is entirely preventable with disciplined use of Eloquent's eager loading capabilities. The combination of development-time detection through Model::preventLazyLoading(), strategic application of with() and load(), and PostgreSQL-specific optimizations where needed, ensures your application maintains responsiveness at scale.

Remember that optimization is contextual. Eager loading trades memory for query efficiency, and aggressive eager loading of unused relationships wastes resources. Profile your actual queries, measure under realistic load, and optimize based on evidence rather than assumption. The tools are available; the discipline to use them consistently distinguishes production-grade applications from prototypes.