Saltar a contenido

Schema migration strategy — Postgres zero-downtime

Estrategia para evolución del schema sin downtime. Patterns: expand/contract, online migrations, partitioning, índice concurrente. Tooling: golang-migrate + scripts custom. Cobertura M1-M4.

Premisa

Workflow engine en producción no puede tener downtime para migraciones. Postgres permite zero-downtime migrations si seguís reglas. La clave: expand → migrate → contract en 3 deploys.

Categorías de migración por riesgo

Tipo Lock requerido Downtime Estrategia
ADD COLUMN (nullable) AccessShareLock (read) 0 Direct
ADD COLUMN (default no-null en PG 11+) AccessShareLock 0 Direct
DROP COLUMN AccessExclusiveLock bloquea writes Expand/contract
RENAME COLUMN AccessExclusiveLock bloquea writes Expand/contract
ALTER COLUMN TYPE AccessExclusiveLock + rewrite bloquea writes prolongado Expand/contract o online
ADD INDEX ShareLock (bloquea writes) bloquea writes CONCURRENTLY
DROP INDEX AccessExclusiveLock breve CONCURRENTLY
ADD FOREIGN KEY ShareRowExclusiveLock bloquea writes NOT VALID + validate
ADD CHECK CONSTRAINT AccessExclusiveLock bloquea writes NOT VALID + validate
CREATE TABLE None 0 Direct
DROP TABLE AccessExclusiveLock brief Expand/contract

Regla: cualquier operación con AccessExclusiveLock puede bloquear writes (y reads en PG < 16) durante segundos a minutos. Bloquea writes ⇒ engine no puede procesar comandos.

Tooling: golang-migrate

go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@latest

Estructura:

migrations/
├── 000001_initial.up.sql
├── 000001_initial.down.sql
├── 000002_add_audit_trail.up.sql
├── 000002_add_audit_trail.down.sql
├── 000003_partition_command_log.up.sql
└── 000003_partition_command_log.down.sql

Reglas: - Cada migración es forward-only en producción (down sólo para tests). - Cada migración es idempotent (IF NOT EXISTS, etc.). - Pre-flight check: cada migración tiene --lock-timeout/--statement-timeout.

-- 000004_add_priority_column.up.sql
SET lock_timeout = '5s';
SET statement_timeout = '60s';

ALTER TABLE jobs ADD COLUMN IF NOT EXISTS priority INTEGER NOT NULL DEFAULT 0;

Si la migración no puede adquirir lock en 5s, falla y se reintenta más tarde. Evita esperar indefinido.

Pattern: Expand / Migrate / Contract (E/M/C)

Para cambios que requerirían un lock pesado:

Fase 1: Expand (deploy 1)

Agregar nueva versión SIN romper la vieja.

-- 000010_expand_add_new_status.up.sql
ALTER TABLE jobs ADD COLUMN status_v2 TEXT;  -- nullable, no FK

-- Trigger para mantener sincronizado
CREATE OR REPLACE FUNCTION sync_job_status() RETURNS trigger AS $$
BEGIN
    NEW.status_v2 = CASE NEW.status
        WHEN 'ACTIVATABLE' THEN 'pending'
        WHEN 'ACTIVATED' THEN 'active'
        WHEN 'COMPLETED' THEN 'completed'
        ...
    END;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER sync_status_v2
BEFORE INSERT OR UPDATE ON jobs
FOR EACH ROW EXECUTE FUNCTION sync_job_status();

App code: lee de status (legacy), trigger pobla status_v2.

Fase 2: Backfill (offline o online)

-- Backfill in batches (online)
DO $$
DECLARE
    batch_size INT := 10000;
    rows_updated INT;
BEGIN
    LOOP
        UPDATE jobs
        SET status_v2 = CASE status
            WHEN 'ACTIVATABLE' THEN 'pending'
            ...
        END
        WHERE status_v2 IS NULL
        AND key IN (
            SELECT key FROM jobs WHERE status_v2 IS NULL LIMIT batch_size
        );

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

        PERFORM pg_sleep(0.1);  -- yield
    END LOOP;
END $$;

Throughput: ~10K rows / 100ms = 100K rows/s. 100M rows = 16min sin bloqueo.

Fase 3: Migrate (deploy 2)

App code: lee/escribe status_v2, mantiene status por compatibilidad.

