EF Core ↔ SQL Server: OFFSET/FETCH vs Keyset Pagination for Large Tables

Recommendation

For a high‑volume, frequently updated table on PostgreSQL with EF Core 7, favor keyset (seek) pagination. It delivers roughly constant‑time page reads and stable results under concurrent writes, whereas OFFSET/FETCH cost grows linearly with page depth and can skip or duplicate rows when data shifts.

Why keyset wins for this scenario

• Performance: A keyset query translates to an indexed seek (WHERE key > @lastKey ORDER BY key LIMIT @pageSize). The database touches only the rows for the requested page.
• Stability: Because the predicate is anchored to the last‑seen key value, inserts or updates between requests do not cause missing or duplicate rows.
• No raw SQL required: The pattern query.Where(x => x.Id > lastId).OrderBy(x => x.Id).Take(pageSize) is pure LINQ and EF Core 7 translates it to the optimal seek plan.

When OFFSET/FETCH may still be acceptable

• You need random page‑number navigation (jump to page N) or easy backward paging.
• Pages are shallow (e.g., first 10‑20 pages) and a covering index on the ORDER BY columns exists.
• The table is relatively static, so page drift is not a concern.

LINQ patterns for keyset pagination in EF Core 7

Single‑column unique key (e.g., primary key Id)

var page = await ctx.Entities
    .Where(e => e.Id > lastSeenId)
    .OrderBy(e => e.Id)
    .Take(pageSize)
    .ToListAsync();

Generates: WHERE "Id" > @lastSeenId ORDER BY "Id" LIMIT @pageSize.

Multi‑column ordering (e.g., CreatedAt then Id)

EF Core 7 cannot translate ValueTuple comparisons directly, so expand the predicate:

var page = await ctx.Entities
    .Where(e => e.CreatedAt > lastCreatedAt
             || (e.CreatedAt == lastCreatedAt && e.Id > lastSeenId))
    .OrderBy(e => e.CreatedAt)
    .ThenBy(e => e.Id)
    .Take(pageSize)
    .ToListAsync();

Ensure the ORDER BY columns match the predicate order.

Index requirement

Create a composite index that matches the ordering columns exactly:

CREATE INDEX IX_Entities_CreatedAt_Id ON "Entities" ("CreatedAt", "Id");

Without this index the keyset query degrades to a sort/scan and loses its advantage.

Verification steps (run once in your environment)

1. Enable EF Core logging or call query.ToQueryString() to confirm the generated SQL contains a WHERE predicate and no OFFSET clause.
2. Run EXPLAIN ANALYZE on both the keyset and OFFSET/FETCH queries at a deep page (e.g., offset 500 000). Compare Index Scan vs. Rows Removed by Filter.
3. Simulate concurrent inserts between two page fetches and verify that keyset returns no duplicates or gaps while OFFSET/FETCH may.

One missing diagnostic detail

Do you require true random‑access page numbers (e.g., a UI with page‑number links) or only forward/backward “next/previous” navigation? If random access is mandatory, OFFSET/FETCH with a covering index may be the simpler choice despite its deeper‑page cost.