feat(datastore): batched container remote upstream lookup (S32 plan: 6/19)

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

What

Adds the batched remote cache read for a virtual container repository and the Phase 1 adapter that answers one remote position from it.

ContainerVirtualRemoteLookupStore.ByPositions answers every remote position in one statement per reference shape — tag, manifest by digest, blob by digest — instead of one statement per position. Each statement is the IN-list twin of the read ContainerRemoteCacheStore already answers per repository. oci.VirtualRemoteUpstream maps one position's verdict onto remote.LookupResult.

LookupResult.ETag carries the row's upstream_etag. That field is what makes the conditional HEAD reachable: conditionalETag reads it off the Phase 1 result, and remote.Prober maps a 304 to ProbeFresh only on a non-empty If-None-Match. An adapter that left it empty would silently disable the spec's stale-tag revalidation for every container position.

No route reaches either entry point yet. Nothing on main calls ByPositions or NewVirtualRemoteUpstream, so this MR changes no observable behavior and rolls back cleanly in both directions — an older binary simply emits three fewer name label values.

Why the predicates are enumerated

A batched rewrite is where a per-repository read's predicates get dropped, and nothing fails when one goes missing: the SQL compiles and the wrong answer is plausible. internal/datastore/container_virtual_remote_lookup_predicates.md names each predicate the twins carry, per statement, alongside what dropping it would cost. Every excluded row shape has an integration subtest asserting a miss, and each runs per reference shape rather than once, because identical SQL across the three tables is not a reason to assert one.

Dropped-subtest diff

Guardrail 6's mirror clause applies twice here: this suite mirrors Step 5's hosted half, and the oci adapter mirrors internal/format/npm's. Both diffs were run, and they found three dropped subtests, all now restored:

  • A live content row answering when a tombstoned row shares its key, on both digest routes. unique_container_remote_manifests_ns_id_image_id_digest and unique_container_remote_blobs_ns_id_image_id_digest are partial on soft_deleted_at IS NULL (migrations/structure.sql:54878, :56427), so a tombstone releases its slot and a re-cached row sits beside it. An earlier comment in this branch's suite claimed those indexes carry no WHERE clause and skipped the case on that basis. That claim was wrong; the case is required and is now covered.
  • The by-digest routes not crossing tables. Manifests and blobs both key on (namespace, image, digest), so a builder reaching for the wrong one answers plausibly.
  • A miss-carrying adapter in the oci path-mismatch table. That table varied the asked-for path and built a hit adapter every time, so it never asserted that the guard precedes the verdict for a miss. A Miss is the zero LookupResult, so a verdict returned ahead of the guard reads as a plausible miss on the asked-for path rather than as a fault.

Two findings left for follow-up

Neither is closed here. The first has had its test half fixed, which the paragraph below states.

The two freshness reads spell one window differently. freshWithinHoursExpr builds its interval from a Go time.Duration, and jet splits whole days out first, so a 24-hour window — the cache_validity_hours default — renders INTERVAL '1 DAY'. freshWithinColumnWindowExpr multiplies INTERVAL '1 HOUR' by the column, so it always renders exact hours. Postgres makes timestamptz - interval '1 day' DST-aware and - interval '24 hours' not, so under a session timezone that observes DST the two edges sit an hour apart on a shift day. Measured under America/New_York at 2026-11-01 12:00:00-05: the day edge falls 25 real hours back and the hours edge 24, so a row 24.5 hours old reads fresh by the per-repository read and stale by this one. The "two reads agree about staleness" claim therefore holds everywhere except that hour, and the remaining exposure is production's rather than the suite's. cvrlIntStaleStamp is 30 hours, not 25, which keeps every fixture clear of the gap at either edge, so the agreement test holds under any session timezone. At 25 hours it did not: both reads call that row stale, but with zero margin against the day edge, and the skew between the fixture's application-side stamp and the database's NOW() then decides the verdict. Closing the gap for production means rendering exact hours in freshWithinHoursExpr, which the per-repository container read and both npm remote reads share, so it belongs in its own change. Tracked in #1001.

