Database Connection Pool Exhaustion Prompt

This prompt is designed for backend reliability engineers and SREs actively investigating a production incident where the primary symptom is database connection pool saturation. The ideal user has access to real-time or recent pool metrics (active, idle, pending counts), query latency percentiles, and the application source code responsible for managing the connection lifecycle. The core job-to-be-done is to move beyond surface-level metrics and correlate runtime signals with specific code patterns—such as connections opened but not closed in a finally block, transactions held open across an external API call, or a missing connection timeout setting—to identify the precise leak source or bottleneck. The prompt instructs an AI coding agent to perform this correlation and produce a concrete, actionable remediation plan, including a data-driven pool sizing recommendation.

You should not use this prompt when you lack direct access to the application's source code or when the relevant pool metrics are unavailable. The analysis depends entirely on the agent's ability to cross-reference observed behavior (e.g., a steadily climbing ActiveConnections count) with implementation details (e.g., a connection acquisition point in a loop). If you only have a database server's perspective without application-level telemetry, the agent cannot trace the leak to its origin in code. Similarly, this prompt is not a substitute for a database administrator's performance tuning of the server itself; it is specifically for diagnosing exhaustion caused by the application client. For high-risk production environments, the output must be treated as a strong hypothesis requiring human validation before any configuration or code change is deployed.

Before using this prompt, assemble a context package: a time-series snapshot of pool metrics, a sample of slow query logs from the saturation window, and the relevant code paths that acquire and release connections. The prompt's effectiveness is directly proportional to the quality of this input. After receiving the AI agent's output, expect a structured report that identifies a primary leak candidate, a secondary long-running transaction pattern, and a recommended pool size with justification. The next step is to have a senior engineer review the agent's code annotations and proposed patch before opening a pull request. Avoid the temptation to blindly apply the pool sizing recommendation without understanding the underlying fix; increasing the pool size without resolving a leak only delays the next exhaustion event.

This prompt template is designed to be pasted directly into an AI coding agent with access to your repository, database metrics, and configuration files. It instructs the agent to systematically correlate connection pool saturation signals with application code patterns, identifying leak sources and recommending sizing adjustments. Replace every square-bracket placeholder with real data from your incident context before execution. The prompt assumes the agent has read access to source code, database configuration files, and recent deployment or change logs.

textCopy
You are an SRE investigating a database connection pool exhaustion incident. Your task is to identify the root cause of pool saturation and produce a structured diagnosis with remediation steps.

## Input Data
- Pool metrics snapshot: [POOL_METRICS_JSON_OR_TEXT]
- Query latency percentiles during incident: [QUERY_LATENCY_DATA]
- Connection lifecycle events (acquire, release, timeout): [CONNECTION_LIFECYCLE_LOG]
- Application configuration for connection pooling: [POOL_CONFIG_FILE_CONTENTS]
- Relevant source code paths to search: [SOURCE_CODE_PATHS]
- Recent deployments or configuration changes: [RECENT_CHANGES_LOG]
- Database server-side connection count during incident: [DB_SERVER_CONNECTION_COUNT]

## Output Schema
Return a JSON object with the following structure:
{
  "diagnosis": {
    "primary_cause": "leak|long_running_transaction|pool_undersizing|connection_storm|other",
    "confidence": "high|medium|low",
    "evidence_summary": "string summarizing key evidence"
  },
  "leak_sources": [
    {
      "file_path": "string",
      "function_or_block": "string",
      "leak_mechanism": "unclosed_connection|transaction_not_committed|missing_finally_block|async_leak|other",
      "code_snippet": "string",
      "estimated_contribution_percent": number
    }
  ],
  "pool_sizing_recommendation": {
    "current_max": number,
    "recommended_max": number,
    "rationale": "string",
    "risks_of_change": "string"
  },
  "immediate_mitigations": ["string"],
  "long_term_fixes": ["string"],
  "requires_human_review": true_or_false
}

