Database Troubleshooting and Diagnostics Questions

Systematic approaches and technical techniques for diagnosing database issues and restoring healthy operation. Topics include identifying symptoms, gathering diagnostic data from error logs and system views, analyzing slow queries with explain plans and profiling, diagnosing connection and authentication failures, detecting and resolving deadlocks and blocking, capacity and storage issues, replication and consistency problems, backup and restore verification, and corruption investigation. Candidates should be familiar with database specific diagnostic tools, monitoring and alerting metrics, indexing and query optimization strategies, and effective communication of findings to application and infrastructure teams.

EasyTechnical

0 practiced

Explain the difference between an index scan and a sequential scan in relational databases. Describe the cost trade-offs that influence the optimizer's choice, when an index helps most, and concrete situations where adding an index will not improve—and may worsen—performance (consider write overhead, poor selectivity, and random I/O).

MediumSystem Design

0 practiced

Design a runbook for responding to a primary database failure aimed at minimizing downtime. Include detection thresholds, automated checks, commands for promoting a healthy replica, verification steps after promotion (smoke writes/reads), rollback triggers if problems are detected, and communication steps for stakeholders.

HardTechnical

0 practiced

A deadlock occurs once per million transactions and is hard to reproduce. Design a test harness to reliably reproduce and triage the deadlock: include workload generator design, sampling of failing transactions, instrumentation to capture lock graphs and stack traces, and how to integrate minimal reproducible cases into CI to prevent regressions.

MediumTechnical

0 practiced

You ran EXPLAIN ANALYZE on a SELECT that takes 12 seconds. The plan shows a nested loop join reading large row counts. Explain step-by-step how to interpret EXPLAIN ANALYZE to determine why a nested loop was chosen (cardinality estimates, missing indexes, join ordering) and propose concrete changes (indexes, query rewrite, ANALYZE, hinting) to reduce latency. Mention trade-offs for each change.

MediumTechnical

0 practiced

You suspect index bloat is causing performance regressions on a PostgreSQL cluster. Describe how to detect index bloat (which queries/metricts to run, pgstattuple/pgstatindex usage), and provide a safe plan to defragment or rebuild indexes in production (concurrent rebuilds, scheduling, monitoring) with minimal impact.

Unlock Full Question Bank

Get access to hundreds of Database Troubleshooting and Diagnostics interview questions and detailed answers.

Join thousands of developers preparing for their dream job.