chore(datastore): the windowed child-edge read over container_manifest_relationships (S17 Container Manifest Reads plan: 17/20)

Why

Step 17 of the S17 Container Manifest Reads plan adds the batched child-edge read behind manifest detail's children array and the manifests list's children_preview and children_count. One windowed statement serves both shapes, so an index's child count and the entries beside it come from the same read and agree within one response (plan D3). Nothing calls it until Step 18 serves those fields, the interval the plan's Accepted code smells entry accepts, so this step's own tests are the caller meanwhile.

What is not obvious

The child manifest joins inside the derived table. The preview orders by the child's digest, so the window needs every matched edge's digest before it can pick ten. That join is also where the platform triple comes from.

The ordering carries a trailing child-id key. Nothing ties an edge's container_image_id to its parent manifest's, and (namespace_id, container_image_id, digest) is unique per image, so one parent can hold two edges naming equal-digest children under two images. Digest order alone is not total there.

An image equality on the join was tried and reverted (06c936073, then e0386dfe7). It closes one divergence shape and serves the other, no production writer creates either, and it costs the relationship side its index-only path. The full argument is recorded below for agents. The enforceable form is a composite foreign key, so it is a follow-up rather than a read-path patch, and two tests record the gap.

internal/managementapi/list.go is outside the step's declared file list. maxPageSize's comment claimed six unexported datastore copies were the whole set and this step adds a seventh, so the drift guard's failure message named an incomplete list. Steps 8 and 9 each made the same registration, and the enumeration now lives once, in the test that already carried it.

Measurement

AC #139 (closed)'s second paragraph and plan D4 put this figure here rather than in a test. EXPLAIN (ANALYZE, BUFFERS) on PostgreSQL 17.10 under CI's .pg-service-options flags, schema from structure.sql, seeded as committed data with VACUUM (ANALYZE), one namespace, 100 index manifests at container.manifest_max_references (200) each: 20,000 edges over 20,000 distinct child manifests. Worst of three on an otherwise idle host, both insert orders the fixture admits. Which arrival order reads faster reverses between measurement rounds, so the spread is host load rather than arrival order, and the figures below are the interleaved arm.

Shape	Platform triple at its 255-byte clamp	Runtime image-config values
Page preview, 100 index ids, ten each	52 ms	38 ms
Manifest detail, one index at the 200 cap	2 ms	5 ms

Both shapes a caller issues clear the 100 ms budget, so D4's counter fallback is not reached. Complete over a full 100-id batch is no caller's shape, since D3 makes Complete the detail read, which passes one id. Measured anyway it reaches 62 ms at the clamp.

The cost is the ordering, not the scan: at the clamp the sort spills to disk (external merge, 9 MB against the default 4 MB work_mem, which .pg-service-options does not set), where at runtime widths it stays an in-memory quicksort. Projecting the platform triple in the outer SELECT rather than the window would cut that payload about tenfold. The runtime-width column prices the prize, since narrow triple values are what a smaller payload looks like, but the rewrite adds a second join on container_manifests, buys the Complete arm nothing, and the shipped shape already clears the budget.

At this ceiling the page asks for every index in the namespace, so the planner reaches the rows by sequential scan and hash join rather than the index the EXPLAIN pin asserts. That partition-share effect is why AC #139 (closed) needs two fixtures: the pin runs on 40 indexes with four asked for, under the share threshold.

Spec coverage

