Design Pagination: Offset vs Cursor (Keyset)

Every list endpoint needs pagination. Pagination means splitting a long list of results into small pages, so the server sends 20 items at a time instead of 2 million. A precise answer is a strong signal in an interview: "here is the SQL, here is the index, here is why rows repeat".

Words you need first

• Row is one record in a database table (one order, for example). Column is one field of every row (such as created_at).
• Index is an extra sorted list that lets the database find rows fast, like the index at the back of a book. A composite index is an index on more than one column, such as (created_at, id).
• Cursor is a small value that marks "where you stopped" in a list. The client sends it back to get the next page. (It is not the same as a database cursor.)
• Keyset means "use the values of the sort columns of the last row as the position". Keyset pagination and cursor pagination are two names for the same idea in this lesson.
• Query plan is the way the database decides to run a query. The EXPLAIN command shows it.
• Latency is how long a request takes.
• Big-O notation, such as O(offset), says how the cost grows. O(offset) means the cost grows in step with the offset: ten times the offset costs about ten times the work.

Offset pagination

GET /orders?page=3&limit=20

SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 40;

ORDER BY created_at DESC sorts the newest rows first. OFFSET 40 means "skip the first 40 rows". LIMIT 20 means "then return 20 rows". Page 3 with 20 rows per page skips 2 × 20 = 40 rows.

Pros

• Easy to build and easy to understand.
• Lets users jump to any page and see "page 3 of 57" (this needs a COUNT(*)).
• Works with any sort order with no extra design.

Cons

1. It gets slower with depth. With OFFSET 100000, the database must find and read and throw away 100,000 rows before it returns 20. Even with an index, it must walk through 100,000 index entries. The time grows in a straight line with the page number.
2. Results are inconsistent when data changes. Between two page requests, rows are added or deleted, so everything moves. The user sees duplicates (a new row pushes the old last row onto the next page) or misses rows (a deleted row pulls an unseen row onto the previous page).
3. COUNT(*) (a query that counts rows) for the total is also slow on big tables.

Why feeds repeat or skip items

Scenario: items repeat or get skipped while you page through a live feed.

Newest first, 3 per page. Each letter is one item. E is newer than D, and so on.Page 1 (offset 0): [E, D, C]            User is now looking at E, D, C                                         — someone posts a new item F —Order is now: F, E, D, C, B, APage 2 (offset 3): [C, B, A]            C appears AGAIN (a duplicate), because everything shifted by one.If an item is DELETED between pages instead, one unseen item slides into the earlier page and is skipped.

The root cause: an offset is a position, and positions change when the data changes. A cursor points at a specific row, and that row does not move.

Cursor (keyset) pagination

Do not say "skip N rows". Say "give me the rows after this one". You use the sort key to find "this one".

GET /orders?limit=20→ { "data": [...20 items...], "page": { "nextCursor": "eyJjIjoiMjAyNi0wNS0wMVQwOTozMDowMFoiLCJpIjoxNDJ9" } }GET /orders?limit=20&cursor=eyJjIjoi...

-- first pageSELECT * FROM orders ORDER BY created_at DESC, id DESC LIMIT 21;-- next page: rows strictly "after" the last seen (created_at, id) in this ordering-- ("$1" and "$2" are placeholders: the database fills in the real values safely)SELECT * FROM ordersWHERE (created_at, id) < ($1, $2)          -- $1, $2 = the cursor values from the last rowORDER BY created_at DESC, id DESCLIMIT 21;

Main properties:

• Fast at any depth. With an index on (created_at DESC, id DESC), the database goes straight to the cursor position and reads 21 rows. The cost does not grow as the user pages further.
• Stable. New or deleted rows elsewhere do not move your position. You see no duplicates and no skips among the rows you have not yet seen.
• Trade-offs: you cannot jump to page N, there is no cheap total count, and you need a fixed sort order with a unique tiebreaker. A tiebreaker is an extra sort column that decides the order when the other sort values are equal.

A stable sort needs a unique tiebreaker

Say you sort by created_at only, and two rows have the same time. The database may return them in any order. Then the cursor cannot tell which one was seen last. Always add a unique column (usually the primary key) as the last sort key:

ORDER BY created_at DESC, id DESC

Without it, rows at the page edge can be skipped or repeated, even with keyset pagination.

The row-value comparison

(created_at, id) < ($1, $2) is a row-value comparison. It compares two pairs like words in a dictionary: look at the first value, and use the second only if the first values are equal. It means: "created_at is less than $1, or created_at equals $1 and id is less than $2". PostgreSQL, MySQL and SQLite (3.15 or newer) support it. PostgreSQL can use a composite index for it, and MySQL may need a check with EXPLAIN. If your database does not support it, write the longer form:

