The machine under your API

Twenty-four chapters on what happens between a browser and your database: the socket, the two proxies, the event loop, the connection pool, the query planner, and the cache.

It is not a FastAPI tutorial. It assumes you can already build an endpoint, and spends its time one layer down, where the surprising behaviour lives — why authentication runs after your request body is already in memory, why commit() empties the objects you are still holding, why the fix for a cache stampede makes the endpoint slower rather than faster. Every number, plan and error message was produced by running the thing, on PostgreSQL 16.13, Redis 7.0.15, FastAPI 0.141.1, SQLAlchemy 2.0.52 and Pydantic 2.13.4.

It runs about four hours end to end, so read it in sittings — one part at a time, each resting on the one before:

• One · from the socket to the function (1–4) — what actually arrives, and what turns it into a Python call. The event loop, the threadpool, and the two proxies in front of you.
• Two · the application (5–9) — what FastAPI is made of. Routing, the dependency tree, yield, and what Pydantic is really doing on the way out.
• Three · the database (10–15) — connections as processes, pooling (10–11), row versions and isolation, the query planner, and what an ORM has to be.
• Four · changing it (16–17) — Alembic and the lock queue that takes production down; then Neon, and what happens when storage and compute come apart.
• Five · state that outlives the request (18–22) — caching from first principles (18), Redis from zero (19), invalidation (20), revalidation under load (21), and work that outlives the request (22).
• Six · running it (23–24) — finding the slow thing, and the contract you signed without noticing.

If you came for one of those, start at its part; each opens by naming what it assumes from earlier. Part five is the one to read twice.

A request is bytes on a socket

Your handler takes a Request and returns a dict. Neither of those exists on the wire. What arrives is a stream of bytes on a socket, and something has to turn those bytes into a Python call before your function ever runs.

That something is a contract. It has a name, ASGI, and unlike most contracts you can print it out and read it. That is what this chapter does. By the end, the request stops being a magic object the framework hands you and becomes a plain dictionary you have watched get built — the same dictionary every later chapter reaches in and edits.

Three things happen to the bytes before Python is involved at all.

TCP gives you an ordered byte stream. Not messages, a stream. It promises the bytes arrive, in order, with no gaps, and it has no idea where one request ends and the next begins.

Before any of those bytes move, the two ends agree to talk. The client sends a SYN, a packet whose only job is to say "I would like to open a connection". The server answers SYN-ACK, the client replies ACK, and only then is there a connection to write into. Three packets, one round trip, before your application has seen anything. This step is worth knowing by name, because it fails on its own: a refused connection and a dropped SYN are two different failures that both look like "the request hung", and chapters 3 and 4 have to tell them apart.

TLS encrypts that stream, and on the way it settles which protocol you are about to speak. During the handshake the client offers a list of protocols and the server picks one. The mechanism is an extension called ALPN, for Application-Layer Protocol Negotiation. Here is a real handshake against Caddy, offering HTTP/2 or HTTP/1.1:

$ echo | openssl s_client -connect localhost:58044 -servername localhost -alpn h2,http/1.1

issuer=CN = Caddy Local Authority - ECC Intermediate
Verification: OK
New, TLSv1.3, Cipher is TLS_AES_128_GCM_SHA256
Server public key is 256 bit
ALPN protocol: h2

The last line is the point. h2 was chosen right there, inside the TLS handshake, before a single byte of HTTP existed. Not upgraded later, not negotiated in a header. This is why the HTTP version your browser thinks it is using can differ from the version your application actually receives, a mismatch chapter 3 comes back to.

Then framing cuts the stream into separate requests. HTTP/1.1 does it with text: a request line, headers, a blank line, and a body whose length comes from Content-Length or from a length prefix on each chunk. HTTP/2 does it with binary frames tagged by a stream number, so several requests share one connection without tangling. Different packaging, same job.

By the time anything reaches Python, that cutting is done. The server is holding a pile of bytes it is confident make up exactly one request. Now it has to call you, and for that it needs a contract.

The contract is three arguments

ASGI, the Asynchronous Server Gateway Interface, is that contract. Its specification states the whole thing in one sentence:

ASGI applications should be a single async callable:

coroutine application(scope, receive, send)

That is the entire interface. Three arguments. Here is a complete ASGI application that uses them — no framework, nothing imported but a pretty-printer. It answers everything with ok and prints what it was handed.

import pprint


async def app(scope, receive, send):
    pprint.pprint(scope, width=78)
    if scope["type"] == "lifespan":
        while True:
            message = await receive()
            print("  <- ", message)
            await send({"type": message["type"] + ".complete"})
            print("  -> ", message["type"] + ".complete")
    event = await receive()
    print("  <- ", event)
    await send({"type": "http.response.start", "status": 200,
                "headers": [(b"content-type", b"text/plain")]})
    await send({"type": "http.response.body", "body": b"ok\n"})

Run it under uvicorn and ask for one URL. This is a request reaching Python with nothing in the way:

{'asgi': {'spec_version': '2.3', 'version': '3.0'},
 'client': ('127.0.0.1', 47854),
 'headers': [(b'host', b'127.0.0.1:58041'),
             (b'user-agent', b'curl/8.5.0'),
             (b'accept', b'*/*'),
             (b'x-request-id', b'7c1f')],
 'http_version': '1.1',
 'method': 'GET',
 'path': '/orders/8821',
 'query_string': b'include=lines',
 'raw_path': b'/orders/8821',
 'root_path': '',
 'scheme': 'http',
 'server': ('127.0.0.1', 58041),
 'state': {},
 'type': 'http'}
  <-  {'type': 'http.request', 'body': b'', 'more_body': False}

That dictionary is scope, the first of the three arguments. It describes the connection: who opened it, how, and what they asked for. Three things in it repay a closer look, because each one comes back later as a surprise if you skate over it now.

headers is a list of pairs of bytes, not a dict. HTTP allows a name to appear twice, and when it does the order matters, so a dict would quietly lose information. Everything further up the stack that lets you read a header like a dict is a convenience view built over this list.

client is a ('host', port) tuple naming whoever opened the socket. It looks authoritative. Chapter 3 is largely about how often it is not.

And the body is not in scope at all. It arrives through receive, the second argument, as a separate event — the empty one at the bottom of the dump, empty because a GET has no body. That separation is the reason ASGI exists:

Unlike WSGI, there are two separate parts to an ASGI connection: a connection scope, which represents a protocol connection to a user and survives until the connection closes, and events, which are messages sent to the application as things happen on the connection.

So a request is not a single value handed to you. It is a connection with events happening on it, some of them arriving after your function has already started. receive pulls the next event when you ask for it; send, the third argument, pushes one back. A WebSocket is these same three arguments with a different type and more events. A streamed response is these same three arguments calling send for an hour.

Where a pool is allowed to live

There is one more kind of scope, and it is not a request. Before uvicorn accepts a single connection, it calls your app once with this:

INFO:     Waiting for application startup.
{'asgi': {'spec_version': '2.0', 'version': '3.0'}, 'state': {}, 'type': 'lifespan'}
  <-  {'type': 'lifespan.startup'}
  ->  lifespan.startup.complete
INFO:     Application startup complete.
INFO:     Uvicorn running on http://127.0.0.1:58041 (Press CTRL+C to quit)

INFO:     Shutting down
INFO:     Waiting for application shutdown.
  <-  {'type': 'lifespan.shutdown'}
  ->  lifespan.shutdown.complete
INFO:     Application shutdown complete.

The server sends lifespan.startup and then waits. The spec is blunt about the waiting: "a server must wait for this message before it starts processing connections." One call, on an event loop that is already running, before any traffic exists, and a matching call on the way down.

What is it for? The specification says so in its opening paragraph, and the example it reaches for is oddly specific:

An example of this would be creating a connection pool and subsequently closing the connection pool to release the connections.

It names a connection pool because a pool has nowhere else sensible to live. Open it when the module is first imported and you have built it before there is an event loop for its sockets to attach to. Open it inside a request and you build a fresh pool and throw it away every request, which is the one thing a pool exists not to do. The spec says why the loop is what matters:

Lifespans should be executed once per event loop that will be processing requests. In a multi-process environment there will be lifespan events in each process ... The important part is that lifespans and requests are run in the same event loop to ensure that objects like database connection pools are not moved or shared across event loops.

Lifespan is the one moment that is neither too early nor too late: the loop is running, and no request has arrived yet. Chapter 11 works out how big the pool should be. This is where it is allowed to open.

Notice what has not come up once: FastAPI. That was deliberate. scope, receive and send are the object the rest of this guide keeps modifying. Middleware wraps the callable. A reverse proxy edits scope["headers"] before uvicorn sees them, and uvicorn rewrites scope["client"] from what it finds there. Come at FastAPI first and each of those is a feature to memorise. Come at it from here and each one is an edit to a dict you have already watched print.

Your server is one thread, and def can block it

In Node you write async because there is nothing else to write; blocking the loop takes deliberate effort. Python hands you two spellings for the same handler, def and async def. They look interchangeable. One of them can stop your entire server.

Start from the fact everything else rests on: the event loop is one thread, in one process. Not a thread pool with a loop on top, one thread, running one coroutine at a time, switching between them only at an await. If that thread is busy doing arithmetic, nothing else in the process runs. Not another request, not a health check, not the code that would have written a log line.

So: three handlers, each nominally one second of work.

@app.get("/sync-sleep")
def sync_sleep():
    time.sleep(1.0)             # a `def` handler

@app.get("/async-sleep")
async def async_sleep():
    await asyncio.sleep(1.0)    # awaits, so the loop is free

@app.get("/async-blocking")
async def async_blocking():
    time.sleep(1.0)             # `async def`, but no await. the bug.

@app.get("/ping")
async def ping():
    return {"t": ...}           # a cheap probe. if this is slow, the loop is blocked

Five requests in flight against each, and meanwhile /ping three times. Guess which of the three hurts the probe, and by how much, before you read on.

=== D: /ping while 5x /async-sleep in flight ===
  while 5x /async-sleep in flight, /ping latencies: [0.004, 0.002, 0.001]
=== E: /ping while 5x /async-blocking in flight ===
  while 5x /async-blocking in flight, /ping latencies: [4.869, 0.003, 0.002]
=== F: /ping while 5x /sync-sleep in flight ===
  while 5x /sync-sleep in flight, /ping latencies: [0.004, 0.002, 0.002]

Four point eight seconds on a health check that does nothing. Five one-second blocking calls, serialised on the one loop thread, and the cheap probe queued behind all of them. Case F is the identical blocking work, spelled def instead, and the probe answered in four milliseconds.

That is the whole lesson, and it points the opposite way from JavaScript intuition. async def is not a performance mode. It is a promise that you will not block, and Python will not check it for you. If you cannot keep the promise, def is the safer word — a blocking call marked async def is strictly worse than the same call left plain.

Where def actually goes

Case F was fast because a def handler does not run on the loop thread at all. Starlette makes that decision once, at route registration, in a single line:

f = func if is_async_callable(func) else functools.partial(run_in_threadpool, func)

run_in_threadpool calls anyio.to_thread.run_sync, and that draws a token from a capacity limiter — a counter of how many worker threads may be busy at once. AnyIO's default:

except LookupError:
    limiter = CapacityLimiter(40)

Forty. Not derived from your CPU count, your traffic or your database. A constant in a library most people never open. You can watch it from outside — 80 concurrent one-second def requests, against 80 async def ones:

=== B: 80x /sync-sleep, above the ceiling ===
path=/sync-sleep  n=80  wall=2.15s
  handler-start buckets (sec after first): {0.0: 40, 1.0: 40}

=== C: 80x /async-sleep ===
path=/async-sleep  n=80  wall=1.18s
  handler-start buckets (sec after first): {0.0: 80}

Forty start immediately; the other forty start exactly one second later, when the first forty hand their tokens back. The limiter is not an abstraction — it is a visible step in the data. And nothing told the client: all 80 returned 200, some just took twice as long.

--workers is a process boundary, not a throughput knob

--workers 4 is usually filed under performance. Look at what it creates:

  PID  PPID   RSS COMMAND
 2450  2447 15492 python3 -c from multiprocessing.resource_tracker import main;main(7)
 2451  2447 45876 python3 -c from multiprocessing.spawn import spawn_main; ... --multiprocessing-fork
 2452  2447 46576 python3 -c from multiprocessing.spawn import spawn_main; ... --multiprocessing-fork
 2453  2447 45908 python3 -c from multiprocessing.spawn import spawn_main; ... --multiprocessing-fork
 2454  2447 45880 python3 -c from multiprocessing.spawn import spawn_main; ... --multiprocessing-fork

Four operating-system processes, spawned rather than forked, about 46 MB of resident memory each — copied, not shared. The parent binds the listening socket once and passes it down, as uvicorn's own docstring says: "Sockets are bound once by the parent process, and then passed to the child processes." All four accept() on it, and the kernel picks the winner, which is why the distribution is lumpy rather than round-robin.

Now set an in-process cache key on one request and read it back on eight concurrent ones:

{"pid":2556,"set":true}
{"pid":2555,"has_key":false}
{"pid":2555,"has_key":false}
{"pid":2555,"has_key":false}
{"pid":2555,"has_key":false}
{"pid":2558,"has_key":false}
{"pid":2558,"has_key":false}
{"pid":2558,"has_key":false}
{"pid":2558,"has_key":false}

Eight cache misses that are not cache misses. The key exists; it exists in a process nobody asked. Anything you keep in memory — a rate-limit counter, an lru_cache, a WebSocket registry, a "have we already sent this email" flag — becomes per-worker the moment you pass --workers. Those falses are the argument for a cache that lives in a separate box, which is chapter 19.

Two numbers to carry forward. Each worker reported its own limiter:

{"pid":2454,"ppid":2447,"limiter_total_tokens":40}
{"pid":2453,"ppid":2447,"limiter_total_tokens":40}
{"pid":2452,"ppid":2447,"limiter_total_tokens":40}

Four processes, forty threads each, so up to 160 threads running handler code at once, every one of them able to want a database connection. That product is the input to every pooling decision in chapter 11, and it is almost never computed, because --workers lives in a deploy config and pool_size lives next to the database.

Two proxies, and both can lie about the client

request.url.scheme says https. Your handler builds a redirect from it and everything works. Ask where that string came from and the answer is uncomfortable: not from the socket, because the socket your process is reading was plaintext.

A request from a browser to a FastAPI app behind Cloudflare and Caddy is not one connection. It is three. The browser negotiates TLS with a Cloudflare datacentre and the encryption ends there — that is what termination means, literally. Cloudflare opens a second connection to your Caddy, with its own handshake and its own certificate; Caddy terminates that one and opens a third, usually plaintext over loopback, to uvicorn. Cloudflare's own docs frame their TLS settings exactly this way: the encryption mode "controls how Cloudflare manages two connections: one between your visitors and Cloudflare, and the other between Cloudflare and your origin server."

Drawn out, with what each hop can actually see:

   browser            Cloudflare edge          your Caddy            uvicorn
      │                     │                      │                    │
      ├──── TLS #1 ────────▶│                      │                    │
      │   cert: Cloudflare  │                      │                    │
      │   HTTP/2 or /3      │                      │                    │
      │                     ├──── TLS #2 ─────────▶│                    │
      │                     │  cert: yours         │                    │
      │                     │  (Full (strict)      │                    │
      │                     │   validates it)      │                    │
      │                     │                      ├─── plaintext ─────▶│
      │                     │                      │    HTTP/1.1        │
      │                     │                      │    over loopback   │
      │                     │                      │                    │
  knows: everything    knows: real client IP   knows: Cloudflare's   knows: Caddy's
                            real TLS version    IP, and whatever      IP, and whatever
                            real HTTP version   CF put in headers     Caddy put in headers

Three handshakes, three certificates' worth of decisions, and one fact under all of it: every hop after the first knows only what the previous hop chose to write down. The client's IP address, the TLS version it negotiated, the HTTP version it spoke, all of it stopped being observable at the edge and continued as hearsay in a header. That is the trust question this chapter is named for, and it comes out of termination, not a design flaw in anybody's proxy.

Here is one hop of it, measured. curl speaks HTTP/2 over TLS to Caddy; Caddy proxies to an app that dumps its own scope:

[curl] negotiated http_version=2
{
    "scope_client": ["127.0.0.1", 0],
    "scope_scheme": "https",
    "http_version": "1.1",
    "headers": {
        "host": "localhost:58044",
        "user-agent": "curl/8.5.0",
        "accept": "*/*",
        "via": "2.0 Caddy",
        "x-forwarded-for": "127.0.0.1",
        "x-forwarded-host": "localhost:58044",
        "x-forwarded-proto": "https",
        "accept-encoding": "gzip"
    }
}

The client spoke HTTP/2. The application received HTTP/1.1. Your FastAPI app has probably never seen an HTTP/2 request in its life; the upgrade happened at a layer that does not forward it, which is also why multiplexing and stream priorities are somebody else's problem.

And scope_client is 127.0.0.1 — correct, and useless. Termination destroyed everything about the original client, because from uvicorn's point of view the client is Caddy. The proxy is now the only party that knows who called.

X-Forwarded-* is the repair

So the proxy writes down what it knows. Caddy's documentation is precise about what it adds and — the part people miss — about what it refuses to pass through:

By default, Caddy passes through incoming headers—including Host—to the backend without modifications, with three exceptions: It sets or augments the X-Forwarded-For header field. It sets the X-Forwarded-Proto header field. It sets the X-Forwarded-Host header field. For these X-Forwarded-* headers, by default, the proxy will ignore their values from incoming requests, to prevent spoofing.

All three are in the dump above, plus Via: 2.0 Caddy recording the protocol Caddy received and an Accept-Encoding: gzip it adds so it can compress on the way back out. Host is untouched.

That is the repair, and it works. Now the awkward part: those are headers, and anyone can send a header.

Trust fails in both directions

This is not a checkbox with a safe setting. It has two failure modes, and they are opposites.

Believe too little and every client on earth is 127.0.0.1. The rate limiter throttles the proxy, the audit log records the proxy, and your https redirects come out as http, which, if the proxy redirects HTTP to HTTPS, is an infinite loop.

Believe too much and every client can name themselves whatever they like. Run uvicorn with no proxy in front of it at all, send it a forged header, and watch:

-- a. uvicorn defaults: proxy_headers=True, forwarded_allow_ips='127.0.0.1' --
   curl -H 'X-Forwarded-For: 203.0.113.9' -H 'X-Forwarded-Proto: https'
   client = ['203.0.113.9', 0]  scheme = https
   curl -H 'X-Forwarded-For: 203.0.113.9, 198.51.100.4'   (a lie, then a real proxy)
   client = ['198.51.100.4', 0]  scheme = http
-- b. --no-proxy-headers --
   client = ['127.0.0.1', 48166]  scheme = http
-- c. --forwarded-allow-ips '*' --
   client = ['203.0.113.9', 0]  scheme = http

Case (a) is the default, out of the box, today. Your app is already rewriting scope["client"] and scope["scheme"] from headers, whether or not you configured anything. This is not a mistake you might make; it is a default you need to understand.

It is safe only because of the second half of that default. forwarded_allow_ips is "127.0.0.1", so the rewrite happens only when the socket peer is loopback — and if your proxy really is on loopback, nobody else can reach that socket to lie into it.

The middle line of (a) shows the algorithm. Given lie, real-proxy, uvicorn walks the list right to left and takes the first host not in its trust list. Each proxy appends its own observation on the right, so the right-hand entries were written by machines you control and the leftmost is whatever the client typed. Case (c) is --forwarded-allow-ips '*', pasted into a great many Dockerfiles: it takes the leftmost value, and the leftmost value belongs to the attacker.

The rule generalises past this one setting. A header is evidence about the request; the socket peer is evidence about the sender. Only the second is authenticated by the network, so a header may be promoted to evidence only after the peer has been checked.

It applies unchanged to CF-Connecting-IP. Cloudflare recommends reading it instead of X-Forwarded-For, and that is sound for requests that came through Cloudflare, but nothing in the header proves one did. If your origin IP is reachable directly, a rate limiter keyed on that header counts one attacker as a million.

Why the proxy answers instead of waiting

One more thing the layer does for you, which looks like rudeness. Point Caddy at an upstream port with nothing listening:

HTTP/1.1 502 Bad Gateway
Server: Caddy
Content-Length: 0

[curl] total=0.001128s

{"level":"error","logger":"http.log.error","msg":"dial tcp 127.0.0.1:58099: connect: connection refused",
 "duration":0.000421395,"status":502,"err_id":"pmcax6w51"}

Four hundred microseconds. The kernel returned ECONNREFUSED on the SYN, so there was nothing to wait for — and Caddy did not invent something to wait for either. Its defaults are lb_try_duration zero ("By default, retries are disabled") and passive health checks off. A proxy that waits holds a connection slot, delays the caller's own error handling, and turns a fast unambiguous failure into a slow ambiguous one.

Which is the right instinct, and exactly why the other case is dangerous. A dead upstream fails in microseconds. A hung upstream does not fail at all, because Caddy's response_header_timeout default is, in its own docs' words, "No timeout." Chapter 4 is that asymmetry.

Everything above can fail slowly

A dead upstream is the easy case: it fails in microseconds and everything above it knows at once. The expensive failure returns nothing, slowly, holding a connection, a thread and a database session the whole time.

You already set a timeout for that. Here is why it probably does not do what you think.

Four timeouts, and the one that does not exist

httpx's default is a single number, and it expands into four:

DEFAULT_TIMEOUT_CONFIG: Timeout(timeout=5.0)
   -> {'connect': 5.0, 'read': 5.0, 'write': 5.0, 'pool': 5.0}
DEFAULT_LIMITS       : Limits(max_connections=100, max_keepalive_connections=20,
                              keepalive_expiry=5.0)

Four clocks, none of which is a deadline. Run all four against a real server:

1. CONNECT timeout -- peer never answers SYN
  connect=1.5, read=30 -> blackhole      ConnectTimeout         in  1.51s

2. READ timeout -- connected, then silence
  connect=30, read=1.5 -> /stall (10s)   ReadTimeout            in  1.51s

3. READ timeout does NOT bound total time
  read=1.5 -> /drip (0.4s per byte, 12s) OK   in 12.07s  (30 bytes)
     ^ every gap is under 1.5s, so the read timeout never fires.

4. A real deadline, imposed outside httpx
  asyncio.timeout(3.0) -> /drip          TimeoutError           in  3.00s

5. POOL timeout -- the queue inside your own client
  request 0: Response
  request 1: PoolTimeout
  request 2: PoolTimeout
  wall 10.01s -- two waited on a pool of 1 and gave up

