Exclude deprecated status from installable NuGet packages

What does this MR do and why?

The group-level NuGet metadata endpoint (GET /api/:version/groups/:id/-/packages/nuget/metadata/*package_name/index) runs a slow, unindexed query at high volume.

deprecated is an npm-only package status; only Packages::Npm::DeprecatePackageService ever writes it, so a NuGet package is never deprecated. But INSTALLABLE_STATUSES lives on the shared Packages::Package base model, and Packages::Nuget::Package did not override installable_statuses. So the NuGet installable scope filtered status IN (0, 1, 5).

That extra 5 made the query ineligible for its purpose-built partial index idx_pkgs_project_id_lower_name_when_nuget_installable_version, whose predicate is status = ANY (ARRAY[0, 1]). Postgres cannot use a partial index when the query can return rows outside the index predicate, so it fell back to a slower plan.

This MR overrides installable_statuses in Packages::Nuget::Package to [:default, :hidden]. That restores status IN (0, 1), which the existing index already serves. No migration is needed, and results are unchanged because no NuGet row is ever deprecated.

The same dead status = 5 filter also applies to other types with no deprecation concept (Conan, Maven, PyPI, Terraform), and their ARRAY[0, 1] partial indexes carry the same latent trap. That broader cleanup is tracked in the issue and left out of scope here.

How it broke

deprecated was added to the shared INSTALLABLE_STATUSES in !174312 (merged) (an npm change, merged Dec 2024) without updating the NuGet index predicate.

Database analysis

Both queries are the .exists? guard from find_packages, run on a gitlab-production-main clone against the same group and package (traversal_ids @> '{6052424}', LOWER(name) = 'verify.clipboardaccept'). They differ only in the status IN (...) list.

Before: status IN (0, 1, 5) (current behavior)

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/55357/commands/159008

Time: 2.381 s
  - planning: 28.542 ms
  - execution: 2.352 s
    - I/O read: 1.596 s
    - I/O write: 177.897 ms

Shared buffers:
  - hits: 2721406 (~20.80 GiB) from the buffer pool
  - reads: 82934 (~647.90 MiB) from the OS file cache, including disk I/O
  - dirtied: 2 (~16.00 KiB)
  - writes: 7792 (~60.90 MiB)

After: status IN (0, 1) (this MR)

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/55357/commands/159009

Time: 173.716 ms
  - planning: 31.902 ms
  - execution: 141.814 ms
    - I/O read: 107.325 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 0 from the buffer pool
  - reads: 6671 (~52.10 MiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Dropping the dead status = 5 value lets the query use idx_pkgs_project_id_lower_name_when_nuget_installable_version: ~2.38 s down to ~174 ms, and shared-buffer access from ~20.8 GiB down to ~52 MiB.

Related

Closes #622811 (closed)