How Databases Actually Store Your Data

1. Hook & Problem: The Illusion of the "Save" Button #

Early in my career, I viewed a database as a magical, black-box version of JSON.stringify() and fs.writeFileSync(). I assumed that when I executed INSERT INTO users..., the database simply opened a file, appended a line of text, and closed it.

Then came the "Black Friday Incident."

We were running a high-traffic e-commerce platform. Under 50x normal load, our "reliable" database started throwing IO Wait spikes that paralyzed the entire API. Worse, after a sudden power failure in the data center, we realized that several "successfully committed" orders had simply vanished, while others had corrupted "ghost" records.

I realized then that I didn't actually know how a database stores data. I knew the SQL syntax, but I didn't understand the violent physical reality of magnetic platters, NAND gates, and kernel page caches.

If you think a database is just a wrapper around a file system, you are building on a foundation of sand. To build systems that survive 100x spikes and literal power outages, you must understand the journey of a bit from a COMMIT command down to the physical disk.

2. The Mental Model: The Librarian and the Ledger #

Imagine a massive, infinite library (the Database).

If you were the librarian, how would you store new books (data) so they could be found instantly among millions?

1. The Naive Way (Append-only): You just throw every new book at the end of the shelf. Writing is fast! But finding "The Great Gatsby" requires walking past every single book from the beginning. This is a Full Table Scan.
2. The Sorted Way: You keep all books in alphabetical order. Finding a book is fast (Binary Search), but every time a new book arrives, you have to physically shift every other book on the shelf to make room. This is Write Amplification.

The Real Way (The B-Tree / Page Model):
You divide the library into Fixed-Size Pages (e.g., 8KB blocks). Instead of one giant shelf, you have a hierarchy of index cards. The top card tells you which aisle to go to; the aisle card tells you which shelf; the shelf card tells you the exact page.

The Architectural Flowchart #

[ APPLICATION LAYER ]
       |
       |  "INSERT INTO users (id, name) VALUES (1, 'Barath')"
       v
[ QUERY ENGINE ] (Parser, Optimizer, Executor)
       |
       |  "Store this tuple in Table 'users'"
       v
[ STORAGE ENGINE ] <----------------------------+
       |                                        |
       +-----> [ WRITE-AHEAD LOG (WAL) ]        | (Recovery)
       |       (Sequential Append to Disk)      |
       |                                        |
       +-----> [ BUFFER POOL (RAM) ] -----------+
       |       (8KB Pages cached in memory)
       |                |
       |                | (Dirty Page Flush / Checkpoint)
       v                v
[ OS KERNEL / FILE SYSTEM ] (page cache, scheduler)
       |
       +-----> [ SYSCALLS: pwrite(), fsync() ]
       |
       v
[ PHYSICAL STORAGE ] (SSD / HDD / NVMe)
       |
       +-----> [ CONTROLLER ] -> [ NAND CELLS / PLATTERS ]

3. Step-by-Step Internal Architecture: Down to the Metal #

A. The Atomic Unit: The Page #

Databases do not read or write individual rows. They operate on Pages (usually 8KB in PostgreSQL/SQLite or 16KB in MySQL).

A Page is a binary blob with a specific layout:

• Header: Metadata (Page ID, checksums, free space pointers).
• Slotted Page Directory: An array of pointers to the start of each row (tuple) within the page.
• Tuples: The actual data, stored from the bottom of the page upwards.

Senior Insight: Why fixed-size pages? Because disk I/O is expensive. The OS and the hardware also think in blocks (sectors). By aligning DB pages with OS blocks, we avoid "Partial Writes" where one DB write spans two physical sectors, risking corruption if power fails mid-write.

B. The Buffer Pool: Why RAM is a Lie #

When you ask for a row, the DB looks in the Buffer Pool (a large chunk of memory allocated at startup).

1. If the page is in RAM, it's a Cache Hit (nanoseconds).
2. If not, it triggers a Disk Read (milliseconds).

When you update a row, the DB marks the page in RAM as "Dirty." It is NOT immediately written to the data file. Writing to the main data file is "Random I/O" (slow). Instead, it stays in RAM.

C. The WAL (Write-Ahead Log): The Durability Secret #

If we don't write to the data file immediately, what happens if the power cuts?
This is where the WAL comes in. Before the DB confirms the transaction to you, it appends the change to a sequential, append-only file called the WAL.

• Append-only I/O is fast (sequential).
• Checkpoints: Periodically, the DB flushes "Dirty Pages" from the Buffer Pool to the main data file and clears the WAL.

D. The Kernel Bridge: `fsync()` and `O_DIRECT` #

When a DB calls write(), the OS doesn't actually put the data on disk. It puts it in the Kernel Page Cache. To the DB, the write is done. To the hardware, it isn't.
If the OS crashes, that data is gone.
To prevent this, databases use the fsync() syscall. This forces the OS to flush the hardware buffers to the physical medium.

The Cost of Safety: An fsync() can be 10x-100x slower than a regular write(). This is the primary bottleneck of database performance.

4. Hands-on Experiment: Building a Mini-WAL #

Let's simulate how a database ensures durability. We will write a script that simulates a "Database" with a data file and a WAL. We will simulate a crash and show how the WAL recovers the state.

import os
import json
import time

DATA_FILE = "storage.db"
WAL_FILE = "storage.wal"

def log_to_wal(operation):
    """Simulates Write-Ahead Logging with fsync."""
    with open(WAL_FILE, "a") as f:
        f.write(json.dumps(operation) + "\n")
        f.flush()
        os.fsync(f.fileno()) # The 'Senior' move: Force bits to physical disk

