Postgres by Example: LIMIT and OFFSET

LIMIT n returns at most n rows. OFFSET m skips the first m rows of the result. Combined, they implement classic page-based pagination: page k of size p is LIMIT p OFFSET (k - 1) * p. Without an ORDER BY, "first" is meaningless — always pair LIMIT/OFFSET with an explicit order.

What you'll learn:

• Restricting result size with LIMIT
• Skipping rows with OFFSET
• Pagination patterns and their costs
• Why offset-based pagination scales poorly on large tables
• Keyset (cursor) pagination as the better alternative

-- First two rows, ordered by id
SELECT * FROM fruits ORDER BY id LIMIT 2;

-- Skip one, then take two: rows 2 and 3
SELECT * FROM fruits ORDER BY id LIMIT 2 OFFSET 1;

-- Page 2 of 3 per page
SELECT * FROM fruits ORDER BY id LIMIT 3 OFFSET 3;

-- Keyset pagination: rows with id > last_seen_id
SELECT * FROM fruits WHERE id > 2 ORDER BY id LIMIT 2;

LIMIT 2 OFFSET 1 reads "two rows, after skipping one." Internally, PostgreSQL still has to compute all rows up to OFFSET + LIMIT and discard the offset — for page 10,000 of 50 rows per page, that means scanning 500,050 rows. Offset pagination is fine for the first few pages but terrible for deep pagination.

The keyset pattern (also called cursor-based or seek pagination) uses the last seen value as the filter: WHERE id > $last_id ORDER BY id LIMIT 50. The query touches only the next 50 rows regardless of how far you have paginated. It requires an indexed column (often the primary key or created_at, id) but is the standard approach in production systems.

To run (requires fruits from earlier lessons):

$ psql -f source/limit-offset.sql postgres
 id |  name
----+--------
  1 | apple
  2 | banana
(2 rows)

 id |  name
----+--------
  2 | banana
  3 | cherry
(2 rows)
 ...

Common pitfalls:

• LIMIT 10 without ORDER BY returns some ten rows — and the choice can change between executions. Always sort.
• Two rows with the same sort key may swap places on different pages. Add a tie-breaker (ORDER BY created_at DESC, id DESC) for stable pagination.
• Deep offsets are slow. For page 1,000 of an "infinite scroll" feed, switch to keyset.
• OFFSET is also used in SELECT ... FOR UPDATE patterns and can interact poorly with row locks; if you need to process batches, keyset over a primary key is again the safer pattern.

Tip: PostgreSQL also accepts FETCH FIRST n ROWS ONLY (SQL-standard), equivalent to LIMIT n. FETCH FIRST n ROWS WITH TIES returns more than n if there are ties at the boundary — handy for top-N queries.

Try it: Compute "the third-largest fruit id": SELECT id FROM fruits ORDER BY id DESC LIMIT 1 OFFSET 2;. Then try OFFSET 0, OFFSET 1, etc. Then rewrite as keyset pagination: SELECT id FROM fruits WHERE id < (some_value) ORDER BY id DESC LIMIT 1.

Source: limit-offset.sql

Next: DISTINCT

Home: Postgres by Example