The name label cardinality budget is nearly spent. internal/metrics/cardinality.go sets labelName: 500. query_names.go now declares 476 and internal/storage/queries.go 12, so 488 are spoken for including this MR's 3. Four open MRs — !2030 (merged), !2035 (merged), !2123 (merged), !2040 (merged) — add 17 more, which crosses 500. AuditCardinality sees only observed values, so no test fails when it does. The plan assigns the hand-raise to Step 4 and this MR correctly leaves the file alone, but the arithmetic already crosses from work in flight.

Size

3,192 reviewable LOC, past the 500 ceiling. Split by file group:

Group LOC Files
Production Go 472 the datastore read 356, the oci adapter 109, query_names.go 7
In-package doc 236 container_virtual_remote_lookup_predicates.md
Tests 2,484 datastore integration 1,378, datastore unit 414, datastore explain 312, oci unit 241, oci internal 139

Measured at d914bce94.

Splitting was considered and rejected. The production halves would split by package along S31's precedent for the identical step, but the 2,484 test LOC follow whichever half they exercise: the datastore side would still carry ~2,600 LOC and the oci side ~470. That trades one over-ceiling MR for one over-ceiling MR plus a stack, a rewritten history, and a step marker duplicated across two titles. The production code is 472 LOC, under the ceiling on its own, and the test bulk is what the plan's Step 6 Tests bullet asked for — statement counts at 2 and 20 positions, freshness agreement with the per-repository reader, and an EXPLAIN suite. The plan's own size section (## Step sizing) states that every step of this plan trips the gate and carries this justification.

Testing

  • Unit and integration suites for both new files, green locally against a lock-tuned Postgres.
  • The EXPLAIN suite asserts one partition per table at 2 and at 20 positions. 20 is ADR-004's upstream cap.
  • TestContainerVirtualRemoteLookupStore_StatementCountIsIndependentOfPositionCount pins the statement count against the query counter.
  • Freshness agreement with ContainerRemoteCacheStore.Lookup across four window-and-age combinations.
  • The ETag claim is asserted through conditionalETag's exported equivalent rather than on the field, because a populated field is not the property that matters.
  • golangci-lint run --build-tags=integration reports nothing on the new files beyond contextcheck, which fires on every sibling integration suite and never reaches CI.

e2e scenarios

docs/testing/ is untouched. Step 18 of the plan owns the hermetic end-to-end proxy harness and the container scenario catalogs, so no scenario is added or affected here.

Notes for the reviewer

  • The plan's Status-table row for this step is recorded in the S32 batched docs(plans) MR, not here — guardrail 4 gives the plan file a single writer.
  • container_virtual_remote_lookup_predicates.md is a fifth file, beyond the plan's four-file Files: list. internal/datastore already carries npm_virtual_remote_lookup_join_order.md, and internal/format/npm carries virtual_remote_upstream.md, so an in-package note for a batched read has precedent — despite AGENTS.md putting project documentation under docs/.
  • ByPositions does not check that a position belongs to the virtual repository. That is the producing read's gate, documented under ## Positions are trusted, and nothing enforces ADR-004's 20-upstream cap yet because no container virtual write path exists.
  • The access path is deliberately unpinned. The EXPLAIN suite asserts partition count and table set, not scan method, for the reason container_remote_cache_explain_integration_test.go's header gives: at one row per table every join order costs the same, so an Index Cond assertion would pin a fixture artifact rather than the plan production wants. container_virtual_remote_lookup_predicates.md records that under ## Partition pruning, so the deferral is stated in the tree and not only here. The npm sibling made the opposite call for the same IN-list rewrite. npm_virtual_remote_lookup_join_order.md records a measurement at two tenant sizes and at one, two and 20 positions, and npm_virtual_remote_lookup_join_order_integration_test.go guards the drive across PostgreSQL 16, 17 and 18. Container defers that fixture, and what it defers is real: no index on the tag table or either digest table leads with the columns this predicate binds directly — container_remote_image_id is the second column of all three unique indexes and arrives only through the join — so a leaf-driven plan scans the tenant's whole partition, and the 20-wide IN list is the input that could move the planner there. Building the container twin of that fixture is the close, and this MR does not.

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.ContainerVirtualRemoteLookupStore.blobByPositions Nested Loop container_remote_blobs_p58_namespace_id_container_remote_im_idx 3 / 3 28.72 0.032ms 15 / 0 1/64, 1/64, 1/64, 1/64
datastore.ContainerVirtualRemoteLookupStore.manifestByPositions Nested Loop container_remote_manifests_p5_namespace_id_container_remot_idx8 3 / 3 28.72 0.033ms 15 / 0 1/64, 1/64, 1/64, 1/64
datastore.ContainerVirtualRemoteLookupStore.tagByPositions Nested Loop container_remote_tags_p58_namespace_id_container_remote_ima_idx, container_remote_manifests_p58_pkey 1 / 3 49.46 0.264ms 28 / 0 1/64, 1/64, 1/64, 1/64, 1/64
datastore.ContainerVirtualRemoteLookupStore.blobByPositions

