SQL & PostgreSQL interview questions & answers

1. 1.Explain the different types of joins: INNER, LEFT, RIGHT, FULL OUTER, CROSS and self join.easy

   A join combines rows from two tables based on a condition. The types differ in what happens to rows that don't match.

   • INNER JOIN: only rows that match on both sides.
   • LEFT JOIN: every row from the left table, plus matching right rows; where there's no match, the right-side columns are NULL.
   • RIGHT JOIN: the mirror image. Most people just swap the tables and write a LEFT JOIN.
   • FULL OUTER JOIN: every row from both sides, with NULLs wherever there's no partner. MySQL doesn't support it; you emulate it with a UNION of a left and a right join.
   • CROSS JOIN: the Cartesian product, every row paired with every row, so 6 × 3 rows gives 18.
   • Self join: not a keyword, just a table joined to itself under two aliases, such as employees to their managers.

   -- Flo has no department; HR has no employees
   SELECT e.name, d.name AS dept
   FROM employees e
   LEFT JOIN departments d ON d.id = e.dept_id;
   
   -- INNER JOIN -> 5 rows (Flo dropped)
   -- LEFT JOIN  -> 6 rows (Flo with dept NULL)
   -- RIGHT JOIN -> 6 rows (HR with name NULL)
   -- FULL JOIN  -> 7 rows (both kept)

   What interviewers listen for

   • INNER keeps only matching rows
   • LEFT/RIGHT keep all rows of one side, NULL-padded
   • FULL keeps unmatched rows from both sides
   • CROSS is the Cartesian product (m × n rows)
   • Self join = same table, two aliases

   Likely follow-up: How would you find employees who have no department? · How do you emulate a FULL OUTER JOIN in MySQL?

   answer it out loud →
2. 2.What is the difference between WHERE and HAVING?easy

   Both filter, but at different stages of the query.

   • WHERE filters individual rows before grouping. It can't contain aggregates, so WHERE count(*) > 1 is an error.
   • HAVING filters groups after GROUP BY has run, so it's where conditions on count, sum or avg go.

   The logical order is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. That's also why, in PostgreSQL, you can't use a SELECT alias in WHERE or HAVING: the alias doesn't exist yet. MySQL is more lenient and allows aliases in HAVING.

   Rule of thumb: put any condition on plain columns in WHERE, so fewer rows get grouped, and keep HAVING for conditions on aggregates. HAVING without GROUP BY treats the whole result as one group.

   SELECT dept_id, count(*) AS n, avg(salary) AS avg_sal
   FROM employees
   WHERE salary > 95          -- row filter, before grouping
   GROUP BY dept_id
   HAVING count(*) >= 2;      -- group filter, after grouping

   What interviewers listen for

   • WHERE filters rows before grouping
   • HAVING filters groups after GROUP BY
   • Aggregates are allowed in HAVING, not WHERE
   • Push plain-column filters into WHERE

   Likely follow-up: Can you use a SELECT alias in HAVING? · What is the logical order of evaluation of a SELECT?

   answer it out loud →
3. 3.Write a query to find the second (or Nth) highest salary. How do you handle ties and the case where it doesn't exist?mid

   First clarify what "second highest" means with ties: salaries 200, 150, 150 have a second-highest distinct salary of 150. Three common approaches:

   • DENSE_RANK: rank distinct salaries and pick rank N. It handles ties and generalizes to any N, and with PARTITION BY dept_id it works per department.
   • DISTINCT + OFFSET: ORDER BY salary DESC OFFSET N-1 LIMIT 1. Short, but forgetting DISTINCT gives the wrong answer on ties.
   • Nested MAX: max(salary) WHERE salary < (SELECT max(salary) ...). Only practical for N = 2.

   If there's no Nth salary, the OFFSET query returns zero rows. Wrapping it in a scalar subquery turns that into a single NULL, which is often what the grader expects.

   -- Nth highest with DENSE_RANK (N = 2)
   SELECT DISTINCT salary
   FROM (SELECT salary,
                DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
         FROM employees) t
   WHERE rnk = 2;
   
   -- Returns NULL instead of no rows when N is too large
   SELECT (SELECT DISTINCT salary FROM employees
           ORDER BY salary DESC OFFSET 1 LIMIT 1) AS second_highest;

   What interviewers listen for

   • Clarify how ties should count
   • DENSE_RANK generalizes to any N and per group
   • DISTINCT is required with the OFFSET approach
   • Scalar subquery returns NULL when no Nth value

   Likely follow-up: How would you get the highest-paid employee in each department? · Why would RANK give a different answer than DENSE_RANK here?

   answer it out loud →
4. 4.What is the difference between a primary key, a unique key and a foreign key?easy

   • A primary key uniquely identifies each row. It's UNIQUE plus NOT NULL, and a table has at most one, though it can span several columns (a composite key).
   • A unique constraint also forbids duplicates, but a table can have many, and the columns can be nullable. In PostgreSQL, NULLs are considered distinct, so a unique column can hold several NULLs; since PostgreSQL 15 you can write UNIQUE NULLS NOT DISTINCT to allow only one.
   • A foreign key says a column's values must exist in a primary key or unique column of another (or the same) table. It enforces referential integrity: you can't insert an order for a customer that doesn't exist, or delete a customer who still has orders, unless you set ON DELETE CASCADE or SET NULL.

   PostgreSQL automatically creates an index for primary keys and unique constraints, but not for the referencing side of a foreign key. Index it yourself, or joins and parent deletes get slow.

   What interviewers listen for

   • PK = unique + not null, one per table
   • Unique allows NULLs and several per table
   • FK references a PK or unique column
   • FK enforces referential integrity; ON DELETE actions
   • PostgreSQL doesn't auto-index the FK column

   Likely follow-up: Natural key or surrogate key: which would you choose and why? · What does ON DELETE CASCADE do?

   answer it out loud →
5. 5.What is the difference between DELETE, TRUNCATE and DROP?easy

   • DELETE is DML. It removes the rows matching a WHERE clause (or all rows), one row at a time, fires row-level DELETE triggers, and can use RETURNING. In PostgreSQL the deleted rows become dead tuples, and the space is reclaimed later by VACUUM.
   • TRUNCATE removes all rows at once by swapping in new, empty storage, so it's much faster on big tables and gives the space back without waiting for VACUUM. It takes an ACCESS EXCLUSIVE lock, doesn't fire DELETE triggers, fails if other tables reference it with a foreign key (unless you add CASCADE), and can reset identity columns with RESTART IDENTITY.
   • DROP TABLE removes the table itself: data, structure, indexes, constraints and triggers.

   A classic gotcha: in PostgreSQL, TRUNCATE and DROP are transactional and can be rolled back. In MySQL and Oracle they cause an implicit commit and can't be.

   What interviewers listen for

   • DELETE: row by row, WHERE, triggers, RETURNING
   • TRUNCATE: all rows, fast, frees space immediately
   • DROP removes the table definition too
   • PostgreSQL can roll back TRUNCATE and DROP
   • MySQL/Oracle auto-commit on TRUNCATE

   Likely follow-up: Does TRUNCATE reset an identity column in PostgreSQL? · Why might DELETE FROM big_table be slow and bloat the table?

   answer it out loud →
6. 6.How do you find duplicate rows in a table, and how do you delete them while keeping one copy?mid

   Finding them is a GROUP BY on the columns that define "duplicate", with HAVING count(*) > 1.

   Deleting them needs a rule for which copy survives, usually the lowest id. Two clean ways:

   • ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) numbers each copy; delete everything with rn > 1. This is the most flexible because the ORDER BY decides who survives.
   • A self-join delete: in PostgreSQL, DELETE FROM t a USING t b WHERE a.email = b.email AND a.id > b.id.

   If the table has no key at all, PostgreSQL's system column ctid (the row's physical location) can stand in for the id within a single statement.

   Then fix the cause: add a UNIQUE constraint so duplicates can't come back, and do the cleanup inside a transaction so you can check the count before committing.

   -- Find
   SELECT email, count(*)
   FROM employees
   GROUP BY email
   HAVING count(*) > 1;
   
   -- Delete all but the lowest id per email
   DELETE FROM employees
   WHERE id IN (
     SELECT id FROM (
       SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
       FROM employees) t
     WHERE rn > 1);

   What interviewers listen for

   • GROUP BY the duplicate columns, HAVING count > 1
   • ROW_NUMBER per partition, delete rn > 1
   • Decide explicitly which copy survives
   • ctid works when there is no key (PostgreSQL)
   • Add a UNIQUE constraint afterwards

   Likely follow-up: How would you do this on a 500-million-row table without long locks?

   answer it out loud →
7. 7.What is the difference between UNION and UNION ALL, and which should you prefer?easy

   Both stack the results of two queries vertically. The queries must return the same number of columns with compatible types, and the column names come from the first query.

   • UNION removes duplicate rows from the combined result, so the database has to sort or hash everything to find them. For deduplication, two NULLs count as equal.
   • UNION ALL keeps every row, including duplicates, and just appends the results. It's cheaper and can stream rows without waiting.

   Prefer UNION ALL unless you actually need deduplication: it's faster and it doesn't silently drop rows that happen to be identical, such as two equal payments. Related operators are INTERSECT and EXCEPT (Oracle calls it MINUS), which also deduplicate unless you add ALL. An ORDER BY at the end applies to the whole combined result.

   What interviewers listen for

   • UNION deduplicates, UNION ALL keeps everything
   • Deduplication costs a sort or hash
   • Same column count and compatible types
   • Default to UNION ALL unless you need distinct rows

   Likely follow-up: What do INTERSECT and EXCEPT do?

   answer it out loud →
