Eradicating OFFSET: Cursor Pagination in Laravel

#laravel #php #architecture #database

The Silent Killer: Offset Pagination

When you build an API endpoint to return a list of users or transactions, the default implementation is almost always Offset Pagination. In Laravel, you simply call User::paginate(15). Under the hood, this generates a SQL query like SELECT * FROM users LIMIT 15 OFFSET 150000;.

For the first few pages, this performs flawlessly. However, as your enterprise platform scales and a user attempts to view page 10,000, this query becomes a catastrophic architectural bottleneck. Relational databases like PostgreSQL and MySQL do not magically jump to row 150,000. To satisfy the OFFSET command, the database engine must physically scan the B-Tree index, fetch 150,015 rows into memory, discard the first 150,000 rows, and return the remaining 15.

This results in massive CPU spikes, extreme memory allocation, and degraded API response times (often jumping from 10ms to several seconds). Furthermore, if a new record is inserted while the user is clicking from Page 1 to Page 2, standard offset pagination causes a "Data Shift," resulting in the user seeing duplicate records.

At Smart Tech Devs, we eradicate this computational waste. For any enterprise dataset exceeding 100,000 rows, we abandon `OFFSET` entirely and implement Cursor Pagination (Keyset Pagination).

The Philosophy of Keyset Pagination

Cursor pagination flips the mathematical approach. Instead of telling the database "skip 150,000 rows," we tell the database exactly where we left off. If the last user we saw on Page 1 had an ID of 150000, our query for Page 2 becomes SELECT * FROM users WHERE id > 150000 ORDER BY id ASC LIMIT 15;.

Because the id column is indexed, the database engine executes an O(1) B-Tree lookup. It instantly jumps to ID 150000 without scanning the preceding rows, fetching the next 15 records in less than a millisecond. The performance curve remains perfectly flat whether you are on Page 1 or Page 1,000,000.

Phase 1: Implementing Laravel Cursor Pagination

Laravel natively supports this architecture via the cursorPaginate() method. When you use this, Laravel generates an encoded string (a cursor) containing the exact values of the sorted columns from the last item in the dataset.


namespace App\Http\Controllers;

use App\Models\Transaction;
use Illuminate\Http\Request;

class FinancialLedgerController extends Controller
{
    public function index(Request $request)
    {
        // 1. Execute Cursor Pagination
        // We order by ID (which is strictly sequential and indexed)
        $transactions = Transaction::orderBy('id', 'desc')->cursorPaginate(50);

        // 2. The JSON response automatically includes a 'next_cursor' string.
        // The frontend must pass this string as a ?cursor= query parameter 
        // to fetch the next sequential page.
        return response()->json($transactions);
    }
}

Phase 2: Architecting Complex Multi-Column Sorting

Cursor pagination is mathematically perfect when sorting by a unique, sequential column like an auto-incrementing ID or a strict timestamp. However, enterprise applications often require complex sorting—for example, sorting an e-commerce catalog by price or views.

If you sort purely by views, cursor pagination breaks. Multiple products can have exactly 500 views. If the cursor just says WHERE views > 500, the database won't know which of the remaining 500-view products to return. To architect this correctly, you must construct a composite cursor by strictly appending a unique tie-breaker column (like the primary key) to the sort definition.


namespace App\Domain\Catalog\Queries;

use App\Models\Product;
use Illuminate\Http\Request;

class GetTrendingProductsQuery
{
    public function execute(Request $request)
    {
        // 1. We want to sort by 'views' (Descending).
        // 2. Because 'views' is NOT unique, we MUST add a secondary sort 
        //    on a unique column ('id') to act as a mathematical tie-breaker.
        $products = Product::query()
            ->orderBy('views', 'desc')
            ->orderBy('id', 'desc') // The strict tie-breaker
            ->cursorPaginate(20);

        return $products;
    }
}

Infrastructure Note: To ensure this query executes in single-digit milliseconds, you must create a composite index on your database matching the exact sort order: CREATE INDEX idx_products_views_id ON products (views DESC, id DESC);

The Engineering ROI and Infinite Scrolling

Adopting Cursor Pagination is non-negotiable for modern architectural design. By eliminating the OFFSET command, you completely decouple your API response time from the depth of the user's pagination. Your database CPU utilization drops drastically, completely insulating your infrastructure against scraping bots that attempt to crawl deep into your paginated endpoints.

Furthermore, this architecture flawlessly supports Infinite Scroll user interfaces on mobile devices. Because the cursor acts as an absolute physical anchor in the database, users will never see duplicate items or skip records when new data is inserted dynamically at the top of the feed, providing a bulletproof, enterprise-grade user experience.

DEV Community