¿Cómo realizar una migración de esquema en PostgreSQL sin tiempo de inactividad (Zero-Downtime)?

Problema y alcance

Un servicio de usuarios se ejecuta en PostgreSQL y su tabla users contiene 200 millones de filas. username TEXT NOT NULL tiene un índice único. Cada solicitud en línea, trabajo en segundo plano y consumidor de eventos actualmente la lee y escribe. El equipo desea cambiar el nombre del campo a handle; durante la migración, ambas columnas representan exactamente el mismo valor de negocio.

El servicio cuenta con 80 instancias de aplicación y un despliegue progresivo (rolling deployment) tarda 30 minutos. Los workers en segundo plano pueden reiniciarse más tarde que las instancias web. El tráfico normal no se puede pausar y no hay ventana de mantenimiento. Los SLO existentes siguen vigentes: la migración debe ralentizarse o detenerse si el p99 de escritura, las esperas de bloqueo, el retraso de replicación o la carga de la base de datos superan los umbrales de producción. El objetivo es completar el cambio de nombre proporcionando a cada fase un invariante explícito, una compuerta de entrada (entry gate), una compuerta de salida (exit gate) y una ruta de rollback.

Los 200 millones de filas, las 80 instancias y el despliegue de 30 minutos son supuestos de la entrevista. "Zero downtime" significa que no hay interrupciones planificadas mientras se protegen los SLO existentes; no significa ignorar bloqueos cortos, intentos fallidos o la limitación de tasa (throttling). La habilidad principal consiste en dividir un cambio de base de datos en varios lanzamientos compatibles hacia adelante y hacia atrás, por lo que esta es una pregunta de backend. El sharding, la migración entre bases de datos y los conflictos multimaestro quedan fuera del alcance de esta primera ronda.

Qué evalúa el entrevistador

La primera señal es si el candidato reconoce el problema de compatibilidad. Si el equipo ejecuta directamente RENAME COLUMN username TO handle, las instancias antiguas seguirán apuntando a username mientras que las nuevas instancias solo reconocerán handle. Al menos una versión fallará durante la ventana de 30 minutos de versiones mixtas. Una sentencia de base de datos rápida no hace que el despliegue de la aplicación sea sin tiempo de inactividad.

La segunda señal es la separación entre la migración de esquema, la migración de datos y el cutover de código. Un diseño seguro suele seguir el patrón expand-and-contract: agregar una estructura que el código antiguo pueda ignorar, desplegar código compatible, realizar el backfill y validar en lotes, cambiar lecturas y escrituras, y solo entonces eliminar la estructura antigua. Cada paso debe ser desplegable, observable y reintentable de forma independiente. Una actualización de 200 millones de filas y un DDL destructivo no deben coexistir en una sola transacción de despliegue.

La tercera señal es comprender los límites de bloqueos y escaneos en PostgreSQL. Diferentes subcomandos de ALTER TABLE requieren distintos bloqueos, y PostgreSQL adquiere ACCESS EXCLUSIVE a menos que la documentación indique lo contrario. Agregar una columna que admita valores nulos sin valor por defecto no reescribe la tabla, pero aún requiere un bloqueo. Si esa solicitud de bloqueo se encola detrás de una transacción larga, las solicitudes posteriores pueden encolarse detrás de ella. NOT VALID, VALIDATE CONSTRAINT y CREATE INDEX CONCURRENTLY reducen el impacto en las escrituras concurrentes, pero cada uno tiene bloqueos, carga y comportamientos de recuperación ante fallos distintos.

Finalmente, el entrevistador busca compuertas guiadas por invariantes. Una respuesta sólida hace más que enumerar "agregar una columna, escribir en ambas, hacer backfill, eliminar una columna". Explica cuándo cada escritor es compatible, cómo se detectan las escrituras perdidas, por qué el backfill es idempotente, cuándo es seguro leer únicamente la nueva columna, a qué estado puede retroceder cada fase y cuándo la columna antigua deja de ser una ruta de recuperación práctica.

Preguntas de clarificación

