What pagination strategy do you use for a mobile list backed by a huge dataset?

#The pagination question is really a "does your data change" question

Every mobile list — a feed, a chat history, a product catalog — eventually needs to load more than one screen's worth of data, and the naive approach is OFFSET/LIMIT: page 1 is rows 0-19, page 2 is rows 20-39, and so on. It's trivial to implement and it's exactly what most tutorials teach, which is also why it's the pagination trap most engineers walk into without noticing until QA reports duplicated or missing rows in a live feed. The real question a pagination strategy has to answer isn't "how do I get the next 20 rows," it's "what happens to page 3 when someone inserts or deletes a row while the user is scrolling through page 1."

#Why offset pagination breaks under real traffic

OFFSET 40 LIMIT 20 doesn't mean "give me items 40 through 59" in any stable sense — it means "skip the first 40 rows of whatever the table currently sorts to, then give me the next 20," and if a single row was deleted from the front of that ordering between your last request and this one, every row shifts up by one position: what was row 41 is now row 40, so it gets served twice — once at the tail of the previous page, once at the head of this one — while whatever used to be row 60 gets skipped entirely and never appears. On a mobile infinite-scroll list backed by live data (new posts arriving, items being deleted), this isn't a rare edge case, it's the default behavior. Offset pagination also gets slower as the offset grows, because most databases still have to scan and discard every skipped row rather than jumping straight to position 40,000.

-- Offset pagination: page 500 means scanning (and discarding) 5,000 rows,
-- and the result set shifts under you if anything above it changes.
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC
OFFSET 5000 LIMIT 10;

#Cursor (keyset) pagination: resume from a position, not a count

Cursor pagination replaces "skip N rows" with "give me the rows that come after this specific row," using the last item's own sort key as the resume point instead of a row count. Because the query filters on a value rather than counting past N rows, performance stays flat regardless of how deep the user has scrolled, and because it never says "skip 40," a deletion earlier in the list can't cause a duplicate or a skipped item — the next page is always defined relative to the last item the client actually saw.

-- Keyset pagination: resumes exactly where the client left off,
-- using (created_at, id) as a stable, tie-broken sort key.
SELECT id, title, created_at
FROM articles
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 10;

The id tiebreaker matters more than it looks like it should: created_at alone isn't unique enough (two rows can share a timestamp), so without a secondary key on the same index, rows with identical timestamps can be silently dropped or repeated across a page boundary. The cursor itself should be opaque to the client — encode {created_at, id} as a base64 string rather than exposing raw column values, so the API is free to change its underlying sort key later without breaking every client that parsed the old cursor format.

{
  "items": [{ "id": "a_9231", "title": "...", "createdAt": "2026-08-01T12:00:00Z" }],
  "nextCursor": "eyJjcmVhdGVkQXQiOiIyMDI2LTA4LTAxVDEyOjAwOjAwWiIsImlkIjoiYV85MjMxIn0="
}

#Wiring it into the client

On Android, this maps directly onto Paging 3's PagingSource, keyed by the opaque cursor string rather than an integer page index — the load function asks for "the page after this cursor" and hands back the next one, and Paging 3 handles prefetch distance, request de-duplication, and Compose/RecyclerView diffing on top of it.

class ArticlesPagingSource(
    private val api: ArticlesApi,
) : PagingSource<String, Article>() {

    override suspend fun load(params: LoadParams<String>): LoadResult<String, Article> {
        return try {
            val page = api.getArticles(cursor = params.key, limit = params.loadSize)
            LoadResult.Page(
                data = page.items,
                prevKey = null, // this feed only ever loads forward
                nextKey = page.nextCursor,
            )
        } catch (e: IOException) {
            LoadResult.Error(e)
        } catch (e: HttpException) {
            LoadResult.Error(e)
        }
    }

    override fun getRefreshKey(state: PagingState<String, Article>): String? = null
}

For an offline-first list, pair this with a RemoteMediator that writes each fetched page into a local Room table and lets the PagingSource actually read from that table — the network call becomes a background refill rather than the thing the UI directly renders, which is what lets the list stay scrollable and correct even while a page request is in flight or failing.

#What you give up, and when that's the right trade

Cursor pagination isn't free: you lose the ability to jump straight to "page 12" or show a reliable total count, because the whole point is that there is no stable global ordering by row number to jump into — you can only go forward (or backward, if you also capture a prevCursor) from a known position. That's an acceptable, even desirable, trade for a chat history, a social feed, or a search-results list, all of which are consumed sequentially and where "how many results total" is rarely something users act on. It's the wrong trade for something like a data table with page-number navigation and a "jump to page 40" control, where offset pagination (often on a read replica, or a periodically-refreshed materialized view to blunt the performance concern) is still the more honest fit for the UI you're actually building.

🧠 Offset vs. cursor pagination
0/2 · 0/2 answered
1. Why does offset pagination get slower as the offset grows?
The client has to download every previous page firstMost databases still scan and discard every skipped row before returning the requested pageIt doesn't — performance is constantOFFSET triggers a full table lock
2. What does a keyset/cursor pagination query filter on instead of an offset?
The sort key value of the last item the client already sawThe total row countThe client's IP addressA random sample of rows