fix(npm): credit the freed packument-cache bytes on every delete arm (!2232) · Merge requests · GitLab.org / ops / Artifact Registry

What this delivers

An npm packument cache row holds a real blob, and its bytes are charged to repositories.size_bytes when the row is written. A whole-package delete hard-deletes those rows. Before this change, none of the three delete arms credited the bytes back to the repository. All three now carry the figure: the protocol whole-package unpublish, the management API single-package delete, and the bulk npm delete worker.

The body of this branch's first commit, 38a5dc1f9, still carries the superseded wording, "Only one of the three delete arms credited the bytes back to the repository". A squash merge on this project lands the merge request title alone with no body, measured on merged !2214 (merged) and !2192 (merged). That sentence therefore reaches no commit on main. The sentence stays visible on the Commits tab of this merge request.

The cause. NpmMetadataFileStore.DeleteNpmMetadataFiles hard-deletes a package's npm_metadata_files rows inside the caller's transaction, and it measured nothing. No later pass can supply the credit, and that is what makes the delete the only site able to give it. The rows are gone before any reap sees them. The package reaper's cache leg therefore reaches its freed-byte probe with an empty digest list, and it returns before any statement runs. The bytes were credited exactly zero times, so a credit at the delete cannot double-count.

The method now measures at the one moment the rows still exist. It reuses the probe the hosted npm package reaper already uses, rather than adding a second one. It looks up the repository itself, because neither caller holds repositories.id.

The two management API arms carry the figure through a fourth delete target, deleteCounterTargetCachedTombstone. deleteCounterTargetTombstone cannot widen to cover them. That target moves artifacts_count alone, and its early return drops a delete that hid no version and still freed bytes.

The new target is a live label migration on management_api_delete_counter_emits_total. deleteCounterTarget.label() renders it as cached_tombstone, and the merge base carries that string nowhere. The npm hosted package DELETE and the bulk npm packages worker's package entries move off target="tombstone" and appear at target="cached_tombstone". The Maven hosted package DELETE keeps target="tombstone". A dashboard or an alert selector that matches target="tombstone" therefore stops seeing these npm deletes. The shed branch of the emit writes no log line, so this metric is the whole record that an emit happened. Widening those selectors is not a repository change, and this merge request does not make it.

A design point a reviewer will ask about

The two management API arms returned before their counter emit whenever they lost the race to apply the delete mark. The same committed transaction had already hard-deleted the package's cache rows, so real freed bytes went nowhere. The race is reachable rather than theoretical. A version delete's orphan rule and a protocol single-version unpublish both tombstone the package and only force-expire its cache rows. The next package delete then finds real rows and frees real bytes.

Both arms now emit (0, -freedBytes) on that path. They skip the tracker event and the hidden-version half, which belong to the delete whose mark committed. This needed no new constant and no new field. The deleteCounterTargetCachedTombstone guard already emits when either amount alone is non-zero, and it skips when both are zero.

The freed-byte measurement runs inside the delete's transaction, so an error from it fails the delete. The alternative, crediting zero and letting the delete stand, was considered and not taken. A failed statement leaves the transaction unable to commit, so that alternative needs a savepoint, which this tree uses nowhere, and it would rescue only the errors that do not reach the server.

Test coverage

Source: the run's derived acceptance list, approved at its triage gate. Issue 939 carries no acceptance list of its own.