• ¿Dónde están todos los escritores? Inventarie servicios web, workers, tareas programadas, scripts administrativos, funciones de base de datos, triggers, CDC, ETL, consultas de BI, vistas e integraciones externas. Un solo escritor no actualizado puede recrear inconsistencias continuamente.
• ¿Deben permanecer las columnas exactamente iguales durante la migración? En este problema, sí: es un renombramiento puro. Si handle también introduce normalización, nuevas reglas de mayúsculas/minúsculas o valores seleccionados por el usuario, el manejo de conflictos se convierte en un problema de migración de datos diferente.
• ¿Cómo se implementa la unicidad de username? La nueva columna necesita un índice único o restricción equivalente. Confirme que la distinción entre mayúsculas y minúsculas, el ordenamiento (collation), el comportamiento de los nulos y cualquier predicado parcial no cambien.
• ¿Se pueden publicar los cambios de aplicación y de base de datos por separado? Deben hacerse por separado. Si el esquema y la aplicación solo pueden desplegarse como una operación indivisible, el equipo no puede avanzar a través de varias fases de compatibilidad.
• ¿Cuánto tiempo puede durar la migración? El backfill de 200 millones de filas puede tardar horas o días. El diseño necesita políticas de pausa, reanudación, finalización y retención de la columna antigua que sobrevivan a múltiples despliegues.
• ¿Qué significa rollback? Revertir el código de la aplicación, detener un backfill, restaurar escrituras en la columna antigua y recuperar una columna eliminada son cuatro operaciones diferentes. Un rollback normal de la aplicación ya no es seguro después de que se elimina la columna antigua.

Respuesta de 30 segundos

“No renombraría directamente en el lugar. Usaría expand-and-contract. Primero agregaría un handle que admita nulos con un lock_timeout corto; si el bloqueo no está disponible, fallaría y reintentaría. La versión de compatibilidad A aún lee username pero escribe en ambas columnas de forma atómica. Después de que las 80 instancias y todos los workers se hayan actualizado, haría un backfill de las filas con handle IS NULL en lotes pequeños e idempotentes basados en la clave primaria. Monitorearía nulos, discrepancias, errores de escritura, bloqueos, p99, retraso de replicación, WAL y autovacuum durante todo el proceso. Tras el backfill, construiría el nuevo índice único concurrentemente, agregaría una comprobación NOT VALID, la validaría por separado y luego establecería NOT NULL. La versión B preferiría handle con fallback a username y mantendría la escritura dual. Tras un periodo de observación, leería únicamente de handle y luego detendría las escrituras en la columna antigua. Retendría la columna antigua durante toda la ventana de rollback y la eliminaría solo después de que el código, las tareas, las vistas y los consumidores ya no hagan referencia a ella. Una compuerta fallida deja el sistema en su fase compatible actual; eliminar la columna no se trata como un punto de rollback ordinario.”

Solución paso a paso

Definir primero los invariantes y compuertas de la migración

Antes de ejecutar DDL, escriba cuatro invariantes:

1. Durante el periodo de versiones mixtas, username sigue siendo la fuente de la verdad para rollback. handle puede ser nulo, pero un valor no nulo debe ser igual a username.
2. Después de que la versión de compatibilidad llegue a todos los escritores, cada nueva escritura debe actualizar ambas columnas en una sola transacción. Una columna no puede tener éxito sin la otra.
3. Las lecturas no pueden desacoplarse de la columna antigua hasta que los conteos de discrepancias nulas y no nulas sean cero, todos los escritores sean compatibles y el nuevo índice y restricciones sean válidos.
4. La contracción del esquema no puede comenzar hasta que el código de producción, las tareas en segundo plano, las vistas, los reportes, el CDC y las versiones de rollback ya no requieran username.

Registre la versión del despliegue, la hora de inicio, el responsable, el cursor de progreso, los paneles de monitoreo y las condiciones de salida para cada fase. El ejecutor de la migración debe mantener un lease o un advisory lock de PostgreSQL para que dos ejecutores no puedan avanzar el mismo paso simultáneamente. Cada paso también necesita una versión única y un registro durable de éxito, lo que permite que un reinicio se reanude en lugar de repetir toda la migración.

