Database Performance Optimization: Indexes, Query Optimization & Caching Strategies

This article is a comprehensive introduction to database performance optimization – including indexes, query optimization and caching strategies with practical examples.

In a Nutshell

Database performance optimization is the process of improving the speed and efficiency of database operations through strategic indexing, query optimization and caching techniques.

Compact Technical Description

Database Performance Optimization comprises techniques for accelerating database queries and operations through structural improvements and strategic caching implementation.

Core Components:

Indexes

B-Tree Indexes: Standard for equality and range queries
Hash Indexes: Optimized for exact matches
Composite Indexes: Multi-column indexes for complex queries
Partial Indexes: Conditional indexes for data subsets
Covering Indexes: Indexes containing all required columns

Query Optimization

Execution Plans: Analysis of query execution plans
Query Hints: Manual optimization hints
Statistics: Current statistics for optimal planning
Partitioning: Data distribution for better performance
Materialized Views: Pre-calculated query results

Caching Strategies

Query Cache: Caching of query results
Application Cache: Application-side caching
Distributed Cache: Caching across multiple servers
Cache Invalidation: Strategies for cache updates
Redis/Memcached: Specialized caching systems

Exam-Relevant Key Points

Indexes: Data structures for accelerating data searches
Query Optimization: Improving SQL query performance
Execution Plan: Execution plan of an SQL query
Caching: Temporary storage of frequently used data
Database Statistics: Statistical information for optimizer
Partitioning: Division of large tables into smaller units
Materialized Views: Pre-calculated views with stored results
Performance Monitoring: Database performance monitoring
IHK-relevant: Database administration and performance tuning

Core Components

Index Management: Creation and maintenance of database indexes
Query Analysis: Analysis and optimization of SQL queries
Caching Layer: Implementation of caching strategies
Performance Monitoring: Continuous monitoring and analysis
Database Configuration: Optimization of database configuration
Hardware Optimization: Hardware-based performance improvements
Connection Pooling: Efficient management of database connections
Backup & Recovery: Fast recovery without performance loss

Practical Examples

1. Indexes and Query Optimization with SQL

-- Beispiel für eine E-Commerce Datenbank
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    phone VARCHAR(20),
    birth_date DATE,
    gender ENUM('M', 'F', 'O'),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    last_login TIMESTAMP,
    registration_ip VARCHAR(45),
    INDEX idx_email (email),
    INDEX idx_username (username),
    INDEX idx_created_at (created_at),
    INDEX idx_active_created (is_active, created_at),
    INDEX idx_name_search (first_name, last_name)
);

CREATE TABLE products (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    category_id BIGINT NOT NULL,
    brand_id BIGINT,
    sku VARCHAR(100) UNIQUE,
    stock_quantity INT DEFAULT 0,
    min_stock_level INT DEFAULT 5,
    weight DECIMAL(8, 3),
    dimensions VARCHAR(50),
    color VARCHAR(30),
    size VARCHAR(20),
    material VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id),
    FOREIGN KEY (brand_id) REFERENCES brands(id),
    INDEX idx_category_active (category_id, is_active),
    INDEX idx_price (price),
    INDEX idx_brand (brand_id),
    INDEX idx_name (name),
    INDEX idx_sku (sku),
    INDEX idx_created_at (created_at),
    FULLTEXT idx_search (name, description)
);

CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    total_amount DECIMAL(12, 2) NOT NULL,
    tax_amount DECIMAL(10, 2) DEFAULT 0,
    shipping_amount DECIMAL(8, 2) DEFAULT 0,
    discount_amount DECIMAL(10, 2) DEFAULT 0,
    currency VARCHAR(3) DEFAULT 'EUR',
    payment_method VARCHAR(50),
    payment_status ENUM('pending', 'paid', 'failed', 'refunded') DEFAULT 'pending',
    shipping_address JSON,
    billing_address JSON,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    shipped_at TIMESTAMP,
    delivered_at TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_user_status (user_id, status),
    INDEX idx_order_number (order_number),
    INDEX idx_created_at (created_at),
    INDEX idx_status_created (status, created_at),
    INDEX idx_total_amount (total_amount)
);

CREATE TABLE order_items (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    total_price DECIMAL(12, 2) NOT NULL,
    product_snapshot JSON, -- Produktinformationen zum Zeitpunkt der Bestellung
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id),
    INDEX idx_order_id (order_id),
    INDEX idx_product_id (product_id),
    INDEX idx_order_product (order_id, product_id)
);

-- Optimale Indizes für häufige Abfragen

-- Composite Index für Benutzer-Suche mit Filtern
CREATE INDEX idx_users_search ON users(is_active, created_at DESC, username);

-- Partial Index nur für aktive Produkte
CREATE INDEX idx_products_active ON products(category_id, price) WHERE is_active = TRUE;

-- Covering Index für Bestellübersicht
CREATE INDEX idx_orders_covering ON orders(user_id, status, created_at, total_amount, order_number);

-- Full-Text Index für Produktsuche
CREATE FULLTEXT INDEX idx_products_fulltext ON products(name, description);

-- Partitionierung für große Tabellen (Bestellungen nach Datum)
-- ALTER TABLE orders PARTITION BY RANGE (YEAR(created_at)) (
--     PARTITION p2022 VALUES LESS THAN (2023),
--     PARTITION p2023 VALUES LESS THAN (2024),
--     PARTITION p2024 VALUES LESS THAN (2025),
--     PARTITION p2025 VALUES LESS THAN (2026),
--     PARTITION pmax VALUES LESS THAN MAXVALUE
-- );

-- Materialized View für Umsatzstatistiken
CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT 
    DATE(created_at) as sale_date,
    COUNT(*) as order_count,
    SUM(total_amount) as total_sales,
    AVG(total_amount) as avg_order_value,
    COUNT(DISTINCT user_id) as unique_customers
FROM orders 
WHERE status IN ('delivered', 'shipped')
GROUP BY DATE(created_at);

