Database Performance Optimization

Database optimization strategies including query optimization, indexing, and monitoring for Smart Shelf.

Database Performance Optimization

Database performance is fundamental to Smart Shelf's overall responsiveness. This section covers comprehensive strategies for optimizing PostgreSQL database performance, from query optimization to monitoring and maintenance.

Query Optimization

Index Strategy

Proper indexing is crucial for query performance, especially with large datasets.

Primary Indexes for Core Operations

-- Products table indexes
CREATE INDEX CONCURRENTLY idx_products_name ON products(name);
CREATE INDEX CONCURRENTLY idx_products_sku ON products(sku);
CREATE INDEX CONCURRENTLY idx_products_category ON products(category_id);
CREATE INDEX CONCURRENTLY idx_products_active ON products(is_active) WHERE is_active = true;
CREATE INDEX CONCURRENTLY idx_products_created_at ON products(created_at DESC);

-- Inventory table indexes
CREATE INDEX CONCURRENTLY idx_inventory_product_warehouse 
ON inventory(product_id, warehouse_id);
CREATE INDEX CONCURRENTLY idx_inventory_quantity ON inventory(quantity_on_hand);
CREATE INDEX CONCURRENTLY idx_inventory_low_stock 
ON inventory(product_id) WHERE quantity_on_hand <= reorder_point;

-- Stock movements table indexes
CREATE INDEX CONCURRENTLY idx_stock_movements_product_date 
ON stock_movements(product_id, created_at DESC);
CREATE INDEX CONCURRENTLY idx_stock_movements_type ON stock_movements(movement_type);
CREATE INDEX CONCURRENTLY idx_stock_movements_warehouse ON stock_movements(warehouse_id);

-- Orders table indexes
CREATE INDEX CONCURRENTLY idx_purchase_orders_supplier ON purchase_orders(supplier_id);
CREATE INDEX CONCURRENTLY idx_purchase_orders_status ON purchase_orders(status);
CREATE INDEX CONCURRENTLY idx_purchase_orders_date ON purchase_orders(created_at DESC);

CREATE INDEX CONCURRENTLY idx_sales_orders_customer ON sales_orders(customer_id);
CREATE INDEX CONCURRENTLY idx_sales_orders_status ON sales_orders(status);
CREATE INDEX CONCURRENTLY idx_sales_orders_date ON sales_orders(created_at DESC);

Composite Indexes for Multi-Column Queries

-- Frequently used filter combinations
CREATE INDEX CONCURRENTLY idx_products_category_active 
ON products(category_id, is_active) WHERE is_active = true;

CREATE INDEX CONCURRENTLY idx_inventory_warehouse_available 
ON inventory(warehouse_id, quantity_on_hand) WHERE quantity_on_hand > 0;

CREATE INDEX CONCURRENTLY idx_orders_status_date 
ON purchase_orders(status, created_at DESC);

-- Search and filter combinations
CREATE INDEX CONCURRENTLY idx_products_search_category 
ON products(category_id, name) WHERE is_active = true;

Partial Indexes for Specific Conditions

-- Index only active products
CREATE INDEX CONCURRENTLY idx_products_active_name 
ON products(name) WHERE is_active = true;

-- Index only available inventory
CREATE INDEX CONCURRENTLY idx_inventory_available 
ON inventory(product_id, warehouse_id) WHERE quantity_on_hand > 0;

-- Index only pending orders
CREATE INDEX CONCURRENTLY idx_orders_pending 
ON purchase_orders(created_at DESC) WHERE status = 'pending';

-- Index only recent stock movements
CREATE INDEX CONCURRENTLY idx_stock_movements_recent 
ON stock_movements(product_id, created_at DESC) 
WHERE created_at > CURRENT_DATE - INTERVAL '30 days';

Full-Text Search Indexes

-- Full-text search for products
ALTER TABLE products ADD COLUMN search_vector tsvector;

UPDATE products SET search_vector = 
  to_tsvector('english', name || ' ' || coalesce(description, '') || ' ' || sku);

CREATE INDEX CONCURRENTLY idx_products_search 
ON products USING gin(search_vector);

