Why Cursor-Based Pagination Beats Offset: Performance Insights and Code

When an API must return thousands of records, sending the entire result set is inefficient, so pagination is used.

Typical pagination relies on an offset or page number, e.g.:

GET /api/products?page=10
{"items": [...100 products]}
GET /api/products?page=11
{"items": [...another 100 products]}

Using offset (e.g., ?offset=1000) translates to OFFSET 1000 LIMIT 100 in SQL, which forces the database to skip the first 1000 rows. This is sub‑optimal because every database must read and discard those rows, incurring extra sorting and counting overhead.

A better approach is cursor‑based pagination. Databases support a cursor—a pointer to a row—allowing a query like “return the next 100 rows after this cursor.” An API can return a cursor string along with the data:

GET /api/products
{"items": [...100 products], "cursor": "qWe"}
GET /api/products?cursor=qWe
{"items": [...100 products], "cursor": "qWr"}

Implementation often encodes a sortable field such as id. When a request includes a cursor, the server decodes it and runs a query like WHERE id > :cursor LIMIT 100, which avoids scanning the skipped rows.

Performance comparison (PostgreSQL) shows the difference:

# explain analyze select id from product offset 10000 limit 100;
Limit  (cost=1114.26..1125.40 rows=100 width=4) (actual time=39.431..39.561 rows=100 loops=1)
  ->  Seq Scan on product  (cost=0.00..1274406.22 rows=11437243 width=4) (actual time=0.015..39.123 rows=10100 loops=1)
Execution Time: 39.589 ms

# explain analyze select id from product where id > 10000 limit 100;
Limit  (cost=0.00..11.40 rows=100 width=4) (actual time=0.016..0.067 rows=100 loops=1)
  ->  Seq Scan on product  (cost=0.00..1302999.32 rows=11429082 width=4) (actual time=0.015..0.052 rows=100 loops=1)
  Filter: (id > 10000)
Execution Time: 0.094 ms

The cursor method can be orders of magnitude faster, especially on large tables and deep pagination.

Beyond performance, cursor pagination avoids issues with concurrent modifications: with offset, deletions or insertions on earlier pages can cause items to be skipped or duplicated; a cursor reliably continues from the last seen position.

However, stateless APIs using cursors cannot easily support “previous page” navigation, so UI designs that need explicit page numbers may still rely on offset.

When using LIMIT, it is important to use an ORDER BY clause that defines a unique order; otherwise the subset of rows is unpredictable.

In practice, choose the pagination strategy that matches your data size, ordering requirements, and UI needs; cursor‑based pagination is generally preferred for large, deep, and mutable datasets.