chore(datastore): reap the Maven remote-cache subtree (S20-A plan: 11/21)

What this step delivers

Reap logic for the Maven remote-cache subtree: maven_remote_packages, maven_remote_versions, maven_remote_files, and their blob_storage_attachments rows.

Two exported reapers implement the existing Reaper interface:

• MavenRemoteVersionReaper drains the files beneath one tombstoned cache version.
• MavenRemotePackageReaper drains the version-less files beneath one tombstoned cache package.

Each arm works in bounded chunks. Each arm removes an attachment row in the same transaction as the row that referenced it.

The step adds four query-name constants. The instrumentation test requires one name per statement.

No caller reaches either reaper yet.

This merge request was stacked on step 9

Step 11 built on step 9, which is merge request !1702 (merged). !1702 (merged) merged as f79239807, so the stack is resolved.

Where the branch stands now:

• The target is main.
• This branch sits on main at d1a51045f. The rebase dropped the fifteen step-9 commits the branch carried, because f79239807 already carries their content.
• The diff is step 11's own work alone: three files and 1657 added lines.

Step 11 uses two symbols that step 9 added, and both are on main:

• internal/datastore/lifecycle_reap_maven.go, the hosted sibling that this code mirrors arm for arm.
• BlobStorageAttachmentStore.DeleteIfUnreferencedByMaven, the attachment guard.

The guard needed no change. It already correlates over maven_files and maven_remote_files, so a shared attachment goes only when the last reference to it goes.

Both parent-delete sites return ErrReapParentPinned through mapReapParentDeleteError. That value is on main now, with the name and shape this branch compiles against. So these arms report a pinned parent the same way the hosted arms do.

The rebase produced one conflict, in internal/datastore/query_names.go. main gained queryMavenRemotePackagesUpdateSoftDelete from d4aaf3320, the Maven remote eviction composers, where this branch adds queryMavenRemotePackagesDeleteReap. The resolution keeps both constants, in the order the sibling blocks use. Step 11's own content is unchanged: 1657 added lines and no removed line, the same set as before the rebase.

Diff size

The diff adds 1657 lines across three files:

File	Lines
internal/datastore/lifecycle_reap_maven_remote_integration_test.go	1127
internal/datastore/lifecycle_reap_maven_remote.go	526
internal/datastore/query_names.go	4

The test suite is 68 percent of the diff. A split does not help here. The suite is the acceptance evidence for this step, and the plan defines the step as two created files. A test-only merge request lands a file that compiles against nothing. An implementation-only merge request lands code with no coverage.

Runtime behavior

This step changes no runtime behavior.

• No caller reaches either reaper in a running service. No non-test consumer of Reaper exists in internal/ or cmd/ today, so this step has no registration site to join.
• The diff adds no route, handler, middleware, composition root, configuration key, proto file, OpenAPI file, Bruno file, or migration.
• The only new observable surface is four Prometheus name label values. No running code path emits them yet.

End-to-end scenarios

This step adds no scenario to docs/testing/.

The step type is chore, which the guardrail exempts. The substantive reason is stronger than the exemption. docs/testing/e2e/ holds README.md, docker.md, maven.md, npm.md, and oci.md. Each file puts remote and virtual repositories under an explicit out-of-scope heading until the capability ships. Four existing rows name a reap, but only as a background condition. All four are hosted-path rows and they keep their outcomes. A reap behind no request path has no user journey to add.

Spec coverage

Spec: docs/specs/S20-a-lifecycle-closed-beta.md Plan: docs/plans/2026-08-11-s20a-lifecycle-closed-beta.md, Step 11

Step 11 owns the Maven remote-cache slice of AC-18 and AC-20. Every other row is listed with the step that owns it, so nothing is silently dropped.

Acceptance criteria

