Pagination: offset, cursor, and keyset

Why lists come in pages

Picture a support tool. An agent opens the ticket queue, and the queue holds 80,000 tickets going back four years. Each ticket in the list is about 600 bytes of JSON: subject, customer, status, assignee, a few dates. Sending the whole queue would be about 48 MB.

Nobody needs that. The agent looks at the newest 25 tickets, maybe scrolls to the next 25, and then opens one. So the app asks for the list one slice at a time. That is pagination: the server returns a page of items plus a way to ask for the next page.

The idea is simple. The hard part, and the reason this topic comes up in interviews, is the word "next". The list keeps changing while people read it: new tickets arrive at the top, and old ones are closed and leave the queue. How you say "next" decides whether the agent sees a ticket twice, never sees a ticket at all, or waits three seconds for page 400.

What a page saves, layer by layer

Pagination protects every layer of the system at the same time. It is worth naming each one, because each layer gives you a different reason to pick a page size.

• The database reads 25 rows instead of 80,000. With the right index, that is a few milliseconds instead of a full table scan.
• The network carries about 15 KB instead of 48 MB. On a 10 Mbps mobile link that is the difference between about 12 ms of transfer and more than 38 seconds.
• The browser parses a small JSON body and renders 25 rows. A list of 80,000 rows would mean hundreds of thousands of DOM nodes, slow layout, and janky scrolling (the virtualization lesson covers rendering big lists).
• The user sees the first items in a few hundred milliseconds, instead of a spinner while everything loads.

Choosing a page size

Small pages, like 10, make the first load fast but cost more requests as people scroll. Large pages, like 200, cost fewer requests but make each one slower and waste bytes on items nobody looks at. For feeds and tables, 20 to 50 is a common middle ground. Chat history often uses 50 to 100, because messages are small and people scroll fast.

Whatever the client asks for, the server must cap it. If the API accepts limit=1000000, one curious user or one buggy script can ask for the whole table. Stripe, for example, accepts a limit from 1 to 100 and rejects anything larger. Pick a default (say 25) and a maximum (say 100), and either reject larger values or clamp them to the maximum.

Two separate questions

Pagination is really two decisions that people often mix up. Keep them apart and the rest of this lesson gets easier.

1. The API question: how does the client say "next"? By position (offset), or by a bookmark that points at the last item seen (cursor, built on a keyset query). This decides correctness and database cost.
2. The UI question: how does the user move through the list? Numbered pages, a "Load more" button, or infinite scroll. This decides usability, accessibility, and what the back button does.

The two pull on each other. Numbered pages want to jump to page 7, which offsets do well and cursors do badly. Infinite scroll only ever wants "the next one", which is exactly what cursors do well. The lesson covers the API side first, then the UI side, then the hardest case: a chat window that pages in both directions.

Offset and limit, and why it drifts

The simplest way to say "next" is by position. The client sends ?offset=50&limit=25: skip the first 50 items, return the next 25. Some APIs use ?page=3&per_page=25 instead, which is the same thing with a multiplication: page 3 is offset 50.

GET /tickets?status=open&offset=50&limit=25

-- becomes, on the server:
SELECT id, subject, created_at
FROM tickets
WHERE status = 'open'
ORDER BY created_at DESC
LIMIT 25 OFFSET 50;

It has real strengths. Any page is one request away (page 7 is offset 150), so numbered page links are trivial, and with a total count you can show "Page 3 of 3,200". The URL ?page=3 is easy to share and bookmark. For a list that never changes while people read it, like last year's invoices, offset is perfectly fine.

Drift: positions move, items don't stay put

The trouble is that an offset counts positions, not items. Position 50 means "whatever happens to be 51st right now". If the list changes between two requests, the same position points at a different item. This is called drift, and it shows up in two ways.

• An insert above the reader causes a duplicate. You load page 1 (positions 1 to 25). A new ticket arrives at the top. Everything shifts down by one, so the old item 25 is now at position 26. Page 2 starts at position 26 and shows it again.
• A delete above the reader causes a skip. You load page 1. Someone closes a ticket that was on page 1, so it leaves the open queue. Everything shifts up by one, and the old item 26 is now at position 25. Page 2 starts at position 26, so the old item 26 is never shown to you at all.

