What is this issue?
An idle connection is connected to the database but not executing any query.
This is normal for brief periods, but connections idle for 10+ minutes may be:
- Abandoned by a crashed application
- Leaked by improper connection handling
- Held unnecessarily by connection pool misconfiguration
Unlike "idle in transaction," these connections don't hold locks but still
consume memory and connection slots.
Why it matters
Wasted Resources
Each connection uses 5-10 MB of memory
Connection Slot
Takes a slot that could serve active requests
Pool Inefficiency
May indicate connection pool misconfiguration
Potential Leak
May indicate application bugs not releasing connections
How PG Pilot detects it
```sql SELECT pid, usename, application_name, client_addr, state, state_change, age(now(), state_change) AS idle_duration FROM pg_stat_activity WHERE state = 'idle' AND state_change < now() - interval '10 minutes' ORDER BY state_change; ```
How to fix it
Identify the source
Check which application is holding idle connections:
SELECT application_name, client_addr, count()
FROM pg_stat_activity
WHERE state = 'idle'
GROUP BY application_name, client_addr
ORDER BY count() DESC;Review application connection handling
Verify the application:
- Uses connection pooling
- Releases connections after use
- Has appropriate pool idle timeouts
Terminate if abandoned
Close connections idle for too long:
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
AND state_change < now() - interval '30 minutes';Consider server-side timeout
Set automatic idle connection timeout (PostgreSQL 14+):
ALTER SYSTEM SET idle_session_timeout = '30min';
SELECT pg_reload_conf();Prevention
- Configure connection pool idle timeouts
- Use PgBouncer with appropriate pool modes
- Implement connection health checks
- Set server-side idle_session_timeout