feat(npm): maintain npm_versions.size_bytes and serialize version size

Dzmitry (Dima) Meshcharakourequested to merge
dmeshcharakou/npm-artifact-accounting-step-2 into main

🎯 What this MR does

Makes npm_versions.size_bytes live: written when a version is published, recomputable from source data, and visible through the management API. Step 2 of the three-MR stack for #549 (closed).

📚 The stack:

Step MR What it delivers
1 !1401 (merged) npm_versions.size_bytes column + index + backfill (merged)
2 · this MR 👉 Size written on publish, reconciliation recompute, size in the API, version-lookup index
3 !1403 (merged) versions_count / tags_count semantics fixes on unpublish

!1401 (merged) has merged, so this MR targets main. !1403 (merged) targets this branch and needs a rebase — this branch's history was rewritten.

🧩 The four pieces

1️⃣ Size written on publish (hosted)

InsertNpmVersion takes the tarball's byte count as SizeBytes and writes it in the version INSERT, inside the commit transaction that also inserts the version's single npm_files row. That one file is the version's whole ADR-007 size by construction — republish is a 409 — so no within-version deduplication is needed at the call site.

This replaces an earlier shape that emitted the size through bufferedUpdate after commit. Review found the flaw: bufferedUpdate sheds at 64 in flight and dies with the pod, and a dropped emit left size_bytes at 0 permanently — which the API contract reports as a genuinely empty version, not as a missing value. Nothing would have repaired it, because the recompute below has no production caller. Writing it in the transaction removes the drift class rather than narrowing it.

It also removes a write. The old shape inserted size_bytes as 0 and then updated it, and because the column is indexed by index_npm_versions_on_ns_id_pkg_id_size_bytes that update could never be HOT: it wrote a new tuple into all six of the table's indexes.

AddNpmVersionSizeBytes is gone with it — store method, committer adapter, seam method, metric label and tests — because that emit was its only caller. It returns with the lifecycle purger, which needs the hard-delete decrement it was written for.

2️⃣ Reconciliation recompute

SumDistinctNpmFileSizesByVersion is ADR-007's artifact-level recompute: DISTINCT blob_sha256 first, then the size join (equal-length blobs must not collapse), soft-deleted files included (bytes leave at hard-delete only).

It has no production caller yet, and that is deliberate rather than an oversight. The publish path writes the column in the same transaction as the file row, so the two cannot diverge there. This read exists for the mutations that lack that property — the hard-delete decrement the lifecycle purger will apply, and any future attach site that is not a single-file insert. The integration suite pins it against the value InsertNpmVersion writes for a publish-shaped seed, and asserts both equal the tarball's byte count, so an agreeing pair of zeros cannot pass.

3️⃣ API surface

