fix(datastore): report the hosted container reap's freed bytes

Pawel Rozlachrequested to merge
prozlach/issue-942-container-reaper-freed-bytes into main

What this delivers

ContainerReaper.Reap now names the repository it reaped and reports the bytes its own chunk freed. Before this change it reported neither.

The cause

A hosted OCI push raises repositories.size_bytes, because wireOCI builds an oci.CounterSink. The reap side reported no repository and no byte figure. The size gate at internal/lifecycle/emit.go:34 reads chunk.Rows != 0 && chunk.SizeBytes != 0 && chunk.RepositoryID != uuid.Nil, so it stayed closed for every container chunk. An artifact purge of a hosted OCI image therefore emitted the component delta alone, and repositories.size_bytes kept the value the push put on it. The column read high from the purge until the next reconciliation pass, which recomputes it from source rather than adjusting it.

What the code now does

  • Reap resolves the repository once per chunk, through resolveReapRepository on a new container_images route. The resolve runs after reapManifests returns and before reapImageRow, the one statement that removes the row this chain starts at.
  • Both legs collect the addresses they freed, and sumFreedRepositoryBytes sums them over a new statement for the hosted container family. The probe keys on blob_sha256 rather than the OCI digest. It counts an address only where no surviving row of the same repository still carries it.
  • Each leg contributes an address only for a row its own delete removed. The manifest leg takes DeleteManifestOutcome.PayloadSHA256 on a Deleted answer. The blob leg takes container_blobs.blob_sha256 on the unlink's answer, and reapBlobPageStmt now projects that column beside the digest. A delete race that another transaction wins therefore charges no bytes twice.

ReapTotals gains no field. RepositoryID and SizeBytes were already declared on it, and the emit gate opens as soon as the arm fills them.

docs/dev/storage-accounting.md and docs/specs/S20-a-lifecycle-closed-beta.md move the container arm out of the group that computes no byte figure. NpmRemoteVersionReaper is the one member left in that group.

Test coverage

Rows come from the issue card's own acceptance list — no plan governs, and the spec states the rule rather than a numbered criterion for this arm. The Source column names the ## Done when bullet each item was derived from.

Test keys:

  T1  TestContainerReaper_Reap_ReportsTheRepositoryScopedBytes (new)
  T2  TestArtifactReapers_Reap_NameNoRepositoryOnADrainedSubtree (+row)
  T3  TestReapSizeStmts_EveryAliasBindsThePartitionKey (+2 rows)
  T4  TestContainerReaper_Reap_ChunkBound (2 assertions updated)
  T5  TestContainerReaper_Reap_ArtifactsAlreadyGone (assertion updated)
  T6  TestEmitChunkCounters_IssuesTheDeltasTheChunkOwes (merged)
  T7  TestResolveReapRepository_AnswersWithoutAStatementWhenTheRowDeclines
  T8  TestRepositoryReaper_Reap_WalksEveryFormatAndKindArm (merged)

| #  | Card acceptance item | Source | Tests |
|----|----------------------|--------|-------|
| 1  | RepositoryID is the image's repositories.id | DW1 | T1, T4, T5 |
| 2  | A digest no surviving row carries frees its bytes | DW1 | T1, T3 |
| 3  | A digest a survivor carries frees zero, names the repo | DW1 | T1, T3 |
| 4  | A copy in another repository holds nothing alive | DW1 | T1 |
| 5  | A tombstoned but unpurged survivor holds the digest | DW1 | T1 |
| 6  | One page carrying one digest twice counts it once | DW1 | T1 |
| 7  | Declining row, unknown id: uuid.Nil and no bytes | DW1 | T2, T7, T8 |
| 8  | SizeBytes non-zero implies RepositoryID non-nil | DW1 | T1, T2 |
| 9  | The emit gate opens for a container chunk | DW4 | T6 + rows 1-2 |
| 10 | storage-accounting.md container-arm text reads true | DW3 | none: docs |
| 11 | Out of scope: the npm arm, and "neither arm" | DW3 | none: by design |

Row 9 needs no new emit case. The emit decision is a pure function of the ReapTotals the chunk reports, and T6 covers it generically. Rows 1 and 2 are what open both of the gate's remaining conjuncts. Row 10 is the documentation obligation. Row 11 is the scope this merge request excludes, and the issue link below says what remains.

The e2e scenario catalog

No scenario is added, and none is edited. An existing scenario becomes true instead.

docs/testing/e2e/oci.md:152 (e2e.oci.lifecycle.delete-storage-counters) and docs/testing/e2e/docker.md:127 (e2e.docker.lifecycle.delete-storage-counters) each already read "while size_bytes holds until the reap frees the rows". Before this change the reap moved no bytes on that column, so both scenarios named an outcome the reap did not produce. This change is what makes the clause hold, so the catalogs need no new row and no edit.

