The Part of PostgreSQL I Hate the Most
Presented by:
Bohan Zhang
I am currently an engineer at OpenAI infra team. Previously, I co-founded OtterTune, an AI-powered database optimization startup. Before that, I worked with Prof.Andy Pavlo at the Carnegie Mellon database group on the OtterTune research project. I have spent several years in optimizing Postgres for many customers with both domain knowledge and machine learning.
I spoke at PostgreSQL Conferences in the past including PGConf Asia 2020, PGConf Asia 2021, PGConf Silicon Valley 2023 and Postgres Conference 2024.
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, expensive vacuums), (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) real-world examples of optimizations from our customers (e.g., we reduced the job time from 52 minutes to 34 seconds for our customer by tunning vacuums), and (4) some insights we learned from analyzing customer data. (e.g., we can understand how bad the bloat problem is by analyzing the table bloat ratio distribution of our customers' databases).
- Date:
- 2023 April 21 11:40 PDT
- Duration:
- 20 min
- Room:
- San Pedro, Lvl C
- Conference:
- Silicon Valley 2023
- Language:
- Track:
- Ops
- Difficulty:
- Easy