chore(datastore): recompute repository size_bytes from source (S22 plan: 12/21) (!1712) · Merge requests · GitLab.org / ops / Artifact Registry

What this step delivers

RepositoryReconcileStore.RecomputeSizeBytes computes one repository's size_bytes from the source tables. It is ADR-007's blob walk for a single repository. Step 11 introduced the (format, kind) dispatch, and this walk lands as a branch of it.

What the branch adds:

RecomputeSizeBytes(ctx, namespaceID, repositoryID, format, kind), with the guard ladder and the error vocabulary of RecomputeArtifactsCount.
Six statement builders, one per non-virtual (format, kind) family, and a shared tail reconcileBlobSizeStmt. The tail carries the join to blob_storage_blobs_by_namespace and the aggregate.
One widened dispatch map. A single entry now holds both walks, so the two key sets cannot drift apart, and one totality test covers both.
WriteBackCounters and writeBackRepositoryCountersStmt now write size_bytes beside artifacts_count. downloads_count stays out. Accepted code smell: two adjacent int64 counters on the write-back signature. WriteBackCounters(ctx, namespaceID, repositoryID uuid.UUID, artifactsCount, sizeBytes int64) keeps the two primitives rather than a struct or two named int64 types, and the named-types alternative is declined because it would not close the transposition either — both recomputes return a bare (int64, error), so ArtifactsCount(sizeValue) still compiles, and closing it would mean changing merged step-11 return types — while the shape itself matches merged accounting.Emitter.EmitRepoCounters, whose identical (namespaceID, repositoryID uuid.UUID, deltaArtifacts, deltaSize int64) the plan already names and accepts under Accepted code smell: primitives across the emit boundary.
RepositoryReconcileTarget.SizeBytes, projected by the namespace-scoped enumeration.
Six database_query_duration_seconds{name} values in query_names.go, one per statement. The merged total is 329 names against the budget of 350 in internal/metrics/cardinality.go, with 21 of headroom. The basis belongs with the number, because origin/main moves under the branch: measured with origin/main at 848a2da3c, which declares 311 names in internal/datastore/query_names.go, plus this branch's 6, plus the 12 in internal/storage/queries.go. The branch's six are disjoint from every name main has added since the diff base, so the catalog tests stay green. An earlier count of 325 read the branch alone, and one of 327 was measured with origin/main at a16f895d, two names ago.

The digest source per (format, kind), each one checked against internal/datastore/migrations/structure.sql:

`(format, kind)`	Digest sources	Reached through
`(docker, hosted)`, `(oci, hosted)`	`container_blobs` ∪ `container_manifests`	`container_images` → `container_repositories`
`(docker, remote)`, `(oci, remote)`	`container_remote_blobs` ∪ `container_remote_manifests`	`container_remote_images` → `container_remote_repositories`
`(maven, hosted)`	`maven_files` (DISTINCT)	`maven_packages` → `maven_repositories`
`(maven, remote)`	`maven_remote_files` (DISTINCT)	`maven_remote_packages` → `maven_remote_repositories`
`(npm, hosted)`	`npm_files` ∪ `npm_metadata_files`	files through `npm_versions` → `npm_packages`, metadata through `npm_packages`
`(npm, remote)`	`npm_remote_files` ∪ `npm_remote_metadata_files`	as hosted, over the `_remote_` chain
all four `virtual`	none — a decided zero, with no round trip	a virtual repository owns no rows

A break in one of the next three properties gives a wrong answer and no error, so they are the first ones to read:

No soft_deleted_at predicate anywhere in the six walks. This is the inverse of the count walks beside them, and it is the clause of acceptance criterion 9 that this step owns. The doc comment on RecomputeSizeBytes enumerates every row shape the absence admits.
maven_files is reached through maven_package_id, never through maven_version_id. That column is nullable, and it is NULL on a package-level file such as maven-metadata.xml. A version-routed join drops every one of those files and reports no error.
The aggregate carries an explicit alias. PostgreSQL labels a COALESCE over a SUM as ?column?, where it labels a bare COUNT(*) as count. A destination field that matches no projected column keeps its zero value, so an unaliased sum returns zero over correct SQL. Three constants carry the names, and only one of them has two sites to hold in agreement. reconcileBlobDigestAlias is written with AS by every collecting arm and read back by reconcileBlobSizeStmt, so a disagreement between those two yields SQL PostgreSQL rejects. reconcileBlobDigestTable is written once, at AsTable, and go-jet qualifies the column reference from the SelectTable value rather than from a second spelling. reconcileBlobSizeAlias's partner is the Go field name Total on the destination struct, not a second use of the constant — so changing the constant's value alone would silently reintroduce the zero-return this property is about.

What this changes in production today: nothing yet, by design

RecomputeSizeBytes has no call site on main. NewRepositoryReconcileStore has no construction site outside the test suites, and step 11's dispatch is itself package-private. No running service reaches the new SQL, and the six new metric series stay unobserved. The first production caller arrives with the reconciliation task, which is step 14.

This is also the reason every runtime obligation was skipped. The section ## Checks that did not run names all four with their triggers.

Spec coverage

Spec: docs/specs/S22-storage-accounting.md

Acceptance criteria

#	Criterion	Tests
AC-1	Repo-scoped increment reaches `artifacts_count`/`size_bytes` after a drain tick	Owned by the drain steps (batched guarded `UPDATE`, chunk workers). Not this step.
AC-2	Namespace-scoped increment reaches `namespace_statistics`	Owned by the drain steps. Not this step.
AC-3	Concurrent increments to one scope sum exactly	Owned by the Redis primitive and drain steps. Not this step.
AC-4	`SPOP`-claimed scope re-incremented before its merge is not lost	Owned by the chunk-worker step. Not this step.
AC-5	Stale-dequeue chunk bails, re-adds, meters, issues no `UPDATE`	Owned by the chunk-worker step. Not this step.
AC-6	Chunk failing every attempt re-adds its scopes	Owned by the chunk-worker step. Not this step.
AC-7	Redis unavailable at increment time does not fail the operation	Owned by the emit-API step. Not this step.
AC-8	Reconciliation clears buffered state before the scan	Owned by the reconciliation-task step. Not this step.
AC-9	Per-counter soft-delete visibility, per format — `size_bytes` includes tombstoned rows	`TestRepositoryReconcileStore_ReconciledCounters_SoftDeleteVisibility` (six branches: count is live-only, size is all-rows, size exceeds the live-only total, and neither moves on a second pass); `TestRepositoryReconcileStore_RecomputeSizeBytes_FormatKindMatrix`. The `artifacts_count`-excludes clause is the count step's, `components_count` the namespace step's, and the pass-level idempotence the reconciliation-task step's.
AC-10	Positive hit per version-type table and `(format, kind)` for `artifacts_count`/`components_count`	Owned by the count step (repository half) and the namespace step. This step re-asserts the repository half over the same fixtures in `TestRepositoryReconcileStore_ReconciledCounters_SoftDeleteVisibility`.
AC-11	Drift histogram observed before the override	Owned by the reconciliation-task step. Not this step.
AC-12	Crash between `HINCRBY` and `SADD` still captured by reconciliation	Owned by the emit and chunk fault suites. Not this step.
AC-13	Hash TTL refreshed on every write	Owned by the Redis primitive step. Not this step.
AC-14	Migrations apply cleanly and roundtrip	Owned by the table and trigger steps. Not this step.
AC-15	Shadow triggers keep `blob_storage_blobs_by_namespace` exactly consistent	Owned by the trigger step. This step asserts the read-side consequence: `TestRepositoryReconcileStore_RecomputeSizeBytes_ShadowMatchesBaseTable`, plus a per-cell base-table comparison inside `TestRepositoryReconcileStore_RecomputeSizeBytes_FormatKindMatrix`.
AC-16	Every namespace has a zero-valued statistics row by construction	Owned by the `namespace_statistics` step. Not this step.
AC-17	npm publish and unpublish emit on the pipeline	Owned by the npm call-site step. Not this step.
AC-18	OCI emits increments at the actual sites	Owned by the OCI call-site step. Not this step.
AC-19	OCI emits decrements at the delete handler	Owned by the OCI call-site step. Not this step.
AC-20	Maven upload emits post-commit	Owned by the Maven call-site step. Not this step.
AC-21	Repository cascade hard-delete	Integration contract verified in the cascade and purger work, not in S22.
AC-22	One asynq task per namespace candidate	Owned by the reconciliation-task step. Not this step.
AC-23	Namespace with no statistics row gets one on its first pass	Owned by the namespace-recompute step. Not this step.
AC-24	In-flight reconciliation tasks never exceed the cap	Owned by the reconciliation-task step. Not this step.
AC-25	Source-first ordering at every emit site	Owned by the three call-site steps. Not this step.
AC-26	`last_reconciled_at` stamped only after every repository is written back	Namespace half owned by the reconciliation-task step. This step's write-back stamps its own row alone, asserted in `TestRepositoryReconcileStore_WriteBackCounters`.
AC-27	Trigger fire selects only stale namespaces	Owned by the trigger and candidate-selection step. Not this step.
AC-28	A namespace is enqueued at most once at a time	Owned by the trigger step. Not this step.
AC-29	`reconciliation_backlog` from a single writer	Owned by the collector step. Not this step.
AC-30	Drain chunk skips a scope reconciled since its baseline read	Owned by the chunk-worker fault suite. Not this step.
AC-31	`counter_dirty_set_size` sampled once per tick before `SPOP`	Owned by the drain-trigger step. Not this step.
AC-32	Config load rejects each invalid configuration	Owned by the configuration steps. Not this step.
AC-33	The six S22-defined metrics carry their exact names, types and label sets	Owned by the four steps that introduce them. Not this step; the six `database_query_duration_seconds{name}` values this step declares are covered by the pre-existing `TestQueryNames_WellFormedAndUnique`, `TestQueryNames_IdentifierMatchesValue`, `TestQueryNames_EachUsedExactlyOnce` and `TestNameBudget_CoversEveryDeclaredQueryName`.
AC-34	Reconciliation saturation returns for re-enqueue	Owned by the reconciliation-task step. Not this step.
AC-35	Namespace-scoped chunk with no statistics row drops and deletes `:flushed`	Owned by the chunk-worker step. Not this step.
AC-36	A failing recovery `SADD` loses no delta	Owned by the chunk-worker fault suite. Not this step.
AC-37	Management-API deletes emit their deltas	Integration contract verified in the management-API delete work, not in S22.

