feat(managementapi): serve the npm remote package and version reads (S17 Phase 6 plan: 17/42)

Why

The npm package and version read routes answer 404 on a kind=remote repository. Every remote arm has sat on that pending 404 since the artifact-read kind-dispatch scaffold landed in chore(managementapi): artifact-read kind-dispat... (!1582 - merged) • Hayley Swimelar • 19.4, so a remote repository's cached npm packages and versions were unreachable through the management API even though the rows existed.

This fills those arms from npm_remote_packages and npm_remote_versions, implementing Step 17 of docs/plans/2026-08-13-s17-phase6-virtual-remote-repositories.md against the npm package and version slices of acceptance criteria #95, #97, #98, #99, and #100 in docs/specs/S17-rest-management-api.md. The npm file routes stay on the pending 404, which step 18 closes.

What

Each caller peels its own remote arm off ahead of the shared kind gate. Both gates this step fills, resolvePackageRepositoryID and hostedVersionFileRepository, are shared with route families it must not serve, so the fill lands at each caller rather than inside the switch. The Maven package reads, the npm dist-tag routes, both package deletes, both file routes, and both file deletes keep answering exactly what they answered before. Eight gate-holder test functions (52 subtests) pin that.

The kind gate stays after the query parse, so an invalid sort on a remote repository still answers 400 rather than 404 (TestVersionFileRoutes_InvalidQueryBeats404OnNonHostedRepository).

The keyset list is index-backed in both directions with no post-scan sort, pinned by EXPLAIN against 5000 analyzed rows. The deep-page tests also assert the absence of a post-index Filter, so rewriting the row-value keyset bound into its expanded OR form cannot silently turn a deep page from O(limit) into O(offset).

The new store methods' error strings name the operation and the table but no row ids. docs/dev/database-query-patterns.md requires that of a newly added store query method, with the dropped identifiers logged as structured fields at the call site instead. Five methods and six call sites were converted. namespace_id is deliberately absent from those fields, because the 500 writers already supply it.

Four operations move from 404 to 200 on a remote repository: the npm package list and detail, and the npm version list and detail. That is the intended behavior change rather than a regression, and no client can observe it. docs/dev/api-style.md line 134 calls any status-code change breaking, but create.go line 214 rejects a non-hosted kind with 422 and RepositoryStore.Update names kind among the columns it never assigns, so no reachable repository holds that kind today.

Departures from the plan

api/openapi/v1.yaml widens NpmPackage.versions_count and tags_count to [integer, 'null']. The plan assigns v1.yaml to step 18, but the two cache tables store no counters, so both fields serialize as null and a remote npm package response matches zero Package oneOf branches. The oneOf discriminates on required-key presence, so it is unaffected. No Bruno change is owed, because no operation was added, renamed, or removed.

Three sibling files outside the plan's list each take a //nolint:dupl. dupl reports both sides of a clone and this branch's code creates it, so without the directives the default-build lint fails on already-merged code. Every dupl token this MR adds was re-measured by drop-and-rerun after the error strings lost their identifiers, one token at a time so a clone partner's own directive could not mask the finding. Thirteen fire, each naming the file its partner sits in. The fourteenth had gone stale: the identifier drop took the FindNpmVersionByID pair under the threshold, so that exemption is deleted and npm_versions.go leaves the change set. The funlen token on requireArtifactDeps was measured the same way, at 79 lines against the 70 limit. The rebase onto main also retired the dupl token main carries on requireArtifactDeps and requireArtifactWriteDeps, because the npm remote seams grow the first past dupl's similarity threshold, so the pair no longer clones. Four now-dead exemptions came out of packages.go and versions.go earlier in the branch.

Both npm version sorts ship, not only created_at. The plan's step 17 says "same shapes as Step 15" and step 15 names created_at alone, but AC #95 carries Phase 3's sorts over unchanged and the Phase 3 npm version list accepts created_at and version. Both are index-backed on npm_remote_versions, and both get an EXPLAIN pin.

The run recipe was stale. The management API rejects kind != hosted, so nothing could reach these routes. driver.sh seed-remote, the cache rows the management reads serve, and the recipe update ride this MR, with driver.sh smoke passing 30 of 30 from a cold rebuild.

Spec coverage

Acceptance criterion	Tests
#95 cached npm packages and versions serve with Phase 3's sorts, pagination, and error rules, and an uncached coordinate is absent	TestNpmRemotePackageList_ServesTheCachedPackages, _DescendingOrderAndKeysetWalk, _EmptyCacheIsAnEmptyArray, _ClampsAnOversizedLimit, TestNpmRemotePackageDetail_ServesTheCachedRow, TestNpmRemoteVersionList_ServesTheCachedVersions, _BothSortsAndOrders, _KeysetWalkFollowsTheLinkHeader, _ParentResolveSeparatesAbsentFromEmpty, TestNpmRemoteVersionDetail_ServesTheCachedRow, and the store-side TestNpmRemotePackageStore_ListNpmRemotePackages* and TestNpmRemoteVersionStore_ListNpmRemoteVersionsByPackage* families
#97 version publish attribution and the npm package counters serialize null	TestNpmRemotePackageFromModel_NullsTheCounters, _UnscopedPackageNullsTheScope, TestVersionFromNpmRemoteModel_NullsTheAttribution, _ZeroSizeIsPresent, TestNpmPackageFromModel_KeepsTheHostedCountersPresent, and the two new rows in TestPackageReadHandlers_ResponsesMatchOpenAPIContract and TestVersionFileHandlers_ResponsesMatchOpenAPIContract
#98 dist-tag clause: npm dist-tag routes return 404 on a remote repository	Held unchanged by the two dist-tag remote rows of TestPackageSubtreeRoutes_NonHostedRepositoryIs404. The remote cache stores dist-tags inside one opaque rewritten document, so this 404 is permanent
#99 every artifact route on a virtual repository returns 404	Held unchanged. Every virtual row of TestPackageSubtreeRoutes_NonHostedRepositoryIs404 and TestVersionFileRoutes_NonHostedRepositoryIs404 survives
#100 an evicted-but-unreaped row is absent from every list and detail read	TestNpmRemotePackageStore_SoftDeletedPackageIsInvisibleToReads, TestNpmRemoteVersionStore_SoftDeletedVersionIsInvisibleToReads, _MarkedPackageHidesItsLiveVersions, TestNpmRemotePackageList_EvictedRowsAreAbsent, TestNpmRemoteVersionList_EvictedVersionsAreAbsent, and the evicted rows of TestNpmRemotePackageDetail_IdOutsideTheChainIs404 and TestNpmRemoteVersionDetail_IdOutsideTheChainIs404
Invalid sort, order, limit, or an undecodable cursor returns 400, while a limit past the maximum clamps	TestNpmRemotePackageList_RejectsInvalidQueryBeforeAnyRead, TestNpmRemoteVersionList_RejectsInvalidQueryBeforeAnyRead, both _ClampsAnOversizedLimit cases, the store-side *_ArgumentGuards pair, and TestNpmRemoteVersionCursor_HasKeysFor
Tenant isolation: every store call carries the namespace and the remote binding	TestNpmRemotePackageList_ScopesTheQueryByTheRemoteBinding, TestNpmRemoteVersionList_ScopesTheChainByTheRemoteBinding, and the store-side *_ChainScoping pair
An unexpected store failure returns 500 and logs the scoping ids as structured fields	TestNpmRemotePackageRoutes_StoreFailureLogsTheScopingIDs and TestNpmRemoteVersionRoutes_StoreFailureLogsTheScopingIDs, six cases across the packages list, the binding resolve, the package detail, the parent package read, the versions list, and the version detail. Store-side: the four *_QueryFailure cases and TestNpmRemoteRepositoryStore_FindNpmRemoteRepositoryID

