Hello All,
If autovacuum of a large PostgreSQL table is in progress, and I start a manual vacuum on the table, is there likely to be a conflict (locking etc.) between the two vacuums?
I did the following tests:
-
ALTER TABLE <table_name> SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE <table_name> SET (autovacuum_vacuum_threshold = 1);
The deleted a few rows from table <table_name>. This triggered an autovacuum in a minute or two. While this autovacuum was in progress I started a manual vacuum from another session while monitoring pg_stat_activity.
-
My tests showed two conflicting results, one of which I am not able to repeat:
- Until the manual vacuum is started, pg_stat_activity shows the autovacuum as active. As soon as the manual vacuum is started, pg_stat_activity starts showing the manual vacuum as active, and stops showing the autovacuum. This suggests that a manual vacuum ‘replaces’ the auto vacuum. I was able to repeat this test.
- The manual vacuum does not progress, it seems to be blocked by the autovacuum. pg_stat_activity continues to show the autovacuum. I am not able to repeat this test. Could this be because the autovacuum is at a certain stage in the vacuuming process, and cannot stop at that point?
Thanks,
Mahesh
