Understanding Cursor Pagination and Why It's So Fast: A Deep Dive

Finding your place in a thick book

Imagine you are reading a thick novel, maybe 800 pages long. You stop reading for the night. How do you find your place tomorrow?

One way is to count. You remember "I was on page 312." Tomorrow you flip from page 1, counting, until you reach 312. If you stopped at page 790, that is a lot of flipping every single night.

A smarter way is to use a bookmark. You slide a thin paper between the pages where you stopped. Tomorrow you just open the book straight at the bookmark. It does not matter if you are near the start or near the end. Opening to a bookmark takes the same small effort every time.

This bookmark is exactly what cursor pagination does. The cursor is the bookmark. It remembers the exact spot where you stopped, so the database opens straight to the next rows instead of counting from the beginning. In this deep dive we will see how that bookmark works, why it stays fast, and how to build it in EF Core step by step.

A quick word on offset pagination first

To understand why cursor pagination is special, we need to see the slow way it replaces.

Offset pagination uses two ideas: skip and take. You skip some rows, then take a few. In EF Core this is Skip() and Take(). In raw SQL it is OFFSET and LIMIT (or FETCH).

// Offset pagination: page 3, with 20 rows per page.
var page = 3;
var pageSize = 20;
 
var products = await db.Products
    .OrderBy(p => p.Id)
    .Skip((page - 1) * pageSize)   // skip 40 rows
    .Take(pageSize)                // take the next 20
    .ToListAsync();

This looks neat. The problem hides in that little word skip. The database cannot magically jump to row 41. It must read rows 1 through 40 first, then throw them away, just to reach the rows you want. On page 3 that is cheap. On page 5,000 it must read and discard 100,000 rows. The deeper you go, the slower it gets.

What cursor pagination actually does

Cursor pagination throws away the idea of "page numbers" completely. Instead, it asks a different question:

"Give me the next 20 rows that come after the last row I already saw."

The "last row I already saw" is the cursor. It is the bookmark. To use it, you need two things:

1. A stable sort order (for example, sort by Id, smallest first).
2. The sort value of the last row on the current page.

Say your first page ended with a product whose Id is 120. For the next page, you do not skip 120 rows. You simply say: give me products where Id > 120, sorted by Id, and take 20. The database uses the index on Id to seek straight to just after 120 and read forward.

// Cursor pagination: rows after the last Id we saw.
int? afterId = 120;   // the cursor; null means first page
var pageSize = 20;
 
var query = db.Products.OrderBy(p => p.Id);
 
if (afterId is not null)
{
    query = (IOrderedQueryable<Product>)query
        .Where(p => p.Id > afterId);   // seek, do not skip
}
 
var products = await query
    .Take(pageSize)
    .ToListAsync();
 
// The cursor for the next page is the last row's Id.
int? nextCursor = products.Count == pageSize
    ? products[^1].Id
    : null;   // null means we reached the end

Notice there is no Skip() anywhere. We replaced "skip N rows" with "find rows greater than a value". That single change is the whole secret.

Why the index makes it fast

The magic word here is index. A database index is like the alphabetical thumb tabs on a phone directory or a dictionary. To find words starting with "M", you do not read from "A". You jump to the "M" tab.

When you have an index on Id and you ask for Id > 120, the database walks down the index tree (a B-tree) directly to the entry just after 120. That lookup costs only a few steps, no matter how big the table is. Then it reads forward 20 entries. Done.

Offset cannot use this trick. OFFSET 100000 has no value to seek to. "Skip 100,000 rows" is not a position in the index, it is a count. So the engine has no choice but to walk past 100,000 entries one by one.

Here is the cost difference written plainly.

With offset, the work grows with the page number. With cursor, the work stays flat. This is why people describe offset as roughly O(n) (cost grows with depth) and cursor as roughly O(1) (cost stays constant). Real benchmarks on millions of rows often show cursor pagination running many times faster on deep pages.

The tie-breaker problem (very important)

There is a catch you must handle, and most beginners miss it.

What if you sort by something that is not unique? Imagine sorting products by CreatedAt (the time they were added). Many products can be created in the same second, so several rows share the same CreatedAt. If your cursor is only the CreatedAt value, the database cannot tell which of those same-time rows you already saw. You might skip a row or show one twice.

The fix is simple: always add a unique tie-breaker to the sort. The primary key (Id) is perfect for this. So you sort by CreatedAt, and then by Id. Now every row has a unique pair of values (CreatedAt, Id). No two rows are ever equal, so the bookmark is always exact.

The WHERE clause for "after this row" then becomes a little longer. In words: give me rows where the CreatedAt is later, OR the CreatedAt is the same but the Id is larger.

// Cursor over a non-unique sort column, with Id as the tie-breaker.
DateTime? lastCreatedAt = cursor?.CreatedAt;
int? lastId = cursor?.Id;
var pageSize = 20;
 
var query = db.Products
    .OrderBy(p => p.CreatedAt)
    .ThenBy(p => p.Id);   // tie-breaker keeps the order stable
 
if (cursor is not null)
{
    query = query.Where(p =>
        p.CreatedAt > lastCreatedAt ||
        (p.CreatedAt == lastCreatedAt && p.Id > lastId));
}
 
