Intermediate concepts in Databases & Architecture, page 4
Buffer Manager: The Database's Memory Gatekeeper
The buffer manager acts as a database's private RAM cache, deciding which data pages to keep in memory versus fetching from slow disk. It's central to query performance, as it tries to serve all data requests from this fast cache.
Single-Leader Replication: One Node to Rule Them All
Think of a single source of truth. One 'leader' server takes all writes, while 'follower' servers handle read traffic. This is the default for many databases like PostgreSQL and MongoDB to scale reads.
Multi-Leader Replication: Enabling Writes Across Datacenters
Multi-leader replication allows multiple nodes to accept writes, avoiding a single-leader bottleneck. It's used in multi-datacenter systems for low-latency local writes and in offline apps. The main footgun is resolving write conflicts from concurrent updates.
Hash-Based Aggregation: Grouping Data Without Sorting
Hash-based aggregation uses a hash table to group data for functions like COUNT or SUM, avoiding a costly sort. It's used in database query engines for GROUP BY operations, especially when distinct groups fit in memory.
Vectorized Query Execution: Processing Batches, Not Rows
Vectorized execution processes data in batches of thousands of rows, not one at a time. This lets analytical databases like ClickHouse and Snowflake scan billions of rows in seconds by keeping data in CPU cache and using SIMD instructions.
Synchronous vs. Asynchronous Replication: A Trade-off
Replication is a trade-off: synchronous waits for all copies to confirm a write, guaranteeing consistency but risking availability. Asynchronous lets the primary move on immediately, prioritizing speed.
Multi-Region Databases: Resilience, Latency, and Compliance
A multi-region database is a strategy for resilience, low latency, and data compliance. It's used to survive region outages, keep data in-country, and serve reads close to users. The footgun is managing low-level replica placement directly, which is complex.
Watermarks: Defining 'Done' in Event Streams
A watermark tells a stream processor when a time window is 'complete' for unordered events. It's a timestamped signal declaring 'no more data is expected before this point,' allowing aggregations to be finalized. The key footgun is balancing lateness vs.
Phantom Reads: When New Rows Appear Mid-Transaction
A phantom read occurs when a transaction repeats a query and finds new rows that match its search criteria, inserted by another committed transaction. It's common in reporting jobs that need a stable set of data.
Caching: Write-Through for Safety, Write-Back for Speed
Write-through caching writes to the database immediately for data safety, while write-back delays writes for speed. Use write-through for critical data and write-back for high-volume updates.
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.
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.
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.
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.
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.
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.
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.
Backpressure: Slow the Producer or Crash
Backpressure is a feedback signal telling upstream to slow down when downstream cannot keep up. You see it in stream processors like Flink or Kafka where a slow consumer risks memory exhaustion. Ignore it and queues grow until the service crashes.
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.
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