Backend interview prep

Start

Scenario-Based Database Interview Questions

Master Scenario-Based Database Interviews with 100 real-world production scenarios covering performance tuning, locking, deadlocks, replication, sharding, caching, transactions, cloud databases, and troubleshooting.

Introduction

Senior database interviews rarely ask only theory.

Instead, interviewers ask questions like

"Production database is slow. What will you do?"

"Users are complaining about duplicate orders."

"Replication lag increased after deployment."

These questions evaluate

  • Production Experience
  • Debugging Skills
  • Architecture Knowledge
  • Performance Tuning
  • Decision Making

This guide contains 100 real-world database interview scenarios frequently asked in FAANG and enterprise companies.

Problem Solving Framework

Always answer production scenarios using this approach.

Problem
     │
     ▼
Collect Symptoms
     │
     ▼
Identify Root Cause
     │
     ▼
Verify
     │
     ▼
Fix
     │
     ▼
Monitor
     │
     ▼
Prevent Future Issues

Performance Scenarios

1. Production database suddenly became slow. What will you check?

Answer

  • CPU
  • Memory
  • Disk I/O
  • Slow Queries
  • Blocking Sessions
  • Connection Count
  • Execution Plans
  • Recent Deployments

2. One query suddenly takes 30 seconds instead of 200 ms.

Check

  • Execution Plan
  • Statistics
  • Missing Index
  • Parameter Changes
  • Data Growth

3. CPU reaches 100%.

Possible causes

  • Full Table Scans
  • Missing Indexes
  • Cartesian Joins
  • Infinite Loops
  • Large Reports

4. Database memory usage is high.

Check

  • Buffer Cache
  • Connection Pool
  • Large Sorts
  • Temporary Tables
  • Memory Leaks

5. Disk usage increases every day.

Check

  • Logs
  • Audit Tables
  • Archive Tables
  • Backups
  • Table Growth

6. Queries became slow after deployment.

Check

  • New SQL
  • Missing Index
  • ORM Generated SQL
  • Statistics
  • Schema Changes

7. Application timeout occurs only during peak hours.

Investigate

  • Connection Pool
  • Locking
  • Slow Queries
  • CPU
  • Thread Pool

8. One report takes 20 minutes.

Improve

  • Indexes
  • Partitioning
  • Materialized Views
  • Pagination
  • Query Rewrite

9. SELECT query is slow.

Check

  • Execution Plan
  • Index Usage
  • Statistics
  • Joins

10. INSERT performance dropped.

Investigate

  • Too Many Indexes
  • Triggers
  • Foreign Keys
  • Logging
  • Disk

Locking & Transaction Scenarios

11. Users cannot update records.

Check

Blocking Sessions.

12. Deadlocks increased after release.

Check

  • Lock Order
  • Transaction Size
  • New SQL

13. Long-running transactions.

Problems

  • Blocking
  • Undo Growth
  • Lock Contention

14. Application hangs during UPDATE.

Investigate

  • Locks
  • Deadlocks
  • Waiting Sessions

15. Duplicate Orders are created.

Solution

  • Unique Constraint
  • Idempotency
  • Transactions

16. Money deducted twice.

Check

  • Retry Logic
  • Idempotency
  • Transaction Handling

17. Dirty Read issue reported.

Increase Isolation Level.

18. Phantom Reads observed.

Use

Serializable

or appropriate locking depending on requirements.

19. Lost Updates occur.

Use

Optimistic Locking

or

Pessimistic Locking.

20. Transaction rollback takes long.

Investigate

  • Transaction Size
  • Undo Log
  • Large Updates

Replication Scenarios

21. Replica is 15 minutes behind.

Check

  • Network
  • WAL/Binlog
  • Disk
  • CPU

22. Read Replica returns old data.

Expected with asynchronous replication.

Use primary for critical reads if necessary.

23. Replication stopped.

Check

  • Errors
  • Disk
  • Network
  • Credentials

