293: Database Interview Review: SQL, PostgreSQL, MongoDB, Modeling, and Concurrency

Learning outcomes

By the end of this lesson, you can:

explain and apply sql modeling in a realistic implementation;
explain and apply postgresql performance in a realistic implementation;
explain and apply transactions and isolation in a realistic implementation;
explain and apply mongodb modeling in a realistic implementation;
explain and apply store selection in a realistic implementation.

These outcomes are intentionally practical. In an interview, it is not enough to name normalization, an index type, or an isolation level. You should be able to connect the choice to a requirement, implement a small part of it, and explain what evidence would tell you that the design is working.

Prerequisites and retrieval

This lesson assumes the earlier 01–06 foundation and the preceding lessons in this module. Before reading, retrieve one concrete example from a previous project where one of these concerns appeared. It might be a duplicate record, a slow query, a retry that changed data twice, or a decision to keep some data in a document store. The point is not to memorize terminology in isolation. Use the example to practice making a defensible decision in a realistic full-stack interview loop, where your explanation, trade-offs, debugging approach, code, and project evidence need to agree.

Terminology

SQL modeling: Review keys, constraints, normalization and denormalization, NULL semantics, joins, aggregates, CTEs, window functions, views, database functions, and secure parameterized writes. The question is how the schema represents valid state and prevents invalid state.
PostgreSQL performance: Explain B-tree, composite, partial, expression, and specialized indexes; EXPLAIN ANALYZE; cardinality estimates; scan and join choices; connection pools; vacuum; and partitioning at a high level. A good performance explanation starts with an observed query or workload, not with an index chosen by habit.
Transactions and isolation: Be able to reproduce a lost update and explain MVCC, read committed, repeatable read, serializable isolation, row locks, optimistic version checks, deadlocks, and transaction scope. These concepts describe what concurrent operations can observe and which changes are allowed to win.
MongoDB modeling: Review embedding and referencing, indexes, aggregation, transactions, replica sets, sharding and shard keys, read and write concern, and when document aggregates are a better fit. The access pattern and consistency boundary should drive the document shape.
Store selection: Choose SQL, document, key-value, search, or specialized storage from invariants and access patterns rather than generic scaling slogans. “It scales” is not a sufficient reason to choose a store.
Operational concerns: Backups and restores, replication lag, migration safety, least privilege, tenant isolation, monitoring, and data retention distinguish production knowledge from query practice. A design is incomplete if it only works while the database is healthy and the schema never changes.

Mental model

Treat Database Interview Review: SQL, PostgreSQL, MongoDB, Modeling, and Concurrency as a design problem with observable inputs, outputs, invariants, and failure modes. Database interviews are decision interviews. You may need to explain relational and document modeling, constraints and indexes, transactions, query plans, isolation, and the point at which a datastore choice should change because the access patterns changed. A strong implementation makes its assumptions visible, narrows uncertainty at system boundaries, and leaves evidence such as tests, types, constraints, metrics, or diagrams that can demonstrate why the design is safe.

A useful sequence for both an interview answer and a production change is:

requirement -> constraints -> model -> implementation -> failure analysis -> verification

Start with the requirement and its invariants. Then decide which layer should enforce each invariant. Only after that should you choose a library call, query, index, transaction strategy, or datastore. Jumping directly from a requirement to a tool often produces a plausible happy path while leaving duplicates, retries, concurrent writes, and recovery undefined.

Deep dive

1. SQL modeling

Review keys, constraints, normalization and denormalization, NULL semantics, joins, aggregates, CTEs, window functions, views, database functions, and secure parameterized writes. These are not separate vocabulary items: together they determine how the database represents relationships, rejects invalid states, answers questions, and protects writes from unsafe input.

The useful decision rule is to use SQL modeling deliberately when it makes the contract or invariant easier to prove. A unique constraint, foreign key, or NOT NULL constraint can enforce a rule for every caller, not just for the current application code. Normalization can make updates consistent, while denormalization can make a known read pattern cheaper at the cost of additional write and consistency work. If a modeling choice only reduces typing while hiding an assumption, prefer the more explicit design.

2. PostgreSQL performance

Explain B-tree, composite, partial, expression, and specialized indexes; EXPLAIN ANALYZE; cardinality estimates; scan and join choices; connection pools; vacuum; and partitioning at a high level. The first performance question is usually not “Which index should I add?” It is “What does the query actually do, and what does the workload require?”

