feat(managementapi): run npm bulk delete as a remote eviction pass (S17 Phase 6 plan: 32/42)

Hayley Swimelarrequested to merge
314/step-32-npm-remote-bulk-pass into main

Why

The npm bulk-delete routes have narrowed to hosted since the write-route scaffold landed, so a remote npm repository's packages, versions, and files collections answered the existence-hiding 404 while its cached rows stayed put. This MR serves the remote arms: the routes admit a remote repository, and the mgmtapi:bulk-delete-npm worker runs an eviction pass instead of the hosted delete.

Eviction is not deletion, so the pass drops the couplings that only make sense for owned data: no artifact_registry_artifact_deleted event, no packument force-expiry, no last-version-removes-package rule. It reaches those by calling NpmRemoteEvictor rather than by branching inside the hosted composer.

Depends on

Merge gate: satisfied. The fail-closed worker read this widening relies on merged as c437da899, from fix(managementapi): fail closed on bulk-worker ... (!1736 - merged) • Hayley Swimelar • 19.4 for Bulk workers no-op on non-hosted kinds where th... (#723 - closed) • Hayley Swimelar • 19.4.

Deploy gate: open, and it is not a merge order. This route must not widen in production until that fail-closed read is running fleet-wide, so it must not ride the same promotion as c437da899's release. docs/dev/releases.md gates the production manifest upload behind a manual play (runway_manual_production_deploy: true); staging uploads automatically. Confirm what is actually running by reading the version field of a recent access log row, not from a green deployment pipeline, because the pipeline ends at manifest upload and Flux reconciles the rollout separately.

The window this closes: during a rolling deploy a new pod's widened route enqueues a remote batch onto the shared mgmtapi:bulk-delete-npm kind, and an old pod without the fail-closed read claims it and completes it as a silent no-op, a 202'd eviction that never runs. With the read deployed everywhere a skewed claim returns a typed error and River retries until an armed binary picks it up.

Evidence bearing on relaxing the ordering: the widened route is unreachable in production today. internal/managementapi/create.go answers 422 to every non-hosted kind, and CreateNpmRemoteRepository has no non-test caller, so no remote npm repository can exist until the remote-create step lands. Whether that justifies dropping the deploy ordering is an operator call, not one this MR makes.

What (the non-obvious parts)

  • The versions pass walks created_at DESC, and the direction is load-bearing. A cache fill stamps NOW(), so under an ascending walk a version re-cached mid-pass sorts ahead of the cursor and is evicted, which makes the re-cache-survival clause unreachable and leaves the walk unbounded. Descending puts that row in the region the walk has already left, and it is the versions-list default sort, so walk order and list order are the same thing. A unit test cannot catch the direction (the page fake is direction-agnostic), so an integration test pins it and fails against ASC.
  • The route widening is bulk-scoped rather than a widening of the shared gate. resolvePackageRepositoryID has 11 callers and 10 must keep their remote 404, so the bulk arms dispatch on the already-resolved repositories row kind (npmBulkRepositoryServesKind). Building the gate on the per-format finder instead passes every unit test and 404s the wire test, because that finder joins npm_repositories, which a remote repository has no row in.
  • The binding read leaves the upstream credential in the database. The pass needs the npm_remote_repositories child id and reaches no upstream, so the seam takes FindRemoteDetailsByRepositoryID, whose projection computes a credential presence bit in SQL, rather than the resolver read that materializes the token into a crypto.RedactedString. Both share one statement builder and differ only in that slot. The narrow read takes a database handle, so the seam is satisfied by the wire_management.go adapter the settings reads already use rather than by the store directly, and the audit sink that store needs arrives from the wiring as an interface instead of as a zero-value literal.
  • No acceptance-time predicate on the remote scope reads. The hosted bound protects concurrent publishes and a remote collection has none. Two of the three tables carry no created_at column to bound on in any case.
  • The drain carries a per-run page budget for the two name-ordered walks, where a coordinate cached for the first time under a name after the cursor is still reachable. Both exits preserve progress: committed entries stay committed, and the retry resumes on a collection the run shrank.
  • Throughput is measured only against a non-durable container. The budget's ceiling is 100,000 single-row transactions inside the 30-minute job deadline, so 18 ms per entry. Measured 112 µs (marking) and 342 µs (files) with fsync=off and synchronous_commit=off, which is a ceiling rather than a production number. The honest figure is the break-even: the deadline binds only if a durable single-row commit averages 18 ms or worse.
  • Scope beyond the plan's Files list, disclosed per the plan-contradiction rule. Step 32 names only the worker and route files, but the step also needs new internal/datastore scope-page reads and chain probes, plus a cmd/artifact-registry/wire_management.go edit. No migration: the keyset index the spec names as a Phase 6 addition already exists, and the branch touches no migration file.

Spec coverage

Acceptance criterion Covered by
AC #102 (closed): evictions emit no artifact_registry_artifact_deleted, hosted emission unchanged TestBulkDeleteNpmWorker_Remote* event assertions, both directions
AC #103: an evicted version leaves the cached packument untouched remote worker integration suite (npm_remote_metadata_files untouched)
AC #104 tail: a package emptied of versions by eviction remains listed remote delete_all versions integration walk
AC #108: one ordered pass; an entry re-cached behind the walk survives without failing the job store-integration re-cache subtests, per collection (packages, versions, files)
Pass-run log line: resolved kind plus applied and skipped counts logRemotePass assertions in the remote worker suite

Test plan

  • go build ./..., go vet ./..., and go vet -tags=integration on the three touched packages: clean.
  • go test ./...: 75 packages ok, 0 failures.
  • go test -tags=integration ./internal/datastore/ ./internal/managementapi/ ./cmd/artifact-registry/: ok (571 s / 80 s / 127 s), real test bodies in all five touched rig files, no rig panic at construction.
  • go test -race ./internal/managementapi/ ./cmd/artifact-registry/: ok, no setRoot race.
  • golangci-lint 2.12.2 with --max-same-issues=0 --max-issues-per-linter=0 --uniq-by-line=false: 0 issues untagged. Integration-tagged, the only findings on files this branch touches are contextcheck, matching the merged sibling npm_remote_eviction_integration_test.go unsuppressed rather than adding a directive.
  • scripts/ci/check-migration-immutability.sh origin/main: OK, no migration touched.
  • TestEveryStatementIsInstrumented and TestQueryNames_EachUsedExactlyOnce: pass.
  • EXPLAIN pins for all three walks; the versions walk is an Index Only Scan Backward, single-partition pruned, with the row-value comparison as an Index Cond rather than a filter.

No conformance run: this changes no Maven, npm, or Container/OCI protocol behavior, only the management API. No e2e scenario row: docs/testing/e2e/npm.md scopes remote npm out of the catalog.

LOC

5952 insertions across 33 files: 1540 production Go, 4331 test Go, 81 contract (OpenAPI and Bruno). Splitting does not help, because the route widening, the worker arm, and the scope reads have to land in one MR or the widened route enqueues jobs whose worker cannot dispatch them, which is the failure this step exists to avoid. The test mass is the step's own suite: the scope reads across three collections, the worker arms, the route gate and its non-leak proof, the boot-level composition walk, and the EXPLAIN pins.

Database Review Evidence

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: the four scope pages are measured at bulkNpmScopePageSize (500), the page the drain actually asks for. Each read target carries 5000 rows under the addressed parent plus 2000 under a live sibling parent in the same namespace, so the parent-id predicate has to discriminate rather than match the whole partition.

Method Plan node Index Rows (plan / actual) Cost Time Buffers (hit / read) Partitions
NpmRemoteFileStore.ListNpmRemoteFileIDsForDeleteAll Limit → Index Scan unique_npm_remote_files_ns_id_ver_id_file_name 500 / 500 84.19 0.115ms 18 / 0 1/64
NpmRemotePackageStore.FindNpmRemotePackageInRepository Limit → Index Scan pk_npm_remote_packages 1 / 1 8.30 0.023ms 3 / 0 1/64
NpmRemotePackageStore.ListNpmRemotePackageIDsForDeleteAll Limit → Index Scan unique_npm_remote_packages_ns_id_repo_id_name 500 / 500 64.72 0.107ms 15 / 0 1/64
NpmRemoteVersionStore.FindNpmRemoteVersionInPackage Limit → Index Scan pk_npm_remote_versions 1 / 1 8.30 0.044ms 3 / 0 1/64
NpmRemoteVersionStore.FindNpmRemoteVersionInRepository Limit → Nested Loop pk_npm_remote_versions, pk_npm_remote_packages 1 / 1 16.62 0.074ms 6 / 0 1/64, 1/64
NpmRemoteVersionStore.ListNpmRemoteVersionIDsForDeleteAll.AfterNil Limit → Index Only Scan Backward index_npm_remote_versions_on_ns_id_pkg_id_created_at_id 500 / 500 64.26 0.125ms 18 / 0 1/64
NpmRemoteVersionStore.ListNpmRemoteVersionIDsForDeleteAll.AfterSet Limit → Index Only Scan Backward index_npm_remote_versions_on_ns_id_pkg_id_created_at_id 500 / 500 95.62 0.153ms 18 / 0 1/64

Every plan names the partition-local child index. The Index column gives the parent index each child inherits from, read from pg_inherits.

NpmRemoteFileStore.ListNpmRemoteFileIDsForDeleteAll

Summary: The Index Scan rides unique_npm_remote_files_ns_id_ver_id_file_name, whose prefix covers the version scope and the file_name keyset bound together, and whose partial predicate absorbs the marker exclusion so no Filter line remains. The namespace_id literal prunes to one of 64 partitions and the ascending ORDER BY needs no Sort. No anomalies.

Seed shape: namespaces=1, repositories=2, npm_remote_repositories=2, npm_remote_packages=7000, npm_remote_versions=7000, blob_storage_blobs=1, blob_storage_attachments=1, npm_remote_files=7000

Rendered SQL:

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

Bound args: ['d3bef03e-d12c-4961-9117-490401ffd253', '265e244f-ce27-46d2-bbd3-0be3a06bff50', 'review-prep-file-002500.tgz', 500]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..84.19 rows=500 width=45) (actual time=0.014..0.093 rows=500 loops=1)
  Buffers: shared hit=18
  ->  Index Scan using npm_remote_files_p61_namespace_id_npm_remote_version_id_fil_idx on npm_remote_files_p61 npm_remote_files  (cost=0.28..537.43 rows=3201 width=45) (actual time=0.013..0.072 rows=500 loops=1)
        Index Cond: ((namespace_id = 'd3bef03e-d12c-4961-9117-490401ffd253'::uuid) AND (npm_remote_version_id = '265e244f-ce27-46d2-bbd3-0be3a06bff50'::uuid) AND (file_name > 'review-prep-file-002500.tgz'::text))
        Buffers: shared hit=18