Fase uno: Expandir el esquema sin cambiar el comportamiento

Ensaye el DDL en una copia con tamaño de producción e inspeccione transacciones largas, colas de bloqueo y espacio en disco. Agregue una columna que admita nulos sin valor por defecto:

BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE users ADD COLUMN handle text;
COMMIT;

Una columna que admite nulos sin valor por defecto no reescribe 200 millones de filas, pero ALTER TABLE aún necesita un bloqueo fuerte. lock_timeout hace que falle un intento que no pueda adquirir el bloqueo rápidamente, permitiendo que el sistema de despliegue reintente más tarde con fluctuación (jitter). Identifique transacciones largas anómalas antes de la ejecución y observe quién está bloqueando a quién durante la ejecución. Esperar indefinidamente no es seguro porque un bloqueo fuerte en cola puede hacer que las solicitudes posteriores sobre la tabla se acumulen detrás de él.

Las aplicaciones antiguas ignoran handle tras este paso, por lo que el rollback de la aplicación sigue siendo independiente. No combine la adición con NOT NULL, un valor por defecto volátil, una restricción única y una actualización completa. Eso acoplaría cambios de metadatos, escaneos de tabla, construcción de índices y amplificación de escritura de datos en una sola operación de alto riesgo.

Fase dos: Desplegar escritores compatibles mientras la columna antigua sigue siendo la fuente de lectura

La versión A se comporta de la siguiente manera:

• Las lecturas continúan utilizando únicamente username, por lo que el comportamiento visible para el usuario no cambia.
• La creación y actualización de usuarios escribe el mismo valor en username y handle en una sola transacción SQL.
• Un fallo en cualquiera de las columnas revierte toda la transacción; las escrituras duales no son dos solicitudes asíncronas.
• Las métricas registran intentos de escritura dual, fallos y discrepancias sin registrar nombres de usuario reales.

Durante el despliegue de la versión A, las instancias que no se han actualizado aún escriben únicamente en username, por lo que las nuevas filas con handle IS NULL son temporalmente válidas. Esa excepción se cierra solo después de que las 80 instancias web, workers, tareas programadas y consumidores independientes reporten una versión compatible. Si el equipo no puede dar cuenta de cada escritor —por ejemplo, un programa de terceros escribe directamente en la base de datos— un trigger temporal en la base de datos puede sincronizar las columnas. La compensación es un comportamiento de escritura oculto, costo adicional y una replicación, CDC y diagnóstico de incidentes más complejos. Trate dicho trigger como infraestructura de migración con una fecha de eliminación establecida.

Fase tres: Backfill idempotente por rango de clave primaria

Inicie la migración en segundo plano solo después de que cada escritor sea compatible. Utilice rangos de conjunto de claves (keyset) de clave primaria en lugar de OFFSET, y mantenga cada lote en una transacción corta:

UPDATE users
SET handle = username
WHERE id > $1
  AND id <= $2
  AND handle IS NULL;

El predicado handle IS NULL hace que el lote sea reintentable de forma segura y evita sobrescribir un valor que ya haya sido escrito por una solicitud en línea. Persista el último rango de clave primaria completado. El ejecutor puede detenerse después de cualquier lote y continuar desde el último rango confirmado tras un reinicio. Comience con un solo worker, luego ajuste el tamaño del lote y el retraso según las mediciones sin prometer un rendimiento fijo por adelantado.

Después de cada lote, observe el p99 de escritura, la CPU de la base de datos, las esperas de bloqueo, el retraso de replicación, WAL, el disco, las tuplas muertas (dead tuples) y autovacuum. Reduzca el tamaño del lote o pause tan pronto como cualquier métrica se acerque a su umbral de producción. El objetivo es una finalización estable dentro del SLO. Una sola transacción de 200 millones de filas no está justificada simplemente porque el script sea más corto.

Ejecute dos validaciones independientes al mismo tiempo:

SELECT count(*) FROM users WHERE handle IS NULL;

SELECT count(*)
FROM users
WHERE handle IS DISTINCT FROM username;

