The symptom

Your application starts logging "too many connections" or "connection timeout". New requests queue or fail. Existing queries finish normally — the database itself is not slow. The connection pool is the bottleneck.

This post walks through the exact diagnosis path: which system views to query, what the numbers mean, and the three most common fixes.

Step 1: read pg_stat_activity

SELECT
  state,
  wait_event_type,
  wait_event,
  COUNT(*) AS sessions
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
GROUP BY state, wait_event_type, wait_event
ORDER BY sessions DESC;

The output tells you where sessions are actually stuck:

state = 'idle' — connection is open but doing nothing. Classic pool leak or oversized pool.
state = 'idle in transaction' — a transaction was opened and never committed or rolled back. High risk: it holds locks and blocks autovacuum.
state = 'active' — query is running. If this count is high, you have a throughput problem, not a pool problem.
wait_event_type = 'Lock' — sessions are blocked waiting for a row or table lock.

A healthy production database has mostly 'idle' connections (your pool's min_size) and a small active count proportional to your server's CPU cores. If you see dozens of 'idle in transaction' sessions, that is the problem to solve first.

Step 2: check max_connections headroom

SELECT
  max_conn,
  used,
  max_conn - used AS available
FROM
  (SELECT setting::int AS max_conn FROM pg_settings WHERE name = 'max_connections') mc,
  (SELECT COUNT(*) AS used FROM pg_stat_activity) ua;

Rule of thumb: once you are above 80% of max_connections, query latency rises because Postgres spends CPU on connection management. The Linux kernel also pays overhead per connection (stack allocation, memory mapping). On a t3.small, max_connections = 100 is a reasonable ceiling. On an RDS db.t3.micro, Postgres sets it to 87 by default.

Raising max_connections is rarely the right fix. Each connection consumes ~5 MB of shared memory. Raising from 100 to 500 costs 2 GB of RAM that could be used for shared_buffers and the OS page cache.

Step 3: check if your application is leaking connections

Run this query every 30 seconds and watch the counts:

SELECT
  application_name,
  state,
  COUNT(*) AS sessions,
  MAX(EXTRACT(EPOCH FROM (NOW() - state_change))) AS oldest_state_seconds
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
GROUP BY application_name, state
ORDER BY sessions DESC;

If 'idle' session count grows over time without a corresponding increase in traffic, you have a pool leak. Common causes:

1. Missing connection release in error paths. In Python, a with db.connection() block that raises an exception before the context manager exits. 2. Wrong pool library configuration. In Node.js with pg, forgetting to call client.release() after pool.connect(). 3. Long-lived Lambda or serverless functions that open a connection per invocation and never reuse the pool.

Step 4: configure PgBouncer

PgBouncer is a lightweight connection pooler that sits between your application and Postgres. It maintains a small number of actual server connections and multiplexes thousands of client connections over them.

A minimal pgbouncer.ini for a bootstrapped SaaS running on a single EC2 box:

[databases]
app = host=127.0.0.1 port=5432 dbname=app

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

server_idle_timeout = 600
client_idle_timeout = 0

Key settings to understand:

pool_mode = transaction — PgBouncer returns the server connection to the pool after each transaction, not after each client disconnect. This gives the best multiplexing ratio. Note: session-level features (SET LOCAL, advisory locks, LISTEN/NOTIFY, prepared statements with PREPARE) do not work in transaction mode. Use pool_mode = session if you need those.
default_pool_size = 25 — number of actual Postgres connections PgBouncer maintains. A common starting point is 2 * num_cores + 1 on the Postgres host. For a db.t3.small (2 vCPU), that is 5. For a db.m5.large (2 vCPU dedicated), 25 is reasonable with headroom for admin connections.
max_client_conn = 1000 — maximum client connections to PgBouncer. This can be much larger than max_connections because PgBouncer handles them in userspace.

Supabase-specific gotchas

Supabase exposes two connection strings:

1. Direct connection (port 5432) — goes straight to Postgres. max_connections applies. Use for migrations and long-running admin tasks. 2. Pooler connection (port 6543) — goes through Supavisor (Supabase's managed PgBouncer fork). Use for application queries.

If you are hitting connection exhaustion on Supabase, check which port your application is using. Switching from port 5432 to 6543 is often the entire fix.

Supabase also sets max_connections based on your plan. On the free plan it is 60. On Pro it is 200. You can check your current usage with:

SELECT COUNT(*) FROM pg_stat_activity;

Compare that number to the limits in the Supabase dashboard under Settings > Database.

Another Supabase gotcha: database branching. Each branch is a separate Postgres instance with its own max_connections. If your CI pipeline spins up 20 branches and each runs integration tests with a pool of size 10, you have 200 connections before any production traffic.

insightral PG-R13: connection saturation detection

insightral's rule PG-R13 fires when the ratio of used connections to max_connections crosses 80%. The finding includes the exact count, the threshold, and the three longest-running idle sessions. You do not need to remember to run these queries manually — insightral polls every 5 minutes and sends a Slack alert before you hit the wall.

The detection query is:

SELECT
  (SELECT COUNT(*) FROM pg_stat_activity) AS used,
  (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_conn,
  ROUND(
    100.0 * (SELECT COUNT(*) FROM pg_stat_activity) /
    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections'),
    1
  ) AS pct_used;

Summary

1. Query pg_stat_activity grouped by state to find what sessions are doing. 2. Check your headroom against max_connections — above 80% is a warning sign. 3. Look for growing 'idle' counts over time (pool leak) or high 'idle in transaction' counts (uncommitted transactions). 4. Add PgBouncer in transaction mode between your app and Postgres. Set pool size to 2 * server_cores + 1. 5. On Supabase: use the pooler port (6543), not the direct port (5432), for application connections.

Raising max_connections buys you time but does not fix the underlying issue. The database can only do useful work on queries that are actually running; idle connections are overhead.