Too many connections troubleshooter

This error occurs when the number of connected backends reaches max_connections (minus superuser_reserved_connections). For an immediate fix during an outage, connect as a superuser (the reserved connections are kept for this purpose) and identify what is consuming slots: run select state, count(*) from pg_stat_activity where backend_type = 'client backend' group by state;. The most common findings are: (1) hundreds of idle connections from an application without connection pooling or with a leaked pool — terminate them with select pg_terminate_backend(pid) from pg_stat_activity where state = 'idle' and state_change < now() - interval '10 minutes';, (2) many idle in transaction connections from abandoned transactions — terminate them similarly and set idle_in_transaction_session_timeout to prevent recurrence, or (3) a sudden spike in active connections from a traffic surge — this requires connection pooling (PgBouncer) as the long-term fix. Do not simply raise max_connections as a permanent solution. Each backend consumes memory and OS resources; PostgreSQL performs poorly with hundreds of truly active connections due to lock contention, context switching, and buffer pool pressure. The sustainable fix is to place a connection pooler in front of PostgreSQL and keep max_connections at a reasonable value (100-300 for most workloads).

Transaction pooling mode (pool_mode = transaction) provides the best connection multiplexing because the server connection is returned to the pool after each transaction completes. This means 2000 application connections can share 25 server connections, as long as they are not all executing transactions simultaneously. However, transaction mode has restrictions: you cannot use session-level features like SET commands (use SET LOCAL inside transactions instead), PREPARE/EXECUTE for prepared statements (use PgBouncer's prepared_statement_mode with version 1.21+), LISTEN/NOTIFY, temporary tables that persist across transactions, or advisory locks held across transactions. Session pooling mode (pool_mode = session) assigns a server connection for the entire client session, which supports all PostgreSQL features but provides much less multiplexing — a connection is only reused after the client disconnects. For most web applications and microservices, transaction mode is the correct choice because each HTTP request runs one or a few transactions and does not need session-level state. For long-lived connections that use session features (like background workers using LISTEN/NOTIFY), either use session mode for those specific pools or connect directly to PostgreSQL, bypassing PgBouncer.

Connection leaks happen when application code obtains a connection from the pool but fails to return it — typically because an exception is thrown before the close() or release() call, or because a code path simply forgets to release. The leaked connection sits idle in PostgreSQL until the process exits or a timeout fires. To detect leaks, query pg_stat_activity for connections that have been idle for an unusually long time: select pid, usename, application_name, client_addr, now() - state_change as idle_time from pg_stat_activity where state = 'idle' and now() - state_change > interval '30 minutes' order by idle_time desc;. If you see connections idle for hours from an application that should only hold connections briefly, you have a leak. To identify the leaking code, use application_name (set it in your connection string to include the service name or version) and client_addr to narrow down which instance is leaking. In your application code, always use try-with-resources (Java), context managers (Python with), or ensure blocks (Ruby) to guarantee connections are returned. Configure your ORM pool with aggressive leak detection: SQLAlchemy pool_recycle=1800 and pool_pre_ping=True, HikariCP leakDetectionThreshold=60000, Django CONN_MAX_AGE=300. On the PostgreSQL side, set tcp_keepalives_idle=60, tcp_keepalives_interval=10, and tcp_keepalives_count=6 to detect dead TCP connections within about two minutes instead of the OS default of two hours.

Each PostgreSQL backend process allocates memory for work_mem (used per sort/hash operation, can be multiple per query), temp_buffers (for temporary tables), catalog caches, and various per-session state. A completely idle connection uses roughly 5-10 MB, but an active connection running a complex query with multiple sort operations can easily use 50-200 MB or more (since work_mem is allocated per operation node, not per connection). The formula for a safe ceiling is: max_connections * estimated_memory_per_backend + shared_buffers + OS_needs < total_RAM. For a server with 16 GB RAM, 4 GB shared_buffers, and assuming 10 MB average per backend, you could support around 400 connections before memory pressure becomes a concern — but having 400 *active* connections will cause severe CPU and lock contention. A better approach: set max_connections to 200-300 and use PgBouncer to handle the application-side connection count. The superuser_reserved_connections setting (default 3, recommend 5) keeps slots available for emergency access. For cloud-managed PostgreSQL (RDS, Cloud SQL), the provider sets max_connections based on instance memory, but connection pooling is still essential. PostgreSQL 14+ introduced min_dynamic_shared_memory and other improvements that reduce per-backend overhead, but the fundamental advice remains: fewer active connections with pooling outperforms many direct connections.