PGCon2016 - 20180510

PGCon 2016
The PostgreSQL Conference

Ilya Kosmodemiansky
Day Talks - Day 1 - 2016-05-19
Room DMS 1120
Start time 13:00
Duration 00:45
ID 961
Event type Lecture
Track DBA
Language used for presentation English

Autovacuum, explained for engineers

What is autovacuum, how it works and why is it so important.

In this talk, I will cover PostgreSQL MVCC and Undo-management basics, problems and hazards of misconfigured autovacuum, a couple of strategies on how to make your PostgreSQL installation feel happy about autovacuum. In addition I will show a couple of bloat-removing techniques and give some advices on how to monitor autovacuum activity in your database.

Misconfigured autovacuum is probably the most common source of PostgreSQL performance problems I have ever seen. DBAs tend to underestimate its importance, and when inadequate autovacuum finally starts to mess things up, they simply turn it off. I will explain why it is a very bad idea and why vacuuming is a kind of job that finally must be done. To do so, I'll overview briefly how PostgreSQL MVCC works and why it needs some sort of garbage collection. We will see some examples that will show how misconfigured autovacuum can look like on the monitoring graphs or in the other sources of diagnostic information. Next I will provide some good approaches to configuring autovacuum aggressively enough, with practical description of each setting and explanation of how it can change autovacuum behaviour. Finally, I will show a couple of bloat-removing techniques and explain why they still can be useful even in combination with the aggressive enough autovacuum.