1. How would you optimize a slow-running SQL query?

S – Situation

A production query is taking several seconds/minutes, affecting user experience.

T – Task

Identify bottlenecks and optimize the query for better performance.

A – Action

  • Analyze execution plan (EXPLAIN)
  • Check for:
    • Full table scans
    • Missing indexes
  • Optimize:
    • Add appropriate indexes
    • Rewrite query (avoid subqueries, use joins)
    • Reduce data with proper filters
    • Use pagination (LIMIT)
  • Ensure proper normalization or selective denormalization

R – Result

  • Reduced query execution time significantly
  • Improved system responsiveness

2. How do you handle deadlocks in production systems?

S – Situation

Multiple transactions are blocking each other, causing system slowdown.

T – Task

Resolve and prevent deadlocks without affecting data consistency.

A – Action

  • Identify deadlock using logs / DB monitoring
  • Kill one transaction (victim selection)
  • Prevent future deadlocks:
    • Maintain consistent lock order
    • Reduce transaction size
    • Use proper indexing
    • Implement retry logic

R – Result

  • Deadlocks resolved quickly
  • System stability improved

3. How would you design a database for an e-commerce application?

S – Situation

Need scalable DB design for products, users, orders, payments.

T – Task

Design a normalized yet scalable schema.

A – Action

  • Identify entities:
    • Users, Products, Orders, Cart, Payments
  • Relationships:
    • User → Orders (1:N)
    • Orders → Products (M:N via Order_Items)
  • Apply:
    • Normalization (up to 3NF)
    • Indexing on search fields (product name, category)
  • Add:
    • Caching layer for product catalog
    • Partitioning for large tables

R – Result

  • Scalable and efficient system design
  • Supports high traffic and transactions

4. How do you migrate data between two DBMSs?

S – Situation

Migrating from one DBMS (e.g., MySQL → PostgreSQL).

T – Task

Ensure safe and consistent data transfer.

A – Action

  • Analyze schema differences
  • Use ETL tools or scripts
  • Steps:
    • Extract data
    • Transform format
    • Load into new DB
  • Validate:
    • Data integrity checks
    • Row counts, constraints
  • Perform testing before production

R – Result

  • Smooth migration with minimal downtime
  • No data loss

5. How do you handle database downtime during peak hours?

S – Situation

System must remain available during high traffic.

T – Task

Minimize or eliminate downtime.

A – Action

  • Use replication (read replicas)
  • Implement failover systems
  • Perform rolling updates
  • Schedule maintenance during low traffic
  • Use blue-green deployment

R – Result

  • Zero or minimal downtime
  • Continuous availability

6. How do you ensure data consistency in a distributed system?

S – Situation

Multiple nodes handling concurrent updates.

T – Task

Maintain consistency across nodes.

A – Action

  • Use:
    • Distributed transactions (2PC)
    • Consensus algorithms (Raft/Paxos)
  • Apply:
    • Strong consistency where required
    • Eventual consistency where acceptable
  • Use versioning and conflict resolution

R – Result

  • Reliable and consistent data across system

7. How would you manage rapid data growth?

S – Situation

Database size growing rapidly, affecting performance.

T – Task

Scale system efficiently.

A – Action

  • Implement:
    • Partitioning and sharding
    • Archiving old data
    • Compression
  • Use distributed databases
  • Optimize indexes

R – Result

  • Improved scalability
  • Sustained performance

8. How do you troubleshoot sporadic performance issues?

S – Situation

Intermittent slowdowns without clear pattern.

T – Task

Identify root cause.

A – Action

  • Monitor:
    • CPU, memory, disk I/O
    • Query logs
  • Analyze slow queries
  • Check:
    • Lock contention
    • Network latency
  • Use profiling tools

R – Result

  • Root cause identified and fixed
  • Stable performance

9. How do you design for high read-heavy workloads?

S – Situation

System has significantly more reads than writes.

T – Task

Optimize for fast read performance.

A – Action

  • Use:
    • Read replicas
    • Caching (Redis)
    • Indexing
  • Denormalize data where needed
  • Use CDN for static data

R – Result

  • Faster response times
  • Reduced database load

