Skip to content
Tutorials Center
My learning

Topics

Lesson 93 of 125 ยท Performance

Connection Pooling and Plan Caching

Learn why connections are expensive, how to size a connection pool, and how prepared statements let the database reuse query plans. Covers PgBouncer and pitfalls.

You can write a perfect query, put the right indexes behind it and still have a slow application, because the time is spent around the query. Opening a database connection takes real work: a network handshake, TLS, authentication, and a new server process or thread. Planning a query takes work too: the database has to parse your SQL, pick join orders and choose indexes before it executes a single row. Two techniques remove most of that overhead. Connection pooling reuses connections. Plan caching, through prepared statements, reuses the results of planning.

Both are things you configure rather than write, and getting them wrong causes some of the most confusing production outages: "too many connections", random slowdowns, and queries that are fast in testing but slow in the app. This lesson explains how each works and how to size and use them.

Why connections are expensive

Each connection is more than a socket. In PostgreSQL every connection is a separate operating-system process, typically using several megabytes of memory and more under load. MySQL uses a thread per connection, and SQL Server uses worker threads. Beyond memory, every active connection competes for CPU, locks and disk. This leads to a counterintuitive truth: more connections do not mean more throughput. A server with 8 cores cannot run 500 queries at once. Past a point, extra connections only add context switching and lock contention, and everything gets slower.

Connecting once per request is just as bad, because the setup cost is paid again and again:

sequenceDiagram
    participant A as Application
    participant D as Database
    Note over A,D: Without a pool, every request pays for setup
    A->>D: TCP and TLS handshake
    A->>D: Authenticate, start session
    A->>D: Run query
    D-->>A: Result
    A->>D: Close connection

Opening and closing a connection for each request can cost more than the query itself.

What a connection pool does

A connection pool keeps a fixed set of open connections and lends them out. A request borrows one, runs its queries, and gives it back instead of closing it. If every connection is busy, requests wait in line.

flowchart LR
    R1["Request 1"] --> POOL
    R2["Request 2"] --> POOL
    R3["Request 3"] --> POOL
    R4["Request 4<br/>(waiting)"] -.-> POOL
    subgraph POOL["Pool: 3 connections"]
        C1["conn 1"]
        C2["conn 2"]
        C3["conn 3"]
    end
    POOL --> DB[("Database")]

Many requests share a few long-lived connections; extra requests queue instead of overloading the database.

Pools live in two places:

  • Inside the application, a library pool such as HikariCP (Java), pg.Pool (Node), SQLAlchemy's pool (Python) or the persistent connections of a PHP-FPM worker. Each application process has its own pool.
  • In front of the database, a separate proxy such as PgBouncer or pgcat (PostgreSQL), ProxySQL (MySQL), or the managed equivalents (RDS Proxy, Cloud SQL connection pooling). One proxy serves all application servers, which matters when you have many of them.

Sizing a pool

The number of connections you need is smaller than most people expect. Little's law gives a first estimate: the average number of busy connections equals the arrival rate times the average time each request holds a connection.

TEXT
busy connections = requests per second ร— seconds a connection is held per request
SQL How many connections do we really need?

Press Run (or Ctrl + Enter) to see the result here.

The busy shop handles 400 requests per second with only about 5 connections busy at any moment, because each request is quick. The "slow, unindexed" version needs 100 connections for the same traffic, and the database would likely fall over. Notice what that means: the cheapest way to cut the connections you need is to make queries faster (see Query Optimization). Adding connections is the last resort.

Practical rules:

  • Start small. A commonly quoted starting point for a database server is about (CPU cores ร— 2) + number of disks, so 10 to 30 connections in total for a typical server, not hundreds.
  • Remember that every app process multiplies the number. Ten servers each with a pool of 20 means 200 connections. Divide the budget, or put a shared proxy in front.
  • Set a checkout timeout so a request that waits too long fails quickly with a clear error instead of hanging.
  • Set a maximum lifetime (for example 30 minutes) so connections are recycled, which helps with failovers, DNS changes and memory growth.
  • Always return connections: use finally or your framework's "with" scope. A leaked connection slowly drains the pool until the app freezes.
  • Do not hold a connection (or an open transaction) while doing something slow outside the database, such as calling an external API. It blocks the connection and its locks for the whole wait (see Transactions).

Pooler modes and their catches

An external pooler such as PgBouncer can hand out a server connection in different ways:

Mode A server connection is held for Notes
Session The whole client session Safest; fewest savings
Transaction One transaction Best reuse. Session state (SET, temporary tables, advisory locks, LISTEN) does not carry over between transactions
Statement One statement No multi-statement transactions at all

Transaction mode is the usual choice for web apps, but it means you cannot assume that two transactions from the same client run on the same server connection. This interacts with prepared statements, below.

Plan caching

When the database receives SQL text, it goes through several stages:

  1. Parse the text into a tree and check the syntax.
  2. Analyze: resolve table and column names, check permissions.
  3. Plan: estimate costs and choose the access paths and join order (see Reading Query Plans).
  4. Execute the plan.

For a simple lookup, planning can take longer than executing. If the same shape of query runs a thousand times per second with different values, planning it every time is wasteful. A prepared statement separates the shape from the values: you send the SQL once with placeholders, and then run it many times with different parameters.

