chore(datastore): blob-footprint sum (S17 Phase 8 plan: 11/13)

Why

Phase 8 serves a version's byte footprint by deriving it at read time: collect the distinct blob digests the version's own file rows reach, then resolve each digest's size on S22's blob_storage_blobs_by_namespace shadow. Two walks will need that second half, the Maven version footprint and the npm one, and they land on different stores, so neither can own it. This lands the half they share ahead of either caller, so both are built against one execution seam.

What (the parts a reviewer would not guess)

The join is not new, and this MR does not write it again. The plan's Files entry says to create the distinct-digests-to-shadow-join sum. It already exists: reconcileBlobSizeStmt in internal/datastore/reconcile_repository.go, same shadow, same COALESCE(SUM(size), 0), same three alias constants, composed by six reconcile walk builders. The plan's near-miss survey named the npm and Maven version sums and missed this one, which is the closer match. So sumDistinctBlobSizes composes it and adds only the execution half those six each hand-roll: the guards, the instrumented call, the destination struct, and the wrap. A third copy of the join would have been the finding.

namespaceID is a parameter the plan's arg list did not have. The plan sketches four arguments. Without the namespace as a bound literal nothing prunes the shadow, and a digest two namespaces both hold is counted twice — so the sketch could not satisfy the step's own Acceptance line. the sum is scoped to the addressed namespace is the subtest that fails if the literal is dropped.

queryName is a parameter, not a constant, because the catalog assigns the name of a statement a shared helper executes to that helper's caller. This adds nothing to query_names.go, and the two consumers keep their own names without editing this file. The probes pass a name no catalog enumerates.

The unit-test file is untagged on purpose. .golangci.yaml sets no run.build-tags, so no linter compiles a //go:build integration file. With the integration probes invisible, unused reports the helper as dead. That file's calls are what keep it visible, which is why it is not folded into the integration suite.

The plan pin runs with enable_seqscan off, and the plan it pins is version-dependent. TestRecomputeContainerBlobsSizeStmt_PrunesEveryTableToOnePartition asserts the prune and declines the Index Only Scan, because at default GUCs a few-row partition plans as a sequential scan the planner is right to pick. This pin is that missing half, and it holds across CI's whole matrix for different reasons on each leg. Measured on postgres:{16,17,18}-alpine against structure.sql with CI's own service settings, growing the addressed namespace's shadow rows with the digest set held at two: PG18 nested-loops through the composite Index Cond and keeps the primary key past 1,000,002 rows; PG16 and PG17 merge-join and keep it to roughly 100 rows, then take a bitmap heap scan, and only reach the narrower (namespace_id) INCLUDE (size) index past about 1,000. That is why footprintDigestJoinRe carries four alternatives rather than asserting one shape, and the per-leg figures are on the test so a future red pin can be told apart from a grown fixture.

Two things measured on this branch, for whoever reads the plan next

The statement plans what ADR-007 measured, but this pin cannot show it. For the shape the version walks will issue — a version-scoped digest set from maven_files — the plan is Nested Loop over Index Only Scan using blob_storage_blobs_by_namespace_pNN_pkey, Index Cond: ((namespace_id = ...) AND (sha256 = maven_files.blob_sha256)), one partition, ~25 buffers, ~0.20 ms, holding at both 100k and 1M shadow rows in the namespace. On PG16 and PG17 the probe here cannot reach that shape, because its digest source is blob_storage_attachments, which is PARTITION BY HASH (sha256): the DISTINCT arrives as a 64-way Merge Append and the outer estimate floors at 64 however few digests exist, so a per-digest nested loop never wins. PG18 estimates the same subquery at 3 and reaches the composite Index Cond directly, which is the ADR-007 shape. So the pin does demonstrate the per-digest probe, on one of three legs, and the version walks' own pins are what will demonstrate it on all of them.

