feat(npm): package, version, tag datastore read queries (S11 Step 4a) (!470) · Merge requests · GitLab.org / ops / Artifact Registry

📦 What

Step 4a of the npm local plan — the relational read-side datastore queries the packument path consumes: NpmPackageByName, NpmVersionsByPackage, NpmTagsByPackage.

Originally the single Step 4 MR; split into two cohesive slices by storage shape. This MR is the three relational stores; the two content-addressed stores (npm_files, npm_metadata_files) follow in Step 4b (!476 (merged)), stacked on this branch and sharing the fixture layer introduced here.

Store file	Read method
`npm_packages.go`	`NpmPackageByName`
`npm_versions.go`	`NpmVersionsByPackage`
`npm_tags.go`	`NpmTagsByPackage`

All queries follow docs/dev/database-query-patterns.md: jet via the pg-aliased package with columns listed explicitly (no AllColumns), the simple query protocol (PgBouncer-safe), client.DB() resolved per query, and qrm.ErrNoRows wrapped as datastore.ErrNotFound on the single-row read. Every filter leads with the namespace_id partition key (ADR 022), is index-backed (the Step-2 unique indexes for the point and tag reads; a dedicated keyset index added in this MR for the version-list order — see Database Review Evidence below), and excludes soft-deleted rows via soft_deleted_at IS NULL. The two list reads (NpmVersionsByPackage, NpmTagsByPackage) are keyset-paginated (a *ByPackageParams cursor + Limit); the single-row NpmPackageByName is not.

✅ Acceptance coverage

Step 4 carries no numbered spec ACs (those land in the consuming handler steps); its acceptance is the plan's per-step bullet. Evidence in npm_read_integration_test.go:

Behaviour	Test
Each query returns expected rows for the fixture state	`TestNpmPackageStore_NpmPackageByName`, `TestNpmVersionStore_NpmVersionsByPackage`, `TestNpmTagStore_NpmTagsByPackage`
Missing row → `ErrNotFound` (single-row) / empty slice (list)	single-row miss + list empty-slice subtests
Soft-deleted rows excluded	soft-delete subtests on packages / versions
Scope-prefixed `@scope/foo` looked up by full name (scope stored with the `@` sigil)	scoped-name subtest on `NpmPackageByName`
Namespace isolation (+ repo / package scoping)	cross-namespace subtest per store; cross-repo on packages; cross-package on tags

🧪 Testing

internal/datastore/npm_read_integration_test.go (integration, testcontainers PostgreSQL).
internal/datastore/npm_read_test.go (unit): argument-guard rejections + constructor nil-client panics.
All integration + unit tests pass locally; full pre-commit suite (gofmt, goimports, golangci-lint, go-test, gitlint) green.

MR size: ~1,120 LOC hand-written, majority test code, now including the 20260615120000 keyset-index migration (plus its generated structure.sql partition DDL). One cohesive slice (the three relational read stores over near-identical jet queries); the content-addressed pair is split out to !476 (merged).

♻️ Review feedback

