DBMS Interview Questions (2026): Must-Know Q&A With Answers

Four concrete failure modes, not one vague "it's less organized." Redundancy: the same student's phone number sits in an admissions file, a hostel file, and a library file, and nothing keeps them in sync. Inconsistency, the direct result of that redundancy, one file gets updated and the other two quietly go stale. No atomicity, if a program crashes halfway through writing a record, the file is left half-written with no way to roll back. And no concurrent access control, two clerks editing the same result sheet at the same time can silently overwrite each other's changes.

A college result system built on per-subject text files is the classic version of this. A registrar manually fixes a typo in one file and forgets the other four files that also list the same student's roll number. Nothing catches the mismatch until someone notices at graduation.

An entity is a real-world object worth tracking on its own, Student, Course, Employee. An attribute is a property of that entity, name, roll number, salary. A relationship connects two or more entities, Enrolls, Teaches, Manages.

A weak entity is the wrinkle: it has no primary key of its own and can't be identified without the entity it depends on. A Dependent entity tied to an Employee is the standard example, two employees can each have a dependent named Priya, so Dependent needs the owning Employee's key plus a partial key (Priya, plus which employee) to be uniquely identifiable at all.

1NF requires every column to hold a single atomic value, no repeating groups, no comma-separated list crammed into one cell.

Before (violates 1NF):
StudentID | StudentName | Phones
101    | Aditi Rao  | 9876543210, 9123456780
102    | Rohan Iyer | 9988776655

After (1NF):
StudentID | StudentName
101    | Aditi Rao
102    | Rohan Iyer

StudentID | Phone
101    | 9876543210
101    | 9123456780
102    | 9988776655

One student, two phone numbers, one cell. Split it into its own table keyed by StudentID plus Phone, and every value in every column is atomic again. Nothing fancy, just don't let a column secretly hold a list.

Atomicity means all-or-nothing, enforced through an undo log, a crash halfway through a transaction rolls back everything it had already written, not just what's convenient. Consistency means every transaction takes the database from one valid state to another, respecting whatever constraints and triggers you've defined, this one is really an obligation on your schema design as much as it is a DBMS feature. Isolation means concurrent transactions behave as if they ran one after another, though how strictly that holds depends entirely on the isolation level actually configured, more on that below. Durability means once a transaction commits, it survives a crash, typically because a write-ahead log gets flushed to disk before the commit is ever acknowledged back to the client.

A transaction is a sequence of one or more operations treated as a single logical unit, either the whole thing happens or none of it does. BEGIN starts logging changes to an undo/redo log without making them visible to other transactions yet. COMMIT flushes the transaction's changes durably (the write-ahead log gets written to disk) and makes them visible. ROLLBACK discards everything logged since BEGIN, using that same undo log to reverse whatever had already been applied.

Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Each level down the list removes one more anomaly at the cost of more locking or more aborted transactions.

A clustered index determines the physical storage order of a table's rows, the table's data pages themselves are organized by that index, which is why a table can only have one. A non-clustered index is a separate structure entirely, it stores the indexed column's values alongside a pointer back to where the actual row lives, and a table can have several of these.

INNER JOIN returns only rows with a match in both tables. LEFT JOIN keeps every row from the left table regardless of a match, filling in NULLs for any right-side columns that don't have one. Getting this wrong shows up immediately as a row-count mismatch a candidate can't explain.

A view is a stored SELECT statement that behaves like a table when you query it. Yes, for an ordinary view, every SELECT against it re-executes the underlying query fresh, a view stores SQL, not data. A materialized view is the exception, it actually persists the result set physically and needs an explicit refresh to pick up new data, trading staleness risk for speed.

DDL (Data Definition Language) covers CREATE, ALTER, DROP, and TRUNCATE, statements that change the shape of the database itself: tables, indexes, schemas. These are auto-committed in most databases, meaning you can't roll one back inside a transaction the way you might expect. Postgres is an exception, it actually supports transactional DDL.

DML (Data Manipulation Language) is INSERT, UPDATE, DELETE, and SELECT, the commands that read and write rows, and this is what you wrap in BEGIN/COMMIT/ROLLBACK. DCL (Data Control Language) is GRANT and REVOKE, controlling who can do what to which objects. TCL (Transaction Control Language) is COMMIT, ROLLBACK, and SAVEPOINT, the commands that manage transaction boundaries.

Worth mentioning in an interview: TRUNCATE is DDL, not DML, which is why it's faster than DELETE (no row-by-row logging) but also why it resets auto-increment counters and can't take a WHERE clause.