Error cases

#	Condition	Tests
E-1	Redis unavailable at increment time	Owned by the emit-API step. Not this step.
E-2	Redis unavailable at drain-trigger time	Owned by the drain-trigger step. Not this step.
E-3	Chunk job's Postgres `UPDATE` fails	Owned by the chunk-worker step. Not this step.
E-4	Chunk job's `:flushed` `DEL` fails after the `UPDATE` succeeded	Owned by the chunk-worker step. Not this step.
E-5	Chunk job exhausts all retry attempts	Owned by the chunk-worker step. Not this step.
E-6	Recovery `SADD` itself fails	Owned by the chunk-worker fault suite. Not this step.
E-7	Chunk dequeued later than `drain_chunk_stale_timeout`	Owned by the chunk-worker step. Not this step.
E-8	Worker dies mid-chunk after merging into `:flushed`	Owned by the chunk-worker step. Not this step.
E-9	Two chunks run the same scope concurrently	Owned by the chunk-worker step. Not this step.
E-10	Trigger's `EnqueueTx` fails while the process is alive	Owned by the drain-trigger step. Not this step.
E-11	Crash between the trigger's `SPOP` and its commit	Owned by the drain-trigger step. Not this step.
E-12	Assigned repository or namespace row hard-deleted before its chunk drains	Drain half owned by the chunk-worker step. The write-back's matching no-op is asserted by the "is a no-op for a repository row that is gone" subtest of `TestRepositoryReconcileStore_WriteBackCounters`, which now carries both counters.
E-13	Namespace-scoped chunk drains a namespace with no statistics row	Owned by the chunk-worker step. Not this step.
E-14	Crash between a scope's `HINCRBY` and its `SADD`	Owned by the emit and chunk fault suites. Not this step.
E-15	Crash between reconciliation's clear and its `SET`	Owned by the reconciliation-task step. Not this step.
E-16	Reconciliation scan races a concurrent increment	Owned by the reconciliation-task step. Not this step.
E-17	A drain chunk and a reconciliation process one scope concurrently	Owned by the chunk-worker fault suite. Not this step.
E-18	Reconciliation finds a discrepancy	Owned by the reconciliation-task step. Not this step.
E-19	Namespace has no statistics row when its task runs	Owned by the namespace-recompute step. Not this step.
E-20	Reconciliation task fails before its final UPSERT	Owned by the reconciliation-task step. Not this step.
E-21	A namespace can never be reconciled	Owned by the reconciliation-task and trigger steps. Not this step.

Security considerations

#	Concern	Tests
S-1	Redis keys carry internal UUIDs only, so no key-injection risk exists	Owned by the Redis key-grammar step. Not this step, which writes no Redis key.
S-2	Counter values are non-secret, but the billing inputs make an inflating or deflating bug a financial one	`TestRepositoryReconcileStore_RecomputeSizeBytes_FormatKindMatrix` (each cell moves if a union arm is dropped, a digest is counted twice, a tombstone is excluded or a sibling repository folds in, and each reruns to show the value settles); `TestRepositoryReconcileStore_RecomputeSizeBytes_SharedBlobAcrossRepositories` (a shared blob is billed once at the namespace and appears in full in each repository, so the two levels are not summable); `TestRepositoryReconcileStore_RecomputeSizeBytes_ScopedToTheRequestedNamespace`; `TestRepositoryReconcileStore_RecomputeSizeBytes_ShadowMatchesBaseTable`
S-3	Redis and Postgres connectivity reuse existing clients; no new credential surface	No test, and none is possible: this step adds no client, no credential and no configuration, and reuses the pool the store already holds.

e2e scenario catalogs

No scenario is added, and none is affected. docs/testing/ is untouched. The step is a chore that adds no route, no request path and no response change, so no client-visible flow moves. The plan records no e2e impact for steps 1 through 16, and it puts the catalog revisits on steps 17, 18 and 19.

Diff size

The diff is 5 files and 2407 insertions, all under internal/datastore/. That passes the 500-reviewable-line ceiling, so docs/dev/development-model.md asks for a split or a justification. The split by file group, measured at fcd799c74 against base 5f1e4cfc:

Group	File	Insertions	Reviewable
Integration test	`reconcile_repository_integration_test.go`	1453	852
Implementation	`reconcile_repository.go`	759	337
EXPLAIN test	`reconcile_repository_explain_integration_test.go`	97	19
Unit test	`reconcile_repository_test.go`	92	43
Query names	`query_names.go`	6	6
Total		2407	1257

Reviewable counts the inserted lines that are neither blank nor comment. 931 of the 2407 inserted lines are comments, which is 39%, and 219 are blank. That leaves 1257 reviewable lines: 343 production (337 in reconcile_repository.go and the six query names) and 914 test. A reviewer who sizes the change from the insertion count alone over-reads it by well over a third.

The justification: the size is inherent, and a split makes the step worse rather than smaller. The production half is 343 reviewable lines, inside the ceiling on its own. Two floors set the rest, and neither one is a choice:

Six distinct SQL shapes, written as explicit go-jet join chains. Each (format, kind) family reaches its own tables through its own stub table, so no two builders share a chain.
A twelve-cell (format, kind) matrix whose tables differ per cell. It needs nine fixtures, ten leaf-row seeders (one per table that carries blob_sha256 and is reached by a walk) and twelve table-driven cases. Collapsing a dimension deletes coverage that acceptance criterion 9 asks for cell by cell.

Splitting the test suite from the implementation lands a step whose tests do not compile. Splitting the implementation has no seam to cut: this step is one method, one dispatch widening and one write-back column. The plan already interrogated the same boundary for step 11. It recorded that step 11's one extractable seam ships either a walk with nothing to recompute, or a recompute that cannot reach a repository.

Dependencies: both merged, nothing left to sequence

The plan gives step 12 two dependencies, and both are on main already. This is stated as a satisfied requirement rather than as a merge order, and each half is checkable:

Dependency	MR	State
Step 2b — `blob_storage_blobs_by_namespace` triggers and seed	!1652 (merged)	Merged 2026-08-19, squash commit `1eca8282`
Step 11 — repository-level `artifacts_count` recompute	!1604 (merged)	Merged 2026-08-18

The size walk joins the shadow table, which is what puts step 2b in this step's Depends on: line. The table, its two triggers and its seed all come from step 2b, and this step only reads them. This step adds no migration.

This branch was rebased onto origin/main at 5f1e4cfc after !1652 (merged) merged, so it is not stacked and it targets main directly. Every statement here is valid against main alone. Both the spec and ADR-007 name the shadow table for the repository-level sum. ADR-007's example queries always named it, and !1358 (merged) brought the spec's Reconciliation table and its scoping sentence into line. The plan's conditional fallback wording therefore does not apply.

This step widens no predicate ahead of the code that serves it.

It does defer findings, rather than none: the two database-review sections whose headings end in "(concern)" — Flag 1 and Flag 2 — each name an option set and leave the choice to whoever owns the reconcile worker, and neither records a decision here.

One consequence of the interim state is worth stating where the reconciliation task's author will read it, because it sets a floor under a paging signal. RepositoryStore.DecrementRepositorySizeBytes takes a size_bytes decrement at the npm whole-package unpublish tombstone, and this walk deliberately keeps a tombstoned row's bytes. So once the reconciliation task lands and while that interim decrement still stands, every npm whole-package unpublish has its decrement undone by the next reconciliation pass, and reconciliation_drift_bytes records that as real drift, one-directionally, until the purger swap retires the tombstone-time site. That is the designed interim state and not a fault, but an alert tuned without it meets a non-zero floor with no documented cause, and raising the threshold is what would then hide the fast-path defect the histogram exists to catch.

Plan versus reality: the LOC estimate

The plan's dependency-and-estimate table gives step 12 about 350 LOC. The reviewable figure is 1211. This contradiction is stated here rather than corrected in the plan. A reader who cross-references the two documents can then tell a deliberate correction from a transcription error. This MR does not amend the plan.

The production half explains the shape of the miss. 342 reviewable production lines against an estimate of 350 is a near-exact prediction. The whole difference is the test matrix that criteria 9 and 10 ask for cell by cell. The plan already diagnoses the same overshoot for step 11, in the same words. The estimate was wrong about the test surface rather than the production one.

Plan Status row 12

Row 12 of the plan's Status table is empty, and this MR leaves it empty. This is a deliberate deviation from the guardrail that asks an opening MR to fill its own row. The reason: one separate MR fills every row in batch, once all child MR numbers exist, so no child fills its own. !1559 (merged) argues the same position at project level.

Query-plan pin

One pin was added, TestRecomputeContainerBlobsSizeStmt_PrunesEveryTableToOnePartition. It EXPLAINs recomputeContainerBlobsSizeStmt and asserts that each of the five tables on that chain prunes to exactly one partition, blob_storage_blobs_by_namespace included. The statement is planned rather than executed. The test refreshes statistics with ANALYZE on each of the five tables first, and the EXPLAIN itself carries no ANALYZE.

The pin is prune-only, and that is measured rather than assumed. ADR-007's index-only claim for the shadow table is not covered by it. A plain EXPLAIN over a fixture this size plans a Seq Scan on every one of the five pruned partitions. An index-only assertion therefore fails against correct code.

The five other size statements carry no pin of their own, and the file's header states that gap. The shared reconcileBlobSizeStmt tail is what this one case covers for all six, because every branch ends at the same shadow join.

Lint

Both figures are diff-scoped against base 5f1e4cfc, and both were taken at fcd799c74 under a private lint cache:

Pass	Result
`golangci-lint run --new-from-rev=5f1e4cfc --max-same-issues=0 --max-issues-per-linter=0 ./internal/datastore/`	0 issues
the same command with `--build-tags=integration`	0 issues

The tagged pass is the one CI cannot run. .golangci.yaml sets no run.build-tags, so golangci-lint compiles no //go:build integration file in CI. Two of the five files here carry that tag: the integration suite and the EXPLAIN suite. --max-same-issues=0 --max-issues-per-linter=0 are load-bearing, because the defaults cap repeats of one message at 3 and any single linter at 50.

The whole-package total is not evidence about this change, and the numbers are what show why. It moved from 3844 to 3949 over this branch with no code change of ours: the rebase brought main's own advance into the package, and all 105 new findings sit in files this branch does not open.

go build, go vet -tags=integration and gofmt -l are all clean over internal/datastore/ at fcd799c74.

