JSON API Pagination: Offset, Cursor, Keyset & Seek

Written and reviewed by the Jsonic editorial team — every guide is verified against the official spec or runtime before publication.

Last updated: May 20, 2026

JSON API pagination controls how many results are returned per request — offset pagination (page=2&limit=20) is simplest but breaks when rows are inserted mid-query; cursor pagination uses an opaque token to maintain stable position. Offset pagination is O(n) at the database — OFFSET 10000 forces the database to scan 10,000 rows even when discarding them. Cursor pagination with WHERE id > :cursor LIMIT 20 is O(log n) with a B-tree index on id.

This guide covers offset vs cursor vs keyset vs seek pagination, JSON response envelope design (data, meta, links), SQL query patterns, React Query integration, and infinite scroll implementation. Every strategy includes edge cases for deletion and re-ordering. For broader context on JSON API design patterns including versioning and error handling, see our companion guide.

Offset Pagination: Pros, Cons, and SQL Implementation

Offset pagination is the simplest strategy: the client sends a page number and page size, the server translates them to a SQL LIMIT / OFFSET clause. It works correctly for small, stable datasets and is the only strategy that supports random page jumps. The fatal flaw is O(n) performance — OFFSET 10000 LIMIT 20 makes the database scan and discard the first 10,000 rows on every request, even with a perfect index.

-- ── Offset pagination SQL ────────────────────────────────────────
-- page=1 → OFFSET 0, page=2 → OFFSET 20, etc.
SELECT id, name, created_at
FROM   items
ORDER  BY created_at DESC, id DESC   -- always include id as tiebreaker
LIMIT  :limit
OFFSET (:page - 1) * :limit;

-- Count query for total pages (expensive — full table scan or index scan)
SELECT COUNT(*) FROM items;          -- run once, cache aggressively

-- ── Performance degradation ───────────────────────────────────────
-- Page 1   → OFFSET 0     → scans 20 rows      ✓ fast
-- Page 10  → OFFSET 180   → scans 200 rows     ✓ acceptable
-- Page 100 → OFFSET 1980  → scans 2,000 rows   ⚠ slow
-- Page 500 → OFFSET 9980  → scans 10,000 rows  ✗ very slow
-- Page 501 → OFFSET 10000 → scans 10,020 rows  ✗ unacceptable

-- ── PostgreSQL EXPLAIN (sequential offset scan) ───────────────────
EXPLAIN SELECT id FROM items ORDER BY id OFFSET 100000 LIMIT 20;
-- Limit  (cost=12530.40..12530.45 rows=20)
--   ->  Index Scan using items_pkey on items
--         (cost=0.43..62651.43 rows=1000000)
--   Rows Removed by Offset: 100000     ← must visit 100,000 rows

-- ── REST API request/response format ─────────────────────────────
-- Request:  GET /api/items?page=3&per_page=20
-- Response:
{
  "data": [
    { "id": 41, "name": "Widget A", "created_at": "2024-06-01T10:00:00Z" },
    { "id": 42, "name": "Widget B", "created_at": "2024-06-01T09:55:00Z" }
  ],
  "meta": {
    "total":    1500,
    "page":     3,
    "per_page": 20,
    "total_pages": 75,
    "has_next": true,
    "has_prev": true
  },
  "links": {
    "first": "/api/items?page=1&per_page=20",
    "prev":  "/api/items?page=2&per_page=20",
    "next":  "/api/items?page=4&per_page=20",
    "last":  "/api/items?page=75&per_page=20"
  }
}

-- ── Node.js / Express implementation ─────────────────────────────
app.get('/api/items', async (req, res) => {
  const page    = Math.max(1, parseInt(req.query.page)     || 1);
  const perPage = Math.min(100, parseInt(req.query.per_page) || 20);
  const offset  = (page - 1) * perPage;

  const [rows, [{ count }]] = await Promise.all([
    db.query(
      'SELECT * FROM items ORDER BY created_at DESC, id DESC LIMIT $1 OFFSET $2',
      [perPage, offset]
    ),
    db.query('SELECT COUNT(*)::int AS count FROM items'),
  ]);

  const total = count;
  const totalPages = Math.ceil(total / perPage);

  res.json({
    data: rows,
    meta: { total, page, per_page: perPage, total_pages: totalPages,
            has_next: page < totalPages, has_prev: page > 1 },
    links: {
      first: `/api/items?page=1&per_page=${perPage}`,
      prev:  page > 1         ? `/api/items?page=${page - 1}&per_page=${perPage}` : null,
      next:  page < totalPages ? `/api/items?page=${page + 1}&per_page=${perPage}` : null,
      last:  `/api/items?page=${totalPages}&per_page=${perPage}`,
    },
  });
});

