database-optimizer

Start by running EXPLAIN ANALYZE to inspect the execution plan and identify costly sequential scans or nested loops. Then rewrite subqueries, reorder JOINs, and add composite or covering indexes matching the query's filter and join columns.

N+1 queries are resolved with eager loading: use select_related or prefetch_related in Django, and joinedload or selectinload in SQLAlchemy. For GraphQL APIs, apply DataLoader patterns to batch and cache field-level fetches.

Cache-aside with Redis is the most common pattern: the application checks the cache first and populates it on misses. Combine it with TTL-based invalidation and an L1 application cache for frequently accessed, rarely changing data.

Yes, it covers MongoDB aggregation pipeline optimization, compound index design, and DynamoDB GSI/LSI patterns. It also addresses cloud services like Aurora, Cosmos DB, and BigQuery with platform-specific tuning.

Denormalize when read-heavy workloads suffer from expensive multi-table JOINs and benchmarks show measurable gains. Always validate with profiling data first, since denormalization trades write complexity and storage for read speed.

Planners skip indexes when statistics are stale, the index column order mismatches the query, or the table is small enough that a sequential scan is cheaper. Update statistics and verify column ordering against actual query patterns.