ADR-007 says two different things at two scopes, and the second one is not this MR's to settle. Its repository-level reconciliation section prescribes exactly this shape and is what reconcileBlobSizeStmt implements. Its artifact-level section reads blob_storage_blobs directly for a per-version walk and declines the shadow, on the grounds that such a walk holds its digests up front and can prune HASH(sha256) with sha256 = ANY(...). Both in-tree version walks follow the ADR today. The spec's version scope asserts the opposite premise. Measured against a namespace holding 100k to 1M shadow rows, for one six-digest version: the shadow join reaches one partition; the base-table shape reaches all 64 when the digests come from a subquery, and matches the shadow only when they are passed as a bound array. That last cost is uneven across the ADR's own two walks, so it is worth stating precisely rather than as a blanket claim — RecomputeVersionSize pays it per keyset page, and SumDistinctNpmFileSizesByVersion joins the base table in a single statement and pays nothing. Of the ADR's three grounds for declining the shadow, one is spent and one is live. Trigger-maintenance write amplification is sunk: S22 landed the shadow and its triggers, so a reader adds none. The stale-copy ground still stands, and it is the only one of the three about read correctness rather than write cost — reconcileBlobSizeStmt's own doc records that there is no AFTER UPDATE trigger, so an in-place blob_storage_blobs.size update leaves the shadow answering the pre-update value without erroring. Reconciling that with the ADR text belongs with the version walks and a handbook amendment, not with an unexported helper that has no caller.

Diff size

561 reviewable LOC, over the 500 threshold: 73 production, 488 test (348 integration, 140 unit). Every line over the ceiling is test. Splitting them off forfeits the test-first cycle, and splitting the two suites from each other leaves either a plan pin with no helper or a helper with no plan pin, neither reviewable alone. The 73 production lines carry five guards and one composed statement; the rest is seven largely parallel table-shaped subtests, three argument-guard tables, and one EXPLAIN pin.

Test plan

go test ./internal/datastore/
go test -tags=integration -count=1 -run 'TestSumDistinctBlobSizes' ./internal/datastore/
golangci-lint run ./internal/datastore/...
golangci-lint run --build-tags=integration --max-same-issues=0 --max-issues-per-linter=0 --uniq-by-line=false ./internal/datastore/...
bash scripts/ci/check-comment-caps.sh --base origin/main

Four test functions and twelve subtests, green in both builds. A bare testcontainer fails the namespace-delete cascade in the sum is scoped to the addressed namespace with out of shared memory (SQLSTATE 53200); that is the default max_locks_per_transaction=64, which .gitlab-ci.yml raises to 1024 for the integration services and documents as this exact symptom. Against a Postgres carrying that setting the suite is green. The integration-tagged lint pass reports only contextcheck, 19 findings from calling pre-existing helpers that take no ctx, against a package baseline of 4177 identical ones, unsuppressed everywhere.

Database Review Evidence

Queries

Note

Plans are from EXPLAIN (ANALYZE, BUFFERS) against an ephemeral PostgreSQL 17 container (matching GL_PG_CURR_VERSION from .gitlab-ci-other-versions.yml), with synthesized seed data rolled back per query and the container torn down at the end of the run. Numbers reflect moderate cardinality and do not capture production-scale effects. See Database review evidence for seed sizing, methodology, and the anomalies the skill flags. Expand each row's details for the seed shape, rendered SQL, bound args, and raw plan.

Method	Plan node	Index	Rows (plan / actual)	Cost	Time	Buffers (hit / read)	Partitions
datastore.sumDistinctBlobSizes	Aggregate	n/a	1 / 1	550.15	3.248ms	187 / 0	⚠️ 1/64 blob_storage_blobs_by_namespace, 64/64 blob_storage_attachments

Warning

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

• datastore.sumDistinctBlobSizes: 64/64 partitions of blob_storage_attachments. The fan-out is in the caller-supplied digests table, not in the shadow join this helper composes. blob_storage_attachments is PARTITION BY HASH (sha256), so its namespace_id predicate prunes nothing at any cardinality. The digest source here is the branch's own test probe; the two named future callers read maven_files and npm_files, both PARTITION BY HASH (namespace_id), which do prune. The shadow half this MR owns pruned to 1 of 64.

datastore.sumDistinctBlobSizes

