More in Databases & Architecture — page 8
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.
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.
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.
ORM: The Virtual Object Database Layer
ORM converts data between relational databases and object-oriented program memory, creating a virtual object database inside your code. The footgun is designing object models that ignore the relational structure, forcing awkward translations you never see.
How SQL Queries Become Abstract Syntax Trees
An AST turns a flat SQL string into a tree of operations the database can reason about. The parser builds this tree before execution planning. Do not confuse it with the raw parse tree, which keeps punctuation and formatting the AST strips away.
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.
DML: Insert, Update, and Delete
DML is the change-focused subset of database languages like SQL that adds, modifies, and removes data. It appears in every write to a table. The footgun is assuming SELECT belongs in DML; read-only querying is sometimes split out as DQL instead.
DDL: The Blueprint for Database Objects
DDL is the blueprint for your database. You reach for it when spinning up new tables, indexes, or user permissions, not when querying rows. The footgun is running DROP thinking you are deleting data, not vaporizing the entire table structure.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Optimizer Hints: Backseat Driving Your Database
An optimizer hint lets you override the database's query plan, like telling a GPS which street to take. Use it as a last resort when you know more than the optimizer, but beware: hints can become performance traps when data or schemas change.
HNSW: Vector Search with a Graph Highway System
HNSW finds approximate nearest neighbors in huge datasets by building a multi-layered graph, like a highway system over local roads. It's the engine in vector databases for similarity search. The footgun: it trades perfect accuracy for massive speed gains.