Optimizing Database Performance in High-Traffic Applications

As applications scale, database performance often becomes the bottleneck. In this post, I'll share proven strategies I've used to optimize databases handling millions of daily requests.

Understanding the Bottlenecks

Before optimizing, identify where your database is struggling:

• Slow queries: Queries taking >100ms
• Connection pool exhaustion: Too many concurrent connections
• Lock contention: Multiple transactions waiting on the same resources
• I/O saturation: Disk read/write limits reached

Query Optimization

1. Proper Indexing Strategy

Indexes are the most powerful optimization tool, but they must be used correctly:

-- Good: Composite index for common query pattern
CREATE INDEX idx_user_status_created 
ON users(status, created_at) 
WHERE status = 'active';

-- Bad: Index on low-cardinality column
CREATE INDEX idx_user_status ON users(status); -- Don't do this!

2. Query Pattern Analysis

Use EXPLAIN ANALYZE to understand query execution:

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.id, u.name;

Connection Pooling

HikariCP Configuration

For Java applications, HikariCP is the gold standard:

@Configuration
public class DataSourceConfig {
    
    @Bean
    public DataSource dataSource() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb");
        config.setUsername("user");
        config.setPassword("password");
        
        // Optimize pool size
        config.setMaximumPoolSize(20);
        config.setMinimumIdle(5);
        config.setConnectionTimeout(30000);
        config.setIdleTimeout(600000);
        config.setMaxLifetime(1800000);
        
        return new HikariDataSource(config);
    }
}

Caching Strategies

Multi-Level Caching

Implement a caching hierarchy:

@Service
public class ProductService {
    
    @Cacheable(value = "products", key = "#id")
    public Product getProduct(Long id) {
        // L1: Check local cache
        // L2: Check Redis
        // L3: Query database
    }
    
    @CacheEvict(value = "products", key = "#product.id")
    public void updateProduct(Product product) {
        // Update and invalidate cache
    }
}

Database Schema Optimization

1. Normalization vs Denormalization

Choose based on read/write patterns:

-- Normalized (write-heavy)
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255) UNIQUE
);

CREATE TABLE user_preferences (
    user_id INTEGER REFERENCES users(id),
    theme VARCHAR(20),
    language VARCHAR(10),
    PRIMARY KEY (user_id)
);

-- Denormalized (read-heavy)
CREATE TABLE users_denormalized (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255) UNIQUE,
    theme VARCHAR(20),
    language VARCHAR(10)
);

2. Partitioning

For large tables, use partitioning:

CREATE TABLE orders (
    id BIGSERIAL,
    user_id INTEGER,
    total DECIMAL(10,2),
    created_at TIMESTAMP
) PARTITION BY RANGE (created_at);

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

Read Replicas

Implement read replicas to distribute load:

@Configuration
public class DatabaseConfig {
    
    @Bean
    @Primary
    public DataSource writeDataSource() {
        // Master database
    }
    
    @Bean
    public DataSource readDataSource() {
        // Read replica
    }
    
    @Bean
    public DataSource routingDataSource() {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put("write", writeDataSource());
        targetDataSources.put("read", readDataSource());
        
        RoutingDataSource routingDataSource = new RoutingDataSource();
        routingDataSource.setTargetDataSources(targetDataSources);
        routingDataSource.setDefaultTargetDataSource(writeDataSource());
        
        return routingDataSource;
    }
}

Monitoring and Alerting

Key Metrics to Track

• Query latency (p50, p95, p99)
• Connection pool utilization
• Cache hit rate
• Disk I/O usage
• Lock wait time

Example: Spring Boot Actuator

@Configuration
public class MetricsConfig {
    
    @Bean
    public MeterRegistryCustomizer<MeterRegistry> metricsCommonTags() {
        return registry -> registry.config().commonTags(
            "application", "my-app",
            "environment", "production"
        );
    }
}

Conclusion

Database optimization is an ongoing process. Start with proper indexing and query optimization, then implement caching, connection pooling, and read replicas as needed. Always measure before and after changes to ensure improvements.

In my next post, I'll cover event-driven architecture and how to decouple services for better scalability.