Cursor Pagination vs Offset Pagination

You need to return a long list, so you add pagination. The first version uses LIMIT and OFFSET:

SELECT * FROM customers ORDER BY id LIMIT 20 OFFSET 4000;

Page 1 is instant. The demo looks fine. But this has two problems, and both appear later.

Problem 1: it gets slow

OFFSET 4000 does not jump to row 4000. The database reads the first 4000 rows, throws them away, and then returns the next 20. The deeper the page, the more work it does for nothing. On a big table, the last pages become very slow.

Problem 2: it skips and repeats rows

OFFSET counts rows by position, not by identity. But between two requests, the data can change.

You read page 1 (the first 20 rows). Then someone deletes a customer from page 1. Every row after it moves up by one. Now the row that used to be at position 21 sits at position 20, inside the page you already read. When you ask for page 2 with OFFSET 20, you start after it, so you never see that row. It fell through the gap.

An insert before your current position does the opposite: it pushes rows down, and you read the same row two times.

So offset pagination can give you holes and duplicates. For a UI this is annoying. For a job that must read every row one time, like a sync, it is a real bug.

The fix: remember where you stopped

Cursor pagination (also called keyset pagination) does not count positions. It remembers the last value you saw, and asks for the rows after it:

SELECT * FROM customers
WHERE id > 4283         -- the last id from the previous page
ORDER BY id
LIMIT 20;

The id of the last row is your cursor. You send it back to ask for the next page.

This is fast, because with an index the database jumps straight to id > 4283 and reads 20 rows. And it is stable: a change earlier in the list does not shift your position, so you do not skip or repeat rows.

The one rule

Cursor pagination needs a column that is unique and ordered, like an auto-increment id or a (timestamp, id) pair. If your sort column has duplicates, add the id as a tie-breaker, or you can still skip rows.

This is why an API that you want to keep in sync with another system must offer cursor-based listing: it is the only way to read all the rows one time, safely, even while the data keeps changing.