How to Use LIMIT and OFFSET in PostgreSQL for Query Pagination

In PostgreSQL, use LIMIT to specify the maximum number of rows to return and OFFSET to skip a number of rows before starting to return rows. Together, LIMIT and OFFSET help paginate query results by controlling which rows appear in the output.

Syntax

The basic syntax to limit and offset rows in PostgreSQL is:

• LIMIT n: Returns at most n rows.
• OFFSET m: Skips the first m rows before returning results.

You can use them together to paginate results, for example, to get rows 11 to 20, use LIMIT 10 OFFSET 10.

sql

SELECT * FROM table_name
LIMIT n OFFSET m;

Example

This example shows how to select 3 rows from a sample table, skipping the first 2 rows. It demonstrates how LIMIT and OFFSET work together to paginate results.

sql

CREATE TEMP TABLE fruits (id SERIAL PRIMARY KEY, name TEXT);

INSERT INTO fruits (name) VALUES
('Apple'), ('Banana'), ('Cherry'), ('Date'), ('Elderberry');

SELECT id, name FROM fruits
LIMIT 3 OFFSET 2;

Output

 id |   name   
----+----------
  3 | Cherry  
  4 | Date    
  5 | Elderberry
(3 rows)

Common Pitfalls

Common mistakes when using LIMIT and OFFSET include:

• Using OFFSET without ORDER BY, which can lead to inconsistent row order and unpredictable results.
• Setting a very large OFFSET can cause performance issues because PostgreSQL still scans and discards those rows.
• Confusing OFFSET as a zero-based index; it skips rows, so OFFSET 0 means start from the first row.

Always use ORDER BY to ensure consistent pagination.

sql

/* Wrong: No ORDER BY, results may vary */
SELECT * FROM fruits LIMIT 2 OFFSET 2;

/* Right: Use ORDER BY for consistent results */
SELECT * FROM fruits ORDER BY id LIMIT 2 OFFSET 2;

Quick Reference

Clause	Description	Example
LIMIT n	Returns up to n rows	LIMIT 5
OFFSET m	Skips first m rows	OFFSET 10
LIMIT with OFFSET	Paginate results	LIMIT 10 OFFSET 20
ORDER BY	Sort rows for consistent pagination	ORDER BY id ASC

Key Takeaways

Use LIMIT to control how many rows you get back from a query.

Use OFFSET to skip a number of rows before returning results, useful for pagination.

Always combine LIMIT and OFFSET with ORDER BY to get consistent and predictable results.

Large OFFSET values can slow down queries because PostgreSQL still processes skipped rows.

OFFSET starts counting from zero, so OFFSET 0 means start from the first row.