Planning:
  Buffers: shared hit=160
Planning Time: 0.695 ms
Execution Time: 0.115 ms

Timings: planning 0.695ms, execution 0.115ms, total 0.810ms.

NpmRemotePackageStore.FindNpmRemotePackageInRepository

Summary: The probe resolves through pk_npm_remote_packages on (id, namespace_id) and applies the repository scope and the marker exclusion as a Filter over the single row the primary key returns. A Filter is the right shape here, because the index has already reduced the scan to one row and no other index would fetch fewer. No anomalies.

Seed shape: namespaces=1, repositories=2, npm_remote_repositories=2, npm_remote_packages=7000, npm_remote_versions=7000, blob_storage_blobs=1, blob_storage_attachments=1, npm_remote_files=7000

Rendered SQL:

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

Bound args: ['a0c6fd07-4e66-4e94-947e-2b0e32aac01d', '7a0b33fd-e470-4c84-9f21-004bb1db3b6c', '60a47d44-6f83-4df4-86ee-a57514640619', 1]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..8.30 rows=1 width=72) (actual time=0.011..0.011 rows=1 loops=1)
  Buffers: shared hit=3
  ->  Index Scan using npm_remote_packages_p52_pkey on npm_remote_packages_p52 npm_remote_packages  (cost=0.28..8.30 rows=1 width=72) (actual time=0.010..0.010 rows=1 loops=1)
        Index Cond: ((id = '7a0b33fd-e470-4c84-9f21-004bb1db3b6c'::uuid) AND (namespace_id = 'a0c6fd07-4e66-4e94-947e-2b0e32aac01d'::uuid))
        Filter: ((soft_deleted_at IS NULL) AND (npm_remote_repository_id = '60a47d44-6f83-4df4-86ee-a57514640619'::uuid))
        Buffers: shared hit=3
