Pagination

API Design

Meaning

Pagination is a technique for dividing large datasets into discrete pages so clients can retrieve subsets incrementally rather than loading everything at once. It addresses the pain point of timeouts, memory exhaustion, and poor UX when an API or query returns more rows than can be efficiently transmitted or rendered in a single response. It is triggered whenever a result set is large enough that a single response would degrade performance or exceed practical limits.

Primary Function

Data retrieval

Communicative Purpose

Enables incremental loading of large datasets by splitting them into manageable chunks, preventing server timeouts, client memory pressure, and unbounded response sizes.

Pattern

client requests page or cursor → server returns subset plus next token → client repeats until exhausted

Função primária

Data retrieval

Propósito comunicativo

Enables incremental loading of large datasets by splitting them into manageable chunks, preventing server timeouts, client memory pressure, and unbounded response sizes.

Situações de gatilho

REST APIs: returning large collections without overwhelming the client or server

Contextos

REST APIs, GraphQL, relational databases, web frontends, mobile apps, search engines

Padrão

client requests page or cursor → server returns subset plus next token → client repeats until exhausted

Colocados típicos

cursor token
offset
limit
page_size
next_page
has_next
total_count
Link header
opaque token

Substituições comuns

Offset-based vs cursor-based: offset is simpler but degrades at depth
cursor is stable under inserts/deletes but opaque to clients

Erros comuns

Using OFFSET on large tables causes the database to scan and discard skipped rows, making deep pages slow

Similar / contraste

Cursor pagination: stable under concurrent writes; offset pagination: simple but degrades with depth; keyset pagination: efficient for indexed sort columns; infinite scroll: UI pattern built on pagination

Interferências

Coming from SQL OFFSET: may assume offset lookup is O(1) — large offsets require scanning all skipped rows, so deep pagination becomes linear

Família do chunk

offset pagination
cursor pagination
keyset pagination
infinite scroll
page-based navigation
Link header (RFC 5988)

Nuance

When NOT to use: small datasets where loading everything is fine, or real-time streams where cursor semantics break under concurrent inserts

Efeito pragmático

Enables building APIs that scale to millions of records without timeouts or memory blowups, and gives clients a predictable way to walk large result sets.

Dica de memória

Pagination is like a librarian handing you one shelf at a time and pointing to the next shelf — you never carry the whole library, but you always know where to look next.

Upgrade path

Cursor-based pagination with opaque tokens for stable, performant deep pagination over millions of rows

Frequência: HighFormulaicidade: FlexiblePrioridade de aquisição: Active recallPrioridade de output: BothTag de espaçamento: Medium-term

Examples

from flask import Flask, request, jsonify app = Flask(__name__) @app.route('/api/users') def list_users(): page = int(request.args.get('page', 1)) page_size = int(request.args.get('page_size', 20)) offset = (page - 1) * page_size rows = db.execute( "SELECT id, email FROM users ORDER BY id LIMIT %s OFFSET %s", (page_size, offset), ).fetchall() total = db.execute("SELECT COUNT(*) FROM users").scalar() return jsonify({ 'data': [dict(r) for r in rows], 'page': page, 'page_size': page_size, 'total': total, 'has_next': offset + page_size < total, })

def fetch_orders(session, cursor=None, limit=50): if cursor is not None: rows = session.execute( "SELECT id, total, created_at FROM orders " "WHERE id > %s ORDER BY id LIMIT %s", (cursor, limit + 1), ).fetchall() else: rows = session.execute( "SELECT id, total, created_at FROM orders " "ORDER BY id LIMIT %s", (limit + 1,), ).fetchall() has_next = len(rows) > limit items = rows[:limit] next_cursor = items[-1].id if has_next and items else None return {'items': items, 'next_cursor': next_cursor, 'has_next': has_next}

-- First page: newest published articles SELECT id, title, created_at FROM articles WHERE status = 'published' ORDER BY created_at DESC, id DESC LIMIT 20; -- Next page using keyset cursor (last seen created_at and id) SELECT id, title, created_at FROM articles WHERE status = 'published' AND (created_at, id) < ('2024-01-15 10:30:00', 12345) ORDER BY created_at DESC, id DESC LIMIT 20;