Cache the COUNT(*) result aggressively — recomputing it on every paginated request doubles database load. A Redis TTL of 60 seconds is usually acceptable; stale total counts by a few seconds are far less harmful than per-request full-table scans. For datasets under 10,000 rows, offset pagination is a completely reasonable choice. Above that threshold, benchmark carefully and plan a migration to cursor pagination before performance becomes a user-visible problem. See our JSON performance guide for database query optimization strategies.

Cursor-Based Pagination: Stable, Efficient, Production-Ready

Cursor pagination replaces the page number with an opaque token that encodes the position of the last item seen. The server decodes the token, applies a WHERE id > :cursor filter, and returns the next batch. Because the query always uses an indexed column comparison rather than a row skip, performance is O(log n) regardless of how deep into the result set the client has scrolled — page 1,000 is as fast as page 1.

-- ── Cursor pagination SQL (ascending by id) ─────────────────────
-- First page (no cursor)
SELECT id, name, created_at
FROM   items
ORDER  BY id ASC
LIMIT  20;

-- Subsequent pages — cursor encodes last seen id
SELECT id, name, created_at
FROM   items
WHERE  id > :last_seen_id           -- O(log n) B-tree seek
ORDER  BY id ASC
LIMIT  20;

-- ── EXPLAIN shows index seek vs offset scan ───────────────────────
EXPLAIN SELECT id FROM items WHERE id > 100000 ORDER BY id LIMIT 20;
-- Index Scan using items_pkey on items
--   Index Cond: (id > 100000)
--   Rows Removed by Filter: 0     ← no rows discarded
-- Actual rows: 20, loops: 1

-- ── Cursor encoding — Base64 JSON (opaque to client) ─────────────
// Encode cursor (server-side, after fetching a page)
function encodeCursor(lastItem) {
  return Buffer.from(JSON.stringify({ id: lastItem.id })).toString('base64url');
}
// "eyJpZCI6MTIwfQ"  (URL-safe Base64)

// Decode cursor (server-side, before querying)
function decodeCursor(cursor) {
  try {
    return JSON.parse(Buffer.from(cursor, 'base64url').toString('utf8'));
  } catch {
    throw new Error('Invalid cursor');
  }
}
// { id: 120 }

// ── Express API handler ───────────────────────────────────────────
app.get('/api/items', async (req, res) => {
  const limit  = Math.min(100, parseInt(req.query.limit) || 20);
  const cursor = req.query.cursor;

  let rows;
  if (cursor) {
    const { id } = decodeCursor(cursor);
    rows = await db.query(
      'SELECT * FROM items WHERE id > $1 ORDER BY id ASC LIMIT $2',
      [id, limit + 1]          // fetch limit+1 to detect has_next
    );
  } else {
    rows = await db.query(
      'SELECT * FROM items ORDER BY id ASC LIMIT $1',
      [limit + 1]
    );
  }

  const hasNext = rows.length > limit;
  if (hasNext) rows.pop();      // remove the extra sentinel row

  const nextCursor = hasNext ? encodeCursor(rows[rows.length - 1]) : null;

  res.json({
    data: rows,
    meta: {
      has_next:    hasNext,
      next_cursor: nextCursor,
    },
  });
});

// ── Client usage ──────────────────────────────────────────────────
// Page 1:  GET /api/items?limit=20
// Page 2:  GET /api/items?limit=20&cursor=eyJpZCI6MjB9
// Page 3:  GET /api/items?limit=20&cursor=eyJpZCI6NDB9
// Page N:  GET /api/items?limit=20&cursor=<next_cursor from previous response>

