Guía práctica: reconstruir índices en PostgreSQL en producción con CREATE INDEX CONCURRENTLY e índices parciales (paso a paso seguro)

Por Kevin Marcondes · Publicado el 28/10/2026 · 10 min de lectura

Esta guía práctica muestra cuándo aplicar cada técnica y cómo preparar el entorno para reconstruir índices en PostgreSQL en producción usando CREATE INDEX CONCURRENTLY e índices parciales. Incluye prerrequisitos, pasos defensivos, monitorización, rollback y cuidados con UNIQUE/PK y réplicas para reducir bloqueos y evitar regresiones de planes en tablas activas.

Cuándo reconstruir un índice y señales de que vale la pena

Reconstruir índices puede recuperar rendimiento perdido por bloat, corregir definiciones inadecuadas y reducir I/O. En producción, el reto es hacerlo con el menor impacto posible.

Busca estas señales antes de actuar: consultas degradadas, más barridos secuenciales de lo esperado, índices más grandes que la propia tabla o aumento del tiempo de VACUUM.

Si sospechas corrupción o estadísticas dañadas, prioriza la integridad antes del intercambio. Consulta la guía específica de recuperación en Recuperar índices y estadísticas en PostgreSQL.

Los cambios en los filtros a lo largo del tiempo generan índices subaprovechados. Un índice parcial bien diseñado puede reducir bloat y mejorar selectividad. Coordina cambios de esquema y despliegues con tu proceso de migraciones y rollbacks en PostgreSQL.

Para mantener la densidad de palabras clave: reconstruir índices en PostgreSQL en producción usando CREATE INDEX CONCURRENTLY e índices parciales es exactamente lo que aplicarás aquí.

• Las consultas cambiaron y el índice actual ya no cubre los filtros principales.
• Índice creció demasiado tras muchas actualizaciones/eliminaciones (bloat).
• El planner evita el índice por baja selectividad.
• Necesidad de marcar UNIQUE una columna ya poblada.

Cómo CREATE INDEX CONCURRENTLY reduce el impacto (y cuándo no usarlo)

CREATE INDEX CONCURRENTLY (CIC) evita bloquear lecturas y escrituras por largos periodos. Realiza múltiples pasadas sobre la tabla para captar cambios concurrentes y sólo toma locks breves en la fase final.

Limitaciones importantes: CIC es más lento que CREATE INDEX tradicional, exige espacio extra en disco y no puede ejecutarse dentro de una transacción. No lo uses en ventanas muy cortas sin margen, ya que la duración varía con la carga.

Para constraints existentes (PK/UNIQUE) o para reindexar sin cambiar nombres, considera REINDEX CONCURRENTLY. En escenarios con informes intensos y carga mixta, combínalo con prácticas de hardening y tuning de PostgreSQL para ERPs.

• Evita bloqueo prolongado de SELECT/INSERT/UPDATE/DELETE.
• Consume más I/O y tiempo por las múltiples pasadas.
• Requiere espacio adicional, en general similar al tamaño del índice objetivo.
• Las fallas pueden dejar un índice inválido que hay que eliminar.

Checklist pre‑ejecución: seguridad, espacio y concurrencia

Antes de crear un índice concurrente, valida la salud del autovacuum, espacio libre y sesiones largas. CIC no se ejecuta dentro de TRANSACTION y puede generar mucho WAL, aumentando el lag en réplicas.

Define límites de tiempo razonables para tu sesión, prepara la ventana para el intercambio y asegúrate de que no hay migraciones conflictivas pendientes. Consulta también cómo organizar migraciones y rollbacks.

Si la base sufre picos de conexiones, alinea timeouts y la ventana de mantenimiento para evitar impacto durante el rebuild; revisa la guía sobre Too many connections en PostgreSQL.

Usa las consultas de abajo como checklist. Sustituye nombres de tabla/columnas según tu caso.

• Confirma espacio libre para el nuevo índice y WAL adicional.
• Verifica sesiones largas y posibles locks.
• Busca duplicados antes de crear UNIQUE.
• Ajusta maintenance_work_mem para acelerar la construcción.

-- Ajustes temporários de tempo (opcional)
SET lock_timeout = '3s';
SET statement_timeout = '1h';

-- 1) Tamanho da tabela e de índices
SELECT 
  pg_size_pretty(pg_relation_size('public.my_table')) AS table_size,
  pg_size_pretty(pg_indexes_size('public.my_table'))  AS indexes_size;

-- 2) Sessões e transações longas
SELECT pid, usename, xact_start, now()-xact_start AS age, wait_event, query
FROM pg_stat_activity
WHERE state <> 'idle' AND xact_start IS NOT NULL
ORDER BY age DESC;

