Rigorous-Prescriptive

Python Database Internals: Building a Persistent Engine from Scratch

This book was generated using AI assistance.

7 Chapters

1h 44m total

20,775 words

Start Reading

About This Book

Voice Rigorous-Prescriptive

Tone Authoritative but accessible

Categories

Analytical Definitional Argumentative Narrative

1

The Architecture of Persistence

This section introduces the high-level architecture of a database management system, organized...

4 min read
This section introduces the high-level architecture of a database management system, organized as a layered structure: REPL for user interaction, Parser for SQL interpretation, Virtual Machine for bytecode execution, B-Tree for indexing, Pager for page-based storage management, and OS Interface for system abstraction. It emphasizes modularity and data flow between layers. The project skeleton is set up using Python 3.12+ with type hints, defining a Constants module with PAGE_SIZE=4096 bytes, HEADER_LIMIT=100, and PAGE_HEADER_FORMAT='<I I' for binary headers. A stub Pager class uses struct and memoryview for low-level binary manipulation. Key design trade-offs are discussed, comparing page sizes (4KB, 8KB, 2KB) in terms of I/O performance, memory usage, and overhead. This foundation enables further development with a focus on efficiency and scalability.
Read Chapter
1. The Disk vs. Memory Dichotomy
  5 min read
2. Defining the Row Format
  4 min read
2

The Storage Engine: Pages and the Pager

The Pager module manages fixed-size pages for disk storage, optimizing I/O operations....

5 min read
The Pager module manages fixed-size pages for disk storage, optimizing I/O operations. Each page is 4KB (4096 bytes), with a header using PAGE_HEADER_FORMAT ('<I I') for metadata like page number and data length, and a data section for actual content. The Pager class in Python 3.12+ implements file handling with attributes for filename and a page cache. Methods include open_file (handling file creation), close_file, read_page (returning a memoryview), write_page (ensuring exact PAGE_SIZE), and get_page_count. Trade-offs in page size are analyzed: 2KB pages reduce memory usage but increase overhead; 4KB balances performance; 8KB improves throughput but has higher memory footprint. The design emphasizes modularity, using struct and memoryview for low-level binary data manipulation, ensuring efficient disk I/O and data consistency.
Read Chapter
1. Implementing the Pager
  5 min read
2. The Table Abstraction
  5 min read
3

Cursor Abstraction and Linear Scan

The Cursor abstraction facilitates sequential row iteration in database tables via linear...

4 min read
The Cursor abstraction facilitates sequential row iteration in database tables via linear scan, ensuring O(N) time complexity as per boundary constraints. Implemented in Python with the Cursor class, it collaborates with Table and Pager components: Table manages row storage with Pager, and Cursor uses start, end, and advance methods for traversal. Key methods include cursor_start (initializes to first row), cursor_end (marks termination), and cursor_advance (progresses with boolean feedback). Row retrieval employs fetch_row via Table, leveraging binary data manipulation with struct and memoryview for performance. Design rationale emphasizes modular simplicity, postponing index-based optimizations like B-trees. Example diagrams illustrate page-based traversal, and data tables demonstrate cursor states during scans.
Read Chapter
1. The Virtual Machine (VM)
  5 min read
2. Benchmarking the Linear Scan
  4 min read
4

B-Tree Implementation: Leaf Nodes

This section introduces the structure of leaf nodes in a B-Tree, the...

5 min read
This section introduces the structure of leaf nodes in a B-Tree, the foundational component for database indexing. Leaf nodes store key-value pairs without child pointers, using a binary layout for efficient access. The leaf node header contains metadata: Node Type (0 for leaf, 1 for internal), Is Root flag (boolean), and Num Cells (unsigned integer), formatted in little-endian byte order. Each cell consists of a key (4-byte integer) for binary search and a value (36-byte serialized row data) for actual storage, enabling O(log N) access times. The implementation is in Python 3.12+ with type hints, using the struct module and memoryview for low-level binary manipulation. Key functions include initialize_leaf_node to create nodes with default headers, create_cell to pack key-value pairs, and extract_cell to retrieve data without copying. The design aligns with page-based storage (4096-byte pages) and leverages fixed offsets for direct access, setting the stage for B-Tree operations.
Read Chapter
1. Inserting into a Leaf Node
  8 min read
2. Reading from a Leaf Node
  6 min read
5

B-Tree Implementation: Internal Nodes and Splitting

This section implements the core components of B-Trees for growth and balance....

7 min read
This section implements the core components of B-Trees for growth and balance. Internal nodes are defined as structures storing sorted keys and child pointers, with a header format including Node Type, Is Root flag, and Num Keys. The binary layout is detailed with a markdown table showing field sizes and descriptions. The splitting algorithm is crucial for handling full nodes: it finds the median key, creates a new sibling node, redistributes keys and pointers, and updates the parent or creates a new root if necessary, ensuring all leaf nodes remain at the same depth. Parent-child relationship tracking is discussed as essential for maintaining tree integrity during these operations. Python code examples demonstrate initializing an internal node using struct and memoryview with type hints, and a stub for the splitting logic is provided. Key terminology includes Internal Node (stores keys and child pointers), Child Pointer (references to child nodes), Splitting Algorithm (procedure for dividing full nodes), and Median Key (middle key promoted during splitting). This builds on previous leaf node implementations to complete the B-Tree structure for efficient search, insertion, and deletion.
Read Chapter
1. The Splitting Algorithm
  8 min read
2. Traversing the Tree
  8 min read
6

Durability and Transactions

5 min read
Read Chapter
1. Writing the Log
  5 min read
2. Crash Recovery
  7 min read
7

Query Parsing and Interface

3 min read
Read Chapter
1. The Tokenizer
  6 min read
2. The Compiler
  7 min read

About This Book

Table of Contents