Performance & Optimization

Database performance optimization, indexing strategies, and query optimization for Smart Shelf.

Performance & Optimization

Comprehensive database performance optimization strategies, indexing patterns, and query optimization techniques for Smart Shelf's PostgreSQL/Supabase architecture.

Performance Architecture Overview

Smart Shelf's database performance strategy focuses on:

Strategic Indexing - Optimized indexes for common query patterns
Query Optimization - Efficient queries with proper execution plans
Connection Pooling - Managed database connections
Caching Strategies - Multi-level caching implementation
Monitoring & Analysis - Performance tracking and optimization

Indexing Strategy

Primary Indexes for Foreign Keys

-- Core foreign key indexes
CREATE INDEX CONCURRENTLY idx_products_category_id ON products(category_id);
CREATE INDEX CONCURRENTLY idx_inventory_product_id ON inventory(product_id);
CREATE INDEX CONCURRENTLY idx_inventory_warehouse_id ON inventory(warehouse_id);
CREATE INDEX CONCURRENTLY idx_stock_movements_product_id ON stock_movements(product_id);
CREATE INDEX CONCURRENTLY idx_stock_movements_warehouse_id ON stock_movements(warehouse_id);
CREATE INDEX CONCURRENTLY idx_stock_movements_created_by ON stock_movements(created_by);

-- User and role indexes
CREATE INDEX CONCURRENTLY idx_user_profiles_warehouse_id ON user_profiles(warehouse_id);
CREATE INDEX CONCURRENTLY idx_user_profiles_role ON user_profiles(role);

Composite Indexes for Common Queries

-- Inventory management queries
CREATE INDEX CONCURRENTLY idx_inventory_product_warehouse 
ON inventory(product_id, warehouse_id);

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

-- Stock movement history queries
CREATE INDEX CONCURRENTLY idx_stock_movements_product_date 
ON stock_movements(product_id, created_at DESC);

CREATE INDEX CONCURRENTLY idx_stock_movements_warehouse_date 
ON stock_movements(warehouse_id, created_at DESC);

CREATE INDEX CONCURRENTLY idx_stock_movements_type_date 
ON stock_movements(movement_type, created_at DESC);

-- Product search and filtering
CREATE INDEX CONCURRENTLY idx_products_name_trgm 
ON products USING gin(name gin_trgm_ops);

CREATE INDEX CONCURRENTLY idx_products_sku_trgm 
ON products USING gin(sku gin_trgm_ops);

CREATE INDEX CONCURRENTLY idx_products_category_active 
ON products(category_id, is_active) 
WHERE is_active = true;

Partial Indexes for Active Records

-- Active products index
CREATE INDEX CONCURRENTLY idx_products_active 
ON products(name, sku) 
WHERE is_active = true;

-- Active warehouses index
CREATE INDEX CONCURRENTLY idx_warehouses_active 
ON warehouses(name, code) 
WHERE is_active = true;

-- Low stock alerts
CREATE INDEX CONCURRENTLY idx_inventory_low_stock 
ON inventory(product_id, warehouse_id, quantity_available) 
WHERE quantity_available <= 10;

-- Recent stock movements (last 30 days)
CREATE INDEX CONCURRENTLY idx_stock_movements_recent 
ON stock_movements(product_id, created_at DESC) 
WHERE created_at > NOW() - INTERVAL '30 days';

Full-Text Search Indexes

-- Enable trigram extension for fuzzy search
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Full-text search for products
CREATE INDEX CONCURRENTLY idx_products_fulltext 
ON products USING gin(
  to_tsvector('english', name || ' ' || COALESCE(description, '') || ' ' || sku)
);

-- Trigram indexes for fuzzy matching
CREATE INDEX CONCURRENTLY idx_products_name_trgm 
ON products USING gin(name gin_trgm_ops);

CREATE INDEX CONCURRENTLY idx_products_sku_trgm 
ON products USING gin(sku gin_trgm_ops);

Query Optimization

Efficient Inventory Queries

Current Inventory with Stock Status

-- Optimized inventory query with stock status
CREATE OR REPLACE VIEW current_inventory_optimized AS
SELECT 
  i.product_id,
  p.name AS product_name,
  p.sku,
  p.category_id,
  pc.name AS category_name,
  i.warehouse_id,
  w.name AS warehouse_name,
  i.quantity_on_hand,
  i.quantity_allocated,
  i.quantity_available,
  p.min_stock_level,
  p.max_stock_level,
  CASE 
    WHEN i.quantity_available <= 0 THEN 'OUT_OF_STOCK'
    WHEN i.quantity_available <= p.min_stock_level THEN 'LOW_STOCK'
    WHEN i.quantity_available >= p.max_stock_level THEN 'OVERSTOCK'
    ELSE 'IN_STOCK'
  END AS stock_status,
  p.cost_price * i.quantity_on_hand AS total_cost_value,
  p.selling_price * i.quantity_available AS total_selling_value,
  i.last_counted,
  i.updated_at
