Bound result sizes

What you will be able to do

Make the ticket list return one bounded page with one query, at the same speed on page 1 and on page 1,000. Prove the gain with the same data and the same workload as before.

Before you startWhat to know and to have readyAbout 55 minutes

What you need to know

• Reading a plan, and the three indexes of migration 008, from Read a query plan and Index deliberately.
• The ticket list’s code in ticket_api/repository.py: list_query with OFFSET, and two more queries for each ticket, from Safe queries.
• A join, and a subquery in FROM, from Join tables and Readable queries in the SQL course.

What you need on your computer

• The project after the last lesson: migration 008 applied, bench-before.json saved. python -m pytest prints 79 passed.
• PostgreSQL running, with the large data loaded.

This lesson changes four files of ticket_api/ and the API contract, and adds two test files. At the end, your project matches the snapshot end-of-m04, and python -m pytest prints 87 passed.

A shopping list and a bookmark

You need 41 things from the shop. You could walk to the shop, buy one thing, walk home, and do it again 41 times. Each trip is short, but the walking adds up. Or you write a list, and make one trip.

You are reading a long book, and you stopped on page 1,001. Tomorrow you could open the book at the start and count 1,000 pages. Or you open it at your bookmark. Counting gets slower with every page you read; the bookmark takes the same time on any page.

The ticket list does the first thing in both cases, and also carries more than it needs.

The problem: 41 trips, a page that counts, and every column

After 008, the ticket list takes 13.3 ms per page. It is fast, but the code still has three problems.

The N+1 query. The code gets one page of 20 tickets with one query. Then, for each ticket, it sends two more queries: the customer’s name and the latest AI run. That is 1 + 20 + 20 = 41 queries, 41 trips to the database and back. The name is the N+1 query: one query for the list, then N more, one per row. It is the most common performance mistake in code that reads related rows in a loop.

OFFSET. To show page 2, the code asks for LIMIT 20 OFFSET 20: skip 20 rows, then return 20. PostgreSQL cannot jump to row 21. It must find the first 20 rows, step over them, and only then return the next 20. On a deep page, that is slow. The course measured page 1,001 of the closed shipping list (OFFSET 20000), with the index. The plan read 20,020 rows to return 20, in 4.4 ms, and the endpoint took 21.5 ms.

OFFSET also gives wrong pages while tickets arrive. Try it in psql on the large data. The transaction makes a new ticket arrive between two pages, then undoes everything:

BEGIN;
SELECT ticket_id, created_at FROM tickets WHERE status = 'open' AND team = 'billing'
ORDER BY created_at DESC, ticket_id DESC LIMIT 3 OFFSET 0;
INSERT INTO tickets (ticket_id, customer_id, subject, body, channel, team, priority)
VALUES ('T-400001', 'C-0001', 'Charged twice', 'A new ticket arrives.', 'email', 'billing', 2);
SELECT ticket_id, created_at FROM tickets WHERE status = 'open' AND team = 'billing'
ORDER BY created_at DESC, ticket_id DESC LIMIT 3 OFFSET 3;
ROLLBACK;

 ticket_id |       created_at
-----------+------------------------
 T-399991  | 2026-10-08 20:44:09+00
 T-399988  | 2026-10-08 20:39:23+00
 T-399979  | 2026-10-08 20:23:56+00
(3 rows)

INSERT 0 1
 ticket_id |       created_at
-----------+------------------------
 T-399979  | 2026-10-08 20:23:56+00
 T-399955  | 2026-10-08 19:50:11+00
 T-399954  | 2026-10-08 19:50:09+00
(3 rows)

T-399979 is on both pages. The new ticket went to the top and pushed every row down by one. So “skip 3” now skips one row less than before. An agent who works through the list sees one ticket twice; when a ticket closes, an agent misses one instead.

Every column. The list query is SELECT *. That includes body, the full text of each ticket, which the list never shows. The plan says width=201: about 201 bytes per row. Migrations showed a second cost: after a migration adds a column, a prepared SELECT * can fail.

The ideas: one query, a keyset, named columns

One query with joins. Ask for everything that the page shows in one query. The customer’s name is a join. The latest AI run is harder: each ticket needs its newest run, and only one. LEFT JOIN LATERAL does that. LATERAL lets a subquery in FROM use the columns of the row before it, so it runs once per ticket, with that ticket’s ticket_id. LEFT keeps a ticket that has no run, with empty run columns.

The same page of 20 tickets, before and after this lesson. The times are medians under the course's workload, with the indexes of 008.

Text description of the diagram

