skills.rest
cockroachdb
cockroachdbOFFICIAL
Explore all skills by this author →
WebsiteGitHub

analyzing-schema-change-storage-risk

Estimates storage headroom for CockroachDB online schema change backfills using SHOW RANGES.

2|1|Updated Mar 11, 2026
Search Tags:#sql#schema change#capacity planning#backfill#cockroachdb#storage estimation#show ranges
One-click install
npx skills add https://github.com/cockroachdb/cursor-plugin --skill analyzing-schema-change-storage-risk-cockroachdb
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: analyzing-schema-change-storage-risk
Source: https://github.com/cockroachdb/cursor-plugin/tree/main/skills/cockroachdb-observability-and-diagnostics/analyzing-schema-change-storage-risk
Command: npx skills add https://github.com/cockroachdb/cursor-plugin --skill analyzing-schema-change-storage-risk-cockroachdb

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve? Online schema changes in CockroachDB (like CREATE INDEX or ALTER PRIMARY KEY) trigger backfills that can temporarily consume up to 3x the size of the affected table or index, risking mid-backfill disk exhaustion and paused schema change jobs. ## Core Features & Use Cases - Per-Index Size Estimation: Aggregates SHOW RANGES WITH DETAILS, KEYS, INDEXES output by index to compute per-index storage footprints in MB/GB. - Operation-Specific Capacity Targets: Maps each DDL operation (CREATE INDEX, ALTER PRIMARY KEY, CREATE MATERIALIZED VIEW, etc.) to a conservative free-space target per store. - Failure Recovery Guidance: Explains how InsufficientSpaceError pauses schema change jobs and how to detect and resume paused jobs via SHOW JOBS. - Use Case: Before running ALTER PRIMARY KEY on a multi-terabyte table, estimate the table's total index footprint and compare it against the smallest per-store free capacity to confirm the change will not exhaust disk. ## Quick Start Ask the AI to estimate whether there is enough free disk space to safely run CREATE INDEX on a specific large CockroachDB table.

Frequently Asked Questions about analyzing-schema-change-storage-risk

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I estimate storage needed for a CockroachDB schema change?▼

Run SHOW RANGES FROM TABLE with DETAILS, KEYS, INDEXES and aggregate range_size_mb by index_name to get per-index sizes. Compare the result against the smallest per-store free capacity, planning for up to 3x the affected table or index size as free space.

How much disk space does CREATE INDEX need in CockroachDB?▼

CREATE INDEX needs roughly 1x the size of the new index (indexed columns plus primary key columns), with official guidance recommending up to 3x as a conservative bound. ALTER PRIMARY KEY is heavier, potentially approaching the full table size during the rewrite.

What privileges are required to run SHOW RANGES WITH DETAILS?▼

SHOW RANGES WITH DETAILS requires the ZONECONFIG system privilege or the admin role. Granting ZONECONFIG is recommended for read-only range analysis since it avoids giving full admin write access.

What happens if CockroachDB runs out of disk during a backfill?▼

The bulk ingest checks remaining store capacity and rejects writes below the min_capacity_remaining_fraction threshold (default 5%), raising InsufficientSpaceError. The schema change job pauses rather than wedging; free disk space and resume the paused job to continue.

When is storage estimation unnecessary before a schema change?▼

Estimation is unnecessary for tables whose indexes are small, in the kilobyte to few-megabyte range, since the storage risk is trivial. It matters for tables with multiple GB per index or many ranges per index.

Why is SHOW RANGES WITH DETAILS slow on large tables?▼

SHOW RANGES WITH DETAILS computes span statistics on demand, which is expensive on tables with many ranges. Always target a specific table rather than running cluster-wide, and prefer maintenance windows for tables with thousands of ranges.

Related Skills

Explore More Software Engineering Skills→
cockroachdbcockroachdb
3

hardening-user-privileges

Audit CockroachDB users and role grants to reduce excessive privileges.

Official
Advanced
Rust-sohamRust-soham

golem-interrupt-resume-agent

Interrupt and resume Golem agents while preserving their state.

Community
Basic
cockroachlabscockroachlabs
18

analyzing-range-distribution

Diagnose range distribution and fragmentation in CockroachDB using SHOW RANGES queries.

Official
Advanced
cockroachdbcockroachdb
2

analyzing-schema-change-storage-risk

Estimates storage headroom for CockroachDB online schema change backfills using SHOW RANGES.

Official
Intermediate