Ehsan Bagherzadegan
Back to articles
7 min read

Laravel's DB::olap(): Running Large Analytical Queries on PlanetScale and Vitess

Laravel 13.36 adds DB::olap() to safely run analytical queries on Vitess-backed databases like PlanetScale, bypassing the 100k-row OLTP limit with automatic workload switching and guaranteed cleanup.

If you have used PlanetScale or any Vitess-backed MySQL database, you have likely hit the OLTP limits: queries that return more than 100,000 rows or exceed 64 MiB of result data are rejected. This is by design — Vitess optimises for transactional workloads where low latency and high concurrency matter more than scanning large datasets.

Laravel 13.36 introduced DB::olap() to solve this cleanly. It temporarily switches your connection to Vitess's OLAP workload, runs your analytical query without the row or size limits, and guarantees the workload is reset afterwards — even if the query throws an exception.

The problem: OLTP limits in Vitess

PlanetScale runs on Vitess, which separates traffic into OLTP (online transaction processing) and OLAP (online analytical processing) workloads. The default OLTP workload enforces strict limits:

  • 100,000 rows maximum per query
  • 64 MiB maximum result set size

These limits protect the database from runaway queries that could starve transactional traffic. But they also block legitimate analytical work: monthly revenue reports, data exports, backfills, or any query that scans large portions of a table.

Before DB::olap(), your options were awkward:

  • Use LIMIT and OFFSET to paginate through results (slow, inconsistent snapshots)
  • Copy data to a separate analytics warehouse (overkill for occasional reports)
  • Run raw SQL with SET workload = 'olap' and remember to reset it manually (error-prone)

How DB::olap() works

The method lives on the database connection. You pass it a closure that receives the connection instance. Laravel sets workload = 'olap' on both the read and write connections, executes your closure, then resets to workload = 'oltp' in a finally block.

use Illuminate\Support\Facades\DB;

$revenueByMonth = DB::connection('mysql')->olap(function ($connection) {
    return $connection->table('orders')
        ->selectRaw('DATE_FORMAT(created_at, "%Y-%m") as month, SUM(total) as revenue')
        ->where('status', 'completed')
        ->groupBy('month')
        ->orderBy('month')
        ->get();
});

The callback receives the connection so you can use the query builder, Eloquent, or raw statements — whatever fits the task. The return value of the closure becomes the return value of olap().

It handles read replicas automatically

If your config/database.php defines read and write hosts (common on PlanetScale), olap() sets the workload on both. You don't need to think about which connection your query will hit.

It is a no-op on non-Vitess databases

On standard MySQL, MariaDB, PostgreSQL, or SQLite, the callback runs without any workload changes. This means the same code works in local development (SQLite), CI (MySQL), and production (PlanetScale) without conditional logic.

Detecting Vitess at runtime

You can check whether the current connection is Vitess-backed:

if (DB::connection('mysql')->isVitess()) {
    // PlanetScale or another Vitess cluster
}

This is useful if you want to conditionally enable features that only make sense on Vitess, or to show a different code path in a package that supports multiple database backends.

Practical patterns

Large exports with lazy collections

For exports that could return hundreds of thousands of rows, combine olap() with lazyById() (or lazy() / cursor()) to stream results in chunks. The entire iteration must happen inside the callback, otherwise the cursor will execute after the workload has been reset to OLTP.

use Illuminate\Support\Facades\DB;
use Illuminate\Support\LazyCollection;

$orders = DB::connection('mysql')->olap(function ($connection) {
    return $connection->table('orders')
        ->where('status', 'completed')
        ->orderBy('id')
        ->lazyById(1000); // Returns a LazyCollection
});

// Safe: iteration happens inside the callback scope
foreach ($orders as $order) {
    $writer->writeRow([
        $order->id,
        $order->customer_email,
        $order->total,
        $order->created_at,
    ]);
}

If you need to return the LazyCollection and iterate outside, you cannot — the underlying cursor would run in OLTP mode. Instead, collect the data inside the callback or write directly to a stream/file within the closure.

Periodic analytical reports