-- Trigger für automatische Statistik-Aktualisierung
CREATE OR REPLACE FUNCTION update_order_stats()
RETURNS TRIGGER AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_order_stats
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH STATEMENT
    EXECUTE FUNCTION update_order_stats();

-- Optimierte Abfragen mit EXPLAIN ANALYZE

-- Abfrage 1: Bestellungen eines Benutzers mit Pagination
EXPLAIN ANALYZE
SELECT 
    o.id,
    o.order_number,
    o.status,
    o.total_amount,
    o.created_at,
    COUNT(oi.id) as item_count
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE o.user_id = 12345
    AND o.created_at >= '2024-01-01'
GROUP BY o.id, o.order_number, o.status, o.total_amount, o.created_at
ORDER BY o.created_at DESC
LIMIT 20 OFFSET 0;

-- Abfrage 2: Produktsuche mit Filtern und Sortierung
EXPLAIN ANALYZE
SELECT 
    p.id,
    p.name,
    p.price,
    p.stock_quantity,
    c.name as category_name,
    b.name as brand_name,
    MATCH(p.name, p.description) AGAINST('laptop computer' IN NATURAL LANGUAGE MODE) as relevance_score
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN brands b ON p.brand_id = b.id
WHERE p.is_active = TRUE
    AND p.stock_quantity > 0
    AND p.price BETWEEN 500 AND 2000
    AND MATCH(p.name, p.description) AGAINST('laptop computer' IN NATURAL LANGUAGE MODE)
ORDER BY relevance_score DESC, p.price ASC
LIMIT 50;

-- Abfrage 3: Umsatzstatistiken mit Zeitraumfilter
EXPLAIN ANALYZE
SELECT 
    DATE_TRUNC('month', created_at) as month,
    COUNT(*) as order_count,
    SUM(total_amount) as total_sales,
    AVG(total_amount) as avg_order_value,
    COUNT(DISTINCT user_id) as unique_customers
FROM orders
WHERE status IN ('delivered', 'shipped')
    AND created_at BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

-- Abfrage 4: Top-Produkte nach Umsatz
EXPLAIN ANALYZE
SELECT 
    p.id,
    p.name,
    p.category_id,
    SUM(oi.quantity) as total_sold,
    SUM(oi.total_price) as total_revenue,
    COUNT(DISTINCT oi.order_id) as order_count
FROM products p
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
WHERE o.status IN ('delivered', 'shipped')
    AND o.created_at >= '2024-01-01'
GROUP BY p.id, p.name, p.category_id
ORDER BY total_revenue DESC
LIMIT 100;

-- Abfrage 5: Benutzer-Activity-Report
EXPLAIN ANALYZE
SELECT 
    u.id,
    u.username,
    u.email,
    COUNT(o.id) as total_orders,
    SUM(o.total_amount) as total_spent,
    AVG(o.total_amount) as avg_order_value,
    MAX(o.created_at) as last_order_date,
    u.last_login
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.is_active = TRUE
    AND u.created_at >= '2023-01-01'
GROUP BY u.id, u.username, u.email, u.last_login
HAVING COUNT(o.id) > 0
ORDER BY total_spent DESC
LIMIT 1000;

-- Performance Monitoring Abfragen

-- Langsame Abfragen identifizieren
SELECT 
    query,
    calls,
    total_time,
    mean_time,
    rows
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- Indizes-Nutzung analysieren
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Tabellengrößen und Wachstum überwachen
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 size_bytes DESC;

-- Dead Locks überwachen
SELECT 
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- Datenbank-Konfigurationsoptimierung

-- PostgreSQL Konfiguration für hohe Performance
-- postgresql.conf

# Memory Settings
shared_buffers = 256MB                    # 25% of RAM
effective_cache_size = 1GB                 # 75% of RAM
work_mem = 4MB                           # Per sort operation
maintenance_work_mem = 64MB              # For VACUUM/CREATE INDEX
checkpoint_completion_target = 0.9        # Checkpoint tuning
wal_buffers = 16MB                       # WAL buffers
default_statistics_target = 100           # Statistics accuracy

# Connection Settings
max_connections = 200                     # Maximum connections
shared_preload_libraries = 'pg_stat_statements'  # Performance monitoring

# Query Planning
random_page_cost = 1.1                    # SSD optimization
effective_io_concurrency = 200             # SSD concurrent I/O
cpu_tuple_cost = 0.01                     # CPU cost estimation
cpu_operator_cost = 0.000125              # Operator cost
cpu_index_tuple_cost = 0.005              # Index access cost

# WAL Settings
wal_level = replica
max_wal_size = 1GB
min_wal_size = 80MB
checkpoint_timeout = 5min
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

# Autovacuum Settings
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1

2. Cache Implementation with Python and Redis

# cache_manager.py
import redis
import json
import pickle
import hashlib
from typing import Any, Optional, Union, Callable
from functools import wraps
from datetime import timedelta
import logging

logger = logging.getLogger(__name__)

