Thread: Re: Proposal - Reduce lock during first phase of VACUUM TRUNCATE from ACCESS EXCLUSIVE to EXCLUSIVE
Re: Proposal - Reduce lock during first phase of VACUUM TRUNCATE from ACCESS EXCLUSIVE to EXCLUSIVE
From
Ramanathan
Date:
Hi,
The vacuum truncate operation consists of two phases: a backward scan of the heap [1] followed by the work to perform the actual truncation [2]. In the current implementation, both phases maintain an ACCESS EXCLUSIVE lock on the relation for the duration of the processing. The ACCESS EXCLUSIVE lock is unnecessarily restrictive during the first phase of VACUUM TRUNCATE and can prevent read-only access , which can cause extended outages on hot standby replicas as the primary does not release the lock based on waiting queries on the hot standby.
I propose modifying the use of an EXCLUSIVE lock during the backward scan phase, then upgrading that lock to ACCESS EXCLUSIVE only for the actual truncation phase. Since the truncation phase should be relatively quick, the impact of the ACCESS EXCLUSIVE lock should be minimal.
For your consideration, on tables with constant high traffic, it may be impossible to acquire the ACCESS EXCLUSIVE lock needed for the truncation phase.
I would appreciate feedback on this approach, particularly regarding any potential issues with changing lock levels mid-operation, the impact on concurrent operations, and any alternative approaches worth considering.
Thanks,
Ram
[1] https://github.com/postgres/postgres/blob/master/src/backend/access/heap/vacuumlazy.c#L3267
[2] https://github.com/postgres/postgres/blob/master/src/backend/access/heap/vacuumlazy.c#L3280
The vacuum truncate operation consists of two phases: a backward scan of the heap [1] followed by the work to perform the actual truncation [2]. In the current implementation, both phases maintain an ACCESS EXCLUSIVE lock on the relation for the duration of the processing. The ACCESS EXCLUSIVE lock is unnecessarily restrictive during the first phase of VACUUM TRUNCATE and can prevent read-only access , which can cause extended outages on hot standby replicas as the primary does not release the lock based on waiting queries on the hot standby.
I propose modifying the use of an EXCLUSIVE lock during the backward scan phase, then upgrading that lock to ACCESS EXCLUSIVE only for the actual truncation phase. Since the truncation phase should be relatively quick, the impact of the ACCESS EXCLUSIVE lock should be minimal.
For your consideration, on tables with constant high traffic, it may be impossible to acquire the ACCESS EXCLUSIVE lock needed for the truncation phase.
I would appreciate feedback on this approach, particularly regarding any potential issues with changing lock levels mid-operation, the impact on concurrent operations, and any alternative approaches worth considering.
Thanks,
Ram
[1] https://github.com/postgres/postgres/blob/master/src/backend/access/heap/vacuumlazy.c#L3267
[2] https://github.com/postgres/postgres/blob/master/src/backend/access/heap/vacuumlazy.c#L3280
Re: Proposal - Reduce lock during first phase of VACUUM TRUNCATE from ACCESS EXCLUSIVE to EXCLUSIVE
From
Tom Lane
Date:
Ramanathan <sivakrishnathan@gmail.com> writes:
> I propose modifying the use of an EXCLUSIVE lock during the backward scan
> phase, then upgrading that lock to ACCESS EXCLUSIVE only for the actual
> truncation phase. Since the truncation phase should be relatively quick,
> the impact of the ACCESS EXCLUSIVE lock should be minimal.
Except that mid-transaction lock upgrades increase the risk of
deadlock failures.
regards, tom lane