Databases

Prerequisites

Complete 101–104 and 116–121. Understand indexes, transactions, and API ownership checks.

Topics

• MongoDB aggregation, $lookup, explain(), transactions, replica sets, and write concerns;
• PostgreSQL window functions, CTEs, isolation anomalies, locking, and partitioning;
• migration and index rollout, backward compatibility, backup/restore, and recovery objectives;
• query budgets, N+1 detection, pagination, and integration-test isolation.

Investigation

Create a migration that adds an indexed task field without breaking an older API version. Test rollout order, duplicate data, rollback limits, query plans, backup restore, and authorization after migration.

Interview checkpoint

Explain when a transaction is necessary, why an index can hurt writes, and the difference between a backup that exists and a restore that has been verified.

Query patterns and concurrency lab

Top-N, deduplication, and correlated work

Use row_number() OVER (PARTITION BY owner_id ORDER BY created_at DESC, id DESC) for a deterministic top-N-per-group result. Use a CTE to name stages, not as a promise that PostgreSQL materializes them. Use DISTINCT ON when PostgreSQL-specific syntax is acceptable and the ORDER BY explicitly chooses the winner. A correlated subquery or LATERAL can be excellent with a selective index, but can also repeat work for every outer row. Compare alternatives with actual plans.

WITH newest AS (
  SELECT t.*, row_number() OVER (
    PARTITION BY owner_id ORDER BY created_at DESC NULLS LAST, id DESC
  ) AS rn
  FROM tasks AS t
)
SELECT id, owner_id, title
FROM newest
WHERE rn <= 3;

Test equal timestamps, NULL timestamps, empty groups, duplicates, and concurrent inserts. Add a uniqueness rule only after a duplicate-cleanup migration has a deterministic winner and a rollback plan.

Isolation is executable behavior

In two disposable psql sessions, run a pair of reads around a committed update under READ COMMITTED to observe a non-repeatable read. Repeat under REPEATABLE READ; then create a write-skew scenario where each transaction sees the other doctor's schedule as available and inserts a conflicting booking. Under SERIALIZABLE, one transaction should receive SQLSTATE 40001 rather than silently committing an unsafe history.

Retry the entire transaction with a cap and jitter for 40001 and deadlock SQLSTATE 40P01. Do not retry an email, payment, or non-idempotent HTTP call merely because the database transaction was retried. Assert atomic rollback, version-conflict row counts, lock timeout behavior, and that authorization predicates remain present in every write.

Interview questions

1. Why does a window function not replace GROUP BY? It preserves row identity; grouping collapses rows.
2. Why is a CTE not automatically faster? It expresses structure, while the planner still chooses execution and may inline or materialize it.
3. What is the safe response to SQLSTATE 40001? Roll back, rebuild all transaction inputs, retry within a bounded policy, and keep external effects idempotent.
4. What does an EXPLAIN ANALYZE result prove? Performance for that data, plan, cache state, and environment, not every production workload.

References

• MongoDB: Aggregation
• MongoDB: Explain
• PostgreSQL: Concurrency control