8. 8.What is normalization? Explain 1NF, 2NF and 3NF with examples.mid

   Normalization organizes tables so each fact is stored once, which prevents update, insert and delete anomalies.

   • 1NF: every column holds a single atomic value and there are no repeating groups. A phones column containing "555-1, 555-2" violates it; move phones to their own table.
   • 2NF: 1NF, and every non-key column depends on the whole primary key. In order_items(order_id, product_id, qty, product_name), product_name depends only on product_id, so it belongs in products. 2NF only matters with composite keys.
   • 3NF: 2NF, and no non-key column depends on another non-key column (no transitive dependency). In employees(id, dept_id, dept_name), dept_name depends on dept_id, not on id, so it moves to departments.

   The summary people quote: every non-key column depends on "the key, the whole key, and nothing but the key". Most OLTP schemas aim for 3NF and denormalize deliberately where reads demand it.

   What interviewers listen for

   • Goal: store each fact once, avoid anomalies
   • 1NF: atomic values, no repeating groups
   • 2NF: no partial dependency on a composite key
   • 3NF: no transitive dependency between non-key columns
   • OLTP usually targets 3NF

   Likely follow-up: What is BCNF and how does it differ from 3NF? · When would you denormalize?

   answer it out loud →
9. 9.What is an index, how does it speed up queries, and what does it cost?easy

   An index is a separate data structure that maps column values to the rows that contain them, like the index at the back of a book. Without one, finding WHERE email = 'a@x.com' means reading every row (a sequential scan). The default index type, a B-tree, keeps values sorted in a shallow balanced tree, so a lookup takes a handful of page reads, and it also helps with ranges, ORDER BY and joins.

   The costs:

   • Slower writes: every INSERT, UPDATE of an indexed column and DELETE has to maintain every index.
   • Storage and memory: indexes take disk space and compete for cache.
   • Not always used: if a query matches a large fraction of the table, a sequential scan is cheaper, and the planner will choose it.

   PostgreSQL creates indexes automatically for primary keys and unique constraints. For others, use CREATE INDEX, or CREATE INDEX CONCURRENTLY on a live table so writes aren't blocked.

   What interviewers listen for

   • Separate structure mapping values to rows
   • B-tree: sorted, logarithmic lookups, supports ranges
   • Costs: write overhead, storage, cache
   • Low-selectivity queries still use a seq scan
   • PK and unique constraints are indexed automatically

   Likely follow-up: When would an index make things worse? · What is a composite index and does column order matter?

   answer it out loud →
10. 10.What is a transaction, and what do the ACID properties mean?easy

    A transaction is a group of statements that succeed or fail as a unit, between BEGIN and COMMIT (or ROLLBACK). The classic example is a transfer: debit one account, credit another, and never do just one of them.

    • Atomicity: all or nothing. If anything fails, or you roll back, none of the changes are visible.
    • Consistency: a transaction takes the database from one valid state to another; constraints, foreign keys and checks hold at commit.
    • Isolation: concurrent transactions don't see each other's uncommitted work. How strictly depends on the isolation level; PostgreSQL defaults to Read Committed.
    • Durability: once COMMIT returns, the change survives a crash. PostgreSQL writes it to the write-ahead log (WAL) and flushes that to disk before acknowledging, unless you relax synchronous_commit.

    In PostgreSQL, a statement outside BEGIN runs in its own implicit transaction and commits automatically.

    What interviewers listen for

    • Transaction = unit of work, all or nothing
    • Atomicity, Consistency, Isolation, Durability
    • Isolation strength depends on the isolation level
    • Durability via WAL flushed at commit

    Likely follow-up: What isolation levels exist and what anomalies do they allow? · What is a savepoint?

    answer it out loud →
11. 11.How does GROUP BY work with aggregate functions, and what is the rule about non-aggregated columns in the SELECT list?easy

    GROUP BY collapses rows that share the same values in the grouping columns into one output row per group, and aggregate functions such as count, sum, avg, min and max summarize each group.

    The rule: every column in the SELECT list must either appear in GROUP BY or be inside an aggregate. Otherwise the database wouldn't know which row's value to show. PostgreSQL relaxes this in one safe case: if you group by a table's primary key, you can select that table's other columns, because they're functionally dependent on it. Old MySQL versions silently picked an arbitrary value; modern MySQL enables ONLY_FULL_GROUP_BY by default.

    Other details interviewers like: aggregates ignore NULLs (except count(*)), all NULLs in a grouping column form one group, and without GROUP BY an aggregate treats the whole table as one group. PostgreSQL also supports count(*) FILTER (WHERE ...) for conditional aggregates and ROLLUP for subtotals.

    What interviewers listen for

    • One output row per distinct group
    • Non-aggregated SELECT columns must be in GROUP BY
    • Aggregates skip NULLs, except count(*)
    • NULLs group together into one group
    • FILTER (WHERE ...) for conditional aggregates

    Likely follow-up: What is the difference between count(*), count(col) and count(DISTINCT col)?

    answer it out loud →
12. 12.What is the difference between ROW_NUMBER, RANK and DENSE_RANK?mid

    All three are window functions that number rows according to the window's ORDER BY. They only differ on ties:

    • ROW_NUMBER() gives every row a unique, consecutive number. Tied rows get different numbers in an arbitrary order unless you add a tiebreaker to the ORDER BY.
    • RANK() gives tied rows the same rank and then skips: 1, 2, 2, 4.
    • DENSE_RANK() gives tied rows the same rank without gaps: 1, 2, 2, 3.

    Choosing between them is really a requirements question. Use ROW_NUMBER when you need exactly one row per group, such as deduplication or "latest order per customer". Use DENSE_RANK for "Nth highest distinct value". Use RANK for competition-style rankings, where two people tied for second means nobody is third.

    Add PARTITION BY to restart the numbering for each group.

    SELECT name, salary,
           ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
           RANK()       OVER (ORDER BY salary DESC) AS rnk,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
    FROM employees;
    
    -- name | salary | rn | rnk | drnk
    -- Ada  |    200 |  1 |   1 |    1
    -- Bob  |    150 |  2 |   2 |    2
    -- Cy   |    150 |  3 |   2 |    2
    -- Di   |    120 |  4 |   4 |    3

    What interviewers listen for

    • They differ only in how ties are numbered
    • ROW_NUMBER: unique numbers, ties broken arbitrarily
    • RANK: ties share a rank, then gaps
    • DENSE_RANK: ties share a rank, no gaps
    • Add a tiebreaker for deterministic ROW_NUMBER

    Likely follow-up: Which would you use to get the top 3 salaries per department?

    answer it out loud →
13. 13.What is SQL, and what are DDL, DML, DCL and TCL? Give examples of each.easy

    SQL is the standard declarative language for relational databases: you describe the result you want, and the database's optimizer decides how to get it. Its commands are usually grouped into sublanguages:

    • DDL (Data Definition): defines structure. CREATE, ALTER, DROP, TRUNCATE.
    • DML (Data Manipulation): works with the rows. INSERT, UPDATE, DELETE, MERGE, and SELECT (some books split SELECT out as DQL, Data Query Language).
    • DCL (Data Control): permissions. GRANT and REVOKE.
    • TCL (Transaction Control): BEGIN, COMMIT, ROLLBACK, SAVEPOINT.

    A practical difference between databases: in PostgreSQL, most DDL is transactional, so you can run a migration inside BEGIN and roll the whole thing back. In MySQL and Oracle, DDL statements commit implicitly. Some PostgreSQL commands, like CREATE INDEX CONCURRENTLY, can't run inside a transaction block at all.

    What interviewers listen for

    • SQL is declarative; the optimizer picks the plan
    • DDL defines structure: CREATE, ALTER, DROP
    • DML changes data: INSERT, UPDATE, DELETE, SELECT
    • DCL: GRANT/REVOKE; TCL: COMMIT/ROLLBACK/SAVEPOINT
    • PostgreSQL DDL is mostly transactional

    answer it out loud →
14. 14.What is the difference between a clustered and a non-clustered index? Does PostgreSQL have clustered indexes?mid

    A clustered index determines the physical order of the table itself: the table's rows live inside the index's leaf pages, sorted by the key. So there can be only one. A non-clustered (secondary) index is a separate structure whose entries point back to the rows.

    How this plays out depends on the engine:

    • SQL Server: the primary key becomes the clustered index by default; non-clustered indexes point to rows via the clustering key.
    • MySQL InnoDB: every table is clustered on its primary key (or a hidden row id if it has none), and secondary indexes store the primary key value, so a lookup by secondary index does a second lookup by PK. That's why a long PK makes every index bigger.
    • PostgreSQL: no clustered indexes. Tables are unordered heaps, and every index, including the primary key's, stores the row's physical location (its ctid). The CLUSTER command physically reorders a table by an index once, but the order isn't maintained as rows change.

    What interviewers listen for

    • Clustered = table rows stored in index order
    • Only one clustered index per table
    • InnoDB clusters on the PK; secondaries store the PK
    • PostgreSQL tables are heaps; all indexes are secondary
    • CLUSTER reorders once and isn't maintained

    Likely follow-up: Why is a random UUID primary key a problem for InnoDB inserts?

    answer it out loud →
15. 15.How does SQL treat NULL? Why doesn't = NULL work, and what do COALESCE and NULLIF do?easy

    NULL means "unknown" or "missing", not zero or an empty string. SQL uses three-valued logic: any comparison with NULL, even NULL = NULL, yields NULL (unknown), and WHERE only keeps rows where the condition is true. So WHERE col = NULL never matches; you write IS NULL or IS NOT NULL.

    Consequences to know:

    • Arithmetic and || concatenation with NULL give NULL.
    • WHERE dept_id <> 1 silently skips rows where dept_id is NULL.
    • Aggregates ignore NULLs, and sum over zero rows is NULL, not 0.
    • IS DISTINCT FROM is a null-safe comparison.

    COALESCE(a, b, c) returns the first non-NULL argument, handy for defaults like COALESCE(sum(amount), 0). NULLIF(a, b) returns NULL when a = b, the classic trick to avoid division by zero: total / NULLIF(count, 0).

    SELECT NULL = NULL;               -- NULL, not true
    SELECT NULL IS NULL;              -- true
    SELECT 10 + NULL, 'a' || NULL;    -- NULL, NULL
    SELECT 1 IS DISTINCT FROM NULL;   -- true
    SELECT COALESCE(NULL, NULL, 'x'); -- 'x'
    SELECT NULLIF(5, 5);              -- NULL

    What interviewers listen for

    • NULL = unknown; three-valued logic
    • Comparisons with NULL yield NULL, not true/false
    • Use IS NULL, never = NULL
    • COALESCE returns the first non-NULL value
    • NULLIF avoids division by zero

    Likely follow-up: Why does NOT IN return no rows when the subquery contains a NULL?

    answer it out loud →
