fix(datastore): reach remote parent tombstones from three scan levels (S20-A lifecycle purger plan: 20b/24, stack: 5/5)

What this MR does

The purger now reaps a tombstoned maven_remote_packages, npm_remote_packages or container_remote_images row while its repository stays live. On main, these rows stay in the database for ever.

Cause

Each remote evictor marks only the parent row (maven_remote_packages, npm_remote_packages, container_remote_images) and leaves the children unmarked. No tombstone scan read those three tables, and artifactScopeLevels in internal/lifecycle/purge.go registered no purge level for them. The version-level scans find nothing under the mark, and the repository walk starts only from a deleted repository. As a result, no route gave the marked parent to its reaper. The defect was on the discovery side, not in the reapers.

The fix

The fix is split across a stack of five MRs. The Stack section below gives the order. This MR holds the purge levels, the gauge tables and the documentation. The bullets say which MR holds each other change.

  • Three new scans in internal/datastore/lifecycle_scan_packages.go, one for each table, in the shape that ScanTombstonedNpmPackages uses (!3403 (merged)). Each scan is namespace-scoped, reads the oldest tombstone first, is bounded by its limit, and rejects a limit outside (0, MaxLifecycleScanPageSize] before it runs a statement.
  • Three new purge levels in DefaultPurgeLevels, in a new remoteParentScopeLevels list that comes after the nine existing artifact levels. The order of the existing levels does not change. Each new parent level runs after the version or manifest level that feeds it. DefaultPurgeLevels now holds thirteen levels.
  • artifactTombstoneCountTables now holds twelve tables, so the gitlab_artifact_registry_lifecycle_tombstones gauge counts the three new tables. The HELP string in internal/accounting/metrics.go states the new count.
  • MavenRemotePackageReaper, NpmRemotePackageReaper and ContainerRemoteImageReaper report freed bytes and the RepositoryID on the scan route (!3402 (merged)). The byte figure is deduplicated within the repository. Where TombstoneRow.SkipFreedBytes is set (the repository walk), each arm reports zero bytes and uuid.Nil, as before.
  • resolveReapRepository gets three new routes, and sumFreedRepositoryBytes gets a sixth size family for npm remote, over npm_remote_files and npm_remote_metadata_files (!3401 (merged)). Both guards move to the new last value. The npm conversion follows operator ruling E2, option B.
  • Ten new query names in internal/datastore/query_names.go: four in !3401 (merged), three in !3403 (merged), and the three count names in this MR.

A cost that reviewers need to see

With the new container_remote_images scan, the artifact scope and the repository scope can both reach one image under a tombstoned repository at the same time. The two scopes are not serialized against each other. docs/dev/storage-accounting.md states the cost, which is the same cost that ContainerReaper already accepts: a 40P01 abort, a rollback of the whole chunk, one purge_outcome="error" row, and a discarded attempt that the next sweep does again.

Documentation that moves with the code

  • docs/dev/storage-accounting.md: the statements that no scan reads the three tables, and the Known gaps row for the missing levels, are replaced. A new section holds the caller obligations of ContainerRemoteImageReaper.Reap and the containerRemoteImageScope reasoning, which this MR moved out of the Go doc comments and rewrote to hold for both routes.
  • docs/dev/observability.md: the tombstone gauge counts, and the exclusion that pointed at the missing levels, are updated.
  • internal/datastore/lifecycle_reap_repository.go and .md: the reaper census and the walk shape are corrected.
  • internal/datastore/version_type_table_invariants.md: the inventory counts twelve tables and thirteen levels.

Spec coverage

Source: the test author's spec-coverage table, verbatim. No commit message on the branch carries this table.

Rows are the issue card's acceptance items (the S20-A section on the discovery path states that no acceptance criterion of the spec covers the three levels), then the spec criteria and considerations the card names.

Spec: docs/specs/S20-a-lifecycle-closed-beta.md, docs/specs/S17-rest-management-api.md (Eviction markers, Follow-ups)

Acceptance criteria

