High autovacuum CPU/IO usage from database with more than 10k append-only tables In my system I have more than 10k tables that are very similar, I only do insert operations in these tables and delete old data via a cron schedule one time per day.
These insertions come each minute, a mean of 5 rows per table which are inserted in a single insert query, meaning something around 50k rows per minute.
The issue I'm seeing is that stats collector (I disabled all stats except track_counts) uses a lot of CPU and IO all the time.
The same happens to autovacuum which will also use a lot of the PC resources for itself.
Another issue is that they never seem to stop, autovacuum and stats collector are always running as long as I'm always adding new data in the rate shown above.
If I disable track_counts and autovacuum, it fixes my issue entirelly, the database is kept idle most of the time.
The problem with this approach is that I obviously are not running vacuum anymore.
So, my question is, would it be a good solution for my case to disable autovacuum entirelly and run VACUUM FULL after my cron script deleted old data? This would mean that I would trigger a full vacuum query for each table one time per day.
If not, do you have some sugestions for postgres configuration changes to improve the situation without disabling autovacuum?
This is part of my postgresql.conf (feel free to ask for more information about it if needed):
track_activities = off
track_counts = on
track_io_timing = off
track_functions = none

log_parser_stats = off
log_planner_stats = off
log_executor_stats = off
log_statement_stats = off

#------------------------------------------------------------------------------
# AUTOVACUUM
#------------------------------------------------------------------------------

autovacuum = on

log_autovacuum_min_duration = -1

autovacuum_max_workers = 6
autovacuum_naptime = 3600

I already saw some answers for cases of append-only tables or big tables, but I think I'm seeing this issue because in my case I have a LOT of tables.
Also, for completeness, I'm running the database in Postgres 12 and with extension TimescaleDB 1.7.1 inside docker.
Disabling autovacuum is a very bad idea. Autovacuum removes dead lines and updates statistics. Disabling autovacuum will quickly lead to performance loss. Also, when autovacuum is disabled, sooner or later you will encounter a wraparound problem.
Since you are using TimescaleDB, why not use the drop_chunks() function to remove old data? This will significantly reduce the need to perform vacuum on tables.
However, autovacuum will still need to handle tables to prevent the wraparound I wrote about above. In addition, you can change the size of the chunk so that the size of the data to be processed by autovacuum is not so large.
There are no universal values ​​for the autovacuum setting. Try adjusting the following settings for the best performance in your case:
autovacuum_vacuum_threshold
autovacuum_vacuum_scale_factor
autovacuum_vacuum_cost_delay

For statistics:
autovacuum_analyze_threshold
autovacuum_analyze_scale_factor

You can also configure cost parameters for the autovacuum daemon to process pages:
vacuum_cost_page_hit
vacuum_cost_page_miss
vacuum_cost_page_dirty
vacuum_cost_limit