24. Primary database crashed.

Perform

Automatic

or

Manual Failover.

25. Multi-region replication is slow.

Check

  • Network Latency
  • Distance
  • Compression

26. Replica storage fills quickly.

Review

Retention

Logs

Backups.

27. Replication conflicts occur.

Review

Conflict Resolution Strategy.

28. Failover takes too long.

Improve

Health Checks

Automation

29. Replica CPU is high.

Investigate

Large Queries

Replay Lag

Indexes.

30. Disaster Recovery test failed.

Review

Recovery Procedures

Backups

Automation.

Index Scenarios

31. Index exists but query ignores it.

Possible causes

  • Function on Column
  • Wrong Data Type
  • Low Selectivity

32. Too many indexes.

Problems

  • Slow Writes
  • Storage Usage
  • Maintenance

33. Composite Index not used.

Verify

Left-most Prefix.

34. Index became fragmented.

Rebuild

or

Reorganize.

35. Large index consumes storage.

Evaluate necessity.

36. Query performs Full Table Scan.

Create

Proper Index.

37. Index creation blocks users.

Use

Online Index Creation

where supported.

38. Covering Index improves performance.

Explain why.

39. Unique Index violation.

Investigate duplicate requests.

40. Index maintenance strategy?

Schedule during low traffic.

NoSQL Scenarios

41. MongoDB aggregation becomes slow.

Optimize

  • Match Early
  • Proper Indexes
  • Projection

42. Redis cache hit ratio drops.

Check

TTL

Eviction

Application Logic.

43. Cassandra hot partition.

Redesign

Partition Key.

44. DynamoDB throttling.

Increase Capacity

or

Improve Partition Key.

45. Redis memory reaches limit.

Review

Eviction Policy.

46. MongoDB collection grows rapidly.

Use

Sharding.

47. Cassandra repair takes hours.

Run

Incremental Repairs

Regularly.

48. DynamoDB scan is slow.

Use

Query

Instead.

49. Redis cache inconsistency.

Implement proper cache invalidation.

50. MongoDB replica lag.

Check

Oplog

Disk

Network.

System Design Scenarios

51. Design database for banking.

Focus

  • ACID
  • Audit
  • Security
  • Transactions

52. Design shopping cart.

Use

Redis

Database.

53. Design order management.

Include

Inventory

Payments

Orders

54. Design social media feed.

Use

Cache

Fan-out

Timeline.

55. Design hospital database.

Patient

Doctor

Appointments

Billing.

56. Design inventory system.

Stock

Warehouse

Orders.

57. Design ticket booking.

Prevent

Double Booking.

58. Design payment gateway.

Idempotency

Transactions

Retries.

59. Design notification service.

Queue

Retry

DLQ.

60. Design audit system.

Immutable Logs.

Cloud Database Scenarios

61. AWS RDS CPU high.

Check

Performance Insights

Slow Queries.

62. Aurora failover occurred.

Verify

Reader Promotion

Connections.

63. DynamoDB costs increased.

Check

Scans

Unused GSIs

Capacity.

64. MongoDB Atlas storage growing.

Review

Indexes

Data Retention.

65. Redis Cluster node failed.

Automatic failover should occur.

66. PostgreSQL VACUUM not running.

Check

Autovacuum Configuration.

67. Oracle archive logs full.

Archive

Backup

Delete safely.

68. MySQL replication lag after backup.

Monitor

Binary Logs

Network.

69. Database migration fails.

Rollback

Retry

Validate.

70. Kubernetes database pod restarted.

Check

Resources

Storage

Logs.

Security Scenarios

71. Unauthorized data access.

Review

IAM

Roles

Audit Logs.

72. SQL Injection detected.

Use

Prepared Statements.

73. Sensitive data exposed.

Encrypt

Mask

Audit.

74. Database credentials leaked.

Rotate

Secrets

Immediately.

75. Excessive failed logins.

