DBMS Interview Questions and Answers

Answer

Frame this as the problem the file system creates, not as history. With flat files every capability has to be rebuilt by the application. First, concurrency.

Two recruiters open applications.csv, both edit, both save, and the second save silently overwrites the first. A DBMS gives you locking and transactions so concurrent writers serialise correctly. Second, atomicity and recovery.

If the process dies halfway through writing a row, a CSV is left truncated with no way to know which records committed. A DBMS uses a write ahead log so an interrupted write is either fully applied or fully rolled back at restart. Third, integrity.

Nothing stops a CSV holding an application whose job_id does not exist, or two rows with the same application_id. Foreign keys, primary keys, NOT NULL and CHECK constraints push those rules into the engine so every client obeys them, not just the one careful script. Fourth, query efficiency.

Finding all applications for one job in a 4 million row CSV is a full scan every time. An index turns it into a few page reads. Fifth, controlled access, backup, and a declarative query language so you say what you want rather than how to loop.

RDBMS is the subset of DBMS that stores data as relations with keys and constraints and supports SQL. Older or simpler DBMS products, and plain key value stores, may store hierarchies or documents without enforcing relational integrity. The honest one line summary: a DBMS is what you get when you stop writing the same concurrency, recovery and integrity code in every application.

/* The file system version of a lost update */
Recruiter A reads applications.csv  (1000 rows)
Recruiter B reads applications.csv  (1000 rows)
A edits row 42, saves            -> file now has A's change
B edits row 900, saves           -> file overwrites A's change

/* The DBMS version */
BEGIN;
  UPDATE applications SET status = 'shortlisted' WHERE id = 42;
COMMIT;   /* row locked for the duration, B's update to 900 proceeds independently */

Answer

Degree is the number of attributes, here 5, and it is a property of the schema. Cardinality is the number of tuples, here 5000, and it is a property of the current instance, so it changes with every insert. Candidates routinely swap these two, which is why the question is asked in this shape.

The vocabulary underneath: a relation is the table, a tuple is a row, an attribute is a column, and a domain is the set of permitted values for an attribute, for example status has the domain applied, shortlisted, rejected, hired. In the strict relational model a relation is a set of tuples, and a set has no duplicate members and no ordering, so two identical rows cannot exist and there is no first row. That is the theory.

In practice SQL tables are multisets, so an actual MySQL or PostgreSQL table with no unique constraint will happily store the same row twice, which is exactly why you declare a primary key. Similarly SQL result sets are ordered only when you write ORDER BY, and an interviewer will test that by asking whether a query without ORDER BY returns rows in insertion order. It does not, and code that relies on it breaks the day the optimiser switches from a table scan to an index scan. Also worth knowing: relation schema means the structure applications(app_id, ...), relation instance means the rows at a point in time, and NULL means the value is unknown or inapplicable rather than zero or empty string.

applications
app_id | candidate_id | job_id | applied_on | status
=======+==============+========+============+============
  1    |   2201       |  17    | 2026-02-01 | applied
  2    |   2201       |  19    | 2026-02-03 | shortlisted
  3    |   2450       |  17    | 2026-02-03 | rejected

Degree      = 5   (number of attributes, fixed by the schema)
Cardinality = 3   (number of tuples, changes with every INSERT)
Domain(status) = { applied, shortlisted, rejected, hired }

Answer

A super key is any attribute set that uniquely identifies a tuple. Here emp_id alone is a super key, and so is every superset of it: (emp_id, email), (emp_id, first_name, dept_id) and so on. Super keys are cheap and mostly useless, there are exponentially many.

A candidate key is a minimal super key, meaning no attribute can be removed without losing uniqueness. This table has three: emp_id, pan and email, assuming email is unique per employee. The primary key is the one candidate key the designer picks, typically emp_id.

The remaining candidate keys, pan and email, become alternate keys and are enforced with UNIQUE constraints. A composite key is any key made of more than one attribute, common in junction tables, for example (emp_id, project_id) in employee_project. A foreign key is an attribute referencing the primary key of another relation, dept_id referencing department(dept_id).

A surrogate key is a system generated value with no business meaning, an AUTO_INCREMENT integer or a UUID, as opposed to a natural key which already exists in the business domain, like pan. Two rules interviewers use to catch guessing: a primary key cannot be NULL while an alternate key enforced by UNIQUE can be NULL, and in MySQL a UNIQUE index permits multiple NULLs because NULL is not equal to NULL, which surprises people who expect one NULL only. Also note that (emp_id, email) is a super key but not a candidate key, because it is not minimal, that specific distinction is the actual answer being tested.

CREATE TABLE employee (
  emp_id       INT AUTO_INCREMENT PRIMARY KEY,   /* surrogate primary key */
  pan          CHAR(10) UNIQUE,                  /* alternate key, natural */
  email        VARCHAR(120) UNIQUE,              /* alternate key */
  dept_id      INT NOT NULL,
  first_name   VARCHAR(60) NOT NULL,
  last_name    VARCHAR(60),
  joining_date DATE NOT NULL,
  FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);

Super keys      : {emp_id}, {pan}, {email}, {emp_id,email}, {pan,dept_id}, ...
Candidate keys  : {emp_id}, {pan}, {email}            /* minimal super keys */
Primary key     : {emp_id}
Alternate keys  : {pan}, {email}
Composite key   : {emp_id, project_id} in employee_project

Answer

This is the natural versus surrogate key judgement call, and Indian identifiers make it concrete. PAN looks perfect: 10 characters, unique, government issued, already known to the business. The problems are practical.

First, availability at insert time. A user signs up on Goodspace with a phone number and has not entered PAN yet, but a primary key cannot be NULL, so you cannot create the row. Any identifier that arrives later in the funnel cannot be the primary key.

Second, mutability. PAN is reissued after a name correction, and people genuinely do get a corrected PAN. Changing a primary key means cascading the update through every child table that references it.

A surrogate integer never changes. Third, propagation of sensitive data. If PAN is the primary key, it appears as a foreign key in applications, payments, audit logs, analytics exports and in every URL of the form /users/ABCDE1234F.

Under India's DPDP framework, personal identifiers should be minimised, not scattered. Aadhaar is worse: storing full Aadhaar numbers carries specific legal restrictions, and it must never become a join column. Fourth, size and index cost.

A CHAR(10) key is wider than a 4 byte INT, and in InnoDB every secondary index stores the primary key value in its leaf nodes, so a wide primary key inflates every index on the table. Fifth, correctness of your uniqueness assumption. Duplicate PANs exist in practice due to data entry errors and legacy issuance. The right design is a surrogate primary key, an AUTO_INCREMENT BIGINT or a UUID, with PAN kept as a nullable UNIQUE column, encrypted or tokenised, and validated by a CHECK on the format.

/* Wrong */
CREATE TABLE users (
  pan CHAR(10) PRIMARY KEY,
  name VARCHAR(100)
);
/* signup without PAN is impossible; PAN leaks into every FK and URL */

/* Right */
CREATE TABLE users (
  user_id BIGINT AUTO_INCREMENT PRIMARY KEY,     /* surrogate, stable, narrow */
  pan     CHAR(10) NULL UNIQUE,                  /* natural key kept as alternate */
  name    VARCHAR(100) NOT NULL,
  CONSTRAINT chk_pan CHECK (pan IS NULL OR pan REGEXP '^[A-Z]{5}[0-9]{4}[A-Z]$')
);

CREATE TABLE payments (
  payment_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id    BIGINT NOT NULL,                    /* FK carries no PII */
  FOREIGN KEY (user_id) REFERENCES users(user_id)
);

Answer

A foreign key is an attribute in a child relation whose values must exist as primary key values in a parent relation, or be NULL. Referential integrity is the guarantee that there are no orphan rows, no application pointing at a job_id that does not exist. The engine enforces it on insert, update and delete of both tables.

Referential actions decide what happens to children when the parent is deleted or its key updated. ON DELETE RESTRICT, and NO ACTION which behaves the same in MySQL, refuses the delete while any application still references the job, so the recruiter sees an error and has to deal with the applications first. This is the safest default for anything financial.

ON DELETE CASCADE deletes all 400 applications along with the job, which is convenient and dangerous: one DELETE can silently remove millions of rows in a chain of cascades, hold locks on all of them, and blow up your replication lag. ON DELETE SET NULL nulls the child job_id so the applications survive as orphans in a business sense, which requires the column to be nullable and usually means your model is wrong. In practice most production systems do not hard delete at all, they set a deleted_at timestamp, because applications are legally and analytically valuable after the job closes. Engine specifics interviewers probe: in MySQL foreign keys work only in InnoDB and are silently ignored by MyISAM, foreign key columns should be indexed and InnoDB creates that index automatically if you do not, cascading deletes do not fire row triggers in MySQL, and a foreign key adds a lock and a lookup on the parent row for every child insert, which is a real write cost on a high throughput table.

CREATE TABLE applications (
  app_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  job_id BIGINT NULL,
  candidate_id BIGINT NOT NULL,
  FOREIGN KEY (job_id) REFERENCES jobs(job_id) ON DELETE RESTRICT
);

DELETE FROM jobs WHERE job_id = 17;

/* RESTRICT  -> ERROR 1451: Cannot delete or update a parent row */
/* CASCADE   -> job row deleted AND all 400 application rows deleted */
/* SET NULL  -> job row deleted, 400 applications keep existing with job_id = NULL */

/* What production usually does instead */
UPDATE jobs SET deleted_at = NOW() WHERE job_id = 17;

Answer

Both come from the same root: SQL uses three valued logic, TRUE, FALSE and UNKNOWN, and NULL means the value is unknown rather than zero or empty string. COUNT(*) counts tuples. COUNT(salary) counts non NULL values of salary, so if 200 of 5000 employees have NULL salary you get 4800.

The same rule applies to SUM, AVG and MAX, they all ignore NULLs, which means AVG(salary) divides by 4800 and not by 5000, a real source of wrong dashboards. The NOT IN trap is sharper. x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL. The last comparison evaluates to UNKNOWN, and TRUE AND UNKNOWN is UNKNOWN, so the whole predicate is never TRUE and the WHERE clause filters out every row.

The query returns an empty result and looks like a data problem when it is a logic problem. NOT EXISTS does not have this behaviour because it tests row existence rather than value equality, which is why experienced engineers reach for NOT EXISTS by default. Related rules to state: NULL = NULL is UNKNOWN, so you must write IS NULL, two NULLs are treated as equal by GROUP BY and DISTINCT and by ORDER BY, a UNIQUE index allows multiple NULLs for the same reason, and in MySQL NULL sorts first ascending while PostgreSQL puts NULLs last by default unless you write NULLS FIRST. Use COALESCE to substitute a default, and prefer NOT NULL with a sensible default in the schema so you avoid the whole class.

employees
emp_id | salary
=======+========
  1    | 120000
  2    | NULL
  3    |  90000

SELECT COUNT(*)      FROM employees;   /* 3  */
SELECT COUNT(salary) FROM employees;   /* 2  */
SELECT AVG(salary)   FROM employees;   /* 105000, divides by 2 not 3 */

/* The NOT IN trap */
SELECT * FROM jobs
WHERE job_id NOT IN (SELECT job_id FROM applications);  /* 0 rows if any job_id is NULL */

/* Safe rewrite */
SELECT j.* FROM jobs j
WHERE NOT EXISTS (SELECT 1 FROM applications a WHERE a.job_id = j.job_id);

Answer

Entity types are the things you store, Candidate, Job, Company, Application, Interview. An entity is one instance, one specific candidate. Attributes describe entities and come in flavours that map differently to tables.

A simple attribute is atomic, like applied_on. A composite attribute has parts, address decomposing into line1, city, state, pincode, and you normally flatten it into separate columns. A multivalued attribute holds many values for one entity, a candidate having several skills or several phone numbers, and it cannot stay in the candidate row, it becomes its own table.

A derived attribute is computed rather than stored, experience_years derived from joining_date, and you either compute it in the query or maintain it as a generated column. A key attribute uniquely identifies the entity. A relationship type connects entity types, applies_to between Candidate and Job, with its own descriptive attributes like applied_on and status, which is why an M:N relationship becomes a table.

A weak entity is one that has no key of its own and cannot exist without an owner. Classic example: Interview_Round belongs to an Application and is identified as round 1, round 2 within that application, so round_no is only a partial key and the full identifier is (app_id, round_no). The relationship connecting a weak entity to its owner is the identifying relationship, drawn with a double diamond, and the weak entity always has total participation in it.

Dependent of an employee is the other standard example, dependent_name is unique only within one employee. Recognising a weak entity matters because it tells you the child table must carry the owner key as part of its primary key.

Candidate (candidate_id, name, address{line1, city, pincode}, phones{multivalued},
           dob, age /* derived */)

/* multivalued attribute becomes its own table */
CREATE TABLE candidate_skill (
  candidate_id BIGINT NOT NULL,
  skill        VARCHAR(60) NOT NULL,
  PRIMARY KEY (candidate_id, skill),
  FOREIGN KEY (candidate_id) REFERENCES candidate(candidate_id)
);

/* weak entity: round_no is only a partial key */
CREATE TABLE interview_round (
  app_id    BIGINT NOT NULL,
  round_no  INT    NOT NULL,
  scheduled DATETIME,
  PRIMARY KEY (app_id, round_no),        /* owner key + partial key */
  FOREIGN KEY (app_id) REFERENCES applications(app_id) ON DELETE CASCADE
);

Answer

Cardinality ratio says how many entities on each side a relationship connects: 1:1, 1:N, M:N. Participation says whether every entity must take part, total participation drawn as a double line, or may take part, partial participation drawn as a single line. Together they decide where the foreign key lives and whether the column is NOT NULL.

