Full Stack Developer DBMS Concepts Interview Guide

At Read Committed, each statement receives a new snapshot, so later statements can see commits made in between. Higher isolation changes the guarantees and may require retrying serialization failures. Choose isolation from the business invariant, not from an assumption that every transaction freezes all reads.

Check your understanding: Interleave two sessions and document the snapshots each statement sees.

Common trap: Treating transaction atomicity and isolation as identical.

Concept reference: PostgreSQL: transaction isolation

An index is an option, not a command. The planner considers selectivity, statistics, table size and access cost. Read estimated and actual rows, filters and scan types before changing indexes. EXPLAIN ANALYZE executes the statement, so use safe data and transaction handling for writes.

Check your understanding: Compare estimated and actual row counts on representative data.

Common trap: Forcing an index without measuring the full query.

Concept reference: PostgreSQL: reading EXPLAIN plans

Check the grain of each table and whether the join creates several rows for one order. Aggregate at the intended business grain before joining, or use the correct relationship and key. Reconcile totals using a tiny fixture containing multiple child rows and an unmatched parent.

Check your understanding: Use one order with two child records and verify the total remains correct.

Common trap: Using DISTINCT as a universal repair for a wrong join.

Concept reference: PostgreSQL: reading EXPLAIN plans

Keep a stable client operation identity and let the server reconcile duplicates with the durable outcome. Show pending, failed and saved states separately; do not discard the draft on an ambiguous timeout. After reconnecting, fetch the authoritative result instead of blindly applying the same side effect again.

Check your understanding: Lose the response after persistence and retry from the same client operation.

Common trap: Assuming a network error means the database write did not happen.

Concept reference: IETF RFC 9110: HTTP semantics

Validate external payloads, size limits and allowed values, and enforce authorization for the target resource. Client validation improves usability but is bypassable. Keep the API contract explicit so validation failures can be shown accurately without leaking private fields or implementation details.

Check your understanding: Send a handcrafted request with another account’s resource ID.

Common trap: Treating a disabled submit button as a security boundary.

Concept reference: OWASP: authorization checks

Test the shared boundary with representative success, empty, failure and delayed responses. Verify the actual UI workflow as well as the response shape. Version or coordinate breaking changes, and retain compatibility during a rollout where old and new clients can coexist.

Check your understanding: Run the current client against the proposed response contract before deployment.

Common trap: Testing frontend and backend only in isolation.

Concept reference: Playwright: reliable browser tests

First, identify technical constraints and define measurable service objectives. Next, follow a request across validation, business logic, storage and asynchronous work. Contrast architectural trade-offs across write cost, read latency, consistency and operational complexity, explicitly mitigate the risk of a partially deployed reader cannot understand the new representation, and confirm system stability using a compatibility contract, expand-and-contract rollout and rollback rehearsal.

Common trap: Reaching for a specific library or framework before defining constraints, failure envelopes, and automated verification criteria.

Prioritise the failure mode exhibiting the highest user blast radius and lowest observability. Formulate an explicit containment boundary, implement idempotent retries with jitter, and establish an automated rollback threshold. Verify resilience through a compatibility test matrix and a staged rollout metric.

Common trap: Relying on passive monitoring dashboards without defining explicit error-budget alerts, rollback triggers, and verified recovery procedures.

Structure the response using Context-Decision-Tradeoff-Result: articulate the business and technical constraints, compare viable alternatives, explain the implementation (follow a request across validation, business logic, storage and asynchronous work), and document the accepted trade-off. Provide concrete proof: an explain plan, concurrency test and recovery verification.

Common trap: Speaking only in high-level abstractions or team accomplishments without detailing your direct implementation decisions, trade-offs, and measured results.

Formulate a falsifiable hypothesis from observable telemetry before altering configurations. Then correlate the request trace with transaction state, queue ownership and dependency budgets. Isolate the defect to the smallest reproducible boundary, validate root cause with evidence, and confirm full resolution using a heap profile, bounded reproduction and post-fix soak-test result.

Common trap: Applying speculative fixes or restarting services blindly without establishing an observable signal connected to a falsifiable hypothesis.

Produce an idempotency test, failure trace and repeatable recovery runbook. Document baseline assumptions, technical mechanism (follow a request across validation, business logic, storage and asynchronous work), rejected alternatives, bounded failure envelopes, and deterministic pass criteria. Supply reproducible verification via a server-authoritative timestamp trace and boundary property tests.

Common trap: Presenting architecture diagrams or slides lacking automated unit/integration tests, observable metrics, or automated rollback configurations.