#	Criterion	Covered by
AC #132 (closed)	One entry per child, each {digest, architecture, os, os_variant}	TestListChildrenByParentIDsStmt (both shapes), .../Complete_returns_the_whole_array_with_the_same_total
AC #132 (closed)	Child-digest-byte ascending order	TestListChildrenByParentIDsStmt, .../a_preview_returns_at_most_the_limit_per_parent, ..._EqualDigestsBreakOnTheChildID
AC #132 (closed)	The count is the whole array's length beside a truncated preview	.../a_preview_returns_at_most_the_limit_per_parent, .../Complete_returns_the_whole_array_with_the_same_total
AC #132 (closed)	The preview is the first ten entries in the same order	.../a_preview_limit_past_a_parent's_child_count_returns_every_child
AC #132 (closed)	A cap-shaped index: ten previewed, 200 whole, 200 in both counts	..._ReferenceCapShapedIndex
AC #132 (closed)	An image manifest and an index with an empty manifests[] both read [] and 0	.../an_image_manifest_and_an_empty_index_are_both_absent_from_the_map, .../an_id_set_matching_no_row_returns_an_empty_map
AC #131 (closed)	The platform triple is null per value, not as a unit	.../the_platform_triple_round-trips_per_value,_not_as_a_unit
AC #138 (closed)	One statement per family, whatever the page size	TestListChildrenByParentIDsStmt: one builder, one call site, one query name. The page-level count is Step 18's
AC #139 (closed)	Rides unique_cmr_ns_id_parent_cm_id_child_cm_id by its truncated child-index suffix, binds both key columns, scans no partition	TestListChildrenByParentIDsStmt_RidesTheRelationshipIndex
AC #139 (closed)	The 100 ms figure at the 20,000-edge ceiling	The measurement above, and one row of docs/dev/database-query-patterns.md's "Measured at the ceiling" table
D3	The zero value and the two-shapes call reject before any SQL runs	..._ArgumentGuards, ten rows, Complete beside a negative limit included
Store	Namespace isolation, the batch limit from both sides, the error wrap	..._NamespaceIsolation, ..._BatchLimitBoundary, .../a_failed_read_wraps_its_cause_with_no_scope_identifier_in_the_text

The list-versus-detail key split and the serializer arms are Step 18's.

Reviewable LOC

1,220 added and 6 removed, past guardrail 18's 500: 216 internal/datastore source, 3 internal/managementapi source, 1,000 test, 1 doc. The read, its unit tests, and its integration suite each land in a file of their own, so the step leaves container_manifest_relationship.go and both of its existing suites untouched.

Splitting would not help: the source half is one statement builder, one method, and one query name with no seam between them, and guardrail 6 asks the suite to be diffed against the mirrored tag-read suite, which a split defeats. The test half is 82% of the diff and runs 3.8x the plan's ~260 forecast because the order key is the child's digest. The tag mirror batch-seeds names, while every case here needs a manifest seeded with a chosen digest and platform triple, each carrying its own blob and attachment row to satisfy the composite foreign key. Ten fixture helpers carry that.

Test plan

mise exec -- env -u GOROOT go test ./internal/datastore/ ./internal/managementapi/ -count=1
ARTIFACT_REGISTRY_DATABASE_TEST_DSN="postgres://...@127.0.0.1:PORT/db?sslmode=disable" \
  mise exec -- env -u GOROOT go test -tags=integration ./internal/datastore/ \
  -run 'ChildrenByParentIDs|RidesTheRelationshipIndex|ReferenceCapShapedIndex|EqualDigests' -count=1
mise exec -- env -u GOROOT golangci-lint run --build-tags=integration \
  --max-same-issues=0 --max-issues-per-linter=0 --uniq-by-line=false \
  --new-from-rev "$(git merge-base HEAD origin/main)" ./internal/datastore/... ./internal/managementapi/...

The integration Postgres needs max_locks_per_transaction raised the way CI's service sets it, because at the stock 64 this package's cascade cleanups fail with SQLSTATE 53200. The last command is the achievable criterion for a build-tagged package CI lint never compiles: zero new findings against about 5,600 pre-existing ones. It reads the merge base rather than a pinned rev, which goes stale on every rebase.

Database Review Evidence

Query mode only. This step adds one query-producing method and no schema change.

Migrations

None. No migration, no index, and no column. The read rides unique_cmr_ns_id_parent_cm_id_child_cm_id, which 20260526120400_oci_container_manifest_relationships.sql created.

Queries

Note

