database-optimizer

Start by running EXPLAIN ANALYZE to inspect the execution plan and identify full table scans or expensive join operations. Then rewrite the query, add composite or covering indexes matching the join and filter columns, and validate the improvement with benchmarking.

Detect N+1 patterns by profiling ORM query logs or application traces, then resolve them with eager loading, batch queries, or JOIN optimization. For GraphQL APIs, use DataLoader patterns with query batching and field-level caching.

Design indexes based on actual query patterns rather than indexing every column. Use composite indexes with correct column ordering, partial indexes for filtered queries, and specialized types like GIN for JSONB or full-text search in PostgreSQL.

Yes, the guidance covers MongoDB aggregation pipeline optimization and compound indexes, plus DynamoDB query patterns and GSI/LSI design. It also addresses cloud-native services like Aurora, Cosmos DB, and BigQuery.

Read replicas scale read-heavy workloads with eventual consistency trade-offs, while sharding addresses write-heavy workloads requiring horizontal partitioning. Choose sharding only when vertical scaling and read replicas no longer meet write throughput demands.

Bottlenecks often span the full stack: connection pooling, transaction isolation, missing cache layers, or schema design. Profile the entire system with APM tools and database metrics before assuming the query itself is the root cause.