feat(datastore): npm remote repository health seams

Dzmitry (Dima) Meshcharakourequested to merge
dmeshcharakou/s15-step-9-mr1-npm-datastore-health into main

🎯 What this MR does

Gives NpmRemoteRepositoryStore the durable health read/write pair and the fleet-wide enumeration the scheduled upstream-health sweep needs, mirroring the merged Maven store's implementation. Step 1 of the 5-MR stack splitting an oversized branch — see the table below.

📚 The stack:

Step MR What it delivers
0 !1479 (merged) (merged) Shared refsWithUsableURL enumeration helper
1 · this MR 👉 !1481 (merged) npm datastore health seams (this MR)
2a !1482 (merged) npmremote.Target + UpstreamRequestBuilder
2b !1483 (merged) npmremote.CacheStore + health-sweep glue
3 !1484 (merged) Composition root: wiring, audit sink, breadcrumb

🧩 The two pieces

1️⃣ Health read/write

HealthStatus and SetHealthStatus satisfy remote.HealthStatusReader and remote.HealthStatusWriter. The write is a compare-and-set inside one transaction — SELECT ... FOR UPDATE, the recency decision under that lock, then the UPDATE — so a stale probe can never apply over a newer one, and previous is always the value actually replaced. The incoming timestamp is truncated to microsecond first, or a probe older than the stored one but newer than its rounded form would pass the guard.

2️⃣ Fleet-wide enumeration

ListRemotes returns every live npm-format (kind=2) parent across every namespace, capped one row past defaultMaxListNpmRemotes so exceeding it fails loudly rather than handing the sweep a truncated page it would read as the whole fleet. A row whose URL remote.ParseUpstreamBase refuses is dropped by !1479 (merged)'s shared refsWithUsableURL gate rather than handed to the prober to fail on less legibly later.

defaultMaxListNpmRemotes is 100_000, not container's rejected 10_000: that value was rejected there because five namespaces at managementapi's per-(namespace, format) repository quota of 1000, across container's two formats, hit it exactly — reachable by ordinary quota'd growth, not just a bug. npm is single-format, so the same 10_000 takes ten quota'd namespaces to reach — twice container's five, but still ordinary quota'd growth rather than a bug, so the rejection carries over. 100_000 takes a hundred namespaces fully at quota to reach — the same order of implausibility container's own 100_000 was sized to.

A prerequisite fixture fix ships first: npmRemoteRepoOpts.url becomes a *string so a test scenario can seed a genuinely empty URL (a reachable production state — the column is NOT NULL with no non-empty CHECK) to exercise the refusal path in ListRemotes' suite.

🔁 What changed since the rebase onto main

!1479 (merged) merged in a different shape than this branch carried: review there replaced the shared gate's plain-string table prefix with the remoteTablePrefix integer enum, so a caller cannot pass log text of its own. Three follow-on commits adapt to it and close gaps that adaptation exposed.

  • feat commit (amended). npm declares npmRemoteTablePrefix and its skipSummaryMessage arm instead of passing the "npm_remote" literal, which no longer compiles. The shared file's doc comments said container was the only caller and spoke about a second one in the future tense; npm is that second caller now, so they are stated in the present.
  • fix commit. ListRemotes' nil-context guard moves from the unexported listRemotes body onto the public method, matching ContainerRemoteRepositoryStore.ListRemotes. Behavior was already correct — the point is that the contract is now stated at the remote.RemoteRepositoryEnumerator boundary a caller holds, so splitting or inlining the body later cannot drop it silently.
  • test commit. Four claims the container ListRemotes suite makes were missing here, each with a predicate that can be deleted with the npm suite still green: two remotes in one namespace both returned (listNpmRemotesStmt selects ID only to stop qrm collapsing them, and nothing reads it off the row, so it reads as removable); the refusal summary carrying this store's own prefix; a failed enumeration surfacing as an error rather than an empty fleet; and the plan spanning every partition of both tables, so a namespace arm added to the parent predicate later cannot quietly reduce a fleet-wide sweep to one namespace. listRemotesSkipSummary now takes the enumerator seam rather than the container store's concrete type, so both suites decode the summary through one helper.