Plans are EXPLAIN (ANALYZE, BUFFERS) from the run behind the Measurement section above: PostgreSQL 17.10 under CI's .pg-service-options flags, schema from structure.sql, the 20,000-edge ceiling fixture (100 indexes at container.manifest_max_references) seeded as committed data and VACUUM (ANALYZE)d, one namespace, no sibling, worst of three runs. Each row is one of the two call shapes the argument guards admit, on the interleaved-arrival arm at the 255-byte platform-value clamp, and each row's figures come from that arm's own worst run rather than across runs. Numbers do not capture production-scale effects. See Database review evidence for methodology.

Method	Plan node	Index	Rows (plan / actual)	Cost	Time	Buffers (hit / read)	Partitions
datastore.ListChildrenByParentIDs, preview, 100 ids at ten each	Subquery Scan over two WindowAggs, Gather Merge, Sort, Parallel Hash Join	none at this ceiling, a Parallel Seq Scan per side	20000 / 1000	12744.91	52.1ms	2864 / 0, plus 2101 temp blocks read and 2105 written	1/64 on each table
datastore.ListChildrenByParentIDs, Complete, one index at the 200 cap	Sort over WindowAgg, Nested Loop	container_manifest_relations_namespace_id_parent_containe_idx32 by Index Only Scan, then container_manifests_p32_pkey once per edge	200 / 200	1643.43	1.8ms	811 / 0	1/64 on each table

Query notes:

• Both shapes clear the 100 ms budget, and the preview's margin is host load rather than headroom. The interleaved arm reads 49.9, 51.8, and 52.1 ms. The grouped arm of the same round, on an identical plan with identical buffers and the same temp-block counts, reads 88.4, 104.7, and 93.0 ms, and the round before this one inverts the pair (interleaved 88.4 to 109.6 ms, grouped 51.6 to 56.6 ms). Arrival order is not what moves it, and one grouped run crossing 100 ms is what this ceiling's margin looks like on a loaded host.
• The preview's cost is the ordering, and at the clamp it spills. Sort Method: external merge takes 8,480 to 8,976 kB in the leader and 7,824 to 8,320 kB in its one worker, against the default 4 MB work_mem the CI flags do not set. The runtime-width arm sorts in memory instead (quicksort Memory: 2644kB), reads 38.0 ms at its worst, and drops from 2,864 shared hits plus about 2,100 temp blocks to 864 shared hits and no temp at all.
• The preview's row estimate runs 20x high, 20,000 planned against 1,000 actual. COUNT(*) OVER (PARTITION BY parent_container_manifest_id) needs every edge of every parent asked for, so the row number's Run Condition cannot end the scan: the inner WindowAgg reports rows=20000 and the outer one removes 19,000 by filter. Harmless for a standalone read, and it would misprice a join that nested this one.
• At this ceiling the planner leaves the index, which is expected rather than a regression. A page asking for all 100 indexes in the namespace selects the whole partition, so both sides come back as Parallel Seq Scans under a Parallel Hash Join. The index-backed shape is the detail read's, above: an Index Only Scan whose Index Cond binds namespace_id and parent_container_manifest_id together rather than filtering the second in the heap. TestListChildrenByParentIDsStmt_RidesTheRelationshipIndex pins that shape on a 40-parent fixture with four asked for, with enable_seqscan=off so a small fixture cannot hide behind a Seq Scan.
• The partition child's index name is the auto-truncated spelling, measured rather than derived. unique_cmr_ns_id_parent_cm_id_child_cm_id's child on container_manifest_relationships_p32 prints as container_manifest_relations_namespace_id_parent_containe_idx32: PostgreSQL truncates the table prefix, drops the _pNN entirely, cuts parent_container_manifest_id to parent_containe, and appends an ordinal. The pin matches that family by pattern for the same reason.
• Both namespace predicates prune to one partition of 64 on each table. Every capture reaches container_manifest_relationships_p32 and container_manifests_p32 and nothing else, which is why the join repeats namespace_id rather than joining on the child id alone. assertSinglePartition on both tables is the falsifier.
• Row width, not row count, picks the detail read's join. At the clamp the planner takes the Nested Loop above, 200 pkey probes at 1.8 ms. At runtime widths it hash-joins the partition's 20,100 manifest rows and reads 5.2 ms at its worst, which is why the detail shape's clamp arm is the faster of the two. Neither shape is pinned: plan D3 prices a probe per matched edge, and the pin asserts a join node in any spelling plus the columns it binds.
• The index-only scan pays a heap visit per edge on this fixture. Heap Fetches: 200 on both detail arms, entirely from cache (811 shared hits, zero reads), because the fixture's pages are not all-visible to the reading snapshot. Production reads the same way until autovacuum reaches the partition, and the figure above already carries that cost.
• Complete over a full 100-id batch is no caller's shape, since D3 makes Complete the detail read, which passes one id. Measured anyway it plans an Incremental Sort over 20,000 rows and reads 61.7 ms at the clamp, still inside the budget.

