Intermediate concepts in Databases & Architecture, page 2
Page Replacement Algorithms: Evicting Data from Memory
A page replacement algorithm is a bouncer for your RAM. When memory is full and a new page is needed, it decides which existing page to evict to disk. This is crucial in virtual memory systems.
Hash Join: Faster Database Joins with Hash Tables
A hash join speeds up database joins by building an in-memory lookup table (a hash table) for the smaller table, then streaming the larger one past it to find matches. It's ideal for large, unsorted equijoins.
Sort-Merge Join: The 'Line Up and Walk' Join
A sort-merge join is like merging two sorted lines of people. It's efficient when tables are already sorted on the join key or memory is tight. The footgun: if data isn't pre-sorted, the initial sort can make it slower than other join methods.
External Merge Sort: Sorting Data Bigger Than RAM
External merge sort handles datasets too big for RAM. It sorts the data in memory-sized chunks, writes them to disk, then merges the sorted chunks back together. It's crucial for database indexing, but its speed is limited by disk I/O, not CPU.
Query Rewriting: Your Database's Unseen Optimizer
A database's query rewriter is like a smart GPS, finding a faster route (execution plan) to the same destination (your query result). It happens automatically to speed up joins and filters. The footgun: your handwritten query isn't what actually runs.
Eventual Consistency: Availability Now, Correctness Later
Eventual consistency prioritizes availability by letting replicas temporarily disagree. If updates stop, all nodes will eventually converge on the same value.
Wide-Column Store: Flexible Schema for Massive Datasets
A wide-column store is like a spreadsheet where each row can have its own unique columns. It's ideal for sparse data like user profiles or IoT readings. The footgun is thinking it's just a relational table with many columns—the flexibility is the point.
Graph Databases: When Relationships Are the Data
A graph database treats connections between data as first-class citizens. It's ideal for social networks or fraud detection where you query relationships by traversing links. The footgun is using it for simple tabular data where a relational DB is faster.
Database Sharding: Splitting Data for Scale
Sharding splits a database across multiple servers, like dividing a phone book into A-M and N-Z volumes. It's used when a single server can't handle the data size or write load. The footgun is that querying across shards is complex and slow.
Time Series Database: A Logbook, Not a Filing Cabinet
A Time Series Database (TSDB) is a specialized logbook for data that happens over time, like server metrics or sensor readings. It's built for high-speed writes and fast range queries. The footgun: don't use it for relational data like user profiles.
Fact Table: The Numbers in Your Data Warehouse
A fact table is the ledger of business events, recording what happened and how much. It's the core of a data warehouse, holding sales figures or page views. The footgun is storing descriptive text here; that belongs in linked dimension tables.
Dimension Tables: The 'Who, What, Where, When' of Your Data
Dimension tables provide the descriptive context—the 'who, what, where, when'—for raw numbers in a fact table. They are the backbone of data warehouses, letting you slice sales data by product or region. The footgun is polluting them with transactional data.
OLAP Cube Operations
OLAP cube operations let you analyze data like a multi-dimensional spreadsheet. Instead of just rows and columns, you navigate dimensions like time and location. Used in business intelligence to answer complex analytical questions.
Consistent Hashing: Resizing Distributed Systems Gracefully
Consistent hashing prevents mass data reshuffling when servers are added or removed. It maps keys to servers on a logical ring, so only a fraction of keys need remapping during a resize. This is crucial for distributed caches to avoid stampedes.
Quorum: How Distributed Systems Agree Without Unanimity
A quorum is a majority vote for distributed systems, letting them operate without waiting for every node. It's used in databases and consensus algorithms to ensure consistent writes. The footgun is setting the quorum too low, risking conflicting decisions.
Two-Phase Commit (2PC): All or Nothing, Together
Two-Phase Commit (2PC) ensures a distributed transaction is atomic: all participants either commit or abort together. A coordinator first asks all nodes to prepare (vote), then issues a final commit or abort.
Point-in-Time Recovery: Rewind Your Database to a Specific Second
Point-in-Time Recovery (PITR) is a database time machine, restoring data to a specific second, not just the last snapshot. It's crucial for reversing application-level errors.
How Database Indexes Rot and How to Fix Them
Your database indexes rot over time, making queries slower. Frequent writes cause fragmentation (disordered pages) and low page density (half-empty pages), forcing more disk I/O.
Database Auditing: Your Database's Security Camera
Think of database auditing as a security camera for your data, recording who did what and when. It's essential for security investigations and compliance, but the footgun is treating it as a substitute for access control—it only records a breach, it doesn't…
Database Encryption: Protecting Data at Rest
Database encryption turns your data into useless gibberish for anyone without the key. It protects sensitive data at rest, like PII or financial records, from direct theft of the database files.
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