The limit+1 trick is the standard way to detect has_next without a separate COUNT query — fetch one more row than needed, and if you get it, there is a next page; pop it before returning. Always use base64url encoding (not standard Base64) for cursors that appear in URLs — standard Base64 contains +, /, and = which must be percent-encoded in query strings and cause double-encoding bugs. Base64url replaces these with -, _, and omits padding. See our JSON caching guide for strategies on caching paginated API responses.

Keyset and Seek Pagination for Multi-Column Sort

Keyset pagination is cursor pagination without the encoding layer — the client passes the actual column values of the last seen row as query parameters. Seek pagination is the SQL technique that powers both: a row value comparison WHERE (col1, col2) > (:val1, :val2) that efficiently seeks past a known position. Both solve the fundamental problem of paginating by a non-unique column like created_at, where ties must be broken deterministically.

-- ── Problem: non-unique sort column creates ambiguous pages ──────
-- Sorting by created_at alone — rows with the same timestamp span pages
SELECT id, name, created_at FROM items ORDER BY created_at ASC LIMIT 20;
-- If rows 20 and 21 share the same created_at, the page boundary is arbitrary
-- Next page with WHERE created_at > :last_ts may skip or duplicate ties

-- ── Keyset pagination with row value comparison (PostgreSQL/MySQL) ─
-- Stable sort: (created_at ASC, id ASC) — id breaks all ties
-- First page
SELECT id, name, created_at
FROM   items
ORDER  BY created_at ASC, id ASC
LIMIT  20;

-- Subsequent pages — pass both sort values as keyset
SELECT id, name, created_at
FROM   items
WHERE  (created_at, id) > (:last_created_at, :last_id)
ORDER  BY created_at ASC, id ASC
LIMIT  20;

-- ── Composite index required ──────────────────────────────────────
CREATE INDEX idx_items_created_id ON items (created_at ASC, id ASC);
-- The row value comparison (created_at, id) > (val1, val2) uses this index
-- Both PostgreSQL and MySQL 8+ support row value comparisons on composite indexes

-- ── REST API: keyset exposed as query params (no Base64) ──────────
-- Page 1:  GET /api/items?limit=20
-- Page 2:  GET /api/items?limit=20&after_created_at=2024-06-01T10:00:00Z&after_id=20
-- Page 3:  GET /api/items?limit=20&after_created_at=2024-06-01T10:05:00Z&after_id=40

// ── Express handler for keyset pagination ─────────────────────────
app.get('/api/items', async (req, res) => {
  const limit         = Math.min(100, parseInt(req.query.limit) || 20);
  const afterCreatedAt = req.query.after_created_at;
  const afterId        = req.query.after_id;

  let rows;
  if (afterCreatedAt && afterId) {
    rows = await db.query(
      `SELECT id, name, created_at
       FROM items
       WHERE (created_at, id) > ($1, $2)
       ORDER BY created_at ASC, id ASC
       LIMIT $3`,
      [afterCreatedAt, parseInt(afterId), limit + 1]
    );
  } else {
    rows = await db.query(
      'SELECT id, name, created_at FROM items ORDER BY created_at ASC, id ASC LIMIT $1',
      [limit + 1]
    );
  }

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

  const last = rows[rows.length - 1];
  res.json({
    data: rows,
    meta: {
      has_next:         hasNext,
      next_after_created_at: hasNext ? last.created_at : null,
      next_after_id:         hasNext ? last.id         : null,
    },
  });
});

-- ── Descending sort keyset ────────────────────────────────────────
-- For newest-first feeds: (created_at DESC, id DESC)
SELECT id, name, created_at
FROM   items
WHERE  (created_at, id) < (:last_created_at, :last_id)  -- note: < for DESC
ORDER  BY created_at DESC, id DESC
LIMIT  20;

CREATE INDEX idx_items_created_id_desc ON items (created_at DESC, id DESC);

Row value comparisons ((a, b) > (x, y)) are semantically equivalent to (a > x) OR (a = x AND b > y) but are more concise and are optimized differently by query planners. PostgreSQL uses the composite index efficiently for row value comparisons; MySQL 8.0+ added support in 2018. Older MySQL versions (5.7 and below) may not use the composite index for row value comparisons — test with EXPLAIN and fall back to the explicit OR expansion if needed. Always verify index usage with EXPLAIN ANALYZE before deploying to production.