# Criterion Tests
Card 1 Each scan returns a tombstoned remote parent row under a live repository, oldest first, bounded, window-eligible, and rejects a limit outside (0, MaxLifecycleScanPageSize] before any statement TestLifecycleScanStore_PackageLevelTombstoneScans (10 cases per new target: window split, zero window, live row excluded, live parent, tombstoned parent, oldest first, bounded page, tenant isolation, empty probe, wrapped query failure), TestLifecycleScanStore_PackageLevelTombstoneScans_Guards (6 guard cases per new target), TestLifecycleScanStore_PackageLevelScansStmt_OrderAndBinding (ascending soft_deleted_at, no ROW( keyset, namespace and limit bound)
Card 2 Page and count statements prune to one partition on the discovery index; recorded in the MR's database review No Go test asserts a plan (docs/dev/go-testing.md; issue note 3966036444, F2). Owned by the MR's database review
Card 3 A tombstoned row of each table under a live repository is reaped end to end through the purge worker; fails on main TestPurgeWorker_Work_ArtifactScopeReapsARemoteParentMarkedUnderALiveRepository (one subtest per table: parent and its live child gone, live sibling and repository stand)
Card 4 A repository purge reaps the same rows, order and totals as before the levels Held by the existing TestRepositoryReaper_Reap_WalksEveryFormatAndKindArm, TestRepositoryReaper_Reap_OnAFullyReapedSubtreeIsANoOp, TestPurgeWorker_Work_ReapsATombstonedRepositoryAndLeavesALiveNeighbour; the walk route's zero figure on the converted arms is the Card 6 row
Card 5 Each of the three arms reports a byte figure deduplicated within the repository, by a shared-digest and a solo-digest case TestContainerRemoteImageReaper_Reap_ReportsTheRepositoryScopedBytes, TestMavenRemotePackageReaper_Reap_ReportsTheRepositoryScopedBytes, TestNpmRemotePackageReaper_Reap_ReportsTheRepositoryScopedBytes (each: a digest a second artifact of the repository holds frees nothing, a digest nothing holds frees its size across both legs, a copy in a neighbour repository holds nothing)
Card 6 SkipFreedBytes set: zero bytes and uuid.Nil on each arm; off that route the npm arm reports bytes and RepositoryID through the npm remote size family over npm_remote_files and npm_remote_metadata_files The "the repository walk's row declines the figure" subtest of each of the three functions above; TestNpmRemotePackageReaper_Reap_ReportsTheRepositoryScopedBytes (survivors in both cache tables); TestSumFreedRepositoryBytes_AdmitsTheNpmRemoteFamilyAndNothingPast (statement reads both cache tables and their chains)
Card 7 resolveReapRepository admits the three new routes and rejects the next value; sumFreedRepositoryBytes admits the npm remote family and rejects the next value; both before any statement TestResolveReapRepository_AdmitsTheRemoteParentRoutesAndNothingPast, TestSumFreedRepositoryBytes_AdmitsTheNpmRemoteFamilyAndNothingPast, TestReapSizeStmts_TheRemoteParentStatementsBindThePartitionKey
Card 8 artifactTombstoneCountTables holds twelve entries and the seeder list pairs a seeder with each TestArtifactTombstoneCountTables_CountsTheRemoteParentTables, TestArtifactTombstoneSeeders_PairOneSeederWithEachCountedTable, TestLifecycleScanStore_CountTombstonesByLevel (one positive-hit subtest per new seeder)
Card 9 DefaultPurgeLevels runs each new parent level after the level that feeds it, per pair TestDefaultPurgeLevels_RunEachRemoteParentLevelAfterTheLevelThatFeedsIt (three pairs, scope, family, scan and reaper present)
Card 10 DefaultPurgeLevels holds thirteen levels; HELP string, doc counts and never-verdict membership agree TestDefaultPurgeLevels_NameTheFamilyOfEveryArtifactLevel (positional, thirteen), TestDefaultPurgeLevels_CoverEveryScanLevelOfBothScopes (table, scan and reaper per level). The HELP string, the doc counts and the never-verdict prose are not Go-tested: no shared count declaration exists to test against (issue #919 (closed))
S20-A remote-cache subtree criterion A tombstoned remote-cache subtree is reaped for every remote table carrying the column TestPurgeWorker_Work_ArtifactScopeReapsARemoteParentMarkedUnderALiveRepository adds the parent-level positive hits under a live repository
S20-A artifact-purge criterion An artifact purge emits the repository-scoped delta where its chunks free bytes; a repository purge issues none The arm halves: Card 5 and Card 6 rows. The emit half is held by the existing internal/lifecycle chunk emit suites

Error cases

Condition Tests
Purge job: 23503 on a parent delete Unchanged by this change; held by the existing ErrReapParentPinned cases of the three arms' suites
Purge job: retention window changed between enqueue and run The new scans' window cases in TestLifecycleScanStore_PackageLevelTombstoneScans

Security considerations

Concern Tests
The purger's authority comes from the tombstone TestPurgeWorker_Work_ArtifactScopeReapsARemoteParentMarkedUnderALiveRepository (the row's own mark, repository live, live sibling untouched); live-row exclusion case of TestLifecycleScanStore_PackageLevelTombstoneScans
Tenant isolation: every scan is namespace-scoped Tenant case of TestLifecycleScanStore_PackageLevelTombstoneScans; TestReapSizeStmts_TheRemoteParentStatementsBindThePartitionKey
Purge failures stay visible on the tombstone gauge Card 8 row
No injection surface: parameterized statements TestLifecycleScanStore_PackageLevelScansStmt_OrderAndBinding (two bound arguments)

Derived readings, open to review

The test author derived two readings, and did not put them to the operator:

  • Card 9, "each after the level that feeds it": the three levels come after maven_files, in the order maven_remote_packages, npm_remote_packages, container_remote_images. The positional family test pins that order, and the pair test pins only the pairs.
  • Card 1, "in the shape ScanTombstonedNpmPackages uses": no keyset cursor. The container_remote_images scan can break ties by id, because its discovery index keys id after soft_deleted_at.

E2e scenario catalogs

  • docs/testing/e2e/npm.md: the e2e.npm.remote.evict-package row said that no sweep reaches the evicted rows. This MR makes that statement false, because the new npm_remote_packages scan level lets the purger reap an evicted package while its repository stays live. The row now asserts the refetch after the eviction. It explains the reap after the retention window and the size_bytes decrease, but it does not assert them. The Maven sibling row, e2e.maven.remote.evict-package, says that the request leaves the rows for the purger. That statement stays true, so the row is unchanged.
  • This MR adds no new row. No row covers the eviction of a cached container remote image, and a new row for it is advisory, not required, per docs/testing/e2e/README.md (## Writing a row). The destructive repository delete keeps its own rows, e2e.npm.setup.delete-repository and e2e.oci.setup.delete-repository, and this MR makes neither of them false.

Plan and spec amendments

  • docs/plans/2026-08-11-s20a-lifecycle-closed-beta.md (review-driven correction, finding F5 at the extent that F17 records): the Research Findings passage now states that the npm remote package arm converts through an npm remote size family. Step 20b's Scope, Files, Acceptance, Tests and Accepted smell lines now state three converted arms, three resolve routes and one size family. The npm_remote_files leg sentence and the route ordinals are unchanged.
  • docs/plans/2026-08-11-s20a-lifecycle-closed-beta.md (count correction, decided on #1599 (closed)): Step 20b's merge-order note now records that #937 (closed)'s maven_packages level and the Maven metadata level merged before this step, and that the level count ends at thirteen. The Files and Acceptance counts now state what the rebased step ships: twelve artifactTombstoneCountTables entries, thirteen purge levels, and a HELP string that goes from nine to twelve. The bullets for purge.md, chunk.go and chunk.md now say that this step neither created nor changed them.
  • docs/specs/S20-a-lifecycle-closed-beta.md: each site that recorded the remote parent scan levels as owed now records them as delivered. These sites are the inventory note, the maven_packages passage, the discovery-path lead and its bound-table row, the unreachable-parent passage, the owed-levels section, the rejected-shape bullet, the container remote authority sentence, the scan-coverage note and the Follow-ups entry. This MR adds no acceptance criterion, and the four-table index criterion is unchanged.
  • docs/specs/S17-rest-management-api.md, Follow-ups, "S20-A amendment: three eviction scan levels, and the reachable destructive=false branch": the bullet said that the S20-A scan inventory stops at version level for the remote tables. The bullet now records the scan-level half as delivered by S20-A plan Steps 20a and 20b. The destructive=false half stays in the bullet as still owed, because the S20-A section on the unreachable destructive=false branch is unchanged.
  • docs/specs/S17-rest-management-api.md, Follow-ups, "Container remote eviction tombstones are unreapable today": the bullet said that ContainerRemoteImageReaper and ContainerRemoteManifestReaper have no composition-root wiring, and that no discovery scan reads container_remote_images. The bullet now records that the purger reaps these tombstones, and that the container_remote_images scan reaches them while the repository stands.

Database review

This MR adds no migration. It adds ten new statements:

  • Six scan statements: a page statement and a count statement for each of the three new scans.
  • Three resolve statements: one for each new resolveReapRepository route.
  • One freed-size statement: the npm remote size family of sumFreedRepositoryBytes.

Acceptance item 2 asks that each scan statement prunes to one namespace partition on the discovery index of its table. No Go test asserts a query plan, so the database review of this MR records the plans of all ten statements.

Database Review Evidence

These plans were recorded at 283017a38 on PostgreSQL 17.10. The statements are the same at the current head: the later commits change comments only, and main brought no migration.

Queries

Note

Plans are from EXPLAIN (ANALYZE, BUFFERS) against an ephemeral PostgreSQL 17 container (matching GL_PG_CURR_VERSION from .gitlab-ci-other-versions.yml), with synthesized seed data rolled back per query and the container torn down at the end of the run. Numbers reflect moderate cardinality and do not capture production-scale effects. See Database review evidence for seed sizing, methodology, and the anomalies the skill flags. Expand each row's details for the seed shape, rendered SQL, bound args, and raw plan.

Method Plan node Index Rows (plan / actual) Cost Time Buffers (hit / read) Partitions
datastore.CountTombstonesByLevel.container_remote_images_select_tombstone_count Aggregate container_remote_images_p29_namespace_id_soft_deleted_at_id_idx 1 / 1 81.01 0.099ms 3 / 0 ⚠️ 64/64
datastore.CountTombstonesByLevel.maven_remote_packages_select_tombstone_count Aggregate maven_remote_packages_p29_namespace_id_soft_deleted_at_idx 1 / 1 81.01 0.105ms 3 / 0 ⚠️ 64/64
datastore.CountTombstonesByLevel.npm_remote_packages_select_tombstone_count Aggregate npm_remote_packages_p37_namespace_id_soft_deleted_at_idx 1 / 1 89.15 0.125ms 7 / 0 ⚠️ 64/64
datastore.ScanTombstonedContainerRemoteImages Limit container_remote_images_p29_namespace_id_soft_deleted_at_id_idx 50 / 50 72.15 0.019ms 2 / 0 1/64
datastore.ScanTombstonedMavenRemotePackages Limit maven_remote_packages_p29_namespace_id_soft_deleted_at_idx 50 / 50 72.15 0.015ms 3 / 0 1/64
datastore.ScanTombstonedNpmRemotePackages Limit npm_remote_packages_p37_namespace_id_soft_deleted_at_idx 50 / 50 72.15 0.017ms 3 / 0 1/64
datastore.resolveReapRepository.container_remote_images_select_reap_repository Limit container_remote_images_p29_pkey 1 / 1 9.33 0.013ms 4 / 0 1/64, 1/64
datastore.resolveReapRepository.maven_remote_packages_select_reap_repository Limit maven_remote_packages_p29_pkey 1 / 1 9.33 0.014ms 4 / 0 1/64, 1/64
datastore.resolveReapRepository.npm_remote_packages_select_reap_repository Limit npm_remote_packages_p37_pkey 1 / 1 9.33 0.013ms 4 / 0 1/64, 1/64
datastore.sumFreedRepositoryBytes.blob_storage_blobs_by_namespace_select_reap_freed_size_npm_remote Aggregate npm_remote_files_p37_namespace_id_blob_sha256_idx, npm_remote_metadata_files_p37_namespace_id_blob_sha256_idx, blob_storage_blobs_by_namespace_p37_pkey 1 / 1 113.17 0.101ms 88 / 0 1/64 each table

Warning

Partition fan-out. These statements scan more than one partition of a hash-partitioned table:

  • datastore.CountTombstonesByLevel.container_remote_images_select_tombstone_count: 64/64 partitions of container_remote_images. The count is a fleet-wide gauge input with no namespace, the same shape as the existing count levels; the partial soft_deleted_at IS NOT NULL index serves the populated partition and empty partitions cost nothing. Accepted.
  • datastore.CountTombstonesByLevel.maven_remote_packages_select_tombstone_count: 64/64 partitions of maven_remote_packages. Same reason. Accepted.
  • datastore.CountTombstonesByLevel.npm_remote_packages_select_tombstone_count: 64/64 partitions of npm_remote_packages. Same reason. Accepted.
datastore.CountTombstonesByLevel.container_remote_images_select_tombstone_count

Summary: Plan matches the method's intent: a fleet-wide count with no namespace predicate, so an Append over all 64 partitions, by design. The populated partition is read through a Bitmap Index Scan on the partial discovery index. Planning (16ms, cold catalog) dominates execution (0.26ms); the caller floors the call rate.

Seed shape: namespaces=1, repositories=2, container_remote_repositories=1, container_remote_images=5000 (100 tombstoned)

Rendered SQL:

SELECT COUNT(*)
FROM public.container_remote_images
WHERE container_remote_images.soft_deleted_at IS NOT NULL;

Bound args: []

Plan (EXPLAIN (ANALYZE, BUFFERS) output; 63 empty-partition lines elided):

 Aggregate  (cost=81.00..81.01 rows=1 width=8) (actual time=0.096..0.099 rows=1 loops=1)
   Buffers: shared hit=3
   ->  Append  (cost=0.00..80.59 rows=163 width=0) (actual time=0.050..0.095 rows=100 loops=1)
         Buffers: shared hit=3
         -- 63 empty partitions: Seq Scan on container_remote_images_pNN (cost=0.00..0.00 rows=1 width=0) (rows=0 loops=1), Filter: (soft_deleted_at IS NOT NULL)
         ->  Bitmap Heap Scan on container_remote_images_p29 container_remote_images_30  (cost=8.67..79.78 rows=100 width=0) (actual time=0.016..0.021 rows=100 loops=1)
               Recheck Cond: (soft_deleted_at IS NOT NULL)
               Heap Blocks: exact=2
               Buffers: shared hit=3
               ->  Bitmap Index Scan on container_remote_images_p29_namespace_id_soft_deleted_at_id_idx  (cost=0.00..8.64 rows=100 width=0) (actual time=0.011..0.011 rows=100 loops=1)
                     Buffers: shared hit=1
 Planning:
   Buffers: shared hit=2659 read=252
 Planning Time: 15.959 ms
 Execution Time: 0.255 ms

Timings: planning 15.959ms, execution 0.255ms, total 16.214ms.

datastore.CountTombstonesByLevel.maven_remote_packages_select_tombstone_count

Summary: Plan matches the method's intent: a fleet-wide count over all 64 partitions, by design. The populated partition is read through a Bitmap Index Scan on the partial discovery index. Planning (17ms, cold catalog) dominates execution (0.35ms).

Seed shape: namespaces=1, repositories=2, maven_remote_repositories=1, maven_remote_packages=5000 (100 tombstoned)

Rendered SQL:

SELECT COUNT(*)
FROM public.maven_remote_packages
WHERE maven_remote_packages.soft_deleted_at IS NOT NULL;

Bound args: []

Plan (EXPLAIN (ANALYZE, BUFFERS) output; 63 empty-partition lines elided):

 Aggregate  (cost=81.00..81.01 rows=1 width=8) (actual time=0.101..0.105 rows=1 loops=1)
   Buffers: shared hit=3
   ->  Append  (cost=0.00..80.59 rows=163 width=0) (actual time=0.052..0.100 rows=100 loops=1)
         Buffers: shared hit=3
         -- 63 empty partitions: Seq Scan on maven_remote_packages_pNN (cost=0.00..0.00 rows=1 width=0) (rows=0 loops=1), Filter: (soft_deleted_at IS NOT NULL)
         ->  Bitmap Heap Scan on maven_remote_packages_p29 maven_remote_packages_30  (cost=8.67..79.78 rows=100 width=0) (actual time=0.018..0.023 rows=100 loops=1)
               Recheck Cond: (soft_deleted_at IS NOT NULL)
               Heap Blocks: exact=2
               Buffers: shared hit=3
               ->  Bitmap Index Scan on maven_remote_packages_p29_namespace_id_soft_deleted_at_idx  (cost=0.00..8.64 rows=100 width=0) (actual time=0.011..0.011 rows=100 loops=1)
                     Buffers: shared hit=1
 Planning:
   Buffers: shared hit=2628 read=252
 Planning Time: 16.831 ms
 Execution Time: 0.348 ms

Timings: planning 16.831ms, execution 0.348ms, total 17.179ms.

datastore.CountTombstonesByLevel.npm_remote_packages_select_tombstone_count

Summary: Plan matches the method's intent: a fleet-wide count over all 64 partitions, by design. The populated partition is read through a Bitmap Index Scan on the partial discovery index; another partition with no tombstones uses an Index Only Scan. No anomaly beyond the accepted fan-out.

Seed shape: namespaces=1, repositories=1, npm_remote_repositories=1, npm_remote_packages=5000 (100 tombstoned), npm_remote_versions=5000, blob_storage_blobs=5500, blob_storage_attachments=5500, npm_remote_files=5000, npm_remote_metadata_files=500

Rendered SQL:

SELECT COUNT(*)
FROM public.npm_remote_packages
WHERE npm_remote_packages.soft_deleted_at IS NOT NULL;

Bound args: []

Plan (EXPLAIN (ANALYZE, BUFFERS) output; 62 empty-partition lines elided):

 Aggregate  (cost=89.14..89.15 rows=1 width=8) (actual time=0.123..0.125 rows=1 loops=1)
   Buffers: shared hit=7
   ->  Append  (cost=0.00..88.73 rows=163 width=0) (actual time=0.078..0.122 rows=100 loops=1)
         Buffers: shared hit=7
         -- 62 empty partitions: Seq Scan on npm_remote_packages_pNN (cost=0.00..0.00 rows=1 width=0) (rows=0 loops=1), Filter: (soft_deleted_at IS NOT NULL)
         ->  Index Only Scan using npm_remote_packages_p19_namespace_id_soft_deleted_at_idx on npm_remote_packages_p19 npm_remote_packages_20  (cost=0.12..8.14 rows=1 width=0) (actual time=0.022..0.022 rows=0 loops=1)
               Heap Fetches: 100
               Buffers: shared hit=4
         ->  Bitmap Heap Scan on npm_remote_packages_p37 npm_remote_packages_38  (cost=8.67..79.78 rows=100 width=0) (actual time=0.015..0.020 rows=100 loops=1)
               Recheck Cond: (soft_deleted_at IS NOT NULL)
               Heap Blocks: exact=2
               Buffers: shared hit=3
               ->  Bitmap Index Scan on npm_remote_packages_p37_namespace_id_soft_deleted_at_idx  (cost=0.00..8.64 rows=100 width=0) (actual time=0.010..0.010 rows=100 loops=1)
                     Buffers: shared hit=1
 Planning:
   Buffers: shared hit=1840
 Planning Time: 5.593 ms
 Execution Time: 0.287 ms

Timings: planning 5.593ms, execution 0.287ms, total 5.880ms.

datastore.ScanTombstonedContainerRemoteImages

Summary: Plan matches the method's intent: Index Only Scan Backward over the partial (namespace_id, soft_deleted_at DESC, id DESC) discovery index, pruned to one of 64 partitions, giving oldest tombstone first with no Sort. Actual rows match the estimate (50 / 50). No anomalies.

Seed shape: namespaces=1, repositories=2, container_remote_repositories=1, container_remote_images=5000 (50 older than the window, 50 inside it)

Rendered SQL:

SELECT container_remote_images.namespace_id AS "tombstone_row.namespace_id",
     container_remote_images.id AS "tombstone_row.id",
     container_remote_images.soft_deleted_at AS "tombstone_row.soft_deleted_at"
FROM public.container_remote_images
WHERE (container_remote_images.namespace_id = $1::uuid) AND (container_remote_images.soft_deleted_at < (NOW() - INTERVAL '7 DAY'))
ORDER BY container_remote_images.soft_deleted_at ASC
LIMIT $2;

Bound args: [a44ab74a-7e8b-7f91-84af-024669432c19, 100] (window 7 days, inlined)

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.15..72.15 rows=50 width=40) (actual time=0.013..0.019 rows=50 loops=1)
   Buffers: shared hit=2
   ->  Index Only Scan Backward using container_remote_images_p29_namespace_id_soft_deleted_at_id_idx on container_remote_images_p29 container_remote_images  (cost=0.15..72.15 rows=50 width=40) (actual time=0.012..0.016 rows=50 loops=1)
         Index Cond: ((namespace_id = 'a44ab74a-7e8b-7f91-84af-024669432c19'::uuid) AND (soft_deleted_at < (now() - '7 days'::interval)))
         Heap Fetches: 50
         Buffers: shared hit=2
 Planning Time: 0.089 ms
 Execution Time: 0.029 ms

Timings: planning 0.089ms, execution 0.029ms, total 0.118ms.

datastore.ScanTombstonedMavenRemotePackages

Summary: Plan matches the method's intent: Index Scan Backward over the partial (namespace_id, soft_deleted_at DESC) discovery index, pruned to one of 64 partitions, with no Sort. Actual rows match the estimate (50 / 50). No anomalies.

Seed shape: namespaces=1, repositories=2, maven_remote_repositories=1, maven_remote_packages=5000 (50 older than the window, 50 inside it)

Rendered SQL:

SELECT maven_remote_packages.namespace_id AS "tombstone_row.namespace_id",
     maven_remote_packages.id AS "tombstone_row.id",
     maven_remote_packages.soft_deleted_at AS "tombstone_row.soft_deleted_at"
FROM public.maven_remote_packages
WHERE (maven_remote_packages.namespace_id = $1::uuid) AND (maven_remote_packages.soft_deleted_at < (NOW() - INTERVAL '7 DAY'))
ORDER BY maven_remote_packages.soft_deleted_at ASC
LIMIT $2;

Bound args: [a44ab74a-7e8b-7f91-84af-024669432c19, 100] (window 7 days, inlined)

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.15..72.15 rows=50 width=40) (actual time=0.010..0.015 rows=50 loops=1)
   Buffers: shared hit=3
   ->  Index Scan Backward using maven_remote_packages_p29_namespace_id_soft_deleted_at_idx on maven_remote_packages_p29 maven_remote_packages  (cost=0.15..72.15 rows=50 width=40) (actual time=0.010..0.013 rows=50 loops=1)
         Index Cond: ((namespace_id = 'a44ab74a-7e8b-7f91-84af-024669432c19'::uuid) AND (soft_deleted_at < (now() - '7 days'::interval)))
         Buffers: shared hit=3
 Planning Time: 0.090 ms
 Execution Time: 0.028 ms

Timings: planning 0.090ms, execution 0.028ms, total 0.118ms.

datastore.ScanTombstonedNpmRemotePackages

Summary: Plan matches the method's intent: Index Scan Backward over the partial (namespace_id, soft_deleted_at DESC) discovery index, pruned to one of 64 partitions, with no Sort. Actual rows match the estimate (50 / 50). No anomalies.

Seed shape: namespaces=1, repositories=1, npm_remote_repositories=1, npm_remote_packages=5000 (50 older than the window, 50 inside it), npm_remote_versions=5000, blob_storage_blobs=5500, blob_storage_attachments=5500, npm_remote_files=5000, npm_remote_metadata_files=500

Rendered SQL:

SELECT npm_remote_packages.namespace_id AS "tombstone_row.namespace_id",
     npm_remote_packages.id AS "tombstone_row.id",
     npm_remote_packages.soft_deleted_at AS "tombstone_row.soft_deleted_at"
FROM public.npm_remote_packages
WHERE (npm_remote_packages.namespace_id = $1::uuid) AND (npm_remote_packages.soft_deleted_at < (NOW() - INTERVAL '7 DAY'))
ORDER BY npm_remote_packages.soft_deleted_at ASC
LIMIT $2;

Bound args: [578e6a0f-45ac-7336-bc03-01d2db2b7e7b, 100] (window 7 days, inlined)

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.15..72.15 rows=50 width=40) (actual time=0.011..0.017 rows=50 loops=1)
   Buffers: shared hit=3
   ->  Index Scan Backward using npm_remote_packages_p37_namespace_id_soft_deleted_at_idx on npm_remote_packages_p37 npm_remote_packages  (cost=0.15..72.15 rows=50 width=40) (actual time=0.011..0.015 rows=50 loops=1)
         Index Cond: ((namespace_id = '578e6a0f-45ac-7336-bc03-01d2db2b7e7b'::uuid) AND (soft_deleted_at < (now() - '7 days'::interval)))
         Buffers: shared hit=3
 Planning Time: 0.080 ms
 Execution Time: 0.027 ms