Diff size

docs/dev/development-model.md asks for a split or a justification past 500 reviewable lines. This diff is 823 changed lines across 8 files, measured at b014e7e5c.

Group Lines
Tests 528
Production Go 165
Docs 130
Total 823

64 percent of the diff is tests, and the production change is 165 lines. A split does not help here. If the tests move to a merge request of their own, the conversion arrives without what holds its answer apart from a plausible wrong one. If the documents move, the conversion arrives without the contract it now meets.

Merge order and base

  • The branch is rebased onto a5c3759b5, which already carries the sibling MavenPackageReaper arm from work item 1012. The S20-A bullet in this diff therefore records two arms leaving the first group: this one and the Maven package arm. The rebase reconciled them, because the sibling merge left main's own copy of the bullet internally inconsistent.
  • !2123, the write side of the npm remote cache fill, is merged at 96d8532b5. The spec and document text naming CacheStore.emitCommittedFillCounters is true at this base.
  • The branch commit keeps its feat(datastore) subject on purpose: this project squash-merges, so the fix(datastore) title is what reaches main.

Every verdict below was measured at 2026-09-03T14:25Z with git merge-tree --write-tree --name-only, against this branch at b014e7e5c and against origin/main at dad725fa6. A verdict is a claim about the two heads it was measured at rather than a standing property of the merge request: !2262 moved from 6e8803f31 to ed6eaa332 to 665250a79 inside the day this section was written.

Merge request Head Against this branch Against origin/main
!2262 feat(oci): container remote fill emits its storage-accounting deltas 665250a79 Conflicts: docs/dev/storage-accounting.md, docs/specs/S20-a-lifecycle-closed-beta.md Conflicts: docs/dev/storage-accounting.md, internal/format/oci/emit_dispatch.go
!2232 fix(npm): credit the freed packument-cache bytes on every delete arm 413315345 Conflicts: docs/dev/storage-accounting.md Clean

Both are open and neither is a draft. This branch is clean against origin/main.

Merge order in docs/dev/storage-accounting.md

Both siblings contend here. !2232's conflict belongs to this branch, because !2232 is clean against origin/main. !2262's does not belong to this branch alone: !2262 conflicts with its own target branch on the same path, so it carries a rebase of its own, and this measurement does not settle whether the pair still conflicts after that rebase.

The resolution against !2262 keeps both facts. !2262 establishes that a container remote cache fill raises repositories.size_bytes, and this branch establishes that one artifact-scope arm is left reporting nothing. Both are true after both land, so neither statement replaces the other.

Against !2232 the two changes are adjacent rather than exclusive. !2232 inserts sentences after the pair this branch rewrites, and its text reads correctly against the rewritten pair.

Merge order in docs/specs/S20-a-lifecycle-closed-beta.md

This conflict belongs to this branch: !2262 and origin/main do not contend on this path.

The reap-arms bullet does not take the keep-both resolution. Both merge requests rewrite the same count sentence in ways that exclude each other. This branch moves ContainerReaper out of the first group and writes "one from one side and two from the other". !2262 moves ContainerRemoteManifestReaper out of the second and writes "two from one side and one from the other". After both land the count is two arms, one from each side: NpmRemoteVersionReaper and NpmPackageReaper. Whoever lands second recounts that sentence rather than keeping both hunks.

Two further lines sit in the same hunk:

  • The work-item sentences below the bullet do split cleanly, one from each side. This branch's #942 sentence covers NpmRemoteVersionReaper, and !2262's #939 sentence covers NpmPackageReaper.
  • The Closing this takes ... sentence is wrong from both sides. This branch reads "the missing increments and the one remaining conversion" and !2262 reads "the one missing increment and the two remaining conversions", where after both land it is one missing increment and one remaining conversion.

Merge order in internal/datastore/query_names.go

The third path both merge requests change, and it merges clean against both. This branch adds queryBlobStorageBlobsByNamespaceSelectReapFreedSizeContainerHosted and queryContainerImagesSelectReapRepository, and neither name is among !2262's additions. internal/metrics/cardinality.go caps labelName at 600 and records in its own comment that no count is inlined and that the audit reads observed values, so a merged catalog is not gated on a declared figure.

The plan guardrail

AGENTS.md guardrail 3 asks for a merged plan merge request before an implementation merge request opens. This work has no plan under docs/plans/. The merged plan for this spec does name the arm, to exclude it: docs/plans/2026-08-11-s20a-lifecycle-closed-beta.md carries ContainerReaper at :1210, :1211 and :1213. Those three lines sit in the Step 19a scope block that opens at :1206, and :1215 hands the arm to this issue. The operator settled that deviation on 2026-08-25.

Seen and left unchanged