#	Item	Tests
1	`DeleteNpmMetadataFiles` reports what its removed cache rows freed	`TestNpmMetadataFileStore_DeleteNpmMetadataFiles_ReportsFreedBytes/the repository's last reference to each cache digest frees its size`; `TestNpmPackageManagementDeleter_DeletePackage_ReportsFreedBytes/the delete reports what its cache rows freed`
2	Two cache kinds on one digest free those bytes once	`..._ReportsFreedBytes/two cache kinds on one digest free those bytes once`
3	A digest a surviving `npm_files` or `npm_metadata_files` row of the repository carries frees nothing	three subtests of `..._ReportsFreedBytes` — negative controls, green before and after
4	A digest with no `blob_storage_blobs_by_namespace` row frees zero and raises nothing	`.../a digest the blob shadow does not carry frees zero and raises nothing` — negative control
5	A package with no cache rows frees zero	`.../a package with no cache rows frees zero` — negative control
6	The single-package management delete emits minus the freed bytes beside minus the hidden versions	`TestNpmPackageDelete_EmitsTheFreedCacheBytes`; `TestEmitDeleteCounters_TranslatesEachTargetRow`
7	The bulk npm packages worker emits that delta per applied entry	`TestBulkDeleteNpmWorker_PackagesCarryTheFreedCacheBytes`
8	The protocol whole-package unpublish emits minus the freed bytes in place of the literal 0	`TestUnpublishPackageHandler_EmitsTheSeamsDeltaPair`; `TestUnpublishPackageIntegration_EmitsTheFreedCacheBytes`
9	A delete that marks nothing emits no artifact delta, and emits the bytes its own transaction freed	`TestNpmPackageDelete_LostRaceEmitsTheFreedBytes`; `TestNpmPackageDelete_LostRaceEmitsNothing`; the rev-mismatch and not-found rows; the bulk lost-race entry
10	After the delete, `size_bytes` agrees with a recompute from source rows	`..._DeletePackage_ReportsFreedBytes/the reported bytes close the gap between size_bytes and its recompute`
M	Byte-only delete: a package whose versions were all already unpublished hides nothing and still frees real bytes	`..._DeletePackage_ReportsFreedBytes/a package whose versions are all already unpublished still reports its bytes`; `TestNpmPackageDelete_EmitsTheFreedCacheBytes/a package whose versions were all already unpublished still moves size`; `TestEmitDeleteCounters_TranslatesEachTargetRow/a cached tombstone that hid nothing still moves size by the freed bytes`; the byte-only entry of `TestBulkDeleteNpmWorker_PackagesCarryTheFreedCacheBytes/each entry carries what its own transaction did`

Rows 3, 4 and 5 are green before and after by design. They are negative controls, not defect reproductions, and they are marked so that nobody counts a green control as evidence the defect was reproduced. Row 9's rev-mismatch, not-found and bulk lost-race guards are green before and after for the same reason.

Two cells of this table were edited after the test author produced it, because the branch moved past them. Row 9's earlier wording read "A delete that marks nothing emits no size delta", against one test named TestNpmPackageDelete_LostRaceEmitsNoFreedBytes. The design point above makes that wording false, and that test is now the two tests the row names. Row M pointed at a section of an internal artifact, and it now names the four tests instead. They sit in two packages, internal/datastore and internal/managementapi.

e2e catalog and conformance

docs/testing/e2e/npm.md gains two corrected rows of the npm lifecycle table. No new scenario is added, because both rows describe journeys the catalog already carries.

e2e.npm.lifecycle.management-delete-storage-counters — the row said that size_bytes holds at a management package delete, and that a delete which lost the mark race moves nothing. Both statements are now false. The row states the credit, its per-repository deduplication, and the lost-race exception.
e2e.npm.lifecycle.repository-storage-counters — the row said that a whole-package unpublish moves nothing, because the rebuild finds the package tombstoned. The rebuild still stamps no row. The row now names the other reason the column moves, and it says that a single-version unpublish reaches the cache delete on no path.

Conformance: no suite was run by hand. CI's conformance:npm:s3-garage job covers the changed paths.

Reviewable lines

The diff passes 500 reviewable lines, so docs/dev/development-model.md asks for a split or a justification. Measured at head 413315345 against merge base d5ee57ffd, with reviewable lines counted as insertions plus deletions:

Group	Files	Insertions	Deletions	Reviewable
Production Go	13	181	275	456
Go tests	24	1074	89	1163
Markdown	8	120	47	167
Total	45	1375	411	1786

A split does not help here.

Production Go is 456 lines, under the threshold on its own. Most of it is one return value carried through three delete arms and their two handler seams. A split that lands the widened signature first ships callers that drop the value, and that is a half-credited counter on main. The deletions include 83 comment lines relocated out of internal/datastore/reconcile_repository.go, which move rather than disappear.
Go tests are 1163 of the 1786 lines, 65% of the diff. The widened return reaches 20 existing call sites and four fakes that assert nothing new.
Markdown is 167 lines. Each edit corrects a claim this same change makes false, except internal/datastore/reconcile_repository_size_bytes.md, which is the relocated prose the comment cap has no room for. Without them, the tree carries a Go comment, an accounting table and a catalog row that contradict the code.

Merge order

!2163 (merged) — merged, and the conflict it left is resolved. !2163 (merged) merged on 2026-09-02, as squash commit 9ac5c267d on origin/main. That squash and this branch disagreed on docs/dev/storage-accounting.md and on internal/managementapi/artifact_write.go, so this branch conflicted with origin/main on those two files. Before this branch, the two files auto-merged with no conflict, and that silent merge left a table and a Go doc comment that contradict each other, with nothing to report it. The conflict was the reported form of a real disagreement rather than an oversight. The rebase onto 51ba66f58 took the recorded resolution, which is the union, in three hunks:

internal/managementapi/artifact_write.go, the deleteCounterTargetTombstone doc block. It keeps the container remote targets !2163 (merged) adds and !2163 (merged)'s reworded reap clause, and it keeps the hosted npm package out of that target. The exclusion is a trailing comment on the declaration line, and it names deleteCounterTargetCachedTombstone, so a reader who arrives at either target reaches the other.
docs/dev/storage-accounting.md, the npm bullet of the per-format delete list. It keeps this branch's reap line and its whole-package cache-row sentence first, then !2163 (merged)'s two increment sentences in !2163 (merged)'s own order.
docs/dev/storage-accounting.md, the management-API delete-target table. Row one keeps !2163 (merged)'s target list minus the hosted npm package, and !2163 (merged)'s wider "What the request does" cell, which is a superset of this branch's. The hosted npm package takes a row of its own, and that row carries the byte half.

The known-gaps row — two open merge requests hold it as context, and neither re-adds it. This branch removes the #939 known-gaps row from docs/dev/storage-accounting.md. Measured at head 3c76e6226: !2210 (merged) and !2039 (merged) each carry that row as unchanged context inside a hunk they edit around. Neither adds it as a + line. An earlier revision of this section said that !2210 (merged) and !2039 (merged) re-add the row, and that mechanism is wrong. The collisions are real all the same. Both conflict with this branch on docs/dev/storage-accounting.md. !2039 (merged) at head 45d8aa00a merges cleanly into origin/main at 719845590, so its conflict belongs to this branch. !2210 (merged) at head 928816cd0 does too: its docs/dev/storage-accounting.md conflict is absent against that same origin/main, so this branch is what it collides with. !2210 (merged) separately conflicts with origin/main on internal/format/npm/metrics.md, which is a rebase it owes whatever happens here, and that file produces no conflict against this branch. !2210 (merged) sits on the branch for work item 910. !2123 (merged) held that row as context too, and it merged on 2026-09-02, so it is out of this set. Take this branch's deletion at the merge, together with the other side's own row edits.

docs/specs/S20-a-lifecycle-closed-beta.md carries a second #939 reference. Merged !2040 (merged) edited that file, and it produces no conflict against this head. Across the 88 open merge requests read on 2026-09-02, !2162 (merged) is the only one that adds a #939 line. Its two lines are written in condition-and-event form, and the event half names settleNpmReapedFiles, for which this branch adds a third caller. That text therefore reads correctly on either side of the merge, and it needs no repair here. !2162 (merged) produces no conflict against this head either.

!2123 (merged) — merged, and both documentation corrections are on this branch. !2123 (merged) merged at 2026-09-02T11:43:27.684Z, as squash commit 96d8532b5 on main. The first correction waited on that merge. It is the unpublish_package row of internal/format/npm/metrics.md, and that row alone. The row read "a repository-scoped pair whose byte half is always zero", which this branch makes false. This branch rewrites it: the row now states an artifacts_count decrement for the versions the cascade tombstoned, and a size_bytes credit for the bytes the package's hard-deleted npm_metadata_files cache rows freed. It names both readings that leave the byte half at zero: the package held no cache rows, so the delete removed none, or those rows went and the repository still holds every digest they carried. The unpublish_version row below it is untouched and stays true, because internal/format/npm/unpublish_version.go still emits a literal zero byte half. The second correction never waited on !2123 (merged). It is the sentence in docs/dev/storage-accounting.md that read "What that arm misses is the credit for the cache rows an npm delete removes inside its own transaction, and the reaper's own section carries it." This branch replaces that sentence rather than deleting it, because the paragraph needs a sentence in that slot. The replacement says the packument write's arm never reaches those cache rows, so the delete carries their credit itself, and it names the "Hosted npm reapers" section of the same file as the site that measures the figure. An earlier revision of this section named a third surface, the colRepositorySizeBytes doc block in internal/format/npm/counter_emit_dispatch.go. The merge of !2123 (merged) removed that doc block. One line stands in its place, and it sends the reader to metrics.md, so nothing is owed there. This branch does not touch that file, so the removal arrived on its own at the rebase.

!2052, !2217 (merged) and !2218 (merged) — lower weight. All three collide with this branch on one table of docs/testing/e2e/npm.md. All three also conflict with origin/main at 719845590 on five files: .gitleaks.toml, .gitlab/secret-detection-gitleaks.toml, docs/testing/e2e/docker.md, docs/testing/e2e/npm.md and docs/testing/e2e/oci.md. The same five conflict against this head 3c76e6226, so each owes a rebase whatever happens here, and the npm catalog is the only one of the five this branch touches. !2219 (merged) collides on the same npm table and owes the same rebase, and it is not lower weight, for the reason the next entry gives.