The Version resource gains a required, nullable size key (OpenAPI + serializer):

  • npm → npm_versions.size_bytes, zero included, so an empty version is distinguishable from an unmaintained one;
  • Maven → null until maven_versions.size_bytes lands (its own per-format issue, same family as #549 (closed)).

No Bruno change: docs/dev/bruno.md requires collection updates for operation additions/renames/removals; this adds a response field to existing operations.

4️⃣ Version-lookup index (npm_files)

SumDistinctNpmFileSizesByVersion reads on (namespace_id, npm_version_id) and had no index to use. Measured against a 5000-row partition, the planner chose a Seq Scan and discarded 4995 rows through the filter.

The one existing index leading with that key tuple, unique_npm_files_ns_id_version_id_file_name, is partial on soft_deleted_at IS NULL. The recompute omits that filter deliberately — a version's bytes leave only at hard-delete — and Postgres may use a partial index only for a query whose predicate implies the index predicate, so that index cannot serve this read however the keys are ordered. Hence a new non-partial index_npm_files_on_ns_id_ver_id, mirroring index_npm_remote_files_on_ns_id_ver_id, the same key tuple the remote sibling table already carried and npm_files alone was missing.

The evidence section below carries the plan after the index: the inner DISTINCT is an index scan, and the query's root buffer count drops from 138 to 25 on the same seed.

🗑️ What this MR no longer does

The remote cache-fill delta path is reverted in full — NpmRemoteFileFillResult, fillSizeBytesDelta, siblingHoldsDigest, blobSizeForDigest, and the UpsertNpmRemoteFileForBlob signature change. Those four files are back to main exactly.

It computed a size delta that nothing emitted. The npm-remote read path that would have consumed it is not written, and there is no merge request or issue tracking it, so no test could exercise the path end to end and nothing guaranteed the emit would ever be wired. The failure mode if it never was is npm_remote_versions.size_bytes staying 0 — the symptom #549 (closed) was filed about.

Item 3 of #549 (closed) ("wire npm_remote_versions.size_bytes on the cache-fill path, or remove the column and its index") therefore stays open, and is no longer partly-done-but-dead.

✅ Verification

  • Unit + integration suites for both remaining pieces: the recompute suite covers dedup, soft-deleted inclusion, the file-less version, version scoping, and agreement with the column InsertNpmVersion writes; the publish integration test asserts npm_versions.size_bytes == tarball bytes after a real publish, now satisfied by the commit transaction rather than by a post-commit emit.
  • golangci-lint run --build-tags=integration --max-same-issues=0 --max-issues-per-linter=0, compared before/after across datastore, format/npm and managementapi: 2944 → 2926 findings, the whole difference being deleted code. No new findings in touched files.
  • Redocly lint: both API docs valid.
  • ⚠️ npm conformance needs re-running. The earlier pass was against the previous shape of the publish path. This MR moved the size write into the commit transaction, so that result no longer covers the code as it stands.

🧪 E2E scenario impact

None: docs/testing/e2e/ catalogs cover container flows only (oci.md, docker.md); no npm catalog exists for this to extend.

Process note 🔍: this stack ships without a prior plan MR by explicit operator decision — the plan-level context lives in these MR descriptions (see !1401 (merged) for the full background).

Database Review Evidence

Migrations

Note

Timings are from CI (db:migrate matrix, goose verbose) against an empty database, in apply / rollback order per PG version. Production-scale validation via Database Lab is not yet available. See Database review evidence for the matrix rationale and how to read the numbers.

Migration PG 16 PG 17 PG 18
20260812120000_add_npm_files_version_index.sql OK (133.95ms / 25.61ms) OK (11.64ms / 10.7ms) OK (50.83ms / 24.62ms)

Migration notes:

  • add_npm_files_version_index.sql: PG 16 up (133.95ms) is 2.64x the second-slowest version (PG 18, 50.83ms), which trips the version-regression threshold. It is not a regression this migration introduces. PG 16 is slower than PG 17 on every migration in this run — the median PG 16 / PG 17 up ratio across all 67 is 3.87x, so this migration sits below the run's median. The three other parent-level CREATE INDEX migrations over the 64-partition tables, all already on main, land in the same band: add_ns_enc_keys_tombstone_index 127.2ms (2.43x), add_npm_versions_size_bytes_index 143.7ms (2.38x), add_npm_files_name_index 152.8ms (2.53x). At 134ms on an empty database it is three orders of magnitude inside the 5-minute boot budget.

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
NpmFileStore.SumDistinctNpmFileSizesByVersion Aggregate npm_files_pNN_namespace_id_npm_version_id_idx (inner), blob_storage_blobs_pNN_namespace_id_sha256_idx (join) 1 / 1 2612.74 22.923ms 25 / 0 npm_files 1 of 64; blob_storage_blobs 5 of 64 executed
NpmVersionStore.InsertNpmVersion Insert unique_npm_versions_ns_id_pkg_id_version (conflict arbiter) 1 / 1 0.01 2.767ms 131 / 8 1 of 64
NpmVersionStore.FindNpmVersionByID Limit npm_versions_pNN_pkey 1 / 1 8.30 0.049ms 3 / 0 1 of 64
NpmVersionStore.ListNpmVersionsByPackage.CreatedAtDescFirstPage Limit npm_versions_pNN_namespace_id_npm_package_id_created_at_id_idx 21 / 21 2.85 0.089ms 5 / 0 1 of 64

No query anomalies: every statement reaches an index path and prunes to a single partition, except the recompute's join, which runtime-prunes to one partition per distinct digest.

NpmFileStore.SumDistinctNpmFileSizesByVersion

Summary: Plan matches the method's intent. The inner DISTINCT is an index scan over index_npm_files_on_ns_id_ver_id, the non-partial index this MR adds; each surviving digest then joins as a single-partition index probe on blob_storage_blobs, runtime-pruned to 5 of 64 partitions for the 5 seeded digests. Without the index the inner side was a Seq Scan discarding 4995 of 5000 rows at 138 root buffers; it is 25 here. No anomalies.

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

Rendered SQL:

SELECT COALESCE(SUM(blob_storage_blobs.size), $1) AS "total"
FROM (
          SELECT DISTINCT npm_files.blob_sha256 AS "npm_files.blob_sha256"
          FROM public.npm_files
          WHERE (npm_files.namespace_id = $2::uuid) AND (npm_files.npm_version_id = $3::uuid)
     ) AS uniq_blobs
     INNER JOIN public.blob_storage_blobs ON ((blob_storage_blobs.namespace_id = $4::uuid) AND (blob_storage_blobs.sha256 = uniq_blobs."npm_files.blob_sha256"));

Bound args: [0, <ns_id>, <version_id>, <ns_id>]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Aggregate  (cost=2612.73..2612.74 rows=1 width=32) (actual time=16.542..21.379 rows=1 loops=1)
  Buffers: shared hit=25
  ->  Nested Loop  (cost=1015.68..2612.72 rows=5 width=8) (actual time=16.228..21.297 rows=5 loops=1)
        Buffers: shared hit=25
        ->  Unique  (cost=1015.40..1015.76 rows=3 width=33) (actual time=15.961..20.801 rows=5 loops=1)
              Buffers: shared hit=10
              ->  Gather Merge  (cost=1015.40..1015.75 rows=3 width=33) (actual time=15.956..20.790 rows=5 loops=1)
                    Workers Planned: 1
                    Workers Launched: 1
                    Buffers: shared hit=10
                    ->  Unique  (cost=15.39..15.40 rows=3 width=33) (actual time=0.094..0.102 rows=2 loops=2)
                          Buffers: shared hit=10
                          ->  Sort  (cost=15.39..15.39 rows=3 width=33) (actual time=0.092..0.096 rows=2 loops=2)
                                Sort Key: npm_files.blob_sha256
                                Sort Method: quicksort  Memory: 25kB
                                Buffers: shared hit=10
                                Worker 0:  Sort Method: quicksort  Memory: 25kB
                                ->  Parallel Index Scan using npm_files_p26_namespace_id_npm_version_id_idx on npm_files_p26 npm_files  (cost=0.28..15.36 rows=3 width=33) (actual time=0.014..0.016 rows=2 loops=2)
                                      Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (npm_version_id = '2f7783d3-0201-4bf2-9c93-3698faf38a8f'::uuid))
                                      Buffers: shared hit=3
        ->  Append  (cost=0.28..531.68 rows=64 width=41) (actual time=0.084..0.087 rows=1 loops=5)
              Buffers: shared hit=15
              ->  Index Scan using blob_storage_blobs_p00_namespace_id_sha256_idx on blob_storage_blobs_p00 blob_storage_blobs_1  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p01_namespace_id_sha256_idx on blob_storage_blobs_p01 blob_storage_blobs_2  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p02_namespace_id_sha256_idx on blob_storage_blobs_p02 blob_storage_blobs_3  (cost=0.28..8.30 rows=1 width=41) (actual time=0.049..0.050 rows=1 loops=1)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
                    Buffers: shared hit=3
              ->  Index Scan using blob_storage_blobs_p03_namespace_id_sha256_idx on blob_storage_blobs_p03 blob_storage_blobs_4  (cost=0.28..8.30 rows=1 width=41) (actual time=0.218..0.218 rows=1 loops=1)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
                    Buffers: shared hit=3
              ->  Index Scan using blob_storage_blobs_p04_namespace_id_sha256_idx on blob_storage_blobs_p04 blob_storage_blobs_5  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p05_namespace_id_sha256_idx on blob_storage_blobs_p05 blob_storage_blobs_6  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p06_namespace_id_sha256_idx on blob_storage_blobs_p06 blob_storage_blobs_7  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p07_namespace_id_sha256_idx on blob_storage_blobs_p07 blob_storage_blobs_8  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p08_namespace_id_sha256_idx on blob_storage_blobs_p08 blob_storage_blobs_9  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p09_namespace_id_sha256_idx on blob_storage_blobs_p09 blob_storage_blobs_10  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p10_namespace_id_sha256_idx on blob_storage_blobs_p10 blob_storage_blobs_11  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p11_namespace_id_sha256_idx on blob_storage_blobs_p11 blob_storage_blobs_12  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p12_namespace_id_sha256_idx on blob_storage_blobs_p12 blob_storage_blobs_13  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p13_namespace_id_sha256_idx on blob_storage_blobs_p13 blob_storage_blobs_14  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p14_namespace_id_sha256_idx on blob_storage_blobs_p14 blob_storage_blobs_15  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p15_namespace_id_sha256_idx on blob_storage_blobs_p15 blob_storage_blobs_16  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p16_namespace_id_sha256_idx on blob_storage_blobs_p16 blob_storage_blobs_17  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p17_namespace_id_sha256_idx on blob_storage_blobs_p17 blob_storage_blobs_18  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p18_namespace_id_sha256_idx on blob_storage_blobs_p18 blob_storage_blobs_19  (cost=0.28..8.30 rows=1 width=41) (actual time=0.042..0.043 rows=1 loops=1)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
                    Buffers: shared hit=3
              ->  Index Scan using blob_storage_blobs_p19_namespace_id_sha256_idx on blob_storage_blobs_p19 blob_storage_blobs_20  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p20_namespace_id_sha256_idx on blob_storage_blobs_p20 blob_storage_blobs_21  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p21_namespace_id_sha256_idx on blob_storage_blobs_p21 blob_storage_blobs_22  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p22_namespace_id_sha256_idx on blob_storage_blobs_p22 blob_storage_blobs_23  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p23_namespace_id_sha256_idx on blob_storage_blobs_p23 blob_storage_blobs_24  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p24_namespace_id_sha256_idx on blob_storage_blobs_p24 blob_storage_blobs_25  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p25_namespace_id_sha256_idx on blob_storage_blobs_p25 blob_storage_blobs_26  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p26_namespace_id_sha256_idx on blob_storage_blobs_p26 blob_storage_blobs_27  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p27_namespace_id_sha256_idx on blob_storage_blobs_p27 blob_storage_blobs_28  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p28_namespace_id_sha256_idx on blob_storage_blobs_p28 blob_storage_blobs_29  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p29_namespace_id_sha256_idx on blob_storage_blobs_p29 blob_storage_blobs_30  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p30_namespace_id_sha256_idx on blob_storage_blobs_p30 blob_storage_blobs_31  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p31_namespace_id_sha256_idx on blob_storage_blobs_p31 blob_storage_blobs_32  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p32_namespace_id_sha256_idx on blob_storage_blobs_p32 blob_storage_blobs_33  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p33_namespace_id_sha256_idx on blob_storage_blobs_p33 blob_storage_blobs_34  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p34_namespace_id_sha256_idx on blob_storage_blobs_p34 blob_storage_blobs_35  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p35_namespace_id_sha256_idx on blob_storage_blobs_p35 blob_storage_blobs_36  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p36_namespace_id_sha256_idx on blob_storage_blobs_p36 blob_storage_blobs_37  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p37_namespace_id_sha256_idx on blob_storage_blobs_p37 blob_storage_blobs_38  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p38_namespace_id_sha256_idx on blob_storage_blobs_p38 blob_storage_blobs_39  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p39_namespace_id_sha256_idx on blob_storage_blobs_p39 blob_storage_blobs_40  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p40_namespace_id_sha256_idx on blob_storage_blobs_p40 blob_storage_blobs_41  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p41_namespace_id_sha256_idx on blob_storage_blobs_p41 blob_storage_blobs_42  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p42_namespace_id_sha256_idx on blob_storage_blobs_p42 blob_storage_blobs_43  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p43_namespace_id_sha256_idx on blob_storage_blobs_p43 blob_storage_blobs_44  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p44_namespace_id_sha256_idx on blob_storage_blobs_p44 blob_storage_blobs_45  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p45_namespace_id_sha256_idx on blob_storage_blobs_p45 blob_storage_blobs_46  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p46_namespace_id_sha256_idx on blob_storage_blobs_p46 blob_storage_blobs_47  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p47_namespace_id_sha256_idx on blob_storage_blobs_p47 blob_storage_blobs_48  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p48_namespace_id_sha256_idx on blob_storage_blobs_p48 blob_storage_blobs_49  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p49_namespace_id_sha256_idx on blob_storage_blobs_p49 blob_storage_blobs_50  (cost=0.28..8.30 rows=1 width=41) (actual time=0.048..0.048 rows=1 loops=1)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
                    Buffers: shared hit=3
              ->  Index Scan using blob_storage_blobs_p50_namespace_id_sha256_idx on blob_storage_blobs_p50 blob_storage_blobs_51  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p51_namespace_id_sha256_idx on blob_storage_blobs_p51 blob_storage_blobs_52  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p52_namespace_id_sha256_idx on blob_storage_blobs_p52 blob_storage_blobs_53  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p53_namespace_id_sha256_idx on blob_storage_blobs_p53 blob_storage_blobs_54  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p54_namespace_id_sha256_idx on blob_storage_blobs_p54 blob_storage_blobs_55  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p55_namespace_id_sha256_idx on blob_storage_blobs_p55 blob_storage_blobs_56  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p56_namespace_id_sha256_idx on blob_storage_blobs_p56 blob_storage_blobs_57  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p57_namespace_id_sha256_idx on blob_storage_blobs_p57 blob_storage_blobs_58  (cost=0.28..8.30 rows=1 width=41) (actual time=0.036..0.036 rows=1 loops=1)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
                    Buffers: shared hit=3
              ->  Index Scan using blob_storage_blobs_p58_namespace_id_sha256_idx on blob_storage_blobs_p58 blob_storage_blobs_59  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p59_namespace_id_sha256_idx on blob_storage_blobs_p59 blob_storage_blobs_60  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p60_namespace_id_sha256_idx on blob_storage_blobs_p60 blob_storage_blobs_61  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p61_namespace_id_sha256_idx on blob_storage_blobs_p61 blob_storage_blobs_62  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p62_namespace_id_sha256_idx on blob_storage_blobs_p62 blob_storage_blobs_63  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
              ->  Index Scan using blob_storage_blobs_p63_namespace_id_sha256_idx on blob_storage_blobs_p63 blob_storage_blobs_64  (cost=0.28..8.30 rows=1 width=41) (never executed)
                    Index Cond: ((namespace_id = '109f87bf-e060-4679-904f-8be4890c21b3'::uuid) AND (sha256 = npm_files.blob_sha256))
