Why API pagination is important Link to heading
API pagination is one of those tricky details that contributes a lot to the performance of a website. I use it frequently as an acurate indicator of an interview candidate. The ones who, when defining the API contract, mention API pagination tend to be the ones who perform better later on.
Most of the internet is CRUD-based, i.e. the majority of websites are just plain forms with a Create, Read, Update or Delete. Of course, there are more nuanced business rules here and there (and I don’t want to sound like a project manager trying to negotiate a deadline); however, if we take most of the edge cases and exception scenarios out, we end up with a CRUD flow. As a consequence, a great performance impact comes from how well the four operations are handled, especially the READ operation which could be a simple index hit or a full table scan.
A /ListAll operation on a massive table could generate a lot of database CPU work and even more network traffic in the database-to-server and in the server-to-client connections. The pagination comes to the rescue breaking the workload in predictable chunks (pages) so the user (or the application) can decide if he/she wants to pay the toll to get more data.
Page-size (offset-limit) as industry standard Link to heading
The current standard for pagination is strongly based on page and limit https://my-endpoint/api/collections?page=3&size=30 that defines the page size and the page number. Here are some other examples:
GitHub (REST API): GET https://api.github.com/user/repos?page=2&per_page=30
Spotify API: GET https://api.spotify.com/v1/artists/{id}/albums?offset=20&limit=10
Unsplash API: GET https://api.unsplash.com/photos?page=3&per_page=10
The pagination based on chunks of a query (page-size or offset-limit) are the easiest form of creating a pagination feature. It mimics the database query structure and are the default of most of the frameworks.
The issues with the page-size Link to heading
Even though the page-size is a quick-win (and it should always be considered), it comes with a performance drawback when the resource table is massive. The default implementations of the page-size is converting the values to query params:
SELECT * FROM products ORDER BY id LIMIT $1 OFFSET $2
the issue with this approach comes from the OFFSET keyword. Instead of using an index to jump directly to the required record (and navigating horizontally to the leaves), the database engine needs to go through all the previous records until it reaches the offset index and this can impact the lookup speed when requesting high page numbers.
Another issue with page and offset comes from the deletion/insertion of records. Relying on offsets requested indivually for each page could cause some records to be missing. Let’s say that we call for page 1 with size 10, we get the ids from 0 til 9. If we delete the record 7 and ask for page 2, the id number 10 that would be on page two is now on page one and does not come back in the requested page 2.
Token pagination as an alternative Link to heading
The alternative to the OFFSET is an Index. Search for an indexable/queryable column (or group of columsn) allows the database to leverage its engine and get the requested set of tuples directly instead of scanning the first records. In addition to that, having the id and the updated_at columns used as tokens we can avoid the missing records when adding or deleting records.
Here it comes the concept of page_token. A page token is an encoded anchor that maps to the first record of an indexed queried filter. Instead of going throught a list skipping the viewed rows, the page_token contains an encoded “where” clauses that is going to leverage the database engine to get the next subset (page) of a list.
If the http get does not contain the pagination_token, the service assumes that it is requesting the first page. If it does contain the page_token, then it uses it to get the next page encoded in the token.
Some examples:
Twitter: GET https://api.twitter.com/2/tweets/search/recent?query=tech&pagination_token=8675309
Youtube: GET https://www.googleapis.com/youtube/v3/search?part=snippet&q=coding&pageToken=CAUQAA
Zoom: GET https://api.zoom.us/v2/users?page_size=30&next_page_token=T4x9LzA2VkpSbmMxY1I2Sw==
The page token is mostly used in infinite scroll pages on which getting to the page number hundred is more common.
How the token works behind the scenes Link to heading
It’s commom to use the sequential Id with an updated_at column to compose the page token (id, updated_at).
Here’s an example of a pagination made with page token
type pageToken struct {
ID int64 `json:"id"`
UpdatedAt time.Time `json:"updated_at"`
}
func (s *server) tokenPagination(w http.ResponseWriter, r *http.Request) {
limit, err := parseLimit(r.URL.Query().Get("limit"))
if err != nil {
http.Error(w, err.Error(), http.StatusBadRequest)
return
}
query := `SELECT id, external_id, name, description, price, create_at, updated_at FROM products`
args := []any{limit}
encodedToken := r.URL.Query().Get("page_token")
if encodedToken != "" {
var token pageToken
if err := decodeToken(encodedToken, &token); err != nil {
http.Error(w, "invalid page_token", http.StatusBadRequest)
return
}
query += " WHERE (updated_at, id) > ($2, $3)"
args = append(args, token.UpdatedAt, token.ID)
}
query += " ORDER BY updated_at, id LIMIT $1"
items, err := s.products(r.Context(), query, args...)
if err != nil {
http.Error(w, "could not list products", http.StatusInternalServerError)
return
}
response := pageResponse{Items: items, Limit: limit}
if len(items) == limit {
last := items[len(items)-1]
response.NextPageToken = encodeToken(pageToken{ID: last.ID, UpdatedAt: last.UpdatedAt})
}
writeJSON(w, http.StatusOK, response)
}
The token is built using the composite index of (id,updated_at), and queried from it. The last record is them used to encode the next_token that is going to return in the response and the client should store it and use it to get the next page (if needed).
In addition to the performance improvement, the updated_at helps with the consistency issue. A record that is deleted or added is not going to impact the pagination since it is ordering by index instead of skipping an offset.
Pros and Cons of Token Pagination Link to heading
Token pagination solves a performance issue that is not common to the mundane websites. The performance impact of a page navigation via offset is going to happen when the dataset is unusually big. Therefore, it adds complexity and make the pagination one-way only (without the possibility to skip to an specific page) but helps with performance and consistency.