Skip to main content
skill CX Codex

db-performance-watchlist — Claude Code skill: setup and features

db-performance-watchlist is a Claude Code skill that tracks PostgreSQL metrics, including slow queries, connection pools, and unused indexes through Prisma.

MIT 260 installs ★ 64 updated 2026-01-12

Install into Claude Code · Skills CLI (Vercel) All methods and platforms ↓
npx skills add patricio0312rev/skills --skill db-performance-watchlist -a claude-code
Open on GitHub

What db-performance-watchlist does

The skill provides TypeScript interfaces and query functions for monitoring database health. It collects metrics across connection pool capacity, cache hit ratios, query execution rates, and disk usage.

Slow query detection relies on Prisma raw queries against PostgreSQL pg_stat_statements, sorting commands by execution time with an adjustable threshold. To catch query regressions, the skill also queries pg_stat_user_indexes for unused indexes and flags large tables that suffer from frequent sequential scans.

The implementation relies directly on PostgreSQL system tables and the pg_stat_statements view, so it will fail on databases lacking those statistics. It defines alert limits, such as a warning when connection pool utilization crosses 70% or cache hit rate drops below 90%.

What it helps with

  • detect slow queries using pg_stat_statements and Prisma
  • audit connection pool utilization against max_connections
  • identify unused indexes and tables with high sequential scan counts
  • monitor PostgreSQL cache hit ratios and storage footprints

Example prompts for the agent

  • Check our PostgreSQL database for queries taking longer than 100ms
  • Inspect connection pool usage and warn me if utilization exceeds 70%
  • Find unused database indexes and tables with excessive sequential scans

How to install db-performance-watchlist

1Skills CLI (Vercel)

npx skills add patricio0312rev/skills --skill db-performance-watchlist -a claude-code

2Manually: put the files in place

~/.claude/skills/db-performance-watchlist/SKILL.md

Copy the whole skill folder; for one project use .claude/skills/db-performance-watchlist/

Step by step, with commands for each platform: How to install skills in Claude Code: five ways

Questions about db-performance-watchlist

How do I install db-performance-watchlist in Claude Code?

Run npx skills add patricio0312rev/skills --skill db-performance-watchlist -a claude-code. The skill will be placed in ~/.claude/skills/db-performance-watchlist/SKILL.md.

What does db-performance-watchlist do?

It equips your agent with a monitoring strategy and TypeScript scripts for database metrics. It covers slow query logging, connection limits, cache performance, and missing index detection.

Which database systems does this skill support?

The provided queries are built specifically for PostgreSQL and use Prisma raw SQL commands alongside system views like pg_stat_statements and pg_stat_user_tables.

Source text

SKILL.md · MIT
---
name: db-performance-watchlist
description: Defines database performance monitoring strategy with slow query detection, resource usage alerts, query execution thresholds, and automated alerting. Use for "database monitoring", "performance alerts", "slow queries", or "DB metrics".
---

# DB Performance Watchlist

Monitor database performance and prevent regressions.

## Key Performance Metrics

```typescript
// 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

```typescript
// 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

```typescript
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

```typescript
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

```typescript
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

```typescript
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

```typescript
// 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

```typescript
// 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

```sql
-- 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

1. **Monitor continuously**: Don't wait for problems
2. **Set appropriate thresholds**: Based on your SLAs
3. **Alert on trends**: Not just absolute values
4. **Review regularly**: Weekly performance reviews
5. **Automate everything**: No manual checks
6. **Document baselines**: Know what's normal
7. **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

© patricio0312rev · MIT · patricio0312rev/skills

Similar skills

All skills for Data and databases →
database-performance-debugging aj-geddes database-performance-debugging — a skill that walks your agent through finding slow SQL queries and reading their execution plans. Useful when the app drags or the database burns CPU. CX Codex +1 500installs Neon Performance Analyzer official github Neon Performance Analyzer identifies slow Postgres queries on Neon, tests schema fixes inside isolated database branches, and prepares PRs with execution metrics. CX Codex +1 ★ 41,430repo stars optimize-database-performance BuildWithClaude Community optimize-database-performance — a slash command that works through slow queries, index strategy and schema layout for PostgreSQL and MySQL, then hands back SQL you can apply. Useful once page loads start dragging. CX Codex +1 ★ 3,785repo stars PostgreSQL performance Tuner MCP Server isdaniel PostgreSQL performance Tuner MCP Server — an MCP server that diagnoses PostgreSQL slow queries and tests hypothetical indexes using HypoPG without writing to disk. CX Codex +1 ★ 31repo stars diagnosing-endpoint-performance PostHog diagnosing-endpoint-performance — a skill that identifies why a PostHog endpoint is slow or expensive, then recommends a concrete fix: increase cache TTL, enable materialisation, restructure variables, or rewrite the query. CX Codex +1 316installs database-design spencerpauly database-design designs relational database schemas, foreign keys, indexes, and constraints from raw requirements, with output tailored for SQL, Prisma, or Drizzle. CX Codex +1 505installs

From the same set148

The whole set →
auth-module-builder auth-module-builder lays out a full authentication module: login and registration routes, JWT signing, bcrypt hashing, Redis sessions and a threat model. Useful when the login flow starts from an empty file. CX Codex +1 263installs kubernetes-manifest-generator kubernetes-manifest-generator produces production-grade Kubernetes YAML manifests with hardening, autoscaling, and networking defaults. Useful when preparing workloads for cluster deployment. CX Codex +1 262installs caching-cdn-strategy-planner caching-cdn-strategy-planner is a Claude Code skill for planning CDN and server-side caching, response headers, and invalidation. Use it in API design. CX Codex +1 262installs dependency-doctor dependency-doctor scans your manifests, flags outdated and duplicated packages, known CVEs and risky version pairs, then hands back a report with a step-by-step upgrade plan. CX Codex +1 261installs api-contract-normalizer The api-contract-normalizer skill aligns API responses across endpoints, covering envelopes, errors, pagination, status codes, and versioning. Use it when API conventions have drifted. CX Codex +1 260installs vscode-rest-client-generator vscode-rest-client-generator generates .http files from Express, Next.js, and Fastify routes for the VS Code REST Client extension. Ideal for testing APIs in the editor without Postman. CX Codex +1 259installs embedding-pipeline-builder embedding-pipeline-builder builds document embedding workflows for loading, cleaning, chunking, indexing, and retrieval. Useful when setting up RAG preprocessing or semantic search. CX Codex +1 259installs llm-debugger llm-debugger diagnoses LLM output failures, patches faulty prompts, refines tool schemas, and generates regression test cases. CX Codex +1 259installs

Project sponsors

Companies that keep this analytics open