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