feat(datastore): reap the three uncovered container remote tables

Pawel Rozlachrequested to merge
prozlach/container-remote-reap-three-tables into main

What this does

Closes all three tables #690 names. Each gets a parent-keyed DELETE of its own, at the level whose parent row it pins:

Table Reached by Keyed on
container_remote_tags both reapers the manifest, at the manifest level; the image, at the image level
container_remote_manifest_relationships both reapers the manifest on either side, at the manifest level; the image, at the image level
container_remote_blobs the image reaper the image

container_remote_blobs holds no key onto container_remote_manifests, which is why it has no manifest-level arm — the manifest reap is not a statement any blob row can refuse.

Three container remote-cache tables had no reap arm: container_remote_blobs, container_remote_tags and container_remote_manifest_relationships. None of the foreign keys in this family declares an ON DELETE action, so a surviving row in any of them refuses the parent delete with SQLSTATE 23503 and takes the whole chunk transaction with it — the manifest and attachment deletes in that chunk roll back too, and a retry repeats the work rather than continuing from it.

The issue's correction to the plan text is confirmed against internal/datastore/migrations/structure.sql. Four foreign keys reference container_remote_images, all composite over (container_remote_image_id, namespace_id), and none carries an ON DELETE action. Nothing here is reaped by a foreign key, so each table needs its own parent-keyed DELETE.

  • ContainerRemoteManifestReaper.Reap clears the container_remote_tags rows pointing at its manifest and the container_remote_manifest_relationships rows naming it on either side, then deletes the manifest row. Both keys pin it, and which side applies depends on whether the reaped digest is an index or an image an index lists.
  • ContainerRemoteImageReaper.Reap drains four child legs in the order tags, relationships, manifests, blobs, then takes the image row on a call that found all four empty.

The attachment rule for container_remote_blobs

container_remote_blobs carries its own NOT NULL blob_storage_attachment_id, and the doc on DeleteIfUnreferencedByContainerRemoteManifest left the choice open between an arm there and a guard of its own.

This adds a guard of its own, DeleteIfUnreferencedByContainerRemoteBlob, with one NOT EXISTS arm over container_remote_blobs. Three reasons:

  • Nothing mints an attachment two tables share. BlobStorageAttachmentStore.Create is a bare INSERT ... RETURNING against a sequence with no ON CONFLICT, and UpsertCacheFill mints one per fill, so a widened arm would carry plan cost on every chunk for a state no writer produces.
  • It keeps each guard answering for the one table its caller deleted from, which is the shape the Maven and npm guards already take.
  • The failure stays loud. An attachment the other table also held raises 23503 from the guard's DELETE rather than reporting 0, and that behaviour is already pinned by an existing subtest, which this change keeps green rather than rewriting.

index_container_remote_blobs_on_ns_id_bsa_id backs the correlation, so the arm reads one partition.

Bounding

limit is one budget shared across the four legs rather than a page per leg, which is what Reaper's contract asks of an arm that walks more than one child table. Each attachment freed with a manifest or blob row sits on top of the budget, and at most one goes per row, so the documented 2*limit ceiling per call is unchanged. At the manifest level the pins are collateral of the one artifact row, so limit does not bound them, and nothing else does either. One call there reports 2 + t + r rows, where t is the tag rows naming the manifest and r the relationship rows naming it on either side. t is uncapped: unique_container_remote_tags_ns_id_image_id_name keys on (namespace_id, container_remote_image_id, name), so a fill writes one row per tag name requested and any number of names under one image may address one digest. container.manifest_max_tags has two readers and both are hosted: ContainerManifestPersister on the push path, and ContainerTagUpserter on the management-API tag write. Each caps container_tags, and no writer on the cache-fill path reads the value, so ADR-004's tags-per-version cap — the premise ADR-007 rests the hosted container arm's exemption on — does not hold here, and borrowing that arm's bounded-fan-in argument would cite a precedent this path does not meet. What holds the statement is the caller's context deadline, which bounds the time rather than the rows. r is zero in practice, because no writer records a container_remote_manifest_relationships row. ContainerRemoteManifestReaper.Reap now states that ceiling on its own doc, and Reaper's contract in lifecycle_scan.go names the third condition its 2*limit sentence rests on, so an arm that fails it is required to state its own.

One shape aborts on the image legs, and it is documented

A tag or relationship row whose container_remote_image_id names a different image than the manifest it points at is reached by some of these statements and not others, because each keys on a different column. The manifest reap takes it: its two pin deletes key on the manifest with no image conjunct. The image reap of the image the row names takes it, because its tag and relationship legs key on exactly that column. The image reap of the image the manifest sits under does not, because all four of its legs key on that image, and the row then pins a manifest that reap is trying to remove. No writer mints one — the cache fill writes the tag under the same image id it upserted the manifest under, and nothing records a relationship row at all. On the image legs it is left to abort rather than reached by a second predicate, on the terms DeleteIfUnreferencedByContainerRemoteManifest already sets for the same class of unmintable row.

Tests

  • Positive hits for all five tables the two reapers remove rows from, with both states covered on every table that carries soft_deleted_at and both relationship sides covered at the manifest level.
  • A shared-budget test that fails against a per-leg page.
  • Explain tests for the five new statements and for the new guard, asserting one partition per table.
  • A mirrored suite for DeleteIfUnreferencedByContainerRemoteBlob, subtest for subtest against the manifest guard's, plus a marked-row case that table's soft_deleted_at column makes reachable.
  • A live manifest handed to the reaper is refused whole, with both pin tables seeded. The tag and relationship deletes run ahead of the manifest delete, so ungated they would strip a live row's tags on a call reporting zero totals. Measured: dropping the gate reddens that subtest.
  • The two abort subtests whose statements this change fixes become positive hits, and ErrReapParentPinned is asserted in two places instead, one per reaper. TestContainerRemoteManifestReaper_RefusesAConcurrentlyPinnedManifest covers the manifest reaper's arm against a real database: a second transaction commits a container_remote_tags row under the manifest mid-chunk, the startBlocked lock-wait harness forces the interleaving, and PostgreSQL raises the 23503. Measured: replacing mapReapParentDeleteError with a plain wrap reddens that test and nothing else. TestDeleteContainerRemoteImage_MapsAForeignKeyViolation covers the image reaper's arm with a synthesized *pgconn.PgError in the untagged file, so that half runs with no database behind it.

