Why OFFSET/LIMIT Pagination Breaks at Scale and How to Use High‑Performance Keyset Pagination

Background

Modern APIs that expose large data sets need pagination that scales. While OFFSET + LIMIT works for small tables, it becomes a performance bottleneck as the number of rows grows beyond what fits in memory.

Problem with OFFSET + LIMIT

When the offset is large the database must read every row up to the offset, causing a full‑table scan, heavy disk I/O, and unnecessary memory consumption. For example, the query

SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 500000;

on a table of 100 million rows forces the engine to scan the first 500 000 rows before returning just 20 rows.

A benchmark on a 100 k‑row test table shows the OFFSET/LIMIT version taking more than 30 × longer than an optimized approach.

Live comparison: https://www.db-fiddle.com/f/3JSpBxVgcqL3W2AzfRNCyq/1?ref=hackernoon.com

Proof‑of‑concept repository: https://github.com/IvoPereira/Efficient-Pagination-SQL-PoC?ref=hackernoon.com

Keyset (Cursor‑Based) Pagination

Instead of an offset, store the last seen primary‑key (or a unique timestamp) and request rows greater than that value. This lets the database use the primary‑key index directly, avoiding scans outside the desired range.

SELECT * FROM users
WHERE id > :last_id
ORDER BY id
LIMIT 20;

Benchmarking the same dataset shows execution time dropping from ~12.80 seconds to ~0.01 seconds.

Illustration of the original slow query versus the optimized keyset query:

Key requirements for keyset pagination:

A unique, monotonic column (e.g., auto‑increment integer ID, UUID, or timestamp) must exist.

If such a column is missing, adding an auto‑increment primary key solely for pagination is recommended.

Caveats:

Keyset pagination cannot jump directly to an arbitrary page number without knowing the corresponding key value.

It is ideal for “next”/“previous” navigation patterns rather than random page access.

Further Reading

In‑depth discussion of list pagination techniques: http://mysql.rjweb.org/doc.php/lists