chore(datastore): repository walk, finalizer, and fault-suite job (S20-A plan: 14/21) (!1812) · Merge requests · GitLab.org / ops / Artifact Registry

What this step delivers

RepositoryReaper walks a tombstoned repositories row down through its contents, then removes the row itself. No code on main calls it. A later step of this plan adds the chunk driver that calls it, so the running service behaves exactly as it does today.

The walk

The dispatch reads the repository's format and kind, then picks one entry from a table of twelve pairs. Ten dispatch keys reach the per-format reapers the package already holds. The eleventh reaper, ContainerRemoteManifestReaper, is the one the walk never dispatches. That reaper takes only a container_remote_manifests row with a tombstone of its own. A repository tombstone marks no manifest, so the manifest-level reaper spares exactly the rows this walk exists to take.

Each level enumerates one page of artifact row ids, then hands each id to the level's reaper. The levels run children first. Every parent-to-child foreign key in these families declares no ON DELETE action. A package delete that runs while one of its versions stands aborts the chunk with SQLSTATE 23503. One chunk therefore works one level, and it reaches the next level only after the level below enumerates nothing.

The walk is state-blind on the row it is handed, as the per-format reapers are. It reaps live children under a tombstoned repository by design. A live repository is never a walk root, and each arm asserts that with a live neighbor repository seeded beside the tombstoned one.

The finalizer

RepositoryReaper.finalize issues its own DELETE FROM repositories on the qrm.DB handle that Reap receives. The statement lands in the purge chunk's transaction, so a refusal aborts that chunk. RepositoryStore.Delete keeps its signature and its callers. That method runs on the store's own pool, where the same statement commits independently of the chunk. A finalizer on that pool also queues behind the row locks the chunk holds. finalize applies the same kind gate as RepositoryStore.Delete, so the two paths refuse the same rows.

Seven inbound foreign keys to repositories cascade. One statement therefore removes the parent row, the format child, any remote child, and every collection link. Two NO ACTION families abort the delete instead. One is an artifact that still references the format child, and the other is a cached remote row that still references the remote child. mapRepositoryFinalizeError maps that abort to ErrRepositoryNotEmpty.

The fault suite

The new CI job test:lifecycle-failpoints and the mise task of the same name arm the walk's one gofail seam. The seam leaves one artifact in place, which is the state that puts the finalizer in front of a non-empty repository. That case skips under a plain integration run, because the seam is not compiled in. It must not skip under the job, which narrows the binary to that one case with -run: a lost // gofail: var directive would otherwise leave the job running one test, skipping it, and reporting success over the scenario it exists to exercise. -run cannot report a name that stops resolving either, because a pattern that matches nothing exits 0 having run no test, so the job and the task both assert the case's name against the source before the run. The job and the mise task both rewrite the tree before they run, so both set ARTIFACT_REGISTRY_GOFAIL_SEAMS_REQUIRED, and armRepoWalkSeam fails rather than skips when that variable is set. A plain integration run leaves it unset and still skips. The job prints per-test names either way, which is what shows which cases ran. after_script runs gofail disable, so a red job leaves the tree as it found it. -timeout 360s sits under the job's own timeout: 10m, so a hung suite trips go's timeout first and leaves a goroutine dump.

The job's changes: rule watches six paths, not the two the plan's Step 14 bullet names. The plan bullet reads "a changes: rule covering internal/datastore/**/* and .gitlab-ci.yml", and this job also watches go.mod, go.sum, .gitlab-ci-asdf-versions.yml and .tool-versions. Review raised the gap: a dependency bump touches only go.mod and go.sum, and a Go toolchain bump touches only .tool-versions and the derived .gitlab-ci-asdf-versions.yml that sets this job's own golang:${GL_ASDF_GOLANG_VERSION} image, so under the two-path rule neither bump ran the job on the merge request that carried it. The four added paths and the reason for them are the ones &integration-test-files already carries, and the list now matches test:accounting-failpoints entry for entry. Step merge requests do not edit the plan file, so this paragraph is where the deviation is recorded.

Tests

internal/datastore/lifecycle_reap_repository_test.go — unit level. Constructor guards, argument guards, and dispatch-map totality over all twelve pairs. Also the exported zero value's fail-closed answer, the page budget arithmetic, the virtual-kind refusal, and the SQLSTATE classification.
internal/datastore/lifecycle_reap_repository_integration_test.go — eight subtests, one per non-virtual pair. Each asserts the subtree gone, the finalizer's cascade, and a live neighbor repository untouched at both artifact levels. The file also holds the no-op re-run and the seam-dependent finalizer refusal.
internal/datastore/lifecycle_reap_repository_explain_integration_test.go — EXPLAIN over every level's page statement, one hash partition per table on that level's chain.

Also in the diff

internal/datastore/query_names.go gains 12 constants, so every statement the walk runs is timed under a name.
docs/specs/S20-a-lifecycle-closed-beta.md gains the corrections this step's code settles: the finalizer's own mechanism, the finalizer's 23503 reading, the ground that exempts ContainerImageStore.Delete from the tombstone predicate, and the second route that reaches maven_packages. Two more follow from those: the reap inventory now states once, above the table, that the Discovery column names scan levels and that the repository walk is a second route into a table, so a row that names only a scan level no longer reads as saying the walk cannot reach that table; and the FK-stall sentence downstream no longer counts sites, because the row this diff adds to that table falsified its count.
.gitignore and internal/datastore/repository_tombstone_visibility_integration_test.go carry comment text this change falsifies. The .gitignore note counted three gofail producers and there are now four. The test file's comment rested its pin on a purge finalizer that reaches RepositoryStore.Delete, and the finalizer this step lands does not reach it.

One limit, named rather than fixed

A tombstoned virtual repository reaps nothing. The four virtual pairs map to an empty walk, because a virtual repository owns no rows of its own. The finalizer's kind gate then refuses the row with ErrRepositoryDeleteVirtualKind, so the walk fails loudly rather than reporting a drained subtree. TestRepositoryReaper_Finalize_RefusesAVirtualKindRow pins that refusal. Neither the spec nor the plan settles what a walk rooted on a virtual repository does, and this step invents no contract for it.

Accepted smells

Two costs this step ships knowingly. Each is named here with its reason, so a reviewer can tell an accepted trade-off from an oversight.

Four of the ten reap pages cannot be driven from the parent row

npm_versions, npm_packages, maven_versions and maven_packages carry no index a page here can bind the parent foreign key with. Every index on those four that leads (namespace_id, <parent fk>) is partial on soft_deleted_at — IS NULL on all of them but index_npm_packages_on_ns_id_repo_id_name_tombstoned, which is IS NOT NULL. A partial index serves a query only where the query's predicate implies the index predicate, and these pages are state-blind by design and carry no soft_deleted_at predicate at any level, so they imply neither polarity. The primary key is (id, namespace_id) and leads with the wrong column. No statistics change that: the index a nested loop would need is not there to be chosen.

The other six each carry one index leading the parent foreign key with no predicate on it, so those pages can flip to a nested loop as statistics grow: index_npm_remote_versions_on_ns_id_pkg_id, index_npm_remote_packages_on_ns_id_repo_id, index_maven_remote_versions_on_ns_id_pkg_id, index_maven_remote_packages_on_ns_id_repo_id, index_container_images_on_ns_id_cr_id_last_downloaded_at and index_container_remote_images_on_ns_id_repo_id_last_dl_at.

What it costs is mostly the prove-empty scan rather than the working one. A working page stops early at its LIMIT while matching rows are dense, but a LIMIT stops a scan early only once it has produced limit rows, so a level's last working page pays what the prove-empty ones pay. RepositoryReaper.Reap needs an empty page to advance a level and to finalize, and its level loop restarts at level 0 on every chunk, so once an npm or Maven hosted level is drained every remaining chunk re-reads that level, and the finalizing chunk re-reads every level.

