SQL queries involving pagination often use `OFFSET` to progress through a series of results, and pair that with `COUNT(*)` to show how far into the page count the pagination has progressed. This is slow, and gets progressively slower the further in it gets. Particularly once sorting is involved. One solution is to use *Keyset Pagination*. ## What is Keyset Pagination? Keyset Pagination is a database pagination method that makes use of a _Cursor_ to specify where in the total set of results to fetch next. This is done by including a `WHERE` clause that limits results to a set that would appear after the previous page's results, taking advantage of indexes to narrow down the search space. The simplest example would be a list ordered by `id`. If fetching a page of 20 results with `id` 1 through 20, the next page's results would be fetched with: ```sql SELECT <columns> FROM <table> WHERE id > 20 ORDER BY id LIMIT 20; ``` If that returns results with `id` between 21 and 30, the next would be fetched using `id > 30`. This should have a predictable cost whether it's the second page of results of the 200th. ## Pros and Cons to Keyset Pagination **Pros:** * Faster pagination at stable costs. * Pagination continues from where you left off, even if data is added/deleted on a "page". **Cons:** * No ability to jump to a particular page. * No way to know what page you're in (you have to think in terms of "first/next/previous" and maybe "last" only). * If referenced column data disappears or changes, the cursor becomes invalidated. ## Sorting Additional Columns Real-world examples would likely include sorting criteria, perhaps multiple sorted columns. If sorting with a `last_updated` column, one might do: ```sql SELECT <columns> FROM <table> WHERE (last_updated, id) < ('2026-03-15 12:21:42', 20) ORDER BY last_updated DESC, id DESC LIMIT 20; ``` This would choose the next 20 results that are older than `2026-03-15 12:21:42`, using the `id` as a [[PK Tie-Breaker]] in case values match. > [!important] Don't forget the tie-breaker > You'll always want `id` (or whatever the field name is for the table) as a tie-breaker in order to ensure stable results. For instance, if two results had the same timestamp, sort order would be inconsistent and could lead to missing or duplicating a result when moving across page boundaries. A stable tie-breaker avoids this. This can include multiple columns, but it'll need to become a bit more complex: ```sql SELECT <columns> FROM <table> WHERE last_updated < '2026-03-15 12:21:42' OR ( last_updated = '2026-03-15 12:21:42' AND author_id > 1234 ) OR ( last_updated = '2026-03-15 12:21:42' AND author_id = 1234 AND id < 20 ) ORDER BY last_updated DESC, author_id ASC, id DESC LIMIT 20; ``` > [!important] Make sure your operators and `ASC`/`DESC` match! > The example above is factoring in `author_id` as sorting in ascended order, so we use `>` in the comparison.If it was sorting `DESC`, we'd use `<`. > [!attention] Ordering *must* be stable! > The final sorting must guarantee uniqueness of results, or you can get duplicates, skipped rows, and bad page boundaries. As the number of columns increase, the complexity of the conditions increase. It's also pretty important to make sure these are indexed columns. You may also need to handle `NULL` specially. For this, you need to decide how a `NULL` should sort in relation to other values. ## Paginating Backwards If you need to move to a previous page, you have to reverse the order of operations. The above examples would become: ```sql SELECT <columns> FROM <table> WHERE (last_updated, id) > ('2026-03-15 12:21:42', 20) ORDER BY last_updated ASC, id ASC LIMIT 20; ``` We reversed the operator and the switched from `DESC` to `ASC`. For the more complex example: ```sql SELECT <columns> FROM <table> WHERE last_updated > '2026-03-15 12:21:42' OR ( last_updated = '2026-03-15 12:21:42' AND author_id < 1234 ) OR ( last_updated = '2026-03-15 12:21:42' AND author_id = 1234 AND id > 20 ) ORDER BY last_updated ASC, author_id DESC, id ASC LIMIT 20; ``` Same deal: We flipped the operators and `ASC`/`DESC`. This will give us rows in reverse order, though, so we need to take the results we get and reverse them back. ## Navigating to the Last Page While Keyset Pagination naturally supports the first, previous, and next pages, it does not naturally support navigating to the last page. Supporting navigating to the last page of results requires: 1. Reversing the sort conditions. 2. Fetching the _first_ page of those results 3. Reversing the results in the application code. As an example, let's look at how we might get the _first_ page of results: ```sql SELECT <columns> FROM <table> ORDER BY last_updated ASC, author_id DESC, id ASC LIMIT 20; ``` To get the last, we'll reverse that: ```sql SELECT <columns> FROM <table> ORDER BY last_updated DESC, author_id ASC, id DESC LIMIT 20; ``` We simply flipped the `ASC`/`DESC` and avoided any cursor-related `WHERE` clauses. > [!NOTE] You'll need to reverse the resulting rows. > The order of rows will be in inverse order, just like when navigating to a previous page. You'll need to reverse this when processing the results. ## What is a Cursor? We've modeled how this works under the hood, but the client needs to know how to communicate what the previous or next page is. For this, a _Cursor_ is used. This is a value that can represent this state. An example may be a base64-encoded JSON payload. For example: ```json { "last_updated": "2026-03-15 12:21:42", "id": 20 } ``` The resulting base64 value could then be used directly, or signed with [[HMAC]] to ensure there's no tampering. The backend would then verify/deserialize this and plug the values into the query. ## To COUNT(\*) or not to COUNT(\*) Offset-based pagination usually performs a `COUNT(*)` to determine how many results and therefore pages there are left, so users can jump around to later pages without just clicking Next a bunch. When using a _Cursor_, this isn't needed, because we're not dealing with page counts. We're dealing with relative values in queries. Removing `COUNT(*)` can therefore be an optimization. That said, it can still be necessary to communicate this information to the user or to an API. While keyset pagination does not require a `COUNT(*)`, applications can still include it, but if it's an expensive calculation then they'll still have to deal with that cost. ## Keeping Page-Based Navigation Keyset pagination is really about Previous/Next. It's great for [[Infinite Scrolling]], or patterns like that. But sometimes you still want to let users navigate to other pages. Pages get more expensive the further you go, but there may be some value in showing a number of pages early in the list. A hybrid approach can be employed. ### Offset-based pagination for the first N pages One approach is to show up to, say, 10 pages in the UI, and use offsets if jumping to those. After that, avoid page-based navigation and only allow Previous/Next. ### Cache boundaries as you navigate Assuming data is relatively stable, you can cache cursors for page boundaries as the user navigates. For example, if they've clicked Next 9 times, on that 9th page we know the cursor for page 10. That can go in cache, so the next page visit navigation to page 10 becomes cheap. This can be combined with offset-based navigation, preferring the cursor if known. **Note:** If the state the cursor is based off of changes, it may no longer point to the right place in the results. ### Precompute cursors If it's inexpensive to do so, you can fetch a larger number of partial results up-front (just enough to build the cursors) and then generate cursors from them. If your page size is `P`, and you want to precompute cursors for `N` pages, you'd need to fetch: $ P * (N - 1) $ This gives you enough information to compute each boundary's cursor. Let's take `P = 10` and `N = 4`: * Page 1 (rows 1-10) needs no cursor * Page 2 (rows 11-20) needs the last row of page 1 (`P`) * Page 3 (rows 21-30) needs the last row of page 2 (`2P`) * Page 4 (rows 31-40) needs needs row `3P` You only need to fetch up to 30 rows (`10 * (4 - 1)`) to know the cursors for pages 2-4. This, again, only works well if results are stable. Otherwise you wouldn't want to cache long, since the meaning of page 5 (for example) might change. You could just pre-compute when reaching certain page counts (e.g., page 1, page 10, page 20, etc.), combining this with [[#Cache boundaries as you navigate]]. > [!tip] The fewer pages you need to show, the cheaper this is > If you only need to know 5 pages ahead, that's not a lot of results to fetch. ### Hybrid approach: Offsets and Cursors Offset-based pagination gets more expensive as you go, but it's fairly cheap early in the results. So one approach is to allow usage of offsets for the first N pages but require cursor-based navigation and pre-computing after. For example: * Pages 1-5 might allow an Offset * Pages 6+ might require cursors * Precomputing can be used at regular page boundaries (say, every 5 pages) to precompute cursors for the next 5 pages * Large jumps (say, 20, 50, 100 pages in) are either disallowed or rate-limited ## Simulating Page Numbers Keyset pagination doesn't require throwing out page numbers. You can still represent them by simulating them based on knowledge of the cursor position. This assumes: 1. You know what page the user is currently on (tracking as you paginate) 2. You know the cursors for nearby pages (using one of the techniques above) 3. You can label those positions relative to the current page number. If you know the user is on page 4, you can show: * Page 2 * Page 3 * **Page 4** * Page 5 * Page 6 Navigating to pages 3 or 4 would use standard Cursor-based navigation. Page 6 might be a cached cursor from looking ahead.