Skip to main content
command CX Codex

optimize-database-performance — Claude Code command: what it does

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.

MIT ★ 3,785 updated 2026-10-25

Install into Claude Code · Plugin marketplace All methods and platforms ↓
/plugin marketplace add davepoon/buildwithclaude
/plugin install all-commands@buildwithclaude
Open on GitHub

What optimize-database-performance does

The steps run in order. Slow query logs and execution plans come first, then the schema, the indexing strategy and how hard the box is working. For PostgreSQL and MySQL you get SQL to run: switch on statement logging, capture a plan with EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON), build indexes with CREATE INDEX CONCURRENTLY.

Most of the work is indexes. It reads pg_stat_user_indexes and pg_stat_statements to find scans nobody uses and statements that drag, then proposes composite, partial or covering indexes. On the schema side come read-heavy tricks: a denormalised display table, materialized views refreshed concurrently, monthly range partitions for large tables. A Node.js helper built on the pg pool repeats the index checks from a script.

Only Read and Write are granted, so nothing runs against your database by itself. You paste the plans and logs in. The index queries expect pg_stat_statements to be enabled — on a server without it they come back empty.

What it helps with

  • read a slow query plan and name the bottleneck
  • list unused PostgreSQL indexes
  • add a composite index for a frequent filter
  • split a large orders table into monthly partitions
  • size a Node.js connection pool

Example prompts for the agent

  • /optimize-database-performance here is the EXPLAIN ANALYZE output for orders, point out where the time goes
  • Check my index usage from this pg_stat_user_indexes dump and tell me what I can drop
  • I need monthly partitions for orders, write the DDL and the function that creates them

How to install optimize-database-performance

1Plugin marketplace

/plugin marketplace add davepoon/buildwithclaude
/plugin install all-commands@buildwithclaude

2Manually: put the files in place

.claude/commands/optimize-database-performance.md

Call it with /optimize-database-performance. For all projects use ~/.claude/commands/

Questions about optimize-database-performance

How do I install optimize-database-performance in Claude Code?

Use the marketplace: /plugin marketplace add davepoon/buildwithclaude, then /plugin install all-commands@buildwithclaude. The command file ends up at .claude/commands/optimize-database-performance.md.

What does optimize-database-performance do?

It reviews slow queries and execution plans, index usage and schema design, then suggests partitioning and connection pool settings. Examples cover PostgreSQL and MySQL.

Does it connect to my database?

No. Only Read and Write are allowed, so it works on query text, plans, logs or a schema dump you hand it. The index checks rely on pg_stat_statements being present on PostgreSQL.

Source text

optimize-database-performance.md · MIT
---
description: Optimize database queries and performance
category: database-operations
allowed-tools: Read, Write
---

# Optimize Database Performance

Optimize database queries and performance

## Instructions

1. **Database Performance Analysis**
   - Analyze current database performance and identify bottlenecks
   - Review slow query logs and execution plans
   - Assess database schema design and normalization
   - Evaluate indexing strategy and query patterns
   - Monitor database resource utilization (CPU, memory, I/O)

2. **Query Optimization**
   - Optimize slow queries and improve execution plans:

   **PostgreSQL Query Optimization:**
   ```sql
   -- Enable query logging for analysis
   ALTER SYSTEM SET log_statement = 'all';
   ALTER SYSTEM SET log_min_duration_statement = 1000; -- Log queries > 1 second
   SELECT pg_reload_conf();

   -- Analyze query performance
   EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) 
   SELECT u.id, u.name, COUNT(o.id) as order_count
   FROM users u
   LEFT JOIN orders o ON u.id = o.user_id
   WHERE u.created_at > '2023-01-01'
   GROUP BY u.id, u.name
   ORDER BY order_count DESC;

   -- Optimize with proper indexing
   CREATE INDEX CONCURRENTLY idx_users_created_at ON users(created_at);
   CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
   CREATE INDEX CONCURRENTLY idx_orders_user_created ON orders(user_id, created_at);
   ```

   **MySQL Query Optimization:**
   ```sql
   -- Enable slow query log
   SET GLOBAL slow_query_log = 'ON';
   SET GLOBAL long_query_time = 1;
   SET GLOBAL log_queries_not_using_indexes = 'ON';

   -- Analyze query performance
   EXPLAIN FORMAT=JSON 
   SELECT p.*, c.name as category_name
   FROM products p
   JOIN categories c ON p.category_id = c.id
   WHERE p.price BETWEEN 100 AND 500
   AND p.created_at > DATE_SUB(NOW(), INTERVAL 30 DAY);

   -- Add composite indexes
   ALTER TABLE products 
   ADD INDEX idx_price_created (price, created_at),
   ADD INDEX idx_category_price (category_id, price);
   ```