Case 3 is the one to keep. A read timeout measures the gap between bytes, and it resets on every byte. A server dribbling out one byte every 400 milliseconds never trips a 1.5-second read timeout, however long it goes on: that request ran twelve seconds and returned 200. Every slow-loris-shaped incident lives in this gap.

Case 4 builds the missing row by hand — asyncio.timeout(3.0) around the call, which fired at 3.00 seconds exactly. Case 5 is worth a glance for a different reason: with max_connections=1, two of three requests never touched the network, they queued inside the client and gave up. That is the same shape as a database connection pool, and pool_timeout in chapter 11 is this row of the table.

Retries shape load; they do not handle errors

The reflex when something fails is to retry it, and that reflex turns a blip into an outage. A retry is not error handling. It is a decision to send more traffic to a service that has just said it cannot cope.

Take 500 clients that all fail at the same instant, each retrying six times, and count arrivals per 100 milliseconds. The worst bucket is what the recovering service has to survive:

policy                       peak/100ms      at  %in peak  buckets used   horizon
----------------------------------------------------------------------------------
fixed 1s                            500    1.0s     16.7%             6      6.0s
exponential, no jitter              500    1.0s     16.7%             6     61.0s
exponential + equal jitter          112    0.8s      3.7%           420     57.7s
exponential + full jitter            82    0.9s      2.7%           435     55.0s
decorrelated jitter                  40    2.1s      1.3%           656    121.3s

Exponential backoff without jitter reduces the peak by exactly nothing. All 500 still land in the same 100 milliseconds, the same as a fixed one-second delay. Backoff spreads one client's attempts across time; it does not spread clients across each other, because they all compute the same deterministic delay from the same failure instant.

Randomness is the mechanism. Full jitter — delay = random(0, min(cap, base * 2**attempt)) — cut the peak from 500 to 82. Backoff only decides how long the storm lasts.

Three queues, and none of them will tell you

So the load arrives. Where does it wait? Fire 200 concurrent requests at a server whose handlers take a second each, and measure two things separately: how long the connection took to establish, and how long until the first byte came back.

  n=200  path=/sync-sleep
  TCP connect     : min    13.3ms  p50    28.3ms  p99    34.9ms  max    35.1ms
  first byte      : min  1054.9ms  p50  3083.5ms  p99  5083.4ms  max  5083.5ms
  mean first byte :  3078.6ms   <- the number a dashboard shows you
  statuses        : {'HTTP/1.1 200 OK': 200}

Every connection was accepted in under 35 milliseconds. Every response took up to five. The arithmetic is exact: 200 requests over chapter 2's 40 worker threads is five waves of one second.

The queue is real, it is five deep, and all 200 requests returned 200. Nothing called it a failure. connect() succeeding is an answer from the kernel, not from your application, so neither the client nor an uptime check can tell "fast" from "queued".

Three of these queues are stacked on top of each other:

Only the last one is introspectable, and it is worth exporting:

CapacityLimiterStatistics(borrowed_tokens=40, total_tokens=40, borrowers=(...), tasks_waiting=10)

tasks_waiting=10 is the best queue-depth signal a default FastAPI process has, and almost nobody exports it.

Refusing is a feature

Which gives the definition. Backpressure is the absence of a queue. A system with backpressure refuses work it cannot do. A system without one accepts the work and hides it in a buffer, turning a capacity problem into a latency problem. That is worse, because latency propagates to your callers and triggers their retries, and you have just seen what those do to a peak.

The deliberate alternative is load shedding: fail some requests cheaply so the rest succeed. Run the same 200 with --limit-concurrency 50:

  TCP connect     : min    14.5ms  p50    24.6ms  p99    32.0ms  max    32.1ms
  first byte      : min    35.8ms  p50    55.4ms  p99    56.9ms  max    57.0ms
  statuses        : {'HTTP/1.1 503 Service Unavailable': 200}

Fast, honest, and all 200 got a 503, including the 50 that should have been served. The reason is in uvicorn's own source: the check is len(self.connections) >= limit or len(self.tasks) >= limit. It counts connections, and all 200 connections existed before any request was handled.

That is not an argument against shedding but for knowing what yours counts. The right idea applied to the wrong number refused everything — and still answered in 57 milliseconds instead of five seconds, which is the trade this whole chapter is about. A fast 503 is information. A slow 200 is not.

FastAPI is Starlette with a type system

Ask what FastAPI is and the honest answer is short. Here is the class hierarchy, and the entire contents of fastapi/requests.py:

FastAPI MRO   : ['fastapi.applications.FastAPI', 'starlette.applications.Starlette', 'builtins.object']
fastapi.Request  is starlette.requests.Request  -> True
fastapi.Response is starlette.responses.Response -> True

fastapi/requests.py, in full:
from starlette.requests import HTTPConnection as HTTPConnection  # noqa: F401
from starlette.requests import Request as Request  # noqa: F401

class FastAPI(Starlette). Request, Response, TestClient, every response class, StaticFiles, WebSocket — re-exports, not implementations. Routing, middleware, exception handling and the ASGI callable itself are Starlette's.

What FastAPI adds is one idea applied everywhere: read the type annotations and do something with them. A Dependant tree built from signatures (chapter 7), a parameter layer turning annotations into Pydantic fields, response_model, and OpenAPI generation. The override surface is small — FastAPI replaces three of Starlette's methods, APIRouter four of Router's, APIRoute three of Route's — and everything else it does, it does by composition.

Knowing the split is not trivia. It tells you where to look when something misbehaves, and it settles the question in this chapter.

The stack is a chain of nested apps

Chapter 1's ASGI callable takes (scope, receive, send). So does a middleware. That is the whole mechanism: a middleware is an ASGI app holding another ASGI app, and building the stack is a loop.

middleware = [Middleware(ServerErrorMiddleware, handler=error_handler, debug=debug)]
middleware += self.user_middleware
middleware.append(Middleware(ExceptionMiddleware, handlers=exception_handlers, debug=debug))

app = self.router
for cls, args, kwargs in reversed(middleware):
    app = cls(app, *args, **kwargs)
return app

No registry, no dispatch table, no hooks. app = cls(app, ...) in a loop. Register three middlewares on a real app and walk the resulting object graph by following .app:

ServerErrorMiddleware
  RaisesInMiddleware
    THIRD-ADDED
      SECOND-ADDED
        FIRST-ADDED
          ExceptionMiddleware
            AsyncExitStackMiddleware
              APIRouter
                method

Two things in that picture are worth pinning down.

The last middleware added is the outermost. add_middleware does self.user_middleware.insert(0, ...), so registration order and nesting order are reversed. (Pass FastAPI(middleware=[...]) instead and the list is outermost-first — the two spellings are opposite, which is the most common ordering mistake there is.) Requests travel down that tree outside-in; responses come back inside-out.

ExceptionMiddleware sits below every middleware you wrote. Your exception handlers are registered on it. So handlers are not global; they are a layer, and everything outside that layer is beyond their reach.

That is the whole explanation for a bug that otherwise looks like a framework defect. Register a handler for HTTPException, then raise the same exception from two places:

  raised in the ENDPOINT   -> 418 {"handled_by":"my exception handler"}
  raised in MIDDLEWARE     -> 500 'Internal Server Error'

Same exception class, same app, same handler. The endpoint's raise travels up through ExceptionMiddleware, which catches it. The middleware's raise happens above that layer, so the only thing left to catch it is ServerErrorMiddleware at the very top, whose job is to turn anything unhandled into a 500. Once you have seen the tree, that stops being surprising and starts being obvious.

Middleware and dependencies are not the same tool

They feel like the same tool at two granularities: both "wrap the request", and a Depends in a router's dependencies= list looks exactly like middleware scoped to a prefix. The stack diagram says otherwise, because it shows where each one sits.

A dependency runs inside the router, after matching, after the body has been read, inside ExceptionMiddleware. Middleware runs outside all of that. So the powers do not overlap:

Which gives a rule you can apply without re-deriving it. If the thing belongs in the API contract, or must fail with a real status code, or needs a per-request resource, it is a dependency. If it is infrastructure that must run even for requests that hit nothing — a request-id header, access logging, a body-size limit — it is middleware.

Chapter 6 is what happens at the bottom of that stack, where APIRouter decides which of your functions this request was for.

Routing matches in order, and auth runs after the body

Two routes, declared in this order:

@app.get("/users/{user_id}")
async def by_id(user_id: str): ...

@app.get("/users/me")
async def me(): ...

GET /users/me returns 200. It also runs the wrong function:

=== A: /users/{user_id} declared FIRST ===
  GET /users/me -> {'matched': '/users/{user_id}', 'user_id': 'me'}

No error, no warning, a perfectly plausible response. Somewhere a user account named me was looked up and not found. The reason is that the router does not pick the route that fits best — it does not rank candidates at all:

for route in self.routes:
    match, child_scope = route.matches(scope)
    if match == Match.FULL:
        scope.update(child_scope)
        await route.handle(scope, receive, send)
        return
    elif match == Match.PARTIAL and partial is None:
        partial = route
        partial_scope = child_scope

A loop, in declaration order, first full match wins. There is no specificity score and no longest-prefix rule. (Match.PARTIAL exists only for "the path matched but the method did not", which is where your 405 comes from.)

And "match" means a regular expression, because that is what a path declaration compiles to:

  /users/me                -> ^/users/me$
  /users/{user_id}         -> ^/users/(?P<user_id>[^/]+)$            ['user_id']
  /users/{user_id:int}     -> ^/users/(?P<user_id>[0-9]+)$           ['user_id']
  /files/{path:path}       -> ^/files/(?P<path>.*)$                  ['path']
  does /users/{user_id} match '/users/me'? -> True

[^/]+ matches me. Of course it does.

Now the part that catches people who think they have already fixed this by typing the parameter. Annotate it as int and try again:

  GET /items/7      -> 200 {'matched': '/items/{item_id}', 'item_id': 7}
  GET /items/latest -> 422 {'detail': [{'type': 'int_parsing', 'loc': ['path', 'item_id'],
    'msg': 'Input should be a valid integer, unable to parse string as an integer',
    'input': 'latest'}]}

The Python annotation is not consulted during matching. item_id: int still compiles to [^/]+; the integer conversion happens later, during validation, by which time the router has already committed. That 422 reads like a validation problem and is a routing problem — and the second route, which would have answered perfectly, was never reached.

Two fixes, and they are not equivalent. Declare /items/latest first, which works and depends on a line's position in a file. Or use Starlette's converter syntax, /items/{item_id:int}, which compiles to [0-9]+ and makes order irrelevant — at the cost that the converter is dropped from the generated OpenAPI path, so the schema shows /items/{item_id} either way.

The body is read before authentication runs

Here is an ordering question with a less comfortable answer. Your endpoint has an auth dependency and takes a JSON body. A client sends five megabytes with a wrong token. What happens first?

  POST /upload, 5 MB body, WRONG token -> 401 {'detail': 'bad token'}
    request.body() awaited -> 5242891 bytes buffered
    request.body() awaited -> 5242891 bytes buffered
    auth dependency ran
    auth REJECTED

The 401 is correct. It arrives after your process has already buffered 5,242,891 bytes on behalf of somebody who could not prove who they were.

That is not a bug, and FastAPI's own source shows why. In get_request_handler, the body is read at line 430 and solve_dependencies — which is where every dependency, including auth, actually runs — is not called until line 481. It has to be that way round: a dependency is allowed to declare body parameters, so the body must exist before the dependency tree can be solved. Authentication is a dependency like any other, and dependencies come after the body.

The consequence is worth stating plainly, because it decides where a fix belongs. No dependency can protect you from a large upload, because every dependency runs too late. A body size limit has to sit at a layer above the router:

• At the edge or the reverse proxy, which is where it costs your origin nothing at all.
• Or in-process, above the router: Starlette 1.6.0 added max_body_size on the app and on route classes, described in its own docstring as "Non-negative maximum total size in bytes of an HTTP request body. The default, None, does not limit request body size." It is inserted into the middleware stack from chapter 5 as RequestBodyLimitMiddleware, outside everything, which is exactly where it needs to be.

One consolation from the same code path: a route with no declared body parameter never reads the body at all, because the read is guarded by if body_field:. The exposure is specific to endpoints that accept one — which, admittedly, is every endpoint that matters.

A dependency tree, solved once per request

An endpoint takes two repositories. Both repositories take a database session. How many sessions does one request open?

def db():                       return f"conn-{n}"
def repo_a(conn=Depends(db)):   ...
def repo_b(conn=Depends(db)):   ...

@app.get("/order")
async def order(a=Depends(repo_a), b=Depends(repo_b)): ...

The honest answer is that you cannot tell from the code, and the guess most people make is wrong in a way that costs them a transaction. So look at what FastAPI built when it imported that module:

order      use_cache=True  cache key = (order, (), '')
  repo_a     use_cache=True  cache key = (repo_a, (), '')
    db         use_cache=True  cache key = (db, (), '')
  repo_b     use_cache=True  cache key = (repo_b, (), '')
    db         use_cache=True  cache key = (db, (), '')

That is a Dependant: a node holding a callable, its own declared parameters, and a list of child Dependants. FastAPI builds it once, at import, by reading signatures — the same signatures it reads to build your OpenAPI schema. db appears twice in the tree, because the tree is a description of the declarations, not of the run.

At request time it walks that tree, depth first, in declaration order:

  body : {'a': 'repo_a(conn-1)', 'b': 'repo_b(conn-1)'}
  trace: ['db() call #1', 'repo_a()', 'repo_b()', 'endpoint']

One db() call, and both repositories got conn-1. The walk keeps a dictionary for the duration of the request; the second time it reaches db, the answer is already there. That is why a session declared five levels apart in five different dependencies is still one session, and therefore one transaction.

Note also what the trace does not show: any overlap. solve_dependencies iterates the children and awaits each in turn, with no task group anywhere. Sibling dependencies do not run concurrently — two that each take 200 milliseconds of network measured 405 ms together, not 200.

The key is the callable, by identity

The cache is keyed on a three-part tuple, and the first part is the function object itself:

return (
    dependant.call,
    scopes_for_cache,
    _get_computed_scope(dependant=dependant) or "",
)

(the callable, its sorted OAuth scopes, "function" | "request" | ""). The scopes slot is empty unless the subtree actually uses security scopes; the third is the teardown scope from chapter 8.

Identity, not equivalence. Two Depends(db) written in different files share one entry because they name the same object. Two functions that do the same thing do not, and neither does the same function wrapped twice. If you define def get_session() and def get_db_session() and both open a session, one request opens two sessions and commits two transactions — and nothing anywhere will tell you.

use_cache=False refuses to read, not to write

There is an opt-out, Depends(db, use_cache=False), and its behaviour is one-directional. Reading FastAPI's solve_dependencies, the cache is consulted only when use_cache is true — but the result is written back unconditionally. Which means the outcome depends on what you declared first:

  /cached-first    body: {'a': 'conn-1', 'b': 'conn-2'}   trace: ['db() call #1', 'db() call #2']
  /uncached-first  body: {'a': 'conn-1', 'b': 'conn-1'}   trace: ['db() call #1']

Read the second line carefully. The consumer that asked for a cached value received conn-1, and conn-1 is the connection the uncached consumer produced and then deposited in the cache. Swapping two parameters in a function signature changed the number of connections a request opens from two to one.

So if you reach for use_cache=False to guarantee a fresh resource per consumer, you have not got one. You have got a value that depends on parameter order.

Annotated, and one output line that settles the argument

Both spellings work:

def old_style(conn = Depends(get_conn)): ...
def new_style(conn: Annotated[str, Depends(get_conn)]): ...

The difference shows up the moment you call the function yourself, which is what a unit test does:

  old_style() -> Depends(dependency=<function get_conn at 0x7f0e927082c0>, use_cache=True, scope=None)
  new_style() -> TypeError: new_style() missing 1 required positional argument: 'conn'

old_style() returned a Depends object. Not a connection — a marker object, silently, with no error, and it will keep travelling until something several frames away fails in a way that has nothing to do with this line. new_style() failed immediately and said what was missing.

That is the case for Annotated, and it is not about newness. The default-argument form lies to every caller that is not FastAPI, including your type checker, which sees conn as a Depends. The Annotated form puts the marker in the annotation where FastAPI reads it, leaves the declared type honest, and lets you name the whole thing once — SessionDep = Annotated[Session, Depends(get_session)] — so the identity that keys the cache is shared by construction rather than by convention.

Chapter 8 is the third element of that cache key, and the reason a dependency that says yield is a different kind of thing.

yield decides when your session closes

A dependency that says yield has two halves and a rule about when the second one runs. Most people learn that rule as a fact to memorise, get it slightly wrong, and then cannot explain why a lazy load blew up halfway through a streamed response. It is derivable, and the derivation is six lines of FastAPI's own source.

async def app(scope, receive, send):
    response_awaited = False
    async with AsyncExitStack() as request_stack:
        scope["fastapi_inner_astack"] = request_stack
        async with AsyncExitStack() as function_stack:
            scope["fastapi_function_astack"] = function_stack
            response = await f(request)
        await response(scope, receive, send)
        response_awaited = True
    ...
# Same as in Starlette
await wrap_app_handling_exceptions(app, request)(scope, receive, send)

An exit stack is a container you push cleanup callbacks onto; when the async with block ends, it runs them, in reverse. There are two of them here, nested, and one line decides everything: await response(...), the call that actually writes bytes to the client, sits inside request_stack and outside function_stack.

A yield dependency goes on request_stack by default. So its teardown runs when the outer block ends, which is after the response has been sent. Pass Depends(..., scope="function") and it goes on the inner one instead, which ends before.

That is the whole mechanism. Everything below is that one line, observed.

=== /ok  -> 200 ===
    session: OPEN
    endpoint body
    >>> http.response.start sent
    >>> response fully sent to the client
    session: CLOSE

=== /fn-scope  -> 200 ===
    fn: OPEN
    endpoint body
    fn: CLOSE
    >>> http.response.start sent
    >>> response fully sent to the client

On the happy path, teardown runs after the client already has the bytes. Which means a db.commit() written after yield commits after you told the client 200. If that commit fails, the client has a success it will never be able to reconcile — put the commit inside the endpoint, and leave close() and rollback() for after the yield.

Now the error path, which goes the other way:

=== /http-error  -> 409 ===
    session: OPEN
    endpoint raises HTTPException
    session: saw HTTPException
    session: CLOSE
    >>> http.response.start sent
    >>> response fully sent to the client

=== /crash  -> 500 ===
    session: OPEN
    endpoint raises RuntimeError
    session: saw RuntimeError
    session: CLOSE

The exception is thrown into your generator at the yield, so a try/except around it sees it — which is how you write a rollback. Then it keeps unwinding, past request_stack, out to wrap_app_handling_exceptions, which sits outside both stacks and turns it into a response. Teardown has already happened by the time anything is sent.

Reconciled into one sentence: teardown runs immediately before whichever comes next, and on the error path the response comes later.

Two more cases fill in the corners:

=== /background  -> 200 ===
    session: OPEN
    endpoint body
    >>> http.response.start sent
    >>> response fully sent to the client
    background task ran
    session: CLOSE

=== /stream  -> 200 ===
    session: OPEN
    >>> http.response.start sent
    stream chunk 0
    stream chunk 1
    stream chunk 2
    >>> response fully sent to the client
    session: CLOSE

A BackgroundTasks job runs before teardown, so it can still use the session — which is not what most people assume, and is a genuinely useful fact until chapter 22 explains why you should not lean on it. And a streaming response holds the session open for the entire stream, because await response(...) does not return until the last chunk is out.

Why this decides whether a lazy load works

Here is the payoff. When a serializer touches an unloaded ORM relationship it fires a lazy SELECT, and whether that works is a question about which stack your session is on and when the serializer runs. Both are answerable from the six lines. Confirm the prediction against a session that raises once it is closed:

=== /request-scope -> 200 {"id":1,"lines":"loaded"}
    session OPEN
    serializer: lazy load OK
    session CLOSED

=== /function-scope -> 200 {"id":1,"lines":"loaded"}
    session OPEN
    serializer: lazy load OK
    session CLOSED

=== /stream-request-scope -> 200 loaded
    session OPEN
    stream: lazy load OK
    session CLOSED

=== /stream-function-scope -> 200 FAILED
    session OPEN
    session CLOSED
    stream: session is closed - cannot lazy load

response_model serialization happens inside f(request), which is inside both stacks, so an ordinary endpoint is safe under either scope. A streaming body is different: its generator runs during await response(...), which is inside request_stack and outside function_stack. Request scope holds the session open for it. Function scope has already closed it, and you get the failure at chunk one, after a 200 has been sent — nothing can turn that into an error response.

That asymmetry is why there are two stacks at all. A comment in fastapi/applications.py says so directly: dependencies with yield were given their own stack "to properly support streaming responses while keeping compatibility with the previous versions ... that allowed doing except HTTPException inside a dependency with yield."

Pydantic is two compiled objects

Declaring a Pydantic model does not create a class with some validation methods on it. It runs a compiler. At class-creation time Pydantic walks your annotations, emits a core schema — a plain dict describing the shape — and hands that to Rust twice:

pydantic 2.13.4 | pydantic_core 2.46.4
  __pydantic_validator__ : <class 'pydantic_core._pydantic_core.SchemaValidator'>
  __pydantic_serializer__: <class 'pydantic_core._pydantic_core.SchemaSerializer'>
  core schema type       : model

Two objects. One reads data in, one writes data out, and they are compiled separately from the same declaration. Almost everything surprising about Pydantic falls out of the fact that there are two of them and they were built for different jobs.

Reading in: three modes, not one

The validator has three input modes and the differences are not cosmetic. validate_python in lax mode coerces; in strict mode it refuses to; and validate_json is a separate code path that parses and validates in one pass in Rust.

  type       input                       lax             strict               json
  int        '7'                           7       ERR int_type                  7
  int        7.0                           7       ERR int_type                  7
  int        7.5          ERR int_from_float       ERR int_type ERR int_from_float
  int        True                          1       ERR int_type   ERR json_invalid
  float      2                           2.0                2.0                2.0
  str        7               ERR string_type    ERR string_type    ERR string_type
  bool       'yes'                      True      ERR bool_type               True
  bool       2              ERR bool_parsing      ERR bool_type   ERR bool_parsing
  list[int]  ('1','2')                [1, 2]      ERR list_type             [1, 2]

Two shapes are worth naming. Coercion is lossless-only: 7.0 becomes 7, 7.5 does not. And it is asymmetric: '7' becomes 7, but 7 never becomes '7', because if it did, str would accept literally everything and stop being a constraint.

