Pagination Strategies in Backend Systems: Offset vs Cursor

When building APIs or data-heavy applications, pagination is not optional—it’s a necessity. Whether you’re loading a social media feed, listing users, or querying logs, how you paginate directly impacts performance, consistency, and user experience.

Pagination Strategies in Backend Systems: Offset vs Cursor

In this blog, we’ll break down the two most common pagination strategies—offset-based and cursor-based pagination—and understand when to use each.

📌 What is Pagination?

Pagination is the process of dividing large datasets into smaller, manageable chunks (pages), so clients don’t have to load everything at once.

⚙️ Offset Pagination

Offset pagination is the most straightforward approach and is commonly used in many applications.

🔹 How it works

SELECT * FROM users
ORDER BY id
LIMIT 10 OFFSET 20;

This means:

Skip the first 20 rows
Return the next 10 rows

✅ Advantages

Easy to implement
Supports page-based navigation (page=1,2,3...)
Works well for small datasets

❌ Disadvantages

1. Performance Issues at Scale

As the offset increases, the database must scan and skip more rows.

For example:

OFFSET 10,000 → DB scans 10,000 rows just to discard them

👉 This leads to O(n) complexity

2. Inconsistent Results

If data changes between requests:

New rows inserted → duplicates
Rows deleted → missing data

👉 Results become unreliable

📌 When to Use Offset Pagination

Admin dashboards
Reporting tools
Small or static datasets

🚀 Cursor Pagination (Keyset Pagination)

Cursor pagination is designed for performance and consistency, especially in large-scale systems.

🔹 How it works

Instead of skipping rows, we use a reference (cursor) to fetch the next set.

SELECT * FROM posts
WHERE id > 100
ORDER BY id
LIMIT 10;

Here:

100 is the last seen ID (cursor)
We fetch the next 10 records after it

✅ Advantages

1. High Performance

No row skipping
Uses indexes efficiently
Constant query time

2. Consistent Results

No duplicates or missing records
Safe even when data changes

❌ Disadvantages

Cannot jump to arbitrary pages (e.g., page 10)
Requires careful implementation
Needs stable and deterministic sorting

⚠️ Key Concepts to Understand

🔹 Stable Ordering

Sorting must remain consistent across queries.
Example:

ORDER BY created_at DESC, id DESC

🔹 Composite Cursor

If created_at is not unique, use a tie-breaker: (created_at, id)

This prevents duplicate or skipped records.

🔹 Forward vs Backward Pagination

Forward: WHERE id > cursor

Backward: WHERE id < cursor

🧠 Offset vs Cursor — Quick Comparison

Performance

Offset → Degrades as data grows (due to row skipping)
Cursor → Constant performance (uses index efficiently)

Consistency

Offset → Can return duplicates or miss records if data changes
Cursor → Provides consistent results even with inserts/deletes

Implementation

Offset → Simple and quick to implement
Cursor → More complex (requires cursor logic and stable sorting)

Page Navigation

Offset → Supports direct page access (e.g., page 5, page 10)
Cursor → No direct page jumping, sequential navigation only

Use Case

Offset → Best for small datasets, admin dashboards
Cursor → Best for large-scale systems, APIs, infinite scroll feeds

🏗️ Real-World Example

Think of platforms like Instagram or Twitter.

They don’t use page numbers
They use infinite scroll
They load data using cursors

👉 This ensures fast and consistent user experience even with millions of records.

🎯 Final Takeaway

Offset pagination is simple but doesn’t scale well due to performance and consistency issues. Cursor pagination, while slightly more complex, provides efficient and reliable data retrieval for large and dynamic datasets.

Pagination Strategies in Backend Systems: Offset vs Cursor

Table of Content