-- Trigger to maintain search vector
CREATE OR REPLACE FUNCTION update_product_search_vector()
RETURNS trigger AS $$
BEGIN
  NEW.search_vector := to_tsvector('english', 
    NEW.name || ' ' || coalesce(NEW.description, '') || ' ' || NEW.sku
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER products_search_vector_update
  BEFORE INSERT OR UPDATE ON products
  FOR EACH ROW EXECUTE FUNCTION update_product_search_vector();

Query Optimization Techniques

Write efficient queries that leverage indexes and minimize resource usage.

Optimized Product Search Query

-- Before optimization (slow)
SELECT 
  p.id,
  p.name,
  p.sku,
  p.price,
  c.name as category_name,
  i.quantity_on_hand,
  w.name as warehouse_name
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN inventory i ON p.id = i.product_id
LEFT JOIN warehouses w ON i.warehouse_id = w.id
WHERE p.name ILIKE '%widget%'
ORDER BY p.name
LIMIT 50;

-- After optimization (fast)
SELECT 
  p.id,
  p.name,
  p.sku,
  p.price,
  c.name as category_name,
  i.quantity_on_hand,
  w.name as warehouse_name
FROM products p
JOIN categories c ON p.category_id = c.id
JOIN inventory i ON p.id = i.product_id AND i.quantity_on_hand > 0
JOIN warehouses w ON i.warehouse_id = w.id
WHERE p.is_active = true
  AND p.search_vector @@ plainto_tsquery('english', 'widget')
ORDER BY p.name
LIMIT 50;

Efficient Inventory Summary Query

-- Optimized inventory summary with aggregation
WITH inventory_summary AS (
  SELECT 
    p.id,
    p.name,
    p.sku,
    SUM(i.quantity_on_hand) as total_quantity,
    COUNT(i.warehouse_id) as warehouse_count,
    AVG(i.quantity_on_hand) as avg_quantity_per_warehouse
  FROM products p
  JOIN inventory i ON p.id = i.product_id
  WHERE p.is_active = true
    AND i.quantity_on_hand >= 0
  GROUP BY p.id, p.name, p.sku
  HAVING SUM(i.quantity_on_hand) > 0
)
SELECT * FROM inventory_summary
ORDER BY total_quantity DESC
LIMIT 100;

Optimized Recent Activity Query

-- Recent stock movements with efficient date filtering
SELECT 
  sm.id,
  sm.movement_type,
  sm.quantity,
  sm.created_at,
  p.name as product_name,
  p.sku,
  w.name as warehouse_name
FROM stock_movements sm
JOIN products p ON sm.product_id = p.id
JOIN warehouses w ON sm.warehouse_id = w.id
WHERE sm.created_at >= CURRENT_DATE - INTERVAL '7 days'
  AND sm.created_at <= CURRENT_DATE + INTERVAL '1 day'
ORDER BY sm.created_at DESC
LIMIT 100;

Query Analysis and Monitoring

Use PostgreSQL's EXPLAIN functionality to analyze and optimize queries.

Query Performance Analysis

-- Analyze query execution plan
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) 
SELECT 
  p.id,
  p.name,
  i.quantity_on_hand,
  w.name as warehouse_name
FROM products p
JOIN inventory i ON p.id = i.product_id
JOIN warehouses w ON i.warehouse_id = w.id
WHERE p.category_id = '123e4567-e89b-12d3-a456-426614174000'
  AND i.quantity_on_hand > 0
ORDER BY p.name
LIMIT 50;

-- Check for sequential scans (should be avoided on large tables)
SELECT 
  schemaname,
  tablename,
  seq_scan,
  seq_tup_read,
  idx_scan,
  idx_tup_fetch,
  seq_tup_read / GREATEST(seq_scan, 1) as avg_seq_read
FROM pg_stat_user_tables
WHERE seq_scan > 1000  -- Tables with many sequential scans
ORDER BY seq_tup_read DESC;

-- Identify slow queries
SELECT 
  query,
  calls,
  total_time,
  mean_time,
  rows,
  100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
WHERE mean_time > 100  -- Queries taking more than 100ms on average
ORDER BY total_time DESC
LIMIT 20;

Connection Management

Connection Pooling

Implement efficient connection pooling to handle concurrent requests.

Supabase Connection Configuration

// lib/supabase/connection-pool.ts
import { createClient } from '@supabase/supabase-js'

interface PoolConfig {
  min: number
  max: number
  idleTimeoutMillis: number
  connectionTimeoutMillis: number
}

class ConnectionPool {
  private pools: Map<string, any> = new Map()
  private config: PoolConfig

  constructor(config: PoolConfig) {
    this.config = config
  }

  getClient(key: string = 'default') {
    if (!this.pools.has(key)) {
      const client = createClient(
        process.env.NEXT_PUBLIC_SUPABASE_URL!,
        process.env.SUPABASE_SERVICE_ROLE_KEY!,
        {
          db: {
            schema: 'public',
          },
          auth: {
            autoRefreshToken: false,
            persistSession: false,
          },
          global: {
            headers: {
              'X-Client-Info': 'smart-shelf@1.0.0',
            },
          },
        }
      )
      
      this.pools.set(key, client)
    }
    
    return this.pools.get(key)
  }

