/**
* Database Health Check Prompt
*
* Comprehensive health assessment workflow for PostgreSQL databases.
*/
import type { PromptDefinition, RequestContext } from "../../../types/index.js";
export function createDatabaseHealthCheckPrompt(): PromptDefinition {
return {
name: "pg_database_health_check",
description:
"Comprehensive database health assessment covering indexes, connections, vacuum, replication, and buffer cache.",
arguments: [
{
name: "focus",
description:
"Health area to focus on: all, indexes, connections, vacuum, replication, buffer",
required: false,
},
],
// eslint-disable-next-line @typescript-eslint/require-await
handler: async (
args: Record<string, string>,
_context: RequestContext,
): Promise<string> => {
const focus = args["focus"] ?? "all";
let content = `# Database Health Check - Focus: ${focus.charAt(0).toUpperCase() + focus.slice(1)}
## Health Check Categories
### 1. Extension Availability
First, verify critical extensions are installed:
\`\`\`sql
SELECT extname, extversion FROM pg_extension WHERE extname IN ('pg_stat_statements', 'hypopg', 'vector', 'postgis');
\`\`\`
Use \`pg_list_extensions\` to see all installed extensions.
### 2. Database Health Analysis
Use \`pg_analyze_db_health\` with health_type: "${focus}"
This runs comprehensive checks on:
`;
if (focus === "indexes" || focus === "all") {
content += `
**Index Health:**
- Invalid indexes (need rebuilding)
- Duplicate indexes (waste space)
- Bloated indexes (need REINDEX)
- Unused indexes (candidates for removal)
`;
}
if (focus === "connections" || focus === "all") {
content += `
**Connection Health:**
- Current connection count vs. max_connections
- Connection pool utilization
- Idle connections consuming resources
- Long-running transactions blocking others
`;
}
if (focus === "vacuum" || focus === "all") {
content += `
**Vacuum Health:**
- Transaction ID wraparound risk (CRITICAL)
- Autovacuum effectiveness
- Table bloat estimates
- Dead tuple accumulation
`;
}
if (focus === "replication" || focus === "all") {
content += `
**Replication Health:**
- Replication lag in milliseconds
- Replication slot status
- WAL sender/receiver status
`;
}
if (focus === "buffer" || focus === "all") {
content += `
**Buffer Cache Health:**
- Cache hit ratio for tables (should be > 99%)
- Cache hit ratio for indexes (should be > 99%)
- Shared buffers effectiveness
`;
}
content += `
### 3. Performance Metrics
Check query performance using \`pg_stat_statements\`:
- Queries with mean_exec_time > 1000ms
- High variation in execution times
- Queries dominating total database time
### 4. Capacity Planning
Use \`pg_capacity_planning\` to analyze growth:
- Database size growth rate
- Estimated time to disk full
- Table growth patterns
### 5. Alert Thresholds
Use \`pg_alert_threshold_set\` for:
- Connection limits (warning: 80%, critical: 95%)
- Cache hit ratio (warning: 95%, critical: 90%)
- Replication lag (warning: 5s, critical: 30s)
## Health Report
After running checks, I'll provide:
- **Health Score:** Overall status (Good/Warning/Critical)
- **Critical Issues:** Fix immediately
- **Warnings:** Plan to fix
- **Recommendations:** Maintenance schedule
**Pro Tip:** Run health checks during low-traffic periods for accurate baselines!`;
return content;
},
};
}
