What a Database is & Why

In early computing, software stored data in flat text files (data.csv or records.json). As applications scaled to concurrent multi-user environments, flat files encountered fatal architectural bottlenecks:

1. No Concurrency Control (Race Conditions): Two threads opening users.txt simultaneously overwrite each other's updates, causing silent data corruption.
2. Scan Inefficiencies (O(N) Lookups): Finding record #8,412,091 requires reading every preceding line from disk, taking seconds to minutes instead of microseconds.
3. No Crash Durability: If a server loses power during a file write, the operating system leaves the file half-written and corrupted.
4. No Transactional Atomicity: If a bank transfer debit succeeds but the credit fails midway due to a disk-full error, money vanishes into thin air.

A Database Management System (DBMS) is a complex software engine specifically engineered to provide structured data definitions, sub-millisecond indexed search, multi-client concurrent isolation, and guaranteed crash recovery.

Every production database (PostgreSQL, MySQL, SQLite, Oracle) is composed of four distinct internal layers:

1. The Query Processing Engine

• Parser & Lexer: Validates SQL syntax and converts the query string into an Abstract Syntax Tree (AST).
• Cost-Based Query Optimizer (CBO): Evaluates dozens of equivalent execution strategies (e.g., Index Scan vs Sequential Scan, Hash Join vs Nested Loop Join), estimating disk I/O and CPU costs using statistical histograms.
• Execution Engine: Implements the Volcano Iterator Model (open(), next(), close()) to stream rows through execution operators.

2. The Buffer Pool Manager

Maintains a dedicated chunk of physical RAM (e.g., 8GB to 128GB) containing cached Data Pages (typically 8KB in PostgreSQL, 16KB in MySQL InnoDB). When a query requests a row, the buffer pool checks if the page is in memory (Buffer Hit); if not, it reads the page from disk and evicts an old page using LRU-2 (Least Recently Used) algorithms.

3. The Concurrency & Lock Manager

Enforces transaction isolation. Uses Multi-Version Concurrency Control (MVCC) and Two-Phase Locking (2PL) to prevent dirty reads, non-repeatable reads, and lost updates without halting concurrent readers.

4. The Recovery & Log Manager (ARIES / WAL)

Maintains an append-only Write-Ahead Log (WAL). Before any modified page in RAM is allowed to overwrite the physical table file, the change description is flushed to the WAL disk file via fsync(). During a sudden crash or power outage, the recovery engine replays the WAL to reconstruct a mathematically consistent state.