Pg Connection Analysis

PostgreSQL Connection Analysis

This skill guides analysis and optimization of PostgreSQL connection usage using pgtuner-mcp tools.

When to Use This Skill

Use this skill when the user:

• Reports "too many connections" errors or connection refused
• Asks about connection pooling or PgBouncer setup
• Sees high connection counts in monitoring
• Reports idle connections consuming resources
• Asks "how many connections is my database using?" or "do I need a connection pooler?"
• Reports memory pressure related to per-connection overhead

MCP Resources Available

• pgtuner://health/connections -- Quick connection utilization check (lightweight)
• pgtuner://settings/connections -- Connection-related configuration

Related MCP Prompt

This skill supplements the health_check MCP Prompt's connection dimension.

Prerequisites

• The pgtuner-mcp server must be connected with a valid DATABASE_URI

Agent Decision Tree

What is the user's symptom?
  |
  +--> "Too many connections / connection refused"
  |    --> Start with Step 1 (Connection State Breakdown), urgent
  |
  +--> "Should I use connection pooling?"
  |    --> Start with Step 1, then Step 3 (Pooling Analysis)
  |
  +--> "Idle connections eating memory"
  |    --> Start with Step 1 (focus on idle), then Step 4 (Configuration)
  |
  +--> "General connection health question"
       --> Start with Quick Check (MCP Resource), then Step 1 if needed

Connection Analysis Workflow

Quick Check: Use MCP Resource First

Before running full tool queries, the agent can read the lightweight resource:

Read resource: pgtuner://health/connections

This provides the connection utilization ratio without running the full health check tool. If this shows > 80% utilization, proceed with the full workflow.

Step 1: Connection State Breakdown

Tool: get_active_queries
Parameters:
  include_idle: true
  include_system: true
  min_duration_seconds: 0

Categorize all connections:

Agent reasoning:

For multi-database setups, filter by database:

Tool: get_active_queries
Parameters:
  include_idle: true
  database: "<specific_database>"

Step 2: Connection Duration Analysis

Tool: get_active_queries
Parameters:
  include_idle: true
  min_duration_seconds: 300

Long-lived connections (> 5 minutes) that are idle may indicate:

• Connection pooler in session mode: Normal (connections are held open)
• No connection pooler: Possible leak. Application should close connections when done.
• Long idle in transaction: Application bug. Most harmful pattern.

Then check for truly stuck sessions:

Tool: get_active_queries
Parameters:
  include_idle: true
  min_duration_seconds: 3600

Sessions idle for > 1 hour are almost always leaks or misconfigured poolers.

Step 3: Connection Pooling Assessment

Evaluate whether a connection pooler is needed based on the data:

Signs you need a connection pooler:

Connection pooler recommendations:

Memory calculation:

Per-connection RAM = ~10MB (shared_buffers mappings + work_mem potential + catalog cache)
100 idle connections = ~1GB wasted RAM
With PgBouncer: 20 active connections = ~200MB
Savings: ~800MB returned to OS cache (improves effective_cache_size)

Step 4: Configuration Review

Tool: review_settings
Parameters:
  category: "connections"
  include_all_settings: false

Key settings to evaluate:

Step 5: Wait Events for Connection Issues

Tool: analyze_wait_events
Parameters:
  active_only: false

Connection-related wait events:

Many Client:ClientRead waits suggest network latency between application and database. Consider:

• Co-locating application and database
• Using connection pooler close to the application

Output Format

Connection Analysis Report

Summary:

Connection State Distribution:

active:                 ||||||||| 15 (15%)
idle:                   |||||||||||||||||||||||||||||||||||||||||||| 70 (70%)
idle in transaction:    |||||||||| 12 (12%)
idle in txn (aborted):  || 3 (3%)

Risk Assessment:

• Connection saturation risk: Low / Medium / High / Critical
• Memory waste from idle connections: ~X MB
• Idle-in-transaction impact: None / Minor / Significant

Recommendations:

1. ...
2. ...

When to Stop and Ask the User

• Before recommending PgBouncer: "Is a connection pooler already in use? If so, which one and in what mode?"
• Before recommending max_connections reduction: "What is the peak connection count? Reducing max_connections during high traffic could cause connection refused errors."
• If application-level changes are needed: "The idle-in-transaction problem is caused by application code. The application needs to ensure transactions are committed or rolled back promptly. Would you like specific guidance for your framework?"
• If managed database: "Are you on a managed service (RDS, Cloud SQL)? Connection limits may be tied to instance size and not directly configurable."

PostgreSQL Version Notes

Production Safety

• All diagnostic queries are read-only
• pg_terminate_backend() should only be used for clearly leaked connections
• Changing max_connections requires a PostgreSQL restart
• idle_in_transaction_session_timeout is reload-only (pg_reload_conf())
• TCP keepalive settings are reload-only