chore(datastore): add container remote image and manifest read stores

This targets main directly. The MR it was stacked on has merged: chore(datastore): add remote cache keyset and d... (!1581 - merged) • Hayley Swimelar • 19.3.

Why

Step 14 of the S17 Phase 6 plan (docs/plans/2026-08-13-s17-phase6-virtual-remote-repositories.md): the first hand-written read stores over the container remote cache tables, finders and keyset lists for images and manifests, landing ahead of their route consumers (plan Steps 19 and 20) so the handler MRs compose over a reviewed store surface. The plan moved the keyset EXPLAIN pins from the index migration into the consumer read steps, so the container pins ride here: they prove the lists run on the manifests keyset index plan Step 2 added, now on main, and on the images partial unique that already exists.

What (the non-obvious parts)

• The image finder is by id, not name: the management routes address images by {image_id}, and by-name resolution already lives on the protocol path. The manifest digest finder mirrors the hosted GetContainerManifestByDigest, and its consumers arrive with the plan's later eviction composers.
• The EXPLAIN pins tolerate planner variation: catalog-resolved child index names, all four Index Scan prefix variants, no post-scan Sort, 5,000-row analyzed seeds. A Seq Scan at small cardinality is planner noise, so the seed volume is load-bearing.
• The manifests list has no referrer split: the remote table stores no subject_digest, so one statement serves both include_referrers values. The parameter's grammar and its 400 are Step 19's.
• The store-level LIMIT is uncapped, mirroring the hosted store/handler division. The Step 19 handler owns the clamp.
• Three hosted files carry two-line //nolint:dupl companions: the mirrored suites fire dupl on both sides of each pair, and each token was kept only after measuring that dropping it re-fires the finding.

Size

3,239 changed LOC against main: 786 source (362 images, 424 manifests), 4 in query_names.go, 2,429 test (863 unit, 1,566 integration), 14 in keyset.go, and 6 //nolint companion lines across three hosted files. The overrun past the 500-LOC guideline is test mass at 3.1x source, inside the plan's measured 2.5x to 4.5x band for datastore-heavy steps. Splitting the stores from the tests that pin them would not reduce review load.

Test plan

• Unit and integration suites per the coverage table below, run on the repo's standard integration bootstrap.
• Observed pins: images asc = Index Scan and desc = Index Scan Backward on children of unique_container_remote_images_ns_id_repo_id_name, manifests desc and asc on children of index_container_remote_manifests_on_ns_id_cri_id_created_at_id, no Sort node anywhere, every plan pruned to a single partition.
• Gates: gofmt, go build ./..., go vet -tags=integration, golangci-lint in both modes with zero findings in this MR's files, go test ./internal/datastore/, and the branch-scoped -tags=integration run.

Spec coverage

Spec: docs/specs/S17-rest-management-api.md

Scope: this table carries the criteria that bind Step 14's store surface. Rows for the HTTP surface over these stores are owned by plan Steps 19 and 20 and travel with those MRs.

Acceptance criteria

#	Criterion (Step 14 slice)	Tests
AC-17	Images list ordered by name, keyset with no duplicates or gaps; detail carries id, name, last_downloaded_at; missing id resolves not-found	TestContainerRemoteImageStore_ListContainerRemoteImages, TestContainerRemoteImageStore_ListContainerRemoteImages_KeysetWalk, TestContainerRemoteImageStore_FindContainerRemoteImageInRepository, TestListContainerRemoteImagesStmt, TestFindContainerRemoteImageInRepositoryStmt. Route wiring: Step 19.
AC-19	Manifests list newest-first with the resource columns	TestContainerRemoteManifestStore_ListContainerRemoteManifests_KeysetWalkBothDirections, TestContainerRemoteManifestStore_ListContainerRemoteManifests_RowFidelity, TestListContainerRemoteManifestsStmt. artifact_type/subject_digest null mapping: Step 19 (no columns on the remote table).
AC-25	Soft-deleted artifact, and every artifact beneath a soft-deleted parent, absent from lists and not-found on detail	Image level: TestContainerRemoteImageStore_SoftDeletedImageIsInvisibleToReads. Manifest level: TestContainerRemoteManifestStore_SoftDeletedManifestIsInvisibleToReads. Beneath-a-marked-parent at the HTTP level: Step 19 (the handler resolves the image through the finder first; the finder's not-found is the store-level gate).
AC-27	Existing parent with no children returns an empty page	Empty-scope arms of TestContainerRemoteImageStore_ListContainerRemoteImages_ChainScoping and TestContainerRemoteManifestStore_ListContainerRemoteManifests_ChainScoping; "image with no manifests" in TestContainerRemoteManifestStore_ListContainerRemoteManifests_HasMoreSignal. 200-with-[] shape: Step 19.
AC-28	Invalid sort, order, limit, or cursor rejected	TestContainerRemoteImageStore_ListContainerRemoteImages_ArgumentGuards, TestContainerRemoteManifestStore_ListContainerRemoteManifests_ArgumentGuards (+ _AcceptsPopulatedCursor on both). Sentinels the handler maps to 400; wire-level 400 is Step 19 plus the Phase 3 parsers.
AC-95	Every Phase 3 list and detail route serves a remote repository from its cache tables; an unpulled coordinate is absent	The whole store surface of this MR (both files' suites); rows exist only when seeded. Route serving: Step 19 (images, manifests), Step 20 (tags).
AC-97	Remote manifests list returns the same rows under both include_referrers values	Store slice: one statement, no referrer split. TestListContainerRemoteManifestsStmt (asserts no subject_digest predicate); ListContainerRemoteManifestsParams carries no referrer field by construction. Parameter grammar, its 400, and null serialization: Step 19.
AC-100	An evicted-but-unreaped row (marked soft_deleted_at) is absent from every list and detail read	Both SoftDeletedXxxIsInvisibleToReads suites (both directions, page-size-1 walks, finder arms). The images suite carries the hasMore probe discriminator, a cursor-bounded arm whose only row past the page is marked. The manifests probe discriminator is the 'limit equals the live count with a marked row beyond' arm of TestContainerRemoteManifestStore_ListContainerRemoteManifests_HasMoreSignal. Marker-filter pins in TestListContainerRemoteImagesStmt, TestFindContainerRemoteImageInRepositoryStmt, TestListContainerRemoteManifestsStmt, TestGetContainerRemoteManifestByDigestStmt.
Step acceptance	EXPLAIN pins the Step 2 keyset index and the images partial unique; keyset order index-resident with no post-scan Sort	TestContainerRemoteImageStore_ListContainerRemoteImages_DeepPageIsIndexBacked, TestContainerRemoteManifestStore_ListContainerRemoteManifests_DeepPageIsIndexBacked (5000 analyzed rows, catalog-resolved child names, any scan direction); TestFindContainerRemoteImageInRepositoryStmt_SinglePartitionPrune, TestGetContainerRemoteManifestByDigestStmt_SinglePartitionPrune.

Error cases

#	Condition	Tests
E-1	Artifact lists: invalid sort, order, limit, or a cursor with no usable keyset boundary	The two _ArgumentGuards tables (typed sentinels per input class); include_referrers grammar and cursor decode: Step 19.
E-2	Artifact routes: parent or artifact missing, soft-deleted, or outside the URL's parent chain	TestContainerRemoteImageStore_FindContainerRemoteImageInRepository (missing, cross-repository, cross-namespace), TestContainerRemoteManifestStore_GetContainerRemoteManifestByDigest (unknown digest, sibling image, foreign namespace), both invisibility suites. Non-UUID path id: handler-owned (Step 19, parsePathID).
E-3	List query failure is distinguishable from an empty page	TestContainerRemoteManifestStore_ListContainerRemoteManifests_QueryFailure and TestContainerRemoteImageStore_ListContainerRemoteImages_QueryFailure (wraps preserve the cause for errors.Is; the images family pins its identifier-carrying wrap, the manifests family pins identifier absence).

Security considerations

#	Concern	Tests
S-1	Injection: Jet builder with bound parameters	TestListContainerRemoteImagesStmt and TestListContainerRemoteManifestsStmt assert cursor boundaries arrive as $n binds, never inlined; validKeysetText bounds the images cursor (the guard table carries the NUL arm, and TestValidKeysetText covers the wider classes).
S-2	Tenant isolation: every query scoped by namespace_id, artifact reads verify the parent chain	Both _ChainScoping suites, the cross-namespace and cross-repository finder arms, and the single-partition EXPLAIN pins.
S-3	Existence hiding: missing, foreign, and hidden rows are indistinguishable	Finders return the one ErrNotFound sentinel for absent, cross-chain, and marked rows alike (finder subtests assert the same sentinel per arm).

Context for LLM agents

Design rationale and rejected alternatives:

• A by-name image finder was rejected: the management routes address images by {image_id}, and remote by-name resolution already exists inline on the protocol path (containerRemoteCacheImageScope). The finder mirrors the hosted FindContainerImageInRepository.
• A referrer-split manifests list was rejected: container_remote_manifests stores no subject_digest, so the hosted partial/full index pair does not carry over and one statement serves the full cached inventory.
• Manifests-under-a-marked-image invisibility is enforced at the image-finder gate (the finder answers not-found), mirroring the hosted arrangement. The manifests list scopes by (namespace, image) with no image join, which is the single-table shape the keyset index serves.
• Empty pages assert Empty rather than pinning nil: the handler owns the JSON array shape, and pinning nil would constrain an allocation choice the spec does not reach.
• The store-level LIMIT is deliberately uncapped (hosted parity); the Step 19 handler owns the clamp, and its review should verify that.

Non-goals:

• Route wiring, 400/404 shaping, include_referrers grammar, and null serialization belong to plan Steps 19 and 20.
• Tag stores belong to plan Step 20.
• The plan's Status table row for Step 14 is filled by the designated table-owner MR for this fan-out wave, not here.

Known environmental note for local reviewers: at 8e2952120 the whole-package go test -tags=integration ./internal/datastore/ fails 104 tests on stock testcontainers Postgres, every one of them on the namespace-cleanup cascade exhausting the lock table (SQLSTATE 53200). None of the 104 is a test of this MR's. That is container tuning rather than an unavoidable cost: the same whole-package run passes clean, zero failures, against Postgres started with -c max_locks_per_transaction=4096. This MR's suites pass inside that run and in the branch-scoped run. The provisioning fix is tracked in Local testcontainers Postgres needs max_locks_p... (#657) • Unassigned.

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. The Index column names the logical index; the plans below name the partition child that Postgres actually scanned.

Method	Plan node	Index	Rows (plan / actual)	Cost	Time	Buffers (hit / read)	Partitions
datastore.ListContainerRemoteImages.CursorNilAsc	Limit	unique_container_remote_images_ns_id_repo_id_name	11 / 11	1.43	0.028ms	3 / 0	1/64
datastore.ListContainerRemoteImages.CursorNilDesc	Limit	unique_container_remote_images_ns_id_repo_id_name	11 / 11	1.43	0.026ms	5 / 0	1/64
datastore.ListContainerRemoteImages.CursorSetAsc	Limit	unique_container_remote_images_ns_id_repo_id_name	11 / 11	1.67	0.028ms	3 / 0	1/64
datastore.ListContainerRemoteImages.CursorSetDesc	Limit	unique_container_remote_images_ns_id_repo_id_name	11 / 11	2.12	0.032ms	5 / 0	1/64
datastore.FindContainerRemoteImageInRepository	Limit	pk_container_remote_images	1 / 1	8.30	0.022ms	3 / 0	1/64
datastore.ListContainerRemoteManifests.CursorNilAsc	Limit	index_container_remote_manifests_on_ns_id_created_at_id	11 / 11	2.15	0.028ms	4 / 0	1/64
datastore.ListContainerRemoteManifests.CursorNilDesc	Limit	index_container_remote_manifests_on_ns_id_created_at_id	11 / 11	2.15	0.030ms	20 / 0	1/64
datastore.ListContainerRemoteManifests.CursorSetAsc	Limit	index_container_remote_manifests_on_ns_id_cri_id_created_at_id	11 / 11	2.60	0.049ms	3 / 0	1/64
datastore.ListContainerRemoteManifests.CursorSetDesc	Limit	index_container_remote_manifests_on_ns_id_created_at_id	11 / 11	3.52	0.047ms	3 / 0	1/64
datastore.GetContainerRemoteManifestByDigest	Limit	unique_container_remote_manifests_ns_id_image_id_digest	1 / 1	8.43	0.029ms	4 / 0	1/64

Query notes:

• datastore.ListContainerRemoteImages and datastore.ListContainerRemoteManifests: the store-level LIMIT is uncapped by design, pending the Step 19 handler's clamp, matching the hosted store and handler division. probeLimit neither clamps nor rejects, so the rendered LIMIT $n carries the caller's value, and the skill counts a caller-fed limit with no server-side maximum as unbounded. The two finders bind a constant LIMIT 1 and are exempt.
• datastore.ListContainerRemoteManifests: the rows above and the committed EXPLAIN pins measure different statements, so read the index column as evidence about the rows above only. These rows run LIMIT 11 against this run's seeder, where the queried image holds 5000 of the namespace's 7000 manifests. At that near-parity index_container_remote_manifests_on_ns_id_created_at_id, which is (namespace_id, created_at DESC, id DESC), and the keyset index index_container_remote_manifests_on_ns_id_cri_id_created_at_id cost about the same, and the planner takes either. Raising the sibling to 20000 rows, so the image is a minority of the namespace's manifests as it is in production, moves every plan onto the keyset index. Both shapes prune to a single partition, take their order from an index, and finish in tens of microseconds. The committed pins run LIMIT 21 against batchSeedContainerRemoteManifests and show no such variation. Their sibling image exists so container_remote_image_id is not a no-op key.

datastore.ListContainerRemoteImages.CursorNilAsc

Summary: Plan matches the method's intent: the first ascending page rides the partial unique unique_container_remote_images_ns_id_repo_id_name, with namespace_id pruning to one of 64 partitions. The index supplies the name order, so there is no post-scan Sort, and the estimate matches reality exactly (11 / 11). No anomalies.

Seed shape: namespaces=1, repositories=2, container_remote_repositories=2, container_remote_images=7000 (5000 under the queried repository, 2000 under a sibling)

Rendered SQL:

SELECT container_remote_images.namespace_id AS "container_remote_images.namespace_id",
container_remote_images.id AS "container_remote_images.id",
container_remote_images.container_remote_repository_id AS "container_remote_images.container_remote_repository_id",
container_remote_images.last_downloaded_at AS "container_remote_images.last_downloaded_at",
container_remote_images.name AS "container_remote_images.name"
FROM public.container_remote_images
WHERE ((container_remote_images.namespace_id = $1::uuid) AND (container_remote_images.container_remote_repository_id = $2::uuid)) AND (container_remote_images.soft_deleted_at IS NULL)
ORDER BY container_remote_images.name ASC
LIMIT $3;

Bound args: :ns_id, :crr_id, 11 (ns_id and the parent id are the seeded UUIDs, 11 is probeLimit(10): the page of 10 plus the hasMore probe row)

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..1.43 rows=11 width=81) (actual time=0.011..0.014 rows=11 loops=1)
Buffers: shared hit=3
->  Index Scan using container_remote_images_p59_namespace_id_container_remote_r_idx on container_remote_images_p59 container_remote_images  (cost=0.28..520.72 rows=5000 width=81) (actual time=0.010..0.012 rows=11 loops=1)
Index Cond: ((namespace_id = '4c04495e-f149-457f-8d48-abaeb72e85ab'::uuid) AND (container_remote_repository_id = '2a312df4-ea1d-4df0-929d-be49bb339ebb'::uuid))
Buffers: shared hit=3
Planning:
Buffers: shared hit=274
Planning Time: 0.934 ms
Execution Time: 0.028 ms

Timings: planning 0.934ms, execution 0.028ms, total 0.962ms.

datastore.ListContainerRemoteImages.CursorNilDesc

Summary: Descending pages the same index backward rather than sorting, which is why one index serves both directions. Partition pruned to one of 64, no Sort node, estimate exact at 11 / 11. No anomalies.

Seed shape: namespaces=1, repositories=2, container_remote_repositories=2, container_remote_images=7000 (5000 under the queried repository, 2000 under a sibling)

Rendered SQL:

SELECT container_remote_images.namespace_id AS "container_remote_images.namespace_id",
container_remote_images.id AS "container_remote_images.id",
container_remote_images.container_remote_repository_id AS "container_remote_images.container_remote_repository_id",
container_remote_images.last_downloaded_at AS "container_remote_images.last_downloaded_at",
container_remote_images.name AS "container_remote_images.name"
FROM public.container_remote_images
WHERE ((container_remote_images.namespace_id = $1::uuid) AND (container_remote_images.container_remote_repository_id = $2::uuid)) AND (container_remote_images.soft_deleted_at IS NULL)
ORDER BY container_remote_images.name DESC
LIMIT $3;

Bound args: :ns_id, :crr_id, 11 (ns_id and the parent id are the seeded UUIDs, 11 is probeLimit(10): the page of 10 plus the hasMore probe row)

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..1.43 rows=11 width=81) (actual time=0.009..0.013 rows=11 loops=1)
Buffers: shared hit=5
->  Index Scan Backward using container_remote_images_p48_namespace_id_container_remote_r_idx on container_remote_images_p48 container_remote_images  (cost=0.28..520.72 rows=5000 width=81) (actual time=0.008..0.012 rows=11 loops=1)
Index Cond: ((namespace_id = 'bc3c23f3-91fa-496e-bbe5-941e7b7c57eb'::uuid) AND (container_remote_repository_id = '9faf0211-a850-4d7d-bcad-bff8da6ea247'::uuid))
Buffers: shared hit=5
Planning:
Buffers: shared hit=79
Planning Time: 0.360 ms
Execution Time: 0.026 ms

Timings: planning 0.360ms, execution 0.026ms, total 0.386ms.

datastore.ListContainerRemoteImages.CursorSetAsc

Summary: The keyset bound folds into the index range: name > $3 appears in the Index Cond, not as a post-scan Filter, so a deep page costs the same as the first. Partition pruned to one of 64, no Sort. No anomalies.

Seed shape: namespaces=1, repositories=2, container_remote_repositories=2, container_remote_images=7000 (5000 under the queried repository, 2000 under a sibling)

Rendered SQL:

SELECT container_remote_images.namespace_id AS "container_remote_images.namespace_id",
container_remote_images.id AS "container_remote_images.id",
container_remote_images.container_remote_repository_id AS "container_remote_images.container_remote_repository_id",
container_remote_images.last_downloaded_at AS "container_remote_images.last_downloaded_at",
container_remote_images.name AS "container_remote_images.name"
FROM public.container_remote_images
WHERE (((container_remote_images.namespace_id = $1::uuid) AND (container_remote_images.container_remote_repository_id = $2::uuid)) AND (container_remote_images.soft_deleted_at IS NULL)) AND (container_remote_images.name > $3::text)
ORDER BY container_remote_images.name ASC
LIMIT $4;

Bound args: :ns_id, :crr_id, review-prep-image-002500, 11 (ns_id and the parent id are the seeded UUIDs, 11 is probeLimit(10): the page of 10 plus the hasMore probe row)

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..1.67 rows=11 width=81) (actual time=0.013..0.016 rows=11 loops=1)
Buffers: shared hit=3
->  Index Scan using container_remote_images_p31_namespace_id_container_remote_r_idx on container_remote_images_p31 container_remote_images  (cost=0.28..402.68 rows=3201 width=81) (actual time=0.013..0.014 rows=11 loops=1)
Index Cond: ((namespace_id = '72ad84d3-af92-4d23-8ca3-bec2636ee206'::uuid) AND (container_remote_repository_id = '30259406-6c98-4d38-ac05-3eb37bd0167c'::uuid) AND (name > 'review-prep-image-002500'::text))
Buffers: shared hit=3
Planning:
Buffers: shared hit=76
Planning Time: 0.402 ms
Execution Time: 0.028 ms

