Advanced

Visualized Advanced Databases

Season 1 · Inside a Database Engine

How a database engine really works: pages and tuples, the buffer pool, B+ trees and LSM trees, joins, the query optimizer, locking, MVCC and crash recovery. Every mechanism is animated from a small simulation, then proven with real Postgres output.

45 episodes · 6.6 hours · SQL · Postgres

Start with Episode 0 →

Module 0 · Orientation

3 episodes · 25 min
  1. 00 Visual Dictionary 6:58
  2. 01 The Life of a Query 9:49
  3. 02 The Memory Hierarchy 7:47

Module 1 · Storage

7 episodes · 59 min
  1. 03 Files, Pages and Heap Files 9:50
  2. 04 Slotted Pages 7:51
  3. 05 Tuple Layout 7:51
  4. 06 Row vs. Column vs. PAX 7:12
  5. 07 The Buffer Pool 9:16
  6. 08 Replacement Policies 9:42
  7. 09 Lab: I Deleted Half the Table and It Got Bigger 7:35

Module 2 · Indexes

9 episodes · 1 h 22 min
  1. 10 B+ Trees: Search and Insert 8:49
  2. 11 B+ Trees: Delete 9:59
  3. 12 B+ Trees in Practice 9:41
  4. 13 Concurrent B+ Trees 10:00
  5. 14 Hash Indexes 9:57
  6. 15 LSM Trees 9:28
  7. 16 Compaction 9:43
  8. 17 Bloom Filters 7:39
  9. 18 Lab: The Index That Wasn't Used 7:05

Module 3 · Query execution

7 episodes · 1 h 0 min
  1. 19 Processing Models 9:46
  2. 20 Access Paths 9:18
  3. 21 External Merge Sort 8:38
  4. 22 Joins I: Nested Loops 7:45
  5. 23 Joins II: Sort-Merge and Hash 10:03
  6. 24 Aggregation 7:47
  7. 25 Lab: The Query That Spilled to Disk 7:04

Module 4 · Query optimization

6 episodes · 52 min
  1. 26 From SQL to Relational Algebra 8:59
  2. 27 Statistics and Cost Models 9:15
  3. 28 Cardinality Estimation 8:26
  4. 29 Join Ordering 9:57
  5. 30 Reading EXPLAIN 8:12
  6. 31 Lab: Fast Yesterday, Slow Today 7:05

Module 5 · Transactions and concurrency control

9 episodes · 1 h 21 min
  1. 32 ACID and Schedules 9:25
  2. 33 The Anomalies 9:38
  3. 34 Serializability 8:26
  4. 35 Two-Phase Locking 10:15
  5. 36 Deadlocks 7:48
  6. 37 Timestamp Ordering and OCC 8:35
  7. 38 MVCC 9:45
  8. 39 Isolation Levels in Real Life 8:40
  9. 40 Lab: Two Users Bought the Last Ticket 8:00

Module 6 · Logging and recovery

3 episodes · 27 min
  1. 41 Write-Ahead Logging 10:17
  2. 42 Checkpoints and ARIES 10:04
  3. 43 Lab: Pull the Plug 7:06

Season 1 finale

1 episode · 7 min
  1. 44 Season Finale: The Life of a Query, Revisited 7:09