The Life of a PostgreSQL Table, Part 1 with Bruce Momjian

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.

Topics include:

• Table creation: physical storage, constraints, and why data types matter
• Storage internals: 8KB pages, TOAST, fill factors, and how data is physically laid out
• Data insertion: write-ahead logging, transaction visibility, and durability guarantees
• Index types and their use cases: B-tree, BRIN, GiST, GIN, SP-GiST and when to use each
• Multi-Version Concurrency Control (MVCC): how Postgres handles concurrent reads and writes
• Query planning: statistics, costs, and how Postgres chooses access methods
• Join strategies: nested loops, hash joins, merge joins and when each is optimal
• The importance of constraints and how they protect data integrity
• Sequences, transaction visibility, and dealing with concurrent operations

Bruce 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.

Show Links

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