DBMS Interview Questions
DBMS is a core topic in almost every software and data-engineering interview. These are the questions interviewers actually ask, grouped by theme and tagged by experience level.
125 questions with concise, interview-ready answers.
Contents
DBMS Fundamentals
1. What is a DBMS, and how does it differ from an RDBMS? Fresher
A DBMS (Database Management System) is software that lets you store, retrieve, and manage data in a database. An RDBMS (Relational DBMS) is a type of DBMS that stores data in tables (relations) made of rows and columns and enforces relationships between them using keys. Every RDBMS is a DBMS, but a plain DBMS need not be relational. Examples of RDBMS include MySQL, PostgreSQL, Oracle, and SQL Server.
2. How is a DBMS better than a traditional file system? Fresher
A file system stores raw data in files with no built-in relationships, leading to redundancy, inconsistency, and difficult concurrent access. A DBMS removes redundancy through normalization, enforces data integrity with constraints, supports multi-user concurrent access with locking, provides transactions, and offers powerful querying with SQL. It also handles security, backup, and recovery, which file systems leave to the application.
3. What is a relation, tuple, attribute, degree and cardinality? Fresher
A relation is a table. A tuple is one row of that table, and an attribute is one column. The degree of a relation is its number of attributes, while the cardinality is its number of tuples. Interviewers use the formal vocabulary to check you know the relational model as a model, not just as SQL syntax, so it is worth being able to switch between the two sets of terms.
4. What are the three levels of the ANSI-SPARC database architecture? 2–5 yrs
The internal (physical) level describes how data is actually stored: files, pages, indexes and access paths. The conceptual (logical) level describes the whole database as tables, relationships and constraints, independent of storage. The external (view) level presents each user group only the subset it needs. The point of the split is that a change at one level does not force a change at the level above it.
5. What is data independence, and what is the difference between logical and physical? 2–5 yrs
Data independence is the ability to change one level of the schema without changing the level above. Physical data independence means you can add an index, change file organisation or move to different storage without touching the logical schema or any application. Logical data independence means you can add a column or split a table without breaking existing user views, which is harder to achieve and is why views matter so much.
6. What is the difference between a schema and an instance? Fresher
The schema is the structural definition of the database: the tables, columns, data types and constraints. It is set at design time and changes rarely. An instance is the actual data held in the database at a particular moment, which changes with every insert, update and delete. The usual analogy is that the schema is the class and the instance is the object.
7. What is a data dictionary or metadata in a DBMS? Fresher
Metadata is data about the data: table and column definitions, data types, constraints, indexes, users and privileges, and statistics. The DBMS stores it in a data dictionary or system catalog, which is itself usually a set of system tables you can query, such as information_schema in MySQL and PostgreSQL. The query optimiser depends on the statistics held there, which is why stale statistics produce bad execution plans.
8. What are the main advantages and disadvantages of using a DBMS? Fresher
Advantages are controlled redundancy, enforced integrity and constraints, concurrent multi-user access, transactions with recovery from failure, security and access control, and a declarative query language. Disadvantages are cost of software and hardware, the complexity of installing and tuning it, the specialised skills it needs, and the fact that it becomes a single critical dependency for the whole application. For a small single-user dataset a file or an embedded database is often the right answer.
9. What is a DBA and what do they do? Fresher
A database administrator owns the operational health of the database: schema and storage design, user accounts and privileges, backups and recovery testing, performance monitoring and tuning, capacity planning, and upgrades. They are the ones who decide indexing strategy, review slow queries, and rehearse the restore procedure before it is needed. In smaller teams the responsibilities are shared with backend engineers, but the checklist stays the same.
10. What is the difference between a database and a data warehouse? 2–5 yrs
An operational database is designed for many small, concurrent reads and writes, is highly normalised, and holds current data. A data warehouse is designed for analytical queries over historical data, is deliberately denormalised into star or snowflake schemas, is loaded in batches by ETL, and is optimised for scanning large volumes rather than for single-row updates. Running heavy analytics on a production database is the mistake the warehouse exists to prevent.
11. What is the difference between OLTP and OLAP? 2–5 yrs
OLTP (Online Transaction Processing) systems handle high volumes of short transactions such as placing an order; they need low latency, high concurrency and strict consistency, and they are normalised. OLAP (Online Analytical Processing) systems run few but very large aggregate queries across historical data; they need throughput and scan efficiency, and they are denormalised and often column-oriented. The workloads conflict, which is why they are usually separated onto different systems.
12. What is a column-oriented database and when is it better? 2–5 yrs
A row store keeps all the columns of a row together, which is efficient when you read or write whole rows, as OLTP does. A column store keeps each column contiguously, so a query touching three columns of a hundred-column table reads only those three, and the similar values within a column compress extremely well. That makes column stores far better for analytical scans and aggregation, and worse for single-row inserts and updates.
Normalisation, Anomalies & Denormalisation
13. What is normalization, and why is it used? Fresher
Normalization is the process of organizing columns and tables to reduce data redundancy and avoid update, insert, and delete anomalies. It splits large tables into smaller related ones and links them with keys. The result is cleaner, more consistent data that is easier to maintain. It is achieved by applying a series of normal forms.
14. Explain 1NF, 2NF, 3NF, and BCNF. Fresher
1NF requires every column to hold atomic (indivisible) values with no repeating groups. 2NF is 1NF plus no partial dependency — every non-key column must depend on the whole primary key, not just part of it. 3NF is 2NF plus no transitive dependency — non-key columns must not depend on other non-key columns. BCNF (Boyce-Codd Normal Form) is a stricter 3NF where, for every functional dependency, the left side must be a super key.
15. What is denormalization, and when would you use it? 2–5 yrs
Denormalization deliberately adds redundancy back into a normalized database by combining tables or duplicating columns. It is used to improve read performance by reducing the number of joins needed for frequent queries. The trade-off is more storage and the burden of keeping duplicated data consistent on writes. It is common in reporting, analytics, and read-heavy systems.
16. What are insertion, update and deletion anomalies? Fresher
They are the three problems that redundancy causes. An insertion anomaly is when you cannot record one fact without inventing another, such as being unable to add a new department until it has at least one employee. An update anomaly is when a fact stored in many rows is changed in some but not all, leaving contradictory data. A deletion anomaly is when removing one row destroys unrelated information, such as losing the department entirely when its last employee leaves.
17. What is a functional dependency? 2–5 yrs
A functional dependency X to Y means that for any two rows with the same value of X, the value of Y must also be the same; X determines Y. For example student_id determines student_name. Functional dependencies are the formal tool normalisation is built on: every normal form is defined in terms of which dependencies are allowed. They come from the meaning of the data, not from the rows that happen to be in the table today.
18. What is a partial dependency and a transitive dependency? 2–5 yrs
A partial dependency is when a non-key attribute depends on only part of a composite primary key, which is what 2NF forbids; in an (order_id, product_id) table, storing order_date is partial because it depends on order_id alone. A transitive dependency is when a non-key attribute depends on another non-key attribute, which is what 3NF forbids; storing department_name next to department_id in an employee table is transitive. Both are just redundancy with a formal name.
19. What is the difference between 3NF and BCNF, with an example? Senior
3NF allows a functional dependency X to Y if Y is a prime attribute, that is part of some candidate key. BCNF removes that exemption and requires X to be a super key for every non-trivial dependency. The classic example is a table (student, subject, teacher) where each teacher teaches exactly one subject: it is in 3NF because subject is prime, but teacher determines subject and teacher is not a key, so it is not in BCNF. Decomposing fixes the redundancy but can lose dependency preservation, which is why BCNF is not always adopted.
20. What is a multivalued dependency, and what are 4NF and 5NF? Senior
A multivalued dependency exists when one attribute determines a set of values for another independently of the rest of the table, which forces you to store every combination. 4NF removes these: a relation is in 4NF if it is in BCNF and has no non-trivial multivalued dependency other than on a super key, typically fixed by splitting the two independent facts into separate tables. 5NF (project-join normal form) goes further and removes join dependencies that are not implied by candidate keys. Both are rare in practice but common in exams.
21. What is a lossless-join decomposition, and what is dependency preservation? Senior
A decomposition is lossless if joining the resulting tables reproduces exactly the original rows with nothing spurious added; the sufficient condition is that the shared attributes form a key of at least one of the pieces. Dependency preservation means every original functional dependency can still be enforced on a single table without a join. Lossless join is non-negotiable, because losing it means losing information; dependency preservation is desirable but sometimes sacrificed to reach BCNF.
22. What is attribute closure and why is it useful? Senior
The closure of a set of attributes is every attribute that can be determined from it by repeatedly applying the functional dependencies, using Armstrong axioms: reflexivity, augmentation and transitivity. It is the practical tool for exam questions: if the closure of a set includes every attribute of the relation, that set is a super key, and if no proper subset also does, it is a candidate key. It is also how you check whether a given dependency is implied by the others.
23. Is it always right to normalise to the highest normal form? 2–5 yrs
No. Normalisation optimises for write consistency and storage, but every split adds a join, and joins cost time at read. Third normal form is the usual practical stopping point for transactional systems. Reporting and analytical schemas are deliberately denormalised, and even in OLTP a carefully chosen duplicated column (a cached count, a materialised total) is a legitimate trade-off provided you own the job of keeping it consistent.
24. What is a spurious tuple and how does it arise? 2–5 yrs
A spurious tuple is a row that appears when you join decomposed tables back together but did not exist in the original relation, so the database now asserts something false. It arises from a bad decomposition where the shared attribute is not a key of either piece, so rows match up in combinations that were never real. This is exactly what the lossless-join property prevents, and it is the reason you cannot split a table on any arbitrary column.
Transactions & ACID Properties
25. What are the ACID properties of a transaction? Fresher
ACID stands for Atomicity, Consistency, Isolation, and Durability. Atomicity means a transaction runs fully or not at all. Consistency means it moves the database from one valid state to another, respecting all constraints. Isolation means concurrent transactions do not interfere with each other. Durability means once a transaction commits, its changes survive even a system crash.
26. What are the states of a transaction? Fresher
A transaction starts in the active state while its operations execute. When the last operation finishes it moves to partially committed, then to committed once the changes are safely written to durable storage. If something fails it moves to the failed state and then to aborted after rollback, at which point the system either restarts it or kills it. Drawing this state diagram is a standard exam question and it makes the difference between partially committed and committed easy to explain.
27. How is atomicity actually implemented? 2–5 yrs
Through logging. Before a change is applied, the DBMS writes a log record containing enough information to undo it, and it writes that log record to durable storage before the data page is written, which is the write-ahead rule. If the transaction aborts or the system crashes mid-transaction, recovery walks the log backwards and applies the undo records, so partial effects disappear. Commit is the single atomic act of writing the commit log record.
28. What is a schedule, and what makes one serializable? 2–5 yrs
A schedule is a specific interleaving of the operations of several concurrent transactions. It is serial if transactions run one after another with no interleaving, which is correct but slow. It is serializable if, despite the interleaving, its final effect is identical to some serial order, which is the correctness criterion concurrency control aims for. Serializability, not the specific order, is what matters.
29. What is the difference between conflict serializability and view serializability? Senior
Two operations conflict if they belong to different transactions, touch the same item, and at least one is a write. A schedule is conflict serializable if it can be turned into a serial schedule by swapping non-conflicting adjacent operations, which you test by checking the precedence graph for a cycle. View serializability is a weaker, broader condition based on which transaction read which value and who wrote the final value; every conflict serializable schedule is view serializable but not the reverse. Practical systems enforce conflict serializability because testing view serializability is NP-complete.
30. What is a recoverable schedule and a cascadeless schedule? Senior
A schedule is recoverable if a transaction commits only after every transaction whose data it read has committed; otherwise you could have a committed transaction that read a value which was later rolled back, and there is no way to fix it. A cascadeless schedule goes further and forbids reading uncommitted data at all, which eliminates cascading rollbacks. Strict schedules are stricter still, forbidding both reading and overwriting uncommitted data, and they are what strict two-phase locking produces.
31. What is a cascading rollback? 2–5 yrs
If transaction B reads a value written by uncommitted transaction A, and A then aborts, B has read a value that never officially existed, so B must be rolled back too, and anything that read from B after that, and so on. That chain is a cascading rollback, and it can undo a large amount of committed-looking work. It is prevented by not allowing dirty reads, which is why almost every real system runs at read committed or higher.
32. What is the two-phase commit protocol? Senior
Two-phase commit coordinates an atomic commit across several databases or services. In the prepare phase a coordinator asks every participant whether it can commit, and each replies yes only after making its changes durable and promising not to abort. In the commit phase, if all voted yes the coordinator tells everyone to commit, otherwise it tells everyone to abort. Its weakness is blocking: if the coordinator dies after the vote, participants hold locks indefinitely, which is why distributed systems often prefer sagas or idempotent retries instead.
Keys & the Relational Model
33. What is the difference between a primary key, a unique key, and a foreign key? Fresher
A primary key uniquely identifies each row and cannot contain NULLs; a table has exactly one. A unique key also enforces uniqueness but allows one NULL (depending on the database) and a table can have several. A foreign key is a column that references the primary key of another table to enforce referential integrity between them.
34. What is the difference between a candidate key and a super key? Fresher
A super key is any set of one or more columns that can uniquely identify a row in a table. A candidate key is a minimal super key — one with no redundant columns, so removing any column would break uniqueness. Every candidate key is a super key, but not every super key is a candidate key. The primary key is chosen from among the candidate keys.
35. What is a composite key? Fresher
A composite key is a primary key made of two or more columns, used when no single column uniquely identifies a row. A typical example is an order line table keyed on (order_id, product_id). The combination must be unique and no part may be NULL. Composite keys are also where partial dependencies come from, which is exactly what second normal form addresses.
36. What is an alternate key? Fresher
A table can have several candidate keys, each capable of uniquely identifying a row. One is chosen as the primary key, and the remaining candidate keys are called alternate keys. In an employee table, employee_id, national_insurance_number and work_email might all be candidates; picking employee_id makes the other two alternate keys, which you would normally still enforce with unique constraints.
37. What is a surrogate key and when should you use one? 2–5 yrs
A surrogate key is a system-generated identifier with no business meaning, such as an auto-increment integer or a UUID. You use one when the natural key is large, composite, or liable to change, because a natural key that changes forces cascading updates through every table that references it. The trade-off is an extra column and an extra lookup when you only have the business identifier. Note that random UUIDs as a clustered primary key cause page splits and index bloat, which is why ordered UUIDs exist.
38. What is entity integrity and referential integrity? Fresher
Entity integrity says every table must have a primary key and no part of it may be NULL, so every row is uniquely identifiable. Referential integrity says a foreign key value must either match an existing primary key in the referenced table or be NULL, so you cannot have an order pointing at a customer who does not exist. Together they are the two structural integrity rules of the relational model.
39. What happens to child rows when a referenced row is deleted? 2–5 yrs
It depends on the referential action declared on the foreign key. RESTRICT or NO ACTION blocks the delete while children exist, which is the safe default. CASCADE deletes the children too, which is right for genuinely owned data such as order lines but dangerous when it silently removes large amounts of history. SET NULL and SET DEFAULT keep the child row but clear the reference, which requires the column to be nullable or to have a default.
40. Can a foreign key be NULL, and can it reference the same table? 2–5 yrs
Yes to both. A NULL foreign key simply means the relationship is optional, such as an employee with no assigned manager, and referential integrity is not violated because there is nothing to check. A self-referencing foreign key points at the primary key of the same table, which is how you model hierarchies such as manager and employee or a category tree. Self-references need care on delete and are queried with recursive CTEs.
41. Can a table exist without a primary key, and should it? 2–5 yrs
Technically yes in most engines, but it is almost always a mistake. Without one you cannot reliably identify or update a single row, duplicates creep in, replication tools that need a row identity break, and in InnoDB a hidden internal key is created anyway. The rare defensible cases are append-only log or staging tables where rows are never updated individually. Even then, adding a surrogate key costs little and saves a lot later.
42. What is a NULL and how does it behave in comparisons? Fresher
NULL means unknown or not applicable; it is not zero and not an empty string. Any comparison with NULL yields unknown rather than true or false, so a WHERE clause never matches a row on column = NULL; you must use IS NULL. It also affects aggregates, which skip NULLs, so COUNT(column) and COUNT(*) can differ, and it affects uniqueness, since most engines allow multiple NULLs in a unique column because two unknowns are not proven equal.
43. What is a constraint, and what types are there in SQL? Fresher
Constraints are rules the database itself enforces so bad data cannot get in regardless of which application writes it. NOT NULL requires a value, UNIQUE forbids duplicates, PRIMARY KEY combines the two and identifies the row, FOREIGN KEY enforces referential integrity, CHECK enforces an arbitrary condition such as a positive price, and DEFAULT supplies a value when none is given. Enforcing these in the database rather than only in application code is what stops a second service or a manual script corrupting the data.
Joins, Relational Algebra & SQL Commands
44. What are the different types of joins in SQL? Fresher
An INNER JOIN returns only the rows that match in both tables. A LEFT (OUTER) JOIN returns all rows from the left table plus matching rows from the right, with NULLs where there is no match. A RIGHT (OUTER) JOIN does the reverse, keeping all right-table rows. A FULL OUTER JOIN returns all rows from both tables, matched where possible and NULL-filled otherwise.
45. What is the difference between DDL, DML, DCL, and TCL? Fresher
DDL (Data Definition Language) defines schema — CREATE, ALTER, DROP, TRUNCATE. DML (Data Manipulation Language) works with the data itself — SELECT, INSERT, UPDATE, DELETE. DCL (Data Control Language) manages permissions — GRANT and REVOKE. TCL (Transaction Control Language) manages transactions — COMMIT, ROLLBACK, and SAVEPOINT.
46. What is a cross join or Cartesian product? Fresher
A cross join pairs every row of the first table with every row of the second, producing m times n rows and no matching condition. It is occasionally deliberate, for generating combinations or a date scaffold, but far more often it is an accident caused by forgetting the join condition in an old-style comma join. If a query suddenly returns a vastly inflated row count, an unintended Cartesian product is the first thing to check.
47. What is a self join and when is it used? 2–5 yrs
A self join joins a table to itself using two aliases, so rows in one table can be matched against other rows in the same table. It is the standard way to walk a hierarchy stored with a parent reference, such as pairing each employee with their manager, and to compare rows against each other, such as finding pairs of products with the same price. For arbitrary-depth hierarchies you need a recursive CTE rather than a single self join.
48. What is a natural join and why is it usually avoided? 2–5 yrs
A natural join automatically joins on every pair of columns with the same name in both tables and returns each shared column once. It is avoided in production because it depends on column naming rather than an explicit condition, so adding a column such as created_at to both tables silently changes the join and quietly breaks the query. An explicit INNER JOIN with an ON clause is safer and self-documenting.
49. What is an equi join and a non-equi join? 2–5 yrs
An equi join matches rows using an equality condition, which is by far the most common case and the only kind a hash join can perform. A non-equi join uses any other comparison, such as joining a transaction to a rate table where the amount falls BETWEEN a lower and upper bound. Non-equi joins are legitimate but far more expensive, because the optimiser is usually left with a nested loop or a merge over ranges.
50. What are the nested loop, hash join and sort-merge join algorithms? Senior
Nested loop takes each row of the outer table and probes the inner one, which is excellent when the outer input is small and the inner side has an index, and terrible otherwise. Hash join builds a hash table on the smaller input and probes it with the larger, which is the fastest option for large unindexed equality joins provided the build side fits in memory. Sort-merge sorts both inputs on the join key and walks them together, which wins when the inputs are already sorted or the join is a range. The optimiser picks between them using table statistics.
51. What is the difference between WHERE and HAVING? Fresher
WHERE filters individual rows before grouping and aggregation, so it cannot reference an aggregate. HAVING filters groups after GROUP BY has produced them, so it can. The practical rule is to push every condition you can into WHERE, because filtering earlier means fewer rows to group, and only conditions on aggregates such as HAVING COUNT(*) > 5 belong in HAVING.
52. What is the difference between DELETE, TRUNCATE and DROP? Fresher
DELETE is DML: it removes rows one at a time, can have a WHERE clause, fires triggers, is logged per row and can be rolled back. TRUNCATE is DDL: it removes all rows by deallocating the pages, is far faster, does not fire row triggers, usually resets the identity counter and in many engines cannot be rolled back. DROP is DDL that removes the table definition itself along with its data, indexes and constraints.
53. What is the difference between UNION and UNION ALL? Fresher
Both stack the results of two queries that have compatible columns. UNION removes duplicate rows, which requires a sort or hash over the whole result and therefore costs time. UNION ALL keeps everything as-is and is significantly faster. Use UNION ALL by default and only reach for UNION when duplicates are genuinely possible and genuinely unwanted.
54. What are aggregate functions and how does GROUP BY work? Fresher
Aggregate functions such as COUNT, SUM, AVG, MIN and MAX collapse many rows into one value. GROUP BY splits the rows into groups by the listed columns and applies the aggregate to each group, returning one row per group. Every non-aggregated column in the select list must appear in the GROUP BY, and all aggregates except COUNT(*) ignore NULLs, which is why AVG over a column with NULLs is not the same as summing and dividing by the row count.
55. What is a subquery, and what is a correlated subquery? 2–5 yrs
A plain subquery is evaluated once and its result used by the outer query, so it can often be rewritten as a join. A correlated subquery references a column from the outer query, so conceptually it is re-evaluated for each outer row, which can be very slow on large tables. EXISTS with a correlated subquery is usually fine because it stops at the first match, whereas an IN over a large uncorrelated subquery is often better written as a join or a semi-join.
56. What is relational algebra and what are its basic operators? 2–5 yrs
Relational algebra is the formal procedural language underlying SQL, operating on relations and returning relations. The fundamental operators are selection (filter rows), projection (choose columns), union, set difference, Cartesian product and rename. Everything else, including intersection and every kind of join, is derived from those. It matters in interviews because it is how the query optimiser thinks: SQL is translated into an algebra tree and then rewritten into an equivalent, cheaper tree.
Transaction Control, Isolation Levels & Anomalies
57. What is a transaction in a database? Fresher
A transaction is a single logical unit of work made up of one or more SQL statements that must succeed or fail together. It is the mechanism that enforces the ACID properties. You start it, perform operations, and then either COMMIT to make changes permanent or ROLLBACK to undo them. A classic example is transferring money, where the debit and credit must both happen or neither does.
58. What is the difference between COMMIT, ROLLBACK and SAVEPOINT? Fresher
COMMIT ends the transaction and makes every change permanent and visible to others, releasing its locks. ROLLBACK ends it by undoing every change since it began, leaving the database as if it never ran. SAVEPOINT marks a named point inside a transaction so you can roll back to it and continue rather than discarding all the work, which is useful when one optional step in a long transaction fails.
59. What is autocommit? Fresher
With autocommit on, which is the default in most clients, every individual statement is wrapped in its own transaction and committed immediately. That is convenient but means a multi-statement operation has no atomicity, so a failure halfway leaves the database in a partial state. To get a real transaction you must disable autocommit or explicitly BEGIN. It also hurts performance for bulk loads, because each row pays a separate commit and flush.
60. What are the four SQL isolation levels? 2–5 yrs
From weakest to strongest: READ UNCOMMITTED allows reading uncommitted data; READ COMMITTED only ever reads committed data, which is the default in PostgreSQL, Oracle and SQL Server; REPEATABLE READ additionally guarantees that a row re-read within the transaction is unchanged, and is the default in MySQL InnoDB; SERIALIZABLE guarantees the result is equivalent to running the transactions one at a time. Each step up prevents more anomalies and reduces concurrency.
61. What are dirty reads, non-repeatable reads and phantom reads, and which level prevents each? 2–5 yrs
A dirty read is reading data another transaction has written but not committed, prevented from READ COMMITTED upward. A non-repeatable read is reading the same row twice and getting different values because another transaction updated and committed in between, prevented from REPEATABLE READ upward. A phantom read is re-running the same range query and getting extra rows because another transaction inserted matching ones, prevented only at SERIALIZABLE by the standard, though InnoDB blocks it at REPEATABLE READ using next-key locks.
62. What is a lost update? 2–5 yrs
A lost update happens when two transactions read the same value, each computes a new value from it, and both write back, so the second silently overwrites the first; a classic case is two users incrementing the same counter. It is not one of the three standard anomalies but it is what actually bites in production. The fixes are an atomic update expressed in SQL such as SET balance = balance - 100, a SELECT FOR UPDATE lock, or an optimistic version column checked in the WHERE clause.
63. What is the trade-off of running at a higher isolation level? 2–5 yrs
Stronger isolation means more locks held for longer, or more version conflicts under MVCC, so throughput drops, lock waits and deadlocks rise, and retries become part of normal operation. Weaker isolation gives more concurrency but exposes anomalies your application must then handle itself. The right answer is usually the weakest level that is correct for the specific operation, with targeted locking or an atomic statement where a particular piece of logic needs more.
64. What is snapshot isolation and what is write skew? Senior
Snapshot isolation gives each transaction a consistent view of the database as of its start time, so readers never block writers, and it is what most MVCC engines mean by REPEATABLE READ. It prevents dirty, non-repeatable and phantom reads, but it is not serializable. Write skew is the gap: two transactions read overlapping data, each checks a constraint that still holds in its own snapshot, and both write disjoint rows that together violate it, such as two doctors both cancelling their on-call shift. Fixing it needs SERIALIZABLE or an explicit lock on the read set.
Indexing & Query Performance
65. What is an index, and how does it speed up queries? Fresher
An index is a separate data structure, usually a B-tree, that stores sorted key values along with pointers to the matching rows. Instead of scanning every row (a full table scan), the database can look up values quickly, much like an index at the back of a book. This dramatically speeds up SELECT, WHERE, JOIN, and ORDER BY operations. The trade-off is slower writes and extra storage, since indexes must be updated when data changes.
66. What is the difference between a clustered and a non-clustered index? Fresher
A clustered index determines the physical order in which rows are stored on disk, so a table can have only one. A non-clustered index is a separate structure that stores key values with pointers (row locators) back to the actual data, and a table can have many. Clustered indexes are faster for range queries, while non-clustered indexes are flexible for looking up specific columns.
67. Why do databases use B+ trees rather than binary search trees or hash tables? 2–5 yrs
A B+ tree is wide and shallow, so a node holds hundreds of keys and fits one disk page; even a very large table is three or four levels deep, meaning a lookup costs only a handful of page reads. A binary tree with the same row count would be dozens of levels deep and cost a read at each. B+ trees also chain their leaf nodes, so range scans and ORDER BY are sequential reads. Hash indexes beat them for single-value equality but cannot do ranges or sorted output at all.
68. What is a hash index and when is it better than a B-tree? 2–5 yrs
A hash index hashes the key to a bucket, giving average constant-time lookup for an exact match, which can beat a B-tree for high-volume equality lookups. Its limitation is that it supports nothing else: no range queries, no prefix matching, no sorted retrieval, and no help for ORDER BY. It also degrades badly with collisions and is expensive to resize. That narrowness is why B-trees remain the default for almost every index.
69. What is a composite index and why does column order matter? 2–5 yrs
A composite index is built on several columns in a defined order, and it is sorted by the first column, then the second within that, and so on. That means it can serve queries filtering on a leading prefix of the columns, but not one that filters only on a later column, which is the leftmost-prefix rule. So an index on (customer_id, order_date) helps a query on customer_id alone and on both, but not one on order_date alone. Put the column used for equality first and the range or sort column after it.
70. What is a covering index? Senior
A covering index contains every column a query needs, in the index itself, so the database can answer the query from the index alone and never touch the table. Removing that second lookup, sometimes called a bookmark lookup or heap fetch, can be an order of magnitude faster on a wide table. Engines support it either by adding columns to the key or by an INCLUDE clause that stores them only in the leaf. The cost is a wider index that is slower to maintain and takes more space.
71. What is index selectivity or cardinality, and why does it matter? Senior
Selectivity is the fraction of rows a predicate eliminates; high selectivity means few rows match. An index on a column with very few distinct values, such as a boolean flag, is low-cardinality and mostly useless, because reading the index and then fetching half the table costs more than scanning the table once. The optimiser uses the statistics it holds about distinct values and distribution to make exactly this decision, which is why out-of-date statistics cause it to choose badly.
72. When can adding an index make things worse? 2–5 yrs
Every index must be updated on insert, update and delete, so write-heavy tables pay for each one. Indexes consume storage and memory in the buffer pool, evicting pages that would have been more useful. Low-selectivity indexes are chosen and then abandoned, or worse, chosen and slow. Redundant indexes, such as one on (a) when (a, b) already exists, are pure cost. The habit worth showing in an interview is auditing unused indexes, not just adding new ones.
73. Why does applying a function to an indexed column stop the index being used? 2–5 yrs
The index stores the raw column values in sorted order, but WHERE YEAR(created_at) = 2026 asks about a computed value the index knows nothing about, so the engine must evaluate the function for every row. The predicate is then non-sargable. The fix is to rewrite it as a range against the raw column, such as created_at >= a date AND created_at < the next one, or to create an expression or functional index on the computed value itself. The same applies to implicit type conversion and to a leading wildcard in LIKE.
74. What is a full table scan and when does the optimiser prefer one? 2–5 yrs
A full table scan reads every row rather than using an index. The optimiser chooses it when the table is small, when the query returns a large fraction of the rows, when no usable index exists, or when the predicate is non-sargable. It is not automatically a problem: reading 60 percent of a table sequentially is much cheaper than 60 percent of random index lookups plus row fetches. It is a problem when it appears on a large table for a query that should have been selective.
75. What is a query execution plan and how do you read one? 2–5 yrs
The plan is the optimiser chosen strategy for a query, shown by EXPLAIN or EXPLAIN ANALYZE, as a tree of operations executed from the leaves upward. You read it for the access method on each table (index seek versus full scan), the join algorithm and order, and above all the gap between estimated and actual row counts, since a large discrepancy points at stale statistics and explains a bad plan. Also look for sorts and hash operations spilling to disk.
76. What does the query optimiser do? 2–5 yrs
It takes the parsed, declarative query and produces an efficient physical plan. It rewrites the query into an equivalent form, pushing predicates down so filtering happens early, flattening subqueries and eliminating unneeded joins. It then enumerates candidate plans, choosing access paths, join algorithms and join order, and estimates the cost of each using catalog statistics on table size, cardinality and value distribution. It picks the cheapest estimate, which is why the whole thing depends on the statistics being current.
77. What is the difference between a cost-based and a rule-based optimiser? Senior
A rule-based optimiser applies a fixed ranked list of heuristics, such as always preferring an index over a scan, without looking at the data. It is predictable but frequently wrong, because the best plan depends on how many rows there actually are. A cost-based optimiser estimates the I/O and CPU cost of alternative plans from statistics and picks the cheapest, which is what every modern database uses. Its weakness is that bad or missing statistics produce confidently bad plans.
78. What is a bitmap index and when is it appropriate? Senior
A bitmap index stores, for each distinct value, a bitmap with one bit per row indicating whether that row has the value. It is extremely compact for low-cardinality columns and lets the engine combine several predicates with fast bitwise AND and OR before touching any rows, which is ideal for the multi-attribute filters typical of a data warehouse. It is poor for OLTP because updating a single row locks and rewrites a whole bitmap segment, blocking concurrent writers.
79. What is the difference between a unique index and a primary key? 2–5 yrs
A primary key is a logical constraint: it identifies the row, forbids NULLs, and there is one per table; most engines implement it by creating a unique index underneath. A unique index is a physical structure that enforces uniqueness, generally permits NULLs, and a table may have many. So the primary key is a role and the unique index is a mechanism, which is why every primary key has a unique index but not every unique index is a primary key.
ER Modelling & Relationships
80. What is the ER model in DBMS? Fresher
The ER (Entity-Relationship) model is a high-level conceptual way to design a database by describing it as entities, their attributes, and the relationships between them. Entities become tables, attributes become columns, and relationships become foreign keys or link tables. It is usually drawn as an ER diagram and is the blueprint that guides the actual schema.
81. What are the types of relationships in a database? Fresher
There are three main types. One-to-one, where one row in a table relates to exactly one row in another. One-to-many, where one row relates to many rows in another table (the most common). And many-to-many, where many rows relate to many rows, which is implemented using a junction (bridge) table that holds the foreign keys of both sides.
82. What is the difference between a strong entity and a weak entity? 2–5 yrs
A strong entity has its own primary key and exists independently. A weak entity has no sufficient key of its own and depends on an owner entity for identification, so its key is the owner key plus a partial (discriminator) key. An order line identified by order number plus line number is the standard example. In the diagram a weak entity is drawn with a double rectangle and its identifying relationship with a double diamond, and it always has total participation.
83. What do cardinality and participation mean in an ER diagram? 2–5 yrs
Cardinality is how many instances of one entity may relate to instances of another: one to one, one to many, or many to many. Participation is whether every instance must take part: total participation, drawn as a double line, means each instance must appear in the relationship, while partial participation means it may. The two are independent, and together they decide whether the foreign key column ends up NOT NULL and on which side it lives.
84. What are composite, derived and multivalued attributes? Fresher
A composite attribute is one that breaks into meaningful parts, such as address splitting into street, city and postcode; you usually store the parts as separate columns. A derived attribute is computed from others, such as age from date of birth, and normally should not be stored at all. A multivalued attribute holds several values for one entity, such as multiple phone numbers, and cannot be a single column in 1NF, so it becomes its own table with a foreign key back.
85. What are generalisation, specialisation and aggregation? 2–5 yrs
Specialisation is top-down: you take a general entity and split it into subclasses with their own attributes, such as Employee into Manager and Engineer. Generalisation is the bottom-up reverse, factoring shared attributes of several entities into a common superclass. Aggregation treats a whole relationship as if it were a single higher-level entity so that another relationship can refer to it, which you need when a relationship itself participates in a relationship.
86. How do you convert an ER diagram into relational tables? 2–5 yrs
Each strong entity becomes a table with its key as the primary key. Each weak entity becomes a table whose primary key is the owner key plus its discriminator, with a foreign key to the owner. A one-to-many relationship becomes a foreign key on the many side. A one-to-one relationship becomes a foreign key on either side, preferably the one with total participation. A many-to-many relationship becomes its own junction table holding both foreign keys as a composite primary key, and multivalued attributes each become a separate table.
87. What is a recursive or unary relationship? 2–5 yrs
A recursive relationship connects an entity to itself, such as an employee who manages other employees or a part composed of other parts. It is implemented with a foreign key in the table pointing at the same table primary key, and queried with a self join for one level or a recursive CTE for arbitrary depth. Watch out for cycles, which will make a naive recursive query run forever, and for the NULL at the root of the hierarchy.
Concurrency Control, Locking & Deadlocks
88. What is a deadlock, and how can it be handled? Fresher
A deadlock occurs when two or more transactions each hold a lock the other needs, so none can proceed and they wait forever. Databases handle it with deadlock detection, where the system finds the cycle and aborts (rolls back) one transaction as a victim so the others continue. It can be prevented by acquiring locks in a consistent order, keeping transactions short, and using lower isolation levels or timeouts.
89. What is concurrency control and why is it needed? Fresher
Concurrency control is the set of mechanisms that let many transactions run at once while still producing a result equivalent to running them one at a time. Without it you get the classic problems: lost updates, dirty reads, non-repeatable reads and inconsistent analysis. Running transactions strictly one after another would be correct but would waste the machine, so the whole discipline is about getting serializable outcomes with as much genuine parallelism as possible.
90. What is the difference between a shared lock and an exclusive lock? 2–5 yrs
A shared (read) lock lets many transactions read the same item at once but blocks any writer. An exclusive (write) lock is held by one transaction alone and blocks both readers and other writers. So shared locks are compatible with each other and with nothing else. This is the compatibility matrix behind lock-based concurrency control, and it is why a long-running report can block a write in a system that does not use MVCC.
91. What is two-phase locking, and what is strict 2PL? Senior
Two-phase locking splits a transaction into a growing phase, where it may acquire locks but not release any, and a shrinking phase, where it may release but not acquire. That single rule guarantees conflict serializability. Its weakness is that releasing locks early allows other transactions to read uncommitted data, so a rollback cascades. Strict 2PL fixes this by holding all exclusive locks until commit or abort, which is what real databases implement; rigorous 2PL holds shared locks until then as well.
92. What is timestamp-based concurrency control? Senior
Each transaction gets a unique timestamp when it starts, and each data item records the timestamp of the last transaction that read it and the last that wrote it. An operation is allowed only if it is consistent with the timestamp order; a transaction attempting to read or write something already touched by a logically later transaction is aborted and restarted with a new timestamp. It is deadlock-free because there is no waiting, but it can cause repeated restarts of long transactions, which is a starvation risk.
93. What is MVCC and how does it avoid read locks? Senior
Multi-version concurrency control keeps multiple versions of each row rather than overwriting in place. A write creates a new version stamped with the transaction identifier, and each reader sees the version that was committed as of its own snapshot, so readers never block writers and writers never block readers. This is how PostgreSQL, Oracle and InnoDB deliver high read concurrency. The costs are storage for old versions, the need for vacuum or undo-segment cleanup, and the fact that snapshot isolation still allows write skew.
94. What is the difference between optimistic and pessimistic concurrency control? 2–5 yrs
Pessimistic control assumes conflicts are likely and locks data before touching it, so conflicting transactions wait; it is right when contention is high and retries are expensive. Optimistic control assumes conflicts are rare, lets transactions run against a snapshot, and validates at commit time, aborting and retrying if something conflicted; it is right when contention is low and it avoids lock overhead entirely. The common application-level form of optimistic control is a version column checked in the UPDATE WHERE clause.
95. What are lock granularity and lock escalation? Senior
Granularity is the size of the object locked: database, table, page or row. Fine-grained row locks maximise concurrency but each lock costs memory and bookkeeping, so a statement touching a million rows would need a million locks. Escalation is when the engine notices a transaction holding too many fine-grained locks and replaces them with a single coarser lock, saving memory but abruptly reducing concurrency. Intent locks at the higher levels are what let the engine check compatibility without scanning every row lock.
96. What is starvation and how does it differ from deadlock? 2–5 yrs
A deadlock is a cycle in which each transaction waits for a lock another holds, so none can ever proceed without intervention. Starvation is different: the system is making progress, but one particular transaction is repeatedly passed over or repeatedly chosen as the deadlock victim, so it never completes. Deadlock is resolved by detection and rollback; starvation is prevented by fair scheduling, ageing priorities, and not always choosing the same victim.
97. What are the conditions for deadlock, and what are wait-die and wound-wait? Senior
Deadlock needs four conditions to hold together: mutual exclusion, hold and wait, no preemption, and circular wait; break any one and it cannot occur. Wait-die and wound-wait are timestamp-based prevention schemes that break circular wait by using age. Under wait-die an older transaction may wait for a younger one but a younger one requesting a lock held by an older is killed immediately. Under wound-wait an older transaction preempts (wounds) the younger holder, and a younger one simply waits. Both guarantee no cycle because waiting always runs in one direction of age.
Views, Stored Procedures & Triggers
98. What is a view in a database? Fresher
A view is a virtual table defined by a stored SELECT query; it does not store data itself but presents the result of that query as if it were a table. Views simplify complex queries, provide a consistent interface, and improve security by exposing only specific columns or rows. A materialized view, by contrast, physically stores the result and must be refreshed to stay current.
99. Can you insert or update data through a view? 2–5 yrs
Sometimes. A view is updatable only if the database can map each modified row unambiguously back to a single row of one base table, which generally rules out views containing joins, aggregates, GROUP BY, DISTINCT, UNION or window functions. When a view is not naturally updatable you can still make it writable with an INSTEAD OF trigger that spells out what to do. The WITH CHECK OPTION clause is also worth knowing: it prevents writing rows through the view that the view itself would not show.
100. What is a stored procedure and what are its advantages? Fresher
A stored procedure is a named block of SQL and procedural logic stored in the database and executed by name, optionally with parameters. Advantages are that logic close to the data avoids round trips, the plan can be reused, permissions can be granted on the procedure instead of the underlying tables, and parameters make injection much harder. Disadvantages are that the logic is harder to version control, test and review than application code, and it ties you more tightly to one database vendor.
101. What is the difference between a stored procedure and a function? 2–5 yrs
A function must return a value and is designed to be called from inside a SQL expression, such as in a SELECT list or a WHERE clause, so it is normally restricted from modifying data. A procedure is invoked as a statement with CALL or EXECUTE, may return zero or many result sets through output parameters, and is allowed to perform DML and manage transactions. In short, functions compute and are usable in queries; procedures act and are not.
102. What is a trigger and when would you use one? Fresher
A trigger is a block of code the database runs automatically when a specified event occurs on a table, typically INSERT, UPDATE or DELETE. Legitimate uses are maintaining an audit trail, enforcing a complex rule that a CHECK constraint cannot express, and keeping a denormalised column in sync. The danger is invisibility: a trigger executes work nobody reading the application code can see, which makes debugging and performance analysis much harder, so they should be few and simple.
103. What are the different types of triggers? 2–5 yrs
They are classified by timing and by granularity. BEFORE triggers fire ahead of the statement and can modify or reject the incoming row; AFTER triggers fire once the change has been applied and are the right place for auditing and cascading; INSTEAD OF triggers replace the operation entirely and are mainly used to make views writable. By granularity, a row-level trigger fires once per affected row while a statement-level trigger fires once per statement no matter how many rows it touches.
104. What is a cursor and why is it usually avoided? 2–5 yrs
A cursor lets procedural code step through a result set one row at a time. It is avoided because SQL is set-based: a single statement lets the optimiser choose an efficient plan, whereas a cursor forces row-by-row execution with per-row overhead and long-held locks, and is frequently orders of magnitude slower. The legitimate cases are genuinely sequential logic such as running maintenance on each table in turn, or deliberately batching a huge update to avoid one enormous transaction.
105. What is a materialised view and when do you refresh it? Senior
A materialised view stores the physical result of its query on disk rather than recomputing it, so an expensive aggregation becomes a cheap read, and it can carry its own indexes. The cost is staleness: it must be refreshed, either completely, incrementally where the engine supports it, on a schedule, or on commit of the base tables. Choose it when the query is expensive, is run far more often than the data changes, and the consumers can tolerate a known refresh lag.
106. What is SQL injection and how do prepared statements prevent it? 2–5 yrs
SQL injection happens when user input is concatenated into a query string, so the input can close a literal and add its own SQL, letting an attacker read, modify or destroy data. A prepared statement sends the query text with placeholders to the server first, where it is parsed and planned, and the values are sent separately afterwards; because the structure is already fixed, a value can never become syntax. Escaping and validation are useful defence in depth, but parameterisation is the actual fix, and it also gives plan reuse.
Storage, File Organisation & Recovery
107. How is data physically stored in a database? 2–5 yrs
Data lives in files divided into fixed-size pages or blocks, typically 4 to 16 kilobytes, which are the unit of I/O between disk and the buffer pool. A page holds a header, a slot directory and the rows themselves, with a pointer per row so rows can move within the page without changing their identifier. Understanding this explains a lot of behaviour: why row size affects rows per page and therefore scan cost, and why random single-row access is expensive when it means one page read per row.
108. What are heap, sequential and hash file organisations? 2–5 yrs
A heap file stores records wherever there is space, which makes insertion very fast and any search a full scan. A sequential (sorted) file keeps records ordered by a key, so binary search and range scans are efficient, but inserting in the middle is costly and requires overflow areas. A hash file places records in buckets by hashing a key, giving fast exact-match lookups but no ordering and poor behaviour when buckets overflow. Real tables are usually heaps or index-organised, with the ordering supplied by indexes.
109. What is the difference between a dense and a sparse index, and between primary and secondary? Senior
A dense index has an entry for every record, so a lookup always succeeds in the index. A sparse index has an entry only per block, which makes it far smaller and able to stay in memory, but it requires the file to be sorted on that key so you can find the right block and scan within it. A primary index is built on the field the file is ordered by, so there can be only one and it can be sparse; a secondary index is on any other field, must be dense, and requires an extra fetch to reach the row.
110. What is the buffer pool and why does it matter so much? 2–5 yrs
The buffer pool is the in-memory cache of database pages. Every read checks it first and only goes to disk on a miss, and writes modify the page in memory and mark it dirty, to be flushed later. Because a memory access is orders of magnitude faster than a disk one, the buffer hit ratio dominates performance, which is why sizing it correctly is usually the single highest-impact tuning decision. It also explains why a large scan can hurt unrelated queries by evicting their hot pages.
111. What is write-ahead logging? Senior
Write-ahead logging is the rule that a log record describing a change must reach durable storage before the modified data page does. It buys both atomicity and durability while allowing data pages to be written lazily in large sequential batches, because the log alone is enough to reconstruct anything lost. Commit therefore costs one sequential log flush rather than random data writes. On recovery, the log is replayed to redo committed work and undo uncommitted work.
112. What is a checkpoint in a database? Senior
A checkpoint flushes dirty pages from the buffer pool to disk and writes a marker into the log recording which transactions were active at that moment. It exists to bound recovery time: without one, restart would have to replay the log from the beginning of time, so the checkpoint tells recovery where it can safely start. The trade-off is a burst of I/O, which is why modern engines spread checkpoints out rather than doing them all at once.
113. What is the difference between undo and redo in recovery? Senior
Undo removes the effects of transactions that were still in flight when the system crashed, restoring the before-images from the log so no partial work survives, which gives atomicity. Redo reapplies the effects of transactions that committed but whose data pages had not yet been flushed, using the after-images, which gives durability. A standard ARIES-style recovery runs three passes: analysis to find the state at the crash, then redo, then undo.
114. What is shadow paging? Senior
Shadow paging keeps two page tables: a current one that is modified and a shadow that reflects the last committed state. Updates are written to new pages rather than in place, and commit is the atomic act of switching the page table pointer, so no undo log is needed and recovery is instant. Its drawbacks are that it fragments data, complicates concurrent transactions sharing pages, and creates significant garbage collection work, which is why write-ahead logging won in practice.
115. What is point-in-time recovery, and how does it relate to backups? 2–5 yrs
A full backup is a copy of the database at a moment in time; on its own it can only restore you to that moment, losing everything since. Point-in-time recovery combines the backup with the transaction log archived after it, replaying the log forward to any chosen instant, which is how you recover from an accidental DROP or a bad migration without losing the whole day. The two numbers that matter are the recovery point objective (how much data you can lose) and the recovery time objective (how long restoring may take), and a backup you have never test-restored should not be counted on.
116. What is database replication, and what is the difference between synchronous and asynchronous? Senior
Replication maintains copies of the database on other servers for read scaling, high availability and disaster recovery. Synchronous replication makes the primary wait for at least one replica to confirm the write before acknowledging the commit, so no committed data is lost on failover, at the cost of latency on every write and vulnerability to a slow replica. Asynchronous replication acknowledges immediately and ships changes afterwards, which is faster but means replication lag: a failover can lose recent commits, and reads from a replica may be stale.
SQL vs NoSQL, Scaling & Modern Databases
117. What is the difference between SQL and NoSQL databases? Fresher
SQL databases are relational, use a fixed schema of tables with typed columns and constraints, support joins, and provide strong ACID transactions, which suits data with real relationships and correctness requirements. NoSQL databases relax the schema and usually the transactional guarantees in exchange for horizontal scalability and flexible or nested data, and they generally have no joins so the data model is shaped around the access pattern. It is a trade-off, not a ranking: the right question is which one the workload needs.
118. What are the main types of NoSQL database? 2–5 yrs
Key-value stores such as Redis map an opaque key to a value and are the fastest and simplest, good for caching and sessions. Document stores such as MongoDB hold self-describing JSON-like documents and allow querying inside them, good for varied or evolving entities. Wide-column stores such as Cassandra organise data by partition and clustering keys for enormous write throughput. Graph databases such as Neo4j store nodes and edges and are the right answer when the relationships themselves are what you traverse.
119. What is the CAP theorem? 2–5 yrs
CAP states that a distributed data store can guarantee at most two of consistency, availability and partition tolerance. Since network partitions do happen and cannot be designed away, the real choice during a partition is between staying consistent and refusing some requests (CP) or staying available and serving possibly stale data (AP). Outside of a partition, the trade-off is really between consistency and latency, which is what the PACELC refinement makes explicit.
120. What is BASE and how does it compare with ACID? 2–5 yrs
BASE stands for Basically Available, Soft state, Eventually consistent, and it is the design philosophy of many distributed NoSQL systems. Where ACID insists every transaction leave the database in a fully consistent state immediately, BASE accepts temporary inconsistency in exchange for availability and scale, on the assumption that replicas converge once updates propagate. The practical consequence is that the application must tolerate stale reads and handle conflicts, which pushes complexity from the database up into your code.
121. What is eventual consistency? 2–5 yrs
Eventual consistency guarantees that if no new writes are made, all replicas will converge on the same value in time, but says nothing about how long that takes or what you see meanwhile. So a read right after a write may return the old value, and two clients may briefly disagree. It is acceptable for a follower count or a product view total and unacceptable for an account balance, which is exactly the judgement an interviewer is testing. Stronger session guarantees such as read-your-own-writes are the usual middle ground.
122. What is the difference between horizontal and vertical scaling for databases? 2–5 yrs
Vertical scaling means a bigger machine: more CPU, memory and faster storage. It is simple, needs no application change, and takes you a long way, but there is a hard ceiling and the machine remains a single point of failure. Horizontal scaling means more machines, usually read replicas first and then sharding for writes. It scales further and adds redundancy, but it introduces replication lag, cross-shard queries and distributed transactions. The usual order is optimise, then scale up, then add replicas, then shard.
123. What is sharding, and how does it differ from partitioning? Senior
Partitioning splits one table into pieces, typically by range, list or hash, usually within a single database instance, so the engine can prune irrelevant partitions and manage them independently. Sharding distributes those pieces across separate database servers, so each holds a subset of the data and the write throughput of the whole system scales with the number of shards. Sharding costs far more: you need a shard key chosen so traffic spreads evenly, cross-shard queries and joins become application work, and resharding a live system is a serious project.
124. When would you choose a NoSQL database over a relational one? 2–5 yrs
When the data has no meaningful relational structure or the schema genuinely varies per record, when you need write throughput or data volume beyond a single relational primary, when the access pattern is a simple key lookup at very high rate, or when the domain is naturally a graph. Conversely, stay relational when you need joins, multi-row transactions, ad-hoc querying or strong constraints. A very common answer in a system-design interview is to use both: relational for the source of truth and a key-value store for caching.
125. What is connection pooling and why does it matter? 2–5 yrs
Opening a database connection is expensive: a TCP handshake, authentication and server-side session setup, and each open connection consumes memory and a process or thread on the server. A pool keeps a fixed set of connections open and hands them out to requests, returning them afterwards, so that cost is paid once. It also acts as a throttle, since a database performs far better with a modest number of busy connections than with thousands mostly idle. Pool size too small causes queuing; too large causes contention on the database itself.
Get these answered live in your real interview
NostrobeAI is a real-time AI interview copilot — it hears the question and drafts a strong answer on your screen, invisible on Zoom, Meet, and Teams. One-time pricing, no subscription.
Try NostrobeAI free