Top 50 Most Asked Database Interview Questions

Table of contents

Data Modeling and Fundamentals

1. What are the differences between SQL and NoSQL databases?

SQL databases are relational: they store data in tables with a fixed schema and usually support joins, transactions, and strong consistency guarantees. NoSQL is a broad category that includes document, key-value, wide-column, and graph databases; they often optimize for flexible schema, horizontal scale, or specialized access patterns. The right answer in interviews is not “NoSQL is faster,” but “pick the model that matches the workload and consistency requirements.”

2. How do you design a database schema for a given application?

Start from business entities and access patterns, not from tables. Identify the core objects, relationships, invariants, read/write paths, and growth expectations, then choose keys, constraints, and indexes that support those paths. A good schema is one that preserves correctness first and supports the most important queries cheaply.

3. What is normalization, and why is it important?

Normalization is the practice of structuring data to reduce redundancy and update anomalies. It matters because duplicated data becomes hard to keep consistent when writes happen in multiple places. In practice, most systems normalize the source-of-truth model first and selectively denormalize later for performance.

4. What are indexes, and how do they improve database performance?

An index is an auxiliary data structure that helps the database find rows without scanning the full table. It improves read performance by reducing the amount of data the engine must examine for filters, joins, sorting, and range scans. The tradeoff is that indexes consume storage and make writes more expensive because the index must also be updated.

5. What is a primary key, and why is it important?

A primary key uniquely identifies each row in a table. It matters because it gives the database a stable way to address records, enforce uniqueness, and build relationships from other tables. Good primary keys are immutable, narrow enough to index efficiently, and aligned with the access patterns of the system.

6. What are the different types of joins in SQL, and when would you use each?

INNER JOIN returns only matching rows from both sides. LEFT JOIN keeps all rows from the left side even when no match exists, which is useful for optional relationships or reporting missing associations. RIGHT JOIN is the symmetric version of LEFT JOIN, though many teams avoid it for readability, and FULL OUTER JOIN keeps unmatched rows from both sides.

7. What are transactions, and why are they important in databases?

A transaction groups multiple operations into one unit of work. Transactions matter because they let the database enforce atomicity, consistency, isolation, and durability for state changes that must either succeed together or fail together. Without transactions, complex business actions become vulnerable to partial writes and race conditions.

8. What is a foreign key, and how does it enforce referential integrity?

A foreign key declares that a value in one table must match a valid primary key or unique key in another table. It enforces referential integrity by preventing orphaned references, such as an order that points to a nonexistent customer. In production systems, foreign keys are valuable when you want the database, not just the application, to protect the integrity of relationships.

9. What is denormalization, and when would you use it?

Denormalization means intentionally duplicating or precomputing data to make reads faster or simpler. You use it when join-heavy or aggregation-heavy workloads become too expensive and the read-path benefit clearly outweighs the write-path complexity. The tradeoff is that duplicated data creates synchronization risk, so the source of truth must stay explicit.

10. What is a view, and how is it used?

A view is a named query that behaves like a virtual table. Teams use views to simplify repeated queries, hide complexity, or expose a controlled interface to underlying tables. A regular view does not usually store data itself; it stores the query definition.

Querying and Performance

11. How do you optimize SQL queries for performance?

Start with the execution plan instead of guessing. Check whether the query is scanning too much data, missing a selective index, sorting unnecessarily, or joining in the wrong order. The highest-leverage fixes are usually better indexes, narrower result sets, better predicates, and query rewrites that reduce work early.

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

A clustered index determines the physical order of table data, so the table itself is organized around that key. A non-clustered index is a separate structure that points back to the underlying rows. Clustered indexes are excellent for range scans on the clustering key, while non-clustered indexes are useful for additional access paths.

13. What is the difference between DELETE and TRUNCATE in SQL?

