PHP 8.6 Lets Postgres Fetch in Chunks, and Your Laravel Export Stops Eating RAM

PHP 8.6 adds a PDO pgsql chunk size attribute so Laravel's cursor() finally streams Postgres rows. Set it, size it, and dodge the silent one-statement trap.

Steven Richardson
Steven Richardson
· 7 min read

The export ran fine locally against MySQL and died in production against Postgres. Same code: DB::table('events')->cursor(), piped into a CSV, 4.2 million rows, Allowed memory size of 536870912 bytes exhausted. Nothing in the Laravel code explains it, because the problem sits below Laravel — until PHP 8.6, pdo_pgsql asked libpq for the entire result set before you fetched a single row, and the generator was streaming out of a buffer you had already paid for. The new PDO pgsql chunk size attribute is the fix, and it is two lines.

Confirm PHP 8.6 and libpq 17 are both present#

Pdo\Pgsql::ATTR_CHUNK_SIZE is only defined when PHP 8.6 was compiled against libpq 17 or later. Plenty of distro and Docker images still ship libpq 15 or 16, so check the runtime rather than the PHP version — on a mismatch the constant does not exist and you get an Error, not a graceful fallback.

php -r 'var_dump(defined("Pdo\Pgsql::ATTR_CHUNK_SIZE"));'
# bool(true) on PHP 8.6 + libpq >= 17

# The client library version PDO was built against:
php -r '$p = new PDO(getenv("DSN")); var_dump($p->getAttribute(PDO::ATTR_CLIENT_VERSION));'

If that returns bool(false), the rest of this article does nothing for you — rebuild the image against libpq 17 first. The wider set of things to check when moving a Laravel app onto this version is in my PHP 8.6 upgrade checklist.

Measure what cursor() costs you today#

Before changing anything, get a number. Peak memory is the honest metric here, not wall time, and it is the one that fails the job. Run this against the real table on a staging box with the same Postgres.

use Illuminate\Support\Facades\DB;

$rows = 0;

foreach (DB::table('events')->orderBy('id')->cursor() as $event) {
    $rows++;
}

printf(
    "%d rows, peak %.1f MB\n",
    $rows,
    memory_get_peak_usage(true) / 1024 / 1024, // true = real allocated memory
);

On PHP 8.5 and below with Postgres, peak memory tracks the size of the result set, not the size of one row. Four million rows of a dozen columns will sit somewhere north of 2 GB. The same loop on MySQL holds flat, because pdo_mysql has had unbuffered queries for years — which is exactly why this bug only ever shows up on one of your two databases.

Set the PDO pgsql chunk size on the connection#

The cleanest place for it is the options array of the pgsql connection, which Laravel passes straight into the PDO constructor. Every statement prepared on that connection inherits the value, including the ones Laravel builds internally for cursor() and lazy().

// config/database.php

'pgsql' => [
    'driver' => 'pgsql',
    // ...
    'options' => extension_loaded('pdo_pgsql') && defined('Pdo\Pgsql::ATTR_CHUNK_SIZE')
        ? [Pdo\Pgsql::ATTR_CHUNK_SIZE => 2000] // rows per round trip
        : [],
],

That defined() guard matters if the same config boots on a machine without libpq 17 — a CI container, a colleague's laptop. With the attribute set, DB::cursor() becomes what you always thought it was: the generator is lazy and the wire is lazy, and peak memory drops to the cost of 2,000 rows plus whatever your loop body holds on to. The same applies to Laravel's lazy collections when you are writing CSVs — the collection was never the problem, the driver was.

Set the chunk size for one heavy statement instead#

Turning lazy fetching on globally changes the behaviour of every query on that connection, including the small ones where buffering is free and the extra round trips are not. I prefer to scope it to the report that needs it.

use Illuminate\Support\Facades\DB;

$pdo = DB::connection('pgsql')->getPdo();
$pdo->setAttribute(Pdo\Pgsql::ATTR_CHUNK_SIZE, 5000);

try {
    foreach (DB::table('events')->orderBy('id')->cursor() as $event) {
        fputcsv($handle, (array) $event);
    }
} finally {
    // 0 restores buffered fetching for everything after this job
    $pdo->setAttribute(Pdo\Pgsql::ATTR_CHUNK_SIZE, 0);
}

Or skip Laravel's query builder entirely and set it as a driver option on a single prepared statement, which never leaks into the rest of the request:

$statement = DB::connection('pgsql')->getPdo()->prepare(
    'select id, type, payload, created_at from events order by id',
    [Pdo\Pgsql::ATTR_CHUNK_SIZE => 5000],
);

$statement->execute();

while ($event = $statement->fetch(PDO::FETCH_ASSOC)) {
    fputcsv($handle, $event);
}

Pick a chunk size you can defend#

The attribute accepts 1 through 2147483647. 1 is single-row mode — correct, lowest memory, and one network round trip per row, which on a remote database turns a two-minute export into an afternoon. 0 is the default and means "let PDO::ATTR_PREFETCH decide", which in practice means the old buffered behaviour. Any other integer throws a ValueError.

