Stop Using OFFSET Paging with Realm: Leveraging Lazy Live Collections

The Pagination Performance Trap

When building lists for mobile apps, the instinct is to implement pagination using LIMIT and OFFSET. This approach mimics how we fetch data from a server: request 20 items, then request the next 20 once the user scrolls to the bottom. However, applying this pattern to a local object database like Realm creates unnecessary overhead and ignores the database's core architecture.

The takeaway is simple: Do not manually page your Realm queries. Because Realm results are lazy-loaded and auto-updating, you can query your entire dataset once and let the SDK handle the memory management and UI synchronization.

How Lazy Loading Replaces Paging

In a traditional SQL database, a query typically returns a snapshot of data loaded into memory. In Realm, a RealmResults collection is not a list of objects, but a view into the underlying data. When you execute a query that matches 100,000 records, Realm does not instantiate 100,000 objects; it simply calculates the indices of the matching records.

Objects are only materialized (converted into usable language objects) when you access them by index. If your UI only displays 10 items on screen, only those 10 objects are held in memory, regardless of whether the total result set contains ten or ten million records. This eliminates the need for OFFSET logic, as the cost of accessing the 1,000th element is nearly the same as accessing the 1st.

Live Collections and UI Synchronization

Beyond memory efficiency, Realm collections are "live." If a background thread updates a record or inserts a new one that matches your query criteria, the RealmResults object updates automatically.

Instead of re-running a query to refresh a list, you can attach a change listener. These listeners provide fine-grained notifications—specifically which indices were inserted, deleted, or modified. This maps directly to modern UI frameworks like Android's RecyclerView or iOS's UITableView, allowing you to animate specific row changes rather than reloading the entire list.

Implementation Example: The Windowing Pattern

Rather than requesting pages, use a "windowing" approach. Maintain a single query and expose a growing slice of that query to your UI adapter.

// Example using Realm Kotlin SDK
// 1. Define the full query (this is nearly instantaneous and lazy)
val allTasks = realm.query<Task>("status == 'pending'")
    .sort("dueDate", Sort.ASCENDING)
    .find()

// 2. Maintain a variable for the current visible window
var visibleCount = 20

// 3. In your UI adapter, access only the window
fun getItem(index: Int): Task {
    return allTasks[index] // Only this specific object is materialized
}

// 4. When the user scrolls to the bottom, simply increase the window
fun loadMore() {
    visibleCount += 20
    notifyDataSetChanged() // Or update your UI state
}

Trade-offs and Threading Constraints

While lazy loading is powerful, it introduces two critical constraints: threading and file size.

• Thread Confinement: Live objects and results cannot be passed between threads. If you query data on a background thread and try to pass that RealmResults object to the main UI thread, the application will crash. To move data across threads, you must use Frozen Objects (snapshots of the data that are thread-safe but no longer auto-update).
• MVCC and File Growth: Realm uses Multi-Version Concurrency Control (MVCC). If you have a long-running read transaction (like a live result set being held open) while performing heavy writes on another thread, Realm must keep old versions of the data available. This can cause the database file to grow rapidly.

Verification and Diagnostics

To verify that your implementation is truly lazy and not loading the entire set into memory, you can perform a simple memory profile check:

1. Insert 50,000 dummy objects into a Realm file.
2. Run a sorted query on the entire set.
3. Monitor the heap memory. You should see a negligible increase upon executing the query, and a small, incremental increase only as you iterate through the results.
4. To manage file growth, configure compactOnLaunch in your RealmConfiguration to reclaim space from old MVCC versions during app startup.

Closing Action

Review your current data layer. If you are using LIMIT or OFFSET logic to manage local Realm data, remove it. Replace it with a single, sorted query and a windowed access pattern. This will reduce boilerplate code and improve the responsiveness of your lists by leveraging Realm's native lazy-loading architecture.