chore(datastore): gate container image reads on the removal marker

Hayley Swimelarrequested to merge
313/step-06-container-marker-predicates into main

Stacked on chore(datastore): add artifact tombstone marker... (!1454 - merged) • Hayley Swimelar • 19.3, merges after it.

Why

The parent MR adds container_images.soft_deleted_at and nothing reads it. A marked image would stay listed, stay resolvable by name, and still serve its tags and its blobs to a cross-repository mount. This lands every read gate before the first writer exists, so no window opens in which a mark hides nothing.

Behavior-preserving today, because nothing stamps the column until the container image DELETE handler, which depends on this step and on the partial-index swap. Step 6 of docs/plans/2026-08-10-s17-phase4-artifact-writes.md, spec section Removal is deferred behind a marker.

What (the non-obvious parts)

Code-first is the deploy order, and the index swap is deliberately absent. The upsert's conflict target gains WHERE soft_deleted_at IS NULL while unique_container_images_ns_id_cr_id_name is still total. PostgreSQL infers a non-partial unique index for a predicate-carrying conflict target, so the clause is safe against today's index. The reverse order raises SQLSTATE 42P10 on every image push from a pod still running a predicate-less binary. Both directions are measured on a throwaway PostgreSQL 16, not predicted. The clause carries its own rendered-SQL test because no behavioral assertion can reach it while the index is total: delete the line and every other test stays green.

Where each predicate sits. findContainerImageByTriple is shared by FindByName and the upsert's conflict-path read, so one edit closes the /v2/ resolution gate and the resurrect-on-conflict hole together. The tag chain hoists the filter into containerTagChainJoin's ON clause because two statements share that chain, while the mount check keeps it in the WHERE because one statement owns its join.

ContainerImageStore.Delete stays unfiltered. It is the leaf a marked subtree is removed through, so a filter there would make a marked row unreapable. A test goes red if it gains the predicate.

Two corrections to the plan's Step 6 text. The plan calls the tag-chain predicate defense in depth behind the management image gate. That holds for the tags list, but tag detail has no handler-side image gate, so the chain join is its only gate. And the step's Files entry names the soft_deleted_at doc claims: the same type also claimed container_images carries no created_at, which the parent MR falsified, so that claim is rewritten in the same file.

One window this does not close. ADD COLUMN left soft_deleted_at with no pg_statistic row, so until an ANALYZE runs the planner uses a default null-fraction estimate and the image list degrades from an ordered index scan to Sort plus Bitmap Heap Scan: 55 buffers and 0.765 ms against 4 buffers and 0.048 ms at 5,000 images. The tables are empty before launch and autovacuum closes it unattended. A fix means editing a migration, which belongs to the parent MR or to the swap step.

Spec coverage

# Acceptance criterion Covered by
34 Every read under a marked image answers 404 from the mark onward: image detail, the manifest and tag reads beneath it, and a /v2/ fetch TestContainerImageStore_SoftDeletedImageIsInvisibleToReads (list in both directions and at page size 1, FindContainerImageInRepository, FindByName, FindByID), TestContainerTagStore_ManagementReads_ExcludeSoftDeletedImage. The HTTP mapping is the handler steps'; this is the datastore half
34 The mount source check treats a marked image's blobs as absent and falls back to its missing-source 202 TestContainerBlobStore_FindBlobInRepository_SoftDeletedImage, covering marked-only, live-only, and the mixed case a repository-wide filter gets wrong
35 A push reusing a marked name succeeds as a fresh row rather than resurrecting the marked one Half of it: TestContainerImageStore_Upsert_WithASoftDeletedSibling pins never-resurrect, which holds under both index shapes. The fresh-row half needs the partial index and belongs to Step 7
33 An interrupted reap completes on the next pass Purger behavior, S20-A's to verify

Every statement over container_images, with the test that pins it:

Statement Predicate Pinned by
upsertContainerImageInsert conflict target gains TestUpsertContainerImageInsertStmt_ArbiterPredicate
findContainerImageByTriple (FindByName and the conflict-path read) gains ..._SoftDeletedImageIsInvisibleToReads/FindByName..., ..._Upsert_WithASoftDeletedSibling
FindByID's SELECT gains ..._SoftDeletedImageIsInvisibleToReads/FindByID...
findContainerImageInRepositoryStmt gains ..._SoftDeletedImageIsInvisibleToReads/FindContainerImageInRepository...
listContainerImagesStmt gains ..._SoftDeletedImageIsInvisibleToReads list subtests
ContainerImageStore.Delete stays unfiltered TestContainerImageStore_Delete_RemovesASoftDeletedRow
findBlobInRepositoryExists's image join gains TestContainerBlobStore_FindBlobInRepository_SoftDeletedImage
containerTagChainJoin's image join (both statements on it) gains TestContainerTagStore_ManagementReads_ExcludeSoftDeletedImage

Test plan

go test -tags=integration ./internal/datastore/ -count=1 \
  -run 'TestContainerImage|TestContainerTag|TestContainerBlob|TestUpsertContainerImage'

Tests stage a marked row with a direct UPDATE, since no writer stamps the column yet. Each of the seven predicates was deleted one at a time in a throwaway tree and each turned at least one named subtest red, and adding the predicate to Delete turns its test red, so no assertion here is vacuous. The pre-existing EXPLAIN assertions on the image list, the image detail, the tag page, the tag detail, and the upsert prune all still pass.

Context for LLM agents

Rationale

  • Swap the unique index first, then add predicates. Rejected: a pod running the old binary sends a predicate-less conflict target at a partial index and every image push fails with 42P10. Measured, not assumed.
  • Filter FindByName at its call site instead of in the shared builder. Rejected: the upsert's conflict-path read uses the same builder, so a caller-local filter leaves a marked row coming back as an existing row.
  • Put the tag marker filter in each statement's WHERE, matching the chain's stated ON-versus-WHERE split. Rejected: the filter is invariant across callers rather than caller scope, and the ON clause makes any later statement on that chain inherit it.
  • Filter every container table. Rejected: only container_images is marked, and each read of a manifest, tag, or blob resolves its image first.

Consequence: while the unique index is total, an upsert on a marked name conflicts and the filtered fallback read surfaces ErrNotFound, which the OCI upload path wraps into a 500. Unreachable today because nothing marks a row. The step that lands the marker writer owns the 500-versus-404 call for a push racing a mark, which the partial index does not close either.

Non-goals

  • No migration, no index change, no structure.sql edit. Step 7 owns the partial-index swap and its deploy gate.
  • No reverse-reference check gains the predicate, BlobStorageAttachmentStore.DeleteIfUnreferenced included. A marked row still counts as a reference, and filtering there frees an attachment out from under a row the purger has not reaped.
  • No marker writer, no handler change, no 404 mapping. Those are the handler steps'.
  • The plan's Status table stays empty. Sibling stacked branches would collide on the same rows, so the operator fills it once for the whole fan-out.
  • Stale claims outside this diff are left alone and routed: internal/managementapi/container_resources.go:22's created_at reason, ListContainerManifests' doc gap on the caller's image gate, and the parent MR's migration comment about the upsert fallback.

