Skip to content
Tutorials Center
My learning

Topics

Lesson 66 of 90 ยท Performance Tuning

Connection Pooling

Why PostgreSQL connections are expensive, how PgBouncer's session, transaction and statement modes work, what breaks in transaction mode, and how to size pools.

Opening a database connection feels cheap in application code, and PostgreSQL makes it deceptively expensive. Every connection is a separate operating-system process, and every new one means a TCP handshake, authentication (often TLS plus SCRAM) and process creation before the first query runs. At a few dozen connections nothing bad happens. At a few thousand, a server with 16 cores spends its effort switching between processes rather than running queries.

Connection pooling solves this by keeping a small set of long-lived database connections and lending them out to many short-lived application requests. This lesson explains the problem, the three pooling modes of PgBouncer (the most widely used pooler), the session-state traps of transaction pooling, and how to size pools. PgBouncer and drivers are shown as read-only configuration and code, because they need a real server; the live examples do the sizing arithmetic in SQL.

Why connections cost so much

PostgreSQL uses a process-per-connection model. The postmaster process forks a dedicated backend for every client. That design is simple and robust (a crash in one backend does not corrupt another), but it has costs:

  • Startup time. Forking, authenticating and loading catalog caches take milliseconds each time. With TLS the connection setup alone can take longer than a simple query.
  • Memory. Each backend uses a few megabytes of its own memory, plus whatever work_mem its queries use. Idle connections still hold their share.
  • Contention. Many active backends compete for CPU and for shared structures, such as the lock manager and the snapshot machinery. Beyond a point, adding active connections reduces total throughput.

Most connections are idle most of the time: an application server holds a connection open, runs a 3 ms query, then waits for the next request. That mismatch (many mostly idle clients, few truly active queries) is exactly what a pool exploits.

Without and with a pooler

Direct One server process per client.
Overload Thousands of mostly idle processes.
Pooled Many clients share a few connections.
Steady The database runs a limited, healthy load.

Most connections are idle; a pool lends them out only while work happens.

1 / 2

Where the pool lives