Summary: The plan matches the method's intent on the half it owns. The namespace_id literal prunes the shadow to one of 64 partitions and the COALESCE(SUM(size), 0) answers 17622500, the addressed namespace's bytes only, with the second namespace's 5000 rows excluded. Two anomalies, both in the seed rather than the statement: the digest source fans out over all 64 blob_storage_attachments partitions because that table hashes on sha256, and the pruned shadow partition is read by Seq Scan because the seed's 5000-digest set needs every row in it.

Seed shape: namespaces=2, blob_storage_blobs=10000, blob_storage_blobs_by_namespace=10000 (trigger-populated), blob_storage_attachments=10000

Rendered SQL:

SELECT COALESCE(SUM(blob_storage_blobs_by_namespace.size), $1) AS "total"
FROM (
          SELECT DISTINCT blob_storage_attachments.sha256 AS "blob_sha256"
          FROM public.blob_storage_attachments
          WHERE blob_storage_attachments.namespace_id = $2::uuid
     ) AS uniq_blobs
     INNER JOIN public.blob_storage_blobs_by_namespace ON ((blob_storage_blobs_by_namespace.namespace_id = $3::uuid) AND (blob_storage_blobs_by_namespace.sha256 = uniq_blobs.blob_sha256));

Bound args: [0, 48eb9957-5ab6-41f5-9d61-8b5f852fb2de, 48eb9957-5ab6-41f5-9d61-8b5f852fb2de]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Aggregate  (cost=550.14..550.15 rows=1 width=32) (actual time=3.243..3.248 rows=1 loops=1)
   Buffers: shared hit=187
   ->  Hash Join  (cost=405.00..537.64 rows=5000 width=8) (actual time=2.193..3.079 rows=5000 loops=1)
         Hash Cond: (blob_storage_blobs_by_namespace.sha256 = blob_storage_attachments.sha256)
         Buffers: shared hit=187
         ->  Seq Scan on blob_storage_blobs_by_namespace_p52 blob_storage_blobs_by_namespace  (cost=0.00..119.50 rows=5000 width=41) (actual time=0.004..0.308 rows=5000 loops=1)
               Filter: (namespace_id = '48eb9957-5ab6-41f5-9d61-8b5f852fb2de'::uuid)
               Buffers: shared hit=57
         ->  Hash  (cost=342.50..342.50 rows=5000 width=33) (actual time=2.166..2.171 rows=5000 loops=1)
               Buckets: 8192  Batches: 1  Memory Usage: 382kB
               Buffers: shared hit=130
               ->  HashAggregate  (cost=292.50..342.50 rows=5000 width=33) (actual time=1.399..1.669 rows=5000 loops=1)
                     Group Key: blob_storage_attachments.sha256
                     Batches: 1  Memory Usage: 721kB
                     Buffers: shared hit=130
                     ->  Append  (cost=0.00..280.00 rows=5000 width=33) (actual time=0.002..0.732 rows=5000 loops=1)
                           Buffers: shared hit=130
                           ->  Seq Scan on blob_storage_attachments_p00 blob_storage_attachments_1  (cost=0.00..4.18 rows=86 width=33) (actual time=0.002..0.009 rows=86 loops=1)
                                 Filter: (namespace_id = '48eb9957-5ab6-41f5-9d61-8b5f852fb2de'::uuid)
                                 Rows Removed by Filter: 88
                                 Buffers: shared hit=2
                           ->  Seq Scan on blob_storage_attachments_p01 blob_storage_attachments_2  (cost=0.00..4.05 rows=79 width=33) (actual time=0.002..0.008 rows=79 loops=1)
                                 Filter: (namespace_id = '48eb9957-5ab6-41f5-9d61-8b5f852fb2de'::uuid)
                                 Rows Removed by Filter: 85
                                 Buffers: shared hit=2
                           [62 further Seq Scan nodes, one per remaining blob_storage_attachments partition p02..p63, identical in shape: 58-101 rows each, 59-99 removed by the same namespace_id filter, 2-3 buffers each]
 Planning Time: 0.987 ms
 Execution Time: 3.536 ms

Timings: planning 0.987ms, execution 3.536ms, total 4.523ms.

Related to #316