El primer conteo debería disminuir de forma constante hasta cero. El segundo utiliza IS DISTINCT FROM para capturar tanto diferencias de nulos como valores no nulos desiguales. Cualquier resultado distinto de cero bloquea el cutover y se investiga por rango de clave primaria. El backfill no debe sobrescribir silenciosamente un conflicto no nulo porque eso podría ocultar un escritor antiguo o una nueva lógica incorrecta que aún esté activa.

Fase cuatro: Agregar el índice y las restricciones

Dado que username es único, la nueva columna necesita un índice único equivalente. Después del backfill y la validación de duplicados, ejecute esto fuera de un bloque de transacción:

CREATE UNIQUE INDEX CONCURRENTLY users_handle_key
ON users (handle);

Una construcción concurrente permite que continúen las escrituras normales, pero realiza más trabajo, escanea la tabla dos veces y espera a las transacciones relevantes. Sigue consumiendo CPU e I/O. Una construcción fallida puede dejar atrás un índice INVALID. La recuperación debe inspeccionar el catálogo, eliminar el artefacto fallido y reintentar después de corregir la causa; el fallo de un comando por sí solo no prueba que la base de datos haya regresado a su estado original.

Establezca también la no nulidad por fases:

ALTER TABLE users
  ADD CONSTRAINT users_handle_not_null
  CHECK (handle IS NOT NULL) NOT VALID;

ALTER TABLE users
  VALIDATE CONSTRAINT users_handle_not_null;

ALTER TABLE users
  ALTER COLUMN handle SET NOT NULL;

ALTER TABLE users
  DROP CONSTRAINT users_handle_not_null;

NOT VALID comienza a hacer cumplir la comprobación para escrituras posteriores sin escanear inmediatamente las filas antiguas. Luego, VALIDATE CONSTRAINT comprueba las filas existentes con un nivel de bloqueo inferior. Un CHECK válido demuestra que no hay nulos, lo que permite que el posterior SET NOT NULL omita su escaneo habitual de tabla completa. Cada sentencia DDL aún requiere una espera de bloqueo corta, un paso de ejecución separado y monitoreo en producción. "Concurrente" y "sin reescritura" no significan "gratis".

Fase cinco: Realizar el cutover de lecturas, luego detener las escrituras en la columna antigua

La versión B prefiere handle, recurre a username cuando es nulo y continúa con escrituras duales atómicas. La compuerta indica que los nulos ya deberían ser cero, pero el mecanismo de fallback preserva la compatibilidad para el rollback y aísla datos inesperados. Comience con un canary, expanda gradualmente y compare los resultados de la nueva columna y de la antigua, los errores y las métricas de negocio.

Después de la ventana de observación, la versión C lee únicamente de handle mientras continúa escribiendo en ambas columnas. Un problema en la ruta de lectura aún puede revertirse a B o A porque username se mantiene actualizado. Solo después de un período de observación que cubra tareas en segundo plano, endpoints de baja frecuencia y un ciclo completo de despliegue, la versión D debería dejar de escribir en username.

Detener las escrituras en la columna antigua cambia la semántica del rollback. Un rollback posterior a una versión que solo reconozca username requiere primero restaurar las escrituras duales y hacer un backfill inverso de los valores creados durante el intervalo. Revertir directamente la aplicación expondría datos desactualizados. Incluya este requisito en el runbook y en la compuerta de despliegue para que la gestión del incidente no dependa de la memoria de un ingeniero.

Fase seis: Retrasar la contracción

Antes de la eliminación, utilice búsqueda de código, registros de consultas, catálogos de dependencias y el inventario de consumidores para demostrar que username no tiene lectores. Elimine primero los índices antiguos, restricciones, triggers y dependencias de vistas, y luego elimine la columna en una versión separada:

ALTER TABLE users DROP COLUMN username;

Eliminar la columna cruza un límite destructivo. Incluso si PostgreSQL no reescribe inmediatamente la tabla, las aplicaciones antiguas, los cachés de esquema, las vistas y las consultas externas pueden fallar de inmediato. Los datos eliminados tampoco están disponibles para un rollback ordinario de la aplicación. La eliminación debe ocurrir al menos una ventana completa de retención de rollback después del cutover de lectura y escritura, de forma separada del lanzamiento de código que deja de usar la columna. Una copia de seguridad es para recuperación ante desastres, no un rollback de despliegue de baja latencia.

