Pagination Patterns: Offset vs Cursor

Why Pagination Strategy Matters More Than You Think

Pagination looks like a solved problem until your dataset grows. Most developers reach for SKIP/LIMIT (or OFFSET/LIMIT) because it maps directly to how humans think about pages. It works fine at small scale and then degrades in ways that are hard to diagnose unless you know what to look for.

This post covers both approaches, explains where each breaks, and gives you a working cursor implementation you can adapt for MongoDB or PostgreSQL.

For the query performance angle, MongoDB Indexing: Make Your Queries Fast is the prerequisite — pagination queries without the right indexes are painful regardless of the strategy.

Offset Pagination

Offset pagination is the familiar page + limit model:

GET /api/posts?page=5&limit=20

Which translates to:

sql
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 80;

Or in MongoDB:

typescript
const posts = await Post.find()
  .sort({ createdAt: -1 })
  .skip((page - 1) * limit)
  .limit(limit);

The Problems with Offset

Performance at depth. SKIP 80 is cheap. SKIP 80000 is not. The database still scans and discards all preceding rows even if they aren't returned. For a collection with millions of documents, deep-page queries become full-collection scans regardless of indexes.

Drift. If new items are inserted between page 1 and page 2 fetches, every subsequent page is off by the number of inserts. Users see duplicates (an item from the bottom of page 1 appears at the top of page 2) or miss items entirely. This is not theoretical — it happens in any live feed.

No reliable total count for free. To show "Page 5 of 212", you need a separate COUNT(*) query, which is also expensive on large collections without covering indexes.

Cursor Pagination

Cursor pagination avoids SKIP entirely. Instead of "give me page 5", you ask "give me the 20 items after this specific item":

GET /api/posts?cursor=<opaque_cursor>&limit=20

The cursor encodes a stable, unique position in the dataset — typically the sorted field value (and the _id as a tiebreaker for equal values).

MongoDB Implementation

typescript
// lib/pagination.ts
import { Buffer } from 'buffer';

export interface CursorPayload {
  createdAt: string; // ISO string
  id: string;
}

export function encodeCursor(payload: CursorPayload): string {
  return Buffer.from(JSON.stringify(payload)).toString('base64url');
}

export function decodeCursor(cursor: string): CursorPayload {
  return JSON.parse(Buffer.from(cursor, 'base64url').toString('utf8'));
}

typescript
// app/api/posts/route.ts
import { NextRequest, NextResponse } from 'next/server';
import Post from '@/models/Post';
import { decodeCursor, encodeCursor } from '@/lib/pagination';

const PAGE_SIZE = 20;

export async function GET(req: NextRequest) {
  const { searchParams } = req.nextUrl;
  const cursorParam = searchParams.get('cursor');
  const limit = Math.min(Number(searchParams.get('limit') ?? PAGE_SIZE), 100);

  let filter: Record<string, unknown> = {};

  if (cursorParam) {
    const { createdAt, id } = decodeCursor(cursorParam);
    // Items strictly after the cursor position.
    // The compound condition handles ties on createdAt.
    filter = {
      $or: [
        { createdAt: { $lt: new Date(createdAt) } },
        { createdAt: new Date(createdAt), _id: { $lt: id } },
      ],
    };
  }

  // Fetch one extra to know if there's a next page
  const items = await Post.find(filter)
    .sort({ createdAt: -1, _id: -1 })
    .limit(limit + 1)
    .lean();

  const hasNextPage = items.length > limit;
  const results = hasNextPage ? items.slice(0, limit) : items;

  const nextCursor =
    hasNextPage
      ? encodeCursor({
          createdAt: results[results.length - 1].createdAt.toISOString(),
          id: String(results[results.length - 1]._id),
        })
      : null;

  return NextResponse.json({ results, nextCursor, hasNextPage });
}

The compound sort { createdAt: -1, _id: -1 } is stable because _id is unique. The compound filter mirrors this exactly, handling the edge case where multiple documents share the same createdAt timestamp.

Required index:

typescript
// models/Post.ts
PostSchema.index({ createdAt: -1, _id: -1 });

With this index, the query becomes a range scan starting from the cursor position — O(limit) regardless of how deep in the dataset you are.

Client-Side Usage

typescript
let cursor: string | null = null;

async function loadNextPage() {
  const url = cursor ? `/api/posts?cursor=${cursor}` : '/api/posts';
  const { results, nextCursor } = await fetch(url).then((r) => r.json());
  cursor = nextCursor;
  appendToFeed(results);
}

This pattern is identical to infinite scroll — load more on scroll bottom, track nextCursor. Because cursor values encode a stable position in the sorted order, inserts and deletes between fetches don't shift your position.

Bidirectional Cursors

For "previous page" support, store both a startCursor (first item) and endCursor (last item) in each response. To go back, invert the sort and the filter direction, then reverse the returned results. This is the approach GraphQL's Relay Cursor Connections spec formalizes, and it's a solid model even outside GraphQL.

When Offset Is Still Acceptable

Offset pagination isn't wrong, it's just limited:

• Admin UIs with small datasets (fewer than ~10k rows)
• Features where users jump to arbitrary page numbers (offset is the only way to do this directly)
• Reports or exports where the full scan happens anyway

For user-facing feeds, search results, and any feature with "load more", cursor pagination is the correct choice.

Connecting to API Design

Pagination response shape should be consistent across your API. At minimum: results, nextCursor, hasNextPage. Add prevCursor if you need backwards navigation. For REST design principles that complement pagination, see How to Design APIs for Next.js Applications.

Also consider that designing idempotent APIs and solid pagination go hand in hand — a robust API handles retries and repeated requests gracefully at every layer.

Summary

Offset pagination is intuitive but breaks at scale due to performance degradation and data drift. Cursor pagination is O(limit) regardless of dataset depth, handles live data correctly, and is the right default for any user-facing paginated list. The implementation is slightly more complex but entirely manageable with a small utility module. Add a compound index on your sort fields, fetch limit + 1 to detect next-page, and encode cursor state as opaque base64.

See the projects page for APIs built with these patterns, and interview-prep for system design questions on pagination at scale.