Database Optimization: From Slow Queries to Lightning Fast

Database performance can make or break your application. Let’s dive into proven techniques to optimize database queries, design efficient schemas, and monitor performance at scale.

Query Optimization Fundamentals

Understanding Query Execution Plans

-- PostgreSQL: Analyze query performance
EXPLAIN ANALYZE SELECT u.name, p.title, p.created_at
FROM users u
JOIN posts p ON u.id = p.user_id
WHERE u.active = true
AND p.published_at > '2024-01-01'
ORDER BY p.created_at DESC
LIMIT 20;

-- Look for:
-- 1. Sequential Scans (bad for large tables)
-- 2. Nested Loop joins (can be expensive)
-- 3. Sort operations (consider indexes)
-- 4. High cost values

Index Strategy Patterns

-- Single column indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_published_at ON posts(published_at);

-- Composite indexes (order matters!)
CREATE INDEX idx_posts_user_published ON posts(user_id, published_at DESC);
CREATE INDEX idx_users_active_created ON users(active, created_at) WHERE active = true;

-- Partial indexes for better performance
CREATE INDEX idx_active_users ON users(email) WHERE active = true;
CREATE INDEX idx_published_posts ON posts(title, created_at) WHERE published = true;

-- Functional indexes
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
CREATE INDEX idx_posts_year ON posts(EXTRACT(YEAR FROM created_at));

Query Rewriting Techniques

-- Bad: Using OR conditions
SELECT * FROM users 
WHERE status = 'active' OR status = 'pending';

-- Good: Using IN clause
SELECT * FROM users 
WHERE status IN ('active', 'pending');

-- Bad: Function in WHERE clause
SELECT * FROM posts 
WHERE YEAR(created_at) = 2024;

-- Good: Range query
SELECT * FROM posts 
WHERE created_at >= '2024-01-01' 
AND created_at < '2025-01-01';

-- Bad: Subquery in SELECT
SELECT u.name,
       (SELECT COUNT(*) FROM posts WHERE user_id = u.id) as post_count
FROM users u;

-- Good: JOIN with aggregation
SELECT u.name, COALESCE(p.post_count, 0) as post_count
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(*) as post_count
    FROM posts
    GROUP BY user_id
) p ON u.id = p.user_id;

Advanced Indexing Strategies

MongoDB Indexing Patterns

// Compound indexes for MongoDB
db.users.createIndex({ 
  "status": 1, 
  "created_at": -1 
});

// Text search indexes
db.posts.createIndex({ 
  "title": "text", 
  "content": "text" 
});

// Geospatial indexes
db.locations.createIndex({ 
  "coordinates": "2dsphere" 
});

// Sparse indexes (only index documents with the field)
db.users.createIndex({ 
  "phone": 1 
}, { 
  sparse: true 
});

// TTL indexes for automatic document expiration
db.sessions.createIndex({ 
  "createdAt": 1 
}, { 
  expireAfterSeconds: 3600 
});

// Query optimization with explain
db.users.find({ 
  status: "active", 
  created_at: { $gte: ISODate("2024-01-01") } 
}).explain("executionStats");

Index Monitoring and Maintenance

-- PostgreSQL: Monitor index usage
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_tup_read,
    idx_tup_fetch,
    idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Find unused indexes
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexname NOT LIKE '%_pkey';

-- Index size analysis
SELECT 
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

Connection Pool Optimization

Node.js with PostgreSQL

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

// Optimized connection pool configuration
const pool = new Pool({
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  
  // Connection pool settings
  min: 2,                    // Minimum connections
  max: 20,                   // Maximum connections
  idleTimeoutMillis: 30000,  // Close idle connections after 30s
  connectionTimeoutMillis: 2000, // Timeout for new connections
  
  // Query timeout
  query_timeout: 10000,
  
  // SSL configuration for production
  ssl: process.env.NODE_ENV === 'production' ? {
    rejectUnauthorized: false
  } : false
});