The whole internal/datastore package passed with the integration tag twice, and each run is labelled with the commit and base it actually ran at rather than with the commit that later replaced it. GOFLAGS= go test -tags=integration -count=1 ./internal/datastore/ returned ok internal/datastore 437.688s with zero FAIL lines at 6065a42e, on the retired base e7b6a6cc; the rebase renamed that commit 54205df2d. The pre-commit chain of a5e49fd0, which the rebase renamed 523e19623, ran the tagged suite again on the same base. Neither run covers the test set at the names those commits now carry: git diff --numstat 6065a42e 54205df2d -- internal/datastore/ lists 32 files, four of them new test files in the package under test. What covers the current tree is a pre-commit chain on that exact tree, plus the merge-request pipeline. The chain ran go test -short ./... with the integration tag — GOFLAGS carries -tags=integration,development_stubs in this worktree — plus go-fmt, go-imports, go-mod-tidy and golangci-lint, all passing. It ran at the commit that a later message-only rewrite replaced, so it is the tree rather than the commit name that carries the evidence: git diff between the two commits is empty, and the tip's four commands above were re-run at fcd799c74 itself.

Checks that did not run

Each of the four runtime obligations is named with its trigger, because silence reads as a pass:

Obligation	Trigger	Outcome
Drive the running service (`/run`)	a change to handlers, routes, middleware, storage behavior, migrations or configuration semantics	not invoked — none of those changed, and no production caller reaches the new code
Run recipe plus `driver.sh smoke`	a change to how the service builds, boots or dispatches requests	untriggered — `.claude/skills/run-artifact-registry/` names none of this code
Maven, npm and OCI conformance	a change to a format package or a wire protocol	untriggered — no file under `internal/format/`, and datastore SQL over format tables is on no client's wire
`docs/testing/` catalogs	a `feat` or `fix` step	untriggered — the plan gives this step the type `chore`

/run was not invoked because its trigger did not fire. Two build checks replace the boot, and both passed: go build ./... over the whole tree, and a link of ./cmd/artifact-registry. No obligation ran and failed.

Database Review Evidence

Note

Query mode of db-review-prep ran on this branch. Migration mode did not run, because the diff adds no migration file. The plans come from EXPLAIN (ANALYZE, BUFFERS) against an ephemeral PostgreSQL 17 container, at the migration chain of this branch head. PostgreSQL 17 is the GL_PG_CURR_VERSION value in .gitlab-ci-other-versions.yml. Each seed ran inside BEGIN and rolled back. The container is torn down.

Queries

Six new statements reach the database, all of them from RepositoryReconcileStore.RecomputeSizeBytes. The dispatch in repositoryReconcileWalks picks one per (format, kind) pair, so each of the six is a branch of that one method. Every other statement builder in the file is byte-identical to the merge base 5f1e4cfc, so none of them carries a new plan.

Scenario A is the baseline. One namespace holds all six chains, and the addressed repository holds most of the rows in its own tables.

Method branch	Plan node	Index	Rows (plan / actual)	Cost	Time	Buffers (hit / read)	Partitions
`recomputeContainerBlobsSizeStmt`	Aggregate	none (Seq Scan)	3025 / 5300	547.51	3.992 ms	211 / 0	5 tables, each 1/64
`recomputeContainerRemoteBlobsSizeStmt`	Aggregate	none (Seq Scan)	3025 / 5300	547.51	4.354 ms	211 / 0	5 tables, each 1/64
`recomputeMavenFilesSizeStmt`	Aggregate	none (Seq Scan)	2750 / 5000	567.33	4.010 ms	260 / 0	4 tables, each 1/64
`recomputeMavenRemoteFilesSizeStmt`	Aggregate	none (Seq Scan)	2750 / 5000	564.09	4.097 ms	257 / 0	4 tables, each 1/64
`recomputeNpmFilesSizeStmt`	Aggregate	none (Seq Scan)	3250 / 6200	634.74	5.553 ms	225 / 0	6 tables, each 1/64
`recomputeNpmRemoteFilesSizeStmt`	Aggregate	none (Seq Scan)	3250 / 6200	643.11	5.318 ms	234 / 0	6 tables, each 1/64

The Rows column reads off the join above the aggregate, because the aggregate itself always returns one row. The Partitions column gives one figure for a group of tables where every member has the same figure. The details block for each walk names its own tables.

No partition fan-out anywhere. Every partitioned table in all six plans reached exactly one of its 64 hash partitions. In scenario A that partition is p28 for each table. This confirms the prune that reconcileBlobSizeStmt claims for the shadow table, and it confirms it for all six walks, not only for the one walk that carries a plan pin.

ADR-007: the prune claim holds, and the index-only claim is reachable

reconcileBlobSizeStmt states two properties of blob_storage_blobs_by_namespace. The namespace literal prunes the table to one partition, and each digest then resolves index-only inside it. The physical table agrees with the ADR. Its primary key is btree (namespace_id, sha256) INCLUDE (size).

The prune holds in every plan here.

The index-only half is not visible in scenario A, because the planner reads the whole pruned shadow partition and hash-joins it. That is the correct plan at that shape. The partition holds 7000 rows and the repository reaches 5000 to 6200 digests. The pin comment in reconcile_repository_explain_integration_test.go records the same effect at fixture scale.

Scenario B makes the shape production-like. The namespace holds 200,400 shadow rows and the repository reaches 200 digests. The planner then chooses what the ADR describes:

->  Index Only Scan using blob_storage_blobs_by_namespace_p19_pkey on blob_storage_blobs_by_namespace_p19
      Index Cond: ((namespace_id = '3333...'::uuid) AND (sha256 = container_blobs.blob_sha256))
      Heap Fetches: 200
      (actual time=0.001..0.001 rows=1 loops=200)

Execution time drops to 0.508 ms for the container walk and to 0.414 ms for the Maven walk. No plan in this run contradicts either of the two properties this section checked: the prune holds in every plan reported here, and the index-only path is reachable, which scenario B demonstrates. The Heap Fetches figure equals the row count only because the seed rows are uncommitted, so no visibility-map bit is set yet.

Two other claims in the same section of ADR-007 do not hold, and this run's own evidence is what falsifies them. ADR-007:2094 opens "Reconciliation cost scales with the repository's artifact count, not the namespace's", where Flag 1 shows a Seq Scan with a namespace Filter rather than an Index Cond, so the cost follows the partition. The same sentence says the format-specific indexes are partial on WHERE soft_deleted_at IS NULL, and against the DDL only the npm half is stated correctly: unique_container_blobs_ns_id_ci_id_digest (internal/datastore/migrations/structure.sql:50128) and unique_container_manifests_ns_id_ci_id_digest (:51438) are plain unique indexes with no predicate, and the Maven one carries a second maven_version_id IS NULL conjunct beyond the predicate quoted. The container half is the one that matters, because it is the walk ADR-007 spells out in full and the one this MR's EXPLAIN pin covers.

The code is not the deviation. It implements ADR-007's walk shape, its DISTINCT-before-join order and its shadow join, and it is stronger than the ADR's example queries, because it carries the namespace equality on the leaf table those examples leave implicit. What is falsified is ADR-007's text, against the schema and against measurement. This MR records the falsification and states nothing about what happens to the ADR: docs/adr/ is a synced mirror that cannot be written here, and the amendment is not this MR's to decide.

Flag 1: the cost of a size walk follows the partition, not the repository (concern)

Scenario C keeps the addressed repository at 200 digests. A sibling repository in the same namespace holds 59,800 rows in the same partition. The plan reverts to two full partition scans, and the row estimate is wrong by two orders of magnitude:

->  Hash Join  (cost=3410.63..5000.14 rows=30000 width=8) (actual time=8.515..17.390 rows=200 loops=1)
      Hash Cond: (blob_storage_blobs_by_namespace.sha256 = container_blobs.blob_sha256)
      ->  Seq Scan on blob_storage_blobs_by_namespace_p51 ...  (actual ... rows=60000 loops=1)
      ...
            ->  Seq Scan on container_blobs_p51 container_blobs ...  (actual ... rows=59980 loops=1)

Execution time is 18.157 ms for the same 180-blob repository that scenario B answered in 0.508 ms. The cause is the estimate, not a missing index. The repository predicate sits on the stub table container_repositories, two join levels away from container_blobs. PostgreSQL keeps no cross-table correlation statistics for that path, so it estimates 30,000 digests where 200 exist. This over-estimate is what takes the shadow join off the index-only path.

The effect belongs to all six walks, because all six share the tail and all six reach the blob-bearing table through two joins.

The pass is over the partition, not over the namespace. The node above is Seq Scan … Filter: (namespace_id = …) rather than an Index Cond, so one RecomputeSizeBytes call reads every heap page of the partition and applies the filter per row. The collecting tables and the shadow are partitioned HASH(namespace_id) over 64 partitions, so one partition holds the rows of roughly 1/64 of all namespaces — a fleet-level quantity, independent of how small the addressed repository or its namespace is.

Stated as inference, not measurement. Scenario C seeded one namespace, so Rows Removed by Filter was zero and the 18.157 ms reading covers one namespace's slice alone. The extrapolation follows from the node type and from the partition count; a multi-namespace partition is what would measure it.

A namespace-wide sweep over R repositories costs R such passes, and R is not bounded above 4,000. ADR-004:102 caps repositories at 1,000 per namespace per artifact type, so four formats admit up to 4,000 live at the ADR's own value, and the same row records the cap as configurable at the instance level. docs/specs/S22-storage-accounting.md:660 then says the walk also reaches the namespace's tombstoned-but-unpurged repositories, which that cap does not count at any value.

Where that decision sits is already written down, which is why this MR records the data rather than the answer. The plan's Step 14 entry (docs/plans/2026-08-04-s22-storage-accounting.md:1125-1129) assigns the per-statement deadline to that step and warns that too tight is the expensive direction, because last_reconciled_at is written only on success: a namespace whose recomputes cannot finish inside the bound is never stamped, stays selected on every fire, and drifts with no correction. The same entry's reasoning was written against one statement per repository; this step makes it two, of which the slower is the partition-scoped one. A batched form exists and is named here as an option for that step rather than as a recommendation this MR makes: one statement per (format, kind) grouping on the stub's repository_id returns every repository's total in a single pass, which is the shape namespace-level reconciliation already takes.

No decision is recorded here. Three options exist, the choice belongs to whoever owns the reconcile worker, and they are not three equal options:

Add a CREATE STATISTICS object so that the planner sees the correlation. This is the only one of the three that changes the scaling rather than the constant, because the over-estimate is what takes the shadow join off the index-only path, as the causal reading above states. It has no in-tree precedent: no CREATE STATISTICS object exists anywhere in the repository.
Accept the cost, and bound the sweep by namespace size in the worker. This accepts the partition scan and bounds its blast radius.
Split each walk into two round trips. Collect the digests first, then sum them with a bounded = ANY($1) list. This accepts the partition scan on the collecting half and bounds only the shadow read.

Flag 2: `maven_files` is the one collecting table with no repository-driven index path (concern)

Five of the six chains have a non-partial index that leads with (namespace_id, <parent id>), which is the pair each walk joins on:

Table	Walk joins on	Non-partial index that serves it
`container_blobs`	`container_image_id`	`unique_container_blobs_ns_id_ci_id_digest`
`container_manifests`	`container_image_id`	`unique_container_manifests_ns_id_ci_id_digest`
`container_remote_blobs`	`container_remote_image_id`	`index_container_remote_blobs_on_ns_id_image_id`
`container_remote_manifests`	`container_remote_image_id`	`index_container_remote_manifests_on_ns_id_image_id_last_dl_at`
`maven_files`	`maven_package_id`	none
`maven_remote_files`	`maven_remote_package_id`	`index_maven_remote_files_on_ns_id_pkg_id`
`npm_files`	`npm_version_id`	`index_npm_files_on_ns_id_ver_id`
`npm_metadata_files`	`npm_package_id`	`unique_npm_metadata_files_ns_id_pkg_id_kind`
`npm_remote_files`	`npm_remote_version_id`	`index_npm_remote_files_on_ns_id_ver_id`
`npm_remote_metadata_files`	`npm_remote_package_id`	`unique_npm_remote_metadata_files_ns_id_pkg_id_kind`

The only index on maven_files that leads with (namespace_id, maven_package_id) is unique_maven_files_ns_id_package_id_file_name_when_ver_null. It is partial on soft_deleted_at IS NULL AND maven_version_id IS NULL, and the size walk carries neither predicate. The walk must count a tombstoned file, and it must count a version-routed file, so both halves of that index predicate exclude it. The other non-partial index over that region, index_maven_files_on_ns_id_ver_id, keys (namespace_id, maven_version_id, blob_sha256). That is the column recomputeMavenFilesSizeStmt must not join through, for the reason its own doc comment gives.

A forced probe over scenario C makes the difference measurable. With enable_seqscan = off, the container and Maven remote chains reach an index-only scan over the rows of the addressed repository. Hosted Maven does not:

Chain	Node over the collecting table	Rows read	Buffers
container hosted	`Index Only Scan using container_blobs_p51_namespace_id_container_image_id_digest_idx`	180	158
Maven remote	`Index Only Scan using maven_remote_files_p51_namespace_id_maven_remote_package_i_idx1`	200	83
Maven hosted	`Bitmap Heap Scan on maven_files_p51` through `maven_files_p51_namespace_id_blob_storage_attachment_id_idx`	60000	2063

Hosted Maven therefore has no per-repository path available, where its five siblings have one. Flag 1 hides this today, because the estimate keeps all six on a full scan anyway. No improvement in row estimates makes the hosted Maven walk repository-scoped, because the only index leading (namespace_id, maven_package_id) is partial on soft_deleted_at IS NULL AND maven_version_id IS NULL and the size walk can imply neither conjunct. So a fix for Flag 1 through statistics alone helps five walks and leaves hosted Maven exactly where it is.

The remedy is ADR-007's own named next step rather than a novel option. ADR-007:2096 says that if production metrics ever show this is too slow for very large repositories, "the cheapest next step is a non-partial (namespace_id, parent_id) index on each of those tables", and nine of the ten collecting tables already carry that index — maven_files is the one that does not. So this is that remedy applied inconsistently, which is a materially easier decision than a new option to weigh.

Two shapes are in circulation for it and they are not interchangeable, so both are stated:

maven_files (namespace_id, maven_package_id) is ADR-007's own two-column form, and it is what the parity sibling carries: index_maven_remote_files_on_ns_id_pkg_id is bare btree (namespace_id, maven_remote_package_id) with no WHERE and no INCLUDE, and no blob_sha256.
maven_files (namespace_id, maven_package_id) INCLUDE (blob_sha256) would additionally keep the collecting half index-only, and that is strictly stronger than parity rather than another spelling of it: no parity index on any of the nine covers the column its own walk projects. container_blobs carries digest and blob_sha256 as separate columns and the walk projects blob_sha256, while unique_container_blobs_ns_id_ci_id_digest carries digest; index_maven_remote_files_on_ns_id_pkg_id carries neither.

The enable_seqscan = off probe in this section therefore measured the row-locating half of each walk rather than the walk's own projection. The cost either way is one more index on a table that already carries six: pk_maven_files plus five secondary indexes.

Checks with nothing to report

Unbounded SELECT: none. Each of the six is a single-row aggregate, COALESCE(SUM(...), 0) with no GROUP BY.
Plan versus actual row mismatch over 10x: none in scenario A. The worst figure is 1.9x, on the two npm walks. Flag 1 covers the scenario C figures.
Sort node above an index scan: none. No scenario A plan carries a Sort node.
Buffer reads: every scenario A plan is all shared hit during execution. The only read counts sit in the planning phase, against the catalog.
UNION deduplication: present and correct. Each of the four union walks shows HashAggregate with Group Key: <table>.blob_sha256 above an Append. Each of the two Maven walks shows the same HashAggregate from its DISTINCT, and no Append.

Scenario A seed shape

namespaces=1, repositories=12, blob_storage_blobs=7000, blob_storage_attachments=7000, blob_storage_blobs_by_namespace=7000.

Per format: container_repositories=2, container_images=70, container_blobs=5700, container_manifests=350. The container_remote_* chain has the same shape. maven_repositories=2, maven_packages=330, maven_versions=900, maven_files=5500, of which 1000 rows carry maven_version_id IS NULL. The maven_remote_* chain has the same shape. npm_repositories=2, npm_packages=440, npm_versions=1320, npm_files=5300, npm_metadata_files=1200. The npm_remote_* chain has the same shape.

Each format holds a second repository in the same namespace, so a walk joined to the wrong stub row would fold its rows in. The two container chains and the two npm chains share digests with each other, which is what a real namespace does. Each union walk reaches at least one digest twice, so the UNION has real duplicates to fold.

Scenario A plans, one block per walk

datastore.RecomputeSizeBytes.recomputeContainerBlobsSizeStmt

Partitioned tables in the plan: container_blobs, container_manifests, container_images, container_repositories, blob_storage_blobs_by_namespace. Each reached partition p28, one of 64.

Rendered SQL

SELECT COALESCE(SUM(blob_storage_blobs_by_namespace.size), $1) AS "total"
FROM (
          (
               SELECT container_blobs.blob_sha256 AS "blob_sha256"
               FROM public.container_blobs
                    INNER JOIN public.container_images ON ((container_images.id = container_blobs.container_image_id) AND (container_images.namespace_id = container_blobs.namespace_id))
                    INNER JOIN public.container_repositories ON ((container_repositories.id = container_images.container_repository_id) AND (container_repositories.namespace_id = container_images.namespace_id))
               WHERE (((container_blobs.namespace_id = $2::uuid) AND (container_images.namespace_id = $3::uuid)) AND (container_repositories.namespace_id = $4::uuid)) AND (container_repositories.repository_id = $5::uuid)
          )
          UNION
          (
               SELECT container_manifests.blob_sha256 AS "blob_sha256"
               FROM public.container_manifests
                    INNER JOIN public.container_images ON ((container_images.id = container_manifests.container_image_id) AND (container_images.namespace_id = container_manifests.namespace_id))
                    INNER JOIN public.container_repositories ON ((container_repositories.id = container_images.container_repository_id) AND (container_repositories.namespace_id = container_images.namespace_id))
               WHERE (((container_manifests.namespace_id = $6::uuid) AND (container_images.namespace_id = $7::uuid)) AND (container_repositories.namespace_id = $8::uuid)) AND (container_repositories.repository_id = $9::uuid)
          )
     ) AS uniq_blobs
     INNER JOIN public.blob_storage_blobs_by_namespace ON ((blob_storage_blobs_by_namespace.namespace_id = $10::uuid) AND (blob_storage_blobs_by_namespace.sha256 = uniq_blobs.blob_sha256));

Bound args: [0, 11111111-1111-1111-1111-111111111111, 11111111-1111-1111-1111-111111111111, 11111111-1111-1111-1111-111111111111, aa000000-0000-4000-8000-000000000001, 11111111-1111-1111-1111-111111111111, 11111111-1111-1111-1111-111111111111, 11111111-1111-1111-1111-111111111111, aa000000-0000-4000-8000-000000000001, 11111111-1111-1111-1111-111111111111]

