Post-mortem: a CREATE INDEX CONCURRENTLY that filled up disk space in production

The context

A consent table with around 16 million rows needed a new index to speed up queries that were getting slower and slower in production. To avoid a blocking lock on the table during creation (a standard CREATE INDEX takes an exclusive lock that blocks writes — and often reads — on the table for the whole duration of the build), the natural choice was:

Sql

CREATE INDEX CONCURRENTLY idx_consentement_xxx ON consentement (colonne);

CONCURRENTLY avoids locking writes while the index is being built — in theory, the safest option in production. The trade-off: the operation takes longer, since PostgreSQL has to scan the table multiple times without ever holding a lock that would block writes.

What went wrong

On a table of this size, building the index consumed a significant amount of temporary disk space — PostgreSQL builds a new index by scanning the table, sorting the data, and writing intermediate results to disk before assembling the final index structure — to the point of saturating the space available on the server. PostgreSQL then started rejecting writes for lack of space.

Diagnosis

First reflexes when PostgreSQL disk space is saturated:

Sql

SELECT pg_size_pretty(pg_database_size('nom_db'));

Then identifying large objects and any indexes currently being built:

Sql

SELECT * FROM pg_stat_progress_create_index;

This system view shows the real-time progress of a CREATE INDEX CONCURRENTLY and the current phase (scan, sort, build — respectively: reading the table, ordering the index keys, and writing the final index structure), which helps determine whether the operation is actually progressing or stuck.

The main lesson

On a large table, CREATE INDEX CONCURRENTLY can require a substantial amount of temporary disk space, potentially on the same order of magnitude as the table itself, depending on the nature of the data and the sorting required. This isn't something intuitive until you've actually experienced it.

How to avoid it next time

Check available disk space before any operation of this kind on a large table, with a comfortable margin
Monitor pg_stat_progress_create_index during execution instead of launching the command and waiting passively
Schedule it during a low-load window, even with CONCURRENTLY, to limit side effects
Have a monitoring alert on remaining disk space, not just on CPU/memory load

In summary

CONCURRENTLY protects against blocking locks, not against resource consumption. On tables with several million rows, temporary disk space deserves to be checked beforehand, just like any other high-volume operation.