API Pagination: Offset vs Cursor, and Why Offsets Break

Items repeat and items go missing, because the app asks for the next page by counting rows from the top, and the top keeps moving. Deep pages also get slower with every page.

Picture a common setup. A small team runs a local classifieds app in Indore, and its home screen shows new listings for one city, newest first: used scooters, rooms on rent, shop offers. The setup is made up, but the problem is real.

The feed uses infinite scroll. When the user nears the bottom, the app quietly asks the API for the next page of 20 listings. The API takes a page number, so page 3 means skip the first 40 listings and send the next 20.

It worked in testing, where nobody was posting. Then the evening rush arrived, with sellers posting about 30 listings a minute (made up). Three kinds of complaint came in.

• A buyer scrolled past the same red scooter three times in one session.
• A seller's fridge, posted that afternoon, never showed up for a buyer who scrolled through every page that evening.
• Scrapers, bots that copy a whole site, paged to the very end, and every deep request made the database work harder.

The team's first guess was a caching bug. It was not. All three problems come from the way the feed is split into pages, and this guide fixes them one by one.

API pagination is the way an API hands over a long list in small pieces, called pages, instead of all at once. The client asks for one page, then asks for the next.

An API (application programming interface) is the set of requests one program can make to another. Here, the phone app sends requests and the team's server answers them. The Indore feed holds about 2 lakh (200,000) live listings, far too many for one response.

So every paginated API must answer one question. When the client asks for the next page, how does the server know where the last page ended? There are two common answers.

• Offset pagination: the client says how many items to skip and how many to take, such as skip 40, take 20.
• Cursor pagination: the client sends back a bookmark from the last page, meaning continue after the last listing I got.

A cursor is that bookmark: a small token the server hands out, pointing at the last item the client received. Keyset pagination is the most common way to build one. It stores the values of the sort columns from that last item.

Offset pagination sorts the whole list, skips a number of rows called the offset, and returns the next few rows, called the limit. Page n starts at offset (n minus 1) times the page size.

SQL is the language most databases speak. The team's first feed query said, in plain words: sort Indore listings by created time, newest first, skip 40, return 20. The SQL keywords for this are ORDER BY, OFFSET and LIMIT.

This is the naive first try, and it is popular for good reasons. It is easy to build and easy to explain. The client can jump straight to page 37, which suits numbered page buttons.

Offset pagination breaks because an offset is a position, and positions move when rows are added or removed above it. The same number then points at a different item.

Picture a bangle stall at a busy market. The seller stacks glass bangles on a tall rod, and every new bangle slides on at the front. Ask her to keep the 20th bangle for you, and come back in an hour.

Five new bangles have gone on since then. The 20th is now a different bangle, and the one you liked is 25th. The rod is the sorted feed, each new bangle is a new listing, and 20th from the front is the offset.

Pause and predict before reading on. The buyer has page 1, then 8 new listings arrive, then the app asks for offset 20. Which listings does the buyer see at the top of page 2?

1. 1

   Page 1 at 7:40:00 pm

   The app asks for offset 0, limit 20. It gets listings L1 to L20, newest first, and the buyer starts reading.

2. 2

   Sellers keep posting

   In the next 20 seconds, 8 new listings arrive. They go on top, so L1 is now at position 9 and L20 at position 28.

3. 3

   Page 2 at 7:40:20 pm

   The app asks for offset 20, limit 20. Positions 21 to 40 now hold L13 to L32.

4. 4

   The buyer sees repeats

   L13 to L20 appear a second time, right below where they were. That is the red scooter showing up again.

What just happened? Nothing went wrong on the server. Every query returned exactly what it asked for; the list simply moved between two requests. Our PostgreSQL 16 test reproduced it: 8 new rows on top gave 8 repeats on page 2.

Deletes cause the opposite bug. When sellers mark items as sold, those listings leave the feed, and everything below them moves up. The next offset then jumps over listings the buyer never saw.

That is how the fridge went missing. While the buyer read page 9, six listings above were sold. The fridge moved up from page 10 into page 9's range, which the app had already fetched.