16. 16.When would you use a subquery instead of a join? Is one faster than the other?mid

    They answer different shapes of question, so start with semantics:

    • A join returns columns from both tables and can multiply rows: joining departments to employees returns a department once per employee.
    • A subquery with IN or EXISTS is a semi-join: it only asks "is there a match?", so each outer row appears at most once. That's cleaner than a join plus DISTINCT.
    • A scalar subquery returns one value, such as comparing a salary to the overall average.

    On performance, a good optimizer often produces the same plan. PostgreSQL turns both IN (SELECT ...) and EXISTS into a semi-join and NOT EXISTS into an anti-join. The differences show up at the edges: a correlated subquery in the SELECT list may be executed once per outer row, and NOT IN can't be turned into an anti-join because of NULL semantics.

    So write the version that states the intent most clearly, then check EXPLAIN if it's slow.

    What interviewers listen for

    • Joins can multiply rows; semi-joins don't
    • IN/EXISTS express "has a match" without DISTINCT
    • Optimizers often produce identical plans
    • Correlated SELECT-list subqueries may run per row
    • Verify with EXPLAIN rather than folklore

    Likely follow-up: What is a correlated subquery? · What is the difference between EXISTS and IN?

    answer it out loud →
17. 17.How would you get the top 2 highest-paid employees in each department?mid

    This is the "top N per group" pattern. The standard answer is a window function in a subquery or CTE, because window functions are computed after WHERE, so you can't filter on them directly:

    1. Number the rows inside each department with PARTITION BY dept_id ORDER BY salary DESC. 2. Keep the rows numbered 1 and 2 in the outer query.

    Then settle ties: ROW_NUMBER returns exactly 2 rows per department, breaking ties arbitrarily unless you add a tiebreaker such as id. DENSE_RANK returns everyone in the top two salary levels, which could be more than two people.

    In PostgreSQL, a LATERAL join with ORDER BY ... LIMIT 2 is an alternative that can be much faster when there are few groups and an index on (dept_id, salary DESC), because it does a short index scan per department instead of ranking the whole table.

    SELECT dept_id, name, salary
    FROM (
      SELECT e.*,
             ROW_NUMBER() OVER (PARTITION BY dept_id
                                ORDER BY salary DESC, id) AS rn
      FROM employees e
    ) ranked
    WHERE rn <= 2;

    What interviewers listen for

    • Window function with PARTITION BY the group
    • Filter in an outer query or CTE
    • ROW_NUMBER vs DENSE_RANK decides tie handling
    • LATERAL ... LIMIT N with an index is an alternative

    Likely follow-up: How would you write this with LATERAL? · Why can't you put rn <= 2 in the inner WHERE?

    answer it out loud →
18. 18.What are window functions, and how is PARTITION BY different from GROUP BY?mid

    A window function computes a value over a set of rows related to the current row, without collapsing them. GROUP BY turns each group into one output row; a window function keeps every row and adds a column.

    The syntax is function() OVER (PARTITION BY ... ORDER BY ... frame):

    • PARTITION BY splits rows into groups, like GROUP BY, but only for the calculation.
    • ORDER BY orders rows within the partition, which matters for ranking, LAG/LEAD and running totals.
    • The frame (for example ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) chooses which rows an aggregate sees.

    Families: ranking (ROW_NUMBER, RANK, DENSE_RANK, NTILE), offset (LAG, LEAD, FIRST_VALUE) and ordinary aggregates used with OVER.

    They're evaluated after WHERE, GROUP BY and HAVING, so to filter on one you wrap the query in a subquery or CTE.

    -- Every employee, plus their department's average
    SELECT name, dept_id, salary,
           avg(salary) OVER (PARTITION BY dept_id) AS dept_avg,
           salary - avg(salary) OVER (PARTITION BY dept_id) AS diff
    FROM employees;

    What interviewers listen for

    • Computes across related rows, keeps every row
    • PARTITION BY groups only for the calculation
    • ORDER BY and frame control ordering and scope
    • Ranking, offset and aggregate window functions
    • Can't be used in WHERE; wrap in a subquery

    Likely follow-up: What is the default window frame when you add ORDER BY?

    answer it out loud →
19. 19.In what order are the clauses of a SELECT statement logically evaluated?easy

    The order you write clauses isn't the order they're evaluated. Logically it's:

    • FROM and JOINs: build the combined row set
    • WHERE: filter rows
    • GROUP BY: form groups
    • HAVING: filter groups
    • SELECT: compute expressions, including window functions
    • DISTINCT: remove duplicates
    • ORDER BY: sort
    • LIMIT / OFFSET: take a slice

    This explains many common errors. You can't use a SELECT alias in WHERE, because WHERE runs first; you can in ORDER BY, because it runs after. You can't use aggregates in WHERE, and you can't filter on a window function without wrapping the query.

    It's a logical model, not the physical plan: the optimizer is free to reorder work, such as pushing filters into joins or using an index to avoid a sort, as long as the result is the same.

    What interviewers listen for

    • FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT
    • Aliases work in ORDER BY, not in WHERE
    • Window functions are computed at the SELECT step
    • Logical order, not the physical execution plan

    answer it out loud →
20. 20.Explain the transaction isolation levels and the anomalies each one allows. What does PostgreSQL do by default?hard

    The SQL standard defines four levels by which anomalies they must prevent:

    • Dirty read: seeing another transaction's uncommitted data.
    • Non-repeatable read: re-reading a row and getting a different value because someone committed in between.
    • Phantom read: re-running a query and getting a different set of rows.
    • Serialization anomaly: a result no serial ordering could produce, such as write skew.

    Read Uncommitted allows all of them; Read Committed prevents dirty reads; Repeatable Read also prevents non-repeatable reads; Serializable prevents everything.

    PostgreSQL is stricter than the standard. Its default is Read Committed, where each statement sees a fresh snapshot. Read Uncommitted behaves like Read Committed, so dirty reads never happen. Repeatable Read uses one snapshot for the whole transaction, which also prevents phantoms, and fails with "could not serialize access due to concurrent update" if you modify a row someone else changed. Serializable adds Serializable Snapshot Isolation. At both higher levels, be ready to retry on SQLSTATE 40001.

    MySQL InnoDB defaults to Repeatable Read.

    What interviewers listen for

    • Dirty, non-repeatable, phantom, serialization anomaly
    • PostgreSQL default is Read Committed
    • PG never allows dirty reads; RR prevents phantoms
    • Serializable uses SSI; retry on 40001
    • MySQL InnoDB defaults to Repeatable Read

    Likely follow-up: What is write skew and which level prevents it? · How would you implement a retry loop for serialization failures?

    answer it out loud →
21. 21.What is the difference between EXISTS and IN? Why is NOT IN dangerous when the subquery can return NULL?mid

    IN compares a value against a list or subquery result; EXISTS just checks whether a correlated subquery returns at least one row. For the positive case they're equivalent, and PostgreSQL plans both as the same semi-join, so the old "EXISTS is always faster" advice is mostly folklore.

    The negative forms are not equivalent. x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL. The last comparison is NULL (unknown), so the whole condition can never be true, and the query returns no rows at all. One stray NULL in the subquery empties your result without any error.

    NOT EXISTS doesn't have this problem, because it only asks whether a matching row exists. It's also the form PostgreSQL can turn into an efficient anti-join; NOT IN with a subquery can't be, precisely because of the NULL rule.

    So default to NOT EXISTS, or filter WHERE col IS NOT NULL inside a NOT IN subquery.

    -- HR has no employees; Flo's dept_id is NULL
    SELECT name FROM departments
    WHERE id NOT IN (SELECT dept_id FROM employees);  -- 0 rows!
    
    SELECT name FROM departments d
    WHERE NOT EXISTS (SELECT 1 FROM employees e
                      WHERE e.dept_id = d.id);        -- HR

    What interviewers listen for

    • IN and EXISTS are equivalent semi-joins when positive
    • NOT IN with any NULL returns no rows
    • Three-valued logic: x <> NULL is unknown
    • NOT EXISTS is NULL-safe and becomes an anti-join

    Likely follow-up: How else could you write an anti-join?

    answer it out loud →
22. 22.What is a CTE (WITH clause), and when would you use one instead of a subquery?easy

    A common table expression is a named, temporary result set defined with WITH that exists only for one statement. It's mainly about readability: you build a query in steps, top to bottom, instead of nesting subqueries inside out, and you can reference the same CTE more than once.

    Use one when:

    • a query has several logical steps, such as aggregate, then rank, then filter;
    • you need recursion (WITH RECURSIVE) for hierarchies or series;
    • in PostgreSQL, you want a data-modifying CTE, like WITH moved AS (DELETE ... RETURNING *) INSERT INTO archive SELECT * FROM moved.

    Performance note for PostgreSQL: before version 12, every CTE was materialized and acted as an optimization fence, so filters outside it weren't pushed in. Since 12, a non-recursive CTE without side effects that's referenced once is inlined like a subquery. You can still force either behavior with AS MATERIALIZED or AS NOT MATERIALIZED.

    WITH dept_avg AS (
      SELECT dept_id, avg(salary) AS avg_sal
      FROM employees
      GROUP BY dept_id
    )
    SELECT e.name, e.salary, d.avg_sal
    FROM employees e
    JOIN dept_avg d USING (dept_id)
    WHERE e.salary > d.avg_sal;

    What interviewers listen for

    • Named temporary result for one statement
    • Improves readability; can be referenced repeatedly
    • Enables recursion and data-modifying statements
    • PG 12+ inlines simple CTEs; before that, a fence
    • MATERIALIZED / NOT MATERIALIZED override it

    Likely follow-up: How does a recursive CTE work?

    answer it out loud →
