Data pagination

Overview

Data pagination divides a query result into ranges and determines where a later request continues. A search service returning twenty-five results at a time needs a range size, an order, and a boundary for the next request.

Data access must determine both how many records to retrieve and where to continue. The second question first requires an order. A size limit applied to an unordered result does not define the same range across repeated requests.

The concept connects visible partitioning with server-side retrieval. Displaying 25 records in the browser does not reduce storage work if the server first reads every result and slices it. The API, database query, and screen need compatible definitions of one page.

The main distinctions concern how position is represented, how order is fixed, and what changes between requests mean. OFFSET and keyset pagination make different choices along these axes.

For page numbers and previous/next navigation, see UI pagination. The same navigation interface can use several retrieval strategies.

Reading origins and implementation records

The need to browse results in parts predates particular database releases. This chronology follows documented changes in products and standards. PostgreSQL's 1999 documentation already explains selecting row ranges with LIMIT and OFFSET; the row-comparison change of 2006 is a later implementation milestone.¹

Chronology

• June 9, 1999: PostgreSQL 6.5 added LIMIT/OFFSET. Its contemporary SELECT documentation describes limiting the returned rows and selecting their range.¹

• December 5, 2006: PostgreSQL 8.2 was released. Its row comparisons using <, <=, >, >= were changed to follow SQL-standard left-to-right semantics, and could be used as index constraints for multicolumn indexes matching the row value.²

• September 2007: Mark Nottingham’s RFC 5005, “Feed Paging and Archiving,” distinguished complete, paged, and archived web feeds.³

• 2008: According to the contemporary PostgreSQL 8.4 manual, SQL:2008 introduced OFFSET … FETCH syntax. This dates the standard syntax; LIMIT and pagination were already in use.⁴

• October 14, 2016: SQLite 3.15.0 introduced row values. The date concerns SQLite’s feature; row comparisons could already be used to find the next range in PostgreSQL 8.2 in 2006.⁵

OFFSET and row positions

OFFSET skips a number of rows in an ordered result before returning the next range. It maps page numbers to positions naturally: page three at twenty rows per page becomes “skip forty and read twenty.”

The current PostgreSQL manual stresses that the ordering must uniquely determine results for consistent range selection. The server still computes skipped rows, so deeper offsets can increase work.⁶

Keysets and row comparisons

Keyset pagination uses the last observed key instead of the number of preceding rows. SQLite’s scrolling-window example uses a row-value comparison to seek beyond the last ordering value instead of applying a large OFFSET.⁵

When several posts share a timestamp, the timestamp alone does not identify a position. Adding a unique ID gives a compound boundary, (created_at, id). With both columns descending, the next batch contains tuples smaller than the previous page’s last tuple. PostgreSQL compares row values from left to right; non-null timestamps and unique IDs give the example a definite order even within one timestamp.⁷

SQL positions and API cursors

The current GraphQL Cursor Connections specification defines contracts including edges, pageInfo, first, and after. Clients treat cursors as opaque position identifiers rather than interpreting their contents.⁸

A cursor may encode a compound key or identify a position stored on the server. A base64-looking string does not establish that the database uses keyset queries.

This separates the interface from retrieval strategy. A “load more” control takes a cursor into the next request; the server interprets it to navigate storage. Numbered buttons and infinite scrolling do not by themselves determine the underlying query.

Pages for navigation and archives for preservation

RFC 5005 gives bandwidth savings and sliding-window access as reasons to split feeds. A paged feed consists of temporary documents whose contents can change, while an archived feed uses permanent archive documents and links intended to preserve earlier entries and support reconstructing the complete collection.³

Navigating to another batch and obtaining a complete, fixed record are separate requirements. Today’s API cursors do not all follow the RFC, but its distinction between exploring a list and preserving its contents remains useful for pagination.

Changing lists and fixed results

Inserting posts ahead of an OFFSET can shift positions, causing repeats or omissions. Keyset pagination avoids that particular count-based shift, but does not freeze changes to sort keys or filter membership.

Requests reading different snapshots explore a changing list. Under PostgreSQL Read Committed, even statements within one transaction can acquire different snapshots.⁹

An export requiring every item exactly once needs an appropriate snapshot or an identity for a stored result set. The distinction between navigational pages and preserved archives reappears as a question about the query's time boundary.

Relationship loading and N+1

N+1 describes one query for a parent collection followed by separate relationship queries for its rows. Limiting parents to twenty can still add twenty queries. Selecting a range and retrieving relationships carry separate costs.

SQLAlchemy's relationship-loading documentation explains how lazy loading produces this pattern and how strategies such as select-IN loading batch related rows.¹⁰

Applying LIMIT directly to a one-to-many join can count expanded child rows instead of parents. Choosing the parent page before loading relationships makes the counted item explicit. Pagination ultimately defines the item's unit, its order, and its boundary across time.

Finding the next range with a compound key

This PostgreSQL example uses a unique ID to distinguish A and B at the same time. The first page's final value, (100, 1), becomes the next query's boundary.

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 next returns C and D. Matching sort directions and row comparisons connect the two ranges. The example demonstrates range selection over fixed data, without measuring concurrent updates or large-data query plans.