Pagination
Paginating results with LIMIT and OFFSET
Run the following statement to see how many orders are in the orders table:
1SELECT COUNT(*) FROM orders;The result should look like this:
123 count-------- 100000As you can see, the orders table contains 100,000 orders.
Imagine you want to display these orders in your application. Displaying all 100,000 orders at once would not be practical, so you decide to display 100 orders per page.
A user can then move between pages to see different groups of orders. Page 1 displays the first 100 orders, page 2 displays the next 100 orders, and so on.
How can we retrieve only the orders needed for each page?
PostgreSQL provides the LIMIT and OFFSET clauses for doing this. This approach is called pagination.
Retrieving the first page
Let's start by retrieving 100 orders for the first page.
Run the following statement:
123SELECT id, payment_referenceFROM ordersLIMIT 100;The query returns 100 rows.
The LIMIT clause specifies the maximum number of rows PostgreSQL should return. Here, LIMIT 100 tells PostgreSQL to return no more than 100 rows.
But there is a problem.
We want the first page of our application to contain the first 100 orders. Which 100 orders are the first 100?
Our query doesn't specify an order.
Without an ORDER BY clause, PostgreSQL does not guarantee the order in which rows are returned. The results might appear to be ordered by id when you run the query, but your application can't rely on that order.
To define exactly which orders belong on the first page, we need to specify an order.
Run the query again, this time with ORDER BY id:
1234SELECT id, payment_referenceFROM ordersORDER BY idLIMIT 100;ORDER BY id sorts the orders by their id in ascending order. Because id is the primary key and every order has a unique id, the order is unambiguous.
The query now returns the orders with IDs 1 through 100.
We have our first page.
Retrieving the second page
We now know how to retrieve the 100 orders for the first page.
But what should happen when the user clicks page 2?
Page 1 contains orders 1 through 100, so for page 2 we need to skip those first 100 orders and return the next 100.
PostgreSQL provides the OFFSET clause for this.
Run the following statement:
12345SELECT id, payment_referenceFROM ordersORDER BY idLIMIT 100OFFSET 100;The result should contain the orders with IDs 101 through 200.
OFFSET 100 tells PostgreSQL to skip the first 100 rows in the ordered result before returning rows. LIMIT 100 then limits the result to the next 100 rows.
To retrieve page 3, we need to skip the first 200 rows:
12345SELECT id, payment_referenceFROM ordersORDER BY idLIMIT 100OFFSET 200;This query returns the orders with IDs 201 through 300.
So the pattern is:
Page 1: skip 0 rows
Page 2: skip 100 rows
Page 3: skip 200 rows
As the page number increases, the number of rows we need to skip increases as well.
Changing the order of the results
So far, we have ordered the results by id:
1ORDER BY idBy default, PostgreSQL sorts values in ascending order, from smallest to largest. This is equivalent to writing:
1ORDER BY id ASCThat is why page 1 contains orders 1 through 100.
But suppose you want the highest-numbered orders to appear first.
Run the following statement:
1234SELECT id, payment_referenceFROM ordersORDER BY id DESCLIMIT 100;DESC tells PostgreSQL to sort the values in descending order, from largest to smallest.
The first page now starts with the orders that have the highest IDs.
The sort direction is part of the pagination order. If you change the direction, you also change which rows belong to each page.
Offset-based pagination
The pagination approach we have built using LIMIT and OFFSET is called offset-based pagination.
12345SELECT id, payment_referenceFROM ordersORDER BY idLIMIT 100OFFSET 200;PostgreSQL skips the first 200 rows in the ordered result and then returns up to 100 rows.
This works well for retrieving pages of results, but there is an important drawback: as the OFFSET becomes larger, PostgreSQL has to skip more and more rows.
In the next chapter, you will see how this affects query performance.