SolveWithSQL

LIMIT vs OFFSET vs Cursor-Based Pagination

Difficulty: Beginner → Early Intermediate
Estimated time: 15–20 minutes
SQL concepts: LIMIT, OFFSET, cursor-based pagination, performance trade-offs
Goal: Understand when each pagination pattern should be used—and why.

The Scenario

The product manager asks:

“We’re loading more and more products. Which pagination approach should we use so pages stay fast and consistent?”

Pagination isn’t just syntax—it’s a design decision that affects performance and user experience.

Database Table

products

column_name	type	description
product_id	INTEGER	Unique product ID
name	TEXT	Product name
price	DECIMAL	Product price

Assume product_id is indexed and monotonically increasing.

The Three Pagination Patterns

1️⃣ LIMIT + OFFSET (Page-Based)

Use case: Simple lists, small datasets, admin tools.

SELECT name, price
FROM products
ORDER BY price ASC
LIMIT 10
OFFSET 20;

How it works

• Skips the first 20 rows
• Returns the next 10

Pros

• Simple
• Easy to reason about

Cons

• Slower on large datasets (database must count/skip rows)
• Can show duplicates or miss rows if data changes

2️⃣ LIMIT Only (First Page / “Top N”)

Use case: Dashboards, “Top results”, summaries.

SELECT name, price
FROM products
ORDER BY price ASC
LIMIT 10;

Pros

• Fast
• Predictable

Cons

• Not suitable for multi-page browsing

3️⃣ Cursor-Based Pagination (Keyset)

Use case: Large datasets, infinite scroll, APIs.

Instead of “page numbers”, you pass the last seen value.

SELECT name, price, product_id
FROM products
WHERE product_id > 105
ORDER BY product_id ASC
LIMIT 10;

Pros

• Very fast
• Stable results (no duplicates)
• Scales well

Cons

• Slightly more complex
• No direct “jump to page 7”

Your Task

Answer the following questions:

1. Which approach would you use for a small admin dashboard?
2. Which approach would you use for an infinite scrolling product list?
3. Why might OFFSET become slow as page numbers increase?

(No SQL to write here—this is about understanding patterns.)

Expected Understanding

Scenario	Best Pattern
Small dataset	LIMIT + OFFSET
“Top N” summary	LIMIT
Large dataset / infinite scroll	Cursor-based

Key Takeaways

• OFFSET is easy—but not free
• Cursor-based pagination scales better
• Pagination choice is a performance decision, not just syntax

Common Mistakes

• Using OFFSET on very large tables
• Paginating without ORDER BY
• Assuming pagination is only a frontend concern

Optional Extension (Mini Bonus)

Think about:

• What happens if rows are inserted while paginating?
• Why APIs like Twitter/X avoid OFFSET?
• How indexes change pagination performance

Next Challenge

Beginner Challenge #22: Counting Rows with COUNT()

🔗 View reference solution on GitHub
(After you’ve tried the challenge)

Want more practical SQL challenges?
Subscribe to the Solve With SQL newsletter and get new problems delivered to your inbox.