API Pagination: Fast and Predictable Choice

Choosing the right pagination strategy for your API keeps responses fast and predictable as your data grows. This guide walks you through the main options—offset, cursor, and keyset—and helps you pick based on your data size, mutation rate, and client needs. You’ll learn the trade-offs, implementation steps, and common pitfalls.

What You’ll Need

• A basic understanding of HTTP and REST or GraphQL APIs.
• Knowledge of your database query language (SQL, NoSQL, etc.).
• An idea of your data’s growth rate and how often rows are added or removed.

Step-by-Step

1. Assess your data’s mutation pattern. If your dataset is static or only grows slowly (e.g., a blog archive), offset pagination is simple. If rows are frequently added, removed, or reordered, cursor-based pagination is more stable.
2. Define your page size. Choose a default like 20 or 50 items per page, and allow clients to override it within a sane range (e.g., 1–100). Too large a page size causes slow queries and heavy payloads.
3. For offset pagination: Use LIMIT and OFFSET in SQL, or the equivalent in your data store. Return the total count if needed, but be aware that counting on every request is expensive for large tables. Example: GET /items?offset=40&limit=20.
4. For cursor pagination: Use an opaque cursor (a base64-encoded string) that encodes the last item’s sort key. For example, in SQL: SELECT * FROM items WHERE id > ? ORDER BY id ASC LIMIT 20, then return the last id as the next cursor. In the response, include next_cursor and has_more.
5. For keyset pagination: Similar to cursor, but the cursor is the raw sort key value (e.g., ?after=12345). This is simpler to debug but exposes your data’s structure. Use a unique, indexed column (like a timestamp or auto-increment ID) as the key.
6. Choose a stable sort order. Always sort by at least one unique column (e.g., id or created_at plus id). Without a unique tiebreaker, items may be skipped or duplicated when the sort key changes.
7. Implement the response format. Return a JSON object with data (the array of items), pagination (containing next_cursor, previous_cursor, has_more, and optionally total for offset).
8. Add validation. Reject invalid cursor values with a 400 error. For offset, cap the maximum offset to prevent deep-pagination performance issues (e.g., refuse offsets beyond 10,000).
9. Test with concurrent changes. Simulate rows being added or removed between requests to see how your pagination behaves. Offset will skip or duplicate items; cursor will not.
10. Document the behavior. Clearly state in your API docs whether the total count is included, how cursors expire (if they do), and what happens if the sort key changes.

Common Mistakes

• Using offset for large datasets: Deep offsets (e.g., page 1000) are slow because the database scans and discards many rows.
• Sorting by a non-unique column: If you sort only by created_at and two rows share the same timestamp, pagination can skip or repeat them.
• Returning a total count on every request: Counting millions of rows is expensive; consider omitting it or making it optional.
• Exposing raw database IDs as cursors: This leaks information and can be exploited. Use opaque, encoded cursors.
• Ignoring cursor expiry: If your data changes, a cursor may point to a deleted row. Decide whether to return an empty page or an error.

Tips

• Prefer cursor-based pagination for real-time data (feeds, messages, notifications) because it handles inserts and deletes gracefully.
• Use offset pagination for simple, admin-style lists where the dataset is small or static.
• Always index the columns used in the WHERE and ORDER BY clauses to keep queries fast.
• Consider using a composite cursor (e.g., created_at + id) for even more stability.
• Provide both next and previous cursors so clients can navigate both directions.
• Cache the total count if you must show it, and refresh it periodically rather than on every request.

Troubleshooting

• Pages are slow: Check that your sort and filter columns are indexed. For offset, consider switching to cursor.
• Items are skipped or duplicated: This usually means your sort key is not unique. Add a secondary unique column to the ORDER BY.
• Cursor becomes invalid: If the referenced row is deleted, you can either return an empty page or, better, use a cursor that encodes the sort key value (not the row ID) and filter with >=.
• Client sends a malformed cursor: Return a 400 with a clear error message. Avoid exposing internal stack traces.
• Total count is wrong: If you must return it, compute it with a separate COUNT query that uses the same filters, and cache it.

Frequently Asked Questions

What is the difference between cursor and keyset pagination?

Keyset pagination uses the raw sort key value (e.g., ?after=12345), while cursor pagination encodes that value (and sometimes additional info) into an opaque string. Both are stable under data changes, but cursor is more secure and flexible.

Is offset pagination ever a good choice?

Yes, for small, static datasets like a list of countries or a user’s own settings. It’s simpler to implement and allows jumping to a specific page number, which is useful for admin panels.

How do I handle a client that requests a page beyond the last one?

Return an empty data array with has_more: false. For offset, you can also return a 400 if the offset exceeds a maximum, but an empty result is more graceful.

Should I include a total count in cursor pagination?

It’s optional. If you include it, be aware it may be expensive and slightly stale. Many APIs omit it for performance, or provide it only when explicitly requested.

Can I use cursor pagination with a sort order that isn’t by ID?

Yes, as long as the sort key is unique and indexed. For example, sort by created_at and id to break ties. The cursor should encode both values.

What should the cursor look like?

It should be an opaque string, often base64-encoded JSON, like eyJpZCI6MTIzNDV9. Clients should treat it as an opaque token and not attempt to decode or modify it.

Related

• How Long Do iPhones Last Before You Need to Replace One?
• Replacement Fuse Compatibility: What to Check
• Voltage Calculator for Parallel Circuits