LIMIT & OFFSET

Queries can return far more rows than an application or a screen needs at once. LIMIT caps how many rows a query returns, and OFFSET skips a number of rows before starting to return them. Together they are the classic building blocks for pagination.

LIMIT: capping the result set

The 5 most expensive products

SQL

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 5;

Without an ORDER BY, LIMIT gives you an arbitrary 5 rows — whatever PostgreSQL happens to read first. Always pair LIMIT with an ORDER BY when you care which rows you get.

OFFSET: skipping rows

Skip the 10 most recent orders, then take the next 10

SQL

SELECT order_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC
LIMIT 10 OFFSET 10;

This is the standard “page 2 of 10 results per page” pattern: page n uses OFFSET (n - 1) * page_size and LIMIT page_size.

OFFSET gets slow on large offsets

PostgreSQL cannot jump straight to row 100,000 — it has to scan and discard every row before the offset. On a large, deep-paginated table (think “page 5,000”), a query with a huge OFFSET value can become dramatically slower than the early pages, even with a supporting index on the sort column.

The scalable alternative: keyset (cursor-based) pagination

For deep pagination, instead of counting rows with OFFSET, remember the last row you saw and filter for rows “after” it:

Keyset pagination — next page after order_id 4820

SQL

SELECT order_id, order_date, total_amount
FROM orders
WHERE order_id < 4820
ORDER BY order_id DESC
LIMIT 10;

Because this uses a WHERE condition on an indexed column instead of counting through discarded rows, performance stays constant no matter how deep you page. This pattern is often called cursor-based pagination and is the preferred approach for infinite scroll, APIs, and any table that grows large.

FETCH FIRST — the SQL-standard equivalent

LIMIT/OFFSET is a PostgreSQL (and MySQL) convenience. The SQL standard spells the same idea differently, and PostgreSQL supports that syntax too:

Equivalent to LIMIT 5, using standard SQL syntax

SQL

SELECT name, price
FROM products
ORDER BY price DESC
FETCH FIRST 5 ROWS ONLY;

Skipping rows the standard way, with OFFSET ... FETCH

SQL

SELECT order_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

Note

FETCH FIRST 1 ROW ONLY reads slightly more naturally than LIMIT 1 in generated or portable SQL, and both forms compile to the exact same execution plan in PostgreSQL — pick whichever your team is more comfortable reading.

LIMIT and OFFSET (or FETCH) are applied last, after ORDER BY
Always specify an ORDER BY when using LIMIT so the result is deterministic
For deep pagination on large tables, prefer keyset/cursor pagination over a large OFFSET