Pagination
Navigating forward and backward with keyset pagination
So far, we’ve used the last row of the current page as a cursor to retrieve the next page.
To move backward, we can use the first row of the current page as the boundary instead.
In this chapter, you’ll learn how to move both forward and backward with keyset pagination.
We’ll use the id column again so that we can focus specifically on the direction of navigation.
Moving forward
Suppose the current page contains orders with IDs 101 through 200.
To retrieve the next page, we use the last row of the current page:
1id = 200Run the following statement:
12345SELECT id, payment_referenceFROM ordersWHERE id > 200ORDER BY idLIMIT 100;The query returns orders with IDs 201 through 300.
This follows the same keyset pattern you’ve already seen:
123WHERE id > cursorORDER BY idLIMIT 100When we’re ordering by id in ascending order, rows after the cursor have a larger id.
Moving backward
Now suppose we’re still on the page containing orders 101 through 200, but the user wants to return to the previous page.
This time, the important boundary is the first row of the current page:
id = 101
The previous page contains the 100 rows immediately before id = 101.
Run the following statement:
12345SELECT id, payment_referenceFROM ordersWHERE id < 101ORDER BY id DESCLIMIT 100;The condition:
1WHERE id < 101restricts the result to rows that come before the current page.
But notice that we also changed the ordering:
1ORDER BY id DESCWhy?
We need the 100 rows closest to id = 101.
Sorting in descending order makes PostgreSQL start with:
12341009998...and LIMIT 100 returns the 100 rows immediately before the current page.
If we instead used:
12ORDER BY idLIMIT 100PostgreSQL would return IDs 1 through 100 only because this particular boundary happens to be 101. For a later page, it would return the earliest matching rows rather than the rows immediately before the current page.
So when moving backward, we temporarily reverse the ordering to find the nearest preceding rows.
Restoring the display order
The previous query finds the correct rows, but it returns them in descending order:
123451009998...1Our pages have been displayed in ascending order, so we need to put those rows back into that order.
One way to do that is with a subquery.
A subquery is a query nested inside another query. The inner query runs first, and its result is then used by the outer query.
Run the following statement:
123456789SELECT id, payment_referenceFROM ( SELECT id, payment_reference FROM orders WHERE id < 101 ORDER BY id DESC LIMIT 100) AS previous_pageORDER BY id;The inner query finds the 100 rows immediately before id = 101.
The outer query then sorts those rows back into ascending order, so the result contains IDs 1 through 100 in the same order in which the page would normally be displayed.
The two boundaries
Keyset pagination therefore uses different boundaries depending on the direction of navigation.
To move forward, use the last row of the current page:
123WHERE id > last_idORDER BY idLIMIT 100To move backward, use the first row of the current page and temporarily reverse the ordering:
123WHERE id < first_idORDER BY id DESCLIMIT 100The same idea applies when the cursor contains multiple sort values. The comparison and ordering need to follow the same sort rules that define the position of each row.