Pagination with Relative Cursors

Summary

Incremental page-number pagination becomes increasingly expensive for large offsets, because the database scans and discards earlier records before returning a page. Shopify describes relative cursor pagination, which remembers the last record seen and continues from that point, using an indexed id to keep query time nearly constant as pages increase. In tests on a shop with 14 million products, a 100,000 offset took 2,221.60 ms, compared with 5.24 ms using the last id, while offsets of 1,000,000 and above consistently timed out. Sorting by another field requires retaining both its last value and a unique secondary key such as id, with a matching compound index to avoid skipped records and maintain performance. Shopify introduced Link-header URLs for next and previous pages and began migrating REST endpoints from page numbers, reporting early /admin/products.json usage as about 11 times faster, though adoption remained low and full results were pending.

Context

Large page-number offsets made catalogue synchronization increasingly slow, sometimes timing out and consuming database connections needed by unrelated queries. This was especially problematic for large Shopify shops and shared database instances when applications loaded all records for products, collects, orders, or other models.

Approach / What changed

Replace page numbers and offsets with relative cursors that retain the last record's id, or the last sort value plus a unique secondary id when sorting by a non-unique field. Shopify also generated next and previous page URLs in the Link header, supplied shared implementation patterns and migration guidance, and coordinated endpoint conversion across teams.

Takeaways

• Using the last id reduced the 100,000-offset test from 2,221.60 ms to 5.24 ms, a 99.76% improvement and more than 400 times faster than the offset query.
• When sorting by a non-unique field such as title, clients must retain both the last field value and a unique secondary key such as id; otherwise records can be skipped.
• Relative cursors prevent jumping directly to a specific page. Shopify’s early /admin/products.json usage was about 11 times faster on average, but third-party adoption was still low.

Related reading