Module 8: Anti-Patterns and Final Project

Anti-pattern: large OFFSET

SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 50000; looks harmless. It's the "obvious" way to paginate: page N is OFFSET (N-1)*20. It works perfectly in testing with 1000 rows. It works perfectly in production for the first 6 months. And one day your app has 10 million rows, someone paginates to page 50,000, and your server takes 8 seconds to return the 20 rows.

The problem is structural: to return OFFSET 50000 LIMIT 20, PostgreSQL has to read 50,020 rows and discard the first 50,000. There's no magic that avoids the discard — it's written into the logic of the SQL. The complexity is O(n) where n is the page you reach.

In this capsule you're going to see the exponential curve with your own eyes (page 100 → 5ms, page 10,000 → 800ms, page 100,000 → 8s), understand why it happens, and refactor to cursor pagination which is O(1) no matter how deep you paginate. You're going to learn the basic pattern here; the deep dive (multiple sorting, bidirectional pagination) is in guide #13.

The problem in numbers

You're going to reproduce the exponential curve.

DROP TABLE IF EXISTS pagination_demo;
CREATE TABLE pagination_demo (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    user_id INTEGER NOT NULL,
    payload TEXT
);

CREATE INDEX idx_pagination_created ON pagination_demo(created_at);

-- Insert 1 million rows
INSERT INTO pagination_demo (user_id, payload, created_at)
SELECT
    (random() * 10000)::INT,
    'data_' || generate_series,
    NOW() - (random() * INTERVAL '365 days')
FROM generate_series(1, 1000000);

ANALYZE pagination_demo;

Now run the pagination queries with an increasing OFFSET:

-- Page 1 (OFFSET 0)
EXPLAIN ANALYZE
SELECT * FROM pagination_demo ORDER BY created_at LIMIT 20 OFFSET 0;

-- Page 100 (OFFSET 1980)
EXPLAIN ANALYZE
SELECT * FROM pagination_demo ORDER BY created_at LIMIT 20 OFFSET 1980;

-- Page 1000 (OFFSET 19980)
EXPLAIN ANALYZE
SELECT * FROM pagination_demo ORDER BY created_at LIMIT 20 OFFSET 19980;

-- Page 10,000 (OFFSET 199980)
EXPLAIN ANALYZE
SELECT * FROM pagination_demo ORDER BY created_at LIMIT 20 OFFSET 199980;

-- Page 49,000 (OFFSET 979980)
EXPLAIN ANALYZE
SELECT * FROM pagination_demo ORDER BY created_at LIMIT 20 OFFSET 979980;

Typical results:

Page	OFFSET	Time
1	0	~2 ms
100	1980	~5 ms
1,000	19,980	~45 ms
10,000	199,980	~620 ms
49,000	979,980	~3,200 ms

The curve is linear in OFFSET but the times feel exponential to the user. An API that takes 3 seconds to load page 49,000 is unacceptable, but technically the query "is fine" — it's just doing what you asked it to.

Why it happens

Look at the plan of the query with a large OFFSET:

EXPLAIN ANALYZE
SELECT * FROM pagination_demo ORDER BY created_at LIMIT 20 OFFSET 199980;

Limit  (cost=15234.89..15235.04 rows=20 width=128)
       (actual time=619.34..619.42 rows=20 loops=1)
  ->  Index Scan using idx_pagination_created on pagination_demo
      (cost=0.42..76234.89 rows=1000000 width=128)
      (actual time=0.012..567.23 rows=200000 loops=1)

The Index Scan says rows=200000 (it reads the first 200k entries of the index). The Limit says rows=20 (it returns only the last 20).

What happened: PostgreSQL read 200,000 rows from the index, discarded the first 199,980, returned the final 20. The discard is the cost.

Why doesn't it jump directly to row 199,981? Because PostgreSQL doesn't know which rows are at each position without traversing them. The index lets you traverse in order, but it doesn't give you random access to "row number 199,981." That information doesn't exist — the index isn't an indexed array, it's an ordered B-tree.

And since the order is by created_at, not by a contiguous autoincrement id, there's no way to calculate "row 199,981." The structure of the problem is linear.

The solution: cursor pagination

Instead of "page N," the client passes the server a cursor — a value that identifies the last element of the previous page. The server filters from there.

-- First page: no cursor
SELECT * FROM pagination_demo ORDER BY created_at LIMIT 20;
-- Returns: [..., row_with_created_at_X]

