Your API Is Slower Than It Needs to Be and Pagination Is Probably Why

The endpoint that seemed fine in development

In development, your database has 200 records. GET /orders returns all of them in 12ms. The developer integrating your API fetches everything in one call and builds their UI. Simple.

Three years later, the database has 4 million records. GET /orders times out. The developer's code has no pagination logic because the endpoint never required it. You have a mutual problem.

This is not hypothetical — it is the standard trajectory for list endpoints that ship without pagination. The fix requires coordinated changes on both sides after the damage is done.

Offset vs. cursor pagination

Offset pagination:

GET /orders?offset=200&limit=50

Intuitive and easy to implement. The database query is LIMIT 50 OFFSET 200.

The problem: at large offsets, the database still scans all preceding rows before returning results. OFFSET 200000 LIMIT 50 on a table of 2 million rows performs a full index scan of 200,050 rows to return 50. On PostgreSQL with a btree index, expect this to degrade roughly linearly with offset size. At offset 1 million on a table of 5 million rows, you are waiting on a scan of 1 million index entries for every request.

Offset pagination also has a consistency problem: if records are inserted or deleted between page requests, offsets shift and you either skip or duplicate records.

Cursor pagination:

GET /orders?limit=50&after=01HZQK7P3WVXBN

The cursor is an opaque token that encodes the position in the result set — typically the ID or a timestamp of the last item returned. The query becomes:

SELECT * FROM orders
WHERE id > '01HZQK7P3WVXBN'
ORDER BY id ASC
LIMIT 50

This is an index seek, not a scan. Performance is constant regardless of cursor depth. At page 100,000, it is as fast as page 1.

The tradeoffs: no random access to page N, no total count without a separate query, and the cursor must encode the sort key. If the client needs "jump to page 47," cursor pagination cannot do it. If the client needs to stream through millions of records efficiently, offset cannot do it reasonably.

Making the cursor opaque

Never expose raw database IDs as cursors. If your cursor is after=42, clients will use it as an integer and build logic around the ID semantics. When you change the sort order or the ID format, you break clients.

Encode the cursor as a base64 string of the underlying value:

import base64
import json

def encode_cursor(last_id: str, last_created_at: str) -> str:
    payload = json.dumps({"id": last_id, "created_at": last_created_at})
    return base64.urlsafe_b64encode(payload.encode()).decode()

def decode_cursor(cursor: str) -> dict:
    payload = base64.urlsafe_b64decode(cursor.encode()).decode()
    return json.loads(payload)

The client treats the cursor string as opaque. You can change the internal representation without breaking the interface.

The response envelope

Include navigation context in every paginated response:

{
  "data": [...],
  "pagination": {
    "limit": 50,
    "has_more": true,
    "next_cursor": "eyJpZCI6IjAxSFpRS..."
  }
}

has_more lets the client know whether to continue fetching without needing a total count. next_cursor is null when there are no more results.

Avoid including a total_count unless you actually need it. COUNT(*) on a large table with filters is expensive. If you include it by default, you are running a count query on every page fetch. Clients rarely need the exact total — they need to know if there is more. has_more serves that need at zero extra cost.

Default limits matter

If your endpoint defaults to no limit, eventually a client will call it without specifying one and get all 4 million records. Add a sensible default limit at the framework level and enforce a maximum:

@app.get("/orders")
def list_orders(
    after: Optional[str] = None,
    limit: int = Query(default=50, ge=1, le=500)
):
    ...

le=500 means the client cannot ask for more than 500 records per page regardless of what they send. Default of 50 means a client that does not specify a limit gets a reasonable number, not everything.

The index you need

Cursor pagination is only fast if the column you are paginating on is indexed. For the query WHERE id > :cursor ORDER BY id LIMIT 50, you need an index on id. For WHERE created_at > :cursor AND account_id = :account ORDER BY created_at LIMIT 50, you need a composite index on (account_id, created_at).

Check EXPLAIN ANALYZE output to confirm your pagination queries are doing index seeks, not sequential scans. An unindexed cursor column gives you the syntax of cursor pagination with the performance of a full table scan.

Retrofit path for existing unbound endpoints

If you have live clients calling unbounded list endpoints today, you cannot suddenly enforce a limit and break them. Add the pagination parameters as optional, apply the limit only when they are present, and use the Deprecation header to signal that the unbounded behavior is going away:

Deprecation: true
Sunset: 2027-01-01
Link: https://api.example.com/docs/pagination; rel="deprecation"

Give clients 12 months. Monitor which clients are still not paginating. Contact them directly. Remove the unbounded path on the announced date.