Database Review Evidence

Migrations

No migration files in this MR, so migration mode did not run. soft_deleted_at and its discovery index arrive with chore(datastore): add artifact tombstone marker... (!1454 - merged) • Hayley Swimelar • 19.3, and this MR only reads the column.

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 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 rendered SQL, bound args, and raw plan.

The diff is taken against origin/313/step-05-marker-columns-discovery-indexes, not main, so the parent MR's migration and jet regeneration are outside this evidence.

Seed shape, one transaction shared by every plan: namespaces=1, repositories=1, container_repositories=1, blob_storage_blobs=1, blob_storage_attachments=1, container_images=5000 (4000 live, 1000 marked), container_manifests=10000, container_tags=10000 (5000 under a marked image), container_blobs=5000 (2500 under a marked image). Every row carries the one test namespace's namespace_id. The six container-side tables (repositories, container_repositories, container_images, container_blobs, container_manifests, container_tags) are hash-partitioned on that column at modulus 64, so the whole seed lands in one partition child per table. namespaces is unpartitioned, and the two blob_storage_* ancestors are hash-partitioned on sha256 and hold one row each. Marked rows come from a direct UPDATE ... SET soft_deleted_at = now() on every fifth image.

Statements whose predicate can select a marked row ran a second time bound to one. Those runs appear as Marker probe inside the details block and are what show the filter discriminating rather than sitting inert.

Method Plan node Index Rows (plan / actual) Cost Time Buffers (hit / read) Partitions Rows removed by marker
datastore.upsertContainerImageInsert Insert on container_images n/a (arbiter unique_container_images_ns_id_cr_id_name) 1 / 1 0.01 1.277ms 31 / 0 container_images: 1 (container_images_p13) n/a (conflict arbiter; marked name still conflicts)
datastore.findContainerImageByTriple Limit container_images_p13_namespace_id_container_repository_id_n_idx 1 / 1 8.30 0.018ms 3 / 0 container_images: 1 (container_images_p13) 1 (probe)
datastore.ContainerImageStore.FindByID Limit container_images_p13_pkey 1 / 1 8.30 0.017ms 3 / 0 container_images: 1 (container_images_p13) 1 (probe)
datastore.findContainerImageInRepositoryStmt Limit container_images_p13_pkey 1 / 1 8.30 0.012ms 3 / 0 container_images: 1 (container_images_p13) 1 (probe)
datastore.listContainerImagesStmt.AscNoCursor Limit container_images_p13_namespace_id_container_repository_id_n_idx 11 / 11 1.72 0.019ms 7 / 0 container_images: 1 (container_images_p13) 2 (main run)
datastore.listContainerImagesStmt.AscCursor Limit container_images_p13_namespace_id_container_repository_id_n_idx 11 / 11 2.19 0.023ms 7 / 0 container_images: 1 (container_images_p13) 2 (main run)
datastore.listContainerImagesStmt.DescNoCursor Limit container_images_p13_namespace_id_container_repository_id_n_idx 11 / 11 1.72 0.019ms 10 / 0 container_images: 1 (container_images_p13) 3 (main run)
datastore.listContainerImagesStmt.DescCursor Limit container_images_p13_namespace_id_container_repository_id_n_idx 11 / 11 2.19 0.020ms 9 / 0 container_images: 1 (container_images_p13) 2 (main run)
datastore.findBlobInRepositoryExists Result container_blobs_p13_namespace_id_digest_idx, container_images_p13_pkey 1 / 1 16.63 0.029ms 6 / 0 container_blobs: 1 (container_blobs_p13); container_images: 1 (container_images_p13) 1 (probe)
datastore.listContainerTagsPageStmt.AscNoCursor Limit container_tags_p13_namespace_id_container_image_id_name_idx, container_manifests_p13_pkey, container_images_p13_pkey 11 / 11 9.22 0.087ms 40 / 0 container_images: 1 (container_images_p13); container_manifests: 1 (container_manifests_p13); container_tags: 1 (container_tags_p13) 1 (probe)
datastore.listContainerTagsPageStmt.AscCursor Limit container_tags_p13_namespace_id_container_image_id_name_idx, container_manifests_p13_pkey, container_images_p13_pkey 11 / 11 12.95 0.095ms 41 / 0 container_images: 1 (container_images_p13); container_manifests: 1 (container_manifests_p13); container_tags: 1 (container_tags_p13) 0 (bound image is live)
datastore.listContainerTagsPageStmt.DescNoCursor Limit container_tags_p13_namespace_id_container_image_id_name_idx, container_manifests_p13_pkey, container_images_p13_pkey 11 / 11 9.22 0.084ms 40 / 0 container_images: 1 (container_images_p13); container_manifests: 1 (container_manifests_p13); container_tags: 1 (container_tags_p13) 0 (bound image is live)
datastore.listContainerTagsPageStmt.DescCursor Limit container_tags_p13_namespace_id_container_image_id_name_idx, container_manifests_p13_pkey, container_images_p13_pkey 11 / 11 12.95 0.092ms 40 / 0 container_images: 1 (container_images_p13); container_manifests: 1 (container_manifests_p13); container_tags: 1 (container_tags_p13) 0 (bound image is live)
datastore.findContainerTagByNameStmt Limit container_tags_p13_namespace_id_container_image_id_name_idx, container_manifests_p13_pkey, container_images_p13_pkey 1 / 1 25.07 0.063ms 10 / 0 container_images: 1 (container_images_p13); container_manifests: 1 (container_manifests_p13); container_tags: 1 (container_tags_p13) 1 (probe)
datastore.upsertContainerImageInsert

Summary: Insert with the arbiter inferred as unique_container_images_ns_id_cr_id_name, the total index in place today, and the target pruned to one container_images_p13 child. A fresh name inserts one row. The marker probe confirms what the description claims about the pre-partial-index window: a push to a marked name still finds the arbiter, reports Conflicting Tuples: 1 / Tuples Inserted: 0, and falls to the filtered conflict-path read, which returns ErrNotFound rather than the marked row. No anomalies.

Rendered SQL:

INSERT INTO public.container_images (id, namespace_id, container_repository_id, name)
VALUES ($1::uuid, $2::uuid, $3::uuid, $4::text)
ON CONFLICT (namespace_id, container_repository_id, name) WHERE soft_deleted_at IS NULL DO NOTHING
RETURNING container_images.id AS "container_images.id",
container_images.namespace_id AS "container_images.namespace_id",
container_images.container_repository_id AS "container_images.container_repository_id",
container_images.name AS "container_images.name"

Bound args: [<fresh uuid>, 84cfc6fe-…-1c5d, 2ca61da3-…-8d9c, 'review-prep-image-999999']

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Insert on container_images  (cost=0.00..0.01 rows=1 width=104) (actual time=0.137..0.138 rows=1 loops=1)
  Conflict Resolution: NOTHING
  Conflict Arbiter Indexes: unique_container_images_ns_id_cr_id_name
  Tuples Inserted: 1
  Conflicting Tuples: 0
  Buffers: shared hit=31
  ->  Result  (cost=0.00..0.01 rows=1 width=104) (actual time=0.001..0.001 rows=1 loops=1)
