Cursor vs Offset Pagination at Scale
Implement keyset/cursor pagination for stable, performant listing over large, frequently changing datasets.
Why Pagination Strategy Matters
When a list endpoint returns thousands or millions of rows, you must page the results. The two dominant strategies are offset pagination (LIMIT/OFFSET or ?page=3) and cursor/keyset pagination (?after=<token>).
- Offset is simple and supports jumping to arbitrary pages.
- Cursor is stable and fast on large, frequently changing datasets.
This lesson shows why offset breaks down at scale and how to implement keyset pagination correctly in a FastAPI service.
How Offset Pagination Works
Offset pagination tells the database to skip N rows and return the next page-sized chunk. A request for page 3 with page size 20 becomes OFFSET 40 LIMIT 20.
The endpoint is trivial to write and lets clients jump directly to any page. Here is a minimal in-memory simulation of how offset slicing behaves.
def offset_page(rows, page, size):
start = (page - 1) * size
end = start + size
return rows[start:end]
items = [f"item-{i}" for i in range(1, 101)]
print("page 1:", offset_page(items, 1, 5))
print("page 3:", offset_page(items, 3, 5))
print("page 20:", offset_page(items, 20, 5))All lessons in this course
- URL, Header and Media-Type Versioning
- Cursor vs Offset Pagination at Scale
- Dynamic Filtering and Sorting Parameters
- Designing Stable Response Envelopes