A scheduled command that generates a weekly sales summary:

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Carbon;

class GenerateWeeklySalesReport extends Command
{
    protected $signature = 'report:weekly-sales';
    protected $description = 'Generate weekly sales summary';

    public function handle(): int
    {
        $since = Carbon::now()->subWeek();

        $summary = DB::connection('mysql')->olap(function ($conn) use ($since) {
            return $conn->table('orders')
                ->where('status', 'completed')
                ->where('created_at', '>=', $since)
                ->selectRaw('
                    DATE(created_at) as day,
                    COUNT(*) as orders,
                    SUM(total) as revenue,
                    AVG(total) as avg_order_value
                ')
                ->groupBy('day')
                ->orderBy('day')
                ->get();
        });

        // Store, email, or push to a dashboard
        $this->info("Report generated: {$summary->count()} days");
        return self::SUCCESS;
    }
}

One-off backfills and data migrations

When you need to update millions of rows based on a complex calculation, olap() lets you read the full dataset without limit errors. Pair it with chunked updates:

use Illuminate\Support\Facades\DB;

DB::connection('mysql')->olap(function ($conn) {
    $conn->table('products')
        ->whereNull('search_vector')
        ->orderBy('id')
        ->chunkById(500, function ($products) use ($conn) {
            foreach ($products as $product) {
                $vector = computeSearchVector($product);
                $conn->table('products')
                    ->where('id', $product->id)
                    ->update(['search_vector' => $vector]);
            }
        });
});

Note that the UPDATE statements run in OLAP mode too. This is fine for one-off maintenance, but avoid running heavy writes in OLAP as a regular pattern — it can impact cluster performance.

Gotchas and limitations

Gotcha Explanation
Lazy collections must be consumed inside the callback cursor(), lazy(), lazyById() return a LazyCollection that executes the query when iterated. If you return it and iterate later, the query runs in OLTP mode and fails.
Not for regular application queries PlanetScale recommends OLAP only for occasional analytical workloads. Using it for everyday queries defeats the purpose of the OLTP/OLAP separation and can degrade cluster performance.
No transaction support across workload switch The workload change is a session variable, not a transaction. You cannot wrap the callback in DB::transaction() and expect the workload reset to be transactional.
Read/write connections both switch If you have sticky reads enabled (sticky: true), a prior write in the same request will have pinned you to the write connection. olap() sets the workload on both, so the sticky behaviour still applies.
Connection pooling If you use a connection pooler (e.g., PgBouncer for Postgres, though olap() is MySQL/Vitess-specific), ensure the session variable survives checkout/checkin. Vitess connections are typically not pooled in a way that loses session state.

When to reach for DB::olap()

Use it when:

  • You need to scan or aggregate more than 100,000 rows on PlanetScale/Vitess
  • You are building admin dashboards, scheduled exports, or one-off backfills
  • You want a safe, framework-native way to toggle the workload without raw SQL

Avoid it when:

  • The query runs on every user request (move it to a scheduled job or materialised view instead)
  • You can rewrite the query to stay under OLTP limits with proper indexing
  • You are on a non-Vitess database where the limits don't exist (it still works, but adds no value)

A note on Laravel versions

DB::olap() arrived in Laravel 13.36 (October 2026). If you are on an older version, you can achieve the same manually:

// Manual equivalent for older Laravel versions
$connection = DB::connection('mysql');
$connection->statement("SET workload = 'olap'");

try {
    $results = $connection->table('orders')->...->get();
} finally {
    $connection->statement("SET workload = 'oltp'");
}

Upgrading to 13.36+ gives you the safety of the finally block, automatic read-replica handling, and the no-op behaviour on non-Vitess connections.

Takeaway

Vitess's OLTP/OLAP separation is a thoughtful design: it protects transactional performance while still allowing analytical queries when you explicitly opt in. DB::olap() makes that opt-in ergonomic and safe in Laravel — no raw SQL, no forgotten resets, no environment-specific branches. If you run PlanetScale and have been paginating around the 100k limit, this is the tool you were waiting for.

Sources