Pagination Patterns

OFFSET/LIMIT Pagination

The most common pagination approach uses OFFSET to skip rows and LIMIT to return a fixed number of rows per page.

Basic Syntax

-- Page 1: First 10 rows
SELECT * FROM orders 
ORDER BY order_date DESC 
LIMIT 10 OFFSET 0;

-- Page 2: Next 10 rows
SELECT * FROM orders 
ORDER BY order_date DESC 
LIMIT 10 OFFSET 10;

-- Page 3: Next 10 rows
SELECT * FROM orders 
ORDER BY order_date DESC 
LIMIT 10 OFFSET 20;

-- General formula for page P with size S:
-- OFFSET = (P - 1) × S
-- LIMIT = S

Application Code Example

def get_orders_page(page_number, page_size=10):
    offset = (page_number - 1) * page_size
    query = f"""
        SELECT id, customer_id, total, order_date
        FROM orders 
        ORDER BY order_date DESC 
        LIMIT {page_size} OFFSET {offset}
    """
    return db.execute(query)

# Page 1
orders = get_orders_page(1)  # OFFSET 0, LIMIT 10

# Page 2
orders = get_orders_page(2)  # OFFSET 10, LIMIT 10

Total Count for UI

-- Get total count for pagination UI
SELECT COUNT(*) FROM orders;

-- Or with filters
SELECT COUNT(*) FROM orders WHERE status = 'pending';

-- Combined query
SELECT 
    o.*, 
    (SELECT COUNT(*) FROM orders WHERE status = 'pending') as total_count
FROM orders o
WHERE o.status = 'pending'
ORDER BY o.order_date DESC
LIMIT 10 OFFSET 20;

Limitations

• Slow for large offsets: OFFSET 1000000 must scan and skip 1M rows
• Inconsistent results: Rows may appear/disappear if data changes between pages
• Database must process all skipped rows even though they're not returned

Why Large Offsets Are Slow

OFFSET-based pagination degrades significantly as the offset increases. The database must scan and discard all skipped rows before returning results.

How OFFSET Works Internally

-- OFFSET 10: Database scans 10 rows, discards them, returns next 10
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 10;
-- Scans 20 rows, returns 10

-- OFFSET 1000000: Database scans 1M rows, discards them!
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 1000000;
-- Scans 1,000,010 rows, returns 10

Performance Comparison

OFFSET 0:        2ms    (scan 10 rows)
OFFSET 100:      5ms    (scan 110 rows)
OFFSET 1,000:    25ms   (scan 1,010 rows)
OFFSET 10,000:   200ms  (scan 10,010 rows)
OFFSET 100,000:  2.0s   (scan 100,010 rows)
OFFSET 1,000,000: 20s   (scan 1,000,010 rows)

Linear degradation!

Visual Explanation

Table: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]

OFFSET 0, LIMIT 3:  Return [1, 2, 3]
OFFSET 3, LIMIT 3:  Return [4, 5, 6]
OFFSET 6, LIMIT 3:  Return [7, 8, 9]

To get [7, 8, 9], database must:
1. Read rows 1-6 (skip them)
2. Return rows 7-9

Mitigation Strategies

-- 1. Cap maximum page size
SELECT * FROM orders 
ORDER BY order_date DESC 
LIMIT LEAST(100, :requested_limit) OFFSET :offset;

-- 2. Warn users about deep pages
-- "Showing results 1-10 of 1,000,000. Use search for more specific results."

-- 3. Use keyset pagination instead (see next chapter)

-- 4. Cache total counts
-- Don't run COUNT(*) on every page request

Real-World Example

-- E-commerce product listing
-- Page 1: Fast
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 0;

-- Page 50,000: Very slow
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 999980;
-- Must scan 1,000,000 rows to skip them!

-- Solution: Use keyset pagination
SELECT * FROM products WHERE id > 999980 ORDER BY id LIMIT 20;
-- Uses index, scans only 20 rows!

Keyset Pagination