Each row was checked on its own evidence, and none of them is an oversight. Every line number here is measured at b014e7e5c.

Site What is left Why
docs/specs/S20-a-lifecycle-closed-beta.md, the sentence that groups ContainerRemoteManifestReaper with NpmPackageReaper The ground it states is false for NpmPackageReaper. An npm packument write does raise the column, through emitRebuildCounters in internal/format/npm/packument_cache.go:1395, wired at cmd/artifact-registry/wire_npm.go:123-:126. docs/dev/storage-accounting.md:2011 says the same, so the two inherited lines disagree. The container remote half of the same sentence holds while !2262 is open. Once !2262 merges, a container remote cache fill raises the column too, and the sentence has two false halves. !2262 at 665250a79 does edit this spec, and its new ContainerRemoteManifestReaper sentence is that repair; the merge-order section above records how the two hunks compose The sentence is inherited from main, and the operator chose to leave the inherited lines as they are. A plus-and-minus scan of the spec diff matches neither line
The closing sentence of the same bullet, which names work items 834 and 939 Inherited from main in the same way The same decision covers it. This merge request takes no position on either work item
ADR 007 :498-:499, against two migrations already at the merge base The ADR says the container tables carry no (namespace_id, blob_sha256) index and accepts a partition scan. Two migrations add such indexes, and docs/ records no amendment. Work item #762 tracks the amendment This branch does not deviate. Its two survivor arms read container_blobs.blob_sha256 and container_manifests.blob_sha256 under namespace_id. internal/datastore/container_blob_footprint.go:100-:125 already does that at the merge base, so the branch adds a consumer of those indexes
docs/dev/storage-accounting.md:1999 It claims that an arm which reports a family zero says so in its own Reap doc. The :2001 rewrite narrows that group to NpmRemoteVersionReaper, whose Reap doc does not say it The claim was already false at the base for both members of the group. This diff removes one false instance and leaves the other, so it introduces nothing
The two range guards in internal/datastore/lifecycle_reap_size.go checkReapRepositoryResolveArgs and checkReapSizeSumArgs bind by hand against whichever constant is last, and each out-of-range test case is written the same way. Nothing enforces that pairing The claim is narrow and covers the guards alone. The dispatch half is lint-enforced: exhaustive runs repo-wide and neither switch carries a default clause, so an unhandled route or family fails lint
docs/specs/S20-a-lifecycle-closed-beta.md:1065 It reads that work item 1012 closes with the merge request that lands the figure. That merge request is merged and 1012 is closed The sentence names a condition and its event, which is the form the project asks for. The line is outside this diff

Evidence

Against a running service. A push, a management delete and the purge net repositories.size_bytes back to its pre-push value. The column reads 0, then 543, then 543 while the tombstone stands, then 0 after the purge. That 543 is the pushed footprint exactly: config 105 plus layer 37 plus manifest payload 401. The buffered delta was read out of Redis before the drain claimed it, as size -543 on the repository-scoped hash. Before this change the emit gate wrote no repository-scoped hash at all, which is the signature of the defect.

The survivor case, on the same run. A second repository holds two images pushed from byte-identical content. A purge of one image leaves size_bytes at 550, unmoved, and writes no repository-scoped hash. The surviving image still carries every address that chunk freed.

The blob leg's discipline, proved by mutation. The blob leg appends an address only inside its deleted guard. A run with that guard taken out fails exactly one test, the lost-race case. That case gives the arm an unlinker which removes the row and answers deleted == false. No surviving row of the repository holds the address. The survivor probe therefore cannot keep those bytes out of the figure, and only the unlink's own answer can.

Suite. The full internal/datastore integration suite is green at this tree. A full run of that package sits at or past Go's 600 s default timeout, so a re-run needs an explicit -timeout.

Related to #942 (closed)

What remains is the NpmRemoteVersionReaper arm, which is ## Done when bullet 2, and the "neither arm" half of bullet 3 that goes with it. This merge request converts the ContainerReaper arm alone, and it excludes the npm arm deliberately. That arm's stated precondition is discharged, because its write side !2123 is merged, so the remaining half is actionable rather than blocked.

Database Review Evidence

Every plan in this section was measured at 4bf6368f5, which this branch's rebase carried to 760514dcd. The nine commits between that commit and 11424185f change five files, and only internal/datastore/lifecycle_reap_container.go is production Go. Its whole diff is a renamed interface parameter, one corrected comment, and cascadeLayer converted to named returns. reapBlobPageStmt is byte-identical across the two commits, and internal/datastore/lifecycle_reap_size.go, internal/datastore/query_names.go and internal/datastore/migrations/ are unchanged, so no statement, index or partition the plans rest on moved.

Queries

Note

