/optimize-database-performance

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:**

   -- 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:**

   -- 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:**

   -- 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:**

   // 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:**

   -- 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:**

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