CREATE INDEX CONCURRENTLY: indexar sin downtime en PostgreSQL

Un CREATE INDEX normal toma un bloqueo que impide escribir en la tabla durante toda la construccion. En una tabla diminuta no se nota; en una tabla caliente con millones de filas, congelar cada INSERT y UPDATE durante minutos es un incidente. CREATE INDEX CONCURRENTLY construye el indice sin bloquear las escrituras, y es el caballo de batalla de las migraciones sin downtime.

Que bloquea un CREATE INDEX normal

Un CREATE INDEX normal toma un bloqueo SHARE sobre la tabla: las lecturas siguen fluyendo, pero cualquier escritura se encola hasta que termina la construccion.

-- locks out writes to orders until the index is built
CREATE INDEX idx_orders_user_id ON orders (user_id);

En una tabla orders con decenas de millones de filas la construccion tarda minutos, y la aplicacion no puede insertar pedidos en todo ese tiempo. La variante con CONCURRENTLY soluciona justo eso:

CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);

Postgres hace dos pasadas sobre la tabla y espera a las transacciones en vuelo, asi que la construccion es mas lenta y exige mas al disco, pero las escrituras nunca se detienen. La sintaxis es por lo demas identica, incluidos los indices compuestos:

CREATE INDEX CONCURRENTLY idx_orders_user_status
    ON orders (user_id, status);

Por que no funciona dentro de una transaccion

CREATE INDEX CONCURRENTLY no puede ejecutarse dentro de un bloque de transaccion. La orden gestiona varias transacciones internas y espera a otras sesiones, asi que envolverla en BEGIN ... COMMIT es un error:

BEGIN;
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);  -- ERROR
COMMIT;

La consecuencia practica para las migraciones: esta sentencia debe ejecutarse fuera de una transaccion. Muchas herramientas de migracion envuelven cada paso en una transaccion por defecto, asi que para un paso con CONCURRENTLY hay que desactivarlo (disable_ddl_transaction! en Rails, atomic = False en Django, un paso no transaccional aparte en otras).

• Un indice por paso de migracion.
• No mezcles CONCURRENTLY con otro DDL en el mismo paso.
• Reserva tiempo: en tablas grandes son decenas de minutos.

El indice INVALID que deja un fallo

Esta es la parte mas traicionera. Si una construccion CONCURRENTLY falla (filas duplicadas en un indice UNIQUE, una cancelacion, una conexion caida), el indice no desaparece; queda en el catalogo marcado como INVALID. El planificador lo ignora, pero aun asi ralentiza las escrituras, ocupa espacio y bloquea volver a crear un indice con el mismo nombre.

Para encontrarlos:

SELECT indexrelid::regclass AS index_name
FROM pg_index
WHERE indisvalid = false;

Tambien ayuda vigilar el progreso de una construccion en curso:

SELECT phase, blocks_done, blocks_total
FROM pg_stat_progress_create_index;

Arreglar un indice INVALID es sencillo: borralo y reconstruyelo.

DROP INDEX CONCURRENTLY IF EXISTS idx_orders_user_id;
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);

Trampa: no "repares" a ciegas un indice INVALID con REINDEX. Antes de Postgres 12 un REINDEX sobre un indice normal toma un bloqueo pesado; desde la 12 existe REINDEX INDEX CONCURRENTLY, pensado precisamente para una reconstruccion sin downtime.

DROP INDEX CONCURRENTLY e idempotencia

Borrar un indice tambien toma un bloqueo: un DROP INDEX normal sobre una tabla caliente congela lecturas y escrituras durante el momento del borrado. DROP INDEX CONCURRENTLY elimina ese bloqueo igual que lo hace la construccion:

DROP INDEX CONCURRENTLY IF EXISTS idx_orders_user_status;

Tiene las mismas restricciones: nada de bloque de transaccion, y no puedes borrar varios indices en una sola orden. Pero IF EXISTS hace el paso idempotente, asi que una reejecucion no fallara.

El patron seguro completo para "recrear un indice sin downtime":

DROP INDEX CONCURRENTLY IF EXISTS idx_employees_dept;
CREATE INDEX CONCURRENTLY idx_employees_dept ON employees (dept, salary);

En que se diferencian otros motores

• MySQL/InnoDB: no existe la palabra clave CONCURRENTLY, pero el DDL online esta integrado. CREATE INDEX ... ALGORITHM=INPLACE, LOCK=NONE (o ALTER TABLE ... ADD INDEX) suele construir el indice mientras las escrituras continuan. El motor decide si LOCK=NONE es posible y da error si no lo es.
• ClickHouse: los indices secundarios de salto se agregan con ALTER TABLE ... ADD INDEX, que es barato, pero el indice solo se aplica a los datos existentes tras MATERIALIZE INDEX, que hace el trabajo en segundo plano.

Para PostgreSQL la regla es simple: en produccion construye y borra cada indice con CONCURRENTLY, como su propio paso no transaccional, y comprueba siempre pg_index.indisvalid despues de la migracion.