LIMIT and OFFSET for Paging

LIMIT caps the number of rows returned. OFFSET skips that many rows before the cap is applied. Together they give you a page: LIMIT 2 OFFSET 2 is the third and fourth row of the sorted result. The ORDER BY is not optional in practice — without it the engine is free to return rows in any order, so the pages are not stable.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  city VARCHAR(80) NOT NULL
);

INSERT INTO customers (id, name, city) VALUES
  (1, 'Ada Lovelace', 'London'),
  (2, 'Grace Hopper', 'New York'),
  (3, 'Alan Turing',  'London'),
  (4, 'Joan Clarke',  'London'),
  (5, 'Katherine Johnson', 'Hampton');

-- Page 2 with a page size of 2: rows 3 and 4.
SELECT id, name
FROM customers
ORDER BY id
LIMIT 2 OFFSET 2;

-- Page 3: row 5.
SELECT id, name
FROM customers
ORDER BY id
LIMIT 2 OFFSET 4;

The first query returns Alan Turing and Joan Clarke, the second returns Katherine Johnson. Note the ordering of the two keywords: LIMIT comes first, OFFSET second, in SQLite, PostgreSQL and MySQL. SQL Server spells it OFFSET n ROWS FETCH NEXT m ROWS ONLY and insists on an ORDER BY.

Now the problem. OFFSET makes the engine produce and discard the first n rows, so page 100 has to walk through a hundred rows before it can start — the cost grows linearly with the page number, and a deep page can be slow enough to time out. Worse, it is wrong under concurrent writes. If a customer is deleted between two requests, every row after the deletion shifts up by one, and one row is skipped entirely; if a row is inserted at the top, one row is repeated. The user sees duplicates and gaps while paging.

The other problem is that OFFSET cannot be pushed into an index when the sort key is not the same as the offset key, so even a moderately deep page on a large table can be expensive.

The fix is keyset pagination: instead of saying "skip 100 rows", you say "give me rows after the last id I saw". The next query continues exactly where the previous one stopped, costs the same regardless of page number, and is immune to insertions and deletions above the cursor. The cost is that you need a stable, indexed sort key and that you cannot jump to an arbitrary page number.

Keyset pagination — also called seek pagination — replaces the offset with a cursor. Instead of telling the database "skip 50 rows and give me the next 20", you tell it "give me the 20 rows that come after the last row I received", using the sort key as the cursor: WHERE id > :last_seen_id ORDER BY id LIMIT 20.

The advantages are three. First, cost: the engine seeks directly to the cursor value in the index and reads 20 rows, whether it is page 1 or page 5000. OFFSET has to walk and discard every skipped row, so its cost grows with the page number. Second, correctness under concurrency: because the cursor is a value rather than a position, rows inserted or deleted elsewhere in the table do not shift the window. A new customer at the top simply does not appear until you restart the scan, and a deleted customer does not cause a duplicate. Third, it matches how a real cursor-based API works, since the client holds a token rather than a page number.

The costs are honest ones. You cannot jump to an arbitrary page — page 50 is only reachable by walking to it — so the interface has to be next-and-previous rather than numbered. The sort key must be unique and immutable; if you sort by a timestamp that can tie, add the primary key as a second component and build a composite comparison. And the cursor has to be encoded safely, typically as an opaque base64 token, so the client cannot tamper with it.

There is a middle path worth mentioning: the WHERE clause can combine both approaches — WHERE (sort_key, id) > (:last_sort_key, :last_id) — using row-value comparison, which SQLite, PostgreSQL and MySQL all support. That gives you a deterministic total order with a single index seek.