Bases de datos: SQL, modelado, rendimiento y NoSQL

Casi todos los problemas graves de rendimiento de una aplicación Java están en la base de datos, no en el código Java. Y casi todos los problemas graves de corrección están en el modelo de datos, no en el servicio. Este módulo es el de mayor retorno de todo el plan: aquí aprendes a modelar un dominio, a escribir SQL que un planificador pueda ejecutar rápido, a leer un plan de ejecución, a razonar sobre concurrencia y a decidir con criterio cuándo hace falta algo que no sea PostgreSQL.

1 · Panorama: qué bases de datos existen y cuál elegir

Antes de escribir una línea de SQL conviene situarse. La industria ha pasado por una fase de entusiasmo NoSQL (2009-2015), una fase de resaca (2015-2020) y ha llegado a un consenso bastante sólido: se empieza con una base de datos relacional y se añade otra tecnología solo cuando existe un problema medido que la relacional no resuelve. Lo que sigue es el mapa completo para que esa decisión sea informada.

Las ocho familias de bases de datos

EL MAPA MENTAL: ¿QUÉ PREGUNTA LE VAS A HACER A LOS DATOS?

  «Dame el pedido 4711 y sus líneas»                 → relacional (clave primaria + join)
  «Dame todos los pedidos de este cliente en 2026»   → relacional (índice compuesto)
  «Dame este perfil completo tal cual lo guardé»     → documental o relacional con jsonb
  «Dame el valor de esta clave, ya»                  → clave-valor
  «Suma las ventas por país y mes de 3 años»         → relacional pequeño / columnar si es grande
  «Busca "zapatilas rojas" con erratas y facetas»    → búsqueda (índice invertido)
  «Encuentra productos parecidos a esta descripción» → vectorial
  «Amigos de amigos que compraron esto»              → grafo
  «Media de CPU por minuto de los últimos 30 días»   → series temporales

REGLA DEL ARQUITECTO:  una tecnología nueva = un modelo de datos nuevo
                                             + una operativa nueva
                                             + una guardia nueva
                                             + una fuente de incoherencias nueva
Solo se paga ese precio con una métrica delante.

OLTP frente a OLAP: dos mundos que hablan el mismo idioma

La distinción más útil de todo el capítulo no es «SQL o NoSQL», sino OLTP o OLAP: procesamiento transaccional en línea frente a procesamiento analítico en línea. Ambos se consultan con SQL, y por eso se confunden; pero optimizan cosas opuestas, y meter carga analítica en la base de datos transaccional es una de las formas más habituales de tumbar una aplicación.

ARQUITECTURA HABITUAL (y por qué existe cada flecha)

   ┌──────────────┐   escribe/lee     ┌───────────────────┐
   │  Aplicación  │ ────────────────▶ │   PostgreSQL      │  OLTP: verdad del negocio
   │  Spring Boot │                   │   (primaria)      │  transacciones, invariantes
   └──────────────┘                   └─────────┬─────────┘
          │                                     │ replicación física (streaming WAL)
          │ lee informes                        ▼
          │                           ┌───────────────────┐
          └─────────────────────────▶ │  Réplica lectura  │  informes ligeros, backups
                                      └─────────┬─────────┘
                                                │ CDC (Debezium) o ETL nocturno
                                                ▼
                                      ┌───────────────────┐
                                      │  Almacén columnar │  OLAP: cuadros de mando,
                                      │  ClickHouse/BQ    │  histórico de años
                                      └───────────────────┘

Regla: el cuadro de mando de dirección NUNCA consulta la primaria. Un informe mal escrito
que escanee 200 millones de filas puede dejar sin caché y sin conexiones a los clientes
que están pagando en ese momento.

Por qué el relacional sigue siendo el default en 2026

No es inercia ni conservadurismo. Son ocho ventajas concretas, y cada una de ellas es un problema que no tendrás que resolver tú a mano:

1. El esquema es un contrato verificado por la máquina. Una columna numeric(12,2) NOT NULL no puede contener "12,50 €" ni null. En un almacén sin esquema, ese contrato existe igual, pero vive en la cabeza del equipo y en el código de la aplicación, es decir, se rompe en la primera migración descuidada.
2. Las transacciones ACID son correctas por defecto. «Cobra y crea el pedido, o no hagas nada» es una línea de código, no un patrón de compensación distribuido con reintentos idempotentes.
3. La integridad referencial la garantiza el motor. No existen líneas de pedido huérfanas si hay una clave foránea. Es imposible, no improbable.
4. SQL es declarativo: describes el resultado y el planificador decide el algoritmo. Cuando los datos crecen y las estadísticas cambian, el plan cambia sin que tú toques el código. En una base de datos sin planificador, el algoritmo lo escribes tú y envejece contigo.
5. Soporta preguntas que nadie previó. Un modelo normalizado responde consultas que no existían cuando se diseñó. Un modelo documental optimizado para un patrón de acceso obliga a reescribir datos —no consultas— cuando aparece un patrón nuevo.
6. PostgreSQL absorbió a la competencia. jsonb cubre el caso documental, tsvector la búsqueda, pgvector la semántica, PostGIS lo geoespacial, TimescaleDB las series temporales, LISTEN/NOTIFY y SKIP LOCKED las colas, y las tablas particionadas los históricos grandes. Una sola operativa.
7. Ecosistema y conocimiento. Cualquier herramienta habla SQL; cualquier persona con experiencia sabe leer un esquema; hay 30 años de literatura sobre cómo optimizarlo.
8. El hardware ha ganado la discusión. Una instancia normal con NVMe y 64 GB de RAM sirve decenas de miles de transacciones por segundo. La mayoría de los sistemas que «necesitaban escalar horizontalmente» en 2012 caben hoy holgadamente en un servidor.

2 · Modelado relacional: del dominio al esquema

El esquema es la decisión más difícil de revertir de todo tu sistema. El código se refactoriza en una tarde; un modelo de datos equivocado con 40 millones de filas y doce integraciones encima se arrastra durante años. Merece la pena dedicarle el tiempo que se le dedica a una API pública.

Entidades, atributos y relaciones

Modelar es responder tres preguntas en este orden: ¿de qué cosas habla el negocio (entidades)?, ¿qué se sabe de cada cosa (atributos)?, ¿cómo se conectan (relaciones y su cardinalidad)? El vocabulario debe ser el del negocio, no el de la tecnología: si en la empresa se dice «albarán», la tabla se llama albaran y no delivery_note_v2.

Claves primarias: naturales, sintéticas, secuencias y UUID

Una clave primaria identifica una fila de forma única, estable y no nula. Hay dos escuelas y la elección tiene consecuencias físicas medibles.

-- ✅ Recomendación general: PK sintética + UNIQUE sobre la clave natural
CREATE TABLE cliente (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,   -- SQL estándar, mejor que serial
    email       text        NOT NULL,
    nif         text,
    nombre      text        NOT NULL,
    pais        char(2)     NOT NULL DEFAULT 'ES',
    activo      boolean     NOT NULL DEFAULT true,
    creado_en   timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT cliente_email_uk UNIQUE (email),                    -- clave natural protegida
    CONSTRAINT cliente_nif_uk   UNIQUE (nif),                      -- UNIQUE permite varios NULL
    CONSTRAINT cliente_pais_ck  CHECK (pais ~ '^[A-Z]{2}$')
);

-- ❌ Antipatrón 1: PK natural mutable. El día que un cliente corrige su email,
--    hay que actualizar en cascada pedido, pago, envío, log de auditoría...
CREATE TABLE cliente_mal (email text PRIMARY KEY, nombre text);

-- ❌ Antipatrón 2: PK sintética SIN unicidad natural. Nada impide dos clientes
--    con el mismo email; en tres meses tienes 4.000 duplicados y un proyecto de limpieza.
CREATE TABLE cliente_mal2 (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email text);

-- ✅ Clave natural en tablas de referencia estables y pequeñas: aquí sí compensa
CREATE TABLE pais (
    codigo char(2) PRIMARY KEY,          -- ISO 3166-1 alpha-2: no va a cambiar
    nombre text NOT NULL,
    iva    numeric(4,2) NOT NULL
);

-- ✅ Clave compuesta natural en tablas de unión N:M: es lo correcto,
--    no añadas un id sintético que solo sirve para engordar el índice
CREATE TABLE producto_etiqueta (
    producto_id  bigint NOT NULL REFERENCES producto(id) ON DELETE CASCADE,
    etiqueta_id  int    NOT NULL REFERENCES etiqueta(id) ON DELETE CASCADE,
    PRIMARY KEY (producto_id, etiqueta_id)
);

Secuencia (bigint) frente a UUID: el impacto real en los índices

Esta es la discusión que más veces se resuelve mal, porque el argumento suele ser estético cuando en realidad es físico. Un índice B-tree se llena por páginas de 8 kB. Insertar claves crecientes añade siempre a la página de la derecha: esa página está en caché, se llena al 90% y se cierra. Insertar claves aleatorias obliga a tocar una página cualquiera del índice para cada INSERT: se leen páginas frías de disco, se parten por la mitad (page split) y el índice acaba ocupando el doble con la mitad de densidad.

INSERCIÓN SECUENCIAL (bigint IDENTITY)      INSERCIÓN ALEATORIA (UUIDv4)

  [ ... páginas frías ... ][ 90% ][ +1 ]      [ 60% ][ 55% ][ 70% ][ 51% ][ 63% ]
                              ▲                   ▲       ▲              ▲
                     siempre la misma        cada INSERT cae en una página
                     página, en caché        distinta: fallo de caché, split

  · 1 página caliente en RAM                  · N páginas tocadas y ensuciadas
  · WAL mínimo                                · WAL con full-page writes frecuentes
  · Índice denso (~90% de ocupación)          · Índice hinchado (~50-60%)
  · Rango por fecha ≈ rango por id            · Ningún orden útil

MEDIDA TÍPICA: la misma carga de 10 M de inserciones puede tardar 2-4× más
y ocupar 1,5-2× de índice con UUIDv4 frente a bigint o UUIDv7.

-- UUID en Postgres: usa el tipo uuid (16 bytes), NUNCA text (37 bytes + collation)
CREATE EXTENSION IF NOT EXISTS pgcrypto;   -- aporta gen_random_uuid() en versiones antiguas

CREATE TABLE evento_publico (
    id        uuid PRIMARY KEY DEFAULT gen_random_uuid(),   -- v4: aleatorio
    payload   jsonb NOT NULL,
    creado_en timestamptz NOT NULL DEFAULT now()
);

-- ❌ El error que multiplica por 2,3 el tamaño del índice y rompe la comparación
CREATE TABLE evento_mal (id text PRIMARY KEY DEFAULT gen_random_uuid()::text);

-- ✅ Patrón intermedio muy útil: PK interna bigint + identificador público opaco.
--    Los joins internos son baratos y la API no filtra cuántos pedidos tienes.
CREATE TABLE pedido_publico (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id_publico uuid   NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    cliente_id bigint NOT NULL REFERENCES cliente(id)
);

Claves foráneas e integridad referencial

CREATE TABLE pedido (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    cliente_id bigint      NOT NULL,
    estado     text        NOT NULL DEFAULT 'NUEVO',
    total      numeric(12,2) NOT NULL DEFAULT 0,
    moneda     char(3)     NOT NULL DEFAULT 'EUR',
    creado_en  timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT pedido_cliente_fk FOREIGN KEY (cliente_id)
        REFERENCES cliente(id)
        ON DELETE RESTRICT          -- no permitas borrar un cliente con pedidos
        ON UPDATE CASCADE,
    CONSTRAINT pedido_estado_ck CHECK (estado IN ('NUEVO','PAGADO','ENVIADO','ENTREGADO','CANCELADO')),
    CONSTRAINT pedido_total_ck  CHECK (total >= 0)
);

-- ⚠️ CRÍTICO: PostgreSQL crea índice para la PK y para UNIQUE, pero NO para las FK.
-- Sin este índice, borrar o actualizar un cliente hace un Seq Scan de pedido para
-- comprobar la restricción, y cualquier "dame los pedidos del cliente X" escanea la tabla.
CREATE INDEX pedido_cliente_idx ON pedido (cliente_id);

Cardinalidades: cómo se traduce cada tipo de relación

1:1   cliente ──── perfil_facturacion
      · FK con UNIQUE en el lado dependiente, o misma PK compartida.
      · Úsalo para separar columnas grandes o de acceso raro, o datos con otro nivel
        de sensibilidad (RGPD). Si no hay motivo, son la misma tabla.

1:N   cliente ──────< pedido
      · FK en el lado "muchos" (pedido.cliente_id). Es el 80% de las relaciones.
      · Índice en la FK, siempre.

N:M   producto >────── producto_etiqueta ──────< etiqueta
      · Tabla intermedia con PK compuesta (producto_id, etiqueta_id).
      · Si la relación tiene atributos propios (cantidad, precio pactado, fecha de alta)
        deja de ser "intermedia": es una entidad de pleno derecho, como linea_pedido.

1:N recursiva   categoria ──┐
                    ▲       │  padre_id → categoria.id
                    └───────┘
      · Árboles: categorías, organigramas, respuestas anidadas.
      · Se recorre con WITH RECURSIVE (sección 3) o con ltree/closure table si el árbol
        es profundo y se consulta mucho.

Normalización explicada de verdad

Normalizar no es «partir tablas hasta que duela». Es un procedimiento con un objetivo muy concreto: eliminar las anomalías de actualización, que son tres. Anomalía de modificación: tengo que cambiar el mismo dato en 500 filas y si me olvido de una, el sistema se contradice. Anomalía de inserción: no puedo registrar un hecho porque me falta otro sin relación con él. Anomalía de borrado: al borrar una fila pierdo información que no tenía nada que ver.

Vamos a partir de una tabla deliberadamente horrible y arreglarla paso a paso.

Punto de partida: la tabla que se hereda de una hoja de cálculo

-- ❌ Todo en una tabla, tal como salió del Excel de ventas
CREATE TABLE pedido_plano (
    pedido_id       int,
    cliente_email   text,
    cliente_nombre  text,
    cliente_ciudad  text,
    cliente_cp      text,
    productos       text,          -- 'SKU-1 x2, SKU-9 x1'   ← varios valores en una celda
    precios         text,          -- '19.90, 5.00'
    fecha           text,          -- '03/04/2026'           ← ni siquiera es una fecha
    total           real           -- ← dinero en coma flotante
);

Esta tabla es imposible de consultar («¿cuántas unidades del SKU-1 vendí?» requiere analizar texto), imposible de mantener coherente (el total no cuadra con los precios) e imposible de indexar de forma útil.

1FN — Primera forma normal: cada celda, un solo valor atómico

Regla: no hay grupos repetitivos ni listas dentro de una columna; existe una clave que identifica la fila. Se cumple partiendo los valores múltiples en filas.

-- ✅ 1FN: una fila por producto del pedido, tipos correctos
CREATE TABLE pedido_1fn (
    pedido_id        int         NOT NULL,
    linea_num        smallint    NOT NULL,
    cliente_email    text        NOT NULL,
    cliente_nombre   text        NOT NULL,
    cliente_ciudad   text        NOT NULL,
    cliente_cp       text        NOT NULL,
    sku              text        NOT NULL,
    producto_nombre  text        NOT NULL,
    cantidad         int         NOT NULL,
    precio_unitario  numeric(10,2) NOT NULL,
    fecha            timestamptz NOT NULL,
    PRIMARY KEY (pedido_id, linea_num)
);

-- Sigue habiendo un problema evidente: los datos del cliente se repiten en CADA línea
-- de CADA pedido. Cambiar una ciudad son N filas; si falla a mitad, el cliente vive
-- en dos ciudades a la vez.

2FN — Segunda forma normal: nada depende de parte de la clave

Regla: estando en 1FN, todo atributo no clave depende de la clave completa. Solo puede incumplirse con claves compuestas. Aquí la clave es (pedido_id, linea_num), y sin embargo cliente_email, cliente_nombre y fecha dependen únicamente de pedido_id (dependencia parcial), mientras que producto_nombre depende únicamente de sku.

-- ✅ 2FN: separamos lo que depende de la clave completa de lo que depende de una parte
CREATE TABLE pedido_2fn (
    pedido_id      int PRIMARY KEY,
    cliente_email  text NOT NULL,
    cliente_nombre text NOT NULL,
    cliente_ciudad text NOT NULL,
    cliente_cp     text NOT NULL,
    fecha          timestamptz NOT NULL
);

CREATE TABLE linea_2fn (
    pedido_id       int NOT NULL REFERENCES pedido_2fn(pedido_id),
    linea_num       smallint NOT NULL,
    sku             text NOT NULL,
    cantidad        int NOT NULL,
    precio_unitario numeric(10,2) NOT NULL,   -- ← depende de la clave completa: OK
    PRIMARY KEY (pedido_id, linea_num)
);

CREATE TABLE producto_2fn (
    sku    text PRIMARY KEY,
    nombre text NOT NULL
);

3FN — Tercera forma normal: ningún atributo depende de otro no clave

Regla: estando en 2FN, no hay dependencias transitivas: ningún atributo no clave determina a otro atributo no clave. En pedido_2fn, cliente_email determina cliente_nombre, cliente_ciudad y cliente_cp. El cliente es una entidad y hay que sacarla.

-- ✅ 3FN: la entidad cliente sale a su propia tabla y el pedido la referencia
CREATE TABLE cliente_3fn (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email  text NOT NULL UNIQUE,
    nombre text NOT NULL,
    ciudad text NOT NULL,
    cp     text NOT NULL
);

CREATE TABLE pedido_3fn (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    cliente_id bigint NOT NULL REFERENCES cliente_3fn(id),
    fecha      timestamptz NOT NULL DEFAULT now()
);

-- Las tres anomalías han desaparecido:
--   · Modificación: la ciudad del cliente está en UNA fila.
--   · Inserción: puedo dar de alta un cliente sin que haya comprado nada.
--   · Borrado: al borrar su último pedido no pierdo sus datos de contacto.

3FN es el objetivo por defecto de un esquema OLTP. Con la inmensa mayoría de los dominios, llegar aquí y parar es exactamente lo correcto.

BCNF — Forma normal de Boyce-Codd: el caso raro pero real

Regla: todo determinante debe ser una superclave. Es una 3FN más estricta y solo se incumple cuando hay varias claves candidatas solapadas. Ejemplo con el dominio: un producto se sirve desde almacenes, y la regla de negocio dice que cada producto tiene un único responsable por almacén, y además cada responsable trabaja en un solo almacén.

-- Tabla en 3FN pero NO en BCNF
CREATE TABLE asignacion (
    producto_id   bigint NOT NULL,
    almacen_id    int    NOT NULL,
    responsable   text   NOT NULL,
    PRIMARY KEY (producto_id, almacen_id)
);
-- Dependencias funcionales:
--   (producto_id, almacen_id) -> responsable      (la clave declarada)
--   responsable               -> almacen_id       ← determinante que NO es superclave
--
-- Consecuencia: el hecho "Marta trabaja en el almacén de Getafe" se repite en cada
-- producto que gestione. Si Marta cambia de almacén hay que actualizar N filas, y si
-- Marta no gestiona ningún producto todavía, el hecho no se puede registrar.

-- ✅ BCNF: cada determinante manda en su propia tabla
CREATE TABLE responsable (
    nombre     text PRIMARY KEY,
    almacen_id int NOT NULL REFERENCES almacen(id)
);
CREATE TABLE asignacion_bcnf (
    producto_id bigint NOT NULL REFERENCES producto(id),
    responsable text   NOT NULL REFERENCES responsable(nombre),
    PRIMARY KEY (producto_id, responsable)
);

Desnormalización controlada: cuándo y cómo

Desnormalizar es introducir redundancia a propósito para evitar un coste de lectura medido. Las tres palabras importantes son «a propósito» y «medido». Desnormalizar sin haber visto un plan de ejecución es simplemente un error de diseño con buena prensa.

-- ✅ Columna generada: la mantiene el motor, no puede divergir NUNCA.
--    Es la forma más segura de desnormalizar en PostgreSQL 12+.
ALTER TABLE linea_pedido
    ADD COLUMN importe numeric(12,2)
    GENERATED ALWAYS AS (cantidad * precio_unitario * (1 - descuento)) STORED;

-- ✅ Total del pedido mantenido por trigger: coherente en la misma transacción
CREATE OR REPLACE FUNCTION recalcular_total_pedido() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE pedido p
       SET total = COALESCE((SELECT sum(l.importe)
                               FROM linea_pedido l
                              WHERE l.pedido_id = p.id), 0)
     WHERE p.id = COALESCE(NEW.pedido_id, OLD.pedido_id);
    RETURN NULL;
END $$;

CREATE TRIGGER linea_pedido_total_trg
AFTER INSERT OR UPDATE OR DELETE ON linea_pedido
FOR EACH ROW EXECUTE FUNCTION recalcular_total_pedido();

-- ⚠️ Y la consulta de auditoría que NADIE escribe y que siempre hace falta:
--    ¿el total desnormalizado sigue cuadrando con las líneas?
SELECT p.id, p.total AS total_guardado, sum(l.importe) AS total_real
FROM   pedido p
JOIN   linea_pedido l ON l.pedido_id = p.id
GROUP  BY p.id, p.total
HAVING p.total <> sum(l.importe);

Tipos de datos: la decisión que más bugs silenciosos evita

-- Demostración de por qué el dinero no va en coma flotante
SELECT 0.1::real + 0.2::real            AS con_real,       -- 0.3 (redondeado al mostrar)
       (0.1::real + 0.2::real) = 0.3    AS parece_igual,   -- false
       0.1::numeric + 0.2::numeric      AS con_numeric,    -- 0.3
       (0.1::numeric + 0.2::numeric) = 0.3 AS es_igual;    -- true

-- Demostración de por qué timestamptz y no timestamp
SET TIME ZONE 'Europe/Madrid';
CREATE TEMP TABLE prueba_tz (con_zona timestamptz, sin_zona timestamp);
INSERT INTO prueba_tz VALUES ('2026-03-29 02:30:00+01', '2026-03-29 02:30:00');
SET TIME ZONE 'UTC';
SELECT * FROM prueba_tz;
-- con_zona se muestra como 01:30 UTC: es el MISMO instante visto desde otra zona.
-- sin_zona sigue diciendo 02:30: no significa nada sin saber dónde se generó.

-- Cómo se trabaja bien con el tiempo
SELECT now()                                       AS instante_absoluto,
       now() AT TIME ZONE 'Europe/Madrid'          AS reloj_de_pared_en_madrid,
       date_trunc('day', now() AT TIME ZONE 'Europe/Madrid') AS inicio_dia_local,
       (now() - interval '30 days')                AS hace_30_dias;

-- ❌ Filtro que rompe el índice y además es ambiguo con las zonas
SELECT * FROM pedido WHERE date(creado_en) = '2026-04-03';

-- ✅ Rango semiabierto: usa el índice y no deja fuera los pedidos de las 23:59:59.7
SELECT * FROM pedido
WHERE  creado_en >= '2026-04-03 00:00:00+02'
  AND  creado_en <  '2026-04-04 00:00:00+02';

enum nativo, CHECK o tabla de referencia

-- ✅ Estados de pedido: CHECK, porque van a cambiar y quiero leerlos en los volcados
ALTER TABLE pedido
    ADD CONSTRAINT pedido_estado_ck
    CHECK (estado IN ('NUEVO','PAGADO','ENVIADO','ENTREGADO','CANCELADO','DEVUELTO'));

-- Añadir un estado nuevo sin bloquear la tabla para escrituras largas:
ALTER TABLE pedido DROP CONSTRAINT pedido_estado_ck;
ALTER TABLE pedido ADD CONSTRAINT pedido_estado_ck
    CHECK (estado IN ('NUEVO','PAGADO','ENVIADO','ENTREGADO','CANCELADO','DEVUELTO','REEMBOLSADO'))
    NOT VALID;                                   -- no revisa las filas existentes: instantáneo
ALTER TABLE pedido VALIDATE CONSTRAINT pedido_estado_ck;   -- revisa en segundo plano, con lock suave

-- ✅ Tabla de referencia cuando hay metadatos que el negocio gestiona
CREATE TABLE metodo_pago (
    codigo    text PRIMARY KEY,
    etiqueta  text NOT NULL,
    comision  numeric(5,4) NOT NULL DEFAULT 0,
    activo    boolean NOT NULL DEFAULT true,
    orden     smallint NOT NULL DEFAULT 0
);
INSERT INTO metodo_pago (codigo, etiqueta, comision) VALUES
    ('TARJETA','Tarjeta de crédito',0.0140),
    ('SEPA','Domiciliación SEPA',0.0035),
    ('PAYPAL','PayPal',0.0290),
    ('TRANSFER','Transferencia',0.0000);

jsonb: cuándo sí y cuándo es una trampa

-- Modelo híbrido bien planteado: lo estructurado en columnas, lo variable en jsonb
CREATE TABLE producto (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku          text NOT NULL UNIQUE,
    nombre       text NOT NULL,
    categoria_id int  NOT NULL REFERENCES categoria(id),
    precio       numeric(10,2) NOT NULL CHECK (precio >= 0),
    stock        int NOT NULL DEFAULT 0 CHECK (stock >= 0),
    activo       boolean NOT NULL DEFAULT true,
    atributos    jsonb NOT NULL DEFAULT '{}'::jsonb,     -- talla, color, voltaje...
    creado_en    timestamptz NOT NULL DEFAULT now()
);

-- Operadores de jsonb que hay que conocer
SELECT atributos ->  'color'          AS color_json,   -- devuelve jsonb
       atributos ->> 'color'          AS color_texto,  -- devuelve text
       atributos #>> '{envio,plazo}'  AS plazo,        -- ruta anidada como texto
       atributos ?   'talla'          AS tiene_talla,  -- ¿existe la clave?
       atributos @>  '{"color":"rojo"}'::jsonb AS es_rojo   -- ¿contiene?
FROM   producto WHERE id = 1;

-- Índice GIN para búsquedas por contención dentro del jsonb
CREATE INDEX producto_atributos_gin ON producto USING gin (atributos jsonb_path_ops);
SELECT id, nombre FROM producto WHERE atributos @> '{"color":"rojo","talla":"M"}';

-- Índice B-tree sobre UNA clave concreta muy consultada (más pequeño y sirve para rangos)
CREATE INDEX producto_voltaje_idx ON producto ((atributos ->> 'voltaje'));

-- Y si esa clave se consulta en TODAS las pantallas, promuévela a columna de verdad:
ALTER TABLE producto ADD COLUMN color text;
UPDATE producto SET color = atributos ->> 'color' WHERE atributos ? 'color';
-- ...y valida lo que queda dentro del jsonb con un CHECK, para que no crezca sin control
ALTER TABLE producto ADD CONSTRAINT producto_atributos_ck
    CHECK (jsonb_typeof(atributos) = 'object');

Arrays y rangos: dos tipos infravalorados

-- Arrays: cómodos para conjuntos pequeños y cerrados que NO necesitan integridad
ALTER TABLE producto ADD COLUMN etiquetas text[] NOT NULL DEFAULT '{}';
UPDATE producto SET etiquetas = ARRAY['oferta','novedad'] WHERE id = 1;

CREATE INDEX producto_etiquetas_gin ON producto USING gin (etiquetas);
SELECT * FROM producto WHERE etiquetas @> ARRAY['oferta'];       -- contiene
SELECT * FROM producto WHERE 'novedad' = ANY(etiquetas);          -- pertenencia
SELECT unnest(etiquetas) AS etiqueta, count(*) FROM producto GROUP BY 1;  -- desplegar

-- ⚠️ Un array NO puede tener FK. Si las etiquetas son una entidad con nombre
--    traducible, color y estado, necesitas tabla de unión. El array es para
--    conjuntos de valores, no de referencias.

-- Rangos + EXCLUDE: la restricción que evita el bug de las reservas solapadas
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE precio_producto (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    producto_id bigint NOT NULL REFERENCES producto(id) ON DELETE CASCADE,
    precio      numeric(10,2) NOT NULL,
    vigencia    daterange NOT NULL,
    EXCLUDE USING gist (producto_id WITH =, vigencia WITH &&)   -- sin solapamientos
);

INSERT INTO precio_producto (producto_id, precio, vigencia)
VALUES (1, 19.90, daterange('2026-01-01','2026-07-01','[)'));
INSERT INTO precio_producto (producto_id, precio, vigencia)
VALUES (1, 24.90, daterange('2026-06-01','2026-12-31','[)'));
-- ERROR: conflicting key value violates exclusion constraint
-- El motor acaba de impedir un bug que la aplicación habría cometido en producción.

-- Consulta del precio vigente hoy
SELECT precio FROM precio_producto
WHERE producto_id = 1 AND vigencia @> CURRENT_DATE;

Restricciones: la única validación que nadie puede saltarse

La validación en la aplicación es imprescindible para dar mensajes de error decentes, pero no es una garantía: hay un batch nocturno, un script de soporte, una consola de administración, una migración y un becario con acceso a psql. La restricción en la base de datos es la última línea de defensa y la única que se cumple siempre.

-- Restricciones que resuelven reglas de negocio reales, no ejemplos de manual

-- 1) Un cliente solo puede tener UN carrito en estado abierto
CREATE UNIQUE INDEX carrito_abierto_uk ON carrito (cliente_id) WHERE estado = 'ABIERTO';

-- 2) El email se guarda tal cual (respetando mayúsculas) pero es único sin distinguirlas
CREATE UNIQUE INDEX cliente_email_lower_uk ON cliente (lower(email));

-- 3) Coherencia entre columnas de la misma fila
ALTER TABLE envio ADD CONSTRAINT envio_fechas_ck
    CHECK (entregado_en IS NULL OR enviado_en IS NULL OR entregado_en >= enviado_en);

-- 4) Reglas condicionales: si está cancelado, tiene que haber motivo
ALTER TABLE pedido ADD CONSTRAINT pedido_cancelacion_ck
    CHECK (estado <> 'CANCELADO' OR motivo_cancelacion IS NOT NULL);

-- 5) Al menos un canal de contacto obligatorio, sin exigir los dos
ALTER TABLE cliente ADD CONSTRAINT cliente_contacto_ck
    CHECK (email IS NOT NULL OR telefono IS NOT NULL);

-- 6) Restricción aplazada: el total debe cuadrar al COMMIT, no en cada paso intermedio
ALTER TABLE pedido ADD CONSTRAINT pedido_total_positivo_ck
    CHECK (total >= 0);
ALTER TABLE linea_pedido
    ADD CONSTRAINT linea_pedido_fk FOREIGN KEY (pedido_id) REFERENCES pedido(id)
    DEFERRABLE INITIALLY IMMEDIATE;
-- En la transacción que necesite insertar en orden inverso:
--   SET CONSTRAINTS linea_pedido_fk DEFERRED;

Auditoría, histórico y soft delete

«¿Quién cambió este precio y cuándo?» es una pregunta que llega tarde o temprano, y si el esquema no la previó, la respuesta es «no se puede saber». Hay tres niveles de solución, con coste creciente.

-- NIVEL 1: columnas de auditoría en la propia tabla. Barato; solo sabes el ÚLTIMO cambio.
ALTER TABLE producto
    ADD COLUMN creado_en       timestamptz NOT NULL DEFAULT now(),
    ADD COLUMN creado_por      text,
    ADD COLUMN modificado_en   timestamptz,
    ADD COLUMN modificado_por  text;

CREATE OR REPLACE FUNCTION marcar_modificacion() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    NEW.modificado_en  := now();
    NEW.modificado_por := current_setting('app.usuario', true);   -- lo fija la aplicación
    RETURN NEW;
END $$;

CREATE TRIGGER producto_mod_trg BEFORE UPDATE ON producto
FOR EACH ROW EXECUTE FUNCTION marcar_modificacion();