Where the picture breaks: a real seller would remember which bangle you liked. A database remembers nothing between requests. It only follows the instruction it gets, which is why the instruction itself must name the item.

Why do deep pages get slower?

Deep pages get slower because the database cannot jump straight to where a deep page starts. The PostgreSQL documentation says rows skipped by OFFSET still have to be computed inside the server, so a large offset might be inefficient.

Even an index, a sorted copy of chosen columns, does not record how many rows sit before each entry. So PostgreSQL walks past the skipped rows one by one. Page 200 skips 3,980 rows to return 20, and the last page skips nearly 2 lakh.

We measured this on PostgreSQL 16.15 with 2 lakh Indore rows and an index on the sort columns. Times are the database's own execution times, medians of 25 back-to-back runs in two test sessions on one machine. They leave out network and app time, and yours will differ.

• Offset 0 (page 1): about 0.02 ms.
• Offset 3,980 (page 200): about 2 ms.
• Offset 1 lakh (100,000): 50 to 70 ms.
• Offset 199,980 (the last page): 110 to 120 ms.
• Cursor query at each of those depths: under 0.1 ms.

Even 120 ms sounds small, until a scraper sends thousands of these requests at once. Each deep request keeps a database connection busy for its whole run, so busy evenings get slower for every user.

Cursor pagination remembers the last item the client received and asks for the items that come after it in the sort order. The request says where to continue, not how many rows to skip.

Back at the bangle stall, try a different request. Ask her to keep the bangles just after the green glass one with gold dots. New bangles still slide on at the front, but that description still points at the same place.

The feed sorts by created time, newest first, with the listing ID as a tie-breaker. So the cursor holds two values from the last listing on the page: its created time and its ID. The next request asks for listings older than that pair.

In SQL this is a row comparison: (created_at, id) < (last_time, last_id). PostgreSQL compares such pairs left to right and stops at the first pair that differs. So the ID only matters when two created times are equal.

There is no OFFSET anywhere. With an index on the sort columns, the database jumps straight to the cursor's spot and reads 20 rows. So the last page costs about the same as page 1.

Here is the full exchange between the app, the API and the database. Follow the cursor: the API creates it in one response and gets it back in the next request.

Offset pagination finds a page by counting rows from the top, while cursor pagination finds it by continuing after a known item. That one change affects correctness, speed and what the client can do.

When is offset pagination still the right choice?

Offset is fine when the list is small or changes slowly, and people need page numbers. The app's moderator panel is a good example.

• Moderators review reported listings, a few hundred a day, sorted by report time.
• They tell each other to check page 4, which a cursor cannot offer.
• A repeated row now and then costs nothing, because moderators act on each row anyway.
• The team caps the offset at 5,000, so no request can wander too deep.

The diagram below sums up how the team decides for each new list endpoint.

You query the rows after the last sort-key pair, fetch one extra row to learn whether more exist, and add an index that matches the sort. Here is the core of the feed query.

Here db is a psycopg 3 connection to PostgreSQL. Its execute method runs the SQL with each %s safely filled from the list. The after argument holds the created time and ID from the cursor.

PAGE_SIZE = 20

def newest_listings(db, city, after=None):
    sql = "SELECT id, title, created_at FROM listings WHERE city = %s "
    params = [city]
    if after:                                    # (created_at, id) of the last row sent
        sql += "AND (created_at, id) < (%s, %s) "
        params += [after[0], after[1]]
    sql += "ORDER BY created_at DESC, id DESC LIMIT %s"
    params.append(PAGE_SIZE + 1)                 # one extra row reveals if more exist
    rows = db.execute(sql, params).fetchall()
    has_more = len(rows) > PAGE_SIZE
    rows = rows[:PAGE_SIZE]
    last = (rows[-1][2], rows[-1][0]) if has_more else None
    return rows, has_more, last

Two lines matter most. The row comparison is the cursor at work: no skipping, only rows older than this pair. Asking for PAGE_SIZE + 1 rows fetches one extra, so has_more costs no second query.

The index should hold the filter column and then the sort columns, in order: city, then created_at, then id. PostgreSQL's default index type, the B-tree, can be read backwards, so a plain ascending index also serves this newest-first sort.