class CacheManager:
    """
    Advanced caching manager with Redis backend
    """
    
    def __init__(
        self,
        host: str = 'localhost',
        port: int = 6379,
        db: int = 0,
        password: Optional[str] = None,
        default_timeout: int = 3600,
        key_prefix: str = 'app:',
        serializer: str = 'json'
    ):
        """
        Initialize cache manager
        
        Args:
            host: Redis host
            port: Redis port
            db: Redis database number
            password: Redis password
            default_timeout: Default cache timeout in seconds
            key_prefix: Key prefix for all cache keys
            serializer: Serialization method ('json' or 'pickle')
        """
        self.redis_client = redis.Redis(
            host=host,
            port=port,
            db=db,
            password=password,
            decode_responses=False,
            socket_connect_timeout=5,
            socket_timeout=5,
            retry_on_timeout=True
        )
        
        self.default_timeout = default_timeout
        self.key_prefix = key_prefix
        self.serializer = serializer
        
        # Test connection
        try:
            self.redis_client.ping()
            logger.info("Redis connection established")
        except redis.ConnectionError as e:
            logger.error(f"Redis connection failed: {e}")
            raise
    
    def _make_key(self, key: str) -> str:
        """Create full cache key with prefix"""
        return f"{self.key_prefix}{key}"
    
    def _serialize(self, value: Any) -> bytes:
        """Serialize value for storage"""
        if self.serializer == 'json':
            return json.dumps(value, default=str).encode('utf-8')
        elif self.serializer == 'pickle':
            return pickle.dumps(value)
        else:
            raise ValueError(f"Unsupported serializer: {self.serializer}")
    
    def _deserialize(self, value: bytes) -> Any:
        """Deserialize value from storage"""
        if self.serializer == 'json':
            return json.loads(value.decode('utf-8'))
        elif self.serializer == 'pickle':
            return pickle.loads(value)
        else:
            raise ValueError(f"Unsupported serializer: {self.serializer}")
    
    def set(
        self,
        key: str,
        value: Any,
        timeout: Optional[int] = None,
        nx: bool = False,
        xx: bool = False
    ) -> bool:
        """
        Set cache value
        
        Args:
            key: Cache key
            value: Value to cache
            timeout: Timeout in seconds
            nx: Only set if key doesn't exist
            xx: Only set if key exists
        
        Returns:
            True if value was set
        """
        try:
            full_key = self._make_key(key)
            serialized_value = self._serialize(value)
            expire_time = timeout or self.default_timeout
            
            result = self.redis_client.set(
                full_key,
                serialized_value,
                ex=expire_time,
                nx=nx,
                xx=xx
            )
            
            if result:
                logger.debug(f"Cache set: {key}")
            
            return result
            
        except Exception as e:
            logger.error(f"Cache set error for key {key}: {e}")
            return False
    
    def get(self, key: str, default: Any = None) -> Any:
        """
        Get cache value
        
        Args:
            key: Cache key
            default: Default value if key not found
        
        Returns:
            Cached value or default
        """
        try:
            full_key = self._make_key(key)
            value = self.redis_client.get(full_key)
            
            if value is None:
                logger.debug(f"Cache miss: {key}")
                return default
            
            result = self._deserialize(value)
            logger.debug(f"Cache hit: {key}")
            return result
            
        except Exception as e:
            logger.error(f"Cache get error for key {key}: {e}")
            return default
    
    def delete(self, key: str) -> bool:
        """
        Delete cache value
        
        Args:
            key: Cache key
        
        Returns:
            True if key was deleted
        """
        try:
            full_key = self._make_key(key)
            result = self.redis_client.delete(full_key)
            
            if result:
                logger.debug(f"Cache deleted: {key}")
            
            return bool(result)
            
        except Exception as e:
            logger.error(f"Cache delete error for key {key}: {e}")
            return False
    
    def exists(self, key: str) -> bool:
        """
        Check if key exists in cache
        
        Args:
            key: Cache key
        
        Returns:
            True if key exists
        """
        try:
            full_key = self._make_key(key)
            return bool(self.redis_client.exists(full_key))
        except Exception as e:
            logger.error(f"Cache exists error for key {key}: {e}")
            return False
    
    def expire(self, key: str, timeout: int) -> bool:
        """
        Set expiration for existing key
        
        Args:
            key: Cache key
            timeout: Timeout in seconds
        
        Returns:
            True if expiration was set
        """
        try:
            full_key = self._make_key(key)
            result = self.redis_client.expire(full_key, timeout)
            return bool(result)
        except Exception as e:
            logger.error(f"Cache expire error for key {key}: {e}")
            return False
    
    def ttl(self, key: str) -> int:
        """
        Get time to live for key
        
        Args:
            key: Cache key
        
        Returns:
            TTL in seconds, -1 if no expiration, -2 if key doesn't exist
        """
        try:
            full_key = self._make_key(key)
            return self.redis_client.ttl(full_key)
        except Exception as e:
            logger.error(f"Cache TTL error for key {key}: {e}")
            return -2
    
    def increment(self, key: str, amount: int = 1) -> Optional[int]:
        """
        Increment numeric value
        
        Args:
            key: Cache key
            amount: Increment amount
        
        Returns:
            New value or None if error
        """
        try:
            full_key = self._make_key(key)
            return self.redis_client.incr(full_key, amount)
        except Exception as e:
            logger.error(f"Cache increment error for key {key}: {e}")
            return None
    
    def get_many(self, keys: list[str]) -> dict[str, Any]:
        """
        Get multiple values
        
        Args:
            keys: List of cache keys
        
        Returns:
            Dictionary of key-value pairs
        """
        try:
            full_keys = [self._make_key(key) for key in keys]
            values = self.redis_client.mget(full_keys)
            
            result = {}
            for i, key in enumerate(keys):
                if values[i] is not None:
                    result[key] = self._deserialize(values[i])
            
            return result
            
        except Exception as e:
            logger.error(f"Cache get_many error: {e}")
            return {}
    
    def set_many(self, mapping: dict[str, Any], timeout: Optional[int] = None) -> bool:
        """
        Set multiple values
        
        Args:
            mapping: Dictionary of key-value pairs
            timeout: Timeout in seconds
        
        Returns:
            True if all values were set
        """
        try:
            expire_time = timeout or self.default_timeout
            pipe = self.redis_client.pipeline()
            
            for key, value in mapping.items():
                full_key = self._make_key(key)
                serialized_value = self._serialize(value)
                pipe.setex(full_key, expire_time, serialized_value)
            
            pipe.execute()
            return True
            
        except Exception as e:
            logger.error(f"Cache set_many error: {e}")
            return False
    
    def clear_pattern(self, pattern: str) -> int:
        """
        Clear keys matching pattern
        
        Args:
            pattern: Pattern to match (e.g., "user:*")
        
        Returns:
            Number of keys deleted
        """
        try:
            full_pattern = self._make_key(pattern)
            keys = self.redis_client.keys(full_pattern)
            
            if keys:
                result = self.redis_client.delete(*keys)
                logger.info(f"Cleared {result} keys matching pattern: {pattern}")
                return result
            
            return 0
            
        except Exception as e:
            logger.error(f"Cache clear_pattern error: {e}")
            return 0
    
    def get_cache_info(self) -> dict[str, Any]:
        """
        Get cache statistics
        
        Returns:
            Cache information dictionary
        """
        try:
            info = self.redis_client.info()
            return {
                'used_memory': info.get('used_memory', 0),
                'used_memory_human': info.get('used_memory_human', '0B'),
                'connected_clients': info.get('connected_clients', 0),
                'total_commands_processed': info.get('total_commands_processed', 0),
                'keyspace_hits': info.get('keyspace_hits', 0),
                'keyspace_misses': info.get('keyspace_misses', 0),
                'hit_rate': info.get('keyspace_hits', 0) / max(1, info.get('keyspace_hits', 0) + info.get('keyspace_misses', 0))
            }
        except Exception as e:
            logger.error(f"Cache info error: {e}")
            return {}

