Offset Pagination Example: limit=25 and offset=100

Skip a hundred rows, return the next twenty-five, and jump to any page number.

Offset pagination asks for a page by counting: skip this many rows, then return the next ones. This example fetches page 5 of a list of customers at 25 a page, shows the request and the SQL it becomes, and the one formula that connects page numbers to offsets.

The formula. Page 1 skips nothing; each page after it skips one more page's worth. So the offset is the page number minus one, times the limit. For page 5 at 25 a page: (5 − 1) × 25 = 100.

text

GET /customers?limit=25&offset=100

The request for page 5

sql

SELECT id, name, created_at
FROM customers
ORDER BY id
LIMIT 25 OFFSET 100;

The query the server runs for it

What comes back. Customers in positions 101 to 125 of the sorted list. The response often includes the total count too, so the screen can show "page 5 of 40" and a row of page numbers. Counting the total is a query of its own, and on a large table it is not free.

What it does well. A client can jump to any page directly: page 12 is offset 275, with no need to visit pages 1 to 11 first. The request is easy to read and to build, and the SQL is one clause.

What it relies on. That the rows before the offset stay where they were between requests, and that skipping them is cheap. The gotchas in this topic show what happens when either stops being true.

So with offset pagination, page n is offset = (n − 1) × limit, and the ability to jump straight to any page number is what it offers that a cursor does not.

← → move through sections and cards