Paging Large Salesforce Data in Apex: Why OFFSET Fails and Keyset Pagination Wins

If you've written an Apex job that pages through tens of thousands of records with OFFSET, you've probably watched it get slower with every page — and eventually hit a governor limit or a timeout. The fix is a well-established pattern called keyset (cursor-based) pagination, and it usually cuts both query time and CPU usage dramatically. This post explains why OFFSET degrades, shows a working keyset implementation, and covers the one requirement that makes or breaks the pattern.

Why OFFSET Gets Expensive

SOQL's OFFSET clause tells the database to skip N rows and return the next batch. That sounds cheap, but the engine still has to locate and count through all N skipped rows on every page. Page 1 skips 0 rows; page 50 skips 50,000. The work grows linearly with each request, so total cost across a full scan grows roughly quadratically.

Salesforce also caps OFFSET at 2,000 in standard SOQL queries, which makes it unusable for deep pagination anyway. Even below that cap, each successive page costs more than the last. In a Batch Apex execute method or a Queueable job with tight CPU limits, that compounding cost is often what pushes a job over the edge.

The Keyset Alternative

Keyset pagination skips the counting entirely. Instead of saying "give me rows 10,001 through 10,200," you say "give me the next 200 rows after the last one I saw." You do that with a WHERE clause on a unique, monotonically increasing field — typically Id or CreatedDate:

public class KeysetPaginator {
    public static List<Account> nextPage(Id lastSeenId, Integer pageSize) {
        if (lastSeenId == null) {
            return [SELECT Id, Name FROM Account
                    ORDER BY Id ASC LIMIT :pageSize];
        }
        return [SELECT Id, Name FROM Account
                WHERE Id > :lastSeenId
                ORDER BY Id ASC LIMIT :pageSize];
    }
}

Run this in a Queueable job, a Batch class, or anonymous Apex in the Developer Console (no special permissions beyond read access to the object). After each page, save the last Id returned and pass it into the next call. Because the filter uses an indexed, unique column, every page costs roughly the same as the first — the database seeks directly to the cursor position instead of scanning from the top.

Two details matter here. First, the ORDER BY must match your filter field, or rows can arrive out of sequence and your cursor becomes meaningless. Second, Id works well as a cursor because it's unique, indexed, and sortable — but only within a single object and org; don't assume cross-object ordering.

A Worked Example: Chained Queueable Export

Here's the pattern applied to a real task: processing every Account in an org without a Batch class, by chaining Queueable jobs that each handle one page:

public class AccountExportJob implements Queueable {
    private Id cursor;

    public AccountExportJob(Id cursor) {
        this.cursor = cursor;
    }

    public void execute(QueueableContext ctx) {
        List<Account> page = KeysetPaginator.nextPage(cursor, 200);
        if (page.isEmpty()) {
            return; // done
        }
        // ... do the actual work on 'page' ...
        Id next = page[page.size() - 1].Id;
        if (page.size() == 200) {
            System.enqueueJob(new AccountExportJob(next));
        }
    }
}

// Kick it off:
System.enqueueJob(new AccountExportJob(null));

Each job processes exactly 200 records, so per-transaction governor limits (SOQL queries, DML rows, CPU time) stay comfortably flat no matter how large the table is. Compare that to an OFFSET loop inside one transaction, where later pages consume progressively more CPU until the 10-second synchronous (or 60-second asynchronous) limit kills the transaction.

To verify the improvement yourself, run a benchmark in a sandbox: query 10,000+ records with both patterns via anonymous Apex and compare Limits.getCpuTime() and Limits.getQueries() after each. The keyset version should show near-constant per-page cost; OFFSET should not. Don't treat any specific speedup number as guaranteed — data distribution and sharing rules affect results — but the trend is consistent.

The Catch: Your Cursor Field Must Be Trustworthy

Keyset pagination only works if the cursor field is unique, non-null, and monotonically ordered. Id satisfies this. CreatedDate mostly does, but two records created in the same millisecond can tie — if you sort by CreatedDate alone and a page boundary lands on a tie, you can skip or duplicate rows. The safe fix is a compound cursor: WHERE (CreatedDate > :lastDate) OR (CreatedDate = :lastDate AND Id > :lastId), ordered by both fields.

Custom sequence fields are riskier. If a "sequence number" field has gaps, nulls, or duplicates from historical data loads, the cursor silently skips records. Before adopting any custom field, confirm it's indexed and run SELECT Count() FROM MyObject__c WHERE Seq__c = null plus a duplicate check in a sandbox that mirrors production's data volume and API version.

One more limitation: keyset pagination is forward-only. If you're building a UI where users jump to "page 37," OFFSET (within its 2,000-row cap) or a precomputed cache is a better fit. Keyset shines for sequential processing — exports, integrations, data maintenance — not random access.

What to Do Next

If you have any Apex that pages with OFFSET near or beyond a few thousand rows, switch it to an Id-based cursor using the pattern above. Test it in a full-copy sandbox against realistic data volume, watch Limits.getCpuTime() per page, and confirm your cursor field has no nulls or duplicates. It's a small refactor that removes an entire class of "job got slower until it died" failures.