Queue and cache for an open-data API

What you will be able to do

Take a real open-data API, put a stated workload on it, and measure it. Then add a cache that is never wrong after new data arrives, and move a slow CSV export into a bounded background queue. Measure again under the same workload, and report what improved, what did not, and what runs out next.

Case studyPortfolio piece

Dataset

NYC 311 Service Requests, transport department (DOT), January to June 2025

Licence

NYC Open Data: no restrictions on use

Time

About 4 hours

Runs in

VS Code, Python 3.12+, Docker

Skills used

• Workload model
• Locust load tests
• Medians of passes
• Cache keys with a data version
• Invalidation on new data
• Jobs in PostgreSQL
• Idempotency keys
• Backpressure with Retry-After

Download the starter project (.zip)Finished project (.zip)

Unzip the starter, open the folder in VS Code, and follow the steps below. They start with a new virtual environment.

The starter is the finished API of the case study Build a data API for city service requests, plus a first CSV export. It moved from a SQLite file to PostgreSQL, because new data must arrive while the API runs. The finished project is the result, for when you are stuck. The code is under the MIT licence.

Before you startWhat to know and to have readyAbout 240 minutes

What you need to know

This case study uses the whole course, on an API that is not Larkfield’s. It leans most on Workload model, I/O versus CPU work, Keys and invalidation, Job lifecycle, Backpressure and Load-test design. You do not need the API course’s case study.

What you need on your computer

• Python 3.12 or newer, VS Code, curl, and Docker Desktop.
• About 300 MB of free disk space: the packages, the data (8 MB to download, 36 MB unpacked) and PostgreSQL.
• About 4 hours, of which about 30 minutes are load tests that keep your computer busy.

Tested on macOS (Apple M4, 16 GB, Docker Desktop with 10 CPUs, PostgreSQL 18.6, Valkey 9.1.2, Python 3.12.13). The Windows and Linux commands are not tested. Every timing is from the author’s computer, which other work shared: your numbers will differ.

The situation

Teresa Okafor leads the analytics team of a city’s transport department. Her team’s API serves the cleaned 311 street-repair requests to every dashboard; Priya Nair builds the dashboards. The people are invented; the requests are New York City’s real open data.

The API is now popular, and two things worry Teresa:

• Monday at 9:00, about 30 people open the dashboards and click through views. Will the views stay fast?
• The CSV export. Analysts asked for a file of a filter, and a first version was added quickly: GET /v1/exports.csv builds the file while the client waits. At the end of each month, 16 analysts export for the monthly report, one file after another. Someone said: “the exports will time out.”

Teresa’s rule: “Measure first. Change one thing at a time. Tell me what got better, what did not, and what will break next.”

The objective. Under a written-down workload, three things. Dashboard requests have a p95 under 300 ms, even while analysts export. Every export ends with a file. And after new data loads, no old number is ever shown.

What you will build

The starter does all the work inside the request. The finished project reads repeated answers from a cache and makes files in separate worker processes.

Text description of the diagram

On the left, one API process does everything, and an export holds a thread, a connection and the CPU until the file is sent. On the right, the API answers aggregates from a cache and answers an export at once; two worker processes write the files.

Part	What changes	Measured after it
1	docs/workload.md: the workload, in numbers	–
2	nothing: the baseline	three runs
3	a Valkey cache for the two aggregate endpoints	three runs
4	the export becomes a job in a bounded queue, done by workers	three runs
5	nothing: the three versions side by side	–
6	new data, end to end	–

The data

The data is New York City’s 311 Service Requests for the transport department, January to June 2025. It has 109,915 requests about potholes, signals, street lights, sidewalks and signs. NYC Open Data says: “There are no restrictions on the use of Open Data.” The API course cleaned the data with nine rules and published it as one SQLite file with a checksum. DATASET.md in the project lists the rules and the limits.

This project loads January to May (90,329 requests) into PostgreSQL first, and June (19,586) later, as new data. Each load is a row in the table data_loads, and its number is the data version.

Set up the project

The steps use VS Code and its terminal. Every command runs in the project folder.

Stop the course's containers first

This project uses the usual ports: PostgreSQL on 5432 and Valkey on 6379. The course’s ticket-api uses the same ones. In the ticket-api folder, run docker compose stop before you start. Or set POSTGRES_PORT and CACHE_PORT in a .env file here, and use the same ports in DATABASE_URL and CACHE_URL.

1. Download the starter project from the box at the top of this page. Unzip it into your projects folder, for example ~/projects. You get the folder city-requests-scaling.

2. In VS Code, choose File > Open Folder, and open city-requests-scaling. Then open a terminal: Terminal > New Terminal.

