Offset vs Cursor Pagination Lab (Interactive)
Scroll a live feed while posts land at the top and compare OFFSET scan latency and drift against keyset seeks. Walk a paginated feed page by page, inject concurrent posts, and watch offset windows slide backwards into duplicates while cursor seeks stay constant-time and drift-free.
Offset vs Cursor Pagination Lab
Walk a live feed page by page: OFFSET scans grow linearly while concurrent inserts shift the window and duplicate items.
SELECT * FROM posts ORDER BY created_at DESC LIMIT 5 OFFSET 0;
Delivered pages (newest first)
Last query latency
5.7 ms
Rows scanned (last)
5
Scan complexity
O(N) per page
New posts never served
0
OFFSET 0 forces the engine to read and discard 0 index tuples before returning 5 rows. With +2 posts landing per page, the window slides backwards over already-seen ids.
How It Works Under the Hood
OFFSET 1000000 LIMIT 20 forces the database engine to read, count, and discard a million index tuples before returning the requested slice, so latency grows linearly with scroll depth and deep pages blow gateway timeouts. Worse, when new rows land at the feed top while a user scrolls, the window shifts over already-seen rows and clients render duplicates. Keyset cursors store the last (created_at, id) pair and perform an O(log N) B-Tree seek to resume exactly after it — constant milliseconds at any depth — trading away random page jumps and cheap total counts.
Core Architectural Principles
- Offset latency scales O(N) because the engine discards every preceding row before slicing 20.
- Concurrent inserts at the feed top shift offset windows backward, producing duplicate rendered items.
- Compound opaque cursors (created_at, id) seek in O(log N) and resume after the last seen key.
For any infinite-scroll feed or message history design, state you choose cursor-based keyset pagination, explain both the O(N) scan penalty and page drift, specify a deterministic compound cursor (timestamp, unique id) for tie-breaking, and encode it opaque in Base64 so clients never couple to column names.
Cursors give constant-time stable deep paging but lose direct page jumps and trivial COUNT(*) totals that offset pagination provides.