The duplicate is annoying but visible. The skip is worse, because nobody notices. In a support queue, a skipped ticket is a customer who never gets an answer. Step through both cases in the lab below: watch which item lands at each position before and after the change.

Busy lists drift all the time. A feed that gets 10 new posts a minute shifts by 10 positions every minute, so a user who reads page 1 for 30 seconds sees 5 repeats at the top of page 2. In an infinite-scroll feed those repeats look like a bug, and React will also warn about duplicate key values if you render them.

Patches that only half work

Teams that are stuck with offsets often try two patches. Both are worth knowing, and both are worth knowing the limits of.

• Remove duplicates on the client by id. Keep a Set of ids you have shown and drop repeats. This hides duplicates, but does nothing for skips, and a page full of repeats arrives looking almost empty.
• Freeze the list with a snapshot time. The first request returns asOf=2026-10-19T10:00:00Z, and every later page adds created_at <= asOf. New items no longer shift the list, which fixes duplicates from inserts. Deletes and status changes still shift it, so skips remain.

Deep pages get slow

The second problem is cost. A database cannot jump to position 100,000. To answer LIMIT 25 OFFSET 100000, it walks the sorted rows from the start, counts 100,000 of them, throws them away, and returns the next 25. Even with an index on the sort column, the work grows with the offset.

So page 1 takes a couple of milliseconds and page 4,000 can take hundreds of milliseconds or more, depending on row size and what is in memory. Real users rarely go that deep, but crawlers, export scripts, and bots do, and one bot walking every page can load a database far more than all your users together. This is why many search systems refuse deep offsets: Elasticsearch, by default, rejects requests where from + size is over 10,000.

Cursors: a bookmark instead of a position

A cursor fixes drift by changing the question. Instead of "give me positions 26 to 50", the client says "give me the 25 items that come after this one". The "this one" is the last item it already has. New items above it and deleted items above it no longer matter, because the request is anchored on an item, not a position.

The client does not build that anchor itself; the server hands it out. Every page comes back with a cursor: a short string that marks where the page ended. To get the next page, the client sends the cursor back unchanged. When there is nothing more, the server returns null.

The response shape

{
  "items": [
    { "id": "tk_9051", "subject": "Refund not received", "createdAt": "2026-10-19T09:58:12.431Z" },
    { "id": "tk_9050", "subject": "Cannot log in", "createdAt": "2026-10-19T09:57:40.002Z" }
  ],
  "nextCursor": "eyJ2IjoxLCJ0IjoiMjAyNi0xMC0xOVQwOTo1Nzo0MC4wMDJaIiwiaWQiOiJ0a185MDUwIn0",
  "hasMore": true
}

How does the server know there is more? A common trick is to ask the database for one extra row: LIMIT 26 for a page of 25. If 26 rows come back, there is more, so the server returns the first 25 and builds the cursor from the 25th; if 25 or fewer come back, this is the last page. It costs one row and avoids a count query entirely.

What is inside a cursor

A typical cursor is the sort key of the last item plus its id, put in a small JSON object and encoded with base64url (the URL-safe variant of base64, using - and _ instead of + and /, so it can sit in a query string without escaping). Decode the cursor above and you get the timestamp and id of ticket tk_9050, plus a version number.

type CursorV1 = { v: 1; t: string; id: string };

function encodeCursor(row: { createdAt: string; id: string }): string {
  const payload: CursorV1 = { v: 1, t: row.createdAt, id: row.id };
  return Buffer.from(JSON.stringify(payload)).toString("base64url");
}

function decodeCursor(raw: string): CursorV1 {
  try {
    const parsed = JSON.parse(Buffer.from(raw, "base64url").toString("utf8"));
    if (parsed.v !== 1 || typeof parsed.t !== "string" || typeof parsed.id !== "string") {
      throw new Error("bad shape");
    }
    return parsed;
  } catch {
    throw new HttpError(400, "invalid_cursor");
  }
}

