Run Large PlanetScale Queries in Laravel With DB::olap()

PlanetScale's MySQL databases run on Vitess, which limits each query in its default OLTP workload. On PlanetScale, a query can return, update, or delete at most 100,000 rows, and the result set can be at most 64 MiB. A reporting query or a CSV export over a large table can fail on those limits, even though the same query runs fine on a local MySQL server.

Vitess has a second workload, OLAP, without those limits. Laravel 13.36 adds a DB::olap() method that switches a connection to OLAP for the length of a callback.

#Switching Workloads by Hand

Before this release, the way to run a query in OLAP mode was a raw statement and a try/finally block:

try {
    DB::statement("set workload = 'olap'");
 
    $rows = Order::query()->where('created_at', '>=', now()->subYears(3))->get();
} finally {
    DB::statement("set workload = 'oltp'");
}

The raw statement runs on the write connection. If the connection has read replicas, the select goes to a read connection that is still in OLTP mode.

The workload is also a session setting, so a missing finally block leaves the connection in OLAP mode for the next job in a queue worker or the next request in Octane.

#Using DB::olap()

Jack Bayliss added olap() to the MySQL connection class. It sets the workload, runs the callback, and sets the workload back to OLTP in a finally block. It returns whatever the callback returns:

$revenue = DB::olap(fn () => Order::query()
    ->selectRaw("customer_id, date_format(created_at, '%Y-%m') as month, sum(total) as revenue")
    ->where('created_at', '>=', now()->subYears(3))
    ->groupBy('customer_id', 'month')
    ->get()
);

The method sets the workload on the write connection and on the read connection, so the query runs in OLAP mode whichever connection it uses. If the read connection has not connected yet, olap() connects it first.

When the database is not Vitess, olap() calls the callback and does nothing else. You can use the same code against a local MySQL server, in CI, and on PlanetScale.

The callback receives the connection, which is useful for a connection other than the default one:

DB::connection('analytics')->olap(function ($connection) {
    return $connection->table('page_views')
        ->selectRaw('path, count(*) as views')
        ->groupBy('path')
        ->get();
});

The olap() method is defined on MySqlConnection, so calling DB::olap() when the default connection is PostgreSQL or SQLite throws a BadMethodCallException.

#A Queued Export

A queued job that writes a large export to a file can do all of its querying inside the callback:

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;
 
class ExportOrders implements ShouldQueue
{
    use Queueable;
 
    public function __construct(public string $path) {}
 
    public function handle(): void
    {
        $stream = fopen('php://temp', 'w+');
 
        fputcsv($stream, ['id', 'customer_id', 'total', 'created_at']);
 
        DB::olap(function () use ($stream) {
            Order::query()
                ->select('id', 'customer_id', 'total', 'created_at')
                ->lazyById(5000)
                ->each(fn (Order $order) => fputcsv($stream, [
                    $order->id,
                    $order->customer_id,
                    $order->total,
                    $order->created_at,
                ]));
        });
 
        rewind($stream);
 
        Storage::put($this->path, $stream);
    }
}

Each chunk that lazyById() fetches runs while the workload is OLAP. Once the callback returns, the worker's connection is back in OLTP mode for the next job.

#Keep the Query Inside the Callback

The workload switches back as soon as the callback returns. A lazy result that has not run its query yet runs it later, in OLTP mode:

// The query runs after olap() has switched back to OLTP
$orders = DB::olap(fn () => Order::query()->cursor());
 
foreach ($orders as $order) {
    // ...
}

The cursor(), lazy(), and lazyById() methods return a LazyCollection that runs its query when you iterate it. Iterate it inside the callback, as in the export above, or call get() there.

#Detecting Vitess

The same PR adds isVitess() to the MySQL connection. It returns true when the server version string contains Vitess or PlanetScale:

if (DB::connection()->isVitess()) {
    // PlanetScale-specific behavior
}

The olap() method uses this check to decide whether to switch the workload. Like isMaria(), it connects to the database to read the server version.

#When to Use OLAP Mode

PlanetScale's documentation recommends against OLAP mode for regular application queries. The OLTP limits stop long-running queries from using up the database's resources. Its system limits page suggests paginating large queries, or copying the data to a separate analytics database for reporting. It also warns that OLAP queries can fail with errors if the connection to PlanetScale is interrupted.

A good fit for DB::olap() is an occasional export, a backfill, or an admin report that has to read more than 100,000 rows in one query.

#Further Reading

• Rule Builders for Every Validation Rule in Laravel 13.36
• PlanetScale Database Migrations for Laravel
• Laravel Pagination Explained (with the SQL)

Jack Bayliss contributed olap() and isVitess() in #61932.