Keyset pagination (also called cursor-based pagination) uses the last seen value to fetch the next page. Instead of skipping rows with OFFSET, it uses a WHERE clause to start from a specific point.

Basic Concept

-- Instead of: OFFSET 100, LIMIT 10
-- Use: WHERE id > last_seen_id

-- Page 1
SELECT * FROM orders 
ORDER BY id 
LIMIT 10;
-- Returns rows with ids: 1, 2, 3, ..., 10
-- Last seen id: 10

-- Page 2 (using last seen id)
SELECT * FROM orders 
WHERE id > 10 
ORDER BY id 
LIMIT 10;
-- Returns rows with ids: 11, 12, 13, ..., 20
-- Last seen id: 20

-- Page 3
SELECT * FROM orders 
WHERE id > 20 
ORDER BY id 
LIMIT 10;
-- Returns rows with ids: 21, 22, 23, ..., 30

Application Code

def get_orders_cursor(cursor=None, page_size=10):
    if cursor is None:
        # First page
        query = """
            SELECT id, customer_id, total, order_date
            FROM orders 
            ORDER BY id 
            LIMIT %s
        """
        params = [page_size]
    else:
        # Subsequent pages
        query = """
            SELECT id, customer_id, total, order_date
            FROM orders 
            WHERE id > %s
            ORDER BY id 
            LIMIT %s
        """
        params = [cursor, page_size]
    
    rows = db.execute(query, params)
    
    # Next cursor is the last row's id
    next_cursor = rows[-1]['id'] if rows else None
    
    return rows, next_cursor

# Usage
page1, cursor1 = get_orders_cursor()
page2, cursor2 = get_orders_cursor(cursor=cursor1)
page3, cursor3 = get_orders_cursor(cursor=cursor2)

Keyset with Composite Sort

-- Sort by order_date DESC, then id (for tiebreaking)
SELECT * FROM orders 
WHERE (order_date, id) < ('2024-01-15', 12345)
ORDER BY order_date DESC, id DESC 
LIMIT 10;

-- Or using row value constructor (PostgreSQL, MySQL)
SELECT * FROM orders 
WHERE order_date < '2024-01-15' 
   OR (order_date = '2024-01-15' AND id < 12345)
ORDER BY order_date DESC, id DESC 
LIMIT 10;

Cursor Encoding (API Design)

import base64
import json

def encode_cursor(order_id, order_date):
    """Encode cursor for API response."""
    data = json.dumps({'id': order_id, 'date': str(order_date)})
    return base64.b64encode(data.encode()).decode()

def decode_cursor(cursor):
    """Decode cursor from API request."""
    data = json.loads(base64.b64decode(cursor).decode())
    return data['id'], data['date']

# API response
{
    "data": [...],
    "next_cursor": "eyJpZCI6IDEyMzQ1LCAiZGF0ZSI6ICIyMDI0LTAxLTE1In0="
}

Keyset Pagination Advantages

Advantage	Description
Consistent performance	Always scans same number of rows
No skipped rows	No data changes between pages
Index-friendly	Uses index for both seek and sort
Scalable	Works well with millions of rows

Offset vs Keyset: When to Use Each

Both approaches have their place. Understanding the trade-offs helps you choose the right one.

Direct Comparison

Feature	OFFSET/LIMIT	Keyset/Cursor
Performance	Degrades with depth	Constant
Jump to page	Yes (any page)	No (sequential only)
Random access	Yes	No
Consistent results	No (may shift)	Yes
Index usage	Partial	Full
Complexity	Simple	Moderate
UI support	Page numbers	Infinite scroll

When to Use OFFSET/LIMIT

-- Use OFFSET when:
-- 1. Users need to jump to specific pages
-- 2. Dataset is small (< 100K rows)
-- 3. Page numbers are required in UI
-- 4. Data doesn't change frequently

-- Example: Admin dashboard with page numbers
SELECT * FROM orders 
ORDER BY order_date DESC 
LIMIT 25 OFFSET 50;  -- Page 3 of admin list