FROM inventory i
JOIN products p ON i.product_id = p.id
JOIN warehouses w ON i.warehouse_id = w.id
LEFT JOIN product_categories pc ON p.category_id = pc.id
WHERE p.is_active = true AND w.is_active = true;

-- Index to support the view
CREATE INDEX CONCURRENTLY idx_inventory_view_support 
ON inventory(product_id, warehouse_id, quantity_available, updated_at)
WHERE quantity_available >= 0;

Stock Movement History with Performance

-- Optimized stock movement history function
CREATE OR REPLACE FUNCTION get_stock_movements_optimized(
  p_product_id UUID DEFAULT NULL,
  p_warehouse_id UUID DEFAULT NULL,
  p_from_date TIMESTAMPTZ DEFAULT NOW() - INTERVAL '30 days',
  p_to_date TIMESTAMPTZ DEFAULT NOW(),
  p_limit INTEGER DEFAULT 100
)
RETURNS TABLE (
  id UUID,
  product_name VARCHAR,
  warehouse_name VARCHAR,
  movement_type movement_type,
  quantity INTEGER,
  unit_cost DECIMAL,
  total_cost DECIMAL,
  created_at TIMESTAMPTZ,
  created_by_name VARCHAR,
  reason VARCHAR,
  running_balance BIGINT
) AS $$
BEGIN
  RETURN QUERY
  WITH movement_data AS (
    SELECT 
      sm.id,
      p.name AS product_name,
      w.name AS warehouse_name,
      sm.movement_type,
      sm.quantity,
      sm.unit_cost,
      sm.unit_cost * sm.quantity AS total_cost,
      sm.created_at,
      up.full_name AS created_by_name,
      sm.reason,
      SUM(
        CASE 
          WHEN sm.movement_type IN ('IN', 'ADJUSTMENT') AND sm.quantity > 0 THEN sm.quantity
          WHEN sm.movement_type = 'ADJUSTMENT' AND sm.quantity < 0 THEN sm.quantity
          ELSE -sm.quantity
        END
      ) OVER (
        PARTITION BY sm.product_id, sm.warehouse_id 
        ORDER BY sm.created_at ASC, sm.id ASC
        ROWS UNBOUNDED PRECEDING
      ) AS running_balance
    FROM stock_movements sm
    JOIN products p ON sm.product_id = p.id
    JOIN warehouses w ON sm.warehouse_id = w.id
    JOIN user_profiles up ON sm.created_by = up.id
    WHERE 
      (p_product_id IS NULL OR sm.product_id = p_product_id)
      AND (p_warehouse_id IS NULL OR sm.warehouse_id = p_warehouse_id)
      AND sm.created_at >= p_from_date
      AND sm.created_at <= p_to_date
  )
  SELECT * FROM movement_data
  ORDER BY created_at DESC
  LIMIT p_limit;
END;
$$ LANGUAGE plpgsql;

Optimized Reporting Queries

Daily Inventory Summary

-- Materialized view for daily inventory snapshots
CREATE MATERIALIZED VIEW daily_inventory_summary AS
SELECT 
  DATE(NOW()) AS summary_date,
  w.id AS warehouse_id,
  w.name AS warehouse_name,
  COUNT(i.product_id) AS total_products,
  COUNT(CASE WHEN i.quantity_available <= 0 THEN 1 END) AS out_of_stock_products,
  COUNT(CASE WHEN i.quantity_available <= p.min_stock_level THEN 1 END) AS low_stock_products,
  SUM(i.quantity_on_hand) AS total_quantity_on_hand,
  SUM(i.quantity_available) AS total_quantity_available,
  SUM(p.cost_price * i.quantity_on_hand) AS total_inventory_cost,
  SUM(p.selling_price * i.quantity_available) AS total_inventory_value,
  NOW() AS created_at
FROM inventory i
JOIN products p ON i.product_id = p.id
JOIN warehouses w ON i.warehouse_id = w.id
WHERE p.is_active = true AND w.is_active = true
GROUP BY w.id, w.name;

-- Index for the materialized view
CREATE INDEX idx_daily_inventory_summary_date_warehouse 
ON daily_inventory_summary(summary_date, warehouse_id);

