Too Many Connections

In this lesson we diagnose and fix a common Postgres problem: your application exhausts available database connections. This typically appears during traffic spikes and can bring critical flows (like checkout) to a halt. Learn how to investigate live connections, apply immediate mitigations, fix the application pattern, and adopt architectural improvements to prevent recurrence. Scenario

Ten application servers.
One Postgres database.
Each server keeps a connection pool of 10 connections under normal load and can grow to 20 under heavy load.
Postgres is configured with max_connections = 200.

During a flash sale, traffic triples and Postgres logs this error:

postgres logs

FATAL:  sorry, too many clients already
# "clients" here means database connections from the app servers, not users

Your checkout is down. What’s the correct response? A common but hasty reaction is to raise the Postgres connection limit (for example, max_connections = 500) so the app servers can open more connections. That can buy time but has serious drawbacks: each Postgres connection maps to an OS process and consumes memory even when idle. Raising max_connections can cause the database to spend more time and memory managing processes than executing queries.

# postgresql.conf
max_connections = 500

Before changing max_connections, inspect what connections actually exist — often the real cause is different. In this scenario, the autoscaler added more servers during the spike. Four extra servers were provisioned, explaining the additional connections: 4 servers × 10 normal connections = 40 extra connections, bringing the usual total to ~140. Under peak load each server’s pool can grow to 20 connections, which may exhaust the 200-connection limit.

A dark infographic showing an autoscaler and many servers opening lots of connections converging on a Postgres database. Text highlights a 156/200 connection count and notes that under load each pool went from 10 to 20 connections.

Investigate live connections with pg_stat_activity Postgres exposes pg_stat_activity which lists every live connection and what it’s doing. Run this as a superuser or a role with sufficient access:

-- inspect connections (run as a superuser or a role with access)
SELECT pid, usename, client_addr, state, query_start, state_change, query
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY query_start DESC;

If many sessions show idle in transaction, that is a red flag: the application opened a transaction, then waited (for an external call, user input, etc.) while still holding the DB connection. Example problematic output:

idle in transaction
idle in transaction
idle in transaction
...

Root cause in this scenario

The checkout code begins a DB transaction, performs writes, then calls an external payment provider before committing.
The external call can be slow (tens of seconds), so the transaction holds the connection while doing no DB work.
Many concurrent checkouts holding transactions lead to connection exhaustion.

Fix this in stages Stage 1 — Immediate mitigation (bring the service back up)

Terminate stuck sessions so new checkouts can proceed. Postgres will roll back open transactions safely.

-- terminate a session by pid (from pg_stat_activity)
SELECT pg_terminate_backend(4022);

Configure an automatic timeout to prevent transactions remaining idle inside a transaction for too long. Use idle_in_transaction_session_timeout to kill sessions that stay idle in a transaction beyond a sensible threshold:

-- set a sensible timeout (example: 5 seconds)
ALTER SYSTEM SET idle_in_transaction_session_timeout = '5s';
-- then reload configuration
SELECT pg_reload_conf();

Setting idle_in_transaction_session_timeout prevents long-running idle in transaction sessions from accumulating. Choose a value that accommodates legitimate long transactions for your workload, but protects the server from resource exhaustion.

Stage 2 — Fix the application

Never hold a DB connection while waiting on a slow external operation. Commit and release the connection before making external network calls (e.g., payment provider). If you need to persist the external result, obtain a short-lived connection after the external call to write the result.

Example (Python / psycopg2-like pseudocode):

# BAD: holding a DB connection while waiting on an external service
with db_conn.cursor() as cur:
    cur.execute("BEGIN")
    cur.execute("UPDATE orders SET status = 'processing' WHERE id = %s", (order_id,))
    # still inside transaction -> hold connection while calling payment provider
    payment_response = call_payment_provider(...)
    cur.execute("UPDATE orders SET payment_result = %s WHERE id = %s", (payment_response, order_id))
    # commit on the connection (execute COMMIT is also possible, but call the connection's commit)
    db_conn.commit()

# GOOD: commit before calling external service
with db_conn.cursor() as cur:
    cur.execute("BEGIN")
    cur.execute("UPDATE orders SET status = 'processing' WHERE id = %s", (order_id,))
# commit to release connection back to the pool
db_conn.commit()

# connection returned to pool here; call external service
payment_response = call_payment_provider(...)

# use a new short-lived DB connection to save the result
with db_pool.connect() as conn:
    with conn.cursor() as cur:
        cur.execute("UPDATE orders SET payment_result = %s, status = %s WHERE id = %s",
                    (payment_response, 'complete', order_id))
        conn.commit()

Stage 3 — Architectural improvement: put a PgBouncer in front of Postgres

PgBouncer is a lightweight connection pooler/proxy that sits between your application and Postgres.
Applications can open many cheap client connections to PgBouncer while PgBouncer keeps a small, fixed pool of real Postgres connections.
In transaction pooling mode, PgBouncer assigns a real Postgres connection only for the duration of a transaction; once the transaction completes the connection returns to the pool. This dramatically reduces the number of concurrent Postgres processes.

A dark-themed diagram titled "Stage 3: A Bouncer at the Door" showing multiple client/server icons funneling many cheap connections into a central "PG BOUNCER" box, which then forwards through a small fixed pool of real connections to a Postgres database.

Be careful when using PgBouncer in transaction pooling mode: session-level features (temporary tables, session-local GUC settings, prepared statements) do not persist across pooled transactions. Some ORMs or application patterns require session pooling instead. Choose the pooling mode and application changes that match your workload.

Summary checklist

Step	Action	Example / Notes
Inspect connections	Query `pg_stat_activity` to see what sessions are doing	`SELECT * FROM pg_stat_activity WHERE datname = current_database();`
Kill stuck sessions	Terminate long `idle in transaction` sessions to free connections	`SELECT pg_terminate_backend(<pid>);`
Set timeouts	Prevent recurrence with `idle_in_transaction_session_timeout`	`ALTER SYSTEM SET idle_in_transaction_session_timeout = '5s';` + `SELECT pg_reload_conf();`
Fix application	Commit before external calls; use short-lived connections for follow-up writes	Refactor to avoid holding transactions across network I/O
Add pooler	Deploy PgBouncer in the appropriate pooling mode	Use transaction pooling to reduce real Postgres processes (if compatible)
Increase `max_connections`?	Only after measuring memory/CPU impact and applying mitigations	Raising limits can increase OS processes and memory usage per connection

Links and references

Follow this flow: observe before changing limits, mitigate quickly to restore service, fix the application pattern that holds connections, and adopt a pooler when appropriate. Only after you understand the memory and CPU implications should you consider increasing max_connections.

Docker

Kubernetes

CI/CD & DevOps

Security

System Design

Networking

Advanced Concepts

Interview Setup

Databases

Too Many Connections

Watch Video