Databases
229 bites tagged Databases — interview questions with model answers, and 60-second explainers.
Why synchronous DB libraries block async FastAPI endpoints and correct SQLAlchemy usage
This tests event loop blocking: sync DB calls in async def halt all requests. Answer: sync drivers block the loop despite releasing the GIL; use asyncpg with SQLAlchemy create_async_engine and AsyncSession. Red flag: recommending run_in_executor as default.
How do you use FastAPI dependency injection for database sessions?
Build a generator dependency that yields a session and closes it after; inject via Depends(get_db). FastAPI Depends() for session lifecycle and testability.
ODM: Your Database as JavaScript Objects
ODM translates JavaScript objects to database records and back, letting you work with plain objects instead of raw queries. It removes boilerplate in Node.js apps but hides the real queries underneath.
Compare PostgreSQL versus a columnar warehouse for raw event data
Contrast row vs column storage, compression, and scan speed; note Postgres suits OLTP and point lookups while columnar stores excel at aggregations. data orientation tradeoffs.
What is the difference between WHERE and HAVING in SQL?
Tests SQL execution order and aggregation. A strong answer states WHERE filters rows before grouping, HAVING filters groups after aggregation, and gives an aggregate example that WHERE cannot evaluate.
Find users who never placed an order and explain JOIN choice
This tests SQL anti-joins and NULL semantics. A strong answer uses LEFT JOIN with IS NULL or NOT EXISTS, explains why NOT IN is risky with NULLs, and why NOT EXISTS is preferred. Red flag: using INNER JOIN or ignoring NULLs.
Explain database indexes, the classic data structure, and write-heavy trade-offs
Tests the read-write trade-off of indexing. A strong answer names B-Trees, explains they avoid full scans, and notes that inserts, updates, and deletes must update the index, adding write amplification and storage cost. Red flag: claiming indexes are free.
Describe 1NF, 2NF, 3NF, normalization's purpose, and its performance trade-off.
1NF atomic values; 2NF no partial dependencies; 3NF no transitive dependencies; prevents update anomalies but adds join overhead. Linking forms to anomaly prevention and join overhead. Jargon without linking to anomalies.
What is the difference between DDL and DML in SQL?
DDL shapes schema with CREATE or ALTER; DML handles row-level data with SELECT, INSERT, UPDATE, or DELETE. Your grasp of the schema-versus-data boundary. Labeling SELECT as DDL or insisting DDL never affects data.
Explain ACID properties and why they matter for banking or e-commerce
Define each as a failure-handling guarantee; show how partial commits cause double-spending. Mapping ACID to real failure modes in finance. Vague definitions that skip Isolation levels or Durability details.
What is the difference between primary, foreign, and unique keys?
This tests relational integrity basics. Answer: primary keys identify rows, foreign keys reference tables, and unique keys are alternate candidates. Red flag: saying unique keys are just for indexing or omitting a non-PK example like email.
Relevance Ranking: Sorting Results by Likely Usefulness
Relevance ranking orders results by how well they satisfy query intent, not just keyword overlap. It powers ecommerce, documentation, and log search. The footgun is chasing click-through over task completion, which surfaces popular but wrong answers.
Stream-Table Duality: Two Views of One Dataset
A table is a snapshot; a stream is the changelog that built it. The same data can be viewed either way: tables answer what is true now, while streams capture every change that led there. Treating them as separate systems is the expensive footgun.
The N+1 Query Problem
N+1 means fetching one record, then looping to query its relations one by one. It explodes latency in ORM code that looks innocent, turning a page load into hundreds of round-trips. The fix is eager loading, yet developers often miss it until production melts.
Leaderless Replication: No Master, No Bottleneck
Leaderless replication lets any node accept writes, skipping a single leader bottleneck. Systems like Dynamo stay available during partitions, reconciling conflicts with vector clocks later.
Volcano Model: Pipelined Query Execution
Volcano makes every query operator a generator yielding one tuple per call. Scans, joins, and sorts stream data upward through open-next-close interfaces without materializing intermediates. The hidden cost is millions of virtual calls that stall modern CPUs.
Database Joins: Nested, Hash, Sort-Merge
A join matches rows by trading memory for speed. Nested loops use indexes; hash joins load large sets into RAM; sort-merge streams sorted data. The optimizer hides its choice, so a missing index can force a disk-spilling hash join.
SQL JOIN: Match Rows Across Tables
A SQL JOIN matches rows across tables on a shared key to build one logical record. You use it when orders need customer names or posts need authors. The footgun is that INNER JOIN silently drops rows with missing keys, making data seem to vanish.
ORM: The Virtual Object Database Layer
ORM converts data between relational databases and object-oriented program memory, creating a virtual object database inside your code. The footgun is designing object models that ignore the relational structure, forcing awkward translations you never see.
How SQL Queries Become Abstract Syntax Trees
An AST turns a flat SQL string into a tree of operations the database can reason about. The parser builds this tree before execution planning. Do not confuse it with the raw parse tree, which keeps punctuation and formatting the AST strips away.
Second Normal Form (2NF)
2NF ensures every non-prime attribute depends on the entire candidate key, not just part of it. It only matters when a relation has a composite key. The footgun is assuming single-attribute keys automatically satisfy 2NF.
DML: Insert, Update, and Delete
DML is the change-focused subset of database languages like SQL that adds, modifies, and removes data. It appears in every write to a table. The footgun is assuming SELECT belongs in DML; read-only querying is sometimes split out as DQL instead.
DDL: The Blueprint for Database Objects
DDL is the blueprint for your database. You reach for it when spinning up new tables, indexes, or user permissions, not when querying rows. The footgun is running DROP thinking you are deleting data, not vaporizing the entire table structure.
Architect a large-scale real-time recommendation system with data pipelines
Tests multi-stage ML serving under 200ms latency. Strong answers use a funnel: two-tower embeddings with ANN retrieval, ranking, and guardrails, plus separate batch and real-time pipelines. Red flag: scoring the full catalog per request without approximation.
Get Databases bites daily.
Five a day, five minutes, offline. With quizzes so it sticks.
Open testing — you’ll join as an early tester.