A Developer's Guide to API Pagination: Offset vs. Cursor-Based

You've just built a shiny new feature that lists user transactions. In testing, it runs flawlessly: fast responses, clean UI, no complaints. Then your biggest client joins with 50,000 records. Suddenly, that endpoint stalls and times out, and support tickets start piling up.

This is a classic pagination problem. Instead of trying to load an entire data set at once, pagination breaks it into smaller, more manageable chunks (or pages) that can be fetched incrementally. Your bank does this; it shows around ten recent transactions at a time, not your entire account history. There's a good reason for this: research shows that even a one-second delay in page load can reduce conversions by 7 percent. When you're dealing with payroll systems, financial data, or anything time sensitive, those delays translate to missed deadlines, compliance headaches, and frustrated customers.

In this article, you'll explore two common approaches to pagination: offset-based and cursor-based. You'll learn the trade-offs of each and how to implement pagination in the real world using the Gusto Embedded Payroll API.

Compare Offset vs. Cursor-Based API Pagination: Which Method to Choose

API pagination controls how much data flows between a client and a server in each request. Instead of returning an entire data set at once, the API sends back a smaller subset, along with details on how to fetch the next one. These details can include the total number of items, page numbers, or a cursor marking where to continue. By fetching data in steps, pagination keeps applications fast and responsive while preventing large data sets from overloading the server or client.

Understand Offset Pagination

Offset pagination is one of the simplest and most common ways to paginate API results. You specify an offset (how many records to skip from the start of the data set) and a limit (how many records to return) while fetching data. This method works much like saying, "Skip the first fifty records and show me the next twenty." It's easy to understand and implement, which is why many beginner-friendly APIs and SQL queries use it.

Learn How Offset Pagination Works

The client specifies two parameters: offset and limit (or page_size). Here's what a typical API request looks like:

GET /api/transactions?limit=20&offset=40

This translates to "skip the first forty records and give me the next twenty," essentially fetching page 3 if each page contains twenty items. On the backend, this maps directly to an SQL query:

-- Fetch 20 records starting from the 41st record
SELECT * FROM transactions
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

The response typically includes metadata to help clients navigate:

{
  "data": [...],
  "pagination": {
    "limit": 20,
    "offset": 40,
    "total": 5000
  }
}

With the total count, clients can calculate exactly how many pages exist and build traditional page number navigation (page 1, 2, 3 … 250).

Examine the Benefits and Drawbacks of Offset Pagination

Offset pagination is simple and easy to implement. Most object-relational mappings (ORMs) and database libraries support LIMIT/OFFSET out of the box, and the math involved is intuitive: offset = (page_number - 1) * page_size. Users can jump to any page, bookmark specific pages, or navigate backward and forward freely. For small to medium data sets (a few thousand records), performance is good (queries produce results quickly), and the user experience matches what people expect from traditional web pagination.

The performance problems for offset pagination show up at scale. As the offset grows, so does the query cost. A request like OFFSET 10000 forces the database to scan and discard 10,000 rows before returning results. Fetching page 1 can take 10 milliseconds, while page 1000 can take several seconds on the same data set.

There's also the shifting data problem. Say a user is on page 5 of transaction records. While they're browsing, three new transactions are added at the top. When they click Next, the offset advances, but so does the data. Now they either see duplicate records or skip some entirely (phantom records). This shifting-records issue makes offset pagination unreliable for real-time or frequently changing data.

Then there's the total issue. Running SELECT COUNT(*) FROM transactions adds more overhead. On large tables, counting millions of rows is expensive and only gets slower as data grows. Some APIs skip this entirely and lose the ability to show total page numbers, while others cache the count and accept stale numbers.

Know When to Use Offset Pagination

Despite its limitations, offset pagination works well for specific use cases: admin dashboards with mostly static data, search results where users rarely go past the first few pages, or any small data set (under ~10,000 records). It's also helpful when users expect traditional page number navigation or need to share links to specific pages.

For large, fast-changing data sets or high-traffic applications where speed matters, cursor-based pagination is usually a better fit.

Understand Cursor-Based Pagination

Instead of counting rows from the beginning each time, cursor-based pagination (also called keyset pagination) uses a pointer (the cursor) that marks your current position in the data set. This cursor is like a bookmark pointing to a specific row in your data set.

Learn How Cursor-Based Pagination Works

