Database Performance Engineering

Inside SQLite, PostgreSQL, Browser AI, Web Automation and Modern Storage Engines

Introduction

Chapter 1: The Anatomy of a Database System

1. The Database as a Stack: Layers from SQL to Silicon
2. Core Responsibilities: Durability, Consistency, Concurrency, Availability
3. The Storage Engine Contract: What the Query Processor Expects
4. System vs Process: Database as Application vs Database as Operating System
5. Performance as an Emergent Property

Chapter 2: Hardware Reality and Its Consequences

1. CPU Cache Hierarchy and Locality: L1, L2, L3 and Working Set Size
2. Memory Subsystem: Latency, Bandwidth and NUMA Effects
3. Storage Media Physics: SSDs, NVMe, Rotational Disks and Persistent Memory
4. I/O Patterns: Sequential, Random and Mixed Workloads
5. Hardware-Aware Design Decisions

Chapter 3: The Mathematics and Statistics of Performance

1. Latency Distributions: Mean, Median and Why They Lie
2. Percentiles and Tail Latency: P99, P999 and What Matters
3. Throughput and Saturation: Finding the Knee of the Curve
4. Little’s Law and System Thinking
5. Statistical Rigor: Repetition, Variance and Avoiding False Conclusions

Chapter 4: B-Trees and Their Variants

1. B-Tree Fundamentals: Shape, Balance and Amortized Cost
2. Page Structure and Node Layout
3. Insertion, Deletion and Split Algorithms
4. Search and Range Scan Mechanics
5. Space Utilization and Fragmentation

Chapter 5: LSM Trees and Log-Structured Storage

1. The LSM Invariant: Sequential Writes, Hierarchical Merge
2. MemTable, SSTables and Flush Operations
3. Compaction Strategies: Leveled, Size-Tiered and Hybrid
4. Read Amplification and Bloom Filters
5. Write-Ahead Logs in LSM Systems

Chapter 6: Page-Oriented Storage and Buffer Pools

1. Page Format: Headers, Payloads and Free Space Maps
2. The Buffer Pool: LRU, Clock and Adaptive Replacement
3. Dirty Page Management and Write-Back
4. Pinning, Latching and Concurrency Control in the Cache
5. OS Page Cache vs Database Buffer Pool

Chapter 7: Write-Ahead Logging and Crash Recovery

1. The Write-Ahead Logging Invariant
2. Log Records, LSNs and Sequence Ordering
3. Checkpointing: Full, Incremental and Archived
4. ARIES and Modern Recovery Protocols
5. Recovery Procedures: Analysis, Redo, Undo

Chapter 8: Transactions, MVCC and Isolation

1. ACID as Implementation Constraints
2. Two-Phase Locking and Deadlock Detection
3. MVCC Snapshot Mechanics: Version Chains and Visibility
4. Isolation Levels and Their Anomalies
5. Serializable Execution: From 2PL to Snapshot Isolation

Chapter 9: SQLite Architecture Overview

1. Design Philosophy: Zero Configuration and File-Based Isolation
2. Codebase Layout and Build System
3. The VDBE: Virtual Database Engine Architecture
4. Memory Management: malloc, Page Cache and the Heap
5. Threading Models and Mutex Strategies

Chapter 10: SQLite Storage and Page Format

1. Database File Header: Version, Schema and Flags
2. B-Tree Page Types: Interior, Leaf and Overflow
3. Varint Encoding and Row Storage
4. Cell Pointers and Free Byte Lists
5. Schema Storage and the sqlite_master Table

Chapter 11: SQLite Transaction and Concurrency Model

1. File Locking: UNIX flock and Windows LockFileEx
2. Rollback Journal vs Write-Ahead Log
3. WAL Checkpointing and Reader Blocking
4. Mutex Hierarchies and Thread Safety
5. SAVEPOINT and Nested Transaction Semantics

Chapter 12: SQLite Query Processing and Indexing

1. Parsing and Bytecode Generation
2. The VDBE OpCodes and Execution Flow
3. Index Selection Heuristics and Cost Estimation
4. Co-routine Queries and Subqueries
5. EXPLAIN and EXPLAIN QUERY PLAN Analysis

Chapter 13: PostgreSQL Architecture Overview

1. Process Model: Postmaster, Backend and Shared Memory
2. Shared Buffers and the Buffer Manager
3. Background Workers: Autovacuum, Writer, Checkpointer
4. Configuration Architecture and GUCs
5. Extension System and Custom Storage Engines

Chapter 14: PostgreSQL Storage and Indexing

1. Heap Page Layout and Tuple Structure
2. Item Pointers and TOAST Compression
3. B-Tree Index Internals and Vacuum Interaction
4. GiST and GIN: Generalized Search Structures
5. BRIN: Block Range Indexes for Large Tables

Chapter 15: PostgreSQL MVCC and Transaction System

