Why OFFSET and LIMIT Are Inefficient for Pagination and How Cursor‑Based Pagination Improves Performance

Modern applications that need to browse massive datasets must provide reliable and efficient API endpoints, and traditional pagination using OFFSET and LIMIT quickly becomes a bottleneck as data volume grows.

OFFSET/LIMIT forces the database to perform a full‑table scan and load all preceding rows into memory before returning the requested page, which leads to excessive I/O and long query times, especially when the offset is large (e.g., fetching the 5 000 000‑th row out of 10 000 000).

Benchmarks show that a naïve OFFSET/LIMIT query on 100 000 rows can take over 12 seconds, while an optimized version using a cursor‑based approach finishes in a few milliseconds.

Cursor‑based pagination works by remembering the last seen primary‑key (or another unique, indexed column) and using it in the next query, allowing the database to start scanning directly from the correct position without scanning irrelevant rows.

Implementation typically looks like: SELECT * FROM table WHERE id > :last_id ORDER BY id ASC LIMIT 20;

While this method requires a unique, ordered column (such as an auto‑increment ID or timestamp), it dramatically reduces query latency and scales well for large tables; if a table lacks such a column, adding an auto‑increment primary key is recommended.

In summary, avoid OFFSET/LIMIT for high‑traffic or large‑scale pagination and adopt cursor‑based techniques to achieve consistent, low‑latency data retrieval.