Geo: Add reverification index to ci_job_artifact_states

What does this MR do and why?

Adds a partial index index_job_artifact_states_reverification on ci_job_artifact_states (verified_at) WHERE verification_state = 2 to fix slow Geo re-verification batch queries.

Geo::ReverificationBatchWorker periodically resets verified job artifacts for re-verification via the needs_reverification scope (ee/app/models/concerns/geo/verification_state.rb), which filters on verification_state = 2 AND verified_at < interval and orders by verified_at. No existing index covers verification_state = 2 (the existing partials cover states 0 and 3 only), so every batch falls back to a sequential scan over the whole table. On large self-managed Geo instances this takes 4-5+ seconds per query at ~13,000 queries/day.

The partial index serves both the filter and the ORDER BY verified_at ASC LIMIT 1000 (no sort step), reducing the batch UPDATE from minutes/seconds to milliseconds.

Relates to #601031 (closed)

Changelog: performance

Why a synchronous index creation on a LargeTables entry?

ci_job_artifact_states is classified table_size: large, but it is a Geo-only table: it is empty on GitLab.com because Geo is not active there (Geo runs only on GitLab Dedicated and Self-Managed). Index creation on .com is instant, so the async two-migration workflow is unnecessary. On self-managed Geo instances the index is built with add_concurrent_index (non-blocking) during the post-deploy migration.

This follows the precedent of migration 20260415131537_add_functional_index_on_ci_job_artifact_states_for_geo_bucket (index_ci_job_artifact_states_on_bucket_number), which was approved under exception issue database-team/team-tasks#614. Exception issue for this index: database-team/team-tasks#668

Database review

Database review notes:

• Geo is not active on GitLab.com, so ci_job_artifact_states has 0 rows in the postgres.ai snapshot (verified via SELECT count(*) — 8 ms, and a GROUP BY verification_state explain returning 0 rows).
• Seeded 5,000,000 rows in Database Lab (gitlab-production-ci) sourced from real p_ci_job_artifacts rows (real id/partition_id/project_id distribution). States interleaved via modulo, not contiguous ranges: 90% verification_state = 2 (succeeded), 5% state 0 (pending), 5% state 3 (failed) — matching a healthy Geo primary where nearly all artifacts are verified. verified_at decorrelated from the PK via now() - (random() * interval '180 days').
• ANALYZE ci_job_artifact_states was run after seeding, before capturing plans.
• The index build on the seeded 4.5M state=2 rows took 1.4 s in Database Lab.

Query: reverification batch UPDATE (issued by Geo::ReverificationBatchWorker)

Ruby:

# ee/app/models/concerns/geo/verification_state.rb
def reverify_batch(batch_size:)
  relation = needs_reverification.order(:verified_at).limit(batch_size)
  mark_as_verification_pending(relation)
end

SQL:

UPDATE ci_job_artifact_states
SET verification_state = 0
WHERE job_artifact_id IN (
  SELECT job_artifact_id
  FROM p_ci_job_artifacts
  INNER JOIN ci_job_artifact_states cjas
    ON cjas.job_artifact_id = p_ci_job_artifacts.id
    AND cjas.partition_id = p_ci_job_artifacts.partition_id
  WHERE cjas.partition_id IS NOT NULL
    AND cjas.verification_state = 2
    AND cjas.verified_at < now() - interval '7 days'
  ORDER BY cjas.verified_at ASC
  LIMIT 1000
);

Scenario	Execution time	Buffers	Plan
Before (no index)	2.2 min	16.4M hits (~125 GiB) + 1.0M reads (~7.8 GiB)	158662
After (with index)	90 ms	35k hits (~274 MiB) + 750 reads (~5.9 MiB)	158664
After, zero-match worst case (verified_at < '1970-01-01')	0.33 ms	3 hits	158665

Before: Seq Scan + Hash Join over all 4.5M state=2 rows, then top-N sort. After: ordered Index Scan on the new partial index returning exactly 1000 rows, no sort. The zero-match case (nothing due for re-verification, the common steady-state) terminates immediately on the index boundary.

Local benchmark for corroboration (5M rows, PG 17, all 6 existing production indexes): warm-cache 2.4 s → 12 ms (~200x).

Index size estimate: ~23 bytes/row → ~110 MB at 5M rows, ~600 MB at 26M rows (largest known affected instance). The index only receives writes when Geo verification workers update verified_at/verification_state — no hot application write paths.

How to set up and validate locally

1. Run the migration: bin/rails db:migrate

2. Confirm the index exists:

   gdk psql -c "\d ci_job_artifact_states" | grep reverification

3. Confirm rollback removes it:

   bin/rails db:migrate:down VERSION=20260820194936

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #601031 (closed)