CHAR(n) is fixed-length. The database pads it with spaces up to n characters no matter what you store, and it always takes exactly n bytes. VARCHAR(n) is variable-length, it stores only the actual characters plus a small length prefix, so 'cat' in a VARCHAR(50) column takes 4 bytes, not 50.

TEXT is for large, effectively unbounded strings, and most engines store it out of the main row once it crosses a size threshold (Postgres calls this TOASTing), which means reading a TEXT column can cost an extra disk seek that a VARCHAR read wouldn't.

The practical rule: use CHAR only for genuinely fixed-width data like a two-letter country code, VARCHAR for anything with a reasonable known max length, and TEXT when you actually expect large blobs of content, since indexing and comparing TEXT columns is slower and some engines won't let you index the full column without a prefix length.

A PRIMARY KEY is a UNIQUE constraint plus a NOT NULL constraint, and a table can only have one, because it's what every foreign key elsewhere in the schema points to by default. A UNIQUE constraint just says no two rows can share a value in that column, and a table can have several of them.

Unlike a primary key, most databases let a UNIQUE column hold NULL, sometimes even multiple NULLs, because NULL isn't treated as equal to another NULL. A concrete example: a users table has an auto-increment id as the primary key, but email is UNIQUE because two accounts should never share an address, even though nothing else in the schema references email as a foreign key.

NULL means unknown or not applicable, it isn't the same as zero or an empty string, and that distinction breaks a lot of intuitive SQL. Writing WHERE salary = NULL returns zero rows every time, because NULL isn't equal to anything, not even another NULL. You have to use IS NULL or IS NOT NULL to test for it.

This turns into a real bug source with NOT IN. If a subquery returns even one NULL, `WHERE column NOT IN (subquery)` silently returns zero rows for the whole outer query, because SQL can't prove the value isn't equal to that unknown NULL. That's why a lot of teams default to NOT EXISTS instead of NOT IN whenever the inner column might contain NULLs.

Aggregate functions mostly ignore NULLs too. COUNT(*) counts every row, but COUNT(column) skips NULLs in that column, and AVG/SUM ignore NULLs rather than treating them as zero, which matters when someone expects an average to include unset values as zero and gets a different number instead.

UNION combines two result sets and removes duplicate rows, which means the database has to sort or hash the whole combined result before returning anything. UNION ALL just concatenates both result sets and returns everything, duplicates included, with no dedup pass at all.

The practical impact is performance. On a large result set, UNION ALL can be noticeably faster because it skips the dedup step. If you already know the two queries can't overlap, say one filters status = 'active' and the other status = 'inactive', switching to UNION ALL is a free win with no change in behavior.

WHERE filters individual rows before any grouping happens. HAVING filters groups after GROUP BY has already collapsed the rows, which is why HAVING can reference an aggregate like COUNT(*) or SUM(amount) and WHERE can't, at the point WHERE runs those aggregates don't exist yet.

A query like `SELECT customer_id, COUNT(*) FROM orders WHERE status = 'completed' GROUP BY customer_id HAVING COUNT(*) > 5` filters down to completed orders first, groups by customer, then keeps only customers with more than 5 of those orders. Putting COUNT(*) > 5 in the WHERE clause instead throws an error, because WHERE executes before GROUP BY in the logical processing order, even though it's written earlier in the query text.

Controlled redundancy, data gets normalized instead of copy-pasted across files. A real query language, SQL, instead of custom parsing code for every report. ACID transactions, so a crash mid-write doesn't leave half a record behind. Concurrent access control through locking or MVCC, so two people editing at once don't corrupt each other's work. And centralized security, permissions live on the database itself instead of on whichever file happens to hold the data.

Textbooks like to round this to a tidy four. I'd rather say five and be honest that the list keeps growing depending on who's asking, backup and recovery deserves its own line item too, and most interviewers are fine with you naming four solid ones instead of forcing a fifth just to hit a number.

Take an Employee table with EmpID, Aadhaar, Email, and PAN as columns. Each of those four, on its own, uniquely identifies a row, so each is a candidate key. A super key is any set of columns that uniquely identifies a row, whether or not it's minimal, {EmpID, Name} is a super key because it still identifies a row uniquely, it's just not minimal since EmpID alone already does the job. A primary key is whichever candidate key the designer actually picks to be the real one, usually EmpID here, since exposing Aadhaar in every join and foreign key reference is a privacy problem waiting to happen.

The candidate keys that don't get chosen, Aadhaar, Email, PAN, don't disappear. They become alternate keys, still unique, still enforceable with a UNIQUE constraint, just not the one everything else in the schema references.

