API Pagination Strategies: Cursor vs Offset

Q: When should I use cursor pagination instead of offset?

Use cursor or keyset pagination for high-volume, sorted, or real-time feeds where deep paging would make OFFSET scan and discard many rows. Offset suits small, bounded datasets where users need to jump to an arbitrary page, and the prompt's decision matrix maps each of your `[endpoint_count]` endpoints to the right choice.

Q: Why is OFFSET slow on large tables?

OFFSET still reads and discards every row it skips, so requesting page 1,000 forces the database to walk through all the preceding rows before returning results. On a table with `[row_count]` rows that gets progressively slower the deeper you page, which is why keyset pagination seeks directly instead.

Q: Does cursor pagination handle rows being added or deleted mid-paging?

Yes, that's a key advantage. Because the cursor encodes the sort field values and record ID and the query uses WHERE clauses, pagination stays stable even when rows change between requests. Offset pagination, by contrast, can skip or duplicate rows when data shifts under it.

Q: Can I paginate a nested resource separately from its parent?

Yes. The prompt paginates nested collections like `/users/:id/[nested_resource]` independently, and supports paginating parent and child resources simultaneously in compound endpoints. It also handles the case where a parent has a very large number of child records efficiently rather than loading them all.

Implement and compare cursor-based and offset-based pagination for APIs with guidance on choosing the right strategy for each endpoint.

Fill in the placeholders

Edit the values, then copy your finished prompt.

[framework]

[database]

[row_count]

[default_page_size]

[max_page_size]

[cursor_encoding]

[sort_fields]

[endpoint_count]

[nested_resource]

[max_children]

Your Prompt

prompt.txt

You are an API design expert. Help me implement robust pagination for a Laravel API that serves data from MySQL 8 with tables containing 10 million rows.

Step 1: Implement offset-based pagination for simple listing endpoints. Create a reusable pagination middleware that accepts page and per_page parameters (default 25, max 100). Query the database with LIMIT and OFFSET. Return the response envelope with data array, pagination metadata (current_page, per_page, total_items, total_pages), and navigation links (first, prev, next, last). Explain the performance implications of OFFSET on large tables.

Step 2: Implement cursor-based pagination for high-volume and real-time feeds. Encode the cursor as a base64-encoded JSON string containing the sort field value and record ID. Create the pagination logic that uses WHERE clauses instead of OFFSET (e.g., WHERE created_at < :cursor_timestamp OR (created_at = :cursor_timestamp AND id < :cursor_id)). Return the response with data, next_cursor, prev_cursor, and has_more boolean.

Step 3: Build a keyset pagination variant for sorted endpoints. Support pagination on created_at, updated_at, popularity, name with configurable sort direction. Handle compound sort keys (e.g., sort by popularity descending then by created_at descending). Generate cursors that encode all sort field values to maintain stable pagination even when data changes between requests.

Step 4: Create a decision matrix for when to use each pagination strategy. For 20 API endpoints, analyze: does the endpoint need total count? Is the data sorted by a stable key? How large is the dataset? Does the consumer need random page access? Map each endpoint to the optimal strategy. Document the trade-offs in the API documentation.

Step 5: Implement pagination for related resources in nested endpoints. When fetching /users/:id/orders, paginate the nested collection independently. Support simultaneous pagination of the parent and child resources in compound endpoints. Handle the case where a parent has 50,000 child records efficiently.

Step 6: Write comprehensive integration tests for each pagination strategy. Test edge cases: empty result sets, single-page results, deleted records between pages, concurrent inserts during pagination, and sort order stability. Benchmark the performance of each strategy at 10 million rows and document the results with query execution plans.

What this prompt does

This prompt makes the AI an API design expert implementing pagination for a [framework] API serving data from [database] with tables up to [row_count] rows. It implements offset-based pagination, cursor-based pagination, a keyset variant, a decision matrix, nested-resource pagination, and integration tests. Offset pagination uses [default_page_size]/[max_page_size] limits, while cursor pagination encodes a [cursor_encoding] cursor and uses WHERE clauses instead of OFFSET.

