Cursor pagination

You can already page through a list: orderBy, then limit(20).offset(40) for page three. Offset is the simple default, but it has a ceiling, and the primary-key tiebreaker the first lesson paired with your sort column comes due here too.

Offset works until a list grows large or changes while the user pages through it: an invoice list that piles up month after month, or an activity feed written to while someone scrolls. Then it breaks two ways at once. It gets slow the deeper you page, and it shows the same row twice or skips one entirely. A small admin table never reveals either.

The fix for both is cursor pagination : instead of counting from the front, you remember the last row a page returned and ask for the rows after it. Offset doesn’t disappear, it stays right for small, stable tables, but it stops being your default.

Where offset breaks

Offset in one sentence: sort the rows, skip the first N, take the next page’s worth.

const pageThree = await db
  .select()
  .from(invoices)
  .where(eq(invoices.organizationId, orgId))
  .orderBy(desc(invoices.createdAt), desc(invoices.id))
  .limit(20)
  .offset(40);

Skip forty rows, take twenty: page three. It reads the way you’d say it aloud, and for a few hundred rows nobody pages deep into, it’s the right tool. But two failures hide behind that clean syntax, and neither shows up on your laptop with ten seeded rows.

Failure one: it gets slow the deeper you page

OFFSET 10000 looks cheap, because you asked for only the twenty rows after the first ten thousand. The database doesn’t get to skip them. To return rows 10,001 through 10,020 in sorted order, Postgres has to build the first 10,000 in that order, walk past every one, and throw them away. The cost of a page grows with how deep it sits: page one is instant, page five hundred builds and discards ten thousand rows to hand you twenty.

A cursor doesn’t count from the front, so it never pays this cost.

Offset

scanned, then discarded

the page you wanted

Cursor

seek straight to the boundary

Offset builds and discards every row before the page; a cursor seeks straight to the boundary. The deeper the page, the more offset throws away, while the cursor's cost stays flat.

Failure two: it shows the wrong rows on live data

This failure has nothing to do with size. It hits a small list the moment that list changes while someone reads it.

Offset addresses rows by their position in the current ordering: “give me rows 41 through 60.” But position is relative to what’s in the table right now. Say a user loads page one, the twenty newest invoices, and clicks “next.” Between the two clicks a new invoice is created and, since the list is newest first, lands at the top. Every row shifts down one: the invoice that was row 20 is now row 21. So page two’s “rows 21 through 40” starts with a row the user already saw at the bottom of page one. They see it twice. A delete above their position does the mirror-image damage: every row shifts up, and one row falls into the gap between the pages, skipped entirely.

1. page 1 — returned
   1

   #105
2. 2

   #104
3. 3

   #103
4. 4

   #102
5. 5

   #101
6. 6

   #100

Page 1 loads: the three newest invoices, #105 #104 #103. The user reads them.

1. 1

   #106 new
2. 2

   #105
3. 3

   #104
4. 4

   #103
5. 5

   #102
6. 6

   #101
7. 7

   #100

Between clicks, a new invoice #106 is created. Newest-first, it lands at the very top — and pushes every row below it down one position.

1. skipped (offset 3)
   1

   #106
2. 2

   #105
3. 3

   #104
4. page 2 will return these
   4

   #103
5. 5

   #102
6. 6

   #101
7. 7

   #100

The user clicks ‘next’. Page 2 asks for rows 4–6 by position: offset 3, limit 3. Position 4 is now #103.

1. 1

   #106
2. 2

   #105
3. 3

   #104
4. page 2's window
   4

   #103 also page 1
5. 5

   #102
6. 6

   #101
7. 7

   #100

But #103 was the last row of page 1. The shift pushed it from position 3 into position 4 — squarely inside page 2's window.

Page 1

#105

#104

#103 duplicate

Page 2

#103 duplicate

#102

#101

Result: the user sees #103 twice — once at the bottom of page 1, once at the top of page 2. (A delete above them would skip a row instead.)

Prev

Next

The fix is to address rows relative to the last one the user actually saw; a new invoice at the top can’t change which row that was. That anchor is a cursor , the idea the rest of the lesson builds on:

A cursor remembers the last row you saw and asks for the rows after it; offset counts from the front and re-counts every time.

When to keep using offset

The opposite mistake is reaching for a cursor on every list. Offset isn’t broken; it just fits a narrower set of cases than people assume. It’s the right tool when all three hold:

• The list is small and bounded. A few hundred feature flags that won’t grow without limit, never paged deep enough for the re-scan to cost anything.
• The data is read-mostly. It isn’t mutating under the user as they page, so nothing shifts position.
• The UI wants random page access or a total count. Offset pairs naturally with “page 3 of 12”: numbered pages, jump-to-page, a known total. A cursor only knows whether there’s a next page.

Two triggers end offset’s safety: a list that could grow past the low thousands, where re-scan cost adds up, or one users see while it’s written to, like a feed, inbox, or activity log, where rows shift position. Most SaaS list endpoints cross at least one, so cursor is the default and offset the justified exception.

Sort each list into the pagination it should use. Drag each item into the bucket it belongs to, then press Check.

Offset is fine Small, bounded, read-mostly

Reach for a cursor Grows past low thousands, or mutates under the user

An internal admin table of ~200 feature flags, edited rarely

A customer-facing activity feed, thousands of rows, written to live

A report with a fixed “page 4 of 9” control over a static nightly export

The invoices list — grows every month, shows newly created invoices at the top

A one-page settings screen of ~30 rows, no paging UI at all

An inbox the user reads while new messages keep arriving

Check Reset

The cursor query

The core idea: give me the rows after the last one I saw.

The naive single-key cursor

The invoices list is newest-first, sorted by createdAt. The last row of the current page has some createdAt, and the next page is the rows after it. Since the list descends, “after” means “older,” so the obvious filter takes rows whose createdAt is less than the cursor’s:

const page = await db
  .select()
  .from(invoices)
  .where(
    and(
      eq(invoices.organizationId, orgId),
      cursor ? lt(invoices.createdAt, cursor.createdAt) : undefined,
    ),
  )
  .orderBy(desc(invoices.createdAt))
  .limit(pageSize);

Two details. The cursor is an object holding the last row’s sort value, just { createdAt } for now. On the very first page there’s no last row, so cursor is undefined, Drizzle drops that condition, and you get “newest, limited.” (organizationId is always in the where; more on why it lives there, not in the cursor, below.)

This works until two rows share a timestamp, and then it silently loses a row.

The tie that skips a row

Two invoices can share a createdAt: a bulk import writes a batch in the same instant, or a traffic burst stamps two rows the same millisecond. At real volume, collisions happen.

Suppose the page boundary lands between two invoices with identical createdAt: one ends this page, the other should start the next. The next-page query asks for lt(createdAt, boundary), rows strictly older. But the row that should start the next page shares the cursor’s timestamp rather than being older, so it fails the filter and never comes back. The user pages right past it, with no error.

The fix is the habit from the first lesson: compare on the pair (createdAt, id), not on createdAt alone. The primary key is unique, so two rows can tie on createdAt but never on id. The pair gives every row one unambiguous position, so the boundary is never stuck between two indistinguishable rows. The predicate “the pair (createdAt, id) is less than (cursor.createdAt, cursor.id)” expands to:

or(
  lt(invoices.createdAt, cursor.createdAt),
  and(eq(invoices.createdAt, cursor.createdAt), lt(invoices.id, cursor.id)),
)

In words: rows strictly older than the cursor’s timestamp, OR rows with the same timestamp but a smaller id. The first branch handles the common case; the second reaches into the tied group for exactly the rows the single-key version dropped. This is the compound-cursor shape from Drizzle’s pagination guide, in its descending form (lt, not gt) because the list runs newest-first.

Matching the sort to the cursor

The predicate decides which rows are “after the cursor” by comparing (createdAt, id) as a pair, descending. For that boundary to mean anything, the rows must come back in that same order; otherwise “after” is defined against one ordering while the result follows another. So the orderBy carries the same two columns in the same two directions:

.orderBy(desc(invoices.createdAt), desc(invoices.id))

The rule to hold onto: the cursor predicate and the orderBy are one design, same columns and same directions, or the boundary is meaningless. Here’s the whole query assembled, with the where stepped through slowly.

const page = await db
  .select()
  .from(invoices)
  .where(
    and(
      eq(invoices.organizationId, orgId),
      cursor
        ? or(
            lt(invoices.createdAt, cursor.createdAt),
            and(
              eq(invoices.createdAt, cursor.createdAt),
              lt(invoices.id, cursor.id),
            ),
          )
        : undefined,
    ),
  )
  .orderBy(desc(invoices.createdAt), desc(invoices.id))
  .limit(pageSize);