#	Criterion	Tests
AC-1	A repositories tombstone older than retention_window is discovered and its subtree reaped; a newer one is not	Step 5 (repository scan and eligibility predicate). No reaper evaluates the predicate.
AC-2	A row with soft_deleted_at IS NULL is never discovered by any scan or walk	Discovery half: Steps 5-7. The walk half is a positive hit here: TestMavenRemoteVersionReaper_Reap/a_cache_version_row_is_reaped_in_either_marker_state, .../a_cache_file_is_reaped_in_either_marker_state, and the package arm's two equivalents assert a live row under a tombstone is reaped, which is what makes the scan the only discovery route.
AC-3	The repositories, npm_packages and container_images scans each prune to one partition, by EXPLAIN	Steps 5 and 7.
AC-4	The version-level scans prune to one partition, by EXPLAIN, per table	Step 6. index_maven_remote_versions_on_ns_id_soft_deleted_at is that step's positive hit, not this one's.
AC-5	A re-run purge on a fully reaped subtree is a no-op that reports success	Reap half: .../a_second_reap_of_a_drained_subtree_reports_no_rows_and_no_error and the package arm's .../a_second_reap_of_a_drained_cache_package_.... The mid-walk abort and its gofail seam are Steps 14-15.
AC-6	A tombstoned npm repository's walk reaps live and already-tombstoned rows in one pass	Step 10 for npm remote, Step 8 for npm hosted. The Maven remote analogue is the either-marker-state pair above.
AC-7	Every purge transaction is bounded, asserted by seeding more artifacts than one chunk holds	.../each_chunk_runs_in_one_transaction,_deletes_at_most_limit_file_rows,_and_draining_takes_more_than_one, both arms: 5 files at limit=2, the first chunk removes exactly one page, Rows equals the stated 2*limit ceiling, the parent row stands, and the drain takes more than one committed transaction.
AC-8	A purge job in a namespace that stopped serving writes refuses retryably before any row is reaped	Step 15a. No reaper reads the lifecycle columns.
AC-9	A tombstoned npm version is reaped with its files and each file's attachment	Step 8.
AC-10	Unpublish-then-republish before the reap leaves no orphaned row	Step 8.
AC-11	A tombstoned npm package is reaped with its tags, metadata files and their attachments	Step 8.
AC-12	NpmMetadataFileStore.DeleteNpmMetadataFiles removes each row's attachment at the unpublish call site too	Step 8.
AC-13	A tombstoned Maven version and a tombstoned Maven package are reaped with their files and attachments	Step 9 (lifecycle_reap_maven_integration_test.go). This suite is its remote-cache mirror.
AC-14	A tombstoned container repository is reaped through the existing per-artifact deleters	Steps 13-14.
AC-15	An image index and its children are reaped parent-first with no MANIFEST_REFERENCED refusal	Step 13.
AC-16	A tombstoned container_images row under a live repository is reaped through the same walk	Step 13.
AC-17	A tombstoned container repository's walk reaps live and already-tombstoned images in one pass	Step 13.
AC-18	A tombstoned remote-cache subtree is reaped for every remote table carrying the column, a positive hit per table, seeded by writing soft_deleted_at directly	This step, for the three maven_remote_* tables. maven_remote_versions: .../a_tombstoned_cache_version_is_reaped_with_its_files_and_each_file's_attachment and .../a_cache_version_row_is_reaped_in_either_marker_state (both marker values). maven_remote_packages: TestMavenRemotePackageReaper_Reap/a_tombstoned_cache_package_is_reaped_with_its_version-less_files_and_their_attachments and .../a_cache_package_row_is_reaped_in_either_marker_state (both marker values). maven_remote_files: reaped under both parents, at the version level in the first subtest and at the package level in the second, with .../a_cache_file_is_reaped_in_either_marker_state and .../a_version-less_cache_file_is_reaped_in_either_marker_state covering both of its own marker values. Every fixture writes soft_deleted_at directly, which is what AC-18 asks for. !1574 merged and gave these three tables a writer on main. That store has no production caller, so no request produces a Maven remote tombstone yet. This branch sits on a base older than !1574, so the suite has no writer to call. The npm_remote_* and container_remote_images slices are Steps 10 and 12.
AC-19	container_remote_manifests and container_remote_blobs are reaped the same way	Step 12.
AC-20	After a subtree is reaped, every blob it referenced has zero blob_storage_attachments rows, counted per sha256	This step, for the Maven remote-cache subtree. .../every_blob_the_subtree_referenced_ends_with_no_attachment_rows counts countAttachments per digest and asserts zero, and asserts nothing about blob_storage_blobs. The package arm's first subtest counts the same way for its two version-less files. .../an_attachment_two_cache_files_share_goes_only_when_the_last_of_them_goes is the negative half: a shared attachment survives the first reap and goes with the second.
AC-21	DELETE without destructive, or with a non-boolean value, returns 400	Step 18.
AC-22	destructive=false on an empty repository returns 204 and frees the name	Step 18.
AC-23	destructive=false on a non-empty repository returns 409 and changes nothing	Step 18.
AC-24	That 409's message names the remedy the caller has, per format and kind	Step 18.
AC-25	destructive=true on a non-empty repository returns 202, tombstones, and enqueues in one transaction	Steps 4 and 18.
AC-26	destructive=true on an empty repository returns 204 with no tombstone and no job	Step 18.
AC-27	Every client route naming a tombstoned repository returns 404	Step 3.
AC-28	A create at a tombstoned name returns 409 until the finalizer runs	Steps 4 and 14.
AC-29	The finalizer cannot succeed early with an artifact left in place	Step 14.
AC-30	A finalizer reaching an empty subtree removes the repository row, its format child, and its collection links	Step 14.
AC-31	A tombstoned repository with no live purge job is re-queued within one sweep_interval	Step 16.
AC-32	The sweep reads each namespace's tombstones oldest-first	Step 16.
AC-33	A tombstoned npm_packages row whose versions are all reaped is enumerated and re-queued	Steps 7 and 16.
AC-34	A repository with a live purge job is not re-queued	Step 16.
AC-35	A repository whose prior job reached a terminal state is re-queued	Step 16.
AC-36	A permanently failing purge stays counted and re-queued at the bounded cadence	Steps 16 and 17b.
AC-37	The sweep registers a worker for its periodic kind	Step 16.
AC-38	A namespace that does not serve writes has no tombstone enqueued, and is re-queued once the condition clears	Steps 15a and 16.
AC-39	ContainerRepositoryStore.FindByNameInNamespace returns ErrNotFound for a tombstoned repository, both formats	Step 3.
AC-40	RepositoryStore.FindByID returns ErrNotFound for a tombstoned repository	Step 3.
AC-41	RepositoryStore.Update returns ErrNotFound for a tombstoned repository	Step 3.
AC-42	Delete, the three counter writes, and Missing still act on or report a tombstoned row	Step 3.
AC-43	parentRepositoryIsActive returns ErrParentRepositoryMissing on a concurrent tombstone	Step 4.
AC-44	Each of the four container_images gates hides a tombstoned image	S17 Phase 4, Step 6.
AC-45	Every /v2/ route resolving a tombstoned image answers 404 NAME_UNKNOWN	S17 Phase 4.
AC-46	The blob mount route answers 202 when every image linking the blob in the source is tombstoned	S17 Phase 4.
AC-47	A tombstoned image named in from= still mounts and answers 201	S17 Phase 4.
AC-48	A mount into a tombstoned destination name answers 201	S17 Phase 4.
AC-49	UpsertContainerImage does not resurrect a tombstoned image	S17 Phase 4.
AC-50	ContainerImageStore.Delete still acts on a tombstoned row	S17 Phase 4.
AC-51	The destructive DELETE emits no counter delta at the tombstone write	Step 19.
AC-52	Δsize and Δcomponents fire at the purger, one format-dispatched site	Emission is Step 19's. The components half of the input is pinned here: .../the_totals_carry_one_component_and_no_size_delta asserts Components == 1 for a reaped cache version and .../the_totals_carry_no_component_and_no_size asserts zero for a reaped cache package. The size half is pinned as a gap rather than satisfied: both subtests assert SizeBytes == 0, the version one against a stamped size_bytes of 4242 so the zero cannot be an accident. This mirrors the hosted arm, whose doc states why: the figure the counter wants is repository-scoped and computable only while the deleted rows still exist. A reviewer who wants AC-52's size half closed has to close it at the reap layer, in both arms.
AC-53	Every emit site commits its source rows before issuing the delta	Step 19.
AC-54	A purge chunk emits from its own committed affected-row aggregate	Step 19. The aggregate's honesty is pinned here: every ReapTotals assertion reads a value the reap's own statements produced, and .../a_second_reap_of_a_drained_subtree_... asserts a chunk that affected nothing reports ReapTotals{}.
AC-55	A raw foreign-key cascade emits nothing and is not used	Container-side criterion, Steps 12-14. For Maven remote the equivalent is that no maven_remote_* foreign key declares an ON DELETE action, which the two 23503 subtests exercise from both directions.
AC-56	The two discovery-index migrations apply cleanly and every partition inherits them	Step 1.
AC-57	No migration adds or drops a column, and no CHECK pins any soft_deleted_at	Step 1.
AC-58	unique_container_images_ns_id_cr_id_name carries its partial predicate on parent and partitions	S17 Phase 4.
AC-59	Config load accepts a zero retention_window and rejects the invalid values	Step 2.

