API Pagination — Offset, Cursor, and Keyset Patterns Compared

Every API that returns a list needs pagination. Without it, you're either returning 50,000 rows in a single response (goodbye server memory) or arbitrarily truncating results (goodbye user trust).

But there's no single "correct" way to paginate. The three main approaches -- offset, cursor, and keyset -- have different performance characteristics, different API ergonomics, and different failure modes. Most teams pick offset because it's familiar, then discover its problems at scale. Let's understand all three so you can pick the right one upfront.

Offset Pagination

The classic. You've seen this everywhere:

GET /api/posts?page=1&limit=20
GET /api/posts?page=2&limit=20
GET /api/posts?offset=0&limit=20
GET /api/posts?offset=20&limit=20

Implementation

SQL:

SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;  -- Page 3

Express + Prisma:

app.get("/api/posts", async (req, res) => {
  const page = parseInt(req.query.page as string) || 1;
  const limit = Math.min(parseInt(req.query.limit as string) || 20, 100);
  const offset = (page - 1) * limit;

const [posts, total] = await Promise.all([
    prisma.post.findMany({
      orderBy: { createdAt: "desc" },
      skip: offset,
      take: limit,
    }),
    prisma.post.count(),
  ]);

res.json({
    data: posts,
    pagination: {
      page,
      limit,
      total,
      totalPages: Math.ceil(total / limit),
      hasNext: page * limit < total,
      hasPrev: page > 1,
    },
  });
});

Response:

{
  "data": [...],
  "pagination": {
    "page": 2,
    "limit": 20,
    "total": 1543,
    "totalPages": 78,
    "hasNext": true,
    "hasPrev": true
  }
}

The Problem

Offset pagination has a fundamental performance issue: the database still reads and discards all the rows before the offset.

-- Page 1: reads 20 rows ✓
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 0;

-- Page 100: reads 2000 rows, discards 1980, returns 20
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 1980;

-- Page 5000: reads 100,000 rows, discards 99,980, returns 20
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 99980;

The further you paginate, the slower it gets. On a table with millions of rows, page 5000 might take seconds.

The second problem: shifting windows. If a new post is inserted while someone is browsing page 2, every subsequent page shifts by one. The user sees the same item twice (on page 2 and page 3) and misses one item entirely. For social feeds or real-time data, this is unacceptable.

When Offset Works Fine

• Small to medium datasets (under 100K rows)
• Admin dashboards where users rarely go past page 10
• Search results where deep pagination doesn't happen
• Situations where you need "jump to page 50"

Cursor Pagination

Cursor pagination uses an opaque token that points to a specific position in the dataset:

GET /api/posts?limit=20
GET /api/posts?limit=20&after=eyJpZCI6NDU2fQ==

The cursor is typically a Base64-encoded reference to the last item on the current page.

Implementation

app.get("/api/posts", async (req, res) => {
  const limit = Math.min(parseInt(req.query.limit as string) || 20, 100);
  const after = req.query.after as string | undefined;

let cursor: { id: number } | undefined;
  if (after) {
    const decoded = JSON.parse(Buffer.from(after, "base64url").toString());
    cursor = { id: decoded.id };
  }

const posts = await prisma.post.findMany({
    take: limit + 1, // Fetch one extra to check if there's a next page
    ...(cursor && {
      skip: 1, // Skip the cursor item itself
      cursor: { id: cursor.id },
    }),
    orderBy: { id: "desc" },
  });

const hasNext = posts.length > limit;
  if (hasNext) posts.pop(); // Remove the extra item

const endCursor = posts.length > 0
    ? Buffer.from(JSON.stringify({ id: posts[posts.length - 1].id })).toString("base64url")
    : null;

const startCursor = posts.length > 0
    ? Buffer.from(JSON.stringify({ id: posts[0].id })).toString("base64url")
    : null;

res.json({
    data: posts,
    pageInfo: {
      hasNextPage: hasNext,
      hasPreviousPage: !!after,
      startCursor,
      endCursor,
    },
  });
});

Response:

{
  "data": [...],
  "pageInfo": {
    "hasNextPage": true,
    "hasPreviousPage": true,
    "startCursor": "eyJpZCI6NDc3fQ",
    "endCursor": "eyJpZCI6NDU4fQ"
  }
}

This is the pattern GitHub, Slack, Stripe, and most modern APIs use. It follows the GraphQL Relay Connection specification, even in REST APIs.

Why Cursors Are Better at Scale

The database query with a cursor doesn't use OFFSET:

-- Cursor-based: always fast, regardless of position
SELECT * FROM posts
WHERE id < 458
ORDER BY id DESC
LIMIT 20;

This query uses an index seek. Whether you're on "page 1" or "page 5000," performance is identical. The database jumps directly to the cursor position and reads 20 rows forward.

No shifting window problem either. Even if new items are inserted, the cursor still points to the same position. You'll never see duplicates or miss items.

Cursor Drawbacks

• No "jump to page 50" -- you can only go forward (or backward with a before cursor)
• Total count is expensive to compute (and often omitted)
• Not suitable for UIs with numbered page buttons
• Slightly more complex to implement

Keyset Pagination

Keyset pagination is what cursor pagination does under the hood, but exposed directly:

GET /api/posts?limit=20
GET /api/posts?limit=20&created_before=2026-03-25T10:30:00Z&id_before=456

