Eloquent at Scale: 7 Quirks Nobody Warns You About

Production gotchas that only show up when your dataset stops being polite.

Photo by Adam Birkett on Unsplash

A reader left a comment on my last Laravel article that stuck with me. Paraphrasing: “Eager loading is great — until you hit the upper limit on parameterized query items. There’s a 65K cap on parameters. Does Eloquent account for that on a query like `SELECT FROM posts WHERE user_id IN (?, ?, ?, …)?”*

The honest answer is: no, it doesn’t.

And that question opens a much bigger conversation. Eloquent is one of the friendliest ORMs in the PHP ecosystem. It hides a lot of complexity behind clean, expressive syntax. For 95% of the apps written in Laravel, that abstraction is a gift — you ship faster, your code reads like English, and you almost never think about what’s happening at the SQL layer.

The other 5% is what this article is about.

When your dataset crosses a certain size — somewhere between “comfortable demo” and “actual production traffic” — Eloquent’s abstractions start showing seams. Things that worked perfectly in development quietly break, or worse, quietly degrade. You don’t get a stack trace; you get a 30-second response time, a memory exhaustion error at 3 AM, or a batch job that just… stops.

I’ve collected seven of these over the years. None of them are bugs — they’re consequences of the abstractions Eloquent chose to make. But they’re the kind of consequences that bite you exactly once, very expensively, and then you remember them forever.

Let’s walk through them.

1. The IN Clause Has a Ceiling

Start with the one that prompted this article.

When you write something like this:

$users = User::with('posts')->get();

Eloquent runs two queries. The first fetches users. The second is the eager-load:

SELECT * FROM posts WHERE user_id IN (?, ?, ?, ?, ?, ?, ?, ...)

The number of placeholders equals the number of users loaded. For 100 users, that’s 100 placeholders. For 10,000 users, that’s 10,000 placeholders. For 70,000 users?

It depends on your database.

Database Parameter Limit Notes PostgreSQL 65,535 Hard limit from the wire protocol (16-bit integer) SQL Server ~2,100 The lowest of the major engines — first to break MySQL No fixed count Bounded by max_allowed_packet (default 64MB) SQLite 999 by default Can be raised at compile time, rarely is

Here’s what hitting the limit actually looks like in production. Picture this code in a nightly job:

// Generate a monthly digest email for every active user
$users = User::query()
    ->where('status', 'active')
    ->where('last_login_at', '>=', now()->subDays(30))
    ->with(['posts', 'subscriptions', 'notifications'])
    ->get();
foreach ($users as $user) {
    dispatch(new SendDigestEmail($user));
}

In development with 500 users, this is fine. In production with 80,000 active users on PostgreSQL, it blows up — not on the user query, but on one of the eager-load queries:

SQLSTATE[08P01]: <<Unknown error>>: 7 ERROR:
extended query protocol cannot have more than 65535 parameters

Eloquent doesn’t chunk the IN clause for you. It builds the query, hands it to PDO, and PDO hands it to the database. The fix is on you. The cleanest pattern is to chunk the parent query, so each batch's eager load stays under any database's ceiling:

User::query()
    ->where('status', 'active')
    ->where('last_login_at', '>=', now()->subDays(30))
    ->with(['posts', 'subscriptions', 'notifications'])
    ->chunkById(1000, function ($users) {
        foreach ($users as $user) {
            dispatch(new SendDigestEmail($user));
        }
    });

Note the with() call is before chunkById(), not inside the closure. Laravel applies the eager loads to each chunk automatically, and each chunk's IN clause has at most 1,000 placeholders — well under any database's ceiling.

chunkById() is preferable to chunk() for large tables because it paginates by primary key rather than OFFSET, avoiding the quadratic slowdown that OFFSET introduces on big tables. (More on that later — it's gotcha #4.)

If you don’t need all the records in memory at once, lazyById() is even better:

User::query()
    ->where('status', 'active')
    ->lazyById(1000)
    ->each(function ($user) {
        // process one user at a time, but query in batches of 1000
    });

But there’s a trap here. Plain lazy() doesn't eager-load relationships in a memory-friendly way. If you call $user->posts inside the closure, you've reintroduced the N+1 problem one user at a time:

// BAD: this is N+1 in slow motion
User::lazy()->each(function ($user) {
    foreach ($user->posts as $post) {  // one query per user!
        // ...
    }
});

The fix is lazyById() with explicit load() per batch — verbose, but correct:

// GOOD: chunks with eager loading, one batch at a time
User::query()
    ->lazyById(1000)
    ->chunk(1000)
    ->each(function ($chunk) {
        $chunk->load('posts');
        foreach ($chunk as $user) {
            foreach ($user->posts as $post) {
                // no extra queries — posts already loaded
            }
        }
    });

This is the pattern, by the way: at scale, you trade Eloquent’s syntactic sugar for explicit control. The abstractions don’t go away — you just have to drive them yourself.

2. Eager Loading Has an N+1 Sibling Nobody Talks About

Everyone knows about N+1. It’s the canonical Laravel performance article topic. with() solves it. Done.

Except with() introduces its own failure mode that's much harder to see.

Consider this:

$posts = Post::with('comments.user.profile')->get();

Looks innocent. What actually runs:

SELECT * FROM posts;
SELECT * FROM comments WHERE post_id IN (...);
SELECT * FROM users WHERE id IN (...);
SELECT * FROM profiles WHERE user_id IN (...);

Four queries instead of 1 + N + N×M + N×M×K. Massive win on query count.

But each subsequent query’s IN clause grows with the accumulated set of IDs from above. If your 1,000 posts have 50 comments each, query #2 returns 50,000 comments. Query #3 has up to 50,000 user IDs in its IN clause. Query #4 has up to 50,000 profile lookups. You haven't hit the parameter limit, but you've just allocated four large collections in PHP memory, hydrated 100,000+ Eloquent models, and held all of them in scope at once.

The query count is fine. The memory profile is a disaster.

Here’s the failing version, almost certainly written with good intentions:

// Generate a CSV export of all post activity
public function exportActivity(): string
{
    $posts = Post::with('comments.user.profile')->get();
    $csv = "post_id,comment,author,department\n";
    foreach ($posts as $post) {
        foreach ($post->comments as $comment) {
            $csv .= sprintf(
                "%d,%s,%s,%s\n",
                $post->id,
                str_replace(',', ';', $comment->body),
                $comment->user->name,
                $comment->user->profile->department ?? '',
            );
        }
    }
    return $csv;
}

I once watched a report exactly like this timeout consistently in production. It ran 4 queries. Four. Beautifully eager-loaded. The problem wasn’t the database — it was that PHP was hydrating 380,000 model objects and running out of memory before it could format them into a CSV.

The fix isn’t to undo with(). The fix is to recognize when you don't need full models:

public function exportActivity(): string
{
    $rows = DB::table('posts')
        ->join('comments', 'comments.post_id', '=', 'posts.id')
        ->join('users', 'users.id', '=', 'comments.user_id')
        ->leftJoin('profiles', 'profiles.user_id', '=', 'users.id')
        ->select(
            'posts.id as post_id',
            'comments.body as comment_body',
            'users.name as author_name',
            'profiles.department as department',
        )
        ->orderBy('posts.id')
        ->cursor();   // streams rows; doesn't load all at once
    $csv = "post_id,comment,author,department\n";
    foreach ($rows as $row) {
        $csv .= sprintf(
            "%d,%s,%s,%s\n",
            $row->post_id,
            str_replace(',', ';', $row->comment_body),
            $row->author_name,
            $row->department ?? '',
        );
    }
    return $csv;
}

Three things changed and each one matters.

First, the query is now a single JOIN instead of four separate queries with PHP-side assembly. The database does the joining, which it’s spectacularly good at.

Second, DB::table() returns plain stdClass objects, not Eloquent models. Each row carries the columns and nothing else — no attribute casting, no mutator caches, no relationship loading machinery, no dirty tracking, no event hooks. Memory per row drops by an order of magnitude.

Third, cursor() uses a database cursor to stream rows one at a time instead of materializing the full result set. The CSV is built incrementally; at no point does the entire dataset sit in memory.

The rule of thumb: the moment you’re more than ~10,000 records into a ->get(), ask yourself whether you actually need models, or just data. Eloquent models are expensive. Each one carries attribute casting, mutator caches, relationship loading machinery, dirty tracking, and event hooks. For a CSV export, you don't need any of that. The query builder gives you raw rows for a fraction of the memory.

3. whereIn() With Duplicates Will Embarrass You

This one is subtle, and I missed it for embarrassingly long.

You write a perfectly reasonable query:

// "Give me the users who created these events"
$events = Event::where('created_at', '>=', now()->subMonth())->get();
$userIds = $events->pluck('user_id')->toArray();
$users = User::whereIn('id', $userIds)->get();

What you forgot: $events->pluck('user_id') returns duplicates. If 500 events were created by the same 50 users, $userIds contains 500 entries, only 50 of which are unique. The query that gets generated:

SELECT * FROM users WHERE id IN (
    42, 17, 42, 89, 17, 42, 89, 17, 42, ...
    -- 500 placeholders, 50 unique values
)

The query works. The result is correct (IN deduplicates internally for the result set). But you've sent 500 parameters when you needed 50, you've made the query plan harder to optimize, and you've moved 10× closer to the parameter limit from gotcha #1. On SQL Server, this is how you accidentally exceed 2,100 parameters with what looks like a perfectly reasonable query.

The fix is trivial once you know to look for it:

$userIds = $events->pluck('user_id')->unique()->values()->toArray();
$users = User::whereIn('id', $userIds)->get();

The unique() deduplicates. The values() resets the array keys — otherwise you get an array like [0 => 42, 2 => 17, 5 => 89, ...] with non-sequential keys, which JSON-encodes as an object instead of an array and can confuse some database drivers' parameter binding.

For the common case where you want to load related users from a collection of events, the more idiomatic Laravel solution is to do it in one query:

// One query, no manual deduplication needed
$users = User::whereIn('id',
    Event::where('created_at', '>=', now()->subMonth())
         ->select('user_id')
         ->distinct()
)->get();

This pushes the DISTINCT down to the database, which deduplicates the IDs before they ever cross the wire. You send one query instead of two, you don't materialize the events collection in PHP at all if you don't need it, and there's no chance of a duplicate-IDs bug.

I’ve seen the un-deduplicated pattern crash a SQL Server connection in production. Three lines of code, one missing unique(), six-hour debugging session.

4. chunk() Lies to You About Mutation

Suppose you want to mark 100,000 records as processed:

Order::where('status', 'pending')->chunk(1000, function ($orders) {
    foreach ($orders as $order) {
        $order->update(['status' => 'processed']);
    }
});

This is almost right, and the “almost” will cost you 20% of your records.

Here’s why: chunk() paginates using OFFSET. Internally, it runs:

SELECT * FROM orders WHERE status = 'pending' LIMIT 1000 OFFSET 0;
SELECT * FROM orders WHERE status = 'pending' LIMIT 1000 OFFSET 1000;
SELECT * FROM orders WHERE status = 'pending' LIMIT 1000 OFFSET 2000;
-- ...

Your loop is changing the status of each row. After the first chunk runs, the original 1,000 pending orders are now processed. They no longer match WHERE status = 'pending'. So when the second chunk runs OFFSET 1000, it counts forward 1,000 rows from the new pending set, which has shrunk by 1,000. The query effectively skips 1,000 records per iteration.

You can verify this in a quick test:

// Setup: insert 5 rows
DB::table('orders')->insert([
    ['id' => 1, 'status' => 'pending'],
    ['id' => 2, 'status' => 'pending'],
    ['id' => 3, 'status' => 'pending'],
    ['id' => 4, 'status' => 'pending'],
    ['id' => 5, 'status' => 'pending'],
]);
$processedIds = [];
Order::where('status', 'pending')->chunk(2, function ($orders) use (&$processedIds) {
    foreach ($orders as $order) {
        $processedIds[] = $order->id;
        $order->update(['status' => 'processed']);
    }
});
// $processedIds will be [1, 2, 5] - orders 3 and 4 silently skipped!

Laravel actually warns about this in the docs, but the warning is easy to miss because the code runs fine. No errors, no warnings — just records that quietly never get processed.

The fix is chunkById(), which paginates by primary key instead of OFFSET:

Order::where('status', 'pending')->chunkById(1000, function ($orders) {
    foreach ($orders as $order) {
        $order->update(['status' => 'processed']);
    }
});

Now each chunk is effectively “give me the next 1000 records with id > the last id I saw,” which is stable under mutation of any column except the primary key. The same test above with chunkById() returns [1, 2, 3, 4, 5] — all records processed.

There’s a bonus: chunkById() is significantly faster on large tables. OFFSET 100000 requires the database to count past 100,000 rows every time it runs. WHERE id > 100000 ORDER BY id LIMIT 1000 uses the primary key index directly. On a million-row table, this is the difference between "minutes" and "hours."

For the truly mutation-heavy case, consider doing the update without chunking at all:

// Single query, atomic, no chunking needed
Order::where('status', 'pending')->update(['status' => 'processed']);

This is one SQL statement, runs in milliseconds even for millions of rows, and has no chunking complications because there is no chunking. The only reason to use the chunk-and-update pattern is if you need to do per-row work (dispatching events, calling external APIs, custom logic per record). For “just change this column for everyone,” the single UPDATE wins every time.

Default to chunkById() for any chunk operation that mutates rows. Reserve chunk() for read-only iteration over a stable dataset. And remember that sometimes the right answer is no chunking at all — just a single UPDATE.

5. count() On A Relationship Is Almost Always Wrong

This is one of the cheapest mistakes to make and the most expensive to leave in production.

$users = User::all();
foreach ($users as $user) {
    if ($user->posts->count() > 10) {
        $this->notifyProlificAuthor($user);
    }
}

What you wrote: “for each user, if they have more than 10 posts.”

What Eloquent runs: for each user, load every single post they’ve ever made into memory, then count the resulting collection.

If you have 10,000 users with an average of 50 posts each, that’s 500,000 Post objects hydrated for the sake of a comparison. The query count is bad enough (one per user, so 10,001 queries total — classic N+1). The memory profile is, again, catastrophic.

The fix is withCount():

$users = User::withCount('posts')->get();
foreach ($users as $user) {
    if ($user->posts_count > 10) {
        $this->notifyProlificAuthor($user);
    }
}

withCount() adds a subquery to the original SELECT that returns just the count as a column. Two queries become one. The generated SQL looks roughly like:

SELECT
    users.*,
    (SELECT COUNT(*) FROM posts WHERE posts.user_id = users.id) AS posts_count
FROM users;

No N+1, no hydration of post objects, no half-million unused models sitting in memory. The cost is one extra column in the parent query and a (SELECT COUNT(*) ...) subquery that any database optimizes trivially.

The same applies to the rest of the aggregate-relationship family:

// Sum of order totals per user
User::withSum('orders', 'total')->get();
// Access via $user->orders_sum_total
// Average rating per product
Product::withAvg('reviews', 'rating')->get();
// Access via $product->reviews_avg_rating
// Most recent activity timestamp per user
User::withMax('activities', 'created_at')->get();
// Access via $user->activities_max_created_at
// Just check whether the relationship exists at all
User::withExists('orders')->get();
// Access via $user->orders_exists (boolean)

You can also constrain the aggregate by adding a callback:

// Count only the *published* posts per user
$users = User::withCount(['posts as published_posts_count' => function ($query) {
    $query->where('status', 'published');
}])->get();
foreach ($users as $user) {
    echo "{$user->name} has {$user->published_posts_count} published posts\n";
}

There’s a corollary worth knowing: $model->relationship() (with parentheses, returning a query builder) runs SELECT COUNT(*) and returns an integer without loading anything:

// FINE: runs SELECT COUNT(*) FROM posts WHERE user_id = ?
$count = $user->posts()->count();
// BAD: loads all posts, then counts the collection
$count = $user->posts->count();

The difference is one character — () vs no () — and several orders of magnitude of memory. If you find yourself reaching for ->relationship->count() inside a loop, almost certainly use withCount() on the parent query instead. If you're checking a single instance's relationship size outside a loop, use the parenthesized form.

6. Mass Assignment + firstOrCreate Is A Race Condition

This one is rare but devastating when it bites.

public function registerOrLogin(string $email, string $name): User
{
    return User::firstOrCreate(
        ['email' => $email],
        ['name' => $name, 'created_at' => now()],
    );
}

This looks atomic. It is not.

firstOrCreate runs two separate queries:

-- Step 1: try to find the user
SELECT * FROM users WHERE email = ? LIMIT 1;
-- Step 2: if not found, create them
INSERT INTO users (email, name, created_at) VALUES (?, ?, ?);

Between those two statements, another request can complete its own SELECT (also returning empty) and INSERT a row with the same email. Now both requests proceed to INSERT, your unique constraint fires on the second one, and you get a 500 error in production from code that looked completely idempotent.

The race window is small — usually a few milliseconds — but at any meaningful traffic level you will hit it. I have debugged production incidents where 0.1% of registrations failed with UniqueConstraintViolationException, and the root cause was exactly this pattern under load from a marketing campaign.

If your email column has a unique constraint (it should), the worst case is an exception. If it doesn’t have one, the worst case is silent duplicates that corrupt your business logic for months until someone notices the same user can log in with two different passwords.

The fix has two parts.

First, always have a unique constraint at the database level for any field you’re using as a logical key. Application code is not a constraint. Two web requests don’t coordinate through your validation rules. Your migration should look like:

Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('email');
    $table->string('name');
    $table->timestamps();
    $table->unique('email');   // This is what protects you under concurrency
});

Second, use the database’s atomic upsert primitive instead of firstOrCreate. Laravel exposes this as upsert(), which compiles to INSERT ... ON DUPLICATE KEY UPDATE (MySQL) or INSERT ... ON CONFLICT (PostgreSQL):

public function registerOrLogin(string $email, string $name): User
{
    User::upsert(
        [['email' => $email, 'name' => $name, 'created_at' => now()]],
        uniqueBy: ['email'],
        update: ['name'],   // columns to update if the row already exists
    );
    return User::where('email', $email)->firstOrFail();
}

One SQL statement, atomic at the database level, no race window. Two requests both calling this with the same email cannot both INSERT — the database serializes them.

If upsert() doesn't fit (because you need different INSERT vs UPDATE logic), wrap the original pattern in a transaction with a row lock:

public function registerOrLogin(string $email, string $name): User
{
    return DB::transaction(function () use ($email, $name) {
        $user = User::where('email', $email)->lockForUpdate()->first();
        if ($user) {
            return $user;
        }
        return User::create([
            'email' => $email,
            'name' => $name,
        ]);
    });
}

lockForUpdate() issues a SELECT ... FOR UPDATE, which holds a row-level lock until the transaction commits. The second request blocks until the first finishes, sees the row that was just inserted, and returns it cleanly. Slower than upsert(), more flexible, still correct.

The pattern to internalize: any “find or create” operation has a race condition unless the atomicity is enforced at the database level. firstOrCreate is convenient, but its name implies an atomicity it doesn't deliver. Treat it as a hint, not a guarantee.

7. JSON Columns Are A Performance Cliff Disguised As A Convenience

Laravel’s support for JSON columns is delightful. You can store flexible metadata, query it with arrow syntax, and treat it like a first-class column:

// Find users who opted into email notifications
User::where('preferences->notifications->email', true)->get();

That generates SQL like:

-- MySQL
SELECT * FROM users
WHERE JSON_EXTRACT(preferences, '$.notifications.email') = true;
-- PostgreSQL
SELECT * FROM users
WHERE (preferences->'notifications'->>'email')::boolean = true;

The query works. The query returns the right results. The query also does not use any index on the preferences column, regardless of how many indexes you've defined, unless you've created a very specific kind of generated-column or functional index.

For 1,000 users, you won’t notice. For 100,000 users, the query takes 4 seconds. For 1 million, it times out.

Here’s the table schema you probably have:

Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('email')->unique();
    $table->json('preferences');
    $table->timestamps();
    $table->index('preferences');   // ← This index is useless for JSON queries
});

The index('preferences') line indexes the JSON blob as a single value, which is not what you want and not what the query uses. JSON queries scan the table row by row, parsing each JSON document, regardless of what indexes exist on the column.

The fixes, in order of preference:

First, ask whether the field belongs in a column at all. If you’re querying it, filtering on it, or sorting by it, it’s not metadata — it’s data. Pull it out:

Schema::table('users', function (Blueprint $table) {
    $table->boolean('email_notifications')->default(false);
    $table->index('email_notifications');
});
// Migration script to backfill from JSON
User::whereRaw("preferences->>'$.notifications.email' = 'true'")
    ->update(['email_notifications' => true]);
// Now queries are trivial and fast
User::where('email_notifications', true)->get();

JSON columns should hold things you retrieve with the parent row, not things you search by. If a field crosses that line, it’s earned its own column.

Second, if the field truly is dynamic but high-volume, use a generated column with an index. This keeps the data in JSON but exposes a queryable, indexed projection:

-- MySQL
ALTER TABLE users
ADD COLUMN email_notifications BOOLEAN
GENERATED ALWAYS AS (preferences->>'$.notifications.email' = 'true') VIRTUAL,
ADD INDEX idx_email_notifications (email_notifications);

-- PostgreSQL (functional index, slightly different approach)
CREATE INDEX idx_email_notifications ON users
((preferences->'notifications'->>'email'));

Then your Eloquent queries can use the indexed path explicitly:

// MySQL with generated column
User::where('email_notifications', true)->get();
// PostgreSQL with functional index - query needs to match index expression exactly
User::whereRaw("preferences->'notifications'->>'email' = 'true'")->get();

Third, accept that some JSON queries are going to be slow and design around it — cache the results, run them in background jobs, or filter the candidate set with an indexed condition first to reduce the rows that need JSON scanning:

// Restrict to a small indexed candidate set first, then filter JSON
User::where('status', 'active')                           // uses index on status
    ->where('created_at', '>', now()->subMonths(3))       // uses index on created_at
    ->where('preferences->notifications->email', true)    // JSON scan on small set
    ->get();

If the first two conditions reduce 10M users to 50K, the JSON scan happens on 50K rows instead of 10M. Same JSON query, two orders of magnitude faster.

The general principle: a JSON column is a feature for storing structure, not for querying it at scale. Every unindexed JSON query you write is a promise to read every row.

The Pattern Behind All of These

If you look at all seven together, a pattern emerges. Eloquent’s defaults are optimized for clarity and small-to-medium datasets. Every gotcha above is a place where the clear version of the code is also the wrong version once the dataset gets big enough.

That’s not a flaw in Eloquent. It’s a deliberate trade-off, and for most applications it’s the right one. The framework is making a bet: that the developer’s time is worth more than the database’s, and that most apps will never grow large enough for the difference to matter.

When you cross into the territory where it does matter, the work shifts. You stop writing the “obvious” code and start writing the “honest” code — the version that admits the dataset is large, the version that names its constraints explicitly, the version that uses chunkById() and withCount() and lockForUpdate and unique()->values() because each of those has earned its place by saving you from a specific production incident.

There’s a third stage, too, which I’ll only mention here: at some point, Eloquent itself stops being the right tool. If you’re processing tens of millions of rows in a single job, you’re often better served by raw SQL, LazyCollection::remember(), generator-based pipelines, or — sometimes — moving the work out of PHP entirely and into a database stored procedure or a queue of small jobs.

But that’s a longer conversation. For now, if any of these seven gotchas reminded you of an incident from your own production logs, the comments are open. I genuinely want to hear them. The next article is shaped by the war stories you bring.

If you found this useful, the previous article (about what SQL your Eloquent relationships actually generate) is the foundation this one builds on. And if you’ve hit a scale problem in Laravel that isn’t on this list, leave a comment — that’s how the next one gets written.