A foreign key enforces referential integrity: a value in the child table has to exist in the parent table's key column, or be null. It stops orphaned rows, an employee record pointing at a department ID that no longer exists.

CREATE TABLE department (
  dept_id INT PRIMARY KEY,
  dept_name VARCHAR(50)
);

CREATE TABLE employee (
  emp_id INT PRIMARY KEY,
  name VARCHAR(50),
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES department(dept_id)
    ON DELETE SET NULL
);

Three real options when a parent row gets deleted. RESTRICT (or the default, NO ACTION) blocks the delete outright while a referencing row exists. CASCADE deletes the child rows along with the parent, dangerous if you don't mean it. SET NULL, used above, keeps the employee row but clears dept_id, which only works if that column is nullable in the first place.

2NF only becomes relevant once you've got a composite key, and it requires every non-key column to depend on the whole key, not just part of it. Here, StudentName depends only on StudentID, not on the (StudentID, CourseID) pair together, that's a partial dependency, a 2NF violation. CourseName, InstructorID, and InstructorRoom all depend only on CourseID, another partial dependency. Grade is the one column that genuinely needs both halves of the key, a grade only makes sense for a specific student in a specific course.

Fix: split into Student(StudentID, StudentName), Course(CourseID, CourseName, InstructorID, InstructorRoom), and Enrollment(StudentID, CourseID, Grade). If the primary key had been a single column to begin with, this step is automatic, 2NF only ever bites tables with composite keys.

InstructorRoom depends on InstructorID, and InstructorID depends on CourseID, the actual key. So CourseID determines InstructorRoom only indirectly, through InstructorID, that's a transitive dependency, and 3NF forbids it.

Fix: pull Instructor(InstructorID, InstructorRoom) into its own table, and leave Course as (CourseID, CourseName, InstructorID) referencing Instructor by foreign key. Now every non-key column in every table depends directly on that table's key and nothing else.

Read-heavy reporting is the usual case. Copy CourseName directly onto every Enrollment row instead of joining to Course on every report query, and you trade a join for a small update anomaly you're choosing to accept, if a course gets renamed, you now have to update it in two places instead of one.

Star-schema data warehouses do this systematically, denormalized fact and dimension tables on purpose, because the whole workload is reads. I don't have a clean number for exactly where the tradeoff flips, it depends on hardware, index quality, and query pattern, but a join across four normalized tables that's fine at 10,000 rows can genuinely hurt at 40 million. Denormalizing a normalized-order-line's product name is actually a correctness fix too, not just speed, a customer's receipt from eighteen months ago should show the product name they actually saw, not whatever it got renamed to last week.

Isolation. Full serializability is expensive, it needs either heavier locking or conflict-detection overhead, so almost every production database defaults to something weaker: Read Committed on Postgres, Oracle, and SQL Server, Repeatable Read on MySQL's InnoDB.

Atomicity, Consistency, and Durability rarely get negotiated down, getting those wrong corrupts data outright. Isolation gets dialed back because the failure mode, a stale read, an occasional phantom row, is usually survivable for the specific application, not because isolation matters less in principle.

A shared (S) lock lets multiple transactions read the same row at once, but blocks anyone from taking an exclusive lock on it while any shared lock is held. An exclusive (X) lock is stricter, only one transaction can hold it, and no one else can take a shared or exclusive lock on that row until it's released.

-- Session A
BEGIN;
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE; -- exclusive lock

-- Session B, run at the same time
BEGIN;
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE; -- blocks until A commits or rolls back

FOR UPDATE requests an exclusive lock. FOR SHARE (or LOCK IN SHARE MODE on older MySQL) requests a shared one instead, letting other readers in but still blocking writers.

A dirty read: transaction A updates a balance from 500 to 300 but hasn't committed yet, transaction B reads 300, then A rolls back. B now holds a value that never really existed in any committed state. A non-repeatable read: B reads the same row twice inside one transaction, gets 500 the first time and 460 the second, because A committed an update to that exact row in between B's two reads. A phantom read: B runs SELECT COUNT(*) WHERE dept = 'CS' twice, gets 14 the first time and 15 the second, because A inserted a new matching row in between, no existing row changed, a new one just appeared where B's query looks.

A hash table gives roughly constant-time lookup for exact-match equality, but it can't serve range queries at all, WHERE age BETWEEN 25 AND 35, or an ORDER BY, since hashing scrambles sort order on purpose. A B+ tree keeps keys sorted, with its leaf nodes linked together in order, so a range scan just walks that leaf chain instead of scanning the whole table.

