I have an issue with autovacuum - I have several big tables (over 10 million records) which are updated very frequently (500 updates per second). Everything works OK until bloat happen :)
As I can see, some of the tables are not autovacuumed for more than 10 minutes, and in that period, a lot of dead tuples are generated which then kill performance.
So, my question is - what is the flow of autovacuum process? E.g. If I have 10 tables that should be vacuumed (autovacuum threshold is reached on those tables), will Postgres vacuum tables one-by-one, or will it vacuum all tables in parallel? I couldn't find that info in official documentation. Another question - Postgres will spawn single autovacuum worker per database? If I have only one database, then I do not have to change "autovacuum_max_workers" parameter?
I have changed the following autovacuum config parameters:
- autovacuum_vacuum_cost_limit = 1000
- autovacuum_max_workers = 6
- autovacuum_vacuum_scale_factor = 0.01
- maintenance_work_mem = 512MB
Beside that, I have configured autovacuum threshold on big tables (because 1% od 10 million is 100k):
- ALTER TABLE mytable SET (autovacuum_vacuum_threshold = 15000);
- ALTER TABLE mytable SET (autovacuum_vacuum_scale_factor = 0);