feat(npm): remote tarball cache reads (S15 Step 5, part 4/4)

David Fernandezrequested to merge
10io/s15-npm-remote-step-5-part4of4 into main

📚 Stacked MRs

S15 Step 5 — the read side of the npm-remote datastore layer — is split into 4 stacked MRs to keep each within the review size target (~600 reviewable LoC). Each part targets the previous one (part 1 targets main); review and merge proceed bottom-up. Together the four parts supersede !1050 (closed): the split initially reproduced its diff byte-for-byte, and each part then absorbed its own review revisions as it merged.

Stacked MRs (review/merge bottom-up)

📦 What this adds (part 4 of 4)

The tarball cache reads: NpmRemoteFileByVersionAndName returns the cached row with the same SQL-clock Fresh verdict as the metadata reads — the lookup narrows to the consumed columns and the freshness projection is the shared freshWithinHoursExpr from part 3 — plus the pin rule: a tarball is pinned fresh forever when cache_validity_hours = 0 (an immutable upstream such as npmjs.org), so a zero window is legitimate and only a negative one is rejected. Soft deletion is gated in the WHERE clause, unlike the metadata read: a cached tarball can be soft-deleted and re-cached, which is why the unique index is partial on soft_deleted_at IS NULL. The behavioral suite lives in its own file, mirroring the metadata suite's file-per-store split. Completes the stack.

🧪 Testing

Case Test
Constructor panics on a nil client TestNewNpmRemoteFileStore_NilClientPanics
Every argument guard rejects; zero window accepted as the pin, negative rejected TestNpmRemoteFileStore_NpmRemoteFileByVersionAndName_ArgumentGuards
Fresh vs. stale hits, with and without a stored etag; window edges pinned to a minute each side; a future stamp reads Fresh (one-sided window); sub-day windows render as hours; pinned forever at cache_validity_hours = 0 TestNpmRemoteFileStore_NpmRemoteFileByVersionAndName (freshness subtests)
Soft-deleted and missing rows resolve as ErrNotFound (both on the pinned window); the re-cached active row wins over a soft-deleted twin; several files cached under one version each resolve to their own blob by name; file-name, version and namespace predicates each isolated TestNpmRemoteFileStore_NpmRemoteFileByVersionAndName (gate and scoping subtests)
A transient DB failure is not misreported as ErrNotFound .../a transient DB failure is not misreported as ErrNotFound
Both freshness shapes (windowed and pinned) prune to 1 of 64 partitions TestNpmRemoteFileStore_NpmRemoteFileByVersionAndName_PrunesToOnePartition

🔗 References

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.

NpmRemoteFileByVersionAndName renders two statement shapes, because freshWithinHoursExpr changes the fresh projection with the window: .HoursPositive is the windowed form (a NOW()-relative interval comparison, here at cacheValidityHours = 24) and .HoursZero is the legitimate pin (the projection collapses to a bound TRUE). Both are EXPLAINed, so a plan regression that reached only one shape cannot hide.

Method Plan node Index Rows (plan / actual) Cost Time Buffers (hit / read) Partitions
datastore.NpmRemoteFileStore.NpmRemoteFileByVersionAndName.HoursPositive Limit → Index Scan npm_remote_files_p49_namespace_id_npm_remote_version_id_fil_idx 1 / 1 8.31 0.015ms 3 / 0 1 of 64
datastore.NpmRemoteFileStore.NpmRemoteFileByVersionAndName.HoursZero Limit → Index Scan npm_remote_files_p49_namespace_id_npm_remote_version_id_fil_idx 1 / 1 8.30 0.012ms 3 / 0 1 of 64
datastore.NpmRemoteFileStore.NpmRemoteFileByVersionAndName.HoursPositive

Summary: The plan matches the method's intent. The driving scan is an Index Scan over the partition-local child of unique_npm_remote_files_ns_id_ver_id_file_name — the partial unique index on (namespace_id, npm_remote_version_id, file_name) WHERE soft_deleted_at IS NULL — so the index is an exact match for the query: its Index Cond absorbs all three equality predicates and the index predicate absorbs the soft-delete gate, leaving the plan with no Filter, no Recheck and no Sort, and the LIMIT never discards a row. The fresh verdict is a projection over the row the index already located, so evaluating the NOW()-relative comparison against the database clock adds no scan work. Passing namespace_id prunes to exactly one of the table's 64 hash partitions (npm_remote_files_p49), the estimate matches reality exactly (1 / 1), and all 3 execution-time buffer accesses are cache hits with no reads, against 5000 seeded tarball rows in that one partition. No anomalies against the flagged criteria. Two properties worth recording rather than flagging: the validity window is inlined by the builder as an INTERVAL '1 DAY' literal instead of a bind parameter, so a repository configured with a different cache_validity_hours produces a distinct statement text and its own plan-cache entry — harmless at the handful of distinct window values a deployment uses, and it lets the planner see the constant; and planning (0.996ms) exceeds execution (0.027ms), which is what a 64-partition table over a tiny seeded dataset looks like — the 376 planning-time buffer hits are catalog reads for pruning, not query work — so the realistic per-request cost is the 1.023ms total, still well inside the latency budget.

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

