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

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

KU
· Founder & Chief Editor
• 8 min read•Updated •39 views

Offset pagination is easy until your dataset grows or shifts under load; here's why cursor pagination wins for large, changing, and public APIs.

Key takeaways

  • Use cursor (keyset) pagination for large, changing, or public APIs, because it stays fast at any depth and does not skip or duplicate rows when data changes.
  • Use offset pagination only for small, stable lists where users need to jump to a page number.
  • Always sort on a unique key.

Use cursor (keyset) pagination for large, frequently changing, or public APIs: it seeks straight to the next page through an index and never skips or repeats rows when data shifts. Use offset/limit only for small, stable lists where users need jump-to-page. Either way, sort on a unique key such as (created_at, id).

Verified against

PostgreSQL 18 (18.6 documentation), against the PostgreSQL LIMIT and OFFSET and row constructor comparison docs and RFC 8288 (Web Linking). Checked October 2026.

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 scans a full table while other queries wait.

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
-- 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 PostgreSQL 18 docs say it directly: rows skipped by OFFSET "still have to be computed inside the server; therefore a large OFFSET might be inefficient." 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.

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
-- 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;

The (created_at, id) < (...) form is a row constructor comparison: PostgreSQL compares the elements left to right and stops at the first unequal pair, which is exactly the "after this timestamp, then after this id" logic you want. Both columns must be NOT NULL, because a null in either pair makes the comparison unknown and the row silently disappears.

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
{
  "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. If you prefer headers to body fields, RFC 8288 defines the Link header with rel="next", which many public APIs use for the same purpose. Sign or validate tokens server-side, because a decoded cursor is still user input.

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. The PostgreSQL docs warn that without an ORDER BY that "constrains the result rows into a unique order" you get an unpredictable subset. 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.

Questions people ask

What is the difference between offset and cursor pagination?

Offset pagination skips a number of rows (LIMIT 20 OFFSET 60), so it supports jumping to any page but slows down as the offset grows and shifts results when rows are inserted. Cursor pagination asks for rows after the last one seen, using the sort key, so every page costs the same and writes elsewhere cannot cause skips or duplicates. It cannot jump to page 40.

Why is OFFSET slow on large tables?

The database still has to produce every skipped row before discarding it. OFFSET 2000000 means reading two million rows through the sort order and throwing them away to return twenty. PostgreSQL's documentation notes that a large OFFSET "might be inefficient" for exactly this reason. Keyset pagination avoids it by seeking to the cursor through an index on the sort columns.

Should a paginated API return a total count?

Only when the product genuinely needs it. An exact count is a second query over the whole matching set on every request, and on large tables it can cost more than the page itself. Return a has_more flag instead, computed by fetching limit + 1 rows. If a UI needs "1,240 results," cache the count or show an estimate.

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.

Sources

Explore topics:DevOps
React
Share this post
Cite this article
Plain text

Kiril Urbonas (2026). API Pagination: Offset vs Cursor (and When to Use Each). DevOpsNess. Updated 2026-10-31. https://www.devopsness.com/blog/api-pagination

Markdown

[API Pagination: Offset vs Cursor (and When to Use Each)](https://www.devopsness.com/blog/api-pagination) (Kiril Urbonas, DevOpsNess, updated 2026-10-31)

KU

Kiril Urbonas

Founder & Chief Editor

605 articles
View all articles by Kiril Urbonas

You might have missed

Evergreen posts worth revisiting.