-- 3) Lag de replicação (se houver standbys)
SELECT application_name, state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication;

-- 4) Procure duplicados antes de UNIQUE
-- Substitua col1, col2 pelas colunas almejadas
SELECT col1, col2, COUNT(*)
FROM public.my_table
GROUP BY col1, col2
HAVING COUNT(*) > 1
LIMIT 50;

Paso a paso: reconstruir un índice existente con downtime imperceptible

Estrategia general: crea un índice nuevo con CREATE INDEX CONCURRENTLY, valida, sustituye el antiguo y, si quieres, renombra el nuevo para mantener la convención. En tablas muy activas, prioriza horarios de menor pico.

Flujo sugerido: crear nuevo índice, comprobar validez, soltar el antiguo con DROP INDEX CONCURRENTLY y renombrar. Evita IF NOT EXISTS con definiciones ambiguas; prefiere nombres explícitos y revisión previa.

Tras el intercambio, monitoriza el uso del nuevo índice y conserva el antiguo un periodo corto sólo si precisas revertir rápidamente. Si jobs concurrentes causan locks, revisa su scheduling según la guía de auditar cron jobs y evitar deadlocks.

Reafirmando la intención: reconstruir índices en PostgreSQL en producción usando CREATE INDEX CONCURRENTLY e índices parciales es la vía para reducir bloqueos manteniendo previsibilidad.

• Nombra el nuevo índice con sufijo _new para mayor claridad.
• Valida indisvalid antes de reemplazar.
• DROP INDEX CONCURRENTLY en el antiguo para evitar bloqueos.
• Renombra el nuevo si el nombre es referenciado por queries o herramientas.

-- 1) Criar o novo índice (definição equivalente ao antigo)
CREATE INDEX CONCURRENTLY idx_my_table_created_at_new
  ON public.my_table (created_at);

-- 2) Verificar que o índice ficou válido
SELECT i.relname, idx.indisvalid
FROM pg_index idx
JOIN pg_class i ON i.oid = idx.indexrelid
WHERE i.relname = 'idx_my_table_created_at_new';

-- 3) Remover o antigo de forma não bloqueante
DROP INDEX CONCURRENTLY IF EXISTS idx_my_table_created_at;

-- 4) (Opcional) Padronizar o nome
ALTER INDEX IF EXISTS idx_my_table_created_at_new
  RENAME TO idx_my_table_created_at;

UNIQUE y PK: intercambio seguro con bloqueos mínimos

Para convertir una columna en UNIQUE sin bloquear, crea el índice único CONCURRENTLY y, a continuación, asócialo como constraint. La adición/remoción de constraints requiere un lock breve en la tabla, así que planifica la ventana.

Si ya existe una PK/UNIQUE problemática, REINDEX CONCURRENTLY puede ser más directo. Si hay que cambiar columnas de la clave, crea un nuevo UNIQUE CONCURRENTLY y cambia la constraint para usar ese índice.

Antes del UNIQUE, elimina duplicados; si existen, el comando fallará y dejará un índice inválido que tendrás que descartar.

• Crea el UNIQUE CONCURRENTLY primero.
• Cambia la constraint para usar el nuevo índice.
• Espera un lock breve al modificar constraints.
• Deduplica antes para evitar fallos.

-- 1) Criar índice único de forma concorrente
CREATE UNIQUE INDEX CONCURRENTLY idx_my_table_email_uq_new
  ON public.my_table (email);

-- 2) Trocar a constraint para usar o novo índice (lock breve)
ALTER TABLE public.my_table
  DROP CONSTRAINT IF EXISTS my_table_email_key,
  ADD CONSTRAINT my_table_email_key
    UNIQUE USING INDEX idx_my_table_email_uq_new;

-- Observação: se a constraint antiga existia, o DROP pode remover
-- o índice antigo automaticamente. Limpeza manual só se necessário.

Índices parciales: cuándo brillan y cómo diseñarlos

Los índices parciales cubren un subconjunto de filas definido por un predicado WHERE. Son excelentes cuando la mayoría de consultas filtran por un estado concreto, reduciendo tamaño y bloat.

Reglas prácticas: la condición del índice debe reflejar el filtro más habitual. El planner sólo usará el índice si el predicado de la consulta implica el del índice. Prefiere expresiones simples y funciones inmutables.

Valida el patrón real de filtros en tu workload. Un índice parcial mal elegido puede convertirse en peso muerto. En entornos de reporting intensivo, combina el diseño del índice con tuning de consultas y hardware — consulta el servicio de hardening y tuning de PostgreSQL para ERPs.

