RDRYUSLOG DEV
Back to Blog
Python

Python FastAPI Pagination: Offset vs Cursor

python fastapi pagination: Learn how to implement offset and cursor-based pagination in Python FastAPI, including response models, validation, and performance tradeoff...

FastAPIPaginationSQLAlchemyREST APICursor
Illustration of a FastAPI pagination endpoint showing a list of items with offset and cursor navigation arrows.

python fastapi pagination requires a clear understanding of the core syntax, runtime behavior, and practical implementation patterns demonstrated in the examples below.

FastAPI does not ship with a built-in pagination helper, so every API that returns a list of records eventually needs an explicit strategy. The choice between offset-based and cursor-based pagination affects query efficiency, response shape, and how clients navigate the dataset. This article walks through implementing both approaches in Python FastAPI, including response models, validation, and the tradeoffs that matter in production.

The Core Pagination Decision in FastAPI

Pagination in a FastAPI endpoint usually comes down to two parameters: how many items to return and where to start. The simplest implementation reads limit and offset from the query string and passes them directly to a database query. That works well for small datasets, but the database must scan and discard rows up to the offset on every request. As the offset grows, the cost increases even if the result set stays small.

Cursor-based pagination avoids that problem by using a unique, sortable column as a bookmark. Instead of skipping rows, the query filters on that column with a comparison operator. The client receives an opaque cursor that encodes the position of the last item, and the next request uses that cursor to fetch the following batch. This approach is stable even when new rows are inserted between requests, but it requires a unique ordering key and slightly more complex response handling.

The rest of this article shows concrete implementations for both strategies in FastAPI, along with the response models and validation logic you need to make them production-ready.

Offset and Limit Pagination: The Simplest Approach

Offset pagination is the most direct way to implement pagination in FastAPI. You define two query parameters, offset and limit, and pass them to your database query. Here is a minimal example using SQLAlchemy's ORM:

from fastapi import FastAPI, Query, Depends
from sqlalchemy.orm import Session
from sqlalchemy import select
from .database import get_db
from .models import Item

app = FastAPI()

@app.get("/items")
def list_items(
    offset: int = Query(0, ge=0),
    limit: int = Query(20, ge=1, le=100),
    db: Session = Depends(get_db),
):
    items = db.scalars(select(Item).offset(offset).limit(limit)).all()
    return {"items": items, "offset": offset, "limit": limit}

This endpoint returns a list of items along with the offset and limit that were used. The Query validation ensures offset is non-negative and limit is between 1 and 100, which prevents clients from requesting absurdly large pages. The database query uses OFFSET and LIMIT clauses, which are supported by virtually every relational database.

For a small table, this implementation is perfectly adequate. The main limitation appears when the offset becomes large. Suppose you have a million rows and a client requests offset=900000&limit=20. The database must scan and discard 900,000 rows before returning the 20 you asked for. That work happens on every request, and it gets worse as the dataset grows.

Another issue is page drift. If new rows are inserted between two requests, the offset no longer points to the same logical position. A client that fetched page 1 and then page 2 might see the same row twice or skip a row entirely. For many applications this is acceptable, but it can be a problem for real-time feeds or audit trails.

Page-Number Pagination: A Thin Wrapper Over Offset

Many APIs expose pagination as page and page_size instead of offset and limit. This is just a different way to express the same offset calculation: offset = (page - 1) * page_size. The FastAPI endpoint can accept these parameters and convert them internally.

@app.get("/items/page")
def list_items_page(
    page: int = Query(1, ge=1),
    page_size: int = Query(20, ge=1, le=100),
    db: Session = Depends(get_db),
):
    offset = (page - 1) * page_size
    items = db.scalars(select(Item).offset(offset).limit(page_size)).all()
    total = db.scalar(select(func.count()).select_from(Item)) or 0
    return {
        "items": items,
        "page": page,
        "page_size": page_size,
        "total": total,
    }

Here the endpoint also returns the total number of records, which clients often need to render a page-number control. The total count requires a separate COUNT(*) query, which can be expensive on large tables. If you do not need the total, omit it to save a round trip.

