Can manual vacuuming conflict with auto vacuuming?

[Mahesh Jhala <mjhala@xxxxxxxxxxxxxx>]

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:

1. 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.
2. 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