  async executeQuery<T>(
    queryFn: (client: any) => Promise<T>,
    poolKey: string = 'default'
  ): Promise<T> {
    const client = this.getClient(poolKey)
    
    const startTime = performance.now()
    
    try {
      const result = await queryFn(client)
      
      const duration = performance.now() - startTime
      
      // Log slow queries
      if (duration > 1000) {
        console.warn(`Slow query detected: ${duration}ms`)
      }
      
      return result
    } catch (error) {
      console.error('Database query error:', error)
      throw error
    }
  }
}

export const connectionPool = new ConnectionPool({
  min: 2,
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000
})

Connection Monitoring

-- Monitor active connections
SELECT 
  state,
  count(*) as connections,
  max(now() - state_change) as max_duration
FROM pg_stat_activity
WHERE state IS NOT NULL
GROUP BY state
ORDER BY connections DESC;

-- Monitor connection usage by database
SELECT 
  datname,
  count(*) as connections,
  max(now() - state_change) as max_duration
FROM pg_stat_activity
WHERE state = 'active'
GROUP BY datname
ORDER BY connections DESC;

-- Check for long-running queries
SELECT 
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - pg_stat_activity.query_start > interval '5 minutes'
ORDER BY duration DESC;

Database Monitoring

Performance Metrics Collection

Monitor key database performance metrics for optimization opportunities.

Database Performance Monitoring Queries

-- Table sizes and growth
SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size,
  pg_total_relation_size(schemaname||'.'||tablename) as size_bytes
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Index usage statistics
SELECT 
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch,
  pg_size_pretty(pg_relation_size(indexname::regclass)) as index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Unused indexes (candidates for removal)
SELECT 
  schemaname,
  tablename,
  indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexname::regclass)) as index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexname NOT LIKE '%_pkey'  -- Exclude primary keys
ORDER BY pg_relation_size(indexname::regclass) DESC;

-- Cache hit ratios
SELECT 
  'Database' as type,
  round(100.0 * sum(blks_hit) / (sum(blks_hit) + sum(blks_read)), 2) as hit_ratio
FROM pg_stat_database
WHERE datname = current_database()
UNION ALL
SELECT 
  'Tables' as type,
  round(100.0 * sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)), 2) as hit_ratio
FROM pg_statio_user_tables
UNION ALL
SELECT 
  'Indexes' as type,
  round(100.0 * sum(idx_blks_hit) / (sum(idx_blks_hit) + sum(idx_blks_read)), 2) as hit_ratio
FROM pg_statio_user_indexes;

Automated Performance Monitoring

Implement automated monitoring for proactive performance management.

Performance Monitoring Script

// lib/monitoring/database-monitoring.ts
interface DatabaseMetrics {
  connectionCount: number
  activeQueries: number
  cacheHitRatio: number
  avgQueryTime: number
  slowQueries: number
  tableSize: Record<string, number>
  indexUsage: Record<string, number>
}

export class DatabaseMonitor {
  private supabase: any

  constructor(supabase: any) {
    this.supabase = supabase
  }

  async collectMetrics(): Promise<DatabaseMetrics> {
    const [
      connections,
      queries,
      cacheHit,
      slowQueries,
      tableSizes,
      indexUsage
    ] = await Promise.all([
      this.getConnectionCount(),
      this.getActiveQueries(),
      this.getCacheHitRatio(),
      this.getSlowQueries(),
      this.getTableSizes(),
      this.getIndexUsage()
    ])

    return {
      connectionCount: connections,
      activeQueries: queries.length,
      cacheHitRatio: cacheHit,
      avgQueryTime: queries.length > 0 
        ? queries.reduce((sum, q) => sum + q.duration, 0) / queries.length 
        : 0,
      slowQueries: slowQueries.length,
      tableSize: tableSizes,
      indexUsage: indexUsage
    }
  }

  private async getConnectionCount(): Promise<number> {
    const { data } = await this.supabase.rpc('get_connection_count')
    return data || 0
  }

  private async getActiveQueries(): Promise<any[]> {
    const { data } = await this.supabase.rpc('get_active_queries')
    return data || []
  }

  private async getCacheHitRatio(): Promise<number> {
    const { data } = await this.supabase.rpc('get_cache_hit_ratio')
    return data || 0
  }

  private async getSlowQueries(): Promise<any[]> {
    const { data } = await this.supabase.rpc('get_slow_queries')
    return data || []
  }

  private async getTableSizes(): Promise<Record<string, number>> {
    const { data } = await this.supabase.rpc('get_table_sizes')
    return data?.reduce((acc: any, row: any) => {
      acc[row.table_name] = row.size_bytes
      return acc
    }, {}) || {}
  }

  private async getIndexUsage(): Promise<Record<string, number>> {
    const { data } = await this.supabase.rpc('get_index_usage')
    return data?.reduce((acc: any, row: any) => {
      acc[row.index_name] = row.usage_count
      return acc
    }, {}) || {}
  }