The size of that read splits the four in two. npm_versions and maven_versions each carry a non-partial index_*_on_ns_id_created_at leading namespace_id, so the scan is bounded by the namespace's rows in the hash partition even though nothing binds the parent match — the forced-index diagnostic in the EXPLAIN section shows that plan. npm_packages and maven_packages carry no non-partial index leading namespace_id: every secondary index there is predicated on soft_deleted_at, and the primary key leads id. Their only plan is a sequential scan of the whole hash partition, which holds every namespace hashed to it rather than the walked namespace's slice. So the scaling term is the namespace's row count in that partition on the two version tables, and the partition's own row count on the two package tables — the walked repository's row count on neither.

The remedy is four non-partial indexes — (namespace_id, npm_package_id) on npm_versions, (namespace_id, npm_repository_id) on npm_packages, and the two Maven equivalents — matching the shape the six above already carry. The two package-table indexes are the pair that removes a full-partition read; the two version-table ones bind an already namespace-bounded scan to the parent row. This step does not add them: its Files: list names no migration, and a migration in this repository is its own change shape, with squawk directives, structure.sql, and the schema and column-catalog integration tests, and its own reviewer. Nothing runs this scan in production before the chunk driver lands, because NewRepositoryReaper has no caller on main, and ADR-010's Release Phasing puts the purger in closed beta. The signal exists from day one either way: database_query_duration_seconds{name="npm_versions_select_reap_page"} and its nine siblings are named in this diff. RepositoryReaper.Reap's cost paragraph carries the same fact in the code, and the head of internal/datastore/lifecycle_reap_repository_explain_integration_test.go records that its fixture cannot pin scan shape and that on these four no fixture could.

The Maven hosted arm is quadratic in packages inside one chunk

mavenPackagesReapPageStmt hands up to limit maven_packages ids to MavenPackageReaper in one chunk, and that reaper's version-less file page is namespace-scoped rather than package-scoped. Its own doc states the cost — a repository walk reaping P packages pays P walks over a set that itself grows with P — and names #684 as the work item carrying it. This walk is the caller that makes that prediction real, and it does so inside one transaction. It is accepted rather than worked around here: the fix belongs in MavenPackageReaper, where the page is built, and a driver-side limit small enough to bound it would shrink every other arm's chunk for no reason.

Spec coverage

Acceptance criteria

#	Criterion	Tests
AC-1	Retention-window discovery admits an old tombstone, not a new one	Step 5. Not this step: the walk takes no window
AC-2	A live row is never a purge root, for every table	Walk half: `TestRepositoryReaper_Reap_WalksEveryFormatAndKindArm` (`assertRepoWalkNeighbourIntact`, per arm). Scan half: Steps 5-7
AC-3	Repository/package/image scans use their discovery index (EXPLAIN)	Steps 5, 7
AC-4	Version-level scans use their four indexes (EXPLAIN)	Steps 6, 12
AC-5	Re-run on a reaped subtree is a no-op reporting success	`TestRepositoryReaper_Reap_OnAFullyReapedSubtreeIsANoOp`. The mid-walk-abort half is Step 15's chunk driver
AC-6	Tombstoned npm repository: live and already-tombstoned rows in one pass	`TestRepositoryReaper_Reap_WalksEveryFormatAndKindArm/npm_hosted` (`assertRepoWalkSubtreeGone`)
AC-7	Every purge transaction is bounded	Step 15. Per-reaper bounds: Steps 8-13
AC-8	A frozen namespace refuses retryably before any row is reaped	Step 15a
AC-9	Tombstoned npm version reaped with files and attachments	Step 8
AC-10	Unpublish-then-republish leaves no orphan	Step 8
AC-11	Tombstoned npm package reaped with tags and metadata files	Step 8
AC-12	`DeleteNpmMetadataFiles` frees attachments at the unpublish site	Step 8
AC-13	Tombstoned Maven version and package reaped	Step 9
AC-14	Container repository reaped through the existing deleters	Step 13 (deleter observation). The walk's container arms drive the same reaper end to end
AC-15	Index and its children reaped parent-first, no refusal	Step 13
AC-16	Tombstoned `container_images` row under a live repository	Step 13
AC-17	Tombstoned container repository: live and marked images in one pass	`TestRepositoryReaper_Reap_WalksEveryFormatAndKindArm/docker_hosted` and `/oci_hosted`
AC-18	Remote-cache subtree reaped, a positive hit per remote table	Steps 10, 11, 12. The walk's four remote arms drive those reapers end to end but assert no per-table hit
AC-19	`container_remote_manifests` and `container_remote_blobs` reaped	Step 12. `container_remote_blobs`, `_tags` and `_manifest_relationships` are #690's, so this suite seeds none
AC-20	A reaped subtree leaves every blob at zero attachments	`TestRepositoryReaper_Reap_WalksEveryFormatAndKindArm` (`assertRepoWalkSubtreeGone`, per digest, per arm)
AC-21	`DELETE` without `destructive`, or with a non-boolean, returns 400	Step 18
AC-22	`destructive=false` on an empty repository returns 204	Step 18
AC-23	`destructive=false` on a non-empty repository returns 409	Step 18
AC-24	That 409's message names the remedy the caller has	Step 18
AC-25	`destructive=true` non-empty: 202, tombstone, job in one transaction	Step 18
AC-26	`destructive=true` on an empty repository returns 204	Step 18
AC-27	After the 202 every client route naming the repository returns 404	Step 3 (reads), Step 18 (routes)
AC-28	Create at a tombstoned name: 409, then 201 after the finalizer	Step 18. The finalizer half this step provides is AC-30
AC-29	The finalizer cannot succeed early: `ErrRepositoryNotEmpty`	`TestRepositoryReaper_Reap_FinalizerRefusesWhileAnArtifactRemains`. The "job reports failure" half is Step 15
AC-30	Finalizer removes the row, its format child, and every link	`TestRepositoryReaper_Reap_WalksEveryFormatAndKindArm` (`assertRepoWalkFinalized`, per arm)
AC-31	Sweep re-queues within one `sweep_interval`	Step 16
AC-32	Sweep reads each namespace's tombstones oldest-first	Step 16
AC-33	A tombstoned `npm_packages` row with no versions left is swept	Steps 7, 16
AC-34	A repository with a live purge job is not re-queued	Step 16
AC-35	A terminal prior job is re-queued on the next tick	Step 16
AC-36	A permanently failed purge stays counted and bounded-cadence	Steps 16, 17b
AC-37	Sweep registers a worker; `ErrPeriodicKindNoWorker` on omission	Step 16
AC-38	A namespace that does not serve writes has nothing enqueued	Steps 15a, 16
AC-39	`ContainerRepositoryStore.FindByNameInNamespace` hides a tombstone	Step 3
AC-40	`RepositoryStore.FindByID` returns `ErrNotFound`	Step 3
AC-41	`RepositoryStore.Update` returns `ErrNotFound`	Step 3
AC-42	Delete, the three counter writes and `Missing` still act	Steps 3, 4
AC-43	`parentRepositoryIsActive` and the row lock close the window	Step 4
AC-44	Each of the four `container_images` gates hides a marked image	S17 Phase 4
AC-45	Every `/v2/` route resolving through `FindByName` answers 404	S17 Phase 4
AC-46	Blob mount answers 202, not 404, on a tombstoned source	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. Exercised here through the container hosted arms, which finalize only if the image row goes
AC-51	The tombstone write emits no counter delta	Step 19
AC-52	The purger is the one format-dispatched emit site	Step 19
AC-53	Every emit site commits its source rows first	Step 19

Error cases