WHERE created_at < $1 OR (created_at = $1 AND id < $2)

(Some databases find it harder to use an index with the longer OR form. Test it with EXPLAIN.)

Fetch limit + 1 to know if there is a next page

Ask for 21 rows when the page size is 20. If you get 21 rows, there is another page. Return 20 rows and make nextCursor from the 20th. This way you need no extra COUNT query.

Cursor encoding

Make the cursor opaque. This means clients must treat it as a closed box and not look inside. Then you can change what is inside later. The example turns the cursor values into JSON text and then writes the text as base64url (a way to write any bytes with only letters, digits, - and _, so it is safe to put in a URL).

type Cursor = { c: string; i: number };            // created_at (ISO) and idexport const encodeCursor = (c: Cursor) => Buffer.from(JSON.stringify(c)).toString("base64url");export function decodeCursor(raw: string): Cursor {  try {    const v = JSON.parse(Buffer.from(raw, "base64url").toString("utf8"));    const isoTime = /^\d{4}-\d\d-\d\dT[\d:.]+Z$/;     // Date.parse alone accepts odd text such as "1"    if (typeof v.c !== "string" || !isoTime.test(v.c) || Number.isNaN(Date.parse(v.c)) || !Number.isInteger(v.i)) throw new Error();    return v;  } catch {    throw new ValidationError("Invalid cursor");   // your own error class: answer 400 or 422. Never trust a client-supplied cursor  }}

• Validate it strictly. A bad cursor must not cause SQL injection (an attack where an attacker puts SQL commands inside a value so your database runs them) or a crash. Use a parameterised query: the values are passed separately from the SQL text, so the database never reads them as commands.
• If it matters, sign the cursor. An HMAC is a short code made from the data and a secret key. Only your server can make a correct one, so any change to the cursor can be detected. Then clients cannot make up positions. For example, this stops them from probing hidden ranges.
• Put a fingerprint of the sort and filters in the cursor. A fingerprint is a short value, such as a hash, that identifies them. Then you can reject a cursor that is used with different filters. A cursor from one sort order means nothing in another.
• Keep the full precision of the sort value. A JavaScript Date holds milliseconds. Prisma's default DateTime column also keeps milliseconds, so the two match. If the column keeps microseconds, a rounded value in the cursor can skip or repeat rows. Then put the exact value in the cursor as text.

Doing it with Prisma

Prisma is an ORM (object-relational mapper): a library that lets you read and write a database from TypeScript code instead of raw SQL.

// Simple case: ordering by a unique, sequential column. afterId is the id from the cursor.const rowsById = await prisma.order.findMany({  take: limit + 1,  ...(afterId !== undefined ? { cursor: { id: afterId }, skip: 1 } : {}),   // skip the cursor row itself  orderBy: { id: "desc" },});// Non-unique sort key: use the composite condition explicitlyconst after = rawCursor ? decodeCursor(rawCursor) : null;const rows = await prisma.order.findMany({  where: after    ? {        OR: [          { createdAt: { lt: new Date(after.c) } },          { createdAt: new Date(after.c), id: { lt: after.i } },        ],      }    : undefined,  orderBy: [{ createdAt: "desc" }, { id: "desc" }],  take: limit + 1,});const hasMore = rows.length > limit;const page = hasMore ? rows.slice(0, limit) : rows;const last = page.at(-1);const nextCursor = hasMore && last ? encodeCursor({ c: last.createdAt.toISOString(), i: last.id }) : null;

Prisma's cursor option works with a unique field and the orderBy of the query. If you sort by a column that is not unique, the explicit composite where above is the safe pattern. Make sure the matching composite index exists:

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

Paging backwards and "previous"

Support before in the same way. Flip the comparison and the sort, then reverse the result. This query returns the rows nearest to the cursor first, so the extra 21st row is the farthest one. After the reverse it is the first row. Drop it, and use it only to know that an earlier page exists:

SELECT * FROM (  SELECT * FROM orders WHERE (created_at, id) > ($1, $2) ORDER BY created_at ASC, id ASC LIMIT 21) t ORDER BY created_at DESC, id DESC;

Return prevCursor (from the first row that is kept) and nextCursor (from the last row).

Filters and different sort orders

Every different ORDER BY needs an index that matches it. For example, use (status, created_at DESC, id DESC) for "orders with a given status, newest first". If users can sort by any column, you need an index for each one, or you must limit the allowed sorts. When the filters change, the old cursor is no longer valid. Start again from the first page.

When you sort by a computed or nullable column (a column that can be empty), set clear rules (NULLS LAST puts the empty values at the end) and use them in the comparison. Nullable sort keys cause many keyset bugs.

Relay-style connections (GraphQL)

