APIs
Pagination: migrate an offset feed to keyset
Reading that offset dies at depth is not the same as watching page 10,000 time out and seeing a user get the same post twice. Build the broken version, reproduce both failures with evidence, then migrate to a keyset cursor API and prove each fix with measurements.
Turn the unit’s mental model into a reproducible engineering loop: seed a large table, demonstrate offset’s deep-page cost and write-drift, design a correct keyset cursor (unique key, matching index, opaque encoding, n+1 hasNextPage), and verify flat latency and zero drift under concurrent writes.
Take a list endpoint backed by a large table, demonstrate that offset pagination is both slow at depth and incorrect under concurrent writes, then migrate it to a keyset cursor API that stays flat and stable — proving every claim with before/after measurements, not assertions.
- A before/after table: page-1, page-1k, page-10k latency for offset vs keyset, plus the 'actual rows' read at each depth — measured under EXPLAIN ANALYZE, not estimated.
- Keyset latency is flat across all depths (within normal variance) and the composite index is confirmed used (Index Scan, no extra sort) in the plan.
- The concurrency test shows the documented duplicate/skip under offset and zero duplicates or skips under keyset across the same insert/delete workload.
- A short write-up naming the unique sort key chosen and why, how the opaque cursor is encoded/decoded, how hasNextPage is derived, and what replaced the exact count and why.
- Add bidirectional paging (before/after, first/last) the Relay way: flip the comparison operator and the ORDER BY direction, then reverse the returned slice so the client sees a stable order. Test that paging backward returns exactly the forward pages in reverse.
- Add an offset surface alongside keyset for a bounded admin table that genuinely needs 'jump to page N', and document why two strategies coexist for the same data.
- Add a cursor-tampering guard: sign or validate the opaque cursor so a hand-crafted cursor can't seek into expensive ranges, and show a malformed cursor is rejected cleanly rather than scanning.
- Repeat the experiment on a non-time sort (e.g. order by score DESC, id DESC where scores tie heavily) and show the tiebreaker prevents seam duplicates that created_at-only would cause.
This is the migration you will run on real list endpoints: prove the offset failures first — deep-page scan cost from the plan, and duplicate/skip drift under concurrent writes — then replace position with value. A unique, indexed (created_at, id) tuple, an opaque cursor, an n+1 hasNextPage probe, and a dropped-or-estimated count turn a feed that dies at depth into one that stays flat and correct. Doing it once on a seeded table makes the production version muscle memory — and teaches you when offset is still the right tool.