Planning:
  Buffers: shared hit=62
Planning Time: 0.354 ms
Execution Time: 0.023 ms

Timings: planning 0.354ms, execution 0.023ms, total 0.377ms.

NpmRemotePackageStore.ListNpmRemotePackageIDsForDeleteAll

Summary: The Index Scan rides unique_npm_remote_packages_ns_id_repo_id_name, whose (namespace_id, npm_remote_repository_id, name) prefix carries the repository scope and the keyset bound in one cond, and whose partial predicate absorbs the marker exclusion. The 2000 sibling-repository rows seeded into the same partition are excluded by the index cond rather than by a filter. No anomalies.

Seed shape: namespaces=1, repositories=2, npm_remote_repositories=2, npm_remote_packages=7000, npm_remote_versions=7000, blob_storage_blobs=1, blob_storage_attachments=1, npm_remote_files=7000

Rendered SQL:

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

Bound args: ['2b23666d-9fe3-4893-85f4-afd489fe6b58', '4daedab9-4708-465d-ae21-c661b3ed92a2', 'review-prep-pkg-002500', 500]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..64.72 rows=500 width=40) (actual time=0.012..0.085 rows=500 loops=1)
  Buffers: shared hit=15
  ->  Index Scan using npm_remote_packages_p23_namespace_id_npm_remote_repository__idx on npm_remote_packages_p23 npm_remote_packages  (cost=0.28..412.81 rows=3201 width=40) (actual time=0.012..0.064 rows=500 loops=1)
        Index Cond: ((namespace_id = '2b23666d-9fe3-4893-85f4-afd489fe6b58'::uuid) AND (npm_remote_repository_id = '4daedab9-4708-465d-ae21-c661b3ed92a2'::uuid) AND (name > 'review-prep-pkg-002500'::text))
        Buffers: shared hit=15
Planning:
  Buffers: shared hit=68
Planning Time: 0.382 ms
Execution Time: 0.107 ms

Timings: planning 0.382ms, execution 0.107ms, total 0.489ms.

NpmRemoteVersionStore.FindNpmRemoteVersionInPackage

Summary: Same shape as FindNpmRemotePackageInRepository one level down. pk_npm_remote_versions returns the single row, and the package scope with the marker exclusion apply as a Filter over it. Pruned to one of 64 partitions. No anomalies.

Seed shape: namespaces=1, repositories=2, npm_remote_repositories=2, npm_remote_packages=7000, npm_remote_versions=7000, blob_storage_blobs=1, blob_storage_attachments=1, npm_remote_files=7000

Rendered SQL:

SELECT npm_remote_versions.id AS "npm_remote_versions.id",
     npm_remote_versions.namespace_id AS "npm_remote_versions.namespace_id",
     npm_remote_versions.npm_remote_package_id AS "npm_remote_versions.npm_remote_package_id",
     npm_remote_versions.created_at AS "npm_remote_versions.created_at",
     npm_remote_versions.version AS "npm_remote_versions.version"
FROM public.npm_remote_versions
WHERE (((npm_remote_versions.namespace_id = $1::uuid) AND (npm_remote_versions.id = $2::uuid)) AND (npm_remote_versions.npm_remote_package_id = $3::uuid)) AND (npm_remote_versions.soft_deleted_at IS NULL)
LIMIT $4;

Bound args: ['2a7e6037-31a0-464c-a134-a9c2ff194bdb', 'af1c65c0-2153-424b-bd80-0f44989cb811', '61f62c5c-a649-471b-b9c3-41eb94872d7d', 1]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..8.30 rows=1 width=64) (actual time=0.022..0.022 rows=1 loops=1)
  Buffers: shared hit=3
  ->  Index Scan using npm_remote_versions_p48_pkey on npm_remote_versions_p48 npm_remote_versions  (cost=0.28..8.30 rows=1 width=64) (actual time=0.021..0.021 rows=1 loops=1)
        Index Cond: ((id = 'af1c65c0-2153-424b-bd80-0f44989cb811'::uuid) AND (namespace_id = '2a7e6037-31a0-464c-a134-a9c2ff194bdb'::uuid))
        Filter: ((soft_deleted_at IS NULL) AND (npm_remote_package_id = '61f62c5c-a649-471b-b9c3-41eb94872d7d'::uuid))
        Buffers: shared hit=3