FastAPI runs everything through lax python mode. Path, query and header values arrive as strings, which is why ?limit=10 hands you an int and nobody writes int(request.query_params["limit"]) any more. The body goes the same way: FastAPI calls request.json() itself and then hands the resulting Python objects to validate_python — so the JSON-mode column above describes Pydantic, not the path your request bodies take.

Writing out: three modes, and where it bites

The serializer has three modes too, and they are not "the same thing, one of them stringified":

  model_dump()            : {'id': 1, 'issued': datetime.date(2026, 8, 26), 'amount': Decimal('1.10'),
                             'ref': UUID('12345678-1234-5678-1234-567812345678'), 'tags': {'eu'}}
  model_dump(mode='json') : {'id': 1, 'issued': '2026-08-26', 'amount': '1.10',
                             'ref': '12345678-1234-5678-1234-567812345678', 'tags': ['eu']}
  model_dump_json()       : {"id":1,"issued":"2026-08-26","amount":"1.10",
                             "ref":"12345678-1234-5678-1234-567812345678","tags":["eu"]}
  json.dumps(model_dump()) -> TypeError: Object of type date is not JSON serializable

model_dump() is python mode, and that last line is the point: it is not JSON, it does not become JSON, and handing it to json.dumps raises. model_dump(mode="json") is JSON mode producing Python primitives — the bridge. model_dump_json() is JSON mode straight to bytes, in Rust.

Look at amount. Decimal('1.10') serialises to the JSON string "1.10", not the number 1.10, and that is correct — JSON has no decimal type, and emitting 1.1 as a float would silently lose the trailing digit and eventually a cent. Every client consuming your money field gets a string. If nobody told the frontend, they find out in production when total + 1 produces "1.101".

set becomes a list, and timedelta becomes an ISO 8601 duration like PT1M30S. None of these are round-trip identities; they are choices the serializer makes, and your API contract carries them.

The serializer only knows the fields it was built with

Now the fact that makes response_model a security boundary rather than a convention. Take a model with secrets in it, and serialise it through a different declared type:

  the object itself         : {'id': 1, 'email': '[email protected]',
                               'password_hash': '$2b$deadbeef', 'is_admin': True}
  inside Envelope[Public]   : {'user': {'id': 1, 'email': '[email protected]'}}
  TypeAdapter(Public)       : {'id': 1, 'email': '[email protected]'}

The SchemaSerializer was compiled from Public. It emits Public's fields. The runtime class of the object it is handed is irrelevant. This is not a filter applied afterwards; the serializer has no code path for fields it was not built with.

That is exactly what response_model does. Five endpoints returning the identical object:

  /leak    -> {"id":1,"email":"[email protected]","password_hash":"$2b$deadbeef","is_admin":true}
  /annot   -> {"id":1,"email":"[email protected]"}      def annot() -> UserOut
  /model   -> {"id":1,"email":"[email protected]"}      response_model=UserOut
  /dict    -> {"id":1,"email":"[email protected]"}      endpoint returned a plain dict
  /raw     -> {"id":1,"email":"[email protected]","password_hash":"$2b$deadbeef","is_admin":true}

/dict matters: the endpoint returned a dictionary containing password_hash and it still did not escape, because the declared type decides, not the value. /raw is the hole — returning a Response object yourself bypasses serialize_response entirely, and response_model becomes documentation. If you hand-build a JSONResponse, you are the serializer now.

It validates as well as filters, and a failure is your fault, not the client's: return something that does not fit the declared type and you get a 500, on the reasoning that a response violating your own published contract is a bug on your side.

And it is the fast path

Which leaves the objection everyone has: surely an extra validate-then-serialize pass costs something. Two thousand items, twenty requests each, in-process:

  /with        4.29 ms/request   118040 bytes
  /without    25.47 ms/request   118040 bytes

The endpoint with response_model is roughly six times faster than the one without. The reason is in serialize_response: with a response field, FastAPI calls field.serialize_json, and Pydantic's Rust serializer goes straight to JSON bytes. Without one, it falls into else: return jsonable_encoder(response_content) — a recursive pure-Python walk of your whole object graph, followed by json.dumps.

This reversed recently. FastAPI's release notes for 0.130.0 record it: "Serialize JSON response with Pydantic (in Rust), when there's a Pydantic return type or response model ... This results in 2x (or more) performance increase for JSON responses." The following release deprecated ORJSONResponse and UJSONResponse as no longer worth their existence.

So the advice you will still find written down — "response_model costs you a pass, skip it on hot endpoints, use ORJSONResponse" — is now precisely backwards. Declaring the type is the fast path, the safe path and the documented path, and chapter 24 shows it is also the contract.

A connection is a process

Open four database connections from Python, then look at the machine with ps.

python connection 0  ->  backend pid 2050
python connection 1  ->  backend pid 2051
python connection 2  ->  backend pid 2052
python connection 3  ->  backend pid 2053

  PID   RSS COMMAND
 1692 32776 /usr/lib/postgresql/16/bin/postgres -D .../sandbox/pgdata
 1693  8940 postgres: checkpointer
 1694  7588 postgres: background writer
 1696 10388 postgres: walwriter
 1697  9112 postgres: autovacuum launcher
 1698  8324 postgres: logical replication launcher
 2050 15012 postgres: guide guidedb 127.0.0.1(55654) idle in transaction
 2051 15048 postgres: guide guidedb 127.0.0.1(55670) idle in transaction
 2052 15052 postgres: guide guidedb 127.0.0.1(55678) idle in transaction
 2053 15048 postgres: guide guidedb 127.0.0.1(55692) idle in transaction

Four connections, four operating-system processes, roughly 15 MB of resident memory each. Not four threads, not four entries in a table of sockets. Four processes, with process IDs you can send signals to.

PostgreSQL's own architecture chapter says it plainly:

PostgreSQL implements a process per user client/server model. In this model, every client process connects to exactly one backend process. As we do not know ahead of time how many connections will be made, we have to use a supervisor process that spawns a new backend process every time a connection is requested. This supervisor process is called postmaster and listens at a specified TCP/IP port for incoming connections. Whenever it detects a request for a connection, it spawns a new backend process.

Everything else in this chapter is arithmetic on that sentence.

What it costs to ask for one

Connecting is not free, and you can measure how not-free. Here is the median of thirty fresh connections, against a server on 127.0.0.1 with trust authentication and TLS switched off — the cheapest connection this machine can make:

raw psycopg.connect() x30: median 3.50 ms  min 2.96  max 335.73
SELECT 1 on an open connection x30: median 0.126 ms
ratio: opening a connection costs 28x a trivial query

Twenty-eight times, with no network and no cryptography. That number is a floor, not an estimate. A real connection to a managed database adds a TCP handshake, a TLS handshake, and the Postgres startup and authentication exchange, over a network with actual latency — and somewhere in the middle of it, a process gets forked.

So you keep the connection. That is the whole idea of a pool: a set of already-open connections your application holds and lends out, instead of opening one per request and closing it. Reuse turns that 3.50 ms into 0.480 ms of checkout, measured on the same loop.

The number nobody computes

A pool has a ceiling, and SQLAlchemy 2.0.52 picks it for you if you don't:

pool class      : QueuePool
pool_size       : 5
max_overflow    : 10
pool_timeout    : 30.0
pool_recycle    : -1
pool_pre_ping   : False
ceiling per process = 15

pool_size is how many connections the pool keeps around. max_overflow is how many extra it will open under load and then discard. Fifteen, then, per process — and per process is where this goes wrong, because chapter 2 established that --workers 4 gives you four separate Python processes that share no memory. A pool is memory. Four workers is four pools.

4 processes, pool_size=5, idle — no overflow opened
   poolceiling_0 -> 5 backends
   poolceiling_1 -> 5 backends
   poolceiling_2 -> 5 backends
   poolceiling_3 -> 5 backends
  total backends held by the 'app': 20
  arithmetic: processes x pool_size = 4 x 5 = 20

Twenty, at rest. That is not the number that matters, because max_overflow has not been touched — nothing was under enough load to ask for a sixth connection. Drive each process past pool_size and the same count is taken again:

4 processes under load, each driven past pool_size into max_overflow
   poolceiling_0 -> 15 backends
   poolceiling_1 -> 15 backends
   poolceiling_2 -> 15 backends
   poolceiling_3 -> 15 backends
  total backends held by the 'app': 60
  arithmetic: processes x (pool_size + max_overflow) = 4 x 15 = 60

  at rest 20, ceiling 60, server allows 97

So there are two numbers, and confusing them is how this bites. Twenty is what you see when you look; sixty is what the configuration permits. The formula is the second one:

ceiling = processes × (pool_size + max_overflow) × services

Against a server whose own ceiling on this sandbox is max_connections = 100, minus superuser_reserved_connections = 3, so 97 usable. Four workers on SQLAlchemy's defaults can take 60 of that 97 — from one service, before a background worker, a migration runner, or your own psql session has asked for anything. And it will take them at exactly the moment you least want to find out, because overflow opens under load and load is when everything else wants a connection too.

Why serverless melts

Now take the process count away. A serverless platform has no fixed number of application processes: it starts an instance when a request arrives and stops it when traffic drops. Each live instance is its own Python heap, so each is its own pool — frequently a pool of one, connected, used for 40 ms, and left idle.

Multiply by however many instances the platform decided to run. There is no workers in the formula any more, so there is nothing to multiply by except the platform's concurrency, which is a number you do not control and cannot cap from inside your code. That is the shape of the problem: a database that can afford about a hundred processes, in front of an application layer with no upper bound on how many things want one.

Which is why there is a second pool, living somewhere else entirely, and why it breaks things the first one never touched.

Two pools, and the second breaks your session

Here is an error that gets filed in the wrong drawer roughly every time it happens.

asyncpg 0.31.0
  FAILED  asyncpg -> pgbouncer TRANSACTION mode, engine defaults
           DuplicatePreparedStatementError: prepared statement "__asyncpg_stmt_1__" already exists

It has asyncpg in the name, arrives under concurrency, and never happens in development. So it gets read as an async bug — coroutines racing, a session shared across tasks. It is none of those. Something outside your process is deciding which database backend each of your transactions lands on. That something is a second pool, and this is what it broke.

The first pool lives in your process

QueuePool is what chapter 10 described, and it behaves like a lending library. There are so many copies on the shelf (pool_size). When demand exceeds them the library buys a few extra and gives them away again afterwards (max_overflow). Borrowers who find the shelf empty join a waiting list and give up after a while (pool_timeout). A returned copy might have fallen apart, so you can check it before lending it again (pool_pre_ping). And copies past a certain age get retired whether or not anything is wrong with them (pool_recycle).

The analogy holds all the way through, with one exception worth naming now: a book visibly falls apart, and a dead connection does not. Each parameter defends against one specific failure.

The shelf is empty. pool_size=2, max_overflow=1 is a hard ceiling of three, and five threads each want one for six seconds:

  worker 0: got a connection after 11 ms, backend pid 2598
  worker 1: got a connection after 5 ms, backend pid 2600
  worker 2: got a connection after 6 ms, backend pid 2602
  worker 3: FAILED after 3001 ms
  worker 4: FAILED after 3000 ms

sqlalchemy.exc.TimeoutError
QueuePool limit of size 2 overflow 1 reached, connection timed out, timeout 3.00

That is chapter 4's pool timeout in the flesh: a queue inside your own process, waiting on a resource you own rather than on the network. It fires at exactly the configured value — 3001 ms, 3000 ms — so it shows up in a latency graph as a flat cliff at a round number, which is how you tell it from a slow database. And it is a symptom: the pool reported a shortage, it did not cause one.

The copy on the shelf looks fine and is not. This is the exception to the analogy. An idle pooled connection is a socket to a backend process that may have died since you last used it, and nothing about the socket says so — it looks exactly like a working one until you write to it. Kill the backend from outside, then check that connection out:

########## A: no pre_ping — the pool hands you a dead socket ##########
  sqlalchemy.exc.OperationalError
  (psycopg.errors.AdminShutdown) terminating connection due to administrator command
  connection_invalidated: True

########## B: pool_pre_ping=True — same murder, no error ##########
checked in connection with backend pid 2635
  SELECT worked. new backend pid: 2636 (silently reconnected)

It sends a trivial statement at checkout and replaces the connection if it fails. Off by default; costs one round trip; buys survival across every restart, failover and idle reap on the far end. Its limit is stated outright: "the pre-ping approach does not accommodate for connections dropped in the middle of transactions or other SQL operations." The ping is at checkout. Nothing protects the middle.

Something else retires the copy first. Proxies and managed databases close long-lived connections without telling you, so retire yours before they do:

immediate re-checkout, same backend? True (2637 / 2637)
after 2.5s with pool_recycle=2, same backend? False (2637 / 2639)

And "the invalidation only occurs during checkout — not on any connections that are held in a checked out state." A connection you are holding will not be recycled underneath you, which is both the good news and the reason this alone does not save a long-running job.

The second pool lives somewhere else

PgBouncer speaks the PostgreSQL wire protocol on both sides: your application connects to it, and it keeps a much smaller set of real connections to the database. Neon runs one, so this is not hypothetical for you.

Everything hinges on when it hands a real connection back:

session — Server is released back to pool after client disconnects. Default. transaction — Server is released back to pool after transaction finishes. statement — Server is released back to pool after query finishes.

Session mode is a bigger version of what you already have. Transaction mode is a different thing under the same word. Six clients, four transactions each, through three server connections, printing which backend actually ran each one:

# A. 6 concurrent clients, default_pool_size=3, transaction mode
  client 0: [2179, 2179, 2182, 2181]
  client 1: [2181, 2181, 2182, 2181]
  client 2: [2182, 2181, 2179, 2182]
  client 3: [2179, 2179, 2179, 2179]
  6 clients x 4 transactions = 24 transactions served by 3 backends: [2179, 2181, 2182]

# B. the same under session mode
  client 0: [2191, 2191, 2191, 2191]
  backends used: 3 — one per client, and each client kept its own

Read client 2. Four consecutive transactions on one client connection, run by three different processes, in an order nobody chose. Chapter 10 said a connection is a process; under transaction pooling your connection is not a process at all. It is a claim on whichever one is free when your BEGIN arrives.

So anything PostgreSQL remembers per session stops being yours. Six clients each set their own statement_timeout, then read it back one transaction later:

# A. SET statement_timeout — transaction mode
  client 0 set 1s   later read '3s'     <-- WRONG
  client 1 set 2s   later read '5s'     <-- WRONG
  client 2 set 3s   later read '5s'     <-- WRONG
  client 5 set 6s   later read '3s'     <-- WRONG

# B. the same on session mode
  client 0 set 1s   later read '1s'     OK    ... all OK

They are reading each other's settings, left on shared backends. PgBouncer's docs say why: server_reset_query — the DISCARD ALL that would clean a connection between users — "is not used" in transaction mode, "because in that mode, clients must not use any session-based features". The cleanup is skipped because you were not supposed to need it.

Temp tables and session advisory locks fail identically. PgBouncer's feature matrix marks SET/RESET, LISTEN, WITH HOLD CURSOR, SQL-level PREPARE/DEALLOCATE and session advisory locks as Never under transaction pooling, with a framing sentence that ought to be printed on the box: "transaction pooling breaks client expectations of the server by design and can be used only if the application cooperates by not using non-working features."

Back to the error at the top

A prepared statement is a query sent once to be parsed and planned, then run many times by name. Every modern Postgres driver does this unasked, because it is a real saving. asyncpg names them __asyncpg_stmt_N__; psycopg 3 names them _pg3_N.

Put that on a connection that changes backends between transactions. The driver believes statement 1 is prepared; it lands on a backend where it is not, and gets "does not exist" — or lands on one where another client's statement 1 already sits, and gets "already exists". Same fault, two faces, and which you see is luck.

PgBouncer's fix is one setting. With max_prepared_statements non-zero it intercepts protocol-level prepares, renames each unique query to an internal PGBOUNCER_{id}, and re-prepares it on whichever backend you land on. The default is 0. Set it to 1000, re-run the identical script:

  OK      asyncpg -> pgbouncer TRANSACTION mode, engine defaults
  OK      asyncpg -> pgbouncer TRANSACTION mode, statement_cache_size=0
  OK      asyncpg -> direct to postgres, engine defaults

Two things follow, both worth more than the fix. First, the advice you will find online is version-scoped: "set statement_cache_size=0 for asyncpg behind PgBouncer" was right before PgBouncer 1.21 and works here — that is the middle line — but it throws away plan caching. Neon's PgBouncer sets max_prepared_statements=1000, so on the pooled endpoint you do not need it.

Second, the setting fixes exactly one breakage. Re-run the whole suite at 1000:

# A. SET statement_timeout
  client 0 set 1s   later read '3s'     <-- WRONG   (still broken)

# C. a SQL-level PREPARE, then EXECUTE after a transaction boundary
  client 0: InvalidSqlStatementName: prepared statement "st_0" does not exist
  client 1: 10                                       <-- succeeded by luck
  client 2: InvalidSqlStatementName: prepared statement "st_2" does not exist

# D. psycopg3 auto-prepare under contention
  client 0: OK … client 5: OK                        <-- fixed

Protocol-level prepares, which your driver does invisibly: fixed. SQL-level PREPARE typed by you: still broken, because those commands "are forwarded straight to Postgres" untouched. SET: still broken, always will be.

Which endpoint, and when

Neon gives every project two hostnames for one database, differing by one string:

direct:  ep-cool-darkness-123456.us-east-2.aws.neon.tech
pooled:  ep-cool-darkness-123456-pooler.us-east-2.aws.neon.tech

-pooler puts PgBouncer in the path, in transaction mode, with max_client_conn=10000 and default_pool_size at 90% of the compute's max_connections. That 10,000 is how many clients PgBouncer will accept, not how many queries run at once — for a 1 CU compute (a compute unit, Neon's billing quantum of vCPU and RAM) the numbers underneath are 419 real connections and about 377 concurrent transactions.

Neon's own table says use pooled for web applications, serverless functions and high connection churn, and direct for schema migrations, pg_dump, logical replication, long analytics and admin work. The reason is everything above: migrations run session-scoped things — advisory locks, SET, sometimes a CREATE INDEX CONCURRENTLY that must not sit in a transaction. Most codebases never touch session state. Running Alembic through the pooler is how they meet transaction-pooling breakage anyway.

So a correct configuration is two URLs. The application gets -pooler, with pool_pre_ping on and a pool_size you have actually multiplied by your worker count. The migration runner gets the direct host and a pool of one. Different jobs, and the platform says so in the hostname.

Picking the number

Chapter 2 left you holding a product: four worker processes times a forty-thread limiter, so up to 160 threads running handler code at once, every one of them able to want a connection. Chapter 10 gave you the other side: a ceiling of processes × (pool_size + max_overflow), and a server that allows 97. Those two numbers are not equal and were never going to be. Here is what to do about it.

Size from the database's ceiling downward, not from your application's appetite upward. The database's number is fixed and small; yours is an assumption.

1. Start with what the server allows. max_connections minus superuser_reserved_connections. Ninety-seven on this sandbox. Behind a pooler, use the pooler's default_pool_size instead — that is the number of real backends, and the max_client_conn figure in the thousands is a waiting room, not capacity.
2. Subtract what is not your web application. The migration runner, a background worker, pg_dump, and the psql session you will open at exactly the wrong moment. Reserve them explicitly. They are the connections you need most on the day you cannot get one.
3. Divide by process count, not by thread count. A pool is memory, and chapter 2 established that processes share none.
4. That quotient is pool_size + max_overflow, per process. Split it so pool_size carries your steady state and max_overflow absorbs the spike.

Sixty of ninety-seven, for four workers on the defaults, with nothing reserved. That is not a crash. It is a configuration that works until the day a migration runs during a traffic peak.

Then the part that is genuinely counter-intuitive: more threads than connections is not an error condition, and it does not look like one.

each thread holds a connection for 200 ms of real query time

   10 threads, ceiling  5, pool_timeout 30s -> ok  10  err   0  p50     415ms  p90     416ms
   40 threads, ceiling  5, pool_timeout 30s -> ok  40  err   0  p50    1016ms  p90    1621ms
   40 threads, ceiling 15, pool_timeout 30s -> ok  40  err   0  p50     422ms  p90     625ms

Forty threads through a pool of five: every request succeeds, and the median more than doubles. Nothing is logged, nothing raises, no counter increments. The queue is real and it is invisible — chapter 4's third queue, the one that does not report its depth, wearing library clothes. Give the same forty threads a ceiling of fifteen and the latency goes back to roughly the query's own cost.

Saturation is a latency symptom before it is an error symptom, which is why it gets diagnosed as a slow database. The database was never slow. The 200 ms query took 200 ms every time.

It becomes an error only when the wait exceeds pool_timeout — and then it fails in a way you can recognise from across the room:

each thread holds a connection for 500 ms of real query time
      first error: TimeoutError: QueuePool limit of size 5 overflow 0 reached,
                   connection timed out, timeout 3.00
   60 threads, ceiling  5, pool_timeout 3s -> ok  30  err  30  p50 2014ms  p90 3017ms  max 3018ms
   60 threads, ceiling 15, pool_timeout 3s -> ok  60  err   0  p50 1547ms  p90 2059ms  max 2062ms

Look at max: 3018 ms, against a pool_timeout of 3.00 seconds. Half the requests failed, and the slowest survivor is pinned to a round number you configured. That flat ceiling at exactly your timeout is the signature — a real database problem produces a ragged distribution, because queries vary. A pool problem produces a cliff, because a timeout does not.

So the diagnostic, and it transfers to any pool anywhere. Latency that scales with concurrency while the query's own cost stays flat is a pool that is too small. A max latency pinned to a round number is a pool that has run out. Neither is a slow database, and neither will appear in your slow-query log.

A row is a stack of versions

Two rows in a table. Postgres will show you its own bookkeeping columns if you ask for them by name:

 ctid  | xmin | xmax | id | balance
-------+------+------+----+---------
 (0,1) | 1330 |    0 |  1 |     100
 (0,2) | 1330 |    0 |  2 |     200

ctid is the physical address — page 0, slot 1. xmin is the transaction that created the row. xmax is the transaction that killed it, and 0 means nobody has.

Now run one UPDATE on row 1:

 ctid  | xmin | xmax | id | balance
-------+------+------+----+---------
 (0,3) | 1331 |    0 |  1 |     150
 (0,2) | 1330 |    0 |  2 |     200