Error cases

#	Condition	Tests
E-1	DELETE omits destructive: 400	Step 18.
E-2	destructive carries a non-boolean value: 400	Step 18.
E-3	destructive=false on a non-empty repository: 409	Step 18.
E-4	An artifact is published between the DELETE and its DELETE FROM repositories	Steps 14 and 18.
E-5	DELETE on a missing or already-tombstoned repository: 404	Steps 3 and 18.
E-6	Create at a name held by a tombstoned repository: 409	Step 4.
E-7	Any client route naming a tombstoned repository: 404 in that route's vocabulary	Step 3.
E-8	A blob mount whose from= names a tombstoned repository: 202	S17 Phase 4.
E-9	Purge job: 23503 on a parent delete, from either of its two causes	Both causes are positive hits here, per arm. Permanent, an unreaped child nothing here removes: TestMavenRemotePackageReaper_Reap/a_cache_version_row_still_beneath_the_package_surfaces_the_foreign-key_violation. Transient, a sibling chunk that committed while this page waited on its locks: .../a_sibling_chunk's_committed_page_shortens_this_one,_and_the_cache_version_delete_raises_23503 and the package arm's .../and_the_cache_package_delete_raises_23503, both driven through startBlocked so the interleaving is event-based. Each asserts ErrReapParentPinned with require.ErrorIs, that the failed chunk reports ReapTotals{}, and that the chunk's own file page comes back with the rollback. The value names the class of refusal and not its cause. Persistence across re-queues separates the two, and only the caller sees that.
E-10	Purge job: ContainerManifestDeleter refuses a manifest as still referenced	Steps 13-14.
E-11	Purge job: the finalizer aborts because an artifact remains	Step 14.
E-12	Purge job: repository already hard-deleted when the job runs	Step 14. The reap-level analogue is a positive hit here: .../a_second_reap_of_a_drained_subtree_... and .../a_second_reap_of_a_drained_cache_package_... report success with ReapTotals{}.
E-13	Purge job: retention window changed between enqueue and run	Steps 5 and 15.
E-14	Sweep: enumeration query fails	Step 16.
E-15	Sweep: interval elapses before enumeration completes	Step 16.
E-16	Emit API unavailable at a purge or tombstone site	Step 19.
E-17	Parent gate: parent tombstoned between the gate and the child INSERT	Step 4.
E-18	A tombstoned coordinate reoccupied by a republished row before the reap	Step 8 (npm republish). The isolation half holds here: .../the_reap_does_not_widen_past_the_cache_version_row_it_was_handed and its package-arm twin assert a sibling version, a version-less row, another cache package, and another namespace all survive, so a reap keyed on one tombstone cannot reach a row that reoccupied a neighbouring coordinate.