3. Make and activate a virtual environment, and install the packages:

   macOS

   python3 -m venv .venv
   source .venv/bin/activate
   python -m pip install -r requirements.txt

   Windows

   python -m venv .venv
   .venv\Scripts\Activate.ps1
   python -m pip install -r requirements.txt

   Linux

   python3 -m venv .venv
   source .venv/bin/activate
   python -m pip install -r requirements.txt

   The prompt starts with (.venv). The packages are the course’s versions: FastAPI 0.142.2, psycopg 3.3.6 with its pool, Locust 2.46.7, pytest and ruff.

4. Start PostgreSQL:

   docker compose up -d

5. Download the data, check it, and load January to May into PostgreSQL:

   python scripts/get_db.py
   python -m scripts.load

   Output

   Downloading https://github.com/Nextia-Intelligence/nextia-learning-labs/raw/main/C16/case-studies/data/service_requests_2025h1.db.gz
   Checked service_requests_2025h1.db.gz: SHA-256 26492959add9... is correct
   Wrote data/service_requests_2025h1.db: 109,915 service requests
   Loaded 90,329 requests created from 2025-01-01 to 2025-06-01 (not included).
   Data version: 1 (0.9 s)

6. Run the tests:

   python -m pytest

   Output (end)

   .................................................................        [100%]
   65 passed in 1.95s

What this tells us. The script checked the download’s SHA-256 checksum. The loader put 90,329 requests into PostgreSQL in one transaction: data version 1. The tests use their own small database, city_test, so they never touch your data.

Part 1: write the workload down

A load test answers one question: “Is the service good enough for this traffic?” So the first file is not code. It is docs/workload.md, the workload model for Teresa’s team. The API has no traffic log yet, so the model is the team’s assumptions, written so that anyone can check them:

Who	How many	What they do	Rate
Dashboard viewers	30, on Monday from 9:00	open a view: complaint types, two daily charts, a page, the next page, one request (6 requests)	a view every 10 s each: about 18 requests/s
Analysts, normal morning	4	ask for a CSV file of a filter	an export every 15 s each
Analysts, month end	16	ask for CSV files for the monthly report	back to back

“Today” is early June 2025, and June arrives later as new data. Most views show May or the three months to May, and 40% show all boroughs. Exports are one borough for three months (about 17,000 rows), one complaint type (32,202 rows), or everything (90,329 rows).

The file also says what good means. The dashboard p95 is under 300 ms and the p99 under 500 ms. Every export ends with a file. No old answer comes after a load. A client gives up after 30 seconds.

loadtest/locustfile.py turns the table into two Locust users. Two details matter:

loadtest/locustfile.py (part)

class DashboardViewer(FastHttpUser):
    """Opens a dashboard: six requests, then waits for the next visit."""

    fixed_count = int(os.environ.get("VIEWERS", "30"))
    pace = float(os.environ.get("PACE_SECONDS", "10"))
    wait_time = constant_pacing(pace)
    network_timeout = TIMEOUT
    connection_timeout = TIMEOUT

    def on_start(self):
        # Start at a random moment of the first pace, so that the viewers do not all
        # click in the same second again and again.
        time.sleep(_rng.uniform(0, self.pace))

• constant_pacing(10) makes an arrival rate: each viewer starts a view every 10 seconds, however long the last one took, as real people do. With PACE_SECONDS=0, a viewer clicks again as soon as an answer arrives: a closed loop, which finds the most the API can do.
• The random start. The author’s first run had none: 30 viewers clicked in the same second, and the API was idle in between. No team has that workload.

scripts/bench.py runs the workload three times, leaves out the first 10 seconds of each pass, and prints the median of the passes with the range. It also samples the CPU of the API and of the database container, and the API’s memory, as in Load-test design.

Part 2: the baseline

Start the API in production mode, without a log line per request, which costs time at 20 requests a second. Use a second terminal for the benchmark.

macOS

LOG_LEVEL=WARNING fastapi run

Windows

$env:LOG_LEVEL = "WARNING"
fastapi run

Linux

LOG_LEVEL=WARNING fastapi run

First write down the machine. Then run the three workloads of docs/workload.md. Each takes three to four minutes:

python -m scripts.machine
python -m scripts.bench --label baseline
python -m scripts.bench --label baseline-burst --analysts 16 --export-pace 0
python -m scripts.bench --label baseline-capacity --viewers 32 --pace 0 --analysts 0 --seconds 40