-- The client receives the last created_at: 'X'

-- Next page: passes X as the cursor
SELECT * FROM pagination_demo
WHERE created_at > 'X'
ORDER BY created_at LIMIT 20;
-- Returns the next 20

-- And so on

The query is O(1) — it uses the index to jump directly to the right point, reads only 20 rows, returns. It doesn't matter if you're on page 1 or page 50,000, the cost is identical.

EXPLAIN ANALYZE
SELECT * FROM pagination_demo
WHERE created_at > '2025-06-15 14:23:11+00'
ORDER BY created_at LIMIT 20;

Limit  (cost=0.42..2.34 rows=20 width=128)
       (actual time=0.034..0.089 rows=20 loops=1)
  ->  Index Scan using idx_pagination_created on pagination_demo
      (cost=0.42..47892.34 rows=499000 width=128)
      (actual time=0.032..0.085 rows=20 loops=1)

actual time=0.085 ms. No matter how deep, always <1ms.

Implementation in FastAPI + SQLAlchemy

Endpoint with OFFSET (anti-pattern)

from fastapi import APIRouter, Query
from sqlalchemy.ext.asyncio import AsyncSession

router = APIRouter()

@router.get("/orders")
async def list_orders_offset(
    page: int = Query(1, ge=1),
    page_size: int = Query(20, ge=1, le=100),
    db: AsyncSession = Depends(get_db),
):
    offset = (page - 1) * page_size
    result = await db.execute(
        select(Order).order_by(Order.created_at).limit(page_size).offset(offset)
    )
    orders = result.scalars().all()

    # Bonus problem: COUNT(*) that we'll see in capsule 03
    total = await db.scalar(select(func.count(Order.id)))

    return {
        "items": orders,
        "page": page,
        "page_size": page_size,
        "total": total,
    }

This degrades with a large OFFSET. And the COUNT(*) adds its own problem (capsule 03).

Endpoint with cursor pagination (refactor)

from datetime import datetime
from typing import Optional
from base64 import urlsafe_b64encode, urlsafe_b64decode

@router.get("/orders")
async def list_orders_cursor(
    cursor: Optional[str] = None,
    page_size: int = Query(20, ge=1, le=100),
    db: AsyncSession = Depends(get_db),
):
    query = select(Order).order_by(Order.created_at, Order.id).limit(page_size + 1)

    # If there's a cursor, decode it and filter
    if cursor:
        decoded = decode_cursor(cursor)
        query = query.where(
            (Order.created_at, Order.id) > (decoded["created_at"], decoded["id"])
        )

    result = await db.execute(query)
    orders = result.scalars().all()

    # If we fetched page_size+1, there are more pages
    has_more = len(orders) > page_size
    if has_more:
        orders = orders[:page_size]

    # Generate a cursor for the next page
    next_cursor = None
    if has_more and orders:
        last = orders[-1]
        next_cursor = encode_cursor({
            "created_at": last.created_at.isoformat(),
            "id": last.id,
        })

    return {
        "items": orders,
        "page_size": page_size,
        "next_cursor": next_cursor,
    }


def encode_cursor(data: dict) -> str:
    """Opaque cursor — the client shouldn't interpret it."""
    import json
    return urlsafe_b64encode(json.dumps(data).encode()).decode()


def decode_cursor(cursor: str) -> dict:
    import json
    return json.loads(urlsafe_b64decode(cursor.encode()))

Three important details:

(Order.created_at, Order.id) > (X, Y): ordering by created_at ALONE isn't enough if there are duplicate values. If two orders have the same created_at, the cursor could skip one. Adding id as a tiebreaker guarantees stable ordering.
limit(page_size + 1): you request one extra row to detect whether there are more pages, without having to run a separate query.
Opaque cursor: the cursor is a base64 blob the client returns as-is. It's not the client's responsibility to interpret it — that lets you change the internal format without breaking the API.

Endpoint output

First page:

curl http://localhost:8000/orders?page_size=20

{
  "items": [...],
  "page_size": 20,
  "next_cursor": "eyJjcmVhdGVkX2F0IjoiMjAyNi0wMS0wMVQxMjozNDo1NiIsImlkIjoyMH0="
}

Next page:

curl "http://localhost:8000/orders?page_size=20&cursor=eyJjcmVhdGVkX2F0IjoiMjAyNi0wMS0wMVQxMjozNDo1NiIsImlkIjoyMH0="

