Pair partition indexes by parent index in schema validation

What does this MR do and why?

The schema validation (the index_inconsistencies Service Ping metric, gitlab:db:schema_checker:run, and the pre-upgrade check gitlab:db:validate_schema) reports false positives for partition child indexes whose names differ from db/structure.sql, and for indexes that db/structure.sql can never have. This MR:

1. Pairs attached partition child indexes by their table and parent index, not by name.
2. Stops reporting the temporary bigint_idx_ indexes and the legacy unique_schema_migrations index as extra indexes.

Together these are about 97% of the index inconsistencies in the 19.4 Service Ping sample.

1. Partition child indexes

PostgreSQL generates the names of partition child indexes. When several tables share the same truncated name prefix, it adds a shared numeric suffix, so the name each table gets depends on the order in which the tables were created. The four virtual registry cache tables share such a prefix. db/structure.sql was generated from a database where the npm local-entries migration ran before the npm remote-entries migration, but upgraded instances run migrations in timestamp order, so 32 child index names end up on other partitions than in db/structure.sql. In the 19.4 sample, these 32 names are in all 200 pings and are 77% of all index inconsistencies.

db/structure.sql records each child with ALTER INDEX <parent> ATTACH PARTITION <child>, and the database records it in pg_inherits. PostgreSQL attaches a child only when its definition matches the parent. Validators::PartitionIndexMatcher uses that link:

• An attached child pairs with the database child of the same table and parent index, whatever the names.
• A paired child with a different definition is one DifferentDefinitionIndexes item.
• A db/structure.sql child whose table has no attached child of that parent index is MissingIndexes.
• A database child whose table and parent index are not in db/structure.sql is ExtraIndexes.
• The indexes left over on both sides (indexes without a parent, and attached children without a pair) match by schema and name, so a name held by a paired child does not hide an unpaired index.
• A child of a parent index that is itself missing or extra is not reported. The parent report covers it.

This covers attached children in gitlab_partitions_static and in public (the uploads partitions). Sources::Database reads the parent of each child in the same pg_indexes query. It now keeps every index row: before, the rows went through a map keyed by name, so a public index and a partition index with the same name collapsed to one.

On a GDK database (PostgreSQL 17.8) with the db/structure.sql of this branch, both sides have 1310 attached children (1043 in gitlab_partitions_static, 267 in public), and the index validators report the same items as on master. None of them is a partition index.

Not covered: multi-level partitioning, where the key holds the generated name of the middle index (db/structure.sql has none), and a partition left in the detach-pending state.

We chose not to add a migration to rename the indexes. PostgreSQL assigns the names, so a rename would fix only this collision, and the next collision would bring the problem back. No migration helper uses a child index name that PostgreSQL generated.

The gem now requires Ruby 3.2 or later (was 3.0 or later), because the matcher uses to_set without require 'set'. Ruby 3.2 loads Set automatically, and RuboCop (TargetRubyVersion 3.2) flags require 'set' as redundant. CI runs the gem on Ruby 3.3 and 3.4.

2. Temporary and legacy extra indexes

The investigation in #629731 (comment 3920601439) found these in the 19.4 sample:

• bigint_idx_<sha> indexes: 1707 items in 155 of 200 pings (20% of all index inconsistencies). ConvertToBigint#bigint_index_name names them bigint_idx_ plus the first 20 hex characters of a SHA256. The database that generates db/structure.sql already has bigint IDs, so db/structure.sql never has them, and every upgraded instance reports them while a conversion runs.
• unique_schema_migrations: 64 of 200 pings. Old Rails versions created this unique index on schema_migrations. db/structure.sql has only the primary key.

Rules, both only in ExtraIndexes:

• The name matches \Abigint_idx_\h{20}\z exactly. A hand-made bigint_idx_manual is still reported.
• The statement is exactly CREATE UNIQUE INDEX unique_schema_migrations ON public.schema_migrations USING btree (version).

db/structure.sql has no index that matches either rule.

Not covered: the conversion columns and triggers, which the column and trigger validators report. See the future work section of the investigation comment.

Effect on gitlab:db:validate_schema

Among the index validators, the pre-upgrade check runs only MissingIndexes. A partition child now fails it only when the database has the parent index but no child of it on that partition. The child name has no effect. An index without a parent fails it when no database index has the same schema and name.

Results on PostgreSQL 17.8, one partitioned index with children on two partitions:

Database state	master	this MR
All children attached	nothing, pass	nothing, pass
Child names swapped between the partitions	2 DifferentDefinitionIndexes, pass	nothing, pass
Child renamed by PostgreSQL (_idx1)	MissingIndexes and ExtraIndexes, fail	nothing, pass
Index with the expected child name exists but is not attached	nothing, pass	MissingIndexes, fail
Partition has no child index	MissingIndexes, fail	MissingIndexes, fail

The fourth row is a new failure. It is correct: the partition has no working child of the parent index, and PostgreSQL marks the parent index invalid.

References

• Related to #629731 (closed)
• Investigation comment: #629731 (comment 3920601439)
• Split out of this MR: !259890 (invalid indexes) and !259893 (inconsistency filter)

Screenshots or screen recordings

Not applicable, this MR has no UI change.

How to set up and validate locally

1. In a Rails console, swap the names of two npm cache child indexes inside a transaction that gets rolled back, and check the metric:

   ApplicationRecord.transaction do
     ActiveRecord::Base.connection.execute(<<~SQL)
       ALTER INDEX gitlab_partitions_static.virtual_registries_packages__upstream_id_group_id_relativ_idx16 RENAME TO tmp_swap_idx;
       ALTER INDEX gitlab_partitions_static.virtual_registries_packages__upstream_id_group_id_relativ_idx32 RENAME TO virtual_registries_packages__upstream_id_group_id_relativ_idx16;
       ALTER INDEX gitlab_partitions_static.tmp_swap_idx RENAME TO virtual_registries_packages__upstream_id_group_id_relativ_idx32;
     SQL
   
     pp Gitlab::Usage::Metrics::Instrumentations::IndexInconsistenciesMetric.new(time_frame: 'none').value
   
     raise ActiveRecord::Rollback
   end

   Expect neither name in the output. On master, the same block prints both names as DifferentDefinitionIndexes.

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.