Plan

 Aggregate  (cost=547.50..547.51 rows=1 width=32) (actual time=3.850..3.853 rows=1 loops=1)
   Buffers: shared hit=211
   ->  Hash Join  (cost=354.05..539.93 rows=3025 width=8) (actual time=2.683..3.700 rows=5300 loops=1)
         Hash Cond: (blob_storage_blobs_by_namespace.sha256 = container_blobs.blob_sha256)
         Buffers: shared hit=211
         ->  Seq Scan on blob_storage_blobs_by_namespace_p28 blob_storage_blobs_by_namespace  (cost=0.00..167.50 rows=7000 width=41) (actual time=0.005..0.414 rows=7000 loops=1)
               Filter: (namespace_id = '11111111-1111-1111-1111-111111111111'::uuid)
               Buffers: shared hit=80
         ->  Hash  (cost=316.24..316.24 rows=3025 width=32) (actual time=2.660..2.661 rows=5300 loops=1)
               Buckets: 8192 (originally 4096)  Batches: 1 (originally 1)  Memory Usage: 401kB
               Buffers: shared hit=131
               ->  HashAggregate  (cost=285.99..316.24 rows=3025 width=32) (actual time=1.933..2.174 rows=5300 loops=1)
                     Group Key: container_blobs.blob_sha256
                     Batches: 1  Memory Usage: 721kB
                     Buffers: shared hit=131
                     ->  Append  (cost=3.87..278.42 rows=3025 width=32) (actual time=0.031..1.121 rows=5500 loops=1)
                           Buffers: shared hit=131
                           ->  Hash Join  (cost=3.87..241.99 rows=2850 width=33) (actual time=0.031..0.853 rows=5200 loops=1)
                                 Hash Cond: (container_blobs.container_image_id = container_images.id)
                                 Buffers: shared hit=119
                                 ->  Seq Scan on container_blobs_p28 container_blobs  (cost=0.00..188.25 rows=5700 width=65) (actual time=0.003..0.426 rows=5700 loops=1)
                                       Filter: (namespace_id = '11111111-1111-1111-1111-111111111111'::uuid)
                                       Buffers: shared hit=117
                                 ->  Hash  (cost=3.43..3.43 rows=35 width=32) (actual time=0.023..0.024 rows=60 loops=1)
                                       Buckets: 1024  Batches: 1  Memory Usage: 12kB
                                       Buffers: shared hit=2
                                       ->  Nested Loop  (cost=0.00..3.43 rows=35 width=32) (actual time=0.006..0.012 rows=60 loops=1)
                                             Buffers: shared hit=2
                                             ->  Seq Scan on container_repositories_p28 container_repositories  (cost=0.00..1.03 rows=1 width=32) (actual time=0.002..0.003 rows=1 loops=1)
                                                   Filter: ((namespace_id = '11111111-1111-1111-1111-111111111111'::uuid) AND (repository_id = 'aa000000-0000-4000-8000-000000000001'::uuid))
                                                   Rows Removed by Filter: 1
                                                   Buffers: shared hit=1
                                             ->  Seq Scan on container_images_p28 container_images  (cost=0.00..2.05 rows=35 width=48) (actual time=0.002..0.006 rows=60 loops=1)
                                                   Filter: ((namespace_id = '11111111-1111-1111-1111-111111111111'::uuid) AND (container_repositories.id = container_repository_id))
                                                   Rows Removed by Filter: 10
                                                   Buffers: shared hit=1
                           ->  Hash Join  (cost=3.87..21.30 rows=175 width=33) (actual time=0.035..0.090 rows=300 loops=1)
                                 Hash Cond: (container_manifests.container_image_id = container_images_1.id)
                                 Buffers: shared hit=12
                                 ->  Seq Scan on container_manifests_p28 container_manifests  (cost=0.00..14.38 rows=350 width=65) (actual time=0.004..0.034 rows=350 loops=1)
                                       Filter: (namespace_id = '11111111-1111-1111-1111-111111111111'::uuid)
                                       Buffers: shared hit=10
                                 ->  Hash  (cost=3.43..3.43 rows=35 width=32) (actual time=0.023..0.023 rows=60 loops=1)
                                       Buckets: 1024  Batches: 1  Memory Usage: 12kB
                                       Buffers: shared hit=2
                                       ->  Nested Loop  (cost=0.00..3.43 rows=35 width=32) (actual time=0.005..0.011 rows=60 loops=1)
                                             Buffers: shared hit=2
                                             ->  Seq Scan on container_repositories_p28 container_repositories_1  (cost=0.00..1.03 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)
                                                   Filter: ((namespace_id = '11111111-1111-1111-1111-111111111111'::uuid) AND (repository_id = 'aa000000-0000-4000-8000-000000000001'::uuid))
                                                   Rows Removed by Filter: 1
                                                   Buffers: shared hit=1
                                             ->  Seq Scan on container_images_p28 container_images_1  (cost=0.00..2.05 rows=35 width=48) (actual time=0.003..0.006 rows=60 loops=1)
                                                   Filter: ((namespace_id = '11111111-1111-1111-1111-111111111111'::uuid) AND (container_repositories_1.id = container_repository_id))
                                                   Rows Removed by Filter: 10
                                                   Buffers: shared hit=1
 Planning:
   Buffers: shared hit=621 read=9
 Planning Time: 1.417 ms
 Execution Time: 3.992 ms

Timings: planning 1.417 ms, execution 3.992 ms, total 5.409 ms.

datastore.RecomputeSizeBytes.recomputeContainerRemoteBlobsSizeStmt

Partitioned tables in the plan: container_remote_blobs, container_remote_manifests, container_remote_images, container_remote_repositories, blob_storage_blobs_by_namespace. Each reached partition p28, one of 64.

Rendered SQL

SELECT COALESCE(SUM(blob_storage_blobs_by_namespace.size), $1) AS "total"
FROM (
          (
               SELECT container_remote_blobs.blob_sha256 AS "blob_sha256"
               FROM public.container_remote_blobs
                    INNER JOIN public.container_remote_images ON ((container_remote_images.id = container_remote_blobs.container_remote_image_id) AND (container_remote_images.namespace_id = container_remote_blobs.namespace_id))
                    INNER JOIN public.container_remote_repositories ON ((container_remote_repositories.id = container_remote_images.container_remote_repository_id) AND (container_remote_repositories.namespace_id = container_remote_images.namespace_id))
               WHERE (((container_remote_blobs.namespace_id = $2::uuid) AND (container_remote_images.namespace_id = $3::uuid)) AND (container_remote_repositories.namespace_id = $4::uuid)) AND (container_remote_repositories.repository_id = $5::uuid)
          )
          UNION
          (
               SELECT container_remote_manifests.blob_sha256 AS "blob_sha256"
               FROM public.container_remote_manifests
                    INNER JOIN public.container_remote_images ON ((container_remote_images.id = container_remote_manifests.container_remote_image_id) AND (container_remote_images.namespace_id = container_remote_manifests.namespace_id))
                    INNER JOIN public.container_remote_repositories ON ((container_remote_repositories.id = container_remote_images.container_remote_repository_id) AND (container_remote_repositories.namespace_id = container_remote_images.namespace_id))
               WHERE (((container_remote_manifests.namespace_id = $6::uuid) AND (container_remote_images.namespace_id = $7::uuid)) AND (container_remote_repositories.namespace_id = $8::uuid)) AND (container_remote_repositories.repository_id = $9::uuid)
          )
     ) AS uniq_blobs
     INNER JOIN public.blob_storage_blobs_by_namespace ON ((blob_storage_blobs_by_namespace.namespace_id = $10::uuid) AND (blob_storage_blobs_by_namespace.sha256 = uniq_blobs.blob_sha256));

Bound args: [0, 11111111-1111-1111-1111-111111111111, 11111111-1111-1111-1111-111111111111, 11111111-1111-1111-1111-111111111111, aa000000-0000-4000-8000-000000000002, 11111111-1111-1111-1111-111111111111, 11111111-1111-1111-1111-111111111111, 11111111-1111-1111-1111-111111111111, aa000000-0000-4000-8000-000000000002, 11111111-1111-1111-1111-111111111111]

Plan

 Aggregate  (cost=547.50..547.51 rows=1 width=32) (actual time=4.210..4.213 rows=1 loops=1)
   Buffers: shared hit=211
   ->  Hash Join  (cost=354.05..539.93 rows=3025 width=8) (actual time=2.926..4.049 rows=5300 loops=1)
         Hash Cond: (blob_storage_blobs_by_namespace.sha256 = container_remote_blobs.blob_sha256)
         Buffers: shared hit=211
         ->  Seq Scan on blob_storage_blobs_by_namespace_p28 blob_storage_blobs_by_namespace  (cost=0.00..167.50 rows=7000 width=41) (actual time=0.005..0.460 rows=7000 loops=1)
               Filter: (namespace_id = '11111111-1111-1111-1111-111111111111'::uuid)
               Buffers: shared hit=80
         ->  Hash  (cost=316.24..316.24 rows=3025 width=32) (actual time=2.904..2.906 rows=5300 loops=1)
               Buckets: 8192 (originally 4096)  Batches: 1 (originally 1)  Memory Usage: 401kB
               Buffers: shared hit=131
               ->  HashAggregate  (cost=285.99..316.24 rows=3025 width=32) (actual time=2.093..2.352 rows=5300 loops=1)
                     Group Key: container_remote_blobs.blob_sha256
                     Batches: 1  Memory Usage: 721kB
                     Buffers: shared hit=131
                     ->  Append  (cost=3.87..278.42 rows=3025 width=32) (actual time=0.030..1.227 rows=5500 loops=1)
                           Buffers: shared hit=131
                           ->  Hash Join  (cost=3.87..241.99 rows=2850 width=33) (actual time=0.030..0.942 rows=5200 loops=1)
                                 Hash Cond: (container_remote_blobs.container_remote_image_id = container_remote_images.id)
                                 Buffers: shared hit=119
                                 ->  Seq Scan on container_remote_blobs_p28 container_remote_blobs  (cost=0.00..188.25 rows=5700 width=65) (actual time=0.004..0.473 rows=5700 loops=1)
                                       Filter: (namespace_id = '11111111-1111-1111-1111-111111111111'::uuid)
                                       Buffers: shared hit=117
                                 ->  Hash  (cost=3.43..3.43 rows=35 width=32) (actual time=0.024..0.024 rows=60 loops=1)
                                       Buckets: 1024  Batches: 1  Memory Usage: 12kB
                                       Buffers: shared hit=2
                                       ->  Nested Loop  (cost=0.00..3.43 rows=35 width=32) (actual time=0.005..0.012 rows=60 loops=1)
                                             Buffers: shared hit=2
                                             ->  Seq Scan on container_remote_repositories_p28 container_remote_repositories  (cost=0.00..1.03 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1)
                                                   Filter: ((namespace_id = '11111111-1111-1111-1111-111111111111'::uuid) AND (repository_id = 'aa000000-0000-4000-8000-000000000002'::uuid))
                                                   Rows Removed by Filter: 1
                                                   Buffers: shared hit=1
                                             ->  Seq Scan on container_remote_images_p28 container_remote_images  (cost=0.00..2.05 rows=35 width=48) (actual time=0.003..0.006 rows=60 loops=1)
                                                   Filter: ((namespace_id = '11111111-1111-1111-1111-111111111111'::uuid) AND (container_remote_repositories.id = container_remote_repository_id))
                                                   Rows Removed by Filter: 10
                                                   Buffers: shared hit=1
                           ->  Hash Join  (cost=3.87..21.30 rows=175 width=33) (actual time=0.031..0.091 rows=300 loops=1)
                                 Hash Cond: (container_remote_manifests.container_remote_image_id = container_remote_images_1.id)
                                 Buffers: shared hit=12
                                 ->  Seq Scan on container_remote_manifests_p28 container_remote_manifests  (cost=0.00..14.38 rows=350 width=65) (actual time=0.004..0.036 rows=350 loops=1)
                                       Filter: (namespace_id = '11111111-1111-1111-1111-111111111111'::uuid)
                                       Buffers: shared hit=10
                                 ->  Hash  (cost=3.43..3.43 rows=35 width=32) (actual time=0.025..0.025 rows=60 loops=1)
                                       Buckets: 1024  Batches: 1  Memory Usage: 12kB
                                       Buffers: shared hit=2
                                       ->  Nested Loop  (cost=0.00..3.43 rows=35 width=32) (actual time=0.005..0.011 rows=60 loops=1)
                                             Buffers: shared hit=2
                                             ->  Seq Scan on container_remote_repositories_p28 container_remote_repositories_1  (cost=0.00..1.03 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)
                                                   Filter: ((namespace_id = '11111111-1111-1111-1111-111111111111'::uuid) AND (repository_id = 'aa000000-0000-4000-8000-000000000002'::uuid))
                                                   Rows Removed by Filter: 1
                                                   Buffers: shared hit=1
                                             ->  Seq Scan on container_remote_images_p28 container_remote_images_1  (cost=0.00..2.05 rows=35 width=48) (actual time=0.003..0.007 rows=60 loops=1)
                                                   Filter: ((namespace_id = '11111111-1111-1111-1111-111111111111'::uuid) AND (container_remote_repositories_1.id = container_remote_repository_id))
                                                   Rows Removed by Filter: 10
                                                   Buffers: shared hit=1
 Planning:
   Buffers: shared hit=1301 read=7
 Planning Time: 2.074 ms
 Execution Time: 4.354 ms

