How Databases Work: Architecture & DBMS Internals

In this lesson, you will learn:

The end-to-end execution pipeline from query submission to physical disk retrieval:

Precise distinctions between Database, DBMS, and Database Server

Why browsers never connect directly to database servers

Query parsing and Abstract Syntax Tree (AST) compilation

Cost-Based Optimizer (CBO) decision-making algorithms

Buffer Pool caching mechanics and disk page retrieval

Embedded (SQLite) vs Client-Server (PostgreSQL) architectures

1. Dissecting Core Terminology

Database, DBMS, and Database Server: Are They the Same?

In casual software conversations, developers often use the words "database", "DBMS", and "server" interchangeably. However, from an architectural standpoint, each represents an entirely different layer of the technical stack:

1. Database (The Data)

The structured collection of persistent data stored on disk or flash memory. For an e-commerce platform, this is the collection of tables, rows, schemas, indexes, and catalogs containing users, orders, and payment records.

users, orders, products tables

2. DBMS (The Software Engine)

The Database Management System software program that controls the storage, indexing, querying, concurrency locking, and transaction guarantees of the data. Examples include PostgreSQL, MySQL InnoDB, Oracle, and SQLite.

Postgres Engine, MySQL InnoDB

3. Database Server (The Running Daemon)

The long-running background service (daemon process) hosting the DBMS. It binds to a network socket/port (such as 5432 or 3306), accepts concurrent client connections, manages threads, and executes remote queries.

localhost:5432 (Postgres Daemon)

Architectural Summary: The Database is the structured data itself; the DBMS is the computational software managing that data; and the Database Server is the running networked process providing connection endpoints.

2. Deployment Topology

Embedded vs Client-Server Database Architectures

A common beginner misconception is assuming that every database requires a separate remote cloud virtual machine. In real-world software engineering, database architectures fall into two primary architectural paradigms:

Client-Server (e.g., PostgreSQL, MySQL) TCP Socket

The DBMS runs as a dedicated server process on the same machine or across the network. Applications connect over TCP/IP sockets using binary wire protocols.

• Supports hundreds or thousands of simultaneous application nodes

• Built for high concurrency, horizontal read-replicas, and ACID clustering

• Incurs small network round-trip overhead (~0.5–5 ms per query)

Embedded (e.g., SQLite, DuckDB) In-Process C Lib

There is no independent server daemon. The DBMS engine is compiled directly into the application process as a C library.

• Zero network latency (direct in-memory function calls, sub-microsecond)

• The complete database resides in a single binary file (app.db)

• Powers mobile apps (iOS/Android), desktop software, and edge analytics

3. Security & Concurrency Isolation

Why Browsers Never Connect Directly to the Database

A standard multi-tier software system follows this architectural flow:

User Browser Backend API DBMS Disk Storage

Why can't the client browser connect directly to PostgreSQL or MySQL over a WebSocket or TCP connection? Why is the backend intermediary strictly mandatory?

1. Credential & Secret Exposure: If database credentials were embedded in client-side JavaScript, any malicious user opening Developer Tools ("Inspect Element") could extract the database password and execute destructive queries like DROP TABLE users;.

2. Business Logic & Authorization Enforcing: Is the requesting user logged in? Does their wallet have sufficient funds? Is the product order price non-negative? These validation and authorization rules must be verified by trusted backend application logic before reaching database storage.

3. Connection Pooling: If 100,000 active mobile users each opened a dedicated TCP socket directly to PostgreSQL, the database server would run out of file descriptors and RAM instantly. A backend server uses a Connection Pool (e.g., PgBouncer) with only 20–50 multiplexed connections to serve hundreds of thousands of users.

4. Query Internals

The 6-Stage Query Execution Pipeline

Suppose our backend API sends the following standard SQL command to the database engine:

SELECT name, email FROM users WHERE id = 25;

How does a plain ASCII text string travel through database internals to locate the physical bytes on disk? Below is the 6-stage scientific execution lifecycle:

Stage 1: Query Reception & Connection Management Stage 1

The DBMS Connection Manager accepts the incoming TCP packet, authenticates the backend client credentials, and assigns a dedicated worker process or thread to execute the request.

Stage 2: Lexical Parsing & Syntax Validation Stage 2

The Query Parser decomposes the string into tokens and builds an Abstract Syntax Tree (AST). It checks syntax validity and queries the internal system catalog: does the users table exist? Are the name and email columns valid attributes?

