engineering

Why Returning Total Counts in API Pagination Is a Trap

The default pagination response in most API design guides includes a total count. This seems obviously useful and is almost always wrong: counting is expensive, the count is stale before the client sees it, and the UX it enables is one most products do not want. The patterns that survive at sca

Anethoth

— 4 min read

The most common pagination response in API design guides looks something like:

{
  "data": [...],
  "pagination": {
    "page": 1,
    "per_page": 50,
    "total": 12483,
    "total_pages": 250
  }
}

This shape is reflexively included in most API designs and reflexively expected by most consumers. It is also one of the most expensive mistakes you can make. The total count is the load-bearing problem: it does not scale, it goes stale immediately, and it enables a UX pattern that is worse than the alternatives.

We made this mistake on the early versions of DocuMint, CronPing, FlagBit, and WebhookVault, and walking it back was harder than getting it right the first time would have been.

The performance problem

Counting rows is not free. SELECT COUNT(*) with a WHERE clause requires the database to evaluate the filter against every candidate row, even if you only want the count. For small tables this is instant. For tables of a few million rows with even modest filters, the count takes substantially longer than the data query — often by a factor of 5-10x.

The naive optimization is to cache the count, but the cache invalidates on every insert, update, or delete that affects the filter. For frequently updated tables, the cache misses constantly and provides no benefit. The slightly less naive optimization is to use an approximate count (PostgreSQL's pg_class.reltuples or MySQL's information_schema.tables.table_rows), but these are only accurate to within a few percent and only update after VACUUM or ANALYZE.

The deeper problem is that pagination queries with both data and count have to execute two queries — the data query and the count query — and the count query is usually the slower of the two. For p99 latency budgets, the count is the load-bearing piece.

The staleness problem

Even if you compute the count perfectly, it is stale by the time the client receives it. Between the moment the API computes the count and the moment the client renders the pagination UI, new rows may have been inserted or existing rows deleted. The total count shown to the user is technically accurate at one point in time but already wrong by the time the user reads it.

This staleness compounds when the user navigates. The total count from page 1 may differ from the total count from page 2 because the underlying data changed between requests. Some pagination libraries handle this by snapshotting the result set at request time using cursor pagination, but offset-based pagination with totals has no such mechanism and produces visibly inconsistent results.

The UX problem

Total counts enable a specific UX pattern: page number selection. "Page 1 of 250" with clickable jumps to any page. This sounds useful but is rarely what users actually do. Real user behavior on paginated lists is:

Scroll or click "next" sequentially until they find what they want or give up. The total count is irrelevant; what matters is whether there are more results after the current page.

Use search or filter to narrow the result set rather than navigating through pages. Page navigation is a fallback when search isn't powerful enough.

Sort by relevance or recency and look at the first few results only. Pages 5 through 250 are dead weight.

The "jump to page 137" use case exists but is much rarer than "next page" or "filter by something specific." Designing the API around the rare case at the cost of the common case is a bad trade.

What to return instead

The minimum viable pagination response answers two questions: what is the next page, and are there more results? Cursor-based pagination handles both cleanly:

{
  "data": [...],
  "pagination": {
    "next_cursor": "eyJpZCI6MTI1MH0=",
    "has_more": true
  }
}

The cursor is opaque to the client; it encodes the position in the result set (typically the ID and sort key of the last row returned). The has_more field tells the client whether to show a "next page" button. There is no total count, no total pages, no jump-to-page-N.

The has_more field is cheap to compute: fetch one extra row beyond the requested limit, then drop it from the response and set has_more to true. This is the limit+1 trick and it costs almost nothing compared to a separate COUNT query.

When you actually need a count

Some use cases genuinely need a count. Admin dashboards showing system-wide statistics. Reports that need to show "showing 1-50 of N results." Analytics queries that compute distinct counts.

For these cases, expose a separate count endpoint:

GET /api/v1/invoices/count?status=paid

This makes the cost explicit. The client knows the count is expensive and can decide whether to call it. The cost is not bundled into every list request, where it gets paid even when nobody looks at the total.

The hybrid approach for moderate-scale APIs

If you cannot give up the total count entirely — for example, if your customers depend on it for invoice numbering or compliance reports — make it opt-in:

GET /api/v1/invoices?include_count=true

Default to no count. Only compute it when the caller explicitly asks. This trains consumers to think about whether they actually need the count, and it makes the cost visible.

What this looks like in practice

For our four products, the pagination shape is:

{
  "data": [...],
  "next_cursor": "eyJpZCI6MTI1MH0=",
  "has_more": true
}

No total count, no total pages, no page number. Customers initially asked for the count; once we explained the cost and showed that the cursor-based UX worked for their actual use cases, nobody pushed back. The query latency improved by 60-80% on the larger tables.

The deeper observation

API pagination design is one of those decisions that compounds over time. A pagination shape baked into version 1 is almost impossible to change in version 2 because every client depends on it. Getting it right the first time means choosing the shape that scales rather than the shape that looks familiar. The standard "page, per_page, total" pattern is familiar but does not scale; the cursor-with-has_more pattern is less familiar but scales to any size of result set and produces a better UX in the common case.

Read more