Postgres - autovacuum on several big tables 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);
If only one database needs any attention, then upto autovacuum_max_workers will all get launched in that one database.  But it will take a few passages of autovacuum_naptime to get all of them launched.  Each one will take a different table to work on at a given time.  

This might not have the effect you want, however, as the IO throttling threshold will get divided up between them.  That means that while multiple tables are vacuumed in parallel, increasing the number autovacuum_max_workers does not increase the overall speed of vacuuming when you have IO throttling turned on.  The purpose of using a higher value of autovacuum_max_workers is to ensure that small tables don't get entirely neglected if all the (would-be-lower max number) of workers are individually pinned down by large tables.

If you are worried autovac is not working fast enough, I would just go for the big guns and set autovacuum_vacuum_cost_delay = 0 in order to turn off IO throttling, and set log_autovacuum_min_duration = 0 so you can see the what is going on.  This should quickly give you a definitive answer.  As opposed to incrementally changing autovacuum_vacuum_cost_limit, which will slowly give you ambiguous answers.