Diff size

7317 reviewable LOC, past the 500 that docs/dev/development-model.md asks be split or justified. By group: test Go 5588 (76%), source Go 1407 (19%), run recipe 226, dev docs 65, contract 31.

Two cuts, measured:

• Store from handler, 3876 LOC against 3165. The store half can land alone, as the container remote read stores did in chore(datastore): add container remote image an... (!1629 - merged) • Hayley Swimelar • 19.4. The handler half cannot: this MR widens NpmPackage.versions_count and tags_count to nullable, and without that widening a cached package's body fails its own contract test.
• Packages from versions, 3014 LOC packages, 3770 versions, 1386 shared, measured before the comment-caps pass shortened every group. The dependency runs one way, since the remote version reads resolve their parent through FindNpmRemotePackageInRepository, so packages could land first with versions stacked on it. The versions half needs no contract change, because the Version attribution fields are already nullable on main. Step 18 depends on both finders.

The shape follows Step 15, which the plan names as this step's template twice and which ships the same two entity types undivided at 6940 LOC in feat(managementapi): serve the maven remote pac... (!1749 - merged) • Hayley Swimelar • 19.4. The 75% test mass mirrors the merged container read-store suite subtest for subtest, across two entity types and two sorts, plus EXPLAIN pins on both sorts in both directions.

e2e scenarios

No scenario is added or affected. docs/testing/e2e/npm.md lists virtual and remote npm repositories under "Out of scope until the capability ships", and the management API still rejects a non-hosted kind on create, so no protocol-client journey can reach a remote repository.

Test plan

go test -count=1 ./internal/managementapi/... ./internal/datastore/... ./cmd/...
ARTIFACT_REGISTRY_DATABASE_TEST_DSN=<dsn> go test -tags=integration -count=1 \
  ./internal/managementapi/... ./internal/datastore/...
golangci-lint run --build-tags=integration --max-same-issues=0 \
  --max-issues-per-linter=0 ./internal/datastore/... ./internal/managementapi/...
.claude/skills/run-artifact-registry/driver.sh smoke

Verified on the local rig against a cold-booted binary with a remote npm repository seeded through the new seed-remote: an empty cache lists 200 [], a cached package lists 200 with null counters, a cached version detail serves 200 with null attribution, sort=bogus answers 400, the dist-tag and file routes hold their 404, and setting soft_deleted_at on either cache table drops the row from its list and turns its detail into a 404.

Both deep-page Filter assertions, the enumerated-kind totality enrollment, and the package list's parse-before-resolve assertion are mutation-verified.

Context for LLM agents

Rationale

• Hold the contract widening for step 18, as the plan assigns it. Rejected because a remote npm package response then matches no Package oneOf branch, so the route's own contract test fails on main for a whole step.
• Fill the remote arm inside the shared kind switch. Rejected because both gates are shared with the Maven reads, the dist-tag routes, the deletes, and the file routes, none of which this step serves, so a switch-level fill changes six route families at once.
• Keyset the version list on created_at alone, as step 15's template does. Rejected because Phase 3's npm version list accepts version too, and unique_npm_remote_versions_ns_id_pkg_id_version already backs it, so dropping the sort would narrow the remote surface against AC #95.

Non-goals

