PostgreSQL - LIMIT Clause

PostgreSQL - LIMIT and OFFSET Clauses

The PostgreSQL LIMIT and OFFSET clauses are essential for implementing pagination, which is the practice of dividing a large result set into smaller, more manageable ‘pages’ of data. LIMIT restricts the number of rows returned, while OFFSET skips a specified number of rows before starting to return them.

Syntax

The LIMIT and OFFSET clauses are placed at the end of a SELECT statement.

SELECT column1, column2, ...
FROM table_name
ORDER BY sort_expression
LIMIT row_count OFFSET offset_value;

Where:

• row_count: The maximum number of rows to return.
• offset_value: The number of rows to skip from the beginning of the result set.
• ORDER BY: A critical component for stable and predictable results.

The Importance of ORDER BY

Crucial Best Practice: Always use an ORDER BY clause with LIMIT and OFFSET. Without ORDER BY, the database does not guarantee the order of the returned rows. This means running the same query twice could produce different results, making your pagination unreliable.

Example

Consider the following products table:

id | product_name      | price
----+-------------------+-------
 1  | Quantum Keyboard  | 75.00
 2  | Nebula Mouse      | 25.00
 3  | Galaxy Headset    | 50.00
 4  | Stellar Monitor   | 300.00
 5  | Cosmic Webcam     | 60.00
 6  | Supernova Mousepad| 15.00
 7  | Meteor Microphone | 120.00

Fetching the Top N Rows

To fetch the 3 most expensive products, we combine ORDER BY with LIMIT.

SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 3;

This would produce a predictable result:

 product_name    | price
-------------------+--------
 Stellar Monitor   | 300.00
 Meteor Microphone | 120.00
 Quantum Keyboard  | 75.00
(3 rows)

Implementing Pagination

Let’s say we want to display products on a page with 3 items per page. To get the second page, we use LIMIT and OFFSET.

-- Page 2: Skip the first 3 products (3 * (2-1)), and fetch the next 3.
SELECT product_name, price
FROM products
ORDER BY id ASC -- Ordering by ID for stable pagination
LIMIT 3 OFFSET 3;

This would produce the following result:

 product_name     | price
------------------+--------
 Stellar Monitor  | 300.00
 Cosmic Webcam    | 60.00
 Supernova Mousepad| 15.00
(3 rows)

Performance Considerations and Alternatives

While OFFSET is easy to use, it can become inefficient with large offset values (e.g., OFFSET 100000). This is because the database still has to generate all 100,000 rows internally before discarding them.

For high-performance pagination on large datasets, a technique called keyset pagination (or cursor-based pagination) is recommended. This method uses a WHERE clause to filter rows based on the values from the last row of the previous page, avoiding the need for OFFSET.

-- Keyset pagination example: Fetching the page after the row with id=3
SELECT product_name, price
FROM products
WHERE id > 3 -- The 'keyset' condition
ORDER BY id ASC
LIMIT 3;