Pagination that scales past page 1000: A Technical Guide

Pagination that scales past page 1000 is a classic "trap" in API design. You start with a simple LIMIT and OFFSET query, everything feels snappy, and you ship it. But once your table hits a few million rows, your database performance starts cratering.

I learned this the hard way during an on-call rotation three years ago. We had a dashboard that allowed users to browse logs, and when someone tried to jump to "page 1200," the query latency spiked from 20ms to over 2 seconds. The database was scanning through thousands of rows just to discard them.

Why Offset Pagination Dies at Scale

Most ORMs default to LIMIT 20 OFFSET 1000. In SQL, this translates to:

SQL
SELECT * FROM logs ORDER BY created_at DESC LIMIT 20 OFFSET 20000;

The database engine must read 20,020 rows, sort them, and then discard the first 20,000. It’s an $O(N)$ operation where $N$ is the offset. As the offset grows, the cost grows linearly. Even with an index on created_at, the engine still performs a significant amount of work to calculate the skip.

If you’re killing N+1 queries at the database layer, you’ve already realized that efficient data access is the backbone of a stable system. Offset pagination is the antithesis of that efficiency.

The Solution: Keysets (Cursor-based Pagination)

To achieve pagination that scales past page 1000, you need to stop using absolute positions and start using relative ones. This is called keyset or cursor-based pagination.

Instead of asking for "page 50," you ask for "the next 20 items after this specific record."

How it works

1. Your API returns a cursor (usually the ID or timestamp of the last item in the result set).
2. The client sends that cursor back in the next request.
3. Your database query uses a WHERE clause to filter by that value.

Here is a simplified example using a timestamp and a unique ID to handle ties:

SQL
-- Client sends: GET /logs?limit=20&cursor=2023-10-27T10:00:00Z_5501
SELECT * FROM logs 
WHERE (created_at, id) < ('2023-10-27T10:00:00', 5501)
ORDER BY created_at DESC, id DESC
LIMIT 20;

This query is $O(\log N)$ because the database can jump directly to the index entry matching the cursor. It doesn't matter if you're on page 1 or page 10,000; the performance remains consistent.

Trade-offs and Constraints

There is no free lunch. While cursors are the gold standard for performance, they introduce friction in the user experience:

• No "Jump to Page X": You lose the ability to skip directly to a specific page number. In most modern UIs, this is actually a UX improvement—"infinite scroll" or "load more" is generally preferred over numbered pagination.
• Complex Sorting: If you allow the user to change the sort order, you must re-generate the cursors. The cursor is tightly coupled to the sort criteria.
• Data Consistency: If new items are inserted during pagination, items might shift, potentially causing the user to see the same item twice or skip one. For most log or activity-feed use cases, this is an acceptable trade-off.

Implementation Tips

When I implement this, I usually encode the cursor as a Base64 string. This keeps the client-side implementation clean and prevents users from trying to guess or manipulate the cursor values.

If you are dealing with complex data structures, consider how this fits into your overall architecture. If you're building a highly interactive UI, you might also want to look at streaming and suspense in Next.js: optimize your page load to handle the data fetching gracefully as the user reaches the end of the list.

FAQ

Q: Can I keep offset pagination for small lists? A: Absolutely. If you know for a fact a table will never exceed a few thousand rows, offset pagination is simpler to implement. Don't over-engineer if you don't have the scale.

Q: What if I have duplicate timestamps? A: Always include a unique column, like id or uuid, in your WHERE clause and ORDER BY clause. This ensures a deterministic sort order, even if multiple records share the exact same timestamp.

Q: Is there a library for this? A: Most modern ORMs have cursor-based support built-in. Check your docs for "Relay-style pagination" or "Keyset pagination."

Wrapping Up

Moving to cursor-based pagination was the single biggest performance win for our logging service. We went from query times that would spike to several seconds down to a consistent ~10-15ms.

Next time, I’d probably start with cursor-based pagination from day one, even for small tables. It avoids the refactor headache later and forces you to think about your data ordering early. The "page number" mindset is a relic of the early web; when you’re building systems that need to handle real-world volume, the cursor is your best friend.