Backend interview prep

Start

Database System Design Interview Questions

Master Database System Design Interviews with production-ready questions covering database selection, schema design, partitioning, replication, caching, CAP theorem, consistency models, scaling, disaster recovery, and real-world architecture scenarios.

Introduction

Database System Design interviews evaluate whether you can design databases that are

  • Scalable
  • Highly Available
  • Fault Tolerant
  • Secure
  • Cost Effective
  • Easy to Maintain

Unlike SQL interviews,

these interviews focus on

  • Architecture
  • Scalability
  • Distributed Systems
  • High Availability
  • Performance
  • Tradeoffs

These questions are common in interviews for

  • Senior Software Engineer
  • Staff Engineer
  • Principal Engineer
  • Solution Architect
  • Cloud Architect

Database Design Roadmap

Requirements
      │
      ▼
Data Model
      │
      ▼
Database Selection
      │
      ▼
Schema Design
      │
      ▼
Indexes
      │
      ▼
Caching
      │
      ▼
Replication
      │
      ▼
Partitioning
      │
      ▼
Monitoring
      │
      ▼
Scaling

Functional Design

1. How do you start a Database System Design interview?

Always begin with

  • Functional Requirements
  • Non-Functional Requirements
  • Scale Estimation
  • Data Model
  • Access Patterns

2. What functional requirements should you gather?

Examples

  • Users
  • Products
  • Orders
  • Payments
  • Reports
  • Search
  • Notifications

3. What non-functional requirements should you gather?

  • Availability
  • Scalability
  • Latency
  • Throughput
  • Security
  • Disaster Recovery
  • Cost

4. Why is access pattern important?

Database design should optimize for

  • Read Frequency
  • Write Frequency
  • Search Patterns
  • Reporting Queries

5. How do you estimate scale?

Estimate

  • Users
  • Requests/sec
  • Reads/sec
  • Writes/sec
  • Storage Growth
  • Daily Traffic

Database Selection

6. SQL vs NoSQL?

SQL NoSQL
ACID Flexible Schema
Strong Consistency Horizontal Scaling
Complex Queries High Write Throughput

7. When should you choose SQL?

  • Banking
  • ERP
  • Finance
  • Payments
  • Inventory

8. When should you choose NoSQL?

  • Logs
  • IoT
  • Social Media
  • Analytics
  • User Sessions

9. PostgreSQL vs MySQL?

PostgreSQL

Better advanced SQL.

MySQL

Simpler administration.

10. MongoDB vs Cassandra?

MongoDB

Flexible documents.

Cassandra

Massive write scalability.

Schema Design

11. Normalize or Denormalize?

Normalize

for consistency.

Denormalize

for performance.

12. Primary Keys?

Use stable

unique identifiers.

13. UUID vs Auto Increment?

UUID Auto Increment
Distributed Simple
Unique Globally Sequential

14. Composite Keys?

Useful when

multiple columns identify data.

15. Foreign Keys?

Maintain referential integrity.

Index Design

16. Why indexes?

Improve query performance.

17. Which columns should be indexed?

Frequently

  • Filtered
  • Joined
  • Sorted

18. Composite Index?

Multiple columns.

19. Covering Index?

Contains every required column.

20. Too many indexes?

Increase write overhead.

Partitioning

21. What is Partitioning?

Splitting a table

into smaller pieces.

22. Types?

  • Range
  • Hash
  • List

23. Why Partition?

  • Faster Queries
  • Easier Maintenance

24. Horizontal Partitioning?

Split rows.

25. Vertical Partitioning?

Split columns.

Replication

26. Why Replication?

  • High Availability
  • Disaster Recovery
  • Read Scaling

27. Primary-Replica?

Single writer.

Multiple readers.

28. Multi-Primary?

Multiple writers.

Requires conflict resolution.

29. Synchronous Replication?

Strong consistency.

Higher latency.

30. Asynchronous Replication?

Better performance.

Eventual consistency.

