The Part Of PostgreSQL I Hate The Most: MVCC and How To Optimize It

The Part Of PostgreSQL I Hate The Most: MVCC and How To Optimize It


March 7, 2024    
6:00 pm - 8:00 pm


2201 6th Ave, Seattle, WA

Event Organizer

Seattle Postgres User Group

Postgres is awesome. It is currently the fourth most popular database (source: DB-Engines), and its popularity continues to grow as strong as ever due to several factors: its rich set of features, mixing object storage and relational capabilities; its extensibility; and its status as an open source project. But as much as we love PostgreSQL, there are certain aspects about it that are not that great. So instead of talking about the awesomeness of everyone’s favorite elephant-themed DBMS, I want to discuss the one part about it that sucks: how PostgreSQL implements multi-versioning. Our research at Carnegie Mellon University and experience with optimizing PostgreSQL databases for our customers have shown that their design and implementation of MVCC can cause severe performance issues for some workloads, requiring effective database administration to achieve good performance.

In this talk, I will discuss the following: (1) the implementation of MVCC in Postgres and the issues caused by its design (e.g., write amplification, table bloat, autovacuum), (2) how to optimize Postgres to address those issues with domain knowledge and machine learning (e.g., heuristic-based and ML-based autovacuum tuning, identifying long-running transactions, etc.), (3) the internals of autovacuum, its potential challenges, and effective methods for tuning autovacuum (4) real-world examples of optimizations from our customers.

Pizza is provided, and we also have a user group lending library of books & papers related to PostgreSQL and Databases.

More details at

Speaker Bohan Zhang is the co-founder of OtterTune, an AI-powered database optimization startup. Before that, he worked at the Carnegie Mellon database group on the OtterTune research project. He has spent several years working around Postgres optimization for many customers, using both domain knowledge and machine learning. Bohan has spoken at a number of PostgreSQL Conferences in the past including PGConf Asia 2020, PGConf Asia 2021 and PGConf Silicon Valley 2023.