Tag: autovacuum

Waiting for PostgreSQL 18 – Allow changing autovacuum_max_workers without restarting.

On 6th of January 2025, Nathan Bossart committed patch:

Allow changing autovacuum_max_workers without restarting.
 
This commit introduces a new parameter named
autovacuum_worker_slots that controls how many autovacuum worker
slots to reserve during server startup.  Modifying this new
parameter's value does require a server restart, but it should
typically be set to the upper bound of what you might realistically
need to set autovacuum_max_workers.  With that new parameter in
place, autovacuum_max_workers can now be changed with a SIGHUP
(e.g., pg_ctl reload).
 
If autovacuum_max_workers is set higher than
autovacuum_worker_slots, a WARNING is emitted, and the server will
only start up to autovacuum_worker_slots workers at a given time.
If autovacuum_max_workers is set to a value less than the number of
currently-running autovacuum workers, the existing workers will
continue running, but no new workers will be started until the
number of running autovacuum workers drops below
autovacuum_max_workers.
 
Reviewed-by: Sami Imseih, Justin Pryzby, Robert Haas, Andres Freund, Yogesh Sharma
Discussion: https://postgr.es/m/20240410212344.GA1824549%40nathanxps13

Continue reading Waiting for PostgreSQL 18 – Allow changing autovacuum_max_workers without restarting.

Is my autovacuum configured properly?

Autovacuum was added LONG time ago (in 7.4, as pg_autovacuum). Since then, there were many changes related to it.

These days, hopefully, we no longer see someone saying that they have to disable autovacuum due to performance issues.

But I still see people that say that they have to run daily/weekly vacuum because “autovacuum is not enough". Is it really?

Continue reading Is my autovacuum configured properly?

Waiting for PostgreSQL 13 – Allow autovacuum to log WAL usage statistics.

On 6th of April 2020, Amit Kapila committed patch:

Allow autovacuum to log WAL usage statistics. 
 
This commit allows autovacuum to log WAL usage statistics added by commit
df3b181499.
 
Author: Julien Rouhaud
Reviewed-by: Dilip Kumar and Amit Kapila
Discussion: https://postgr.es/m/CAB-hujrP8ZfUkvL5OYETipQwA=e3n7oqHFU=4ZLxWS_Cza3kQQ@mail.gmail.com

Continue reading Waiting for PostgreSQL 13 – Allow autovacuum to log WAL usage statistics.

Which tables should be auto vacuumed or auto analyzed – UPDATE

Some time ago I wrote blogpost which showed how to list tables that should be autovacuumed or autoanalyzed.

Query in there had one important problem – it didn't take into account per-table settings.

Continue reading Which tables should be auto vacuumed or auto analyzed – UPDATE

Which tables should be auto vacuumed or auto analyzed?

Recently I was in a situation where autovacuum couldn't keep up with changes. To solve the problem I finally decided to manually vacuum analyze all tables (manual vacuum/analyze is faster than one ran by autovacuum daemon).

But it irritated me that I didn't have ready way to check which tables are waiting for autovacuum to work on them.

So, I wrote it.

Continue reading Which tables should be auto vacuumed or auto analyzed?