Planning:
  Buffers: shared hit=92
Planning Time: 0.923 ms
Execution Time: 0.044 ms

Timings: planning 0.923ms, execution 0.044ms, total 0.967ms.

NpmRemoteVersionStore.FindNpmRemoteVersionInRepository

Summary: The chain probe runs as a Nested Loop over two primary-key lookups, and both tables prune to one partition, which is what the join's namespace_id equality buys. The inner lookup carries the repository scope and the package-level marker exclusion as a Filter over the one package row. No anomalies.

Seed shape: namespaces=1, repositories=2, npm_remote_repositories=2, npm_remote_packages=7000, npm_remote_versions=7000, blob_storage_blobs=1, blob_storage_attachments=1, npm_remote_files=7000

Rendered SQL:

SELECT npm_remote_versions.id AS "npm_remote_versions.id",
     npm_remote_versions.namespace_id AS "npm_remote_versions.namespace_id",
     npm_remote_versions.npm_remote_package_id AS "npm_remote_versions.npm_remote_package_id",
     npm_remote_versions.created_at AS "npm_remote_versions.created_at",
     npm_remote_versions.version AS "npm_remote_versions.version"
FROM public.npm_remote_versions
     INNER JOIN public.npm_remote_packages ON ((npm_remote_versions.npm_remote_package_id = npm_remote_packages.id) AND (npm_remote_versions.namespace_id = npm_remote_packages.namespace_id))
WHERE ((((npm_remote_versions.namespace_id = $1::uuid) AND (npm_remote_versions.id = $2::uuid)) AND (npm_remote_versions.soft_deleted_at IS NULL)) AND (npm_remote_packages.npm_remote_repository_id = $3::uuid)) AND (npm_remote_packages.soft_deleted_at IS NULL)
LIMIT $4;

Bound args: ['b3414f8c-4645-4d9f-a594-780497a9fee9', 'f21a9aa3-60a3-4d6a-9559-c5bc25b2434f', 'b299c147-c618-4331-9be1-7de3249ac1e9', 1]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.56..16.62 rows=1 width=64) (actual time=0.039..0.039 rows=1 loops=1)
  Buffers: shared hit=6
  ->  Nested Loop  (cost=0.56..16.62 rows=1 width=64) (actual time=0.037..0.038 rows=1 loops=1)
        Buffers: shared hit=6
        ->  Index Scan using npm_remote_versions_p16_pkey on npm_remote_versions_p16 npm_remote_versions  (cost=0.28..8.30 rows=1 width=64) (actual time=0.020..0.020 rows=1 loops=1)
              Index Cond: ((id = 'f21a9aa3-60a3-4d6a-9559-c5bc25b2434f'::uuid) AND (namespace_id = 'b3414f8c-4645-4d9f-a594-780497a9fee9'::uuid))
              Filter: (soft_deleted_at IS NULL)
              Buffers: shared hit=3
        ->  Index Scan using npm_remote_packages_p16_pkey on npm_remote_packages_p16 npm_remote_packages  (cost=0.28..8.30 rows=1 width=32) (actual time=0.015..0.015 rows=1 loops=1)
              Index Cond: ((id = npm_remote_versions.npm_remote_package_id) AND (namespace_id = 'b3414f8c-4645-4d9f-a594-780497a9fee9'::uuid))
              Filter: ((soft_deleted_at IS NULL) AND (npm_remote_repository_id = 'b299c147-c618-4331-9be1-7de3249ac1e9'::uuid))
              Buffers: shared hit=3
Planning:
  Buffers: shared hit=177
Planning Time: 1.879 ms
Execution Time: 0.074 ms

Timings: planning 1.879ms, execution 0.074ms, total 1.953ms.

NpmRemoteVersionStore.ListNpmRemoteVersionIDsForDeleteAll.AfterNil

Summary: The opening page reads index_npm_remote_versions_on_ns_id_pkg_id_created_at_id as an Index Only Scan Backward, which is the descending walk the method documents rather than a Sort over an ascending read. Both selected columns sit in the index and the partial predicate absorbs the marker exclusion. Heap Fetches: 500 is an artifact of measuring rows inserted in the same uncommitted transaction, where the visibility map is unset. No anomalies.

