TL;DR
For large datasets, use cursor-based or keyset pagination instead of offset-based pagination. Offset pagination (?page=1&limit=20) performs poorly with millions of records and allows data inconsistency. Modern PetstoreAPI implements cursor-based pagination with opaque tokens and HATEOAS links for efficient, consistent results.
Introduction
Your API returns a list of pets. You have 10 million pets in the database. A client requests GET /pets?page=500000&limit=20. Your database executes OFFSET 10000000 LIMIT 20. The query takes 30 seconds. Your API times out.
This is the offset pagination problem. It works fine for small datasets but breaks at scale. The database must scan millions of rows to reach the offset, even though you only return 20 results.
The old Swagger Petstore doesn’t address pagination at all. Modern PetstoreAPI implements cursor-based pagination that scales to millions of records with consistent performance.
In this guide, you’ll learn why offset pagination fails, how cursor-based pagination works, and how Modern PetstoreAPI implements efficient pagination.
Why Offset Pagination Fails at Scale
Offset pagination is the most common approach, but it has serious problems.
How Offset Pagination Works
GET /pets?page=1&limit=20 → OFFSET 0 LIMIT 20
GET /pets?page=2&limit=20 → OFFSET 20 LIMIT 20
GET /pets?page=3&limit=20 → OFFSET 40 LIMIT 20
The database skips offset rows and returns limit rows.
Problem 1: Performance Degrades with Page Number
Page 1:
SELECT * FROM pets OFFSET 0 LIMIT 20;
-- Fast: scans 20 rows
Page 1000:
SELECT * FROM pets OFFSET 20000 LIMIT 20;
-- Slow: scans 20,020 rows, returns 20
Page 500,000:
SELECT * FROM pets OFFSET 10000000 LIMIT 20;
-- Very slow: scans 10,000,020 rows, returns 20
The database must scan all rows up to the offset, even though it discards them. Performance degrades linearly with page number.
Problem 2: Inconsistent Results
While a client pages through results, data changes:
Request 1:
GET /pets?page=1&limit=2
Returns: [Pet A, Pet B]
Someone adds Pet Z (sorts first alphabetically)
Request 2:
GET /pets?page=2&limit=2
Returns: [Pet B, Pet C] ← Pet B appears twice!
Pet B appeared on both pages because a new pet was inserted. Conversely, pets can be skipped if deletions occur.
Problem 3: Deep Pagination Is Expensive
Users rarely go beyond page 10. But if your API allows ?page=1000000, you must handle it. Deep pagination queries are expensive and can be used for denial-of-service attacks.
When Offset Pagination Is Acceptable
Offset pagination works fine for:
- Small datasets (< 10,000 records)
- Internal APIs with controlled usage
- Admin interfaces where users won’t deep-page
- Data that changes infrequently
For public APIs or large datasets, use cursor-based pagination.
Cursor-Based Pagination Explained
Cursor-based pagination uses an opaque token to mark position in the result set.
How It Works
Request 1:
GET /pets?limit=20
Response 1:
{
"data": [...],
"pagination": {
"nextCursor": "eyJpZCI6IjAxOWI0MTMyLTcwYWEtNzY0Zi1iMzE1LWUyODAzZDg4MmEyNCJ9",
"hasMore": true
}
}
Request 2:
GET /pets?cursor=eyJpZCI6IjAxOWI0MTMyLTcwYWEtNzY0Zi1iMzE1LWUyODAzZDg4MmEyNCJ9&limit=20
The cursor is an opaque token (usually base64-encoded) that encodes the position. The client doesn’t parse it—just passes it back.
Benefits
1. Consistent Performance
The database uses an index to find the cursor position directly:
SELECT * FROM pets
WHERE id > '019b4132-70aa-764f-b315-e2803d882a24'
ORDER BY id
LIMIT 20;
This query is fast regardless of position in the dataset. It uses an index seek, not a scan.
2. Consistent Results
Cursors are stable. If data changes between requests, you still get consistent results. New records don’t cause duplicates or skips.
3. No Deep Pagination Attacks
Clients can’t jump to arbitrary positions. They must page sequentially, which limits abuse.
Cursor Format
Cursors are typically base64-encoded JSON:
// Decoded cursor
{
"id": "019b4132-70aa-764f-b315-e2803d882a24",
"createdAt": "2026-03-13T10:30:00Z"
}
The cursor contains enough information to resume pagination. For Modern PetstoreAPI, this includes the resource ID and sort field.
Keyset Pagination for Sorted Data
Keyset pagination is a variant of cursor-based pagination for sorted data.
How It Works
Instead of an opaque cursor, you use the last value from the previous page:
Request 1:
GET /pets?limit=20&sortBy=createdAt
Response 1:
{
"data": [
{"id": "...", "createdAt": "2026-03-13T10:00:00Z"},
...
{"id": "...", "createdAt": "2026-03-13T10:30:00Z"}
]
}
Request 2:
GET /pets?limit=20&sortBy=createdAt&after=2026-03-13T10:30:00Z
The after parameter uses the last createdAt value from the previous page.
SQL Query
SELECT * FROM pets
WHERE created_at > '2026-03-13T10:30:00Z'
ORDER BY created_at
LIMIT 20;
This is efficient because it uses an index on created_at.
When to Use Keyset Pagination
- Data is naturally sorted (by timestamp, ID, etc.)
- Clients need to understand the pagination key
- You want transparent pagination (not opaque cursors)
Modern PetstoreAPI uses cursor-based pagination by default but supports keyset pagination for time-series data.
How Modern PetstoreAPI Implements Pagination
Modern PetstoreAPI uses cursor-based pagination with HATEOAS links.
Request Format
GET /pets?limit=20
GET /pets?cursor={token}&limit=20
Parameters:
limit- Number of results per page (default: 20, max: 100)cursor- Opaque pagination token from previous response
Response Format
{
"data": [
{
"id": "019b4132-70aa-764f-b315-e2803d882a24",
"name": "Fluffy",
"species": "CAT"
}
],
"pagination": {
"limit": 20,
"hasMore": true,
"nextCursor": "eyJpZCI6IjAxOWI0MTMyLTcwYWEtNzY0Zi1iMzE1LWUyODAzZDg4MmEyNCJ9"
},
"links": {
"self": "https://petstoreapi.com/pets?limit=20",
"next": "https://petstoreapi.com/pets?cursor=eyJpZCI6IjAxOWI0MTMyLTcwYWEtNzY0Zi1iMzE1LWUyODAzZDg4MmEyNCJ9&limit=20"
}
}
Key Features
1. Opaque Cursors
Cursors are base64-encoded. Clients don’t parse them.
2. HATEOAS Links
The links object provides ready-to-use URLs. Clients don’t need to construct pagination URLs.
3. hasMore Flag
Indicates whether more results exist. Clients know when to stop paging.
4. Limit Validation
Maximum limit is 100. Prevents clients from requesting huge pages.
See the Modern PetstoreAPI pagination documentation for complete details.
Pagination Response Format
Modern PetstoreAPI wraps paginated responses in a consistent structure.
Collection Wrapper
{
"data": [...],
"pagination": {...},
"links": {...}
}
Why wrap collections?
- Extensibility - Can add metadata without breaking clients
- Consistency - All paginated endpoints use the same format
- HATEOAS - Links guide clients through pagination
Pagination Metadata
"pagination": {
"limit": 20,
"hasMore": true,
"nextCursor": "...",
"totalCount": 1000 // Optional, expensive to compute
}
totalCount is optional because computing it is expensive for large datasets. Most clients don’t need it.
Testing Pagination with Apidog
Apidog helps you test pagination behavior comprehensively.
Test Scenarios
1. First Page
GET /pets?limit=20
Expect: 20 results, hasMore=true, nextCursor present
2. Subsequent Pages
GET /pets?cursor={token}&limit=20
Expect: 20 results, hasMore=true/false, nextCursor present/absent
3. Last Page
GET /pets?cursor={lastToken}&limit=20
Expect: < 20 results, hasMore=false, no nextCursor
4. Empty Results
GET /pets?status=NONEXISTENT&limit=20
Expect: 0 results, hasMore=false, no nextCursor
5. Limit Validation
GET /pets?limit=1000
Expect: 400 Bad Request (exceeds max limit)
Apidog Test Configuration
// Test: Pagination structure
pm.test("Response has pagination", () => {
pm.expect(pm.response.json()).to.have.property('pagination');
pm.expect(pm.response.json().pagination).to.have.property('hasMore');
});
// Test: HATEOAS links
pm.test("Response has links", () => {
const links = pm.response.json().links;
pm.expect(links).to.have.property('self');
if (pm.response.json().pagination.hasMore) {
pm.expect(links).to.have.property('next');
}
});
Choosing the Right Pagination Strategy
Different strategies fit different use cases.
Offset Pagination
Use when:
- Dataset is small (< 10,000 records)
- Users need random access (jump to page 50)
- Data changes infrequently
- Internal API with controlled usage
Don’t use when:
- Dataset is large (> 100,000 records)
- Performance matters
- Data changes frequently
Cursor-Based Pagination
Use when:
- Dataset is large
- Performance matters
- Data changes frequently
- Sequential access is sufficient
Don’t use when:
- Users need random access
- Cursor complexity is a concern
Keyset Pagination
Use when:
- Data is naturally sorted
- Transparent pagination is preferred
- Performance matters
Don’t use when:
- Sort order is complex
- Multiple sort fields are needed
Modern PetstoreAPI recommendation: Use cursor-based pagination for public APIs and large datasets.
Conclusion
Pagination is critical for APIs that return large datasets. Offset pagination is simple but doesn’t scale. Cursor-based pagination provides consistent performance and reliable results for millions of records.
Modern PetstoreAPI implements cursor-based pagination with opaque tokens, HATEOAS links, and proper metadata. This design scales efficiently and provides a great developer experience.
Test your pagination implementation with Apidog to ensure it handles edge cases, validates limits, and returns consistent results.
Key takeaways:
- Avoid offset pagination for large datasets
- Use cursor-based pagination for scalability
- Wrap collections with metadata and links
- Test pagination thoroughly with Apidog
- Follow Modern PetstoreAPI’s pagination patterns
FAQ
Why not just return all results without pagination?
Returning millions of records in one response causes memory issues, slow network transfer, and poor user experience. Pagination is essential for large datasets.
Can clients jump to a specific page with cursor pagination?
No, cursor pagination requires sequential access. If random access is needed, consider offset pagination for small datasets or implement search/filtering instead.
How do I handle pagination with filtering?
Include filter parameters in pagination requests: GET /pets?status=AVAILABLE&cursor={token}&limit=20. The cursor encodes both position and filter state.
Should I include total count in pagination responses?
Only if clients need it and your dataset is small. Computing total count is expensive for large datasets (requires a separate COUNT query).
How do I implement cursor pagination in SQL?
Use a WHERE clause with the cursor value: SELECT * FROM pets WHERE id > ? ORDER BY id LIMIT 20. Ensure you have an index on the sort column.
What if my cursor tokens become invalid?
Return 400 Bad Request with an error message. Cursors can become invalid if data is deleted or the pagination state expires.
How long should cursors remain valid?
Modern PetstoreAPI cursors are valid indefinitely as long as the referenced resource exists. Some APIs expire cursors after 24 hours.
Can I use cursor pagination with multiple sort fields?
Yes, but the cursor must encode all sort fields. This makes cursors more complex. Consider using a single composite sort key instead.
