/DB

Keyset pagination

Page through large tables with a stable cursor instead of OFFSET, using the generated query builder for ORDER BY and LIMIT.

updated 5 Jun 20261 min readv0.3.2View as Markdown

OFFSET pagination re-scans every skipped row and can drift when data changes between pages. Keyset (cursor) pagination filters on the last value you saw, so each page is a fast index range scan with a stable boundary.

The context sets cover predicate reads; ordering and LIMIT come from the generated query builder, so reach for the builder here. Borrow a connection from the context with WithConnectionAsync:

using System.Linq;   // ToListAsync() over the async stream (System.Linq.Async)

// First page: the 20 newest tasks in a project.
List<TaskItem> page = await db.ExecuteAsync(d => d.WithConnectionAsync(conn =>
    TaskItem.Query(x => x.ProjectId == projectId)
        .OrderByDesc(x => new object?[] { x.CreatedAt })
        .Limit(20)
        .WithConnection(conn)
        .ExecuteAsync()
        .ToListAsync()));

For the next page, use the last row's sort value as the cursor and ask for rows beyond it:

DateTime cursor = page[^1].CreatedAt;

List<TaskItem> next = await db.ExecuteAsync(d => d.WithConnectionAsync(conn =>
    TaskItem.Query(x => x.ProjectId == projectId && x.CreatedAt < cursor)
        .OrderByDesc(x => new object?[] { x.CreatedAt })
        .Limit(20)
        .WithConnection(conn)
        .ExecuteAsync()
        .ToListAsync()));
WARNING
A single column is only a stable cursor if its values are unique. When timestamps can collide, add a tiebreaker (the primary key) to both the ORDER BY and the predicate so no row is skipped or repeated.
.Query(x => x.ProjectId == projectId
    && (x.CreatedAt < cursorCreatedAt
        || (x.CreatedAt == cursorCreatedAt && x.Id < cursorId)))
.OrderByDesc(x => new object?[] { x.CreatedAt, x.Id })

Index the sort columns (CREATE INDEX ON tasks (project_id, created_at DESC, id DESC)) so each page stays an index range scan. See Sorting & pagination for the builder methods and the OFFSET form.