Pagination Performance

title: "Pagination Performance — Optimizing Paginated Database Queries" description: "Pagination performance optimization focuses on using indexed keys, avoiding deep offsets, and leveraging cursor-based pagination for large-scale database queries." date: 2026-06-28 lastmod: 2026-06-28 weight: 24 tags: [apis, pagination] }

Pagination performance optimization addresses the slowdown of deep offset queries by using indexed cursor lookups, covering indexes, and database-specific optimizations.

What You'll Learn

• Why offset pagination slows at scale
• Using covering indexes for pagination
• Database-specific pagination optimizations

Why It Matters

A paginated endpoint that works at page 10 may take 30 seconds at page 10,000. Optimizing pagination ensures consistent response times at any page depth.

Code Examples

-- Slow: offset scans and discards rows
EXPLAIN ANALYZE
SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 10000;
-- -> 10020 rows scanned, 20 returned

-- Fast: cursor uses index seek
EXPLAIN ANALYZE
SELECT * FROM users WHERE id > 10000 ORDER BY id LIMIT 20;
-- -> 20 rows scanned (index seek + range scan)

-- Covering index for pagination
CREATE INDEX idx_users_page ON users (id) INCLUDE (name, email, status);

-- Keyset pagination with composite index
CREATE INDEX idx_users_created ON users (created_at, id);

SELECT * FROM users
WHERE (created_at, id) < ('2026-06-28', 9999)
ORDER BY created_at DESC, id DESC
LIMIT 20;

# Performance comparison: offset vs cursor @ 100000 rows
# Offset: 200ms at page 1, 2500ms at page 5000
# Cursor: 3ms at any depth

@app.route('/slow-users')
def slow_users():
    page = request.args.get('page', 1, type=int)
    return db.execute("SELECT * FROM users ORDER BY id LIMIT 20 OFFSET ?",
                      [(page - 1) * 20])

@app.route('/fast-users')
def fast_users():
    cursor = request.args.get('cursor', type=int)
    return db.execute("SELECT * FROM users WHERE id > ? ORDER BY id LIMIT 20",
                      [cursor or 0])

Common Mistakes

1. No EXPLAIN ANALYZE

Always verify query performance with EXPLAIN ANALYZE or equivalent.

2. Missing Composite Indexes

cursor pagination requires indexes on (sort_column, id).

3. SELECT * on Wide Tables

Select only needed columns to reduce I/O.

4. Count Performance Offset

Total COUNT on 10M rows is slow. Use estimated counts.

5. N+1 Pagination in ORM

ORMs may generate N+1 queries for paginated data. Use eager loading.

Practice Questions

1. Why does offset pagination slow down?
2. How do covering indexes help pagination?
3. What is the keyset pagination technique?
4. How do you measure pagination performance?
5. What is the ideal index for cursor pagination by created_at?

Answers:

1. The database scans and discards offset rows before returning results.
2. They include all needed columns, avoiding table lookups.
3. Using WHERE clauses instead of OFFSET with composite indexes.
4. Use EXPLAIN ANALYZE and measure p50/p99 response times.
5. Composite index on (created_at, id).

Challenge: Optimize a paginated query that takes 10 seconds on page 10,000. Implement keyset pagination, add proper indexes, and measure the improvement.

FAQ