# Decorators for easy caching
def cache_result(
    timeout: int = 3600,
    key_func: Optional[Callable] = None,
    unless: Optional[Callable] = None
):
    """
    Decorator for caching function results
    
    Args:
        timeout: Cache timeout in seconds
        key_func: Function to generate cache key
        unless: Function to determine if caching should be skipped
    """
    def decorator(func):
        @wraps(func)
        def wrapper(*args, **kwargs):
            # Generate cache key
            if key_func:
                cache_key = key_func(*args, **kwargs)
            else:
                # Default key generation
                key_parts = [func.__name__]
                key_parts.extend(str(arg) for arg in args)
                key_parts.extend(f"{k}={v}" for k, v in sorted(kwargs.items()))
                cache_key = hashlib.md5('|'.join(key_parts).encode()).hexdigest()
            
            # Check if caching should be skipped
            if unless and unless(*args, **kwargs):
                return func(*args, **kwargs)
            
            # Try to get from cache
            cached_result = cache.get(cache_key)
            if cached_result is not None:
                return cached_result
            
            # Execute function and cache result
            result = func(*args, **kwargs)
            cache.set(cache_key, result, timeout)
            
            return result
        
        return wrapper
    return decorator

def cache_user_data(timeout: int = 1800):
    """Decorator for user-specific data caching"""
    def decorator(func):
        @wraps(func)
        def wrapper(self, user_id: int, *args, **kwargs):
            cache_key = f"user:{user_id}:{func.__name__}:{hashlib.md5(str(args).encode()).hexdigest()}"
            
            cached_result = cache.get(cache_key)
            if cached_result is not None:
                return cached_result
            
            result = func(self, user_id, *args, **kwargs)
            cache.set(cache_key, result, timeout)
            
            return result
        
        return wrapper
    return decorator

def cache_query_result(timeout: int = 600):
    """Decorator for database query result caching"""
    def decorator(func):
        @wraps(func)
        def wrapper(query: str, params: tuple = (), *args, **kwargs):
            # Generate cache key from query and parameters
            query_hash = hashlib.md5((query + str(params)).encode()).hexdigest()
            cache_key = f"query:{query_hash}"
            
            cached_result = cache.get(cache_key)
            if cached_result is not None:
                return cached_result
            
            result = func(query, params, *args, **kwargs)
            cache.set(cache_key, result, timeout)
            
            return result
        
        return wrapper
    return decorator

# Initialize global cache instance
cache = CacheManager(
    host='localhost',
    port=6379,
    db=0,
    default_timeout=3600,
    key_prefix='myapp:',
    serializer='json'
)

# Usage examples
class UserService:
    """Example service with caching"""
    
    @cache_user_data(timeout=1800)  # 30 minutes
    def get_user_profile(self, user_id: int) -> dict:
        """Get user profile with caching"""
        # Simulate database query
        return {
            'id': user_id,
            'username': f'user_{user_id}',
            'email': f'user_{user_id}@example.com',
            'profile_data': {'age': 25, 'city': 'New York'}
        }
    
    @cache_result(timeout=300)  # 5 minutes
    def get_user_orders(self, user_id: int, status: str = 'all') -> list:
        """Get user orders with caching"""
        # Simulate database query
        return [
            {'id': 1, 'user_id': user_id, 'status': status, 'total': 100.50},
            {'id': 2, 'user_id': user_id, 'status': status, 'total': 75.25}
        ]
    
    def update_user_profile(self, user_id: int, profile_data: dict) -> bool:
        """Update user profile and invalidate cache"""
        # Update database (simulated)
        
        # Invalidate relevant cache keys
        cache.clear_pattern(f"user:{user_id}:*")
        
        return True

class ProductService:
    """Example product service with advanced caching"""
    
    @cache_result(timeout=600)  # 10 minutes
    def get_product_details(self, product_id: int) -> dict:
        """Get product details with caching"""
        return {
            'id': product_id,
            'name': f'Product {product_id}',
            'price': 99.99,
            'description': 'Product description',
            'stock': 100
        }
    
    def get_product_list(
        self,
        category_id: Optional[int] = None,
        min_price: Optional[float] = None,
        max_price: Optional[float] = None,
        page: int = 1,
        per_page: int = 20
    ) -> dict:
        """Get product list with smart caching"""
        # Generate cache key based on all parameters
        cache_key_parts = [
            'product_list',
            f'cat:{category_id or "all"}',
            f'price:{min_price or "min"}-{max_price or "max"}',
            f'page:{page}',
            f'per_page:{per_page}'
        ]
        cache_key = ':'.join(cache_key_parts)
        
        cached_result = cache.get(cache_key)
        if cached_result is not None:
            return cached_result
        
        # Simulate database query
        products = [
            {'id': i, 'name': f'Product {i}', 'price': 50 + i}
            for i in range((page - 1) * per_page + 1, page * per_page + 1)
        ]
        
        result = {
            'products': products,
            'total': 1000,
            'page': page,
            'per_page': per_page,
            'total_pages': 50
        }
        
        cache.set(cache_key, result, timeout=300)  # 5 minutes
        return result
    
    def update_product_price(self, product_id: int, new_price: float) -> bool:
        """Update product price and invalidate relevant caches"""
        # Update database (simulated)
        
        # Invalidate product details cache
        cache.delete(f"product:{product_id}")
        
        # Invalidate product list caches
        cache.clear_pattern("product_list:*")
        
        return True