Plans are from EXPLAIN (ANALYZE, BUFFERS) against an ephemeral PostgreSQL 17.10 container (matching GL_PG_CURR_VERSION: "17" 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.reapBlobPageStmt Limit → Index Scan unique_container_blobs_ns_id_ci_id_digest 10 / 10 20.23 0.016ms 3 / 0 1/64 container_blobs
datastore.resolveReapRepository.ContainerImage Limit → Nested Loop pk_container_images 1 / 1 9.33 0.018ms 4 / 0 1/64 container_images, 1/64 container_repositories
datastore.sumFreedRepositoryBytes.ContainerHosted Aggregate → Nested Loop over two Anti Joins index_container_blobs_on_ns_id_blob_sha256, index_container_manifests_on_ns_id_blob_sha256, pk_container_images, pk_container_repositories, pk_blob_storage_blobs_by_namespace 1 / 1 107.01 0.209ms 85 / 0 1/64 container_blobs, 1/64 container_manifests, 1/64 container_images, 1/64 container_repositories, 1/64 blob_storage_blobs_by_namespace

Every partitioned table in every plan pruned to one partition of 64. No statement reaches a second partition, so no fan-out warning applies.

Query notes:

  • datastore.sumFreedRepositoryBytes.ContainerHosted: the page size decides the plan shape. At 50 addresses the container_blobs NOT EXISTS runs as an index-driven Nested Loop Anti Join over index_container_blobs_on_ns_id_blob_sha256. From 100 addresses up it becomes a Hash Anti Join whose build side sequentially scans a whole container_blobs partition and joins it to container_images. lifecycle.DefaultPurgeChunkSize is 100 at internal/lifecycle/purge.go:445, and cmd/artifact-registry/wire_lifecycle.go:70-:74 passes no ChunkSize, so internal/lifecycle/purge.go:536-:537 fills that zero with the default. A full chunk therefore lands on the scanning side, and MaxLifecycleReapPageSize is a 1000-row ceiling rather than the size production runs. Execution then tracks the scanned partition's own row count rather than the page size. The times are 0.335 ms at 50 addresses, 2.508 ms at 100, 2.524 ms at 200 and 4.294 ms at 1000, over one namespace seeded with 4990 container_blobs rows. That sweep seeded one namespace per partition, so the partition's row count and the namespace's coincide in these plans and the turn locates a share-of-partition threshold rather than an absolute address count. In general the two differ: container_blobs and container_images are each PARTITION BY HASH (namespace_id) over 64 partitions in internal/datastore/migrations/structure.sql, and ADR 007 prices a partition as a fleet-scale quantity inferred from the node type and the partition count rather than measured. Below the threshold the governing term is the reaped namespace's own rows, which cb.namespace_id = $1 and the namespace-leading index_container_blobs_on_ns_id_blob_sha256 give the planner a path to, so a partition holding other tenants moves the threshold toward larger reaps rather than making the term fleet-wide at any size. Pruning holds at 1/64 in every variant, so the exposure is the partition's own size and not a fan-out. The crossover is a cost ratio between the bound array and the table, so a larger table raises it. Production need not turn at a hundred addresses at all, and the plan shape is what the measurement establishes rather than the threshold.
  • datastore.resolveReapRepository.ContainerImage: under the skill's one-row-per-ancestor seed the container_repositories arm reads Seq Scan, because that partition holds a single row. A re-run with 500 container_repositories rows in the namespace takes pk_container_repositories on that arm and leaves the rest of the plan unchanged, at 6 shared hits and 0.053 ms execution. The Seq Scan in the table above is a seed artifact rather than a property of the statement.
  • Planning cost is visible on the sum: 6.690 ms planning against 0.379 ms execution on the session's first plan, from plan-time pruning across five hash-partitioned tables. A repeat of the same prepared statement in the same session plans in 0.817 ms, so the figure amortizes over a pooled connection's prepared-statement cache rather than recurring per reap chunk.
datastore.reapBlobPageStmt

Summary: The plan matches the method's intent. The namespace equality prunes container_blobs to one partition of 64, and unique_container_blobs_ns_id_ci_id_digest answers both equalities from the index, with the LIMIT stopping the scan at 10 rows. The column this change adds, blob_sha256, is not in that index — but neither is the already-projected id, so the read was a heap fetch before the change and stays one: no new buffer class, 3 shared hits for a 10-row page. No anomalies.

Seed shape: namespaces=1, repositories=1, container_repositories=1, container_images=5000, blob_storage_blobs=5000, blob_storage_blobs_by_namespace=5000 (trigger), blob_storage_attachments=5000, container_manifests=4990, container_blobs=4990

Rendered SQL:

SELECT container_blobs.namespace_id AS "container_blobs.namespace_id",
     container_blobs.id AS "container_blobs.id",
     container_blobs.digest AS "container_blobs.digest",
     container_blobs.blob_sha256 AS "container_blobs.blob_sha256"
FROM public.container_blobs
WHERE (container_blobs.namespace_id = $1::uuid) AND (container_blobs.container_image_id = $2::uuid)
LIMIT $3;

Bound args: [00000000-0000-7000-8000-000000000001, 00000000-0000-7000-0000-000000000001, 10]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.28..20.23 rows=10 width=98) (actual time=0.014..0.016 rows=10 loops=1)
   Buffers: shared hit=3
   ->  Index Scan using container_blobs_p57_namespace_id_container_image_id_digest_idx on container_blobs_p57 container_blobs  (cost=0.28..40.18 rows=20 width=98) (actual time=0.014..0.015 rows=10 loops=1)
         Index Cond: ((namespace_id = '00000000-0000-7000-8000-000000000001'::uuid) AND (container_image_id = '00000000-0000-7000-0000-000000000001'::uuid))
         Buffers: shared hit=3
 Planning:
   Buffers: shared hit=167
 Planning Time: 0.785 ms
 Execution Time: 0.029 ms

