LIMIT 20 OFFSET 400000 looks harmless — it is the most expensive query your API will run, and it gets worse on every page. This trace walks both sides: what the database physically does on an offset scan, and what a keyset query does instead.
1. Request for page N arrives
The API receives GET /api/events?page=20001&limit=20. The handler translates it mechanically: LIMIT 20 OFFSET 400000, attached to the same WHERE clause as page 1. Nothing in the query suggests the growing cost; the SQL looks identical to OFFSET 20.
2. The offset query plans
The planner picks an access path. With a (created_at) index, it plans an index scan: walk the index in order, count to the offset, return the next 20 rows. What the plan doesn't tell you — and what the executor pays for — is the discard work: every row before the offset must be visited and skipped. The index walk is O(offset) regardless of the page size.
3. The database scans and discards
Execution: the executor steps through the index from the beginning — page 1's rows, page 2's, ..., page 20,000's — comparing rowcount against the offset, throwing each away. The LIMIT 20 gives the executor early-exit permission only after the offset is reached. The cost table for a 1M-row table, LIMIT 20:
page 1 OFFSET 0 → 20 rows walked, ~0.2ms
page 100 OFFSET 1980 → 2000 rows walked, ~2ms
page 10,000 OFFSET 199980 → 200k rows walked, ~200ms
page 50,000 OFFSET 999980 → 1M rows walked, ~1s+ and growingWorse: if the query's WHERE doesn't match the index order (e.g., a filter on user_id plus ORDER BY created_at), the planner may sort — O(N log N) on the full filtered set per page — or bitmap-scan. Either way, per-page cost grows linearly with page depth, and deep pages become the API's p99.
4. Rows are returned — plus a count
Most list APIs also return a total count: SELECT count(*) FROM events WHERE ... — a full index scan of its own (~50-200ms on large tables, uncached). The count doubles the deep-page cost and is usually the larger half. It exists only so the client can render page numbers, and it is the first thing to delete when moving to keyset.
5. The keyset query runs on the cursor
The keyset version asks: WHERE (created_at, id) < ('2026-08-01T00:00:00', 482913) ORDER BY created_at DESC, id DESC LIMIT 20. The previous page's last row is the pagination state — there is no arithmetic, no offset, no counting. The client sends an opaque cursor token; the server decodes it to the keyset values.
6. The index walk becomes proportional to the page
The executor starts the index scan at the cursor position — it seeks directly to ('2026-08-01...', 482913) via the B-tree, then walks 20 rows. Work per page: O(page size + index seek (~3-4 B-tree levels of reads, microseconds-to-milliseconds)). Deep pages cost the same as page 1. The count(*) is gone, and rows inserted before the cursor simply don't shift anything: no duplicates, no missing rows, page state that is stable under writes.
The one caveat
Keyset has one real limitation: no random page access. "Go to page 500" requires walking 500 pages — 10,000 rows of index reads. The resolution in practice: keep keyset for the walk, and if "jump to page" is a real product need, compute the page start via a keyset seek (WHERE ... ORDER BY ... LIMIT 1 OFFSET page*limit-1 — one cheap seek, not a full scan) or use an indexed id-range encoding of position.
The one sentence
An offset query's cost is proportional to how far into the list you are — the database re-walks everything before the page, every time. A keyset query's cost is proportional to the page itself, and it stays constant until the end of the table.