feat(datastore): batched hosted upstream lookup (S32 plan: 5/19)

Radamanthus Batnagrequested to merge
rbatnag/s32-container-virtual-step-5 into main

What

The batched authoritative reads a hosted position answers Phase 1 from, and the remote.Upstream that maps their rows onto remote.LookupResult.

One statement per reference shape covers every hosted position in the upstream list: the tag read over container_tags, the manifest-by-digest read over container_manifests, and the blob-by-digest read over container_blobs. A hosted read is authoritative, so every Hit is Fresh with an empty ETag and no hosted position enters Phase 2. A miss is the zero-value result, which the resolver reads as an established absence.

A manifest-backed hit also carries container_manifests.media_type. A manifest read serves that column as its Content-Type and negotiates a client's Accept against it, and remote.LookupResult declares no field for it. The remote arm already answers this the same way: ContainerRemoteCacheRow carries the format-agnostic result plus the container columns that type has no field for. The blob read projects none, because container_blobs has no such column.

ContainerVirtualHostedLookupStore's doc comment names the three repositories checks its caller owns: liveness, kind, and format. All three sit outside this read, which reaches the leaf tables through container_images and never touches repositories. The format check is the one worth naming, because a wrong-format position fails open — its lookup against the container tables misses by construction, a hosted miss establishes an absence, and every pull then answers a 404 that a Docker client treats as permanent.

Where the media type stops

VirtualHostedUpstreams.Lookup returns map[uuid.UUID]remote.LookupResult, so the media type reaches the datastore hit and no further. Step 10b assembles the upstream list in internal/format/oci, the adapter's own package, so it reads the hit directly rather than through remote.LookupResult. While that step is unwritten no caller consumes the column; once it lands, it hands the value to Step 12's content negotiation.

Projecting the column here rather than re-reading it later costs one column on a statement that already joined the row. The later read costs a statement.

Why the reads carry their own soft-delete predicate

Each read filters container_images.soft_deleted_at IS NULL itself. Every hosted container read on main filters the image, but the by-digest reads inherit it from a caller that has already resolved a live image. This step goes from repositories.id straight to the three leaf tables, so it skips that resolution and adds the predicate.

Without it a docker pull through a virtual repository succeeds on an image the hosted repository answers 404 for, and a warm digest read then serves it indefinitely. The direction is a miss, not an error: a soft-deleted image is an absence, so the 404 is the correct answer rather than a fail-open.

Spec line 269's lookup paragraph omits the predicate. The plan adds it deliberately and records the correction in its ## Research Findings.

Size

3,176 reviewable LOC, past the 500 the development model asks about. Every figure below is git diff main...HEAD --numstat, additions plus deletions, at 8405fac60. Splitting does not help: the three reads share one image predicate and one row-to-hit mapping per shape, and the suites assert the statement count across all three shapes together, so a split would either duplicate the fixture or leave a read untested until the second MR.

File group LOC
internal/datastore/container_virtual_hosted_lookup.go 385
internal/format/oci/virtual_hosted_lookup.go 308
internal/datastore/query_names.go 24
Source total 717
internal/datastore/container_virtual_hosted_lookup_integration_test.go 976
internal/format/oci/virtual_hosted_lookup_test.go 734
internal/datastore/container_virtual_hosted_lookup_test.go 288
internal/datastore/container_virtual_hosted_lookup_guards_test.go 234
internal/datastore/container_virtual_hosted_lookup_explain_integration_test.go 227
Test total 2,459

Test outweighs source roughly 3.4 to 1. Two suites drive it. The integration tests run every assertion once per reference shape, and the _explain suite asserts one partition per table, because a statement count cannot see an Append over 64 partitions.

Testing

Unit tests cover the row-to-LookupResult mapping, the argument guards, every zero value, and the rendered SQL per shape. Integration tests seed hosted repositories and assert one statement at both 2 and 20 positions, a soft-deleted image as a miss per shape, and a positive hit for both docker and oci.

The media-type projection is asserted on both manifest-backed statements and denied on the blob statement, and the integration hits assert the stored value per shape, empty on the blob shape. Dropping the mapping line fails the tag and manifest hits, which is how the assertion was confirmed to have teeth rather than to restate the code.

