🌟 PostgreSQL Uncovered: Internals, Trace Analysis, and Performance

A step-by-step journey to help you understand how PostgreSQL works from the inside out.

🎓 About the Course

This course is for developers and anyone curious about what goes on behind the scenes in PostgreSQL. You’ll start with simple SQL queries and gradually explore how PostgreSQL works under the hood: B+ trees, shared buffers, WAL (Write-Ahead Logging), MVCC, vacuum, transactions, the planner, and extensions.

You'll learn not just what PostgreSQL does, but why it does it that way.

🚀 Extra Benefits

This is the standard package for the full learning experience:

💬 Private Discord Community
Join a members-only space where you can ask questions and share insights.

📨 Discord Access
After purchasing the course, you’ll receive a Discord invite by email (usually within 3 days). Please check your inbox carefully.
If you haven’t received it, contact wangbin579@gmail.com.

🔍 PostgreSQL Trace Tool
Get access to a high-performance tracing program built on a specific PostgreSQL version. Use it to explore how SQL statements are parsed, optimized, and executed in real time.

📌 Note: The download link for the trace tool is available in the Discord #downloads channel.

📚 Course Outline

PostgreSQL Uncovered: Internals, Trace Analysis, and Performance

1. Why PostgreSQL Is More Than Just Data Storage

2. Inside PostgreSQL: Unlocking Secrets with Trace Analysis

3. How a SQL Query Is Executed in PostgreSQL

4. PostgreSQL Architecture: Inside the Database Engine

5. PostgreSQL Indexes: Deep Dive for Developers

6. Why PostgreSQL Uses Heap Storage

7. How PostgreSQL Traverses B+ Trees to Locate Rows

8. B+ Tree Depth in PostgreSQL and Its Performance Impact

9. Index-Only Scans in PostgreSQL: When Tables Are Skipped

10. Shared Buffers Explained: What PostgreSQL Cache Internally

11. Understanding WAL in PostgreSQL: The Write-Ahead Log

12. Inside PostgreSQL Writes: From Shared Buffers to Disk

13. Why Doesn’t PostgreSQL Use Undo Logs for MVCC?

14. MVCC Internals: How PostgreSQL Keeps Old Versions Alive

15. Autovacuum in PostgreSQL: How It Works and When It Fails

16. PostgreSQL VACUUM: Understanding Performance, Benefits, and Costs

17. System Catalogs: The Metadata Control Center of a Database

18. Inside PostgreSQL: How Transactions Really Work

19. PostgreSQL’s Hidden Strength: Transactional DDL

20. How PostgreSQL Ensures Atomicity Using MVCC, CLOG, and WAL

21. Data Consistency in PostgreSQL: Constraints and Guarantees

22. PostgreSQL Isolation Levels: A Practical Guide

23. Same Isolation Level, Different Behavior: PostgreSQL vs MySQL

24. Durability in PostgreSQL: How Crashes Don’t Lose Data

25. Why ROLLBACK Is So Fast in PostgreSQL

26. Checkpoints in PostgreSQL: What They Are and Why They Matter

27. Transaction ID Wraparound: Why It Happens and How to Handle It

28. PostgreSQL Locks: A Simple Guide to Types and Use Cases

29. Do Read-Only Queries Really Need VACUUM in PostgreSQL?

30. Inside PostgreSQL’s Planner: From Query to Logical Plan

31. Inside PostgreSQL’s Cost Model: How the Planner Thinks

32. How JOINs Work Internally in PostgreSQL

33. Join Tree Structures in Query Optimization

34. Join Enumeration Strategies: Dynamic Programming vs Genetic Search

35. How PostgreSQL Reduces the Join Search Space

36. Understanding Join Graph Topology and Its Impact on Query Optimization

37. Why PostgreSQL Handles This JOIN 10× Faster Than MySQL

38. PostgreSQL Aggregate Functions: Execution Models and Internal Algorithms

39. PostgreSQL Parallel Query Execution: A Key to Faster Analytics

40. WITH Queries Demystified: How PostgreSQL Plans CTEs

41. How Databases Execute Window Functions: A Deep Dive

42. Why PostgreSQL Query Optimization Is So Challenging

43. Planner Challenges in Distributed PostgreSQL

44. What PostgreSQL Extensions Are and How They Work

45. Representative PostgreSQL Extensions and Their Capabilities

46. How pg_trgm Speeds Up Fuzzy Text Search

47. Understanding Hstore’s Extension Mechanism in PostgreSQL

48. Inside PostgreSQL Extensions: How FDW Works Under the Hood

49. Inside PostgreSQL Extensions: How Citus Works Under the Hood

50. How Citus Teaches the Planner to Think Globally

51. How Citus Handles Joins Internally

52. Citus Transactions Uncovered: How It Really Works

53. How Extensions Shape PostgreSQL Planning and Execution

54. PostgreSQL Extensions: How They Affect Performance and Stability

55. Why Hot Updates to a Single Row Can Severely Impact Its Read Performance

56. Why Long Transactions May Kill Your PostgreSQL Performance

57. Why Performance Degrades Quickly in Long-Running TPC-C Tests

58. Trace Analysis: Understanding Its Power and Limitations

59. Migrating from MySQL to PostgreSQL: Key Differences You Need to Know

60. Becoming a PostgreSQL Expert: Roadmap to Rapid Growth

📌 Important Notes

🗓️ Overall, the course took seven months to complete: four months to develop the trace tool and three months to create the course content.

💬 If you’re unsure about purchasing, feel free to join our Discord community (https://discord.gg/g8ksTXPPnY) to ask questions and check out sample lessons.

📚 Current course content is the first version, which contains 60 lessons. The second version is expected to be released on July 1, 2026, with about 72 lessons, mainly adding content on extensions.

💡 MySQL Course: MySQL Uncovered: Internals, Trace Analysis, and Performance can be found here.

⚠️ Please note: This is a digital product with instant access upon purchase. All sales are final and non-refundable.