When to Use Keyset

-- Use Keyset when:
-- 1. Large datasets (millions of rows)
-- 2. Infinite scroll UI
-- 3. Real-time data (frequent inserts)
-- 4. API pagination (cursor-based)

-- Example: Social media feed
SELECT * FROM posts 
WHERE created_at < :last_seen_time 
ORDER BY created_at DESC 
LIMIT 20;

Hybrid Approach

-- Use OFFSET for first few pages, keyset for deep pages

-- Page 1-100: Use OFFSET (fast enough)
SELECT * FROM orders 
ORDER BY order_date DESC 
LIMIT 25 OFFSET 0;  -- Page 1

-- Page 100+: Switch to keyset
SELECT * FROM orders 
WHERE order_date < :last_date_seen 
ORDER BY order_date DESC 
LIMIT 25;

Performance Benchmarks

Dataset: 10 million rows, page size 20

OFFSET Pagination:
  Page 1:     2ms
  Page 100:   15ms
  Page 1000:  120ms
  Page 10000: 1.2s
  Page 50000: 6.0s

Keyset Pagination:
  Page 1:     2ms
  Page 100:   2ms
  Page 1000:  2ms
  Page 10000: 2ms
  Page 50000: 2ms

Keyset maintains constant time!

Decision Matrix

Scenario	Recommendation
Admin panel, < 100K rows	OFFSET
Social media feed	Keyset
API with cursors	Keyset
E-commerce listing, < 50K	OFFSET
E-commerce listing, > 1M	Keyset
Search results	OFFSET
Real-time chat history	Keyset

Practical Guidelines

OFFSET/LIMIT: Best For

-- 1. Small to medium datasets
SELECT * FROM products 
WHERE category = 'electronics' 
ORDER BY name 
LIMIT 20 OFFSET 0;
-- Only 500 products in category, OFFSET is fine

-- 2. When users need page numbers
-- "Page 1 of 25" in the UI
SELECT COUNT(*) FROM products WHERE category = 'electronics';  -- 500 total
-- 500 / 20 = 25 pages

-- 3. When data is relatively static
-- Reference data, archived orders, etc.

-- 4. Simple implementation
-- Easy to understand and debug

Keyset: Best For

-- 1. Large, continuously growing datasets
SELECT * FROM events 
WHERE id > :last_event_id 
ORDER BY id 
LIMIT 50;
-- Millions of events, always fast

-- 2. Infinite scroll / real-time feeds
SELECT * FROM messages 
WHERE created_at < :last_message_time 
ORDER BY created_at DESC 
LIMIT 20;
-- New messages arrive constantly

-- 3. API pagination
-- "Get next page" instead of "Go to page 5"

-- 4. Performance-critical paths
-- Every millisecond matters

Example: E-commerce Site

-- Homepage: Featured products (small set, OFFSET fine)
SELECT * FROM products 
WHERE featured = true 
ORDER BY popularity DESC 
LIMIT 12 OFFSET 0;

-- Category page: All products (medium set, OFFSET fine)
SELECT * FROM products 
WHERE category_id = 5 
ORDER BY created_at DESC 
LIMIT 24 OFFSET 0;

-- Search results: All products (large set, consider keyset)
SELECT * FROM products 
WHERE name ILIKE '%wireless%' 
ORDER BY relevance DESC, id 
LIMIT 24 OFFSET 0;
-- For deep pagination in search, use keyset

Migration Strategy

-- If you're currently using OFFSET and hitting performance issues:

-- Step 1: Add cursor support to your API
-- Step 2: Keep OFFSET for backward compatibility
-- Step 3: New clients use cursor-based API
-- Step 4: Deprecate OFFSET API when migration complete

-- Example: Dual pagination support
-- GET /api/orders?page=2&page_size=20           (OFFSET)
-- GET /api/orders?cursor=abc123&limit=20        (KEYSET)