WebTech1223411 logo WebTech1223411Web tech, read closely
Back-End

API Pagination With Cursors vs Offsets and When to Use Each

Your list endpoint was fast when the table had 2,000 rows. Now it has 8 million, page 4,000 takes three seconds to load, and users report seeing the same item twice while scrolling.

A developer monitor showing a JSON API response with a next cursor field beside a database query plan in a dark terminal

Your list endpoint was fast when the table had 2,000 rows. Now it has 8 million, page 4,000 takes three seconds to load, and users report seeing the same item twice while scrolling. Both problems usually trace back to how you built API pagination, and the fix starts with choosing between offsets and cursors deliberately rather than by habit.

I have migrated several production APIs from one style to the other. Neither is always right, but the trade offs are concrete and easy to reason about once you see what the database is actually doing.

How offset API pagination works

Offset pagination is the version most of us learned first. The client sends a starting position, usually alongside a limit parameter that sets the page size:

GET /orders?offset=200

The server translates that into SQL like SELECT ... ORDER BY created_at DESC LIMIT 50 OFFSET 200. It is simple to build, simple to explain and supports jumping straight to page 17, which is handy for admin tables with numbered page links.

The cost hides in the OFFSET. A database cannot skip rows for free. To return rows 200,001 to 200,050, it typically has to walk through the first 200,000 entries in index order and throw them away. On a well indexed PostgreSQL table I measured recently, offset 0 returned in about 2 ms, offset 100,000 in about 60 ms and offset 2,000,000 in over 1.2 seconds. Same query, same page size, very different work.

The duplicate and missing row problem

Offsets also break when data changes between requests. Suppose a user loads page one of newest orders, and five new orders arrive before they request page two. Every row shifts down by five, so the first five rows of page two are the last five rows they already saw. Deletes do the opposite and silently skip items. For a feed or an infinite scroll, that is a visible bug.

How cursor pagination works

Cursor pagination, sometimes called keyset pagination, replaces "skip N rows" with "give me rows after this one." The server returns an opaque token pointing at the last item on the page:

GET /orders?after=eyJjIjoiMjAyNi0xMS0wNCIsImlkIjo4ODEyfQ

Decoded, that cursor holds the sort values of the last row, for example the created_at timestamp and the id. The query then filters to rows whose (created_at, id) pair sorts before the pair stored in the cursor, ordered by created_at DESC, id DESC, with LIMIT 50. With a composite index on (created_at, id), the database jumps straight to the right spot in the index. Page 1 and page 40,000 cost the same.

Inserts and deletes no longer shift pages either, because each request anchors on a specific row rather than a position count.

Choosing between them

NeedOffsetCursor
Jump to page NYesNo, only next and previous
Stable results while data changesNoYes
Deep pages on large tablesSlowConstant time with the right index
Total page count in UIEasy, though COUNT can be slowAwkward
Arbitrary user chosen sortEasyNeeds an index per sort
Implementation effortLowModerate

My rule of thumb: use cursors for feeds, timelines, activity logs, sync endpoints and any public API where clients page through everything. Use offsets for small, mostly static datasets and internal admin screens where people want numbered pages and rarely go past page 20.

Why "always use cursors" is overstated

A lot of API advice now says offsets are an anti pattern. I disagree with that as a blanket rule.

For a table under roughly 50,000 rows, offset queries stay in the low milliseconds even at the last page. Cursor pagination adds real complexity: you need a unique, stable sort key, composite indexes for every sort order you expose, careful encoding of the cursor and clear rules for what happens when the client changes filters mid scroll. If your product team wants a "sort by any column" table with a page selector, forcing cursors there creates more bugs than it prevents.

The better question is not which style is modern but how large the dataset will get and how fast it changes. A settings list for 300 user roles does not need keyset pagination.

Implementation details that save you later