// Connection monitoring
pool.on('connect', (client) => {
  console.log('New client connected');
});

pool.on('error', (err, client) => {
  console.error('Unexpected error on idle client', err);
  process.exit(-1);
});

// Graceful shutdown
process.on('SIGINT', () => {
  pool.end(() => {
    console.log('Pool has ended');
    process.exit(0);
  });
});

// Query with error handling
const executeQuery = async (text, params) => {
  const start = Date.now();
  try {
    const res = await pool.query(text, params);
    const duration = Date.now() - start;
    
    // Log slow queries
    if (duration > 1000) {
      console.warn(`Slow query detected: ${duration}ms`, { text, params });
    }
    
    return res;
  } catch (error) {
    console.error('Database query error:', error);
    throw error;
  }
};

MongoDB Connection Optimization

const mongoose = require('mongoose');

// Optimized MongoDB connection
const connectDB = async () => {
  try {
    const conn = await mongoose.connect(process.env.MONGODB_URI, {
      // Connection pool settings
      maxPoolSize: 10,        // Maximum connections
      minPoolSize: 2,         // Minimum connections
      maxIdleTimeMS: 30000,   // Close connections after 30s idle
      serverSelectionTimeoutMS: 5000, // Timeout for server selection
      socketTimeoutMS: 45000, // Socket timeout
      
      // Buffering settings
      bufferMaxEntries: 0,    // Disable mongoose buffering
      bufferCommands: false,  // Disable mongoose buffering
      
      // Write concern
      w: 'majority',
      wtimeoutMS: 2500,
      
      // Read preference
      readPreference: 'primary'
    });
    
    console.log(`MongoDB Connected: ${conn.connection.host}`);
    
    // Monitor connection events
    mongoose.connection.on('error', (err) => {
      console.error('MongoDB connection error:', err);
    });
    
    mongoose.connection.on('disconnected', () => {
      console.log('MongoDB disconnected');
    });
    
  } catch (error) {
    console.error('Database connection failed:', error);
    process.exit(1);
  }
};

// Query performance monitoring
mongoose.set('debug', (collectionName, method, query, doc) => {
  const start = Date.now();
  console.log(`${collectionName}.${method}`, JSON.stringify(query));
});

Caching Strategies

Redis Caching Patterns

const redis = require('redis');
const client = redis.createClient({
  host: process.env.REDIS_HOST,
  port: process.env.REDIS_PORT,
  password: process.env.REDIS_PASSWORD,
  
  // Connection settings
  connect_timeout: 60000,
  lazyConnect: true,
  
  // Retry strategy
  retry_strategy: (options) => {
    if (options.error && options.error.code === 'ECONNREFUSED') {
      return new Error('The server refused the connection');
    }
    if (options.total_retry_time > 1000 * 60 * 60) {
      return new Error('Retry time exhausted');
    }
    if (options.attempt > 10) {
      return undefined;
    }
    return Math.min(options.attempt * 100, 3000);
  }
});

// Cache-aside pattern
class CacheService {
  constructor(redisClient, defaultTTL = 3600) {
    this.redis = redisClient;
    this.defaultTTL = defaultTTL;
  }
  
  async get(key) {
    try {
      const cached = await this.redis.get(key);
      return cached ? JSON.parse(cached) : null;
    } catch (error) {
      console.error('Cache get error:', error);
      return null; // Fail gracefully
    }
  }
  
  async set(key, value, ttl = this.defaultTTL) {
    try {
      await this.redis.setex(key, ttl, JSON.stringify(value));
    } catch (error) {
      console.error('Cache set error:', error);
    }
  }
  
  async del(key) {
    try {
      await this.redis.del(key);
    } catch (error) {
      console.error('Cache delete error:', error);
    }
  }
  
  // Cache with automatic refresh
  async getOrSet(key, fetchFunction, ttl = this.defaultTTL) {
    let cached = await this.get(key);
    
    if (cached === null) {
      const fresh = await fetchFunction();
      await this.set(key, fresh, ttl);
      return fresh;
    }
    
    return cached;
  }
  
