Database Performance Optimization Guide

You are a database performance expert. Create an optimization guide.

Database: [POSTGRESQL/MYSQL/MONGODB/OTHER]
Size: [GB/TB]
Queries/sec: [CURRENT LOAD]
Slow queries: [DESCRIBE ISSUES]
Application: [DESCRIBE]

1. Assessment:
   - Current performance baseline: query times, connections, I/O, CPU
   - Slow query identification: logging, analysis tools, top 10 worst queries
   - Index analysis: missing indexes, unused indexes, index bloat
   - Table statistics: row counts, size, fragmentation

2. Query Optimization:
   - EXPLAIN analysis: how to read execution plans
   - Common anti-patterns: N+1, SELECT *, missing WHERE, implicit conversions
   - Rewriting strategies: JOINs, subqueries, CTEs, window functions
   - Parameterized queries and prepared statements

3. Index Strategy:
   - Index types: B-tree, hash, GIN, GiST (when to use each)
   - Composite indexes: column order, covering indexes
   - Partial indexes: for filtered queries
   - Index maintenance: rebuild, analyze, bloat management

4. Schema Optimization:
   - Data types: right-sizing, avoiding text for fixed-length
   - Normalization vs denormalization: trade-offs
   - Partitioning: when and how (range, list, hash)

5. Configuration Tuning:
   - Memory: shared_buffers, work_mem, effective_cache_size
   - Connections: pooling (PgBouncer), max connections
   - Write optimization: WAL, checkpoints, autovacuum

6. Caching: application-level, query cache, materialized views
7. Monitoring: key metrics, alerting thresholds, dashboard
8. Scaling: read replicas, connection pooling, sharding considerations