Interview Questions: Transactions, Performance and Design

Senior questions are rarely about syntax. They test whether you can reason about trade-offs, name the failure mode before it happens and tell a symptom from its cause.

The strongest answers share a shape: state the principle, show the failure it prevents, then admit its cost. Nothing in database design is free, and interviewers notice when you know the price.

1. What does ACID mean, and how does PostgreSQL provide it?#

Short answer. Atomicity (all or nothing), Consistency (constraints always hold), Isolation (concurrent transactions do not interfere beyond what the isolation level allows), Durability (committed data survives crashes).

How. Atomicity and isolation come from MVCC: updates write new row versions, so a rollback just ignores them and readers see consistent snapshots. Durability comes from the write-ahead log, flushed to disk before COMMIT returns. Consistency comes from the constraints you declared.

Caveat worth adding. The database guarantees only the rules it knows about. A transfer procedure that forgets the second UPDATE will still commit.

2. Explain the isolation levels and the anomalies they prevent.#

Short answer. Anomalies are dirty reads, non-repeatable reads, phantoms, lost updates and write skew. The standard defines four levels. PostgreSQL implements three behaviors: Read Committed (default; each statement sees data committed when it began), Repeatable Read (one snapshot for the whole transaction), and Serializable (as if transactions ran one at a time).

Detail. PostgreSQL's Read Uncommitted behaves as Read Committed, so dirty reads never happen. Its Repeatable Read also prevents phantoms. At the higher levels, expect serialization failures and retry. Say that clearly, because candidates who skip the retry requirement have not used these levels in production.

3. What is MVCC, and what are its consequences?#

Short answer. Multi-version concurrency control keeps several versions of a row, tagged with transaction IDs, so readers never block writers and writers never block readers.

Consequences. Every update leaves a dead version. Vacuum reclaims them, but only versions no running transaction could still need, so one long-running transaction can cause table bloat. This is also why COUNT(*) on a large table is slow: visibility must be checked row by row.

4. What is a lost update, and how do you prevent it?#

Short answer. Two transactions read a value, each computes a new one, both write, and one overwrites the other.

Prevention, in order of preference.

1. Make the database do the arithmetic: UPDATE products SET stock_qty = stock_qty - 1 WHERE product_id = 9 AND stock_qty > 0. A single statement is atomic.
2. SELECT ... FOR UPDATE to lock the row while you work.
3. Optimistic locking: a version column and UPDATE ... WHERE version = ?, retrying when zero rows change.
4. A higher isolation level, accepting retries.

5. How do deadlocks happen, and how do you avoid them?#

Short answer. Two transactions each hold a lock the other needs. PostgreSQL detects the cycle after deadlock_timeout and aborts one.

Avoid. Take locks in a consistent order (always update the lower account ID first), keep transactions short, and retry on deadlock detected. Mention pg_locks and pg_blocking_pids for diagnosis.

6. Pessimistic versus optimistic locking?#

Short answer. Pessimistic locking locks the row up front (SELECT ... FOR UPDATE) and suits high-contention, short critical sections. Optimistic locking takes no lock, checks for conflict at write time with a version column, and retries. It suits low contention and long user "think times", such as web forms.

7. Why is my index not being used?#

Short answer. Walk the usual causes, from the most common.

• A function on the column (WHERE EXTRACT(YEAR FROM d) = 2024). Rewrite as a range or build an expression index.
• A leading wildcard in LIKE.
• A type mismatch forcing a cast.
• Low selectivity, so a sequential scan is cheaper.
• Stale statistics, so the planner misjudges row counts. Run ANALYZE.
• A composite index used without its leftmost column.
• A very small table.

Show process. Say you would run EXPLAIN (ANALYZE, BUFFERS) and compare estimated and actual rows first. That signals method over guesswork.

8. How does column order in a composite index matter?#

Short answer. The index is sorted by the first column, then the second within it, so it supports lookups on a leftmost prefix. An index on (customer_id, order_date) serves filters on customer_id and on both, but not on order_date alone.

Rule of thumb. Put equality columns first, then the range or sort column. Mention covering indexes (INCLUDE) that allow index-only scans.

9. How do you read an EXPLAIN ANALYZE plan?#

Short answer. It is a tree: read from the innermost node outward. Compare estimated rows to actual rows, since a large gap points to bad statistics. Check loops, because per-loop time is multiplied. Look at Rows Removed by Filter for wasted work, and note the scan and join types.

Add. EXPLAIN ANALYZE executes the statement, so wrap UPDATE and DELETE in a transaction you roll back.

10. What join algorithms does the planner use?#

Short answer. Nested loop (for each outer row, look up inner rows, best with an index and few outer rows), hash join (build a hash table of the smaller side, probe with the larger, the usual choice for large unsorted inputs) and merge join (walk two sorted inputs together).

Tie to practice. A nested loop over two large tables with no index is a classic cause of slow queries.

11. When would you partition a table, and when not?#

Short answer. When a table is very large and has a natural key, usually time, that queries filter on. The benefits are partition pruning, much cheaper retention (drop a partition instead of deleting millions of rows) and easier maintenance.

Cautions. It does not speed up arbitrary queries. The partition key must be part of unique constraints, too many partitions slow planning, and queries without the key scan every partition. Partitioning is different from sharding, which spreads data across servers, and from replication, which copies it.

12. Normalize or denormalize?#

Short answer. Normalize by default, because each fact lives in one place and cannot contradict itself. Denormalize with evidence, for a measured read bottleneck or for reporting.

Good example. Storing the price paid on an order line is not denormalization. It is history. If you duplicate data for speed, name the mechanism that keeps the copies in sync (a trigger, a materialized view, one transaction), or do not duplicate it.

13. What is SQL injection, and how do you prevent it?#

Short answer. Untrusted input becomes part of the SQL text, so an attacker's characters change the query's structure. Prevent it with parameterized queries, which send the statement and the values separately.

Fuller answer. Parameters cannot replace identifiers or keywords, so use an allowlist for dynamic sort columns and format('%I') for identifiers in PL/pgSQL. Add least privilege so a successful injection can do little, never expose raw database errors, and remember second-order injection from stored data. Escaping by hand is not a defense.

14. A query that used to be fast is now slow. What do you do?#

Short answer. Measure before changing anything. This is a favorite behavioral-technical question, and the process is the answer.

1. Confirm it is really slower and find when it changed. Did the data grow, a deploy go out, an index get dropped?
2. Capture the plan with EXPLAIN (ANALYZE, BUFFERS) and compare with a known good one if you have it.
3. Check estimates versus actuals. Run ANALYZE if they are off.
4. Look for the usual suspects: lost index, query change, bloat from long transactions, lock waits (pg_stat_activity), a different parameter value choosing a different plan.
5. Change one thing, re-measure, and verify you did not slow the writes.
6. Add a monitor so you find out sooner next time (pg_stat_statements, slow query log).

Practice#

1. Pick three questions and draw the timeline for each (lost update, deadlock, write skew) on paper, with two sessions and the order of statements.
2. For question 7, build big_orders from Module 6 and demonstrate three different reasons an index goes unused.
3. Answer question 14 aloud for a query you have actually met. Note which step you would have skipped before this course.
4. Explain question 11 to someone who has never heard of partitioning, using a filing cabinet drawn by year.