PostgreSQL desde cero (II): tipos, restricciones y relaciones

Segunda entrega de la serie PostgreSQL desde cero a pro. Tiempo de lectura estimado: 11 minutos.

En la entrega anterior levantamos PostgreSQL, creamos una base de datos y metimos las primeras filas. En este post nos centramos en lo que convierte PostgreSQL en una base de datos seria: tipos ricos y restricciones que garantizan que los datos sean correctos desde la base de datos, no desde la capa de aplicación.

El principio subyacente: si puedes hacer que un dato imposible sea imposible de insertar, hazlo. Las validaciones en la aplicación están muy bien; las restricciones en la base de datos son la última línea de defensa y la única que no se salta con un backfill manual por psql a las tres de la mañana.

El sistema de tipos

PostgreSQL tiene uno de los sistemas de tipos más ricos entre las bases de datos relacionales.

Enteros, decimales, floats

SMALLINT       -- 2 bytes, ±32k
INTEGER        -- 4 bytes, ±2.100M
BIGINT         -- 8 bytes, ±9 trillones

NUMERIC(p, s)  -- precisión arbitraria. Para dinero: SIEMPRE
REAL           -- 4 bytes float
DOUBLE PRECISION  -- 8 bytes float

Regla universal: dinero = NUMERIC. Nunca REAL ni DOUBLE PRECISION para importes. El redondeo de los float te hace llorar el día de la auditoría.

Texto

TEXT          -- variable, sin límite de longitud
VARCHAR(n)    -- variable, con longitud máxima
CHAR(n)       -- fijo (padding con espacios)

Al contrario que en otras bases de datos, en PostgreSQL TEXT y VARCHAR tienen el mismo rendimiento. No hay razón para usar VARCHAR(n) salvo que quieras imponer un límite. CHAR(n) casi nunca es lo que quieres.

Fechas y tiempos

DATE
TIME           -- solo hora, sin fecha
TIMESTAMP      -- fecha y hora, SIN zona horaria
TIMESTAMPTZ    -- fecha y hora, CON zona horaria
INTERVAL

Usa siempre TIMESTAMPTZ a menos que sepas exactamente por qué no. TIMESTAMPTZ guarda el instante en UTC y lo presenta en el huso horario de la sesión. TIMESTAMP te dejará clavado en un huso sin más contexto y ese bug aparecerá cuando viajes a otro país.

UUID

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  ...
);

Los UUID como PK son útiles cuando generas IDs en el cliente o quieres evitar adivinar enumeraciones. Son más grandes que los enteros (16 bytes vs 8) y se fragmentan más en los índices: para una clave primaria muy caliente, sigue siendo mejor BIGINT con secuencia.

Tipos propios: enums

CREATE TYPE post_status AS ENUM ('draft', 'published', 'archived');

ALTER TABLE posts ADD COLUMN status post_status NOT NULL DEFAULT 'draft';

Los enums son rápidos y ocupan poco. El contra: añadir un valor requiere ALTER TYPE, que en versiones antiguas podía ser molesto. En PostgreSQL moderno es trivial:

ALTER TYPE post_status ADD VALUE 'scheduled' AFTER 'draft';

Alternativa idiomática: una columna TEXT con un CHECK que limita los valores. Más flexible que el enum, más lento por fila (muy poco).

Arrays

CREATE TABLE posts (
  ...
  tags TEXT[]
);

INSERT INTO posts (..., tags) VALUES (..., ARRAY['postgresql', 'tutorial']);

SELECT * FROM posts WHERE 'postgresql' = ANY(tags);
SELECT * FROM posts WHERE tags @> ARRAY['postgresql', 'tutorial'];
SELECT tag, count(*) FROM posts, unnest(tags) AS tag GROUP BY tag;

Los arrays son geniales para datos que no merecen una tabla dedicada: tags, identificadores secundarios, opciones de una config simple. Si estás manipulando un array con mucha frecuencia o necesitas atributos por elemento, es señal de que toca modelo relacional normal.

JSON y JSONB

PostgreSQL tiene dos tipos para JSON:

• JSON: almacena el texto tal cual. Preserva formato y duplicados de claves.
• JSONB: parseado a estructura binaria. Más rápido para consultar, permite índices GIN.

Usa JSONB siempre, salvo que necesites preservar el formato exacto (casi nunca).

CREATE TABLE events (
  id    BIGSERIAL PRIMARY KEY,
  type  TEXT NOT NULL,
  data  JSONB NOT NULL
);

INSERT INTO events (type, data) VALUES
  ('signup', '{"email":"a@b.com","plan":"free","referrer":"google"}');

-- Acceso
SELECT data->>'email', data->'plan' FROM events;
SELECT * FROM events WHERE data @> '{"plan":"free"}';
SELECT * FROM events WHERE data ? 'referrer';

-- Índice GIN para consultas de contención
CREATE INDEX events_data_gin ON events USING GIN (data jsonb_path_ops);

Operadores clave:

• -> devuelve JSONB
• ->> devuelve TEXT
• @> "contiene"
• ? "tiene la clave"
• #> ruta (igual que -> encadenado)

JSONB no reemplaza al modelo relacional: es para lo que de verdad es semi-estructurado (payloads de webhooks, propiedades de evento de analítica, configuraciones flexibles). Si vas a consultar siempre los mismos cinco campos, crea columnas.

Restricciones

NOT NULL

Obligatorio pensar en qué columnas pueden ser nulas y cuáles no. NOT NULL es la restricción más fundamental y la más olvidada.

CREATE TABLE authors (
  id    BIGSERIAL PRIMARY KEY,
  email TEXT NOT NULL,
  name  TEXT NOT NULL
);

UNIQUE

