Cambios de índices en PostgreSQL sin tiempo de inactividad: una guía segura

Por qué los cambios de índice causan tiempo de inactividad (y cómo evitarlo)

El trabajo con índices parece inofensivo. "Solo" estás añadiendo una estructura de ayuda. En PostgreSQL, sin embargo, crear, borrar o intercambiar un índice puede requerir locks que bloqueen otras sesiones. Si la tabla está ocupada, esas esperas se acumulan y la aplicación empieza a parecer rota.

El tiempo de inactividad rara vez se ve como un banner de corte limpio. A menudo aparece como páginas que se cuelgan, trabajos en segundo plano que se quedan atrasados y una cola creciente de peticiones esperando en la base de datos. Alguien pulsa "Buscar" y recibe un timeout, mientras las herramientas de soporte y las pantallas de administración se vuelven lentas porque consultas simples no pueden obtener el lock que necesitan.

"Solo ejecútalo de noche" falla por dos razones comunes. Muchos sistemas nunca están realmente tranquilos (usuarios globales, jobs por lotes, ETL, backups). Y las operaciones de índice pueden durar más de lo que esperas porque leen muchos datos y compiten por CPU y disco. Si la ventana se cierra a mitad de la construcción, te quedas eligiendo entre esperar más tiempo o interrumpir el trabajo.

Los cambios de índices sin tiempo de inactividad no son magia. Se basan en elegir la operación que menos bloquee, poner medidas de seguridad (timeouts y comprobaciones de disco) y vigilar la base de datos mientras corre.

Esta guía se centra en hábitos prácticos para producción:

• Prefiere las construcciones concurrentes cuando las lecturas y escrituras deben seguir fluyendo.
• Monitoriza locks y progreso de la construcción para poder reaccionar temprano.
• Ten un camino de reversión si el cambio provoca regresiones o tarda demasiado.

Lo que no cubre: teoría profunda de diseño de índices, ajuste amplio de consultas o refactors de esquema que reescriben muchos datos.

El modelo simple de locks detrás del trabajo con índices

PostgreSQL usa locks para mantener los datos correctos cuando muchas sesiones tocan la misma tabla. Un lock es solo una regla que indica quién puede leer o escribir un objeto ahora mismo y quién debe esperar.

La mayor parte del tiempo no notas los locks porque PostgreSQL puede usar modos ligeros que permiten que las consultas normales sigan ejecutándose. El DDL es diferente. Cuando creas o borras un índice, PostgreSQL necesita control sobre la tabla para mantener consistente el catálogo y los datos. Cuanto más control necesita, más otras sesiones pueden verse forzadas a esperar.

Construir un índice vs usar un índice

Usar un índice suele ser barato desde el punto de vista de los locks. SELECT, UPDATE y DELETE pueden leer o mantener índices mientras otras sesiones hacen lo mismo.

Construir un índice es distinto. PostgreSQL debe escanear la tabla, ordenar o hacer hash de claves y escribir una nueva estructura en disco. Ese trabajo lleva tiempo, y el tiempo es lo que convierte los "pequeños locks" en "grandes problemas" en producción.

Qué cambia CONCURRENTLY (y qué no cambia)

Un CREATE INDEX normal toma un lock fuerte que bloquea escrituras durante toda la construcción. CREATE INDEX CONCURRENTLY está diseñado para mantener las lecturas y escrituras normales en movimiento mientras se construye el índice.

Pero "concurrente" no significa "sin locks". Aún hay ventanas de bloqueo cortas al inicio y al final, y la construcción puede fallar o esperar si algo más mantiene locks incompatibles.

Los resultados que más importan:

• Las construcciones no concurrentes pueden bloquear inserts, updates y deletes en la tabla.
• Las construcciones concurrentes suelen permitir lecturas y escrituras, pero pueden ralentizarse o quedarse paradas por transacciones largas.
• Los pasos finales aún necesitan locks breves, así que sistemas muy ocupados pueden experimentar esperas cortas.

Elige el enfoque adecuado: concurrente o normal