Planning Time: 0.055 ms
Trigger for constraint fk_container_images_namespace_id_namespaces on container_images_p13: time=0.315 calls=1
Trigger for constraint fk_container_images_repository_id_container_repositories on container_images_p13: time=0.802 calls=1
Execution Time: 1.277 ms

Timings: planning 0.055ms, execution 1.277ms, total 1.332ms.

Marker probe (name review-prep-image-002500, a marked row):

Insert on container_images  (cost=0.00..0.01 rows=1 width=104) (actual time=0.096..0.096 rows=0 loops=1)
  Conflict Resolution: NOTHING
  Conflict Arbiter Indexes: unique_container_images_ns_id_cr_id_name
  Tuples Inserted: 0
  Conflicting Tuples: 1
  Buffers: shared hit=24
  ->  Result  (cost=0.00..0.01 rows=1 width=104) (actual time=0.001..0.001 rows=1 loops=1)
Planning Time: 0.038 ms
Execution Time: 0.107 ms
datastore.findContainerImageByTriple

Summary: Index Scan over the unique_container_images_ns_id_cr_id_name child, one partition, with soft_deleted_at IS NULL applied as the scan's Filter. The marker probe is the proof the predicate is live: bound to a marked name the same index finds the entry and the filter drops it (Rows Removed by Filter: 1, 0 rows out). No anomalies.

Rendered SQL:

SELECT container_images.id AS "container_images.id",
container_images.namespace_id AS "container_images.namespace_id",
container_images.container_repository_id AS "container_images.container_repository_id",
container_images.name AS "container_images.name"
FROM public.container_images
WHERE (((container_images.namespace_id = $1::uuid) AND (container_images.container_repository_id = $2::uuid)) AND (container_images.name = $3::text)) AND (container_images.soft_deleted_at IS NULL)
LIMIT $4

Bound args: [84cfc6fe-…-1c5d, 2ca61da3-…-8d9c, 'review-prep-image-002501', 1]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..8.30 rows=1 width=73) (actual time=0.008..0.009 rows=1 loops=1)
  Buffers: shared hit=3
  ->  Index Scan using container_images_p13_namespace_id_container_repository_id_n_idx on container_images_p13 container_images  (cost=0.28..8.30 rows=1 width=73) (actual time=0.008..0.008 rows=1 loops=1)
        Index Cond: ((namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid) AND (container_repository_id = '2ca61da3-3c05-4973-922b-4c7b6e9e8d9c'::uuid) AND (name = 'review-prep-image-002501'::text))
        Filter: (soft_deleted_at IS NULL)
        Buffers: shared hit=3
Planning:
  Buffers: shared hit=6
Planning Time: 0.093 ms
Execution Time: 0.018 ms

Timings: planning 0.093ms, execution 0.018ms, total 0.111ms.

Marker probe (name review-prep-image-002500, a marked row):

Limit  (cost=0.28..8.30 rows=1 width=73) (actual time=0.009..0.009 rows=0 loops=1)
  Buffers: shared hit=4
  ->  Index Scan using container_images_p13_namespace_id_container_repository_id_n_idx on container_images_p13 container_images  (cost=0.28..8.30 rows=1 width=73) (actual time=0.009..0.009 rows=0 loops=1)
        Index Cond: ((namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid) AND (container_repository_id = '2ca61da3-3c05-4973-922b-4c7b6e9e8d9c'::uuid) AND (name = 'review-prep-image-002500'::text))
        Filter: (soft_deleted_at IS NULL)
        Rows Removed by Filter: 1
        Buffers: shared hit=4
Planning Time: 0.048 ms
Execution Time: 0.015 ms
datastore.ContainerImageStore.FindByID

Summary: Index Scan over pk_container_images' child on the (id, namespace_id) key, one partition, marker applied as the scan Filter. The probe on a marked id removes the one matching row and returns nothing. No anomalies.

Rendered SQL:

SELECT container_images.id AS "container_images.id",
container_images.namespace_id AS "container_images.namespace_id",
container_images.container_repository_id AS "container_images.container_repository_id",
container_images.name AS "container_images.name"
FROM public.container_images
WHERE ((container_images.namespace_id = $1::uuid) AND (container_images.id = $2::uuid)) AND (container_images.soft_deleted_at IS NULL)
LIMIT $3

Bound args: [84cfc6fe-…-1c5d, 091ad9d2-…-311c (live image), 1]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..8.30 rows=1 width=73) (actual time=0.010..0.010 rows=1 loops=1)
  Buffers: shared hit=3
  ->  Index Scan using container_images_p13_pkey on container_images_p13 container_images  (cost=0.28..8.30 rows=1 width=73) (actual time=0.010..0.010 rows=1 loops=1)
        Index Cond: ((id = '091ad9d2-9fb3-4d79-9486-444ec1ec311c'::uuid) AND (namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid))
        Filter: (soft_deleted_at IS NULL)
        Buffers: shared hit=3
Planning Time: 0.048 ms
Execution Time: 0.017 ms

Timings: planning 0.048ms, execution 0.017ms, total 0.065ms.

Marker probe (id 35deb30e-…-5c50, a marked image):

Limit  (cost=0.28..8.30 rows=1 width=73) (actual time=0.008..0.008 rows=0 loops=1)
  Buffers: shared hit=4
  ->  Index Scan using container_images_p13_pkey on container_images_p13 container_images  (cost=0.28..8.30 rows=1 width=73) (actual time=0.008..0.008 rows=0 loops=1)
        Index Cond: ((id = '35deb30e-8339-4a32-a759-8c9e41da5c50'::uuid) AND (namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid))
        Filter: (soft_deleted_at IS NULL)
        Rows Removed by Filter: 1
        Buffers: shared hit=4
Planning Time: 0.038 ms
Execution Time: 0.014 ms
datastore.findContainerImageInRepositoryStmt

Summary: Same pk_container_images path as FindByID, with the repository scope and the marker riding together in the scan Filter. The probe on a marked id removes the row. This is the read the tag and manifest handlers run first, so a marked image 404s here before any child list executes. No anomalies.

Rendered SQL:

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

Bound args: [84cfc6fe-…-1c5d, 2ca61da3-…-8d9c, 091ad9d2-…-311c (live image), 1]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..8.30 rows=1 width=81) (actual time=0.006..0.006 rows=1 loops=1)
  Buffers: shared hit=3
  ->  Index Scan using container_images_p13_pkey on container_images_p13 container_images  (cost=0.28..8.30 rows=1 width=81) (actual time=0.006..0.006 rows=1 loops=1)
        Index Cond: ((id = '091ad9d2-9fb3-4d79-9486-444ec1ec311c'::uuid) AND (namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid))
        Filter: ((soft_deleted_at IS NULL) AND (container_repository_id = '2ca61da3-3c05-4973-922b-4c7b6e9e8d9c'::uuid))
        Buffers: shared hit=3
