Datenbank Performance Optimierung: Indizes, Query Optimierung & Caching Strategien

Dieser Beitrag ist eine umfassende Einführung in die Datenbank Performance Optimierung – inklusive Indizes, Query Optimierung und Caching Strategien mit praktischen Beispielen.

In a Nutshell

Datenbank Performance Optimierung ist der Prozess der Verbesserung der Geschwindigkeit und Effizienz von Datenbankoperationen durch strategische Indizierung, Query-Optimierung und Caching-Techniken.

Kompakte Fachbeschreibung

Datenbank Performance Optimierung umfasst Techniken zur Beschleunigung von Datenbankabfragen und -operationen durch strukturelle Verbesserungen und strategische Caching-Implementierung.

Kernkomponenten:

Indizes

B-Tree Indizes: Standard für Gleichheits- und Bereichsanfragen
Hash Indizes: Optimiert für exakte Übereinstimmungen
Composite Indizes: Mehrspaltige Indizes für komplexe Abfragen
Partial Indizes: Bedingte Indizes für Teilmenge der Daten
Covering Indizes: Indizes, die alle benötigten Spalten enthalten

Query Optimierung

Execution Plans: Analyse von Abfrageausführungsplänen
Query Hints: Manuelle Optimierungshinweise
Statistics: Aktuelle Statistiken für optimalen Plan
Partitioning: Datenaufteilung für bessere Performance
Materialized Views: Vorberechnete Abfrageergebnisse

Caching Strategien

Query Cache: Zwischenspeicherung von Abfrageergebnissen
Application Cache: Applikationsseitiges Caching
Distributed Cache: Verteiltes Caching über mehrere Server
Cache Invalidation: Strategien zur Cache-Aktualisierung
Redis/Memcached: Spezialisierte Caching-Systeme

Prüfungsrelevante Stichpunkte

Indizes: Datenstrukturen zur Beschleunigung von Datensuchen
Query Optimierung: Verbesserung der SQL-Abfrageperformance
Execution Plan: Ausführungsplan einer SQL-Abfrage
Caching: Zwischenspeicherung häufig genutzter Daten
Database Statistics: Statistische Informationen für Optimierer
Partitioning: Aufteilung großer Tabellen in kleinere Einheiten
Materialized Views: Vorberechnete Sichten mit gespeicherten Ergebnissen
Performance Monitoring: Überwachung der Datenbankperformance
IHK-relevant: Datenbankadministration und Performance-Tuning

Kernkomponenten

Index Management: Erstellung und Wartung von Datenbankindizes
Query Analysis: Analyse und Optimierung von SQL-Abfragen
Caching Layer: Implementierung von Caching-Strategien
Performance Monitoring: Kontinuierliche Überwachung und Analyse
Database Configuration: Optimierung der Datenbankkonfiguration
Hardware Optimization: Hardware-basierte Performance-Verbesserungen
Connection Pooling: Effizientes Management von Datenbankverbindungen
Backup & Recovery: Schnelle Wiederherstellung ohne Performance-Verlust

Praxisbeispiele

1. Indizes und Query Optimierung mit 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. Caching Implementierung mit Python und 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 mit Java und 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
*/