Problem
Every paginated list I have shipped worked perfectly until somebody walked
deep into it. Page 2 is instant. Page 2,000 is not, and nobody notices until
an admin, a data-export job, or a very determined user gets there. The query
looks innocent — LIMIT 20 OFFSET 39980 — and on a small table it is. But
OFFSET is a promise the database keeps by walking: it reads all 39,980
rows you asked it to skip, throws them away, and hands you the next 20. The
cost grows linearly with depth, so the slowest page is always the one your
heaviest users (exports, auditors, infinite scroll archaeologists) ask for
most. I built this demo because "OFFSET is slow at depth" is the kind of
thing everyone nods at and nobody has felt in their own hands. Here you can
feel it: same page, two strategies, stopwatch on.
Context
The demo holds 50,000 ordered rows in a single SQLite file (6.6 MB on disk,
reseeded from scratch in 0.24 seconds over localhost HTTP when you hit
reset). You pick a page and a page size, and the server runs the same page
twice: once as ORDER BY id LIMIT ? OFFSET ?, once as WHERE id > ?
ORDER BY id LIMIT ?, each timed around query execution plus full row scan.
It then compares the two result sets element by element and reports whether
they are identical — they always are — alongside both timings and the speedup
factor.
Running this against local SQLite on the demo box is deliberate. There is no network hop, no pool, no cache tier, so the numbers isolate exactly one variable: the algorithm. On my run, page 1 (size 20) took 0.135 ms via offset versus 0.061 ms via keyset — a 2.2x gap nobody would notice. Page 100 measured 0.298 ms against 0.080 ms (3.7x). Page 1000: 2.055 ms against 0.093 ms (22.1x). Page 2000: 6.112 ms against 0.206 ms (29.7x). The last full page, 2500: 6.208 ms against 0.159 ms, 39.0x. A wider page tells the same story: page 500 at size 100 measured 5.120 ms versus 0.239 ms (21.4x). The keyset line stays flat while the offset line climbs with depth — that shape, not any single number, is the whole point. Rerun it yourself with one command; the demo exists so you do not have to take my box's word for it:
go run . &
curl -s -X POST localhost:8080/api/benchmark \
-H 'Content-Type: application/json' \
-d '{"page":2000,"page_size":20}' \
| jq '{page, offset_ms, keyset_ms, speedup, identical}'Alternatives Considered
OFFSET with a covering index. If the index contains every column the
query needs, the database skips rows without touching the table heap. This
genuinely helps — narrower rows to walk — but it does not change the
complexity: the engine still walks and discards OFFSET entries one by one.
On my dataset the payload column is wide on purpose (~110 bytes per row), so
an index-only scan would narrow the gap, not close it. I rejected it as the
answer because it optimizes the constant while the linear term keeps growing.
Deferred join (offset the ids, then fetch the page). Select just the ids
with OFFSET, then JOIN back for the full rows: the skip phase walks a
slim index instead of fat rows. This is a real improvement when rows are wide
and I would reach for it as a stopgap. But it is still OFFSET underneath —
the walk is cheaper per step, and there are just as many steps. It also
splits one clear query into two phases the next reader has to understand.
Don't allow deep pages (product-level cap). Limit the UI to the first N pages and offer search, filters, or export instead. Honestly, this is often the right product call — nobody thoughtfully browses page 2,000 — and for human-facing lists I prefer it. But it answers a different question. Exports, migrations, sync jobs, and APIs still need to walk the whole table, and they need a mechanism that does not degrade with depth. The demo compares mechanisms, so capping was out of scope.
Solution
I kept OFFSET as the baseline and implemented keyset pagination as the
challenger: remember the last id of the previous page and seek straight to
it. Because the seed assigns contiguous ids 1–50,000, the cursor for page
p is exactly (p - 1) * page_size rows in — no extra lookup query, and the
server verifies the invariant by diffing both result sets on every run. The
handler runs one query each, timed around execution and scan — the baseline
SELECT id, name FROM items ORDER BY id LIMIT ? OFFSET ? and the challenger
SELECT id, name FROM items WHERE id > ? ORDER BY id LIMIT ? — then reports
both timings plus the server-side identical flag.
The rest is plumbing the demo needs to stay honest: inputs are capped
(page_size 1–100, pages beyond the last row are a 400, never an unbounded
scan), both queries share a 10-second context timeout, and the speedup
ratio is clamped so the JSON stays finite even when a query rounds to zero
microseconds. The UI appends every run to a comparison table — newest first
— so you can walk pages 2, 100, 1000, 2000 yourself and watch the factor
climb: 2.0x, 3.7x, 22.1x, 29.7x on my box. If you are starting from a
codebase on OFFSET today, this is also the migration I recommend: ship the
keyset path for forward iteration (exports, feeds, sync), keep OFFSET
where humans jump to named pages, and let the benchmark above justify the
split to your team.
Trade-offs
Keyset pagination buys its flat line with constraints, and I would be lying
if I presented it as free. First, it needs a stable ordering with a unique
tiebreak — here id, in real tables usually (created_at, id). Without the
tiebreak, rows sharing a sort key can repeat or vanish between pages. Second,
it only moves forward (or backward) from a cursor: "jump to page 47" without
visiting page 46 requires either an offset or a cached cursor, so admin UIs
with random access still want OFFSET for shallow jumps. Third, the dataset
underneath is allowed to move: concurrent inserts shift offsets (page 2 of
an OFFSET walk can show a row twice), while a keyset walk sees each row
once but can miss rows inserted behind the cursor — pick your anomaly
consciously. Fourth, sort direction changes need mirrored cursors and
matching indexes; every new sort order is a new composite index to maintain
on write. None of this disqualifies keyset — it is still the right default
for forward scans — but "just add keyset" without the tiebreak and the index
is how you trade a performance problem for a correctness one.
Production Notes
Three things differ between this demo and a production rollout. First, treat
my numbers as shapes, not SLAs: rerunning the last page three times gave
offset timings of 3.434, 4.476, and 5.598 ms against keyset timings of
0.067, 0.296, and 0.202 ms — speedups of 51.3x, 15.1x, and 27.7x on the same
box within seconds. Single benchmark runs are anecdotes; in production I
would track the distribution (p50/p99 of both strategies per endpoint) and
alert on the ratio drifting, not on one slow page. Second, the index must
match the ORDER BY exactly or the planner sorts at runtime and the flat
line disappears — EXPLAIN QUERY PLAN on the keyset query is part of the
deploy checklist, and every new sort order needs its composite index before
traffic finds it. Third, keyset does not solve "page 47 of 312": COUNT(*)
over a huge filtered table is its own slow query, so plan for estimated
counts or cursor-only navigation ("next", no total) rather than exact page
counts at depth. Operationally this demo stays boring on purpose — one
binary, one SQLite file, POST /reset rate-limited to 6/minute so a curious
crowd cannot reseed-loop the box — and that boredom is the recommendation:
the pagination strategy is where the cleverness goes; everything around it
should be disposable.