-- NIVEL 2: tabla de auditoría genérica. Registro completo de quién, cuándo y qué cambió.
CREATE TABLE auditoria (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tabla      text        NOT NULL,
    fila_id    text        NOT NULL,
    operacion  char(1)     NOT NULL CHECK (operacion IN ('I','U','D')),
    antes      jsonb,
    despues    jsonb,
    cambiado_por text,
    cambiado_en  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX auditoria_tabla_fila_idx ON auditoria (tabla, fila_id, cambiado_en DESC);

CREATE OR REPLACE FUNCTION auditar() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE v_id text;
BEGIN
    v_id := COALESCE(to_jsonb(NEW) ->> 'id', to_jsonb(OLD) ->> 'id');
    INSERT INTO auditoria (tabla, fila_id, operacion, antes, despues, cambiado_por)
    VALUES (TG_TABLE_NAME, v_id, left(TG_OP,1),
            CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN to_jsonb(OLD) END,
            CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN to_jsonb(NEW) END,
            current_setting('app.usuario', true));
    RETURN NULL;
END $$;

CREATE TRIGGER producto_audit_trg AFTER INSERT OR UPDATE OR DELETE ON producto
FOR EACH ROW EXECUTE FUNCTION auditar();

-- NIVEL 3: histórico temporal (versionado por rangos). La opción cara y completa:
-- permite reconstruir el estado exacto del catálogo en cualquier instante del pasado.
CREATE TABLE producto_historia (
    producto_id bigint NOT NULL,
    precio      numeric(10,2) NOT NULL,
    nombre      text NOT NULL,
    validez     tstzrange NOT NULL,
    PRIMARY KEY (producto_id, validez),
    EXCLUDE USING gist (producto_id WITH =, validez WITH &&)
);

-- ¿Cómo era el catálogo el 15 de marzo a las 12:00?
SELECT producto_id, nombre, precio
FROM   producto_historia
WHERE  validez @> '2026-03-15 12:00:00+01'::timestamptz;

Soft delete: la decisión que contamina todas las consultas

-- Si haces soft delete, hazlo completo. La mitad de las implementaciones olvidan
-- alguno de estos cuatro elementos:

-- 1) La columna, con marca temporal (no un boolean: querrás saber cuándo)
ALTER TABLE cliente ADD COLUMN borrado_en timestamptz;

-- 2) Unicidad que solo aplica a los vivos
DROP INDEX IF EXISTS cliente_email_lower_uk;
CREATE UNIQUE INDEX cliente_email_activo_uk
    ON cliente (lower(email)) WHERE borrado_en IS NULL;

-- 3) Índice parcial para las consultas normales (más pequeño y más rápido)
CREATE INDEX cliente_activo_idx ON cliente (creado_en DESC) WHERE borrado_en IS NULL;

-- 4) Una vista que sea el camino por defecto, para que olvidarse sea difícil
CREATE VIEW cliente_activo AS SELECT * FROM cliente WHERE borrado_en IS NULL;

-- ✅ Alternativa muchas veces mejor: mover a una tabla de archivo.
--    Las consultas del día a día no pagan nada y el histórico se conserva.
CREATE TABLE cliente_archivado (LIKE cliente INCLUDING ALL);
ALTER TABLE cliente_archivado ADD COLUMN archivado_en timestamptz NOT NULL DEFAULT now();

WITH movidos AS (
    DELETE FROM cliente WHERE id = 4711 RETURNING *
)
INSERT INTO cliente_archivado SELECT *, now() FROM movidos;

El esquema de referencia: diagrama ER y DDL completo

Este es el esquema que usa todo el resto del módulo. Créalo en tu PostgreSQL local (ejercicio 1) y ejecuta cada consulta que aparezca de aquí en adelante.

DIAGRAMA ER — TIENDA EN LÍNEA

  ┌──────────────────────┐            ┌───────────────────────┐
  │       cliente        │            │        pais           │
  ├──────────────────────┤            ├───────────────────────┤
  │ PK id        bigint  │      ┌────▶│ PK codigo    char(2)  │
  │ UK email     text    │      │     │    nombre    text     │
  │ UK nif       text    │      │     │    iva       numeric  │
  │    nombre    text    │      │     └───────────────────────┘
  │ FK pais      char(2) ├──────┘
  │    activo    bool    │
  │    creado_en tstz    │
  └──────────┬───────────┘
             │ 1
             │
             │ N
  ┌──────────▼───────────┐            ┌───────────────────────┐
  │       pedido         │            │      categoria        │
  ├──────────────────────┤            ├───────────────────────┤
  │ PK id        bigint  │            │ PK id        int      │
  │ FK cliente_id bigint │            │    nombre    text     │
  │    estado    text    │            │ FK padre_id  int ─────┼──┐ auto-
  │    total     numeric │            └───────────┬───────────┘  │ referencia
  │    moneda    char(3) │                        │ 1        ▲───┘
  │    creado_en tstz    │                        │          │
  └──────────┬───────────┘                        │ N        │
             │ 1                       ┌──────────▼───────────┐
             │                         │       producto       │
             │ N                       ├──────────────────────┤
  ┌──────────▼───────────┐             │ PK id        bigint  │
  │    linea_pedido      │      N      │ UK sku       text    │
  ├──────────────────────┤             │    nombre    text    │
  │ PK pedido_id  bigint ├────────────▶│ FK categoria_id int  │
  │ PK linea_num  smallint│      1     │    precio    numeric │
  │ FK producto_id bigint│             │    stock     int     │
  │    cantidad   int    │             │    activo    bool    │
  │    precio_unitario   │             │    atributos jsonb   │
  │    descuento  numeric│             │    etiquetas text[]  │
  │    importe (generado)│             └──────────────────────┘
  └──────────────────────┘

  ┌──────────────────────┐            ┌───────────────────────┐
  │        pago          │            │        envio          │
  ├──────────────────────┤            ├───────────────────────┤
  │ PK id        bigint  │            │ PK id        bigint   │
  │ FK pedido_id bigint  │            │ FK pedido_id bigint   │
  │ FK metodo    text    │            │    transportista text │
  │    importe   numeric │            │    enviado_en   tstz  │
  │    estado    text    │            │    entregado_en tstz  │
  │    creado_en tstz    │            └───────────────────────┘
  └──────────────────────┘

  LEYENDA:  PK clave primaria   UK única   FK foránea   tstz = timestamptz
            ──────▶ apunta al lado "uno" de la relación

-- =============================================================================
--  ESQUEMA DE REFERENCIA DEL MÓDULO 06 — ejecútalo entero en una base vacía
-- =============================================================================
DROP SCHEMA IF EXISTS tienda CASCADE;
CREATE SCHEMA tienda;
SET search_path TO tienda, public;

CREATE TABLE pais (
    codigo char(2)      PRIMARY KEY,
    nombre text         NOT NULL,
    iva    numeric(4,2) NOT NULL CHECK (iva >= 0 AND iva <= 100)
);

CREATE TABLE cliente (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email     text        NOT NULL,
    nif       text,
    nombre    text        NOT NULL,
    pais      char(2)     NOT NULL DEFAULT 'ES' REFERENCES pais(codigo),
    activo    boolean     NOT NULL DEFAULT true,
    creado_en timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT cliente_nif_uk UNIQUE (nif)
);
CREATE UNIQUE INDEX cliente_email_uk ON cliente (lower(email));
CREATE INDEX cliente_pais_idx ON cliente (pais);

CREATE TABLE categoria (
    id       int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nombre   text NOT NULL,
    padre_id int REFERENCES categoria(id) ON DELETE RESTRICT,
    CONSTRAINT categoria_nombre_padre_uk UNIQUE (nombre, padre_id)
);
CREATE INDEX categoria_padre_idx ON categoria (padre_id);

CREATE TABLE producto (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku          text          NOT NULL UNIQUE,
    nombre       text          NOT NULL,
    categoria_id int           NOT NULL REFERENCES categoria(id),
    precio       numeric(10,2) NOT NULL CHECK (precio >= 0),
    stock        int           NOT NULL DEFAULT 0 CHECK (stock >= 0),
    activo       boolean       NOT NULL DEFAULT true,
    atributos    jsonb         NOT NULL DEFAULT '{}'::jsonb,
    etiquetas    text[]        NOT NULL DEFAULT '{}',
    creado_en    timestamptz   NOT NULL DEFAULT now()
);
CREATE INDEX producto_categoria_idx ON producto (categoria_id);

CREATE TABLE pedido (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    cliente_id bigint        NOT NULL REFERENCES cliente(id) ON DELETE RESTRICT,
    estado     text          NOT NULL DEFAULT 'NUEVO',
    total      numeric(12,2) NOT NULL DEFAULT 0 CHECK (total >= 0),
    moneda     char(3)       NOT NULL DEFAULT 'EUR',
    creado_en  timestamptz   NOT NULL DEFAULT now(),
    motivo_cancelacion text,
    CONSTRAINT pedido_estado_ck CHECK (estado IN
        ('NUEVO','PAGADO','ENVIADO','ENTREGADO','CANCELADO','DEVUELTO')),
    CONSTRAINT pedido_cancelacion_ck CHECK
        (estado <> 'CANCELADO' OR motivo_cancelacion IS NOT NULL)
);
CREATE INDEX pedido_cliente_idx    ON pedido (cliente_id);
CREATE INDEX pedido_creado_idx     ON pedido (creado_en DESC);
CREATE INDEX pedido_estado_idx     ON pedido (estado) WHERE estado IN ('NUEVO','PAGADO');

CREATE TABLE linea_pedido (
    pedido_id       bigint        NOT NULL REFERENCES pedido(id) ON DELETE CASCADE,
    linea_num       smallint      NOT NULL,
    producto_id     bigint        NOT NULL REFERENCES producto(id) ON DELETE RESTRICT,
    cantidad        int           NOT NULL CHECK (cantidad > 0),
    precio_unitario numeric(10,2) NOT NULL CHECK (precio_unitario >= 0),
    descuento       numeric(5,4)  NOT NULL DEFAULT 0 CHECK (descuento >= 0 AND descuento < 1),
    importe         numeric(12,2) GENERATED ALWAYS AS
                    (round(cantidad * precio_unitario * (1 - descuento), 2)) STORED,
    PRIMARY KEY (pedido_id, linea_num)
);
CREATE INDEX linea_producto_idx ON linea_pedido (producto_id);

CREATE TABLE metodo_pago (
    codigo   text PRIMARY KEY,
    etiqueta text NOT NULL,
    comision numeric(5,4) NOT NULL DEFAULT 0,
    activo   boolean NOT NULL DEFAULT true
);

CREATE TABLE pago (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    pedido_id bigint        NOT NULL REFERENCES pedido(id) ON DELETE CASCADE,
    metodo    text          NOT NULL REFERENCES metodo_pago(codigo),
    importe   numeric(12,2) NOT NULL CHECK (importe > 0),
    estado    text          NOT NULL DEFAULT 'PENDIENTE'
                            CHECK (estado IN ('PENDIENTE','OK','FALLIDO','DEVUELTO')),
    referencia text UNIQUE,          -- idempotencia frente a la pasarela
    creado_en timestamptz   NOT NULL DEFAULT now()
);
CREATE INDEX pago_pedido_idx ON pago (pedido_id);

CREATE TABLE envio (
    id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    pedido_id     bigint      NOT NULL REFERENCES pedido(id) ON DELETE CASCADE,
    transportista text        NOT NULL,
    enviado_en    timestamptz,
    entregado_en  timestamptz,
    CONSTRAINT envio_fechas_ck CHECK
        (entregado_en IS NULL OR enviado_en IS NULL OR entregado_en >= enviado_en)
);
CREATE INDEX envio_pedido_idx ON envio (pedido_id);

-- Semilla mínima para que las consultas del módulo devuelvan algo
INSERT INTO pais (codigo, nombre, iva) VALUES
    ('ES','España',21.00), ('PT','Portugal',23.00), ('FR','Francia',20.00),
    ('DE','Alemania',19.00), ('IT','Italia',22.00);

INSERT INTO metodo_pago (codigo, etiqueta, comision) VALUES
    ('TARJETA','Tarjeta',0.0140), ('SEPA','Domiciliación',0.0035),
    ('PAYPAL','PayPal',0.0290), ('TRANSFER','Transferencia',0.0000);

INSERT INTO categoria (nombre, padre_id) VALUES ('Informática', NULL);
INSERT INTO categoria (nombre, padre_id) VALUES ('Periféricos', 1), ('Monitores', 1);
INSERT INTO categoria (nombre, padre_id) VALUES ('Teclados', 2), ('Ratones', 2);

3 · SQL de consulta: dominar el lenguaje

SQL es declarativo: describes qué resultado quieres y el planificador decide cómo obtenerlo. Eso es su gran virtud y también la causa de la mayoría de los malentendidos, porque el orden en el que escribes las cláusulas no es el orden en el que se evalúan.

El orden lógico de evaluación (la clave de todo)

ORDEN EN QUE SE ESCRIBE            ORDEN EN QUE SE EVALÚA (conceptualmente)

  SELECT   columnas          (5)      1. FROM / JOIN   → construye el conjunto de filas
  FROM     tabla             (1)      2. WHERE         → descarta filas individuales
  JOIN     otra ON ...       (1)      3. GROUP BY      → agrupa las que quedan
  WHERE    condición         (2)      4. HAVING        → descarta GRUPOS
  GROUP BY expresión         (3)      5. SELECT        → calcula expresiones y alias
  HAVING   condición         (4)      6. DISTINCT      → elimina duplicados del resultado
  ORDER BY expresión         (7)      7. ORDER BY      → ordena el resultado final
  LIMIT    n OFFSET m        (8)      8. LIMIT/OFFSET  → recorta

        ┌──────┐   ┌───────┐   ┌──────────┐   ┌────────┐
  datos │ FROM │──▶│ WHERE │──▶│ GROUP BY │──▶│ HAVING │──┐
        └──────┘   └───────┘   └──────────┘   └────────┘  │
                                                          ▼
        ┌───────┐   ┌──────────┐   ┌──────────┐   ┌────────┐
   ◀────│ LIMIT │◀──│ ORDER BY │◀──│ DISTINCT │◀──│ SELECT │
        └───────┘   └──────────┘   └──────────┘   └────────┘

Las funciones de ventana se evalúan entre SELECT y DISTINCT: ven el resultado
DESPUÉS de agrupar, y por eso no se pueden usar en WHERE ni en GROUP BY.

Este diagrama explica de un golpe los cuatro errores más frecuentes de SQL:

-- ❌ ERROR 1: usar un alias del SELECT en el WHERE.
--    Cuando se evalúa WHERE (paso 2) el alias todavía no existe (paso 5).
SELECT total * 1.21 AS total_con_iva
FROM   pedido
WHERE  total_con_iva > 100;             -- ERROR: column "total_con_iva" does not exist

-- ✅ Repite la expresión, o envuelve en una subconsulta/CTE
SELECT total * 1.21 AS total_con_iva FROM pedido WHERE total * 1.21 > 100;

-- ✅ En ORDER BY sí funciona el alias, porque se evalúa en el paso 7
SELECT total * 1.21 AS total_con_iva FROM pedido ORDER BY total_con_iva DESC;

-- ❌ ERROR 2: filtrar un agregado en WHERE
SELECT cliente_id, count(*) FROM pedido
WHERE  count(*) > 5                      -- ERROR: aggregate functions are not allowed in WHERE
GROUP  BY cliente_id;

-- ✅ HAVING filtra grupos (paso 4), WHERE filtra filas (paso 2)
SELECT cliente_id, count(*) FROM pedido GROUP BY cliente_id HAVING count(*) > 5;

-- ❌ ERROR 3: creer que LIMIT limita el trabajo del GROUP BY.
--    LIMIT es el paso 8: la agregación ya ha recorrido TODAS las filas.
SELECT cliente_id, sum(total) FROM pedido GROUP BY cliente_id LIMIT 10;

-- ❌ ERROR 4: filtrar la tabla externa de un LEFT JOIN en el WHERE.
--    El WHERE se aplica DESPUÉS del join y convierte el LEFT en INNER.
SELECT c.nombre, p.id
FROM   cliente c
LEFT   JOIN pedido p ON p.cliente_id = c.id
WHERE  p.creado_en >= '2026-01-01';      -- los clientes sin pedidos desaparecen

-- ✅ La condición sobre la tabla externa va en el ON
SELECT c.nombre, p.id
FROM   cliente c
LEFT   JOIN pedido p ON p.cliente_id = c.id AND p.creado_en >= '2026-01-01';

JOINs: los seis tipos y cuándo usar cada uno

                  A = cliente          B = pedido

  INNER JOIN              LEFT JOIN               RIGHT JOIN
  ┌─────┐  ┌─────┐        ┌─────┐  ┌─────┐        ┌─────┐  ┌─────┐
  │  A  │██│  B  │        │█████│██│  B  │        │  A  │██│█████│
  └─────┘  └─────┘        └─────┘  └─────┘        └─────┘  └─────┘
  solo lo que coincide    todo A + lo que          todo B + lo que
  en ambos lados          coincide de B            coincide de A

  FULL OUTER JOIN         CROSS JOIN              LATERAL / self-join
  ┌─────┐  ┌─────┐        A × B                   A ──┐
  │█████│██│█████│        cada fila de A con      ▲    │  la derecha ve
  └─────┘  └─────┘        cada fila de B          └────┘  cada fila de la izquierda
  todo, coincida o no     (producto cartesiano)

-- Datos de trabajo para ver las diferencias con claridad:
--   cliente 1 Ana   → 2 pedidos      cliente 3 Carlos → 0 pedidos
--   cliente 2 Bruno → 1 pedido       pedido 99        → cliente_id inexistente (imposible con FK)

-- 1) INNER JOIN: la intersección. Es el 80% de los joins que escribirás.
SELECT c.nombre, p.id AS pedido, p.total
FROM   cliente c
JOIN   pedido  p ON p.cliente_id = c.id;
-- Ana(2 filas), Bruno(1 fila). Carlos NO aparece.

-- 2) LEFT JOIN: todo lo de la izquierda; NULL donde no hay pareja.
--    Imprescindible para "clientes y su número de pedidos, incluidos los que no tienen".
SELECT c.nombre, count(p.id) AS pedidos
FROM   cliente c
LEFT   JOIN pedido p ON p.cliente_id = c.id
GROUP  BY c.id, c.nombre
ORDER  BY pedidos DESC;
-- Ana 2, Bruno 1, Carlos 0.
-- ⚠️ count(p.id) cuenta filas con valor: da 0 para Carlos.
--    count(*) contaría la fila del LEFT JOIN y daría 1. Es el bug clásico.

-- 3) LEFT JOIN + IS NULL: el "anti-join". Encuentra lo que NO tiene pareja.
SELECT c.id, c.nombre
FROM   cliente c
LEFT   JOIN pedido p ON p.cliente_id = c.id
WHERE  p.id IS NULL;                      -- clientes que nunca han comprado

-- 4) RIGHT JOIN: idéntico al LEFT con las tablas intercambiadas.
--    Existe por simetría; en la práctica se usa poco porque se lee peor.
SELECT c.nombre, p.id
FROM   pedido p
RIGHT  JOIN cliente c ON p.cliente_id = c.id;   -- equivale al LEFT de arriba

-- 5) FULL OUTER JOIN: todo de los dos lados. Útil para conciliar dos fuentes.
SELECT COALESCE(a.referencia, b.referencia) AS referencia,
       a.importe AS importe_banco,
       b.importe AS importe_sistema,
       CASE WHEN a.referencia IS NULL THEN 'FALTA EN SISTEMA'
            WHEN b.referencia IS NULL THEN 'FALTA EN BANCO'
            WHEN a.importe <> b.importe THEN 'DESCUADRE'
            ELSE 'OK' END AS diagnostico
FROM   extracto_banco a
FULL   OUTER JOIN pago b ON b.referencia = a.referencia;

-- 6) CROSS JOIN: producto cartesiano. Casi siempre es un error... salvo cuando
--    quieres generar combinaciones deliberadamente (una fila por mes y país).
SELECT p.codigo, m.mes
FROM   pais p
CROSS  JOIN generate_series(1, 12) AS m(mes);   -- 5 países × 12 meses = 60 filas

-- El uso más valioso: rejilla completa de meses × categorías con ceros incluidos
SELECT g.mes::date AS mes, cat.nombre, COALESCE(sum(l.importe), 0) AS facturado
FROM   generate_series('2026-01-01'::date, '2026-12-01'::date, interval '1 month') AS g(mes)
CROSS  JOIN categoria cat
LEFT   JOIN producto pr    ON pr.categoria_id = cat.id
LEFT   JOIN linea_pedido l ON l.producto_id = pr.id
LEFT   JOIN pedido pe      ON pe.id = l.pedido_id
                          AND date_trunc('month', pe.creado_en) = g.mes
GROUP  BY g.mes, cat.nombre
ORDER  BY g.mes, cat.nombre;

Self-join: la misma tabla dos veces

-- Jerarquía de categorías: cada categoría con el nombre de su padre
SELECT hija.id, hija.nombre AS categoria, padre.nombre AS padre
FROM   categoria hija
LEFT   JOIN categoria padre ON padre.id = hija.padre_id
ORDER  BY padre.nombre NULLS FIRST, hija.nombre;

-- Comparar una fila con otra de la misma tabla: pedidos duplicados sospechosos
-- (mismo cliente, mismo importe, menos de 5 minutos de diferencia)
SELECT a.id AS pedido_a, b.id AS pedido_b, a.cliente_id, a.total,
       b.creado_en - a.creado_en AS diferencia
FROM   pedido a
JOIN   pedido b ON b.cliente_id = a.cliente_id
               AND b.total      = a.total
               AND b.id        > a.id                    -- evita parejas repetidas y a=a
               AND b.creado_en < a.creado_en + interval '5 minutes'
ORDER  BY a.cliente_id, a.creado_en;

LATERAL: el join que puede mirar a la izquierda

En un join normal, las dos partes se evalúan de forma independiente y luego se combinan. Con LATERAL, la subconsulta de la derecha puede referenciar columnas de la izquierda, de modo que se ejecuta una vez por cada fila externa. Es la herramienta idiomática para «los N mejores por grupo» y para llamar a funciones que devuelven conjuntos.

JOIN NORMAL                          LATERAL JOIN

 A ──┐                                A ──┐
     ├─▶ combina                          │  para CADA fila de A
 B ──┘                                    ├─▶ ejecuta B(fila) y combina
                                          │
 B no sabe nada de A                  B recibe los valores de A

Coste: LATERAL ejecuta N subconsultas (una por fila externa). Con un índice
adecuado cada una cuesta microsegundos y gana a cualquier alternativa. Sin
índice, es un N+1 dentro de la base de datos.

-- Los 3 últimos pedidos de cada cliente activo: el caso de uso estrella de LATERAL
SELECT c.id, c.nombre, p.id AS pedido, p.creado_en, p.total
FROM   cliente c
CROSS  JOIN LATERAL (
           SELECT p.id, p.creado_en, p.total
           FROM   pedido p
           WHERE  p.cliente_id = c.id          -- ← referencia a la fila externa
           ORDER  BY p.creado_en DESC
           LIMIT  3
       ) p
WHERE  c.activo
ORDER  BY c.nombre, p.creado_en DESC;
-- Necesita el índice: CREATE INDEX ON pedido (cliente_id, creado_en DESC);

-- Con LEFT JOIN LATERAL ... ON true se conservan los clientes sin pedidos
SELECT c.nombre, p.id AS ultimo_pedido, p.total
FROM   cliente c
LEFT   JOIN LATERAL (
           SELECT p.id, p.total FROM pedido p
           WHERE p.cliente_id = c.id ORDER BY p.creado_en DESC LIMIT 1
       ) p ON true;

-- Desplegar un array o un jsonb por fila: LATERAL implícito con funciones de conjunto
SELECT pr.sku, e.etiqueta
FROM   producto pr
CROSS  JOIN LATERAL unnest(pr.etiquetas) AS e(etiqueta);

SELECT pr.sku, clave, valor
FROM   producto pr
CROSS  JOIN LATERAL jsonb_each_text(pr.atributos) AS a(clave, valor);

-- Calcular varias métricas correlacionadas SIN repetir la subconsulta tres veces
SELECT c.nombre, m.pedidos, m.gastado, m.ultimo
FROM   cliente c
CROSS  JOIN LATERAL (
           SELECT count(*)          AS pedidos,
                  COALESCE(sum(total),0) AS gastado,
                  max(creado_en)    AS ultimo
           FROM   pedido p WHERE p.cliente_id = c.id
       ) m
WHERE  m.pedidos > 0
ORDER  BY m.gastado DESC;

NULL: lógica de tres valores y la trampa de NOT IN

NULL no es un valor: es la ausencia de valor, «no se sabe». Cualquier comparación con algo desconocido da desconocido, y WHERE solo deja pasar lo que es verdadero. De ahí salen bugs que no lanzan ningún error y que simplemente devuelven menos filas de las que debían.

TABLAS DE VERDAD CON TRES VALORES (V = true, F = false, D = desconocido)

   AND │ V  F  D          OR  │ V  F  D          NOT │
   ────┼─────────         ────┼─────────         ────┼───
    V  │ V  F  D           V  │ V  V  V           V  │ F
    F  │ F  F  F           F  │ V  F  D           F  │ V
    D  │ D  F  D           D  │ V  D  D           D  │ D

REGLA DE ORO:  NULL = NULL          → D  (¡no true!)
               NULL <> NULL         → D  (¡no false!)
               NULL + 1             → NULL
               'texto' || NULL      → NULL
               WHERE <D>            → la fila NO sale

-- ❌ Nunca compares con = o <> contra NULL
SELECT * FROM cliente WHERE nif = NULL;      -- 0 filas SIEMPRE, aunque haya NULL
SELECT * FROM cliente WHERE nif <> NULL;     -- 0 filas SIEMPRE

-- ✅ Los operadores correctos
SELECT * FROM cliente WHERE nif IS NULL;
SELECT * FROM cliente WHERE nif IS NOT NULL;

-- ✅ Comparación que trata NULL como un valor más (muy útil para detectar cambios)
SELECT * FROM cliente WHERE nif IS DISTINCT FROM '12345678Z';
--    'IS DISTINCT FROM' devuelve true si uno es NULL y el otro no.
--    'IS NOT DISTINCT FROM' es el "= seguro contra NULL".

-- ⚠️ LA TRAMPA CLÁSICA: NOT IN con un NULL en la lista
-- Supongamos que producto.descatalogado_por puede ser NULL.
SELECT * FROM pedido
WHERE  cliente_id NOT IN (SELECT id FROM cliente WHERE nif IS NULL);
-- Si la subconsulta devuelve algún NULL, el resultado es SIEMPRE 0 filas.
-- Por qué: 'x NOT IN (a, b, NULL)' se expande a
--          x <> a AND x <> b AND x <> NULL
--       →  V      AND V      AND D        →  D  →  la fila no sale.

-- ✅ Solución 1 (la mejor): NOT EXISTS, que es inmune a los NULL
SELECT p.* FROM pedido p
WHERE  NOT EXISTS (SELECT 1 FROM cliente c WHERE c.id = p.cliente_id AND c.nif IS NULL);

-- ✅ Solución 2: filtrar los NULL de la subconsulta explícitamente
SELECT * FROM pedido
WHERE  cliente_id NOT IN (SELECT id FROM cliente WHERE nif IS NULL AND id IS NOT NULL);

-- Otras sorpresas útiles de conocer:
SELECT count(*)      FROM cliente;         -- cuenta filas
SELECT count(nif)    FROM cliente;         -- cuenta filas con nif NO nulo
SELECT avg(descuento) FROM linea_pedido;   -- ignora los NULL: el divisor cambia
SELECT sum(importe)  FROM linea_pedido WHERE false;   -- devuelve NULL, no 0

-- UNIQUE permite varios NULL (son "desconocidos distintos"):
--   por eso puedes tener 500 clientes sin NIF con CONSTRAINT UNIQUE (nif).
-- Desde PostgreSQL 15 se puede exigir lo contrario:
--   CREATE UNIQUE INDEX ... NULLS NOT DISTINCT;

-- ORDER BY: en Postgres los NULL van al final en ASC. Contrólalo explícitamente.
SELECT nombre, nif FROM cliente ORDER BY nif ASC NULLS FIRST;

COALESCE, NULLIF y compañía

-- COALESCE: primer argumento no nulo. Para valores por defecto en la consulta.
SELECT c.nombre,
       COALESCE(c.nif, 'SIN NIF')                       AS nif_mostrable,
       COALESCE(sum(p.total), 0)                        AS gastado
FROM   cliente c LEFT JOIN pedido p ON p.cliente_id = c.id
GROUP  BY c.id, c.nombre, c.nif;

-- NULLIF: convierte un valor en NULL. Su uso canónico es evitar la división por cero.
SELECT categoria_id,
       sum(l.importe) / NULLIF(sum(l.cantidad), 0) AS precio_medio   -- NULL si no hay unidades
FROM   linea_pedido l JOIN producto p ON p.id = l.producto_id
GROUP  BY categoria_id;

-- NULLIF también sirve para normalizar cadenas vacías que llegan de formularios
UPDATE cliente SET nif = NULLIF(trim(nif), '');

-- GREATEST / LEAST ignoran los NULL (¡ojo, distinto de max/min sobre filas!)
SELECT GREATEST(enviado_en, entregado_en) AS ultimo_hito FROM envio;

-- Y el error de concatenación más habitual en informes:
SELECT 'Cliente: ' || nombre || ' (' || nif || ')' FROM cliente;   -- ❌ NULL si nif es NULL
SELECT concat('Cliente: ', nombre, ' (', nif, ')') FROM cliente;   -- ✅ concat ignora NULL
SELECT format('Cliente: %s (%s)', nombre, COALESCE(nif,'s/n')) FROM cliente;  -- ✅ explícito

Agregaciones, GROUP BY y FILTER

-- LA REGLA: toda columna del SELECT que no esté dentro de una función de agregación
-- tiene que estar en el GROUP BY (o ser funcionalmente dependiente de la PK agrupada).
SELECT c.pais,
       count(*)                       AS pedidos,
       count(DISTINCT p.cliente_id)   AS clientes_distintos,
       sum(p.total)                   AS facturado,
       avg(p.total)::numeric(10,2)    AS ticket_medio,
       min(p.creado_en)               AS primer_pedido,
       max(p.total)                   AS pedido_mayor,
       percentile_cont(0.5) WITHIN GROUP (ORDER BY p.total) AS mediana,
       stddev_pop(p.total)::numeric(10,2) AS desviacion
FROM   pedido p
JOIN   cliente c ON c.id = p.cliente_id
WHERE  p.creado_en >= '2026-01-01'          -- filtra FILAS antes de agrupar
GROUP  BY c.pais
HAVING sum(p.total) > 1000                  -- filtra GRUPOS después de agregar
ORDER  BY facturado DESC;

-- PostgreSQL admite agrupar por la PK y seleccionar sus dependientes funcionales:
SELECT c.id, c.nombre, c.email, count(p.id) AS pedidos
FROM   cliente c LEFT JOIN pedido p ON p.cliente_id = c.id
GROUP  BY c.id;         -- válido: nombre y email dependen de c.id (que es PK)
-- Esto NO es portable a todos los motores; MySQL lo permite de forma laxa
-- (ONLY_FULL_GROUP_BY desactivado) y puede devolver valores arbitrarios. Evítalo si
-- necesitas portabilidad: lista las columnas.

-- WHERE vs HAVING: el mismo resultado, coste muy distinto
SELECT cliente_id, sum(total) FROM pedido
WHERE  estado = 'PAGADO'                     -- ✅ descarta antes: menos trabajo
GROUP  BY cliente_id;

SELECT cliente_id, sum(total) FROM pedido
GROUP  BY cliente_id
HAVING bool_and(estado = 'PAGADO');          -- ❌ agrupa todo y luego descarta

FILTER: agregados condicionales legibles

-- ❌ La forma antigua con CASE: funciona, pero es ruidosa y fácil de equivocar
SELECT date_trunc('month', creado_en) AS mes,
       count(*) AS total,
       sum(CASE WHEN estado = 'ENTREGADO' THEN 1 ELSE 0 END) AS entregados,
       sum(CASE WHEN estado = 'CANCELADO' THEN total ELSE 0 END) AS importe_cancelado
FROM   pedido GROUP BY 1;

-- ✅ FILTER (SQL estándar, PostgreSQL 9.4+): dice exactamente lo que hace
SELECT date_trunc('month', creado_en) AS mes,
       count(*)                                          AS total,
       count(*) FILTER (WHERE estado = 'ENTREGADO')       AS entregados,
       count(*) FILTER (WHERE estado = 'CANCELADO')       AS cancelados,
       sum(total) FILTER (WHERE estado = 'CANCELADO')     AS importe_cancelado,
       round(100.0 * count(*) FILTER (WHERE estado = 'CANCELADO') / count(*), 2)
                                                          AS pct_cancelacion
FROM   pedido
GROUP  BY 1
ORDER  BY 1;
-- Diferencia importante frente a CASE: sum(...) FILTER devuelve NULL si ningún grupo
-- cumple la condición, mientras que sum(CASE ... ELSE 0) devuelve 0. Elige a conciencia.

-- Agregados de texto y de JSON, muy útiles para APIs de lectura
SELECT p.id,
       string_agg(pr.nombre, ', ' ORDER BY pr.nombre)     AS productos,
       array_agg(l.cantidad ORDER BY l.linea_num)         AS cantidades,
       jsonb_agg(jsonb_build_object('sku', pr.sku, 'uds', l.cantidad)
                 ORDER BY l.linea_num)                    AS lineas_json
