Keyset pagination and when to use it

Capsule overview

Up to here you used cursor pagination with an opaque cursor that wraps two values: timestamp + id. Internally, the SQL query is keyset pagination with tuple comparison: WHERE (created_at, id) < ($1, $2). This capsule takes that SQL expression apart down to its pieces, shows you why it's the only correct way to compare tuples in SQL, and teaches you to generalize it for sorting by more columns or by columns with mixed directions (ASC on one, DESC on another).

You're going to understand:

• What tuple comparison is in standard SQL and why PostgreSQL implements it natively
• The classic trap: WHERE a < $1 OR (a = $1 AND b < $2) looks equivalent to tuple comparison but generates worse plans
• When to use pure keyset (the client exposes the values) vs an opaque cursor (an encoded token)
• Multi-column sorting with mixed directions (e.g. ORDER BY priority ASC, created_at DESC, id DESC)
• How to verify with EXPLAIN ANALYZE that the planner uses an Index Scan with Index Cond: ROW(...)

It's the densest SQL capsule of the module. By the end you'll be able to paginate any query correctly, not just the "easy" ones.

Tuple comparison in standard SQL

PostgreSQL (and standard SQL generally) supports comparing tuples element by element, as if they were words in a dictionary:

-- These three comparisons are equivalent to comparing words
SELECT (1, 2) < (1, 3);   -- true (1=1, 2<3)
SELECT (1, 2) < (2, 0);   -- true (1<2)
SELECT (1, 2) < (1, 2);   -- false (equal)
SELECT (2, 0) < (1, 100); -- false (2>1, the second element doesn't matter)

Mental model: lexicographic order.

Comparing (a, b) < (x, y) is like comparing the words "ab" and "xy":

1. Look at the first letter. If a < x, done: the word is smaller.
2. If a > x, done: the word is greater.
3. If a = x, you have to look at the second letter: b vs y.

It's exactly what you do when you sort contacts by last name, then by first name.

(2026-04-15 10:23:45, 12345) < (2026-04-15 10:23:45, 99999)
       ↓                              ↓
   equal timestamps → look at the second element
       ↓                              ↓
              12345 < 99999  →  true

(2026-04-15 10:23:45, 12345) < (2026-04-16 09:00:00, 1)
       ↓                              ↓
   different timestamps → only the first one matters
       ↓                              ↓
   2026-04-15 < 2026-04-16  →  true (the id is irrelevant)

The correct syntax in PostgreSQL

PostgreSQL accepts tuple comparison with (...) or with ROW(...):

-- Both forms are equivalent
WHERE (created_at, id) < ($1, $2)
WHERE ROW(created_at, id) < ROW($1, $2)

ROW(...) is more explicit; (...) is more concise. Both work identically. SQLAlchemy 2.0 with tuple_() generates the ROW(...) version.

Why the exact syntax matters

Here's the critical detail. These two queries return the same results but generate very different plans:

-- ✅ Tuple comparison (correct, efficient)
SELECT * FROM tasks
WHERE (created_at, id) < ('2026-04-15 10:23:45+00', 12345)
ORDER BY created_at DESC, id DESC LIMIT 50;

-- ❌ "Looks equivalent" but internally it is NOT
SELECT * FROM tasks
WHERE created_at < '2026-04-15 10:23:45+00'
   OR (created_at = '2026-04-15 10:23:45+00' AND id < 12345)
ORDER BY created_at DESC, id DESC LIMIT 50;

You'll see the difference in the worked example.

Worked example: comparing the plans of the two forms

We're going to set up the table from capsule 02 (1M rows), run both queries, and look at the plans.

Setup

(if you already have pagination_demo from capsule 02, you can reuse it)

-- Verify the composite index exists
\d tasks
-- Indexes:
--   "tasks_pkey" PRIMARY KEY, btree (id)
--   "idx_tasks_created_at_id_desc" btree (created_at DESC, id DESC)

If you don't have the composite index, create it:

CREATE INDEX IF NOT EXISTS idx_tasks_created_at_id_desc
  ON tasks (created_at DESC, id DESC);
ANALYZE tasks;

Plan 1: tuple comparison (the correct form)

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, created_at FROM tasks
WHERE (created_at, id) < ('2026-04-15 10:23:45+00'::timestamptz, 12345)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Expected output:

Limit  (cost=0.42..2.83 rows=50 width=27) (actual time=0.025..0.215 rows=50 loops=1)
  Buffers: shared hit=4
  ->  Index Scan using idx_tasks_created_at_id_desc on tasks
        (cost=0.42..23502.21 rows=500000 width=27)
        (actual time=0.024..0.208 rows=50 loops=1)
        Index Cond: (ROW(created_at, id) < ROW('2026-04-15 10:23:45+00'::timestamptz, 12345))
        Buffers: shared hit=4
Planning Time: 0.108 ms
Execution Time: 0.245 ms

The critical part:

• Index Cond: (ROW(created_at, id) < ROW(...)) — the filter is applied inside the Index Scan.
• Buffers: shared hit=4 — it only reads 4 buffers (the ones it needs).
• Execution Time: 0.245 ms — constant, regardless of depth.

Plan 2: the "equivalent" form with OR (the trap)

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, created_at FROM tasks
WHERE created_at < '2026-04-15 10:23:45+00'::timestamptz
   OR (created_at = '2026-04-15 10:23:45+00'::timestamptz AND id < 12345)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Expected output (the figures vary, but the pattern is robust):

Limit  (cost=0.42..3.41 rows=50 width=27) (actual time=0.030..0.295 rows=50 loops=1)
  Buffers: shared hit=4
  ->  Index Scan using idx_tasks_created_at_id_desc on tasks
        (cost=0.42..29845.10 rows=500000 width=27)
        (actual time=0.028..0.288 rows=50 loops=1)
        Filter: ((created_at < '2026-04-15 10:23:45+00'::timestamptz)
              OR ((created_at = '...') AND (id < 12345)))
        Buffers: shared hit=4
Planning Time: 0.142 ms
Execution Time: 0.330 ms

The critical part:

• Filter: ((created_at < ...) OR ...) — the filter is applied AFTER the Index Scan, as a post-scan filter.
• In this particular case the result is similar (because PostgreSQL can still use the ordered Index Scan), but the planner can't use the Index Cond to do a direct seek — it has to scan and filter.

When the OR pattern breaks badly

The problem with the OR version shows up when there are additional conditions. Imagine you also filter by tenant_id:

-- Tuple comparison with an extra filter: PostgreSQL leverages the (tenant_id, created_at, id) index
WHERE tenant_id = 42
  AND (created_at, id) < ('2026-04-15 10:23:45+00', 12345);

-- OR form with an extra filter: the planner gets confused
WHERE tenant_id = 42
  AND (created_at < '2026-04-15 10:23:45+00'
       OR (created_at = '2026-04-15 10:23:45+00' AND id < 12345));

In the first case, the planner keeps the Index Cond and can combine it with a composite index (tenant_id, created_at DESC, id DESC). In the second, the OR makes the planner fall into suboptimal plans (Bitmap Heap Scan + Filter, or worse: Seq Scan).

Universal rule: use tuple comparison (a, b) < ($1, $2). It's the only form the planner knows how to optimize consistently. The OR form is a trap that looks equivalent — but the planner treats it differently.

Deeper coverage of how the planner chooses between Index Scan, Bitmap Heap Scan, and Seq Scan is in guide #12 module 2. The practical rule: if you see Filter: instead of Index Cond: in the EXPLAIN, you lost efficiency.

Sorting by more than two columns

The pattern generalizes. For a sort on (priority DESC, created_at DESC, id DESC):

WHERE (priority, created_at, id) < ($1, $2, $3)
ORDER BY priority DESC, created_at DESC, id DESC
LIMIT 50;

The cursor carries all three values:

{
    "v": 1,
    "p": 5,                          # priority
    "t": "2026-04-15T10:23:45Z",     # created_at
    "i": 12345,                      # id
    "d": "next"
}

And the composite index has to match:

CREATE INDEX idx_tasks_priority_created_id
  ON tasks (priority DESC, created_at DESC, id DESC);

Implementation with SQLAlchemy

from sqlalchemy import select, tuple_

stmt = (
    select(Task)
    .where(
        tuple_(Task.priority, Task.created_at, Task.id)
        < tuple_(cursor_priority, cursor_created_at, cursor_id)
    )
    .order_by(
        Task.priority.desc(),
        Task.created_at.desc(),
        Task.id.desc(),
    )
    .limit(limit + 1)
)

A trivial extension. The only thing that changes is the number of elements in the tuple, and the cursor carries more fields.

Sorting with mixed directions

Here you have to be careful. If the sort is ORDER BY priority ASC, created_at DESC, you can't use tuple comparison directly because the directions differ.

-- ❌ THIS DOES NOT DO WHAT IT LOOKS LIKE
WHERE (priority, created_at) < ($1, $2)
ORDER BY priority ASC, created_at DESC;

Tuple comparison assumes the same direction for every column. For mixed directions, you need to convert manually:

Trick 1: invert the column

If priority is ASC but you want to express "items after the cursor":

-- If the sort is priority ASC, "after" means a GREATER priority
-- If the sort is created_at DESC, "after" means a SMALLER created_at
-- You mix both with explicit AND/OR:

WHERE priority > $1
   OR (priority = $1 AND created_at < $2)
   OR (priority = $1 AND created_at = $2 AND id < $3)
ORDER BY priority ASC, created_at DESC, id DESC
LIMIT 50;

It's more verbose, but it's the correct thing to do when the directions are mixed.

Trick 2: invert the problematic column's sort through the WHERE

If you have priority ASC but want it to behave like DESC in the tuple, you can invert the value at runtime (multiply by -1, etc.). But that requires functional indexes and complicates everything. Not recommended except in extreme cases.

Trick 3 (recommended): choose a sort that is uniformly ASC or uniformly DESC

In practice, you can almost always design the sort so every column goes in the same direction:

✅ ORDER BY created_at DESC, id DESC          (all DESC, direct tuple comparison)
✅ ORDER BY priority DESC, created_at DESC, id DESC  (all DESC)
❌ ORDER BY priority ASC, created_at DESC     (mixed, requires explicit AND/OR)

If your UX requires priority ASC (low to high), consider redefining priority so lower values mean high priority, making DESC make sense. Or, if it's very specific, accept the complexity of the explicit WHERE.

To go deeper on this topic, see Markus Winand's blog on keyset pagination with mixed sorts: he covers it in detail on use-the-index-luke.com.

Pure keyset vs opaque cursor: when to choose each

We already saw in capsule 03 that an opaque cursor is the default choice for public APIs. But there are cases where pure keyset (the values exposed directly in the URL) is the right choice:

Pure keyset wins when:

• It's an internal API between your own services. You don't need opacity because you control both sides.
• The client needs to persist the cursor to resume later. A pure keyset is readable and can be inspected/logged (?after_id=12345&after_t=2026-04-15T10:23:45Z). An opaque cursor can't.
• You want the user to be able to build URLs by hand. Useful in admin tools or CSV exports.
• The sort is stable and the schema isn't going to change. If you expose the values and tomorrow you switch from (created_at, id) to (updated_at, id), you break your consumers. If you know the schema is stable for years, that risk is low.

An opaque cursor wins when:

• It's a public API with external consumers. You don't want to expose schema details.
• You want to sign the cursor with HMAC (capsule 06) to prevent tampering.
• The schema may evolve — opacity gives you the flexibility to change things internally without breaking clients.
• You want version validation of the cursor format (rejecting old versions).

Summary table

Pragmatic rule: public API → opaque cursor. Internal API between your own services → pure keyset is fine. If in doubt, opaque cursor (it's safer and more flexible).

Why does this matter in real work?

1. Code review of pagination queries. When someone on your team proposes WHERE created_at < $1 OR (created_at = $1 AND id < $2), you'll be able to point out that tuple comparison is the correct form and show the difference in PostgreSQL's plan. It's the kind of feedback that separates a senior dev from a junior.

2. Designing indexes to support pagination. Knowing that you need a composite index whose order matches the ORDER BY exactly lets you design the schema correctly from the start. Without this, you add cursor pagination and discover the plan is still slow (because it falls into a Sort after the Index Scan).

3. Handling multi-column queries. Most real UIs don't sort by a single column. "Sort by priority, then by date" is a classic case. If you only know cursor over one column, you'll be limited to simple cases.

4. Public vs internal API decisions. You work at a company with microservices. Some APIs are public (external customers), others are internal. Knowing when to use pure keyset vs an opaque cursor gives you the language to defend the architectural decision in design reviews.

Traps and common mistakes

Mistake 1 (conceptual): thinking "tuple comparison is just syntactic sugar"

Symptom: "(a, b) < (x, y) is the same as a < x OR (a = x AND b < y)."

Why it's wrong: they're equivalent in boolean logic, but the planner treats them differently. Tuple comparison is a specific SQL operator the planner knows how to optimize as an Index Cond. The OR form ends up as a post-scan Filter, which:

• Doesn't leverage the index's order as much as it could.
• Breaks worse when there are additional conditions (AND tenant_id = $3).
• Is harder to read.

How to tell: look at the plan. If you see Index Cond: with ROW(...), you're fine. If you see Filter: with the OR expression, you're losing efficiency.

Mistake 2 (practical): not creating the composite index that matches the sort

Symptom: the query with a cursor "works," but EXPLAIN shows a Sort before Limit.

Why it happens: without a composite index (created_at DESC, id DESC), the planner has to pull rows from the simple (created_at) index and then sort by id to break timestamp ties. It's inefficient.

How to tell:

->  Sort
      Sort Key: tasks.created_at DESC, tasks.id DESC
      ->  Index Scan using idx_tasks_created_at on tasks

If you see Sort above Index Scan, you're missing the composite index.

How to fix it:

CREATE INDEX idx_tasks_created_at_id_desc ON tasks (created_at DESC, id DESC);

Mistake 3 (conceptual): assuming tuple comparison works with mixed directions

Symptom: a sort of ORDER BY priority ASC, created_at DESC with a cursor (priority, created_at) < ($1, $2). Some items show up duplicated or get skipped.

Why it happens: tuple comparison assumes all columns go in the same direction. (priority, created_at) < (5, '2026-04-15') means "priority < 5 OR (priority = 5 AND created_at < '...')", which is not what you want when priority is ASC.

How to fix it: either redefine the sort so every column is DESC, or use the explicit AND/OR pattern:

-- Sort: priority ASC, created_at DESC, id DESC
-- Cursor: priority=5, created_at='2026-04-15...', id=12345
WHERE priority > $1
   OR (priority = $1 AND created_at < $2)
   OR (priority = $1 AND created_at = $2 AND id < $3);

Mistake 4 (practical): exposing pure keyset in a public API without thinking it through

Symptom: you shipped ?after_id=12345&after_created_at=2026-04-15T10:23:45Z in a public API. Six months later you need to change the sort from (created_at, id) to (updated_at, id). You break 50 consumers.

Why it happens: pure keyset leaks the internal sort. Any client that persisted URLs with those parameters depends on your internal sort.

How to fix it: for public APIs, use an opaque cursor from day 1. Even if it seems like overkill at first, it pays a dividend when you need to evolve.

Mistake 5 (edge case): a cursor that points at a deleted row

Symptom: the client sends a cursor pointing at a (created_at, id) that no longer exists (the item was deleted between pages).

Why it happens: cursor pagination doesn't require the row the cursor points at to exist. The query is WHERE (created_at, id) < ($1, $2) — it only needs a comparison point, not for the row to exist.

How to tell: the query works normally. The client gets the "next page" as if nothing happened. This is expected behavior, not a bug.

How to handle it (if you want to be explicit): you can decide to return a warning or continue silently. Most APIs (Stripe, GitHub) continue silently — the cursor is a "point in time," not a "reference to a specific row."

Exercises

Exercise 1: compare the plans in your DB

Using the tasks table from previous capsules, run both queries (tuple comparison vs OR) and compare them with EXPLAIN ANALYZE. What differences do you see in Index Cond: vs Filter:?

psql -d pagination_demo

-- Form 1: tuple comparison
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, created_at FROM tasks
WHERE (created_at, id) < ('2026-04-15 10:23:45+00'::timestamptz, 12345)
ORDER BY created_at DESC, id DESC LIMIT 50;

Limit  (actual time=0.025..0.215 rows=50 loops=1)
  Buffers: shared hit=4
  ->  Index Scan using idx_tasks_created_at_id_desc on tasks
        (actual time=0.024..0.208 rows=50 loops=1)
        Index Cond: (ROW(created_at, id) < ROW('2026-04-15 10:23:45+00'::timestamptz, 12345))
        Buffers: shared hit=4

-- Form 2: explicit OR
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, created_at FROM tasks
WHERE created_at < '2026-04-15 10:23:45+00'::timestamptz
   OR (created_at = '2026-04-15 10:23:45+00'::timestamptz AND id < 12345)
ORDER BY created_at DESC, id DESC LIMIT 50;

Limit  (actual time=0.030..0.295 rows=50 loops=1)
  Buffers: shared hit=4
  ->  Index Scan using idx_tasks_created_at_id_desc on tasks
        (actual time=0.028..0.288 rows=50 loops=1)
        Filter: ((created_at < '...'::timestamptz)
              OR ((created_at = '...') AND (id < 12345)))
        Buffers: shared hit=4

Exercise 2: add tenant_id and see the scenario where OR blows up

Add a tenant_id column to tasks, fill 5 tenants, and compare plans:

ALTER TABLE tasks ADD COLUMN tenant_id INTEGER NOT NULL DEFAULT 1;
UPDATE tasks SET tenant_id = (random() * 4 + 1)::int;
CREATE INDEX idx_tasks_tenant_created_id
  ON tasks (tenant_id, created_at DESC, id DESC);
ANALYZE tasks;

Run both forms filtering by tenant_id = 3 and compare the plans.

-- Form 1: tuple comparison + tenant_id filter
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, created_at FROM tasks
WHERE tenant_id = 3
  AND (created_at, id) < ('2026-04-15 10:23:45+00'::timestamptz, 12345)
ORDER BY created_at DESC, id DESC LIMIT 50;

Limit  (actual time=0.045..0.265 rows=50 loops=1)
  Buffers: shared hit=8
  ->  Index Scan using idx_tasks_tenant_created_id on tasks
        (actual time=0.043..0.258 rows=50 loops=1)
        Index Cond: ((tenant_id = 3) AND (ROW(created_at, id) < ROW(...)))
        Buffers: shared hit=8
Execution Time: 0.305 ms

-- Form 2: explicit OR + tenant_id filter
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, created_at FROM tasks
WHERE tenant_id = 3
  AND (created_at < '2026-04-15 10:23:45+00'::timestamptz
       OR (created_at = '...'::timestamptz AND id < 12345))
ORDER BY created_at DESC, id DESC LIMIT 50;

Limit  (actual time=8.450..12.380 rows=50 loops=1)
  Buffers: shared hit=2840
  ->  Sort
        Sort Key: tasks.created_at DESC, tasks.id DESC
        ->  Bitmap Heap Scan on tasks
              Recheck Cond: (tenant_id = 3)
              Filter: ((created_at < '...') OR ((created_at = '...') AND (id < 12345)))
              ->  Bitmap Index Scan on idx_tasks_tenant_created_id
                    Index Cond: (tenant_id = 3)
                    Buffers: shared hit=2840
Execution Time: 12.420 ms

Exercise 3: a cursor with three columns

Design a cursor for the sort ORDER BY priority DESC, created_at DESC, id DESC. Modify app/pagination.py so make_task_cursor and parse_task_cursor handle three fields. Then modify list_tasks_paginated so it uses tuple_(Task.priority, Task.created_at, Task.id).

# app/pagination.py
def make_task_cursor_v2(
    priority: int,
    created_at: datetime,
    last_id: int,
) -> str:
    return encode_cursor({
        "v": 1,
        "p": priority,
        "t": created_at.isoformat().replace("+00:00", "Z"),
        "i": last_id,
        "d": "next",
    })


def parse_task_cursor_v2(cursor: str) -> tuple[int, datetime, int]:
    decoded = decode_cursor(cursor)
    try:
        priority = int(decoded["p"])
        ts = datetime.fromisoformat(decoded["t"].replace("Z", "+00:00"))
        last_id = int(decoded["i"])
    except (KeyError, ValueError, TypeError) as e:
        raise CursorError("Cursor with invalid fields") from e
    return priority, ts, last_id

# app/repositories/tasks.py
async def list_tasks_paginated_v2(
    session: AsyncSession,
    cursor: str | None = None,
    limit: int = 50,
) -> Page[TaskOut]:
    stmt = select(Task).order_by(
        Task.priority.desc(),
        Task.created_at.desc(),
        Task.id.desc(),
    )

    if cursor is not None:
        c_priority, c_created_at, c_id = parse_task_cursor_v2(cursor)
        stmt = stmt.where(
            tuple_(Task.priority, Task.created_at, Task.id)
            < tuple_(c_priority, c_created_at, c_id)
        )

    stmt = stmt.limit(limit + 1)
    result = await session.execute(stmt)
    rows = result.scalars().all()

    has_more = len(rows) > limit
    page_items = rows[:limit]

    next_cursor = None
    if has_more and page_items:
        last = page_items[-1]
        next_cursor = make_task_cursor_v2(last.priority, last.created_at, last.id)

    return Page[TaskOut](
        items=[TaskOut.model_validate(t) for t in page_items],
        next_cursor=next_cursor,
        has_more=has_more,
    )

CREATE INDEX idx_tasks_priority_created_id
  ON tasks (priority DESC, created_at DESC, id DESC);

EXPLAIN (ANALYZE)
SELECT * FROM tasks
WHERE (priority, created_at, id) < (5, '2026-04-15 10:23:45+00'::timestamptz, 12345)
ORDER BY priority DESC, created_at DESC, id DESC LIMIT 50;

Exercise 4: implement a sort with mixed directions

The sort is ORDER BY priority ASC, created_at DESC, id DESC. You can't use tuple comparison directly. Implement the query with explicit AND/OR.

from sqlalchemy import or_, and_

async def list_tasks_mixed_sort(
    session: AsyncSession,
    cursor: str | None = None,
    limit: int = 50,
) -> Page[TaskOut]:
    stmt = select(Task).order_by(
        Task.priority.asc(),       # ASC (mixed)
        Task.created_at.desc(),    # DESC
        Task.id.desc(),            # DESC
    )

    if cursor is not None:
        c_priority, c_created_at, c_id = parse_task_cursor_v2(cursor)
        # Mixed sort requires explicit AND/OR
        stmt = stmt.where(
            or_(
                Task.priority > c_priority,
                and_(
                    Task.priority == c_priority,
                    Task.created_at < c_created_at,
                ),
                and_(
                    Task.priority == c_priority,
                    Task.created_at == c_created_at,
                    Task.id < c_id,
                ),
            )
        )

    stmt = stmt.limit(limit + 1)
    # ... the rest is identical ...

SELECT ... FROM tasks
WHERE priority > $1
   OR (priority = $1 AND created_at < $2)
   OR (priority = $1 AND created_at = $2 AND id < $3)
ORDER BY priority ASC, created_at DESC, id DESC
LIMIT 51;

CREATE INDEX idx_tasks_priority_asc_created_id
  ON tasks (priority ASC, created_at DESC, id DESC);

Exercise 5: decide pure keyset vs opaque cursor

For each case, decide between pure keyset (values exposed in the URL) or an opaque cursor (an encoded token), and justify it:

a) A public API /v1/charges so customers can see their payment history. Stripe-like.

b) An internal endpoint /internal/audit-events that the notification service queries every 5 minutes to sync.