Timings: planning 0.785ms, execution 0.029ms, total 0.814ms.

datastore.resolveReapRepository.ContainerImage

Summary: The plan matches the method's intent. pk_container_images answers (id, namespace_id) on the driving side, and the namespace literal prunes both tables to one partition of 64, so the one-hop chain costs 4 shared hits. The Seq Scan on the container_repositories partition comes from the skill's one-row-per-ancestor seed rather than from the statement: at 500 rows in that namespace the planner takes pk_container_repositories, as the second plan below shows. No anomalies.

Seed shape: namespaces=1, repositories=1, container_repositories=1, container_images=5000, blob_storage_blobs=5000, blob_storage_blobs_by_namespace=5000 (trigger), blob_storage_attachments=5000, container_manifests=4990, container_blobs=4990

Rendered SQL:

SELECT r.repository_id
FROM container_images i
JOIN container_repositories r
    ON r.id = i.container_repository_id AND r.namespace_id = i.namespace_id
WHERE i.namespace_id = $1 AND r.namespace_id = $1
  AND i.id = $2
LIMIT 1

Bound args: [00000000-0000-7000-8000-000000000001, 00000000-0000-7000-0000-000000000001]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.28..9.33 rows=1 width=16) (actual time=0.017..0.018 rows=1 loops=1)
   Buffers: shared hit=4
   ->  Nested Loop  (cost=0.28..9.33 rows=1 width=16) (actual time=0.016..0.017 rows=1 loops=1)
         Join Filter: (r.id = i.container_repository_id)
         Buffers: shared hit=4
         ->  Index Scan using container_images_p57_pkey on container_images_p57 i  (cost=0.28..8.30 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=1)
               Index Cond: ((id = '00000000-0000-7000-0000-000000000001'::uuid) AND (namespace_id = '00000000-0000-7000-8000-000000000001'::uuid))
               Buffers: shared hit=3
         ->  Seq Scan on container_repositories_p57 r  (cost=0.00..1.01 rows=1 width=48) (actual time=0.005..0.005 rows=1 loops=1)
               Filter: (namespace_id = '00000000-0000-7000-8000-000000000001'::uuid)
               Buffers: shared hit=1
 Planning:
   Buffers: shared hit=347
 Planning Time: 1.495 ms
 Execution Time: 0.040 ms

Timings: planning 1.495ms, execution 0.040ms, total 1.535ms.

Ancestor-cardinality re-run — same statement and same bind, seeded with 500 repositories and 500 container_repositories rows in the namespace and 5000 container_images spread across them:

 Limit  (cost=0.55..16.61 rows=1 width=16) (actual time=0.029..0.029 rows=1 loops=1)
   Buffers: shared hit=6
   ->  Nested Loop  (cost=0.55..16.61 rows=1 width=16) (actual time=0.028..0.028 rows=1 loops=1)
         Buffers: shared hit=6
         ->  Index Scan using container_images_p57_pkey on container_images_p57 i  (cost=0.28..8.30 rows=1 width=32) (actual time=0.015..0.015 rows=1 loops=1)
               Index Cond: ((id = '00000000-0000-7000-0000-000000000001'::uuid) AND (namespace_id = '00000000-0000-7000-8000-000000000001'::uuid))
               Buffers: shared hit=3
         ->  Index Scan using container_repositories_p57_pkey on container_repositories_p57 r  (cost=0.27..8.29 rows=1 width=48) (actual time=0.011..0.011 rows=1 loops=1)
               Index Cond: ((id = i.container_repository_id) AND (namespace_id = '00000000-0000-7000-8000-000000000001'::uuid))
               Buffers: shared hit=3
 Planning:
   Buffers: shared hit=286
 Planning Time: 1.943 ms
 Execution Time: 0.053 ms
