Re: Proposal - Reduce lock during first phase of VACUUM TRUNCATE from ACCESS EXCLUSIVE to EXCLUSIVE
Hi,
The vacuum truncate operation consists of two phases: a backward scan of
the heap [1]https://github.com/postgres/postgres/blob/master/src/backend/access/heap/vacuumlazy.c#L3267 followed by the work to perform the actual truncation [2]https://github.com/postgres/postgres/blob/master/src/backend/access/heap/vacuumlazy.c#L3280. 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
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
https://github.com/postgres/postgres/blob/master/src/backend/access/heap/vacuumlazy.c#L3280
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
Except that mid-transaction lock upgrades increase the risk of
deadlock failures.
Thanks for the feedback. However, wouldn’t that risk already exist in the
current vacuum truncate process? As it stands, VACUUM TRUNCATE performs a
lock upgrade—from ShareUpdateExclusiveLock to AccessExclusiveLock—during
the actual truncate phase. This upgrade, while brief, also carries an
inherent risk of deadlocks.
The proposed change simply shifts part of the locking burden to the
backward scan phase by using an EXCLUSIVE lock instead of ACCESS EXCLUSIVE.
The idea is to reduce the lock's restrictiveness during the scan phase and
only escalate to ACCESS EXCLUSIVE for the fast truncation. Since we’re
already handling a similar upgrade in the current workflow, the risk
profile should be comparable.Which can mitigate the extended outages on hot
standby replicas as the primary does not release the lock based on waiting
queries on the hot standby.
Looking forward to your thoughts.
Best regards,
Ram
ᐧ
On Mon, 17 Feb 2025 at 20:50, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
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