Paging 3 in Android: avoiding duplicate pages when the backend has no stable cursor

Paging 3 handles most of the tedious work of paginating a long list on Android out of the box: it tracks loading state, exposes LoadState for spinners and errors, and plugs into RecyclerView through PagingDataAdapter without anyone hand-rolling the "when do I fetch the next page" logic. What Paging 3 does not solve for you is what "next page" even means when the backend defines it with a numeric offset and the underlying table keeps receiving inserts while the user is still scrolling. That mismatch gave us intermittent duplicates in production for weeks before we understood the problem wasn't in the client at all.

How RemoteMediator talks to the backend

In a list backed by network plus a local cache, RemoteMediator is the component that decides when to ask the backend for more data and how to write it into the local database that PagingSource then exposes to the UI. A typical implementation looks like this:

class PedidosRemoteMediator(
    private val api: PedidosApi,
    private val db: AppDatabase,
) : RemoteMediator<Int, PedidoEntity>() {

    override suspend fun load(
        loadType: LoadType,
        state: PagingState<Int, PedidoEntity>,
    ): MediatorResult {
        return try {
            val response = when (loadType) {
                LoadType.REFRESH -> api.getPedidos(offset = 0, limit = state.config.pageSize)
                LoadType.PREPEND -> return MediatorResult.Success(endOfPaginationReached = true)
                LoadType.APPEND -> {
                    val offset = state.pages.sumOf { it.data.size }
                    api.getPedidos(offset = offset, limit = state.config.pageSize)
                }
            }

            db.withTransaction {
                if (loadType == LoadType.REFRESH) db.pedidoDao().clearAll()
                db.pedidoDao().insertAll(response.items)
            }

            MediatorResult.Success(endOfPaginationReached = response.items.isEmpty())
        } catch (e: IOException) {
            MediatorResult.Error(e)
        } catch (e: HttpException) {
            MediatorResult.Error(e)
        }
    }
}

The detail that matters is in the APPEND branch: the offset sent to the backend is computed by adding up how many items were already loaded across previous pages. It's the most direct way to implement offset pagination, and it works without surprises as long as the source table is effectively read-only from the perspective of whoever is scrolling. The problem shows up the moment that table receives new inserts during the same window of time someone is paging through it.

Why the offset drifts under concurrent inserts

A numeric offset answers the question "give me the items between position 40 and 60 under the current ordering." If, between the first and second page, five new orders get inserted that -under the backend's ordering, say descending creation date- land before position 40 because they're more recent than everything already loaded, offset 40 no longer points to what it pointed to when the first page was requested. The five items that used to sit at positions 35 through 39 are now at positions 40 through 44, and the second request with offset=40 fetches them again, duplicated, mixed in with genuinely new items.

This isn't a rare case that only shows up under extreme load. For a list of orders, notifications, or any entity created frequently while someone has the screen open, it's the normal case. The symptom we saw in production was intermittent and hard to reproduce on an emulator precisely because it depends on a real insert landing in the time window between two page requests, something that never happens with static test data.

The fix: a cursor based on an ordered id

The fix isn't in the client, it's in changing what we ask the backend for. Instead of a numeric offset that depends on a snapshot of the total row count at request time, we ask for "the next batch after this id," using a column with stable ordering -typically the auto-incrementing primary key, or a timestamp with enough resolution to avoid ties- as the cursor:

LoadType.APPEND -> {
    val lastItem = state.lastItemOrNull()
        ?: return MediatorResult.Success(endOfPaginationReached = true)
    api.getPedidos(cursorId = lastItem.id, limit = state.config.pageSize)
}

On the backend side, the corresponding query replaces OFFSET with a condition on the cursor:

SELECT * FROM pedidos
WHERE id < :cursor_id
ORDER BY id DESC
LIMIT :limit;

The difference is that this query doesn't depend on how many rows exist before an arbitrary point, it depends on a concrete value we've already seen. A new insert with an id greater than the cursor simply doesn't show up in the result, no matter how many rows were inserted between one request and the next. The client never sees an item it already loaded a second time, because the cutoff condition doesn't move when the table's size changes.

One condition has to hold for this to work: the column used as the cursor needs a total, time-stable order -an auto-incrementing id satisfies this naturally- and the query's ORDER BY has to match that column exactly. If the list needs to sort by something else, like an updated-at timestamp, the cursor has to be composite (timestamp, id) to break ties when two rows share the same timestamp, because a cursor over a column with repeated values reintroduces the same problem offset pagination had: two items with the same cursor value can land inconsistently split across two pages.

What didn't work: deduplicating on the client

Before touching the backend, we tried to fix this client-side: keep a Set<Long> of already-seen ids in the ViewModel and filter out any repeated item before it reached the PagingDataAdapter. The idea seemed reasonable -if the problem is duplicates arriving, just don't show them twice- but it broke more than it fixed.

The first problem was scroll position: Paging 3 calculates scroll position and prefetching of subsequent pages assuming the page size reported by RemoteMediator matches the actual number of new items. If we filtered duplicates after RoomDatabase had already inserted them, PagingSource kept reporting the full count -duplicates included- while the adapter showed fewer items than Paging 3 believed existed, and that threw off prefetching: sometimes it requested the next page too late, leaving the user staring at a loading spinner longer than expected.

The second problem was more fundamental: filtering on the client treats the symptom without touching the cause, so any other screen hitting the same endpoint -a paginated search, an export- would have to reimplement the same deduplication Set on its own, with the risk that someone would forget to. When we finally switched the backend to an id-based cursor, we removed client-side deduplication entirely: it was no longer needed, and keeping it around would have hidden any real problem that resurfaced later instead of leaving it visible.

When numeric offset is still reasonable

Not every pagination case needs a cursor. If the source table is effectively read-only during the pagination session -a report generated once and browsed afterward, a historical export that receives no new writes- numeric offset is simpler to implement and to debug, and carries no real downside, because the condition that breaks cursor-free pagination -concurrent inserts shifting positions- simply doesn't occur. The question we now ask before picking one approach or the other is direct: can a new row be inserted into the range already paged through while the user is still paging? If the answer is yes, numeric offset isn't an option, it's a bug waiting for someone to scroll at the wrong moment.