Pagination: OFFSET vs Keyset (Cursor)

In one line

OFFSET pagination reads and discards all skipped rows, so page 10,000 is slow, and rows shift between pages under concurrent inserts. Keyset (cursor) pagination remembers the last row's sort key and continues WHERE (created_at, id) < (?, ?): constant cost per page and stable results, at the cost of no random page jumps.

0/5 · 0%

Think of it like this

Reading a long book. OFFSET is "count 5,000 pages from the start every time you want to resume". Keyset is a bookmark: open exactly where you stopped.

Key ideas

01
OFFSET 100000 LIMIT 20 makes the database produce 100,020 rows and throw away 100,000. Cost grows linearly with depth, which is also an easy denial-of-service vector.
02
Keyset: order by a unique, stable key (append id as a tiebreaker), return a cursor encoding the last row's values, and query WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 20. PostgreSQL supports row-value comparison with an index on (created_at, id).
03
Concurrent writes: with OFFSET, a new row at the top shifts everything, so users see duplicates or miss rows. Keyset is anchored to values, so new rows only appear on the first page.
04
Make cursors opaque (base64 of JSON, optionally signed) so clients can't tamper with them and you can change the format later. Filters must be part of the cursor contract.
05
OFFSET is fine for small, bounded lists (admin tables with page numbers up to a few hundred rows). For search results, cap depth ("showing first 1,000 results").

Code & diagrams

keyset.sqlsql

CREATE INDEX ON orders (customer_id, created_at DESC, id DESC);

-- page 1
SELECT id, created_at, total FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- last row: created_at = '2026-09-10 11:02:07+00', id = 881234

-- next page: continue after the last row
SELECT id, created_at, total FROM orders
WHERE customer_id = 42
  AND (created_at, id) < ('2026-09-10 11:02:07+00', 881234)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- cost comparison on 10M rows:
-- OFFSET 500000 LIMIT 20 -> ~420 ms, 500k rows read
-- keyset at the same depth -> ~0.2 ms, 20 rows read

Cursor.javajava

record Cursor(Instant createdAt, long id) {
    String encode() {
        return Base64.getUrlEncoder().withoutPadding()
            .encodeToString((createdAt.toEpochMilli() + ":" + id).getBytes(UTF_8));
    }
    static Cursor decode(String s) {
        String[] p = new String(Base64.getUrlDecoder().decode(s), UTF_8).split(":");
        return new Cursor(Instant.ofEpochMilli(Long.parseLong(p[0])), Long.parseLong(p[1]));
    }
}

Interview problem

The problem

Paginate a Twitter-like home feed

The feed shows posts from followed accounts, newest first, 30 per page, with infinite scroll. New posts arrive constantly. Users complain about duplicates when scrolling. Design pagination.

When it breaks

Keyset on a non-unique sort key

What you see

Rows with the same created_at on a page boundary are skipped or repeated.

Fix & prevent

Always add a unique tiebreaker (id) to both ORDER BY and the cursor comparison.

Explain it without notes

Why is deep OFFSET slow even with an index?

Practice

Write the keyset query for ascending order by (price, id) for a product listing filtered by category.

Trade-offs

↔
Keyset gives constant-time, stable pages but no "jump to page 57"; OFFSET allows random access but degrades with depth.

Done when you can

I can implement keyset pagination with composite cursors and explain its advantages.

Command Palette