Debugging Postgres autovacuum problems: 13 tips - Citus Data<br>The schedule is out ️ for POSETTE: An Event for Postgres 2026!

Skip navigation

SHARE THIS POST<br>X icon Share on X LinkedIn icon Share on<br>LinkedIn link icon Copy link

Get our monthly newsletter

This post by Samay Sharma about autovacuum was originally published on the Microsoft TechCommunity Blog.

If you've been running PostgreSQL for a while, you've heard about autovacuum. Yes, autovacuum, the thing which everybody asks you not to turn off, which is supposed to keep your database clean and reduce bloat automatically.<br>And yet—imagine this: one fine day, you see that your database size is larger than you expect, the I/O load on your database has increased, and things have slowed down without much change in workload. You begin looking into what might have happened. You run the excellent Postgres bloat query and you notice you have a lot of bloat. So you run the VACUUM command manually to clear the bloat in your Postgres database. Good!<br>But then you have to address the elephant in the room: why didn't Postgres autovacuum clean up the bloat in the first place...? Does the above story sound familiar? Well, you are not alone. 😊<br>Autovacuum and VACUUM provide a number of configuration parameters to adapt it to fit your workload, but the challenge is figuring out which ones to tune. In this post—based on my optimizing autovacuum talk at Citus Con: An Event for Postgres—you'll learn to figure out where the problem lies and what to tune to make it better.<br>More specifically, you'll learn how to investigate—and how to fix—these 3 common types of autovacuum problems:<br>Problem #1: Autovacuum doesn't trigger vacuum often enough<br>Problem #2: Vacuum is too slow<br>Problem #3: Vacuum isn't cleaning up dead rows<br>Another common type of autovacuum problem is transaction id wraparound related, which is a meaty topic all on its own. In the future I plan to write a separate, follow-on blog post to focus on that topic.<br>Overview of all 13 autovacuum tips in this blog post<br>This cheat sheet diagram of "autovacuum tips" gives you an overview of all the Postgres autovacuum fixes you'll learn about in this blog post:<br>Figure 1: Diagram of the 13 different types of possible autovacuum fixes for the 3 most common types of autovacuum problems in Postgres. Intro to Autovacuum<br>If you're not yet familiar, Postgres uses Multiversion Concurrency Control (MVCC) to guarantee isolation while providing concurrent access to data. This means multiple versions of a row can exist in the database simultaneously. So, when rows are deleted, older versions are still kept around, since older transactions may still be accessing those versions.<br>Once all transactions which require a row version are complete, those row versions can be removed. This can be done by the VACUUM command. Now, VACUUM can be run manually but that requires you to monitor and make decisions about various things like: when to run vacuum, which tables to vacuum, how frequently to vacuum etc.<br>To make life easier for you, PostgreSQL has an autovacuum utility that:<br>wakes up every autovacuum_naptime seconds<br>checks for tables that have been "significantly modified"<br>starts more workers to run VACUUM and ANALYZE jobs on those tables in parallel.<br>You can learn more about autovacuum in general from Joe Nelson's great post on why Autovacuum is not the enemy.<br>Now, the definition of "significantly modified" in bullet #2 above—and how much to vacuum in parallel—depends heavily on your workload, transaction rate, and hardware. Let's start looking into debugging autovacuum with one of the most common autovacuum issues—autovacuum not vacuuming a "significantly modified" table.<br>Problem #1: Autovacuum doesn't trigger vacuum often enough<br>Vacuuming is typically triggered for a table if (non-transaction id wrapround related)<br>obsoleted tuples > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * number of tuples OR<br>the number of inserted tuples > autovacuum_vacuum_insert_threshold + autovacuum_vacuum_insert_scale_factor * number of tuples.<br>If you see bloat growing more than expected and find yourself needing to manually run VACUUM to clear up bloat, it's an indication that autovacuum is not vacuuming tables often enough.<br>You can check when and how frequently tables were vacuumed by checking pg_stat_user_tables. If your large tables show up here with low autovacuum counts and last_autovacuum well in the past, it's another sign that autovacuum isn't vacuuming your tables at the right time.<br>SELECT last_autovacuum, autovacuum_count, vacuum_count FROM<br>pg_stat_user_tables;<br>Copy<br>To vacuum tables at the right frequency, you should adjust the autovacuum_vacuum_scale_factor and autovacuum_vacuum_insert_scale_factor based on the size and growth rate of the tables.<br>As an example, for a table which has 1B rows, the default scale factor will lead to a vacuum being triggered when 200M rows change, which is quite a lot of bloat. To bring that to a more...