Security considerations

#	Concern	Tests
S-1	Authorization unchanged; the purger holds no caller identity and its authority comes from the tombstone	No request surface in this step. The reaper takes a TombstoneRow and a transaction handle and reads no identity. Steps 16 and 18 own the entry points.
S-2	destructive is a confirmation, not an authorization control	Step 18.
S-3	Deletion is irreversible and the API must say so	Step 18 and the API documentation.
S-4	Input validation; no purger input is user-controlled	Positive hit: .../argument_guards_reject_a_zero-valued_call_before_any_statement_runs, both arms, drives every rejection through assertReapArgumentGuards and then asserts the named subtree is untouched, so no rejected call reached a statement. Every reap input is a uuid.UUID or an int; no text reaches a query.
S-5	No injection surface is added; all purger SQL is parameterized	The reap statements are jet-built and the attachment guard is the parameterized raw statement Step 9 landed. No test in this file concatenates into a reap statement, and seedMavenRemoteFileOnAttachment binds soft_deleted_at rather than splicing it, which is what keeps the fixture free of the G202 its siblings carry. Statement-shape pins are the implementation commit's.
S-6	Error messages must not become an existence oracle	Step 3 and Step 18. Reap-side: the 23503 subtests assert the error reaches the caller as ErrReapParentPinned. That value is returned bare, so PgError.Detail and PgError.Where do not travel out of the store. This is the identifier-free rule docs/dev/database-query-patterns.md states.
S-7	Purge failures are operationally visible; a purge that cannot complete must never report success	Positive hit for the reap layer: both 23503 subtests require the chunk to fail rather than report a subtree it did not drain, and assert the failed chunk accounts for ReapTotals{}. The gauge that surfaces a stalled purge is Step 17b.

Coverage gaps this step leaves open

Gap	Why
ReapTotals.SizeBytes is asserted zero rather than asserted to carry the reaped bytes	AC-52's size half. The hosted arm made the same call; the delta the counter wants is repository-scoped and derivable only from rows the chunk is about to delete. Pinned by test so a later change has to face it.
A mis-dispatched TombstoneRow is reported, not rejected	.../a_cache_package_id_handed_to_the_version_reaper_reaps_nothing_and_reports_no_error and its package-arm twin state the behavior. No argument guard can separate the ids, both being UUIDs, so closing it means a caller-side obligation in the Reaper contract rather than a change here.
The four dupl findings positioned in lifecycle_reap_maven_integration_test.go	Reported under --build-tags=integration against this file's mirrored subtests. The suppression on this side is in place; the other side sits in a file this step does not own.

Database Review Evidence

Migration mode did not run. The diff adds no file under internal/datastore/migrations/sql/ and changes no schema, so there is no apply or rollback timing to collect.

Queries

Note

Plans are from EXPLAIN (ANALYZE, BUFFERS) against an ephemeral PostgreSQL 17.10 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.MavenRemoteVersionReaper.Reap	Delete, Index Scan	maven_remote_versions_p26_id_maven_remote_package_id_namesp_idx	1 / 1	8.30	6.598ms	12 / 0	1/64
datastore.reapMavenRemoteChildPage.PackagePage	Delete, Hash Semi Join	n/a (Seq Scan, see notes)	100 / 100	245.27	1.466ms	365 / 0	1/64
datastore.reapMavenRemoteChildPage.VersionPage	Delete, Hash Semi Join	n/a (Seq Scan, see notes)	100 / 100	254.55	1.341ms	365 / 0	1/64
datastore.reapMavenRemotePackageRow	Delete, Index Scan	maven_remote_packages_p36_pkey	0 / 0	8.30	18.137ms	17 / 0	1/64

Every statement pruned to one partition of 64. No partition fan-out.

Query notes:

• datastore.reapMavenRemoteChildPage.VersionPage and .PackagePage: Seq Scan on the target partition at the 5000-row seed, in both the inner page SELECT and the outer DELETE. This is a cost artifact of the seed, not a missing index. The probes in #### Scale probes for the two file-page arms re-run both arms at 220000 file rows with a realistic spread. There the planner picks index_maven_remote_files_on_ns_id_ver_id for the version arm, index_maven_remote_files_on_ns_id_pkg_id for the package arm, and pk_maven_remote_files for the outer DELETE. Both of the predicate's equalities are index conditions in each case, which is what the two comments on the arms state.
• datastore.MavenRemoteVersionReaper.Reap: the planner picked unique_maven_remote_versions_id_pkg_id_ns_id over pk_maven_remote_versions. Both are single-row lookups at the same cost, and both take id and namespace_id as index conditions.
• datastore.MavenRemoteVersionReaper.Reap and datastore.reapMavenRemotePackageRow: most of the reported execution time is the foreign-key triggers, which run once per statement and carry first-call planning. The version-row delete spent 5.649ms of 6.598ms in one trigger; the package-row delete spent 16.323ms of 18.137ms in two. Both subtrees were already drained, so each trigger found no row.
• The file-page LIMIT is bounded. checkMavenReapArgs rejects limit <= 0 and limit > MaxLifecycleReapPageSize (1000) before any statement runs, so the page cannot grow with the table.
• The attachment removal each arm drives is BlobStorageAttachmentStore.DeleteIfUnreferencedByMaven. That statement is unchanged on this branch and belongs to the step 9 merge request, so it carries no plan here.

datastore.MavenRemoteVersionReaper.Reap

Summary: Plan matches the method's intent. A single-row index lookup on the cache version's whole primary key, pruned to one partition of 64 by the namespace_id equality. The reported execution time is dominated by the fk_maven_remote_files_version_belongs_to_package trigger, which the delete must run and which found no row because the file page had drained. No anomalies.

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

Rendered SQL:

DELETE FROM public.maven_remote_versions
WHERE (maven_remote_versions.namespace_id = $1::uuid) AND (maven_remote_versions.id = $2::uuid)
RETURNING maven_remote_versions.id AS "maven_remote_versions.id";

Bound args: [db61b7e6-e9b1-46d9-aa85-2e919b071b34, 2c5a0c34-a9b4-4e8e-a1f5-14338485a1be]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Delete on maven_remote_versions  (cost=0.28..8.30 rows=1 width=10) (actual time=0.112..0.114 rows=1 loops=1)
   Delete on maven_remote_versions_p26 maven_remote_versions_1
   Buffers: shared hit=12
   ->  Index Scan using maven_remote_versions_p26_id_maven_remote_package_id_namesp_idx on maven_remote_versions_p26 maven_remote_versions_1  (cost=0.28..8.30 rows=1 width=10) (actual time=0.031..0.033 rows=1 loops=1)
         Index Cond: ((id = '2c5a0c34-a9b4-4e8e-a1f5-14338485a1be'::uuid) AND (namespace_id = 'db61b7e6-e9b1-46d9-aa85-2e919b071b34'::uuid))
         Buffers: shared hit=3
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.213 ms
 Trigger for constraint maven_remote_files_maven_remote_version_id_maven_remote__fkey26 on maven_remote_versions_p26: time=5.649 calls=1
 Execution Time: 6.598 ms

Timings: planning 0.213ms, execution 6.598ms, total 6.811ms.

datastore.reapMavenRemoteChildPage.PackagePage

Summary: Plan matches the method's intent at scale, but not at this seed size. At 5000 rows the planner reads the whole partition twice over: a Seq Scan with an early stop for the inner page, and a Seq Scan feeding a Hash Semi Join for the outer delete. The scale probe at 220000 rows shows the intended shape, index_maven_remote_files_on_ns_id_pkg_id with both equalities as index conditions and maven_remote_version_id IS NULL as a filter, driving a nested loop over pk_maven_remote_files. The namespace_id literal prunes to one partition of 64 in both cases.

Seed shape: namespaces=1, repositories=1, maven_remote_repositories=1, maven_remote_packages=1, maven_remote_versions=1, blob_storage_blobs=5000, blob_storage_attachments=5000, maven_remote_files=5000

The 5000 cache file rows split 2500 with a maven_remote_version_id and 2500 without, and 500 of them carry a soft_deleted_at. The mixed marker state is deliberate. It is what makes the two partial unique indexes unavailable to the planner, which is the state-blindness the arm documents.

Rendered SQL:

DELETE FROM public.maven_remote_files
WHERE (maven_remote_files.namespace_id = $1::uuid) AND (maven_remote_files.id IN ((
           SELECT maven_remote_files.id AS "maven_remote_files.id"
           FROM public.maven_remote_files
           WHERE (maven_remote_files.namespace_id = $2::uuid) AND ((maven_remote_files.maven_remote_package_id = $3::uuid) AND (maven_remote_files.maven_remote_version_id IS NULL))
           LIMIT $4
      )))
RETURNING maven_remote_files.blob_storage_attachment_id AS "maven_remote_files.blob_storage_attachment_id",
          maven_remote_files.blob_sha256 AS "maven_remote_files.blob_sha256";