3. **Index Strategy Optimization**
   - Design and implement optimal indexing strategy:

   **Index Analysis and Creation:**
   ```sql
   -- PostgreSQL index usage analysis
   SELECT 
     schemaname,
     tablename,
     indexname,
     idx_scan as index_scans,
     seq_scan as table_scans,
     idx_scan::float / (idx_scan + seq_scan + 1) as index_usage_ratio
   FROM pg_stat_user_indexes 
   ORDER BY index_usage_ratio ASC;

   -- Find missing indexes
   SELECT 
     query,
     calls,
     total_time,
     mean_time,
     rows
   FROM pg_stat_statements 
   WHERE mean_time > 1000 -- queries taking > 1 second
   ORDER BY mean_time DESC;

   -- Create covering indexes for common query patterns
   CREATE INDEX CONCURRENTLY idx_orders_covering 
   ON orders(user_id, status, created_at) 
   INCLUDE (total_amount, discount);

   -- Partial indexes for selective conditions
   CREATE INDEX CONCURRENTLY idx_active_users 
   ON users(last_login) 
   WHERE status = 'active';
   ```

   **Index Maintenance Scripts:**
   ```javascript
   // Node.js index analysis tool
   const { Pool } = require('pg');
   const pool = new Pool();

   class IndexAnalyzer {
     static async analyzeUnusedIndexes() {
       const query = `
         SELECT 
           schemaname,
           tablename,
           indexname,
           idx_scan,
           pg_size_pretty(pg_relation_size(indexrelid)) as size
         FROM pg_stat_user_indexes 
         WHERE idx_scan = 0
         AND schemaname = 'public'
         ORDER BY pg_relation_size(indexrelid) DESC;
       `;
       
       const result = await pool.query(query);
       console.log('Unused indexes:', result.rows);
       return result.rows;
     }

     static async suggestIndexes() {
       const query = `
         SELECT 
           query,
           calls,
           total_time,
           mean_time
         FROM pg_stat_statements 
         WHERE mean_time > 100
         AND query NOT LIKE '%pg_%'
         ORDER BY total_time DESC
         LIMIT 20;
       `;
       
       const result = await pool.query(query);
       console.log('Slow queries needing indexes:', result.rows);
       return result.rows;
     }
   }
   ```

4. **Schema Design Optimization**
   - Optimize database schema for performance:

   **Normalization and Denormalization:**
   ```sql
   -- Denormalization example for read-heavy workloads
   -- Instead of joining multiple tables for product display
   CREATE TABLE product_display_cache AS
   SELECT 
     p.id,
     p.name,
     p.price,
     p.description,
     c.name as category_name,
     b.name as brand_name,
     AVG(r.rating) as avg_rating,
     COUNT(r.id) as review_count
   FROM products p
   JOIN categories c ON p.category_id = c.id
   JOIN brands b ON p.brand_id = b.id
   LEFT JOIN reviews r ON p.id = r.product_id
   GROUP BY p.id, c.name, b.name;

   -- Create materialized view for complex aggregations
   CREATE MATERIALIZED VIEW monthly_sales_summary AS
   SELECT 
     DATE_TRUNC('month', created_at) as month,
     category_id,
     COUNT(*) as order_count,
     SUM(total_amount) as total_revenue,
     AVG(total_amount) as avg_order_value
   FROM orders 
   WHERE created_at >= DATE_TRUNC('year', CURRENT_DATE)
   GROUP BY DATE_TRUNC('month', created_at), category_id;

   -- Refresh materialized view periodically
   REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_summary;
   ```

   **Partitioning for Large Tables:**
   ```sql
   -- PostgreSQL table partitioning
   CREATE TABLE orders_partitioned (
     id SERIAL,
     user_id INTEGER,
     total_amount DECIMAL(10,2),
     created_at TIMESTAMP NOT NULL,
     status VARCHAR(50)
   ) PARTITION BY RANGE (created_at);

   -- Create monthly partitions
   CREATE TABLE orders_2024_01 PARTITION OF orders_partitioned
   FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

   CREATE TABLE orders_2024_02 PARTITION OF orders_partitioned
   FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

   -- Automatic partition creation
   CREATE OR REPLACE FUNCTION create_monthly_partition(table_name text, start_date date)
   RETURNS void AS $$
   DECLARE
     partition_name text;
     end_date date;
   BEGIN
     partition_name := table_name || '_' || to_char(start_date, 'YYYY_MM');
     end_date := start_date + interval '1 month';
     
     EXECUTE format('CREATE TABLE %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)',
       partition_name, table_name, start_date, end_date);
   END;
   $$ LANGUAGE plpgsql;
   ```