JSON Pagination Response Envelope Design

A well-structured JSON envelope separates concerns cleanly: data holds the array of items, meta holds pagination metadata, and links provides pre-built navigation URLs. Getting the envelope right from the start prevents breaking API changes later — adding fields to meta is backward compatible, but restructuring the top-level shape is not. Design the envelope to work for both offset and cursor pagination simultaneously, with optional fields for each mode.

// ── Full pagination envelope (supports both offset and cursor) ────
{
  "data": [
    {
      "id":         41,
      "name":       "Widget A",
      "created_at": "2024-06-01T10:00:00Z",
      "updated_at": "2024-06-15T08:30:00Z"
    }
  ],
  "meta": {
    // Offset pagination fields
    "total":       1500,         // total record count — omit for cursor pagination
    "page":        3,            // current page number (1-indexed)
    "per_page":    20,           // items per page
    "total_pages": 75,           // Math.ceil(total / per_page)

    // Cursor pagination fields
    "has_next":    true,         // always include — used by all pagination types
    "has_prev":    true,         // optional for cursor pagination
    "next_cursor": "eyJpZCI6NjB9",     // base64url encoded cursor
    "prev_cursor": "eyJpZCI6MjF9",     // optional — for bidirectional pagination

    // Both modes
    "count":       20            // number of items in this response
  },
  "links": {
    "first": "https://api.example.com/items?per_page=20&page=1",
    "prev":  "https://api.example.com/items?per_page=20&page=2",
    "self":  "https://api.example.com/items?per_page=20&page=3",
    "next":  "https://api.example.com/items?per_page=20&page=4",
    "last":  "https://api.example.com/items?per_page=20&page=75"
  }
}

// ── Minimal cursor-only envelope ──────────────────────────────────
{
  "data": [ /* items */ ],
  "meta": {
    "has_next":    true,
    "next_cursor": "eyJpZCI6NjB9",
    "count":       20
  }
}

// ── TypeScript type definitions ───────────────────────────────────
interface PaginatedResponse<T> {
  data: T[];
  meta: {
    // Offset fields (optional)
    total?:       number;
    page?:        number;
    per_page?:    number;
    total_pages?: number;
    // Cursor fields (optional)
    next_cursor?: string | null;
    prev_cursor?: string | null;
    // Always present
    has_next:     boolean;
    has_prev?:    boolean;
    count:        number;
  };
  links?: {
    first?: string;
    prev?:  string | null;
    self?:  string;
    next?:  string | null;
    last?:  string;
  };
}

// ── JSON:API specification format ─────────────────────────────────
// https://jsonapi.org — uses links at top level
{
  "data": [
    { "type": "items", "id": "41", "attributes": { "name": "Widget A" } }
  ],
  "links": {
    "first": "https://api.example.com/items?page[number]=1",
    "prev":  "https://api.example.com/items?page[number]=2",
    "next":  "https://api.example.com/items?page[number]=4",
    "last":  "https://api.example.com/items?page[number]=75"
  },
  "meta": { "total-pages": 75 }
}

// ── GitHub API envelope (cursor via Link header) ──────────────────
// Header: Link: <https://api.github.com/repos?page=2>; rel="next",
//               <https://api.github.com/repos?page=75>; rel="last"
// Body: [ { "id": 1, ... }, { "id": 2, ... } ]  — flat array, no wrapper

Avoid returning total from cursor-paginated endpoints — it requires a COUNT(*) that negates the performance advantage of cursor pagination. If clients need a count, provide a separate GET /api/items/count endpoint that can be independently cached. The GitHub API uses HTTP Link headers for pagination URLs rather than a response body envelope — this is elegant but makes client-side parsing harder since most JSON clients do not parse response headers by default. A body envelope is more ergonomic for most use cases. See our JSON API design guide for complete REST envelope patterns.

Bidirectional Pagination: Previous and Next Page Links

