Pagination: why 'next page' shows a post twice, and how a bookmark fixes it

You scroll a feed, tap "next page", and see the same post twice. Or a table of a million rows loads page 1 instantly but page 90,000 takes a noticeable pause. Both come from the same habit: asking the database for "page N" by counting rows. This article shows why counting is fragile, and what to do instead.

The usual way: LIMIT and OFFSET

A database stores rows in tables, and SQL is the language you use to ask for them. To show 5 posts at a time, newest first, the common recipe uses two keywords. LIMIT 5 means "give me at most 5 rows". OFFSET 5 means "skip the first 5 rows of the answer".

-- page 1
SELECT id, title FROM posts ORDER BY id DESC LIMIT 5 OFFSET 0;
-- page 2
SELECT id, title FROM posts ORDER BY id DESC LIMIT 5 OFFSET 5;
-- page 3
SELECT id, title FROM posts ORDER BY id DESC LIMIT 5 OFFSET 10;

It reads naturally: page number times page size gives the offset. The trouble is that "skip 5 rows" means "skip whatever the first 5 rows are right now".

Problem 1: the list moves under you

Imagine a table with posts 1 to 12. Page 1 (newest first) shows 12, 11, 10, 9, 8. Before you tap "next", someone publishes post 13. Now the newest-first list is 13, 12, 11, 10, 9, 8, 7... and "skip 5" lands one row too early. I ran exactly this in SQLite:

page 1:        [12, 11, 10, 9, 8]
(post 13 is added)
page 2 OFFSET: [8, 7, 6, 5, 4]    -- 8 appears twice!

Post 8 is shown twice. If a post had been deleted instead, the list would shift the other way and one post would be skipped, never shown at all. Nothing is broken in the database; it answered the question you asked. The question was just the wrong one.

Problem 2: skipping still costs work

The database can't jump straight to row 900,000. To "skip" rows it has to walk past them first and throw them away. So OFFSET 900000 does the work of reading 900,000 rows to return 10. The deeper the page, the slower it gets.

The fix: remember where you stopped

Instead of "skip 5 rows", say "give me the rows after the last one I saw". This is called keyset pagination (also "cursor" or "seek" pagination). The last row you showed is your bookmark:

-- you last saw id 8
SELECT id, title FROM posts
WHERE id < 8
ORDER BY id DESC
LIMIT 5;

Run on the same data after post 13 appeared, this returns [7, 6, 5, 4, 3]: no repeat, no gap, because the bookmark is a value, not a count. And because id is a primary key (which databases keep in sorted order, like the index in a book), the database can jump to "id below 8" directly instead of walking from the top.

Try it: both styles side by side

Below is a feed of posts. Load a few pages on each side, then press "New post arrives" and load another page. Watch the left column for repeats, and watch the "rows read" counter on each side.

Measured: how much slower is a deep page?

I built a table of 1,000,000 posts in SQLite (version 3.53 in the test) and asked for 10 rows around position 900,000:

Query	Time (this machine)	Query plan
OFFSET 900000	about 6 ms	SCAN posts
WHERE id < 100001	about 0.03 ms	SEARCH posts USING INTEGER PRIMARY KEY

Your numbers will differ, and SQLite in memory is fast, so 6 ms looks harmless. The point is the ratio (roughly 200 times here) and the shape: OFFSET gets slower the deeper you go, keyset stays flat. A server database over a network, with bigger rows, makes the gap larger. The plan words show the reason: SCAN walks rows, SEARCH jumps straight to a spot.

What keyset pagination can't do

Nothing is free. Keyset has trade-offs worth knowing:

• You can't jump to "page 47". You can only go to the next page from where you are (or the previous one, with the reverse condition).
• The sort column must be unique, or ties will cause skips. If you sort by something like date, add id as a tie-breaker: WHERE (created_at, id) < (?, ?). Many databases, including SQLite since 3.15, support comparing pairs like this.
• For fast jumps, the sort columns need an index. A primary key already has one.

So which should you use?

For an admin table with a few hundred rows where people want "go to page 7", OFFSET is fine and simpler. For an endless feed, a big table, or anything where rows are added while people read, use a bookmark. When you build an API for a feed, hand the client an opaque "next cursor" value (the last id, encoded) instead of a page number, so you can change how it works later.