Timings: planning 0.080ms, execution 0.027ms, total 0.107ms.

datastore.resolveReapRepository.container_remote_images_select_reap_repository

Summary: Plan matches the method's intent: primary-key Index Scan on the image by (id, namespace_id), then a one-row join to its remote repository, both pruned to one of 64 partitions. The Seq Scan is on a one-row remote-repository partition and is the cheaper path there. No anomalies.

Seed shape: namespaces=1, repositories=2, container_remote_repositories=1, container_remote_images=5000

Rendered SQL:

SELECT r.repository_id
		FROM container_remote_images i
		JOIN container_remote_repositories r
			ON r.id = i.container_remote_repository_id AND r.namespace_id = i.namespace_id
		WHERE i.namespace_id = $1 AND r.namespace_id = $1
		  AND i.id = $2
		LIMIT 1

Bound args: [a44ab74a-7e8b-7f91-84af-024669432c19, 86738c0e-7caa-73d0-b4b9-c5d367339ba4]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.28..9.33 rows=1 width=16) (actual time=0.013..0.013 rows=1 loops=1)
   Buffers: shared hit=4
   ->  Nested Loop  (cost=0.28..9.33 rows=1 width=16) (actual time=0.012..0.012 rows=1 loops=1)
         Join Filter: (r.id = i.container_remote_repository_id)
         Buffers: shared hit=4
         ->  Index Scan using container_remote_images_p29_pkey on container_remote_images_p29 i  (cost=0.28..8.30 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=1)
               Index Cond: ((id = '86738c0e-7caa-73d0-b4b9-c5d367339ba4'::uuid) AND (namespace_id = 'a44ab74a-7e8b-7f91-84af-024669432c19'::uuid))
               Buffers: shared hit=3
         ->  Seq Scan on container_remote_repositories_p29 r  (cost=0.00..1.01 rows=1 width=48) (actual time=0.002..0.002 rows=1 loops=1)
               Filter: (namespace_id = 'a44ab74a-7e8b-7f91-84af-024669432c19'::uuid)
               Buffers: shared hit=1
 Planning:
   Buffers: shared hit=278
 Planning Time: 0.863 ms
 Execution Time: 0.022 ms