FROM   pedido p
JOIN   linea_pedido l ON l.pedido_id = p.id
JOIN   producto pr    ON pr.id = l.producto_id
GROUP  BY p.id;

-- bool_and / bool_or: invariantes sobre un grupo
SELECT p.id,
       bool_and(pr.activo) AS todos_activos,
       bool_or(pr.stock = 0) AS algun_sin_stock
FROM   pedido p JOIN linea_pedido l ON l.pedido_id = p.id
JOIN   producto pr ON pr.id = l.producto_id
GROUP  BY p.id;

GROUPING SETS, ROLLUP y CUBE: subtotales en una sola pasada

-- ❌ Lo que hace todo el mundo: tres consultas y un UNION ALL, tres escaneos de la tabla
SELECT pais, estado, sum(total) FROM v_pedido GROUP BY pais, estado
UNION ALL SELECT pais, NULL, sum(total) FROM v_pedido GROUP BY pais
UNION ALL SELECT NULL, NULL, sum(total) FROM v_pedido;

-- ✅ ROLLUP: jerarquía de subtotales (pais+estado, pais, total general) en UN escaneo
SELECT c.pais, p.estado, sum(p.total) AS facturado, count(*) AS pedidos
FROM   pedido p JOIN cliente c ON c.id = p.cliente_id
GROUP  BY ROLLUP (c.pais, p.estado)
ORDER  BY c.pais NULLS LAST, p.estado NULLS LAST;

-- ✅ CUBE: TODAS las combinaciones (pais+estado, pais, estado, general)
SELECT c.pais, p.estado, sum(p.total)
FROM   pedido p JOIN cliente c ON c.id = p.cliente_id
GROUP  BY CUBE (c.pais, p.estado);

-- ✅ GROUPING SETS: exactamente las combinaciones que quieres, ni una más
SELECT c.pais, p.estado, date_trunc('month', p.creado_en) AS mes, sum(p.total)
FROM   pedido p JOIN cliente c ON c.id = p.cliente_id
GROUP  BY GROUPING SETS (
             (c.pais, p.estado),
             (c.pais, date_trunc('month', p.creado_en)),
             ()                                   -- el gran total
         );

-- GROUPING() distingue "es un subtotal" de "el valor real es NULL": imprescindible
-- para etiquetar las filas en el informe.
SELECT CASE WHEN GROUPING(c.pais) = 1 THEN 'TODOS' ELSE c.pais END AS pais,
       CASE WHEN GROUPING(p.estado) = 1 THEN 'TODOS' ELSE p.estado END AS estado,
       sum(p.total) AS facturado
FROM   pedido p JOIN cliente c ON c.id = p.cliente_id
GROUP  BY ROLLUP (c.pais, p.estado)
ORDER  BY GROUPING(c.pais), c.pais, GROUPING(p.estado), p.estado;

Subconsultas: escalares, IN, EXISTS y correlacionadas

-- 1) Subconsulta ESCALAR: devuelve exactamente una fila y una columna.
--    Si devuelve más de una fila, error en ejecución. Si no devuelve ninguna, NULL.
SELECT sku, precio,
       (SELECT avg(precio) FROM producto)                      AS precio_medio_global,
       precio - (SELECT avg(precio) FROM producto)             AS diferencia
FROM   producto;

-- 2) Subconsulta en FROM (tabla derivada): necesita alias obligatoriamente en Postgres
SELECT pais, ticket_medio
FROM   (SELECT c.pais, avg(p.total) AS ticket_medio
        FROM   pedido p JOIN cliente c ON c.id = p.cliente_id
        GROUP  BY c.pais) AS resumen
WHERE  ticket_medio > 50;

-- 3) IN con subconsulta: legible, correcto si no hay NULL
SELECT * FROM pedido
WHERE  cliente_id IN (SELECT id FROM cliente WHERE pais = 'ES');

-- 4) EXISTS: pregunta "¿hay al menos una?" y para en la primera coincidencia
SELECT c.id, c.nombre
FROM   cliente c
WHERE  EXISTS (SELECT 1 FROM pedido p
               WHERE p.cliente_id = c.id AND p.total > 500);

-- 5) NOT EXISTS: el anti-join seguro (y el que debes usar en lugar de NOT IN)
SELECT c.id, c.nombre
FROM   cliente c
WHERE  NOT EXISTS (SELECT 1 FROM pedido p WHERE p.cliente_id = c.id);

-- 6) Subconsulta CORRELACIONADA en el SELECT: cómoda, potencialmente cara.
--    Se ejecuta una vez por fila del resultado externo.
SELECT c.nombre,
       (SELECT count(*)      FROM pedido p WHERE p.cliente_id = c.id) AS pedidos,
       (SELECT max(creado_en) FROM pedido p WHERE p.cliente_id = c.id) AS ultimo
FROM   cliente c;

-- ✅ La misma información con un LEFT JOIN + GROUP BY: un solo recorrido
SELECT c.nombre, count(p.id) AS pedidos, max(p.creado_en) AS ultimo
FROM   cliente c LEFT JOIN pedido p ON p.cliente_id = c.id
GROUP  BY c.id, c.nombre;

-- ✅ O con LATERAL, que además evita repetir la subconsulta por cada métrica

CTEs: WITH, encadenadas y recursivas

Una Common Table Expression es una subconsulta con nombre que se declara antes de usarse. Su valor principal es la legibilidad: convierte una consulta anidada ilegible en una secuencia de pasos con nombres del dominio.

-- CTEs encadenadas: cada paso se apoya en el anterior y se lee de arriba abajo
WITH pedidos_2026 AS (
    SELECT * FROM pedido
    WHERE  creado_en >= '2026-01-01' AND estado <> 'CANCELADO'
),
gasto_por_cliente AS (
    SELECT cliente_id, sum(total) AS gastado, count(*) AS pedidos
    FROM   pedidos_2026
    GROUP  BY cliente_id
),
umbral AS (
    SELECT percentile_cont(0.9) WITHIN GROUP (ORDER BY gastado) AS p90
    FROM   gasto_por_cliente
)
SELECT c.nombre, g.gastado, g.pedidos
FROM   gasto_por_cliente g
JOIN   cliente c ON c.id = g.cliente_id
CROSS  JOIN umbral u
WHERE  g.gastado >= u.p90                      -- el 10% de clientes que más gasta
ORDER  BY g.gastado DESC;

-- CTE con DML: mover datos entre tablas en una sola sentencia atómica
WITH archivados AS (
    DELETE FROM pedido
    WHERE  creado_en < '2024-01-01' AND estado = 'ENTREGADO'
    RETURNING *
)
INSERT INTO pedido_archivo SELECT * FROM archivados;

-- ⚠️ Todas las CTE de una sentencia ven el MISMO snapshot: una CTE que modifica
--    datos no es visible para las demás. Y el orden de ejecución entre varias CTE
--    con DML no está garantizado. No construyas lógica que dependa de eso.

-- CTE RECURSIVA: la única forma estándar de recorrer una jerarquía de profundidad
-- desconocida. Anatomía: caso base UNION ALL caso recursivo (que se refiere al nombre).
WITH RECURSIVE arbol AS (
    -- Caso base: las raíces
    SELECT id, nombre, padre_id, 1 AS nivel, nombre AS ruta
    FROM   categoria
    WHERE  padre_id IS NULL

    UNION ALL

    -- Caso recursivo: los hijos de lo que ya tengo
    SELECT c.id, c.nombre, c.padre_id, a.nivel + 1, a.ruta || ' > ' || c.nombre
    FROM   categoria c
    JOIN   arbol a ON c.padre_id = a.id
    WHERE  a.nivel < 10                        -- ⚠️ tope de seguridad SIEMPRE
)
SELECT repeat('    ', nivel - 1) || nombre AS jerarquia, nivel, ruta
FROM   arbol
ORDER  BY ruta;

-- Resultado:
--   Informática              1  Informática
--       Periféricos          2  Informática > Periféricos
--           Teclados         3  Informática > Periféricos > Teclados
--           Ratones          3  Informática > Periféricos > Ratones
--       Monitores            2  Informática > Monitores

-- Hacia arriba: los ancestros de una categoría concreta (migas de pan)
WITH RECURSIVE ancestros AS (
    SELECT id, nombre, padre_id FROM categoria WHERE id = 4
    UNION ALL
    SELECT c.id, c.nombre, c.padre_id
    FROM   categoria c JOIN ancestros a ON c.id = a.padre_id
)
SELECT string_agg(nombre, ' > ' ORDER BY id) AS migas FROM ancestros;

-- Todas las ventas de una categoría INCLUYENDO sus subcategorías
WITH RECURSIVE rama AS (
    SELECT id FROM categoria WHERE id = 2
    UNION ALL
    SELECT c.id FROM categoria c JOIN rama r ON c.padre_id = r.id
)
SELECT sum(l.importe) AS facturado_rama
FROM   linea_pedido l
JOIN   producto p ON p.id = l.producto_id
WHERE  p.categoria_id IN (SELECT id FROM rama);

-- Generar series: útil para rejillas de fechas sin tabla de calendario
WITH RECURSIVE meses AS (
    SELECT '2026-01-01'::date AS mes
    UNION ALL
    SELECT (mes + interval '1 month')::date FROM meses WHERE mes < '2026-12-01'
)
SELECT * FROM meses;
-- (en Postgres, generate_series() hace esto mejor; el ejemplo es para entender el patrón)

Funciones de ventana: el salto de nivel en SQL

Una función de ventana calcula un valor por fila mirando un conjunto de filas relacionadas, sin colapsarlas. Ahí está toda la diferencia con GROUP BY: la agregación reduce N filas a una; la ventana conserva las N filas y les añade información del grupo.

GROUP BY                              OVER (PARTITION BY ...)

 pedido  cliente total                 pedido cliente total  total_cliente  posicion
 ------  ------- -----                 ------ ------- -----  -------------  --------
   1       Ana    100                    1      Ana    100        250          2
   2       Ana    150       ──▶            2      Ana    150        250          1
   3      Bruno    80                    3     Bruno    80          80          1

 cliente  suma                        Las 3 filas SIGUEN AHÍ, enriquecidas.
 -------  ----
   Ana     250     ← 2 filas se han                ANATOMÍA
  Bruno     80       convertido en 1
                                       funcion() OVER (
                                           PARTITION BY  ← grupos independientes
                                           ORDER BY      ← orden dentro del grupo
                                           frame         ← qué filas entran (ROWS/RANGE)
                                       )

-- Las tres familias de funciones de ventana, en una sola consulta didáctica
SELECT p.id,
       c.nombre,
       p.total,
       p.creado_en::date AS fecha,

       -- FAMILIA 1: numeración y ranking
       row_number() OVER (PARTITION BY p.cliente_id ORDER BY p.creado_en DESC) AS num_desc,
       rank()       OVER (ORDER BY p.total DESC)                                AS rank_global,
       dense_rank() OVER (ORDER BY p.total DESC)                                AS dense_global,
       ntile(4)     OVER (ORDER BY p.total)                                     AS cuartil,
       percent_rank() OVER (ORDER BY p.total)                                   AS percentil,

       -- FAMILIA 2: desplazamiento (comparar con otras filas)
       lag(p.total)  OVER (PARTITION BY p.cliente_id ORDER BY p.creado_en)      AS total_anterior,
       lead(p.total) OVER (PARTITION BY p.cliente_id ORDER BY p.creado_en)      AS total_siguiente,
       p.total - lag(p.total) OVER (PARTITION BY p.cliente_id ORDER BY p.creado_en)
                                                                               AS variacion,
       first_value(p.total) OVER (PARTITION BY p.cliente_id ORDER BY p.creado_en)
                                                                               AS primer_pedido,
       last_value(p.total)  OVER (PARTITION BY p.cliente_id ORDER BY p.creado_en
                                  ROWS BETWEEN UNBOUNDED PRECEDING
                                           AND UNBOUNDED FOLLOWING)            AS ultimo_pedido,

       -- FAMILIA 3: agregados como ventana
       sum(p.total)   OVER (PARTITION BY p.cliente_id)                          AS total_cliente,
       sum(p.total)   OVER (PARTITION BY p.cliente_id ORDER BY p.creado_en)     AS acumulado,
       avg(p.total)   OVER (PARTITION BY p.cliente_id)                          AS media_cliente,
       count(*)       OVER (PARTITION BY p.cliente_id)                          AS pedidos_cliente,
       round(100.0 * p.total / sum(p.total) OVER (), 2)                         AS pct_del_total
FROM   pedido p
JOIN   cliente c ON c.id = p.cliente_id
ORDER  BY c.nombre, p.creado_en;

-- WINDOW: nombra la ventana una vez y reutilízala. Imprescindible si repites la
-- misma definición 4 veces (menos ruido y el planificador la calcula una sola vez).
SELECT p.id, c.nombre, p.total,
       row_number() OVER w  AS num,
       sum(p.total) OVER w  AS acumulado,
       lag(p.total) OVER w  AS anterior
FROM   pedido p JOIN cliente c ON c.id = p.cliente_id
WINDOW w AS (PARTITION BY p.cliente_id ORDER BY p.creado_en)
ORDER  BY c.nombre, p.creado_en;

ROW_NUMBER frente a RANK y DENSE_RANK

total  row_number  rank  dense_rank   ¿Cuál uso?
-----  ----------  ----  ----------
 500        1        1        1       row_number : numeración sin empates. Para
 300        2        2        2                    deduplicar y paginar.
 300        3        2        2       rank       : deja huecos. "Puesto en la
 100        4        4        3                    clasificación" deportivo.
                                      dense_rank : sin huecos. "Nivel de precio"
                                                   o "escalón" de una escala.

Frames: ROWS frente a RANGE

El frame define QUÉ FILAS de la partición entran en el cálculo de cada fila.

  ROWS   → cuenta FILAS físicas          RANGE  → cuenta VALORES del ORDER BY
                                                  (agrupa los empates)

  Por defecto (con ORDER BY):  RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  Por defecto (sin ORDER BY):  RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

  fila  fecha       total   sum() OVER (ORDER BY fecha)
                            ROWS      RANGE     ← ¡distinto con fechas repetidas!
   1    01-03        100     100       250
   2    01-03        150     250       250      RANGE incluye TODAS las filas con
   3    02-03         80     330       330      el mismo valor de fecha.
   4    03-03         50     380       380

  MORALEJA: si el ORDER BY tiene empates y quieres un acumulado fila a fila,
            escribe ROWS explícitamente. Es el bug silencioso más común de
            las funciones de ventana.

-- Media móvil de 7 días de facturación: el caso de uso canónico de ROWS
WITH diario AS (
    SELECT creado_en::date AS dia, sum(total) AS facturado
    FROM   pedido
    WHERE  estado <> 'CANCELADO'
    GROUP  BY 1
)
SELECT dia,
       facturado,
       round(avg(facturado) OVER (ORDER BY dia
                                  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2)
           AS media_movil_7d,
       sum(facturado) OVER (ORDER BY dia ROWS UNBOUNDED PRECEDING) AS acumulado_anual,
       facturado - lag(facturado, 7) OVER (ORDER BY dia) AS vs_misma_semana_anterior
FROM   diario
ORDER  BY dia;

-- Media móvil centrada (3 antes, 3 después) para suavizar una serie
SELECT dia, avg(facturado) OVER (ORDER BY dia ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING)
FROM   diario;

-- Frame por RANGE con intervalos de tiempo (PostgreSQL 11+): "últimos 30 días reales",
-- aunque falten días sin ventas. Con ROWS esto sería incorrecto.
SELECT dia, sum(facturado) OVER (ORDER BY dia
                                 RANGE BETWEEN '29 days' PRECEDING AND CURRENT ROW)
           AS ventas_30d
FROM   diario;

Los cuatro patrones de ventana que resuelven el 90% de los casos reales

-- PATRÓN 1: TOP-N POR GRUPO. "Los 3 productos más vendidos de cada categoría."
-- La función de ventana no se puede filtrar en WHERE (se evalúa después), así que
-- hace falta una subconsulta o CTE.
WITH ventas AS (
    SELECT pr.categoria_id,
           pr.id, pr.nombre,
           sum(l.cantidad) AS unidades,
           row_number() OVER (PARTITION BY pr.categoria_id
                              ORDER BY sum(l.cantidad) DESC, pr.id) AS puesto
    FROM   producto pr
    JOIN   linea_pedido l ON l.producto_id = pr.id
    GROUP  BY pr.categoria_id, pr.id, pr.nombre
)
SELECT c.nombre AS categoria, v.nombre AS producto, v.unidades, v.puesto
FROM   ventas v JOIN categoria c ON c.id = v.categoria_id
WHERE  v.puesto <= 3
ORDER  BY c.nombre, v.puesto;
-- Nota: se puede usar row_number() sobre un agregado en la misma consulta porque las
-- ventanas se evalúan después de GROUP BY.

-- PATRÓN 2: DEDUPLICACIÓN. "Quédate con la fila más reciente de cada email duplicado."
WITH numeradas AS (
    SELECT id, email,
           row_number() OVER (PARTITION BY lower(email) ORDER BY creado_en DESC, id DESC) AS rn
    FROM   cliente
)
DELETE FROM cliente
WHERE  id IN (SELECT id FROM numeradas WHERE rn > 1);

-- PATRÓN 3: DETECTAR HUECOS Y RACHAS (gaps and islands).
-- "¿Cuántos días consecutivos ha comprado cada cliente?"
WITH dias AS (
    SELECT DISTINCT cliente_id, creado_en::date AS dia FROM pedido
),
grupos AS (
    SELECT cliente_id, dia,
           dia - (row_number() OVER (PARTITION BY cliente_id ORDER BY dia))::int AS grupo   -- fecha - entero = fecha
    FROM   dias
)
SELECT cliente_id, min(dia) AS desde, max(dia) AS hasta, count(*) AS dias_seguidos
FROM   grupos
GROUP  BY cliente_id, grupo
HAVING count(*) >= 3
ORDER  BY dias_seguidos DESC;
-- El truco: si los días son consecutivos, (dia - nº_de_fila) es constante.

-- PATRÓN 4: COMPARAR CON EL AGREGADO DEL GRUPO SIN SUBCONSULTA.
-- "Productos cuyo precio está por encima de la media de su categoría."
SELECT * FROM (
    SELECT pr.sku, pr.nombre, pr.precio, c.nombre AS categoria,
           avg(pr.precio) OVER (PARTITION BY pr.categoria_id)::numeric(10,2) AS media_cat,
           pr.precio - avg(pr.precio) OVER (PARTITION BY pr.categoria_id) AS diferencia
    FROM   producto pr JOIN categoria c ON c.id = pr.categoria_id
) t
WHERE  precio > media_cat
ORDER  BY diferencia DESC;

Operaciones de conjuntos y DISTINCT ON

-- UNION elimina duplicados: implica ordenar o construir una tabla hash. Cuesta.
SELECT email FROM cliente
UNION
SELECT email FROM cliente_potencial;

-- UNION ALL no elimina nada: es una concatenación pura y es MUCHO más barato.
-- Úsalo SIEMPRE que sepas que no hay duplicados o que no te importan.
SELECT 'cliente' AS origen, email FROM cliente
UNION ALL
SELECT 'potencial', email FROM cliente_potencial;

-- INTERSECT y EXCEPT: útiles para comparar conjuntos y para tests de datos
SELECT email FROM cliente INTERSECT SELECT email FROM cliente_potencial;  -- en ambos
SELECT email FROM cliente EXCEPT    SELECT email FROM cliente_potencial;  -- solo clientes

-- Requisitos: mismo número de columnas y tipos compatibles. El nombre de las columnas
-- lo pone la PRIMERA consulta. ORDER BY y LIMIT se aplican al resultado combinado
-- (o encierra cada rama entre paréntesis si quieres limitarla por separado).
(SELECT id, total FROM pedido ORDER BY total DESC LIMIT 3)
UNION ALL
(SELECT id, total FROM pedido ORDER BY total ASC LIMIT 3)
ORDER BY total DESC;

-- DISTINCT ON: extensión de PostgreSQL que resuelve "una fila por grupo" de forma
-- directa. La expresión de DISTINCT ON debe coincidir con el inicio del ORDER BY.
SELECT DISTINCT ON (cliente_id)
       cliente_id, id AS ultimo_pedido, total, creado_en
FROM   pedido
ORDER  BY cliente_id, creado_en DESC, id DESC;   -- el DESC decide qué fila "gana"

-- Equivalente estándar y portable (más largo):
SELECT cliente_id, id, total, creado_en FROM (
    SELECT *, row_number() OVER (PARTITION BY cliente_id
                                 ORDER BY creado_en DESC, id DESC) AS rn
    FROM pedido
) t WHERE rn = 1;

-- ⚠️ Error habitual: DISTINCT no es una función y no se aplica a una columna.
SELECT DISTINCT(cliente_id), total FROM pedido;   -- ❌ engañoso: los paréntesis no hacen nada,
                                                  --    el DISTINCT aplica a TODAS las columnas

DML avanzado: upsert, RETURNING y MERGE

-- INSERT ... ON CONFLICT: el upsert de PostgreSQL (9.5+). Atómico y sin condición
-- de carrera, al contrario que "comprobar si existe y luego insertar".
INSERT INTO producto (sku, nombre, categoria_id, precio, stock)
VALUES ('SKU-1001', 'Teclado mecánico', 4, 89.90, 50)
ON CONFLICT (sku) DO UPDATE
   SET nombre    = EXCLUDED.nombre,          -- EXCLUDED = la fila que se intentaba insertar
       precio    = EXCLUDED.precio,
       stock     = producto.stock + EXCLUDED.stock,   -- la tabla = valores actuales
       activo    = true
 WHERE producto.precio <> EXCLUDED.precio     -- actualiza solo si algo cambia:
   OR  producto.nombre <> EXCLUDED.nombre;    -- evita WAL y bloat innecesarios

-- ON CONFLICT DO NOTHING: inserción idempotente, ideal para consumir eventos
INSERT INTO pago (pedido_id, metodo, importe, referencia)
VALUES (4711, 'TARJETA', 129.90, 'stripe_ch_3Nx7')
ON CONFLICT (referencia) DO NOTHING;
-- Si el mensaje de la pasarela llega dos veces, el segundo no hace nada.

-- Upsert masivo desde una lista de valores: una sola ida y vuelta desde Java
INSERT INTO producto (sku, nombre, categoria_id, precio, stock)
SELECT * FROM unnest(
    $1::text[], $2::text[], $3::int[], $4::numeric[], $5::int[]
) AS t(sku, nombre, categoria_id, precio, stock)
ON CONFLICT (sku) DO UPDATE SET precio = EXCLUDED.precio, stock = EXCLUDED.stock;

-- ⚠️ ON CONFLICT necesita un índice único o restricción que lo respalde. No existe
--    "ON CONFLICT (cualquier cosa)". Y solo puedes indicar un objetivo de conflicto.

-- RETURNING: recupera lo que acaba de escribirse sin un SELECT extra. Funciona en
-- INSERT, UPDATE y DELETE. Es la forma correcta de obtener claves generadas.
INSERT INTO pedido (cliente_id, estado)
VALUES (1, 'NUEVO')
RETURNING id, creado_en;

UPDATE producto SET stock = stock - 1
WHERE  id = 10 AND stock > 0
RETURNING id, stock AS stock_restante;
-- Si devuelve 0 filas, no había stock: comprobación y actualización en una operación
-- atómica, sin bloqueo explícito ni condición de carrera.

DELETE FROM carrito WHERE actualizado_en < now() - interval '30 days'
RETURNING id, cliente_id;

-- UPDATE ... FROM: actualizar usando datos de otra tabla (extensión de Postgres,
-- también en SQL Server con sintaxis parecida)
UPDATE producto p
   SET precio = p.precio * (1 + t.subida)
  FROM tarifa_nueva t
 WHERE t.categoria_id = p.categoria_id
   AND p.activo;

-- MERGE (SQL estándar, PostgreSQL 15+): más expresivo que ON CONFLICT porque decide
-- por condición arbitraria y admite varias acciones, incluida la de borrar.
MERGE INTO producto p
USING importacion_proveedor i
   ON p.sku = i.sku
WHEN MATCHED AND i.descatalogado THEN
    UPDATE SET activo = false
WHEN MATCHED AND i.precio IS DISTINCT FROM p.precio THEN
    UPDATE SET precio = i.precio, nombre = i.nombre
WHEN NOT MATCHED THEN
    INSERT (sku, nombre, categoria_id, precio, stock)
    VALUES (i.sku, i.nombre, i.categoria_id, i.precio, COALESCE(i.stock, 0));

4 · Índices y planes de ejecución

Un índice es una estructura de datos redundante que el motor mantiene para encontrar filas sin leer la tabla entera. La analogía es exacta: el índice alfabético del final de un libro. Sin él, para encontrar «MVCC» hay que hojear las 800 páginas; con él, vas a la M, lees el número de página y saltas. Y como en el libro, el índice ocupa papel y hay que rehacerlo cada vez que cambia el contenido.

Cómo funciona un B-tree por dentro

El 95% de los índices que crearás son B-tree (concretamente B+tree). Es un árbol equilibrado en el que todas las hojas están a la misma profundidad, las claves están ordenadas y las hojas se enlazan entre sí. Esas tres propiedades explican todo lo que un B-tree sabe hacer.

ÍNDICE B-TREE SOBRE pedido(creado_en)   —   3 niveles bastan para 200 millones de filas

                        ┌─────────── RAÍZ ───────────┐
                        │   [ 2026-03-01 | 2026-07-01 ]│      nivel 0 (siempre en caché)
                        └───┬────────────┬───────────┬─┘
              < 03-01       │            │           │      >= 07-01
         ┌──────────────────┘            │           └──────────────────┐
         ▼                               ▼                              ▼
  ┌──────────────┐              ┌──────────────┐              ┌──────────────┐
  │[01-15│02-10] │              │[04-02│05-20] │              │[08-11│10-03] │  nivel 1
  └──┬───┬───┬───┘              └──┬───┬───┬───┘              └──┬───┬───┬───┘  (interno)
     ▼   ▼   ▼                     ▼   ▼   ▼                     ▼   ▼   ▼
  ┌────┐┌────┐┌────┐            ┌────┐┌────┐┌────┐            ┌────┐┌────┐┌────┐
  │hoja││hoja││hoja│◀──────────▶│hoja││hoja││hoja│◀──────────▶│hoja││hoja││hoja│ nivel 2
  └────┘└────┘└────┘            └────┘└────┘└────┘            └────┘└────┘└────┘
     ▲                                                                     ▲
     └──── las hojas están ENLAZADAS entre sí en orden ────────────────────┘

  Cada hoja contiene: (valor_de_la_clave  →  ctid = puntero físico a la fila)

BÚSQUEDA DE creado_en = '2026-04-02':
  1. Leer raíz          → 04-02 está entre 03-01 y 07-01 → ir al hijo del medio
  2. Leer nodo interno  → 04-02 coincide con la primera clave → ir a esa hoja
  3. Leer hoja          → encontrar el ctid
  4. Leer la fila de la tabla (heap) por su ctid
  = 4 accesos a bloques de 8 kB, de los cuales 2-3 estarán en caché.
  Sin índice: leer los 200 millones de filas.

BÚSQUEDA POR RANGO creado_en >= '2026-04-02' AND < '2026-05-01':
  1-3. Localizar la primera hoja (igual que antes)
  4.   Recorrer las hojas enlazadas hacia la derecha hasta pasarse del límite.
  Aquí está la clave: por eso un B-tree sirve para =, <, <=, >, >=, BETWEEN,
  IN, LIKE 'prefijo%' y ORDER BY, y NO sirve para LIKE '%sufijo'.

PROFUNDIDAD REAL:  cada bloque de 8 kB guarda cientos de claves.
  ~200 claves por bloque  →  200³ = 8.000.000 de filas con 3 niveles
                          →  200⁴ = 1.600.000.000 con 4 niveles
  Un índice "grande" casi nunca pasa de 4-5 niveles. Es logarítmico de verdad.

Los tipos de índice de PostgreSQL y cuándo usar cada uno

-- GIN para búsqueda de texto completo en español, con relevancia
ALTER TABLE producto ADD COLUMN busqueda tsvector
    GENERATED ALWAYS AS (
        to_tsvector('spanish', coalesce(nombre,'') || ' ' || coalesce(sku,''))
    ) STORED;
CREATE INDEX producto_busqueda_gin ON producto USING gin (busqueda);

SELECT sku, nombre, ts_rank(busqueda, q) AS relevancia
FROM   producto, websearch_to_tsquery('spanish', 'teclado mecánico -membrana') AS q
WHERE  busqueda @@ q
ORDER  BY relevancia DESC
LIMIT  20;

-- GIN con pg_trgm: búsquedas por subcadena y tolerantes a erratas, con índice
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX producto_nombre_trgm ON producto USING gin (nombre gin_trgm_ops);

SELECT sku, nombre FROM producto WHERE nombre ILIKE '%teclad%';      -- ¡ahora usa índice!
SELECT sku, nombre, similarity(nombre, 'teclado mecanico') AS s
FROM   producto WHERE nombre % 'teclado mecanico' ORDER BY s DESC;   -- difuso

-- BRIN sobre una tabla de eventos de 500 millones de filas insertadas en orden temporal
CREATE INDEX evento_ocurrido_brin ON evento USING brin (ocurrido_en) WITH (pages_per_range = 64);
-- Tamaño típico: B-tree ≈ 10 GB   vs   BRIN ≈ 15 MB
-- Contrapartida: no sirve para buscar una fila concreta, solo para acotar rangos
-- amplios, y deja de funcionar si el orden físico se desordena (UPDATE masivos).

-- GiST para excluir solapamientos (ya visto) y para "el más cercano"
CREATE INDEX tienda_geo_gist ON tienda_fisica USING gist (ubicacion);
SELECT nombre, ubicacion <-> ST_Point(-3.70, 40.42)::geography AS metros
FROM   tienda_fisica
ORDER  BY ubicacion <-> ST_Point(-3.70, 40.42)::geography
LIMIT  5;   -- índice + ORDER BY por distancia = "KNN search"

Índices compuestos y el orden de las columnas

Un índice compuesto ordena las filas por la primera columna, y dentro de cada valor de la primera, por la segunda, y así sucesivamente. Es exactamente el orden de un listín telefónico: (apellido, nombre). Con ese listín puedes buscar «todos los García» y «García, Ana», pero no «todas las Ana» sin leerlo entero.

ÍNDICE (cliente_id, creado_en)    →  el orden es: primero cliente, luego fecha

  cliente_id │ creado_en
  ───────────┼──────────────
      1      │ 2026-01-05     ┐
      1      │ 2026-02-11     ├── todas las filas del cliente 1, YA ORDENADAS por fecha
      1      │ 2026-03-02     ┘
      2      │ 2026-01-20     ┐
      2      │ 2026-04-15     ┘
      3      │ 2026-02-01

CONSULTAS QUE APROVECHAN ESTE ÍNDICE:
  ✅ WHERE cliente_id = 1                                  → prefijo exacto
  ✅ WHERE cliente_id = 1 AND creado_en >= '2026-02-01'     → prefijo + rango
  ✅ WHERE cliente_id = 1 ORDER BY creado_en DESC LIMIT 10  → sin ordenación extra
  ✅ WHERE cliente_id IN (1,2,3) ORDER BY creado_en         → recorre 3 rangos

CONSULTAS QUE NO LO APROVECHAN (o solo a medias):
  ❌ WHERE creado_en >= '2026-02-01'                        → no hay prefijo izquierdo
     (Postgres puede hacer un "index skip"/full index scan si el índice es más
      pequeño que la tabla, pero no es una búsqueda: recorre todo el índice)
  ⚠️ WHERE cliente_id > 5 AND creado_en = '2026-02-01'      → el rango en la 1ª columna
     rompe la utilidad de la 2ª: hay que revisar todas las fechas de cada cliente > 5

LAS DOS REGLAS DE ORO DEL ORDEN DE COLUMNAS:
  1) IGUALDAD ANTES QUE RANGO.  Las columnas con predicado de igualdad (=, IN) van
     primero; la columna con rango (<, >, BETWEEN, LIKE 'x%'), la última.
  2) LUEGO EL ORDER BY.  Si la consulta ordena, la columna de ordenación va justo
     después de las de igualdad y con la MISMA dirección (o toda invertida).
  3) LO MÁS SELECTIVO PRIMERO, entre columnas de igualdad, si tienes varias opciones
     y ninguna consulta te obliga a un orden concreto.

-- Consulta objetivo: "los últimos 20 pedidos pagados de un cliente"
SELECT id, total, creado_en FROM pedido
WHERE  cliente_id = 42 AND estado = 'PAGADO'
ORDER  BY creado_en DESC
LIMIT  20;

