Skip to Content
Home └Database System Internals (PostgreSQL)
Database System Internals (PostgreSQL)

Database System Internals (PostgreSQL)

Most database courses teach you how to talk to a database. This course teaches you how the database talks to the hardware. Database System Internals (PostgreSQL) is a rigorous, comprehensive analysis into the physical architecture and execution pipeline of the world's most advanced open-source relational database. Moving beyond logical design and SQL syntax, this course strips away the abstraction layers to explore how PostgreSQL parses queries, estimates costs, manipulates memory caching, enforces concurrency without locking, and guarantees data durability.
Join this Course
Responsible Mark Shamy
Last Update 04/29/2026
Completion Time 9 hours 25 minutes
Members 1
Academic Under Construction Computer Science
  • Resources (empty)
    3Lessons · 10 min
  • Chapter 1: The Anatomy of PostgreSQL: Architecture and Process Model (empty)
    6Lessons · 15 min
  • Chapter 2: The Journey of a Query: Lexing, Parsing, and the Traffic Cop (empty)
    5Lessons ·
    • 2.0: Overview & Readings
      Preview
    • 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 (empty)
    5Lessons ·
    • 3.0 Overview & Readings
      Preview
    • 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 (empty)
    6Lessons ·
    • 4.0: Overview & Readings
      Preview
    • 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 (empty)
    7Lessons ·
    • 5.0: Overview & Readings
      Preview
    • 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 (empty)
    6Lessons ·
    • 6.0: Overview & Readings
      Preview
    • 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 (empty)
    6Lessons ·
    • 7.0: Overview & Readings
      Preview
    • 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 (empty)
    8Lessons ·
    • 8.0: Overview & Readings
      Preview
    • 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 (empty)
    7Lessons ·
    • 9.0: Overview & Readings
      Preview
    • 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 (empty)
    6Lessons ·
    • 10.0: Overview & Readings
      Preview
    • 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 (empty)
    6Lessons ·
    • 11.0: Overview & Readings
      Preview
    • 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 (empty)
    7Lessons ·
    • 12.0: Overview & Readings
      Preview
    • 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 (empty)
    6Lessons ·
    • 13.0: Overview & Readings
      Preview
    • 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 (empty)
    3Lessons · 9 hr