The main branch: rows strictly older than the cursor’s timestamp. Almost every row of the next page comes back through here. On its own, though, it silently drops any row that ties the cursor’s timestamp.

const page = await db
  .select()
  .from(invoices)
  .where(
    and(
      eq(invoices.organizationId, orgId),
      cursor
        ? or(
            lt(invoices.createdAt, cursor.createdAt),
            and(
              eq(invoices.createdAt, cursor.createdAt),
              lt(invoices.id, cursor.id),
            ),
          )
        : undefined,
    ),
  )
  .orderBy(desc(invoices.createdAt), desc(invoices.id))
  .limit(pageSize);

The tiebreaker branch: same createdAt, smaller id. Without it, two invoices sharing a timestamp at the page seam mean the next page skips one. The unique id breaks the tie so no row falls through.

const page = await db
  .select()
  .from(invoices)
  .where(
    and(
      eq(invoices.organizationId, orgId),
      cursor
        ? or(
            lt(invoices.createdAt, cursor.createdAt),
            and(
              eq(invoices.createdAt, cursor.createdAt),
              lt(invoices.id, cursor.id),
            ),
          )
        : undefined,
    ),
  )
  .orderBy(desc(invoices.createdAt), desc(invoices.id))
  .limit(pageSize);

The or unions the two branches: strictly after on the sort key, or tied on it but after on the id. Together they describe exactly “every row after this specific row,” a gap-free boundary.

const page = await db
  .select()
  .from(invoices)
  .where(
    and(
      eq(invoices.organizationId, orgId),
      cursor
        ? or(
            lt(invoices.createdAt, cursor.createdAt),
            and(
              eq(invoices.createdAt, cursor.createdAt),
              lt(invoices.id, cursor.id),
            ),
          )
        : undefined,
    ),
  )
  .orderBy(desc(invoices.createdAt), desc(invoices.id))
  .limit(pageSize);

Same two columns, same descending directions as the predicate. The boundary only makes sense if the rows come back in the order the comparison assumes, so the where and the orderBy are a matched pair.

← Previous 1 / 1 Next →

Two more mistakes sit close to this query, both separate from the missing tiebreaker.

Sort only on a stable key. A cursor works only if a row’s sort value can’t change mid-pagination. createdAt is set once at insert and never touched. updatedAt is the opposite: mark an invoice paid while a user is paging, and its updatedAt jumps to now, moving the row to the top under newest-first, back across a boundary the user already passed. They see it twice or never. A mutable sort column brings back the exact instability the cursor was meant to escape, so sort on creation time or another immutable key, never on a “last modified” column.

The tenant scope rides on the request, not the cursor. The where still carries eq(invoices.organizationId, orgId), but orgId comes from your server auth context, not the cursor. The cursor is a token you hand the client and the client hands back; bake the tenant id into it and a user could decode it, swap in another organization’s id, and read that tenant’s data. So the cursor carries only the sort key and tiebreaker, { createdAt, id }: the request authenticates the tenant, the cursor marks a position within it.

This compound predicate has a formal name you’ll meet in the docs: keyset pagination .

Practice: the compound cursor

Now write the predicate that does the real work. The table is seeded newest-first, and two invoices share the exact same created_at, straddling the page boundary. A naive lt(createdAt, …) drops one of them; only the compound or(lt(createdAt), and(eq(createdAt), lt(id))) returns the full next page. Write the where.

Page 1 ended on the invoice in cursor, and its created_at is shared with another, lower-id invoice that belongs on page 2. Finish the where so it returns every row after cursor under newest-first order with id as the tiebreaker: rows with an older created_at, OR the same created_at but a smaller id. The orderBy is already wired to match.

View schema & seed rows

Schema (Drizzle)

export const organizations = pgTable('organizations', {
  id: integer('id').primaryKey(),
  name: text('name').notNull(),
});

export const invoices = pgTable('invoices', {
  id: integer('id').primaryKey(),
  organizationId: integer('organization_id')
    .references(() => organizations.id)
    .notNull(),
  amountDue: numeric('amount_due', { precision: 12, scale: 2 }).notNull(),
  status: text('status').notNull(),
  createdAt: timestamp('created_at', { mode: 'string' }).notNull(),
});

Seed rows (SQL)

INSERT INTO organizations (id, name) VALUES (1, 'Acme');