Bidirectional cursor pagination lets users navigate both forward and backward through a result set without losing their position. The server maintains both a next_cursor (encodes the last item on the current page) and a prev_cursor (encodes the first item). Each direction uses a different SQL comparison operator and sort direction, with the backward results reversed in application code before returning.

-- ── Forward page (after cursor) ─────────────────────────────────
-- cursor encodes { id: 100 } — last item on previous page
SELECT id, name, created_at
FROM   items
WHERE  id > 100               -- items AFTER the cursor
ORDER  BY id ASC
LIMIT  20;

-- ── Backward page (before cursor) ────────────────────────────────
-- cursor encodes { id: 121 } — first item on current page
SELECT id, name, created_at
FROM   items
WHERE  id < 121               -- items BEFORE the cursor
ORDER  BY id DESC             -- ← reversed to grab the 20 items before
LIMIT  20;
-- Reverse result in application code to restore ASC order

-- ── Cursor encoding for bidirectional ────────────────────────────
function encodeNextCursor(lastItem)  { return btoa(JSON.stringify({ id: lastItem.id, dir: 'next' })) }
function encodePrevCursor(firstItem) { return btoa(JSON.stringify({ id: firstItem.id, dir: 'prev' })) }

// ── Express handler ────────────────────────────────────────────────
app.get('/api/items', async (req, res) => {
  const limit  = Math.min(100, parseInt(req.query.limit) || 20);
  const after  = req.query.after;    // forward cursor
  const before = req.query.before;   // backward cursor

  let rows;

  if (after) {
    const { id } = decodeCursor(after);
    rows = await db.query(
      'SELECT * FROM items WHERE id > $1 ORDER BY id ASC LIMIT $2',
      [id, limit + 1]
    );
    const hasNext = rows.length > limit;
    if (hasNext) rows.pop();
    const hasPrev = true;  // came from a previous page

    return res.json({
      data: rows,
      meta: {
        has_next:    hasNext,
        has_prev:    hasPrev,
        next_cursor: hasNext ? encodeNextCursor(rows.at(-1)) : null,
        prev_cursor: encodePrevCursor(rows[0]),
      },
    });
  }

  if (before) {
    const { id } = decodeCursor(before);
    rows = await db.query(
      'SELECT * FROM items WHERE id < $1 ORDER BY id DESC LIMIT $2',
      [id, limit + 1]
    );
    const hasPrev = rows.length > limit;
    if (hasPrev) rows.pop();
    rows.reverse();          // restore ascending order

    return res.json({
      data: rows,
      meta: {
        has_next:    true,    // came from a next page
        has_prev:    hasPrev,
        next_cursor: encodeNextCursor(rows.at(-1)),
        prev_cursor: hasPrev ? encodePrevCursor(rows[0]) : null,
      },
    });
  }

  // First page — no cursor
  rows = await db.query('SELECT * FROM items ORDER BY id ASC LIMIT $1', [limit + 1]);
  const hasNext = rows.length > limit;
  if (hasNext) rows.pop();

  res.json({
    data: rows,
    meta: {
      has_next:    hasNext,
      has_prev:    false,
      next_cursor: hasNext ? encodeNextCursor(rows.at(-1)) : null,
      prev_cursor: null,
    },
  });
});

// ── GraphQL Relay bidirectional ───────────────────────────────────
// query { items(first: 20, after: "eyJpZCI6MTAwfQ==") { ... } }  — forward
// query { items(last: 20, before: "eyJpZCI6MTIxfQ==") { ... } }  — backward

Bidirectional cursor pagination is significantly more complex than forward-only — the backward query requires a reversed sort order and an in-code result reversal, and the has_prev / has_next flags must be set correctly for the first and last pages. Unless your product genuinely requires backward navigation (e.g., a data table where users browse history), forward-only cursor pagination with a "back to top" UX covers the vast majority of use cases with far simpler code. The GraphQL Relay spec's last/before arguments implement this pattern formally. For JSON error handling on invalid cursor values, return a 400 with a descriptive error body.

React Query Infinite Scroll with JSON Cursor Pagination

React Query's useInfiniteQuery hook is purpose-built for cursor pagination — it accumulates pages into an array, tracks the next cursor automatically, and exposes a fetchNextPage function. Combined with an IntersectionObserver sentinel element, it provides infinite scroll with no custom state management. The integration requires only a getNextPageParam function that extracts next_cursor from each page response.

