One Connection, Many Requests: Pooling, Transactions, and the N+1 Trap

So far in this series the database has answered you at a psql prompt. In production it answers an application: a web server that takes a request, runs some SQL, and returns a response, thousands of times a minute. The gap between “a query at a prompt” and “a query inside a request handler” is where a surprising number of production problems live. Crossing it well comes down to three habits. Hold a connection efficiently, wrap work in a short transaction, and avoid the one pattern that makes the database do a hundred times more work than it needs to.

Opening a connection is expensive

Connecting to Postgres is not free. The client opens a TCP socket, the two sides negotiate TLS, Postgres authenticates the role, and the server forks a whole backend process to serve that session. That handshake can cost several milliseconds, which dwarfs the sub-millisecond queries most requests actually run. Do it once per request and the connection setup, not your SQL, becomes the bottleneck. Worse, each connection is a live server process holding memory, so a burst of traffic that opens a connection per request can exhaust the server’s max_connections and start refusing everyone.

The fix is a connection pool: open a handful of connections once, keep them alive, and lend them out. A request borrows a connection, runs its queries, and returns it to the pool instead of closing it. The next request reuses the same warm connection. You pay the handshake a few times at startup, not on every request.

In a Python app with psycopg 3, the pool is a small object you create once and share:

from psycopg_pool import ConnectionPool

# created once, at application startup
pool = ConnectionPool(
    "postgresql://app:[email protected]/bookshop",
    min_size=4,      # keep at least 4 warm connections open
    max_size=20,     # never exceed 20 (stay well under server max_connections)
)

def get_customer_orders(customer_id):
    with pool.connection() as conn:           # borrow one from the pool
        with conn.cursor() as cur:
            cur.execute(
                "SELECT order_id, status FROM orders WHERE customer_id = %s",
                (customer_id,),
            )
            return cur.fetchall()
    # leaving the `with` returns the connection to the pool, not closes it

max_size is the important knob. Every pooled connection is a backend process on the server, so the sum of max_size across all your app instances must stay comfortably under the server’s max_connections. Ten app servers with max_size=20 each is 200 connections, and a default Postgres allows 100. When the pool is exhausted, pool.connection() blocks until one frees up, which is the pressure valve you want rather than an out-of-connections crash.

For fleets bigger than one app, a dedicated pooler like PgBouncer sits between the app and Postgres as a separate process. It multiplexes many client connections onto a small set of real server connections. It is most useful in transaction pooling mode: a server connection is assigned to a client only for the duration of one transaction, then handed to the next client the moment that transaction ends. That mode is what lets hundreds of app connections share a dozen backends. It comes with a rule. A connection can change hands between statements, so session-scoped state does not survive across transactions: no relying on SET, session-level advisory locks, or plain prepared statements.

The pooling code and PgBouncer configuration above are application architecture; they were written against the documented psycopg_pool and PgBouncer APIs and are not exercised at a psql prompt. Everything below the line is run against PostgreSQL 18.

A transaction per unit of work

Inside a request, the queries that must succeed or fail together belong in one transaction. Charge the card, insert the order, decrement the stock: either all three land or none do. You draw that boundary with BEGIN and COMMIT, and psycopg draws it for you. The connection context manager above commits when the block exits cleanly and rolls back if it raises. The mental model is one transaction per request, opened when work starts and closed when the response is ready.

The discipline that matters is keep them short. Series 3 showed what a long transaction costs. It holds its locks until commit. It pins a snapshot that stops VACUUM from cleaning up the dead rows behind it. And it makes everyone waiting on those locks wait longer. A transaction should wrap the database work and nothing else. The classic mistake is opening the transaction, then calling a slow payment API or waiting on user input while it stays open. Do the slow, non-database work outside the transaction; open it only when you are ready to write, and commit as soon as you are done.

The pathological version has a name Postgres reports directly. A connection that runs BEGIN, then sits waiting on the client without committing, is idle in transaction, and you can watch one from another session. Here one connection opened a transaction, wrote a row, and then went quiet:

SELECT pid, state, wait_event_type,
       round(extract(epoch FROM now() - state_change),1) AS idle_secs,
       left(query,45) AS last_query
FROM pg_stat_activity
WHERE datname='bookshop_r3' AND state='idle in transaction';

 pid  |        state        | wait_event_type | idle_secs |               last_query
------+---------------------+-----------------+-----------+---------------------------------------
 1636 | idle in transaction | Client          |       2.0 | UPDATE orders SET status = status WHERE order

That session is holding a row lock and pinning a snapshot while doing nothing. A leaked transaction like this, multiplied across a busy app, is a leading cause of bloat and lock pileups. Postgres gives you a guard: set idle_in_transaction_session_timeout and it will terminate any session that idles too long inside a transaction. With it set to two seconds, the offending session was killed rather than allowed to hold its lock forever:

SET
BEGIN
UPDATE 1
FATAL:  terminating connection due to idle-in-transaction timeout
server closed the connection unexpectedly

Blunt, but it caps the damage. Set it in production, and treat a session that hits it as a bug in your code, not a tuning problem.

The N+1 problem

Here is the mistake that hides best, because each query it runs is fast. Say a page needs five customers and their orders. The natural object-by-object code fetches the customers, then loops and fetches each customer’s orders:

1 query:   SELECT customer_id, name FROM customers WHERE customer_id <= 5;
then, per customer:
   SELECT order_id, status FROM orders WHERE customer_id = 1;   -- round trip 2
   SELECT order_id, status FROM orders WHERE customer_id = 2;   -- round trip 3
   ...                                                          -- 4, 5, 6

That is 1 + N round trips: one for the parents, then one more for each of the N parents. Five customers is six queries; a page of a hundred rows is a hundred and one. Every one is a network hop and a parse, and the latency stacks up even though each query is trivial. This is the N+1 problem, and an ORM produces it silently the moment you touch a related collection inside a loop.

The database was built to do this in one query. A single JOIN reassembles exactly the same rows, all children for all parents, in one round trip:

SELECT c.customer_id, c.name, o.order_id, o.status
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE c.customer_id <= 5
ORDER BY c.customer_id, o.order_id;

 customer_id |    name    | order_id |  status
-------------+------------+----------+-----------
           1 | Customer 1 |    23871 | returned
           1 | Customer 1 |    27508 | returned
   ...
           5 | Customer 5 |    56067 | delivered
(54 rows)

Six round trips collapse to one, and the result carries the same fifty-four rows the loop would have assembled by hand. If your access pattern really is “look up children for a known set of parent ids,” the set-based form of the same idea is a single IN:

SELECT customer_id, count(*) AS orders
FROM orders WHERE customer_id IN (1,2,3,4,5)
GROUP BY customer_id ORDER BY customer_id;

 customer_id | orders
-------------+--------
           1 |      6
           2 |     17
           3 |     16
           4 |      9
           5 |      6

One query, all five answered. This is the set-based stance from the very first chapter of the track, now with a production edge. The loop is not just less elegant. It is N extra round trips the database never needed to make. When you see an ORM issue the same SELECT ... WHERE parent_id = ? over and over in your query log, that is N+1. The fix is to fetch the related data in one join or one IN. Most ORMs call it eager loading or a JOIN fetch.

Final thoughts

An application talks to Postgres through three habits worth making automatic. Pool your connections so you pay the expensive handshake once, not per request. Wrap each unit of work in a short transaction, and never let one sit idle holding locks. And watch your query log for the N+1 pattern, because the database can almost always answer in one set-based query what your loop is asking one row at a time. None of this is new SQL. It is the same set-based thinking, applied at the seam where the query meets the request. Next we look at who is allowed to run that query at all, and which rows they get to see.

Next: Who can see which rows: roles and RLS — least-privilege roles, and row-level security that filters the table for you.