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
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 |