How I Replaced Offset Pagination With Cursor Pagination in Prisma and Postgres

For a long time every list in my apps used the same pagination: a page query param, skip and take in Prisma, and a row of numbered buttons at the bottom. It worked fine until one table crossed a few hundred thousand rows and an infinite-scroll feed started doing two strange things: deep pages got noticeably slower, and users occasionally saw the same post twice while scrolling. Both problems had the same root cause, and the fix was switching to cursor (keyset) pagination. Here is how I did it with Prisma and Postgres, including the parts the quick-start examples skip.

The offset version and why it degrades

This is roughly what I had:

// app/api/posts/route.ts — the version I started with
const page = Number(searchParams.get("page") ?? 1);
const pageSize = 20;
const posts = await prisma.post.findMany({
  orderBy: { createdAt: "desc" },
  skip: (page - 1) * pageSize,
  take: pageSize,
});

Under the hood Prisma turns that into LIMIT and OFFSET. On page 5,001 the SQL looks like this:

SELECT * FROM "Post"
ORDER BY "createdAt" DESC
LIMIT 20 OFFSET 100000;

The important thing to understand is that Postgres cannot jump to row 100,000. Even with an index on createdAt, it walks the index from the start, produces 100,000 rows, throws them away, and then returns the next 20. The cost grows linearly with the offset. The Prisma docs say this directly: offset pagination does not scale at the database level, because skipping a large number of records still forces the database to traverse them.

The duplicate posts were the second problem. Offset is a position, not a bookmark. If three new posts are inserted at the top of the feed between the user loading page 1 and page 2, every row shifts down by three, so the first three items of page 2 are the last three items of page 1 again. Deletions cause the opposite problem and silently skip rows. For a feed that is constantly changing, that is a real bug, not just a cosmetic one.

The idea behind cursor pagination

Instead of saying "give me rows 100,000 to 100,020", cursor pagination says "give me the 20 rows that come after this specific row". You remember the sort key of the last item the client saw and use it in a WHERE clause. With a matching index, Postgres can seek straight to that position in the B-tree and read 20 entries, so page 5,001 costs about the same as page 1. And because the bookmark is a value, not a position, inserts at the top of the list no longer shift what comes next.

The trade-off is that you lose random access. You can go "next" (and "previous" with a bit more work), but you cannot jump to page 47. For feeds, infinite scroll, activity logs and API exports, that is almost always fine. For an admin table where someone genuinely needs page numbers, offset is still a reasonable choice on smaller datasets.

Prisma's built-in cursor option

Prisma has a first-class cursor argument on findMany. The documented pattern looks like this:

// First page
const firstPage = await prisma.post.findMany({
  take: 20,
  orderBy: { id: "asc" },
});
// Next page: start at the last id we saw, but skip that row itself
const lastId = firstPage[firstPage.length - 1].id;
const nextPage = await prisma.post.findMany({
  take: 20,
  skip: 1,
  cursor: { id: lastId },
  orderBy: { id: "asc" },
});

The skip: 1 is there because the cursor row itself is included in the result by default, and you already showed it on the previous page. This works well when you sort by a unique, sequential column such as an auto-increment id.

My feed, however, sorts by createdAt descending, and my ids are cuids, which are not something I want to sort a timeline by. createdAt alone is also not unique: two posts created in the same millisecond (a bulk import does this constantly) make the ordering ambiguous, and an ambiguous ordering is exactly how you get skipped or repeated rows at page boundaries. So I wrote the keyset condition myself with a compound sort key: createdAt first, id as a tiebreaker.

Step 1: an index that matches the sort

Keyset pagination is only fast if Postgres can read the rows in order straight from an index. I added a composite index whose columns match the ORDER BY:

model Post {
  id        String   @id @default(cuid())
  title     String
  createdAt DateTime @default(now())
  @@index([createdAt(sort: Desc), id(sort: Desc)])
}

After prisma migrate dev, Postgres has an index on ("createdAt" DESC, "id" DESC). A B-tree can be scanned in either direction, so for a two-column sort where both columns go the same way, an ascending index would also work. I declare the direction anyway so the schema documents the intended access pattern.

Step 2: an opaque cursor and the keyset query

I encode the cursor as base64url JSON so clients treat it as an opaque token rather than something to construct by hand. That also lets me change what is inside it later without breaking the API contract.

// lib/posts.ts
import { prisma } from "@/lib/prisma";
type Cursor = { createdAt: string; id: string };
export function encodeCursor(c: Cursor): string {
  return Buffer.from(JSON.stringify(c)).toString("base64url");
}
export function decodeCursor(raw: string | null): Cursor | null {
  if (!raw) return null;
  try {
    const parsed = JSON.parse(Buffer.from(raw, "base64url").toString("utf8"));
    if (typeof parsed?.createdAt !== "string" || typeof parsed?.id !== "string") {
      return null;
    }
    return parsed;
  } catch {
    return null;
  }
}
export async function getPostsPage(rawCursor: string | null, pageSize = 20) {
  const cursor = decodeCursor(rawCursor);
  const rows = await prisma.post.findMany({
    where: cursor
      ? {
          OR: [
            { createdAt: { lt: new Date(cursor.createdAt) } },
            { createdAt: new Date(cursor.createdAt), id: { lt: cursor.id } },
          ],
        }
      : undefined,
    orderBy: [{ createdAt: "desc" }, { id: "desc" }],
    take: pageSize + 1, // fetch one extra row to know if there is a next page
  });
  const hasMore = rows.length > pageSize;
  const items = hasMore ? rows.slice(0, pageSize) : rows;
  const last = items[items.length - 1];
  return {
    items,
    nextCursor:
      hasMore && last
        ? encodeCursor({ createdAt: last.createdAt.toISOString(), id: last.id })
        : null,
  };
}