Tienes dos opciones principales al cambiar índices: construir el índice normalmente (rápido, pero bloqueante) o construirlo con CONCURRENTLY (normalmente no bloqueante para el tráfico de la aplicación, pero más lento y más sensible a transacciones largas).

Cuándo CONCURRENTLY es la opción correcta

Usa CREATE INDEX CONCURRENTLY cuando la tabla sirve tráfico real y no puedes pausar las escrituras. Es típicamente la opción más segura cuando:

• La tabla es lo bastante grande como para que una construcción normal pueda durar minutos u horas.
• La tabla tiene escrituras constantes, no solo lecturas.
• No puedes programar una ventana de mantenimiento real.
• Necesitas construir primero, verificar y luego borrar un índice antiguo más tarde.

Cuándo es aceptable una construcción normal

Un CREATE INDEX normal puede estar bien cuando la tabla es pequeña, el tráfico es bajo o tienes una ventana controlada. A menudo termina más rápido y es más simple de ejecutar.

Considera el enfoque normal si la construcción es consistentemente rápida en staging y puedes detener las escrituras temporalmente (aunque sea brevemente).

Si necesitas unicidad, decide temprano. CREATE UNIQUE INDEX CONCURRENTLY funciona, pero fallará si existen valores duplicados. En muchos sistemas de producción, encontrar y arreglar duplicados es el verdadero proyecto.

Comprobaciones previas antes de tocar producción

La mayoría de los problemas ocurren antes de que el comando siquiera empiece. Unas pocas comprobaciones te ayudan a evitar dos grandes sorpresas: bloqueos inesperados y una construcción de índice que dura mucho más (o usa más espacio) del planeado.

1. Asegúrate de no estar dentro de una transacción. CREATE INDEX CONCURRENTLY fallará si lo ejecutas después de BEGIN, y algunas herramientas gráficas envuelven en transacciones sin avisar. Si no estás seguro, abre una sesión nueva y ejecuta solo el comando del índice allí.

2. Define expectativas de tiempo y disco. Las construcciones concurrentes suelen tardar más que las normales y necesitan espacio de trabajo extra mientras se ejecutan. Planifica para el nuevo índice más la sobrecarga temporal y confirma que tienes margen libre en disco.

3. Ajusta timeouts que coincidan con tu objetivo. Quieres que la construcción falle rápido si no puede obtener un lock, pero no quieres que la sesión muera a mitad de la construcción por un statement_timeout agresivo.

4. Captura una línea base. Quieres pruebas de que el cambio ayudó y una forma rápida de detectar regresiones. Registra un snapshot previo: tiempos de consultas lentas, un EXPLAIN (ANALYZE, BUFFERS) representativo y una visión rápida de CPU, IO, conexiones y espacio libre en disco.

Ajustes de sesión seguros que muchos equipos usan como punto de partida (ajusta según tus reglas):

-- Run in the same session that will build the index
SET lock_timeout = '2s';
SET statement_timeout = '0';

Paso a paso: crear un índice con CONCURRENTLY

Usa CREATE INDEX CONCURRENTLY cuando necesitas que el tráfico de la aplicación siga moviéndose y puedes permitirte un tiempo de construcción mayor.

Primero, decide exactamente qué vas a construir:

• Sé específico sobre el orden de columnas (importa).
• Considera si un índice parcial es suficiente. Si la mayoría de consultas filtran por filas "activas", un índice parcial puede ser más pequeño, más rápido y más barato de mantener.

Una ejecución segura se ve así: escribe el objetivo y el nombre del índice, ejecuta la construcción fuera de cualquier bloque de transacción, vigílala hasta que termine y luego verifica que el planner puede usarla antes de eliminar cualquier cosa.

-- Example: speed up searches by email for active users
CREATE INDEX CONCURRENTLY idx_users_active_email
ON public.users (email)
WHERE status = 'active';

-- Validate it exists
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'users';

-- Check the plan can use it
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM public.users
WHERE status = 'active' AND email = '[email protected]';

Para notas de progreso (útiles en auditorías), registra hora de inicio, hora de fin y cualquier espera observada. Mientras corre, puedes consultar pg_stat_progress_create_index desde otra sesión.

