Everything in Databases & Architecture, page 14
Snapshot Isolation: A 'Photo' of Your Database
Snapshot Isolation gives a transaction a private 'photo' of the database from when it started, ensuring consistent reads. It's used in high-concurrency systems to prevent readers from blocking writers. The footgun is that it doesn't prevent all anomalies.
Timestamp Concurrency Control: No Locks, Just Time
Timestamp-based concurrency control bets that transaction conflicts are rare, using timestamps to order operations instead of locking data. It's used where lock overhead is high, but the footgun is that frequent conflicts can cause transaction starvation.
Strict Two-Phase Locking (S2PL): Safety Over Speed
Strict Two-Phase Locking (S2PL) forces a transaction to hold all its locks until it fully commits or aborts. This prevents cascading aborts in databases but at the cost of concurrency, as other transactions are blocked for longer periods.
MVCC: Read and Write Data Without Blocking Each Other
MVCC avoids slow, traditional locks by giving each transaction its own consistent data snapshot. This allows readers and writers to work at the same time without blocking each other, boosting performance in databases like PostgreSQL.
Database Deadlock: The Two-Way Standoff
A deadlock is a 'Mexican standoff' where two transactions can't finish because each is waiting for a resource the other has locked. This happens in systems with concurrent writes. The database will kill one transaction, forcing your app to handle the retry.
Two-Phase Locking (2PL): Preventing Database Race Conditions
2PL is a database's pessimistic strategy for safe concurrency. A transaction acquires all necessary locks before releasing any, ensuring operations don't clash. It's used to guarantee consistency.
Shared & Exclusive Locks: The Read vs. Write Rule
A Shared (S) lock is like many people reading a library book at once; an Exclusive (X) lock is one person writing in it alone. Databases use S/X locks to manage concurrency, preventing writes from corrupting reads.
Database Transaction Log: Your System's Safety Net
A transaction log is your database's safety journal. Before changing data, it records the intended action in a durable file. This is vital for crash recovery, ensuring data isn't left corrupt. The footgun: its primary role is integrity, not just auditing.
Write-Ahead Logging (WAL): Survive Crashes by Journaling First
Think of it as a journal of intentions. Before changing data, a database writes the intended change to a log file first. This ensures that if the system crashes, it can recover by replaying the log, guaranteeing no writes are lost.
Lock Now or Check Later: Optimistic vs. Pessimistic Concurrency
Pessimistic concurrency locks data first, assuming conflict is likely ('ask permission'). Optimistic concurrency proceeds without locks and checks for conflicts before saving, assuming they're rare ('ask forgiveness'). Use pessimistic for high-contention.
Fourth Normal Form (4NF): Isolating Independent Facts
4NF prevents storing independent, multi-valued facts in one table. It applies when a key relates to two unrelated lists, like a restaurant's pizza types and its delivery areas.
Materialized Views: Pre-computing Slow Queries
A materialized view trades data freshness for query speed by storing the result of a slow query as a physical table. It's ideal for dashboards that run heavy aggregations, making them load instantly. The footgun is stale data: users see old results.
Denormalization: Trading Write Speed for Faster Reads
Denormalization speeds up database reads by intentionally adding redundant data, trading write-speed for read-performance. Use it for read-heavy systems like reporting dashboards where joins are too slow.
Surrogate Keys: Stable IDs for Unstable Data
A surrogate key is a meaningless, system-generated ID that never changes, unlike a 'natural' key (like an email) which can. Use it for stable joins between tables. The footgun is exposing these internal IDs in public URLs, which leaks data structure.
Third Normal Form (3NF): Nothing But The Key
Third Normal Form (3NF) dictates that every non-key column must describe 'nothing but the key.' This prevents transitive dependencies, where one non-key column determines another.
Functional Dependency: The Rules Behind Your Data
A functional dependency is a rule: if you know column A, you uniquely know column B. It's the logic behind primary keys and is used for database normalization to prevent data duplication.
Junction Table: Connecting Many to Many
A junction table is a bridge between two other tables, enabling a many-to-many relationship. It's used to connect students to classes or tags to articles. The common footgun is forgetting a unique constraint, allowing duplicate connections.
First Normal Form (1NF): No Nested Data
First Normal Form (1NF) means every table cell holds one single value, not a list. It's the first rule of relational databases, stopping you from storing comma-separated strings. The footgun is stuffing multiple values into one field, which breaks SQL queries.
Entity-Relationship Diagrams: A Blueprint for Your Data
An Entity-Relationship Diagram (ERD) is a blueprint for a database, showing what you're storing and how it connects. It's used before writing code to design a relational database schema. The main footgun is designing the model to match the UI, not the data.
Boyce-Codd Normal Form (BCNF): Stricter Than 3NF
BCNF is a database design rule stricter than 3NF, ensuring no attribute is determined by anything but a superkey. It's used to eliminate all data redundancies from functional dependencies, but can force trade-offs with dependency preservation.
We are hiring for this. Every open role lists the topics its interview covers, so you can prepare for the real thing rather than guessing.
See open roles