Offset Pagination in Mobile Development: What It Is and How to Implement

What Is Offset Pagination?

Offset Pagination is a method of paginating data where the client request contains two parameters: offset (how many records to skip) and limit (how many records to return). The server executes a SQL query with OFFSET and LIMIT, skips the specified number of rows, and returns a fixed-size result set.

The method originated in relational databases as the simplest way to organize page navigation and was transferred to HTTP APIs along with the development of REST architecture. Offset Pagination does not require state storage on the server — each request is independent and contains all the information needed for the query.

According to the Postman API design report (2025), offset pagination is used in 72% of public REST APIs, making it the dominant standard despite known performance limitations on large datasets.

Request and Response Structure

A typical REST request with Offset Pagination includes query parameters offset and limit. The response contains the record list for the requested page and metadata for building the navigation interface.

The limit parameter restricts the number of returned records and protects the server and client from excessive load. Typical limit values range from 10 to 50 records per page depending on data complexity.

data class PageRequest(
    val offset: Int,
    val limit: Int
)

data class PageResponse<T>(
    val items: List<T>,
    val total: Int,
    val hasMore: Boolean
)

fun RetrofitApi.fetchPage(request: PageRequest): Call<PageResponse<Item>>

How Offset Pagination Works

Offset Pagination translates into a SQL query with OFFSET and FETCH NEXT (or LIMIT in MySQL/SQLite) constructs. The database server scans the table, skips the number of rows equal to the offset, and returns the next limit rows. The larger the offset, the longer the query takes.

The performance problem stems from the fact that the database cannot jump directly to the offset position — it must read and discard all previous rows. At offset = 100000 and limit = 20, the DBMS reads 100,020 rows and returns only 20.

SQL Query Under the Hood

SQL — the language in which the server executes offset pagination. PostgreSQL and MySQL use LIMIT, while SQL Server and Oracle use OFFSET...FETCH. Different DBMSs optimize this query differently, but the fundamental scanning problem remains the same.

SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC
OFFSET 100000 ROWS
FETCH NEXT 20 ROWS ONLY;

Consistency Problem

Data consistency is the main drawback of Offset Pagination when working with dynamic datasets. If a new record is added to the beginning of the table between two user requests, all existing records shift. The user sees duplicates or gaps.

Consider a table of 100 records with limit = 20. On page 1, the user sees records 1-20. An administrator adds 5 new records. On page 2, the user sees records 26-45 instead of the expected 21-40 — records 21-25 are skipped, and records 21-25 from the previous set are duplicated on page 1.

Offset vs Cursor-based: Comparison of Approaches

Cursor-based pagination is an alternative to Offset Pagination that uses a pointer to the last record of the current page. Instead of a numeric offset, the client passes the identifier of the last received record, and the server returns the next N records after it.

The cursor-based approach solves the consistency problem: the cursor position does not change with inserts or deletions because the cursor refers to a specific record, not a position. However, it is more complex to implement — it requires a unique sortable field (usually ID or timestamp).

The choice between approaches depends on the interface requirements. If page number navigation and direct jumps are needed — Offset Pagination is simpler. For infinite scroll or news feeds, cursors are preferable.

Keyset Pagination

Keyset pagination is a variation of the cursor-based approach where filtering is performed on a unique key using WHERE instead of OFFSET. The SQL query uses a condition like WHERE id > lastId, allowing the database to use an index without scanning discarded rows.

According to the PostgreSQL Wiki, keyset pagination runs 100-1000 times faster than offset queries at large offsets because index scanning replaces full table scans. The disadvantage is the inability to jump to an arbitrary page without sequential traversal.

When to Use Offset Pagination

Offset Pagination is optimal for small to medium datasets (up to 10,000 records) where the user needs a page-number interface. Typical scenarios include admin panels, order lists, and filtered catalogs with page-based pagination.

For mobile applications, offset pagination is suitable when loading historical data where new inserts are rare or impossible — for example, user order history, completed task lists, or transaction archives. In these scenarios, the consistency problem does not arise.

Not recommended for social media feeds, comment lists, chats, and other dynamic datasets with frequent inserts. In these cases, gaps and duplicate records degrade the user experience and require additional deduplication logic on the client.

Hybrid Approach

Hybrid pagination combines offset and cursor: the first request uses offset to show the initial page, while subsequent requests use cursor for infinite scroll loading. This approach is used in Instagram and Twitter, where the first page is loaded via cursor, but offset is used to calculate the position when returning to a previous view.

Implementing a hybrid approach requires storing the user's virtual position on the client and coordinating two pagination mechanisms on the server. According to the Instagram Engineering blog, their team uses cursor-based pagination with an additional startCursor field that replaces offset for initial loading.

Offset Pagination in Mobile Applications

Mobile applications use Offset Pagination together with Retrofit/OkHttp on Android and URLSession/Combine on iOS. The typical pattern is loading the next page when scrolling to the end of the list via RecyclerView.OnScrollListener or UICollectionView prefetching.

Implementing offset pagination on a mobile client includes three components: a pagination manager (stores current offset and hasMore), a list adapter (displays items and loading indicator), and a repository (executes requests and handles errors). Android Jetpack offers the Paging 3 Library, which supports both offset and cursor-based pagination out of the box.

Kotlin Implementation with Paging 3

Paging 3 is an Android Jetpack library for paginated data loading. It encapsulates pagination logic, including offset tracking, loading state management, and automatic prefetching on scroll. PagingSource defines keys for the next and previous pages.

class OffsetPagingSource(
    private val api: ApiService,
    private val limit: Int = 20
) : PagingSource<Int, Item>() {

    override suspend fun load(
        params: LoadParams<Int>
    ): LoadResult<Int, Item> {
        val offset = params.key ?: 0
        return try {
            val response = api.getItems(offset, limit)
            LoadResult.Page(
                data = response.items,
                prevKey = null,
                nextKey = if (response.hasMore) offset + limit else null
            )
        } catch (e: Exception) {
            LoadResult.Error(e)
        }
    }
}

PagingSource defines prevKey and nextKey keys for page navigation. With offset pagination, prevKey is always null (cannot go to the previous page without saving history), while nextKey increases by limit with each load until the server returns hasMore = false. This is a simple and predictable model for mobile lists.

Common Mistakes with Offset Pagination

First mistake — relying on record order without sorting. Offset Pagination requires stable ORDER BY sorting on a unique field. Without it, the DBMS may return records in arbitrary order, leading to random duplicates and gaps between pages.

Second mistake — using offset to calculate the page number in the UI. The formula page = offset / limit + 1 only works if no records were deleted or added between loads. With dynamic data, the page number becomes inaccurate and the user sees incorrect information.

Third mistake — ignoring timeouts for queries with large offset. At offset over 100,000, the query can take tens of seconds, blocking the UI and consuming server resources. It is recommended to set a maximum offset value at the API level (e.g., 10,000) and use cursor-based pagination for large volumes.

Fourth mistake — not including total count in the response. Without the total number of records, the client cannot display the page count and implement numbered pagination. However, COUNT(*) on large tables is expensive — for datasets over 100,000 records, use approximate estimates or limit the maximum total value.