DELETE removes rows one by one and can be filtered with a WHERE clause. TRUNCATE removes all rows from a table much more efficiently, usually by deallocating storage or resetting metadata, and it typically cannot target a subset of rows. The exact transactional and logging behavior depends on the database engine, so it is worth answering with that caveat.

14. What is a materialized view, and how does it differ from a regular view?

A materialized view stores the result of a query physically, while a regular view stores only the query definition. Materialized views are useful for expensive aggregations or reporting workloads that do not need fresh results on every read. The tradeoff is refresh cost and data staleness between refreshes.

15. What is the role of a database cache, and how does it work?

Caching keeps frequently accessed data closer to the application to avoid repeated expensive database work. It can live inside the database buffer pool, inside the application, or in an external cache such as Redis. The key tradeoff is consistency: once you introduce a cache, you need a clear invalidation or expiry strategy.

16. What is a connection pool, and why is it important?

A connection pool reuses existing database connections instead of creating a new connection for every request. This improves performance because opening connections is expensive and databases can only handle a limited number of concurrent connections efficiently. A well-tuned pool protects both the application and the database from overload.

17. What are some best practices for database indexing?

Index columns used in selective filters, joins, and common sort paths. Avoid indexing every column because each extra index slows inserts, updates, and deletes. Good indexing is workload-specific: the correct question is not “should we add an index?” but “which queries become cheaper, and what write cost are we accepting?“

18. How do you implement full-text search in a database?

Use the database’s native full-text indexing features when the search scope is moderate and the relevance model is simple. For example, PostgreSQL supports text search with tokenization, stemming, and ranking. Once search becomes a core product capability with complex ranking, typo tolerance, or large-scale analytics, a dedicated engine such as Elasticsearch may be more appropriate.

19. How do you handle large-scale data imports and exports?

Use bulk operations instead of row-by-row application writes. Stage data, validate it, load in batches, disable or defer nonessential secondary work when safe, and monitor locks, log growth, and replication lag. For exports, chunk by key range or time window so the operation is restartable and does not monopolize the database.

20. How do you assess the performance and scalability of a database system?

Measure latency, throughput, error rate, CPU, memory, disk I/O, lock contention, and replication lag under realistic load. Then test whether the system still meets SLOs as data volume and concurrency grow. A senior answer should include both benchmarking and operational failure analysis, not just “run EXPLAIN.”

Transactions and Concurrency

21. How do you use transactions to ensure data integrity in a concurrent environment?

Wrap all related writes in one transaction and choose an isolation level that prevents the anomalies you care about. For example, inventory reservation often needs stronger guarantees than analytics writes. The correct answer always depends on the business invariant you are protecting.

22. What are the common causes of database deadlocks, and how do you prevent them?

Deadlocks happen when concurrent transactions acquire overlapping locks in conflicting orders. Common causes are long transactions, inconsistent update ordering, and touching more rows than expected due to poor query plans. Prevention usually means keeping transactions short, locking rows in a consistent order, and retrying deadlock victims safely.

23. What is eventual consistency, and how does it differ from strong consistency?

Strong consistency means a read after a successful write sees the latest committed value. Eventual consistency means replicas may temporarily diverge, but they converge if no new updates arrive. The engineering tradeoff is that eventual consistency improves availability or latency in distributed systems, but clients must tolerate stale reads or conflict resolution.

24. How do you use transactions to avoid lost updates?

Either serialize access with locking or detect conflicts with optimistic concurrency control. Common techniques include SELECT ... FOR UPDATE, version columns, or compare-and-swap style updates. The important point is to make concurrent writes visible to each other before commit.

25. What are isolation levels, and how do they affect behavior?

Isolation levels define what anomalies concurrent transactions may observe. Lower isolation allows more concurrency but may permit anomalies such as non-repeatable reads or phantoms, while stronger isolation reduces anomalies at the cost of more coordination. In interviews, mention that Serializable is the safest but not always the cheapest.

26. What are stored procedures, and what are their advantages and disadvantages?