Summary: The plan matches the method's intent. The planner drives from container_remote_images, then probes the digest unique index once per position, so the three-position IN list costs three index lookups rather than a partition scan. Every scanned table prunes to partition p58, and no anomalies appear.

Seed shape: namespaces=1, blob_storage_blobs=1, blob_storage_attachments=1, repositories=3, container_remote_repositories=3, container_remote_images=3, container_remote_blobs=5003, container_remote_manifests=5003, container_remote_tags=5006

Rendered SQL:

SELECT container_remote_images.container_remote_repository_id AS "container_remote_images.container_remote_repository_id",
     container_remote_blobs.blob_sha256 AS "container_remote_blobs.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))
     INNER JOIN public.repositories ON ((repositories.id = container_remote_repositories.repository_id) AND (repositories.namespace_id = container_remote_repositories.namespace_id))
WHERE (((container_remote_blobs.namespace_id = $1::uuid) AND (container_remote_blobs.digest = $2::bytea)) AND (container_remote_blobs.soft_deleted_at IS NULL)) AND (((((((container_remote_images.namespace_id = $3::uuid) AND (container_remote_images.container_remote_repository_id IN ($4::uuid, $5::uuid, $6::uuid))) AND (container_remote_images.name = $7::text)) AND (container_remote_images.soft_deleted_at IS NULL)) AND (container_remote_repositories.namespace_id = $8::uuid)) AND (container_remote_repositories.id IN ($9::uuid, $10::uuid, $11::uuid))) AND ((repositories.namespace_id = $12::uuid) AND (((repositories.format IN ($13, $14)) AND (repositories.kind = $15)) AND (repositories.soft_deleted_at IS NULL))));