Without a suitable index, PostgreSQL scans the whole table and sorts the matching rows on every request, and the speed gain disappears. We saw exactly that plan after dropping the index.

You can check this yourself in psql, PostgreSQL's command-line client. Replace feeddb with your database name. The first command adds the index; the next two show the plan and real timings for a deep offset page and a cursor page.

psql -d feeddb -c "CREATE INDEX listings_feed ON listings (city, created_at, id)"
psql -d feeddb -c "EXPLAIN ANALYZE SELECT id, title, created_at FROM listings WHERE city = 'Indore' ORDER BY created_at DESC, id DESC OFFSET 3980 LIMIT 20"
psql -d feeddb -c "EXPLAIN ANALYZE SELECT id, title, created_at FROM listings WHERE city = 'Indore' AND (created_at, id) < ('2026-10-16 01:04:20+00', 183382) ORDER BY created_at DESC, id DESC LIMIT 20"

In the offset plan, the line under Limit says Index Scan Backward using listings_feed, with actual rows=4000. The database read 4,000 rows to return 20. Single psql runs are slower than our medians: page 200 took 12 to 17 ms and the cursor page about 0.2 ms. Row counts stay the same, so compare those.

In the cursor plan, the scan line shows actual rows=20, the Index Cond line below it holds the ROW comparison, and there is no Sort line. Use created time and ID from a real row in your table as the cursor values.

What about going back a page?

To go backwards, flip both the comparison and the sort, then reverse the rows before sending them. Our test got back exactly page 1 this way. Stripe's list endpoints offer this as ending_before, the mirror of starting_after.

A good cursor follows a unique sort order, means nothing to the client, and cannot be edited or reused with different filters. Getting these details right is most of the work.

• Unique order. Sort by something that never ties, or add the ID as the last tie-breaker. In our test, a cursor on created time alone skipped a row that shared the boundary time.
• Opaque, meaning the client must not read or build it. The cursor connections specification for GraphQL, a query language for APIs, says clients should treat cursors as opaque, which lets you change the inside later.
• Signed. If a client can edit the cursor, it can poke at your query. Add a short signature, so the server rejects any cursor it did not create.
• Tied to its filters. A cursor from the Indore feed should not work on the Bhopal feed. Put the filters inside the cursor or its signature, and check them.
• Built from fields that do not change and are never NULL, so declare them NOT NULL. A row comparison with a NULL gives unknown, not true. Newest first, NULL rows show at the top of page 1, then drop out of every cursor page. A NULL inside the cursor returns an empty next page.

Here are the cursor helpers. URL-safe base64 turns bytes into plain text that can travel in a URL. HMAC is a standard way to sign data with a secret key, and load_secret stands in for your config loader.

import base64, hashlib, hmac, json
from datetime import datetime

SECRET = load_secret("CURSOR_KEY")               # from config, never hard-coded

def _sign(body, city):                           # the feed's city is signed too
    return hmac.new(SECRET, body + b"|" + city.encode(), hashlib.sha256).hexdigest()[:16]

def encode_cursor(created_at, listing_id, city):
    body = json.dumps({"t": created_at.isoformat(), "id": listing_id}).encode()
    return base64.urlsafe_b64encode(body).decode() + "." + _sign(body, city)

def decode_cursor(token, city):
    body_b64, _, sig = token.partition(".")
    body = base64.urlsafe_b64decode(body_b64)
    if not hmac.compare_digest(sig.encode(), _sign(body, city).encode()):
        raise ValueError("invalid cursor")       # edited, or made for another feed
    data = json.loads(body)                      # read only after the check passes
    return datetime.fromisoformat(data["t"]), data["id"]

The signature is a short hash of the cursor body plus the feed's city, made with the secret key. An edited cursor, or one made for another city, fails the check. The body is read only after the check passes.

Python's documentation says compare_digest avoids content-based short-circuiting, so response times do not reveal how much of a guessed signature was right. Different lengths can still leak the length, which is harmless here.

Opaque does not mean secret. Anyone can base64-decode the body and read the time and ID, so never put private data in a cursor. Encrypt it only if even those values must stay hidden.