5. **Connection Pool Optimization**
   - Configure optimal database connection pooling:

   **Node.js Connection Pool Configuration:**
   ```javascript
   const { Pool } = require('pg');

   // Optimized connection pool configuration
   const pool = new Pool({
     user: process.env.DB_USER,
     host: process.env.DB_HOST,
     database: process.env.DB_NAME,
     password: process.env.DB_PASSWORD,
     port: process.env.DB_PORT,
     
     // Connection pool settings
     max: 20, // Maximum connections
     idleTimeoutMillis: 30000, // 30 seconds
     connectionTimeoutMillis: 2000, // 2 seconds
     maxUses: 7500, // Max uses before connection refresh
     
     // Performance settings
     statement_timeout: 30000, // 30 seconds
     query_timeout: 30000,
     
     // SSL configuration
     ssl: process.env.NODE_ENV === 'production' ? { rejectUnauthorized: false } : false,
   });

   // Connection pool monitoring
   pool.on('connect', (client) => {
     console.log('Connected to database');
   });

   pool.on('error', (err, client) => {
     console.error('Database connection error:', err);
   });

   // Pool stats monitoring
   setInterval(() => {
     console.log('Pool stats:', {
       totalCount: pool.totalCount,
       idleCount: pool.idleCount,
       waitingCount: pool.waitingCount,
     });
   }, 60000); // Every minute
   ```

   **Database Connection Middleware:**
   ```javascript
   class DatabaseManager {
     static async executeQuery(query, params = []) {
       const client = await pool.connect();
       try {
         const start = Date.now();
         const result = await client.query(query, params);
         const duration = Date.now() - start;
         
         // Log slow queries
         if (duration > 1000) {
           console.warn(`Slow query (${duration}ms):`, query);
         }
         
         return result;
       } finally {
         client.release();
       }
     }

     static async transaction(callback) {
       const client = await pool.connect();
       try {
         await client.query('BEGIN');
         const result = await callback(client);
         await client.query('COMMIT');
         return result;
       } catch (error) {
         await client.query('ROLLBACK');
         throw error;
       } finally {
         client.release();
       }
     }
   }
   ```

6. **Query Result Caching**
   - Implement intelligent database result caching:

   ```javascript
   const Redis = require('redis');
   const redis = Redis.createClient();

   class QueryCache {
     static generateKey(query, params) {
       return `query:${Buffer.from(query + JSON.stringify(params)).toString('base64')}`;
     }

     static async get(query, params) {
       const key = this.generateKey(query, params);
       const cached = await redis.get(key);
       return cached ? JSON.parse(cached) : null;
     }

     static async set(query, params, result, ttl = 300) {
       const key = this.generateKey(query, params);
       await redis.setex(key, ttl, JSON.stringify(result));
     }

     static async cachedQuery(query, params = [], ttl = 300) {
       // Try cache first
       let result = await this.get(query, params);
       if (result) {
         return result;
       }

       // Execute query and cache result
       result = await DatabaseManager.executeQuery(query, params);
       await this.set(query, params, result.rows, ttl);
       
       return result;
     }

     // Cache invalidation by table patterns
     static async invalidateTable(tableName) {
       const pattern = `query:*${tableName}*`;
       const keys = await redis.keys(pattern);
       if (keys.length > 0) {
         await redis.del(keys);
       }
     }
   }
   ```

