Pattern: API pagination with cursor-based approach

Offset-based pagination (LIMIT/OFFSET) becomes slow on large datasets because the database must scan and discard offset rows.

Use cursor-based (keyset) pagination for consistent performance:

# Offset-based (slow for large offsets):
SELECT * FROM posts ORDER BY id LIMIT 20 OFFSET 10000;
-- DB reads 10,020 rows, discards 10,000

# Cursor-based (constant performance):
SELECT * FROM posts
WHERE id > :last_seen_id -- cursor
ORDER BY id
LIMIT 20;
-- DB uses index, reads exactly 20 rows

# API response format:
{
"data": [...],
"pagination": {
"next_cursor": "eyJpZCI6MTIzfQ==", // Base64 encoded
"has_more": true
}
}

# For non-sequential ordering (e.g., by created_at):
SELECT * FROM posts
WHERE (created_at, id) < (:cursor_date, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- Compound cursor handles ties in created_at

# Python FastAPI example:
@app.get('/posts')
async def list_posts(cursor: str = None, limit: int = 20):
query = select(Post).order_by(Post.id)
if cursor:
last_id = decode_cursor(cursor)
query = query.where(Post.id > last_id)
posts = await db.execute(query.limit(limit + 1))
has_more = len(posts) > limit
return {
'data': posts[:limit],
'next_cursor': encode_cursor(posts[-1].id) if has_more else None,
}

Cursor pagination uses index seeks (O(log n)) vs offset scans (O(n)). Performance is constant regardless of which page you're on.