Databases
229 bites tagged Databases — interview questions with model answers, and 60-second explainers.
How the write-ahead log ensures atomicity and durability
Log changes before applying, flush log at commit, replay redo and undo on recovery. WAL mechanics behind durability and atomicity. thinking commit must flush all data pages, or confusing the WAL with a backup.
Write skew under snapshot isolation
Two transactions read an overlapping set, each writes disjoint rows, jointly violating an invariant. subtle anomaly snapshot isolation misses. thinking snapshot isolation equals serializable or that row locks alone fix it.
Two-Phase Locking and serializability
A growing phase only acquires locks, a shrinking phase only releases, no lock taken after one is freed. how locking enforces serial-equivalent schedules. confusing 2PL with the two-phase commit protocol.
How MVCC enables non-blocking reads
Writers create new row versions instead of overwriting, readers see a consistent snapshot, so readers never block writers. grasp of versioned rows and snapshots. claiming MVCC eliminates all locking including write conflicts.
Database deadlocks and how engines resolve them
Define a deadlock as mutual waiting on locks, name detection plus victim rollback, and prevention by consistent lock ordering. understanding circular lock waits. confusing a deadlock with a slow query or simple lock wait.
The lost update anomaly explained
Both transactions read the same value, each adds one, the second overwrite erases the first. read-modify-write race awareness. thinking the database auto-serializes plain reads, or that the final value is always correct.
The ACID properties of transactions
Define Atomicity, Consistency, Isolation, Durability and why each matters. foundational transaction guarantees. confusing Consistency with Isolation or thinking durability means in-memory only.
Shard key impact on uniqueness and cross-shard lookups
Uniqueness and FKs hold only within a shard; non-shard-key lookups need scatter-gather or a secondary index. understanding constraints break across shards. assuming a global unique index just works across shards.
Relational versus wide-column for a news feed
Relational gives flexible joins but read-time fan-out; Cassandra precomputes per-user feed rows for fast writes-side fan-out. matching data model to feed access patterns. choosing a store without naming the read pattern.
Polymorphic associations and referential integrity
A single column can't FK two tables, so integrity is unenforced; alternatives use exclusive arcs or per-type tables. knowing why polymorphic columns break foreign keys. claiming a normal FK can target multiple tables.
3NF versus BCNF and the overlapping-key gap
BCNF requires every determinant be a superkey; 3NF allows exceptions for prime attributes. precise grasp of functional dependencies and candidate keys. claiming 3NF and BCNF are always equivalent.
Adjacency List versus Nested Set for hierarchies
Adjacency list is simple writes but recursive reads; nested set is fast subtree reads but costly writes. read-versus-write trade-offs in tree storage. ignoring recursive CTEs or the wide updates nested sets need.
Modeling one-to-many versus many-to-many relationships
One-to-many uses a foreign key on the many side; many-to-many needs a junction table with two foreign keys. cardinality and junction-table modeling. storing comma-separated IDs instead of a junction table.
Normalizing a flat orders table to 3NF
Split repeating data, remove partial dependencies, remove transitive dependencies, define keys. applying 1NF, 2NF, 3NF rules concretely. jumping to tables without naming which dependency each step removes.
When to intentionally denormalize a schema
Identify read-heavy join cost, duplicate or precompute data, and own the consistency burden. trading read speed for write complexity deliberately. denormalizing prematurely or ignoring how duplicates drift.
Diagnosing and fixing the N+1 query problem
Define the 1 parent plus N child queries, fix via JOIN or batched IN, and ORM eager loading. spotting hidden per-row queries from lazy loading. solving it only with caching while ignoring round-trip count.
Read Committed versus Serializable isolation levels
Name the four levels, map each anomaly (dirty read, non-repeatable read, phantom) to the level that blocks it. grasp of concurrency anomalies versus consistency cost. claiming Serializable blocks only phantoms.
Materialization and Pipelining
Two query-execution strategies: materialization writes each operator's full output to disk before the next reads it, while pipelining streams tuples operator-to-operator without intermediate storage.
Running stateful apps with StatefulSets
Stateful apps need stable identity and storage; a StatefulSet gives stable names, ordered rollout, and per-Pod volumes. stateful workloads in Kubernetes. claiming a Deployment plus PVC solves it.
RDS Multi-AZ versus Read Replicas
Multi-AZ is a synchronous standby for failover, replicas are async for read scaling, combine both when needed. availability versus scalability. claiming the standby serves reads or that replicas auto-failover.
How does caching reduce database load?
Cache-aside reads, RAM-speed lookups, TTL plus invalidation. caching as a read-offload layer. treating the cache as durable source of truth or ignoring stale-data and invalidation.
Read replicas in managed relational databases
A read replica is an async copy of the primary that serves read-only queries, offloading the primary and scaling read-heavy workloads; expect replication lag. scaling reads and replication basics.
Choosing relational vs NoSQL managed databases
Choose relational for complex relationships, joins, flexible queries, and strong transactions; choose NoSQL for known access patterns needing massive horizontal scale. data model and access pattern fit.
Design DB transaction middleware and identify the background-task pitfall
Tests request-scoped DB lifecycle awareness. Strong answer: middleware closes the session on response, yet BackgroundTasks run afterward, so sharing that session causes crashes or leaks. Red flag: saying background tasks can reuse the request transaction.
Get Databases bites daily.
Five a day, five minutes, offline. With quizzes so it sticks.
Open testing — you’ll join as an early tester.