Connections & Pooling
Most important commands to remember
SHOW POOLS— inspect clients, server connections, and waiting work.SHOW STATS— inspect pooled transaction and query statistics.
Commands and flags
| Command or option | Meaning |
|---|---|
psql -X |
Connect without executing psql startup files. |
-h localhost -p 6432 |
Select the existing local pooler endpoint. |
-d pgbouncer |
Connect to PgBouncer’s virtual administration database. |
-c 'SHOW POOLS;' |
Execute this inspection command and exit. |
-c 'SHOW STATS;' |
Read statistics, then exit. |
These SHOW commands belong to PgBouncer, not ordinary PostgreSQL SQL. Authentication uses your configured test account. Output fields vary by PgBouncer version; inspect their names rather than assuming fixed column positions.
The concepts that matter
1. A connection is a resource, not just a socket
Opening a database connection involves transport setup, authentication, and server-side state. An application that repeatedly connects for every small query adds avoidable setup work.
A connection pool retains reusable connections and lends access to callers. An application can pool locally, and a proxy such as PgBouncer can multiplex many clients onto fewer database connections. Those are separate layers whose limits must be considered together.
2. A full pool makes requests wait
A bounded pool cannot hand out unlimited connections. When its available capacity is occupied, callers may wait, time out, or be rejected under the configured policy.
Waiting can indicate excessive concurrency, slow queries, or transactions holding connections too long. Increasing the pool size may move the queue into the database and increase contention. The useful target is enough concurrency for the workload, not the largest possible connection count.
3. Pooling mode determines which state follows a client
Session pooling keeps a server connection assigned for a client session. Transaction pooling returns it to the pool after each transaction, allowing another client to use it.
That changes assumptions about session state. Temporary objects, session settings, and other features need compatibility checks for the actual pooler mode and version. Prepared-statement support is also implementation and configuration dependent. Do not assume all session behavior survives because ordinary SELECT queries work.
4. Measure waiting separately from execution
A slow request can spend time waiting for a pool slot before the database begins its query. Query execution time alone then understates what the user experienced.
Inspect client waiting counts, server connection states, transaction duration, and request latency together. Statistics aggregated over many operations can hide occasional long waits. Two snapshots help reveal movement, but counters by themselves do not establish why a particular request was slow.
One small example
Optional: inspect the existing authorized pooler. These commands read its administration views and do not change connection limits, restart services, or issue application queries.
psql -X -h localhost -p 6432 -d pgbouncer -c 'SHOW POOLS;'
psql -X -h localhost -p 6432 -d pgbouncer -c 'SHOW STATS;'In SHOW POOLS, compare cl_waiting with server states such as sv_active and sv_idle, and check pool_mode. maxwait, where present, reports the oldest current client wait in seconds; associated fractional fields may also appear.
SHOW STATS separates cumulative counts from recent average fields. Timing fields such as avg_query_time use microseconds; consult their names before treating a value as milliseconds or a total. An idle lab may show zeros. That is a valid snapshot, not a benchmark of pool capacity. The connections close after each command.
Keep this idea: A pool reuses scarce database connections; its size and mode determine where callers wait and which session assumptions remain valid.