  async generateReport(): Promise<string> {
    const metrics = await this.collectMetrics()
    
    return `
Database Performance Report
===========================
Connections: ${metrics.connectionCount}
Active Queries: ${metrics.activeQueries}
Cache Hit Ratio: ${metrics.cacheHitRatio.toFixed(2)}%
Average Query Time: ${metrics.avgQueryTime.toFixed(2)}ms
Slow Queries: ${metrics.slowQueries}

Largest Tables:
${Object.entries(metrics.tableSize)
  .sort(([,a], [,b]) => b - a)
  .slice(0, 5)
  .map(([table, size]) => `  ${table}: ${(size / 1024 / 1024).toFixed(2)} MB`)
  .join('\n')}

Most Used Indexes:
${Object.entries(metrics.indexUsage)
  .sort(([,a], [,b]) => b - a)
  .slice(0, 5)
  .map(([index, usage]) => `  ${index}: ${usage} scans`)
  .join('\n')}
    `.trim()
  }
}

// Usage
export async function monitorDatabase() {
  const monitor = new DatabaseMonitor(connectionPool.getClient())
  const report = await monitor.generateReport()
  console.log(report)
  
  // Send to monitoring service or save to logs
}

Database Health Checks

Implement regular health checks to identify and address issues proactively.

Health Check Functions

-- Create database functions for health monitoring
CREATE OR REPLACE FUNCTION get_connection_count()
RETURNS INTEGER AS $$
BEGIN
  RETURN (SELECT count(*) FROM pg_stat_activity WHERE state = 'active');
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION get_cache_hit_ratio()
RETURNS NUMERIC AS $$
BEGIN
  RETURN (
    SELECT round(
      100.0 * sum(blks_hit) / (sum(blks_hit) + sum(blks_read)), 2
    )
    FROM pg_stat_database
    WHERE datname = current_database()
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION get_slow_queries()
RETURNS TABLE(
  query_text TEXT,
  duration_seconds NUMERIC,
  calls BIGINT,
  mean_time NUMERIC
) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    query,
    EXTRACT(EPOCH FROM (total_time / 1000.0)) as duration_seconds,
    calls,
    mean_time
  FROM pg_stat_statements
  WHERE mean_time > 100  -- Queries taking more than 100ms
  ORDER BY total_time DESC
  LIMIT 10;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION get_table_sizes()
RETURNS TABLE(
  table_name TEXT,
  size_bytes BIGINT
) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    tablename::TEXT,
    pg_total_relation_size(schemaname||'.'||tablename) as size_bytes
  FROM pg_tables
  WHERE schemaname = 'public'
  ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Automated Health Check Service

// lib/services/health-check.ts
export class DatabaseHealthCheck {
  private supabase: any
  private alertThresholds = {
    maxConnections: 80,
    minCacheHitRatio: 95,
    maxSlowQueries: 5,
    maxAvgQueryTime: 200
  }

  constructor(supabase: any) {
    this.supabase = supabase
  }

  async performHealthCheck(): Promise<{
    healthy: boolean
    issues: string[]
    metrics: any
  }> {
    const monitor = new DatabaseMonitor(this.supabase)
    const metrics = await monitor.collectMetrics()
    
    const issues: string[] = []

    // Check connection count
    if (metrics.connectionCount > this.alertThresholds.maxConnections) {
      issues.push(`High connection count: ${metrics.connectionCount}`)
    }

    // Check cache hit ratio
    if (metrics.cacheHitRatio < this.alertThresholds.minCacheHitRatio) {
      issues.push(`Low cache hit ratio: ${metrics.cacheHitRatio.toFixed(2)}%`)
    }

    // Check slow queries
    if (metrics.slowQueries > this.alertThresholds.maxSlowQueries) {
      issues.push(`Too many slow queries: ${metrics.slowQueries}`)
    }

    // Check average query time
    if (metrics.avgQueryTime > this.alertThresholds.maxAvgQueryTime) {
      issues.push(`High average query time: ${metrics.avgQueryTime.toFixed(2)}ms`)
    }

    return {
      healthy: issues.length === 0,
      issues,
      metrics
    }
  }

  async scheduleHealthChecks() {
    // Run health check every 5 minutes
    setInterval(async () => {
      try {
        const result = await this.performHealthCheck()
        
        if (!result.healthy) {
          console.error('Database health check failed:', result.issues)
          // Send alerts to monitoring service
        } else {
          console.log('Database health check passed')
        }
      } catch (error) {
        console.error('Health check error:', error)
      }
    }, 5 * 60 * 1000) // 5 minutes
  }
}

Database performance optimization is an ongoing process that requires regular monitoring, analysis, and tuning. These strategies provide a comprehensive foundation for maintaining optimal database performance in Smart Shelf.

Backend Performance

Caching Strategies