database-performance

Schema indexing strategy requires indexing foreign keys and columns used in join, where, and order clauses. You must avoid over-indexing by applying composite index left-prefix rules and considering covering indexes to optimize read paths without degrading write throughput.

Detecting N+1 query patterns involves analyzing query execution plans to identify unbounded loops of sequential database calls. Fix them by rewriting the queries to use proper joins or batch fetching, aligning pagination strategies, and verifying index coverage for the access paths.

Safe database migrations require analyzing locking risks, rollback procedures, and idempotency before execution. You evaluate foreign key behaviors and apply guardrails that prevent unsafe destructive changes without explicit confirmation, ensuring data integrity throughout the scaling process.

Database scaling options like partitioning, sharding, or archival are needed when you identify scaling bottlenecks that connection pooling and query optimization cannot resolve. You select these strategies alongside replicas and caching when monitoring metrics indicate sustained workload limits.

Query optimization applies to common relational database workloads by reviewing schemas, slow queries, and indexing strategies. It produces evidence-backed recommendations with prioritized actions and clear risks, ensuring data integrity constraints are maintained across different database systems.