Database Connection Pool Exhaustion Analysis Prompt

This prompt is designed for backend engineers and SREs who are actively responding to an application outage where the immediate symptom is database connection pool exhaustion. The ideal user has already identified the symptom—typically through APM dashboards or monitoring alerts showing Timeout waiting for idle object, Active connections: [MAX], or a flatline in application throughput—but has not yet isolated the root cause. The prompt's job is to accelerate the transition from symptom to root cause by analyzing raw connection pool logs, relevant application stack traces, and database performance metrics to distinguish a slow connection leak from a legitimate traffic spike or a sudden blocking operation.

To use this prompt effectively, you must provide structured, timestamped data from three sources: the connection pool logs showing connection acquisition/release events and timeouts, application traces or thread dumps that reveal what the code was doing while holding connections, and database metrics such as active queries, locks, and transaction duration. The prompt is not a replacement for your APM or connection pool monitoring dashboard. It is a force multiplier when those tools have surfaced the symptom but the root cause remains ambiguous. Do not use this prompt for proactive capacity planning, general performance tuning, or as a substitute for setting proper connection pool sizes and timeouts in your application configuration.

Before executing this prompt, ensure you have isolated the time window of the incident and gathered the relevant logs. The prompt will produce a structured analysis identifying the specific query or code path leaking connections, the blocking operation preventing connection return, and a short-term mitigation. However, the output is a diagnostic hypothesis, not a guaranteed fix. Always validate the identified code path against your source code and consider the long-term fix separately. If the data is incomplete or the incident is ongoing, prioritize restoring service with a pool size increase or connection timeout reduction before running a full analysis.

This template is designed to be the core analytical engine for diagnosing database connection pool exhaustion. It forces the model to differentiate between a legitimate traffic spike and a connection leak by requiring evidence from logs, pool metrics, and application traces. The output is structured to deliver an immediate mitigation step and a root cause hypothesis, not a generic troubleshooting guide. Before using this prompt, ensure you have gathered the relevant data sources; the model's analysis is only as good as the input context.

textCopy
You are an SRE specializing in database performance and backend failure analysis. Your task is to diagnose a database connection pool exhaustion event.

## INPUT DATA
- **Pool Metrics:** [POOL_METRICS]
- **Application Logs (anonymized):** [APP_LOGS]
- **Relevant Code Context (connection lifecycle):** [CODE_CONTEXT]
- **Deployment/Config Change Timeline:** [CHANGE_TIMELINE]

## ANALYSIS INSTRUCTIONS
1.  **Triage:** First, determine if the exhaustion is from a legitimate traffic spike or a connection leak. A leak is indicated by a growing number of active connections without a proportional increase in request rate, or connections stuck in an 'idle in transaction' state.
2.  **Identify the Leak:** If a leak is suspected, pinpoint the specific code path or query pattern. Look for connections acquired but not released in `finally` blocks, or long-running transactions that hold connections without activity.
3.  **Identify the Blocker:** If a spike is suspected, identify the slowest queries or blocking operations that are preventing connections from being recycled quickly.
4.  **Propose Mitigation:** Provide an immediate, short-term mitigation (e.g., a query kill command, a feature flag to disable a non-critical path, a temporary pool size increase with a caveat).
5.  **Propose Root Cause Fix:** Provide a long-term code or configuration fix.

## OUTPUT FORMAT
You must respond with a valid JSON object and nothing else. Do not include markdown fences.
{
  "exhaustion_type": "LEAK" | "TRAFFIC_SPIKE" | "INCONCLUSIVE",
  "confidence": "HIGH" | "MEDIUM" | "LOW",
  "evidence": [
    {
      "source": "pool_metrics | app_logs | code_context",
      "observation": "string",
      "interpretation": "string"
    }
  ],
  "leaking_code_path_or_blocking_query": "string | null",
  "immediate_mitigation": {
    "action": "string",
    "command_or_config": "string | null",
    "risk": "string"
  },
  "root_cause_fix": {
    "description": "string",
    "code_change_suggestion": "string | null",
    "configuration_change": "string | null"
  },
  "requires_human_review": true
}

## CONSTRAINTS
- Never suggest a permanent fix that simply increases the pool size without addressing the underlying leak or bottleneck.
- If the evidence is insufficient to determine the cause, set `exhaustion_type` to `INCONCLUSIVE` and `confidence` to `LOW`. Do not guess.
- The `immediate_mitigation.risk` field must honestly describe the potential side effects (e.g., 'may drop in-flight requests', 'increases database memory pressure').

To adapt this template, replace the [PLACEHOLDER] variables with raw data from your observability stack. For [POOL_METRICS], paste a snapshot of your connection pool dashboard showing active, idle, and pending connections. For [APP_LOGS], include log lines surrounding the timeout errors, ensuring any PII is redacted. The [CODE_CONTEXT] should be the specific functions where connections are acquired and released. After running the prompt, validate the JSON output against a schema validator in your harness. If requires_human_review is true or confidence is LOW, route the analysis to an on-call engineer instead of automatically applying the mitigation.

This prompt is designed to be integrated into an automated incident response or debugging pipeline, not just used as a one-off chat. The primary integration point is a webhook or event-driven function that triggers when a connection pool exhaustion alert fires (e.g., from Datadog, Prometheus, or PagerDuty). The harness must gather the required inputs—the raw connection pool logs, application traces for the affected time window, and the pool configuration—and assemble them into the [INPUT] placeholder. A pre-processing step should sanitize any sensitive data like full connection strings or user PII before the prompt is assembled, using a redaction library or regex patterns to replace them with placeholders like [REDACTED_HOST].

The application layer should wrap the LLM call in a retry loop with exponential backoff, catching ValidationError exceptions from the output parser. The model's response must be parsed into a strict JSON schema that includes fields for leak_source (the specific query or code path), blocking_operation, evidence, mitigation_steps, and a confidence_score (0.0 to 1.0). If the JSON is malformed or missing required fields, a repair prompt should be invoked once before failing over to a human on-call engineer. Log every raw prompt, raw response, and parsed output to an immutable audit log for post-incident review. For model choice, a fast, instruction-following model like GPT-4o or Claude 3.5 Sonnet is appropriate; avoid smaller models that may hallucinate code paths under the pressure of complex log data.

The most critical failure mode to guard against is the model confusing a legitimate traffic spike with a connection leak. To mitigate this, the harness should inject a [CONSTRAINTS] block that forces the model to explicitly compare the connection acquisition rate to the request rate. If they are proportional, the model must output a leak_source of null and a confidence_score below a configurable threshold (e.g., 0.6), which the harness should interpret as a signal to suppress the automated diagnosis and escalate to a human. Do not allow the system to automatically execute the model's suggested mitigation steps, such as restarting a connection pool or killing a query, without explicit human approval. The output should be routed to a Slack channel or incident ticket as a structured diagnostic aid, not an autonomous action.