-- ❌ Dos índices de una columna: el planificador tiene que combinarlos con un
--    Bitmap And, leer la tabla y ORDENAR el resultado.
CREATE INDEX pedido_cliente_idx ON pedido (cliente_id);
CREATE INDEX pedido_estado_idx  ON pedido (estado);

-- ✅ Un índice compuesto en el orden correcto: igualdades, luego ordenación
CREATE INDEX pedido_cliente_estado_creado_idx
    ON pedido (cliente_id, estado, creado_en DESC);
-- El plan pasa a ser un Index Scan que lee exactamente 20 entradas y para.
-- No hay Sort porque el índice ya entrega las filas en el orden pedido.

-- REGLA DEL PREFIJO IZQUIERDO: un índice (a, b, c) sirve para (a), (a,b) y (a,b,c),
-- pero NO para (b), (c) ni (b,c). Por eso NO necesitas los tres índices:
--   (cliente_id)                      ← redundante, el compuesto ya lo cubre
--   (cliente_id, estado)              ← redundante
--   (cliente_id, estado, creado_en)   ← este basta
DROP INDEX pedido_cliente_idx;    -- ojo: seguirá siendo necesario para las FK si
                                  -- el compuesto no empieza por la columna de la FK

-- Direcciones mixtas en el ORDER BY: el índice debe reflejarlas
SELECT * FROM pedido ORDER BY cliente_id ASC, creado_en DESC;
CREATE INDEX pedido_mixto_idx ON pedido (cliente_id ASC, creado_en DESC);
-- Un índice (cliente_id ASC, creado_en ASC) NO sirve para este ORDER BY, porque
-- el motor solo puede leer un índice hacia adelante o hacia atrás COMPLETO.

-- Y NULLS: si ordenas con NULLS FIRST, dilo también en el índice
CREATE INDEX cliente_nif_idx ON cliente (nif NULLS FIRST);

Covering index e index-only scan

Normalmente el motor usa el índice para localizar los ctid y luego va a la tabla (el heap) a leer las columnas que faltan. Si todas las columnas que la consulta necesita están en el índice, ese segundo paso desaparece: es un index-only scan, y puede ser 5-10 veces más rápido porque el índice es mucho más pequeño y cabe en caché.

-- Consulta muy caliente de un cuadro de mando
SELECT cliente_id, sum(total) FROM pedido
WHERE  creado_en >= '2026-01-01' GROUP BY cliente_id;

-- ✅ Índice que cubre TODAS las columnas implicadas → Index Only Scan
CREATE INDEX pedido_cobertura_idx ON pedido (creado_en, cliente_id, total);

-- ✅ Mejor aún con INCLUDE (PostgreSQL 11+): las columnas de INCLUDE se guardan
--    solo en las hojas, no participan en la ordenación ni en la unicidad, y por
--    tanto el índice es más compacto y sigue sirviendo para el filtro y el orden.
DROP INDEX pedido_cobertura_idx;
CREATE INDEX pedido_cobertura_idx ON pedido (creado_en, cliente_id) INCLUDE (total);

-- Truco muy útil: hacer único un índice y aprovecharlo para cubrir
CREATE UNIQUE INDEX pago_referencia_uk ON pago (referencia) INCLUDE (pedido_id, importe);

-- ⚠️ El Index Only Scan depende del "visibility map": si la tabla tiene muchas
--    filas modificadas recientemente y no se ha pasado VACUUM, el motor tiene que
--    ir al heap de todas formas para comprobar la visibilidad. Un plan que decía
--    "Heap Fetches: 0" puede pasar a "Heap Fetches: 180000" tras una carga masiva.
--    Solución: VACUUM (ANALYZE) tras cargas grandes.

Índices parciales y de expresión: los dos más infrautilizados

-- ÍNDICE PARCIAL: indexa solo las filas que cumplen una condición.
-- La cola de pedidos pendientes son 300 filas de una tabla de 40 millones.
CREATE INDEX pedido_pendiente_idx ON pedido (creado_en)
    WHERE estado IN ('NUEVO','PAGADO');
-- Ventajas: el índice ocupa kilobytes en lugar de gigabytes, cabe entero en caché,
-- y las escrituras de pedidos ya entregados NO lo tocan (menos amplificación).

SELECT id FROM pedido WHERE estado = 'NUEVO' ORDER BY creado_en LIMIT 100;
-- ⚠️ Para que el planificador use un índice parcial, tiene que poder DEMOSTRAR que
--    la consulta implica la condición del índice. 'estado = NUEVO' implica
--    "IN ('NUEVO','PAGADO')": lo usa. Pero si el estado llega como parámetro
--    ($1), no puede demostrarlo en tiempo de planificación genérica.

-- Otros usos canónicos del índice parcial:
CREATE INDEX cliente_activo_idx    ON cliente (creado_en DESC) WHERE activo;
CREATE INDEX producto_sinstock_idx ON producto (categoria_id)  WHERE stock = 0;
CREATE UNIQUE INDEX carrito_abierto_uk ON carrito (cliente_id) WHERE estado = 'ABIERTO';

-- ÍNDICE DE EXPRESIÓN: indexa el RESULTADO de una función, no la columna.
-- ❌ Este filtro no puede usar un índice sobre email:
SELECT * FROM cliente WHERE lower(email) = 'ana@example.com';

-- ✅ Con el índice de la expresión exacta, sí:
CREATE INDEX cliente_email_lower_idx ON cliente (lower(email));

-- Más ejemplos reales
CREATE INDEX pedido_anio_idx    ON pedido ((date_part('year', creado_en)));
CREATE INDEX cliente_dominio_idx ON cliente ((split_part(email, '@', 2)));
CREATE INDEX producto_color_idx  ON producto ((atributos ->> 'color'));

-- ⚠️ La expresión del índice debe coincidir LITERALMENTE con la de la consulta,
--    y la función debe ser IMMUTABLE. Por eso no puedes indexar
--    (creado_en AT TIME ZONE 'Europe/Madrid')::date en versiones antiguas:
--    la conversión depende de la base de datos de zonas horarias, que puede cambiar.

Por qué el motor no usa tu índice

«Tengo el índice pero hace un Seq Scan» es probablemente la frase más repetida en cualquier canal de dudas sobre bases de datos. Estas son las nueve causas, por orden de frecuencia:

-- Demostración de la causa nº 3, que es la más difícil de ver a simple vista
CREATE TABLE demo (id bigint PRIMARY KEY, codigo text);
CREATE INDEX demo_codigo_idx ON demo (codigo);

EXPLAIN SELECT * FROM demo WHERE codigo = 12345;
--  Seq Scan on demo   Filter: ((codigo)::bigint = 12345)
--  ↑ Postgres ha convertido LA COLUMNA a bigint para poder comparar.
--    Una función sobre la columna = adiós al índice.

EXPLAIN SELECT * FROM demo WHERE codigo = '12345';
--  Index Scan using demo_codigo_idx   Index Cond: (codigo = '12345'::text)

-- El mismo problema desde Java, y es MUY frecuente:
--   ❌ ps.setLong(1, 12345L)   sobre una columna text
--   ✅ ps.setString(1, "12345")
-- Con JPA aparece cuando el tipo del campo Java no coincide con el de la columna.

-- Forzar la mano al planificador SOLO para diagnosticar (nunca en producción):
SET enable_seqscan = off;
EXPLAIN (ANALYZE) SELECT * FROM pedido WHERE cliente_id = 42;
SET enable_seqscan = on;
-- Si con el índice forzado va más rápido que sin él, el problema son las
-- estadísticas o los parámetros de coste (random_page_cost), no el índice.

El coste de los índices: no son gratis

CADA ÍNDICE ADICIONAL SOBRE UNA TABLA CUESTA:

  ESCRITURA   Un INSERT en una tabla con 6 índices son 7 estructuras que actualizar.
              Un UPDATE que toque una columna indexada invalida la entrada antigua y
              crea una nueva (en Postgres, con MVCC, el UPDATE crea una fila nueva y
              hay que añadirla a TODOS los índices salvo que aplique la optimización
              HOT: solo si NINGUNA columna indexada cambia y hay hueco en la página).
              Efecto medible: pasar de 2 a 8 índices puede reducir a la mitad el
              rendimiento de inserción.

  ESPACIO     Un índice B-tree suele ocupar entre el 15% y el 60% del tamaño de la
              tabla. Seis índices pueden ocupar más que los datos.

  CACHÉ       shared_buffers es limitado. Cada página de índice inútil que se lee
              expulsa una página de datos que sí se usaba.

  VACUUM      Más índices = VACUUM más lento = más ventana de bloat.

  PLANIFICACIÓN  Más caminos posibles = planificar cuesta un poco más (marginal).

CONCLUSIÓN: los índices se diseñan a partir de las CONSULTAS REALES, no "por si
acaso". Y se revisan periódicamente para borrar los que nadie usa.

EXPLAIN y EXPLAIN ANALYZE, línea a línea

-- EXPLAIN: solo el plan estimado. No ejecuta nada. Instantáneo y siempre seguro.
EXPLAIN SELECT * FROM pedido WHERE cliente_id = 42;

-- EXPLAIN ANALYZE: EJECUTA la consulta y mide de verdad.
-- ⚠️ Con INSERT/UPDATE/DELETE, ejecuta los cambios. Protégete:
BEGIN;
EXPLAIN (ANALYZE) DELETE FROM pedido WHERE id = 1;
ROLLBACK;

-- La invocación que debes usar siempre para diagnosticar en serio:
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT c.nombre, count(*) AS pedidos, sum(p.total) AS facturado
FROM   cliente c
JOIN   pedido p ON p.cliente_id = c.id
WHERE  p.creado_en >= '2026-01-01'
GROUP  BY c.id, c.nombre
ORDER  BY facturado DESC
LIMIT  10;

-- Qué aporta cada opción:
--   ANALYZE   ejecuta y mide: filas reales, tiempo real, número de bucles
--   BUFFERS   bloques leídos de caché (hit), de disco (read), sucios y escritos
--   VERBOSE   columnas de salida, nombres de esquema
--   SETTINGS  parámetros no predeterminados que han influido en el plan
--   WAL       WAL generado (útil en escrituras)
--   FORMAT JSON  salida para explain.dalibo.com o pgMustard

Un plan real, interpretado línea a línea

Limit  (cost=1893.44..1893.47 rows=10 width=48)
       (actual time=41.905..41.909 rows=10 loops=1)
  Buffers: shared hit=612 read=284
  ->  Sort  (cost=1893.44..1901.19 rows=3100 width=48)
            (actual time=41.903..41.905 rows=10 loops=1)
        Sort Key: (sum(p.total)) DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  HashAggregate  (cost=1750.10..1781.10 rows=3100 width=48)
                           (actual time=39.812..41.104 rows=3100 loops=1)
              Group Key: c.id
              Batches: 1  Memory Usage: 913kB
              ->  Hash Join  (cost=189.00..1610.10 rows=18660 width=26)
                             (actual time=1.902..30.451 rows=18654 loops=1)
                    Hash Cond: (p.cliente_id = c.id)
                    ->  Seq Scan on pedido p  (cost=0.00..1372.00 rows=18660 width=14)
                                              (actual time=0.011..18.220 rows=18654 loops=1)
                          Filter: (creado_en >= '2026-01-01 00:00:00+01'::timestamptz)
                          Rows Removed by Filter: 31346
                    ->  Hash  (cost=150.00..150.00 rows=3120 width=20)
                              (actual time=1.870..1.871 rows=3120 loops=1)
                          Buckets: 4096  Batches: 1  Memory Usage: 197kB
                          ->  Seq Scan on cliente c  (cost=0.00..150.00 rows=3120 width=20)
                                                     (actual time=0.008..0.702 rows=3120 loops=1)
Planning Time: 0.412 ms
Execution Time: 42.031 ms


CÓMO SE LEE ────────────────────────────────────────────────────────────────────
 · Es un ÁRBOL. Se ejecuta de DENTRO hacia FUERA (de las hojas más indentadas
   hacia arriba). El nodo de arriba es el resultado final.
 · Cada nodo consume las filas de sus hijos y produce filas para su padre.
 · Empieza a leer por el nodo MÁS indentado: aquí, los dos Seq Scan.

QUÉ SIGNIFICA CADA NÚMERO ──────────────────────────────────────────────────────
 cost=1893.44..1893.47
     El primero es el coste de arrancar (antes de la 1ª fila), el segundo el coste
     total. NO son milisegundos: son unidades arbitrarias donde 1.0 = leer un
     bloque secuencial. Solo sirven para comparar planes entre sí.
 rows=10
     Filas ESTIMADAS por el planificador a partir de las estadísticas.
 width=48
     Bytes medios por fila. Si es enorme, quizá traes columnas que no necesitas.
 actual time=41.905..41.909
     Tiempo real en ms hasta la 1ª fila .. hasta la última. ACUMULADO, incluyendo
     el tiempo de los hijos.
 rows=10 loops=1
     Filas REALES y cuántas veces se ejecutó el nodo.
     ⚠️ CLAVE: las filas y el tiempo mostrados son POR BUCLE. Con loops=500 el
     coste real de ese nodo es tiempo × 500. Es el error de lectura más frecuente.
 Buffers: shared hit=612 read=284
     612 bloques servidos desde caché, 284 leídos del sistema de ficheros.
     Muchos "read" en una consulta repetida = memoria insuficiente o tabla enorme.
 Rows Removed by Filter: 31346
     ⚠️ La señal de índice ausente más clara que existe: el motor leyó 50.000
     filas y tiró 31.346. Un índice sobre creado_en habría leído solo 18.654.

DIAGNÓSTICO DE ESTE PLAN ───────────────────────────────────────────────────────
 1. Seq Scan on pedido con Filter y 31.346 filas descartadas
    →  falta CREATE INDEX ON pedido (creado_en);
 2. Estimado 18.660 vs real 18.654 → las estadísticas son buenas (bien).
 3. Sort Method: top-N heapsort → Postgres es listo: con LIMIT 10 no ordena las
    3.100 filas completas, mantiene un montículo de 10. Si dijera
    "external merge Disk: 24MB" habría que subir work_mem.
 4. Hash Join con la tabla pequeña (cliente) del lado del Hash: correcto.
 5. Planning 0.4 ms / Execution 42 ms → el problema es la ejecución, no el plan.

Los nodos que vas a ver y qué te dice cada uno

ANALYZE, VACUUM, bloat y las vistas de estadísticas

-- ANALYZE recalcula las estadísticas que usa el planificador. Sin ellas, adivina.
ANALYZE pedido;
ANALYZE;                        -- toda la base de datos

-- Ver lo que el planificador cree saber
SELECT attname, n_distinct, null_frac, correlation,
       most_common_vals, most_common_freqs
FROM   pg_stats WHERE tablename = 'pedido' AND attname IN ('estado','cliente_id');
--   n_distinct   valores distintos (negativo = fracción sobre el total de filas)
--   null_frac    proporción de NULL
--   correlation  1 = orden físico igual al lógico (ideal para BRIN y Index Scan)

-- Más precisión en columnas con distribución sesgada (por defecto 100 "buckets")
ALTER TABLE pedido ALTER COLUMN estado SET STATISTICS 1000;
ANALYZE pedido;

-- ESTADÍSTICAS EXTENDIDAS: la solución al problema de columnas correlacionadas.
-- El planificador asume independencia: si 'ciudad = Madrid' es el 10% y
-- 'provincia = Madrid' es el 10%, estima 1% cuando en realidad es 10%.
CREATE STATISTICS cliente_geo_stx (dependencies, ndistinct) ON pais, ciudad FROM cliente;
ANALYZE cliente;

-- VACUUM: marca como reutilizable el espacio de las versiones de fila muertas.
-- NO devuelve espacio al sistema operativo (salvo al final del fichero).
VACUUM pedido;
VACUUM (ANALYZE, VERBOSE) pedido;

-- VACUUM FULL: reescribe la tabla compactada, DEVUELVE espacio al SO...
-- ...y toma ACCESS EXCLUSIVE LOCK: la tabla queda inaccesible. NUNCA en producción
-- sin ventana de parada. Alternativa online: la extensión pg_repack.
VACUUM FULL pedido;

-- Diagnóstico de bloat y de mantenimiento pendiente
SELECT relname,
       n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS pct_muertas,
       last_vacuum, last_autovacuum, last_analyze, autovacuum_count
FROM   pg_stat_user_tables
ORDER  BY n_dead_tup DESC
LIMIT  20;

-- Tamaños reales: tabla, índices y TOAST
SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total,
       pg_size_pretty(pg_relation_size(relid))       AS solo_tabla,
       pg_size_pretty(pg_indexes_size(relid))        AS solo_indices
FROM   pg_catalog.pg_statio_user_tables
ORDER  BY pg_total_relation_size(relid) DESC
LIMIT  20;

-- Autovacuum más agresivo en tablas muy actualizadas (por tabla, no global)
ALTER TABLE pedido SET (autovacuum_vacuum_scale_factor = 0.02,
                        autovacuum_analyze_scale_factor = 0.01);

-- Transacciones abiertas más antiguas: el enemigo silencioso
SELECT pid, usename, application_name, state,
       now() - xact_start  AS duracion_transaccion,
       now() - query_start  AS duracion_consulta,
       left(query, 80)      AS consulta
FROM   pg_stat_activity
WHERE  xact_start IS NOT NULL
  AND  now() - xact_start > interval '5 minutes'
ORDER  BY xact_start;

-- Y el remedio preventivo, mejor que la alerta (PostgreSQL 17+ para el tercero):
ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
ALTER SYSTEM SET statement_timeout = '30s';           -- por rol o sesión, mejor
ALTER SYSTEM SET transaction_timeout = '60s';
SELECT pg_reload_conf();

-- Matar una sesión concreta (cancelar la consulta / cerrar la conexión)
SELECT pg_cancel_backend(12345);      -- educado: cancela la consulta
SELECT pg_terminate_backend(12345);   -- contundente: cierra la conexión

pg_stat_statements: encontrar las consultas que de verdad cuestan

-- Activación (requiere reinicio porque carga una librería compartida)
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
ALTER SYSTEM SET pg_stat_statements.max = 10000;
ALTER SYSTEM SET pg_stat_statements.track = 'top';
-- ...reiniciar...
CREATE EXTENSION pg_stat_statements;

-- LA CONSULTA MÁS ÚTIL DE TODO EL MÓDULO: top 20 por TIEMPO TOTAL.
-- Ordena por total_exec_time, NO por mean_exec_time: una consulta de 3 ms
-- ejecutada 2 millones de veces consume más que un informe de 40 s al día.
SELECT round(total_exec_time::numeric / 1000, 1) AS total_s,
       calls,
       round(mean_exec_time::numeric, 2)         AS media_ms,
       round(stddev_exec_time::numeric, 2)       AS desv_ms,
       rows,
       round(100.0 * shared_blks_hit /
             NULLIF(shared_blks_hit + shared_blks_read, 0), 1) AS cache_pct,
       left(regexp_replace(query, '\s+', ' ', 'g'), 100)       AS consulta
FROM   pg_stat_statements
WHERE  query NOT LIKE '%pg_stat_statements%'
ORDER  BY total_exec_time DESC
LIMIT  20;

-- Las que más se ejecutan (candidatas a caché o a un N+1 desde la aplicación)
SELECT calls, round(mean_exec_time::numeric,3) AS media_ms,
       left(query, 90) AS consulta
FROM   pg_stat_statements ORDER BY calls DESC LIMIT 20;

-- Las más irregulares (a veces rápidas, a veces no: parámetros o bloqueos)
SELECT calls, round(mean_exec_time::numeric,2) AS media,
       round(stddev_exec_time::numeric,2) AS desv, left(query,80)
FROM   pg_stat_statements
WHERE  calls > 100
ORDER  BY stddev_exec_time DESC LIMIT 20;

-- Reiniciar los contadores antes de una prueba de carga
SELECT pg_stat_statements_reset();

pg_stat_user_indexes: borrar lo que nadie usa

-- Índices que nunca se han usado desde el último reinicio de estadísticas
SELECT s.schemaname, s.relname AS tabla, s.indexrelname AS indice,
       s.idx_scan AS veces_usado,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS tamano
FROM   pg_stat_user_indexes s
JOIN   pg_index i ON i.indexrelid = s.indexrelid
WHERE  s.idx_scan = 0
  AND  NOT i.indisunique          -- no toques los únicos: son restricciones
  AND  NOT i.indisprimary
ORDER  BY pg_relation_size(s.indexrelid) DESC;

-- Índices duplicados o redundantes por prefijo (el mismo trabajo, doble coste)
SELECT indrelid::regclass AS tabla,
       array_agg(indexrelid::regclass) AS indices_equivalentes
FROM   pg_index
GROUP  BY indrelid, indkey, indclass, indexprs, indpred
HAVING count(*) > 1;

-- Uso de índice frente a escaneo secuencial por tabla: dónde falta un índice
SELECT relname, seq_scan, seq_tup_read, idx_scan,
       CASE WHEN seq_scan > 0 THEN seq_tup_read / seq_scan END AS filas_por_seqscan
FROM   pg_stat_user_tables
WHERE  seq_scan > 1000
ORDER  BY seq_tup_read DESC LIMIT 20;

-- ⚠️ ANTES DE BORRAR UN ÍNDICE:
--   1) Comprueba las estadísticas en TODAS las réplicas: los informes van ahí.
--   2) Ten en cuenta el batch mensual y el cierre trimestral.
--   3) En PostgreSQL 15+ puedes desactivarlo primero para ver qué pasa:
--      UPDATE pg_index SET indisvalid = false WHERE indexrelid = 'x_idx'::regclass;
--      (hazlo en una réplica o en preproducción, no en la primaria)
--   4) Borra siempre en modo concurrente para no bloquear:
DROP INDEX CONCURRENTLY IF EXISTS pedido_estado_idx;

5 · Optimización de consultas: 12 patrones con antes y después

Los doce patrones

Patrón 1 · SELECT * → solo las columnas necesarias

-- ❌ ANTES: trae 40 columnas, entre ellas un jsonb de 8 kB y un texto largo
SELECT * FROM producto WHERE categoria_id = 4;

-- ✅ DESPUÉS: solo lo que pinta el listado
SELECT id, sku, nombre, precio FROM producto WHERE categoria_id = 4;

-- POR QUÉ IMPORTA (y no es solo "menos red"):
--   1. Menos bytes por la red y menos memoria en la aplicación.
--   2. Habilita el Index Only Scan: con CREATE INDEX ... (categoria_id) INCLUDE (sku, nombre, precio)
--      la consulta ni toca la tabla.
--   3. Evita leer valores TOAST (columnas grandes se guardan fuera de la fila:
--      cada jsonb grande es un acceso adicional a otra tabla).
--   4. Sobrevive a los cambios de esquema: añadir una columna no rompe el mapeo
--      ni multiplica el tráfico de golpe.

Patrón 2 · N+1 desde la aplicación → una sola consulta

// ❌ ANTES: 1 consulta para los pedidos + 1 por pedido para el cliente = 201 idas y vueltas
List<Pedido> pedidos = jdbc.sql("SELECT id, cliente_id, total FROM pedido LIMIT 200")
                           .query(Pedido.class).list();
for (Pedido p : pedidos) {
    Cliente c = jdbc.sql("SELECT * FROM cliente WHERE id = ?")
                    .param(p.clienteId()).query(Cliente.class).single();
    p.setCliente(c);   // 200 round-trips de 1 ms = 200 ms de latencia pura
}

-- ✅ DESPUÉS: una consulta, un viaje, el motor hace el join con un Hash Join
SELECT p.id, p.total, c.id AS cliente_id, c.nombre, c.email
FROM   pedido p
JOIN   cliente c ON c.id = p.cliente_id
ORDER  BY p.creado_en DESC
LIMIT  200;

-- Si la relación es 1:N y no quieres duplicar la fila padre, agrega en la base de datos:
SELECT p.id, p.total,
       jsonb_agg(jsonb_build_object('sku', pr.sku, 'uds', l.cantidad)
                 ORDER BY l.linea_num) AS lineas
FROM   pedido p
JOIN   linea_pedido l ON l.pedido_id = p.id
JOIN   producto pr    ON pr.id = l.producto_id
WHERE  p.id = ANY($1::bigint[])
GROUP  BY p.id, p.total;

Este es el mismo problema que se ataca desde JPA con JOIN FETCH, @EntityGraph y @BatchSize en el módulo 05. Desde el lado de la base de datos, un N+1 se reconoce en pg_stat_statements por una consulta trivial con un número absurdo de calls.

Patrón 3 · OR entre columnas distintas → UNION ALL

-- ❌ ANTES: el planificador no puede usar los dos índices a la vez en un Index Scan
--    y a menudo acaba en Seq Scan
SELECT * FROM cliente WHERE email = 'ana@example.com' OR nif = '12345678Z';

-- ✅ DESPUÉS: dos búsquedas por índice, cada una óptima
SELECT * FROM cliente WHERE email = 'ana@example.com'
UNION
SELECT * FROM cliente WHERE nif = '12345678Z';
-- UNION (no ALL) porque una misma fila podría cumplir las dos condiciones.
-- Si sabes que son excluyentes, UNION ALL es más barato.

-- Alternativa cuando el OR es sobre la MISMA columna: IN o = ANY, que sí usan índice
SELECT * FROM pedido WHERE estado = 'NUEVO' OR estado = 'PAGADO';       -- funciona igual
SELECT * FROM pedido WHERE estado = ANY(ARRAY['NUEVO','PAGADO']);       -- mejor desde Java

-- Y observa que Postgres SÍ puede combinar índices con BitmapOr; comprueba el plan
-- antes de reescribir. Con índices selectivos, el BitmapOr puede ser suficiente.
EXPLAIN (ANALYZE) SELECT * FROM cliente WHERE email = 'x' OR nif = 'y';

Patrón 4 · Subconsulta correlacionada → join agregado

-- ❌ ANTES: dos subconsultas por cada uno de los 50.000 clientes = 100.000 búsquedas
SELECT c.id, c.nombre,
       (SELECT count(*)       FROM pedido p WHERE p.cliente_id = c.id) AS pedidos,
       (SELECT sum(p.total)   FROM pedido p WHERE p.cliente_id = c.id) AS gastado
FROM   cliente c;

-- ✅ DESPUÉS: un solo recorrido de pedido, agregado y unido una vez
SELECT c.id, c.nombre,
       COALESCE(r.pedidos, 0) AS pedidos,
       COALESCE(r.gastado, 0) AS gastado
FROM   cliente c
LEFT   JOIN (SELECT cliente_id, count(*) AS pedidos, sum(total) AS gastado
             FROM   pedido GROUP BY cliente_id) r ON r.cliente_id = c.id;

-- ✅ O con LATERAL, si necesitas límites o varias métricas heterogéneas y hay índice.
--    Con índice en pedido(cliente_id) y pocos clientes, LATERAL puede ganar.
SELECT c.id, c.nombre, m.*
FROM   cliente c
CROSS  JOIN LATERAL (SELECT count(*) AS pedidos, COALESCE(sum(total),0) AS gastado
                     FROM pedido p WHERE p.cliente_id = c.id) m;

Patrón 5 · Función sobre la columna en WHERE → rango o índice de expresión

-- ❌ ANTES: date() sobre la columna impide usar el índice de creado_en
SELECT * FROM pedido WHERE date(creado_en) = '2026-04-03';
-- ❌ Igual de malo, y además dependiente de la zona del servidor
SELECT * FROM pedido WHERE to_char(creado_en, 'YYYY-MM') = '2026-04';
-- ❌ Y este es el clásico de los importes
SELECT * FROM pedido WHERE total * 1.21 > 1000;

-- ✅ DESPUÉS: rango semiabierto sobre la columna desnuda → Index Scan
SELECT * FROM pedido
WHERE  creado_en >= '2026-04-03 00:00:00+02'
  AND  creado_en <  '2026-04-04 00:00:00+02';

SELECT * FROM pedido
WHERE  creado_en >= '2026-04-01' AND creado_en < '2026-05-01';

SELECT * FROM pedido WHERE total > 1000 / 1.21;    -- mueve el cálculo al literal

-- ✅ Si el filtro por la expresión es inevitable y frecuente, indexa la expresión
CREATE INDEX cliente_email_lower_idx ON cliente (lower(email));
SELECT * FROM cliente WHERE lower(email) = lower($1);

-- Truco para rangos de fechas: usa el tipo rango y un solo parámetro
SELECT * FROM pedido WHERE creado_en <@ tstzrange($1, $2, '[)');

Patrón 6 · OFFSET grande → paginación keyset

POR QUÉ OFFSET 100000 ES LENTO

  LIMIT 20 OFFSET 100000  →  el motor tiene que PRODUCIR 100.020 filas ordenadas,
                             descartar 100.000 y devolver 20.
                             El coste crece linealmente con el número de página:
                             la página 1 tarda 2 ms y la página 5.000 tarda 900 ms.

  Y hay un segundo problema, peor: si alguien inserta una fila mientras el usuario
  pasa de la página 3 a la 4, una fila se DUPLICA o se SALTA. La paginación por
  OFFSET no es estable sobre datos que cambian.

KEYSET (o "seek method"): en vez de "sáltate 100.000", di "empieza después de esta"

  Página 1:  WHERE ...                        ORDER BY creado_en DESC, id DESC LIMIT 20
  Página N:  WHERE (creado_en, id) < ($1,$2)  ORDER BY creado_en DESC, id DESC LIMIT 20
                    ↑ el cursor es la última fila de la página anterior

  Coste CONSTANTE para cualquier página: el índice te deja justo donde empiezas.

-- ❌ ANTES
SELECT id, total, creado_en FROM pedido
ORDER BY creado_en DESC LIMIT 20 OFFSET 100000;

-- ✅ DESPUÉS: keyset con comparación de tuplas (SQL estándar y muy eficiente)
SELECT id, total, creado_en FROM pedido
WHERE  (creado_en, id) < ($1::timestamptz, $2::bigint)   -- cursor de la página anterior
ORDER  BY creado_en DESC, id DESC
LIMIT  20;

-- Índice imprescindible, con las MISMAS columnas y direcciones del ORDER BY
CREATE INDEX pedido_keyset_idx ON pedido (creado_en DESC, id DESC);

-- Notas de implementación que casi siempre se olvidan:
--   · El orden debe ser TOTAL: añade la PK como último criterio o habrá filas
--     perdidas o repetidas entre páginas cuando haya empates en creado_en.
--   · La comparación de tuplas (a,b) < (x,y) es SQL estándar y Postgres la resuelve
--     con el índice compuesto; escribirla desglosada
--       WHERE creado_en < $1 OR (creado_en = $1 AND id < $2)
--     es equivalente pero el planificador la aprovecha peor.
--   · No puedes "ir a la página 137": el keyset da anterior/siguiente. En la práctica
--     nadie va a la página 137; se usa un buscador. Si el requisito es real, combina
--     keyset para el scroll con OFFSET acotado (máximo 1.000) para el salto directo.
--   · Codifica el cursor en Base64 para que la API no exponga tus columnas internas.

Patrón 7 · COUNT(*) exacto → estimado o acotado

-- ❌ ANTES: "Mostrando 20 de 4.312.887 resultados" cuesta un escaneo completo.
--    En Postgres, count(*) SIEMPRE recorre las filas (por MVCC no hay contador global).
SELECT count(*) FROM pedido;

-- ✅ OPCIÓN A: estimación instantánea del catálogo. Error típico < 5% si hay ANALYZE.
SELECT reltuples::bigint AS filas_aprox
FROM   pg_class WHERE oid = 'pedido'::regclass;

-- ✅ OPCIÓN B: estimación con filtro, leyendo el plan del propio planificador
CREATE OR REPLACE FUNCTION contar_aprox(consulta text) RETURNS bigint
LANGUAGE plpgsql AS $$
DECLARE plan json;
BEGIN
    EXECUTE 'EXPLAIN (FORMAT JSON) ' || consulta INTO plan;
    RETURN (plan -> 0 -> 'Plan' ->> 'Plan Rows')::bigint;
END $$;

SELECT contar_aprox('SELECT 1 FROM pedido WHERE estado = ''PAGADO''');

-- ✅ OPCIÓN C: cuenta acotada. "Más de 1.000 resultados" es suficiente para la interfaz
--    y cuesta lo mismo con 1.001 filas que con 40 millones.
SELECT count(*) FROM (SELECT 1 FROM pedido WHERE estado = 'PAGADO' LIMIT 1001) t;

-- ✅ OPCIÓN D: contador materializado, cuando el número exacto es un requisito
CREATE TABLE contador (tabla text PRIMARY KEY, filas bigint NOT NULL DEFAULT 0);
-- ...mantenido por trigger. Ojo: la fila del contador se convierte en un punto de
-- contención serializado. Con mucha concurrencia, agrega por lotes en lugar de fila a fila.