Whichever style you pick, a few habits prevent painful migrations:

  1. Always sort on a unique tiebreaker. Sorting only by created_at gives unstable ordering when two rows share a timestamp. Add the primary key as a second sort column.
  2. Make cursors opaque. Base64 encode them so clients cannot build their own. That lets you change the internals later without breaking anyone.
  3. Cap the page size. Accept a limit but enforce a maximum, such as 100. Without it, one client asking for 50,000 rows can exhaust your connection pool. Our guide to database connection pooling explains why that hurts everyone else.
  4. Return a next link, not just a token. Include a ready made URL for the next page in the response. Clients make fewer mistakes.
  5. Avoid exact totals on big tables. COUNT(*) on millions of rows can cost more than the page itself. Return "has_more" or an estimated count instead.
  6. Watch out for N+1 queries per page. Fetching related records row by row on a 100 item page multiplies latency. See how N+1 queries slow an API for ways to batch them.

Migrating an existing offset API

If clients already depend on offsets, you do not have to break them. Add cursor support alongside the old parameters, return a next cursor in every response, and log which clients still send offset values. In one migration I worked on, about 80 percent of traffic moved to cursors within two months simply because the official SDK switched first. After that, we capped the maximum offset at 10,000 and returned a clear error message pointing to the cursor parameter, which handled the stragglers without a hard cutoff.

Document the behaviour clearly, especially what happens when the cursor points at a deleted row. Good pagination is part of designing an API that feels predictable, which we cover more broadly in designing a REST API other developers enjoy.

Frequently asked questions

What is the difference between offset and cursor pagination?

Offset pagination asks the database to skip a number of rows and return the next batch. Cursor pagination asks for rows that come after a specific row, identified by its sort values. Cursors stay fast on deep pages and do not repeat or skip items when data changes.

Why is offset pagination slow on large tables?

The database usually has to read and discard every row before the offset, even with an index. Deep pages therefore get slower as the offset grows. Cursor queries use the index to jump directly to the starting point.

Can you jump to a specific page with cursor pagination?

Not directly, since cursors only know the next and previous positions. Some APIs combine both approaches, offering cursors for scrolling and offsets for shallow numbered pages. If users truly need page numbers, offsets are usually the simpler fit.

KO
Kofi Oosterhuis

Kofi spent years keeping APIs and game servers alive through traffic spikes and the occasional bad deploy. He covers back-end design, scaling and networking, with a preference for boring systems that do not page anyone at night.

More posts by Kofi

More in Back-End

01doorganico 084goad 3hot8 4465456 after5internprogram ahandfulofblessings ahmadpc ahomcybersecurity allodialresources annodex antiquesandperiodcollectibles aotc asianworldkashmir beedlectrics bloggertemplatefree bluefeatheragency bodyweightsolution campaign08blog carfixadvisor catstreetcollective cbstvsports cnfanspurchasing committeeof500yearsofdignityandresistance covertopsdjs cs-kaiyuangame cuestacomunicaciontotal cxoutlet dancebetgame deskgearhub destinyvillastay detectmagazine di33 diamondsteelbuildings donmo dyanadropship emarsysfinance emimattl escapechan eskitarih evautoplay everythingweddings-orlando farmzone feedviewerapp foxstudiosscratch gamevui123 gleasongrade greetingsday hacklabasu haskell iw-kaiyun jagoanproperti jilidskripsi joltbusinessfinance juegodepokergratis juegospucca kaeru-festival latestmehndidesigns lestanzediottaviano litemagazines localcommunityhero lustrsazan mariashebets menswheels newwebstechs1 nikkit ootheday oxigenvpn plurfitness prefetial probudgetcarpetcleaning psychicmrsmartin pyaten qvantor rejoignezlogis rexzbot riselayouts ruchin rwxqsgd5 sakaltime shibiaowang shop-bot skrtips spotlesshaven strcoding tetapatrium topselller uneplanteartificielle vipertechcr wo-kygaming yamaha-mekar yeknava yoybuysheets zenunwa yamaha-mekar jilidskripsi ankertoto ankertoto ankertoto