Pagination: OFFSET vs Keyset

~12 min · operations, pagination

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

The slow page problem

OFFSET-based pagination is fine for the first 10 pages. By page 1000, you're asking the database to scan and discard 10,000 rows on every request. The latency creeps up linearly with offset. Eventually the user clicks page 100 and it takes 8 seconds.

Keyset pagination — constant cost

Instead of "page N", remember the last (sort_value, id) seen and ask "next 20 after this". The query uses an index range scan: WHERE (created_at, id) < (last_seen_created_at, last_seen_id) ORDER BY created_at DESC, id DESC LIMIT 20. Constant cost regardless of page depth.

Trade-offs

Keyset pagination is forward-only by default — no jumping to "page 50". You can support back-and-forth with two cursors. URLs become opaque (cursor strings instead of ?page=N). For most "infinite scroll" UIs, that's fine — page numbers were a UX concession to OFFSET, not a user need.

Code

OFFSET — degrades with depth·sql

-- Page 1
SELECT * FROM articles ORDER BY created_at DESC LIMIT 20;

-- Page 100
SELECT * FROM articles ORDER BY created_at DESC LIMIT 20 OFFSET 1980;
-- Reads 2000 rows, returns 20.

Keyset — constant cost·sql

-- First page
SELECT id, title, created_at
FROM   articles
ORDER  BY created_at DESC, id DESC
LIMIT  20;

-- Next page: pass the last (created_at, id) as cursor
SELECT id, title, created_at
FROM   articles
WHERE  (created_at, id) < ('2026-04-01 00:00:00', 12345)
ORDER  BY created_at DESC, id DESC
LIMIT  20;

Use the row constructor for compound cursors·python

# Last seen values from previous page
last_created_at = "2026-04-01 00:00:00"
last_id = 12345

rows = db.execute(
    """
    SELECT id, title, created_at
    FROM   articles
    WHERE  (created_at, id) < (%s, %s)
    ORDER  BY created_at DESC, id DESC
    LIMIT  %s
    """,
    (last_created_at, last_id, 20)
).fetchall()

External links

Use The Index, Luke — Pagination

Exercise

Take an OFFSET-based pagination endpoint and convert it to keyset. Compare query latency at page 1 and page 100 before and after. The improvement should be dramatic.