Database Performance Monitoring

You are a database monitoring expert. Help me set up performance monitoring.

## Monitoring Needs
Database: ${{DATABASE}}
Metrics: ${{METRICS}}
Alerting: ${{ALERTING}}
Visualization: ${{VISUALIZATION}}

## Please Configure:

1. **Key Metrics**

// Essential metrics:
// - Connections (active, idle, waiting)
// - Query performance (duration, frequency)
// - Cache hit ratio
// - Disk I/O
// - Lock waits
// - Replication lag
// - Table/index sizes


2. **PostgreSQL Monitoring**

-- Connection stats:
SELECT * FROM pg_stat_activity;

-- Query stats (requires pg_stat_statements):
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;

-- Cache hit ratio:
SELECT
  sum(heap_blks_hit) / sum(heap_blks_hit + heap_blks_read) as cache_hit_ratio
FROM pg_statio_user_tables;

-- Table sizes:
SELECT
  relname,
  pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;


3. **Slow Query Detection**

-- Enable slow query log:
-- postgresql.conf
log_min_duration_statement = 1000  -- ms

-- Query for slow queries:
SELECT query, calls, mean_time, total_time
FROM pg_stat_statements
WHERE mean_time > 1000
ORDER BY mean_time DESC;


4. **Prometheus Setup**

# postgres_exporter config:
scrape_configs:
  - job_name: 'postgres'
    static_configs:
      - targets: ['localhost:9187']


5. **Grafana Dashboard**

// Dashboard panels:
{
  "panels": [
    { "title": "Connections", "query": "pg_stat_activity_count" },
    { "title": "Cache Hit Ratio", "query": "pg_stat_database_blks_hit_rate" },
    { "title": "Transactions/sec", "query": "rate(pg_stat_database_xact_commit[5m])" },
    { "title": "Replication Lag", "query": "pg_replication_lag" }
  ]
}


6. **Alerting Rules**

# Prometheus alerts:
groups:
  - name: postgres
    rules:
      - alert: HighConnectionCount
        expr: pg_stat_activity_count > 100
        for: 5m

      - alert: LowCacheHitRatio
        expr: pg_stat_database_blks_hit_rate < 0.95
        for: 10m

      - alert: ReplicationLag
        expr: pg_replication_lag > 60
        for: 5m


7. **Custom Metrics**

// Application-level metrics:
const queryDuration = new Histogram({
  name: 'db_query_duration_seconds',
  help: 'Database query duration',
  labelNames: ['query_type', 'table']
});

// Track in middleware:
const end = queryDuration.startTimer({ query_type, table });
await query();
end();


8. **Health Checks**

// Database health endpoint:
app.get('/health/db', async (req, res) => {
  try {
    await db.$queryRaw`SELECT 1`;
    const stats = await getDbStats();
    res.json({ status: 'healthy', ...stats });
  } catch (error) {
    res.status(503).json({ status: 'unhealthy', error });
  }
});