Bruce Momjian

Postgres Internals Presentations


PostgreSQL Internals Through Pictures

Duration: 3 hours, 4 hours with questions
This talk is designed for advanced PostgreSQL users who want to know more about how the database internally processes a query.  It is also ideal for people wanting to modify the PostgreSQL server source code.  It covers PostgreSQL network communication, query parsing, optimizer plan selection, multiversion concurrency control (MVCC), and internal storage characteristics.  It does not require any programming knowledge.

Unlocking the Postgres Lock Manager

Duration: 45 minutes, 60 minutes with questions
Locking is critical for providing high concurrency for any database — you cannot fully utilize your hardware if locking is throttling its use. This talk explores all aspects of locking in Postgres by showing queries and their locks; covered lock types include row, table, shared, exclusive, and advisory lock types. The high concurrency provided by Multiversion Concurrency Control (MVCC) is also covered.

Inside PostgreSQL Shared Memory

Duration: 30 minutes, 45 minutes with questions
This talk is for people who want to understand how PostgreSQL shares information among processes using shared memory. The talk covers the internal data page format, usage of the shared buffers, locking methods, and various other shared memory data structures.

MVCC Unmasked

Duration: 60 minutes, 75 minutes with questions
Multi-Version Concurrency Control (MVCC) allows Postgres to offer high concurrency even during significant database read/write activity. MVCC specifically offers behavior where "readers never block writers, and writers never block readers". This talk explains how MVCC is implemented in Postgres and highlights optimizations which minimize the downsides of MVCC. This talk is for advanced users.

Explaining the Postgres Query Optimizer

Duration: 75 minutes, 90 minutes with questions
The optimizer is the "brain" of the database, interpreting SQL queries and determining the fastest method of execution. This talk uses the explain command to show how the optimizer interprets queries and determines optimal execution. The talk will assist developers and administrators in understanding how Postgres optimally executes their queries and what steps they can take to understand and perhaps improve its behavior.