Your API works perfectly today.

Then six months later, your transaction table grows from 10,000 records to 10million records.

Suddenly, requests that once took 80ms now takes 4seconds.

The question is:

Did you choose the right pagination strategy?

Most backend developers are familiar with pagination, but many don't realize that there are different pagination strategies, and choosing the wrong one can seriously affects your application's performance as data grows.

In this articles, we'll compare the two most common approaches:

  • Offset Pagination
  • Cursor Pagination

We'll look at how they work, their advantages and disadvantages, when to use each one, and how companies handling millions of records approach this problem.

What is Pagination?

Pagination is simply the process of breaking a large dataset into smaller chunks.

Imagine your database contains 5 million transactions.

Returning all 5 million records in a single API response would:

  • Consume excessive memory
  • Slow down the database
  • Increase API response time
  • Create a poor user experience

Instead, we return only a small subset.

For example:

10 records per request

The client then requests additional records when needed.

The question is...

How should the next set of records be fetched?

There are two common answers.

1. Offset Pagination

Offset pagination is probably the first pagination technique every backend developer learns.
A request usually looks like this:

GET /transactions?page=3&limit=10

or

GET /transactions?offset=20&limit=10

The database query looks something like:

SELECT *
FROM transactions
ORDER BY created_at DESC
LIMIT 10
OFFSET 20;

Enter fullscreen mode Exit fullscreen mode

Here, the database skips the first 20 rows before returning the next 10.

Visual Representation

Suppose we have these transaction IDs:

 100
 99
 98
 97
 96
 95
 94
 93
 92
 91
 90
 89
 88
 87
 ...

Enter fullscreen mode Exit fullscreen mode

with

Page = 1
Limit = 5

Enter fullscreen mode Exit fullscreen mode

You get:

 100
 99
 98
 97
 96

Enter fullscreen mode Exit fullscreen mode

Page 2 skips those records and returns:

 95
 94
 93
 92
 91

Enter fullscreen mode Exit fullscreen mode

Simple, Easy and Readable.

Advantage of Offset Pagination

→ Very easy to implement
Almost every framework supports it.

→ Easy to jump to any page
Want page 50?
No problem

?page=50

Enter fullscreen mode Exit fullscreen mode

→ Great for admin dashboards

This is why most admin panels use offset pagination.

Users often want to jump directly to page 8 or page 30

The problem with Offset Pagination

Imagine this query:
OFFSET 5000000

The database still has to scan and skip five million rows before returning the next 10.

That means:

 Skip
 Skip
 Skip
 Skip
 Return 10 rows.

Enter fullscreen mode Exit fullscreen mode

As the table grows larger, this becomes increasingly expensive.

Large offsets lead to slower queries.

Another issue appears when new records are inserted while users are browsing.

Imagine:

Page 1

 100
 99
 98
 97
 96

Enter fullscreen mode Exit fullscreen mode

A new transaction arrives:

101

Now Page 2 may suddenly contain duplicate or missing records because the dataset shifted between requests.

2. Cursor Pagination

Cursor pagination solves this differently.
Instead of saying:

"Skip 500 rows."

It says:

"Continue from the last record I already received."

A request looks like:

GET /transactions?limit=10

The API returns:

{
    "data": [....],
    "next_cursor": "2026-07-31T16:06:30"
}

Enter fullscreen mode Exit fullscreen mode

the client now sends:

GET /transactions?cursor=2026-07-31T16:06:30

Enter fullscreen mode Exit fullscreen mode

The SQL becomes:

SELECT *
FROM transactions
WHERE created_at < '2026-07-31T16:06:30'
ORDER BY created_at DESC
LIMIT 10;

Enter fullscreen mode Exit fullscreen mode

Notice something.

The database isn't skipping millions of rows.

It's jumping directly to the next location using an indexed column.

That's significantly faster.

Production APIs usually Fetch One Extra Record

One interesting trick used in production systems is:

Instead of fetching:

LIMIT 10

The backend fetches:

LIMIT 11

Why?

To determine whether another page exists.

If 11 records are returned:

  • Return only the first 10
  • Use the 10th record as the cursor
  • Set has_next = true

If only 10 records are returned:

has_next = false
next_cursor = null

Enter fullscreen mode Exit fullscreen mode

The frontend now knows there are no more records to load.

Advantage of Cursor Pagination

→ Extremely fast on very large datasets

→ Doesn't become slower over time
Whether your table has:

  • 10,000 rows
  • 10 million rows
  • 500 million rows Performance remains consistent.

→ Prevent duplicate and skipped records

Since the cursor always continues from the last item returned, new inserts don't shift pages in the same way they do with offsets.

FastApi code example 

@transaction_router.get(
    "/get_transactions", status_code=status.HTTP_200_OK
)
async def get_transactions(
        limit: Optional[int] = Query(10, ge=1, le=100),
        cursor: Optional[datetime] = Query(None),
        db: Session = Depends(get_db)
):
    limit_offset = limit + 1
    transactions = db.query(TransactionHistory)
    if cursor:
        transactions = transactions.filter(TransactionHistory.created_at < cursor)

    transactions = transactions.order_by(TransactionHistory.created_at.desc()).limit(limit_offset).all()

    if len(transactions) > limit:
        has_next = True
        transactions = transactions[:-1]
        next_cursor = transactions[-1].created_at
    else:
        has_next = False
        next_cursor = None

    return return_response(
        StatRes.SUCCESS,
        "Transactions fetched successfully",
        {
            "transactions": [TransactionResponse.model_validate(t) for t in transactions],
            "next_cursor": next_cursor,
            "has_next":has_next
        }
    )

Enter fullscreen mode Exit fullscreen mode

The Downside

Cursor pagination has one major limitation.

You cannot jump directly to page 75.

The client only knows:

"Give me the next records."

This makes cursor pagination perfect for:

  • Infinite scrolling
  • "Load More" buttons
  • Mobile applications
  • Social media feeds
  • Banking transaction history
  • Notifications
  • Activity logs

So Which One Should You Use?
The answer is...

It depends on how your users consume the data.

Use Offset Pagination when:

  • Building admin dashboards
  • Users need page numbers
  • Users jump between pages
  • Total pages and total records are required

Examples:

  • User management
  • Product inventory
  • Employee records
  • Reports
  • Admin portals

Use Cursor Pagination when:

  • Building APIs with huge datasets
  • Users simply scroll
  • Performance is critical
  • Consistency is important
  • New records are constantly being inserted

Examples:

  • Banking transaction history
  • Noitifications
  • Chat messages
  • Social media feeds
  • Activity logs
  • Mobile applications

Can Both Exists in the Same System?

Absolutely.

This is something many engineers overlook.
Imagine you're building a fintech platform.

Admin Dashboard

The operations team wants:

  • Page numbers
  • Total records
  • Jump to page 18 Offset pagination is the better choice.

Mobile App
Customers simply scroll through transaction history.
They don't care about page 23.
They only want:

"Show me more"

Cursor pagination is the better choice.

The same backend can expose different endpoints or different pagination modes - for different clients.

Final Thoughts

One of the biggest lessons I've learned as a backend engineer is that good API design isn't just about making things work, it's about making them scale.

Offset pagination isn't bad.

Cursor pagination isn't automatically better.
Each solves a different problem.

The best engineers don't ask:

"Which pagination strategy is the best?"

They ask:

"Which pagination strategy best fits how my users interact with this data?"

That shift in thinking is what separates writing APIs that work today from designing APIs that continue to perform as your product grows.

How do you handle pagination in your projects?

Do you stick with offset pagination, prefer cursor pagination, or support both depending on the use case?

I'd love to hear your experience in the comments.