CF

PostgreSQL Uncovered: Internals, Trace Analysis, and Performance

0h 0m 0s
English
Paid
Added to CourseFlix

PostgreSQL Uncovered: Internals, Trace Analysis, and Performance is a self-paced course by Bin Wang. Explore the inner workings of PostgreSQL with this comprehensive course designed for developers, database administrators, and anyone keen to understand what happens "under the hood" of this powerful DBMS.

Course facts

Language
English
Added to CourseFlix
March 2026
Instructor
Bin Wang
Price
Premium

Explore the inner workings of PostgreSQL with this comprehensive course designed for developers, database administrators, and anyone keen to understand what happens "under the hood" of this powerful DBMS.

Course Overview

Beginning with familiar SQL queries, this course will guide you step by step into the internal mechanics of PostgreSQL. You will gain insight into:

  • The functionality of the query planner
  • Structure and organization of transactions and isolation
  • The mechanics of MVCC (Multi-Version Concurrency Control)
  • The role and operation of B+Tree indexes
  • Management of shared buffers
  • The importance and process of WAL (Write-Ahead Logging)
  • Understanding vacuum and autovacuum processes
  • Checkpoints and replication
  • And many other critical PostgreSQL mechanisms

Theory and Practice

The course offers a blend of theoretical knowledge and hands-on practice, enabling you to:

  • Read and interpret EXPLAIN ANALYZE statements in detail (BUFFERS, VERBOSE, TIMING)
  • Utilize tools like gdb and perf to analyze traces
  • Understand and interpret PostgreSQL logs
  • Identify and resolve performance bottlenecks in complex scenarios

Outcomes

Upon completing the course, PostgreSQL will no longer be a "black box". You'll have a clear understanding of why queries behave as they do and how to optimize them efficiently at the core DBMS level.

Who teaches PostgreSQL Uncovered: Internals, Trace Analysis, and Performance? Bin Wang

Bin Wang thumbnail

Bin Wang is a database engineer and educator publishing deep technical material on the internals of MySQL and PostgreSQL — the two most-used open-source relational databases in the modern web stack. His material is unusually rigorous for the YouTube tutorial market, going into the actual implementation, on-disk format, and trace-level performance analysis of these systems.

His CourseFlix listing carries two Bin Wang courses: Explore MySQL — A Beginner's Journey into Database Internals and PostgreSQL Uncovered — Internals, Trace Analysis, and Performance. Material is paid and aimed at engineers ready to treat the database as a first-class object of study.

Get all 60 books with Premium

From $6.67/mo. One payment unlocks every premium course. No auto-renew.

See plans

Books

Read Book PostgreSQL Uncovered: Internals, Trace Analysis, and Performance

