Autovacuum, explained for engineers

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.