def commit_to_storage(data):
    """Simulates the background 'Checkpoint' process."""
    # In a real DB, this happens asynchronously
    with open(DATA_FILE, "w") as f:
        json.dump(data, f)
        f.flush()
        os.fsync(f.fileno())

def recover():
    """Simulates recovery after a crash."""
    print("--- Starting Recovery ---")
    # Load last known state
    state = {}
    if os.path.exists(DATA_FILE):
        with open(DATA_FILE, "r") as f:
            state = json.load(f)
    
    # Replay WAL
    if os.path.exists(WAL_FILE):
        with open(WAL_FILE, "r") as f:
            for line in f:
                op = json.loads(line)
                print(f"Replaying WAL log: {op}")
                state.update(op)
    
    print(f"Recovered State: {state}")
    return state

# --- THE SIMULATION ---

# 1. Initial State
db_state = {"balance": 100}
commit_to_storage(db_state)

# 2. A Transaction happens
new_op = {"balance": 150, "last_tx": "deposit"}
print("Writing to WAL...")
log_to_wal(new_op)

# 3. SIMULATE CRASH HERE
print("CRASHING SYSTEM BEFORE MAIN STORAGE UPDATE...")
# We do NOT call commit_to_storage(new_op) yet.

# 4. On Restart
recovered_state = recover()

# Clean up for next run
# os.remove(DATA_FILE); os.remove(WAL_FILE)

Why this matters: #

Run this script. Notice that storage.db still says balance: 100, but because the WAL recorded the 150 change and was fsync'd, the database is able to "replay" the history and arrive at the correct state. This is exactly how PostgreSQL or MySQL survives a kill -9.

5. Failure Modes & War Stories #

War Story 1: The "Torn Page" Outage #

A FinTech company I consulted for had a corrupted database after a power failure.
The Root Cause: The DB used 8KB pages. The SSD hardware only guaranteed atomic writes for 512-byte sectors. During the power failure, the SSD was halfway through writing an 8KB page. It wrote 4KB of new data and left 4KB of old data.
The Result: The checksum failed, and the database refused to start.
The Fix: We enabled "Full Page Writes" in Postgres, which logs the entire page to the WAL the first time it's modified after a checkpoint, allowing the DB to restore the whole page if a "tear" is detected.

War Story 2: The `fsync()` Gate (Fsyncgate) #

In 2018, the PostgreSQL community discovered a terrifying bug in how the Linux kernel handled fsync() errors. If a disk failed to write data, fsync() would return an error. However, if the DB called fsync() again, the kernel would return success, even though the data was never written.
The Result: Silent data corruption.
The Lesson: Senior engineers don't just trust the 0 return code of a syscall. They understand the kernel's error-reporting state machine.

6. Trade-offs & Production Considerations #

Row-Oriented (OLTP) vs. Column-Oriented (OLAP) #

• Row-Oriented (Postgres/MySQL): Stores [ID, Name, Email] together on the same page. Great for "Give me all info for User 5." Terrible for "What is the average age of 10 million users?" (because it has to read the Names and Emails into memory just to get the Age).
• Column-Oriented (ClickHouse/DuckDB): Stores all Ages together, then all Names. Great for analytics; terrible for single-row lookups.

B-Trees vs. LSM-Trees #

• B-Trees (Read Optimized): Keeps data sorted in a balanced tree. Great for reads. Every write requires finding a specific spot on disk (Random I/O).
• LSM-Trees (Write Optimized - used in Cassandra/RocksDB): Never updates data in place. It just appends new versions to "SSTables." Periodically "compacts" them in the background. This turns random writes into sequential writes, making it 10x faster for heavy ingestion.

7. Personal Retrospective & Lessons Learned #

Understanding storage changed how I write code:

1. Batch Your Writes: 1,000 individual INSERTs mean 1,000 fsync() calls. One transaction with 1,000 rows means 1 fsync(). The difference is seconds vs. milliseconds.
2. Respect the Working Set: If your "hot" data (the pages people access frequently) exceeds your Buffer Pool (RAM), your performance will fall off a cliff as the DB starts "swapping" pages from disk.
3. IOPS are the real budget: In the cloud (AWS EBS), you don't just pay for GBs; you pay for IOPS. Understanding page sizes allows you to calculate exactly how many IOPS your application will consume.

8. Connection to Agentic AI: The Memory of the Machine #

Why does a systems engineer care about database internals when building AI Agents?

1. Vector Databases are Databases First: A Vector DB (Pinecone, Milvus, Weaviate) still has to solve the durability problem. When an agent "remembers" a conversation, that embedding must be persisted. If the Vector DB doesn't use a WAL or proper page management, your agent will have "amnesia" after a crash.
2. LSM-Trees for High-Velocity Logs: AI agents generate massive amounts of trace data (thought loops, tool calls). Storing these in a B-Tree database like Postgres can lead to write-bottlenecks. Using an LSM-tree based store (like ClickHouse or an optimized log) is essential for observability at scale.
3. Context Window vs. Disk: The "Context Window" of an LLM is like the CPU Cache. The "Vector Store" is the Disk. Moving data between them efficiently requires understanding Data Locality—the same principle that makes B-Trees fast.

9. What's Next? #

We've mastered how data sits still. But how does it move? Tomorrow, for Day 5, we dive into the nervous system of the internet: "The Life of a Query: How a String of SQL Becomes a Result Set." We'll look at query planners, cost-based optimizers, and the magic of join algorithms.

See you in the next one.

Barath Raju
Senior Systems Engineer
Building the future, one byte at a time.