Timings: planning 2.074 ms, execution 4.354 ms, total 6.428 ms.

datastore.RecomputeSizeBytes.recomputeMavenFilesSizeStmt

Partitioned tables in the plan: maven_files, maven_packages, maven_repositories, blob_storage_blobs_by_namespace. Each reached partition p28, one of 64.

Rendered SQL

SELECT COALESCE(SUM(blob_storage_blobs_by_namespace.size), $1) AS "total"
FROM (
          SELECT DISTINCT maven_files.blob_sha256 AS "blob_sha256"
          FROM public.maven_files
               INNER JOIN public.maven_packages ON ((maven_packages.id = maven_files.maven_package_id) AND (maven_packages.namespace_id = maven_files.namespace_id))
               INNER JOIN public.maven_repositories ON ((maven_repositories.id = maven_packages.maven_repository_id) AND (maven_repositories.namespace_id = maven_packages.namespace_id))
          WHERE (((maven_files.namespace_id = $2::uuid) AND (maven_packages.namespace_id = $3::uuid)) AND (maven_repositories.namespace_id = $4::uuid)) AND (maven_repositories.repository_id = $5::uuid)
     ) AS uniq_blobs
     INNER JOIN public.blob_storage_blobs_by_namespace ON ((blob_storage_blobs_by_namespace.namespace_id = $6::uuid) AND (blob_storage_blobs_by_namespace.sha256 = uniq_blobs.blob_sha256));

Plan

 Aggregate  (cost=567.32..567.33 rows=1 width=32) (actual time=3.885..3.887 rows=1 loops=1)
   Buffers: shared hit=260
   ->  Hash Join  (cost=374.56..560.44 rows=2750 width=8) (actual time=2.631..3.729 rows=5000 loops=1)
         Hash Cond: (blob_storage_blobs_by_namespace.sha256 = maven_files.blob_sha256)
         Buffers: shared hit=260
         ->  Seq Scan on blob_storage_blobs_by_namespace_p28 blob_storage_blobs_by_namespace  (cost=0.00..167.50 rows=7000 width=41) (actual time=0.006..0.455 rows=7000 loops=1)
               Filter: (namespace_id = '11111111-1111-1111-1111-111111111111'::uuid)
               Buffers: shared hit=80
         ->  Hash  (cost=340.18..340.18 rows=2750 width=33) (actual time=2.610..2.613 rows=5000 loops=1)
               Buckets: 8192 (originally 4096)  Batches: 1 (originally 1)  Memory Usage: 382kB
               Buffers: shared hit=180
               ->  HashAggregate  (cost=312.68..340.18 rows=2750 width=33) (actual time=1.803..2.068 rows=5000 loops=1)
                     Group Key: maven_files.blob_sha256
                     Batches: 1  Memory Usage: 721kB
                     Buffers: shared hit=180
                     ->  Hash Join  (cost=14.93..305.81 rows=2750 width=33) (actual time=0.093..1.034 rows=5000 loops=1)
                           Hash Cond: (maven_files.maven_package_id = maven_packages.id)
                           Buffers: shared hit=180
                           ->  Seq Scan on maven_files_p28 maven_files  (cost=0.00..242.75 rows=5500 width=65) (actual time=0.005..0.495 rows=5500 loops=1)
                                 Filter: (namespace_id = '11111111-1111-1111-1111-111111111111'::uuid)
                                 Buffers: shared hit=174
                           ->  Hash  (cost=12.87..12.87 rows=165 width=32) (actual time=0.087..0.088 rows=300 loops=1)
                                 Buckets: 1024  Batches: 1  Memory Usage: 27kB
                                 Buffers: shared hit=6
                                 ->  Hash Join  (cost=1.04..12.87 rows=165 width=32) (actual time=0.013..0.057 rows=300 loops=1)
                                       Hash Cond: (maven_packages.maven_repository_id = maven_repositories.id)
                                       Buffers: shared hit=6
                                       ->  Seq Scan on maven_packages_p28 maven_packages  (cost=0.00..9.12 rows=330 width=48) (actual time=0.003..0.024 rows=330 loops=1)
                                             Filter: (namespace_id = '11111111-1111-1111-1111-111111111111'::uuid)
                                             Buffers: shared hit=5
                                       ->  Hash  (cost=1.03..1.03 rows=1 width=32) (actual time=0.008..0.009 rows=1 loops=1)
                                             Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                             Buffers: shared hit=1
                                             ->  Seq Scan on maven_repositories_p28 maven_repositories  (cost=0.00..1.03 rows=1 width=32) (actual time=0.002..0.003 rows=1 loops=1)
                                                   Filter: ((namespace_id = '11111111-1111-1111-1111-111111111111'::uuid) AND (repository_id = 'aa000000-0000-4000-8000-000000000003'::uuid))
                                                   Rows Removed by Filter: 1
                                                   Buffers: shared hit=1
 Planning:
   Buffers: shared hit=526 read=3
 Planning Time: 1.001 ms
 Execution Time: 4.010 ms

Timings: planning 1.001 ms, execution 4.010 ms, total 5.011 ms.

datastore.RecomputeSizeBytes.recomputeMavenRemoteFilesSizeStmt

Partitioned tables in the plan: maven_remote_files, maven_remote_packages, maven_remote_repositories, blob_storage_blobs_by_namespace. Each reached partition p28, one of 64.

Rendered SQL

SELECT COALESCE(SUM(blob_storage_blobs_by_namespace.size), $1) AS "total"
FROM (
          SELECT DISTINCT maven_remote_files.blob_sha256 AS "blob_sha256"
          FROM public.maven_remote_files
               INNER JOIN public.maven_remote_packages ON ((maven_remote_packages.id = maven_remote_files.maven_remote_package_id) AND (maven_remote_packages.namespace_id = maven_remote_files.namespace_id))
               INNER JOIN public.maven_remote_repositories ON ((maven_remote_repositories.id = maven_remote_packages.maven_remote_repository_id) AND (maven_remote_repositories.namespace_id = maven_remote_packages.namespace_id))
          WHERE (((maven_remote_files.namespace_id = $2::uuid) AND (maven_remote_packages.namespace_id = $3::uuid)) AND (maven_remote_repositories.namespace_id = $4::uuid)) AND (maven_remote_repositories.repository_id = $5::uuid)
     ) AS uniq_blobs
     INNER JOIN public.blob_storage_blobs_by_namespace ON ((blob_storage_blobs_by_namespace.namespace_id = $6::uuid) AND (blob_storage_blobs_by_namespace.sha256 = uniq_blobs.blob_sha256));

Plan

 Aggregate  (cost=564.08..564.09 rows=1 width=32) (actual time=3.955..3.957 rows=1 loops=1)
   Buffers: shared hit=257
   ->  Hash Join  (cost=371.32..557.20 rows=2750 width=8) (actual time=2.693..3.793 rows=5000 loops=1)
         Hash Cond: (blob_storage_blobs_by_namespace.sha256 = maven_remote_files.blob_sha256)
         Buffers: shared hit=257
         ->  Seq Scan on blob_storage_blobs_by_namespace_p28 blob_storage_blobs_by_namespace  (cost=0.00..167.50 rows=7000 width=41) (actual time=0.006..0.452 rows=7000 loops=1)
               Filter: (namespace_id = '11111111-1111-1111-1111-111111111111'::uuid)
               Buffers: shared hit=80
         ->  Hash  (cost=336.94..336.94 rows=2750 width=33) (actual time=2.669..2.671 rows=5000 loops=1)
               Buckets: 8192 (originally 4096)  Batches: 1 (originally 1)  Memory Usage: 382kB
               Buffers: shared hit=177
               ->  HashAggregate  (cost=309.44..336.94 rows=2750 width=33) (actual time=1.905..2.162 rows=5000 loops=1)
                     Group Key: maven_remote_files.blob_sha256
                     Batches: 1  Memory Usage: 721kB
                     Buffers: shared hit=177
                     ->  Hash Join  (cost=14.69..302.57 rows=2750 width=33) (actual time=0.081..1.087 rows=5000 loops=1)
                           Hash Cond: (maven_remote_files.maven_remote_package_id = maven_remote_packages.id)
                           Buffers: shared hit=177
                           ->  Seq Scan on maven_remote_files_p28 maven_remote_files  (cost=0.00..239.75 rows=5500 width=65) (actual time=0.004..0.538 rows=5500 loops=1)
                                 Filter: (namespace_id = '11111111-1111-1111-1111-111111111111'::uuid)
                                 Buffers: shared hit=171
                           ->  Hash  (cost=12.63..12.63 rows=165 width=32) (actual time=0.075..0.076 rows=300 loops=1)
                                 Buckets: 1024  Batches: 1  Memory Usage: 27kB
                                 Buffers: shared hit=6
                                 ->  Nested Loop  (cost=0.00..12.63 rows=165 width=32) (actual time=0.007..0.044 rows=300 loops=1)
                                       Buffers: shared hit=6
                                       ->  Seq Scan on maven_remote_repositories_p28 maven_remote_repositories  (cost=0.00..1.03 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=1)
                                             Filter: ((namespace_id = '11111111-1111-1111-1111-111111111111'::uuid) AND (repository_id = 'aa000000-0000-4000-8000-000000000004'::uuid))
                                             Rows Removed by Filter: 1
                                             Buffers: shared hit=1
                                       ->  Seq Scan on maven_remote_packages_p28 maven_remote_packages  (cost=0.00..9.95 rows=165 width=48) (actual time=0.003..0.026 rows=300 loops=1)
                                             Filter: ((namespace_id = '11111111-1111-1111-1111-111111111111'::uuid) AND (maven_remote_repositories.id = maven_remote_repository_id))
                                             Rows Removed by Filter: 30
                                             Buffers: shared hit=5
 Planning:
   Buffers: shared hit=943
 Planning Time: 1.521 ms
 Execution Time: 4.097 ms

