Page 1 loads instantly. Page 10 is fine. Page 500? Your API takes 4 seconds. Users on page 1000 give up entirely.

I spent way too long blaming “slow queries” before I realized the pagination itself was the problem.

Why Offset Pagination Breaks#

SELECT * FROM messages
ORDER BY created_at DESC
LIMIT 20 OFFSET 50000;

MySQL doesn’t skip to row 50,000. It reads 50,020 rows, throws away the first 50,000, and returns 20. The deeper the page, the more work. EXPLAIN will show you a growing row scan count that makes the cost obvious.

There’s another problem: the shifting window. User is on page 5. A new message arrives. Everything shifts by one. User clicks “next” and either sees a duplicate or misses a row entirely.

Cursor Pagination#

Instead of “skip N rows,” say “give me rows after this point.”

SELECT * FROM messages
WHERE created_at < '2026-02-15 08:30:00'
ORDER BY created_at DESC
LIMIT 20;

With an index on created_at, MySQL seeks directly to the right position. No scanning, no skipping. Page 1 and page 1000 cost the same.

public List<Message> getMessages(String conversationId, Instant cursor) {
    if (cursor == null) {
        return messageRepo.findRecent(conversationId, PageRequest.of(0, 20));
    }
    return messageRepo.findBefore(conversationId, cursor, PageRequest.of(0, 20));
}

The “cursor” is just the last item’s sort key. Client sends it back, server uses it as the starting point for the next page.

graph TD O[Offset: OFFSET 50000] --> S[Scan 50,020 rows] S --> D[Discard 50,000] D --> R1[Return 20] C[Cursor: WHERE created_at < X] --> SK[Seek to index position] SK --> R2[Return 20] style O fill:#000000,stroke:#ff0000,stroke-width:2px,color:#fff style S fill:#000000,stroke:#ff0000,stroke-width:2px,color:#fff style D fill:#000000,stroke:#ff0000,stroke-width:2px,color:#fff style R1 fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style C fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style SK fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style R2 fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff

The Trade-Off#

Cursor pagination doesn’t support “jump to page 47.” You can only go forward or backward. For most feeds, message histories, and activity logs, that’s fine. Users scroll, they don’t jump.

At Oracle, we had a notification feed with 2M+ rows. Offset pagination worked for months until adoption grew and users started scrolling deeper. Page load times crossed 2 seconds around page 200. Switching to cursor-based pagination with a composite index on (user_id, created_at) brought every page back under 30ms. Same data, same query optimization framework, just a different access pattern.

If your data is sharded, cursor pagination is even more important. Offset across shards requires each shard to return its full offset, then merge. Cursors let each shard independently seek.

What I’m Learning#

Offset pagination is the default everyone reaches for. It works fine until it doesn’t, and by then you’ve baked it into your API contract. Starting with cursor pagination costs almost nothing extra upfront and saves you a painful migration later.

Do you use offset or cursor pagination? At what scale did you hit the wall?