# Cache warming and preloading
def warm_cache():
    """Warm up cache with frequently accessed data"""
    logger.info("Starting cache warming...")
    
    # Preload popular products
    popular_products = [1, 2, 3, 4, 5]
    service = ProductService()
    
    for product_id in popular_products:
        service.get_product_details(product_id)
    
    # Preload user data for active users
    active_users = [100, 101, 102]
    user_service = UserService()
    
    for user_id in active_users:
        user_service.get_user_profile(user_id)
        user_service.get_user_orders(user_id)
    
    logger.info("Cache warming completed")

# Cache monitoring and maintenance
def monitor_cache_performance():
    """Monitor cache performance and health"""
    cache_info = cache.get_cache_info()
    
    logger.info(f"Cache Performance Metrics:")
    logger.info(f"  Used Memory: {cache_info['used_memory_human']}")
    logger.info(f"  Connected Clients: {cache_info['connected_clients']}")
    logger.info(f"  Hit Rate: {cache_info['hit_rate']:.2%}")
    logger.info(f"  Keyspace Hits: {cache_info['keyspace_hits']}")
    logger.info(f"  Keyspace Misses: {cache_info['keyspace_misses']}")
    
    # Alert if hit rate is low
    if cache_info['hit_rate'] < 0.8:
        logger.warning("Cache hit rate is below 80%")

def cleanup_expired_cache():
    """Clean up expired cache entries and optimize cache"""
    logger.info("Starting cache cleanup...")
    
    # Redis automatically handles expired keys
    # But we can manually clean up patterns if needed
    
    # Example: Clean up session caches older than 24 hours
    # This would require custom implementation with timestamps
    
    logger.info("Cache cleanup completed")

# Distributed cache implementation for multiple Redis instances
class DistributedCache:
    """Distributed cache with consistent hashing"""
    
    def __init__(self, redis_nodes: list[dict]):
        """
        Initialize distributed cache
        
        Args:
            redis_nodes: List of Redis node configurations
        """
        self.nodes = []
        for node_config in redis_nodes:
            client = redis.Redis(**node_config)
            self.nodes.append(client)
        
        # Simple round-robin for now (in production, use consistent hashing)
        self.current_node = 0
    
    def _get_node(self, key: str) -> redis.Redis:
        """Get Redis node for key (simple round-robin)"""
        node = self.nodes[self.current_node]
        self.current_node = (self.current_node + 1) % len(self.nodes)
        return node
    
    def set(self, key: str, value: Any, timeout: int = 3600) -> bool:
        """Set value in distributed cache"""
        node = self._get_node(key)
        try:
            serialized_value = json.dumps(value, default=str).encode('utf-8')
            return node.setex(key, timeout, serialized_value)
        except Exception as e:
            logger.error(f"Distributed cache set error: {e}")
            return False
    
    def get(self, key: str, default: Any = None) -> Any:
        """Get value from distributed cache"""
        node = self._get_node(key)
        try:
            value = node.get(key)
            if value is None:
                return default
            return json.loads(value.decode('utf-8'))
        except Exception as e:
            logger.error(f"Distributed cache get error: {e}")
            return default

# Example usage
if __name__ == "__main__":
    # Initialize cache
    cache = CacheManager()
    
    # Test basic operations
    cache.set("test_key", {"data": "test_value"}, timeout=60)
    result = cache.get("test_key")
    print(f"Cached result: {result}")
    
    # Test decorator
    @cache_result(timeout=300)
    def expensive_function(x: int, y: int) -> int:
        print("Computing expensive function...")
        return x * y + 100
    
    # First call - computes and caches
    result1 = expensive_function(5, 10)
    # Second call - returns from cache
    result2 = expensive_function(5, 10)
    
    print(f"Results: {result1}, {result2}")
    
    # Monitor cache
    monitor_cache_performance()

3. Performance Monitoring with Java and Micrometer

// DatabasePerformanceMonitor.java
package com.company.performance;

import io.micrometer.core.annotation.Counted;
import io.micrometer.core.annotation.Timed;
import io.micrometer.core.instrument.MeterRegistry;
import io.micrometer.core.instrument.Timer;
import io.micrometer.core.instrument.Counter;
import io.micrometer.core.instrument.Gauge;
import io.micrometer.core.instrument.Metrics;
import org.springframework.stereotype.Component;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.boot.actuate.health.Health;
import org.springframework.boot.actuate.health.HealthIndicator;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.Duration;
import java.time.Instant;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.atomic.AtomicLong;

@Component
public class DatabasePerformanceMonitor implements HealthIndicator {
    
    private final DataSource dataSource;
    private final JdbcTemplate jdbcTemplate;
    private final MeterRegistry meterRegistry;
    
    // Custom metrics
    private final Timer queryExecutionTimer;
    private final Counter slowQueryCounter;
    private final Counter connectionFailureCounter;
    private final Map<String, AtomicLong> queryCounters = new ConcurrentHashMap<>();
    private final Map<String, Timer> queryTimers = new ConcurrentHashMap<>();
    
