Loading
chore(datastore): the reap computes the bytes it frees, per repository (S20-A plan: 19a/22)
What this merge request delivers
Five lifecycle reap arms gain the ability to compute the bytes their page frees, per repository.
The arms are MavenVersionReaper, NpmVersionReaper, NpmPackageReaper, ContainerRemoteManifestReaper and MavenRemoteVersionReaper.
Each arm resolves its repository from the tombstone row's parent chain before the delete.
It then runs one set-based statement over the digests its page deleted.
A digest contributes its blob's size only when no surviving row of the same repository still carries it.
datastore.ReapTotals gains RepositoryID, and SizeBytes stops being identically zero at the type level.
The arms compute the figure when a caller asks for it, and on this branch no caller does.
runPurgeChunks sets TombstoneRow.SkipFreedBytes on the row it drains, so neither statement runs on any path the purger dispatches.
Known risk 5 carries what that switch protects against and the event that removes it.
The tests are what exercise the computation here.
Nothing reads the figure.
The site that turns it into a counter delta is emitChunkCounters, and it will land in internal/lifecycle with the emit half of this step.
The type is chore because this half computes a figure nothing reads.
No counter moves, no capability ships, and ReapTotals lives in internal/datastore, so no external contract changes either.
The project's own convention agrees.
The plan author typed 4 of 21 steps feat and 13 chore.
The step that makes the behavior observable is the one that carries feat.
The reap path itself does run, and an earlier version of this description said otherwise.
!1871 (merged) registered the purge worker and scheduled the sweep on main.
cmd/artifact-registry/wire.go:392 calls wireLifecycle, cmd/artifact-registry/wire_lifecycle.go:91 calls lifecycle.RegisterPeriodicJobs, and internal/lifecycle/register.go:83 registers the worker beside a RegisterPeriodic for the sweep.
internal/config/lifecycle.go defaults sweep_interval to 5m and retention_window to 0s, and it carries no flag that gates any of it off.
So the statements this merge request adds run in production from the moment it merges.
What they do not do is move a counter, and that is what the type is read off.
Two artifact-scope arms still report zero: ContainerReaper and NpmRemoteVersionReaper.
Both reasons were re-checked against origin/main rather than carried over.
For hosted OCI, oci.NewCounterSink exists in internal/format/oci/emit.go and no composition root under cmd/ constructs it.
For npm remote, internal/format/npm has no remote-fill emit at all.
Every caller of EmitRepoCounters in that package is a hosted path: the publish commit and the two unpublish handlers.
So no merged path raises repositories.size_bytes for either arm, and a decrement today would drive the column down against bytes nothing added.
docs/dev/storage-accounting.md ("ReapTotals: what one chunk reports") holds that reason, and each arm's Reap doc points there rather than repeating it.
MavenRemoteVersionReaper was the third arm in that group and is now converted.
!1901 (merged) merged on 2026-08-25 and is an ancestor of this branch.
RemoteCacheStore.emitCommittedFillCounters in internal/format/maven/remote_fill_emit.go raises repositories.size_bytes on every committed fill, and cmd/artifact-registry/wire_maven.go supplies the emitter.
The column therefore rises on a fill.
A silent arm would make that a one-way ratchet rather than a documentation defect, which is why the arm was converted here.
It takes the hosted arm's shape: a resolve up maven_remote_versions to maven_remote_packages to maven_remote_repositories, then a survivor walk over maven_remote_files through maven_remote_package_id.
That column is NOT NULL on every row, so the walk covers the version-less rows too.
MavenRemotePackageReaper stays silent for a different reason.
It is reached only by RepositoryReaper's walk, and a repository purge owes no repository-scoped delta at all.
Two further readings have an arm reporting bytes and no write side behind it.
Nothing raises repositories.size_bytes for a container remote cache fill, and ContainerRemoteManifestReaper reports the bytes it frees.
Nothing raises it for an npm packument write either, and NpmPackageReaper reports the packument digests its cache leg frees.
Known risk 1 covers the container remote reading, and finding N1 covers the npm one.
Nothing reads either figure on this branch.
docs/dev/storage-accounting.md ("ReapTotals: what one chunk reports") lists all four writes the column never gains, and the two of them an arm reports bytes against.
Two smaller changes travel with the probe.
Nine query names enter internal/datastore/query_names.go, one per reap statement and one per family for the shared freed-size helper.
Two comments in internal/datastore/blob_storage_attachments_integration_test.go no longer cite sprint identifiers.
They attribute the reclamation of a content-addressable blob row to garbage collection under ADR-025.
Forward references are deliberate
Seven sites on this branch name the emit site: six assertion messages and one comment.
All seven sit in the reap integration suites, and all seven are in the future tense.
The site does not exist on main, and this merge request targets main, so a present-tense claim promises a property grep cannot find.
Guardrail 19 is what asks for the future tense here.
The consumer is named once, in docs/dev/storage-accounting.md ("ReapTotals: what one chunk reports"), as plain text with no godoc doc-link brackets.
That name is emitChunkCounters, and it will land in internal/lifecycle with the emit half.
No Go file on this branch names it, because the long-form reasoning that did name it moved into that document.
The repository walk pays nothing for the figure it drops
RepositoryReaper's walk drops SizeBytes and RepositoryID, and every converted sub-reap it drove still paid a resolve statement and a correlated NOT EXISTS to produce them.
TombstoneRow gains SkipFreedBytes, the walk sets it on every row it hands a sub-reaper, and resolveReapRepository answers uuid.Nil without a statement on such a row.
That answer is the input sumFreedRepositoryBytes already short-circuits on, so no second mechanism was added.
The zero value asks for the figure, and no discovery scan projects the column, so every scan-delivered row still gets one.
What 48a51a2b4 removes is a multiplier.
The other half of the cost stays, so the two halves are stated apart below.
- Removed: the hundredfold multiplier on the repository walk. One chunk hands out up to its whole budget of ids.
At
lifecycle.DefaultPurgeChunkSize100 that was up to 100 resolves and up to 100 correlatedNOT EXISTSanti-joins. All of them ran inside the one transaction that held every row lock the chunk took. - Unchanged: the base per-arm cost on the artifact-purge path. One resolve and one survivor walk per reap, priced against the digests the page carries.
Where production sits on that curve is still unmeasured.
The two production lookups that would settle it were not run.
Nothing in the schema bounds how many surviving rows of one repository can carry one digest:
internal/datastore/migrations/structure.sqldeclares no unique index on the digest columns, andmaven_files.blob_sha256carries only length CHECKs.
The probe now runs on a live production path, because the purge worker is registered and the sweep is scheduled on main.
That holds whatever the type label says, and it is why the base cost is worth stating rather than deferring.
Database Review Evidence below measures one survivor walk at the seed sizes an ephemeral container reaches.
Production cardinality is not in those numbers.
TestRepositoryReaper_ReapLevelPage_DeclinesTheByteFigure and TestResolveReapRepository_AnswersWithoutAStatementWhenTheRowDeclines are the two tests that assert the skip.
Spec coverage
test-author produced one coverage table for step 19 before the split.
Every acceptance-criteria row of that table is asserted at the emit site, so those rows travel with the emit half.
The rows below are the same table's rows for this half, in that table's own wording.
Two rows are new after that table, D-11 and E-3, and both are marked as such.
Spec: docs/specs/S20-a-lifecycle-closed-beta.md, Accounting call sites.
Design properties the criteria rest on
| # | Property | Tests |
|---|---|---|
| D-1 | Δsize is deduplicated within the repository, not within the artifact |
TestMavenVersionReaper_Reap_ReportsTheRepositoryScopedBytes, TestNpmVersionReaper_…, TestNpmPackageReaper_…, TestContainerRemoteManifestReaper_…, TestMavenRemoteVersionReaper_… |
| D-2 | A copy in another repository of the same namespace holds no bytes alive | the "a copy in another repository of the namespace holds nothing alive" subtest of each of the five above. The Maven remote arm names its subtest "a copy in another remote repository of the namespace holds nothing alive" |
| D-3 | A drained subtree names no repository and frees nothing | TestArtifactReapers_Reap_NameNoRepositoryOnADrainedSubtree |
| D-4 | SizeBytes != 0 implies RepositoryID != uuid.Nil |
assertReapSize, on every case of the five arm tests |
| D-6 | The probe refuses an out-of-range discriminator where it enters | TestResolveReapRepository_RefusesBeforeAnyStatement, TestSumFreedRepositoryBytes_RefusesBeforeAnyStatement |
| D-7 | The probe's second statement runs only over a named repository and a non-empty digest set | TestSumFreedRepositoryBytes_AnswersWithoutAStatement |
| D-8 | The arms and parent reapers that report no bytes still report none, and name no repository | TestMavenRemotePackageReaper_Reap, TestNpmRemoteVersionReaper_Reap_RemovesTheVersionSubtree, TestNpmRemotePackageReaper_Reap_RemovesTheMarkedPackageAndItsCache, TestMavenPackageReaper_Reap |
| D-10 | The repository walk carries up neither its sub-reapers' bytes nor the repository they named | TestRepositoryReaper_ReapLevelPage_SpendsOneBudgetAcrossThePage |
| D-11 | The repository walk pays no statement for the figure it drops | TestRepositoryReaper_ReapLevelPage_DeclinesTheByteFigure, TestResolveReapRepository_AnswersWithoutAStatementWhenTheRowDeclines |
D-5 and D-9 are the emit half's rows and are not repeated here.
D-11 is new after that table, for the same reason E-3 below is: it covers 48a51a2b4, which landed after the table was written.
Error cases
| # | Condition | Tests |
|---|---|---|
| E-3 | The probe receives a connection pool rather than a transaction, where each statement commits alone and answers about a state the chunk is not in: it refuses before any statement | the pool case of the guard table in TestResolveReapRepository_RefusesBeforeAnyStatement and in TestSumFreedRepositoryBytes_RefusesBeforeAnyStatement |
E-3 is new after the coverage table above.
It closes the one sentinel in the probe that nothing asserted, errReapSizeNotInTransaction.
E-1 and E-2 of that table are the emit half's rows.
Security considerations
| # | Concern | Tests |
|---|---|---|
| S-1 | No injection surface is added: the probe's raw SQL is fully parameterized | The statements are package constants with $1/$2/$3 binds only; the spec's own Security Considerations bless the shape, naming DeleteIfUnreferenced's correlated NOT EXISTS as the reused pattern. No test asserts parameterization directly |
| S-2 | Authorization is unchanged; the purger performs no authorization decision | Not this step's. No emit site reads a caller identity |
The fixture that distinguishes the two scopes
One artifact in an empty repository makes the version-scoped sum and the repository-scoped sum equal, which is the trap #681 states. Each of the five converted arms therefore carries a pair of cases in one test function. The first case gives a digest a second artifact of the same repository still holds, and the answer is zero freed bytes. The second case gives a digest nothing else holds, and the answer is the blob's own size. An implementation that never probes passes the first case and fails the second. A third case per arm attaches the same digest in a different repository of the same namespace, where it holds no bytes alive.
Every blob is seeded at a distinct non-zero size.
seedBlobStorageBlob writes size = 0, so a fixture built on it asserts 0 == 0 and passes for the wrong reason.
e2e scenario catalogs
Guardrail 12 is scoped to feat and fix, and this merge request is chore.
docs/testing/ is untouched, and the reason differs per file.
| File | Why it is untouched |
|---|---|
docs/testing/e2e/npm.md |
Its e2e.npm.lifecycle.repository-storage-counters row. The row already says the bytes come off when the purger hard-deletes the rows, and its journey stops at the unpublish. An extension of that journey to the purge needs a purge that emits, so the row travels with the emit half. |
docs/testing/e2e/maven.md |
It carries one counter scenario, e2e.maven.lifecycle.management-delete-storage-counters, and no claim in it becomes false: that row's size_bytes half already points at the purger, and nothing on main reads the figure this branch computes. Its Out of scope until the capability ships list puts remote Maven repositories outside the catalog, owned by S30 and S14, so MavenRemoteVersionReaper has no surface there and only the hosted arm does. The catalog has no row asserting size_bytes rising at a deploy, which is the publish half of the npm row; the unpublish half has no Maven counterpart by design, because S10 defines no protocol-level DELETE. That gap is worth a name and is not owed by this merge request. |
docs/testing/e2e/oci.md, docs/testing/e2e/docker.md |
Both put remote repositories out of catalog scope for the closed beta, so ContainerRemoteManifestReaper has no catalog surface. |
The plan's Testing Strategy carries the same claim about docs/testing/e2e/maven.md.
It was wrong when written rather than stale by drift: the counters row landed in f42703ddc on 2026-08-25, and the plan sentence arrived in ee1e1b656 the next day.
Guardrail 4 keeps a step merge request out of the plan file, so that half is corrected there rather than here.
Diff size, guardrail 18
25 files, +3147 / −1577, from git diff --numstat origin/main...HEAD at head bc20de0b3 over merge base 3c26bfef6.
Both endpoints are on origin, so a reader can re-derive every figure below.
22 of the files are under internal/datastore/, 2 under internal/lifecycle/, and 1 is docs/dev/storage-accounting.md, which is new.
1442 of the 3147 added lines are tests, which is 46 percent.
Reviewable production Go is 666 added lines.
1180 of the 1577 deleted lines are production Go.
Most of them are the long reap doc comments this branch moved into the new document.
| Group | Files | +/− |
|---|---|---|
The freed-byte probe, internal/datastore/lifecycle_reap_size.go (new) |
1 | +412 / −0 |
The five converted arms, the two silent arms, the repository walk, ReapTotals and the query names |
9 | +251 / −1180 |
The chunk driver's narrowing, internal/lifecycle/chunk.go |
1 | +3 / −0 |
| Production Go total | 11 | +666 / −1180 |
The new probe suites, lifecycle_reap_size_integration_test.go and lifecycle_reap_size_test.go |
2 | +1213 / −0 |
| Edits to the merged reap suites, the attachment suite and the query-name catalog | 10 | +191 / −392 |
The chunk driver suite, internal/lifecycle/chunk_integration_test.go |
1 | +38 / −5 |
| Test Go total | 13 | +1442 / −397 |
The storage-accounting document, docs/dev/storage-accounting.md (new) |
1 | +1039 / −0 |
| Total | 25 | +3147 / −1577 |
This merge request is already the result of a split.
Step 19 as the plan wrote it measured 33 files, +3955 / −340.
It was cut at the seam between the reap's computation and the emit site.
The cut removed 1538 added lines from this review, and it moved this half off a four-deep stack whose root has no approvals.
This half targets main directly, and it builds, vets and passes its own suites with no other step of the stack present.
A further split helps, and the honest answer is what it costs rather than a claim that it does not. The remaining seam is per format family: the probe machinery plus Maven, then npm, then container remote. That is three merge requests where this one stands, so the plan's Status table takes two more rows than the amendment already adds. The largest production review then falls below the 664 added lines of production Go this one carries. The plan's own estimate for that largest part is about 450, and it is an estimate rather than a measured split. It was made against a body that still carried its long-form reasoning in the Go comments. The document move then took that reasoning out of those files. The cost is three review cycles instead of one, and the shared-digest scope fixture reviewed three times. The closed beta lands 2026-09-07, and the emit half waits on a stack whose root has no approvals. The operator took the two-way split for that reason and stopped there.
Stated deviations
Guardrail 3 says that no step merge request opens before its plan merge request lands.
The S20-A plan merged long ago and is on main today.
What was in flight was an amendment to that merged plan, not a plan that arrives late.
The operator decided that the amendment and this merge request run in parallel, with no merge order between them.
This is a stated and accepted deviation rather than an oversight.
The amendment is !1914 (merged), a separate docs(plans) merge request against docs/plans/2026-08-11-s20a-lifecycle-closed-beta.md, opened in the same sitting as this one.
It splits the plan's row 19 into 19a and 19b, which moves the Status-table row count from 21 to 22.
It also corrects the Depends on line, because the plan's declared dependency on step 18 is true of the emit half alone.
The title's denominator is 22 because it names the row count the amendment sets, and that amendment merged on 2026-08-26.
So the plan on main now carries those 22 rows.
Neither merge request gated the other at any point.
Guardrail 4 is not relaxed by that decision.
This merge request edits no plan file, the Status table included.
The 19a row is recorded in !1914 (merged), the docs(plans) merge request opened in the same sitting, and that title does not quote the step marker.
Merge order
This half depends on nothing in the S20-A plan.
The emit half needs two things: this merge request, for ReapTotals.RepositoryID, and step 18 (!1885), for the destructive repository DELETE.
It therefore stacks on step 18 and lands after both.
The two unconverted arms carry one merge order between them, and it is against !1895.
!1895 wires the hosted OCI counter sink.
While it stays unmerged, ContainerReaper's zero is correct rather than a gap.
Once it merges, that arm's absent decrement becomes a real over-count.
The remainder of work item 681 must therefore land before that merge or with it.
npm remote cache fill has no merge request at all, only work item 834, so no merge order exists to state for NpmRemoteVersionReaper.
!1901 (merged)'s order is discharged rather than pending.
It merged on 2026-08-25 and this branch carries it, and f860e4aac converts MavenRemoteVersionReaper here rather than leaving the arm to work item 681.
Two findings are deferred to the emit half rather than dropped.
docs/dev/storage-accounting.md says that the format layer computes Δsize itself and passes it in.
That sentence describes an emit site, and this merge request adds none.
The npm catalog row named above is the other finding.
Both corrections belong where the emit site lands.
Known risks a reviewer accepts on the record
1. A container remote repository's size_bytes can read negative, per #836
ContainerRemoteManifestReaper computes real freed bytes, and no merged path raises repositories.size_bytes for a container remote cache fill.
ADR-007 puts the remote cache inside the repository's footprint.
The ADR therefore asks for this byte figure, and the absent increment is the gap.
That gap is recorded in #836.
Two conditions open a window on it: some caller turns this figure into a counter delta, and #836 stays open.
On this merge request no caller does, so no counter can move and the window stays shut.
The exposure begins when the emit half merges.
While both conditions hold, a container remote repository's size_bytes can read negative between a cached-manifest purge and the next reconciliation pass.
That pass recomputes the column from source rather than adjusts it.
Merged code registers the pass and schedules it on a default interval of 1h, with no enable flag.
The arm's own doc comment carries this same two-condition form.
Two facts bound the exposure.
The magnitude is manifest documents rather than layers, because cached layers live in container_remote_blobs and ContainerRemoteImageReaper still reports zero.
The blast radius is display and ordering.
The management API serializes the column and sorts on it, no serializer fails, and no aggregate sums these rows.
One further caveat covers every counter in this family, and this merge request does not introduce it. Both columns are recorded as deltas on a buffered pipeline and written by a periodic drain. A write shed under load can therefore lose a delta, which is #632.
2. File overlap that no pipeline reports
internal/datastore/query_names.go was listed here against eleven open merge requests.
Nine of them merged on 2026-08-25: !1721 (merged), !1726 (merged), !1748 (merged), !1754 (merged), !1761 (merged), !1794 (merged), !1805 (merged), !1900 (merged) and !1901 (merged).
This branch's merge base is 3c26bfef6, which carries all nine, so their overlap is already resolved in the diff a reviewer reads.
The nine new names are reap-specific, <table>_select_reap_repository and blob_storage_blobs_by_namespace_select_reap_freed_size_<family>, and each is declared once in the file.
The list is open merge requests other than this step's own two halves. !1916 is the other half and is therefore not on it: it carries these same nine constants, so a rebase drops them rather than raising the compile error this section is about. !1916's own known-risk table names the same eleven and omits this merge request for the same reason.
!1870 (merged) has since merged, at 2026-08-26T13:06:29Z as 472ef3ec0.
Its query_names.go and this branch's auto-merged with no duplicate constant, because it added the tombstone-count names and this branch the reap ones.
It also raised the name budget in internal/metrics/cardinality.go from 450 to 500, which is the file the reviewer asked about: this branch had a commit doing the same, and the rebase dropped it with git's own "patch contents already upstream", so that file left this diff entirely.
One overlap stays open.
!1895 touches internal/datastore/query_names.go.
An overlap conflicts whichever merge request lands second, and no pipeline reports a conflict of that kind.
A same-named constant in both changes is a compile error rather than a merge conflict, which is the failure a reviewer sees.
Whoever lands second reconciles the two name sets.
3. Two arms still report zero
ContainerReaper and NpmRemoteVersionReaper report zero.
The reason sits in docs/dev/storage-accounting.md, and each arm's Reap doc points there.
ContainerReaper has a second obstacle beyond the absent increment.
Its deleters do pass digests, but those come from the OCI digest column while the probe reads blob_sha256.
Both container_blobs and container_manifests declare blob_sha256 beside their digest, on the rows this walk's two pages read.
ADR-007's container reconciliation branch unions the two tables on that column.
So the obstacle is the projection rather than the schema, and the arm reports zero until the projection changes.
The order this implies is in Merge order above.
4. One pre-commit hook bypass, and it is the documented carve-out
b0c5d7bfc, the test commit, used the /implement-step hook-bypass carve-out.
At that commit the implementation is absent, so the go-test hook runs the suite against a tree the tests cannot pass on.
That is the test-first authorship contract rather than a defect, and the carve-out is scoped to that one commit.
The pre-split branch carried three such commits and the split squashed them to one, so a reviewer who counts bypasses finds exactly one.
Every other commit on this branch ran the full hook chain.
5. The freed-byte probe is switched off, and the switch is temporary
The survivor walk has no ceiling this repository can supply.
No unique index bounds how many rows of one namespace carry one digest, ADR-004 permits 25,000 versions per package with no cap on packages, and the service sets no statement_timeout, so nothing bounds the statement from inside or outside.
docs/dev/storage-accounting.md ("What the survivor walk costs") puts the 100 ms application-query budget as crossed at L around 100,000 and L around 1,000,000 at about three seconds.
Two conditions would open a window on that cost: the purger runs the statement, and the fan-out reaches that range.
runPurgeChunks closes the first.
It sets TombstoneRow.SkipFreedBytes on the row it drains, so neither the resolve nor the survivor walk runs on any path the purger dispatches, and TestPurgeWorker_Work_EveryChunkDeclinesTheFreedByteFigure pins it.
The switch comes off when emitChunkCounters lands, because a caller that reads the figure is what the cost buys.
The window opens then, not before.
Neither of the two production lookups that would size the fan-out was run, and neither is reachable from this branch: the deployed sweep_interval and pool size, and the distribution of rows per (namespace_id, blob_sha256) in maven_files and npm_files.
The two in-repo bounds are ADR-004's enforced 25,000 versions per package, which is the ceiling rather than the observed value, and the basis every recent migration states for treating namespaces as empty, which stops holding the day the first namespace is created.
Neither substitutes for the two lookups.
The plan puts closed-beta launch at 2026-09-07.
Findings that reach the reviewer as text
Both were triaged as fix with the disposition "reaches the reviewer".
Neither is a code change.
C3. The storage-accounting work-item pointers now live in one document
The reap arms' long-form storage-accounting reasoning moved out of the Go comments and into docs/dev/storage-accounting.md, and the work-item URLs went with it.
No Go file this merge request touches names work item 681, 632, 686 or 836 now.
ContainerRemoteImageReaper.Reap still names work item 728, and that pointer is about an unscanned tombstone source rather than about storage accounting.
The move reads as the right call under the comment caps.
What it removed is the second place a wrong pointer would have shown up, and one pointer is wrong.
docs/dev/storage-accounting.md assigns the two silent arms to work item 681.
That item's own Scope section names the arms this stack converts, and none of the silent ones.
So a reader who closes 681 on this stack leaves the silent arms' obligation recorded in no work item and no longer on the arms.
This is the consequence of a choice the operator made with the full framing in front of them, rather than a defect.
Note 3736200072 on this merge request states the gap in full and is the record of it.
This entry points at that note rather than repeating it.
One thing changed after that note: f860e4aac converted MavenRemoteVersionReaper, so the count it discusses is two arms rather than three.
Which work item carries the two is a scope question, and this merge request does not answer it.
N1. repositories.size_bytes can read negative on the npm packument path
Nothing raises repositories.size_bytes for an npm packument write, and NpmPackageReaper reports the packument digests its cache leg frees.
Once a caller turns that figure into a counter delta, the repository's size_bytes can read negative.
It stays negative until a reconciliation pass recomputes the column from source rows.
size_bytes is a required field of the public Repository schema and a sort key on the repository list.
So the reading is externally visible rather than internal bookkeeping.
Work item 836 tracks it. That item is scoped to the container remote cache fill, so it under-scopes this path, and it is still the item the gap reaches. Nothing reads the figure on this branch, so the window is shut here. It opens with the emit half.
Checks
The full internal/datastore integration suite passed on the pre-split branch, which carried every file in this diff.
Before you read a datastore run, set -timeout higher than the 600 s default of go test.
The complete runs took 504 s to 556 s, which is 84 to 93 percent of that default.
Guardrail 7: every integration-tagged package was linted with the pinned golangci-lint 2.12.2, under --build-tags=integration --max-same-issues=0 --max-issues-per-linter=0 --uniq-by-line=false.
Findings in internal/datastore are package-wide and pre-existing, and none sits on a production file this merge request writes.
The pipeline here does not compile //go:build integration files at all, which is why guardrail 7 asks for that separate run.
One CI-visible consequence comes with the Maven remote conversion.
MavenRemoteVersionReaper.Reap became a dupl twin of MavenVersionReaper.Reap, and those two were the only findings the tagless golangci-lint run reported in internal/datastore.
Both are suppressed with //nolint:dupl where they fire, measured before and after.
go-jet's generated table structs are distinct types, so the two bodies cannot be shared.
Those two directives are in 48a51a2b4 rather than in f860e4aac.
The nine new query names take the declared total to 465, inside the 500-name budget in internal/metrics/cardinality.go.
The count is 453 names in internal/datastore/query_names.go plus 12 in internal/storage/queries.go, measured at bc20de0b3.
!1870 (merged) raised that budget from 450 to 500 when it merged, so this merge request no longer raises it.
TestNameBudget_CoversEveryDeclaredQueryName still runs on this branch, and !1948 (merged) deleted it on main.
So while this branch is based before !1948 (merged) the test reads both catalogs here; once the branch rebases past it, nothing reads either catalog against the budget and the merged catalogs are read by hand.
Issue #908 decides what replaces the check.
TestEveryStatementIsInstrumented gains the probe's two functions, so both new statements carry a database_query_duration_seconds{name=…} series.
Guardrail 8 exempts chore, so the line below is informational rather than owed.
This merge request is the computation half of work item 681, and that item stays open for the two arms it does not convert.
Finding C3 above says why the item's own scope does not name those two.
Database Review Evidence
Query mode only. This merge request changes no file under internal/datastore/migrations/sql/, so migration mode had nothing to collect and did not run.
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 nine statements below are the ones this merge request adds. They are package constants in internal/datastore/lifecycle_reap_size.go dispatched through db.QueryContext, so the rendered SQL is the constant verbatim and no builder output had to be extracted. The reap arms this merge request also edits (lifecycle_reap_container.go, lifecycle_reap_container_remote.go, lifecycle_reap_maven.go, lifecycle_reap_maven_remote.go, lifecycle_reap_npm.go, lifecycle_reap_npm_remote.go, lifecycle_reap_repository.go) change no statement text against the merge base; they gain calls to the two probe functions.
| Method | Plan node | Index | Rows (plan / actual) | Cost | Time | Buffers (hit / read) | Partitions |
|---|---|---|---|---|---|---|---|
datastore.resolveReapRepository.MavenVersion |
Limit → Nested Loop | maven_versions_p49_id_maven_package_id_namespace_id_idx, maven_packages_p49_pkey, maven_repositories_p49_pkey |
1 / 1 | 16.78 | 0.163 ms | 8 / 0 | 1/64, 1/64, 1/64 |
datastore.resolveReapRepository.NpmPackage |
Limit → Nested Loop | npm_packages_p49_pkey |
1 / 1 | 9.39 | 0.057 ms | 4 / 0 | 1/64, 1/64 |
datastore.resolveReapRepository.NpmVersion |
Limit → Nested Loop | npm_versions_p49_pkey, npm_packages_p49_pkey, npm_repositories_p49_pkey |
1 / 1 | 16.78 | 0.096 ms | 8 / 0 | 1/64, 1/64, 1/64 |
datastore.resolveReapRepository.ContainerRemoteManifest |
Limit → Nested Loop | container_remote_manifests_p49_pkey, container_remote_images_p49_pkey, container_remote_repositories_p49_pkey |
1 / 1 | 16.78 | 0.101 ms | 8 / 0 | 1/64, 1/64, 1/64 |
datastore.resolveReapRepository.MavenRemoteVersion |
Limit → Nested Loop | maven_remote_versions_p49_id_maven_remote_package_id_namesp_idx, maven_remote_packages_p49_pkey, maven_remote_repositories_p49_pkey |
1 / 1 | 16.78 | 0.115 ms | 8 / 0 | 1/64, 1/64, 1/64 |
datastore.sumFreedRepositoryBytes.MavenHosted |
Aggregate → Hash Right Anti Join | none at this seed size; see the note below | 1 / 1 | 586.29 | 6.187 ms | 296 / 0 | 1/64, 1/64, 1/64, 1/64 |
datastore.sumFreedRepositoryBytes.NpmHosted |
Aggregate → Hash Right Anti Join | none at this seed size; see the note below | 1 / 1 | 1036.62 | 9.142 ms | 446 / 0 | 1/64, 1/64, 1/64, 1/64, 1/64, 1/64 |
datastore.sumFreedRepositoryBytes.ContainerRemote |
Aggregate → Nested Loop | container_remote_manifests_p49_namespace_id_blob_sha256_idx, container_remote_blobs_p49_namespace_id_blob_sha256_idx, blob_storage_blobs_by_namespace_p49_pkey |
1 / 1 | 26.09 | 0.466 ms | 19 / 0 | 1/64, 1/64, 1/64, 1/64, 1/64 |
datastore.sumFreedRepositoryBytes.MavenRemote |
Aggregate → Hash Right Anti Join | maven_remote_packages_p49_namespace_id_maven_remote_reposi_idx1 on the repository leg; none at the survivor leaf at this seed size |
1 / 1 | 539.06 | 5.119 ms | 294 / 0 | 1/64, 1/64, 1/64, 1/64 |
No partition fan-out. Every partitioned table in every plan is read at one partition of 64, because each statement binds namespace_id at every level.
Query notes:
datastore.sumFreedRepositoryBytes.MavenHostedanddatastore.sumFreedRepositoryBytes.NpmHosted: Seq Scan over the namespace partition of every survivor table, at the skill's 5000-row seed with a 1000-digest page. The planner builds a hash anti-join rather than probing(namespace_id, blob_sha256)once per bound digest. A second run at 50,000 rows in the namespace shows the crossover and the plan flips to the per-digest index probe, so the scan is the seed's cardinality rather than a missing index. The figures are inWhere the survivor anti-join's cost goesbelow.- The
SELECT DISTINCT u.sha256 FROM unnest($3::bytea[])node estimates 200 distinct rows whatever the array holds (HashAggregate ... rows=200against 1000 actual, in every sum plan). That five-fold under-estimate is what makes the hash build look cheap against the index probes. It is PostgreSQL's default forDISTINCTover a set-returning function rather than something the statement can restate. - Cold-relcache planning cost. The first plan of a session for a statement over these 64-partition tables spends 3.6 ms to 9.5 ms with 234 to 1022 planning buffers; a warm re-plan of the same statement spends 0.195 ms to 1.9 ms. No plan in this run enumerated more than one partition per table, which is the difference from the planner cost #815 records on
CountTombstonesByLevel: every statement here bindsnamespace_idas a plan-time literal and prunes before the joins are planned. datastore.sumFreedRepositoryBytes.MavenRemote: Seq Scan over the namespace partition ofmaven_remote_filesand of the shadow table, at the 5000-row seed with a 1000-digest page. That is the regime the first bullet above describes for the two hosted sums. The repository leg takes an index path here,maven_remote_packages_p49_namespace_id_maven_remote_reposi_idx1, somaven_remote_filesis the only survivor table scanned. Bound to one digest instead, the same statement flips to the probe regime:index_maven_remote_files_on_ns_id_blob_sha256, 11 buffers, 0.256 ms.Where the survivor anti-join's cost goesbelow is the sweep for that crossover, and it was measured over the seven statements above, so its leaf-index list does not namemaven_remote_filesand its worst-case paragraph does not name the Maven remote page. Both extend without moving a figure in it: the missing leaf index is(namespace_id, blob_sha256)like the five it lists, andMavenRemoteVersionReaper.Reapclamps its cache file page to the sameMaxLifecycleReapPageSizeof 1,000.- Planning cost of the two statements above that the sweep did not cover. The resolve statement's first plan of a session cost 2.751 ms at 286 planning buffers, and the sum's cost 5.787 ms at 501 buffers with 1 read. The 2.751 ms sits below the 3.6 ms floor the cold-relcache bullet above states, so read that bullet's range as the seven statements it was measured over rather than all nine. The partition reading is unchanged: both plans prune every partitioned table to one partition of 64.
Where the survivor anti-join's cost goes
The anti-join's leaf indexes are (namespace_id, blob_sha256) on maven_files, npm_files, npm_metadata_files, container_remote_manifests and container_remote_blobs. None carries a repository column, so the statement cannot narrow by repository at the leaf. This sweep measures what that costs, over the Maven arm, with five repositories inside one namespace and the bind fixed on the first of them.
Namespace maven_files rows |
Bound digests | Plan node | Leaf access | Buffers | Execution |
|---|---|---|---|---|---|
| 5,000 | 1 | Nested Loop Anti Join | maven_files_p49_namespace_id_blob_sha256_idx |
11 | 0.255 ms |
| 5,000 | 10 | Nested Loop Anti Join | maven_files_p49_namespace_id_blob_sha256_idx |
104 | 0.260 ms |
| 5,000 | 100 | Hash Right Anti Join | Seq Scan, whole namespace partition | 296 | 5.281 ms |
| 5,000 | 1,000 | Hash Right Anti Join | Seq Scan, whole namespace partition | 296 | 6.187 ms |
| 50,000 | 1 | Nested Loop Anti Join | maven_files_p49_namespace_id_blob_sha256_idx |
16 | 0.272 ms |
| 50,000 | 100 | Nested Loop Anti Join | maven_files_p49_namespace_id_blob_sha256_idx |
1,520 | 1.041 ms |
| 50,000 | 1,000 | Nested Loop Anti Join | maven_files_p49_namespace_id_blob_sha256_idx |
15,094 | 8.804 ms |
Three readings a reviewer can take from that.
The hash-build regime is bounded by the namespace partition and flat in the page: 296 buffers at 100 digests and at 1,000 alike. The planner picks it only while scanning the whole partition is cheaper than the probes, which at 5,000 rows it is.
The probe regime is proportional to the page, not to the namespace: roughly 15 buffers per bound digest at 50,000 rows against roughly 11 at 5,000, the difference being index depth. Ten times the namespace does not cost ten times the read.
The absent repository column costs one extra index lookup per probe that hits, not a namespace scan. In the 50,000-row plans the repository test is maven_repositories_p49_pkey with repository_id as a filter, 2,000 buffers of the 15,094 at the 1,000-digest page.
The worst case a reap arm can reach today is the Maven or hosted npm page at MaxLifecycleReapPageSize, which is 1,000. On a 50,000-row namespace that is 8.8 ms and 15,094 buffer hits, inside the chunk's open transaction. The container remote arm deletes one manifest row per call, so its array holds one digest and its measured plan is the 19-buffer, 0.466 ms row in the Queries table.
datastore.resolveReapRepository.MavenVersion
Summary: Plan matches the method's intent. Every level prunes to the one partition holding the row and resolves through a unique index, ending at maven_repositories.repository_id in 8 buffer hits. Estimates match actuals at 1 row per node. No anomalies.
Seed shape: namespaces=1, repositories=5, maven_repositories=5, maven_packages=5000, maven_versions=5000, maven_files=5000, blob_storage_blobs=5000, blob_storage_blobs_by_namespace=5000, blob_storage_attachments=5000
Rendered SQL:
SELECT r.repository_id
FROM maven_versions v
JOIN maven_packages p
ON p.id = v.maven_package_id AND p.namespace_id = v.namespace_id
JOIN maven_repositories r
ON r.id = p.maven_repository_id AND r.namespace_id = p.namespace_id
WHERE v.namespace_id = $1 AND p.namespace_id = $1 AND r.namespace_id = $1
AND v.id = $2
LIMIT 1Bound args: [00000000-0000-4000-8000-000000000001, 20000000-0000-4000-8000-000000000001]
Returned: a0000000-0000-4000-8000-000000000001
Plan (EXPLAIN (ANALYZE, BUFFERS) output):
Limit (cost=0.70..16.78 rows=1 width=16) (actual time=0.113..0.115 rows=1 loops=1)
Buffers: shared hit=8
-> Nested Loop (cost=0.70..16.78 rows=1 width=16) (actual time=0.112..0.113 rows=1 loops=1)
Buffers: shared hit=8
-> Nested Loop (cost=0.56..16.62 rows=1 width=32) (actual time=0.092..0.093 rows=1 loops=1)
Buffers: shared hit=6
-> Index Only Scan using maven_versions_p49_id_maven_package_id_namespace_id_idx on maven_versions_p49 v (cost=0.28..8.30 rows=1 width=32) (actual time=0.059..0.059 rows=1 loops=1)
Index Cond: ((id = '20000000-0000-4000-8000-000000000001'::uuid) AND (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid))
Heap Fetches: 1
Buffers: shared hit=3
-> Index Scan using maven_packages_p49_pkey on maven_packages_p49 p (cost=0.28..8.30 rows=1 width=48) (actual time=0.031..0.031 rows=1 loops=1)
Index Cond: ((id = v.maven_package_id) AND (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid))
Buffers: shared hit=3
-> Index Scan using maven_repositories_p49_pkey on maven_repositories_p49 r (cost=0.13..0.15 rows=1 width=48) (actual time=0.018..0.019 rows=1 loops=1)
Index Cond: ((id = p.maven_repository_id) AND (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid))
Buffers: shared hit=2
Planning:
Buffers: shared hit=263 read=1
Planning Time: 3.842 ms
Execution Time: 0.163 msTimings: planning 3.842 ms, execution 0.163 ms, total 4.005 ms.
datastore.resolveReapRepository.NpmPackage
Summary: Plan matches the method's intent, and this is the shortest of the four chains because npm_metadata_files hangs off the package rather than a version. The package resolves through its primary key in one partition, and npm_repositories falls to a Seq Scan of five rows in the partition, which is the seed's cardinality rather than a missing index. No anomalies.
Seed shape: namespaces=1, repositories=5, npm_repositories=5, npm_packages=5000, npm_versions=5000, npm_files=5000, npm_metadata_files=5000, blob_storage_blobs=5000, blob_storage_blobs_by_namespace=5000, blob_storage_attachments=5000
Rendered SQL:
SELECT r.repository_id
FROM npm_packages p
JOIN npm_repositories r
ON r.id = p.npm_repository_id AND r.namespace_id = p.namespace_id
WHERE p.namespace_id = $1 AND r.namespace_id = $1
AND p.id = $2
LIMIT 1Bound args: [00000000-0000-4000-8000-000000000001, b2000000-0000-4000-8000-000000000001]
Plan (EXPLAIN (ANALYZE, BUFFERS) output):
Limit (cost=0.28..9.39 rows=1 width=16) (actual time=0.038..0.039 rows=1 loops=1)
Buffers: shared hit=4
-> Nested Loop (cost=0.28..9.39 rows=1 width=16) (actual time=0.037..0.038 rows=1 loops=1)
Buffers: shared hit=4
-> Index Scan using npm_packages_p49_pkey on npm_packages_p49 p (cost=0.28..8.30 rows=1 width=32) (actual time=0.030..0.030 rows=1 loops=1)
Index Cond: ((id = 'b2000000-0000-4000-8000-000000000001'::uuid) AND (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid))
Buffers: shared hit=3
-> Seq Scan on npm_repositories_p49 r (cost=0.00..1.07 rows=1 width=48) (actual time=0.006..0.006 rows=1 loops=1)
Filter: ((namespace_id = '00000000-0000-4000-8000-000000000001'::uuid) AND (id = p.npm_repository_id))
Buffers: shared hit=1
Planning:
Buffers: shared hit=2
Planning Time: 0.195 ms
Execution Time: 0.057 msTimings: planning 0.195 ms, execution 0.057 ms, total 0.252 ms.
datastore.resolveReapRepository.NpmVersion
Summary: Plan matches the method's intent, and it is the Maven version chain one format over: three unique-index lookups, each pruned to one partition, 8 buffer hits. Estimates match actuals at 1 row per node. No anomalies.
Seed shape: same npm seed as datastore.resolveReapRepository.NpmPackage above.
Rendered SQL:
SELECT r.repository_id
FROM npm_versions v
JOIN npm_packages p
ON p.id = v.npm_package_id AND p.namespace_id = v.namespace_id
JOIN npm_repositories r
ON r.id = p.npm_repository_id AND r.namespace_id = p.namespace_id
WHERE v.namespace_id = $1 AND p.namespace_id = $1 AND r.namespace_id = $1
AND v.id = $2
LIMIT 1Bound args: [00000000-0000-4000-8000-000000000001, b3000000-0000-4000-8000-000000000001]
Plan (EXPLAIN (ANALYZE, BUFFERS) output):
Limit (cost=0.70..16.78 rows=1 width=16) (actual time=0.057..0.058 rows=1 loops=1)
Buffers: shared hit=8
-> Nested Loop (cost=0.70..16.78 rows=1 width=16) (actual time=0.055..0.056 rows=1 loops=1)
Buffers: shared hit=8
-> Nested Loop (cost=0.56..16.62 rows=1 width=32) (actual time=0.040..0.041 rows=1 loops=1)
Buffers: shared hit=6
-> Index Scan using npm_versions_p49_pkey on npm_versions_p49 v (cost=0.28..8.30 rows=1 width=32) (actual time=0.021..0.021 rows=1 loops=1)
Index Cond: ((id = 'b3000000-0000-4000-8000-000000000001'::uuid) AND (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid))
Buffers: shared hit=3
-> Index Scan using npm_packages_p49_pkey on npm_packages_p49 p (cost=0.28..8.30 rows=1 width=48) (actual time=0.017..0.017 rows=1 loops=1)
Index Cond: ((id = v.npm_package_id) AND (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid))
Buffers: shared hit=3
-> Index Scan using npm_repositories_p49_pkey on npm_repositories_p49 r (cost=0.13..0.15 rows=1 width=48) (actual time=0.015..0.015 rows=1 loops=1)
Index Cond: ((id = p.npm_repository_id) AND (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid))
Buffers: shared hit=2
Planning:
Buffers: shared hit=869
Planning Time: 5.551 ms
Execution Time: 0.096 msTimings: planning 5.551 ms, execution 0.096 ms, total 5.647 ms. The planning figure is the session's first plan over these tables; the datastore.resolveReapRepository.NpmPackage row above, planned next in the same session, spends 0.195 ms.
datastore.resolveReapRepository.ContainerRemoteManifest
Summary: Plan matches the method's intent, and it is the same three-lookup shape as the two version chains: manifest to image to remote repository, each pruned to one partition, 8 buffer hits. Estimates match actuals at 1 row per node. No anomalies.
Seed shape: namespaces=1, repositories=5, container_remote_repositories=5, container_remote_images=5000, container_remote_manifests=5000, container_remote_blobs=5000, blob_storage_blobs=5000, blob_storage_blobs_by_namespace=5000, blob_storage_attachments=5000
Rendered SQL:
SELECT r.repository_id
FROM container_remote_manifests m
JOIN container_remote_images i
ON i.id = m.container_remote_image_id AND i.namespace_id = m.namespace_id
JOIN container_remote_repositories r
ON r.id = i.container_remote_repository_id AND r.namespace_id = i.namespace_id
WHERE m.namespace_id = $1 AND i.namespace_id = $1 AND r.namespace_id = $1
AND m.id = $2
LIMIT 1Bound args: [00000000-0000-4000-8000-000000000001, c3000000-0000-4000-8000-000000000001]
Plan (EXPLAIN (ANALYZE, BUFFERS) output):
Limit (cost=0.70..16.78 rows=1 width=16) (actual time=0.060..0.062 rows=1 loops=1)
Buffers: shared hit=8
-> Nested Loop (cost=0.70..16.78 rows=1 width=16) (actual time=0.059..0.060 rows=1 loops=1)
Buffers: shared hit=8
-> Nested Loop (cost=0.56..16.62 rows=1 width=32) (actual time=0.044..0.044 rows=1 loops=1)
Buffers: shared hit=6
-> Index Scan using container_remote_manifests_p49_pkey on container_remote_manifests_p49 m (cost=0.28..8.30 rows=1 width=32) (actual time=0.024..0.025 rows=1 loops=1)
Index Cond: ((id = 'c3000000-0000-4000-8000-000000000001'::uuid) AND (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid))
Buffers: shared hit=3
-> Index Scan using container_remote_images_p49_pkey on container_remote_images_p49 i (cost=0.28..8.30 rows=1 width=48) (actual time=0.017..0.017 rows=1 loops=1)
Index Cond: ((id = m.container_remote_image_id) AND (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid))
Buffers: shared hit=3
-> Index Scan using container_remote_repositories_p49_pkey on container_remote_repositories_p49 r (cost=0.13..0.15 rows=1 width=48) (actual time=0.015..0.015 rows=1 loops=1)
Index Cond: ((id = i.container_remote_repository_id) AND (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid))
Buffers: shared hit=2
Planning:
Buffers: shared hit=1022 read=1
Planning Time: 8.729 ms
Execution Time: 0.101 msTimings: planning 8.729 ms, execution 0.101 ms, total 8.830 ms. Session's first plan again; the sum statement planned next in the same session spends 5.587 ms and the warm re-plans in the sweep spend under 2 ms.
datastore.resolveReapRepository.MavenRemoteVersion
Summary: Plan matches the method's intent, and it is the Maven version chain over the remote-cache tables: three lookups, each pruned to one partition, 8 buffer hits. The cache version row resolves index-only through unique_maven_remote_versions_id_pkg_id_ns_id, which is the covering shape the hosted Maven row above uses one family over. Estimates match actuals at 1 row per node. No anomalies.
Seed shape: namespaces=1, repositories=5, maven_remote_repositories=5, maven_remote_packages=5000, maven_remote_versions=5000, maven_remote_files=5000, blob_storage_blobs=5000, blob_storage_blobs_by_namespace=5000, blob_storage_attachments=5000. Cache package i sits in remote repository ((i - 1) mod 5) + 1, and cache file i carries digest i. blob_storage_blobs_by_namespace is not seeded directly: trg_blob_storage_blobs_shadow_insert fills it from the blob_storage_blobs insert.
Rendered SQL:
SELECT r.repository_id
FROM maven_remote_versions v
JOIN maven_remote_packages p
ON p.id = v.maven_remote_package_id AND p.namespace_id = v.namespace_id
JOIN maven_remote_repositories r
ON r.id = p.maven_remote_repository_id AND r.namespace_id = p.namespace_id
WHERE v.namespace_id = $1 AND p.namespace_id = $1 AND r.namespace_id = $1
AND v.id = $2
LIMIT 1Bound args: [00000000-0000-4000-8000-000000000001, d3000000-0000-4000-8000-000000000001]
Returned: a0000000-0000-4000-8000-000000000001
Plan (EXPLAIN (ANALYZE, BUFFERS) output):
Limit (cost=0.70..16.78 rows=1 width=16) (actual time=0.074..0.075 rows=1 loops=1)
Buffers: shared hit=8
-> Nested Loop (cost=0.70..16.78 rows=1 width=16) (actual time=0.073..0.074 rows=1 loops=1)
Buffers: shared hit=8
-> Nested Loop (cost=0.56..16.62 rows=1 width=32) (actual time=0.054..0.054 rows=1 loops=1)
Buffers: shared hit=6
-> Index Only Scan using maven_remote_versions_p49_id_maven_remote_package_id_namesp_idx on maven_remote_versions_p49 v (cost=0.28..8.30 rows=1 width=32) (actual time=0.033..0.033 rows=1 loops=1)
Index Cond: ((id = 'd3000000-0000-4000-8000-000000000001'::uuid) AND (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid))
Heap Fetches: 1
Buffers: shared hit=3
-> Index Scan using maven_remote_packages_p49_pkey on maven_remote_packages_p49 p (cost=0.28..8.30 rows=1 width=48) (actual time=0.018..0.018 rows=1 loops=1)
Index Cond: ((id = v.maven_remote_package_id) AND (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid))
Buffers: shared hit=3
-> Index Scan using maven_remote_repositories_p49_pkey on maven_remote_repositories_p49 r (cost=0.13..0.15 rows=1 width=48) (actual time=0.019..0.019 rows=1 loops=1)
Index Cond: ((id = p.maven_remote_repository_id) AND (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid))
Buffers: shared hit=2
Planning:
Buffers: shared hit=286
Planning Time: 2.751 ms
Execution Time: 0.115 msTimings: planning 2.751 ms, execution 0.115 ms, total 2.866 ms. The planning figure is this statement's first plan in its session. A re-plan in a session that had already executed it spends 0.497 ms at 2 planning buffers.
datastore.sumFreedRepositoryBytes.MavenHosted
Summary: The statement answers the right number. The seed attaches 1,000 of the bound digests inside the namespace and 200 of them under the bound repository, and the sum comes back at 1,200,800, which is the seeded blob sizes of exactly the 800 digests that survive nowhere in that repository. The plan prunes every table to one partition of 64 but, at 5,000 rows, hash-builds the whole namespace partition rather than probing (namespace_id, blob_sha256) per digest; Where the survivor anti-join's cost goes above measures both regimes and shows the crossover.
Seed shape: namespaces=1, repositories=5, maven_repositories=5, maven_packages=5000, maven_versions=5000, maven_files=5000, blob_storage_blobs=5000, blob_storage_blobs_by_namespace=5000, blob_storage_attachments=5000. Package i sits in repository ((i - 1) mod 5) + 1, and file i carries digest i, so 200 of the first 1,000 digests survive under the bound repository.
Rendered SQL:
SELECT COALESCE(SUM(b.size), 0)::bigint
FROM (SELECT DISTINCT u.sha256 FROM unnest($3::bytea[]) AS u(sha256)) AS d
JOIN blob_storage_blobs_by_namespace b
ON b.namespace_id = $1 AND b.sha256 = d.sha256
WHERE NOT EXISTS (
SELECT 1
FROM maven_files f
JOIN maven_packages p
ON p.id = f.maven_package_id AND p.namespace_id = f.namespace_id
JOIN maven_repositories r
ON r.id = p.maven_repository_id AND r.namespace_id = p.namespace_id
WHERE f.namespace_id = $1 AND p.namespace_id = $1 AND r.namespace_id = $1
AND r.repository_id = $2 AND f.blob_sha256 = d.sha256
)Bound args: [00000000-0000-4000-8000-000000000001, a0000000-0000-4000-8000-000000000001, <1000 digests, sha256 = the 32-byte big-endian encodings of 1 through 1000>]. The page size is 1,000 because MaxLifecycleReapPageSize is what both hosted arms clamp their page to before this statement runs.
Returned: 1200800
Plan (EXPLAIN (ANALYZE, BUFFERS) output):
Aggregate (cost=586.28..586.29 rows=1 width=8) (actual time=5.927..5.931 rows=1 loops=1)
Buffers: shared hit=296
-> Hash Right Anti Join (cost=324.48..585.88 rows=160 width=8) (actual time=5.800..5.876 rows=800 loops=1)
Hash Cond: (f.blob_sha256 = u.sha256)
Buffers: shared hit=296
-> Hash Join (cost=172.34..429.59 rows=1000 width=33) (actual time=1.460..2.908 rows=1000 loops=1)
Hash Cond: (f.maven_package_id = p.id)
Buffers: shared hit=239
-> Seq Scan on maven_files_p49 f (cost=0.00..228.50 rows=5000 width=65) (actual time=0.011..0.799 rows=5000 loops=1)
Filter: (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid)
Buffers: shared hit=166
-> Hash (cost=159.84..159.84 rows=1000 width=32) (actual time=1.432..1.434 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 71kB
Buffers: shared hit=73
-> Hash Join (cost=1.09..159.84 rows=1000 width=32) (actual time=0.069..1.233 rows=1000 loops=1)
Hash Cond: (p.maven_repository_id = r.id)
Buffers: shared hit=73
-> Seq Scan on maven_packages_p49 p (cost=0.00..134.50 rows=5000 width=48) (actual time=0.005..0.637 rows=5000 loops=1)
Filter: (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid)
Buffers: shared hit=72
-> Hash (cost=1.07..1.07 rows=1 width=32) (actual time=0.038..0.038 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on maven_repositories_p49 r (cost=0.00..1.07 rows=1 width=32) (actual time=0.006..0.008 rows=1 loops=1)
Filter: ((namespace_id = '00000000-0000-4000-8000-000000000001'::uuid) AND (repository_id = 'a0000000-0000-4000-8000-000000000001'::uuid))
Rows Removed by Filter: 4
Buffers: shared hit=1
-> Hash (cost=149.64..149.64 rows=200 width=40) (actual time=2.721..2.722 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 87kB
Buffers: shared hit=57
-> Hash Join (cost=17.00..149.64 rows=200 width=40) (actual time=0.996..2.463 rows=1000 loops=1)
Hash Cond: (b.sha256 = u.sha256)
Buffers: shared hit=57
-> Seq Scan on blob_storage_blobs_by_namespace_p49 b (cost=0.00..119.50 rows=5000 width=41) (actual time=0.013..0.763 rows=5000 loops=1)
Filter: (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid)
Buffers: shared hit=57
-> Hash (cost=14.50..14.50 rows=200 width=32) (actual time=0.971..0.971 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 72kB
-> HashAggregate (cost=12.50..14.50 rows=200 width=32) (actual time=0.641..0.750 rows=1000 loops=1)
Group Key: u.sha256
Batches: 1 Memory Usage: 209kB
-> Function Scan on unnest u (cost=0.00..10.00 rows=1000 width=32) (actual time=0.129..0.183 rows=1000 loops=1)
Planning:
Buffers: shared hit=65 read=2 dirtied=1
Planning Time: 4.085 ms
Execution Time: 6.187 msTimings: planning 4.085 ms, execution 6.187 ms, total 10.272 ms.
datastore.sumFreedRepositoryBytes.NpmHosted
Summary: The statement answers the right number across both survivor arms. The seed puts 200 of the bound page's digests under an npm_files row of the bound repository and a disjoint 200 under an npm_metadata_files row of it, and the sum comes back at 900,900, which is the seeded blob sizes of exactly the 600 digests neither arm holds. This is the widest of the three sums: two correlated NOT EXISTS arms over five distinct tables. Plan shape and the Seq Scan caveat are the Maven arm's, one format over.
Seed shape: namespaces=1, repositories=5, npm_repositories=5, npm_packages=5000, npm_versions=5000, npm_files=5000, npm_metadata_files=5000, blob_storage_blobs=5000, blob_storage_blobs_by_namespace=5000, blob_storage_attachments=5000. Package i sits in repository ((i - 1) mod 5) + 1; its version's file carries digest i and its packument carries digest ((i + 2500) mod 5000) + 1, so the two arms hold disjoint slices of the bound page.
Rendered SQL:
SELECT COALESCE(SUM(b.size), 0)::bigint
FROM (SELECT DISTINCT u.sha256 FROM unnest($3::bytea[]) AS u(sha256)) AS d
JOIN blob_storage_blobs_by_namespace b
ON b.namespace_id = $1 AND b.sha256 = d.sha256
WHERE NOT EXISTS (
SELECT 1
FROM npm_files f
JOIN npm_versions v
ON v.id = f.npm_version_id AND v.namespace_id = f.namespace_id
JOIN npm_packages p
ON p.id = v.npm_package_id AND p.namespace_id = v.namespace_id
JOIN npm_repositories r
ON r.id = p.npm_repository_id AND r.namespace_id = p.namespace_id
WHERE f.namespace_id = $1 AND v.namespace_id = $1 AND p.namespace_id = $1
AND r.namespace_id = $1
AND r.repository_id = $2 AND f.blob_sha256 = d.sha256
)
AND NOT EXISTS (
SELECT 1
FROM npm_metadata_files mf
JOIN npm_packages p
ON p.id = mf.npm_package_id AND p.namespace_id = mf.namespace_id
JOIN npm_repositories r
ON r.id = p.npm_repository_id AND r.namespace_id = p.namespace_id
WHERE mf.namespace_id = $1 AND p.namespace_id = $1 AND r.namespace_id = $1
AND r.repository_id = $2 AND mf.blob_sha256 = d.sha256
)Bound args: [00000000-0000-4000-8000-000000000001, a0000000-0000-4000-8000-000000000001, <1000 digests, sha256 = the 32-byte big-endian encodings of 1 through 1000>]
Returned: 900900
Plan (EXPLAIN (ANALYZE, BUFFERS) output):
Aggregate (cost=1036.61..1036.62 rows=1 width=8) (actual time=8.781..8.790 rows=1 loops=1)
Buffers: shared hit=446
-> Hash Right Anti Join (cost=858.96..1036.28 rows=128 width=8) (actual time=8.699..8.753 rows=600 loops=1)
Hash Cond: (mf.blob_sha256 = u.sha256)
Buffers: shared hit=446
-> Hash Join (cost=171.34..344.59 rows=1000 width=33) (actual time=1.182..2.538 rows=1000 loops=1)
Hash Cond: (mf.npm_package_id = p_1.id)
Buffers: shared hit=154
-> Seq Scan on npm_metadata_files_p49 mf (cost=0.00..144.50 rows=5000 width=65) (actual time=0.007..0.621 rows=5000 loops=1)
Filter: (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid)
Buffers: shared hit=82
-> Hash (cost=158.84..158.84 rows=1000 width=32) (actual time=1.158..1.160 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 71kB
Buffers: shared hit=72
-> Hash Join (cost=1.09..158.84 rows=1000 width=32) (actual time=0.034..0.958 rows=1000 loops=1)
Hash Cond: (p_1.npm_repository_id = r_1.id)
Buffers: shared hit=72
-> Seq Scan on npm_packages_p49 p_1 (cost=0.00..133.50 rows=5000 width=48) (actual time=0.005..0.511 rows=5000 loops=1)
Filter: (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid)
Buffers: shared hit=71
-> Hash (cost=1.07..1.07 rows=1 width=32) (actual time=0.014..0.014 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on npm_repositories_p49 r_1 (cost=0.00..1.07 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=1)
Filter: ((namespace_id = '00000000-0000-4000-8000-000000000001'::uuid) AND (repository_id = 'a0000000-0000-4000-8000-000000000001'::uuid))
Rows Removed by Filter: 4
Buffers: shared hit=1
-> Hash (cost=685.63..685.63 rows=160 width=40) (actual time=6.016..6.021 rows=800 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 71kB
Buffers: shared hit=292
-> Hash Right Anti Join (cost=499.23..685.63 rows=160 width=40) (actual time=5.796..5.865 rows=800 loops=1)
Hash Cond: (f.blob_sha256 = u.sha256)
Buffers: shared hit=292
-> Hash Join (cost=347.09..529.34 rows=1000 width=33) (actual time=2.446..3.591 rows=1000 loops=1)
Hash Cond: (f.npm_version_id = v.id)
Buffers: shared hit=235
-> Seq Scan on npm_files_p49 f (cost=0.00..153.50 rows=5000 width=65) (actual time=0.006..0.616 rows=5000 loops=1)
Filter: (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid)
Buffers: shared hit=91
-> Hash (cost=334.59..334.59 rows=1000 width=32) (actual time=2.420..2.422 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 71kB
Buffers: shared hit=144
-> Hash Join (cost=171.34..334.59 rows=1000 width=32) (actual time=1.178..2.260 rows=1000 loops=1)
Hash Cond: (v.npm_package_id = p.id)
Buffers: shared hit=144
-> Seq Scan on npm_versions_p49 v (cost=0.00..134.50 rows=5000 width=48) (actual time=0.006..0.544 rows=5000 loops=1)
Filter: (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid)
Buffers: shared hit=72
-> Hash (cost=158.84..158.84 rows=1000 width=32) (actual time=1.158..1.160 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 71kB
Buffers: shared hit=72
-> Hash Join (cost=1.09..158.84 rows=1000 width=32) (actual time=0.043..0.997 rows=1000 loops=1)
Hash Cond: (p.npm_repository_id = r.id)
Buffers: shared hit=72
-> Seq Scan on npm_packages_p49 p (cost=0.00..133.50 rows=5000 width=48) (actual time=0.005..0.537 rows=5000 loops=1)
Filter: (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid)
Buffers: shared hit=71
-> Hash (cost=1.07..1.07 rows=1 width=32) (actual time=0.015..0.016 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on npm_repositories_p49 r (cost=0.00..1.07 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=1)
Filter: ((namespace_id = '00000000-0000-4000-8000-000000000001'::uuid) AND (repository_id = 'a0000000-0000-4000-8000-000000000001'::uuid))
Rows Removed by Filter: 4
Buffers: shared hit=1
-> Hash (cost=149.64..149.64 rows=200 width=40) (actual time=2.073..2.074 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 87kB
Buffers: shared hit=57
-> Hash Join (cost=17.00..149.64 rows=200 width=40) (actual time=0.799..1.878 rows=1000 loops=1)
Hash Cond: (b.sha256 = u.sha256)
Buffers: shared hit=57
-> Seq Scan on blob_storage_blobs_by_namespace_p49 b (cost=0.00..119.50 rows=5000 width=41) (actual time=0.011..0.530 rows=5000 loops=1)
Filter: (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid)
Buffers: shared hit=57
-> Hash (cost=14.50..14.50 rows=200 width=32) (actual time=0.770..0.770 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 72kB
-> HashAggregate (cost=12.50..14.50 rows=200 width=32) (actual time=0.508..0.597 rows=1000 loops=1)
Group Key: u.sha256
Batches: 1 Memory Usage: 209kB
-> Function Scan on unnest u (cost=0.00..10.00 rows=1000 width=32) (actual time=0.101..0.144 rows=1000 loops=1)
Planning:
Buffers: shared hit=870
Planning Time: 9.501 ms
Execution Time: 9.142 msTimings: planning 9.501 ms, execution 9.142 ms, total 18.643 ms. The planning figure is the session's first plan over these tables.
The same statement, bound to one digest instead of a thousand, so a reviewer can see the other regime on the widest of the three sums. The planner uses index_npm_files_on_ns_id_blob_sha256 and index_npm_metadata_files_on_ns_id_blob_sha256 and reads 22 buffers in 0.392 ms, against 446 buffers in 9.142 ms above:
Aggregate (cost=26.50..26.51 rows=1 width=8) (actual time=0.223..0.225 rows=1 loops=1)
Buffers: shared hit=22
-> Nested Loop Anti Join (cost=1.98..26.49 rows=1 width=8) (actual time=0.218..0.221 rows=1 loops=1)
Buffers: shared hit=22
-> Nested Loop (cost=1.28..17.61 rows=1 width=40) (actual time=0.169..0.171 rows=1 loops=1)
Buffers: shared hit=14
-> Nested Loop Anti Join (cost=1.00..9.30 rows=1 width=32) (actual time=0.105..0.107 rows=1 loops=1)
Buffers: shared hit=11
-> Unique (cost=0.02..0.03 rows=1 width=32) (actual time=0.025..0.026 rows=1 loops=1)
-> Sort (cost=0.02..0.03 rows=1 width=32) (actual time=0.024..0.025 rows=1 loops=1)
Sort Key: u.sha256
Sort Method: quicksort Memory: 25kB
-> Function Scan on unnest u (cost=0.00..0.01 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=1)
-> Nested Loop (cost=0.98..9.26 rows=1 width=33) (actual time=0.079..0.080 rows=0 loops=1)
Buffers: shared hit=11
-> Nested Loop (cost=0.85..9.09 rows=1 width=65) (actual time=0.063..0.064 rows=1 loops=1)
Buffers: shared hit=9
-> Nested Loop (cost=0.56..8.70 rows=1 width=65) (actual time=0.047..0.047 rows=1 loops=1)
Buffers: shared hit=6
-> Index Scan using npm_files_p49_namespace_id_blob_sha256_idx on npm_files_p49 f (cost=0.28..8.30 rows=1 width=65) (actual time=0.027..0.027 rows=1 loops=1)
Index Cond: ((namespace_id = '00000000-0000-4000-8000-000000000001'::uuid) AND (blob_sha256 = u.sha256))
Buffers: shared hit=3
-> Index Scan using npm_versions_p49_pkey on npm_versions_p49 v (cost=0.28..0.39 rows=1 width=48) (actual time=0.017..0.017 rows=1 loops=1)
Index Cond: ((id = f.npm_version_id) AND (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid))
Buffers: shared hit=3
-> Index Scan using npm_packages_p49_pkey on npm_packages_p49 p (cost=0.28..0.39 rows=1 width=48) (actual time=0.015..0.015 rows=1 loops=1)
Index Cond: ((id = v.npm_package_id) AND (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid))
Buffers: shared hit=3
-> Index Scan using npm_repositories_p49_pkey on npm_repositories_p49 r (cost=0.13..0.16 rows=1 width=32) (actual time=0.016..0.016 rows=0 loops=1)
Index Cond: ((id = p.npm_repository_id) AND (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid))
Filter: (repository_id = 'a0000000-0000-4000-8000-000000000001'::uuid)
Rows Removed by Filter: 1
Buffers: shared hit=2
-> Index Only Scan using blob_storage_blobs_by_namespace_p49_pkey on blob_storage_blobs_by_namespace_p49 b (cost=0.28..8.30 rows=1 width=41) (actual time=0.063..0.063 rows=1 loops=1)
Index Cond: ((namespace_id = '00000000-0000-4000-8000-000000000001'::uuid) AND (sha256 = u.sha256))
Heap Fetches: 1
Buffers: shared hit=3
-> Nested Loop (cost=0.70..8.87 rows=1 width=33) (actual time=0.048..0.048 rows=0 loops=1)
Buffers: shared hit=8
-> Nested Loop (cost=0.56..8.70 rows=1 width=65) (actual time=0.035..0.035 rows=1 loops=1)
Buffers: shared hit=6
-> Index Scan using npm_metadata_files_p49_namespace_id_blob_sha256_idx on npm_metadata_files_p49 mf (cost=0.28..8.30 rows=1 width=65) (actual time=0.020..0.020 rows=1 loops=1)
Index Cond: ((namespace_id = '00000000-0000-4000-8000-000000000001'::uuid) AND (blob_sha256 = u.sha256))
Buffers: shared hit=3
-> Index Scan using npm_packages_p49_pkey on npm_packages_p49 p_1 (cost=0.28..0.39 rows=1 width=48) (actual time=0.014..0.014 rows=1 loops=1)
Index Cond: ((id = mf.npm_package_id) AND (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid))
Buffers: shared hit=3
-> Index Scan using npm_repositories_p49_pkey on npm_repositories_p49 r_1 (cost=0.13..0.16 rows=1 width=32) (actual time=0.012..0.012 rows=0 loops=1)
Index Cond: ((id = p_1.npm_repository_id) AND (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid))
Filter: (repository_id = 'a0000000-0000-4000-8000-000000000001'::uuid)
Rows Removed by Filter: 1
Buffers: shared hit=2
Planning:
Buffers: shared hit=5
Planning Time: 1.619 ms
Execution Time: 0.392 msdatastore.sumFreedRepositoryBytes.ContainerRemote
Summary: Plan matches the method's intent and is the cheapest of the three sums, because this arm deletes one manifest row per call and so binds one digest. Both survivor arms resolve through their (namespace_id, blob_sha256) index and the size through the shadow's covering primary key, index-only, in 19 buffer hits. A control run over a digest a surviving manifest of the bound repository still carries returns 0, so the anti-join discriminates rather than always freeing. No anomalies.
Seed shape: namespaces=1, repositories=5, container_remote_repositories=5, container_remote_images=5000, container_remote_manifests=5000, container_remote_blobs=5000, blob_storage_blobs=5000, blob_storage_blobs_by_namespace=5000, blob_storage_attachments=5000. Image i sits in remote repository ((i - 1) mod 5) + 1; its manifest carries digest i and its cached layer carries digest ((i + 2500) mod 5000) + 1.
Rendered SQL:
SELECT COALESCE(SUM(b.size), 0)::bigint
FROM (SELECT DISTINCT u.sha256 FROM unnest($3::bytea[]) AS u(sha256)) AS d
JOIN blob_storage_blobs_by_namespace b
ON b.namespace_id = $1 AND b.sha256 = d.sha256
WHERE NOT EXISTS (
SELECT 1
FROM container_remote_manifests m
JOIN container_remote_images i
ON i.id = m.container_remote_image_id AND i.namespace_id = m.namespace_id
JOIN container_remote_repositories r
ON r.id = i.container_remote_repository_id AND r.namespace_id = i.namespace_id
WHERE m.namespace_id = $1 AND i.namespace_id = $1 AND r.namespace_id = $1
AND r.repository_id = $2 AND m.blob_sha256 = d.sha256
)
AND NOT EXISTS (
SELECT 1
FROM container_remote_blobs cb
JOIN container_remote_images i
ON i.id = cb.container_remote_image_id AND i.namespace_id = cb.namespace_id
JOIN container_remote_repositories r
ON r.id = i.container_remote_repository_id AND r.namespace_id = i.namespace_id
WHERE cb.namespace_id = $1 AND i.namespace_id = $1 AND r.namespace_id = $1
AND r.repository_id = $2 AND cb.blob_sha256 = d.sha256
)Bound args: [00000000-0000-4000-8000-000000000001, a0000000-0000-4000-8000-000000000001, <1 digest, sha256 = the 32-byte big-endian encoding of 3>]
Returned: 1003, the seeded size of that one blob. Rebinding to a digest a manifest of the bound repository still carries returns 0.
Plan (EXPLAIN (ANALYZE, BUFFERS) output):
Aggregate (cost=26.08..26.09 rows=1 width=8) (actual time=0.241..0.243 rows=1 loops=1)
Buffers: shared hit=19
-> Nested Loop (cost=1.70..26.07 rows=1 width=8) (actual time=0.235..0.237 rows=1 loops=1)
Buffers: shared hit=19
-> Nested Loop Anti Join (cost=1.42..17.76 rows=1 width=32) (actual time=0.187..0.189 rows=1 loops=1)
Buffers: shared hit=16
-> Nested Loop Anti Join (cost=0.72..8.89 rows=1 width=32) (actual time=0.126..0.128 rows=1 loops=1)
Buffers: shared hit=8
-> Unique (cost=0.02..0.03 rows=1 width=32) (actual time=0.045..0.046 rows=1 loops=1)
-> Sort (cost=0.02..0.03 rows=1 width=32) (actual time=0.044..0.045 rows=1 loops=1)
Sort Key: u.sha256
Sort Method: quicksort Memory: 25kB
-> Function Scan on unnest u (cost=0.00..0.01 rows=1 width=32) (actual time=0.017..0.017 rows=1 loops=1)
-> Nested Loop (cost=0.70..8.85 rows=1 width=33) (actual time=0.080..0.081 rows=0 loops=1)
Buffers: shared hit=8
-> Nested Loop (cost=0.56..8.69 rows=1 width=65) (actual time=0.061..0.061 rows=1 loops=1)
Buffers: shared hit=6
-> Index Scan using container_remote_blobs_p49_namespace_id_blob_sha256_idx on container_remote_blobs_p49 cb (cost=0.28..8.30 rows=1 width=65) (actual time=0.030..0.031 rows=1 loops=1)
Index Cond: ((namespace_id = '00000000-0000-4000-8000-000000000001'::uuid) AND (blob_sha256 = u.sha256))
Buffers: shared hit=3
-> Index Scan using container_remote_images_p49_pkey on container_remote_images_p49 i_1 (cost=0.28..0.37 rows=1 width=48) (actual time=0.028..0.028 rows=1 loops=1)
Index Cond: ((id = cb.container_remote_image_id) AND (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid))
Buffers: shared hit=3
-> Index Scan using container_remote_repositories_p49_pkey on container_remote_repositories_p49 r_1 (cost=0.13..0.16 rows=1 width=32) (actual time=0.018..0.018 rows=0 loops=1)
Index Cond: ((id = i_1.container_remote_repository_id) AND (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid))
Filter: (repository_id = 'a0000000-0000-4000-8000-000000000001'::uuid)
Rows Removed by Filter: 1
Buffers: shared hit=2
-> Nested Loop (cost=0.70..8.85 rows=1 width=33) (actual time=0.060..0.060 rows=0 loops=1)
Buffers: shared hit=8
-> Nested Loop (cost=0.56..8.69 rows=1 width=65) (actual time=0.045..0.046 rows=1 loops=1)
Buffers: shared hit=6
-> Index Scan using container_remote_manifests_p49_namespace_id_blob_sha256_idx on container_remote_manifests_p49 m (cost=0.28..8.30 rows=1 width=65) (actual time=0.029..0.029 rows=1 loops=1)
Index Cond: ((namespace_id = '00000000-0000-4000-8000-000000000001'::uuid) AND (blob_sha256 = u.sha256))
Buffers: shared hit=3
-> Index Scan using container_remote_images_p49_pkey on container_remote_images_p49 i (cost=0.28..0.37 rows=1 width=48) (actual time=0.015..0.015 rows=1 loops=1)
Index Cond: ((id = m.container_remote_image_id) AND (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid))
Buffers: shared hit=3
-> Index Scan using container_remote_repositories_p49_pkey on container_remote_repositories_p49 r (cost=0.13..0.16 rows=1 width=32) (actual time=0.014..0.014 rows=0 loops=1)
Index Cond: ((id = i.container_remote_repository_id) AND (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid))
Filter: (repository_id = 'a0000000-0000-4000-8000-000000000001'::uuid)
Rows Removed by Filter: 1
Buffers: shared hit=2
-> Index Only Scan using blob_storage_blobs_by_namespace_p49_pkey on blob_storage_blobs_by_namespace_p49 b (cost=0.28..8.30 rows=1 width=41) (actual time=0.046..0.046 rows=1 loops=1)
Index Cond: ((namespace_id = '00000000-0000-4000-8000-000000000001'::uuid) AND (sha256 = u.sha256))
Heap Fetches: 1
Buffers: shared hit=3
Planning:
Buffers: shared hit=490
Planning Time: 5.587 ms
Execution Time: 0.466 msTimings: planning 5.587 ms, execution 0.466 ms, total 6.053 ms.
datastore.sumFreedRepositoryBytes.MavenRemote
Summary: The statement answers the right number. The seed puts all 1,000 bound digests in the namespace's shadow rows and attaches 200 of them to a cache file of the bound repository, and the sum comes back at 1,200,800, which is the seeded blob sizes of exactly the 800 digests no cache file of that repository carries. It has one NOT EXISTS arm, like the hosted Maven sum, because maven_remote_files is the family's only survivor table. The plan prunes every table to one partition of 64 but, at 5,000 rows with a 1,000-digest page, hash-builds that partition of maven_remote_files rather than probing (namespace_id, blob_sha256) per digest; Where the survivor anti-join's cost goes above measures both regimes and shows the crossover, and the one-digest plan below shows the probe regime on this arm.
Seed shape: same Maven remote seed as datastore.resolveReapRepository.MavenRemoteVersion above. Cache package i sits in remote repository ((i - 1) mod 5) + 1 and cache file i carries digest i, so 200 of the first 1,000 digests survive under the bound repository.
Rendered SQL:
SELECT COALESCE(SUM(b.size), 0)::bigint
FROM (SELECT DISTINCT u.sha256 FROM unnest($3::bytea[]) AS u(sha256)) AS d
JOIN blob_storage_blobs_by_namespace b
ON b.namespace_id = $1 AND b.sha256 = d.sha256
WHERE NOT EXISTS (
SELECT 1
FROM maven_remote_files f
JOIN maven_remote_packages p
ON p.id = f.maven_remote_package_id AND p.namespace_id = f.namespace_id
JOIN maven_remote_repositories r
ON r.id = p.maven_remote_repository_id AND r.namespace_id = p.namespace_id
WHERE f.namespace_id = $1 AND p.namespace_id = $1 AND r.namespace_id = $1
AND r.repository_id = $2 AND f.blob_sha256 = d.sha256
)Bound args: [00000000-0000-4000-8000-000000000001, a0000000-0000-4000-8000-000000000001, <1000 digests, sha256 = the 32-byte big-endian encodings of 1 through 1000>]. The page size is 1,000 because MaxLifecycleReapPageSize is what MavenRemoteVersionReaper.Reap clamps its cache file page to before this statement runs.
Returned: 1200800. Rebound to the single digest 3, which a cache file of remote repository 3 carries and no cache file of the bound repository does, it returns 1003, the seeded size of that one blob. Rebound to digest 1, which a surviving cache file of the bound repository does carry, it returns 0, so the anti-join discriminates rather than always freeing.
Plan (EXPLAIN (ANALYZE, BUFFERS) output):
Aggregate (cost=539.05..539.06 rows=1 width=8) (actual time=4.807..4.815 rows=1 loops=1)
Buffers: shared hit=294
-> Hash Right Anti Join (cost=281.25..538.65 rows=160 width=8) (actual time=4.690..4.760 rows=800 loops=1)
Hash Cond: (f.blob_sha256 = u.sha256)
Buffers: shared hit=294
-> Hash Join (cost=129.11..382.36 rows=1000 width=33) (actual time=0.761..2.122 rows=1000 loops=1)
Hash Cond: (f.maven_remote_package_id = p.id)
Buffers: shared hit=237
-> Seq Scan on maven_remote_files_p49 f (cost=0.00..224.50 rows=5000 width=65) (actual time=0.010..0.788 rows=5000 loops=1)
Filter: (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid)
Buffers: shared hit=162
-> Hash (cost=116.61..116.61 rows=1000 width=32) (actual time=0.730..0.733 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 71kB
Buffers: shared hit=75
-> Nested Loop (cost=18.53..116.61 rows=1000 width=32) (actual time=0.085..0.548 rows=1000 loops=1)
Buffers: shared hit=75
-> Seq Scan on maven_remote_repositories_p49 r (cost=0.00..1.07 rows=1 width=32) (actual time=0.006..0.008 rows=1 loops=1)
Filter: ((namespace_id = '00000000-0000-4000-8000-000000000001'::uuid) AND (repository_id = 'a0000000-0000-4000-8000-000000000001'::uuid))
Rows Removed by Filter: 4
Buffers: shared hit=1
-> Bitmap Heap Scan on maven_remote_packages_p49 p (cost=18.53..105.53 rows=1000 width=48) (actual time=0.075..0.273 rows=1000 loops=1)
Recheck Cond: ((namespace_id = '00000000-0000-4000-8000-000000000001'::uuid) AND (r.id = maven_remote_repository_id))
Heap Blocks: exact=72
Buffers: shared hit=74
-> Bitmap Index Scan on maven_remote_packages_p49_namespace_id_maven_remote_reposi_idx1 (cost=0.00..18.28 rows=1000 width=0) (actual time=0.055..0.056 rows=1000 loops=1)
Index Cond: ((namespace_id = '00000000-0000-4000-8000-000000000001'::uuid) AND (maven_remote_repository_id = r.id))
Buffers: shared hit=2
-> Hash (cost=149.64..149.64 rows=200 width=40) (actual time=2.408..2.411 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 87kB
Buffers: shared hit=57
-> Hash Join (cost=17.00..149.64 rows=200 width=40) (actual time=0.927..2.177 rows=1000 loops=1)
Hash Cond: (b.sha256 = u.sha256)
Buffers: shared hit=57
-> Seq Scan on blob_storage_blobs_by_namespace_p49 b (cost=0.00..119.50 rows=5000 width=41) (actual time=0.016..0.624 rows=5000 loops=1)
Filter: (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid)
Buffers: shared hit=57
-> Hash (cost=14.50..14.50 rows=200 width=32) (actual time=0.886..0.888 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 72kB
-> HashAggregate (cost=12.50..14.50 rows=200 width=32) (actual time=0.565..0.669 rows=1000 loops=1)
Group Key: u.sha256
Batches: 1 Memory Usage: 209kB
-> Function Scan on unnest u (cost=0.00..10.00 rows=1000 width=32) (actual time=0.139..0.185 rows=1000 loops=1)
Planning:
Buffers: shared hit=501 read=1
Planning Time: 5.787 ms
Execution Time: 5.119 msTimings: planning 5.787 ms, execution 5.119 ms, total 10.906 ms. A repeat of the same transaction on this host measured the same cost and the same 294 buffers with execution at 8.148 ms, so the execution figure carries a few milliseconds of host-load spread and the buffer count does not.
The same statement, bound to one digest instead of a thousand, so a reviewer can see the other regime on this arm. The planner uses index_maven_remote_files_on_ns_id_blob_sha256 and reads 11 buffers in 0.256 ms, against 294 buffers in 5.119 ms above:
Aggregate (cost=17.22..17.23 rows=1 width=8) (actual time=0.141..0.142 rows=1 loops=1)
Buffers: shared hit=11
-> Nested Loop (cost=0.99..17.21 rows=1 width=8) (actual time=0.136..0.138 rows=1 loops=1)
Buffers: shared hit=11
-> Nested Loop Anti Join (cost=0.71..8.90 rows=1 width=32) (actual time=0.092..0.094 rows=1 loops=1)
Buffers: shared hit=8
-> HashAggregate (cost=0.02..0.03 rows=1 width=32) (actual time=0.007..0.008 rows=1 loops=1)
Group Key: u.sha256
Batches: 1 Memory Usage: 24kB
-> Function Scan on unnest u (cost=0.00..0.01 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=1)
-> Nested Loop (cost=0.70..8.87 rows=1 width=33) (actual time=0.084..0.085 rows=0 loops=1)
Buffers: shared hit=8
-> Nested Loop (cost=0.56..8.70 rows=1 width=65) (actual time=0.051..0.053 rows=1 loops=1)
Buffers: shared hit=6
-> Index Scan using maven_remote_files_p49_namespace_id_blob_sha256_idx on maven_remote_files_p49 f (cost=0.28..8.30 rows=1 width=65) (actual time=0.026..0.027 rows=1 loops=1)
Index Cond: ((namespace_id = '00000000-0000-4000-8000-000000000001'::uuid) AND (blob_sha256 = u.sha256))
Buffers: shared hit=3
-> Index Scan using maven_remote_packages_p49_pkey on maven_remote_packages_p49 p (cost=0.28..0.39 rows=1 width=48) (actual time=0.023..0.023 rows=1 loops=1)
Index Cond: ((id = f.maven_remote_package_id) AND (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid))
Buffers: shared hit=3
-> Index Scan using maven_remote_repositories_p49_pkey on maven_remote_repositories_p49 r (cost=0.13..0.16 rows=1 width=32) (actual time=0.031..0.031 rows=0 loops=1)
Index Cond: ((id = p.maven_remote_repository_id) AND (namespace_id = '00000000-0000-4000-8000-000000000001'::uuid))
Filter: (repository_id = 'a0000000-0000-4000-8000-000000000001'::uuid)
Rows Removed by Filter: 1
Buffers: shared hit=2
-> Index Only Scan using blob_storage_blobs_by_namespace_p49_pkey on blob_storage_blobs_by_namespace_p49 b (cost=0.28..8.30 rows=1 width=41) (actual time=0.043..0.043 rows=1 loops=1)
Index Cond: ((namespace_id = '00000000-0000-4000-8000-000000000001'::uuid) AND (sha256 = u.sha256))
Heap Fetches: 1
Buffers: shared hit=3
Planning:
Buffers: shared hit=2
Planning Time: 0.849 ms
Execution Time: 0.256 msBoth plans in this block are custom plans built with the bound values in hand. The transaction ran five EXECUTEs of the prepared statement, PostgreSQL keeps a prepared statement on custom plans for its first five executions, and every scan of a partitioned table in both plans carries the bound namespace_id as a literal in its Index Cond, Recheck Cond or Filter line, with the maven_remote_repositories scan carrying the bound repository_id alongside it.
How to read these numbers
Each statement was planned with PREPARE and executed once with EXECUTE, so every plan above is a custom plan built with the bound values in hand. In production the driver reaches the same statements through the extended protocol, where PostgreSQL may switch to a generic plan after the fifth execution of a prepared statement; a generic plan cannot see the array's length and would not make the hash-versus-probe choice on the same evidence.
Every seed ran inside one transaction with ANALYZE over the seeded tables, and the transaction was rolled back afterwards, so the container carried no state between statements.
Related to #681
This is a bot message
Edited by Pawel Rozlach