# LIMIT and OFFSET

> Limit result size and compare offset pagination with keyset pagination.

---

LLMS index: [llms.txt](/en/llms.txt)

---

`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 {#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 {#example}

```sql {num="7.1" caption="LIMIT and OFFSET"}
-- 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 {#run}

To run (requires `fruits` from earlier lessons):

```text
$ 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 {#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] 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.

> [!NOTE] 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`.

## Downloads {#downloads}

SQL file: [limit-offset.sql](/examples/source/limit-offset.sql) · [All lesson files](/examples/examples.zip) · [Setup guide](/en/#prerequisites)