Planning:
  Buffers: shared hit=3750 read=139
Planning Time: 50.519 ms
Execution Time: 22.923 ms
NpmVersionStore.InsertNpmVersion

Summary: The statement this MR changes: size_bytes joins the column list, so the version's size is written by the insert instead of a post-commit UPDATE. The arbiter is still the partial unique index, and the added column does not change conflict handling — Tuples Inserted: 1, Conflicting Tuples: 0. Carrying the value here also removes a write: the previous shape inserted size_bytes as 0 and then updated it, and because size_bytes is indexed by index_npm_versions_on_ns_id_pkg_id_size_bytes that update could never be HOT, so it wrote a new tuple into all six of the table's indexes. The two FK triggers in the footer are the seed's doing: it inserts the parent rows in the same transaction. No anomalies.

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

Rendered SQL:

INSERT INTO public.npm_versions (id, namespace_id, npm_package_id, version, size_bytes, package_json)
VALUES ($1::uuid, $2::uuid, $3::uuid, $4::text, $5, $6::text::jsonb)
ON CONFLICT (namespace_id, npm_package_id, version) WHERE soft_deleted_at IS NULL DO NOTHING
RETURNING npm_versions.id AS "npm_versions.id";

Bound args: [<new_uuid>, <ns_id>, <pkg_id>, '9.9.9', 12345, '{"name":"lodash","version":"9.9.9"}']

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Insert on npm_versions  (cost=0.00..0.01 rows=1 width=240) (actual time=1.523..1.525 rows=1 loops=1)
  Conflict Resolution: NOTHING
  Conflict Arbiter Indexes: unique_npm_versions_ns_id_pkg_id_version
  Tuples Inserted: 1
  Conflicting Tuples: 0
  Buffers: shared hit=131 read=8
  ->  Result  (cost=0.00..0.01 rows=1 width=240) (actual time=0.003..0.003 rows=1 loops=1)