Timings: planning 0.402ms, execution 0.028ms, total 0.43ms.

datastore.ListContainerRemoteImages.CursorSetDesc

Summary: The descending deep page folds name < $3 into the Index Cond of the same index scanned backward. Latency stays flat with offset rather than growing. Partition pruned to one of 64, no Sort. No anomalies.

Seed shape: namespaces=1, repositories=2, container_remote_repositories=2, container_remote_images=7000 (5000 under the queried repository, 2000 under a sibling)

Rendered SQL:

SELECT container_remote_images.namespace_id AS "container_remote_images.namespace_id",
container_remote_images.id AS "container_remote_images.id",
container_remote_images.container_remote_repository_id AS "container_remote_images.container_remote_repository_id",
container_remote_images.last_downloaded_at AS "container_remote_images.last_downloaded_at",
container_remote_images.name AS "container_remote_images.name"
FROM public.container_remote_images
WHERE (((container_remote_images.namespace_id = $1::uuid) AND (container_remote_images.container_remote_repository_id = $2::uuid)) AND (container_remote_images.soft_deleted_at IS NULL)) AND (container_remote_images.name < $3::text)
ORDER BY container_remote_images.name DESC
LIMIT $4;

Bound args: :ns_id, :crr_id, review-prep-image-002500, 11 (ns_id and the parent id are the seeded UUIDs, 11 is probeLimit(10): the page of 10 plus the hasMore probe row)

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..2.12 rows=11 width=81) (actual time=0.015..0.019 rows=11 loops=1)
Buffers: shared hit=5
->  Index Scan Backward using container_remote_images_p36_namespace_id_container_remote_r_idx on container_remote_images_p36 container_remote_images  (cost=0.28..300.99 rows=1798 width=81) (actual time=0.014..0.018 rows=11 loops=1)
Index Cond: ((namespace_id = 'f3231323-4931-450a-a3c6-3fa3a633b077'::uuid) AND (container_remote_repository_id = '1639f39d-b7c1-4a42-966a-c92651039d7c'::uuid) AND (name < 'review-prep-image-002500'::text))
Buffers: shared hit=5
Planning:
Buffers: shared hit=78
Planning Time: 0.417 ms
Execution Time: 0.032 ms