Bound args: [ce838ee0-e5ad-464c-a76c-89154b0b3dfb, ce838ee0-e5ad-464c-a76c-89154b0b3dfb, 5e040505-ab34-43aa-86b4-90cd6ca6fc53, 100]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Delete on maven_remote_files  (cost=11.53..245.27 rows=100 width=50) (actual time=0.087..1.078 rows=100 loops=1)
   Delete on maven_remote_files_p51 maven_remote_files_1
   Buffers: shared hit=365
   ->  Hash Semi Join  (cost=11.53..245.27 rows=100 width=50) (actual time=0.079..1.004 rows=100 loops=1)
         Hash Cond: (maven_remote_files_1.id = "ANY_subquery"."maven_remote_files.id")
         Buffers: shared hit=165
         ->  Seq Scan on maven_remote_files_p51 maven_remote_files_1  (cost=0.00..219.50 rows=5000 width=26) (actual time=0.008..0.618 rows=5000 loops=1)
               Filter: (namespace_id = 'ce838ee0-e5ad-464c-a76c-89154b0b3dfb'::uuid)
               Buffers: shared hit=157
         ->  Hash  (cost=10.28..10.28 rows=100 width=56) (actual time=0.065..0.067 rows=100 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 17kB
               Buffers: shared hit=8
               ->  Subquery Scan on "ANY_subquery"  (cost=0.00..10.28 rows=100 width=56) (actual time=0.011..0.045 rows=100 loops=1)
                     Buffers: shared hit=8
                     ->  Limit  (cost=0.00..9.28 rows=100 width=16) (actual time=0.006..0.029 rows=100 loops=1)
                           Buffers: shared hit=8
                           ->  Seq Scan on maven_remote_files_p51 maven_remote_files_2  (cost=0.00..232.00 rows=2500 width=16) (actual time=0.005..0.024 rows=100 loops=1)
                                 Filter: ((maven_remote_version_id IS NULL) AND (namespace_id = 'ce838ee0-e5ad-464c-a76c-89154b0b3dfb'::uuid) AND (maven_remote_package_id = '5e040505-ab34-43aa-86b4-90cd6ca6fc53'::uuid))
                                 Rows Removed by Filter: 99
                                 Buffers: shared hit=8
 Planning:
   Buffers: shared hit=259
 Planning Time: 1.835 ms
 Execution Time: 1.466 ms

Timings: planning 1.835ms, execution 1.466ms, total 3.301ms.

datastore.reapMavenRemoteChildPage.VersionPage

Summary: Plan matches the method's intent at scale, but not at this seed size. Same shape as the package arm: two Seq Scans of the target partition at 5000 rows, because half the partition matches the predicate and the planner reaches the LIMIT after 200 rows. The scale probe at 220000 rows shows the intended shape, index_maven_remote_files_on_ns_id_ver_id with both equalities and the IS NOT NULL as index conditions, driving a nested loop over pk_maven_remote_files. The namespace_id literal prunes to one partition of 64 in both cases.

Seed shape: namespaces=1, repositories=1, maven_remote_repositories=1, maven_remote_packages=1, maven_remote_versions=1, blob_storage_blobs=5000, blob_storage_attachments=5000, maven_remote_files=5000

The split and the marker mix are the ones the package arm's block states.

Rendered SQL:

DELETE FROM public.maven_remote_files
WHERE (maven_remote_files.namespace_id = $1::uuid) AND (maven_remote_files.id IN ((
           SELECT maven_remote_files.id AS "maven_remote_files.id"
           FROM public.maven_remote_files
           WHERE (maven_remote_files.namespace_id = $2::uuid) AND ((maven_remote_files.maven_remote_version_id = $3::uuid) AND (maven_remote_files.maven_remote_version_id IS NOT NULL))
           LIMIT $4
      )))
RETURNING maven_remote_files.blob_storage_attachment_id AS "maven_remote_files.blob_storage_attachment_id",
          maven_remote_files.blob_sha256 AS "maven_remote_files.blob_sha256";

Bound args: [e6a33d9d-1079-49d6-a092-2c0bc63f975c, e6a33d9d-1079-49d6-a092-2c0bc63f975c, 9f87c1d2-1f0b-4a3a-96da-e0fa3c73b984, 100]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Delete on maven_remote_files  (cost=20.81..254.55 rows=100 width=50) (actual time=0.111..0.984 rows=100 loops=1)
   Delete on maven_remote_files_p20 maven_remote_files_1
   Buffers: shared hit=365
   ->  Hash Semi Join  (cost=20.81..254.55 rows=100 width=50) (actual time=0.103..0.928 rows=100 loops=1)
         Hash Cond: (maven_remote_files_1.id = "ANY_subquery"."maven_remote_files.id")
         Buffers: shared hit=165
         ->  Seq Scan on maven_remote_files_p20 maven_remote_files_1  (cost=0.00..219.50 rows=5000 width=26) (actual time=0.009..0.564 rows=5000 loops=1)
               Filter: (namespace_id = 'e6a33d9d-1079-49d6-a092-2c0bc63f975c'::uuid)
               Buffers: shared hit=157
         ->  Hash  (cost=19.56..19.56 rows=100 width=56) (actual time=0.087..0.089 rows=100 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 17kB
               Buffers: shared hit=8
               ->  Subquery Scan on "ANY_subquery"  (cost=0.00..19.56 rows=100 width=56) (actual time=0.010..0.045 rows=100 loops=1)
                     Buffers: shared hit=8
                     ->  Limit  (cost=0.00..18.56 rows=100 width=16) (actual time=0.005..0.029 rows=100 loops=1)
                           Buffers: shared hit=8
                           ->  Seq Scan on maven_remote_files_p20 maven_remote_files_2  (cost=0.00..232.00 rows=1250 width=16) (actual time=0.004..0.023 rows=100 loops=1)
                                 Filter: ((maven_remote_version_id IS NOT NULL) AND (namespace_id = 'e6a33d9d-1079-49d6-a092-2c0bc63f975c'::uuid) AND (maven_remote_version_id = '9f87c1d2-1f0b-4a3a-96da-e0fa3c73b984'::uuid))
                                 Rows Removed by Filter: 100
                                 Buffers: shared hit=8
 Planning:
   Buffers: shared hit=344 read=1
 Planning Time: 1.973 ms
 Execution Time: 1.341 ms

Timings: planning 1.973ms, execution 1.341ms, total 3.314ms.

datastore.reapMavenRemotePackageRow

Summary: Plan matches the method's intent. A single-row index lookup on pk_maven_remote_packages, pruned to one partition of 64. The root node reports rows=0 because the statement carries no RETURNING; the scan node under it matched the one row, and RowsAffected is what the reap reads. The reported execution time is dominated by the two foreign-key triggers the delete must run, both of which found no row because the subtree had drained. No anomalies.

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

Rendered SQL:

DELETE FROM public.maven_remote_packages
WHERE (maven_remote_packages.namespace_id = $1::uuid) AND (maven_remote_packages.id = $2::uuid);

Bound args: [b5102ba0-766f-41b4-9305-ca9b3639ca8b, 38c969d4-578b-433d-92b3-6f36c604486c]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Delete on maven_remote_packages  (cost=0.28..8.30 rows=0 width=0) (actual time=0.242..0.243 rows=0 loops=1)
   Delete on maven_remote_packages_p36 maven_remote_packages_1
   Buffers: shared hit=17
   ->  Index Scan using maven_remote_packages_p36_pkey on maven_remote_packages_p36 maven_remote_packages_1  (cost=0.28..8.30 rows=1 width=10) (actual time=0.046..0.048 rows=1 loops=1)
         Index Cond: ((id = '38c969d4-578b-433d-92b3-6f36c604486c'::uuid) AND (namespace_id = 'b5102ba0-766f-41b4-9305-ca9b3639ca8b'::uuid))
         Buffers: shared hit=3
 Planning Time: 0.521 ms
 Trigger for constraint maven_remote_versions_maven_remote_package_id_namespace__fkey36 on maven_remote_packages_p36: time=8.301 calls=1
 Trigger for constraint maven_remote_files_maven_remote_package_id_namespace_id_fkey36 on maven_remote_packages_p36: time=8.022 calls=1
 Execution Time: 18.137 ms

Timings: planning 0.521ms, execution 18.137ms, total 18.658ms.

Scale probes for the two file-page arms

The 5000-row seed is below the point where an index path wins on cost for these two statements. These probes re-run both arms against 220000 cache file rows in one partition, spread over 200 cache packages and 2000 cache versions, 100 files per version and 100 version-less files per package. The probes are supplementary evidence for the Seq Scan note above. They are not part of the standard seed.

Version arm at 220000 rows

 Delete on maven_remote_files  (cost=170.65..932.09 rows=91 width=50) (actual time=0.597..1.196 rows=100 loops=1)
   Delete on maven_remote_files_p04 maven_remote_files_1
   Buffers: shared hit=703 dirtied=10
   ->  Nested Loop  (cost=170.65..932.09 rows=91 width=50) (actual time=0.577..1.076 rows=100 loops=1)
         Buffers: shared hit=503
         ->  HashAggregate  (cost=170.23..171.14 rows=91 width=56) (actual time=0.542..0.572 rows=100 loops=1)
               Group Key: "ANY_subquery"."maven_remote_files.id"
               Batches: 1  Memory Usage: 32kB
               Buffers: shared hit=103
               ->  Subquery Scan on "ANY_subquery"  (cost=0.42..170.00 rows=91 width=56) (actual time=0.318..0.496 rows=100 loops=1)
                     Buffers: shared hit=103
                     ->  Limit  (cost=0.42..169.09 rows=91 width=16) (actual time=0.033..0.189 rows=100 loops=1)
                           Buffers: shared hit=103
                           ->  Index Scan using maven_remote_files_p04_namespace_id_maven_remote_version_i_idx1 on maven_remote_files_p04 maven_remote_files_2  (cost=0.42..169.09 rows=91 width=16) (actual time=0.032..0.179 rows=100 loops=1)
                                 Index Cond: ((namespace_id = 'af4d616f-2f08-4bca-a125-5aa6599e5f02'::uuid) AND (maven_remote_version_id IS NOT NULL) AND (maven_remote_version_id = 'eeed9d87-51a9-40b8-9626-5f4c4ab26895'::uuid))
                                 Buffers: shared hit=103
         ->  Index Scan using maven_remote_files_p04_pkey on maven_remote_files_p04 maven_remote_files_1  (cost=0.42..8.35 rows=1 width=26) (actual time=0.005..0.005 rows=1 loops=100)
               Index Cond: ((id = "ANY_subquery"."maven_remote_files.id") AND (namespace_id = 'af4d616f-2f08-4bca-a125-5aa6599e5f02'::uuid))
               Buffers: shared hit=400
 Planning:
   Buffers: shared hit=53 read=2
 Planning Time: 2.430 ms
 Execution Time: 3.595 ms

Both equalities and the IS NOT NULL are index conditions on index_maven_remote_files_on_ns_id_ver_id, with no filter above them. This is what the version arm's comment states.

Package arm at 220000 rows

 Delete on maven_remote_files  (cost=1825.56..2654.68 rows=99 width=50) (actual time=1.404..1.993 rows=100 loops=1)
   Delete on maven_remote_files_p04 maven_remote_files_1
   Buffers: shared hit=1703
   ->  Nested Loop  (cost=1825.56..2654.68 rows=99 width=50) (actual time=1.392..1.879 rows=100 loops=1)
         Buffers: shared hit=1503
         ->  HashAggregate  (cost=1825.14..1826.13 rows=99 width=56) (actual time=1.352..1.382 rows=100 loops=1)
               Group Key: "ANY_subquery"."maven_remote_files.id"
               Batches: 1  Memory Usage: 32kB
               Buffers: shared hit=1103
               ->  Subquery Scan on "ANY_subquery"  (cost=0.42..1824.89 rows=99 width=56) (actual time=1.166..1.314 rows=100 loops=1)
                     Buffers: shared hit=1103
                     ->  Limit  (cost=0.42..1823.90 rows=99 width=16) (actual time=1.160..1.286 rows=100 loops=1)
                           Buffers: shared hit=1103
                           ->  Index Scan using maven_remote_files_p04_namespace_id_maven_remote_package_i_idx1 on maven_remote_files_p04 maven_remote_files_2  (cost=0.42..1823.90 rows=99 width=16) (actual time=1.158..1.277 rows=100 loops=1)
                                 Index Cond: ((namespace_id = 'af4d616f-2f08-4bca-a125-5aa6599e5f02'::uuid) AND (maven_remote_package_id = '56e6b9ce-fc5e-4f61-9c13-4981940e479b'::uuid))
                                 Filter: (maven_remote_version_id IS NULL)
                                 Rows Removed by Filter: 900
                                 Buffers: shared hit=1103
         ->  Index Scan using maven_remote_files_p04_pkey on maven_remote_files_p04 maven_remote_files_1  (cost=0.42..8.36 rows=1 width=26) (actual time=0.005..0.005 rows=1 loops=100)
               Index Cond: ((id = "ANY_subquery"."maven_remote_files.id") AND (namespace_id = 'af4d616f-2f08-4bca-a125-5aa6599e5f02'::uuid))
               Buffers: shared hit=400
 Planning:
   Buffers: shared hit=3
 Planning Time: 0.763 ms
 Execution Time: 2.142 ms

Both equalities are index conditions on index_maven_remote_files_on_ns_id_pkg_id, and only the IS NULL filters what they return. This is what the package arm's comment states.

Rows Removed by Filter: 900 is an artifact of this probe, not the production shape. The probe leaves 1000 version-ful files under the target package, and the index scan walks them to reach the 100 version-less ones. The package arm's caller contract requires every maven_remote_versions row beneath the package to be reaped first, and fk_maven_remote_files_version_belongs_to_package stops a version-ful file from outliving its version row. So a real package-arm page finds no version-ful entries left to skip.

Referential-integrity checks the two parent deletes trigger

The implementation states that the checks PostgreSQL runs when a cache parent row is deleted are index-served rather than scans. These probes issue each check's query shape against the 220000-row seed above. All three use an index on one partition.

Foreign key	Index the probe used	Rows the probe found	Index-scan buffers (hit)
fk_maven_remote_files_maven_remote_package_id	index_maven_remote_files_on_ns_id_pkg_id	1000	1003
fk_maven_remote_versions_maven_remote_package_id	index_maven_remote_versions_on_ns_id_pkg_id	10	12
fk_maven_remote_files_version_belongs_to_package	index_maven_remote_files_on_ns_id_ver_id	100	103

The row counts come from the probe seed. In a reap the subtree is already drained, so each check stops at the first index entry and finds nothing.

Related to #507

This is a bot message 🤖 — /smurfit