API Pagination Design Template for PMs

Cursor pagination performs consistently regardless of how far into the dataset the client has scrolled. Offset pagination uses SQL `OFFSET N`, which requires the database to scan and discard N rows before returning results. On page 500 of a million-row table, the database scans 10,000 rows to return 20. Cursor pagination uses a `WHERE` clause with an indexed column, so page 500 is just as fast as page 1. The tradeoff is that clients cannot jump to arbitrary page numbers.

For small to medium tables (under 1M rows), `COUNT(*)` is fast on most databases. For larger tables, consider three options. First, use a cached approximate count that is updated periodically (every minute). Second, only compute the count when the client explicitly requests it via a query parameter (`?include=total_count`). Third, skip the count entirely and rely on `has_next_page` alone. Most API consumers only need "is there more data?" not "exactly how many records exist."

Yes. Choose the strategy per endpoint based on data characteristics. It is common to use cursor pagination for public-facing list endpoints and offset pagination for internal admin APIs where page-number navigation is expected. Document the strategy per endpoint in your [API spec](/glossary/api-first-design) so consumers know what to expect.

Cursors are bound to a specific sort order. If a client changes the sort parameter, their existing cursors become invalid. The simplest approach: return a 400 error if the client sends a cursor with a different sort order than the one that generated it. Document this clearly and instruct clients to restart pagination from the beginning when changing sort order.

20 is a safe default for most APIs. It balances payload size (small enough for mobile clients) against request count (large enough that consumers do not need dozens of requests). Set the maximum at 100 to prevent consumers from requesting their entire dataset in one call. Allow consumers to specify any value between 1 and the maximum. Use the [RICE Calculator](/tools/rice-calculator) to weigh the engineering cost of supporting flexible page sizes against the benefit to different consumer segments. ---