-- Refresh function for the materialized view
CREATE OR REPLACE FUNCTION refresh_daily_inventory_summary()
RETURNS VOID AS $$
BEGIN
  REFRESH MATERIALIZED VIEW CONCURRENTLY daily_inventory_summary;
END;
$$ LANGUAGE plpgsql;

Top Products Analysis

-- Function for top products by movement volume
CREATE OR REPLACE FUNCTION get_top_products_by_movement(
  p_warehouse_id UUID DEFAULT NULL,
  p_days INTEGER DEFAULT 30,
  p_limit INTEGER DEFAULT 10
)
RETURNS TABLE (
  product_id UUID,
  product_name VARCHAR,
  sku VARCHAR,
  total_movements BIGINT,
  total_quantity_moved BIGINT,
  avg_movement_size NUMERIC,
  movement_frequency NUMERIC
) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    p.id,
    p.name,
    p.sku,
    COUNT(sm.id) AS total_movements,
    SUM(ABS(sm.quantity)) AS total_quantity_moved,
    AVG(ABS(sm.quantity)) AS avg_movement_size,
    COUNT(sm.id)::NUMERIC / p_days AS movement_frequency
  FROM products p
  JOIN stock_movements sm ON p.id = sm.product_id
  WHERE 
    (p_warehouse_id IS NULL OR sm.warehouse_id = p_warehouse_id)
    AND sm.created_at >= NOW() - (p_days || ' days')::INTERVAL
    AND p.is_active = true
  GROUP BY p.id, p.name, p.sku
  ORDER BY total_quantity_moved DESC
  LIMIT p_limit;
END;
$$ LANGUAGE plpgsql;

Connection Pooling & Resource Management

Connection Pool Configuration

-- Recommended connection pool settings for Supabase
-- These would be configured in the Supabase dashboard or connection string

-- Connection Pool Settings:
-- max_connections: 100 (production)
-- pool_size: 15 (per service)
-- pool_timeout: 10 seconds
-- pool_recycle: 3600 seconds (1 hour)
-- pool_pre_ping: true

Query Timeout and Resource Limits

-- Set statement timeout for long-running queries
SET statement_timeout = '30s';

-- Set lock timeout to prevent deadlocks
SET lock_timeout = '10s';

-- Limit work memory for sorting operations
SET work_mem = '4MB';

-- Function to set session-specific timeouts
CREATE OR REPLACE FUNCTION set_query_timeout(timeout_seconds INTEGER)
RETURNS VOID AS $$
BEGIN
  EXECUTE format('SET statement_timeout = %L', timeout_seconds * 1000);
END;
$$ LANGUAGE plpgsql;

Performance Monitoring

Query Performance Tracking

-- Enable pg_stat_statements extension for query performance tracking
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- View for analyzing slow queries
CREATE OR REPLACE VIEW slow_queries AS
SELECT 
  query,
  calls,
  total_time,
  total_time / calls AS avg_time_ms,
  rows,
  100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
WHERE calls > 10
ORDER BY total_time DESC
LIMIT 20;

-- Function to analyze table performance
CREATE OR REPLACE FUNCTION analyze_table_performance(table_name TEXT)
RETURNS TABLE (
  schemaname NAME,
  tablename NAME,
  seq_scan BIGINT,
  seq_tup_read BIGINT,
  idx_scan BIGINT,
  idx_tup_fetch BIGINT,
  n_tup_ins BIGINT,
  n_tup_upd BIGINT,
  n_tup_del BIGINT
) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    st.schemaname,
    st.relname,
    st.seq_scan,
    st.seq_tup_read,
    st.idx_scan,
    st.idx_tup_fetch,
    st.n_tup_ins,
    st.n_tup_upd,
    st.n_tup_del
  FROM pg_stat_user_tables st
  WHERE st.relname = table_name;
END;
$$ LANGUAGE plpgsql;

Index Usage Analysis

-- View to analyze index usage
CREATE OR REPLACE VIEW index_usage_stats AS
SELECT 
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Function to identify unused indexes
CREATE OR REPLACE FUNCTION find_unused_indexes()
RETURNS TABLE (
  schema_name NAME,
  table_name NAME,
  index_name NAME,
  index_size TEXT
) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid))
  FROM pg_stat_user_indexes
  WHERE idx_scan = 0
    AND schemaname = 'public'
  ORDER BY pg_relation_size(indexrelid) DESC;
END;
$$ LANGUAGE plpgsql;

Caching Strategies

Application-Level Caching

