API Pagination: Cursor vs Offset

August 15, 2026 Khimananda Oli 8 min read Virtualization

By Khimananda Oli | Last reviewed: August 2026

Choosing the wrong pagination strategy is one of the most common causes of silent performance degradation in modern web applications. When you evaluate API Pagination: Cursor vs Offset, you are essentially deciding between simple implementation and predictable scalability under load. While offset-based pagination feels intuitive during development, it frequently becomes a bottleneck as datasets grow, whereas cursor-based approaches maintain constant read performance regardless of dataset size. Understanding this trade-off early prevents costly refactors later, especially when designing systems that must handle high traffic or comply with strict latency SLOs discussed in our guide to defining meaningful SLIs and SLOs.

Quick answer: For API Pagination: Cursor vs Offset, use cursor-based (keyset) pagination for production systems requiring stable performance on large datasets or real-time feeds. Reserve offset pagination only for small admin dashboards or static datasets where deep jumping is required and data volume remains low.

Visualizing API Pagination: Cursor vs Offset reveals why offset degrades linearly while cursors maintain constant time complexity via index seeks.

How does API Pagination: Cursor vs Offset impact database performance?

The fundamental difference lies in how the database engine locates the starting row for each page. Offset pagination relies on positional logic: "skip X rows, then take Y." The database must physically or logically traverse every skipped row before returning results. As the offset increases, the work increases proportionally. Requesting page 1,000 with 50 items per page forces the engine to process 50,000 rows just to return 50. This behavior is inherent to SQL standards and affects PostgreSQL, MySQL, MariaDB, and SQL Server similarly. For teams managing database health, understanding this cost is as critical as the tuning strategies covered in our MySQL performance tuning guide.

Cursor-based pagination, often called keyset pagination, replaces position with value. Instead of skipping rows, you ask for "rows where ID > last_seen_id, limit Y." Because IDs are typically indexed primary keys, the database performs a B-tree seek directly to the anchor point. The cost of finding row 50 or row 5,000,000 is identical. This predictability makes cursor pagination essential for high-traffic APIs where p99 latency must remain stable. In my experience auditing SOC 2 compliant systems, inconsistent query performance from deep offsets frequently triggers availability incidents during peak loads.

Why offset causes cache invalidation issues

Beyond raw query cost, offset pagination fights against caching layers. If a new record is inserted at the top of the list, every subsequent page shifts. A user requesting page 2 after an insertion receives different results than before, potentially duplicating or missing items. This volatility makes HTTP caching ineffective because the cache key (page number) no longer maps to stable content. Cursor pagination avoids this entirely; the cursor encodes state, not position. Even if new data arrives, the cursor still points to the exact same logical location in the sorted set, preserving cache validity and user experience consistency.

When should you implement cursor-based pagination over offset?

You should default to cursor-based pagination for any public-facing API, infinite scroll interface, real-time feed, or dataset exceeding 100,000 rows. It is also mandatory when your sorting criteria involve non-unique columns combined with frequent writes, as offset drift becomes severe. Mobile applications almost always require cursors because users expect seamless scrolling without jumps or duplicates. If your system serves global audiences, including regions with variable network conditions like Nepal, reducing payload variability through stable cursors improves perceived performance significantly.

Real-time feeds: Social timelines, notification streams, and chat histories where new items appear continuously.
Large datasets: E-commerce catalogs, audit logs, or transaction records exceeding millions of rows.
Mobile interfaces: Infinite scroll patterns where users cannot jump to arbitrary pages anyway.
High-write environments: Systems where inserts/deletes happen faster than users paginate, making offset unreliable.
Compliance-sensitive data: Audit trails where missing or duplicating records due to offset shift violates integrity requirements.

Sequence diagram illustrating the secure cursor encoding and decoding workflow required for robust API Pagination: Cursor vs Offset implementations.

Handling composite sort keys safely

A common mistake is using only the primary key as a cursor when results are sorted by another column like created_at. If multiple rows share the same timestamp, pagination breaks. Always include a unique tiebreaker in your cursor. Encode both the sort column and the primary key: (created_at, id). Your query then becomes WHERE (created_at, id) > (?, ?). Most modern databases optimize this tuple comparison efficiently. Never expose raw database values directly in URLs; encode cursors as opaque Base64 strings to prevent tampering and simplify future schema changes. This aligns with security best practices for API design, similar to secrets management principles in Kubernetes secrets management.

What are the practical trade-offs between cursor and offset pagination?

While cursor pagination wins on performance, it introduces complexity that offset avoids. You lose the ability to jump to "page 47" directly because cursors are sequential anchors, not page numbers. This makes traditional numbered pagination UIs impractical. Implementing bidirectional navigation (previous/next) requires maintaining two cursors or reversible encoding logic. Debugging is harder; an opaque cursor hides state, making ad-hoc investigation difficult without decoding tools. Development time increases initially, though libraries like Relay, Django REST Framework, and Laravel Scout now abstract much of this boilerplate.

Criteria	Offset Pagination	Cursor Pagination
Performance at Scale	Degrades linearly O(N)	Constant time O(1)
Deep Page Access	Supported natively	Not supported (sequential only)
Consistency During Writes	Prone to skips/duplicates	Stable anchor point
Implementation Complexity	Trivial (LIMIT/OFFSET)	Moderate (encoding, composite keys)
Caching Efficiency	Poor (volatile pages)	Excellent (stable keys)
Total Count Availability	Easy (COUNT(*))	Expensive or approximate
Best Use Case	Admin panels, small static sets	Feeds, mobile, large public APIs

