API Pagination: Offset vs Cursor (and When to Use Each)

The fastest way to take down your own service is to ship an endpoint that returns a list without a limit. It works fine in staging with 200 rows. Then a customer imports 2 million records, calls GET /events, and your API tries to serialize all of them into one JSON blob. Memory spikes, the request times out, and the database sits there scanning a full table while every other query waits behind it.

Pagination is the fix, and there are two main ways to do it. They look similar from the outside but behave very differently once data gets large or starts changing while people read it.

Why paginate at all #

Three reasons, and they compound.

Memory: Loading an unbounded result set pulls every row into your process before you send a byte. One big request can push a service into an out-of-memory kill.

Latency: Clients wait for the whole set to be built and transferred. A capped page returns in predictable time regardless of how much data sits behind it.

Cost: Bandwidth, database CPU, and serialization all scale with rows returned. Unbounded endpoints turn one careless client into a bill.

The rule is simple: never return a list whose size you don't control. Cap it, and give the caller a way to ask for the next chunk.

Approach 1: offset / limit #

This is the one everybody reaches for first. The client sends a page number and a page size, and you translate that into LIMIT and OFFSET.

sql.sql

-- page 4, 20 per page  ->  OFFSET (4-1)*20
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 60;

What's good: It's trivial to implement, and it supports jump-to-page. A UI can show "Page 1 2 3 ... 47" and let someone click straight to page 40, because offset is just arithmetic.

What hurts: Two things.

First, deep offsets are slow. OFFSET 60 is cheap, but OFFSET 2000000 forces the database to read and discard two million rows before it reaches the ones you want. The cost grows with how deep you page, so the last pages of a big list are the slowest.

Second, offset is inconsistent under writes. It counts rows by position, and position moves when the data changes. Insert a new article at the top while a client is paging and every row shifts down by one. The client sees the same row twice across two pages, or skips one entirely. On a busy feed this happens constantly.

Offset is fine when the dataset is small and mostly static. It's a poor fit for anything large or actively changing.

Approach 2: cursor / keyset pagination #

Instead of "skip N rows," keyset pagination says "give me rows after this specific point." The point is a stable pointer built from the columns you sort on, usually the last row's sort key plus its id as a tiebreaker.

sql.sql

-- rows after the last one the client saw
SELECT id, title, created_at
FROM articles
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

What's good: It's fast at any depth. With an index on (created_at, id), the database seeks straight to the cursor position and reads 20 rows. Page one million costs the same as page one. It's also consistent under writes: because the cursor is anchored to actual data, not a row count, inserts and deletes elsewhere don't cause skips or duplicates.

What hurts: No jump-to-page. You can go next (and, with a mirrored query, previous), but you can't land on "page 40" without walking there. You also need a stable, total sort order, which we'll come back to.

Approach 3: opaque page tokens #

The keyset examples above leak last_created_at and last_id into the API surface. That's a maintenance trap: the moment a client hardcodes those fields, you can't change your sort key without breaking them.

The fix is to hand back an opaque token. Encode the cursor state (sort key, id, maybe the sort direction) into a string, base64 it, and treat it as meaningless to the client. Their only job is to send it back.

json.json

{
  "data": [
    { "id": 8823, "title": "Rolling restarts done right", "created_at": "2026-08-10T14:02:00Z" },
    { "id": 8822, "title": "Draining connections cleanly", "created_at": "2026-08-10T13:47:00Z" }
  ],
  "page": {
    "next_cursor": "eyJjIjoiMjAyNi0wOC0xMFQxMzo0NzowMFoiLCJpZCI6ODgyMn0",
    "has_more": true
  }
}

When next_cursor is null or absent, the client knows it has reached the end. This keeps your internals private and lets you evolve the cursor format later.

Response shape and the total-count question #

The core response is data plus a next cursor. The tempting extra is a total count so the UI can render "1,240 results." Be careful with it. Counting the full matching set is a separate, often expensive query, sometimes a full scan, on every page request. On a large table that count can cost more than fetching the page itself.

If you don't strictly need an exact total, drop it. A has_more boolean, which you get for free by fetching limit + 1 rows and checking whether the extra one exists, is usually enough. Offer exact counts only where the product requires them, and cache them when you do.

Stable sort ordering is not optional #

Both approaches assume a total order. If you sort by created_at alone and two rows share a timestamp, their relative order is undefined and can flip between queries, which breaks cursors and can duplicate rows. Always append a unique tiebreaker (the primary key) to the sort, and make sure your cursor and your index both use the full key. Sort by (created_at, id), index on (created_at, id), and encode both into the cursor.

How to choose #

Reach for cursor/keyset when: the dataset is large, it changes while people read it, or it's a public API where clients will page deeply and you can't predict their behavior. Infinite-scroll feeds, event streams, and export endpoints all want cursors.

Reach for offset when: the list is small and bounded, it changes rarely, and a human wants to jump to an arbitrary page. Internal admin tables and settings screens are the sweet spot. The simplicity is worth it there and the downsides never bite.

The call we'd make #

Default to cursor pagination with opaque page tokens for anything customer-facing or anything that might grow. It costs a little more up front to build, but it stays fast at scale, stays correct under writes, and doesn't force a painful migration later when a table you thought was small turns out not to be. Keep offset for the handful of small admin lists where jump-to-page actually earns its keep, and skip the total count unless the product demands it.

For the broader picture on versioning, errors, and consistency across your endpoints, see our guide to API design best practices.

#Pagination

API Pagination: Offset vs Cursor (and When to Use Each)