Timings: planning 0.863ms, execution 0.022ms, total 0.885ms.

datastore.resolveReapRepository.maven_remote_packages_select_reap_repository

Summary: Plan matches the method's intent: primary-key Index Scan on the package, then a one-row join to its remote repository, both pruned to one of 64 partitions. No anomalies.

Seed shape: namespaces=1, repositories=2, maven_remote_repositories=1, maven_remote_packages=5000

Rendered SQL:

SELECT r.repository_id
		FROM maven_remote_packages p
		JOIN maven_remote_repositories r
			ON r.id = p.maven_remote_repository_id AND r.namespace_id = p.namespace_id
		WHERE p.namespace_id = $1 AND r.namespace_id = $1
		  AND p.id = $2
		LIMIT 1

Bound args: [a44ab74a-7e8b-7f91-84af-024669432c19, 6176a2b9-aff0-70f4-8542-a088f1d95c03]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.28..9.33 rows=1 width=16) (actual time=0.014..0.014 rows=1 loops=1)
   Buffers: shared hit=4
   ->  Nested Loop  (cost=0.28..9.33 rows=1 width=16) (actual time=0.013..0.013 rows=1 loops=1)
         Join Filter: (r.id = p.maven_remote_repository_id)
         Buffers: shared hit=4
         ->  Index Scan using maven_remote_packages_p29_pkey on maven_remote_packages_p29 p  (cost=0.28..8.30 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=1)
               Index Cond: ((id = '6176a2b9-aff0-70f4-8542-a088f1d95c03'::uuid) AND (namespace_id = 'a44ab74a-7e8b-7f91-84af-024669432c19'::uuid))
               Buffers: shared hit=3
         ->  Seq Scan on maven_remote_repositories_p29 r  (cost=0.00..1.01 rows=1 width=48) (actual time=0.003..0.003 rows=1 loops=1)
               Filter: (namespace_id = 'a44ab74a-7e8b-7f91-84af-024669432c19'::uuid)
               Buffers: shared hit=1
 Planning:
   Buffers: shared hit=314
 Planning Time: 0.943 ms
 Execution Time: 0.025 ms

