<!-- Thanks for filing this issue to help us understand the rationale behind changing this very large table! If your change falls into one of our accepted exceptions, you don't need this issue. If not, please provide the following! --> ### Change Details - **Related issue:** https://gitlab.com/gitlab-org/gitlab/-/work_items/601031 - **Table:** `ci_job_artifact_states` - **Table size:** 26M+ rows on large self-managed instances (Geo table — no meaningful data on GitLab.com) - **What:** `index_job_artifact_states_reverification` — partial index on `(verified_at) WHERE verification_state = 2` - **Columns affected:** `verified_at` - **Data type:** `timestamp with time zone` - **Query/feature improved:** Geo re-verification batch UPDATE issued by `Geo::ReverificationBatchWorker` — filters on `verification_state = 2 AND verified_at < interval`, orders by `verified_at ASC`, batch of 1000. See [MR !251195](https://gitlab.com/gitlab-org/gitlab/-/merge_requests/251195). - **Estimated storage cost:** ~23 bytes/row on state=2 rows (~all rows on a healthy Geo primary): ~110 MB at 5M rows, ~600 MB at 26M rows **Note:** Geo is not active on GitLab.com — it runs only on GitLab Dedicated and Self-Managed. The `ci_job_artifact_states` table has 0 rows in the postgres.ai snapshot. The query plans below were generated after seeding the table with 5M rows (90% verification_state=2, states interleaved via modulo, `verified_at` decorrelated from the PK) to simulate production-scale self-managed data. ### Performance Impact - **Context:** Geo re-verification is perpetual: every succeeded record is re-verified every `minimum_reverification_interval` (default 7 days). Each batch query pays for a full scan regardless of `LIMIT`, because the `ORDER BY verified_at` requires examining all matching rows. A production instance with ~26M artifacts observed ~13,000 of these queries/day at 4.3-5.4 s each ([customer report](https://gitlab.com/gitlab-org/gitlab/-/work_items/601031)) — roughly 15 hours/day of cumulative seq-scan time on the primary database. - **Current performance issue:** Without the index, the batch UPDATE seq-scans and hash-joins all ~4.5M state=2 rows then top-N sorts, reading ~133 GiB of buffers per execution on the seeded clone. Execution time: **2.2 min** (cold Database Lab clone). [Query plan (without index)](https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/55166/commands/158662) - **Expected improvement:** With the partial index, the query becomes an ordered Index Scan returning exactly 1000 rows with no sort step, reading ~280 MiB of buffers. Execution time: **90 ms** — and **0.33 ms** in the zero-match steady state (nothing due for re-verification). [Query plan (with index)](https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/55166/commands/158664), [zero-match plan](https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/55166/commands/158665) - **Write operation impact:** Minimal — the index only receives entries when `verification_state`/`verified_at` change, which happens at Geo verification-worker pace (throttled batches), not on application hot paths. Index build took 1.4 s on the seeded 4.5M-row clone; the index is added concurrently in a post-deploy migration, so no downtime or locking. ### Alternatives Considered **Why these won't work:** - **No index, accept slower queries:** ~13,000 queries/day at multi-second seq scans continuously evicts the buffer cache and spills temp pages on the primary database of affected instances, degrading all other queries. Scaling is effectively O(N²) per re-verification interval (batch count and per-batch scan cost both grow linearly with table size). - **Reuse an existing index:** no existing index covers `verification_state = 2` — the existing partials cover states 0 and 3 only, and `index_on_job_artifact_id_partition_id_verification_state` cannot serve the `verified_at` filter/sort. - **Full (non-partial) index on `(verification_state, verified_at)`:** larger and slower to maintain; the partial predicate makes `verification_state` constant so the key needs only `verified_at`. ### Justification **Business impact:** Fixes an S2/P2 performance bug ([#601031](https://gitlab.com/gitlab-org/gitlab/-/work_items/601031), SLO Near Miss) affecting large self-managed Geo instances. This is the same table and effectively the same workload pattern already approved in [team-tasks#614](https://gitlab.com/gitlab-org/database-team/team-tasks/-/work_items/614) (bucket-number functional index for Geo verification summaries). **Risk of not implementing:** Affected instances continue burning ~15 hrs/day of primary-DB scan time on re-verification batches, degrading whole-instance performance. Customers are already applying the index manually as a workaround, which drifts from `structure.sql` and confuses future upgrades/`db:repair_index`. ### Checklist - [x] Title updated to "Schema change request: ci_job_artifact_states" - [x] Issue assigned to change author - [x] All sections completed - [x] Alternatives thoroughly evaluated - [x] Mentioned `@gitlab-org/database-team/triage` ### Database Team Review - [x] Change approved - [ ] Change denied - Rationale: - [ ] Requestor notified of decision