#	Condition	Tests
E-1	`DELETE` omits `destructive`: 400	Step 18
E-2	`destructive` carries a non-boolean: 400	Step 18
E-3	`destructive=false` on a non-empty repository: 409	Step 18
E-4	A publish races the `DELETE FROM repositories`: 23503 classified	Step 18
E-5	`DELETE` on a missing or already-tombstoned repository: 404	Steps 3, 18
E-6	Create at a name a tombstone holds: 409	Step 18
E-7	Any client route naming a tombstoned repository: 404	Steps 3, 18
E-8	Blob mount whose `from=` names a tombstoned repository: 202	S17 Phase 4
E-9	Purge job: 23503 on a parent delete aborts the transaction	Steps 8-13 (`ErrReapParentPinned`). Here `drainRepoWalk` fails loudly on it through `requireNoFKViolation`, so a walk that ordered its legs wrong is attributable rather than generic
E-10	Purge job: a manifest refused as still referenced is retried	Step 13
E-11	Purge job: the finalizer aborts because an artifact remains	`TestRepositoryReaper_Reap_FinalizerRefusesWhileAnArtifactRemains`. The "job fails, sweep re-queues" half is Steps 15, 16
E-12	Purge job: the repository is already hard-deleted when it runs	`TestRepositoryReaper_Reap_OnAFullyReapedSubtreeIsANoOp`
E-13	Purge job: the retention window changed between enqueue and run	Steps 5, 15. Not this step: the walk reads no window
E-14	Sweep: the enumeration query fails	Step 16
E-15	Sweep: the interval elapses before enumeration completes	Step 16
E-16	Emit API unavailable at a purge or tombstone site	Step 19
E-17	Parent gate: the parent is tombstoned between gate and INSERT	Step 4
E-18	A reoccupied coordinate: the purger keys on the tombstone	Steps 8, 10, 12

Security considerations

#	Concern	Tests
S-1	Authorization unchanged; the purger decides nothing itself	Step 18 (handler middleware). The walk reads no caller identity, and nothing here asserts that absence
S-2	`destructive` is a confirmation, not an authorization control	Step 18
S-3	Deletion is irreversible and the API says so	Step 18 (documentation)
S-4	Input validation: `destructive` is a strict boolean	Step 18. No purger input is user-controlled: the walk takes a namespace id, a row id and an integer bound
S-5	No injection surface is added	Not asserted by a test at this step. The walk's statements are jet-built or parameterized raw SQL per ADR-023
S-6	Error messages must not become an existence oracle	Steps 3, 18
S-7	Purge failures are visible; a purge that cannot complete must never report success	`TestRepositoryReaper_Reap_FinalizerRefusesWhileAnArtifactRemains` asserts the walk reports failure rather than success. The gauge that surfaces it is Step 17b

Review added three tests after this table was written, and the table does not name them: TestRepositoryReaper_ReapLevelPage_SpendsOneBudgetAcrossThePage, TestRepositoryReaper_Finalize_RefusesAVirtualKindRow, and TestRepositoryReapPageStmts_PruneEveryChainTableToOnePartition.

Catalogs, conformance, and the run recipe

No scenario in docs/testing/ is added or affected, and the diff touches no file there. The step type is chore, so the e2e-catalog guardrail does not bind on this merge request. The plan gives the catalog work to the two behavior-changing steps of this workstream, and this step is neither of them.

Conformance suites are unaffected. The diff touches no format package and no protocol route.

The run recipe needs no edit. The service builds, boots, and dispatches requests exactly as it does today. .claude/skills/run-artifact-registry/driver.sh smoke was run on this branch anyway, and it passed 24 of 24.

Diff size

The diff is 3214 insertions and 26 deletions across ten files, so the 500-line guardrail applies.

File group	Files	Lines
Walk, dispatch, and finalizer	`internal/datastore/lifecycle_reap_repository.go`	+1070
Query-name constants	`internal/datastore/query_names.go`	+12
Unit tests	`internal/datastore/lifecycle_reap_repository_test.go`	+470
Integration tests	`internal/datastore/lifecycle_reap_repository_integration_test.go`	+1229
`EXPLAIN` tests	`internal/datastore/lifecycle_reap_repository_explain_integration_test.go`	+205
Fault-suite job and task	`.gitlab-ci.yml`, `.mise.toml`	+189
Spec corrections	`docs/specs/S20-a-lifecycle-closed-beta.md`	+21 / -11
Comment text only	`.gitignore`, `internal/datastore/repository_tombstone_visibility_integration_test.go`	+18 / -15

Tests are 1904 of the 3214 lines. Production Go is 1082 lines, and the CI job with its mise task is 189.

A split does not help here, because the four parts do not compile or verify apart.

The walk and its unit tests are one package and one compile unit. The unit tests reach the unexported dispatch table and the unexported page builders.
The integration suite is the only proof that a walk drains a real subtree, and it needs the walk.
The EXPLAIN suite pins the partition pruning that the ten page statements claim in prose, and it needs those statements.
The CI job is the only thing that arms the finalizer-refusal seam. Without the job that case skips, and the refusal has no evidence.

Merge order and open items

Every merge request this step depends on is merged, and the branch sits on origin/main. This step widens no predicate ahead of the code that serves it, so no merge order is owed on that ground.

!1750 (merged) (feat(managementapi): serve the npm remote package and version reads (S17 Phase 6 plan: 17/42)) landed the name label budget at 450 on main. This branch carried an identical raise, and the rebase onto main dropped that commit as already upstream, so internal/metrics/cardinality.go is not in this diff. The 12 query names this step adds land under the budget main already carries, and TestNameBudget_CoversEveryDeclaredQueryName is what checks that.

Four open merge requests bear on what this one says, and none of them blocks it. Each note below reads correctly on either side of its merge.

!1811 (merged) (feat(datastore): reap the three uncovered container remote tables) replaces the ContainerRemoteImageReaper file-doc paragraph that this step's dispatch comment in internal/datastore/lifecycle_reap_repository.go cites for the three tables that reaper leaves in place. While !1811 (merged) is open, that comment is true and the doc it cites still says so. Once !1811 (merged) merges, the limitation is gone, the comment describes a state the code no longer has, and the citation no longer resolves, so the comment goes with it. A small conflict in internal/datastore/query_names.go is expected either way: !1811 (merged) inserts at the line this step appends after.
!1781 (ci: shard the internal/datastore integration suite) moves internal/datastore out of test:integration and into a new test:integration:datastore. While !1781 is open, the .gitlab-ci.yml and .mise.toml comments here correctly name test:integration as the job that runs the cases around the seam. Once it merges, both comments name test:integration:datastore instead. The wiring-script claim in the same block survives either order, because !1781 leaves the recursive ./internal/datastore/... go list argument in place and does its exclusion in a grep, and the wiring script reads the go list arguments.
!1798 (merged) (feat(npm): swap the publish and unpublish counters onto the buffered pipeline) rewrites the same spec table this step rewrites at around line 560, with hunks starting on the same line, and disagrees with it about whether the npm publish site moves a counter. Whichever rebases second resolves both the conflict and the disagreement. This step's other spec hunks, at around lines 235 and 657, are clear of that region.
!1813 (merged) (docs: sync ADRs from handbook repo) carries exactly the three decision files the mirror is behind on. While it is open, ADR-007, ADR-012 and ADR-021 have to be read from the handbook rather than from docs/adr/. Once it merges, the mirror is current and every ADR is read from docs/adr/ again.

Two items are left open on purpose.

./scripts/adr-freshness.sh exits 1 on this branch. The mirror is behind three upstream commits, touching ADR-007, ADR-021 and ADR-012, and the script also reports that it could not reach the internal handbook with this token, so the internal ADRs were not checked at all. The one of the three that reaches this step is ADR-007, whose cascade paragraph the finalizer rests on; that paragraph is byte-identical between the mirror and the handbook, and the unsynced ADR-007 change is in the artifact-level reconciliation material, which this walk does not touch. ADR-010 and ADR-025 were read in full with no conflict. A reviewer with internal-handbook access can close the internal half.
The run-artifact-registry skill documents one driver slot, /tmp/ar-run, shared by the whole host, and no way to run two instances at once. cmd_up finds a live pid, logs that the service already runs, and returns 0. cmd_smoke then seeds and smokes whatever instance holds the slot, so a pass can come from another worktree's binary. The gap predates this step and sits outside its diff. Work under .claude/skills/** takes its own chore(skills): change, so the note lands there rather than here.

Plan status row

This branch does not edit the plan file, the Status table included. Step merge requests leave that table to a single writer, and a batch docs(plans) change records the row for this step. An empty cell means "not yet recorded", not "not started".

