You can't return a million rows in one response, so you return a page at a time — and the two ways to do that have wildly different behavior at scale. Offset pagination (LIMIT/OFFSET) is simple and lets you jump to any page, but it gets slower the deeper you go and quietly duplicates or skips rows when the data changes underneath you. Cursor (keyset) pagination is fast at any depth and stable under inserts, at the cost of only moving next/previous. This lesson explains exactly why each behaves that way — down to the index mechanics — and why Fable's feeds use cursors keyed on ULIDs.

Pagination: offset vs cursor

An endpoint that returns "all the expenses in this group" is a bug waiting to happen — for an active group that's thousands of rows, a huge payload, a slow query, and a client that has to render it all. So APIs paginate: return a bounded page and a way to ask for the next one. There are two ways to do it, they look interchangeable in a demo, and they diverge sharply the moment the dataset is large or changing. Knowing which to reach for — and why — is the difference between a feed that stays fast forever and one that times out on page 200.

Offset pagination: simple, and quietly O(n)

The obvious approach is LIMIT / OFFSET: "skip the first N rows, give me the next 20."

SELECT * FROM expenses WHERE group_id = 'g1'
  ORDER BY created_at DESC
  LIMIT 20 OFFSET 40;   -- page 3, if pages are 20 rows

It's beautifully simple, and it gives you something cursors can't: random access — jump straight to page 50 by setting OFFSET 980. For a small dataset or an admin table with numbered pages, it's exactly right. But it has two flaws that get worse with scale, and both trace straight back to what OFFSET physically means.

It gets slower the deeper you page. OFFSET 40 doesn't magically start at row 41 — the database has to generate and then throw away the first 40 rows to find where your page begins. OFFSET 100000 means the database reads one hundred thousand rows through the index and heap and discards them, just to return the 20 you wanted. The work is O(offset): page 1 is instant, page 5000 is a table-scan's worth of wasted I/O. Deep pages don't just get slow — they get slow proportionally to how deep you go, which is why "page 1 is fine but the export that walks every page times out" is such a common story.

It duplicates and skips rows when data changes. Offset defines a page by position, and positions move. Load page 1 (rows 1–20). While you're reading, someone inserts a new expense at the top. Now request page 2 (OFFSET 20): every row shifted down one, so the row that was #20 is now #21 — and it appears again on page 2. You saw it twice. A deletion causes the mirror bug: a row is skipped entirely. On a busy, frequently-inserted list, offset pagination silently shows duplicates and drops rows, because the thing it counts from is shifting under it.

Cursor pagination: point at a value, not a position

Cursor (or keyset) pagination fixes both by changing the question from "skip N rows" to "give me the rows after this specific one." You order by a unique, stable key and remember the last value you saw — the cursor — then ask for rows beyond it:

SELECT * FROM expenses WHERE group_id = 'g1'
  AND id < :last_seen_id           -- the cursor
  ORDER BY id DESC
  LIMIT 20;
-- return the last row's id as the cursor for the next page

This is faster and stable, and both properties come from the same fact — the cursor is a value the index can seek to directly:

Constant speed at any depth. WHERE id < :cursor ORDER BY id DESC is an index seek straight to the cursor's position in the B-tree, then a scan of the next 20 entries. It reads only the rows it returns — O(limit), no matter how deep into the list you are. Page 1 and page 5000 cost the same. There are no discarded rows because there's no offset to count past.
Stable under inserts and deletes. The window is defined by a value (id < X), not a position, so inserting or deleting rows elsewhere doesn't shift your boundary. New expenses added while you scroll simply appear (or don't) relative to the cursor — never duplicated, never skipped. The page boundary is anchored to data, not to a moving count.

The price is the thing offset was good at: no random access. A cursor is inherently relative ("after this"), so you can go next (and, with care, previous), but you can't jump to "page 50" — there's no positional page number to jump to. That's a non-issue for the dominant use case (infinite-scroll feeds, timelines, "load more") and a real limitation for a classic numbered-pages UI.

The one requirement: cursor pagination needs a stable, unique, ordered sort key. A plain created_at isn't unique (two rows can share a timestamp, causing rows at the boundary to be skipped or repeated), so you either use a unique monotonic id or a compound cursor (created_at, then id as a tiebreak). A time-sortable unique id is the ideal cursor — which is exactly where Fable comes in.

Page through it yourself. In Offset mode, jump to a deep page and watch "rows read" balloon while you still get back only 8 — then insert a row at the top, hit Next, and catch the duplicate. Switch to Cursor and repeat: rows-read stays flat at any depth and the insert changes nothing — at the cost of no jump-to-page.

jump to

expense #120

expense #119

expense #118

expense #117

expense #116

expense #115

expense #114

expense #113