La validación no es solo "el índice existe." Confirma que el planner puede elegirlo y luego observa los tiempos reales de consulta tras el despliegue. Si el nuevo índice no se usa, no te apresures a eliminar el antiguo: arregla la consulta o la definición del índice primero.

Paso a paso: reemplazar o eliminar índices sin bloquear

El patrón más seguro es añadir primero, dejar que el tráfico se beneficie del nuevo índice y solo entonces eliminar el antiguo. Así mantienes una alternativa funcional.

Intercambiar un índice antiguo por uno nuevo (orden seguro)

1. Crea el nuevo índice con CREATE INDEX CONCURRENTLY.

2. Verifica que se está usando. Revisa EXPLAIN en las consultas lentas que te importan y observa el uso del índice con el tiempo.

3. Solo entonces, elimina el índice antiguo concurrentemente. Si el riesgo es alto, mantén ambos índices durante un ciclo de negocio completo antes de borrar nada.

Eliminar índices: cuándo funciona CONCURRENTLY (y cuándo no)

Para un índice normal que creaste tú mismo, DROP INDEX CONCURRENTLY suele ser la opción correcta. Dos advertencias: no puede ejecutarse dentro de un bloque de transacción y aún necesita locks breves al inicio y al final, por lo que puede retrasarse por transacciones largas.

Si el índice existe por una PRIMARY KEY o una restricción UNIQUE, normalmente no puedes borrarlo directamente. Debes cambiar la restricción con ALTER TABLE, lo que puede tomar locks más fuertes. Trata eso como una operación de mantenimiento planificada aparte.

Renombrar índices para mayor claridad

Renombrar (ALTER INDEX ... RENAME TO ...) suele ser rápido, pero evítalo si herramientas o migraciones hacen referencia a nombres de índices. Una práctica más segura es escoger un nombre claro desde el inicio.

Si el índice antiguo todavía es necesario

A veces dos patrones de consulta necesitan dos índices diferentes. Si consultas importantes aún dependen del antiguo, mantén ese índice. Considera ajustar el nuevo índice (orden de columnas, condición parcial) en lugar de forzar una eliminación.

Monitoriza locks y progreso mientras se construye el índice

Incluso con CREATE INDEX CONCURRENTLY, debes vigilar lo que ocurre en tiempo real. La mayoría de incidentes sorpresa vienen de una de dos cosas: una sesión bloqueadora que no notaste o una transacción prolongada que mantiene la construcción esperando.

Detectar sesiones bloqueadas (quién bloquea a quién)

Empieza por encontrar sesiones esperando locks:

SELECT
  a.pid,
  a.usename,
  a.application_name,
  a.state,
  a.wait_event_type,
  a.wait_event,
  now() - a.xact_start AS xact_age,
  left(a.query, 120) AS query
FROM pg_stat_activity a
WHERE a.wait_event_type = 'Lock'
ORDER BY xact_age DESC;

Si necesitas al bloqueador exacto, sigue blocked_pid a blocking_pid:

SELECT
  blocked.pid  AS blocked_pid,
  blocking.pid AS blocking_pid,
  now() - blocked.xact_start AS blocked_xact_age,
  left(blocked.query, 80)  AS blocked_query,
  left(blocking.query, 80) AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY (pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

Vigilar el progreso de la construcción y señales de "atasco"

PostgreSQL expone el progreso de la construcción de índices. Si no ves movimiento por mucho tiempo, busca una transacción larga (a menudo una sesión idle que mantiene un snapshot antiguo).

SELECT
  pid,
  phase,
  lockers_total,
  lockers_done,
  blocks_total,
  blocks_done,
  tuples_total,
  tuples_done
FROM pg_stat_progress_create_index;

También vigila la presión del sistema: IO de disco, lag de réplica y tiempos de consulta crecientes. Las construcciones concurrentes son más amables con la disponibilidad, pero aún leen muchos datos.

Reglas simples que funcionan bien en producción:

• Espera si el progreso avanza y el impacto en usuarios es bajo.
• Cancela y reprograma si la construcción está atrapada por una transacción larga que no puedes finalizar de forma segura.
• Pausa durante picos de tráfico si el IO perjudica consultas orientadas al cliente.
• Termina solo como último recurso y solo después de confirmar lo que hace la sesión.

Para la comunicación del equipo, mantén las actualizaciones cortas: hora de inicio, fase actual, qué está bloqueando (si hay algo) y cuándo revisarás de nuevo.

Plan de reversión: cómo dar marcha atrás de forma segura

Los cambios de índice solo siguen siendo de bajo riesgo si planeas la salida antes de empezar. La reversión más segura suele no ser un deshacer dramático. Es simplemente detener el trabajo nuevo y mantener el índice antiguo en su lugar.

Formas comunes en que falla el trabajo con índices

La mayoría de fallos en producción son previsibles: la construcción alcanza un timeout, alguien la cancela durante un incidente, el servidor se queda sin disco o la construcción compite con el tráfico normal hasta que la latencia visible para usuarios sube.

Con CREATE INDEX CONCURRENTLY, cancelar suele ser seguro para la app porque las consultas siguen funcionando. El intercambio es la limpieza: una construcción concurrente cancelada o fallida puede dejar un índice inválido.

Reglas seguras para cancelar y limpiar

Cancelar una construcción concurrente no revierte como una transacción normal. PostgreSQL puede dejar un índice que existe pero no es válido para el planner.

-- Cancel the session building the index (use the PID you identified)
SELECT pg_cancel_backend(\u003cpid\u003e);

-- If the index exists but is invalid, remove it without blocking writes
DROP INDEX CONCURRENTLY IF EXISTS your_index_name;

Antes de borrar, confirma lo que estás viendo:

SELECT
  c.relname AS index_name,
  i.indisvalid,
  i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = 'your_index_name';

Si indisvalid = false, no se está usando y es seguro eliminarlo.

Lista práctica de comprobaciones para rollback al reemplazar un índice existente:

• Mantén el índice antiguo hasta que el nuevo esté completamente construido y sea válido.
• Si la construcción nueva falla o se cancela, elimina el índice inválido concurrentemente.
• Si ya eliminaste el índice antiguo, recréalo con CREATE INDEX CONCURRENTLY para restaurar el estado previo.
• Si la falla fue por presión de disco, libera espacio primero y luego reintenta.
• Si los timeouts causaron la falla, programa una ventana más tranquila en lugar de forzarla.

Ejemplo: empiezas un nuevo índice para una búsqueda de administración, corre 20 minutos y luego saltan alertas de disco. Cancelas la construcción, eliminas el índice inválido concurrentemente y mantienes el antiguo sirviendo tráfico. Puedes intentarlo de nuevo tras liberar espacio, sin un outage visible para usuario.

Errores comunes que generan outages sorpresa

La mayoría de los outages alrededor de índices no son culpa de que PostgreSQL sea “lento”. Ocurren porque un pequeño detalle convierte un cambio seguro en uno bloqueante.

1) Poner una construcción concurrente dentro de una transacción

CREATE INDEX CONCURRENTLY no puede ejecutarse dentro de un bloque de transacción. Muchas herramientas de migración envuelven cada cambio en una transacción por defecto. El resultado es un error contundente (mejor caso) o un despliegue desordenado con reintentos.

Antes de ejecutar la migración, confirma que tu herramienta puede ejecutar una sentencia sin una transacción externa, o separa la migración en un paso no transaccional especial.

2) Lanzarlo en un pico de tráfico

Las construcciones concurrentes reducen el bloqueo, pero aún añaden carga: lecturas extra, escrituras extra y más presión sobre autovacuum. Iniciar la construcción durante una ventana de deploy cuando el tráfico está subiendo es una forma común de crear una ralentización que se siente como un outage.

Elige un periodo tranquilo y trátalo como cualquier otro mantenimiento en producción.

3) Ignorar transacciones largas

Una sola transacción larga puede atrasar la fase de limpieza de una construcción concurrente. El índice puede parecer progresar y luego quedarse casi al final esperando a que desaparezcan snapshots antiguos.

