{"id":1003,"date":"2025-11-21T13:59:25","date_gmt":"2025-11-21T21:59:25","guid":{"rendered":"https:\/\/embedded.gusto.com\/blog\/?p=1003"},"modified":"2025-11-20T13:59:39","modified_gmt":"2025-11-20T21:59:39","slug":"api-pagination","status":"publish","type":"post","link":"https:\/\/embedded.gusto.com\/blog\/api-pagination\/","title":{"rendered":"A Developer’s Guide to API Pagination: Offset vs. Cursor-Based"},"content":{"rendered":"

A Developer\u2019s Guide to API Pagination: Offset vs. Cursor-Based<\/h1>\n

You\u2019ve 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.<\/p>\n

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\u2019s a good reason for this: research shows that even a one-second delay in page load can reduce conversions by 7 percent<\/a>. When you\u2019re dealing with payroll systems, financial data, or anything time sensitive, those delays translate to missed deadlines, compliance headaches, and frustrated customers.<\/p>\n

In this article, you\u2019ll explore two common approaches to pagination: offset-based<\/em> and cursor-based<\/em>. You\u2019ll learn the trade-offs of each and how to implement pagination in the real world using the Gusto Embedded Payroll API<\/a>.<\/p>\n

Compare Offset vs. Cursor-Based API Pagination: Which Method to Choose<\/h2>\n

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.<\/p>\n

Understand Offset Pagination<\/h3>\n

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

Learn How Offset Pagination Works<\/h4>\n

The client specifies two parameters: offset<\/code> and limit<\/code> (or page_size<\/code>). Here\u2019s what a typical API request looks like:<\/p>\n

\nGET \/api\/transactions?limit<span class="token operator">=<\/span>20<span class="token operator">&<\/span>offset<span class="token operator">=<\/span>40\n\n<\/code><\/pre>\n
This translates to \u201cskip the first forty records and give me the next twenty,\u201d essentially fetching page 3 if each page contains twenty items. On the backend, this maps directly to an SQL query:<\/p>\n
\n```sql\n-- Fetch 20 records starting from the 41st record\nSELECT * FROM transactions\nORDER BY created_at DESC\nLIMIT 20 OFFSET 40;\n```\n<\/code><\/pre>\n
The response typically includes metadata to help clients navigate:<\/p>\n
\n```json\n{\n  "data": [...],\n  "pagination": {\n    "limit": 20,\n    "offset": 40,\n    "total": 5000\n  }\n}\n```\n\n<\/code><\/pre>\n
With the total count, clients can calculate exactly how many pages exist and build traditional page number navigation (page 1, 2, 3 \u2026 250).<\/p>\n
Examine the Benefits and Drawbacks of Offset Pagination<\/h4>\n
Offset pagination is simple and easy to implement. Most object-relational mappings (ORMs) and database libraries support LIMIT<\/code>\/OFFSET<\/code> out of the box, and the math involved is intuitive: offset = (page_number - 1) * page_size<\/code>. 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.<\/p>\n
The performance problems for offset pagination show up at scale<\/a>. As the offset grows, so does the query cost. A request like OFFSET 10000<\/code> 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.<\/p>\n
There\u2019s also the shifting data problem. Say a user is on page 5 of transaction records. While they\u2019re browsing, three new transactions are added at the top. When they click Next<\/strong>, 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.<\/p>\n
Then there\u2019s the total issue. Running SELECT COUNT(*) FROM transactions<\/code> 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.<\/p>\n
Know When to Use Offset Pagination<\/h4>\n
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\u2019s also helpful when users expect traditional page number navigation or need to share links to specific pages.<\/p>\n
For large, fast-changing data sets or high-traffic applications where speed matters, cursor-based pagination is usually a better fit.<\/p>\n
Understand Cursor-Based Pagination<\/h3>\n
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.<\/p>\n
Learn How Cursor-Based Pagination Works<\/h4>\n
Rather than asking, \u201cskip forty records and give me twenty,\u201d cursor pagination specifies, \u201cgive me twenty records starting after this specific marker.\u201d The cursor is typically an encoded reference to the last item you received: often a combination of the record\u2019s ID and timestamp, or a unique identifier that the database can use to locate the next batch.<\/p>\n
Here\u2019s what a typical API request looks like:<\/p>\n
\n```bash\nGET \/api\/transactions?limit=20&cursor=eyJpZCI6MTIzNDUsInRzIjoiMjAyNC0wMS0xNVQxMDowMDowMFoifQ==\n```\n<\/code><\/pre>\n
The cursor value is usually Base64-encoded<\/a> to obscure internal implementation details and prevent clients from manually constructing invalid cursors. On the backend, this translates to a query like this:<\/p>\n
\n```sql\n-- Fetch 20 records after the given marker\nSELECT * FROM transactions\nWHERE (created_at, id) < ('2025-10-15 10:00:00', 12345)\nORDER BY created_at DESC, id DESC\nLIMIT 20;\n```\n<\/code><\/pre>\n
In this WHERE<\/code> clause, instead of skipping rows with OFFSET<\/code>, you use a filter condition that the database can optimize with appropriate indexes. The response may look like this:<\/p>\n
\n```json\n{\n  "data": [\n    {\n      "id": 12344,\n      "amount": 1500.00,\n      "created_at": "2025-10-15T09:58:30Z"\n    },\n    \/\/ ... 19 more records\n  ],\n  "pagination": {\n    "next_cursor": "eyJpZCI6MTIzMjUsInRzIjoiMjAyNC0wMS0xNVQwODowMDowMFoifQ==",\n    "prev_cursor": "eyJpZCI6MTIzNDQsInRzIjoiMjAyNC0wMS0xNVQwOTo1ODozMFoifQ==",\n    "has_more": true\n  }\n}\n```\n<\/code><\/pre>\n
Here, next_cursor<\/code> and prev_cursor<\/code> act as pointers to navigate the records.<\/p>\n
Examine the Benefits and Drawbacks of Cursor-Based Pagination<\/h4>\n
Cursor-based pagination delivers consistent performance<\/a> regardless of how deep you paginate. Whether you\u2019re fetching the first page or the ten-thousandth, the query cost remains constant (time complexity: O(1)<\/a>) because you\u2019re 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.<\/p>\n
Cursors also solve the shifting data problem. Cursors track specific records, not positions, so new entries don\u2019t cause duplicates or skipped items. If new transactions are inserted while a user is paginating, they don\u2019t 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.<\/p>\n
From an architectural standpoint, cursors scale better as well. You don\u2019t need expensive COUNT(*)<\/code> 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.<\/p>\n
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.<\/p>\n
Users also lose the ability to jump around. Navigation is typically forward (and sometimes backward), but skipping directly to page 50 isn\u2019t 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<\/strong> buttons or infinite scroll.<\/p>\n
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.<\/p>\n
Know When to Use Cursor-Based Pagination<\/h4>\n
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.<\/p>\n
Analyze a Real-World Example: Gusto Embedded API Pagination<\/h2>\n
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.<\/p>\n
Implement the Dual Approach of Gusto Embedded<\/h3>\n
Gusto Embedded implements both offset-based and cursor-based pagination<\/a>, depending on the endpoint\u2019s characteristics. For most collection endpoints (like fetching employees), Gusto uses offset-based pagination:<\/p>\n
\n```bash\nGET https:\/\/api.gusto.com\/v1\/companies\/abc123\/employees?page=2&per=5\n```\n\nPagination metadata is sent via HTTP headers:\n\n```\nX-Page: 2\nX-Total-Count: 47\nX-Total-Pages: 10\nX-Per-Page: 5\n```\n<\/code><\/pre>\n
This works well for relatively stable data sets where users need total counts and page navigation.<\/p>\n
For real-time endpoints, like the events API, Gusto uses cursor-based pagination<\/a> using starting_after_uuid<\/code> and limit<\/code>:<\/p>\n
\n```bash\nGET https:\/\/api.gusto.com\/v1\/events?starting_after_uuid=10ac74e7-d6f0-46c0-9697-8ec77ab475ba&limit=5\n```\n<\/code><\/pre>\n
The cursor is simply the UUID of the last record. The response includes a header indicating whether more data exists:<\/p>\n
\nX-Has-Next-Page: true\n<\/code><\/pre>\n
When X-Has-Next-Page<\/code> is true<\/code>, the client extracts the UUID from the last record in the response and uses it as the starting_after_uuid<\/code> for the next request. Here\u2019s a sample code:<\/p>\n
\n```python\nimport httpx\nimport asyncio\n\nasync def fetch_all_events(api_token: str):\n    all_events = []\n    cursor = None\n    has_more = True\n    base_url = "https:\/\/api.gusto.com\/v1\/events"\n\n    headers = {\n        "Authorization": "Bearer {api_token}",\n        "X-Gusto-API-Version": "2024-03-01"\n    }\n\n    async with httpx.AsyncClient() as client:\n        while has_more:\n            params = {"limit": 10}\n            if cursor:\n                params["starting_after_uuid"] = cursor\n\n            response = await client.get(base_url, headers=headers, params=params)\n            response.raise_for_status()  # The caller must handle exceptions\n\n            events = response.json()\n            all_events.extend(events)\n\n            has_more = response.headers.get("X-Has-Next-Page") == "true"\n            if has_more and events:\n                cursor = events[-1]["uuid"]\n\n    return all_events\n```\n<\/code><\/pre>\n
The payroll API\u2019s 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.<\/p>\n
It also prevents data inconsistencies. As new events are added from payroll runs or employee updates, clients don\u2019t see duplicates or miss records. New entries appear at the beginning of the stream but don\u2019t affect the cursor\u2019s position.<\/p>\n
Explore How Developers Can Benefit<\/h3>\n
The Gusto Embedded design makes integration simple. The starting_after_uuid<\/code> parameter is intuitive: you just pass the last record\u2019s UUID. No complex cursor encoding or decoding is required, and the X-Has-Next-Page<\/code> header clearly signals when to stop, avoiding extra requests.<\/p>\n
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\u2014showing how production APIs can stay both developer-friendly and performant.<\/p>\n
Choose the Right Approach<\/h2>\n
Offset pagination<\/strong> 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.<\/p>\n
Cursor-based pagination<\/strong> is great for production applications, especially software-as-a-service (SaaS) platforms handling financial data, payroll, or real-time streams. It\u2019s ideal when data sets grow continuously, data consistency is critical (users can\u2019t miss or duplicate records), or users navigate sequentially, like in mobile apps with infinite scroll, activity feeds, or notifications.<\/p>\n
\"Offset<\/p>\n
Before you decide which approach is right for your use case, ask yourself these three questions:<\/p>\n