• npm file reads and the cache object. Step 18 owns them, and the file routes keep their pending 404 here.
• Eviction and the delete arms. Steps 26 and 29 own them, which is why the remote deletes keep the gate's 404.
• Backfilling the cache. internal/format/npm/npmremote writes these tables on the outbound fetch path, and this MR reads them.

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.FindNpmRemotePackageInRepository	Limit → Index Scan	pk_npm_remote_packages	1 / 1	8.30	0.010ms	3 / 0	1/64
datastore.FindNpmRemoteRepositoryID	Limit → Index Scan	unique_npm_remote_repositories_namespace_id_and_repository_id	1 / 1	8.30	0.009ms	3 / 0	1/64
datastore.FindNpmRemoteVersionByID.ParentProbe	Limit → Index Scan	pk_npm_remote_packages	1 / 1	8.30	0.010ms	3 / 0	1/64
datastore.FindNpmRemoteVersionByID.VersionLeg	Limit → Index Scan	pk_npm_remote_versions	1 / 1	8.30	0.010ms	3 / 0	1/64
datastore.ListNpmRemotePackages.AscCursorNil	Limit → Index Scan	unique_npm_remote_packages_ns_id_repo_id_name	101 / 101	9.41	0.021ms	4 / 0	1/64
datastore.ListNpmRemotePackages.AscCursorSet	Limit → Index Scan	unique_npm_remote_packages_ns_id_repo_id_name	101 / 101	15.83	0.026ms	5 / 0	1/64
datastore.ListNpmRemotePackages.DescCursorNil	Limit → Index Scan Backward	unique_npm_remote_packages_ns_id_repo_id_name	101 / 101	10.28	0.026ms	6 / 0	1/64
datastore.ListNpmRemotePackages.DescCursorSet	Limit → Index Scan Backward	unique_npm_remote_packages_ns_id_repo_id_name	101 / 101	11.22	0.026ms	6 / 0	1/64
datastore.ListNpmRemoteVersionsByPackage.CreatedAtAscCursorNil	Limit → Index Scan	index_npm_remote_versions_on_ns_id_pkg_id_created_at_id	101 / 101	11.39	0.026ms	5 / 0	1/64
datastore.ListNpmRemoteVersionsByPackage.CreatedAtAscCursorSet	Limit → Index Scan	index_npm_remote_versions_on_ns_id_pkg_id_created_at_id	101 / 101	18.78	0.028ms	5 / 0	1/64
datastore.ListNpmRemoteVersionsByPackage.CreatedAtDescCursorNil	Limit → Index Scan Backward	index_npm_remote_versions_on_ns_id_pkg_id_created_at_id	101 / 101	11.39	0.027ms	6 / 0	1/64
datastore.ListNpmRemoteVersionsByPackage.CreatedAtDescCursorSet	Limit → Index Scan Backward	index_npm_remote_versions_on_ns_id_pkg_id_created_at_id	101 / 101	12.62	0.028ms	6 / 0	1/64
datastore.ListNpmRemoteVersionsByPackage.VersionAscCursorNil	Limit → Index Scan	unique_npm_remote_versions_ns_id_pkg_id_version	101 / 101	10.92	0.027ms	5 / 0	1/64
datastore.ListNpmRemoteVersionsByPackage.VersionAscCursorSet	Limit → Index Scan	unique_npm_remote_versions_ns_id_pkg_id_version	101 / 101	17.49	0.028ms	5 / 0	1/64
datastore.ListNpmRemoteVersionsByPackage.VersionDescCursorNil	Limit → Index Scan Backward	unique_npm_remote_versions_ns_id_pkg_id_version	101 / 101	10.92	0.028ms	6 / 0	1/64
datastore.ListNpmRemoteVersionsByPackage.VersionDescCursorSet	Limit → Index Scan Backward	unique_npm_remote_versions_ns_id_pkg_id_version	101 / 101	22.13	0.053ms	6 / 0	1/64

datastore.FindNpmRemotePackageInRepository

Summary: Plan matches the method's intent: a single-row (id, namespace_id) primary-key seek on pk_npm_remote_packages, pruned to 1 of 64 partitions. The remaining two terms (npm_remote_repository_id, soft_deleted_at IS NULL) apply as a Filter over the one row the PK returns, which costs nothing and is the correct shape for an id-addressed detail read. No anomalies.

Seed shape: npm_remote_packages=7500 (5000 live target repo, 2000 live sibling repo, 500 marked target), npm_remote_repositories=2, repositories=2, namespaces=1

Rendered SQL:

SELECT npm_remote_packages.namespace_id AS "npm_remote_packages.namespace_id",
     npm_remote_packages.id AS "npm_remote_packages.id",
     npm_remote_packages.npm_remote_repository_id AS "npm_remote_packages.npm_remote_repository_id",
     npm_remote_packages.last_downloaded_at AS "npm_remote_packages.last_downloaded_at",
     npm_remote_packages.soft_deleted_at AS "npm_remote_packages.soft_deleted_at",
     npm_remote_packages.name AS "npm_remote_packages.name",
     npm_remote_packages.scope AS "npm_remote_packages.scope"
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.id = $3::uuid)) AND (npm_remote_packages.soft_deleted_at IS NULL)
LIMIT $4;

Bound args: ['88539ef7-f7bf-4f49-ae9c-cb2f4933618c', 'b517a34f-c945-4ac9-9ea7-a74a8786e32f', '013217b2-a08b-4a30-8cbb-8479bf3bfb8f', 1]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..8.30 rows=1 width=109) (actual time=0.009..0.010 rows=1 loops=1)
  Buffers: shared hit=3
  ->  Index Scan using npm_remote_packages_p36_pkey on npm_remote_packages_p36 npm_remote_packages  (cost=0.28..8.30 rows=1 width=109) (actual time=0.009..0.009 rows=1 loops=1)
        Index Cond: ((id = '013217b2-a08b-4a30-8cbb-8479bf3bfb8f'::uuid) AND (namespace_id = '88539ef7-f7bf-4f49-ae9c-cb2f4933618c'::uuid))
        Filter: ((soft_deleted_at IS NULL) AND (npm_remote_repository_id = 'b517a34f-c945-4ac9-9ea7-a74a8786e32f'::uuid))
        Buffers: shared hit=3
Planning:
  Buffers: shared hit=75
Planning Time: 0.401 ms
Execution Time: 0.021 ms

Timings: planning 0.401ms, execution 0.021ms, total 0.422ms.

datastore.FindNpmRemoteRepositoryID

Summary: Plan matches the doc comment: both key columns are bound as equalities, so the probe is satisfied entirely from unique_npm_remote_repositories_namespace_id_and_repository_id with repository_id as the index's second column rather than a heap filter, and namespace_id prunes to 1 of 64 partitions. Seeded at 5000 bindings in the namespace to rule out a small-table Seq Scan, and the planner still picks the index (cost 8.30, 3 buffer hits). No anomalies.

Seed shape: npm_remote_repositories=5000, repositories=5000, namespaces=1

Rendered SQL:

SELECT npm_remote_repositories.id AS "npm_remote_repositories.id"
FROM public.npm_remote_repositories
WHERE (npm_remote_repositories.namespace_id = $1::uuid) AND (npm_remote_repositories.repository_id = $2::uuid)
LIMIT $3;

Bound args: ['c1eb1b6e-257a-45c1-aca3-2ac6b4f6c15f', '34b3b519-9386-4582-918f-ee5f44295454', 1]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..8.30 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=1)
  Buffers: shared hit=3
  ->  Index Scan using npm_remote_repositories_p38_namespace_id_repository_id_idx on npm_remote_repositories_p38 npm_remote_repositories  (cost=0.28..8.30 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=1)
        Index Cond: ((namespace_id = 'c1eb1b6e-257a-45c1-aca3-2ac6b4f6c15f'::uuid) AND (repository_id = '34b3b519-9386-4582-918f-ee5f44295454'::uuid))
        Buffers: shared hit=3
Planning:
  Buffers: shared hit=91
Planning Time: 0.422 ms
Execution Time: 0.020 ms