Timings: planning 0.943ms, execution 0.025ms, total 0.968ms.

datastore.resolveReapRepository.npm_remote_packages_select_reap_repository

Summary: Plan matches the method's intent: primary-key Index Scan on the package, then a one-row join to its remote repository, both pruned to one of 64 partitions. No anomalies.

Seed shape: namespaces=1, repositories=1, npm_remote_repositories=1, npm_remote_packages=5000, npm_remote_versions=5000, blob_storage_blobs=5500, blob_storage_attachments=5500, npm_remote_files=5000, npm_remote_metadata_files=500

Rendered SQL:

SELECT r.repository_id
		FROM npm_remote_packages p
		JOIN npm_remote_repositories r
			ON r.id = p.npm_remote_repository_id AND r.namespace_id = p.namespace_id
		WHERE p.namespace_id = $1 AND r.namespace_id = $1
		  AND p.id = $2
		LIMIT 1

Bound args: [578e6a0f-45ac-7336-bc03-01d2db2b7e7b, f70b1d09-227f-79e7-b8e0-0ca49715be98]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.28..9.33 rows=1 width=16) (actual time=0.012..0.013 rows=1 loops=1)
   Buffers: shared hit=4
   ->  Nested Loop  (cost=0.28..9.33 rows=1 width=16) (actual time=0.012..0.012 rows=1 loops=1)
         Join Filter: (r.id = p.npm_remote_repository_id)
         Buffers: shared hit=4
         ->  Index Scan using npm_remote_packages_p37_pkey on npm_remote_packages_p37 p  (cost=0.28..8.30 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=1)
               Index Cond: ((id = 'f70b1d09-227f-79e7-b8e0-0ca49715be98'::uuid) AND (namespace_id = '578e6a0f-45ac-7336-bc03-01d2db2b7e7b'::uuid))
               Buffers: shared hit=3
         ->  Seq Scan on npm_remote_repositories_p37 r  (cost=0.00..1.01 rows=1 width=48) (actual time=0.003..0.003 rows=1 loops=1)
               Filter: (namespace_id = '578e6a0f-45ac-7336-bc03-01d2db2b7e7b'::uuid)
               Buffers: shared hit=1
 Planning:
   Buffers: shared hit=277
 Planning Time: 0.839 ms
 Execution Time: 0.024 ms

