In modern web applications, handling large datasets requires efficient pagination. Developers often face three primary challenges: optimizing query performance for deep pages, ensuring data consistency (preventing "drifting"), and maintaining API simplicity. This article explores the two dominant approaches to database pagination: the Offset-based method and the Keyset (Seek-based) method.

Offset-Based Pagination

Offset-based pagination is the traditional approach, utilizing the LIMIT and OFFSET clauses found in most relational databases. It calculates the starting point by skipping a specific number of records.

Core Mechanism

The database engine scans the index (if available) from the beginning, counts the rows to be skipped, and then returns the requested number of records. For example, to retrieve the 20th page with 10 records per page, the database skips 190 rows and fetches the next 10.

Example SQL (PostgreSQL/MySQL):

-- Requesting page 5 with 20 records per page
-- Calculation: offset = (5 - 1) * 20 = 80
SELECT order_id, amount, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 80;

Pros and Cons

• Pros: Extremely easy to implement and supports "jumping" to a specific page number (e.g., clicking page 15 directly).
• Cons:
  • Performance Degradation: As the offset increases, the database must still read all preceding rows before discarding them, leading to significant latency in large tables.
  • Page Drifting: If records are inserted or deleted while a user is navigating, rows may appear twice or be missed entirely because the relative positions of records have changed.

Keyset Pagination (The Seek Method)

Keyset pagination, often called Cursor-based pagination, uses a value (or a set of values) from the last record of the previous page as a "pointer" to find the next set of data.

Core Mechanism

Instead of skipping rows, the query uses a WHERE filter on the indexed sorting columns. This allows the database to jump directly to the starting point via an index seek.

Example SQL (Multi-column Seek):

-- Assume the last record of page 1 had:
-- created_at = '2023-11-15 14:00:00' and order_id = 5021
SELECT order_id, amount, created_at
FROM orders
WHERE (created_at, order_id) < ('2023-11-15 14:00:00', 5021)
ORDER BY created_at DESC, order_id DESC
LIMIT 20;

Note: The sorting must be deterministic. If created_at is not unique, the primary key (order_id) must be included in both the sort and the filter to ensure a stable sequence.

Pros and Cons

• Pros:
  • Constant Performance: Query execution time remains stable regardless of how "deep" the user scrolls.
  • Stability: New inserts before the current page do not cause items to shift between pages.
• Cons:
  • No Random Access: Users cannot jump to page 500; they can only move to the "next" or "previous" page.
  • Implementation Complexity: Requires menaging state (cursors) and ensuring composite indexes are correctly configured.

NoSQL and Search Engine Implementations

Differant data stores offer specific tools for these patterns:

• Elasticsearch: While from and size handle offset pagination, the search_after parameter is the recommended approach for deep pagination, functioning similarly to the Keyset method.
• MongoDB: The skip() method suffers from the same performance issues as SQL OFFSET. Range-based queries on _id or custom indexed fields are preferred for high-volume collections.

API Design Best Practices

Offset-Based Interface

Standard for administrative dashboards where page numbers are expected.

GET /v1/orders?page=2&size=50

{
  "items": [...],
  "pagination": {
    "total_count": 1200,
    "total_pages": 24,
    "current_page": 2
  }
}

Cursor-Based Interface

Ideal for mobile feeds or infinite scroll interfaces.

GET /v1/orders?limit=50&cursor=Y3JlYXRlZF9hdDoxNjk5OTk5OTk5

{
  "items": [...],
  "paging": {
    "next_cursor": "Y3JlYXRlZF9hdDoxNjk4ODg4ODg4",
    "has_more": true
  }
}

Comparison Summary

Strategic Recommendations

1. Index Coverage: Regardless of the method, ensure that the columns used in ORDER BY are covered by an appropriate index.
2. The Count Penalty: Calculating total_items requires a COUNT(*), which can be expensive on large tables. For Keyset pagination, consider omitting the total count or using an estimate.
3. Hybrid Approach: For systems requiring both features, use Offset for the first few pages and force a Keyset/Cursor flow for deeper navigation.