Timings: planning 0.422ms, execution 0.020ms, total 0.442ms.

datastore.FindNpmRemoteVersionByID.ParentProbe

Summary: The parent leg of FindNpmRemoteVersionByID plans identically to the package detail read: a pk_npm_remote_packages seek pruned to one partition, with the repository scope and the marker as a Filter over one row. 3 buffer hits, so the chain's second statement adds a single index descent to the request. No anomalies.

Seed shape: npm_remote_packages=7500 (5000 live target repo, 2000 live sibling repo, 500 marked target), npm_remote_repositories=2, repositories=2, namespaces=1

Rendered SQL:

SELECT npm_remote_packages.id AS "npm_remote_packages.id"
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: ['8b796221-3024-48bb-bca8-d190aa18aa25', '38ff0ab3-f644-4595-b4bb-8377c0610856', 'b970afd0-3e77-451c-879b-85e56c5826a5', 1]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..8.30 rows=1 width=16) (actual time=0.010..0.010 rows=1 loops=1)
  Buffers: shared hit=3
  ->  Index Scan using npm_remote_packages_p44_pkey on npm_remote_packages_p44 npm_remote_packages  (cost=0.28..8.30 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=1)
        Index Cond: ((id = '38ff0ab3-f644-4595-b4bb-8377c0610856'::uuid) AND (namespace_id = '8b796221-3024-48bb-bca8-d190aa18aa25'::uuid))
        Filter: ((soft_deleted_at IS NULL) AND (npm_remote_repository_id = 'b970afd0-3e77-451c-879b-85e56c5826a5'::uuid))
        Buffers: shared hit=3
Planning:
  Buffers: shared hit=65
Planning Time: 0.369 ms
Execution Time: 0.022 ms

Timings: planning 0.369ms, execution 0.022ms, total 0.391ms.

datastore.FindNpmRemoteVersionByID.VersionLeg

Summary: The version leg of the chain: a (id, namespace_id) primary-key seek on pk_npm_remote_versions, pruned to 1 of 64 partitions, with soft_deleted_at IS NULL as a Filter over the one row returned. Paired with the parent probe above, a version detail request costs two single-partition index descents whose cost is independent of how many versions the package holds, which is the two-statement shape the method's doc argues for. No anomalies.

Seed shape: npm_remote_versions=7500 (5000 live target package, 2000 live sibling package, 500 marked target), npm_remote_packages=2, npm_remote_repositories=1, repositories=1, namespaces=1

Rendered SQL:

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

Bound args: ['2904e3b5-0ab9-4dab-812b-9f344b40015b', '782c586c-3d53-471d-8d79-7117c307bf6a', 1]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..8.30 rows=1 width=93) (actual time=0.010..0.010 rows=1 loops=1)
  Buffers: shared hit=3
  ->  Index Scan using npm_remote_versions_p26_pkey on npm_remote_versions_p26 npm_remote_versions  (cost=0.28..8.30 rows=1 width=93) (actual time=0.009..0.009 rows=1 loops=1)
        Index Cond: ((id = '782c586c-3d53-471d-8d79-7117c307bf6a'::uuid) AND (namespace_id = '2904e3b5-0ab9-4dab-812b-9f344b40015b'::uuid))
        Filter: (soft_deleted_at IS NULL)
        Buffers: shared hit=3
Planning:
  Buffers: shared hit=103
Planning Time: 0.456 ms
Execution Time: 0.025 ms

Timings: planning 0.456ms, execution 0.025ms, total 0.481ms.

datastore.ListNpmRemotePackages.AscCursorNil

Summary: Plan matches the method's intent: a forward Index Scan on the partial unique unique_npm_remote_packages_ns_id_repo_id_name, with the namespace_id literal pruning to 1 of 64 partitions. The index leads with (namespace_id, npm_remote_repository_id) and carries WHERE soft_deleted_at IS NULL, so every predicate term is index-resident and ORDER BY name ASC is satisfied by the scan with no Sort node. Root estimate matches actual (101 / 101). No anomalies.

Seed shape: npm_remote_packages=7500 (5000 live target repo, 2000 live sibling repo, 500 marked target), npm_remote_repositories=2, repositories=2, namespaces=1

Rendered SQL:

SELECT npm_remote_packages.namespace_id AS "npm_remote_packages.namespace_id",
     npm_remote_packages.id AS "npm_remote_packages.id",
     npm_remote_packages.npm_remote_repository_id AS "npm_remote_packages.npm_remote_repository_id",
     npm_remote_packages.last_downloaded_at AS "npm_remote_packages.last_downloaded_at",
     npm_remote_packages.soft_deleted_at AS "npm_remote_packages.soft_deleted_at",
     npm_remote_packages.name AS "npm_remote_packages.name",
     npm_remote_packages.scope AS "npm_remote_packages.scope"
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)
ORDER BY npm_remote_packages.name ASC
LIMIT $3;

Bound args: ['3d9425a3-8231-4789-a387-6ba5258fd9fd', '6491fc07-6aef-4319-80cf-1b2a6cb68cc3', 101]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..9.41 rows=101 width=109) (actual time=0.010..0.021 rows=101 loops=1)
  Buffers: shared hit=4
  ->  Index Scan using npm_remote_packages_p50_namespace_id_npm_remote_repository__idx on npm_remote_packages_p50 npm_remote_packages  (cost=0.28..464.32 rows=5133 width=109) (actual time=0.010..0.017 rows=101 loops=1)
        Index Cond: ((namespace_id = '3d9425a3-8231-4789-a387-6ba5258fd9fd'::uuid) AND (npm_remote_repository_id = '6491fc07-6aef-4319-80cf-1b2a6cb68cc3'::uuid))
        Buffers: shared hit=4
Planning:
  Buffers: shared hit=275
Planning Time: 0.881 ms
Execution Time: 0.036 ms

Timings: planning 0.881ms, execution 0.036ms, total 0.917ms.

datastore.ListNpmRemotePackages.AscCursorSet

Summary: Same index and partition as the first page, reached by a forward scan. The cursor's name > $3 folds into the index range bound (it appears inside Index Cond, not as a Filter), which is what keeps a deep page O(limit) rather than O(offset): 5 buffer hits at the 2500th name, against 4 on the first page. No anomalies.

Seed shape: npm_remote_packages=7500 (5000 live target repo, 2000 live sibling repo, 500 marked target), npm_remote_repositories=2, repositories=2, namespaces=1

