Paginate search results

Web applications often paginate data as it's presented to users. The end user receives one page of results, and when they navigate to the next page, the next batch of results is retrieved and presented. This page describes how to add pagination to search results when performing a full-text search in Spanner.

Pagination options

Each page of a paginated result set is fetched by an independent query, and Spanner keeps no state between pages. Anything that changes between consecutive queries, such as the data or the sort order, can make the results inconsistent: when the application moves to the next page, rows can be skipped, or rows from the previous page can be repeated. This behavior applies to all paginated queries in Spanner, though the pagination method you choose determines how much these changes affect the results.

There are two ways to implement paginated queries in Spanner: key-based pagination (recommended) and offset-based pagination.

Key-based pagination is a method for retrieving search results in smaller, more manageable chunks while ensuring consistent results across requests. A unique identifier (the "key") from the last result of a page is used as a reference point to fetch the next set of results.

Spanner generally recommends using key-based pagination, except when paginating ranked search results. While offset-based pagination is easier to implement, it has two significant drawbacks:

  • Higher query cost: Offset-based pagination has to retrieve and discard the results of all preceding pages, leading to increased costs and decreased performance.
  • Inconsistent results: In paginated queries, each page is typically retrieved at a different read timestamp. For example, the first page might come from a query at 1 PM, and the next from a query at 1:10 PM. Between queries, any row inserted or deleted before the current offset shifts the rows that follow it, so rows are skipped or repeated across pages.

Key-based pagination, on the other hand, uses a unique identifier (key) from the last result of a page to fetch the next set of results. This approach ensures efficient retrieval and more consistent results: the next page resumes from the last key of the previous page, so only concurrent writes that move rows across that boundary affect the results. For example, a newly inserted row that sorts before that key is skipped, while a previously returned row that is updated to sort after that key is repeated.

To provide stability in page results, the application could issue all queries for different pages at the same timestamp. This, however, might fail if the query exceeds the version retention period (the default is 1 hour). For example, this failure happens if version_gc is one hour, and the end user fetched the first results at 1 PM and clicked Next at 3 PM.

Use key-based pagination

Key-based pagination remembers the last item of the previous page and uses it as a starting point for the next page query. To achieve this, the query must return the columns specified in the ORDER BY clause and limit the number of rows using LIMIT.

For key-based pagination to work, the query must order results by some strict total order. The easiest way to get one is to choose any total order and then add tie-breaker columns, if needed. In most cases, the total order is the search index sort order and the unique combination of columns is the base table primary key.

Using our Albums sample schema, for the first page, the query looks like the following:

GoogleSQL

SELECT AlbumId, ReleaseTimestamp
FROM Albums
WHERE SEARCH(AlbumTitle_Tokens, "fifth symphony")
ORDER BY ReleaseTimestamp DESC, AlbumId
LIMIT 10;

PostgreSQL

SELECT albumid, releasetimestamp
FROM albums
WHERE spanner.search(albumtitle_tokens, 'fifth symphony')
ORDER BY releasetimestamp DESC, albumid
LIMIT 10;

The AlbumId is the tie breaker since ReleaseTimestamp isn't a key. There might be two different albums with the same value for ReleaseTimestamp.

To resume, the application runs the same query again, but with a WHERE clause that restricts the results from the previous page. The additional condition needs to account for key direction (ascending versus descending), tie breakers, and the order of NULL values for nullable columns.

In our example, AlbumId is the only key column (in ascending order) and it can't be NULL, so the condition is the following:

GoogleSQL

SELECT AlbumId, ReleaseTimestamp
FROM Albums
WHERE (ReleaseTimestamp < @last_page_release_timestamp
    OR (ReleaseTimestamp = @last_page_release_timestamp
        AND AlbumId > @last_page_album_id))
    AND SEARCH(AlbumTitle_Tokens, @p)
ORDER BY ReleaseTimestamp DESC, AlbumId ASC
LIMIT @page_size;

PostgreSQL

This example uses query parameters $1, $2, $3 and $4 which are bound to values specified for last_page_release_timestamp, last_page_album_id, query, and page_size, respectively.

SELECT albumid, releasetimestamp
FROM albums
WHERE (releasetimestamp < $1
    OR (releasetimestamp = $1
        AND albumid > $2))
    AND spanner.search(albumtitle_tokens, $3)
ORDER BY releasetimestamp DESC, albumid ASC
LIMIT $4;

Spanner interprets this kind of condition as seekable. This means that Spanner doesn't read the index for documents you're filtering out. This optimization is what makes key-based pagination much more efficient than offset-based pagination.

Use offset-based pagination

Offset-based pagination leverages the LIMIT and OFFSET clauses of SQL query to simulate pages. The LIMIT value indicates the number of results per page. The OFFSET value is set to zero for the first page, page size for the second page, and double the page size for the third page.

For example, the following query fetches the third page, with a page size of 50:

GoogleSQL

SELECT AlbumId
FROM Albums
WHERE SEARCH(AlbumTitle_Tokens, "fifth symphony")
ORDER BY ReleaseTimestamp DESC, AlbumId
LIMIT 50 OFFSET 100;