Adopta la rutina: busca transacciones largas antes de empezar y otra vez si el progreso se atasca.

4) Borrar lo equivocado (o romper una restricción)

Los equipos a veces borran un índice por nombre de memoria o eliminan un índice que respalda una regla de unicidad. Si borras el objeto equivocado, puedes perder enforcement (constraints) o degradar el rendimiento de consultas al instante.

Lista de seguridad rápida: verifica el nombre del índice en el catálogo, confirma si soporta una restricción, revisa esquema y tabla y separa "crear nuevo" de "borrar viejo". Ten el comando de rollback listo antes de empezar.

Ejemplo realista: acelerar una búsqueda administrativa

Un punto de dolor común es una búsqueda administrativa que es instantánea en staging pero lenta en producción. Imagina una gran tabla tickets (decenas de millones de filas) detrás de un panel interno, y los agentes buscan a menudo "tickets abiertos de un cliente, los más recientes primero."

La consulta es:

SELECT id, customer_id, subject, created_at
FROM tickets
WHERE customer_id = $1
  AND status = 'open'
ORDER BY created_at DESC
LIMIT 50;

Un índice completo en (customer_id, status, created_at) ayuda, pero añade sobrecarga de escritura para cada actualización de ticket, incluso cuando se cierran. Si la mayoría de filas no son open, un índice parcial suele ser una victoria más simple:

CREATE INDEX CONCURRENTLY tickets_open_by_customer_created_idx
ON tickets (customer_id, created_at DESC)
WHERE status = 'open';

Cronología segura en producción:

• Preflight: confirma que la forma de la consulta es estable y que la tabla tiene disco libre suficiente para construir un nuevo índice.
• Construcción: ejecuta CREATE INDEX CONCURRENTLY en una sesión separada con timeouts claros.
• Validación: ejecuta ANALYZE tickets; y confirma que el planner usa el nuevo índice.
• Limpieza: cuando estés seguro, elimina cualquier índice redundante con DROP INDEX CONCURRENTLY.

Éxito se ve así:

• La búsqueda administrativa baja de segundos a decenas de milisegundos para clientes típicos.
• Lecturas y escrituras normales siguen funcionando durante la construcción.
• CPU e IO de disco suben durante la construcción pero se mantienen dentro de límites seguros.
• Puedes mostrar números antes/después claros: tiempo de consulta, filas escaneadas e historial de locks.

Lista rápida y siguientes pasos

El trabajo con índices es más seguro si lo tratas como un pequeño lanzamiento en producción: prepáralo, vigílalo mientras corre y verifica el resultado antes de limpiar.

Antes de empezar:

• Ajusta timeouts para que un bloqueo sorpresa no cuelgue para siempre.
• Confirma espacio libre en disco suficiente para la nueva construcción.
• Busca transacciones largas que puedan ralentizar la construcción.
• Elige una ventana de bajo tráfico y define qué significa "hecho".
• Escribe tu plan de reversión ahora.

Mientras corre:

• Vigila bloqueos y cadenas de espera.
• Sigue el progreso con pg_stat_progress_create_index.
• Observa síntomas en la app: tasa de errores, timeouts y endpoints lentos relacionados con la tabla.
• Estate listo para cancelar si aumentan las esperas por locks o los timeouts visibles para usuarios.
• Registra lo ocurrido: hora de inicio, hora de fin y alertas.

Después de terminar, confirma que el índice es válido, ejecuta las una o dos consultas clave para ver mejora en plan y tiempos, y solo entonces elimina índices antiguos de forma no bloqueante.

Si haces esto más de una vez, conviértelo en un paso de entrega repetible: un pequeño runbook, un ensayo en staging con datos parecidos a producción y un responsable claro vigilando la construcción.

Si estás construyendo herramientas internas o paneles de administración con AppMaster (appmaster.io), ayuda tratar los cambios en la base de datos como las construcciones de índices: parte de la misma lista de verificación de release que tus actualizaciones de backend: medido, monitorizado y con una reversión que puedas ejecutar rápidamente.