---
title: 'Eloquent and Large Datasets: chunk, lazy and cursor | DevSense'
description: 'How to process and export hundreds of thousands of rows in Laravel without running out of memory: N+1 and preventLazyLoading, chunk vs chunkById, lazy and cursor, PDO buffering, CSV streaming, Query Builder and raw SQL for reports.'
faq:
- { question: 'What is the difference between chunk() and cursor() in Laravel?', answer: "chunk() runs many queries with LIMIT and OFFSET and passes a collection of N models to the closure, so it supports eager loading. cursor() runs a single query and hydrates models one at a time through a generator, but it can't eager load, and by default the PDO driver still keeps the entire raw result set in memory." }
- { question: 'Why does chunk() skip records when you update them inside the loop?', answer: "chunk() pages through the result with OFFSET. If the loop changes the column the query filters on (for example, processed = false → true), processed rows drop out of the result set, and the next OFFSET jumps over unprocessed ones. chunkById() pages by primary key (WHERE id > last) and doesn't have this problem." }
- { question: 'Why does cursor() still run out of memory?', answer: "Models are created one at a time, but by default PDO fetches the entire result set from the server and keeps it in a client-side buffer — that's how both pdo_mysql (buffered queries) and pdo_pgsql work. With millions of rows, that buffer won't fit into memory_limit. For volumes like that, use lazyById() or a server-side database cursor." }
- { question: 'When should you drop Eloquent in favour of Query Builder or raw SQL?', answer: "When you don't need models: aggregates and reports (GROUP BY, window functions), bulk UPDATE and INSERT ... SELECT, flat data exports. Eloquent creates an object per row and applies casts and events — across hundreds of thousands of rows that's extra seconds and hundreds of megabytes. Keep in mind that model events and observers are not fired for bulk operations." }
published: '2026-10-03'
---
# Eloquent and Large Datasets: N+1, chunk, lazy and cursor Without Running Out of Memory
The "all orders for the year as CSV" report works on staging, where there are five thousand orders, and dies in production with `Allowed memory size of 536870912 bytes exhausted`. The nightly bonus recalculation command processes half the customers and exits quietly — and the next day it turns out every other customer was skipped. A page listing fifty articles fires two hundred database queries.
All three stories come down to the same skill: understanding what Eloquent does with the database and with memory, and picking the right tool for the data volume. This article covers N+1, the difference between `chunk`, `chunkById`, `lazy` and `cursor`, why `cursor` won't save you from running out of memory, how to stream a million-row export, and when it's more honest to just write SQL.
**Related guides:** [Query Optimization](database-query-optimization) · [Database Indexes](database-indexes-deep-dive) · [PostgreSQL for Dashboards](postgresql-for-dashboards) · [Laravel Queues in Production](laravel-queues-production)
## Contents
* [Where memory exhaustion comes from](#why-memory)
* [N+1: hundreds of queries instead of two](#n-plus-one)
* [A map of methods for large result sets](#methods)
* [chunk() and the OFFSET trap](#chunk)
* [lazy() and lazyById(): a stream instead of batches](#lazy)
* [cursor() and PDO buffering](#cursor)
* [Exporting a million rows to CSV](#export)
* [When you need Query Builder or raw SQL](#query-builder)
* [How to measure](#measure)
* [Common Mistakes](#common-mistakes)
* [Checklist](#checklist)
* [Self-Test Quiz](#self-test-quiz)
---
## Where memory exhaustion comes from
`Order::where('year', 2026)->get()` does three things:
1. Runs the query and pulls **every row** of the result into PHP memory.
2. Creates **a model object per row**: attributes, a copy of the original values for change tracking, casts, relations.
3. Puts the models into a **collection** that lives as long as the variable does.
An Eloquent model takes up several times more memory than the table row itself. Ten thousand models are usually fine; a million is a guaranteed trip past `memory_limit`. So for large volumes there's one goal: **never hold the whole result in memory at once** — neither models nor raw rows.
---
## N+1: hundreds of queries instead of two
The classic case: a list of articles with author names.
```php
$articles = Article::latest()->take(50)->get();
foreach ($articles as $article) {
echo $article->author->name; // one extra query per article
}
```
One query for the articles plus one query per article for its author — 51 queries. Add tags and a comment count, and the page hits the database two hundred times. The fix is **eager loading**:
```php
$articles = Article::latest()
->with(['author:id,name', 'tags'])
->withCount('comments')
->take(50)
->get();
```
Now there are four queries: articles, authors, tags (via the pivot table) and the comment count as a subquery. `author:id,name` loads only the columns you need — you must include the `id` primary key, otherwise Eloquent cannot match authors to articles.
To keep N+1 from creeping back in unnoticed, disable lazy loading outside production:
```php
// app/Providers/AppServiceProvider.php
use Illuminate\Database\Eloquent\Model;
public function boot(): void
{
Model::preventLazyLoading(! $this->app->isProduction());
}
```
In development and tests, accessing a relation that wasn't loaded throws an exception, while production code keeps working. Recent Laravel versions also offer the opposite approach — `Model::automaticallyEagerLoadRelationships()`: the first time a relation is accessed, it is loaded for every model in the collection at once. It's a handy safety net, but an explicit `with()` remains clearer: the code shows what data the page needs.
---
## A map of methods for large result sets
| Method | Queries | In memory at once | Eager loading | Safe when the filter changes |
|-------|----------|-----------------------|---------------|---------------------------------|
| `get()` | 1 | All models | Yes | — |
| `chunk(N)` | Many (`LIMIT/OFFSET`) | N models | Yes | **No** |
| `chunkById(N)` | Many (`WHERE id > ?`) | N models | Yes | Yes |
| `lazy(N)` | Many (`LIMIT/OFFSET`) | N models, yielded one at a time | Yes | **No** |
| `lazyById(N)` | Many (`WHERE id > ?`) | N models, yielded one at a time | Yes | Yes |
| `cursor()` | 1 | 1 model + the entire raw result in the PDO buffer | **No** | Yes |
The default rule for background jobs and commands: **`lazyById()`** or **`chunkById()`**. They cap memory, support `with()` and don't skip records.
---
## chunk() and the OFFSET trap
`chunk()` splits the result set into pages using `LIMIT` and `OFFSET`:
```php
Customer::where('bonus_recalculated', false)
->chunk(1000, function (Collection $customers) {
foreach ($customers as $customer) {
$customer->recalculateBonus(); // sets bonus_recalculated = true
}
});
```
This code processes roughly half the customers. The first batch is rows 1–1000; once processed, they no longer match `bonus_recalculated = false`. The second query asks for `OFFSET 1000` — but the result set has already shifted by a thousand rows, so customers 1001–2000 are skipped. No error is raised, and the command finishes successfully.
`chunkById()` pages by primary key: each subsequent query is `WHERE id > :last_id ORDER BY id LIMIT 1000`. A shifting result set doesn't bother it:
```php
Customer::where('bonus_recalculated', false)
->chunkById(1000, function (Collection $customers) {
foreach ($customers as $customer) {
$customer->recalculateBonus();
}
});
```
`OFFSET` has a second problem too — performance. To return `OFFSET 900000 LIMIT 1000`, the database still reads and discards 900,000 rows. The last batches take many times longer than the first ones. Keyset pagination uses the primary key index and costs the same at any depth.
> [!WARNING]
> **Group conditions that use `orWhere`.** `chunkById()` and `lazyById()` add their own `id > ?` condition. If your query contains `orWhere`, without parentheses you end up with `a = 1 OR b = 2 AND id > 100`, and pagination breaks. Wrap your conditions in a closure:
>
> ```php
> Customer::where(function ($query) {
> $query->where('tier', 'gold')->orWhere('lifetime_cents', '>', 1_000_000);
> })->chunkById(1000, fn (Collection $customers) => /* ... */);
> ```
---
## lazy() and lazyById(): a stream instead of batches
Under the hood `lazy()` does the same thing as `chunk()`, but returns a `LazyCollection` — a stream of models you can iterate with a regular `foreach` and use collection methods on:
```php
Customer::where('newsletter', true)
->with('subscription')
->lazyById(1000)
->filter(fn (Customer $customer) => $customer->subscription?->isActive())
->each(fn (Customer $customer) => SendDigest::dispatch($customer->id));
```
`LazyCollection` methods run lazily: `filter` and `each` process models as they arrive, and only the current batch of 1000 sits in memory. `with()` works — relations are loaded for each batch with a separate query. For iterating in reverse order there's `lazyByIdDesc()`.
Code using `lazyById()` reads like an ordinary loop, so in new jobs it's more convenient than `chunkById()` with a closure.
---
## cursor() and PDO buffering
`cursor()` runs **a single** query and hydrates models one at a time through a generator:
```php
foreach (Order::where('status', 'paid')->cursor() as $order) {
// only one Order model is hydrated at a time
}
```
It looks perfect, but there are two limitations.
**No eager loading.** Only one model is in memory, so `with()` doesn't apply. Accessing `$order->customer` inside the loop is N+1 all over again — across the entire result set.
**The entire raw result is still in memory.** By default PDO fetches the whole query result from the server and keeps it in a client-side buffer:
* **pdo_mysql** runs in buffered query mode (`PDO::MYSQL_ATTR_USE_BUFFERED_QUERY = true`);
* **pdo_pgsql** receives the entire result from libpq at once.
Models are created one by one, but an array of raw rows for a million records sits in process memory — and sooner or later hits `memory_limit`. The Laravel documentation explicitly recommends `lazy()` over `cursor()` for very large volumes.
If you really need a single pass without pagination, there are two honest ways to stream.
**MySQL: an unbuffered query on a dedicated connection.**
```php
// config/database.php — a dedicated connection for exports
'mysql_unbuffered' => array_merge(config('database.connections.mysql'), [
'options' => [PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false],
]),
```
Until the result has been read to the end, the connection is busy: you can't run another query on it. That's why such a connection is used only for reading the stream, while writes go through the main one.
**PostgreSQL: a server-side cursor.**
```php
DB::transaction(function () {
DB::statement(
'DECLARE export_cursor NO SCROLL CURSOR FOR
SELECT id, customer_id, total_cents, created_at FROM orders WHERE created_at >= ?',
[now()->startOfYear()],
);
while ($rows = DB::select('FETCH 5000 FROM export_cursor')) {
foreach ($rows as $row) {
// stream $row somewhere
}
}
});
```
The cursor lives on the server, and PHP receives data in portions of 5000 rows. A cursor without `WITH HOLD` exists only inside a transaction. Don't keep that transaction open for hours: a long-running transaction prevents VACUUM from cleaning up old row versions.
In practice `lazyById()` covers 95% of cases, and a server-side cursor is needed when sorting isn't by primary key, or when the query is complex and expensive to re-run for every batch.
---
## Exporting a million rows to CSV
A typical export that doesn't run out of memory relies on three techniques: select only the columns you need, don't create models, and write the result to a stream rather than a string.
```php
// app/Http/Controllers/OrderExportController.php
use Symfony\Component\HttpFoundation\StreamedResponse;
public function __invoke(Request $request): StreamedResponse
{
$year = (int) $request->validate(['year' => ['required', 'integer', 'min:2020']])['year'];
return response()->streamDownload(function () use ($year) {
$out = fopen('php://output', 'w');
fputcsv($out, ['id', 'customer_id', 'total', 'created_at']);
DB::table('orders')
->select(['id', 'customer_id', 'total_cents', 'created_at'])
->whereYear('created_at', $year)
->lazyById(5000)
->each(function (object $row) use ($out) {
fputcsv($out, [
$row->id,
$row->customer_id,
number_format($row->total_cents / 100, 2, '.', ''),
$row->created_at,
]);
});
fclose($out);
}, "orders-{$year}.csv", ['Content-Type' => 'text/csv']);
}
```
* `DB::table()` instead of a model — you get lightweight `stdClass` objects with no casts or change tracking. For a query built on a model, `->toBase()` does the same.
* `select()` limits the columns: `SELECT *` drags along `TEXT` fields the CSV doesn't need.
* `streamDownload()` sends data to the client as it's generated; the response is never assembled in memory.
> [!NOTE]
> **Exports longer than 30 seconds belong in a queue.** An HTTP request will hit PHP-FPM, nginx or load balancer timeouts. A large export is better generated in a queued job into a file on disk or in S3, then sending the user a link. Details on worker timeouts and memory are in the article on [queues in production](laravel-queues-production#heavy-jobs).
Note the `whereYear()`: a function applied to a column prevents the use of an index on `created_at`. For large tables a range is more reliable — `whereBetween('created_at', [$from, $to])` (more in the article on [indexes](database-indexes-deep-dive)).
---
## When you need Query Builder or raw SQL
Eloquent shines when you need models: business logic, relations, events. For working with data "in bulk" it's often overkill.
**Compute aggregates in the database, not in PHP.**
```php
// Bad: loads every order into PHP to sum one column
$total = Order::where('status', 'paid')->get()->sum('total_cents');
// Good: the database returns one number
$total = Order::where('status', 'paid')->sum('total_cents');
// Reports: grouping and window functions belong in SQL
$daily = DB::table('orders')
->selectRaw('date(created_at) as day, count(*) as orders, sum(total_cents) as revenue')
->where('created_at', '>=', now()->subDays(30))
->groupByRaw('date(created_at)')
->orderBy('day')
->get();
```
**Bulk changes in a single query.**
```php
// One UPDATE instead of loading and saving 200 000 models
Order::where('status', 'pending')
->where('created_at', '<', now()->subDays(30))
->update(['status' => 'expired']);
// Batch upsert for imports: one statement per batch of rows
DB::table('product_prices')->upsert(
$rows, // array of ['sku' => ..., 'price_cents' => ..., 'updated_at' => ...]
uniqueBy: ['sku'],
update: ['price_cents', 'updated_at'],
);
```
A bulk `update()` through the Eloquent builder will set `updated_at`, but it **will not fire model events or observers**, won't apply mutators and won't check `$fillable`. If there's logic hooked to the `updated` event (cache invalidation, auditing), you have to run it explicitly.
**Complex queries — honest SQL with parameter binding.** `INSERT ... SELECT`, CTEs and window functions via `DB::select()` or `selectRaw()` read better than a chain of twenty builder methods. The main rule: values go in only through placeholders:
```php
// Safe: values are bound, never concatenated into SQL
$rows = DB::select(
'SELECT customer_id, sum(total_cents) AS spent
FROM orders WHERE created_at >= ? GROUP BY customer_id HAVING sum(total_cents) > ?',
[$from, 100_000],
);
```
Interpolating user input into an SQL string is a direct path to SQL injection, even in an "internal" report (more in the article on [web attacks](web-attacks-and-prevention)).
---
## How to measure
Don't guess — measure on a realistic data volume:
```php
$start = hrtime(true);
DB::enableQueryLog();
// ... code under test ...
logger()->info('export stats', [
'queries' => count(DB::getQueryLog()),
'peak_mb' => round(memory_get_peak_usage(true) / 1024 / 1024, 1),
'ms' => (int) ((hrtime(true) - $start) / 1_000_000),
]);
```
* `memory_get_peak_usage(true)` reports the peak, not the current value — and it's the peak that hits `memory_limit`.
* Enable the query log only for the duration of the measurement. In long-running commands it becomes a leak itself: every query is stored in an array. For the same reason, Telescope and Debugbar, which collect queries, bloat the memory of long-lived workers.
* For queries that are still slow after fixing N+1, look at the execution plan — `EXPLAIN ANALYZE` (covered in detail in the [query optimization masterclass](database-query-optimization)).
---
## Common Mistakes
**1. `chunk()` while changing a column from the condition.**
Half the records are silently skipped. Use `chunkById()` or `lazyById()`.
**2. `cursor()` as a cure for memory exhaustion.**
Models are created one at a time, but the raw result is buffered by PDO. With millions of rows it's the same OOM, just later.
**3. Accessing relations inside `cursor()`.**
Eager loading doesn't work, so you get N+1 across the whole result set.
**4. `get()->sum()`, `get()->count()`, `all()->filter()`.**
Aggregates and filters computed in PHP pull the whole table into memory. Compute them in the database.
**5. Ungrouped `orWhere` combined with `chunkById()`.**
The `id > ?` condition gets glued to your `OR`, and pagination breaks.
**6. A bulk `update()` where model events are required.**
Observers and events aren't fired — the cache isn't flushed and the audit log isn't written.
**7. Query log left enabled in a long-running command.**
Every query piles up in memory. Enable the log only for measurements.
---
## Checklist
1. Lists with relations are loaded via `with()` and `withCount()`; `preventLazyLoading()` is enabled outside production.
2. Result sets larger than a few thousand rows are processed via `lazyById()` or `chunkById()`, not `get()`.
3. `chunk()` and `lazy()` are not used when the loop changes columns from the query condition.
4. `cursor()` is used deliberately: without relations and with an understanding of PDO buffering.
5. Exports select only the columns they need, work without models and write to a stream.
6. Long-running exports and imports run in a queue, not in an HTTP request.
7. Aggregates and bulk changes run in the database as a single query.
8. Raw SQL uses parameter binding only.
9. Peak memory and query count have been checked on a realistic data volume.
---
## Summary
Eloquent isn't slow — it does exactly what you asked: loads everything the query returned and creates an object for every row. Large datasets call for a different question: "how much of this will be in memory at the same time?" For processing — `lazyById()`; for reports — aggregates in the database; for exports — a stream without models; for bulk changes — a single query. And let `chunk()` with a mutating condition and `cursor()` on millions of rows stay on the list of common mistakes rather than in your production.
---
## Self-Test Quiz
### Question 1: A command sets `processed = true` on records selected by the condition `processed = false`, using `chunk(500)`. What happens?
- A) All records will be processed, just more slowly than with `chunkById()`.
- B) Roughly half the records will be skipped: the result set shifts while `OFFSET` grows.
- C) Laravel will throw a concurrent modification exception.
Click to view the answer
**Answer: B**
After the first batch is processed, those rows no longer match the condition, and the next page with `OFFSET 500` jumps over records that haven't been processed yet. `chunkById()` pages by `id > last` and doesn't depend on the result set shifting.
### Question 2: Why can `cursor()` exhaust memory on a five-million-row result set, even though models are created one at a time?
- A) PHP generators keep all previously yielded values.
- B) By default the PDO driver fetches the entire query result and keeps it in a client-side buffer.
- C) `cursor()` automatically loads all model relations.
Click to view the answer
**Answer: B**
Both pdo_mysql in buffered mode and pdo_pgsql fetch the query result in full. For very large volumes, use `lazyById()`, an unbuffered MySQL connection or a PostgreSQL server-side cursor.
### Question 3: You need to move 300,000 overdue orders to the `expired` status. The `Order` model has cache invalidation hooked to its `updated` event. Which approach is correct?
- A) A single `Order::where(...)->update(['status' => 'expired'])` query — the events will fire automatically.
- B) A single bulk `update()` followed by an explicit cache flush, or `lazyById()` with saving models if the event logic is needed for every record.
- C) `Order::where(...)->get()->each->update(...)` — it's the fastest option.
Click to view the answer
**Answer: B**
A bulk `update()` doesn't fire model events or observers. If their logic is needed, run it explicitly after the query or stream the models through `lazyById()`. Option C loads all 300,000 models into memory.