analyzing-schema-change-storage-risk

Analyzing Schema Change Storage Risk

Estimates the storage headroom needed to safely run online schema changes. Mirrors the official guidance: some operations may temporarily require up to 3× the size of the affected table or index while the schema change is in flight.

For ongoing range-distribution monitoring, see analyzing-range-distribution.

When to Use This Skill

Run a quick estimate before issuing any of these operations on a table whose indexes are large (multiple GB per index, or many ranges per index):

• CREATE INDEX
• ADD COLUMN with INDEX or UNIQUE
• ALTER PRIMARY KEY
• CREATE MATERIALIZED VIEW
• CREATE TABLE AS
• REFRESH MATERIALIZED VIEW
• ALTER TABLE ... SET LOCALITY (when the locality change rewrites data)

Tables whose indexes are small (kilobytes to a few megabytes) carry trivial storage risk; estimation is unnecessary.

Background

How much temporary space does a backfill actually need?

The honest answer depends on the operation:

• CREATE INDEX / ADD COLUMN ... UNIQUE: needs roughly 1× the size of the new index — the indexed columns plus the primary key columns, written into a fresh index span. This is typically a small fraction of the table. Worst-case headroom is bounded by the size of that one index.
• ALTER PRIMARY KEY: rewrites the primary index and any secondary indexes whose definitions depend on the old PK. Old data sticks around until GC, so peak on-disk usage during the change can approach the size of the table again.
• All bulk-ingest backfills: extra MVCC versions and pre-compaction SSTs add overhead until Pebble compacts and GC runs.

The official docs round these up into a single conservative recommendation: plan for up to 3× the size of the affected table or index as free space. That figure is a safety bound, not a precise prediction. For most CREATE INDEX operations the real cost is much smaller; for ALTER PRIMARY KEY on a large table it is the right ballpark.