Pagination is an essential feature for efficiently retrieving large datasets in web applications. Traditionally, offset-based pagination (LIMIT ... OFFSET ...) has been the default approach, but it suffers from performance issues as datasets grow. A more efficient alternative is cursor-based pagination, which eliminates unnecessary database scans and improves response times.

In this article, we’ll compare these two pagination methods, analyze their performance trade-offs, and demonstrate how cursor pagination provides a faster, more scalable solution using GraphQL.

Offset-Based Pagination

Offset-based pagination is a straightforward approach where the database fetches a fixed number of records while skipping a certain number of rows.

Code Example — Retrieving paginated list of transactions using Offset-based pagination.

This C# method, GetTransactions, is an asynchronous function that retrieves a paginated list of transactions.

SQL Server

SELECT * FROM Transactions
ORDER BY TransactionDate DESC
LIMIT 10 OFFSET 50;

Entity Framework

public async Task<IEnumerable<Transaction>> GetTransactions(int offset, int limit, [Service] AppDbContext db)
{
    return await db.Transactions
        .OrderByDescending(t => t.TransactionDate)
        .Skip(offset)
        .Take(limit)
        .ToListAsync();
}

GraphQL

query {
  transactions(offset: 50, limit: 10) {
    id
    amount
    transactionDate
  }
}

Performance Issues

1. Performance Degrades with Higher Offsets

The database scans and discards rows before returning results. For instance, with OFFSET 100000, the database reads 100,000 rows before fetching just 10, increasing I/O, memory usage, and query execution time.

2. Data Inconsistencies (Phantom Records)

Since offset values are based on row positions, insertions, deletions, or updates can cause missing, duplicate, or reordered records.

• New records inserted → Earlier pages shift, leading to duplicate results.
• Records deleted → Later pages shift, causing missing results.
• Updated records → Sorting changes can reorder results, making them appear twice or disappear.

3. Increased Memory & CPU Usage

High offset values lead to longer query execution times, especially when:

• Sorting on non-indexed columns
• Using complex joins and aggregations
• Running frequent queries on large datasets

Cursor-Based Pagination — A Faster Alternative

Cursor pagination avoids the costly offset scan by using a unique cursor (such as a timestamp or ID) to fetch the next batch of results relative to the last retrieved item.

Instead of specifying an offset, the client sends a cursor referencing the last retrieved item. The database fetches only the next set of results greater than (or less than) the cursor value.

Code Example — Retrieving paginated list of transactions using Cursor-based pagination.

SQL Query

SELECT * FROM Transactions
WHERE TransactionDate < '2024-02-01T12:00:00Z'
ORDER BY TransactionDate DESC
LIMIT 10;

Entity Framework

public async Task<TransactionConnection> GetTransactions(
    string? after, int first, [Service] AppDbContext db)
{
    var query = db.Transactions
        .OrderByDescending(t => t.TransactionDate)
        .AsQueryable();

    if (!string.IsNullOrEmpty(after))
    {
        DateTime cursorDate = DateTime.Parse(after);
        query = query.Where(t => t.TransactionDate < cursorDate);
    }

    var transactions = await query.Take(first + 1).ToListAsync();
    var hasNextPage = transactions.Count > first;

    return new TransactionConnection
    {
        Edges = transactions.Take(first).Select(t => new TransactionEdge
        {
            Node = t,
            Cursor = t.TransactionDate.ToString("o")  // ISO 8601 format
        }).ToList(),
        PageInfo = new PageInfo
        {
            HasNextPage = hasNextPage,
            EndCursor = hasNextPage ? transactions.Last().TransactionDate.ToString("o") : null
        }
    };
}

GraphQL

query {
  transactions(after: "2024-02-01T12:00:00Z", first: 10) {
    edges {
      node {
        id
        amount
        transactionDate
      }
      cursor
    }
    pageInfo {
      hasNextPage
      endCursor
    }
  }
}

Cursor Pagination is More Performant

1. No Skipping Over Rows

Instead of scanning and discarding previous rows, cursor-based pagination fetches only the next required set of records using an indexed WHERE condition.

2. Consistent Data

Each query fetches results after a known cursor value, preventing duplicate or skipped records.

3. Better Index Utilization

Cursor pagination leverages indexed lookups, allowing the database to fetch only the necessary records without full table scans.

Comparing Performance — Offset vs. Cursor

Performance on Large Data

• Offset Pagination: Slower as OFFSET increases, leading to degraded performance with large datasets.
• Cursor Pagination: Fast and scalable, efficiently retrieves only the required records.

Index Utilization

• Offset Pagination: Poor performance due to full table scans.
• Cursor Pagination: Excellent performance through indexed lookups.

Data Consistency

• Offset Pagination: Can show duplicate or missing records if data changes between requests.
• Cursor Pagination: Ensures a stable order, avoiding inconsistencies.

Scalability

• Offset Pagination: Struggles with high offsets, leading to slower query times.
• Cursor Pagination: Ideal for infinite scrolling and large datasets, maintaining efficiency and speed.

When to Use Cursor Pagination

✅ For large datasets (millions of records)
✅ In real-time feeds (e.g., e-commerce orders, social media posts)
✅ When implementing infinite scrolling (e.g., messaging apps, activity logs)

When Offset Pagination is Acceptable

✅ Small datasets (<10,000 rows)
✅ Simple admin dashboards

Cursor pagination is the superior choice for efficiently managing large datasets in Entity Framework. It eliminates costly offset scans, ensures data consistency, and enhances the user experience. If your application deals with large-scale data, switching to cursor pagination can significantly improve performance and scalability.