LIMIT

Introduction to PostgreSQL LIMIT

LIMIT is an optional clause of the SELECT statement that caps the number of rows returned. It is commonly used for pagination, sampling, and "top N" queries.

Basic syntax:

SELECT select_list
FROM table_name
ORDER BY sort_expression
LIMIT row_count;

If row_count is zero, the query returns an empty set. If row_count is NULL, the LIMIT clause has no effect and all rows are returned.

To skip a number of rows before returning results, add OFFSET:

SELECT select_list
FROM table_name
ORDER BY sort_expression
LIMIT row_count OFFSET row_to_skip;

PostgreSQL evaluates OFFSET before LIMIT: it skips the first row_to_skip rows, then returns the next row_count rows from the remaining set.

LIMIT examples

These examples use the film table from the DVD rental sample database.

Get the first 5 films by film ID:

SELECT film_id, title, release_year
FROM film
ORDER BY film_id
LIMIT 5;

Result:

film_id |      title       | release_year
---------+------------------+--------------
      1 | Academy Dinosaur |         2006
      2 | Ace Goldfinger   |         2006
      3 | Adaptation Holes |         2006
      4 | Affair Prejudice |         2006
      5 | African Egg      |         2006
(5 rows)

Get 4 films starting from position 4 (LIMIT with OFFSET):

SELECT film_id, title, release_year
FROM film
ORDER BY film_id
LIMIT 4 OFFSET 3;

Result:

film_id |      title       | release_year
---------+------------------+--------------
      4 | Affair Prejudice |         2006
      5 | African Egg      |         2006
      6 | Agent Truman     |         2006
      7 | Airplane Sierra  |         2006
(4 rows)

The OFFSET 3 skips the first three rows, then LIMIT 4 returns the next four.

Top 10 most expensive rentals:

SELECT film_id, title, rental_rate
FROM film
ORDER BY rental_rate DESC
LIMIT 10;

Result:

film_id |        title        | rental_rate
---------+---------------------+-------------
     13 | Ali Forever         |        4.99
     20 | Amelie Hellfighters |        4.99
      7 | Airplane Sierra     |        4.99
     10 | Aladdin Calendar    |        4.99
      2 | Ace Goldfinger      |        4.99
...

Why ORDER BY is required with LIMIT

PostgreSQL stores rows internally in an unspecified order. Without ORDER BY, the rows returned by LIMIT may differ between queries or after table updates. Always include ORDER BY to ensure you consistently get the rows you intend:

-- Correct: deterministic result
SELECT film_id, title FROM film ORDER BY film_id LIMIT 10;

-- Risky: unpredictable which 10 rows are returned
SELECT film_id, title FROM film LIMIT 10;

LIMIT / OFFSET pagination and its limitations

LIMIT and OFFSET are widely used for page-based pagination (page 1 = OFFSET 0, page 2 = OFFSET 20, etc.). However, at large offsets this becomes slow because PostgreSQL must scan and discard all preceding rows before returning the page you want.

For large tables or high page numbers, consider keyset (cursor-based) pagination instead:

-- Page 1
SELECT film_id, title FROM film ORDER BY film_id LIMIT 20;

-- Next page: use the last film_id from the previous result
SELECT film_id, title FROM film WHERE film_id > 20 ORDER BY film_id LIMIT 20;

Keyset pagination is O(1) per page instead of O(offset), making it much faster for deep pages.

Tips for using LIMIT effectively

• Always pair LIMIT with ORDER BY to get consistent, predictable results.
• For "top N" queries, an index on the sort column allows PostgreSQL to use an efficient index scan and stop early once N rows are found.
• Avoid large OFFSET values on big tables — performance degrades linearly. Switch to keyset pagination for high-volume use cases.
• LIMIT 1 is useful when you need exactly one row (e.g., the latest record), but remember it only makes sense with an explicit ORDER BY.

Reference: PostgreSQL documentation — SELECT / LIMIT.