CREATE TABLE authors (
  id    BIGSERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

-- Único compuesto
CREATE TABLE memberships (
  user_id  BIGINT NOT NULL,
  group_id BIGINT NOT NULL,
  UNIQUE (user_id, group_id)
);

-- Único parcial (solo para filas activas)
CREATE UNIQUE INDEX users_email_active_unique
  ON users(lower(email))
  WHERE deleted_at IS NULL;

El índice único parcial es una herramienta infravalorada: permite tener "único entre los activos" sin sacrificar historicidad.

PRIMARY KEY vs IDENTITY

BIGSERIAL sigue siendo válido, pero el estándar SQL moderno es:

CREATE TABLE authors (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  ...
);

Diferencias prácticas:

• GENERATED ALWAYS: el cliente no puede insertar un valor explícito (a menos que use OVERRIDING SYSTEM VALUE).
• GENERATED BY DEFAULT: se genera si no lo das, pero puedes darlo.
• Encaja mejor con migraciones entre SGBDs.

Si empiezas un proyecto hoy, usa IDENTITY. Si mantienes uno viejo, BIGSERIAL sigue funcionando perfectamente.

CHECK

Cualquier expresión booleana vale como restricción:

CREATE TABLE products (
  id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name     TEXT NOT NULL,
  price    NUMERIC(12,2) NOT NULL CHECK (price >= 0),
  sku      TEXT NOT NULL CHECK (sku ~ '^[A-Z0-9-]{4,20}$'),
  discount NUMERIC(4,2) CHECK (discount IS NULL OR (discount >= 0 AND discount <= 1))
);

Los CHECK son rápidos, se ejecutan en inserts/updates y sobreviven a cualquier cliente buggy. Úsalos sin miedo.

Foreign keys

CREATE TABLE posts (
  id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id BIGINT NOT NULL REFERENCES authors(id) ON DELETE RESTRICT,
  ...
);

Opciones en ON DELETE y ON UPDATE:

• NO ACTION (default): error si quedan referencias.
• RESTRICT: igual que NO ACTION pero se evalúa inmediatamente.
• CASCADE: propaga el borrado/update.
• SET NULL: pone NULL en la referencia.
• SET DEFAULT: pone el default de la columna.

CASCADE es cómodo pero peligroso: un borrado accidental puede vaciar media base de datos. Yo uso RESTRICT salvo que el modelo de dominio claramente pida cascada (por ejemplo, "al borrar un pedido, sus líneas").

Exclusion constraints

El pariente menos conocido y más potente. Impide que dos filas "se solapen" según cualquier operador:

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE bookings (
  id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id  BIGINT NOT NULL,
  period   TSTZRANGE NOT NULL,
  EXCLUDE USING GIST (
    room_id WITH =,
    period  WITH &&
  )
);

Traducción: no puede haber dos reservas de la misma habitación cuyos periodos se solapen. Imposible. Sin triggers, sin locks manuales, sin código de aplicación defensivo. Esto es PostgreSQL luciéndose.

Relaciones: uno a muchos, muchos a muchos

Uno a muchos: una foreign key en el lado "muchos".

authors 1 ----< N posts

CREATE TABLE authors (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  ...
);
CREATE TABLE posts (
  id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id BIGINT NOT NULL REFERENCES authors(id),
  ...
);

Muchos a muchos: una tabla de unión con dos FKs.

posts N >----< M tags  (vía post_tags)

CREATE TABLE tags (
  id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE post_tags (
  post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  tag_id  BIGINT NOT NULL REFERENCES tags(id)  ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

La tabla de unión con PK compuesta da unicidad y el índice necesario para consultas en ambos sentidos.

Migraciones seguras

PostgreSQL es transaccional también para DDL: CREATE TABLE, ALTER TABLE, etc. pueden envolverse en BEGIN ... COMMIT. Esto significa que una migración que falla a mitad no deja el esquema a medio aplicar. Es una de sus mejores cualidades.

Algunas operaciones, sin embargo, toman locks exclusivos en la tabla que pueden bloquear tráfico:

• ALTER TABLE ... ADD COLUMN col TYPE NOT NULL DEFAULT 'x' en versiones antiguas reescribía la tabla.
• CREATE INDEX bloquea escrituras hasta que termina.
• ALTER TYPE con cambios de representación reescribe datos.

Alternativas seguras:

CREATE INDEX CONCURRENTLY posts_slug_idx ON posts(slug);

CONCURRENTLY construye el índice sin bloquear escrituras, a cambio de ser más lento y no poder ejecutarse dentro de una transacción. Las herramientas modernas de migración (Flyway, sqitch, la CLI de tu ORM) suelen tener soporte explícito.

Para añadir columnas nuevas con NOT NULL en tablas grandes, el patrón moderno es:

1. Añadir columna sin NOT NULL.
2. Hacer backfill por lotes.
3. Añadir el NOT NULL (validación barata si no hay nulos).

Un esquema completo

Uniendo piezas de los dos posts:

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS citext;

CREATE TYPE post_status AS ENUM ('draft', 'published', 'archived');

CREATE TABLE authors (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      CITEXT NOT NULL UNIQUE,
  name       TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE posts (
  id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id    BIGINT NOT NULL REFERENCES authors(id) ON DELETE RESTRICT,
  title        TEXT NOT NULL,
  slug         TEXT NOT NULL UNIQUE,
  body         TEXT NOT NULL,
  status       post_status NOT NULL DEFAULT 'draft',
  metadata     JSONB NOT NULL DEFAULT '{}',
  published_at TIMESTAMPTZ,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (status != 'published' OR published_at IS NOT NULL)
);

CREATE TABLE tags (
  id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name CITEXT NOT NULL UNIQUE
);

CREATE TABLE post_tags (
  post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  tag_id  BIGINT NOT NULL REFERENCES tags(id)  ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

CREATE INDEX posts_published_at_idx ON posts(published_at DESC)
  WHERE status = 'published';

Dos detalles nuevos:

• CITEXT (extensión): texto case-insensitive. Para emails y nombres de tag, ahorra lower() por todos lados.
• El CHECK en posts impone invariante: si el estado es published, published_at no puede ser NULL.

Por dónde seguir

• III: CTEs, window functions y consultas avanzadas — ya tienes buenos datos, ahora toca consultarlos como un adulto.
• IV: índices, EXPLAIN y rendimiento — cuando el modelo crece y las consultas ya no son instantáneas.
• V: replicación, backups y producción — operar PostgreSQL sin perder datos ni sueño.

¿Llegas tarde a la serie? I: instalación, psql y primeros pasos.