Unoptimized auto-vacuum configuration on self-managed PostgreSQL

Note

This is a collection issue. It is used to gather customer signals (RFH issues, Zendesk tickets) that will inform database state work. See the parent epic for instructions on adding a new signal topic.

Warning

Privacy: These issues are not confidential. Do not include any customer-identifying information — no customer names, company names, or details that could identify a specific customer. See the SAFE framework for guidance. Version numbers and general configuration details are fine.

Background

Large self-managed GitLab instances repeatedly run into autovacuum problems whose root cause is the same: PostgreSQL is configured with default or near-default autovacuum settings that are too conservative for a database of GitLab's typical size and write churn. The Database team has applied the same tuning playbook to multiple customers; the playbook is not in customer-facing documentation, and customers only discover the issue after it has caused an incident.

The recurring misconfigurations are:

Setting Customer-observed value Modern PostgreSQL default Recommended for large GitLab self-managed Notes
autovacuum_vacuum_cost_delay 10–20 ms 2 ms (PG12+) 2 ms Older defaults (20ms) leave autovacuum throttled 10× slower than modern PG.
maintenance_work_mem 16 MB – 508 MB 64 MB 1–2 GB (subject to RAM; GitLab.com uses 2GB) Each autovacuum_max_workers worker uses this much; also speeds up index creation.
vacuum_cost_limit 200 200 800+ Increase further on fast modern disks.
autovacuum_vacuum_insert_scale_factor 0.2 0.2 0.02 Match autovacuum_vacuum_scale_factor; insert-heavy tables otherwise never get vacuumed.
autovacuum_max_workers 3 3 >3 for large DBs Requires a server restart, so frequently deferred even after the rest of the tuning is applied.

When these settings are left at their defaults, the team observes a recurring set of symptoms:

  • Multi-day autovacuum runs, including the dreaded autovacuum: VACUUM <table> (to prevent wraparound) holding a lock for 5+ days. In one observed case a customer's manual ANALYZE was blocked for 2 days behind a wraparound autovacuum that had already been running for 5.
  • System-catalog bloat (pg_class, pg_attribute, pg_index, pg_statistic). This is the upstream cause of gitlab-org/gitlab#600498 (gitlab:db:reindex times out on the postgres_index_bloat_estimates lookup).
  • Customers manually running ANALYZE because they have learned that "running ANALYZE helps" — a strong signal that autovacuum is not keeping statistics current. The team's standing observation is: if a customer feels they need to run ANALYZE manually, autovacuum is mis-tuned.
  • Timeout/VacuumDelay wait events dominating, with elevated CPU load that customers misattribute to query load.
  • Statement timeouts and lock contention on heavy tables (merge_request_diff_commits, ci_builds, p_ci_* partitions, partitioned LFK tables), which then cascade into LFK cleanup-worker timeouts (gitlab-org/gitlab#600628).
  • Transaction-wraparound risk, especially for installations with elevated write activity from CI churn or recently-introduced AI features (code-review assistants, etc.).

The operational gaps the signal captures are:

  • No customer-facing tuning guidance. There is no GitLab documentation page that says "if your database is over X GB, set these five values." Customers and field engineers reach for the values ad-hoc, and the recommendations are shared as Slack/RFH comments rather than as a runbook.
  • GitLab.com's tuned values are not published as a reference. Examples: maintenance_work_mem = 2GB, autovacuum_vacuum_cost_delay = 2ms (the PG12+ default but customers on older configs are stuck at 20ms). Publishing the reference set would shortcut every conversation.
  • Post-tuning observability is unclear. Once the settings are reloaded with select pg_reload_conf();, in-progress vacuums keep their old parameters. There is no documented procedure for whether to cancel them via pg_cancel_backend or wait; the team currently reasons about it case-by-case.
  • The autovacuum_max_workers restart penalty is buried. Even when the other four settings are applied immediately, the worker-count change is deferred because it requires a restart — leaving the largest installations with the same 3-worker bottleneck the recommendations were trying to address.
  • Cross-cutting impact is invisible to the operator. Catalog bloat (→ db:reindex failures), LFK cleanup-worker timeouts, query-plan flips during incidents — all share this single upstream cause but are reported as separate problems and triaged separately. The signal here is the upstream condition that, once fixed, would meaningfully reduce inflow for several other sub-issues under this epic.

Affected installations: large self-managed deployments (frequently on managed PG — RDS, Azure Flexible Server), inherited PostgreSQL configurations originating from older defaults, multi-thousand seats, high write churn from CI and/or AI-assisted workflows. Affected PostgreSQL versions span PG13 through current; the issue is configuration-level, not version-bound.

Contributing References

Add references as comments on this issue, not inline here. Each comment should include:

  1. A 1–3 sentence summary of the customer problem, written without any customer-identifying information.
  2. The link to the RFH issue or Zendesk ticket.

Links should be to an RFH issue, Zendesk ticket, or GitLab bug issue. If your reference doesn't fit the signal described here, check the parent epic for instructions on adding a new signal topic.

This issue should have at least 2 references before it is considered active.