The suite was diffed against the merged internal/datastore/npm_virtual_hosted_lookup* files for dropped subtests, per guardrail 6. Two npm predicates had no counterpart and now do: an untagged manifest resolving on the by-digest read, and a duplicate hit for one position, which the map write would otherwise resolve by order.

digestSum's VirtualHostedReferenceInvalid arm now has a case that reaches it. The rejection table's bare zero value exited on the earlier ImageName check, and both arms return ErrVirtualHostedReference, so the ErrorIs assertion passed while the case name claimed coverage it did not give.

Database Review Evidence

Queries

Note

Plans are from EXPLAIN (ANALYZE, BUFFERS) against an ephemeral PostgreSQL 17.10 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.ContainerVirtualHostedBlobLookup Nested Loop index_container_blobs_on_namespace_id_and_digest 3 / 3 53.30 0.034ms 19 / 0 1/64, 1/64, 1/64
datastore.ContainerVirtualHostedManifestLookup Nested Loop unique_container_repositories_namespace_id_and_repository_id 3 / 3 66.66 0.036ms 28 / 0 1/64, 1/64, 1/64
datastore.ContainerVirtualHostedTagLookup Nested Loop unique_container_repositories_namespace_id_and_repository_id 1 / 3 91.62 0.047ms 37 / 0 1/64, 1/64, 1/64, 1/64

Query notes:

  • datastore.ContainerVirtualHostedBlobLookup: the driving scan is index_container_blobs_on_namespace_id_and_digest, keyed on (namespace_id, digest) alone. The position list reaches the plan later, as a Materialize under a Join Filter, so that scan's row count tracks how many images in the namespace hold the digest rather than len(repositoryIDs). A base layer shared across a large namespace widens it, and digest is near-unique, so the planner predicts about one row whatever the real count is and the prediction does not improve with more statistics.

    Accepted for this step, and tracked in #1031. Nothing reaches this read yet, so it costs nothing until the step that assembles the hosted upstream list merges; the issue says to close it before that step or accept it explicitly there. It also records the three formulations measured and rejected — a MATERIALIZED CTE, the digest carried through that CTE, and JOIN LATERAL (... LIMIT 1) — none of which held the image-keyed index, because MATERIALIZED is an evaluation barrier and not a join-order barrier. An earlier revision of this branch shipped the CTE and this description claimed the planner would switch "once statistics favour it"; both were wrong and both are reverted.

    The two manifest-backed reads need no fence and have no such anomaly: every digest- or name-bearing index on container_manifests and container_tags leads with container_image_id after namespace_id, so the planner has no wrong entry point to choose.

datastore.ContainerVirtualHostedBlobLookup

Summary: The plan matches the method's intent and prunes all three partitioned tables to one hash partition each. The planner enters through container_blobs on (namespace_id, digest), joins up to container_images through pk_container_images, and applies the position list last. The entry point is the one anomaly, recorded in the Query notes above; the soft-delete predicate still holds, as a Filter on the container_images scan.

Seed shape: namespaces=1, blob_storage_blobs=1, blob_storage_attachments=1, repositories=5003, container_repositories=5003, container_images=5003, container_blobs=15000

Rendered SQL:

SELECT container_repositories.repository_id AS "container_repositories.repository_id",
     container_blobs.blob_sha256 AS "container_blobs.blob_sha256"
FROM public.container_repositories
     INNER JOIN public.container_images ON ((container_images.namespace_id = container_repositories.namespace_id) AND (container_images.container_repository_id = container_repositories.id))
     INNER JOIN public.container_blobs ON ((container_blobs.namespace_id = container_images.namespace_id) AND (container_blobs.container_image_id = container_images.id))
WHERE ((((((container_repositories.namespace_id = $1::uuid) AND (container_repositories.repository_id IN ($2::uuid, $3::uuid, $4::uuid))) AND (container_images.namespace_id = $5::uuid)) AND (container_images.name = $6::text)) AND (container_images.soft_deleted_at IS NULL)) AND (container_blobs.namespace_id = $7::uuid)) AND (container_blobs.digest = $8::bytea);