!2219 (merged) — an assertion and a limitations entry that this branch falsifies, and no tool reports it. !2219 (merged)'s stack introduces e2e/bootstrap/harness/npm_counters_test.go, whose whole-package unpublish asserts assert.Equal(t, afterVersion.SizeBytes, settled.SizeBytes, "a whole-package unpublish moves no bytes") at !2219 (merged)'s head 0f6fb014a. The same stack's docs/testing/e2e/limitations.md records that the hard-deleted packument cache rows' bytes reach the counter only at a reconciliation pass. Neither file exists on origin/main at 719845590, which carries no e2e/ directory at all. This branch can therefore correct neither one, and no CI job reports the clash. The two sides touch different files, so git merges them clean and has_conflicts reports nothing. Whichever lands second breaks the other. Which side moves is derivable rather than a judgment. This branch makes a whole-package unpublish credit the bytes its own transaction freed, so that assertion and that limitations entry are what change.

!1011 (closed) — a conflict that predates this branch. !1011 (closed) at head 018c19ab8 conflicts with this branch on 30 files, and the two this branch also edits are internal/datastore/repositories.go and internal/datastore/repositories_test.go. The same 30 files conflict against this head 3c76e6226, against the merge base 51ba66f58, and against origin/main at 719845590. The set does not change with the other side, which makes the conflict !1011 (closed)'s own rebase debt rather than something this branch created. That set also covers .gitlab-ci.yml, go.mod, internal/datastore/migrations/structure.sql and proto/artifactregistry/config/v1/config.proto, which this branch leaves alone, so most of the debt is unrelated to this work.

The branch merges cleanly into origin/main. git merge-tree --write-tree origin/main HEAD exits 0 for head 3c76e6226 against origin/main at 719845590, and it writes one tree with no conflicted stage. The rebase onto 51ba66f58 resolved the two files of the deliberate disagreement the !2163 (merged) entry above records, and every other file auto-merges. 719845590 is later than that rebase target, so the clean result is not an artifact of origin/main standing still. origin/main has moved 25 files since the merge base.

What remains

The write half of issue 939 is stale in the issue text alone: origin/main already carries it, from merged !1997 (merged). Work item #1017 owns the rewrite of that text. After this merge, one #939 reference stays in the tree, at docs/specs/S20-a-lifecycle-closed-beta.md:1086 at head 3c76e6226, which is that work item's second listed edit.

Database Review Evidence

The db-review-prep skill collected this evidence. Migration mode did not run: git diff --name-only 51ba66f58..3c76e6226 returns 42 files, and none of them sits under internal/datastore/migrations/.

Queries

Note

The plans come from EXPLAIN (ANALYZE, BUFFERS) against an ephemeral PostgreSQL 17.10 container. That version matches GL_PG_CURR_VERSION: "17" in .gitlab-ci-other-versions.yml. Each query ran on synthesized seed data inside a transaction that rolled back, and the run then removed the container. The numbers hold at moderate cardinality, and they do not carry production-scale effects. Database review evidence states the seed sizing, the method, and the anomalies the skill flags. Every details block below carries the seed shape, the rendered SQL, the bound arguments, and the raw plan.

What this run planned. No jet chain in the diff changed. Against the merge base 51ba66f58, every statement chain in the five changed dispatching files is byte-identical. The skill's new-or-changed-method pass therefore returns an empty list. What the change moves is the place where two existing statements run.

NpmMetadataFileStore.DeleteNpmMetadataFiles now calls resolveReapRepository and settleNpmReapedFiles on the caller's transaction. Two statements therefore run on a request path for the first time, inside NpmPackageManagementDeleter.DeletePackage and NpmPackageUnpublishDeleter.UnpublishPackage. Both are raw SQL constants in internal/datastore/lifecycle_reap_size.go, so neither one needs a chain-extraction harness. Both sit behind the len(refs) == 0 early return, so a delete that removed no cache row runs neither. unique_npm_metadata_files_ns_id_pkg_id_kind and the kind CHECK cap a package at three cache rows, so the second statement binds at most three digests.

