Course contentsShow
← System Design
Lesson 1888 of 1,91945. API Design and VersioningFree lesson

Offset-Based Pagination

Using LIMIT/OFFSET parameters, their simplicity, and why they break with concurrent modifications.

Offset-Based Pagination

What you'll learn: How the simplest pagination approach works using LIMIT and OFFSET parameters, and why it can fail when data changes concurrently.

The Core Idea

Offset-based pagination is the most intuitive way to split large result sets into pages. You specify two parameters:

  • LIMIT: How many items per page
  • OFFSET: How many items to skip before starting

For example, fetching page 3 with 20 items per page means skipping the first 40 items (OFFSET 40) and returning the next 20 (LIMIT 20). The formula is simple: OFFSET = (page_number - 1) × page_size.

Why It's Attractive

This approach maps perfectly to how databases work: SELECT * FROM items LIMIT 20 OFFSET 40. It's stateless—the client just sends a page number, and the server calculates the offset. No need to track cursors or tokens between requests.

The Fatal Flaw: Concurrent Modifications

Imagine browsing page 2 while someone adds a new item to the top of the list. When you click "Next" to page 3, your offset calculation is now wrong—you'll skip an item or see duplicates. Similarly, if items are deleted, you might see the same item twice across pages.

Analogy: Picture a numbered queue at a deli. You're called as "number 30," but while you were waiting, three people left. Now position 30 is someone completely different than when you joined.

At high scale with frequent writes, offset-based pagination becomes unreliable and can confuse users with inconsistent results across page views.

Key Takeaway: Offset-based pagination is simple and stateless but breaks consistency when the underlying dataset changes between page requests—a critical limitation for systems with concurrent writes.