Before, the API sends 41 queries to PostgreSQL for one page of open billing tickets. One query gets the 20 tickets. Then 20 queries get the customers’ names and 20 get the latest AI runs, one ticket at a time. That is 41 round trips per page, and 13.3 ms per request with the indexes. After, the API sends one query: tickets JOIN customers, and LEFT JOIN LATERAL for the latest run. That is one round trip per page, and 2.8 ms per request with the indexes.

Keyset pagination. Instead of “skip 20,000 rows”, say “start after this row”. The list is sorted by created_at DESC, ticket_id DESC, so the last row of a page is a bookmark: its time and its ID. The next page asks for the rows that come after it in that order:

WHERE (t.created_at, t.ticket_id) < ('2026-06-26 16:38:57+00', 'T-314017')

This is a row comparison: it compares the times first, and the IDs only when the times are equal. Two tickets can have the same time, so the ID breaks the tie, and no row is lost or repeated. The comparison has the same columns and order as the index of 008. So the database searches the index once, lands on the bookmark and reads on. That is keyset pagination: the page is found by its key, not by counting.

Page 1,001 of the closed shipping list. OFFSET reads every row before the page; the keyset jumps to the bookmark. Plan times from the course run.

Text description of the diagram

The closed shipping list, 20 tickets per page, with the index (status, team, created_at DESC, ticket_id DESC). With OFFSET 20000, the database reads 20,000 index entries and throws them away, then returns the next 20; 56,140 more entries follow. It reads 20,020 rows, and the plan takes 4.4 ms; the deeper the page, the more it reads. With the keyset, after the row (2026-06-26 16:38:57, T-314017), one index search jumps over the first 20,000 entries. The database reads 21 rows: 20 for the page and 1 to know that a next page exists. The plan takes 0.085 ms, on page 1 or page 1,001.

The API gives the client the bookmark as a cursor: the next value of each page. The client sends it back as after. The cursor is the time and the ID, joined with | and encoded with base64 so that it fits in a URL. It is not a secret and not a signature. A client that changes it gets the page after another row, or an error.

Name the columns. List only the columns that the page shows. This is a projection. The new query returns 12 columns; the plan says width=104 instead of width=201.

Larkfield’s new list query

Here is the new list_query in ticket_api/repository.py. The columns come first:

ticket_api/repository.py (part)

LIST_COLUMNS = """
    t.ticket_id, t.customer_id, c.name AS customer_name, t.subject, t.team, t.priority,
    t.status, t.created_at, t.message_count, t.last_message_at,
    r.model AS last_run_model, r.status AS last_run_status
"""

ticket_api/repository.py (part)

    where, params = [], {"limit": limit + 1}
    for column, value in (("status", status), ("team", team), ("customer_id", customer_id)):
        if value is not None:
            where.append(f"t.{column} = %({column})s")
            params[column] = value
    if after:
        params["after_at"], params["after_id"] = decode_cursor(after)
        where.append("(t.created_at, t.ticket_id) < (%(after_at)s, %(after_id)s)")
    sql = (
        f"SELECT {LIST_COLUMNS}"
        " FROM tickets t JOIN customers c ON c.customer_id = t.customer_id"
        " LEFT JOIN LATERAL (SELECT a.model, a.status FROM ai_runs a WHERE a.ticket_id ="
        " t.ticket_id"
        " ORDER BY a.created_at DESC LIMIT 1) r ON true"
        + (" WHERE " + " AND ".join(where) if where else "")
        + " ORDER BY t.created_at DESC, t.ticket_id DESC LIMIT %(limit)s"
    )

Three details matter:

• limit + 1. The query asks for 21 rows for a page of 20. If the 21st row comes back, there is a next page, so list_tickets returns the first 20 and a next cursor made from the 20th row. If not, next is empty. One extra row is cheaper than a second query that counts.
• The values are still parameters, as Safe queries requires. The cursor’s time and ID go in as %(after_at)s and %(after_id)s. decode_cursor raises InvalidCursor for a value that the API did not make, and the error handler turns it into 422 invalid_request with fields: ["after"].
• The page has a maximum. limit must be between 1 and 100, as before. A client cannot ask for 300,000 rows in one page.

The API changes too: offset is gone, after is new, and the response has next instead of next_offset. So contract/openapi.json changes, and the contract test checks it. The plan of the new query, on the large data with 008, from the course run:

Limit  (cost=1.13..195.36 rows=21 width=104) (actual time=0.011..0.074 rows=21.00 loops=1)
  Buffers: shared hit=164
  ->  Nested Loop Left Join  (cost=1.13..15743.11 rows=1702 width=104) (actual time=0.011..0.074 rows=21.00 loops=1)
        Buffers: shared hit=164
        ->  Nested Loop  (cost=0.71..6662.02 rows=1702 width=89) (actual time=0.008..0.039 rows=21.00 loops=1)
              Buffers: shared hit=80
              ->  Index Scan using tickets_queue_idx on tickets t  (cost=0.42..4842.91 rows=1702 width=77) (actual time=0.006..0.010 rows=21.00 loops=1)
                    Index Cond: ((status = 'open'::text) AND (team = 'billing'::text))
                    Index Searches: 1
                    Buffers: shared hit=17
              ->  Index Scan using customers_pkey on customers c  (cost=0.29..1.07 rows=1 width=19) (actual time=0.001..0.001 rows=1.00 loops=21)
                    Index Cond: (customer_id = t.customer_id)
                    Index Searches: 21
                    Buffers: shared hit=63
        ->  Limit  (cost=0.42..5.32 rows=1 width=23) (actual time=0.001..0.002 rows=1.00 loops=21)
              Buffers: shared hit=84
              ->  Index Scan using ai_runs_ticket_idx on ai_runs a  (cost=0.42..10.21 rows=2 width=23) (actual time=0.001..0.001 rows=1.00 loops=21)
                    Index Cond: (ticket_id = t.ticket_id)
                    Index Searches: 21
                    Buffers: shared hit=84
Planning:
  Buffers: shared hit=6
Planning Time: 0.081 ms
Execution Time: 0.081 ms

Read it from the bottom up, with the last lesson’s eyes. The queue index gives 21 tickets in order. For each one, a nested loop looks up its customer by key (loops=21) and its newest run through ai_runs_ticket_idx (loops=21). Every step is an index lookup, and the whole query reads 164 pages, in 0.08 ms (median of 5). The 20 lookups of the N+1 code are still there, but inside one query, with no trip to Python and back between them.

Measure before optimizing

Is one query always faster than 41? Mei measured both versions of the code, with and without the indexes, on the large data. The workload was the same: 5 passes of 200 requests, 4 at a time.

• No indexes
• With 008

The ticket list endpoint under the course's workload (scripts.bench: the open page of each of the 5 teams, 200 requests, 4 at a time, median of 5 passes), on the large data. N+1 is the Module 3 code (41 queries, OFFSET); one query is this lesson's code.

Text description of the chart

A column chart of the median time per request. Without indexes: the N+1 code 375 ms, and the one-query code 1,148 ms. With the indexes of 008: the N+1 code 13.3 ms, and the one-query code 2.8 ms.

Without indexes, the one query is 3 times slower than the 41 queries: 1,148 ms against 375 ms. With the indexes, it is almost 5 times faster: 2.8 ms against 13.3 ms. The same change helps or hurts, depending on what else is true. So “fewer queries is faster” is a guess, not a rule; the failure case below shows why. Measure every change, one at a time, with the same data and workload.

The whole path of the module, under that workload: 375 ms → 13.3 ms → 2.8 ms per page, and from 10.6 to 1,334.4 requests per second. The deep page after row 20,000 takes 2.9 ms with this lesson’s code; the Module 3 code took 21.5 ms with OFFSET 20000.

Guided practice: one query, keyset pages

1. Run the OFFSET example of this lesson in psql, and find the ticket that is on both pages.

2. Copy the files of this lesson from the snapshot end-of-m04:

   macOS / Linux

   S=../nextia-learning-labs/C20/snapshots/end-of-m04
   for f in ticket_api/repository.py ticket_api/tickets.py ticket_api/models.py ticket_api/errors.py contract/openapi.json tests/test_indexes.py tests/test_pagination.py; do cp "$S/$f" "$f"; done

   Windows (PowerShell)

   $S = "..\nextia-learning-labs\C20\snapshots\end-of-m04"
   foreach ($f in 'ticket_api\repository.py', 'ticket_api\tickets.py', 'ticket_api\models.py', 'ticket_api\errors.py', 'contract\openapi.json', 'tests\test_indexes.py', 'tests\test_pagination.py') { Copy-Item "$S\$f" $f }

   Open ticket_api/repository.py, and find list_query, list_tickets, encode_cursor and decode_cursor.

3. Run the tests: python -m pytest. Then run the module’s new tests alone, with their names: python -m pytest tests/test_indexes.py tests/test_pagination.py -v.

4. Explain the new query: python -m scripts.explain queue.

5. Measure with the same workload as at the start of the module, and save the result:

   python -m scripts.bench --json bench-after.json