Method	Plan node	Index	Rows (plan / actual)	Cost	Time	Buffers (hit / read)	Partitions
`datastore.resolveReapRepository` (`reapRepositoryRouteNpmPackage`)	Limit	`npm_packages_p58_pkey`, `npm_repositories_p58_pkey`	1 / 1	16.62	0.112 ms	6 / 0	1/64 on each of 2 tables
`datastore.sumFreedRepositoryBytes` (`reapSizeFamilyNpmHosted`, no bound digest still referenced)	Aggregate	`blob_storage_blobs_by_namespace_p22_pkey`, `npm_files_p22_namespace_id_blob_sha256_idx`, `npm_metadata_files_p22_namespace_id_blob_sha256_idx`	1 / 1	46.01	0.226 ms	21 / 0	1/64 on each of 6 tables
`datastore.sumFreedRepositoryBytes` (`reapSizeFamilyNpmHosted`, every bound digest still referenced)	Aggregate	`blob_storage_blobs_by_namespace_p22_pkey`, `npm_files_p22_namespace_id_blob_sha256_idx`, `npm_metadata_files_p22_namespace_id_blob_sha256_idx`	1 / 1	46.01	0.270 ms	38 / 0	1/64 on each of 6 tables

The Partitions cell states one scanned/modulus pair per partitioned table. Every pair here is 1/64, so the cell counts the tables instead of repeating the pair. Each details block names them.

Query notes: none. No anomaly from the skill's catalog appears. Both statements are bounded, because the first carries LIMIT 1 and the second is a single-row aggregate with no GROUP BY. Neither one falls back to a sequential scan, and every partitioned table in both plans prunes to one partition of 64.

datastore.resolveReapRepository (reapRepositoryRouteNpmPackage)

Summary: The plan matches the resolve's intent. Postgres reaches npm_packages through npm_packages_p58_pkey on (id, namespace_id), then npm_repositories through its own primary key. The bound namespace_id prunes both tables to one partition of 64. Plan rows and actual rows agree at 1 / 1, and the statement touches 6 buffers, all of them hits. No anomalies.

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

All 5000 packages sit under one of the 5000 npm_repositories rows. A one-row npm_repositories seed made the planner pick a sequential scan over that single row, which is a seed artifact rather than a property of the statement.

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 1

Bound args: $1 = c4de939a-6a01-7ea0-957c-97f72d60d425 (namespace id), $2 = 924c8123-6a72-7e0f-a100-d0d24dd4afbc (npm package id). The statement returned 0d8c0338-9860-764d-b527-93f3da30f0f9, which is the seeded repositories.id.

Partitions: npm_packages 1/64, npm_repositories 1/64.

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.56..16.62 rows=1 width=16) (actual time=0.078..0.079 rows=1 loops=1)
   Buffers: shared hit=6
   ->  Nested Loop  (cost=0.56..16.62 rows=1 width=16) (actual time=0.077..0.077 rows=1 loops=1)
         Buffers: shared hit=6
         ->  Index Scan using npm_packages_p58_pkey on npm_packages_p58 p  (cost=0.28..8.30 rows=1 width=32) (actual time=0.039..0.039 rows=1 loops=1)
               Index Cond: ((id = '924c8123-6a72-7e0f-a100-d0d24dd4afbc'::uuid) AND (namespace_id = 'c4de939a-6a01-7ea0-957c-97f72d60d425'::uuid))
               Buffers: shared hit=3
         ->  Index Scan using npm_repositories_p58_pkey on npm_repositories_p58 r  (cost=0.28..8.30 rows=1 width=48) (actual time=0.035..0.035 rows=1 loops=1)
               Index Cond: ((id = p.npm_repository_id) AND (namespace_id = 'c4de939a-6a01-7ea0-957c-97f72d60d425'::uuid))
               Buffers: shared hit=3
 Planning:
   Buffers: shared hit=58
 Planning Time: 1.119 ms
 Execution Time: 0.112 ms

Timings: planning 1.119 ms, execution 0.112 ms, total 1.231 ms.

datastore.sumFreedRepositoryBytes (reapSizeFamilyNpmHosted) — no bound digest still referenced

Summary: The plan matches the probe's intent. The three-element unnest drives two nested-loop anti joins. Each anti join enters through the (namespace_id, blob_sha256) index of npm_files or npm_metadata_files, and neither probe finds a row. The surviving digests reach blob_storage_blobs_by_namespace through an index-only scan on its primary key. All six partitioned tables prune to one partition of 64, and the statement returned 12288, which is the seeded size of the three freed digests. No anomalies.

Seed shape: namespaces=1, repositories=5000, npm_repositories=5000, npm_packages=5000, npm_versions=5000, npm_files=5000, npm_metadata_files=5000, blob_storage_blobs=10003, blob_storage_attachments=10000, blob_storage_blobs_by_namespace=10003

