Your list API uses OFFSET/LIMIT pagination. Under concurrent inserts, clients see missing or duplicate rows while scrolling. How would you design pagination that stays correct?

API DesignPaginationDatabaseConsistency

How I frame it

OFFSET pagination is unstable when the underlying set grows/shrinks between pages: new inserts shove rows, deletes create gaps. Cursor / keyset pagination is the standard fix for feeds and admin lists.

Why OFFSET breaks

Page1 OFFSET 0 LIMIT 2 → rows A B
Insert X (newest) at top
Page2 OFFSET 2 LIMIT 2 → rows C D  but B appears again or C skipped
depending on sort — classic duplicate/miss

OFFSET means "skip N rows of whatever the query sees now" — not "continue after the last item I saw."

Keyset (cursor) pagination

Sort by stable unique key, e.g. (created_at DESC, id DESC).
Client sends cursor = opaque encoding of last seen (created_at, id).
Query: WHERE (created_at, id) < (:ts, :id) ORDER BY created_at DESC, id DESC LIMIT 20
New inserts with newer timestamps appear on a later refresh of page 1 — they do not reshuffle page 2's window the OFFSET way.

Extras

Return next_cursor null when done — no total count required.
If you must show total pages, accept approximate count or cache count — do not rely on OFFSET for deep pages (slow + wrong).
Seek-only works with matching index on sort columns.

Weak answer

"Lock the table while the user paginates." Ridiculous availability. "Use higher isolation only." Does not fix OFFSET semantics across requests.

Follow-up questions

User wants jump to page 50?

Rare for infinite scroll products. If required, accept OFFSET for shallow admin tools only, or precompute page bookmarks.

Filtering + cursor?

Cursor must include filter hash or bind cursor to the same filter params; changing filters resets cursor.