c) An admin tool /admin/users-export where the operator needs to be able to paste the URL into another session to resume an interrupted export.

d) A /feed endpoint for a consumer product (mobile-first), infinite scroll.

Exercise 6: detect the deleted-row trap

Insert a task. Do page 1 with an opaque cursor. Delete the last item of page 1 (the one the cursor points at). Do page 2. What happens?

# tests/test_deleted.py
async def test_cursor_points_to_deleted_row(client, session):
    """The cursor survives the deletion of the row it points at."""
    # Seed 5 tasks
    now = datetime.now(timezone.utc)
    tasks = [
        Task(title=f"task_{i}", created_at=now - timedelta(minutes=i))
        for i in range(5)
    ]
    session.add_all(tasks)
    await session.commit()
    for t in tasks:
        await session.refresh(t)

    # Page 1: 2 items
    res = await client.get("/tasks?limit=2")
    page1 = res.json()
    assert len(page1["items"]) == 2
    last_in_page1_id = page1["items"][-1]["id"]

    # Delete the task the cursor points at
    await session.execute(
        text("DELETE FROM tasks WHERE id = :id"),
        {"id": last_in_page1_id},
    )
    await session.commit()

    # Page 2 with page 1's cursor
    res = await client.get(f"/tasks?limit=2&cursor={page1['next_cursor']}")
    page2 = res.json()

    # The query works normally — the cursor is a "point in time",
    # not a reference to a specific row.
    assert res.status_code == 200
    assert len(page2["items"]) > 0
    # No item from page 2 is duplicated with page 1
    page1_ids = {t["id"] for t in page1["items"]}
    page2_ids = {t["id"] for t in page2["items"]}
    assert page1_ids.isdisjoint(page2_ids)

