Pagination

Pagination is the technique of splitting a large dataset into discrete pages. Without it, list endpoints become slower as data grows, eventually timing out or exhausting memory. Pagination keeps responses small, improves perceived performance, and reduces load on databases and clients.

There is no single best pagination strategy. Offset pagination is simple but performs poorly on deep pages. Cursor pagination is fast and stable but hard to jump to arbitrary pages. Keyset and search-after pagination offer additional flexibility for sorted datasets.

This guide explains each approach, when to use it, and how to implement it in SQL and application code. Choosing the right strategy early prevents painful migrations later.

Offset pagination uses LIMIT and OFFSET to retrieve a slice of results. Clients request a page number and a page size, and the server translates these into an offset. It is the easiest strategy to implement and understand.

The main problem with offset pagination is performance. To return rows one thousand through one thousand and twenty, the database must scan and discard the first one thousand rows. As offset grows, query time grows linearly. Offset pagination also suffers from drift: if a row is inserted or deleted while a client pages, rows may be skipped or duplicated.

Cursor pagination returns a pointer to a specific row, usually encoded as an opaque string. The client sends the cursor from the previous page to request the next page. Because the database can use an index to find the starting row, performance remains constant regardless of depth.

Cursors are stable against insertions and deletions as long as the sort key is unique. If the sort key can collide, append a unique tiebreaker such as the primary key. The cursor typically contains the last seen sort value and tiebreaker, encoded with base64.

Keyset pagination is the SQL implementation of cursor pagination. Instead of an offset, the query filters on the sort columns. For example, to get the next page after the row with sort value forty-two, you filter for rows where the sort value is greater than forty-two.

Keyset performs well because the database can use an index on the sort column. It also handles concurrent modifications gracefully. The tradeoff is that jumping to an arbitrary page is not possible; navigation is strictly sequential.

Search-after pagination is a generalization of keyset pagination used by search engines such as Elasticsearch and OpenSearch. The client provides the sort values of the last result, and the search returns the next set. It works with multiple sort fields and relevance scores.

Search-after is preferred over deep offset scrolling in search because scoring and sorting can be expensive. It is also the only practical way to paginate sorted results in distributed search indices.

A consistent pagination response shape makes client code simpler. Include the data, cursors or page metadata, and a flag indicating whether more results are available. Clients should not need to infer state from the data array length.

Pagination seems simple but hides several traps. Unbounded endpoints crash under load. Deep offset queries slow down unexpectedly. Cursors built on non-unique columns skip rows when values collide. And list endpoints without authorization filtering expose data that belongs to other users.

Always enforce a maximum page size, even with cursors. A client requesting ten thousand items per page can still overwhelm your database. Validate and clamp limit parameters before executing the query. Apply the same authorization filters used for single-resource lookups to list queries so users only see rows they are allowed to see.