Verification, all local:

  • go test ./internal/datastore/ ./internal/metrics/ — pass.
  • go test -tags=integration ./internal/datastore/ -run 'TestContainerRemote…' — every new subtest passes. Two pre-existing subtests failed in cleanup with SQLSTATE 53200 (out of shared memory) under the full run and pass in isolation; that is the known structural contention on this host, not this change.
  • golangci-lint run and golangci-lint run --build-tags=integration, both with --max-same-issues=0 --max-issues-per-linter=0 --uniq-by-line=false. The untagged pass is clean. Against a baseline run on main restricted to the files this MR touches, the tagged pass adds no finding and removes two: the three gosec G202 sites in the reap fixture file now carry one suppression each, where two of them were unsuppressed before. New //nolint tokens were each measured by dropping them and watching the finding return.

Two notes for whoever reviews or merges this:

  • The 53200 failures in a full-package integration run are not this change. Two pre-existing subtests fail in cleanup with SQLSTATE 53200, out of shared memory, and pass in isolation. That is the known structural contention of 42 hash-partitioned tables against the local lock-slot budget, and it reproduces on main.
  • !1805 also edits internal/datastore/query_names.go. The two changes add constants to different table sections of the same const block, so whichever merges second may need a trivial textual rebase. No name and no value overlaps.

No e2e scenario is affected. Nothing dispatches these reapers from a running server yet — the chunk driver is a later step — so no user-level behaviour in docs/testing/ changes.

Diff size

2335 added, 447 removed, past the 500 reviewable LOC the development model asks about. Split by file group:

Group Added Removed
Production 949 184
Tests 1386 263

Splitting would not help. The production change is one file's four legs plus one guard method, and any split leaves a half-fix on main that still cannot purge a container remote repository — the tag arm without the blob arm aborts on layers, and the blob arm without the tag arm aborts on tags. The test half is the larger one and is fixed by guardrail 6: five tables owed a positive hit, and the guard owed a mirrored suite.

Database Review Evidence

Queries

Note

Plans are from EXPLAIN (ANALYZE, BUFFERS) against an ephemeral PostgreSQL 17.10 container (matching GL_PG_CURR_VERSION from .gitlab-ci-other-versions.yml), with synthesized seed data rolled back per query and the container torn down at the end of the run. Numbers reflect moderate cardinality and do not capture production-scale effects. See Database review evidence for seed sizing, methodology, and the anomalies the skill flags. Expand each row's details for the seed shape, rendered SQL, bound args, and raw plan.

Method Plan node Index Rows (plan / actual) Cost Time Buffers (hit / read) Partitions
datastore.BlobStorageAttachmentStore.DeleteIfUnreferencedByContainerRemoteBlob Delete → Nested Loop Anti Join index_blob_storage_attachments_on_namespace_id_and_sha256, index_container_remote_blobs_on_ns_id_bsa_id 0 / 0 (anti join 1 / 1) 16.62 21.333ms 69 / 0 1/64, 1/64
datastore.deleteContainerRemoteBlobsUnderImageStmt Delete → Hash Semi Join index_container_remote_blobs_on_ns_id_image_id 100 / 100 232.83 1.292ms 388 / 0 1/64, 1/64
datastore.deleteContainerRemoteManifestsUnderImageStmt Delete → Hash Semi Join index_container_remote_manifests_on_ns_id_image_id_last_dl_at 100 / 100 300.72 72.429ms 590 / 0 1/64, 1/64
datastore.deleteContainerRemoteRelationshipsOnManifestStmt Delete → Bitmap Heap Scan (BitmapOr) unique_crmr_ns_id_parent_manifest_id_child_manifest_id, index_crmr_on_ns_id_child_manifest_id 0 / 0 (driving scan 6 / 6) 35.65 0.338ms 15 / 0 1/64, 1/64
datastore.deleteContainerRemoteRelationshipsUnderImageStmt Delete → Hash Semi Join index_crmr_on_ns_id_image_id 0 / 0 (driving scan 100 / 100) 181.82 1.045ms 173 / 0 1/64, 1/64
datastore.deleteContainerRemoteTagsOnManifestStmt Delete → Index Scan index_container_remote_tags_on_ns_id_manifest_id 0 / 0 (driving scan 6 / 6) 25.45 0.365ms 13 / 0 1/64, 1/64
datastore.deleteContainerRemoteTagsUnderImageStmt Delete → Hash Semi Join unique_container_remote_tags_ns_id_image_id_name 0 / 0 (driving scan 100 / 100) 207.96 1.417ms 242 / 0 1/64, 1/64
datastore.deleteTombstonedContainerRemoteManifestStmt Delete → Index Scan pk_container_remote_manifests 1 / 1 8.30 1.016ms 25 / 0 1/64

Every statement pruned to one partition of every partitioned table it reached. No statement shows partition fan-out.

Query notes:

  • Outer leg of the four *UnderImage chunk deletes: the seeded partition holds 5000 rows, and the planner answers id IN (<chunk>) with a Hash Semi Join over a full scan of that partition rather than with the primary key. A scale probe settles the reading. At 100,005 rows in the same partition, with the target image still at 255 rows, the planner picks a Nested Loop over pk_container_remote_tags and execution falls from 1.417ms to 0.601ms. The chunk cost therefore tracks the limit value and not the partition size. The 5000-row plan is an artifact of the seed size.
  • datastore.deleteContainerRemoteManifestsUnderImageStmt: foreign-key re-check triggers take 71.0ms of the statement's 72.429ms for a 100-row chunk. Three triggers fire 100 times each, one per inbound key from container_remote_tags and from both sides of container_remote_manifest_relationships. The scan work below the Delete node is 0.992ms.
  • datastore.BlobStorageAttachmentStore.DeleteIfUnreferencedByContainerRemoteBlob: ten foreign-key re-check triggers take 20.7ms of the statement's 21.333ms for a single row, one per table that references blob_storage_attachments. The guard's own Anti Join is 0.048ms. A repeat measurement of 100 sequential calls in one transaction takes 694ms, which is 6.9ms per freed attachment once the trigger plans are cached. freeReapedAttachments calls the guard once per reaped row, so a 100-row chunk pays about 0.7s of trigger work for the attachment leg alone.
  • datastore.deleteContainerRemoteTagsOnManifestStmt and datastore.deleteContainerRemoteRelationshipsOnManifestStmt carry no bound. Both doc comments state this and give the reading. The fixture puts 6 rows behind each, so the plans do not exercise a large collateral set.
datastore.BlobStorageAttachmentStore.DeleteIfUnreferencedByContainerRemoteBlob