-- ✅ OPCIÓN E (la mejor de todas): quita el número total de la interfaz.
--    Sustituye "página 1 de 215.644" por scroll infinito o por "siguiente".
--    Google no te dice cuántos resultados hay exactamente, y nadie se queja.

Patrón 8 · IN gigante → array, unnest o tabla temporal

-- ❌ ANTES: SQL generado con 10.000 literales. Cada valor distinto produce un plan
--    distinto (se llena la caché de planes), el parseo tarda más que la ejecución
--    y algunos drivers tienen límite de parámetros (JDBC: 32.767 en Postgres).
SELECT * FROM producto WHERE id IN (1, 2, 3, /* ...9.997 más... */ 10000);

-- ✅ DESPUÉS 1: un único parámetro de tipo array. Un plan, una preparación.
SELECT * FROM producto WHERE id = ANY($1::bigint[]);
-- Desde Java:  ps.setArray(1, con.createArrayOf("bigint", ids));
--              jdbc.sql(sql).param(ids.toArray(Long[]::new))

-- ✅ DESPUÉS 2: unnest en un join, si además necesitas datos asociados a cada valor
SELECT p.id, p.nombre, x.cantidad
FROM   unnest($1::bigint[], $2::int[]) AS x(producto_id, cantidad)
JOIN   producto p ON p.id = x.producto_id;

-- ✅ DESPUÉS 3: para listas enormes (>100.000) o si hay que unir varias veces,
--    una tabla temporal con estadísticas propias gana claramente
CREATE TEMP TABLE ids_buscados (id bigint PRIMARY KEY) ON COMMIT DROP;
COPY ids_buscados FROM STDIN;      -- carga masiva desde el cliente
ANALYZE ids_buscados;              -- ¡importante! si no, el planificador estima a ciegas
SELECT p.* FROM producto p JOIN ids_buscados b ON b.id = p.id;

-- ✅ DESPUÉS 4: VALUES como tabla derivada, cómodo para listas medianas con tuplas
SELECT p.* FROM producto p
JOIN   (VALUES (1,'A'), (2,'B'), (3,'C')) AS v(id, etiqueta) ON v.id = p.id;

Patrón 9 · Ordenar sin índice → índice que entrega el orden

-- ❌ ANTES: el plan muestra "Sort  Sort Method: external merge  Disk: 84MB"
SELECT id, cliente_id, total FROM pedido
WHERE  estado = 'PAGADO'
ORDER  BY creado_en DESC
LIMIT  50;

-- ✅ DESPUÉS: índice que ya está ordenado como pide la consulta → desaparece el Sort
CREATE INDEX pedido_pagado_creado_idx
    ON pedido (creado_en DESC) WHERE estado = 'PAGADO';
-- El plan pasa a: Index Scan ... Limit → lee 50 entradas y para.

-- Cuando ordenar es inevitable (informes analíticos), dale memoria a la sesión.
-- work_mem es POR OPERACIÓN DE ORDENACIÓN Y POR CONEXIÓN: subirlo globalmente a
-- 512 MB con 200 conexiones puede agotar la RAM del servidor. Hazlo por sesión:
SET LOCAL work_mem = '256MB';
-- ... la consulta del informe ...
-- (con SET LOCAL vuelve a su valor al terminar la transacción)

-- Y verifica que ha servido: el plan debe decir "quicksort Memory: ..." y no "Disk"

Patrón 10 · DISTINCT innecesario → arreglar el join

-- ❌ ANTES: el DISTINCT está tapando una duplicación causada por el join.
--    Ordenar o hashear 2 millones de filas para quedarse con 30.000 es carísimo.
SELECT DISTINCT c.id, c.nombre
FROM   cliente c
JOIN   pedido p ON p.cliente_id = c.id
WHERE  p.total > 100;

-- ✅ DESPUÉS: lo que querías preguntar era "¿existe al menos uno?" → semi-join
SELECT c.id, c.nombre
FROM   cliente c
WHERE  EXISTS (SELECT 1 FROM pedido p WHERE p.cliente_id = c.id AND p.total > 100);
-- EXISTS para en la primera coincidencia y no duplica filas: no hay nada que deduplicar.

-- Regla práctica: cada vez que escribas DISTINCT, pregúntate POR QUÉ hay duplicados.
--   · ¿Un join 1:N que no necesitabas?      → EXISTS
--   · ¿Falta una condición en el ON?         → arregla el ON
--   · ¿Realmente quieres una fila por grupo? → DISTINCT ON o GROUP BY, que expresan la intención

-- Y ojo con DISTINCT sobre muchas columnas: si una de ellas es única, el DISTINCT
-- no elimina nada y solo pagas el coste.

Patrón 11 · Agregar en la aplicación → agregar en la base de datos

// ❌ ANTES: trae 2 millones de filas a la JVM para sumarlas. Red, GC y memoria.
List<Linea> lineas = jdbc.sql("SELECT producto_id, importe FROM linea_pedido")
                         .query(Linea.class).list();
Map<Long, BigDecimal> porProducto = lineas.stream()
        .collect(groupingBy(Linea::productoId,
                 reducing(BigDecimal.ZERO, Linea::importe, BigDecimal::add)));

-- ✅ DESPUÉS: 300 filas por la red; la agregación la hace quien tiene los datos,
--    los índices, el paralelismo y las estadísticas.
SELECT producto_id, sum(importe) AS facturado, count(*) AS lineas
FROM   linea_pedido
GROUP  BY producto_id
ORDER  BY facturado DESC;

-- Cuándo NO aplicar este patrón (que también existe):
--   · La lógica de agregación es reglas de negocio complejas que deben estar en el
--     dominio y ser testeables sin base de datos.
--   · Necesitas los datos crudos igualmente para otra cosa.
--   · La agregación es tan costosa que compite con la carga transaccional: entonces
--     va a una réplica de lectura o a una vista materializada.

Patrón 12 · Escritura fila a fila → lotes y COPY

// ❌ ANTES: 100.000 round-trips. Con 1 ms de latencia de red, 100 segundos mínimo.
for (Producto p : productos) {
    jdbc.sql("INSERT INTO producto (sku, nombre, precio) VALUES (?,?,?)")
        .params(p.sku(), p.nombre(), p.precio()).update();
}

// ✅ DESPUÉS 1: batch de JDBC. Una ida y vuelta por cada 1.000 filas.
jdbcTemplate.batchUpdate(
    "INSERT INTO producto (sku, nombre, precio) VALUES (?,?,?)",
    productos, 1000,
    (ps, p) -> { ps.setString(1, p.sku()); ps.setString(2, p.nombre());
                 ps.setBigDecimal(3, p.precio()); });
// ⚠️ En PostgreSQL, el batch de JDBC solo agrupa de verdad si añades
//    reWriteBatchedInserts=true a la URL: entonces el driver reescribe 1.000
//    INSERT en un solo INSERT ... VALUES (...),(...),(...). Mejora de 5-10×.

// ✅ DESPUÉS 2: COPY, la vía rápida real. 10-50× más que INSERT.
var copyManager = jdbcTemplate.getDataSource().getConnection()
        .unwrap(org.postgresql.PGConnection.class).getCopyAPI();
copyManager.copyIn("COPY producto (sku, nombre, precio) FROM STDIN WITH (FORMAT csv)",
                   new java.io.StringReader(csv));

-- COPY desde fichero (en el servidor) o desde el cliente con \copy en psql
COPY producto (sku, nombre, categoria_id, precio, stock)
FROM '/tmp/productos.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',');

\copy producto FROM 'productos.csv' WITH (FORMAT csv, HEADER true)

-- Receta completa para una carga masiva de verdad (millones de filas):
BEGIN;
CREATE TEMP TABLE staging (LIKE producto INCLUDING DEFAULTS);
COPY staging FROM STDIN WITH (FORMAT csv);
-- 1) Índices: crearlos DESPUÉS de la carga es mucho más rápido que mantenerlos durante
-- 2) Deduplicar y validar en SQL sobre la tabla temporal
INSERT INTO producto SELECT * FROM staging
ON CONFLICT (sku) DO UPDATE SET precio = EXCLUDED.precio, stock = EXCLUDED.stock;
COMMIT;
ANALYZE producto;         -- imprescindible: las estadísticas han quedado obsoletas

-- Y una advertencia sobre el sentido contrario: agrupar demasiado.
-- Un UPDATE de 20 millones de filas en una sola transacción genera un WAL enorme,
-- retiene el snapshot durante horas (bloat en TODA la base) y si falla, el ROLLBACK
-- tarda lo mismo. Trocea en lotes de 10.000-50.000 con commit intermedio:
DO $$
DECLARE afectadas int;
BEGIN
    LOOP
        UPDATE producto SET activo = false
        WHERE  id IN (SELECT id FROM producto
                      WHERE stock = 0 AND activo LIMIT 10000);
        GET DIAGNOSTICS afectadas = ROW_COUNT;
        EXIT WHEN afectadas = 0;
        COMMIT;                    -- válido en un bloque DO desde PostgreSQL 11
        PERFORM pg_sleep(0.05);    -- deja respirar a autovacuum y a la replicación
    END LOOP;
END $$;

Cómo priorizar: el orden que da resultados

6 · Transacciones, aislamiento y concurrencia

Una transacción es una unidad de trabajo indivisible. Es la abstracción más valiosa que ofrece una base de datos relacional, porque convierte «si falla el paso 3, deshaz los pasos 1 y 2 y no dejes a nadie ver el estado intermedio» en dos palabras: BEGIN y COMMIT. Todo lo que sigue explica qué garantiza exactamente eso y qué no.

ACID en detalle

-- Anatomía de una transacción, con todo lo que puede aparecer dentro
BEGIN;                                   -- o START TRANSACTION
    SET LOCAL statement_timeout = '5s';  -- se revierte al terminar la transacción

    INSERT INTO pedido (cliente_id, estado) VALUES (1, 'NUEVO') RETURNING id;

    SAVEPOINT antes_de_lineas;           -- punto de retorno parcial
    INSERT INTO linea_pedido (pedido_id, linea_num, producto_id, cantidad, precio_unitario)
    VALUES (currval(pg_get_serial_sequence('pedido','id')), 1, 10, 2, 89.90);

    -- si algo va mal solo en las líneas:
    -- ROLLBACK TO SAVEPOINT antes_de_lineas;
    RELEASE SAVEPOINT antes_de_lineas;

    UPDATE producto SET stock = stock - 2 WHERE id = 10 AND stock >= 2;
COMMIT;

-- ⚠️ En PostgreSQL, un error dentro de una transacción la aborta ENTERA:
--    todo comando posterior devuelve
--    "current transaction is aborted, commands ignored until end of transaction block".
--    Es distinto de MySQL/Oracle, donde un error de sentencia no aborta la transacción.
--    Por eso los SAVEPOINT son la única forma de "seguir tras un error controlado"
--    (y es exactamente lo que hace Spring con @Transactional(propagation = NESTED)).

-- Y la durabilidad se puede negociar (con cabeza):
SET synchronous_commit = off;   -- el COMMIT no espera al fsync: mucho más rápido,
-- pero puedes perder los últimos ~200 ms de transacciones confirmadas si se corta la
-- corriente. Aceptable para telemetría o para una carga masiva reprocesable.
-- INACEPTABLE para pagos. Se puede fijar por sesión, por transacción o por usuario.

Las cinco anomalías de concurrencia, con dos sesiones

1) DIRTY READ — leer datos no confirmados
   Sesión A                              Sesión B
   BEGIN;
   UPDATE producto SET stock=0
     WHERE id=10;
                                         BEGIN;
                                         SELECT stock FROM producto WHERE id=10;
                                         → 0   ⚠️ lee algo que puede no existir nunca
   ROLLBACK;                             -- B ha decidido con datos falsos
   ✅ PostgreSQL NO permite esto en NINGÚN nivel de aislamiento, ni siquiera en
      READ UNCOMMITTED (que existe pero se comporta como READ COMMITTED).

2) NON-REPEATABLE READ — la misma fila cambia dentro de mi transacción
   Sesión A                              Sesión B
   BEGIN;  -- READ COMMITTED
   SELECT stock FROM producto WHERE id=10;
   → 50
                                         UPDATE producto SET stock=30 WHERE id=10;
                                         COMMIT;
   SELECT stock FROM producto WHERE id=10;
   → 30   ⚠️ ¡la misma consulta, otro resultado!
   COMMIT;
   Se evita con REPEATABLE READ (snapshot fijo al inicio).

3) PHANTOM READ — aparecen FILAS NUEVAS que cumplen mi filtro
   Sesión A                              Sesión B
   BEGIN;  -- READ COMMITTED
   SELECT count(*) FROM pedido
     WHERE cliente_id=1;  → 3
                                         INSERT INTO pedido(cliente_id,...) VALUES(1,...);
                                         COMMIT;
   SELECT count(*) FROM pedido
     WHERE cliente_id=1;  → 4   ⚠️ un "fantasma"
   COMMIT;
   Se evita con REPEATABLE READ en PostgreSQL (por MVCC de snapshot).

4) LOST UPDATE — dos escrituras y una se pierde
   Sesión A                              Sesión B
   BEGIN;                                BEGIN;
   SELECT stock FROM producto            SELECT stock FROM producto
     WHERE id=10;  → 50                    WHERE id=10;  → 50
   -- la aplicación calcula 50-1=49       -- la aplicación calcula 50-2=48
   UPDATE producto SET stock=49
     WHERE id=10;
   COMMIT;
                                         UPDATE producto SET stock=48 WHERE id=10;
                                         COMMIT;
   Resultado: stock = 48. Se han vendido 3 unidades y solo se han restado 2.
   ⚠️ Esto ocurre en READ COMMITTED y es EL bug de concurrencia más frecuente.
   Se evita de tres formas (ver más abajo): UPDATE relativo, SELECT FOR UPDATE,
   o bloqueo optimista con versión.

5) WRITE SKEW — cada transacción es válida por separado, juntas rompen la invariante
   Regla de negocio: "siempre debe haber al menos un administrador activo".
   Hay exactamente dos: Ana y Bruno.

   Sesión A (desactiva a Ana)             Sesión B (desactiva a Bruno)
   BEGIN;  -- REPEATABLE READ             BEGIN;  -- REPEATABLE READ
   SELECT count(*) FROM usuario           SELECT count(*) FROM usuario
     WHERE admin AND activo;  → 2           WHERE admin AND activo;  → 2
   -- "hay 2, puedo desactivar 1"         -- "hay 2, puedo desactivar 1"
   UPDATE usuario SET activo=false        UPDATE usuario SET activo=false
     WHERE nombre='Ana';                    WHERE nombre='Bruno';
   COMMIT;                                COMMIT;
   → CERO administradores activos. Ninguna transacción hizo nada ilegal por separado.
   ⚠️ REPEATABLE READ NO lo evita: cada una escribe filas DISTINTAS, no hay conflicto
      de escritura que detectar. Solo lo evitan SERIALIZABLE o un bloqueo explícito.

Niveles de aislamiento: el estándar y la realidad de PostgreSQL

-- Cómo se fija el nivel
BEGIN ISOLATION LEVEL REPEATABLE READ;   -- por transacción (lo habitual)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;   -- justo tras BEGIN
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;  -- sesión
ALTER DATABASE tienda SET default_transaction_isolation = 'repeatable read'; -- global

SELECT current_setting('transaction_isolation');   -- ¿en qué nivel estoy?

-- READ COMMITTED: el "modo por defecto" y su sorpresa más importante.
-- Dentro de la MISMA transacción, dos consultas iguales pueden dar resultados
-- distintos, y un UPDATE espera al bloqueo y luego RE-LEE la fila más reciente.
BEGIN;  -- READ COMMITTED
    SELECT sum(total) FROM pedido;             -- 100.000 €
    -- ... otra sesión confirma pedidos ...
    SELECT sum(total) FROM pedido;             -- 105.000 €  → informe incoherente
COMMIT;

-- ✅ Un informe con varias consultas que deben cuadrar entre sí necesita snapshot fijo
BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;
    SELECT sum(total) FROM pedido;
    SELECT count(*)   FROM linea_pedido;       -- coherente con la línea anterior
    SELECT sum(importe) FROM pago;
COMMIT;

-- SERIALIZABLE: el nivel que resuelve el write skew, con su contrato.
-- El motor puede abortar CUALQUIERA de las transacciones en conflicto, y la
-- aplicación DEBE reintentar. Si no reintentas, has cambiado un bug de datos por
-- un error 500 esporádico, que no es mejor.
BEGIN ISOLATION LEVEL SERIALIZABLE;
    SELECT count(*) FROM usuario WHERE admin AND activo;   -- registra la lectura
    UPDATE usuario SET activo = false WHERE nombre = 'Ana';
COMMIT;
-- ERROR:  could not serialize access due to read/write dependencies among transactions
-- HINT:   The transaction might succeed if retried.
-- SQLSTATE: 40001

// El reintento es OBLIGATORIO con SERIALIZABLE. Spring Retry lo hace declarativo:
@Service
public class ServicioAdministradores {

    @Retryable(retryFor = CannotSerializeTransactionException.class,
               maxAttempts = 4,
               backoff = @Backoff(delay = 30, multiplier = 2, random = true))
    @Transactional(isolation = Isolation.SERIALIZABLE)
    public void desactivar(String nombre) {
        long activos = repo.contarAdminsActivos();
        if (activos <= 1) throw new UltimoAdministradorException();
        repo.desactivar(nombre);
    }

    @Recover
    void agotado(CannotSerializeTransactionException e, String nombre) {
        throw new ConflictoDeConcurrenciaException("Reintenta la operación", e);
    }
}
// ⚠️ Detalle crítico: el reintento tiene que envolver la transacción COMPLETA, es
// decir, estar FUERA del @Transactional. Si pones @Retryable dentro del mismo
// método transaccional o en un método interno, reintentas sobre una transacción ya
// abortada y siempre fallará. Por eso el patrón habitual es una fachada con
// @Retryable que llama a otro bean con @Transactional.
// Ver módulo 05 (05-spring-data.html#transacciones) para propagación y rollback.

MVCC: cómo PostgreSQL consigue que las lecturas no bloqueen

MVCC significa Multi-Version Concurrency Control. La idea es sencilla y sus consecuencias son enormes: un UPDATE no modifica la fila, crea una versión nueva. La antigua sigue ahí, marcada como válida hasta la transacción que la sustituyó. Cada transacción, según su snapshot, ve la versión que le corresponde.

CADA FILA FÍSICA (tupla) LLEVA DOS CAMPOS OCULTOS

  xmin = id de la transacción que la CREÓ
  xmax = id de la transacción que la BORRÓ o la sustituyó (0 si sigue viva)

  UPDATE producto SET stock = 30 WHERE id = 10;   (transacción 502)

  ANTES                                  DESPUÉS
  ┌────┬───────┬──────┬──────┐           ┌────┬───────┬──────┬──────┐
  │ id │ stock │ xmin │ xmax │           │ id │ stock │ xmin │ xmax │
  ├────┼───────┼──────┼──────┤           ├────┼───────┼──────┼──────┤
  │ 10 │  50   │ 341  │  0   │           │ 10 │  50   │ 341  │ 502  │ ← versión muerta
  └────┴───────┴──────┴──────┘           │ 10 │  30   │ 502  │  0   │ ← versión viva
                                         └────┴───────┴──────┴──────┘

  REGLA DE VISIBILIDAD (simplificada): una transacción T ve una versión si
     xmin está confirmada y es anterior al snapshot de T,  Y
     xmax es 0, o no está confirmada, o es posterior al snapshot de T.

CONSECUENCIAS QUE HAY QUE INTERIORIZAR

 ✅ Las lecturas NUNCA bloquean escrituras y las escrituras NUNCA bloquean lecturas.
    Un informe de 20 minutos no impide vender. Esto es la gran ventaja de Postgres.

 ⚠️ Las versiones muertas ocupan espacio hasta que VACUUM las recicla.
    Una tabla con muchos UPDATE crece aunque el número de filas sea constante.

 ⚠️ UPDATE de una sola columna reescribe la FILA COMPLETA (incluidas las columnas
    grandes) y añade una entrada en TODOS los índices... salvo que se dé el caso HOT
    (Heap-Only Tuple): ninguna columna indexada cambia Y hay hueco libre en la misma
    página. Diseñar para HOT (fillfactor, no indexar columnas que cambian mucho) es
    una optimización real en tablas de contadores.

 ⚠️ count(*) tiene que recorrer filas para comprobar visibilidad: no hay contador global.

 ⚠️ Una transacción abierta muy antigua congela el "horizonte" de VACUUM y provoca
    bloat en TODA la base de datos, no solo en las tablas que ella toca.

 ⚠️ Los identificadores de transacción son de 32 bits: si nunca se hace VACUUM,
    PostgreSQL detiene las escrituras para evitar el "wraparound". autovacuum lo
    gestiona solo, pero es la razón por la que no puedes desactivarlo "por rendimiento".

-- Inspeccionar el MVCC con los ojos (útil para entenderlo de verdad)
SELECT ctid, xmin, xmax, id, stock FROM producto WHERE id = 10;

CREATE EXTENSION IF NOT EXISTS pageinspect;   -- para los muy curiosos

-- Ver cuánto espacio muerto tiene una tabla
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('producto');
-- dead_tuple_percent alto y free_percent alto = bloat: considera pg_repack

-- Reservar hueco en cada página para favorecer las actualizaciones HOT en una tabla
-- con muchos UPDATE (contadores, estados, colas). Por defecto fillfactor = 100.
ALTER TABLE producto SET (fillfactor = 85);
VACUUM FULL producto;    -- necesario para que el nuevo fillfactor se aplique
-- (o pg_repack si no puedes bloquear la tabla)

Bloqueos de fila: FOR UPDATE y compañía

-- EL PROBLEMA (lost update) Y SUS TRES SOLUCIONES

-- ❌ SOLUCIÓN CERO (incorrecta): leer, calcular en Java, escribir
--    SELECT stock ... ; if (stock >= 1) { UPDATE producto SET stock = stock - 1 ... }

-- ✅ SOLUCIÓN 1: UPDATE RELATIVO. La mejor cuando basta con un contador.
--    Es atómico: el motor bloquea la fila, lee el valor actual y calcula.
UPDATE producto SET stock = stock - 1
WHERE  id = 10 AND stock >= 1
RETURNING stock;
-- Si devuelve 0 filas, no había stock. Sin bloqueos explícitos y sin condición de
-- carrera. Este patrón resuelve el 80% de los casos y es el que se olvida.

-- ✅ SOLUCIÓN 2: BLOQUEO PESIMISTA con SELECT ... FOR UPDATE.
--    Necesario cuando hay que LEER, aplicar lógica de negocio compleja y ESCRIBIR.
BEGIN;
    SELECT id, stock, precio FROM producto WHERE id = 10 FOR UPDATE;
    -- La fila queda bloqueada: cualquier otra transacción que haga FOR UPDATE
    -- o UPDATE sobre ella ESPERA aquí hasta el COMMIT.
    -- ... lógica de negocio con el valor leído ...
    UPDATE producto SET stock = stock - 1 WHERE id = 10;
COMMIT;   -- ← se libera el bloqueo

-- Variantes de FOR UPDATE y para qué sirve cada una
SELECT ... FOR UPDATE;               -- bloqueo exclusivo: nadie más lee-para-escribir
SELECT ... FOR NO KEY UPDATE;        -- más suave: permite FKs que apunten a la fila
SELECT ... FOR SHARE;                -- varios pueden leer-para-escribir, nadie modifica
SELECT ... FOR KEY SHARE;            -- el más suave: solo impide cambiar la clave
SELECT ... FOR UPDATE NOWAIT;        -- si está bloqueada, ERROR 55P03 inmediato
SELECT ... FOR UPDATE SKIP LOCKED;   -- si está bloqueada, la IGNORA y sigue
SELECT ... FOR UPDATE OF producto;   -- en un join, bloquea solo esa tabla

-- ⚠️ Cuidado con FOR UPDATE y LIMIT sin ORDER BY determinista: dos sesiones pueden
--    bloquear filas en orden distinto y producir un deadlock.

-- ✅ SOLUCIÓN 3: BLOQUEO OPTIMISTA con columna de versión. Sin bloqueos, escala
--    mucho mejor, pero exige reintento. Es lo que hace @Version de JPA.
ALTER TABLE producto ADD COLUMN version int NOT NULL DEFAULT 0;

UPDATE producto
   SET stock = $2, version = version + 1
 WHERE id = $1 AND version = $3;      -- $3 es la versión que leyó la aplicación
-- 0 filas afectadas = alguien lo cambió antes: relee y reintenta (o avisa al usuario).

SKIP LOCKED: una cola de trabajos con PostgreSQL

FOR UPDATE SKIP LOCKED (PostgreSQL 9.5+) convierte una tabla en una cola de trabajo con competencia entre consumidores, sin condiciones de carrera y sin que ningún worker espere a otro. Para volúmenes moderados (hasta unos cuantos miles de mensajes por segundo) evita tener que operar un Kafka o un RabbitMQ, y te da transaccionalidad con el resto de tus datos, que es una ventaja enorme.

CREATE TABLE tarea (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tipo         text        NOT NULL,
    payload      jsonb       NOT NULL,
    estado       text        NOT NULL DEFAULT 'PENDIENTE'
                 CHECK (estado IN ('PENDIENTE','EN_CURSO','HECHA','FALLIDA')),
    intentos     int         NOT NULL DEFAULT 0,
    ejecutar_en  timestamptz NOT NULL DEFAULT now(),
    ultimo_error text,
    creado_en    timestamptz NOT NULL DEFAULT now()
);

-- Índice parcial: la cola son las pendientes, no los 50 millones de históricos
CREATE INDEX tarea_pendiente_idx ON tarea (ejecutar_en)
    WHERE estado = 'PENDIENTE';

-- EL CONSUMIDOR. Cada worker ejecuta esto en su propia transacción.
BEGIN;
WITH siguiente AS (
    SELECT id FROM tarea
    WHERE  estado = 'PENDIENTE' AND ejecutar_en <= now()
    ORDER  BY ejecutar_en
    FOR UPDATE SKIP LOCKED        -- ← la clave: no espera, coge otra
    LIMIT  10
)
UPDATE tarea t
   SET estado = 'EN_CURSO', intentos = t.intentos + 1
  FROM siguiente s
 WHERE t.id = s.id
RETURNING t.id, t.tipo, t.payload;
-- ... procesar las 10 tareas ...
UPDATE tarea SET estado = 'HECHA' WHERE id = ANY($1::bigint[]);
COMMIT;

-- Reintento con retardo exponencial en caso de fallo
UPDATE tarea
   SET estado = CASE WHEN intentos >= 5 THEN 'FALLIDA' ELSE 'PENDIENTE' END,
       ejecutar_en  = now() + (interval '10 seconds' * power(2, intentos)),
       ultimo_error = $2
 WHERE id = $1;

-- Recuperar tareas de un worker que murió a mitad (el "reaper"):
UPDATE tarea SET estado = 'PENDIENTE'
WHERE  estado = 'EN_CURSO' AND creado_en < now() - interval '10 minutes';
-- Mejor aún: guarda una columna 'tomada_en' y usa esa para el rescate.

Interbloqueos (deadlocks): reproducirlos y evitarlos

UN DEADLOCK ES UN CICLO DE ESPERA

  Sesión A                              Sesión B
  BEGIN;                                BEGIN;
  UPDATE producto SET stock=stock-1
    WHERE id=10;        -- bloquea 10
                                        UPDATE producto SET stock=stock-1
                                          WHERE id=20;      -- bloquea 20
  UPDATE producto SET stock=stock-1
    WHERE id=20;        -- ESPERA a B
                                        UPDATE producto SET stock=stock-1
                                          WHERE id=10;      -- ESPERA a A
                                                            -- ⛔ CICLO

        A ──espera──▶ fila 20 ──poseída por──▶ B
        ▲                                      │
        └──────poseída por── fila 10 ◀──espera─┘

  PostgreSQL detecta el ciclo tras deadlock_timeout (1 s por defecto), elige una
  víctima y la aborta con:
     ERROR: deadlock detected
     DETAIL: Process 1234 waits for ShareLock on transaction 5678...
     SQLSTATE: 40P01

-- ✅ PREVENCIÓN 1 (la más eficaz): ORDEN CANÓNICO DE BLOQUEO.
--    Si TODAS las transacciones bloquean las filas en el mismo orden, no hay ciclos.
--    Basta con ordenar por la clave primaria.
BEGIN;
    SELECT id FROM producto
    WHERE  id = ANY($1::bigint[])
    ORDER  BY id                 -- ← el orden canónico: siempre ascendente por PK
    FOR UPDATE;
    -- ...ahora ya puedes actualizar en cualquier orden...
COMMIT;

-- En Java, lo mismo: ordena los identificadores ANTES de tocar nada.
--   var ids = new ArrayList<>(pedido.productoIds()); Collections.sort(ids);

-- ✅ PREVENCIÓN 2: transacciones cortas. Un deadlock necesita dos transacciones
--    solapadas: si duran 3 ms, la probabilidad se desploma. Nunca hagas una llamada
--    HTTP, un envío de correo o una espera de usuario dentro de una transacción.

-- ✅ PREVENCIÓN 3: menos filas bloqueadas. Bloquea la fila concreta, no rangos.

-- ✅ PREVENCIÓN 4: si el conflicto es inevitable, serialízalo con un advisory lock
--    por entidad (ver más abajo) en lugar de con varios bloqueos de fila.

-- ✅ MITIGACIÓN: reintento. Un deadlock (40P01) siempre es reintentable.
--    Es un error transitorio, no un bug de datos: captúralo y vuelve a intentarlo.

-- DIAGNÓSTICO: quién bloquea a quién, ahora mismo
SELECT bloqueada.pid       AS pid_bloqueado,
       bloqueada.usename   AS usuario_bloqueado,
       left(bloqueada.query, 60) AS consulta_bloqueada,
       bloqueante.pid      AS pid_bloqueante,
       left(bloqueante.query, 60) AS consulta_bloqueante,
       now() - bloqueada.query_start AS esperando_desde
FROM   pg_stat_activity bloqueada
JOIN   LATERAL unnest(pg_blocking_pids(bloqueada.pid)) AS b(pid) ON true
JOIN   pg_stat_activity bloqueante ON bloqueante.pid = b.pid
WHERE  cardinality(pg_blocking_pids(bloqueada.pid)) > 0;

-- Dejar rastro de los deadlocks en el log para poder analizarlos después
ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM SET deadlock_timeout = '1s';
ALTER SYSTEM SET log_min_duration_statement = '500ms';
SELECT pg_reload_conf();

Bloqueos de tabla y el DDL que tumba producción

MODOS DE BLOQUEO DE TABLA (los que importan) Y QUÉ SE PERMITE A LA VEZ

  ACCESS SHARE          ← SELECT
  ROW SHARE             ← SELECT FOR UPDATE / FOR SHARE
  ROW EXCLUSIVE         ← INSERT, UPDATE, DELETE
  SHARE UPDATE EXCLUSIVE← VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY,
                          ALTER TABLE ... VALIDATE CONSTRAINT
  SHARE                 ← CREATE INDEX (sin CONCURRENTLY)
  SHARE ROW EXCLUSIVE   ← CREATE TRIGGER
  EXCLUSIVE             ← REFRESH MATERIALIZED VIEW CONCURRENTLY
  ACCESS EXCLUSIVE      ← ⛔ ALTER TABLE (mayoría), DROP, TRUNCATE, REINDEX,
                          VACUUM FULL, CLUSTER
                          BLOQUEA ABSOLUTAMENTE TODO, incluidos los SELECT.

⚠️ EL EFECTO DOMINÓ QUE TUMBA UNA APLICACIÓN:

   1. Un informe lento tiene un ACCESS SHARE sobre pedido desde hace 4 minutos.
   2. Tu ALTER TABLE pide ACCESS EXCLUSIVE  →  se pone A LA COLA esperando.
   3. Todas las consultas nuevas piden ACCESS SHARE → se ponen DETRÁS del ALTER
      (la cola de bloqueos es ordenada: no adelantan).
   4. En 30 segundos: 200 conexiones esperando, pool agotado, aplicación caída.
      Y el ALTER todavía no ha empezado.

   MORALEJA: el problema no es la duración del ALTER, es la ESPERA por el bloqueo.