Timings: planning 1.521 ms, execution 4.097 ms, total 5.618 ms.

datastore.RecomputeSizeBytes.recomputeNpmFilesSizeStmt

Partitioned tables in the plan: npm_files, npm_metadata_files, npm_versions, npm_packages, npm_repositories, blob_storage_blobs_by_namespace. Each reached partition p28, one of 64.

Rendered SQL

SELECT COALESCE(SUM(blob_storage_blobs_by_namespace.size), $1) AS "total"
FROM (
          (
               SELECT npm_files.blob_sha256 AS "blob_sha256"
               FROM public.npm_files
                    INNER JOIN public.npm_versions ON ((npm_versions.id = npm_files.npm_version_id) AND (npm_versions.namespace_id = npm_files.namespace_id))
                    INNER JOIN public.npm_packages ON ((npm_packages.id = npm_versions.npm_package_id) AND (npm_packages.namespace_id = npm_versions.namespace_id))
                    INNER JOIN public.npm_repositories ON ((npm_repositories.id = npm_packages.npm_repository_id) AND (npm_repositories.namespace_id = npm_packages.namespace_id))
               WHERE ((((npm_files.namespace_id = $2::uuid) AND (npm_versions.namespace_id = $3::uuid)) AND (npm_packages.namespace_id = $4::uuid)) AND (npm_repositories.namespace_id = $5::uuid)) AND (npm_repositories.repository_id = $6::uuid)
          )
          UNION
          (
               SELECT npm_metadata_files.blob_sha256 AS "blob_sha256"
               FROM public.npm_metadata_files
                    INNER JOIN public.npm_packages ON ((npm_packages.id = npm_metadata_files.npm_package_id) AND (npm_packages.namespace_id = npm_metadata_files.namespace_id))
                    INNER JOIN public.npm_repositories ON ((npm_repositories.id = npm_packages.npm_repository_id) AND (npm_repositories.namespace_id = npm_packages.namespace_id))
               WHERE (((npm_metadata_files.namespace_id = $7::uuid) AND (npm_packages.namespace_id = $8::uuid)) AND (npm_repositories.namespace_id = $9::uuid)) AND (npm_repositories.repository_id = $10::uuid)
          )
     ) AS uniq_blobs
     INNER JOIN public.blob_storage_blobs_by_namespace ON ((blob_storage_blobs_by_namespace.namespace_id = $11::uuid) AND (blob_storage_blobs_by_namespace.sha256 = uniq_blobs.blob_sha256));

Bound args: [0, 11111111-1111-1111-1111-111111111111, 11111111-1111-1111-1111-111111111111, 11111111-1111-1111-1111-111111111111, 11111111-1111-1111-1111-111111111111, aa000000-0000-4000-8000-000000000005, 11111111-1111-1111-1111-111111111111, 11111111-1111-1111-1111-111111111111, 11111111-1111-1111-1111-111111111111, aa000000-0000-4000-8000-000000000005, 11111111-1111-1111-1111-111111111111]

Plan

 Aggregate  (cost=634.73..634.74 rows=1 width=32) (actual time=5.392..5.395 rows=1 loops=1)
   Buffers: shared hit=225
   ->  Hash Join  (cost=440.71..626.60 rows=3250 width=8) (actual time=4.041..5.206 rows=6200 loops=1)
         Hash Cond: (blob_storage_blobs_by_namespace.sha256 = npm_files.blob_sha256)
         Buffers: shared hit=225
         ->  Seq Scan on blob_storage_blobs_by_namespace_p28 blob_storage_blobs_by_namespace  (cost=0.00..167.50 rows=7000 width=41) (actual time=0.005..0.449 rows=7000 loops=1)
               Filter: (namespace_id = '11111111-1111-1111-1111-111111111111'::uuid)
               Buffers: shared hit=80
         ->  Hash  (cost=400.09..400.09 rows=3250 width=32) (actual time=4.020..4.023 rows=6200 loops=1)
               Buckets: 8192 (originally 4096)  Batches: 1 (originally 1)  Memory Usage: 458kB
               Buffers: shared hit=145
               ->  HashAggregate  (cost=367.59..400.09 rows=3250 width=32) (actual time=3.095..3.391 rows=6200 loops=1)
                     Group Key: npm_files.blob_sha256
                     Batches: 1  Memory Usage: 721kB
                     Buffers: shared hit=145
                     ->  Append  (cost=74.19..359.46 rows=3250 width=32) (actual time=0.496..2.057 rows=6200 loops=1)
                           Buffers: shared hit=145
                           ->  Hash Join  (cost=74.19..278.82 rows=2650 width=33) (actual time=0.495..1.481 rows=5000 loops=1)
                                 Hash Cond: (npm_files.npm_version_id = npm_versions.id)
                                 Buffers: shared hit=118
                                 ->  Seq Scan on npm_files_p28 npm_files  (cost=0.00..158.25 rows=5300 width=65) (actual time=0.005..0.507 rows=5300 loops=1)
                                       Filter: (namespace_id = '11111111-1111-1111-1111-111111111111'::uuid)
                                       Buffers: shared hit=92
                                 ->  Hash  (cost=65.94..65.94 rows=660 width=32) (actual time=0.489..0.490 rows=1200 loops=1)
                                       Buckets: 2048 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 91kB
                                       Buffers: shared hit=26
                                       ->  Hash Join  (cost=18.89..65.94 rows=660 width=32) (actual time=0.134..0.368 rows=1200 loops=1)
                                             Hash Cond: (npm_versions.npm_package_id = npm_packages.id)
                                             Buffers: shared hit=26
                                             ->  Seq Scan on npm_versions_p28 npm_versions  (cost=0.00..35.50 rows=1320 width=48) (actual time=0.004..0.099 rows=1320 loops=1)
                                                   Filter: (namespace_id = '11111111-1111-1111-1111-111111111111'::uuid)
                                                   Buffers: shared hit=19
                                             ->  Hash  (cost=16.14..16.14 rows=220 width=32) (actual time=0.127..0.128 rows=400 loops=1)
                                                   Buckets: 1024  Batches: 1  Memory Usage: 33kB
                                                   Buffers: shared hit=7
                                                   ->  Hash Join  (cost=1.04..16.14 rows=220 width=32) (actual time=0.022..0.087 rows=400 loops=1)
                                                         Hash Cond: (npm_packages.npm_repository_id = npm_repositories.id)
                                                         Buffers: shared hit=7
                                                         ->  Seq Scan on npm_packages_p28 npm_packages  (cost=0.00..11.50 rows=440 width=48) (actual time=0.004..0.035 rows=440 loops=1)
                                                               Filter: (namespace_id = '11111111-1111-1111-1111-111111111111'::uuid)
                                                               Buffers: shared hit=6
                                                         ->  Hash  (cost=1.03..1.03 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=1)
                                                               Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                                               Buffers: shared hit=1
                                                               ->  Seq Scan on npm_repositories_p28 npm_repositories  (cost=0.00..1.03 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=1)
                                                                     Filter: ((namespace_id = '11111111-1111-1111-1111-111111111111'::uuid) AND (repository_id = 'aa000000-0000-4000-8000-000000000005'::uuid))
                                                                     Rows Removed by Filter: 1
                                                                     Buffers: shared hit=1
                           ->  Hash Join  (cost=18.89..64.39 rows=600 width=33) (actual time=0.137..0.347 rows=1200 loops=1)
                                 Hash Cond: (npm_metadata_files.npm_package_id = npm_packages_1.id)
                                 Buffers: shared hit=27
                                 ->  Seq Scan on npm_metadata_files_p28 npm_metadata_files  (cost=0.00..35.00 rows=1200 width=65) (actual time=0.006..0.103 rows=1200 loops=1)
                                       Filter: (namespace_id = '11111111-1111-1111-1111-111111111111'::uuid)
                                       Buffers: shared hit=20
                                 ->  Hash  (cost=16.14..16.14 rows=220 width=32) (actual time=0.118..0.119 rows=400 loops=1)
                                       Buckets: 1024  Batches: 1  Memory Usage: 33kB
                                       Buffers: shared hit=7
                                       ->  Hash Join  (cost=1.04..16.14 rows=220 width=32) (actual time=0.018..0.081 rows=400 loops=1)
                                             Hash Cond: (npm_packages_1.npm_repository_id = npm_repositories_1.id)
                                             Buffers: shared hit=7
                                             ->  Seq Scan on npm_packages_p28 npm_packages_1  (cost=0.00..11.50 rows=440 width=48) (actual time=0.002..0.032 rows=440 loops=1)
                                                   Filter: (namespace_id = '11111111-1111-1111-1111-111111111111'::uuid)
                                                   Buffers: shared hit=6
                                             ->  Hash  (cost=1.03..1.03 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=1)
                                                   Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                                   Buffers: shared hit=1
                                                   ->  Seq Scan on npm_repositories_p28 npm_repositories_1  (cost=0.00..1.03 rows=1 width=32) (actual time=0.002..0.003 rows=1 loops=1)
                                                         Filter: ((namespace_id = '11111111-1111-1111-1111-111111111111'::uuid) AND (repository_id = 'aa000000-0000-4000-8000-000000000005'::uuid))
                                                         Rows Removed by Filter: 1
                                                         Buffers: shared hit=1
 Planning:
   Buffers: shared hit=863
 Planning Time: 1.942 ms
 Execution Time: 5.553 ms

Timings: planning 1.942 ms, execution 5.553 ms, total 7.495 ms.

datastore.RecomputeSizeBytes.recomputeNpmRemoteFilesSizeStmt