Output of the first command (the author's computer)

Computer: Apple M4, 10 cores, 16.0 GB memory; Darwin 25.6.0 (arm64)
Python 3.12.13; Docker: Engine 29.6.1, 10 CPUs, 7.8 GB
Load average (1 minute): 5.36 (other programs that use the CPU make the numbers worse)

The end of the burst run, the author’s run (your numbers will differ):

Output (end)

Median of 3 passes (lowest-highest):
GET /v1/complaint-types                    | ok_per_s 2.96 (2.83-2.98) | p50_ms 1,440 (1,340-1,454) | p95_ms 2,037 (1,842-2,253) | p99_ms 2,146 (2,046-2,464) | errors 0 (0-0)
GET /v1/exports.csv                        | ok_per_s 5.66 (5.09-5.75) | p50_ms 2,262 (2,217-2,436) | p95_ms 5,499 (5,126-5,662) | p99_ms 5,984 (5,515-6,195) | errors 0 (0-0)
GET /v1/service-requests                   | ok_per_s 6.39 (6.16-6.5) | p50_ms 1,299 (1,237-1,394) | p95_ms 1,985 (1,842-2,238) | p99_ms 2,392 (1,957-2,579) | errors 0 (0-0)
GET /v1/service-requests/{id}              | ok_per_s 3.28 (3.2-3.33) | p50_ms 1,248 (1,226-1,360) | p95_ms 1,939 (1,781-2,281) | p99_ms 2,058 (1,990-2,476) | errors 0 (0-0)
GET /v1/stats/daily                        | ok_per_s 6.19 (5.93-6.25) | p50_ms 1,370 (1,351-1,478) | p95_ms 1,975 (1,910-2,173) | p99_ms 2,262 (2,066-2,501) | errors 0 (0-0)
dashboard (all GET requests of a visit)    | ok_per_s 18.55 (17.91-18.91) | p50_ms 1,340 (1,289-1,430) | p95_ms 1,970 (1,851-2,242) | p99_ms 2,244 (2,035-2,481) | errors 0 (0-0)
export: click to file                      | ok_per_s 5.66 (5.09-5.74) | p50_ms 2,262 (2,217-2,436) | p95_ms 5,499 (5,126-5,662) | p99_ms 5,984 (5,516-6,195) | errors 0 (0-0)

• Monday morning (4 analysts)
• Month-end burst (16 analysts)

The starter, medians of 3 passes on the author's computer. Under the month-end burst, the same dashboard requests became more than ten times slower at p95.

Text description of the chart

On Monday morning, dashboard requests took 29 ms at the median, 175 ms at p95 and 289 ms at p99. During the month-end burst, the same requests took 1,340 ms, 1,971 ms and 2,244 ms.

What this tells us. Three findings and a surprise.

• Monday morning is fine. The dashboard p95 was 175 ms, under the 300 ms target, with no errors. The slowest were the aggregates: /v1/stats/daily had a p95 of 255 ms, a page of the list 41 ms.
• The month-end burst breaks the target for everyone. With 16 exports in flight, a dashboard request took 1,340 ms at the median and 1,971 ms at p95. Even one request by its ID, 14 ms on Monday, took 1,249 ms. The API process used one full CPU core (a mean of 104%), the database only 20%. One Python process runs Python code on one core at a time, so the CSV work made every other request wait its turn.
• Memory. The API process grew to 974 MB during the burst (1,032 MB in one pass), against 284 MB on Monday. Each export holds all its rows, then the whole text, in memory. A container with a 512 MB limit would have been stopped.
• The surprise: nothing timed out. Every export in every pass came back, the slowest in about 6.3 seconds, far from the 30-second limit. The fear “the exports will time out” was wrong for this data and this workload. The real damage was to everyone else: the dashboards and the memory. Write that down as it is. More data would make exports slower, but this run does not say when they would pass 30 seconds.

The capacity run asked how much fits, with dashboards only and no pause. The starter answered 881 dashboard requests per second with a p95 of 50 ms. The database used about 315% CPU (three cores), the API 123%: half of the requests are aggregates, computed again every time.

Part 3: a cache for the aggregates

The problem: the same sum, again and again

A shop counts its till every time someone asks “how much did we sell in May?” It does the same sum again and again, although May is over. The API does the same. Every view of May runs GROUP BY over the month’s rows, and most viewers ask for the same few views.

The idea: store the answer, with everything that changes it in the key

A cache keeps a computed answer under a key, so the next request with the same key reads it instead of computing it. The four questions of Choose cacheable work all say yes. A GROUP BY and a median are costly, and a few views are most of the traffic. Every caller may see the answer, and only a load of new data changes it. So the key holds the parameters and the data version, as in Keys and invalidation:

A real key of the project. The data version (green) makes new data a new key; the scope (red) says who may receive the answer.

Text description of the diagram

Five parts, separated by colons: whose key and which answer, the data version, the scope, and a hash of the validated parameters.

Two parts need a reason:

• scope=public. The course’s rule is that every key holds the caller’s identity scope. Here every caller gets the same answer, so the scope is public, written out. If the API one day shows staff-only fields, those answers get another scope, and no public key can serve them.
• data=2. Invalidation by version, not by deleting keys. The loader adds the new rows and a row in data_loads in one transaction. The API reads the newest version, then builds the key. After a load, every request builds a new key, so no old answer is read again; old keys expire with their TTL (1 hour).

Here is a tiny example, with every value visible:

Step	Event	Newest version	Key ends with	Result
1	Priya opens Brooklyn, June	1	data=1:…:17649fd3…	miss: June has 0 requests (not loaded yet)
2	She opens it again	1	data=1:…:17649fd3…	hit: still 0
3	The loader adds June	1 → 2	–	–
4	She opens it again	2	data=2:…:17649fd3…	miss: the June numbers

Build it

Here are the parts that carry the idea; the finished project has every file.

1. Add the cache to compose.yaml, below db, and start it. It keeps nothing on disk, so a restart empties it, which a cache must survive:

   compose.yaml (part)

     cache:
       image: valkey/valkey:9.1.2-trixie
       command: ["valkey-server", "--save", "", "--appendonly", "no", "--maxmemory", "128mb",
                 "--maxmemory-policy", "allkeys-lru"]
       ports:
         - "127.0.0.1:${CACHE_PORT:-6379}:6379"

   docker compose up -d cache

2. Add redis==8.1.0 to requirements.txt and pyproject.toml, and install it: python -m pip install -r requirements.txt. It is the client for Valkey.

3. Add city_api/cache.py, with the Cache class of Failure and stampede behaviour. It has a 50 ms timeout, fails open after an error, and lets only one request compute a new key. The key:

   city_api/cache.py (part)

   def answer_key(name: str, data_version: int, params: dict, scope: str = "public") -> str:
       """The key of a cached answer. The same parameters in another order give the same key."""
       text = json.dumps(params, sort_keys=True, default=str, separators=(",", ":"))
       digest = hashlib.sha256(text.encode()).hexdigest()[:32]
       return f"{PREFIX}:v1:{name}:data={data_version}:scope={scope}:{digest}"

4. In city_api/routes.py, both aggregate routes call one helper. It reads the data version and returns the connection before it asks the cache. So a request that waits for another one’s result holds no connection:

   city_api/routes.py (part)

   with app.state.db.connection() as conn:
       version = queries.data_version(conn)
   key = answer_key(name, version, params)
   
   def compute_with_a_connection():
       with app.state.db.connection() as conn:
           return compute(conn)
   
   value, how = cache.get_or_compute(
       key, compute_with_a_connection, settings.cache_ttl_seconds, settings.cache_lock_seconds
   )
   response.headers["X-Cache"] = how
   response.headers["X-Data-Version"] = str(version)

   params are the validated values, so the same parameters in another order build one key, and an invalid request never reaches the cache.

5. Add CACHE_URL and the other cache settings to city_api/config.py and .env.example, open the cache in main.py, and add tests/test_cache.py. Then run the tests:

   python -m pytest

   Output (end)

   ........................................................................ [ 94%]
   ....                                                                     [100%]
   76 passed in 2.66s

The new tests check hits, misses, new data and a stopped cache (X-Cache: off). They also check the stampede guard: 8 requests at once for a new key compute it once.

Now watch the whole life of one key. Restart the API (fastapi dev is fine here), and ask for Brooklyn in June twice. June is not loaded yet:

curl -i "http://127.0.0.1:8000/v1/stats/daily?borough=brooklyn&created_from=2025-06-01&created_to=2025-07-01"

Output (headers and the first day; the second call is the same with X-Cache: hit)

HTTP/1.1 200 OK
date: Sat, 10 Oct 2026 15:05:01 GMT
server: uvicorn
content-length: 3671
content-type: application/json
x-cache: miss
x-data-version: 1
x-request-id: d81898d3ceb6

{"items":[{"date":"2025-06-01","opened":0,"closed_within_14_days":0,"share_closed_within_14_days":null,"median_days_to_close":null}, …

Then load June, the new data, and ask again:

python -m scripts.load --month 2025-06
curl -i "http://127.0.0.1:8000/v1/stats/daily?borough=brooklyn&created_from=2025-06-01&created_to=2025-07-01"

Output (the load, then headers and the first day)

Added 19,586 requests created in 2025-06.
Data version: 2 (0.3 s)

HTTP/1.1 200 OK
date: Sat, 10 Oct 2026 15:05:02 GMT
server: uvicorn
content-length: 3809
content-type: application/json
x-cache: miss
x-data-version: 2
x-request-id: 8bf56733c141

{"items":[{"date":"2025-06-01","opened":96,"closed_within_14_days":86,"share_closed_within_14_days":0.896,"median_days_to_close":1.54}, …

What this tells us. The second answer came from the cache. Then the load added June and data version 2 in one transaction. The next request built a new key, missed, and computed the June numbers at once. Nobody deleted a key, and no TTL had to end; the old answer expires unused within an hour.

Now measure the same three workloads again (--label cache, and so on). The export is still synchronous: one change at a time.

The author’s run (medians of 3 passes; your numbers will differ):

Workload	Measure	Starter	With the cache
Monday morning	dashboard p95	175 ms	118 ms
Monday morning	/v1/stats/daily p95	255 ms	160 ms
Month-end burst	dashboard p95	1,971 ms	2,213 ms
Capacity	dashboard requests per second	881	1,348
Capacity	database CPU (mean)	315%	33%

What this tells us. With dashboards only, the API answered about half as much again, and the database’s CPU fell from three cores to a third of one. On Monday, the p95 fell; a new filter is still a miss. But the month-end burst did not get better: the exports’ CPU work is in the API process, not in the database. A cache removes repeated work, nothing else. The export is the real problem.

Part 4: exports as background jobs

The problem: a big job inside a small request

At a photo shop, you can wait at the counter while the clerk prints one photo. For a whole album, the clerk gives you a ticket: “Come back in an hour.” If the clerk printed albums at the counter, everyone behind you would wait too.

GET /v1/exports.csv prints albums at the counter. One export of everything reads 90,329 rows and builds about 15 MB of text in memory. Until the file is sent, it keeps a thread, a connection and the API’s CPU busy. That CPU work slows every other request in the process, as in I/O versus CPU work. And nothing limits how many exports run at once.

The idea: a ticket now, the file later

The API saves the export as a job and answers 202 Accepted at once, with Location: /v1/exports/N. A separate worker process takes queued jobs one at a time and writes the files. The client asks for the job’s state until it says succeeded, then downloads the file. This is the queue of Job lifecycle, in PostgreSQL, with three protections:

• A bound (Backpressure). At most 20 exports wait. The 21st gets 503 queue_full with Retry-After, and nothing is saved.
• A rate limit per caller. More than 10 exports a minute from one X-Client-ID gets 429 rate_limited, counted in Valkey. One busy script cannot fill the queue for everyone.
• An idempotency key (Delivery and retries). A client that sends the same request again with the same Idempotency-Key gets the same job back, not a second export.

An export's path. The checks refuse work before it is saved; the worker makes the file; a second delivery writes the same file again.

Text description of the diagram

Two checks come before anything is saved: the caller’s rate limit, then the queue’s bound. Each refusal says when to try again. A worker takes a saved job with a lease and writes the file under a temporary name. An error sends the job back with a backoff; after three attempts it is a dead letter.

Why the same file twice is safe. A job can be delivered twice: a worker can stop after it wrote the file but before it marked the job done. So the work must be idempotent. The file’s name is the job’s ID, and the worker renames export-N.csv.tmp only at the end. A second delivery writes the same file again, and a client never sees half a file.

Build it

1. Add migrations/002_export_jobs.sql: the table export_jobs, with the state, the attempts, the lease, the caller, a unique idempotency key per caller, and the file’s facts. Apply it:

   python -m city_api.migrate

   Output

   Applied 002_export_jobs.sql

2. Add city_api/exports.py, every SQL statement of the queue. In Backpressure, a queue bounded at 100 held 107, because two requests could both count 99 and both insert. Here a transaction lock makes “count, then insert” one step:

   city_api/exports.py (part)

   conn.execute("SELECT pg_advisory_xact_lock(%s)", (SUBMIT_LOCK,))
   if idempotency_key is not None:
       old = conn.execute(
           f"SELECT {JOB_COLUMNS}, request_sha256 FROM export_jobs"
           " WHERE actor = %s AND idempotency_key = %s",
           (actor, idempotency_key),
       ).fetchone()
       if old is not None:
           if old.pop("request_sha256") != request_sha256:
               raise IdempotencyConflict(idempotency_key)
           return old, True
   queued = queued_count(conn)
   if queued >= queue_max:
       raise QueueFull(queued, retry_after_seconds(conn, queued))

   The lock makes submits wait for each other: cheap at a few exports a second, a bottleneck at thousands.

3. Add city_api/export_routes.py (POST /v1/exports, GET /v1/exports/{id}, GET /v1/exports/{id}/file, GET /v1/export-queue) and city_api/worker.py, and remove city_api/csv_export.py. The worker reads the data version and the rows in one REPEATABLE READ transaction, so a load cannot mix two versions in one file. It streams the rows, 2,000 at a time, so a big file never sits in memory whole.

4. Change the analyst in loadtest/locustfile.py: it sends POST /v1/exports, asks for the state every second, and downloads the file. “export: click to file” still measures the whole wait, so the versions compare fairly.

5. Add tests/test_exports.py, and run the tests:

   python -m pytest

   Output (end)

   ........................................................................ [ 80%]
   ..................                                                       [100%]
   90 passed in 3.11s

Removing GET /v1/exports.csv breaks its clients. Here the only one is Priya’s dashboard, which changes at the same time. With outside clients, keep the old endpoint until they have moved, as Resource and endpoint design says.

Try it with three terminals: the API (fastapi dev), the client, and later a worker. Ask for Brooklyn in May, then send the same request again with the same key:

curl -i -X POST http://127.0.0.1:8000/v1/exports -H "Content-Type: application/json" -H "Idempotency-Key: 7f9c2b1e-may-brooklyn" -H "X-Client-ID: priya" -d '{"borough": "brooklyn", "created_from": "2025-05-01T00:00:00", "created_to": "2025-06-01T00:00:00"}'

Output of the first request

HTTP/1.1 202 Accepted
date: Sat, 10 Oct 2026 15:05:11 GMT
server: uvicorn
content-length: 399
content-type: application/json
location: /v1/exports/1
x-request-id: 3864df68e922

{"export_id":1,"state":"queued","filters":{"status":null,"borough":"brooklyn","created_to":"2025-06-01T00:00:00","created_from":"2025-05-01T00:00:00","complaint_type":null},"attempts":0,"created_at":"2026-10-10T15:05:12+00:00","started_at":null,"finished_at":null,"queued_ahead":0,"estimated_wait_seconds":null,"data_version":null,"row_count":null,"file_bytes":null,"download_url":null,"error":null}

Output of the same request again (headers)

HTTP/1.1 202 Accepted
date: Sat, 10 Oct 2026 15:05:11 GMT
server: uvicorn
content-length: 399
content-type: application/json
location: /v1/exports/1
idempotent-replayed: true
x-request-id: 64915a19b0a3

The same key with another body ({"borough": "queens"}) gets 422 idempotency_key_reused. Nothing has made the file yet: no worker runs. Start one in the third terminal, python -m city_api.worker, and ask for the state:

Worker log, then GET /v1/exports/1

INFO city_api.worker: worker Usmans-Mac-mini.local-26621: waiting for exports
INFO city_api.worker: export 1: 5690 rows in 0.04 s

$ curl -s http://127.0.0.1:8000/v1/exports/1
{"export_id":1,"state":"succeeded","filters":{"status":null,"borough":"brooklyn","created_to":"2025-06-01T00:00:00","created_from":"2025-05-01T00:00:00","complaint_type":null},"attempts":1,"created_at":"2026-10-10T15:05:12+00:00","started_at":"2026-10-10T15:05:12+00:00","finished_at":"2026-10-10T15:05:12+00:00","queued_ahead":null,"estimated_wait_seconds":null,"data_version":2,"row_count":5690,"file_bytes":954422,"download_url":"/v1/exports/1/file","error":null}

Last, the bound. Stop the worker (Ctrl+C). Send 30 exports of everything at the same moment, each from its own caller, so that only the bound can stop them:

python -m scripts.send_exports --count 30

Output

30 export requests at once: status codes {202: 20, 503: 10}
Retry-After of the refused ones: 300 s
Queue now: {'queued': 20, 'running': 0, 'dead_letter': 0, 'succeeded_last_minute': 1, 'oldest_queued_seconds': 0.1}

Now start the worker again in the third terminal, wait half a minute, look at the queue, and send 30 more:

python -m city_api.worker

curl -s http://127.0.0.1:8000/v1/export-queue
python -m scripts.send_exports --count 30

Worker log (start), then the two commands

INFO city_api.worker: worker Usmans-Mac-mini.local-26640: waiting for exports
INFO city_api.worker: export 2: 109915 rows in 0.59 s
INFO city_api.worker: export 3: 109915 rows in 0.59 s

$ curl -s http://127.0.0.1:8000/v1/export-queue
{"queued":0,"running":0,"dead_letter":0,"succeeded_last_minute":21,"oldest_queued_seconds":0.0}
$ python -m scripts.send_exports --count 30
30 export requests at once: status codes {202: 21, 503: 9}
Retry-After of the refused ones: 58 s
Queue now: {'queued': 20, 'running': 1, 'dead_letter': 0, 'succeeded_last_minute': 21, 'oldest_queued_seconds': 0.1}

What this tells us. The repeat with the same key got the same job back, marked Idempotent-Replayed. The worker made the file in 0.04 seconds. With no worker running, the queue filled up to its bound, and the rest got 503 queue_full. The Retry-After follows the pace. With one export finished in the last minute, 20 queued exports need 20 minutes, so the API said its maximum, 300 seconds. With 21 a minute, it said 58 seconds. (With no export in the last minute, it cannot know, and says 60.)

Now measure the three workloads again (--label finished, and so on), with two workers running in two more terminals.

Part 5: the fair comparison

The three versions ran the same three workloads on the same computer: the same users, rates, seconds and data, three passes each. Here they are side by side, the author’s run (medians of 3 passes; your numbers will differ):

Workload	Measure	Starter	Cache	Cache and queue
Monday morning	dashboard p50 / p95 / p99	29 / 175 / 289 ms	23 / 118 / 182 ms	23 / 48 / 56 ms
Monday morning	export, click to file, p50	303 ms	306 ms	1,057 ms
Month-end burst	dashboard p50 / p95 / p99	1,340 / 1,971 / 2,244 ms	1,340 / 2,213 / 2,875 ms	30 / 53 / 68 ms
Month-end burst	dashboard errors	0	0	0
Month-end burst	export, click to file, p50 / p95	2,262 / 5,499 ms	2,350 / 5,129 ms	2,025 / 2,256 ms
Month-end burst	files per second	5.7	5.6	3.6
Month-end burst	exports refused (429), per pass	0	0	75,327
Month-end burst	API CPU (mean) / API memory (max)	104% / 974 MB	104% / 604 MB	70% / 99 MB
Capacity	dashboard requests per second, p95	881, 50 ms	1,348, 37 ms	1,409, 32 ms

• Monday morning
• Month-end burst

Dashboard p95, medians of 3 passes on the author's computer. Only the queue brought the month-end burst under the 300 ms target.

Text description of the chart

On Monday morning, the dashboard p95 was 175 ms for the starter, 118 ms with the cache and 48 ms with the cache and the queue. During the month-end burst, it was 1,971 ms, 2,213 ms and 53 ms.

The API process's largest memory during the burst, medians of 3 passes. The workers stream the rows, so the API holds no file.

Text description of the chart

The API process used up to 974 MB as the starter, 604 MB with the cache, and 99 MB with the queue.

What this tells us. The objective is met, with one trade and one surprise.

• The dashboards. During the burst, the dashboard p95 fell from about two seconds to 53 ms, under the target, with no errors. The API process does no CSV work now, and its memory stayed near 99 MB.
• The trade: a small export waits longer. On Monday, click to file went from 303 ms to 1,057 ms at the median. The client asks for the state once a second, so even a quick file waits for the next question. Under the burst, the files were steadier: a p95 of 2,256 ms, against 5,499 ms.
• The surprise: 75,327 refusals a pass. Fewer files came per second (3.6 against 5.7), because each analyst may start 10 exports a minute. The test’s analysts asked again at once after each 429: they ignored Retry-After. The refusals were cheap and saved nothing, but they kept the API’s CPU busy. A real client must wait as Retry-After says, and Teresa must decide if 10 a minute fits the monthly report.

Is the comparison fair? The workload, the data and the load test were the same, except for the analyst’s new export steps. The machine was not quiet: the load before the passes was between 3.3 and 12.1. So read only differences larger than the range of the passes. The burst dashboards of the starter and the cache overlap: “the cache made the burst worse” is not a finding.

Part 6: new data, end to end

Follow June through the whole system, with the outputs that you saw:

Step	What you saw	What proves it
The city publishes June	scripts.load --month 2025-06 added 19,586 requests	Data version: 2, rows and version in one transaction
A dashboard asks for June	X-Cache: miss, then the June numbers	the key now holds data=2; the old answer is never read
An analyst exports everything	the worker wrote 109,915 rows	90,329 + 19,586: January to June; each job records its data version (export 1: 2)

A new month reaches every answer at once, without a restart, a deleted key or a stale file.

The next bottleneck

The capacity run, with dashboards only, now answers 1,409 requests per second with a p95 of 32 ms. The database container used 33% CPU, the API process 142%. The database is no longer the limit: the one API process is. Locust ran on the same computer and took CPU too.

The next step is more API processes, for example fastapi run --workers 4. The cache, the rate limit and the queue are outside the process, so every process shares them. But measure it, as in Compare configurations: do not promise four times the rate. After that come limits not measured here: the submit lock, the disk for the files, and a cache miss on a big filter.

Do not extrapolate. 1,409 requests per second is about 66 times the measured Monday rate, on this computer, with this data. A server with other CPUs, a network and three times the data is another system. Measure it there.

What a real deployment must measure

• The real workload. Replace the assumptions of docs/workload.md with a traffic log: views, filters, and how often analysts export.
• The cache: the hit ratio and the miss time per endpoint, and how long after a load the first misses take.
• The queue: its length, the age of the oldest job, files per minute, dead letters, and the counts of 429 and 503. GET /v1/export-queue gives most of them; put them on a dashboard with alerts.
• Memory of the API and of each worker, against the container limits.
• The load generator on another computer, against a copy of the real data size.

Insights and limits

• Measure the fear. The team feared timeouts. The measurement found something else: slow dashboards and 1 GB of memory. Fix what you measure.
• One change, one bottleneck. The cache fixed the repeated database work. It did not touch the burst, because the burst’s problem was CPU in the API. The queue fixed that.
• A queue moves the wait; it does not remove the work. The files take the same work, in another process, behind limits. A small export waits a little longer.
• Limits of this study. One shared laptop, the load generator on it, no network, 90,329 rows, a workload made of assumptions. Windows and Linux are not tested.

Responsible use

• Open data is not free of people. The requests carry places, not names. Do not join an export with other data to find out who reported what.
• A request is not a problem, and closed is not repaired. Ranking boroughs by request counts ranks where people use 311. Show the caveats of DATASET.md next to the numbers.
• Label the data version. Show X-Data-Version and its load time on the dashboard, so nobody mistakes May’s numbers for June’s.
• Load tests only against your own services. Never point Locust at the city’s API or at a service that you do not run: to its owner, it looks like an attack.
• Exports are copies. A downloaded file leaves the API’s control. Keep files for a short time, and say how long in the README.

Make it a portfolio piece

This project shows what employers look for in a backend engineer. It has a stated workload, honest measurements, a cache that is never wrong, and slow work moved off the request path.

What to put in the repository:

city-requests-scaling/
  README.md          what it is, how to run, check and measure it, the results table
  DATASET.md         the source, the licence, the checksum, the changes, the limits
  LICENSE            MIT, for the code
  docs/workload.md   the workload, with every assumption
  docs/results.md    your numbers: machine, workload, medians of 3 passes, the next bottleneck
  city_api/          the API, the cache, the export queue and the worker
  migrations/        001 the requests and the data versions, 002 the export jobs
  loadtest/          the Locust workload
  scripts/           get_db, load, bench, percentiles, machine, send_exports
  tests/             90 tests

1. Make a new Git repository from your project folder, and push it to GitHub. .gitignore keeps out the database file, the exports and the results.
2. Fill in docs/results.md with your own three runs and your computer. Link it from the README.
3. Make sure that python -m pytest passes from a fresh clone, with docker compose up -d, and say so in the README.

Knowledge check

Check your understanding

With the cache, the database's CPU in the capacity run fell from about 315% to 33%. Yet during the month-end burst, the dashboards still waited about two seconds. Why?

Show the answer

The cache removes repeated database work. The burst’s problem was somewhere else. Sixteen exports built CSV text inside the one API process, and that CPU work made every other request wait. The database was not busy during the burst (about 20% CPU), so a cache in front of it could not help. Only moving the export out of the API process fixed the burst.

Check your understanding

After the queue, a small export on Monday took about 1,060 ms from click to file, against about 300 ms before. Is the queue a mistake?

Show the answer

No, it is a trade. The client asks for the state once a second, so even a file that takes a tenth of a second waits for the next question. In exchange, exports no longer slow the dashboards: a burst p95 of 53 ms instead of about two seconds. And the API’s memory stays near 100 MB. If the wait matters, ask more often at first, and measure again.

Check your understanding

In the burst run of the finished project, the API refused about 75,000 export requests in each pass, all with 429. A colleague says that the rate limit broke the exports. What do you answer?

Show the answer

The refusals came from the load test’s client. Each analyst may start 10 exports a minute; the test’s analysts asked again at once after each 429, without waiting for Retry-After. Each refusal took a few milliseconds and saved nothing, and the dashboards stayed at a p95 of 53 ms. Make the client wait as Retry-After says, ask Teresa if 10 a minute fits the monthly report, and measure again.

Recap

• You wrote the workload down first: 30 viewers with a view every 10 seconds, 4 analysts on a normal morning, 16 at month end.
• The baseline was fine on Monday. The burst made every dashboard request about ten times slower at p95 and grew the API to about 1 GB. Nothing timed out.
• A cache with the data version in its key raised the dashboards-only capacity by about half and was never wrong after new data. It did not fix the burst.
• Exports became jobs in a bounded queue, with idempotency keys, a rate limit and two workers. The burst’s dashboard p95 fell to 53 ms, and the API’s memory to about 100 MB. A small export now waits about a second.
• The next bottleneck is the one API process. Measure more processes; do not extrapolate.

Next, the final assignment: extend a ticket-processing service with a bounded queue, and benchmark it under a stated workload.

Next: Final assignment