server: SpanStats ApproximateTotalStats over-counts, scaling with node count instead of replication factor

[alyshanjahani-crl]

Describe the problem

The SpanStats RPC returns an ApproximateTotalStats field intended to approximate the post-replication (physical) MVCC stats of a span — i.e. roughly logical_size × replication_factor. Instead, it scales with the number of nodes the span's ranges live on, not the replication factor. For a span that spans more nodes than RF (any large span on a cluster bigger than RF), ApproximateTotalStats over-reports by a factor of numNodesContacted / RF.

Root cause

Two behaviors compound in pkg/server/span_stats_server.go:

1. statsForSpan applies no locality filter. It scans all range descriptors overlapping the span and fetches RangeStats for every range via leaseholder-routed KV requests (span_stats_server.go#L325-L348). So every fanned-out node returns the full logical span size in its TotalStats, not just the portion it holds replicas for.

2. collectSpanStatsResponses sums each node's TotalStats into ApproximateTotalStats unconditionally, once per responding node (span_stats_server.go#L223):

   res.SpanToStats[spanStr].ApproximateTotalStats.Add(spanStats.TotalStats)

Result: ApproximateTotalStats ≈ numNodesContacted × logical_size. The bug is masked for small spans confined to exactly RF nodes (where numNodes == RF), and only appears once a span is wide enough to live on more than RF nodes.

To Reproduce

1. Create a 5-node cluster, default RF=3 (so node count > RF). (Reproduced on v26.2.2; the code is identical on master.)
2. Seed a table whose ranges spread across all 5 nodes:

   CREATE DATABASE repro;
   CREATE TABLE repro.t (id INT PRIMARY KEY, v STRING);
   INSERT INTO repro.t SELECT g, repeat('x', 256) FROM generate_series(1, 200000) AS g;
   ALTER TABLE repro.t SPLIT AT SELECT g*4000 FROM generate_series(1, 49) AS g;
   ALTER TABLE repro.t SCATTER;
   -- wait ~60s; verify all 5 nodes hold replicas:
   -- WITH r AS (SELECT unnest(replicas) AS n FROM [SHOW RANGES FROM TABLE repro.t])
   -- SELECT count(DISTINCT n) FROM r;   --> 5

3. Read the span stats (drives planner.SpanStats → NodeID="0" fan-out, SkipApproxTotalStats=false):

   WITH sp AS (SELECT crdb_internal.table_span('repro.t'::regclass::oid::int) AS s)
   SELECT (stats->'total_stats'->>'live_bytes')::int              AS logical_live_bytes,
          (stats->'approximate_total_stats'->>'live_bytes')::int  AS approx_total_live_bytes,
          jsonb_array_length(stats->'store_ids')                  AS nodes
   FROM crdb_internal.tenant_span_stats(ARRAY[((SELECT s FROM sp)[1], (SELECT s FROM sp)[2])]);

Observed — with a control span confined to a single range (3 nodes). RF=3 in both cases:

span	ranges	nodes span lives on	RF	logical live_bytes	expected approx (RF×)	actual ApproximateTotalStats	multiplier
repro.t (50 ranges)	50	5	3	55,597,817	166,793,451	281,397,937	5.06×
repro.small (1 range)	1	3	3	1,131,636	3,394,908	3,394,908	3.00×

Since RF is fixed at 3 and only the node count differs (3 → 5), the multiplier tracks node count, not RF. key_bytes shows the same 5.06× / 3.00× split.

Expected behavior

ApproximateTotalStats should approximate RF × logical (≈ 3× here, ~167 MB), independent of how many nodes the span happens to occupy. It should not grow with cluster size.

Additional context — impact

Consumers of SpanStats / crdb_internal.tenant_span_stats see inflated physical/replicated size for any multi-node span; the error grows with numNodes / RF, so it's worst on large clusters.

Possible fix direction: have each fanned-out node report stats only for ranges it actually holds a replica of (locality filter), so summing across nodes yields the true replicated total; or weight per-range by replica count. Note RF can vary per range.

Environment: CockroachDB v26.2.2 (confirmed identical on master @ dceb771); 5-node AWS cluster, insecure.

Epic: none

Jira issue: CRDB-66508

[blathers-crl]

Hi @alyshanjahani-crl, please add a branch-* label to identify the earliest affected branch for this C-bug

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[alyshanjahani-crl]

User-facing impact: the sql.aggregated_livebytes metric inherits this bug

Confirmed end-to-end that this over-count reaches a served metric, not just internal APIs. The mvccStatisticsUpdateJob exports sql.aggregated_livebytes by summing ApproximateTotalStats.LiveBytes over the whole tenant span:

• pkg/sql/mvcc_statistics_update_job.go#L102-L118 — SpanStats(NodeID="0", Spans=[Codec.TenantSpan()]) then total += stats.ApproximateTotalStats.LiveBytes.

Scope note: the exporter only runs when the SQL server has no node ID (line 73, "All external mode tenant servers have no node IDs"). So this affects separate-process (external) SQL tenants; shared-process tenants share the host node ID and never register the gauge.

Repro (v26.2.2): 4 KV nodes (RF=3) + 1 external SQL server; tenant app seeded with a table split into ~50 ranges scattered across all 4 KV nodes.

measurement	value	notes
tenant logical live bytes (total_stats.live_bytes)	60,806,354	ground truth
expected metric (RF=3 × logical)	182,419,062	what it should be
approximate_total_stats.live_bytes (span stats over tenant span)	243,225,416
sql_aggregated_livebytes (external SQL _status/vars)	243,225,416	exact match
multiplier vs logical	4.000	= number of KV nodes, not RF

The exported gauge matches the span-stats ApproximateTotalStats to the byte, and equals numNodes × logical (4×) rather than RF × logical (3×) — over-reporting by numNodes / RF. The error grows with cluster size: a tenant spread across 20 nodes at RF=3 would report ~6.7× the true replicated size.