Summary and next step

In this capsule you learned:

• Tuple comparison ((a, b) < ($1, $2)) is the standard SQL form and the only one the planner optimizes consistently with an Index Cond:.
• The explicit-OR trap (a < $1 OR (a = $1 AND b < $2)) generates worse plans, especially when there are additional filters — it can be 40x slower on queries with an AND.
• The composite index must match the ORDER BY exactly ((created_at DESC, id DESC) requires an index on (created_at DESC, id DESC)).
• Multi-column sorting generalizes trivially: a longer tuple, more fields in the cursor, a wider index.
• Sorting with mixed directions breaks pure tuple comparison — you need explicit AND/OR, or you redefine the sort to be uniform.
• Pure keyset vs opaque cursor is an architectural decision: public → opaque; internal → pure keyset is fine.
• The cursor survives the deletion of the row it points at — that's expected behavior, not a bug.

Before moving on you should be able to:

• Write a query with tuple comparison for a sort on 2-3 columns
• Identify Index Cond: vs Filter: in an EXPLAIN ANALYZE
• Decide between pure keyset and an opaque cursor with a technical argument
• Recognize when a mixed sort requires explicit AND/OR

Next capsule — Bidirectional pagination and opaque cursors. You're going to add the two features that separate a toy implementation from a production one: backward navigation (previous in addition to next) and signing the cursor with HMAC so the client can't tamper with it. You'll understand exactly what happens if someone intercepts your cursor and changes a field, and how you defend against that with 10 lines of code.

Resources

1. Markus Winand — "Pagination Done the PostgreSQL Way" — the reference on keyset, with examples of tuple comparison and why it beats OR.
2. Markus Winand — "We need tool support for keyset pagination" — the canonical "anti-OFFSET." Required reading.
3. PostgreSQL Documentation — Row and Array Comparisons — the official reference for the ROW(...) syntax and tuple comparison.
4. PostgreSQL Wiki — Index Combination Strategies — how the planner combines indexes, useful for understanding why OR breaks the optimization.
5. Brandur Leach — "Building Robust APIs" — discusses pure keyset vs opaque cursor from an API design perspective.
6. Milan Jovanović — "Cursor-Based Pagination in EF Core" — although it's .NET, the tuple comparison concepts are the same. Good diagrams.
7. Stack Overflow — "Why is tuple comparison faster than OR in PostgreSQL?" — a detailed answer from a PostgreSQL committer on why the planner treats them differently.
8. Citus — "Five Tips for Faster Postgres Queries with Indexes" — includes a section on composite indexes and ordering.

Module 1 — SQL Patterns for Production APIs Guide

Next capsule: Bidirectional pagination and opaque cursors — previous/next navigation and HMAC.