-- ✅ LA REGLA DE ORO DE TODA MIGRACIÓN EN PRODUCCIÓN
SET lock_timeout = '3s';        -- si no consigo el bloqueo en 3 s, fallo y no bloqueo a nadie
SET statement_timeout = '0';    -- pero una vez empezado, déjame terminar
ALTER TABLE pedido ADD COLUMN canal text;
-- Si falla por lock_timeout, se reintenta más tarde. Un despliegue fallido es
-- infinitamente mejor que una caída de 20 minutos.

-- Y para las operaciones largas, la versión concurrente
CREATE INDEX CONCURRENTLY pedido_canal_idx ON pedido (canal);
--   · No bloquea escrituras (toma SHARE UPDATE EXCLUSIVE).
--   · Tarda 2-3 veces más y hace dos pasadas sobre la tabla.
--   · NO se puede ejecutar dentro de una transacción (ni en un bloque de Flyway
--     transaccional: usa una migración con transaction = false).
--   · Si falla, deja un índice INVÁLIDO que hay que borrar a mano:
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;
DROP INDEX CONCURRENTLY pedido_canal_idx;   -- y volver a crearlo

Advisory locks: un mutex distribuido gratis

Un advisory lock es un bloqueo con nombre (un número de 64 bits) que no está asociado a ninguna fila: lo interpretas tú. Sirve para serializar procesos entre varias instancias de tu aplicación, y es una de las herramientas más útiles y menos conocidas de PostgreSQL.

-- Nivel de sesión: se mantiene hasta que lo sueltas o cierras la conexión
SELECT pg_advisory_lock(42);              -- espera hasta obtenerlo
SELECT pg_try_advisory_lock(42);          -- true/false inmediato, no espera
SELECT pg_advisory_unlock(42);

-- Nivel de transacción (RECOMENDADO): se libera solo al terminar la transacción,
-- así que es imposible olvidarse de soltarlo.
BEGIN;
    SELECT pg_try_advisory_xact_lock(hashtext('cierre-mensual-2026-04')) AS obtenido;
    -- si es false, otra instancia ya está haciendo el cierre: sal sin hacer nada
    -- ... el trabajo exclusivo ...
COMMIT;

-- Dos claves de 32 bits: muy práctico para "clase de objeto + id"
SELECT pg_advisory_xact_lock(1001, cliente_id);   -- 1001 = "espacio de nombres cliente"

-- Ver los advisory locks activos
SELECT pid, objid, classid, granted FROM pg_locks WHERE locktype = 'advisory';

// Caso de uso 1: un job programado que solo debe ejecutarse en UNA instancia
@Scheduled(cron = "0 0 3 * * *")
@Transactional
public void cierreDiario() {
    Boolean obtenido = jdbc.sql("SELECT pg_try_advisory_xact_lock(hashtext(?))")
                           .param("cierre-diario")
                           .query(Boolean.class).single();
    if (!Boolean.TRUE.equals(obtenido)) {
        log.info("Otra instancia está ejecutando el cierre; salgo.");
        return;
    }
    cerrarDia();       // exclusivo en todo el clúster, sin Redis ni ZooKeeper
}

// Caso de uso 2: serializar por entidad para evitar deadlocks entre varias tablas
@Transactional
public void procesarPedido(long pedidoId) {
    jdbc.sql("SELECT pg_advisory_xact_lock(?, ?)").params(2001, pedidoId).query().rowSet();
    // A partir de aquí soy el único procesando ESE pedido, en cualquier instancia.
    // Los demás pedidos siguen procesándose en paralelo sin ninguna espera.
}

Aislamiento visto desde JPA y Spring

// El aislamiento se declara en Spring, pero lo aplica la base de datos
@Transactional(isolation = Isolation.REPEATABLE_READ, readOnly = true, timeout = 10)
public InformeMensual generar(int anio, int mes) { ... }

// Bloqueo pesimista con JPA = SELECT ... FOR UPDATE
@Lock(LockModeType.PESSIMISTIC_WRITE)
@Query("select p from Producto p where p.id = :id")
Optional<Producto> findByIdParaActualizar(@Param("id") Long id);

// Con tiempo de espera, para no colgar el hilo indefinidamente
@Lock(LockModeType.PESSIMISTIC_WRITE)
@QueryHints(@QueryHint(name = "jakarta.persistence.lock.timeout", value = "3000"))
Optional<Producto> findConEspera(Long id);

// Bloqueo optimista: la forma idiomática y la que mejor escala
@Entity
public class Producto {
    @Id private Long id;
    @Version private int version;      // Hibernate añade "AND version = ?" al UPDATE
    private int stock;
}
// Al confirmar, si otra transacción cambió la fila: OptimisticLockingFailureException.
// Se captura, se relee y se reintenta (o se informa al usuario del conflicto).

// ⚠️ Tres trampas frecuentes:
//   1. readOnly = true NO impide escribir en la base: en Hibernate desactiva el
//      dirty checking y en Postgres marca la transacción como READ ONLY, lo que sí
//      la protege. Úsalo siempre en consultas: es más rápido y más seguro.
//   2. Un @Transactional dentro del MISMO bean no crea transacción nueva: la llamada
//      no pasa por el proxy. Extrae el método a otro bean.
//   3. Los reintentos deben estar FUERA de la transacción (ver sección de SERIALIZABLE).

// Detalle completo de propagación, rollback y proxies en el módulo 05:
// 05-spring-data.html#transacciones

7 · Diseño físico y operación en producción

Hasta aquí hemos hablado de datos y consultas. Esta sección es sobre lo que separa una base de datos que funciona en tu portátil de una que aguanta un lunes de rebajas: conexiones, particiones, réplicas, copias de seguridad y migraciones que no tumban el servicio.

Conexiones y pooling: por qué PostgreSQL sufre con miles de conexiones

MODELO DE PROCESOS DE POSTGRESQL

  Cliente 1 ──┐
  Cliente 2 ──┼──▶ postmaster ──fork()──▶ un PROCESO por conexión
  Cliente N ──┘                            · ~5-10 MB de RSS cada uno
                                           · work_mem PROPIO por operación de orden
                                           · entrada en la tabla de bloqueos
                                           · participa en cada snapshot de MVCC

  Consecuencia: el coste NO es lineal, es peor.
  Con 1.000 conexiones el servidor pasa más tiempo cambiando de contexto y
  recorriendo estructuras compartidas que ejecutando consultas.

  (MySQL usa un hilo por conexión: más barato, pero el problema de fondo es el mismo.
   Oracle tiene servidores compartidos. PostgreSQL 18 empieza a mejorar esto, pero
   la recomendación no cambia: usa un pool.)

CUÁNTAS CONEXIONES NECESITAS DE VERDAD

  Regla de partida:   conexiones_activas ≈ núcleos × 2 + husos_de_disco
  Para un servidor de 8 vCPU con NVMe:  ~20 conexiones ACTIVAS.

  Y ahora la parte contraintuitiva: si tu aplicación necesita 400 conexiones
  simultáneas para responder, el problema son las consultas o el diseño, no el
  número de conexiones. Un pool de 20 con consultas de 2 ms sirve 10.000
  transacciones por segundo.

  Ley de Little:   concurrencia = throughput × latencia
                   20 conexiones / 0,002 s = 10.000 tps

ARQUITECTURA RECOMENDADA

  50 pods × HikariCP(10) = 500 conexiones ──▶ PgBouncer (pool_mode=transaction)
                                              ──▶ 25 conexiones reales a PostgreSQL

# HikariCP en Spring Boot: los parámetros que importan y por qué
spring:
  datasource:
    url: jdbc:postgresql://pg:6432/tienda?reWriteBatchedInserts=true&ApplicationName=tienda-api
    username: app_tienda
    password: ${DB_PASSWORD}
    hikari:
      maximum-pool-size: 10        # NO lo subas "por si acaso": mide la saturación
      minimum-idle: 10             # igual al máximo evita picos de latencia al crear
      connection-timeout: 3000     # ms esperando una conexión libre → falla rápido
      validation-timeout: 2000
      idle-timeout: 600000         # 10 min
      max-lifetime: 1800000        # 30 min; debe ser MENOR que cualquier timeout
                                   # de la red o del balanceador intermedio
      leak-detection-threshold: 20000   # avisa de conexiones no devueltas
      pool-name: tienda-pool
      data-source-properties:
        # Postgres: imprescindible para que el batch de JDBC agrupe de verdad
        reWriteBatchedInserts: true
        # Cachés de sentencias preparadas del driver
        prepareThreshold: 5
        preparedStatementCacheQueries: 256
  jpa:
    properties:
      hibernate.jdbc.batch_size: 50
      hibernate.order_inserts: true
      hibernate.order_updates: true

# pgbouncer.ini mínimo pero correcto
[databases]
tienda = host=127.0.0.1 port=5432 dbname=tienda

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
max_client_conn = 2000          # clientes que PgBouncer acepta
default_pool_size = 25          # conexiones REALES por par (base de datos, usuario)
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 600
query_wait_timeout = 10         # cuánto espera un cliente en la cola de PgBouncer
max_prepared_statements = 200   # 1.21+: permite sentencias preparadas en modo transaction
server_lifetime = 3600
ignore_startup_parameters = extra_float_digits

-- DIAGNÓSTICO: "HikariPool-1 - Connection is not available, request timed out"
-- Ese error significa "no hay conexiones libres en el pool". Hay tres causas y solo
-- una se arregla subiendo el tamaño del pool.

-- 1) ¿Qué están haciendo las conexiones ahora mismo?
SELECT state, wait_event_type, wait_event, count(*)
FROM   pg_stat_activity
WHERE  datname = 'tienda'
GROUP  BY 1,2,3 ORDER BY 4 DESC;
--   'active'                    → consultas en curso: mira las lentas
--   'idle in transaction'       → ⛔ FUGA: código que abre transacción y no cierra
--   'idle'                      → conexiones del pool en reposo: normal
--   wait_event = 'ClientRead'   → el servidor espera a la APLICACIÓN

-- 2) Consultas activas más largas: si hay 10 consultas de 30 s, el pool de 10 está
--    lleno por definición. El problema es la consulta, no el pool.
SELECT pid, now() - query_start AS duracion, state, left(query, 100)
FROM   pg_stat_activity
WHERE  state = 'active' AND datname = 'tienda'
ORDER  BY query_start;

-- 3) Uso frente a límite global
SELECT count(*) AS conexiones,
       current_setting('max_connections')::int AS maximo,
       count(*) FILTER (WHERE state = 'idle in transaction') AS fugas
FROM   pg_stat_activity;

-- LAS TRES CAUSAS Y SU ARREGLO
--   a) Consultas lentas          → índices y reescritura (secciones 4 y 5)
--   b) 'idle in transaction'     → transacción abierta en el código: quita el
--      @Transactional de métodos que hacen llamadas HTTP; pon
--      idle_in_transaction_session_timeout como red de seguridad
--   c) Pool realmente pequeño    → súbelo... y añade PgBouncer si hay muchos pods

Particionado: cuándo, cómo y qué gana

Particionar es dividir una tabla lógica en varias tablas físicas según una clave. PostgreSQL 10+ lo soporta de forma declarativa. No es una optimización general: una tabla de 10 millones de filas con buenos índices no necesita particionado. Se justifica a partir de decenas o cientos de millones de filas, y sobre todo cuando hay un patrón temporal claro.

-- TABLA PARTICIONADA POR RANGO DE FECHA
CREATE TABLE evento (
    id          bigint GENERATED ALWAYS AS IDENTITY,
    pedido_id   bigint      NOT NULL,
    tipo        text        NOT NULL,
    payload     jsonb       NOT NULL DEFAULT '{}',
    ocurrido_en timestamptz NOT NULL,
    PRIMARY KEY (id, ocurrido_en)      -- ⚠️ la clave de partición DEBE estar en la PK
) PARTITION BY RANGE (ocurrido_en);

CREATE TABLE evento_2026_03 PARTITION OF evento
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
CREATE TABLE evento_2026_04 PARTITION OF evento
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

-- Partición por defecto: recoge lo que no encaje. Sin ella, un INSERT fuera de rango
-- da error... lo cual a veces es justo lo que quieres (detectar datos mal fechados).
CREATE TABLE evento_resto PARTITION OF evento DEFAULT;

-- Los índices se declaran en la tabla padre y se propagan a todas las particiones
CREATE INDEX evento_pedido_idx ON evento (pedido_id);
CREATE INDEX evento_tipo_idx   ON evento (tipo, ocurrido_en DESC);

-- PARTITION PRUNING: el planificador descarta particiones enteras.
-- Aquí solo toca evento_2026_04, no las 60 particiones del histórico.
EXPLAIN (ANALYZE)
SELECT count(*) FROM evento
WHERE  ocurrido_en >= '2026-04-01' AND ocurrido_en < '2026-04-15';
--  Append
--    ->  Seq Scan on evento_2026_04   ← una sola partición

-- ⚠️ Si el filtro NO incluye la clave de partición, se leen TODAS:
SELECT * FROM evento WHERE pedido_id = 4711;        -- toca las 60 particiones
-- Por eso la clave de partición tiene que estar en las consultas frecuentes.
-- Comprueba que el pruning está activo (por defecto lo está):
SHOW enable_partition_pruning;

-- LA GRAN VENTAJA: retención instantánea. Borrar un mes es un DROP, no un DELETE.
DROP TABLE evento_2025_03;                       -- milisegundos, sin bloat
-- Frente a: DELETE FROM evento WHERE ocurrido_en < '2025-04-01';  → horas y bloat

-- Desacoplar sin borrar (para archivar en almacenamiento frío)
ALTER TABLE evento DETACH PARTITION evento_2025_03 CONCURRENTLY;

-- Adjuntar una tabla ya existente como partición, sin bloqueo largo:
-- añade primero un CHECK que garantice el rango y el ATTACH no tendrá que escanear.
ALTER TABLE evento_2026_05 ADD CONSTRAINT rango_ck
    CHECK (ocurrido_en >= '2026-05-01' AND ocurrido_en < '2026-06-01');
ALTER TABLE evento ATTACH PARTITION evento_2026_05
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

-- HASH para repartir carga cuando no hay criterio temporal
CREATE TABLE sesion (
    id uuid NOT NULL, cliente_id bigint NOT NULL, datos jsonb,
    PRIMARY KEY (id, cliente_id)
) PARTITION BY HASH (cliente_id);
CREATE TABLE sesion_0 PARTITION OF sesion FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sesion_1 PARTITION OF sesion FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sesion_2 PARTITION OF sesion FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sesion_3 PARTITION OF sesion FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Archivado y retención: los datos que no borras te van a costar dinero

-- Toda tabla que crece sin límite necesita una política de retención POR ESCRITO,
-- decidida con negocio y con la asesoría legal. Cuatro niveles:

-- NIVEL 1: partición + DROP (lo ideal, ya visto)
DROP TABLE evento_2025_03;

-- NIVEL 2: mover a tabla de archivo en la misma base, con menos índices
CREATE TABLE pedido_archivo (LIKE pedido);        -- sin INCLUDING INDEXES: no hacen falta
WITH movidos AS (
    DELETE FROM pedido
    WHERE  creado_en < now() - interval '3 years'
      AND  estado IN ('ENTREGADO','CANCELADO')
    RETURNING *
)
INSERT INTO pedido_archivo SELECT * FROM movidos;
-- ⚠️ Trocea esto en lotes de 10.000 con COMMIT: un DELETE de 20 millones de filas
--    en una transacción genera un WAL enorme y bloquea VACUUM durante horas.

-- NIVEL 3: exportar a almacenamiento frío (S3 en Parquet) y borrar de la base
COPY (SELECT * FROM pedido WHERE creado_en < '2023-01-01')
  TO '/tmp/pedidos_2022.csv' WITH (FORMAT csv, HEADER true);
-- Luego se sube a S3 con clase de almacenamiento Glacier y se consulta con Athena
-- o DuckDB cuando (raramente) haga falta.

-- NIVEL 4: agregar y tirar el detalle. Muchas veces es la respuesta correcta:
-- nadie necesita las 400 millones de líneas de 2019, sino las ventas por mes.
INSERT INTO venta_mensual (mes, categoria_id, unidades, importe)
SELECT date_trunc('month', p.creado_en), pr.categoria_id,
       sum(l.cantidad), sum(l.importe)
FROM   pedido p
JOIN   linea_pedido l ON l.pedido_id = p.id
JOIN   producto pr    ON pr.id = l.producto_id
WHERE  p.creado_en < '2023-01-01'
GROUP  BY 1, 2
ON CONFLICT (mes, categoria_id) DO NOTHING;

Réplicas de lectura y lag de replicación

REPLICACIÓN FÍSICA POR STREAMING DEL WAL

   ┌──────────────┐   WAL   ┌──────────────┐   WAL   ┌──────────────┐
   │  PRIMARIA    │────────▶│  RÉPLICA 1   │────────▶│  RÉPLICA 2   │  (cascada)
   │ lee+escribe  │         │ solo lectura │         │ solo lectura │
   └──────────────┘         └──────────────┘         └──────────────┘
        │                          │
        │ backups                  │ informes, búsquedas, exportaciones
        ▼                          ▼

  · Asíncrona (por defecto): el COMMIT no espera a la réplica. Rápido, pero puedes
    perder las últimas transacciones en un failover (RPO > 0).
  · Síncrona: el COMMIT espera confirmación de N réplicas. RPO = 0, pero cada
    escritura paga la latencia de red. Con synchronous_commit = remote_apply,
    además esperas a que la réplica lo APLIQUE (lectura consistente garantizada).

EL PROBLEMA QUE VAS A TENER: "read your own writes"

  t0  POST /pedidos          → escribe en la PRIMARIA, devuelve 201 Created
  t1  GET  /pedidos/4711     → lee de la RÉPLICA, que va 80 ms por detrás
                             → 404 Not Found
  El usuario acaba de crear algo que "no existe". Y es intermitente, así que
  el informe de error dirá "a veces no se guarda".

-- Medir el lag DESDE LA PRIMARIA (por réplica conectada)
SELECT client_addr, application_name, state, sync_state,
       pg_wal_lsn_diff(sent_lsn, replay_lsn)  AS bytes_pendientes,
       write_lag, flush_lag, replay_lag       -- intervalos, muy legibles
FROM   pg_stat_replication;

-- Medir el lag DESDE LA RÉPLICA (en tiempo, que es lo que entiende el negocio)
SELECT CASE WHEN pg_is_in_recovery()
            THEN now() - pg_last_xact_replay_timestamp() END AS retraso;

-- ✅ SOLUCIONES AL "READ YOUR OWN WRITES", de menos a más sofisticada
-- a) Enrutado por operación: toda lectura dentro de la misma petición que ha escrito
--    (o en los N segundos siguientes para ese usuario) va a la primaria.
-- b) Devuelve el recurso creado en la respuesta del POST (201 + body con RETURNING):
--    resuelve el 90% de los casos y además ahorra una petición.
-- c) Consistencia causal con LSN: guarda el LSN del COMMIT y espera en la réplica.
SELECT pg_current_wal_lsn();                                   -- tras escribir
SELECT pg_wal_lsn_diff(pg_last_wal_replay_lsn(), $1) >= 0;      -- en la réplica
-- d) Replicación síncrona con remote_apply (paga latencia en TODAS las escrituras).

-- ⚠️ Una consulta larga en la réplica puede entrar en conflicto con la aplicación
--    del WAL y ser cancelada: "canceling statement due to conflict with recovery".
--    Dos ajustes, con contrapartida cada uno:
ALTER SYSTEM SET max_standby_streaming_delay = '30s';  -- retrasa el WAL hasta 30 s
ALTER SYSTEM SET hot_standby_feedback = on;            -- la réplica frena el VACUUM
                                                       -- de la primaria (¡ojo al bloat!)

// Enrutado de lecturas a la réplica en Spring: un DataSource que decide por
// la marca readOnly de la transacción.
public class RoutingDataSource extends AbstractRoutingDataSource {
    @Override protected Object determineCurrentLookupKey() {
        return TransactionSynchronizationManager.isCurrentTransactionReadOnly()
               ? "replica" : "primaria";
    }
}

@Configuration
class DataSourceConfig {
    @Bean DataSource dataSource(@Qualifier("primaria") DataSource p,
                                @Qualifier("replica")  DataSource r) {
        var routing = new RoutingDataSource();
        routing.setTargetDataSources(Map.of("primaria", p, "replica", r));
        routing.setDefaultTargetDataSource(p);
        return new LazyConnectionDataSourceProxy(routing);   // ⚠️ imprescindible:
        // sin LazyConnection, Spring pide la conexión ANTES de saber si es readOnly
    }
}

// Uso: @Transactional(readOnly = true) → réplica.  @Transactional → primaria.
// Y la regla de oro: si la lectura debe ver lo que acabas de escribir, NO pongas
// readOnly = true, aunque solo leas.

Copias de seguridad y recuperación en un punto en el tiempo (PITR)

# Volcado lógico: usa el formato "custom" (-Fc), que se comprime y permite
# restaurar selectivamente y en paralelo
pg_dump -h pg -U postgres -Fc -Z6 -f tienda.dump tienda
pg_dump -h pg -U postgres -Fd -j 4 -f tienda_dir tienda      # directorio, en paralelo

# Restaurar
pg_restore -h pg -U postgres -d tienda_nueva -j 4 tienda.dump
pg_restore -h pg -U postgres -d tienda -t pedido tienda.dump   # solo una tabla
pg_restore --schema-only -f esquema.sql tienda.dump            # solo el DDL

# No olvides los roles: no van en pg_dump de una sola base
pg_dumpall --globals-only -f globales.sql

# Copia física + archivado del WAL = PITR. En producción, con una herramienta:
#   pgBackRest (la más recomendable), Barman, o el servicio gestionado del proveedor.
pgbackrest --stanza=tienda backup --type=full
pgbackrest --stanza=tienda backup --type=incr
pgbackrest --stanza=tienda info

# LA RESTAURACIÓN QUE DE VERDAD IMPORTA: "vuelve a justo antes del desastre"
pgbackrest --stanza=tienda --delta \
           --type=time --target="2026-04-03 11:59:30+02" \
           --target-action=promote restore

# Configuración de archivado del WAL en postgresql.conf
#   wal_level = replica
#   archive_mode = on
#   archive_command = 'pgbackrest --stanza=tienda archive-push %p'
#   archive_timeout = 60      # fuerza un segmento cada minuto aunque haya poca carga

# ⚠️ LA COMPROBACIÓN QUE CASI NADIE AUTOMATIZA Y QUE LO ES TODO:
#    un job semanal que restaura la última copia en un entorno aparte, ejecuta
#    unas consultas de verificación y avisa si algo falla. Sin esto, tu RTO es
#    "desconocido", que en la práctica significa "muy alto".
pgbackrest --stanza=tienda check

Alta disponibilidad y failover

CONCEPTOS QUE HAY QUE SABER DEFENDER EN UNA REUNIÓN

  RPO (Recovery Point Objective)  ¿cuántos datos puedo perder?
       Replicación síncrona → 0.  Asíncrona → los últimos ms.  Backup diario → 24 h.

  RTO (Recovery Time Objective)   ¿cuánto puedo estar caído?
       Failover automático → 10-60 s.  Manual → minutos u horas.
       Restaurar un PITR de 2 TB → horas.

  FAILOVER  promover una réplica a primaria cuando la primaria muere.
  SWITCHOVER  hacerlo de forma planificada (mantenimiento, cambio de versión).
  SPLIT-BRAIN  dos primarias aceptando escrituras a la vez = corrupción lógica.
       Se evita con quórum y con "fencing" (STONITH): apagar de verdad a la vieja.

ARQUITECTURA TÍPICA AUTOGESTIONADA

   ┌──────────────────────────────────────────────────┐
   │  Aplicación → HAProxy / pgBouncer                │
   │                    │                             │
   │                    ▼                             │
   │  Patroni (primaria) ◀─── etcd / Consul ───▶ Patroni (réplica)
   │       │ elige líder por consenso, mueve la IP virtual
   │       ▼                                          │
   │  PostgreSQL         ══ WAL streaming ══▶  PostgreSQL
   └──────────────────────────────────────────────────┘

   Con 3 nodos de etcd y Patroni tienes failover automático en ~20-30 s.
   Alternativas: repmgr (más simple, menos automático), pg_auto_failover,
   o el operador de Kubernetes CloudNativePG / Zalando Postgres Operator.

QUÉ HACE FALTA EN LA APLICACIÓN (y siempre se olvida)

  · Reintento de conexión con backoff: durante el failover, la conexión se corta.
  · Detección de "read-only": si vas a la primaria antigua degradada, escribir da
    "cannot execute INSERT in a read-only transaction". Trátalo como reintentable.
  · Idempotencia: si el COMMIT se cortó, no sabes si se aplicó. Con una clave de
    idempotencia (pago.referencia UNIQUE) el reintento es seguro.
  · targetServerType en la URL de JDBC, que sabe hacer esto solo:
      jdbc:postgresql://pg1:5432,pg2:5432/tienda?targetServerType=primary&loadBalanceHosts=true

Esquemas multi-tenant: las cuatro opciones

-- OPCIÓN 1+2: tenant_id en cada tabla, y RLS para que el motor lo garantice.
-- Es la opción por defecto para SaaS con muchos clientes pequeños.
ALTER TABLE pedido ADD COLUMN tenant_id int NOT NULL;

-- ⚠️ El tenant_id va PRIMERO en todos los índices: es el filtro de todas las consultas
CREATE INDEX pedido_tenant_creado_idx ON pedido (tenant_id, creado_en DESC);
-- Y en las restricciones únicas, o dos tenants no podrán tener el mismo código:
CREATE UNIQUE INDEX pedido_tenant_ref_uk ON pedido (tenant_id, referencia_externa);

ALTER TABLE pedido ENABLE ROW LEVEL SECURITY;
ALTER TABLE pedido FORCE ROW LEVEL SECURITY;   -- aplica también al propietario

CREATE POLICY pedido_tenant_pol ON pedido
    USING      (tenant_id = current_setting('app.tenant_id')::int)
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::int);

-- La aplicación fija el tenant al inicio de CADA transacción
BEGIN;
    SET LOCAL app.tenant_id = '77';
    SELECT * FROM pedido;        -- solo ve los del tenant 77, sin WHERE explícito
COMMIT;

-- OPCIÓN 3: un esquema por tenant. Aislamiento alto sin multiplicar instancias.
CREATE SCHEMA tenant_acme;
-- La aplicación cambia de esquema por conexión:
SET search_path TO tenant_acme, public;
-- En Hibernate: MultiTenantConnectionProvider + CurrentTenantIdentifierResolver.
-- ⚠️ Con 2.000 tenants tienes 2.000 × 30 tablas = 60.000 relaciones en el catálogo:
--    autovacuum, pg_dump y la planificación empiezan a sufrir de verdad.

Migraciones sin downtime: expand-contract

EL PROBLEMA: durante un despliegue conviven la versión N y la N+1 de la aplicación.
El esquema tiene que ser compatible con las DOS a la vez.

   Renombrar cliente.nombre → cliente.nombre_completo en un solo paso:
     · La versión N sigue leyendo "nombre" → error 500 en producción.
     · Y no hay marcha atrás sin más downtime.

LA SOLUCIÓN: EXPAND / MIGRATE / CONTRACT, en 3 despliegues

  ┌── DESPLIEGUE 1: EXPAND (solo esquema, compatible con N) ──────────────────┐
  │ ALTER TABLE cliente ADD COLUMN nombre_completo text;   -- nullable        │
  │ CREATE TRIGGER: al escribir en cualquiera, copia a la otra                │
  │ La versión N sigue funcionando sin cambios.                              │
  └──────────────────────────────────────────────────────────────────────────┘
  ┌── DESPLIEGUE 2: MIGRATE (aplicación N+1) ────────────────────────────────┐
  │ Backfill por lotes:  UPDATE ... WHERE nombre_completo IS NULL LIMIT 10000│
  │ La aplicación N+1 escribe y lee nombre_completo.                         │
  │ ROLLBACK POSIBLE: la columna vieja sigue actualizada por el trigger.     │
  └──────────────────────────────────────────────────────────────────────────┘
  ┌── DESPLIEGUE 3: CONTRACT (limpieza, días después) ───────────────────────┐
  │ DROP TRIGGER;  ALTER TABLE cliente DROP COLUMN nombre;                   │
  │ ALTER TABLE cliente ALTER COLUMN nombre_completo SET NOT NULL;           │
  └──────────────────────────────────────────────────────────────────────────┘

REGLA GENERAL: el esquema y el código se despliegan por separado, y el esquema
siempre va un paso por delante siendo compatible hacia atrás.

-- RECETARIO DE OPERACIONES SEGURAS EN CALIENTE

-- 1) SIEMPRE, en toda migración
SET lock_timeout = '3s';

-- 2) Columna nueva NOT NULL sin reescribir la tabla (PostgreSQL 11+)
ALTER TABLE pedido ADD COLUMN canal text NOT NULL DEFAULT 'WEB';   -- instantáneo
-- (con DEFAULT constante, Postgres 11+ guarda el valor en el catálogo)
-- ❌ Con DEFAULT volátil sí reescribe: ADD COLUMN creado timestamptz DEFAULT now()

-- 3) Columna existente a NOT NULL sin bloqueo largo (PostgreSQL 12+)
ALTER TABLE pedido ADD CONSTRAINT canal_nn CHECK (canal IS NOT NULL) NOT VALID;
ALTER TABLE pedido VALIDATE CONSTRAINT canal_nn;    -- SHARE UPDATE EXCLUSIVE: suave
ALTER TABLE pedido ALTER COLUMN canal SET NOT NULL; -- ahora es instantáneo
ALTER TABLE pedido DROP CONSTRAINT canal_nn;

-- 4) Clave foránea nueva sin bloquear
ALTER TABLE pedido ADD CONSTRAINT pedido_canal_fk
    FOREIGN KEY (canal) REFERENCES canal_venta(codigo) NOT VALID;
ALTER TABLE pedido VALIDATE CONSTRAINT pedido_canal_fk;

-- 5) Índice nuevo
CREATE INDEX CONCURRENTLY pedido_canal_idx ON pedido (canal);

-- 6) Cambiar el tipo de una columna sin reescribir: columna nueva + backfill
--    (nunca ALTER COLUMN TYPE en una tabla grande en caliente)

-- 7) Renombrar sin romper nada: vista con el nombre antiguo
ALTER TABLE cliente RENAME TO cliente_v2;
CREATE VIEW cliente AS SELECT * FROM cliente_v2;    -- las vistas simples son actualizables

-- 8) Backfill por lotes, con pausas para no ahogar la replicación
DO $$
DECLARE n int;
BEGIN
    LOOP
        UPDATE cliente SET nombre_completo = nombre
        WHERE  id IN (SELECT id FROM cliente WHERE nombre_completo IS NULL LIMIT 5000);
        GET DIAGNOSTICS n = ROW_COUNT;
        EXIT WHEN n = 0;
        COMMIT;
        PERFORM pg_sleep(0.1);
    END LOOP;
END $$;

Monitorización: las métricas que de verdad avisan

-- Panel de salud en una consulta: úsala como primer diagnóstico
SELECT datname,
       numbackends                                              AS conexiones,
       xact_commit, xact_rollback,
       round(100.0 * blks_hit / NULLIF(blks_hit + blks_read, 0), 2) AS cache_hit_pct,
       tup_returned, tup_fetched,
       conflicts, deadlocks, temp_files,
       pg_size_pretty(temp_bytes)                               AS temp_escrito,
       pg_size_pretty(pg_database_size(datname))                AS tamano,
       stats_reset
FROM   pg_stat_database
WHERE  datname = current_database();
-- temp_files/temp_bytes altos = consultas ordenando en disco: revisa work_mem.

-- Exposición a Prometheus/Grafana: postgres_exporter y el panel oficial.
-- Para el APM de la aplicación: Micrometer expone métricas de HikariCP
-- (hikaricp.connections.active, .pending, .timeout) que son la primera señal
-- de que la base de datos va mal. Ver módulo 09 (09-devops-cloud.html).

Coste en la nube: lo que se paga y lo que se puede recortar

8 · Seguridad de los datos

La base de datos es donde están los datos, así que es el objetivo final de cualquier ataque. Todo lo que sigue es defensa en profundidad: si la aplicación falla —y va a fallar—, estas capas limitan el daño.

Usuarios y privilegios mínimos

-- ARQUITECTURA DE ROLES RECOMENDADA (roles sin login + usuarios con login)

-- 1) Dueño del esquema: solo se usa en las migraciones (Flyway), nunca desde la app
CREATE ROLE tienda_owner NOLOGIN;
CREATE SCHEMA tienda AUTHORIZATION tienda_owner;

-- 2) Roles de grupo por nivel de acceso
CREATE ROLE tienda_lectura   NOLOGIN;
CREATE ROLE tienda_escritura NOLOGIN;