Planning:
  Buffers: shared hit=3
Planning Time: 0.060 ms
Execution Time: 0.012 ms

Timings: planning 0.060ms, execution 0.012ms, total 0.072ms.

Marker probe (id 35deb30e-…-5c50, a marked image):

Limit  (cost=0.28..8.30 rows=1 width=81) (actual time=0.007..0.007 rows=0 loops=1)
  Buffers: shared hit=4
  ->  Index Scan using container_images_p13_pkey on container_images_p13 container_images  (cost=0.28..8.30 rows=1 width=81) (actual time=0.007..0.007 rows=0 loops=1)
        Index Cond: ((id = '35deb30e-8339-4a32-a759-8c9e41da5c50'::uuid) AND (namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid))
        Filter: ((soft_deleted_at IS NULL) AND (container_repository_id = '2ca61da3-3c05-4973-922b-4c7b6e9e8d9c'::uuid))
        Rows Removed by Filter: 1
        Buffers: shared hit=4
Planning Time: 0.043 ms
Execution Time: 0.013 ms
datastore.listContainerImagesStmt.AscNoCursor

Summary: Keyset page as intended: forward Index Scan over the unique_container_images_ns_id_cr_id_name child supplies the ORDER BY name with no Sort node, and one partition is scanned. The marker is a post-index Filter (Rows Removed by Filter: 2), which is the pre-partial-index behavior the description records. No anomalies.

Rendered SQL:

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

Bound args: [84cfc6fe-…-1c5d, 2ca61da3-…-8d9c, 11]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..1.72 rows=11 width=81) (actual time=0.009..0.012 rows=11 loops=1)
  Buffers: shared hit=7
  ->  Index Scan using container_images_p13_namespace_id_container_repository_id_n_idx on container_images_p13 container_images  (cost=0.28..523.16 rows=4000 width=81) (actual time=0.009..0.012 rows=11 loops=1)
        Index Cond: ((namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid) AND (container_repository_id = '2ca61da3-3c05-4973-922b-4c7b6e9e8d9c'::uuid))
        Filter: (soft_deleted_at IS NULL)
        Rows Removed by Filter: 2
        Buffers: shared hit=7
Planning:
  Buffers: shared hit=6
Planning Time: 0.069 ms
Execution Time: 0.019 ms

Timings: planning 0.069ms, execution 0.019ms, total 0.088ms.

datastore.listContainerImagesStmt.AscCursor

Summary: The cursor bound folds into the index range (name > $3 joins the Index Cond, not a filter), so the page still costs one index descent and no Sort. The marker filter removes 2 marked rows on the way to 11 live ones. No anomalies.

Rendered SQL:

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

Bound args: [84cfc6fe-…-1c5d, 2ca61da3-…-8d9c, 'review-prep-image-002500', 11]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..2.19 rows=11 width=81) (actual time=0.012..0.015 rows=11 loops=1)
  Buffers: shared hit=7
  ->  Index Scan using container_images_p13_namespace_id_container_repository_id_n_idx on container_images_p13 container_images  (cost=0.28..347.22 rows=2000 width=81) (actual time=0.011..0.014 rows=11 loops=1)
        Index Cond: ((namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid) AND (container_repository_id = '2ca61da3-3c05-4973-922b-4c7b6e9e8d9c'::uuid) AND (name > 'review-prep-image-002500'::text))
        Filter: (soft_deleted_at IS NULL)
        Rows Removed by Filter: 2
        Buffers: shared hit=7
Planning:
  Buffers: shared hit=3
Planning Time: 0.180 ms
Execution Time: 0.023 ms

Timings: planning 0.180ms, execution 0.023ms, total 0.203ms.

datastore.listContainerImagesStmt.DescNoCursor

Summary: Descending runs as an Index Scan Backward over the same index, so the order is free in both directions and no Sort appears. Three marked rows are removed before the page fills. No anomalies.

Rendered SQL:

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

Bound args: [84cfc6fe-…-1c5d, 2ca61da3-…-8d9c, 11]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..1.72 rows=11 width=81) (actual time=0.007..0.013 rows=11 loops=1)
  Buffers: shared hit=10
  ->  Index Scan Backward using container_images_p13_namespace_id_container_repository_id_n_idx on container_images_p13 container_images  (cost=0.28..523.16 rows=4000 width=81) (actual time=0.007..0.012 rows=11 loops=1)
        Index Cond: ((namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid) AND (container_repository_id = '2ca61da3-3c05-4973-922b-4c7b6e9e8d9c'::uuid))
        Filter: (soft_deleted_at IS NULL)
        Rows Removed by Filter: 3
        Buffers: shared hit=10
Planning:
  Buffers: shared hit=5
Planning Time: 0.073 ms
Execution Time: 0.019 ms

Timings: planning 0.073ms, execution 0.019ms, total 0.092ms.

datastore.listContainerImagesStmt.DescCursor

Summary: Backward scan with the name < $3 bound in the Index Cond, mirroring the ascending cursor arm. Two marked rows removed. No anomalies.

Rendered SQL:

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

Bound args: [84cfc6fe-…-1c5d, 2ca61da3-…-8d9c, 'review-prep-image-002500', 11]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..2.19 rows=11 width=81) (actual time=0.008..0.014 rows=11 loops=1)
  Buffers: shared hit=9
  ->  Index Scan Backward using container_images_p13_namespace_id_container_repository_id_n_idx on container_images_p13 container_images  (cost=0.28..347.20 rows=1999 width=81) (actual time=0.008..0.013 rows=11 loops=1)
        Index Cond: ((namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid) AND (container_repository_id = '2ca61da3-3c05-4973-922b-4c7b6e9e8d9c'::uuid) AND (name < 'review-prep-image-002500'::text))
        Filter: (soft_deleted_at IS NULL)
        Rows Removed by Filter: 2
        Buffers: shared hit=9
Planning Time: 0.067 ms
Execution Time: 0.020 ms

Timings: planning 0.067ms, execution 0.020ms, total 0.087ms.

datastore.findBlobInRepositoryExists

Summary: SELECT EXISTS runs as an InitPlan over a Nested Loop: digest lookup on the index_container_blobs_on_namespace_id_and_digest child, then a primary-key probe of the joined image with the repository scope and the marker in its Filter. Both tables prune to one partition. The marker probe flips the answer: a digest held only by a marked image removes the joined row and the statement returns f, so the mount takes the re-upload fallback rather than mounting from a row queued for removal. No anomalies.

Rendered SQL:

SELECT EXISTS (
SELECT $1
FROM public.container_blobs
INNER JOIN public.container_images ON ((container_blobs.container_image_id = container_images.id) AND (container_blobs.namespace_id = container_images.namespace_id))
WHERE (((container_images.namespace_id = $2::uuid) AND (container_images.container_repository_id = $3::uuid)) AND (container_images.soft_deleted_at IS NULL)) AND (container_blobs.digest = $4::bytea)
)

