PostgreSQL LIMIT

PostgreSQL LIMIT Clause

The PostgreSQL LIMIT clause restricts the number of rows returned by a SELECT query. It is useful when you need only a small sample of records, the first few matching rows, or one page of a paginated result set.

For example, a table may contain thousands of rows, while an application screen displays only 10 or 20 rows at a time. Adding LIMIT prevents PostgreSQL from sending the entire matching result set to the application.

For predictable results, use LIMIT together with ORDER BY. Without an explicit sort order, PostgreSQL may return any qualifying rows, and the selected rows can change when the query plan or table data changes.

PostgreSQL LIMIT Syntax

LIMIT is the clause used in SQL to limit the number of rows in the query result.

The syntax to use LIMIT clause is shown below.

 SELECT *
	FROM tablename
	LIMIT N;

where N is an integer that specifies the number of rows the result set has to be limited to.

You can also specify a range, to fetch only those rows in that range of the result set.

 SELECT *
	FROM tablename
	LIMIT N OFFSET i;

where N number of rows are fetched after offset i.

In this syntax, PostgreSQL first skips i rows and then returns up to N rows. Both values must be zero or positive. LIMIT 0 returns no rows, while OFFSET 0 skips none.

PostgreSQL LIMIT with ORDER BY

A query that uses LIMIT should normally define which rows come first. The following query returns the three students with the lowest IDs:

SELECT *
FROM students
ORDER BY id ASC
LIMIT 3;

To return the three most recently added rows instead, reverse the sort direction:

SELECT *
FROM students
ORDER BY id DESC
LIMIT 3;

The column used in ORDER BY should provide a stable sequence. If the sort column can contain duplicate values, add a unique column as a secondary sort key.

SELECT *
FROM students
ORDER BY score DESC, id ASC
LIMIT 3;

PostgreSQL LIMIT Example Returning Three Rows

Let us consider a table named students and run a SELECT query limiting the number of rows in the result to be 3.

 SELECT * 
	FROM students
	LIMIT 3;

The query returns no more than three rows. Because this example does not include ORDER BY, it should be treated as a demonstration of the row-count restriction rather than a request for a specific three rows.

PostgreSQL LIMIT with OFFSET Example

Now, let us limit the number of rows in the result to 2 with offset of 2.

 SELECT * 
	FROM students
	LIMIT 2 OFFSET 2;

This query skips the first two rows produced by the query and returns the next two rows. A deterministic pagination query should also include an ORDER BY clause:

SELECT *
FROM students
ORDER BY id ASC
LIMIT 2 OFFSET 2;

Paginating PostgreSQL Results with LIMIT and OFFSET

For page-based pagination, calculate the offset from the page number and page size:

OFFSET = (page_number - 1) × page_size

For a page size of 10 rows, the first three pages use the following values:

• Page 1: LIMIT 10 OFFSET 0
• Page 2: LIMIT 10 OFFSET 10
• Page 3: LIMIT 10 OFFSET 20

SELECT id, name, score
FROM students
ORDER BY id ASC
LIMIT 10 OFFSET 20;

The query above returns the third page when each page contains 10 rows.

PostgreSQL FETCH FIRST as an Alternative to LIMIT

PostgreSQL also supports the SQL-standard FETCH FIRST syntax. The following query is equivalent to LIMIT 5:

SELECT *
FROM students
ORDER BY id ASC
FETCH FIRST 5 ROWS ONLY;

You can combine OFFSET with FETCH as well:

SELECT *
FROM students
ORDER BY id ASC
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY;

LIMIT is concise and commonly used in PostgreSQL, while FETCH FIRST may be preferable when writing SQL intended to follow the standard syntax.

Performance Considerations for Large OFFSET Values

LIMIT reduces the number of rows returned to the client, but a large OFFSET can still require PostgreSQL to locate and discard many earlier rows. For example, a query with OFFSET 100000 may process those skipped rows before returning the requested page.

For deep pagination, keyset pagination can be more efficient. Instead of counting and skipping every earlier row, it continues from the last value returned on the previous page.

SELECT id, name, score
FROM students
WHERE id > 1000
ORDER BY id ASC
LIMIT 20;

In this example, 1000 is the last ID from the preceding page. An index that supports the filtering and ordering columns can further improve this access pattern.

Common PostgreSQL LIMIT Mistakes

• Using LIMIT without ORDER BY: The query limits the row count but does not guarantee which rows are returned.
• Using a non-unique sort column alone: Rows with equal sort values may move between pages. Add a unique secondary column such as an ID.
• Confusing LIMIT and OFFSET: LIMIT controls how many rows are returned; OFFSET controls how many rows are skipped.
• Using increasingly large offsets: Deep offset pagination can become slow because PostgreSQL still processes skipped rows.
• Building LIMIT values through string concatenation: Application code should validate or parameterize pagination values rather than inserting unchecked input into SQL.

PostgreSQL LIMIT Questions

Does PostgreSQL LIMIT return the first rows in a table?

Not necessarily. A table has no guaranteed default row order. Use ORDER BY to define what “first” means before applying LIMIT.

What does LIMIT 1 do in PostgreSQL?

LIMIT 1 returns at most one row from the query result. Combine it with ORDER BY when you need a specific row, such as the newest or highest-scoring record.

What is the difference between LIMIT and OFFSET?

LIMIT sets the maximum number of rows to return. OFFSET tells PostgreSQL how many rows to skip before it starts returning rows.

Can PostgreSQL use OFFSET without LIMIT?

Yes. PostgreSQL permits a query to skip a specified number of rows without setting a limit, although this is less common for application pagination.

Is LIMIT faster than returning every matching row?

It can reduce result transfer and may allow PostgreSQL to stop producing rows once the limit is satisfied. Actual performance still depends on filtering, sorting, indexes, the query plan, and any offset that must be processed.

PostgreSQL LIMIT Editorial Checklist

• Confirm that examples requiring predictable rows include an appropriate ORDER BY.
• Verify that every pagination offset follows (page number - 1) × page size.
• Check that duplicate sort values are resolved with a stable secondary key.
• Distinguish clearly between returned rows, skipped rows, and rows processed internally.
• Consider keyset pagination when an example uses large or continually increasing offsets.

Summary of PostgreSQL LIMIT and OFFSET

In this PostgreSQL Tutorial, we used LIMIT to restrict the number of rows returned by a query and OFFSET to skip rows before returning a page of results. For reliable output, combine these clauses with a stable ORDER BY. For very deep pagination, consider continuing from the last returned key instead of using a large offset.