Pagination That Doesn't Lie to the User

TL;DR — LIMIT/OFFSET is correct only over an immutable list. On a feed, items inserted between page fetches push everything down, so the user sees duplicates; items deleted cause content to be skipped silently, which is worse because nobody reports what they never saw. Use keyset (cursor) pagination ordered by a stable, unique key — and include a tiebreak column, because a non-unique sort key reintroduces the bug at page boundaries. Then stop lying about totalCount, don't trust hasMore over the cursor, and keep cursors opaque. One test catches the whole class: insert at the head between page fetches and assert nothing duplicated or skipped.

The bug, precisely

Page 1:  LIMIT 20 OFFSET 0   → items 1–20
         [3 new items posted]
Page 2:  LIMIT 20 OFFSET 20  → offset 20 of the NEW list

before:  [1 … 20][21 … 40]
after:   [n1 n2 n3][1 … 20][21 … 37]
                        ↑ offset 20 now lands here
result:  user sees 18, 19, 20 for a second time

Deletions produce the mirror image: the window slides past items the user never saw. That's the more damaging one — a duplicate is visible and annoying, a skip is invisible and permanent. Your engagement metrics will never tell you, and the support ticket ("my friend's post isn't in my feed") is unreproducible by definition.

The frequency scales with how active your feed is and how slowly the user scrolls. Which means: worst for your most engaged users, on your busiest days, and effectively never on your test account.

Keyset pagination

Stop asking "skip 20". Ask "give me what comes after this item".

select *
from posts
where (created_at, id) < (:cursor_created_at, :cursor_id)
order by created_at desc, id desc
limit 20;

Insertions at the head no longer affect the query at all — you're anchored to a position in the data, not a position in a count. Deletions below your cursor don't shift anything either.

It's also faster. OFFSET 10000 makes the database walk and discard 10,000 rows; a keyset query seeks directly via the index. On large tables the difference is a page of the feed loading in 8ms versus 900ms.

The tiebreak is not optional

This is the detail that gets dropped, and it silently recreates the original bug:

-- ❌ created_at is not unique
where created_at < :cursor_created_at

-- ✅ composite key, guaranteed unique and stable
where (created_at, id) < (:cursor_created_at, :cursor_id)

If two posts share a timestamp — bulk import, high write rate, second-granularity timestamps — one of them sits exactly on the boundary and is either duplicated or skipped. It happens rarely enough to survive testing and often enough to reach production.

Requirements for the sort key: stable (never updated), unique (with the tiebreak), and ordered consistently with the sort. updated_at fails the first test badly — editing an old post teleports it across the user's pagination window.

Make the cursor opaque

{ "items": [...], "nextCursor": "eyJ0IjoxNzMwLCJpIjo4OH0" }

Base64 of whatever you need internally. Clients must treat it as a token: send it back, never parse it.

If clients can decode and construct cursors, your sort key becomes public API. The day you add a pinned column to the ordering, or move from created_at to a ranking score, every client that reverse-engineered the format breaks — and they will have reverse-engineered it.

Three more lies

totalCount

SELECT COUNT(*) on a large filtered table is expensive, and the answer is stale before the response is serialised. Worse, its presence invites a "Page 3 of 47" UI that cannot be honest about a feed.

Options, in order of preference: don't return it; return "20+" semantics via a hasMore boolean; return an explicitly approximate count and name it approximateTotal so no one builds a pager on it.

Exception: a genuinely bounded, genuinely static list — a user's own order history — where a count is cheap, accurate and useful. Know which one you have.

hasMore disagreeing with the cursor

// Two sources of truth. They will diverge.
data class Page<T>(
    val items: List<T>,
    val nextCursor: String?,
    val hasMore: Boolean,
)

The classic bug: hasMore: true with an empty items array and a stale cursor. The client loops, or shows a spinner forever.

nextCursor == null means the end. That's it. If you keep hasMore for readability, derive it server-side from the cursor so the two can't disagree — and on the client, trust the cursor.

The client that refetches page 1 on rotation

Not the API's fault, but it produces the same user-visible symptom. Paging state belongs in the ViewModel, not the composable.

On Android: Paging 3

class FeedPagingSource(
    private val api: FeedApi,
) : PagingSource<String, Post>() {

    override suspend fun load(
        params: LoadParams<String>,
    ): LoadResult<String, Post> = try {
        val page = api.feed(cursor = params.key, limit = params.loadSize)
        LoadResult.Page(
            data = page.items,
            prevKey = null,               // forward-only feed
            nextKey = page.nextCursor,    // null ⇒ end. The only signal.
        )
    } catch (e: IOException) {
        LoadResult.Error(e)
    }

    // Cursors aren't positional, so anchor refresh on an item id.
    override fun getRefreshKey(state: PagingState<String, Post>): String? = null
}

Two things worth noting. nextKey = null is how Paging learns it's finished — pass page.hasMore in here and you've imported the inconsistency. And getRefreshKey returning null restarts from the top on refresh, which is usually right for a feed; for a "resume where you were" experience you need an anchor id and a server endpoint that accepts it.

Deduplicate client-side anyway. Belt and braces, keyed by stable id — it costs nothing and it means a server-side regression degrades to a missing item rather than a visibly broken list.

The test nobody writes

The entire bug class is caught by one test, and it takes ten minutes:

@Test
fun `insertion between pages neither duplicates nor skips`() = runTest {
    val page1 = repo.feed(cursor = null)

    // Someone posts while the user is reading.
    backend.insertAtHead(count = 3)

    val page2 = repo.feed(cursor = page1.nextCursor)

    val ids = (page1.items + page2.items).map { it.id }
    assertEquals(ids.size, ids.toSet().size)          // no duplicates
    assertTrue(backend.idsBetween(page1, page2).all { it in ids })  // nothing skipped
}

Add the deletion variant. That's twenty minutes total for a bug class that otherwise ships to production and arrives as unreproducible support tickets.

The rule

Offset pagination isn't wrong — it's correct for data that doesn't change, which isn't the data you have. Anchor to a position in the data, include the tiebreak, keep the cursor opaque, let nextCursor == null be the only end-of-list signal, and write the insertion test. Then the feed shows each item exactly once, which is the entire promise a list makes.