Every broken cursor raises ValueError; Python's base64 error is a kind of ValueError too. The API turns it into a 400 Bad Request, the error code for a request the server will not accept.

What should a paginated response look like?

A paginated response returns the items, a flag saying whether more exist, and the cursor for the next request. This feed returns items, has_more and next_cursor in its body as JSON, a plain-text data format.

Well-known APIs use the same shape. Stripe's v1 list endpoints take a limit from 1 to 100, default 10, and a starting_after holding an object ID. Each response carries has_more, as of October 2026.

GraphQL APIs usually follow the cursor connections specification. Each item, called a node, comes with its own cursor. A pageInfo object gives endCursor and hasNextPage, and the client passes first and after to get more.

Plain HTTP has an option too. RFC 8288, an internet standards document, defines the Link header, which points from one resource to related ones. IANA, the body that keeps the internet's official lists, registers the link types next and prev.

The app keeps the two directions apart. Older items come from the cursor as the user scrolls down, and newer items are checked separately and shown only when the user asks.

With cursors, the repeats are gone. New listings on top are still not part of the downward scroll, and that is correct, because pushing them in would move everything under the user's thumb.

So the team adds a second, small request. Every 30 seconds, the app asks how many listings are newer than the first one on screen. The API counts at most 100, so the check stays cheap.

In SQL, that is a count over a sub-query, a query nested inside another, that keeps rows with (created_at, id) > (first_time, first_id) and LIMIT 100. Our plan for it showed the same index, read forwards, with no Sort.

If the count is above zero, a pill at the top says, for example, 12 new deals. Tapping it reloads the feed from the top with no cursor, and the old cursor is thrown away.

The app also follows four rules:

• Store only next_cursor between requests, never a page number.
• Skip any listing whose ID is already on screen, as a cheap safety net.
• On pull-to-refresh, drop the cursor and start again from the top.
• If the API rejects a cursor, for example after a secret key rotation, reload from the top instead of showing an error.

A cursor misses any row whose sort key lands behind it after the user has scrolled past. The most common cause is a slow transaction, a group of database writes that become visible together when they commit.

In PostgreSQL, now() returns the start time of the current transaction, not the commit time. So a listing saved by a slow transaction gets an early created_at but appears only when that transaction commits.

We ran this on PostgreSQL 16.15. A slow transaction inserted a row, then waited one second. Meanwhile a fast one inserted and committed, and the reader fetched page 1, with the fast row on top.

When the slow row finally committed, its created_at sat between page 1's top and the cursor. Page 2 did not include it, and the newer-than check counted 0. Only a reload from the top showed it.

Sorting by ID does not save you. Identity IDs, the numbers PostgreSQL hands out in order to new rows, are assigned when the row is inserted, not when it commits. In our test the slow row also had the smaller ID.

For a feed, this gap is usually acceptable, because pull-to-refresh shows the row. Keep insert transactions short, so the window stays small.

If clients must receive every row, as in a sync job, a time cursor alone is not enough. A common fix is to re-read a short overlap, longer than your slowest insert transaction, and drop IDs already seen.

Edited sort keys cause a smaller version of the same problem. If a feed sorts by price, cheapest first, and a seller cuts a price, that listing can move into the part already scrolled past.

Explain it as a choice between counting positions and continuing after a known item. Then show that you know where each one fails.

1. 1

   Ask how the list changes

   Ask how fast items are added or removed, and how deep users page. A busy feed and a quiet admin table need different answers.

2. 2

   Name the offset problems

   Say that offsets shift when rows arrive or leave above, causing repeats and skips. Add that deep offsets get slow, because skipped rows are still read.

3. 3

   Propose keyset cursors

   Sort by a unique key, such as created time plus ID. Query rows after the last pair, with an index on the same columns.

4. 4

   Design the cursor

   Make it opaque, signed and tied to its filters. Return items, has_more and next_cursor, and cap the page size.

5. 5

   Handle new items

   Keep the downward scroll stable. Show new items through a separate newer-than check and a refresh.

6. 6

   Admit the trade-offs

   Cursors cannot jump to page 37 or give a cheap total count. Late commits can still slip behind a cursor. Keep offset for small, calm lists.