fix(datastore): reap maven_packages tombstones via a purge level

Pawel Rozlachrequested to merge
prozlach/issue-937-maven-packages-tombstones-purger-scan into main

What this delivers

The purger now reaps a Maven package that the management API tombstones, together with everything under it. Before this change no purger scan read maven_packages, so the tombstoned row and its subtree stayed in the database forever.

The cause. Two gaps compose, both in internal/datastore. MavenBulkMarkers.markMavenPackageTx writes maven_packages.soft_deleted_at and no child row. The maven_versions rows under the mark stay live, and ScanTombstonedMavenVersions keys on a version's own tombstone, so it returns none of them. The repository above the package still stands, so no repository walk reaches them either. The one reaper that can drain the row, MavenPackageReaper, refused that exact input. Its caller contract required every maven_versions row beneath the package to be reaped first. A scan level added alone therefore gives a permanent ErrReapParentPinned stall and not a reap. That second half is why the fix is a scan level and a downward walk, and not a scan level alone. The new tests fail on main with datastore: reap parent row is still referenced by a child row, out of MavenPackageReaper.Reap.

The change.

  • LifecycleScanStore.ScanTombstonedMavenPackages, a discovery scan on the shape ScanTombstonedNpmPackages uses. It reads index_maven_packages_on_ns_id_soft_deleted_at, which is on main already, so this merge request carries no migration.
  • A maven_packages level in lifecycle.DefaultPurgeLevels, paired with datastore.NewMavenPackageReaper.
  • A downward walk in MavenPackageReaper.Reap. It pages the live maven_versions rows under the package, drains each one with its maven_files rows and their blob_storage_attachments rows, then removes the package row.
  • A maven_packages row in artifactTombstoneCountTables, so the tombstone gauge counts the table.

The mark side does not move. The mark writes no child row, and the reaper descends instead. The package arm reports a component count and no byte figure, so this change adds no reap-time size_bytes decrement.

Acceptance item 3 closed as a conservation invariant. The issue's own wording names a reap-side counter emit that this branch does not make. It asks that "the counter deltas the reap emits agree with what the mark reported hidden". This branch emits no counter delta, so it cannot close that sentence as written. The item was restated as a conservation property, asserted on main with no emit. The live version count the mark reports hidden equals the number of maven_versions rows the reap removes. That same number equals the Components total the purge chunks report. TestRunPurgeChunks_MavenPackageComponentsEqualTheHiddenVersionCount asserts it. The reap-side counter emit belongs to !1916 (merged) and is asserted there. The restatement is the issue author's own decision, taken when this work was scoped. It is not a reading this branch made of the issue's text, and the issue keeps its own wording.

Spec coverage

Spec: docs/specs/S20-a-lifecycle-closed-beta.md

Issue acceptance

Rows are the issue card's acceptance items, which are the issue's own words bar item 3; that one carries the operator's restatement, marked below.

# Item Tests
I-1 A maven_packages row tombstoned through the management API's bulk delete is reaped without a repository tombstone TestLifecycleScanStore_PackageLevelTombstoneScans/maven packages/* (ten cases, including the row discovered while its repository stands), TestLifecycleScanStore_PackageLevelTombstoneScans_Guards/maven packages/*, TestDefaultPurgeLevels_CoverEveryScanLevelOfBothScopes/artifact scope, TestDefaultPurgeLevels_NameTheFamilyOfEveryArtifactLevel, TestMavenPackageReaper_Reap/a package tombstoned over live versions is drained whole in one reap, TestRunPurgeChunks_MavenPackageComponentsEqualTheHiddenVersionCount
I-2 Its maven_versions, maven_files and blob_storage_attachments rows are gone after the reap, so a blob the package held reaches zero attachments TestMavenPackageReaper_Reap/a package tombstoned over live versions is drained whole in one reap, .../a version is reaped in either marker state, .../an attachment a version-ful and a version-less file share goes with the last of them
I-3 [OPERATOR-RESTATED] The live version count the mark reports hidden equals the number of maven_versions rows the reap removes, and equals the Components total the purge chunks report TestRunPurgeChunks_MavenPackageComponentsEqualTheHiddenVersionCount

Acceptance criteria

Rows are the criteria of ## Acceptance Criteria this fix bears on, numbered continuously across that section the way the spec numbers its own cross-references. The other criteria are untouched by this change.

# Criterion Tests
AC-2 A row with soft_deleted_at IS NULL is never discovered as a purge root, for every table the purger reaps TestLifecycleScanStore_PackageLevelTombstoneScans/maven packages/a row with soft_deleted_at IS NULL is never returned, .../a tombstone older than a non-zero window is returned and a newer one is not
AC-3 The package- and image-level scans use their (namespace_id, soft_deleted_at DESC) WHERE soft_deleted_at IS NOT NULL index and prune to one partition, asserted by EXPLAIN per table TestLifecycleScanStore_PackageLevelScans_AreIndexBackedOldestFirst/maven packages. The criterion's text enumerates repositories, npm_packages and container_images; maven_packages joins that set with this fix, and the spec edit belongs to the documentation change rather than to this commit
AC-5 Re-running a purge on a fully reaped subtree is a no-op that reports success TestMavenPackageReaper_Reap/a second reap of a drained package reports no rows and no error (pre-existing, still green after the walk lands)
AC-7 Every purge transaction is bounded: rows written per transaction do not grow with the subtree TestMavenPackageReaper_Reap/each chunk deletes at most limit version rows and draining takes more than one, .../a mixed version page deletes only the versions the shared budget leaves room for, .../each chunk runs in one transaction, deletes at most limit file rows, and draining takes more than one (pre-existing)
AC-13 A tombstoned Maven package is reaped with its files and their attachments TestMavenPackageReaper_Reap/a package tombstoned over live versions is drained whole in one reap, .../a version is reaped in either marker state, .../a package holding no version carries no component, no size, and no repository
AC-20 After a subtree is reaped, every blob it referenced has zero blob_storage_attachments rows TestMavenPackageReaper_Reap/a package tombstoned over live versions is drained whole in one reap, .../an attachment a version-ful and a version-less file share goes with the last of them

Error cases