var products = await query.Take(pageSize).ToListAsync();

That OR condition is the standard keyset comparison. To keep it fast, your index should cover both columns in the same order: an index on (CreatedAt, Id). Then the database can still seek directly. If the index does not match your ORDER BY, you lose the speed and the database falls back to scanning and sorting.

Encoding the cursor for clients

So far our cursor was a plain number or a pair of values. When you send a cursor to a web client (a browser or a mobile app), it is good manners to make it opaque. That means the client should treat it as a meaningless string and just send it back to you. It should not try to read or build it.

Why hide it? Two reasons. First, it stops clients from depending on your internal columns, so you can change them later. Second, it keeps your API clean: one short token instead of several query parameters.

A common trick is to put the cursor values into a small string and then Base64-encode it.

// Turn the last row's sort values into one opaque token.
public static string EncodeCursor(DateTime createdAt, int id)
{
    var raw = $"{createdAt:O}|{id}";        // e.g. 2026-06-10T10:00:00|140
    var bytes = Encoding.UTF8.GetBytes(raw);
    return Convert.ToBase64String(bytes);    // opaque to the client
}
 
public static (DateTime createdAt, int id) DecodeCursor(string token)
{
    var raw = Encoding.UTF8.GetString(Convert.FromBase64String(token));
    var parts = raw.Split('|');
    return (DateTime.Parse(parts[0], null, DateTimeStyles.RoundtripKind),
            int.Parse(parts[1]));
}

Your API then returns the page of data plus a nextCursor token. The client sends that token back to fetch the next page. A typical response looks like this in shape: a list of items, plus a small string the client keeps for "load more". The endpoint might be GET /products?cursor={token}&limit=20, where the client never touches the inside of {token}.

A real comparison side by side

Let me put the two styles next to each other so the difference is clear.

The takeaway is not "cursor is always better." It is "cursor is better for big, fast-moving, infinite-scroll style data, and offset is better when users click page numbers." Social feeds, activity logs, chat history, and notification lists are perfect for cursors. An admin grid where someone jumps to "page 12" is better with offset.

The stability bonus

There is one more gift cursor pagination gives you, and it has nothing to do with speed.

Imagine an offset list of newest items first. You are on page 1. While you read, five new items get added at the top. You click "next page." Because offset counts positions, those five new items push everything down. You now see five items you already saw on page 1. They got duplicated, and five real items got skipped.

Cursor pagination does not have this bug. Your bookmark points at a specific row, not at a position. New rows added elsewhere do not move your bookmark. You always continue from exactly where you stopped. This is why every big "infinite scroll" feed you have ever used relies on cursors under the hood.

Common mistakes to avoid

A few traps catch people when they first build cursor pagination. Keep these in mind.

• No tie-breaker. Sorting by a non-unique column without adding the primary key will skip or repeat rows. Always end your sort with a unique column.
• Index does not match the sort. If your ORDER BY is (CreatedAt, Id) but your index is only on CreatedAt, the database may sort in memory and lose the speed. Match the index to the sort exactly.
• Changing the sort mid-scroll. The cursor only makes sense for one fixed sort order. If the user changes sorting, throw the old cursor away and start fresh.
• Forgetting the direction. Sorting descending (newest first) flips the comparison: you want rows "less than" the cursor, not "greater than". Be careful which way the > points.
• Leaking internal data. If you send a raw Id as the cursor, clients may start depending on it. Encode it so it stays opaque.

Putting it all together

Here is the mental model in one picture. Offset is the reader who counts pages from the start every night. Cursor is the reader with a bookmark who opens straight to the right page. Both reach the same page, but only one stays fast as the book gets thicker.

When you build a list endpoint in .NET, ask yourself a simple question: do users click page numbers, or do they scroll down for more? If they scroll, reach for the cursor. Your deep pages will stay fast, your feed will stay stable, and your database will thank you for not making it count to a hundred thousand every time.

Quick recap

• Offset pagination uses Skip/Take (OFFSET/LIMIT). It must read and throw away every row before your page, so deep pages get slow.
• Cursor pagination (also called keyset or seek pagination) remembers the last row you saw and asks for rows after it using a WHERE clause.
• Because it uses an index seek instead of skipping, the cost stays roughly constant whether you are on page 1 or page 10,000.
• Always add a unique tie-breaker (like the primary key) to your sort so the cursor points at exactly one row.
• Match your database index to your ORDER BY so the seek stays fast.
• Send the cursor as an opaque token (often Base64) so clients treat it as a black box.
• Cursor pagination also fixes the duplicate/skip bug when new rows arrive while a user scrolls.
• Use cursor pagination for infinite scroll, feeds, and logs. Use offset when users need clickable page numbers or a total page count.

References and further reading

• Pagination — EF Core (Microsoft Learn)
• Efficient Querying — EF Core (Microsoft Learn)
• Keyset Cursors, Not Offsets, for Postgres Pagination (Sequin)
• PostgreSQL Keyset Pagination vs Offset (Stacksync)
• Comparing Limit-Offset and Cursor Pagination (DEV Community)