Bound args: [29fe9b00-4072-4ccb-9f51-c6035dfae2dd, fb3e9d9c-aafe-452f-825b-7eacd873039f, 8e4930fe-44cf-4a1c-b233-6c9dfb52f890, 803e7f28-f7fa-485a-bdbd-2d08a7b0c3f2, 29fe9b00-4072-4ccb-9f51-c6035dfae2dd, review-prep-image, 29fe9b00-4072-4ccb-9f51-c6035dfae2dd, \x00000000000000000000000000000000000000000000000000000000000009c4]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Nested Loop  (cost=0.85..53.30 rows=3 width=49) (actual time=0.029..0.034 rows=3 loops=1)
  Join Filter: (container_repositories.id = container_images.container_repository_id)
  Rows Removed by Join Filter: 3
  Buffers: shared hit=19
  ->  Nested Loop  (cost=0.57..32.77 rows=3 width=65) (actual time=0.016..0.021 rows=3 loops=1)
        Buffers: shared hit=12
        ->  Index Scan using container_blobs_p51_namespace_id_digest_idx on container_blobs_p51 container_blobs  (cost=0.29..11.85 rows=3 width=65) (actual time=0.008..0.009 rows=3 loops=1)
              Index Cond: ((namespace_id = '29fe9b00-4072-4ccb-9f51-c6035dfae2dd'::uuid) AND (digest = '\x00000000000000000000000000000000000000000000000000000000000009c4'::bytea))
              Buffers: shared hit=3
        ->  Index Scan using container_images_p51_pkey on container_images_p51 container_images  (cost=0.28..8.30 rows=1 width=48) (actual time=0.003..0.003 rows=1 loops=3)
              Index Cond: ((id = container_blobs.container_image_id) AND (namespace_id = '29fe9b00-4072-4ccb-9f51-c6035dfae2dd'::uuid))
              Filter: ((soft_deleted_at IS NULL) AND (name = 'review-prep-image'::text))
              Buffers: shared hit=9
  ->  Materialize  (cost=0.28..20.42 rows=3 width=48) (actual time=0.003..0.004 rows=2 loops=3)
        Buffers: shared hit=7
        ->  Index Scan using container_repositories_p51_namespace_id_repository_id_idx on container_repositories_p51 container_repositories  (cost=0.28..20.41 rows=3 width=48) (actual time=0.008..0.011 rows=3 loops=1)
              Index Cond: ((namespace_id = '29fe9b00-4072-4ccb-9f51-c6035dfae2dd'::uuid) AND (repository_id = ANY ('{fb3e9d9c-aafe-452f-825b-7eacd873039f,8e4930fe-44cf-4a1c-b233-6c9dfb52f890,803e7f28-f7fa-485a-bdbd-2d08a7b0c3f2}'::uuid[])))
              Buffers: shared hit=7
Planning:
  Buffers: shared hit=577
Planning Time: 1.422 ms
Execution Time: 0.049 ms

Timings: planning 1.422ms, execution 0.049ms, total 1.471ms.

datastore.ContainerVirtualHostedManifestLookup

Summary: The plan matches the method's intent. The planner drives from the position list on unique_container_repositories_namespace_id_and_repository_id, seeks one image per position, then seeks unique_container_manifests_ns_id_ci_id_digest for the digest. All three partitioned tables prune to one hash partition, the estimate matches reality (3 / 3), and every buffer is a hit. No anomalies.

Seed shape: namespaces=1, blob_storage_blobs=1, blob_storage_attachments=1, repositories=5003, container_repositories=5003, container_images=5003, container_manifests=15000

Rendered SQL:

SELECT container_repositories.repository_id AS "container_repositories.repository_id",
     container_manifests.blob_sha256 AS "container_manifests.blob_sha256",
     container_manifests.media_type AS "container_manifests.media_type"
FROM public.container_repositories
     INNER JOIN public.container_images ON ((container_images.namespace_id = container_repositories.namespace_id) AND (container_images.container_repository_id = container_repositories.id))
     INNER JOIN public.container_manifests ON ((container_manifests.namespace_id = container_images.namespace_id) AND (container_manifests.container_image_id = container_images.id))
WHERE ((((((container_repositories.namespace_id = $1::uuid) AND (container_repositories.repository_id IN ($2::uuid, $3::uuid, $4::uuid))) AND (container_images.namespace_id = $5::uuid)) AND (container_images.name = $6::text)) AND (container_images.soft_deleted_at IS NULL)) AND (container_manifests.namespace_id = $7::uuid)) AND (container_manifests.digest = $8::bytea);

