cockroachlabs

Analyze table statistics and estimated row counts to understand optimizer decisions and diagnose plan quality issues

Use SHOW BACKUP to analyze incremental backup efficiency by comparing data_size, row_count, and performance metrics across backup chains. Calculate storage savings, monitor change rates, and optimize backup frequency based on data patterns. Essential for balancing RPO requirements with storage costs.

Use SHOW RANGES commands to analyze how table data and indexes are split into ranges and distributed across cluster nodes. Understand key-value encoding, range boundaries, replica placement, and leaseholder assignment for performance troubleshooting.

Analyze query latency using mean, max, and min metrics from statement statistics; percentile data (p50/p90/p99) only available in DB Console UI

Analyze how ranges and replicas are distributed across regions in multi-region clusters. Query range placement, verify regional constraints, identify misplaced ranges, and detect rebalancing issues. Essential for troubleshooting latency, verifying compliance, and optimizing multi-region performance.

Use USING HASH clause to distribute sequential keys across ranges, preventing write hotspots. Hash function maps sequential values to buckets (default 16) spreading writes across the cluster. Fixes timestamp, auto-increment, and date-based hotspots with trade-off on range scan efficiency.

Comprehensive guide to index best practices in CockroachDB. Covers when to create indexes, naming conventions, composite vs single-column indexes, covering indexes with STORING clause, avoiding redundant indexes, monitoring index usage, and dropping unused indexes. Use when user says "index best practices", "index guidelines", "index strategy", "how to index", or needs comprehensive indexing advice.

Identify and cancel problematic queries and sessions impacting cluster performance

Audits a self-hosted CockroachDB cluster against the CIS CockroachDB Benchmark v1.0.0 Level 1 controls. Supports two audit depths — quick automated scans and full CIS audit procedures. Produces a structured PASS/FAIL/MANUAL report covering installation, system hardening, logging, user access, data protection, and CockroachDB settings. Use when preparing for CIS compliance assessments, hardening self-hosted deployments, or validating security posture against industry benchmarks.

Guides benchmarking and comparing explicit multi-statement transactions versus single-statement CTE transactions in CockroachDB, with fair test methodology, contention analysis, and performance interpretation. Use when comparing transaction formulations, benchmarking CockroachDB workloads under contention, investigating retry pressure, or deciding whether to rewrite multi-step application flows into single SQL statements.

Guides application developers in designing correct and performant transaction patterns for CockroachDB, covering transaction lifetime, implicit vs explicit transactions, retry handling with exponential backoff, pushing invariants into SQL, selective pessimistic locking, set-based operations, connection pooling, prepared statements, keyset pagination, follower reads, and separating business logic from database logic. Use when building applications on CockroachDB, designing transaction workflows, handling retries, optimizing application-layer database interactions, or configuring connection pools.

Guides developers in selecting and implementing multi-region patterns for CockroachDB applications, covering active-passive vs active-active architectures, REGIONAL BY ROW, GLOBAL tables, manual geo-partitioning with lease preferences, and live demo setup with validation queries. Use when designing multi-region database topologies, choosing between REGIONAL BY ROW and manual partitioning, building multi-region demos, or optimizing cross-region latency.

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.

Estimates storage requirements for CockroachDB online schema change backfills using SHOW RANGES WITH DETAILS, KEYS, INDEXES. Use before CREATE INDEX, ADD COLUMN with INDEX/UNIQUE, ALTER PRIMARY KEY, CREATE MATERIALIZED VIEW, CREATE TABLE AS, REFRESH, or SET LOCALITY on tables with large per-index footprints, to avoid mid-backfill disk exhaustion.

Audits optimizer table statistics for staleness, missing coverage, and data quality issues using SHOW STATISTICS. Use when diagnosing poor query performance, unexpected plan changes, or after bulk data changes to identify stale statistics requiring refresh via CREATE STATISTICS.

Monitors CockroachDB background job health by identifying failed, paused, and long-running jobs using SHOW JOBS and SHOW AUTOMATIC JOBS. Surfaces schema changes, backups/restores, automatic statistics collection, and SQL stats compaction jobs without DB Console access. Use when investigating schema change delays, failed backups, or automatic job issues.