Pagination
Keyset pagination with multiple sort columns
In the previous chapter, we used the id column to determine where one page ended and the next page should begin.
That worked because id is unique. Every order has a different id, so each row has an exact position in the ordered result.
Now imagine that you want to display an order history in your application, with the most recently placed orders shown first.
To do that, you would sort the rows by the placed_at column in descending order.
But unlike id, the placed_at column is not unique. Multiple orders can have the same placed_at value.
In this chapter, you'll learn why that creates a problem for keyset pagination and how to solve it using multiple sort columns.
Why placed_at alone is not enough
Let's first confirm that multiple orders can have the same placed_at value.
Run the following statement:
123456SELECT placed_at, COUNT(*)FROM ordersGROUP BY placed_atHAVING COUNT(*) > 1ORDER BY placed_at DESCLIMIT 5;The result should show some placed_at values that belong to more than one order.
Now run the following statement to retrieve the 100 most recently placed orders:
1234SELECT id, placed_atFROM ordersORDER BY placed_at DESCLIMIT 100;ORDER BY placed_at DESC places orders with later placed_at values before orders with earlier values.
But when two orders have exactly the same placed_at value, this query doesn't specify which of those orders should come first.
This creates a problem for keyset pagination.
Suppose the last row on a page has:
1placed_at = 2024-12-30 10:00:00+00If we used only that value as the cursor, the next page might use a condition like this:
1WHERE placed_at < timestamptz '2024-12-30 10:00:00+00'This condition retrieves orders placed before the cursor timestamp.
However, imagine that there are other orders with the same placed_at value that weren't returned on the previous page. They won't match this condition because their placed_at value is equal to the cursor value, not less than it.
Those orders will therefore be skipped.
Adding a unique tie-breaker
To solve this problem, we need another column that can determine the order when multiple orders have the same placed_at value.
We can use id because it's unique.
Run the following statement:
1234SELECT id, placed_atFROM ordersORDER BY placed_at DESC, id DESCLIMIT 100;PostgreSQL first orders the rows by placed_at.
If two rows have different placed_at values, that determines their order. If two rows have the same placed_at value, PostgreSQL uses id to determine which row comes first.
Because id is unique, no two orders can have the same combination of placed_at and id. Every row therefore has an exact position in the ordered result.
For keyset pagination, this means that the cursor now needs to contain both values from the last row of the page:
12placed_at = ...id = ...In the next section, you'll use both values to retrieve the following page.
Retrieving the next page with two cursor values
We now have an ordering that gives every row an exact position:
1ORDER BY placed_at DESC, id DESCThis also means that the cursor needs to contain both values from the last row of the page.
Suppose the last row has:
12placed_at = 2024-12-30 00:00:00+00id = 86139To retrieve the next page, we need orders that come after this row in the ordering.
An order comes after the cursor if:
its
placed_atvalue is earlier than2024-12-30 00:00:00+00, orit has the same
placed_atvalue but a lowerid.
Conceptually, the condition looks like this:
12345placed_at < timestamptz '2024-12-30 00:00:00+00'OR ( placed_at = timestamptz '2024-12-30 00:00:00+00' AND id < 86139)PostgreSQL lets us express the same comparison more compactly:
1234(placed_at, id) < ( timestamptz '2024-12-30 00:00:00+00', 86139)Run the following statement:
12345678SELECT id, placed_atFROM ordersWHERE (placed_at, id) < ( timestamptz '2024-12-30 00:00:00+00', 86139)ORDER BY placed_at DESC, id DESCLIMIT 100;PostgreSQL compares the values from left to right.
It first compares placed_at. If the timestamps are different, that determines which row comes after the cursor.
If the timestamps are equal, PostgreSQL compares id. Because id is also ordered in descending order, a lower id comes after the cursor.
This lets the next page include both orders with earlier timestamps and any remaining orders that have the same timestamp as the final row of the previous page.
What about the previous page?
So far, we’ve used keyset pagination to move forward through the results. Each time, we used the last row of the current page as the cursor for retrieving the next page.
But a pagination interface may also let users move back to the previous page.
How can we use keyset pagination to move backward?
You’ll learn how in the next chapter.