-- Function to cache frequently accessed data
CREATE OR REPLACE FUNCTION get_cached_inventory_summary(
  p_warehouse_id UUID,
  p_cache_duration INTERVAL DEFAULT '5 minutes'
)
RETURNS TABLE (
  product_count BIGINT,
  total_value NUMERIC,
  low_stock_count BIGINT
) AS $$
DECLARE
  cache_key TEXT;
  cached_result RECORD;
  cache_expiry TIMESTAMPTZ;
BEGIN
  cache_key := 'inventory_summary_' || p_warehouse_id::TEXT;
  
  -- Check if cached result exists and is valid
  SELECT data, expires_at INTO cached_result, cache_expiry
  FROM cache_table 
  WHERE key = cache_key AND expires_at > NOW();
  
  IF FOUND THEN
    RETURN QUERY SELECT 
      (cached_result.data->>'product_count')::BIGINT,
      (cached_result.data->>'total_value')::NUMERIC,
      (cached_result.data->>'low_stock_count')::BIGINT;
  ELSE
    -- Generate new result and cache it
    RETURN QUERY
    WITH summary AS (
      SELECT 
        COUNT(*) AS product_count,
        SUM(p.selling_price * i.quantity_available) AS total_value,
        COUNT(CASE WHEN i.quantity_available <= p.min_stock_level THEN 1 END) AS low_stock_count
      FROM inventory i
      JOIN products p ON i.product_id = p.id
      WHERE i.warehouse_id = p_warehouse_id
        AND p.is_active = true
    )
    SELECT * FROM summary;
    
    -- Cache the result
    INSERT INTO cache_table (key, data, expires_at)
    VALUES (
      cache_key,
      json_build_object(
        'product_count', (SELECT COUNT(*) FROM inventory i JOIN products p ON i.product_id = p.id WHERE i.warehouse_id = p_warehouse_id AND p.is_active = true),
        'total_value', (SELECT SUM(p.selling_price * i.quantity_available) FROM inventory i JOIN products p ON i.product_id = p.id WHERE i.warehouse_id = p_warehouse_id AND p.is_active = true),
        'low_stock_count', (SELECT COUNT(CASE WHEN i.quantity_available <= p.min_stock_level THEN 1 END) FROM inventory i JOIN products p ON i.product_id = p.id WHERE i.warehouse_id = p_warehouse_id AND p.is_active = true)
      ),
      NOW() + p_cache_duration
    )
    ON CONFLICT (key) DO UPDATE SET
      data = EXCLUDED.data,
      expires_at = EXCLUDED.expires_at;
  END IF;
END;
$$ LANGUAGE plpgsql;

Cache Management

-- Cache table for storing computed results
CREATE TABLE IF NOT EXISTS cache_table (
  key VARCHAR(200) PRIMARY KEY,
  data JSONB NOT NULL,
  expires_at TIMESTAMPTZ NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index for cache expiry cleanup
CREATE INDEX idx_cache_expires_at ON cache_table(expires_at);

-- Function to clean expired cache entries
CREATE OR REPLACE FUNCTION cleanup_expired_cache()
RETURNS INTEGER AS $$
DECLARE
  deleted_count INTEGER;
BEGIN
  DELETE FROM cache_table WHERE expires_at < NOW();
  GET DIAGNOSTICS deleted_count = ROW_COUNT;
  RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

-- Schedule cache cleanup (would be called by a cron job)
CREATE OR REPLACE FUNCTION schedule_cache_cleanup()
RETURNS VOID AS $$
BEGIN
  PERFORM cleanup_expired_cache();
END;
$$ LANGUAGE plpgsql;

Maintenance and Optimization

Database Maintenance Tasks

-- Function to analyze and vacuum tables
CREATE OR REPLACE FUNCTION maintain_database()
RETURNS VOID AS $$
BEGIN
  -- Analyze all tables to update statistics
  ANALYZE;
  
  -- Vacuum tables to reclaim space
  VACUUM (ANALYZE);
  
  -- Reindex if needed (be careful in production)
  -- REINDEX DATABASE current_database();
END;
$$ LANGUAGE plpgsql;

-- Function to update table statistics
CREATE OR REPLACE FUNCTION update_table_statistics()
RETURNS VOID AS $$
BEGIN
  -- Update statistics for frequently queried tables
  ANALYZE products;
  ANALYZE inventory;
  ANALYZE stock_movements;
  ANALYZE user_profiles;
END;
$$ LANGUAGE plpgsql;

This comprehensive performance optimization strategy ensures Smart Shelf's database operates efficiently at scale while maintaining data integrity and query responsiveness.

Security & Access Control

Migrations & Versioning