For 1:1, say Candidate to Resume where a candidate has at most one active resume, you put the foreign key on either side and add a UNIQUE constraint on it, and you put it on the side with total participation so it can be NOT NULL. For 1:N, one Job receives many Applications, the foreign key goes on the many side, applications.job_id, never on the one side, because a column cannot hold a list. If every application must belong to a job, that is total participation on the application side and job_id is NOT NULL.

For M:N, a Candidate can have many Skills and a Skill belongs to many Candidates, you must create a junction table whose primary key is the pair of foreign keys, and any attribute of the relationship, proficiency, years, lives on that table. Structural constraints in min max notation say the same thing more precisely: Application participates in applies_to as (1,1), Job as (0,N). The practical translation interviewers want: 1:N puts the FK on the many side, M:N always needs a third table, total participation becomes NOT NULL, and a relationship with its own attributes is a sign that it deserves to be an entity in its own right, which is exactly why Application exists at all instead of a bare candidate to job link.

Candidate (1) ====== applies ====== (N) Application (N) ====== for ====== (1) Job

/* 1:N, FK on the many side, total participation -> NOT NULL */
CREATE TABLE applications (
  app_id       BIGINT AUTO_INCREMENT PRIMARY KEY,
  candidate_id BIGINT NOT NULL,      /* (1,1) participation */
  job_id       BIGINT NOT NULL,
  UNIQUE KEY uq_one_apply (candidate_id, job_id)   /* no duplicate applications */
);

/* M:N -> junction table, PK is the pair */
CREATE TABLE candidate_skill (
  candidate_id BIGINT NOT NULL,
  skill_id     INT    NOT NULL,
  years        DECIMAL(3,1),         /* relationship attribute */
  PRIMARY KEY (candidate_id, skill_id)
);

/* 1:1 -> FK plus UNIQUE */
ALTER TABLE resume ADD UNIQUE KEY uq_candidate (candidate_id);

Answer

The conversion follows a fixed algorithm and interviewers want to hear the algorithm, not just the final SQL. Step 1, every strong entity type becomes a table with its key attribute as primary key: company, job, candidate. Step 2, composite attributes are flattened into their components, address becomes address_line, city, pincode.

Step 3, multivalued attributes become separate tables keyed on the owner key plus the value. Step 4, a 1:N relationship does not become a table, you push the primary key of the one side into the many side as a foreign key, so job gets company_id NOT NULL, and any attribute of the relationship, posted_on, also moves onto job. Step 5, an M:N relationship always becomes its own table whose primary key is the union of both participating keys, and whose non key attributes are the relationship attributes, so applies becomes applications(candidate_id, job_id, applied_on) with primary key (candidate_id, job_id).

If the business allows a candidate to reapply after rejection then the pair is no longer unique and you need a surrogate app_id with a partial unique index, which is a good detail to raise unprompted. Step 6, a weak entity becomes a table whose primary key is the owner key plus the partial key, so interview_round(app_id, round_no, ...) with primary key (app_id, round_no) and a cascading foreign key to applications, since a weak entity cannot outlive its owner. Step 7, 1:1 relationships become a foreign key with UNIQUE on the total participation side.

Step 8, an ISA hierarchy is handled separately by one of the three strategies. Finally you add NOT NULL for total participation and indexes on every foreign key.

CREATE TABLE company (
  company_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(150) NOT NULL,
  city       VARCHAR(60)
);

/* 1:N  -> FK pushed to the many side, no separate table */
CREATE TABLE job (
  job_id     BIGINT AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  title      VARCHAR(150) NOT NULL,
  posted_on  DATE NOT NULL,
  FOREIGN KEY (company_id) REFERENCES company(company_id)
);

/* M:N  -> own table, PK = union of both keys, relationship attr rides along */
CREATE TABLE applications (
  candidate_id BIGINT NOT NULL,
  job_id       BIGINT NOT NULL,
  applied_on   DATETIME NOT NULL,
  status       VARCHAR(20) NOT NULL DEFAULT 'applied',
  PRIMARY KEY (candidate_id, job_id)
);

/* weak entity -> owner key + partial key */
CREATE TABLE interview_round (
  candidate_id BIGINT NOT NULL,
  job_id       BIGINT NOT NULL,
  round_no     INT    NOT NULL,
  verdict      VARCHAR(20),
  PRIMARY KEY (candidate_id, job_id, round_no),
  FOREIGN KEY (candidate_id, job_id)
    REFERENCES applications(candidate_id, job_id) ON DELETE CASCADE
);

Answer

Specialisation is top down: you have Employee, you notice that some employees have contract_end_date and agency, others have pf_number and stock_units, so you split into subclasses. Generalisation is bottom up: you already have Contractor and FullTimeEmployee, you notice shared attributes, and you factor out a superclass Employee. The relationship is called ISA, and it carries two constraints.

Disjointness says whether an entity can belong to more than one subclass, disjoint versus overlapping. Completeness says whether every superclass entity must belong to some subclass, total versus partial. Attribute inheritance means subclasses inherit all superclass attributes plus the key.

Three mapping strategies. One, table per hierarchy, also called single table: one employee table with all columns plus a discriminator emp_type, and subclass specific columns left nullable. Simplest, fastest to query, but the schema fills with NULLs and you cannot enforce NOT NULL on subclass attributes without a CHECK keyed on the discriminator.

Two, table per type, the usual choice: employee holds shared attributes and the primary key, contractor and full_time_employee each hold the same primary key as both PK and FK plus their own attributes. Cleanly normalised, enforces NOT NULL correctly, but every read joins. Three, table per concrete class: no employee table at all, each subclass table repeats the shared columns.

Reads of one subclass are fast but a query across all employees becomes a UNION, and you lose a single key space so foreign keys from other tables cannot reference an employee generically. Pick single table when subclasses differ by two or three columns and you query them together, pick table per type when subclasses are genuinely different and other tables need to reference the superclass.

/* Strategy 2: table per type, the default choice */
CREATE TABLE employee (
  emp_id   BIGINT AUTO_INCREMENT PRIMARY KEY,
  name     VARCHAR(100) NOT NULL,
  emp_type ENUM('contractor','fulltime') NOT NULL   /* discriminator */
);

CREATE TABLE contractor (
  emp_id           BIGINT PRIMARY KEY,   /* PK and FK together */
  agency           VARCHAR(100) NOT NULL,
  contract_end     DATE NOT NULL,
  FOREIGN KEY (emp_id) REFERENCES employee(emp_id) ON DELETE CASCADE
);

CREATE TABLE full_time_employee (
  emp_id       BIGINT PRIMARY KEY,
  pf_number    VARCHAR(30) NOT NULL,
  stock_units  INT DEFAULT 0,
  FOREIGN KEY (emp_id) REFERENCES employee(emp_id) ON DELETE CASCADE
);

/* Strategy 1 alternative: one table, nullable columns, CHECK on discriminator */
ALTER TABLE employee
  ADD CONSTRAINT chk_contractor
  CHECK (emp_type <> 'contractor' OR agency IS NOT NULL);

Answer

First normal form requires every attribute to hold a single atomic value from its domain, with no repeating groups and no multivalued cells. This table violates it twice. The phone column holding 9876543210,9812345678 is a multivalued cell.

The item1, item2, item3 columns are a repeating group, the same logical attribute smeared across three physical columns. The problems this causes are not theoretical. You cannot query it: finding all orders containing item SKU-88 needs item1 = 'SKU-88' OR item2 = ...

OR item3 = ..., and no index helps. You cannot count: SUM over three columns with NULLs is fragile. You cannot extend: the fourth item has nowhere to go without an ALTER TABLE, and the fourth phone number requires string surgery.

Updating one phone number means parsing and rewriting the whole string, and a concurrent update loses the other edit entirely. The fix is to split each repeating group into its own row in a child table. Phones become customer_phone(customer_id, phone) with primary key on the pair.

Items become order_item(order_id, sku, qty, unit_price) with primary key (order_id, sku). Now every cell holds one value, the item search becomes an indexed equality lookup, and adding a fifth item is an INSERT. Note what 1NF does not fix: order_item still repeats the customer name if you left it there, and the unit price may still be duplicated per SKU, those are 2NF and 3NF problems. Also note the modern caveat: JSON columns in MySQL 8 and PostgreSQL JSONB technically break 1NF, and they are acceptable for genuinely schemaless payloads, but they are not an excuse to store a list of order items you intend to query and join on.

/* Violates 1NF: repeating group + multivalued cell */
order_id | customer | phone                       | item1  | item2  | item3
=========+==========+=============================+========+========+=======
 5001    | Priya    | 9876543210,9812345678       | SKU-88 | SKU-12 | NULL
 5002    | Arjun    | 9900112233                  | SKU-12 | NULL   | NULL

/* 1NF: one value per cell, repeating group becomes rows */
orders                      order_item
order_id | customer_id      order_id | sku    | qty
=========+============      =========+========+====
 5001    |  201              5001    | SKU-88 |  1
 5002    |  202              5001    | SKU-12 |  2
                             5002    | SKU-12 |  1

customer_phone
customer_id | phone
============+============
   201      | 9876543210
   201      | 9812345678

CREATE TABLE order_item (
  order_id BIGINT NOT NULL,
  sku      VARCHAR(20) NOT NULL,
  qty      INT NOT NULL,
  PRIMARY KEY (order_id, sku)
);

Answer

It is in 1NF but not in 2NF. Second normal form says the relation is in 1NF and every non prime attribute is fully functionally dependent on the whole candidate key, with no partial dependency on a proper subset of it. The key here is composite, (student_id, course_id).

Check each non prime attribute. Grade genuinely depends on both, the same student has different grades in different courses, that is fine. Student_name depends on student_id alone, a proper subset of the key, that is a partial dependency.

Course_fee depends on course_id alone, another partial dependency. Note that 2NF violations are only possible when the key is composite, a single attribute key has no proper subsets, so any 1NF relation with a single attribute primary key is automatically in 2NF, and stating that rule scores points. The anomalies are concrete.

Update anomaly: a student changes their name and you must update every enrolment row for that student, and if you miss one the database now claims two different names for the same student_id. Insertion anomaly: you cannot record a new course and its fee until at least one student enrols, because student_id is part of the primary key and cannot be NULL. Deletion anomaly: when the last student drops a course you lose the course_fee entirely, the fact disappears with the enrolment.

The decomposition splits by determinant: student(student_id, student_name), course(course_id, course_fee) and enrolment(student_id, course_id, grade). This is lossless because each new relation shares a key with enrolment, and dependency preserving because every original FD lives inside one of the three tables.

enrolment  (violates 2NF)
student_id | course_id | student_name | course_fee | grade
===========+===========+==============+============+======
   S101    |  CS301    | Priya        |   45000    |  A
   S101    |  CS302    | Priya        |   38000    |  B
   S102    |  CS301    | Arjun        |   45000    |  A

FDs:
  student_id            -> student_name    /* PARTIAL: subset of the key */
  course_id             -> course_fee      /* PARTIAL: subset of the key */
  student_id, course_id -> grade           /* full dependency, fine */

After decomposition (2NF):
student(student_id, student_name)
course (course_id, course_fee)
enrolment(student_id, course_id, grade)

/* insertion anomaly gone: a new course can exist with zero enrolments */
INSERT INTO course VALUES ('CS303', 52000);

Answer

The key is emp_id, single attribute, so the relation is trivially in 2NF, there are no partial dependencies possible. It still violates 3NF. Third normal form requires that for every non trivial functional dependency X to Y, either X is a super key or Y is a prime attribute.

Here emp_id determines dept_id, and dept_id determines dept_name and dept_head, so emp_id determines dept_name transitively through a non key attribute. Dept_id is not a super key and dept_name is not prime, so the FD dept_id to dept_name breaks 3NF. The anomalies follow directly.

Update anomaly: the Engineering department head changes and you must update every employee row in that department, thousands of rows, and any missed row leaves the database asserting two different heads for one department, which is an inconsistency the schema itself permits. Insertion anomaly: you cannot create a new department until you hire its first employee, because emp_id is the key. Deletion anomaly: if the only employee in the Legal department resigns and you delete the row, the existence of the Legal department, its name and its head all vanish.

The fix is to split at the determinant: employee(emp_id, emp_name, dept_id) with a foreign key, and department(dept_id, dept_name, dept_head). Now the department head is stored exactly once, changing it is a single row update, a department can exist with no employees, and deleting the last employee leaves the department intact. The decomposition is lossless because dept_id is a key of department, and it preserves both original dependencies. The one liner examiners like: 1NF removes repeating groups, 2NF removes partial dependency, 3NF removes transitive dependency, and each removes a specific class of redundancy driven anomaly.

employee  (2NF, violates 3NF)
emp_id | emp_name | dept_id | dept_name   | dept_head
=======+==========+=========+=============+===========
  E1   | Priya    |  D10    | Engineering | Meera
  E2   | Arjun    |  D10    | Engineering | Meera
  E3   | Kavya    |  D20    | Legal       | Rohit

FDs:
  emp_id  -> emp_name, dept_id
  dept_id -> dept_name, dept_head      /* transitive via a non key attribute */

UPDATE employee SET dept_head='Nikhil' WHERE dept_id='D10';  /* touches every row */

After decomposition (3NF):
employee  (emp_id, emp_name, dept_id)
department(dept_id, dept_name, dept_head)

UPDATE department SET dept_head='Nikhil' WHERE dept_id='D10';  /* one row */

Answer

An index is a separate, ordered data structure, in almost every relational engine a B+ tree, that maps column values to row locations. Without it, WHERE job_id = 17 is a full table scan, the engine reads every page of a 4 million row table. With it, the engine descends three or four levels of the tree and reads only the matching leaf entries, then fetches the actual rows.

The read win is enormous and it is why the question is never really about reads. The cost is on writes. Every INSERT must add an entry to every index on the table, in the correct sorted position, which may split a leaf page.

