cockroach-university-assets

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

Check cluster version and upgrade status using version queries, cluster settings, and internal tables to monitor upgrade progress and verify version states

Check where leaseholders are located for tables using SHOW RANGES and crdb_internal queries. Verify leaseholder placement matches expected locality, troubleshoot wrong-region leaseholders, and understand how locality affects read performance.

Understand and configure CRON expressions for CockroachDB backup schedules. Use when user asks "how to write cron", "schedule syntax", "cron format", "backup timing", or needs to configure custom backup frequencies beyond shortcuts.

Configure HAProxy health checks, backend servers, load balancing algorithms, and connection limits for CockroachDB

Configure load balancer health checks using CockroachDB health endpoints to automatically remove draining nodes from rotation during maintenance. Use when user asks "load balancer health check", "HAProxy health endpoint", "/health?ready=1", or "automatic node removal".

Configure load balancer health checks using CockroachDB /health?ready=1 endpoint to route traffic only to healthy, ready nodes and avoid routing to failed or draining nodes.

Configure backups with COCKROACH_LOCALITY URL parameter to write backup data to storage locations matching node locality. Minimizes cross-region data transfer costs and latency while supporting data sovereignty compliance by keeping data within geographic boundaries.

Create automated recurring backup schedules in CockroachDB using CREATE SCHEDULE FOR BACKUP command. Use when user asks to "automate backups", "schedule backups", "recurring backups", "automatic backups", or wants backups to run without manual intervention.

Create backups with revision history to enable point-in-time recovery (PITR) in CockroachDB. Captures all MVCC versions between backup start and end times, allowing restore to any timestamp within backup window. Use when user asks to "enable PITR", "backup with revision history", "point-in-time recovery", "backup all versions", or needs ability to restore to arbitrary timestamps.

Create incremental backups using BACKUP INTO LATEST command to capture only data changed since last backup. Use when user asks for "incremental backup", "backup changes only", "reduce backup size", "hourly backups", or wants storage-efficient frequent backups.

Diagnose node failures by correlating multiple signals including liveness, health checks, logs, and metrics. Use when investigating cluster instability, troubleshooting node outages, or determining root causes of failures.

Diagnose write intent buildup issues by monitoring intent metrics, identifying problematic long-running transactions, analyzing root causes, and implementing resolution strategies to restore performance.

Enable automatic diagnostics collection for slow queries and download bundles containing EXPLAIN ANALYZE output, trace data, and execution metadata for deep troubleshooting

Enable and configure managed backups for CockroachDB Cloud clusters, adjusting backup frequency and retention to meet disaster recovery requirements. Use when setting up automated backups, adjusting RPO/RTO targets, or optimizing backup costs.

Create full cluster backups in CockroachDB using BACKUP INTO command. Captures all databases, tables, schemas, users, and system metadata in a single consistent snapshot. Use when user asks to "backup the cluster", "create full backup", "backup all databases", "disaster recovery backup", "pre-upgrade backup", or needs complete cluster state for DR or migration.

Create database-level backups in CockroachDB using BACKUP DATABASE command. Use when user asks to "backup a database", "backup specific database", "application backup", or needs targeted recovery for single database without full cluster restore.

Create backups of individual tables or groups of tables in CockroachDB using BACKUP TABLE syntax. Enables surgical backup strategies for specific datasets with independent schedules and retention policies. Use when user asks to "backup a table", "backup specific tables", "backup schema", "surgical backup", or needs granular control over backup scope.

Inspect CockroachDB backup metadata using SHOW BACKUP command to examine databases, tables, row counts, and sizes. Use when user asks to "check backup contents", "verify backup", "what's in backup", "inspect backup", or needs to validate backup before restore.

Inspect cluster node status and health using SHOW CLUSTER QUERIES, crdb_internal.kv_node_status, and related commands. Monitor node liveness, uptime, build version, and resource utilization across the cluster.

List all available CockroachDB backups in a storage location using SHOW BACKUPS command. Use when user asks "list backups", "show all backups", "available backups", "backup history", or needs to find restore points and manage retention.

Design and implement backup retention policies balancing RPO requirements with storage costs. Use storage lifecycle rules (S3/GCS) or manual cleanup scripts. Implement graduated retention strategies (daily 30d, weekly 90d, monthly 1yr). Ensure retention exceeds recovery window and meets compliance requirements.

Manage backup schedule lifecycle with PAUSE, RESUME, and DROP SCHEDULES commands. Use when user asks to "pause backups", "stop schedule", "resume backups", "delete schedule", or needs to manage automated backup schedules for maintenance or decommissioning.

Modify existing backup schedule frequencies using ALTER BACKUP SCHEDULE. Change full backup intervals with SET FULL BACKUP or incremental intervals with SET RECURRING. Supports CRON expressions and shortcuts. Find schedule IDs with SHOW SCHEDULES. Use when adjusting RPO requirements, backup windows, or cost optimization.

Monitor admission control queue depths and wait times using DB Console Queues dashboard or crdb_internal.node_metrics. High queuing indicates cluster overload or insufficient capacity. Track queue length and wait duration metrics to detect performance bottlenecks.

Monitor background operations using SHOW JOBS to track BACKUP, RESTORE, IMPORT, CHANGEFEED, CREATE STATISTICS, and schema changes. Filter by status, type, and time. Monitor progress and troubleshoot failures.

Monitor backup job status, progress, and history through the CockroachDB Cloud Console. Use when tracking backup execution, troubleshooting failed backups, validating backup schedules, or investigating backup performance issues.

Check certificate expiration using cockroach cert list. Monitor node, client, and CA certificates. Alert when certificates expire within 30 days. Automate rotation procedures.

Monitor and track changefeed job status, health, and progress using SQL commands and DB Console

Monitor changefeed backlog and lag using max_behind_nanos metric, query crdb_internal.jobs for details, track emitted messages/bytes, and correlate lag with cluster load

Access and interpret changefeed metrics in DB Console dashboard. Monitor emitted messages, bytes, backfill progress, lag (max behind nanos), and per-changefeed performance to identify throughput issues and correlate with SQL queries.

Monitor changefeed health using SHOW CHANGEFEED JOBS, DB Console metrics, high water mark lag, and sink-specific indicators

Monitor clock offset across cluster nodes using crdb_internal.node_metrics clock-offset.meannanos metric. Alert when offset exceeds 250ms (warning) or 500ms (fatal crash threshold). Critical for transaction timestamp ordering and HLC correctness. Use DB Console Hardware dashboard or SQL queries. Correlate with transaction errors, serialization failures, or "timestamp in future" issues.