The structure works because pagination is a quiet performance killer when you default to OFFSET everywhere. On large tables, OFFSET still scans and discards every skipped row, so requesting a deep page on a [row_count]-row table gets progressively slower; the prompt deliberately offers cursor and keyset strategies that seek directly to the cursor position instead. The keyset variant encodes all the sort-field values for [sort_fields] so pagination stays stable even as rows are inserted or deleted between requests, avoiding the skipped and duplicated rows that plague offset paging on live data. The decision matrix then maps each of your [endpoint_count] endpoints to the right strategy based on whether it needs total counts, stable sort keys, large-dataset performance, or random page access.

When to use it

You're choosing pagination per endpoint instead of defaulting to OFFSET everywhere
A large table's listing query degrades as users page deeper
You need stable pagination over feeds that change between requests
You want a decision matrix to justify cursor versus offset for each endpoint
You're paginating nested resources independently of their parent
You want edge cases like deletes and concurrent inserts covered by tests
You need both a count-bearing offset envelope and a high-volume cursor option in one API

Example output

Expect implementation plus guidance: reusable offset middleware with [default_page_size]/[max_page_size] limits, a response envelope, and first/prev/next/last navigation links, cursor-based logic encoding [cursor_encoding] cursors with next_cursor, prev_cursor, and has_more, a keyset variant for compound sorts over [sort_fields], a decision matrix table mapping [endpoint_count] endpoints to strategies with tradeoffs, nested-resource pagination for endpoints like /users/:id/[nested_resource], and integration tests with benchmarks at [row_count] rows including query execution plans. It's structured as labeled [framework] steps.

Pro tips

Don't reach for offset on big tables — deep OFFSET scans and discards every skipped row, which is the slow path the prompt steers you off
Use cursor or keyset pagination for feeds and high-volume endpoints where stability matters more than random page access
Encode all sort-key values in the cursor for [sort_fields] so results stay stable when rows are inserted or deleted mid-paging
Reserve offset for small, bounded datasets where users genuinely need to jump to an arbitrary page
Be honest in the decision matrix: if an endpoint truly needs a total count, that's a real cost cursor pagination doesn't give cheaply
Test deletes and concurrent inserts between pages; that's where naive pagination quietly skips or duplicates rows

Frequently Asked Questions

When should I use cursor pagination instead of offset?

Use cursor or keyset pagination for high-volume, sorted, or real-time feeds where deep paging would make OFFSET scan and discard many rows. Offset suits small, bounded datasets where users need to jump to an arbitrary page, and the prompt's decision matrix maps each of your `[endpoint_count]` endpoints to the right choice.

Why is OFFSET slow on large tables?

OFFSET still reads and discards every row it skips, so requesting page 1,000 forces the database to walk through all the preceding rows before returning results. On a table with `[row_count]` rows that gets progressively slower the deeper you page, which is why keyset pagination seeks directly instead.

Does cursor pagination handle rows being added or deleted mid-paging?

Yes, that's a key advantage. Because the cursor encodes the sort field values and record ID and the query uses WHERE clauses, pagination stays stable even when rows change between requests. Offset pagination, by contrast, can skip or duplicate rows when data shifts under it.

Can I paginate a nested resource separately from its parent?

Yes. The prompt paginates nested collections like `/users/:id/[nested_resource]` independently, and supports paginating parent and child resources simultaneously in compound endpoints. It also handles the case where a parent has a very large number of child records efficiently rather than loading them all.

Need this built for real?

Engr Mejba Ahmed

AI Developer · Software Engineer

I'm Mejba — I design and ship production AI systems, automations, and full-stack apps. If you want this turned into a working solution for your team, let's talk.

Let's Build Together Explore Services

Details

Category: API Development Prompts
Variables: 10
views: 596
copies: 0
Published: Mar 2026

Platform

Claude ChatGPT Cursor

Browse All Prompts

API Pagination Strategies: Cursor vs Offset

Fill in the placeholders

What this prompt does

When to use it

Example output

Pro tips

Frequently Asked Questions

Engr Mejba Ahmed

More in API Development Prompts

Claude/ChatGPT Prompt to Design a Versioned REST API That Won't Break Clients

Claude/ChatGPT Prompt to Decide Between REST and GraphQL with a Framework

Claude/ChatGPT Prompt to Design a Webhook System with Signatures and Retries

Ready to Transform

Your Ideas?

Engr Mejba Ahmed

Hey there!