Limiting and Offsetting Results: Mastering PostgreSQL Pagination

Previously in this course, we covered Sorting Query Results: Mastering ORDER BY in PostgreSQL. Now that you can organize your data, this lesson adds the ability to restrict how much of that data we actually retrieve, allowing us to build responsive, paginated user interfaces.

Why Limit Your Results?

In a production store database, you might eventually have thousands or millions of products. If a user visits your "All Products" page, attempting to load every single row at once would crash the browser and overwhelm the network.

LIMIT and OFFSET are the primary tools used to break these massive result sets into manageable "pages."

Using the LIMIT Clause

The LIMIT clause is the simplest way to restrict the number of rows returned by a query. It acts as a hard cap on the size of the result set.

If you want to see just the first five products from your products table, you write:

SQL
SELECT product_name, price
FROM products
ORDER BY product_name
LIMIT 5;

PostgreSQL will scan the table, sort it by name, and stop execution as soon as it has collected five rows. This is highly efficient because the database engine doesn't need to process the entire table if it only needs to fulfill a small request.

Implementing Pagination with OFFSET

While LIMIT gives you the first "page," OFFSET allows you to skip a specific number of rows, effectively moving to the next page.

To implement a classic "pagination" system (where each page shows 10 items), you combine them:

• Page 1: LIMIT 10 OFFSET 0 (or just LIMIT 10)
• Page 2: LIMIT 10 OFFSET 10
• Page 3: LIMIT 10 OFFSET 20

SQL
-- Fetching the second page of products (rows 11-20)
SELECT product_name, price
FROM products
ORDER BY product_name
LIMIT 10 OFFSET 10;

The Critical Rule: Always Use ORDER BY

A common mistake beginners make is using LIMIT and OFFSET without an ORDER BY clause.

In PostgreSQL, rows are not guaranteed to be returned in a specific order unless you explicitly define one. If you omit ORDER BY, the database might return the rows in a different order each time you run the query, causing items to "jump" between pages or appear twice. Always combine these clauses with a stable sort order.

Hands-on Exercise

Using our store project's products table, perform the following steps in your terminal:

1. Write a query that returns the 5 most expensive products. (Hint: You'll need to sort by price in descending order and use LIMIT.)
2. Suppose you want to display products in groups of 3. Write the query to retrieve the second page (rows 4 through 6) sorted by product_name alphabetically.

Common Pitfalls

• Performance at Scale: While OFFSET is easy to understand, it becomes slow on very large tables because the database must still scan through all the skipped rows before it starts returning the ones you want. For massive datasets, you might eventually look into REST API Pagination: Choosing Between Offset and Cursor-Based to understand when to switch strategies.
• Off-by-one errors: Remember that OFFSET 10 skips 10 rows. If you are on page 2 of a 10-item page size, you are skipping the first 10 rows (0-9) to start at row 11.
• Implicit Sorting: Never rely on the "default" order of rows. If your business logic requires consistency, define your ORDER BY strictly.

FAQ

Does LIMIT work with other clauses? Yes, LIMIT is the very last clause evaluated in a SELECT statement, coming after WHERE, GROUP BY, and ORDER BY.

What happens if OFFSET is larger than the number of rows? PostgreSQL will simply return an empty result set (0 rows) without throwing an error.

Can I use LIMIT without OFFSET? Yes, you can use LIMIT alone whenever you only need the "top N" results.

Recap

We’ve learned that LIMIT caps the result size and OFFSET skips records to allow for pagination. By anchoring these with ORDER BY, we ensure our store application displays data predictably and efficiently for our users.

Up next: We will discuss how to uniquely identify every row in our tables using Primary Keys.