Bound args: [aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa, \x000102030405060708090a0b0c0d0e0f101112131415161718191a1b1c1d1e1f, aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa, 11111111-1111-4111-8111-111111111111, 22222222-2222-4222-8222-222222222222, 33333333-3333-4333-8333-333333333333, library/nginx, aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa, 11111111-1111-4111-8111-111111111111, 22222222-2222-4222-8222-222222222222, 33333333-3333-4333-8333-333333333333, aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa, 0, 3, 2]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Nested Loop  (cost=0.41..28.72 rows=3 width=49) (actual time=0.024..0.032 rows=3 loops=1)
   Join Filter: (container_remote_repositories.repository_id = repositories.id)
   Rows Removed by Join Filter: 3
   Buffers: shared hit=15
   ->  Nested Loop  (cost=0.41..27.54 rows=3 width=81) (actual time=0.019..0.026 rows=3 loops=1)
         Join Filter: (container_remote_repositories.id = container_remote_images.container_remote_repository_id)
         Rows Removed by Join Filter: 3
         Buffers: shared hit=14
         ->  Nested Loop  (cost=0.41..26.38 rows=3 width=65) (actual time=0.014..0.021 rows=3 loops=1)
               Buffers: shared hit=13
               ->  Seq Scan on container_remote_images_p58 container_remote_images  (cost=0.00..1.06 rows=3 width=48) (actual time=0.004..0.005 rows=3 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (namespace_id = 'aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa'::uuid) AND (name = 'library/nginx'::text) AND (container_remote_repository_id = ANY ('{11111111-1111-4111-8111-111111111111,22222222-2222-4222-8222-222222222222,33333333-3333-4333-8333-333333333333}'::uuid[])))
                     Buffers: shared hit=1
               ->  Index Scan using container_remote_blobs_p58_namespace_id_container_remote_im_idx on container_remote_blobs_p58 container_remote_blobs  (cost=0.41..8.43 rows=1 width=65) (actual time=0.005..0.005 rows=1 loops=3)
                     Index Cond: ((namespace_id = 'aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa'::uuid) AND (container_remote_image_id = container_remote_images.id) AND (digest = '\x000102030405060708090a0b0c0d0e0f101112131415161718191a1b1c1d1e1f'::bytea))
                     Buffers: shared hit=12
         ->  Materialize  (cost=0.00..1.06 rows=3 width=48) (actual time=0.001..0.002 rows=2 loops=3)
               Buffers: shared hit=1
               ->  Seq Scan on container_remote_repositories_p58 container_remote_repositories  (cost=0.00..1.05 rows=3 width=48) (actual time=0.002..0.002 rows=3 loops=1)
                     Filter: ((namespace_id = 'aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa'::uuid) AND (id = ANY ('{11111111-1111-4111-8111-111111111111,22222222-2222-4222-8222-222222222222,33333333-3333-4333-8333-333333333333}'::uuid[])))
                     Buffers: shared hit=1
   ->  Materialize  (cost=0.00..1.07 rows=3 width=32) (actual time=0.001..0.002 rows=2 loops=3)
         Buffers: shared hit=1
         ->  Seq Scan on repositories_p58 repositories  (cost=0.00..1.05 rows=3 width=32) (actual time=0.002..0.003 rows=3 loops=1)
               Filter: ((soft_deleted_at IS NULL) AND (format = ANY ('{0,3}'::integer[])) AND (namespace_id = 'aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa'::uuid) AND (kind = 2))
               Buffers: shared hit=1
 Planning:
   Buffers: shared hit=307
 Planning Time: 1.185 ms
 Execution Time: 0.051 ms

Timings: planning 1.185ms, execution 0.051ms, total 1.236ms.

datastore.ContainerVirtualRemoteLookupStore.manifestByPositions

Summary: The plan matches the method's intent, and it is the container_remote_manifests twin of the blob plan: an image-driven Nested Loop with one digest index probe per position. Every scanned table prunes to partition p58. No anomalies appear.

Seed shape: namespaces=1, blob_storage_blobs=1, blob_storage_attachments=1, repositories=3, container_remote_repositories=3, container_remote_images=3, container_remote_manifests=5003, container_remote_blobs=5003, container_remote_tags=5006

Rendered SQL:

SELECT container_remote_images.container_remote_repository_id AS "container_remote_images.container_remote_repository_id",
     container_remote_manifests.blob_sha256 AS "container_remote_manifests.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))
     INNER JOIN public.repositories ON ((repositories.id = container_remote_repositories.repository_id) AND (repositories.namespace_id = container_remote_repositories.namespace_id))
