Thread: POC: Parallel processing of indexes in autovacuum
Hi!
The
VACUUM command can be executed with the parallel option. As documentation states, it will perform index vacuum and index cleanup phases of VACUUM in parallel using integer background workers. But such an interesting feature is not used for an autovacuum. After a quick look at the source codes, it became clear to me that when the parallel option was added, the corresponding option for autovacuum wasn't implemented, although there are no clear obstacles to this.Actually, one of our customers step into a problem with autovacuum on a table with many indexes and relatively long transactions. Of course, long transactions are an ultimate evil and the problem can be solved by calling running vacuum and a cron task, but, I think, we can do better.
Anyhow, what about adding parallel option for an autovacuum? Here is a POC patch for proposed functionality. For the sake of simplicity's, several GUC's have been added. It would be good to think through the parallel launch condition without them.
As always, any thoughts and opinions are very welcome!
--
Best regards,
Maxim Orlov.
Attachment
HI Maxim Orlov
Thank you for your working on this ，I like your idea ，but I have a suggestion ,autovacuum_max_workers is not need change requires restart , I think those guc are can like
autovacuum_max_workers
+#max_parallel_index_autovac_workers = 0 # this feature disabled by default
+ # (change requires restart)
+#autovac_idx_parallel_min_rows = 0
+ # (change requires restart)
+#autovac_idx_parallel_min_indexes = 2
+ # (change requires restart)
+ # (change requires restart)
+#autovac_idx_parallel_min_rows = 0
+ # (change requires restart)
+#autovac_idx_parallel_min_indexes = 2
+ # (change requires restart)
Thanks
On Wed, Apr 16, 2025 at 7:05 PM Maxim Orlov <orlovmg@gmail.com> wrote:
Hi!TheVACUUMcommand can be executed with the parallel option. As documentation states, it will perform index vacuum and index cleanup phases ofVACUUMin parallel usingintegerbackground workers. But such an interesting feature is not used for an autovacuum. After a quick look at the source codes, it became clear to me that when the parallel option was added, the corresponding option for autovacuum wasn't implemented, although there are no clear obstacles to this.Actually, one of our customers step into a problem with autovacuum on a table with many indexes and relatively long transactions. Of course, long transactions are an ultimate evil and the problem can be solved by calling running vacuum and a cron task, but, I think, we can do better.Anyhow, what about adding parallel option for an autovacuum? Here is a POC patch for proposed functionality. For the sake of simplicity's, several GUC's have been added. It would be good to think through the parallel launch condition without them.As always, any thoughts and opinions are very welcome!
--Best regards,Maxim Orlov.