Pagination looks like a solved problem until a collection is large enough, and changes fast enough, that the naive approach starts silently skipping or duplicating rows — and by the time anyone notices, it's usually reported as "search results seem inconsistent" rather than "your pagination has a known class of bug," because the failure is subtle and intermittent. The two dominant approaches, offset-based and cursor-based, trade off implementation simplicity against correctness under concurrent writes, and the honest answer is that offset pagination is fine until it isn't.
Offset pagination: simple, and fragile under writes
GET /invoices?page=3&per_page=20 translates directly to OFFSET 40 LIMIT 20 in SQL — trivial to implement, trivial to reason about, and lets a client jump straight to page 10 without having walked through pages 1-9 first. The problem shows up the moment rows are inserted or deleted between page requests: if a row is deleted from page 2 while a client is paging, everything after it shifts left by one, and page 3 now starts one row earlier than the client expects — one row gets skipped entirely, silently. The inverse happens with inserts: a row can appear twice across two page fetches.
GET /v1/invoices?page=3&per_page=20 HTTP/1.1
HTTP/1.1 200 OK
{
"data": [...],
"page": 3,
"per_page": 20,
"total_count": 4821
}Cursor pagination: stable under concurrent writes
Cursor-based pagination replaces "give me page 3" with "give me the 20 rows after this specific row I already saw," using an opaque cursor token (typically an encoded value from a unique, monotonically ordered column like an id or created_at+id compound key) rather than a numeric offset. Because the cursor identifies an actual row rather than a position count, inserts and deletes elsewhere in the table don't shift what "after this cursor" means — the client always resumes from exactly where it left off, correctly, regardless of what else changed in between.
GET /v1/invoices?after=eyJpZCI6NDQ3MSwidHMiOjE3MTk0NzJ9&limit=20
HTTP/1.1 200 OK
{
"data": [...],
"next_cursor": "eyJpZCI6NDQ5MSwidHMiOjE3MTk0Nzl9",
"has_more": true
}Base64-encoding the cursor (an id, a timestamp, or a compound key) keeps it opaque to the client and gives you room to change its internal structure later without breaking the pagination contract — clients should treat the cursor as a token to pass back, never a value to parse or construct themselves.
Total count is expensive, and cursor pagination usually drops it
total_count requires a full COUNT(*) scan (or an approximation) that gets more expensive as the table grows, and it's the reason many APIs that adopt cursor pagination also drop total count entirely in favor of has_more: true/false. If your UI genuinely needs "page 4 of 92," you likely want offset pagination (or a cached, periodically-refreshed approximate count) rather than paying for an exact count on every single request.
Pick based on the actual access pattern, not habit
Offset pagination is the right choice when users need to jump to an arbitrary page number, when the underlying data is small or rarely mutated concurrently with reads, or when total-count display matters more than perfect consistency. Cursor pagination is the right choice for infinite-scroll UIs, for any API consumed by another system doing a full paginated export, and for any table under meaningful concurrent write load — which describes most production APIs backing an active application, more often than teams initially assume.
A client that caches total_count and computes page = total_count / per_page locally to "jump to the last page" will compute the wrong page the moment the server-side count has changed since the client last fetched it — another symptom of the same underlying fragility offset pagination has under concurrent writes.
Wrapping up
Offset pagination is simpler to build and to reason about, and is fine for small or slow-changing collections; cursor pagination costs a little more implementation complexity up front and buys correctness under concurrent inserts and deletes, which is the more common real-world condition than it looks like in a design doc written before the API has real traffic.
Independent software engineer in Nairobi specialising in Acumatica customisations, Laravel backends, and tax fiscalisation integrations across East and Southern Africa.