Summary: The plan matches the method's intent. The delete prunes to blob_storage_attachments_p00 on the bound sha256, reaches the row through index_blob_storage_attachments_on_namespace_id_and_sha256, and answers the NOT EXISTS arm with index_container_remote_blobs_on_ns_id_bsa_id as a Nested Loop Anti Join. Both indexes are the ones the doc comment names, and both tables prune to one of 64 partitions. The one anomaly is cost distribution rather than plan shape: ten foreign-key re-check triggers take 20.7ms of the 21.333ms execution time, against 0.048ms for the guard itself.

Seed shape: namespaces=1, repositories=1, container_remote_repositories=1, container_remote_images=20, blob_storage_blobs=15000, blob_storage_attachments=15000, container_remote_manifests=5000, container_remote_blobs=5000, container_remote_tags=5005, container_remote_manifest_relationships=4985

5000 of the attachment rows carry a sha256 that hashes to partition remainder 0, so the target partition holds 5163 rows. The bound attachment is one of them and no container_remote_blobs row references it.

Rendered SQL:

DELETE FROM blob_storage_attachments bsa
	WHERE bsa.namespace_id = $1 AND bsa.id = $2 AND bsa.sha256 = $3
	AND NOT EXISTS (
		SELECT 1 FROM container_remote_blobs ref
		WHERE ref.namespace_id = bsa.namespace_id AND ref.blob_storage_attachment_id = bsa.id
	)

