Index working-set preflight

PostgreSQL Index Build & Reindex Space Planner

Budget new index files, simultaneous work, temporary build space, and writes that continue while an index is being created or replaced.

Decision guide

Count the new index before expecting the old index to disappear

What this tool helps you decide

This planner checks whether a PostgreSQL CREATE INDEX, REINDEX, or concurrent index operation has enough additional filesystem space. It reports total new index output for the selected simultaneous count, the peak additional working set after temporary files and concurrent writes, usable free disk after reserve, headroom, and minimum current free disk. The output is a storage preflight; it does not choose an index or claim that an index will improve a query.

Before you start

Estimate the finished size of each new index from a representative environment, a previous build, or measured source relation and index evidence. If indexes differ materially, run them separately or enter a conservative average and count. Record temporary sort and build files from observed work or allocate an explicit allowance. Measure write growth during the expected maintenance window. Confirm where the index and temporary files are written, because free capacity on another mount does not protect the target tablespace.

How the calculation works

New output equals expected output per index multiplied by indexes built at the same time. Peak additional need adds that output, temporary work, and concurrent write growth. Existing indexes are already consuming the filesystem and therefore are not deducted again from the current free-space input. Usable free disk applies the selected reserve. Headroom is usable free disk minus peak additional need. Minimum current free disk divides the need by one minus the reserve fraction.

How to interpret the results

A pass means the explicit byte allowances fit the measured free-space boundary. It does not prove that the operation can acquire locks, complete before a deployment window, or avoid invalid indexes after interruption. Concurrent operations reduce write blocking but PostgreSQL documents additional scans, waits, and failure artifacts. Small headroom should prompt a staged plan with one index at a time, more free disk, or better measurement. A failure is a capacity stop, not a reason to remove an existing index without a recovery review.

Worked example

One concurrent replacement is expected to produce a 180 GB index. The operator reserves 60 GB for temporary build work and 20 GB for writes during the operation, so the additional need is 260 GB. A filesystem with 400 GB free and a 15% reserve supplies 340 GB of usable free space. The plan passes with 80 GB headroom. Reversing the reserve shows that at least about 305.88 GB must be free when the operation starts.

Assumptions and limitations

The equation intentionally treats all entered new outputs and temporary files as overlapping at peak. Actual PostgreSQL index methods, parallel workers, maintenance memory, tablespaces, partitioning, write volume, and version-specific implementation can change the observed peak. REINDEX CONCURRENTLY can leave transient invalid indexes after failure, and CREATE INDEX CONCURRENTLY performs more work than a standard build. This tool does not inspect catalog state, predict output from row count, estimate time or I/O, validate uniqueness, or model WAL and replicas.

Next steps

Review the current CREATE INDEX and REINDEX documentation. Capture the expected and actual index sizes after a representative build, verify invalid-index cleanup and cancellation behavior, and alert before free space reaches the selected reserve. Use the WAL Retention & Replication Slot Planner if the maintenance window can increase consumer lag, and the VACUUM FULL checker for a complete relation rewrite.