#TitleTypeOpen
1Lesson 1. Why PostgreSQL Is More Than Just Data Storage —Unlock
2Lesson 2. Inside PostgreSQL Unlocking Secrets with Trace Analysis —Unlock
3Lesson 3. How a SQL Query Is Executed in PostgreSQL —Unlock
4Lesson 4. PostgreSQL Architecture Inside the Database Engine —Unlock
5Lesson 5. PostgreSQL Indexes Deep Dive for Developers —Unlock
6Lesson 6. Why PostgreSQL Uses Heap Storage —Unlock
7Lesson 7. How PostgreSQL Finds Rows in a B+ Tree_A Step-by-Step Example —Unlock
8Lesson 8. B+ Tree Depth in PostgreSQL and Its Performance Impact —Unlock
9Lesson 9. Index-Only Scans in PostgreSQL When Tables Are Skipped —Unlock
10Lesson 10. Shared Buffers Explained What PostgreSQL Caches Internally —Unlock
11Lesson 11. Understanding WAL in PostgreSQL The Write-Ahead Log —Unlock
12Lesson 12. Inside PostgreSQL Writes From Shared Buffers to Disk —Unlock
13Lesson 13. Why Doesn’t PostgreSQL Use Undo Logs for MVCC —Unlock
14Lesson 14. MVCC Internals_How PostgreSQL Keeps Old Versions Alive —Unlock
15Lesson 15. Autovacuum in PostgreSQL How It Works and When It Fails —Unlock
16Lesson 16. PostgreSQL VACUUM Understanding Performance, Benefits, and Costs —Unlock
17Lesson 17. System Catalogs The Metadata Control Center of a Database —Unlock
18Lesson 18. Inside PostgreSQL How Transactions Really Work —Unlock
19Lesson 19. PostgreSQL’s Hidden Strength Transactional DDL —Unlock
20Lesson 20. How PostgreSQL Ensures Atomicity Using MVCC, CLOG, and WAL —Unlock
21Lesson 21. Data Consistency in PostgreSQL_Constraints and Guarantees —Unlock
22Lesson 22. Isolation Levels in PostgreSQL_A Practical Introduction —Unlock
23Lesson 23. Same Isolation Level, Different Behavior PostgreSQL vs MySQL —Unlock
24Lesson 24. Durability in PostgreSQL_How Crashes Don’t Lose Data —Unlock
25Lesson 25. Why ROLLBACK Is So Fast in PostgreSQL —Unlock
26Lesson 26. Checkpoints in PostgreSQL_What They Are and Why They Matter —Unlock
27Lesson 27. Transaction ID Wraparound Why It Happens and How to Handle It —Unlock
28Lesson 28. Locks in PostgreSQL_An Easy Guide to Types and Use Cases —Unlock
29Lesson 29. Do Read-Only Queries Really Need VACUUM in PostgreSQL —Unlock
30Lesson 30. Inside PostgreSQL’s Planner_From Query to Logical Plan —Unlock
31Lesson 31. Inside PostgreSQL’s Cost Model How the Planner Thinks —Unlock
32Lesson 32. How JOINs Work Internally in PostgreSQL —Unlock
33Lesson 33. Join Tree Structures in Query Optimization —Unlock
34Lesson 34. Join Enumeration Strategies Dynamic Programming vs Genetic Search —Unlock
35Lesson 35. How PostgreSQL Reduces the Join Search Space —Unlock
36Lesson 36. Understanding Join Graph Topology and Its Impact on Query Optimization —Unlock
37Lesson 37. Why PostgreSQL Handles This JOIN 10× Faster Than MySQL —Unlock
38Lesson 38. How PostgreSQL Aggregates Work Internally —Unlock
39Lesson 39. PostgreSQL Parallel Query Execution The Key to Faster Analytics —Unlock
40Lesson 40. WITH Queries Demystified How PostgreSQL Plans CTEs —Unlock
41Lesson 41. How Databases Execute Window Functions A Deep Dive —Unlock
42Lesson 42. Why PostgreSQL Query Optimization Is So Challenging —Unlock
43Lesson 43. Planner Challenges in Distributed PostgreSQL —Unlock
44Lesson 44. What PostgreSQL Extensions Are and How They Work —Unlock
45Lesson 45. Representative PostgreSQL Extensions and Their Capabilities —Unlock
46Lesson 46. Understanding Hstore’s Extension Mechanism in PostgreSQL —Unlock
47Lesson 47. How pg_trgm Speeds Up Fuzzy Text Search —Unlock
48Lesson 48. Inside PostgreSQL Extensions_How FDW Works Under the Hood —Unlock
49Lesson 49. Inside PostgreSQL Extensions_How Citus Works Under the Hood —Unlock
50Lesson 50. How Citus Teaches the Planner to Think Globally —Unlock
51Lesson 51. How Citus Handles Joins Internally —Unlock
52Lesson 52. Citus Transactions Uncovered How It Really Works —Unlock
53Lesson 53. How Extensions Shape PostgreSQL Planning and Execution —Unlock
54Lesson 54. PostgreSQL Extensions_How They Affect Performance and Stability —Unlock
55Lesson 55. Why Hotspot Updates Hurt PostgreSQL Performance —Unlock
56Lesson 56. Why Long Transactions May Kill Your PostgreSQL Performance —Unlock
57Lesson 57. Why Performance Degrades Quickly in Long-Running TPC-C Tests —Unlock
58Lesson 58. Trace Analysis_Understanding Its Power and Limitations —Unlock
59Lesson 59. Migrating from MySQL to PostgreSQL Key Differences You Need to Know —Unlock
60Lesson 60. Becoming a PostgreSQL Expert_Roadmap to Rapid Growth —Unlock

What courses are similar to PostgreSQL Uncovered: Internals, Trace Analysis, and Performance?

More courses by Bin Wang

Frequently asked questions

What prerequisites are needed before taking this course?
This course is designed for developers and database administrators who already have a basic understanding of SQL. Familiarity with PostgreSQL is beneficial, but not strictly necessary, as the course begins with familiar SQL queries and expands into deeper PostgreSQL internals.
What will I be able to build or understand by the end of the course?
By the end of the course, you will have a thorough understanding of PostgreSQL's internal mechanisms. You'll be able to read and interpret EXPLAIN ANALYZE statements in detail, utilize tools like gdb and perf for trace analysis, and identify and resolve performance bottlenecks in complex scenarios.
Who is the ideal target audience for this course?
The course is ideal for developers, database administrators, and anyone interested in gaining a deep understanding of PostgreSQL's internal workings. It caters to those who want to go beyond basic SQL queries and delve into the mechanics of the DBMS.
How does the depth of this course compare to other PostgreSQL courses?
This course offers a detailed exploration of PostgreSQL internals, beyond the typical SQL query tutorials. It covers advanced topics such as query planner functionality, MVCC mechanics, B+Tree indexes, and more, providing insights that are not typically covered in introductory PostgreSQL courses.
What specific tools or platforms will I learn to use in this course?
The course includes practical instruction on using tools such as gdb and perf for trace analysis, as well as techniques for interpreting PostgreSQL logs. These tools will be crucial for understanding and optimizing PostgreSQL performance.
What topics are not covered in this course?
The course does not cover introductory SQL queries or basic database management concepts in detail. It assumes that participants already have a foundational understanding of these areas and focuses instead on the in-depth mechanics and performance aspects of PostgreSQL.
How can the skills gained in this course be applied to a career?
Skills gained from this course can significantly enhance a career in database administration or development. Understanding PostgreSQL at a deep level enables professionals to optimize database performance and solve complex issues efficiently, making them valuable assets in any data-driven environment.