Why We Ditched OFFSET Pagination?

Let me share a quick story from our backend team.

We were building a pretty straightforward API to fetch a user’s transaction history — complete with pagination. For the first few months, everything ran smoothly. But as the data grew, the same query that used to return in 200ms ballooned to over 2–3 seconds.

At first, we suspected server load or maybe a missing index. But after digging into the database execution plan, we discovered something surprisingly basic — and surprisingly expensive:

OFFSET was the silent killer.

🚨 OFFSET Pagination: Looks Clean, Performs Poorly

Here’s the original query we were using:

It looks fine at first glance, right?

But under the hood, the database fetches 10,020 rows, discards the first 10,000, and returns just the 20 you asked for. That’s a lot of wasted work — and it gets worse the further you paginate.

Every scroll, every “next page” request, increases the OFFSET… and the lag. As your dataset grows, performance tanks.

✅ The Fix: Keyset (Seek) Pagination

After a few half-hearted optimizations failed to help, we rewrote the query using keyset pagination — and the difference was night and day.

New query:

Instead of skipping thousands of rows, we just tell the database:

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

This is known as keyset or seek pagination — and it’s way more efficient because the database can jump straight to the right record using indexes. No scanning, no discarding.

Result:
Query time dropped from 2.6 seconds to under 200ms — no infra changes, no caching. Just smarter SQL.

🧠 Handling Duplicate Timestamps

We hit one snag: multiple transactions sometimes shared the same created_at value (e.g., during bulk uploads), which caused pagination glitches — duplicated or skipped rows.

The fix? Add a tie-breaker:

By using both created_at and id in the WHERE and ORDER BY clauses, pagination became stable and predictable.

🛠 Other Techniques We Explored

While keyset pagination solved our main issue, we explored a few other options depending on the use case:

1. Cursor-Based Pagination

Same as keyset, but instead of passing raw values, we encode them into a cursor token:

"next_cursor": "2025-06-27T10:00:00Z_98765"

This is how APIs like Twitter and Instagram handle infinite scrolling — efficient, stateless, and clean.

2. Index-Only OFFSET (When You Must Use OFFSET)

Some internal tools (like admin dashboards) needed traditional pagination (e.g., jump to page 7). Keyset doesn’t support that.

In these cases, we:

• Added a covering index:

CREATE INDEX idx_user_created_id_amount
ON transactions(user_id, created_at DESC, id, amount);

• Queried only indexed columns

This didn’t eliminate the performance hit but cut query time in half compared to the raw OFFSET version.

3. Materialized Views (For Static Dashboards)

One reporting dashboard kept running a slow summary query repeatedly. We solved this with a materialized view:

We refreshed it every few minutes via a cron job. Result: near-instant reports with less load on live data.

📊 Real-World Performance Results

This experience reminded us of something important:

You don’t always need more infrastructure. Sometimes, you just need better SQL.

If your app is using OFFSET-based pagination and things are slowing down, switch to keyset pagination. It’s elegant, efficient, and surprisingly easy to implement.

Hope this helps someone skip the rabbit hole we fell into.

We’re here to assist you with any questions about your project https://synpass.pro/contactsynpass/ 🤝