Optimizing Eloquent Queries: Eliminating N+1 and Memory Leaks in Laravel

Eloquent ORM simplifies database interactions, but its abstractions frequently hide inefficient SQL queries. At production scale with millions of records, misconfigured Eloquent queries cause severe N+1 overhead and memory exhaustion (OOM).

1. Eliminating N+1 Queries with Eager Loading

The N+1 problem occurs when an application loads relationships inside a loop without prior eager loading.

Bad (N+1 Queries):

$posts = Post::all(); // 1 query

foreach ($posts as $post) {
    echo $post->author->name; // N extra queries
}

Good (Eager Loading):

$posts = Post::with('author')->get(); // Only 2 queries

foreach ($posts as $post) {
    echo $post->author->name;
}

Automatic Detection:

Prevent lazy loading in development via app/Providers/AppServiceProvider.php:

use Illuminate\Database\Eloquent\Model;

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

2. Preventing Memory Exhaustion with Chunking

Fetching massive datasets using all() or get() loads all hydrated model instances into memory at once.

Avoid get() for large tables:

// Fatal error: Allowed memory size exhausted
$orders = Order::where('status', 'completed')->get();

Use lazyById() or chunkById():

// Keeps memory allocation constant
Order::where('status', 'completed')
    ->chunkById(500, function ($orders) {
        foreach ($orders as $order) {
            $order->processInvoice();
        }
    });

chunkById is safer than chunk because it uses WHERE id > last_seen_id LIMIT 500, preventing offset drift when modifying rows during iteration.

3. Selective Column Projection

By default, Eloquent performs SELECT *. For tables with large text or JSON columns, this causes unnecessary memory and network I/O overhead.

$users = User::query()
    ->select(['id', 'name', 'email'])
    ->with(['profile:id,user_id,avatar'])
    ->paginate(20);

Note: When selecting columns on relations, always include foreign keys (user_id) and primary keys (id) so Eloquent can resolve relations correctly.

Summary

1. Enable Model::preventLazyLoading() in non-production environments.
2. Use with() to eager load relations ahead of time.
3. Use chunkById() or lazyById() for heavy batch operations.
4. Select explicit columns to minimize hydrated model memory footprint.