API Pagination: Cursor vs Offset, Consistency & Performance

API Pagination

Why Engineers Care About This

Pagination is how you return large datasets efficiently. Without pagination, returning millions of records would be slow, memory-intensive, and wasteful. But pagination strategies have trade-offs—offset-based is simple but degrades with dataset size, cursor-based is efficient but more complex. Understanding pagination helps you design APIs that scale.

When your API is slow for large datasets, or pagination breaks with concurrent modifications, or clients can't navigate results efficiently, you're hitting pagination problems. These problems compound. Without proper pagination, large datasets cause timeouts and memory issues. Poor pagination strategies degrade performance as data grows. Good pagination enables efficient data retrieval at scale.

In interviews, when someone asks "How would you paginate this list?", they're really asking: "Do you understand different pagination strategies? Do you know when to use cursor-based vs offset-based? Do you understand the performance implications?" Most engineers don't. They use offset-based pagination for everything (simple but inefficient) or avoid pagination entirely.

Core Intuitions You Must Build

• Offset-based pagination is simple but degrades with dataset size. Offset-based pagination (LIMIT 20 OFFSET 100) is easy to understand and implement. But it degrades as offset increases—to get page 1000, the database must skip 19,980 records, which is slow. Also, offset-based pagination breaks with concurrent modifications (new records shift offsets). Use offset-based for small datasets or when you need random page access.

• Cursor-based pagination is efficient but more complex. Cursor-based pagination uses a cursor (usually an ID or timestamp) to mark position. To get the next page, query records after the cursor. This is efficient—no skipping, just filtering. But it's more complex—cursors must be stable (don't change), and you can't jump to arbitrary pages. Use cursor-based for large datasets or when you need consistent results during data changes.

• Pagination parameters should be consistent and intuitive. Use standard pagination parameters (limit, offset for offset-based, cursor or after for cursor-based). Also, return pagination metadata (hasNext, hasPrevious, nextCursor, totalCount if needed). This helps clients navigate results and build UIs. Don't invent custom pagination parameters—use standards.

• Pagination works with filtering and sorting, but adds complexity. Pagination is often combined with filtering and sorting. This works, but adds complexity—filters and sorts must be applied before pagination. Also, cursor-based pagination with sorting requires stable sort keys (don't change). Design pagination that works well with filtering and sorting—consider these together, not separately.

• Total count is expensive for large datasets. Returning total count (total: 1,000,000) requires counting all matching records, which is expensive for large datasets. Consider whether clients need total count—if they only need "has more pages," don't return total count. If they need total count (for progress bars), consider approximate counts or caching.

• Pagination metadata helps clients build UIs. Return pagination metadata (hasNext, hasPrevious, nextCursor, prevCursor) in responses. This helps clients build UIs (show/hide next/previous buttons, display page numbers). Also, include links for next/previous pages (HATEOAS-style) to make navigation easier.

Subtopics (Taught Through Real Scenarios)

Offset-Based Pagination

What people usually get wrong:

Engineers often use offset-based pagination (LIMIT 20 OFFSET 100) for everything. It's simple and intuitive. But offset-based pagination degrades as offset increases—to get page 1000, the database must skip 19,980 records, which is slow. Also, offset-based pagination breaks with concurrent modifications (new records shift offsets, causing duplicates or skipped records). Use offset-based for small datasets or when you need random page access.

How this breaks systems in the real world:

A service used offset-based pagination for a large dataset (millions of records). Users could paginate through results. But as users went deeper (page 100, page 1000), pagination became slow (database had to skip many records). Also, during pagination, new records were added, shifting offsets and causing duplicates or skipped records. The fix? Use cursor-based pagination for large datasets—no skipping, just filtering, and stable cursors prevent duplicates. But the real lesson is: offset-based pagination degrades with dataset size and breaks with concurrent modifications.

What interviewers are really listening for:

They want to hear you talk about offset-based pagination, its limitations (degradation with offset, concurrent modification issues), and when to use it. Junior engineers say "just use LIMIT/OFFSET." Senior engineers say "offset-based pagination is simple but degrades with dataset size and breaks with concurrent modifications—use it for small datasets or when you need random page access." They're testing whether you understand that pagination strategies have trade-offs.

Cursor-Based Pagination

What people usually get wrong:

Engineers often avoid cursor-based pagination because it's "too complex." But cursor-based pagination is efficient for large datasets—no skipping, just filtering. Cursors must be stable (don't change) and unique (identify records). Use record IDs or timestamps as cursors. Also, cursors must be opaque (base64-encoded) to prevent clients from manipulating them. Cursor-based pagination is more complex but more efficient.

How this breaks systems in the real world:

A service used offset-based pagination for a large dataset. As the dataset grew, pagination became slow (deep pages required skipping many records). The fix? Use cursor-based pagination—query records after cursor, no skipping needed. Pagination became fast regardless of dataset size. But the real lesson is: cursor-based pagination is efficient for large datasets. Use it when offset-based pagination becomes slow.

What interviewers are really listening for:

They want to hear you talk about cursor-based pagination, cursor stability, and efficiency. Junior engineers say "cursor-based is too complex." Senior engineers say "cursor-based pagination is efficient for large datasets (no skipping, just filtering), requires stable cursors (record IDs or timestamps), and is more complex but scales better than offset-based." They're testing whether you understand that cursor-based pagination solves offset-based pagination's problems.

Pagination with Filtering and Sorting

What people usually get wrong:

Engineers often implement pagination without considering filtering and sorting. But pagination is often combined with filtering and sorting. This works, but adds complexity—filters and sorts must be applied before pagination. Also, cursor-based pagination with sorting requires stable sort keys (don't change). Design pagination that works well with filtering and sorting—consider these together, not separately.

How this breaks systems in the real world:

A service implemented pagination and sorting separately. Users could sort by date, then paginate. But cursor-based pagination used record IDs as cursors, which didn't work with date sorting (cursors were based on ID, not date). Pagination broke when combined with sorting. The fix? Use sort key (date) as cursor when sorting by date, or use composite cursors (ID + date). But the real lesson is: pagination, filtering, and sorting must work together. Design them together, not separately.

What interviewers are really listening for:

They want to hear you talk about combining pagination with filtering and sorting, and the complexity it adds. Junior engineers say "just add pagination, filtering, and sorting." Senior engineers say "pagination with filtering and sorting adds complexity—filters and sorts must be applied before pagination, and cursor-based pagination with sorting requires stable sort keys." They're testing whether you understand that pagination is part of a larger query system.

---

• Offset-based pagination is simple but degrades with dataset size—use for small datasets or random page access
• Cursor-based pagination is efficient for large datasets—no skipping, just filtering, but more complex
• Pagination parameters should be consistent—use standard parameters (limit, offset, cursor)
• Pagination works with filtering and sorting—but adds complexity, design them together
• Total count is expensive for large datasets—only return if needed, consider approximate counts
• Pagination metadata helps clients build UIs—return hasNext, hasPrevious, nextCursor
• Choose pagination strategy based on dataset size and access patterns—not just simplicity

---

• API Design - Designing list endpoints with pagination
• Databases - Database query optimization for pagination