PostgreSQL
Crear y evolucionar tablas
Creación y evolución de tablas con CREATE TABLE y ALTER TABLE, considerando columnas, defaults, restricciones, locks y migraciones seguras.
- Última actualización
- Actualizada
- Nivel
- Fundamentos
PostgreSQL
Creación y evolución de tablas con CREATE TABLE y ALTER TABLE, considerando columnas, defaults, restricciones, locks y migraciones seguras.
Crear una tabla define más que columnas: establece identidad, tipos, defaults, reglas y dependencias. Evolucionarla exige pensar en locks, compatibilidad entre versiones de la aplicación y coste sobre los datos existentes.
CREATE TABLE materializa una parte del modelo relacional. ALTER TABLE cambia ese contrato con el tiempo. En una base pequeña, muchos cambios parecen instantáneos; en producción, una operación DDL puede bloquear escrituras, reescribir millones de filas o dejar incompatibles dos versiones de la aplicación desplegadas al mismo tiempo.
El modelo correcto es:
requisito de dominio
→ diseño de tabla y constraints
→ migración versionada
→ despliegue compatible
→ validación y observaciónUna tabla mal creada puede aceptar datos inválidos desde el primer día. Una migración mal planificada puede provocar downtime aunque el SQL sea sintácticamente correcto.
Problemas frecuentes:
CREATE TABLE app.orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES app.customers(id),
status text NOT NULL DEFAULT 'pending',
notes text,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT orders_status_check
CHECK (status IN ('pending', 'confirmed', 'cancelled'))
);
COMMENT ON TABLE app.orders IS 'Customer orders';
COMMENT ON COLUMN app.orders.status IS 'Current lifecycle state';Este diseño establece:
id.DEFAULT solo se aplica cuando la columna se omite. No impide que alguien envíe otro valor; por eso status también necesita una regla.
serial es un shorthand histórico que crea:
nextval.Las identity columns expresan la relación con más claridad:
id bigint GENERATED ALWAYS AS IDENTITYRechaza valores explícitos salvo que se use OVERRIDING SYSTEM VALUE. Es útil cuando PostgreSQL debe controlar la generación.
Permite valores explícitos y genera uno cuando se omite. Puede facilitar imports o replicaciones, pero también permite colisiones si no se controla.
Ninguna garantiza IDs contiguos. Las sequences avanzan fuera de la transacción y pueden dejar huecos.
created_at timestamptz NOT NULL DEFAULT now()El default se evalúa durante el insert. Para varias filas de una misma sentencia, now() es estable dentro de la transacción.
Evita defaults que esconden decisiones importantes. Por ejemplo, generar automáticamente tenant_id desde estado de sesión puede ser útil con RLS, pero exige controlar pooling y seguridad.
CREATE TABLE app.order_items (
order_id bigint NOT NULL REFERENCES app.orders(id),
quantity numeric(12,3) NOT NULL,
unit_price numeric(14,2) NOT NULL,
subtotal numeric(16,2)
GENERATED ALWAYS AS (quantity * unit_price) STORED
);Una generated column mantiene el valor según la expresión. Considera:
Si el valor es barato de calcular y poco consultado, una expresión en SELECT puede ser suficiente.
Supón que orders necesita cancelled_at obligatorio cuando status sea cancelled.
ALTER TABLE app.orders
ADD COLUMN cancelled_at timestamptz;La aplicación nueva empieza a escribirlo, mientras la versión antigua sigue funcionando.
UPDATE app.orders
SET cancelled_at = updated_at
WHERE status = 'cancelled'
AND cancelled_at IS NULL;En tablas grandes, procesa por lotes para reducir locks, WAL y replicación atrasada.
ALTER TABLE app.orders
ADD CONSTRAINT cancelled_at_consistency
CHECK (
(status = 'cancelled' AND cancelled_at IS NOT NULL)
OR
(status <> 'cancelled' AND cancelled_at IS NULL)
) NOT VALID;Después:
ALTER TABLE app.orders
VALIDATE CONSTRAINT cancelled_at_consistency;Cuando ningún código antiguo depende de la forma anterior, elimina compatibilidad temporal o columnas obsoletas.
Añadir NOT NULL puede requerir verificar todas las filas. Una estrategia moderna puede utilizar una constraint validada:
ALTER TABLE app.orders
ADD CONSTRAINT orders_cancelled_at_nn
CHECK (cancelled_at IS NOT NULL) NOT VALID;
ALTER TABLE app.orders
VALIDATE CONSTRAINT orders_cancelled_at_nn;
ALTER TABLE app.orders
ALTER COLUMN cancelled_at SET NOT NULL;La optimización exacta depende de versión. Verifica el plan de migración en la major version desplegada.
ALTER TABLE app.products
ALTER COLUMN price TYPE numeric(14,2)
USING price::numeric(14,2);USING explica cómo convertir datos. Riesgos:
Antes de cambiar:
Cambiar customer_id a buyer_id directamente rompe código anterior.
Estrategia:
Alternativamente, una view o trigger temporal puede mantener compatibilidad, pero añade complejidad y debe retirarse.
DELETE FROM app.orders WHERE created_at < now() - interval '5 years';RETURNING.TRUNCATE TABLE staging.import_rows;ACCESS EXCLUSIVE.RESTART IDENTITY.Elimina definición y datos. CASCADE elimina dependencias; úsalo con extrema cautela.
Buena parte del DDL es transaccional en PostgreSQL, pero operaciones concurrentes y comandos administrativos tienen restricciones particulares.
CREATE TEMP TABLE selected_orders (...)
ON COMMIT DROP;Son específicas de sesión y útiles para trabajo intermedio. Riesgos:
search_path.CREATE UNLOGGED TABLE transient_import (...);Reducen WAL de datos y pueden ser rápidas para información reconstruible. Después de ciertos crashes se truncan y no participan normalmente en replicación física como una tabla logged. No las uses para órdenes, pagos o inventario crítico.
CREATE TABLE events (
occurred_at timestamptz NOT NULL,
payload jsonb NOT NULL
) PARTITION BY RANGE (occurred_at);La tabla padre enruta filas a partitions. Antes de particionar, define:
Particionar no mejora automáticamente cualquier consulta. Añade objetos, operación y planificación.
CREATE INDEX CONCURRENTLY orders_customer_id_idx
ON app.orders(customer_id);CONCURRENTLY reduce bloqueo de escrituras, pero:
La herramienta de migraciones debe soportar pasos no transaccionales conscientemente.
Muchos ALTER TABLE adquieren ACCESS EXCLUSIVE, incluso si duran poco. El riesgo no es solo el tiempo de ejecución: una migración puede quedar esperando detrás de una transacción larga y bloquear consultas posteriores en cola.
Mitigaciones:
SET lock_timeout = '2s';
SET statement_timeout = '10min';Si no consigue el lock rápidamente, falla y se reintenta en una ventana mejor.
Requisito: exponer un UUID sin reemplazar la primary key bigint.
ALTER TABLE app.orders ADD COLUMN public_id uuid;La aplicación o un default genera UUID para nuevos registros.
UPDATE app.orders
SET public_id = gen_random_uuid()
WHERE id IN (
SELECT id
FROM app.orders
WHERE public_id IS NULL
ORDER BY id
LIMIT 1000
);CREATE UNIQUE INDEX CONCURRENTLY orders_public_id_uidx
ON app.orders(public_id);ALTER TABLE app.orders
ADD CONSTRAINT orders_public_id_key
UNIQUE USING INDEX orders_public_id_uidx;Después de validar, SET NOT NULL con estrategia compatible.
El cambio se divide para evitar una única operación larga y permitir convivencia de versiones.
Dependiendo de versión y expresión, puede reescribir. Verifica documentación y prueba.
Usa NOT VALID, corrige y valida.
No siempre es posible recuperar una columna eliminada. El rollback puede ser restaurar backup o desplegar forward fix.
Un backfill o index build genera WAL y puede aumentar lag.
DDL se aplica a una primary; consumidores y réplicas deben tolerar el cambio durante propagación.
Crea drift entre entornos. Usa migraciones revisables.
Los datos existentes pueden impedirla. Audita primero.
Las instancias antiguas fallan durante rollout.
Puede eliminar views, constraints u objetos no previstos.
Aumenta locks, WAL, bloat y tiempo de rollback.
serial en diseños nuevos.NOT VALID y creación concurrente reducen ciertos impactos, no todos.ALTER TABLE que espera un lock?CREATE INDEX CONCURRENTLY necesita tratamiento especial?Tipos de datos y semántica explica cómo cada columna define precisión, operaciones, almacenamiento y significado del dominio.