Bruce Momjian returns to explore the internals of PostgreSQL by way of a table’s lifecycle.
From creation through insertion to querying, we dive deep into how Postgres actually implements tables, indexes, and queries under the hood, revealing why understanding these internals helps you make better decisions as a database user.
We examine how Postgres handles everything from physical storage to transaction visibility.
Bruce explains why constraints are critical for data integrity, how MVCC enables consistent reads without blocking writes, and how the query planner makes decisions about accessing your data.
Table creation: physical storage, constraints, and why data types matterStorage internals: 8KB pages, TOAST, fill factors, and how data is physically laid outData insertion: write-ahead logging, transaction visibility, and durability guaranteesIndex types and their use cases: B-tree, BRIN, GiST, GIN, SP-GiST and when to use eachMulti-Version Concurrency Control (MVCC): how Postgres handles concurrent reads and writesQuery planning: statistics, costs, and how Postgres chooses access methodsJoin strategies: nested loops, hash joins, merge joins and when each is optimalThe importance of constraints and how they protect data integritySequences, transaction visibility, and dealing with concurrent operationsBruce also shares insights into why certain design decisions were made, how they compare to other databases, and practical tips for working with Postgres effectively.
This is Part 1 of a 2-part series. In Part 2, we’ll explore the remaining lifecycle of a PostgreSQL table - including updating rows, altering table structure, row deletion, and finally dropping tables.
Bruce generously shared links to all the presentations (with direct references to the discussed slides) and blog articles mentioned in the episode. You’ll find them below:
Bruce Momjian’s Website
Presentation: Postgres Scaling Opportunities (Horizontal Database Scaling)
Presentation: Making Postgres Central in Your Data Center (How Is this Accomplished?)
Presentation: Making Postgres Central in Your Data Center (Why Extensibility Matters Today)
Presentation: PostgreSQL Internals Through Pictures
Bruce’s Blog: Toast-y Goodness
Presentation: Flexible Indexing with Postgres (Partial Indexes)
Presentation: Flexible Indexing with Postgres (Index Type Summary)
Presentation: Flexible Indexing with Postgres (When To Create Indexes)
Presentation: Non-Relational Postgres (JSONB)
Presentation: Database Hardware Selection Guideline (Magnetic Disk I/O Stack)
Bruce’s Blog: When Does a Commit Happen?
Presentation: MVCC Unmasked (Aborted IDs Remain)
Presentation: MVCC Unmasked (Snapshots Determine Row Visibility)
Presentation: MVCC Unmasked (Traditional Cleanup Requirements)
Presentation: Unlocking the Postgres Lock Manager (Two Concurrent Updates Show Locking)
Presentation: Explaining the Postgres Query Optimizer (Running ANALYZE)
Presentation: Explaining the Postgres Query Optimizer (Which Join Method?)
Bruce’s Blog: The Externality of Index Creation
Bruce’s Blog: Optimizer Hints
Three Devs and a Maybe - Postgres Performance Tuning and Query Planner
Three Devs and a Maybe - Managing Concurrency in Postgres