Every UPDATE that touches an indexed column must delete the old index entry and insert the new one. Every DELETE must remove entries from all indexes. So a table with five indexes does roughly six structured writes per insert instead of one, plus the extra page splits, plus more pages to keep in the buffer pool, plus more redo in the write ahead log.

On a festival sale evening when order volume is ten times normal, that difference is the gap between keeping up and building a queue. Indexes also consume disk, often more than the table itself on a wide index set, and they slow bulk loads, which is why a standard ETL trick is to drop indexes, load, and recreate them. The practical rule: index columns you filter, join or sort on, do not index columns you only ever project, and drop indexes nobody uses. Both MySQL 8 with performance_schema and PostgreSQL with pg_stat_user_indexes will tell you which indexes have never been read.

CREATE INDEX idx_applications_job ON applications (job_id);

/* read path before and after */
SELECT * FROM applications WHERE job_id = 17;
  before: full scan, ~4,000,000 rows examined
  after : index lookup, ~400 rows examined

/* write cost: 1 row insert = 1 heap write + N index writes */
INSERT INTO applications (candidate_id, job_id, applied_on) VALUES (2201, 17, NOW());

/* find indexes nobody uses (PostgreSQL) */
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

Answer

A clustered index determines the physical order of the rows on disk, which means the table itself is stored inside the index. There can be only one, because rows can be sorted one way. A non clustered, or secondary, index is a separate structure whose leaves hold the indexed values and a pointer back to the row.

In InnoDB the clustered index is the primary key and it is not optional: if you do not define a primary key InnoDB uses the first suitable UNIQUE NOT NULL index, and failing that generates a hidden 6 byte row id. The leaf pages of the clustered index contain the full rows. Secondary index leaves do not store a physical row pointer, they store the primary key value.

So a query filtered by a secondary index does two traversals: descend the secondary index to get the primary key, then descend the clustered index to fetch the row. This is the bookmark lookup, and it is why a secondary index on a huge table can still be slower than expected when it matches many rows, and why the optimiser sometimes ignores your index and scans instead. Consequences worth stating: keep the primary key narrow, because it is copied into every secondary index leaf, which is a second reason a CHAR(36) UUID primary key is expensive.

Prefer a monotonically increasing primary key so inserts append to the rightmost leaf instead of splitting pages all over the tree, which is why random UUIDv4 keys cause write amplification and UUIDv7 or an AUTO_INCREMENT BIGINT is preferred. PostgreSQL differs: it has no clustered index, its heap is unordered, all indexes point at a tuple id, and CLUSTER is a one time physical reorder that is not maintained.

/* InnoDB clustered index (the table itself), ordered by PK */
PK=1 -> [1, 'Priya',  'priya@x.com', 2026-01-02]
PK=2 -> [2, 'Arjun',  'arjun@x.com', 2026-01-03]

/* secondary index on email: leaf stores the PK, not a row pointer */
'arjun@x.com' -> 2
'priya@x.com' -> 1

SELECT * FROM users WHERE email = 'arjun@x.com';
  1. traverse idx_email  -> PK 2
  2. traverse clustered  -> full row       /* bookmark lookup */

/* one traversal only, because the index covers the query */
SELECT user_id FROM users WHERE email = 'arjun@x.com';

Answer

Atomicity means a transaction is all or nothing. Without it: a UPI transfer debits ₹5000 from the sender, the process is killed before the credit, and the money is gone. The engine provides it with undo logs, a rollback replays the undo records and restores the pre transaction state.

Consistency means a transaction moves the database from one valid state to another, valid meaning every declared constraint holds, foreign keys, unique keys, CHECK constraints and any application invariant. Without it: an application row survives pointing to a deleted job, and every report that joins them silently drops or duplicates rows. Note that consistency is partly your responsibility, the engine enforces only what you declared.

Isolation means concurrent transactions do not see each other's uncommitted intermediate states, and the outcome is as if they ran in some serial order. Without it: two ticket booking transactions both read seats_left = 1 and both write 0, and two passengers hold the same seat. This is the property with a dial on it, isolation levels, because full serialisability costs throughput.

Durability means once COMMIT returns, the change survives a crash, a power cut or an OS panic. Without it: the payment gateway got a success response, the server lost power two seconds later, and the payment row is not there on restart while the customer's bank has already debited them. Durability is implemented by write ahead logging with an fsync of the log before the commit acknowledgement, which is why innodb_flush_log_at_trx_commit = 1 is the durable setting and 2 or 0 trade durability for throughput. Say the failure for each property rather than just the definition, that is what separates a scoring answer here.

/* Atomicity: both statements or neither */
START TRANSACTION;
  UPDATE wallet SET balance = balance - 5000 WHERE user_id = 101;
  UPDATE wallet SET balance = balance + 5000 WHERE user_id = 202;
COMMIT;    /* crash before COMMIT -> undo log rolls the debit back */

/* Isolation missing: lost update on seat inventory */
T1: SELECT seats_left FROM trip WHERE id=9;   /* 1 */
T2: SELECT seats_left FROM trip WHERE id=9;   /* 1 */
T1: UPDATE trip SET seats_left = 0 WHERE id=9;
T2: UPDATE trip SET seats_left = 0 WHERE id=9;   /* two passengers, one seat */

/* Durability knob in MySQL */
SET GLOBAL innodb_flush_log_at_trx_commit = 1;  /* fsync on every commit */

Answer

A transaction moves through five states. Active, from the first statement onward, while reads and writes execute. Partially committed, after the final statement has executed but before the log records are safely on disk.

Committed, once the log is flushed and the commit record is durable, at which point the effects are permanent and visible to other transactions. Failed, when the transaction cannot proceed because of a constraint violation, a deadlock victim selection, a lock wait timeout or a system error. Aborted, after rollback has restored the previous state, from where the system may restart the transaction or kill it.

The transition candidates forget is partially committed to failed: the statements all succeeded but the commit itself failed, typically because the disk write failed, and this is precisely the window durability protects. The MySQL specific trap is autocommit and implicit commits. MySQL runs with autocommit = 1 by default, so every standalone statement is its own transaction and there is nothing to roll back afterwards.

You disable it with SET autocommit = 0 or open an explicit START TRANSACTION. More subtly, DDL in MySQL is not transactional in the way DML is: CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE and several others cause an implicit commit of the transaction currently in progress before they run. So a migration script that opens a transaction, updates rows, alters a table and then rolls back has already durably committed those updates, and the ROLLBACK does nothing.

PostgreSQL is different, its DDL is fully transactional, you can wrap ALTER TABLE and data changes in one transaction and roll the whole thing back, which is a genuine operational advantage for migrations. SAVEPOINT and ROLLBACK TO SAVEPOINT let you undo part of a transaction without ending it.

Active -> Partially Committed -> Committed
   |               |
   v               v
 Failed  <=========+
   |
   v
 Aborted   (rolled back, may restart)

/* MySQL implicit commit trap */
START TRANSACTION;
  UPDATE users SET plan='gold' WHERE user_id=101;
  ALTER TABLE users ADD COLUMN trial_ends DATE;   /* IMPLICIT COMMIT happens here */
ROLLBACK;                                          /* does nothing, update is durable */

/* Savepoints for partial rollback */
START TRANSACTION;
  INSERT INTO orders ...;
  SAVEPOINT after_order;
  INSERT INTO order_item ...;
  ROLLBACK TO SAVEPOINT after_order;   /* order kept, item undone */
COMMIT;

Answer

An inner join returns only rows where the predicate matches on both sides, so jobs INNER JOIN applications returns only jobs that have at least one application, and a job with three applications appears three times. A left outer join returns every row from the left table plus matched rows from the right, filling NULLs where nothing matched, so it answers list all jobs with their application count including the zero ones. A right outer join is the mirror image and is rarely written, most people flip the table order and use LEFT.

A full outer join returns unmatched rows from both sides, useful for reconciliation between two systems, and MySQL does not support it so you emulate it with a UNION of a left and a right join. A self join joins a table to itself with aliases, the standard case being employee to manager where manager_id references emp_id in the same table, and it must be a LEFT JOIN if you want the CEO whose manager_id is NULL to appear. A cross join is the Cartesian product with no predicate, m rows times n rows, occasionally deliberate for generating date grids but usually the symptom of a forgotten join condition.

The trap: SELECT ... FROM jobs j LEFT JOIN applications a ON a.job_id = j.job_id WHERE a.status = 'shortlisted' silently becomes an inner join, because for unmatched jobs a.status is NULL and NULL = 'shortlisted' is UNKNOWN, so those rows are filtered out after the join. The fix is to move the condition into the ON clause, where it restricts which rows are eligible to match rather than filtering the result. That distinction, ON filters before the outer join fills NULLs and WHERE filters after, is the single most useful thing to say here.

jobs                applications
job_id | title       app_id | job_id | status
=======+========     =======+========+============
  17   | SDE-1         901  |  17    | shortlisted
  18   | QA            902  |  17    | applied
  19   | Designer      903  |  18    | applied

INNER JOIN  -> 3 rows (job 19 missing)
LEFT  JOIN  -> 4 rows (job 19 appears with NULLs)
CROSS JOIN  -> 3 x 3 = 9 rows

/* BROKEN: WHERE on the right table kills the outer join */
SELECT j.title, a.status
FROM jobs j LEFT JOIN applications a ON a.job_id = j.job_id
WHERE a.status = 'shortlisted';           /* job 19 disappears */

/* CORRECT: predicate belongs in ON */
SELECT j.title, a.status
FROM jobs j LEFT JOIN applications a
  ON a.job_id = j.job_id AND a.status = 'shortlisted';

/* self join, LEFT so the CEO survives */
SELECT e.name, m.name AS manager
FROM employee e LEFT JOIN employee m ON e.manager_id = m.emp_id;

Answer

A functional dependency X to Y means that any two tuples agreeing on X must agree on Y, so X determines Y. The closure of an attribute set X, written X plus, is every attribute derivable from X using the FDs, computed by starting with X itself and repeatedly adding the right hand side of any FD whose left hand side is already contained in the set, until nothing changes. A set is a super key if its closure is all attributes, and a candidate key if it is a minimal such set.

The exam technique is to classify attributes first. Attributes that appear only on the left of every FD must be in every candidate key. Attributes that appear only on the right can never be in any candidate key.

Attributes on both sides, or on neither, are the candidates you test. Here every attribute appears on some right hand side, so no attribute is forced, and none appears only on the right either, so you test single attributes then pairs. A plus: start {A}, A gives BC so {A,B,C}, B gives D so {A,B,C,D}, CD gives E so {A,B,C,D,E}.

All attributes, so A is a super key, and since it is a single attribute it is minimal, hence a candidate key. B plus: {B}, B gives D so {B,D}, no FD has B or D or BD alone on the left beyond that, closure stops at {B,D}, not a key. E plus: E gives A, and A plus is everything, so E plus is everything, E is a candidate key.

CD plus: CD gives E, E gives A, A gives BC, so all five, and neither C nor D alone is a key, so CD is a candidate key minimally. So the candidate keys are A, E and CD. Prime attributes are therefore A, C, D and E, and B is the only non prime attribute.

R(A, B, C, D, E)
F = { A -> BC,  CD -> E,  B -> D,  E -> A }

Closure computation:
  {A}+  = A            -> A,B,C      -> +D (via B->D)   -> +E (via CD->E)  = ABCDE  KEY
  {B}+  = B            -> B,D                                              = BD     not key
  {C}+  = C                                                                = C      not key
  {D}+  = D                                                                = D      not key
  {E}+  = E            -> E,A        -> A,B,C           -> +D -> +E        = ABCDE  KEY
  {C,D}+= C,D          -> +E (CD->E) -> +A (E->A)       -> +B,C (A->BC)    = ABCDE  KEY
        C alone and D alone are not keys, so CD is minimal

Candidate keys : { A }, { E }, { C, D }
Prime attrs    : A, C, D, E
Non prime attrs: B

Answer

BCNF strengthens 3NF by removing the escape clause. 3NF says for every non trivial FD X to Y, either X is a super key or Y is a prime attribute. BCNF drops the second option entirely: X must be a super key, full stop. So the only relations that are 3NF but not BCNF are those where a non super key determines a prime attribute.

The classic example is a teaching allocation. Relation teaches(student, subject, teacher) with the business rules that each teacher teaches exactly one subject, and each student takes a given subject from exactly one teacher. The FDs are (student, subject) to teacher and teacher to subject.

Candidate keys are (student, subject) and (student, teacher). Every attribute is prime, student, subject and teacher all appear in some candidate key. So the FD teacher to subject satisfies 3NF because its right hand side, subject, is a prime attribute, even though teacher is not a super key.

It violates BCNF because teacher is not a super key. The redundancy is real and visible: the fact that Meera teaches DBMS is repeated once per student she teaches, so if Meera switches to Operating Systems you must update every row and a missed row makes the database self contradictory. The BCNF decomposition splits on the offending FD: teacher_subject(teacher, subject) with teacher as key, and student_teacher(student, teacher).

This is lossless because teacher is a key of the first relation. But it is not dependency preserving, the FD (student, subject) to teacher spans both relations and can no longer be checked locally, so nothing stops two rows asserting that one student takes DBMS from two different teachers. That trade off is the whole point of the question.

teaches(student, subject, teacher)
student | subject | teacher
========+=========+========
 S1     | DBMS    | Meera
 S2     | DBMS    | Meera
 S1     | OS      | Rohit

FDs:  (student, subject) -> teacher
      teacher            -> subject

Candidate keys: (student, subject), (student, teacher)
Prime attributes: student, subject, teacher   /* all of them */

3NF?   teacher -> subject : teacher is not a super key, BUT subject IS prime -> allowed
BCNF?  teacher -> subject : teacher is not a super key                      -> VIOLATION