PostgreSQL

SELECT albumid
FROM albums
WHERE spanner.search(albumtitle_tokens, 'fifth symphony')
ORDER BY releasetimestamp DESC, albumid
LIMIT 50 OFFSET 100;

Usage Notes:

  • The ORDER BY clause is highly recommended to ensure consistent ordering between pages.
  • In production queries, use query parameters rather than constants to specify LIMIT and OFFSET to make query caching more efficient. For more information, see Query parameters.

Paginate ranked search results

Spanner recommends offset-based pagination for ranked queries, which order results by the value of the SCORE function rather than by key columns of the table. Although you can include the previous page's last score in the WHERE clause for the next page, that condition isn't seekable and therefore doesn't provide the cost and performance benefits of key-based pagination. In addition, SCORE values aren't comparable across scorer versions, so offset-based pagination produces fewer skipped or repeated rows when the scorer version changes.

To paginate ranked results, order by the score with the primary key as a tie breaker, and page with LIMIT and OFFSET. For example, the following query fetches the first page with a page size of two:

GoogleSQL

SELECT
  AlbumId,
  SCORE(AlbumTitle_Tokens, @p) AS Score
FROM Albums
WHERE SEARCH(AlbumTitle_Tokens, @p)
ORDER BY Score DESC, AlbumId
LIMIT 2 OFFSET 0;

PostgreSQL

This example uses query parameter $1, which is bound to the query string.

SELECT
  albumid,
  spanner.score(albumtitle_tokens, $1) AS score
FROM albums
WHERE spanner.search(albumtitle_tokens, $1)
ORDER BY score DESC, albumid
LIMIT 2 OFFSET 0;

The value of SCORE depends on the scorer version. Spanner uses the database's default scorer version unless the query sets one. A new scorer version can change the ordering of results, so if the scorer version changes between two pages of the same paginated query, the pages can be inconsistent. For example, suppose the matching albums have the following scores under two scorer versions:

AlbumId Score with version 1 Score with version 2
1 0.9 0.7
2 0.8 0.95
3 0.7 0.6
4 0.6 0.85

With version 1 and a page size of two, the first page returns albums 1 and 2. If the scorer version changes to version 2 before the second page is fetched, the second page is computed with OFFSET 2 on the version 2 order, in which the albums are ranked 2, 4, 1, 3. As a result, the second page returns albums 1 and 3, so album 1 is repeated and album 4 is skipped.

Scorer version changes are infrequent: the database's default scorer version changes only when you update it or when Spanner migrates the database to a later default version, and only paginated queries that are in progress at that moment are affected. For most applications, this brief inconsistency is acceptable, and Spanner recommends using the default scorer version so that you always benefit from the latest scorer improvements.

If you need more control over the change, you have two options:

  • Database level: Set the database's default scorer version yourself, as described in Set the database's default scorer version. Setting this option doesn't avoid the inconsistency, but it lets you choose when the switch happens—for example, during a low-traffic period—or stay on the current version until you're ready.
  • Query level: Pin the scorer version in the query and carry it in your pagination state, as described in the following steps. This approach keeps in-progress paginated queries on the scorer version they started with, so the switch doesn't affect them. However, these queries remain subject to rows shifting because of concurrent writes, as with any offset-based pagination. This approach is more complex to implement and isn't necessary for most applications.

To pin the scorer version at the query level, complete the following steps:

  1. Set the scorer version explicitly in the SCORE function's options argument. Use the same query for every page, with the offset set to 0 for the first page:

    GoogleSQL

    SELECT
      AlbumId,
      SCORE(AlbumTitle_Tokens, @p,
            options=>JSON_OBJECT('version', @score_version)) AS Score
    FROM Albums
    WHERE SEARCH(AlbumTitle_Tokens, @p)
    ORDER BY Score DESC, AlbumId
    LIMIT @page_size OFFSET @offset;
    

    PostgreSQL

    This example uses query parameters $1, $2, $3, and $4, which are bound to the query string, the scorer options as a JSONB value such as {"version": 2}, the page size, and the offset, respectively.

    SELECT
      albumid,
      spanner.score(albumtitle_tokens, $1, options => $2) AS score
    FROM albums
    WHERE spanner.search(albumtitle_tokens, $1)
    ORDER BY score DESC, albumid
    LIMIT $3 OFFSET $4;
    

    For the first page, take the scorer version from your application's configuration; typically it's the same value as your database's default scorer version, so that paginated and other queries rank the same way.

  2. Store the scorer version you bound, together with the offset of the next page, in your pagination state, for example in a session cookie or in the opaque page token returned by your API.

  3. For every subsequent page, pass the stored scorer version in the query rather than reading the version from your application's current configuration. This approach keeps the paginated results consistent across scorer version changes, whether the database's default changes or you update your application's configuration.

The scorer version pinned in the query takes precedence over the database's default scorer version. To move pinned queries to a newer scorer version, change the value in your application's configuration.

What's next