Condition Tests
Purge job: 23503 on a parent delete — the transaction aborts and the job fails, and it is not mapped to ErrRepositoryNotEmpty TestMavenPackageReaper_Reap/a sibling chunk's committed page shortens this one, and the package delete raises 23503 (pre-existing, on fk_maven_files_maven_package_id_maven_packages) covers the file leg, which reports only its delete's affected count and so cannot tell a shortened page from a drained one. .../a sibling chunk's committed file page shortens this one, and the version delete raises 23503 covers the version leg, on fk_maven_files_version_belongs_to_package: that leg returns before its version delete only when the file page filled its budget, so a page a sibling emptied reaches the delete with a live child still under the version. .../a sibling chunk's committed version page shortens this one, and the leg reports not drained pins the corrected predicate on the version page itself, which returns not-drained instead of attempting the package delete. Restoring the old predicate makes that subtest fail with ErrReapParentPinned, so it discriminates the two readings rather than passing under both.
The five argument guards refuse a call before any statement runs TestLifecycleScanStore_PackageLevelTombstoneScans_Guards/maven packages/* (six shapes), TestMavenPackageReaper_Reap/argument guards reject a zero-valued call before any statement runs (pre-existing)
Discovery scan query failure: the wrap keeps the cause and names no namespace TestLifecycleScanStore_PackageLevelTombstoneScans/maven packages/query failure wraps the cause and names no namespace

Security considerations

Concern Tests
Authorization is unchanged and is not weakened No new surface. The scan and the reap take a namespace and a row id from the purge job and perform no authorization decision
destructive is a confirmation, not an authorization control Not reached. This change adds no request-handling path
Deletion is irreversible in closed beta and the API must say so Not reached. No API surface changes
Input validation: no purger input is user-controlled TestLifecycleScanStore_PackageLevelScansStmt_OrderAndBinding/maven packages pins that the namespace and the page size are bound arguments rather than interpolated text
No injection surface is added Same test. The scan is a jet statement and the fixtures are the only raw SQL, in test files
Purge failures are operationally visible rather than silent TestLifecycleScanStore_CountTombstonesByLevel gains a maven_packages seeder, so the tombstone-count gauge covers the table this fix makes reapable. The three 23503 subtests assert the refusal reaches the caller rather than reporting a subtree it did not drain, on the package's file leg, on the version reaper's file leg, and on the package's version leg. The version-page subtest asserts the leg reports not drained where the sibling shortened that page instead.

Resolved ambiguities:

  • DefaultPurgeLevels' level order -> the maven_packages level sits between container_remote_manifests and npm_packages, at the head of the parent-level group. Both positions are correct code, since that function's doc records every pair bar npm_versions/npm_packages as unordered; this one puts the parent group in the maven, npm, container order the version group above it already runs in, and it leaves npm_packages seventh of the artifact scope's eight levels with container_images behind it.
  • The version leg's marker predicate -> state-blind, matching the file legs of both Maven reapers. A marked version the walk skipped would refuse the package delete on every later chunk too, so the tombstone would never drain.

e2e scenario catalog

docs/testing/e2e/maven.md is updated in this change set, so guardrail 12 is met rather than waived. Two existing rows move and no new scenario is added, because the change opens no route and no operator-visible surface. e2e.maven.lifecycle.delete-package now records that the purger reaches the mark the package delete writes. e2e.maven.lifecycle.management-delete-storage-counters now records that a Maven package mark reaches the purger through the maven_packages scan level. That row also records that this arm reports no byte figure of its own.

Decisions this change records

A reader who compares the issue against this branch does not see these decisions otherwise.

The fix shape is the downward walk, and there is no cascade at the mark. MavenPackageReaper drains the versions under the package itself. internal/datastore/maven_bulk_markers.go, internal/managementapi/bulk_maven_worker.go, internal/managementapi/package_delete.go and the mark site in internal/datastore/maven_packages.go are unchanged for this purpose.

Acceptance item 3 is routed as the conservation invariant above. The reap-side counter emit stays out of scope, and item 3 is not narrowed to the mark side.

The GitLab Duo review carries no finding to work. Its note on this merge request reads "I finished my review and found nothing to comment on", and the thread is resolvable: false, so it can neither be answered nor closed. It is recorded here rather than replied to.

The non-blocking structural note is not worked on this branch. Note 3764059637 gathers the structural and tidiness items from the same review pass, and it states that none of them gates the merge. One item is the ptr(x) and new(x) spelling in the two reap test files, which ## Reviewable size answers. The rest are left as they stand.

The publish-versus-mark semantic is not settled here. MavenVersionStore.FindOrCreateVersion runs on the pool and reads no parent soft_deleted_at, so a publish that resolved the package before the mark can commit a maven_versions row after it. 877b9d19 corrects docs/dev/storage-accounting.md to name that window and the foreign key that refuses the package delete. The choice between fencing the insert and accepting delete-wins is tracked in #1044 (closed), which is the package-level sibling of #901 (closed). This branch carries no publish-path change, no interleaving test, and no ## Error Cases row.

Work item 684 keeps both of its pointers in MavenPackageReaper.Reap. 684 records two obligations, one index per table, each its own migration. 1cfd8f84 names the maven_versions half at the version leg so the two pointers no longer read as one obligation copied twice. docs/dev/go-style.md:124-126 is the rule: "A symbol carrying two obligations keeps both pointers, so compressing to the cap drops something else."

This work is not a numbered plan step. There is no step id, no Status row, no separate docs(plans) amendment and no step marker in the title. Guardrails 3 and 4 in AGENTS.md are both scoped to step merge requests. The S20-A plan declines to make this gap a step, because a step contradicts the spec's bound table. The plan adds a step through an amendment only when the work outgrows one merge request. Merged !1811 (merged) is the precedent: the same files, no step, no Status row, no marker, and it edited the plan in its own change set. This branch therefore carries the plan's Research Findings bullet directly.

Comment blocks removed to meet the comment caps

scripts/ci/check-comment-caps.sh caps an unexported top-level doc block at one line, and it has no waiver. Several long blocks in the files this change touches were compressed for that reason. Each deleted fact was checked against the rest of the tree, and all but one survive somewhere else.

One survives nowhere: the definition of the root level as the artifact rows hanging directly off the repository's own child row. internal/datastore/lifecycle_reap_repository.go still uses the term at its skipped-artifact seam and no longer defines it. That definition belongs in internal/datastore/lifecycle_reap_repository.md, the sidecar main carries for that file.

One block in that file now depends on the caps script's banner rule, and it is worth stating as a condition rather than a state. check-comment-caps.sh gives a // --- banner segment its own row, and the repositoryReapWalk block relies on that to stay inside its one-line cap. The dependency was measured rather than assumed: with the banner rule replaced by ordinary prose, the gate fails at that block with two lines against a cap of one. If that carve-out is ever removed, the block exceeds the cap — and the exposure is main's shape as much as this branch's, because the banner arrived with the merge that rewrote this block and a change removing the rule takes main's version down with it.

The sweep that found the stale comment claims

Three earlier sweeps on this branch declared completeness, and each one still left a stale claim in the tree. A *.go path filter cannot reach docs/. An anchor on an identifier cannot reach a Prometheus Help string that names its label and no symbol. The anchor that works is the removed doc text. Take the symbols the diff deletes from comment lines, then grep comment lines in the files the diff does not touch for those symbols. That reaches a comment in an untouched file which names a symbol whose contract the diff rewrote. The other two anchors miss that case.

Run it from the checkout root:

mb=$(git merge-base origin/main HEAD)

# 1. Symbols named in doc lines this branch removed. These are the contracts it rewrote.
git diff -U0 "$mb"...HEAD -- '*.go' \
  | grep -E '^-//' \
  | grep -oE '\b[A-Z][A-Za-z0-9]*[a-z][A-Za-z0-9]*(\.[A-Za-z][A-Za-z0-9]*)?\b' \
  | grep -E '[A-Z].*[A-Z]' | sort -u > /tmp/removedsyms.txt

# 2. Every comment line outside the diff that still names one of them.
git diff --name-only "$mb"...HEAD -- '*.go' > /tmp/changed_go.txt
grep -rn --include='*.go' -E '^[[:space:]]*//' internal cmd \
  | grep -v -F -f /tmp/changed_go.txt \
  | grep -F -f /tmp/removedsyms.txt