// ── React Query useInfiniteQuery setup ───────────────────────────
import { useInfiniteQuery } from '@tanstack/react-query';
import { useEffect, useRef, useCallback } from 'react';

interface Item {
  id:         number;
  name:       string;
  created_at: string;
}

interface PageResponse {
  data: Item[];
  meta: {
    has_next:    boolean;
    next_cursor: string | null;
  };
}

async function fetchItems(cursor?: string, limit = 20): Promise<PageResponse> {
  const params = new URLSearchParams({ limit: String(limit) });
  if (cursor) params.set('cursor', cursor);
  const res = await fetch(`/api/items?${params}`);
  if (!res.ok) throw new Error(`API error: ${res.status}`);
  return res.json();
}

// ── Hook ──────────────────────────────────────────────────────────
function useInfiniteItems() {
  return useInfiniteQuery({
    queryKey:    ['items'],
    queryFn:     ({ pageParam }) => fetchItems(pageParam as string | undefined),
    getNextPageParam: (lastPage: PageResponse) =>
      lastPage.meta.has_next ? lastPage.meta.next_cursor ?? undefined : undefined,
    initialPageParam: undefined,
    staleTime: 30_000,   // 30s — paginated lists can go stale
  });
}

// ── IntersectionObserver infinite scroll ──────────────────────────
function ItemList() {
  const { data, fetchNextPage, hasNextPage, isFetchingNextPage, status } =
    useInfiniteItems();

  const sentinelRef = useRef<HTMLDivElement>(null);

  const handleIntersect = useCallback(
    (entries: IntersectionObserverEntry[]) => {
      if (entries[0].isIntersecting && hasNextPage && !isFetchingNextPage) {
        fetchNextPage();
      }
    },
    [fetchNextPage, hasNextPage, isFetchingNextPage]
  );

  useEffect(() => {
    const el = sentinelRef.current;
    if (!el) return;
    const observer = new IntersectionObserver(handleIntersect, {
      rootMargin: '200px',   // trigger 200px before bottom of viewport
    });
    observer.observe(el);
    return () => observer.disconnect();
  }, [handleIntersect]);

  if (status === 'pending') return <div>Loading...</div>;
  if (status === 'error')   return <div>Error loading items</div>;

  // Flatten all pages into a single array
  const allItems = data.pages.flatMap((page) => page.data);

  return (
    <div>
      <ul>
        {allItems.map((item) => (
          <li key={item.id}>{item.name}</li>
        ))}
      </ul>

      {/* Sentinel — triggers fetchNextPage when scrolled into view */}
      <div ref={sentinelRef} style={{ height: 1 }} />

      {isFetchingNextPage && <div>Loading more...</div>}
      {!hasNextPage && <div>No more items</div>}
    </div>
  );
}

// ── Prefetch next page for faster UX ─────────────────────────────
// In getNextPageParam, also trigger a prefetch 2 pages ahead:
// queryClient.prefetchInfiniteQuery({ queryKey: ['items'] });

// ── Reset on filter change ────────────────────────────────────────
// When search/filter params change, reset the infinite query:
const { data, fetchNextPage, hasNextPage, refetch } = useInfiniteQuery({
  queryKey: ['items', { search: searchTerm, category }],   // include filters in key
  // ... rest of config
  // Changing searchTerm or category resets the query automatically
});

The rootMargin: '200px' on the IntersectionObserver triggers fetchNextPage 200 pixels before the sentinel reaches the viewport — this pre-fetches the next batch while the user is still scrolling, eliminating the loading flash. Without pre-fetching, users see a spinner at the bottom of every page. Use staleTime: 30_000 to prevent React Query from re-fetching all accumulated pages on window focus — for infinite lists with many pages, a full re-fetch is expensive. The queryKey array should include all filter/sort parameters so changing them resets the infinite query to page 1 automatically.

Pagination Edge Cases: Deleted Rows, Re-ordering, and Empty Pages

