Connection management

What you will be able to do

Stop opening a new database connection for every request. Share a few open connections in a pool, and set its limits. Make sure that a failed request never keeps a connection or leaves half a change behind.

Before you startWhat to know and to have readyAbout 55 minutes

What you need to know

• The repository and the ticket endpoints, from Safe queries.
• A transaction, commit and rollback, and SELECT … FOR UPDATE, from Transactions.
• The connection string in DATABASE_URL, from From SQLite to PostgreSQL.

What you need on your computer

• The project after Safe queries: python -m pytest prints 67 passed, and .env has the line DB_POOL=false.
• PostgreSQL running, with the small data loaded. Later in the lesson you load the large data; it was made once in Access-pattern inventory. If data/large/ is missing, the loader makes it again (about 15 seconds).

After this lesson, your project has three more files and matches the snapshot end-of-m03, except for the migration files of the next lesson. Every busy pool and every leak in this lesson is a constructed case. The pool and the database are real; the slow requests and the wrong code are on purpose. Tested on macOS (Apple silicon); the Windows commands are not tested. The times were measured on an Apple M4 computer with nothing else busy; your times will differ, but the shape stays the same.

A row of trolleys

At the door of a supermarket stands a row of trolleys. You take one, shop, and bring it back. The next shopper takes the same trolley. Nobody builds a new trolley for each visit, and nobody throws one away at the end.

Three things can go wrong. If 30 shoppers arrive at once and the shop has 10 trolleys, 20 shoppers wait at the door. If some shoppers take their trolleys home, the row slowly empties, and shoppers wait although the shop is quiet. And if the shop had no trolleys at all, each shopper would need one made at the door: a slow start to every visit.

A database connection is the trolley.

The problem: a new connection for every request

Until now, history.py opened a new connection for every request, and closed it at the end. Since the last lesson, the ticket endpoints do the same, because .env says DB_POOL=false:

ticket_api/history.py (end of Module 2)

return psycopg.connect(self.url, connect_timeout=3, row_factory=dict_row)

A connection is more than an open line. Before the first query, the client and the server open a network connection and check the password. Then PostgreSQL starts a new server process just for this connection, with its own memory. At the end, all of it is thrown away. The author measured it with scripts.pool_demo connect:

== connect
open a new connection: median 3.25 ms | borrow one from the pool: median 0.003 ms (50 times each)

Opening a connection took 3.25 ms. Borrowing an open one took 0.003 ms: about a thousand times less. Is 3 ms a lot? Measure before you decide. The author ran the same workload, 200 requests to the ticket list on the large data, 4 at a time, with and without the pool:

200 requests, 4 at a time, pool on (max 10)
median 438.7 ms | p95 500.8 ms | slowest 574.7 ms | 9.0 requests/s
errors: 0 {200: 200} | database queries per request: 41

200 requests, 4 at a time, pool off
median 405.1 ms | p95 477.8 ms | slowest 516.0 ms | 9.7 requests/s
errors: 0 {200: 200} | database queries per request: 41

No difference. The pool-off run was even a little faster, which is noise: two runs of a 400 ms request differ by more than 3 ms. Each request sends 41 slow queries, and 3 ms is less than 1% of the time. The pool cannot help an endpoint whose time goes to its queries.

Module 4 makes the same list take one query and 2.8 ms. Then the 3 ms of a new connection is more than the whole request. At 4 requests at a time, the median grew from 2.8 ms with the pool to 8.1 ms without it. The API served about 482 instead of 1,334 requests per second. So a pool matters most when queries are fast. Measure your own endpoint before you blame the connections.

There is a second reason, and it is about limits. A PostgreSQL server accepts a fixed number of connections: SHOW max_connections; prints 100 on the course database, and each connection is a server process with its own memory. Without a pool, the number of connections is the number of requests in progress. In the author’s test with 64 clients at once and no pool, every request still succeeded, and the limit was never reached. FastAPI runs a normal (def) endpoint in a thread pool. That pool runs at most 40 at a time by default, so at most 40 connections were open. Add a second app process or more workers, and the sum can pass 100. Then new connections fail with too many clients. The course did not run that case; it is what happens when many processes each open their own connections.