Rendered SQL:

SELECT npm_remote_packages.namespace_id AS "npm_remote_packages.namespace_id",
     npm_remote_packages.id AS "npm_remote_packages.id",
     npm_remote_packages.npm_remote_repository_id AS "npm_remote_packages.npm_remote_repository_id",
     npm_remote_packages.last_downloaded_at AS "npm_remote_packages.last_downloaded_at",
     npm_remote_packages.soft_deleted_at AS "npm_remote_packages.soft_deleted_at",
     npm_remote_packages.name AS "npm_remote_packages.name",
     npm_remote_packages.scope AS "npm_remote_packages.scope"
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: ['92425581-a8fd-4adc-aefb-95bc18ebe3eb', '63e0d903-f107-4c09-b0c9-048a379c418d', 'explain-2500', 101]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..15.83 rows=101 width=109) (actual time=0.012..0.026 rows=101 loops=1)
  Buffers: shared hit=5
  ->  Index Scan using npm_remote_packages_p59_namespace_id_npm_remote_repository__idx on npm_remote_packages_p59 npm_remote_packages  (cost=0.28..261.97 rows=1700 width=109) (actual time=0.011..0.021 rows=101 loops=1)
        Index Cond: ((namespace_id = '92425581-a8fd-4adc-aefb-95bc18ebe3eb'::uuid) AND (npm_remote_repository_id = '63e0d903-f107-4c09-b0c9-048a379c418d'::uuid) AND (name > 'explain-2500'::text))
        Buffers: shared hit=5
Planning:
  Buffers: shared hit=77
Planning Time: 0.407 ms
Execution Time: 0.039 ms

Timings: planning 0.407ms, execution 0.039ms, total 0.446ms.

datastore.ListNpmRemotePackages.DescCursorNil

Summary: Descending order runs as an Index Scan Backward over the same partial unique, so the descending page needs no Sort either. Partition pruning and estimate accuracy are unchanged from the ascending first page (101 / 101, 1 of 64 partitions). No anomalies.

Seed shape: npm_remote_packages=7500 (5000 live target repo, 2000 live sibling repo, 500 marked target), npm_remote_repositories=2, repositories=2, namespaces=1

Rendered SQL:

SELECT npm_remote_packages.namespace_id AS "npm_remote_packages.namespace_id",
     npm_remote_packages.id AS "npm_remote_packages.id",
     npm_remote_packages.npm_remote_repository_id AS "npm_remote_packages.npm_remote_repository_id",
     npm_remote_packages.last_downloaded_at AS "npm_remote_packages.last_downloaded_at",
     npm_remote_packages.soft_deleted_at AS "npm_remote_packages.soft_deleted_at",
     npm_remote_packages.name AS "npm_remote_packages.name",
     npm_remote_packages.scope AS "npm_remote_packages.scope"
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)
ORDER BY npm_remote_packages.name DESC
LIMIT $3;

Bound args: ['5a866be6-3b3d-44ab-9c05-f96b95f58c02', '9e046fe4-5718-4cad-bbec-80ddb18b6862', 101]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..10.28 rows=101 width=109) (actual time=0.012..0.026 rows=101 loops=1)
  Buffers: shared hit=6
  ->  Index Scan Backward using npm_remote_packages_p13_namespace_id_npm_remote_repository__idx on npm_remote_packages_p13 npm_remote_packages  (cost=0.28..508.32 rows=5133 width=109) (actual time=0.012..0.021 rows=101 loops=1)
        Index Cond: ((namespace_id = '5a866be6-3b3d-44ab-9c05-f96b95f58c02'::uuid) AND (npm_remote_repository_id = '9e046fe4-5718-4cad-bbec-80ddb18b6862'::uuid))
        Buffers: shared hit=6
Planning:
  Buffers: shared hit=79
Planning Time: 0.406 ms
Execution Time: 0.041 ms

Timings: planning 0.406ms, execution 0.041ms, total 0.447ms.

datastore.ListNpmRemotePackages.DescCursorSet

Summary: Backward scan with the cursor's name < $3 folded into the index range bound, the mirror of the ascending deep page. 6 buffer hits at the 2500th name confirms the bound is a seek and not a post-scan filter. No anomalies.

Seed shape: npm_remote_packages=7500 (5000 live target repo, 2000 live sibling repo, 500 marked target), npm_remote_repositories=2, repositories=2, namespaces=1

Rendered SQL:

SELECT npm_remote_packages.namespace_id AS "npm_remote_packages.namespace_id",
     npm_remote_packages.id AS "npm_remote_packages.id",
     npm_remote_packages.npm_remote_repository_id AS "npm_remote_packages.npm_remote_repository_id",
     npm_remote_packages.last_downloaded_at AS "npm_remote_packages.last_downloaded_at",
     npm_remote_packages.soft_deleted_at AS "npm_remote_packages.soft_deleted_at",
     npm_remote_packages.name AS "npm_remote_packages.name",
     npm_remote_packages.scope AS "npm_remote_packages.scope"
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 DESC
LIMIT $4;

Bound args: ['caf071aa-a94e-4b8a-ac1d-3efa9a807b38', '53dda79f-1dc5-4ae2-9799-066989261834', 'explain-2500', 101]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..11.22 rows=101 width=109) (actual time=0.012..0.026 rows=101 loops=1)
  Buffers: shared hit=6
  ->  Index Scan Backward using npm_remote_packages_p46_namespace_id_npm_remote_repository__idx on npm_remote_packages_p46 npm_remote_packages  (cost=0.28..372.21 rows=3433 width=109) (actual time=0.011..0.021 rows=101 loops=1)
        Index Cond: ((namespace_id = 'caf071aa-a94e-4b8a-ac1d-3efa9a807b38'::uuid) AND (npm_remote_repository_id = '53dda79f-1dc5-4ae2-9799-066989261834'::uuid) AND (name < 'explain-2500'::text))
        Buffers: shared hit=6
Planning:
  Buffers: shared hit=80
Planning Time: 0.411 ms
Execution Time: 0.039 ms

Timings: planning 0.411ms, execution 0.039ms, total 0.45ms.

datastore.ListNpmRemoteVersionsByPackage.CreatedAtAscCursorNil

