Why LIMIT and OFFSET didn't make GitHub's star history query any cheaper

What star history is, and where the data came from

A star-history chart plots how many people have starred a GitHub repository over time. Maintainers use it as a rough signal of interest: did a launch, a release or an announcement change how fast a project was growing? Tools such as star-history.com draw these charts for any public repository.

GitHub's API never had an endpoint for that curve. What it had was the stargazers endpoint, GET /repos/{owner}/{repo}/stargazers, which answers a different question: who starred this repository? It returns a paginated list of people, at most 100 per page, and when the request asks for a custom media type each entry also carries starred_at, the moment that person starred it. To build a curve, a tool requested every page, kept the timestamps and threw the identities away.

That data model has a quirk worth knowing. GitHub stores the current set of stars, not a log of events: when someone removes a star, the record is deleted. So any curve built this way only ever shows people who still have their star.

Why the old endpoint was expensive

The cost grew with the repository's popularity. For react/react, with about 251,000 stars, the full curve took about 2,511 requests, so tools often hit rate limits or sampled pages instead of reading them all.

Each of those requests was also heavier than it looked. GitHub says a bug in the pagination implementation made every request count all of the repository's current stars to work out how many pages existed, even though the caller got back only one page of up to 100 people. On a popular repository that meant counting many thousands of rows per request. In the week before GitHub restricted access, nearly 37,000 of these count queries ran slowly enough to be logged, averaging nearly 3 seconds each, and database safeguards killed more than 1,000 of them, which contributed to request timeouts.

After the restriction described below, the database served about 60% fewer stargazer requests, and the total time it spent on that count query fell by about 80%. Those two figures are the effect of restricting access, not of the new endpoint.

The video leaves the count bug out for time. It is the same lesson in another form: the work per request scaled with the whole repository rather than with the page being returned.

Why the stargazer list was restricted

Stargazer lists were public, and bad actors were scraping them in bulk for spam and phishing campaigns. GitHub's first move was to restrict stargazer and watcher lists to repository administrators and collaborators. Other callers now get an empty response or a 403 Forbidden.

That cut off the tools that used the list for star history, most of which never displayed the usernames at all. GitHub talked to maintainers and tool builders, including Star History's maintainer, and found what they actually needed: counts and dates at a useful resolution, not identities. In GitHub's words, identity only looked like part of the contract because every timestamp arrived attached to a user.

The first idea: keep the events, drop the names

GitHub's first prototype kept one record per star and returned starred_at without the identity to callers who lacked collaborator access. It was the quickest path: a small change to existing serialization and authorization, and it closed the identity-harvesting route while letting existing tools keep working.

It was rejected because it left the cost where it was. Consumers would still have to page through many thousands of individual records to build an aggregate curve, and the new use case would stay tied to an endpoint and pagination model designed for listing people rather than retrieving trends.

Why aggregating first, then paginating, didn't help

The obvious next step is to let the database do the counting: group stars into daily or weekly buckets, sort the buckets and paginate the much smaller result. Far fewer rows come back, so it should be cheaper.

The problem is where pagination happens. In a straightforward SQL query the database first finds every matching row, groups them, sorts the groups, and only then applies LIMIT (how many buckets to return) and OFFSET (where the page starts). GitHub sums the order up like this:

all matching stars -> bucket every row by day or week -> sort every bucket -> OFFSET/LIMIT -> response

