API Pagination Patterns: Cursor vs Offset vs Keyset

Every API that returns lists needs pagination. Without it, listing 1 million records returns 1 million records. The three main patterns — offset, cursor, and keyset — have different performance characteristics, consistency guarantees, and client experience.

TL;DR

• Offset pagination is simple but degrades to O(n) at deep pages — use only for small datasets or admin interfaces
• Cursor pagination is the right default for production APIs: O(1) performance at any depth, consistent during writes
• Keyset pagination is cursor pagination made transparent — good for internal APIs, tricky with multi-column sorts
• Relay Connections (the GraphQL cursor spec) standardizes cursor pagination for GraphQL APIs
• Never expose COUNT(*) as a default — on large tables it's a full table scan; approximate or omit it

Offset Pagination

Request: GET /users?limit=20&offset=40

Response:

{
  "data": [...],
  "pagination": {
    "total": 1500,
    "limit": 20,
    "offset": 40,
    "has_more": true
  }
}

How it works: SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 40

Pros

• Simple to implement and understand
• Random access — jump to any page (offset = (page - 1) * limit)
• Can show total count and "page X of Y"
• Works with any sorting

Cons

• Slow at deep offsets — OFFSET 100000 scans and discards 100K rows
• Inconsistent during writes — if a record is inserted/deleted between pages, clients skip or duplicate records
• Performance degrades linearly with offset value
• Total count query (COUNT(*)) is expensive on large tables

Performance

Offset	Query Time (1M rows)
0	~1ms
10,000	~15ms
100,000	~150ms
500,000	~800ms
900,000	~1.5s

Use when: Small datasets (<100K records), admin panels, or when "page X of Y" UI is required.

Cursor Pagination

Request: GET /users?limit=20&cursor=eyJpZCI6MTIzfQ==

Response:

{
  "data": [...],
  "pagination": {
    "has_more": true,
    "next_cursor": "eyJpZCI6MTQzfQ==",
    "prev_cursor": "eyJpZCI6MTI0fQ=="
  }
}