Stored procedures are routines that run inside the database engine. They can reduce network round trips, centralize logic close to the data, and improve encapsulation for some workflows. The drawbacks are harder testing, vendor lock-in, and the risk of putting too much business logic in a place with weaker engineering ergonomics than application code.

27. What is a trigger, and how is it used?

A trigger is code that runs automatically in response to table events such as insert, update, or delete. Triggers can be useful for audit logging, derived data maintenance, or enforcing rules that must never be bypassed. The downside is hidden behavior: when overused, they make write paths harder to reason about and debug.

28. What is optimistic concurrency control, and when is it useful?

Optimistic concurrency control assumes conflicts are relatively rare and detects them at commit or update time. It works well for systems with many reads and fewer conflicting writes because it avoids heavy locking on the hot path. The tradeoff is that conflicting writers must retry.

29. What is pessimistic locking, and when is it useful?

Pessimistic locking acquires locks early to prevent other transactions from making conflicting changes. It is useful when contention is expected and the cost of conflict is high, such as financial transfers or scarce inventory reservation. The tradeoff is lower concurrency and higher risk of blocking or deadlocks.

30. Why should transactions usually be short?

Long transactions hold locks longer, retain more undo or MVCC state, increase deadlock risk, and delay cleanup. They also amplify the blast radius of failures because more work must be rolled back. A practical rule is to keep user interaction, remote API calls, and slow background work outside the transaction boundary whenever possible.

Operations and Reliability

31. How do you handle database migrations?

Treat migrations as versioned, reviewable, testable changes that can be rolled forward safely. Split risky work into phases: add new columns or tables first, backfill asynchronously, switch reads and writes, then remove old structures later. The main goal is to avoid large blocking changes in production.

32. How do you handle schema changes in a live production database?

Use backward-compatible changes first. Additive changes are easiest; destructive changes should wait until all application instances no longer depend on old columns or formats. For large tables, avoid full-table rewrites during peak traffic and use batched backfills or online DDL features where available.

33. How do you manage database versions and change control?

Keep schema changes in source control, tie them to application versions, and ensure every environment can be reproduced deterministically. Mature teams use migration tooling, review checklists, staging validation, and rollback or roll-forward procedures. Schema changes should be treated with the same discipline as code deployments.

34. How do you handle backups and restore operations?

Backups are only useful if restore works. Take regular backups, validate them, define retention, and rehearse restore procedures against realistic scenarios such as point-in-time recovery or region failure. A strong answer mentions both backup strategy and recovery time objectives.

35. What is replication, and what are its benefits?

Replication copies data from one node to one or more other nodes. It is used for high availability, read scaling, disaster recovery, and maintenance flexibility. The main tradeoffs are replication lag, failover complexity, and consistency semantics during outages.

36. What is the difference between synchronous and asynchronous replication?

Synchronous replication waits for one or more replicas to confirm a write before acknowledging success, which improves durability and consistency but increases latency. Asynchronous replication lets the primary acknowledge writes immediately and ship them to replicas later, which improves latency but risks lag or data loss during failover. The right choice depends on the cost of stale or missing data.

37. How do you ensure high availability in a database system?

Use redundancy, health checks, failover procedures, and operational simplicity. In practice that means replica topology, automated failover where justified, tested backup/restore, capacity headroom, and clear failure playbooks. High availability is not just a topology decision; it is also an operational discipline.

38. What tools do you use for database monitoring and profiling?

Use engine-native metrics plus centralized observability. Typical signals include query latency, top queries, lock waits, deadlocks, cache hit rate, connection usage, replication lag, disk growth, and slow query logs. The best tooling is the one that lets you move quickly from symptom to root cause.

39. How do you implement audit trails and logging in a database?

Capture who changed what, when, and ideally why. This can be done with append-only audit tables, change-data-capture pipelines, or application-level event logs, depending on requirements. For regulated systems, make the audit trail immutable or at least tamper-evident.