Step 1 returns 25 symbols on this branch. Step 2 is noisy across all 25, because generic names such as TombstoneRow dominate the output. Read against the five symbols whose contract this branch changed, the output is one short pass: MavenPackageReaper, MavenVersionReaper, DefaultPurgeLevels, PurgeWorker.Work and ErrReapParentPinned.

Reviewable size

The diff is 30 files, 1218 insertions and 584 deletions against the merge base, measured at a08a7a065 with git diff --stat origin/main...HEAD. That is past the 500-line threshold guardrail 18 and docs/dev/development-model.md set.

Group Files Insertions Deletions
Production Go 9 277 185
Tests 14 795 345
Specs and plans 3 50 30
Dev docs and the e2e catalog 4 96 24
Total 30 1218 584

internal/lifecycle/chunk.go left the diff in the rebase onto main, which is why the production group holds nine files rather than ten. A later rebase dropped one more production line each way: main deleted the table count from LifecycleScanStore.CountTombstonesByLevel's doc comment to fit an abort-semantics sentence, so the branch's edit to that phrase had nothing left to change. The nine-table arithmetic itself is untouched, in artifactTombstoneCountTables' eight entries beside the one repositories table.

A split does not help, and the shape of the table is why. The behavior change is small. In the production Go group, comment lines are 70 insertions and 178 deletions, and everything else — code, blank lines, and the string continuations of the purge-outcomes Help literal — is 208 and 8. The scan level, the purge-level registration and the reaper's version leg are one behavioral change. A split leaves each part unable to reach a reap on its own. The tests are the largest group because guardrail 6 asks for a positive hit per enumerated value. The maven_packages scan therefore gets its own cases beside the npm and container ones, rather than a shared one. Review added four things past the figure this section first carried. The 81-line subtest in a955d8a5 pins the version leg's ErrReapParentPinned route, which the two file legs already pin and this leg did not. Then 2ef5e3508 put two comment lines beside the reapVersionPage call, naming the work item that tracks the index that page has no candidate for. 204eb011 added two lines to docs/dev/storage-accounting.md pricing the version page per chunk on the scan-level route, where the figures already in that document price it per call. a08a7a06 added a 37-line subtest forcing reapVersionPage's partial take, the one branch of the shared-budget arithmetic no fixture reached. Three later commits are prose only and carry no behavior: 877b9d19 rewrites two sentences in docs/dev/storage-accounting.md, 1cfd8f84 rewrites one comment line in internal/datastore/lifecycle_reap_maven.go, and 0a08b5fe rewrites one more sentence in docs/dev/storage-accounting.md. Those three SHAs are current at the head this section is read against; the four above them predate two rebases and no longer resolve. The rest of the volume is prose the change falsified. Table counts, level counts and partition counts named eight tables and now name nine. They move across the spec, the plan, docs/dev/observability.md, docs/dev/storage-accounting.md and the doc comments in the Go files.

Some lines in internal/datastore/lifecycle_reap_maven_integration_test.go and internal/datastore/lifecycle_reap_size_integration_test.go move ptr(x) to new(x) and are not scope creep. At a955d8a5 the size file is uniformly new(x) at 18 sites, and the maven file carries 19 ptr(x) against 10 new(x). The pre-commit lint runs over the staged set, and a merge stages everything it brings in. A revert only makes the hook apply the change again. CI lints new-from-merge-base and never sees these lines. new(expr) needs Go 1.26 or later, and .tool-versions pins 1.26.7 with go.mod at 1.26.0. Returning the maven file to one spelling is declined on that measurement: the hook re-applies the change on the next commit that stages the file, so the mixed state is the tooling's output and not a choice this branch made.

Merge order

