Use Cases
Common Use Cases
- Slow query alerts
- Connection pool monitoring
- Storage capacity warnings
- Replication lag detection
Before You Begin
Prerequisites
- PostgreSQL 12+ with pg_stat_statements enabled
- Slack workspace
- Node.js 18+
Walkthrough
Step-by-Step Guide
1
Configure PostgreSQL MCP Server
Set up the PostgreSQL MCP Server with read-only credentials for monitoring queries.
2
Enable Monitoring Extensions
Ensure pg_stat_statements and pg_stat_activity are available for performance metrics.
-- Enable pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;3
Create Monitoring Queries
Write queries that check for slow queries, high connection counts, and disk usage.
4
Set Up Alert Thresholds
Define when to alert: queries > 5s, connections > 80%, disk > 85%.
async function checkDatabaseHealth() {
const slowQueries = await postgres.query("SELECT query, mean_exec_time FROM pg_stat_statements WHERE mean_exec_time > 5000 ORDER BY mean_exec_time DESC LIMIT 5");
const connections = await postgres.query("SELECT count(*) as active, (SELECT setting FROM pg_settings WHERE name='max_connections') as max FROM pg_stat_activity");
if (slowQueries.rows.length > 0) {
await slack.sendMessage({ channel: "#db-alerts", text: `⚠️ ${slowQueries.rows.length} slow queries detected (>5s avg)` });
}
}5
Schedule Regular Checks
Run health checks every 5 minutes using a cron schedule.
Examples
Code Examples
sql
Health Check Query
SELECT
count(*) FILTER (WHERE state = 'active') as active_queries,
count(*) FILTER (WHERE state = 'idle') as idle_connections,
count(*) FILTER (WHERE wait_event IS NOT NULL) as waiting,
pg_database_size(current_database()) / 1024 / 1024 as db_size_mb
FROM pg_stat_activity;typescript
Alert Formatter
function formatDBAlert(metrics) {
const alerts = [];
if (metrics.active_queries > 50) alerts.push(`🔴 High query load: ${metrics.active_queries} active`);
if (metrics.db_size_mb > 50000) alerts.push(`🟡 Database size: ${(metrics.db_size_mb/1024).toFixed(1)}GB`);
return alerts.join("\n");
}Help
Troubleshooting
Can I monitor multiple databases?+
How do I avoid alert fatigue?+
Quick Info
DifficultyIntermediate
Time Estimate45 minutes
Tools
PostgreSQL MCP ServerSlack MCP Server