There are two places to pool, and large systems use both:

  1. In the application. Every driver offers a pool (HikariCP in Java, pg.Pool in Node.js, SQLAlchemy's pool in Python, and so on). It saves connection setup time and caps connections per process.
  2. In an external pooler such as PgBouncer, PgCat or Odyssey, or a managed proxy such as Amazon RDS Proxy. It caps connections to the database across all application processes, which an in-process pool cannot do.

The in-process pool alone fails to scale when you have many application instances: 50 containers with a pool of 20 means up to 1,000 connections, even if each container is nearly idle. An external pooler turns those 1,000 client connections into a few dozen server connections. Serverless functions and short-lived workers make this even more important, since each invocation might otherwise open a new connection.

PgBouncer pooling modes

A minimal PgBouncer configuration looks like this:

TEXT
; pgbouncer.ini
[databases]
shop = host=127.0.0.1 port=5432 dbname=shop

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 2000        ; clients allowed to connect to PgBouncer
default_pool_size = 20        ; real server connections per database/user pair
reserve_pool_size = 5         ; extra connections for bursts
server_idle_timeout = 600

The application connects to port 6432 instead of 5432, and PgBouncer maps client connections onto server connections according to pool_mode:

Mode A server connection is held for Use it when
session The whole client session You need full PostgreSQL session semantics; it saves connection setup but not connection count
transaction One transaction The default choice for web applications: most savings, with some restrictions
statement One statement Rare; multi-statement transactions are not allowed

Transaction pooling is the mode people usually mean. When a client finishes a transaction (COMMIT or ROLLBACK, or one autocommit statement), its server connection returns to the pool and another client can use it. A thousand clients can share twenty server connections if each is inside a transaction only a few percent of the time.

What breaks in transaction mode

In transaction mode, two consecutive transactions from the same client may run on different server connections. Anything that lives in the session rather than the transaction can therefore vanish or leak between clients:

  • SET on a session. Use SET LOCAL (transaction-scoped) instead, or configure settings per role with ALTER ROLE ... SET.
  • Session-level advisory locks (pg_advisory_lock): they may be held by a connection another client is now using. Use transaction-level locks (pg_advisory_xact_lock).
  • LISTEN/NOTIFY: notifications arrive on one specific connection; a listener needs a dedicated direct (or session-mode) connection.
  • Temporary tables and WITH HOLD cursors that outlive a transaction.
  • PREPARE statements and the extended protocol. Server-side prepared statements belong to a connection. Historically transaction pooling failed with errors such as prepared statement "S_1" does not exist; the usual workaround was disabling prepared statements in the driver. Since PgBouncer 1.21, setting max_prepared_statements to a non-zero value makes PgBouncer track protocol-level prepared statements and re-create them on whichever server connection a client gets, so most drivers work unchanged. Check your PgBouncer version and driver notes. This is also related to the generic-versus-custom-plan behavior in Statistics and the Query Planner.

A practical pattern: run the application through PgBouncer in transaction mode, and give the few components that need session state (migrations, a LISTEN worker, a job with advisory locks) a direct connection or a separate session-mode pool.

Also watch for transactions that stay open. A client "idle in transaction" holds a server connection for as long as it dawdles, which defeats pooling and blocks vacuum. Set idle_in_transaction_session_timeout on the server, and avoid doing slow work (HTTP calls, file uploads) between BEGIN and COMMIT.

Sizing pools

More connections do not mean more throughput. A database with 8 CPU cores can run roughly 8 CPU-bound queries truly in parallel (plus some more while others wait on disk). Beyond that, extra active connections just queue, and add contention. A classic rule of thumb for the number of active database connections is:

TEXT
connections โ‰ˆ (CPU cores ร— 2) + effective spindle count

For SSD-backed servers treat it as a starting point and tune by load testing. A rough, useful way to size from your workload is Little's law: the average number of requests in the system equals the arrival rate times the time each one spends there.

TEXT
concurrent queries โ‰ˆ queries per second ร— average query time (in seconds)

If your application issues 400 queries per second and each holds a connection for 10 ms, it needs about 400 ร— 0.010 = 4 connections on average, not 400. Add headroom for bursts and slower moments, but the order of magnitude is single digits to a few dozen, not hundreds.

Here is that calculation for several services sharing one database, set against what naive per-instance pools would open:

SQL Little's law: connections needed versus connections opened

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

The three services are busy on only about 22 connections in total (8, 8 and 6), but their pools open up to 320: more than three times the server's max_connections of 100. Behind a pooler that keeps 30 or 40 server connections, everything fits comfortably, with the 320 client connections spread across them.

The same arithmetic works for a PgBouncer pool. The share of time a client spends inside a transaction decides how many server connections a crowd of clients needs:

SQL How many server connections do N clients need?

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

Two thousand mostly idle web clients need about 40 busy servers on average, and a pool of 60 gives headroom; 5,000 serverless clients with tiny transactions need only 25 on average, a pool of 38 (rounded). Compare that with the busy internal tool: its mere 100 clients still need about 50 server connections on average: pooling helps least when clients are genuinely busy. If the calculated figure exceeds what your CPU can run efficiently, the cure is faster queries or more hardware, not a bigger pool. A pool that is too small shows up as clients waiting (PgBouncer's SHOW POOLS lists cl_waiting), which is a visible, healthy signal compared with an overloaded database.

Operating a pooler

A few practical points:

  • Monitoring. Connect to PgBouncer's admin console (psql -p 6432 pgbouncer) and run SHOW POOLS;, SHOW STATS; and SHOW CLIENTS;. Alert on cl_waiting and on average wait time.
  • Authentication. PgBouncer needs to verify users itself: use auth_query to look up credentials in the database instead of maintaining a copy of password hashes by hand.
  • Failover. A pooler is another component to make highly available; see High Availability and Upgrades.
  • TLS. Configure TLS on both sides if traffic crosses a network.
  • Timeouts. query_wait_timeout and server_lifetime keep stuck clients and old connections from piling up.

An application pool and PgBouncer work together. Keep the application's own pool small (a handful of connections per process), let PgBouncer cap the total, and keep the database's max_connections a little above PgBouncer's pool sizes plus the direct administrative connections.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why is a PostgreSQL connection more expensive than a connection to a thread-based server?

    PostgreSQL forks a backend process for every connection, which costs startup time, memory and contention as the number grows.
  2. 2. Which PgBouncer mode shares server connections most aggressively among web requests?

    In transaction mode a server connection is lent only for the duration of a transaction, so many mostly idle clients can share few server connections.
  3. 3. What is a likely problem when using plain SET statements through transaction pooling?

    Session state belongs to a server connection, and transaction pooling reassigns connections between transactions. Use SET LOCAL or per-role settings.
  4. 4. An application serves 300 queries per second and each query uses a connection for 20 ms. About how many connections are busy on average?

    Little's law: 300 per second times 0.020 seconds is 6 concurrently busy connections on average, plus headroom for bursts.

Summary

  • PostgreSQL forks a process per connection, so thousands of mostly idle connections waste memory and CPU.
  • Pool in the application to save setup time, and in an external pooler such as PgBouncer to cap total connections.
  • PgBouncer modes: session, transaction (the usual choice) and statement.
  • Transaction pooling breaks session state: session SET, session advisory locks, LISTEN, temp tables; give such work a direct connection. PgBouncer 1.21+ supports protocol-level prepared statements when max_prepared_statements is set.
  • Size pools with Little's law (queries per second times seconds per query), not with the number of clients.
  • Prevent long-open transactions with idle_in_transaction_session_timeout, and monitor waiting clients.

Next: Declarative Partitioning.