How Databases Work: A Developer’s Guide

Databases power almost every application. They turn raw bytes on disk into structured, queryable information with guarantees around consistency, durability, and concurrency.

This guide explains the core building blocks: data models, storage engines, indexing, transactions, query planning, and scaling patterns. It focuses on practical concepts you will meet in SQL and NoSQL systems.

Data Models

• Relational (SQL): Data organized into tables with rows and columns, constrained by schemas, queried with SQL.
• Document/Key-Value (NoSQL): Flexible schemas, JSON-like documents or simple key-value pairs, optimized for scale and agility.
• Graph: Nodes and edges for highly connected data, suited for relationship-heavy queries.

Storage Engines

Under the hood, databases write to disk via structured files and logs to balance speed and safety.

• WAL (Write-Ahead Log): Changes are appended to a log to ensure durability and crash recovery.
• B-Tree/B+Tree: Balanced trees index ordered keys for fast lookups and range scans.
• LSM Tree: Batched writes and compaction optimize high-ingest workloads.

Indexes

Indexes accelerate queries by maintaining searchable structures separate from the base data.

• Primary index: Organizes the main data by primary key.
• Secondary index: Speeds lookups on non-key fields.
• Composite and covering indexes: Combine multiple columns and can satisfy queries without touching the table.

Transactions and ACID

Transactions group operations so they succeed or fail together.

• Atomicity: All or nothing.
• Consistency: Constraints always hold.
• Isolation: Concurrent operations do not corrupt state.
• Durability: Committed changes persist across crashes.

Common isolation levels:

• Read Uncommitted
• Read Committed
• Repeatable Read
• Serializable

Query Planning and Execution

SQL queries are parsed, optimized, and executed using available indexes and statistics.

• Parse: Translate SQL into an internal representation.
• Optimize: Choose join orders, access paths, and push down filters.
• Execute: Run operators (scan, filter, join, aggregate) and stream results.

Scalability Patterns

• Vertical scaling: Bigger machine for single-node databases.
• Replication: Copy data to followers for availability and reads.
• Sharding: Split data across nodes by key ranges or hashes.
• Caching: Keep hot results in memory to reduce latency.

The Developer Perspective

Workflows differ between SQL and NoSQL, but the fundamentals remain: model data, index critical queries, and respect transactional guarantees.

SELECT id, name, email
FROM users
WHERE email = $1;

import { Client } from "pg";
const client = new Client();
await client.connect();
const res = await client.query("SELECT id FROM users WHERE email = $1", [email]);
await client.end();

const user = await db.collection("users").findOne({ email });
await db.collection("users").updateOne({ id }, { $set: { name } });

Summary

Databases combine data models, storage engines, indexes, transactions, and planners to provide reliable, fast access to information. Understanding these layers helps you design schemas, write efficient queries, and scale systems with confidence.