SQL keyset pagination: continue an order without promising a snapshot

First principles

Pagination is an agreement about how to continue reading an ordered relation. Offset pagination describes how many rows to skip in the relation visible to the next query. If earlier rows are inserted or deleted between requests, the same offset can now point to a different boundary. Keyset pagination instead describes the last ordering value already returned and requests rows after it. This changes continuation from positional counting to a comparison against a specific boundary.

The ordering must be total for the rows being paginated. Sorting by creation time alone is insufficient because several rows can share one timestamp. Add a unique tie-breaker such as an immutable primary key. A descending order by creation time and ID means the next page contains rows with an earlier time, or the same time and a smaller ID. The predicate must match both the order direction and the tie-breaker. Returning only the timestamp in the cursor throws away information needed to continue correctly.

A cursor does not freeze the database. New rows can appear, existing rows can be deleted, and mutable sort values can move across the cursor boundary. If a row already returned changes its creation time to an earlier value, it can appear again later. For a live feed this may be acceptable; for a complete financial export it may not be. Decide whether the operation needs a live traversal, an application high-water boundary, or a genuine database snapshot before choosing implementation details.

Runnable example

Run this fixture in the local SQL workspace using SQLite, or in a disposable SQLite database. It deliberately gives two rows the same ordering time. The final query continues after the second row of the first page, preserving the tie-breaker.

CREATE TABLE page_events (
  id INTEGER PRIMARY KEY,
  created_at TEXT NOT NULL,
  message TEXT NOT NULL
);
CREATE INDEX page_events_order ON page_events(created_at DESC, id DESC);
INSERT INTO page_events VALUES
  (1, '2026-10-06T09:00:00Z', 'opened'),
  (2, '2026-10-06T09:01:00Z', 'checked'),
  (3, '2026-10-06T09:01:00Z', 'approved'),
  (4, '2026-10-06T09:02:00Z', 'published');

SELECT id, created_at, message
FROM page_events
ORDER BY created_at DESC, id DESC
LIMIT 2;

SELECT id, created_at, message
FROM page_events
WHERE created_at < '2026-10-06T09:01:00Z'
   OR (created_at = '2026-10-06T09:01:00Z' AND id < 3)
ORDER BY created_at DESC, id DESC
LIMIT 2;

The first page is IDs four and three; the second is two and one. The text timestamps sort chronologically here only because every value uses the same UTC representation and precision. Mixed offsets or mixed fractional formatting can violate that assumption. Use the database's appropriate temporal representation in production and inspect suspicious inputs with timestamp tools. Bind cursor values as parameters; the literals above are a controlled fixture, not a template for interpolating user input.

Contextual failure case

A support dashboard sorts by creation time descending and uses only that time as its cursor. Twenty records share the same second. After the first ten, the next query uses a strict earlier-than predicate and skips the remaining peers. Switching to earlier-than-or-equal returns the original ten repeatedly. Neither change repairs the missing total order. Carry both timestamp and unique ID, and build the lexicographic predicate that precisely matches the requested sort.

A less obvious problem appears when a nightly exporter uses keysets and assumes that means snapshot consistency. A late insert carries an old event time and lands below the current cursor, so it appears in a later page despite not existing at export start. An upper timestamp boundary alone cannot exclude that late insert. A separate ingestion sequence high-water mark can help only if its allocation and commit behavior match the promised membership semantics. Otherwise use a real snapshot or materialize the export's members.

Verification

Run both queries and check the exact ID sequences. Insert ID five with a newer timestamp after page one, then repeat page two: its continuation remains two and one. Insert a backdated row before repeating page two and observe how membership changes. These two tests separate stable continuation from frozen visibility. Inspect the query plan to see whether the index assists ordering and filtering, but do not assume every optimizer transforms an OR predicate equally well.

Exercise

Add a tenant column and paginate one tenant at a time. Include tenant scope and sort version in your cursor contract. Change the sort direction to ascending and rewrite both branches of the continuation predicate. Verify that all fixture rows are visited exactly once without concurrent mutations. Then state what extra mechanism would be needed to make the same completeness claim while inserts and sort-key updates continue.

Safety and caveats

An opaque cursor is not authorization. Validate its shape, enforce tenant permissions independently, and reject cursors issued for another filter or sort. Do not encode secrets or unnecessary personal data in cursors. Nullable ordering columns require an explicit null policy because comparisons involving null do not behave like ordinary values. Long-lived database snapshots can retain old versions and consume resources; a stronger consistency promise has an operational cost that should be measured rather than hidden inside the pagination implementation.