datastore.sumFreedRepositoryBytes.ContainerHosted

Summary: On a reap-sized page the plan matches the method's intent: both NOT EXISTS arms probe (namespace_id, blob_sha256) on their own table, the blob_storage_blobs_by_namespace join is an Index Only Scan over pk_blob_storage_blobs_by_namespace, and all five partitioned tables prune to one partition of 64. The answer is right on the seed — a 10-address page holding 5 addresses a survivor still carries and 5 it does not sums to 29965, the exact total of the 5 freed blobs' size values. The one thing to weigh is the page-size flip in the query notes: from 100 addresses the container_blobs arm stops probing the index and hashes a full scan of the namespace's partition, so cost at the 1000-address maximum tracks the namespace's blob-link count.

Seed shape: namespaces=1, repositories=1, container_repositories=1, container_images=5000, blob_storage_blobs=5000, blob_storage_blobs_by_namespace=5000 (trigger), blob_storage_attachments=5000, container_manifests=4990, container_blobs=4990

Rendered SQL:

SELECT COALESCE(SUM(b.size), 0)::bigint
FROM (SELECT DISTINCT u.sha256 FROM unnest($3::bytea[]) AS u(sha256)) AS d
JOIN blob_storage_blobs_by_namespace b
    ON b.namespace_id = $1 AND b.sha256 = d.sha256
WHERE NOT EXISTS (
    SELECT 1
    FROM container_manifests m
    JOIN container_images i
        ON i.id = m.container_image_id AND i.namespace_id = m.namespace_id
    JOIN container_repositories r
        ON r.id = i.container_repository_id AND r.namespace_id = i.namespace_id
    WHERE m.namespace_id = $1 AND i.namespace_id = $1 AND r.namespace_id = $1
      AND r.repository_id = $2 AND m.blob_sha256 = d.sha256
)
AND NOT EXISTS (
    SELECT 1
    FROM container_blobs cb
    JOIN container_images i
        ON i.id = cb.container_image_id AND i.namespace_id = cb.namespace_id
    JOIN container_repositories r
        ON r.id = i.container_repository_id AND r.namespace_id = i.namespace_id
    WHERE cb.namespace_id = $1 AND i.namespace_id = $1 AND r.namespace_id = $1
      AND r.repository_id = $2 AND cb.blob_sha256 = d.sha256
)

