Keyset Pagination in FastAPI and PostgreSQL for Large Tables

OFFSET pagination works until it doesn't. On a table with 30 million rows, page 1000 means scanning and discarding 30 million rows before returning 20. Response times go from milliseconds to minutes.

Keyset pagination (also called the seek method) is the fix. I use it in production on member platforms with ~30 million records — queries stay fast regardless of page depth.

Why OFFSET fails

```sql
SELECT * FROM members ORDER BY id LIMIT 20 OFFSET 1000000;
```

PostgreSQL must fetch 1,000,020 rows, sort them, discard the first million, and return 20. The cost grows linearly with offset. At 30M rows, deep pagination is unusable.

How keyset pagination works

Instead of skipping rows, you remember where you left off:

```sql
SELECT * FROM members
WHERE id > :last_seen_id
ORDER BY id
LIMIT 20;
```

The database uses the primary key index, jumps straight to the next row, and returns 20. Cost is constant regardless of page depth.

Composite keysets

Real-world sorting isn't always by ID. For sorting by `created_at` then `id`:

```sql
SELECT * FROM members
WHERE (created_at, id) > (:last_created_at, :last_id)
ORDER BY created_at, id
LIMIT 20;
```

Row-value comparisons make this work in PostgreSQL.

FastAPI + SQLAlchemy async implementation

```python
async def list_members(
    cursor: str | None = None,
    limit: int = 20,
    db: AsyncSession = Depends(get_db),
):
    query = select(Member).order_by(Member.id).limit(limit)
    if cursor:
        last_id = decode_cursor(cursor)
        query = query.where(Member.id > last_id)
    
    result = await db.execute(query)
    members = result.scalars().all()
    
    next_cursor = encode_cursor(members[-1].id) if len(members) == limit else None
    return {"members": members, "next_cursor": next_cursor}
```

The cursor is a base64-encoded last ID. Clients pass it back in the next request.

Redis caching for hot pages

Keyset cursors are cacheable. Cache the first page (most commonly requested) with a short TTL, invalidated on writes:

- Page 1: cached, 60-second TTL. - Deeper pages: not cached (too many variations), but fast via index.

Benchmarks

| Rows | OFFSET page 100 | Keyset page 100 | |------|-----------------|-----------------| | 1M | 240ms | 3ms | | 10M | 2.4s | 3ms | | 30M | 7.1s | 4ms |

Keyset stays flat; OFFSET grows linearly.

Gotchas

- **Deleted rows**: Keyset skips deleted rows naturally (they never match `id > last_seen_id`). This is usually desired. - **Non-unique sort keys**: Always include a tiebreaker (primary key) to avoid skipping or duplicating rows. - **NULL handling**: Explicitly handle NULLs in sort columns, or use `NULLS FIRST/LAST`.

Bottom line

If your table has more than ~100K rows and users paginate deeply, switch to keyset pagination. The code change is small; the performance difference is orders of magnitude.

Need help optimizing your API? [Get in touch](/contact).