Production pagination must handle four edge cases that standard implementations miss: (1) the cursor row is deleted between pages; (2) rows are re-ordered (sort column updated) while the client is paginating; (3) concurrent inserts push items into the current window; (4) the last page is empty because all remaining items were deleted. Each requires a specific handling strategy to avoid exposing errors to clients or returning incorrect data.

// ── Edge Case 1: Cursor row deleted ──────────────────────────────
// WHERE id > :deleted_id still works — it returns all rows after the
// deleted id, even though the deleted row itself is gone.
// The page is slightly shorter than expected but structurally correct.
// No special handling needed for forward cursor pagination on primary key.

// ── Edge Case 2: Cursor row sort column updated ───────────────────
// If cursor encodes { created_at, id } and created_at is updated,
// WHERE (created_at, id) > (:old_created_at, :last_id) may skip
// the updated row (now at a different position) or return it twice.
// Fix: use immutable cursor columns only — id (auto-increment PK)
// never changes. Avoid cursors based on mutable columns like status.

// ── Edge Case 3: Concurrent inserts into current window ───────────
// New items inserted AFTER the cursor are included in next pages — correct.
// New items inserted BEFORE the cursor are NOT shown — acceptable for
// most UIs (no phantom items appearing mid-scroll).
// If you need real-time updates, use a WebSocket or SSE to push new items
// to the top of the list rather than relying on pagination to include them.

// ── Edge Case 4: Empty page (all remaining rows deleted) ──────────
// Query returns 0 rows, has_next = false, next_cursor = null.
// The client must handle empty data arrays without crashing.

// ── Edge Case 5: Invalid or expired cursor ────────────────────────
function decodeCursor(cursor) {
  try {
    const decoded = JSON.parse(Buffer.from(cursor, 'base64url').toString());
    if (!decoded.id || typeof decoded.id !== 'number') {
      throw new Error('Invalid cursor structure');
    }
    return decoded;
  } catch {
    // Return 400 — do not silently fall back to page 1
    const err = new Error('Invalid cursor');
    err.statusCode = 400;
    throw err;
  }
}

// ── Edge Case 6: Page size manipulation ──────────────────────────
const MAX_PAGE_SIZE = 100;
function sanitizeLimit(raw) {
  const n = parseInt(raw, 10);
  if (!Number.isInteger(n) || n < 1) return 20;   // default
  return Math.min(n, MAX_PAGE_SIZE);               // cap at 100
}

// ── Edge Case 7: Offset page beyond total ────────────────────────
// OFFSET beyond total rows returns empty data — not an error.
// Detect and return a structured empty response:
if (offset >= total) {
  return res.status(200).json({
    data: [],
    meta: { total, page, per_page: perPage, has_next: false, has_prev: page > 1 },
  });
  // Do NOT return 404 — empty pages are valid, not "not found"
}

// ── Edge Case 8: Re-ordering items changes page structure ─────────
// User sorts by "price ASC" then "price DESC" mid-session.
// Cursor from the previous sort is no longer valid for the new sort.
// Fix: include sort parameters in the cursor and validate on decode.
function encodeCursor(lastItem, sortBy, sortDir) {
  return btoa(JSON.stringify({ id: lastItem.id, sortBy, sortDir }));
}

function decodeCursor(cursor) {
  const decoded = JSON.parse(atob(cursor));
  if (decoded.sortBy !== req.query.sort_by || decoded.sortDir !== req.query.sort_dir) {
    throw new Error('Cursor sort mismatch — start from page 1');
  }
  return decoded;
}

// ── Edge Case 9: Race condition on total count ─────────────────────
// total=1500 fetched at page 1, inserts happen, page 75 now has 76 pages.
// last page (page=75) returns data, but reported total_pages is stale.
// Fix: cache the total with a short TTL (60s) and accept slight staleness,
// or omit total entirely and use has_next for navigation only.

The most important rule is: never silently fall back to page 1 on an invalid cursor — return a 400 error so clients know their cursor is bad and can prompt users to refresh. Silent fallback causes user-visible bugs where the feed restarts from the beginning without warning. For the sort-mismatch edge case, embed the sort parameters inside the cursor payload and validate them on decode — this makes the cursor self-validating. If you anticipate frequent sort changes, consider making cursor pagination sort-aware by including a sort key in the encoded payload. See our JSON error handling guide for structuring 4xx error responses consistently.