GraphQL is a query language for APIs where the client asks for exactly the fields it needs. Relay, a GraphQL client from Meta, defined a standard for cursor pagination called connections:

query {  orders(first: 20, after: "eyJjIjoi...") {    edges { cursor node { id total status } }    pageInfo { hasNextPage hasPreviousPage startCursor endCursor }    totalCount           # optional and expensive; often omitted or approximated  }}

• first and after go forward. last and before go backward.
• Each edge (one item in the list) has its own cursor. Next time, you pass pageInfo.endCursor as after.
• This is exactly the keyset idea. It also fits client libraries (Apollo, Relay) that join pages in a cache (Backend, Lesson 09).

Total counts and page numbers

If the UI needs "page 3 of 57" or a total:

• COUNT(*) on large tables is slow, because the database must visit every matching row or index entry. Your options:
  • Approximate counts (PostgreSQL's reltuples, which is its stored estimate of the row count, or the estimate shown by EXPLAIN).
  • Cached counts (a number you saved earlier) that you refresh from time to time.
  • A counter table: a small table where you keep the count and add or subtract one when a row is inserted or deleted.
  • Show "10,000+" and stop counting at a limit (SELECT count(*) FROM (SELECT 1 FROM t WHERE … LIMIT 10001) AS capped).
• Or skip totals. Infinite scroll (the list grows as you scroll down) and "Load more" only need hasMore.

Choosing

Many APIs offer both. Or they document a maximum offset to protect the database.

Failure modes and protections

• Cap limit (for example, max 100) and set a default.
• Cap offset depth if you support offset.
• Validate cursors. Handle a cursor that points at a row that was deleted later. Keyset still works, because the comparison uses values, not the row itself.
• Consistent snapshots (rarely needed). For exports, use a snapshot timestamp, or run one long read in a repeatable-read transaction (a transaction is a group of database steps that run as one unit; repeatable-read means it sees the same data from start to end), or page by a fixed ID range.
• Index regressions (an index goes missing or stops being used). Check the plan with EXPLAIN (Backend, Lesson 07). A missing index turns keyset into a full sort.
• Documentation: say that the cursor is opaque, say what the order is, and say what happens when filters change.

A small experiment (runs anywhere with SQLite)

You can see the difference without a server. SQLite is a small database that runs inside your program. This Python script builds 50,000 rows. It pages with a keyset while it adds new rows, and it checks that there are no duplicates or skips:

import sqlite3db = sqlite3.connect(":memory:")db.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, created INTEGER)")db.executemany("INSERT INTO t (created) VALUES (?)", [(i // 3,) for i in range(1, 50001)])  # many ties on `created`db.execute("CREATE INDEX t_idx ON t (created DESC, id DESC)")seen, cursor = [], Nonewhile True:    if cursor is None:        rows = db.execute("SELECT id, created FROM t ORDER BY created DESC, id DESC LIMIT 1001").fetchall()    else:        rows = db.execute("SELECT id, created FROM t WHERE (created, id) < (?, ?) ORDER BY created DESC, id DESC LIMIT 1001", cursor).fetchall()    page = rows[:1000]    seen += [r[0] for r in page]    if len(rows) <= 1000: break    cursor = (page[-1][1], page[-1][0])    db.execute("INSERT INTO t (created) VALUES (999999)")     # new rows appear at the TOP while we pageassert len(seen) == len(set(seen)) == 50000, (len(seen), len(set(seen)))print("no duplicates, no skips:", len(seen))

New rows arrive at the top while we page. Even so, the keyset walk visits each original row exactly once. An offset version of the same loop would repeat rows after each insert.

Common mistakes

1. Offset pagination on large tables that change.
2. A cursor without a unique tiebreaker, so ties cause skips or duplicates.
3. A missing composite index, so keyset still sorts the whole table.
4. Trusting client cursors without checking them, or putting raw IDs in an easy-to-guess cursor with no checks.
5. A limit with no maximum.
6. Running COUNT(*) on every request.
7. Using a cursor again after the sort or filter changed.
8. Handling nullable sort columns in different ways in different places.

Interview questions

Key takeaways

• Offset = position. It is simple and lets you jump, but the cost grows with the offset (O(offset)) and it is unstable when data changes.
• Keyset = anchor. It is fast at any depth and stable. It needs a unique tiebreaker, a composite index and an opaque, validated cursor.
• Fetch limit + 1 to work out hasMore. Cap limit. Avoid expensive totals.
• GraphQL connections are keyset pagination in a standard shape. Search engines use search_after.
• Choose by data size, how fast data changes, and what the UX needs. Test the query plan.

What's next

Lesson 04: Design File Upload. Pagination moves data out of the API. Uploads move big data in. The main lesson is to keep those bytes away from your servers.