Note: This feature is available with the Spanner Enterprise edition and Enterprise Plus edition. For more information, see theSpanner editions overview.

Web applications often paginate data as it's presented to users. The end userreceives one page of results, and when they navigate to the next page, the nextbatch of results is retrieved and presented. This page describes how to addpagination to search results when performing afull-text search in Spanner.

Pagination options

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

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

Spanner generally recommends using key-based pagination. Whileoffset-based pagination is easier to implement, it has two significantdrawbacks:

1. Higher query cost:Offset-based pagination repeatedly retrieves anddiscards the same results, leading to increased costs and decreasedperformance.
2. Inconsistent Results: In paginated queries, each page is typicallyretrieved at a different read timestamp. For example, the first page mightcome from a query at 1 PM, and the next from a query at 1:10 PM. This meansthat search results can change between queries, leading to inconsistentresults across pages.

Key-based pagination, on the other hand, uses a unique identifier (key) from thelast result of a page to fetch the next set of results. This ensures bothefficient retrieval and consistent results, even if the underlying data changes.

To provide stability in page results, the application could issue all queriesfor different pages at the same timestamp. This, however, might fail if thequery exceeds theversion retentionperiod(the default is 1 hour). For example, this failure happens ifversion_gc isone hour, and the end user fetched the first results at 1 PM and clickedNextat 3 PM.

Use key-based pagination

Key-based pagination remembers the last item of the previous page and uses it asa starting point for the next page query. To achieve this, the query must returnthe columns specified in theORDER BY clause and limit the number of rowsusingLIMIT.

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

Using ourAlbums sample schema, for the first page, the query looks like thefollowing:

SELECTAlbumId,ReleaseTimestampFROMAlbumsWHERESEARCH(AlbumTitle_Tokens,"fifth symphony")ORDERBYReleaseTimestampDESC,AlbumIdLIMIT10;

SELECTalbumid,releasetimestampFROMalbumsWHEREspanner.search(albumtitle_tokens,'fifth symphony')ORDERBYreleasetimestampDESC,albumidLIMIT10;

TheAlbumId is the tie breaker sinceReleaseTimestamp isn't a key. Theremight be two different albums with the same value forReleaseTimestamp.

To resume, the application runs the same query again, but with aWHERE clausethat restricts the results from the previous page. The additional conditionneeds 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 itcan't be NULL, so the condition is the following:

SELECTAlbumId,ReleaseTimestampFROMAlbumsWHERE(ReleaseTimestamp <@last_page_release_timestampOR(ReleaseTimestamp=@last_page_release_timestampANDAlbumId >@last_page_album_id))ANDSEARCH(AlbumTitle_Tokens,@p)ORDERBYReleaseTimestampDESC,AlbumIdASCLIMIT@page_size;

SELECTalbumid,releasetimestampFROMalbumsWHERE(releasetimestamp <$1OR(releasetimestamp=$1ANDalbumid >$2))ANDspanner.search(albumtitle_tokens,$3)ORDERBYreleasetimestampDESC,albumidASCLIMIT$4;

Spanner interprets this kind of condition asseekable. This means thatSpanner doesn't read the index for documents you're filteringout. This optimization is what makes key-based pagination much more efficientthan offset-based pagination.

Use offset-based pagination

Offset-based pagination leverages theLIMIT andOFFSET clauses of SQL queryto simulate pages. TheLIMIT value indicates the number of results per page.TheOFFSET value is set to zero for the first page, page size for the secondpage, and double the page size for the third page.

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

SELECTAlbumIdFROMAlbumsWHERESEARCH(AlbumTitle_Tokens,"fifth symphony")ORDERBYReleaseTimestampDESC,AlbumIdLIMIT50OFFSET100;

SELECTalbumidFROMalbumsWHEREspanner.search(albumtitle_tokens,'fifth symphony')ORDERBYreleasetimestampDESC,albumidLIMIT50OFFSET100;

Usage Notes:

• TheORDER BY clause is highly recommended to ensure consistent orderingbetween pages.
• In production queries, use query parameters rather than constants to specifyLIMIT andOFFSET to make query caching more efficient. For moreinformation, seeQuery parameters.

What's next

• Learn how torank search results.
• Learn how toperform a substring search.
• Learn how tomix full-text and non-text queries.
• Learn how tosearch multiple columns.