What they're testing
Whether you know what the database actually does with OFFSET, and whether you've noticed the duplicate-row bug when data shifts between pages.
The short answer~30 seconds
OFFSET can't skip: the engine still reads and discards 100,000 rows before returning 20, so deep pages get linearly slower. Replace it with keyset pagination — remember the sort values of the last row and ask WHERE (created_at, id) < ($1, $2) ORDER BY … LIMIT 20. Cost is flat at any depth, and it also removes the duplicate row you get when a new record lands mid-scroll.
The long answer
The second problem is less discussed and more annoying: OFFSET isn't stable. A user reads page 3, a new record lands at the top meanwhile, they go to page 4 and see the last row of page 3 again. With infinite scroll it's visible enough that users report it. Keyset doesn't have that, because it anchors to the last row's VALUES, not to an ordinal position.
The implementation detail people get wrong: the sort key must be unique, or you must append the primary key to break ties. Sorting on created_at alone with two rows in the same millisecond will skip or repeat rows. A tuple (created_at, id) compared as a row value — (created_at, id) < ($1, $2) — is the tidiest form; Postgres compares tuples lexicographically and can use a composite index on (created_at DESC, id DESC).
The genuine trade-off is that keyset gives up random access: there is no "page 47", only next and previous. For APIs and infinite scroll that costs nothing. For an admin table where people really do click page numbers, either keep OFFSET and cap the depth, or redesign toward filtering instead of deep paging.
-- OFFSET: đọc rồi vứt 100.000 hàng
SELECT * FROM posts ORDER BY created_at DESC, id DESC
OFFSET 100000 LIMIT 20; -- ~420 ms, càng sâu càng chậm
-- Keyset: đi thẳng vào vị trí trên index
SELECT * FROM posts
WHERE (created_at, id) < ($1, $2) -- giá trị của hàng cuối trang trước
ORDER BY created_at DESC, id DESC
LIMIT 20; -- ~1 ms, ở trang 1 hay trang 5.000 đều vậy
CREATE INDEX posts_feed_idx ON posts (created_at DESC, id DESC);What they'll ask next
?What should an API cursor contain?
Exactly the sort values of the last row, base64-encoded so clients treat it as opaque. Don't put an offset inside and call it a cursor — you keep the problem and lose the freedom to change the paging strategy later.
?What if you need a total page count?
An exact count on a large table is its own expensive query. Usually an estimate from statistics (reltuples) is enough for the UI, or you show "there is more" rather than a number nobody navigates to.
These lose points
- Saying OFFSET is slow "because there's no index". With an index it's still slow — the engine still walks every entry to count the offset out.
- Skipping the tie-break. Keyset without one silently drops rows, and only once the data is dense enough to collide.