Advanced Database Performance Optimization Techniques

-- Create composite index for queries filtering on multiple columns
CREATE INDEX idx_user_created_at ON users(status, created_at DESC);

-- This index supports:
-- SELECT * FROM users WHERE status = 'active' ORDER BY created_at DESC;
-- SELECT * FROM users WHERE status = 'active' AND created_at > '2024-01-01';

-- Column order matters in composite indexes
CREATE INDEX idx_user_status_date ON users(created_at, status);
-- Good for: WHERE created_at > '2024-01-01' AND status = 'active'
-- Bad for: WHERE status = 'active' (can't use index efficiently)

-- Create covering index
CREATE INDEX idx_user_covering ON users(status, created_at, name, email);

-- Query can be satisfied entirely from the index
SELECT name, email
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 10;

-- Without covering index: Index seek + table lookup
-- With covering index: Index scan only (much faster)

-- Index only active users
CREATE INDEX idx_active_users ON users(email)
WHERE status = 'active';

-- Index recent orders
CREATE INDEX idx_recent_orders ON orders(user_id, created_at)
WHERE created_at > CURRENT_DATE - INTERVAL '90 days';

-- This is more efficient than indexing all rows
-- especially when most queries target a subset of data

-- Analyze query execution plan
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 10;

-- Look for:
-- Seq Scan (bad) → should use Index Scan
-- Nested Loop (bad for large datasets) → consider Hash Join
-- high cost or actual time

-- ❌ Inefficient: Subquery in WHERE clause
SELECT u.name, o.total
FROM users u
WHERE u.id IN (
  SELECT o.user_id
  FROM orders o
  WHERE o.total > 1000
);

-- ✅ Efficient: JOIN with proper indexing
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.total > 1000;

-- Ensure indexes exist:
CREATE INDEX idx_orders_user_total ON orders(user_id, total);
CREATE INDEX idx_users_id ON users(id);

-- ❌ Using correlated subqueries (slow)
SELECT
  id,
  amount,
  (SELECT AVG(amount) FROM sales WHERE user_id = s.user_id) as avg_amount
FROM sales s;

-- ✅ Using window functions (fast)
SELECT
  id,
  amount,
  AVG(amount) OVER (PARTITION BY user_id) as avg_amount
FROM sales;

-- CTEs can improve readability and performance
WITH active_users AS (
  SELECT id, name
  FROM users
  WHERE status = 'active'
),
user_orders AS (
  SELECT user_id, COUNT(*) as order_count, SUM(total) as total_spent
  FROM orders
  WHERE created_at > CURRENT_DATE - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT
  u.name,
  COALESCE(o.order_count, 0) as order_count,
  COALESCE(o.total_spent, 0) as total_spent
FROM active_users u
LEFT JOIN user_orders o ON u.id = o.user_id
ORDER BY o.total_spent DESC NULLS LAST
LIMIT 100;

// Redis caching implementation
const Redis = require('ioredis');
const redis = new Redis();

class UserRepository {
  async getUserById(userId) {
    const cacheKey = `user:${userId}`;

    // Try cache first
    const cached = await redis.get(cacheKey);
    if (cached) {
      return JSON.parse(cached);
    }

    // Cache miss - query database
    const user = await db.query(
      'SELECT * FROM users WHERE id = $1',
      [userId]
    );

    // Cache for 1 hour
    await redis.setex(cacheKey, 3600, JSON.stringify(user));

    return user;
  }

  async updateUser(userId, data) {
    const user = await db.query(
      'UPDATE users SET ... WHERE id = $1 RETURNING *',
      [userId, data]
    );

    // Invalidate cache
    await redis.del(`user:${userId}`);

    return user;
  }
}

// Cache expensive query results
class AnalyticsRepository {
  async getTopProducts(dateRange) {
    const cacheKey = `top_products:${JSON.stringify(dateRange)}`;

    const cached = await redis.get(cacheKey);
    if (cached) {
      return JSON.parse(cached);
    }

    const result = await db.query(`
      SELECT
        p.id,
        p.name,
        SUM(oi.quantity) as total_sold,
        SUM(oi.quantity * oi.price) as revenue
      FROM products p
      JOIN order_items oi ON p.id = oi.product_id
      JOIN orders o ON oi.order_id = o.id
      WHERE o.created_at BETWEEN $1 AND $2
      GROUP BY p.id, p.name
      ORDER BY revenue DESC
      LIMIT 100
    `, [dateRange.start, dateRange.end]);

    // Cache for 15 minutes
    await redis.setex(cacheKey, 900, JSON.stringify(result));

    return result;
  }
}

// PostgreSQL connection pool with pg
const { Pool } = require('pg');

const pool = new Pool({
  host: process.env.DB_HOST,
  port: 5432,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  max: 20, // Maximum pool size
  idleTimeoutMillis: 30000, // Close idle connections after 30s
  connectionTimeoutMillis: 2000, // Return error after 2s if connection unavailable
});

// Use connection pool
async function getUsers() {
  const client = await pool.connect();
  try {
    const result = await client.query('SELECT * FROM users');
    return result.rows;
  } finally {
    client.release(); // Important: release connection back to pool
  }
}

// Direct reads to replicas, writes to primary
const primaryPool = new Pool({ host: 'primary-db.example.com' });
const replicaPool = new Pool({ host: 'replica-db.example.com' });

class Repository {
  async findById(id) {
    // Read from replica (eventually consistent)
    return await replicaPool.query(
      'SELECT * FROM users WHERE id = $1',
      [id]
    );
  }

  async create(data) {
    // Write to primary
    return await primaryPool.query(
      'INSERT INTO users (...) VALUES (...) RETURNING *',
      [data]
    );
  }
}

-- Partition large table by date
CREATE TABLE orders (
  id BIGSERIAL,
  user_id INTEGER NOT NULL,
  total NUMERIC(10,2) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  status VARCHAR(50) NOT NULL
) PARTITION BY RANGE (created_at);

-- Create partitions
CREATE TABLE orders_2024_q1 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE orders_2024_q2 PARTITION OF orders
  FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- Query automatically hits relevant partition
SELECT * FROM orders WHERE created_at >= '2024-02-01';

-- Benefits:
-- Faster queries (scan smaller partition)
-- Easier maintenance (drop old partitions)
-- Better parallelism

-- Create materialized view for expensive aggregations
CREATE MATERIALIZED VIEW user_daily_stats AS
SELECT
  user_id,
  DATE(created_at) as date,
  COUNT(*) as order_count,
  SUM(total) as total_spent,
  AVG(total) as avg_order_value
FROM orders
GROUP BY user_id, DATE(created_at);

-- Create index for fast lookups
CREATE INDEX idx_daily_stats_user_date ON user_daily_stats(user_id, date);

-- Query is now instant
SELECT * FROM user_daily_stats
WHERE user_id = 123 AND date >= CURRENT_DATE - INTERVAL '30 days';

-- Refresh materialized view periodically
REFRESH MATERIALIZED VIEW user_daily_stats;

-- Schedule refresh with cron
-- Example: Refresh every hour

-- Find slow queries
SELECT
  query,
  calls,
  total_time,
  mean_time,
  max_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- Find missing indexes
SELECT
  schemaname,
  tablename,
  seq_scan,
  idx_scan,
  seq_scan / idx_scan as seq_to_idx_ratio
FROM pg_stat_user_tables
WHERE seq_scan > 0 AND idx_scan > 0
ORDER BY seq_to_idx_ratio DESC;

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