GRANT USAGE ON SCHEMA tienda TO tienda_lectura, tienda_escritura;
GRANT SELECT ON ALL TABLES IN SCHEMA tienda TO tienda_lectura;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA tienda TO tienda_escritura;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA tienda TO tienda_escritura;  -- para IDENTITY

-- 3) CLAVE: privilegios por defecto para los objetos FUTUROS.
--    Sin esto, la tabla que cree la próxima migración no tendrá permisos y la
--    aplicación fallará en producción con "permission denied for table ...".
ALTER DEFAULT PRIVILEGES FOR ROLE tienda_owner IN SCHEMA tienda
    GRANT SELECT ON TABLES TO tienda_lectura;
ALTER DEFAULT PRIVILEGES FOR ROLE tienda_owner IN SCHEMA tienda
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO tienda_escritura;
ALTER DEFAULT PRIVILEGES FOR ROLE tienda_owner IN SCHEMA tienda
    GRANT USAGE ON SEQUENCES TO tienda_escritura;

-- 4) Usuarios reales con contraseña, cada uno con su rol
CREATE USER app_tienda   WITH PASSWORD 'xxx' IN ROLE tienda_escritura;
CREATE USER informes_bi  WITH PASSWORD 'yyy' IN ROLE tienda_lectura;
CREATE USER soporte_n2   WITH PASSWORD 'zzz' IN ROLE tienda_lectura;

-- 5) Cerrar el esquema public, que por defecto es demasiado permisivo
--    (en PostgreSQL 15+ ya viene cerrado; en versiones anteriores, hazlo a mano)
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE tienda FROM PUBLIC;
GRANT  CONNECT ON DATABASE tienda TO app_tienda, informes_bi, soporte_n2;

-- 6) Límites por usuario: el informe de BI no debe poder tumbar la tienda
ALTER ROLE informes_bi SET statement_timeout = '120s';
ALTER ROLE informes_bi SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE informes_bi SET work_mem = '128MB';
ALTER ROLE app_tienda  SET statement_timeout = '10s';
ALTER ROLE app_tienda  CONNECTION LIMIT 60;
ALTER ROLE informes_bi SET default_transaction_read_only = on;

-- 7) Permisos a nivel de COLUMNA cuando hace falta grano fino
GRANT SELECT (id, nombre, pais, creado_en) ON tienda.cliente TO soporte_n2;
-- soporte_n2 puede consultar clientes pero NO ve email ni NIF.

-- AUDITAR lo concedido: la consulta que deberías ejecutar cada trimestre
SELECT grantee, table_name, string_agg(privilege_type, ', ' ORDER BY privilege_type)
FROM   information_schema.role_table_grants
WHERE  table_schema = 'tienda'
GROUP  BY grantee, table_name
ORDER  BY grantee, table_name;

-- ¿Quién es superusuario o puede crear roles? (debería ser una lista muy corta)
SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolbypassrls, rolconnlimit
FROM   pg_roles WHERE rolcanlogin ORDER BY rolsuper DESC;

Row Level Security: el filtro que no se puede olvidar

-- RLS mueve el "WHERE tenant_id = ?" del código de la aplicación al motor.
-- Deja de ser una convención que alguien puede olvidar y pasa a ser una garantía.

ALTER TABLE pedido ENABLE ROW LEVEL SECURITY;
ALTER TABLE pedido FORCE  ROW LEVEL SECURITY;   -- aplica también al propietario

-- Política para el usuario de la aplicación
CREATE POLICY pedido_tenant ON pedido
    FOR ALL
    TO   app_tienda
    USING      (tenant_id = current_setting('app.tenant_id', true)::int)   -- al LEER
    WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::int);  -- al ESCRIBIR
-- USING filtra lo que se ve; WITH CHECK impide insertar o actualizar filas de otro
-- tenant. Si omites WITH CHECK, se puede escribir en un tenant ajeno. Es un error grave.

-- Política adicional para soporte, que puede ver todo pero solo leer
CREATE POLICY pedido_soporte ON pedido FOR SELECT TO soporte_n2 USING (true);

-- Uso desde la aplicación: SET LOCAL en cada transacción
BEGIN;
    SET LOCAL app.tenant_id = '77';
    SELECT count(*) FROM pedido;          -- solo los del tenant 77
    INSERT INTO pedido (cliente_id, tenant_id) VALUES (1, 99);
    -- ERROR: new row violates row-level security policy for table "pedido"
COMMIT;

-- Comprobar que las políticas están donde crees
SELECT tablename, policyname, roles, cmd, qual, with_check
FROM   pg_policies WHERE schemaname = 'tienda';
SELECT relname, relrowsecurity, relforcerowsecurity
FROM   pg_class WHERE relnamespace = 'tienda'::regnamespace AND relkind = 'r';

// Fijar el tenant en CADA conexión que se toma del pool, sin poder olvidarlo
@Component
class TenantConnectionInitializer {

    private final JdbcClient jdbc;

    // Se llama al inicio de cada transacción de negocio (interceptor, filtro o aspecto)
    void aplicar(int tenantId) {
        // set_config(..., true) = ámbito LOCAL a la transacción, igual que SET LOCAL
        jdbc.sql("SELECT set_config('app.tenant_id', ?, true)")
            .param(String.valueOf(tenantId))
            .query().rowSet();
    }
}

// ⚠️ TRES TRAMPAS DE RLS EN PRODUCCIÓN
// 1. PgBouncer en modo transaction: usa SET LOCAL (o set_config(...,true)), NUNCA SET.
//    Con SET, la variable se queda pegada a la conexión física y el siguiente
//    cliente hereda el tenant de otro. Es una fuga de datos silenciosa y grave.
// 2. current_setting('app.tenant_id') sin el segundo parámetro 'true' lanza excepción
//    si la variable no está definida. Con true devuelve NULL... y NULL = NULL es
//    desconocido, así que la política no deja ver NADA. Que falle cerrado es correcto:
//    asegúrate de que es ese el comportamiento y no lo contrario.
// 3. Un rol con BYPASSRLS o el superusuario ignoran las políticas. Comprueba que el
//    usuario de la aplicación no los tiene.

Cifrado: en tránsito, en reposo y por columna

-- Forzar TLS para todas las conexiones no locales, en pg_hba.conf:
--   hostssl  all  all  0.0.0.0/0  scram-sha-256
--   host     all  all  0.0.0.0/0  reject
-- Y desde Java: jdbc:postgresql://pg/tienda?sslmode=verify-full&sslrootcert=/certs/ca.crt
-- ⚠️ sslmode=require cifra pero NO valida el certificado: no protege del MITM.
--    Usa verify-full en producción. Siempre.

SELECT ssl, version, cipher, client_addr
FROM   pg_stat_ssl JOIN pg_stat_activity USING (pid)
WHERE  datname = current_database();

-- Cifrado por columna con pgcrypto (para el dato que NO se busca ni se ordena)
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE dato_sensible (
    cliente_id bigint PRIMARY KEY REFERENCES cliente(id),
    iban_cifrado bytea NOT NULL,
    iban_ultimos4 char(4) NOT NULL       -- para mostrar sin descifrar
);

INSERT INTO dato_sensible (cliente_id, iban_cifrado, iban_ultimos4)
VALUES (1, pgp_sym_encrypt('ES9121000418450200051332', $1), '1332');

SELECT pgp_sym_decrypt(iban_cifrado, $1) AS iban FROM dato_sensible WHERE cliente_id = 1;

-- ⚠️ CONSIDERACIONES SERIAS
--   · La clave NO puede estar en la base de datos ni en el código: va en Vault,
--     AWS KMS/Secrets Manager, GCP Secret Manager... (ver módulo 10).
--   · Si la clave se pasa como parámetro, aparece en pg_stat_activity y puede
--     acabar en los logs. Por eso muchos equipos cifran en la APLICACIÓN y guardan
--     un bytea opaco: la base de datos nunca ve la clave.
--   · Una columna cifrada no se puede indexar para búsquedas ni ordenar. Si necesitas
--     buscar por ella, guarda además un hash con sal fija (HMAC) e indexa ese hash:
ALTER TABLE dato_sensible ADD COLUMN iban_hmac bytea;
CREATE INDEX dato_sensible_hmac_idx ON dato_sensible (iban_hmac);
UPDATE dato_sensible SET iban_hmac = hmac('ES9121000418450200051332', $2, 'sha256');
-- Búsqueda por igualdad exacta sin descifrar nada:
SELECT cliente_id FROM dato_sensible WHERE iban_hmac = hmac($3, $2, 'sha256');

-- Y lo que NUNCA se hace: guardar contraseñas cifradas (reversibles).
-- Las contraseñas se hashean con bcrypt/argon2 y la comprobación se hace en la
-- aplicación con Spring Security. La base de datos guarda solo el hash.

Enmascaramiento de datos personales

-- PROBLEMA: preproducción y los portátiles de desarrollo tienen una copia de
-- producción con nombres, correos y NIF reales. Es la brecha de datos más habitual
-- y la más fácil de evitar. La regla: los datos personales NO salen de producción.

-- Anonimización tras restaurar un volcado en un entorno no productivo
UPDATE cliente
   SET nombre = 'Cliente ' || id,
       email  = 'cliente' || id || '@example.invalid',   -- .invalid nunca existirá
       nif    = CASE WHEN nif IS NOT NULL
                     THEN lpad((id % 100000000)::text, 8, '0') || 'Z' END,
       telefono = CASE WHEN telefono IS NOT NULL
                       THEN '+3460000' || lpad((id % 10000)::text, 4, '0') END;

-- Conserva la FORMA de los datos (longitudes, distribución, proporción de nulos)
-- para que las pruebas de rendimiento sigan siendo representativas.

-- Vista enmascarada para soporte, que necesita identificar sin ver todo
CREATE VIEW cliente_soporte AS
SELECT id,
       nombre,
       regexp_replace(email, '(.).*(@.*)', '\1***\2')            AS email_parcial,
       CASE WHEN nif IS NOT NULL THEN '****' || right(nif, 4) END AS nif_parcial,
       pais, activo, creado_en
FROM   cliente;

REVOKE SELECT ON cliente FROM soporte_n2;
GRANT  SELECT ON cliente_soporte TO soporte_n2;

-- ⚠️ Ojo: una vista NO es una barrera de seguridad por defecto. Un usuario listo
--    puede inferir datos con funciones en el WHERE. Para que lo sea:
ALTER VIEW cliente_soporte SET (security_barrier = true);
-- Y en PostgreSQL 15+, vistas que se ejecutan con los permisos de QUIEN CONSULTA:
--    CREATE VIEW ... WITH (security_invoker = true);

Auditoría, RGPD y el conflicto del derecho al olvido

-- Documentar dónde hay datos personales, para poder responder a una auditoría
COMMENT ON COLUMN cliente.email IS 'PII: dato de contacto. Base legal: contrato. Retención: 6 años tras baja.';
COMMENT ON COLUMN cliente.nif   IS 'PII: identificativo. Obligación fiscal. Retención: 6 años (art. 30 CCom).';

SELECT c.table_name, c.column_name, pgd.description
FROM   information_schema.columns c
JOIN   pg_class     t ON t.relname = c.table_name
JOIN   pg_description pgd ON pgd.objoid = t.oid AND pgd.objsubid = c.ordinal_position
WHERE  pgd.description LIKE 'PII%';

-- EL CONFLICTO REAL: "bórrame todo" vs "conserva las facturas 6 años".
-- No se resuelve borrando la fila (rompería la contabilidad y las FK), sino
-- DISOCIANDO: se destruye la identidad y se conserva el hecho económico.
BEGIN;
    -- 1) Anonimizar la identidad de forma IRREVERSIBLE
    UPDATE cliente
       SET nombre    = 'ANONIMIZADO',
           email     = 'anon-' || id || '@example.invalid',
           nif       = NULL,
           telefono  = NULL,
           anonimizado_en = now()
     WHERE id = 4711;

    -- 2) Los pedidos y las facturas SE CONSERVAN: son hechos económicos con
    --    obligación legal de conservación, y ya no identifican a nadie.
    --    (Ojo: no dejes copias del nombre en linea_pedido, envio o factura_pdf.)
    UPDATE envio SET direccion = 'ANONIMIZADO'
     WHERE pedido_id IN (SELECT id FROM pedido WHERE cliente_id = 4711);

    -- 3) Borrar de verdad lo que no tiene base legal para conservarse
    DELETE FROM carrito           WHERE cliente_id = 4711;
    DELETE FROM sesion            WHERE cliente_id = 4711;
    DELETE FROM consentimiento    WHERE cliente_id = 4711;
    DELETE FROM auditoria         WHERE tabla = 'cliente' AND fila_id = '4711';

    -- 4) Registrar la solicitud (esto sí hay que poder demostrarlo)
    INSERT INTO solicitud_rgpd (tipo, cliente_id, ejecutada_en)
    VALUES ('SUPRESION', 4711, now());
COMMIT;

-- 5) Y lo que casi nunca se hace y hay que planificar:
--    · Redis: borrar las claves con datos personales de ese cliente.
--    · Elasticsearch: borrar el documento.
--    · Almacén analítico: propagar la anonimización.
--    · Logs de aplicación: no registrar PII desde el principio (es la única solución).
--    · Copias de seguridad: no se pueden editar. Se documenta que la supresión se
--      completa cuando la copia caduca según la política de retención.

-- Auditoría a nivel de motor (complementaria a la tabla de auditoría de la sección 2)
ALTER SYSTEM SET log_statement = 'ddl';          -- 'all' es carísimo y llena el disco
ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_disconnections = on;
ALTER SYSTEM SET log_line_prefix = '%m [%p] %u@%d app=%a host=%h ';
-- Para auditoría fina y selectiva por rol o tabla: extensión pgaudit.

Inyección SQL vista desde la base de datos

// ❌ INYECTABLE. El clásico, y sigue apareciendo en 2026.
String sql = "SELECT * FROM cliente WHERE email = '" + email + "'";
// email = "x' OR '1'='1"          → devuelve TODOS los clientes
// email = "x'; DROP TABLE pedido; --"  → si el usuario tiene permiso, adiós tabla
// email = "x' UNION SELECT ... --"     → exfiltración de otras tablas

// ✅ SENTENCIA PREPARADA: el valor viaja SEPARADO del texto de la consulta,
//    así que nunca se interpreta como SQL. Es la única defensa completa.
jdbc.sql("SELECT * FROM cliente WHERE email = ?").param(email).query(Cliente.class);

// ✅ Con JPQL / Criteria / Spring Data, los parámetros también van vinculados
@Query("select c from Cliente c where c.email = :email")
Optional<Cliente> buscar(@Param("email") String email);

// ⚠️ EL CASO QUE NO SE PUEDE PARAMETRIZAR: identificadores (nombres de columna
//    o dirección de ordenación). Un parámetro nunca puede ser un nombre de columna.
// ❌ String sql = "SELECT * FROM pedido ORDER BY " + campo + " " + direccion;
// ✅ Lista blanca cerrada, con valor por defecto:
private static final Map<String, String> ORDENABLES = Map.of(
        "fecha", "creado_en", "total", "total", "estado", "estado");

String columna = ORDENABLES.getOrDefault(campo, "creado_en");
String dir     = "asc".equalsIgnoreCase(direccion) ? "ASC" : "DESC";
String sql     = "SELECT * FROM pedido ORDER BY " + columna + " " + dir + " LIMIT ?";
// El texto solo puede tomar valores de un conjunto que TÚ has escrito. No hay
// forma de inyectar nada, porque la entrada del usuario nunca llega al SQL.

// ⚠️ Y ojo con las funciones nativas "cómodas" que reciben fragmentos:
//    ...where 1=1 " + filtroConstruidoAMano  → mismo agujero, otra forma.

-- LO QUE LA BASE DE DATOS PUEDE HACER PARA LIMITAR EL DAÑO
-- (defensa en profundidad: asume que la inyección ocurrirá alguna vez)

-- 1) Privilegios mínimos: sin DROP, sin DDL, sin acceso a otras tablas.
--    Una inyección con un usuario que solo puede leer 'producto' es un incidente;
--    con el propietario, es una catástrofe.
-- 2) Sin acceso a funciones peligrosas
REVOKE EXECUTE ON FUNCTION pg_read_file(text) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION pg_ls_dir(text)    FROM PUBLIC;
-- 3) statement_timeout: corta el "OR sleep(...)" y las consultas de exfiltración masiva
ALTER ROLE app_tienda SET statement_timeout = '10s';
-- 4) RLS: aunque inyecten, solo verán su propio tenant
-- 5) pg_stat_statements y logs: detección posterior (busca patrones raros)
SELECT calls, left(query, 200) FROM pg_stat_statements
WHERE  query ILIKE '%union%select%' OR query ILIKE '%pg_sleep%'
ORDER  BY calls DESC;
-- 6) Si construyes SQL dinámico en PL/pgSQL, usa format con %I y %L,
--    que citan correctamente identificadores y literales:
CREATE OR REPLACE FUNCTION contar_de(tabla text) RETURNS bigint
LANGUAGE plpgsql AS $$
DECLARE n bigint;
BEGIN
    EXECUTE format('SELECT count(*) FROM %I', tabla) INTO n;   -- %I = identificador
    RETURN n;
END $$;
-- Nunca:  EXECUTE 'SELECT count(*) FROM ' || tabla;

-- Más sobre inyección, OWASP y validación de entrada: módulo 10 (10-seguridad.html).

9 · NoSQL: cuándo, cuál y por qué

NoSQL no significa «sin SQL» ni «moderno»: significa que se relaja alguna garantía del modelo relacional —el esquema, las transacciones, los joins o la consistencia inmediata— para ganar otra cosa: velocidad, escalado horizontal, flexibilidad o un modelo de datos más natural para un problema concreto. La pregunta correcta nunca es «¿SQL o NoSQL?», sino «¿qué garantía puedo permitirme perder y qué gano a cambio?».

CAP y PACELC, explicados con honestidad

TEOREMA CAP (Brewer, 1998; demostrado por Gilbert y Lynch, 2002)

  En presencia de una PARTICIÓN DE RED, un sistema distribuido debe elegir entre
  seguir siendo CONSISTENTE o seguir estando DISPONIBLE.

  C  Consistency         toda lectura ve la última escritura confirmada
                         (ojo: es LINEALIZABILIDAD, no la C de ACID)
  A  Availability        toda petición recibe respuesta (no un error)
  P  Partition tolerance el sistema sigue funcionando aunque se corten mensajes

  ┌───────────────────────────────────────────────────────────────────┐
  │  EL MALENTENDIDO:  "elige 2 de 3"  →  ES FALSO                    │
  │                                                                   │
  │  P no se elige: las redes SE PARTEN. Es un hecho físico, no una    │
  │  opción de diseño. Todo sistema distribuido real es P.             │
  │  Por tanto la elección REAL es una sola:  ¿C o A cuando hay        │
  │  partición?  Y solo se aplica DURANTE la partición.                │
  │                                                                   │
  │  "CA" solo existe en un sistema de un único nodo... que no es      │
  │  distribuido, y cuya disponibilidad es la de esa máquina.          │
  └───────────────────────────────────────────────────────────────────┘

  CP (prioriza consistencia): PostgreSQL con replicación síncrona, MongoDB con
      w=majority, etcd, ZooKeeper, HBase, Spanner.
      Ante una partición, la minoría RECHAZA escrituras. Prefiere un error a un
      dato incorrecto. Es lo que quieres para dinero e inventario.

  AP (prioriza disponibilidad): Cassandra y DynamoDB con consistencia eventual,
      Riak, CouchDB, DNS.
      Ante una partición, TODOS siguen aceptando escrituras y luego se reconcilian.
      Es lo que quieres para un carrito, un contador de "me gusta" o un catálogo.


PACELC (Abadi, 2012): la extensión que de verdad se usa a diario

  IF Partition:  ¿A o C?
  ELSE (normal): ¿Latencia (L) o Consistencia (C)?
                 ↑ ESTA es la decisión que tomas el 99,99% del tiempo,
                   porque las particiones son raras y la latencia es constante.

  PC/EC  PostgreSQL síncrono, Spanner, etcd  → consistente siempre, pagas latencia
  PC/EL  MongoDB (por defecto)               → consistente en partición, lee de
                                               secundarios rápidos si se lo pides
  PA/EL  Cassandra, DynamoDB (eventual)      → rápido y disponible; consistencia
                                               eventual como norma
  PA/EC  raro en la práctica

  TRADUCCIÓN PRÁCTICA: "leer de una réplica" es exactamente una decisión E/L.
  Estás cambiando consistencia por latencia, sin que haya ninguna partición.

Consistencia eventual y quórum

CONSISTENCIA EVENTUAL: si dejas de escribir, en algún momento todas las réplicas
convergen al mismo valor. No dice CUÁNDO ("eventualmente" puede ser 10 ms o 10 s),
y no da ninguna garantía sobre lo que lees mientras tanto.

QUÓRUM: la forma de comprar consistencia en un sistema AP, escritura a escritura.

        N = número de réplicas
        W = réplicas que deben confirmar una ESCRITURA
        R = réplicas que se consultan en una LECTURA

        Si  W + R > N  →  al menos una réplica leída tiene la última escritura,
                          y por tanto la lectura es consistente.

  N = 3 réplicas
  ┌──────────────────────────────────────────────────────────────────┐
  │ W=1, R=1 → W+R=2 ≤ 3   ❌ rapidísimo, consistencia eventual      │
  │ W=2, R=2 → W+R=4 > 3   ✅ equilibrado; tolera 1 nodo caído       │
  │ W=3, R=1 → W+R=4 > 3   ✅ lecturas rapidísimas, escrituras       │
  │                            frágiles (cae un nodo y no escribes)  │
  │ W=1, R=3 → W+R=4 > 3   ✅ escrituras rápidas, lecturas costosas  │
  └──────────────────────────────────────────────────────────────────┘

GARANTÍAS DE SESIÓN (más baratas que la consistencia fuerte y suelen bastar):
  · Read your writes      : veo lo que YO acabo de escribir.
  · Monotonic reads       : no veo el tiempo ir hacia atrás entre dos lecturas.
  · Consistent prefix     : si veo un efecto, veo su causa (no leo la respuesta
                            a un mensaje antes que el mensaje).

RESOLUCIÓN DE CONFLICTOS cuando dos réplicas escriben a la vez:
  · Last write wins (LWW): sencillo y PIERDE DATOS silenciosamente. Cassandra por
    defecto. Depende de relojes sincronizados, que nunca lo están del todo.
  · Vector clocks / version vectors: detecta el conflicto y te lo entrega para que
    tu aplicación decida (Riak, DynamoDB con versiones).
  · CRDT (tipos convergentes): estructuras que se fusionan sin conflicto por
    construcción (contadores, conjuntos). Redis Enterprise, Riak, Automerge.
  · Reconciliación de dominio: la mejor. "Suma los dos carritos" es una decisión
    de negocio, no técnica.

MongoDB: modelo documental

RELACIONAL                          DOCUMENTAL

pedido                              {
 ├─ id: 4711                          "_id": ObjectId("..."),
 ├─ cliente_id: 1                     "cliente": { "id": 1, "nombre": "Ana" },
 └─ total: 199.80                     "total": 199.80,
                                      "lineas": [
linea_pedido                            { "sku": "SKU-1", "uds": 2, "precio": 89.90 },
 ├─ (4711, 1) SKU-1 ×2                  { "sku": "SKU-9", "uds": 1, "precio": 20.00 }
 └─ (4711, 2) SKU-9 ×1                ],
                                      "creado": ISODate("2026-04-03T10:15:00Z")
3 tablas + 2 joins                  }

                                    1 documento, 1 lectura, 0 joins

// Operaciones básicas en mongosh
db.pedidos.insertOne({ cliente: { id: 1, nombre: "Ana" }, total: 199.80,
                       lineas: [ { sku: "SKU-1", uds: 2, precio: 89.90 } ],
                       estado: "NUEVO", creado: new Date() })

db.pedidos.find({ "cliente.id": 1, estado: "NUEVO" })
          .sort({ creado: -1 }).limit(20)

db.pedidos.updateOne({ _id: id },
                     { $set: { estado: "PAGADO" },
                       $push: { historial: { estado: "PAGADO", en: new Date() } },
                       $inc:  { version: 1 } })

// Índices: mismas reglas que en SQL (prefijo izquierdo, igualdad antes que rango)
db.pedidos.createIndex({ "cliente.id": 1, creado: -1 })
db.pedidos.createIndex({ estado: 1 }, { partialFilterExpression: { estado: "NUEVO" } })
db.pedidos.createIndex({ "lineas.sku": 1 })                  // índice multiclave
db.pedidos.createIndex({ creado: 1 }, { expireAfterSeconds: 7776000 })  // TTL: 90 días
db.pedidos.createIndex({ referencia: 1 }, { unique: true })

// El EXPLAIN de Mongo: busca IXSCAN (bien) frente a COLLSCAN (mal)
db.pedidos.find({ "cliente.id": 1 }).explain("executionStats")

// Aggregation pipeline: el GROUP BY de Mongo. Filtra ($match) LO ANTES POSIBLE
// para que el índice sirva y se muevan menos documentos entre etapas.
db.pedidos.aggregate([
  { $match:  { creado: { $gte: ISODate("2026-01-01") }, estado: { $ne: "CANCELADO" } } },
  { $unwind: "$lineas" },
  { $group:  { _id: "$lineas.sku",
               unidades:  { $sum: "$lineas.uds" },
               facturado: { $sum: { $multiply: ["$lineas.uds", "$lineas.precio"] } } } },
  { $sort:   { facturado: -1 } },
  { $limit:  10 },
  { $lookup: { from: "productos", localField: "_id",
               foreignField: "sku", as: "producto" } }   // el "join" de Mongo
])

// Transacciones multi-documento (4.0+ en réplica, 4.2+ en clúster fragmentado)
const s = db.getMongo().startSession();
s.withTransaction(() => {
  db.pedidos.updateOne({ _id: id }, { $set: { estado: "PAGADO" } });
  db.pagos.insertOne({ pedidoId: id, importe: 199.80 });
});
// ⚠️ Existen, pero son más caras que en un relacional y tienen límites (60 s por
//    defecto). Si tu modelo las necesita a menudo, el modelo documental es el
//    problema: probablemente querías un relacional.

// Nivel de consistencia por operación (la decisión PACELC en la práctica)
db.pedidos.insertOne(doc, { writeConcern: { w: "majority", j: true } })
db.pedidos.find(...).readConcern("majority").readPref("secondaryPreferred")

// Validación de esquema en MongoDB: úsala SIEMPRE en producción
db.createCollection("pedidos", {
  validator: { $jsonSchema: {
    bsonType: "object",
    required: ["cliente", "total", "estado", "creado", "_v"],
    properties: {
      _v:     { bsonType: "int", minimum: 1 },
      total:  { bsonType: "decimal", minimum: 0 },
      estado: { enum: ["NUEVO","PAGADO","ENVIADO","ENTREGADO","CANCELADO"] },
      cliente: { bsonType: "object", required: ["id"],
                 properties: { id: { bsonType: "long" } } },
      lineas: { bsonType: "array", minItems: 1, items: {
                  bsonType: "object", required: ["sku","uds","precio"] } }
    }
  }},
  validationLevel: "strict", validationAction: "error"
})
// ⚠️ Y usa "decimal" (Decimal128) para dinero, nunca "double": el problema de la
//    coma flotante es exactamente el mismo que en SQL.

// Spring Data MongoDB
@Document(collection = "pedidos")
@CompoundIndex(name = "cliente_creado_idx", def = "{'cliente.id': 1, 'creado': -1}")
public class PedidoDoc {
    @Id private String id;
    private ClienteEmbebido cliente;            // incrustado: se lee siempre junto
    private List<LineaEmbebida> lineas;         // incrustado: uno a pocos
    private BigDecimal total;                   // se mapea a Decimal128
    private String estado;
    @Indexed(expireAfter = "90d") private Instant creado;
    @Version private Long version;              // bloqueo optimista, igual que en JPA
}

public interface PedidoDocRepo extends MongoRepository<PedidoDoc, String> {
    List<PedidoDoc> findByClienteIdAndEstadoOrderByCreadoDesc(Long clienteId, String estado);

    @Query("{ 'total': { $gte: ?0 }, 'creado': { $gte: ?1 } }")
    List<PedidoDoc> grandesDesde(BigDecimal minimo, Instant desde);
}

// Agregación con la API tipada
var agg = newAggregation(
        match(Criteria.where("creado").gte(desde)),
        unwind("lineas"),
        group("lineas.sku").sum("lineas.uds").as("unidades"),
        sort(Sort.Direction.DESC, "unidades"),
        limit(10));
var top = mongoTemplate.aggregate(agg, "pedidos", VentaSku.class).getMappedResults();

Redis: la navaja suiza en memoria

# Caché de un objeto con expiración
SET producto:1001 '{"sku":"SKU-1","nombre":"Teclado","precio":89.90}' EX 300
GET producto:1001
TTL producto:1001

# RATE LIMITING por ventana fija: 100 peticiones por minuto y usuario
MULTI
INCR      ratelimit:usuario:77:2026-04-03T10:15
EXPIRE    ratelimit:usuario:77:2026-04-03T10:15 120
EXEC
# Si el INCR devuelve más de 100 → responde 429 Too Many Requests
# Ventana deslizante (más justa) con un zset de marcas de tiempo:
ZREMRANGEBYSCORE ratelimit:77 -inf (ahora-60000)
ZADD             ratelimit:77 1712131200000 uuid-peticion
ZCARD            ratelimit:77

# LEADERBOARD con zset: ordenado siempre, sin ordenar nada
ZADD  ranking:2026-04 1250 cliente:1  980 cliente:2  1400 cliente:3
ZREVRANGE ranking:2026-04 0 9 WITHSCORES     # top 10
ZREVRANK  ranking:2026-04 cliente:1          # mi puesto: O(log n)
ZINCRBY   ranking:2026-04 50 cliente:1

# VISITANTES ÚNICOS con HyperLogLog: 12 kB para millones de valores
PFADD  visitas:2026-04-03 usuario:1 usuario:2 usuario:3
PFCOUNT visitas:2026-04-03
PFMERGE visitas:2026-04 visitas:2026-04-01 visitas:2026-04-02 visitas:2026-04-03

# LOCK DISTRIBUIDO (con mucho cuidado, ver aviso más abajo)
SET lock:pedido:4711 <token-aleatorio> NX PX 30000
# Liberar SOLO si el token es mío: hace falta un script Lua para que sea atómico
EVAL "if redis.call('get',KEYS[1])==ARGV[1] then return redis.call('del',KEYS[1]) else return 0 end" 1 lock:pedido:4711 <token>

# Diagnóstico
INFO memory
INFO stats                 # keyspace_hits / keyspace_misses → hit ratio
SLOWLOG GET 10
--scan --pattern 'producto:*'   # con redis-cli; NUNCA uses KEYS * en producción

Patrones de caché e invalidación

CACHE-ASIDE (lazy loading) — el 90% de los casos, y el que usa @Cacheable
  leer:     ¿está en caché? → sí: devuelve
                            → no: lee de la BD, guarda en caché con TTL, devuelve
  escribir: escribe en la BD y BORRA la clave (no la actualices: evita carreras)
  ✅ Simple, resistente (si Redis cae, la aplicación sigue con la BD)
  ⚠️ La primera petición de cada clave siempre es lenta

WRITE-THROUGH
  escribir: escribe en caché Y en la BD, de forma síncrona
  ✅ La caché nunca está obsoleta       ⚠️ Toda escritura es más lenta

WRITE-BEHIND (write-back)
  escribir: escribe en caché y encola la escritura a la BD
  ✅ Escrituras rapidísimas             ⛔ Si Redis cae, PIERDES DATOS confirmados

REFRESH-AHEAD
  refresca la clave en segundo plano antes de que caduque
  ✅ Sin picos de latencia              ⚠️ Refresca cosas que quizá nadie pedirá

INVALIDACIÓN: el problema difícil de verdad
  1. TTL corto (30 s - 5 min): la solución del 80% de los casos. Sencilla y robusta:
     acepta estar desactualizado un rato y no hay nada que mantener.
  2. Borrado explícito al escribir: preciso, pero hay que acordarse en TODOS los
     caminos de escritura (incluidos el batch nocturno y el script de soporte).
  3. Versionado de clave: producto:1001:v7. Cambiar la versión invalida todo de golpe,
     sin borrar nada. Muy útil para invalidaciones masivas.
  4. Publicación de eventos: la BD publica el cambio (outbox o CDC) y los
     consumidores invalidan. Correcto y complejo; para cachés distribuidas grandes.