The idea: a connection pool

A connection pool keeps a few connections open and lends them out. A request borrows one, uses it, and gives it back. The connection stays open for the next request. The pool also sets the rules of the trolley row:

• Minimum size: how many connections stay open when the API is quiet (DB_POOL_MIN, 2).
• Maximum size: the most it opens, also when the API is busy (DB_POOL_MAX, 10). This protects the database’s limit.
• Wait limit: how long a request waits for a free connection before it gives up (DB_POOL_TIMEOUT, 5 seconds). Then the API answers 503 database_busy.
• Lifetimes: a connection is replaced after 30 minutes (max_lifetime), and an extra idle connection closes after 5 minutes (max_idle). Old connections do not live forever, and a quiet API holds few.

A tiny example shows the rules with every number. A pool has 3 connections. Six requests arrive, 0.05 seconds apart, and each one needs its connection for 2 seconds. A request waits at most 1 second. The output of scripts.pool_demo exhaust:

== exhaust
pool of 3, each request holds its connection 2.0 s, a request waits at most 1.0 s
  request 1: asked at 0.00 s, connection at 0.00 s, done at 2.01 s: ok
  request 2: asked at 0.05 s, connection at 0.05 s, done at 2.06 s: ok
  request 3: asked at 0.11 s, connection at 0.11 s, done at 2.11 s: ok
  request 4: asked at 0.17 s, connection at -, done at 1.17 s: busy (503)
  request 5: asked at 0.23 s, connection at -, done at 1.23 s: busy (503)
  request 6: asked at 0.29 s, connection at -, done at 1.30 s: busy (503)

Requests 1 to 3 get a connection at once. Requests 4 to 6 wait. The first connection comes back at 2.01 s, but they gave up at about 1.2 s, after their 1 second. Each of them answers 503. Try the other sizes and wait limits:

Explore

A connection pool: waits, 503s and a leak

Answered

3 of 6

Busy (503)

3

Longest wait for a connection

0.00 s

A pool of 3, 6 requests 0.05 s apart, each holds its connection 2 s, and a request waits at most 1 s. Requests 1 to 3 get a connection. The other 3 wait 1 s, get nothing, and answer 503 database_busy. A connection comes back only at about 2 s.

• Waits for a connection
• Holds a connection
• Gives up: 503

1. Request 1ok at 2.01 s
2. Request 2ok at 2.07 s
3. Request 3ok at 2.12 s
4. Request 4503503 at 1.17 s
5. Request 5503503 at 1.23 s
6. Request 6503503 at 1.29 s

0 s1 s2 s3 s4 s

A constructed case with a real pool and a real PostgreSQL database (scripts.pool_demo). The times come from the settings (2 s of work, the wait limit), so they are almost the same on every computer.

Recorded runs of scripts.pool_demo (a real pool on a real database; the slow work and the leak are constructed) and the measured endpoint times with the pool on and off.

Text description of the explorer

The explorer has three views. The first, “Six requests at once”, shows six requests as rows on a time axis from 0 to 4.5 seconds. A hatched bar is the time a request waits for a connection. A solid bar is the 2 seconds it holds one, and a 503 mark is the moment it gives up. It starts with a pool of 3 and a wait limit of 1 s. Requests 1 to 3 hold a connection until about 2.1 s. Requests 4 to 6 answer 503 at about 1.2 s.

With a wait limit of 3 s and a pool of 3, all six get an answer. Requests 4 to 6 wait about 1.9 s for a connection that comes back, then hold it for 2 s. The last one finishes at 4.15 s. With a pool of 1 and 1 s, only request 1 works. With a pool of 1 and 3 s, request 2 waits 2.0 s and works. Requests 3 to 6 answer 503 at about 3.2 s. With a pool of 2 and 3 s, four work and two answer 503. With a pool of 6, all six get a connection at once.

The second view, “A leak”, steps through code that forgets to give connections back (see the common mistake below). After three failed lookups, 0 of 3 connections are free. The fourth call fails with PoolTimeout, and a normal request then waits 1.01 s and answers 503.