Summary: Plan matches the method's intent: a forward Index Scan on index_npm_remote_versions_on_ns_id_pkg_id_created_at_id, pruned to 1 of 64 partitions. The index's (namespace_id, npm_remote_package_id, created_at, id) key set covers both the predicate and the two-column ORDER BY, and its soft_deleted_at IS NULL predicate absorbs the marker filter, so nothing is left for a Sort or a Filter. No anomalies.

Seed shape: npm_remote_versions=7500 (5000 live target package, 2000 live sibling package, 500 marked target), npm_remote_packages=2, npm_remote_repositories=1, repositories=1, namespaces=1

Rendered SQL:

SELECT npm_remote_versions.namespace_id AS "npm_remote_versions.namespace_id",
     npm_remote_versions.id AS "npm_remote_versions.id",
     npm_remote_versions.npm_remote_package_id AS "npm_remote_versions.npm_remote_package_id",
     npm_remote_versions.last_downloaded_at AS "npm_remote_versions.last_downloaded_at",
     npm_remote_versions.soft_deleted_at AS "npm_remote_versions.soft_deleted_at",
     npm_remote_versions.created_at AS "npm_remote_versions.created_at",
     npm_remote_versions.version AS "npm_remote_versions.version",
     npm_remote_versions.size_bytes AS "npm_remote_versions.size_bytes"
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 ASC, npm_remote_versions.id ASC
LIMIT $3;

Bound args: ['7120ea5f-e1b5-4346-a0d7-4a40ef98f984', 'f2dc2f4d-289b-40b8-b9a5-349fc7caada3', 101]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..11.39 rows=101 width=93) (actual time=0.010..0.026 rows=101 loops=1)
  Buffers: shared hit=5
  ->  Index Scan using npm_remote_versions_p04_namespace_id_npm_remote_package_id_idx2 on npm_remote_versions_p04 npm_remote_versions  (cost=0.28..564.75 rows=5133 width=93) (actual time=0.010..0.021 rows=101 loops=1)
        Index Cond: ((namespace_id = '7120ea5f-e1b5-4346-a0d7-4a40ef98f984'::uuid) AND (npm_remote_package_id = 'f2dc2f4d-289b-40b8-b9a5-349fc7caada3'::uuid))
        Buffers: shared hit=5
Planning:
  Buffers: shared hit=322
Planning Time: 1.085 ms
Execution Time: 0.039 ms

Timings: planning 1.085ms, execution 0.039ms, total 1.124ms.

datastore.ListNpmRemoteVersionsByPackage.CreatedAtAscCursorSet

Summary: The row-value bound (created_at, id) > ($3, $4) renders as a single ROW(...) > ROW(...) comparison and lands inside Index Cond, so Postgres folds it into the index range instead of re-checking each row. That is the distinction between an O(limit) deep page and an O(offset) one, and it is why the mid-dataset page costs 5 buffer hits rather than scanning to the cursor. No anomalies.

Seed shape: npm_remote_versions=7500 (5000 live target package, 2000 live sibling package, 500 marked target), npm_remote_packages=2, npm_remote_repositories=1, repositories=1, namespaces=1

Rendered SQL:

SELECT npm_remote_versions.namespace_id AS "npm_remote_versions.namespace_id",
     npm_remote_versions.id AS "npm_remote_versions.id",
     npm_remote_versions.npm_remote_package_id AS "npm_remote_versions.npm_remote_package_id",
     npm_remote_versions.last_downloaded_at AS "npm_remote_versions.last_downloaded_at",
     npm_remote_versions.soft_deleted_at AS "npm_remote_versions.soft_deleted_at",
     npm_remote_versions.created_at AS "npm_remote_versions.created_at",
     npm_remote_versions.version AS "npm_remote_versions.version",
     npm_remote_versions.size_bytes AS "npm_remote_versions.size_bytes"
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 ASC, npm_remote_versions.id ASC
LIMIT $5;

Bound args: ['1ab83bcb-33a1-4062-b665-2e19867bd0e7', '90fc9c07-6d80-42e7-ac69-9592c02e4192', '2026-01-02 17:40:00+00', '66666666-6666-4666-8666-666666666666', 101]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..18.78 rows=101 width=93) (actual time=0.011..0.028 rows=101 loops=1)
  Buffers: shared hit=5
  ->  Index Scan using npm_remote_versions_p30_namespace_id_npm_remote_package_id_idx2 on npm_remote_versions_p30 npm_remote_versions  (cost=0.28..314.88 rows=1718 width=93) (actual time=0.011..0.023 rows=101 loops=1)
        Index Cond: ((namespace_id = '1ab83bcb-33a1-4062-b665-2e19867bd0e7'::uuid) AND (npm_remote_package_id = '90fc9c07-6d80-42e7-ac69-9592c02e4192'::uuid) AND (ROW(created_at, id) > ROW('2026-01-02 17:40:00+00'::timestamp with time zone, '66666666-6666-4666-8666-666666666666'::uuid)))
        Buffers: shared hit=5
Planning:
  Buffers: shared hit=127
Planning Time: 0.582 ms
Execution Time: 0.059 ms

Timings: planning 0.582ms, execution 0.059ms, total 0.641ms.

datastore.ListNpmRemoteVersionsByPackage.CreatedAtDescCursorNil

Summary: The API's default sort. created_at DESC, id DESC runs as an Index Scan Backward over the created_at index with no Sort node, so the default page is as cheap as the ascending one (101 / 101, 6 buffer hits, 1 of 64 partitions). No anomalies.

Seed shape: npm_remote_versions=7500 (5000 live target package, 2000 live sibling package, 500 marked target), npm_remote_packages=2, npm_remote_repositories=1, repositories=1, namespaces=1

Rendered SQL:

SELECT npm_remote_versions.namespace_id AS "npm_remote_versions.namespace_id",
     npm_remote_versions.id AS "npm_remote_versions.id",
     npm_remote_versions.npm_remote_package_id AS "npm_remote_versions.npm_remote_package_id",
     npm_remote_versions.last_downloaded_at AS "npm_remote_versions.last_downloaded_at",
     npm_remote_versions.soft_deleted_at AS "npm_remote_versions.soft_deleted_at",
     npm_remote_versions.created_at AS "npm_remote_versions.created_at",
     npm_remote_versions.version AS "npm_remote_versions.version",
     npm_remote_versions.size_bytes AS "npm_remote_versions.size_bytes"
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: ['25f8e413-67e5-490d-bde5-fe7ef6492cd0', '4ba14eca-13d9-482f-b972-be974ee30a3b', 101]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..11.39 rows=101 width=93) (actual time=0.011..0.027 rows=101 loops=1)
  Buffers: shared hit=6
  ->  Index Scan Backward using npm_remote_versions_p61_namespace_id_npm_remote_package_id_idx2 on npm_remote_versions_p61 npm_remote_versions  (cost=0.28..564.75 rows=5133 width=93) (actual time=0.010..0.023 rows=101 loops=1)
        Index Cond: ((namespace_id = '25f8e413-67e5-490d-bde5-fe7ef6492cd0'::uuid) AND (npm_remote_package_id = '4ba14eca-13d9-482f-b972-be974ee30a3b'::uuid))
        Buffers: shared hit=6
