Add rake task to clean up orphaned pool repositories on removed storages (!252707) · Merge requests · GitLab.org / GitLab

What does this MR do and why?

Adds a maintenance Rake task, gitlab:pool_repositories:cleanup_orphaned_on_missing_shards, to delete pool_repositories records that reference permanently decommissioned Gitaly storages and are fully orphaned (no source_project_id, no member projects).

On GitLab.com, ~2,048 such records point at retired storages (nfs-file01–nfs-file110, praefect-file01). ObjectPool::DestroyWorker cannot clean them up because its Gitaly RPC would target a storage that no longer exists — the records are dangling pointers that can only be removed from the database.

Design decisions:

Rake task, not a migration. The deletion query would be the same either way; the difference is the input. A migration runs unattended, so it would have to treat "storage missing from Gitaly config" as "permanently decommissioned" — but a storage can be absent from config temporarily (misconfiguration, partial rollout), and deleting its pool records would orphan still-existing Gitaly data. The rake task instead has the operator assert which storages are permanently gone.
Abort guard. The task refuses to run when any given storage is present in the current Gitaly configuration.
Batching iterates over the indexed scope only. each_batch runs on (shard_id, source_project_id IS NULL); the member-project NOT EXISTS filter is applied to each yielded batch, not the outer scope, so batch-boundary queries never touch projects and their plans stay stable across batches (per the batching guidance).
Conditions evaluated inside the DELETE statement (via delete_all on the batch relation, never on collected IDs), so pools still referenced by projects are structurally excluded at delete time. A per-batch reconciliation warning is logged if CSV rows and deleted rows disagree.
Dry run by default (DRY_RUN=false to delete), with an audit CSV containing all columns of the affected rows, written before each batch delete, as the recovery record.
No callbacks — delete_all deliberately skips the state machine, so no ObjectPool::DestroyWorker jobs are enqueued against dead storages. The deletes do fire the standard loose-foreign-key trigger on pool_repositories, so the LFK cleanup worker will asynchronously nullify any projects.pool_repository_id still pointing at a deleted pool (expected: none, since referenced pools are excluded — this is a second safety net behind the NOT EXISTS check).

Execution on GitLab.com will happen via a separate change request (dry-run first, CSV attached).

Database query plans

Shard lookup

https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/56127/commands/160203

The excluded-pools count(log_excluded_pools_with_members method)

https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/56127/commands/160211

Initial batch-boundary query (finds the first id; runs once at the start of each_batch)

https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/56668/commands/160998

Batch read (the SELECT that loads rows for the audit CSV)

https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/56668/commands/161000