Planning:
  Buffers: shared hit=4 read=2
Planning Time: 0.310 ms
Trigger for constraint fk_npm_versions_namespace_id_namespaces on npm_versions_p08: time=0.286 calls=1
Trigger for constraint fk_npm_versions_npm_package_id_npm_packages on npm_versions_p08: time=0.899 calls=1
Execution Time: 2.767 ms
NpmVersionStore.FindNpmVersionByID

Summary: Unchanged shape: a primary-key seek, still 3 buffers with size_bytes added to the projection. The column is a heap column on a plan that was never index-only, so it costs nothing. No anomalies.

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

Rendered SQL:

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

Bound args: [<ns_id>, <version_id>, 1]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..8.30 rows=1 width=184) (actual time=0.022..0.023 rows=1 loops=1)
  Buffers: shared hit=3
  ->  Index Scan using npm_versions_p51_pkey on npm_versions_p51 npm_versions  (cost=0.28..8.30 rows=1 width=184) (actual time=0.021..0.022 rows=1 loops=1)
        Index Cond: ((id = 'ae9db984-efb7-49b5-9816-e200248a8526'::uuid) AND (namespace_id = '57f0929f-f89c-4562-9774-cc5898fc3238'::uuid))
        Filter: (soft_deleted_at IS NULL)
        Buffers: shared hit=3
