7 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

Example

-- 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 processing 500,000 rows for a one-based page number. 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.

Run the example

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.

Downloads

SQL file: limit-offset.sql · All lesson files · Setup guide