Bound args: [1, 84cfc6fe-…-1c5d, 2ca61da3-…-8d9c, \x00…04d2 (digest held by the live image)]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Result  (cost=16.62..16.63 rows=1 width=1) (actual time=0.016..0.016 rows=1 loops=1)
  Buffers: shared hit=6
  InitPlan 1
    ->  Nested Loop  (cost=0.56..16.62 rows=1 width=0) (actual time=0.015..0.015 rows=1 loops=1)
          Buffers: shared hit=6
          ->  Index Scan using container_blobs_p13_namespace_id_digest_idx on container_blobs_p13 container_blobs  (cost=0.28..8.30 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=1)
                Index Cond: ((namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid) AND (digest = '\x00000000000000000000000000000000000000000000000000000000000004d2'::bytea))
                Buffers: shared hit=3
          ->  Index Scan using container_images_p13_pkey on container_images_p13 container_images  (cost=0.28..8.30 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=1)
                Index Cond: ((id = container_blobs.container_image_id) AND (namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid))
                Filter: ((soft_deleted_at IS NULL) AND (container_repository_id = '2ca61da3-3c05-4973-922b-4c7b6e9e8d9c'::uuid))
                Buffers: shared hit=3
Planning:
  Buffers: shared hit=24
Planning Time: 0.264 ms
Execution Time: 0.029 ms

Timings: planning 0.264ms, execution 0.029ms, total 0.293ms.

Marker probe (digest \x00…018b72, carried only by a marked image):

Result  (cost=16.62..16.63 rows=1 width=1) (actual time=0.018..0.019 rows=1 loops=1)
  Buffers: shared hit=7
  InitPlan 1
    ->  Nested Loop  (cost=0.56..16.62 rows=1 width=0) (actual time=0.018..0.018 rows=0 loops=1)
          Buffers: shared hit=7
          ->  Index Scan using container_blobs_p13_namespace_id_digest_idx on container_blobs_p13 container_blobs  (cost=0.28..8.30 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=1)
                Index Cond: ((namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid) AND (digest = '\x0000000000000000000000000000000000000000000000000000000000018b72'::bytea))
                Buffers: shared hit=3
          ->  Index Scan using container_images_p13_pkey on container_images_p13 container_images  (cost=0.28..8.30 rows=1 width=32) (actual time=0.007..0.007 rows=0 loops=1)
                Index Cond: ((id = container_blobs.container_image_id) AND (namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid))
                Filter: ((soft_deleted_at IS NULL) AND (container_repository_id = '2ca61da3-3c05-4973-922b-4c7b6e9e8d9c'::uuid))
                Rows Removed by Filter: 1
                Buffers: shared hit=4
Planning Time: 0.166 ms
Execution Time: 0.032 ms
datastore.listContainerTagsPageStmt.AscNoCursor

Summary: Keyset page over the chain join: forward Index Scan on the unique_container_tags_ns_id_ci_id_name child supplies the order with no Sort, then primary-key probes of the manifest and the image. All three tables prune to one partition each. The image probe carries the inherited soft_deleted_at IS NULL; the marker probe below shows it excluding a marked image's tags. See the note on the marked-image plan shape under the table.

Rendered SQL:

SELECT container_tags.namespace_id AS "container_tags.namespace_id",
container_tags.id AS "container_tags.id",
container_tags.container_image_id AS "container_tags.container_image_id",
container_tags.container_manifest_id AS "container_tags.container_manifest_id",
container_tags.created_at AS "container_tags.created_at",
container_tags.updated_at AS "container_tags.updated_at",
container_tags.name AS "container_tags.name",
container_manifests.digest AS "container_manifests.digest"
FROM public.container_tags
INNER JOIN public.container_manifests ON (((container_manifests.id = container_tags.container_manifest_id) AND (container_manifests.namespace_id = container_tags.namespace_id)) AND (container_manifests.container_image_id = container_tags.container_image_id))
INNER JOIN public.container_images ON (((container_images.id = container_tags.container_image_id) AND (container_images.namespace_id = container_tags.namespace_id)) AND (container_images.soft_deleted_at IS NULL))
WHERE ((container_tags.namespace_id = $1::uuid) AND (container_tags.container_image_id = $2::uuid)) AND (container_images.container_repository_id = $3::uuid)
ORDER BY container_tags.name ASC
LIMIT $4

Bound args: [84cfc6fe-…-1c5d, 091ad9d2-…-311c (live image), 2ca61da3-…-8d9c, 11]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.98..9.22 rows=11 width=136) (actual time=0.035..0.059 rows=11 loops=1)
  Buffers: shared hit=40
  ->  Nested Loop  (cost=0.98..3749.22 rows=5000 width=136) (actual time=0.035..0.058 rows=11 loops=1)
        Buffers: shared hit=40
        ->  Nested Loop  (cost=0.70..3678.41 rows=5000 width=136) (actual time=0.021..0.041 rows=11 loops=1)
              Buffers: shared hit=37
              ->  Index Scan using container_tags_p13_namespace_id_container_image_id_name_idx on container_tags_p13 container_tags  (cost=0.41..707.22 rows=5000 width=103) (actual time=0.012..0.013 rows=11 loops=1)
                    Index Cond: ((namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid) AND (container_image_id = '091ad9d2-9fb3-4d79-9486-444ec1ec311c'::uuid))
                    Buffers: shared hit=4
              ->  Index Scan using container_manifests_p13_pkey on container_manifests_p13 container_manifests  (cost=0.29..0.58 rows=1 width=81) (actual time=0.002..0.002 rows=1 loops=11)
                    Index Cond: ((id = container_tags.container_manifest_id) AND (namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid))
                    Filter: (container_image_id = '091ad9d2-9fb3-4d79-9486-444ec1ec311c'::uuid)
                    Buffers: shared hit=33
        ->  Materialize  (cost=0.28..8.31 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=11)
              Buffers: shared hit=3
              ->  Index Scan using container_images_p13_pkey on container_images_p13 container_images  (cost=0.28..8.30 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=1)
                    Index Cond: ((id = '091ad9d2-9fb3-4d79-9486-444ec1ec311c'::uuid) AND (namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid))
                    Filter: ((soft_deleted_at IS NULL) AND (container_repository_id = '2ca61da3-3c05-4973-922b-4c7b6e9e8d9c'::uuid))
                    Buffers: shared hit=3
Planning:
  Buffers: shared hit=78
Planning Time: 0.546 ms
Execution Time: 0.087 ms

Timings: planning 0.546ms, execution 0.087ms, total 0.633ms.

Marker probe (image id 35deb30e-…-5c50, a marked image):