An illustrative query of that shape (ours, not GitHub's code) looks like this:

SELECT DATE(created_at) AS day, COUNT(*) AS stars
FROM stars
WHERE repository_id = ?
GROUP BY day
ORDER BY day DESC
LIMIT 210 OFFSET 420;

The LIMIT shrinks the JSON response. It does nothing to the grouping and sorting that already happened. For react/react, reading the full history this way would have meant 24 requests that each reprocess all of its stars, roughly 6 million rows examined for one chart.

The design GitHub shipped: pages as date ranges

The shipped endpoint paginates star history by calendar week. For each request it translates page and per_page into a range of created_at values, filters the stars to those weeks using an index on that column, and only then groups the matching rows into daily counts. A response covers up to 30 weeks, and a higher page moves backward through the repository's history.

indexed date range -> group rows in that range -> daily buckets -> response

Again as an illustration of the shape (ours):

SELECT DATE(created_at) AS day, COUNT(*) AS stars
FROM stars
WHERE repository_id = ?
  AND created_at >= ? AND created_at < ?   -- the 30 weeks this page covers
GROUP BY day
ORDER BY day;

Because the date filter runs first and the index lets the database jump straight to the start of the range, each request processes only the weeks it returns. React's full history takes about 24 requests instead of about 2,511, and the database examines each star about once, roughly 251,000 rows across the whole history. No response contains any identity.

The week-based shape matches GitHub's existing weekly commit activity endpoint, GET /repos/{owner}/{repo}/stats/commit_activity, and fits how maintainers read the chart: across weeks, or around a known release or event.

What the new endpoint still can't do

GitHub lists the trade-offs that remain, and calls them part of the API contract:

• Dense weeks. The time range limits a request to a period, not to a number of stars. A week in which a project goes viral can still produce a slow or expensive query.
• Calendar boundaries. Pages follow calendar weeks rather than arbitrary dates. GitHub names calendar boundaries as one of the details that affect what the endpoint returns, without saying more.
• Removed stars. Because unstarring deletes the record, the history reflects current stars only. A star that was added and later removed never appears.

The same pattern elsewhere on GitHub

Comparing the old and new API paths turned up other places that counted stars on demand. The repository stargazers page on GitHub.com did the same thing and now uses the repository's cached star count; slow queries from that page, which had numbered in the hundreds each week, dropped to zero. GitHub says it has recently shipped a similar fix to other stargazer API endpoints.

Applying it to your own paginated queries

This section is Breakpoint's reading, not GitHub's.

• Find where the page gets decided. If the only thing that narrows a query is LIMIT/OFFSET at the end, the database does the full job on every page. Run EXPLAIN (or your database's equivalent) and look at how many rows each step examines, not how many come back.
• Filter before you aggregate. A WHERE on an indexed column narrows the rows before GROUP BY and ORDER BY touch them. A HAVING or a LIMIT narrows only the result.
• Large offsets cost even without grouping. A plain ORDER BY id LIMIT 100 OFFSET 100000 still walks past the skipped rows. Keyset (cursor) pagination, WHERE id > :last_seen ORDER BY id LIMIT 100, turns the page into a range on an index, the same move GitHub made with dates.
• Watch for hidden totals. A "page 3 of N" total can cost a full count on every request, which is exactly the bug GitHub found. Cache it, estimate it, or drop it.

Terms

Stargazer

GitHub's name for a person who has starred a repository. The stargazers endpoint lists them.

starred_at

The timestamp of a star, returned by the stargazers endpoint when the request asks for the star media type.

LIMIT and OFFSET

SQL clauses that choose how many rows to return and how many to skip. They apply after filtering, grouping and sorting.

Aggregate then paginate

Grouping all rows into buckets first and paging through the buckets. Cheap to send, expensive to compute.

Index

A sorted structure on a column that lets the database jump to a value or range without reading every row.

created_at range

The date window, on an indexed column, that each page of the new endpoint covers.

Keyset pagination

Paging with a WHERE condition on the last value seen instead of an OFFSET, so each page is an index range.

Rows examined

How many rows the database read to answer a query, as opposed to how many it returned.

Questions

// further reading

• From 2,500 requests to 24: Rebuilding star history without the stargazers ↗: GitHub's own account, with the load charts this page describes in words
• Starring in the GitHub REST API ↗: the stargazers endpoint and the star media type
• Repository statistics in the GitHub REST API ↗: the weekly commit activity endpoint the new response is shaped after
• Star History ↗: the best-known star-history chart tool