Course contentsShow
← Software Engineering
Lesson 886 of 1,93327. Database Design in PracticeFree lesson

Pagination and Large Result Sets

Learn efficient pagination techniques using keyset pagination and avoiding OFFSET with LIMIT for large datasets.

Pagination and Large Result Sets

You'll learn efficient techniques for paginating large datasets without killing database performance.

The Problem with Simple Pagination

When displaying large result sets (think thousands or millions of rows), you can't return everything at once. The naive approach uses LIMIT and OFFSET:

SELECT * FROM products 
ORDER BY created_at 
LIMIT 20 OFFSET 200;

This works for small offsets, but becomes disastrously slow as OFFSET grows. Why? The database must scan and skip all those offset rows on every request. At OFFSET 1000000, it reads a million rows just to throw them away.

Keyset Pagination (Cursor-Based)

Keyset pagination uses the last seen value as a filter instead of counting rows:

-- First page
SELECT * FROM products 
ORDER BY created_at, id 
LIMIT 20;

-- Next page (using last row's values: created_at='2024-01-15', id=450)
SELECT * FROM products 
WHERE (created_at, id) > ('2024-01-15', 450)
ORDER BY created_at, id 
LIMIT 20;

This approach leverages indexes to seek directly to the starting point—no skipping required. Performance stays constant whether you're on page 2 or page 20,000.

Why It Works

Think of OFFSET like counting pages in a phone book by flipping through them one-by-one. Keyset pagination is like opening directly to "Smith" because the book is alphabetically indexed—you jump straight there.

Key requirements:

  • Include a unique column (like id) in the sort for tie-breaking
  • Index must cover your ordering columns
  • Trade-off: can't jump to arbitrary page numbers (only next/previous)

Key Takeaway: For large datasets, avoid OFFSET entirely and use keyset pagination with indexed columns to maintain consistent, fast performance regardless of page depth.