Limit  (cost=0.98..9.22 rows=11 width=136) (actual time=5.684..5.685 rows=0 loops=1)
  Buffers: shared hit=15218
  ->  Nested Loop  (cost=0.98..3749.22 rows=5000 width=136) (actual time=5.683..5.684 rows=0 loops=1)
        Buffers: shared hit=15218
        ->  Nested Loop  (cost=0.70..3678.41 rows=5000 width=136) (actual time=0.025..5.248 rows=5000 loops=1)
              Buffers: shared hit=15214
              ->  Index Scan using container_tags_p13_namespace_id_container_image_id_name_idx on container_tags_p13 container_tags  (cost=0.41..707.22 rows=5000 width=103) (actual time=0.013..0.483 rows=5000 loops=1)
                    Index Cond: ((namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid) AND (container_image_id = '35deb30e-8339-4a32-a759-8c9e41da5c50'::uuid))
                    Buffers: shared hit=214
              ->  Index Scan using container_manifests_p13_pkey on container_manifests_p13 container_manifests  (cost=0.29..0.58 rows=1 width=81) (actual time=0.001..0.001 rows=1 loops=5000)
                    Index Cond: ((id = container_tags.container_manifest_id) AND (namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid))
                    Filter: (container_image_id = '35deb30e-8339-4a32-a759-8c9e41da5c50'::uuid)
                    Buffers: shared hit=15000
        ->  Materialize  (cost=0.28..8.31 rows=1 width=32) (actual time=0.000..0.000 rows=0 loops=5000)
              Buffers: shared hit=4
              ->  Index Scan using container_images_p13_pkey on container_images_p13 container_images  (cost=0.28..8.30 rows=1 width=32) (actual time=0.008..0.008 rows=0 loops=1)
                    Index Cond: ((id = '35deb30e-8339-4a32-a759-8c9e41da5c50'::uuid) AND (namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid))
                    Filter: ((soft_deleted_at IS NULL) AND (container_repository_id = '2ca61da3-3c05-4973-922b-4c7b6e9e8d9c'::uuid))
                    Rows Removed by Filter: 1
                    Buffers: shared hit=4
Planning:
  Buffers: shared hit=1
Planning Time: 0.295 ms
Execution Time: 5.717 ms
datastore.listContainerTagsPageStmt.AscCursor

Summary: The name > $4 bound joins the tags Index Cond, so the cursor arm keeps the same three-table shape and adds no Sort. No anomalies.

Rendered SQL:

SELECT container_tags.namespace_id AS "container_tags.namespace_id",
container_tags.id AS "container_tags.id",
container_tags.container_image_id AS "container_tags.container_image_id",
container_tags.container_manifest_id AS "container_tags.container_manifest_id",
container_tags.created_at AS "container_tags.created_at",
container_tags.updated_at AS "container_tags.updated_at",
container_tags.name AS "container_tags.name",
container_manifests.digest AS "container_manifests.digest"
FROM public.container_tags
INNER JOIN public.container_manifests ON (((container_manifests.id = container_tags.container_manifest_id) AND (container_manifests.namespace_id = container_tags.namespace_id)) AND (container_manifests.container_image_id = container_tags.container_image_id))
INNER JOIN public.container_images ON (((container_images.id = container_tags.container_image_id) AND (container_images.namespace_id = container_tags.namespace_id)) AND (container_images.soft_deleted_at IS NULL))
WHERE (((container_tags.namespace_id = $1::uuid) AND (container_tags.container_image_id = $2::uuid)) AND (container_images.container_repository_id = $3::uuid)) AND (container_tags.name > $4::text)
ORDER BY container_tags.name ASC
LIMIT $5

Bound args: [84cfc6fe-…-1c5d, 091ad9d2-…-311c, 2ca61da3-…-8d9c, 'review-prep-tag-002500', 11]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.98..12.95 rows=11 width=136) (actual time=0.038..0.060 rows=11 loops=1)
  Buffers: shared hit=41
  ->  Nested Loop  (cost=0.98..2721.36 rows=2499 width=136) (actual time=0.037..0.059 rows=11 loops=1)
        Buffers: shared hit=41
        ->  Nested Loop  (cost=0.70..2681.82 rows=2499 width=136) (actual time=0.025..0.043 rows=11 loops=1)
              Buffers: shared hit=38
              ->  Index Scan using container_tags_p13_namespace_id_container_image_id_name_idx on container_tags_p13 container_tags  (cost=0.41..504.39 rows=2499 width=103) (actual time=0.014..0.015 rows=11 loops=1)
                    Index Cond: ((namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid) AND (container_image_id = '091ad9d2-9fb3-4d79-9486-444ec1ec311c'::uuid) AND (name > 'review-prep-tag-002500'::text))
                    Buffers: shared hit=5
              ->  Index Scan using container_manifests_p13_pkey on container_manifests_p13 container_manifests  (cost=0.29..0.86 rows=1 width=81) (actual time=0.002..0.002 rows=1 loops=11)
                    Index Cond: ((id = container_tags.container_manifest_id) AND (namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid))
                    Filter: (container_image_id = '091ad9d2-9fb3-4d79-9486-444ec1ec311c'::uuid)
                    Buffers: shared hit=33
        ->  Materialize  (cost=0.28..8.31 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=11)
              Buffers: shared hit=3
              ->  Index Scan using container_images_p13_pkey on container_images_p13 container_images  (cost=0.28..8.30 rows=1 width=32) (actual time=0.006..0.007 rows=1 loops=1)
                    Index Cond: ((id = '091ad9d2-9fb3-4d79-9486-444ec1ec311c'::uuid) AND (namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid))
                    Filter: ((soft_deleted_at IS NULL) AND (container_repository_id = '2ca61da3-3c05-4973-922b-4c7b6e9e8d9c'::uuid))
                    Buffers: shared hit=3
Planning:
  Buffers: shared hit=4
Planning Time: 0.383 ms
Execution Time: 0.095 ms

Timings: planning 0.383ms, execution 0.095ms, total 0.478ms.

datastore.listContainerTagsPageStmt.DescNoCursor

Summary: Index Scan Backward on the tags index, same join shape and same single-partition pruning on all three tables. No anomalies.

Rendered SQL:

SELECT container_tags.namespace_id AS "container_tags.namespace_id",
container_tags.id AS "container_tags.id",
container_tags.container_image_id AS "container_tags.container_image_id",
container_tags.container_manifest_id AS "container_tags.container_manifest_id",
container_tags.created_at AS "container_tags.created_at",
container_tags.updated_at AS "container_tags.updated_at",
container_tags.name AS "container_tags.name",
container_manifests.digest AS "container_manifests.digest"
FROM public.container_tags
INNER JOIN public.container_manifests ON (((container_manifests.id = container_tags.container_manifest_id) AND (container_manifests.namespace_id = container_tags.namespace_id)) AND (container_manifests.container_image_id = container_tags.container_image_id))
INNER JOIN public.container_images ON (((container_images.id = container_tags.container_image_id) AND (container_images.namespace_id = container_tags.namespace_id)) AND (container_images.soft_deleted_at IS NULL))
WHERE ((container_tags.namespace_id = $1::uuid) AND (container_tags.container_image_id = $2::uuid)) AND (container_images.container_repository_id = $3::uuid)
ORDER BY container_tags.name DESC
LIMIT $4