Four of the merge requests this section tracked have merged, and this branch has rebased past all of them.

  • !2036 (merged) and !2028 (merged) both merged. Each rewrote a region this branch also rewrites — the lifecycle_purge_outcomes_total row in docs/dev/observability.md, and the maven_packages const block in internal/datastore/query_names.go. Both conflicts were resolved during the rebase onto them, and neither claim in this description now depends on either being open.
  • !1988 (merged) merged and targets main. It rewrote the same repositoryReapWalk doc block this branch corrects, so that block is no longer uncorrected on main.
  • !1916 (merged) "feat(lifecycle): the purger emits the deltas its chunks earn (S20-A plan: 19b/22)" merged on 2026-08-31 at 14:57Z, and this branch rebased onto it. It took row.SkipFreedBytes = true out of runPurgeChunks and landed emitChunkCounters, so the hosted Maven package arm's freed-byte gap is live rather than latent. That is what #1012 (closed) tracks.
  • !2037 (merged) "docs(plans): append the remote parent tombstone scan steps to the S20-A plan" is still open. Its plan hunks start at line 278 and this branch's span 135 to 153, so the two do not overlap. Its Step 20b text describes a merge order for this work that predates the decision recorded below, and its own text is being corrected separately; this branch does not depend on it.
  • !2123 (merged) "feat(npm): remote cache fills emit their storage-accounting deltas" is still open and rewrites lines 706 to 721 of docs/dev/storage-accounting.md. That is the block the rebase onto main resolved by taking main's six lines verbatim, so this branch leaves those lines untouched and the two no longer collide there. This branch's own hunks in that file start at line 942. No pipeline reports a collision of that kind, so the check is recorded here.
  • The implementation merge request for work item #728 does not exist in any state. The recorded order is #728 first; that order is unaffected by this branch being ready first.

This branch merges cleanly into main as it stands.

The ADR-007 amendment

docs/adr/ in this repository is a daily-synced mirror, so guardrail 13 holds and this branch does not touch it. The correction is handbook merge request 20919, "Artifact Registry ADR 007: index maven_packages for tombstone discovery". It declares index_maven_packages_on_ns_id_soft_deleted_at and corrects the claim that no other *_packages table needs a tombstone index. It reaches this repository in two hops: that merge request merges, then the daily sync lands the mirror merge request here. It does not gate this merge request.

Database Review Evidence

No migration is added or modified on this branch, so migration mode did not run. index_maven_packages_on_ns_id_soft_deleted_at is created by the already-merged 20260812150200_add_artifact_tombstone_discovery_indexes.sql, and this branch is its first reader.

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.

The Time column carries the plan's footer Execution Time, not the root node's own actual time. Two of these statements fire referential-integrity triggers that the root node's number leaves out, and on MavenPackageReaper.deleteVersionRows those triggers are 99% of the cost. Every statement was bound with limit = 100, which is lifecycle.DefaultPurgeChunkSize.

Method Plan node Index Rows (plan / actual) Cost Time Buffers (hit / read) Partitions
datastore.LifecycleScanStore.CountTombstonesByLevel.maven_packages Aggregate maven_packages_p07_namespace_id_soft_deleted_at_idx 1 / 1 157.19 0.778ms 224 / 0 ⚠️ 64/64
datastore.LifecycleScanStore.ScanTombstonedMavenPackages Limit maven_packages_p07_namespace_id_soft_deleted_at_idx 100 / 100 33.74 0.107ms 46 / 0 1/64
datastore.MavenPackageReaper.Reap.packageDelete Delete maven_packages_p07_pkey 0 / 0 8.30 13.133ms 11 / 0 1/64
datastore.MavenPackageReaper.Reap.versionlessFiles Delete maven_files_p07_namespace_id_maven_version_id_blob_sha256_idx 91 / 100 1369.21 1.274ms 540 / 0 1/64
datastore.MavenPackageReaper.deleteVersionRows Delete maven_versions_p07_id_maven_package_id_namespace_id_idx 0 / 0 805.66 73.574ms 210 / 0 1/64
datastore.MavenPackageReaper.reapVersionPage.files Delete maven_files_p07_namespace_id_maven_version_id_blob_sha256_idx 100 / 100 1067.58 1.075ms 705 / 0 1/64
datastore.MavenPackageReaper.versionPage Limit n/a (Seq Scan) 100 / 100 43.40 0.545ms 138 / 0 1/64
datastore.RepositoryReaper.mavenPackagesReapPage Limit n/a (Seq Scan) 100 / 100 75.40 0.658ms 234 / 0 1/64, 1/64

The last row is a chain this branch does not change. RepositoryReaper.mavenPackagesReapPage is the other route to maven_packages and is rendered so the two routes can be read against each other. The row above it is not: MavenPackageReaper.versionPage is new on this branch, and it is the state-blind page no index serves. Its per-call figure is the 0.545ms and 138 buffers in that row, at 5,000 rows in the partition. MavenPackageReaper.Reap names the work item that tracks the index it wants, beside its own call, and the measurement at 200,000 rows is on #684. MavenPackageReaper.Reap.packageDelete and .Reap.versionlessFiles are the two legs the new version leg now runs ahead of, and their budget arithmetic changes even though their SQL does not.

Warning

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

  • datastore.LifecycleScanStore.CountTombstonesByLevel.maven_packages: 64/64 partitions of maven_packages. The statement carries no namespace_id predicate because the count is fleet-wide by design, so nothing can prune it. The method's own doc states the shape and asks the caller to floor its call rate. This branch adds the ninth such statement to the call: nine COUNT(*) statements over 9 x 64 = 576 partitions per call, up from eight over 512.

Query notes:

  • datastore.MavenPackageReaper.versionPage: Seq Scan of the whole namespace partition of maven_versions, with Rows Removed by Filter: 4500 at 5000 seeded rows. Every index leading (namespace_id, maven_package_id) on that table is partial on soft_deleted_at IS NULL, and the page is deliberately state-blind, so the planner can use none of them. The cost is paid again on every chunk, because the page restarts at the head of the partition. Measured against partition size, with the target package's rows placed last in heap order: 5000 rows in the partition costs 0.545ms and 138 buffers; 200000 rows costs 17.347ms and 2993 buffers. A non-partial (namespace_id, maven_package_id, id) index, created and rolled back inside the same transaction, takes the 200000-row case to 0.101ms and 7 buffers through an Index Only Scan. The plan and the two comparison plans are in the method's details block.
  • datastore.MavenPackageReaper.deleteVersionRows: execution is dominated by the ON DELETE NO ACTION referential-integrity trigger on maven_files, which fires once per deleted version row. Four samples of the same statement at 100 rows: 26.777ms, 73.164ms, 25.972ms and 34.323ms of trigger time, out of 26.263ms to 73.574ms total execution. The probe the trigger runs is itself indexed and prunes to one partition (LockRows over maven_files_p07_namespace_id_maven_version_id_blob_sha256_idx, 0.056ms, 4 buffers), so this is per-row trigger overhead rather than a missing index. It scales with the page size, and it is held inside the caller's transaction.
  • datastore.MavenPackageReaper.Reap.packageDelete: two triggers fire on the single deleted row, 10.785ms and 1.905ms of the 13.133ms execution. Both are first-call plan builds in a cold session and both drop away once the backend has cached the plan.
  • datastore.RepositoryReaper.mavenPackagesReapPage: Seq Scan of the namespace partition of maven_packages, 992 rows read to fill a 100-row page at 5000 seeded rows, for the same partial-index reason. index_maven_packages_on_ns_id_repo_id_last_downloaded_at is partial on soft_deleted_at IS NULL, and the walk is state-blind. This is pre-existing and unchanged; it is recorded here because the two routes to maven_packages page differently and a reviewer comparing them will ask.
  • Neither file page carries an ORDER BY in its inner SELECT, and neither maven_packages route orders its page either. reapMavenFilePageStmt's doc records that as a deliberate trade against a fixed lock-acquisition order. No plan here contradicts it: no Sort node appears above any of these scans.
  • No Sort node sits above any scan. The skill flags a root-node estimate that misses actual rows by more than 10x, and nothing here comes near it: the widest divergence anywhere in these plans is the count's Append node at 563 planned against 500 actual.