INSERT INTO invoices (id, organization_id, amount_due, status, created_at) VALUES
  (10, 1, '100.00', 'sent', '2026-05-10 10:00:00Z'),
  (9,  1, '200.00', 'sent', '2026-05-09 10:00:00Z'),
  (8,  1, '300.00', 'sent', '2026-05-08 10:00:00Z'),
  (7,  1, '400.00', 'sent', '2026-05-08 10:00:00Z'),
  (6,  1, '500.00', 'sent', '2026-05-07 10:00:00Z'),
  (5,  1, '600.00', 'sent', '2026-05-06 10:00:00Z');

Run query Feedback Reset

Encoding the cursor as a URL token

The query needs a { createdAt, id } object, but that object lives on the server, while the next page is a fresh request from the client. So the cursor has to round-trip: out to the client in the response, then back to the server in the next request’s URL.

It travels as an opaque token — an encoded string the client copies back without parsing. Keep it opaque so the client never depends on your field names or sort columns; the moment it can read or build cursors, you can’t change the ordering without breaking every caller. The server still decodes it; opaque means hidden, not encrypted.

The encoding is two steps, serialize to JSON then base64url it:

const token = Buffer.from(JSON.stringify(cursor)).toString('base64url');

The base64url variant drops straight into a query string with nothing to escape, unlike plain base64, whose +, /, and = all mean something in a URL. The result reads ?cursor=eyJjcmVhdGVkQXQ.... Page size travels as its own param, ?pageSize=20, with a server-side cap and default.

Coming back in, decode the token and then validate before you trust it. The cursor is user-controlled: malformed, stale from an old sort, or hand-crafted by someone poking at your API. Reject a bad one as bad input, the way a 400 rejects any malformed request — not swallowed silently, not left to crash deep in the query layer.

const decoded = JSON.parse(Buffer.from(raw, 'base64url').toString());
const result = cursorSchema.safeParse(decoded);

if (!result.success) {
  return err('validation', 'Invalid cursor');
}
const cursor = result.data; // { createdAt: string; id: number } — safe to query with

Two names are forward references. cursorSchema.safeParse is Zod (forms chapter) — read it as “confirm the decoded object really is { createdAt, id }.” And err('validation', …) returns a failure instead of throwing, so a bad cursor travels back as a typed error the caller turns into a 400 (error-handling chapter).

A cursor is just another request parameter, so it earns the same boundary check as any query string or body field — and decoding it opens no SQL-injection door, since createdAt and id flow into lt(...) and eq(...) as bound $1 placeholders, never spliced into SQL text. Untrusted but parameterized.

In practice these two steps become an encodeCursor/parseCursor pair in lib/ rather than inlined: encode on the way out, decode and validate on the way in.

Knowing there’s a next page

A paginated UI needs one more fact: is this the last page? A separate count() query would answer it, but spends a round-trip on a yes/no. One extra row answers it cheaper.

Ask for one row more than the page needs with limit(pageSize + 1), then read the result by its length. If you get pageSize + 1 rows, a next page exists; slice off the extra row, which belongs to that next page. If you get fewer, you’ve reached the end. The probe row is never shown; its existence is the whole signal.

const rows = await query.limit(pageSize + 1);

const hasNextPage = rows.length > pageSize;
const items = rows.slice(0, pageSize);
const nextCursor = hasNextPage ? encodeCursor(items.at(-1)) : null;

return { items, nextCursor };

The next cursor is the key of the last row you return, items.at(-1) after the slice, never the probe row. Encoded, it becomes the ?cursor= for the next request; when there’s no next page it’s null and the UI stops. The shape { items, nextCursor } is what the code conventions specify for a paginated read.

row 1

row 2

row 3

row 4

next cursor

row 5

row 6

returned (the page) handed back: 5 rows

probe sliced off — “there’s a next page”

Fetch pageSize + 1. If the probe row comes back, there's a next page: slice it off and return the first pageSize. The next cursor is the key of the last row you return, never the probe.

The index this depends on

Everything so far has been about correctness: the right rows, in the right order, no skips or duplicates. But correctness rests on a performance condition that must ship alongside the query. Cursor pagination is fast only when an index covers the cursor’s columns in the cursor’s sort direction. Without it, “seek to the boundary” degrades into a sequential scan , and a cursor that scans is slower than the offset it replaced: all the complexity, none of the payoff.

Here’s the shape of index the query needs:

index('idx_invoices_org_created_at_id').on(
  invoices.organizationId,
  invoices.createdAt.desc(),
  invoices.id.desc(),
)

