Data pagination

Overview

Pagination is a technique for dividing a long list into manageable ranges for browsing and querying. Rather than returning all search results at once, it shows 25 items at a time or fetches the next batch from a feed. Page numbers on screen and infinite scroll are different interfaces through which users navigate these ranges.

Data retrieval requires deciding both "how many items to fetch" and "where to continue from." Answering the second question requires that the list have a defined order. If you apply only a count limit to data with no established sort order, there is no reliable guarantee that repeating the same request refers to the same range.

Pagination involves both how the UI splits content and what retrieval strategy the server uses. Even if the browser displays only 25 items, if the server reads the entire result set and then trims it, the UI's division and the storage read cost move independently. The API, the database query, and the UI must all agree on what constitutes one page.

Pagination strategies differ in how they express position, what anchors the sort order, and what the results mean when data changes between requests. OFFSET and keyset make different choices on all three counts.

History

• December 5, 2006: PostgreSQL 8.2 was released. In this version, row comparisons using <, <=, >, and >= were updated to compare columns left to right in accordance with the SQL standard, and multi-column indexes whose column composition matches a row value became usable as index conditions.

• September 2007: Mark Nottingham, in RFC 5005 "Feed Paging and Archiving," distinguished web feeds into complete feeds, paged feeds, and archived feeds.

• 2008: The PostgreSQL 8.4 documentation of that era notes that SQL:2008 introduced the OFFSET … FETCH syntax. This marks when the standard syntax was formalized; LIMIT and list partitioning did not originate at this point.

• October 14, 2016: SQLite 3.15.0 was released, introducing row value support. This date marks when the feature landed in SQLite; using row comparisons to find the next range was already possible in PostgreSQL 8.2 back in 2006.

OFFSET and Row Position

OFFSET skips a fixed number of rows from the front of a sorted result and returns the next range. It maps easily from a page number to a row position: page 3 of 20 items per page becomes "skip 40 rows, read 20."

As the current PostgreSQL documentation emphasizes, the sort order must uniquely determine the result for multiple ranges to be read consistently. Because the server must also count the skipped rows, the cost can grow as you go deeper into the result set.

Keyset and Row Comparison

Keyset uses the last seen key as the boundary instead of a count of rows seen so far. The scrolling window example in the SQLite documentation also describes finding rows after the last sort value via row value comparison rather than a large OFFSET.

When multiple posts share the same timestamp, the timestamp alone does not determine position, so a unique ID is sorted alongside it and (created_at, id) serves as the boundary for the next range. If both columns are in descending order, the next batch is found in the range that is less than the last tuple of the previous batch. PostgreSQL's row comparison judges differences starting from the leftmost column; since the timestamp and ID in the example contain no NULLs and the ID is unique, order is fully determined even among posts sharing the same timestamp.

SQL Position and API Cursor

The current GraphQL Cursor Connections specification defines a contract around edges, pageInfo, first, after, and similar fields, and clients treat the cursor as an opaque position identifier without interpreting its internal representation. This post cites that specification when describing the cursor interface.

A cursor may carry a composite key, or it may carry an identifier for a query position stored on the server. Because of this, you cannot conclude from the fact that a string looks like base64 that the server is executing a keyset query.

Keeping these two concerns separate allows the interface and the retrieval strategy to be compared independently. A "Load More" button on screen takes a cursor and constructs the next request, while the server navigates the data store according to what that cursor means. Whether the UI shows numbered buttons or uses infinite scroll, the actual query is determined independently of that presentation.

Navigation Batches and Archival Documents

RFC 5005 cites bandwidth savings and sliding window access as reasons for splitting feeds. A paged feed is a temporary batch with no guarantee that its contents remain unchanged, while an archived feed deals with permanent archival documents and links that preserve past entries and allow the full set of entries to be reconstructed.

This effectively separates "there is a next batch to navigate to" from "obtain a fixed, complete record," treating them as distinct requirements. Not every cursor in today's web APIs strictly follows this distinction, which originated in feed formats, but the question of separating navigation from preservation remains relevant in modern pagination.

Changing Lists and Stable Results

When new posts are prepended to the front, an OFFSET-based position shifts, causing the same post to be read again or skipped entirely. Keyset reduces this drift, but changes where sort values are modified or items move in and out of a filter still remain.

If each request reads a different snapshot, the user is continually navigating a changing list. Under PostgreSQL's Read Committed isolation level, each statement can obtain a fresh snapshot even within the same transaction.

An export that must include every item exactly once requires an appropriate snapshot or an identifier for a stored result set. The question that RFC 5005 raised by separating navigation batches from archival documents resurfaces as a time boundary concern in database queries.

Relationship Loading and N+1

N+1 is a pattern in which a parent list is read once and then the related data for each row is fetched separately. Even with 20 parents, up to 20 additional relationship queries may be issued, so paginating a range and loading relationships carry separate costs.

The SQLAlchemy relationship loading documentation explains how this problem manifests with lazy loading and describes batch loading strategies such as select-IN. This post uses it as a reference for understanding how modern ORM relationship queries behave.

Applying LIMIT directly to a one-to-many join may count the expanded child rows rather than the number of parents. Determining the parent page first and then loading relationships makes the unit of items unambiguous. Pagination is a design decision that simultaneously defines the unit of items, their sort order, and the time boundary of the query.

Finding the Next Range with a Composite Key

The PostgreSQL example below distinguishes posts A and B, which share the same timestamp, by their unique IDs. It uses the last value of the first batch, (100, 1), as the boundary for the next query.

CREATE TEMP TABLE posts (
  id integer PRIMARY KEY,
  created_at integer NOT NULL,
  title text NOT NULL
);
INSERT INTO posts VALUES
  (1, 100, 'A'), (2, 100, 'B'),
  (3, 90, 'C'), (4, 80, 'D');

SELECT id, title FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 2;

SELECT id, title FROM posts
WHERE (created_at, id) < (100, 1)
ORDER BY created_at DESC, id DESC
LIMIT 2;

The first query returns B and A; the second returns C and D. The sort direction and row comparison are consistent across both queries, so the next range follows on seamlessly. What this example demonstrates is the process of selecting which range to retrieve from a fixed dataset. Concurrent modifications and query plans for large data sets are outside the scope of this example.