    // Performance thresholds
    private static final Duration SLOW_QUERY_THRESHOLD = Duration.ofSeconds(1);
    private static final int MAX_CONNECTION_POOL_SIZE = 100;
    private static final double HIGH_CPU_USAGE_THRESHOLD = 0.8;
    private static final long HIGH_MEMORY_USAGE_THRESHOLD = 1024 * 1024 * 1024; // 1GB
    
    @Autowired
    public DatabasePerformanceMonitor(
            DataSource dataSource,
            JdbcTemplate jdbcTemplate,
            MeterRegistry meterRegistry) {
        this.dataSource = dataSource;
        this.jdbcTemplate = jdbcTemplate;
        this.meterRegistry = meterRegistry;
        
        // Initialize custom metrics
        this.queryExecutionTimer = Timer.builder("database.query.execution.time")
                .description("Database query execution time")
                .register(meterRegistry);
        
        this.slowQueryCounter = Counter.builder("database.query.slow")
                .description("Number of slow database queries")
                .register(meterRegistry);
        
        this.connectionFailureCounter = Counter.builder("database.connection.failures")
                .description("Number of database connection failures")
                .register(meterRegistry);
        
        // Register JVM metrics
        registerJVMMetrics();
        
        // Register database connection pool metrics
        registerConnectionPoolMetrics();
    }
    
    private void registerJVMMetrics() {
        // Memory usage
        Gauge.builder("jvm.memory.used")
                .description("JVM memory usage")
                .register(meterRegistry, this, obj -> Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory());
        
        Gauge.builder("jvm.memory.max")
                .description("JVM max memory")
                .register(meterRegistry, obj -> Runtime.getRuntime().maxMemory());
        
        // CPU usage (simplified)
        Gauge.builder("system.cpu.usage")
                .description("System CPU usage")
                .register(meterRegistry, obj -> getCPUUsage());
        
        // Thread count
        Gauge.builder("jvm.threads.live")
                .description("Live thread count")
                .register(meterRegistry, obj -> Thread.activeCount());
    }
    
    private void registerConnectionPoolMetrics() {
        try {
            Connection connection = dataSource.getConnection();
            
            // Connection pool metrics (HikariCP specific)
            Gauge.builder("database.connections.active")
                    .description("Active database connections")
                    .register(meterRegistry, obj -> getActiveConnections());
            
            Gauge.builder("database.connections.idle")
                    .description("Idle database connections")
                    .register(meterRegistry, obj -> getIdleConnections());
            
            Gauge.builder("database.connections.max")
                    .description("Maximum database connections")
                    .register(meterRegistry, obj -> MAX_CONNECTION_POOL_SIZE);
            
            connection.close();
        } catch (SQLException e) {
            connectionFailureCounter.increment();
        }
    }
    
    @Timed(value = "database.query", description = "Database query execution")
    @Counted(value = "database.query.calls", description = "Database query calls")
    public <T> T executeQuery(String query, Object[] params, ResultSetExtractor<T> extractor) {
        Instant start = Instant.now();
        String queryType = determineQueryType(query);
        
        try {
            // Increment query counter
            queryCounters.computeIfAbsent(queryType, k -> new AtomicLong(0)).increment();
            
            // Execute query
            T result = jdbcTemplate.query(query, params, extractor);
            
            // Record execution time
            Duration executionTime = Duration.between(start, Instant.now());
            queryTimers.computeIfAbsent(queryType, k -> Timer.builder("database.query.time.by.type")
                    .tag("type", queryType)
                    .register(meterRegistry))
                    .record(executionTime);
            
            queryExecutionTimer.record(executionTime);
            
            // Check for slow query
            if (executionTime.compareTo(SLOW_QUERY_THRESHOLD) > 0) {
                slowQueryCounter.increment();
                logSlowQuery(query, executionTime);
            }
            
            return result;
            
        } catch (Exception e) {
            connectionFailureCounter.increment();
            logQueryError(query, e);
            throw e;
        }
    }
    
    @Scheduled(fixedRate = 30000) // Every 30 seconds
    public void collectDatabaseMetrics() {
        try {
            // Collect database-specific metrics
            collectDatabaseSizeMetrics();
            collectIndexUsageMetrics();
            collectTableSizeMetrics();
            collectLockMetrics();
            
            // Collect system metrics
            collectSystemMetrics();
            
        } catch (Exception e) {
            connectionFailureCounter.increment();
        }
    }
    
    private void collectDatabaseSizeMetrics() {
        String query = "SELECT pg_size_pretty(pg_database_size(current_database())) as size";
        
        jdbcTemplate.query(query, (rs) -> {
            String size = rs.getString("size");
            Gauge.builder("database.size")
                    .description("Database size")
                    .register(meterRegistry, obj -> parseSizeToBytes(size));
        });
    }
    
    private void collectIndexUsageMetrics() {
        String query = """
            SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
            FROM pg_stat_user_indexes
            ORDER BY idx_scan DESC
            LIMIT 20
        """;
        
        jdbcTemplate.query(query, (rs) -> {
            String indexName = rs.getString("indexname");
            long scans = rs.getLong("idx_scan");
            long tuplesRead = rs.getLong("idx_tup_read");
            
            Gauge.builder("database.index.scans")
                    .tag("index", indexName)
                    .description("Index scan count")
                    .register(meterRegistry, obj -> scans);
            
            Gauge.builder("database.index.tuples.read")
                    .tag("index", indexName)
                    .description("Index tuples read")
                    .register(meterRegistry, obj -> tuplesRead);
        });
    }
    