The tenant scope leads, because organizationId is an equality filter (eq). Then come the two cursor columns, createdAt and id, in the same descending order the orderBy uses. With all three in one index, Postgres applies the org filter, produces rows already in sort order, and seeks straight to the cursor boundary, no scanning. That match across the index, the where, and the orderBy is the whole reason the seek is cheap.

Note

This lesson owns correct; the next chapter owns fast. Declaring indexes, choosing their columns and direction, and reading EXPLAIN ANALYZE to confirm the database really seeks belong there. Here you only need to know the cursor query has a hard index dependency and what that index looks like, so query and index ship together.

The same query through the relational API

Cursor pagination is a query shape, not a feature of one API. This chapter gave you two ways to read, the SQL builder (db.select) and the relational query builder (db.query), and the cursor rides on whichever the feature already uses. Here’s the same (createdAt, id) boundary, desc/desc order, and pageSize + 1 probe, written both ways.

SQL builder

const rows = await db
  .select()
  .from(invoices)
  .where(
    and(
      eq(invoices.organizationId, orgId),
      cursor
        ? or(
            lt(invoices.createdAt, cursor.createdAt),
            and(
              eq(invoices.createdAt, cursor.createdAt),
              lt(invoices.id, cursor.id),
            ),
          )
        : undefined,
    ),
  )
  .orderBy(desc(invoices.createdAt), desc(invoices.id))
  .limit(pageSize + 1);

Reach here when the read is flat. For a plain list of invoices, already a db.select, the cursor is just the compound where plus the + 1 probe. The boundary is the or(...) block; the orderBy carries the same pair in the same direction.

Relational query builder

const rows = await db.query.invoices.findMany({
  where: {
    organizationId: orgId,
    RAW: (t) =>
      sql`(
        ${t.createdAt} < ${cursor.createdAt}
        or (${t.createdAt} = ${cursor.createdAt} and ${t.id} < ${cursor.id})
      )`,
  },
  orderBy: { createdAt: 'desc', id: 'desc' },
  limit: pageSize + 1,
});

Reach here when the page rows are a tree. When each row carries its relations, like an invoice with its line items, you paginate the parents through db.query. The filter object can’t express the compound boundary, so it drops to the RAW callback while the tenant scope and orderBy stay in object syntax. On the first page, leave the RAW key off.

The only difference is the predicate: the SQL builder takes operator helpers in where(...), the relational builder takes a filter object and drops to RAW for the one predicate it can’t express. There’s no “which pagination API” decision to make. You paginate whatever read the feature already does: a flat list through db.select, a tree through db.query.

What cursor pagination gives up

A cursor buys speed and stability by giving up two things. Knowing them keeps you from over-engineering to win back what the product doesn’t need.

No total count, no “page 3 of 12.” A cursor knows whether a next page exists, not how many pages there are. That rules out numbered pages, which is why most 2026 web app lists drop them for a “Load more” button or infinite scroll, with no count or an approximate one (“200+”). An exact total is a separate count() query you run only where it earns its keep.

Forward-only, by default. The cursor you built pages forward. “Previous page” is usually re-anchoring from a position the UI already kept, or just the browser’s back button. True bidirectional cursors exist, a reversed predicate over a reversed order with the rows flipped back at the end, but in early web apps they rarely earn the cost.

The cursor lives in the URL’s search params, which makes a paged view shareable and refresh-proof. You met that primitive reading and validating ?cursor= in the Unit 4 URL-state lesson; wiring it to a live, navigable list comes in the Unit 10 list-view chapters.

Watch video

PlanetScale walks the whole arc in 13 minutes: the ID tiebreaker, offset’s shift-on-delete and deep-page cost, and the base64 cursor token.

Keep these close

Drizzle — Cursor-based pagination

orm.drizzle.team

The official compound-cursor recipe this lesson follows: the or(gt, and(eq, gt)) predicate, the matching orderBy, and the required composite index.

Drizzle — Select

orm.drizzle.team

The where / orderBy / limit reference the SQL-builder cursor query is built from.

External resources

Use The Index, Luke! — No Offset

use-the-index-luke.com

Markus Winand on why offset is slow and unstable, and how the keyset seek the next chapter indexes for avoids both.

Cursor Pagination, EXPLAIN ANALYZE'd

youtube.com

Milan Jovanović builds the exact (date, id) compound cursor this lesson teaches, then proves the index dependency with EXPLAIN ANALYZE — seq-scan vs index-scan on a million rows.