// pseudo-code
func writeJob(j Job) {
    db.Exec("INSERT INTO jobs (status, status_v2) VALUES ($1, $2)",
        legacyStatus(j.Status), j.Status)
}

func readJob(key int64) Job {
    var statusV2 sql.NullString
    db.QueryRow("SELECT status_v2 FROM jobs WHERE key=$1", key).Scan(&statusV2)
    if statusV2.Valid {
        return Job{Status: statusV2.String}
    }
    // Fallback durante backfill
    var statusLegacy string
    db.QueryRow("SELECT status FROM jobs WHERE key=$1", key).Scan(&statusLegacy)
    return Job{Status: mapLegacy(statusLegacy)}
}

Fase 4: Contract (deploy 3)

Eliminar el legacy.

-- 000012_contract_remove_legacy_status.up.sql
DROP TRIGGER sync_status_v2 ON jobs;
DROP FUNCTION sync_job_status();
ALTER TABLE jobs DROP COLUMN status;
ALTER TABLE jobs RENAME COLUMN status_v2 TO status;
ALTER TABLE jobs ALTER COLUMN status SET NOT NULL;

App code: usa sólo status.

Reglas

  • Cada fase es deploy independiente. No mezclar.
  • Entre fases, app code soporta ambos estados (compat).
  • Si el deploy de la fase 2 falla, podés rollback al fase 1.

Patterns específicos

Add NOT NULL column

-- BIEN (PG 11+): default sin rewrite
ALTER TABLE x ADD COLUMN y TEXT NOT NULL DEFAULT 'default-value';

-- MAL (PG < 11): rewrite completo
-- Usar E/M/C en su lugar

Add foreign key

-- Fase 1: agregar sin validar
ALTER TABLE jobs ADD CONSTRAINT jobs_instance_fk
    FOREIGN KEY (process_instance_key) REFERENCES process_instances(key)
    NOT VALID;

-- Fase 2: validar online
ALTER TABLE jobs VALIDATE CONSTRAINT jobs_instance_fk;

VALIDATE toma ShareUpdateExclusiveLock (no bloquea writes). Puede tardar pero no down.

Add CHECK constraint

ALTER TABLE jobs ADD CONSTRAINT jobs_retries_positive CHECK (retries >= 0) NOT VALID;
ALTER TABLE jobs VALIDATE CONSTRAINT jobs_retries_positive;

Add index

-- BIEN
CREATE INDEX CONCURRENTLY idx_jobs_type_state ON jobs (type, state);

-- MAL
CREATE INDEX idx_jobs_type_state ON jobs (type, state);  -- bloquea writes

CONCURRENTLY no se puede ejecutar en transaction; separar:

-- 000020.up.sql
COMMIT;  -- finalizar tx implícita
CREATE INDEX CONCURRENTLY idx_jobs_type_state ON jobs (type, state);
BEGIN;  -- reabrir para próximas operations

Si falla a mitad: hay un index inválido. Detectar:

SELECT relname FROM pg_class
WHERE relname = 'idx_jobs_type_state' AND NOT relisvalid;
-- Si retorna fila: drop y reintentar
DROP INDEX CONCURRENTLY IF EXISTS idx_jobs_type_state;

Drop column

-- E/M/C:
-- Fase 1: app deja de usar la columna.
-- Fase 2: app deploy.
-- Fase 3 (deploy 2): drop column (rápido, sólo cataloga).
ALTER TABLE jobs DROP COLUMN deprecated_field;

Rename table

-- BIEN: crear view legacy
CREATE VIEW jobs_old AS SELECT * FROM jobs_new;
-- App migrates progressively
-- Eventually: DROP VIEW

Partition existing table

-- Para tablas grandes (commands log, audit log)
-- 1. Crear partitioned table nueva
CREATE TABLE commands_partitioned (LIKE commands INCLUDING ALL) PARTITION BY RANGE (created_at);

-- 2. Crear partitions
CREATE TABLE commands_2026_05 PARTITION OF commands_partitioned
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

-- 3. Copy data en batches
INSERT INTO commands_partitioned SELECT * FROM commands;

-- 4. Swap (atomic)
BEGIN;
ALTER TABLE commands RENAME TO commands_old;
ALTER TABLE commands_partitioned RENAME TO commands;
COMMIT;