It also stays balanced regardless of insert order, unlike a plain unbalanced binary search tree that can degrade into something close to a linked list under the wrong insert pattern. Depth usually stays around three or four levels even for tables with tens of millions of rows, which is why an index lookup on a huge table still resolves in a handful of disk reads.

A self join joins a table to itself, useful whenever a table has a hierarchical or peer relationship encoded in its own columns.

SELECT e.name AS employee, m.name AS manager
FROM employee e
LEFT JOIN employee m ON e.manager_id = m.emp_id;

An employee table with a manager_id column pointing back at another row in the same table is the textbook case. LEFT JOIN rather than INNER matters here specifically because the CEO's manager_id is null, an INNER JOIN would silently drop that row from the result.

A trigger is a block of code that fires automatically on an INSERT, UPDATE, or DELETE against a specific table. The real risk is hidden logic: a developer reading application code has no way to see that inserting one row silently fires three cascading triggers elsewhere in the schema, which makes behavior hard to trace and genuinely hard to test.

I'd rather see business logic sitting in application code or an explicit stored procedure than buried in a trigger nobody remembers exists six months later. Audit-logging triggers, writing a row to a history table on every UPDATE, are the one case I think earns the tradeoff, since that's exactly the kind of thing you want to happen no matter which code path touched the row.

A function has to return a value and can typically be called directly inside a SELECT or WHERE expression, and most engines restrict functions from making data-modifying side effects, though the exact rules vary by database. A stored procedure doesn't have to return anything, it can return multiple result sets or use output parameters instead, and it's called explicitly with CALL or EXEC, with full freedom to run INSERT, UPDATE, DELETE, and its own transaction control.

Schema flexibility versus enforced structure is the headline one, a document store lets you add a field to one record without touching every other row, a relational table wants a migration. Beyond that: join support, relational databases are built for multi-table joins, most NoSQL stores either can't do them well or push that work back onto the application. And consistency model, distributed NoSQL stores traditionally leaned on eventual consistency for horizontal write scale, though that line has blurred, MongoDB has supported multi-document ACID transactions since version 4.0 in 2018, so "NoSQL means no transactions" hasn't been categorically true for a while.

A non-correlated subquery runs once, independent of the outer query, and its result is used as a fixed value or list. `SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees)` runs that inner average exactly once no matter how many rows the outer query touches.

A correlated subquery references a column from the outer query, so conceptually it has to be re-evaluated once per outer row. `SELECT * FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e.department_id)` finds people earning more than their own department's average, and the inner query depends on e.department_id from whichever row is currently being checked.

A good optimizer will often rewrite a correlated subquery into a join or a window function at the implementation level, but you can't rely on that happening on every engine. That's why this exact pattern is very often written instead with a window function, `AVG(salary) OVER (PARTITION BY department_id)`, computed once in a single pass instead of leaning on the optimizer to de-correlate it.

All three can express "give me rows in A that have a match in B," but they behave differently in ways that matter. EXISTS stops as soon as it finds one matching row, it's a pure existence check that doesn't care how many matches there are, which makes it a good fit when the subquery might return a large or duplicate-heavy result.

IN materializes the subquery's result as a list and checks membership against it, and if that list contains a NULL and you're using NOT IN, the whole query can silently break, as covered elsewhere. IN is fine for a small, known, NULL-free list, but riskier against a subquery result.

A JOIN is different in kind, not just syntax, because it can duplicate rows from A if there are multiple matches in B, which EXISTS and IN never do since they're only filtering, not multiplying. If you just need to check for a match and don't need columns from B, EXISTS is usually correct and fast. If you actually need to pull columns from B into the result, you need a JOIN, and you need to think carefully about whether duplicate rows from a one-to-many relationship are what you actually want.

A covering index contains every column a query needs, both the ones in the WHERE clause and the ones in the SELECT list, so the engine can answer the query entirely from the index without touching the underlying table. Normally, once a non-clustered index finds a matching row, it has to do a second lookup, sometimes called a key lookup, back into the table to fetch the remaining columns.

For example, if `SELECT customer_id, order_date FROM orders WHERE status = 'shipped'` runs constantly, an index on (status, customer_id, order_date) means the engine can scan the index, find every 'shipped' row, and return customer_id and order_date directly from the index leaf, no trip to the table needed.

The tradeoff is size and write cost. Every extra column added to make an index covering makes it bigger on disk and slower to maintain on every INSERT and UPDATE touching those columns, so this is a decision made for specific hot queries, not something you do reflexively on every index.