6. Compare the two files. This one line works in both terminals:

   python -c "import json; a, b = (json.load(open(f)) for f in ('bench-before.json', 'bench-after.json')); print('same workload:', all(a[k] == b[k] for k in ('paths', 'requests', 'concurrency', 'pool'))); [print(k, a[k], '->', b[k]) for k in ('median_ms', 'p95_ms', 'per_second', 'queries_per_request')]"

7. Run python -m scripts.bench once more, to see that the result repeats.

8. Start the API with fastapi dev. In a second terminal, ask for three closed shipping tickets. If your .env sets API_KEY, add -H "X-API-Key: <your key>"; on Windows, type curl.exe:

   curl -s 'http://127.0.0.1:8000/v1/tickets?status=closed&team=shipping&limit=3'

   Then ask for the next page: add &after=<the next value> to the same address. Last, send a broken cursor: curl -s 'http://127.0.0.1:8000/v1/tickets?after=not-a-cursor'. Stop the API with Ctrl+C.

Show the expected result

Step 1: T-399979 is the last row of page 1 and the first row of page 2. Step 3: 87 passed. The new tests include test_the_ticket_list_uses_the_queue_index, test_the_pages_together_have_every_ticket_once, test_a_new_ticket_does_not_shift_the_next_page, test_a_broken_cursor_is_422 and test_the_page_size_has_a_maximum. Step 4 shows the plan of this lesson: Nested Loop Left Join, three Index Scan steps, no Seq Scan.

Steps 5 and 6, from the author’s run on a fresh copy of the project. Your numbers will differ; compare only your own runs.

same workload: True
median_ms 390.4 -> 2.9
p95_ms 428.4 -> 6.6
per_second 10.2 -> 1168.5
queries_per_request [41] -> [1]

Step 7 gives a median close to step 5’s: 2.9 ms again in the same run. Step 8, the first page (the author’s run, one line, shortened here to its first ticket and its end):

{"items":[{"ticket_id":"T-398266","customer_id":"C-17526","customer_name":"Theo Gomez","subject":"Change delivery address","team":"shipping","priority":3,"status":"closed","created_at":"2026-10-06T20:27:26Z","message_count":1,"last_message_at":"2026-10-07T13:46:26Z","last_run_model":"keywords-1.1","last_run_status":"ok"}, … ],"next":"MjAyNi0xMC0wNlQxOToyOTo0MyswMDowMHxULTM5ODIyNw"}

The next page starts with T-398225, the ticket after T-398227 in the list’s order. The next value decodes to 2026-10-06T19:29:43+00:00|T-398227: the time and the ID of the last ticket of the first page. The broken cursor gives 422:

{"error":{"code":"invalid_request","message":"The value of after is not valid.","request_id":"bc473ae19341","fields":["after"]}}

Module check: a repeatable improvement

Step 6 is the module’s completion check. It compares two runs of the same workload: the same requests in the same order, at the same concurrency, on the same data. same workload: True proves that. Between the two files, only 008 and this lesson’s code changed. The result is many times faster, with 1 query per request instead of 41. Step 7 shows that the result repeats. A single fast run could be luck; two runs close together are a measurement.

The tests guard the change from now on. test_indexes.py checks that the list and the latest-run query can use their indexes. test_pagination.py walks all the pages, checks that each ticket comes exactly once with one query per page, and adds a ticket between two pages.

Your turn: a bigger page

Change one condition: the page size. Predict first: a page of 100 tickets has 5 times the rows of a page of 20. Is it 5 times slower? Measure both on the closed shipping list:

python -m scripts.bench --path "/v1/tickets?status=closed&team=shipping&limit=20"
python -m scripts.bench --path "/v1/tickets?status=closed&team=shipping&limit=100"

Show an example answer

In the author’s run, the median was 3.1 ms for 20 tickets and 4.4 ms for 100. Your numbers differ, but the larger page is much less than 5 times slower. Each request has a fixed cost: the HTTP request, the trip to the database, the start of the query. The rows themselves are cheap here, because each one is three index lookups. The cost grows with the rows, though: JSON, network and the client’s memory. That is why the API caps limit at 100: a client cannot turn one request into a copy of the table. With OFFSET, a large page size would also hide a slow deep page. With the keyset, every page costs the same.

Common mistake

One query, and 3 times slower

Symptom: Amira rewrites the list as one query on a copy of the database without the indexes of 008. scripts.bench says database queries per request: 1, as hoped, but the median is about 1,148 ms, against 375 ms for the 41 queries.

Cause: the LATERAL subquery runs once per ticket, 21 times, and without ai_runs_ticket_idx each run is a full scan of 449,734 AI runs. The N+1 code also did 20 such scans, but each of its separate queries got two parallel workers. A subquery inside a nested loop does not, so each scan took twice as long.

