Database Interview Questions

1. A Query Suddenly Becomes Slow in Production

Question

A query suddenly becomes slow in production. How would you investigate?

Crisp Interview Answer

I’d troubleshoot it systematically:

Check metrics – query latency, throughput, CPU, memory, DB connections, and locks.
Identify the exact query – inspect slow-query logs or APM and compare with normal execution time.
Check the execution plan – use EXPLAIN / EXPLAIN ANALYZE.
Check indexes – verify that the required indexes exist and are being used.
Check data growth – a large increase in table size or data distribution can change the execution plan.
Check blocking and locks – look for long-running transactions or lock contention.
Check DB resources – CPU, I/O, memory, and connection-pool exhaustion.
Check recent changes – deployments, schema changes, indexes, configuration changes, or database upgrades.
Mitigate first – safely stop problematic queries, rollback a bad change, fix an index, or temporarily scale resources.
Prevent recurrence – add monitoring, slow-query alerts, and query-performance checks.

Investigation Flow

flowchart TD
    A[Query Suddenly Slow] --> B[Check APM / Slow Query Logs]
    B --> C{Query Specific or DB Wide?}

    C -->|Query Specific| D[Check Execution Plan]
    D --> E[Check Index Usage]
    E --> F[Check Joins / Full Scans]
    F --> G[Check Data Growth]

    C -->|DB Wide| H[Check CPU / Memory / I/O]
    H --> I[Check Connections]
    I --> J[Check Locks / Blocking]
    J --> K[Check Long Transactions]

    G --> L[Check Recent Changes]
    K --> L
    L --> M[Mitigate]
    M --> N[Permanent Fix + Monitoring]

Interview One-Liner

“I first identify whether the slowdown is query-specific or database-wide, then check execution plans, indexes, locks, data growth, and DB resource utilization while correlating it with recent production changes.”

2. Clustered Index vs Non-Clustered Index

Question

What is the difference between a clustered index and a non-clustered index?

Comparison

Feature	Clustered Index	Non-Clustered Index
Data storage	Determines how table data is organized	Separate structure from table data
Number per table	Usually one	Multiple
Lookup	Efficient for ordered/range access	Efficient for indexed lookups
Storage	Data is organized around the index	Stores key + row reference
Common example	Primary key in systems such as InnoDB	Index on email, status, etc.

Clustered Index

A clustered index determines the organization of the table’s data around the index key.

Conceptually:

1 → Employee row
2 → Employee row
3 → Employee row
4 → Employee row

Non-Clustered Index

A non-clustered index is maintained separately from the table data.

Conceptually:

IT → reference → Employee row
HR → reference → Employee row
Finance → reference → Employee row

Mermaid Illustration

flowchart LR
    A[Clustered Index] --> B[Organized Table Data]

    C[Non-Clustered Index] --> D[Index Keys]
    D --> E[Row References]
    E --> F[Table Data]

Interview Answer

“A clustered index determines how the table’s rows are organized for storage and access, so a table generally has only one. A non-clustered index is a separate index structure containing keys and references to the actual rows, so a table can have multiple.”

Important Database-Specific Note

The exact implementation depends on the database.

For example:

SQL Server explicitly distinguishes clustered and non-clustered indexes.
MySQL InnoDB uses the primary key as the clustered index.
Other database engines can implement physical storage and indexes differently.

So in an interview, avoid presenting the behavior as identical across every database.

3. Identifying N+1 Query Problems in Hibernate

Question

How would you identify N+1 query problems in Hibernate?

What Is N+1?

An N+1 problem occurs when Hibernate executes:

1 query to load the parent records
N additional queries to load related records

For example:

SELECT * FROM employee;

SELECT * FROM department WHERE id = 1;
SELECT * FROM department WHERE id = 2;
SELECT * FROM department WHERE id = 3;
SELECT * FROM department WHERE id = 4;

Instead of one efficient query, the application performs many database round trips.

Identification Techniques

Enable Hibernate SQL logging
- Look for repeated queries generated inside loops.
Use Hibernate statistics
- hibernate.generate_statistics=true
- Check query counts and entity fetching behavior.
Use APM tools
- APM systems can reveal repeated DB calls and their latency.
Inspect application logs
- Look for one parent query followed by repeated child queries.
Review entity relationships
- Pay particular attention to @OneToMany and @ManyToMany.
- Lazy relationships accessed repeatedly can trigger the problem.