Bound args: $1 = 00000000-0000-7000-8000-000000000001, $2 = 00000000-0000-7000-8000-000000000002, $3 = a 10-address bytea[] page — the 32-byte addresses whose big-endian value is 1, 2, 3, 4, 5, 4991, 4992, 4993, 4994 and 4995. The first five are carried by a surviving container_manifests and container_blobs row of the same repository, the last five by nothing. The statement answers 29965, the exact sum of those five blobs' size values.

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Aggregate  (cost=107.00..107.01 rows=1 width=8) (actual time=0.207..0.209 rows=1 loops=1)
   Buffers: shared hit=85
   ->  Nested Loop  (cost=1.79..106.99 rows=1 width=8) (actual time=0.105..0.201 rows=5 loops=1)
         Buffers: shared hit=85
         ->  Nested Loop Anti Join  (cost=1.51..98.68 rows=1 width=32) (actual time=0.057..0.144 rows=5 loops=1)
               Buffers: shared hit=70
               ->  Nested Loop Anti Join  (cost=0.82..89.73 rows=1 width=32) (actual time=0.041..0.119 rows=5 loops=1)
                     Buffers: shared hit=60
                     ->  HashAggregate  (cost=0.13..0.23 rows=10 width=32) (actual time=0.020..0.022 rows=10 loops=1)
                           Group Key: u.sha256
                           Batches: 1  Memory Usage: 24kB
                           ->  Function Scan on unnest u  (cost=0.00..0.10 rows=10 width=32) (actual time=0.013..0.013 rows=10 loops=1)
                     ->  Nested Loop  (cost=0.69..8.94 rows=1 width=33) (actual time=0.009..0.009 rows=0 loops=10)
                           Buffers: shared hit=60
                           ->  Nested Loop  (cost=0.56..8.78 rows=1 width=65) (actual time=0.007..0.007 rows=0 loops=10)
                                 Buffers: shared hit=50
                                 ->  Index Scan using container_blobs_p57_namespace_id_blob_sha256_idx on container_blobs_p57 cb  (cost=0.28..8.30 rows=1 width=65) (actual time=0.004..0.004 rows=0 loops=10)
                                       Index Cond: ((namespace_id = '00000000-0000-7000-8000-000000000001'::uuid) AND (blob_sha256 = u.sha256))
                                       Buffers: shared hit=30
                                 ->  Index Scan using container_images_p57_pkey on container_images_p57 i_1  (cost=0.28..0.47 rows=1 width=48) (actual time=0.005..0.005 rows=1 loops=5)
                                       Index Cond: ((id = cb.container_image_id) AND (namespace_id = '00000000-0000-7000-8000-000000000001'::uuid))
                                       Buffers: shared hit=15
                           ->  Index Scan using container_repositories_p57_pkey on container_repositories_p57 r_1  (cost=0.12..0.15 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=5)
                                 Index Cond: ((id = i_1.container_repository_id) AND (namespace_id = '00000000-0000-7000-8000-000000000001'::uuid))
                                 Filter: (repository_id = '00000000-0000-7000-8000-000000000002'::uuid)
                                 Buffers: shared hit=10
               ->  Nested Loop  (cost=0.69..8.94 rows=1 width=33) (actual time=0.005..0.005 rows=0 loops=5)
                     Buffers: shared hit=10
                     ->  Nested Loop  (cost=0.56..8.78 rows=1 width=65) (actual time=0.004..0.004 rows=0 loops=5)
                           Buffers: shared hit=10
                           ->  Index Scan using container_manifests_p57_namespace_id_blob_sha256_idx on container_manifests_p57 m  (cost=0.28..8.30 rows=1 width=65) (actual time=0.004..0.004 rows=0 loops=5)
                                 Index Cond: ((namespace_id = '00000000-0000-7000-8000-000000000001'::uuid) AND (blob_sha256 = u.sha256))
                                 Buffers: shared hit=10
                           ->  Index Scan using container_images_p57_pkey on container_images_p57 i  (cost=0.28..0.47 rows=1 width=48) (never executed)
                                 Index Cond: ((id = m.container_image_id) AND (namespace_id = '00000000-0000-7000-8000-000000000001'::uuid))
                     ->  Index Scan using container_repositories_p57_pkey on container_repositories_p57 r  (cost=0.12..0.15 rows=1 width=32) (never executed)
                           Index Cond: ((id = i.container_repository_id) AND (namespace_id = '00000000-0000-7000-8000-000000000001'::uuid))
                           Filter: (repository_id = '00000000-0000-7000-8000-000000000002'::uuid)
         ->  Index Only Scan using blob_storage_blobs_by_namespace_p57_pkey on blob_storage_blobs_by_namespace_p57 b  (cost=0.28..8.30 rows=1 width=41) (actual time=0.011..0.011 rows=1 loops=5)
               Index Cond: ((namespace_id = '00000000-0000-7000-8000-000000000001'::uuid) AND (sha256 = u.sha256))
               Heap Fetches: 5
               Buffers: shared hit=15
 Planning:
   Buffers: shared hit=632 read=3
 Planning Time: 6.690 ms
 Execution Time: 0.379 ms

Timings: planning 6.690ms, execution 0.379ms, total 7.069ms.

Page-size sweep — same seed, same statement, same repository, the array widened to 50, 100, 200 and 1000 addresses. Ten of the addresses are always free; every other one is carried by a surviving row.

Page size Root cost Execution Buffers (hit / read) container_blobs arm
10 107.01 0.379 ms 85 / 0 Nested Loop Anti Join over index_container_blobs_on_ns_id_blob_sha256
50 423.95 0.335 ms 469 / 0 Nested Loop Anti Join over index_container_blobs_on_ns_id_blob_sha256
100 650.20 2.508 ms 1005 / 0 Hash Anti Join over Seq Scan on container_blobs_p57, Memoize on pk_container_images
200 650.49 2.524 ms 1005 / 0 Hash Anti Join over Seq Scan on container_blobs_p57, Memoize on pk_container_images
1000 524.59 4.294 ms 225 / 0 Hash Anti Join over Seq Scan on container_blobs_p57, Hash Join to Seq Scan on container_images_p57