Timings: planning 0.839ms, execution 0.024ms, total 0.863ms.

datastore.sumFreedRepositoryBytes.blob_storage_blobs_by_namespace_select_reap_freed_size_npm_remote

Summary: Plan matches the method's intent: the caller's digests are de-duplicated (HashAggregate over unnest), each is probed by primary key in blob_storage_blobs_by_namespace, and both NOT EXISTS legs run as Nested Loop Anti Joins driven by the (namespace_id, blob_sha256) indexes on npm_remote_files and npm_remote_metadata_files. Every table is pruned to one of 64 partitions and the work grows with the digest count, not the table size. The plan shown is the reaper's shape (the package's file rows already deleted), so all 11 digests are summed (41294 bytes, the seeded sizes); with the rows still present the same plan returns 0 rows at 0.143ms. No anomalies.

Seed shape: namespaces=1, repositories=1, npm_remote_repositories=1, npm_remote_packages=5000, npm_remote_versions=5000, blob_storage_blobs=5500, blob_storage_attachments=5500, npm_remote_files=5000, npm_remote_metadata_files=500 (blob_storage_blobs_by_namespace=5500 through the blob_storage_blobs trigger); then the reaped package's 10 npm_remote_files rows and 1 npm_remote_metadata_files row deleted.

Rendered SQL:

SELECT COALESCE(SUM(b.size), 0)::bigint
		FROM (SELECT DISTINCT u.sha256 FROM unnest($3::bytea[]) AS u(sha256)) AS d
		JOIN blob_storage_blobs_by_namespace b
			ON b.namespace_id = $1 AND b.sha256 = d.sha256
		WHERE NOT EXISTS (
			SELECT 1
			FROM npm_remote_files f
			JOIN npm_remote_versions v
				ON v.id = f.npm_remote_version_id AND v.namespace_id = f.namespace_id
			JOIN npm_remote_packages p
				ON p.id = v.npm_remote_package_id AND p.namespace_id = v.namespace_id
			JOIN npm_remote_repositories r
				ON r.id = p.npm_remote_repository_id AND r.namespace_id = p.namespace_id
			WHERE f.namespace_id = $1 AND v.namespace_id = $1 AND p.namespace_id = $1
			  AND r.namespace_id = $1
			  AND r.repository_id = $2 AND f.blob_sha256 = d.sha256
		)
		AND NOT EXISTS (
			SELECT 1
			FROM npm_remote_metadata_files mf
			JOIN npm_remote_packages p
				ON p.id = mf.npm_remote_package_id AND p.namespace_id = mf.namespace_id
			JOIN npm_remote_repositories r
				ON r.id = p.npm_remote_repository_id AND r.namespace_id = p.namespace_id
			WHERE mf.namespace_id = $1 AND p.namespace_id = $1 AND r.namespace_id = $1
			  AND r.repository_id = $2 AND mf.blob_sha256 = d.sha256
		)