A few details here are doing real work:

• The OR condition is the keyset predicate for a descending two-column sort: "strictly older than the cursor, or the same timestamp with a smaller id". This is what makes ties on createdAt safe.
• take: pageSize + 1 fetches one extra row so I know whether another page exists without running a separate COUNT(*), which on a big table is often more expensive than the page query itself.
• The orderBy must exactly mirror the cursor condition. If you sort by createdAt only but filter on (createdAt, id), you are back to an ambiguous ordering.
• Invalid cursors return null, which falls back to the first page instead of throwing a 500 because someone edited the URL.

Step 3: wiring it into a route and the UI

The route handler stays tiny:

// app/api/posts/route.ts
import { NextRequest, NextResponse } from "next/server";
import { getPostsPage } from "@/lib/posts";
export async function GET(req: NextRequest) {
  const cursor = req.nextUrl.searchParams.get("cursor");
  const page = await getPostsPage(cursor);
  return NextResponse.json(page);
}

And a minimal "Load more" client component just appends each page and stores the next cursor:

"use client";
import { useState } from "react";
type Post = { id: string; title: string };
export function PostFeed({ initial }: { initial: { items: Post[]; nextCursor: string | null } }) {
  const [posts, setPosts] = useState(initial.items);
  const [cursor, setCursor] = useState(initial.nextCursor);
  const [loading, setLoading] = useState(false);
  async function loadMore() {
    if (!cursor || loading) return;
    setLoading(true);
    try {
      const res = await fetch(`/api/posts?cursor=${encodeURIComponent(cursor)}`);
      const data = await res.json();
      setPosts((prev) => [...prev, ...data.items]);
      setCursor(data.nextCursor);
    } finally {
      setLoading(false);
    }
  }
  return (
    <>
      <ul>{posts.map((p) => <li key={p.id}>{p.title}</li>)}</ul>
      {cursor && (
        <button onClick={loadMore} disabled={loading}>
          {loading ? "Loading..." : "Load more"}
        </button>
      )}
    </>
  );
}

In my real app the first page is rendered on the server by calling getPostsPage(null) directly in a Server Component and passing the result in as initial, so the feed shows content without waiting for a client-side fetch.

Checking the query plan

I never trust that an index is being used until I see it. I copied the generated SQL from Prisma's query log and ran it with EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT * FROM "Post"
WHERE ("createdAt" < $1) OR ("createdAt" = $1 AND "id" < $2)
ORDER BY "createdAt" DESC, "id" DESC
LIMIT 21;

What I want to see is an Index Scan on the composite index with a LIMIT on top, and no separate Sort node. If you see a Sort over a large number of rows, the index columns or directions do not match your ORDER BY. I would encourage you to run this on your own data rather than trusting any numbers in a blog post, since the plan depends on table size, statistics and your Postgres version.

If you are comfortable dropping to raw SQL with prisma.$queryRaw, Postgres also supports row-value comparison, which expresses the same predicate more compactly and plays nicely with a composite index: WHERE ("createdAt", "id") < ($1, $2). I stuck with the Prisma query builder version to keep type safety, and it has worked well for me.

Gotchas I hit along the way

• Timestamp precision. Postgres timestamp columns store microseconds, but JavaScript Date only holds milliseconds. If your rows are written by something other than Prisma (a database default like now() or another service), the value you round-trip through the cursor can be truncated, so the equality branch of the predicate may not match the original row. Prisma's @default(now()) generates the timestamp in the client, but if you rely on database-generated timestamps, consider declaring the column as @db.Timestamp(3) so precision matches on both sides.
• Filters belong in the same index. If the feed is always filtered by, say, authorId, the index should be (authorId, createdAt, id) so Postgres can seek directly to that author's slice.
• Total counts are a different feature. Product folks often ask for "Showing 20 of 312,450". If you really need it, compute it separately and cache it, or show an estimate. Don't let it drag every page request back to a full scan.
• "Previous page" means flipping the comparison and the sort, then reversing the results in memory. It is doable, but if your UI is infinite scroll you probably don't need it.

Key takeaways

• OFFSET makes Postgres walk and discard every skipped row, so deep pages get slower as the table grows.
• Offset pages are positions, so inserts and deletes cause duplicated or missing items in live feeds; cursors are bookmarks and avoid that.
• Always paginate on a unique, deterministic sort key; add id as a tiebreaker when sorting by a timestamp.
• Back the sort with a composite index that matches the ORDER BY, and confirm it with EXPLAIN ANALYZE.
• Fetch pageSize + 1 rows to detect the next page, return an opaque cursor, and handle invalid cursors gracefully.