Planning:
  Buffers: shared hit=128
Planning Time: 0.580 ms
Execution Time: 0.041 ms

Timings: planning 0.580ms, execution 0.041ms, total 0.621ms.

datastore.ListNpmRemoteVersionsByPackage.CreatedAtDescCursorSet

Summary: Backward scan with the row-value bound ROW(created_at, id) < ROW($3, $4) inside Index Cond, the descending mirror of the ascending deep page. This is the combination the versions list serves by default, and it holds the flat-latency property at 6 buffer hits. No anomalies.

Seed shape: npm_remote_versions=7500 (5000 live target package, 2000 live sibling package, 500 marked target), npm_remote_packages=2, npm_remote_repositories=1, repositories=1, namespaces=1

Rendered SQL:

SELECT npm_remote_versions.namespace_id AS "npm_remote_versions.namespace_id",
     npm_remote_versions.id AS "npm_remote_versions.id",
     npm_remote_versions.npm_remote_package_id AS "npm_remote_versions.npm_remote_package_id",
     npm_remote_versions.last_downloaded_at AS "npm_remote_versions.last_downloaded_at",
     npm_remote_versions.soft_deleted_at AS "npm_remote_versions.soft_deleted_at",
     npm_remote_versions.created_at AS "npm_remote_versions.created_at",
     npm_remote_versions.version AS "npm_remote_versions.version",
     npm_remote_versions.size_bytes AS "npm_remote_versions.size_bytes"
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: ['d3a2a6b8-37e4-4f21-bcc9-ec87fe2accbe', '469d2581-5689-459e-91de-9ef17bf7f746', '2026-01-02 17:40:00+00', '66666666-6666-4666-8666-666666666666', 101]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..12.62 rows=101 width=93) (actual time=0.010..0.028 rows=101 loops=1)
  Buffers: shared hit=6
  ->  Index Scan Backward using npm_remote_versions_p32_namespace_id_npm_remote_package_id_idx2 on npm_remote_versions_p32 npm_remote_versions  (cost=0.28..417.43 rows=3415 width=93) (actual time=0.010..0.023 rows=101 loops=1)
        Index Cond: ((namespace_id = 'd3a2a6b8-37e4-4f21-bcc9-ec87fe2accbe'::uuid) AND (npm_remote_package_id = '469d2581-5689-459e-91de-9ef17bf7f746'::uuid) AND (ROW(created_at, id) < ROW('2026-01-02 17:40:00+00'::timestamp with time zone, '66666666-6666-4666-8666-666666666666'::uuid)))
        Buffers: shared hit=6
Planning:
  Buffers: shared hit=134
Planning Time: 0.579 ms
Execution Time: 0.059 ms

Timings: planning 0.579ms, execution 0.059ms, total 0.638ms.

datastore.ListNpmRemoteVersionsByPackage.VersionAscCursorNil

Summary: The version sort reads its own index: a forward scan on the partial unique unique_npm_remote_versions_ns_id_pkg_id_version, pruned to 1 of 64 partitions, with ORDER BY version ASC satisfied by the index key. Ordering is lexicographic rather than semver, which the type's doc states. No anomalies.

Seed shape: npm_remote_versions=7500 (5000 live target package, 2000 live sibling package, 500 marked target), npm_remote_packages=2, npm_remote_repositories=1, repositories=1, namespaces=1

Rendered SQL:

SELECT npm_remote_versions.namespace_id AS "npm_remote_versions.namespace_id",
     npm_remote_versions.id AS "npm_remote_versions.id",
     npm_remote_versions.npm_remote_package_id AS "npm_remote_versions.npm_remote_package_id",
     npm_remote_versions.last_downloaded_at AS "npm_remote_versions.last_downloaded_at",
     npm_remote_versions.soft_deleted_at AS "npm_remote_versions.soft_deleted_at",
     npm_remote_versions.created_at AS "npm_remote_versions.created_at",
     npm_remote_versions.version AS "npm_remote_versions.version",
     npm_remote_versions.size_bytes AS "npm_remote_versions.size_bytes"
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.version ASC
LIMIT $3;

Bound args: ['60a674c5-2ab5-4d7e-937a-bf1134694aad', 'b01ebde0-8ccb-4d6a-888e-b5d8df622378', 101]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..10.92 rows=101 width=93) (actual time=0.010..0.027 rows=101 loops=1)
  Buffers: shared hit=5
  ->  Index Scan using npm_remote_versions_p24_namespace_id_npm_remote_package_id__idx on npm_remote_versions_p24 npm_remote_versions  (cost=0.28..540.75 rows=5133 width=93) (actual time=0.010..0.022 rows=101 loops=1)
        Index Cond: ((namespace_id = '60a674c5-2ab5-4d7e-937a-bf1134694aad'::uuid) AND (npm_remote_package_id = 'b01ebde0-8ccb-4d6a-888e-b5d8df622378'::uuid))
        Buffers: shared hit=5
Planning:
  Buffers: shared hit=116
Planning Time: 0.544 ms
Execution Time: 0.042 ms

Timings: planning 0.544ms, execution 0.042ms, total 0.586ms.

datastore.ListNpmRemoteVersionsByPackage.VersionAscCursorSet

Summary: Same index as the version first page, with the cursor's version > $3 folded into the index range bound. 5 buffer hits at the 2500th version. No anomalies.

Seed shape: npm_remote_versions=7500 (5000 live target package, 2000 live sibling package, 500 marked target), npm_remote_packages=2, npm_remote_repositories=1, repositories=1, namespaces=1

Rendered SQL:

SELECT npm_remote_versions.namespace_id AS "npm_remote_versions.namespace_id",
     npm_remote_versions.id AS "npm_remote_versions.id",
     npm_remote_versions.npm_remote_package_id AS "npm_remote_versions.npm_remote_package_id",
     npm_remote_versions.last_downloaded_at AS "npm_remote_versions.last_downloaded_at",
     npm_remote_versions.soft_deleted_at AS "npm_remote_versions.soft_deleted_at",
     npm_remote_versions.created_at AS "npm_remote_versions.created_at",
     npm_remote_versions.version AS "npm_remote_versions.version",
     npm_remote_versions.size_bytes AS "npm_remote_versions.size_bytes"
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.version > $3::text)
ORDER BY npm_remote_versions.version ASC
LIMIT $4;

Bound args: ['900bd3d6-4422-45b0-9912-6f1c5d3099a2', '80538164-c3c9-40d9-8ba4-55656bdf9cb8', 'explain-2500', 101]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..17.49 rows=101 width=93) (actual time=0.011..0.028 rows=101 loops=1)
  Buffers: shared hit=5
  ->  Index Scan using npm_remote_versions_p46_namespace_id_npm_remote_package_id__idx on npm_remote_versions_p46 npm_remote_versions  (cost=0.28..289.91 rows=1700 width=93) (actual time=0.011..0.023 rows=101 loops=1)
        Index Cond: ((namespace_id = '900bd3d6-4422-45b0-9912-6f1c5d3099a2'::uuid) AND (npm_remote_package_id = '80538164-c3c9-40d9-8ba4-55656bdf9cb8'::uuid) AND (version > 'explain-2500'::text))
        Buffers: shared hit=5
Planning:
  Buffers: shared hit=118
Planning Time: 0.538 ms
Execution Time: 0.044 ms

Timings: planning 0.538ms, execution 0.044ms, total 0.582ms.

datastore.ListNpmRemoteVersionsByPackage.VersionDescCursorNil

Summary: ORDER BY version DESC runs as an Index Scan Backward over the same partial unique with no Sort node. No anomalies.

Seed shape: npm_remote_versions=7500 (5000 live target package, 2000 live sibling package, 500 marked target), npm_remote_packages=2, npm_remote_repositories=1, repositories=1, namespaces=1

Rendered SQL:

SELECT npm_remote_versions.namespace_id AS "npm_remote_versions.namespace_id",
     npm_remote_versions.id AS "npm_remote_versions.id",
     npm_remote_versions.npm_remote_package_id AS "npm_remote_versions.npm_remote_package_id",
     npm_remote_versions.last_downloaded_at AS "npm_remote_versions.last_downloaded_at",
     npm_remote_versions.soft_deleted_at AS "npm_remote_versions.soft_deleted_at",
     npm_remote_versions.created_at AS "npm_remote_versions.created_at",
     npm_remote_versions.version AS "npm_remote_versions.version",
     npm_remote_versions.size_bytes AS "npm_remote_versions.size_bytes"
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.version DESC
LIMIT $3;

Bound args: ['d9f2e057-998f-4d4f-a887-d45be08b9782', '732f2a02-b236-4727-996d-8f0055241c97', 101]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..10.92 rows=101 width=93) (actual time=0.011..0.028 rows=101 loops=1)
  Buffers: shared hit=6
  ->  Index Scan Backward using npm_remote_versions_p28_namespace_id_npm_remote_package_id__idx on npm_remote_versions_p28 npm_remote_versions  (cost=0.28..540.75 rows=5133 width=93) (actual time=0.011..0.023 rows=101 loops=1)
        Index Cond: ((namespace_id = 'd9f2e057-998f-4d4f-a887-d45be08b9782'::uuid) AND (npm_remote_package_id = '732f2a02-b236-4727-996d-8f0055241c97'::uuid))
        Buffers: shared hit=6
Planning:
  Buffers: shared hit=120
Planning Time: 0.570 ms
Execution Time: 0.042 ms

Timings: planning 0.570ms, execution 0.042ms, total 0.612ms.

datastore.ListNpmRemoteVersionsByPackage.VersionDescCursorSet

Summary: Backward scan with version < $3 in the Index Cond. Execution time (0.077 ms) is the highest in the set and still two orders of magnitude under any page budget; the gap is measurement noise on a 101-row read, not a plan difference. No anomalies.

Seed shape: npm_remote_versions=7500 (5000 live target package, 2000 live sibling package, 500 marked target), npm_remote_packages=2, npm_remote_repositories=1, repositories=1, namespaces=1

Rendered SQL:

SELECT npm_remote_versions.namespace_id AS "npm_remote_versions.namespace_id",
     npm_remote_versions.id AS "npm_remote_versions.id",
     npm_remote_versions.npm_remote_package_id AS "npm_remote_versions.npm_remote_package_id",
     npm_remote_versions.last_downloaded_at AS "npm_remote_versions.last_downloaded_at",
     npm_remote_versions.soft_deleted_at AS "npm_remote_versions.soft_deleted_at",
     npm_remote_versions.created_at AS "npm_remote_versions.created_at",
     npm_remote_versions.version AS "npm_remote_versions.version",
     npm_remote_versions.size_bytes AS "npm_remote_versions.size_bytes"
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.version < $3::text)
ORDER BY npm_remote_versions.version DESC
LIMIT $4;

Bound args: ['9c3f7a86-869c-4517-8e6b-34a022ba389d', '8c5d4fdd-5e44-4f31-b196-ce4202e4f805', 'explain-2500', 101]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..22.13 rows=101 width=93) (actual time=0.026..0.053 rows=101 loops=1)
  Buffers: shared hit=6
  ->  Index Scan Backward using npm_remote_versions_p46_namespace_id_npm_remote_package_id__idx on npm_remote_versions_p46 npm_remote_versions  (cost=0.28..742.71 rows=3433 width=93) (actual time=0.025..0.044 rows=101 loops=1)
        Index Cond: ((namespace_id = '9c3f7a86-869c-4517-8e6b-34a022ba389d'::uuid) AND (npm_remote_package_id = '8c5d4fdd-5e44-4f31-b196-ce4202e4f805'::uuid) AND (version < 'explain-2500'::text))
        Buffers: shared hit=6
Planning:
  Buffers: shared hit=120
Planning Time: 1.098 ms
Execution Time: 0.077 ms

Timings: planning 1.098ms, execution 0.077ms, total 1.175ms.

Related to #314