• Úsalos cuando pocos estados son consultados con frecuencia.
• Predicados simples, sobre columnas directas.
• Evita funciones volátiles en el predicado.
• Asegura que las consultas repitan el filtro íntegro.

-- Exemplo: foco em pedidos ativos e não deletados
CREATE INDEX CONCURRENTLY idx_orders_active_created_at
  ON public.orders (created_at)
  WHERE status = 'active' AND deleted_at IS NULL;

-- Padrão de consulta que aproveita o índice
EXPLAIN ANALYZE
SELECT id
FROM public.orders
WHERE status = 'active' AND deleted_at IS NULL
  AND created_at >= now() - interval '7 days';

Monitorización, validación y rollback sin sobresaltos

Monitorea el progreso con la vista de progreso y valida el uso tras el intercambio. Si el comando falla, un índice inválido puede quedarse atrás; límpialo con DROP INDEX CONCURRENTLY.

Confirma que el nuevo índice se está usando con pg_stat_user_indexes y EXPLAIN. Si la carga cambia, reevalúa estadísticas y planes.

En entornos con réplicas, observa el lag durante la operación; los intercambios de índices generan WAL significativo. Si detectas anomalías graves, revisa consistencia y estadísticas según la guía de recuperación de índices y stats.

Para cerrar el ciclo, esta guía prioriza pasos auditables y reversibles al reconstruir índices en PostgreSQL en producción usando CREATE INDEX CONCURRENTLY e índices parciales.

• Sigue pg_stat_progress_create_index.
• Revisa idx_scan después del deploy.
• Elimina índices inválidos que queden.
• Monitorea el lag en réplicas durante la ventana.

-- Progresso da criação do índice
SELECT pid, phase, relid::regclass AS table,
       index_relid::regclass AS index, tuples_total, tuples_done
FROM pg_stat_progress_create_index;

-- Uso de índices após a troca
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE relname = 'my_table'
ORDER BY idx_scan DESC;

-- Gerar comandos para limpar índices inválidos
SELECT 'DROP INDEX CONCURRENTLY ' || quote_ident(relname) || ';' AS drop_cmd
FROM pg_class c
JOIN pg_index i ON i.indexrelid = c.oid
WHERE NOT i.indisvalid AND relkind = 'i';

Preguntas frecuentes

¿Puedo ejecutar CREATE INDEX CONCURRENTLY dentro de una transacción?

No. CREATE INDEX CONCURRENTLY no puede ejecutarse dentro de un bloque de transacción. Abre una sesión dedicada para la operación. Si tu herramienta de migrations inicia transacciones automáticamente, desactívalas para este paso específico.

¿Cuánto espacio extra necesito para reconstruir un índice?

Depende del tamaño del índice objetivo y del churn de datos durante la construcción. Planea al menos espacio equivalente al nuevo índice, además de WAL adicional. En tablas muy activas, la necesidad puede crecer hasta la finalización.

¿Qué pasa si hay duplicados al crear un índice UNIQUE CONCURRENTLY?

El comando falla y deja un índice inválido. Debes identificar y eliminar o consolidar duplicados antes de intentarlo de nuevo. Ejecuta una consulta con GROUP BY y HAVING COUNT(*) > 1 sobre las columnas objetivo para localizar los casos.

¿Funcionan los índices parciales si el WHERE de la consulta no es idéntico?

El planner usará el índice parcial cuando el predicado de la consulta implique el predicado del índice. Si el filtro no equivale lógicamente al del índice, puede ser ignorado. Mantén los filtros consistentes entre consultas e índice.

¿Cuándo elegir REINDEX CONCURRENTLY en lugar de CREATE INDEX CONCURRENTLY?

Cuando necesitas reindexar una constraint existente sin cambiar columnas, REINDEX CONCURRENTLY suele ser más sencillo y mantiene nombres. Si vas a modificar la definición o usar un índice parcial, crea un nuevo índice CONCURRENTLY y luego haz el intercambio.

Ver también

• Guía práctica: configurar failover activo‑pasivo entre dos VPS con Keepalived, HAProxy y lsyncd para uploads sincronizados
• Guía: checklist técnico para preparar un VPS Debian con Docker Compose para aplicaciones PHP en producción
• Guía práctica para dimensionamiento de VPS y tuning de PHP‑FPM para tiendas Magento/OpenMage con picos estacionales: del dimensionamiento a la prueba de carga
• Guía: cómo planificar y ejecutar pruebas de carga en aplicaciones PHP monolito usando k6 y Docker Compose — paso a paso