Choosing Between Offset and Keyset Pagination in SQLAlchemy

The Performance Wall of Deep Pagination

When building APIs or dashboards that handle large datasets, the standard approach of using page numbers often leads to a critical performance failure: the "deep page" slowdown. As a user navigates to page 10, 100, or 1,000, the database must scan and discard thousands of rows before returning the requested slice, leading to increased CPU load and slow response times.

The decision is between Offset-based pagination (standard page numbers) and Keyset pagination (also known as Cursor pagination). The right choice depends on whether your users need to jump to a specific page or if they simply need to scroll through a continuous stream of data.

Comparison of Pagination Strategies

Feature	Offset-based	Keyset (Cursor)
Implementation	Simple (.limit().offset())	Moderate (.filter() on unique keys)
Performance	Degrades linearly with page depth	Constant time (O(log N) with index)
Random Access	Supported (Jump to Page X)	Not supported (Next/Previous only)
Data Consistency	Prone to skipping/duplicating rows	Stable during concurrent inserts

Trade-offs and Constraints

Offset Pagination is ideal for small datasets or administrative interfaces where the ability to jump to a specific page is a requirement. However, it is dangerous for tables with millions of rows because the database engine must still process the rows it intends to discard.

Keyset Pagination solves the performance issue by using a WHERE clause on a unique, ordered column (usually the Primary Key). Instead of saying "skip 10,000 rows," it says "give me 20 rows where the ID is greater than the last ID I saw." This allows the database to use an index to jump directly to the starting point.

The primary constraint of Keyset pagination is that it requires a strictly ordered, unique column. If you order by a non-unique column (like created_at), you must include a unique tie-breaker (like id) to avoid skipping records with identical timestamps.

Implementation in SQLAlchemy

Assume a SQLAlchemy 2.0 environment with a User model and a primary key id.

Option A: Offset Implementation

Run this within your application service layer. This is suitable for low-volume data.

# Required permissions: Read access to the database
# Placeholders: page_number (int), page_size (int)

from sqlalchemy import select

stmt = (
    select(User)
    .order_by(User.id)
    .limit(page_size)
    .offset((page_number - 1) * page_size)
)
results = session.execute(stmt).scalars().all()

Option B: Keyset Implementation

Use this for high-volume data or infinite-scroll interfaces. The client must provide the last_id from the previous request.

# Required permissions: Read access to the database
# Placeholders: last_id (int or None), page_size (int)

from sqlalchemy import select

stmt = select(User).order_by(User.id).limit(page_size)

if last_id:
    # Filter for records strictly greater than the last seen ID
    stmt = stmt.where(User.id > last_id)

results = session.execute(stmt).scalars().all()

Validating the Decision

To verify if your current pagination is causing a bottleneck, use the database's EXPLAIN ANALYZE tool on a query with a high offset (e.g., OFFSET 50000). Look for "Sequential Scan" or a high number of "rows removed by filter." If the execution time increases significantly as the offset grows, you have reached the performance wall and should migrate to Keyset pagination.

Verification Test for Data Drifting:

1. Fetch Page 1 using Offset pagination.
2. Insert a new record into the table.
3. Fetch Page 2.
4. Observe that the last item of Page 1 now appears as the first item of Page 2. Keyset pagination prevents this because it anchors the query to a specific ID rather than a relative position.

Limitations

• Keyset: Cannot calculate the total number of pages easily without a separate COUNT query.
• Offset: Memory and CPU usage on the DB server spike as the offset increases.