Can manual vacuuming conflict with auto vacuuming?

From: Mahesh Jhala <mjhala(at)carrentals(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Can manual vacuuming conflict with auto vacuuming?
Date: 2017-06-01 19:11:11
Message-ID: CY1PR02MB210591B98EF1786C92B91CB1B5F60@CY1PR02MB2105.namprd02.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Alvaro Herrera 2017-06-01 19:16:40 Re: Can manual vacuuming conflict with auto vacuuming?
Previous Message Samed YILDIRIM 2017-05-31 10:13:19 Re: How to load data in psql DB