PostgreSQL without available connections: diagnosis and connection pooling with PgBouncer
Published on 2026-11-14 by frfrand
Identify connection exhaustion in PostgreSQL, investigate pg_stat_activity and decide when to configure pooling and PgBouncer.

Your application starts responding with PostgreSQL connection errors just as traffic grows. Increasing max_connections seems like an immediate solution, but it can worsen memory pressure. Before doing so, identify who opens connections, how long they last, and whether they are actually working.
1. Verify the problem
Check the limit and activity with an authorized account:
SHOW max_connections;
SHOW superuser_reserved_connections;
SELECT state, count(*) AS conexiones
FROM pg_stat_activity
GROUP BY state
ORDER BY conexiones DESC;
Review application errors, number of instances, processes, connections per worker, and peak history. Not all visible connections are simultaneously active queries: there may be idle sessions occupying resources without doing any work.
2. Find blocked or abandoned connections
A basic observation query:
SELECT pid, usename, application_name, state,
now() - state_change AS tiempo_en_estado,
wait_event_type
FROM pg_stat_activity
WHERE backend_type = 'client backend'
ORDER BY tiempo_en_estado DESC
LIMIT 30;
Avoid terminating processes without investigating: you could interrupt legitimate transactions. Check idle in transaction because it keeps open transactions that can affect locks and maintenance.
3. Calculate maximum connections for your architecture
A rule of thumb is:
conexiones potenciales = instancias de API × pool por instancia
+ workers × pool de cada worker
+ tareas administrativas
Do not confuse the maximum pool size with its constant use. If you scale from two to ten instances and each opens 20 connections, the potential ceiling jumps from 40 to 200 before counting other services.
4. What PgBouncer does
PgBouncer receives connections from clients and reuses server connections. In transaction pooling mode it lends a PostgreSQL connection per transaction, allowing many logical clients with fewer physical backends. However, functions that depend on session state, temporary tables, prepared statements, or other behaviors may require configuration or changes. Check your driver and version compatibility.
Do not enable transaction pooling blindly: test migrations, explicit transactions, sessions, and long-running tasks. For some cases, session pooling or a pool within the application itself is more convenient.
5. Do not hide slow queries behind a pool
If all connections execute expensive queries, PgBouncer only queues more work. Review query times, indexes, locks, and p95; add wait limits and saturation metrics. A pool should control concurrency and stability, not replace SQL optimization.
6. Safe plan
1. Measure connections and states during peak hours. 2. Fix leaks, open transactions, and poorly sized pools. 3. Test PgBouncer with representative traffic. 4. Deploy gradually with alerts and rollback capability.
Complementa con EXPLAIN ANALYZE for PostgreSQL, SSH tunnel for PostgreSQL and PostgreSQL on ArduMaker.
Frequently Asked Questions
### Is increasing max_connections enough? No if the cause is a leak, uncontrolled scaling, or blocked queries.
Does PgBouncer make queries faster?
Not directly: it reduces connection pressure and can improve stability.
Do I need pooling if I have few users?
It depends on the number of processes, pool size, and usage pattern.
References: pg_stat_activity, PgBouncer.