Almost every list endpoint starts with LIMIT 20 OFFSET 40. It’s simple, it maps neatly onto page numbers, and it works fine for the first few pages. Then the table grows, someone scrolls deep into a feed or an export job walks the whole table, and two problems show up: pages get slower the further you go, and rows appear twice or vanish when data changes between requests. Keyset pagination (also called cursor or seek pagination) fixes both, and interviewers like it because it tests whether you understand what an index can and can’t do.
The measurements here are from PostgreSQL 16.15 on a posts table with one million rows and an index on (created_at DESC, id DESC).
What OFFSET actually does
OFFSET 900000 doesn’t jump to row 900,001. There’s no way to jump: the index is a sorted structure, not an array with positions. PostgreSQL walks the index from the start, produces 900,020 rows, throws away 900,000 and returns 20.
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT id, created_at, title
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 900000; Limit (actual rows=20 loops=1)
Buffers: shared hit=6621 read=3448
-> Index Scan using posts_created_id on posts (actual rows=900020 loops=1)
Buffers: shared hit=6621 read=3448
Execution Time: 84.669 msPage 2 (OFFSET 20) touched 4 buffers and took 0.09 ms. Page 45,001 touched about 10,000 buffers and took 85 ms. The cost is proportional to the offset, so a crawler or export job that walks every page does quadratic work overall. On a busier table with wider rows, that 85 ms turns into seconds.
Keyset: ask for rows after the last one you saw
Instead of “skip 900,000 rows”, say “give me rows that sort after the last row of the previous page”. If the previous page ended with the post created at 2026-01-24 03:33:00+00 with id 100001, the next page is:
SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < ('2026-01-24 03:33:00+00', 100001)
ORDER BY created_at DESC, id DESC
LIMIT 20; Limit (actual rows=20 loops=1)
Buffers: shared hit=3 read=1
-> Index Scan using posts_created_id on posts (actual rows=20 loops=1)
Index Cond: (ROW(created_at, id) < ROW('2026-01-24 03:33:00+00'::timestamp with time zone, 100001))
Execution Time: 0.248 msSame position in the list, 4 buffers instead of 10,000, and the condition is in Index Cond, which means PostgreSQL descended the B-tree straight to that point and read 20 entries. The first rows returned (ids 100000, 99999, 99998) are exactly what OFFSET 900000 returned, so the results agree; only the cost differs. Page 2 and page 45,001 cost the same.
The client gets a cursor instead of a page number. Usually that’s the last row’s sort values encoded into an opaque string (base64 of 2026-01-24T03:33:00Z|100001, or a signed token), which the API decodes on the next request.
Why the tiebreaker isn’t optional
Sorting by created_at alone looks enough until two rows share a timestamp, which is common with bulk imports, second-precision clocks or busy tables. Here’s a six-row feed where three posts landed at 09:05:
id | created_at
----+------------------------
1 | 2026-10-01 09:00:00+00
2 | 2026-10-01 09:05:00+00
3 | 2026-10-01 09:05:00+00
4 | 2026-10-01 09:05:00+00
5 | 2026-10-01 09:10:00+00
6 | 2026-10-01 09:12:00+00Paginate by time only, three per page. Page 1 with ORDER BY created_at DESC LIMIT 3 returned ids 6, 5, 2. Which of the three 09:05 rows comes third isn’t defined, and PostgreSQL happened to pick 2. Page 2 asks for created_at < '09:05' and returns only id 1. Posts 3 and 4 were never shown. Using <= instead would repeat id 2 and possibly others. With timestamps alone, there’s no condition that resumes exactly where the previous page stopped.
Add the primary key to make every sort position unique:
-- page 1: ORDER BY created_at DESC, id DESC LIMIT 3
6 | 09:12
5 | 09:10
4 | 09:05
-- page 2: WHERE (created_at, id) < ('09:05', 4)
3 | 09:05
2 | 09:05
1 | 09:00Every row appears exactly once. The same rule applies to OFFSET pagination, incidentally: without a unique final sort column, the database is free to order ties differently on each query, so offset pages can overlap too.
Row comparisons, the OR form and indexes
(created_at, id) < (a, b) is a standard SQL row comparison. It means “created_at is less than a, or created_at equals a and id is less than b”, which is lexicographic order, the same order the composite index uses. You’ll often see it written out by hand:
WHERE created_at < $1 OR (created_at = $1 AND id < $2)That’s logically identical, but on PostgreSQL 16 the planner didn’t use it as an index condition here:
Index Only Scan using posts_created_id on posts (actual rows=20 loops=1)
Filter: ((created_at < ...) OR ((created_at = ...) AND (id < 100001)))
Rows Removed by Filter: 900000
Execution Time: 64.108 msIt walked the index from the start, filtering 900,000 entries: no better than OFFSET. Prefer the row comparison in PostgreSQL. In databases without row-comparison support for indexes (MySQL’s optimiser historically handled them poorly), add a redundant created_at <= $1 AND (...) to give the optimiser a range it can use.
Two more requirements make it work:
- The index must match the sort.
(created_at DESC, id DESC)or(created_at, id)both work for this query, since a B-tree can be scanned backwards. Mixed directions (created_at DESC, id ASC) need an index with the same mix, and the row comparison no longer expresses the order, so you’re back to the expanded form. Keep sort directions uniform when you can. - Sort columns shouldn’t be NULL. Row comparisons with NULLs yield unknown, so rows with NULL
created_atwould never appear on any page after the first. Make themNOT NULLor coalesce consistently in both the sort and the condition.
What keyset gives up
You can’t jump to page 37. Keyset supports next (and previous, by flipping the comparison and the sort, then reversing the results in the application), which fits infinite scroll, “load more” buttons, API cursors and batch jobs. It’s a weaker fit for admin tables where users really do click page numbers; for those, OFFSET with a cap (say, 100 pages) and good filters is a reasonable compromise.
Keyset also behaves better under concurrent writes. With OFFSET, a post inserted at the top between page 1 and page 2 pushes everything down one position. In the six-row feed, page 1 showed 6, 5, 4; a new post 7 was inserted; then OFFSET 3 returned 4, 3, 2. Post 4 appeared twice. The keyset cursor (09:05, 4) still returns 3, 2, 1, because it’s anchored to a row, not a position. Deleted rows don’t cause skips either.
Total counts (“page 3 of 812”) need a separate count(*), which is expensive on big tables (see count semantics). Many APIs drop the total and return a has_more flag by fetching LIMIT 21 and checking whether the 21st row exists.
Interview practice: paginating a filtered, multi-tenant list
An API lists invoices for a tenant, newest first, optionally filtered by status. It uses WHERE tenant_id = $1 AND status = $2 ORDER BY issued_at DESC LIMIT 50 OFFSET $3, and large tenants report timeouts on deep pages plus occasional duplicate invoices in the UI. Redesign it.
Answer
Switch to keyset pagination with the primary key as a tiebreaker. The query becomes:
SELECT id, issued_at, number, total
FROM invoices
WHERE tenant_id = $1
AND status = $2
AND (issued_at, id) < ($3, $4)
ORDER BY issued_at DESC, id DESC
LIMIT 51;The first page omits the row condition. The response returns 50 rows, a has_more flag from the 51st, and a cursor encoding the 50th row’s issued_at and id.
The index should put equality columns first and the sort columns after them: (tenant_id, status, issued_at DESC, id DESC). Then the tenant and status equalities plus the row comparison all become one contiguous index range, and every page reads about 51 entries regardless of depth. If the status filter is optional, a second index on (tenant_id, issued_at DESC, id DESC) serves the unfiltered list.
The duplicates come from two things keyset fixes: ties on issued_at (invoices created in one batch share a timestamp) and inserts shifting offsets between requests. I’d also make issued_at NOT NULL, sign or encrypt the cursor so clients can’t hand-edit it into another tenant’s range (the tenant_id condition still protects data, but the cursor shouldn’t be a guessable API), and keep the old page-number interface only for small result sets if the UI depends on it.