The step type is chore, so the Related to <issue URL> line does not apply. That guardrail binds on feat and fix work and exempts maintenance and toil.

Database Review Evidence

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.

No migration files change in this MR, so migration mode collected nothing and only the Queries subsection appears.

Queries

Twelve statements: the dispatch read, the ten reap-page enumerations, and the finalizing delete. The Partitions column restates for the ten pages what TestRepositoryReapPageStmts_PruneEveryChainTableToOnePartition in internal/datastore/lifecycle_reap_repository_explain_integration_test.go already pins in-tree; the two repositories statements are the ones no test EXPLAINs.

Method	Plan node	Index	Rows (plan / actual)	Cost	Time	Buffers (hit / read)	Partitions
`datastore.readRepositoryReapDispatch`	Limit -> Index Scan	`repositories_p46_pkey`	1 / 1	8.30	0.016ms	3 / 0	`repositories` 1/64
`datastore.RepositoryReaper.finalize`	Delete -> Seq Scan	n/a	0 / 0	1.75	0.245ms	45 / 0	`repositories` 1/64
`datastore.containerImagesReapPageStmt`	Limit -> Nested Loop	n/a	100 / 100	3.86	0.028ms	4 / 0	`container_images` 1/64, `container_repositories` 1/64
`datastore.containerRemoteImagesReapPageStmt`	Limit -> Nested Loop	n/a	100 / 100	3.76	0.045ms	4 / 0	`container_remote_images` 1/64, `container_remote_repositories` 1/64
`datastore.mavenPackagesReapPageStmt`	Limit -> Nested Loop	n/a	100 / 100	3.96	0.029ms	4 / 0	`maven_packages` 1/64, `maven_repositories` 1/64
`datastore.mavenRemotePackagesReapPageStmt`	Limit -> Nested Loop	n/a	100 / 100	3.86	0.025ms	4 / 0	`maven_remote_packages` 1/64, `maven_remote_repositories` 1/64
`datastore.mavenRemoteVersionsReapPageStmt`	Limit -> Nested Loop	n/a	100 / 100	3.86	0.032ms	5 / 0	`maven_remote_versions` 1/64, `maven_remote_packages` 1/64, `maven_remote_repositories` 1/64
`datastore.mavenVersionsReapPageStmt`	Limit -> Nested Loop	n/a	100 / 100	3.96	0.029ms	5 / 0	`maven_versions` 1/64, `maven_packages` 1/64, `maven_repositories` 1/64
`datastore.npmPackagesReapPageStmt`	Limit -> Nested Loop	n/a	100 / 100	4.06	0.028ms	4 / 0	`npm_packages` 1/64, `npm_repositories` 1/64
`datastore.npmRemotePackagesReapPageStmt`	Limit -> Nested Loop	n/a	100 / 100	3.76	0.029ms	4 / 0	`npm_remote_packages` 1/64, `npm_remote_repositories` 1/64
`datastore.npmRemoteVersionsReapPageStmt`	Limit -> Nested Loop	n/a	100 / 100	3.86	0.030ms	5 / 0	`npm_remote_versions` 1/64, `npm_remote_packages` 1/64, `npm_remote_repositories` 1/64
`datastore.npmVersionsReapPageStmt`	Limit -> Nested Loop	n/a	100 / 100	3.98	0.033ms	5 / 0	`npm_versions` 1/64, `npm_packages` 1/64, `npm_repositories` 1/64

Query notes:

All ten reap-page enumerations: Seq Scan on the target table with 5000 seeded rows in the partition. The planner drives the nested loop from the artifact table and applies the parent match as a Join Filter above the scan, so no index condition binds the parent foreign key on any of the ten. Under the page's LIMIT the scan stops early only while matching rows are dense.
Drained-page measurement for that shape. Seeding a second repository of the same pair in the namespace, with the walked repository's level empty and the sibling holding 5000 npm_versions rows in the same partition, npm_versions_p34 is scanned whole to return an empty page: Rows Removed by Join Filter: 5000, 74 shared buffer hits, 0.485 ms execution. An empty page is the signal RepositoryReaper.Reap needs to advance a level and to finalize, so a purge runs at least one of these per level. The fixture here is npm_versions, where index_npm_versions_on_ns_id_created_at bounds the scan to the namespace's rows in the partition, so the scaling term measured is the namespace's row count in that table's partition, not the walked repository's. On npm_packages and maven_packages no non-partial index leads namespace_id, so the same plan shape reads the whole partition; the ## Accepted smells section carries that half.
Forcing index paths (SET LOCAL enable_seqscan = off, run as a diagnostic and not one of the plans above) does not change the shape: the Index Cond stays namespace_id = $1 and the parent match stays a Join Filter, on both a table whose only (namespace_id, parent_id) indexes are partial on soft_deleted_at IS NULL (npm_versions) and one carrying a non-partial index on that pair (npm_remote_versions, index_npm_remote_versions_on_ns_id_pkg_id).
What that diagnostic can and cannot show, corrected. Turning sequential scans off removes one path and leaves the next-cheapest of the indexes that exist; on npm_versions that is index_npm_versions_on_ns_id_created_at, read with namespace_id as the only Index Cond over the same partition slice, which is the shape above. It cannot cost a nested loop against an index that is not there, so it is not evidence either way about a missing one, and the reading that this plan shape does not come from a missing index does not follow from it. The DDL is what settles it, and it splits the ten six-four. The four remote families and the two container pages each carry a non-partial index leading the parent foreign key, so their measured Seq Scan reads as a small-fixture choice and they can flip to a nested loop as statistics grow. npm_versions, npm_packages, maven_versions and maven_packages carry no such index, so on those four the scan is the only plan available and no statistics change it. ## Accepted smells records those four, the remedy, and why this step does not take it. Reinforcing the first half: at this seed size the LIMIT made the planner drive from the artifact table on npm_remote_versions too, a table that does carry the full index, so a Seq Scan here is by itself no evidence of a missing index on any of the ten.
datastore.RepositoryReaper.finalize: the delete node itself costs 0.245 ms, and the seven foreign-key constraint triggers into repositories (container_repositories, npm_repositories, maven_repositories, repository_collection_repositories, npm_remote_repositories, maven_remote_repositories, container_remote_repositories) account for the remaining 12.0 ms of the 12.534 ms execution time in a cold transaction.
No partition fan-out anywhere: every table in all twelve plans reaches 1 of 64 hash partitions. No unbounded SELECT: each page's LIMIT is clamped by validateRepositoryReapArgs to 1..MaxLifecycleReapPageSize, and the dispatch read is LIMIT 1. No plan-versus-actual divergence above 10x on any root node.

datastore.readRepositoryReapDispatch

Summary: Plan matches the method's intent: an Index Scan on the primary key (id, namespace_id), with the namespace_id equality pruning to one of 64 hash partitions. Estimate and actual agree (1 / 1) and the read costs three buffer hits at 5000 repositories in the partition. No anomalies.

Seed shape: namespaces=1, repositories=5000

Rendered SQL:

SELECT repositories.format AS "repositories.format",
     repositories.kind AS "repositories.kind"
FROM public.repositories
WHERE (repositories.namespace_id = $1::uuid) AND (repositories.id = $2::uuid)
LIMIT $3;

Bound args: [d4caafef-4d9a-499c-b9e4-a6fbbdaf1e82, 57c21227-0c06-4466-9af5-1b91556c36ba, 1]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.28..8.30 rows=1 width=4) (actual time=0.016..0.016 rows=1 loops=1)
   Buffers: shared hit=3
   ->  Index Scan using repositories_p46_pkey on repositories_p46 repositories  (cost=0.28..8.30 rows=1 width=4) (actual time=0.015..0.015 rows=1 loops=1)
         Index Cond: ((id = '57c21227-0c06-4466-9af5-1b91556c36ba'::uuid) AND (namespace_id = 'd4caafef-4d9a-499c-b9e4-a6fbbdaf1e82'::uuid))
         Buffers: shared hit=3
 Planning:
   Buffers: shared hit=347
 Planning Time: 1.452 ms
 Execution Time: 0.030 ms

