Module 1: Pagination Patterns

Introduction: Pagination Patterns for Production APIs

Overview

The previous guide (#12: Database Performance & Query Tuning) ended with an uncomfortable diagnosis: a large OFFSET is O(n), degrades exponentially, and kills APIs on deep pages. You identified it with EXPLAIN ANALYZE, you saw it in pg_stat_statements, and you learned to measure it. But you didn't solve it. You left with an open wound: "ok, OFFSET is bad, now what?".

This module closes that wound. Here you build the two patterns that replace OFFSET when it breaks: cursor pagination (an opaque token the client hands back) and keyset pagination (a WHERE filter on the sort columns). You're going to understand when to use each one, implement them in SQLAlchemy 2.0 async + FastAPI, handle the real edge cases (a dataset that changes between pages, ordering by multiple columns, cursors signed with HMAC), and measure the speedup against OFFSET on a 5M-row table.

By the end of the module you'll have a GET /tasks endpoint with cursor pagination that holds constant latency at page 50,000 (where OFFSET takes seconds), and a decision tree that tells you when cursor is not the right answer — because it isn't always.

Where are we in the guide?

This is Module 1 of the SQL Patterns for Production APIs Guide — guide #13 of the Backend Python Developer with FastAPI path and the second of the Data Layer sub-track.

The full guide has five blocks:

Block 1: Pagination (Module 1)                           ← YOU ARE HERE
Block 2: Defensive Modeling (Modules 2-3)
Block 3: Multi-Tenancy and Live Migrations (Modules 4-5)
Block 4: Concurrency, Versioning, and Bulk (Modules 6-7)
Block 5: Final Project TaskFlow (Module 8)

Pagination opens the guide for three pedagogical reasons. First, it gives narrative continuity with #12: it closes the OFFSET → cursor loop in the very first module. Second, it's the most isolated pattern in the scope — it doesn't depend on RLS, audit logs, or multi-tenancy, so you can master it without carrying context from the rest of the guide. Third, it has the most immediate "wow": measuring a 17x speedup against OFFSET on a real table is an early win that buys attention for the drier modules that come later (audit logs, schema versioning, zero-downtime migrations).

The fundamental principle: cursor isn't magic, it's a concrete trade-off

Cursor pagination has evangelical fans on the internet ("always use cursor", "OFFSET is legacy"). Reality is more nuanced.

Cursor wins when:

The UI is infinite scroll, a feed, or "load more" (Twitter, Instagram, Slack)
You need to paginate large datasets (>10k rows) where page 50,000 is plausible
The client can hold an opaque token between requests
You don't need to jump to a specific page ("go to page 47")
You want constant latency between page 1 and page N

OFFSET wins when:

The UI is an admin table with page numbers ("Page 1 of 250")
The dataset is small (<5k rows) and you'll never have a deep page
You need to "go to the last page" without inverting the order
The client expects stable URLs like ?page=47&per_page=20
Your team doesn't want to maintain cursor encoding

Keyset pagination is an internal variant: using the sort columns directly in the WHERE (WHERE created_at < $1). Cursor pagination is almost always implemented internally as keyset, but the client only sees an opaque token. You'll understand the exact relationship in capsules 03 and 05.

The rule of this module: before implementing cursor, decide with a decision tree that cursor is right for your case. If you dive into cursor encoding and it turns out your UI needs "go to page 47", you'll have done the work twice.

Professional objective

By the end of this module you'll be able to:

Distinguish when to use OFFSET, cursor, or keyset pagination with a concrete decision tree (not "it depends")
Implement cursor pagination in SQLAlchemy 2.0 async + FastAPI with cursor encoding (base64 + HMAC)
Implement keyset pagination with composite cursors for a secondary sort (ORDER BY created_at DESC, id DESC)
Model the paginated response with Pydantic: Page[T] with next_cursor, previous_cursor, has_more
Handle edge cases: the dataset changes between pages, duplicate items, items lost at the boundary
Measure the real speedup against OFFSET with benchmarks (page 1 vs page 50,000)
Combine pagination with dynamic filters and parameterizable ordering without breaking the cursor

These are skills that come up in any senior backend SaaS interview — and the ones that separate an API that works in dev from one that scales in production.

Why does this module matter?

This happens in production all the time:

Your team shipped a GET /events?page=1&limit=50 endpoint that looks perfect in QA. Three months later, a power user reaches page 800. The query takes 6 seconds. Your pager goes off. You investigate: PostgreSQL is reading 40,000 rows to discard 39,950 and return 50. The problem isn't the index. The problem is OFFSET.

If your first instinct is "let's add a bigger index" or "let's bump the RDS RAM": you're going to lose hours and money, and the problem won't go away, because OFFSET is still O(n) no matter how many indexes or how much RAM you throw at it.

If your first instinct is to change the pattern — convert the endpoint to cursor pagination — you solve the problem in a single PR and you learn something reusable for the rest of your career.

This module trains you for the second instinct. You'll apply it:

When an endpoint with pagination starts showing up in alerts with a high p99
In senior technical interviews where "how do you paginate 100M events?" is a mandatory question
When you design a public API with a latency SLA (Stripe, GitHub, and Slack all use cursor)
When you lead the redesign of a legacy API that's starting to break on deep pages

The pattern is transferable. The exact SQLAlchemy syntax may change tomorrow. The idea of "navigating using a stable pointer instead of an offset" won't.

A scenario that illustrates the module

Imagine you join a B2B SaaS productivity startup. They assign you your first ticket:

"We have GET /tasks?page=N&limit=50 in production. It works fine for 99% of users, but an enterprise customer has 500k tasks and reports that when they navigate past page 200, everything gets slow. The customer's complaints are escalating. Fix it."

The wrong way to proceed (the one you'll avoid):

You open New Relic, see the endpoint takes 4-8s in that case, and decide "it's the DB."
You add a composite index on (tenant_id, created_at, id).
You deploy. You measure again. It still takes 4-8s on page 200+. The index solved nothing.
You decide "it needed more RAM," you scale up the RDS instance. Same latency.
A week lost and the customer is still complaining.

The right way (what you'll do by the end of this module):

You identify that the problem is OFFSET (capsule 02): the plan runs the correct Index Scan, reads 10,000 rows from the index, and discards 9,950. The latency is linear with the depth of the page.
You decide between cursor and keyset (capsule 05): the customer's UI is infinite scroll, not an admin table with page numbers. Cursor is the right choice.
You implement cursor pagination (capsules 03-04): you build an opaque cursor eyJ0aW1lc3RhbXAiOiIyMDI2L... that the client hands back. Internally it's keyset over (created_at, id).
You handle the secondary sort (capsule 05): the real ORDER BY is (created_at DESC, id DESC) to avoid collisions when there are multiple tasks in the same second.
You sign the cursor with HMAC (capsule 06): the client can't manipulate it to read other tenants' data.
You measure: page 1 = 8ms, page 200 = 8ms, page 10,000 = 8ms. Constant latency. You report "p99 of /tasks with deep pagination: 8s → 12ms (-99.9%)".
You document it in the team's decision matrix: "infinite scroll → cursor; admin table with numbers → OFFSET".

The module trains you in every one of those steps, with real code and reproducible benchmarks.

Module map

Capsule	Topic	What you'll learn
02	OFFSET pagination and its limits	How OFFSET works internally, why it's O(n), when it's fine and when it breaks, the initial decision tree
03	Cursor pagination: fundamentals	What a cursor is, how it differs from keyset, cursor encoding (base64), Pydantic models for paginated responses
04	Cursor pagination in FastAPI	End-to-end implementation: SQLAlchemy 2.0 async + FastAPI endpoint + tests
05	Keyset pagination and when to use it	Composite cursors, tuple comparison in SQL (`WHERE (a, b) < ($1, $2)`), multi-column sort without collisions
06	Bidirectional pagination and opaque cursors	next/previous, encoding with HMAC to prevent tampering, cursor expiration
07	Pagination with filters and dynamic ordering	Combining cursor with filters (`?status=active&priority=high`), parameterizable ordering, input validation
08	Project: cursor pagination in TaskFlow	An integrative mini-project with measured benchmarks

Learning flow: First you understand why OFFSET breaks (02). Then you learn cursor from conceptual fundamentals (03) and implement it in FastAPI (04). You go deeper into keyset and composite cursors (05), which is what makes the cursor work when the sort has more than one column. You add bidirectionality and security (06), which is what separates a toy implementation from a production one. You combine everything with dynamic filters (07), which is the real case. You close with the module project (08).

Connection with the integrative project

The consolidating project for the whole guide is TaskFlow: a multi-tenant SaaS task management API built in Module 8. The cursor pagination built here is reused directly in TaskFlow's GET /tasks endpoint.

Specifically:

The composite cursor on (created_at DESC, id DESC) from Module 1 capsule 05 is exactly what TaskFlow needs: listing tasks ordered by date without collisions when multiple tasks are created in the same second.
The HMAC-signed cursor from Module 1 capsule 06 is what stops a client from the Acme tenant from tampering with the cursor to read Globex's tasks.
The decision tree from Module 1 capsule 02 is what justifies TaskFlow's architectural decision ("why cursor and not OFFSET? because the UI is infinite scroll").

The module's mini-project (capsule 08) uses a simple standalone tasks table — you don't need to have built TaskFlow yet. What matters is that the table has 5M rows so that the difference with OFFSET is visceral, not anecdotal.

What is NOT covered in this module?

An explicit list, with reasons:

Soft deletes (WHERE deleted_at IS NULL) — Module 2. Here the queries are on "live" tables without soft delete. The cursor + soft delete interaction comes later.
Audit logs — Module 3. You're not going to audit who paginates what.
Multi-tenancy with RLS — Module 4. Here we assume single-tenant to keep things simple. The cursor + RLS integration is covered in the final project.
Optimistic locking — Module 6. Pagination is read-only in this module.
Deep EXPLAIN ANALYZE — Covered in guide #12 (Modules 2-3). Here we use it as a diagnostic tool, we don't re-explain it.
Advanced indexing (composite, covering) — Covered in guide #12 (Module 3). We assume it when we justify the ORDER BY being matched by an index.
GraphQL pagination (the Relay spec) — Out of scope. This guide is REST with FastAPI. The idea of an opaque cursor translates to Relay effortlessly, but we don't cover it explicitly.
Pool tuning, PgBouncer — Covered in guide #12 (Module 6).
Caching of paginated responses — A topic for guide #10 (Redis & Caching).

Golden rule of the module: this is a module about pagination in isolation. If along the way you discover your app needs soft deletes or multi-tenancy, note it down and keep going. Those patterns come in their own modules.

Traps to avoid while taking the module

1. "Cursor is always better than OFFSET, so I'm going to skip capsule 02."

If you skip capsule 02, you'll come out convinced that OFFSET should never be used — and that's false. Capsule 02 shows you when OFFSET is the right choice (a UI with page numbers, small datasets) and when it breaks. Without that decision tree, you'll over-apply cursor in cases where it needlessly complicates the frontend's life. Read 02 even if it seems "basic."

2. "The cursor is just base64 encoding, I don't need HMAC."

Tempting, until a curious client decodes your cursor, sees {"created_at": "...", "id": 12345, "tenant_id": 7}, and starts tampering with tenant_id to see other tenants' data. HMAC turns the cursor into something the client cannot forge. It's 10 lines of code and it saves you a security incident. Capsule 06 covers it — don't skip it.

3. "Composite cursors are the same as simple cursors, just with more fields."

No. A simple cursor compares one column (WHERE created_at < $1). A composite cursor compares a tuple of columns (WHERE (created_at, id) < ($1, $2)). The SQL syntax is different, and the typical mistake is comparing column by column with AND/OR, which breaks at boundaries. Capsule 05 shows you the real SQL generated by SQLAlchemy and why tuple comparison is the only correct way.

4. "I'm going to benchmark with a 10k-row table."

You won't see the difference. OFFSET 100 and cursor on 10k rows look almost identical. The difference becomes visceral on large tables with deep pages (page 50,000 on 5M rows). The module project (capsule 08) gives you the seeding script to get there. Don't skip that setup.

5. "Cursor pagination solves the 'total count' problem."

It doesn't. COUNT(*) on a large table is slow regardless of how you paginate. If you need "Page 47 of 12,500", you need an exact COUNT(*), which is expensive. Cursor pagination avoids that problem by eliminating the notion of "page N of M" — you only have "next page" and "previous page." If your UI really needs "12,500 pages total," cursor won't work for you and you go back to OFFSET.

6. "I'm going to implement cursor to get the experience, even though my app has 200 records."

Over-engineering. If your app has <5k records and will never have deep pages, cursor is unnecessary complexity. The pedagogical rule: learn cursor for when you need it — but don't ship it to production "just in case."

Self-assessment question

Before starting this module, try to answer honestly:

Why does OFFSET 50000 read 50,050 rows in PostgreSQL even with a perfect index?
If your customer asks you to "go to page 47" and your API uses cursor pagination, what do you tell them?
What is the minimum information a cursor needs so the next query is stable when there are multiple rows in the same second?
Why is signing the cursor with HMAC not decoration, but security?
How is tuple comparison (WHERE (a, b) < ($1, $2)) like the way you order words in a dictionary?
When is COUNT(*) the right decision, and when is it the thing that's killing your API?

If you hesitated on any of them, this module is for you. If you answered them all with confidence, read it anyway — you'll find nuances you only learn by implementing cursor pagination in production.

Evidence of success

By the end of the module, you'll know you succeeded if:

You can explain to a colleague when NOT to use cursor pagination with a concrete example (an admin table UI with page numbers)
You have a FastAPI endpoint with cursor pagination that holds constant latency at page 50,000 on a 5M-row table
Your cursor is HMAC-signed and rejects tampered tokens with HTTP 400
You handle composite cursors on (created_at DESC, id DESC) without collisions
You document the real measured speedup in BENCHMARKS.md (expected: ~17x on a deep page, Design Gurus)
When someone says "let's add pagination," your first question is "does the UI have page numbers or is it infinite scroll?"

How to get the most out of this module

Estimated time: 1 hour reading + 1.5-2 hours implementing code + ~30 min running the benchmarks from capsule 08.

Minimum recommended setup:

macOS or Linux (Windows works in WSL2)
Python 3.11+
PostgreSQL 16+ installed locally or in Docker
SQLAlchemy 2.0+, FastAPI 0.110+, asyncpg, Pydantic v2 — installed in capsule 04
Familiarity with EXPLAIN ANALYZE (covered in guide #12 module 2)
Familiarity with SQLAlchemy 2.0 async (covered in guide #8)

Dependencies are installed in their respective capsules — you don't need everything ready right now. Each technical capsule starts with its own setup section.

We start in the next capsule

Capsule 02 — OFFSET pagination and its limits — is the closing of the wound guide #12 left you with. You're going to understand exactly why OFFSET 50000 is O(n) by reading PostgreSQL's plan step by step, you'll see the full decision tree (when OFFSET is still correct), and you'll come out with the motivation to get into cursor pagination in capsule 03.

Before moving on, make sure you can answer: what happens internally when you run SELECT ... LIMIT 50 OFFSET 50000 with a perfect index on the sort column?

If you don't have a clear answer, perfect — that's what the next capsule is for.

Summary

Cursor pagination isn't magic, it's a concrete trade-off. It saves you 17x on a deep page but you lose "go to page 47".
The decision tree matters more than the implementation: cursor for infinite scroll/feeds; OFFSET for admin tables with page numbers; keyset when you have full control of the client and don't need opacity.
You're going to build an endpoint with cursor pagination that holds constant latency at page 50,000 on a 5M-row table.
The HMAC-signed cursor isn't decoration — it's what stops a client from tampering with the cursor to read other tenants' data.
Composite cursors with tuple comparison (WHERE (a, b) < ($1, $2)) are the only correct way to paginate when the sort has more than one column.
This module closes the OFFSET → cursor loop that guide #12 left open. The narrative continuity with module 8 of #12 is direct.

Resources for the module

Markus Winand — "We need tool support for keyset pagination" — the canonical reference on why OFFSET is problematic and why keyset is the right alternative. Required reading for the whole module.
Slack Engineering — "Evolving API Pagination at Slack" — a real case of how Slack moved from OFFSET to cursor in their public API. Excellent for understanding the practical "why."
Brandur Leach — "API Paginations Design" — a deep analysis of the cursor vs OFFSET trade-off with code and references to Stripe.
Stripe API Reference — Pagination — the de facto standard of cursor pagination in public APIs. It uses starting_after / ending_before.
PostgreSQL Documentation — LIMIT and OFFSET — the official reference. The warning about rows getting "skipped" with a large OFFSET is in the docs themselves.
Milan Jovanović — "Pagination in EF Core: Keyset vs Offset" — although it's .NET, the concepts and benchmarks transfer. Good diagrams.
Design Gurus — "Cursor vs OFFSET pagination" — the source of the "17x speedup" benchmark we validate in this module.
Aaron Patterson (Tenderlove) — "Pagination is for the birds" — a classic talk on why pagination is one of the most underestimated topics in backend.

Module 1 — SQL Patterns for Production APIs Guide

Next capsule: OFFSET pagination and its limits — closing the wound guide #12 left you with.

Next →OFFSET pagination and its limits