23. 23.What is a correlated subquery? Write one that finds employees who earn more than their department's average.mid

    A correlated subquery references a column from the outer query, so logically it's re-evaluated for every outer row. A non-correlated subquery is independent and can be computed once.

    Here, x.dept_id = e.dept_id ties the inner query to the current outer employee, so each employee is compared against the average of their own department.

    Performance depends on the optimizer. PostgreSQL turns correlated EXISTS and IN into joins, but a correlated scalar subquery like this one usually runs as a SubPlan once per outer row. That's fine for small tables or with an index on dept_id, but on large ones rewrite it as:

    • a join to a pre-aggregated derived table or CTE (compute each average once), or
    • a window function: avg(salary) OVER (PARTITION BY dept_id) in a subquery, then filter.

    Note that employees with a NULL dept_id drop out, because x.dept_id = NULL matches nothing.

    SELECT e.name, e.salary
    FROM employees e
    WHERE e.salary > (
      SELECT avg(x.salary)
      FROM employees x
      WHERE x.dept_id = e.dept_id   -- correlation
    );

    What interviewers listen for

    • Inner query references the outer row
    • Logically evaluated once per outer row
    • Scalar ones often run as a per-row SubPlan
    • Rewrite as a join to an aggregate or a window function

    Likely follow-up: Rewrite it with a window function.

    answer it out loud →
24. 24.What is a self join? Write a query that lists each employee with their manager's name.easy

    A self join joins a table to itself, using two aliases so each copy plays a different role. It's the standard tool for hierarchical or pairwise data stored in one table: employees and managers, flights and connecting flights, or comparing rows with other rows in the same table.

    In employees, manager_id points at another row's id. Alias one copy as the employee (e) and one as the manager (m), then join m.id = e.manager_id.

    Use a LEFT JOIN, not an inner join: the CEO has manager_id IS NULL, and an inner join would silently drop them.

    The same pattern answers the classic follow-up "who earns more than their manager?": use an inner join and add WHERE e.salary > m.salary. For an arbitrary number of levels, such as the whole reporting chain, a single self join isn't enough; you need a recursive CTE.

    SELECT e.name AS employee, m.name AS manager
    FROM employees e
    LEFT JOIN employees m ON m.id = e.manager_id;
    
    -- employee | manager
    -- Ada      | NULL      (kept by LEFT JOIN)
    -- Bob      | Ada
    -- Ed       | Di

    What interviewers listen for

    • Same table joined to itself with two aliases
    • Used for hierarchies and row-to-row comparisons
    • LEFT JOIN keeps rows without a manager
    • Multi-level hierarchies need a recursive CTE

    Likely follow-up: Find employees who earn more than their manager.

    answer it out loud →
25. 25.What is the difference between a view and a materialized view?mid

    A view is a stored query with a name. It holds no data; every time you select from it, the database expands it into the underlying query and runs it, so it's always current. Views are used for simplifying complex joins, providing a stable interface over a changing schema, and access control: grant access to the view but not the base tables. In PostgreSQL, simple single-table views are automatically updatable, and WITH CHECK OPTION rejects writes that fall outside the view's WHERE.

    A materialized view runs the query once and stores the result like a table. Reads are fast and can be indexed, but the data is a snapshot. In PostgreSQL it only changes when you run REFRESH MATERIALIZED VIEW, which blocks reads while it runs. REFRESH ... CONCURRENTLY avoids blocking readers but requires a unique index on the materialized view.

    Rule of thumb: views for abstraction; materialized views for expensive aggregations, such as dashboards, where slightly stale data is acceptable.

    What interviewers listen for

    • View = stored query, no data, always current
    • Materialized view = stored result, can be indexed
    • MV data is stale until refreshed
    • REFRESH ... CONCURRENTLY needs a unique index
    • Views help with abstraction and access control

    Likely follow-up: How would you keep a materialized view fresh?

    answer it out loud →
26. 26.What is SQL injection, and how do prepared statements prevent it?easy

    SQL injection happens when user input is concatenated into a SQL string, so the input can change the query's structure. With "... WHERE email = '" + input + "'", an input of ' OR '1'='1 turns the filter into one that matches every row, and worse inputs can read or destroy other data.

    The fix is parameterized queries (prepared statements): the SQL text contains placeholders, such as $1 in PostgreSQL or ? in JDBC, and the values are sent separately. The database parses the query first and only then binds the values, so the input is always treated as data and never as SQL.

    Things to add in an interview:

    • Placeholders only work for values. Table or column names can't be parameters; validate them against an allowlist.
    • ORMs are safe until you build raw fragments with string concatenation.
    • Stored procedures that build dynamic SQL by concatenation are just as vulnerable.
    • Defense in depth: a least-privilege database role limits the damage.

    What interviewers listen for

    • Caused by concatenating input into SQL text
    • Parameterized queries send SQL and values separately
    • Values are bound after parsing, never executed
    • Identifiers can't be parameters: use an allowlist
    • Least-privilege roles limit the blast radius

    Likely follow-up: Is escaping quotes a good enough defense?

    answer it out loud →
27. 27.What is the difference between count(*), count(column) and count(DISTINCT column)?easy

    • count(*) counts rows, regardless of their content.
    • count(column) counts rows where that column is not NULL.
    • count(DISTINCT column) counts distinct non-NULL values.

    With six employees where one has no department, count(*) is 6, count(dept_id) is 5, and count(DISTINCT dept_id) is 2. That difference is a common source of bugs, for example counting count(o.id) versus count(*) after a LEFT JOIN: count(*) counts a customer with no orders as 1, while count(o.id) correctly gives 0.

    count(1) gives the same result as count(*); it isn't faster.

    A PostgreSQL-specific point: because of MVCC, there's no stored row count that's valid for every transaction, so SELECT count(*) FROM big_table has to scan the table or an index. If an estimate is enough, read reltuples from pg_class.

    What interviewers listen for

    • count(*) counts rows
    • count(col) skips NULLs
    • count(DISTINCT col) counts distinct non-NULL values
    • Use count(child.id) after a LEFT JOIN
    • PG count(*) scans; no cached row count

    answer it out loud →
28. 28.How do you use EXPLAIN and EXPLAIN ANALYZE in PostgreSQL, and what do you look for in the output?mid

    EXPLAIN shows the plan the optimizer chose, with estimated costs and row counts, without running the query. EXPLAIN ANALYZE actually executes it and adds real timings, row counts and loop counts; adding BUFFERS shows how many pages came from cache (hit) or disk (read). Because it executes, wrap an UPDATE or DELETE in BEGIN ... ROLLBACK.

    How to read it:

    • It's a tree; the most indented nodes run first and feed their parents.
    • cost=0.42..41.40 is startup cost..total cost in planner units, not milliseconds.
    • Compare estimated rows vs actual rows. A large mismatch means bad statistics, so run ANALYZE, and it's the usual root cause of a bad plan.
    • Actual time and rows are per loop; multiply by loops.
    • Look for scan types (Seq Scan, Index Scan, Bitmap Heap Scan), expensive sorts spilling to disk, and nested loops over many rows.

    EXPLAIN (ANALYZE, BUFFERS)
    SELECT * FROM orders
    WHERE customer_id = 42
    ORDER BY created_at DESC LIMIT 10;
    
    -- Limit (cost=0.42..41.40 rows=10) (actual time=0.039..0.063 rows=10 loops=1)
    --   -> Index Scan Backward using orders_customer_created_idx on orders
    --        Index Cond: (customer_id = 42)
    --        Buffers: shared hit=10 read=3
    -- Execution Time: 0.101 ms

    What interviewers listen for

    • EXPLAIN estimates; EXPLAIN ANALYZE executes
    • Read the tree from the innermost nodes
    • Compare estimated vs actual rows
    • Actual numbers are per loop
    • BUFFERS shows cache hits vs disk reads

    Likely follow-up: What is the difference between a seq scan, an index scan and a bitmap heap scan?

    answer it out loud →
29. 29.Does column order matter in a composite index? How would you choose it?mid

    Yes. A B-tree on (customer_id, created_at) is sorted by customer_id first, and by created_at only within each customer, like a phone book sorted by last name, then first name.

    That gives the leftmost prefix rule. The index efficiently serves:

    • WHERE customer_id = ?
    • WHERE customer_id = ? AND created_at > ?
    • WHERE customer_id = ? ORDER BY created_at DESC LIMIT 10, with no sort needed

    It doesn't efficiently serve WHERE created_at > ? alone, because matching rows are scattered across every customer. Some engines, including recent MySQL and PostgreSQL 18, can skip scan when the leading column has few distinct values, but don't design around it.

    How to choose: put equality columns first and the range or sort column last, because once a range condition is applied, later columns can't narrow the scan. Then order by which queries you actually run. "Most selective column first" matters less than equality-before-range. And one (a, b) index usually makes a separate index on a redundant.

    What interviewers listen for

    • Sorted by the first column, then the next
    • Leftmost prefix rule
    • Equality columns first, range or sort column last
    • (a, b) makes a separate index on a redundant

    Likely follow-up: Would you build an index on (created_at, customer_id) instead? When?

    answer it out loud →
30. 30.A query in production has become slow. How do you approach optimizing it?mid

    I'd work from evidence, not guesses:

    • Find and measure: use pg_stat_statements or the slow query log to see which queries cost the most in total, not just the slowest single run.
    • Read the plan with EXPLAIN (ANALYZE, BUFFERS). Look for estimate vs actual row mismatches (stale statistics, so ANALYZE), sequential scans on selective filters, sorts or hashes spilling to disk, and nested loops over many rows.
    • Index for the query: the right composite column order, a covering or partial index, or an expression index if the filter wraps the column in a function.
    • Rewrite: keep predicates sargable (no lower(col) = ... without a matching index, no leading-wildcard LIKE '%x'), select only needed columns, replace deep OFFSET with keyset pagination, and fix N+1 query patterns in the application.
    • Reduce work: pre-aggregate with a materialized view, partition huge tables, cache results.

    Then re-measure, and check that a new index doesn't slow down writes too much.

    What interviewers listen for

    • Measure first: pg_stat_statements, slow query log
    • EXPLAIN ANALYZE; compare estimated vs actual rows
    • Targeted indexes; keep predicates sargable
    • Fix N+1, deep OFFSET, SELECT *
    • Re-measure and watch write overhead

    Likely follow-up: What does "sargable" mean?

    answer it out loud →
