Paginating the Wrong Thing: Design the Endpoint for the Question, Not the Entity

On October 14, GitHub published an engineering post about rebuilding how its API serves star history. The headline comparison is memorable: to draw the growth curve of a repository with about 251,000 stars, a tool needed roughly 2,511 requests against the old stargazers endpoint, and needs about 24 against the new one. The more transferable detail is the reason. Callers never wanted the list of people. They wanted a count per day, and the API made them download the list and compute it themselves.

Most organizations have an endpoint with that shape: a paginated list of entities that every consumer immediately folds into a sum, a chart or a threshold check. Each consumer pays the transfer cost, the server pays the query cost on every page, and the data that was never needed gets exposed along the way. This article uses GitHub’s account as the case, checks its reasoning against independent database and API-security sources, and ends with a review you can run on your own list endpoints.

What GitHub says went wrong

According to the GitHub post, the old stargazers endpoint returned pages of up to 100 people, with a timestamp for each star when a caller asked for the right media type. Tools reconstructed a growth curve by collecting every page and discarding the identities. That caused two problems the authors name directly: bad actors scraped the public lists for spam and phishing, and the database did heavy work on every request for popular repositories.

The database cost had a specific mechanism. Each request counted all current stars to calculate the page count, even though the caller received a single page. In the week before access was restricted, nearly 37,000 of these count queries were logged as slow, averaging nearly 3 seconds each, and database safeguards killed more than 1,000 of them. Restricting stargazer and watcher lists to administrators and collaborators reduced the load, with about 60 percent fewer stargazer requests and about 80 percent less time spent on the count query. The post also reports that the same on-demand counting existed on the stargazers page on github.com, and that switching it to the cached star count took its slow queries from hundreds a week to zero.

The root cause the authors identify is a modeling one. The star table stores only the current set of stars, and removing a star deletes its row, so it was never designed as an event log. Everyone downstream treated it as one anyway.

Why the obvious fixes did not work

The post describes two approaches that were considered and rejected, and they are the instructive part.

The first was to expose event records without identities. That solves the privacy problem, but a consumer still has to page through many thousands of individual records to get a count. The second was to aggregate in the database and paginate the aggregate. That sounds right, yet the database still grouped and sorted every matching star before applying the limit and offset, so 24 requests would have reprocessed all the stars, roughly 6 million rows in total for the example repository.

That second failure has two parts. One is the cost of recomputing the whole aggregate for every page, which is specific to aggregating before paginating. The other is the cost of offset itself, which is a general property of offset pagination, not a GitHub quirk. The PostgreSQL documentation warns that rows skipped by an offset still have to be computed inside the server, so a large offset can be inefficient, and it requires a unique ordering for a stable result. Markus Winand’s Use The Index, Luke explains the same cost from the index side: the database must fetch and order the skipped rows before it can return the next ones, and the seek method that filters on the last key seen avoids that work at the price of not being able to jump to an arbitrary page. Taken together, they say the cost of page N depends on N, which is why a smaller response does not always mean a cheaper query. Keyset pagination addresses the offset part only. It would not, on its own, stop a server from regrouping every row on each request.

GitHub’s actual fix changed the unit of pagination. The new endpoint returns up to 30 weeks of daily star counts per request, with no identities, and a page maps to a calendar-week range on an indexed creation timestamp. Stars are filtered to the requested weeks before they are grouped into daily counts. Stepping the page number moves backward through history. For the example repository that means about 24 requests, and the database examines each star about once, roughly 251,000 rows across the whole history. The response deliberately mirrors the shape of the existing weekly commit activity endpoint.

The pattern: the unit of pagination should match the unit of the question

flowchart TD
  A[What do callers do with the list?] --> B{Fold it into a count, sum or trend?}
  B -->|Yes| C[Return the aggregate]
  C --> D[Paginate by time or key range, filter before grouping]
  D --> E[Document staleness and edge cases]
  B -->|No, they act on records| F[Return records]
  F --> G[Use keyset pagination, cap page size]
  G --> H[Return only the fields the use case needs]

Pagination protects the server from returning too much in one response. It does not protect the system from the total work a consumer causes by reading everything. When every real caller reads every page and sums it, the effective operation is a full scan spread across many requests, and the pagination only hides its size. The tell is in your access logs: clients that walk a collection from the first page to the last, at regular intervals, and then discard most of what they received.

What this looks like in your own system

The following is an illustrative scenario, not a client case and not a measurement. Imagine an internal API for support tickets that lists tickets with limit and offset, and a dashboard that shows tickets opened per day by walking the whole collection. The query behind each page might look like this:

-- Per-page query: cost grows with the page number
SELECT id, requester_email, subject, created_at
FROM tickets
WHERE team_id = $1
ORDER BY created_at, id
LIMIT 100 OFFSET $2;

The dashboard needs none of the requester data and none of the subjects. The aggregate version asks the question the dashboard is really asking, and the time filter comes before the grouping so the index on the creation time does the narrowing:

-- Aggregate endpoint: one bounded time range per page
SELECT date_trunc('day', created_at) AS day, count(*) AS opened
FROM tickets
WHERE team_id = $1
  AND created_at >= $2
  AND created_at <  $3
GROUP BY 1
ORDER BY 1;

Whether this is cheaper in your system depends on your indexes, your data distribution and your database, so check the plan on production-sized data rather than trusting the shape of the query. GitHub’s post gives a good reminder of why: the first version of their own fix would have been correct and still slow. They also state a limit that carries over. A time range does not cap the number of rows inside it, so a day with an unusual spike is still expensive.

The side benefit: less data exposed

The aggregate endpoint is also a smaller attack surface. The OWASP API Security Top 10 covers APIs that return more object properties than a caller should receive, and advises keeping returned data structures to the bare minimum. A list endpoint that returns full records so that clients can count them is an easy way to break that advice. OWASP’s entry on unrestricted resource consumption adds the cost side: it advises validating the parameter that controls how many records come back and rate limiting, and it points out that requests consume shared resources and can raise infrastructure bills. Capping page size is necessary, but as the star history example shows, it is not sufficient when the access pattern is a full walk.

In GitHub’s case the identity list was both the thing worth scraping and the thing expensive to serve, so dropping it from the answer helped on both fronts. When a design change reduces the data you hold out and the work you do for the same reason, it is usually a sign that you have found what the endpoint was actually for.

The costs of the aggregate

Replacing a list with an aggregate is a trade, and the post is candid about it. Calendar boundaries shape what each page can return. Removed stars are not reflected, because removal deletes the row. Those are the consequences of the data model, not of the endpoint, and they belong in the documentation as part of the contract.

If you precompute rather than compute on demand, you add the staleness question. PostgreSQL’s materialized view documentation says plainly that the stored data is not always current, and that sometimes current data is not needed. Its example summarizes historical sales and notes that readers may not care about incomplete data for the current date. Decide which of those your consumers are before you choose a refresh schedule, and tell them.

There are three other costs worth naming:

• Flexibility. A consumer that later wants a different breakdown, by hour or by label, cannot get it from a daily count. Each new question can become a new endpoint, or a parameter that you must then keep bounded.
• Contract drift. An aggregate encodes definitions, such as what counts as opened or which timezone defines a day. Two teams computing the same number differently is a worse failure than an expensive endpoint.
• A second path to maintain. The record-level endpoint usually remains for the callers who act on individual items. Make sure both are protected by the same authorization rules.

A review for your list endpoints

You can run this in an afternoon with access logs and a query plan viewer.

1. Find the full walkers. From access logs, list the clients that request every page of a collection in sequence. These are the first candidates, because they are paying the cost of an aggregate through the wrong interface.
2. Ask what they compute. For each, talk to the owner or read the code. If the answer is a count, a sum, a latest value or a trend, you have an aggregate that should be an endpoint.
3. Measure the cost by page number. Run the page query at page 1 and at a deep page on production-sized data. If the deep page is much slower, offset is the problem, and keyset pagination is the first fix for the callers who need records.
4. Check the count. If each request computes a total to report the number of pages, as GitHub’s did, measure that separately and consider returning a cached or approximate total, or none at all.
5. Filter before grouping. For new aggregates, confirm in the plan that the range condition uses an index before the grouping step.
6. Trim the fields. For the record-level endpoint, list which fields any caller actually reads, and drop or gate the rest.
7. Write the contract. State the time boundaries, the effect of deletions, the freshness, and the cost bound per request.

When this is not worth doing

If a list endpoint is small, rarely walked in full and has callers who do need the records, the redesign adds surface area for no gain. The same holds when the data fits in memory and the query is cheap at any depth. Aggregates also do not remove the need for the underlying records, so if your consumers need drill-down from the number to the items behind it, the record-level path with keyset pagination remains. The point is not that aggregates beat lists. It is that the interface should be chosen by what callers do, and the logs usually show what that is.

If your APIs have grown list endpoints that callers only ever sum, chart or count, we help engineering teams read access logs and query plans to find those patterns, design aggregate and keyset-paginated alternatives, and plan a migration that does not break existing consumers. Talk with us about your API and query design.