Rather than asking, "skip forty records and give me twenty," cursor pagination specifies, "give me twenty records starting after this specific marker." The cursor is typically an encoded reference to the last item you received: often a combination of the record's ID and timestamp, or a unique identifier that the database can use to locate the next batch.

Here's what a typical API request looks like:

GET /api/transactions?limit=20&cursor=eyJpZCI6MTIzNDUsInRzIjoiMjAyNC0wMS0xNVQxMDowMDowMFoifQ==

The cursor value is usually Base64-encoded to obscure internal implementation details and prevent clients from manually constructing invalid cursors. On the backend, this translates to a query like this:

-- Fetch 20 records after the given marker
SELECT * FROM transactions
WHERE (created_at, id) < ('2025-10-15 10:00:00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;

In this WHERE clause, instead of skipping rows with OFFSET, you use a filter condition that the database can optimize with appropriate indexes. The response may look like this:

{
  "data": [
    {
      "id": 12344,
      "amount": 1500.00,
      "created_at": "2025-10-15T09:58:30Z"
    },
    // ... 19 more records
  ],
  "pagination": {
    "next_cursor": "eyJpZCI6MTIzMjUsInRzIjoiMjAyNC0wMS0xNVQwODowMDowMFoifQ==",
    "prev_cursor": "eyJpZCI6MTIzNDQsInRzIjoiMjAyNC0wMS0xNVQwOTo1ODozMFoifQ==",
    "has_more": true
  }
}

Here, next_cursor and prev_cursor act as pointers to navigate the records.

Examine the Benefits and Drawbacks of Cursor-Based Pagination

Cursor-based pagination delivers consistent performance regardless of how deep you paginate. Whether you're fetching the first page or the ten-thousandth, the query cost remains constant (time complexity: O(1)) because you're always using indexed filters rather than scanning and discarding rows. This makes it ideal for large data sets where offset pagination can grind to a halt.

Cursors also solve the shifting data problem. Cursors track specific records, not positions, so new entries don't cause duplicates or skipped items. If new transactions are inserted while a user is paginating, they don't see duplicates or skip records; the cursor maintains its position relative to the data itself, not relative to an arbitrary row count. This reliability makes cursor pagination ideal for real-time feeds, activity streams, or any constantly changing data set.

From an architectural standpoint, cursors scale better as well. You don't need expensive COUNT(*) queries for the total count. The database can efficiently use composite indexes on your sorting columns, and the queries remain fast even as your data set grows into millions of records.

That said, cursor-based pagination adds complexity. You need to handle cursor encoding and decoding, build proper composite queries, and ensure your indexes support your filtering strategy. For developers new to API design, encoded cursor tokens are less intuitive than page numbers.

Users also lose the ability to jump around. Navigation is typically forward (and sometimes backward), but skipping directly to page 50 isn't possible. This makes cursor pagination less practical when accessing random pages or when bookmarking specific pages is required. The UI often shifts to Load More buttons or infinite scroll.

One more thing to consider is that cursors can become invalid if records are deleted or if your API enforces time-based expiration. This prevents users from fetching stale or invalid data, but it means APIs need to issue fresh cursors with each response and may return an error, prompting the client to restart pagination from the latest position.

Know When to Use Cursor-Based Pagination

Cursor-based pagination is ideal for large, fast-changing data sets, like social media feeds, real-time events, chat histories, or any API where data is constantly added. Choose it when working on a project where consistent performance at scale matters more than random page access or when building mobile apps and infinite-scroll interfaces where users move sequentially through content.

Analyze a Real-World Example: Gusto Embedded API Pagination

Gusto processes payroll and compliance data for thousands of companies, and relies on strategies that keep performance steady and data consistent, even with high-volume, real-time updates. Gusto Embedded is a great example of how production APIs can handle pagination at scale.

Implement the Dual Approach of Gusto Embedded

Gusto Embedded implements both offset-based and cursor-based pagination, depending on the endpoint's characteristics. For most collection endpoints (like fetching employees), Gusto uses offset-based pagination:

GET https://api.gusto.com/v1/companies/abc123/employees?page=2&per=5

Pagination metadata is sent via HTTP headers:

X-Page: 2
X-Total-Count: 47
X-Total-Pages: 10
X-Per-Page: 5

This works well for relatively stable data sets where users need total counts and page navigation.

For real-time endpoints, like the events API, Gusto uses cursor-based pagination using starting_after_uuid and limit:

GET https://api.gusto.com/v1/events?starting_after_uuid=10ac74e7-d6f0-46c0-9697-8ec77ab475ba&limit=5

The cursor is simply the UUID of the last record. The response includes a header indicating whether more data exists:

X-Has-Next-Page: true

When X-Has-Next-Page is true, the client extracts the UUID from the last record in the response and uses it as the starting_after_uuid for the next request. Here's a sample code:

import httpx
import asyncio

async def fetch_all_events(api_token: str):
    all_events = []
    cursor = None
    has_more = True
    base_url = "https://api.gusto.com/v1/events"

    headers = {
        "Authorization": "Bearer {api_token}",
        "X-Gusto-API-Version": "2024-03-01"
    }

    async with httpx.AsyncClient() as client:
        while has_more:
            params = {"limit": 10}
            if cursor:
                params["starting_after_uuid"] = cursor

            response = await client.get(base_url, headers=headers, params=params)
            response.raise_for_status()  # The caller must handle exceptions

            events = response.json()
            all_events.extend(events)

            has_more = response.headers.get("X-Has-Next-Page") == "true"
            if has_more and events:
                cursor = events[-1]["uuid"]

    return all_events

The payroll API’s cursor-based pagination keeps performance fast, regardless of the data set size. Using UUID-based cursors and indexed lookups, the database quickly finds the cursor record and retrieves the next batch, without counting or skipping rows.

It also prevents data inconsistencies. As new events are added from payroll runs or employee updates, clients don't see duplicates or miss records. New entries appear at the beginning of the stream but don't affect the cursor's position.

Explore How Developers Can Benefit

The Gusto Embedded design makes integration simple. The starting_after_uuid parameter is intuitive: you just pass the last record's UUID. No complex cursor encoding or decoding is required, and the X-Has-Next-Page header clearly signals when to stop, avoiding extra requests.

This approach also scales effortlessly. Whether a company has 10 employees or 10,000, or generates 50 events or 5,000 per day, pagination performance is predictable. Gusto Embedded uses offset pagination when data is stable and cursors when data changes frequently—showing how production APIs can stay both developer-friendly and performant.

Choose the Right Approach

Offset pagination is best for small data sets, prototypes, internal tools, or admin dashboards where speed of development matters. It works well when data rarely changes and users need traditional page numbers or bookmarking.

Cursor-based pagination is great for production applications, especially software-as-a-service (SaaS) platforms handling financial data, payroll, or real-time streams. It's ideal when data sets grow continuously, data consistency is critical (users can't miss or duplicate records), or users navigate sequentially, like in mobile apps with infinite scroll, activity feeds, or notifications.