BCNF decomposition:
  R1(teacher, subject)     key = teacher
  R2(student, teacher)     key = (student, teacher)

Lossless      : yes, teacher is a key of R1
Dep preserving: NO, (student, subject) -> teacher is lost

Answer

A decomposition of R into R1 and R2 is lossless if the natural join of R1 and R2 returns exactly the original rows, no more and no fewer. Lossy is a misleading name, the failure mode is spurious tuples, you get back extra rows that were never in the original, which is worse than losing rows because the data now asserts facts that are false. The test for a binary decomposition is simple: the decomposition is lossless if the common attributes R1 intersect R2 form a super key of at least one of R1 or R2.

If the shared column determines everything in one of the two pieces, the join can only reassemble the original pairs. For decomposition into more than two relations you run the chase algorithm on a tableau, but the binary rule is what gets asked. A decomposition is dependency preserving if the union of the FDs that can be checked locally on each relation implies the entire original FD set.

This matters operationally: if an FD spans two tables, you cannot enforce it with a UNIQUE constraint, you need an application check or a trigger and a join on every write, and violations creep in. The two guarantees are not always both achievable. 3NF decomposition using the synthesis algorithm is always both lossless and dependency preserving. BCNF decomposition is always lossless but may sacrifice dependency preservation, exactly as in the teacher subject example.

That is the real reason production schemas stop at 3NF: 3NF is enough to remove the dominant redundancy anomalies, and the residual redundancy BCNF would remove is usually smaller than the cost of losing a constraint you can no longer enforce declaratively. Most well designed OLTP schemas are in 3NF and coincidentally in BCNF anyway, because tables with overlapping composite candidate keys are rare.

R(emp_id, dept_id, dept_head)
Decompose into  R1(emp_id, dept_id)  and  R2(dept_id, dept_head)

R1 intersect R2 = { dept_id }
dept_id -> dept_head, so dept_id is a super key of R2   -> LOSSLESS

/* Lossy example: split on a non key */
R1(emp_id, dept_id)   R2(emp_id, dept_head)  is lossless (emp_id is a key)
R1(emp_id, dept_head) R2(dept_id, dept_head) is LOSSY:

R1                 R2                 natural join produces
E1 | Meera         D10 | Meera        E1 | D10 | Meera
E3 | Meera         D30 | Meera        E1 | D30 | Meera   <== SPURIOUS TUPLE
                                      E3 | D10 | Meera   <== SPURIOUS TUPLE

Guarantees:
  3NF  synthesis : lossless AND dependency preserving   (always)
  BCNF decomposition: lossless, dependency preservation NOT guaranteed

Answer

This relation is in BCNF, because the only candidate key is all three attributes together and there are no non trivial functional dependencies at all. It is still badly designed, and that is exactly what 4NF exists to catch. The problem is a multivalued dependency.

A candidate has a set of skills and independently has a set of preferred cities, the two facts have nothing to do with each other, but storing them in one relation forces the Cartesian product: 4 skills and 3 cities means 12 rows to express 7 independent facts. The notation is candidate_id double arrow skill and candidate_id double arrow preferred_city, read as candidate_id multidetermines skill. Formally, an MVD X double arrow Y holds if for any two tuples agreeing on X you can swap their Y values and both resulting tuples are also in the relation, which is precisely what the Cartesian pattern means.

Fourth normal form requires that for every non trivial MVD X double arrow Y, X must be a super key. Here candidate_id is not a super key, so 4NF is violated, and the anomaly is concrete: adding one new skill means inserting three rows, one per city, and forgetting one leaves the data inconsistent. The fix is to split into candidate_skill(candidate_id, skill) and candidate_city(candidate_id, preferred_city).

Fifth normal form, or project join normal form, goes further and handles join dependencies where a relation must be decomposed into three or more relations to remove redundancy but cannot be split losslessly into any two. The textbook case is supplier, part, project where a supplier supplies a part, a project uses a part and a supplier serves a project, with a cyclic business rule tying the three. 5NF is almost never a practical concern, but knowing that 4NF handles MVDs and 5NF handles join dependencies is the expected depth.

candidate_profile  (BCNF, violates 4NF)
candidate_id | skill  | preferred_city
=============+========+===============
    C1       | Java   | Bengaluru
    C1       | Java   | Pune
    C1       | SQL    | Bengaluru
    C1       | SQL    | Pune         /* 2 skills x 2 cities = 4 rows for 4 facts */

MVDs: candidate_id ->> skill
      candidate_id ->> preferred_city

4NF decomposition:
candidate_skill(candidate_id, skill)
candidate_city (candidate_id, preferred_city)

/* adding a third skill now costs ONE insert, not one per city */
INSERT INTO candidate_skill VALUES ('C1', 'Kafka');

/* 5NF: join dependency across three relations */
supplies(supplier, part)  uses(project, part)  serves(supplier, project)

Answer

Denormalisation is deliberately reintroducing redundancy to avoid joins or aggregations at read time. It is a valid engineering decision, not a failure, but it must be a decision and not an accident. Reach for it when the read to write ratio is heavily skewed, when the join is on the critical path of a user facing page, and when you have already exhausted indexing, query rewriting and caching.

Typical patterns: store company_name on the job row so the listing page does not join company, keep a materialised application_count on the job row instead of running COUNT(*) on a 40 million row table, store a precomputed JSON blob of the rendered card, or maintain a summary table refreshed on a schedule. The costs are specific. First, update anomalies come back.

Company changes its name and now you have thousands of job rows carrying the old name, and you need a background job to fix them, so you have traded a read cost for a write cost plus a consistency window. Second, the invariant now lives in application code or a trigger rather than in the schema, so any script, any migration, any other service that writes directly can break it silently. Third, storage and cache pressure grows, wider rows mean fewer rows per page and a colder buffer pool.

Fourth, counters are a concurrency hotspot: UPDATE jobs SET application_count = application_count + 1 on a viral job serialises every applicant behind one row lock, and on a festival sale that single row becomes the bottleneck. Mitigations: use a materialised view where the engine maintains it, in PostgreSQL with REFRESH MATERIALIZED VIEW CONCURRENTLY, or shard counters into buckets and sum them. The honest interview answer is: normalise first, measure, denormalise the one hot path with an explicit owner for the consistency job.

/* Normalised read path: 6 joins on the listing page */
SELECT j.title, c.name, COUNT(a.app_id)
FROM job j JOIN company c ON c.company_id = j.company_id
           LEFT JOIN applications a ON a.job_id = j.job_id
GROUP BY j.job_id;                              /* ~2s at 40M applications */

/* Denormalised: redundant company_name + maintained counter */
ALTER TABLE job ADD COLUMN company_name VARCHAR(150) NOT NULL;
ALTER TABLE job ADD COLUMN application_count INT NOT NULL DEFAULT 0;

SELECT title, company_name, application_count FROM job WHERE status='open';  /* ~15ms */

/* The cost: this counter row is now a lock hotspot on a viral job */
UPDATE job SET application_count = application_count + 1 WHERE job_id = 17;

/* Safer: PostgreSQL materialised view, engine owns the refresh */
CREATE MATERIALIZED VIEW job_card AS SELECT ...;
REFRESH MATERIALIZED VIEW CONCURRENTLY job_card;

Answer

The answer is disk pages, not comparisons. A database reads and writes in fixed size pages, 16KB in InnoDB and 8KB in PostgreSQL, and the cost that matters is the number of page reads, not the number of key comparisons. A binary search tree or a red black tree stores one key per node, so a tree over 10 million rows is about 23 levels deep and each level is potentially a separate random page read, 23 I/Os per lookup.

A B+ tree packs hundreds of keys into each node so that a node fills exactly one page, giving a fanout of several hundred and a height of only three or four for the same 10 million rows. Three page reads instead of 23, and the upper levels stay cached in the buffer pool so in practice only the leaf read touches disk. B trees and B+ trees differ in one important way: a B+ tree stores data or row pointers only in the leaves, and the leaves are linked together in a doubly linked list.

That linked leaf level is precisely what makes range queries efficient. For applied_on BETWEEN two dates you descend once to the first qualifying leaf and then walk sideways through the leaf chain reading sequential pages, no re descent per row. It is also what makes ORDER BY applied_on free when the index is used, and what allows an index to satisfy MIN, MAX and a LIMIT with a sort.

A hash index gives O(1) equality lookup and can beat a B+ tree for a pure point query, but it stores no order at all, so range scans, prefix matches with LIKE 'abc%', sorting and MIN/MAX all degrade to a full scan. That is why hash indexes exist in PostgreSQL and in InnoDB's adaptive hash index but are never the default. Also note a B+ tree keeps itself balanced through splits and merges, so worst case height is guaranteed, unlike an unbalanced BST which degenerates to a linked list under sorted inserts.

B+ tree over 10,000,000 rows, fanout ~500

            [ root: 1 page ]
           /       |        \
     [internal] [internal] [internal]     level 2
      /  |  \
  [leaf]<->[leaf]<->[leaf]<->[leaf] ...   level 3, doubly linked

Height 3-4  ->  3-4 page reads per lookup
Binary tree ->  log2(10^7) = ~23 levels = ~23 random page reads

/* range query walks the linked leaf level, one descent then sequential */
SELECT * FROM applications
WHERE applied_on BETWEEN '2026-01-01' AND '2026-01-31';

/* hash index: O(1) for equality, useless for the query above */
CREATE INDEX idx_hash ON applications USING HASH (job_id);   /* PostgreSQL */

Answer

A composite index is sorted by the first column, then within equal values of the first by the second, then by the third, exactly like a phone directory sorted by city then surname then first name. The leftmost prefix rule follows directly: the index can be used only for a prefix of its column list, starting at the first column. Query one, city = 'Pune', uses the index on one column, full seek.

Query two, experience_years = 5 with no city predicate, cannot use the index at all for seeking, because the entries for experience 5 are scattered across every city block. MySQL 8 may still choose an index skip scan when city has very few distinct values, and both engines may do a full index scan if the index is narrower than the table, but there is no efficient seek. Query three, city = 'Pune' AND salary > 20, uses only the city column for the seek.

Salary is the third column and the second column, experience_years, is missing, so the index cannot narrow further, the engine reads all Pune entries and filters salary as a residual predicate. Query four is the important one: city = 'Pune' AND experience_years > 3 AND salary = 15 seeks on city and then uses the range on experience_years, but salary cannot be used for seeking because a range predicate stops the usable prefix. Everything after the first range column is filter only.

The design rule that follows: put equality predicate columns first, ordered by selectivity, and the single range column last. If your workload really needs experience_years alone, reorder or add a second index, and remember that idx (a, b) makes a separate index on (a) redundant, which is a cheap index cleanup win.

CREATE INDEX idx ON candidates (city, experience_years, salary);

Index order:  (Bengaluru,2,12) (Bengaluru,5,20) (Pune,3,14) (Pune,5,15) (Pune,5,22)

WHERE city='Pune'                                     -> seek on 1 col   GOOD
WHERE experience_years=5                              -> no usable prefix  BAD
WHERE city='Pune' AND salary>20                       -> seek on 1 col, salary filtered
WHERE city='Pune' AND experience_years>3 AND salary=15 -> seek on 2 cols, salary filtered
WHERE city='Pune' AND experience_years=5 AND salary=15 -> seek on all 3    BEST

/* MySQL EXPLAIN tells you how much of the index was used */
EXPLAIN SELECT * FROM candidates WHERE city='Pune' AND experience_years>3 AND salary=15;
  key: idx   key_len: 67   /* covers city + experience_years only */

/* redundant index, idx already serves it */
DROP INDEX idx_city ON candidates;

Answer

Cardinality here means the number of distinct values in the column, and selectivity is distinct values divided by total rows, a number between 0 and 1. Gender on 10 million users has maybe 3 distinct values, so selectivity is 0.0000003, and any single value matches roughly a third of the table. The optimiser looks at its statistics, estimates that using the index means reading 3.3 million index entries and then doing 3.3 million random row lookups back into the clustered index, and correctly concludes that a sequential full table scan is cheaper.

Random I/O is roughly an order of magnitude more expensive per row than sequential I/O, so the crossover point is low: most optimisers abandon a secondary index somewhere around 5 to 20 percent of the table matching. So the index is never chosen, it is dead weight that still costs write time and disk. High selectivity columns, email, phone, order_id, are the ones that pay.

Three cases where a low cardinality index is still correct. First, skewed distributions: status has 5 values but 99.9 percent of rows are 'completed' and you always query status = 'failed', which matches 0.1 percent, so a partial index in PostgreSQL, WHERE status = 'failed', is excellent, and MySQL has no partial indexes so you emulate it with a generated column. Second, as the leading column of a composite index whose later columns are selective, (gender, city, experience_years) can be fine even though gender alone is not.

Third, as part of a covering index where the engine never touches the table. Also worth mentioning: MySQL updates index cardinality statistics by sampling with innodb_stats_persistent_sample_pages, so a badly estimated cardinality can make the optimiser pick wrongly, and ANALYZE TABLE refreshes it.

SELECT COUNT(*), COUNT(DISTINCT gender), COUNT(DISTINCT email) FROM users;
  10,000,000 | 3 | 9,998,412

selectivity(gender) = 3 / 10,000,000        -> terrible
selectivity(email)  = 9,998,412 / 10,000,000 -> ideal

EXPLAIN SELECT * FROM users WHERE gender = 'F';
  type: ALL          /* full scan, index ignored, and correctly so */
  rows: 9,812,004

/* skewed low cardinality column: partial index IS worth it (PostgreSQL) */
CREATE INDEX idx_failed ON payments (created_at) WHERE status = 'failed';

