API Pagination Cursor vs Offset Deep Dive

By Kokil Thapa | Last reviewed: September 2026

Every list endpoint forces a choice: cursor pagination vs offset pagination. Offset feels natural because SQL and ORMs support it out of the box. Cursor pagination trades page numbers for stable, constant-time reads on large tables. I've hit both patterns in production—legal document archives, booking feeds, eCommerce catalogs—and the wrong default costs real money after launch. This guide walks through mechanics, trade-offs, Laravel implementation, and how to decide before you ship. For broader API design context, see my notes on Laravel API best practices and API development services.

How does offset pagination work and when does it fail?

Offset pagination uses two parameters: limit (page size) and offset (rows to skip). The database runs LIMIT 20 OFFSET 1000. It must scan and discard 1,000 rows before returning the next 20. For small tables or admin screens where users rarely pass page five, this works fine. The SQL is readable. Every ORM supports it without extra setup.

Problems appear at scale. On a table with millions of rows, page 50,000 forces MySQL or PostgreSQL to process 999,980 rows for 20 results. Query time grows linearly with offset. I've seen timeouts on legal-tech portals where case archives outgrew early estimates. Offset also drifts under concurrent writes. A new row inserted while a user browses shifts items between pages. Users see duplicates or skipped records. For stable feeds at depth, offset breaks down.

You can mitigate offset pain without switching entirely. Keyset pagination on a single indexed column is a middle ground. Covering indexes reduce scan cost. Still, deep offset remains O(offset + limit) unless you change the model. For query tuning basics, see database indexing for performance and MySQL query optimization for slow queries.

How does cursor pagination solve performance and consistency issues?

Cursor pagination replaces 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 index-seeks to that position. Query time stays near constant regardless of depth. Social feeds and activity timelines use this pattern for a reason.

Cursors also stabilize results. The query anchors to a specific record, not a positional slot. Inserts and deletes between requests do not shuffle items across pages. The trade-off is navigation. There is no native "page 47." Users move next or previous only. That fits infinite scroll and mobile feeds. It frustrates users who need arbitrary jumps or total page counts.

Laravel 13 ships CursorPaginator on PHP 8.3+. Laravel 12 on PHP 8.2 supports the same API. The framework handles encoding, decoding, and query construction. For REST fundamentals, see how to build a REST API in Laravel and the official Laravel cursor pagination documentation.

Implementing cursor pagination in Laravel

Here is a production-ready controller method with a deterministic sort:

<?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')
            ->cursorPaginate(20);

        return response()->json([
            'data' => $paginator->items(),
            'meta' => [
                'next_cursor' => $paginator->nextCursor()?->encode(),
                'prev_cursor' => $paginator->previousCursor()?->encode(),
                'per_page' => $paginator->perPage(),
            ],
        ]);
    }
}

Always add a unique tie-breaker column. When sorting by file_date, many rows share the same value. Without a final sort on id, cursors skip or duplicate records. Chain orderBy calls in the same direction for every column except where product rules require otherwise.

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

No single strategy wins everywhere. Product navigation, dataset size, and reporting needs drive the choice. Many apps use both—offset on admin routes, cursor on public feeds. The table below summarizes what I weigh on real client projects.

On a legal-tech portal, staff dashboards need offset so clerks jump to page 12 and see total case counts. The public activity feed uses cursors because users scroll continuously. Performance must stay flat as the archive grows. I've shipped similar splits on Mijar Law Associates and booking systems like Adventure Third Pole Trek. For teams evaluating architecture, the full-stack developer landscape in Nepal increasingly expects fluency in both patterns.

Hybrid designs are common. Expose offset on internal admin APIs. Expose cursor on mobile and public endpoints. Document the split in your OpenAPI spec so frontend teams do not assume one model everywhere. Pair pagination with API rate limiting and Laravel rate limiting to protect deep offset abuse.

How do you handle edge cases and common pitfalls in cursor pagination?

Cursor pagination has subtleties that surface only under load or multi-tenant auth. Address them during design, not after support tickets pile up.

Multi-column sorts and composite cursors

When sorting by multiple columns, the cursor must encode every sort column plus the unique tie-breaker. Laravel's cursorPaginate handles this when orderBy chains match the cursor decode path. Custom SQL needs compound WHERE clauses:

-- Composite cursor: status + created_at + id
SELECT * FROM cases
WHERE (status, created_at, id) > ('active', '2026-09-10 14:30:00', 58392)
ORDER BY status ASC, created_at DESC, id DESC
LIMIT 20;

MySQL 8.4 LTS and PostgreSQL 18 support row-value comparisons. Older MySQL versions need expanded OR conditions that are harder to optimize. Match your cursor logic to the database version in production, not just local dev. Use a JSON formatter when debugging encoded cursor payloads in API responses.