Timings: planning 0.417ms, execution 0.032ms, total 0.449ms.

datastore.FindContainerRemoteImageInRepository

Summary: The id lookup takes the primary key (id, namespace_id), the most selective path available, and prunes to one of 64 partitions. container_remote_repository_id and the soft-delete marker are re-checked as a Filter on the single matched row, which is what keeps a guessed id from another repository resolving to a row. No anomalies.

Seed shape: namespaces=1, repositories=2, container_remote_repositories=2, container_remote_images=7000 (5000 under the queried repository, 2000 under a sibling)

Rendered SQL:

SELECT container_remote_images.namespace_id AS "container_remote_images.namespace_id",
container_remote_images.id AS "container_remote_images.id",
container_remote_images.container_remote_repository_id AS "container_remote_images.container_remote_repository_id",
container_remote_images.last_downloaded_at AS "container_remote_images.last_downloaded_at",
container_remote_images.name AS "container_remote_images.name"
FROM public.container_remote_images
WHERE (((container_remote_images.namespace_id = $1::uuid) AND (container_remote_images.container_remote_repository_id = $2::uuid)) AND (container_remote_images.id = $3::uuid)) AND (container_remote_images.soft_deleted_at IS NULL)
LIMIT $4;

Bound args: :ns_id, :crr_id, :mid_id, 1 (ns_id and the parent id are the seeded UUIDs, and the trailing 1 is the builder's constant LIMIT 1)

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..8.30 rows=1 width=81) (actual time=0.009..0.010 rows=1 loops=1)
Buffers: shared hit=3
->  Index Scan using container_remote_images_p62_pkey on container_remote_images_p62 container_remote_images  (cost=0.28..8.30 rows=1 width=81) (actual time=0.009..0.009 rows=1 loops=1)
Index Cond: ((id = 'e99ca55e-f018-4533-a438-57701c0e3c37'::uuid) AND (namespace_id = '04b9fa3b-bf7d-45da-b8df-5a26e7931d7b'::uuid))
Filter: ((soft_deleted_at IS NULL) AND (container_remote_repository_id = '020957b3-4cfd-4283-9850-2b0c7de26464'::uuid))
Buffers: shared hit=3
Planning:
Buffers: shared hit=67
Planning Time: 0.349 ms
Execution Time: 0.022 ms