/* MySQL equivalent: generated column that is NULL for the common case */
ALTER TABLE payments
  ADD failed_at DATETIME GENERATED ALWAYS AS
      (IF(status='failed', created_at, NULL)) STORED,
  ADD INDEX idx_failed (failed_at);

ANALYZE TABLE users;   /* refresh cardinality stats */

Answer

A covering index is one that contains every column the query needs, so the engine can answer entirely from the index without fetching the row from the table. This eliminates the bookmark lookup, the second tree traversal into the clustered index, and it is often a five to twenty times improvement on a query that returns many rows. In MySQL EXPLAIN the Extra column shows Using index, which means covering, and beware the near identical Using index condition which means index condition pushdown, a different and lesser optimisation.

In PostgreSQL the plan node reads Index Only Scan rather than Index Scan. To build one, list the filter and join columns first in the index, then append the columns you only project. Both engines support an INCLUDE clause, MySQL 8 does not but PostgreSQL and SQL Server do, letting you add payload columns to the leaf pages without making them part of the sorted key, which keeps the tree smaller and avoids affecting uniqueness.

In InnoDB the primary key is implicitly present in every secondary index leaf, so an index on (job_id) already covers SELECT app_id, job_id where app_id is the primary key, a useful thing to point out. The PostgreSQL catch is the visibility map. Because PostgreSQL indexes do not store transaction visibility information, an index only scan must still check whether the row version is visible to your snapshot, and it can skip that check only for pages marked all visible in the visibility map.

That map is maintained by VACUUM, so on a heavily updated table that has not been vacuumed recently, an Index Only Scan degrades into heap fetches anyway and the plan shows a non zero Heap Fetches count. The fix is autovacuum tuning, not a different index. The cost of covering indexes generally: they are wider, so fewer entries per page, more disk, and more write amplification.

/* Not covering: index gives job_id, then a lookup fetches status and applied_on */
CREATE INDEX idx_job ON applications (job_id);
SELECT status, applied_on FROM applications WHERE job_id = 17;
  Extra: NULL             /* bookmark lookup per row */

/* Covering */
CREATE INDEX idx_job_cover ON applications (job_id, status, applied_on);
EXPLAIN SELECT status, applied_on FROM applications WHERE job_id = 17;
  key: idx_job_cover
  Extra: Using index      /* answered entirely from the index */

/* PostgreSQL INCLUDE keeps payload out of the sorted key */
CREATE INDEX idx_job_cover ON applications (job_id) INCLUDE (status, applied_on);

EXPLAIN (ANALYZE) SELECT status FROM applications WHERE job_id = 17;
  Index Only Scan using idx_job_cover  (Heap Fetches: 0)     /* good */
  Index Only Scan using idx_job_cover  (Heap Fetches: 41211) /* needs VACUUM */

Answer

In MySQL, EXPLAIN gives one row per table access. Read type first, it is the access method and it has a rough quality order: system and const are single row lookups by primary or unique key, eq_ref is one row per outer row via a unique index in a join, ref is an index lookup returning several rows, range is an index range scan, index is a full scan of the index, and ALL is a full table scan. Seeing ALL on a large table in a join is your headline problem.

Read key next, which index was actually chosen, and possible_keys, which were available, because a discrepancy means the optimiser rejected your index and you want to know why. key_len tells you how many bytes of a composite index were used, which is how you detect that only the first column of a three column index is doing work. rows is the estimate of rows examined, and filtered is the percentage expected to survive the WHERE clause, so rows times filtered is the estimated output. Extra carries the qualitative flags: Using index means a covering index, Using where means post filtering, Using filesort means a sort that the index could not provide, and Using temporary means an internal temp table, usually from GROUP BY or DISTINCT on an unindexed expression. Use EXPLAIN ANALYZE in MySQL 8.0.18 and later to get actual timings rather than estimates.

In PostgreSQL always use EXPLAIN (ANALYZE, BUFFERS). Read it inside out, deepest node first. The critical habit is comparing the estimated rows against actual rows on every node: a node estimating 12 rows but returning 400000 means the planner's statistics are wrong, and that misestimate is what caused it to choose a nested loop instead of a hash join.

BUFFERS shows shared hit versus read so you can tell cache misses from real work. Also watch actual loops, since the per loop time is multiplied by that count.

/* MySQL */
EXPLAIN SELECT a.app_id, j.title
FROM applications a JOIN job j ON j.job_id = a.job_id
WHERE a.status = 'shortlisted';

id | table | type | possible_keys | key      | key_len | rows    | filtered | Extra
===+=======+======+===============+==========+=========+=========+==========+=============
 1 | a     | ALL  | NULL          | NULL     | NULL    | 4102331 |   10.00  | Using where
 1 | j     | eq_ref| PRIMARY      | PRIMARY  | 8       |       1 |  100.00  | NULL

/* type ALL on 4.1M rows = the problem. Fix: */
CREATE INDEX idx_status_job ON applications (status, job_id);
  -> type: ref, rows: 41022, Extra: Using index

/* PostgreSQL: compare estimated vs actual on every node */
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM applications WHERE status='shortlisted' AND applied_on > '2026-01-01';

Seq Scan on applications  (cost=0..91204 rows=12 width=64)
                          (actual time=0.4..812.9 rows=402118 loops=1)
  Filter: (...)
  Buffers: shared hit=1204 read=88110
Planning Time: 0.2 ms
Execution Time: 921.4 ms
/* rows=12 estimated vs 402118 actual -> stale statistics, run ANALYZE */

Answer

Dirty read: T1 updates a wallet balance to 500 but has not committed, T2 reads 500, T1 rolls back, and T2 acted on a value that never existed. Prevented by READ COMMITTED and above. Non repeatable read: T1 reads balance = 1000, T2 updates it to 400 and commits, T1 reads again in the same transaction and sees 400.

The same row gave two answers inside one transaction, which breaks any logic that reads twice. Prevented by REPEATABLE READ. Phantom read: T1 counts applications for a job and gets 40, T2 inserts a new application and commits, T1 repeats the count and gets 41.

Nothing T1 read changed, a new row appeared in the range it queried. Prevented by SERIALIZABLE, and in InnoDB largely prevented at REPEATABLE READ by gap locks. Lost update: T1 and T2 both read balance = 1000, both compute 1000 minus 200 in application code, both write 800, and one debit vanished.

Note this is a read modify write in the application, not a single UPDATE with an expression, and it is the most common real bug of the five. Prevented by SELECT FOR UPDATE, an atomic UPDATE with an expression, or an optimistic version check. Write skew: two doctors are on call, each transaction checks that at least two are on call, both see 2, both mark themselves off duty, and now zero doctors are on call.

Neither transaction wrote a row the other read, so snapshot isolation permits it. This is the important one for a senior answer, because it is the anomaly that PostgreSQL REPEATABLE READ, which is really snapshot isolation, does not prevent. You need SERIALIZABLE, which PostgreSQL implements as serialisable snapshot isolation and which will abort one transaction with a serialisation failure, or you need an explicit lock on a row both transactions agree to contend on.

DIRTY READ                          NON REPEATABLE READ
T1: UPDATE bal=500 (no commit)      T1: SELECT bal -> 1000
T2: SELECT bal -> 500               T2: UPDATE bal=400; COMMIT
T1: ROLLBACK                        T1: SELECT bal -> 400   /* changed mid txn */
T2 acted on a value that never existed

PHANTOM READ                        LOST UPDATE
T1: SELECT COUNT(*) -> 40           T1: SELECT bal -> 1000
T2: INSERT ...; COMMIT              T2: SELECT bal -> 1000
T1: SELECT COUNT(*) -> 41           T1: UPDATE bal = 1000-200 -> 800
                                    T2: UPDATE bal = 1000-200 -> 800  /* one debit lost */

WRITE SKEW  (snapshot isolation does NOT prevent this)
T1: SELECT COUNT(*) FROM oncall WHERE on_duty -> 2
T2: SELECT COUNT(*) FROM oncall WHERE on_duty -> 2
T1: UPDATE oncall SET on_duty=false WHERE doc='A'
T2: UPDATE oncall SET on_duty=false WHERE doc='B'
Both COMMIT -> zero doctors on call, invariant broken

/* Fixes for lost update */
UPDATE wallet SET balance = balance - 200 WHERE user_id = 101;      /* atomic */
SELECT balance FROM wallet WHERE user_id = 101 FOR UPDATE;          /* pessimistic */
UPDATE wallet SET balance=?, version=version+1 WHERE user_id=? AND version=?;

Answer

The ANSI levels, weakest first. READ UNCOMMITTED permits dirty reads, non repeatable reads and phantoms. Almost nobody uses it.

READ COMMITTED prevents dirty reads but permits non repeatable reads and phantoms, and it is the default in PostgreSQL, Oracle and SQL Server. REPEATABLE READ prevents dirty and non repeatable reads, and by the standard still permits phantoms. SERIALIZABLE prevents all three and the outcome is equivalent to some serial order.

The standard says nothing about lost update or write skew, which is a known weakness of the ANSI definition. Now the engine reality, which is what interviewers actually want. MySQL InnoDB defaults to REPEATABLE READ and, unusually, it does prevent phantoms for locking reads.

It does this two ways. Plain non locking SELECT statements use a consistent read: the transaction establishes one read view at its first read and every subsequent read in that transaction sees exactly that snapshot, so no phantoms and no non repeatable reads. Locking reads, SELECT FOR UPDATE, SELECT FOR SHARE, UPDATE and DELETE, use next key locking, which is a record lock plus a gap lock on the gap before the record, so another transaction cannot insert into the scanned range.

Gap locks are why InnoDB deadlocks appear on inserts in ways developers find surprising, and why a DELETE with a non indexed WHERE clause can lock far more than it deletes. The oddity to mention: in InnoDB REPEATABLE READ, an UPDATE reads the latest committed row version, not your snapshot, so a snapshot read and a write in the same transaction can disagree. PostgreSQL REPEATABLE READ is snapshot isolation implemented with MVCC and no gap locks: it also prevents phantoms, but instead of blocking it aborts the second writer with could not serialize access due to concurrent update, so your application must retry. PostgreSQL SERIALIZABLE adds predicate dependency tracking, SSI, and also aborts rather than blocking.

Level             | dirty read | non repeatable | phantom | lost update | write skew
==================+============+================+=========+=============+===========
READ UNCOMMITTED  |  yes       |  yes           |  yes    |  yes        |  yes
READ COMMITTED    |  no        |  yes           |  yes    |  yes        |  yes
REPEATABLE READ   |  no        |  no            |  yes*   |  no**       |  yes
SERIALIZABLE      |  no        |  no            |  no     |  no         |  no

*  InnoDB prevents phantoms via next key (gap) locks; PostgreSQL via snapshots
** InnoDB blocks the second writer; PostgreSQL aborts it with a serialisation error

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@transaction_isolation;   /* MySQL 8 default: REPEATABLE-READ */
SHOW transaction_isolation;       /* PostgreSQL default: read committed */

/* InnoDB gap lock in action */
T1: SELECT * FROM applications WHERE job_id BETWEEN 10 AND 20 FOR UPDATE;
T2: INSERT INTO applications (job_id) VALUES (15);  /* BLOCKS on the gap lock */

/* PostgreSQL repeatable read: the second writer is aborted, retry in app code */
ERROR: could not serialize access due to concurrent update

Answer

Two phase locking is a protocol that guarantees conflict serialisability. Every transaction has a growing phase in which it may acquire locks but not release any, and a shrinking phase in which it may release locks but not acquire any. The moment of the first release is the lock point, and ordering transactions by lock point gives an equivalent serial schedule, which is the proof.

Locks come in modes: a shared lock allows other shared locks but blocks exclusive, an exclusive lock blocks both. Basic 2PL guarantees serialisability but not recoverability. If T1 releases its exclusive lock on a row during its shrinking phase and then aborts before committing, T2 may already have read the uncommitted value and possibly written based on it, so T2 must be rolled back too, and anything that read T2's writes must roll back as well.

That chain is cascading rollback and it can unwind a large amount of work. It also means dirty reads are still possible under basic 2PL. Strict 2PL fixes this by holding all exclusive locks until the transaction commits or aborts, releasing shared locks earlier if it wants.

No other transaction can read or write uncommitted data, so cascading rollback is impossible and the schedule is strict as well as serialisable. Rigorous 2PL goes one step further and holds all locks, shared and exclusive, until commit, which makes reasoning simpler and makes the commit order equal to the serialisation order. Real engines that use locking, including InnoDB for its locking reads, implement something close to strict 2PL: row locks taken by UPDATE, DELETE and SELECT FOR UPDATE are held until COMMIT or ROLLBACK, which is exactly why a long running transaction that updates a hot row blocks everyone else for its entire duration and why keeping transactions short is the single most repeated piece of production advice. Conservative 2PL, acquiring all locks up front, is deadlock free but impractical because you rarely know the full lock set in advance.

Basic 2PL timeline
  T1: lock-X(A) ... write(A) ... unlock(A)   <- shrinking phase starts
  T2:                          lock-X(A) read(A)      /* reads uncommitted value */
  T1: ABORT                                            /* T2 must cascade rollback */

Strict 2PL: X locks held to COMMIT
  T1: lock-X(A) write(A) ................. COMMIT, unlock(A)
  T2:                                      lock-X(A) waits until here

Phases:
  locks held
     ^     ____
     |    /    \           basic 2PL: release before commit
     |   /      \_____
     +================> time

  Strict 2PL: the curve drops vertically at COMMIT, never before.

/* InnoDB behaves like strict 2PL for locking reads */
START TRANSACTION;
  SELECT balance FROM wallet WHERE user_id=101 FOR UPDATE;  /* X lock taken */
  /* any other txn touching user 101 waits until the COMMIT below */
COMMIT;                                                      /* lock released here */

Answer