blob_storage_blobs_by_namespace carries no explicit insert. The trg_blob_storage_blobs_shadow_insert trigger on blob_storage_blobs writes every one of its rows. All 5000 packages sit under one npm_repositories row, which is the shape a single repository has. The three bound digests hold a blob_storage_blobs row each and no attachment row, which is the state a completed cache delete leaves.

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: $1 = f2cf1bda-4674-7bcb-9b79-e81c4edda101 (namespace id), $2 = b6a9f585-7003-74c3-95f9-4b66f37f7b45 (repositories id), $3 = {\x00...0dbba1, \x00...0dbba2, \x00...0dbba3} (three 32-byte digests, each one the big-endian encoding of 900001, 900002 and 900003). The statement returned 12288, which is 3 x 4096.

Partitions: npm_files 1/64, npm_versions 1/64, npm_packages 1/64, npm_repositories 1/64, npm_metadata_files 1/64, blob_storage_blobs_by_namespace 1/64.

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Aggregate  (cost=46.00..46.01 rows=1 width=8) (actual time=0.096..0.097 rows=1 loops=1)
   Buffers: shared hit=21
   ->  Nested Loop Anti Join  (cost=2.30..46.00 rows=1 width=8) (actual time=0.084..0.093 rows=3 loops=1)
         Buffers: shared hit=21
         ->  Nested Loop  (cost=1.46..36.89 rows=1 width=40) (actual time=0.072..0.079 rows=3 loops=1)
               Buffers: shared hit=15
               ->  Nested Loop Anti Join  (cost=1.17..28.58 rows=1 width=32) (actual time=0.032..0.037 rows=3 loops=1)
                     Buffers: shared hit=6
                     ->  HashAggregate  (cost=0.04..0.07 rows=3 width=32) (actual time=0.008..0.009 rows=3 loops=1)
                           Group Key: u.sha256
                           Batches: 1  Memory Usage: 24kB
                           ->  Function Scan on unnest u  (cost=0.00..0.03 rows=3 width=32) (actual time=0.005..0.005 rows=3 loops=1)
                     ->  Nested Loop  (cost=1.13..9.49 rows=1 width=33) (actual time=0.009..0.009 rows=0 loops=3)
                           Buffers: shared hit=6
                           ->  Nested Loop  (cost=0.85..9.11 rows=1 width=65) (actual time=0.008..0.008 rows=0 loops=3)
                                 Buffers: shared hit=6
                                 ->  Nested Loop  (cost=0.56..8.70 rows=1 width=65) (actual time=0.008..0.008 rows=0 loops=3)
                                       Buffers: shared hit=6
                                       ->  Index Scan using npm_files_p22_namespace_id_blob_sha256_idx on npm_files_p22 f  (cost=0.28..8.30 rows=1 width=65) (actual time=0.008..0.008 rows=0 loops=3)
                                             Index Cond: ((namespace_id = 'f2cf1bda-4674-7bcb-9b79-e81c4edda101'::uuid) AND (blob_sha256 = u.sha256))
                                             Buffers: shared hit=6
                                       ->  Index Scan using npm_versions_p22_pkey on npm_versions_p22 v  (cost=0.28..0.39 rows=1 width=48) (never executed)
                                             Index Cond: ((id = f.npm_version_id) AND (namespace_id = 'f2cf1bda-4674-7bcb-9b79-e81c4edda101'::uuid))
                                 ->  Index Scan using npm_packages_p22_pkey on npm_packages_p22 p  (cost=0.28..0.40 rows=1 width=48) (never executed)
                                       Index Cond: ((id = v.npm_package_id) AND (namespace_id = 'f2cf1bda-4674-7bcb-9b79-e81c4edda101'::uuid))
                           ->  Index Scan using npm_repositories_p22_pkey on npm_repositories_p22 r  (cost=0.28..0.37 rows=1 width=32) (never executed)
                                 Index Cond: ((id = p.npm_repository_id) AND (namespace_id = 'f2cf1bda-4674-7bcb-9b79-e81c4edda101'::uuid))
                                 Filter: (repository_id = 'b6a9f585-7003-74c3-95f9-4b66f37f7b45'::uuid)
               ->  Index Only Scan using blob_storage_blobs_by_namespace_p22_pkey on blob_storage_blobs_by_namespace_p22 b  (cost=0.29..8.30 rows=1 width=41) (actual time=0.014..0.014 rows=1 loops=3)
                     Index Cond: ((namespace_id = 'f2cf1bda-4674-7bcb-9b79-e81c4edda101'::uuid) AND (sha256 = u.sha256))
                     Heap Fetches: 3
                     Buffers: shared hit=9
         ->  Nested Loop  (cost=0.85..9.09 rows=1 width=33) (actual time=0.004..0.004 rows=0 loops=3)
               Buffers: shared hit=6
               ->  Nested Loop  (cost=0.56..8.71 rows=1 width=65) (actual time=0.004..0.004 rows=0 loops=3)
                     Buffers: shared hit=6
                     ->  Index Scan using npm_metadata_files_p22_namespace_id_blob_sha256_idx on npm_metadata_files_p22 mf  (cost=0.28..8.30 rows=1 width=65) (actual time=0.004..0.004 rows=0 loops=3)
                           Index Cond: ((namespace_id = 'f2cf1bda-4674-7bcb-9b79-e81c4edda101'::uuid) AND (blob_sha256 = u.sha256))
                           Buffers: shared hit=6
                     ->  Index Scan using npm_packages_p22_pkey on npm_packages_p22 p_1  (cost=0.28..0.40 rows=1 width=48) (never executed)
                           Index Cond: ((id = mf.npm_package_id) AND (namespace_id = 'f2cf1bda-4674-7bcb-9b79-e81c4edda101'::uuid))
               ->  Index Scan using npm_repositories_p22_pkey on npm_repositories_p22 r_1  (cost=0.28..0.37 rows=1 width=32) (never executed)
                     Index Cond: ((id = p_1.npm_repository_id) AND (namespace_id = 'f2cf1bda-4674-7bcb-9b79-e81c4edda101'::uuid))
                     Filter: (repository_id = 'b6a9f585-7003-74c3-95f9-4b66f37f7b45'::uuid)
 Planning:
   Buffers: shared hit=111 read=1
 Planning Time: 1.898 ms
 Execution Time: 0.226 ms

