Skip to content

Adding ability to cleanup by partitions

Max Fan requested to merge 451231_loose_foreign_key_clean_up_partitionized into master

What does this MR do and why?

Currently LooseForeignKeys::CleanupWorker doesn't cleanup the records using partition pruning. One example is p_ci_builds. When a runner entry is deleted, the worker does a scan on the entire p_ci_builds

UPDATE \"p_ci_builds\" SET \"runner_id\" = $1 WHERE (\"p_ci_builds\".\"id\", \"p_ci_builds\".\"partition_id\") IN (SELECT \"p_ci_builds\".\"id\", \"p_ci_builds\".\"partition_id\" FROM \"p_ci_builds\" WHERE \"p_ci_builds\".\"runner_id\" IN ($2) LIMIT $3 FOR UPDATE SKIP LOCKED)

Current behaviour performance: https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/27632/commands/86189

With this change, instead of updating p_ci_builds directly, we'll be updating the partitions directly for p_ci_builds

   (13.3ms)  UPDATE "public"."ci_builds" SET "runner_id" = NULL WHERE ("public"."ci_builds"."id", "public"."ci_builds"."partition_id") IN (SELECT "public"."ci_builds"."id", "public"."ci_builds"."partition_id" FROM "public"."ci_builds" WHERE "public"."ci_builds"."runner_id" IN (5) LIMIT 500 FOR UPDATE SKIP LOCKED) /*application:console,db_config_name:ci,console_hostname:MaxPro.lan,console_username:maxfan,line:/app/services/loose_foreign_keys/partition_cleaner_service.rb:31:in `block in execute_partitioned_queries'*/

   (1.8ms)  UPDATE "gitlab_partitions_dynamic"."ci_builds_101" SET "runner_id" = NULL WHERE ("gitlab_partitions_dynamic"."ci_builds_101"."id", "gitlab_partitions_dynamic"."ci_builds_101"."partition_id") IN (SELECT "gitlab_partitions_dynamic"."ci_builds_101"."id", "gitlab_partitions_dynamic"."ci_builds_101"."partition_id" FROM "gitlab_partitions_dynamic"."ci_builds_101" WHERE "gitlab_partitions_dynamic"."ci_builds_101"."runner_id" IN (5) LIMIT 500 FOR UPDATE SKIP LOCKED) /*application:console,db_config_name:ci,console_hostname:MaxPro.lan,console_username:maxfan,line:/app/services/loose_foreign_keys/partition_cleaner_service.rb:31:in `block in execute_partitioned_queries'*/

After performance (With postgres reset):

public.ci_builds (AKA partition 100): https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/27632/commands/86191

ci_builds_101: https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/27632/commands/86195

MR acceptance checklist

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

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

Before After

How to set up and validate locally

  1. New behaviour
    1. Delete a runner from the UI
    2. Wait for LooseForeignKeys::CleanupWorker to run. Or manually trigger it via the rails console
    3. In the logs, the sql queries should be only updating from partitions of p_ci_builds and not the table itself
    4. In the database the builds with runner_id equal to the runner you deleted should have been updated to null
  2. Existing behaviour
    1. For every other loose foreign key, it should work as normal
    2. An easy one to test is deleting a pipeline_id should set the merge_train's pipeline id to null

Relates to : #451231

Edited by Max Fan

Merge request reports