Design Cursor-Based Pagination Logic for High-Volume APIs

Get a complete implementation plan for cursor-based pagination including SQL queries, cursor encoding logic, and strategies for handling record deletions.

Prompt

Prompt
Act as a Senior Backend Architect. Design a cursor-based pagination system for a high-volume API endpoint. 1. Use keyset pagination with unique, indexed columns for sorting. 2. Define an opaque cursor format using base64 encoding of JSON objects containing the sort values and primary ID. 3. Address edge cases: explain how to handle records deleted between pages and records with identical sort keys. 4. Provide the exact SQL query structure using a WHERE clause that handles composite sorting. Output format: 1. Architecture Overview (a brief summary of the keyset approach). 2. Cursor Encoding Spec (fields to include and encoding method). 3. SQL Query Pattern (the base query template). 4. Edge Case Handling (a bulleted list explaining deletions and duplicate sort keys). This is technical architectural advice for implementation; consult with your database administrator regarding specific index optimization for your schema.

Example output

At a glanceCursor pagination implementation planFor a 50 million entry activity feed
  1. Architecture overviewKeyset pagination on a composite index (created_at, id)
  2. Cursor encodingBase64-encoded JSON object, opaque to the client
  3. SQL query patternRow comparison on (created_at, id) with LIMIT 50
  4. Edge case handlingDuplicate keys, deleted records, B-Tree indexing

Example for: a high-volume activity feed containing 50 million log entries.

1. Architecture Overview

This design uses keyset pagination, which filters results based on the last seen record instead of skipping rows. This ensures O(log n) performance even at high offsets by utilizing a composite index on (created_at, id).

2. Cursor Encoding Spec

The cursor is a Base64-encoded string of a JSON object. Example: eyJjcmVhdGVkX2F0IjoiMjAyMy0xMC0yN1QxNDozMDowMFoiLCJpZCI6OTg3NjV9. This approach keeps the cursor opaque to the client and prevents users from guessing sequences.

3. SQL Query Pattern

SELECT * FROM activities WHERE (created_at, id) < ('2023-10-27T14:30:00Z', 98765) ORDER BY created_at DESC, id DESC LIMIT 50;

4. Edge Case Handling
  • Duplicate Sort Keys: By including the primary ID in the composite index and the WHERE clause, you guarantee a deterministic order even if multiple records have the identical timestamp.
  • Deleted Records: Keyset pagination handles deletions naturally. If the record used for the cursor is deleted, the query will simply return the next available record that satisfies the less-than condition.
  • Indexing Strategy: Create a B-Tree index on (created_at DESC, id DESC). This allows the database to perform a range scan rather than a full table scan or sort, keeping latency under 10ms for millions of rows.
Written by AI from this prompt. Your results will vary.

Tips and common edits

Why this works

  • Forces a deterministic sort order using composite keys.
  • Provides a concrete base64 encoding strategy for opaque cursors.
  • Addresses performance issues by avoiding expensive offset operations.

Use this prompt when building APIs that need to handle millions of rows efficiently without the performance degradation of OFFSET. Copy this into ChatGPT, Claude, or Gemini to get a production-ready specification.

Common edits

  • Change the sort keys to match your specific database schema columns.
  • Add a requirement to support reverse pagination if your UI needs 'previous' button functionality.

Make it repeatable

Keep using this prompt without pasting it in every time.

Keep exploring

More in Coding