Timings: planning 1.452ms, execution 0.030ms, total 1.482ms.

datastore.RepositoryReaper.finalize

Summary: Plan matches the method's intent: the delete prunes to one partition and removes the single matching row. The Seq Scan is a seed-size artifact -- the skill seeds a delete target at 50 rows, and the identical predicate over 5000 rows takes pk_repositories in the dispatch read above. Execution is dominated by the seven foreign-key constraint triggers into repositories, not by the delete node; see Query notes.

Seed shape: namespaces=1, repositories=50

Rendered SQL:

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

Bound args: [e3fcd8e5-70be-4eca-8c28-3d2b375606f3, bc81c796-2cde-4c61-a659-f9248242a5db]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Delete on repositories  (cost=0.00..1.75 rows=0 width=0) (actual time=0.245..0.245 rows=0 loops=1)
   Delete on repositories_p07 repositories_1
   Buffers: shared hit=45
   ->  Seq Scan on repositories_p07 repositories_1  (cost=0.00..1.75 rows=1 width=10) (actual time=0.008..0.010 rows=1 loops=1)
         Filter: ((namespace_id = 'e3fcd8e5-70be-4eca-8c28-3d2b375606f3'::uuid) AND (id = 'bc81c796-2cde-4c61-a659-f9248242a5db'::uuid))
         Rows Removed by Filter: 49
         Buffers: shared hit=1
 Planning:
   Buffers: shared hit=156
 Planning Time: 0.890 ms
 Trigger for constraint container_repositories_repository_id_namespace_id_fkey7 on repositories_p07: time=1.617 calls=1
 Trigger for constraint npm_repositories_repository_id_namespace_id_fkey7 on repositories_p07: time=1.521 calls=1
 Trigger for constraint maven_repositories_repository_id_namespace_id_fkey7 on repositories_p07: time=1.532 calls=1
 Trigger for constraint repository_collection_reposito_repository_id_namespace_id_fkey7 on repositories_p07: time=1.592 calls=1
 Trigger for constraint npm_remote_repositories_repository_id_namespace_id_fkey7 on repositories_p07: time=2.002 calls=1
 Trigger for constraint maven_remote_repositories_repository_id_namespace_id_fkey7 on repositories_p07: time=1.930 calls=1
 Trigger for constraint container_remote_repositories_repository_id_namespace_id_fkey7 on repositories_p07: time=1.820 calls=1
 Execution Time: 12.534 ms

Timings: planning 0.890ms, execution 12.534ms, total 13.424ms.

datastore.containerImagesReapPageStmt

Summary: Plan matches the method's intent: the page returns 100 ids under the bound LIMIT $4, and every table on the chain prunes to one of 64 hash partitions. The planner drives from container_images and applies the repository match as a Join Filter above the scan rather than as an index condition, so the scan stops early only while matching rows are dense. See Query notes for the drained-page measurement that shape produces.

Seed shape: namespaces=1, repositories=1, container_repositories=1, container_images=5000

Rendered SQL:

SELECT container_images.id AS "reap_page_row.id"
FROM public.container_images
     INNER JOIN public.container_repositories ON ((container_repositories.id = container_images.container_repository_id) AND (container_repositories.namespace_id = container_images.namespace_id))
WHERE ((container_images.namespace_id = $1::uuid) AND (container_repositories.namespace_id = $2::uuid)) AND (container_repositories.repository_id = $3::uuid)
LIMIT $4;

Bound args: [abc55a43-d7ef-4329-bb94-80d93a590a59, abc55a43-d7ef-4329-bb94-80d93a590a59, 546bb437-c48d-4614-97b3-11120a82bca9, 100]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.00..3.86 rows=100 width=16) (actual time=0.013..0.028 rows=100 loops=1)
   Buffers: shared hit=4
   ->  Nested Loop  (cost=0.00..193.01 rows=5000 width=16) (actual time=0.012..0.023 rows=100 loops=1)
         Join Filter: (container_repositories.id = container_images.container_repository_id)
         Buffers: shared hit=4
         ->  Seq Scan on container_repositories_p41 container_repositories  (cost=0.00..1.01 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=1)
               Filter: ((namespace_id = 'abc55a43-d7ef-4329-bb94-80d93a590a59'::uuid) AND (repository_id = '546bb437-c48d-4614-97b3-11120a82bca9'::uuid))
               Buffers: shared hit=1
         ->  Seq Scan on container_images_p41 container_images  (cost=0.00..129.50 rows=5000 width=48) (actual time=0.004..0.010 rows=100 loops=1)
               Filter: (namespace_id = 'abc55a43-d7ef-4329-bb94-80d93a590a59'::uuid)
               Buffers: shared hit=3
 Planning:
   Buffers: shared hit=655 read=1
 Planning Time: 3.003 ms
 Execution Time: 0.050 ms

Timings: planning 3.003ms, execution 0.050ms, total 3.053ms.

datastore.containerRemoteImagesReapPageStmt

Summary: Plan matches the method's intent: the page returns 100 ids under the bound LIMIT $4, and every table on the chain prunes to one of 64 hash partitions. The planner drives from container_remote_images and applies the repository match as a Join Filter above the scan rather than as an index condition, so the scan stops early only while matching rows are dense. See Query notes for the drained-page measurement that shape produces.

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

Rendered SQL:

SELECT container_remote_images.id AS "reap_page_row.id"
FROM public.container_remote_images
     INNER JOIN public.container_remote_repositories ON ((container_remote_repositories.id = container_remote_images.container_remote_repository_id) AND (container_remote_repositories.namespace_id = container_remote_images.namespace_id))
WHERE ((container_remote_images.namespace_id = $1::uuid) AND (container_remote_repositories.namespace_id = $2::uuid)) AND (container_remote_repositories.repository_id = $3::uuid)
LIMIT $4;

Bound args: [c9cb2cbc-0e36-4160-9580-e2530d1635cc, c9cb2cbc-0e36-4160-9580-e2530d1635cc, e9e4f9a2-48e0-444b-8b83-58b86f74c7fd, 100]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.00..3.76 rows=100 width=16) (actual time=0.016..0.045 rows=100 loops=1)
   Buffers: shared hit=4
   ->  Nested Loop  (cost=0.00..188.01 rows=5000 width=16) (actual time=0.015..0.037 rows=100 loops=1)
         Join Filter: (container_remote_repositories.id = container_remote_images.container_remote_repository_id)
         Buffers: shared hit=4
         ->  Seq Scan on container_remote_repositories_p52 container_remote_repositories  (cost=0.00..1.01 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=1)
               Filter: ((namespace_id = 'c9cb2cbc-0e36-4160-9580-e2530d1635cc'::uuid) AND (repository_id = 'e9e4f9a2-48e0-444b-8b83-58b86f74c7fd'::uuid))
               Buffers: shared hit=1
         ->  Seq Scan on container_remote_images_p52 container_remote_images  (cost=0.00..124.50 rows=5000 width=48) (actual time=0.005..0.018 rows=100 loops=1)
               Filter: (namespace_id = 'c9cb2cbc-0e36-4160-9580-e2530d1635cc'::uuid)
               Buffers: shared hit=3
 Planning:
   Buffers: shared hit=636 read=1
 Planning Time: 3.457 ms
 Execution Time: 0.066 ms

Timings: planning 3.457ms, execution 0.066ms, total 3.523ms.

datastore.mavenPackagesReapPageStmt

Summary: Plan matches the method's intent: the page returns 100 ids under the bound LIMIT $4, and every table on the chain prunes to one of 64 hash partitions. The planner drives from maven_packages and applies the repository match as a Join Filter above the scan rather than as an index condition, so the scan stops early only while matching rows are dense. See Query notes for the drained-page measurement that shape produces.

Seed shape: namespaces=1, repositories=1, maven_repositories=1, maven_packages=5000

Rendered SQL:

SELECT maven_packages.id AS "reap_page_row.id"
FROM public.maven_packages
     INNER JOIN public.maven_repositories ON ((maven_repositories.id = maven_packages.maven_repository_id) AND (maven_repositories.namespace_id = maven_packages.namespace_id))
WHERE ((maven_packages.namespace_id = $1::uuid) AND (maven_repositories.namespace_id = $2::uuid)) AND (maven_repositories.repository_id = $3::uuid)
LIMIT $4;

Bound args: [a4fd788d-3f92-495d-8754-d436dd5eb4b5, a4fd788d-3f92-495d-8754-d436dd5eb4b5, 94bb906d-9cf9-4bf6-8201-0682cb71d36a, 100]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.00..3.96 rows=100 width=16) (actual time=0.012..0.029 rows=100 loops=1)
   Buffers: shared hit=4
   ->  Nested Loop  (cost=0.00..198.01 rows=5000 width=16) (actual time=0.010..0.024 rows=100 loops=1)
         Join Filter: (maven_repositories.id = maven_packages.maven_repository_id)
         Buffers: shared hit=4
         ->  Seq Scan on maven_repositories_p17 maven_repositories  (cost=0.00..1.01 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=1)
               Filter: ((namespace_id = 'a4fd788d-3f92-495d-8754-d436dd5eb4b5'::uuid) AND (repository_id = '94bb906d-9cf9-4bf6-8201-0682cb71d36a'::uuid))
               Buffers: shared hit=1
         ->  Seq Scan on maven_packages_p17 maven_packages  (cost=0.00..134.50 rows=5000 width=48) (actual time=0.004..0.011 rows=100 loops=1)
               Filter: (namespace_id = 'a4fd788d-3f92-495d-8754-d436dd5eb4b5'::uuid)
               Buffers: shared hit=3
 Planning:
   Buffers: shared hit=238
 Planning Time: 1.434 ms
 Execution Time: 0.045 ms

Timings: planning 1.434ms, execution 0.045ms, total 1.479ms.

datastore.mavenRemotePackagesReapPageStmt

Summary: Plan matches the method's intent: the page returns 100 ids under the bound LIMIT $4, and every table on the chain prunes to one of 64 hash partitions. The planner drives from maven_remote_packages and applies the repository match as a Join Filter above the scan rather than as an index condition, so the scan stops early only while matching rows are dense. See Query notes for the drained-page measurement that shape produces.

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

Rendered SQL:

SELECT maven_remote_packages.id AS "reap_page_row.id"
FROM public.maven_remote_packages
     INNER JOIN public.maven_remote_repositories ON ((maven_remote_repositories.id = maven_remote_packages.maven_remote_repository_id) AND (maven_remote_repositories.namespace_id = maven_remote_packages.namespace_id))
WHERE ((maven_remote_packages.namespace_id = $1::uuid) AND (maven_remote_repositories.namespace_id = $2::uuid)) AND (maven_remote_repositories.repository_id = $3::uuid)
LIMIT $4;

Bound args: [bb606a5f-fff2-41d1-847e-fdc9fb97c42f, bb606a5f-fff2-41d1-847e-fdc9fb97c42f, 363ab100-2b7f-48ae-874c-b4a1ac1e8771, 100]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.00..3.86 rows=100 width=16) (actual time=0.011..0.025 rows=100 loops=1)
   Buffers: shared hit=4
   ->  Nested Loop  (cost=0.00..193.01 rows=5000 width=16) (actual time=0.009..0.020 rows=100 loops=1)
         Join Filter: (maven_remote_repositories.id = maven_remote_packages.maven_remote_repository_id)
         Buffers: shared hit=4
         ->  Seq Scan on maven_remote_repositories_p25 maven_remote_repositories  (cost=0.00..1.01 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=1)
               Filter: ((namespace_id = 'bb606a5f-fff2-41d1-847e-fdc9fb97c42f'::uuid) AND (repository_id = '363ab100-2b7f-48ae-874c-b4a1ac1e8771'::uuid))
               Buffers: shared hit=1
         ->  Seq Scan on maven_remote_packages_p25 maven_remote_packages  (cost=0.00..129.50 rows=5000 width=48) (actual time=0.003..0.009 rows=100 loops=1)
               Filter: (namespace_id = 'bb606a5f-fff2-41d1-847e-fdc9fb97c42f'::uuid)
               Buffers: shared hit=3
 Planning:
   Buffers: shared hit=241
 Planning Time: 1.147 ms
 Execution Time: 0.038 ms

Timings: planning 1.147ms, execution 0.038ms, total 1.185ms.

datastore.mavenRemoteVersionsReapPageStmt

Summary: Plan matches the method's intent: the page returns 100 ids under the bound LIMIT $5, and every table on the chain prunes to one of 64 hash partitions. The planner drives from maven_remote_versions and applies the repository match as a Join Filter above the scan rather than as an index condition, so the scan stops early only while matching rows are dense. See Query notes for the drained-page measurement that shape produces.

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

Rendered SQL:

SELECT maven_remote_versions.id AS "reap_page_row.id"
FROM public.maven_remote_versions
     INNER JOIN public.maven_remote_packages ON ((maven_remote_packages.id = maven_remote_versions.maven_remote_package_id) AND (maven_remote_packages.namespace_id = maven_remote_versions.namespace_id))
     INNER JOIN public.maven_remote_repositories ON ((maven_remote_repositories.id = maven_remote_packages.maven_remote_repository_id) AND (maven_remote_repositories.namespace_id = maven_remote_packages.namespace_id))
WHERE (((maven_remote_versions.namespace_id = $1::uuid) AND (maven_remote_packages.namespace_id = $2::uuid)) AND (maven_remote_repositories.namespace_id = $3::uuid)) AND (maven_remote_repositories.repository_id = $4::uuid)
LIMIT $5;

Bound args: [5dab6fad-f1b4-4024-b8dd-617aea37a15a, 5dab6fad-f1b4-4024-b8dd-617aea37a15a, 5dab6fad-f1b4-4024-b8dd-617aea37a15a, 71cc6262-87db-4c04-8167-c9fa3cbc993d, 100]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.00..3.86 rows=100 width=16) (actual time=0.016..0.032 rows=100 loops=1)
   Buffers: shared hit=5
   ->  Nested Loop  (cost=0.00..193.04 rows=5000 width=16) (actual time=0.015..0.027 rows=100 loops=1)
         Join Filter: (maven_remote_packages.id = maven_remote_versions.maven_remote_package_id)
         Buffers: shared hit=5
         ->  Nested Loop  (cost=0.00..2.04 rows=1 width=32) (actual time=0.010..0.011 rows=1 loops=1)
               Join Filter: (maven_remote_repositories.id = maven_remote_packages.maven_remote_repository_id)
               Buffers: shared hit=2
               ->  Seq Scan on maven_remote_packages_p59 maven_remote_packages  (cost=0.00..1.01 rows=1 width=48) (actual time=0.006..0.006 rows=1 loops=1)
                     Filter: (namespace_id = '5dab6fad-f1b4-4024-b8dd-617aea37a15a'::uuid)
                     Buffers: shared hit=1
               ->  Seq Scan on maven_remote_repositories_p59 maven_remote_repositories  (cost=0.00..1.01 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=1)
                     Filter: ((namespace_id = '5dab6fad-f1b4-4024-b8dd-617aea37a15a'::uuid) AND (repository_id = '71cc6262-87db-4c04-8167-c9fa3cbc993d'::uuid))
                     Buffers: shared hit=1
         ->  Seq Scan on maven_remote_versions_p59 maven_remote_versions  (cost=0.00..128.50 rows=5000 width=48) (actual time=0.004..0.011 rows=100 loops=1)
               Filter: (namespace_id = '5dab6fad-f1b4-4024-b8dd-617aea37a15a'::uuid)
               Buffers: shared hit=3
 Planning:
   Buffers: shared hit=1023
 Planning Time: 4.032 ms
 Execution Time: 0.064 ms

Timings: planning 4.032ms, execution 0.064ms, total 4.096ms.

datastore.mavenVersionsReapPageStmt

