Optimizing Large Dataset Queries: The Pagination Problem

It's a familiar scenario: your "Recent Activity" feed works perfectly with 100 items. But when you have 10 million items, clicking "Page 5000" causes the database to time out.

Why? Because of how standard pagination (OFFSET) works.

In this guide, we'll explore why deep pagination kills performance and how to fix it using the Seek Method (also known as Keyset Pagination).

The Problem with OFFSET

Most developers learn pagination like this:

-- Get Page 2 (Items 11-20)
SELECT * FROM orders 
ORDER BY date DESC 
LIMIT 10 OFFSET 10;

This seems fine. But what happens when you need Page 100,000?

-- Get Page 100,000
SELECT * FROM orders 
ORDER BY date DESC 
LIMIT 10 OFFSET 1000000;

The Database's Hidden Work: To fulfill OFFSET 1,000,000, the database must:

1. Fetch 1,000,000 rows.
2. Sort them.
3. Throw them away.
4. Return the next 10.

It gets slower and slower the deeper you go. This is O(N) complexity for pagination.

The Solution: Seek Method (Keyset Pagination)

The Seek Method doesn't count rows. It simply asks: "Give me the 10 rows after the last one I saw."

If the last order on Page 1 had id=500, Page 2 asks:

SELECT * FROM orders
WHERE id < 500  -- Assuming we sort DESC
ORDER BY id DESC
LIMIT 10;

Why it's fast: The database jumps directly to id=500 using the index. It doesn't read or sort the previous 499 rows. This is O(1) complexity—constant time, regardless of how deep you paginate!

Interactive Comparison

Let's verify this conceptual difference. While our dataset is small, the logic holds for millions of rows.

1. OFFSET Method (The Slow Way)

2. Seek Method (The Fast Way)

Faster, but requires knowing the last ID from the previous page.

Trade-offs

Feature	OFFSET	Seek Method
Performance	Slows down as pages increase	Constant speed
Jumping	Can jump to "Page 500" easily	Cannot jump (must scroll linearly)
Consistency	Rows can shift if new data is added	More stable results (anchored to ID)
Implementation	Trivial	Complex (need to track last ID)

Why Stable Ordering Matters

Keyset pagination only works well when the ordering is stable and deterministic.

This means your query should sort by a column, or set of columns, that gives every row a predictable position. A timestamp alone is often not enough because multiple rows can share the same value.

A safer production pattern is:

SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < ('2026-04-01 10:00:00', 98123)
ORDER BY created_at DESC, id DESC
LIMIT 20;

That extra tie-breaker column prevents duplicates and missing rows when many records share the same timestamp.

Multi-Column Cursors in Real Apps

Most infinite-scroll interfaces do not paginate on a single numeric ID forever. They paginate on a business order:

• newest events first
• highest score first
• orders by created date inside one account

In those cases, your cursor usually needs to store more than one field. Common examples include:

• (created_at, id)
• (score, id)
• (customer_id, created_at, id) for per-customer feeds

The rule is simple: your cursor must match the same columns and order used in ORDER BY.

If it does not, the database cannot jump cleanly to the next page.

OFFSET Is Not Always Wrong

It is easy to overcorrect and declare OFFSET forbidden. That is not true.

OFFSET is often perfectly acceptable when:

• the table is small
• the result set is already tightly filtered
• the page depth stays shallow
• you genuinely need random page access, such as "jump to page 17"

The real problem is using OFFSET on a query shape that has to scan, sort, and discard enormous numbers of rows repeatedly.

This is why pagination strategy belongs inside the broader performance conversation. It is not just a UI choice. It is a database access pattern.

A Practical Pagination Workflow

Use this checklist when a listing page gets slow:

1. Check the ORDER BY clause. Is it backed by a useful index?
2. Measure whether the slowdown happens only on deeper pages.
3. Confirm whether the query uses OFFSET or a cursor/seek pattern.
4. Decide whether the product actually needs random access to deep pages.
5. If not, switch the interface to cursor-based navigation and optimize for the real browsing pattern.

Many teams discover that users do not actually need "page 5000." They need "load more recent results quickly."

Related Interview Question

This concept is so important it's a dedicated interview question!

• Slow Query Detection (Databricks) - While this question focuses on finding slow queries, understanding why queries are slow (like inefficient scans) is key to solving performance problems.

Related Articles

• SQL Optimization Techniques for the wider checklist around sorting, indexing, batching, and query rewrites.
• How to Read SQL Execution Plans for understanding how sorts and scans show up when pagination starts getting expensive.
• Understanding Database Indexes for the indexing patterns that make seek-based pagination efficient.

Conclusion

If you're building an "Infinite Scroll" feed (like Instagram or Twitter), always use the Seek Method. It scales infinitely.

If you are building a small admin dashboard with < 1,000 items, OFFSET is perfectly fine and easier to code.

Rule of thumb: Optimization is about picking the right tool for the scale.