    private void collectTableSizeMetrics() {
        String query = """
            SELECT schemaname, tablename, 
                   pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size,
                   n_tup_ins as inserts, n_tup_upd as updates, n_tup_del as deletes
            FROM pg_stat_user_tables
            ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
            LIMIT 20
        """;
        
        jdbcTemplate.query(query, (rs) -> {
            String tableName = rs.getString("tablename");
            String size = rs.getString("size");
            long inserts = rs.getLong("inserts");
            long updates = rs.getLong("updates");
            long deletes = rs.getLong("deletes");
            
            Gauge.builder("database.table.size")
                    .tag("table", tableName)
                    .description("Table size")
                    .register(meterRegistry, obj -> parseSizeToBytes(size));
            
            Counter.builder("database.table.inserts")
                    .tag("table", tableName)
                    .description("Table inserts")
                    .register(meterRegistry)
                    .increment(inserts);
            
            Counter.builder("database.table.updates")
                    .tag("table", tableName)
                    .description("Table updates")
                    .register(meterRegistry)
                    .increment(updates);
            
            Counter.builder("database.table.deletes")
                    .tag("table", tableName)
                    .description("Table deletes")
                    .register(meterRegistry)
                    .increment(deletes);
        });
    }
    
    private void collectLockMetrics() {
        String query = """
            SELECT mode, COUNT(*) as lock_count
            FROM pg_locks
            WHERE pid <> pg_backend_pid()
            GROUP BY mode
            ORDER BY lock_count DESC
        """;
        
        jdbcTemplate.query(query, (rs) -> {
            String mode = rs.getString("mode");
            long count = rs.getLong("lock_count");
            
            Gauge.builder("database.locks.count")
                    .tag("mode", mode)
                    .description("Database locks by mode")
                    .register(meterRegistry, obj -> count);
        });
    }
    
    private void collectSystemMetrics() {
        // CPU usage
        double cpuUsage = getCPUUsage();
        Gauge.builder("system.cpu.usage")
                .description("System CPU usage")
                .register(meterRegistry, obj -> cpuUsage);
        
        // Memory usage
        long usedMemory = Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory();
        long maxMemory = Runtime.getRuntime().maxMemory();
        
        Gauge.builder("jvm.memory.used.ratio")
                .description("JVM memory usage ratio")
                .register(meterRegistry, obj -> (double) usedMemory / maxMemory);
        
        // Disk usage (simplified)
        long freeDiskSpace = getFreeDiskSpace();
        long totalDiskSpace = getTotalDiskSpace();
        
        Gauge.builder("system.disk.free")
                .description("Free disk space")
                .register(meterRegistry, obj -> freeDiskSpace);
        
        Gauge.builder("system.disk.usage.ratio")
                .description("Disk usage ratio")
                .register(meterRegistry, obj -> (double) (totalDiskSpace - freeDiskSpace) / totalDiskSpace);
    }
    
    private String determineQueryType(String query) {
        String upperQuery = query.trim().toUpperCase();
        if (upperQuery.startsWith("SELECT")) return "SELECT";
        if (upperQuery.startsWith("INSERT")) return "INSERT";
        if (upperQuery.startsWith("UPDATE")) return "UPDATE";
        if (upperQuery.startsWith("DELETE")) return "DELETE";
        if (upperQuery.startsWith("CREATE")) return "CREATE";
        if (upperQuery.startsWith("DROP")) return "DROP";
        if (upperQuery.startsWith("ALTER")) return "ALTER";
        return "OTHER";
    }
    
    private long parseSizeToBytes(String size) {
        // Parse size string like "125 MB" to bytes
        try {
            String[] parts = size.split(" ");
            double value = Double.parseDouble(parts[0]);
            String unit = parts[1];
            
            switch (unit) {
                case "bytes": return (long) value;
                case "kB": return (long) (value * 1024);
                case "MB": return (long) (value * 1024 * 1024);
                case "GB": return (long) (value * 1024 * 1024 * 1024);
                case "TB": return (long) (value * 1024 * 1024 * 1024 * 1024);
                default: return 0;
            }
        } catch (Exception e) {
            return 0;
        }
    }
    
    private double getCPUUsage() {
        // Simplified CPU usage calculation
        // In production, use OperatingSystemMXBean or similar
        try {
            com.sun.management.OperatingSystemMXBean osBean = 
                (com.sun.management.OperatingSystemMXBean) 
                java.lang.management.ManagementFactory.getOperatingSystemMXBean();
            
            return osBean.getProcessCpuLoad();
        } catch (Exception e) {
            return 0.0;
        }
    }
    
    private long getActiveConnections() {
        // Get active connections from connection pool
        // This is HikariCP specific
        try {
            if (dataSource instanceof com.zaxxer.hikari.HikariDataSource) {
                com.zaxxer.hikari.HikariPoolMXBean poolBean = 
                    ((com.zaxxer.hikari.HikariDataSource) dataSource).getHikariPoolMXBean();
                return poolBean.getActiveConnections();
            }
        } catch (Exception e) {
            // Ignore
        }
        return 0;
    }
    
    private long getIdleConnections() {
        // Get idle connections from connection pool
        try {
            if (dataSource instanceof com.zaxxer.hikari.HikariDataSource) {
                com.zaxxer.hikari.HikariPoolMXBean poolBean = 
                    ((com.zaxxer.hikari.HikariDataSource) dataSource).getHikariPoolMXBean();
                return poolBean.getIdleConnections();
            }
        } catch (Exception e) {
            // Ignore
        }
        return 0;
    }
    
    private long getFreeDiskSpace() {
        // Get free disk space
        try {
            java.io.File path = new java.io.File(".");
            return path.getFreeSpace();
        } catch (Exception e) {
            return 0;
        }
    }
    
    private long getTotalDiskSpace() {
        // Get total disk space
        try {
            java.io.File path = new java.io.File(".");
            return path.getTotalSpace();
        } catch (Exception e) {
            return 0;
        }
    }
    
    private void logSlowQuery(String query, Duration executionTime) {
        logger.warn("Slow query detected - Execution time: {}ms, Query: {}", 
                executionTime.toMillis(), 
                query.substring(0, Math.min(200, query.length())));
    }
    
    private void logQueryError(String query, Exception e) {
        logger.error("Query failed - Query: {}, Error: {}", 
                query.substring(0, Math.min(200, query.length())), 
                e.getMessage());
    }
    