Ordering is load-bearing, and the plans show it. Running deleteVersionRows against a package whose maven_files rows still stand aborts with

ERROR:  update or delete on table "maven_versions_p07" violates foreign key constraint "maven_files_maven_version_id_maven_package_id_namespace_i_fkey7" on table "maven_files"

Every parent-to-child foreign key in this family carries confdeltype = 'a' (NO ACTION): maven_versions to maven_packages, maven_files to maven_packages, and maven_files to maven_versions. Only maven_repositories to repositories is CASCADE. The version leg's file delete therefore has to precede its version delete, which is what reapVersionPage does, and the measured plan below is taken with the file delete run first.

The count's cost, measured rather than asserted. Nine COUNT(*) statements run once each per CountTombstonesByLevel call over a single connection, with no prepared statement, on an otherwise idle database. Third pass, warm relcache:

Table Planning Execution
repositories 14.805ms 0.375ms
maven_packages 5.347ms 0.338ms
npm_packages 6.424ms 0.377ms
container_images 6.495ms 0.375ms
maven_versions 10.383ms 0.398ms
maven_remote_versions 9.847ms 0.331ms
npm_versions 8.167ms 0.320ms
npm_remote_versions 7.846ms 0.335ms
container_remote_manifests 9.340ms 0.321ms
Total 78.654ms 3.170ms

Planning is 25 times execution, and the maven_packages statement this branch adds is 5.347ms of planning and 0.338ms of execution, about 7% of the call. On the first pass in a cold session the same nine cost between 64.293ms and 302.594ms of planning each, so what a pooled connection pays depends on how long its backend has been serving these tables. The shape the method's doc already warns about is unchanged; the branch makes it one ninth larger.

datastore.LifecycleScanStore.CountTombstonesByLevel.maven_packages

Summary: Full 64/64 fan-out, by design: a fleet-wide count binds no partition key, so nothing prunes. The one partition holding rows answers from the partial discovery index through an Index Only Scan; the other 63 are zero-row Seq Scans that cost nothing to run and everything to plan. Planning at 10.435ms against 0.778ms of execution is the whole finding.

Seed shape: namespaces=1, repositories=10, maven_repositories=10, blob_storage_blobs=1, blob_storage_attachments=1, maven_packages=5000, maven_versions=5000, maven_files=5500

Rendered SQL:

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

Bound args: []