Summary: Plan matches the method's intent: the page returns 100 ids under the bound LIMIT $5, and every table on the chain prunes to one of 64 hash partitions. The planner drives from maven_versions and applies the repository match as a Join Filter above the scan rather than as an index condition, so the scan stops early only while matching rows are dense. See Query notes for the drained-page measurement that shape produces.

Seed shape: namespaces=1, repositories=1, maven_repositories=1, maven_packages=1, maven_versions=5000

Rendered SQL:

SELECT maven_versions.id AS "reap_page_row.id"
FROM public.maven_versions
     INNER JOIN public.maven_packages ON ((maven_packages.id = maven_versions.maven_package_id) AND (maven_packages.namespace_id = maven_versions.namespace_id))
     INNER JOIN public.maven_repositories ON ((maven_repositories.id = maven_packages.maven_repository_id) AND (maven_repositories.namespace_id = maven_packages.namespace_id))
WHERE (((maven_versions.namespace_id = $1::uuid) AND (maven_packages.namespace_id = $2::uuid)) AND (maven_repositories.namespace_id = $3::uuid)) AND (maven_repositories.repository_id = $4::uuid)
LIMIT $5;

Bound args: [de6d6e9a-a7a9-4a8e-a15c-7875b5f633e1, de6d6e9a-a7a9-4a8e-a15c-7875b5f633e1, de6d6e9a-a7a9-4a8e-a15c-7875b5f633e1, 992ff7fb-b7d2-46e8-8e73-f399042be387, 100]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.00..3.96 rows=100 width=16) (actual time=0.015..0.029 rows=100 loops=1)
   Buffers: shared hit=5
   ->  Nested Loop  (cost=0.00..198.04 rows=5000 width=16) (actual time=0.014..0.025 rows=100 loops=1)
         Join Filter: (maven_packages.id = maven_versions.maven_package_id)
         Buffers: shared hit=5
         ->  Nested Loop  (cost=0.00..2.04 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=1)
               Join Filter: (maven_repositories.id = maven_packages.maven_repository_id)
               Buffers: shared hit=2
               ->  Seq Scan on maven_packages_p06 maven_packages  (cost=0.00..1.01 rows=1 width=48) (actual time=0.007..0.007 rows=1 loops=1)
                     Filter: (namespace_id = 'de6d6e9a-a7a9-4a8e-a15c-7875b5f633e1'::uuid)
                     Buffers: shared hit=1
               ->  Seq Scan on maven_repositories_p06 maven_repositories  (cost=0.00..1.01 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1)
                     Filter: ((namespace_id = 'de6d6e9a-a7a9-4a8e-a15c-7875b5f633e1'::uuid) AND (repository_id = '992ff7fb-b7d2-46e8-8e73-f399042be387'::uuid))
                     Buffers: shared hit=1
         ->  Seq Scan on maven_versions_p06 maven_versions  (cost=0.00..133.50 rows=5000 width=48) (actual time=0.003..0.010 rows=100 loops=1)
               Filter: (namespace_id = 'de6d6e9a-a7a9-4a8e-a15c-7875b5f633e1'::uuid)
               Buffers: shared hit=3
 Planning:
   Buffers: shared hit=1017
 Planning Time: 3.737 ms
 Execution Time: 0.048 ms

Timings: planning 3.737ms, execution 0.048ms, total 3.785ms.

datastore.npmPackagesReapPageStmt

Summary: Plan matches the method's intent: the page returns 100 ids under the bound LIMIT $4, and every table on the chain prunes to one of 64 hash partitions. The planner drives from npm_packages and applies the repository match as a Join Filter above the scan rather than as an index condition, so the scan stops early only while matching rows are dense. See Query notes for the drained-page measurement that shape produces.

Seed shape: namespaces=1, repositories=1, npm_repositories=1, npm_packages=5000

Rendered SQL:

SELECT npm_packages.id AS "reap_page_row.id"
FROM public.npm_packages
     INNER JOIN public.npm_repositories ON ((npm_repositories.id = npm_packages.npm_repository_id) AND (npm_repositories.namespace_id = npm_packages.namespace_id))
WHERE ((npm_packages.namespace_id = $1::uuid) AND (npm_repositories.namespace_id = $2::uuid)) AND (npm_repositories.repository_id = $3::uuid)
LIMIT $4;

Bound args: [36563ed0-9f69-48db-862d-1ce6de054631, 36563ed0-9f69-48db-862d-1ce6de054631, 81eaa601-a219-4968-bd39-2c581ef1ab35, 100]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.00..4.06 rows=100 width=16) (actual time=0.011..0.028 rows=100 loops=1)
   Buffers: shared hit=4
   ->  Nested Loop  (cost=0.00..203.01 rows=5000 width=16) (actual time=0.010..0.023 rows=100 loops=1)
         Join Filter: (npm_repositories.id = npm_packages.npm_repository_id)
         Buffers: shared hit=4
         ->  Seq Scan on npm_repositories_p45 npm_repositories  (cost=0.00..1.01 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=1)
               Filter: ((namespace_id = '36563ed0-9f69-48db-862d-1ce6de054631'::uuid) AND (repository_id = '81eaa601-a219-4968-bd39-2c581ef1ab35'::uuid))
               Buffers: shared hit=1
         ->  Seq Scan on npm_packages_p45 npm_packages  (cost=0.00..139.50 rows=5000 width=48) (actual time=0.003..0.010 rows=100 loops=1)
               Filter: (namespace_id = '36563ed0-9f69-48db-862d-1ce6de054631'::uuid)
               Buffers: shared hit=3
 Planning:
   Buffers: shared hit=268
 Planning Time: 1.318 ms
 Execution Time: 0.043 ms

Timings: planning 1.318ms, execution 0.043ms, total 1.361ms.

datastore.npmRemotePackagesReapPageStmt

Summary: Plan matches the method's intent: the page returns 100 ids under the bound LIMIT $4, and every table on the chain prunes to one of 64 hash partitions. The planner drives from npm_remote_packages and applies the repository match as a Join Filter above the scan rather than as an index condition, so the scan stops early only while matching rows are dense. See Query notes for the drained-page measurement that shape produces.

Seed shape: namespaces=1, repositories=1, npm_remote_repositories=1, npm_remote_packages=5000

Rendered SQL:

SELECT npm_remote_packages.id AS "reap_page_row.id"
FROM public.npm_remote_packages
     INNER JOIN public.npm_remote_repositories ON ((npm_remote_repositories.id = npm_remote_packages.npm_remote_repository_id) AND (npm_remote_repositories.namespace_id = npm_remote_packages.namespace_id))
WHERE ((npm_remote_packages.namespace_id = $1::uuid) AND (npm_remote_repositories.namespace_id = $2::uuid)) AND (npm_remote_repositories.repository_id = $3::uuid)
LIMIT $4;

Bound args: [2f6d5837-4797-4c28-9a93-7081c7c827ab, 2f6d5837-4797-4c28-9a93-7081c7c827ab, 46e55b46-2923-4bc8-940a-537a17853f27, 100]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.00..3.76 rows=100 width=16) (actual time=0.013..0.029 rows=100 loops=1)
   Buffers: shared hit=4
   ->  Nested Loop  (cost=0.00..188.01 rows=5000 width=16) (actual time=0.012..0.024 rows=100 loops=1)
         Join Filter: (npm_remote_repositories.id = npm_remote_packages.npm_remote_repository_id)
         Buffers: shared hit=4
         ->  Seq Scan on npm_remote_repositories_p46 npm_remote_repositories  (cost=0.00..1.01 rows=1 width=32) (actual time=0.006..0.007 rows=1 loops=1)
               Filter: ((namespace_id = '2f6d5837-4797-4c28-9a93-7081c7c827ab'::uuid) AND (repository_id = '46e55b46-2923-4bc8-940a-537a17853f27'::uuid))
               Buffers: shared hit=1
         ->  Seq Scan on npm_remote_packages_p46 npm_remote_packages  (cost=0.00..124.50 rows=5000 width=48) (actual time=0.004..0.011 rows=100 loops=1)
               Filter: (namespace_id = '2f6d5837-4797-4c28-9a93-7081c7c827ab'::uuid)
               Buffers: shared hit=3
 Planning:
   Buffers: shared hit=236
 Planning Time: 1.289 ms
 Execution Time: 0.047 ms

