Section 1 of 3 · 6 minutes
Measure connection use before raising limits
Your goal
What you will be able to do
Count connections by state, role, and application before changing max_connections.
Why this matters at work
The practical reason
A high connection count may come from a leak, poor pooling, or idle clients rather than a genuine capacity need.
Learn
The idea in plain English
`max_connections` limits concurrent server sessions. Each session uses a backend process and some memory, even when it is idle.
Group current sessions by application, role, and state. Compare normal demand, incident peaks, and the reserved operator capacity. Fix a connection leak or pool setting before increasing the server limit.
Remember these points
- Count who is connected and what state they are in.
- Keep capacity for monitoring and emergency operator access.
- Use a pool to limit server sessions when client concurrency is higher.
See it in SQL
Summarise connection use
Find which application and role consume server sessions.
SELECT
coalesce(application_name, '') AS application_name,
usename AS role_name,
state,
count(*) AS connection_count
FROM pg_stat_activity
GROUP BY application_name, usename, state
ORDER BY connection_count DESC;The result separates active, idle, and idle-in-transaction sessions. Empty application names are a tracing gap worth fixing.
A large idle group may be healthy pooling or waste; compare it with pool configuration and workload demand.
Connection counts grouped by application, role, and session state, largest groups first.
Useful words
Important terms
- Connection pool
- A service that shares a limited set of database sessions across many client requests.
Quick check · Not graded
Check your understanding
What should happen before max_connections is increased?
Your progress is saved to your signed-in account.