Database Design and Architecture Questions

Designing and architecting databases for production and cloud environments with attention to data modeling, schema design, and access pattern optimization. Topics include normalization and denormalization trade offs, schema versus query driven modeling, entity and relationship design for transactional and analytical workloads, indexing and query optimization techniques, partitioning and sharding design decisions, schema evolution and migration strategies, materialized views and caching strategies, selection of storage layers for different data shapes, and practical operational runbooks for provisioning, monitoring, alerting, backups, disaster recovery, and capacity planning. Candidates should justify schema and architecture choices with respect to latency, throughput, development and operational complexity, maintainability, and cost.

HardTechnical

95 practiced

Design a monitoring and alerting runbook for database performance. List key metrics (e.g., CPU, disk IO, latency, active connections, replication lag), baseline thresholds, alerting rules, and step-by-step remediation steps for common incidents like high IO wait or replica lag.

MediumTechnical

103 practiced

Explain composite and covering indexes. Give examples when including additional columns in an index as included columns improves query performance and what costs those included columns impose.

HardTechnical

90 practiced

Design an alert escalation and runbook for long-running schema migrations that can cause table locks. Include detection, automated mitigation (e.g., canceling jobs, throttling), and manual escalation steps with safe rollback options.

EasyTechnical

79 practiced

Explain database transaction isolation levels (read uncommitted, read committed, repeatable read, serializable). For each level, describe which anomalies it prevents: dirty reads, non-repeatable reads, and phantom reads.

EasyTechnical

96 practiced

What is a query execution plan? As a Software Engineer, explain how you would use EXPLAIN or EXPLAIN ANALYZE output to diagnose a slow select that joins three large tables.

Unlock Full Question Bank

Get access to hundreds of Database Design and Architecture interview questions and detailed answers.

Join thousands of developers preparing for their dream job.