Stop Using Offset for Pagination: Switching to Cursor-Based Filtering for Massive Datasets

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.

Stop Using Offset for Pagination: Switching to Cursor-Based Filtering for Massive Datasets

Related reading

Defeating the OFFSET Penalty: Cursor Pagination in Laravel

Reading a Paginated API Without Holding the Whole Thing in Memory

API Pagination Patterns: Offset vs Cursor vs Keyset

Pagination: Always a "sort" (of) mistake [bugfix]

Why Skip/Take gets slower on every page (and how keyset pagination fixes it)

Killing N+1 Queries: From Per-Row Lookups to Flat, Batched Reads