Pagination: offset and cursor

In short: Pagination splits long result lists into pages. Two methods are common: offset (“skip n entries”) and cursor (“continue after this entry”).

In more detail: With LIMIT 20 OFFSET 40 the database fetches page 3. With large offsets it still has to walk through the skipped rows, which gets slow. If data changes between two pages, entries slip through twice or not at all.

In Depth

• Cursor pagination: WHERE id > :lastId ORDER BY id LIMIT 20. Uses an index and stays fast even with millions of rows.
• Stable ordering: A unique ordering is needed, for example (created_at, id).
• Cursor drawback: You cannot jump directly to page 57.
• API response: Often contains nextCursor or a Link header with the next page.
• Infinite scrolling: Fits cursor well; classic page numbers fit offset.

See also: Connection pooling