Cursor Pagination Vs Offset Pagination Laravel: Postgresql Performance Guide

TL;DR

Offset pagination (LIMIT 15 OFFSET 10000) is the default in Laravel (paginate()) but becomes catastrophically slow on large PostgreSQL tables. To fulfill an OFFSET query, PostgreSQL must fetch, sort, and discard all rows before the offset point—meaning page 10,000 scans 150,000 rows just to throw away 149,985 of them. Impact: Deep pagination response times dropped from 10+ seconds (timeout) to 2ms, providing O(1) constant time performance regardless of how many pages the user navigates.. This guide walks through the problem, the solution, and how to verify the improvement with real metrics.

The Problem

Your Laravel application exposes an API endpoint serving 10 million transaction records. Users complain that fetching page 1 is fast, but navigating to page 5,000 causes the API to time out after 10 seconds. Your database CPU spikes because PostgreSQL is performing an expensive disk sort and sequential scan to discard millions of rows for every deep pagination request.

This is a common scenario in production applications that have been running for months or years. The performance degradation is often gradual, making it hard to notice until it becomes a serious issue affecting users or operational costs.

How It Works

Offset pagination (LIMIT 15 OFFSET 10000) is the default in Laravel (paginate()) but becomes catastrophically slow on large PostgreSQL tables. To fulfill an OFFSET query, PostgreSQL must fetch, sort, and discard all rows before the offset point—meaning page 10,000 scans 150,000 rows just to throw away 149,985 of them. Cursor pagination (cursorPaginate()) solves this by using a WHERE clause on an ordered index (e.g., WHERE id > 150000 ORDER BY id LIMIT 15). It executes instantly regardless of depth.

Understanding the underlying mechanism is key to applying this optimization correctly and knowing when it applies to your specific situation versus when a different approach is needed.

Solution

The following before/after comparison demonstrates the complete solution. Study the comments carefully — they explain the reasoning behind each configuration choice.

Before

-- OFFSET Pagination (Catastrophic on deep pages)
SELECT * FROM transactions 
ORDER BY created_at DESC 
LIMIT 15 OFFSET 150000;

-- PostgreSQL EXPLAIN:
-- Limit  (cost=12345.00..12346.00 rows=15)
--   -> Sort  (cost=10000.00..15000.00 rows=10000000)
--        Sort Key: created_at DESC
--        -> Seq Scan on transactions

-- In Laravel:
$transactions = Transaction::orderBy('created_at', 'desc')->paginate(15);

After

-- Cursor Pagination (Instant on any page)
-- Requires an index: CREATE INDEX idx_trans_created ON transactions(created_at DESC, id DESC);

-- Instead of OFFSET, it uses the last seen values:
SELECT * FROM transactions 
WHERE (created_at, id) < ('2025-03-01 10:00:00', 987654) 
ORDER BY created_at DESC, id DESC 
LIMIT 15;

-- PostgreSQL EXPLAIN:
-- Limit  (cost=0.43..1.50 rows=15)
--   -> Index Scan using idx_trans_created on transactions
--        Index Cond: RowCompare((created_at, id), <, ...)

-- In Laravel:
$transactions = Transaction::orderBy('created_at', 'desc')->cursorPaginate(15);

Performance Impact

Deep pagination response times dropped from 10+ seconds (timeout) to 2ms, providing O(1) constant time performance regardless of how many pages the user navigates.

Here are the measured results from applying this optimization in a production environment:

These numbers will vary based on your specific data volume, hardware, and query patterns, but the relative improvement should be consistent. Always measure before and after in your own environment to confirm the impact.

When to Use This

This optimization is most effective when:

• Your application matches the problem scenario described above
• You’ve confirmed the bottleneck with monitoring or profiling tools
• The data volume is large enough that the optimization makes a meaningful difference

It may not be the right fit if your tables are small (under 100K rows), your queries are already fast (under 10ms), or the bottleneck is elsewhere in your stack (application code, network, or client-side rendering).

Key Takeaways

• Measure first: Always profile before optimizing — the bottleneck may not be where you think it is
• Test in staging: Apply the optimization in a staging environment with production-like data before deploying
• Monitor after: Set up dashboards tracking the metrics above so you can verify the improvement and catch regressions

Frequently Asked Questions

What are the limitations of cursor pagination in Laravel?

Cursor pagination does not support total() page counts or jumping to a specific page number (e.g., navigating directly to Page 5). It only supports Next and Previous links. This makes it perfect for endless scrolling interfaces or API integrations, but unsuitable for traditional numbered pagination UI.

Why does Laravel’s cursorPaginate require ordering by ID as well?

Cursor pagination relies on strict ordering to know exactly where to resume. If you order by created_at and multiple records have the exact same timestamp, the cursor might skip records or create duplicates across pages. Laravel automatically appends the primary key (e.g., id) to the sort order to ensure absolute determinism.