Why cursors are opaque

Base64 is not encryption. Anyone can decode it, and that is fine. The point of an opaque cursor is a contract: the client promises to treat it as a meaningless token and never build or edit one. That promise gives the server freedom.

• You can change what is inside. Today the cursor holds createdAt and id. Next year you add a "sort by priority" option and the cursor needs priority too. Bump v to 2, and no client code changes.
• You can put the query inside. Store the filter and sort the cursor was made for. If a request arrives with a cursor made for status=open but the URL now says status=closed, reject it instead of returning a confusing mix.
• You can sign it if you need to. If the cursor holds anything a user should not tamper with, add an HMAC (a keyed hash) and check it on the way in. For a plain sort key this is usually not needed, because a forged cursor can only reach rows the user's own filters already allow.

What cursors cost you

Cursors give up random access. There is no way to know the cursor for page 50 without walking pages 1 to 49, so "jump to page 50" is gone, and so is "page 3 of 3,200" unless you count separately. For feeds, inboxes, chat, and activity logs nobody misses these. For an admin table where people say "it was on page 12", they matter.

Cursors also go stale. A cursor kept in a bookmark for a month might point at a deleted item or an old format. A good server handles both: a keyset cursor still works after its item is deleted (the next section shows why), and an unknown version gets a 400 with a clear error code, so the client can drop the cursor and restart from page 1.

Keyset queries and tie-breakers

A cursor is the API contract. Keyset pagination is the database technique that makes it fast. Instead of "skip N rows", the query says "rows whose sort key comes after this key". The database can answer that by jumping straight to the key in an index, the way you open a dictionary at "M" instead of counting pages from the front.

-- An index that matches the sort order exactly
CREATE INDEX tickets_open_feed ON tickets (status, created_at DESC, id DESC);

-- First page: no cursor
SELECT id, subject, created_at FROM tickets
WHERE status = 'open'
ORDER BY created_at DESC, id DESC
LIMIT 26;

-- Next page: the cursor decoded to t = '2026-10-19T09:57:40.002Z', id = 'tk_9050'
SELECT id, subject, created_at FROM tickets
WHERE status = 'open'
  AND (created_at, id) < ('2026-10-19T09:57:40.002Z', 'tk_9050')
ORDER BY created_at DESC, id DESC
LIMIT 26;

With the matching index, the database descends the index tree to the cursor's position in a few steps, then reads the next 26 entries in order. Page 1 and page 4,000 cost the same: a seek plus 26 rows. That is the whole performance argument for keyset.

Keyset also explains why a cursor survives deletes. The query does not need the anchor row to exist. It only asks for rows that sort after the anchor's key. If ticket tk_9050 was deleted a minute ago, the query still finds tk_9049 and the rest, exactly where they should be.

Tie-breakers: the bug that hides in timestamps

The most common keyset bug is sorting by a column that is not unique. Timestamps look unique, but they are not: a bulk import can write 500 rows in the same millisecond, and a column with second precision gives many rows the same created_at. Even with microseconds, two busy servers can collide.

Now suppose page 1 ends in the middle of a group of rows that share one timestamp, and the next query says WHERE created_at < :t. Every row in that group that did not fit on page 1 is skipped, because its time is equal, not smaller. Switch to <= and you get the opposite bug: the rows from page 1 come back as duplicates. Try both in the lab.

The fix is to make the sort key unique by adding a tie-breaker: a column that is unique, usually the primary key. Sort by (created_at, id), put both in the cursor, and compare both. Within the same timestamp, the id decides the order, so every row has exactly one place in the list.

Details that bite in production

