Cómo un ALTER TABLE tiró producción por 19 minutos en PostgreSQL

Nota: Este caso no me ocurrió a mí, pero lo comparto porque es un ejemplo muy valioso para cualquier DBA o desarrollador que trabaje con PostgreSQL en producción. El RCA original fue publicado por Haider (The Sev-1 Database newsletter).

El escenario

PostgreSQL 14. Tabla principal con aproximadamente 180 millones de filas y 320 GB. La tabla llevaba cuatro años sin cambios de esquema.

El requerimiento era agregar una columna con constraint NOT NULL y un valor por defecto. El plan parecía simple:

1. Deploy del código de aplicación (backward compatible)
2. Correr la migración: ALTER TABLE orders ADD COLUMN status_v2 TEXT NOT NULL DEFAULT 'pending'
3. Deploy de la segunda versión usando la nueva columna

El paso 2 fue el problema.

¿Qué pasó a las 9:07 AM?

La migración se ejecutó en producción. En minutos:

• La API comenzó a devolver 503s
• Timeouts de conexión en todos los servicios
• CPU del servidor de base de datos al 100%
• El connection pool se agotó por completo

La migración en apariencia «seguía corriendo», pero cada otra query estaba en cola esperando un lock. El servicio estuvo completamente caído en menos de 2 minutos. La migración tardó 47 minutos en completarse. Impacto total: ~19 minutos de downtime para el cliente final, más horas de demora en el deploy.

La causa raíz: ACCESS EXCLUSIVE Lock

ALTER TABLE adquiere un ACCESS EXCLUSIVE lock — el lock más restrictivo de PostgreSQL. Bloquea absolutamente toda otra operación sobre la tabla mientras esté activo.

En PostgreSQL 11+, agregar una columna con un default no-volátil ya no reescribe la tabla completa. Parecía seguro. Pero hay dos condiciones que rompen esa optimización:

1. El default es una función volátil (now(), random())
2. La combinación de NOT NULL + DEFAULT fuerza un full table scan para validar las filas existentes

La condición 2 aplica aquí. PostgreSQL necesitaba verificar que los 180 millones de filas cumplieran el NOT NULL, lo que requirió un full scan manteniendo el ACCESS EXCLUSIVE durante todo ese tiempo.

La cascada de locks

La tabla recibía una cantidad alta de queries por segundo en horario pico. Cuando se adquirió el ACCESS EXCLUSIVE a las 9:07 AM, cada query posterior se puso en cola — cada una manteniendo una conexión abierta del pool.

En 90 segundos el connection pool se agotó. Las nuevas requests no podían adquirir conexión. Empezaron los 503.

El patrón correcto para tablas grandes

Esta es la forma segura de agregar una columna NOT NULL con default a una tabla con millones de filas:

Paso 1: Agregar la columna como nullable primero

-- Rápido — no hace full table rewrite ni validation scan
-- Igual toma ACCESS EXCLUSIVE, así que usá lock_timeout como precaución
SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN status_v2 TEXT DEFAULT 'pending';

Paso 2: Backfill de filas existentes en batches

-- Índice parcial para acelerar el scan
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_status_v2_null
ON orders (id) WHERE status_v2 IS NULL;

DO $$
DECLARE
  rows_updated INT;
BEGIN
  LOOP
    WITH batch AS (
      SELECT id FROM orders
      WHERE status_v2 IS NULL
      LIMIT 5000
      FOR UPDATE SKIP LOCKED
    )
    UPDATE orders SET status_v2 = 'pending'
    FROM batch WHERE orders.id = batch.id;

    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    EXIT WHEN rows_updated = 0;

    COMMIT; -- Libera locks y permite que VACUUM limpie el bloat
    PERFORM pg_sleep(0.1);
  END LOOP;
END $$;

-- Después del backfill, eliminar el índice parcial:
-- DROP INDEX CONCURRENTLY idx_orders_status_v2_null;

Paso 3: Agregar el constraint NOT NULL con NOT VALID, luego validar por separado

-- Fase 1: agregar sin validar filas existentes (rápido, lock breve)
ALTER TABLE orders ADD CONSTRAINT orders_status_v2_nn
  CHECK (status_v2 IS NOT NULL) NOT VALID;

-- Fase 2: validar (usa ShareUpdateExclusiveLock — no bloquea reads/writes)
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_v2_nn;

Siempre correr un VACUUM ANALYZE orders; después de un backfill masivo para que el query planner tenga estadísticas frescas.

Qué cambió después del incidente

• Todas las migraciones sobre tablas con más de 10M de filas ahora requieren revisión de DBA
• Se creó un checklist pre-migración que explícitamente pregunta: ¿Esto adquiere ACCESS EXCLUSIVE? ¿Por cuánto tiempo?

La lección

Un ALTER TABLE inocente en una tabla grande puede tumbar toda tu aplicación en menos de 2 minutos. En PostgreSQL, el tipo de lock que se adquiere y cuánto tiempo se mantiene importa tanto como la migración en sí. Conocer los mecanismos de locking no es un conocimiento «avanzado de DBA» — es una habilidad básica para cualquier backend que toque bases de datos en producción.