Before you decide which approach is right for your use case, ask yourself these three questions:

• Can your pagination handle a ten-times growth without a rewrite? Offset often struggles with scale.
• Do you need strict data integrity, where missing or duplicated records can cause financial errors or shake user confidence? If so, cursor pagination has you covered.
• Is your data set small and relatively static? In that case, offset pagination suffices.

As a rule of thumb, offset works fine for small, rarely changing data sets, but when the stakes and data volume are high, cursor is the better choice.

Follow Best Practices for Pagination Implementation

• Tune your page size: Too small means more requests; too large slows responses. Find a number based on your own data that keeps performance and UX smooth.
• Plan for compatibility: When migrating from offset to cursor pagination, support both temporarily or use API versioning with clear deprecation timelines.
• Choose cursors wisely: Pick indexed, immutable, unique fields (like timestamp + ID combo) or UUIDs, if they're your primary identifiers.
• Handle errors gracefully: If a cursor becomes invalid, return clear errors (400 Bad Request or 410 Gone) and prompt users to restart pagination.
• Document thoroughly: Explain cursor expiration and end-of-dataset behavior, and include working code examples.

These best practices help your API stay fast, reliable, and developer-friendly, even as data and users grow.

Conclusion

Offset pagination works well for small data sets or quick prototypes. It's straightforward to implement and easy to understand. However, as your data grows, you'll run into performance issues and consistency problems with shifting records.

Cursor-based pagination handles scale better. It uses unique position markers instead of offsets, which means no skipped or duplicate records when data changes, and query performance stays consistent even with millions of rows.

If you're building APIs that need to handle growth without sacrificing data consistency, cursor pagination is worth the extra effort. The Gusto approach shows how this works in practice—using the right pagination strategy for each endpoint based on how the data behaves.