Database field guide · 08
Scalable Pagination
Stable pages without counting from zero
Design deterministic, index-backed pagination that remains fast and understandable across joins, partitions, and distributed SQL.
Franck Pachot7 chapterskeyset pagination · indexes · distributed queries
Build the mental model before choosing the mechanism.
This minibook was AI-generated from Franck Pachot's archived blog posts. Links to the original articles are included for source context and verification.
01
OFFSET counts discarded work
OFFSET does not teleport to row N. The executor still identifies and orders earlier rows before discarding them. Later pages therefore consume increasing work, and concurrent changes can shift rows between requests.
- Use OFFSET for small, bounded, low-change result sets.
- Measure deep pages, not only page one.
- A total count is a separate query and product decision.
02
A cursor describes a position
Keyset pagination carries the last ordered values into the next predicate. For ORDER BY created_at, id, the cursor means rows after a specific pair, not rows after an unstable ordinal position.
- Include every ordering expression in the cursor.
- Finish with a unique tie-breaker.
- Encode cursors opaquely but keep their semantics explicit in code.
03
Row-value predicates express lexicographic order
Where supported, a tuple comparison such as (created_at, id) < ($1, $2) mirrors the composite index order. Expanded OR predicates can express the same logic but are easier to get wrong around direction and nulls.
- Match comparison direction to ORDER BY direction.
- Define null placement or avoid nullable cursor columns.
- Use LIMIT page_size + 1 to signal another page.
04
The index is the pagination engine
An effective index begins with stable equality filters and continues with ordered cursor columns. It lets the executor seek to the boundary and stop after a small number of entries rather than sorting or scanning the full eligible set.
- Avoid functions that hide indexable ordering expressions.
- Cover projected columns when it meaningfully avoids lookups.
- Verify actual rows read, not merely rows returned.
05
Joins need bounded inner work
Pagination over one-to-many relationships can duplicate parent rows or fetch unbounded child data. First identify the bounded set of parent keys, then join or aggregate children with indexes and batching that preserve the page boundary.
- Define whether the page unit is a parent or joined row.
- Push LIMIT only through operations where semantics remain valid.
- Batch indexed child lookups in distributed execution.
06
Partitions need a global order
Each partition can return its local top rows, but a merge must choose the global next row. LIMIT pushdown reduces work only when partition ranges and ordering allow the executor to prove which candidates matter.
- Align time partitions with time-oriented cursor keys.
- Expect a merge step across active partitions.
- Retain the partition key in cursors when it narrows routing.
07
Field manual
Concrete mechanics, diagnostic evidence, and executable patterns to carry into a real system.
01
Seek from the last tuple
For descending order, the next page predicate must be lexicographically less than the final tuple of the current page. PostgreSQL row-value comparison implements this directly. The index uses equality on tenant_id, then seeks into created_at and id. LIMIT 51 returns fifty visible rows plus one look-ahead row without a separate count.
SELECT id, created_at, summary
FROM event
WHERE tenant_id = $1
AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT 51;
Design the matching index
The B-tree key must follow predicate and order semantics. An equality prefix can be followed by the ordered cursor tuple. INCLUDE carries small payload columns without changing order. PostgreSQL can scan a B-tree backward, but mixed directions must match the index definition or require a sort. Null cursor values need explicit policy because row comparison with null is unknown.
CREATE INDEX event_page_ix
ON event (tenant_id, created_at DESC, id DESC)
INCLUDE (summary);
Page parent entities before joining children
A one-to-many join changes the unit from orders to order items. Applying LIMIT after the join can return only part of the desired parent set, while applying it carelessly before filters changes semantics. Select the bounded parent keys in a materialized CTE, then join their children. This also bounds distributed inner lookups.
WITH page AS MATERIALIZED (
SELECT id, created_at FROM orders
WHERE customer_id = $1
AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC LIMIT 20
)
SELECT p.id, p.created_at, i.sku, i.quantity
FROM page p LEFT JOIN order_item i ON i.order_id = p.id
ORDER BY p.created_at DESC, p.id DESC, i.line_no;
Define consistency between page requests
Keyset pagination prevents duplicates caused by ordinal shifts before the cursor, but it is not a transaction snapshot across HTTP requests. Rows inserted behind the cursor may be omitted and updates to ordering columns can move rows. For a stable export, hold a database snapshot or materialize identifiers. For an activity feed, document live semantics and keep cursor columns immutable where possible.
-- PostgreSQL stable export pattern
BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;
SELECT pg_export_snapshot();
-- Worker transactions import the snapshot before their first query.
08
Source articles
Optional deep dives with the complete experiments and product-version context behind this guide.
