When building APIs, data tables, or infinite scroll feeds, standard pagination is usually handled via SQL offsets:
SELECT * FROM activity_logs ORDER BY id DESC LIMIT 20 OFFSET 500000;
While this works beautifully on small datasets, it contains a massive, hidden performance trap. As your database grows to millions of rows, pages 1, 2, and 3 will load in milliseconds, but page 25,000 will take seconds to load, spiking your database CPU to 100%.
The Trap: Why OFFSET Destroys Database Performance
Relational database engines cannot jump directly to a specific row offset. To give you rows 500,001 to 500,020, the database engine must actually read through the first 500,000 rows from disk, sort them in memory, discard them, and then return only the 20 rows you requested.
As the offset number gets larger, the query gets slower. This is called a Linear Scan Limitation ($O(N)$ complexity).
The Solution: Cursor-Based Pagination ($O(1)$ Complexity)Instead of telling the database how many rows to skip, you tell the database exactly where to start looking based on a unique identifier (a cursor) from the previous page's payload.
The Old Offset Way:
-- Database scans 500,000 rows just to throw them away
SELECT * FROM activity_logs ORDER BY id DESC LIMIT 20 OFFSET 500000;
-- Database uses primary index to jump instantly to the exact row ID
SELECT * FROM activity_logs WHERE id < 145023 ORDER BY id DESC LIMIT 20;
Because your id field is indexed, the database utilizes a direct pointer lookup to locate the target row instantly, completely ignoring the preceding millions of rows. It doesn't matter if you are loading page 2 or page 50,000—the query execution time remains constant ($O(1)$).
Implementation in Application Code
When returning a collection payload to your frontend, you simply include the last item's ID as the next_cursor pointer:
{
"data": [
{ "id": 145025, "event": "user_login" },
{ "id": 145024, "event": "file_upload" },
{ "id": 145023, "event": "user_logout" }
],
"meta": {
"next_cursor": 145023,
"has_more": true
}
}
The frontend reads next_cursor and appends it to the query parameters (?cursor=145023) for the next page request.
Conclusion
If you are architecting large enterprise systems, application performance comes down to minimizing disk I/O operations. Swapping out standard numerical offset pagination for deterministic, index-driven cursors is one of the easiest ways to scale your read-heavy data pipelines smoothly.
SOCIAL SHARE CARD GENERATOR