Detection Flow

flowchart TD
    A[API Request] --> B[Load Parent Entities]
    B --> C[Inspect Hibernate SQL Logs]
    C --> D{One Parent Query + N Similar Queries?}

    D -->|Yes| E[N+1 Problem]
    D -->|No| F[Investigate Other DB Issues]

    E --> G[JOIN FETCH]
    E --> H[EntityGraph]
    E --> I[Batch Fetching]
    E --> J[DTO Projection]

How to Fix It

Common approaches:

JOIN FETCH
@EntityGraph
Hibernate @BatchSize
Batch fetching configuration
DTO projections
Avoid unnecessary lazy relationship access

Interview One-Liner

“I identify N+1 by checking Hibernate SQL logs or APM for one parent query followed by N similar child queries, then fix it using JOIN FETCH, EntityGraph, batch fetching, or DTO projections.”

4. Transaction Isolation Levels

Question

Explain transaction isolation levels. When do dirty reads occur?

What Is Transaction Isolation?

Transaction isolation defines how much one transaction is protected from changes made concurrently by other transactions.

The standard isolation levels are:

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED	Prevented	Possible	Possible
REPEATABLE READ	Prevented	Prevented	Database-dependent / implementation-specific
SERIALIZABLE	Prevented	Prevented	Prevented

Dirty Read

A dirty read occurs when Transaction A reads data that Transaction B has modified but has not committed yet.

Example:

Transaction B:
UPDATE account
SET balance = 500
WHERE id = 1;

-- Transaction B has not committed

Transaction A then executes:

SELECT balance
FROM account
WHERE id = 1;

Transaction A may read:

If Transaction B subsequently executes:

ROLLBACK;

the value returns to its previous state.

Therefore, Transaction A read data that was never committed.

Dirty Read Flow

sequenceDiagram
    participant A as Transaction A
    participant B as Transaction B
    participant DB as Database

    B->>DB: UPDATE balance = 500
    Note over B,DB: Not committed yet

    A->>DB: SELECT balance
    DB-->>A: 500

    B->>DB: ROLLBACK
    Note over DB: Value returns to previous state

    Note over A,DB: A read uncommitted data

When Do Dirty Reads Occur?

Dirty reads are possible under:

READ UNCOMMITTED

They are prevented from:

READ COMMITTED onwards.

Interview One-Liner

“A dirty read occurs when one transaction reads uncommitted changes made by another transaction. It is possible under READ UNCOMMITTED and prevented from READ COMMITTED onwards.”

Important Note

The exact behavior can depend on the database implementation.

For example, MySQL InnoDB uses MVCC and provides behavior that can be stronger than the minimum SQL-standard isolation semantics for some isolation levels.

5. SQL Database vs NoSQL Database

Question

When would you use:

SQL Database
NoSQL Database

Quick Comparison

Criteria	SQL Database	NoSQL Database
Data model	Relational, structured	Document, key-value, wide-column, graph
Schema	Usually predefined	Flexible schema
Relationships	Strong support for joins	Usually designed around access patterns
Transactions	Strong ACID support	Depends on database
Querying	Complex queries and joins	Often optimized for specific access patterns
Scaling	Vertical scaling, replicas, and other strategies	Commonly designed for horizontal distribution
Consistency	Strong consistency commonly available	Depends on the database
Best suited for	Transactional and relational workloads	Flexible and distributed workloads

When to Use SQL

Use SQL when:

Data has clear relationships.
ACID transactions are important.
Consistency is critical.
Complex joins are required.
Reporting queries are important.
The schema is relatively well-defined.
Multiple entities need to be updated atomically.

Examples

Banking systems
Payment processing
Order management
Inventory management
Customer/account management
Financial reporting

Example E-Commerce Data Model