Also checked:

• Namespace isolation. A second namespace holding its own graph returns none of the first's children: TestContainerManifestRelationshipStore_ListChildrenByParentIDs_NamespaceIsolation.
• The batch bound from both sides. ..._BatchLimitBoundary serves an id set at containerManifestRelationshipParentIDsBatchLimit and rejects one past it before any SQL runs.
• The three empty readings. An image manifest, an index with an empty manifests[], and an id set matching no row are all absent from the map rather than returning a zero-value entry.
• The ordering's trailing key. ..._EqualDigestsBreakOnTheChildID seeds two children of one parent with equal digests under two images, the one shape where the child-id tie-break decides which entry a preview keeps.

Context for LLM agents

The reverted image equality, in full. 06c936073 added child.container_image_id = cmr.container_image_id to the join after a review reproduced a cross-image child being returned and counted. e0386dfe7 removed it, on five grounds:

1. It half-closes. The edge carries its own container_image_id, and nothing ties it to the parent manifest's, so the predicate refuses a child outside the edge's image and serves an edge sitting outside the parent's. The served shape was reproduced: four children and a total of four for a three-child parent.
2. No production writer creates either shape. insertRelationships (container_manifest_persister.go:580) stamps the edge with the parent's image and resolves children inside it, Create has no production caller, nothing updates either image column, and no backfill writes these rows.
3. It costs the relationship side its index-only path, container_image_id sitting in no index that leads with parent_container_manifest_id.
4. The spec states image scoping for the referrers list and leaves children silent.
5. The sibling reads, the tag mirror and ListParentDigestsByChild, join without an image term, and ADR-007 names this access path with no image term.

The enforceable form is a composite foreign key on (namespace_id, container_image_id, child_container_manifest_id) with a parent twin, which needs a new unique index and a migration. Two tests record the gap rather than assert it away, and the follow-up is tracked in Tie a container_manifest_relationships edge to ... (#1339) • Hayley Swimelar • 19.5.

Other design rationale.

• The window rather than fetch-and-slice in Go. The rejected shape has an in-tree precedent in NpmDistTagNamesByVersionIDs, but plan D3 rejects it for the parents family, whose array no per-manifest cap bounds, and two shapes for one job is worse than the window here too.
• The EXPLAIN pin asserts a join node in any spelling plus the columns it binds, not a Nested Loop. The plan's Step 17 acceptance asks it to name "the container_manifests probe per matched edge", which is D3's cost model rather than a plan node: measured at three fixture scales the planner produces a hash join, a merge join, and a per-edge nested loop. The pin asserts nothing about Sort either, because no index carries the child digest under a parent-id prefix (plan D4).
• ManifestChildEdges rather than ManifestChildren, despite the asymmetry with ManifestTags: the plan declares the *Edges name, so the parents direction should follow it.

Non-goals.

• No production caller. Step 18 serves the fields.
• No index and no migration. The spec's Fetch mechanism names unique_cmr_ns_id_parent_cm_id_child_cm_id as this read's path, so ADR-007 is owed nothing, and the covering-index question belongs to the spec owner.
• No configuration knob. PreviewLimit and Complete are exclusive call shapes, and passing container.manifest_max_references would truncate an array the contract calls complete.
• No deadline on the read. Plan D4 records that no read path in internal/datastore or internal/managementapi arms one, and declines to make this the first.

🤖 Generated with Claude Code

Related to #1150 (closed)