How it works: The cursor encodes the position (usually the last record's ID). The query uses WHERE id > 123 LIMIT 20 instead of OFFSET.

Pros

• Constant time — same performance regardless of position in the dataset
• Consistent during writes — cursor is a pointer to a specific record, not a position
• Scales to any dataset size — page 1 and page 50,000 have the same query time
• Opaque cursor hides implementation details

Cons

• No random access — can't jump to page 5
• No total count (or expensive to compute)
• Can't show "page X of Y"
• Cursor becomes invalid if the pointed-to record is deleted
• Slightly more complex to implement

Performance

Position	Query Time (1M rows)
Beginning	~1ms
Middle	~1ms
End	~1ms

Use when: Large datasets, real-time data, infinite scroll, mobile apps, any API where deep pagination is possible.

Keyset Pagination

Request: GET /users?limit=20&after_id=123&after_created=2026-03-08T12:00:00Z

Response:

{
  "data": [...],
  "pagination": {
    "has_more": true,
    "last_id": 143,
    "last_created": "2026-03-08T14:30:00Z"
  }
}

How it works: Like cursor pagination but the "cursor" is the actual column values. WHERE (created_at, id) > ('2026-03-08T12:00:00Z', 123) ORDER BY created_at, id LIMIT 20

Pros

• Same O(1) performance as cursor pagination
• Transparent — client can see and construct the pagination values
• Works with composite sorts (sort by created_at, then by id)
• Debuggable — no opaque base64 cursors

Cons

• Exposes internal column names/values
• Sort order is fixed (can't easily change sort without invalidating pagination)
• Multi-column sorting with keyset requires complex WHERE clauses
• Client needs to understand the sort order to construct valid requests

Use when: Internal APIs, when pagination transparency matters, APIs with fixed sort orders.

Comparison Table

Feature	Offset	Cursor	Keyset
Random access	✅ Yes	❌ No	❌ No
Total count	✅ Yes	❌ No	❌ No
Deep page performance	❌ O(n)	✅ O(1)	✅ O(1)
Consistency during writes	❌ Inconsistent	✅ Consistent	✅ Consistent
Implementation complexity	✅ Simple	⚠️ Medium	⚠️ Medium
Infinite scroll	❌ Poor	✅ Best	✅ Good
"Page X of Y"	✅ Yes	❌ No	❌ No

Real-World Usage

API	Pattern	Implementation
Stripe	Cursor	starting_after=obj_123, ending_before=obj_456
GitHub	Cursor (GraphQL)	after: "cursor", first: 20
Slack	Cursor	cursor=dXNlcl9pZA==, limit=20
Twitter/X	Cursor	pagination_token=abc123
Shopify	Cursor	page_info=abc123 (Link header)
Google	Page token	pageToken=abc123, pageSize=20

Best Practice: Cursor Pagination

For most APIs, cursor pagination is the right default:

1. Encode the cursor — base64 encode the position to make it opaque
2. Include has_more — boolean indicating more results exist
3. Provide next_cursor and prev_cursor — for forward and backward navigation
4. Default limit — set a sensible default (20) and maximum (100)
5. Don't include total count — it's expensive and rarely needed for infinite scroll

{
  "data": [...],
  "pagination": {
    "has_more": true,
    "next_cursor": "eyJpZCI6MTQzfQ==",
    "limit": 20
  }
}

If you need total count for UI, provide it as a separate endpoint or optional parameter (?include_count=true) so the expensive query only runs when needed.

Implementing Cursor Pagination in Node.js

Here is a production-ready cursor pagination implementation using Prisma. The cursor encodes a JSON object (base64) that can contain multiple sort fields, making it extensible without changing the API shape.

import { PrismaClient } from '@prisma/client';
import { z } from 'zod';

const prisma = new PrismaClient();

interface CursorData {
  id: string;
  [key: string]: string | number;
}

function encodeCursor(data: CursorData): string {
  return Buffer.from(JSON.stringify(data)).toString('base64url');
}

function decodeCursor(cursor: string): CursorData {
  try {
    return JSON.parse(Buffer.from(cursor, 'base64url').toString('utf8'));
  } catch {
    throw new Error('Invalid cursor');
  }
}

interface PaginationParams {
  limit?: number;
  cursor?: string;
  direction?: 'forward' | 'backward';
}

interface PaginatedResult<T> {
  data: T[];
  pagination: {
    has_more: boolean;
    next_cursor: string | null;
    prev_cursor: string | null;
    limit: number;
  };
}

async function paginateUsers(
  params: PaginationParams
): Promise<PaginatedResult<any>> {
  const limit = Math.min(params.limit ?? 20, 100);
  const isBackward = params.direction === 'backward';

  let whereClause: any = {};
  let orderBy: any = { id: isBackward ? 'desc' : 'asc' };

  if (params.cursor) {
    const cursorData = decodeCursor(params.cursor);
    whereClause = {
      id: isBackward
        ? { lt: cursorData.id }  // Going backward: records before cursor
        : { gt: cursorData.id }, // Going forward: records after cursor
    };
  }

  // Fetch one extra to determine if there are more results
  const users = await prisma.user.findMany({
    where: whereClause,
    orderBy,
    take: limit + 1,
  });

  const hasMore = users.length > limit;
  const data = hasMore ? users.slice(0, limit) : users;

  // If backward, reverse the results to maintain chronological order
  if (isBackward) data.reverse();

  const firstItem = data[0];
  const lastItem = data[data.length - 1];

  return {
    data,
    pagination: {
      has_more: hasMore,
      next_cursor: hasMore && lastItem
        ? encodeCursor({ id: lastItem.id })
        : null,
      prev_cursor: params.cursor && firstItem
        ? encodeCursor({ id: firstItem.id })
        : null,
      limit,
    },
  };
}

This wrapper handles both forward and backward navigation. The take: limit + 1 trick (fetch one more than requested) is the standard way to determine if more results exist without running a COUNT(*) query.

Pagination endpoint:

app.get('/api/users', async (req, res) => {
  const schema = z.object({
    limit: z.coerce.number().int().min(1).max(100).default(20),
    cursor: z.string().optional(),
    direction: z.enum(['forward', 'backward']).default('forward'),
  });

  const params = schema.parse(req.query);

  try {
    const result = await paginateUsers(params);
    res.json(result);
  } catch (err) {
    if (err instanceof Error && err.message === 'Invalid cursor') {
      res.status(400).json({
        error: { code: 'invalid_cursor', message: 'The cursor value is invalid or expired.' },
      });
      return;
    }
    throw err;
  }
});

For multi-field sorting (e.g., sort by created_at DESC, then id ASC as tiebreaker), the cursor encodes both fields:

// Cursor for multi-field sort
const cursor = encodeCursor({ id: item.id, created_at: item.createdAt.toISOString() });

// WHERE clause for multi-field sort with proper tie-breaking
const whereClause = params.cursor ? {
  OR: [
    { created_at: { lt: cursorData.created_at } },
    { created_at: cursorData.created_at, id: { gt: cursorData.id } },
  ],
} : {};

GraphQL Connections Specification

The Relay Connections spec is the standard for cursor pagination in GraphQL APIs. It was developed by Facebook for the Relay client library but has become the industry standard for GraphQL pagination regardless of which client you use. Understanding it prevents you from reinventing an incompatible pagination interface.

The Relay spec defines a specific shape for paginated data: a "Connection" type that wraps a list of "Edge" types, each containing a node (the data object) and a cursor (the position). The Connection also includes a pageInfo object with navigation metadata.

query GetUsers($first: Int, $after: String) {
  users(first: $first, after: $after) {
    edges {
      cursor
      node {
        id
        name
        email
      }
    }
    pageInfo {
      hasNextPage
      hasPreviousPage
      startCursor
      endCursor
    }
    totalCount  # optional — expensive on large tables
  }
}

Implementing the Relay spec in a GraphQL server (using GraphQL Yoga + Pothos for type safety):

import { builder } from './builder';

builder.queryField('users', t =>
  t.connection({
    type: UserType,
    resolve: async (parent, args, ctx) => {
      const { first, after, last, before } = args;
      const limit = first ?? last ?? 20;

      const cursorId = after ? decodeCursor(after).id : before ? decodeCursor(before).id : null;
      const isBackward = !!last || !!before;

      const users = await prisma.user.findMany({
        where: cursorId ? {
          id: isBackward ? { lt: cursorId } : { gt: cursorId },
        } : {},
        orderBy: { id: isBackward ? 'desc' : 'asc' },
        take: limit + 1,
      });

      const hasMore = users.length > limit;
      const data = hasMore ? users.slice(0, limit) : users;
      if (isBackward) data.reverse();

      return {
        edges: data.map(user => ({
          cursor: encodeCursor({ id: user.id }),
          node: user,
        })),
        pageInfo: {
          hasNextPage: !isBackward && hasMore,
          hasPreviousPage: isBackward && hasMore,
          startCursor: data[0] ? encodeCursor({ id: data[0].id }) : null,
          endCursor: data[data.length - 1] ? encodeCursor({ id: data[data.length - 1].id }) : null,
        },
      };
    },
  })
);

Facebook invented the Relay spec because client-side state management for paginated lists is notoriously complex. The cursor on each edge (not just at the connection level) enables the client to start pagination from any point in the list — not just the beginning and end. This is essential for features like "load more after item X" that appear in social feeds and activity streams.

Pagination and Search

Full-text search APIs (Elasticsearch, Meilisearch, Typesense) require different pagination approaches than database queries. The core issue is that offset pagination breaks search relevance: if new documents are indexed between page loads, the relevance scores change, and offset-based pagination can show different documents on what appear to be the same page.

Elasticsearch's search_after is the cursor-based pagination mechanism for search. Instead of from (offset), you use the sort values of the last document as the starting point for the next page:

// First page
const firstPage = await client.search({
  index: 'products',
  body: {
    query: { match: { name: 'laptop' } },
    sort: [{ _score: 'desc' }, { id: 'asc' }], // Add tiebreaker
    size: 20,
  },
});

const lastHit = firstPage.hits.hits[firstPage.hits.hits.length - 1];

// Next page using search_after
const nextPage = await client.search({
  index: 'products',
  body: {
    query: { match: { name: 'laptop' } },
    sort: [{ _score: 'desc' }, { id: 'asc' }],
    search_after: lastHit.sort, // Sort values from last document
    size: 20,
  },
});

The search_after value (the sort array from the last document) is the cursor. Encode it as base64 for the API response:

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

Why offset pagination breaks search relevance: Elasticsearch (and Meilisearch) score documents based on a snapshot of the index at query time. If you retrieve page 1 (documents 1-20 by score) and then a new document is indexed with a very high score, retrieving page 2 with from: 20 will be relative to the new index state — you might see document 20 from the previous ranking on both page 1 and page 2, or skip documents that moved up in ranking.

Faceted search pagination compounds this problem. Facets (filters like "brand: Apple", "price: $100-$500") change the result set fundamentally. When a user applies a new facet, start pagination from the beginning — never try to maintain cursor state across facet changes.

Keyset Pagination with Multiple Sort Columns

Multi-column sorts with keyset pagination are significantly more complex than single-column sorts. The WHERE clause must correctly express "rows that come after this row in the specified sort order," which requires handling multiple sort directions and the tie-breaking between columns.

The clean SQL approach is PostgreSQL's row value comparison:

-- Sort by created_at DESC, then id ASC (as tiebreaker)
-- "After" the cursor row: lower created_at, or same created_at but higher id
WHERE (created_at, id) < ($1, $2)  -- Note: "less than" because DESC
ORDER BY created_at DESC, id ASC
LIMIT 20

PostgreSQL evaluates (a, b) < (c, d) as a < c OR (a = c AND b < d), which is exactly the tie-breaking logic needed. This is cleaner than manually writing the OR condition and is index-friendly.

Handling NULL values in sort columns is where keyset pagination gets genuinely tricky. PostgreSQL treats NULLs as larger than any non-NULL value in DESC order and smaller in ASC order (configurable with NULLS FIRST / NULLS LAST). If your sort column has NULLs, your cursor comparison must account for them:

-- Sort by optional `completed_at` (nullable) DESC NULLS LAST, then id ASC
-- Three cases for "after" cursor:
WHERE (
  -- Case 1: cursor was NULL (at the end of non-null values)
  -- No rows come "after" a NULL in NULLS LAST ordering for DESC
  ($1::timestamptz IS NULL AND false)

  -- Case 2: cursor was non-NULL, this row's value is lower (older)
  OR (completed_at < $1 AND $1 IS NOT NULL)

  -- Case 3: cursor was non-NULL, same value, tiebreak by id
  OR (completed_at = $1 AND id > $2)

  -- Case 4: cursor was non-NULL, this row's value is NULL (comes after all non-NULLs)
  OR (completed_at IS NULL AND $1 IS NOT NULL)
)
ORDER BY completed_at DESC NULLS LAST, id ASC

This complexity is why many APIs choose either opaque cursors (which hide this complexity from clients) or avoid multi-column keyset pagination in favor of encoding the sort values in a single cursor. For public APIs, the cursor approach is almost always preferable to the keyset approach when composite sorts are involved.

Total Count Problem

Total count (SELECT COUNT(*) FROM table WHERE ...) seems simple but is a significant performance concern on large tables. PostgreSQL must touch every qualifying row to count them — there is no shortcut. For a table with 10 million rows and a non-selective WHERE clause, a COUNT(*) query can take seconds.

PostgreSQL's reltuples estimate is available in pg_class and provides an approximate row count without scanning the table:

-- Fast approximate count (within ~10% on large tables)
SELECT reltuples::bigint AS approximate_count
FROM pg_class
WHERE relname = 'users';

This estimate is updated by VACUUM and ANALYZE, so it is usually within a few percent of the true count on active tables. For pagination contexts where "about 1.2 million results" is as useful as "exactly 1,247,382 results," the estimate is perfectly acceptable.

When to omit total count entirely: for infinite scroll interfaces (the only counts that matter are "are there more?" and "how many pages have loaded?"), for very large datasets where counts are approximations anyway, and for any endpoint where has_more: true/false is sufficient for the UI. The most popular APIs (Stripe, GitHub, Slack) do not return total counts on their cursor-paginated endpoints — they return has_more and that is sufficient.

Providing count via a separate endpoint: if some clients need total count and others don't, offer it via an optional parameter or a separate endpoint. This follows the API design principle that expensive operations should be explicit:

GET /api/users           → no count, O(1) cursor pagination
GET /api/users/count     → approximate count, separate request
GET /api/users?count=true → exact count + data, O(n) for count portion

Clients that need count headers (for showing "2,847 results" in a search interface) opt in explicitly and accept the performance cost. Clients building infinite scroll never pay for a count they don't need.

For more on designing clean REST APIs, see our guides on how to design a REST API developers love and API documentation with OpenAPI. Browse the full API directory for data layer tools, search APIs, and database services that support efficient pagination patterns.

Conclusion

Pagination strategy is one of the first API design decisions that has lasting performance implications. Offset pagination is the path of least resistance but creates silent performance problems as your dataset grows. Cursor pagination adds moderate implementation complexity in exchange for consistent O(1) performance, write consistency, and infinite scroll support.

The practical guidance: default to cursor pagination for any list endpoint that could grow large, implement the Relay Connections spec for GraphQL APIs (your clients will thank you), and never add COUNT(*) to your default response for large tables — make it opt-in. These decisions made at API design time are far easier to implement correctly than to retrofit after a performance incident.

Related: API Error Handling Patterns for Production Applications, Event-Driven APIs: Webhooks, WebSockets & SSE, GraphQL Client Patterns for Production Apps