Postgresql AUTOVACUUM ANALYSE parameters for a specified table

Question

I would like to set autovacuum parameters for a specified table.

My problem is that AUTOVACUUM ANALYSE on a small table takes way to long to complete. This table has only 2 columns (including the primary key) and 150 rows but it is frequently updated. Each row gets updated every minute after the client process has started.

I've found out that the autovacuum process on this table is taking up to 20 seconds. From what I've understood so far, this is because the autovacuum process is not executed as frequently as it should.

Following are the parameters set on the server configuration :

autovacuum_analyse_threshold => 50
autovacuum_analyse_scale_factor => 0.1

What parameters do you suggest I set specifically on this table ?

Thanks in advance.

UPDATE 1 : In production, the autovacuum analyse process runs on this table every 2 minutes and it take more than 10s.

UPDATE 2 : Table size is 15998 pages.

UDPATE 3 : I have 99 users (client connections) updating the table every 15 seconds.

• anon: @jjanes Please se update 2 in my question. The problem is that the table is locked during this process. And the process is eating CPU.
• Colin 't Hart: Please describe the table and the updates being done to it. It sounds to me like you should fix the root cause, and that is the update pattern on this table.

Answer 1

Since your table has 15998 pages but only 150 rows, it must be quite bloated. A one time VACUUM FULL or CLUSTER should fix that. Then the question would be whether it will just get bloated again.

In order to make it more aggressive, you would probably need to lower autovacuum_naptime (unless you have already done so) because it is already being vacuumed very often.

10 seconds is about how long I would expect it to take to analyze a table of that size under default autovac io throttling settings. But in that case, it is hard to believe that it is consuming a meaningful amount of CPU time. Have you measured the CPU usage?

What is the lock interfering with? If you are routinely running commands that conflict with an ANALYZE, that could explain why the table got so bloated in the first place.

• anon: I have not measured CPU usage for this specific task, as the background process is never the same. I may be wrong anyway. I've updated the question with information regarding update on this table (Update 3).