{
  "items": [...],
  "page_size": 20,
  "next_cursor": "eyJjcmVhdGVkX2F0IjoiMjAyNi0wMS0wMVQxMjozNTowMSIsImlkIjo0MH0="
}

Limitations of basic cursor pagination

Cursor pagination is optimal for "next, next, next." It has limitations that guide #13 covers in depth:

1. It doesn't allow "go to specific page N."

The client can only go sequentially: first page → next → next → next. It can't jump to page 500. For many cases this is acceptable (Twitter, Instagram, infinite scroll). For cases where the user wants to go directly to page X (e.g.: an admin table), cursor doesn't apply.

2. It requires an index on the cursor columns.

If you paginate by created_at, you need an index on created_at (or a composite index if you add a tiebreaker). Without an index, cursor degrades to a Seq Scan.

3. The correct tiebreaker requires care.

For a stable order you need the order by (created_at, id) to be deterministic. If the main column has many duplicates, the tiebreaker becomes critical.

4. Dynamic filters change the logic.

If your endpoint accepts filters (/orders?status=pending&cursor=X), the cursor is only valid for that filter. Changing filters invalidates the cursor — you have to reflect it in the client (reset to the first page).

5. Ordering by non-unique columns.

If you want to order by total_amount DESC (common repeated numbers), you need a robust tiebreaker: (total_amount, id) DESC. The query is:

WHERE (total_amount, id) < (last_amount, last_id)
ORDER BY total_amount DESC, id DESC
LIMIT 20;

The tuple-comparison syntax in SQLAlchemy 2.0:

from sqlalchemy import tuple_

query = select(Order).where(
    tuple_(Order.total_amount, Order.id) < (decoded["amount"], decoded["id"])
).order_by(Order.total_amount.desc(), Order.id.desc()).limit(page_size + 1)

Guide #13 (the cursor pagination module) goes deep into these cases.

When OFFSET is acceptable

OFFSET isn't always bad. Cases where it's fine:

Initial pages (page 1, 2, 3 with a strict cap): if your UI never allows paginating more than 5 pages, the maximum OFFSET is small. The degradation never becomes a problem.
Small tables (<10k rows): the curve exists but the absolute numbers are microseconds. Optimizing prematurely would be over-engineering.
Admin reports with a cap: internal dashboards where you know nobody is going to paginate 50,000 pages.
Jump-to-page when the UX requires it: if the user expects "go to page 500 of 1000," there's no easy alternative. Accept the trade-off or change the UX.

The rule: cursor pagination for public listings where users can paginate deep. OFFSET for controlled cases where you know the ceiling.

Traps and common mistakes

1. Implementing a cursor without a tiebreaker.

WHERE created_at > X ORDER BY created_at LIMIT 20. If two rows have the same created_at, the cursor can skip one. Always add a tiebreaker (typically id with (created_at, id) > (X, Y)).

2. Exposing the cursor structure to the client.

If your cursor is ?cursor=2026-01-15T10:34:56.789Z|12345, the client can manipulate it. Better opaque (base64 or a signed JWT). It lets you change the format without breaking clients.

3. Not updating the cursor when the filters change.

If the client changes filters and reuses the previous cursor, the results are wrong. The frontend has to reset the cursor when filters change.

4. Cursor pagination on a UI with numbered pagination.

If your UI shows "page 1 2 3 4 ... 50," cursor doesn't fit. A UX decision: change to "infinite scroll" or "Load more," or accept OFFSET with a cap.

5. OFFSET 0 is perfectly valid.

LIMIT 20 OFFSET 0 is the first page and it's fine. The anti-pattern is a large OFFSET, not OFFSET itself.

6. Cursor pagination without an index.

If you paginate by a column without an index, cursor degrades to Seq Scan + filter — slower than OFFSET (because OFFSET can at least use the PK index). Creating the index is a prerequisite.

7. Mixing OFFSET and cursor.

Some APIs offer both: ?page=N&page_size=20 and ?cursor=X. This doubles the complexity to maintain. Choose one and stay consistent. Cursor covers most modern cases.

Exercise: measure and refactor

Setup: use pagination_demo from the start of the capsule (1M rows).

Step 1: measure the latency of OFFSET with wrk.

# Create a mock endpoint that runs the query
# (we assume your FastAPI app has /orders implemented with OFFSET)