Pessimistic locking assumes conflicts are likely, so it grabs a lock upfront, usually via SELECT... FOR UPDATE, and any other transaction wanting that row has to wait until the lock releases. It's safe by construction but it blocks other transactions, which hurts throughput under heavy contention and opens the door to deadlocks if two transactions lock rows in different orders.

Optimistic locking assumes conflicts are rare, so nothing gets locked upfront. You read a row along with a version number or timestamp, do your work in application memory, and write it back with a WHERE clause checking the version hasn't changed: `UPDATE accounts SET balance = ?, version = version + 1 WHERE id = ? AND version = ?`. If zero rows update, someone else modified it in between, and the application retries or surfaces a conflict.

The call comes down to contention and how expensive a retry is. Optimistic locking suits low-conflict situations like a user editing their own profile, since you avoid the cost of locking entirely. Pessimistic locking is the right call for something like a seat reservation or an inventory decrement, where a lost update is expensive and retries under high contention would just thrash.

Multi-version concurrency control means the database keeps multiple versions of a row instead of locking it for reads. When a transaction updates a row, the old version isn't overwritten in place, a new version gets written instead (Postgres stores a whole new row tuple with its own transaction ID metadata, InnoDB reconstructs the old version from the undo log), and each transaction sees a consistent snapshot based on when it started.

The benefit is that readers never block writers and writers never block readers. A SELECT running under MVCC reads whatever version was current when its transaction began, while an UPDATE running concurrently writes a new version without waiting for that reader to finish. This is why Postgres and MySQL give you a lot of concurrency by default without falling back on shared locks for plain reads.

The cost is that old row versions eventually need cleanup. Postgres calls this vacuuming, and if a long-running transaction keeps a snapshot open for a while, old versions pile up because nothing can reclaim them until no transaction could possibly still need them, which is one of the more common causes of Postgres bloat and vacuum tuning headaches.

A CTE (the WITH clause) is mostly a readability tool, it lets you name a subquery and reference it, possibly more than once, later in the same statement. In Postgres before version 12, a CTE also acted as an optimization fence, the planner materialized it fully before use, which people relied on intentionally and got burned by unintentionally; from Postgres 12 on, non-recursive CTEs can be inlined into the outer query just like a subquery, unless you explicitly mark them MATERIALIZED.

A plain subquery gives the optimizer full freedom to rewrite, merge, and reorder it with the rest of the query, which usually produces a better plan but can be harder to read once nested deeply.

A temp table is a real physical table, it has its own storage and its own statistics, and you can index it. That matters when you need to reference the same intermediate result across several statements, or when the intermediate set is large enough that you want the optimizer working from real row-count statistics instead of an estimate, since a subquery or CTE's cardinality is only a guess until it actually runs.

A nested loop join takes every row from the outer table and, for each one, searches the inner table for a match. It's cheap when the outer side is small and there's a good index on the inner join column, since each lookup is fast, but it gets expensive when both sides are large with no useful index, because it's effectively scanning the inner table once per outer row.

A hash join builds an in-memory hash table from the smaller input keyed on the join column, then streams the larger input through it. It's usually the fastest option for large, unsorted inputs with no useful index, but it needs enough working memory to hold the hash table, and if it doesn't fit, the engine spills to disk in batches, which slows things down considerably.

A merge join needs both inputs already sorted on the join key, then walks both sorted streams in lockstep, advancing whichever side is behind. It's efficient when both inputs are already sorted, for instance because they came from an index scan on that column, but if the optimizer has to sort both sides first just to enable a merge join, a hash join is often cheaper overall. Which one gets picked comes down to the optimizer's cost estimate from table sizes, available indexes, and memory settings, not a fixed rule.

A materialized view actually stores its result set on disk, like a snapshot, so reading from it is as fast as reading a regular table. The tradeoff is that it goes stale the moment the underlying data changes, and someone has to explicitly refresh it, either REFRESH MATERIALIZED VIEW in Postgres or a scheduled job elsewhere.

By default that refresh locks the materialized view for reads while it rebuilds, unless you use the CONCURRENTLY option, which requires a unique index on the materialized view and does a slower merge instead of a full rebuild. There's also storage cost, you're paying disk space to keep a duplicate copy of query results around.

Materialized views fit expensive dashboards or reports that don't need second-by-second freshness, an hourly refresh of a sales summary is usually fine, but they're the wrong tool when the query needs to reflect data written thirty seconds ago.