✅ Verification

  • Full NpmRemoteRepositoryStore suite: 26/26 passing against a real Postgres, including the new HealthStatus/SetHealthStatus/ListRemotes coverage and the CAS-serializes-concurrent-probes property test.
  • TestContainerRemoteRepositoryStore*: passing, since the shared skip-summary helper's signature changed.
  • go build ./... (with and without -tags=integration), go vet: clean.
  • golangci-lint run --max-same-issues=0 --max-issues-per-linter=0 ./internal/datastore/...: 0 issues.
  • golangci-lint run --build-tags=integration --max-same-issues=0 --max-issues-per-linter=0 ./internal/datastore/...: the two new npm test files report contextcheck only (31 and 17), all of it the seedNamespace(t, client.DB(), ...) fixture-helper pattern that 57 other integration-test files in this package already use. Three of those are on lines this MR's test commit adds, and they are the same pattern as the 14 already in that file. The touched production files and the touched container test hunk are clean.

🧪 E2E scenario impact

None: these are datastore-layer seams with no HTTP-reachable caller yet. The npm packument/dist-tags/tarball handlers that consume health status land in later steps of the parent plan.

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.NpmRemoteRepositoryStore.HealthStatus Limit → Index Scan npm_remote_repositories_pNN_namespace_id_repository_id_idx 1 / 1 8.30 0.024ms 3 / 0 1 of 64
datastore.NpmRemoteRepositoryStore.ListRemotes Limit → Hash Join → Append n/a (Seq Scans; see notes) 5063 / 5000 465.17 6.365ms 187 / 0 64 + 64
datastore.NpmRemoteRepositoryStore.SetHealthStatus.CASSelect Limit → LockRows → Index Scan npm_remote_repositories_pNN_namespace_id_repository_id_idx 1 / 1 8.31 0.034ms 4 / 0 1 of 64
datastore.NpmRemoteRepositoryStore.SetHealthStatus.CASUpdate Update → Index Scan npm_remote_repositories_pNN_namespace_id_repository_id_idx 0 / 0 8.30 0.208ms 26 / 0 1 of 64

Query notes:

  • ListRemotes reads every partition of both tables by design: the sweep is system-wide, so there is no namespace to prune on, and TestNpmRemoteRepositoryStore_ListRemotes_PlanShape pins exactly that shape so a namespace arm added later cannot silently shrink the fleet. The result is capped at maxRemotes+1 (100_001 bound below), so it is not an unbounded SELECT.
  • At the fleet-only seed the repositories side is a Seq Scan because every seeded row matches the predicate; that is the planner being right, not an index gap. How the enumeration should be shaped at fleet scale is being discussed on this MR (the escalate: thread).
  • CASUpdate execution time (2.368ms) is dominated by the two FK-constraint trigger checks (~1.76ms), not the scan; the scan itself is the same one-partition index path as the reads.
datastore.NpmRemoteRepositoryStore.HealthStatus

Summary: Plan matches the method's intent: the two-column equality key prunes to one of 64 hash partitions and reads the row through the partition child of unique_npm_remote_repositories_namespace_id_and_repository_id. Actual rows match the estimate (1 / 1) and execution stays under 0.1ms at 5000 seeded remotes. No anomalies.

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

Rendered SQL:

SELECT npm_remote_repositories.last_health_status AS "npm_remote_repositories.last_health_status"
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: [91a00706-6edb-4d26-b6b1-ba0cfa78596b, e57a48b2-c7f1-41e0-9fcc-87cae6034e1e, 1]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.28..8.30 rows=1 width=2) (actual time=0.023..0.024 rows=1 loops=1)
   Buffers: shared hit=3
   ->  Index Scan using npm_remote_repositories_p12_namespace_id_repository_id_idx on npm_remote_repositories_p12 npm_remote_repositories  (cost=0.28..8.30 rows=1 width=2) (actual time=0.022..0.022 rows=1 loops=1)
         Index Cond: ((namespace_id = '91a00706-6edb-4d26-b6b1-ba0cfa78596b'::uuid) AND (repository_id = 'e57a48b2-c7f1-41e0-9fcc-87cae6034e1e'::uuid))
         Buffers: shared hit=3
 Planning:
   Buffers: shared hit=250
 Planning Time: 2.058 ms
 Execution Time: 0.053 ms

