DEV Community

Kanak Tanwar
Kanak Tanwar

Posted on

REST Pagination techniques

#fastapi #pagination #restapi #python

My thoughts had always been that pagination was straightforward — just throw in a LIMIT and OFFSET and you’re good to go. But recently, I got to know that there are different pagination techniques, some more efficient, some used for a specific use case. And so I thought of writing about them, along with code a rudimentary code implementation in FastAPI and some benchmarks of their performance.

The code can be found https://github.com/kanakOS01/pagination.
The instructions for running the code are in the repo itself. If you find it helpful a star would be great.

Among the different types of pagination I found online (offset, page, cursor, keyset etc), we can broadly classify pagination into 2 categories

Offset Pagination

Offset pagination is the most common form. It’s the classic LIMIT/OFFSET approach:

SELECT * FROM data
ORDER BY id
OFFSET 1000 LIMIT 10;
Enter fullscreen mode Exit fullscreen mode

  • Pros:

    • Simple to implement.
    • Great for small datasets.
    • Supports “jump to page X.”

  • Cons:

    • Slow for large offsets (e.g., OFFSET 1M).
    • Can cause inconsistent results if data changes between requests.

Page Pagination

Page pagination is just a wrapper around offset. Instead of offset=1000, you say page=100 with page_size=10, and the system translates it into an offset internally.

SELECT * FROM data
ORDER BY id
OFFSET (page - 1) * page_size LIMIT page_size;
Enter fullscreen mode Exit fullscreen mode

  • Pros:

    • Familiar and user-friendly.
    • Easier UX for users who want to jump to a specific page.

  • Cons:

    • Same performance problems as offset.
    • Still prone to data inconsistency with inserts/deletes.

Cursor Pagination

Cursor pagination uses a known column value as an anchor (like id or created_at) instead of a raw offset.

SELECT * FROM pagination_dataset
WHERE id > cursor_id
ORDER BY id
LIMIT 10;
Enter fullscreen mode Exit fullscreen mode

  • Pros:

    • Super efficient — no scanning and discarding rows.
    • Scales well for large datasets.
    • Great for sequential or immutable data (logs, feeds).

  • Cons:

    • No direct “jump to page 100.”
    • Exposes database internals (like IDs) if not wrapped.

This is the approach used by modern APIs like GitHub, Twitter, and Facebook.

Keyset Pagination

I could not find much difference between keyset and cursor pagination. The idea for both of them is the same (like offset and page). One difference would be that cursor pagination returns a cursor like object (encoded by the application code), this prevents leaking information through endpoints. This however also introduces minor overhead.

Benchmarking

So, I thought it would be fun to benchmark these approaches myself to verify the results I had in my mind (there are a number of benchmarks and comparisons available on the net already but I thought why not just do it again :)).

So I made a simple script (available in the github repo mentioned at the top) to benchmark offset and cursor pagination. And here are the results.

Cursor vs Offset pagination

Final Thoughts

  • Use offset/page pagination for small datasets or cases where “jump to page X” is important.
  • Use keyset or cursor pagination for large datasets, APIs, and infinite scroll use cases.
  • If you’re building a public API, cursor pagination is the way to go.

Pagination might look trivial at first, but at scale, the right choice can save you massive performance headaches.

Top comments (0)