-- 5. Drop old (después de verificación)
DROP TABLE commands_old;

Ver concepts/command-log-compaction para partitioning de command log.

Estrategia para tablas grandes (>100M rows)

Approach: logical replication + table swap.

  1. Crear nueva tabla con esquema target.
  2. Setup logical replication slot copiando data + WAL.
  3. Esperar replica esté al día (< 1s lag).
  4. Stop writes ~5s, swap (rename), restart.

Tools: pg_repack, pglogical.

Multi-tenant migration considerations

Si tabla es per-tenant (RLS o tenant_id):

-- Backfill por tenant para reducir lock pressure
DO $$
DECLARE
    t_id TEXT;
BEGIN
    FOR t_id IN SELECT DISTINCT tenant_id FROM jobs LOOP
        UPDATE jobs SET status_v2 = ... WHERE tenant_id = t_id AND status_v2 IS NULL;
        PERFORM pg_sleep(0.5);  -- yield entre tenants
    END LOOP;
END $$;

Citus / sharded environment (Phase 4+)

En Citus:

-- Ejecutar en coordinator; propaga a workers
SET citus.multi_shard_modify_mode TO 'sequential';  -- evita deadlocks
ALTER TABLE jobs ADD COLUMN priority INT DEFAULT 0;

Algunas operaciones (cambiar distribution column) requieren undistribute_table() + redistribute.

CI/CD integration

Validation pre-merge

# .github/workflows/migration-check.yml
- name: Lint migrations
  run: |
    migrate -path migrations -database "postgres://test" up
    sqlfluff lint migrations/

- name: Check for risky operations
  run: |
    grep -E 'ALTER TABLE .* RENAME|DROP COLUMN' migrations/*.up.sql && \
        echo "::warning::Risky operation detected, ensure E/M/C plan"

Deployment pipeline

1. PR merged → migration applied to staging
2. Staging smoke tests pass
3. Migration applied to prod via change-management approval
4. App deploy (matching app version) after migration succeeds

Migration y app deploy son deploys separados. Migration siempre primero.

Rollback

  • down.sql solo para dev/tests.
  • Producción: rollback es nuevo migration que revierta (forward-only).
  • Para rollback de columna agregada: drop column (siempre safe).
  • Para rollback de columna dropped: re-create + backfill (datos perdidos si no hay logical backup).

Observability

wf_db_migration_started_total{version}
wf_db_migration_completed_total{version, outcome="success|failed"}
wf_db_migration_duration_seconds{version}
wf_db_locks_held_seconds{lock_type}

Alerta: wf_db_locks_held_seconds > 5 durante deploy → potencial corruption.

Anti-patterns documentados

❌ ALTER TABLE ... TYPE en columna grande

-- MAL: rewrite completo de la tabla, bloquea horas
ALTER TABLE jobs ALTER COLUMN data TYPE BYTEA USING data::bytea;

Mejor: E/M/C con nueva columna data_bytea.

❌ Migration en transaction larga

BEGIN;
-- 50 operaciones
COMMIT;

Si la 50ª falla, las 49 se revierten. Lock dura todo el camino. Splittear en migrations independientes.

❌ Migration sin lock_timeout

ALTER TABLE x ADD COLUMN y INT;
-- Si la tabla está siendo accedida, espera indefinido.

Siempre SET lock_timeout.

❌ Backfill sin batching

-- MAL: lock pesado en una sola transacción
UPDATE jobs SET status_v2 = ... WHERE status_v2 IS NULL;

Checklist por migración

  • SET lock_timeout = '5s' al inicio.
  • IF NOT EXISTS / IF EXISTS para idempotencia.
  • Si DROP / RENAME / TYPE: planificado como E/M/C en 3 deploys.
  • Índices con CONCURRENTLY.
  • FKs y CHECKs con NOT VALID + VALIDATE.
  • Backfill en batches con yield.
  • Migration probada en staging con dataset proporcional.
  • Runbook si falla mitad de camino.
  • Verificación post-migration (índices válidos, row count, etc.).

Roadmap

  • M1: golang-migrate setup, schema básico, RLS migrations.
  • M2: Partitioning automation (pg_partman integration).
  • M3: Migration linting + risk assessment automation.
  • M4: Citus-aware migrations.

Referencias