Tighten sharding key NOT NULL constraint for events table

Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.

Context

Why? Logical replication requires REPLICA IDENTITY. This REPLICA IDENTITY must include only columns that are not null. The alternative is a fallback to REPLICA FULL which will slow down the replication by a lot.

Split out from #597554 (closed) to handle the events table separately, since it requires more work than the dependency_list_* tables (which can be tightened via a simple migration).

The current check constraint on events is:

CHECK (((group_id IS NOT NULL) OR (project_id IS NOT NULL) OR (personal_namespace_id IS NOT NULL)))

Per https://docs.gitlab.com/development/organization/sharding/#multiple-sharding-key-columns, this should be tightened so that exactly one of the sharding key columns is non-null per row (i.e. num_nonnulls(group_id, project_id, personal_namespace_id) = 1).

Current state of data

From investigation in #597554 (closed):

total                                     | 4,450,227,916
project_id only                           | 4,443,274,534
group_id only                             |     5,590,089
personal_namespace_id only                |     1,348,505
project_id + group_id                     |             0
personal_namespace_id + group_id          |        14,788  <-- violations
project_id + personal_namespace_id        |             0
all three                                 |             0

The ~14,788 violating rows are all WikiPage::Meta events that have both group_id and personal_namespace_id set:

select target_type, count(*) from events
where group_id is not null and personal_namespace_id is not null
group by target_type;

  target_type   | count
----------------+-------
 WikiPage::Meta | 14788

Per Krasimir, personal_namespace_id should only be set when both group_id and project_id are null. See:

Proposed work

  1. Investigate the source of the WikiPage::Meta events that have both group_id and personal_namespace_id populated, and fix the code path so new rows are not produced this way.
  2. Run a background migration (BBM) to fix the existing ~14,788 violating rows (likely by nulling personal_namespace_id where group_id is set).
  3. Add model level validation
  4. Add a migration to tighten the check constraint to num_nonnulls(group_id, project_id, personal_namespace_id) = 1 - start with NOT VALID
  5. Once the BBM is complete, make the above constraint valid.
  6. Once the BBM is complete, drop the CHECK (((group_id IS NOT NULL) OR (project_id IS NOT NULL) OR (personal_namespace_id IS NOT NULL))) constraint.

Related

Edited by 🤖 GitLab Bot 🤖