Use the execution plan to compare estimates with actual rows and to identify where time, I/O, or memory is being spent. An index can help one access pattern and add write cost or storage cost to another. Connection pools also have limits: too many active database connections can create contention rather than throughput. Vacuum and statistics affect the planner's view of the data, and partitioning is useful only when the partition key and queries allow PostgreSQL to avoid irrelevant partitions. Use PostgreSQL performance deliberately when it makes the contract or invariant easier to prove; measure before changing it, and do not substitute a scalable-sounding mechanism for evidence.

3. Transactions and isolation

Be able to reproduce a lost update and explain MVCC, read committed, repeatable read, serializable isolation, row locks, optimistic version checks, deadlocks, and transaction scope. The central problem is that two individually reasonable operations can be unsafe when their reads and writes overlap.

For example, two requests can both read a balance of 10, subtract 3, and then both write 7. The final value is 7 even though two withdrawals occurred. A transaction alone does not automatically express the intended protection; the isolation level, lock, atomic update, or version predicate must match the invariant. Keep transactions focused and short enough to avoid holding locks across unrelated work. Also be prepared to explain what happens when a transaction is aborted, a deadlock is detected, or a serialization failure requires a retry. Use transactions and isolation deliberately when they make the invariant easier to prove, and state the consistency and retry expectations rather than naming an isolation level without context.

4. MongoDB modeling

Review embedding and referencing, indexes, aggregation, transactions, replica sets, sharding and shard keys, read and write concern, and when document aggregates are a better fit. Embedding is often useful when related data is read and updated as one document aggregate and has a bounded size. Referencing is more appropriate when the related data grows independently, is shared by many parents, or has a different lifecycle.

Indexes and aggregation still need to be evaluated against actual access patterns. Transactions are available for operations that need them, but choosing MongoDB does not remove consistency or operational decisions. Replica sets, read and write concern, replication lag, shard-key selection, and chunk distribution affect what a caller can observe and how the system behaves under failure. Use MongoDB modeling deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design.

5. Store selection

Choose SQL, document, key-value, search, or specialized storage from invariants and access patterns rather than from generic scaling slogans. SQL is a strong default when relationships, constraints, joins, and multi-record invariants are central. A document store can fit an aggregate that is usually read and written together. Key-value storage can fit direct lookups with simple access patterns, while search storage is optimized for retrieval and ranking rather than being the authoritative source for every business invariant.

Those are starting points, not automatic answers. Consider query shape, update frequency, consistency needs, operational burden, recovery, and the cost of keeping derived stores synchronized. Use store selection deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design.

6. Operational concerns

Backups and restores, replication lag, migration safety, least privilege, tenant isolation, monitoring, and data retention distinguish production knowledge from query practice. Ask how a backup is restored and verified, not only whether backups exist. Ask what a read from a lagging replica means, how a migration behaves while old and new application versions overlap, and how access is limited to the data a service or tenant should see.

Monitoring should make important behavior observable: query latency, errors, connection usage, replication lag, lock contention, and storage growth are more useful than a vague claim that the database is healthy. Retention and deletion requirements belong in the design as well. Use operational concerns deliberately when they make the contract or invariant easier to prove; if they only reduce typing while hiding an assumption, prefer the more explicit design.

Worked example

Consider a realistic full-stack interview loop in which explanations, trade-offs, debugging, coding, and project evidence must agree. Start by writing the requirement in one sentence. Then list the input and output contracts and identify which concept above owns each failure mode. The important separation is by responsibility: parsing or validation belongs at the boundary; domain rules belong in the domain or service layer; persistence rules belong in the database or repository; and presentation rules belong in the client. Mixing these concerns can make a happy-path demo shorter, but it makes edge cases much harder to reason about and test.

Use this flow while you work:

Prompt -> clarify -> state assumptions -> solve -> test edge cases -> explain trade-offs

Walk through at least four cases: the normal path; an empty or missing value; a duplicate, retry, or concurrent path where relevant; and a dependency failure. For each case, state which layer detects the problem, whether the operation is safe to retry, and what the caller observes. For a database example, include the relevant constraint or transaction boundary rather than relying on application code alone. This is the level of explanation expected in a senior code review or technical interview.

Production perspective

Production correctness is broader than “the code works on my machine.” Ask how the design behaves during deploys, retries, partial failure, stale clients, concurrent requests, malformed data, schema changes, and high-cardinality workloads. Prefer explicit contracts, bounded resource usage, structured errors, and measurable behavior. Optimize only after you can identify the bottleneck or risk with evidence.

When the topic involves an external dependency, define a timeout and cancellation strategy. When it involves persistence, define transaction and consistency expectations. When it involves user-visible state, define loading, empty, error, stale, and success states. When it involves security, assume the client can be modified and all network input is untrusted. These are not separate concerns from database design: retries can create duplicates, stale reads can affect user-visible decisions, and privilege mistakes can expose data even when every query is syntactically correct.

