Offset vs Cursor Pagination in EF Core

Introduction

Most .NET developers learn offset pagination first. Pass a page number, pass a page size, let the database skip the rows before your page and return the rest. It's the example every tutorial uses, and it works fine on a local database with a few hundred rows.

The cracks show up once your table grows. At 100k rows, asking for page 10,000 of an admin panel can take half a second or more. At a million rows, it gets worse. The database isn't broken - offset pagination just was never built to skip millions of rows efficiently.

Cursor pagination fixes this, but it changes how your API behaves. Instead of jumping to "page 50," the client gets a cursor and asks for "the next 10 after this point." It needs a composite index to be fast, and it trades arbitrary page jumps for consistent performance no matter how deep you go.

I'll build both strategies in a .NET 10 Web API with EF Core, benchmark them against a 100k-row table, and pull up the SQL Server execution plans to show exactly why one degrades and the other doesn't.

🎬 Watch the full video here:

What Is Pagination, and Why Does It Matter at Scale?

Pagination returns data in manageable chunks instead of sending an entire table to the client in one response. If a table has millions of rows, sending all of them is wasteful, and the user usually only cares about the latest few anyway.

There are two common ways to slice that data: offset pagination and cursor pagination. Both return the same shape of result, but they behave very differently once the dataset grows past a few thousand rows.

🐢 Offset Pagination: The Classic Approach

How It Works

The client sends a page number and a page size. The server translates that into a number of rows to skip and a number of rows to take.

The problem is that the database can't teleport to row 100,000. It has to load and sort every row before your page, discard the ones it doesn't need, and only then return the page size you asked for. Page 2 is fast. Page 10,000 isn't, because the work scales with how deep you page.

Implementation in EF Core

The demo API seeds 100k Post rows using the Bogus library, so the numbers later in this article come from a real 100k-row table, not a handful of test records.

public class Post
{
    public Guid Id { get; set; }
    public string Title { get; set; } = string.Empty;
    public string Content { get; set; } = string.Empty;
    public DateTime CreatedAt { get; set; }
}

public class AppDbContext : DbContext
{
    public DbSet<Post> Posts => Set<Post>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Post>(entity =>
        {
            entity.HasKey(p => p.Id);

            entity.Property(p => p.Id)
                .HasDefaultValueSql("NEWSEQUENTIALID()")
                .ValueGeneratedOnAdd();

            entity.HasIndex(p => new { p.CreatedAt, p.Id });
        });
    }
}

• Id uses NEWSEQUENTIALID() so SQL Server generates GUIDs in increasing order, not at random
• A sequential GUID matters later - cursor pagination needs to sort by Id as a tiebreaker when two rows share the same CreatedAt
• If you're not on SQL Server, generate GUID v7 values on the application side instead - they're sortable the same way
• The composite index on (CreatedAt, Id) is set up now even though offset pagination barely touches it - cursor pagination is the one that actually uses it

public record PostDto(Guid Id, string Title, DateTime CreatedAt);

public record OffsetPaginationMetadata(int Page, int PageSize, int TotalCount, int TotalPages);

public record OffsetPaginationResult<T>(List<T> Items, int Count, OffsetPaginationMetadata Metadata);

app.MapGet("/posts/offset", async (AppDbContext db, int page = 1, int pageSize = 10) =>
{
    page = Math.Max(page, 1);
    pageSize = Math.Clamp(pageSize, 1, 100);

    var query = db.Posts.AsQueryable();

    var totalCount = await query.CountAsync();
    var totalPages = (int)Math.Ceiling(totalCount / (double)pageSize);

    var items = await query
        .OrderByDescending(p => p.CreatedAt)
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .Select(p => new PostDto(p.Id, p.Title, p.CreatedAt))
        .ToListAsync();

    var metadata = new OffsetPaginationMetadata(page, pageSize, totalCount, totalPages);

    return Results.Ok(new OffsetPaginationResult<PostDto>(items, items.Count, metadata));
});

• page is clamped to at least 1, pageSize is clamped between 1 and 100 so a client can't request 50,000 rows in one call
• CountAsync runs a full count over the table on every request, which is its own cost at scale
• Skip((page - 1) * pageSize) is the expensive part - the database has to walk through and discard every row before the requested page
• The response includes page, pageSize, totalCount, and totalPages so the client can render page numbers directly

✅ Pros

• Lets users jump to any page directly, including the last one
• Simple to implement and easy for clients to reason about
• Total count and total pages come for free, which is useful for rendering page number UI

❌ Cons

• Gets slower as the page number increases, because every row before it has to be scanned and discarded
• Running CountAsync on every request adds overhead on large tables
• Results can shift between requests if rows are inserted or deleted while a user is paging through

Performance Results

Same table, same page size, same query shape. The only difference is how deep into the data the request goes.

⚡ Cursor Pagination: The Scalable Approach

How It Works

Instead of a page number, the server returns a cursor - a base64-encoded token pointing to exactly where the last page ended. The client sends that cursor back on the next request, and the server uses it in a WHERE clause instead of a Skip.

The database never has to scan and discard rows it doesn't need, because it knows exactly where to start. The tradeoff is that you can only move to the next chunk of data, not jump straight to an arbitrary page.

Implementation in EF Core

The cursor itself is just the last row's CreatedAt and Id:

public record Cursor(DateTime CreatedAt, Guid LastId);

public record CursorPaginationMetadata(string? NextCursor, bool HasMore);

public record CursorPaginationResult<T>(List<T> Items, int Count, CursorPaginationMetadata Metadata);

