DB Performance Watchlist
Monitor database performance and prevent regressions.
Key Performance Metrics
// performance-metrics.ts
export interface DBMetrics {
// Query Performance
slowQueries: {
threshold: number; // ms
count: number;
queries: SlowQuery[];
};
// Connection Pool
connections: {
active: number;
idle: number;
total: number;
maxConnections: number;
utilizationPercent: number;
};
// Resource Usage
resources: {
cpuPercent: number;
memoryPercent: number;
diskUsagePercent: number;
iops: number;
};
// Query Statistics
queryStats: {
selectsPerSecond: number;
insertsPerSecond: number;
updatesPerSecond: number;
deletesPerSecond: number;
};
// Cache Performance
cache: {
hitRate: number; // %
size: number; // MB
evictions: number;
};
// Index Usage
indexes: {
unusedIndexes: string[];
missingIndexes: string[];
};
}
interface SlowQuery {
query: string;
duration: number;
calls: number;
avgDuration: number;
table: string;
}Slow Query Detection
// scripts/detect-slow-queries.ts
async function detectSlowQueries(thresholdMs: number = 100) {
// Enable slow query logging (PostgreSQL)
await prisma.$executeRaw`
ALTER DATABASE mydb
SET log_min_duration_statement = ${thresholdMs};
`;
// Query pg_stat_statements for slow queries
const slowQueries = await prisma.$queryRaw<SlowQuery[]>`
SELECT
query,
calls,
total_exec_time / 1000 as total_time_ms,
mean_exec_time / 1000 as avg_time_ms,
max_exec_time / 1000 as max_time_ms,
(total_exec_time / sum(total_exec_time) OVER()) * 100 as percent_of_total
FROM pg_stat_statements
WHERE mean_exec_time > ${thresholdMs}
ORDER BY mean_exec_time DESC
LIMIT 20
`;
console.log("🐌 Slow Queries Detected:\n");
slowQueries.forEach((q, i) => {
console.log(`${i + 1}. ${q.query.substring(0, 80)}...`);
console.log(` Calls: ${q.calls}`);
console.log(` Avg: ${q.avg_time_ms.toFixed(2)}ms`);
console.log(` Max: ${q.max_time_ms.toFixed(2)}ms`);
console.log(` % of total time: ${q.percent_of_total.toFixed(1)}%\n`);
});
return slowQueries;
}Connection Pool Monitoring
async function monitorConnectionPool() {
const stats = await prisma.$queryRaw<any[]>`
SELECT
sum(numbackends) as total_connections,
sum(CASE WHEN state = 'active' THEN 1 ELSE 0 END) as active,
sum(CASE WHEN state = 'idle' THEN 1 ELSE 0 END) as idle,
max_connections
FROM pg_stat_database
CROSS JOIN (SELECT setting::int as max_connections FROM pg_settings WHERE name = 'max_connections')
WHERE datname = current_database()
GROUP BY max_connections
`;
const { total_connections, active, idle, max_connections } = stats[0];
const utilization = (total_connections / max_connections) * 100;
console.log("🔌 Connection Pool Status:");
console.log(
` Total: ${total_connections}/${max_connections} (${utilization.toFixed(
1
)}%)`
);
console.log(` Active: ${active}`);
console.log(` Idle: ${idle}`);
// Alert if > 80% utilization
if (utilization > 80) {
console.warn("⚠️ Connection pool >80% utilized!");
await sendAlert({
title: "High connection pool usage",
message: `${utilization.toFixed(1)}% of connections in use`,
});
}
}Resource Monitoring
async function monitorResources() {
// CPU Usage
const cpuStats = await prisma.$queryRaw<any[]>`
SELECT
(sum(total_exec_time) / (extract(epoch from (now() - stats_reset)) * 1000 * 100)) as cpu_percent
FROM pg_stat_statements, pg_stat_database
WHERE datname = current_database()
`;
// Memory Usage
const memStats = await prisma.$queryRaw<any[]>`
SELECT
pg_size_pretty(pg_database_size(current_database())) as db_size,
pg_size_pretty(sum(pg_relation_size(schemaname||'.'||tablename))) as tables_size
FROM pg_tables
WHERE schemaname = 'public'
`;
// Cache Hit Rate
const cacheStats = await prisma.$queryRaw<any[]>`
SELECT
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) * 100 as cache_hit_rate
FROM pg_statio_user_tables
`;
console.log("📊 Resource Usage:");
console.log(` CPU: ${cpuStats[0].cpu_percent.toFixed(1)}%`);
console.log(` Database Size: ${memStats[0].db_size}`);
console.log(` Cache Hit Rate: ${cacheStats[0].cache_hit_rate.toFixed(1)}%`);
// Alert if cache hit rate < 90%
if (cacheStats[0].cache_hit_rate < 90) {
console.warn("⚠️ Cache hit rate below 90%!");
await sendAlert({
title: "Low cache hit rate",
message: `Cache hit rate: ${cacheStats[0].cache_hit_rate.toFixed(1)}%`,
});
}
}Index Usage Analysis
async function analyzeIndexUsage() {
// Find unused indexes
const unusedIndexes = await prisma.$queryRaw<any[]>`
SELECT
schemaname,
tablename,
indexname,
idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC
`;
console.log("🗂️ Unused Indexes:\n");
unusedIndexes.forEach((idx) => {
console.log(` ${idx.tablename}.${idx.indexname} (0 scans)`);
});
// Find missing indexes (sequential scans on large tables)
const missingIndexes = await prisma.$queryRaw<any[]>`
SELECT
schemaname,
tablename,
seq_scan,
seq_tup_read,
idx_scan,
n_live_tup
FROM pg_stat_user_tables
WHERE seq_scan > 1000
AND n_live_tup > 10000
ORDER BY seq_scan * n_live_tup DESC
LIMIT 10
`;
console.log("\n📉 Tables with High Sequential Scans:\n");
missingIndexes.forEach((table) => {
console.log(` ${table.tablename}:`);
console.log(` Sequential scans: ${table.seq_scan}`);
console.log(` Rows: ${table.n_live_tup}`);
console.log(` Index scans: ${table.idx_scan}`);
});
}Alert Thresholds
const ALERT_THRESHOLDS = {
slowQuery: {
avgDuration: 500, // ms
maxDuration: 2000, // ms
callsPerMinute: 100,
},
connections: {
utilizationWarning: 70, // %
utilizationCritical: 85, // %
},
resources: {
cpuWarning: 70, // %
cpuCritical: 85, // %
memoryWarning: 80, // %
memoryCritical: 90, // %
diskWarning: 75, // %
diskCritical: 85, // %
},
cache: {
hitRateWarning: 90, // %
hitRateCritical: 80, // %
},
queryRate: {
maxSelectsPerSecond: 10000,
maxWritesPerSecond: 1000,
},
};
async function checkThresholds() {
const metrics = await gatherMetrics();
// Check slow queries
if (metrics.slowQueries.count > 10) {
await sendAlert({
level: "warning",
title: "Slow queries detected",
message: `${metrics.slowQueries.count} queries exceeding ${ALERT_THRESHOLDS.slowQuery.avgDuration}ms`,
});
}
// Check connection pool
if (
metrics.connections.utilizationPercent >
ALERT_THRESHOLDS.connections.utilizationCritical
) {
await sendAlert({
level: "critical",
title: "Connection pool critical",
message: `${metrics.connections.utilizationPercent.toFixed(
1
)}% utilization`,
});
}
// Check cache hit rate
if (metrics.cache.hitRate < ALERT_THRESHOLDS.cache.hitRateCritical) {
await sendAlert({
level: "critical",
title: "Cache hit rate critical",
message: `${metrics.cache.hitRate.toFixed(1)}% hit rate`,
});
}
}Monitoring Dashboard
// Generate monitoring report
async function generatePerformanceReport() {
console.log("📊 Database Performance Report\n");
console.log("=".repeat(50) + "\n");
// Slow queries
const slowQueries = await detectSlowQueries(100);
console.log(`Slow Queries (>100ms): ${slowQueries.length}\n`);
// Connection pool
await monitorConnectionPool();
console.log();
// Resources
await monitorResources();
console.log();
// Index usage
await analyzeIndexUsage();
console.log();
// Query rates
const queryStats = await prisma.$queryRaw<any[]>`
SELECT
sum(xact_commit + xact_rollback) as transactions,
sum(tup_returned) as rows_read,
sum(tup_inserted) as rows_inserted,
sum(tup_updated) as rows_updated,
sum(tup_deleted) as rows_deleted
FROM pg_stat_database
WHERE datname = current_database()
`;
console.log("📈 Query Statistics:");
console.log(` Transactions: ${queryStats[0].transactions}`);
console.log(` Rows read: ${queryStats[0].rows_read}`);
console.log(` Rows inserted: ${queryStats[0].rows_inserted}`);
console.log(` Rows updated: ${queryStats[0].rows_updated}`);
console.log(` Rows deleted: ${queryStats[0].rows_deleted}`);
}Automated Monitoring Script
// scripts/monitor-db.ts
import cron from "node-cron";
// Run every 5 minutes
cron.schedule("*/5 * * * *", async () => {
await checkThresholds();
});
// Generate report every hour
cron.schedule("0 * * * *", async () => {
await generatePerformanceReport();
});
// Analyze indexes weekly
cron.schedule("0 0 * * 0", async () => {
await analyzeIndexUsage();
});Grafana Dashboard Queries
-- Query latency over time
SELECT
bucket,
AVG(mean_exec_time) as avg_latency,
MAX(max_exec_time) as max_latency,
SUM(calls) as total_calls
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
GROUP BY time_bucket('5 minutes', queryid)
ORDER BY bucket;
-- Connection count over time
SELECT
now() as time,
count(*) as total,
count(*) FILTER (WHERE state = 'active') as active,
count(*) FILTER (WHERE state = 'idle') as idle
FROM pg_stat_activity;
-- Cache hit rate
SELECT
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) * 100 as cache_hit_rate
FROM pg_statio_user_tables;Best Practices
- Monitor continuously: Don't wait for problems
- Set appropriate thresholds: Based on your SLAs
- Alert on trends: Not just absolute values
- Review regularly: Weekly performance reviews
- Automate everything: No manual checks
- Document baselines: Know what's normal
- Test alerts: Ensure they work
Output Checklist
- Slow query detection configured
- Connection pool monitoring
- Resource usage tracking
- Cache hit rate monitoring
- Index usage analysis
- Alert thresholds defined
- Monitoring dashboard setup
- Automated checks scheduled
- Grafana/alerting integration
- Performance baseline documented