analyzing-range-distribution

What this skill does

# Analyzing Range Distribution

Analyzes CockroachDB range distribution, leaseholder placement, and zone configuration compliance using `SHOW RANGES` and `SHOW ZONE CONFIGURATIONS` commands. Identifies range count anomalies, size imbalances, leaseholder hotspots, and replication issues - entirely via SQL without requiring DB Console access.

**Complement to profiling skills:** This skill analyzes range-level data distribution; for query performance patterns, see [profiling-statement-fingerprints](../profiling-statement-fingerprints/SKILL.md). For schema change storage planning, see [analyzing-schema-change-storage-risk](../analyzing-schema-change-storage-risk/SKILL.md).

## When to Use This Skill

- Identify tables/indexes with excessive range counts indicating fragmentation
- Detect range size imbalances or uneven data distribution across nodes
- Investigate leaseholder concentration causing read hotspots
- Validate zone configuration effects on range placement and replica distribution
- Diagnose range-level replication issues (under-replicated or unavailable ranges)
- Analyze range split patterns from high write volume
- SQL-only range analysis without DB Console access

**For schema change planning:** Use [analyzing-schema-change-storage-risk](../analyzing-schema-change-storage-risk/SKILL.md) to estimate storage requirements before CREATE INDEX or ADD COLUMN operations.

## Prerequisites

- SQL connection to CockroachDB cluster
- Admin role OR `ZONECONFIG` system privilege
- Understanding of CockroachDB range architecture (default 512MB max size; verify with `SHOW ZONE CONFIGURATION FOR RANGE default`)
- Knowledge of cluster topology (node IDs, regions, availability zones)

**Check your privileges:**
```sql
SHOW SYSTEM GRANTS FOR <username>;  -- Should show admin or ZONECONFIG
```

See [permissions reference](references/permissions.md) for RBAC setup.

## Core Concepts

### Ranges: Units of Data Distribution

**Range:** Contiguous key space segment (default 512MB max size, configurable via zone config `range_max_bytes`)
**Raft group:** Each range replicated across nodes (default 3 replicas)
**Leaseholder:** Single replica handling reads and coordinating writes for a range

**Critical:** Ranges split automatically at `range_max_bytes` (default 512MB), but can fragment further due to load-based splitting during high write traffic.

### Leaseholders and Hotspots

**Leaseholder concentration:** Single node holding disproportionate leaseholders = read hotspot
**Load-based splitting:** CockroachDB splits ranges experiencing high QPS, increasing range count
**Hotspot symptoms:** High CPU on single node, slow reads on specific table/index

### Range Fragmentation

**Fragmentation:** Excessive range splits creating many small ranges (overhead from Raft coordination)
**Causes:** High write throughput, sequential inserts (timestamp-based primary keys), load-based splitting
**Symptoms:** High range count relative to data size, increased latency from Raft overhead

**Fragmentation metric:** Ranges per GB. With the 512MB default `range_max_bytes`, a fully-grown range covers 0.5 GB — so ~2 ranges/GB is the natural floor. Anything well above that (e.g., 10+ ranges/GB) suggests load-based splits or many small ranges; tune to your workload.

### Zone Configurations

**Zone config:** Replication and placement policies for databases, tables, or indexes
**Replication factor:** Number of replicas per range (default: 3)
**Constraints:** Node placement rules (region, availability zone, node attributes)

**Use case:** Validate intended zone config matches actual range placement.

### SHOW RANGES DETAILS Option

**CRITICAL SAFETY WARNING:** The `WITH DETAILS` option computes `span_stats` (range size, key counts) on-demand, causing:
- **High CPU usage** from statistics computation
- **Memory overhead** proportional to range count
- **Query timeouts** on large tables without LIMIT

**Best practice:** Always use `LIMIT` with `DETAILS`, target specific tables/indexes, avoid cluster-wide scans.

## Core Diagnostic Queries

### Query 1: Range Count by Table (Production-Safe)

```sql
SELECT
  table_name,
  index_name,
  COUNT(*) AS range_count
FROM [SHOW RANGES FROM TABLE your_table_name]
GROUP BY table_name, index_name
ORDER BY range_count DESC;
```

