Pagination
Introducing keyset pagination
In the previous chapter, you saw that a large OFFSET makes PostgreSQL process and discard all the rows that precede the requested page.
One way to avoid this work is to stop telling PostgreSQL how many rows to skip. Instead, we can tell it where the previous page ended and ask for the rows that come after that position.
This approach is called keyset pagination.
Keyset pagination uses values from the last row of the previous page to identify where the next page should begin.
It is also commonly called cursor pagination or seek pagination.
How much work does keyset pagination require?
In the previous chapter, we analyzed an offset-based query that skipped the first 90,000 orders before returning the next 100:
12LIMIT 100OFFSET 90000The Index Scan had to produce 90,100 rows even though the query returned only 100.
Now let's retrieve the 100 orders that come after id = 90000 using keyset pagination.
Run the following statement:
123456EXPLAIN ANALYZESELECT id, payment_referenceFROM ordersWHERE id > 90000ORDER BY idLIMIT 100;You should see an execution plan similar to this:
12345678910Limit (cost=0.29..4.23 rows=100 width=17) (actual time=0.300..0.700 rows=100.00 loops=1) Buffers: shared hit=2 read=3 -> Index Scan using orders_pkey on orders (cost=0.29..401.46 rows=10181 width=17) (actual time=0.100..0.200 rows=100.00 loops=1) Index Cond: (id > 90000) Index Searches: 1 Buffers: shared hit=2 read=3Planning: Buffers: shared hit=5 read=1 dirtied=1Planning Time: 0.600 msExecution Time: 0.900 msAgain, focus on the rows value in the actual section of the Index Scan:
1actual time=0.100..0.200 rows=100.00 loops=1The Index Scan produced only 100 rows.
Also notice this line:
1Index Cond: (id > 90000)This tells us that PostgreSQL used the id > 90000 condition when scanning the orders_pkey index.
Because id is indexed, PostgreSQL can begin scanning the part of the index that contains values greater than 90000. It doesn't need to produce the preceding 90,000 rows first.
Once the Index Scan has produced 100 rows, the Limit node has all the rows it needs and the query can stop.
Compare this with the offset-based query from the previous chapter. That query's Index Scan produced 90,100 rows to return 100 rows. With keyset pagination, the Index Scan produced only 100 rows.
What we have learned so far
Keyset pagination lets PostgreSQL continue from the position reached by the previous page instead of processing all the preceding rows.
In our examples, this was straightforward because we ordered the rows by id:
1ORDER BY idThe id column is unique, so every row has an exact position in that order.
Keyset pagination does have a trade-off. To retrieve the next page, you need to know where the previous page ended. This makes it well suited to moving forward through results, but it doesn't naturally support jumping directly to an arbitrary page number.
There is another important question we haven't addressed yet.
What happens if we want to paginate by a column that is not unique, such as the time an order was placed?
You'll solve that problem in the next chapter.