¿Cómo crear o reconstruir índices de PostgreSQL en línea de forma segura?

1. Pregunta

Una tabla de pedidos sigue recibiendo escrituras mientras un equipo de consultas solicita un índice compuesto. Otra tabla tiene un índice que necesita reconstruirse debido al bloat. Las tablas son grandes, la ventana es fuera de horas pico y las lecturas y escrituras ordinarias no pueden bloquearse. Proporcione un plan en línea que sea observable y reversible.

2. Restricciones y aclaraciones

• Confirme la versión de PostgreSQL, los tipos de tabla e índice, la topología de replicación, el margen de espacio en disco y el pico de escritura.
• Distinga entre CREATE INDEX, CREATE INDEX CONCURRENTLY y REINDEX CONCURRENTLY.
• Las construcciones concurrentes reducen el impacto del bloqueo de escritura, pero escanean más de una vez, consumen CPU/IO y no pueden ejecutarse dentro de un bloque de transacción.
• Defina el tiempo de construcción aceptable, la espera de bloqueos, la latencia y la ventana de limpieza antes de la implementación.

3. Idea central

Una construcción de índice normal puede bloquear las escrituras. Una construcción concurrente permite que las inserciones, actualizaciones y eliminaciones continúen, pero tarda más y consume más recursos. Primero estime el costo en un entorno shadow a una escala realista; en producción, configure lock_timeout, limite statement_timeout y monitoree los recursos. Después de la creación, verifique la elección del planificador, las rutas de rollback y la consistencia de las réplicas; un comando DDL exitoso no equivale a un despliegue de negocio exitoso.

4. Flujo de referencia

preflight:
  verify_version_replicas_disk_and_query_shape()
  estimate_scan_cost_on_shadow_copy()
  reserve_maintenance_window_and_abort_thresholds()

build:
  set lock_timeout = short
  set statement_timeout = bounded
  CREATE INDEX CONCURRENTLY idx_orders_customer_time
    ON orders (customer_id, created_at DESC)

verify:
  inspect_index_state_and_size()
  EXPLAIN (ANALYZE, BUFFERS) representative_queries()
  compare_write_latency_replica_lag_and_error_rate()

Prefiera REINDEX CONCURRENTLY para una reconstrucción. Si una falla deja un índice temporal inválido, identifíquelo y límpielo de acuerdo con la documentación antes de volver a intentarlo. Los scripts de despliegue deben hacer que la nomenclatura, la idempotencia y las alertas sean trazables; no oculte un DDL concurrente dentro de una migración transaccional ordinaria.

5. Casos de falla y compensaciones

Una construcción concurrente puede fallar debido a transacciones largas, instantáneas en conflicto o espacio insuficiente en disco. Un objeto fallido puede permanecer inválido y continuar consumiendo espacio. El crecimiento de CPU, IO y WAL durante la construcción puede ralentizar el servicio e incrementar el lag de las réplicas, por lo que debe regularlo o pausarlo. Si el costo es inaceptable, optimice la consulta, particione la tabla o utilice una herramienta de migración en línea, pero aun así verifique sus disparadores, backfill, cutover y comportamiento de rollback.

6. Verificación y observabilidad

• Registre el estado del índice, el tamaño, la duración de la construcción, las esperas de bloqueo, WAL, CPU/IO y el lag de replicación.
• Compare planes, filas escaneadas, latencia p95/p99 y rendimiento de escritura para consultas representativas.
• Verifique transacciones largas, índices inválidos, índices duplicados y dependencias de restricciones.
• Espere a que pasen varios picos completos de tráfico antes de eliminar un índice antiguo y mantenga un script de recuperación.

7. Errores comunes

• Asumir que CONCURRENTLY no toma bloqueos e ignorar las breves esperas de bloqueo y la contención de recursos.
• Colocar CREATE INDEX CONCURRENTLY dentro de un bloque de transacción, lo que hace que falle de inmediato.
• Verificar únicamente el código de retorno del DDL en lugar de revisar los índices inválidos, el lag de las réplicas y el plan de consulta real.
• Reconstruir una tabla enorme sin un presupuesto de disco, WAL y transacciones largas.

8. Puntos de evaluación en entrevistas

Distingue la semántica de DDL concurrente

El candidato explica los bloqueos, las pasadas de escaneo, las restricciones de transacción y los costos de recursos para las operaciones normales y concurrentes de creación o reconstrucción.

Planifica verificaciones previas a producción

El candidato verifica la versión, el disco, las transacciones largas, la topología de replicación y la estructura de la consulta, y luego realiza estimaciones con un volumen de datos realista.

Diseña la recuperación ante fallas

El candidato maneja índices inválidos, tiempos de espera agotados, agotamiento del disco y lag de replicación, con pasos de limpieza, reintento y rollback.

Verifica con métricas de negocio

El candidato compara planes, p95/p99, latencia de escritura, WAL, esperas de bloqueo y lag de replicación en lugar de confiar únicamente en el código de retorno del DDL.