The third view, “Pool on or off”, shows the median time per request of the fast Module 4 list endpoint. At 1 request at a time: 1.8 ms with the pool, 5.4 ms without. At 4: 2.8 and 8.1 ms. At 16: 11.6 and 29.3 ms.

At 64: 46.7 and 116 ms. From 4 at a time up, throughput was about 1,300 requests per second with the pool. Without it, it was about 500. No request failed.

What this tells us. A larger wait limit turns 503s into slow answers; a larger pool turns waits into parallel work. Neither is free. A long wait limit keeps clients waiting on a database that is too slow, and a large pool can push the database past its limit. The right numbers come from measurement: how long a request holds its connection, and how many arrive at once.

Larkfield: the pool in ticket_api/db.py

Amira’s ticket_api/db.py creates one pool for the API. The API opens it at start-up and closes it at shutdown, in main.py, so no connection stays open on the server after the API stops. Every endpoint borrows a connection in a with block:

ticket_api/tickets.py (one endpoint)

def add_message(ticket_id: TicketId, message: MessageIn, db: DB) -> Message:
    with db.connection() as conn:
        row = repository.add_message(conn, ticket_id, message.author, message.body)
    return Message(**row)

The with block is the whole contract. At its end, the connection goes back to the pool. Before that, the block commits if it ended normally, and rolls back if it ended with an error. The code inside cannot forget either step. Here is the part of db.py that does it:

ticket_api/db.py (Database.connection, the pool part)

        try:
            with self.pool.connection() as conn:  # commit at the end, roll back on an error, return
                yield conn
        except PoolTimeout as error:
            ...
            raise DatabaseBusy(f"no connection within {self.timeout} seconds") from error
        except psycopg.OperationalError as error:
            raise DatabaseUnavailable("the database does not answer") from error

Two errors become clear answers in errors.py. DatabaseBusy is 503 database_busy with the header Retry-After: 2, so a client knows that it may try again soon. DatabaseUnavailable is 503 database_unavailable. With DB_POOL=false, the same connection() opens and closes a new connection instead. The project keeps that mode only to measure the difference.

The whole path of one request, with the pool and the transaction:

One request to add a message: borrow a connection, one transaction, give the connection back on both paths.

Text description of the diagram

The diagram has two boxes marked alt, one for each choice. The first is the pool. Either a connection is free at once, or the request waits up to 5 seconds and answers 503 without touching the database. The second is the transaction: either every statement worked and COMMIT keeps both changes, or one failed and ROLLBACK removes both. On both paths, the connection goes back to the pool. That is what “a failed request never keeps a connection or leaves half a change” means.

Keep every transaction short and closed

A borrowed connection is a trolley in use. The pool can lend it to nobody else, and a transaction on it can hold locks that other requests wait for. So the rules are about time:

1. Start and end the transaction in one place. In the project, the endpoint’s with db.connection() is the transaction for reads, and repository.add_message opens with conn.transaction() for its three statements. A repository function never commits on its own.
2. Do no slow work inside it. Do not call an AI model, send an email or upload a file while you hold a connection. Read what you need, give the connection back, do the slow work, then borrow again to save the result. Module 5 follows this rule for file storage.
3. Never take a connection by hand. pool.getconn() needs a matching pool.putconn() on every path, including errors. The with block does that for you. The common mistake shows what happens without it.

The module’s completion check tests the second path of the diagram, the failure. scripts.pool_demo rollback borrows a connection, starts a transaction, adds one message to T-30002, and then adds a second one with the author robot. The CHECK constraint messages_author_known rejects the second message:

== rollback
  error: new row for relation "messages" violates check constraint "messages_author_known"
  messages on T-30002: 1 before, 1 after | pool: 2 of 2 connections free

T-30002 had 1 message before and 1 after, so the first insert did not stay: no partial update. Both connections of the pool are free again, so the failed transaction leaked nothing. The test tests/test_pool.py checks exactly these two facts. It also checks them through the API: five failed requests in a row on a pool of 2, and the next request still works.

Guided practice: switch the pool on and measure it

