API Pagination Cursor vs Offset Deep Dive

By Khimananda Oli | Last reviewed: August 2026

Choosing between cursor-based and offset pagination is one of the most consequential decisions you make when designing a data-heavy API. A wrong choice leads to slow queries at scale, inconsistent user experiences during concurrent writes, or unnecessary infrastructure costs. This API Pagination Cursor vs Offset Deep Dive breaks down the mechanical differences, performance characteristics, and implementation trade-offs so you can select the right strategy for your specific workload. For teams managing high-throughput datasets, understanding these fundamentals is as critical as mastering MySQL performance tuning or proper indexing strategies.

How does offset pagination impact database performance at scale?

Offset pagination is the default mental model for most developers because it maps directly to human navigation: "show me page 5." In SQL, this translates to LIMIT 20 OFFSET 80. For small tables or admin dashboards with limited traffic, this works perfectly. However, as your dataset grows into the millions of rows, the OFFSET clause becomes a significant bottleneck.

The mechanics of the offset penalty

Databases do not have a magical pointer to row number 10,000. When you execute OFFSET 10000 LIMIT 20, the database engine must still retrieve and count the first 10,000 matching rows before discarding them and returning the next 20. This work happens even if those rows are never sent over the network. On a B-Tree index, this means traversing thousands of leaf nodes sequentially. If your query involves joins or lacks a covering index, the database may need to perform random I/O lookups back to the heap for every skipped row.

-- Typical offset query that slows down at depth
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;

-- Execution time on 5M row table:
-- OFFSET 0:     2ms
-- OFFSET 10000: 45ms
-- OFFSET 100000: 380ms

This linear degradation creates unpredictable latency. In my experience auditing APIs for Nepal-based e-commerce platforms during peak sale events, deep pagination queries were often the primary cause of connection pool exhaustion. Users rarely navigate to page 500, but automated scrapers and poorly designed sync jobs do, and they can bring your entire service down by consuming all available database connections with expensive offset scans.

When offset is acceptable

Despite its flaws, offset pagination remains valid for specific scenarios. Admin panels where users need to jump to arbitrary pages, datasets under 10,000 rows, or situations where the total count is required for UI display are legitimate use cases. The key is recognizing the boundary. If your table exceeds a few hundred thousand rows or your p99 latency targets are strict, you should evaluate alternatives discussed in this API Pagination Cursor vs Offset Deep Dive.

How do you implement cursor-based pagination correctly?

Cursor-based pagination (also called keyset or seek pagination) replaces the numeric offset with a pointer to the last seen record. Instead of asking for "page 5," the client asks for "records after this specific ID." This allows the database to use an index seek rather than a sequential scan, delivering constant-time performance regardless of how deep into the dataset you are.

Designing a stable cursor

A common mistake is using a non-unique column like created_at as the sole cursor. If multiple records share the same timestamp, you will either skip rows or return duplicates when paginating through concurrent inserts. Always use a unique, monotonically increasing column as the tiebreaker. The combination of (created_at, id) provides both chronological ordering and deterministic positioning.

-- First request
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Subsequent request using composite cursor
-- Client passes last_created_at='2026-08-15T10:30:00Z' and last_id=48291
SELECT id, title, created_at
FROM articles
WHERE (created_at, id) < ('2026-08-15T10:30:00Z', 48291)
ORDER BY created_at DESC, id DESC
LIMIT 20;

For PostgreSQL users, this tuple comparison syntax is efficient and uses standard B-Tree indexes. If you're working with other databases or prefer explicit logic, expand the tuple into an OR condition: WHERE created_at < ? OR (created_at = ? AND id < ?). Ensure you have a composite index matching your sort order exactly; otherwise, the optimizer cannot perform an efficient seek. Teams migrating from MySQL should review MariaDB vs MySQL differences as tuple optimization behavior varies between engines.

Encoding cursors for API safety

Never expose raw database values as cursor parameters in public APIs. Encode the cursor as an opaque Base64 string containing the necessary fields. This prevents clients from manipulating internal IDs, reduces coupling between your API contract and schema, and allows you to change the underlying cursor composition without breaking clients. Always validate and decode server-side; treat cursors as untrusted input.

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

No pagination strategy is universally superior. Understanding the constraints of each approach prevents costly architectural pivots later. The following comparison synthesizes years of production experience across fintech, e-commerce, and SaaS platforms.

The consistency advantage of cursor pagination deserves emphasis. With offset, if a new record is inserted between page requests, every subsequent page shifts by one row. Users see duplicates or miss items entirely. Cursor-based approaches anchored to immutable identifiers remain stable regardless of concurrent modifications. For real-time feeds or audit logs where accuracy matters more than page numbers, this stability is non-negotiable.

How do you handle sorting and filtering with cursor pagination?

Cursor pagination introduces constraints on sorting that offset pagination doesn't have. Every sort order requires a corresponding composite index that includes the cursor column as the final tiebreaker. If your API supports sorting by price, date, and relevance, you need three separate indexes and three distinct cursor compositions.

Multi-column sort challenges

When sorting by non-unique columns like price, your cursor must encode both the sort value and a unique identifier. The WHERE clause becomes more complex: WHERE (price, id) > (?, ?). This works efficiently with a composite index on (price, id), but fails if users want to sort by price descending and date ascending simultaneously. Such mixed-direction sorts require specialized index structures or application-level merging that often negates cursor pagination's performance benefits.

Filtering compounds the complexity. A filter on category = 'electronics' combined with cursor pagination requires an index on (category, created_at, id). Without it, the database filters after seeking, potentially scanning many rows to find 20 matches. In practice, limit supported filter+sort combinations to those backed by indexes. Document unsupported combinations clearly rather than allowing silent performance degradation. Teams building observability around these APIs should track slow query metrics as outlined in the four golden signals of monitoring to catch unindexed cursor queries before they impact users.

Handling deletions and gaps

Cursors referencing deleted records present a subtle challenge. If a user bookmarks a cursor pointing to a now-deleted item, the next query should resume from the nearest valid position rather than failing. Implement this by using range conditions (> or <) rather than equality checks. The database naturally skips missing rows and returns the next available record. Never store cursors that depend on row existence; always design them to be resilient to data mutations.

Conclusion

This API Pagination Cursor vs Offset Deep Dive has covered the mechanical, operational, and practical dimensions of both strategies. Offset pagination offers simplicity and flexibility for bounded datasets, while cursor-based pagination provides the scalability and consistency required for modern high-volume APIs. The right choice depends entirely on your data volume, access patterns, and consistency requirements—not on industry trends or premature optimization.

Start with offset for admin interfaces and prototypes. Migrate to cursor-based pagination when profiling reveals offset as a bottleneck or when your product demands stable infinite scroll. Whichever path you choose, ensure your decision is backed by benchmarks against realistic data volumes, not assumptions. If you're designing a new API or struggling with pagination performance in production, reach out to discuss your specific architecture. Getting pagination right early prevents painful migrations and user-facing bugs down the road.