Bruce Momjian returns to complete our exploration of PostgreSQL internals through a table’s lifecycle. From updates and alterations to deletions and drops, we dive deep into how Postgres manages data changes, maintains consistency, and handles clean-up operations, revealing the intricate mechanisms that make PostgreSQL both powerful and reliable.

We examine how Postgres manages everything from transaction isolation to reclaiming space. Bruce explains how MVCC enables concurrent updates, how table alterations affect performance, and how the Write-Ahead Log ensures durability whilst enabling features like point-in-time recovery and replication.

Topics include:

Updates: MVCC, heap-only tuples, and how Postgres handles concurrent modifications
Transaction isolation levels: read committed, repeatable read, and serializable
Table alterations: lightweight vs heavyweight changes, column additions, and type changes
Row deletion: transaction visibility, reclaiming space, and foreign key cascades
Vacuum operations: regular vacuum vs vacuum full, and auto-vacuum scheduling
Write-Ahead Log (WAL): crash recovery, point-in-time recovery, and replication
Table drops: dependency tracking and cleanup operations
Concurrency control: row-level locking, deadlock detection, and conflict resolution
The importance of indexing foreign key columns for performance
How Postgres maintains durability through careful write ordering

Bruce also shares insights into performance optimisation, operational considerations, and the evolution of PostgreSQL’s features over time.

This is Part 2 of a 2-part series. In Part 1, we explored table creation, data insertion, and query execution in PostgreSQL.

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:

The Rise of State Violence in the United States

Assembly Required with Stacey Abrams

How Is the Administration Expanding Executive Power?

The AI-powered Podcast Player