Sharding

31. What is Sharding?

Distributing data

across databases.

32. Why Sharding?

Scale horizontally.

33. Sharding Strategies?

  • Hash
  • Range
  • Geographic
  • Directory Based

34. Good Shard Key?

High Cardinality.

35. Bad Shard Key?

Hotspot creation.

Caching

36. Why Cache?

Reduce database load.

Redis.

38. Cache Aside?

Application loads cache.

39. Write Through?

Database and cache updated together.

40. Cache Invalidation?

Most difficult caching problem.

Transactions

41. ACID?

Required for banking.

42. Eventual Consistency?

Suitable for distributed systems.

43. Distributed Transactions?

Use Saga Pattern.

44. Two Phase Commit?

Strong consistency.

45. Saga Pattern?

Preferred for microservices.

CAP Theorem

46. Explain CAP Theorem.

Choose two

  • Consistency
  • Availability
  • Partition Tolerance

47. SQL Database?

Typically

CP.

48. Cassandra?

AP

with tunable consistency.

49. DynamoDB?

Availability

Partition Tolerance

with configurable read consistency.

50. MongoDB?

Replica sets emphasize consistency, while sharded deployments balance availability and consistency depending on configuration.

High Availability

51. How do you achieve HA?

  • Replication
  • Failover
  • Monitoring

52. Automatic Failover?

Supported by many databases.

53. Disaster Recovery?

  • Backup
  • Replication
  • Recovery Plan

54. Multi Region?

Improves resilience.

55. Backup Strategy?

  • Full
  • Incremental
  • Continuous

Performance

56. Slow Queries?

Use

Execution Plans.

57. Full Table Scan?

Avoid using indexes.

58. Connection Pool?

Reuse connections.

59. Batch Writes?

Reduce round trips.

60. Pagination?

Prefer Keyset Pagination.

Security

61. Encryption?

At Rest

In Transit.

62. Authentication?

OAuth

IAM

LDAP.

63. Authorization?

Role Based.

64. Auditing?

Store security logs.

65. Data Masking?

Protect sensitive data.

Monitoring

66. What should you monitor?

  • CPU
  • Memory
  • Connections
  • Queries
  • Replication
  • Disk

67. Slow Query Monitoring?

Enable slow query logs.

68. Replication Monitoring?

Lag.

69. Cache Hit Ratio?

Monitor continuously.

70. Alerts?

CPU

Disk

Latency

Errors.

System Design Scenarios

71. Design a Banking Database.

Focus

  • ACID
  • Transactions
  • Audit
  • Security

72. Design Amazon Shopping Cart.

  • Redis
  • DynamoDB
  • Product Catalog

73. Design YouTube.

  • Metadata
  • CDN
  • Search

74. Design Uber.

  • Geo Queries
  • Real Time Updates
  • Driver Matching

75. Design WhatsApp.

  • Messaging
  • Read Receipts
  • Offline Queue

76. Design Netflix.

  • User Profiles
  • Watch History
  • Recommendation

77. Design Twitter.

  • Feed Generation
  • Followers
  • Timeline

78. Design Hospital Management.

  • Patient Records
  • Billing
  • Appointments

79. Design Banking Ledger.

Immutable Transactions.

80. Design Inventory System.

Real-time Stock Updates.

Senior-Level Questions

81. Database Migration Strategy?

  • Backup
  • Validate
  • Rollback
  • Cutover

82. Blue-Green Database Deployment?

Zero downtime deployment.

83. Zero Downtime Migration?

Dual Writes

Incremental Sync.

84. Multi-Tenant Database?

  • Shared Schema
  • Separate Schema
  • Separate Database

85. Event Sourcing?

Store events instead of current state.

86. CQRS?

Separate read

and

write models.

87. Outbox Pattern?

Reliable event publishing.

88. Change Data Capture (CDC)?

Capture database changes for downstream systems.

89. Idempotency?

Prevent duplicate operations.

90. Distributed Lock?

