feat(npm): unpublish datastore layer (npm hosted step 19, part 1/2) (!778) · Merge requests · GitLab.org / ops / Artifact Registry

Part 1 of 2 of the npm hosted plan — Step 19: single-version unpublish (both wire steps).

🗂️ Stacked MRs

Split into 2 stacked MRs to keep each within the review size limit (~600 ideal reviewable LoC). Each part targets the previous one (part 1 → main); review and merge bottom-up.

feat(npm): unpublish datastore layer (npm hoste... (!778 - merged) • Dzmitry (Dima) Meshcharakou • 19.2 👈
feat(npm): unpublish handler + tests (npm hoste... (!779 - merged) • Dzmitry (Dima) Meshcharakou • 19.2

📦 What this part adds

The datastore layer for the single-version unpublish endpoints:

NpmVersionUnpublishDeleter — the transaction envelope for step (a): counts the target version's npm_tags (the returned tagsRemoved), then soft-deletes the version, soft-deletes its npm_files, and deletes its npm_tags in one transaction, rolling back on any error. Verifies the {rev} handshake against LatestNpmVersionIDByPackage and identifies the unpublish target as the single active version omitted from the submitted packument (0 or >1 omitted → version_not_found). Also serves step (b)'s idempotent file soft-delete. Keeps database/sql out of the format layer per ADR 023, mirroring the Step 20 NpmPackageUnpublishDeleter.
npm_packages counter mutators — DecrementNpmPackageVersionsCount and DecrementNpmPackageTagsCount, backing the handler's post-commit versions_count -= 1 / tags_count -= N buffered decrements, with a GREATEST(col - n, 0) clamp and a negative-n guard.

Test coverage for this layer lands via the integration suite in part 2, which drives the deleter and the counter mutators end-to-end — mirroring the Step 20 split, where !699 (merged)'s datastore layer is exercised by the tests in parts 2/3.

Database Review Evidence

Queries

Note

Plans are from EXPLAIN (ANALYZE, BUFFERS) against an ephemeral PostgreSQL 17 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.activeNpmVersionPage.FirstPage`	Limit → Index Scan	`index_npm_versions_on_ns_id_pkg_id_created_at_id` (`..._p46_..._idx`)	1000 / 1000	0.28..90.81	0.138ms	25 / 0	1
`datastore.activeNpmVersionPage.KeysetPage`	Limit → Index Scan	`index_npm_versions_on_ns_id_pkg_id_created_at_id` (`..._p46_..._idx`)	1000 / 1000	0.28..118.05	0.145ms	25 / 0	1
`datastore.countNpmTagsForVersion`	Aggregate → Bitmap Heap Scan	`index_npm_tags_on_ns_id_version_id` (`..._p07_..._idx`)	1 / 1	70.56..70.57	0.058ms	52 / 0	1
`datastore.fileVersionByName`	Limit → Sort → Nested Loop	n/a (Seq Scan both sides)	1 / 1	7.27..7.27	0.024ms	4 / 0	1 (npm_files) + 1 (npm_versions)
`datastore.decrementNpmPackageColumn.VersionsCount`	Update → Seq Scan	n/a	1 / 1	0.00..1.75	1.264ms	28 / 0	1
`datastore.decrementNpmPackageColumn.TagsCount`	Update → Seq Scan	n/a	1 / 1	0.00..1.75	0.066ms	4 / 0	1

datastore.activeNpmVersionPage.FirstPage

Summary: Plan matches the method's intent: Limit drives an Index Scan on the partial keyset index index_npm_versions_on_ns_id_pkg_id_created_at_id (partition-local child ..._p46_..._idx), with the namespace_id literal pruning to one of 64 partitions. The first page passes no keyset bound, so the Index Cond is just (namespace_id, npm_package_id) and the LIMIT walks the first 1000 rows in (created_at, id) order straight off the index — no separate Sort. Actual rows match the estimate (1000 / 1000); execution 0.138ms at 5000 seeded versions. No anomalies.

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

Rendered SQL:

SELECT npm_versions.id AS "npm_versions.id",
     npm_versions.version AS "npm_versions.version",
     npm_versions.created_at AS "npm_versions.created_at"
FROM public.npm_versions
WHERE ((npm_versions.namespace_id = $1::uuid) AND (npm_versions.npm_package_id = $2::uuid)) 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>, <npm-package-uuid>, 1000]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.28..90.81 rows=1000 width=32) (actual time=0.007..0.114 rows=1000 loops=1)
   Buffers: shared hit=25
   ->  Index Scan using npm_versions_p46_namespace_id_npm_package_id_created_at_id_idx on npm_versions_p46 npm_versions  (cost=0.28..452.91 rows=5000 width=32) (actual time=0.007..0.079 rows=1000 loops=1)
         Index Cond: ((namespace_id = '32e03e5d-0d8b-4e81-9546-543b170c3942'::uuid) AND (npm_package_id = '137115da-75c3-42ac-9d49-371c9d17e200'::uuid))
         Buffers: shared hit=25
 Planning:
   Buffers: shared hit=15
 Planning Time: 0.120 ms
 Execution Time: 0.138 ms

Timings: planning 0.120ms, execution 0.138ms, total 0.258ms.

datastore.activeNpmVersionPage.KeysetPage

Summary: Same partial keyset index and single-partition pruning as the first page; the (created_at, id) > (afterCreatedAt, afterID) row-value bound is pushed into the Index Cond, so PostgreSQL seeks straight to the cursor position and reads the next 1000 rows with no Filter step and no re-sort. 1000 / 1000; execution 0.145ms. The row-value (ROW(...) > ROW(...)) form is what keeps this an index-range scan rather than an O(offset) Filter, and the seed created monotonic created_at values so the mid-range cursor exercises the seek. No anomalies.

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

Rendered SQL:

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

Bound args: [<namespace-uuid>, <npm-package-uuid>, <mid-range-created-at>, <mid-range-version-uuid>, 1000]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.28..118.05 rows=1000 width=32) (actual time=0.006..0.115 rows=1000 loops=1)
   Buffers: shared hit=25
   ->  Index Scan using npm_versions_p46_namespace_id_npm_package_id_created_at_id_idx on npm_versions_p46 npm_versions  (cost=0.28..294.57 rows=2499 width=32) (actual time=0.005..0.080 rows=1000 loops=1)
         Index Cond: ((namespace_id = '32e03e5d-0d8b-4e81-9546-543b170c3942'::uuid) AND (npm_package_id = '137115da-75c3-42ac-9d49-371c9d17e200'::uuid) AND (ROW(created_at, id) > ROW('2026-07-02 12:14:06.785857+00'::timestamp with time zone, '7ee03b16-0a73-4677-978b-e5efa2eb01d7'::uuid)))
         Buffers: shared hit=25
 Planning:
   Buffers: shared hit=10
 Planning Time: 0.077 ms
 Execution Time: 0.145 ms

Timings: planning 0.077ms, execution 0.145ms, total 0.222ms.

datastore.countNpmTagsForVersion

Summary: Plan matches the method's intent — a single-row COUNT of one version's dist-tags. Aggregate over a Bitmap Index Scan on index_npm_tags_on_ns_id_version_id (partition-local ..._p07_..._idx), with namespace_id pruning to one of 64 partitions; 50 tags counted for the target version out of 5000 seeded across 100 versions in the partition. The planner chose a Bitmap (not plain Index) scan only because the round-robin seed scattered each version's tags across the heap (50 exact heap blocks); a real package, whose tags for a version are written together, would use a plain Index Scan. Either way the (namespace_id, npm_version_id) index serves the predicate. Execution 0.058ms. No anomalies.

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

Rendered SQL:

SELECT COUNT(npm_tags.id) AS "count"
FROM public.npm_tags
WHERE (npm_tags.namespace_id = $1::uuid) AND (npm_tags.npm_version_id = $2::uuid);

Bound args: [<namespace-uuid>, <npm-version-uuid>]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Aggregate  (cost=70.56..70.57 rows=1 width=8) (actual time=0.043..0.044 rows=1 loops=1)
   Buffers: shared hit=52
   ->  Bitmap Heap Scan on npm_tags_p07 npm_tags  (cost=4.79..70.44 rows=50 width=16) (actual time=0.013..0.040 rows=50 loops=1)
         Recheck Cond: ((namespace_id = '0ed97034-1c09-4105-a689-7098de1d2e42'::uuid) AND (npm_version_id = 'f131f2b6-584c-484c-a22f-24f3d2b6fecb'::uuid))
         Heap Blocks: exact=50
         Buffers: shared hit=52
         ->  Bitmap Index Scan on npm_tags_p07_namespace_id_npm_version_id_idx  (cost=0.00..4.78 rows=50 width=0) (actual time=0.008..0.008 rows=50 loops=1)
               Index Cond: ((namespace_id = '0ed97034-1c09-4105-a689-7098de1d2e42'::uuid) AND (npm_version_id = 'f131f2b6-584c-484c-a22f-24f3d2b6fecb'::uuid))
               Buffers: shared hit=2
 Planning:
   Buffers: shared hit=307
 Planning Time: 0.526 ms
 Execution Time: 0.058 ms

Timings: planning 0.526ms, execution 0.058ms, total 0.584ms.

datastore.fileVersionByName

Summary: The planner drives from a Seq Scan on npm_files filtered by (namespace_id, file_name) (99 of 100 partition-local rows removed by filter), then Nested Loop-joins to npm_versions (also Seq Scanned, matched on id and filtered on npm_package_id); namespace_id prunes both to one partition each, and the trailing LIMIT 1 adds a Sort on npm_files.id. It does not drive from the selective npm_versions.npm_package_id side. No index covers the (namespace_id, file_name) predicate: the only file_name index, unique_npm_files_ns_id_version_id_file_name, is partial (WHERE soft_deleted_at IS NULL) and leads with npm_version_id, and this method intentionally includes soft-deleted rows (step-(b) idempotency), so that index is unusable here. At 100 seeded files execution is 0.024ms, but the npm_files scan cost grows linearly with the namespace partition's file count. See Query notes.

Seed shape: namespaces=1, repositories=1, npm_repositories=1, npm_packages=1, npm_versions=100, blob_storage_blobs=100, blob_storage_attachments=100, npm_files=100

Rendered SQL:

SELECT npm_files.npm_version_id AS "npm_files.npm_version_id"
FROM public.npm_files
     INNER JOIN public.npm_versions ON ((npm_versions.namespace_id = npm_files.namespace_id) AND (npm_versions.id = npm_files.npm_version_id))
WHERE ((npm_files.namespace_id = $1::uuid) AND (npm_versions.npm_package_id = $2::uuid)) AND (npm_files.file_name = $3::text)
ORDER BY npm_files.id ASC
LIMIT $4;

Bound args: [<namespace-uuid>, <npm-package-uuid>, 'review-prep-pkg-1.0.3.tgz', 1]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=7.27..7.27 rows=1 width=32) (actual time=0.013..0.013 rows=1 loops=1)
   Buffers: shared hit=4
   ->  Sort  (cost=7.27..7.27 rows=1 width=32) (actual time=0.013..0.013 rows=1 loops=1)
         Sort Key: npm_files.id
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=4
         ->  Nested Loop  (cost=0.00..7.26 rows=1 width=32) (actual time=0.006..0.009 rows=1 loops=1)
               Buffers: shared hit=4
               ->  Seq Scan on npm_files_p53 npm_files  (cost=0.00..3.50 rows=1 width=48) (actual time=0.003..0.007 rows=1 loops=1)
                     Filter: ((namespace_id = '5728eb6d-f46b-4561-afe5-c9d0ca436f80'::uuid) AND (file_name = 'review-prep-pkg-1.0.3.tgz'::text))
                     Rows Removed by Filter: 99
                     Buffers: shared hit=2
               ->  Seq Scan on npm_versions_p53 npm_versions  (cost=0.00..3.75 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1)
                     Filter: ((namespace_id = '5728eb6d-f46b-4561-afe5-c9d0ca436f80'::uuid) AND (npm_package_id = '1ae5c1eb-972b-4074-a079-b7c4a24b8810'::uuid) AND (npm_files.npm_version_id = id))
                     Rows Removed by Filter: 2
                     Buffers: shared hit=2
 Planning:
   Buffers: shared hit=110
 Planning Time: 0.338 ms
 Execution Time: 0.024 ms

Timings: planning 0.338ms, execution 0.024ms, total 0.362ms.

datastore.decrementNpmPackageColumn.VersionsCount

Summary: Plan matches the method's intent: Update targeting a single namespace partition (npm_packages_p58) via Seq Scan with a (namespace_id, id) filter, 49 of 50 partition rows removed, 1 updated; GREATEST(versions_count - $1, $2) clamps the decrement at zero. Seq Scan (rather than pk_npm_packages) is the cost-optimal choice at 50 partition rows and mirrors the DecrementRepository* decrements in !699 (merged); at production cardinality the planner switches to the PK index. The 1.264ms execution is dominated by a one-time FK-check-trigger plan-cache warm-up (Trigger ... fk_npm_packages_npm_repository_id_npm_repositories: time=1.056, first invocation in the session) — the plan-identical .TagsCount variant that runs immediately after shows steady-state ~0.066ms. No plan anomaly. See Query notes for the timing caveat.

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

Rendered SQL:

UPDATE public.npm_packages
SET versions_count = GREATEST(npm_packages.versions_count - $1, $2)
WHERE (npm_packages.namespace_id = $3::uuid) AND (npm_packages.id = $4::uuid);

Bound args: [1, 0, <namespace-uuid>, <npm-package-uuid>]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Update on npm_packages  (cost=0.00..1.75 rows=0 width=0) (actual time=0.082..0.082 rows=0 loops=1)
   Update on npm_packages_p58 npm_packages_1
   Buffers: shared hit=28
   ->  Seq Scan on npm_packages_p58 npm_packages_1  (cost=0.00..1.75 rows=1 width=14) (actual time=0.003..0.004 rows=1 loops=1)
         Filter: ((namespace_id = '36c740d7-dcd9-459b-ba90-74aa38b4791c'::uuid) AND (id = '739d75ce-9ee4-4a2e-82fa-f5ccfc4f0684'::uuid))
         Rows Removed by Filter: 49
         Buffers: shared hit=1
 Planning:
   Buffers: shared hit=3
 Planning Time: 0.098 ms
 Trigger for constraint fk_npm_packages_namespace_id_namespaces on npm_packages_p58: time=0.015 calls=1
 Trigger for constraint fk_npm_packages_npm_repository_id_npm_repositories on npm_packages_p58: time=1.056 calls=1
 Execution Time: 1.264 ms

Timings: planning 0.098ms, execution 1.264ms, total 1.362ms.

datastore.decrementNpmPackageColumn.TagsCount

Summary: Identical plan shape to the versions_count variant — Update → Seq Scan on the pruned npm_packages_p58 partition with the (namespace_id, id) filter, GREATEST(tags_count - $1, $2) clamp — differing only in the target column. 1 row updated of 50 scanned; execution 0.066ms, the steady-state cost once the FK-check trigger is warm (the same trigger costs 0.024ms here vs 1.056ms on its first call in the versions_count run). No anomalies.

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

Rendered SQL:

UPDATE public.npm_packages
SET tags_count = GREATEST(npm_packages.tags_count - $1, $2)
WHERE (npm_packages.namespace_id = $3::uuid) AND (npm_packages.id = $4::uuid);

Bound args: [5, 0, <namespace-uuid>, <npm-package-uuid>]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Update on npm_packages  (cost=0.00..1.75 rows=0 width=0) (actual time=0.029..0.029 rows=0 loops=1)
   Update on npm_packages_p58 npm_packages_1
   Buffers: shared hit=4
   ->  Seq Scan on npm_packages_p58 npm_packages_1  (cost=0.00..1.75 rows=1 width=14) (actual time=0.013..0.013 rows=1 loops=1)
         Filter: ((namespace_id = '36c740d7-dcd9-459b-ba90-74aa38b4791c'::uuid) AND (id = '739d75ce-9ee4-4a2e-82fa-f5ccfc4f0684'::uuid))
         Rows Removed by Filter: 49
         Buffers: shared hit=1
 Planning:
   Buffers: shared hit=3
 Planning Time: 0.054 ms
 Trigger for constraint fk_npm_packages_namespace_id_namespaces on npm_packages_p58: time=0.005 calls=1
 Trigger for constraint fk_npm_packages_npm_repository_id_npm_repositories on npm_packages_p58: time=0.024 calls=1
 Execution Time: 0.066 ms

Timings: planning 0.054ms, execution 0.066ms, total 0.120ms.

Query notes:

datastore.fileVersionByName (concern, new access pattern flagged in review): the planner drives from a Seq Scan on npm_files filtered by (namespace_id, file_name) — not from the selective npm_versions.npm_package_id side — then nested-loop joins to npm_versions. No index covers the (namespace_id, file_name) predicate: the only file_name index (unique_npm_files_ns_id_version_id_file_name) is partial (WHERE soft_deleted_at IS NULL) and leads with npm_version_id, so it cannot serve a file_name-first lookup that must also see soft-deleted rows (step (b) resolves a possibly-already-soft-deleted file for idempotency). At 100 seeded files the namespace-partition-local scan is 0.024ms, but the scan cost grows linearly with the namespace partition's file count. Options: add a (namespace_id, file_name) non-partial index, or accept the scan on the basis that file_name is highly selective (1 match) and the lookup is a single pre-transaction read off the lock path. Flagged for reviewer awareness.
datastore.decrementNpmPackageColumn.VersionsCount (measurement caveat, not a plan issue): its 1.264ms execution includes a one-time FK-check-trigger plan-cache warm-up (fk_npm_packages_npm_repository_id_npm_repositories, time=1.056 on first invocation in the session). The plan-identical .TagsCount variant executed immediately after shows the same trigger at 0.024ms and total execution 0.066ms — the representative steady-state cost. Both variants are Seq Scan → Update on a single pruned partition, cost 0.00..1.75.

📚 References

Related to #137 (closed)

Edited Jul 02, 2026 by Dzmitry (Dima) Meshcharakou

feat(npm): unpublish datastore layer (npm hosted step 19, part 1/2)

🗂️ Stacked MRs

📦 What this part adds

Database Review Evidence

Queries

📚 References

Merge request reports