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.
- FOSSSL, September 10, 2005
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.
- PGDay NYC 2013, March 22, 2013
- PGCon 2012, May 17, 2012
- PG Corridor Days DC, March 30, 2012
- Boston PostgreSQL Users Group (BPUG), January 19, 2012
- Postgres Open, September 16, 2011 (video)
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.
- Postgres Open, September 18, 2013 (video)
- PostgreSQL Conference Europe 2012, October 25, 2012
- New York Capital District PostgreSQL Users Group (NYCDPUG), March 4, 2010
- Tsinghua University, November 25, 2009
- PostgreSQL Conference: West 08, October 11, 2008 (video)
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.
- National Research Nuclear University (MEPhI), October 22, 2013
- HighLoad++, October 22, 2012 (video)
- Southeast LinuxFest 2012, June 8, 2012
- PG Brazil Conference, November 3, 2011
- O'Reilly Open Source Data Convention, July 25, 2011 (video)
- PG Day at the O'Reilly Open Source Convention, July 24, 2011 (video)
- International Free Software Forum, July 1, 2011
- EnterpriseDB Webcast, June 8, 2011 (video, registration required)
- PGCon 2011, May 19, 2011
- PgEast 2011, March 23, 2011
- Italian PostgreSQL Day, December 10, 2010
- PGDay Europe 2010, December 6, 2010
- PgWest 2010, November 3, 2010
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.