Diagnose: explain the query. The course’s run, on the large data without 008 (the last lines of the plan):

        ->  Limit  (cost=16101.68..16101.69 rows=1 width=23) (actual time=15.833..15.833 rows=1.00 loops=21)
              Buffers: shared read=220080
              ->  Sort  (cost=16101.68..16101.69 rows=2 width=23) (actual time=15.832..15.833 rows=1.00 loops=21)
                    Sort Key: a.created_at DESC
                    Sort Method: quicksort  Memory: 25kB
                    Buffers: shared read=220080
                    ->  Seq Scan on ai_runs a  (cost=0.00..16101.67 rows=2 width=23) (actual time=10.168..15.828 rows=1.57 loops=21)
                          Filter: (ticket_id = t.ticket_id)
                          Rows Removed by Filter: 449732
                          Buffers: shared read=220080

Execution Time: 404.769 ms

loops=21 on a Seq Scan: 21 full scans, about 15.8 ms each, and 220,080 pages read for one page of tickets. The nested loop of the last lesson’s warning, live.

Fix: add the index that the inner step needs, ai_runs (ticket_id, created_at DESC). Then measure again: 2.8 ms. In general, change one thing at a time, and measure each step. If the index had come first, the rewrite would have looked like a clear win; done the other way round, it looked like a loss.

When to use keyset pages, and what they cost

Use keyset pagination for lists that people or programs walk page by page, in a fixed order, while new rows arrive: queues, feeds, exports. Use one query with joins whenever the code reads related rows in a loop. Name the columns in every query that an API sends.

The costs:

• No jump to page 57. A keyset can only go to the next page. A user interface that shows page numbers needs OFFSET, or another way. On small lists, OFFSET is fine.
• The sort must be unique and match the index. Without ticket_id as a tie-breaker, two tickets with the same time could be lost or repeated between pages. If the sort changes, the cursor and the index must change with it.
• Clients must change. The old offset parameter is gone, and FastAPI ignores a parameter that it does not know. In the author’s run, …&limit=3&offset=20000 answered 200 with the first page, not the rows after row 20,000. An old client sees no error, just the same page again. Tell the clients before such a change, and give them a version or a date.
• A cursor can go stale. If the bookmarked ticket is deleted, the next page still works, because the comparison needs only the values, not the row.

See it end to endDesign and tune storage for an open public datasetCase study

The Boston 311 case study compares an OFFSET page and a keyset page deep in a neighborhood’s 77,047 requests.

Knowledge check

Check your understanding

Why does OFFSET 20000 get slower the deeper the page, while the keyset page takes the same time on page 1 and page 1,001?

Show the answer

With OFFSET, the database cannot know where row 20,001 is without reading the 20,000 rows before it. So it reads 20,020 rows and throws 20,000 away. The keyset gives it a value to search for: (created_at, ticket_id) < (…). With the index on (status, team, created_at DESC, ticket_id DESC), it finds that place with one search and reads 21 rows. The work depends on the page size, not on how deep the page is.

Check your understanding

The new query still does 20 lookups of the latest AI run, just like the N+1 code. So why is it faster?

Show the answer

The lookups happen inside one query, inside the database. The N+1 code sent 41 separate statements. Each one is a trip from Python to PostgreSQL and back, with its own parsing, planning and result. With the indexes, each lookup itself takes microseconds, so the trips were most of the time. The measured result: 13.3 ms with 41 queries, 2.8 ms with one.

Check your understanding

A colleague says: 'One query is always faster than many.' Use this module's measurements to answer.

Show the answer

Not always. Without the indexes, the one-query list took 1,148 ms against 375 ms for 41 queries. Its LATERAL subquery scanned all of ai_runs 21 times, with no parallel workers. With the indexes, the one query was faster: 2.8 against 13.3 ms. The order of changes and the data decide. So measure each change with the same data and workload, and read the plan when a result surprises you.

Recap

• The N+1 query sends one query for a list, then one per row. One query with a join and LEFT JOIN LATERAL gets the page, the names and the latest runs together.
• OFFSET reads and throws away every row before the page, and pages shift when rows arrive. Keyset pagination starts after the last row’s (created_at, ticket_id), the order of the index: 0.085 ms on any page.
• The API returns a next cursor and takes after; a page has at most 100 rows; the query names its 12 columns instead of SELECT *.
• Measure before optimizing: without indexes, one query was 3 times slower than 41. With them: 375 → 13.3 → 2.8 ms per page under the same workload.
• bench-before.json and bench-after.json prove a repeatable improvement. Your project matches end-of-m04: 87 passed. Next: files, in object storage.

Next: Object-storage patterns