Row 1 has moved to slot 3, created by a different transaction. So what is at slot 1? Read the raw page — the heap, which is Postgres's name for the table's own data file, as opposed to any index built over it:

 lp | lp_off | t_xmin | t_xmax | t_ctid
----+--------+--------+--------+--------
  1 |   8152 |   1330 |   1331 | (0,3)
  2 |   8112 |   1330 |      0 | (0,2)
  3 |   8072 |   1331 |      0 | (0,3)

Slot 1 is still there, still holding balance = 100, now stamped t_xmax = 1331 and carrying a forward pointer to its replacement. A tuple, in Postgres's vocabulary, is one physical row version — not the Python kind, and not quite a row either, since one row can be several tuples at once.

Which is what page 0 now holds. Three tuples, two rows:

  page 0
  ┌──────────────────────────────────────────────────────────────┐
  │ slot 1   xmin 1330   xmax 1331   balance 100   ctid → (0,3)  │  row 1, dead
  │            created by 1330, ended by 1331 ───────────┐       │
  ├──────────────────────────────────────────────────────┼───────┤
  │ slot 2   xmin 1330   xmax    0   balance 200         │       │  row 2, live
  │            created by 1330, never ended              │       │
  ├──────────────────────────────────────────────────────┼───────┤
  │ slot 3   xmin 1331   xmax    0   balance 150   ◀─────┘       │  row 1, live
  │            created by 1331, never ended                      │
  └──────────────────────────────────────────────────────────────┘

  a transaction with a snapshot taken before 1331 committed reads slot 1
  a transaction with a snapshot taken after  reads slot 3
  both are on the disk at the same time

Nothing was overwritten. An UPDATE in PostgreSQL is an insert of a new tuple, plus a note on the old one saying "dead as of transaction 1331". A DELETE is the note without the insert.

That is MVCC — multiversion concurrency control. A row is not a place where a value is stored; it is a stack of versions, each labelled with the transaction that created it and the one that ended it. A query walks the stack picking the version alive when its snapshot was taken. The manual states the payoff: "reading never blocks writing and writing never blocks reading."

That is what you buy. Now the bills.

First bill: the old versions are still on disk

A hundred thousand rows, autovacuum off, five full-table updates:

 heap_after_load: 5096 kB
 after_1_update:  10192 kB
 after_5_updates: 30 MB          live_rows: 100000

 table_len | tuple_count | tuple_percent | dead_tuple_count | free_percent
  31293440 |      100000 |         14.62 |              236 |        76.62

Six times the file, the same hundred thousand rows in it, and only 14.62% of it live tuples. This is bloat: dead versions nobody can see and nobody has reclaimed.

VACUUM reclaims — autovacuum runs it for you — and it does not do what its name suggests:

 after_plain_vacuum: 30 MB
 table_len | tuple_count | dead_tuple_count | free_percent
  31293440 |      100000 |                0 |        82.99

 after_reusing_freed_space: 30 MB      <- a sixth update, and the file did not grow
 after_vacuum_full: 5096 kB

The file is exactly the same size after VACUUM. What changed is free_percent: 83% of it is now space Postgres will write new tuples into, and the sixth update proves it. Plain VACUUM does not shrink a table, it makes the inside reusable. VACUUM FULL rewrites it into a new file and does shrink — under an ACCESS EXCLUSIVE lock, so nobody reads that table until it finishes. Autovacuum will never issue it.

The steady state Postgres aims at is not "minimal". It is "big enough, and mostly reused".

Second bill, which is a refund: the visibility map

VACUUM has a second job. Once it confirms that every tuple on a page is visible to every transaction, it sets a bit for that page in a side structure called the visibility map.

 all_visible_pages: 0
 VACUUM
 relpages: 222   all_visible_pages_after_vacuum: 222

One bit per page saying "you need not check this page's rows for visibility". Remember it exists and that only VACUUM fills it in; chapter 13 has a query plan whose entire performance depends on it.

Isolation is a rule about which snapshot you get

Everything above is mechanism. Isolation levels are settings on that mechanism, and once you have the mechanism they stop being definitions to memorise.

Read Committed, the default, takes a new snapshot at the start of every statement:

1. NON-REPEATABLE READ under READ COMMITTED (the default)
A: BEGIN; SELECT balance -> 100
B: UPDATE to 999; COMMIT
A: same SELECT, same transaction -> 999   <-- it moved

Repeatable Read takes one snapshot for the whole transaction:

A: BEGIN; SELECT balance -> 100
B: UPDATE to 999; COMMIT
A: same SELECT -> 100   <-- A still sees its snapshot
A: after COMMIT, a NEW transaction sees: 999

That is the entire difference between the two levels.

The one that will bite you

Two requests both add 50 to a balance of 100, the way application code does it — read, add in Python, write back — under the default level:

3. LOST UPDATE: read-modify-write in application code, READ COMMITTED
A read 100, B read 100   (both intend to add 50)
two +50s applied. final balance = 150   (should be 200)  <-- one update was lost

Fifty of somebody's money, gone, with no error and nothing in a log. Now the same two requests, same isolation level, with the arithmetic written as balance = balance + 50:

4. the same, but the arithmetic is done IN SQL (still READ COMMITTED)
final balance = 200

The difference is not the isolation level; it is where the addition happened. When an UPDATE finds a row a concurrent transaction has already changed, Read Committed makes it wait, then re-read the new version and re-evaluate the WHERE clause against it. The + 50 applies to whatever it finds. Your Python variable is a copy taken before the wait, and nothing re-evaluates it.

Generalised: under Read Committed, a value that makes a round trip through your process can go stale in your hands. Three fixes. Do the arithmetic in SQL. Lock the row with SELECT … FOR UPDATE, so the second reader blocks and then reads the committed value (B: was blocked 502 ms, then read 150). Or raise the isolation level, and handle what it gives you instead:

5. REPEATABLE READ turns the lost update into an ERROR
B: SerializationFailure SQLSTATE 40001
    could not serialize access due to concurrent update

The error you are required to retry

Under Repeatable Read the second writer is not allowed to silently lose. It is aborted with SQLSTATE 40001, and the manual is explicit about whose job that is: "Applications using this level must be prepared to retry transactions due to serialization failures."

Serializable goes further and catches anomalies where no two transactions touched the same row. Two transactions each check "is at least one of us on call?", each see yes, each go off duty:

6. WRITE SKEW: allowed by REPEATABLE READ, caught by SERIALIZABLE
  repeatable read  -> both committed. rows = [(1, 0), (2, 0)]  <-- the invariant is broken
  serializable     -> SerializationFailure SQLSTATE 40001
      could not serialize access due to read/write dependencies among transactions
      HINT:  The transaction might succeed if retried.

Both refusals carry the same code. 40001 is not a bug report; it is the contract. Serializable is not slower Repeatable Read — its monitoring "does not introduce any blocking beyond that present in repeatable read". It does not lock more, it aborts more. The cost lands in your error handling, and without a retry loop it lands in your error rate.

And the snapshots move under a reader

One last consequence, because a later chapter needs it. Two statements in one Read Committed transaction can see two different tables. Here is a reader taking page one and page two of a feed, with somebody inserting between them:

reader isolation: read committed
page 1 (OFFSET 0) : ['post 6', 'post 5', 'post 4']
  ... a new row is inserted and committed by somebody else ...
page 2 (OFFSET 3) : ['post 4', 'post 3', 'post 2']

rows the reader saw   : ['post 6', 'post 5', 'post 4', 'post 4', 'post 3', 'post 2']
duplicated            : ['post 4']

The reader asked for six distinct rows and got five, one of them twice. Nothing went wrong. OFFSET 3 means "skip three rows of the table as it is now", and one row was pushed down by the insert. An insert at the other end of the ordering would have made a row vanish from the results entirely, unread. Chapter 24 collects this.

The invariants worth carrying out: an update never overwrites, so tables grow and something must reclaim; a snapshot decides what you see, and the level decides how often you take one; and any value that leaves the database and comes back needs either SQL, a lock, or a retry loop.

The query planner is guessing

You do not tell PostgreSQL how to run a query. You say what you want, and a component called the planner decides how — which index, which join order, whether to bother with an index at all. It decides by estimating how many rows each step will produce, from statistics sampled at some point in the past.

Estimating, from a sample. This goes wrong in interesting ways, and EXPLAIN is how you watch it.