Batch delete (identical conditions, DELETE instead of SELECT; Joe rolls it back automatically, so it's safe)

https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/56668/commands/161001

References

Resolves https://gitlab.com/gitlab-org/gitlab/-/work_items/616939
Analysis: https://gitlab.com/gitlab-org/gitlab/-/work_items/592039
Epic: https://gitlab.com/groups/gitlab-org/-/epics/19130

How to set up and validate locally

Validated end-to-end on GDK in two parts: a multi-batch run driven from the Rails console (batch size lowered to 3 so batching behavior is actually exercised), and a rake-task smoke test.

Part 1 — multi-batch validation (Rails console)

Setup. Lower the batch size, seed two fake decommissioned shards with 12 sourceless pools — 3 of them still referenced by projects, interleaved so every batch contains at least one excluded row — plus one control pool that has a source_project_id and must never be touched:

klass = Gitlab::PoolRepositories::MissingShardCleaner
klass.send(:remove_const, :BATCH_SIZE)
klass.const_set(:BATCH_SIZE, 3)

# Log SQL to verify the query shapes
ActiveRecord::Base.logger = Logger.new($stdout)
logger = Logger.new($stdout)

shard  = Shard.by_name('msc-test-decommissioned')
shard2 = Shard.by_name('msc-test-decommissioned-2')
org    = Organizations::Organization.first

# 10 sourceless pools on shard 1; positions 2, 5, 8 get member projects
pools = 10.times.map { PoolRepository.create!(shard: shard, organization: org, state: 'ready') }
member_projects = Project.where(pool_repository_id: nil).limit(3).to_a
excluded = [pools[2], pools[5], pools[8]]
excluded.zip(member_projects).each { |pool, project| project.update_column(:pool_repository_id, pool.id) }

# 2 deletable pools on a second shard (multi-shard IN clause coverage)
2.times { PoolRepository.create!(shard: shard2, organization: org, state: 'ready') }

# Control: has a source project, must survive
pool_with_source = PoolRepository.create!(
  shard: shard, organization: org, state: 'ready',
  source_project: Project.where(pool_repository_id: nil).where.not(id: member_projects.map(&:id)).first)

Dry run:
```
klass.new(shard_names: [shard.name, shard2.name], output_file: '/tmp/orphaned_pools_dry.csv',
  logger: logger, dry_run: true).run!
```
Observed: Orphaned pools on given shards still referenced by projects (excluded): 3, Dry run complete. No rows were deleted. The CSV contains 9 rows (7 + 2 across both shards, with correct per-shard names), none of the 3 excluded pool IDs, and nothing is deleted.
Rerun against the existing output file — the overwrite guard aborts before touching it:
```
klass.new(shard_names: [shard.name, shard2.name], output_file: '/tmp/orphaned_pools_dry.csv',
  logger: logger, dry_run: false).run!
```
Observed: ValidationError: Refusing to run: /tmp/orphaned_pools_dry.csv already exists. — file checksum unchanged.

Delete for real:

klass.new(shard_names: [shard.name, shard2.name], output_file: '/tmp/deleted_pools.csv',
  logger: logger, dry_run: false).run!

Observed: Deleted 9 orphaned pool repository rows. across 4 batches, no batch-mismatch warnings. In the logged SQL:

the each_batch boundary queries carry only shard_id IN (...) and source_project_id IS NULL — no NOT EXISTS (the filter moved inside the block, so batch-boundary plans no longer depend on projects):

SELECT "pool_repositories"."id" FROM "pool_repositories" WHERE "pool_repositories"."shard_id" IN (3, 4) AND "pool_repositories"."source_project_id" IS NULL ORDER BY "pool_repositories"."id" ASC LIMIT 1

every DELETE still re-evaluates the full conditions, including the member-project exclusion:

DELETE FROM "pool_repositories" WHERE "pool_repositories"."shard_id" IN (3, 4) AND "pool_repositories"."source_project_id" IS NULL AND "pool_repositories"."id" >= 4 AND "pool_repositories"."id" < 7 AND (NOT EXISTS (SELECT 1 FROM "projects" WHERE "projects"."pool_repository_id" = "pool_repositories"."id"))

Post-delete: the 3 excluded pools and the control pool survive; the CSV contains exactly the 9 deleted rows; loose_foreign_keys_deleted_records gained 9 rows for public.pool_repositories, confirming the LFK trigger fired.

Part 2 — rake task smoke test (default batch size)

Setup: one deletable and one excluded pool on a fresh fake shard:

shard = Shard.by_name('msc-rake-smoke')
org = Organizations::Organization.first
PoolRepository.create!(shard: shard, organization: org, state: 'ready')
excluded_pool = PoolRepository.create!(shard: shard, organization: org, state: 'ready')
project = Project.where(pool_repository_id: nil).first
project.update_column(:pool_repository_id, excluded_pool.id)

Dry run:

bundle exec rake gitlab:pool_repositories:cleanup_orphaned_on_missing_shards \
  SHARD_NAMES=msc-rake-smoke OUTPUT_FILE=/tmp/msc_rake_dry.csv

Observed:

INFO -- : Orphaned pools on given shards still referenced by projects (excluded): 1
INFO -- : Dry run complete. No rows were deleted.
INFO -- : Results saved to /tmp/msc_rake_dry.csv
INFO -- : To delete these records run this command with DRY_RUN=false

The CSV contains only the fully orphaned pool.

Delete with DRY_RUN=false:
```
bundle exec rake gitlab:pool_repositories:cleanup_orphaned_on_missing_shards \
  SHARD_NAMES=msc-rake-smoke OUTPUT_FILE=/tmp/msc_rake_del.csv DRY_RUN=false
```
Observed: Deleted 1 orphaned pool repository rows. — the orphaned pool is gone, the pool with a member project survives, and the CSV (flushed before the delete) contains the deleted row.

Verify the guard with a storage from the current Gitaly configuration:

bundle exec rake gitlab:pool_repositories:cleanup_orphaned_on_missing_shards \
  SHARD_NAMES=default OUTPUT_FILE=/tmp/guard_test.csv

Observed (exit status 1, no output file created):

ERROR -- : ERROR: Refusing to run: default present in current Gitaly configuration. Only permanently decommissioned storages can be cleaned up.

Clean up the test data (console):

member_projects.each { |p| p.update_column(:pool_repository_id, nil) }
project.update_column(:pool_repository_id, nil)
PoolRepository.where(shard: [shard, shard2]).delete_all
Shard.where(name: %w[msc-test-decommissioned msc-test-decommissioned-2 msc-rake-smoke]).delete_all

Then restart the console (the BATCH_SIZE override is process-local).

Edited Sep 08, 2026 by Emma Park

Add rake task to clean up orphaned pool repositories on removed storages