Choosing Laravel Paginator vs Manual Offset/Limit for Large API Result Sets

The Decision

When an API must return thousands of rows, the way you slice the result set determines both response latency and server memory. Laravel ships with a paginate() method that builds pagination metadata and links automatically, while a manual offset()/limit() pair gives you raw control but forces the database to scan and discard rows for every page. The practical takeaway: use the built‑in paginator for most Eloquent or query‑builder queries; fall back to manual offset/limit only when you need a non‑standard cursor or have a proven performance reason.

Constraints to Consider

• Laravel version – cursor pagination (cursorPaginate()) arrived in Laravel 10; Laravel 8/9 only support offset‑based paginate().
• Page size – very large pages (>1 000 rows) can exhaust PHP memory; keep the default 15‑50 range unless you benchmark otherwise.
• Multi‑tenancy / sharding – the paginator runs a separate count(*) query; ensure it executes on the same database connection as the data query.
• API contract – if consumers expect a custom envelope (e.g., {data:[], meta:{}}) you can still use the paginator and transform the response.

Supported Options at a Glance

Feature	paginate() (offset)	cursorPaginate() (cursor)	Manual offset()/limit()
Automatic page links	Yes	Yes (next/prev cursors)	No
Total count query	Yes (extra SELECT COUNT(*))	No (cursor only)	No (unless you add it)
Performance on deep pages	Degrades linearly (offset scan)	Constant (index seek)	Same as offset paginate
Memory footprint	Loads one page + count	Loads one page only	Loads one page only
Laravel version required	≥ 5.1	≥ 10.x	Any
Custom sorting / filtering	Full Eloquent support	Full Eloquent support	Full control

Trade‑offs Explained

Offset pagination (paginate())

Pros: zero‑config, returns current_page, last_page, total out of the box, works with any Eloquent query. Cons: the database must skip (page‑1)*perPage rows; on a 100 k‑row table page 500 can take seconds.

Cursor pagination (cursorPaginate())

Pros: constant‑time seeks using a unique, ordered column (usually id or a composite key). No COUNT(*) query, so lower load. Cons: cannot jump to an arbitrary page; clients must follow next_cursor/prev_cursor. Requires Laravel 10+.

Manual offset/limit

Pros: absolute control over the SQL, useful for legacy APIs that expect a bespoke envelope. Cons: you re‑implement link generation, total count, and you inherit the same offset‑scan penalty. Only choose this when the paginator cannot express the required cursor logic (e.g., composite ordering across joined tables).

Concrete Implementation

Controller using the built‑in paginator

query('per_page', 20);
        $perPage = min(max($perPage, 1), 100); // clamp

        $orders = Order::query()
            ->when($request->has('status'), fn($q) => $q->where('status', $request->query('status')))
            ->orderBy('created_at', 'desc')
            ->paginate($perPage);

        // Transform to a stable API envelope
        return response()->json([
            'data' => $orders->items(),
            'meta' => [
                'current_page' => $orders->currentPage(),
                'last_page'    => $orders->lastPage(),
                'per_page'     => $orders->perPage(),
                'total'        => $orders->total(),
            ],
            'links' => [
                'first' => $orders->url(1),
                'last'  => $orders->url($orders->lastPage()),
                'prev'  => $orders->previousPageUrl(),
                'next'  => $orders->nextPageUrl(),
            ],
        ]);
    }
}

Run this in a standard Laravel controller (no extra permissions). The paginate() method automatically adds a COUNT(*) query on the same connection.

Manual offset/limit alternative (for comparison)

query('page', 1);
$perPage = (int) $request->query('per_page', 20);
$offset = ($page - 1) * $perPage;

$orders = Order::query()
    ->when($request->has('status'), fn($q) => $q->where('status', $request->query('status')))
    ->orderBy('created_at', 'desc')
    ->offset($offset)
    ->limit($perPage)
    ->get();

$total = Order::query()
    ->when($request->has('status'), fn($q) => $q->where('status', $request->query('status')))
    ->count();

return response()->json([
    'data' => $orders,
    'meta' => [
        'current_page' => $page,
        'last_page'    => (int) ceil($total / $perPage),
        'per_page'     => $perPage,
        'total'        => $total,
    ],
]);

Notice the duplicated where logic and the extra count() query – easy to drift out of sync.

Validation & Benchmarking

Functional test (run in php artisan test)

count(55)->create(['status' => 'open']);

        $response = $this->getJson('/api/orders?per_page=20&status=open');

        $response->assertOk()
            ->assertJsonStructure([
                'data',
                'meta' => ['current_page','last_page','per_page','total'],
                'links' => ['first','last','prev','next'],
            ]);
        $this->assertEquals(3, $response->json('meta.last_page'));
        $this->assertEquals(55, $response->json('meta.total'));
    }
}

Run with php artisan test --filter=OrderPaginationTest. No special permissions beyond the testing database.

Performance benchmark (run locally, not in production)

1. Seed a table with >100 k rows: php artisan db:seed --class=LargeOrderSeeder (requires db:seed permission).
2. Create a one‑off Artisan command or Tinker script that executes both approaches 10 times and records microtime(true) before/after.
3. Compare average latency; expect paginate() < 200 ms for page 1, while manual offset on page 5000 will show linear growth.

orderBy('id')->offset(($page-1)*$perPage)->limit($perPage)->get();
    $manualTimes[] = microtime(true) - $start;

    $start = microtime(true);
    Order::query()->orderBy('id')->paginate($perPage, ['*'], 'page', $page);
    $paginateTimes[] = microtime(true) - $start;
}

echo 'Manual avg: ' . (array_sum($manualTimes)/$iterations) . "s\n";
echo 'Paginate avg: ' . (array_sum($paginateTimes)/$iterations) . "s\n";

Expected check: the paginator’s average should stay roughly constant; the manual offset average should increase with deeper pages. If the manual approach is faster for your specific index/layout, document the reason before committing.

Limitations & Gotchas

• Memory – a page size of 5 000 rows can push PHP memory >256 MiB; keep per_page ≤ 100 unless you profile.
• Cursor vs offset mixing – calling cursorPaginate() after an offset() on the same query builder yields undefined ordering; pick one strategy per endpoint.
• Multi‑tenancy – if you switch connections via Model::setConnection(), the paginator’s count query respects the current connection, but a manual count() you write must do the same.
• Version gaps – Laravel 8/9 lack cursorPaginate(); upgrading to 10+ unlocks constant‑time deep paging.

How to Verify in Production

Add a lightweight health‑check endpoint that returns the pagination meta for a known dataset (e.g., first 5 rows of a reference table). Monitor the response time and the total field; a sudden spike indicates a missing index or a runaway page size. Alert if current_page > last_page or if total diverges from the table’s COUNT(*) (run a scheduled SELECT COUNT(*) on the replica).

Summary Decision Checklist

• ✅ Default to paginate() for offset‑based APIs (Laravel ≥ 5.1).
• ✅ Upgrade to Laravel 10+ and use cursorPaginate() when clients can follow cursors and you need deep‑page performance.
• ⚠️ Use manual offset()/limit() only for non‑standard envelopes or legacy contracts; keep the count query in sync.
• 🔧 Clamp per_page, benchmark with >100 k rows, and monitor production pagination latency.