Bound args: [84cfc6fe-…-1c5d, 091ad9d2-…-311c, 2ca61da3-…-8d9c, 11]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.98..9.22 rows=11 width=136) (actual time=0.033..0.055 rows=11 loops=1)
  Buffers: shared hit=40
  ->  Nested Loop  (cost=0.98..3749.22 rows=5000 width=136) (actual time=0.033..0.054 rows=11 loops=1)
        Buffers: shared hit=40
        ->  Nested Loop  (cost=0.70..3678.41 rows=5000 width=136) (actual time=0.025..0.044 rows=11 loops=1)
              Buffers: shared hit=37
              ->  Index Scan Backward using container_tags_p13_namespace_id_container_image_id_name_idx on container_tags_p13 container_tags  (cost=0.41..707.22 rows=5000 width=103) (actual time=0.011..0.012 rows=11 loops=1)
                    Index Cond: ((namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid) AND (container_image_id = '091ad9d2-9fb3-4d79-9486-444ec1ec311c'::uuid))
                    Buffers: shared hit=4
              ->  Index Scan using container_manifests_p13_pkey on container_manifests_p13 container_manifests  (cost=0.29..0.58 rows=1 width=81) (actual time=0.003..0.003 rows=1 loops=11)
                    Index Cond: ((id = container_tags.container_manifest_id) AND (namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid))
                    Filter: (container_image_id = '091ad9d2-9fb3-4d79-9486-444ec1ec311c'::uuid)
                    Buffers: shared hit=33
        ->  Materialize  (cost=0.28..8.31 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=11)
              Buffers: shared hit=3
              ->  Index Scan using container_images_p13_pkey on container_images_p13 container_images  (cost=0.28..8.30 rows=1 width=32) (actual time=0.005..0.006 rows=1 loops=1)
                    Index Cond: ((id = '091ad9d2-9fb3-4d79-9486-444ec1ec311c'::uuid) AND (namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid))
                    Filter: ((soft_deleted_at IS NULL) AND (container_repository_id = '2ca61da3-3c05-4973-922b-4c7b6e9e8d9c'::uuid))
                    Buffers: shared hit=3
Planning:
  Buffers: shared hit=1
Planning Time: 0.297 ms
Execution Time: 0.084 ms

Timings: planning 0.297ms, execution 0.084ms, total 0.381ms.

datastore.listContainerTagsPageStmt.DescCursor

Summary: Backward scan with the name < $4 bound in the Index Cond. No anomalies.

Rendered SQL:

SELECT container_tags.namespace_id AS "container_tags.namespace_id",
container_tags.id AS "container_tags.id",
container_tags.container_image_id AS "container_tags.container_image_id",
container_tags.container_manifest_id AS "container_tags.container_manifest_id",
container_tags.created_at AS "container_tags.created_at",
container_tags.updated_at AS "container_tags.updated_at",
container_tags.name AS "container_tags.name",
container_manifests.digest AS "container_manifests.digest"
FROM public.container_tags
INNER JOIN public.container_manifests ON (((container_manifests.id = container_tags.container_manifest_id) AND (container_manifests.namespace_id = container_tags.namespace_id)) AND (container_manifests.container_image_id = container_tags.container_image_id))
INNER JOIN public.container_images ON (((container_images.id = container_tags.container_image_id) AND (container_images.namespace_id = container_tags.namespace_id)) AND (container_images.soft_deleted_at IS NULL))
WHERE (((container_tags.namespace_id = $1::uuid) AND (container_tags.container_image_id = $2::uuid)) AND (container_images.container_repository_id = $3::uuid)) AND (container_tags.name < $4::text)
ORDER BY container_tags.name DESC
LIMIT $5

Bound args: [84cfc6fe-…-1c5d, 091ad9d2-…-311c, 2ca61da3-…-8d9c, 'review-prep-tag-002500', 11]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.98..12.95 rows=11 width=136) (actual time=0.034..0.056 rows=11 loops=1)
  Buffers: shared hit=40
  ->  Nested Loop  (cost=0.98..2721.72 rows=2500 width=136) (actual time=0.034..0.055 rows=11 loops=1)
        Buffers: shared hit=40
        ->  Nested Loop  (cost=0.70..2682.16 rows=2500 width=136) (actual time=0.023..0.041 rows=11 loops=1)
              Buffers: shared hit=37
              ->  Index Scan Backward using container_tags_p13_namespace_id_container_image_id_name_idx on container_tags_p13 container_tags  (cost=0.41..504.41 rows=2500 width=103) (actual time=0.014..0.015 rows=11 loops=1)
                    Index Cond: ((namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid) AND (container_image_id = '091ad9d2-9fb3-4d79-9486-444ec1ec311c'::uuid) AND (name < 'review-prep-tag-002500'::text))
                    Buffers: shared hit=4
              ->  Index Scan using container_manifests_p13_pkey on container_manifests_p13 container_manifests  (cost=0.29..0.86 rows=1 width=81) (actual time=0.002..0.002 rows=1 loops=11)
                    Index Cond: ((id = container_tags.container_manifest_id) AND (namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid))
                    Filter: (container_image_id = '091ad9d2-9fb3-4d79-9486-444ec1ec311c'::uuid)
                    Buffers: shared hit=33
        ->  Materialize  (cost=0.28..8.31 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=11)
              Buffers: shared hit=3
              ->  Index Scan using container_images_p13_pkey on container_images_p13 container_images  (cost=0.28..8.30 rows=1 width=32) (actual time=0.007..0.008 rows=1 loops=1)
                    Index Cond: ((id = '091ad9d2-9fb3-4d79-9486-444ec1ec311c'::uuid) AND (namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid))
                    Filter: ((soft_deleted_at IS NULL) AND (container_repository_id = '2ca61da3-3c05-4973-922b-4c7b6e9e8d9c'::uuid))
                    Buffers: shared hit=3
Planning:
  Buffers: shared hit=1
Planning Time: 0.366 ms
Execution Time: 0.092 ms

Timings: planning 0.366ms, execution 0.092ms, total 0.458ms.

datastore.findContainerTagByNameStmt

Summary: Point lookup: the full (namespace_id, container_image_id, name) key hits the unique index child, then two primary-key probes. One partition per table. The marker probe removes the image row and the statement returns nothing for a tag whose image is marked, without the drain the list arm shows, because the name equality bounds the outer side to one row. No anomalies.

Rendered SQL:

SELECT container_tags.namespace_id AS "container_tags.namespace_id",
container_tags.id AS "container_tags.id",
container_tags.container_image_id AS "container_tags.container_image_id",
container_tags.container_manifest_id AS "container_tags.container_manifest_id",
container_tags.created_at AS "container_tags.created_at",
container_tags.updated_at AS "container_tags.updated_at",
container_tags.name AS "container_tags.name",
container_manifests.digest AS "container_manifests.digest"
FROM public.container_tags
INNER JOIN public.container_manifests ON (((container_manifests.id = container_tags.container_manifest_id) AND (container_manifests.namespace_id = container_tags.namespace_id)) AND (container_manifests.container_image_id = container_tags.container_image_id))
INNER JOIN public.container_images ON (((container_images.id = container_tags.container_image_id) AND (container_images.namespace_id = container_tags.namespace_id)) AND (container_images.soft_deleted_at IS NULL))
WHERE (((container_tags.namespace_id = $1::uuid) AND (container_tags.container_image_id = $2::uuid)) AND (container_images.container_repository_id = $3::uuid)) AND (container_tags.name = $4::text)
LIMIT $5