public static class CursorHelper
{
    public static string Encode(Cursor cursor)
    {
        var json = JsonSerializer.Serialize(cursor);
        var bytes = Encoding.UTF8.GetBytes(json);
        return Convert.ToBase64String(bytes);
    }

    public static Cursor? Decode(string? cursor)
    {
        if (string.IsNullOrWhiteSpace(cursor))
        {
            return null;
        }

        var bytes = Convert.FromBase64String(cursor);
        var json = Encoding.UTF8.GetString(bytes);
        return JsonSerializer.Deserialize<Cursor>(json);
    }
}

• Encode serializes the cursor to JSON and converts it to base64, so it's a single opaque string the client can store and send back
• Decode reverses that and returns null when no cursor was provided - exactly the case on the first request
• CreatedAt alone isn't enough as a cursor, because multiple rows can share the same timestamp - Id breaks the tie

app.MapGet("/posts/cursor", async (AppDbContext db, string? cursor = null, int pageSize = 10) =>
{
    pageSize = Math.Clamp(pageSize, 1, 100);
    var decoded = CursorHelper.Decode(cursor);

    var query = db.Posts.AsQueryable();

    if (decoded is not null)
    {
        query = query.Where(p =>
            p.CreatedAt < decoded.CreatedAt ||
            (p.CreatedAt == decoded.CreatedAt && p.Id < decoded.LastId));
    }

    var items = await query
        .OrderByDescending(p => p.CreatedAt)
        .ThenByDescending(p => p.Id)
        .Take(pageSize)
        .Select(p => new PostDto(p.Id, p.Title, p.CreatedAt))
        .ToListAsync();

    var lastItem = items.LastOrDefault();
    var hasMore = items.Count == pageSize;

    var nextCursor = hasMore && lastItem is not null
        ? CursorHelper.Encode(new Cursor(lastItem.CreatedAt, lastItem.Id))
        : null;

    var metadata = new CursorPaginationMetadata(nextCursor, hasMore);

    return Results.Ok(new CursorPaginationResult<PostDto>(items, items.Count, metadata));
});

• When decoded is null, the query runs unfiltered - that only happens on the very first request
• The WHERE clause reads as "rows older than the last CreatedAt, or rows with the same CreatedAt but a smaller Id" - that's the tiebreaker doing its job
• There's no Skip anywhere in this query - the database goes straight to the right starting point
• The next cursor is only generated when there are more rows to fetch, so the client knows when to stop requesting

✅ Pros

• Performance stays consistent no matter how deep the client pages
• No full table count required on every request
• Results stay stable even if new rows are inserted while a user is scrolling

❌ Cons

• Clients can't jump to an arbitrary page, only to the next or previous chunk
• Requires a composite index on the cursor columns to actually pay off
• Slightly more code to maintain, since you now own cursor encoding and decoding

Performance Results

The first request looks the same as offset pagination because there's nothing to filter yet. Every request after that stays just as fast, regardless of how many pages deep the client has gone.

What's Actually Happening in SQL Server

Numbers from the API are convincing, but the execution plan is what actually explains them.

Offset Pagination Execution Plan

EF Core translates Skip and Take into OFFSET and FETCH:

SELECT TOP (10) *
FROM Posts
ORDER BY CreatedAt DESC
OFFSET 99990 ROWS
FETCH NEXT 10 ROWS ONLY;

Running this with execution statistics enabled against 100k rows shows a clustered index scan, followed by a sort on CreatedAt, followed by discarding the offset rows, followed by taking the final 10. The database has to touch all 100k rows before it can return anything.

Cursor Pagination Execution Plan

The cursor query looks different on purpose:

SELECT TOP (10) *
FROM Posts
WHERE CreatedAt < @cursorCreatedAt
   OR (CreatedAt = @cursorCreatedAt AND Id < @cursorId)
ORDER BY CreatedAt DESC, Id DESC;

With the composite index on (CreatedAt, Id) in place, the plan shows an index seek straight to the starting point, a key lookup to fetch the remaining columns, and a top operator. There's no scan and no sort - the database already has the rows in the right order.

That difference - scan plus sort versus seek - is the entire reason cursor pagination keeps a flat response time as the table grows.

Offset vs Cursor: Summary

When to Use Offset Pagination

• Admin panels where users need to jump directly to page 1, page 10, or the last page
• Small, stable datasets unlikely to grow into the millions
• Internal reporting tools and dashboards
• Low-traffic APIs or early prototypes where raw performance isn't yet a concern

When to Use Cursor Pagination

• Infinite-scroll feeds and timelines - the pattern behind LinkedIn, X, and Facebook
• High-traffic APIs operating over large or fast-growing datasets
• Mobile apps using a load more pattern instead of page numbers
• Public APIs that need consistent results even while the underlying data keeps changing

Key Takeaways

• Offset pagination skips and discards rows on every request, so response time grows with the page number
• Cursor pagination uses a WHERE clause to jump straight to the next batch, keeping response time flat regardless of depth
• A composite index on the cursor columns is what turns a table scan into an index seek - without it, cursor pagination loses most of its advantage
• Sequential GUIDs, either through NEWSEQUENTIALID() on SQL Server or GUID v7 elsewhere, are required for the cursor tiebreaker to sort correctly
• Use offset pagination for admin panels and small, stable datasets where arbitrary page jumps matter
• Pull up the execution plan before assuming an index will fix a slow pagination query - scan versus seek tells you immediately whether it will