analyzing-cockroachdb

name	analyzing-cockroachdb
description	Use when working with Cockroachdb — cockroachDB cluster health, range distribution, SQL statistics, schema change monitoring, and query optimization.
connection_type	cockroachdb
preload	false

CockroachDB Analysis Skill

Analyze and optimize CockroachDB clusters with safe, read-only operations.

MANDATORY: Two-Phase Execution

You MUST follow this two-phase pattern. Skipping Phase 1 causes hallucinated database/table names.

Phase 1: Discovery (ALWAYS run first)

#!/bin/bash

# 1. Cluster overview
cockroach node status --certs-dir="$CERTS_DIR" --host="$CRDB_HOST"

# 2. List databases
cockroach sql --certs-dir="$CERTS_DIR" --host="$CRDB_HOST" -e "SHOW DATABASES;"

# 3. List tables in a database
cockroach sql --certs-dir="$CERTS_DIR" --host="$CRDB_HOST" -e "SHOW TABLES FROM mydb;"

# 4. Get table schema
cockroach sql --certs-dir="$CERTS_DIR" --host="$CRDB_HOST" -e "SHOW CREATE TABLE mydb.mytable;"

# 5. Check column names
cockroach sql --certs-dir="$CERTS_DIR" --host="$CRDB_HOST" -e "SHOW COLUMNS FROM mydb.mytable;"

Phase 1 outputs:

• Cluster topology and node liveness
• List of databases and tables
• Table schemas with confirmed column names

Phase 2: Analysis (only after Phase 1)

Only reference databases, tables, and columns confirmed in Phase 1.

Shell Script Patterns

Helper Function

#!/bin/bash

# Core SQL runner — always use this
crdb_sql() {
    local query="$1"
    cockroach sql --certs-dir="${CERTS_DIR:-certs}" --host="${CRDB_HOST:-localhost}" \
        --format=tsv -e "$query" 2>/dev/null
}

# Admin UI API helper (if available)
crdb_api() {
    local endpoint="$1"
    curl -sk "https://${CRDB_HOST:-localhost}:8080/_admin/v1/$endpoint"
}

Anti-Hallucination Rules

• NEVER reference a database or table without confirming via SHOW DATABASES / SHOW TABLES
• NEVER reference column names without seeing them in SHOW COLUMNS or SHOW CREATE TABLE
• NEVER assume node IDs — always get them from node status
• NEVER guess range counts — always query crdb_internal.ranges
• NEVER assume cluster version — check with SHOW CLUSTER SETTING version

Safety Rules

• READ-ONLY ONLY: Use only SELECT, SHOW, EXPLAIN, system catalog queries
• FORBIDDEN: DROP, ALTER, INSERT, UPDATE, DELETE, SET CLUSTER SETTING without explicit user request
• ALWAYS add LIMIT to queries on user tables
• Use EXPLAIN ANALYZE carefully — it executes the query
• Prefer EXPLAIN (no ANALYZE) for plan inspection without execution

Common Operations

Cluster Health Overview

#!/bin/bash
echo "=== Node Status ==="
crdb_sql "SELECT node_id, address, is_live, ranges, locality FROM crdb_internal.gossip_nodes ORDER BY node_id;"

echo ""
echo "=== Store Status ==="
crdb_sql "SELECT node_id, store_id, used, available, range_count FROM crdb_internal.kv_store_status ORDER BY node_id;"

echo ""
echo "=== Cluster Settings (non-default) ==="
crdb_sql "SHOW ALL CLUSTER SETTINGS;" | grep -v "default"

echo ""
echo "=== Version ==="
crdb_sql "SELECT version, crdb_internal.node_id() as node_id;"

Range Distribution Analysis

#!/bin/bash
echo "=== Range Distribution per Node ==="
crdb_sql "SELECT lease_holder, count(*) as range_count FROM crdb_internal.ranges GROUP BY lease_holder ORDER BY lease_holder;"

echo ""
echo "=== Hot Ranges (top 10 by QPS) ==="
crdb_sql "SELECT range_id, table_name, start_pretty, lease_holder, queries_per_second FROM crdb_internal.ranges ORDER BY queries_per_second DESC LIMIT 10;"

echo ""
echo "=== Under-replicated Ranges ==="
crdb_sql "SELECT range_id, table_name, replicas, learner_replicas FROM crdb_internal.ranges WHERE array_length(replicas, 1) < 3;"

SQL Statistics Analysis

#!/bin/bash
echo "=== Top Slow Queries (by mean latency) ==="
crdb_sql "SELECT substring(metadata->>'query', 1, 80) as query, statistics->'execution_statistics'->>'cnt' as exec_count, statistics->'execution_statistics'->>'meanRunLatency' as mean_lat FROM crdb_internal.statement_statistics ORDER BY (statistics->'execution_statistics'->>'meanRunLatency')::float DESC LIMIT 15;"

echo ""
echo "=== Schema Changes in Progress ==="
crdb_sql "SELECT job_id, description, status, running_status, fraction_completed FROM crdb_internal.jobs WHERE job_type = 'SCHEMA CHANGE' AND status IN ('running', 'paused', 'pending') ORDER BY created DESC LIMIT 10;"

Table Analysis

#!/bin/bash
DB_NAME="${1:-defaultdb}"
TABLE="${2:-mytable}"

echo "=== Table Stats ==="
crdb_sql "SHOW STATISTICS FOR TABLE $DB_NAME.$TABLE;"

echo ""
echo "=== Index Usage ==="
crdb_sql "SELECT * FROM crdb_internal.index_usage_statistics WHERE table_id = (SELECT id FROM crdb_internal.tables WHERE name = '$TABLE' AND database_name = '$DB_NAME');"

echo ""
echo "=== Table Ranges ==="
crdb_sql "SELECT count(*) as range_count, sum(range_size_mb) as total_mb FROM crdb_internal.ranges WHERE table_name = '$TABLE';"

Output Format

Present results as a structured report:

Analyzing Cockroachdb Report
════════════════════════════
Resources discovered: [count]

Resource       Status    Key Metric    Issues
──────────────────────────────────────────────
[name]         [ok/warn] [value]       [findings]

Summary: [total] resources | [ok] healthy | [warn] warnings | [crit] critical
Action Items: [list of prioritized findings]

Target ≤50 lines of output. Use tables for multi-resource comparisons.

Counter-Rationalizations

Shortcut	Counter	Why
"I'll skip discovery and check known resources"	Always run Phase 1 discovery first	Resource names change, new resources appear — assumed names cause errors
"The user only asked for a quick check"	Follow the full discovery → analysis flow	Quick checks miss critical issues; structured analysis catches silent failures
"Default configuration is probably fine"	Audit configuration explicitly	Defaults often leave logging, security, and optimization features disabled
"Metrics aren't needed for this"	Always check relevant metrics when available	API/CLI responses show current state; metrics reveal trends and intermittent issues
"I don't have access to that"	Try the command and report the actual error	Assumed permission failures prevent useful investigation; actual errors are informative

Common Pitfalls

• Multi-region latency: Cross-region queries have higher latency — check locality settings
• Contention on hot keys: Sequential key generation causes range hotspots — use UUID or hash-sharded indexes
• Schema change locks: Long-running schema changes can block writes — check job status
• Range splits: Tables with rapid growth may need manual pre-splitting
• Transaction retries: CockroachDB uses serializable isolation — application must handle retry errors (40001)
• Clock skew: Node clock skew > 500ms causes errors — check node status for clock offset