Timings: planning 0.349ms, execution 0.022ms, total 0.371ms.

datastore.ListContainerRemoteManifests.CursorNilAsc

Summary: The first ascending page prunes to one of 64 partitions and takes its order from an index rather than a Sort. At this seed the target image holds 5000 of the namespace's 7000 manifests, so container_remote_image_id is weakly selective. The planner may pick either index_container_remote_manifests_on_ns_id_created_at_id, which is (namespace_id, created_at DESC, id DESC), with the image id as a Filter, or the container_remote_image_id keyset index. The two cost about the same here. No anomalies.

Seed shape: namespaces=1, repositories=1, container_remote_repositories=1, container_remote_images=2, blob_storage_blobs=1, blob_storage_attachments=1, container_remote_manifests=7000 (5000 under the queried image, 2000 under a sibling)

Rendered SQL:

SELECT container_remote_manifests.namespace_id AS "container_remote_manifests.namespace_id",
container_remote_manifests.id AS "container_remote_manifests.id",
container_remote_manifests.container_remote_image_id AS "container_remote_manifests.container_remote_image_id",
container_remote_manifests.blob_storage_attachment_id AS "container_remote_manifests.blob_storage_attachment_id",
container_remote_manifests.size AS "container_remote_manifests.size",
container_remote_manifests.created_at AS "container_remote_manifests.created_at",
container_remote_manifests.last_downloaded_at AS "container_remote_manifests.last_downloaded_at",
container_remote_manifests.soft_deleted_at AS "container_remote_manifests.soft_deleted_at",
container_remote_manifests.media_type AS "container_remote_manifests.media_type",
container_remote_manifests.digest AS "container_remote_manifests.digest",
container_remote_manifests.blob_sha256 AS "container_remote_manifests.blob_sha256"
FROM public.container_remote_manifests
WHERE ((container_remote_manifests.namespace_id = $1::uuid) AND (container_remote_manifests.container_remote_image_id = $2::uuid)) AND (container_remote_manifests.soft_deleted_at IS NULL)
ORDER BY container_remote_manifests.created_at ASC, container_remote_manifests.id ASC
LIMIT $3;