Bound args: ['dd60a305-f6a7-4532-9b96-50ccae1cdc1e', 10001, '\x0000000000000000000000000000000000000000000000000000000000030d53']

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Delete on blob_storage_attachments bsa  (cost=0.56..16.62 rows=0 width=0) (actual time=0.379..0.380 rows=0 loops=1)
   Delete on blob_storage_attachments_p00 bsa_1
   Buffers: shared hit=69 dirtied=1
   ->  Nested Loop Anti Join  (cost=0.56..16.62 rows=1 width=20) (actual time=0.047..0.048 rows=1 loops=1)
         Buffers: shared hit=5
         ->  Index Scan using blob_storage_attachments_p00_namespace_id_sha256_idx on blob_storage_attachments_p00 bsa_1  (cost=0.28..8.30 rows=1 width=34) (actual time=0.026..0.027 rows=1 loops=1)
               Index Cond: ((namespace_id = 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e'::uuid) AND (sha256 = '\x0000000000000000000000000000000000000000000000000000000000030d53'::bytea))
               Filter: (id = '10001'::bigint)
               Buffers: shared hit=3
         ->  Index Scan using container_remote_blobs_p50_namespace_id_blob_storage_attach_idx on container_remote_blobs_p50 ref  (cost=0.28..8.30 rows=1 width=34) (actual time=0.020..0.020 rows=0 loops=1)
               Index Cond: ((namespace_id = 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e'::uuid) AND (blob_storage_attachment_id = '10001'::bigint))
               Buffers: shared hit=2
 Planning:
   Buffers: shared hit=989
 Planning Time: 3.989 ms
 Trigger for constraint container_blobs_blob_storage_attachment_id_namespace_id_bl_fkey on blob_storage_attachments_p00: time=2.326 calls=1
 Trigger for constraint container_manifests_blob_storage_attachment_id_namespace_i_fkey on blob_storage_attachments_p00: time=3.091 calls=1
 Trigger for constraint npm_files_blob_storage_attachment_id_namespace_id_blob_sha_fkey on blob_storage_attachments_p00: time=2.102 calls=1
 Trigger for constraint npm_metadata_files_blob_storage_attachment_id_namespace_id_fkey on blob_storage_attachments_p00: time=1.732 calls=1
 Trigger for constraint maven_files_blob_storage_attachment_id_namespace_id_blob_s_fkey on blob_storage_attachments_p00: time=2.145 calls=1
 Trigger for constraint npm_remote_metadata_files_blob_storage_attachment_id_names_fkey on blob_storage_attachments_p00: time=1.879 calls=1
 Trigger for constraint npm_remote_files_blob_storage_attachment_id_namespace_id_b_fkey on blob_storage_attachments_p00: time=1.993 calls=1
 Trigger for constraint maven_remote_files_blob_storage_attachment_id_namespace_id_fkey on blob_storage_attachments_p00: time=2.582 calls=1
 Trigger for constraint container_remote_manifests_blob_storage_attachment_id_name_fkey on blob_storage_attachments_p00: time=2.655 calls=1
 Trigger for constraint container_remote_blobs_blob_storage_attachment_id_namespac_fkey on blob_storage_attachments_p00: time=0.209 calls=1
 Execution Time: 21.333 ms

Timings: planning 3.989ms, execution 21.333ms, total 25.322ms.

datastore.deleteContainerRemoteBlobsUnderImageStmt

Summary: The plan matches the method's intent. The chunk subquery uses index_container_remote_blobs_on_ns_id_image_id, which is the index the doc comment names, and the EXISTS gate prunes container_remote_images to one partition at plan time. container_remote_blobs prunes to one of 64 partitions. The outer id IN (<chunk>) leg scans the 5000-row partition instead of the primary key, which the scale probe in the query notes attributes to the seed size.

Seed shape: namespaces=1, repositories=1, container_remote_repositories=1, container_remote_images=20, blob_storage_blobs=15000, blob_storage_attachments=15000, container_remote_manifests=5000, container_remote_blobs=5000, container_remote_tags=5005, container_remote_manifest_relationships=4985

The 5000 container_remote_blobs rows spread over 20 images, so the target image holds 250 of them.

Rendered SQL:

DELETE FROM public.container_remote_blobs
WHERE ((container_remote_blobs.namespace_id = $1::uuid) AND (container_remote_blobs.id IN ((
           SELECT reap_chunk.id AS "reap_chunk.id"
           FROM public.container_remote_blobs AS reap_chunk
           WHERE (reap_chunk.namespace_id = $2::uuid) AND (reap_chunk.container_remote_image_id = $3::uuid)
           LIMIT $4
      )))) AND (EXISTS (
           SELECT container_remote_images.id AS "container_remote_images.id"
           FROM public.container_remote_images
           WHERE (container_remote_images.namespace_id = $5::uuid) AND (container_remote_images.id = $6::uuid)
      ))
RETURNING container_remote_blobs.blob_storage_attachment_id AS "reaped_attachment.id",
          container_remote_blobs.blob_sha256 AS "reaped_attachment.sha256";

Bound args: ['dd60a305-f6a7-4532-9b96-50ccae1cdc1e', 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e', '4510b0ad-a947-4a99-ab5e-84d4ae47370e', 100, 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e', '4510b0ad-a947-4a99-ab5e-84d4ae47370e']

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Delete on container_remote_blobs  (cost=53.09..232.83 rows=100 width=50) (actual time=0.236..1.027 rows=100 loops=1)
   Delete on container_remote_blobs_p50 container_remote_blobs_1
   Buffers: shared hit=388
   InitPlan 1
     ->  Seq Scan on container_remote_images_p50 container_remote_images  (cost=0.00..1.30 rows=1 width=0) (actual time=0.011..0.011 rows=1 loops=1)
           Filter: ((namespace_id = 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e'::uuid) AND (id = '4510b0ad-a947-4a99-ab5e-84d4ae47370e'::uuid))
           Buffers: shared hit=1
   ->  Result  (cost=51.80..231.53 rows=100 width=50) (actual time=0.204..0.814 rows=100 loops=1)
         One-Time Filter: (InitPlan 1).col1
         Buffers: shared hit=147
         ->  Hash Semi Join  (cost=51.80..231.53 rows=100 width=50) (actual time=0.192..0.797 rows=100 loops=1)
               Hash Cond: (container_remote_blobs_1.id = "ANY_subquery"."reap_chunk.id")
               Buffers: shared hit=146
               ->  Seq Scan on container_remote_blobs_p50 container_remote_blobs_1  (cost=0.00..165.50 rows=5000 width=26) (actual time=0.003..0.396 rows=5000 loops=1)
                     Filter: (namespace_id = 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e'::uuid)
                     Buffers: shared hit=103
               ->  Hash  (cost=50.55..50.55 rows=100 width=56) (actual time=0.179..0.180 rows=100 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 17kB
                     Buffers: shared hit=43
                     ->  Subquery Scan on "ANY_subquery"  (cost=6.84..50.55 rows=100 width=56) (actual time=0.052..0.160 rows=100 loops=1)
                           Buffers: shared hit=43
                           ->  Limit  (cost=6.84..49.55 rows=100 width=16) (actual time=0.041..0.140 rows=100 loops=1)
                                 Buffers: shared hit=43
                                 ->  Bitmap Heap Scan on container_remote_blobs_p50 reap_chunk  (cost=6.84..113.59 rows=250 width=16) (actual time=0.040..0.136 rows=100 loops=1)
                                       Recheck Cond: ((namespace_id = 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e'::uuid) AND (container_remote_image_id = '4510b0ad-a947-4a99-ab5e-84d4ae47370e'::uuid))
                                       Heap Blocks: exact=41
                                       Buffers: shared hit=43
                                       ->  Bitmap Index Scan on container_remote_blobs_p50_namespace_id_container_remote_i_idx1  (cost=0.00..6.78 rows=250 width=0) (actual time=0.026..0.026 rows=250 loops=1)
                                             Index Cond: ((namespace_id = 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e'::uuid) AND (container_remote_image_id = '4510b0ad-a947-4a99-ab5e-84d4ae47370e'::uuid))
                                             Buffers: shared hit=2
 Planning:
   Buffers: shared hit=1031 dirtied=3
 Planning Time: 4.375 ms
 Execution Time: 1.292 ms

Timings: planning 4.375ms, execution 1.292ms, total 5.667ms.

datastore.deleteContainerRemoteManifestsUnderImageStmt

Summary: The plan matches the method's intent. The chunk subquery uses index_container_remote_manifests_on_ns_id_image_id_last_dl_at, which is the index the doc comment names, and both container_remote_manifests and container_remote_images prune to one of 64 partitions. The statement's cost is dominated by the three foreign-key re-check triggers rather than by its own scans: 71.0ms of 72.429ms, against 0.992ms below the Delete node. That cost tracks the chunk size, because each trigger fires once per deleted row.

Seed shape: namespaces=1, repositories=1, container_remote_repositories=1, container_remote_images=20, blob_storage_blobs=15000, blob_storage_attachments=15000, container_remote_manifests=5000, container_remote_blobs=5000, container_remote_tags=5005, container_remote_manifest_relationships=4985

The transaction first drains the tag leg (255 rows) and the relationship leg (254 rows) under the target image, which is the order the image reap runs its legs in. Without that prologue the manifests delete is refused with SQLSTATE 23503.

Rendered SQL:

DELETE FROM public.container_remote_manifests
WHERE ((container_remote_manifests.namespace_id = $1::uuid) AND (container_remote_manifests.id IN ((
           SELECT reap_chunk.id AS "reap_chunk.id"
           FROM public.container_remote_manifests AS reap_chunk
           WHERE (reap_chunk.namespace_id = $2::uuid) AND (reap_chunk.container_remote_image_id = $3::uuid)
           LIMIT $4
      )))) AND (EXISTS (
           SELECT container_remote_images.id AS "container_remote_images.id"
           FROM public.container_remote_images
           WHERE (container_remote_images.namespace_id = $5::uuid) AND (container_remote_images.id = $6::uuid)
      ))
RETURNING container_remote_manifests.blob_storage_attachment_id AS "reaped_attachment.id",
          container_remote_manifests.blob_sha256 AS "reaped_attachment.sha256";

Bound args: ['dd60a305-f6a7-4532-9b96-50ccae1cdc1e', 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e', '4510b0ad-a947-4a99-ab5e-84d4ae47370e', 100, 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e', '4510b0ad-a947-4a99-ab5e-84d4ae47370e']

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Delete on container_remote_manifests  (cost=73.98..300.72 rows=100 width=50) (actual time=0.402..1.242 rows=100 loops=1)
   Delete on container_remote_manifests_p50 container_remote_manifests_1
   Buffers: shared hit=590
   InitPlan 1
     ->  Seq Scan on container_remote_images_p50 container_remote_images  (cost=0.00..1.30 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=1)
           Filter: ((namespace_id = 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e'::uuid) AND (id = '4510b0ad-a947-4a99-ab5e-84d4ae47370e'::uuid))
           Buffers: shared hit=1
   ->  Result  (cost=72.68..299.42 rows=100 width=50) (actual time=0.247..0.992 rows=100 loops=1)
         One-Time Filter: (InitPlan 1).col1
         Buffers: shared hit=212
         ->  Hash Semi Join  (cost=72.68..299.42 rows=100 width=50) (actual time=0.241..0.981 rows=100 loops=1)
               Hash Cond: (container_remote_manifests_1.id = "ANY_subquery"."reap_chunk.id")
               Buffers: shared hit=211
               ->  Seq Scan on container_remote_manifests_p50 container_remote_manifests_1  (cost=0.00..212.50 rows=5000 width=26) (actual time=0.003..0.526 rows=5000 loops=1)
                     Filter: (namespace_id = 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e'::uuid)
                     Buffers: shared hit=150
               ->  Hash  (cost=71.43..71.43 rows=100 width=56) (actual time=0.212..0.213 rows=100 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 17kB
                     Buffers: shared hit=61
                     ->  Subquery Scan on "ANY_subquery"  (cost=6.84..71.43 rows=100 width=56) (actual time=0.050..0.195 rows=100 loops=1)
                           Buffers: shared hit=61
                           ->  Limit  (cost=6.84..70.43 rows=100 width=16) (actual time=0.040..0.176 rows=100 loops=1)
                                 Buffers: shared hit=61
                                 ->  Bitmap Heap Scan on container_remote_manifests_p50 reap_chunk  (cost=6.84..165.81 rows=250 width=16) (actual time=0.039..0.171 rows=100 loops=1)
                                       Recheck Cond: ((namespace_id = 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e'::uuid) AND (container_remote_image_id = '4510b0ad-a947-4a99-ab5e-84d4ae47370e'::uuid))
                                       Heap Blocks: exact=59
                                       Buffers: shared hit=61
                                       ->  Bitmap Index Scan on container_remote_manifests_p_namespace_id_container_remot_idx60  (cost=0.00..6.78 rows=250 width=0) (actual time=0.027..0.027 rows=250 loops=1)
                                             Index Cond: ((namespace_id = 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e'::uuid) AND (container_remote_image_id = '4510b0ad-a947-4a99-ab5e-84d4ae47370e'::uuid))
                                             Buffers: shared hit=2
 Planning:
   Buffers: shared hit=1081 dirtied=1
 Planning Time: 5.012 ms
 Trigger for constraint container_remote_tags_container_remote_manifest_id_names_fkey50 on container_remote_manifests_p50: time=30.536 calls=100
 Trigger for constraint container_remote_manifest_re_parent_container_remote_man_fkey50 on container_remote_manifests_p50: time=33.126 calls=100
 Trigger for constraint container_remote_manifest_re_child_container_remote_mani_fkey50 on container_remote_manifests_p50: time=7.336 calls=100
 Execution Time: 72.429 ms

Timings: planning 5.012ms, execution 72.429ms, total 77.441ms.

datastore.deleteContainerRemoteRelationshipsOnManifestStmt

Summary: The plan matches the method's intent. PostgreSQL answers the parent-or-child disjunction as a BitmapOr of unique_crmr_ns_id_parent_manifest_id_child_manifest_id and index_crmr_on_ns_id_child_manifest_id, which is exactly what the doc comment claims. The EXISTS gate runs once as an InitPlan over pk_container_remote_manifests, so the tombstone test costs one index probe. Both tables prune to one of 64 partitions and estimates match actuals at 6 rows.

Seed shape: namespaces=1, repositories=1, container_remote_repositories=1, container_remote_images=20, blob_storage_blobs=15000, blob_storage_attachments=15000, container_remote_manifests=5000, container_remote_blobs=5000, container_remote_tags=5005, container_remote_manifest_relationships=4985

Six relationship rows name the target manifest, three on each side.

Rendered SQL:

DELETE FROM public.container_remote_manifest_relationships
WHERE ((container_remote_manifest_relationships.namespace_id = $1::uuid) AND ((container_remote_manifest_relationships.parent_container_remote_manifest_id = $2::uuid) OR (container_remote_manifest_relationships.child_container_remote_manifest_id = $3::uuid))) AND (EXISTS (
           SELECT container_remote_manifests.id AS "container_remote_manifests.id"
           FROM public.container_remote_manifests
           WHERE ((container_remote_manifests.namespace_id = $4::uuid) AND (container_remote_manifests.id = $5::uuid)) AND (container_remote_manifests.soft_deleted_at IS NOT NULL)
      ));

Bound args: ['dd60a305-f6a7-4532-9b96-50ccae1cdc1e', '00d32458-748b-4826-9c25-c71ca24dbf04', '00d32458-748b-4826-9c25-c71ca24dbf04', 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e', '00d32458-748b-4826-9c25-c71ca24dbf04']

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Delete on container_remote_manifest_relationships  (cost=16.93..35.65 rows=0 width=0) (actual time=0.086..0.087 rows=0 loops=1)
   Delete on container_remote_manifest_relationships_p50 container_remote_manifest_relationships_1
   Buffers: shared hit=15
   InitPlan 1
     ->  Index Scan using container_remote_manifests_p50_pkey on container_remote_manifests_p50 container_remote_manifests  (cost=0.28..8.30 rows=1 width=0) (actual time=0.024..0.025 rows=1 loops=1)
           Index Cond: ((id = '00d32458-748b-4826-9c25-c71ca24dbf04'::uuid) AND (namespace_id = 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e'::uuid))
           Filter: (soft_deleted_at IS NOT NULL)
           Buffers: shared hit=3
   ->  Result  (cost=8.63..27.35 rows=6 width=10) (actual time=0.065..0.072 rows=6 loops=1)
         One-Time Filter: (InitPlan 1).col1
         Buffers: shared hit=9
         ->  Bitmap Heap Scan on container_remote_manifest_relationships_p50 container_remote_manifest_relationships_1  (cost=8.63..27.35 rows=6 width=10) (actual time=0.039..0.046 rows=6 loops=1)
               Recheck Cond: (((namespace_id = 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e'::uuid) AND (parent_container_remote_manifest_id = '00d32458-748b-4826-9c25-c71ca24dbf04'::uuid)) OR ((namespace_id = 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e'::uuid) AND (child_container_remote_manifest_id = '00d32458-748b-4826-9c25-c71ca24dbf04'::uuid)))
               Heap Blocks: exact=2
               Buffers: shared hit=6
               ->  BitmapOr  (cost=8.63..8.63 rows=6 width=0) (actual time=0.021..0.022 rows=0 loops=1)
                     Buffers: shared hit=4
                     ->  Bitmap Index Scan on container_remote_manifest_re_namespace_id_parent_containe_idx50  (cost=0.00..4.31 rows=3 width=0) (actual time=0.018..0.018 rows=3 loops=1)
                           Index Cond: ((namespace_id = 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e'::uuid) AND (parent_container_remote_manifest_id = '00d32458-748b-4826-9c25-c71ca24dbf04'::uuid))
                           Buffers: shared hit=2
                     ->  Bitmap Index Scan on container_remote_manifest_re_namespace_id_child_container_idx50  (cost=0.00..4.31 rows=3 width=0) (actual time=0.003..0.003 rows=3 loops=1)
                           Index Cond: ((namespace_id = 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e'::uuid) AND (child_container_remote_manifest_id = '00d32458-748b-4826-9c25-c71ca24dbf04'::uuid))
                           Buffers: shared hit=2
 Planning:
   Buffers: shared hit=1007
 Planning Time: 4.144 ms
 Execution Time: 0.338 ms

Timings: planning 4.144ms, execution 0.338ms, total 4.482ms.

datastore.deleteContainerRemoteRelationshipsUnderImageStmt

Summary: The plan matches the method's intent. The chunk subquery uses index_crmr_on_ns_id_image_id, which is the index the doc comment names, and both container_remote_manifest_relationships and container_remote_images prune to one of 64 partitions. The EXISTS gate on the image row runs once as an InitPlan. The outer id IN (<chunk>) leg scans the 4985-row partition instead of the primary key, which the scale probe in the query notes attributes to the seed size.

Seed shape: namespaces=1, repositories=1, container_remote_repositories=1, container_remote_images=20, blob_storage_blobs=15000, blob_storage_attachments=15000, container_remote_manifests=5000, container_remote_blobs=5000, container_remote_tags=5005, container_remote_manifest_relationships=4985

The relationship rows spread over 20 images, so the target image holds 254 of them.

Rendered SQL:

DELETE FROM public.container_remote_manifest_relationships
WHERE ((container_remote_manifest_relationships.namespace_id = $1::uuid) AND (container_remote_manifest_relationships.id IN ((
           SELECT reap_chunk.id AS "reap_chunk.id"
           FROM public.container_remote_manifest_relationships AS reap_chunk
           WHERE (reap_chunk.namespace_id = $2::uuid) AND (reap_chunk.container_remote_image_id = $3::uuid)
           LIMIT $4
      )))) AND (EXISTS (
           SELECT container_remote_images.id AS "container_remote_images.id"
           FROM public.container_remote_images
           WHERE (container_remote_images.namespace_id = $5::uuid) AND (container_remote_images.id = $6::uuid)
      ));

Bound args: ['dd60a305-f6a7-4532-9b96-50ccae1cdc1e', 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e', '4510b0ad-a947-4a99-ab5e-84d4ae47370e', 100, 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e', '4510b0ad-a947-4a99-ab5e-84d4ae47370e']

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Delete on container_remote_manifest_relationships  (cost=38.31..181.82 rows=0 width=0) (actual time=0.738..0.740 rows=0 loops=1)
   Delete on container_remote_manifest_relationships_p50 container_remote_manifest_relationships_1
   Buffers: shared hit=173
   InitPlan 1
     ->  Seq Scan on container_remote_images_p50 container_remote_images  (cost=0.00..1.30 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=1)
           Filter: ((namespace_id = 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e'::uuid) AND (id = '4510b0ad-a947-4a99-ab5e-84d4ae47370e'::uuid))
           Buffers: shared hit=1
   ->  Result  (cost=37.01..180.52 rows=100 width=50) (actual time=0.244..0.687 rows=100 loops=1)
         One-Time Filter: (InitPlan 1).col1
         Buffers: shared hit=72
         ->  Hash Semi Join  (cost=37.01..180.52 rows=100 width=50) (actual time=0.233..0.671 rows=100 loops=1)
               Hash Cond: (container_remote_manifest_relationships_1.id = "ANY_subquery"."reap_chunk.id")
               Buffers: shared hit=71
               ->  Seq Scan on container_remote_manifest_relationships_p50 container_remote_manifest_relationships_1  (cost=0.00..129.31 rows=4985 width=26) (actual time=0.004..0.378 rows=4985 loops=1)
                     Filter: (namespace_id = 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e'::uuid)
                     Buffers: shared hit=67
               ->  Hash  (cost=35.76..35.76 rows=100 width=56) (actual time=0.077..0.077 rows=100 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 17kB
                     Buffers: shared hit=4
                     ->  Subquery Scan on "ANY_subquery"  (cost=6.89..35.76 rows=100 width=56) (actual time=0.043..0.061 rows=100 loops=1)
                           Buffers: shared hit=4
                           ->  Limit  (cost=6.89..34.76 rows=100 width=16) (actual time=0.032..0.042 rows=100 loops=1)
                                 Buffers: shared hit=4
                                 ->  Bitmap Heap Scan on container_remote_manifest_relationships_p50 reap_chunk  (cost=6.89..77.70 rows=254 width=16) (actual time=0.031..0.038 rows=100 loops=1)
                                       Recheck Cond: ((namespace_id = 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e'::uuid) AND (container_remote_image_id = '4510b0ad-a947-4a99-ab5e-84d4ae47370e'::uuid))
                                       Heap Blocks: exact=2
                                       Buffers: shared hit=4
                                       ->  Bitmap Index Scan on container_remote_manifest_re_namespace_id_container_remot_idx50  (cost=0.00..6.82 rows=254 width=0) (actual time=0.022..0.022 rows=254 loops=1)
                                             Index Cond: ((namespace_id = 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e'::uuid) AND (container_remote_image_id = '4510b0ad-a947-4a99-ab5e-84d4ae47370e'::uuid))
                                             Buffers: shared hit=2
 Planning:
   Buffers: shared hit=956
 Planning Time: 4.328 ms
 Execution Time: 1.045 ms

Timings: planning 4.328ms, execution 1.045ms, total 5.373ms.

datastore.deleteContainerRemoteTagsOnManifestStmt

Summary: The plan matches the method's intent. The delete reaches the tag rows through index_container_remote_tags_on_ns_id_manifest_id, which is the index the doc comment names, and the tombstone gate runs once as an InitPlan over pk_container_remote_manifests. Both tables prune to one of 64 partitions and estimates match actuals at 6 rows. The statement takes no bound, which its doc comment states and explains.

Seed shape: namespaces=1, repositories=1, container_remote_repositories=1, container_remote_images=20, blob_storage_blobs=15000, blob_storage_attachments=15000, container_remote_manifests=5000, container_remote_blobs=5000, container_remote_tags=5005, container_remote_manifest_relationships=4985

Six tag rows point at the target manifest.

Rendered SQL:

DELETE FROM public.container_remote_tags
WHERE ((container_remote_tags.namespace_id = $1::uuid) AND (container_remote_tags.container_remote_manifest_id = $2::uuid)) AND (EXISTS (
           SELECT container_remote_manifests.id AS "container_remote_manifests.id"
           FROM public.container_remote_manifests
           WHERE ((container_remote_manifests.namespace_id = $3::uuid) AND (container_remote_manifests.id = $4::uuid)) AND (container_remote_manifests.soft_deleted_at IS NOT NULL)
      ));

Bound args: ['dd60a305-f6a7-4532-9b96-50ccae1cdc1e', '00d32458-748b-4826-9c25-c71ca24dbf04', 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e', '00d32458-748b-4826-9c25-c71ca24dbf04']

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Delete on container_remote_tags  (cost=8.59..25.45 rows=0 width=0) (actual time=0.077..0.078 rows=0 loops=1)
   Delete on container_remote_tags_p50 container_remote_tags_1
   Buffers: shared hit=13
   InitPlan 1
     ->  Index Scan using container_remote_manifests_p50_pkey on container_remote_manifests_p50 container_remote_manifests  (cost=0.28..8.30 rows=1 width=0) (actual time=0.036..0.036 rows=1 loops=1)
           Index Cond: ((id = '00d32458-748b-4826-9c25-c71ca24dbf04'::uuid) AND (namespace_id = 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e'::uuid))
           Filter: (soft_deleted_at IS NOT NULL)
           Buffers: shared hit=3
   ->  Result  (cost=0.28..17.15 rows=6 width=10) (actual time=0.055..0.063 rows=6 loops=1)
         One-Time Filter: (InitPlan 1).col1
         Buffers: shared hit=7
         ->  Index Scan using container_remote_tags_p50_namespace_id_container_remote_man_idx on container_remote_tags_p50 container_remote_tags_1  (cost=0.28..17.15 rows=6 width=10) (actual time=0.018..0.025 rows=6 loops=1)
               Index Cond: ((namespace_id = 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e'::uuid) AND (container_remote_manifest_id = '00d32458-748b-4826-9c25-c71ca24dbf04'::uuid))
               Buffers: shared hit=4
 Planning:
   Buffers: shared hit=992
 Planning Time: 6.524 ms
 Execution Time: 0.365 ms

Timings: planning 6.524ms, execution 0.365ms, total 6.889ms.

datastore.deleteContainerRemoteTagsUnderImageStmt

Summary: The plan matches the method's intent. The chunk subquery uses unique_container_remote_tags_ns_id_image_id_name, which is the index the doc comment names, and both container_remote_tags and container_remote_images prune to one of 64 partitions. The outer id IN (<chunk>) leg scans the 5005-row partition instead of the primary key. A scale probe on this statement, described in the query notes, shows the planner switching to a Nested Loop over pk_container_remote_tags at 100,005 rows.

Seed shape: namespaces=1, repositories=1, container_remote_repositories=1, container_remote_images=20, blob_storage_blobs=15000, blob_storage_attachments=15000, container_remote_manifests=5000, container_remote_blobs=5000, container_remote_tags=5005, container_remote_manifest_relationships=4985

The tag rows spread over 20 images, so the target image holds 255 of them.

Rendered SQL:

DELETE FROM public.container_remote_tags
WHERE ((container_remote_tags.namespace_id = $1::uuid) AND (container_remote_tags.id IN ((
           SELECT reap_chunk.id AS "reap_chunk.id"
           FROM public.container_remote_tags AS reap_chunk
           WHERE (reap_chunk.namespace_id = $2::uuid) AND (reap_chunk.container_remote_image_id = $3::uuid)
           LIMIT $4
      )))) AND (EXISTS (
           SELECT container_remote_images.id AS "container_remote_images.id"
           FROM public.container_remote_images
           WHERE (container_remote_images.namespace_id = $5::uuid) AND (container_remote_images.id = $6::uuid)
      ));

Bound args: ['dd60a305-f6a7-4532-9b96-50ccae1cdc1e', 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e', '4510b0ad-a947-4a99-ab5e-84d4ae47370e', 100, 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e', '4510b0ad-a947-4a99-ab5e-84d4ae47370e']

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Delete on container_remote_tags  (cost=54.14..207.96 rows=0 width=0) (actual time=1.103..1.109 rows=0 loops=1)
   Delete on container_remote_tags_p50 container_remote_tags_1
   Buffers: shared hit=242
   InitPlan 1
     ->  Seq Scan on container_remote_images_p50 container_remote_images  (cost=0.00..1.30 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=1)
           Filter: ((namespace_id = 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e'::uuid) AND (id = '4510b0ad-a947-4a99-ab5e-84d4ae47370e'::uuid))
           Buffers: shared hit=1
   ->  Result  (cost=52.84..206.66 rows=100 width=50) (actual time=0.195..0.956 rows=100 loops=1)
         One-Time Filter: (InitPlan 1).col1
         Buffers: shared hit=113
         ->  Hash Semi Join  (cost=52.84..206.66 rows=100 width=50) (actual time=0.184..0.939 rows=100 loops=1)
               Hash Cond: (container_remote_tags_1.id = "ANY_subquery"."reap_chunk.id")
               Buffers: shared hit=112
               ->  Seq Scan on container_remote_tags_p50 container_remote_tags_1  (cost=0.00..139.56 rows=5005 width=26) (actual time=0.014..0.461 rows=5005 loops=1)
                     Filter: (namespace_id = 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e'::uuid)
                     Buffers: shared hit=77
               ->  Hash  (cost=51.59..51.59 rows=100 width=56) (actual time=0.155..0.159 rows=100 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 17kB
                     Buffers: shared hit=35
                     ->  Subquery Scan on "ANY_subquery"  (cost=18.90..51.59 rows=100 width=56) (actual time=0.062..0.140 rows=100 loops=1)
                           Buffers: shared hit=35
                           ->  Limit  (cost=18.90..50.59 rows=100 width=16) (actual time=0.053..0.122 rows=100 loops=1)
                                 Buffers: shared hit=35
                                 ->  Bitmap Heap Scan on container_remote_tags_p50 reap_chunk  (cost=18.90..99.72 rows=255 width=16) (actual time=0.052..0.117 rows=100 loops=1)
                                       Recheck Cond: ((namespace_id = 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e'::uuid) AND (container_remote_image_id = '4510b0ad-a947-4a99-ab5e-84d4ae47370e'::uuid))
                                       Heap Blocks: exact=30
                                       Buffers: shared hit=35
                                       ->  Bitmap Index Scan on container_remote_tags_p50_namespace_id_container_remote_ima_idx  (cost=0.00..18.83 rows=255 width=0) (actual time=0.045..0.045 rows=255 loops=1)
                                             Index Cond: ((namespace_id = 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e'::uuid) AND (container_remote_image_id = '4510b0ad-a947-4a99-ab5e-84d4ae47370e'::uuid))
                                             Buffers: shared hit=5
 Planning:
   Buffers: shared hit=942 dirtied=2
 Planning Time: 4.346 ms
 Execution Time: 1.417 ms

Scale probe on the same statement, after growing the namespace's tag rows to 100,005 while the target image keeps its 255:

 Delete on container_remote_tags  (cost=172.42..981.75 rows=0 width=0) (actual time=0.317..0.318 rows=0 loops=1)
   Delete on container_remote_tags_p50 container_remote_tags_1
   Buffers: shared hit=534
   InitPlan 1
     ->  Seq Scan on container_remote_images_p50 container_remote_images  (cost=0.00..1.30 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=1)
           Filter: ((namespace_id = 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e'::uuid) AND (id = '4510b0ad-a947-4a99-ab5e-84d4ae47370e'::uuid))
           Buffers: shared hit=1
   ->  Result  (cost=171.12..980.45 rows=100 width=50) (actual time=0.091..0.290 rows=100 loops=1)
         One-Time Filter: (InitPlan 1).col1
         Buffers: shared hit=434
         ->  Nested Loop  (cost=171.12..980.45 rows=100 width=50) (actual time=0.085..0.279 rows=100 loops=1)
               Buffers: shared hit=433
               ->  HashAggregate  (cost=170.70..171.70 rows=100 width=56) (actual time=0.072..0.078 rows=100 loops=1)
                     Group Key: "ANY_subquery"."reap_chunk.id"
                     Batches: 1  Memory Usage: 32kB
                     Buffers: shared hit=33
                     ->  Subquery Scan on "ANY_subquery"  (cost=0.42..170.45 rows=100 width=56) (actual time=0.018..0.056 rows=100 loops=1)
                           Buffers: shared hit=33
                           ->  Limit  (cost=0.42..169.45 rows=100 width=16) (actual time=0.013..0.042 rows=100 loops=1)
                                 Buffers: shared hit=33
                                 ->  Index Scan using container_remote_tags_p50_namespace_id_container_remote_ima_idx on container_remote_tags_p50 reap_chunk  (cost=0.42..468.64 rows=277 width=16) (actual time=0.013..0.038 rows=100 loops=1)
                                       Index Cond: ((namespace_id = 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e'::uuid) AND (container_remote_image_id = '4510b0ad-a947-4a99-ab5e-84d4ae47370e'::uuid))
                                       Buffers: shared hit=33
               ->  Index Scan using container_remote_tags_p50_pkey on container_remote_tags_p50 container_remote_tags_1  (cost=0.42..8.08 rows=1 width=26) (actual time=0.002..0.002 rows=1 loops=100)
                     Index Cond: ((id = "ANY_subquery"."reap_chunk.id") AND (namespace_id = 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e'::uuid))
                     Buffers: shared hit=400
 Planning:
   Buffers: shared hit=50
 Planning Time: 0.572 ms
 Execution Time: 0.601 ms

Timings: planning 4.346ms, execution 1.417ms, total 5.763ms.

datastore.deleteTombstonedContainerRemoteManifestStmt

Summary: The plan matches the method's intent. The delete addresses the one row through pk_container_remote_manifests and applies soft_deleted_at IS NOT NULL as a filter on that row, which is the tombstone test the doc comment describes. namespace_id prunes the statement to one of 64 partitions, and the estimate matches the actual at 1 row. Three foreign-key re-check triggers fire once each and take 0.754ms of the 1.016ms execution time.

Seed shape: namespaces=1, repositories=1, container_remote_repositories=1, container_remote_images=20, blob_storage_blobs=15000, blob_storage_attachments=15000, container_remote_manifests=5000, container_remote_blobs=5000, container_remote_tags=5005, container_remote_manifest_relationships=4985

500 of the manifest rows carry a tombstone. The transaction first runs the two pin deletes that deleteContainerRemoteManifestPins issues, each removing 6 rows. Without that prologue the manifest delete is refused with SQLSTATE 23503.

Rendered SQL:

DELETE FROM public.container_remote_manifests
WHERE ((container_remote_manifests.namespace_id = $1::uuid) AND (container_remote_manifests.id = $2::uuid)) AND (container_remote_manifests.soft_deleted_at IS NOT NULL)
RETURNING container_remote_manifests.blob_storage_attachment_id AS "reaped_attachment.id",
          container_remote_manifests.blob_sha256 AS "reaped_attachment.sha256";

Bound args: ['dd60a305-f6a7-4532-9b96-50ccae1cdc1e', '00d32458-748b-4826-9c25-c71ca24dbf04']

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Delete on container_remote_manifests  (cost=0.28..8.30 rows=1 width=10) (actual time=0.157..0.157 rows=1 loops=1)
   Delete on container_remote_manifests_p50 container_remote_manifests_1
   Buffers: shared hit=25
   ->  Index Scan using container_remote_manifests_p50_pkey on container_remote_manifests_p50 container_remote_manifests_1  (cost=0.28..8.30 rows=1 width=10) (actual time=0.025..0.025 rows=1 loops=1)
         Index Cond: ((id = '00d32458-748b-4826-9c25-c71ca24dbf04'::uuid) AND (namespace_id = 'dd60a305-f6a7-4532-9b96-50ccae1cdc1e'::uuid))
         Filter: (soft_deleted_at IS NOT NULL)
         Buffers: shared hit=3
 Planning:
   Buffers: shared hit=555
 Planning Time: 2.460 ms
 Trigger for constraint container_remote_tags_container_remote_manifest_id_names_fkey50 on container_remote_manifests_p50: time=0.410 calls=1
 Trigger for constraint container_remote_manifest_re_parent_container_remote_man_fkey50 on container_remote_manifests_p50: time=0.186 calls=1
 Trigger for constraint container_remote_manifest_re_child_container_remote_mani_fkey50 on container_remote_manifests_p50: time=0.158 calls=1
 Execution Time: 1.016 ms

Timings: planning 2.460ms, execution 1.016ms, total 3.476ms.

Related to #690

Edited by Pawel Rozlach

Merge request reports