**Interpretation:** High range count (1000s) on small tables indicates fragmentation. Cross-reference with table size.

**Safety:** No `DETAILS` option = production-safe, minimal overhead.

### Query 2: Range Size Analysis (Targeted DETAILS)

```sql
SELECT
  range_id,
  start_key,
  end_key,
  (span_stats->>'approximate_disk_bytes')::INT / 1048576 AS size_mb,
  lease_holder,
  replicas
FROM [SHOW RANGES FROM TABLE your_table_name WITH DETAILS]
ORDER BY (span_stats->>'approximate_disk_bytes')::INT DESC
LIMIT 50;
```

**Interpretation:** Ranges close to or above `range_max_bytes` (default 512MB) indicate split lag; many small ranges (<10MB) indicate fragmentation.

**CRITICAL:** Always include `LIMIT` and target specific tables. Never run `SHOW RANGES WITH DETAILS` on entire database.

### Query 3: Leaseholder Distribution (Hotspot Detection)

```sql
SELECT
  lease_holder,
  COUNT(*) AS leaseholder_count,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS percentage
FROM [SHOW RANGES FROM TABLE your_table_name]
GROUP BY lease_holder
ORDER BY leaseholder_count DESC;
```

**Interpretation:** >40% leaseholders on single node in balanced cluster = hotspot. Check if table has zone constraints favoring specific nodes.

**Remediation:** Use `ALTER TABLE ... CONFIGURE ZONE USING lease_preferences` to spread leaseholders.

### Query 4: Range Replication Health Check

```sql
SELECT
  range_id,
  start_key,
  replicas,
  array_length(replicas, 1) AS replica_count,
  voting_replicas,
  array_length(voting_replicas, 1) AS voting_replica_count,
  lease_holder
FROM [SHOW RANGES FROM TABLE your_table_name]
WHERE array_length(replicas, 1) < 3  -- Under-replicated
ORDER BY range_id
LIMIT 100;
```

**Interpretation:** `replica_count < 3` = under-replicated (data loss risk). Check for node failures, decommissioning operations, or zone config mismatches.

**Safety:** No `DETAILS` = production-safe.

### Query 5: Zone Configuration Audit

```sql
SHOW ZONE CONFIGURATIONS;
```

**Output columns:**
- `target`: Database, table, or index
- `raw_config_sql`: Zone config SQL (replication factor, constraints)

**Use case:** Validate intended replication factor and placement constraints match expected design.

**Cross-reference:** Compare zone configs with Query 3 (leaseholder distribution) and Query 4 (replica health) to validate actual placement.

### Query 6: Fragmentation Analysis (Ranges per GB)

```sql
WITH range_counts AS (
  SELECT
    table_name,
    index_name,
    COUNT(*) AS range_count
  FROM [SHOW RANGES FROM TABLE your_table_name]
  GROUP BY table_name, index_name
),
table_sizes AS (
  SELECT
    table_name,
    SUM((span_stats->>'approximate_disk_bytes')::INT) / 1073741824.0 AS size_gb
  FROM [SHOW RANGES FROM TABLE your_table_name WITH DETAILS]
  GROUP BY table_name
)
SELECT
  rc.table_name,
  rc.index_name,
  rc.range_count,
  ts.size_gb,
  ROUND(rc.range_count / NULLIF(ts.size_gb, 0), 2) AS ranges_per_gb
FROM range_counts rc
JOIN table_sizes ts ON rc.table_name = ts.table_name
ORDER BY ranges_per_gb DESC;
```

**Interpretation:**
- **Healthy:** 1-15 ranges/GB
- **Moderate fragmentation:** 16-50 ranges/GB
- **Severe fragmentation:** 50+ ranges/GB

**CRITICAL:** This query uses `DETAILS` - only run on targeted tables with known size, never cluster-wide.

**Remediation:** Increase `range_max_bytes` via zone config (with caution), or accept fragmentation if caused by necessary load-based splitting.

See [sql-queries reference](references/sql-queries.md) for complete query variations and guardrails.

## Common Workflows

### Workflow 1: Hotspot Investigation

**Scenario:** Single node experiencing high CPU, slow reads on specific table.

**Ste