Analyzes CockroachDB range distribution across tables and indexes using SHOW RANGES to identify range count, size patterns, leaseholder placement, and replication health. Use when investigating hotspots, uneven data distribution, range fragmentation, or validating zone configuration effects without DB Console access.
Analyzes CockroachDB range distribution across tables and indexes using SHOW RANGES to identify range count, size patterns, leaseholder placement, and replication health. Use when investigating hotspots, uneven data distribution, range fragmentation, or validating zone configuration effects without DB Console access.
compatibility
Requires SQL access with admin role or ZONECONFIG system privilege. DETAILS option has high cost; use targeted queries with LIMIT. Production-safe for basic usage.
metadata
{"author":"cockroachdb","version":"1.0"}
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.
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)
SELECT
table_name,
index_name,
COUNT(*) AS range_count
FROM [SHOW RANGES FROMTABLE your_table_name]
GROUPBY table_name, index_name
ORDERBY 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)
SELECT
range_id,
start_key,
end_key,
(span_stats->>'approximate_disk_bytes')::INT/1048576AS size_mb,
lease_holder,
replicas
FROM [SHOW RANGES FROMTABLE your_table_name WITH DETAILS]
ORDERBY (span_stats->>'approximate_disk_bytes')::INTDESC
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)
SELECT
lease_holder,
COUNT(*) AS leaseholder_count,
ROUND(COUNT(*) *100.0/SUM(COUNT(*)) OVER (), 2) AS percentage
FROM [SHOW RANGES FROMTABLE your_table_name]
GROUPBY lease_holder
ORDERBY 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
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 FROMTABLE your_table_name]
WHERE array_length(replicas, 1) <3-- Under-replicatedORDERBY 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
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)
WITH range_counts AS (
SELECT
table_name,
index_name,
COUNT(*) AS range_count
FROM [SHOW RANGES FROMTABLE your_table_name]
GROUPBY table_name, index_name
),
table_sizes AS (
SELECT
table_name,
SUM((span_stats->>'approximate_disk_bytes')::INT) /1073741824.0AS size_gb
FROM [SHOW RANGES FROMTABLE your_table_name WITH DETAILS]
GROUPBY 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
ORDERBY 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.
Scenario: Single node experiencing high CPU, slow reads on specific table.
Steps:
Identify leaseholder concentration: Run Query 3 on suspected table
Validate zone config: Run Query 5 to check lease_preferences
Check for load-based splits: Run Query 1 to detect recent range fragmentation (symptom of hotspot)
Remediate: Configure lease preferences to spread reads, or partition table if hotspot is on sequential key range
Example:
-- Check leaseholder distributionSELECT lease_holder, COUNT(*) FROM [SHOW RANGES FROMTABLE hot_table] GROUPBY lease_holder;
-- Validate zone configSHOW ZONE CONFIGURATION FORTABLE hot_table;
-- Spread leaseholders if concentratedALTER TABLE hot_table CONFIGURE ZONE USING lease_preferences ='[[+region=us-west]]';
Workflow 2: Zone Config Validation
Scenario: After configuring multi-region setup, validate ranges are placed according to constraints.
Steps:
Review intended configs: Run Query 5 (SHOW ZONE CONFIGURATIONS)
Check actual replica placement: Run Query 4 on critical tables, inspect replicas array for node IDs
Map node IDs to regions: Use SHOW REGIONS (cluster-wide) or read the locality column of cockroach node status
Identify mismatches: Ranges not matching constraints indicate rebalancing in progress or misconfiguration
Example:
-- Show zone configSHOW ZONE CONFIGURATION FORTABLE multi_region_table;
-- Check replica placementSELECT range_id, replicas FROM [SHOW RANGES FROMTABLE multi_region_table] LIMIT 20;
-- Map node IDs to regions (cluster-level view)SHOW REGIONS;
-- For per-node locality strings, use the CLI:-- cockroach node status --certs-dir=<certs-dir> --host=<any-live-node>
Workflow 3: Fragmentation Diagnosis
Scenario: Table with high range count relative to size, experiencing latency.
Steps:
Calculate ranges per GB: Run Query 6 (targeted to specific table)
Check for load-based splits: Review write patterns (sequential inserts, high QPS periods)
Determine if expected: Fragmentation may be intentional for load distribution
Remediate if excessive: Increase range_max_bytes (with caution - larger ranges = slower splits), or investigate reducing write hotspots
CRITICAL:range_max_bytes defaults to 512MB. Raising it further without understanding the impact on split/rebalance performance is risky.
Safety Considerations
DETAILS Option Cost
Resource impact:
CPU: Computes span statistics on-demand for each range
Memory: Proportional to range count returned
Timeout risk: High on tables with 1000s of ranges without LIMIT
Mitigation strategies:
Always use LIMIT: Cap at 50-100 ranges for exploratory analysis
Target specific tables: Use FROM TABLE table_name, never cluster-wide SHOW RANGES WITH DETAILS
Use basic queries first: Run Query 1 (no DETAILS) to assess range count before using DETAILS
Production timing: Run during maintenance windows or low-traffic periods
Privilege Safety
Admin role: Full cluster access, use with caution in production
ZONECONFIG privilege: Limited to viewing ranges and zone configs, safer for read-only analysis
Best practice: Grant ZONECONFIG instead of admin for range analysis operators.