Timings: planning 1.898 ms, execution 0.226 ms, total 2.124 ms.

datastore.sumFreedRepositoryBytes (reapSizeFamilyNpmHosted) — every bound digest still referenced

Summary: This variant binds three digests that a surviving row of the same repository still holds, so both anti joins reject all three and the aggregate returned 0. The plan shape is identical to the variant above. Buffer hits rise from 21 to 38, because each anti-join probe now walks its whole join chain up to npm_repositories instead of stopping at the first index lookup. No anomalies.

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: $1 = f2cf1bda-4674-7bcb-9b79-e81c4edda101 (namespace id), $2 = b6a9f585-7003-74c3-95f9-4b66f37f7b45 (repositories id), $3 = {\x00...000001, \x00...000002, \x00...0186a1} (two digests a surviving npm_files row holds, and one a surviving npm_metadata_files row holds). The statement returned 0.

Partitions: npm_files 1/64, npm_versions 1/64, npm_packages 1/64, npm_repositories 1/64, npm_metadata_files 1/64, blob_storage_blobs_by_namespace 1/64.

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Aggregate  (cost=46.00..46.01 rows=1 width=8) (actual time=0.140..0.142 rows=1 loops=1)
   Buffers: shared hit=38
   ->  Nested Loop Anti Join  (cost=2.30..46.00 rows=1 width=8) (actual time=0.138..0.140 rows=0 loops=1)
         Buffers: shared hit=38
         ->  Nested Loop  (cost=1.46..36.89 rows=1 width=40) (actual time=0.040..0.099 rows=1 loops=1)
               Buffers: shared hit=29
               ->  Nested Loop Anti Join  (cost=1.17..28.58 rows=1 width=32) (actual time=0.021..0.080 rows=1 loops=1)
                     Buffers: shared hit=26
                     ->  HashAggregate  (cost=0.04..0.07 rows=3 width=32) (actual time=0.006..0.007 rows=3 loops=1)
                           Group Key: u.sha256
                           Batches: 1  Memory Usage: 24kB
                           ->  Function Scan on unnest u  (cost=0.00..0.03 rows=3 width=32) (actual time=0.004..0.004 rows=3 loops=1)
                     ->  Nested Loop  (cost=1.13..9.49 rows=1 width=33) (actual time=0.024..0.024 rows=1 loops=3)
                           Buffers: shared hit=26
                           ->  Nested Loop  (cost=0.85..9.11 rows=1 width=65) (actual time=0.019..0.019 rows=1 loops=3)
                                 Buffers: shared hit=20
                                 ->  Nested Loop  (cost=0.56..8.70 rows=1 width=65) (actual time=0.013..0.013 rows=1 loops=3)
                                       Buffers: shared hit=14
                                       ->  Index Scan using npm_files_p22_namespace_id_blob_sha256_idx on npm_files_p22 f  (cost=0.28..8.30 rows=1 width=65) (actual time=0.007..0.007 rows=1 loops=3)
                                             Index Cond: ((namespace_id = 'f2cf1bda-4674-7bcb-9b79-e81c4edda101'::uuid) AND (blob_sha256 = u.sha256))
                                             Buffers: shared hit=8
                                       ->  Index Scan using npm_versions_p22_pkey on npm_versions_p22 v  (cost=0.28..0.39 rows=1 width=48) (actual time=0.008..0.008 rows=1 loops=2)
                                             Index Cond: ((id = f.npm_version_id) AND (namespace_id = 'f2cf1bda-4674-7bcb-9b79-e81c4edda101'::uuid))
                                             Buffers: shared hit=6
                                 ->  Index Scan using npm_packages_p22_pkey on npm_packages_p22 p  (cost=0.28..0.40 rows=1 width=48) (actual time=0.008..0.008 rows=1 loops=2)
                                       Index Cond: ((id = v.npm_package_id) AND (namespace_id = 'f2cf1bda-4674-7bcb-9b79-e81c4edda101'::uuid))
                                       Buffers: shared hit=6
                           ->  Index Scan using npm_repositories_p22_pkey on npm_repositories_p22 r  (cost=0.28..0.37 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=2)
                                 Index Cond: ((id = p.npm_repository_id) AND (namespace_id = 'f2cf1bda-4674-7bcb-9b79-e81c4edda101'::uuid))
                                 Filter: (repository_id = 'b6a9f585-7003-74c3-95f9-4b66f37f7b45'::uuid)
                                 Buffers: shared hit=6
               ->  Index Only Scan using blob_storage_blobs_by_namespace_p22_pkey on blob_storage_blobs_by_namespace_p22 b  (cost=0.29..8.30 rows=1 width=41) (actual time=0.019..0.019 rows=1 loops=1)
                     Index Cond: ((namespace_id = 'f2cf1bda-4674-7bcb-9b79-e81c4edda101'::uuid) AND (sha256 = u.sha256))
                     Heap Fetches: 1
                     Buffers: shared hit=3
         ->  Nested Loop  (cost=0.85..9.09 rows=1 width=33) (actual time=0.040..0.040 rows=1 loops=1)
               Buffers: shared hit=9
               ->  Nested Loop  (cost=0.56..8.71 rows=1 width=65) (actual time=0.024..0.024 rows=1 loops=1)
                     Buffers: shared hit=6
                     ->  Index Scan using npm_metadata_files_p22_namespace_id_blob_sha256_idx on npm_metadata_files_p22 mf  (cost=0.28..8.30 rows=1 width=65) (actual time=0.011..0.011 rows=1 loops=1)
                           Index Cond: ((namespace_id = 'f2cf1bda-4674-7bcb-9b79-e81c4edda101'::uuid) AND (blob_sha256 = u.sha256))
                           Buffers: shared hit=3
                     ->  Index Scan using npm_packages_p22_pkey on npm_packages_p22 p_1  (cost=0.28..0.40 rows=1 width=48) (actual time=0.013..0.013 rows=1 loops=1)
                           Index Cond: ((id = mf.npm_package_id) AND (namespace_id = 'f2cf1bda-4674-7bcb-9b79-e81c4edda101'::uuid))
                           Buffers: shared hit=3
               ->  Index Scan using npm_repositories_p22_pkey on npm_repositories_p22 r_1  (cost=0.28..0.37 rows=1 width=32) (actual time=0.015..0.015 rows=1 loops=1)
                     Index Cond: ((id = p_1.npm_repository_id) AND (namespace_id = 'f2cf1bda-4674-7bcb-9b79-e81c4edda101'::uuid))
                     Filter: (repository_id = 'b6a9f585-7003-74c3-95f9-4b66f37f7b45'::uuid)
                     Buffers: shared hit=3
 Planning:
   Buffers: shared hit=5
 Planning Time: 1.882 ms
 Execution Time: 0.270 ms

Timings: planning 1.882 ms, execution 0.270 ms, total 2.152 ms.

Related to #939

This is a bot message 🤖 — /smurfit

Edited Sep 03, 2026 by Pawel Rozlach

fix(npm): credit the freed packument-cache bytes on every delete arm

What this delivers

A design point a reviewer will ask about

Test coverage

e2e catalog and conformance

Reviewable lines

Merge order

What remains

Database Review Evidence

Queries

Merge request reports