Plan (EXPLAIN (ANALYZE, BUFFERS) output; 60 identical zero-row partition scans elided, the elision marked inline):

 Aggregate  (cost=157.18..157.19 rows=1 width=8) (actual time=0.412..0.418 rows=1 loops=1)
   Buffers: shared hit=224
   ->  Append  (cost=0.00..155.78 rows=563 width=0) (actual time=0.097..0.389 rows=500 loops=1)
         Buffers: shared hit=224
         ->  Seq Scan on maven_packages_p00 maven_packages_1  (cost=0.00..0.00 rows=1 width=0) (actual time=0.015..0.015 rows=0 loops=1)
               Filter: (soft_deleted_at IS NOT NULL)
         ->  Seq Scan on maven_packages_p01 maven_packages_2  (cost=0.00..0.00 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=1)
               Filter: (soft_deleted_at IS NOT NULL)
         ->  Seq Scan on maven_packages_p02 maven_packages_3  (cost=0.00..0.00 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
               Filter: (soft_deleted_at IS NOT NULL)
[... maven_packages_p03 through maven_packages_p06, same shape, rows=0 ...]
         ->  Index Only Scan using maven_packages_p07_namespace_id_soft_deleted_at_idx on maven_packages_p07 maven_packages_8  (cost=0.27..152.96 rows=500 width=0) (actual time=0.071..0.237 rows=500 loops=1)
[... maven_packages_p08 through maven_packages_p62, same shape, rows=0 ...]
         ->  Seq Scan on maven_packages_p63 maven_packages_64  (cost=0.00..0.00 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
               Filter: (soft_deleted_at IS NOT NULL)
 Planning:
   Buffers: shared hit=447
 Planning Time: 10.435 ms
 Execution Time: 0.778 ms

Timings: planning 10.435ms, execution 0.778ms, total 11.213ms.

datastore.LifecycleScanStore.ScanTombstonedMavenPackages

Summary: The plan matches the method's intent. The namespace_id equality prunes to one of 64 partitions, and the partial discovery index answers both the predicate and the order: because the index keys soft_deleted_at DESC and the scan wants ASC, the planner reads it backwards and no Sort node appears. Plan and actual rows agree at 100 / 100 and execution is 0.107ms over 5000 seeded packages, 500 of them tombstoned. No anomalies.

Seed shape: namespaces=1, repositories=10, maven_repositories=10, blob_storage_blobs=1, blob_storage_attachments=1, maven_packages=5000, maven_versions=5000, maven_files=5500

Rendered SQL:

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

Bound args: [11111111-1111-4111-8111-111111111111, 100]; the retention window is rendered as the literal INTERVAL '1 DAY' rather than a placeholder, because pg.INTERVALd inlines it.

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.15..33.74 rows=100 width=40) (actual time=0.026..0.084 rows=100 loops=1)
   Buffers: shared hit=46
   ->  Index Scan Backward using maven_packages_p07_namespace_id_soft_deleted_at_idx on maven_packages_p07 maven_packages  (cost=0.15..168.09 rows=500 width=40) (actual time=0.026..0.077 rows=100 loops=1)
         Index Cond: ((namespace_id = '11111111-1111-4111-8111-111111111111'::uuid) AND (soft_deleted_at < (now() - '1 day'::interval)))
         Buffers: shared hit=46
 Planning:
   Buffers: shared hit=83
 Planning Time: 0.882 ms
 Execution Time: 0.107 ms

Timings: planning 0.882ms, execution 0.107ms, total 0.989ms.

datastore.MavenPackageReaper.Reap.packageDelete

Summary: Unchanged by this branch; measured because it is the leg the new version walk runs ahead of. The whole primary key is bound, so the plan is a single-row Index Scan pruned to one partition. Execution is 13.133ms and 12.690ms of that is the two NO ACTION referential-integrity triggers building their plans on first call in a cold session. No anomalies.

Seed shape: namespaces=1, repositories=10, maven_repositories=10, blob_storage_blobs=1, blob_storage_attachments=1, maven_packages=5000, maven_versions=4500 (the target package's 500 drained first), maven_files=4500 (the target package's 1000 drained first)

Rendered SQL:

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

Bound args: [11111111-1111-4111-8111-111111111111, 33333333-3333-4333-8333-333333333333]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Delete on maven_packages  (cost=0.28..8.30 rows=0 width=0) (actual time=0.089..0.090 rows=0 loops=1)
   Delete on maven_packages_p07 maven_packages_1
   Buffers: shared hit=11
   ->  Index Scan using maven_packages_p07_pkey on maven_packages_p07 maven_packages_1  (cost=0.28..8.30 rows=1 width=10) (actual time=0.019..0.020 rows=1 loops=1)
         Index Cond: ((id = '33333333-3333-4333-8333-333333333333'::uuid) AND (namespace_id = '11111111-1111-4111-8111-111111111111'::uuid))
         Buffers: shared hit=3
 Planning:
   Buffers: shared hit=63
 Planning Time: 0.733 ms
 Trigger for constraint maven_versions_maven_package_id_namespace_id_fkey7 on maven_packages_p07: time=10.785 calls=1
 Trigger for constraint maven_files_maven_package_id_namespace_id_fkey7 on maven_packages_p07: time=1.905 calls=1
 Execution Time: 13.133 ms

Timings: planning 0.733ms, execution 13.133ms, total 13.866ms.

datastore.MavenPackageReaper.Reap.versionlessFiles

Summary: Unchanged SQL; the budget it is handed is what this branch changes, since the version leg now spends part of it first. The plan is what the builder's doc predicts: the inner SELECT prunes to one partition and rides index_maven_files_on_ns_id_ver_id, using its maven_version_id IS NULL leg as the index condition and filtering maven_package_id on the heap tuple. Estimate and actual agree within 10% at 91 / 100, and no Sort appears despite the absent ORDER BY. No anomalies.

Seed shape: namespaces=1, repositories=10, maven_repositories=10, blob_storage_blobs=1, blob_storage_attachments=1, maven_packages=5000, maven_versions=5000, maven_files=5500 (500 of them version-less under the target package)

Rendered SQL:

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

Bound args: [11111111-1111-4111-8111-111111111111, 11111111-1111-4111-8111-111111111111, 33333333-3333-4333-8333-333333333333, 100]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Delete on maven_files  (cost=776.45..1369.21 rows=91 width=50) (actual time=0.278..0.557 rows=100 loops=1)
   Delete on maven_files_p07 maven_files_1
   Buffers: shared hit=540
   ->  Nested Loop  (cost=776.45..1369.21 rows=91 width=50) (actual time=0.263..0.456 rows=100 loops=1)
         Buffers: shared hit=340
         ->  HashAggregate  (cost=776.17..777.08 rows=91 width=56) (actual time=0.229..0.245 rows=100 loops=1)
               Group Key: "ANY_subquery"."maven_files.id"
               Batches: 1  Memory Usage: 32kB
               Buffers: shared hit=40
               ->  Subquery Scan on "ANY_subquery"  (cost=0.28..775.94 rows=91 width=56) (actual time=0.142..0.193 rows=100 loops=1)
                     Buffers: shared hit=40
                     ->  Limit  (cost=0.28..775.03 rows=91 width=16) (actual time=0.128..0.159 rows=100 loops=1)
                           Buffers: shared hit=40
                           ->  Index Scan using maven_files_p07_namespace_id_maven_version_id_blob_sha256_idx on maven_files_p07 maven_files_2  (cost=0.28..775.03 rows=91 width=16) (actual time=0.127..0.148 rows=100 loops=1)
                                 Index Cond: ((namespace_id = '11111111-1111-4111-8111-111111111111'::uuid) AND (maven_version_id IS NULL))
                                 Filter: (maven_package_id = '33333333-3333-4333-8333-333333333333'::uuid)
                                 Buffers: shared hit=40
         ->  Index Scan using maven_files_p07_pkey on maven_files_p07 maven_files_1  (cost=0.28..6.63 rows=1 width=26) (actual time=0.002..0.002 rows=1 loops=100)
               Index Cond: ((id = "ANY_subquery"."maven_files.id") AND (namespace_id = '11111111-1111-4111-8111-111111111111'::uuid))
               Buffers: shared hit=300
 Planning:
   Buffers: shared hit=211
 Planning Time: 2.435 ms
 Execution Time: 1.274 ms

Timings: planning 2.435ms, execution 1.274ms, total 3.709ms.

datastore.MavenPackageReaper.deleteVersionRows

Summary: The DELETE itself is cheap and correct: the namespace_id equality prunes to one partition and the 100-element IN list becomes an = ANY index condition, 7 buffers to find the rows. What the statement actually costs is the NO ACTION trigger on maven_files, one call per deleted version, 26.777ms to 73.164ms across four samples of the same 100-row page. That work is held inside the caller's transaction and grows linearly with the page size, so it, and not the plan, is what bounds DefaultPurgeChunkSize on this leg. The probe the trigger runs is itself indexed and pruned, so there is no index to add.

Seed shape: namespaces=1, repositories=10, maven_repositories=10, blob_storage_blobs=1, blob_storage_attachments=1, maven_packages=5000, maven_versions=5000, maven_files=5400 (the page's 100 file rows deleted first, as the leg does)

Rendered SQL (the 100-element IN list is DefaultPurgeChunkSize; ids 4 through 100 elided for length, the elision marked inline):

DELETE FROM public.maven_versions
WHERE (maven_versions.namespace_id = $1::uuid) AND (maven_versions.id IN ($2::uuid, $3::uuid, $4::uuid, /* ... $5 through $101 ... */));

Bound args: [11111111-1111-4111-8111-111111111111] plus the 100 version ids 22222222-2222-4222-8222-000000000001 through 22222222-2222-4222-8222-000000000100.

Plan (EXPLAIN (ANALYZE, BUFFERS) output; the 100-element uuid array in the index condition elided, the elision marked inline):

 Delete on maven_versions  (cost=0.41..805.66 rows=0 width=0) (actual time=0.198..0.199 rows=0 loops=1)
   Delete on maven_versions_p07 maven_versions_1
   Buffers: shared hit=210
   ->  Index Scan using maven_versions_p07_id_maven_package_id_namespace_id_idx on maven_versions_p07 maven_versions_1  (cost=0.41..805.66 rows=100 width=10) (actual time=0.042..0.070 rows=100 loops=1)
         Index Cond: ((id = ANY ('{... 100 maven_versions ids ...}'::uuid[])) AND (namespace_id = '11111111-1111-4111-8111-111111111111'::uuid))
         Buffers: shared hit=7
 Planning:
   Buffers: shared hit=121
 Planning Time: 1.891 ms
 Trigger for constraint maven_files_maven_version_id_maven_package_id_namespace_i_fkey7 on maven_versions_p07: time=73.164 calls=100
 Execution Time: 73.574 ms

The probe that trigger runs, planned on its own:

 LockRows  (cost=0.28..8.31 rows=1 width=14) (actual time=0.030..0.032 rows=1 loops=1)
   Buffers: shared hit=4
   ->  Index Scan using maven_files_p07_namespace_id_maven_version_id_blob_sha256_idx on maven_files_p07 x  (cost=0.28..8.30 rows=1 width=14) (actual time=0.025..0.025 rows=1 loops=1)
         Index Cond: ((namespace_id = '11111111-1111-4111-8111-111111111111'::uuid) AND (maven_version_id = '22222222-2222-4222-8222-000000000001'::uuid))
         Filter: ('33333333-3333-4333-8333-333333333333'::uuid = maven_package_id)
         Buffers: shared hit=3
 Planning Time: 1.517 ms
 Execution Time: 0.056 ms

Timings: planning 1.891ms, execution 73.574ms, total 75.465ms. Three further samples of the same statement gave 26.263ms, 34.707ms and 27.087ms of execution, of which 25.972ms, 34.323ms and 26.777ms was trigger time.

datastore.MavenPackageReaper.reapVersionPage.files

Summary: The new predicate shape, maven_version_id IN (<page of ids>), lands on index_maven_files_on_ns_id_ver_id, which is the one index on maven_files carrying maven_version_id that is not partial on soft_deleted_at. That is what lets this leg be state-blind and still seek. Pruned to one partition, estimate and actual agree at 100 / 100, and execution is 1.075ms. No anomalies.

Seed shape: namespaces=1, repositories=10, maven_repositories=10, blob_storage_blobs=1, blob_storage_attachments=1, maven_packages=5000, maven_versions=5000, maven_files=5500

Rendered SQL (the 100-element IN list is DefaultPurgeChunkSize; placeholders 6 through 102 elided for length, the elision marked inline):

DELETE FROM public.maven_files
WHERE (maven_files.namespace_id = $1::uuid) AND (maven_files.id IN ((
           SELECT maven_files.id AS "maven_files.id"
           FROM public.maven_files
           WHERE (maven_files.namespace_id = $2::uuid) AND (maven_files.maven_version_id IN ($3::uuid, $4::uuid, $5::uuid, /* ... $6 through $102 ... */))
           LIMIT $103
      )))
RETURNING maven_files.blob_storage_attachment_id AS "maven_files.blob_storage_attachment_id",
          maven_files.blob_sha256 AS "maven_files.blob_sha256";

Bound args: the namespace id 11111111-1111-4111-8111-111111111111 twice, then the 100 version ids 22222222-2222-4222-8222-000000000001 through 22222222-2222-4222-8222-000000000100, then the limit 100.

Plan (EXPLAIN (ANALYZE, BUFFERS) output; the 100-element uuid array in the index condition elided, the elision marked inline):

 Delete on maven_files  (cost=427.63..1067.58 rows=100 width=50) (actual time=0.238..0.498 rows=100 loops=1)
   Delete on maven_files_p07 maven_files_1
   Buffers: shared hit=705
   ->  Nested Loop  (cost=427.63..1067.58 rows=100 width=50) (actual time=0.223..0.403 rows=100 loops=1)
         Buffers: shared hit=505
         ->  HashAggregate  (cost=427.35..428.35 rows=100 width=56) (actual time=0.196..0.210 rows=100 loops=1)
               Group Key: "ANY_subquery"."maven_files.id"
               Batches: 1  Memory Usage: 32kB
               Buffers: shared hit=205
               ->  Subquery Scan on "ANY_subquery"  (cost=0.28..427.10 rows=100 width=56) (actual time=0.047..0.162 rows=100 loops=1)
                     Buffers: shared hit=205
                     ->  Limit  (cost=0.28..426.10 rows=100 width=16) (actual time=0.036..0.131 rows=100 loops=1)
                           Buffers: shared hit=205
                           ->  Index Scan using maven_files_p07_namespace_id_maven_version_id_blob_sha256_idx on maven_files_p07 maven_files_2  (cost=0.28..426.10 rows=100 width=16) (actual time=0.035..0.121 rows=100 loops=1)
                                 Index Cond: ((namespace_id = '11111111-1111-4111-8111-111111111111'::uuid) AND (maven_version_id = ANY ('{... 100 maven_versions ids ...}'::uuid[])))
                                 Buffers: shared hit=205
         ->  Index Scan using maven_files_p07_pkey on maven_files_p07 maven_files_1  (cost=0.28..6.50 rows=1 width=26) (actual time=0.002..0.002 rows=1 loops=100)
               Index Cond: ((id = "ANY_subquery"."maven_files.id") AND (namespace_id = '11111111-1111-4111-8111-111111111111'::uuid))
               Buffers: shared hit=300
 Planning:
   Buffers: shared hit=239 read=1
 Planning Time: 3.203 ms
 Execution Time: 1.075 ms

Timings: planning 3.203ms, execution 1.075ms, total 4.278ms.

datastore.MavenPackageReaper.versionPage

Summary: The namespace_id equality prunes to one partition, and inside it the page is a Seq Scan: every index leading (namespace_id, maven_package_id) on maven_versions is partial on soft_deleted_at IS NULL, so a state-blind page can use none of them. The builder's own doc says as much. What the doc does not say is that the scan restarts at the head of the partition on every chunk, so the cost is paid once per 100 versions drained and it grows with the partition rather than with the subtree.

Seed shape: namespaces=1, repositories=10, maven_repositories=10, blob_storage_blobs=1, blob_storage_attachments=1, maven_packages=5000, maven_versions=5000, maven_files=5500. The target package's 500 versions are inserted last, so the scan has to walk the other 4500 before its first match; inserting them first makes the same statement cost 0.044ms and 3 buffers, which flatters it.

Rendered SQL:

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

Bound args: [11111111-1111-4111-8111-111111111111, 33333333-3333-4333-8333-333333333333, 100]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.00..43.40 rows=100 width=32) (actual time=0.503..0.525 rows=100 loops=1)
   Buffers: shared hit=138
   ->  Seq Scan on maven_versions_p07 maven_versions  (cost=0.00..217.00 rows=500 width=32) (actual time=0.500..0.514 rows=100 loops=1)
         Filter: ((namespace_id = '11111111-1111-4111-8111-111111111111'::uuid) AND (maven_package_id = '33333333-3333-4333-8333-333333333333'::uuid))
         Rows Removed by Filter: 4500
         Buffers: shared hit=138
 Planning:
   Buffers: shared hit=41
 Planning Time: 0.657 ms
 Execution Time: 0.545 ms

The same statement at 200000 rows in the partition, the target package's 500 versions again last:

 Limit  (cost=0.00..1284.37 rows=100 width=32) (actual time=17.311..17.327 rows=100 loops=1)
   Buffers: shared hit=2993
   ->  Seq Scan on maven_versions_p07 maven_versions  (cost=0.00..5998.00 rows=467 width=32) (actual time=17.308..17.318 rows=100 loops=1)
         Filter: ((namespace_id = '11111111-1111-4111-8111-111111111111'::uuid) AND (maven_package_id = '33333333-3333-4333-8333-333333333333'::uuid))
         Rows Removed by Filter: 199500
         Buffers: shared hit=2993
 Planning:
   Buffers: shared hit=273 read=7
 Planning Time: 2.245 ms
 Execution Time: 17.347 ms

And the same 200000-row case with a non-partial (namespace_id, maven_package_id, id) index created and rolled back inside the same transaction, to show what the shape would cost if one existed:

 Limit  (cost=0.42..174.91 rows=100 width=32) (actual time=0.045..0.077 rows=100 loops=1)
   Buffers: shared hit=3 read=4
   ->  Index Only Scan using maven_versions_p07_namespace_id_maven_package_id_id_idx1 on maven_versions_p07 maven_versions  (cost=0.42..989.76 rows=567 width=32) (actual time=0.043..0.069 rows=100 loops=1)
         Index Cond: ((namespace_id = '11111111-1111-4111-8111-111111111111'::uuid) AND (maven_package_id = '33333333-3333-4333-8333-333333333333'::uuid))
         Heap Fetches: 100
         Buffers: shared hit=3 read=4
 Planning:
   Buffers: shared hit=176 read=3
 Planning Time: 1.568 ms
 Execution Time: 0.101 ms

Timings: planning 0.657ms, execution 0.545ms, total 1.202ms at 5000 rows in the partition; 2.245ms and 17.347ms at 200000; 1.568ms and 0.101ms at 200000 with the hypothetical index.

datastore.RepositoryReaper.mavenPackagesReapPage

Summary: Unchanged by this branch and rendered for comparison with the new discovery scan, because the two routes to maven_packages page differently. Both prune to one partition. This one carries no tombstone predicate and no ORDER BY, so it cannot use the partial discovery index and falls to a Seq Scan joined against the one maven_repositories row, reading 992 of the partition's 5000 rows to fill a 100-row page. That is the documented shape of a state-blind repository walk rather than a defect, and it is why the new scan level exists.

Seed shape: namespaces=1, repositories=10, maven_repositories=10, blob_storage_blobs=1, blob_storage_attachments=1, maven_packages=5000, maven_versions=5000, maven_files=5500

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: [11111111-1111-4111-8111-111111111111, 11111111-1111-4111-8111-111111111111, 40000000-0000-4000-8000-000000000001, 100]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=1.16..75.40 rows=100 width=16) (actual time=0.360..0.614 rows=100 loops=1)
   Buffers: shared hit=234
   ->  Hash Join  (cost=1.16..372.35 rows=500 width=16) (actual time=0.358..0.604 rows=100 loops=1)
         Hash Cond: (maven_packages.maven_repository_id = maven_repositories.id)
         Buffers: shared hit=234
         ->  Seq Scan on maven_packages_p07 maven_packages  (cost=0.00..352.50 rows=5000 width=48) (actual time=0.215..0.468 rows=992 loops=1)
               Filter: (namespace_id = '11111111-1111-4111-8111-111111111111'::uuid)
               Buffers: shared hit=233
         ->  Hash  (cost=1.15..1.15 rows=1 width=32) (actual time=0.033..0.033 rows=1 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 9kB
               Buffers: shared hit=1
               ->  Seq Scan on maven_repositories_p07 maven_repositories  (cost=0.00..1.15 rows=1 width=32) (actual time=0.004..0.006 rows=1 loops=1)
                     Filter: ((namespace_id = '11111111-1111-4111-8111-111111111111'::uuid) AND (repository_id = '40000000-0000-4000-8000-000000000001'::uuid))
                     Rows Removed by Filter: 9
                     Buffers: shared hit=1
 Planning:
   Buffers: shared hit=100
 Planning Time: 1.626 ms
 Execution Time: 0.658 ms

Timings: planning 1.626ms, execution 0.658ms, total 2.284ms.

Closes #937 (closed)

This is a bot message 🤖 — /smurfit

Edited by Pawel Rozlach

Merge request reports