How Databases Work: SQL, NoSQL, and Data Storage • Root Node

1. What Problem Databases Solve

Why can't applications simply write data into a JSON file on disk using standard filesystem APIs? If two concurrent threads write to that file simultaneously, data corruption occurs. If the server loses power midway through writing a 10MB file, the file is corrupted. If the file grows to 100GB, searching for a specific customer ID requires scanning through the entire file sequentially, exhausting RAM and locking the CPU.

Database Management Systems (DBMS) solve this by providing transactional consistency, high-speed indexed retrieval, concurrent isolation, and durable persistence guarantees.

Enterprise datacenter disk storage array — Figure 2: Non-volatile NVMe disk arrays servicing buffered database transactions.

2. Storage Engines and B-Tree Indexing

At the heart of relational database engines (such as PostgreSQL and MySQL InnoDB) is the B-Tree (and B+Tree) data structure.

Data on disk is stored in fixed-size blocks called Pages (typically 8KB or 16KB). A B+Tree is a self-balancing search tree where internal nodes contain keys guiding searches to child nodes, and all actual record pointers reside in leaf nodes linked in a contiguous doubly linked list.

Because of this branching structure, searching through a table with 100 million rows requires inspecting only 3 or 4 pages from disk—reducing search time from 30 seconds of sequential scanning down to 2 milliseconds.

3. Write-Ahead Logs (WAL) and Crash Recovery

Writing data directly into random locations in B-Tree data files on disk is slow because random disk I/O introduces seek latency.

Database engines resolve this through a Write-Ahead Log (WAL). When an INSERT or UPDATE occurs, the database first writes the change sequentially to an append-only log file on disk and confirms the commit to the client. The actual data pages in memory (buffer pool) are marked "dirty" and lazily flushed to disk in the background. If power cuts out abruptly, the database reboots, replays the WAL log, and restores total consistency.

Terminal terminal displaying SQL query performance plans — Figure 3: Analyzing query execution trees and index scans with EXPLAIN ANALYZE.

4. ACID Transaction Guarantees

Relational databases are celebrated for providing ACID transactional guarantees:

Atomicity: "All or nothing." If a transaction moves money between accounts, debiting Account A and crediting Account B, either both operations succeed or both are completely rolled back.
Consistency: The database transitions from one valid state to another, enforcing foreign keys, unique constraints, and schema rules.
Isolation: Concurrent transactions execute without interfering with one another, managed through isolation levels (Read Committed, Repeatable Read, Serializable).
Durability: Once a transaction commits, its modifications are permanently recorded in non-volatile storage and survive power outages.

5. The Relational Model & SQL

Formulated by Edgar F. Codd in 1970, the relational model organizes data into mathematical relations (tables) composed of tuples (rows) and attributes (columns).

Structured Query Language (SQL) is a declarative language: you specify what data you want, not the step-by-step procedural instructions for how the CPU should fetch it.

Silicon circuitry representing relational database query optimizer logic — Figure 4: Computational pipelines executing relational join algorithms in memory.

6. NoSQL Paradigms & The CAP Theorem

As web applications scaled horizontally across distributed global clusters, traditional single-node relational databases faced scaling bottlenecks. This birthed NoSQL:

Document Stores (MongoDB): Store hierarchical JSON-like documents. Flexible schemas, suitable for rapidly changing data structures.
Key-Value Stores (Redis): Ultra-fast in-memory hash tables. Ideal for caching, session storage, and rate limiting.
Wide-Column (Cassandra, Bigtable): Distributed horizontally partitioned datasets handling billions of write-heavy events.

7. Query Planners & Cost Estimators

When you execute a complex SQL query with multiple JOIN clauses, the database doesn't just run it blindly. The Query Planner evaluates different execution paths (Index Scan vs Sequential Scan, Hash Join vs Nested Loop vs Merge Join), calculates mathematical cost estimates based on table statistics, and selects the optimal path.

8. Real Relational Schema Example

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    author_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    body TEXT NOT NULL,
    published_at TIMESTAMP WITH TIME ZONE
);

CREATE INDEX idx_articles_slug ON articles(slug);

9. Frequently Asked Questions

What is the difference between an Index Scan and a Sequential Scan?

A Sequential Scan (Seq Scan) reads every single page in the table from start to finish. An Index Scan traverses the B-Tree index to locate only the exact pages holding matching records, saving thousands of disk reads.

What is Database Normalization?

Normalization (such as 3NF - Third Normal Form) is the process of organizing database schemas to eliminate redundant duplicate data and prevent update anomalies by dividing tables and establishing foreign key relationships.