• Row comparisons are not supported equally everywhere. PostgreSQL uses an index for (a, b) < (x, y). Other databases may lack row comparisons or plan them poorly, so write the expanded form a < x OR (a = x AND b < y), and add a redundant a <= x AND in front so the planner can still use the index for the range.
• Mixed directions need care. Sorting by priority ASC, created_at DESC cannot be written as one row comparison. Use the expanded form with the right operator for each column, and an index with the same directions.
• NULLs need a rule. If the sort column can be NULL, decide where NULLs go (NULLS LAST) and handle the case in the comparison, or the page that crosses from values to NULLs breaks. Simpler: make sort columns NOT NULL.
• Every sort order needs an index. A table where users can sort by any of 12 columns needs an index for each order you allow. Allow only a few sorts, or move the list to a search engine, which has its own keyset (search_after in Elasticsearch and OpenSearch).
• Ids should be comparable. Sequential ids or time-ordered ids (like UUIDv7 or ULID) work well as tie-breakers. Random UUIDv4 still works as a tie-breaker, since only uniqueness matters there, but it cannot be the main sort key for "newest first".

What a total count really costs

"Showing 1 to 25 of 48,213 tickets" looks like a small detail. It can be the most expensive part of the request. The page needs 25 rows. The count needs to look at all 48,213 of them.

Why counting is slow

A keyset page stops after 26 rows. A count cannot stop early: to know how many rows match status = 'open' AND assignee = 'me', the database has to visit every matching row or index entry.