Planning:
  Buffers: shared hit=109
Planning Time: 1.190 ms
Execution Time: 0.049 ms
NpmVersionStore.ListNpmVersionsByPackage.CreatedAtDescFirstPage

Summary: Backward index scan satisfies ORDER BY created_at DESC, id DESC with no Sort node, and the Limit stops it after 21 of the partition's 5000 rows. Adding size_bytes to the projection did not change the plan: this scan was never index-only. Only the created_at-descending first page is shown; sort column, direction and cursor presence select 8 branches in total, and the projection change is orthogonal to all of them. No anomalies.

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

Rendered SQL:

SELECT npm_versions.namespace_id AS "npm_versions.namespace_id",
     npm_versions.id AS "npm_versions.id",
     npm_versions.npm_package_id AS "npm_versions.npm_package_id",
     npm_versions.last_downloaded_at AS "npm_versions.last_downloaded_at",
     npm_versions.soft_deleted_at AS "npm_versions.soft_deleted_at",
     npm_versions.created_at AS "npm_versions.created_at",
     npm_versions.version AS "npm_versions.version",
     npm_versions.gitlab_user_id AS "npm_versions.gitlab_user_id",
     npm_versions.gitlab_project_id AS "npm_versions.gitlab_project_id",
     npm_versions.gitlab_git_commit_sha AS "npm_versions.gitlab_git_commit_sha",
     npm_versions.size_bytes AS "npm_versions.size_bytes"
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 DESC, npm_versions.id DESC
LIMIT $3;

Bound args: [<ns_id>, <pkg_id>, 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..2.85 rows=21 width=184) (actual time=0.035..0.049 rows=21 loops=1)
  Buffers: shared hit=5
  ->  Index Scan Backward using npm_versions_p15_namespace_id_npm_package_id_created_at_id_idx on npm_versions_p15 npm_versions  (cost=0.28..612.47 rows=5000 width=184) (actual time=0.033..0.045 rows=21 loops=1)
        Index Cond: ((namespace_id = 'dfa9d6c1-490c-42b7-9486-bf0cc5785df5'::uuid) AND (npm_package_id = '53a65e67-b2b7-46bc-aa1c-9509585fbc16'::uuid))
        Buffers: shared hit=5
Planning:
  Buffers: shared hit=133
Planning Time: 1.721 ms
Execution Time: 0.089 ms

Related to #549 (closed)

Edited by Dzmitry (Dima) Meshcharakou

Merge request reports