Row-Level Locks, Deadlocks, and Select FOR UPDATE

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:

• Bruce Momjian’s Website
• Presentation: MVCC Unmasked (Aborted IDs Remain)
• Presentation: MVCC Unmasked (Traditional Cleanup Requirements)
• Presentation: Unlocking the Postgres Lock Manager (Deadlocks)
• Presentation: Mastering PostgreSQL Administration (Table Activity)
• Presentation: Mastering PostgreSQL Administration (Memory Usage)
• Presentation: Mastering PostgreSQL Administration (Write-Ahead Logging)
• Bruce’s Blog: Controlling Autovacuum
• PostgreSQL Documentation: ALTER TABLE
• PostgreSQL Documentation: Dependency Tracking
• PostgreSQL Documentation: Continuous Archiving and Point-in-Time Recovery
• Three Devs and a Maybe - Postgres Performance Tuning and Query Planner
• Three Devs and a Maybe - Managing Concurrency in Postgres