Key Terms

offset pagination

A pagination strategy that uses a numeric page number and page size to fetch results via LIMIT :n OFFSET :m SQL clauses. The client passes ?page=3&per_page=20; the server computes OFFSET (3-1)*20 = 40. Simple to implement and supports random page jumps, but O(n) in performance — the database must scan and discard all rows before the offset on every request. OFFSET 10000 LIMIT 20 scans 10,020 rows even though only 20 are returned. Performance degrades linearly as page numbers increase, making offset pagination impractical for tables with more than ~10,000 rows when deep pagination is required.

cursor pagination

A pagination strategy that uses an opaque token (the cursor) to encode the position of the last item seen. The server decodes the cursor to a column value and applies a keyset filter (WHERE id > :cursor_id) rather than an offset. Performance is O(log n) with a B-tree index on the cursor column — the database seeks directly to the position without scanning discarded rows. Cursors are always Base64-encoded to hide implementation details from clients. The trade-off is that cursor pagination does not support random page jumps — clients must navigate sequentially. Required for infinite scroll, real-time feeds, and large datasets where offset pagination would cause unacceptable database load.

keyset pagination

A pagination strategy where the client passes the actual column values of the last seen row as query parameters, and the server uses them in a WHERE clause filter. Example: ?after_created_at=2024-06-01&after_id=100 translates to WHERE (created_at, id) > ('2024-06-01', 100). Functionally identical to cursor pagination but without the Base64 encoding layer — the sort keys are exposed directly in the URL. Easier to debug and inspect (no decoding needed) but couples clients to the internal sort column names. Cursor pagination is keyset pagination with an opacity wrapper that hides the implementation details. Both require a composite index on all sort columns for efficient query execution.

opaque cursor

A pagination cursor that encodes its payload in a format clients cannot read or construct — typically Base64url-encoded JSON. Example: eyJpZCI6MTAwfQ== decodes to {"{'{"}"id": 100{"'}"}. Opaqueness serves two purposes: it prevents clients from constructing arbitrary cursors (which could be used to access unauthorized data), and it gives the API the freedom to change the cursor format (add fields, change the sort column) without breaking clients. The GraphQL Relay spec mandates opaque cursors. Never use raw database IDs, offsets, or timestamps as cursor values directly in URLs — they reveal schema details and make future migrations harder. Always validate cursor structure on decode and return a 400 error for malformed values.

pagination envelope

The JSON wrapper object that contains paginated results along with metadata needed for navigation. Standard structure: data (array of items), meta (pagination metadata: total, page, per_page, has_next, next_cursor), and optionally links (pre-built navigation URLs for first, prev, next, last). The envelope design should be consistent across all paginated endpoints and accommodate both offset and cursor fields with optional properties. Avoid embedding pagination metadata in the root response object — a dedicated meta key makes it easy to extend without conflicts.

seek method

The SQL technique underlying keyset and cursor pagination: a WHERE clause that uses row value comparison to seek past a known position. Single-column: WHERE id > :last_id ORDER BY id ASC LIMIT 20. Multi-column: WHERE (created_at, id) > (:last_created_at, :last_id). The seek is efficient because the database can use a B-tree index to jump directly to the position without scanning earlier rows, unlike OFFSET which scans and discards. Requires a matching index on all seek columns in the same sort order. The term "seek method" comes from Markus Winand's SQL Performance Explained, where he documented this technique as superior to offset for deep pagination.

Relay spec

The GraphQL Relay Cursor Connections Specification — a formal standard for paginated lists in GraphQL APIs. Defines: Connection (the paginated list type, e.g. UserConnection), Edge (wrapper containing node and cursor per item), PageInfo (object with hasNextPage, hasPreviousPage, startCursor, endCursor), and pagination arguments first/after (forward) and last/before (backward). Cursors are Base64-encoded opaque strings. The spec is implemented by Relay, Apollo Client, and most production GraphQL servers. REST APIs have widely adopted the same cursor and pageInfo concepts even without GraphQL, making the Relay spec the de facto standard vocabulary for cursor pagination across ecosystems.

FAQ