Timings: planning 1.289ms, execution 0.047ms, total 1.336ms.

datastore.npmRemoteVersionsReapPageStmt

Summary: Plan matches the method's intent: the page returns 100 ids under the bound LIMIT $5, and every table on the chain prunes to one of 64 hash partitions. The planner drives from npm_remote_versions and applies the repository match as a Join Filter above the scan rather than as an index condition, so the scan stops early only while matching rows are dense. See Query notes for the drained-page measurement that shape produces.

Seed shape: namespaces=1, repositories=1, npm_remote_repositories=1, npm_remote_packages=1, npm_remote_versions=5000

Rendered SQL:

SELECT npm_remote_versions.id AS "reap_page_row.id"
FROM public.npm_remote_versions
     INNER JOIN public.npm_remote_packages ON ((npm_remote_packages.id = npm_remote_versions.npm_remote_package_id) AND (npm_remote_packages.namespace_id = npm_remote_versions.namespace_id))
     INNER JOIN public.npm_remote_repositories ON ((npm_remote_repositories.id = npm_remote_packages.npm_remote_repository_id) AND (npm_remote_repositories.namespace_id = npm_remote_packages.namespace_id))
WHERE (((npm_remote_versions.namespace_id = $1::uuid) AND (npm_remote_packages.namespace_id = $2::uuid)) AND (npm_remote_repositories.namespace_id = $3::uuid)) AND (npm_remote_repositories.repository_id = $4::uuid)
LIMIT $5;

Bound args: [ce0a3667-c1c1-41bf-8bfc-058ecca7f6c2, ce0a3667-c1c1-41bf-8bfc-058ecca7f6c2, ce0a3667-c1c1-41bf-8bfc-058ecca7f6c2, fa68c5e9-95f9-477d-a760-c6ad653e10d1, 100]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.00..3.86 rows=100 width=16) (actual time=0.015..0.030 rows=100 loops=1)
   Buffers: shared hit=5
   ->  Nested Loop  (cost=0.00..193.04 rows=5000 width=16) (actual time=0.014..0.025 rows=100 loops=1)
         Join Filter: (npm_remote_packages.id = npm_remote_versions.npm_remote_package_id)
         Buffers: shared hit=5
         ->  Nested Loop  (cost=0.00..2.04 rows=1 width=32) (actual time=0.009..0.010 rows=1 loops=1)
               Join Filter: (npm_remote_repositories.id = npm_remote_packages.npm_remote_repository_id)
               Buffers: shared hit=2
               ->  Seq Scan on npm_remote_packages_p59 npm_remote_packages  (cost=0.00..1.01 rows=1 width=48) (actual time=0.006..0.006 rows=1 loops=1)
                     Filter: (namespace_id = 'ce0a3667-c1c1-41bf-8bfc-058ecca7f6c2'::uuid)
                     Buffers: shared hit=1
               ->  Seq Scan on npm_remote_repositories_p59 npm_remote_repositories  (cost=0.00..1.01 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=1)
                     Filter: ((namespace_id = 'ce0a3667-c1c1-41bf-8bfc-058ecca7f6c2'::uuid) AND (repository_id = 'fa68c5e9-95f9-477d-a760-c6ad653e10d1'::uuid))
                     Buffers: shared hit=1
         ->  Seq Scan on npm_remote_versions_p59 npm_remote_versions  (cost=0.00..128.50 rows=5000 width=48) (actual time=0.004..0.010 rows=100 loops=1)
               Filter: (namespace_id = 'ce0a3667-c1c1-41bf-8bfc-058ecca7f6c2'::uuid)
               Buffers: shared hit=3
 Planning:
   Buffers: shared hit=968 read=1
 Planning Time: 4.423 ms
 Execution Time: 0.056 ms

Timings: planning 4.423ms, execution 0.056ms, total 4.479ms.

datastore.npmVersionsReapPageStmt

Summary: Plan matches the method's intent: the page returns 100 ids under the bound LIMIT $5, and every table on the chain prunes to one of 64 hash partitions. The planner drives from npm_versions and applies the repository match as a Join Filter above the scan rather than as an index condition, so the scan stops early only while matching rows are dense. See Query notes for the drained-page measurement that shape produces.

Seed shape: namespaces=1, repositories=1, npm_repositories=1, npm_packages=1, npm_versions=5000

Rendered SQL:

SELECT npm_versions.id AS "reap_page_row.id"
FROM public.npm_versions
     INNER JOIN public.npm_packages ON ((npm_packages.id = npm_versions.npm_package_id) AND (npm_packages.namespace_id = npm_versions.namespace_id))
     INNER JOIN public.npm_repositories ON ((npm_repositories.id = npm_packages.npm_repository_id) AND (npm_repositories.namespace_id = npm_packages.namespace_id))
WHERE (((npm_versions.namespace_id = $1::uuid) AND (npm_packages.namespace_id = $2::uuid)) AND (npm_repositories.namespace_id = $3::uuid)) AND (npm_repositories.repository_id = $4::uuid)
LIMIT $5;

Bound args: [8012244a-3b37-4492-b7a3-36fc6d97255b, 8012244a-3b37-4492-b7a3-36fc6d97255b, 8012244a-3b37-4492-b7a3-36fc6d97255b, 9ac2b14c-d4b5-4a76-a674-f4ab1b02dfc6, 100]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.00..3.98 rows=100 width=16) (actual time=0.016..0.033 rows=100 loops=1)
   Buffers: shared hit=5
   ->  Nested Loop  (cost=0.00..199.04 rows=5000 width=16) (actual time=0.015..0.028 rows=100 loops=1)
         Join Filter: (npm_packages.id = npm_versions.npm_package_id)
         Buffers: shared hit=5
         ->  Nested Loop  (cost=0.00..2.04 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=1)
               Join Filter: (npm_repositories.id = npm_packages.npm_repository_id)
               Buffers: shared hit=2
               ->  Seq Scan on npm_packages_p05 npm_packages  (cost=0.00..1.01 rows=1 width=48) (actual time=0.007..0.007 rows=1 loops=1)
                     Filter: (namespace_id = '8012244a-3b37-4492-b7a3-36fc6d97255b'::uuid)
                     Buffers: shared hit=1
               ->  Seq Scan on npm_repositories_p05 npm_repositories  (cost=0.00..1.01 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1)
                     Filter: ((namespace_id = '8012244a-3b37-4492-b7a3-36fc6d97255b'::uuid) AND (repository_id = '9ac2b14c-d4b5-4a76-a674-f4ab1b02dfc6'::uuid))
                     Buffers: shared hit=1
         ->  Seq Scan on npm_versions_p05 npm_versions  (cost=0.00..134.50 rows=5000 width=48) (actual time=0.005..0.012 rows=100 loops=1)
               Filter: (namespace_id = '8012244a-3b37-4492-b7a3-36fc6d97255b'::uuid)
               Buffers: shared hit=3
 Planning:
   Buffers: shared hit=979
 Planning Time: 4.596 ms
 Execution Time: 0.055 ms

Timings: planning 4.596ms, execution 0.055ms, total 4.651ms.

This is a bot message 🤖 — /smurfit

Edited Aug 22, 2026 by Pawel Rozlach

chore(datastore): repository walk, finalizer, and fault-suite job (S20-A plan: 14/21)

What this step delivers

The walk

The finalizer

The fault suite

Tests

Also in the diff

One limit, named rather than fixed

Accepted smells

Four of the ten reap pages cannot be driven from the parent row

The Maven hosted arm is quadratic in packages inside one chunk

Spec coverage

Acceptance criteria

Error cases

Security considerations

Catalogs, conformance, and the run recipe

Diff size

Merge order and open items

Plan status row

Related issue

Database Review Evidence

Queries

Merge request reports