Bound args: :ns_id, :cri_id, 11 (ns_id and the parent id are the seeded UUIDs, 11 is probeLimit(10): the page of 10 plus the hasMore probe row)

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..2.15 rows=11 width=197) (actual time=0.010..0.013 rows=11 loops=1)
Buffers: shared hit=4
->  Index Scan Backward using container_remote_manifests_p30_namespace_id_created_at_id_idx on container_remote_manifests_p30 container_remote_manifests  (cost=0.28..849.35 rows=5000 width=197) (actual time=0.010..0.012 rows=11 loops=1)
Index Cond: (namespace_id = 'f06fc183-cd0e-4d69-ae6c-0948e2380095'::uuid)
Filter: ((soft_deleted_at IS NULL) AND (container_remote_image_id = '7e7880ef-4a66-4327-871b-ec083cf1fe79'::uuid))
Buffers: shared hit=4
Planning:
Buffers: shared hit=324
Planning Time: 1.091 ms
Execution Time: 0.028 ms

Timings: planning 1.091ms, execution 0.028ms, total 1.119ms.

datastore.ListContainerRemoteManifests.CursorNilDesc

Summary: Descending reads the same index in the opposite direction with no Sort, pruned to one of 64 partitions. The same cost-equivalence between the two candidate indexes applies as on the ascending first page. No anomalies.

Seed shape: namespaces=1, repositories=1, container_remote_repositories=1, container_remote_images=2, blob_storage_blobs=1, blob_storage_attachments=1, container_remote_manifests=7000 (5000 under the queried image, 2000 under a sibling)

Rendered SQL:

SELECT container_remote_manifests.namespace_id AS "container_remote_manifests.namespace_id",
container_remote_manifests.id AS "container_remote_manifests.id",
container_remote_manifests.container_remote_image_id AS "container_remote_manifests.container_remote_image_id",
container_remote_manifests.blob_storage_attachment_id AS "container_remote_manifests.blob_storage_attachment_id",
container_remote_manifests.size AS "container_remote_manifests.size",
container_remote_manifests.created_at AS "container_remote_manifests.created_at",
container_remote_manifests.last_downloaded_at AS "container_remote_manifests.last_downloaded_at",
container_remote_manifests.soft_deleted_at AS "container_remote_manifests.soft_deleted_at",
container_remote_manifests.media_type AS "container_remote_manifests.media_type",
container_remote_manifests.digest AS "container_remote_manifests.digest",
container_remote_manifests.blob_sha256 AS "container_remote_manifests.blob_sha256"
FROM public.container_remote_manifests
WHERE ((container_remote_manifests.namespace_id = $1::uuid) AND (container_remote_manifests.container_remote_image_id = $2::uuid)) AND (container_remote_manifests.soft_deleted_at IS NULL)
ORDER BY container_remote_manifests.created_at DESC, container_remote_manifests.id DESC
LIMIT $3;