STAMPEDE (avalancha): una clave muy caliente caduca y 5.000 peticiones simultáneas
van todas a la base de datos, que se cae. Tres mitigaciones:
  a) TTL con jitter: EX 300 + random(0..60). Evita la caducidad sincronizada.
  b) Bloqueo de recálculo: el primero que falla coge un lock (SET NX PX) y recalcula;
     los demás esperan un poco y reintentan la caché, o sirven el valor viejo.
  c) Refresco anticipado probabilístico: si queda poco TTL, un porcentaje pequeño
     de peticiones refresca de forma asíncrona mientras el resto sirve lo cacheado.

// Spring Cache sobre Redis: la forma declarativa
@Configuration
@EnableCaching
class CacheConfig {
    @Bean
    RedisCacheManager cacheManager(RedisConnectionFactory cf) {
        var base = RedisCacheConfiguration.defaultCacheConfig()
            .entryTtl(Duration.ofMinutes(10))
            .disableCachingNullValues()
            .serializeValuesWith(SerializationPair.fromSerializer(
                    new GenericJackson2JsonRedisSerializer()));   // legible y estable
        return RedisCacheManager.builder(cf)
                .cacheDefaults(base)
                .withCacheConfiguration("productos", base.entryTtl(Duration.ofHours(1)))
                .withCacheConfiguration("stock",     base.entryTtl(Duration.ofSeconds(5)))
                .build();
    }
}

@Service
public class ServicioCatalogo {

    @Cacheable(cacheNames = "productos", key = "#sku", sync = true)   // sync evita stampede
    public ProductoDto porSku(String sku) { return repo.buscar(sku); }

    @CacheEvict(cacheNames = "productos", key = "#p.sku")
    @Transactional
    public void guardar(Producto p) { repo.save(p); }

    @CacheEvict(cacheNames = "productos", allEntries = true)
    public void invalidarTodo() { }
}

// ⚠️ CUATRO ERRORES HABITUALES CON @Cacheable
// 1. Llamada interna al método cacheado: no pasa por el proxy, no cachea. Igual que
//    con @Transactional.
// 2. Serializar entidades JPA con relaciones perezosas: LazyInitializationException
//    o un grafo entero en Redis. Cachea DTOs, siempre.
// 3. Cachear dentro de una transacción y desalojar al final: entre medias, otros
//    leen datos viejos. Usa @CacheEvict con beforeInvocation o publica el evento
//    tras el commit (TransactionSynchronization / @TransactionalEventListener).
// 4. No poner TTL. Un caché sin caducidad es una fuga de memoria con datos rancios.

// Uso directo cuando necesitas estructuras de Redis (no solo caché)
@Service
class Limitador {
    private final StringRedisTemplate redis;

    boolean permitido(long usuarioId, int limitePorMinuto) {
        String clave = "rl:" + usuarioId + ":" + Instant.now().getEpochSecond() / 60;
        Long n = redis.opsForValue().increment(clave);
        if (n != null && n == 1L) redis.expire(clave, Duration.ofSeconds(120));
        return n != null && n <= limitePorMinuto;
    }
}

Elasticsearch / OpenSearch: búsqueda y relevancia

ÍNDICE INVERTIDO: al revés de como lo imaginas

  DOCUMENTOS                          ÍNDICE INVERTIDO
  1: "teclado mecánico rojo"          teclado  → [1, 2]
  2: "teclado membrana negro"         mecánico → [1]
  3: "ratón inalámbrico rojo"         rojo     → [1, 3]
                                      membrana → [2]
                                      ratón    → [3]

  Buscar "teclado rojo" = intersecar [1,2] con [1,3] = documento 1.
  Es O(términos), no O(documentos): por eso escala a miles de millones.

CADENA DE ANÁLISIS (lo que ocurre al indexar y al buscar; ¡debe coincidir!)

  "Teclados Mecánicos ROJOS"
      │ char filter    → quita HTML, normaliza caracteres
      │ tokenizer      → ["Teclados", "Mecánicos", "ROJOS"]
      │ lowercase      → ["teclados", "mecánicos", "rojos"]
      │ asciifolding   → ["teclados", "mecanicos", "rojos"]
      │ stopwords (es) → quita "de", "la", "y"...
      │ stemmer (es)   → ["teclad", "mecanic", "roj"]   ← raíces
      ▼
  Así "teclado rojo" encuentra "Teclados Mecánicos ROJOS".
  ⚠️ Si el analizador de indexación y el de búsqueda no coinciden, no encuentras nada
     y no hay ningún error: es el bug más desconcertante de Elasticsearch.

RELEVANCIA BM25 (la puntuación por defecto), en tres ideas:
  · TF   más veces aparece el término en el documento → más puntos (con saturación)
  · IDF  el término aparece en pocos documentos → vale más (es más discriminante)
  · Longitud: un término en un título corto pesa más que en una descripción larga
  Se ajusta con boosts por campo: title^3, description^1

// Mapeo explícito. NUNCA confíes en el mapeo dinámico en producción.
PUT /productos
{
  "settings": {
    "number_of_shards": 1, "number_of_replicas": 1,
    "analysis": { "analyzer": { "es_custom": {
        "tokenizer": "standard",
        "filter": ["lowercase", "asciifolding", "spanish_stop", "spanish_stemmer"] }},
      "filter": {
        "spanish_stop":    { "type": "stop", "stopwords": "_spanish_" },
        "spanish_stemmer": { "type": "stemmer", "language": "light_spanish" } } }
  },
  "mappings": { "properties": {
    "sku":       { "type": "keyword" },
    "nombre":    { "type": "text", "analyzer": "es_custom",
                   "fields": { "raw": { "type": "keyword" } } },
    "categoria": { "type": "keyword" },
    "precio":    { "type": "scaled_float", "scaling_factor": 100 },
    "activo":    { "type": "boolean" },
    "creado":    { "type": "date" } } }
}

// Búsqueda con relevancia, filtros y facetas en una sola petición
GET /productos/_search
{
  "query": { "bool": {
    "must":   [ { "multi_match": { "query": "teclado mecanico",
                                   "fields": ["nombre^3", "descripcion"],
                                   "fuzziness": "AUTO" } } ],
    "filter": [ { "term":  { "activo": true } },
                { "range": { "precio": { "lte": 150 } } } ]
  }},
  "aggs": { "por_categoria": { "terms": { "field": "categoria", "size": 10 } },
            "rangos_precio": { "range": { "field": "precio",
                "ranges": [ {"to":50}, {"from":50,"to":100}, {"from":100} ] } } },
  "highlight": { "fields": { "nombre": {} } },
  "size": 20
}
// Detalle importante: lo que va en "must" PUNTÚA; lo que va en "filter" no puntúa
// y se cachea. Los filtros exactos siempre en "filter": es más rápido.

SINCRONIZAR POSTGRESQL → ELASTICSEARCH: cuatro estrategias

 1. DOBLE ESCRITURA (❌ evítalo)
    La aplicación escribe en las dos. Si la segunda falla, quedan divergentes para
    siempre y no hay forma de saberlo. No hay transacción que abarque las dos.

 2. OUTBOX TRANSACCIONAL (✅ la recomendada)
    En la MISMA transacción que el dato, inserta una fila en 'outbox'. Un proceso
    aparte la lee y proyecta al índice, marcando lo procesado. Si el proceso muere,
    reintenta; si la transacción falla, no hay evento. Atomicidad garantizada.
       BEGIN;
         UPDATE producto SET precio = 99.90 WHERE id = 1;
         INSERT INTO outbox (agregado, agregado_id, tipo, payload)
           VALUES ('producto', '1', 'PRECIO_CAMBIADO', jsonb_build_object('precio',99.90));
       COMMIT;

 3. CDC CON DEBEZIUM (✅ para volúmenes grandes)
    Lee el WAL de PostgreSQL con replicación lógica y publica en Kafka. No toca el
    código de la aplicación y captura TODO cambio, venga de donde venga (incluido
    el script manual de soporte). Coste: operar Kafka y Kafka Connect.

 4. RECONCILIACIÓN PERIÓDICA (✅ imprescindible como complemento)
    Un job nocturno que compara recuentos y sumas de control por rango y reindexa
    lo que no cuadre. Ninguna de las tres anteriores es perfecta: esta es la red.

 · La reindexación completa se hace con alias: indexa en productos_v2, y cuando
   termine, mueve el alias 'productos' de v1 a v2 de forma atómica. Cero downtime.

Cassandra y DynamoDB: modelar por patrón de acceso

Aquí el cambio mental es más profundo que en MongoDB. En un relacional modelas los datos y luego escribes las consultas que necesites. En Cassandra y DynamoDB modelas las consultas: se enumeran primero todos los patrones de acceso y luego se diseñan las tablas —una por patrón, duplicando datos sin ningún reparo— porque solo se puede consultar por la clave.

CASSANDRA: PRIMARY KEY = (PARTITION KEY, CLUSTERING KEYS)

  PRIMARY KEY ((cliente_id), creado_en, pedido_id)
                 ▲             ▲
                 │             └── clustering: ORDENA dentro de la partición
                 └── partition key: decide EN QUÉ NODO vive la fila

  · Toda consulta DEBE indicar la partition key completa. Sin ella, no hay consulta
    (o hay ALLOW FILTERING, que es un escaneo del clúster: nunca en producción).
  · Los rangos y el ORDER BY solo funcionan sobre las clustering keys.
  · Las particiones deben ser ACOTADAS: una partición de más de ~100 MB o
    ~100.000 filas es un problema operativo grave ("hot partition").
    Si una partición crecería sin límite, añade un bucket temporal a la clave:
    PRIMARY KEY ((cliente_id, anio_mes), creado_en)

DYNAMODB: los mismos conceptos con otros nombres
  · Partition key (PK / HASH)        = partition key de Cassandra
  · Sort key (SK / RANGE)            = clustering key
  · GSI (Global Secondary Index)     = otra tabla mantenida por el servicio,
                                       con otra PK/SK. Consistencia eventual.
  · LSI (Local Secondary Index)      = otra sort key con la misma PK. Debe crearse
                                       al crear la tabla y no se puede quitar.
  · Límites: 400 KB por item, 1 MB por consulta, 3.000 RCU / 1.000 WCU por partición.

SINGLE-TABLE DESIGN (el patrón idiomático de DynamoDB)
  Todas las entidades en UNA tabla, con claves compuestas genéricas:

   PK                  SK                        atributos
   ─────────────────── ───────────────────────── ─────────────────────────
   CLIENTE#1           PERFIL                    nombre, email
   CLIENTE#1           PEDIDO#2026-04-03#4711    total, estado
   CLIENTE#1           PEDIDO#2026-04-01#4702    total, estado
   PEDIDO#4711         LINEA#1                   sku, uds, precio
   PEDIDO#4711         LINEA#2                   sku, uds, precio

  · "Perfil y últimos pedidos de un cliente" = UNA consulta:
       PK = CLIENTE#1 AND SK BETWEEN 'PEDIDO#2026-01' AND 'PEDIDO#2026-12'
  · Ventaja: una sola petición, latencia de un dígito de ms, escala infinita.
  · Coste: el modelo es ilegible para un humano, no admite consultas nuevas sin
    rediseñar o añadir un GSI, y los informes hay que hacerlos fuera (exportando
    a S3 y consultando con Athena).

-- CQL (Cassandra): se parece a SQL y NO es SQL. Es la principal fuente de confusión.
CREATE TABLE pedidos_por_cliente (
    cliente_id  bigint,
    creado_en   timestamp,
    pedido_id   uuid,
    total       decimal,
    estado      text,
    PRIMARY KEY ((cliente_id), creado_en, pedido_id)
) WITH CLUSTERING ORDER BY (creado_en DESC);

-- ✅ Consulta natural: la partition key está presente
SELECT * FROM pedidos_por_cliente WHERE cliente_id = 42 LIMIT 20;
SELECT * FROM pedidos_por_cliente
 WHERE cliente_id = 42 AND creado_en > '2026-01-01';

-- ❌ Imposible sin la partition key: no hay índice global
SELECT * FROM pedidos_por_cliente WHERE estado = 'NUEVO';
-- Para esta consulta se crea OTRA TABLA con los mismos datos:
CREATE TABLE pedidos_por_estado (
    estado     text, creado_en timestamp, pedido_id uuid, cliente_id bigint,
    PRIMARY KEY ((estado), creado_en, pedido_id)
) WITH CLUSTERING ORDER BY (creado_en DESC);
-- La aplicación escribe en LAS DOS (con BATCH o de forma idempotente).
-- Duplicar datos no es un pecado aquí: es el diseño.

-- No hay JOIN, no hay GROUP BY útil, no hay transacciones entre particiones.
-- Sí hay escritura condicional (Paxos ligero), que es caro pero existe:
INSERT INTO cliente (id, email) VALUES (1, 'ana@x.com') IF NOT EXISTS;
-- Y consistencia ajustable por consulta:
CONSISTENCY QUORUM;

Bases de datos vectoriales: pgvector y compañía

Un modelo de embeddings convierte un texto (o una imagen) en un vector de cientos o miles de dimensiones que captura su significado. Textos parecidos producen vectores cercanos. Buscar por similitud semántica es entonces «encuentra los vectores más cercanos a este», y eso es lo que hace una base vectorial. Es la pieza de almacenamiento de cualquier sistema RAG (módulo 11).

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documento (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    producto_id bigint REFERENCES producto(id),
    texto      text NOT NULL,
    embedding  vector(1536),        -- dimensiones del modelo (p. ej. text-embedding-3-small)
    metadatos  jsonb NOT NULL DEFAULT '{}',
    creado_en  timestamptz NOT NULL DEFAULT now()
);

-- Operadores de distancia: elige el que corresponda a tu modelo
--   <->  distancia euclídea (L2)
--   <=>  distancia coseno       ← la más habitual con embeddings de texto
--   <#>  producto interno negativo

-- Índice ANN (vecinos aproximados). Sin él, cada consulta compara con TODAS las filas.
CREATE INDEX documento_embedding_hnsw ON documento
    USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);
-- Alternativa IVFFlat: se construye más rápido y ocupa menos, pero es menos preciso.
--   USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);
--   (regla orientativa: lists ≈ filas/1000, y hay que crearlo con datos ya cargados)

-- Búsqueda semántica pura
SELECT id, texto, 1 - (embedding <=> $1::vector) AS similitud
FROM   documento
ORDER  BY embedding <=> $1::vector
LIMIT  5;
-- ⚠️ El índice ANN solo se usa si el ORDER BY es exactamente por el operador de
--    distancia y hay LIMIT. Compruébalo con EXPLAIN.

-- Ajustar la precisión frente a la velocidad en tiempo de consulta
SET hnsw.ef_search = 100;      -- más alto = más recall, más lento

-- ✅ LA GRAN VENTAJA DE pgvector: filtrar por SQL y buscar por similitud a la vez.
-- Con una base vectorial dedicada, combinar "parecido a esto" con "de la categoría Y,
-- activo, con stock y precio menor de 100" es un problema; aquí es un WHERE.
SELECT d.id, p.nombre, p.precio, 1 - (d.embedding <=> $1::vector) AS similitud
FROM   documento d
JOIN   producto  p ON p.id = d.producto_id
WHERE  p.activo AND p.stock > 0 AND p.categoria_id = $2 AND p.precio < 100
ORDER  BY d.embedding <=> $1::vector
LIMIT  10;

-- ✅ BÚSQUEDA HÍBRIDA (léxica + semántica) con Reciprocal Rank Fusion.
-- Casi siempre da mejores resultados que cualquiera de las dos por separado:
-- la léxica acierta con nombres propios y códigos, la semántica con sinónimos.
WITH lexica AS (
    SELECT id, row_number() OVER (ORDER BY ts_rank(busqueda, q) DESC) AS pos
    FROM   documento, websearch_to_tsquery('spanish', $2) AS q
    WHERE  busqueda @@ q LIMIT 50
),
semantica AS (
    SELECT id, row_number() OVER (ORDER BY embedding <=> $1::vector) AS pos
    FROM   documento ORDER BY embedding <=> $1::vector LIMIT 50
)
SELECT COALESCE(l.id, s.id) AS id,
       COALESCE(1.0 / (60 + l.pos), 0) + COALESCE(1.0 / (60 + s.pos), 0) AS puntuacion
FROM   lexica l FULL OUTER JOIN semantica s ON s.id = l.id
ORDER  BY puntuacion DESC
LIMIT  10;

Tabla final de decisión: «necesito X → usa Y»

10 · Java y bases de datos

JDBC: lo que hay debajo de todo

Hibernate, Spring Data, jOOQ y R2DBC son capas sobre JDBC (o sobre su equivalente reactivo). Conocer el nivel de abajo es lo que te permite diagnosticar cuando la capa de arriba hace algo raro. Los conceptos base están en el módulo 01; aquí van los detalles que importan en producción.

// JDBC a pelo, con todo lo que hay que hacer bien
String sql = """
        SELECT p.id, p.total, p.creado_en, c.nombre
        FROM   pedido p JOIN cliente c ON c.id = p.cliente_id
        WHERE  p.cliente_id = ? AND p.creado_en >= ?
        ORDER  BY p.creado_en DESC
        LIMIT  ?
        """;

try (Connection con = dataSource.getConnection();
     PreparedStatement ps = con.prepareStatement(sql)) {

    ps.setLong(1, clienteId);
    ps.setObject(2, desde);          // OffsetDateTime → timestamptz, sin conversiones raras
    ps.setInt(3, 20);
    ps.setQueryTimeout(5);           // segundos: red de seguridad por consulta

    try (ResultSet rs = ps.executeQuery()) {
        List<PedidoResumen> salida = new ArrayList<>();
        while (rs.next()) {
            salida.add(new PedidoResumen(
                    rs.getLong("id"),
                    rs.getBigDecimal("total"),
                    rs.getObject("creado_en", OffsetDateTime.class),   // ✅ java.time
                    rs.getString("nombre")));
        }
        return salida;
    }
}
// Detalles que se suelen hacer mal:
//   · try-with-resources en Connection, Statement y ResultSet (en ese orden).
//   · setObject con tipos de java.time, nunca setTimestamp con java.sql.Timestamp.
//   · getBigDecimal para dinero, nunca getDouble.
//   · Nunca concatenar valores en el SQL (sección 8).

JdbcClient: la API moderna de Spring

// JdbcClient (Spring Framework 6.1 / Boot 3.2+) es hoy la mejor forma de escribir
// SQL directo en Spring: API fluida, sin el ruido de JdbcTemplate y con parámetros
// con nombre.
@Repository
public class PedidoConsultas {

    private final JdbcClient jdbc;

    PedidoConsultas(JdbcClient jdbc) { this.jdbc = jdbc; }

    // Proyección directa a un record: menos código y nada de entidades gestionadas
    public List<PedidoResumen> ultimosDe(long clienteId, int limite) {
        return jdbc.sql("""
                    SELECT p.id, p.total, p.creado_en, c.nombre AS cliente
                    FROM   pedido p JOIN cliente c ON c.id = p.cliente_id
                    WHERE  p.cliente_id = :cliente
                    ORDER  BY p.creado_en DESC
                    LIMIT  :limite
                    """)
                .param("cliente", clienteId)
                .param("limite", limite)
                .query(PedidoResumen.class)      // mapeo por nombre de columna
                .list();
    }

    public Optional<BigDecimal> gastoTotal(long clienteId) {
        return jdbc.sql("SELECT sum(total) FROM pedido WHERE cliente_id = ?")
                   .param(clienteId)
                   .query(BigDecimal.class)
                   .optional();
    }

    // Paginación keyset con cursor compuesto (sección 5, patrón 6)
    public List<PedidoResumen> pagina(OffsetDateTime cursorFecha, Long cursorId, int tam) {
        return jdbc.sql("""
                    SELECT p.id, p.total, p.creado_en, c.nombre AS cliente
                    FROM   pedido p JOIN cliente c ON c.id = p.cliente_id
                    WHERE  (:fecha IS NULL OR (p.creado_en, p.id) < (:fecha, :id))
                    ORDER  BY p.creado_en DESC, p.id DESC
                    LIMIT  :tam
                    """)
                .param("fecha", cursorFecha).param("id", cursorId).param("tam", tam)
                .query(PedidoResumen.class).list();
    }

    // INSERT con RETURNING: obtener la clave generada sin un SELECT extra
    public long crear(long clienteId) {
        return jdbc.sql("INSERT INTO pedido (cliente_id, estado) VALUES (?, 'NUEVO') RETURNING id")
                   .param(clienteId)
                   .query(Long.class).single();
    }

    // Lista como un solo parámetro (sección 5, patrón 8)
    public List<PedidoResumen> porIds(Collection<Long> ids) {
        return jdbc.sql("SELECT id, total, creado_en FROM pedido WHERE id = ANY(?)")
                   .param(ids.toArray(Long[]::new))
                   .query(PedidoResumen.class).list();
    }

    // RowMapper cuando el mapeo automático no basta (jsonb, arrays, tipos propios)
    public List<ProductoDto> conAtributos() {
        return jdbc.sql("SELECT id, sku, nombre, atributos, etiquetas FROM producto")
                .query((rs, n) -> new ProductoDto(
                        rs.getLong("id"), rs.getString("sku"), rs.getString("nombre"),
                        leerJson(rs.getString("atributos")),
                        List.of((String[]) rs.getArray("etiquetas").getArray())))
                .list();
    }
}

Tipos de Java y de PostgreSQL: la tabla que evita bugs de fechas

// La regla de oro de las fechas: la aplicación trabaja SIEMPRE en UTC y solo
// convierte a la zona del usuario al presentar. Fija la zona de la JVM:
//   -Duser.timezone=UTC     (y en el contenedor, TZ=UTC)

// Escribir un timestamptz correctamente
jdbc.sql("INSERT INTO pedido (cliente_id, creado_en) VALUES (?, ?)")
    .params(clienteId, OffsetDateTime.now(ZoneOffset.UTC))
    .update();

// Leer y presentar en la zona del usuario, sin tocar el dato almacenado
OffsetDateTime instante = rs.getObject("creado_en", OffsetDateTime.class);
String paraElUsuario = instante.atZoneSameInstant(ZoneId.of("Europe/Madrid"))
        .format(DateTimeFormatter.ofPattern("dd/MM/yyyy HH:mm"));

// Escribir jsonb: hay que indicar el tipo o Postgres se queja
//   ERROR: column "atributos" is of type jsonb but expression is of type character varying
var pgObject = new org.postgresql.util.PGobject();
pgObject.setType("jsonb");
pgObject.setValue(objectMapper.writeValueAsString(atributos));
jdbc.sql("UPDATE producto SET atributos = ? WHERE id = ?").params(pgObject, id).update();
// Alternativa limpia sin depender del driver: castea en el SQL
jdbc.sql("UPDATE producto SET atributos = ?::jsonb WHERE id = ?")
    .params(json, id).update();

// Arrays
try (var con = dataSource.getConnection()) {
    Array etiquetas = con.createArrayOf("text", new String[]{"oferta", "novedad"});
    jdbc.sql("UPDATE producto SET etiquetas = ? WHERE id = ?").params(etiquetas, id).update();
}

// En JPA/Hibernate, lo equivalente
@Column(name = "creado_en", columnDefinition = "timestamptz")
private OffsetDateTime creadoEn;

@Column(name = "total", precision = 12, scale = 2)
private BigDecimal total;

@JdbcTypeCode(SqlTypes.JSON)          // Hibernate 6: mapeo nativo de jsonb
private Map<String, Object> atributos;

@JdbcTypeCode(SqlTypes.ARRAY)
private String[] etiquetas;

Leer millones de filas sin agotar la memoria

// ❌ ANTES: el driver trae TODAS las filas a memoria antes de devolver el primer
//    resultado. Con 20 millones de filas, OutOfMemoryError garantizado.
List<Pedido> todos = jdbc.sql("SELECT * FROM pedido").query(Pedido.class).list();

// ✅ DESPUÉS: cursor del servidor. En PostgreSQL hacen falta TRES condiciones
//    simultáneas o el fetchSize se ignora silenciosamente:
//      1. autoCommit = false (si no, el cursor se cierra al instante)
//      2. fetchSize > 0
//      3. ResultSet de tipo FORWARD_ONLY (el valor por defecto)
try (Connection con = dataSource.getConnection()) {
    con.setAutoCommit(false);                             // (1)
    try (PreparedStatement ps = con.prepareStatement(
                 "SELECT id, cliente_id, total FROM pedido ORDER BY id",
                 ResultSet.TYPE_FORWARD_ONLY,             // (3)
                 ResultSet.CONCUR_READ_ONLY)) {
        ps.setFetchSize(5_000);                           // (2)
        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                procesar(rs.getLong(1), rs.getLong(2), rs.getBigDecimal(3));
            }
        }
    }
    con.commit();
}

// ✅ Alternativa idiomática con Spring: stream() cierra los recursos con el try
@Transactional(readOnly = true)
public void exportar(Writer salida) {
    try (Stream<PedidoResumen> flujo = jdbc.sql("SELECT id, total, creado_en FROM pedido")
                                           .query(PedidoResumen.class).stream()) {
        flujo.forEach(p -> escribirCsv(salida, p));
    }
}
// ⚠️ El stream DEBE consumirse dentro de la transacción y cerrarse siempre.
// ⚠️ Con Hibernate, además: entityManager.clear() cada N filas, o la caché de
//    primer nivel acumula todas las entidades y vuelves al OutOfMemoryError.

// ✅ Y la alternativa que casi siempre es mejor para exportar de verdad:
//    que lo haga la base de datos con COPY y no pase por la JVM.
var copyOut = con.unwrap(org.postgresql.PGConnection.class).getCopyAPI();
copyOut.copyOut("COPY (SELECT id, total, creado_en FROM pedido) TO STDOUT WITH CSV HEADER",
                salida);

Elegir entre ORM y SQL directo

11 · Errores comunes y cómo solucionarlos

12 · Preguntas frecuentes

13 · Ejercicios y retos prácticos

Los ejercicios se hacen en orden y sobre el esquema de la sección 2. Todo lo que sigue se puede completar en un portátil con Docker; no hace falta ninguna infraestructura.

Ejercicios guiados (8)

# EJERCICIO 1 · Entorno completo en 30 segundos
docker run -d --name pg16 \
  -e POSTGRES_PASSWORD=secreto -e POSTGRES_DB=tienda \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  postgres:16 \
  -c shared_buffers=512MB \
  -c work_mem=16MB \
  -c random_page_cost=1.1 \
  -c log_min_duration_statement=200 \
  -c shared_preload_libraries=pg_stat_statements

docker exec -it pg16 psql -U postgres -d tienda

# Redis para el ejercicio 7
docker run -d --name redis7 -p 6379:6379 redis:7 \
  redis-server --maxmemory 256mb --maxmemory-policy allkeys-lru

# MongoDB para el ejercicio 8
docker run -d --name mongo7 -p 27017:27017 mongo:7

# Comandos de psql que debes tener en los dedos
#   \dt+           tablas con tamaño        \d+ pedido    describir tabla
#   \di+           índices con tamaño       \l+           bases de datos
#   \timing on     mostrar tiempos          \x auto       salida vertical
#   \e             editar la consulta       \watch 1      repetir cada segundo
#   \copy          importar/exportar CSV desde el cliente

-- =============================================================================
--  EJERCICIO 1 · GENERADOR DE DATOS (ejecútalo tras crear el esquema)
--  Resultado: 5.000 clientes, 2.000 productos, 100.000 pedidos, ~300.000 líneas
-- =============================================================================
SET search_path TO tienda, public;

-- 5.000 clientes repartidos entre los países sembrados
INSERT INTO cliente (email, nombre, pais, activo, creado_en)
SELECT 'cliente' || g || '@example.invalid',
       'Cliente ' || g,
       (ARRAY['ES','PT','FR','DE','IT'])[1 + (g % 5)],
       (g % 20) <> 0,                                   -- un 5% inactivos
       now() - (random() * interval '900 days')
FROM   generate_series(1, 5000) AS g;

-- 2.000 productos con precios y atributos variados
INSERT INTO producto (sku, nombre, categoria_id, precio, stock, activo, atributos, etiquetas)
SELECT 'SKU-' || lpad(g::text, 6, '0'),
       (ARRAY['Teclado','Ratón','Monitor','Alfombrilla','Webcam'])[1 + (g % 5)]
           || ' modelo ' || g,
       1 + (g % 5),
       round((random() * 480 + 12)::numeric, 2),
       (random() * 300)::int,
       (g % 25) <> 0,
       jsonb_build_object('color', (ARRAY['rojo','negro','blanco','azul'])[1 + (g % 4)],
                          'garantia_meses', 12 + (g % 4) * 12),
       CASE WHEN g % 10 = 0 THEN ARRAY['oferta'] ELSE '{}'::text[] END
FROM   generate_series(1, 2000) AS g;

-- 100.000 pedidos distribuidos en 3 años, con sesgo hacia los clientes recientes
INSERT INTO pedido (cliente_id, estado, moneda, creado_en)
SELECT 1 + (random() * 4999)::int,
       (ARRAY['NUEVO','PAGADO','ENVIADO','ENTREGADO','ENTREGADO','ENTREGADO'])
           [1 + (random() * 5)::int],
       'EUR',
       now() - (random() * interval '1095 days')
FROM   generate_series(1, 100000);

-- De 1 a 5 líneas por pedido
INSERT INTO linea_pedido (pedido_id, linea_num, producto_id, cantidad, precio_unitario, descuento)
SELECT p.id,
       l.n,
       1 + (random() * 1999)::int,
       1 + (random() * 4)::int,
       round((random() * 480 + 12)::numeric, 2),
       CASE WHEN random() < 0.15 THEN round((random() * 0.3)::numeric, 4) ELSE 0 END
FROM   pedido p
CROSS  JOIN LATERAL generate_series(1, 1 + (random() * 4)::int) AS l(n)
ON CONFLICT DO NOTHING;

-- Cuadrar los totales con las líneas
UPDATE pedido p
   SET total = COALESCE(s.suma, 0)
  FROM (SELECT pedido_id, sum(importe) AS suma FROM linea_pedido GROUP BY pedido_id) s
 WHERE s.pedido_id = p.id;

-- Un pago por cada pedido no nuevo
INSERT INTO pago (pedido_id, metodo, importe, estado, referencia)
SELECT id,
       (ARRAY['TARJETA','SEPA','PAYPAL','TRANSFER'])[1 + (id % 4)],
       total,
       'OK',
       'ref-' || id
FROM   pedido
WHERE  estado <> 'NUEVO' AND total > 0;

-- ⚠️ IMPRESCINDIBLE tras cualquier carga masiva
VACUUM (ANALYZE) cliente, producto, pedido, linea_pedido, pago;

-- Comprobación
SELECT 'cliente' AS tabla, count(*) FROM cliente
UNION ALL SELECT 'producto', count(*) FROM producto
UNION ALL SELECT 'pedido', count(*) FROM pedido
UNION ALL SELECT 'linea_pedido', count(*) FROM linea_pedido
UNION ALL SELECT 'pago', count(*) FROM pago;

SELECT relname,
       pg_size_pretty(pg_relation_size(relid))  AS tabla,
       pg_size_pretty(pg_indexes_size(relid))   AS indices,
       pg_size_pretty(pg_total_relation_size(relid)) AS total
FROM   pg_catalog.pg_statio_user_tables
ORDER  BY pg_total_relation_size(relid) DESC;

-- =============================================================================
--  EJERCICIO 2 · LAS TRES CONSULTAS QUE DEBES ARREGLAR
--  Guarda el EXPLAIN (ANALYZE, BUFFERS) de cada una ANTES de tocar nada.
-- =============================================================================

-- 2.a) ¿Por qué es lenta? ¿Qué índice falta y con qué orden de columnas?
SELECT p.id, p.total, p.creado_en
FROM   pedido p
WHERE  p.cliente_id = 1234 AND p.estado = 'ENTREGADO'
ORDER  BY p.creado_en DESC
LIMIT  20;

-- 2.b) Aquí el índice de creado_en existe y NO se usa. ¿Por qué? Reescríbela.
SELECT count(*), sum(total)
FROM   pedido
WHERE  date_part('year', creado_en) = 2026
  AND  date_part('month', creado_en) = 4;

-- 2.c) Esta tarda cada vez más según avanza la página. Conviértela en keyset.
SELECT p.id, p.total, c.nombre
FROM   pedido p JOIN cliente c ON c.id = p.cliente_id
ORDER  BY p.creado_en DESC
LIMIT  20 OFFSET 80000;

-- Al terminar, comprueba tu trabajo con las consultas de diagnóstico:
SELECT round(total_exec_time::numeric/1000,1) AS total_s, calls,
       round(mean_exec_time::numeric,2) AS media_ms, left(query, 70)
FROM   pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Retos (5)

Checklist de repaso (sin mirar apuntes)

14 · Resumen y recursos