Pagination: why an item can appear twice—or vanishOrder the records before dividing them into pages

Define one complete order

Our fictional feed sorts lessons by publication time descending and uses id descending to break ties. The second field matters because two lessons can share the same time. Without a unique ordering, “the next two records” is ambiguous even before anyone edits the data. SQL does not promise a particular result order just because it looked consistent in an earlier run.

Choose the order the product actually needs. Newest publication is different from latest edit, and alphabetical order answers another question. If an update changes a sort key, the record can move. Record that behavior in the API contract rather than letting the UI infer stability from the word “page.”

Offset counts positions in the current result

With LIMIT 2 OFFSET 0, our initial ordered ids are 5 and 4. The next offset would skip two rows. Before that request, insert new id 6 at the front. The current result begins 6, 5, 4, 3. Skipping two now returns 4 and 3, so id 4 repeats from the first page.

Deleting a row before the next offset can instead make the client skip an item it has not seen. Offset pagination is not inherently wrong: it can be convenient for bounded lists and page-number navigation. The issue is whether its behavior under change matches the reader’s expectation. Large offsets can also require substantial work; measure the chosen engine and query.

A cursor describes a continuation boundary

A keyset-style continuation can say “return records after the last tuple I saw.” In our descending example, the boundary after id 4 is its publication time and id. The next query selects strictly smaller ordered tuples, rather than skipping a count of current rows. Adding id 6 ahead of that boundary does not cause id 4 to reappear.

For equal timestamps, include the id tie-break in the boundary condition. Using only a date can skip all other records sharing that date. A cursor may encode the boundary opaquely, but encoding is not encryption or authorization. Validate its structure and bind it to the intended filters and ordering. Keep private information out of a casually exposed token.

A cursor is not a snapshot

Continuation helps with certain changes, but records can still be deleted, edited or moved across the boundary. If a product needs a consistent point-in-time report, it may need a snapshot or other database/application mechanism. Neither a cursor nor deduplication in the browser creates that guarantee by itself.

Also define whether new items should appear during a reading session. A feed may offer “new lessons available” and refresh from the top rather than inserting cards above a reader mid-scroll. Stability and freshness are product choices that can conflict. There is no single correct behavior for every catalogue or audit report.

Execute the changing-list example

The companion fixture uses real in-memory SQLite queries with deterministic ids. It checks the initial page, insertion before an offset, the duplicated offset result and a keyset result without that duplicate. A second check gives two records the same timestamp and confirms that the composite boundary retains the remaining item.

This is database query evidence, not a deployed API, a signed-cursor implementation or a performance measurement. The public-looking identifiers are synthetic. Predict each result before running the fixture so a green assertion does not replace understanding of which rows were included.

Design the API response around the reader

Return the requested records and an explicit next-page indication under the service’s contract. Avoid guessing “there must be another page” from a full result alone if the API has a reliable continuation field. Handle expired or invalid continuations with a useful recovery path. Limit page size, authorize every request and prevent endless loading when no next page exists.

Limits

We demonstrate descending keyset continuation and ordinary offset queries. Distributed snapshots, arbitrary sorting, signed cursors and concurrent transaction isolation need additional design. The fixture does not claim that cursor pagination is always faster or preferable. Use the smallest approach that satisfies the list’s real consistency and navigation requirements.

References

Sources used to check the explanations and examples are listed below.

1. PostgreSQL: LIMIT, OFFSET and ordering
2. PostgreSQL: row constructor comparisons

Found a mistake? Send a correction with this lesson’s title, URL and disputed detail. No login needed.