Stage 3: Cost-Based Optimizer & Physical Execution Plan The Brain of DBMS

There are many ways to fulfill a query. The Cost-Based Optimizer (CBO) computes algorithmic costs based on table statistics: "Should I execute a full sequential table scan, or perform a B+ Tree Index Seek on column `id`?" It selects the lowest-cost path and outputs a concrete Execution Plan.

Stage 4: Buffer Pool Lookup & Storage Engine Fetch Cache Hit vs Disk Fetch

The Execution Engine presents the plan to the Buffer Pool Manager. If the target 8 KB page is already in RAM (Cache Hit), it is accessed in ~0.01 ms. If not (Cache Miss), the storage engine reads the exact page block from NVMe/SSD into memory.

Stage 5: Row Materialization & Protocol Serialization Stage 5

The engine reads the raw binary page, extracts the specific row corresponding to id = 25, projects only the requested columns (name, email), serializes the result into the DBMS binary wire format, and streams it back through the network socket.

Stage 6: Application Processing & UI Rendering Stage 6

The backend API receives the raw row set, converts it into a JSON HTTP response, and transmits it over TLS to the client browser. The frontend web app updates the DOM to display the user profile.

5. Storage Hierarchy

The Magic of the Buffer Pool (Page Cache)

A common misconception is assuming the database loads all records into RAM. Consider this real-world scenario: your database size might be 500 GB, while the host server has only 32 GB of RAM. How can the DBMS deliver sub-millisecond queries under this physical constraint?

The Database Memory Model:

Persistent Disk (NVMe / SSD): All database tables and indexes are permanently stored as fixed-size blocks (typically 8 KB in PostgreSQL, 16 KB in MySQL InnoDB) called Pages. Even if power cuts abruptly, disk state remains persistent.

RAM Buffer Pool (Page Cache): Frequently accessed pages ("Hot Pages") are cached in RAM. When a query needs a page, it first checks the Buffer Pool.

When the Buffer Pool becomes full, an LRU (Least Recently Used) or Clock Sweep algorithm evicts cold pages to free space. Dirty pages (modified in memory) are safely synchronized to disk using Write-Ahead Logging (WAL).

6. The Core Primitives

CRUD: The 4 Fundamental Database Operations

A database engine does not merely execute SELECT queries to read records. Dynamic software continuously modifies, inserts, and removes state. These 4 operations form the acronym CRUD:

C — Create

INSERT

Appends a brand-new record to a table page.

R — Read

SELECT

Retrieves filtered rows matching search criteria.

U — Update

UPDATE

Modifies column values in existing rows.

D — Delete

DELETE

Removes or marks rows as deleted from pages.

7. Interactive Visual Laboratory

Query Execution & Client-Server Architecture Lab

Use the interactive simulators below to observe query execution dynamics and compare network-bound vs in-process database engines:

Select a Query Preset and Follow the Data Flow Through 6 Stages

Observe how parsing, cost-based planning, and buffer pool caching work together.

Interactive Execution

Query Preset:

SELECT * FROM users WHERE id = 25;

Browser/Client

HTTP Request

Backend API

SQL Generation

Query Parser

AST Validation

Optimizer

CBO Plan

Buffer / Disk

Page Read / WAL

Result Stream

Response Delivery

Pipeline Decision & Execution Log: Timing: 0.00 ms

> Select a query preset above and click "Run Pipeline".

8. Master Mental Model

The Bidirectional Flow: Remember This Blueprint

Whenever you design backend systems or write SQL queries, keep this mental image of bidirectional flow in mind:

Request Flow (Inbound):

APPLICATION

↓ SQL Query String

DBMS CONNECTION MANAGER

↓ Query Parser & AST

COST-BASED OPTIMIZER

↓ Physical Execution Plan

BUFFER POOL (RAM CACHE)

↓ Disk Page Read (if miss)

STORAGE ENGINE & WAL

Response Flow (Outbound):

RAW DISK PAGE / BUFFER

↑ Record Row Fetch

FILTER & PROJECT ATTRIBUTES

↑ Construct Result Set

DBMS PROTOCOL SERIALIZE

↑ Wire Protocol over TCP

BACKEND APPLICATION

↑ JSON API Serialization

USER INTERFACE (BROWSER/APP)

Concept Check

Validate Your Architectural Understanding

1. What is the primary role of the Query Optimizer in a relational database management system?

2. Why must web browsers communicate with databases through a backend API rather than connecting directly?

3. What is the fundamental architectural difference between SQLite and PostgreSQL?