August 14, 2026
9 min read
Table of Contents
By Kokil Thapa | Last reviewed: August 2026
Choosing between cursor and offset pagination is one of the most consequential architectural decisions you make when designing a REST API. This API Pagination Cursor vs Offset Deep Dive breaks down the mechanical differences, performance characteristics, and real-world trade-offs based on shipping production APIs since 2010. If you are building a data-heavy application or integrating with modern frontend frameworks, understanding these distinctions prevents costly refactors later; for broader API design context, see my guide on Laravel API best practices.
Quick answer: In this API Pagination Cursor vs Offset Deep Dive, use offset pagination for admin dashboards requiring random page access and small datasets; choose cursor pagination for high-volume feeds, infinite scroll, and real-time data where consistent performance and stability matter more than jumping to specific pages.
How does offset pagination work and when does it fail?
Offset pagination is the traditional approach most developers learn first. It uses two parameters: limit (page size) and offset (records to skip). The database executes LIMIT 20 OFFSET 1000, meaning it must scan and discard 1,000 rows before returning the next 20. For small tables or administrative interfaces where users rarely navigate past page five, this works perfectly well. The SQL is simple, human-readable, and supported by every ORM without special configuration.
The problems emerge at scale. On a table with millions of rows, requesting page 50,000 forces MySQL or PostgreSQL to process 999,980 rows just to return 20 results. Query time degrades linearly with the offset value. I have seen this cause timeout errors on legal-tech portals where case archives grew beyond initial projections. Additionally, offset pagination suffers from data drift: if a new record is inserted while a user is browsing, items shift between pages, causing duplicates or skipped records. For stable, high-performance feeds, this model fundamentally breaks down.
How does cursor pagination solve performance and consistency issues?
Cursor pagination replaces the numeric offset with an opaque pointer to the last seen record. Instead of "skip 1,000 rows," the query asks for "20 rows where id > 58392." The database uses an index seek directly to that position, making query time constant regardless of how deep into the dataset you are. This is why platforms like Twitter, Facebook, and GitHub use cursor-based approaches for their primary feeds.
Beyond raw speed, cursors provide stability. Because the query anchors to a specific record rather than a positional offset, inserts and deletes between requests do not cause items to shift or duplicate. The user sees a consistent stream. The trade-off is that you lose random access: there is no "page 47" concept. Navigation becomes strictly sequential (next/previous), which aligns perfectly with infinite scroll and mobile-first interfaces but frustrates users who need to jump to arbitrary positions.
Implementing cursor pagination in Laravel 12
Laravel 12 provides a built-in CursorPaginator that handles encoding, decoding, and query construction automatically. Here is a production-ready controller method:
<?php
namespace App\Http\Controllers\Api;
use App\Models\CaseFile;
use Illuminate\Http\Request;
use Illuminate\Http\JsonResponse;
class CaseFileController extends Controller
{
public function index(Request $request): JsonResponse
{
$paginator = CaseFile::query()
->where('firm_id', $request->user()->firm_id)
->orderByDesc('file_date')
->orderByDesc('id') // Tie-breaker for deterministic ordering
->cursorPaginate(20);
return response()->json([
'data' => $paginator->items(),
'meta' => [
'next_cursor' => $paginator->nextCursor()?->encode(),
'prev_cursor' => $paginator->previousCursor()?->encode(),
'per_page' => $paginator->perPage(),
],
]);
}
}The critical detail is the secondary sort column. When sorting by a non-unique column like file_date, multiple records share the same value. Without a unique tie-breaker (id), the cursor cannot reliably determine position, and records will be skipped or duplicated. Always include a unique column as the final sort criterion.
What are the practical trade-offs between cursor and offset pagination?
No pagination strategy is universally superior. The right choice depends on your product's navigation patterns, dataset size, and user expectations. Below is a comparison based on real implementation experience across eCommerce catalogs, legal document archives, and booking systems.
| Criterion | Offset Pagination | Cursor Pagination |
|---|---|---|
| Random page access | ✅ Supported natively | ❌ Not possible without hacks |
| Performance at depth | ❌ Degrades linearly | ✅ Constant time regardless of position |
| Data consistency | ❌ Prone to drift/duplicates | ✅ Stable across concurrent writes |
| Total count availability | ✅ Cheap COUNT(*) query | ❌ Expensive or estimated only |
| Implementation complexity | ✅ Trivial in any framework | ⚠️ Requires sortable unique key + encoding |
| SEO / crawlability | ✅ Clean ?page=N URLs | ⚠️ Opaque cursors less friendly |
| Best fit | Admin panels, search results, small datasets | Feeds, timelines, infinite scroll, large datasets |
In practice, many applications use both. An admin dashboard for managing court marriage applications benefits from offset pagination because staff need to jump to specific pages and total counts matter for reporting. The same system's public-facing case search feed should use cursors because users scroll continuously and performance must remain predictable as the archive grows. When architecting a new system, consider whether different endpoints warrant different strategies rather than enforcing one globally. For teams evaluating full-stack approaches, the full-stack developer landscape in Nepal increasingly demands fluency in both patterns.
How do you handle edge cases and common pitfalls in cursor pagination?
Cursor pagination introduces subtleties that catch developers off guard during production deployments. Addressing these proactively prevents support tickets and data inconsistencies.
Handling multi-column sorts and composite cursors
When your API supports sorting by multiple columns (e.g., status then created_at), the cursor must encode all sort columns plus the unique tie-breaker. Laravel's cursorPaginate handles this automatically when you chain orderBy calls correctly, but custom implementations must construct compound WHERE clauses:
-- Composite cursor for status + created_at + id
SELECT * FROM cases
WHERE (status, created_at, id) > ('active', '2026-08-10 14:30:00', 58392)
ORDER BY status ASC, created_at DESC, id DESC
LIMIT 20;MySQL 8.0+ and PostgreSQL support row-value comparisons natively. Older MySQL versions require expanded OR conditions, which are harder to optimize. Verify your database version supports this syntax before committing to composite cursors in production.
Managing cursor opacity and security
Cursors should be opaque to clients. Never expose raw IDs or timestamps in the cursor string; encode them using base64 or signed tokens. Laravel's default encoder uses base64, which is sufficient for preventing accidental tampering but not cryptographically secure. If cursor manipulation could leak unauthorized records (e.g., cross-tenant data access in a SaaS platform), implement signed cursors or validate decoded values against authorization policies server-side. I have audited legal-tech portals where unsigned cursors allowed users to enumerate case files belonging to other firms by incrementing encoded IDs.
Dealing with deleted anchor records
If the record referenced by a cursor is deleted before the next request, the query returns zero results even though more data exists. Handle this gracefully: detect empty result sets with valid cursors and either return a descriptive error prompting the client to refresh, or implement fallback logic that seeks to the nearest valid position. For most applications, returning an empty set with a cursor_expired flag in the meta block gives frontends enough information to recover without confusing users.
How do you benchmark and validate pagination performance in production?
Theoretical complexity analysis matters, but real-world performance depends on your specific schema, indexes, and data distribution. Before committing to a pagination strategy for a high-traffic endpoint, run targeted benchmarks.
- Generate realistic test data. Use Laravel factories or seeders to populate tables with production-scale volumes (1M+ rows for cursor candidates). Uniform distributions mask problems that appear with skewed real-world data.
- Profile queries at multiple depths. Test offset pagination at page 1, 100, 1,000, and 10,000. Test cursor pagination at equivalent positions. Record p95 latency, not averages.
- Measure under concurrent load. Use tools like k6 or wrk to simulate realistic traffic. Offset pagination often shows acceptable single-query latency but collapses under concurrency due to repeated full scans competing for I/O.
- Validate index coverage. Run
EXPLAIN ANALYZE(PostgreSQL) orEXPLAIN FORMAT=JSON(MySQL) to confirm cursor queries use index seeks, not scans. Missing composite indexes silently degrade cursor performance to offset-like behavior. - Monitor in production. Add query tags or APM instrumentation distinguishing paginated queries. Set alerts for p95 latency exceeding thresholds. I track this on legal-tech portals where case file listings must respond within 200ms at any depth.
For teams building APIs in Nepal's growing tech sector, investing in proper benchmarking infrastructure pays dividends. The cost difference between a well-indexed cursor query and a deep offset scan can be the difference between Rs 500/month (~USD 4) and Rs 5,000/month (~USD 37) in database hosting for high-traffic endpoints. Understanding these trade-offs is part of being a competent Laravel developer in Nepal working on real production systems.
Making the Right Choice for Your API Pagination Strategy
This API Pagination Cursor vs Offset Deep Dive has covered the mechanics, trade-offs, implementation patterns, and validation approaches that separate theoretical knowledge from production-ready decisions. Neither strategy is inherently superior; each serves distinct product requirements. Offset pagination remains the right choice for administrative interfaces, search results with faceted navigation, and any context where users expect traditional page numbers. Cursor pagination earns its place in feeds, timelines, mobile-first infinite scroll experiences, and any endpoint where dataset growth would otherwise cause unacceptable latency degradation.
The most common mistake I see is not choosing at all—defaulting to offset everywhere because it is familiar, then discovering performance problems after launch when refactoring is expensive. Make the decision deliberately during API design, document the rationale in your codebase, and benchmark before shipping. If you are building a Laravel API and need guidance on pagination strategy, database optimization, or production deployment architecture, reach out to discuss your project.
Frequently Asked Questions
Offset uses numeric page numbers skipping records, while cursor uses an opaque pointer to a specific record position for consistent sequential fetching without gaps or duplicates.
Use offset when users need random page access, total counts, or simple admin tables where dataset stability matters less than navigation flexibility and implementation simplicity.
Offset degrades linearly; page 1000 at 50 items scans 50,000 rows. Cursors remain constant time O(1) regardless of depth because they seek directly via indexed lookups.
Because offset calculates position from the start each request. If rows are inserted or deleted between requests, the absolute position shifts, causing skipped or repeated items in subsequent pages. Cursor pagination avoids this by anchoring to a specific record value rather than a row number, making it stable for real-time feeds, notifications, or high-write tables common in Laravel applications handling bookings or orders.
Use Eloquent's cursorPaginate method introduced in Laravel 8 and refined through version 12. It automatically generates opaque cursors based on your orderBy column. Ensure you order by a unique, indexed column like id or created_at. The response includes next_cursor and previous_cursor metadata. This works natively with API Resources and requires no extra packages, though spatie/laravel-cursor-pagination offers additional customization if needed for complex multi-column sorting.
Yes, but the cursor column must exist in the result set and be uniquely identifiable across joined tables. Prefix columns explicitly to avoid ambiguity. Composite cursors using multiple columns require manual encoding. In my experience building directory sites like Lawyers Pokhara, joining users with profiles required careful index design on the foreign key plus timestamp combination to maintain cursor performance without full table scans during filtering.
Create composite indexes matching your ORDER BY clause exactly. For cursorPaginate('created_at', 'id'), add INDEX idx_created_id (created_at, id). Without this, MySQL performs filesort operations defeating cursor benefits. On production legal-tech portals I maintain, adding proper composite indexes reduced p95 latency from 800ms to under 50ms for paginated document listings exceeding 100,000 records. Always verify with EXPLAIN ANALYZE before deploying.
Append a unique tiebreaker column like primary key to your ordering. Cursor pagination requires deterministic ordering to generate stable pointers. Sorting only by created_at fails when multiple records share identical timestamps. Configure cursorPaginate(['created_at', 'id']) to ensure uniqueness. This pattern is essential for chronological feeds in eCommerce order histories or booking systems where batch imports create timestamp collisions.
No. Cursors are sequential pointers lacking positional context. You cannot compute page 47 directly. Implement hybrid approaches offering both modes: offset for admin dashboards requiring jump navigation, cursors for infinite-scroll consumer interfaces. On client projects like Ajako Deal, we exposed separate endpoints allowing vendors to browse listings via offset while buyers received cursor-paginated deal feeds optimized for mobile scrolling performance.
Return cursors as opaque strings in response metadata alongside data arrays. Never expose internal IDs or timestamps directly. Structure follows JSON:API or custom conventions with next_cursor, prev_cursor, and has_more boolean fields. In Laravel API Resources, wrap collections using CursorPaginator resource classes. Clients pass these values verbatim as query parameters. This abstraction allows backend implementation changes without breaking API contracts consumed by Vue frontends or mobile apps.
Exposed cursors can leak sortable column values if poorly encoded. Always encrypt or hash cursor payloads using Laravel's Crypt facade. Validate cursor structure server-side to prevent injection attacks. Rate-limit cursor endpoints since sequential traversal enables efficient scraping. On public-facing directories I build, signed cursors prevent attackers from manipulating pointers to enumerate private records or bypass intended access boundaries defined by policy scopes.
Cache individual cursor responses keyed by cursor value plus filter parameters. Avoid caching entire result sets since cursors represent positions not content. Invalidate caches on write operations affecting sorted columns. For read-heavy endpoints on platforms like Nepal Gift Card, we cache cursor pages with 60-second TTLs, reducing database load by 80 percent during peak traffic while maintaining acceptable freshness for digital product listings that change infrequently.
Yes, but relevance scores are non-deterministic tiebreakers. Order primarily by MATCH score descending, then by id ascending for stable cursors. Store computed relevance in generated columns if possible. Full-text searches already carry performance costs; ensure covering indexes exist. In practice on legal information sites, we precompute search rankings into materialized views updated via scheduled jobs, allowing cursor pagination over static ranked results instead of expensive runtime scoring.
Version your API or add cursor parameters alongside existing offset/page params. Deprecate offset gradually while monitoring client adoption. Provide migration guides explaining cursor semantics differ fundamentally. Maintain dual support during transition periods. On long-running WooCommerce integrations, we introduced cursor endpoints as v2 while keeping v1 offset functional for six months, allowing third-party developers adequate time to update their consumption patterns without breaking production systems.
Write integration tests inserting records between paginated requests to verify stability. Assert cursor opacity prevents client-side decoding. Test edge cases: empty result sets, single-record pages, boundary conditions at dataset start/end. Verify ordering consistency across concurrent writes. In Laravel test suites, use RefreshDatabase with factory sequences creating predictable timestamp distributions. Production debugging often reveals issues invisible in sterile test environments, so include logging of cursor generation parameters for traceability.