# Test page 1
wrk -t2 -c10 -d10s "http://localhost:8000/orders?page=1&page_size=20"

# Test page 1000
wrk -t2 -c10 -d10s "http://localhost:8000/orders?page=1000&page_size=20"

# Test page 25000
wrk -t2 -c10 -d10s "http://localhost:8000/orders?page=25000&page_size=20"

Write down p50, p95, p99 latency for each page.

Step 2: implement cursor pagination in a separate endpoint (/orders-cursor).

@router.get("/orders-cursor")
async def list_orders_cursor(
    cursor: Optional[str] = None,
    page_size: int = Query(20, ge=1, le=100),
    db: AsyncSession = Depends(get_db),
):
    # implementation from the previous section
    ...

Step 3: measure the latency of cursor pagination paginating deep.

# Iterate to reach the "page 1000 equivalent"
# (50 pages iterating with a cursor each one)

# Or use locust with a realistic infinite scroll scenario

Step 4: compare and report.

Equivalent page	OFFSET p95	Cursor p95	Improvement
1	?	?	?
1,000	?	?	?
25,000	?	?	?

Step 5: decide. For your real app, cursor or OFFSET? Why?

See solution and discussion

Typical results:

Page	OFFSET p95	Cursor p95	Improvement
1	8 ms	6 ms	1.3x
1,000	145 ms	6 ms	24x
25,000	2,400 ms	6 ms	400x

Cursor pagination is O(1) — it doesn't degrade with depth. OFFSET is O(n) — it degrades linearly.

Typical decision:

For public user listings (/orders that the user navigates):

If your UI allows paginating deep (>page 100): cursor pagination mandatory.
If your UI has a strict cap (max 10 pages): OFFSET is acceptable.
If the UX might change to "Load more"/infinite scroll: cursor pagination (scales better).

For internal dashboards:

If nobody paginates more than 100 pages and the dataset fits in RAM: OFFSET is acceptable.
If the data grows and eventually someone is going to paginate 1000+ pages: cursor.

Key lesson: the difference between 6ms and 2400ms in p95 is the difference between "fast API" and "slow API." The refactor to cursor pagination is one of the cleanest wins — slightly more complex code, dramatically better performance.

Summary and next step

What you learned:

OFFSET N is O(n): PostgreSQL reads N rows and discards the first N — there's no way to skip them.
The curve: page 1 (5ms) → page 10,000 (620ms) → page 100,000 (8s).
Cursor pagination replaces ?page=N with ?cursor=X where X identifies the last element of the previous page. It's O(1).
Three critical details: a robust tiebreaker ((created_at, id) > (X, Y)), LIMIT page_size + 1 to detect more pages, an opaque cursor for the client.
Limitations: it doesn't allow "go to page N," it requires an index, it requires a tiebreaker, dynamic filters invalidate the cursor.
OFFSET is still valid in controlled cases: initial pages with a cap, small tables, admin dashboards.

Before moving on, you should be able to:

Recognize an endpoint with a large OFFSET in a code review.
Implement cursor pagination with a tiebreaker in SQLAlchemy 2.0 async.
Decide between OFFSET and cursor based on the UX context.
Measure the impact of the refactor with wrk or similar.

In the next capsule you go to the second most common anti-pattern: a slow COUNT(*). It's ubiquitous in paginated APIs ({"items": [...], "total": COUNT(*)}) and in stats endpoints. You're going to learn why COUNT(*) on large tables is inherently expensive, and the three alternatives with their trade-offs: estimation with pg_class.reltuples (fast but imprecise), a materialized view (precise but with a delay), an incremental counter (precise, real-time, but requiring maintaining triggers).

Resources

Markus Winand — "Pagination Done the PostgreSQL Way" — the classic reference on the topic.
Brandur Leach — "Cursor pagination" — deep implementation with cases.
Slack Engineering — Evolving Slack's API — a real case of an OFFSET → cursor migration.
PostgreSQL Wiki — Don't Do This: pagination — the documented anti-pattern.
Vlad Mihalcea — Hibernate cursor-based pagination — a variant in another stack but it applies.
SQLAlchemy 2.0 — tuple_ and row-value comparison — syntax for the tiebreaker.

Capsule 02 of 08 — Module 8 — Database Performance & Query Tuning Guide

← PreviousModule 8: Anti-Patterns and Final Project Next →Anti-pattern: slow `COUNT(*)`