I start at 2,000 and move from there. A few thousand rows is small enough that memory stays flat and large enough that round trips disappear into the noise. Size it against the row, not the table: a payload jsonb column averaging 40 KB makes 5,000 rows a 200 MB buffer, which is the problem you were trying to solve.

Keep chunkById() when the loop writes#

Lazy fetching holds one result stream open for the life of the loop. If the body of that loop writes — updating the row you just read, dispatching a job that touches the same connection, lazy-loading a relation — you are running a second statement against a connection that already has an active one. chunkById() re-queries each page, so none of that applies.

// Safe: each page is its own query, and the key cursor survives the writes
Event::query()
    ->where('processed_at', null)
    ->chunkById(1000, function ($events) {
        foreach ($events as $event) {
            $event->update(['processed_at' => now()]);
        }
    });
Method Memory Affected by chunk size Use it when
chunk() One page No — each page is a fresh query Read-only paging, stable ordering
chunkById() One page No The loop writes, or rows move
cursor() One row hydrated Yes Read-only streaming, one pass
lazy() One page hydrated No — pages under the hood Collection ergonomics over a big set

cursor() is the only one of the four that the attribute changes, because it is the only one that leaves a single statement open across the whole loop.

Avoid these four gotchas#

One active statement per connection. This is the sharp one. In lazy mode, running another statement on the same connection silently discards the unread rows of the first — no exception, no warning, your export just ends early with a plausible-looking file. Audit the loop body for Eloquent relations, cache misses that fall through to the database, and anything dispatching synchronously. If you need a second query, open a second connection.

rowCount() lies. Under lazy fetching it returns the number of rows most recently retrieved from the server, not the total. Any progress bar built on it will be wrong. Count with a separate select count(*) on another connection, or don't count.

Scrollable cursors reject it. A statement with PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL ignores the connection's chunk size, and setting the attribute to 1 or more on that statement throws a ValueError.

PDO::ATTR_PREFETCH wins. Setting PDO::ATTR_PREFETCH on the connection or on a statement overrides the connection's chunk size. If you set both, the chunk size is the one that gets ignored — pick one and stay with it.

One more, less a gotcha than a consequence: a streaming export keeps a backend busy for its whole duration. Behind PgBouncer in transaction pooling mode that is a server-side connection you have taken out of circulation for minutes. Run these jobs against a read replica and keep them off the primary's pool entirely.

Wrapping Up#

Check defined('Pdo\Pgsql::ATTR_CHUNK_SIZE') on your production image, set it to 2,000 on the pgsql connection, and re-run the export with memory_get_peak_usage() printed at the end. If the number is flat, you are done; if it is not, the loop body is holding references, not the driver. PHP 8.6.0 is due on 19 November 2026, so this is a reason to schedule the upgrade rather than a reason to rewrite the export.

If the box this runs on is also overdue a server-side upgrade, moving Postgres major versions under a live app pairs well with the client-side work — new libpq, new server, one maintenance window.

FAQ#

What is Pdo\Pgsql::ATTR_CHUNK_SIZE?

It is a PHP 8.6 PDO attribute that sets the maximum number of rows a statement retrieves from the PostgreSQL server at a time during lazy fetching. Any value from 1 to 2147483647 enables lazy fetching and takes precedence over PDO::ATTR_PREFETCH. The default is 0, which leaves fetching to PDO::ATTR_PREFETCH, and it requires PHP compiled against libpq 17 or later.

Why does DB::cursor() still run out of memory on Postgres?

Because the laziness was only ever in PHP. cursor() returns a generator, but before PHP 8.5 the pdo_pgsql driver asked libpq for the complete result set and buffered it in client memory before the first fetch() returned. The generator was iterating over memory you had already allocated, so peak usage tracked the whole result set. PHP 8.5 added single-row fetching via PDO::ATTR_PREFETCH => 0, and PHP 8.6 made the batch size configurable.

How do I stream large result sets in Laravel with Postgres?

Set Pdo\Pgsql::ATTR_CHUNK_SIZE to a few thousand in the options array of the pgsql connection in config/database.php, then iterate with DB::cursor() or ->cursor() on a query builder. For a one-off report, set the attribute on the PDO instance before the loop and reset it to 0 afterwards. If the loop writes to the database, use chunkById() instead — lazy fetching allows only one active statement per connection.

What chunk size should I use?

Start at 2,000 and tune against the width of your rows rather than the size of the table. A value of 1 gives the lowest possible memory use but costs a network round trip per row, which is painful on a remote database. Large values rebuild the problem you were solving: 5,000 rows holding a 40 KB JSON column is a 200 MB buffer.

Does this work on MySQL?

No. Pdo\Pgsql::ATTR_CHUNK_SIZE is a pdo_pgsql attribute and the equivalent on MySQL is PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, set to false for unbuffered queries. MySQL has had that option for years, which is why cursor() holds steady on MySQL and climbed on Postgres until now.

Steven Richardson
Steven Richardson

CTO at Digitonic. Writing about Laravel, architecture, and the craft of leading software teams from the west coast of Scotland.