Bound args: [578e6a0f-45ac-7336-bc03-01d2db2b7e7b, b1b3135f-282f-7833-912a-2e5a15416dca, {\x…05b1, \x…0978, \x…136d, \x…04b4, \x…121e, \x…035c, \x…0a1c, \x…101e, \x…0023, \x…0a73, \x…13ba}] (11 digests, each 32 bytes, zero-padded on the left: the reaped package's 10 tarballs and its packument)

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Aggregate  (cost=113.16..113.17 rows=1 width=8) (actual time=0.100..0.101 rows=1 loops=1)
   Buffers: shared hit=88
   ->  Nested Loop Anti Join  (cost=2.07..113.15 rows=1 width=8) (actual time=0.035..0.097 rows=11 loops=1)
         Buffers: shared hit=88
         ->  Nested Loop  (cost=1.39..106.45 rows=1 width=40) (actual time=0.028..0.080 rows=11 loops=1)
               Buffers: shared hit=65
               ->  Nested Loop Anti Join  (cost=1.11..98.13 rows=1 width=32) (actual time=0.018..0.042 rows=11 loops=1)
                     Buffers: shared hit=32
                     ->  HashAggregate  (cost=0.14..0.25 rows=11 width=32) (actual time=0.006..0.007 rows=11 loops=1)
                           Group Key: u.sha256
                           Batches: 1  Memory Usage: 24kB
                           ->  Function Scan on unnest u  (cost=0.00..0.11 rows=11 width=32) (actual time=0.003..0.003 rows=11 loops=1)
                     ->  Nested Loop  (cost=0.97..8.89 rows=1 width=33) (actual time=0.003..0.003 rows=0 loops=11)
                           Buffers: shared hit=32
                           ->  Nested Loop  (cost=0.85..8.73 rows=1 width=65) (actual time=0.003..0.003 rows=0 loops=11)
                                 Buffers: shared hit=32
                                 ->  Nested Loop  (cost=0.56..8.33 rows=1 width=65) (actual time=0.003..0.003 rows=0 loops=11)
                                       Buffers: shared hit=32
                                       ->  Index Scan using npm_remote_files_p37_namespace_id_blob_sha256_idx on npm_remote_files_p37 f  (cost=0.28..7.94 rows=1 width=65) (actual time=0.003..0.003 rows=0 loops=11)
                                             Index Cond: ((namespace_id = '578e6a0f-45ac-7336-bc03-01d2db2b7e7b'::uuid) AND (blob_sha256 = u.sha256))
                                             Buffers: shared hit=32
                                       ->  Index Scan using npm_remote_versions_p37_pkey on npm_remote_versions_p37 v  (cost=0.28..0.38 rows=1 width=48) (never executed)
                                             Index Cond: ((id = f.npm_remote_version_id) AND (namespace_id = '578e6a0f-45ac-7336-bc03-01d2db2b7e7b'::uuid))
                                 ->  Index Scan using npm_remote_packages_p37_pkey on npm_remote_packages_p37 p  (cost=0.28..0.39 rows=1 width=48) (never executed)
                                       Index Cond: ((id = v.npm_remote_package_id) AND (namespace_id = '578e6a0f-45ac-7336-bc03-01d2db2b7e7b'::uuid))
                           ->  Index Scan using npm_remote_repositories_p37_pkey on npm_remote_repositories_p37 r  (cost=0.12..0.15 rows=1 width=32) (never executed)
                                 Index Cond: ((id = p.npm_remote_repository_id) AND (namespace_id = '578e6a0f-45ac-7336-bc03-01d2db2b7e7b'::uuid))
                                 Filter: (repository_id = 'b1b3135f-282f-7833-912a-2e5a15416dca'::uuid)
               ->  Index Only Scan using blob_storage_blobs_by_namespace_p37_pkey on blob_storage_blobs_by_namespace_p37 b  (cost=0.28..8.30 rows=1 width=41) (actual time=0.003..0.003 rows=1 loops=11)
                     Index Cond: ((namespace_id = '578e6a0f-45ac-7336-bc03-01d2db2b7e7b'::uuid) AND (sha256 = u.sha256))
                     Heap Fetches: 11
                     Buffers: shared hit=33
         ->  Nested Loop  (cost=0.68..6.70 rows=1 width=33) (actual time=0.001..0.001 rows=0 loops=11)
               Buffers: shared hit=23
               ->  Nested Loop  (cost=0.55..6.54 rows=1 width=65) (actual time=0.001..0.001 rows=0 loops=11)
                     Buffers: shared hit=23
                     ->  Index Scan using npm_remote_metadata_files_p37_namespace_id_blob_sha256_idx on npm_remote_metadata_files_p37 mf  (cost=0.27..5.38 rows=1 width=65) (actual time=0.001..0.001 rows=0 loops=11)
                           Index Cond: ((namespace_id = '578e6a0f-45ac-7336-bc03-01d2db2b7e7b'::uuid) AND (blob_sha256 = u.sha256))
                           Buffers: shared hit=23
                     ->  Index Scan using npm_remote_packages_p37_pkey on npm_remote_packages_p37 p_1  (cost=0.28..1.14 rows=1 width=48) (never executed)
                           Index Cond: ((id = mf.npm_remote_package_id) AND (namespace_id = '578e6a0f-45ac-7336-bc03-01d2db2b7e7b'::uuid))
               ->  Index Scan using npm_remote_repositories_p37_pkey on npm_remote_repositories_p37 r_1  (cost=0.12..0.15 rows=1 width=32) (never executed)
                     Index Cond: ((id = p_1.npm_remote_repository_id) AND (namespace_id = '578e6a0f-45ac-7336-bc03-01d2db2b7e7b'::uuid))
                     Filter: (repository_id = 'b1b3135f-282f-7833-912a-2e5a15416dca'::uuid)
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.917 ms
 Execution Time: 0.163 ms

Timings: planning 0.917ms, execution 0.163ms, total 1.080ms.

Stack

This MR is part 5 of 5. Before the split, this MR held all five parts. The parts merge in this order, and each MR targets the source branch of the MR before it:

  1. !3399 (merged): the Danger exemption for comment-only Go changes.
  2. !3401 (merged): the resolve routes and the npm remote size family.
  3. !3402 (merged): the three reapers report freed bytes.
  4. !3403 (merged): the three tombstone discovery scans.
  5. This MR: the three purge levels, the gauge tables, and the documentation.

When all five merge, main holds the same tree as this MR held before the split (7f7e265d6), rebased onto !3399 (merged). Parts 3 and 4 use interim wording in the documents and comments that this MR rewrites, so that no part refers to a symbol that a later part adds.

Size

Against !3403 (merged), this MR has 802 reviewable lines: production Go 253, Go tests 316, Markdown 233. Of the production Go lines, about 150 are the ContainerRemoteImageReaper doc comment that moves to docs/dev/storage-accounting.md. The documents must change in the same MR as the levels, because the levels make their statements false.

Merge order

!3348 (merged), !3354 (merged) and !3355 (merged) each add a QueryNames() list in internal/datastore/query_names.go. Whichever of this stack and those MRs merges second must add the ten new query names of this stack to that list. The ten names are queryBlobStorageBlobsByNamespaceSelectReapFreedSizeNpmRemote, the three ...SelectReapRepository names, the three ...SelectTombstoneCount names and the three ...SelectTombstonePage names.

User documentation

No docs/user/ page changes: eviction now lowers a repository's size_bytes, but no page under docs/user/ mentions eviction.

What remains

The acceptance items for this MR came from the enricher, not from the issue, so this MR relates to the issue and does not close it. The plans of all ten new statements, which include the six scan statements of acceptance item 2, go in the database review of this MR.

Related to #728 (closed)

This is a bot message 🤖 — /smurfit

Edited by Pawel Rozlach

Merge request reports

Loading
Loading