Seed shape: namespaces=1, repositories=2, npm_remote_repositories=2, npm_remote_packages=7000, npm_remote_versions=7000, blob_storage_blobs=1, blob_storage_attachments=1, npm_remote_files=7000

Rendered SQL:

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

Bound args: ['296a8f18-5533-4d69-b533-6edea0718cd5', 'dc481e90-680e-4039-af2c-d77b82e9a178', 500]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..64.26 rows=500 width=24) (actual time=0.017..0.102 rows=500 loops=1)
  Buffers: shared hit=18
  ->  Index Only Scan Backward using npm_remote_versions_p59_namespace_id_npm_remote_package_id_idx2 on npm_remote_versions_p59 npm_remote_versions  (cost=0.28..640.10 rows=5000 width=24) (actual time=0.016..0.081 rows=500 loops=1)
        Index Cond: ((namespace_id = '296a8f18-5533-4d69-b533-6edea0718cd5'::uuid) AND (npm_remote_package_id = 'dc481e90-680e-4039-af2c-d77b82e9a178'::uuid))
        Heap Fetches: 500
        Buffers: shared hit=18
Planning:
  Buffers: shared hit=91
Planning Time: 0.527 ms
Execution Time: 0.125 ms

Timings: planning 0.527ms, execution 0.125ms, total 0.652ms.

NpmRemoteVersionStore.ListNpmRemoteVersionIDsForDeleteAll.AfterSet

Summary: The keyset bound lands as an Index Cond rather than a Filter, so ROW(created_at, id) < ROW(...) is evaluated inside the index scan and a resumed page pays no per-row rejection cost for the region the walk already left. Same index and same backward direction as the opening page. No anomalies.

Seed shape: namespaces=1, repositories=2, npm_remote_repositories=2, npm_remote_packages=7000, npm_remote_versions=7000, blob_storage_blobs=1, blob_storage_attachments=1, npm_remote_files=7000

Rendered SQL:

SELECT npm_remote_versions.id AS "npm_remote_versions.id",
     npm_remote_versions.created_at AS "npm_remote_versions.created_at"
FROM public.npm_remote_versions
WHERE (((npm_remote_versions.namespace_id = $1::uuid) AND (npm_remote_versions.npm_remote_package_id = $2::uuid)) AND (npm_remote_versions.soft_deleted_at IS NULL)) AND ((npm_remote_versions.created_at, npm_remote_versions.id) < ($3::timestamp with time zone, $4::uuid))
ORDER BY npm_remote_versions.created_at DESC, npm_remote_versions.id DESC
LIMIT $5;

Bound args: ['3a7b4ce4-34fa-4591-9be2-ddbe78bb5a6b', 'c89f264c-ee19-49b2-9ff3-4d61b82cf6cc', '2026-08-20 16:08:47.74192+00', 'd20e445e-9333-44d8-a936-344e4c7e7c05', 500]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..95.62 rows=500 width=24) (actual time=0.020..0.111 rows=500 loops=1)
  Buffers: shared hit=18
  ->  Index Only Scan Backward using npm_remote_versions_p10_namespace_id_npm_remote_package_id_idx2 on npm_remote_versions_p10 npm_remote_versions  (cost=0.28..340.63 rows=1785 width=24) (actual time=0.020..0.090 rows=500 loops=1)
        Index Cond: ((namespace_id = '3a7b4ce4-34fa-4591-9be2-ddbe78bb5a6b'::uuid) AND (npm_remote_package_id = 'c89f264c-ee19-49b2-9ff3-4d61b82cf6cc'::uuid) AND (ROW(created_at, id) < ROW('2026-08-20 16:08:47.74192+00'::timestamp with time zone, 'd20e445e-9333-44d8-a936-344e4c7e7c05'::uuid)))
        Heap Fetches: 500
        Buffers: shared hit=18
Planning:
  Buffers: shared hit=101
Planning Time: 0.548 ms
Execution Time: 0.153 ms

Timings: planning 0.548ms, execution 0.153ms, total 0.701ms.

Related to #314

Edited by Hayley Swimelar

Merge request reports