A deadlock needs four conditions to hold simultaneously: mutual exclusion, hold and wait, no preemption, and circular wait. In a database the practical cause is almost always inconsistent lock ordering. Job A updates rows in ascending order of user_id, job B iterates a different query that returns them in descending order, and eventually A holds row 5 and wants row 9 while B holds row 9 and wants row 5.

Detection uses a wait for graph, a directed graph with one node per active transaction and an edge from T1 to T2 when T1 is waiting for a lock held by T2. A cycle in that graph is a deadlock. InnoDB maintains this graph continuously and detects cycles immediately, then chooses a victim, usually the transaction that has modified the fewest rows because it is cheapest to undo, and rolls it back with error 1213, deadlock found when trying to get lock.

You read the details with SHOW ENGINE INNODB STATUS. Note that innodb_deadlock_detect can be turned off on very high concurrency systems, in which case deadlocks are resolved by innodb_lock_wait_timeout instead, which is slower to react. Prevention schemes use timestamps to break cycles before they form.

Under wait-die, an older transaction waiting on a younger one is allowed to wait, but a younger transaction requesting a lock held by an older one dies immediately and restarts with its original timestamp. Under wound-wait, an older transaction wounds, that is preempts and aborts, the younger holder, while a younger requester simply waits. Both are non preemptive of the older transaction and both guarantee no starvation because restarted transactions keep their original timestamp and eventually become the oldest. The actual production fix is not clever locking, it is threefold: always acquire locks in a deterministic order, typically ORDER BY primary key in the driving query, keep transactions short so the window for overlap is small, and add a bounded retry with jitter around the deadlock error because a deadlock is a transient failure and a retry usually succeeds.

Wait for graph
  T1 ==waits for==> T2
   ^                |
   |                v
  T4 <==waits for== T3        /* cycle T1->T2->T3->T4->T1 = deadlock */

The nightly bug
  Job A: UPDATE wallet SET .. WHERE user_id=5;   /* holds 5 */
  Job B: UPDATE wallet SET .. WHERE user_id=9;   /* holds 9 */
  Job A: UPDATE wallet SET .. WHERE user_id=9;   /* waits on B */
  Job B: UPDATE wallet SET .. WHERE user_id=5;   /* waits on A -> DEADLOCK */

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
SHOW ENGINE INNODB STATUS;   /* LATEST DETECTED DEADLOCK section */

/* Fix 1: deterministic lock order */
SELECT user_id FROM wallet WHERE user_id IN (5,9) ORDER BY user_id FOR UPDATE;

/* Fix 2: bounded retry with jitter, deadlock is transient */
for attempt in 1..3: try txn; on 1213: sleep(random(50,200)ms); retry

wait-die  : older waits, younger DIES and restarts
wound-wait: older WOUNDS (aborts) the younger holder, younger waits

Answer

This is the lost update problem under extreme contention and each approach has a different failure profile. Pessimistic locking with SELECT ... FOR UPDATE takes an exclusive row lock at read time, so the 299 other requests queue behind the first one.

It is correct and simple, and it is what most payment and inventory code should use. The costs are throughput and tail latency: every waiter holds a database connection while blocked, so 300 waiters can exhaust your connection pool and cause failures in unrelated endpoints, and if any waiter's transaction is slow the queue grows. In MySQL a waiter gives up after innodb_lock_wait_timeout, 50 seconds by default, which is far too long for a web request, so lower it or use FOR UPDATE NOWAIT or SKIP LOCKED.

SKIP LOCKED is genuinely useful for a different shape of problem, a work queue where each worker should grab a different row rather than wait for the same one. Optimistic locking takes no lock. You read the row with its version, do your work, then UPDATE ...

WHERE id = ? AND version = ?, and check the affected row count. If it is zero, someone else won, and you reread and retry or fail the request.

This is excellent when conflicts are rare, because the happy path costs nothing, and terrible here, because with 300 contenders on one seat almost every attempt fails and your retry storm generates far more load than the queue would have. The third option is the atomic conditional UPDATE, and for this specific problem it is the best: UPDATE trip SET seats_left = seats_left - 1 WHERE trip_id = ? AND seats_left > 0.

The engine takes the row lock for the duration of one statement, the decrement is computed inside the database rather than in application code, and the affected row count tells you whether you got a seat. No read modify write window exists at all. The rule of thumb: single row counter, use the atomic UPDATE.

Multi row invariant, use FOR UPDATE. Low contention with user think time in the middle, such as an edit form, use optimistic versioning so you do not hold a lock across a human.

/* 1. Pessimistic: correct, serialises 300 requests, holds connections */
START TRANSACTION;
  SELECT seats_left FROM trip WHERE trip_id=9 FOR UPDATE;   /* others block here */
  UPDATE trip SET seats_left = seats_left - 1 WHERE trip_id=9;
COMMIT;

/* 2. Optimistic: no locks, but 299 of 300 attempts fail -> retry storm */
SELECT seats_left, version FROM trip WHERE trip_id=9;       /* 1, v=77 */
UPDATE trip SET seats_left=0, version=78
 WHERE trip_id=9 AND version=77;
/* affected rows = 0 means someone else won, reread and retry */

/* 3. Atomic conditional update: best for a single counter */
UPDATE trip SET seats_left = seats_left - 1
 WHERE trip_id = 9 AND seats_left > 0;
/* affected rows = 1 -> booked, 0 -> sold out. No read modify write window. */

/* Work queue variant: each worker takes a DIFFERENT row */
SELECT * FROM job_queue WHERE status='pending'
 ORDER BY id LIMIT 10 FOR UPDATE SKIP LOCKED;

Answer

A view is a stored query with a name. It holds no data, so every reference re executes the underlying SQL, and its value is encapsulation and access control: expose a view that hides salary columns and grant SELECT on the view rather than the table. In MySQL a view is updatable only if it maps one to one onto a single table without aggregation, DISTINCT, UNION or GROUP BY, and MySQL will materialise a view into a temporary table when it cannot merge it into the outer query, which is a common hidden performance cliff.

A materialised view stores the result physically, so reads are fast and stale. PostgreSQL supports them natively with REFRESH MATERIALIZED VIEW, and CONCURRENTLY to avoid an exclusive lock during refresh, at the cost of requiring a unique index. MySQL has no materialised views, so teams build summary tables refreshed by a scheduled job, which is the same thing with manual plumbing.

The trade off is always staleness versus latency, and you must decide who owns the refresh and what the acceptable staleness is. A stored procedure is precompiled logic living in the database, invoked with CALL. Advantages: fewer network round trips for multi statement logic, and a single enforcement point across many client applications.

Disadvantages: procedural SQL is hard to unit test, hard to version control, invisible to code review unless you are disciplined, and it moves CPU onto the database server, which is the one tier you cannot horizontally scale easily. Triggers are the ones that hurt. A trigger is code that fires automatically on INSERT, UPDATE or DELETE, and the harm is exactly that automation: a developer reading the application code sees an INSERT and has no signal that it also writes an audit row, updates a counter and calls a second trigger on another table.

Debugging a production incident means discovering the trigger during the incident. They also make every write slower, they run inside your transaction so a slow trigger extends lock hold time, they are skipped by many bulk load paths and by MySQL cascading deletes, and they behave differently under logical replication. Use them for narrow, well documented auditing, and put business logic in the application.

/* View: no storage, re executed every time */
CREATE VIEW open_jobs AS
  SELECT job_id, title, company_id FROM job WHERE status='open';

/* Materialised view: stored result, must be refreshed (PostgreSQL) */
CREATE MATERIALIZED VIEW job_stats AS
  SELECT job_id, COUNT(*) AS applications FROM applications GROUP BY job_id;
CREATE UNIQUE INDEX ON job_stats (job_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY job_stats;

/* Stored procedure */
CREATE PROCEDURE close_job(IN p_job_id BIGINT)
BEGIN
  UPDATE job SET status='closed' WHERE job_id = p_job_id;
  UPDATE applications SET status='closed' WHERE job_id = p_job_id;
END;

/* Trigger: invisible from the application code that caused it */
CREATE TRIGGER trg_app_audit AFTER INSERT ON applications
FOR EACH ROW
  INSERT INTO application_audit (app_id, action, at)
  VALUES (NEW.app_id, 'created', NOW());
/* now every INSERT is 2 writes, inside your transaction, holding locks longer */

Answer

These are the physical algorithms behind the logical join you write. Nested loop join takes each row of the outer relation and probes the inner relation for matches. Naively that is O(n times m) and terrible, but with an index on the inner join column it becomes an index nested loop join, O(n times log m), and it is excellent when the outer side is small.

This is why nested loop is the right plan for fetching 20 applications and joining to jobs, and the wrong plan for joining two 5 million row tables. Block nested loop reduces I/O by loading chunks of the outer relation into a buffer. Hash join builds an in memory hash table on the smaller relation keyed on the join column, then scans the larger relation and probes.

Cost is roughly O(n plus m), it needs no index at all, and it is the workhorse for large equi joins in analytics. Two constraints: it only works for equality predicates, and it needs memory. If the build side does not fit in work_mem in PostgreSQL or join_buffer_size in MySQL, the join spills to disk in partitioned batches and gets much slower, so a hash join spilling is a classic reason a report suddenly takes ten times longer after data growth.

Sort merge join sorts both inputs on the join key and then walks them together in one pass. Sorting is the expensive part, O(n log n), but if both inputs already arrive sorted, because they are being read in order from a B+ tree index or because an earlier operator sorted them, the merge is nearly free. It also supports non equality range joins, which hash join cannot.

Optimiser selection is cost based: small outer with an indexed inner gives nested loop, large unsorted inputs with an equality predicate give hash join, already sorted inputs or a query that needs the output sorted anyway give merge join. MySQL had only nested loop variants until 8.0.18 when hash join arrived, and 8.0.20 removed the older block nested loop, which is a genuinely useful version fact: the same analytical query can be dramatically faster on MySQL 8 than on 5.7 for exactly this reason.

/* Nested loop: for each outer row, probe the inner index */
for each row r in applications (outer, 20 rows):
    lookup job where job_id = r.job_id      /* index seek */

/* Hash join: build on the small side, probe with the big side */
build  hash table on job          (30,000 rows)  keyed by job_id
probe  with applications      (4,000,000 rows)

/* Sort merge: sort both, then one linear pass */
sort applications by job_id ; sort job by job_id ; merge

PostgreSQL plan nodes:
  Nested Loop  ->  small outer + indexed inner
  Hash Join    ->  large equi join, no useful index
  Merge Join   ->  inputs already ordered, or ORDER BY needed anyway

EXPLAIN ANALYZE ... ;
  Hash Join (actual rows=4021118 loops=1)
    Buckets: 8192  Batches: 16  Memory Usage: 4096kB   /* Batches > 1 = spilled to disk */

/* MySQL: hash join only from 8.0.18 onward */
SELECT /*+ NO_HASH_JOIN(a, j) */ ... ;

Answer

Partitioning splits one logical table into multiple physical pieces inside a single database server. Sharding splits data across multiple independent database servers. They are frequently confused, and the distinction matters because partitioning does not increase your write throughput or your total CPU, it only reduces how much data a single query touches.

Partitioning strategies. Range partitioning divides by a continuous key, almost always time: one partition per month of created_at. Its big win is partition pruning, a query filtered on last month reads exactly one partition, and dropping old data becomes DROP PARTITION, an instant metadata operation instead of a DELETE of 60 million rows that would bloat your undo log and stall replication.

Its risk is a hot partition: all writes go to the current month. Hash partitioning applies a hash to the key, typically user_id, and distributes evenly, which fixes write hotspots but destroys pruning for range queries on any other column and makes adding partitions a rebalancing exercise. List partitioning assigns explicit value sets to partitions, for example region in a set of states, which is useful for data residency or when one tenant needs isolation.

What partitioning does not solve: the whole table still lives on one server, so you are still bounded by that machine's IOPS, memory and connection limit. Also, in MySQL every unique key including the primary key must contain the partitioning column, which frequently forces an awkward composite primary key like (payment_id, created_at), and cross partition queries can be slower than the unpartitioned table because the engine opens every partition. Sharding is the answer when a single machine is genuinely the ceiling, and it brings its own bill: choosing a shard key, losing cross shard joins and cross shard transactions, needing a routing layer, and facing a painful resharding when the key turns out to be skewed. The correct sequence is index, then archive cold data, then partition, then read replicas, and only then shard.

/* Range partitioning by month, MySQL 8 */
CREATE TABLE payments (
  payment_id BIGINT NOT NULL AUTO_INCREMENT,
  user_id    BIGINT NOT NULL,
  amount     DECIMAL(12,2) NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (payment_id, created_at)   /* partition col must be in every unique key */
)
PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p2026_01 VALUES LESS THAN (TO_DAYS('2026-02-01')),
  PARTITION p2026_02 VALUES LESS THAN (TO_DAYS('2026-03-01')),
  PARTITION pmax     VALUES LESS THAN MAXVALUE
);

EXPLAIN SELECT * FROM payments WHERE created_at >= '2026-02-01';
  partitions: p2026_02,pmax          /* pruning: other partitions never opened */

ALTER TABLE payments DROP PARTITION p2026_01;   /* instant archive */

/* Hash: even write distribution, no pruning on date */
PARTITION BY HASH (user_id) PARTITIONS 16;

/* List: data residency or tenant isolation */
PARTITION BY LIST COLUMNS (region) (
  PARTITION p_south VALUES IN ('KA','TN','KL'),
  PARTITION p_west  VALUES IN ('MH','GJ')
);

Answer

Multiversion concurrency control lets readers see a consistent snapshot without blocking writers and without being blocked by them. Both engines implement it, but the storage differs and the operational consequences follow from that difference. InnoDB keeps only the current version of each row in the clustered index, along with two hidden columns, DB_TRX_ID for the transaction that last modified it and DB_ROLL_PTR pointing into the undo log.

