Page one looks right. You click Load more, and the last item appears again. Someone deletes a record, and now an item disappears between pages instead.
This can happen with a perfectly valid ORDER BY. The problem is that OFFSET 20 skips twenty rows in the result as it exists now, rather than continuing after the twenty rows you already saw.
For a changing feed, keyset pagination gives you a better boundary: continue after this exact pair of sort values. But it still reads changing data. If you need an export of everything that existed at a particular moment, a cursor alone cannot provide that guarantee.
Let's reproduce both the fix and its limits with six rows, SQLite and a small Node.js script.
A page number remembers a position
Suppose an activity feed sorts newest first. These are illustrative integer timestamps, with larger numbers meaning later events:
| ID | created_at |
|---|---|
| 6 | 500 |
| 5 | 400 |
| 4 | 400 |
| 3 | 300 |
| 2 | 200 |
| 1 | 100 |
The query is explicit about the order:
SELECT id, created_at FROM events
ORDER BY created_at DESC, id DESC
LIMIT 2 OFFSET 0;
It returns IDs 6, 5. The ID breaks the tie between the two events at time 400. Without a unique order, you have an additional problem: the database has no obligation to keep tied rows in the order you happened to observe. PostgreSQL's LIMIT and OFFSET documentation calls out that requirement.
Now insert ID 7 at time 600. The ordered IDs become 7, 6, 5, 4, 3, 2, 1.
Your next request uses OFFSET 2. It skips 7 and 6, then returns 5, 4. ID 5 repeats because the insertion shifted its position. The query did exactly what you asked.
Deletion creates the opposite failure. Starting from the original six rows, delete ID 6 after fetching page one. The list becomes 5, 4, 3, 2, 1. Skipping two now returns 3, 2, so you never see ID 4.
These are separate reads with a change between them, not a claim that one SQL statement sees half an insertion.
A cursor remembers a boundary
After page one, keep its last row's sort values: (created_at=400, id=5).
For the next page, ask for rows below that pair:
SELECT id, created_at FROM events
WHERE (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 2;
Bind 400 and 5 to the placeholders. The query returns 4, 3, whether ID 7 was inserted above the boundary or ID 6 was deleted there.
This is keyset pagination, often exposed by an API as a cursor. SQLite supports row-value comparisons, which compare components from left to right. With both columns sorted descending, < asks for the next, lower pair. An all-ascending version uses >; mixed sort directions need a predicate that follows each column's direction.
ID 4 belongs on the next page because its timestamp equals 400 and its ID is below 5. A timestamp-only condition, created_at < 400, skips it. Using <= 400 instead also admits ID 5 again. The cursor must carry the tie-breaker too.
The boundary values are sufficient even if the row they came from is later deleted. You do not need to look that row up again to reconstruct its timestamp.
Run the failure and the fix
Save this as pagination-demo.mjs. It creates an in-memory database, so it never opens or changes an application database. It needs Node.js with the built-in node:sqlite module; Node.js 24 and 26 provide it without installing a package. The API's stability designation depends on the release; see the documentation for the version used here.
The synchronous database API keeps this disposable example short. It is not a recommendation to block a busy web server's event loop.
import assert from 'node:assert/strict';
import { DatabaseSync } from 'node:sqlite';
const db = new DatabaseSync(':memory:');
const size = 2;
const ids = (rows) => rows.map((row) => row.id);
db.exec(`
CREATE TABLE events (
id INTEGER PRIMARY KEY,
created_at INTEGER NOT NULL
);
CREATE INDEX events_feed ON events(created_at DESC, id DESC);
`);
const insert = db.prepare('INSERT INTO events VALUES (?, ?)');
function reset() {
db.exec('DELETE FROM events');
for (const [id, time] of [[1,100], [2,200], [3,300],
[4,400], [5,400], [6,500]]) {
insert.run(id, time);
}
}
function offsetPage(offset) {
return db.prepare(`
SELECT id, created_at FROM events
ORDER BY created_at DESC, id DESC LIMIT ? OFFSET ?
`).all(size, offset);
}
function after(cursor) {
return db.prepare(`
SELECT id, created_at FROM events
WHERE (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC LIMIT ?
`).all(cursor.created_at, cursor.id, size);
}
function check(label, rows, expected) {
assert.deepEqual(ids(rows), expected);
console.log(`${label}: ${ids(rows).join(', ')}`);
}
try {
reset();
const first = offsetPage(0);
const cursor = first.at(-1);
check('Page 1', first, [6, 5]);
insert.run(7, 600);
check('Insert / OFFSET', offsetPage(2), [5, 4]);
check('Insert / cursor', after(cursor), [4, 3]);
reset();
db.exec('DELETE FROM events WHERE id = 6');
check('Delete / OFFSET', offsetPage(2), [3, 2]);
check('Delete / cursor', after(cursor), [4, 3]);
reset();
const timestampOnly = db.prepare(`
SELECT id, created_at FROM events WHERE created_at < ?
ORDER BY created_at DESC, id DESC LIMIT ?
`).all(cursor.created_at, size);
check('Timestamp-only cursor', timestampOnly, [3, 2]);
check('Composite cursor', after(cursor), [4, 3]);
reset();
db.exec('UPDATE events SET created_at = 550 WHERE id = 4');
check('Moved unseen row / cursor', after(cursor), [3, 2]);
reset();
insert.run(7, 350);
check('Backdated insert / cursor', after(cursor), [4, 7]);
console.log('All assertions passed.');
} finally {
db.close();
}
Run:
node pagination-demo.mjs
The output is:
Page 1: 6, 5
Insert / OFFSET: 5, 4
Insert / cursor: 4, 3
Delete / OFFSET: 3, 2
Delete / cursor: 4, 3
Timestamp-only cursor: 3, 2
Composite cursor: 4, 3
Moved unseen row / cursor: 3, 2
Backdated insert / cursor: 4, 7
All assertions passed.
This example was executed on Windows with Node.js 26.6.0 and SQLite 3.53.3 on 5 November 2026. The assertions verify returned rows under controlled changes; this is not a throughput benchmark or a test of a deployed API.
Notice the last two cases. They explain why switching to cursors should also change how you describe the endpoint's behaviour.
A cursor does not freeze the feed
After page one, the cursor still means “below (400, 5)”. It does not mean “the remaining rows from my first request”.
If unseen ID 4 changes its timestamp from 400 to 550, it moves above the boundary. The next page returns 3, 2. The cursor cannot tell that you have never seen ID 4. Similarly, an already displayed row can reappear if its sort key moves below the boundary.
A newly inserted row can also appear halfway through the traversal. The demo inserts ID 7 at time 350. It is below the cursor, so the next page returns 4, 7, even though ID 7 did not exist when you fetched page one.
That behaviour can be appropriate for a live feed. It is a poor surprise in a report labelled “all records as of 09:00”.
Prefer immutable ordering values when you want a predictable forward traversal. A creation sequence or a creation timestamp plus a unique ID may fit; a mutable popularity score or updated_at usually needs a more deliberate product contract. Even immutable sort keys do not prevent deletions or changes to which rows match the filters.
For a consistent export, use a database-supported consistent read or materialise the required results in a background job. SQLite's isolation documentation explains how a read transaction in WAL mode retains its snapshot while other connections commit changes. Separate HTTP requests do not automatically share that transaction.
Holding a database transaction open while a person browses several pages has operational costs. An export job can own a bounded consistent read and produce a file; an interactive feed can promise forward continuation over current data. Choose the guarantee you actually need.
Also distinguish freezing membership from freezing values. Saving a list of matching IDs preserves which records were selected, but looking those IDs up later can still return edited values or find deleted rows. A historical report needs the corresponding historical values too.
Make the query, index and API agree
For the demo's query, the index follows the ordering columns:
CREATE INDEX events_feed ON events(created_at DESC, id DESC);
An appropriate index lets the database seek towards the boundary instead of discarding a growing prefix. That does not make every cursor query fast: extra filters, joins and the chosen plan still matter. SQLite's query-planning guide explains how indexes can support searching and ordering together. Inspect your actual query plan and representative data before making a performance claim.
For a tenant-scoped feed, apply the tenant restriction on every page. An index starting with the tenant equality column, followed by the sort columns, is a candidate to evaluate. A cursor supplies a position; it never grants permission to read a tenant's records.
When turning the SQL into an HTTP endpoint, keep these details explicit:
- Keep the full ordering tuple. Preserve timestamp precision and exact IDs through the database driver, cursor encoder and decoder. For large integer IDs in JavaScript, Jitsumi's guide to preserving IDs across JSON boundaries covers the numeric precision trap. The demo's small integers avoid that separate issue.
- Keep the same query context. Validate the cursor's shape, version, filter context and sort order. Reject a cursor for a different search instead of silently interpreting it under new rules. A server-issued opaque token can represent that context; base64 encoding alone does not make a token trustworthy. Reapply current authorisation regardless.
- Bind values and bound the page size. Use parameters for cursor values, as the example does. Validate an integer page size against a server limit. Select sorting from an allowlist rather than inserting arbitrary client input into SQL.
-
Define null handling. This example makes
created_atnon-null. Nullable sort columns need an explicit ordering and continuation rule; a tuple comparison involving null does not behave like an ordinary numeric comparison.
To report whether another page is available, request page_size + 1 rows. Return only the first page_size, and construct the next cursor from the last returned row, not the extra look-ahead row. Otherwise you skip the look-ahead item. The extra row tells you that more data existed when that query ran; it cannot promise that it will still exist on the next request.
Keep OFFSET when its trade-off fits
Offset pagination remains useful for small lists and interfaces that need direct page-number navigation. A cursor cannot jump to page 50 without a known boundary or another lookup strategy.
| Reader's task | Approach to consider |
|---|---|
| Browse a small table with numbered pages | OFFSET with a unique order; accept movement between reads |
| Keep loading older items in a changing feed | Keyset cursor with stable sort keys and matching filters |
| Produce a report of one consistent dataset | Consistent read or materialised export; paginate that fixed result if needed |
Before shipping, make the test uncomfortable: give two rows the same timestamp, insert above the boundary, delete a previously displayed row, and change an ordering value. Check exact IDs across successive pages.
The useful promise for a cursor endpoint is precise: continue below this boundary under these filters. If the product promises a frozen dataset, build that guarantee separately.
References
Documentation checked on 5 November 2026.
Top comments (0)