Why OFFSET Pagination Broke Our API at Scale (And How Cursor Pagination Fixed It)

Pagination is one of those things nobody debates early. You add LIMIT and OFFSET, ship the feature, and move on. That’s exactly what we did.

For months, everything felt fine. Lists loaded quickly. APIs stayed stable. Then data volume crossed a threshold. Pages that were once fast started dragging. Not everywhere. Only deep pages. Only during peak usage.

That’s what made it hard to spot. Pagination bugs don’t explode. They decay.

Why OFFSET Pagination Feels Safe at First

OFFSET pagination feels intuitive because it matches how humans think.

Page 1, page 2, page 3.

The database query looks clean and readable. It even performs well on small datasets. That’s the trap.

The database does not “jump” to row 500,000. It walks past 499,980 rows and throws them away. At scale, that cost dominates everything else.

The Exact Query That Started Hurting

This was the production query behind a high-traffic list endpoint.

CopySELECT *
FROM orders
WHERE company_id = ?
ORDER BY created_at DESC
LIMIT 20 OFFSET 400000;

Nothing about this query looks dangerous. Yet this one line was responsible for multi-second response times during peak hours.

The deeper users paginated, the slower the system became. That slowdown multiplied with concurrent traffic.

OFFSET Cost Grows Linearly

OFFSET pagination forces the database to:

• Scan rows it will never return

• Sort large result sets

• Discard most of the work

This is why page 1 is fast and page 200 is painfully slow. In development, you never feel it. In production, you pay for it with every request.

The Moment We Stopped Blaming Infrastructure

The first instinct was scaling. More CPU. Bigger instances. Better disks.

None of that fixed it.

The bottleneck wasn’t resources. It was work amplification. The database was doing more work per request as data grew. That’s when we stopped optimizing infrastructure and started redesigning pagination.

Pagination Is a Data Access Pattern

This was the architectural shift.

Pagination isn’t a UI concern.

It’s a data access strategy.

Once you accept that, OFFSET becomes hard to justify for large, ordered datasets.

Step 1 – Understanding Cursor Pagination

Cursor pagination doesn’t ask the database to skip rows. It asks it to continue from a known position.

Instead of “give me page 200,” you say:

“Give me the next 20 records after this record.”

That aligns with how indexes work.

Rewriting the Query Using a Cursor

Instead of OFFSET, we used the last seen value as a cursor.

CopySELECT *
FROM orders
WHERE company_id = ?
AND created_at < ?
ORDER BY created_at DESC
LIMIT 20;

Now the database:

• Uses the index efficiently

• Stops as soon as it has enough rows

• Does constant work per request

Performance stopped degrading as data grew.

API-Level Change (Without Breaking Clients)

We didn’t remove page numbers immediately. That would have broken consumers. Instead, we introduced cursors gradually.

Old API Response

Copy{
  "items": [...],
  "page": 5
}

New API Response (Cursor-Based)

Copy{
  "items": [...],
  "nextCursor": "2024-12-01T10:22:11Z"
}

Clients could adopt it incrementally. Old pagination still worked temporarily. New clients got better performance immediately.

This mattered more than theoretical purity.

Why We Didn’t Keep OFFSET for “Small Pages”

We considered hybrid logic. OFFSET for early pages, cursor later.

We rejected it.

Reasons:

• Two code paths increase bugs

• Clients behave unpredictably

• Performance cliffs still exist

Consistency beats cleverness in system design.

What Cursor Pagination Costs

Cursor pagination isn’t free.

You lose:

• Easy random access to page numbers

• Simple “jump to page 50” UX

You gain:

• Predictable performance

• Stable latency under load

• Index-friendly queries

• Easier caching

For real systems, that tradeoff is almost always worth it.

How This Changed API Performance

After migration:

• Deep pagination stopped timing out

• Database CPU flattened

• API latency became consistent

• Support tickets disappeared quietly

No one celebrated. That’s how you know it worked.

SEO Note - Pagination and Crawling

For SEO-driven pages, we kept:

• Clean canonical URLs

• Limited crawl depth

• Server-side rendering for initial pages

Cursor pagination powered the API. SEO pagination stayed controlled and intentional. These concerns don’t have to fight each other.

Final Takeaway

OFFSET pagination doesn’t fail because it’s wrong. It fails because it doesn’t scale.

Cursor pagination aligns with how databases actually work. Once you adopt it, performance stops being a guessing game and starts being predictable.

🔗 Suggested Links

If you’re exploring why backend systems feel fast early and then degrade unpredictably, this pagination issue fits a familiar pattern. A very similar failure mode showed up when a single missing database index quietly pushed response times from milliseconds to seconds. That breakdown dives deeper into how databases behave under scale and why “working locally” is a misleading signal.

Likewise, if you’re designing APIs meant to survive traffic growth, it’s worth understanding how caching decisions interact with pagination strategies. In another post, I broke down how a Redis cache actually made our app slower because invalidation logic didn’t match real access patterns. Pagination and caching amplify each other’s mistakes.