OLTP (transactional) systems are built for lots of small, fast reads and writes touching a handful of rows at a time: a checkout, an account update, a single order lookup. The schema is normalized, often to 3NF, specifically to avoid update anomalies and keep writes cheap and consistent, and indexes are built around the exact lookups the application does constantly.

OLAP (analytical) systems are built for scanning huge numbers of rows to compute aggregates, total revenue by region by quarter, not fetching a single row. These schemas are usually deliberately denormalized into a star schema, a central fact table (one row per event, like an order line item) surrounded by small dimension tables (customer, product, date), because joining a huge fact table against a few small dimension tables is far cheaper than joining across a fully normalized transactional schema with a dozen tables.

The practical consequence is that running heavy analytical queries directly against your OLTP database is a common way to cause a production incident, those big scans hold locks or consume buffer cache your transactional workload needs. That's why most teams pipe data into a separate warehouse via ETL or ELT instead of running reports against the primary database.

The N+1 problem happens when code fetches a list of N parent records with one query, then loops over them and fires a separate query per record to get related data, ending up with N+1 round trips instead of 2. It shows up constantly with ORMs: fetch 50 orders, then lazily access order.customer inside a loop, and the ORM quietly issues 50 separate SELECT statements, one at a time.

It's often invisible in development with a small dataset and low latency to a local database, and only becomes a visible problem in production, where each round trip costs real time and the list has hundreds of rows, turning a page load into seconds instead of tens of milliseconds.

# N+1: one query per order
orders = Order.objects.all()
for order in orders:
  print(order.customer.name) # separate query each time

# Fixed: eager load in one join
orders = Order.objects.select_related('customer')
for order in orders:
  print(order.customer.name) # no extra query

The fix is eager loading, joining the related table in the original query (select_related in Django, includes or eager_load in Rails, JOIN FETCH in JPA) so the ORM pulls everything in one or two queries instead of N+1.

An auto-increment integer is small (4 or 8 bytes), sequential, and inserts land at the end of a B+ tree index, keeping the index compact and avoiding page splits scattered across the tree. The downside is it's guessable and it exposes information like row count and growth rate, and it's awkward in a distributed setup because two nodes generating IDs independently will collide.

A random UUID, UUIDv4 specifically, solves the distributed generation problem, any node can generate one with essentially zero collision risk and no coordination needed. But a fully random UUID as a clustered primary key is genuinely bad for write performance at scale: because it's random, each insert lands at a random point in the B+ tree instead of at the end, causing constant page splits and worse cache locality, and the 16-byte UUID makes every secondary index bigger too, since secondary indexes store the primary key value.

The middle ground a lot of teams reach for now is a time-ordered variant, UUIDv7 or a ULID, which keeps randomness in the low bits to avoid collisions but makes the high bits roughly time-sortable, so inserts stay mostly sequential from the index's point of view while still being safely generatable across multiple nodes without coordination.

A composite key is a primary key made of two or more columns together, StudentID plus CourseID in an Enrollment table, where neither column alone is unique but the pair is. A composite index is a completely different concept: a performance structure built on multiple columns for faster lookups, and it doesn't need to have anything to do with uniqueness or identity at all, you can build one on (last_name, city) purely to speed up a common WHERE clause.

Column order matters differently for each. A composite key's uniqueness doesn't care which column you list first, mathematically. A composite index absolutely cares, thanks to the leftmost-prefix rule, an index on (last_name, city) speeds up a query filtering on last_name alone or on last_name and city together, but does nothing for a query filtering on city alone. That distinction trips people up constantly, worth remembering it comes back in the indexing section below.

The standard example: a table of (StudentID, Subject, Instructor), where a student can take a subject from several possible instructors, but each instructor only ever teaches one subject.

StudentID | Subject  | Instructor
101    | DBMS   | Mehta
101    | OS    | Rao
102    | DBMS   | Mehta
103    | DBMS   | Singh

-- Instructor -> Subject holds: Mehta only teaches DBMS, Rao only OS, Singh only DBMS.
-- But Instructor is not a candidate key here, StudentID+Instructor and
-- StudentID+Subject are, so this violates BCNF while still satisfying 3NF.

This table is already in 3NF, every column belongs to some candidate key, so there's no non-key column left to have a transitive dependency at all. It still violates BCNF, because Instructor determines Subject (a real functional dependency) and Instructor isn't a superkey. Fix: split into (Instructor, Subject) and (StudentID, Instructor).