  // Invalidate pattern-based keys
  async invalidatePattern(pattern) {
    try {
      const keys = await this.redis.keys(pattern);
      if (keys.length > 0) {
        await this.redis.del(...keys);
      }
    } catch (error) {
      console.error('Cache invalidation error:', error);
    }
  }
}

// Usage example
const cache = new CacheService(client);

const getUserWithCache = async (userId) => {
  const cacheKey = `user:${userId}`;
  
  return await cache.getOrSet(cacheKey, async () => {
    const user = await User.findById(userId);
    return user;
  }, 1800); // 30 minutes TTL
};

Database Monitoring and Alerting

Performance Metrics Collection

// Database performance monitoring
class DatabaseMonitor {
  constructor() {
    this.metrics = {
      queryCount: 0,
      slowQueries: 0,
      errors: 0,
      totalResponseTime: 0
    };
    
    // Reset metrics every minute
    setInterval(() => {
      this.reportMetrics();
      this.resetMetrics();
    }, 60000);
  }
  
  recordQuery(duration, error = null) {
    this.metrics.queryCount++;
    this.metrics.totalResponseTime += duration;
    
    if (error) {
      this.metrics.errors++;
    }
    
    if (duration > 1000) { // Slow query threshold
      this.metrics.slowQueries++;
      console.warn(`Slow query detected: ${duration}ms`);
    }
  }
  
  reportMetrics() {
    const avgResponseTime = this.metrics.totalResponseTime / this.metrics.queryCount || 0;
    
    console.log('Database Metrics:', {
      queriesPerMinute: this.metrics.queryCount,
      averageResponseTime: avgResponseTime.toFixed(2),
      slowQueries: this.metrics.slowQueries,
      errorRate: (this.metrics.errors / this.metrics.queryCount * 100).toFixed(2)
    });
    
    // Alert on high error rate
    if (this.metrics.errors / this.metrics.queryCount > 0.05) {
      console.error('HIGH ERROR RATE DETECTED!');
      // Send alert to monitoring system
    }
  }
  
  resetMetrics() {
    this.metrics = {
      queryCount: 0,
      slowQueries: 0,
      errors: 0,
      totalResponseTime: 0
    };
  }
}

const dbMonitor = new DatabaseMonitor();

// Wrap database queries with monitoring
const monitoredQuery = async (queryFunction) => {
  const start = Date.now();
  try {
    const result = await queryFunction();
    const duration = Date.now() - start;
    dbMonitor.recordQuery(duration);
    return result;
  } catch (error) {
    const duration = Date.now() - start;
    dbMonitor.recordQuery(duration, error);
    throw error;
  }
};

Health Check Endpoints

// Database health check
app.get('/health/database', async (req, res) => {
  try {
    // Test database connectivity
    const start = Date.now();
    await pool.query('SELECT 1');
    const dbLatency = Date.now() - start;
    
    // Test cache connectivity
    const cacheStart = Date.now();
    await redis.ping();
    const cacheLatency = Date.now() - cacheStart;
    
    // Get connection pool stats
    const poolStats = {
      totalConnections: pool.totalCount,
      idleConnections: pool.idleCount,
      waitingClients: pool.waitingCount
    };
    
    res.json({
      status: 'healthy',
      database: {
        status: 'connected',
        latency: `${dbLatency}ms`,
        pool: poolStats
      },
      cache: {
        status: 'connected',
        latency: `${cacheLatency}ms`
      },
      timestamp: new Date().toISOString()
    });
    
  } catch (error) {
    res.status(503).json({
      status: 'unhealthy',
      error: error.message,
      timestamp: new Date().toISOString()
    });
  }
});

Database optimization is an ongoing process. Start with proper indexing, monitor query performance, implement caching strategically, and always measure the impact of your optimizations. Remember: premature optimization is the root of all evil, but ignoring performance until it’s a problem is equally dangerous.