−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.
+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.[Footnote 1]
Chronology
June 9, 1999: PostgreSQL 6.5 added LIMIT/OFFSET. Its contemporary SELECT documentation describes limiting the returned rows and selecting their range.[Footnote 1]
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.[Footnote 2]
September 2007: Mark Nottingham’s RFC 5005, “Feed Paging and Archiving,” distinguished complete, paged, and archived web feeds.[Footnote 3]
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.[Footnote 4]
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.[Footnote 5]
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.[Footnote 6]
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.[Footnote 5]
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.[Footnote 7]
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.[Footnote 8]
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.[Footnote 3]
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.[Footnote 9]
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.[Footnote 10]
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.