Older versions are reconstructed on demand by walking the undo log chain backwards. A consistent read builds a read view listing the transaction ids active at the time, and for any row whose DB_TRX_ID is not visible it follows the roll pointer until it finds a version that is. The consequence of a long running transaction is that InnoDB cannot purge the undo log entries any snapshot might still need, so the undo tablespace and the history list grow, reads of hot rows get slower because the version chains lengthen, and in extreme cases the undo tablespace fills the disk.

You watch it with the History list length in SHOW ENGINE INNODB STATUS. PostgreSQL takes the opposite approach: every UPDATE writes a new tuple version into the heap and marks the old one with xmax, so dead tuples accumulate in the table itself rather than in a separate undo area. Visibility is decided by comparing xmin and xmax against the snapshot.

Dead tuples are reclaimed by VACUUM, but VACUUM can only remove versions older than the oldest snapshot still in use, so one analyst running a two hour query on the primary blocks cleanup of every table, dead tuples pile up, tables and indexes physically grow, sequential scans read mostly garbage, and index only scans lose their visibility map optimisation. That is table bloat, and it does not shrink back without VACUUM FULL or pg_repack. The other PostgreSQL specific danger is transaction id wraparound: xids are 32 bit, and if autovacuum cannot freeze old rows the database will refuse writes to protect itself. Practical rules: never run long analytics on the primary, run it on a replica with hot_standby_feedback understood, keep transactions short, and monitor n_dead_tup and History list length.

/* InnoDB: current row in place, old versions in the undo log */
clustered index row: [ pk | cols | DB_TRX_ID=1042 | DB_ROLL_PTR ] -> undo -> older version
read view {active trx: 1039,1041} -> walks the chain until a visible version is found

SHOW ENGINE INNODB STATUS;
  History list length 8241093     /* purge is blocked by a long running transaction */

/* PostgreSQL: new tuple version written into the heap, old one marked dead */
SELECT xmin, xmax, ctid, amount FROM payments WHERE payment_id = 5;
  xmin | xmax | ctid   | amount
  4102 | 4155 | (0,1)  | 500.00      /* dead version, waiting for VACUUM */
  4155 |    0 | (0,2)  | 750.00      /* live version */

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;

/* the culprit */
SELECT pid, now()-xact_start AS age, query FROM pg_stat_activity
WHERE state <> 'idle' ORDER BY xact_start LIMIT 5;

Answer

Write ahead logging is one rule: the log record describing a change must reach durable storage before the modified data page does. That single ordering constraint is what makes both atomicity and durability possible, because after a crash the log always contains at least as much information as the data files. Each log record carries a monotonically increasing log sequence number, and every data page stores the LSN of the last log record applied to it, which is how recovery knows whether a page already reflects a change.

Checkpoints bound recovery time. Periodically the engine writes a checkpoint record listing the currently active transactions and the dirty page table, and flushes dirty pages, so recovery does not have to replay the log from the beginning of time. ARIES recovery has three phases and they must be named in order.

Analysis: scan forward from the last checkpoint to rebuild the dirty page table and the transaction table, determining which transactions were in flight at the crash and where redo must start. Redo: replay every logged change from the redo start point, including changes belonging to transactions that will later be undone, which is repeating history. This is the counterintuitive part candidates miss, ARIES redoes uncommitted work first so that the database returns to the exact state at the moment of the crash, which makes undo straightforward.

Redo is skipped for a page whose stored LSN is already at or beyond the record's LSN. Undo: roll back all loser transactions, those active at the crash, using undo log records, and write compensation log records for each undone action so that a crash during recovery does not undo the same action twice. In MySQL the redo log is ib_logfile or the redo log files in 8.0.30 and later, undo lives in undo tablespaces, and innodb_flush_log_at_trx_commit controls whether the log is fsynced at commit, 1 being fully durable and 2 or 0 risking the last second of transactions for throughput. PostgreSQL keeps WAL segments in pg_wal, controls the trade off with synchronous_commit and fsync, and its checkpoint behaviour is tuned with max_wal_size and checkpoint_completion_target.

WAL rule:  log record durable BEFORE the dirty data page is written

Log:  LSN 100 BEGIN T1
      LSN 101 T1 UPDATE wallet pk=101 old=1000 new=800
      LSN 102 CHECKPOINT (active: T1, T2 ; dirty pages: p7, p9)
      LSN 103 BEGIN T2
      LSN 104 T2 UPDATE wallet pk=202 old=500 new=700
      LSN 105 COMMIT T2
      *** CRASH ***   (T1 never committed)

Recovery:
  ANALYSIS : start at LSN 102, losers = {T1}, winners = {T2}
  REDO     : replay 101, 104 (repeating history, T1 included)
  UNDO     : roll back T1 using undo records, write CLRs so a second crash is safe

Final state: T2 durable, T1 fully absent.

/* MySQL durability knob */
innodb_flush_log_at_trx_commit = 1   /* fsync per commit, fully durable */
                               = 2   /* OS buffer, survives process crash not power loss */

/* PostgreSQL */
synchronous_commit = on ;  max_wal_size = 4GB ;  checkpoint_completion_target = 0.9

Answer

This is read your own writes violated by asynchronous replication lag. The write went to the primary, the redirect triggered a read that your load balancer or ORM routed to a read replica, and the replica had not yet applied the transaction. With MySQL asynchronous replication the primary commits and acknowledges the client immediately, then the binlog event travels to the replica and is applied by its applier threads, so the replica is always some milliseconds to some seconds behind, and much further behind during a festival sale write spike or a long ALTER TABLE.

Semi synchronous replication makes the primary wait until at least one replica acknowledges receipt of the binlog event, which bounds data loss on failover but still does not guarantee that the event has been applied and is visible, so it does not by itself fix this bug. Fully synchronous replication, as in group replication or PostgreSQL synchronous_commit set to remote_apply, does guarantee visibility but adds a network round trip to every commit and means a slow replica slows every write, which is why almost nobody runs it for a consumer payment path. Four fixes in increasing sophistication.

One, route reads to the primary for a short window after a write, typically by setting a sticky flag in the user's session for a few seconds. Simple, effective and the most common production choice. Two, read your writes by token: capture the write position, the GTID in MySQL or the LSN in PostgreSQL, pass it along with the request, and have the read path either wait for the replica to reach that position, using WAIT_FOR_EXECUTED_GTID_SET or pg_wal_replay_lsn comparisons, or fall back to the primary.

Three, do not read at all, render the confirmation from the response of the write itself, which is the cleanest answer for a payment success screen and removes the round trip entirely. Four, treat payment confirmation as asynchronous by design: show a pending state and update by webhook or polling, which is also what you need anyway because UPI callbacks are asynchronous and can arrive out of order. Independently, make the payment write idempotent on the gateway reference so a retry cannot double charge, and monitor Seconds_Behind_Master or pg_last_xact_replay_timestamp with an alert.

t0  client POST /pay        -> PRIMARY commits payment row
t0+2ms  primary ACKs (async replication, replica not yet applied)
t0+5ms  client GET /orders     -> REPLICA, lag 900ms, row not there  -> empty page
t0+2s   client refresh         -> REPLICA caught up                  -> row appears

/* Measure the lag */
SHOW REPLICA STATUS\G          /* Seconds_Behind_Source */
SELECT now() - pg_last_xact_replay_timestamp() AS lag;   /* PostgreSQL */

/* Fix 1: sticky primary reads after a write */
session.readFromPrimaryUntil = now() + 5s

/* Fix 2: wait for the replica to reach the write position */
SELECT @@gtid_executed;                        /* on primary after commit */
SELECT WAIT_FOR_EXECUTED_GTID_SET('uuid:1-4821', 1);   /* on replica, 1s timeout */

/* Fix 3: render from the write response, do not re read */
/* Fix 4: idempotency so a retry cannot double charge */
INSERT INTO payments (gateway_ref, amount) VALUES (?, ?)
  ON DUPLICATE KEY UPDATE payment_id = LAST_INSERT_ID(payment_id);

Answer

The precise statement: in the presence of a network partition, a distributed data store cannot provide both consistency, meaning every read receives the most recent write or an error, and availability, meaning every non failing node returns a non error response. Note the three words that make it precise. Consistency here means linearisability, not the C in ACID, which is a different property about constraint preservation, and conflating the two is the mistake interviewers listen for.

Availability means every request to a live node gets a successful response, not merely that the system is up. Partition tolerance means the system continues to operate despite arbitrary message loss between nodes. Pick two is sloppy for a specific reason: partition tolerance is not a design choice you get to decline.

Networks partition, cables are cut, switches fail, a data centre link flaps, and you do not choose whether that happens. So CA is not an available option for any system running across more than one machine, and a single node database is not distributed and the theorem simply does not apply to it. The real choice is a single one, conditional on a partition occurring: when the network splits, do you keep serving reads and writes on both sides and accept divergence, which is AP, or do you refuse service on the minority side to preserve a single truth, which is CP.

Both are legitimate. A payments ledger should be CP, refusing writes is better than double spending. A shopping cart or a feed should be AP, a slightly stale cart is better than a broken checkout page.

PACELC extends this to the case interviewers care about more, because partitions are rare and the trade off exists all the time: if there is a Partition, choose Availability or Consistency, Else, in normal operation, choose Latency or Consistency. That captures the real daily engineering decision, since a synchronous quorum write across regions costs tens of milliseconds of latency even when nothing is broken. DynamoDB and Cassandra are classically PA/EL with tunable quorums, while Spanner and CockroachDB are PC/EC and pay latency for consistency. It is also worth saying that consistency is a spectrum, not a switch: linearisable, sequential, causal and eventual are distinct guarantees.

CAP, stated correctly:
  During a network PARTITION you must sacrifice either
    C (linearisability: every read sees the latest write)
  or
    A (every live node answers successfully)

  P is not optional. Networks partition whether you like it or not.
  CA only describes a single node system, where CAP does not apply.

PACELC:
  if (Partition) choose A or C
  else           choose L (latency) or C (consistency)

System            | P-case | E-case | typical use
==================+========+========+===========================
DynamoDB, Cassandra|  PA   |  EL    | carts, feeds, session data
Spanner, CockroachDB| PC   |  EC    | ledgers, inventory of record
MySQL primary+async|  n/a  |  EL    | single writer, stale replicas

/* Cassandra tunable consistency, the dial in practice */
CONSISTENCY QUORUM;    /* R + W > N gives strong consistency, at latency cost */
CONSISTENCY ONE;       /* fast, may read stale */

Answer

Start by rejecting the scale argument as the primary reason. A well indexed PostgreSQL or MySQL instance handles tens of thousands of transactions per second and terabytes of data, and most teams that moved to NoSQL for scale actually had an indexing problem. The real reasons are data shape and access pattern.

Document stores like MongoDB fit when the entity is naturally a nested aggregate that is always read and written whole, and when the schema varies per document, for example a parsed resume where one candidate has publications and another has certifications. You give up multi document joins, which you replace with embedding or with application side lookups, and you give up schema enforcement unless you add validators. MongoDB has had multi document ACID transactions since 4.0, so the old no transactions objection is outdated, but they are expensive and are not the intended pattern.

Key value stores like Redis and DynamoDB fit when every access is by a known primary key: sessions, rate limit counters, feature flags, caches, idempotency keys. You give up querying by anything except the key, unless you add secondary indexes that reintroduce cost. Wide column stores like Cassandra and HBase fit write heavy, time ordered, partitionable workloads, event logs, IoT telemetry, per user activity feeds, where you design the table around the exact query and accept duplicating data across several tables.

You give up ad hoc querying entirely, you must know your queries before you design the schema, and you give up joins and multi row transactions. Graph stores like Neo4j fit when the queries are about relationship traversal of unbounded depth, fraud rings, who your second degree connections are, recommendation paths, because in SQL that becomes a recursive CTE that degrades fast beyond three hops. You give up ecosystem maturity and analytical tooling.

Across all four, the things you actually give up are: declarative joins, engine enforced referential integrity, mature query optimisers, and a universally understood query language. The strongest interview answer is polyglot: keep the system of record relational, put the specific access pattern that hurts into the store that matches it, and be explicit about which store owns the truth.

Access pattern                          | Store            | What you give up
========================================+==================+===========================
Whole nested aggregate, varying schema   | Document (Mongo) | joins, schema enforcement
Lookup by known key, sub ms, TTL         | KV (Redis/Dynamo)| any non key query
High write, time ordered, partitionable  | Wide column (C*) | ad hoc queries, joins
Deep relationship traversal              | Graph (Neo4j)    | tooling maturity
Joins, constraints, transactions, ad hoc | RDBMS            | horizontal write scaling

/* SQL struggles here: unbounded depth traversal */
WITH RECURSIVE network AS (
  SELECT connection_id, 1 AS depth FROM connections WHERE user_id = 101
  UNION ALL
  SELECT c.connection_id, n.depth + 1
  FROM connections c JOIN network n ON c.user_id = n.connection_id
  WHERE n.depth < 3
)
SELECT DISTINCT connection_id FROM network;   /* explodes past 3 hops */

/* Cassandra: design the table per query, duplicate freely */
CREATE TABLE applications_by_candidate (candidate_id uuid, applied_on timestamp,
  job_id uuid, PRIMARY KEY (candidate_id, applied_on)) WITH CLUSTERING ORDER BY (applied_on DESC);
CREATE TABLE applications_by_job (job_id uuid, applied_on timestamp,
  candidate_id uuid, PRIMARY KEY (job_id, applied_on));

Answer

The shard key decision determines everything else, and it is effectively irreversible without a migration, so reason about it out loud. Candidate keys here: payment_id, user_id, merchant_id, created_at. Sharding on created_at is the worst choice, every write during any given hour lands on one shard, so you get a hotspot and seven idle machines.

