Why OFFSET/LIMIT Pagination Fails at Scale and How to Use Cursor‑Based Pagination

The article explains why using OFFSET and LIMIT for pagination becomes inefficient when the data volume exceeds what can be held in memory, forcing the database to perform costly full‑table scans for each page request.

It describes how a full scan reads every row, incurs massive disk I/O, and wastes time fetching rows that will never be displayed, especially when the offset is large (e.g., retrieving rows 50,000‑50,020 out of 100,000 rows).

Example code illustrating the range to fetch: 10万行中的第5万行到第5万零20行 Performance tests (linked to db‑fiddle) show the naïve OFFSET/LIMIT query taking more than 30 times longer than an optimized version.

To avoid these problems, the article proposes a cursor‑based (keyset) pagination approach: store the last retrieved primary‑key (or timestamp) and query the next page using a condition like WHERE id > :last_id ORDER BY id LIMIT :pageSize instead of OFFSET.

Images illustrate the traditional offset method versus the cursor method:

Comparative query results show the original OFFSET query taking 12.80 seconds while the keyset version finishes in 0.01 seconds.

The article notes that keyset pagination requires a unique, ordered column (usually an auto‑increment integer ID or timestamp) and may not work for tables without such a column, in which case OFFSET/LIMIT remains the fallback despite its drawbacks.

Finally, it advises using auto‑increment primary keys for pagination whenever possible and suggests further reading on large‑scale list queries.