WHERE (((container_remote_manifests.namespace_id = $1::uuid) AND (container_remote_manifests.digest = $2::bytea)) AND (container_remote_manifests.soft_deleted_at IS NULL)) AND (((((((container_remote_images.namespace_id = $3::uuid) AND (container_remote_images.container_remote_repository_id IN ($4::uuid, $5::uuid, $6::uuid))) AND (container_remote_images.name = $7::text)) AND (container_remote_images.soft_deleted_at IS NULL)) AND (container_remote_repositories.namespace_id = $8::uuid)) AND (container_remote_repositories.id IN ($9::uuid, $10::uuid, $11::uuid))) AND ((repositories.namespace_id = $12::uuid) AND (((repositories.format IN ($13, $14)) AND (repositories.kind = $15)) AND (repositories.soft_deleted_at IS NULL))));

Bound args: [aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa, \x000102030405060708090a0b0c0d0e0f101112131415161718191a1b1c1d1e1f, aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa, 11111111-1111-4111-8111-111111111111, 22222222-2222-4222-8222-222222222222, 33333333-3333-4333-8333-333333333333, library/nginx, aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa, 11111111-1111-4111-8111-111111111111, 22222222-2222-4222-8222-222222222222, 33333333-3333-4333-8333-333333333333, aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa, 0, 3, 2]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Nested Loop  (cost=0.41..28.72 rows=3 width=49) (actual time=0.023..0.033 rows=3 loops=1)
   Join Filter: (container_remote_repositories.repository_id = repositories.id)
   Rows Removed by Join Filter: 3
   Buffers: shared hit=15
   ->  Nested Loop  (cost=0.41..27.54 rows=3 width=81) (actual time=0.018..0.027 rows=3 loops=1)
         Join Filter: (container_remote_repositories.id = container_remote_images.container_remote_repository_id)
         Rows Removed by Join Filter: 3
         Buffers: shared hit=14
         ->  Nested Loop  (cost=0.41..26.38 rows=3 width=65) (actual time=0.013..0.020 rows=3 loops=1)
               Buffers: shared hit=13
               ->  Seq Scan on container_remote_images_p58 container_remote_images  (cost=0.00..1.06 rows=3 width=48) (actual time=0.004..0.005 rows=3 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (namespace_id = 'aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa'::uuid) AND (name = 'library/nginx'::text) AND (container_remote_repository_id = ANY ('{11111111-1111-4111-8111-111111111111,22222222-2222-4222-8222-222222222222,33333333-3333-4333-8333-333333333333}'::uuid[])))
                     Buffers: shared hit=1
               ->  Index Scan using container_remote_manifests_p5_namespace_id_container_remot_idx8 on container_remote_manifests_p58 container_remote_manifests  (cost=0.41..8.43 rows=1 width=65) (actual time=0.004..0.005 rows=1 loops=3)
                     Index Cond: ((namespace_id = 'aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa'::uuid) AND (container_remote_image_id = container_remote_images.id) AND (digest = '\x000102030405060708090a0b0c0d0e0f101112131415161718191a1b1c1d1e1f'::bytea))
                     Buffers: shared hit=12
         ->  Materialize  (cost=0.00..1.06 rows=3 width=48) (actual time=0.002..0.002 rows=2 loops=3)
               Buffers: shared hit=1
               ->  Seq Scan on container_remote_repositories_p58 container_remote_repositories  (cost=0.00..1.05 rows=3 width=48) (actual time=0.003..0.003 rows=3 loops=1)
                     Filter: ((namespace_id = 'aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa'::uuid) AND (id = ANY ('{11111111-1111-4111-8111-111111111111,22222222-2222-4222-8222-222222222222,33333333-3333-4333-8333-333333333333}'::uuid[])))
                     Buffers: shared hit=1
   ->  Materialize  (cost=0.00..1.07 rows=3 width=32) (actual time=0.001..0.002 rows=2 loops=3)
         Buffers: shared hit=1
         ->  Seq Scan on repositories_p58 repositories  (cost=0.00..1.05 rows=3 width=32) (actual time=0.002..0.002 rows=3 loops=1)
               Filter: ((soft_deleted_at IS NULL) AND (format = ANY ('{0,3}'::integer[])) AND (namespace_id = 'aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa'::uuid) AND (kind = 2))
               Buffers: shared hit=1
 Planning:
   Buffers: shared hit=249
 Planning Time: 0.977 ms
 Execution Time: 0.052 ms

