Migraciones de Postgres que bloquean tu tabla: qué bloqueo toma cada ALTER TABLE y cómo aplicarlas sin riesgo

Una migración que agrega una columna nullable termina en menos de un milisegundo. Aun así puede tumbar tu aplicación, porque antes de ejecutarse tiene que esperar un bloqueo, y mientras espera, todas las demás consultas sobre esa tabla esperan detrás de ella. Este artículo muestra la cola de bloqueos en acción en tres sesiones de psql, enumera el bloqueo que toma cada ALTER TABLE común y si reescribe la tabla, y repasa los patrones que hacen seguras las migraciones: lock_timeout con reintentos, NOT VALID seguido de VALIDATE, y CREATE INDEX CONCURRENTLY. Todas las sentencias y salidas de abajo se ejecutaron en PostgreSQL 17.11, la versión mayor que usa Relyt One (la única nota sobre PostgreSQL 18 se comprobó en 18.6).

Si solo vas a recordar cuatro cosas:

1. La mayoría de las formas de ALTER TABLE toman un bloqueo ACCESS EXCLUSIVE, que bloquea tanto lecturas como escrituras. Aunque el cambio en sí sea instantáneo, la sentencia primero se pone en cola para obtener ese bloqueo.
2. Mientras está en cola, las nuevas consultas sobre la misma tabla se forman detrás de ella. Una transacción larga más una migración bastan para detener todas las solicitudes que tocan la tabla.
3. Dale a cada migración un lock_timeout, para que se rinda después de un par de segundos en lugar de bloquear el tráfico indefinidamente, y reinténtala.
4. Separa las comprobaciones costosas del bloqueo: agrega las restricciones con NOT VALID y valídalas después con VALIDATE, crea los índices con CONCURRENTLY y evita las sentencias que reescriben una tabla grande.

El resto del artículo muestra cada uno de estos puntos con salidas que puedes reproducir.

Dos tablas: un millón de cuentas y dos millones de pedidos. Ejecuta esto en psql contra una base de datos desechable:

CREATE TABLE accounts (
  id         bigint PRIMARY KEY,
  email      text NOT NULL,
  plan       varchar(20),
  score      integer NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO accounts (id, email, plan, score)
SELECT g, 'user' || g || '@example.com',
       (ARRAY['free', 'pro', 'team'])[1 + g % 3], g % 1000
FROM generate_series(1, 1000000) AS g;

CREATE TABLE orders (
  id         bigint PRIMARY KEY,
  account_id bigint NOT NULL,
  total      numeric(10, 2) NOT NULL
);
INSERT INTO orders
SELECT g, 1 + g % 1000000, (g % 10000) / 100.0
FROM generate_series(1, 2000000) AS g;
ANALYZE;

Abre tres sesiones de psql. En la primera, inicia una transacción que lea la tabla y quede abierta. En un sistema real, esto es un reporte lento, un proceso por lotes o una conexión que una aplicación dejó en idle in transaction porque olvidó hacer commit:

-- Sesión A
SET application_name = 'a_report';
BEGIN;
SELECT count(*) FROM accounts;
-- deja la transacción abierta

En la segunda, ejecuta la migración. Agregar una columna nullable es la migración "segura" de manual:

-- Sesión B
SET application_name = 'b_migration';
ALTER TABLE accounts ADD COLUMN note text;

Se queda colgada. En la tercera, ejecuta el tipo de consulta que tu aplicación ejecuta miles de veces por minuto:

-- Sesión C
SET application_name = 'c_app';
SELECT email FROM accounts WHERE id = 42;

También se queda colgada, aunque solo lee una fila y la sesión A solo lee. Desde una cuarta sesión, pregúntale a Postgres quién está esperando a quién:

SELECT pid, application_name, state, wait_event_type AS wait_type,
       pg_blocking_pids(pid) AS blocked_by, query
FROM pg_stat_activity
WHERE application_name IN ('a_report', 'b_migration', 'c_app')
ORDER BY application_name;

 pid | application_name |        state        | wait_type | blocked_by |                   query
-----+------------------+---------------------+-----------+------------+--------------------------------------------
 478 | a_report         | idle in transaction | Client    | {}         | SELECT count(*) FROM accounts;
 488 | b_migration      | active              | Lock      | {478}      | ALTER TABLE accounts ADD COLUMN note text;
 495 | c_app            | active              | Lock      | {488}      | SELECT email FROM accounts WHERE id = 42;
(3 rows)

Tus pids serán distintos. La columna blocked_by cuenta la historia. C no está bloqueada por A. A tiene un bloqueo ACCESS SHARE, y C quiere el mismo modo, así que no entran en conflicto. C está bloqueada por B. B quiere ACCESS EXCLUSIVE, que entra en conflicto con todo, así que espera a A. Y una nueva solicitud de bloqueo tiene que esperar si entra en conflicto con una solicitud que ya está esperando, no solo con una que ya fue concedida. Esa regla existe para que un flujo constante de lectores no pueda dejar a la migración esperando para siempre, pero su efecto secundario es que un ALTER TABLE en cola se convierte en un muro frente a la tabla.

En nuestra ejecución, la sesión A hizo commit ocho segundos después de empezar, B terminó de inmediato y la búsqueda de una fila de C tardó 6.2 segundos. Con tráfico real, todas las solicitudes que tocan accounts en esa ventana se acumulan, los pools de conexiones se agotan y la caída parece causada por una migración de una línea. Lo fue, pero no porque la migración fuera lenta.

lock_timeout cancela una sentencia que ha esperado un bloqueo más tiempo que el límite. Repite el mismo experimento, pero dale a la sesión B dos segundos:

-- Sesión B
SET application_name = 'b_migration';
SET lock_timeout = '2s';
ALTER TABLE accounts ADD COLUMN note text;

ERROR:  canceling statement due to lock timeout

Esta vez C esperó como máximo dos segundos (1.8 segundos en nuestra ejecución, en lugar de 6.2): solo esperó hasta que B alcanzó su límite y salió de la cola. La migración falló, y de eso se trata: falló a bajo costo en lugar de tumbar la tabla. Ahora la reintentas.

Dos detalles hacen que esto funcione en la práctica.

Usa SET LOCAL dentro de la transacción de la migración. SET LOCAL dura hasta el final de la transacción actual y luego se revierte, así que no puede filtrarse a lo que se ejecute después en esa conexión:

BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE orders
  ADD CONSTRAINT orders_account_fk
  FOREIGN KEY (account_id) REFERENCES accounts (id) NOT VALID;
COMMIT;

Esto importa si tus migraciones pasan por un pooler en modo transacción. Cada transacción puede caer en una conexión de servidor distinta, así que no está garantizado que un SET normal de una transacción anterior se aplique al ALTER TABLE. La cadena de conexión con pooling de Relyt One pasa por PgBouncer con pooling a nivel de transacción. Ejecuta las migraciones con la cadena de conexión directa y, aun así, mantén el ajuste dentro de la transacción.

Reintenta con backoff, pero solo cuando se agota el lock_timeout. Muchas herramientas de migración pueden reintentar; si la tuya no lo hace, basta con un pequeño wrapper. Este reintenta solo cuando la migración se rindió esperando un bloqueo, se detiene en el primer éxito y falla después de cinco intentos. Cualquier otro error, como una errata en el SQL, falla de inmediato. Compara el texto del error en inglés, así que mantén lc_messages en inglés para la sesión de la migración:

#!/usr/bin/env bash
# Uso: ./migrate.sh migration.sql
# Reintenta solo cuando la migración se rindió esperando un bloqueo.
set -u
for attempt in 1 2 3 4 5; do
  if output=$(psql "$DATABASE_URL" -X -v ON_ERROR_STOP=1 -f "$1" 2>&1); then
    echo "$output"
    exit 0
  fi
  echo "$output" >&2
  if ! grep -q "canceling statement due to lock timeout" <<<"$output"; then
    exit 1  # cualquier otro error: corrígelo, no lo reintentes
  fi
  if [ "$attempt" -lt 5 ]; then
    echo "attempt $attempt hit the lock timeout; retrying in $((attempt * 5))s" >&2
    sleep $((attempt * 5))
  fi
done
echo "giving up after 5 attempts" >&2
exit 1

Guardamos la migración de la clave foránea de arriba como 0042_orders_account_fk.sql y ejecutamos el wrapper mientras otra sesión mantenía abierta una transacción que había actualizado una fila de accounts. El primer intento agotó el tiempo de espera del bloqueo y el segundo tuvo éxito en cuanto esa transacción hizo commit:

BEGIN
SET
psql:0042_orders_account_fk.sql:5: ERROR:  canceling statement due to lock timeout
attempt 1 hit the lock timeout; retrying in 5s
BEGIN
SET
ALTER TABLE
COMMIT

Como el archivo envuelve todo en una sola transacción, un intento fallido se revierte por completo y el reintento empieza desde cero. Reintenta solo archivos como este. Una sentencia que no puede ejecutarse dentro de una transacción, como CREATE INDEX CONCURRENTLY, puede dejar algo atrás cuando falla (más sobre esto abajo), así que el siguiente intento tropezaría con ello.

Antes de una migración sobre una tabla con mucho tráfico, también vale la pena buscar la transacción que la va a bloquear:

SELECT pid, state, now() - xact_start AS xact_age, left(query, 50) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND pid <> pg_backend_pid()
ORDER BY xact_start
LIMIT 5;

Una transacción que lleva minutos abierta, sobre todo una en estado idle in transaction, hará que todos los intentos agoten el tiempo de espera del bloqueo. idle_in_transaction_session_timeout (desactivado por defecto) termina esas sesiones automáticamente.

Puedes medirlo tú mismo. Ejecuta la sentencia dentro de una transacción, lee pg_locks antes de hacer commit y revierte con rollback. Para ver si Postgres reescribió la tabla, compara su file node antes y después: una reescritura escribe una nueva copia de la tabla en un archivo nuevo.

CREATE VIEW my_locks AS
SELECT relation::regclass AS table_name, mode
FROM pg_locks
WHERE pid = pg_backend_pid()
  AND locktype = 'relation'
  AND relation IN ('accounts'::regclass, 'orders'::regclass)
ORDER BY 1, 2;

BEGIN;
SELECT pg_relation_filenode('accounts') AS before \gset
ALTER TABLE accounts ALTER COLUMN score TYPE bigint;
SELECT * FROM my_locks;
SELECT pg_relation_filenode('accounts') <> :before AS rewritten;
ROLLBACK;

 table_name |        mode
------------+---------------------
 accounts   | AccessExclusiveLock
 accounts   | ShareLock
(2 rows)

 rewritten
-----------
 t
(1 row)

(\gset es un comando de psql que guarda el resultado en una variable.) Ejecutamos así cada sentencia de la tabla de abajo sobre la tabla accounts de un millón de filas y la tabla orders de dos millones de filas. Para las columnas "Bloquea", mantuvimos cada bloqueo en una transacción abierta e intentamos un SELECT de una fila y un UPDATE de una fila desde otra sesión con un lock_timeout de 500 ms; para el resto, el efecto se deriva del modo de bloqueo según la tabla de conflictos de PostgreSQL.

Las claves foráneas bloquean dos tablas, así que tienen su propia tabla. orders es la tabla que hace la referencia y accounts la referenciada:

Los tiempos provienen de un contenedor local y escalan con el tamaño de la tabla y el hardware, así que léelos como relativos, no absolutos. Algunas cosas de estas tablas sorprenden a la gente:

• Todas las sentencias de aquí que agregan, eliminan, renombran o cambian el tipo de una columna toman ACCESS EXCLUSIVE, incluidas las instantáneas. "Instantánea" describe cuánto tiempo se mantiene el bloqueo, no si hace falta. Todas pueden iniciar una cola de bloqueos.
• Un valor por defecto no implica una reescritura. Desde PostgreSQL 11, un valor por defecto no volátil se guarda una sola vez en el catálogo y las filas existentes lo toman cuando se leen. now() cuenta aquí como no volátil: se evalúa una vez, cuando se ejecuta el ALTER TABLE, y todas las filas existentes reciben ese mismo timestamp. Un valor por defecto volátil como gen_random_uuid() o clock_timestamp() necesita un valor distinto por fila, así que la tabla se reescribe.
• Ampliar un varchar es gratis; reducirlo, no. Pasar de varchar(20) a varchar(50) o a text solo cambia el catálogo. Pasar a varchar(10) reescribió la tabla en PostgreSQL 17.
• De integer a bigint siempre se reescribe. Los valores cambian de tamaño en disco. En una tabla grande, esta es la migración que más necesita un plan: agrega una nueva columna bigint, rellénala por lotes y haz el cambio en una transacción corta.
• Una clave foránea bloquea las escrituras en la tabla a la que apunta, no solo en la tabla que estás modificando. Agregar una a orders también detuvo los UPDATE sobre accounts.
• CREATE INDEX deja pasar las lecturas pero bloquea las escrituras mientras dura la construcción.

ADD CONSTRAINT normalmente comprueba todas las filas existentes mientras mantiene su bloqueo. NOT VALID se salta esa comprobación: la restricción se aplica de inmediato a las filas nuevas y actualizadas, y las filas antiguas se comprueban después con VALIDATE CONSTRAINT, que toma un bloqueo mucho más débil. En las tablas de arriba, agregar la clave foránea directamente bloqueó las escrituras en ambas tablas durante 1.8 segundos. Dividida en dos, la parte que bloquea tardó 3 ms, y la validación de 1.2 segundos dejó pasar tanto lecturas como escrituras.

La migración 1 es el archivo de la Solución 1; si ya la ejecutaste allí, solo queda la migración 2:

-- Migración 1: rápida, pero aún necesita su bloqueo, así que mantén el lock_timeout
BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE orders
  ADD CONSTRAINT orders_account_fk
  FOREIGN KEY (account_id) REFERENCES accounts (id) NOT VALID;
COMMIT;

-- Migración 2: recorre la tabla sin bloquear lecturas ni escrituras
ALTER TABLE orders VALIDATE CONSTRAINT orders_account_fk;

Si VALIDATE encuentra una fila que viola la restricción, falla, y la restricción queda como NOT VALID mientras sigue aplicándose a las nuevas escrituras. Corrige los datos y vuelve a validar.

En PostgreSQL 17, SET NOT NULL no tiene forma NOT VALID, y recorre toda la tabla bajo ACCESS EXCLUSIVE. Pero se salta el recorrido si una restricción CHECK validada ya demuestra que la columna no tiene nulos. Puedes ver la decisión con client_min_messages = debug1:

ALTER TABLE accounts ADD CONSTRAINT plan_not_null CHECK (plan IS NOT NULL) NOT VALID;
ALTER TABLE accounts VALIDATE CONSTRAINT plan_not_null;

SET client_min_messages = debug1;
ALTER TABLE accounts ALTER COLUMN plan SET NOT NULL;
RESET client_min_messages;

ALTER TABLE accounts DROP CONSTRAINT plan_not_null;

DEBUG:  existing constraints on column "accounts.plan" are sufficient to prove that it does not contain nulls

Sin la restricción check, la misma sentencia imprime en su lugar DEBUG: verifying table "accounts", que es el recorrido completo. Una vez que la columna es NOT NULL, la restricción check sobra y puedes eliminarla.

PostgreSQL 18 lo simplifica. Acepta ALTER TABLE accounts ADD CONSTRAINT plan_nn NOT NULL plan NOT VALID, y comprobamos en 18.6 que el VALIDATE CONSTRAINT posterior toma SHARE UPDATE EXCLUSIVE. PostgreSQL 17 rechaza esa sentencia con un error de sintaxis, así que en Relyt One usa la vía del CHECK descrita arriba.

CREATE INDEX CONCURRENTLY toma SHARE UPDATE EXCLUSIVE en lugar de SHARE, así que las escrituras siguen fluyendo mientras se construye. A cambio, como explica la documentación, recorre la tabla dos veces y espera a las transacciones existentes que podrían usar o modificar el índice, así que hace más trabajo y tarda más. También tiene dos reglas propias.

No puede ejecutarse dentro de un bloque de transacción:

BEGIN;
CREATE INDEX CONCURRENTLY accounts_email_idx ON accounts (email);
ROLLBACK;

ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block

Muchas herramientas de migración envuelven cada archivo en una transacción, así que revisa cómo desactiva eso la tuya para una sola migración. Aun así espera su bloqueo, así que dale también un lock_timeout. SET LOCAL no hace nada fuera de una transacción (Postgres responde WARNING: SET LOCAL can only be used in transaction blocks), así que usa un SET lock_timeout = '2s'; a nivel de sesión justo antes, en una conexión directa.

Y si falla, deja atrás un índice inválido. Un índice único sobre una columna con duplicados es una forma fácil de verlo:

CREATE UNIQUE INDEX CONCURRENTLY accounts_plan_key ON accounts (plan);
SELECT indexrelid::regclass AS index_name, indisvalid
FROM pg_index WHERE indrelid = 'accounts'::regclass;

ERROR:  could not create unique index "accounts_plan_key"
DETAIL:  Key (plan)=(free) is duplicated.

    index_name     | indisvalid
-------------------+------------
 accounts_pkey     | t
 accounts_plan_key | f
(2 rows)

(El valor duplicado de la línea DETAIL puede variar entre ejecuciones.) El índice inválido nunca se usa en las consultas. Según la fase de la construcción en la que haya fallado, puede que las escrituras todavía tengan que mantenerlo (revisa indisready en el mismo catálogo). Una construcción concurrente que se rinde por su lock_timeout también deja un índice inválido: cuando ejecutamos una contra una tabla con una transacción de escritura de larga duración, agotó el tiempo de espera del bloqueo y dejó indisvalid = f. Sea cual sea la causa, elimina el índice con DROP INDEX CONCURRENTLY accounts_plan_key; y vuelve a intentarlo.

1. Busca la sentencia en las tablas de arriba. Si toma ACCESS EXCLUSIVE, o cualquier bloqueo que bloquee escrituras (SHARE, SHARE ROW EXCLUSIVE), necesita un lock_timeout, por rápida que sea.
2. Empieza la transacción de la migración con SET LOCAL lock_timeout = '2s' (o la pausa que tu aplicación pueda tolerar) y reintenta cuando se agote el tiempo de espera del bloqueo.
3. Revisa pg_stat_activity en busca de sesiones de larga duración y en idle in transaction antes de empezar.
4. Agrega las claves foráneas y las restricciones check con NOT VALID, y luego valídalas con VALIDATE en un paso aparte.
5. Agrega NOT NULL mediante una restricción CHECK validada en PostgreSQL 17.
6. Crea y elimina índices con CONCURRENTLY, fuera de una transacción, con un SET lock_timeout a nivel de sesión, y busca indisvalid = false después.
7. Trata todo lo que reescriba la tabla (integer a bigint, un valor por defecto volátil, reducir un tipo) como un proyecto, no como una migración.
8. Ejecuta las migraciones por una conexión directa, no a través de un pooler de transacciones.

Las tablas de arriba te dicen qué bloqueo toma una sentencia. No pueden decirte cuánto tarda el recorrido o la reescritura con tus datos, ni si tus datos pasan el paso de VALIDATE. Para eso necesitas datos del tamaño de producción, y conseguir una copia suele ser la parte difícil.

En Relyt One, cada rama es una bifurcación copy-on-write de su rama padre con el conjunto de datos completo y su propio cómputo. Crea una rama a partir de tu rama de producción, ejecuta allí el archivo de la migración con \timing on, usa la comprobación del file node de arriba para ver si reescribe la tabla y averigua si VALIDATE falla con filas antiguas antes de ejecutarlo de verdad. Luego elimina la rama. Producción nunca ve el experimento.

Lo único que una rama no puede ensayar es la cola de bloqueos en sí, porque ahí no se está ejecutando nada más. Te dice cuánto tiempo mantiene la migración su bloqueo una vez que lo obtiene; lock_timeout y los reintentos siguen siendo lo que te protege de esperar el bloqueo en producción. Y compara tiempos relativos, no absolutos: el cómputo de la rama puede tener un tamaño distinto al de producción.

Empieza gratis