OFFSET

Definition

OFFSET skips the first N rows of an ordered result set before returning rows. Combined with LIMIT, it is the classic mechanism for pagination: "give me page 3, 10 rows per page."

sql
SELECT column1 FROM table_name
ORDER BY ...
LIMIT page_size OFFSET (page_number - 1) * page_size;

Worked Example

Assume products is ordered by id and we want 2 items per page.

sql
-- Page 1: skip 0, take 2
SELECT id, name FROM products ORDER BY id LIMIT 2 OFFSET 0;

 id | name
----+-------------------
 1  | Wireless Mouse
 2  | Bluetooth Speaker

sql
-- Page 2: skip 2, take 2
SELECT id, name FROM products ORDER BY id LIMIT 2 OFFSET 2;

 id | name
----+-------------
 3  | Desk Lamp
 4  | Yoga Mat

sql
-- Page 3: skip 4, take 2
SELECT id, name FROM products ORDER BY id LIMIT 2 OFFSET 4;

 id | name
----+---------------
 5  | Notebook Set
 6  | Phone Case

This LIMIT ... OFFSET ... pattern is the naive pagination approach — simple, intuitive, and fine for small tables or early pages.

The Performance Pitfall

OFFSET does not magically jump to row N. Internally, the database must still produce and then discard every one of the first N rows before it can start returning the rows you actually asked for. OFFSET 4 isn't expensive on an 8-row table, but consider:

sql
SELECT id, name FROM products ORDER BY id LIMIT 20 OFFSET 1000000;

To answer this, the engine typically must scan (or walk an index) through 1,000,000 rows, throw all of them away, and only then hand you the next 20. As the offset grows, query time grows roughly linearly with it — page 1 might take 2ms, page 50,000 might take several seconds, even though both pages only return 20 rows. This is exactly the problem addressed by Keyset Pagination (13.6).

Edge Cases

• OFFSET larger than the number of matching rows simply returns an empty result — no error.
• OFFSET without LIMIT is legal in most engines (skip N, return everything after) but rare in practice.
• OFFSET inherits all the non-determinism risk of LIMIT if there is no ORDER BY — "page 2" is meaningless without a defined order to skip through.

Key Takeaways

• Q: What does OFFSET do?

A: Skips the first N rows of the ordered result before returning the next rows.

• Q: Why does `OFFSET 1000000` get slow?

A: The database must still generate and discard all 1,000,000 skipped rows internally — it can't skip directly to row 1,000,001 without doing that work.

• Q: Is LIMIT+OFFSET the only pagination technique?

A: No — see Keyset Pagination (13.6) for a faster alternative at scale.