1. Transaction IDs and Subtransactions
2. Visibility Rules and Snapshot Construction
3. Dead Tuples and Bloat
4. Autovacuum Scheduling and Configuration
5. XID Wraparound and Emergency Vacuum

Chapter 16: PostgreSQL Concurrency and Locking

1. The Lock Manager: Hash Tables and Lock Queues
2. Lock Types and Granularities
3. Row-Level Locks and Key-Share Conflicts
4. Deadlock Detection Algorithm
5. Nowait, Skip Locked and Conflict Resolution

Chapter 17: PostgreSQL Query Optimizer and Execution

1. Parser, Rewriter and Planner Pipeline
2. Path Types and Cost Estimation
3. Join Algorithms: Nested Loop, Hash and Merge
4. Parallel Query Execution
5. EXPLAIN ANALYZE and Plan Interpretation

Chapter 18: Query Parsing, Planning and Cost Models

1. The Parsing Pipeline: Lexing to Logical Plans
2. Logical Optimization: Predicates Pushdown and Join Reordering
3. Cost Models: Selectivity, Cardinality and I/O Estimates
4. Statistics Collection and Histograms
5. Plan Cache and Parameter Sniffing

Chapter 19: Join Algorithms and Their Tradeoffs

1. Nested Loop Joins: Simple and Index-Assisted
2. Hash Joins: Build, Probe and Spill
3. Merge Joins: Sorted Inputs and External Merge
4. Join Order Optimization and Join Trees
5. Skew and Anti-Patterns

Chapter 20: Aggregation, Sorting and Window Functions

1. Hash Aggregation and Sort Aggregation
2. External Sorting: Tiling and Merging
3. Window Functions: Frame Semantics and Memory
4. Approximate Aggregations and Sketches
5. Materialized Views and Incremental Maintenance

Chapter 21: Benchmarking Methodologies and Workload Characterization

1. Benchmark Design: Microbenchmarks vs End-to-End
2. Workload Generation: TPC, YCSB and Custom Patterns
3. Warmup, Steady State and Run Length
4. Controlling Variables and Reproducibility
5. Common Benchmark Fallacies and How to Avoid Them

Chapter 22: Profiling, Tracing and Observability

1. CPU Profiling: Flame Graphs and Sampling
2. I/O Tracing: blktrace, iostat and SystemTap
3. Database-Specific Tracing and Logs
4. eBPF and Kernel-Level Observability
5. Metrics Architecture and Alerting

Chapter 23: Diagnosing and Resolving Performance Problems

1. The Diagnostic Methodology: Symptom to Hypothesis
2. Slow Queries and Bad Plans
3. Lock Contention and Blocking Chains
4. Memory Pressure and Swap
5. Disk Saturation and I/O Bottlenecks

Chapter 24: Designing for Performance from the Start

1. Schema Design for Performance: Normalization vs Denormalization
2. Indexing Strategy: Types, Selectivity and Maintenance Cost
3. Partitioning and Sharding Strategies
4. Caching Layers: In-Memory, Query and Application-Level
5. Connection Management and Pooling

Chapter 25: Browser Architecture and Automation Fundamentals

1. Browser Internals: Rendering, Compositing and Scripting
2. The DOM and Accessibility Trees
3. DevTools Protocol: Commands, Events and Streaming
4. Headless Browsers: Chromium, Firefox and Isolation
5. Browser Extensions as Automation Vectors

Chapter 26: AI-Powered Browser Agents

1. Agent Architecture: Perceive, Plan, Act, Observe
2. DOM as an Environment for Agents
3. Tool Calling and Function Execution
4. Multi-Agent Orchestration and Task Decomposition
5. Reliability Patterns: Retry, Verification and Rollback

Chapter 27: Web Automation Frameworks and Data Extraction

1. Playwright, Selenium and Puppeteer: Architecture and Tradeoffs
2. DOM Scraping and Structured Extraction
3. Handling Dynamic Content and JavaScript-Rendered Pages
4. Authentication, Sessions and Browser Contexts
5. Rate Limiting, Fingerprinting and Ethics

Chapter 28: Integrating Browser Agents with Database Systems

1. Architecture Patterns: Agent, API and Database
2. Data Extraction Pipelines to Database Storage
3. Transaction Boundaries and Atomicity
4. Authentication, Authorization and Security
5. Monitoring, Logging and Operational Concerns

Chapter 29: Production Workflows and End-to-End Implementations

1. Complete Reference Implementation: Architecture Overview
2. Agent Configuration and Prompt Engineering
3. Database Schema Design for Agent Data
4. Error Handling, Recovery and Idempotency
5. Performance Tuning the Full Stack

Chapter 30: Synthesis and Future Directions

1. Key Principles Summarized
2. Where Databases Are Heading: HTAP, Vector and Beyond
3. Persistent Memory and New Storage Paradigms
4. AI-Driven Database Management
5. The Engineer’s Checklist for Database Performance

Conclusion

References