Page-number pagination is convenient for clients because they can jump directly to page 5 without tracking a cursor. However, it inherits all the performance and drift problems of offset pagination. The offset calculation is straightforward, but the underlying database still has to scan and discard rows.

Cursor-Based Pagination: Stable Ordering for Large Datasets

Cursor-based pagination uses a unique, sortable column to define the position of the last returned item. The client sends a cursor that encodes that position, and the server returns the next batch after it. This approach is common in APIs that expose large, frequently updated datasets, such as social media feeds or event streams.

A typical implementation uses an integer primary key as the cursor. The endpoint accepts a cursor query parameter, which is an opaque string that the server decodes into a numeric ID. The query then uses a WHERE id > :cursor condition instead of an offset.

import base64
from pydantic import BaseModel

class CursorPage(BaseModel):
    items: list[Item]
    next_cursor: str | None

@app.get("/items/cursor")
def list_items_cursor(
    cursor: str | None = None,
    limit: int = Query(20, ge=1, le=100),
    db: Session = Depends(get_db),
):
    query = select(Item).order_by(Item.id).limit(limit)
    if cursor:
        decoded = base64.urlsafe_b64decode(cursor.encode()).decode()
        last_id = int(decoded)
        query = query.where(Item.id > last_id)
    items = db.scalars(query).all()
    next_cursor = None
    if len(items) == limit:
        last_item = items[-1]
        next_cursor = base64.urlsafe_b64encode(str(last_item.id).encode()).decode()
    return CursorPage(items=items, next_cursor=next_cursor)

The cursor is base64-encoded to keep it opaque and URL-safe. The server decodes it, extracts the ID, and uses it in a WHERE clause. The order_by(Item.id) ensures a deterministic order, and the limit controls the page size. If the query returns exactly limit items, there is likely another page, so the server generates a next_cursor from the last item's ID. If fewer than limit items are returned, the client has reached the end.

This approach is efficient because the database can use an index on the cursor column to find the starting point. The query does not need to scan and discard rows; it jumps directly to the position after the cursor. It also avoids page drift because the cursor points to a specific record, not a count. New rows inserted before the cursor will not affect the next page.

The main limitation is that the cursor must be based on a unique, sortable column. An integer primary key works well. If you need to sort by a non-unique column, you must include a tiebreaker, such as (created_at, id). The cursor then encodes both values, and the query uses a composite condition.

Returning Pagination Metadata in the Response

A well-designed paginated response does more than return a list of items. It tells the client how to get the next page, and optionally the previous page, the total count, and the current page information. Using Pydantic models keeps the response structure consistent and documented.

For offset-based pagination, a common response model looks like this:

from pydantic import BaseModel
from typing import Generic, TypeVar, List

T = TypeVar("T")

class OffsetPage(BaseModel, Generic[T]):
    items: List[T]
    total: int
    offset: int
    limit: int

You can then use this model as the response model in your endpoint. FastAPI will automatically generate the OpenAPI schema for it.

For cursor-based pagination, the response typically includes only the next cursor, because the previous page can be reconstructed by reversing the direction of the query. Here is a generic cursor page model:

class CursorPage(BaseModel, Generic[T]):
    items: List[T]
    next_cursor: str | None = None
    previous_cursor: str | None = None

Including both next_cursor and previous_cursor allows clients to navigate forward and backward without maintaining state. To generate the previous cursor, you would run a query in the opposite direction and reverse the results, which is an extra database call. Many APIs choose to include only next_cursor to keep the response lightweight.

Performance Considerations: Indexing and Query Efficiency

The performance of pagination in FastAPI depends almost entirely on the database query, not on the Python code. Offset pagination becomes slow when the offset is large because the database must scan and discard rows. Cursor pagination avoids that by using an index on the cursor column. To get the expected performance, you need to create an index on the column used in the WHERE clause and the ORDER BY clause.

