Artifact Registry ADR 007: correct the repository-level reconciliation cost and index claims

What

This merge request makes two corrections to ADR-007. Both rest on EXPLAIN (ANALYZE, BUFFERS) measurement.

The first correction is the cost paragraph in #### Repository-level storage accounting reconciliation, and one phrase two lines below it.

Two of that paragraph's claims are false.

1. Cost. The paragraph says reconciliation cost scales with the repository's artifact count, not the namespace's. Measurement shows that the cost follows the partition.
2. Indexes. The paragraph says the format-specific indexes on those tables are partial on soft_deleted_at IS NULL. That holds for four of the ten tables.

The amendment keeps the paragraph's true clause. Partition pruning holds, and each side of the walk reaches exactly one of 64 partitions.

The phrase "for very large repositories" also goes. It presupposes the same repository-scoped model, two lines below the paragraph that states it.

The second correction is the third bullet under ### Format-specific table partitioning strategy. The bullet says that joins to blob_storage_blobs do not cross-partition scan, because the planner prunes each side independently. The second half of that reason is false for a join that names no digest.

blob_storage_blobs is PARTITION BY HASH (sha256). A column-to-column predicate such as blob_storage_blobs.sha256 = maven_files.blob_sha256 binds no digest at plan time. The planner therefore builds an Append over all 64 partitions and prunes per outer row at execution. The scan is avoided at run time, not at plan time, and the cost lands in planning.

The two corrections agree with each other. The walk in the first correction reaches one partition per side, because it reads the blob_storage_blobs_by_namespace shadow under a namespace_id literal. The join in the second correction reads the base table, and it holds no digest literal to prune that table.

Evidence for the first correction

The evidence is eleven EXPLAIN (ANALYZE, BUFFERS) plans on PostgreSQL 17.10, over the six repository-level recompute statements. The measurements come from the repository-level reconciliation work in artifact-registry!1712.

• The same 200-digest repository answers in 0.508 ms in one fixture and 18.157 ms in another. That is a 36-fold difference, with the repository's artifact count held constant. The second fixture adds a sibling repository of the same namespace, with 59,800 rows in the same partition.
• The cause is a row estimate, not a missing index. The repository predicate sits on the format-specific stub table, two join levels above the blob-bearing table. PostgreSQL keeps no cross-table correlation statistics for that path, so it plans 30,000 rows where 200 exist.
• The node is a Seq Scan with Filter: (namespace_id = ...), not an index condition.
• Every one of the eleven plans reached exactly one partition per table. The branch also commits a test that pins the prune.
• A repository-proportional cost stays reachable. The 0.508 ms plan is a nested loop with an Index Only Scan on blob_storage_blobs_by_namespace. It depends on a row estimate that the planner does not produce for this join shape today.

The new text marks one figure as inferred. A hash partition holds rows from about 1/64 of all namespaces. The fixture seeded one namespace, so Rows Removed by Filter was zero. The 1/64 figure follows from the node type and from HASH(namespace_id) over 64 partitions. No fixture measured it.

The index premise, checked against internal/datastore/migrations/structure.sql:

Tables	Predicate on the unique index
container_blobs, container_manifests, npm_metadata_files, npm_remote_metadata_files	None. These four tables carry no soft_deleted_at column.
npm_files, npm_remote_files, container_remote_blobs, container_remote_manifests	soft_deleted_at IS NULL, exactly as the paragraph states.
maven_files, maven_remote_files	soft_deleted_at IS NULL, and a second conjunct on the version id.

Evidence for the second correction

The evidence is an EXPLAIN (ANALYZE, BUFFERS) comparison on PostgreSQL 17.10. The fixture holds one namespace and 5,000 rows each in blob_storage_blobs, blob_storage_attachments, and maven_files. Both plans read one maven_files row and its blob's size. Each figure is the range over five repeats in one session.