Instead of an opaque cursor, the client passes the actual sort column values.

Implementation

app.get("/api/posts", async (req, res) => {
  const limit = Math.min(parseInt(req.query.limit as string) || 20, 100);
  const createdBefore = req.query.created_before as string | undefined;
  const idBefore = req.query.id_before ? parseInt(req.query.id_before as string) : undefined;

let where = {};
  if (createdBefore && idBefore) {
    // Keyset condition: items "before" this position
    where = {
      OR: [
        { createdAt: { lt: new Date(createdBefore) } },
        {
          createdAt: { equals: new Date(createdBefore) },
          id: { lt: idBefore },
        },
      ],
    };
  }

const posts = await prisma.post.findMany({
    where,
    orderBy: [{ createdAt: "desc" }, { id: "desc" }],
    take: limit + 1,
  });

const hasNext = posts.length > limit;
  if (hasNext) posts.pop();

const lastPost = posts[posts.length - 1];

res.json({
    data: posts,
    pagination: {
      hasNext,
      ...(lastPost && {
        nextParams: {
          created_before: lastPost.createdAt.toISOString(),
          id_before: lastPost.id,
        },
      }),
    },
  });
});

The SQL:

SELECT * FROM posts
WHERE (created_at < '2026-03-25T10:30:00Z')
   OR (created_at = '2026-03-25T10:30:00Z' AND id < 456)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Why Two Columns?

If you sort only by created_at, posts created at the exact same second would be skipped or duplicated. Adding id as a tiebreaker ensures a deterministic sort order. Always include a unique column in your keyset.

When Keyset Is Better Than Cursor

• You want the pagination parameters to be human-readable and debuggable
• Your API consumers need to construct pagination URLs manually
• You're filtering and sorting on the same column (e.g., "all posts before March 25th")

In practice, cursor and keyset are the same technique. Cursors just encode the keyset values.

Comparison Table

Feature	Offset	Cursor	Keyset
Performance at page 1	Fast	Fast	Fast
Performance at page 5000	Slow	Fast	Fast
Jump to arbitrary page	Yes	No	No
Stable under inserts/deletes	No	Yes	Yes
Total count	Easy	Expensive	Expensive
Implementation complexity	Low	Medium	Medium
Client complexity	Low	Low	Medium
Common adopters	Most CRUD apps	GitHub, Stripe, Slack	Twitter, Facebook

The Total Count Problem

Offset pagination naturally gives you a total count. Cursor and keyset don't. Computing COUNT(*) on a large table is expensive -- it requires a full table scan in most databases.

Options:

1. Skip the total count. Many modern UIs use "Load More" buttons instead of "Page 1 of 78." You only need hasNextPage, not the total.

1. Estimate it. PostgreSQL can give you a fast estimate:

SELECT reltuples::bigint AS estimate
FROM pg_class
WHERE relname = 'posts';

1. Cache it. Compute the count every few minutes and serve the cached value:

// Update count every 5 minutes
const cachedCount = await redis.get("posts:count");
if (!cachedCount) {
  const count = await prisma.post.count();
  await redis.set("posts:count", count, "EX", 300);
}

1. Use a counter table. Maintain a separate count that's updated on insert/delete via triggers or application logic.

Index Requirements

Pagination performance lives and dies by indexes:

-- For offset pagination (ORDER BY created_at DESC)
CREATE INDEX idx_posts_created_at ON posts(created_at DESC);

-- For cursor/keyset pagination (ORDER BY created_at DESC, id DESC)
CREATE INDEX idx_posts_created_at_id ON posts(created_at DESC, id DESC);

-- For filtered + paginated queries
CREATE INDEX idx_posts_author_created ON posts(author_id, created_at DESC, id DESC);

Without the right index, even cursor pagination falls back to sequential scans.

Check your query plan:

EXPLAIN ANALYZE
SELECT * FROM posts
WHERE created_at < '2026-03-25T10:30:00Z'
ORDER BY created_at DESC, id DESC
LIMIT 20;

You want to see "Index Scan" or "Index Only Scan," not "Seq Scan."

GraphQL Pagination

GraphQL standardized cursor pagination through the Relay Connection specification:

type Query {
  posts(first: Int, after: String, last: Int, before: String): PostConnection!
}

type PostConnection {
  edges: [PostEdge!]!
  pageInfo: PageInfo!
}

type PostEdge {
  node: Post!
  cursor: String!
}

type PageInfo {
  hasNextPage: Boolean!
  hasPreviousPage: Boolean!
  startCursor: String
  endCursor: String
}

Even if you're building a REST API, this structure is worth copying. It's well-thought-out and clients know how to work with it.

Which Should You Pick?

Offset if:

• Your dataset is small (under 100K rows)
• You need "page X of Y" in the UI
• You're building an admin panel
• You value simplicity over performance

Cursor if:

• Your dataset is large or growing
• You have real-time data (social feeds, notifications, logs)
• You're building a public API
• You want consistent performance at any depth

Keyset if:

• You want the benefits of cursor but with transparent, debuggable parameters
• Your API consumers need to construct pagination parameters manually
• You're already filtering by the sort column

For most production APIs, cursor pagination is the right default. The implementation overhead is minimal compared to offset, and you'll never hit the performance cliff that offset creates at scale. If you're building APIs on CodeUp, cursor pagination is what we recommend for anything that might grow beyond toy-project size.