10. How do you investigate data inconsistency issues?

S – Situation

Data mismatch across tables or systems.

T – Task

Identify and fix inconsistencies.

A – Action

  • Check:
    • Transaction logs
    • Replication lag
  • Validate constraints
  • Run consistency checks
  • Identify root cause:
    • Race conditions
    • Missing transactions
  • Fix data and implement safeguards

R – Result

  • Data consistency restored
  • Preventive mechanisms in place

11. How do you secure sensitive data in a multi-user database?

S – Situation

A production system stores sensitive data (PII, financial info) accessed by multiple users and services.

T – Task

Ensure data confidentiality, integrity, and controlled access across users.

A – Action

  • Authentication & Authorization
    • Implement RBAC (Role-Based Access Control)
    • Principle of least privilege
  • Encryption
    • Encrypt data at rest (disk-level, column-level for PII)
    • Encrypt data in transit (TLS/SSL)
  • Data Masking & Tokenization
    • Mask sensitive fields in non-production environments
  • Auditing & Monitoring
    • Enable audit logs for access and changes
    • Use alerts for suspicious activity
  • Network Security
    • Use firewalls, VPCs, IP whitelisting
  • Secrets Management
    • Store credentials securely (vaults, env configs)

R – Result

  • Strong data protection
  • Compliance readiness (e.g., GDPR-like requirements)
  • Reduced risk of breaches

12. What steps do you follow before deploying a database to production?

S – Situation

Preparing a new or updated database system for production deployment.

T – Task

Ensure stability, performance, and safety before going live.

A – Action

  • Schema Validation
    • Validate tables, constraints, indexes
  • Performance Testing
    • Run load/stress tests
  • Data Integrity Checks
    • Validate constraints and relationships
  • Backup Setup
    • Configure full + incremental backups
  • Security Setup
    • Configure roles, permissions, encryption
  • Migration Testing
    • Test deployment scripts in staging
  • Monitoring Setup
    • Enable logging, alerts, dashboards
  • Rollback Plan
    • Prepare fallback strategy

R – Result

  • Smooth production deployment
  • Minimal risk of failure
  • Faster issue recovery

13. How do you handle schema changes in live systems?

S – Situation

Need to modify database schema without disrupting active users.

T – Task

Apply changes without downtime and without breaking existing applications.

A – Action

  • Backward-Compatible Changes
    • Add columns instead of modifying/removing
  • Versioning
    • Maintain schema versions
  • Zero-Downtime Migration
    • Use rolling deployments
  • Dual Writes (if needed)
    • Write to old + new schema temporarily
  • Data Migration
    • Gradually migrate existing data
  • Feature Flags
    • Switch application logic safely
  • Monitoring
    • Track performance and errors during rollout

R – Result

  • Seamless schema evolution
  • No service disruption
  • Safe transition

14. How do you balance normalization and performance?

S – Situation

Highly normalized schema ensures integrity but causes slow queries due to joins.

T – Task

Balance data integrity and performance.

A – Action

  • Start with normalized design (up to 3NF)
  • Identify performance bottlenecks
  • Apply controlled denormalization:
    • Precompute frequently used data
  • Add indexes on critical columns
  • Use caching (Redis, etc.)
  • Optimize queries:
    • Avoid unnecessary joins
  • Use materialized views for heavy queries

R – Result

  • Optimized performance
  • Maintained data integrity
  • Efficient query execution

15. How do you plan backup and recovery for critical systems?

S – Situation

Critical system where data loss or downtime is unacceptable.

T – Task

Design a robust backup and recovery strategy.

A – Action

  • Backup Strategy
    • Full backup (weekly)
    • Incremental/differential (daily)
    • Transaction log backups (frequent)
  • Redundancy
    • Store backups in multiple locations (cloud + offsite)
  • Automation
    • Schedule backups with monitoring
  • Recovery Planning
    • Define RPO (data loss tolerance)
    • Define RTO (recovery time)
  • Testing
    • Regularly test restore procedures
  • Failover Systems
    • Use replication and standby servers

R – Result

  • Reliable data protection
  • Fast recovery during failures
  • Business continuity ensured