Table joined for the blob's size	Plan for the blob side	Planning	Execution
blob_storage_blobs	Append over 64 partitions, 63 of them never executed	3.85-4.70 ms	0.45-0.72 ms
blob_storage_blobs_by_namespace	Single-partition Index Only Scan, Heap Fetches: 0	0.26-0.37 ms	0.11-0.17 ms

• The first plan for the base table in a fresh backend costs 22.07 ms of planning and reads 5,738 planning buffers.
• The difference is planning, not execution. Run-time pruning reaches one partition either way. The planner opens the other 63 partitions, and that is the cost.
• The fan-out is structural, not a property of the fixture. It follows from HASH(sha256) over 64 partitions and from the absence of a digest at plan time.
• The unique constraint on blob_storage_blobs [namespace_id, sha256] does not close the gap. An index on a partitioned table is local to each partition. It makes a partition cheaper to open, and it does not change the size of the Append.

Why this is a correction, not a deviation

The Artifact Registry change that produced these plans follows ADR-007. It implements the walk shape, the DISTINCT-before-join order, and the shadow join that ADR-007 prescribes. The code does not deviate from the decision.

The escalation guardrail in the Artifact Registry repository triggers on a change that deviates from an ADR. That trigger is unmet here. The decision is wrong about the world, and this merge request corrects the record.

The second correction has the same standing. The behavior it describes predates every unmerged change in this area. Both callers of the Maven file-to-blob join on the Artifact Registry default branch already have it, and the helper's own doc comment records it. This merge request reports no regression.

Scope

• The remedy in the "cheapest next step" sentence keeps its shape, a non-partial index that leads with (namespace_id, <its parent id>), and now names the one table that lacks it. Nine of the ten tables already carry such an index, read per table from internal/datastore/migrations/structure.sql with each table's _p00 partition index set diffed against the parent set. maven_files is the exception, because its only maven_package_id-led index is partial on soft_deleted_at IS NULL AND maven_version_id IS NULL. artifact-registry#684 proposes the index for that table, as a proposal rather than a decision, and this merge request does not decide the column shape. A different remedy is a design decision, and this merge request does not make one.
• Line 2074, in the count-walk subsection above, carries the same index premise. It is false for container_manifests only. This run measured the size walk, not the count walk, so that line stays as it is. This note records the choice, so the next reader can tell it from an oversight.
• Six lines in this file rest on a soft_deleted_at column on container_blobs and on container_manifests, and the implemented schema has neither column. Lines 439 and 454 list the column in the two ER diagrams. Lines 477 and 478 record it in the two table descriptions. Lines 489 and 490 specify a unique index on (namespace_id, container_image_id, digest) WHERE soft_deleted_at IS NULL for the same two tables, and such an index cannot exist without the column. Four query examples also name the column on these tables, at 514, 526, 546 and 554. Merged specs settle which side is right. docs/specs/S20-a-lifecycle-closed-beta.md:343 states that ADR-007 specifies the column on container_images, container_blobs and container_manifests, and line 361 of the same file states that GA adds them. ADR-010, under ### Release Phasing, states that soft delete ships at GA. The document is therefore ahead of the schema rather than wrong about it, and all six lines stay as they are. The paragraph this merge request rewrites now says the column arrives at GA, instead of stating that the column does not exist.
• The second correction records the measurement and corrects the bullet. It proposes no index, no schema change, and no different table for any reader. The first bullet in the same list makes two claims about the format table: HASH(namespace_id) prunes it for every operation, and the read-path shortcut benefits from that partitioning. Both claims are about the format-table side, which still prunes at plan time, so that bullet stays as it is.

Why a second open merge request on this file is safe

!20824 (merged) is the only other open merge request on this file. Its nearest hunk starts at line 2119, 25 lines below the text this merge request changes, and GitLab reports has_conflicts=false. The project merge method is merge, so neither merge order needs a rebase.

The second correction sits at line 1773, further from !20824 (merged) than the first. GitLab still reports has_conflicts=false after it.

This is a bot message 🤖 — /smurfit