Redis

ZooKeeper

etcd.

Solution Architect Questions

91. How would you design a global database?

  • Multi Region
  • Replication
  • Geo Routing
  • Disaster Recovery

92. Design for one billion users.

  • Sharding
  • Caching
  • CDN
  • Load Balancer
  • Replication

93. Handle 100,000 writes/sec.

  • Cassandra
  • DynamoDB
  • Kafka
  • Partitioning

94. Handle 1 TB/day data growth.

  • Archiving
  • Compression
  • Partitioning
  • Data Lake

95. Handle complete region failure.

  • Cross Region Replication
  • DNS Failover
  • Automated Recovery

96. Best Cloud Databases?

  • Aurora
  • DynamoDB
  • Cloud SQL
  • Cosmos DB
  • Spanner

97. How do you reduce database cost?

  • Right Sizing
  • Auto Scaling
  • Archiving
  • Compression
  • Reserved Capacity

98. Most common database bottlenecks?

  • Missing Indexes
  • Locking
  • Slow Queries
  • Hot Partitions
  • Network Latency

99. Database Design Checklist?

  • Schema
  • Indexes
  • Replication
  • Backup
  • Monitoring
  • Security
  • Scaling

100. What do interviewers expect?

  • Clear Thinking
  • Tradeoff Analysis
  • Scalability
  • High Availability
  • Real Production Experience

Enterprise Database Architecture

                    Users
                      │
              Load Balancer
                      │
          ┌───────────┴───────────┐
          │                       │
     Application A          Application B
          │                       │
          └───────────┬───────────┘
                      │
                 Redis Cache
                      │
         ┌────────────┴────────────┐
         │                         │
     Read Replica            Read Replica
         │                         │
         └────────────┬────────────┘
                      │
               Primary Database
                      │
              Backup / Archive
                      │
             Disaster Recovery Site

Database Selection Guide

Requirement Recommended Database
Banking PostgreSQL / Oracle
E-Commerce MySQL + Redis
User Sessions Redis
Social Media MongoDB
IoT Cassandra
Event Store Cassandra
Shopping Cart DynamoDB
Analytics PostgreSQL + Data Warehouse
Logging Elasticsearch / OpenSearch + Object Storage
High-Speed Cache Redis

System Design Checklist

  • Gather functional requirements.
  • Estimate traffic and storage.
  • Choose the right database.
  • Design the schema.
  • Identify indexes.
  • Plan partitioning and sharding.
  • Add caching where appropriate.
  • Design replication and failover.
  • Define backup and disaster recovery.
  • Plan monitoring and alerting.
  • Consider security and compliance.
  • Discuss tradeoffs clearly.

Quick Revision

Topic Key Point
SQL Strong Consistency
NoSQL Horizontal Scaling
Cache Redis
Replication High Availability
Sharding Horizontal Scale
CAP Consistency vs Availability
CQRS Separate Reads/Writes
Saga Distributed Transactions
CDC Database Change Events
Outbox Reliable Event Publishing

Interview Tips

System design interviews are less about finding a single correct answer and more about making sound architectural decisions.

During your discussion:

  • Clarify requirements before designing.
  • Estimate scale (users, TPS, storage).
  • Choose technologies based on requirements.
  • Explain tradeoffs for every decision.
  • Address scalability, availability, and consistency.
  • Consider security, monitoring, backup, and disaster recovery.
  • Draw simple architecture diagrams.
  • Discuss how the design evolves as traffic grows.

Summary

Database System Design interviews evaluate your ability to build scalable, highly available, fault-tolerant, and maintainable data platforms. Success requires understanding database selection, schema design, indexing, replication, partitioning, sharding, caching, CAP theorem, distributed transactions, high availability, monitoring, and disaster recovery.

Mastering these 100 Database System Design interview questions prepares you for Senior Software Engineer, Staff Engineer, Principal Engineer, Solution Architect, Cloud Architect, and Enterprise Architect interviews.