Everything you want to know about Postgres autovacuum
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.
Because of the design and implementation of Multi-Version Concurrency Control (MVCC) in Postgres, the old data accumulates during updates. Hence, the indispensable role of Postgres autovacuum, responsible for garbage collection of dead tuples, becomes crucial for ensuring database performance. However, effectively monitoring and tuning autovacuum is a non-trivial task. In this presentation, I will delve into the following topics: (1) the implementation of Multi-Version Concurrency Control (MVCC) in Postgres and the internals of autovacuum, (2) potential challenges users may encounter in autovacuum (e.g., long-running transactions can block autovacuum, consequently causing more long transactions), and (3) effective methods for monitoring the autovacuum process and fine-tuning it for optimal performance. (4) real-world examples of autovacuum issues experienced by our customers, and practical solutions to address them.
- Date:
- 2024 April 18 11:40 PDT
- Duration:
- 20 min
- Room:
- Santa Clara
- Conference:
- Postgres Conference 2024
- Language:
- English
- Track:
- Essentials
- Difficulty:
- Easy