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
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
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.
Prompt details
- By
- Prompts-GPT editors
- Published
Did this prompt work?
Keep exploring
More like this
Fix N+1 Database Query Patterns in ORM Code
This prompt identifies N+1 query issues in your code, provides the optimized eager-loading syntax, and calculates the reduction in database round-trips.
Debug SQL query errors and incorrect row counts
Find syntax errors, incorrect joins, and silent null filter bugs in database queries. Use it when a query returns syntax errors or unexpected row counts.
Find and Fix Data Quality Issues with Ready-to-Run SQL Checks
Paste your schema and get SQL queries that catch duplicates, NULLs, orphaned foreign keys and out-of-range values, each with what a bad result means and a fix.
Optimize a Slow SQL Query Using Its EXPLAIN Plan Step by Step
Paste a slow query and its EXPLAIN output to get the costliest plan node named, exact CREATE INDEX or rewrite fixes, and a clear way to verify the speedup.
Turn a Feature Description into a REST API Endpoint Spec
Paste a feature description and get a REST API spec with a route table, request and response fields, status codes, exact validation rules, and open questions on auth and pagination.
Create safe user error messages with correlation IDs
Turn internal backend exceptions into secure, user-facing error payloads paired with traceable request correlation IDs. Use it to build clean API error contracts.
- Design a resilient background job and retry system
- Generate complete API documentation from route code
- Design a Scalable API Rate Limiting Architecture
- Design Standardized REST API Error Contracts Using RFC 7807
- Design a Normalized Database Schema from a Plain Feature Description
- Explain a Complex SQL Query in Plain English, Step by Step