Intermediate concepts in Databases & Architecture
The Relational Model: Data as Simple Tables
The relational model organizes data into simple tables (relations) of rows (tuples), forming the foundation of SQL databases. It's used for structured data where consistency is critical, like in banking.
ACID: The Four Guarantees of Database Transactions
ACID is a contract for database transactions: all-or-nothing guarantees for data validity, even during crashes. It's critical for relational databases in finance or e-commerce. The footgun is assuming all databases offer this; many NoSQL systems don't.
Database Normalization: Tidy Tables, Less Redundancy
Database normalization organizes data into smaller, related tables to eliminate redundancy, like separating addresses from orders. It's the standard for transactional (OLTP) systems where data integrity is critical.
Foreign Keys: The Glue of Relational Databases
A foreign key is a pointer from one table to another, linking related data like a user to their posts. It enforces referential integrity, ensuring a post can't exist without a valid user. The footgun is forgetting this blocks deletions of referenced records.
Database Index: The Phonebook for Your Data
An index is like a book's index, letting you jump to the right row without scanning the whole table. It's essential for columns used in WHERE clauses, but over-indexing slows writes since every index must be updated on data changes.
Database Views: A Saved Query That Acts Like a Table
A database view is a saved query you interact with like a real table. It simplifies complex joins for applications or restricts data access for security, showing only specific rows or columns.
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.
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.
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.
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.
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.
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.
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.
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.
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.
The Query Optimizer: Your Database's Internal GPS
A query optimizer is your database's internal GPS, turning your SQL "what" into the fastest "how." It chooses the best execution plan—like join order or index usage—for every query. The footgun: stale statistics can trick it into picking a slow route.
Composite Indexes: One Index for Multiple Columns
A composite index is like a phone book sorted by last name, then first name. It's for queries filtering on multiple columns, like finding a specific person. The footgun is creating separate indexes, which is far less efficient than a single composite one.
Heap File Organization: Fast Writes, Slow Reads
Heap file organization is like tossing records into a box in no particular order. It's great for bulk-loading data quickly, but searching requires a full table scan. The footgun is using it for frequently queried tables, which kills performance.
Slotted Page Structure: Stable Pointers on Disk
A slotted page organizes data by growing records from the end of the page and pointers from the beginning. This allows databases to handle variable-sized records without costly reshuffling, keeping pointers stable.
Database Checkpoints: Faster Recovery After a Crash
A database checkpoint creates a known good point for faster crash recovery. Instead of writing every change to disk, it periodically flushes modified data from memory, reducing the amount of log data to process.
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