A million-row table with no indexes, and one query:

 Gather  (cost=1000.00..19878.33 rows=50 width=73) (actual time=0.814..47.037 rows=35 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared hit=13027 read=638
   ->  Parallel Seq Scan on events  (cost=0.00..18873.33 rows=21 width=73) (actual time=1.201..33.859 rows=12 loops=3)
         Filter: (user_id = 4242)
         Rows Removed by Filter: 333322
         Buffers: shared hit=13027 read=638
 Execution Time: 47.134 ms

Read it inside out — the indented node runs first and feeds its parent. Every number means something specific:

• cost=1000.00..19878.33 — the planner's estimated cost to first row, then to all rows, in abstract units where 1.0 is roughly one sequential page fetch. Compare costs between plans, never to milliseconds.
• rows=50 — the planner's estimate of rows emitted.
• (actual time=0.814..47.037 rows=35 loops=1) — what really happened: milliseconds to first and last row, real rows per loop, and how many times the node ran. Multiply by loops for the total.
• Buffers: shared hit=13027 read=638 — 8 kB pages found in cache, and pages fetched. The closest thing to a unit of work that does not depend on how busy the machine was.
• Rows Removed by Filter: 333322 — work thrown away. Per worker, and there are three.

That last line is the confession: to return 35 rows the server read a million and discarded 999,965, touching 13,665 pages.

Add a B-tree on user_id and run the identical query:

 Bitmap Heap Scan on events  (cost=4.81..196.36 rows=50 width=73) (actual time=0.047..0.113 rows=35 loops=1)
   Heap Blocks: exact=35
   Buffers: shared hit=35 read=3
   ->  Bitmap Index Scan on events_user_id_idx  (cost=0.00..4.80 rows=50 width=0) (actual time=0.039..0.039 rows=35 loops=1)
         Index Cond: (user_id = 4242)
 Execution Time: 0.131 ms

47.134 ms to 0.131 ms; 13,665 buffers to 38.

The number to read is not the time

Two row counts per node, and the gap between them is where bad plans come from. Here is a table where column b always equals column a:

--- one predicate: the estimate is fine ---
 ->  Bitmap Index Scan on corr_a_b_idx  (cost=0.00..8.82 rows=603 width=0) (actual rows=500 loops=1)
       Index Cond: (a = 7)

--- two predicates on perfectly correlated columns ---
 ->  Bitmap Index Scan on corr_a_b_idx  (cost=0.00..4.33 rows=4 width=0) (actual rows=500 loops=1)
       Index Cond: ((a = 7) AND (b = 7))

rows=4 estimated, 500 actual. A 125-fold underestimate with perfect statistics on both columns individually. The mistake is structural: the planner assumes the predicates are independent and multiplies their selectivities — a selectivity being the fraction of the table a condition is expected to keep. Both are one in two hundred, so together it expects one in forty thousand. In truth every row matching a = 7 also matches b = 7.

On its own this query survives. But an estimate is an input to every decision above it — a node that budgets for four rows and gets five hundred picks a nested loop and runs its inner side five hundred times. That is how a query that was fine last month starts timing out.

The fix is better information, not an override:

--- after CREATE STATISTICS corr_ab (dependencies, ndistinct) ON a, b FROM corr ---
 ->  Bitmap Index Scan on corr_a_b_idx  (cost=0.00..9.26 rows=497 width=0) (actual rows=500 loops=1)

PostgreSQL has no query hints, and that is a position rather than a missing feature: a hint pins a decision to the data as it was the day you wrote it. ANALYZE, extended statistics or a rewritten predicate all fix the reason.

That ANALYZE is a different thing from the one you have been typing. ANALYZE on its own is a command that resamples a table's statistics; ANALYZE inside EXPLAIN (ANALYZE, BUFFERS) is an option meaning "actually run the query". Same word, unrelated jobs, and reaching for the second when you needed the first is a fix that changes nothing.

The procedure that finds the culprit: for every node, compare rows= estimated to rows= actual × loops. Find the deepest node where they diverge by an order of magnitude. That node is the bug.

Column order in a composite index is not a detail

An index on two columns is sorted by the first, then by the second within it — like a phone book sorted by surname, then forename. That serves any prefix of its columns and nothing else. (user_id, created_at) answers questions about user_id, and about user_id and created_at. It cannot answer one about created_at alone, for the same reason a phone book cannot find everyone called Michael.

Both indexes below are 30 MB. Both cover created_at. Only one is a prefix:

--- only (user_id, created_at) exists ---
 ->  Parallel Seq Scan on events (actual time=0.051..72.178 rows=1858 loops=3)
       Filter: (created_at > (now() - '2 days'::interval))
       Rows Removed by Filter: 331475
 Execution Time: 86.854 ms

--- (created_at, user_id) exists ---
 ->  Bitmap Index Scan on ix_created_user (actual time=0.828..0.830 rows=5575 loops=1)
 Execution Time: 9.041 ms

Same disk cost, same columns, ten times the speed from the order alone. Put equality columns first and the range column last — and note that a composite index makes a single-column index on its first column redundant.

Heap Fetches: 0, and where it comes from

In that second plan the index found the rows and the heap was visited anyway. The index knows the values; it does not know whether each row version is visible to your snapshot. That lives in the heap.

Unless it doesn't. Chapter 12 left a bit set per page in the visibility map, filled in by VACUUM. When every row on a page is visible to everyone, the index can answer alone:

 all_visible_pages | total_pages
                 0 |       13665
 ->  Bitmap Heap Scan on events (actual time=1.486..16.157 rows=5575 loops=1)
       Heap Blocks: exact=4608
       Buffers: shared hit=4632
 Execution Time: 16.666 ms

 --- VACUUM, then the identical query ---
 all_visible_pages | total_pages
             13665 |       13665
 ->  Index Only Scan using ix_created_user on events (actual time=0.015..0.625 rows=5575 loops=1)
       Heap Fetches: 0
       Buffers: shared hit=25
 Execution Time: 0.903 ms

16.666 ms to 0.903 ms, 4,632 buffers to 25, because a maintenance job set some bits. Heap Fetches: 0 is the plan telling you the visibility map answered. A nonzero number on a table you expected to be index-only means vacuum is behind, and no amount of index tuning touches that.

An index earns its keep by eliminating rows

The usual rule is "don't index a low-cardinality column". It is nearly right and names the wrong thing. status here has two values in practice: 97% 'done', 3% 'pending'. One index, two queries:

--- status = 'done' (97%) ---
 Parallel Index Only Scan using events_status_idx  (actual rows=323337 loops=3)
 Execution Time: 47.831 ms

--- status = 'pending' (3%) ---
 Index Only Scan using events_status_idx  (actual rows=29990 loops=1)
 Execution Time: 5.366 ms

The column's cardinality did not change between those runs. The selectivity of the value did. And when the query needs the row rather than a count, the index on the common value is worse than not using it — 115.715 ms when forced, against 84.273 ms for the sequential scan the planner chose unaided. Asking for 97% of a table via random single-page fetches is slower than reading it in order, and the planner knows.

So the rule that survives: an index pays when it eliminates most of the table. If the value you filter on is common, it cannot.

Which points at the right tool — index only the rows you query:

    indexrelname    |  size
 ix_created_user    | 30 MB
 ix_pending         | 672 kB

 ->  Index Only Scan using ix_pending on events (actual time=0.031..0.087 rows=581 loops=1)
       Heap Fetches: 0
 Execution Time: 0.133 ms

672 kB against 30 MB, forty-five times smaller, because it indexes 3% of the table and answers the only question anyone asks of that column. Its maintenance cost is proportional to how often rows enter and leave the predicate, which for a pending → done workflow is once each.

And every index is a write cost: another structure to update on every INSERT, and on every UPDATE touching an indexed column, since chapter 12's second physical tuple has to be added to each one. The cheapest index is the one you did not create; the second cheapest is partial.

An ORM is two patterns

An object-relational mapper has a harder job than it looks. Rows are values; objects are identities. A row is a set of columns that happens to sit at an address. An object is a thing you can hold two references to, mutate through one, and read through the other.

Two problems fall out of that mismatch, and two patterns are the answers. Both are older than SQLAlchemy; Martin Fowler named them in Patterns of Enterprise Application Architecture. Learn the patterns and SQLAlchemy stops being a library with surprising behaviour and becomes one implementation of them.

Problem one: the same row, loaded twice

You load author 1 for a permissions check. Elsewhere in the same request, you load author 1 for the response. Two Python objects holding the same identity — and if code edits one, the other is silently wrong.

The identity map is the answer: within a defined scope, one in-memory object per database identity.

--- first load by PK ---
a1: Author(id=1, name='Author 1') id(): 139894537577552
--- second load, different query shape, same PK ---
a2: Author(id=1, name='Author 1') id(): 139894537577552
a1 is a2 -> True
--- a third route: through a relationship ---
a3 is a1 -> True
--- mutate through one name, read through the other ---
a2.name is now: RENAMED

is, not ==. And you can look at the map — it is a dictionary keyed on a tuple:

   (<class 'models.Author'>, (1,), None) -> Author(id=1, name='Author 1')
   (<class 'models.Book'>, (1,), None) -> Book(id=1, title='Book 1.1')

The key is (mapped class, primary key tuple, discriminator). The scope is one Session — which in SQLAlchemy is one identity map plus one database transaction. Two sessions, two maps, two objects for the same row: x is y -> False.

### changed in memory only, not flushed
### now re-SELECT the same row
SELECT author.id, author.name, author.bio FROM author WHERE author.id = %(id_1)s::INTEGER
### row came back from the DB, but object says: LOCAL EDIT
### same object? True

Problem two: what order do the writes go in?

You build an object graph in memory. The database needs statements, in an order that satisfies foreign keys, with generated primary keys threaded from parents into children.

The unit of work is the answer: accumulate every change, work out the order at the end, emit it as one transaction. SQLAlchemy's own summary:

The ORM objects maintained by a Session are instrumented such that whenever an attribute or a collection is modified in the Python program, a change event is generated which is recorded by the Session. Whenever the database is about to be queried, or when the transaction is about to be committed, the Session first flushes all pending changes stored in memory to the database. This is known as the unit of work pattern.

Add a child before its parent, on purpose, and watch it not matter:

### s.new (insertion order): ['Book', 'Author']
INSERT INTO author (name, bio) VALUES (...) RETURNING author.id
INSERT INTO book (title, author_id) VALUES (...) RETURNING book.id
### author id: 7 book.author_id: 7

The author went first, because the unit of work sorts by foreign-key dependency rather than by the order you added things, and it carried the generated author.id into the child. It also batches: four Author objects become a single statement using SQLAlchemy 2.0's insertmanyvalues form.

That is the thing an ORM buys you that a query builder does not. Not "write less SQL" — declare a desired end state and let something else derive a legal sequence of statements that reaches it.

flush writes. commit keeps.

### calling flush()
INSERT INTO author (name, bio) VALUES (...) RETURNING author.id
### after flush, pk is: 6 persistent: True
### rolling back — flush did not commit
ROLLBACK
### count of 'Flushed': False

flush() emitted a real INSERT and got a real primary key back, generated by the database and available in Python. Then the rollback erased it. Flush is real SQL inside an open transaction: visible to you, invisible to everyone else, undone by a rollback. It is how you get a generated key before you are ready to commit, and it is not a durability boundary.

commit() is. It flushes first — unconditionally, whatever your autoflush setting — then commits.

Autoflush is the same machinery on a hair trigger: before any query, pending changes are flushed so your query sees your own uncommitted work. Same SELECT count(*), twice:

########## autoflush ON (default) ##########
INSERT INTO author (name, bio) VALUES (...)     <- autoflush fired
### count seen by the query: 6

########## autoflush OFF ##########
### count seen by the query: 5

Neither is wrong. But if you have seen an INSERT in a log where your code only reads, that is autoflush doing what it says.

The most surprising thing in the library

commit() does one more thing, derivable rather than memorable once you know the Session caches object state.

### __dict__ keys before commit: ['bio', 'id', 'name']
### calling commit()
COMMIT
### __dict__ keys after commit: []
### inspect(a).expired -> True
### NOW touching a.name — watch for a SELECT
BEGIN (implicit)
SELECT author.id, author.name, author.bio FROM author WHERE author.id = %(pk_1)s::INTEGER
### a.name = Author 1

The attribute dictionary is empty. Reading a.name, an attribute access on an object you are holding, opens a new transaction and re-selects the whole row.

Why would a library do that? Because those loaded values were correct as of the transaction that just ended. The moment it closes, they are values from a snapshot nobody is standing in. The documentation states it:

Finally, all objects within the Session are expired as the transaction is closed out. This is so that when the instances are next accessed, either through attribute access or by them being present in the result of a SELECT, they receive the most recent state. This behavior may be controlled by the expire_on_commit flag, which may be set to False when this behavior is undesirable.

Set expire_on_commit=False and the object keeps its values and emits nothing. You are choosing: possibly-stale values with no extra queries, or guaranteed-fresh values at one SELECT per object per commit. Neither is free.

Where it explodes, and which explosion it is

Now close the session, as chapter 8's dependency does when a request finishes. The object is detached: it exists in Python, has a database identity, and has no session to ask.

### after close(), detached: True expired: True
### DetachedInstanceError: Instance <Author at 0x...> is not bound to a Session;
    attribute refresh operation cannot proceed

That is expire_on_commit and close() compounding — the commit emptied the object, the close removed the only thing that could refill it — and it surfaces during response serialization, long after the commit() that caused it.

There is a second form, and telling them apart is the diagnosis:

########## detached but NOT expired ##########
### a.name reads fine from the last loaded value: Author 1
### but an unloaded relationship still explodes:
### DetachedInstanceError: Parent instance <Author at 0x...> is not bound to a Session;
    lazy load operation of attribute 'books' cannot proceed

Same exception class, different cause. "attribute refresh operation cannot proceed" means expired columns, and expire_on_commit=False fixes it. "lazy load operation of attribute 'books' cannot proceed" means a relationship that was never loaded, and nothing about expiry will fix it — you had to load it before detaching.

Which raises the obvious question: how do you load a relationship, and why does it so often turn into a hundred queries?

N+1 is a loop, not a relationship

Five authors, three books each. Load the authors, then touch every book. How many queries?

The obvious answer is 16: one for the authors, one per book. The other obvious answer is 6. Guess before you look.

=== 1. lazy='select' (the default) — N+1 ===
authors=5 books touched=15 QUERIES=6
--- [1] ---
SELECT author.id, author.name, author.bio
FROM author
--- [2] ---   (and [3], [4], [5], [6] — identical)
SELECT book.id AS book_id, book.title AS book_title, book.author_id AS book_author_id
FROM book
WHERE %(param_1)s::INTEGER = book.author_id

Six. One for the authors and one per author, because each query loads that author's whole collection, so the second book you touch is already in memory.

Now the other direction: fifteen books, touching book.author on each, with five distinct authors among them.

=== N+1 for many-to-one, the same shape, no options ===
  QUERIES: 6 for 15 books over 5 distinct authors
  (the identity map is why it is 6 and not 16)

Six, from fifteen attribute accesses. Chapter 14's identity map is doing it: the first book asks for author 3 and the object goes into the map; the fourth book asks for author 3 and the ORM finds the identity already loaded and issues nothing.

Which means the usual diagnostic is wrong. "N+1" is not a count you can check. Deduplication makes the number smaller than the formula predicts, and people who count wrong conclude the problem is not there. The right question is: does the query count grow with the size of the result set? Five authors, six queries. Five hundred authors, five hundred and one.

The mechanism: a relationship attribute is lazy by default, so reading it emits a SELECT. Put that read in a loop — including the invisible loop that response serialization runs over your list — and you have written a query per iteration. It is a loop, not a feature of relationships, and it is invisible in Python because attribute access does not look like I/O.

What the fixes actually emit

Three strategies are worth knowing, and the way to know them is the SQL, not the name.

selectinload — two queries, the second an IN list of the parents you already have.

=== 2. selectinload ===
authors=5 books touched=15 QUERIES=2
--- [2] ---
SELECT book.author_id, book.id, book.title
FROM book
WHERE book.author_id IN (%(primary_keys_1)s::INTEGER, … %(primary_keys_5)s::INTEGER)

Load the parents, collect their keys, fetch all children in one statement, distribute them. Two round trips regardless of parent count, and no change to the original query.

joinedload — one query, a LEFT OUTER JOIN against an anonymous alias.

=== 3. joinedload ===
authors=5 books touched=15 QUERIES=1
SELECT author.id, author.name, author.bio, book_1.id AS id_1, book_1.title, book_1.author_id
FROM author LEFT OUTER JOIN book AS book_1 ON author.id = book_1.author_id

One round trip. It looks strictly better. It is not, for reasons of arithmetic rather than taste.

The fan-out

A join returns rows, not objects, and a parent with n children appears in n of them. With one collection that is a mild waste. With two collections on the same parent it multiplies.

Ten parents, ten x children each, ten y children each:

rows the double joinedload makes the server build and ship: 1000
  = 10 parents x 10 xs x 10 ys
two joinedloads    objects=10 children=200 queries=1
two selectinloads  objects=10 children=200 queries=3

A thousand rows across the wire to construct two hundred and ten objects. selectinload shipped 220 rows in three round trips for the identical result.

The trade in one line: joinedload converts round trips into row multiplication, and rows are not free. One query is not the goal; least total work is, and for a collection that is usually two or three statements rather than one enormous one.

The multiplication also leaks into Python — ten parents came back a hundred times each, so SQLAlchemy makes you say so:

=== joinedload on a collection without .unique() ===
  InvalidRequestError: The unique() method must be invoked on this Result, as it contains
  results that include joined eager loads against collections

SQLAlchemy's recommendation matches the arithmetic: "One to Many / Many to Many Collection — The selectinload is generally the best loading strategy to use", and for many to one, where each child has exactly one parent and there is no fan-out, "the joinedload strategy is the most general purpose strategy."

joinedload and LIMIT

Here is a claim you will meet that needs correcting rather than repeating: "joinedload on a collection breaks LIMIT" — because with ten rows per author, LIMIT 2 would return two rows, which is most of one author.

It would, if SQLAlchemy let the LIMIT sit next to the join. It does not:

asked for LIMIT 2, got 2 authors: ['Author 1', 'Author 2']
SELECT anon_1.id, anon_1.name, anon_1.bio, book_1.id AS id_1, book_1.title, book_1.author_id
FROM (SELECT author.id AS id, author.name AS name, author.bio AS bio
FROM author
 LIMIT %(param_1)s::INTEGER) AS anon_1 LEFT OUTER JOIN book AS book_1 ON anon_1.id = book_1.author_id

Your LIMIT was applied to a subquery and the eager-load join applied to that. Two authors, complete collections. Deliberately: "if the query contains a modifier that impacts the rows returned externally to the joins, such as when using DISTINCT, LIMIT, OFFSET or equivalent, the completed statement is first wrapped inside a subquery" — and then, rare in technical documentation, "SQLAlchemy's joined eager loading goes the extra mile, and then ten miles further, to absolutely ensure that it does not affect the end result of the query."

The true statement is narrower and more useful: joinedload on a collection multiplies rows, forces .unique(), and forces a subquery whenever you paginate. The fear about LIMIT is real and applies to a hand-written .join(), which is the next section.

The join you wrote is not the collection you read

This is the trap with the highest ratio of confidence to correctness, because the join is right there in the SQL.

Filter authors by a book title, join to books, read author.books:

########## contains_eager: filter on the joined table AND populate it ##########
authors: 5 books per author: [1, 1, 1, 1, 1]
SELECT book.id, book.title, book.author_id, author.id AS id_1, author.name, author.bio
FROM author JOIN book ON author.id = book.author_id
WHERE book.title LIKE %(title_1)s::VARCHAR

########## the same join WITHOUT contains_eager ##########
authors: 5 books per author: [3, 3, 3, 3, 3]
QUERIES: 6

With contains_eager, each author's books holds the one matching book, populated from the join, in one query. Without it, the join decided which authors came back and the collection was lazily loaded in full — three books each, six queries.

Neither is a bug. Select.join alters which rows your query returns; joinedload deliberately does not alter results at all — "while Select.join is used to alter the results of a query, joinedload goes through great lengths to not alter the results of the query." Two jobs, and the ORM will not assume the join you wrote was meant to fill the collection you are about to read. contains_eager is you saying so.

The defensive default

Every failure in this chapter is a lazy load firing somewhere you were not looking. So turn them into errors:

=== 5. raiseload ===
raiseload fired: 'Author.books' is not available due to lazy='raise'
queries so far: 1

raiseload("*") on a query, or lazy="raise" on the relationship, converts "quietly emit a hundred queries" into "raise, at the line that did it, with the attribute name in the message". Set it, then declare what you need with selectinload and joinedload. Every N+1 becomes a loud failure in a test rather than a latency graph in production, and each endpoint's loading strategy becomes something you wrote down instead of something you inherited.

The cost is honest: raiseload also breaks the convenient lazy access that made an ORM pleasant. You are trading "it just works" for "it tells you when it doesn't", and chapter 14's DetachedInstanceError already showed what the alternative costs at the far end of a request.

A migration is a linked list with blind spots

Two users, with names in them:

 id |       email       | display_name
----+-------------------+--------------
  1 | [email protected]   | Ada Lovelace
  2 | [email protected] | Grace Hopper

Rename the column in your models — display_name becomes full_name, nothing else changes — and run alembic revision --autogenerate. Alembic looks at your models, looks at the database, and writes this:

def upgrade() -> None:
    op.add_column('users', sa.Column('full_name', sa.String(), nullable=True))
    op.drop_column('users', 'display_name')

Run it.

 id |       email       | full_name
----+-------------------+-----------
  1 | [email protected]   |
  2 | [email protected] |

Ada and Grace are gone, and nothing warned you. This is not a bug, and understanding why it is not a bug is most of what you need to know about migrations.

What a migration is

Strip the tooling and a migration is a Python module with four things in it:

revision: str = 'b38548168040'
down_revision: Union[str, Sequence[str], None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None

def upgrade() -> None: ...
def downgrade() -> None: ...

revision is this node's ID; down_revision points at the previous one. That is a linked list made of files on disk, and Alembic is walking it in one direction or the other.

The entire state it keeps in your database:

                    Table "public.alembic_version"
   Column    |         Type          | Nullable
 version_num | character varying(32) | not null

 version_num
 b38548168040

One column, one row per head — a pointer saying "the database is at this node". No checksums, no record of the schema, no way to notice the two have diverged. alembic_version records what Alembic believes, not what is true, and that explains the whole failure class below.

What --autogenerate compares

It diffs the MetaData your models build at import time against the live catalogue, then emits operations turning the second into the first. You can watch it announce each comparison:

INFO  [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.tables
INFO  [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.types
INFO  [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.constraints
INFO  [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.defaults
INFO  [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.checkconstraint_byname

Now go back to the rename. Before: a column called display_name. After: a column called full_name. Nothing in either structure says they are the same column — no identity, no history, only a name.

INFO  [alembic.autogenerate.compare.tables] Detected added column 'users.full_name'
INFO  [alembic.autogenerate.compare.tables] Detected removed column 'users.display_name'

A diff between two states cannot see an intention that existed only between them. Autogenerate is structurally blind to anything whose meaning lives in the transition rather than in either endpoint. A rename is the clearest case; a column split, a type migration needing a value transformation, and any data backfill are the same shape. Autogenerate never looks at data at all.

The operating procedure follows: read every generated migration before you run it, and treat every drop_column next to an add_column as a rename until proved otherwise. The correct version here is op.alter_column('users', 'display_name', new_column_name='full_name'), written by hand.

Transactional DDL is PostgreSQL's, not Alembic's

DDL is data definition language — the statements that change the shape of the database rather than its contents: CREATE, ALTER, DROP. Its counterpart is DML, data manipulation language, which is SELECT, INSERT, UPDATE, DELETE. A migration is almost entirely DDL, and one property of it decides how much a failed migration can hurt you.

Write a migration doing two things, where the second fails:

def upgrade() -> None:
    op.add_column('users', sa.Column('locale', sa.String(length=8), nullable=True))
    op.alter_column('users', 'full_name', nullable=False)

The rows still have NULL in full_name, courtesy of the rename above:

sqlalchemy.exc.IntegrityError: (psycopg.errors.NotNullViolation) column "full_name" of
relation "users" contains null values
[SQL: ALTER TABLE users ALTER COLUMN full_name SET NOT NULL]

The first statement succeeded, so the table has a half-added locale column and a version pointer that says it doesn't. Right?

  Column   |       Type        | Nullable
 id        | integer           | not null
 email     | character varying | not null
 full_name | character varying |

 version_num
 16a1950aef3f

No locale. Version unchanged. Nothing happened at all.

That is PostgreSQL, not Alembic: DDL participates in transactions like anything else, so a rolled-back migration leaves the schema exactly as it found it. Alembic notices and says so at startup — Will assume transactional DDL. Run the identical failure on MySQL and you get a half-migrated table and a manual repair. So do not write one-statement migrations "in case a failure leaves things halfway"; on PostgreSQL you can put a whole coherent change in one revision and know it is all-or-nothing.

Two heads

The list becomes a tree the moment two people branch from the same node. Alembic will not let that happen by accident — please specify --splice to create a new branch from this revision. With --splice, or by merging two feature branches in git:

=== heads ===
aaaa1111 (head)
bbbb2222 (head)

=== history ===
16a1950aef3f -> aaaa1111 (head), feature A: avatar_url
16a1950aef3f -> bbbb2222 (head), feature B: locale
b38548168040 -> 16a1950aef3f (branchpoint), rename display_name to full_name

=== what happens on 'upgrade head' ===
ERROR Multiple head revisions are present for given argument 'head'; please specify a
specific target revision, '<branchname>@head' to narrow to a specific head, or 'heads'

Your deploy stops. alembic merge fixes it with a revision whose down_revision is a tuple: ('aaaa1111', 'bbbb2222'). And the bookkeeping is visible in --sql mode, which prints statements instead of running them:

BEGIN;
-- Running upgrade 16a1950aef3f -> bbbb2222
ALTER TABLE users ADD COLUMN locale VARCHAR(8);
UPDATE alembic_version SET version_num='bbbb2222' WHERE alembic_version.version_num = '16a1950aef3f';

-- Running upgrade 16a1950aef3f -> aaaa1111
ALTER TABLE users ADD COLUMN avatar_url VARCHAR(255);
INSERT INTO alembic_version (version_num) VALUES ('aaaa1111') RETURNING alembic_version.version_num;

-- Running upgrade aaaa1111, bbbb2222 -> merge001
DELETE FROM alembic_version WHERE alembic_version.version_num = 'aaaa1111';
UPDATE alembic_version SET version_num='merge001' WHERE alembic_version.version_num = 'bbbb2222';
COMMIT;

UPDATE to move the first head, INSERT to add a row for the second, DELETE at the merge to collapse back to one.

The operational half, which is where the outages are

Everything above is about whether your migration is correct. The rest is about whether running it takes your site down, and those are separate questions.

A reader holds an open transaction on a table — an analytics query, a psql session somebody left open. An ALTER TABLE arrives, needs ACCESS EXCLUSIVE, conflicts, and waits. Fine. Then an ordinary SELECT arrives:

    (3574, 'idle in transaction', 'Client', 'AccessShareLock',     True,  'SELECT count(*) FROM events WHERE id < 10')
    (3576, 'active',              'Lock',   'AccessExclusiveLock', False, 'ALTER TABLE events ADD COLUMN lock_demo int')
    (3578, 'active',              'Lock',   'AccessShareLock',     False, 'SELECT count(*) FROM events WHERE id < 10')
  after 4s the plain SELECT gave up: LockNotAvailable after 4001 ms
  ALTER: succeeded after 4517 ms

Read the third row. That SELECT is blocked — not by the reader in row one, since two readers never conflict, but because the lock queue is ordered and there is a pending exclusive lock ahead of it.

One idle transaction plus one careless ALTER TABLE takes the table down for every reader, and the ALTER itself might have taken a millisecond. The outage is the queue, not the work.

The fix is to refuse to queue:

  ALTER gave up after 1001 ms:
    canceling statement due to lock timeout SQLSTATE 55P03
  -> the queue never formed; readers were never blocked

SET lock_timeout = '1s' at the top of a migration converts "block the world until I get my lock" into "fail, and retry in a minute". A failed migration is an inconvenience; a ten-minute lock queue on your busiest table is an incident. Put it in env.py and it applies to everything you ever write.

Three operations and what they cost

Adding a column with a default no longer rewrites the table, if the default is constant. Two ALTERs on a million rows, differing by one word:

 filenode_before | size_before
           24771 | 107 MB

ALTER TABLE events ADD COLUMN tier text NOT NULL DEFAULT 'free';
Time: 3.229 ms
 filenode_after  | size_after
           24771 | 107 MB
 attname | atthasmissing | attmissingval
 tier    | t             | {free}

ALTER TABLE events ADD COLUMN token uuid NOT NULL DEFAULT gen_random_uuid();
Time: 8311.373 ms
 filenode_after_volatile | size
                   25050 | 131 MB

3.2 ms against 8.3 seconds. Same filenode means the file was never rewritten: "the default is evaluated at the time of the statement and the result stored in the table's metadata … In neither case is a rewrite of the table required." gen_random_uuid() is volatile — a different value per row — so there is nothing to store once, and every row is rewritten under ACCESS EXCLUSIVE.

Building an index blocks writers, unless you ask it not to. Same index, same table, a writer hammering it throughout:

  CREATE INDEX                 build  1.48s   writes   48   longest single write blocked  1477.8 ms
  CREATE INDEX CONCURRENTLY    build  1.99s   writes  313   longest single write blocked    72.4 ms

34% longer to build, and it blocked a single write for 72 ms instead of 1478 — 313 writes got through instead of 48. It scans the table twice under a weaker lock, and costs you two things: it can fail and leave an invalid index you must drop, and it cannot run inside a transaction block. Which collides with the transactional DDL you were just told to enjoy: an Alembic migration containing CREATE INDEX CONCURRENTLY must be marked non-transactional, and is then not all-or-nothing.

A NOT NULL constraint can be added without a long exclusive lock:

--- the naive way: one ALTER that scans the whole table under ACCESS EXCLUSIVE ---
ALTER TABLE events ALTER COLUMN nn1 SET NOT NULL;                            Time: 240.925 ms
--- online step 1: add the constraint NOT VALID (no scan) ---
ALTER TABLE events ADD CONSTRAINT ck_nn2 CHECK (nn2 IS NOT NULL) NOT VALID;  Time: 2.207 ms
--- online step 2: VALIDATE (scans, but under SHARE UPDATE EXCLUSIVE) ---
ALTER TABLE events VALIDATE CONSTRAINT ck_nn2;                               Time: 181.771 ms
--- online step 3: SET NOT NULL now skips the scan ---
ALTER TABLE events ALTER COLUMN nn2 SET NOT NULL;                            Time: 0.790 ms

The three-step version takes 184.8 ms against 240.9 ms, and that is not the point. The one-liner holds ACCESS EXCLUSIVE for 240.9 ms; the three-step version holds it for 2.2 ms and then 0.8 ms, with the long scan running under SHARE UPDATE EXCLUSIVE, which blocks neither readers nor writers. Step three is instant because "if a valid CHECK constraint exists … which proves no NULL can exist, then the table scan is skipped."

The metric for an online migration is not total time. It is the duration of the strictest lock. Every technique here is that one trade: more total work, in smaller exclusive pieces.

Expand and contract

The last technique is a way of sequencing deploys, and it follows from one observation: during any deploy, old code and new code are both running. A migration the old code cannot survive breaks requests in that window however fast it runs.

So do not change a schema. Grow into a new one and let go of the old:

1. Expand. Add the new column, nullable, unconstrained. Old code ignores it. Deploy.
2. Backfill. Copy the data across in batches, outside a migration, with the application running.
3. Migrate the code. Deploy code that writes both columns and reads the new one.
4. Contract. Once nothing reads the old column, drop it. Deploy.

Which is abstract until you do it to the migration this chapter opened with. Here is display_name → full_name again, the one that deleted Ada and Grace — this time as four steps, with writes arriving throughout:

  START — old code reads and writes display_name
    columns: ['id', 'email', 'display_name']
    (1, '[email protected]', 'Ada Lovelace')
    (2, '[email protected]', 'Grace Hopper')

  STEP 1 — EXPAND: add the new column, nullable. Old code has not changed.
  old code is still writing, and still correct
    columns: ['id', 'email', 'display_name', 'full_name']
    (3, '[email protected]', 'Alan Turing', None)

  STEP 2 — BACKFILL, in batches, outside a migration:
    batch updated 2 rows
    batch updated 1 rows
    batch updated 0 rows
    backfilled 3

  STEP 3 — MIGRATE THE CODE: new code writes BOTH, reads full_name.
  both columns agree, so either version of the code is correct
    (4, '[email protected]', 'Katherine Johnson', 'Katherine Johnson')

  STEP 4 — CONTRACT: nothing reads display_name any more.
  END — and no row ever lost a name
    columns: ['id', 'email', 'full_name']
    (1, '[email protected]', 'Ada Lovelace')
    (4, '[email protected]', 'Katherine Johnson')

Four things in that trace are worth naming, because they are the whole technique.

Alan arrives during step 1, written by old code that has never heard of full_name, and he is not lost — he is a row the backfill has not reached yet. That is the property expand buys: the new column being nullable is what makes old code legal rather than broken.

The backfill runs in batches and stops on its own. The statement behind it is the one to steal:

WITH b AS (
  SELECT id FROM ec_users
   WHERE full_name IS NULL AND display_name IS NOT NULL
   LIMIT 2 FOR UPDATE SKIP LOCKED
)
UPDATE ec_users u SET full_name = u.display_name FROM b WHERE u.id = b.id;

LIMIT bounds how many rows one transaction locks, so it never becomes the long-running write that chapter 12's bloat and this chapter's lock queue both punish. FOR UPDATE SKIP LOCKED steps over rows a live request is currently holding rather than waiting behind them — the backfill yields to the application instead of competing with it. And re-running it is safe, because WHERE full_name IS NULL makes the batch idempotent: it is chapter 22's rule arriving early.

Step 3 is the only step where the code writes twice, and it exists solely so that a rollback is survivable. Deploy step 3, discover a problem, roll back to step 1's code, and everything still works, because both columns are correct and either one can be read.

Step 4 is the only irreversible one, and by the time you reach it nothing has read the old column for a full deploy cycle.

Four deploys where you wanted one. That is the real cost of a rename, and it is why the two-line autogenerated version at the top of this chapter is dangerous — not only because it drops data, but because it pretends there was an atomic way to do it. There was not. There was a sequence, and the tool cannot write it for you because the tool cannot see your deploys.

Neon splits storage from compute

Part three assumed one thing quietly: that the process running your query and the disk holding your data are on the same machine. Chapter 10's backend process reads pages from a filesystem. Chapter 12's UPDATE writes a tuple to a page and a record to a log, both local. Chapter 16's CREATE INDEX builds a file.

Neon removes that assumption. What is left is still Postgres — same parser, same planner, same MVCC — with the storage underneath replaced by something at the other end of a network. Every unusual thing about Neon follows from that one substitution. Read it as "Postgres with a nicer console" and each of them stays an unexplained quirk.

WAL is the seam

To split a database in two you need a joint, and Postgres already had one.

The write-ahead log is Postgres's durability mechanism: before a change reaches a data page, a record describing it is written and flushed. Crash recovery replays the log; replication ships it. The log is, in a real sense, the authoritative description of the database — the data pages are a materialised convenience derived from it.

Neon takes that literally:

Instead of running Postgres as a single stateful system tied to a VM and its filesystem, Lakebase Postgres is a serverless database that splits the system into two independent layers: compute and storage. These layers communicate over the network, with a stream of write-ahead log (WAL) records connecting them.

The compute node is a real Postgres — "it parses SQL, plans queries, executes transactions, enforces MVCC, and manages locks and indexes… What is different is what the compute node is responsible for. It exists to execute work, not to preserve data." And the commit path moves: "Instead of flushing WAL to a local filesystem, the compute node streams WAL to the storage layer. A transaction is considered committed once that WAL has been acknowledged by a quorum of safekeepers."

Three pieces sit on the far side, one line each in Neon's summary:

• Safekeepers: define correctness by replicating WAL
• The pageserver: turns WAL into queryable data pages
• Object storage: holds long-term, immutable history

Safekeepers hold the log and vote; a commit is a quorum acknowledgement rather than an fsync. The pageserver consumes WAL and can produce any page as it existed at any log position, reconstructing it by replaying records. Object storage holds the history and is kept off the query path — "queries do not read from object storage." Reads follow a familiar order: RAM, then a local NVMe cache on the compute node, then the pageserver for a specific page at a specific log sequence number.

Scale to zero, and what a cold start costs

If the compute node holds no durable state, stopping it loses nothing. So Neon stops it: "When your database is inactive, it automatically scales to zero after 5 minutes … Once you query the database again, it reactivates automatically within a few hundred milliseconds." Fixed on the Free plan, disableable on paid ones, offered only up to 16 CU. What counts as inactive is careful — "It treats an 'idle-in-transaction' connection as active to avoid breaking application logic that involves long-running transactions" — which also means a leaked transaction is a compute that never sleeps.

The cost of waking is not only the wake:

if your Neon project has been idle for more than 7 days, you may experience a slightly longer activation time. … Also, Postgres memory buffers are cold after a compute wakes up, which means that initial queries may take longer until the memory buffers are warmed.

Chapter 13 is why that matters. Every plan there reported Buffers: shared hit=N read=M. A woken compute has an empty shared buffer cache, so a query that read 25 buffers from cache now fetches all of them, and the ones missing the local NVMe cache travel to a pageserver.

There is a second consequence: anything Postgres keeps in memory dies with the compute. Session state, and also "Statistics collected by the Postgres cumulative statistics system are not saved when a Neon compute is suspended due to inactivity or restarted." Your pg_stat_statements counters reset when traffic goes quiet, so query-frequency analysis on a low-traffic project measures the interval since the last nap.

Branching, and what it is actually for

Here is the payoff of putting history in the storage layer:

This design turns traditionally heavy-weight database operations (which usually require copying large amounts of data) into simple metadata operations. These include creating a new branch, restoring from a snapshot, spinning up a read replica, or attaching a new compute node.

A branch is a copy-on-write clone: "A branch and its parent can share the same data but diverge at the point of branch creation. Writes to a branch are saved as a delta. … Creating a branch does not increase load on the parent branch or affect it in any way."

Copy-on-write means the branch starts as a reference to the parent's history at a log position, not a copy of it. Only what you change is stored separately. So branching a 200 GB database costs the price of writing down a pointer, and the load on production is nil because nothing was read from it.

Neon's hierarchy also separates a branch — which is data — from a compute endpoint, a running Postgres attached to a branch. Different objects, different lifecycles: exactly the split from the start of this chapter, exposed as two rows in a console.

What it is for, from the uses the docs name: a database per pull request, since a preview deployment can get a branch with production-shaped data and delete it with the pull request; parallel CI, so test suites stop fighting over one database; rehearsing a destructive change, by running chapter 16's migration against real data volumes and throwing the branch away; and restore as a branch — recovery is not a restore procedure but a branch from a past point in time, which Neon calls instant restore.

How far back you can reach is the history window: "defaults of 6 hours on Free plan and 1 day on paid plans… You can configure it up to 7 days on Launch or 30 days on Scale plans." One day by default on a paid plan. If your recovery expectation is longer, that is a setting to go and change.

The arithmetic from chapter 10, priced

Neon publishes max_connections per compute size, and the table is chapter 10 stated as a price list:

Roughly 105 connections per gigabyte of RAM, because a connection is a process and processes need memory. Seven are reserved for Neon's own superuser role, so a 0.25 CU compute gives your application 97 — and four uvicorn workers on SQLAlchemy's untouched defaults want 60 of them. That is why chapter 11's -pooler hostname exists, and it should now read as a consequence rather than a feature.

Where it stops being stock Postgres

Neon supports Postgres 14 through 18 and behaves like Postgres almost everywhere. The exceptions are all the split showing through:

• No superuser. "you cannot access the host operating system, and you can't connect using the Postgres superuser account. In place of the Postgres superuser role, Neon provides a neon_superuser role."
• Unlogged tables are not durable. The sharpest one: an unlogged table skips the WAL, which on Neon means it skips the thing that reaches storage at all. "In Neon, these tables are stored on compute local storage and are not persisted across compute restarts or when a compute scales to zero. This is unlike standard Postgres, where unlogged tables are only truncated in the event of abnormal process termination." On ordinary Postgres an unlogged table survives a clean restart. On Neon it does not survive a quiet afternoon.

Three smaller ones follow from the same removal and can be taken together: there are no tablespaces, because a tablespace names a filesystem location and there is no filesystem to name; most Postgres parameters are not user-configurable, because there is no postgresql.conf to edit; and session state ends when the session does, which is all of chapter 11's transaction-pooling casualties with scale-to-zero as a second way to die.

One commercial fact has real operational teeth, because it is the one you cannot undo: a project's region is fixed at creation — "You cannot change the region for an existing project… you create a new Neon project in that region and migrate your database there." Pick it next to whatever will be calling it, and remember that egress is metered, so a chatty service in the wrong region is billed for its chattiness.

The through-line: Neon did not add features to Postgres. It removed the assumption that storage is local, and branches, instant restore, scale-to-zero, the pooled hostname, the non-durable unlogged tables and the resetting statistics are all that one removal seen from different angles.

A cache is a second copy

You deployed a fix an hour ago. Some users still see the old behaviour. You cannot reproduce it, because your own copy is correct — the worst possible position to debug from.

Nothing is broken. A cache did exactly what you told it. And the reason this is hard has nothing to do with speed.

A cache is a second copy of a truth that lives somewhere else. Everything difficult in the next four chapters follows from that rather than from performance. The moment there are two copies of anything, there is a question about whether they agree, and no free way to answer it.

So give up the idea that staleness is a bug. Staleness is a permanent condition of having a second copy. The only thing you choose is the bound: how wrong are you willing to be, and for how long? That is a product question. Answer it as a purely technical one and you will answer it badly.

The only two moves a cache can make

Given a stored copy and a request for it, a cache has exactly two options.

Freshness — I may use my copy without asking. The origin said in advance how long the copy could be trusted, and that time has not run out. Zero network cost, and the risk that the origin changed its mind.

Validation — I must ask, but cheaply. The cache still holds the bytes and asks the origin "is what I have still current?", hoping for a small "yes" rather than the whole resource.

Everything in this part is one of those two in local clothes. A Redis TTL is freshness. An ETag round trip is validation. stale-while-revalidate is a rule about which to do when. HTTP is where both are written down normatively, so learn the vocabulary here and reuse it.

RFC 9111, the current HTTP caching specification, defines them precisely:

A fresh response is one whose age has not yet exceeded its freshness lifetime. Conversely, a stale response is one where it has. A response's freshness lifetime is the length of time between its generation by the origin server and its expiration time. A response's age is the time that has passed since it was generated by, or successfully validated with, the origin server.

That last clause repays a second read. A successful validation resets the age, so a cache that can validate never has to guess a lifetime and can hold a copy indefinitely without serving anything wrong.

Freshness: the ladder, and the three directives people confuse

How long is a response fresh for? A cache evaluates a list and takes the first match:

If the cache is shared and the s-maxage response directive is present, use its value, or If the max-age response directive is present, use its value, or If the Expires response header field is present, use its value minus the value of the Date response header field … or Otherwise, no explicit expiration time is present in the response.

max-age is the ordinary one: "the response is to be considered stale after its age is greater than the specified number of seconds" — seconds from generation, not from when your cache received it.

Now the pair that gets swapped, behaving rather than asserted, through a shared cache implementing enough of RFC 9111 to make them do what they say. Two identical requests each time:

    max-age: an ordinary cacheable response
      Cache-Control: max-age=60
      request 1: FETCHED AND STORED
      request 2: HIT   (age 0.00s < lifetime 60.0s)
      origin was hit 1x
    no-store: never write it down at all
      Cache-Control: no-store
      request 1: FETCHED, NOT STORED (no-store)
      request 2: FETCHED, NOT STORED (no-store)
      origin was hit 2x
    no-cache: DO store it, but revalidate every time
      Cache-Control: no-cache
      request 1: FETCHED AND STORED
      request 2: REVALIDATE (no-cache: stored, but never used without asking)
      origin was hit 1x
    private: a browser may store it, a CDN may not
      Cache-Control: private, max-age=60
      request 1: FETCHED, NOT STORED (private, and I am a shared cache)
      request 2: FETCHED, NOT STORED (private, and I am a shared cache)
      origin was hit 2x
    s-maxage overrides max-age for shared caches
      Cache-Control: max-age=0, s-maxage=60
      request 1: FETCHED AND STORED
      request 2: HIT   (age 0.00s < lifetime 60.0s)
      origin was hit 1x

no-cache does not mean "do not cache". It means store it and never use it without asking first: "the response MUST NOT be used to satisfy any other request without forwarding it for validation and receiving a successful response." That is a performance feature, because a successful validation returns no body.

no-store means do not write it down: "a cache MUST NOT store any part of either the immediate request or the response" — followed by the sentence worth quoting to anyone reaching for it as a security control: "This directive is not a reliable or sufficient mechanism for ensuring privacy."

So no-cache is about use, no-store about storage. Backwards, and you either pay full price for a resource you could have revalidated for nothing, or leave a private response in a CDN.

private is about which cache: "a shared cache MUST NOT store the response… It also indicates that a private cache MAY store the response." s-maxage overrides max-age for shared caches only, which is why the same max-age=0, s-maxage=60 response was a hit through a shared cache and a miss through a private one:

--- the same s-maxage response through a PRIVATE cache ---
      request 1: FETCHED AND STORED
      request 2: MISS  (stale: age 0.00s >= lifetime 0.0s)

One response, two behaviours, decided by what kind of cache reads it. And "A shared cache MUST NOT reuse a stale response with s-maxage to satisfy another request until it has been successfully validated by the origin" — s-maxage quietly implies proxy-revalidate.

Validation, over the wire

Freshness is a promise made in advance; validation is a question asked at the time, and it needs a validator — a short token identifying which version the cache holds. An ETag.

Here is a real one, with curl:

HTTP/2 200
cache-control: max-age=300
etag: "ca494a80aa7c6699e759137bd7450aca8915a7fd49b4de06c3ac77f19b065a67"
vary: Authorization,Accept-Encoding
content-length: 1487
bytes downloaded: 1487

Send that token back in an If-None-Match header, which is what a cache does when its copy goes stale:

HTTP/2 304
date: Wed, 26 Aug 2026 03:26:21 GMT
cache-control: max-age=300
etag: "ca494a80aa7c6699e759137bd7450aca8915a7fd49b4de06c3ac77f19b065a67"
bytes downloaded: 0

Zero bytes, and the request was not wasted: "A 304 (Not Modified) response status code indicates that the stored response can be updated and reused", with the cache required to "update its header fields with the header fields provided in the 304". The max-age=300 in that 304 restarts the clock — the copy is fresh for another five minutes without a byte of content crossing the wire.

That is why a 304 is not "a small 200". It is a freshness reset, and it is how a cache stays useful indefinitely without anyone guessing a lifetime. Lie about the validator and you get the resource: If-None-Match: "not-the-real-etag" returns HTTP/2 200, 1487 bytes.

Three things fall out of that capture free. The ETag is visibly a content hash — 64 hex characters, a SHA-256 — so it changes exactly when the bytes do. No W/ prefix makes it a strong validator: byte-for-byte identical, not merely equivalent. And vary: Authorization,Accept-Encoding is a real CDN saying its cache key includes who is asking and what compression they accept. Vary multiplies your cache — each distinct combination of nominated headers is a separate entry, and "A stored response with a Vary header field value containing a member '' always fails to match."*

    content-length: 39
    content-type: application/json
    caching-relevant headers present: NONE

The third option, and the word you were unsure about

Freshness and validation look like a complete pair, and there is a gap between them. When a copy goes stale, the next request pays for the revalidation — and under load, everybody arriving in that window waits.

RFC 5861 adds an out:

When present in an HTTP response, the stale-while-revalidate Cache-Control extension indicates that caches MAY serve the response in which it appears after it becomes stale, up to the indicated number of seconds. If a cached response is served stale due to the presence of this extension, the cache SHOULD attempt to revalidate it while still serving stale responses (i.e., without blocking).

And its worked example, the clearest statement of the trade:

A response containing:

Cache-Control: max-age=600, stale-while-revalidate=30

indicates that it is fresh for 600 seconds, and it may continue to be served stale for up to an additional 30 seconds while an asynchronous validation is attempted. If validation is inconclusive, or if there is not traffic that triggers it, after 30 seconds the stale-while-revalidate function will cease to operate, and the cached response will be "truly" stale (i.e., the next request will block and be handled normally).

So — this is the sentence to keep — stale-while-revalidate does not make anything fresher. It moves the cost of getting fresh off the request that noticed. Serve the wrong answer now, get the right one for next time. You are not reducing staleness; you are choosing to serve more of it so that no user waits for a refresh.

The RFC is honest about the size of the bargain: "with both set to 600, the server must be able to tolerate the response being served from cache for up to 20 minutes." Twenty, from two numbers that each read as ten. And about when it does nothing: "asynchronous validation will only happen if a request occurs after the response has become stale, but before the end of the stale-while-revalidate window… If the window is too small, or traffic is too sparse, some requests will fall outside of it, and block." It also cannot help a cache with nothing in it, since there is no stale copy to serve. Chapter 21 measures exactly that.

Its sibling stale-if-error covers the other bad day: "when an error is encountered, a cached stale response MAY be used to satisfy the request, regardless of other freshness information", where an error means a 500, 502, 503 or 504.

The sentence that sets up the rest of this part

RFC 9111 has a rule for keeping caches honest: "A cache MUST invalidate the target URI when it receives a non-error status code in response to an unsafe request method." A successful POST or DELETE invalidates the cached GET for that URI. Sensible, automatic, free.

Then it adds this:

Note that this does not guarantee that all appropriate responses are invalidated globally; a state-changing request would only invalidate responses in the caches it travels through.

Read that as a general law rather than an HTTP footnote. A write can only invalidate the copies it passes. Copies in caches it did not touch — a browser on the other side of the world, a CDN edge in a region nobody wrote to, the second copy of your application process holding an in-memory dict — do not know anything happened.

That is the coherence problem, written into the specification by people who could not solve it either. It is why the next three chapters exist, and why "just delete the key when the data changes" turns out to be the hard half rather than the easy one.

Redis is one command loop, and keys vanish

Chapter 2 left a result waiting for this chapter. Four uvicorn workers, an in-process dictionary used as a cache, set once and read eight times:

{"pid":2334,"set":true}
{"pid":2334,"has_key":true}
{"pid":2333,"has_key":false}      <-- a cache miss that is not a cache miss
{"pid":2334,"has_key":true}

That one false is the entire argument for a cache outside your process. Four workers is four heaps; the other three know nothing about what worker 2334 computed, and no amount of care in your Python fixes it, because they are separate operating-system processes.

So you need one copy of the cache, shared, that all four can reach. That is what Redis is for.

What a key-value store is

Strip the marketing and Redis is a dictionary living in another process, reached over a socket. You give it a key — a byte string — and it gives you a value. No schema, no query language, no join, no planner, no WHERE. You cannot ask "which keys have plan = pro", because Redis does not know your values have fields.

That sounds like a severe limitation and it is the point. Chapter 13 was about a planner making decisions under uncertainty; here there are no decisions. Every operation is "go to this key". The lookup does not slow down as the keyspace grows, and nothing about the machine's state changes what a command costs.

The trade is that you must know the key. Chapter 20 is entirely about what follows from that.

One command at a time

Now the fact everything rests on. Fifty concurrent clients, each incrementing a counter twenty times — a thousand increments, done the way you would write it anywhere: read, add one, write back.

expected           : 1000
GET/+1/SET         : 20   lost updates: 980
INCR               : 1000   lost updates: 0

Ninety-eight per cent of the increments vanished. This is chapter 12's lost update in a different system with an identical mechanism: fifty clients read 7, fifty clients write 8.

The second line used one command instead of three. Not a faster command, not a locked command — one command. And it lost nothing.

The reason is one sentence long: Redis executes one command at a time, start to finish, on a single thread. A loop takes the next command off a queue, runs it to completion, takes the next. There is no moment at which two commands are half-done, so every single command is atomic for free, without a lock anywhere — because a lock's job is to stop two things happening at once, and here two things cannot happen at once.

That also gives you the shape of every bug in this area. INCR is safe; GET then SET is not, and no care makes it so, because the gap between two commands is where every other client's commands run. Atomicity in Redis is per command, and a logical operation spread over two commands has no protection whatsoever.

Three things that are not the same thing

Because atomicity is per command, "do several things together" needs care, and three mechanisms get used interchangeably that should not.

A pipeline sends many commands without waiting for each reply. A thousand RPUSHes: 111.5 ms one at a time, 13.0 ms pipelined — nine times faster, having removed no work at all, only 999 round trips. A pipeline is a network optimisation with no isolation. It looks atomic in a small test, which is how people get this wrong: Redis reads up to 16 KB from a client at a time and drains that buffer before servicing anyone else, so a small pipeline runs in one go by accident. Push past the buffer:

plain pipeline: other client's writes interleaved inside the batch = 45
MULTI/EXEC   : other client's writes interleaved inside the batch = 0

MULTI/EXEC queues commands and runs the whole queue in one slot of the command loop. Real isolation. What it cannot do is decide: nothing inside can see a result before EXEC runs, so "read a value and write something different depending on it" is impossible. (WATCH adds "abort if this key changed", which is a check, not a branch.)

A Lua script runs on the server, in one slot, and can branch on what it reads:

[3] buy 2 of 3 -> remaining 1
[3] buy 2 of 1 -> remaining -1  (-1 = refused)
[3] GET stock  -> 1

GET, compare, conditionally DECRBY, all inside one slot. Any read-compare-write you need is a Lua script, and chapters 20 and 21 both use one.

Five data structures, each for a problem

A value is not always a blob. Learn the types by the question each answers.

String — a byte blob up to 512 MB. Your serialized cache entry, and your counters, since INCR works on it. SET w2:str '{"id":7,"name":"Ada"}' EX 300 → OK, STRLEN → 21.

Hash — a map inside one key, for an object whose fields are read and written separately:

HSET w2:hash id 7 name Ada plan pro seats 12  -> 4
HGET w2:hash plan     -> pro
HINCRBY w2:hash seats 3 -> 15

That HINCRBY is INCR again: atomic, on one field, without reading the object.

List — ordered, duplicates allowed, push and pop at both ends. A queue, or a "most recent N" feed.

RPUSH w2:feed a b c ; LPUSH w2:feed z   -> 3, 4
LRANGE w2:feed 0 -1                     -> ['z', 'a', 'b', 'c']
LPOP w2:feed                            -> z       (a queue: push right, pop left)
LTRIM w2:feed 0 1                       -> ['a', 'b']   (a bounded "most recent N")
LLEN w2:feed                            -> 2

LTRIM is the one worth remembering: it is how a feed stays a fixed size without anybody scanning it for old entries. Push on one end, trim the other, and the structure bounds itself.

Set — unordered, unique, with membership tests and set algebra on the server:

SADD w2:setA alice bob carol ;  SADD w2:setB bob carol dave
SINTER w2:setA w2:setB -> carol, bob        SDIFF w2:setA w2:setB -> alice

"Who has seen this", tags, "which of these users are also in that group" — computed where the data is rather than by shipping two lists to Python.

Sorted set — unique members, each with a floating-point score, kept in score order. Worth dwelling on, because it solves problems that look unrelated:

ZADD w2:board 1200 alice 980 bob 1550 carol 1100 dave -> 4
ZREVRANGE w2:board 0 2 WITHSCORES -> carol 1550, alice 1200, dave 1100
bob's rank: 3
ZINCRBY 400 bob -> 1380
bob's rank now: 1

One command moved bob from third to first and kept the ordering correct, and ZREVRANK answers "what position am I" without scanning. That is a leaderboard.

Score by timestamp instead and the same structure is a sliding-window rate limiter: drop what is older than the window with ZREMRANGEBYSCORE, count with ZCARD, add with ZADD — three commands, so wrap them in Lua.

  8 requests, limit 5 per 1.0s : ['allow','allow','allow','allow','allow','DENY','DENY','DENY']
  after the window slides forward     : allow, 1 in window

A plain INCR counter cannot do this, because it cannot forget requests that have aged out, which is why counter-based limits let a burst through at every window boundary.

Keys vanish. Two different ways.

Everything from here is a premise for the next two chapters: a key can disappear at a moment you did not choose.

The first way you asked for. TTL is a per-key expiry, and reading it back gives two different negative numbers — -1 is "exists, immortal", -2 is "not here".

SET ... EX 300, TTL: 300
after a plain SET, TTL: -1
after SET ... KEEPTTL, TTL: 300

And expiry is not an event. It is a sweep. Two hundred thousand keys written with EX 2, then DBSIZE sampled without touching any of them:

  t (s)    DBSIZE    expired_keys since start
   0.00    199916                          92
   1.01    104915                       95908
   2.03     14272                      185736
   2.28      2948                      197060
   2.53         8                      200000

At t = 2.28 seconds, nearly three thousand logically dead keys still occupied the keyspace. Redis's config explains the design: "Redis reclaims expired keys in two ways: upon access when those keys are found to be expired, and also in background, in what is called the 'active expire key'. … The default effort of the expire cycle will try to avoid having more than ten percent of expired keys still in memory."

Ten percent — a stated target, not an accident. The consequence: a TTL bounds staleness, not memory.

The second way, and the default that surprises people

Memory is bounded by maxmemory and an eviction policy — the rule for what to throw away at the limit. Redis ships eight:

volatile-lru -> Evict using approximated LRU, only keys with an expire set. allkeys-lru -> Evict any key using approximated LRU. volatile-lfu -> Evict using approximated LFU, only keys with an expire set. allkeys-lfu -> Evict any key using approximated LFU. volatile-random -> Remove a random key having an expire set. allkeys-random -> Remove a random key, any key. volatile-ttl -> Remove the key with the nearest expire time (minor TTL) noeviction -> Don't evict anything, just return an error on write operations.

The default is the last one. Set maxmemory to nine megabytes and write four thousand four-kilobyte keys:

policy=noeviction       SET accepted 2016/4000  refused 1984  evicted 0     alive 2016
   first error: "command not allowed when used memory > 'maxmemory'."
policy=allkeys-lru      SET accepted 4000/4000  refused 0     evicted 1984  alive 2016
policy=volatile-lru     SET accepted 2016/4000  refused 1984  evicted 0     <- keys have NO ttl
policy=volatile-lru     SET accepted 4000/4000  refused 0     evicted 2004  <- same policy, keys DO have a ttl
policy=volatile-ttl     SET accepted 4000/4000  refused 0     evicted 1999  lowest survivor d:ev:00149
policy=allkeys-lfu      SET accepted 4000/4000  refused 0     evicted 1980  hot key survived? True

Three findings, in order of trouble caused.

Redis installed as "the cache" does not evict. It refuses writes — 1,984 of 4,000 — with OOM command not allowed when used memory > 'maxmemory'. Your application throws on the write path of a cache, the last place anyone looks.

volatile-* with no TTLs anywhere is noeviction under another name. Look at the two volatile-lru rows: identical policy, and the one whose keys had no expiry refused exactly as many writes as noeviction. The config says why — "when there are no suitable keys for eviction, Redis will return an error on write operations that require more memory."

Redis does not implement LRU. It implements a sampled approximation. "Both LRU, LFU and volatile-ttl are implemented using approximated randomized algorithms… By default Redis will check five keys and pick the one that was used least recently." Five samples out of two thousand candidates; in the run above the "oldest" key survived allkeys-lru despite never being read again. Never say Redis's LRU without the word "approximated".

And it is not a database

The live sandbox instance:

save        -> ""      (snapshotting disabled)
appendonly  -> no
rdb_changes_since_last_save: 309447

Three hundred thousand accepted writes, and no dump.rdb in its working directory. Everything in that instance dies with the process.

Perfectly reasonable for a cache, catastrophic for anything else. Even fully configured the numbers are modest: the default snapshot rule saves "After 3600 seconds (an hour) if at least 1 change was performed", and the append-only log's default everysec means Redis "can lose just one second of writes in a dramatic event like a server power outage." appendfsync always closes that gap at a cost the same file calls "Slow".

So: if a piece of data exists only in Redis, you have chosen to be able to lose it. For a cache that is correct, because a cache is a second copy and the first copy is in Postgres. It stops being correct the moment somebody stores something in Redis that is not also somewhere else.

Between eviction, expiry and a process that can lose everything, a key you wrote is a key that might not be there. The next two chapters are about building things that are correct anyway.

Invalidation is the hard half

Why is there a TTL on your cache entry?

Not "to bound memory" — chapter 19 showed TTLs do not bound memory; maxmemory does. Not "because the data expires", because most data does not expire, it changes. The real answer is:

A TTL is a confession that you do not trust yourself to invalidate correctly. It says: I cannot guarantee I will notice every write that makes this entry wrong, so I am putting an upper bound on how long I will be wrong for. Five minutes is not a property of the data. It is the amount of wrongness you decided to accept in exchange for not solving the problem.

That is a defensible decision. It is also worth knowing precisely what you are declining to solve.

Chapter 18 said a cache has exactly two moves — freshness, where it may use its copy without asking, and validation, where it must ask but cheaply. Hold that up against Redis and something is missing. A Redis TTL is freshness, and Redis has no validation primitive at all. There is no ETag on a key, no way to ask "is what I hold still current" for less than the cost of fetching it again. HTTP's second move is not available to you.

So everything in this chapter is an attempt to build validation out of parts. Explicit deletion is the writer trying to do it on the reader's behalf. Key versioning, which is where this ends up, is validation with the question inverted — instead of asking whether a copy is current, you make a stale copy unreachable by changing its address. And CLIENT TRACKING, at the end of the chapter, is Redis pushing the invalidation at you rather than waiting to be asked, which is the one place the missing move comes back.

The vocabulary, and which one you are actually building

Five patterns get named here. There is no specification defining them — shared industry vocabulary, not a standard — so treat the names as a way of asking "who talks to the database?" and "what happens on a write?".

Four of the five need a caching framework between you and the database. Cache-aside is the one you get without adopting anything, because it is three lines you already know how to write: look in the cache; on a miss, query; store what you found. Almost everybody is building cache-aside, including the people who think they are building something else.

So its failure mode is the one that matters.

The exact race

A reader and a writer, interleaved deliberately so timing is not left to luck. Real Postgres, real Redis. The writer does everything correctly: it updates the database first and invalidates afterwards.

  R1 reader: cache lookup -> None (MISS)
  R2 reader: SELECT from postgres -> 9.99
  W1 writer: UPDATE postgres -> 19.99
  W2 writer: DEL cache key (invalidate)
  R3 reader: SET cache = 9.99 (the fill it decided on earlier)

  postgres now holds : 19.99
  redis now holds    : 9.99
  and will keep it for another 300 seconds
  WRONG: True

Walk the five lines. The reader missed and went to the database, which then held 9.99. The writer changed it to 19.99 and deleted the cache key — correct order, no mistake. Then the reader, still holding a value fetched before any of that, wrote it into the slot the writer had just cleared.

The cache now holds a price that is wrong, that no subsequent write will fix, and that persists for the full TTL. The next writer will delete a key and the same thing can happen again.

Three things are worth being precise about. The window is small — the gap between a reader's SELECT returning and its SET landing, a millisecond or two. Every cache miss enters it, which is what turns a small window into a certainty; at any real request rate, misses are constant. And nothing in the code is wrong. There is no line to fix, because you cannot order two independent processes' operations by writing better code in one of them.

Ordering: it matters, and it does not save you

If you must delete a key, when? Test it, with the writer holding a transaction open for 100 ms as a real UPDATE does, and a reader arriving mid-write:

  DEL cache, then UPDATE     -> db=19.99  cache='9.9900000000000002131628207280300557613372802734375'  STALE
  UPDATE, then DEL cache     -> db=19.99  cache=None  ok

(That long number is asyncpg rendering PostgreSQL's numeric through a float. It is 9.99, and not a caching artefact.)

Delete-first is worse, and chapter 12 is why. A reader arriving during the writer's transaction sees the pre-commit value — MVCC doing exactly what it should — so the window in which a reader can refill the cache with the old value is the entire duration of the write transaction. Delete-after leaves only the much smaller window in which a reader has already read the new value and not yet stored it, and that reader is storing something correct.

So: write first, then invalidate. It does not close the hole. It makes it as small as it can be, which is worth doing and is not a solution.

The trick: make invalidation a write

Here is the observation that turns the problem around.

A delete is a negative operation: a request that a piece of state cease to exist. It races against every positive operation trying to create that state, and the loser is whoever arrives second. That is structural — the shape of the operation, not a bug in its implementation.

So stop deleting. Bump a version instead. Keep a generation counter in Redis, one per entity, and build the cache key out of the entity id and that generation. A reader reads the generation, then the database, then fills product:1:v1. A writer, instead of deleting anything, runs INCR on the generation.

Same forced interleaving as before, twice:

  delete the key             -> lookup key 'd:ver:product:1'
                                db=19.99  cache='9.99…'  STALE
  key versioning             -> lookup key 'd:ver:product:1:v2'
                                db=19.99  cache=None  ok (miss -> refetch)

Read what happened in the second case. The stale reader's SET still landed — nothing stopped it, nothing could have. It landed on product:1:v1, and everybody is now looking at product:1:v2, which does not exist, so they refetch and get the right answer.

The race did not go away. It stopped mattering, because the loser writes to an address nobody will ask for again.

Versioning converts invalidation from a delete into a monotonic write. A monotonic write does not race: there is no interleaving of INCR and anything else that produces a wrong generation, because chapter 19 established INCR is atomic. Old versions are garbage that collects itself, by their own TTLs.

The costs, plainly:

• An extra round trip per read, to fetch the generation. Pipeline it with the data read, or cache the generation in-process with a very short TTL — trading bounded staleness on the generation for the round trip.
• Memory held by orphaned versions until their TTLs run out.
• You can no longer enumerate the cache. There is no "the key for product 1" any more, which makes debugging harder and a bulk purge impossible without bumping everything.
• The generation counter is now the single point of failure, and chapter 19 already told you why that is a problem.

That last one deserves more than a bullet, because it is the objection this whole part has been building toward. Chapter 19 closed on it: between eviction, expiry and a process that can lose everything, a key you wrote is a key that might not be there. The generation counter is a key.

Watch what happens when it is the one that goes:

cached at generation 1: ('1', '9.99  (the old price)')
after INCR, readers see: ('2', '19.99 (the new price)')

generation key gone -> falls back to generation 1
reader now sees     : 9.99  (the old price)
but v2 still holds  : 19.99 (the new price)   ttl 3600s

and a later write INCRs from nothing -> generation 1, not 3

The counter is evicted. Readers apply the obvious default — a missing generation means 1 — and land back on product:1:v1, which is still sitting in the keyspace with fifty-nine minutes left on its TTL. The scheme designed to make stale entries unreachable has just made a stale entry the only reachable one, and it will stay that way for the rest of that TTL. Worse, it does not heal: the next write INCRs from nothing back to 1, so the generation does not climb past the versions that are already cached.

Three ways out, in increasing order of how much you have to care:

1. Make the counter unevictable. Give it no TTL and put it in a Redis instance with maxmemory-policy noeviction, or on a separate instance from the cached values. This is the honest answer for most applications, and it converts the problem into "do not run your counters in the same keyspace as your disposable data".
2. Make the fallback fail closed. A missing generation should mean cache miss, not generation 1. One line, and it turns a silent stale-forever into an ordinary extra database read.
3. Make the generation not need storing. Derive it from something the database already holds — the row's updated_at, which chapter 12 established is written on every update anyway. The key becomes product:1:1756190400, no counter exists to lose, and the database is the only source of truth. It costs you the row read you were trying to avoid, which is why it suits a cache in front of something expensive downstream of the row rather than the row itself.

None of those is free, and the point is not to pick one here. It is that "just bump a version" is a complete answer only if you also say where the version lives — and a cache is the worst available place to keep the one piece of state the cache's correctness depends on.

Tag-based invalidation is the same idea one level up: give each entry a set of tags — org:42, plan:pro — keep a generation per tag, include it in the key, and one INCR invalidates every cached page for that organisation. Useful when one write invalidates many entries, which a per-entity version cannot express.

The other direction: Redis can invalidate you

Everything above assumes the writer must find the caches. Redis 6 inverts it, and it is worth seeing because it makes revalidation concrete in a way prose does not.

When client-side caching is enabled, the Redis server remembers or tracks the set of keys that each client connection has previously read. … When any client writes new data to a tracked key, the server sends an invalidation message to all clients that have accessed that key previously.

On a raw socket, with no client library interpreting anything:

CLIENT TRACKING ON -> b'+OK\r\n'
GET  -> b'$2\r\nv1\r\n'    (this read is now TRACKED)

local in-process cache holds: {'d:ct:product:1': 'v1'}
a different client wrote v2. Nothing was asked of this socket.
bytes that arrived unbidden on the tracking socket:
    b'>2\r\n$10\r\ninvalidate\r\n*1\r\n$14\r\nd:ct:product:1\r\n'
local in-process cache after handling it: {}

The > is RESP3's push type: a message from the server nobody requested, naming a key and saying "forget it". No polling, no TTL, no version counter.

This is how chapter 2's per-worker in-process dictionary becomes safe — a first-level cache in front of Redis, invalidated by Redis rather than guessed at. The catch is in the documentation too: "If any connection from a client gets disconnected (including one from a connection pool), then the client will flush all keys from the client-side cache." Given chapter 11's pool_recycle and pool_pre_ping, that happens more often than you think.

Which to reach for

Nothing here is free, so the decision is about what you are willing to be wrong about:

• TTL only. Simplest, bounded wrongness. Correct wherever "up to N seconds old" is an acceptable product answer, which is more things than people admit.
• TTL plus write-then-delete. Usually right, and honest about its own race.
• Key versioning. When a stale entry is genuinely unacceptable, and worth the extra round trip and memory.
• Tags. When one write invalidates a family of entries.
• Client tracking. When you want a per-process cache in front of the shared one and can tolerate a full local flush on every reconnect.

Notice what is not on that list: a cheap way to be certain every copy agrees. Chapter 18 quoted RFC 9111 conceding the same point — "a state-changing request would only invalidate responses in the caches it travels through." The specification authors did not solve it either.

Stampede, and why revalidating is not a latency fix

A cache entry expires. Two hundred requests arrive for it in the next fifty milliseconds. How many reach the database?

The intuitive answer is one — the first refills the cache, the other 199 hit it. That describes a world where requests arrive one at a time and wait politely.

A. cold key — nothing has ever been cached
  cache-aside (naive)   db calls: 200/200   wall 102.7 ms   distinct results: 200

B. key populated, then expired — the production case
  cache-aside (naive)   db calls: 200/200   wall 114.2 ms   distinct results: 200

All of them. The first request has not finished its query when the two-hundredth checks the cache, so all two hundred see a miss and all two hundred go to the database. The cache did not warm up. It stampeded.

"The cache will warm up" describes a sequential world. Under concurrency an empty cache is an amplifier: the more traffic you have, the more requests pile into the window, and the window is exactly as long as one database query.

And look at the third column. distinct results: 200 — not two hundred wasted reads but two hundred different reads, racing to write the same cache slot. Whichever lands last wins. That is a correctness observation, and it is chapter 20's race running two hundred at a time.

Single-flight: let one go, hold the rest

Make the 199 wait for the one. Take a lock, atomically, using chapter 19's SET … NX:

SET stampede:lock <token> NX PX 5000

NX means "only if it does not exist", and because Redis runs one command at a time, exactly one of two hundred concurrent clients gets OK. That one queries and publishes; the other 199 poll the cache key with a bounded wait.

A. cold key
  cache-aside (naive)        db calls: 200/200   wall 102.7 ms
  single-flight lock         db calls:   1/200   wall 128.3 ms   distinct results: 1

B. key populated, then expired
  cache-aside (naive)        db calls: 200/200   wall 114.2 ms
  single-flight lock         db calls:   1/200   wall 195.5 ms   distinct results: 1

1 of 200. Complete protection in both cases, and distinct results: 1 means the correctness problem went with it.

Now read the other column, because this is the part that gets left out.

Wall time got worse. 128 ms against 103 on a cold key; 196 against 114 in the production case. The naive version had two hundred queries running in parallel, so the slowest request waited for one query. The single-flight version has 199 requests waiting on one query plus the polling interval that tells them it is done.

That is not a bug in the implementation. It is what the technique does. Single-flight converts load into latency, taking two hundred units of database work and turning them into one unit of work and two hundred waits. If your problem was "my database falls over when a hot key expires", that is exactly the trade you want. If your problem was "this endpoint is slow", you have made it slower.

So: stampede protection and latency protection are different problems, and single-flight only solves one of them.

The one that does both, and its condition

Chapter 18's stale-while-revalidate is the other tool: serve the stale value immediately to everybody, refresh in the background. Same harness, third row:

B. key populated, then expired — the production case
  serve-stale + revalidate   key in redis at t0: True   db calls: 1/200   wall  53.6 ms

One database call and 53.6 ms — under half of everything else, because nobody waited for the database at all. Then case A:

A. cold key — nothing has ever been cached
  serve-stale + revalidate   key in redis at t0: False  db calls: 200/200  wall 123.2 ms

Back to 200 of 200. stale-while-revalidate cannot help a cache with nothing in it, because the mechanism is "serve the old value while fetching the new one" and there is no old value. RFC 5861's own admission, measured: "asynchronous validation will only happen if a request occurs after the response has become stale, but before the end of the stale-while-revalidate window."

Which gives you the actual design, and it is two things rather than one:

• Serve-stale handles the ordinary case — a popular key whose entry has just aged out — removing both the load and the latency.
• Single-flight handles the cold case — a deploy, a flush, an eviction, a new key — where there is nothing stale to serve and something has to protect the database.

The implementation shape that follows: store the value with a logical expiry inside it, and give the Redis key a TTL comfortably longer. Past the logical expiry, serve what you have and try to take the lock; if you get it, refresh. Past the Redis TTL there is nothing, and the lock is the only thing between you and two hundred queries.

Penetration: the requests that never had a cache to miss

A different failure with the same symptom. Your cache-aside code almost certainly contains this line:

if row is not None:
    cache.set(key, row, ex=300)

It caches hits. It does not cache the absence of a row, because caching None feels wrong.

  20 requests for a row that does not exist, no negative cache: 20 database reads
  20 requests, same row, WITH a negative cache entry:           1 database reads

Every request for a nonexistent id costs a full round trip, forever, because a miss can never become a hit. Normally that is a slow trickle of 404s. It becomes an incident when somebody enumerates ids: every guess walks past your cache into your database, and the cache provides no protection precisely because none of the guesses exist.

Cache the absence. Store a tombstone — distinguishable from a real value and from "no entry" — with a shorter TTL than a hit, because the row may be created a moment later and you do not want to serve "not found" for five minutes afterwards. Thirty seconds removes the attack and bounds the wrongness. (When the id space is large and sparse, a Bloom filter in front is the other standard answer: a small probabilistic structure answering "definitely not present" or "maybe present", so definite absences never reach anything.)

Hot keys, and why you cannot buy your way out

The last failure cannot be fixed with more machines. Redis Cluster distributes keys by hashing them into slots:

Each master node in a cluster handles a subset of the 16384 hash slots. … The base algorithm used to map keys to hash slots is the following:

HASH_SLOT = CRC16(key) mod 16384

A key maps to exactly one slot; a slot is served by exactly one node. Every request for a given key lands on the same machine, and adding nodes changes nothing. A key with a hundred thousand reads a second is a single-node problem, permanently, by construction.

That makes hot keys their own failure class rather than a variant of "too much load", and constrains the fixes to two shapes, both of which change the key:

• Shard it. Store the same value under key:0 … key:N, write all of them, and have readers pick one at random. Load spreads across N slots, paid for with N times the memory and an invalidation that must touch N entries — chapter 20's version bump handles that neatly.
• Move it out of Redis. Cache it in the application process, so each worker serves its own copy and Redis never sees the read. Chapter 20's CLIENT TRACKING is what makes that safe rather than a guess.

Notice the shape of all three failures here. A stampede is many requests for a key that is not there. Penetration is many requests for a key that can never be there. A hot key is many requests for a key that is very much there. Each is "concurrency meets one key", and each needs a different tool — which is the actual lesson, and the reason "add a cache" is a design decision rather than a configuration change.

Work that outlives the request

A user signs up. You create the account and send a welcome email, inside the handler, before returning a response.

Consider what you have coupled. The signup now takes as long as your mail provider does. If the provider is slow, your handler holds a worker slot for the duration — and chapter 10's arithmetic says that slot is also holding a database connection. If the provider returns 500, your user sees a failed signup for an account that exists. If the process is killed between the COMMIT and the send, the email never happens and nothing knows.

None of that is about email. It is about a handler taking on work whose success is not the same thing as the request's success, and whose duration is not under your control. A request handler should do everything the client is waiting to hear about, and nothing more.

Getting the rest out of the handler is easy. Getting it out correctly is this chapter.

What BackgroundTasks is, exactly

FastAPI has an obvious-looking answer. fastapi/background.py is a subclass of Starlette's class with a docstring and no behaviour of its own, and Starlette's is short enough to read whole:

class BackgroundTask:
    async def __call__(self) -> None:
        if self.is_async:
            await self.func(*self.args, **self.kwargs)
        else:
            await run_in_threadpool(self.func, *self.args, **self.kwargs)

class BackgroundTasks(BackgroundTask):
    async def __call__(self) -> None:
        for task in self.tasks:
            await task()

And the call site, the last lines of Response.__call__:

        await send({"type": "http.response.start", "status": self.status_code, "headers": self.raw_headers})
        await send({"type": "http.response.body", "body": self.body})

        if self.background is not None:
            await self.background()

Everything follows from that await. The response bytes go out first — that part of the promise is real. Then the ASGI call awaits the tasks to completion before returning:

--- 1. how long does the ASGI call take? ---
  response 200 {'status': 'accepted'}
  the await on the request returned after 0.50s, for a 0.5s task

The client got its answer immediately; the server held the request for the full 500 ms. So BackgroundTasks moves work off the client's clock and not off the server's — the worker slot, the connection, the threadpool token if the task is a def, all held.

Two more properties from the same six lines. Tasks run sequentially, in order. And a raising task takes the rest with it:

--- 2. tasks run in order, and one failure kills the rest ---
  the exception escaped INTO THE SERVER: RuntimeError('boom')
    job-1 started  t=0.00s
    job-1 finished t=0.20s
    job-B raising

job-C never ran, and the exception surfaced after the client was told 200 accepted. There is nowhere for it to go: no client to inform, no status code left to change.

And what it does not survive: a process restart, a deploy, an OOM kill, a crash. Nothing is written down — no retry, no backoff, no dead-letter queue, no way to ask afterwards whether it ran.

So BackgroundTasks is correct for work that is cheap, idempotent, and whose loss is acceptable — an audit line, a cache bust, a metric. It is not a queue, and it must not be the thing that charges a card.

A queue, and the promise it can actually make

The real answer is a queue: a durable list of jobs written by your web process and read by a separate worker process. The web process writes "send welcome email to user 4812" and returns; the worker picks it up, possibly seconds later, possibly on another machine.

Three words come with it, and they are worth having before you need them. The broker is whatever actually holds the list — Redis, RabbitMQ, or a Postgres table; it is the queue's storage, and the thing that is down when the queue is down. A dead-letter queue is a second list where jobs go after failing too many times, so that one poisonous job stops blocking the line and stays visible instead of vanishing. And a partition is one of several independent lanes within a queue, where a job's key decides its lane — which matters later, because two jobs in the same lane cannot run at once, and that is a way to get ordering without a lock.

That buys a bounded request latency, a retry policy, somewhere for failures to be seen, and survival across a deploy. It costs a second process to run and monitor — and a guarantee you have to think about.

Every queue worth using delivers at least once. The worker takes a job, does the work, and tells the queue it is done. If it dies between the work and the acknowledgement — and it can, because those are two separate things — the queue redelivers. Your job runs twice.

You cannot buy your way out. This is not a limitation of a particular broker. Consider what "exactly once" would require: the effect and the acknowledgement of the effect committed atomically, across two systems that share no transaction. There is no such commit.

What you can have is at-least-once delivery plus an idempotent consumer, which produces exactly-once effect. The duplicate is not prevented; it is absorbed.

Idempotency keys

An idempotent operation is one you can perform repeatedly with the same result as performing it once. SET user.name = 'Ada' is naturally idempotent. Charging a card is not, so you make it so with a key the caller supplies:

  naive, retried request      : charges made = 2  total = 200
  idempotency key, sequential : charges made = 1  same charge_id returned = True
  idempotency key, CONCURRENT : charges made = 1  second response = {'error': '409 request already in flight'}

The construction is chapter 19's atomic primitive again — SET idem:<key> {"state":"in_progress"} NX EX 86400. If the SET succeeds you are first: do the work, then overwrite with {"state":"done","result": …}. If it fails, read the record and act on what it says.

Two subtleties, both demonstrated above. Recording "seen" is not enough; you must record the result. A retry has to receive the same answer, not merely avoid the second charge — a client that retries and gets "yes, fine" with no charge ID cannot reconcile anything. And there must be a third state: a concurrent retry arrives while the first attempt is still running, and there is no result to return because there is not one yet. The honest response is 409. Returning 200 with an empty body tells the client it succeeded and gives them nothing to identify it with.

The dual write, and the outbox

Now the deeper problem, which is not about queues at all.

Your handler creates an order in Postgres and enqueues a job in Redis. Two systems, two commit points, no shared transaction. Kill the process between them:

1. dual write (INSERT ... COMMIT; then LPUSH), crashing in between
   orders in postgres: 1   jobs in redis: 0

An order exists and nothing will ever process it. Reversing the order does not help — enqueue first, and a rolled-back transaction leaves a job referring to an order that does not exist. There is no sequence of two writes to two systems that is atomic, which is chapter 18's coherence problem one level down.

The transactional outbox refuses to do a dual write. Instead of enqueueing, insert a row into an outbox table in the same transaction as the order:

2. transactional outbox, same crash
   orders: 1   jobs in redis: 0   unpublished outbox rows: 1
   the relay comes along later:
   relay published 1   orders: 1   jobs in redis: 1   unpublished outbox rows: 0

3. and the transaction still rolls BOTH back on a failure before commit
   orders: 0   jobs in redis: 0   unpublished outbox rows: 0

The intent to enqueue is now data in the same transaction as the thing it describes, so PostgreSQL's atomicity covers both. A separate relay process reads committed, unpublished outbox rows and publishes them. The enqueue can now be late; it cannot be lost.

And then the honest part, which most explanations omit:

4. the honest limit: the relay itself is at-least-once
   crash: relay died after publishing but before marking published
   jobs in redis: 1   unpublished outbox rows still: 1
   relay restarts and publishes 1 more -> jobs in redis: 2

The relay is itself a dual write. Publishing the message and marking the row published are two operations in two systems. There is no bottom to that regress: whatever you use to fix the relay has the same shape.

Which is not a failure of the outbox. The outbox converted "the job may be lost" into "the job may be duplicated", and those are not equally bad — a lost job is silent and unrecoverable, a duplicate is absorbed by the idempotency work above. The outbox trades an unfixable problem for a fixable one. That is the whole of what it is for.

Distributed locks, last and least

Sooner or later two workers pick up jobs that must not run at once, and somebody reaches for a lock in Redis. It is the least reliable thing here and it goes last on purpose. Five attempts, each broken by the next.

SETNX then DEL. The holder crashes: TTL on the lock: -1. The resource is locked for the life of the server.

SETNX, then EXPIRE. Crash between the two commands: TTL on the lock: -1. Identical outcome, and chapter 19's lesson again — two commands are not one operation.

SET key value NX PX 5000 — one command, atomic, with a lease. Better. Now let the lease expire while the holder is still working:

  A acquired (lease 100 ms)
  B acquired (lease 5000 ms)
  A sends DEL -> removed 1 key(s)
  [t=350ms, B still working] lock key exists? False
  B sends DEL -> removed 0 key(s)

A's lease ran out mid-work; B legitimately took the lock; then A finished and sent DEL — and deleted B's lock, while B was inside its critical section. DEL has no idea whose lock it is deleting.

SET key <token> NX PX, released by a Lua compare-and-delete. Put a unique token in the value and check it before deleting:

  A acquired with token tok-A
  B acquired with token tok-B
  B release -> deleted 1 key(s)
  A release -> deleted 0 key(s)  (refused: not mine any more)

if redis.call('GET',KEYS[1]) == ARGV[1] then return redis.call('DEL',KEYS[1]) else return 0 end — compare and delete in one command-loop slot. This is the correct single-instance Redis lock, and what Redis's own documentation gives you.

And it is still not mutual exclusion.

  A ENTERS critical section; occupants now ['A']
  B ENTERS critical section; occupants now ['A', 'B']
  moments with two holders inside at once: 1 [['A', 'B']]

The token fixed the release; it did nothing about the overlap. A's lease expired while A was still working — a garbage-collection pause, a stalled disk, a slow syscall, any of which can outlast a lease — so for a real interval two processes were both inside.

No client-side fix exists, because the client cannot know its lease expired. Only the resource can protect itself: attach a monotonically increasing number to each lock acquisition, pass it with every write, and have the resource refuse any write carrying a number lower than one it has already accepted. That is a fencing token, and it works because it moves the decision to the only party that observes the writes in order.

The order to reach for these

Lock last, and preferably not at all, because most jobs that "need a lock" do not:

1. Make the job idempotent. Running twice is then not a problem to prevent, and everything else here becomes optional.
2. Make the database enforce it. A unique constraint, an INSERT … ON CONFLICT DO NOTHING, a SELECT … FOR UPDATE — chapter 12's tools. Real mutual exclusion, enforced by the system that owns the data, with no lease to expire.
3. Serialise by key. Route all jobs for a given entity to the same queue partition, so two never run at once. No lock, because there is no concurrency.
4. Only then a distributed lock — and only for work whose duplication is wasteful rather than incorrect, since an expiring lease means it can happen anyway.

That ordering summarises the chapter. Every mechanism here — the outbox, the idempotency key, the fencing token — exists because two systems cannot agree atomically, and each one either moves the decision into a single system that can, or makes the disagreement survivable. There is no third strategy.

You cannot fix what you cannot see

Almost every mechanism in this guide fails quietly. A queue with no depth gauge, a pool with no free connections, a cache serving a stale copy — none of them raises. You find out from a support ticket, or because you instrumented something first.

There are three instruments, and they are not three views of one thing. Each is affordable at a resolution the others cannot reach.

Metrics are cheap per request and expensive per distinct label combination; logs are the reverse; traces are expensive enough that you sample, so the interesting request is often the one you discarded. Asking a question of the wrong instrument is how people pay five figures and still do not know.

Metrics come in three shapes, and picking the wrong one loses the answer before you ask the question. A counter only goes up — requests served, errors returned — and you read it by asking how fast it rose. A gauge is a level that moves in both directions, sampled whenever you look: queue depth, connections in use, memory held. A histogram records a distribution rather than a value, by counting how many observations fell into each of a set of buckets — which is the only one of the three that can answer a question about percentiles, for a reason the end of this chapter makes uncomfortably concrete.

Two kinds of identifier

To read anything across chapter 3's chain, requests have to be joinable, and there are two different mechanisms for that.

One you carry: X-Request-Id, or Cloudflare's CF-Ray, "a hashed value that encodes information about the data center and the visitor's request." A string in a header, propagating because you wrote code to propagate it.

One the protocol carries. Point an instrumented HTTP client at a server and read what arrives:

local trace_id : 99f93e56b9df97a70c6a08da9b9672fc
local span_id  : 08c59f669cdb4bb9

headers that arrived at the downstream service:
   traceparent: 00-99f93e56b9df97a70c6a08da9b9672fc-aa9c04b9345e2690-03

That is W3C Trace Context: version, 32-hex trace id, 16-hex parent span id, flags. The middle field is not the caller's own span id but the id of the client span created for this call, so the callee can point at the exact operation that invoked it.

Nobody wrote code to send it. Now the difference that makes — two requests, one where the edge service forwards X-Request-Id downstream and one where it does not:

{"service": "inner-service", "path": "/inner",  "request_id": "req-abc-123",
 "trace_id": "78f294170a133b10c85e374d6866902d", "span_id": "179848948acb15b1", "duration_ms": 21.01}
{"service": "edge-service",  "path": "/edge",   "request_id": "req-abc-123",
 "trace_id": "78f294170a133b10c85e374d6866902d", "span_id": "3be534dda9cb3982", "duration_ms": 90.69}

{"service": "inner-service", "path": "/inner",           "request_id": "edc6658c14194f49",
 "trace_id": "3ebb2d99b86d40a6ed00eef662318ec5", "span_id": "c5dc4034c6fd6aff", "duration_ms": 21.02}
{"service": "edge-service",  "path": "/edge-noforward",  "request_id": "req-zzz-999",
 "trace_id": "3ebb2d99b86d40a6ed00eef662318ec5", "span_id": "6b6dfb9d3479d009", "duration_ms": 90.33}

In the second pair the request id was lost — inner minted a fresh one — while the trace id crossed intact. Same code, same libraries. Your correlation id propagates only if you carry it by hand at every hop. Trace context propagates because a specification says how and a library implements it.

Keep both: the request id is what a customer pastes into a ticket, the trace id is what stitches the machines together. Put both in every log line and the join costs nothing later.

The average is a number nothing produced

Two thousand real requests against an endpoint that is usually fast and occasionally not:

  mean (the average)       19.6
  median / p50              8.2
  p90                      11.9
  p95                      75.4
  p99                     118.0
  p99.9                  1087.0
  max                    1158.5

  requests slower than the MEAN: 155 of 2000 (7.8%)

The mean is 19.6 ms and only 7.8% of requests were slower than it. Nothing took anything like 19.6 ms: 92% were much faster and the rest much slower. The average of a skewed distribution names a value the system rarely produces, and latency is always skewed because there is a floor and no ceiling.

Look at p90 (11.9 ms) against p95 (75.4 ms). A dashboard showing p90 calls this healthy while one request in twenty is seven times slower.

A second trap, and this one dashboards commit for you:

  p99 of each window : [99, 112, 105, 119, 110, 110, 809, 96, 611, 1079]
  mean of those p99s :    325.0   <- what a dashboard often shows
  TRUE p99 of all    :    118.0   <- what you actually wanted
  max of those p99s  :   1078.7

You cannot average percentiles. A percentile is a rank statistic and ranks do not add. Ten per-window p99s averaged to 325 ms against a true 118 ms — off by 2.8×, overstating here, and the direction is not predictable. Recovering a true global percentile needs the distribution, not the summary: histogram buckets, or a sketch like t-digest. That is why OTel specifies http.server.request.duration as a histogram with explicit bucket boundaries rather than a gauge of the current p99.

Why p99 rather than p50? Because a user is not a request:

  P(at least one of 20 requests is above p99) = 1 - 0.99^20 = 18.2%
  so a 'p99 of 118ms' means 1 in 5 page loads contains one.

A page making 20 backend calls hits the tail on nearly a fifth of loads. The 99th percentile is not a rare edge affecting one per cent of users; under fan-out it is a common experience. p50 predicts what a request feels like, and nobody experiences a single request.

Cardinality is the bill, and the attack surface

When a metric is not specific enough the instinct is to add a label. That is correct for logs, where a field is a field. For metrics it is a Cartesian product:

A counter http_requests_total, labelled:
  route                                                       10 series
  route, method, status                                      480 series
  route, method, status, pod                               2,880 series
  + user_id   <-- the one that kills you                 199,852 series
  raw URL path instead of the route template              49,138 series

Theoretical ceiling for the bounded label set:
  10 routes x 4 methods x 12 statuses x 6 pods = 2,880 series  (bounded, knowable in advance)
  x 50,000 users = 144,000,000 series  (unbounded, grows with your success)

One label took 2,880 series to 199,852, and the ceiling to 144 million. A collector holds an index entry per active series, so cost scales with series count, not request count: ten million requests against 2,880 series is cheap; ten thousand requests against ten thousand series is not.

So a label's value set must be bounded, small, and not derived from anything a user controls. That is why OTel records both http.route (the template, /orders/{id}) and http.target (the concrete path), and says of the first: "This MUST be low-cardinality and include all static path segments, with dynamic path segments represented with placeholders." Labelling by raw path above produced 49,138 series from ten routes.

And then the sharp edge, repeated in OTel's conventions on every header-derived attribute:

Since this attribute is based on HTTP headers, opting in to it may allow an attacker to trigger cardinality limits, degrading the usefulness of the metric.

A user who can influence a label value can inflate your series count until the collector starts dropping series: a denial of service against your ability to see, executed with ordinary well-formed requests. Cardinality is not only a bill.

Which closes the loop on everything before it. Chapter 4's tasks_waiting is a gauge. Request duration is a histogram labelled by route and status and nothing else. A request id minted in middleware sits in every log line beside the trace id. None of that is much work, and without it every mechanism in the previous twenty chapters fails in silence.

Your schema is a contract you already signed

Every API has a contract, and in most stacks it is a document somebody wrote once whose defining property is that it is wrong. Not through negligence: a document and the code that serves requests are two artefacts, and two artefacts drift.

Here they are one artefact, and that changes what the contract is for. Declare a model with a computed field and ask for the generated schema:

openapi version : 3.1.0
components      : ['HTTPValidationError', 'Money-Input', 'Money-Output', 'ValidationError']
POST /charges request  -> {'$ref': '#/components/schemas/Money-Input'}
POST /charges response -> {'$ref': '#/components/schemas/Money-Output'}
Money-Output    : {"properties": {"currency": {"type": "string", "enum": ["GBP", "USD"], ...},
                   "minor_units": {"type": "integer", ...},
                   "display": {"type": "string", "title": "Display", "readOnly": true}},
                   "required": ["currency", "minor_units", "display"], "title": "Money"}

One class, two schemas. Money-Input has two fields, because that is what the validator accepts; Money-Output has three, because the serializer emits display as well. That is chapter 9's two compiled objects projected into JSON Schema, and FastAPI had no choice about splitting them: they genuinely disagree about what a Money is.

Which is the argument. response_model=Money is simultaneously the serializer deciding what bytes leave your process and the schema documenting them. They cannot drift, because they are one object. A hand-written document can be wrong about your API; this one cannot be — and a document that cannot be wrong is a document you can diff.

Breaking versus additive, read off the schema

Make five changes to a response model and look at what each does to required and properties:

  v1                  required=['id', 'name']
                      props={"id": "integer", "name": "string"}
  + optional field    required=['id', 'name']
                      props={..., "nickname": [{"type": "string"}, {"type": "null"}]}
  + required field    required=['id', 'name', 'nickname']
                      props={..., "nickname": "string"}
  renamed a field     required=['id', 'label']
                      props={"id": "integer", "label": "string"}
  widened id's type   required=['id', 'name']
                      props={"id": [{"type": "integer"}, {"type": "string"}], "name": "string"}

One sentence governs all five: a change is additive if it widens what you accept or narrows what you promise. Everything else breaks somebody — and because requests and responses travel in opposite directions, the same edit is safe on one and fatal on the other:

None of that needs adjudicating by memory or by review. Save openapi.json in CI and diff it against the previous commit: every row above shows up as a mechanical change to required or properties. A schema you generate is a breaking-change detector you already own — and you own it only because the schema comes from the same declarations the runtime uses.

Offset pagination is wrong, not slow

LIMIT 20 OFFSET 40 is correct in every tutorial, every test and every demo, because all of those run against a table nobody is writing to. Here it is against a table somebody is writing to:

=== A. OFFSET, with two rows inserted at the head between requests ===
  page 0 (OFFSET 0) -> ['row-19', 'row-18', 'row-17', 'row-16', 'row-15']
  ... 2 rows inserted at the head ...
  page 1 (OFFSET 5) -> ['row-16', 'row-15', 'row-14', 'row-13', 'row-12']
  DUPLICATES the client received -> ['row-15', 'row-16']

=== B. OFFSET, with the two newest rows deleted between requests ===
  page 0 (OFFSET 0) -> ['row-19', 'row-18', 'row-17', 'row-16', 'row-15']
  ... 2 rows deleted from the head ...
  page 1 (OFFSET 5) -> ['row-12', 'row-11', 'row-10', 'row-09', 'row-08']
  GAPS - rows that exist and the client never saw -> ['row-14', 'row-13']

Two rows delivered twice, two rows that exist and were never delivered at all, no error and nothing the client can inspect to discover either.

OFFSET n means "skip the first n rows of the query I am running right now". It is a position in a result set recomputed from scratch on every request, so it is stable only if the set is stable — and between the two requests another transaction committed. That is chapter 12's mechanism: each statement takes its own snapshot, and two requests are two snapshots of a moving table. Nothing in the database is broken. Offset pagination is asking a question whose answer changed.

The fix is to name a row instead of a position:

SELECT created_at, id, label FROM w1_pagedemo
WHERE (created_at, id) < (%s, %s)
ORDER BY created_at DESC, id DESC LIMIT 5

=== C. KEYSET, the same two disturbances ===
  page 0            -> ['row-19', 'row-18', 'row-17', 'row-16', 'row-15']
  ... 2 rows inserted at the head, then removed ...
  page 1 (cursor)   -> ['row-14', 'row-13', 'row-12', 'row-11', 'row-10']
  duplicates -> none

The cursor names a row, so churn above it is invisible. The tuple comparison matters: created_at is not unique, and without the primary-key tie-break a duplicate timestamp on a page boundary reintroduces the exact duplicate you were removing.

Offset is still right for a bounded, human-browsable list where somebody wants to jump to page 7, which keyset cannot express. That is the real trade — random access versus correctness, not convenience versus speed. (Keyset is also faster; chapter 13's EXPLAIN shows why a deep OFFSET walks and discards every row it skips.)

Errors are part of the contract as well

FastAPI's defaults are fine, and inconsistent. Two shapes for one field called detail:

  422 content-type: application/json
  422 body        : {"detail": [{"type": "literal_error", "loc": ["body", "currency"],
                     "msg": "Input should be 'GBP' or 'USD'", "input": "EUR", ...},
                    {"type": "missing", "loc": ["body", "minor_units"],
                     "msg": "Field required", "input": {"currency": "EUR"}}]}
  404 body        : {"detail": "Not Found"}

A list of objects for a validation failure, a bare string for a 404, and input echoing the client's submitted value back — which will end up in your logs and possibly on their screen.

RFC 9457, Problem Details for HTTP APIs, is the standard shape. It obsoletes RFC 7807, uses application/problem+json, and defines five members: type, a URI naming the kind of problem (defaulting to about:blank); status; title, "a short, human-readable summary of the problem type" that "SHOULD NOT change from occurrence to occurrence"; detail, "a human-readable explanation specific to this occurrence"; and instance, which identifies this occurrence and, "when not dereferenceable, serves as a unique identifier". Extensions are allowed, and clients "MUST ignore any such extensions that they don't recognize" — the additive rule again, written into the format.

Two exception handlers convert everything:

  application/problem+json
  {
    "type": "https://api.example.com/problems/validation-error",
    "title": "Your request is not valid.",
    "status": 422,
    "instance": "/charges",
    "errors": [
      {"detail": "Input should be 'GBP' or 'USD'", "pointer": "#/currency"},
      {"detail": "Field required", "pointer": "#/minor_units"}
    ]
  }
  application/problem+json {"type": "https://api.example.com/problems/404",
                            "title": "Not Found", "status": 404, "instance": "/nope"}

The errors array of JSON Pointers is the spec's own shape for reporting several problems at once, and Pydantic's loc tuples map onto it directly. Note what the second line required: that 404 is only reachable because the handler is registered against starlette.exceptions.HTTPException rather than FastAPI's subclass — chapter 5. Register against the wrong one and every route you wrote is consistent while every 404 and 405 is not.

Three things carry the contract, then, and all three are generated from something you wrote once: the schema, the cursor, and the error shape. A version number is what you reach for when you did not.

Further reading

The primary sources behind this guide, and what each is good for. Everything here was read while writing; nothing is listed because it is famous. Where a page is named in italics rather than linked, it is because this machine could not reach that host to confirm the path — the title is exact, and searching it will find it faster than a link that has rotted.

The specifications. The ASGI specification — the specs/ directory of the django/asgiref repository, mirrored at asgi.readthedocs.io — is short, and reading it once removes most of the mystery from every Python web framework you will ever touch. Two files: asgi.rst for the calling convention, lifespan.rst for startup and shutdown. For HTTP itself, RFC 9110 defines semantics and RFC 9111 defines caching. Read 9111 properly: it is the normative source for every freshness and validation rule in part five, and far more readable than its reputation. RFC 5861 adds stale-while-revalidate in four pages, and is an Informational submission rather than standards-track — worth knowing when a cache ignores it. RFC 9457 is the error-body format from chapter 24.

PostgreSQL's manual is the best documentation of any database, and three chapters carry most of part three: Concurrency Control for MVCC and isolation, and Routine Vacuuming for what bloat is and why it happens. Two more are worth knowing by name: Performance Tips, whose "Using EXPLAIN" section is the reference for chapter 13, and Explicit Locking, which carries the lock conflict table that chapter 16's migration material rests on.

SQLAlchemy's documentation is unusually good and unusually long. Four pages carry part three: Session Basics, State Management — which is where expire_on_commit and the object states are specified — Relationship Loading Techniques for what each loader strategy emits, and Connection Pooling for chapter 11's parameters. All four are under docs.sqlalchemy.org/en/20/. The 2.0 migration guide on the same site is worth a skim purely to recognise 1.x-era code and advice on sight, which is most of what you will find written online. For Alembic, the Auto Generating Migrations page states in its own words what autogenerate cannot detect — the honest list chapter 16 works from.

Redis's documentation is task-shaped and good. Three pages: Key eviction for maxmemory and the policies, Redis persistence for what each option loses in a crash, and Distributed locks, which is Redis's own statement of Redlock. Read that last one alongside Martin Kleppmann's critique and Salvatore Sanfilippo's reply. The disagreement teaches more than either position alone, and chapter 22 does not settle it.

FastAPI, Starlette and Pydantic. For these three the installed source is a better source than the documentation, and reading it is the single habit most likely to move you past intermediate. fastapi/routing.py and fastapi/dependencies/utils.py are where the request lifecycle and the dependency solver live, and they are ordinary readable Python — several claims in part two were established by reading them after the documentation turned out to be behind. Check the release notes before trusting anything written elsewhere: claims about include_router, response_model and BaseHTTPMiddleware that were true for years stopped being true during 2026, and the blog posts have not caught up.

The infrastructure. Caddy's reverse_proxy directive documents exactly which headers it sets and what trusted_proxies changes — chapter 3 is largely a reading of that page plus a packet capture. PgBouncer's configuration reference is the authority on pooling modes and on max_prepared_statements, which is the setting chapter 11 turns on and off. For Neon, Scale to Zero and Compute lifecycle explain the cold start, and Storage overview explains the separation that makes branching possible; its Branching and Connection pooling pages are the two most worth reading if you are already running on it.

For observability, OpenTelemetry's Semantic Conventions for HTTP are what make traces from different libraries comparable, and W3C Trace Context is the two-header standard that carries a trace across a service boundary — both are specifications rather than tutorials, and both are short.

One book, and it is not about any of this. Martin Fowler's Patterns of Enterprise Application Architecture is where the identity map and the unit of work in chapter 14 come from, and it predates every framework in this guide. Reading the two as patterns is what turns SQLAlchemy from a library with surprising behaviour into one implementation of a problem that has no clean solution.