Guided lab

Answer a mock “design persistence for ecommerce” round. Schema both relational and document alternatives, write five SQL queries including a window query, tune one execution plan, explain transaction isolation, and then justify which data belongs in MongoDB, search, or a cache, if any. For each choice, state the invariant, the main access pattern, and the operational cost. The goal is not to claim that one datastore is universally best; it is to make the boundary and trade-off explicit.

Complete the lab with this discipline:

Write the requirement and two non-requirements.
List input, output, and error contracts before implementation.
Implement the smallest correct vertical slice.
Add at least one invalid-input test and one edge-case test.
Instrument or inspect the behavior instead of guessing.
Refactor one hidden assumption into an explicit type, constraint, function, or configuration.
Explain one alternative design and why you did not choose it.
Record a short “what would break at 10× scale?” note.

When tuning the plan, record the query, representative data shape, plan before the change, plan after the change, and the measurement that justifies the change. When discussing MongoDB, search, or cache, say whether the store is authoritative or derived and how it is rebuilt or invalidated. That reasoning is part of the exercise, not extra polish.

Edge cases and failure modes

SQL modeling: test absence, malformed input, duplicates, ordering and concurrency where applicable, and behavior at the smallest and largest credible sizes. Pay particular attention to NULL behavior, missing relationships, and whether a constraint rejects the invalid state.
PostgreSQL performance: test absence, malformed input, duplicates, ordering and concurrency where applicable, and behavior at the smallest and largest credible sizes. Compare estimated and actual rows and check whether the plan changes with realistic data distribution.
Transactions and isolation: test absence, malformed input, duplicates, ordering and concurrency where applicable, and behavior at the smallest and largest credible sizes. Reproduce a lost update, then verify the chosen lock, atomic statement, version check, or isolation level prevents it or reports a retryable failure.
MongoDB modeling: test absence, malformed input, duplicates, ordering and concurrency where applicable, and behavior at the smallest and largest credible sizes. Check document growth, index coverage, aggregate boundaries, and the behavior of reads during replication lag.
Store selection: test absence, malformed input, duplicates, ordering and concurrency where applicable, and behavior at the smallest and largest credible sizes. Include a failure or rebuild scenario for any cache, search index, or derived representation.

Common mistakes and debugging

Solving the example instead of the requirement: a copied pattern can be syntactically correct but architecturally wrong.
Hiding uncertainty with assertions, broad exception handlers, permissive schemas, or “temporary” any values.
Testing only the happy path and therefore discovering contracts only after integration.
Optimizing before measuring, or selecting a scalable mechanism without a scale requirement.
Letting client-side behavior stand in for server-side authorization, validation, or persistence guarantees.

For debugging, reproduce the smallest failing case first. Inspect the actual value, query parameters, execution plan, transaction log, or relevant metric rather than inferring from the intended code. Trace the boundary where the invariant first becomes false: source or build, browser or DOM, Network or HTTP, server or route, database or query, or deployment or configuration. Then fix the layer that owns the rule instead of adding a downstream patch. A slow query needs its plan and data distribution inspected; a concurrency bug needs interleaving and transaction behavior inspected; a missing record needs the write path, commit result, and read consistency inspected.

Interview questions

What problem does SQL modeling solve, and what trade-off or failure mode would make you choose a different approach?
What problem does PostgreSQL performance solve, and what trade-off or failure mode would make you choose a different approach?
What problem does Transactions and isolation solve, and what trade-off or failure mode would make you choose a different approach?
What problem does MongoDB modeling solve, and what trade-off or failure mode would make you choose a different approach?
What problem does Store selection solve, and what trade-off or failure mode would make you choose a different approach?

Answer each question with a concrete invariant or access pattern, one implementation mechanism, and one failure mode. A strong answer also says what you would measure or inspect when the first design did not behave as expected.

Checkpoint

Without notes, explain Database Interview Review: SQL, PostgreSQL, MongoDB, Modeling, and Concurrency to another developer in five minutes. Your explanation must include one invariant, one edge case, one production failure mode, and one alternative design. Then implement a small example without copying the lesson code. Be prepared to explain where validation occurs, which layer enforces the invariant, and what evidence would convince you that the implementation is correct.

Mastery checklist

I can define the core terms precisely.
I can choose a design from requirements instead of from habit.
I can implement and test the normal path and edge cases.
I can explain the runtime, storage, or complexity cost.
I can identify which layer owns validation, errors, and recovery.
I can compare at least two reasonable alternatives.
I can explain how the design changes at larger scale or stricter reliability.