Is it a real problem or a textbook one? Mostly textbook, honestly. Clean BCNF violations that don't also break 3NF are rare in schemas I've actually seen. I'd tell a fresher not to lose sleep memorizing the distinction beyond being able to draw this one example on a whiteboard, interviewers keep asking it anyway because it's a clean way to check whether you understand functional dependencies or just pattern-match normal-form names.

2PL splits a transaction's lock activity into two phases. In the growing phase, it can acquire new locks but never release any. Once it releases its first lock, it enters the shrinking phase and can only release locks from then on, never acquire new ones. That discipline is what actually guarantees serializable schedules.

Plain "lock what you touch, release when you're done with it" doesn't give you that guarantee. Release a lock early and grab a new one later in the same transaction, and you can still produce a non-serializable interleaving with another transaction. Strict 2PL, the variant essentially every real DBMS actually runs, holds all exclusive locks until commit or rollback rather than releasing early once the shrinking phase technically begins, which is specifically what prevents one transaction's rollback from forcing a cascading rollback of others that read its uncommitted writes.

A deadlock happens when transaction A holds a lock B is waiting for, and B holds a lock A is waiting for, and neither can proceed, a cycle with no natural way out. Most engines run deadlock detection by walking a wait-for graph looking for exactly that kind of cycle. Find one, pick a victim, usually whichever transaction has done the least work or would be cheapest to roll back, kill it, and let the other one continue.

MySQL's InnoDB runs this check immediately by default rather than waiting for a timeout, though it still keeps a fallback lock-wait timeout (innodb_lock_wait_timeout, 50 seconds by default) for genuine waits that aren't part of a cycle at all, a lock held by a transaction that's just slow, not deadlocked. I don't have a clean number on how often real production deadlocks come from actual application bugs versus just unlucky timing under load, my guess is it's mostly the former, poor lock ordering, but that's a guess, not something I've measured.

Cost. True serializability needs either heavy lock contention under classic 2PL, or an optimistic approach like Postgres's serializable snapshot isolation, which allows transactions to proceed and then aborts and retries the ones that turn out to conflict, overhead either way. Most engines default to something weaker instead: Postgres, Oracle, and SQL Server default to Read Committed, MySQL's InnoDB defaults to Repeatable Read.

My honestly-could-be-wrong take: most fresher interviewers ask "name the four isolation levels" and stop there, without asking anyone to reason about why Read Committed is a genuinely acceptable default for most web applications. That's a missed opportunity. The reasoning question is what actually separates someone who memorized a table from someone who understands what they're trading away by not paying for Serializable everywhere.

Yes, on the write side specifically. Every INSERT, UPDATE, or DELETE has to update every index defined on that table, not just the base row, so a table with seven indexes doing 500,000 inserts a day is generating roughly 3.5 million extra index-entry writes a day on top of the base table writes alone, before counting any page splits or rebalancing that index maintenance triggers.

The query planner having more index options to weigh is a real cost too, but it's usually negligible next to the write amplification. Reads almost never get slower from an extra index existing, they just stop benefiting from indexes nobody's queries actually use.

Half true, half a decade-old talking point that's stuck around past its expiration date. Postgres and MySQL both scale to genuinely enormous datasets with proper partitioning, sharding, and read replicas, "SQL doesn't scale" was never quite accurate, it was more that scaling it took real engineering effort that a lot of teams didn't want to do.

Actually, let me correct myself mid-thought, that's not quite the full picture either. Some NoSQL stores, Cassandra especially, genuinely built their consistency and partitioning model around horizontal writes from day one in a way relational engines historically didn't bolt on as cleanly. NoSQL's real advantage isn't raw scale so much as schema flexibility for data that doesn't map cleanly to rows, plus horizontal write-scaling being closer to a default instead of something you engineer in later. The honest answer depends more on your access pattern than on either camp having some inherent scaling superiority.

Write-ahead logging means the database writes a description of a change to a durable, append-only log before it modifies the actual data pages in memory or on disk. The rule is strict, the log record for a change has to hit durable storage before the transaction is considered committed, and typically before the matching data page is flushed to disk. That ordering is what makes crash recovery possible.

When the database restarts after a crash, it replays the log from the last checkpoint forward. Any committed transaction whose data changes hadn't reached disk yet gets redone from the log, and any transaction that was in progress but never committed gets its partial changes undone, reconstructing exactly the state as of the last confirmed commit, nothing more and nothing less.

The reason this beats fsyncing data pages on every write is performance: sequential appends to a log file are cheap compared to random writes scattered across data pages, so the database can defer the expensive random-write flushing of actual data pages, done in the background via checkpointing, while still guaranteeing durability, because the log alone is enough to reconstruct any lost state. It's also why disabling fsync on the log, something people try for a quick benchmark win, is one of the fastest ways to silently lose committed data on an unclean shutdown.

