Add siphon_p_vulnerability_identifiers ClickHouse table

Michał Zającrequested to merge
quintasan/siphon-p-vulnerability-identifiers into master

What does this MR do and why?

MR !254763 (merged) will swap the PostgreSQL primary key of vulnerability_identifiers from (id) to (id, partition_id) as part of the list partitioning epic (issue #596859).

The Siphon replication spec (spec/db/clickhouse_siphon_tables_spec.rb) requires the ClickHouse target table's primary key to end with the PostgreSQL primary key columns, and dedup_by to match them exactly. The existing ClickHouse table siphon_vulnerability_identifiers has PRIMARY KEY (traversal_path, id), and ClickHouse cannot alter a table's primary key in place.

This MR creates a new table siphon_p_vulnerability_identifiers with PRIMARY KEY (traversal_path, id, partition_id) and migrates the data over, following the same approach used for siphon_work_item_parent_links in commit ec61b573, based on guidance from the Siphon maintainers.

Implementation

Three ClickHouse migrations:

  1. 20260911151319: creates siphon_p_vulnerability_identifiers. Generated with bundle exec rails generate gitlab:click_house:siphon vulnerability_identifiers --with-traversal-path against a database that already had the composite primary key applied, then renamed.
  2. 20260911151320: creates a materialized view siphon_p_vulnerability_identifiers_mv that pipes new writes from siphon_vulnerability_identifiers into the new table, so replication keeps landing in both tables during and after the backfill.
  3. 20260911151321: backfills existing rows using ClickHouse::Iterator in 500k row batches. Duplicates from the overlap with the MV are collapsed by the ReplacingMergeTree engine. The down migration is a no-op since re-running the copy is idempotent.

What's deliberately not in this MR

  • db/siphon/tables/vulnerability_identifiers.yml is not repointed here. The spec requires dedup_by to equal the live PostgreSQL primary key, so that config change (pointing at siphon_p_vulnerability_identifiers with dedup_by: [id, partition_id]) ships together with the PostgreSQL primary key swap in !254763 (merged). That MR depends on this one.
  • Dropping the old siphon_vulnerability_identifiers table and its materialized view is left for a follow-up cleanup MR after !254763 (merged) merges.

How to set up and validate locally

  • Migrations were run against a real ClickHouse 25.12 container. db/click_house/main.sql and the schema cache YAML were regenerated using the actual gitlab:clickhouse:schema:dump:main tooling.
  • spec/db/clickhouse_siphon_tables_spec.rb: 79 examples, 0 failures
  • spec/scripts/click_house/schema_validator_spec.rb: 92 examples, 0 failures
  • RuboCop clean

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Merge request reports