Rendered SQL:

SELECT npm_remote_files.id AS "npm_remote_files.id",
     npm_remote_files.blob_storage_attachment_id AS "npm_remote_files.blob_storage_attachment_id",
     npm_remote_files.blob_sha256 AS "npm_remote_files.blob_sha256",
     npm_remote_files.upstream_etag AS "npm_remote_files.upstream_etag",
     (npm_remote_files.upstream_checked_at > (NOW() - INTERVAL '1 DAY')) AS "fresh"
FROM public.npm_remote_files
WHERE (((npm_remote_files.namespace_id = $1::uuid) AND (npm_remote_files.npm_remote_version_id = $2::uuid)) AND (npm_remote_files.file_name = $3::text)) AND (npm_remote_files.soft_deleted_at IS NULL)
LIMIT $4;

Bound args: ['00000000-0000-4000-8000-000000000001', '00000000-0000-4000-8000-000000000005', 'review-prep-file-002500.tgz', 1]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.28..8.31 rows=1 width=71) (actual time=0.015..0.015 rows=1 loops=1)
   Buffers: shared hit=3
   ->  Index Scan using npm_remote_files_p49_namespace_id_npm_remote_version_id_fil_idx on npm_remote_files_p49 npm_remote_files  (cost=0.28..8.31 rows=1 width=71) (actual time=0.014..0.014 rows=1 loops=1)
         Index Cond: ((namespace_id = '00000000-0000-4000-8000-000000000001'::uuid) AND (npm_remote_version_id = '00000000-0000-4000-8000-000000000005'::uuid) AND (file_name = 'review-prep-file-002500.tgz'::text))
         Buffers: shared hit=3
 Planning:
   Buffers: shared hit=376
 Planning Time: 0.996 ms
 Execution Time: 0.027 ms

Timings: planning 0.996ms, execution 0.027ms, total 1.023ms.

datastore.NpmRemoteFileStore.NpmRemoteFileByVersionAndName.HoursZero

Summary: The pinned form plans identically to the windowed one, which is the point of EXPLAINing it separately: collapsing the fresh projection to a bound TRUE ($1::boolean) costs the query nothing in access path — same Index Scan over the partition-local child of the partial unique index unique_npm_remote_files_ns_id_ver_id_file_name, same three equality predicates absorbed into the Index Cond with the soft-delete gate absorbed by the index predicate, same single-partition pruning to npm_remote_files_p49, same 71-byte row width, and the same 3 buffer hits with no reads. Estimate matches reality exactly (1 / 1) at 5000 seeded tarball rows in the partition, and the constant projection never appears in the plan body because it is not a scan input. No anomalies against the flagged criteria. As with the windowed shape, planning (0.537ms) exceeds execution (0.021ms) on a dataset this small over a 64-partition table, so the per-request figure to read is the 0.558ms total; the interval literal that gives the windowed form its own plan-cache entry is absent here, so every pinned repository shares one statement text.

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

Rendered SQL:

SELECT npm_remote_files.id AS "npm_remote_files.id",
     npm_remote_files.blob_storage_attachment_id AS "npm_remote_files.blob_storage_attachment_id",
     npm_remote_files.blob_sha256 AS "npm_remote_files.blob_sha256",
     npm_remote_files.upstream_etag AS "npm_remote_files.upstream_etag",
     $1::boolean AS "fresh"
FROM public.npm_remote_files
WHERE (((npm_remote_files.namespace_id = $2::uuid) AND (npm_remote_files.npm_remote_version_id = $3::uuid)) AND (npm_remote_files.file_name = $4::text)) AND (npm_remote_files.soft_deleted_at IS NULL)
LIMIT $5;

Bound args: [true, '00000000-0000-4000-8000-000000000001', '00000000-0000-4000-8000-000000000005', 'review-prep-file-002500.tgz', 1]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.28..8.30 rows=1 width=71) (actual time=0.011..0.012 rows=1 loops=1)
   Buffers: shared hit=3
   ->  Index Scan using npm_remote_files_p49_namespace_id_npm_remote_version_id_fil_idx on npm_remote_files_p49 npm_remote_files  (cost=0.28..8.30 rows=1 width=71) (actual time=0.011..0.011 rows=1 loops=1)
         Index Cond: ((namespace_id = '00000000-0000-4000-8000-000000000001'::uuid) AND (npm_remote_version_id = '00000000-0000-4000-8000-000000000005'::uuid) AND (file_name = 'review-prep-file-002500.tgz'::text))
         Buffers: shared hit=3
 Planning:
   Buffers: shared hit=174
 Planning Time: 0.537 ms
 Execution Time: 0.021 ms

Timings: planning 0.537ms, execution 0.021ms, total 0.558ms.

Related to #342 (closed)

Edited by David Fernandez

Merge request reports