7. **Database Monitoring and Profiling**
   - Set up comprehensive database monitoring:

   **Performance Monitoring Script:**
   ```javascript
   class DatabaseMonitor {
     static async getPerformanceStats() {
       const queries = [
         {
           name: 'active_connections',
           query: 'SELECT count(*) FROM pg_stat_activity WHERE state = \'active\';'
         },
         {
           name: 'long_running_queries',
           query: `SELECT pid, now() - pg_stat_activity.query_start AS duration, query 
                   FROM pg_stat_activity 
                   WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';`
         },
         {
           name: 'table_sizes',
           query: `SELECT relname AS table_name, 
                          pg_size_pretty(pg_total_relation_size(relid)) AS size
                   FROM pg_catalog.pg_statio_user_tables 
                   ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;`
         },
         {
           name: 'index_usage',
           query: `SELECT relname AS table_name, 
                          indexrelname AS index_name,
                          idx_scan AS index_scans,
                          seq_scan AS sequential_scans
                   FROM pg_stat_user_indexes 
                   WHERE seq_scan > idx_scan;`
         }
       ];

       const stats = {};
       for (const { name, query } of queries) {
         try {
           const result = await pool.query(query);
           stats[name] = result.rows;
         } catch (error) {
           stats[name] = { error: error.message };
         }
       }

       return stats;
     }

     static async alertOnSlowQueries() {
       const slowQueries = await pool.query(`
         SELECT query, calls, total_time, mean_time, stddev_time
         FROM pg_stat_statements 
         WHERE mean_time > 1000 
         ORDER BY mean_time DESC 
         LIMIT 10;
       `);

       if (slowQueries.rows.length > 0) {
         console.warn('Slow queries detected:', slowQueries.rows);
         // Send alert to monitoring system
       }
     }
   }

   // Schedule monitoring
   setInterval(async () => {
     await DatabaseMonitor.alertOnSlowQueries();
   }, 300000); // Every 5 minutes
   ```

8. **Read Replica and Load Balancing**
   - Configure read replicas for query distribution:

   ```javascript
   const { Pool } = require('pg');

   class DatabaseCluster {
     constructor() {
       this.writePool = new Pool({
         host: process.env.DB_WRITE_HOST,
         // ... write database config
       });

       this.readPools = [
         new Pool({
           host: process.env.DB_READ1_HOST,
           // ... read replica 1 config
         }),
         new Pool({
           host: process.env.DB_READ2_HOST,
           // ... read replica 2 config
         }),
       ];

       this.currentReadIndex = 0;
     }

     getReadPool() {
       // Round-robin read replica selection
       const pool = this.readPools[this.currentReadIndex];
       this.currentReadIndex = (this.currentReadIndex + 1) % this.readPools.length;
       return pool;
     }

     async executeWrite(query, params) {
       return await this.writePool.query(query, params);
     }

     async executeRead(query, params) {
       const readPool = this.getReadPool();
       return await readPool.query(query, params);
     }

     async executeQuery(query, params, forceWrite = false) {
       const isWriteQuery = /^\s*(INSERT|UPDATE|DELETE|CREATE|ALTER|DROP)/i.test(query);
       
       if (isWriteQuery || forceWrite) {
         return await this.executeWrite(query, params);
       } else {
         return await this.executeRead(query, params);
       }
     }
   }

   const dbCluster = new DatabaseCluster();
   ```

9. **Database Vacuum and Maintenance**
   - Implement automated database maintenance:

   **PostgreSQL Maintenance Scripts:**
   ```sql
   -- Automated vacuum and analyze
   CREATE OR REPLACE FUNCTION auto_vacuum_analyze()
   RETURNS void AS $$
   DECLARE
     rec RECORD;
   BEGIN
     FOR rec IN 
       SELECT schemaname, tablename 
       FROM pg_tables 
       WHERE schemaname = 'public'
     LOOP
       EXECUTE 'VACUUM ANALYZE ' || quote_ident(rec.schemaname) || '.' || quote_ident(rec.tablename);
       RAISE NOTICE 'Vacuumed table %.%', rec.schemaname, rec.tablename;
     END LOOP;
   END;
   $$ LANGUAGE plpgsql;

   -- Schedule maintenance (using pg_cron extension)
   SELECT cron.schedule('nightly-maintenance', '0 2 * * *', 'SELECT auto_vacuum_analyze();');
   ```

   **Maintenance Monitoring:**
   ```javascript
   class MaintenanceMonitor {
     static async checkTableBloat() {
       const query = `
         SELECT 
           tablename,
           pg_size_pretty(pg_total_relation_size(tablename::regclass)) as size,
           n_dead_tup,
           n_live_tup,
           CASE 
             WHEN n_live_tup > 0 
             THEN round(n_dead_tup::numeric / n_live_tup::numeric, 2) 
             ELSE 0 
           END as dead_ratio
         FROM pg_stat_user_tables 
         WHERE n_dead_tup > 1000
         ORDER BY dead_ratio DESC;
       `;

       const result = await pool.query(query);
       
       // Alert if dead tuple ratio is high
       result.rows.forEach(row => {
         if (row.dead_ratio > 0.2) {
           console.warn(`Table ${row.tablename} has high bloat: ${row.dead_ratio}`);
         }
       });

       return result.rows;
     }

     static async reindexIfNeeded() {
       const bloatedIndexes = await pool.query(`
         SELECT indexname, tablename 
         FROM pg_stat_user_indexes 
         WHERE idx_scan = 0 AND pg_relation_size(indexrelid) > 10485760; -- > 10MB
       `);

       // Suggest reindexing unused large indexes
       bloatedIndexes.rows.forEach(row => {
         console.log(`Consider dropping unused index: ${row.indexname} on ${row.tablename}`);
       });
     }
   }
   ```

10. **Performance Testing and Benchmarking**
    - Set up database performance testing:

    **Load Testing Script:**
    ```javascript
    const { Pool } = require('pg');
    const pool = new Pool();

    class DatabaseLoadTester {
      static async benchmarkQuery(query, params, iterations = 100) {
        const times = [];
        
        for (let i = 0; i < iterations; i++) {
          const start = process.hrtime.bigint();
          await pool.query(query, params);
          const end = process.hrtime.bigint();
          
          times.push(Number(end - start) / 1000000); // Convert to milliseconds
        }

        const avg = times.reduce((a, b) => a + b, 0) / times.length;
        const min = Math.min(...times);
        const max = Math.max(...times);
        const median = times.sort()[Math.floor(times.length / 2)];

        return { avg, min, max, median, iterations };
      }

      static async stressTest(concurrency = 10, duration = 60000) {
        const startTime = Date.now();
        const results = { success: 0, errors: 0, totalTime: 0 };
        
        const workers = Array(concurrency).fill().map(async () => {
          while (Date.now() - startTime < duration) {
            try {
              const start = Date.now();
              await pool.query('SELECT COUNT(*) FROM products');
              results.totalTime += Date.now() - start;
              results.success++;
            } catch (error) {
              results.errors++;
            }
          }
        });

        await Promise.all(workers);
        
        results.qps = results.success / (duration / 1000);
        results.avgResponseTime = results.totalTime / results.success;
        
        return results;
      }
    }

    // Run benchmarks
    async function runBenchmarks() {
      console.log('Running database benchmarks...');
      
      const simpleQuery = await DatabaseLoadTester.benchmarkQuery(
        'SELECT * FROM products LIMIT 10'
      );
      console.log('Simple query benchmark:', simpleQuery);
      
      const complexQuery = await DatabaseLoadTester.benchmarkQuery(
        `SELECT p.*, c.name as category 
         FROM products p 
         JOIN categories c ON p.category_id = c.id 
         ORDER BY p.created_at DESC LIMIT 50`
      );
      console.log('Complex query benchmark:', complexQuery);
      
      const stressTest = await DatabaseLoadTester.stressTest(5, 30000);
      console.log('Stress test results:', stressTest);
    }
    ```

© BuildWithClaude Community · MIT · davepoon/buildwithclaude

Similar slash commands

All slash commands for Data and databases →

From the same set235

The whole set →
immich-photo-manager The immich-photo-manager Claude Code skill manages a self-hosted Immich photo library through natural-language requests. It searches images and text in photos, and helps organize albums, duplicates, and metadata. CX Codex +1 105installs tidy This Claude Code skill groups uncategorized transactions and suggests categories. It starts when asked to tidy transactions or sort uncategorized activity. CX Codex +1 104installs google-drive-upload google-drive-upload is a Claude Code skill that sends files to Google Drive through a deployed Google Apps Script web app. It reacts to requests to store or upload a file. CX Codex +1 101installs szamlazz-invoicing The szamlazz-invoicing Claude Code skill handles Hungarian invoices through the szamlazz.hu Agent API. It activates for requests to issue, cancel, or download an invoice, or create a proforma. CX Codex +1 100installs optimize-bundle-size optimize-bundle-size reads your bundle, finds the dependencies that bloat it and rewrites the build config: chunk splitting, lazy routes, tree shaking. Useful when the JS payload slows the page down. CX Codex +1 ★ 3,785repo stars implement-caching-strategy implement-caching-strategy is a Claude Code command that plans and adds caching across browser, application, and database layers. Run it to define TTL and invalidation rules for a project. CX Codex +1 ★ 3,785repo stars add-performance-monitoring add-performance-monitoring generates performance monitoring configs for Node.js backends and browser apps using New Relic, Datadog or Web Vitals. CX Codex +1 ★ 3,785repo stars rollback-deploy rollback-deploy turns a release rollback into a numbered runbook: decide whether to revert, check the live version, undo the deploy, restore traffic, tell users. CX Codex +1 ★ 3,785repo stars

More in the catalog

Project sponsors

Companies that keep this analytics open