Per-store argument-guard sentinels (errNpmPackageNilContext / errNpmPackageZeroNamespace, and the errNpmVersion* / errNpmTag* equivalents) replace the shared guards.go sentinels on all three read methods, so a caller doing errors.Is across stores can attribute a guard failure to the store that raised it — matching the container_blobs / container_images pattern. Per the AppSec review; covered by npm_read_test.go.
NpmVersionsByPackage ordering gains an id tiebreaker (ORDER_BY(created_at ASC, id ASC)): created_at is DEFAULT NOW() and non-unique, so the id makes the order total. The contract is asserted against an ordered slice (the subtest seeds an earliest row plus two sharing one created_at, so a reversed or dropped ORDER BY fails; tags assert the name ASC slice) — 798562d.
Keyset pagination on the two list reads: NpmVersionsByPackage / NpmTagsByPackage now take a *ByPackageParams (keyset cursor + Limit), key on (created_at, id) and name respectively, reject a non-positive Limit, and expose no unpaginated variant — mirroring container_manifest.ListReferrersPage. The prior all-rows reads would invite an unbounded materialization once the packument consumers (Step 11/25) land. Covered by the *_Keyset multi-page walks and the limit guard — 342512a.
Database review (/db-review-prep) surfaced that the NpmVersionsByPackage keyset was not index-backed: the expanded created_at > x OR (created_at = x AND id > y) cursor degraded to a post-scan Filter (Rows Removed by Filter: 2501 at a mid-range page — O(offset) per page, ~O(n²) over the packument generator's full-history paging). Fixed with a partial index index_npm_versions_on_ns_id_pkg_id_created_at_id on (namespace_id, npm_package_id, created_at, id) (migration 20260615120000) and switching the cursor to a row-value bound ROW(created_at, id) > ROW(…) — both required for PostgreSQL to push the bound into the index range. The keyset is now a true Index-Cond seek (0 rows discarded at any page depth, 5 buffers vs 83); npm_tags was already optimal. Full EXPLAIN evidence below — a7bc494.
Guard test cases inlined as anonymous tests slices in each test function (dropped the shared npmGuardCase named struct), per the datastore test convention.
Declined: narrow per-store reader interfaces, keeping the package-wide concrete-*Store convention (consumers define their own interfaces at the call site).

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 (5000 rows in one partition) 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.NpmPackageByName`	Index Scan	`npm_packages…ns_id_npm_repository_id_name_idx`	1 / 1	8.30	0.013ms	3 / 0	1
`datastore.NpmTagsByPackage.FirstPage`	Index Scan	`npm_tags…ns_id_npm_package_id_name_idx`	50 / 50	4.38	0.020ms	3 / 0	1
`datastore.NpmTagsByPackage.KeysetPage`	Index Scan	`npm_tags…ns_id_npm_package_id_name_idx`	50 / 50	5.52	0.019ms	4 / 0	1
`datastore.NpmVersionsByPackage.FirstPage`	Index Scan	`npm_versions…ns_id_pkg_id_created_at_id_idx`	50 / 50	4.37	0.022ms	3 / 0	1
`datastore.NpmVersionsByPackage.KeysetPage`	Index Scan	`npm_versions…ns_id_pkg_id_created_at_id_idx`	50 / 50	5.69	0.040ms	5 / 0	1

Query notes:

datastore.NpmVersionsByPackage (both branches): the initial run found the keyset was not index-backed — no index covered (namespace_id, npm_package_id, created_at, id), so the planner drove off index_npm_versions_on_ns_id_created_at and applied both npm_package_id and the cursor as a post-scan Filter (Rows Removed by Filter: 2501 at the mid-range page, 83 buffers — O(offset) per page). This MR fixes it with (a) a partial index index_npm_versions_on_ns_id_pkg_id_created_at_id on (namespace_id, npm_package_id, created_at, id) WHERE soft_deleted_at IS NULL (migration 20260615120000) and (b) expressing the cursor as a row-value bound ROW(created_at, id) > ROW($, $) in NpmVersionsByPackage. Both are required: the index alone leaves the expanded boolean form as a Filter; the row-value form is what Postgres pushes into the index range. With both, the keyset is a true Index-Cond seek — 0 rows discarded, 5 buffers, regardless of page depth. Mirrors ContainerTagStore.ListByImage and its index (issue #109 (closed)). No remaining anomalies.
datastore.NpmTagsByPackage (both branches): textbook keyset already — the unique (namespace_id, npm_package_id, name) index serves the equality predicates and the name > cursor keyset as Index Conds, so each page seeks straight to the cursor with 0 rows discarded. No anomalies.
datastore.NpmPackageByName: unique-index point lookup pruned to one partition. No anomalies.

datastore.NpmPackageByName

Summary: Plan matches intent — an Index Scan over the unique (namespace_id, npm_repository_id, name) index, with the namespace_id literal pruning to one of 64 partitions. Estimate matches reality (1 / 1) and execution is 0.013ms over 5000 seeded packages. No anomalies.

Seed shape: namespaces=1, repositories=1, npm_repositories=1, npm_packages=5000

Rendered SQL:

SELECT npm_packages.namespace_id, npm_packages.id, npm_packages.npm_repository_id,
       npm_packages.last_downloaded_at, npm_packages.soft_deleted_at,
       npm_packages.versions_count, npm_packages.tags_count, npm_packages.name,
       npm_packages.scope
FROM public.npm_packages
WHERE (((npm_packages.namespace_id = $1::uuid) AND (npm_packages.npm_repository_id = $2)) AND (npm_packages.name = $3::text)) AND (npm_packages.soft_deleted_at IS NULL)
LIMIT $4;

Bound args: [<namespace uuid>, 1, 'review-prep-pkg-002501', 1]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.28..8.30 rows=1 width=111) (actual time=0.006..0.006 rows=1 loops=1)
   Buffers: shared hit=3
   ->  Index Scan using npm_packages_p44_namespace_id_npm_repository_id_name_idx on npm_packages_p44 npm_packages  (cost=0.28..8.30 rows=1 width=111) (actual time=0.006..0.006 rows=1 loops=1)
         Index Cond: ((namespace_id = '...'::uuid) AND (npm_repository_id = '1'::bigint) AND (name = 'review-prep-pkg-002501'::text))
         Buffers: shared hit=3
 Planning:
   Buffers: shared hit=276
 Planning Time: 0.452 ms
 Execution Time: 0.013 ms

Timings: planning 0.452ms, execution 0.013ms, total 0.465ms.

datastore.NpmTagsByPackage.FirstPage

Summary: Plan matches intent — Index Scan over the unique (namespace_id, npm_package_id, name) index, both equality predicates as Index Conds and name ASC provided by the index (no Sort), pruned to one partition. 50 / 50 rows, 0.020ms. No anomalies.

Seed shape: namespaces=1, repositories=1, npm_repositories=1, npm_packages=1, npm_versions=1, npm_tags=5000

Rendered SQL:

SELECT npm_tags.namespace_id, npm_tags.id, npm_tags.npm_package_id,
       npm_tags.npm_version_id, npm_tags.name
FROM public.npm_tags
WHERE (npm_tags.namespace_id = $1::uuid) AND (npm_tags.npm_package_id = $2)
ORDER BY npm_tags.name ASC
LIMIT $3;

Bound args: [<namespace uuid>, <package id>, 50]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.28..4.38 rows=50 width=63) (actual time=0.006..0.010 rows=50 loops=1)
   Buffers: shared hit=3
   ->  Index Scan using npm_tags_p04_namespace_id_npm_package_id_name_idx on npm_tags_p04 npm_tags  (cost=0.28..409.78 rows=5000 width=63) (actual time=0.006..0.008 rows=50 loops=1)
         Index Cond: ((namespace_id = '...'::uuid) AND (npm_package_id = '...'::bigint))
         Buffers: shared hit=3
 Planning:
   Buffers: shared hit=320
 Planning Time: 0.523 ms
 Execution Time: 0.020 ms

Timings: planning 0.523ms, execution 0.020ms, total 0.543ms.

datastore.NpmTagsByPackage.KeysetPage

Summary: Textbook keyset — the unique (namespace_id, npm_package_id, name) index serves both equality predicates and the name > cursor keyset as Index Conds, so the scan seeks straight to the cursor with 0 rows discarded. 50 / 50 rows, 0.019ms, one partition. No anomalies.

Seed shape: namespaces=1, repositories=1, npm_repositories=1, npm_packages=1, npm_versions=1, npm_tags=5000

Rendered SQL:

SELECT npm_tags.namespace_id, npm_tags.id, npm_tags.npm_package_id,
       npm_tags.npm_version_id, npm_tags.name
FROM public.npm_tags
WHERE ((npm_tags.namespace_id = $1::uuid) AND (npm_tags.npm_package_id = $2)) AND (npm_tags.name > $3::text)
ORDER BY npm_tags.name ASC
LIMIT $4;

Bound args: [<namespace uuid>, <package id>, 'review-prep-tag-002501', 50]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.28..5.52 rows=50 width=63) (actual time=0.006..0.012 rows=50 loops=1)
   Buffers: shared hit=4
   ->  Index Scan using npm_tags_p58_namespace_id_npm_package_id_name_idx on npm_tags_p58 npm_tags  (cost=0.28..262.28 rows=2500 width=63) (actual time=0.006..0.010 rows=50 loops=1)
         Index Cond: ((namespace_id = '...'::uuid) AND (npm_package_id = '...'::bigint) AND (name > 'review-prep-tag-002501'::text))
         Buffers: shared hit=4
 Planning:
   Buffers: shared hit=64
 Planning Time: 0.177 ms
 Execution Time: 0.019 ms

Timings: planning 0.177ms, execution 0.019ms, total 0.196ms.

datastore.NpmVersionsByPackage.FirstPage

Summary: Index Scan over the partial index (namespace_id, npm_package_id, created_at, id) WHERE soft_deleted_at IS NULL added by this MR — both equality predicates are Index Conds, (created_at, id) order comes from the index (no Sort), and the partial predicate absorbs the soft_deleted_at IS NULL filter (no residual Filter). 50 / 50 rows, 0.022ms. Before the index the planner drove off (namespace_id, created_at) with npm_package_id as a Filter plus an Incremental Sort. No anomalies.

Seed shape: namespaces=1, repositories=1, npm_repositories=1, npm_packages=1, npm_versions=5000

Rendered SQL:

SELECT npm_versions.namespace_id, npm_versions.id, npm_versions.npm_package_id,
       npm_versions.last_downloaded_at, npm_versions.soft_deleted_at,
       npm_versions.created_at, npm_versions.version, npm_versions.package_json,
       npm_versions.gitlab_user_id, npm_versions.gitlab_project_id,
       npm_versions.gitlab_git_commit_sha
FROM public.npm_versions
WHERE ((npm_versions.namespace_id = $1::uuid) AND (npm_versions.npm_package_id = $2)) AND (npm_versions.soft_deleted_at IS NULL)
ORDER BY npm_versions.created_at ASC, npm_versions.id ASC
LIMIT $3;

Bound args: [<namespace uuid>, <package id>, 50]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.28..4.37 rows=50 width=180) (actual time=0.009..0.013 rows=50 loops=1)
   Buffers: shared hit=3
   ->  Index Scan using npm_versions_p44_namespace_id_npm_package_id_created_at_id_idx on npm_versions_p44 npm_versions  (cost=0.28..408.91 rows=5000 width=180) (actual time=0.008..0.010 rows=50 loops=1)
         Index Cond: ((namespace_id = '...'::uuid) AND (npm_package_id = '...'::bigint))
         Buffers: shared hit=3
 Planning:
   Buffers: shared hit=109
 Planning Time: 0.341 ms
 Execution Time: 0.022 ms

Timings: planning 0.341ms, execution 0.022ms, total 0.363ms.

datastore.NpmVersionsByPackage.KeysetPage

Summary: True keyset seek — with this MR's partial index plus the row-value bound ROW(created_at, id) > ROW($, $), the cursor is an Index Cond, so the scan seeks straight to the page boundary: 0 rows discarded at any depth, 5 buffers, 0.040ms. Before the fix (expanded boolean form, no covering index) the same mid-range page discarded 2501 rows as a post-scan Filter (83 buffers) — O(offset). Fixed by migration 20260615120000 and the row-value predicate. No anomalies.

Seed shape: namespaces=1, repositories=1, npm_repositories=1, npm_packages=1, npm_versions=5000

Rendered SQL:

SELECT npm_versions.namespace_id, npm_versions.id, npm_versions.npm_package_id,
       npm_versions.last_downloaded_at, npm_versions.soft_deleted_at,
       npm_versions.created_at, npm_versions.version, npm_versions.package_json,
       npm_versions.gitlab_user_id, npm_versions.gitlab_project_id,
       npm_versions.gitlab_git_commit_sha
FROM public.npm_versions
WHERE (((npm_versions.namespace_id = $1::uuid) AND (npm_versions.npm_package_id = $2)) AND (npm_versions.soft_deleted_at IS NULL)) AND (ROW(npm_versions.created_at, npm_versions.id) > ROW($3::timestamp with time zone, $4))
ORDER BY npm_versions.created_at ASC, npm_versions.id ASC
LIMIT $5;

Bound args: [<namespace uuid>, <package id>, <mid-range created_at>, <mid-range id>, 50]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.28..5.69 rows=50 width=180) (actual time=0.005..0.014 rows=50 loops=1)
   Buffers: shared hit=5
   ->  Index Scan using npm_versions_p44_namespace_id_npm_package_id_created_at_id_idx on npm_versions_p44 npm_versions  (cost=0.28..270.57 rows=2499 width=180) (actual time=0.005..0.011 rows=50 loops=1)
         Index Cond: ((namespace_id = '...'::uuid) AND (npm_package_id = '...'::bigint) AND (ROW(created_at, id) > ROW('...'::timestamptz, '...'::bigint)))
         Buffers: shared hit=5
 Planning:
   Buffers: shared hit=10
 Planning Time: 0.120 ms
 Execution Time: 0.040 ms

Timings: planning 0.120ms, execution 0.040ms, total 0.160ms.

Related to #122 (closed)

Edited Jun 15, 2026 by Dzmitry (Dima) Meshcharakou

feat(npm): package, version, tag datastore read queries (S11 Step 4a)

📦 What

✅ Acceptance coverage

🧪 Testing

♻️ Review feedback

Database Review Evidence

Queries

Merge request reports