Bound args: :ns_id, :cri_id, 11 (ns_id and the parent id are the seeded UUIDs, 11 is probeLimit(10): the page of 10 plus the hasMore probe row)

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..2.15 rows=11 width=197) (actual time=0.011..0.016 rows=11 loops=1)
Buffers: shared hit=20
->  Index Scan using container_remote_manifests_p49_namespace_id_created_at_id_idx on container_remote_manifests_p49 container_remote_manifests  (cost=0.28..849.35 rows=5000 width=197) (actual time=0.011..0.014 rows=11 loops=1)
Index Cond: (namespace_id = '93f19050-ec6c-44bf-b452-ebc378ddb81c'::uuid)
Filter: ((soft_deleted_at IS NULL) AND (container_remote_image_id = '9d9fcf0b-fd72-4a63-ab16-de6fae5070ab'::uuid))
Rows Removed by Filter: 11
Buffers: shared hit=20
Planning:
Buffers: shared hit=123
Planning Time: 0.563 ms
Execution Time: 0.030 ms

Timings: planning 0.563ms, execution 0.030ms, total 0.593ms.

datastore.ListContainerRemoteManifests.CursorSetAsc

Summary: The row-value bound (created_at, id) > ($3, $4) folds into the Index Cond as a single range, which is the point of the tuple comparison over two chained predicates. Pruned to one of 64 partitions, no Sort, estimate exact at 11 / 11. No anomalies.

Seed shape: namespaces=1, repositories=1, container_remote_repositories=1, container_remote_images=2, blob_storage_blobs=1, blob_storage_attachments=1, container_remote_manifests=7000 (5000 under the queried image, 2000 under a sibling)

Rendered SQL:

SELECT container_remote_manifests.namespace_id AS "container_remote_manifests.namespace_id",
container_remote_manifests.id AS "container_remote_manifests.id",
container_remote_manifests.container_remote_image_id AS "container_remote_manifests.container_remote_image_id",
container_remote_manifests.blob_storage_attachment_id AS "container_remote_manifests.blob_storage_attachment_id",
container_remote_manifests.size AS "container_remote_manifests.size",
container_remote_manifests.created_at AS "container_remote_manifests.created_at",
container_remote_manifests.last_downloaded_at AS "container_remote_manifests.last_downloaded_at",
container_remote_manifests.soft_deleted_at AS "container_remote_manifests.soft_deleted_at",
container_remote_manifests.media_type AS "container_remote_manifests.media_type",
container_remote_manifests.digest AS "container_remote_manifests.digest",
container_remote_manifests.blob_sha256 AS "container_remote_manifests.blob_sha256"
FROM public.container_remote_manifests
WHERE (((container_remote_manifests.namespace_id = $1::uuid) AND (container_remote_manifests.container_remote_image_id = $2::uuid)) AND (container_remote_manifests.soft_deleted_at IS NULL)) AND ((container_remote_manifests.created_at, container_remote_manifests.id) > ($3::timestamp with time zone, $4::uuid))
ORDER BY container_remote_manifests.created_at ASC, container_remote_manifests.id ASC
LIMIT $5;

Bound args: :ns_id, :cri_id, :mid_created_at, :mid_id, 11 (ns_id and the parent id are the seeded UUIDs, 11 is probeLimit(10): the page of 10 plus the hasMore probe row)

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..2.60 rows=11 width=197) (actual time=0.011..0.013 rows=11 loops=1)
Buffers: shared hit=3
->  Index Scan using container_remote_manifests_p_namespace_id_container_remot_idx94 on container_remote_manifests_p24 container_remote_manifests  (cost=0.28..676.28 rows=3214 width=197) (actual time=0.011..0.012 rows=11 loops=1)
Index Cond: ((namespace_id = '0e8c59ce-485d-4231-84e9-85fcb4928675'::uuid) AND (container_remote_image_id = '750e8c7d-82ce-443a-98ea-8ce42d5ca122'::uuid) AND (ROW(created_at, id) > ROW('2026-08-17 23:47:00.656184+00'::timestamp with time zone, 'e40a5a3c-212a-446a-ad2a-7ad8741b1caf'::uuid)))
Buffers: shared hit=3
Planning:
Buffers: shared hit=118
Planning Time: 0.574 ms
Execution Time: 0.049 ms

Timings: planning 0.574ms, execution 0.049ms, total 0.623ms.

datastore.ListContainerRemoteManifests.CursorSetDesc

Summary: The descending deep page folds the row-value bound into the Index Cond the same way, so paging does not degrade with offset. Pruned to one of 64 partitions, no Sort. No anomalies.

Seed shape: namespaces=1, repositories=1, container_remote_repositories=1, container_remote_images=2, blob_storage_blobs=1, blob_storage_attachments=1, container_remote_manifests=7000 (5000 under the queried image, 2000 under a sibling)

Rendered SQL:

SELECT container_remote_manifests.namespace_id AS "container_remote_manifests.namespace_id",
container_remote_manifests.id AS "container_remote_manifests.id",
container_remote_manifests.container_remote_image_id AS "container_remote_manifests.container_remote_image_id",
container_remote_manifests.blob_storage_attachment_id AS "container_remote_manifests.blob_storage_attachment_id",
container_remote_manifests.size AS "container_remote_manifests.size",
container_remote_manifests.created_at AS "container_remote_manifests.created_at",
container_remote_manifests.last_downloaded_at AS "container_remote_manifests.last_downloaded_at",
container_remote_manifests.soft_deleted_at AS "container_remote_manifests.soft_deleted_at",
container_remote_manifests.media_type AS "container_remote_manifests.media_type",
container_remote_manifests.digest AS "container_remote_manifests.digest",
container_remote_manifests.blob_sha256 AS "container_remote_manifests.blob_sha256"
FROM public.container_remote_manifests
WHERE (((container_remote_manifests.namespace_id = $1::uuid) AND (container_remote_manifests.container_remote_image_id = $2::uuid)) AND (container_remote_manifests.soft_deleted_at IS NULL)) AND ((container_remote_manifests.created_at, container_remote_manifests.id) < ($3::timestamp with time zone, $4::uuid))
ORDER BY container_remote_manifests.created_at DESC, container_remote_manifests.id DESC
LIMIT $5;

Bound args: :ns_id, :cri_id, :mid_created_at, :mid_id, 11 (ns_id and the parent id are the seeded UUIDs, 11 is probeLimit(10): the page of 10 plus the hasMore probe row)

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..3.52 rows=11 width=197) (actual time=0.010..0.012 rows=11 loops=1)
Buffers: shared hit=3
->  Index Scan using container_remote_manifests_p10_namespace_id_created_at_id_idx on container_remote_manifests_p10 container_remote_manifests  (cost=0.28..524.95 rows=1785 width=197) (actual time=0.009..0.011 rows=11 loops=1)
Index Cond: ((namespace_id = 'ac36c768-a3ad-4179-bfd8-eda42f1b3888'::uuid) AND (ROW(created_at, id) < ROW('2026-08-17 23:47:01.610102+00'::timestamp with time zone, 'd1a1d871-a1f4-4ea4-9f88-8a46878ae81d'::uuid)))
Filter: ((soft_deleted_at IS NULL) AND (container_remote_image_id = 'ef75cecc-5645-4c8f-9e0c-82f985e0befb'::uuid))
Buffers: shared hit=3
Planning:
Buffers: shared hit=116
Planning Time: 0.593 ms
Execution Time: 0.047 ms

Timings: planning 0.593ms, execution 0.047ms, total 0.64ms.

datastore.GetContainerRemoteManifestByDigest

Summary: The digest lookup rides the partial unique unique_container_remote_manifests_ns_id_image_id_digest, binding all three key columns in the Index Cond and pruning to one of 64 partitions. Single row returned against an exact estimate. No anomalies.

Seed shape: namespaces=1, repositories=1, container_remote_repositories=1, container_remote_images=2, blob_storage_blobs=1, blob_storage_attachments=1, container_remote_manifests=7000 (5000 under the queried image, 2000 under a sibling)

Rendered SQL:

SELECT container_remote_manifests.namespace_id AS "container_remote_manifests.namespace_id",
container_remote_manifests.id AS "container_remote_manifests.id",
container_remote_manifests.container_remote_image_id AS "container_remote_manifests.container_remote_image_id",
container_remote_manifests.blob_storage_attachment_id AS "container_remote_manifests.blob_storage_attachment_id",
container_remote_manifests.size AS "container_remote_manifests.size",
container_remote_manifests.created_at AS "container_remote_manifests.created_at",
container_remote_manifests.last_downloaded_at AS "container_remote_manifests.last_downloaded_at",
container_remote_manifests.soft_deleted_at AS "container_remote_manifests.soft_deleted_at",
container_remote_manifests.media_type AS "container_remote_manifests.media_type",
container_remote_manifests.digest AS "container_remote_manifests.digest",
container_remote_manifests.blob_sha256 AS "container_remote_manifests.blob_sha256"
FROM public.container_remote_manifests
WHERE (((container_remote_manifests.namespace_id = $1::uuid) AND (container_remote_manifests.container_remote_image_id = $2::uuid)) AND (container_remote_manifests.digest = $3::bytea)) AND (container_remote_manifests.soft_deleted_at IS NULL)
LIMIT $4;

Bound args: :ns_id, :cri_id, :dig_digest, 1 (ns_id and the parent id are the seeded UUIDs, and the trailing 1 is the builder's constant LIMIT 1)

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.41..8.43 rows=1 width=197) (actual time=0.015..0.015 rows=1 loops=1)
Buffers: shared hit=4
->  Index Scan using container_remote_manifests_p2_namespace_id_container_remot_idx8 on container_remote_manifests_p28 container_remote_manifests  (cost=0.41..8.43 rows=1 width=197) (actual time=0.014..0.014 rows=1 loops=1)
Index Cond: ((namespace_id = '3de48744-1bf7-448e-9422-69e975a07d38'::uuid) AND (container_remote_image_id = '7abbee8e-d019-45c5-9c89-338aa7536200'::uuid) AND (digest = '\x00000000000000000000000000000000000000000000000000000000000009c5'::bytea))
Buffers: shared hit=4
Planning:
Buffers: shared hit=121
Planning Time: 0.510 ms
Execution Time: 0.029 ms

Timings: planning 0.510ms, execution 0.029ms, total 0.539ms.

Related to #314