Ejemplo de una respuesta sólida

“El riesgo principal es la compatibilidad durante el despliegue progresivo de 30 minutos. Dividiría el cambio en expansión, escrituras compatibles, backfill y validación, cutover de lecturas y contracción retrasada.

Primero agrego handle permitiendo nulos con un lock_timeout corto; si el bloqueo no está disponible, el intento falla y se reintenta. Una columna que admite nulos sin valor por defecto no reescribe la tabla, pero el DDL aún toma un bloqueo fuerte, por lo que inspecciono las transacciones largas y la cola de bloqueos. La versión A todavía lee de username, y cada escritor actualiza ambas columnas en una sola transacción de base de datos. El backfill comienza solo después de que las 80 instancias, workers y consumidores se hayan actualizado.

El backfill utiliza transacciones cortas por rango de clave primaria con UPDATE ... WHERE handle IS NULL, persiste el progreso y es seguro de reintentar. Su velocidad sigue el p99 de producción, el retraso de replicación, WAL, dead tuples y autovacuum. El conteo de nulos debe llegar a cero y handle IS DISTINCT FROM username debe permanecer en cero. Un conflicto no nulo se investiga en lugar de sobrescribirse.

Tras la compuerta de datos, construyo el índice único equivalente con CREATE UNIQUE INDEX CONCURRENTLY y gestiono cualquier índice inválido remanente tras un fallo. Agrego CHECK ... NOT VALID, lo valido por separado y luego establezco NOT NULL. La versión B prefiere la nueva columna con fallback a la columna antigua y mantiene la escritura dual. La versión C lee solo la nueva columna pero sigue escribiendo en ambas. La versión D detiene las escrituras en la columna antigua únicamente tras un ciclo completo de observación.

Cada fase tiene una ruta de rollback. Antes del cutover de lecturas puedo revertir la aplicación directamente. Mientras leo la nueva columna pero continúo con la escritura dual, puedo regresar a las lecturas antiguas. Después de que se detienen las escrituras antiguas, debo restaurar la escritura dual y hacer un backfill inverso antes de que una aplicación antigua sea segura. Finalmente, tras verificar que el código, las tareas, las vistas, CDC y los reportes ya no referencian a username y que la ventana de rollback haya pasado, la elimino en una versión separada. Una compuerta fallida deja el sistema en un estado compatible en lugar de avanzar a la contracción destructiva.”

Errores comunes

• Renombrar la columna in situ → Las instancias antiguas y nuevas requieren nombres diferentes durante el despliegue → Utilice una columna nueva y múltiples versiones compatibles.
• Actualizar 200 millones de filas en una sola transacción → La transacción amplifica WAL, bloqueos, retraso de replicación, bloat y tiempo de recuperación → Utilice lotes cortos e idempotentes por rango de clave primaria.
• Comenzar el backfill tan pronto como empiezan las escrituras duales → Las instancias no actualizadas aún pueden crear nuevos nulos → Espere hasta que cada escritor sea compatible antes de establecer cero nulos como compuerta.
• Implementar escrituras duales como dos solicitudes independientes → Un timeout puede actualizar solo una columna → Actualice ambas atómicamente en una sola transacción de base de datos.
• Comprobar solo handle IS NULL → Los valores no nulos desiguales escapan a la detección → Compruebe también IS DISTINCT FROM e investigue los conflictos.
• Tratar ADD COLUMN como libre de bloqueos → El DDL de metadatos aún necesita un bloqueo de tabla, y una solicitud DDL en cola puede amplificar el bloqueo → Utilice esperas de bloqueo cortas, reintentos y monitoreo de bloqueos/transacciones.
• Construir el índice único normalmente → La construcción de un índice en una tabla grande puede bloquear a los escritores durante un período inaceptable → Utilice CONCURRENTLY y gestione la carga adicional y la recuperación de índices inválidos.
• Eliminar la columna antigua inmediatamente tras el backfill → Workers de baja frecuencia, vistas, cachés de esquema o versiones de rollback aún pueden necesitarla → Retrase la contracción a lo largo de una ventana completa de observación y rollback.
• Tratar una copia de seguridad como un botón de rollback → Restaurar una base de datos de 200 millones de filas es mucho más lento y riesgoso que un rollback de aplicación → Preserve una estructura compatible en línea hasta cruzar el límite destructivo.
• Prometer una "migración sin impacto" → El DDL, los índices y el backfill consumen bloqueos o recursos → Prometa que no habrá interrupciones planificadas, proteja el SLO y pause antes de que se excedan los umbrales.