40. What is the role of a database administrator, and how does it differ from a database developer?

A DBA focuses on reliability, capacity, backups, security, performance, and operational health. A database developer focuses more on schema design, queries, migrations, and application-data interaction. In smaller teams one person may cover both, but the mindsets are different: one optimizes the platform, the other optimizes product behavior on top of it.

Distributed Systems and Scale

41. What is database sharding, and when should it be used?

Sharding splits data across multiple database instances so one machine does not have to store or serve everything. Use it when a single node can no longer handle storage, throughput, or operational isolation needs. Sharding increases complexity significantly, so it should usually come after schema and query optimization, caching, and read scaling.

42. How do you ensure data consistency across distributed systems?

Start by reducing how often you need cross-service distributed coordination at all. Then use patterns such as idempotency keys, outbox/inbox, event-driven workflows, sagas, and explicit reconciliation jobs. The important staff-level answer is that distributed consistency is usually achieved with workflow design, not with pretending the network behaves like one transaction manager.

43. What is the CAP theorem, and how does it relate to distributed databases?

CAP says that when a network partition occurs, a distributed system must choose between strong consistency and availability. In practice, serious systems are always partition-tolerant because networks fail, so the real question is what the database does during the partition. Interviewers usually want to see that you understand the theorem as a failure-mode tradeoff, not a marketing label.

44. What are the trade-offs between consistency and availability in distributed systems?

If you favor consistency, some requests may fail or block during coordination problems, but successful reads stay fresh. If you favor availability, the system continues responding, but some reads may be stale and conflicting writes may need reconciliation. The correct choice depends on product semantics: payments and locks lean one way, feeds and caches often lean the other.

45. What are the differences between horizontal and vertical scaling in databases?

Vertical scaling means making one machine bigger: more CPU, memory, or faster storage. Horizontal scaling means distributing load or data across multiple machines. Vertical scaling is simpler operationally, while horizontal scaling offers a higher long-term ceiling at the cost of significantly more system complexity.

46. How do you design a database for multi-tenant applications?

Common models are shared-table, shared-schema, or separate-database per tenant. Shared-table is operationally simple and cost-efficient but needs strong tenant isolation in queries and indexes; separate databases offer stronger isolation but increase operational overhead. The right design depends on tenant size variance, compliance needs, and noisy-neighbor risk.

47. How do you handle time-series data in a database?

Model around timestamp-based writes, range queries, retention, and downsampling. Time-series workloads benefit from partitioning by time window, append-friendly storage, and policies for compression or cold storage. The main anti-pattern is treating large event streams like ordinary OLTP rows without a lifecycle plan.

48. What are the considerations for selecting a database technology for a new project?

Choose based on access patterns, consistency requirements, scale expectations, operational maturity, team expertise, and failure tolerance. Also consider ecosystem strength, backup/restore tooling, observability, and how hard it will be to migrate later. Senior engineers usually optimize for correctness and operability before novelty.

Security and Governance

49. How do you manage database security and access control?

Use least-privilege access, separate roles for read/write/admin operations, strong authentication, encryption in transit and at rest, secret rotation, and audit logging. Also limit network exposure and treat production data access as a controlled operational path, not a developer convenience. Good security is mostly about reducing unnecessary power and making sensitive actions visible.

50. How do you implement role-based access control and constraints to enforce integrity?

Role-based access control assigns privileges to roles and roles to users or services, which is easier to manage than granting permissions individually. Constraints such as NOT NULL, UNIQUE, CHECK, PRIMARY KEY, and FOREIGN KEY enforce invariants directly in the database. The strongest systems combine both: access control prevents unauthorized actions, and constraints prevent invalid state even from authorized code.

Closing View

If you are preparing for interviews, do not memorize these answers word for word. Practice explaining each topic with one concrete example from systems you have actually built or operated.

That is what usually separates a candidate who knows database vocabulary from a candidate who can be trusted with production data.