8rows the DB read for this pageO(offset + limit) — grows with depth

0duplicated rows on this pageinserts shift positions → repeats

Try it: jump to page 12 and watch rows read climb to ~96 for the same 8 you get back — that's O(offset) work thrown away. Then Insert a row at top and hit Next: a row you already saw comes back, because offset counts from a position that just moved.

The deep-page cliff and the shifting-window bug — the same two failures, always

Offset pagination fails in production in two specific, repeatable ways, and recognizing them is worth more than memorizing syntax.

The deep-page performance cliff. Everything is fine in testing (a few pages) and in the demo (a small table). Then real data arrives, someone (or a script, or a crawler) requests a deep page, and the query that was 5ms at OFFSET 20 is 5 seconds at OFFSET 500000 because the database is discarding half a million rows per request. Worse, a job that paginates through the entire dataset with offset is accidentally O(n²) — each page rescans everything before it — so a full export slows to a crawl and eventually times out. The fix is to switch that path to a cursor, which is O(limit) per page and O(n) for a full walk.

The shifting-window duplicate/skip. On a list with active inserts (a feed, a chat, a group's expenses), a user scrolling with offset pagination sees the same item twice at page boundaries when new items are added above, and misses items when items are removed — because offset counts from a top that keeps moving. It's rarely caught in testing (test data doesn't change mid-scroll) and shows up as "why did this expense appear twice?" bug reports in production. The fix is the same: a cursor anchors the window to a value that doesn't shift.

Both bugs have one root — offset defines a page by a position in a set that is changing and must be recounted every time — and one fix: paginate by a stable key instead.

Where this lives in Fable

Fable's expense list is cursor-paginated, and it's a clean textbook implementation because the data model hands it the perfect cursor for free. The list endpoint returns { items, next_cursor }; the query is where id < :before, orderBy id desc, take: limit + 1. Three things in that line each map to this lesson. First, the cursor is the ULID primary key — and because ULIDs are time-sortable and unique, ORDER BY id DESC is "newest first," and id < :before is "the page older than what you've seen," so a single column serves as both sort order and cursor with no compound-key gymnastics. Second, it's fast at any depth for exactly the reason above — it's a B-tree seek to the cursor then a short scan, never a discard-the-first-N offset — which matters because an active group's expense history grows without bound and a user can scroll a long way back. Third, it's stable while people add expenses: someone adding a dinner while you're scrolling the history never makes an older expense appear twice or vanish, because the window is pinned to an id value, not a position. There's even the classic take: limit + 1 trick — fetch one extra row and, if it comes back, you know there's a next page (hasMore) and what the next cursor is, all without a separate COUNT(*) query. And the deliberate trade is the right one: an expense feed is infinite-scroll, so losing "jump to page 50" costs nothing, while gaining constant-time, stable paging costs everything to a growing money history. Offset would have been simpler to write and wrong by the hundredth page.

Go deeper

Use The Index, Luke! — "We need tool support for keyset pagination" (No Offset) — The definitive explanation of why OFFSET is O(offset) and how keyset pagination becomes a single index seek — with the SQL and index diagrams that make the performance difference concrete.
Relay — GraphQL Cursor Connections spec — The standardized cursor-pagination contract (edges, cursors, pageInfo, hasNextPage) that most modern APIs converge on — a good model for designing the shape Fable returns.
Citus — "Five ways to paginate in Postgres" — A hands-on comparison of LIMIT/OFFSET, keyset, and other strategies with real Postgres numbers — see the deep-page cliff measured, not just described.

Check yourself

Answer out loud, as if an interviewer asked. If you hand-wave, reread that section.

Explain physically what OFFSET 100000 makes the database do, and why offset pagination is O(offset) — tying it back to the index/heap reads from the databases track.
Offset pagination can show a row twice or skip one when data changes. Walk through the insert-at-the-top scenario that causes a duplicate at a page boundary, and explain the root cause in one phrase.
Cursor pagination is both faster and more stable than offset. Explain how a single fact — the cursor is a value the index can seek to — produces both properties.
What is the one thing cursor pagination gives up that offset provides, and why is that acceptable for an infinite-scroll feed but not for a numbered-pages admin UI?
Cursor pagination requires a stable, unique, ordered key. Explain what goes wrong if you use a plain non-unique created_at, and how a compound cursor or a unique id fixes it.
Fable paginates expenses by ULID with id < :before, order by id desc, take limit + 1. Explain why the ULID is an ideal cursor, how the +1 avoids a COUNT query, and why the loss of jump-to-page costs Fable nothing.

Pagination: offset vs cursor

Pagination: offset vs cursor

Offset pagination: simple, and quietly O(n)

Cursor pagination: point at a value, not a position

Go deeper

Check yourself

On this page