1. In ~/projects/ticket-api, with the virtual environment active, copy the three files of this lesson from the snapshot end-of-m03:

   macOS / Linux

   S=../nextia-learning-labs/C20/snapshots/end-of-m03
   for f in scripts/pool_demo.py scripts/bench.py tests/test_pool.py; do cp "$S/$f" "$f"; done

   Windows (PowerShell)

   $S = "..\nextia-learning-labs\C20\snapshots\end-of-m03"
   foreach ($f in 'scripts\pool_demo.py', 'scripts\bench.py', 'tests\test_pool.py') { Copy-Item "$S\$f" $f }

2. Delete the line DB_POOL=false from .env. The pool is now on, which is the default.

3. Run the four scenarios on the small data, one at a time: python -m scripts.pool_demo connect, then exhaust, leak and rollback. Compare them with the outputs in this lesson.

4. Start the API with fastapi dev. In a second terminal, see its connections in PostgreSQL:

   docker compose exec db psql -U tickets -d tickets -c "SELECT pid, state, backend_start::time(0) AS started FROM pg_stat_activity WHERE datname = 'tickets' AND pid <> pg_backend_pid();"

   Then send a few requests to http://127.0.0.1:8000/v1/tickets and run the query again. Stop the API with Ctrl+C, and run it a third time.

5. Run the tests: python -m pytest. Expect 74 passed.

6. Run the module’s completion check alone, with each test’s name: python -m pytest tests/test_pool.py -v.

7. Load the large data for the benchmark: python -m scripts.load --size large --reset. It takes a few seconds on a quiet computer and up to a minute on a busy one. It takes longer if it must make the data again.

8. Measure the list endpoint with the pool, then without it. Each run takes about 25 seconds. Close other busy programs first.

   python -m scripts.bench
   python -m scripts.bench --pool off

   Keep the large data: the next lesson uses it.

Show the expected result

Step 3 prints the four outputs of this lesson; the times of connect and the exact seconds differ a little on your computer. Step 4: while the API runs, the query shows two idle connections, which are the pool’s minimum. After your requests, it shows the same two pid numbers: the pool reused them. After Ctrl+C, it shows (0 rows), because the pool closed them at shutdown.

  pid  | state | started
-------+-------+----------
 18468 | idle  | 04:34:51
 18469 | idle  | 04:34:51
(2 rows)

Step 6 lists seven tests, among them test_a_failed_transaction_leaves_no_partial_update_and_no_connection and test_a_failed_api_request_returns_its_connection, and ends with 7 passed. Step 8: both runs print errors: 0 {200: 200} | database queries per request: 41, with medians of a few hundred milliseconds that are close to each other. Which one is faster changes from run to run. Two lines WARNING ticket_api: API_KEY is not set can appear first; they only say that /v1/classify has no key on your computer.

Your turn: a pool that is too small

Change one setting. The benchmark has the options --pool-max, --pool-timeout, --concurrency and --requests. Predict first: what happens on the large data with a pool of 1 connection, a wait of 1 second and 16 requests at a time? Each request takes a few hundred milliseconds. Then run it, with 48 requests so that it ends soon:

python -m scripts.bench --requests 48 --concurrency 16 --pool-max 1 --pool-timeout 1

Show the answer

Most requests answer 503. Only one request at a time can hold the one connection. The 16th request in the queue would wait for 15 others, several seconds, but it may wait only 1 second. In the author’s run, on a busy computer:

errors: 31 {200: 17, 503: 31} | database queries per request: 41

31 of 48 answered 503, and the log printed WARNING ticket_api: database busy: no connection within 1.0 seconds for each one. Your count will differ, but most requests fail. With the default pool of 10 and the same command without the two pool options, no request failed in the author’s run. The median time grew, though, because 16 slow queries at once share the same database. A bigger pool cannot make a slow query fast. That is the job of Module 4.

A leak that empties the pool

Common mistake

A connection taken by hand and given back only on success

Symptom (constructed): the API works for a while. Then every request waits and answers 503 database_busy, although the computer is quiet. scripts.pool_demo leak shows it with a pool of 3:

== leak
  call 1: LookupError; connections free: 2 of 3
  call 2: LookupError; connections free: 1 of 3
  call 3: LookupError; connections free: 0 of 3
  call 4: PoolTimeout
  a normal request: busy (503) after 1.01 s

Cause: a lookup takes a connection by hand and gives it back only when it finds the ticket:

scripts/pool_demo.py (the bug, on purpose)

conn = db.pool.getconn()
row = conn.execute("SELECT subject FROM tickets WHERE ticket_id = %s", (ticket_id,)).fetchone()
if row is None:
    raise LookupError(ticket_id)  # returns without db.pool.putconn(conn)
db.pool.putconn(conn)

Each lookup of a missing ticket keeps one connection. After three, the pool is empty, and correct code elsewhere pays for the bug.

Diagnose: look at the pool first. Its numbers (db.stats(), or the pool timeout warning in the log) show 0 connections available and requests waiting. Then look at the database. While the buggy code still holds the connections, SELECT state, query FROM pg_stat_activity WHERE datname = 'tickets'; shows them as idle in transaction, with the last query of the bug. In the author’s check, three rows looked like that. A connection in that state has an open transaction that nobody will finish.

Fix:

1. Borrow connections only with with db.connection() as conn:. The block returns the connection on every path.
2. Restart the API to get the connections back now. The leak comes back until the code is fixed.
3. Add a test that fails more often than the pool is large, as test_a_failed_api_request_returns_its_connection does: five failures on a pool of 2, then one normal request.

For an AI service: an AI endpoint often waits seconds for a model. If it holds a connection during that wait, each slow call acts like a short leak. Read what the prompt needs, give the connection back, call the model, then borrow again to save the AI run.

Knowledge check

Check your understanding

On this module's list endpoint, the pool made no difference (438.7 vs 405.1 ms). Should Amira remove it? Explain with the numbers.

Show the answer

No. On this endpoint, each request spends about 400 ms on 41 queries. The 3.25 ms of a new connection is less than 1% of it, lost in the noise. Module 4 makes the same endpoint take 2.8 ms. Then a new connection per request makes it 8.1 ms, and throughput falls from about 1,300 to about 500 requests per second. The pool also caps the number of connections, which protects the database’s limit of 100. Measure each change; keep the pool for both reasons.

Check your understanding

Predict: a pool of 10, a wait limit of 5 seconds. Each request holds its connection for 50 ms. 100 requests arrive at the same moment. Do any of them answer 503?

Show the answer

No, in this simple model. Ten requests run at once, and each batch of ten takes 50 ms. The hundredth request waits for nine batches, about 0.45 seconds, which is far below 5 seconds. Every request works, and the slowest is about half a second late. A request gets 503 only when its wait for a free connection is longer than the limit. That happens when requests hold connections for a long time, or when far more arrive than the pool can serve in time.

Check your understanding

A failed request must leave 'no partial update and no leaked connection'. Which line of the project makes each of these true?

Show the answer

No partial update: with conn.transaction(): in repository.add_message. When any statement inside it fails, psycopg rolls back the whole transaction, so the first insert does not stay. No leaked connection: with db.connection() as conn: in the endpoint. Its end gives the connection back to the pool on every path, also after an error. tests/test_pool.py checks both: messages before equal messages after, and every connection of the pool is free.

Recap

• Opening a connection took 3.25 ms; borrowing one from a connection pool took 0.003 ms. A pool keeps a few connections open and lends them out.
• Measure first. On the slow list endpoint (41 queries), the pool made no difference.
• Without a pool, the fast endpoint of Module 4 took 8.1 instead of 2.8 ms. Its throughput fell from about 1,300 to about 500 per second.
• The pool sets limits: 2 to 10 connections, a wait of at most 5 seconds, then 503 database_busy with Retry-After: 2; the pool replaces a connection after 30 minutes. The database’s own limit is 100 connections.
• with db.connection() gives the connection back on every path; with conn.transaction() keeps all of a change or none of it. Keep transactions short, with no slow work inside.
• A leak empties the pool: three failed lookups left 0 of 3 connections, and a correct request then answered 503.
• Completion check: tests/test_pool.py passes; your project has 74 passed.

Next: Migrations