Cursor opacity, security, and multi-tenancy

Cursors should be opaque to clients. Do not expose raw IDs or timestamps in plain text. Laravel base64-encodes by default. That stops casual tampering but is not cryptographic. On SaaS platforms, validate decoded cursor values against authorization policies. I've audited portals where unsigned cursors let users enumerate records across tenant boundaries by incrementing encoded IDs. Sign cursors or re-check firm_id (or equivalent) on every request.

Deleted anchor records and expired cursors

If the row referenced by a cursor is deleted, the next request may return zero rows despite more data existing. Return a cursor_expired flag in meta so the client refreshes from the top. Alternatively, seek to the nearest valid position. Most mobile clients handle a soft reset gracefully when you document the contract in your API spec.

How do you benchmark and validate pagination performance in production?

Complexity charts help, but your schema, indexes, and data skew decide real latency. Benchmark before you commit on high-traffic endpoints.

1. Seed realistic volume. Use Laravel factories to reach 1M+ rows on cursor candidates. Uniform test data hides skew problems that appear in production.
2. Profile at multiple depths. Test offset at pages 1, 100, 1,000, and 10,000. Test cursor at equivalent positions. Record p95 latency, not averages.
3. Load-test under concurrency. Use k6 or wrk. Offset often looks fine in isolation but collapses when many clients scan deep pages at once.
4. Confirm index usage. Run EXPLAIN ANALYZE on PostgreSQL or EXPLAIN FORMAT=JSON on MySQL. Cursor queries must index-seek. Missing composite indexes silently revert cursor performance toward offset-like scans. See Eloquent patterns for large datasets.
5. Monitor after launch. Tag paginated queries in APM. Alert on p95 above your SLA. On legal-tech listings I target sub-200ms at any depth.

The hosting cost gap between a well-indexed cursor and a deep offset scan can run from Rs 500/month (~USD 4) to Rs 5,000/month (~USD 37) on busy endpoints. That difference matters for Nepal startups on tight budgets. PostgreSQL's LIMIT/OFFSET documentation explicitly notes that large offsets impose significant cost—worth citing when stakeholders ask why cursor pagination vs offset matters financially.

Cache total counts on admin endpoints with Redis 8.10 if COUNT(*) becomes expensive. Public feeds rarely need totals. When they do, show approximate counts or "load more" without page numbers. For caching patterns, see caching strategies for high-traffic sites and REST API design best practices. Version your pagination contract alongside API versioning strategy so clients migrate cleanly.

Key Takeaways

• Choose offset when users need random page access, total counts, or SEO-friendly ?page=N URLs on smaller datasets.
• Choose cursor pagination for feeds, infinite scroll, and any list where depth must stay fast and stable under concurrent writes.
• Always append a unique tie-breaker column (typically id) to every cursor sort chain.
• Encode cursors opaquely and validate decoded values against tenant authorization on every request.
• Run EXPLAIN at production scale before launch—missing composite indexes erase cursor gains.
• Many production apps use both: offset on admin APIs, cursor on public mobile feeds from the same database.

People Also Ask

Is cursor pagination always faster than offset?

Not always on tiny tables. Offset on page 1 with a covering index can match cursor latency. Cursor wins at depth and under write churn. Once offset exceeds a few thousand rows or concurrent users hammer deep pages, cursor pagination vs offset stops being a close call.

Can you show total pages with cursor pagination?

Not cheaply. Exact totals require a full count scan. Most cursor APIs omit totals or return estimates. If your UI needs "Page 3 of 847," offset or a cached count endpoint is the practical choice.

Does Laravel support both pagination types?

Yes. Laravel 12 and 13 provide paginate() for offset and cursorPaginate() for cursor-based lists. Use the same model with different controller methods per route rather than forcing one style globally.

What happens if a user bookmarks a cursor URL?

Bookmarks work until the anchor row is deleted or sort order changes. Return a clear error or reset payload when the cursor is invalid. Document that behavior in your API so clients refresh gracefully instead of showing an empty list silently.

Choose Cursor Pagination vs Offset Before Launch

This guide covered the full cursor pagination vs offset decision: mechanics, Laravel code, edge cases, benchmarks, and hybrid architecture. Neither approach is universally better. Offset serves admin panels and search. Cursor serves feeds and mobile scroll at scale. The expensive mistake is defaulting to offset everywhere because it is familiar, then refactoring under traffic.

Decide during API design. Document the rationale. Benchmark with realistic row counts. If you are building a Laravel API and need help with pagination strategy, database indexes, or production deployment, contact us to discuss your project or explore custom software development. Competent Laravel developers in Nepal treat pagination as architecture—not an afterthought.