Optimizing PostgreSQL chevron_right Efficient Pagination in PostgreSQL: OFFSET vs. Keyset Pagination

Efficient Pagination in PostgreSQL: OFFSET vs. Keyset Pagination

calendar_today schedule 5 min read

Learning Objectives: By the end of this lesson, you will be able to:

  • Explain the differences between OFFSET and keyset pagination
  • Implement both pagination strategies in PostgreSQL
  • Analyze and optimize pagination performance for large datasets

1. Welcome and Introduction (5 minutes)

Icebreaker Activity

Have you ever waited for a web page to load a “page 100” of results and wondered why it was so slow? What kind of pagination have you used in your own projects?

Lesson Overview

  • Duration: 90 minutes
  • Prerequisites: Basic SQL, understanding of SELECT, WHERE, ORDER BY, and indexes
  • Materials Needed: PostgreSQL database, SQL client, large sample table (e.g., 100,000+ rows)

2. Learning Objectives and Session Outline (5 minutes)

What We’ll Cover Today:

  1. How OFFSET and LIMIT work in PostgreSQL
  2. Why OFFSET gets slow for large offsets
  3. Keyset (seek method) pagination: concept and implementation
  4. Hands-on exercises comparing both methods
  5. Best practices and troubleshooting

Success Criteria:

By the end of this session, you should be able to choose and implement the best pagination strategy for your use case.

3. Foundation Knowledge (15-20 minutes)

Core Concepts

OFFSET and LIMIT

OFFSET skips a number of rows before returning results, while LIMIT restricts the number of rows returned.

Real-World Analogy: Like flipping through a book to page 100—OFFSET makes the database “read” all previous pages first.

Why This Matters: OFFSET is simple but can be very slow for large page numbers.

Keyset Pagination (Seek Method)

Keyset pagination uses a WHERE clause to “seek” to the starting point, usually based on a unique, indexed column.

Real-World Analogy: Like using a bookmark to jump directly to the right page, instead of counting every page.

Why This Matters: Keyset pagination is much faster for deep pages, especially on large tables.

Key Terminology

  • OFFSET: Number of rows to skip
  • LIMIT: Maximum number of rows to return
  • Keyset Pagination: Pagination using a WHERE clause on a unique/indexed column

4. Detailed Explanation and Examples (25-30 minutes)

Understanding Pagination Performance

OFFSET requires the database to scan and discard all skipped rows, which is inefficient for large offsets. Keyset pagination uses an indexed value to jump directly to the desired starting point.

Practical Applications

  • Web applications with infinite scroll or “next page” buttons
  • APIs returning paginated data
  • Admin dashboards with large datasets

Common Misconceptions

  1. Misconception: “OFFSET is fine for any size table.” Reality: OFFSET performance degrades linearly with the offset value.
  2. Misconception: “Keyset pagination is too complex to implement.” Reality: It’s straightforward with a unique, indexed column.

5. Live Coding Demonstration (20-25 minutes)

Example 1: OFFSET/LIMIT Pagination

SELECT * FROM orders
ORDER BY id
OFFSET 10000 LIMIT 20;

Explanation:

  • Skips 10,000 rows, then returns 20 rows.
  • Slow for large OFFSET values.

Example 2: Keyset Pagination (Seek Method)

-- Assume last seen id is 10000
SELECT * FROM orders
WHERE id > 10000
ORDER BY id
LIMIT 20;

Key Points:

  • Uses an indexed column to “seek” to the starting point
  • Much faster for deep pages

Example 3: Keyset Pagination with Multiple Columns

-- For ordering by (created_at, id)
SELECT * FROM orders
WHERE (created_at, id) > ('2024-01-01', 10000)
ORDER BY created_at, id
LIMIT 20;

Debugging Demonstration

-- Compare EXPLAIN ANALYZE for both methods
EXPLAIN ANALYZE SELECT * FROM orders ORDER BY id OFFSET 50000 LIMIT 20;
EXPLAIN ANALYZE SELECT * FROM orders WHERE id > 50000 ORDER BY id LIMIT 20;

6. Hands-On Practice (30-40 minutes)

Exercise 1: Basic Practice (10 minutes)

Objective: Implement OFFSET/LIMIT pagination on a large table.

Instructions:

  1. Write a query to fetch page 10 (20 rows per page)
  2. Measure performance with EXPLAIN ANALYZE

Starter Code:

SELECT * FROM products ORDER BY id OFFSET 180 LIMIT 20;

Solution:

EXPLAIN ANALYZE SELECT * FROM products ORDER BY id OFFSET 180 LIMIT 20;

Exercise 2: Intermediate Challenge (15 minutes)

Objective: Implement keyset pagination for the same table.

Requirements:

  • Use a unique, indexed column (e.g., id)
  • Fetch the next 20 rows after a given id

Hints:

  • Use WHERE id > last_seen_id

Exercise 3: Creative Application (15 minutes)

Objective: Implement keyset pagination for a table ordered by (created_at, id).

Challenge:

  • Use a composite WHERE clause
  • Fetch the next 20 rows after a given (created_at, id)

Extension Ideas:

  • Add filters (e.g., category)
  • Implement “previous page” logic

7. Common Pitfalls and Troubleshooting (10 minutes)

Frequent Mistakes

  1. Problem: Using OFFSET for deep pages Cause: Slow queries due to scanning many rows Solution: Use keyset pagination for large datasets

  2. Problem: No index on pagination columns Cause: Full table scans Solution: Add appropriate indexes

Debugging Tips

  • Use EXPLAIN ANALYZE to compare query plans
  • Check for sequential scans on large tables
  • Test with realistic data sizes

8. Best Practices and Professional Tips (5 minutes)

Industry Standards

  • Use OFFSET/LIMIT for small datasets or admin tools
  • Use keyset pagination for user-facing, large-scale applications
  • Always index columns used for keyset pagination

Next Steps in Your Learning Journey

  • Explore cursor-based pagination in APIs
  • Learn about window functions for advanced paging
  • Practice optimizing pagination on real-world data

9. Summary and Review (5 minutes)

Key Takeaways

  1. OFFSET/LIMIT is simple but slow for large offsets
  2. Keyset pagination is efficient for deep paging
  3. Indexes are critical for fast pagination

Quick Review Questions

  1. Why does OFFSET get slower as the page number increases?
  2. How does keyset pagination improve performance?
  3. What is required for efficient keyset pagination?

For Instructors

Teaching Tips

  • Demonstrate performance differences with EXPLAIN ANALYZE
  • Encourage students to implement both methods and compare
  • Discuss real-world use cases for each approach

Assessment Ideas

  • Have students refactor OFFSET queries to use keyset pagination
  • Peer review of pagination strategies
  • Quiz on pagination concepts and performance

Adaptation Notes

  • For beginners: Focus on OFFSET/LIMIT basics
  • For advanced: Cover composite keyset pagination and API integration
  • Online: Use shared databases for hands-on practice
  • In-person: Group query optimization challenges