The Illusion of Standard Pagination
When you build a data-heavy application—like an e-commerce catalog, a social media feed, or a logging dashboard—pagination is one of the first features you implement. In Laravel, this is incredibly simple. You write User::paginate(15); and Laravel automatically handles the database querying, counts the total number of records, and generates the HTML links for "Page 1, Page 2, Page 3."
For applications with a few thousand records, this standard offset-based pagination works perfectly. However, standard pagination harbors a catastrophic flaw that only reveals itself when your application achieves enterprise scale. When your database table hits millions of rows, standard pagination will trigger what database engineers call the OFFSET Death Spiral, eventually taking your entire database server offline.
At Smart Tech Devs, we architect platforms designed to handle massive datasets seamlessly. To prevent our databases from collapsing under the weight of deep pagination queries, we abandon standard offset pagination and implement Keyset Pagination, commonly referred to as Cursor Pagination.
The Anatomy of the OFFSET Death Spiral
To understand why standard pagination fails, we must look at the actual SQL query executed by Laravel under the hood. When a user requests Page 1 of your logs, Laravel executes something like this:
SELECT * FROM api_logs ORDER BY id DESC LIMIT 15 OFFSET 0;
This query is blazing fast. The database looks at the B-Tree index, grabs the first 15 rows, and returns them instantly. But what happens when a user navigates to Page 10,000?
SELECT * FROM api_logs ORDER BY id DESC LIMIT 15 OFFSET 150000;
This is where the architecture breaks down. Relational databases like PostgreSQL and MySQL cannot magically skip ahead to the 150,000th row. Because rows can be of variable length, and because of how B-Tree indexes are structured, the database engine must physically read, count, and discard the first 150,000 rows in memory before it can return the 15 rows you actually requested.
If you have a billion-row table and a bot scrapes Page 50,000 of your API, your database will max out its CPU reading and discarding millions of records just to return 15 rows. This locks up resources, blocks other queries, and eventually crashes the server.
The Solution: Keyset (Cursor) Pagination
Cursor pagination fundamentally changes the SQL execution plan. Instead of telling the database "Skip 150,000 rows," we tell the database exactly where we left off based on a unique identifier (a cursor). Usually, this identifier is an auto-incrementing ID or a highly precise timestamp.
If the last record on Page 1 had an ID of 985000, to get Page 2, we execute this SQL:
SELECT * FROM api_logs WHERE id < 985000 ORDER BY id DESC LIMIT 15;
Because the id column is indexed, the database instantly jumps directly to the record 984999 and reads the next 15 rows. It does not matter if you are on Page 2 or Page 2,000,000; the query execution time remains exactly the same. The algorithmic time complexity drops from O(N) to O(1).
Phase 1: Implementing Cursor Pagination in Laravel
Laravel provides first-party, out-of-the-box support for cursor pagination. Switching an endpoint from offset pagination to cursor pagination often requires changing just a single word in your controller.
namespace App\Http\Controllers;
use App\Models\ApiLog;
use Illuminate\Http\Request;
class LogController extends Controller
{
public function index(Request $request)
{
// ❌ The Standard Offset Pagination (Dangerous at scale)
// $logs = ApiLog::orderBy('id', 'desc')->paginate(15);
// ✅ The Cursor Pagination (O(1) performance at infinite scale)
$logs = ApiLog::orderBy('id', 'desc')->cursorPaginate(15);
return response()->json($logs);
}
}
Phase 2: Handling API Responses and Cursors
When you return a cursorPaginate() collection from a Laravel API, the JSON payload looks fundamentally different from standard pagination. You will notice that there are no "total pages" or "current page" numbers. This is because the database never ran the expensive SELECT COUNT(*) query required to calculate the total pages.
{
"data": [
{ "id": 985000, "message": "Log entry 1..." },
{ "id": 984999, "message": "Log entry 2..." }
],
"path": "https://api.smarttechdevs.in/logs",
"per_page": 15,
"next_page_url": "https://api.smarttechdevs.in/logs?cursor=eyJpZCI6OTg0OTg2LCJfcG9pbnRzVG9OZXh0SXRlbXMiOnRydWV9",
"prev_page_url": null
}
The cursor parameter in the URL is an encoded string. When the frontend wants the next page of results (for example, in an Infinite Scroll UI), it simply makes a GET request to the next_page_url. The frontend does not need to understand or decode the cursor; Laravel handles decoding it and injecting the WHERE id < ? clause automatically.
Architectural Limitations and Trade-offs
While cursor pagination solves the performance crisis, it introduces strict architectural limitations that you must plan for:
- No Page Numbers: You cannot render a traditional pagination UI with "Jump to Page 50". You can only provide "Next" and "Previous" buttons, or an Infinite Scroll implementation.
-
Strict Sorting Rules: You can only paginate over columns that are strictly sequential and unique. If you sort by a
statuscolumn (where 10,000 records have the status 'pending'), the cursor cannot determine where one page ends and the next begins. To solve this, you must always append a unique column to yourorderByclauses (e.g.,orderBy('status')->orderBy('id')).
The Engineering ROI
By migrating your high-volume tables from standard offset pagination to cursor pagination, you permanently future-proof your application against database timeouts. You eliminate the devastating OFFSET performance penalty and avoid the costly COUNT(*) aggregation queries that plague traditional pagination. This pattern is the absolute cornerstone of API development for platforms dealing with millions of records, guaranteeing that your endpoints respond in under 20 milliseconds regardless of how deeply a user scrolls into your data archives.
