SQL OFFSET Clause for Pagination
The OFFSET clause in SQL skips a specified number of rows in a query result, used alongside LIMIT to retrieve a specific subset. It is the most common approach to pagination in relational databases — but it has important performance limitations at scale.
Basic Syntax
SELECT column1, column2, ...
FROM table_name
ORDER BY column_name
LIMIT number_of_rows OFFSET number_of_rows_to_skip;
LIMIT— maximum number of rows to retrieveOFFSET— number of rows to skip before returning results
Example: Fetching Rows with OFFSET
Assume you have an employees table and want to skip the first 10 rows, then retrieve the next 5:
SELECT *
FROM employees
ORDER BY employee_id
LIMIT 5 OFFSET 10;
This returns rows 11–15 (sorted by employee_id).
Implementing Pagination
Calculate OFFSET based on page number:
OFFSET = (page_number - 1) × page_size
| Page | Query |
|---|---|
| Page 1 | LIMIT 10 OFFSET 0 |
| Page 2 | LIMIT 10 OFFSET 10 |
| Page 3 | LIMIT 10 OFFSET 20 |
| Page N | LIMIT 10 OFFSET (N-1) × 10 |
PostgreSQL / MySQL example — dynamic pagination:
-- Page 5, 20 rows per page
SELECT id, name, email
FROM users
ORDER BY created_at DESC
LIMIT 20 OFFSET 80;
The Performance Problem with OFFSET
OFFSET-based pagination has a critical flaw: the database must scan and discard all skipped rows before returning results.
For page 1000 at 20 rows per page:
LIMIT 20 OFFSET 19980 -- database scans 20,000 rows, returns 20
This means late pages are dramatically slower than early pages, regardless of indexes. On a table with millions of rows, page 10,000 can take seconds even with a well-tuned index.
| Table size | OFFSET 0 | OFFSET 10,000 | OFFSET 100,000 |
|---|---|---|---|
| 100k rows | < 1ms | ~10ms | ~100ms |
| 10M rows | < 1ms | ~100ms | ~1,000ms |
Keyset Pagination: The Scalable Alternative
Keyset pagination (also called cursor-based pagination) uses the last seen value as a marker instead of counting rows.
-- Instead of: LIMIT 20 OFFSET 100
-- Use: "give me the next 20 rows after ID 4832"
SELECT id, name, created_at
FROM orders
WHERE id > 4832 -- start from last seen ID
ORDER BY id ASC
LIMIT 20;
The database uses the index on id to jump directly to row 4832 — no scan of earlier rows. Performance is O(1) regardless of where you are in the dataset.
Returning the cursor to the client:
{
"data": [...],
"next_cursor": 4852
}
The client sends ?cursor=4852 on the next request.
OFFSET vs. Keyset: When to Use Each
| OFFSET / LIMIT | Keyset / Cursor | |
|---|---|---|
| Performance | Degrades at high offsets | Constant regardless of position |
| Allows jumping to page N | Yes | No — sequential only |
| Works with changing data | Rows may appear/disappear | Stable — no duplicates or gaps |
| Implementation | Simple | Requires a sortable cursor column |
| Best for | Admin UIs, small tables, "jump to page" | APIs, feeds, infinite scroll, large tables |
Common Mistakes
- No ORDER BY: Without
ORDER BY, the skipped rows are arbitrary. Always specify a deterministic sort order. - Offset without an index on the sort column: The database does a full sequential scan. Index the column you
ORDER BY. - Mutating data between pages: If rows are inserted or deleted between requests, OFFSET pages shift — users may see duplicates or miss rows.
- Using OFFSET for APIs: Expose cursor-based pagination in APIs from the start. Changing later breaks clients.
Database Compatibility
| Database | Syntax |
|---|---|
| PostgreSQL | LIMIT n OFFSET m |
| MySQL / MariaDB | LIMIT m, n or LIMIT n OFFSET m |
| SQL Server | OFFSET m ROWS FETCH NEXT n ROWS ONLY |
| Oracle | OFFSET m ROWS FETCH NEXT n ROWS ONLY |
| SQLite | LIMIT n OFFSET m |