Database System Internals (PostgreSQL)

Resources
Chapter 1: The Anatomy of PostgreSQL: Architecture and Process Model
- 1.0: Overview & Readings
- 1.1: The Client/Server Model and Postmaster
  - Quiz
- 1.2: Shared Memory Architecture
  - Quiz
- 1.3: Core Background Workers
- 1.4: Utility Processes
- 1.5: The Backend Process
Chapter 2: The Journey of a Query: Lexing, Parsing, and the Traffic Cop
- 2.0: Overview & Readings
- 2.1: The Query Lifecycle Overview
- 2.2: Lexical Analysis
- 2.3: Analyzing the Parse Tree
- 2.4: Semantic Analysis
Chapter 3: The PostgreSQL Rule System and Query Rewriting
- 3.0 Overview & Readings
- 3.1: Rules vs Triggers
- 3.2: The Query Tree Structure
- 3.3: Views Under the Hood
- 3.4: The Rewriter in Action
Chapter 4: The Query Planner Part I: Statistics and Cost Estimation
- 4.0: Overview & Readings
- 4.1: The System Catalogs
- 4.2: The Role of ANALYZE
- 4.3: Understanding pg_statistic
- 4.4: Cost Constants
- 4.5: Calculating Cost
Chapter 5: The Query Planner Part II: Path Generation and GEQO
- 5.0: Overview & Readings
- 5.1: Path Generation Basics
- 5.2: Index Scan Paths
- 5.3: Join Strategies I
- 5.4: Join Strategies II
- 5.5: Dynamic Programming in the Planner
- 5.6: Genetic Query Optimization (GEQO)
Chapter 6: The Executor: Processing the Plan Tree
- 6.0: Overview & Readings
- 6.1: The Volcano Execution Model
- 6.2: Executor Phases
- 6.3: Scan Nodes
- 6.4: Join and Materialize Nodes
- 6.5: Aggregate and Sort Nodes
Chapter 7: Advanced Indexing Under the Hood
- 7.0: Overview & Readings
- 7.1: B-Tree Internals
- 7.2: GiST Indexes
- 7.3: GIN Indexes
- 7.4: BRIN Indexes
- 7.5: Operator Classes
Chapter 8: Multiversion Concurrency Control (MVCC) and Vacuuming
- 8.0: Overview & Readings
- 8.1: The MVCC Concept
- 8.2: Tuple Header Anatomy
- 8.3: Snapshot Isolation
- 8.4: Transaction ID (XID) Wraparound
- 8.5: The VACUUM Process
- 8.6: Autovacuum Tuning
- 8.7: Assignment Reminder
Chapter 9: Memory Management and Caching Strategies
- 9.0: Overview & Readings
- 9.1: The Dual Caching Model
- 9.2: The Buffer Manager
- 9.3: Eviction Policies
- 9.4: Local Memory (work_mem)
- 9.5: Maintenance Memory
- 9.6: Assignment Reminder
Chapter 10: The Write-Ahead Log (WAL) and Crash Recovery
- 10.0: Overview & Readings
- 10.1: The Purpose of WAL
- 10.2: WAL Physical Structure
- 10.3: Physiological Logging
- 10.4: Checkpoints
- 10.5: Crash Recovery Mechanics
Chapter 11: Replication: Physical and Logical
- 11.0: Overview & Readings
- 11.1: Physical Streaming Replication
- 11.2: WalSender and WalReceiver
- 11.3: Replication Slots
- 11.4: Logical Replication Basics
- 11.5: Logical Decoding
Chapter 12: Distributed PostgreSQL and Sharding
- 12.0: Overview & Readings
- 12.1: Declarative Partitioning
- 12.2: Partition Pruning
- 12.3: Foreign Data Wrappers (FDW)
- 12.4: Parallel Query Execution
- 12.5: Introduction to Sharding
- 12.6: Assignment Reminder
Chapter 13: Extending the Engine
- 13.0: Overview & Readings
- 13.1: Extensibility Architecture
- 13.2: Custom Data Types
- 13.3: Background Worker Processes
- 13.4: Extension Hooks
- 13.5: Packaging Extensions
Project Based Assignments