Two-phase commit coordinates a transaction across multiple independent databases so it either commits everywhere or nowhere. In the prepare phase, a coordinator asks every participant if it can commit, and each one does whatever's needed to guarantee it can (writing to its own log, holding locks) and replies yes or no without actually committing yet. If everyone says yes, the coordinator sends a commit message in the second phase and each participant finalizes.

The painful failure mode is what happens if the coordinator crashes after some participants said yes, and are sitting in a prepared state holding locks, but before it sends the final commit or abort. Those participants can't unilaterally decide anything, because for all they know another participant said no, so they sit blocked, holding locks, until the coordinator recovers and tells them what happened. That's the classic blocking problem with 2PC, and it's a real production issue, a downed coordinator can leave locks held indefinitely across multiple databases.

That's why most systems avoid true distributed 2PC where they can, favoring either a single database with proper transactions, or the saga pattern for cross-service consistency, where each step commits independently and a compensating action fires if a later step fails. Sagas trade strict atomicity for availability and avoid the blocking coordinator problem, at the cost of the system passing through visibly inconsistent intermediate states the application has to be built to tolerate.

Standard row locks only protect rows that already exist, they don't stop a brand new row from being inserted into a range you've already read, which is exactly what causes a phantom read: you run a range query, see 10 rows, run it again in the same transaction, and now there are 12 because someone inserted new rows in between that weren't locked, since they didn't exist yet at read time.

InnoDB's answer under Repeatable Read is next-key locking, combining a lock on the actual row with a lock on the gap immediately before it. So `SELECT * FROM orders WHERE amount BETWEEN 100 AND 200 FOR UPDATE` doesn't just lock the existing rows in that range, it locks the gaps between them too, blocking any other transaction from inserting a new row into that range until yours commits. That's how InnoDB blocks phantom reads under Repeatable Read without escalating to full Serializable, technically a deviation from the strict textbook definition of Repeatable Read, but it's the behavior MySQL actually ships.

The practical gotcha is that gap locks are a common, non-obvious source of deadlocks and blocked inserts that don't look like they should conflict at all. Two transactions inserting different, non-overlapping values into the same range can still deadlock on each other's gap locks, which is a genuine debugging trap since the rows involved don't overlap, it only makes sense once you realize the locks are on the gaps, not the values.

The hard part of sharding isn't distributing data, it's picking a key that keeps both writes and queries evenly spread without turning every common query into a cross-shard fan-out. Shard by a low-cardinality or unevenly distributed column, say tenant_id where one enterprise customer is 40% of your traffic, and you get a hot shard that adding more shards can't fix, because that customer's data and load are pinned to a single node.

Shard by something like a hash of the primary key instead and you get even distribution, but now any query that isn't a single-key lookup, "give me all orders for this customer" if that customer's data can span shards, has to fan out to every shard and merge results in the application, which erodes the latency and throughput gains sharding was supposed to provide in the first place.

The other hard edge is what happens when the access pattern was wrong from the start and you need to reshard, moving data to change the key or rebalance shards while the system stays live, without downtime and without losing writes mid-migration. That usually means dual-writing to old and new layouts for a transition period, backfilling historical data, and only cutting reads over once you've verified the new layout is consistent, a multi-week operational project, not a config change, which is exactly why teams delay sharding as long as possible and reach for read replicas and vertical scaling first.

In an async master-replica setup, the primary applies a write and treats it as durable as soon as it's committed locally, then streams the change to replicas afterward, so there's always a window, sometimes milliseconds, sometimes seconds under load, where a replica doesn't yet reflect that write. Lag grows when a replica can't keep up, a single-threaded replication apply process falling behind a high write rate, a long-running query blocking apply, or just network latency and bandwidth limits between primary and replica.

The classic bug this causes is read-your-own-write: a user submits a form, the application writes to the primary, then immediately redirects to a page reading from a replica that hasn't caught up yet, so the user's own change appears to have failed or vanished. It's common enough that a lot of frameworks have an explicit pattern for it, either routing reads immediately following a write to the primary for a short window, or having the application track the primary's log position (LSN or GTID) and making the replica wait until it's caught up to that position before answering.

Monitoring replication lag matters operationally too. If replicas are used for failover, a badly lagged replica promoted during an incident will actually lose the most recent committed writes that never made it across before the primary died, so any system that assumes "the replica is basically current" needs a real answer for how stale is too stale to promote from.