Bound args: [191d24a5-1b6b-4682-b3a4-1bb619de8236, 78322f3e-8ee1-4422-9251-72cf6a352c04, 4efba5c5-a221-4e5f-bb8d-f1723ec80306, 8280e547-7b18-459c-85c7-b15ca83efaf9, 191d24a5-1b6b-4682-b3a4-1bb619de8236, review-prep-image, 191d24a5-1b6b-4682-b3a4-1bb619de8236, \x00000000000000000000000000000000000000000000000000000000000009c4]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Nested Loop  (cost=0.97..66.66 rows=3 width=92) (actual time=0.023..0.036 rows=3 loops=1)
  Buffers: shared hit=28
  ->  Nested Loop  (cost=0.56..45.35 rows=3 width=48) (actual time=0.016..0.024 rows=3 loops=1)
        Buffers: shared hit=16
        ->  Index Scan using container_repositories_p52_namespace_id_repository_id_idx on container_repositories_p52 container_repositories  (cost=0.28..20.41 rows=3 width=48) (actual time=0.009..0.012 rows=3 loops=1)
              Index Cond: ((namespace_id = '191d24a5-1b6b-4682-b3a4-1bb619de8236'::uuid) AND (repository_id = ANY ('{78322f3e-8ee1-4422-9251-72cf6a352c04,4efba5c5-a221-4e5f-bb8d-f1723ec80306,8280e547-7b18-459c-85c7-b15ca83efaf9}'::uuid[])))
              Buffers: shared hit=7
        ->  Index Scan using container_images_p52_namespace_id_container_repository_id_i_idx on container_images_p52 container_images  (cost=0.28..8.30 rows=1 width=48) (actual time=0.003..0.003 rows=1 loops=3)
              Index Cond: ((namespace_id = '191d24a5-1b6b-4682-b3a4-1bb619de8236'::uuid) AND (container_repository_id = container_repositories.id))
              Filter: (name = 'review-prep-image'::text)
              Buffers: shared hit=9
  ->  Index Scan using container_manifests_p52_namespace_id_container_image_id_dig_idx on container_manifests_p52 container_manifests  (cost=0.41..8.43 rows=1 width=108) (actual time=0.004..0.004 rows=1 loops=3)
        Index Cond: ((namespace_id = '191d24a5-1b6b-4682-b3a4-1bb619de8236'::uuid) AND (container_image_id = container_images.id) AND (digest = '\x00000000000000000000000000000000000000000000000000000000000009c4'::bytea))
        Buffers: shared hit=12
Planning:
  Buffers: shared hit=481
Planning Time: 1.190 ms
Execution Time: 0.054 ms

Timings: planning 1.190ms, execution 0.054ms, total 1.244ms.

datastore.ContainerVirtualHostedTagLookup

Summary: The plan matches the method's intent, and it is the deepest of the three: four partitioned tables, each pruned to one hash partition. The planner drives from the position list, seeks one image per position, seeks unique_container_tags_ns_id_ci_id_name for the tag, then resolves the manifest on pk_container_manifests with the second composite arm as a Filter. The root estimate of 1 row against 3 actual is a 3x divergence, under the 10x threshold and explained by the three positions each holding the name. No anomalies.

Seed shape: namespaces=1, blob_storage_blobs=1, blob_storage_attachments=1, repositories=5003, container_repositories=5003, container_images=5003, container_manifests=15000, container_tags=15000

Rendered SQL:

SELECT container_repositories.repository_id AS "container_repositories.repository_id",
     container_manifests.blob_sha256 AS "container_manifests.blob_sha256",
     container_manifests.media_type AS "container_manifests.media_type"
FROM public.container_repositories
     INNER JOIN public.container_images ON ((container_images.namespace_id = container_repositories.namespace_id) AND (container_images.container_repository_id = container_repositories.id))
     INNER JOIN public.container_tags ON ((container_tags.namespace_id = container_images.namespace_id) AND (container_tags.container_image_id = container_images.id))
     INNER JOIN public.container_manifests ON (((container_manifests.namespace_id = container_tags.namespace_id) AND (container_manifests.id = container_tags.container_manifest_id)) AND (container_manifests.container_image_id = container_tags.container_image_id))
