Introduction

When building modern Salesforce Lightning Web Components (LWCs), one essential UX feature is lazy loading (or infinite scrolling). It helps load data incrementally as users scroll — improving performance, reducing query load, and creating a smooth user experience.

Most tutorials and sample code rely on SOQL’s OFFSET keyword. This approach introduces serious scalability issues. It also causes performance problems in production environments.

This post dives deep into:

• Why OFFSET-based pagination fails at scale
• How keyset pagination (OFFSET-free) solves it
• How to add multi-field sorting and user-driven dynamic sorting with pagination reset
• The pros and cons of each approach

⚠️ The Problem with Lazy Loading using OFFSET in LWC Pagination

A common first attempt at building pagination in Salesforce looks something like this:

SELECT Id, Name, Email 
FROM Contact 
LIMIT 50 OFFSET 100

At first glance, it seems perfect — you simply skip the first 100 records and fetch the next 50 for your next page or scroll.

However, this approach quickly runs into serious performance and scalability issues when used in Lightning Web Components (LWC) or any large dataset scenario.

If we refer to our previous blog, server side lazy loading, we used the offset approach in our apex class. However, it has the limitations listed below.

Offset Limitations

In short — OFFSET pagination breaks for large datasets and hurts performance even before that.

Due to these limitations, running the code below with a page size of 800 and using a dataset of over 3,000 Contact records causes specific offset values. These offsets occur during pagination: offsets: 0, 800, 1600, 2400.

• serverSideLazyLoadingInHtmlTableWithScrollBar LWC
• LazyLoadingContactController Apex Class

Since our dataset exceeds 2,400 records, the final query executes with an OFFSET greater than 2,000, which violates Salesforce’s limit. As a result, the Apex class in our LWC throws the following error:

System.QueryException: Maximum SOQL offset allowed for apiName Contact is 2000

✅ The Recommended Solution: Keyset Pagination (OFFSET-Free)

Instead of relying on “skip and fetch,” we can use keyset pagination, also known as cursor-based pagination.

“Fetch the next N records where Id (or another key) is greater than the last record we already loaded.”

This approach uses indexed, unique fields which is Id field. This ensures consistent, fast, scalable queries. There is no OFFSET limit.

🔍 Example Apex Controller — OFFSET-Free Query

✅ For the first load, lastRecordId is null → fetches the first batch.
✅ On scroll, we pass the last record’s Id → fetches the next batch.

⚡ LWC HTML Template and JavaScript — Basic Lazy Loading Implementation

How It Works

1. Initial Load: Calls Apex with lastRecordId = null.
2. Next Scroll: Apex runs WHERE Id > :lastRecordId.
3. Repeat: Fetches chunks of records based on page size, until the dataset ends.

This ensures constant performance regardless of data size.

Note – If you don’t need UI based sorting by user, use this approach

⚙️ Sorting Support in Keyset Pagination

The Keyset Pagination (OFFSET-Free) approach we discussed earlier is beneficial. It helps you efficiently load more than 2,000 records. This method avoids hitting Salesforce’s OFFSET limitations.

But in many real-world use cases, such as our Contact list component, require sorting support. Examples include sorting by creation date, name, or email.

To handle this, keyset pagination must use stable, deterministic, and indexed fields in the ORDER BY clause. This ensures that every record has a predictable position in the result set. No records are skipped or duplicated when new pages are loaded.

✅ Supported Sort Orders

Why Composite Sorting?

When multiple records share the same sort field value (like CreatedDate), using that field alone can cause pagination errors. Some records might be skipped or repeated in subsequent pages. To solve this, we combine (CreatedDate, Id) into a composite sort key, ensuring uniqueness and deterministic ordering.

🔍 Example

Imagine the following Contact records:

If you paginate using only CreatedDate and the first page ends with A2 (10:00),
your next query will be:

WHERE CreatedDate > 2024-10-01 10:00

✅ It fetches A3,
❌ but skips any other record (like A4) that also has CreatedDate = 10:00.

That’s why we add Id as a tie-breaker:

WHERE (CreatedDate > :lastCreatedDate 
   OR (CreatedDate = :lastCreatedDate AND Id > :lastId))
ORDER BY CreatedDate, Id

Now, all records are retrieved exactly once — even if multiple share the same timestamp.

🔄 ASC / DESC Sorting Support

Keyset pagination can support both ascending and descending sorts by reversing the comparison logic dynamically:

• For ascending order:

WHERE (CreatedDate > :lastCreatedDate OR (CreatedDate = :lastCreatedDate AND Id > :lastId))
ORDER BY CreatedDate, Id

• For descending order:

WHERE (CreatedDate < :lastCreatedDate OR (CreatedDate = :lastCreatedDate AND Id < :lastId))
ORDER BY CreatedDate DESC, Id DESC

• Use simple ORDER BY Id if you only need stable pagination and don’t care about user-facing sort order.
• Use composite ORDER BY CreatedDate, Id for predictable, chronological paging. This is ideal for list views, audit logs, or any time-based sorting.
• Always align your WHERE clause with your ORDER BY fields and directions to prevent data gaps or duplicates.

⚙️ Sorting by Custom or Non-Indexed Fields

Keyset pagination depends on being able to determine a unique, ordered cursor — that is, “what comes after this record.”
To do that safely, Salesforce must have:

1. A consistent sort order (ascending/descending), and
2. A unique tie-breaker (usually Id).

✅ Case 1: Non-Unique but Stable Fields (e.g., LastName,Email)

You can extend keyset pagination to custom fields if you combine them with Id as a secondary key:

⚙️ Why It Works

The Apex method dynamically builds a query like this (depending on the selected sort field):

WHERE (
    (LastName > :lastLastName)
    OR (LastName = :lastLastName AND CreatedDate > :lastCreatedDate)
    OR (LastName = :lastLastName AND CreatedDate = :lastCreatedDate AND Id > :lastId)
)
ORDER BY LastName ASC, CreatedDate ASC, Id ASC
LIMIT :limitSize

So even if multiple Contacts share the same LastName, Email, or even the same CreatedDate, the Id ensures the next page starts after the exact last record of the previous batch — with no duplicates or gaps.

🔍 Example

If you paginate using only LastName, and your first batch ends at A2 (Smith):

WHERE LastName > 'Smith'

✅ You’ll get A4,
❌ but skip A3 (Smith, same name but later CreatedDate).

By adding both CreatedDate and Id as tiebreakers:

WHERE (
    (LastName > :lastLastName)
    OR (LastName = :lastLastName AND CreatedDate > :lastCreatedDate)
    OR (LastName = :lastLastName AND CreatedDate = :lastCreatedDate AND Id > :lastId)
)
ORDER BY LastName, CreatedDate, Id

✅ Pagination becomes fully deterministic — no skips, no duplicates, even when multiple records share the same name or timestamp.

Example Apex Controller –

Example LWC –

🧩 How the Code Handles Non-Unique but Stable Fields Sorting

This Apex implementation supports keyset pagination with composite sorting. It ensures records are loaded consistently. This is important even when the chosen sort field isn’t unique, such as LastName, Email, or other stable fields.

🔹 Dynamic Query Behavior

When your LWC sets the sort field dynamically:

this.sortField = event.target.dataset.field; // e.g., 'LastName' or 'Email'

the Apex method constructs a SOQL query like:

ORDER BY LastName ASC, Id ASC

If multiple records share the same LastName, the Id acts as a deterministic tie-breaker, ensuring:

✅ No duplicate or skipped records
✅ Predictable continuation for infinite scroll
✅ Consistent sort behavior even as data changes

🔹 WHERE Clause Logic

The WHERE clause is dynamically built to match the same sort chain:

WHERE (
    LastName > :lastSortValue
    OR (LastName = :lastSortValue AND Id > :lastId)
)

This ensures the next batch always starts after the last retrieved record, maintaining seamless continuity without relying on OFFSET.

🔹 Fallback for Default Sorting

If the user hasn’t selected a specific field (sortField is blank), the Apex method defaults to:

ORDER BY CreatedDate ASC, Id ASC

⚠️ When It May Not Work Reliably

Two special cases still require caution:

1. Highly volatile fields (e.g., LastModifiedDate, calculated fields, or any value changing while scrolling).
   • Their values may shift between page loads, causing jumps or duplicates.
2. Nullable or case-sensitive text fields
   • Salesforce may internally use case-insensitive or NULLS FIRST ordering, which can affect boundary comparisons.

For example in Contact object – LastName, Email, and CreatedDate, these risks are minimal. They’re stable and well-behaved fields for composite pagination.

⚠️ Case 2: Frequently Changing or Non-Deterministic Fields

If you want to sort by fields like LastModifiedDate, remember that calculated fields can change between requests. Also, any value changing while scrolling can alter between requests. This change can break the order consistency.
In these cases:

• Use reset-based pagination (re-query from the beginning).
• Use composite keyset (Id + CreatedDate as fallback).

How This Hybrid Sorting Works

This ensures the app stays responsive while avoiding keyset edge cases.

Example LWC + Apex With Hybrid Lazy Loading (Keyset + Reset)

🧠 Rule of Thumb: Choosing Safe Sort Fields for Keyset Pagination in the Contact Object

Best Practices

• Always use CreatedDate + Id as your composite cursor — it’s stable, indexed, and chronological.
• For non-unique text fields (LastName, Email), append both CreatedDate and Id to maintain order consistency.
• For dynamic or volatile fields (LastModifiedDate), do not keyset paginate. Instead, reset and reload from the top when the sort changes.
Keep your WHERE and ORDER BY fields aligned (same sequence and direction).

Advantages of OFFSET-Free (Keyset) Pagination

• 🚀 No 2,000-record SOQL limit
• ⚡ Faster queries with indexed filters
• 🔁 Seamless infinite scrolling
• 🔒 Governor-safe for large data sets
• 🔍 Compatible with hybrid dynamic sorting

Known Limitations

🏁 Final Takeaway

Salesforce’s OFFSET pagination is fine for small prototypes. However, keyset pagination is the only scalable way to handle large data sets.

🔹 Use CreatedDate, Id for infinite scroll stability.
🔹 Use reset-based pagination for user-driven sorts.
🔹 Combine both for a hybrid model that balances performance and flexibility.

This pattern delivers the best of both worlds. It provides speed and scalability on the backend. It offers flexibility in the UI. This is done while staying safely within Salesforce’s governor limits.