Realm Live Results Offset Pagination Versus Snapshot SQL Pagination

Direct answers

Does offset slicing preserve stable page boundaries?

No. Realm Results are live, auto-updating collections. An offset is an index into that live collection, not a position anchored to a specific object. If another write transaction inserts or deletes objects before the offset, the index positions shift. The next page can skip items that moved into the previous page or duplicate items that moved out of it.

Should an application-managed monotonic ordering key replace raw offsets?

Yes, for deep pagination. Query from a known anchor instead of slicing by index: order by an immutable, unique key such as a primary key, ULID, or strictly increasing timestamp, then filter key > lastSeenKey and limit to the page size. This removes dependence on index positions that change under concurrent writes.

Do SDK slicing APIs return live views or snapshots?

It depends on the SDK and version. Swift Results.prefix(_) and range subscripting return live views. Kotlin and Java RealmResults.subList return a live list view backed by the same Results. .NET IQueryable.Skip and Take usually materialize when you enumerate, for example with ToList or ToArray, but re-enumerating the query re-evaluates it unless you materialize immediately. None of these provide a built-in stable cursor token for local queries.

Confirmed behavior versus likely explanation

Item	Status	Notes
Results are live and auto-updating	Confirmed	Applies to local Realm across major SDKs.
Offset is index-based	Confirmed	Writes before the offset shift subsequent indices.
No universal local cursor token	Confirmed	Some sync APIs expose server tokens, but not for purely local pagination.
Duplicate or skipped pages under concurrent writes	Likely explanation	Occurs when the app treats a live index as a static SQL OFFSET snapshot.

Steps for this case

1. Replace offset-based slicing with an anchor query. Example pattern:

// Conceptual; adapt to your SDK
lastKey = lastItemOnPreviousPage.key
page = results
  .where('key > $0', lastKey)
  .sorted('key')
  .limit(pageSize)

1. Make the sort key immutable and unique. If timestamps can collide, append a tiebreaker such as the object primary key.
2. Materialize only the current page. Do not freeze or copy the full Results set unless you genuinely need a point-in-time snapshot and can afford the memory.
3. If you must expose numbered pages such as "page 3," freeze or ToList the Results first, then slice by offset. Treat that slice as static; it will not reflect new writes.

Missing diagnostic detail

Do the pages need to reflect inserts and deletes while the user scrolls, or is a point-in-time snapshot acceptable? If a snapshot is acceptable, freezing and offset slicing is simpler. If pages must stay live, anchor-based keys are required.

Assumptions and uncertainty

• This assumes a local Realm, not a synchronized Realm with server-side continuation tokens.
• SDK behavior varies by major version. Verify whether freeze, snapshot, subList, or ToList returns a copy or a live view in your exact SDK version.
• If an object’s sort key is mutated after it has been paged, anchor keys can still reorder items across page boundaries. Prefer immutable sort keys.
• Freezing very large Results sets can cause memory pressure on mobile devices.

Verification

Run a controlled test before changing production pagination:

1. Create a local Realm with at least 10,000 objects sorted by an integer key.
2. Read page 2 with offset 100 and limit 100.
3. In a separate write transaction, insert 100 objects with keys less than the first key on page 2.
4. Read page 2 again and compare the item set. Expect skipped or duplicated items with offset slicing.
5. Repeat using where('key > lastKey') and confirm the page boundaries remain stable.

// Swift example (adapt to your schema)
let page = results.sorted(byKeyPath: "key")
  .offset(100)
  .limit(100)
// After concurrent insert before offset, re-fetching page may differ.

// Kotlin/Java example (adapt to your schema)
RealmResults page = realm.where(Item.class)
  .greaterThan("key", lastKey)
  .findAll()
  .sort("key")
  .subList(0, pageSize);

// .NET example (adapt to your schema)
var page = realm.All()
  .Where(i => i.Key > lastKey)
  .OrderBy(i => i.Key)
  .Take(pageSize)
  .ToList();