The 1000-address plan, which is MaxLifecycleReapPageSize:

 Aggregate  (cost=524.58..524.59 rows=1 width=8) (actual time=3.942..3.947 rows=1 loops=1)
   Buffers: shared hit=225
   ->  Nested Loop  (cost=513.72..524.57 rows=1 width=8) (actual time=3.776..3.936 rows=10 loops=1)
         Buffers: shared hit=225
         ->  Nested Loop Anti Join  (cost=513.44..521.06 rows=1 width=32) (actual time=3.684..3.833 rows=10 loops=1)
               Buffers: shared hit=195
               ->  Hash Anti Join  (cost=512.75..515.50 rows=1 width=32) (actual time=3.639..3.776 rows=10 loops=1)
                     Hash Cond: (u.sha256 = cb.blob_sha256)
                     Buffers: shared hit=175
                     ->  HashAggregate  (cost=12.50..14.50 rows=200 width=32) (actual time=0.408..0.476 rows=1000 loops=1)
                           Group Key: u.sha256
                           Batches: 1  Memory Usage: 209kB
                           ->  Function Scan on unnest u  (cost=0.00..10.00 rows=1000 width=32) (actual time=0.091..0.121 rows=1000 loops=1)
                     ->  Hash  (cost=437.87..437.87 rows=4990 width=33) (actual time=3.179..3.182 rows=4990 loops=1)
                           Buckets: 8192  Batches: 1  Memory Usage: 381kB
                           Buffers: shared hit=175
                           ->  Nested Loop  (cost=197.00..437.87 rows=4990 width=33) (actual time=1.234..2.478 rows=4990 loops=1)
                                 Join Filter: (r_1.id = i_1.container_repository_id)
                                 Buffers: shared hit=175
                                 ->  Seq Scan on container_repositories_p57 r_1  (cost=0.00..1.01 rows=1 width=32) (actual time=0.021..0.022 rows=1 loops=1)
                                       Filter: ((namespace_id = '00000000-0000-7000-8000-000000000001'::uuid) AND (repository_id = '00000000-0000-7000-8000-000000000002'::uuid))
                                       Buffers: shared hit=1
                                 ->  Hash Join  (cost=197.00..374.48 rows=4990 width=65) (actual time=1.211..2.191 rows=4990 loops=1)
                                       Hash Cond: (cb.container_image_id = i_1.id)
                                       Buffers: shared hit=174
                                       ->  Seq Scan on container_blobs_p57 cb  (cost=0.00..164.38 rows=4990 width=65) (actual time=0.006..0.510 rows=4990 loops=1)
                                             Filter: (namespace_id = '00000000-0000-7000-8000-000000000001'::uuid)
                                             Buffers: shared hit=102
                                       ->  Hash  (cost=134.50..134.50 rows=5000 width=48) (actual time=1.165..1.166 rows=5000 loops=1)
                                             Buckets: 8192  Batches: 1  Memory Usage: 455kB
                                             Buffers: shared hit=72
                                             ->  Seq Scan on container_images_p57 i_1  (cost=0.00..134.50 rows=5000 width=48) (actual time=0.010..0.442 rows=5000 loops=1)
                                                   Filter: (namespace_id = '00000000-0000-7000-8000-000000000001'::uuid)
                                                   Buffers: shared hit=72
               ->  Nested Loop  (cost=0.69..5.55 rows=1 width=33) (actual time=0.005..0.005 rows=0 loops=10)
                     Buffers: shared hit=20
                     ->  Nested Loop  (cost=0.56..5.38 rows=1 width=65) (actual time=0.005..0.005 rows=0 loops=10)
                           Buffers: shared hit=20
                           ->  Index Scan using container_manifests_p57_namespace_id_blob_sha256_idx on container_manifests_p57 m  (cost=0.28..4.96 rows=1 width=65) (actual time=0.005..0.005 rows=0 loops=10)
                                 Index Cond: ((namespace_id = '00000000-0000-7000-8000-000000000001'::uuid) AND (blob_sha256 = u.sha256))
                                 Buffers: shared hit=20
                           ->  Index Scan using container_images_p57_pkey on container_images_p57 i  (cost=0.28..0.41 rows=1 width=48) (never executed)
                                 Index Cond: ((id = m.container_image_id) AND (namespace_id = '00000000-0000-7000-8000-000000000001'::uuid))
                     ->  Index Scan using container_repositories_p57_pkey on container_repositories_p57 r  (cost=0.12..0.15 rows=1 width=32) (never executed)
                           Index Cond: ((id = i.container_repository_id) AND (namespace_id = '00000000-0000-7000-8000-000000000001'::uuid))
                           Filter: (repository_id = '00000000-0000-7000-8000-000000000002'::uuid)
         ->  Index Only Scan using blob_storage_blobs_by_namespace_p57_pkey on blob_storage_blobs_by_namespace_p57 b  (cost=0.28..3.50 rows=1 width=41) (actual time=0.010..0.010 rows=1 loops=10)
               Index Cond: ((namespace_id = '00000000-0000-7000-8000-000000000001'::uuid) AND (sha256 = u.sha256))
               Heap Fetches: 10
               Buffers: shared hit=30
 Planning:
   Buffers: shared hit=739
 Planning Time: 8.201 ms
 Execution Time: 4.294 ms

Migrations

Migration mode did not run. git diff --name-only --diff-filter=ACMR origin/main...HEAD -- 'internal/datastore/migrations/sql/' at 11424185f returns no path, so this merge request adds and modifies no migration.

This is a bot message 🤖 — /smurfit

Edited by Pawel Rozlach

Merge request reports