locks created by autovacuum cause delays to upgrade owing to blocked migrations

Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.

• Close this issue

Proposal

There's been an increase in customers having issues during upgrades caused by contention between autovacuum and migrations.

This feature request is intended to identify improvements we could make to identify this issue and alert administrators.

Related issues

• last lock-retries iteration (without a timeout)... (#480062 - closed)
• Autovacuum on large tables like ci_builds can run effectively indefinitely if workmem is too low. This has been observed twice, see PostgreSQL maintenance_work_mem needs to be tun... (omnibus-gitlab#8377)

Adjacent

• [Feature flag] Pause migration while autovacuum... (#360331 - closed)
• Consider dead tuple ratio to control migration ... (#329769)

Internal links for GitLab team members

• Customer issue where upgrade was stuck trying to get a lock on p_ci_builds.
  • This site also experienced the last lock retries iteration bug.