Pagination (OFFSET vs keyset)

What Pagination Is

Pagination means returning a long list in small pages instead of all at once. The "My Orders" page in our app shows 20 orders, then loads 20 more as the user scrolls.

There are two common ways to ask for "the next 20":

Think of a long book. OFFSET is "turn to page 500" by counting every page from the start. Keyset is a bookmark: you open right where you stopped.

OFFSET Pagination

OFFSET skips a number of rows, and LIMIT caps how many come back:

-- page 3 (20 per page): skip 40, take 20
SELECT id, amount, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

It's simple and supports page numbers. But it has two problems.

Problem 1: deep pages get slow. The database can't jump to row 100,000. It reads and throws away every row before it:

1OFFSET 0      LIMIT 20 → read 20 rows          → fast
2OFFSET 1000   LIMIT 20 → read 1,020 rows       → fine
3OFFSET 100000 LIMIT 20 → read 100,020 rows,
4                         throw away 100,000    → slow

The work grows with the page number, even with an index.

Problem 2: rows shift while scrolling. If a new order arrives between page loads, everything moves down by one:

1Load page 1: orders A B C ... T   (20 newest)
2New order Z arrives at the top
3Load page 2 (OFFSET 20): starts at T again
4→ order T shows twice
5(If an order is deleted instead, one gets skipped)

Keyset (Cursor) Pagination

Keyset pagination remembers the last row of the current page and asks for rows after it. With an index on the sort columns, the database jumps straight there, like the B-tree lookup from the indexing pages.

The sort column must be unique, or rows with the same value can be skipped. Two orders can share the same created_at, so we add id as a tie-breaker:

1Page 1: 20 newest orders → last one is (created_at = 2026-10-20 19:05, id = 881)
2 
3Page 2: WHERE (created_at, id) < (2026-10-20 19:05, 881)
4        ORDER BY created_at DESC, id DESC
5        LIMIT 20
6→ jumps to that spot in the index, reads 20 rows

-- index matching the sort order
CREATE INDEX idx_orders_user_created_id
ON orders (user_id, created_at DESC, id DESC);

-- first page
SELECT id, amount, created_at FROM orders
WHERE user_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- next page: after the last row seen
SELECT id, amount, created_at FROM orders
WHERE user_id = 42
  AND (created_at, id) < ('2026-10-20 19:05:00', 881)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The values are sent to the client as an opaque cursor string (here, base64-encoded JSON). The client sends it back to get the next page, without needing to know what's inside.

1GET /orders                      → { orders: [...20], nextCursor: "eyJjcmVh..." }
2GET /orders?cursor=eyJjcmVh...   → { orders: [...20], nextCursor: "eyJ0aW1l..." }

New orders arriving at the top don't shift anything, because each page starts after a fixed row, not after a count.

Trade-offs

Interview Angle

Common mistakes:

• Sorting keyset pages by a non-unique column only, like created_at, which skips rows that share a value.
• Forgetting the index that matches the keyset sort order.
• Running COUNT(*) on a huge table on every page load just to show total pages.

Summary

1OFFSET   → skip N, take 20 → simple, page numbers
2         → slow on deep pages, duplicates/skips when data changes
3Keyset   → "after the last row I saw" → same speed on every page
4         → needs unique tie-breaker (created_at, id) + matching index
5         → no jumping to page N
6Cursor   → last row's values, encoded and sent to the client
7Choose   → OFFSET for small/admin lists, keyset for feeds, scroll, APIs

Fast pages assume the table's structure stays put. Next, migrations cover how to change a schema safely while the app is running.