Re: Proposal - Reduce lock during first phase of VACUUM TRUNCATE from ACCESS EXCLUSIVE to EXCLUSIVE - Mailing list pgsql-hackers
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
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
pgsql-hackers by date: