The Problem
Your users table has 5 million records. A client requests GET /users. You can't return all 5 million records in one response, that would be gigabytes of data, take minutes to load, and crash mobile clients. Pagination solves this: return results in pages of 20 to 100 records, but the cursor vs offset pagination decision you make here determines whether that endpoint stays fast as the table grows.
But how you implement pagination has massive performance implications. The wrong choice turns a 5ms query into a 5 second query, and your database administrator sends you angry messages at 3am.
Architecture overview
Offset Pagination: Simple but Dangerous
The intuitive approach: GET /users?page=3&limit=20 translates to skip the first 40 records, return the next 20.
The deeper you paginate, the slower it gets. It's O(N), where N is the offset value.
There's a second problem: if a new record is inserted while you're paginating, you might see a record twice (on page N and page N+1) or skip one entirely. The data is shifting under your feet while you read it.
Cursor Pagination: Fast at Any Depth
Instead of saying "skip N records," cursor pagination says "give me records after this specific point." The cursor is typically the ID or timestamp of the last item you received, and it's the backbone of most modern API pagination best practices.
Whether you're on page 1 or page 50,000, the database uses the index to jump straight to the starting point. No scanning, no skipping.
Visual Comparison
The API Response Format
A good cursor-based response for pagination in a REST API looks like this:
Sorting by Non-Unique Columns
What if you need to sort by created_at instead of id? Multiple users can share the same created_at, so a plain cursor on that column alone isn't safe. Use a composite cursor instead:
When to Use Which
This is the core of the cursor vs offset pagination decision, and it usually comes down to how deep users will paginate and how fast your data grows:
Criteria | Offset | Cursor |
|---|---|---|
Performance at deep pages | O(N), gets slower | O(1), always fast |
Jump to page X | Yes (page=47) | No, sequential only |
Data consistency during inserts | Duplicates/skips possible | Stable, no shifting |
Total count | Easy (COUNT(*)) | Expensive or separate query |
Best for | Admin panels, small datasets (<100K rows) | Public APIs, infinite scroll, mobile apps, any growing dataset |
Numbers That Matter
Offset of 1M on a table of 10M rows: roughly 500ms to 5s depending on row size and indexes
Cursor pagination on the same table at any position: a consistent 1 to 5ms
Twitter's timeline API uses cursor pagination, serving billions of timeline requests per day
Stripe's list APIs all use cursor pagination, with auto-pagination built into their client libraries
GitHub's GraphQL API uses cursor-based connections exclusively
If you're also thinking about how to keep those paginated reads fast under load, it's worth pairing this with a solid caching strategy, since cached first pages can absorb a huge share of traffic before the database ever sees a query.
Interview Tip
When an interviewer asks about pagination, immediately say: "I would use cursor-based pagination for any public-facing API or dataset that will grow, because offset pagination is O(N) at deep pages while cursor is O(1). The cursor would be a Base64-encoded composite of the sort key and a unique tiebreaker like the row ID." Then add: "For admin dashboards where users need to jump to page 47, offset is acceptable if the dataset is small." This shows you understand both approaches and their trade-offs.
Pagination is a small piece of a bigger picture, and it tends to come up alongside broader API design questions in interviews. If you want to see how this fits into the rest of the design, the low-level design fundamentals guide and the piece on API gateways and BFF patterns are good next reads.
Key Takeaway
Offset pagination is O(N), it degrades the deeper you go. Cursor pagination is O(1), consistently fast regardless of position. For any public API or dataset that might grow, default to cursor pagination. Use composite cursors (sort key plus ID) when sorting by non-unique columns.