Investigate

Brute Force

Monitoring.

Senior-Level Scenarios

76. Database handles 500 TPS today but needs 20,000 TPS.

Plan

  • Caching
  • Partitioning
  • Replication
  • Sharding

77. Database reaches 10 TB.

Implement

Partitioning

Archiving.

78. Global users experience latency.

Deploy

Multi-Region Databases.

79. Database migration with zero downtime.

Use

Dual Writes

CDC

Cutover.

80. Legacy Oracle migration to PostgreSQL.

Plan

Schema

Data

Testing

Rollback.

Architect-Level Scenarios

81. Choose database for payment system.

SQL.

82. Choose database for IoT.

Cassandra

or

DynamoDB.

83. Choose database for session storage.

Redis.

84. Choose database for product catalog.

MongoDB

or

PostgreSQL depending on query requirements.

85. Choose database for analytics.

Data Warehouse.

86. Multi-tenant database design.

Shared

or

Separate Database.

87. Handle one billion records.

Partition

Shard

Archive.

88. Reduce database costs.

Compression

Archiving

Reserved Capacity.

89. Handle complete region outage.

Cross Region Replication.

90. Design disaster recovery.

Backup

Replication

Recovery Testing.

Leadership Scenarios

91. Production issue at 2 AM.

Remain calm.

Follow incident response.

92. Database outage.

Communicate

Mitigate

Recover.

93. Critical data loss.

Restore

Backups

Validate.

94. Performance issue during Black Friday.

Scale

Cache

Monitor.

95. Multiple databases in one architecture.

Choose

Best Database

Per Use Case.

96. Customer reports missing data.

Audit

Transactions

Logs

Recovery.

97. Production deployment failed.

Rollback

Root Cause Analysis.

98. Unexpected database restart.

Investigate

Logs

Infrastructure

Crash Recovery.

99. What would you monitor continuously?

  • CPU
  • Memory
  • I/O
  • Connections
  • Replication
  • Query Latency
  • Lock Waits
  • Disk Space

100. How do you answer scenario questions?

Use this framework

Understand Problem
        │
        ▼
Ask Clarifying Questions
        │
        ▼
Identify Possible Causes
        │
        ▼
Collect Evidence
        │
        ▼
Implement Solution
        │
        ▼
Verify Results
        │
        ▼
Prevent Future Issues

Enterprise Troubleshooting Workflow

Alert Triggered
       │
       ▼
Check Monitoring Dashboard
       │
       ▼
Identify Bottleneck
       │
       ▼
Analyze Logs
       │
       ▼
Check Database Metrics
       │
       ▼
Fix Root Cause
       │
       ▼
Validate Solution
       │
       ▼
Post-Incident Review

Quick Revision

Scenario Primary Focus
Slow Query Execution Plan + Indexes
High CPU Query Analysis
Deadlock Lock Ordering
Replication Lag Network + Logs
Hot Partition Partition Key
Cache Miss TTL + Eviction
High Connections Connection Pool
Data Loss Backup + Recovery
Region Failure Disaster Recovery
Scaling Sharding + Replication

Interview Tips

When answering scenario-based questions:

  • Clarify the problem before proposing a solution.
  • Gather evidence instead of guessing.
  • Mention monitoring tools and metrics.
  • Explain both immediate mitigation and long-term prevention.
  • Discuss tradeoffs where applicable.
  • Relate answers to production experience.
  • Follow a structured troubleshooting approach.
  • Emphasize observability, automation, and resilience.

Summary

Scenario-based database interviews evaluate how you think under real production conditions. Interviewers expect you to diagnose issues methodically, identify root causes, implement effective solutions, and prevent future occurrences.

Mastering these 100 scenario-based database interview questions prepares you for Senior Software Engineer, Database Engineer, Site Reliability Engineer (SRE), DevOps Engineer, Solution Architect, and Principal Engineer interviews where practical production experience is as important as technical knowledge.