Performance Issues and Missing Indexes After Upgrade to 18.3

How to Use GitLab Upgrade
1

Problem to solve

I recently upgraded to GitLab EE 18.3.1 from 18.2.1. The upgrade path tool recommended two jumps, so I did 18.2.5, then 18.3.1, and let all the background migrations finish at each step.

Afterwards, some users and I noticed some performance issues while navigating the web interface. It’s a bit slower than usual, and we get occasional error bars like “Unable to fetch header information for this pipeline” or “Work item not available” (probably not exact wording on these).

I went to Admin → Monitoring → Database diagnostics and ran the schema check. It shows 14 missing indexes and one missing foreign key. Could this be the cause of some performance issues? How should I approach resolving this?

I did try sudo gitlab-rake db:migrate and sudo gitlab-rake gitlab:db:repair_index already. These generated some background migrations, but didn’t resolve the issues.

As you can see from the output below, I’ve also got some collation version mismatches–is it generally safe to simply run those SQL queries? Would this be related to the missing indexes/performance issues?

Indexes

Missing items

p_ci_builds_metadata_build_id_id_idx

p_ci_builds_metadata_build_id_idx

p_ci_pipelines_ci_ref_id_id_idx

p_ci_pipelines_ci_ref_id_id_source_status_idx

p_ci_pipelines_id_idx

p_ci_pipelines_pipeline_schedule_id_id_idx

p_ci_pipelines_project_id_id_idx

p_ci_pipelines_project_id_ref_id_idx

p_ci_pipelines_project_id_ref_status_id_idx

p_ci_pipelines_status_id_idx

p_ci_pipelines_user_id_id_idx

p_ci_pipelines_user_id_id_idx1

p_ci_stages_pipeline_id_id_idx

p_ci_stages_pipeline_id_position_idx

Foreign keys

Missing items

public.fk_rails_1dfc868911

Steps to reproduce

Unknown

Configuration

Not sure what’s relevant here, but happy to provide information

Versions

Please add an x whether options apply, and add the version information.

  • Self-managed
  • GitLab.com SaaS
  • Dedicated

Versions

  • GitLab (Web: /help or self-managed system information sudo gitlab-rake gitlab:env:info):

WARNING: database “gitlabhq_production” has a collation version mismatch
DETAIL: The database was created using collation version 2.35, but the operating system provides version 2.39.
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE gitlabhq_production REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
WARNING: database “gitlabhq_production” has a collation version mismatch
DETAIL: The database was created using collation version 2.35, but the operating system provides version 2.39.
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE gitlabhq_production REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
WARNING: database “gitlabhq_production” has a collation version mismatch
DETAIL: The database was created using collation version 2.35, but the operating system provides version 2.39.
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE gitlabhq_production REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
WARNING: database “gitlabhq_production” has a collation version mismatch
DETAIL: The database was created using collation version 2.35, but the operating system provides version 2.39.
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE gitlabhq_production REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

System information
System: Ubuntu 24.04
Proxy: no
Current User: git
Using RVM: no
Ruby Version: 3.2.8
Gem Version: 3.6.9
Bundler Version:2.7.1
Rake Version: 13.0.6
Redis Version: 7.2.10
Sidekiq Version:7.3.9
Go Version: unknown
WARNING: database “gitlabhq_production” has a collation version mismatch
DETAIL: The database was created using collation version 2.35, but the operating system provides version 2.39.
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE gitlabhq_production REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

GitLab information
Version: 18.3.1-ee
Revision: cb15859792b
Directory: /opt/gitlab/embedded/service/gitlab-rails
DB Adapter: PostgreSQL
DB Version: 16.8
URL: redacted
HTTP Clone URL: recdacted
SSH Clone URL: redacted
Elasticsearch: yes
Geo: no
Using LDAP: no
Using Omniauth: yes
Omniauth Providers:

GitLab Shell
Version: 14.44.0
Repository storages:

  • default: unix:/var/opt/gitlab/gitaly/gitaly.socket
    GitLab Shell path: /opt/gitlab/embedded/service/gitlab-shell

Gitaly

  • default Address: unix:/var/opt/gitlab/gitaly/gitaly.socket
  • default Version: 18.3.1
  • default Git Version: 2.50.1.gl1
2

Hey @TimAtVolgistics This is being addressed in this MR, fixed in 18.4. Track other incosistencies on the feedback issue mentioned in the docs as well.