In PostgreSQL there is an extra cost. Because of MVCC (multi-version concurrency control, which keeps old row versions around so readers and writers don't block each other), the database must check that each matching row version is visible to your transaction. An index-only scan can skip that check for pages the visibility map marks as all-visible, but it still touches every matching entry. There is no stored row count it can read for a filtered query.

The numbers grow fast. On a table of 50 million rows, a count with a broad filter can take seconds, while the page itself takes 3 ms. And the count runs on every page load, every filter change, and every refresh, so it often ends up as the biggest query in the whole app.

What to show instead

Large products already work this way. Web search shows "About 1,230,000 results", an estimate. GitHub's search API returns a total_count but only lets you page through the first 1,000 results. Most social feeds show no count at all.

Page numbers, Load more, or infinite scroll

Now the UI question: how does the user move through the list? There are three common answers, and each one fits some products and hurts others.

• Numbered pages. Links like 1, 2, 3, Next. Each page replaces the last. The page number sits in the URL (?page=3), so it can be shared, bookmarked, and restored by the back button. It pairs naturally with offsets.
• Load more. A button at the end of the list appends the next page below the current one. It uses cursors, keeps the footer reachable, and only loads what people ask for.
• Infinite scroll. The next page loads by itself as the user nears the end. It suits feeds, where people browse rather than search, and it pairs with cursors because it only ever wants "the next one".

How infinite scroll triggers

Do not listen to every scroll event and measure the page. Put a small empty element, a sentinel, after the last item, and watch it with an IntersectionObserver. A rootMargin of a few hundred pixels makes the next page start loading before the user reaches the end, so the list rarely runs dry.

const observer = new IntersectionObserver(
  (entries) => {
    if (entries[0].isIntersecting && hasMore && !loading) {
      loadNextPage(nextCursor);
    }
  },
  { rootMargin: "800px 0px" },
);
observer.observe(sentinel);

Infinite scroll also grows the DOM forever. After 40 pages of 25 items, the list holds 1,000 rows. Pair long feeds with virtualization (render only the rows near the viewport) and drop pages far behind the user from memory if they can be fetched again.

Accessibility

• Footer reachability. With infinite scroll, the footer (contact, legal, language links) keeps running away. Either avoid footers on infinite pages, or stop auto-loading after a few pages and switch to a "Load more" button.
• Focus after loading. After "Load more", keep focus where it is or move it to the first new item, never to the top of the page. A keyboard user who has to tab through 50 old items to reach the new ones will give up.
• Announce the change. A polite live region (aria-live="polite") saying "25 more tickets loaded" tells screen reader users something happened.
• Use the feed pattern for true feeds. The WAI-ARIA feed role, with article items carrying aria-posinset and aria-setsize (set to -1 when the total is unknown), plus aria-busy while loading, gives assistive tech a proper model of an endless list. Its keys (Page Down and Page Up between articles, Control+End to move past the feed) also give keyboard users a way out to the footer.

The back button

Here is the classic infinite-scroll complaint. A user scrolls 6 pages deep, opens a product, presses Back, and lands at the top of page 1. Everything they scrolled past is gone. With numbered pages this never happens, because ?page=7 is in the URL.

Fixing it takes three pieces. First, keep the loaded pages: a client cache such as TanStack Query's useInfiniteQuery keeps every page and its cursor in memory, so a single-page app can render them again at once. Second, remember the scroll spot: save the scroll position, or better, the id of the item at the top of the viewport, in history.state or sessionStorage, and set history.scrollRestoration to "manual" so the browser does not fight you.

Third, for full page loads, let the browser's back/forward cache (bfcache, which keeps a page you left alive in memory so Back shows it instantly) restore the whole page, scroll position included. Avoid things that block it, such as unload handlers.

Paging in both directions: chat

Chat turns the usual list upside down. A conversation opens at the newest message, at the bottom, and people scroll up to read history, while new messages arrive at the bottom in real time. Sometimes the view opens in the middle: at the first unread message, or at a message someone linked or found through search. From there the user can scroll both ways.

So the client holds a window: a continuous run of messages from some oldest loaded message to some newest loaded message. It can grow at either end. The API needs a cursor for each direction.

Three ways to ask

GET /channels/c42/messages?before=m_7001&limit=50   # older than m_7001
GET /channels/c42/messages?after=m_7350&limit=50    # newer than m_7350
GET /channels/c42/messages?around=m_7200&limit=50   # 25 before, the anchor, 24 after

200 OK
{
  "messages": [ ...50 messages, oldest first... ],
  "hasOlder": true,
  "hasNewer": false
}

Discord's public API has exactly these three parameters on its "get channel messages" endpoint, with a limit from 1 to 100 (default 50). Slack's conversations.history uses oldest and latest timestamps to bound the range, plus a cursor for the next page. The idea is the same: anchor on a message, read in one direction.

The window has states

The tricky part is what to do with a live message pushed over a WebSocket. It depends on whether the window touches the present.

• At the present (hasNewer is false). The newest loaded message is the newest message in the channel. A live message can simply be appended. If the user is scrolled to the bottom, stick to the bottom so the new message is visible.
• Detached (hasNewer is true). The user jumped to an old message. There is a gap between the window and the present. Appending a live message here would put it right after a message from last Tuesday, with hundreds of messages missing in between. Instead, count it and show a "New messages, jump to present" button.

The same logic covers reconnects. If the socket drops for 2 minutes, the window was at the present but may now have a gap. On reconnect, fetch after= the newest id you have, repeat until hasNewer is false, and only then go back to appending live messages. Try it in the lab: jump to an old message, then send live messages, and watch where they go.

Keeping the screen still when history loads

When older messages are inserted above the ones on screen, the content gets taller above the viewport. If scrollTop stays the same, the text the user was reading jumps down by the height of the new messages. The fix is to measure before and adjust after.

// Before rendering the older page
const before = list.scrollHeight;

// After the DOM is updated (useLayoutEffect in React, before paint)
const added = list.scrollHeight - before;
list.scrollTop += added;

Browsers have a built-in feature for this, scroll anchoring (controlled by the CSS overflow-anchor property), which keeps the visible content still when content above it changes. Chrome, Edge, and Firefox support it, and Safari added it only in Safari 27 (September 2026), so most chat apps still make the adjustment themselves for older iPhones and Macs. Another common approach renders the list with flex-direction: column-reverse, so the scroll origin is the bottom and new history grows away from the user.

Memory and long sessions

A user who scrolls back through a year of a busy channel could load 20,000 messages. Keep the window bounded: when it grows past, say, 300 messages, drop messages from the end far from the viewport and set hasOlder or hasNewer back to true for that side. The window slides instead of growing, and virtualization keeps the DOM small.

Failure modes and edge cases

Most pagination bugs are quiet. Nothing crashes: an item is missing, a row appears twice, a page is slow only for one customer with a big account. This chapter lists the ones that reach production, how you notice them, and the fix.

Races on the client

Two of the rows above are client bugs, not server bugs. The first is a double fetch: the sentinel stays visible, the observer fires twice, and two requests go out with the same cursor, so the same page is appended twice. Guard with a loading flag, or key requests by cursor so the second one is ignored. Libraries like TanStack Query do this for you.

The second is a stale response. The user is on page 3 of "open tickets", switches the filter to "closed", and the page 4 request for "open" arrives late and gets appended to the closed list. Treat each query (filter plus sort) as its own list with its own cursors, and drop any response whose query is no longer current. The request-races lesson covers this pattern in depth.

New items above a cursor walk

Cursors fix drift by ignoring everything above the anchor. That has a flip side: items created after the user loaded page 1 are not in the walk at all. In a feed, that is fine, and products show a "12 new posts" pill at the top that loads them on demand. To build it, keep a second cursor for the newest item you have and ask for after= it, or let a WebSocket push a count.

Caching pages

Cursor pages are great for HTTP caching in one way: in a newest-first list, new items land above the anchor, so a page anchored on an old item changes only when something in it is edited or deleted. The first page is the opposite, since it changes with every new item. A short max-age on the first page and a longer one on cursor pages is a fair split for public lists. For private lists, use private and rely on the client cache instead.

In real products

Public APIs are a good place to see these choices made. Their docs are stable, and they show which trade-offs large teams picked.

• Stripe lists objects with limit (1 to 100), starting_after, and ending_before, each taking an object id, and returns has_more. That is cursor pagination where the cursor is simply the last id, in both directions.
• GitHub's REST API returns pagination links in the Link response header with rel="next", rel="prev", and so on. Clients follow the links instead of building URLs, which lets GitHub use page numbers on some endpoints and cursors on others without clients caring.
• GitHub's GraphQL API and other Relay-style APIs use connections: first and after for forward paging, last and before for backward, with pageInfo.endCursor and hasNextPage.
• Slack's Web API uses a cursor parameter and returns response_metadata.next_cursor, empty when you reach the end.
• Elasticsearch limits from + size to 10,000 by default and points deep readers to search_after, its keyset form, usually with a point in time so the results stay consistent.

The pattern is clear. APIs over large, changing data use cursors. Page numbers survive where people need them: search result pages, small admin tables, and documentation. Counts are estimates or caps on anything large.

On the frontend side, data libraries model this directly. TanStack Query's useInfiniteQuery stores a list of pages plus a getNextPageParam function that reads the next cursor from the last page; RTK Query, SWR (useSWRInfinite), and Apollo Client (with field policies that merge pages) have equivalents. They handle de-duplicating in-flight requests and caching pages per query, which removes the client races from the previous chapter.

Using it in a system design interview

Almost every frontend design question has a list in it: a news feed, an inbox, a product grid, comments, search results, chat. Pagination is where you show that you think about data that changes while people look at it. Bring it up when you define the API, not as an afterthought.

What to say, in order

1. Name the list and how it changes. "The feed gets new posts every few seconds and posts can be deleted." This sets up why offsets are a problem.
2. Pick cursor pagination and say why. "Offset would show duplicates and skip posts when the feed changes, and deep pages get slow. I will use an opaque cursor with the last item's timestamp and id."
3. Show the endpoint. GET /feed?cursor=&limit=20 returning items, nextCursor, and hasMore. Mention the server-side cap on limit.
4. Pick the UI and cover accessibility. Infinite scroll with an IntersectionObserver sentinel for a feed, or "Load more" if a footer or keyboard use matters. Mention focus, a live region, and restoring the position on Back.
5. Address the count. "The feed has no total; hasMore is enough." For search: "About N results, from an estimate."
6. Handle new items. A "new posts" pill at the top, fed by a newer-than cursor or a pushed count.

For chat, add the two-way window: before, after, and around cursors, the "at present" versus "detached" states, gap filling on reconnect, and scroll position kept still when history loads.

How deep to go by level