CREATE INDEX idx_items_id ON items (id);
```n
For cursor-based pagination, the index is essential. Without it, the database will perform a full table scan to find the starting point, which defeats the purpose. For composite cursors, create a composite index that matches the ordering columns.

Another performance concern is the `COUNT(*)` query used to return the total number of records. On large tables, counting all rows can be slow, especially if the table has many rows or the count query does not use an index. If your API does not need the total, omit it. If you do need it, consider caching the count or using an approximate value.

Finally, consider the size of the response. Returning 100 items with every column can produce a large payload. Use Pydantic's response models to select only the fields the client needs, and consider using `selectinload` or similar to avoid N+1 queries when serializing relationships.

## Handling Edge Cases: Invalid Parameters and Empty Results

FastAPI's query parameter validation handles many edge cases automatically. The `ge` and `le` constraints on `limit` and `offset` reject invalid values with a 422 response. However, there are a few additional cases to consider.

If a client requests an offset that is beyond the end of the dataset, the query returns an empty list. That is a valid response, and the API should return `200` with an empty `items` array. Do not return a 404 unless the resource itself does not exist.

For cursor-based pagination, an invalid cursor string should result in a 422 or 400 response. The current implementation would raise a `ValueError` when decoding, which FastAPI would turn into a 500 error. You should catch that exception and return a meaningful error message.

```python
from fastapi import HTTPException

@app.get("/items/cursor")
def list_items_cursor(
    cursor: str | None = None,
    limit: int = Query(20, ge=1, le=100),
    db: Session = Depends(get_db),
):
    try:
        last_id = int(base64.urlsafe_b64decode(cursor).decode()) if cursor else None
    except (ValueError, TypeError):
        raise HTTPException(status_code=400, detail="Invalid cursor")
    # ...

Also consider what happens when the cursor points to a record that has been deleted. The WHERE id > :cursor condition will simply skip it and return the next available rows, which is the desired behavior. If you need to support backwards navigation, you would use id < :cursor and reverse the order.

Choosing the Right Strategy for Your API

The decision between offset and cursor pagination depends on the size of your dataset, the stability of the data, and the client's navigation needs.

Use offset pagination when:

  • The dataset is small or bounded (e.g., a few thousand rows).
  • Clients need to jump to a specific page number.
  • The data is not updated frequently, so page drift is not a concern.
  • You need to return the total count for a UI pagination control.

Use cursor pagination when:

  • The dataset is large and grows over time.
  • The data is updated frequently, and you want to avoid duplicate or missing rows.
  • Clients navigate sequentially (e.g., infinite scroll or "load more" buttons).
  • You want consistent query performance regardless of page depth.

There is no one-size-fits-all answer. A public API that serves millions of records should almost certainly use cursor-based pagination. An internal admin panel with a few hundred records can use offset pagination without any measurable impact. The key is to understand the tradeoffs and choose the approach that matches your data and client requirements.

Implementing a Reusable Pagination Dependency in FastAPI

To avoid repeating pagination logic across multiple endpoints, you can create a FastAPI dependency that parses and validates the pagination parameters, then returns a common object. This keeps your route handlers clean and ensures consistent behavior.

from typing import Optional
from dataclasses import dataclass

@dataclass
class OffsetParams:
    offset: int = 0
    limit: int = 20

def offset_params(
    offset: int = Query(0, ge=0),
    limit: int = Query(20, ge=1, le=100),
) -> OffsetParams:
    return OffsetParams(offset=offset, limit=limit)

@app.get("/items")
def list_items(params: OffsetParams = Depends(offset_params), db: Session = Depends(get_db)):
    items = db.scalars(select(Item).offset(params.offset).limit(params.limit)).all()
    return {"items": items, "offset": params.offset, "limit": params.limit}

Similarly, you can create a cursor-based dependency that handles decoding and validation. This abstraction becomes especially useful when you have many endpoints that need the same pagination behavior. It also makes it easier to change the default page size or add new parameters later.

One caveat is that the dependency should not hide the underlying database query. The actual filtering and ordering still need to be implemented in each route handler, because they depend on the model and the sort key. The dependency only handles the HTTP-facing parameters and the cursor encoding/decoding.

By isolating pagination into a dependency, you reduce duplication and make the API more maintainable. New endpoints can opt into pagination by adding a single parameter, and the response format remains consistent across the entire API.

python fastapi pagination: Practical Usage and Code Examples | RYUSLOG DEV