The total count problem deserves special attention. Clients often want "showing 1-20 of 5,432 results." With offset, COUNT(*) is cheap on small tables but expensive on massive ones. With cursors, getting an exact count requires scanning the entire table anyway, defeating the purpose. In practice, I recommend returning approximate counts (pg_class.reltuples in PostgreSQL) or omitting totals entirely for infinite-scroll interfaces. Users rarely need exact counts beyond the first few pages; they need fast, reliable next-page loading.

How do you migrate an existing offset API to cursor pagination safely?

Migrating live APIs requires backward compatibility. Never break existing clients. Start by adding cursor parameters alongside existing offset params. Document the new approach clearly. Monitor adoption via metrics; track which clients use cursors versus offsets. Once critical mass migrates, deprecate offset with sunset headers. During transition, support both modes but log warnings for offset usage on large datasets. Ensure your monitoring stack captures pagination latency separately; tools like Prometheus can histogram query duration by pagination type, helping prove the migration's value. Refer to our Prometheus metrics fundamentals guide for instrumentation patterns.

Add cursor support non-breaking: Accept ?cursor= alongside ?page=&per_page=. Default to offset if cursor absent.
Implement opaque encoding: Serialize {sort_value, id} as URL-safe Base64. Validate strictly; reject malformed cursors with 400.
Update client SDKs: Release updated libraries that prefer cursors automatically. Provide migration guides with code samples.
Instrument dual-mode metrics: Tag requests by pagination method. Compare p95 latency and error rates side-by-side.
Communicate deprecation timeline: Use RFC 8594 Sunset header. Notify enterprise customers directly. Keep offset functional read-only during grace period.

Decision framework for selecting API Pagination: Cursor vs Offset based on dataset scale, access patterns, and write frequency.

Secure Your Pagination Implementation From Day One

Pagination choices made today define your system's operational ceiling for years. Prioritize cursor-based pagination for any endpoint expecting growth, real-time updates, or mobile consumption. Reserve offset strictly for bounded administrative interfaces where simplicity outweighs scale concerns. Always encode cursors opaquely, validate inputs rigorously, and instrument latency differences to justify architectural decisions with data. If your current offset-based API shows p99 degradation beyond acceptable thresholds, begin planning your migration now rather than waiting for the next incident. For teams needing hands-on guidance implementing scalable pagination or optimizing database-backed APIs, reach out via contact me to discuss your specific architecture.

Frequently Asked Questions

Offset uses numeric page numbers while cursors use opaque tokens pointing to specific records. Cursors prevent duplicate or missing results during concurrent writes, whereas offset pagination suffers from data drift when rows are inserted or deleted between requests.

Use offset when users need direct access to arbitrary pages or total record counts. It suits admin dashboards or small datasets under ten thousand rows where performance degradation is acceptable and stable ordering is guaranteed without frequent concurrent modifications.

Databases must scan and discard all preceding rows for high offsets, causing O(n) latency. Querying page one million requires processing a million rows before returning results, leading to timeouts and excessive CPU usage compared to constant-time cursor lookups using indexed columns.

Use Eloquent's cursorPaginate method which automatically generates opaque cursors based on ordered columns. Define consistent sort orders including unique tie-breakers like id, and expose next_cursor and prev_cursor metadata in API responses for stateless client navigation.

No, cursors cannot efficiently calculate totals without scanning entire tables. Run a separate cached count query if needed, but consider whether clients truly require exact totals versus approximate estimates or infinite scroll patterns that avoid expensive aggregation operations entirely.

Yes, cursors are typically base64-encoded column values, not secrets. However, always validate and sanitize decoded parameters server-side to prevent SQL injection. Never include sensitive data in cursors since clients can decode them; use signed tokens if tampering is a concern.

Include filter criteria in both the initial query and cursor decoding logic. The cursor must encode enough context to reconstruct filtered result sets consistently. Rebuild cursors whenever filters change, as stale tokens may return incorrect or unauthorized records across different filter combinations.

Choose monotonically increasing, unique, indexed columns like auto-increment IDs or created_at timestamps paired with IDs for tie-breaking. Avoid mutable fields that change frequently, as updated values invalidate existing cursors and cause clients to skip or duplicate records unexpectedly during traversal.

No, cursors only allow sequential forward or backward traversal. Clients cannot request page fifty directly without iterating through previous pages. If random access is required, implement hybrid approaches or accept offset pagination trade-offs for those specific endpoints.

Verify sort order includes unique tie-breakers and remains identical across requests. Check that cursor encoding matches decoding logic exactly. Inspect database indexes on cursor columns and confirm no concurrent schema changes altered column types or ordering behavior between deployments.

Yes, the Relay connection specification standardizes cursor pagination with edges, nodes, and pageInfo fields. Most GraphQL frameworks provide built-in connection resolvers that handle cursor encoding, slicing, and metadata generation automatically following established conventions for interoperable client consumption.

Offset cache keys depend on page numbers that shift with data changes, causing frequent invalidation. Cursor cache keys remain stable since they reference absolute positions. Cache individual cursor pages aggressively, but invalidate when underlying data mutates to prevent serving stale result sets.

Unbounded limit parameters enable denial-of-service attacks. Missing rate limiting allows enumeration scraping. Unsigned cursors permit parameter tampering to access unauthorized records. Always enforce maximum page sizes, validate cursor integrity, apply row-level authorization checks after pagination, and monitor for abnormal traversal patterns.

Yes, version the endpoint and run both strategies temporarily. Map legacy page numbers to approximate cursor positions for backward compatibility. Communicate breaking changes clearly since clients lose random access capability. Deprecate offset parameters gradually while monitoring adoption metrics and error rates.

Cursor pagination significantly lowers costs by eliminating full table scans for deep pages. Reduced CPU and I/O translate directly to lower compute bills on managed databases like Aurora or Cloud SQL. Offset pagination at scale requires larger instances or read replicas to maintain acceptable response times.