Timings: planning 0.977ms, execution 0.052ms, total 1.029ms.

datastore.ContainerVirtualRemoteLookupStore.tagByPositions

Summary: The plan matches the method's intent. The planner drives from container_remote_images, probes container_remote_tags on the (namespace_id, container_remote_image_id, name) unique index once per position, then resolves each tag's manifest by primary key, so the in-SQL freshness expression evaluates over three rows. Every scanned table prunes to partition p58. The root estimate of 1 row against 3 actual is a 3x divergence, which is the join-selectivity floor and not a stats problem.

Seed shape: namespaces=1, blob_storage_blobs=1, blob_storage_attachments=1, repositories=3, container_remote_repositories=3, container_remote_images=3, container_remote_manifests=5003, container_remote_blobs=5003, container_remote_tags=5006

Rendered SQL:

SELECT container_remote_images.container_remote_repository_id AS "container_remote_images.container_remote_repository_id",
     container_remote_manifests.blob_sha256 AS "container_remote_manifests.blob_sha256",
     container_remote_tags.upstream_etag AS "container_remote_tags.upstream_etag",
     ((container_remote_repositories.cache_validity_hours = $1) OR (container_remote_tags.upstream_checked_at > (NOW() - (INTERVAL '1 HOUR' * container_remote_repositories.cache_validity_hours)))) AS "fresh"
FROM public.container_remote_tags
     INNER JOIN public.container_remote_manifests ON ((((container_remote_manifests.namespace_id = $2::uuid) AND (container_remote_manifests.id = container_remote_tags.container_remote_manifest_id)) AND (container_remote_manifests.container_remote_image_id = container_remote_tags.container_remote_image_id)) AND (container_remote_manifests.soft_deleted_at IS NULL))
     INNER JOIN public.container_remote_images ON ((container_remote_images.id = container_remote_tags.container_remote_image_id) AND (container_remote_images.namespace_id = container_remote_tags.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))
     INNER JOIN public.repositories ON ((repositories.id = container_remote_repositories.repository_id) AND (repositories.namespace_id = container_remote_repositories.namespace_id))
WHERE ((container_remote_tags.namespace_id = $3::uuid) AND (container_remote_tags.name = $4::text)) AND (((((((container_remote_images.namespace_id = $5::uuid) AND (container_remote_images.container_remote_repository_id IN ($6::uuid, $7::uuid, $8::uuid))) AND (container_remote_images.name = $9::text)) AND (container_remote_images.soft_deleted_at IS NULL)) AND (container_remote_repositories.namespace_id = $10::uuid)) AND (container_remote_repositories.id IN ($11::uuid, $12::uuid, $13::uuid))) AND ((repositories.namespace_id = $14::uuid) AND (((repositories.format IN ($15, $16)) AND (repositories.kind = $17)) AND (repositories.soft_deleted_at IS NULL))));