    @Override
    public Health health() {
        try {
            // Test database connection
            jdbcTemplate.query("SELECT 1", (rs) -> rs.getInt(1));
            
            // Check performance metrics
            Health.Builder builder = Health.up();
            
            // Check CPU usage
            double cpuUsage = getCPUUsage();
            if (cpuUsage > HIGH_CPU_USAGE_THRESHOLD) {
                builder.down().withDetail("cpu_usage", cpuUsage);
            }
            
            // Check memory usage
            long usedMemory = Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory();
            if (usedMemory > HIGH_MEMORY_USAGE_THRESHOLD) {
                builder.down().withDetail("memory_usage", usedMemory);
            }
            
            // Check connection pool
            int activeConnections = getActiveConnections();
            if (activeConnections >= MAX_CONNECTION_POOL_SIZE * 0.8) {
                builder.down().withDetail("active_connections", activeConnections);
            }
            
            return builder
                    .withDetail("cpu_usage", cpuUsage)
                    .withDetail("memory_usage", usedMemory)
                    .withDetail("active_connections", activeConnections)
                    .withDetail("slow_queries", slowQueryCounter.count())
                    .build();
            
        } catch (Exception e) {
            return Health.down()
                    .withDetail("error", e.getMessage())
                    .withException(e)
                    .build();
        }
    }
    
    // Performance analysis methods
    public Map<String, Object> getPerformanceReport() {
        Map<String, Object> report = new HashMap<>();
        
        // Query performance
        Map<String, Object> queryStats = new HashMap<>();
        queryCounters.forEach((type, count) -> {
            queryStats.put(type + "_count", count.get());
        });
        
        queryTimers.forEach((type, timer) -> {
            queryStats.put(type + "_avg_time", timer.mean(java.util.concurrent.TimeUnit.MILLISECONDS));
            queryStats.put(type + "_max_time", timer.max(java.util.concurrent.TimeUnit.MILLISECONDS));
        });
        
        report.put("query_statistics", queryStats);
        
        // System metrics
        Map<String, Object> systemStats = new HashMap<>();
        systemStats.put("cpu_usage", getCPUUsage());
        systemStats.put("memory_used", Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory());
        systemStats.put("memory_max", Runtime.getRuntime().maxMemory());
        systemStats.put("active_connections", getActiveConnections());
        systemStats.put("slow_queries", slowQueryCounter.count());
        
        report.put("system_statistics", systemStats);
        
        // Health status
        report.put("health_status", health().getStatus().getCode());
        
        return report;
    }
    
    public List<Map<String, Object>> getTopSlowQueries() {
        String query = """
            SELECT query, calls, total_time, mean_time, rows
            FROM pg_stat_statements
            ORDER BY mean_time DESC
            LIMIT 10
        """;
        
        return jdbcTemplate.query(query, (rs, rowNum) -> {
            Map<String, Object> queryInfo = new HashMap<>();
            queryInfo.put("query", rs.getString("query"));
            queryInfo.put("calls", rs.getInt("calls"));
            queryInfo.put("total_time", rs.getDouble("total_time"));
            queryInfo.put("mean_time", rs.getDouble("mean_time"));
            queryInfo.put("rows", rs.getLong("rows"));
            return queryInfo;
        });
    }
    
    public void optimizeDatabase() {
        logger.info("Starting database optimization...");
        
        // Update statistics
        jdbcTemplate.execute("ANALYZE;");
        
        // Reindex frequently used indexes
        jdbcTemplate.query("""
            SELECT schemaname, tablename, indexname
            FROM pg_stat_user_indexes
            WHERE idx_scan > 1000
            ORDER BY idx_scan DESC
            LIMIT 10
        """, (rs) -> {
            String schema = rs.getString("schemaname");
            String table = rs.getString("tablename");
            String index = rs.getString("indexname");
            
            try {
                jdbcTemplate.execute(String.format("REINDEX INDEX %s.%s;", schema, index));
                logger.info("Reindexed: {}.{}", schema, index);
            } catch (Exception e) {
                logger.error("Failed to reindex: {}.{} - {}", schema, index, e.getMessage());
            }
        });
        
        // Vacuum tables with high dead tuple ratio
        jdbcTemplate.query("""
            SELECT schemaname, tablename, n_dead_tup, n_live_tup
            FROM pg_stat_user_tables
            WHERE n_dead_tup > 0 AND n_live_tup > 0
            AND (CAST(n_dead_tup AS FLOAT) / (n_dead_tup + n_live_tup)) > 0.1
            ORDER BY (n_dead_tup / (n_dead_tup + n_live_tup)) DESC
            LIMIT 10
        """, (rs) -> {
            String schema = rs.getString("schemaname");
            String table = rs.getString("tablename");
            
            try {
                jdbcTemplate.execute(String.format("VACUUM ANALYZE %s.%s;", schema, table));
                logger.info("Vacuumed: {}.{}", schema, table);
            } catch (Exception e) {
                logger.error("Failed to vacuum: {}.{} - {}", schema, table, e.getMessage());
            }
        });
        
        logger.info("Database optimization completed");
    }
}

// ResultSetExtractor interface
@FunctionalInterface
interface ResultSetExtractor<T> {
    T extractData(ResultSet rs) throws SQLException;
}

// Performance monitoring configuration
// application.yml
/*
spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/myapp
    username: ${DB_USER:myapp}
    password: ${DB_PASSWORD:password}
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      idle-timeout: 300000
      max-lifetime: 1200000
      connection-timeout: 20000
      
management:
  endpoints:
    web:
      exposure:
        include: health,info,metrics,prometheus
  endpoint:
    health:
      show-details: always
  metrics:
    export:
      prometheus:
        enabled: true
        
logging:
  level:
    com.company.performance: DEBUG
    org.springframework.jdbc.core: DEBUG
*/