WHERE (((((((container_repositories.namespace_id = $1::uuid) AND (container_repositories.repository_id IN ($2::uuid, $3::uuid, $4::uuid))) AND (container_images.namespace_id = $5::uuid)) AND (container_images.name = $6::text)) AND (container_images.soft_deleted_at IS NULL)) AND (container_tags.namespace_id = $7::uuid)) AND (container_tags.name = $8::text)) AND (container_manifests.namespace_id = $9::uuid);

Bound args: [cf9347d0-88ee-4254-ab3e-6c2c8a3084a6, 6fc51470-134d-4a34-bc03-fba56ff85fea, 8d7d1bf5-10f0-42fc-91dd-975c622097d4, 3013d6df-2f9b-4ee3-825f-4e365182fcdf, cf9347d0-88ee-4254-ab3e-6c2c8a3084a6, review-prep-image, cf9347d0-88ee-4254-ab3e-6c2c8a3084a6, review-prep-tag-000009c4, cf9347d0-88ee-4254-ab3e-6c2c8a3084a6]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Nested Loop  (cost=1.26..91.62 rows=1 width=92) (actual time=0.029..0.047 rows=3 loops=1)
  Buffers: shared hit=37
  ->  Nested Loop  (cost=0.97..66.66 rows=3 width=80) (actual time=0.023..0.037 rows=3 loops=1)
        Buffers: shared hit=28
        ->  Nested Loop  (cost=0.56..45.35 rows=3 width=48) (actual time=0.016..0.023 rows=3 loops=1)
              Buffers: shared hit=16
              ->  Index Scan using container_repositories_p27_namespace_id_repository_id_idx on container_repositories_p27 container_repositories  (cost=0.28..20.41 rows=3 width=48) (actual time=0.008..0.011 rows=3 loops=1)
                    Index Cond: ((namespace_id = 'cf9347d0-88ee-4254-ab3e-6c2c8a3084a6'::uuid) AND (repository_id = ANY ('{6fc51470-134d-4a34-bc03-fba56ff85fea,8d7d1bf5-10f0-42fc-91dd-975c622097d4,3013d6df-2f9b-4ee3-825f-4e365182fcdf}'::uuid[])))
                    Buffers: shared hit=7
              ->  Index Scan using container_images_p27_namespace_id_container_repository_id_i_idx on container_images_p27 container_images  (cost=0.28..8.30 rows=1 width=48) (actual time=0.003..0.004 rows=1 loops=3)
                    Index Cond: ((namespace_id = 'cf9347d0-88ee-4254-ab3e-6c2c8a3084a6'::uuid) AND (container_repository_id = container_repositories.id))
                    Filter: (name = 'review-prep-image'::text)
                    Buffers: shared hit=9
        ->  Index Scan using container_tags_p27_namespace_id_container_image_id_name_idx on container_tags_p27 container_tags  (cost=0.41..8.43 rows=1 width=48) (actual time=0.004..0.004 rows=1 loops=3)
              Index Cond: ((namespace_id = 'cf9347d0-88ee-4254-ab3e-6c2c8a3084a6'::uuid) AND (container_image_id = container_images.id) AND (name = 'review-prep-tag-000009c4'::text))
              Buffers: shared hit=12
  ->  Index Scan using container_manifests_p27_pkey on container_manifests_p27 container_manifests  (cost=0.29..8.31 rows=1 width=124) (actual time=0.003..0.003 rows=1 loops=3)
        Index Cond: ((id = container_tags.container_manifest_id) AND (namespace_id = 'cf9347d0-88ee-4254-ab3e-6c2c8a3084a6'::uuid))
        Filter: (container_tags.container_image_id = container_image_id)
        Buffers: shared hit=9
Planning:
  Buffers: shared hit=504
Planning Time: 1.510 ms
Execution Time: 0.068 ms

Timings: planning 1.510ms, execution 0.068ms, total 1.578ms.

e2e scenario impact

No scenario added or affected, because both catalog sections are owned by Step 18.

Related to #291

Edited by Radamanthus Batnag

Merge request reports