Bound args: [0, aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa, aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa, latest, aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa, 11111111-1111-4111-8111-111111111111, 22222222-2222-4222-8222-222222222222, 33333333-3333-4333-8333-333333333333, library/nginx, aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa, 11111111-1111-4111-8111-111111111111, 22222222-2222-4222-8222-222222222222, 33333333-3333-4333-8333-333333333333, aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa, 0, 3, 2]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Nested Loop  (cost=0.69..49.46 rows=1 width=82) (actual time=0.247..0.264 rows=3 loops=1)
   Buffers: shared hit=28
   ->  Nested Loop  (cost=0.69..48.37 rows=1 width=123) (actual time=0.026..0.042 rows=3 loops=1)
         Buffers: shared hit=25
         ->  Nested Loop  (cost=0.69..47.30 rows=1 width=105) (actual time=0.023..0.037 rows=3 loops=1)
               Buffers: shared hit=22
               ->  Nested Loop  (cost=0.41..22.36 rows=3 width=120) (actual time=0.015..0.024 rows=3 loops=1)
                     Buffers: shared hit=13
                     ->  Seq Scan on container_remote_images_p58 container_remote_images  (cost=0.00..1.06 rows=3 width=48) (actual time=0.005..0.006 rows=3 loops=1)
                           Filter: ((soft_deleted_at IS NULL) AND (namespace_id = 'aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa'::uuid) AND (name = 'library/nginx'::text) AND (container_remote_repository_id = ANY ('{11111111-1111-4111-8111-111111111111,22222222-2222-4222-8222-222222222222,33333333-3333-4333-8333-333333333333}'::uuid[])))
                           Buffers: shared hit=1
                     ->  Index Scan using container_remote_tags_p58_namespace_id_container_remote_ima_idx on container_remote_tags_p58 container_remote_tags  (cost=0.41..8.43 rows=1 width=88) (actual time=0.005..0.005 rows=1 loops=3)
                           Index Cond: ((namespace_id = 'aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa'::uuid) AND (container_remote_image_id = container_remote_images.id) AND (name = 'latest'::text))
                           Buffers: shared hit=12
               ->  Index Scan using container_remote_manifests_p58_pkey on container_remote_manifests_p58 container_remote_manifests  (cost=0.28..8.30 rows=1 width=65) (actual time=0.004..0.004 rows=1 loops=3)
                     Index Cond: ((id = container_remote_tags.container_remote_manifest_id) AND (namespace_id = 'aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa'::uuid))
                     Filter: ((soft_deleted_at IS NULL) AND (container_remote_tags.container_remote_image_id = container_remote_image_id))
                     Buffers: shared hit=9
         ->  Seq Scan on container_remote_repositories_p58 container_remote_repositories  (cost=0.00..1.06 rows=1 width=50) (actual time=0.001..0.001 rows=1 loops=3)
               Filter: ((namespace_id = 'aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa'::uuid) AND (id = container_remote_images.container_remote_repository_id) AND (id = ANY ('{11111111-1111-4111-8111-111111111111,22222222-2222-4222-8222-222222222222,33333333-3333-4333-8333-333333333333}'::uuid[])))
               Rows Removed by Filter: 1
               Buffers: shared hit=3
   ->  Seq Scan on repositories_p58 repositories  (cost=0.00..1.06 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=3)
         Filter: ((soft_deleted_at IS NULL) AND (format = ANY ('{0,3}'::integer[])) AND (namespace_id = 'aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa'::uuid) AND (kind = 2))
         Rows Removed by Filter: 1
         Buffers: shared hit=3
 Planning:
   Buffers: shared hit=1608 read=1
 Planning Time: 3.634 ms
 Execution Time: 0.292 ms

Timings: planning 3.634ms, execution 0.292ms, total 3.926ms.

Query notes:

  • All three statements ran at three positions, the shape the resolver sends today. ## Notes for the reviewer records that no index on the tag table or either digest table leads with container_remote_image_id, so a leaf-driven plan would scan the tenant's whole partition. At three positions the planner drove from container_remote_images in every case and never chose that path. Whether a 20-wide IN list moves it is what the deferred container join-order fixture measures; this evidence does not settle it.
  • The rendered SELECTs carry no LIMIT. Each returns at most one row per position, because unique_container_remote_tags_ns_id_image_id_name and the two unique_..._ns_id_image_id_digest indexes make the bound predicate unique per image, and unique_container_remote_images_ns_id_repo_id_name makes the image unique per position. The result therefore grows with the caller's position list and not with the table. ## Positions are trusted in container_virtual_remote_lookup_predicates.md records that nothing caps that list yet, and that ADR-004's 20-upstream cap is a write-time rule with no container virtual write path to enforce it.

Related to #291

Edited by Radamanthus Batnag

Merge request reports