Sharding on payment_id distributes evenly but scatters a single user's payments across all 8 shards, so the extremely common query show me my payment history becomes a fan out to every shard with an application side merge and sort. Sharding on merchant_id concentrates: one large merchant on a festival sale evening can swamp their shard while others idle, a classic celebrity or hot tenant problem. user_id is usually right, because the dominant access pattern is per user, a user's payments then all live on one shard so history queries and per user transactions stay local, and user ids are numerous enough to spread evenly. Use consistent hashing or virtual buckets rather than user_id modulo 8, because modulo N means changing N remaps almost every row.

What breaks after sharding. Cross shard joins are gone, you denormalise or you join in the application. Cross shard transactions are gone, you need two phase commit, which is slow and has a blocking coordinator failure mode, or sagas with compensating actions and idempotency.

Global uniqueness and AUTO_INCREMENT are gone, you need Snowflake style ids, UUIDv7 or a central id service. Global aggregates require scatter gather or a separate analytics pipeline. Global secondary indexes need their own lookup table.

Resharding without downtime follows a standard playbook: introduce virtual buckets, say 1024, mapped to physical shards so you move buckets rather than rows. Then dual write to old and new locations, backfill historically with a throttled copy, verify with row counts and checksums, flip reads shard by shard behind a feature flag while still dual writing, then stop writing to the old location, and only then drop it. Every step must be individually reversible, and you keep the routing map in a config service so a flip is not a deploy.

Shard key candidates for payments
  created_at  -> all writes on one shard per hour        HOTSPOT
  payment_id  -> even spread, but user history fans out  N shard scatter gather
  merchant_id -> hot tenant swamps one shard             SKEW
  user_id     -> even spread AND user local queries      USUALLY RIGHT

/* virtual buckets so resharding moves buckets, not rows */
bucket   = hash(user_id) % 1024
shard    = bucket_to_shard[bucket]        /* routing map in a config service */

/* globally unique ids without AUTO_INCREMENT */
id = (timestamp_ms << 22) | (shard_id << 12) | sequence     /* Snowflake style */

Resharding playbook (each step reversible)
  1. dual write old + new
  2. throttled backfill of history
  3. verify: row counts + checksums per bucket
  4. flip reads per bucket behind a flag
  5. stop writing old
  6. drop old

/* what you lose */
SELECT u.name, SUM(p.amount) FROM users u JOIN payments p ...  /* no longer one query */

Answer

A cost based optimiser enumerates candidate plans, join orders, join algorithms, access methods, then estimates the cost of each in abstract units combining page reads and CPU, and picks the cheapest. Every one of those estimates depends on cardinality estimation, guessing how many rows each operator will emit, and cardinality estimation depends entirely on statistics: the number of rows in the table, the number of distinct values per column, the null fraction, the most common values list and a histogram of the value distribution. PostgreSQL keeps these in pg_statistic exposed through pg_stats and refreshes them via autovacuum's ANALYZE.

MySQL 8 keeps index cardinality sampled from InnoDB pages and additionally supports explicit histograms created with ANALYZE TABLE ... UPDATE HISTOGRAM ON. The catastrophic case is a two step failure.

Suppose you bulk load 40 million new applications overnight and statistics still describe yesterday's 400 thousand row table. You run a query filtering on status = 'shortlisted' joined to jobs. The planner estimates 12 rows will survive the filter, and 12 rows is exactly the situation where a nested loop join with an index lookup on the inner side is optimal, so it picks that.

In reality 400 thousand rows survive, and the plan now performs 400 thousand index lookups instead of one hash build, turning a 200 millisecond query into a 15 minute one. Nothing is broken, the plan is internally consistent, it is simply built on a wrong number. Correlated predicates cause the same failure without any staleness: the optimiser assumes independence, so it estimates the selectivity of city = 'Mumbai' AND state = 'Maharashtra' as the product of two selectivities, which underestimates by a large factor because the columns are perfectly correlated.

PostgreSQL fixes this with CREATE STATISTICS for multivariate dependencies. Diagnosis is always the same: run EXPLAIN ANALYZE and compare estimated rows against actual rows node by node, and the first node where they diverge by an order of magnitude is your culprit. Remedies in order: ANALYZE the table, raise default_statistics_target or add extended statistics for correlated columns, rewrite the query to be more estimable, and only as a last resort force a plan with hints or pg_hint_plan, because a pinned plan becomes wrong as data changes.

EXPLAIN ANALYZE
SELECT a.*, j.title FROM applications a JOIN job j USING (job_id)
WHERE a.status = 'shortlisted';

Nested Loop  (cost=0.85..112.4 rows=12 width=96)
             (actual time=0.1..913241.7 rows=402118 loops=1)
  ->  Index Scan on applications  (rows=12) (actual rows=402118)
  ->  Index Scan on job           (rows=1)  (actual rows=1 loops=402118)

/* estimate 12 vs actual 402118 -> nested loop chosen on a wrong number */

ANALYZE applications;
  -> planner now estimates ~400000 -> switches to Hash Join -> 210 ms

/* inspect what the planner believes */
SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats WHERE tablename='applications' AND attname='status';

/* correlated columns: independence assumption underestimates badly */
CREATE STATISTICS stx_city_state (dependencies)
  ON city, state FROM candidates;
ANALYZE candidates;

/* MySQL 8 histograms */
ANALYZE TABLE applications UPDATE HISTOGRAM ON status WITH 32 BUCKETS;

Answer

Work from cheapest to most invasive and say so, because the method is what is being graded. Step one, establish scope. Is it this one query, all queries, or the whole server?

Check host CPU, disk IO and memory, and check whether the buffer pool hit ratio dropped, because a server wide symptom means you are chasing the wrong thing. Step two, confirm the query is actually the problem by finding it in the slow query log with long_query_time lowered, or in pg_stat_statements ordered by total_exec_time, which also tells you whether the per call time or the call count grew. A query getting called ten times more often is a very different bug from a query getting slower.

Step three, get the plan with EXPLAIN ANALYZE and compare it to what you expect. Look for a full scan where an index used to be used, a nested loop over a huge row count, a filesort, or an estimated versus actual divergence. Step four, form a hypothesis from a short list of things that change without a deploy.

Data volume crossed a threshold so the optimiser flipped from an index scan to a sequential scan. Statistics went stale after a bulk load, fixed by ANALYZE. An index was dropped or is being rebuilt.

Data distribution changed, a single merchant now owns 60 percent of rows so the index became unselective for that value, which is parameter sniffing sensitivity. Table bloat from missing autovacuum in PostgreSQL means the same rows now span far more pages. Lock contention or a long running transaction is making it wait rather than work, visible in pg_stat_activity wait events or SHOW PROCESSLIST.

Replication lag pushed reads onto a struggling replica. A newly added index changed the plan for this unrelated query. Step five, verify the hypothesis before fixing: run ANALYZE and re plan, or check pg_stat_user_tables for dead tuples, or look at the actual row counts.

Step six, fix the smallest thing, refresh statistics, add or reorder a composite index, rewrite the query to be sargable by removing a function around an indexed column, or add a LIMIT and keyset pagination. Step seven, add a regression guard: an alert on p99 for that endpoint, and pg_stat_statements tracking so the next drift is caught by monitoring rather than by a user.

1. Scope
   top / iostat ; SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

2. Find it
   SELECT query, calls, mean_exec_time, total_exec_time
   FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
   /* mean up = slower query ; calls up = caller regression */

3. Plan
   EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
   Seq Scan (rows=12) (actual rows=402118)   -> stale stats

4. Cheap causes, in order
   ANALYZE applications;                          /* stale statistics */
   SELECT n_dead_tup FROM pg_stat_user_tables;    /* bloat */
   SELECT * FROM pg_stat_activity WHERE state<>'idle' ORDER BY xact_start; /* long txn */
   SHOW INDEX FROM applications;                  /* index dropped or invisible */

5. Sargability fix (function around an indexed column kills the index)
   /* slow  */ WHERE DATE(applied_on) = '2026-02-01'
   /* fast  */ WHERE applied_on >= '2026-02-01' AND applied_on < '2026-02-02'

6. Guard
   alert on p99 latency for the endpoint + keep pg_stat_statements enabled

Answer

Every database connection costs real server side resources. In PostgreSQL a connection is a forked backend process with its own memory, several megabytes of it, plus work_mem allocated per sort or hash node within a query, so 2000 connections can mean gigabytes of overhead before any query runs, plus process scheduling contention and heavier snapshot computation. In MySQL a connection is a thread, cheaper but still costly, and each carries per thread buffers.

Raising max_connections does not create capacity, it converts a clean rejection into a slow death: the server thrashes, every query slows, latency rises, clients time out and retry, which adds more connections, and you get congestion collapse. The right model is that useful concurrency is bounded by cores and disk, not by clients. A machine with 16 cores can genuinely execute roughly 16 to 48 concurrent queries usefully, so the pool should be sized in that range and the rest of the traffic should wait in a queue that you control.

Connection pooling keeps a fixed set of established connections and hands them out for the duration of a query or transaction, so 2000 concurrent users share 40 connections. It also removes the TCP and authentication handshake from the request path, which alone is often several milliseconds. Pool sizing rules: total pool size across all application instances must be under max_connections with headroom for admin connections, so a pool of 20 times 30 pods is already 600 and is usually the real bug.

In PostgreSQL use PgBouncer in transaction pooling mode, which returns the connection after each transaction rather than each session, giving far better multiplexing, with the caveat that session level features break, prepared statements need care, SET commands do not persist, and advisory locks and LISTEN NOTIFY do not work as expected. Set a short pool acquisition timeout so a saturated pool fails fast instead of piling up, keep transactions short since a transaction holds a pooled connection for its whole duration, and never hold a connection across an external HTTP call. Monitor pool wait time as a first class metric, because rising wait time is the earliest signal of saturation, well before errors appear.

Without pooling: 2000 users -> 2000 connections -> FATAL: sorry, too many clients
With pooling   : 2000 users -> 40 connections -> queue depth is a metric you own

/* Useful concurrency is bounded by hardware, not by users */
pool_size ~= cores * 2 + effective_spindles      /* 16 cores -> ~35 */

/* The real bug in Kubernetes */
pool_size 20 x 30 pods = 600 connections > max_connections 500

/* PgBouncer, transaction pooling */
[databases]
app = host=primary port=5432 dbname=app
[pgbouncer]
pool_mode = transaction        /* connection returned after each txn */
max_client_conn = 5000
default_pool_size = 40

/* Fail fast rather than pile up */
connectionTimeoutMillis = 2000
idleTimeoutMillis       = 30000

/* Never do this: connection held across a network call */
BEGIN; UPDATE payments ...; await razorpayApi.capture(); COMMIT;

SELECT count(*), state FROM pg_stat_activity GROUP BY state;

Answer

Once the two writes live in different databases you cannot use a single local transaction, and you have to choose between blocking coordination and eventual consistency with compensation. Two phase commit gives you atomicity across both. A coordinator sends a prepare message, each participant does the work, writes it durably and replies prepared, promising it can commit even after a crash, and holds its locks.

If all reply prepared the coordinator sends commit, otherwise abort. The properties are attractive and the failure mode is not: between prepare and commit each participant holds locks and cannot decide alone, so if the coordinator dies after prepare, participants are blocked indefinitely, holding locks on wallet rows, until the coordinator recovers. That is why 2PC is rare in internet scale systems, it converts a coordinator failure into a distributed outage, and it multiplies latency by adding a round trip plus an fsync per participant.

XA transactions in MySQL implement it and are widely avoided. The saga pattern accepts that atomicity is unavailable and instead makes the sequence recoverable. Each step is a local transaction that commits immediately, and each step has a compensating action that semantically undoes it.

Debit wallet, create order, confirm order, and if order creation fails you run credit wallet as compensation. Sagas are either choreographed, each service reacting to the previous event, which is simple to start and hard to reason about at five steps, or orchestrated, a single workflow service driving the steps and recording progress durably, which is what you want beyond three steps. Sagas give up isolation, there is a window where the wallet is debited and no order exists, so the user interface must show a pending state and the design must tolerate it.

Compensations must be commutative with retries and must handle the case where the thing they compensate never happened. Idempotency is what makes either approach survivable, because every distributed step will be retried. Give each logical operation a client generated key, store it with a unique constraint alongside the effect, and on a duplicate return the original result instead of applying the effect twice. Combined with an outbox table written in the same local transaction as the business change and published by a relay, you get exactly once effects on top of at least once delivery, which is the practical standard.

2PC
  coordinator -> prepare -> [wallet_db: ok, holds locks] [order_db: ok, holds locks]
  coordinator -> commit  -> both commit
  coordinator DIES after prepare -> both blocked holding locks until it recovers

SAGA (orchestrated)
  step 1 debit wallet        compensate: credit wallet
  step 2 create order        compensate: cancel order
  step 3 confirm order       compensate: none
  failure at step 2 -> run compensation for step 1, user sees FAILED not PENDING

/* Idempotency: unique key on the logical operation */
CREATE TABLE wallet_txn (
  txn_id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  idempotency_key CHAR(36) NOT NULL,
  user_id         BIGINT NOT NULL,
  amount          DECIMAL(12,2) NOT NULL,
  UNIQUE KEY uq_idem (idempotency_key)
);

START TRANSACTION;
  INSERT INTO wallet_txn (idempotency_key, user_id, amount) VALUES (?,?,?);
  /* duplicate key -> this retry already happened, return the stored result */
  UPDATE wallet SET balance = balance - ? WHERE user_id = ? AND balance >= ?;
  INSERT INTO outbox (topic, payload) VALUES ('wallet.debited', ?);  /* same txn */
COMMIT;
/* a relay reads outbox and publishes, giving at least once delivery */