Preguntas de seguimiento

¿Por qué no agregar handle con un valor por defecto?

PostgreSQL puede evitar la reescritura de la tabla para un valor por defecto constante no volátil, pero ninguna constante expresa "copiar el username existente de esta fila". Incluso un valor por defecto físicamente rápido aún requiere un bloqueo DDL y no resuelve el problema de las instancias antiguas que solo escriben en la columna antigua. Esta migración aún requiere escritores compatibles y un backfill de datos. Si las futuras inserciones necesitan un valor por defecto es una decisión semántica de negocio, no un sustituto del plan de despliegue.

¿Qué sucede si handle debe convertirse a minúsculas y hacerse único nuevamente?

Eso ya no es un renombramiento puro. Defina una función de normalización y una política de conflictos, luego cuente las colisiones en lower(username) en una réplica o mediante un trabajo offline. Decida si conservar un valor, agregar un sufijo o requerir una acción del usuario. Almacene los valores originales y el estado de conversión durante el backfill, y cree el índice único solo después de que los conflictos lleguen a cero. Las escrituras en línea y el backfill deben usar la misma implementación de normalización.

¿Qué sucede si todos los escritores no se pueden actualizar al mismo tiempo?

Para un sistema externo que no pueda migrar rápidamente, agregue un trigger temporal en la base de datos que copie la escritura de la columna antigua a la nueva y registre el uso para que se pueda identificar al llamador restante. El trigger debe rechazar solicitudes que proporcionen valores en conflicto. Evalúe la recursión, la replicación y el comportamiento del CDC. Una vez que todos los llamadores hayan migrado, deshabilite y observe antes de eliminar el trigger para que la lógica de negocio oculta no se vuelva permanente.

¿Qué sucede si el retraso de replicación sigue aumentando durante el backfill?

Pause nuevos lotes y permita que las réplicas se pongan al día en lugar de agregar workers para acelerar el avance. Inspeccione el tamaño del lote, la cadencia de commits, la tasa de WAL, las consultas largas y el autovacuum. Reanude con lotes más pequeños y menor concurrencia. Si las lecturas dependen de réplicas, el retraso de replicación ya representa un riesgo visible para el usuario; la fecha de finalización del backfill está subordinada al SLO de producción.

¿Se puede simplemente volver a ejecutar CREATE UNIQUE INDEX CONCURRENTLY después de un fallo?

No lo vuelva a ejecutar a ciegas. Un índice inválido con el mismo nombre puede permanecer en el catálogo, y una construcción única concurrente puede haber impuesto unicidad frente a otras transacciones durante una fase fallida. Inspeccione la validez del índice, identifique el fallo de datos o recursos, elimine el índice fallido según el runbook y luego vuelva a construirlo. El comando tampoco puede ejecutarse dentro de un bloque de transacción regular, por lo que la herramienta de migración debe admitir ese modo de ejecución.

¿Qué fase es la más difícil de revertir (rollback)?

Después de que se detienen las escrituras en la columna antigua, el valor antiguo comienza a desactualizarse. Después de que se elimina la columna antigua, tanto los datos como el esquema cruzan un límite destructivo. Lo primero requiere restaurar la escritura dual y hacer un backfill inverso antes de que una versión antigua sea segura. Lo segundo generalmente requiere una reparación hacia adelante o la restauración de una copia de seguridad. Mantenga estas acciones en versiones separadas y mantenga la columna antigua actualizada durante una ventana de observación suficientemente larga.