<!-- APIScout AI-readable guide source -->
<!-- Canonical: https://apiscout.dev/guides/api-pagination-patterns-cursor-vs-offset-2026 -->
<!-- Raw Markdown: https://apiscout.dev/guides/api-pagination-patterns-cursor-vs-offset-2026/raw.md -->
<!-- Source path: content/guides/api-pagination-patterns-cursor-vs-offset-2026.mdx -->

---
og_image: "/images/guides/api-pagination-patterns-cursor-vs-offset-2026.webp"
title: "API Pagination: Cursor vs Offset in 2026"
description: "Compare API pagination patterns — cursor, offset, and keyset. Performance benchmarks, consistency guarantees, implementation code, and when to use each in 2026."
date: "2026-03-08"
author: "APIScout Team"
tags: ["api-pagination", "cursor-pagination", "api-design", "best-practices", "api-architecture"]
tier: 1
---

# 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:**
```json
{
  "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:**
```json
{
  "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:**
```json
{
  "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

```json
{
  "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.

```typescript
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:**

```typescript
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:

```typescript
// 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.

```graphql
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):

```typescript
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:

```typescript
// 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:

```typescript
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:

```sql
-- 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:

```sql
-- 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:

```sql
-- 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](/blog/how-to-design-rest-api-developers-love-2026) and [API documentation with OpenAPI](/blog/api-documentation-openapi-vs-asyncapi-2026). Browse the [full API directory](/apis) 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](/blog/api-error-handling-patterns-production-2026), [Event-Driven APIs: Webhooks, WebSockets & SSE](/blog/event-driven-apis-async-patterns-2026), [GraphQL Client Patterns for Production Apps](/blog/graphql-client-patterns-production-2026)*