erDiagram
    CUSTOMER ||--o{ ORDER : places
    ORDER ||--|{ ORDER_ITEM : contains
    PRODUCT ||--o{ ORDER_ITEM : included_in

    CUSTOMER {
        bigint id PK
        string name
        string email
    }

    ORDER {
        bigint id PK
        bigint customer_id FK
        decimal total_amount
        string status
    }

    ORDER_ITEM {
        bigint id PK
        bigint order_id FK
        bigint product_id FK
        int quantity
    }

    PRODUCT {
        bigint id PK
        string name
        decimal price
    }

This type of relationship is naturally represented using relational tables and foreign keys.

6. When to Use NoSQL

Use NoSQL when:

The schema needs to evolve frequently.
Data is naturally represented as documents, key-value pairs, wide columns, or graphs.
Very high read/write throughput is required.
Horizontal scaling across multiple nodes is important.
Access patterns are known and can be optimized around.
Large volumes of distributed data need to be handled efficiently.

Examples

Product catalogs
User sessions
Caching
Event streams
IoT data
Activity feeds
Large-scale distributed workloads

Example: Document Database

A product catalog may contain different attributes for different product types.

{
  "productId": "P1001",
  "name": "Laptop",
  "category": "Electronics",
  "specifications": {
    "ram": "16GB",
    "storage": "1TB SSD",
    "screenSize": "15.6 inch"
  }
}

Another product can have a different set of attributes without requiring a rigid relational schema.

7. SQL vs NoSQL Decision Guide

flowchart TD
    A[Start: Understand Requirements] --> B{Strong ACID Transactions?}

    B -->|Yes| C{Complex Relationships / Joins?}
    B -->|No| D{Flexible Schema or Distributed Scale?}

    C -->|Yes| E[Prefer SQL]
    C -->|No| F[Evaluate Based on Access Patterns]

    D -->|Yes| G[Consider NoSQL]
    D -->|No| H{Complex Queries and Reporting?}

    H -->|Yes| E
    H -->|No| F

    F --> I[Select Database Based on Workload]

8. SQL and NoSQL Together

A real application does not necessarily have to choose only one database technology.

Different components can use different storage technologies based on their workload.

This is commonly called polyglot persistence.

Example Architecture

flowchart LR
    U[Client] --> API[API / Microservice]

    API --> SQL[(SQL Database)]
    API --> CACHE[(Redis / Key-Value Store)]
    API --> DOC[(Document Database)]
    API --> EVENTS[Event Stream]

    SQL --> T[Orders / Payments / Customers]
    CACHE --> C[Sessions / Cached Data]
    DOC --> D[Product Catalog]
    EVENTS --> E[Activity / Analytics]

Example

PostgreSQL/MySQL → orders, payments, customers
MongoDB → flexible product catalog
Redis → cache/session data
Cassandra → high-volume distributed writes
DynamoDB → managed key-value/document workloads

9. Important Interview Point

Avoid saying:

“NoSQL is always faster and more scalable than SQL.”

A better answer is:

“Database selection should be based on the workload, data model, consistency requirements, transaction requirements, query patterns, and scalability needs.”

For example:

Requirement	Possible Choice
Banking transactions	SQL
Payment processing	SQL
Complex relational queries	SQL
Flexible product catalog	Document NoSQL
Distributed high-write workload	Wide-column NoSQL
Fast cache/session lookup	Key-value store
Event/analytics workload	Depends on access pattern and scale

10. Senior-Level Interview Follow-Up

Question

Can you use SQL and NoSQL together?

Answer

“Yes. In a microservices architecture, different services can use different databases based on their requirements. For example, an order service can use PostgreSQL for transactional consistency, while a product catalog can use MongoDB for flexible product attributes, and Redis can be used for caching.”

Architecture

flowchart TB
    A[Client] --> B[API Gateway]

    B --> C[Order Service]
    B --> D[Product Service]
    B --> E[User Service]

    C --> F[(PostgreSQL)]
    D --> G[(MongoDB)]
    C --> H[(Redis)]
    E --> I[(SQL Database)]

11. Quick Revision Sheet

Production Query Performance

Check APM → slow query logs → execution plan → indexes → locks → data growth → DB resources → recent changes → mitigation.

Clustered Index

Organizes table data around the index key. Generally one per table.

Non-Clustered Index

Separate index structure containing keys and references to table rows. Multiple indexes are possible.

Hibernate N+1

One parent query + N child queries.

Typical fixes:

JOIN FETCH
EntityGraph
Batch Fetching
DTO Projection

Dirty Read

Reading uncommitted data from another transaction.

Occurs with:

READ UNCOMMITTED

Isolation Levels

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

SQL

Relationships + ACID + consistency + complex queries.

NoSQL

Flexible data models + distributed scale + high-throughput access patterns.

Polyglot Persistence

Use different databases for different workloads rather than forcing one database to solve every problem.