Database Optimization

Database Optimization

This skill covers database performance optimization including query tuning, indexing strategies, schema design, and configuration tuning for relational and NoSQL databases.

When to use this skill

Use this skill when you need to:

• Optimize slow-performing queries
• Design efficient database schemas
• Implement effective indexing strategies
• Tune database configuration parameters
• Scale database performance

Prerequisites

• Database access and query permissions
• Understanding of execution plans
• Access to monitoring tools (Query Store, pg_stat_statements)
• Knowledge of database internals

Guidelines

Query Optimization

Analyze Execution Plans

-- SQL Server
SET SHOWPLAN_XML ON;
GO
SELECT * FROM orders WHERE customer_id = 123;
GO

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM orders WHERE customer_id = 123;

-- MySQL
EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE customer_id = 123;

Common Query Anti-Patterns

• SELECT * (fetch only needed columns)
• Implicit type conversions
• Functions on indexed columns
• Missing JOIN predicates
• N+1 query problems

Optimization Techniques

• Use appropriate JOIN types
• Filter early with WHERE clauses
• Leverage covering indexes
• Batch operations when possible
• Use query hints sparingly

Indexing Strategies

Index Types

• B-Tree: Default, good for equality and range queries
• Hash: Exact match lookups
• GIN/GiST: Full-text search, JSON, arrays (PostgreSQL)
• Bitmap: Low cardinality columns
• Partial: Index subset of data
• Composite: Multi-column indexes

Index Design Principles

-- Covering index example
CREATE INDEX idx_orders_covering 
ON orders (customer_id, status, created_at)
INCLUDE (total_amount, shipping_address);

-- Partial index example  
CREATE INDEX idx_active_users
ON users (last_login)
WHERE is_active = TRUE;

Index Maintenance

• Monitor index fragmentation
• Rebuild/reorganize regularly
• Remove unused indexes
• Update statistics frequently

Schema Optimization

Normalization vs Denormalization

• Normalize for OLTP (reduce redundancy)
• Denormalize for OLAP (improve read performance)
• Use materialized views for complex aggregations

Data Types

• Use smallest appropriate data types
• Avoid nullable columns when possible
• Use ENUM/ CHECK constraints for validation
• Consider JSON for flexible schemas

Partitioning

-- PostgreSQL range partitioning
CREATE TABLE events (
    id bigint,
    event_time timestamp,
    data jsonb
) PARTITION BY RANGE (event_time);

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

Configuration Tuning

PostgreSQL Key Parameters

shared_buffers = 25% of RAM
effective_cache_size = 75% of RAM
work_mem = available RAM / max_connections / 2
maintenance_work_mem = 512MB
max_parallel_workers_per_gather = 4
random_page_cost = 1.1  # For SSD

MySQL Key Parameters

innodb_buffer_pool_size = 70-80% of RAM
innodb_log_file_size = 1-2GB
innodb_flush_log_at_trx_commit = 2  # Balance safety/performance
max_connections = 500
query_cache_type = 1  # For read-heavy workloads

Monitoring and Diagnostics

Identify Slow Queries

-- PostgreSQL pg_stat_statements
SELECT query, calls, mean_time, total_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

-- SQL Server Query Store
SELECT qst.query_text_id, qst.query_sql_text,
       qsrs.avg_duration, qsrs.count_executions
FROM sys.query_store_query_text qst
JOIN sys.query_store_runtime_stats qsrs 
    ON qst.query_text_id = qsrs.query_id
ORDER BY qsrs.avg_duration DESC;

Performance Metrics

• Buffer hit ratio (target > 99%)
• Query response times (p50, p95, p99)
• Lock wait time
• Connection pool utilization
• Disk I/O operations

Caching Strategies

Query Result Caching

• Application-level caching (Redis)
• Database query cache (MySQL)
• Materialized views
• Result set caching

Connection Pooling

• Use connection poolers (PgBouncer, HikariCP)
• Size pools appropriately
• Monitor connection leaks

Examples

See the examples/ directory for:

• query-optimization.sql - Before/after query examples
• index-strategies.sql - Various indexing patterns
• partitioning-setup.sql - Table partitioning examples
• monitoring-queries.sql - Performance monitoring scripts

References

• PostgreSQL performance tuning
• SQL Server query tuning
• MySQL optimization
• Use the Index, Luke