31. 31.What are the differences between SQL and NoSQL databases, and how do you choose between them?easy

    Relational (SQL) databases such as PostgreSQL and MySQL store data in tables with a fixed schema, enforce relationships with foreign keys, support joins and ad-hoc queries, and give you multi-row ACID transactions. They traditionally scale up (bigger machine) plus read replicas, with sharding as extra work.

    NoSQL is an umbrella for several models: document (MongoDB), key-value (Redis, DynamoDB), wide-column (Cassandra) and graph (Neo4j). They typically offer a flexible or schema-on-read model, are designed to scale horizontally across many nodes, and many relax consistency in favor of availability and throughput. You design around your access patterns, often by denormalizing, because joins are limited or absent.

    How I'd choose: default to relational when data is relational and correctness matters, such as orders, payments and inventory. Choose NoSQL for a specific need: massive write throughput, a simple key-based access pattern, very flexible documents, or graph traversals. The line has blurred: PostgreSQL has JSONB, and several NoSQL stores now offer transactions.

    What interviewers listen for

    • SQL: fixed schema, joins, ACID transactions
    • NoSQL: document, key-value, wide-column, graph
    • NoSQL is usually built to scale horizontally
    • NoSQL models data around access patterns
    • Pick by data shape, consistency and scale needs

    Likely follow-up: What does the CAP theorem say? · When would you store JSON in PostgreSQL instead of using MongoDB?

    answer it out loud →
32. 32.How do you paginate results? Why does OFFSET pagination get slow, and what is keyset pagination?mid

    The simple way is ORDER BY created_at DESC LIMIT 20 OFFSET 400000. The problem is that OFFSET doesn't skip rows for free: the database still produces and discards all 400,000 of them, so deep pages get linearly slower. It's also unstable: if rows are inserted or deleted between requests, users see duplicates or miss rows.

    Keyset (cursor) pagination remembers the last row of the previous page and asks for rows "after" it: WHERE (created_at, id) < (last_created_at, last_id). With an index on (created_at, id), every page is a short index range scan, no matter how deep. The id tiebreaker makes the ordering unique, so no rows are skipped when timestamps tie.

    Trade-offs: keyset can't jump to "page 57" directly and requires a deterministic, indexed sort order; it's ideal for infinite scroll and APIs with a next_cursor. OFFSET is fine for small tables or when users rarely go past the first few pages.

    -- Page N: reads and throws away every skipped row
    SELECT id, created_at FROM orders
    ORDER BY created_at DESC, id DESC
    LIMIT 20 OFFSET 400000;
    
    -- Keyset: seek straight past the last row seen
    SELECT id, created_at FROM orders
    WHERE (created_at, id) < ($1, $2)
    ORDER BY created_at DESC, id DESC
    LIMIT 20;

    What interviewers listen for

    • OFFSET still reads and discards skipped rows
    • OFFSET pages shift when data changes
    • Keyset filters past the last seen key
    • Needs a unique, indexed sort order (add id)
    • Keyset can't jump to an arbitrary page

    Likely follow-up: How would you encode the cursor in an API response?

    answer it out loud →
33. 33.How does a recursive CTE work? Write one that walks an employee–manager hierarchy.hard

    A recursive CTE has two parts joined by UNION ALL:

    • The anchor query runs once and produces the starting rows, here the top of the tree (manager_id IS NULL).
    • The recursive query references the CTE itself. Conceptually, each iteration runs it against only the rows produced by the previous iteration, and the loop stops when an iteration produces no new rows.

    Carrying extra columns is the useful trick: depth + 1 gives the level, and accumulating an array or string of ids gives the path from the root.

    Things to mention:

    • Cycles: if the data can loop (A manages B manages A), UNION ALL recurses forever. UNION stops only when entire rows repeat, so it fails once you carry depth. Use a path array with a NOT (id = ANY(path)) check, a depth limit, or PostgreSQL 14's CYCLE clause.
    • Common uses: org charts, category trees, bill of materials, graph reachability and generating series.

    WITH RECURSIVE chain AS (
      SELECT id, name, manager_id, 1 AS depth
      FROM employees
      WHERE manager_id IS NULL                 -- anchor
      UNION ALL
      SELECT e.id, e.name, e.manager_id, c.depth + 1
      FROM employees e
      JOIN chain c ON e.manager_id = c.id      -- recursive step
    )
    SELECT * FROM chain ORDER BY depth, id;

    What interviewers listen for

    • Anchor query plus recursive query with UNION ALL
    • Each step joins to the previous step's rows
    • Stops when a step returns no rows
    • Carry depth or path columns
    • Guard against cycles: path check, depth limit, CYCLE

    Likely follow-up: How would you find all subordinates of one manager, at any depth?

    answer it out loud →
34. 34.What is denormalization, and when is it the right choice?mid

    Denormalization is deliberately adding redundancy to a normalized schema to make reads cheaper: storing a copied customer_name on orders, keeping a precomputed order_count on customers, or building wide reporting tables so queries don't need five joins.

    It's the right choice when you've measured that joins or aggregations are a bottleneck and the data is read far more often than it changes. It's standard in analytics: star schemas and data warehouses are denormalized on purpose.

    The cost is that the same fact now lives in several places, so you must keep the copies in sync, and every write does more work. Ways to manage that:

    • triggers or application code that update the copies in the same transaction;
    • materialized views, refreshed on a schedule, when some staleness is acceptable;
    • caches or read models built from change events.

    Normalize first for correctness, then denormalize specific hot paths with a plan for consistency.

    What interviewers listen for

    • Intentional redundancy to speed up reads
    • Justified by measured read bottlenecks
    • Costs: extra write work, risk of inconsistency
    • Keep in sync: triggers, MVs, events
    • Common in OLAP and star schemas

    answer it out loud →
35. 35.What constraints can you define on a table, and why enforce rules in the database rather than only in the application?easy

    The standard constraints are:

    • NOT NULL: the column must have a value.
    • UNIQUE: no two rows share the value (or combination of values).
    • PRIMARY KEY: unique and not null; identifies the row.
    • FOREIGN KEY: the value must exist in the referenced table.
    • CHECK: an arbitrary boolean condition, such as CHECK (price >= 0) or CHECK (end_date > start_date).
    • DEFAULT isn't strictly a constraint, but supplies a value when none is given.

    PostgreSQL also has exclusion constraints, for example "no two bookings for the same room may overlap".

    Why in the database: it's the last line of defense. Every code path, script, migration and future service goes through it, and constraints hold under concurrency. Two application servers can both check "email not taken" and both insert; only a UNIQUE constraint actually prevents the duplicate. Constraints also document the data model and help the optimizer. Validate in the application too, for friendly error messages.

    What interviewers listen for

    • NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK
    • PostgreSQL adds exclusion constraints
    • Database constraints hold for every client
    • App-side checks race under concurrency
    • Validate in the app too, for good UX

    answer it out loud →
36. 36.How do you do an "upsert" in PostgreSQL, and why is it better than checking whether the row exists first?mid

    PostgreSQL's upsert is INSERT ... ON CONFLICT. You name the conflict target, a column list or constraint backed by a unique index, and choose an action:

    • DO NOTHING: skip rows that would violate the constraint.
    • DO UPDATE SET ...: update the existing row instead. The special EXCLUDED table holds the values you tried to insert, and an optional WHERE makes the update conditional.

    It's better than "SELECT, then INSERT or UPDATE" because that pattern is a race: two sessions can both see no row and both insert, and one fails, or both update from stale values. ON CONFLICT resolves the conflict atomically in one statement.

    Gotchas: there must be a unique index matching the target, or you get an error; a single statement can't upsert the same key twice ("cannot affect row a second time"), so deduplicate the input first. MySQL's equivalent is INSERT ... ON DUPLICATE KEY UPDATE, and the SQL standard has MERGE, which PostgreSQL supports since version 15.

    INSERT INTO inventory (sku, qty)
    VALUES ('A', 3), ('B', 7)
    ON CONFLICT (sku) DO UPDATE
    SET qty = inventory.qty + EXCLUDED.qty,
        updated_at = now()
    RETURNING sku, qty;

    What interviewers listen for

    • INSERT ... ON CONFLICT with DO NOTHING or DO UPDATE
    • EXCLUDED holds the proposed row
    • Requires a matching unique index or constraint
    • Atomic, unlike check-then-insert
    • Can't hit the same key twice in one statement

    Likely follow-up: How is MERGE different from ON CONFLICT?

    answer it out loud →
37. 37.What do LAG and LEAD do? Show how you would compute the day-over-day change in revenue.mid

    LAG(expr, offset, default) returns the value of expr from a row before the current one in the window's order; LEAD looks ahead. The offset defaults to 1, and the default value (otherwise NULL) is used when there's no such row, as on the first or last row.

    They replace the old self-join on "t2.day = t1.day - 1", which breaks when days are missing and is slower. With LAG it's one pass over sorted rows.

    Typical uses:

    • day-over-day or month-over-month change and growth percentage;
    • time between consecutive events per user, with PARTITION BY user_id;
    • detecting status changes, or gaps and islands in sequences.

    Note that LAG compares with the previous row, not the previous calendar day. If days can be missing and you need true calendar comparisons, generate the dates first (for example with generate_series) and left join the data onto them.

    SELECT day, revenue,
           LAG(revenue) OVER (ORDER BY day)           AS prev,
           revenue - LAG(revenue) OVER (ORDER BY day) AS change
    FROM daily;
    
    --    day     | revenue | prev | change
    -- 2024-01-01 |     100 | NULL |   NULL
    -- 2024-01-02 |     120 |  100 |     20
    -- 2024-01-03 |      90 |  120 |    -30

    What interviewers listen for

    • LAG looks back, LEAD looks ahead
    • Optional offset and default value
    • Replaces fragile self-joins on adjacent rows
    • PARTITION BY for per-entity sequences
    • Previous row, not necessarily previous day

    answer it out loud →