## Constraints
- Ground every leak source claim in specific code locations found in the repository. Do not speculate without code evidence.
- If pool metrics show connections accumulating without release, prioritize leak detection over sizing recommendations.
- If query latency is high but pool utilization is low, consider long-running transactions or lock contention as alternative causes.
- Cross-reference connection lifecycle events with application code paths that acquire connections.
- Flag any code path where connection release is conditional on exception handling without a finally block or equivalent.
- If confidence is "low," set requires_human_review to true and explain what additional data would increase confidence.

## Risk Level: [HIGH|MEDIUM|LOW]
- If HIGH: Production database availability is at risk. Prioritize immediate mitigations and require human review before applying fixes.
- If MEDIUM: Performance degradation is occurring but not yet critical. Balance speed and thoroughness.
- If LOW: Early warning signals. Focus on long-term fixes and sizing adjustments.

## Tools Available
- Code search across repository: use grep or semantic search to find connection acquisition and release patterns.
- Configuration file reading: inspect pool configuration files for max connections, timeout settings, and validation queries.
- Log analysis: parse connection lifecycle logs for acquire/release timing and timeout events.

## Examples of Leak Patterns to Detect
1. Connections opened in try blocks without finally-block release.
2. ORM sessions created but not closed after request completion.
3. Async connection pools where acquire is not paired with release in all code paths.
4. Transaction objects committed or rolled back but the underlying connection not returned to pool.
5. Connection pools shared across threads without proper thread-local handling.

After pasting this prompt, verify that the agent has access to all referenced data sources. If the agent cannot access live metrics, provide a snapshot file. If the repository is large, narrow [SOURCE_CODE_PATHS] to the services or modules most likely to manage database connections. For high-risk production incidents, always set requires_human_review to true in the output and validate the agent's code-level findings against your own code review before applying any fixes. Run this prompt in a read-only agent session first to avoid unintended modifications to configuration files.

This prompt is designed to be called by an automation harness during an active incident or as a scheduled diagnostic check. The harness must gather the required inputs—pool metrics, query latency percentiles, connection lifecycle logs, and relevant application code—before invoking the model. Because the output includes a leak source identification and pool sizing recommendation that could trigger automated remediation, the harness should treat the model's output as a structured hypothesis, not a direct command. The prompt's [OUTPUT_SCHEMA] placeholder expects a JSON contract with fields for leak_source, confidence, evidence, recommended_pool_size, and requires_human_review. Your harness must validate that the model returns valid JSON conforming to this schema before any downstream action is taken.

Wire the prompt into your observability stack by triggering it when connection pool utilization exceeds a defined threshold (e.g., 80% for more than 5 minutes) or when connection timeout errors spike. The harness should assemble the [INPUT] block from your metrics store (Prometheus, Datadog), your APM traces for query latency, and your connection lifecycle logs from the application or pool library (HikariCP, pgBouncer). For the [CONTEXT] placeholder, use a RAG retrieval step that pulls the relevant database interaction code—look for files containing getConnection, DataSource, or ORM session management patterns. This retrieval step is critical because the model needs to correlate pool exhaustion symptoms with specific code patterns like missing finally blocks, unclosed result sets, or transaction boundaries that span non-DB work. Log the full prompt and response to your incident channel for postmortem review.

Add a validation layer after the model responds. Check that leak_source references a specific file path and line number range present in your repository. If confidence is below 0.7, route the output to a human on-call engineer via your incident management tool (PagerDuty, Opsgenie) rather than applying any automated fix. For the pool sizing recommendation, compare the suggested value against your current configuration and flag any change greater than 50% for human approval. Implement a retry with backoff if the model returns malformed JSON or fails to include all required fields; after three retries, escalate to a human. Do not allow the harness to execute ALTER DATABASE or pool configuration changes without explicit human approval, even if the model's confidence is high. The prompt is a diagnostic tool, not a self-healing controller.