Partitioned tables in the plan: npm_remote_files, npm_remote_metadata_files, npm_remote_versions, npm_remote_packages, npm_remote_repositories, blob_storage_blobs_by_namespace. Each reached partition p28, one of 64.

Rendered SQL

SELECT COALESCE(SUM(blob_storage_blobs_by_namespace.size), $1) AS "total"
FROM (
          (
               SELECT npm_remote_files.blob_sha256 AS "blob_sha256"
               FROM public.npm_remote_files
                    INNER JOIN public.npm_remote_versions ON ((npm_remote_versions.id = npm_remote_files.npm_remote_version_id) AND (npm_remote_versions.namespace_id = npm_remote_files.namespace_id))
                    INNER JOIN public.npm_remote_packages ON ((npm_remote_packages.id = npm_remote_versions.npm_remote_package_id) AND (npm_remote_packages.namespace_id = npm_remote_versions.namespace_id))
                    INNER JOIN public.npm_remote_repositories ON ((npm_remote_repositories.id = npm_remote_packages.npm_remote_repository_id) AND (npm_remote_repositories.namespace_id = npm_remote_packages.namespace_id))
               WHERE ((((npm_remote_files.namespace_id = $2::uuid) AND (npm_remote_versions.namespace_id = $3::uuid)) AND (npm_remote_packages.namespace_id = $4::uuid)) AND (npm_remote_repositories.namespace_id = $5::uuid)) AND (npm_remote_repositories.repository_id = $6::uuid)
          )
          UNION
          (
               SELECT npm_remote_metadata_files.blob_sha256 AS "blob_sha256"
               FROM public.npm_remote_metadata_files
                    INNER JOIN public.npm_remote_packages ON ((npm_remote_packages.id = npm_remote_metadata_files.npm_remote_package_id) AND (npm_remote_packages.namespace_id = npm_remote_metadata_files.namespace_id))
                    INNER JOIN public.npm_remote_repositories ON ((npm_remote_repositories.id = npm_remote_packages.npm_remote_repository_id) AND (npm_remote_repositories.namespace_id = npm_remote_packages.namespace_id))
               WHERE (((npm_remote_metadata_files.namespace_id = $7::uuid) AND (npm_remote_packages.namespace_id = $8::uuid)) AND (npm_remote_repositories.namespace_id = $9::uuid)) AND (npm_remote_repositories.repository_id = $10::uuid)
          )
     ) AS uniq_blobs
     INNER JOIN public.blob_storage_blobs_by_namespace ON ((blob_storage_blobs_by_namespace.namespace_id = $11::uuid) AND (blob_storage_blobs_by_namespace.sha256 = uniq_blobs.blob_sha256));

Bound args: [0, 11111111-1111-1111-1111-111111111111, 11111111-1111-1111-1111-111111111111, 11111111-1111-1111-1111-111111111111, 11111111-1111-1111-1111-111111111111, aa000000-0000-4000-8000-000000000006, 11111111-1111-1111-1111-111111111111, 11111111-1111-1111-1111-111111111111, 11111111-1111-1111-1111-111111111111, aa000000-0000-4000-8000-000000000006, 11111111-1111-1111-1111-111111111111]

Plan

 Aggregate  (cost=643.10..643.11 rows=1 width=32) (actual time=5.164..5.167 rows=1 loops=1)
   Buffers: shared hit=234
   ->  Hash Join  (cost=449.08..634.97 rows=3250 width=8) (actual time=3.830..4.980 rows=6200 loops=1)
         Hash Cond: (blob_storage_blobs_by_namespace.sha256 = npm_remote_files.blob_sha256)
         Buffers: shared hit=234
         ->  Seq Scan on blob_storage_blobs_by_namespace_p28 blob_storage_blobs_by_namespace  (cost=0.00..167.50 rows=7000 width=41) (actual time=0.006..0.451 rows=7000 loops=1)
               Filter: (namespace_id = '11111111-1111-1111-1111-111111111111'::uuid)
               Buffers: shared hit=80
         ->  Hash  (cost=408.46..408.46 rows=3250 width=32) (actual time=3.811..3.813 rows=6200 loops=1)
               Buckets: 8192 (originally 4096)  Batches: 1 (originally 1)  Memory Usage: 458kB
               Buffers: shared hit=154
               ->  HashAggregate  (cost=375.96..408.46 rows=3250 width=32) (actual time=2.887..3.194 rows=6200 loops=1)
                     Group Key: npm_remote_files.blob_sha256
                     Batches: 1  Memory Usage: 721kB
                     Buffers: shared hit=154
                     ->  Append  (cost=71.88..367.83 rows=3250 width=32) (actual time=0.447..1.894 rows=6200 loops=1)
                           Buffers: shared hit=154
                           ->  Hash Join  (cost=71.88..287.50 rows=2650 width=33) (actual time=0.446..1.378 rows=5000 loops=1)
                                 Hash Cond: (npm_remote_files.npm_remote_version_id = npm_remote_versions.id)
                                 Buffers: shared hit=127
                                 ->  Seq Scan on npm_remote_files_p28 npm_remote_files  (cost=0.00..169.25 rows=5300 width=65) (actual time=0.004..0.457 rows=5300 loops=1)
                                       Filter: (namespace_id = '11111111-1111-1111-1111-111111111111'::uuid)
                                       Buffers: shared hit=103
                                 ->  Hash  (cost=63.63..63.63 rows=660 width=32) (actual time=0.442..0.443 rows=1200 loops=1)
                                       Buckets: 2048 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 91kB
                                       Buffers: shared hit=24
                                       ->  Hash Join  (cost=18.58..63.63 rows=660 width=32) (actual time=0.101..0.308 rows=1200 loops=1)
                                             Hash Cond: (npm_remote_versions.npm_remote_package_id = npm_remote_packages.id)
                                             Buffers: shared hit=24
                                             ->  Seq Scan on npm_remote_versions_p28 npm_remote_versions  (cost=0.00..33.50 rows=1320 width=48) (actual time=0.006..0.099 rows=1320 loops=1)
                                                   Filter: (namespace_id = '11111111-1111-1111-1111-111111111111'::uuid)
                                                   Buffers: shared hit=17
                                             ->  Hash  (cost=15.83..15.83 rows=220 width=32) (actual time=0.093..0.093 rows=400 loops=1)
                                                   Buckets: 1024  Batches: 1  Memory Usage: 33kB
                                                   Buffers: shared hit=7
                                                   ->  Nested Loop  (cost=0.00..15.83 rows=220 width=32) (actual time=0.007..0.054 rows=400 loops=1)
                                                         Buffers: shared hit=7
                                                         ->  Seq Scan on npm_remote_repositories_p28 npm_remote_repositories  (cost=0.00..1.03 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=1)
                                                               Filter: ((namespace_id = '11111111-1111-1111-1111-111111111111'::uuid) AND (repository_id = 'aa000000-0000-4000-8000-000000000006'::uuid))
                                                               Rows Removed by Filter: 1
                                                               Buffers: shared hit=1
                                                         ->  Seq Scan on npm_remote_packages_p28 npm_remote_packages  (cost=0.00..12.60 rows=220 width=48) (actual time=0.004..0.033 rows=400 loops=1)
                                                               Filter: ((namespace_id = '11111111-1111-1111-1111-111111111111'::uuid) AND (npm_remote_repositories.id = npm_remote_repository_id))
                                                               Rows Removed by Filter: 40
                                                               Buffers: shared hit=6
                           ->  Hash Join  (cost=18.58..64.08 rows=600 width=33) (actual time=0.090..0.298 rows=1200 loops=1)
                                 Hash Cond: (npm_remote_metadata_files.npm_remote_package_id = npm_remote_packages_1.id)
                                 Buffers: shared hit=27
                                 ->  Seq Scan on npm_remote_metadata_files_p28 npm_remote_metadata_files  (cost=0.00..35.00 rows=1200 width=65) (actual time=0.004..0.103 rows=1200 loops=1)
                                       Filter: (namespace_id = '11111111-1111-1111-1111-111111111111'::uuid)
                                       Buffers: shared hit=20
                                 ->  Hash  (cost=15.83..15.83 rows=220 width=32) (actual time=0.085..0.085 rows=400 loops=1)
                                       Buckets: 1024  Batches: 1  Memory Usage: 33kB
                                       Buffers: shared hit=7
                                       ->  Nested Loop  (cost=0.00..15.83 rows=220 width=32) (actual time=0.005..0.050 rows=400 loops=1)
                                             Buffers: shared hit=7
                                             ->  Seq Scan on npm_remote_repositories_p28 npm_remote_repositories_1  (cost=0.00..1.03 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=1)
                                                   Filter: ((namespace_id = '11111111-1111-1111-1111-111111111111'::uuid) AND (repository_id = 'aa000000-0000-4000-8000-000000000006'::uuid))
                                                   Rows Removed by Filter: 1
                                                   Buffers: shared hit=1
                                             ->  Seq Scan on npm_remote_packages_p28 npm_remote_packages_1  (cost=0.00..12.60 rows=220 width=48) (actual time=0.002..0.030 rows=400 loops=1)
                                                   Filter: ((namespace_id = '11111111-1111-1111-1111-111111111111'::uuid) AND (npm_remote_repositories_1.id = npm_remote_repository_id))
                                                   Rows Removed by Filter: 40
                                                   Buffers: shared hit=6
 Planning:
   Buffers: shared hit=1501
 Planning Time: 2.630 ms
 Execution Time: 5.318 ms

Timings: planning 2.630 ms, execution 5.318 ms, total 7.948 ms.

Related to #515

This is a bot message 🤖 — /smurfit

Edited Aug 19, 2026 by Pawel Rozlach

chore(datastore): recompute repository size_bytes from source (S22 plan: 12/21)

What this step delivers

What this changes in production today: nothing yet, by design

Spec coverage

Acceptance criteria

Error cases

Security considerations

e2e scenario catalogs

Diff size

Dependencies: both merged, nothing left to sequence

Plan versus reality: the LOC estimate

Plan Status row 12

Query-plan pin

Lint

Checks that did not run

Database Review Evidence

Queries

ADR-007: the prune claim holds, and the index-only claim is reachable

Flag 1: the cost of a size walk follows the partition, not the repository (concern)

Flag 2: maven_files is the one collecting table with no repository-driven index path (concern)

Checks with nothing to report

Scenario A seed shape

Scenario A plans, one block per walk

Merge request reports

Flag 2: `maven_files` is the one collecting table with no repository-driven index path (concern)