sequenceDiagram
    participant A as Application
    participant D as Database
    A->>D: PREPARE: SELECT * FROM orders WHERE id = $1
    D-->>A: parsed (and planned) once
    A->>D: EXECUTE with 17
    D-->>A: rows
    A->>D: EXECUTE with 18
    D-->>A: rows (plan reused)

Prepare once, execute many times: parsing and planning are paid once.

Literals ruin the cache

Plan caches are keyed by the text of the statement. If your code pastes values into the SQL string, every different value produces different text, a new cache entry and a new planning pass. This is easy to simulate with a table that counts hits per statement text:

SQL A tiny plan cache: literals versus parameters

Press Run (or Ctrl + Enter) to see the result here.

Ten executions created six cache entries. Five were one-time plans, and the parameterized version was planned once and reused four times. With millions of runs the literal version floods the cache, evicts useful plans and wastes CPU.

Parameters also protect you from SQL injection, which is a second, more important reason to always use them (see SQL Injection). The placeholder syntax depends on the driver (?, $1, :name), but the principle is the same everywhere:

SQL
-- PostgreSQL, explicit SQL-level prepared statement
PREPARE get_order (int) AS
  SELECT id, total FROM orders WHERE id = $1;

EXECUTE get_order(17);
EXECUTE get_order(18);

DEALLOCATE get_order;

Most applications never write PREPARE themselves: the driver does it when you use parameters (pdo->prepare(...), JDBC PreparedStatement, Python's cursor.execute(sql, params), Node's pool.query(text, values)). Whether a plan is actually cached depends on the driver and the database.

Generic plans and parameter sniffing

Reusing a plan has a catch. The best plan can depend on the value. Searching for status = 'cancelled' (2% of rows) should use an index; searching for status = 'paid' (90% of rows) is faster with a full scan. A plan built for one value can be bad for the other.

  • PostgreSQL plans a prepared statement with the actual values for the first few executions ("custom plans"), then switches to a generic plan if it is not much worse on average. The plan_cache_mode setting can force either behavior.
  • SQL Server builds the cached plan from the parameter values of the first call and reuses it for everyone. When that first value was unrepresentative, you get parameter sniffing problems: a query that is fast in one session and slow in the app. Remedies include OPTION (RECOMPILE), OPTIMIZE FOR, and updating statistics.
  • MySQL's server-side prepared statements save parsing, but historically it optimizes again per execution, so the benefit is smaller. Many drivers emulate prepares on the client.
  • Oracle uses bind variables and "bind peeking" in a similar way to SQL Server.

If one query is fast in your SQL console but slow from the application, compare the plan with real parameter values against the one the app gets, and suspect plan reuse (see Database Tuning).

Prepared statements meet pooling

Prepared statements live on a particular server connection. With an application-side pool that is fine: the driver keeps a per-connection statement cache. But with a transaction-mode proxy, the next transaction may land on another server connection that has never seen your PREPARE, and you get errors such as "prepared statement does not exist". Modern poolers (PgBouncer 1.21 and later) can track protocol-level prepared statements for you. With older ones, either turn off driver-side prepares for those connections or use session mode.

A checklist

  • Use a pool. Never open a connection per request.
  • Keep total connections low; fix slow queries before raising the pool size.
  • Use parameters for every value that comes from outside the SQL, both for cache hits and for security.
  • Watch for pool exhaustion: queueing time, checkout timeouts, "too many connections" errors, idle-in-transaction sessions.
  • When something is fast in a console but slow in the app, check parameter sniffing and generic plans.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why is opening a new database connection for every request a bad idea?

    Connection setup (network, TLS, authentication, a new process or thread) can cost more than a simple query. A pool pays it once and reuses the connection.
  2. 2. A server handles 200 requests per second and each request holds a connection for 50 ms. About how many connections are busy on average?

    200 requests per second times 0.05 seconds is 10 busy connections (Little's law).
  3. 3. Which change usually reduces the connections you need the most?

    Required connections equal throughput times hold time. Shorter hold times need fewer connections for the same load.
  4. 4. Why does building SQL by concatenating the value into the text hurt plan caching?

    Caches are keyed by statement text. With placeholders there is one text for all values, so the plan is reused. Placeholders also prevent SQL injection.
  5. 5. What is parameter sniffing?

    A plan that suits one value (for example a rare status) can be slow for another (a common status). SQL Server and others can reuse that plan for all callers.

Summary

  • Connections are expensive and the database can only do so much at once. Use a pool (in the app, in a proxy, or both) and keep it small.
  • Required connections are roughly throughput times hold time, so faster queries beat bigger pools. Set checkout timeouts and maximum lifetimes, and always return connections.
  • Transaction-mode poolers give the best reuse but lose session state and complicate prepared statements.
  • Prepared statements plan once and execute many times. Use placeholders for all values, which also prevents SQL injection.
  • Plan reuse can backfire when the best plan depends on the parameter value (generic plans, parameter sniffing).

Next: PostgreSQL, where the Database Systems chapter looks at the features and syntax of one of the most popular open-source databases.