Timings: planning 2.058ms, execution 0.053ms, total 2.111ms.

datastore.NpmRemoteRepositoryStore.ListRemotes

Summary: Fleet-only seed: 5000 npm remotes and nothing else in repositories. The system-wide enumeration scans every partition of both tables (the shape TestNpmRemoteRepositoryStore_ListRemotes_PlanShape pins) and hash-joins them; Seq Scans are the planner being right here, since every row matches the predicate. Estimate matches actual (5063 / 5000).

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

Rendered SQL:

SELECT npm_remote_repositories.namespace_id AS "npm_remote_repositories.namespace_id",
npm_remote_repositories.id AS "npm_remote_repositories.id",
npm_remote_repositories.repository_id AS "npm_remote_repositories.repository_id",
npm_remote_repositories.url AS "npm_remote_repositories.url"
FROM public.npm_remote_repositories
INNER JOIN public.repositories ON ((repositories.id = npm_remote_repositories.repository_id) AND (repositories.namespace_id = npm_remote_repositories.namespace_id))
WHERE ((repositories.format = $1) AND (repositories.kind = $2)) AND (repositories.soft_deleted_at IS NULL)
LIMIT $3;

Bound args: [2, 2, 100001]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=251.26..465.17 rows=5063 width=75) (actual time=2.771..6.365 rows=5000 loops=1)
   Buffers: shared hit=187
   ->  Hash Join  (cost=251.26..465.17 rows=5063 width=75) (actual time=2.769..5.970 rows=5000 loops=1)
         Hash Cond: ((repositories.id = npm_remote_repositories.repository_id) AND (repositories.namespace_id = npm_remote_repositories.namespace_id))
         Buffers: shared hit=187
         ->  Append  (cost=0.00..187.31 rows=5063 width=32) (actual time=0.043..1.659 rows=5000 loops=1)
               Buffers: shared hit=87
               ->  Seq Scan on repositories_p00 repositories_1  (cost=0.00..0.00 rows=1 width=32) (actual time=0.008..0.009 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p01 repositories_2  (cost=0.00..0.00 rows=1 width=32) (actual time=0.005..0.005 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p02 repositories_3  (cost=0.00..0.00 rows=1 width=32) (actual time=0.003..0.003 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p03 repositories_4  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p04 repositories_5  (cost=0.00..0.00 rows=1 width=32) (actual time=0.005..0.005 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p05 repositories_6  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.003 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p06 repositories_7  (cost=0.00..162.00 rows=5000 width=32) (actual time=0.015..1.045 rows=5000 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
                     Buffers: shared hit=87
               ->  Seq Scan on repositories_p07 repositories_8  (cost=0.00..0.00 rows=1 width=32) (actual time=0.009..0.009 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p08 repositories_9  (cost=0.00..0.00 rows=1 width=32) (actual time=0.010..0.010 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p09 repositories_10  (cost=0.00..0.00 rows=1 width=32) (actual time=0.008..0.009 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p10 repositories_11  (cost=0.00..0.00 rows=1 width=32) (actual time=0.004..0.005 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p11 repositories_12  (cost=0.00..0.00 rows=1 width=32) (actual time=0.003..0.003 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p12 repositories_13  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p13 repositories_14  (cost=0.00..0.00 rows=1 width=32) (actual time=0.005..0.005 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p14 repositories_15  (cost=0.00..0.00 rows=1 width=32) (actual time=0.003..0.003 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p15 repositories_16  (cost=0.00..0.00 rows=1 width=32) (actual time=0.003..0.003 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p16 repositories_17  (cost=0.00..0.00 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p17 repositories_18  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p18 repositories_19  (cost=0.00..0.00 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p19 repositories_20  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.003 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p20 repositories_21  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p21 repositories_22  (cost=0.00..0.00 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p22 repositories_23  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p23 repositories_24  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p24 repositories_25  (cost=0.00..0.00 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p25 repositories_26  (cost=0.00..0.00 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p26 repositories_27  (cost=0.00..0.00 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p27 repositories_28  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p28 repositories_29  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p29 repositories_30  (cost=0.00..0.00 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p30 repositories_31  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p31 repositories_32  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p32 repositories_33  (cost=0.00..0.00 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p33 repositories_34  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p34 repositories_35  (cost=0.00..0.00 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p35 repositories_36  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p36 repositories_37  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p37 repositories_38  (cost=0.00..0.00 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p38 repositories_39  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p39 repositories_40  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p40 repositories_41  (cost=0.00..0.00 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p41 repositories_42  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p42 repositories_43  (cost=0.00..0.00 rows=1 width=32) (actual time=0.010..0.010 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p43 repositories_44  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p44 repositories_45  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p45 repositories_46  (cost=0.00..0.00 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p46 repositories_47  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p47 repositories_48  (cost=0.00..0.00 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p48 repositories_49  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p49 repositories_50  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p50 repositories_51  (cost=0.00..0.00 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p51 repositories_52  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p52 repositories_53  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p53 repositories_54  (cost=0.00..0.00 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p54 repositories_55  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p55 repositories_56  (cost=0.00..0.00 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p56 repositories_57  (cost=0.00..0.00 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p57 repositories_58  (cost=0.00..0.00 rows=1 width=32) (actual time=0.003..0.003 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p58 repositories_59  (cost=0.00..0.00 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p59 repositories_60  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p60 repositories_61  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p61 repositories_62  (cost=0.00..0.00 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p62 repositories_63  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
               ->  Seq Scan on repositories_p63 repositories_64  (cost=0.00..0.00 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (format = '2'::smallint) AND (kind = '2'::smallint))
         ->  Hash  (cost=175.31..175.31 rows=5063 width=75) (actual time=2.663..2.674 rows=5000 loops=1)
               Buckets: 8192  Batches: 1  Memory Usage: 587kB
               Buffers: shared hit=100
               ->  Append  (cost=0.00..175.31 rows=5063 width=75) (actual time=0.084..1.484 rows=5000 loops=1)
                     Buffers: shared hit=100
                     ->  Seq Scan on npm_remote_repositories_p00 npm_remote_repositories_1  (cost=0.00..1.00 rows=1 width=80) (actual time=0.012..0.013 rows=0 loops=1)
                           Buffers: shared hit=1
                     ->  Seq Scan on npm_remote_repositories_p01 npm_remote_repositories_2  (cost=0.00..1.00 rows=1 width=80) (actual time=0.004..0.005 rows=0 loops=1)
                           Buffers: shared hit=1
                     ->  Seq Scan on npm_remote_repositories_p02 npm_remote_repositories_3  (cost=0.00..0.00 rows=1 width=80) (actual time=0.030..0.030 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p03 npm_remote_repositories_4  (cost=0.00..0.00 rows=1 width=80) (actual time=0.010..0.010 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p04 npm_remote_repositories_5  (cost=0.00..0.00 rows=1 width=80) (actual time=0.010..0.010 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p05 npm_remote_repositories_6  (cost=0.00..0.00 rows=1 width=80) (actual time=0.007..0.007 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p06 npm_remote_repositories_7  (cost=0.00..128.00 rows=5000 width=75) (actual time=0.008..0.609 rows=5000 loops=1)
                           Buffers: shared hit=78
                     ->  Seq Scan on npm_remote_repositories_p07 npm_remote_repositories_8  (cost=0.00..1.00 rows=1 width=80) (actual time=0.009..0.009 rows=0 loops=1)
                           Buffers: shared hit=1
                     ->  Seq Scan on npm_remote_repositories_p08 npm_remote_repositories_9  (cost=0.00..1.00 rows=1 width=80) (actual time=0.004..0.004 rows=0 loops=1)
                           Buffers: shared hit=1
                     ->  Seq Scan on npm_remote_repositories_p09 npm_remote_repositories_10  (cost=0.00..1.00 rows=1 width=80) (actual time=0.007..0.007 rows=0 loops=1)
                           Buffers: shared hit=1
                     ->  Seq Scan on npm_remote_repositories_p10 npm_remote_repositories_11  (cost=0.00..0.00 rows=1 width=80) (actual time=0.021..0.021 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p11 npm_remote_repositories_12  (cost=0.00..0.00 rows=1 width=80) (actual time=0.008..0.008 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p12 npm_remote_repositories_13  (cost=0.00..1.00 rows=1 width=80) (actual time=0.006..0.006 rows=0 loops=1)
                           Buffers: shared hit=1
                     ->  Seq Scan on npm_remote_repositories_p13 npm_remote_repositories_14  (cost=0.00..0.00 rows=1 width=80) (actual time=0.009..0.009 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p14 npm_remote_repositories_15  (cost=0.00..0.00 rows=1 width=80) (actual time=0.013..0.013 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p15 npm_remote_repositories_16  (cost=0.00..0.00 rows=1 width=80) (actual time=0.009..0.010 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p16 npm_remote_repositories_17  (cost=0.00..1.00 rows=1 width=80) (actual time=0.005..0.005 rows=0 loops=1)
                           Buffers: shared hit=1
                     ->  Seq Scan on npm_remote_repositories_p17 npm_remote_repositories_18  (cost=0.00..0.00 rows=1 width=80) (actual time=0.014..0.014 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p18 npm_remote_repositories_19  (cost=0.00..0.00 rows=1 width=80) (actual time=0.009..0.009 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p19 npm_remote_repositories_20  (cost=0.00..0.00 rows=1 width=80) (actual time=0.007..0.007 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p20 npm_remote_repositories_21  (cost=0.00..0.00 rows=1 width=80) (actual time=0.010..0.010 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p21 npm_remote_repositories_22  (cost=0.00..0.00 rows=1 width=80) (actual time=0.007..0.007 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p22 npm_remote_repositories_23  (cost=0.00..0.00 rows=1 width=80) (actual time=0.008..0.008 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p23 npm_remote_repositories_24  (cost=0.00..0.00 rows=1 width=80) (actual time=0.009..0.009 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p24 npm_remote_repositories_25  (cost=0.00..0.00 rows=1 width=80) (actual time=0.007..0.007 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p25 npm_remote_repositories_26  (cost=0.00..1.00 rows=1 width=80) (actual time=0.007..0.007 rows=0 loops=1)
                           Buffers: shared hit=1
                     ->  Seq Scan on npm_remote_repositories_p26 npm_remote_repositories_27  (cost=0.00..1.00 rows=1 width=80) (actual time=0.004..0.004 rows=0 loops=1)
                           Buffers: shared hit=1
                     ->  Seq Scan on npm_remote_repositories_p27 npm_remote_repositories_28  (cost=0.00..1.00 rows=1 width=80) (actual time=0.004..0.004 rows=0 loops=1)
                           Buffers: shared hit=1
                     ->  Seq Scan on npm_remote_repositories_p28 npm_remote_repositories_29  (cost=0.00..1.00 rows=1 width=80) (actual time=0.007..0.007 rows=0 loops=1)
                           Buffers: shared hit=1
                     ->  Seq Scan on npm_remote_repositories_p29 npm_remote_repositories_30  (cost=0.00..0.00 rows=1 width=80) (actual time=0.008..0.008 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p30 npm_remote_repositories_31  (cost=0.00..1.00 rows=1 width=80) (actual time=0.004..0.004 rows=0 loops=1)
                           Buffers: shared hit=1
                     ->  Seq Scan on npm_remote_repositories_p31 npm_remote_repositories_32  (cost=0.00..0.00 rows=1 width=80) (actual time=0.010..0.010 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p32 npm_remote_repositories_33  (cost=0.00..0.00 rows=1 width=80) (actual time=0.007..0.007 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p33 npm_remote_repositories_34  (cost=0.00..0.00 rows=1 width=80) (actual time=0.010..0.010 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p34 npm_remote_repositories_35  (cost=0.00..0.00 rows=1 width=80) (actual time=0.007..0.007 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p35 npm_remote_repositories_36  (cost=0.00..1.00 rows=1 width=80) (actual time=0.005..0.005 rows=0 loops=1)
                           Buffers: shared hit=1
                     ->  Seq Scan on npm_remote_repositories_p36 npm_remote_repositories_37  (cost=0.00..1.00 rows=1 width=80) (actual time=0.007..0.007 rows=0 loops=1)
                           Buffers: shared hit=1
                     ->  Seq Scan on npm_remote_repositories_p37 npm_remote_repositories_38  (cost=0.00..0.00 rows=1 width=80) (actual time=0.008..0.008 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p38 npm_remote_repositories_39  (cost=0.00..1.00 rows=1 width=80) (actual time=0.007..0.007 rows=0 loops=1)
                           Buffers: shared hit=1
                     ->  Seq Scan on npm_remote_repositories_p39 npm_remote_repositories_40  (cost=0.00..0.00 rows=1 width=80) (actual time=0.007..0.007 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p40 npm_remote_repositories_41  (cost=0.00..1.00 rows=1 width=80) (actual time=0.004..0.004 rows=0 loops=1)
                           Buffers: shared hit=1
                     ->  Seq Scan on npm_remote_repositories_p41 npm_remote_repositories_42  (cost=0.00..0.00 rows=1 width=80) (actual time=0.009..0.009 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p42 npm_remote_repositories_43  (cost=0.00..1.00 rows=1 width=80) (actual time=0.004..0.004 rows=0 loops=1)
                           Buffers: shared hit=1
                     ->  Seq Scan on npm_remote_repositories_p43 npm_remote_repositories_44  (cost=0.00..0.00 rows=1 width=80) (actual time=0.007..0.007 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p44 npm_remote_repositories_45  (cost=0.00..1.00 rows=1 width=80) (actual time=0.006..0.006 rows=0 loops=1)
                           Buffers: shared hit=1
                     ->  Seq Scan on npm_remote_repositories_p45 npm_remote_repositories_46  (cost=0.00..0.00 rows=1 width=80) (actual time=0.007..0.007 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p46 npm_remote_repositories_47  (cost=0.00..0.00 rows=1 width=80) (actual time=0.009..0.009 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p47 npm_remote_repositories_48  (cost=0.00..0.00 rows=1 width=80) (actual time=0.008..0.008 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p48 npm_remote_repositories_49  (cost=0.00..0.00 rows=1 width=80) (actual time=0.008..0.008 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p49 npm_remote_repositories_50  (cost=0.00..0.00 rows=1 width=80) (actual time=0.010..0.010 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p50 npm_remote_repositories_51  (cost=0.00..0.00 rows=1 width=80) (actual time=0.009..0.009 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p51 npm_remote_repositories_52  (cost=0.00..0.00 rows=1 width=80) (actual time=0.007..0.007 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p52 npm_remote_repositories_53  (cost=0.00..0.00 rows=1 width=80) (actual time=0.010..0.010 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p53 npm_remote_repositories_54  (cost=0.00..1.00 rows=1 width=80) (actual time=0.004..0.004 rows=0 loops=1)
                           Buffers: shared hit=1
                     ->  Seq Scan on npm_remote_repositories_p54 npm_remote_repositories_55  (cost=0.00..0.00 rows=1 width=80) (actual time=0.009..0.009 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p55 npm_remote_repositories_56  (cost=0.00..0.00 rows=1 width=80) (actual time=0.007..0.008 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p56 npm_remote_repositories_57  (cost=0.00..0.00 rows=1 width=80) (actual time=0.008..0.008 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p57 npm_remote_repositories_58  (cost=0.00..1.00 rows=1 width=80) (actual time=0.007..0.007 rows=0 loops=1)
                           Buffers: shared hit=1
                     ->  Seq Scan on npm_remote_repositories_p58 npm_remote_repositories_59  (cost=0.00..1.00 rows=1 width=80) (actual time=0.004..0.004 rows=0 loops=1)
                           Buffers: shared hit=1
                     ->  Seq Scan on npm_remote_repositories_p59 npm_remote_repositories_60  (cost=0.00..0.00 rows=1 width=80) (actual time=0.008..0.008 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p60 npm_remote_repositories_61  (cost=0.00..0.00 rows=1 width=80) (actual time=0.010..0.010 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p61 npm_remote_repositories_62  (cost=0.00..0.00 rows=1 width=80) (actual time=0.007..0.007 rows=0 loops=1)
                     ->  Seq Scan on npm_remote_repositories_p62 npm_remote_repositories_63  (cost=0.00..1.00 rows=1 width=80) (actual time=0.006..0.007 rows=0 loops=1)
                           Buffers: shared hit=1
                     ->  Seq Scan on npm_remote_repositories_p63 npm_remote_repositories_64  (cost=0.00..0.00 rows=1 width=80) (actual time=0.009..0.009 rows=0 loops=1)
 Planning:
   Buffers: shared hit=2357 read=8
 Planning Time: 58.628 ms
 Execution Time: 7.986 ms

Timings: planning 58.628ms, execution 7.986ms, total 66.614ms.

datastore.NpmRemoteRepositoryStore.SetHealthStatus.CASSelect

Summary: The locked read inside the compare-and-set transaction: same one-partition index path as the health read with a LockRows node above it for FOR UPDATE. Actual rows match the estimate (1 / 1). No anomalies.

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

Rendered SQL:

SELECT npm_remote_repositories.last_health_status AS "npm_remote_repositories.last_health_status",
npm_remote_repositories.last_health_checked_at AS "npm_remote_repositories.last_health_checked_at"
FROM public.npm_remote_repositories
WHERE (npm_remote_repositories.namespace_id = $1::uuid) AND (npm_remote_repositories.repository_id = $2::uuid)
LIMIT $3
FOR UPDATE;

Bound args: [8373504c-4e82-4c67-86f2-57ad506352c2, 35b9363d-1395-4f33-8ad5-9fe5c7082c52, 1]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.28..8.31 rows=1 width=20) (actual time=0.033..0.034 rows=1 loops=1)
   Buffers: shared hit=4
   ->  LockRows  (cost=0.28..8.31 rows=1 width=20) (actual time=0.032..0.032 rows=1 loops=1)
         Buffers: shared hit=4
         ->  Index Scan using npm_remote_repositories_p54_namespace_id_repository_id_idx on npm_remote_repositories_p54 npm_remote_repositories  (cost=0.28..8.30 rows=1 width=20) (actual time=0.026..0.027 rows=1 loops=1)
               Index Cond: ((namespace_id = '8373504c-4e82-4c67-86f2-57ad506352c2'::uuid) AND (repository_id = '35b9363d-1395-4f33-8ad5-9fe5c7082c52'::uuid))
               Buffers: shared hit=3
 Planning:
   Buffers: shared hit=54
 Planning Time: 0.825 ms
 Execution Time: 0.076 ms

Timings: planning 0.825ms, execution 0.076ms, total 0.901ms.

datastore.NpmRemoteRepositoryStore.SetHealthStatus.CASUpdate

Summary: The write half of the compare-and-set: Update over the same one-partition index path, touching exactly the locked row. Execution time is dominated by the two FK-constraint trigger checks (~1.76ms of 2.368ms), which every UPDATE on this table pays; the scan itself reads 3 buffers. No anomalies.

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

Rendered SQL:

UPDATE public.npm_remote_repositories
SET (last_health_status, last_health_checked_at) = ($1, $2::timestamp with time zone)
WHERE (npm_remote_repositories.namespace_id = $3::uuid) AND (npm_remote_repositories.repository_id = $4::uuid);

Bound args: [1, now(), 0046a533-d814-48d3-9cb3-7621d1f4c7e1, 852d1d56-7502-49d8-b4a4-be6ed2bfe6bd]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Update on npm_remote_repositories  (cost=0.28..8.30 rows=0 width=0) (actual time=0.207..0.208 rows=0 loops=1)
   Update on npm_remote_repositories_p42 npm_remote_repositories_1
   Buffers: shared hit=26
   ->  Index Scan using npm_remote_repositories_p42_namespace_id_repository_id_idx on npm_remote_repositories_p42 npm_remote_repositories_1  (cost=0.28..8.30 rows=1 width=20) (actual time=0.021..0.022 rows=1 loops=1)
         Index Cond: ((namespace_id = '0046a533-d814-48d3-9cb3-7621d1f4c7e1'::uuid) AND (repository_id = '852d1d56-7502-49d8-b4a4-be6ed2bfe6bd'::uuid))
         Buffers: shared hit=3
 Planning:
   Buffers: shared hit=65
 Planning Time: 0.792 ms
 Trigger for constraint fk_npm_remote_repositories_namespace_id_namespaces on npm_remote_repositories_p42: time=0.030 calls=1
 Trigger for constraint fk_npm_remote_repositories_repository_id_repositories on npm_remote_repositories_p42: time=1.733 calls=1
 Execution Time: 2.368 ms

Timings: planning 0.792ms, execution 2.368ms, total 3.160ms.

Related to #346 (closed)

Edited by Dzmitry (Dima) Meshcharakou

Merge request reports