38. 38.How do you calculate a running total with a window function? What is the subtle issue with the default frame?mid

    A running total is sum(amount) OVER (ORDER BY day), optionally with PARTITION BY account_id to restart per account. It's one pass over sorted rows, instead of the old correlated subquery that re-sums everything for each row.

    The subtlety: when a window has an ORDER BY and no explicit frame, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE includes the current row's peers, all rows with the same ORDER BY value. So if two sales happen on the same day, both rows show the total including both, rather than increasing row by row.

    If you want a strictly row-by-row total, say ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and make the order unique, for example ORDER BY day, id; otherwise the order between tied rows is arbitrary.

    The same frame syntax gives moving windows: avg(x) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) is a 7-row moving average.

    SELECT day, amount,
      sum(amount) OVER (ORDER BY day) AS range_total,
      sum(amount) OVER (ORDER BY day, id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_total
    FROM sales;
    
    --    day     | amount | range_total | rows_total
    -- 2024-01-01 |     10 |          10 |         10
    -- 2024-01-02 |     20 |          35 |         30
    -- 2024-01-02 |      5 |          35 |         35

    What interviewers listen for

    • sum(...) OVER (ORDER BY ...) gives a running total
    • Default frame with ORDER BY is RANGE ... CURRENT ROW
    • RANGE includes tied peer rows
    • Use ROWS plus a unique order for row-by-row
    • Frames also give moving averages

    Likely follow-up: How would you compute a 7-day moving average when some days are missing?

    answer it out loud →
39. 39.When can adding an index hurt, or simply not be used?mid

    Indexes hurt when:

    • The table is write-heavy: every insert, delete and update of an indexed column must maintain every index. In PostgreSQL, indexing a column can also prevent HOT updates, which skip index maintenance only when no indexed column changes and the page has free space.
    • You have redundant indexes, like (a) next to (a, b), or unused ones: they cost writes, disk, cache and VACUUM time for no benefit.
    • Bulk loads: it's often faster to drop indexes, load, then rebuild.

    An index isn't used when:

    • The filter matches a large fraction of the table (low selectivity, such as a status with four values); a sequential scan is cheaper.
    • The predicate isn't sargable: lower(email) = ..., created_at::date = ..., col + 0 = 42, a type mismatch, or LIKE '%term'.
    • The query doesn't use the index's leading column.
    • The table is tiny, or statistics are stale.

    Check pg_stat_user_indexes for indexes with idx_scan = 0 before adding more.

    What interviewers listen for

    • Every index taxes writes and storage
    • Redundant or unused indexes are pure cost
    • Low selectivity favors a seq scan
    • Functions or casts on the column block index use
    • Find unused indexes in pg_stat_user_indexes

    Likely follow-up: What is a HOT update?

    answer it out loud →
40. 40.What is MVCC, and how does PostgreSQL implement it?hard

    Multi-Version Concurrency Control lets readers and writers work concurrently by keeping several versions of a row instead of locking it for reads.

    In PostgreSQL, every row version (tuple) carries hidden system columns: xmin, the ID of the transaction that created it, and xmax, the transaction that deleted or locked it. An UPDATE never overwrites in place: it writes a new version and sets xmax on the old one, so the row's physical location (ctid) changes. A DELETE just sets xmax.

    Each statement (Read Committed) or transaction (Repeatable Read and up) takes a snapshot, and visibility rules based on xmin, xmax and commit status decide which version it sees.

    The payoffs: readers don't block writers and writers don't block readers; only two writers on the same row wait for each other. The costs: old versions become dead tuples that VACUUM must clean up, tables and indexes can bloat, and transaction IDs must be periodically "frozen" to avoid wraparound.

    What interviewers listen for

    • Multiple row versions instead of read locks
    • Tuples carry xmin/xmax transaction IDs
    • UPDATE writes a new version; old one becomes dead
    • Snapshots decide which version is visible
    • Cost: dead tuples, VACUUM, bloat, wraparound

    Likely follow-up: Why does a long-running transaction cause bloat? · What is transaction ID wraparound?

    answer it out loud →
41. 41.What does SELECT ... FOR UPDATE do, and when do you need it? What are NOWAIT and SKIP LOCKED?hard

    FOR UPDATE reads rows and locks them as if they were about to be updated. Other transactions that try to update, delete or lock those rows wait until you commit or roll back. Plain SELECTs aren't blocked, thanks to MVCC.

    You need it for read-modify-write logic done in application code. Without it, two sessions can read a balance of 100, both subtract 30 and both write 70: a lost update. With FOR UPDATE, the second session waits and then reads the new value. (A single UPDATE ... SET bal = bal - 30 is already safe, because the update itself locks the row.)

    Variants:

    • NOWAIT: raise an error immediately instead of waiting for a lock.
    • SKIP LOCKED: silently skip rows that are already locked. This is the standard way to build a job queue in PostgreSQL: each worker grabs different rows without blocking the others.
    • Weaker modes: FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE.

    Keep these transactions short, and lock rows in a consistent order to avoid deadlocks.

    -- Each worker claims a different job without blocking
    BEGIN;
    SELECT id FROM jobs
    WHERE status = 'queued'
    ORDER BY id
    LIMIT 1
    FOR UPDATE SKIP LOCKED;
    -- ... process the job, then:
    UPDATE jobs SET status = 'done' WHERE id = $1;
    COMMIT;

    What interviewers listen for

    • Locks selected rows until the transaction ends
    • Prevents lost updates in read-modify-write
    • Plain SELECTs are not blocked
    • NOWAIT errors; SKIP LOCKED skips locked rows
    • SKIP LOCKED powers work queues

    Likely follow-up: What is the difference between pessimistic and optimistic locking?

    answer it out loud →
42. 42.What is a deadlock in a database, how does PostgreSQL handle it, and how do you prevent deadlocks?hard

    A deadlock is a cycle of waits: transaction A holds a lock on row 1 and wants row 2, while B holds row 2 and wants row 1. Neither can proceed, ever.

    PostgreSQL detects this automatically. When a transaction has waited for a lock longer than deadlock_timeout (1 second by default), it checks for a cycle, and if it finds one, it aborts one of the transactions with "deadlock detected" (SQLSTATE 40P01). The other one continues. The application should catch the error and retry the whole transaction.

    Prevention:

    • Lock in a consistent order: for a transfer, always update the lower account id first, whatever the direction of the money.
    • Keep transactions short, and don't wait on user input or network calls while holding locks.
    • Lock everything you need up front, such as SELECT ... WHERE id IN (1, 2) ORDER BY id FOR UPDATE.
    • Use the weakest lock that works, and index foreign keys so checks don't scan and lock more than necessary.

    Deadlocks can't be ruled out completely, so the retry logic matters.

    What interviewers listen for

    • Cycle of transactions waiting on each other
    • PG checks after deadlock_timeout and aborts one
    • Victim gets SQLSTATE 40P01; retry it
    • Acquire locks in a consistent order
    • Keep transactions short

    Likely follow-up: How would you find out which queries were involved in a deadlock?

    answer it out loud →
43. 43.What is the difference between OLTP and OLAP workloads?easy

    OLTP (online transaction processing) is the operational side of an application: many small, concurrent transactions that read or write a few rows at a time, such as placing an order, updating a profile or checking out a cart. It needs low latency, strong consistency and high concurrency. Schemas are normalized, and queries are served by targeted indexes. PostgreSQL, MySQL and Oracle are typical.

    OLAP (online analytical processing) is analytics and reporting: fewer, much heavier queries that scan and aggregate millions of rows, like "revenue by region by month for three years". Data is often loaded in batches, denormalized into star or snowflake schemas, and stored in columnar engines such as Snowflake, BigQuery, Redshift or ClickHouse, which read only the needed columns and compress well.

    Running heavy analytics on the OLTP primary competes with user traffic, so teams move them to a read replica or copy the data into a warehouse with ETL or change data capture.

    What interviewers listen for

    • OLTP: many small, concurrent read/write transactions
    • OLAP: few large scans and aggregations
    • OLTP normalized; OLAP star schemas, denormalized
    • OLAP favors columnar storage
    • Keep analytics off the OLTP primary

    answer it out loud →
44. 44.What is a CROSS JOIN, and when is it actually useful?easy

    A CROSS JOIN returns the Cartesian product: every row of the first table combined with every row of the second. There's no join condition, so 1,000 rows times 1,000 rows gives a million. The old comma syntax, FROM a, b with no WHERE, does the same thing, which is why a forgotten join condition can accidentally explode a result.

    It's useful when you genuinely want every combination:

    • generating a grid, such as every product for every store, or every user for every day, and then LEFT JOINing actual sales onto it so missing combinations show up as zeros;
    • combining with a one-row result, like CROSS JOIN (SELECT avg(salary) AS avg FROM employees) a, to compare every row with a global value;
    • test data generation with generate_series.

    In PostgreSQL, CROSS JOIN LATERAL is a different, very useful tool: the right side is a subquery that can reference the current left row, as in "top 3 orders per customer".

    What interviewers listen for

    • Cartesian product: every row with every row
    • No join condition; m × n rows
    • Useful for grids of combinations to fill gaps
    • Accidental cross joins cause exploding results
    • CROSS JOIN LATERAL is per-row correlated

    answer it out loud →
45. 45.Why can putting a condition on the right-hand table in the WHERE clause turn a LEFT JOIN into an inner join?mid

    A LEFT JOIN keeps unmatched left rows by filling the right side's columns with NULL. Then WHERE runs after the join. A condition like e.salary > 100 evaluates to NULL (unknown) for those padded rows, so they're thrown away, and you get exactly the rows an inner join would return. Here, HR, which has no employees, disappears.

    The fix depends on intent:

    • If you want all departments, with only the matching employees, move the condition into the ON clause. The condition then decides which right rows match, and unmatched departments survive.
    • If you actually want departments that have such employees, write an INNER JOIN so the intent is clear.
    • Conditions on the left table belong in WHERE either way.

    The one right-table condition that does belong in WHERE is e.id IS NULL: that's the anti-join pattern for "departments with no employees".

    -- HR vanishes: its NULL-padded row fails the WHERE
    SELECT d.name, e.name
    FROM departments d
    LEFT JOIN employees e ON e.dept_id = d.id
    WHERE e.salary > 100;
    
    -- HR is kept, with e.name = NULL
    SELECT d.name, e.name
    FROM departments d
    LEFT JOIN employees e ON e.dept_id = d.id
                         AND e.salary > 100;

    What interviewers listen for

    • WHERE runs after the outer join
    • NULL-padded rows fail right-table conditions
    • Move right-table filters into ON
    • Left-table filters stay in WHERE
    • WHERE right.id IS NULL is the anti-join pattern

    answer it out loud →
46. 46.What is the difference between a function and a stored procedure in PostgreSQL?mid

    A function (CREATE FUNCTION) returns a value, a row or a set of rows, and is called inside a query: SELECT calc_tax(amount) FROM orders, in a WHERE, in an index expression or as a trigger. It always runs inside the caller's transaction and can't commit or roll back. You declare its volatility (IMMUTABLE, STABLE or VOLATILE), which tells the planner whether results can be cached or used in indexes.

    A procedure (CREATE PROCEDURE, added in PostgreSQL 11) is invoked on its own with CALL, not from a SELECT. Its key difference is transaction control: it can COMMIT and ROLLBACK in the middle, which is useful for batch jobs that process millions of rows in chunks. That only works when the CALL isn't inside an explicit transaction block; otherwise you get "invalid transaction termination".

    Both can be written in SQL or PL/pgSQL (among other languages). Other databases differ; in SQL Server, for example, procedures are the main way to package logic, and functions have more restrictions.

    What interviewers listen for

    • Functions return values and are used in queries
    • Functions can't commit or roll back
    • Procedures are invoked with CALL (PG 11+)
    • Procedures can COMMIT/ROLLBACK mid-way
    • Volatility labels guide the planner

    answer it out loud →
47. 47.What is a trigger? Write one that keeps an updated_at column current, and tell me when you would avoid triggers.mid

    A trigger runs a function automatically when an event happens on a table: INSERT, UPDATE, DELETE or TRUNCATE. In PostgreSQL you define a function that RETURNS trigger, then attach it with CREATE TRIGGER.

    • Timing: BEFORE triggers can inspect and modify the row (NEW) or return NULL to skip it; AFTER triggers see the final result, which suits audit logs; INSTEAD OF triggers make views writable.
    • Granularity: FOR EACH ROW runs once per affected row, with OLD and NEW; FOR EACH STATEMENT runs once per statement.

    Good uses: updated_at timestamps, audit trails, and enforcing invariants that constraints can't express.

    When to avoid them: they're invisible to someone reading application code, they add per-row overhead to bulk writes, and chains of triggers firing other triggers are hard to debug and reason about. Keep business workflows, like sending emails or calling APIs, in the application.

    CREATE FUNCTION set_updated_at() RETURNS trigger AS $
    BEGIN
      NEW.updated_at := now();
      RETURN NEW;
    END;
    $ LANGUAGE plpgsql;
    
    CREATE TRIGGER employees_set_updated_at
    BEFORE UPDATE ON employees
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

    What interviewers listen for

    • Function fired automatically on table events
    • BEFORE can modify NEW; AFTER suits auditing
    • Row-level vs statement-level
    • Good for timestamps, audit logs, invariants
    • Hidden logic and bulk-write overhead

    answer it out loud →
48. 48.What happens when you delete a parent row that child rows reference? Explain the ON DELETE options for foreign keys.easy

    By default the delete fails with a foreign-key violation, because the children would point at nothing. The ON DELETE clause changes that:

    • NO ACTION (the default): raise an error if referencing rows exist. The check happens at the end of the statement, or at commit if the constraint is DEFERRABLE and deferred.
    • RESTRICT: also an error, but the check can never be deferred.
    • CASCADE: delete the child rows too, and their children, recursively.
    • SET NULL or SET DEFAULT: keep the children but clear or reset their reference.

    ON UPDATE offers the same options for when a referenced key changes.

    Choose by meaning: CASCADE for rows that can't exist without the parent, like order_items of an order; RESTRICT or NO ACTION for important data you don't want to lose by accident, like orders of a customer; SET NULL for optional links. Cascades can delete a lot silently, and without an index on the child's foreign-key column, each parent delete scans the child table.

    What interviewers listen for

    • Default NO ACTION: delete fails if children exist
    • CASCADE deletes children recursively
    • SET NULL / SET DEFAULT keep children
    • RESTRICT is like NO ACTION but never deferred
    • Index the FK column for fast checks

    answer it out loud →
49. 49.What does VACUUM do in PostgreSQL? What is bloat, and how does autovacuum fit in?hard

    Because of MVCC, updates and deletes leave dead tuples behind. VACUUM finds versions no transaction can still see and marks their space reusable for new rows in the same table. It doesn't normally shrink the file, except for empty pages at the end. It also updates the visibility map, which makes index-only scans cheap, and freezes old transaction IDs to prevent wraparound. VACUUM ANALYZE also refreshes planner statistics.

    VACUUM FULL rewrites the whole table compactly and returns space to the OS, but holds an ACCESS EXCLUSIVE lock the whole time.

    Bloat is space occupied by dead or unused tuples, which makes tables and indexes larger and scans slower. Common causes: autovacuum not keeping up with a high-churn table, and anything that holds back the oldest visible snapshot, such as long-running or "idle in transaction" sessions and stale replication slots.

    Autovacuum runs this automatically per table, by default once dead tuples exceed 50 + 20% of the table. For big, busy tables, lower the per-table scale factor rather than turning it off.

    What interviewers listen for

    • Reclaims dead tuples for reuse, not usually to the OS
    • Updates the visibility map; freezes old XIDs
    • VACUUM FULL rewrites the table under an exclusive lock
    • Long transactions block cleanup and cause bloat
    • Tune autovacuum per table; don't disable it

    Likely follow-up: How would you find the session that is preventing vacuum from cleaning up?

    answer it out loud →
50. 50.PostgreSQL offers B-tree, Hash, GIN, GiST and BRIN indexes. When would you use each?hard

    • B-tree (the default): sorted and balanced. Handles =, <, >, BETWEEN, IN, IS NULL, sorting, and prefix LIKE 'abc%' given a suitable collation or operator class. It's the right answer most of the time.
    • Hash: equality only. It can be smaller than a B-tree for long values, but it can't do ranges, sorting or unique constraints. It's crash-safe since PostgreSQL 10; before that it wasn't WAL-logged.
    • GIN (generalized inverted index): maps each element to the rows that contain it. It's built for values that contain many items: JSONB containment (@>), arrays, full-text search, and trigram search with pg_trgm. Reads are fast; writes are more expensive.
    • GiST (generalized search tree): a framework for overlapping or multidimensional data, such as geometry and PostGIS, ranges, nearest-neighbor ordering, and exclusion constraints like "no overlapping bookings".
    • BRIN (block range index): stores only min/max summaries per range of table blocks. It's tiny, often kilobytes for a table of gigabytes, and works when the column's values follow the physical row order, such as timestamps in an append-only log table.

    What interviewers listen for

    • B-tree: default; equality, ranges, sorting
    • Hash: equality only
    • GIN: JSONB, arrays, full-text search
    • GiST: geometry, ranges, exclusion constraints, KNN
    • BRIN: tiny, for naturally ordered huge tables

    answer it out loud →
51. 51.What is a covering index and an index-only scan? What does INCLUDE add in PostgreSQL?hard

    A covering index contains every column a query needs, so the database can answer from the index alone, without visiting the table. In PostgreSQL that shows up as an Index Only Scan.

    INCLUDE (PostgreSQL 11+) adds non-key "payload" columns to a B-tree's leaf entries. They're returned by the scan but aren't part of the sort order and can't be searched on. That has two benefits over just adding them as extra key columns: the upper levels of the tree stay smaller, and for a UNIQUE index, uniqueness applies only to the key columns. CREATE UNIQUE INDEX ON users (email) INCLUDE (name) enforces unique emails and still covers SELECT name ... WHERE email = ?.

    The PostgreSQL-specific catch is MVCC: the index doesn't store visibility, so an index-only scan checks the visibility map. Pages not marked all-visible still need a heap fetch, which is shown as Heap Fetches in EXPLAIN ANALYZE. Tables that are vacuumed regularly get the full benefit.

    Don't include big or frequently updated columns: it bloats the index and adds write cost.

    CREATE INDEX orders_cust_incl
      ON orders (customer_id) INCLUDE (amount);
    
    EXPLAIN ANALYZE
    SELECT customer_id, amount FROM orders WHERE customer_id = 42;
    
    -- Index Only Scan using orders_cust_incl on orders
    --   Index Cond: (customer_id = 42)
    --   Heap Fetches: 0

    What interviewers listen for

    • Covering index answers the query without the table
    • Shows up as an Index Only Scan
    • INCLUDE adds non-key payload columns
    • Unique indexes enforce uniqueness on key columns only
    • Visibility map decides heap fetches

    answer it out loud →
52. 52.What are partial indexes and expression indexes in PostgreSQL? Give a use case for each.hard

    A partial index has a WHERE clause, so it only indexes the rows that match. If 99% of orders are completed and queries only look for pending ones, CREATE INDEX ... WHERE status = 'pending' is tiny and cheap to maintain. The planner uses it only when the query's WHERE clearly implies the index's predicate. A partial unique index is a great trick for soft deletes: emails must be unique only among rows that aren't deleted.

    An expression index indexes the result of an expression instead of a raw column. A query like WHERE lower(email) = ? can't use a plain index on email, but it can use an index on lower(email). The query must use the same expression, and the functions involved must be IMMUTABLE, so now() isn't allowed. Remember the extra parentheses: CREATE INDEX ON events ((payload ->> 'type')).

    Both let you index exactly what your queries ask for, instead of everything.

    -- Partial: only the rows queries care about
    CREATE INDEX orders_pending_idx
      ON orders (created_at) WHERE status = 'pending';
    
    -- Partial unique: unique among non-deleted users
    CREATE UNIQUE INDEX users_email_active
      ON users (email) WHERE deleted_at IS NULL;
    
    -- Expression: matches WHERE lower(email) = ...
    CREATE INDEX employees_lower_email_idx
      ON employees (lower(email));

    What interviewers listen for

    • Partial index covers only rows matching a WHERE
    • Query predicate must imply the index predicate
    • Partial unique index for soft deletes
    • Expression index must match the query expression
    • Expressions must use IMMUTABLE functions

    answer it out loud →
53. 53.In a PostgreSQL plan, what is the difference between a Seq Scan, an Index Scan, a Bitmap Heap Scan and an Index Only Scan?hard

    • Seq Scan reads the whole table in physical order. It's cheapest when a large share of rows match, and it can run in parallel.
    • Index Scan walks the index and, for each match, fetches the row from the heap. It returns rows in index order, which suits ORDER BY ... LIMIT, but each fetch can be a random read, so it's best for a few rows.
    • Bitmap Index Scan + Bitmap Heap Scan is the middle ground. It first collects all matching row locations into a bitmap, then reads heap pages in physical order, each page once. It can combine several indexes with BitmapAnd or BitmapOr. Recheck Cond appears because a bitmap can become lossy (page-level), so rows are rechecked.
    • Index Only Scan gets every needed column from the index and skips the heap for pages the visibility map marks all-visible.

    The planner chooses by estimated cost, using statistics and settings such as random_page_cost (4 by default, versus 1 for seq_page_cost). On SSDs, lowering random_page_cost makes index scans more attractive.

    What interviewers listen for

    • Seq Scan: whole table, good for many rows
    • Index Scan: ordered, random heap fetches, few rows
    • Bitmap: gather locations, read pages in order
    • Bitmaps combine indexes with BitmapAnd/BitmapOr
    • Index Only Scan skips the heap for all-visible pages

    answer it out loud →
54. 54.What is the difference between json and jsonb in PostgreSQL? Explain the main jsonb operators and how to index them.mid

    json stores the input text as-is, preserving whitespace, key order and duplicate keys, and re-parses it on every access. jsonb stores a decomposed binary form: it's slightly slower to write, drops duplicate keys (the last one wins) and doesn't keep key order, but it's much faster to query and it can be indexed. Use jsonb unless you must preserve the exact text.

    Key operators:

    • -> gets a field as jsonb; ->> gets it as text. #> and #>> do the same for a path like '{user,id}'.
    • @> means "contains": payload @> '{"type":"buy"}'.
    • ?, ?| and ?& test whether a key exists, any keys exist, or all keys exist.
    • || merges objects, - removes a key, and jsonb_set replaces a nested value.

    Indexing: a GIN index on the column speeds up @> and the key-existence operators. It does not help payload ->> 'type' = 'buy'; for that, create a B-tree expression index on (payload ->> 'type').

    SELECT payload -> 'user'           AS user_obj,  -- jsonb
           payload -> 'user' ->> 'plan' AS plan,      -- text
           payload #>> '{user,id}'      AS user_id    -- text
    FROM events
    WHERE payload @> '{"type": "buy"}';               -- containment
    
    CREATE INDEX events_payload_gin ON events USING gin (payload);
    CREATE INDEX events_type_idx ON events ((payload ->> 'type'));

    What interviewers listen for

    • json keeps text; jsonb is binary and indexable
    • -> returns jsonb, ->> returns text
    • @> containment, ? key existence
    • GIN index serves @> and ?
    • Expression B-tree index for ->> equality

    Likely follow-up: When would you move a JSON field into a real column?

    answer it out loud →
55. 55.What is the difference between SERIAL and GENERATED AS IDENTITY in PostgreSQL? Why do sequences have gaps?mid

    Both give you an auto-incrementing integer backed by a sequence.

    • SERIAL is an old PostgreSQL-specific shorthand: it creates an integer column, a sequence, and a DEFAULT nextval(...). The sequence is a separate object, and nothing stops manual inserts that later collide with it.
    • GENERATED ... AS IDENTITY (PostgreSQL 10+) is the SQL standard and the recommended choice. With GENERATED ALWAYS, inserting an explicit value is an error unless you write OVERRIDING SYSTEM VALUE; GENERATED BY DEFAULT allows explicit values. The sequence is tied to the column, which makes schema changes cleaner.

    Gaps are normal. nextval is never rolled back, so a rolled-back or failed insert burns a number, and sequences can also skip values after a crash. Never rely on ids being consecutive.

    A classic incident: rows inserted with explicit ids push the table ahead of the sequence, and later inserts fail with duplicate keys. The fix is setval to the current maximum id.

    CREATE TABLE orders (
      id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      note text
    );
    
    INSERT INTO orders (id, note) VALUES (10, 'x');
    -- ERROR: cannot insert a non-DEFAULT value into column "id"
    
    -- Resync a sequence that fell behind the data
    SELECT setval(pg_get_serial_sequence('orders', 'id'),
                  (SELECT max(id) FROM orders));

    What interviewers listen for

    • Both are backed by a sequence
    • SERIAL is PG shorthand; IDENTITY is SQL standard
    • GENERATED ALWAYS rejects explicit values
    • Sequences aren't rolled back, so gaps are normal
    • Out-of-sync sequence: fix with setval

    answer it out loud →
56. 56.What is Boyce–Codd Normal Form (BCNF), and how does it differ from 3NF?hard

    BCNF says: for every non-trivial functional dependency X → Y, X must be a superkey. 3NF is slightly weaker: it also allows X → Y when Y is a prime attribute, meaning part of some candidate key. The two only differ when a table has multiple overlapping candidate keys.

    Classic example: enrollment(student, course, instructor), where each instructor teaches exactly one course and a student takes a course with one instructor. The candidate keys are (student, course) and (student, instructor). The dependency instructor → course violates BCNF, because instructor isn't a superkey. It's allowed in 3NF, because course is part of a candidate key. The anomaly: you can't record that an instructor teaches a course until a student enrolls.

    Decomposing into (instructor, course) and (student, instructor) reaches BCNF losslessly, but the rule "one instructor per student per course" can no longer be enforced by a single key. That's the trade-off: BCNF decompositions aren't always dependency-preserving, while 3NF always can be.

    What interviewers listen for

    • Every determinant must be a superkey
    • 3NF also allows dependencies onto prime attributes
    • Differs only with overlapping candidate keys
    • BCNF may not preserve all dependencies

    answer it out loud →
57. 57.What is table partitioning in PostgreSQL, when does it help, and what are its limitations?hard

    Partitioning splits one logical table into smaller physical tables by a partition key. PostgreSQL has declarative partitioning with three strategies: RANGE (for example by month), LIST (by region or tenant) and HASH (to spread rows evenly).

    Where it helps:

    • Partition pruning: a query filtering on the key only touches the relevant partitions.
    • Data lifecycle: dropping or detaching an old month is instant and leaves no bloat, instead of a huge DELETE followed by VACUUM. This is often the main reason to partition time-series data.
    • Smaller per-partition indexes and more manageable vacuuming.

    Limitations:

    • Primary keys and unique constraints must include the partition key.
    • Queries that don't filter on the key scan every partition, and too many partitions increase planning overhead.
    • A row with no matching partition fails to insert, unless you create a DEFAULT partition.

    It's not a substitute for indexes; it pays off mainly for very large tables with a natural key like time.

    CREATE TABLE measurements (
      id          bigint GENERATED ALWAYS AS IDENTITY,
      recorded_at timestamptz NOT NULL,
      value       numeric,
      PRIMARY KEY (id, recorded_at)      -- must include the key
    ) PARTITION BY RANGE (recorded_at);
    
    CREATE TABLE measurements_2024_01 PARTITION OF measurements
      FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
    
    -- Retention: instant, no DELETE, no bloat
    ALTER TABLE measurements DETACH PARTITION measurements_2024_01;

    What interviewers listen for

    • RANGE, LIST and HASH partitioning
    • Pruning skips irrelevant partitions
    • Drop or detach old partitions instantly
    • PK/unique must include the partition key
    • Non-key queries scan all partitions

    Likely follow-up: How is partitioning different from sharding?

    answer it out loud →
58. 58.How does replication work in PostgreSQL, and what should you watch out for when sending reads to replicas?hard

    PostgreSQL has two main kinds:

    • Physical (streaming) replication: the primary streams its write-ahead log (WAL) to standbys, which replay it to maintain a byte-for-byte copy of the whole cluster. A hot standby accepts read-only queries and can be promoted if the primary fails.
    • Logical replication: publications and subscriptions replicate row changes for chosen tables. It works across major versions, so it's used for upgrades, partial copies and feeding other systems.

    By default replication is asynchronous: the primary commits without waiting, so replicas lag slightly, and a failover can lose the latest commits. Synchronous replication (synchronous_standby_names) waits for standby confirmation, at the cost of commit latency.

    Read-replica gotchas:

    • Replication lag breaks read-your-own-writes: a user saves a profile, reloads from a replica and sees old data. Route those reads to the primary.
    • Long queries on a standby can be cancelled by conflicts with cleanup replayed from the primary; hot_standby_feedback avoids that but can cause bloat on the primary.
    • A replica isn't a backup: a bad DELETE replicates instantly.

    What interviewers listen for

    • Physical streaming replicates WAL for the whole cluster
    • Logical replicates chosen tables, across versions
    • Async by default; sync trades latency for safety
    • Replica lag breaks read-your-writes
    • Replicas are not backups

    answer it out loud →
59. 59.Why do PostgreSQL deployments need connection pooling, and how does PgBouncer's transaction mode work?hard

    Each PostgreSQL connection is served by its own backend process, with its own memory. Connections are expensive to open and to keep: thousands of mostly idle connections from many app instances or serverless functions waste memory and add contention, and max_connections defaults to just 100.

    A pooler like PgBouncer sits between the app and the database, accepts many client connections, and multiplexes them over a small number of real server connections. Its modes:

    • Session: a client keeps a server connection until it disconnects. Safe, but saves little.
    • Transaction: a server connection is assigned only for the duration of a transaction, then returned to the pool. This is the usual choice and gives the biggest win.
    • Statement: like transaction mode, but multi-statement transactions aren't allowed.

    The catch with transaction mode is that session state doesn't survive between transactions: SET, LISTEN, session-level advisory locks, WITH HOLD cursors and SQL-level PREPARE break. Recent PgBouncer versions can track protocol-level prepared statements when max_prepared_statements is set. Application-side pools, such as HikariCP, complement a server-side pooler rather than replace it.

    What interviewers listen for

    • One backend process per connection; costly
    • Pooler multiplexes many clients over few connections
    • Session, transaction and statement modes
    • Transaction mode loses session state
    • SET, LISTEN, advisory locks, PREPARE break

    answer it out loud →

No questions match that filter.

Prefer multiple choice? All 21 SQL & PostgreSQL MCQs with answers →