Bound args: [84cfc6fe-…-1c5d, 091ad9d2-…-311c, 2ca61da3-…-8d9c, 'review-prep-tag-001234', 1]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.98..25.07 rows=1 width=136) (actual time=0.030..0.031 rows=1 loops=1)
  Buffers: shared hit=10
  ->  Nested Loop  (cost=0.98..25.07 rows=1 width=136) (actual time=0.030..0.030 rows=1 loops=1)
        Buffers: shared hit=10
        ->  Nested Loop  (cost=0.70..16.75 rows=1 width=136) (actual time=0.019..0.019 rows=1 loops=1)
              Buffers: shared hit=7
              ->  Index Scan using container_tags_p13_namespace_id_container_image_id_name_idx on container_tags_p13 container_tags  (cost=0.41..8.43 rows=1 width=103) (actual time=0.011..0.011 rows=1 loops=1)
                    Index Cond: ((namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid) AND (container_image_id = '091ad9d2-9fb3-4d79-9486-444ec1ec311c'::uuid) AND (name = 'review-prep-tag-001234'::text))
                    Buffers: shared hit=4
              ->  Index Scan using container_manifests_p13_pkey on container_manifests_p13 container_manifests  (cost=0.29..8.31 rows=1 width=81) (actual time=0.007..0.007 rows=1 loops=1)
                    Index Cond: ((id = container_tags.container_manifest_id) AND (namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid))
                    Filter: (container_image_id = '091ad9d2-9fb3-4d79-9486-444ec1ec311c'::uuid)
                    Buffers: shared hit=3
        ->  Index Scan using container_images_p13_pkey on container_images_p13 container_images  (cost=0.28..8.30 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=1)
              Index Cond: ((id = '091ad9d2-9fb3-4d79-9486-444ec1ec311c'::uuid) AND (namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid))
              Filter: ((soft_deleted_at IS NULL) AND (container_repository_id = '2ca61da3-3c05-4973-922b-4c7b6e9e8d9c'::uuid))
              Buffers: shared hit=3
Planning:
  Buffers: shared hit=1
Planning Time: 0.311 ms
Execution Time: 0.063 ms

Timings: planning 0.311ms, execution 0.063ms, total 0.374ms.

Marker probe (image id 35deb30e-…-5c50, a marked image, same tag name):

Limit  (cost=0.98..25.07 rows=1 width=136) (actual time=0.041..0.042 rows=0 loops=1)
  Buffers: shared hit=11
  ->  Nested Loop  (cost=0.98..25.07 rows=1 width=136) (actual time=0.041..0.041 rows=0 loops=1)
        Buffers: shared hit=11
        ->  Nested Loop  (cost=0.70..16.75 rows=1 width=136) (actual time=0.026..0.026 rows=1 loops=1)
              Buffers: shared hit=7
              ->  Index Scan using container_tags_p13_namespace_id_container_image_id_name_idx on container_tags_p13 container_tags  (cost=0.41..8.43 rows=1 width=103) (actual time=0.016..0.016 rows=1 loops=1)
                    Index Cond: ((namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid) AND (container_image_id = '35deb30e-8339-4a32-a759-8c9e41da5c50'::uuid) AND (name = 'review-prep-tag-001234'::text))
                    Buffers: shared hit=4
              ->  Index Scan using container_manifests_p13_pkey on container_manifests_p13 container_manifests  (cost=0.29..8.31 rows=1 width=81) (actual time=0.008..0.008 rows=1 loops=1)
                    Index Cond: ((id = container_tags.container_manifest_id) AND (namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid))
                    Filter: (container_image_id = '35deb30e-8339-4a32-a759-8c9e41da5c50'::uuid)
                    Buffers: shared hit=3
        ->  Index Scan using container_images_p13_pkey on container_images_p13 container_images  (cost=0.28..8.30 rows=1 width=32) (actual time=0.014..0.014 rows=0 loops=1)
              Index Cond: ((id = '35deb30e-8339-4a32-a759-8c9e41da5c50'::uuid) AND (namespace_id = '84cfc6fe-c07f-42eb-a3f1-40c94bf21c5d'::uuid))
              Filter: ((soft_deleted_at IS NULL) AND (container_repository_id = '2ca61da3-3c05-4973-922b-4c7b6e9e8d9c'::uuid))
              Rows Removed by Filter: 1
              Buffers: shared hit=4
Planning:
  Buffers: shared hit=1
Planning Time: 0.376 ms
Execution Time: 0.080 ms

Query notes:

  • No flags. Every statement reached an Index Scan, every partitioned table pruned to exactly one child, no plan carried a Sort above an index that already supplies the order, and every buffer access was a shared hit with read=0.
  • Partition-child index names in the table are the local children of the declared indexes: container_images_p13_namespace_id_container_repository_id_n_idx belongs to unique_container_images_ns_id_cr_id_name (truncated at PostgreSQL's 63-character identifier limit), container_tags_p13_namespace_id_container_image_id_name_idx to unique_container_tags_ns_id_ci_id_name, container_blobs_p13_namespace_id_digest_idx to index_container_blobs_on_namespace_id_and_digest, and each _pkey to its table's pk_ index.
  • The image list applies the marker as a post-index Filter, not as an index-excluded range, exactly as this MR's description states. The measured removals are 2 to 3 rows per page against a 20% marked population, and execution stays at 0.019 to 0.023ms. Making unique_container_images_ns_id_cr_id_name partial in the next step takes those rows out of the index and removes the filter.
  • ContainerImageStore.Delete is absent from this evidence by rule, not by omission: its statement is byte-identical to the merge-base (only its doc comment changed), so query-mode's skip rule excludes it. It is also the one container_images statement that intentionally carries no marker predicate, because removal runs through it.
  • These plans are the post-ANALYZE state. The description separately records that ADD COLUMN left soft_deleted_at with no pg_statistic row until an ANALYZE runs, and the procedure here ANALYZEs every seeded table before planning, so nothing below covers the pre-ANALYZE window.
  • The tag list bound to a marked image returns the right answer (0 rows) but drains the outer join first: 5.717ms and 15218 buffers against 0.087ms and 40 for the live-image page, because the images side yields no row and the LIMIT never fills. This plan shape predates the MR. Bound to a live image and a mismatched container_repository_id, the merge-base statement drains identically (5.744ms, 15253 buffers) and so does this MR's (5.622ms, 15253 buffers), so the marker adds a new way to reach an already-reachable shape at the same cost. It is also guarded in the handler: handleContainerTagList resolves the image through FindContainerImageInRepository, which this MR filters, so a marked image 404s before the list statement runs. Only a mark landing between those two reads reaches the drain, and it returns an empty page rather than an error.

Related to #313 (closed)

Edited by Hayley Swimelar

Merge request reports