Manual del DBA

Rangos, tamaño en bytes y casos de uso de los tipos numéricos, de texto, fecha/hora, JSON y espaciales. Incluye diferencias relevantes entre MySQL y MariaDB.

Aplica a: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

La elección del tipo de dato impacta el tamaño en disco, la velocidad de los índices y la integridad de los datos. Esta guía resume los tipos más usados en MySQL y MariaDB, con sus rangos, su tamaño en bytes y los casos típicos de uso.

Numéricos enteros

- TINYINT — 1 byte, rango con signo −128…127 (sin signo 0…255). Útil para banderas booleanas o estados pequeños.
- SMALLINT — 2 bytes, −32 768…32 767. Edad, cantidades pequeñas.
- MEDIUMINT — 3 bytes, −8 388 608…8 388 607. Único en MySQL/MariaDB; raramente usado fuera del ecosistema.
- INT (INTEGER) — 4 bytes, ±2,1·10⁹. Tipo por defecto para claves primarias en tablas medianas.
- BIGINT — 8 bytes, ±9,2·10¹⁸. Claves primarias en tablas grandes, identificadores distribuidos.

CREATE TABLE pedidos (
    id        BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cantidad  SMALLINT UNSIGNED NOT NULL,
    estado    TINYINT UNSIGNED NOT NULL DEFAULT 0
);

Numéricos decimales y flotantes

- DECIMAL(M, D) — precisión exacta, M dígitos totales y D decimales. Obligatorio para dinero.
- FLOAT — 4 bytes, aprox. 7 dígitos significativos. Aproximado.
- DOUBLE — 8 bytes, aprox. 15 dígitos. Aproximado.

CREATE TABLE precios (
    sku    VARCHAR(32) PRIMARY KEY,
    monto  DECIMAL(12, 2) NOT NULL,
    iva    DECIMAL(4, 2)  NOT NULL DEFAULT 13.00
);

Texto y cadenas

- CHAR(N) — longitud fija de N caracteres, hasta 255. Rápido cuando todas las filas tienen el mismo tamaño (códigos de país, hashes).
- VARCHAR(N) — longitud variable, hasta 65 535 bytes por fila (compartido con el resto de columnas). Usa 1 ó 2 bytes adicionales para la longitud.
- TEXT, MEDIUMTEXT, LONGTEXT — 64 KiB, 16 MiB, 4 GiB. Se almacenan fuera de la fila; no se pueden usar como clave sin un prefijo (KEY (col(255))).
- BLOB, MEDIUMBLOB, LONGBLOB — equivalentes binarios.

Fecha y hora

- DATE — 3 bytes, '1000-01-01'…'9999-12-31'.
- TIME — 3 bytes, '-838:59:59'…'838:59:59'. Sí, puede ser mayor que 24 horas (intervalo, no hora del día).
- DATETIME — 8 bytes, sin zona horaria, sin conversión al guardar/leer. Persiste la cadena literal.
- TIMESTAMP — 4 bytes, rango 1970…2038 (en MySQL 5.7) o 1970…2106 (en MariaDB 10.4+). Se almacena en UTC y se convierte al time_zone de la conexión.
- YEAR — 1 byte, 1901…2155.

CREATE TABLE eventos (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ocurrido_en  DATETIME(6) NOT NULL,
    creado_en    TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP
);

JSON

Tipo nativo en MySQL 5.7+ y MariaDB 10.2+. Permite indexar campos extraídos con JSON_EXTRACT o ->> y, desde MySQL 8.0, columnas generadas con índice (MULTI-VALUED INDEX en arrays).

CREATE TABLE perfiles (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    atributos   JSON NOT NULL,
    correo      VARCHAR(120) AS (atributos->>'$.email') STORED,
    INDEX idx_correo (correo)
);

Espaciales (GIS)

- POINT, LINESTRING, POLYGON, GEOMETRY, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION.
- Requieren índice SPATIAL para consultas eficientes (MBRContains, ST_Distance, ST_Within).
- En MySQL 8.0, el SRID es obligatorio para usar índices espaciales.

CREATE TABLE ubicaciones (
    id    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(120),
    punto  POINT NOT NULL SRID 4326,
    SPATIAL INDEX (punto)
) ENGINE = InnoDB;

ENUM y SET

- ENUM — almacena uno entre N valores predefinidos (hasta 65 535). Compacto (1–2 bytes), pero rígido: cambiar la lista exige ALTER TABLE.
- SET — combinación de hasta 64 valores como bitmap. Útil para permisos o etiquetas fijas.

Evítalos si la lista de valores cambia con frecuencia; una tabla de catálogo + clave foránea es más mantenible.

Cómo elegir

1. Usa el tipo más pequeño que cubra el rango esperado. Un BIGINT donde basta INT cuadriplica el espacio del índice.
2. Marca columnas como UNSIGNED cuando no necesitas negativos: duplicas el rango.
3. Evita NULL cuando puedas: una columna NOT NULL DEFAULT … ahorra 1 bit por fila e índices.
4. VARCHAR(255) no es más caro que VARCHAR(20) si los datos reales caben en 20 — solo la longitud declarada importa para índices con prefijo.

Palabras clave: tipos de datos, INT, BIGINT, VARCHAR, TEXT, JSON, DATETIME, TIMESTAMP, DECIMAL, ENUM, SET, POINT, SPATIAL, BLOB, rango, bytes

Tipos de índices (B-tree, hash, fulltext, espacial), índices simples vs compuestos y estrategias para optimizar lectura sin penalizar la escritura.

Aplica a: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Un índice acelera la búsqueda al precio de espacio en disco y de coste en cada INSERT/UPDATE/DELETE. Un buen diseño de índices es la diferencia entre una consulta de milisegundos y una de minutos.

Tipos de índices

- B-tree — Predeterminado en InnoDB. Soporta búsquedas por igualdad, rango (>, <, BETWEEN), prefijo (LIKE 'abc%') y ordenación (ORDER BY).
- Hash — Solo soporta igualdad. Disponible en el engine MEMORY. InnoDB mantiene un adaptive hash index interno que no controlas directamente.
- FULLTEXT — Búsqueda de texto natural y booleana. Disponible en InnoDB y MyISAM. Útil para campos TEXT largos.
- SPATIAL — R-tree para tipos POINT, POLYGON, etc. Requiere columna NOT NULL.
- Multi-valued — Indexa elementos de un array JSON. Solo en MySQL 8.0.17+.

-- Índice B-tree compuesto
CREATE INDEX idx_pedidos_cliente_fecha
    ON pedidos (cliente_id, fecha_pedido DESC);

-- Índice de texto completo
ALTER TABLE articulos
    ADD FULLTEXT INDEX ft_titulo_cuerpo (titulo, cuerpo);

-- Índice espacial
ALTER TABLE ubicaciones
    ADD SPATIAL INDEX sp_punto (punto);

Simples vs compuestos

Un índice compuesto sobre (A, B, C) cubre las búsquedas con prefijo: WHERE A = ?, WHERE A = ? AND B = ?, WHERE A = ? AND B = ? AND C = ?, pero no WHERE B = ? por sí solo.

Regla práctica: ordena las columnas del compuesto por selectividad (cuántos valores únicos tiene cada una) y por la frecuencia de los filtros.

-- Bueno: índice sobre la columna más selectiva primero
CREATE INDEX idx_facturas
    ON facturas (cliente_id, estado, fecha)
    -- cliente_id (alta selectividad) → estado → fecha
;

-- Mal patrón: índice redundante
-- (cliente_id) ya está cubierto por (cliente_id, estado, fecha)
DROP INDEX idx_facturas_cliente ON facturas;

Covering indexes

Un índice que contiene todas las columnas leídas por una consulta evita acceder a la tabla. Usa EXPLAIN y busca Using index en la columna Extra.

-- La consulta solo lee cliente_id y total → el índice la cubre
CREATE INDEX idx_pedidos_cobertura
    ON pedidos (cliente_id, total);

SELECT cliente_id, SUM(total)
FROM pedidos
WHERE cliente_id IN (1, 2, 3)
GROUP BY cliente_id;

Índices con prefijo

Para columnas TEXT o VARCHAR largas, indexa solo los primeros N caracteres. Reduce el tamaño del índice manteniendo selectividad razonable.

CREATE INDEX idx_url
    ON paginas (url(64));   -- primeros 64 caracteres

Índices invisibles

ALTER TABLE pedidos ALTER INDEX idx_legacy INVISIBLE;
-- monitorear performance durante unas horas
ALTER TABLE pedidos ALTER INDEX idx_legacy VISIBLE; -- revertir
-- o
DROP INDEX idx_legacy ON pedidos; -- confirmar borrado

Impacto lectura vs escritura

Cada índice extra:
- Acelera consultas que lo usan.
- Penaliza cada INSERT, UPDATE que toca columnas indexadas, y cada DELETE.
- Ocupa espacio adicional (a menudo entre 10 % y 40 % del tamaño de la tabla).

En tablas con escritura masiva (logs, métricas), mantén el mínimo de índices imprescindibles.

Estrategias de optimización

1. Empieza por EXPLAIN — identifica type: ALL (full scan) y key: NULL (sin índice usado).
2. Mide antes de optimizar — usa el slow query log para encontrar las consultas más caras.
3. Combina selectividad con orden — el índice compuesto debe seguir el orden de las cláusulas WHERE y ORDER BY.
4. Evita índices redundantes — (A), (A, B), (A, B, C) son redundantes entre sí; basta con (A, B, C).
5. No indexes columnas con baja cardinalidad — un índice sobre genero o activo (1 ó 2 valores únicos) casi nunca ayuda.

-- Diagnóstico
EXPLAIN SELECT * FROM pedidos
WHERE cliente_id = 42 AND fecha >= '2024-01-01';

-- Estadísticas de uso de índices
SELECT object_schema, object_name, index_name, count_star
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = DATABASE()
ORDER BY count_star DESC;

Palabras clave: índices, index, B-tree, hash, fulltext, spatial, covering, compuesto, selectividad, EXPLAIN, prefix, invisible, cardinalidad

Primera, segunda y tercera forma normal, cuándo desnormalizar y cómo balancear integridad de datos contra rendimiento.

Aplica a: MySQL 5.7+ MariaDB 10.5+ Aurora 2+ PostgreSQL 13+ SQLite 3.35+ SQL Server 2022+

La normalización es el proceso de organizar un esquema para reducir redundancia y prevenir anomalías de inserción, actualización y borrado. Las formas normales son acumulativas: una tabla en 3FN cumple también 2FN y 1FN.

Primera Forma Normal (1FN)

- Cada columna contiene un único valor atómico (no listas ni JSON anidado representando varias entidades).
- Cada fila es identificable por una clave primaria.
- Sin grupos repetitivos en columnas (tel1, tel2, tel3).

-- Mala: tres columnas que repiten la misma "entidad"
CREATE TABLE clientes_v1 (
    id   BIGINT PRIMARY KEY,
    nombre  VARCHAR(120),
    tel1 VARCHAR(20),
    tel2 VARCHAR(20),
    tel3 VARCHAR(20)
);

-- Buena: una tabla relacionada
CREATE TABLE clientes (
    id BIGINT PRIMARY KEY,
    nombre VARCHAR(120)
);
CREATE TABLE clientes_telefonos (
    cliente_id  BIGINT NOT NULL,
    telefono    VARCHAR(20) NOT NULL,
    tipo        VARCHAR(10) NOT NULL,
    PRIMARY KEY (cliente_id, telefono),
    FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE
);

-- SQLite: se enciende en cada conexión, antes de la primera transacción
PRAGMA foreign_keys = ON;

CREATE TABLE clientes_telefonos (
    cliente_id  INTEGER NOT NULL,
    telefono    TEXT NOT NULL,
    tipo        TEXT NOT NULL CHECK (tipo IN ('movil', 'casa', 'oficina')),
    PRIMARY KEY (cliente_id, telefono),
    FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE
);

-- SQL Server: clave sustituta y conjunto cerrado con nombre
CREATE TABLE clientes_telefonos (
    id          BIGINT IDENTITY(1, 1) PRIMARY KEY,
    cliente_id  BIGINT NOT NULL,
    telefono    VARCHAR(20) NOT NULL,
    tipo        VARCHAR(10) NOT NULL
        CONSTRAINT ck_telefono_tipo CHECK (tipo IN ('movil', 'casa', 'oficina')),
    UNIQUE (cliente_id, telefono),
    FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE
);

-- Cambiar la lista después
ALTER TABLE clientes_telefonos DROP CONSTRAINT ck_telefono_tipo;
ALTER TABLE clientes_telefonos ADD CONSTRAINT ck_telefono_tipo
    CHECK (tipo IN ('movil', 'casa', 'oficina', 'fax'));

Segunda Forma Normal (2FN)

- Cumple 1FN.
- Toda columna no clave depende de la clave primaria completa, no de una parte. Aplica a claves compuestas.

Ejemplo: una tabla detalle_pedido (pedido_id, producto_id, cantidad, nombre_producto) viola 2FN, porque nombre_producto depende solo de producto_id, no del par completo.

-- Mal: nombre_producto se repite en cada línea del mismo producto
CREATE TABLE detalle_pedido_v1 (
    pedido_id        BIGINT,
    producto_id      BIGINT,
    cantidad         INT,
    nombre_producto  VARCHAR(120),
    PRIMARY KEY (pedido_id, producto_id)
);

-- Bien: nombre_producto vive en la tabla productos
CREATE TABLE detalle_pedido (
    pedido_id    BIGINT,
    producto_id  BIGINT,
    cantidad     INT NOT NULL,
    PRIMARY KEY (pedido_id, producto_id),
    FOREIGN KEY (producto_id) REFERENCES productos(id)
);

Tercera Forma Normal (3FN)

- Cumple 2FN.
- Ninguna columna no clave depende de otra columna no clave (sin dependencias transitivas).

Ejemplo clásico: empleados (id, nombre, departamento_id, departamento_nombre). El nombre del departamento depende de departamento_id, no directamente de la clave del empleado.

-- Mal: dependencia transitiva
CREATE TABLE empleados_v1 (
    id                   BIGINT PRIMARY KEY,
    nombre               VARCHAR(120),
    departamento_id      BIGINT,
    departamento_nombre  VARCHAR(80)
);

-- Bien
CREATE TABLE departamentos (
    id      BIGINT PRIMARY KEY,
    nombre  VARCHAR(80)  NOT NULL
);
CREATE TABLE empleados (
    id              BIGINT PRIMARY KEY,
    nombre          VARCHAR(120),
    departamento_id BIGINT NOT NULL,
    FOREIGN KEY (departamento_id) REFERENCES departamentos(id)
);

BCNF y formas superiores

La forma normal de Boyce-Codd (BCNF) endurece 3FN y la 4FN/5FN tratan dependencias multivaluadas y de unión. Para la mayoría de esquemas transaccionales, llegar limpio a 3FN es suficiente.

Cuándo desnormalizar

La desnormalización deliberada rompe las reglas para ganar performance. Es válida si:

1. Lectura masiva, escritura escasa — un campo cacheado en la tabla (pedidos.total_pagado) evita un SUM(...) recurrente.
2. Reporting / analítica — esquemas tipo star o snowflake denormalizan a propósito.
3. Resultados pre-calculados — vistas materializadas o tablas resumen.

Trade-offs que aceptas:

- Anomalías de actualización — si el dato denormalizado cambia, hay que actualizarlo en N filas.
- Inconsistencia transitoria — el campo cacheado puede desfasarse si la actualización falla a mitad.
- Triggers o lógica de aplicación — necesitas mantener el dato sincronizado.

-- Ejemplo: cachear el total del pedido para evitar SUM en cada lectura
ALTER TABLE pedidos
    ADD COLUMN total DECIMAL(12, 2) NOT NULL DEFAULT 0;

-- Mantenerlo con un trigger
DELIMITER //
CREATE TRIGGER detalle_pedido_after_insert
AFTER INSERT ON detalle_pedido
FOR EACH ROW
BEGIN
    UPDATE pedidos
       SET total = (SELECT COALESCE(SUM(cantidad * precio_unit), 0)
                    FROM detalle_pedido
                    WHERE pedido_id = NEW.pedido_id)
     WHERE id = NEW.pedido_id;
END//
DELIMITER ;

CREATE FUNCTION recalcular_total() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE pedidos
       SET total = (SELECT COALESCE(SUM(cantidad * precio_unit), 0)
                    FROM detalle_pedido
                    WHERE pedido_id = NEW.pedido_id)
     WHERE id = NEW.pedido_id;
    RETURN NULL;
END;
$$;

CREATE TRIGGER detalle_pedido_after_insert
AFTER INSERT ON detalle_pedido
FOR EACH ROW EXECUTE FUNCTION recalcular_total();

CREATE TRIGGER detalle_pedido_after_insert
AFTER INSERT ON detalle_pedido
FOR EACH ROW
BEGIN
    UPDATE pedidos
       SET total = (SELECT COALESCE(SUM(cantidad * precio_unit), 0)
                    FROM detalle_pedido
                    WHERE pedido_id = NEW.pedido_id)
     WHERE id = NEW.pedido_id;
END;

ALTER TABLE pedidos
    ADD total DECIMAL(12, 2) NOT NULL DEFAULT 0;

CREATE TRIGGER detalle_pedido_after_insert
ON detalle_pedido
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE p
       SET total = (SELECT COALESCE(SUM(d.cantidad * d.precio_unit), 0)
                    FROM detalle_pedido d
                    WHERE d.pedido_id = p.id)
      FROM pedidos p
     WHERE p.id IN (SELECT pedido_id FROM inserted);
END;

CREATE VIEW dbo.pedidos_total WITH SCHEMABINDING AS
SELECT pedido_id, SUM(cantidad * precio_unit) AS total, COUNT_BIG(*) AS lineas
FROM dbo.detalle_pedido
GROUP BY pedido_id;

CREATE UNIQUE CLUSTERED INDEX ux_pedidos_total ON dbo.pedidos_total (pedido_id);

Recomendación

1. Diseña en 3FN por defecto. La integridad lo agradece.
2. Desnormaliza solo con datos — mide la consulta lenta, prueba un caché y compara.
3. Documenta la desnormalización. Sin un comentario en el DDL, el siguiente DBA lo "normalizará" pensando que es un error.

Palabras clave: normalización, 1FN, 2FN, 3FN, BCNF, desnormalización, redundancia, dependencia funcional, claves, integridad, trigger

INNER, LEFT, RIGHT, CROSS, SELF y FULL OUTER JOIN: cuándo usar cada uno, con ejemplos sobre un esquema típico de pedidos.

Aplica a: MySQL 5.7+ MariaDB 10.5+ Aurora 2+ PostgreSQL 13+ SQLite 3.35+ SQL Server 2022+

Un JOIN combina filas de dos o más tablas según una condición. El tipo de join determina qué pasa con las filas que no encuentran pareja.

Los ejemplos usan estas tablas:

CREATE TABLE clientes (
    id      BIGINT PRIMARY KEY,
    nombre  VARCHAR(120) NOT NULL
);
CREATE TABLE pedidos (
    id          BIGINT PRIMARY KEY,
    cliente_id  BIGINT NOT NULL,
    total       DECIMAL(12, 2) NOT NULL,
    FOREIGN KEY (cliente_id) REFERENCES clientes(id)
);

INNER JOIN

Devuelve solo las filas con pareja en ambas tablas. Es el join por defecto y el más usado.

SELECT c.nombre, p.total
FROM clientes c
INNER JOIN pedidos p ON p.cliente_id = c.id;

LEFT JOIN (LEFT OUTER JOIN)

Todas las filas de la tabla izquierda + las parejas de la derecha. Los campos sin pareja a la derecha aparecen como NULL. Útil para "todos los X, con su Y si existe".

-- Todos los clientes, hayan hecho o no pedidos
SELECT c.nombre, COUNT(p.id) AS pedidos
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.id
GROUP BY c.id, c.nombre;

Clientes sin pedidos — patrón clásico con WHERE … IS NULL:

SELECT c.id, c.nombre
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.id
WHERE p.id IS NULL;

RIGHT JOIN

Inverso del LEFT. Casi siempre se escribe como LEFT JOIN invirtiendo el orden, más legible.

-- Equivalente a LEFT JOIN con orden invertido
SELECT c.nombre, p.id
FROM pedidos p
RIGHT JOIN clientes c ON p.cliente_id = c.id;

CROSS JOIN

Producto cartesiano: cada fila de A con cada fila de B. Sin cláusula ON. Útil para generar todas las combinaciones (calendarios × productos para reportes).

-- Genera todas las combinaciones (cliente, mes) para un reporte
SELECT c.id, m.mes
FROM clientes c
CROSS JOIN (
    SELECT 1 AS mes UNION ALL SELECT 2 UNION ALL SELECT 3
    -- ...hasta 12
) m;

SELF JOIN

La misma tabla aparece dos veces con alias distintos. Útil para jerarquías o comparaciones entre filas de la misma tabla.

CREATE TABLE empleados (
    id           BIGINT PRIMARY KEY,
    nombre       VARCHAR(120),
    jefe_id      BIGINT NULL,
    FOREIGN KEY (jefe_id) REFERENCES empleados(id)
);

-- Cada empleado con el nombre de su jefe
SELECT e.nombre AS empleado, j.nombre AS jefe
FROM empleados e
LEFT JOIN empleados j ON j.id = e.jefe_id;

FULL OUTER JOIN

Todas las filas de ambas tablas; las que no tienen pareja muestran NULL del lado que falta.

-- MariaDB nativo
SELECT c.nombre, p.id
FROM clientes c
FULL OUTER JOIN pedidos p ON p.cliente_id = c.id;

-- Emulación en MySQL
SELECT c.nombre, p.id
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.id

UNION

SELECT c.nombre, p.id
FROM clientes c
RIGHT JOIN pedidos p ON p.cliente_id = c.id
WHERE c.id IS NULL;

-- PostgreSQL nativo
SELECT c.nombre, p.id
FROM clientes c
FULL OUTER JOIN pedidos p ON p.cliente_id = c.id;

-- SQLite 3.39+
SELECT c.nombre, p.id
FROM clientes c
FULL OUTER JOIN pedidos p ON p.cliente_id = c.id;

-- SQL Server nativo
SELECT c.nombre, p.id
FROM clientes c
FULL OUTER JOIN pedidos p ON p.cliente_id = c.id;

STRAIGHT_JOIN

SELECT STRAIGHT_JOIN c.nombre, p.id
FROM clientes c, pedidos p
WHERE p.cliente_id = c.id;

-- SQLite: el orden escrito manda
SELECT c.nombre, p.id
FROM pedidos p
CROSS JOIN clientes c ON p.cliente_id = c.id;

-- SQL Server: el orden escrito manda
SELECT c.nombre, p.id
FROM pedidos p
JOIN clientes c ON p.cliente_id = c.id
OPTION (FORCE ORDER);

-- El pedido más caro de cada cliente
SELECT c.nombre, x.total
FROM clientes c
CROSS APPLY (
    SELECT TOP (1) p.total
    FROM pedidos p
    WHERE p.cliente_id = c.id
    ORDER BY p.total DESC
) x;

Anti-join y semi-join

Patrones lógicos, no palabras clave SQL:

- Semi-join (existe al menos un match) → EXISTS o IN.
- Anti-join (no existe match) → NOT EXISTS o LEFT JOIN ... WHERE ... IS NULL.

-- Semi-join: clientes con al menos un pedido
SELECT c.id, c.nombre
FROM clientes c
WHERE EXISTS (SELECT 1 FROM pedidos p WHERE p.cliente_id = c.id);

-- Anti-join: clientes sin pedidos
SELECT c.id, c.nombre
FROM clientes c
WHERE NOT EXISTS (SELECT 1 FROM pedidos p WHERE p.cliente_id = c.id);

NOT IN no es un anti-join. Si la subconsulta devuelve aunque sea un NULL, la comparación no es cierta nunca y el resultado son cero filas. Medido con 5 clientes y 6 pedidos, 3 de ellos con cliente_id a NULL: NOT EXISTS y LEFT JOIN … IS NULL devuelven 2, y NOT IN devuelve 0. Pasa igual en PostgreSQL 17.6, en MySQL 8.0.46, en SQLite 3.51 y en SQL Server 2025.

Rendimiento

1. Las columnas del ON deben estar indexadas, especialmente del lado "join interno" (el que se busca por cada fila del externo).
2. Filtra lo más posible antes del join (WHERE en cada tabla cuando sea aplicable).
3. Evita JOIN sobre expresiones (ON LOWER(a.cod) = LOWER(b.cod)) — el índice no se usa.
4. EXPLAIN revela el orden de lectura y el método, con el vocabulario de cada motor.

-- El plan de SQLite se pide así, no con EXPLAIN a secas
EXPLAIN QUERY PLAN
SELECT c.id, c.nombre
FROM clientes c
WHERE NOT EXISTS (SELECT 1 FROM pedidos p WHERE p.cliente_id = c.id);

Palabras clave: joins, INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, SELF JOIN, EXISTS, semi-join, anti-join, STRAIGHT_JOIN, nested loop, CROSS APPLY, OUTER APPLY, FORCE ORDER

Variables críticas de performance: buffer pool, conexiones, paquete máximo, caché de queries y diferencias clave entre MySQL y MariaDB.

Aplica a: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

La configuración por defecto del servidor casi nunca es la óptima en producción. Estas son las variables que tienen mayor impacto en rendimiento y estabilidad.

innodb_buffer_pool_size

El caché principal de InnoDB: tablas, índices y datos. Es la variable más importante.

- Regla práctica: 60 %–80 % de la RAM en un servidor dedicado a MySQL.
- Mínimo recomendado: 1 GiB en producción.
- En MySQL 8.0+ y MariaDB 10.5+ se puede cambiar en caliente (sin reiniciar).

-- Ver tamaño actual
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- Cambiar en caliente (8 GB)
SET GLOBAL innodb_buffer_pool_size = 8 * 1024 * 1024 * 1024;

max_connections

Número máximo de conexiones simultáneas. Por defecto 151 en MySQL, 100 en MariaDB.

- Cada conexión consume memoria (thread_stack + buffers por sesión, ~256 KiB).
- Un valor demasiado alto empeora la performance bajo carga (contención).
- Mide con SHOW STATUS LIKE 'Max_used_connections'. Si llega al techo, aumenta gradualmente.

SHOW STATUS LIKE 'Max_used_connections';
SHOW STATUS LIKE 'Threads_connected';
SET GLOBAL max_connections = 500;

max_allowed_packet

Tamaño máximo de un paquete de protocolo (un INSERT grande, un LOAD DATA, un BLOB).

- Por defecto 64 MiB en MySQL 8.0, 16 MiB en versiones anteriores.
- Si una operación lo excede: error Got a packet bigger than 'max_allowed_packet' bytes.
- Aumentar a 256 MiB o 1 GiB es habitual en cargas con BLOBs.

SET GLOBAL max_allowed_packet = 256 * 1024 * 1024;
-- El cliente también debe pasar el parámetro
-- (en línea de comandos: --max-allowed-packet=256M)

Query cache

Cachea el resultado completo de queries SELECT.

SHOW VARIABLES LIKE 'query_cache%';
SET GLOBAL query_cache_type = 'ON';
SET GLOBAL query_cache_size = 64 * 1024 * 1024;

Logs y durabilidad

innodb_flush_log_at_trx_commit controla cuándo se vuelca el redo log:

- 1 (por defecto) — vuelca y fsync en cada commit. Máxima durabilidad, mínima velocidad. ACID estricto.
- 2 — vuelca en cada commit, fsync cada segundo. Una caída del SO puede perder ~1s. Casi ACID.
- 0 — vuelca y fsync cada segundo. Una caída de MySQL puede perder ~1s. No ACID.

En réplicas o entornos donde toleras pérdida acotada, 2 puede multiplicar el throughput x3–x5. No lo cambies en el primario sin entender el riesgo.

thread_pool

tmp_table_size / max_heap_table_size

Tamaño máximo de tablas temporales en memoria. Si una operación excede el límite, MySQL la pasa a disco y pierde velocidad. Mantén ambos valores iguales.

SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';
SET GLOBAL tmp_table_size = 256 * 1024 * 1024;
SET GLOBAL max_heap_table_size = 256 * 1024 * 1024;

-- Cuántas tablas temporales fueron a disco
SHOW STATUS LIKE 'Created_tmp_disk_tables';

innodb_io_capacity / innodb_io_capacity_max

IOPS que InnoDB puede consumir para limpieza de pages sucias y purgas. Por defecto 200 / 2000.

- SSD modernas: 2000 / 4000 o más.
- HDD: deja los valores por defecto.

Configuración persistente

SET PERSIST innodb_buffer_pool_size = 8589934592;
SET PERSIST_ONLY max_connections = 500; -- solo aplica al reiniciar
RESET PERSIST innodb_buffer_pool_size;   -- quitar la persistencia

En MariaDB, la persistencia se hace editando my.cnf (/etc/my.cnf.d/) y reiniciando, o usando includes (!include).

Recomendación general

1. Conoce el workload antes de tocar nada. Un OLTP con escrituras intensas se configura distinto a un data warehouse de lecturas.
2. Cambia una variable a la vez y mide impacto.
3. Documenta cada cambio en my.cnf con un comentario que explique el porqué.
4. No copies configuraciones de blogs sin entenderlas — los valores "óptimos" dependen mucho del hardware y la carga.

Palabras clave: configuración, buffer pool, innodb_buffer_pool_size, max_connections, max_allowed_packet, query_cache, thread_pool, tmp_table_size, innodb_flush_log_at_trx_commit, io_capacity, my.cnf

Tamaños máximos de base de datos, tablas, columnas, longitudes de nombres y caracteres permitidos en identificadores.

Aplica a: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Conocer los límites del motor evita sorpresas al crecer. Estos son los topes prácticos en MySQL y MariaDB modernos.

Por base de datos

- Tamaño total: limitado por el filesystem. Con innodb_file_per_table = ON (default), cada tabla es un fichero .ibd. En ext4 / XFS el límite teórico se mide en exabytes — el límite real lo pone tu storage.
- Tablas por base de datos: prácticamente ilimitado. El catálogo (information_schema, mysql.tables) gestiona varios cientos de miles sin problemas. Cargas con 10 000+ tablas exigen ajustar table_open_cache.

Por tabla

- Filas: 2⁶⁴ filas teóricas. Práctico: cientos de miles de millones si el esquema y los índices son buenos.
- Tamaño máximo de tabla: 64 TiB con la INNODB_PAGE_SIZE por defecto (16 KiB).
- Columnas: máximo 4 096 por tabla, pero el límite real lo dicta el row size, no el número.
- Tamaño máximo de fila: 65 535 bytes (excluyendo BLOB/TEXT que se almacenan fuera de la fila).
- Índices por tabla: 64.
- Columnas por índice: 16 (B-tree InnoDB).
- Longitud máxima de la clave de un índice: 3072 bytes con DYNAMIC/COMPRESSED (formato por defecto en MySQL 5.7+ / MariaDB 10.2+).

-- Inspeccionar tamaño de tablas
SELECT table_schema, table_name,
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC
LIMIT 20;

Por columna

Identificadores (nombres de objetos)

- Bases de datos, tablas, columnas, índices, vistas: 64 caracteres.
- Alias de columnas: 256 caracteres.
- Funciones, procedimientos, triggers, eventos: 64 caracteres.
- Constraints (FK, CHECK, UNIQUE): 64 caracteres.

Caracteres permitidos en identificadores

- Sin comillas inversas: letras ASCII, dígitos, _ y $. No pueden empezar por dígito puro ni ser solo dígitos.
- Con comillas inversas (`nombre raro`): cualquier carácter Unicode excepto U+0000 (NUL).

Convención recomendada: snake_case ASCII (pedido_cliente_id). Evita espacios, acentos y mayúsculas — algunos sistemas las normalizan distinto entre Linux y macOS.

-- Válido pero no recomendable
CREATE TABLE `pedidos del año 2024` (`Número de Orden` INT);

-- Recomendado
CREATE TABLE pedidos_2024 (numero_orden INT);

Sensibilidad a mayúsculas/minúsculas

lower_case_table_names:

- 0 — los nombres se almacenan tal cual se crearon y son sensibles a mayúsculas. Default en Linux.
- 1 — los nombres se guardan en minúsculas y las comparaciones ignoran case. Default en macOS y Windows.
- 2 — se almacenan tal cual pero las comparaciones ignoran case. Solo macOS/Windows.

Cambiar este valor en una instalación existente es destructivo. Decide al inicializar el servidor.

Por consulta

- Subconsultas anidadas: hasta 64 niveles.
- UNION: ilimitado teóricamente, pero el optimizador degrada por encima de varios cientos.
- Parámetros en un prepared statement: 65 535.
- Filas en un IN(...): práctico hasta unos pocos miles; por encima, mejor JOIN con tabla temporal.

Per-sesión

- Variables de sesión (@@SESSION.xxx): pueden setear casi cualquier global runtime.
- Variables de usuario (@variable): hasta 64 caracteres en el nombre.

Charset y collation

- Charset recomendado: utf8mb4 (UTF-8 completo, 4 bytes). El alias utf8 es histórico y limitado a 3 bytes (sin emojis).
- Collation recomendada en MySQL 8.0+: utf8mb4_0900_ai_ci (case-insensitive, acento-insensitive, basado en Unicode 9).
- En MariaDB: utf8mb4_unicode_ci o uca1400_ai_ci (10.10+).

ALTER DATABASE mi_base
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

ALTER TABLE clientes
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

Recomendaciones

1. Diseña con holgura: si esperas 10 millones de filas, dimensiona índices y particiones para 100 M.
2. Usa BIGINT UNSIGNED en claves primarias de tablas que pueden crecer. INT se llena en ~2 mil millones.
3. Define charset y collation explícitos al crear bases, tablas y columnas. Heredar del default puede fallar al migrar.
4. Documenta los límites de tu modelo (filas esperadas/año, tamaño máximo por columna). Sirve para capacity planning y para detectar consultas anómalas.

Palabras clave: límites, máximo, tamaño, columnas, filas, identificadores, charset, collation, utf8mb4, lower_case_table_names, índice, row size

Diseño de esquemas, convenciones de nombres, backups, replicación, seguridad de usuarios, GRANTs mínimos y auditoría.

Aplica a: MySQL 5.7+ MariaDB 10.5+ Aurora 2+ SQL Server 2022+

Recomendaciones operativas que separan una base de datos amateur de una mantenible en producción.

Diseño de esquemas

CREATE TABLE pedidos (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cliente_id    BIGINT UNSIGNED NOT NULL,
    estado        TINYINT UNSIGNED NOT NULL DEFAULT 0,
    creado_en     DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    actualizado_en DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
                              ON UPDATE CURRENT_TIMESTAMP(6),
    FOREIGN KEY (cliente_id) REFERENCES clientes(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    INDEX idx_pedidos_cliente (cliente_id),
    INDEX idx_pedidos_creado (creado_en)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

-- El esquema, antes y en su propio lote: CREATE SCHEMA ventas;
CREATE TABLE ventas.pedidos (
    id             BIGINT IDENTITY(1, 1) CONSTRAINT pk_pedidos PRIMARY KEY,
    cliente_id     BIGINT       NOT NULL,
    estado         TINYINT      NOT NULL CONSTRAINT df_pedidos_estado DEFAULT 0,
    creado_en      DATETIME2(3) NOT NULL CONSTRAINT df_pedidos_creado_en DEFAULT SYSUTCDATETIME(),
    actualizado_en DATETIME2(3) NOT NULL CONSTRAINT df_pedidos_actualizado_en DEFAULT SYSUTCDATETIME(),
    CONSTRAINT fk_pedidos_clientes FOREIGN KEY (cliente_id)
        REFERENCES ventas.clientes (id) ON UPDATE CASCADE,
    CONSTRAINT ck_pedidos_estado CHECK (estado BETWEEN 0 AND 4)
);
CREATE INDEX idx_pedidos_cliente_id ON ventas.pedidos (cliente_id);
CREATE INDEX idx_pedidos_creado_en  ON ventas.pedidos (creado_en);

Convenciones de nombres

- Tablas: snake_case, plural si representan colecciones (pedidos, clientes).
- Columnas: snake_case, sin prefijo redundante (nombre, no cliente_nombre dentro de clientes).
- Claves foráneas: <tabla>_id (cliente_id).
- Índices: idx_<tabla>_<columnas> o uq_<tabla>_<columnas> para únicos.
- Foreign keys explícitas: fk_<tabla>_<tabla_destino>.
- Procedimientos / funciones: prefijo sp_ / fn_ opcional, verbo en infinitivo (fn_calcular_descuento).

Consistencia > preferencia personal. Acuerda la convención en tu equipo y aplícala universalmente.

Backups

-- Volcado lógico con consistencia transaccional
-- (desde shell, no SQL):
-- mysqldump --single-transaction --routines --triggers --events \\
--           -u root -p mi_base > mi_base.sql

-- Backup físico (mariabackup):
-- mariabackup --backup --target-dir=/srv/backup/full \\
--             --user=root --password=...

ALTER DATABASE mi_base SET RECOVERY FULL;
BACKUP DATABASE mi_base TO DISK = N'/var/opt/mssql/data/mi_base_full.bak'
    WITH CHECKSUM, COMPRESSION, INIT;
BACKUP LOG mi_base TO DISK = N'/var/opt/mssql/data/mi_base_log.trn'
    WITH CHECKSUM, INIT;
RESTORE VERIFYONLY FROM DISK = N'/var/opt/mssql/data/mi_base_full.bak'
    WITH CHECKSUM;

Replicación

Usuarios y permisos

Principio de privilegio mínimo: cada conexión usa el usuario más restringido posible.

-- Crear usuario de aplicación con permisos limitados
CREATE USER 'app_pedidos'@'10.0.%.%' IDENTIFIED BY 'contraseña_fuerte';

GRANT SELECT, INSERT, UPDATE, DELETE
   ON mi_base.pedidos        TO 'app_pedidos'@'10.0.%.%';
GRANT SELECT
   ON mi_base.clientes       TO 'app_pedidos'@'10.0.%.%';

-- NUNCA en producción
-- GRANT ALL PRIVILEGES ON *.* TO 'app'@'%';
FLUSH PRIVILEGES;

USE master;
CREATE LOGIN app_pedidos WITH PASSWORD = N'Contraseña-Fuerte-2026',
    CHECK_POLICY = ON, DEFAULT_DATABASE = mi_base;
USE mi_base;
CREATE USER app_pedidos FOR LOGIN app_pedidos WITH DEFAULT_SCHEMA = ventas;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::ventas TO app_pedidos;

-- NUNCA en producción
-- ALTER SERVER ROLE sysadmin ADD MEMBER app_pedidos;
-- ALTER ROLE db_owner ADD MEMBER app_pedidos;

Auditoría

INSTALL SONAME 'server_audit';
SET GLOBAL server_audit_logging = ON;
SET GLOBAL server_audit_events = 'CONNECT,QUERY,TABLE';
SET GLOBAL server_audit_file_path = '/var/log/mysql/audit.log';

USE master;
CREATE SERVER AUDIT aud_ventas TO FILE (FILEPATH = N'/var/opt/mssql/data/');
ALTER SERVER AUDIT aud_ventas WITH (STATE = ON);
USE mi_base;
CREATE DATABASE AUDIT SPECIFICATION das_ventas FOR SERVER AUDIT aud_ventas
    ADD (SELECT, INSERT, UPDATE, DELETE ON SCHEMA::ventas BY public)
    WITH (STATE = ON);

SELECT event_time, action_id, server_principal_name, statement
FROM sys.fn_get_audit_file(N'/var/opt/mssql/data/aud_ventas*.sqlaudit', DEFAULT, DEFAULT);

Calíope guarda un log local de queries ejecutadas (Registro SQL) por cada sesión de conexión, independiente del log del servidor.

Lista mínima para producción

Palabras clave: buenas prácticas, diseño, convenciones, backup, replicación, GTID, binlog, GRANT, auditoría, seguridad, InnoDB, foreign key, TLS, SQL Server, IDENTITY, SEQUENCE, esquema, login, XACT_ABORT, SQL Server Audit

Análisis de queries con EXPLAIN, slow query log, identificación de cuellos de botella, caché de InnoDB y uso de performance_schema.

Aplica a: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

La optimización empieza con medir. Sin datos, optimizar es adivinar. Estos son los instrumentos básicos.

EXPLAIN

Muestra el plan que el optimizador escogió para una consulta. No la ejecuta — es seguro lanzarlo en producción.

EXPLAIN SELECT c.nombre, COUNT(p.id) AS pedidos
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.id
WHERE c.activo = 1
GROUP BY c.id;

Columnas clave:

- type — método de acceso. De mejor a peor: system → const → eq_ref → ref → range → index → ALL. ALL = full table scan = malo en tablas grandes.
- key — índice elegido. NULL = no usa índice.
- rows — estimación de filas examinadas. Si es mucho mayor que las filas devueltas, hay margen de mejora.
- Extra — pistas útiles:
- Using index — covering index (genial).
- Using where — filtro aplicado tras leer las filas.
- Using temporary — necesita tabla temporal (caro).
- Using filesort — ordenación fuera de índice (caro en tablas grandes).

EXPLAIN ANALYZE (MySQL 8.0+ / MariaDB 10.1+)

Ejecuta la consulta y muestra los tiempos reales por nodo. Más caro que EXPLAIN, pero mucho más informativo.

EXPLAIN ANALYZE
SELECT c.nombre, COUNT(p.id) AS pedidos
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.id
GROUP BY c.id;

Calíope tiene un Visual Explain integrado que renderiza el árbol del plan: Workspace › Análisis › Visual Explain.

Slow query log

Registra todas las consultas que tarden más de long_query_time segundos.

SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;     -- 1 segundo
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Análisis del log:

- mysqldumpslow — herramienta clásica incluida con MySQL.
- pt-query-digest (Percona Toolkit) — el estándar de facto, agrupa por fingerprint y muestra estadísticas.

performance_schema

Tabla esquema con estadísticas detalladas del servidor.

-- Top 10 queries por tiempo total acumulado
SELECT digest_text,
       count_star            AS exec,
       ROUND(sum_timer_wait/1e9, 2) AS total_ms,
       ROUND(avg_timer_wait/1e9, 2) AS avg_ms,
       sum_rows_examined     AS rows_exam
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 10;

-- Tablas con más I/O
SELECT object_schema, object_name,
       count_read, count_write,
       ROUND(sum_timer_wait/1e9, 2) AS total_ms
FROM performance_schema.table_io_waits_summary_by_table
WHERE object_schema = DATABASE()
ORDER BY sum_timer_wait DESC
LIMIT 10;

Caché de InnoDB

- Buffer pool — datos e índices. Métrica clave: hit ratio (Innodb_buffer_pool_read_requests / (reads + reads_from_disk)). Objetivo: >99 %.

SELECT ROUND(
    (1 - (
        VARIABLE_VALUE FROM performance_schema.global_status
        WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
    ) / (
        VARIABLE_VALUE FROM performance_schema.global_status
        WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
    )) * 100, 2) AS buffer_pool_hit_pct;

-- (Versión que sí parsea, con dos consultas):
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

- Adaptive hash index — hash automático sobre páginas calientes del buffer pool. Por defecto activado.
- Change buffer — buffera modificaciones a páginas no presentes en el buffer pool.

Cuellos de botella habituales

Optimizaciones de consultas — patrones comunes

1. Selecciona solo lo que necesitas. Evita SELECT * en aplicaciones.
2. Evita funciones en columnas indexadas:
- Mal: WHERE YEAR(fecha) = 2024 → no usa índice.
- Bien: WHERE fecha >= '2024-01-01' AND fecha < '2025-01-01'.
3. LIMIT con offset grande es caro — para paginación profunda, usa keyset pagination: WHERE id > :last_seen ORDER BY id LIMIT 50.
4. COUNT(*) en tablas grandes — InnoDB no mantiene contador. Considera columnas resumen o estimaciones (information_schema.tables.table_rows).
5. Subqueries no correlacionadas se ejecutan una vez; correlacionadas, una vez por fila externa. Reescríbelas como JOIN si es posible.

Recomendación

Crea un dashboard básico de monitorización (Calíope tiene uno: Tablero) con:

- Conexiones (Threads_connected, Threads_running).
- Buffer pool hit ratio.
- Queries lentas por minuto.
- Replication lag.
- Espacio en disco por tablespace.

Cuanto antes detectes una degradación, más fácil es corregirla.

Palabras clave: performance, optimización, EXPLAIN, EXPLAIN ANALYZE, slow query log, performance_schema, buffer pool, filesort, covering index, filter, cuello de botella, pt-query-digest

ACID, COMMIT y ROLLBACK, los cuatro niveles de aislamiento y qué anomalía permite cada uno.

Aplica a: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Una transacción agrupa varias sentencias en una unidad que se aplica entera o no se aplica. En InnoDB toda sentencia va dentro de una transacción: si no abres ninguna, el servidor abre y confirma una por sentencia (autocommit = 1).

ACID
- Atomicidad — o se aplican todos los cambios, o ninguno.
- Consistencia — la base pasa de un estado válido a otro; las restricciones se respetan.
- Aislamiento — las transacciones concurrentes no se ven a medias.
- Durabilidad — lo confirmado sobrevive a una caída del servidor.

Control manual
Con COMMIT confirmas y con ROLLBACK deshaces todo lo hecho desde START TRANSACTION.

START TRANSACTION;
UPDATE cuentas SET saldo = saldo - 100 WHERE id = 1;
UPDATE cuentas SET saldo = saldo + 100 WHERE id = 2;
COMMIT;

Puntos de guardado
Un SAVEPOINT deshace solo una parte, sin perder el resto de la transacción:

START TRANSACTION;
INSERT INTO pedidos (cliente_id) VALUES (42);
SAVEPOINT tras_pedido;
INSERT INTO lineas (pedido_id, sku) VALUES (LAST_INSERT_ID(), 'X-1');
ROLLBACK TO SAVEPOINT tras_pedido;
COMMIT;

Los cuatro niveles

El predeterminado de InnoDB es REPEATABLE READ. Gracias a MVCC y a los bloqueos de hueco, InnoDB evita también las lecturas fantasma en ese nivel, algo que el estándar SQL no exige.

Cambiar el nivel

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT @@transaction_isolation;

Ojo con el DDL
CREATE, ALTER, DROP y TRUNCATE provocan un commit implícito: no se pueden deshacer con ROLLBACK. Una migración a medias deja la tabla como quedó.

Transacciones largas
Una transacción abierta obliga a InnoDB a conservar las versiones antiguas de cada fila para las lecturas consistentes. Un START TRANSACTION olvidado hace crecer el undo log y degrada el servidor entero. Búscalas así:

SELECT trx_id, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

Recomendación
Transacciones cortas, con la lógica de negocio fuera y solo el SQL dentro. READ COMMITTED reduce los bloqueos y es lo que usan muchas aplicaciones web; quédate en REPEATABLE READ si necesitas que dos lecturas dentro de la misma transacción devuelvan lo mismo.

Palabras clave: transacción, commit, rollback, savepoint, aislamiento, acid, mvcc, read committed, repeatable read, serializable, autocommit, innodb_trx

Qué bloquea InnoDB, por qué se produce un deadlock y cómo diagnosticarlo sin adivinar.

Aplica a: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

InnoDB bloquea filas, no tablas, y lo hace de forma automática al escribir. Casi todos los problemas de concurrencia se explican por qué filas acabó bloqueando una consulta, y eso depende del índice que usó.

Tipos de bloqueo
- Compartido (S) — varias transacciones pueden leer la misma fila a la vez.
- Exclusivo (X) — lo toma quien escribe; nadie más puede leerla con bloqueo ni escribirla.
- De hueco (gap) — bloquea el espacio entre dos valores del índice para impedir inserciones. Solo en REPEATABLE READ y SERIALIZABLE.
- Next-key — la fila más el hueco anterior. Es el modo normal de InnoDB al recorrer un índice.
- De intención (IS/IX) — marca a nivel de tabla que hay bloqueos de fila dentro; evita que un LOCK TABLES se cuele.

La consecuencia práctica: si la consulta no usa un índice, InnoDB recorre toda la tabla y bloquea todas las filas que examina, no solo las que coinciden. Un índice adecuado no solo acelera: reduce lo que se bloquea.

Lecturas bloqueantes
Un SELECT normal no bloquea nada (lee una versión consistente vía MVCC). Si necesitas leer y después escribir sin que nadie se cuele en medio, pide el bloqueo explícitamente con FOR UPDATE o FOR SHARE:

START TRANSACTION;
SELECT saldo FROM cuentas WHERE id = 1 FOR UPDATE;
UPDATE cuentas SET saldo = saldo - 100 WHERE id = 1;
COMMIT;

Qué es un deadlock
Dos transacciones que esperan cada una un bloqueo que tiene la otra. Ninguna puede avanzar:

-- A
START TRANSACTION;
UPDATE cuentas SET saldo = saldo - 10 WHERE id = 1;
UPDATE cuentas SET saldo = saldo + 10 WHERE id = 2;

-- B
START TRANSACTION;
UPDATE cuentas SET saldo = saldo - 10 WHERE id = 2;
UPDATE cuentas SET saldo = saldo + 10 WHERE id = 1;

InnoDB lo detecta solo y mata la transacción más barata de deshacer, que recibe el error 1213 Deadlock found when trying to get lock. No es un fallo del servidor ni corrupción: es el comportamiento correcto, y la aplicación debe reintentar esa transacción.

Distinto es el error 1205 Lock wait timeout exceeded: ahí no hay ciclo, solo una espera que superó innodb_lock_wait_timeout (50 s por defecto).

Diagnóstico
La sección LATEST DETECTED DEADLOCK de SHOW ENGINE INNODB STATUS guarda el último deadlock con las dos transacciones y las sentencias implicadas. Para ver los bloqueos ahora mismo, performance_schema:

SHOW ENGINE INNODB STATUS;

SELECT * FROM performance_schema.data_locks;

SELECT * FROM performance_schema.data_lock_waits;

SELECT @@innodb_lock_wait_timeout;

Cómo evitarlos
1. Acceder siempre en el mismo orden — si todo el código toca las tablas y las filas en el mismo orden, no puede haber ciclo.
2. Transacciones cortas — menos tiempo con bloqueos tomados, menos ocasiones de chocar.
3. Indexar lo que se filtra — evita bloquear filas que ni siquiera cumplían la condición.
4. Reintentar — un deadlock ocasional es normal en un sistema concurrente; envuelve la transacción en un reintento con espera creciente.
5. Evitar SELECT ... FOR UPDATE innecesarios — si no vas a escribir, no lo pidas.

Recomendación
Ante bloqueos, mira primero el plan de la consulta: la mayoría de los deadlocks reales desaparecen al añadir el índice que faltaba. La Lista de procesos de Calíope te enseña qué sesión está esperando.

Palabras clave: bloqueo, lock, deadlock, interbloqueo, gap lock, next-key, for update, for share, error 1213, innodb status, data_locks, lock wait timeout

RANGE, LIST, HASH y KEY, poda de particiones y la purga instantánea con DROP PARTITION.

Aplica a: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Particionar parte una tabla en varias piezas físicas que el servidor sigue viendo como una sola. No hace mágicamente rápidas las consultas: lo que da es poda de particiones y, sobre todo, la posibilidad de borrar millones de filas en un instante.

Cuándo compensa
El caso claro es una tabla que crece por fecha y de la que se purga lo viejo: logs, eventos, métricas, auditoría. Ahí DROP PARTITION sustituye a un DELETE de horas.

CREATE TABLE eventos (
    id        BIGINT NOT NULL AUTO_INCREMENT,
    ocurrido  DATE   NOT NULL,
    payload   JSON,
    PRIMARY KEY (id, ocurrido)
)
PARTITION BY RANGE (YEAR(ocurrido)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION pmax  VALUES LESS THAN MAXVALUE
);

Los cuatro tipos
- RANGE — por intervalos de un valor ordenable, casi siempre una fecha. El más útil.
- LIST — por pertenencia a un conjunto discreto de valores.
- HASH — reparto uniforme por una expresión entera; sirve para repartir escritura, no para podar.
- KEY — como HASH pero con la función interna del servidor; acepta columnas no enteras.

Las variantes RANGE COLUMNS y LIST COLUMNS aceptan varias columnas y tipos no enteros sin envolverlos en una función:

PARTITION BY LIST (region_id) (
    PARTITION europa VALUES IN (1, 2, 3),
    PARTITION asia   VALUES IN (4, 5)
);

PARTITION BY HASH (cliente_id) PARTITIONS 8;

PARTITION BY KEY (uuid) PARTITIONS 4;

PARTITION BY RANGE COLUMNS (pais, alta) (
    PARTITION p_es_2024 VALUES LESS THAN ('ES', '2025-01-01')
);

Poda de particiones
La ventaja real: si el WHERE filtra por la columna de partición, el servidor solo lee las particiones que pueden contener resultados. Compruébalo en la columna partitions de EXPLAIN — si aparecen todas, no estás podando nada y el particionado solo te está costando.

EXPLAIN SELECT COUNT(*) FROM eventos
WHERE ocurrido BETWEEN '2025-03-01' AND '2025-03-31';

SELECT partition_name, table_rows
FROM information_schema.PARTITIONS
WHERE table_name = 'eventos';

Limitaciones que hay que saber antes
1. La clave de partición debe formar parte de todas las claves únicas, incluida la primaria. Por eso el ejemplo lleva PRIMARY KEY (id, ocurrido) y no solo id.
2. Sin claves foráneas: una tabla particionada no puede tener ni recibir FOREIGN KEY.
3. Máximo 8192 particiones por tabla, y cada una consume descriptores de archivo.
4. Las consultas que no filtran por la clave tocan todas las particiones y salen más lentas que sin particionar.
5. Los índices son locales a cada partición: no existe el índice global.

Mantenimiento
Añadir la partición del periodo siguiente y soltar la más vieja es la rutina normal. DROP PARTITION es prácticamente instantáneo y libera el espacio de verdad, cosa que un DELETE masivo no hace:

ALTER TABLE eventos DROP PARTITION p2023;

ALTER TABLE eventos REORGANIZE PARTITION pmax INTO (
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION pmax  VALUES LESS THAN MAXVALUE
);

ALTER TABLE eventos REBUILD PARTITION p2025;

Recomendación
Particiona por fecha solo si vas a purgar por fecha, y crea las particiones futuras por adelantado (o con un evento programado): si llega una fila que no encaja en ningún rango, el INSERT falla. Deja siempre una pmax de red.

Palabras clave: particionado, partition, range, list, hash, key, pruning, poda, drop partition, reorganize, information_schema.partitions, purga

WITH, WITH RECURSIVE y OVER (): el SQL moderno que evita subconsultas anidadas y tablas temporales.

Aplica a: MySQL 8.0+ MariaDB 10.2+ Aurora 3+ PostgreSQL 13+ SQLite 3.35+ SQL Server 2022+

Las CTE (WITH) y las funciones de ventana (OVER ()) llegaron a MySQL en la 8.0 y a MariaDB en la 10.2; en PostgreSQL no hay versión con soporte vivo que no las tenga, y en SQLite las dos están muy por debajo del suelo de este manual: las CTE desde la 3.8.3 y las ventanas desde la 3.25. Resuelven en una consulta legible lo que antes pedía subconsultas anidadas, tablas temporales o variables de sesión.

CTE: nombrar un paso intermedio
Una CTE es un resultado con nombre que vive solo durante la consulta. Sirve para partir una consulta larga en pasos y para referirse al mismo subresultado dos veces sin repetirlo:

WITH ventas_mes AS (
    SELECT vendedor_id, DATE_FORMAT(fecha, '%Y-%m') AS mes, SUM(total) AS total
    FROM pedidos
    GROUP BY vendedor_id, mes
)
SELECT * FROM ventas_mes WHERE total > 10000;

WITH ventas_mes AS (
    SELECT vendedor_id, date_trunc('month', fecha) AS mes, SUM(total) AS total
    FROM pedidos
    GROUP BY vendedor_id, mes
)
SELECT * FROM ventas_mes WHERE total > 10000;

WITH ventas_mes AS (
    SELECT vendedor_id, strftime('%Y-%m', fecha) AS mes, SUM(total) AS total
    FROM pedidos
    GROUP BY vendedor_id, mes
)
SELECT * FROM ventas_mes WHERE total > 10000;

WITH ventas_mes AS (
    SELECT vendedor_id, DATETRUNC(month, fecha) AS mes, SUM(total) AS total
    FROM pedidos
    GROUP BY vendedor_id, DATETRUNC(month, fecha)
)
SELECT * FROM ventas_mes WHERE total > 10000;

CTE recursiva: jerarquías
WITH RECURSIVE recorre estructuras de árbol —organigramas, categorías anidadas, listas de materiales— sin bucles en la aplicación. La primera rama es el caso base y la segunda se repite hasta que no devuelve filas:

WITH RECURSIVE arbol AS (
    SELECT id, nombre, jefe_id, 1 AS nivel
    FROM empleados
    WHERE jefe_id IS NULL

    UNION ALL

    SELECT e.id, e.nombre, e.jefe_id, a.nivel + 1
    FROM empleados e
    JOIN arbol a ON e.jefe_id = a.id
)
SELECT * FROM arbol ORDER BY nivel, nombre;

Funciones de ventana: calcular sin agrupar
Un GROUP BY colapsa las filas; una función de ventana calcula sobre un conjunto de filas relacionadas y conserva cada fila. Es lo que permite un acumulado, una media móvil o un puesto dentro del grupo en una sola pasada:

SELECT
    vendedor_id,
    fecha,
    total,
    SUM(total)    OVER (PARTITION BY vendedor_id ORDER BY fecha) AS acumulado,
    AVG(total)    OVER (PARTITION BY vendedor_id
                        ORDER BY fecha
                        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS media_7,
    RANK()        OVER (PARTITION BY vendedor_id ORDER BY total DESC) AS puesto,
    LAG(total, 1) OVER (PARTITION BY vendedor_id ORDER BY fecha) AS anterior
FROM pedidos;

Las que más se usan
- ROW_NUMBER() — número correlativo dentro de la partición, sin empates.
- RANK() / DENSE_RANK() — puesto con empates; RANK deja huecos, DENSE_RANK no.
- LAG() / LEAD() — el valor de la fila anterior o siguiente, sin auto-join.
- FIRST_VALUE() / LAST_VALUE() — extremos de la ventana.
- NTILE(n) — reparte las filas en n cubos, para cuartiles y percentiles.
- Agregados con OVER — SUM, AVG, COUNT, MIN, MAX sin colapsar las filas.

El marco (ROWS BETWEEN ...) define qué filas entran en el cálculo de cada una. Por omisión, un agregado con ORDER BY usa desde el principio de la partición hasta la fila actual, que es justo lo que da el acumulado.

El patrón que más rentabiliza: N mejores por grupo
Sacar los tres productos más vendidos de cada categoría sin ventanas exige una subconsulta correlacionada por fila. Con ROW_NUMBER() es directo:

WITH ranking AS (
    SELECT
        p.*,
        ROW_NUMBER() OVER (PARTITION BY categoria_id ORDER BY ventas DESC) AS rn
    FROM productos p
)
SELECT * FROM ranking WHERE rn <= 3;

Rendimiento
Ninguna de las dos es gratis: la ventana necesita ordenar dentro de cada partición, así que un índice que ya entregue las filas en el orden del PARTITION BY más ORDER BY ahorra esa ordenación. Compruébalo con EXPLAIN antes de dar por buena la versión bonita.

Recomendación
Usa CTE para que se entienda la consulta y ventanas para no hacer en la aplicación lo que el servidor hace en una pasada. Si tu servidor es MySQL 5.7 o MariaDB 10.1, ninguna de las dos está disponible: ahí siguen mandando las subconsultas.

Palabras clave: cte, with, with recursive, función de ventana, over, partition by, row_number, rank, dense_rank, lag, lead, ntile, frame, jerarquía, top n por grupo

Por qué un error deja bloqueada la transacción entera, cómo se sale con un punto de guardado, qué hace de verdad cada nivel, y el DDL que sí se deshace.

Aplica a: PostgreSQL 13+

Una transacción agrupa varias sentencias en una unidad que se aplica entera o no se aplica. Sin un BEGIN explícito, PostgreSQL confirma cada sentencia por su cuenta.

Lo primero que sorprende al venir de MySQL
Un error aborta la transacción entera. A partir de ahí, cualquier sentencia contesta lo mismo — current transaction is aborted, commands ignored until end of transaction block, SQLSTATE 25P02 — hasta que hagas ROLLBACK. No es un fallo de la aplicación: es el diseño, y evita que una transacción siga adelante sobre un estado que ya no es el que creías.

La salida es un punto de guardado
SAVEPOINT marca un punto al que volver, y ROLLBACK TO SAVEPOINT rescata la transacción sin perder lo anterior:

BEGIN;
INSERT INTO cuentas (id, saldo) VALUES (3, 0);
SAVEPOINT tras_alta;
INSERT INTO cuentas (id, saldo) VALUES (3, 0);  -- falla: 23505
ROLLBACK TO SAVEPOINT tras_alta;
COMMIT;

El DDL sí se deshace
CREATE, ALTER y DROP van dentro de la transacción: aquí no hay commit implícito. Una migración que falla a la mitad no deja media tabla.

BEGIN;
ALTER TABLE cuentas ADD COLUMN moneda text;
ROLLBACK;   -- la columna no llegó a existir

Los cuatro niveles

READ UNCOMMITTED se acepta y se informa como tal, pero se comporta como READ COMMITTED: en PostgreSQL no hay lecturas sucias en ningún nivel. El predeterminado es READ COMMITTED.

REPEATABLE READ y SERIALIZABLE no bloquean: abortan
En vez de esperarse, la transacción que no se puede serializar termina con SQLSTATE 40001 (could not serialize access…). Eso significa que la aplicación tiene que reintentar: en estos dos niveles, un 40001 es funcionamiento normal, no una avería. SERIALIZABLE usa SSI, que detecta dependencias de lectura y escritura y no toma bloqueos de más.

Bloqueos y deadlocks
Un abrazo mortal se detecta pasado deadlock_timeout — 1 s por omisión — y el servidor mata a una de las dos con SQLSTATE 40P01. Para no esperar, o para repartir trabajo entre consumidores:

SELECT id FROM cuentas ORDER BY id FOR UPDATE SKIP LOCKED;

FOR UPDATE NOWAIT falla al instante con 55P03 en vez de esperar. Cuidado: ese fallo también aborta la transacción.

Transacciones largas
Aquí una transacción abierta no engorda un undo log: impide que VACUUM limpie las versiones muertas en todo el servidor, y la tabla crece sin filas nuevas. Búscalas así:

SELECT pid, state, xact_start, now() - xact_start AS duracion, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

idle_in_transaction_session_timeout vale 0 por omisión, o sea sin límite; ponerle un valor es la red que evita que una sesión olvidada degrade la base entera.

Recomendación
Transacciones cortas, con la lógica de negocio fuera. Si subes a REPEATABLE READ o a SERIALIZABLE, escribe el reintento antes de subir, no después del primer 40001 en producción.

Palabras clave: transacción, commit, rollback, savepoint, aislamiento, mvcc, read committed, repeatable read, serializable, ssi, 25P02, 40001, 40P01, deadlock, skip locked, pg_stat_activity

Por qué una tabla crece sin filas nuevas, qué limpia de verdad cada operación, cuándo mienten los contadores y qué es el wraparound.

Aplica a: PostgreSQL 13+

En PostgreSQL, actualizar una fila no la modifica: escribe una versión nueva y deja muerta la vieja. Borrar tampoco libera nada al momento. Así funciona MVCC aquí, y VACUUM es quien recoge después. No tiene equivalente en InnoDB, y está detrás de casi todas las sorpresas de tamaño.

Cómo se ve
Una tabla de 50 000 filas ocupaba 12 MB. Un solo UPDATE sobre todas ellas la dejó en 23 MB sin añadir ni una fila: las 50 000 versiones viejas siguen en el archivo. Medido en PostgreSQL 17.6.

Qué hace cada cosa
- VACUUM marca el espacio muerto como reutilizable. No devuelve el espacio al sistema operativo: tras el vacuum, la tabla del ejemplo seguía ocupando 23 MB, sólo que las escrituras siguientes ya caben dentro.
- VACUUM FULL reescribe la tabla entera y sí devuelve el espacio —bajó a 11 MB—, pero toma un bloqueo ACCESS EXCLUSIVE: nadie lee ni escribe mientras dura, y necesita sitio para una copia completa. No es el mantenimiento rutinario, es el último recurso.
- ANALYZE no limpia nada: actualiza las estadísticas del planificador.

Autovacuum, que ya está encendido
autovacuum viene on. Una tabla entra en cola cuando sus filas muertas superan autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × filas, o sea 50 + 20 % con los valores por omisión. En una tabla de diez millones de filas eso son dos millones de filas muertas antes de que se mueva nadie, así que en las tablas grandes y muy actualizadas se baja el factor por tabla:

ALTER TABLE pedidos SET (autovacuum_vacuum_scale_factor = 0.02);

Comprobar que va bien

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC
LIMIT 10;

Cuidado con ese contador: es una estimación y no es instantáneo. Medido en 17.6, justo después de actualizar 50 000 filas seguía diciendo 0; sólo tras un ANALYZE pasó a decir 50 000, y tras el VACUUM volvió a 0. Si acabas de escribir mucho y el número no se mueve, eso no significa que no haya trabajo pendiente.

Un vacuum en curso se sigue así:

SELECT pid, relid::regclass AS tabla, phase, heap_blks_scanned, heap_blks_total
FROM pg_stat_progress_vacuum;

El enemigo: la transacción abierta
VACUUM sólo puede limpiar lo que ya no puede ver nadie. Una transacción abierta —o una réplica con hot_standby_feedback— congela ese horizonte, y entonces el vacuum corre, dice que ha terminado y no libera nada. Por eso una sesión olvidada en idle in transaction hace crecer tablas que ni siquiera toca.

El wraparound, que sí es una emergencia
Los identificadores de transacción son de 32 bits y se reciclan. Para que ninguna fila quede en el futuro, el vacuum congela las viejas. autovacuum_freeze_max_age vale 200 000 000 por omisión: superada esa edad, el servidor lanza un autovacuum que no se puede posponer, y si aun así se agota, deja de aceptar escrituras. Se vigila así:

SELECT datname, age(datfrozenxid) AS edad
FROM pg_database
ORDER BY edad DESC;

Mientras esa edad esté muy por debajo de doscientos millones no hay nada que hacer.

Recomendación
No apagues autovacuum. Si una tabla crece sin filas nuevas, el orden de sospecha es: transacción abierta, scale_factor demasiado alto para su tamaño, y sólo al final VACUUM FULL — con ventana de mantenimiento, porque bloquea la tabla entera.

Palabras clave: vacuum, autovacuum, bloat, mvcc, n_dead_tup, vacuum full, congelar, wraparound, pg_stat_user_tables, pg_stat_progress_vacuum, datfrozenxid, hot_standby_feedback, idle in transaction

Qué bloquea de verdad cada sentencia, por qué un ALTER TABLE puede parar los SELECT, cómo se ve quién espera a quién y qué hacer con un 40P01.

Aplica a: PostgreSQL 13+

En PostgreSQL los bloqueos viven en dos sitios distintos, y confundirlos es lo que hace que un problema no se encuentre. Los de tabla están en pg_locks; los de fila están dentro de la propia fila, en su cabecera, así que no ocupan memoria, no escalan a bloqueo de tabla y no salen en pg_locks. Un millón de filas bloqueadas no cuesta más que una.

Y hay una regla sin excepciones: un SELECT normal nunca espera por una fila. Lee su versión por MVCC. Lo único que puede parar a un SELECT es un bloqueo de tabla.

Quién toma qué modo de tabla

Los tres primeros no se estorban entre sí, y por eso la carga normal no se bloquea nunca. El último choca con todos, incluido el SELECT.

La trampa: el ALTER TABLE que espera forma cola detrás
Ese ACCESS EXCLUSIVE no se cuela: se pone a la cola. Y mientras espera, todo lo que llegue después espera detrás de él, aunque sea un SELECT que con la sentencia de delante no tendría ningún problema. Basta una transacción abierta que sólo haya hecho un SELECT para que un ALTER TABLE deje la tabla parada para todo el mundo sin haber empezado a trabajar. Por eso el DDL en producción se lanza con un tope y se reintenta:

SET lock_timeout = '3s';
ALTER TABLE cuentas ADD COLUMN moneda text;

Cuatro modos de fila, no dos
De más fuerte a más débil: FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE. Sólo tres parejas conviven —los dos compartidos entre sí, y FOR NO KEY UPDATE con FOR KEY SHARE—; FOR UPDATE choca con los cuatro. Esa pareja rara es la que importa: un UPDATE que no toca la clave toma FOR NO KEY UPDATE, así que no bloquea la comprobación de una foránea que apunte a esa fila, que es la que pide FOR KEY SHARE.

BEGIN;
SELECT saldo FROM cuentas WHERE id = 1 FOR UPDATE;
UPDATE cuentas SET saldo = saldo - 100 WHERE id = 1;
COMMIT;

Aquí se espera para siempre
lock_timeout vale 0 por omisión, o sea sin límite: no hay equivalente al innodb_lock_wait_timeout de MySQL, que corta a los 50 s. Ponerlo —por sesión, antes de una sentencia peligrosa, o en la configuración— es lo que convierte una espera eterna en un error que la aplicación puede reintentar. Cuando salta, SQLSTATE 55P03.

No esperar, a propósito

SELECT id FROM cuentas WHERE id = 3 FOR UPDATE NOWAIT;

SELECT id FROM cuentas ORDER BY id FOR UPDATE SKIP LOCKED;

NOWAIT falla al instante con 55P03 —y ese fallo, como cualquier otro, aborta la transacción entera—. SKIP LOCKED no falla: devuelve menos filas. Con la fila 3 bloqueada por otra sesión, la segunda consulta devolvió 1, 2, 4 y 5. Es la forma de repartir una cola de trabajo entre varios consumidores sin que se pisen ni se esperen.

El abrazo mortal

-- Sesión A
BEGIN;
UPDATE cuentas SET saldo = saldo - 10 WHERE id = 1;
UPDATE cuentas SET saldo = saldo + 10 WHERE id = 2;

-- Sesión B
BEGIN;
UPDATE cuentas SET saldo = saldo - 10 WHERE id = 2;
UPDATE cuentas SET saldo = saldo + 10 WHERE id = 1;

El servidor mata a una de las dos con SQLSTATE 40P01 («deadlock detected»), y el detalle dice qué proceso y qué fila. Pero no lo detecta al instante: sólo busca el ciclo cuando una espera pasa de deadlock_timeout, que vale 1 s por omisión, y la víctima tarda ese segundo en morir. InnoDB lo detecta al momento; aquí un abrazo mortal se paga con un segundo de espera. Bajar deadlock_timeout no sale gratis: ese trabajo se gasta también en las esperas normales, que son la mayoría.

Un 40P01 no es una avería del servidor: es el comportamiento correcto, y la aplicación tiene que reintentar esa transacción.

Diagnóstico
Aquí no hay SHOW ENGINE INNODB STATUS. Hay dos consultas, y las dos hay que hacerlas mientras el bloqueo dura:

SELECT pid, pg_blocking_pids(pid) AS bloqueado_por,
       wait_event_type, wait_event, left(query, 40) AS consulta
  FROM pg_stat_activity
 WHERE cardinality(pg_blocking_pids(pid)) > 0;

SELECT l.pid, c.relname, l.locktype, l.mode, l.granted
  FROM pg_locks l LEFT JOIN pg_class c ON c.oid = l.relation
 WHERE NOT l.granted;

pg_blocking_pids da la lista de procesos que retienen lo que el otro pide, que es la pregunta que de verdad se hace uno. Lo que no vas a ver en pg_locks son los bloqueos de fila: quien espera por una fila aparece esperando un transactionid, el número de la transacción que la tiene. Y para dejar rastro de lo que ya pasó, log_lock_waits escribe en el registro del servidor toda espera que supere deadlock_timeout.

La Lista de procesos de Calíope enseña esa espera en la columna de estado: una sesión bloqueada aparece como Lock: transactionid.

Bloqueos consultivos
Ninguna tabla, ningún dato: un número que el servidor guarda por ti para que dos procesos de tu aplicación no hagan lo mismo a la vez.

SELECT pg_try_advisory_lock(42);
SELECT pg_advisory_unlock(42);

Mientras una sesión tiene el 42, pg_try_advisory_lock(42) desde otra devuelve false en vez de esperar. Ojo con el alcance: los de sesión sobreviven al COMMIT y sólo se sueltan al soltarlos o al cerrar la conexión; los pg_advisory_xact_lock se sueltan solos al acabar la transacción, que casi siempre es lo que se quiere.

SERIALIZABLE no bloquea
En ese nivel aparecen en pg_locks bloqueos SIReadLock. No bloquean a nadie: son la marca de lo que la transacción leyó, y el conflicto llega como 40001 al confirmar, no como una espera.

Recomendación
Tocar siempre las filas en el mismo orden y transacciones cortas, igual que en cualquier motor. Lo propio de aquí son dos cosas: lock_timeout puesto antes de cada DDL, porque el que espera para la cola detrás; y el reintento escrito antes de subir a producción, que es lo único que hace inofensivo un 40P01.

Palabras clave: bloqueo, lock, deadlock, interbloqueo, 40p01, 55p03, pg_locks, pg_blocking_pids, lock_timeout, deadlock_timeout, access exclusive, for update, for key share, skip locked, nowait, bloqueo consultivo, advisory

Los seis métodos de índice y cuándo va cada uno, índices parciales y por expresión, por qué un Index Only Scan a veces sí va a la tabla, y cómo encontrar los que no usa nadie.

Aplica a: PostgreSQL 13+

Un índice acelera las búsquedas a cambio de espacio y de trabajo en cada escritura. Lo que cambia al venir de MySQL no es esa idea, sino que aquí hay seis métodos en vez de uno con excepciones, y que casi todo lo que en MySQL es una opción del índice —el prefijo, la invisibilidad— aquí es otra cosa.

Los seis métodos

Los tamaños explican la elección mejor que la teoría. Sobre una tabla de 200 000 filas y 22 MB, con una marca de tiempo que crece con la inserción:

- B-tree sobre esa columna: 4 408 kB.
- BRIN sobre esa misma columna: 24 kB.

BRIN no guarda las filas, sino el mínimo y el máximo de cada bloque, así que sólo sirve si el orden físico se parece al orden del valor —y cuando sirve, cuesta casi nada—. En la misma tabla, un hash sobre la columna de cliente ocupó 7 032 kB y el B-tree sobre esa columna, 1 400 kB: más pequeño, y además sirve para rangos y para ordenar. Por eso el B-tree es la respuesta por omisión y el hash, un caso concreto.

Índices parciales: la mitad de la idea, la décima parte del tamaño
Un índice puede llevar WHERE, y entonces sólo indexa las filas que cumplen la condición. Si consultas la cola de pendientes y los pendientes son el 5 %, indexa el 5 %:

CREATE INDEX idx_pendientes ON pedidos (cliente_id) WHERE estado = 'pendiente';

Medido sobre esa misma tabla: el índice completo de la columna ocupaba 1 400 kB y el parcial, 88 kB. La condición del WHERE de la consulta tiene que implicar la del índice, o el planificador no lo usará.

Aquí no hay índices de prefijo: hay índices por expresión
CREATE INDEX … ON paginas (url(64)) no es sintaxis válida; el servidor lee url(64) como una llamada a función y contesta 42883 function url(integer) does not exist. Lo equivalente es indexar la expresión:

CREATE INDEX idx_url ON paginas (left(url, 64));
CREATE INDEX idx_email ON usuarios (lower(email));

Y la letra pequeña: el índice sólo entra si la consulta escribe la expresión igual. WHERE lower(email) = 'ana@ejemplo.com' lo usa; WHERE email ILIKE 'Ana@%' no, y se come la tabla entera.

Índices de cobertura, y por qué a veces no cubren
INCLUDE añade columnas que se guardan en el índice pero no ordenan:

CREATE INDEX idx_cobertura ON pedidos (cliente_id) INCLUDE (estado);

Con eso, EXPLAIN enseña Index Only Scan. Pero «only» aquí es una promesa a medias: PostgreSQL no sabe por el índice si una fila es visible para tu transacción, así que consulta el mapa de visibilidad, que mantiene VACUUM. Medido: justo después de actualizar mil filas, el mismo plan decía Heap Fetches: 2; tras un VACUUM, Heap Fetches: 0. Un índice de cobertura sobre una tabla que se escribe y no se limpia sigue yendo a la tabla.

La regla del prefijo izquierdo no es estricta
En MySQL, un índice sobre (A, B) no sirve para WHERE B = ?. Aquí sí puede servir: medido, una consulta que sólo filtraba por la segunda columna resolvió con Index Only Scan sobre el compuesto. No es magia ni sustituye al índice correcto —lo recorre entero en vez de bajar por él—, pero cuando el índice es mucho más pequeño que la tabla, sigue saliendo a cuenta. Consecuencia práctica: antes de crear el índice «que falta», mira el plan; puede que ya se esté usando uno.

GIN para lo que va dentro de un campo

CREATE INDEX idx_datos ON eventos USING gin (datos);

SELECT count(*) FROM eventos WHERE datos @> '{"tags":["t7"]}';

Sin el índice, esa consulta es un recorrido secuencial; con él, Bitmap Index Scan. El GIN de la prueba ocupó 864 kB sobre 200 000 filas. Para jsonb, si sólo consultas con @>, jsonb_path_ops ocupa menos: 640 kB frente a los 864 del GIN normal, sobre los mismos datos.

Crear sin parar la tabla
CREATE INDEX normal toma un bloqueo SHARE: deja leer y para las escrituras. CREATE INDEX CONCURRENTLY toma SHARE UPDATE EXCLUSIVE, así que no para nada, a cambio de dos recorridos de la tabla y de tres reglas:

CREATE INDEX CONCURRENTLY idx_pedidos_cliente ON pedidos (cliente_id);

-- ¿Quedó alguno a medias?
SELECT indexrelid::regclass AS indice, indisvalid
  FROM pg_index WHERE NOT indisvalid;

1. No se puede lanzar dentro de una transacción — 25001 CREATE INDEX CONCURRENTLY cannot run inside a transaction block.
2. Si falla, deja un índice inválido: no lo usa nadie, pero se mantiene en cada escritura. Hay que encontrarlo con la consulta de arriba y borrarlo.
3. Desde PostgreSQL 12 existe REINDEX INDEX CONCURRENTLY, que es como se reconstruye un índice hinchado sin parar la tabla.

Los índices que no usa nadie

SELECT relname AS tabla, indexrelname AS indice, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS tamano
  FROM pg_stat_user_indexes
 WHERE idx_scan = 0
 ORDER BY pg_relation_size(indexrelid) DESC;

Dos cautelas antes de borrar. La primera: el contador no es instantáneo. En la prueba, tres consultas que usaron el índice lo dejaron a 0 al momento y al segundo y pico; sólo a los tres segundos dijo 3. La segunda: cuenta desde el último pg_stat_reset(), y en una réplica cuenta lo de la réplica, así que un índice que sólo usa el informe de fin de mes parece muerto los otros 29 días.

Recomendación
Cada índice de más se paga en cada INSERT y en cada UPDATE de sus columnas. El orden que funciona es: mirar el plan, crear el índice con CONCURRENTLY, volver a mirar el plan, y revisar pg_stat_user_indexes un mes después. Un índice que nadie usa no es neutral: cuesta escritura, espacio y tiempo de VACUUM.

Palabras clave: índice, index, btree, brin, gin, gist, spgist, hash, índice parcial, índice por expresión, include, index only scan, heap fetches, concurrently, reindex, pg_stat_user_indexes, indisvalid, jsonb_path_ops, mapa de visibilidad

Leer un plan y comparar la estimación con lo medido, estadísticas extendidas para columnas que se implican, por qué work_mem es por operación y qué mide de verdad la tasa de acierto de caché.

Aplica a: PostgreSQL 13+

Diagnosticar aquí es leer un plan y comparar dos números. Todo lo demás —índices, memoria, estadísticas— sale de esa comparación.

EXPLAIN no ejecuta; EXPLAIN ANALYZE sí
La primera forma sólo pide el plan. La segunda ejecuta la consulta de verdad para medirla, y eso incluye un UPDATE o un DELETE. Si la sentencia escribe, envuélvela:

BEGIN;
EXPLAIN (ANALYZE) DELETE FROM pedidos WHERE creado < '2020-01-01';
ROLLBACK;

Los dos números que importan
En cada nodo hay una estimación y una medición: rows=… es lo que el planificador creyó, actual rows=… lo que salió. Cuando se separan mucho, el plan malo es una consecuencia, no la causa.

Ejemplo medido, con dos columnas que se implican —ciudad y provincia—:

- Sin ayuda, el planificador estimó 11 710 filas y salieron 60 000: multiplicó las dos probabilidades como si fueran independientes.
- Con una estadística extendida, la estimación pasó a 59 610.

CREATE STATISTICS st_ciudad_prov (dependencies, ndistinct)
    ON ciudad, provincia FROM pedidos;
ANALYZE pedidos;

Ésa es la herramienta que no existe en MySQL y que arregla la familia entera de «el plan ignora mi índice»: si el servidor cree que va a leer el 4 % de la tabla cuando lee el 20 %, elegirá mal por buenas razones.

BUFFERS, que hay que pedir

EXPLAIN (ANALYZE, BUFFERS) SELECT … ;

shared hit son bloques que estaban en memoria; shared read, los que hubo que ir a buscar. Y temp read/written es la delación importante: la consulta se salió a disco. Además, track_io_timing viene apagado por omisión, así que los tiempos de E/S no aparecen hasta que se enciende.

work_mem es por operación, no por conexión
Éste es el ajuste que más sorprende, y el que más se equivoca. Cada ordenación, cada hash join y cada agregación por hash puede usar hasta work_mem, y una consulta con tres de esas operaciones —o con dos obreros en paralelo— usa un múltiplo. El valor de fábrica es 4 MB.

Medido sobre 300 000 filas, la misma consulta con ORDER BY:

- Con work_mem = 64kB: Sort Method: external merge Disk: 15680kB, y el nodo de ordenación tardó ~144 ms.
- Con work_mem = 64MB: Sort Method: quicksort Memory: 29627kB, y tardó ~70 ms.

La cifra que dice la verdad es Sort Method. Subir work_mem global multiplica por conexiones y por operaciones; lo prudente es subirlo en la sesión que lo necesita:

SET work_mem = '64MB';

Qué consulta cuesta más: pg_stat_statements
Es el equivalente del registro de consultas lentas, pero agregado: una fila por forma de consulta, con llamadas, tiempo total y filas.
En Calíope esto mismo se lee sin escribir la consulta: la herramienta Consultas lentas enseña este resumen, distingue la extensión que falta de la biblioteca que no está cargada, y lleva cualquier fila al editor.

SELECT calls, round(total_exec_time::numeric, 1) AS ms_total,
       round(mean_exec_time::numeric, 2) AS ms_media, rows, query
  FROM pg_stat_statements
 ORDER BY total_exec_time DESC
 LIMIT 20;

La letra pequeña: no basta con CREATE EXTENSION. Hay que añadirla a shared_preload_libraries y reiniciar el servidor; si sólo se crea la extensión, la primera consulta contesta 55000 pg_stat_statements must be loaded via "shared_preload_libraries". Ordena por total_exec_time, no por mean_exec_time: la consulta que se lleva la tarde suele ser una rápida ejecutada un millón de veces.

La tasa de acierto de caché

SELECT blks_hit, blks_read,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS pct
  FROM pg_stat_database
 WHERE datname = current_database();

Es el número que enseña el Tablero de Calíope como «Caché de datos». Mide cuántas lecturas se resolvieron sin bajar al sistema de archivos desde el último reinicio de estadísticas —no cuánta memoria hay ocupada—, y en una base pequeña sale altísimo por definición: en la prueba, 99,85 %. Un valor bajo de forma sostenida sí es señal de que shared_buffers se queda corto; uno alto no demuestra que todo vaya bien.

Paralelismo
max_parallel_workers_per_gather vale 2 por omisión, y cuando el planificador lo usa aparece un nodo Gather o Gather Merge. Cada obrero tiene su propio work_mem, que es la otra mitad de la trampa de arriba.

Recomendación
El orden que funciona: encontrar la consulta con pg_stat_statements, mirarla con EXPLAIN (ANALYZE, BUFFERS), comparar rows con actual rows y, sólo entonces, decidir si falta un índice, faltan estadísticas o falta memoria. Tocar shared_buffers antes de haber leído un plan es el camino largo.

Palabras clave: rendimiento, explain, analyze, buffers, plan, planificador, estimación, create statistics, estadística extendida, work_mem, sort method, external merge, pg_stat_statements, shared_preload_libraries, pg_stat_database, caché, track_io_timing, paralelismo

Dónde se escribe cada parámetro y cuál gana, qué exige reiniciar, por qué effective_cache_size no reserva memoria y por qué max_connections no se sube.

Aplica a: PostgreSQL 13+

PostgreSQL tiene 378 parámetros —los de este servidor, contados—, y la buena noticia es que se toca un puñado. Lo que hay que aprender primero no es cuáles, sino dónde se escriben y cuándo entran en vigor.

Cuatro sitios, y el que gana lo dice el servidor
- postgresql.conf — el archivo de siempre, editado a mano.
- postgresql.auto.conf — lo escribe ALTER SYSTEM y no se edita a mano; lo dice él mismo en su primera línea.
- Por base o por rol — ALTER DATABASE … SET, ALTER ROLE … SET.
- Por sesión — SET, que dura lo que la conexión.

Quién ganó no se adivina: la columna source de pg_settings lo dice. Medido: tras ALTER DATABASE demo SET work_mem = '32MB', una conexión nueva leía 32MB con source = database; tras el RESET, 4MB con source = default.

SELECT name, setting, unit, context, source, pending_restart
  FROM pg_settings
 WHERE name IN ('shared_buffers','work_mem','max_connections','max_wal_size');

Tres clases de parámetro, y la que duele
La columna context dice qué hace falta para cambiarlo:
- user / superuser — basta un SET en la sesión (work_mem, effective_cache_size).
- sighup — hace falta recargar (checkpoint_timeout, max_wal_size, casi todo el autovacuum).
- postmaster — hace falta reiniciar el servidor. En este servidor son 65 de los 378, y entre ellos shared_buffers, max_connections, wal_level, shared_preload_libraries y autovacuum_max_workers.

ALTER SYSTEM SET max_wal_size = '4GB';
SELECT pg_reload_conf();

-- ¿Quedó algo esperando un reinicio?
SELECT name, setting FROM pg_settings WHERE pending_restart;

Detalle medido que ahorra un susto: pending_restart no se enciende en el mismo instante de la recarga. Justo después seguía en false, y medio segundo más tarde ya decía true. Se consulta después, no en la misma frase.

shared_buffers y effective_cache_size no son lo mismo, y uno de los dos no reserva nada
- shared_buffers es memoria de verdad: el caché propio del servidor. De fábrica son 128 MB, que es poco para cualquier servidor dedicado; la regla habitual es un 25 % de la RAM.
- effective_cache_size no reserva nada. Es lo que el planificador supone que hay entre el caché de PostgreSQL y el del sistema operativo, y sólo sirve para decidir si un índice sale a cuenta. Cambiarlo no mueve un byte: cambia los planes.

Confundirlos lleva a subir effective_cache_size esperando más caché, o a subir shared_buffers esperando otro plan.

max_connections no se sube: se pone un pool delante
Vale 100 de fábrica, y aquí cada conexión es un proceso del sistema operativo, no un hilo. Subirlo a mil no es un número mayor: son mil procesos, con su memoria y su work_mem por operación. La respuesta es un agrupador de conexiones (pgBouncer y similares). Y es de los que exigen reinicio.

WAL y puntos de control
max_wal_size (1 GB de fábrica) y checkpoint_timeout (5 min) deciden cada cuánto se vuelca todo a disco. Si los puntos de control se disparan por tamaño en vez de por tiempo, el servidor escribe a golpes; se ve encendiendo log_checkpoints y se corrige subiendo max_wal_size. checkpoint_completion_target viene ya en 0,9, que es lo que reparte esa escritura en el tiempo en vez de concentrarla.

Autovacuum
Los de aquí valen autovacuum_naptime 60 s, autovacuum_max_workers 3 —éste exige reinicio— y autovacuum_vacuum_scale_factor 0,2, o sea que una tabla se limpia cuando ha cambiado el 20 % de sus filas. En una tabla de mil millones de filas eso es esperar a doscientos millones de versiones muertas, así que las tablas grandes llevan su propio ajuste:

ALTER TABLE eventos SET (autovacuum_vacuum_scale_factor = 0.01);

synchronous_commit, el único que cambia la promesa
Apagarlo hace que el COMMIT no espere a que el WAL llegue al disco: se gana latencia y se arriesgan las últimas transacciones ante un corte eléctrico —no la integridad de la base, sólo lo último confirmado—. Es user, así que puede apagarse sólo donde se acepta ese trato:

SET synchronous_commit = off;

Recomendación
Tocar pocos, uno a uno y midiendo. ALTER SYSTEM en vez de editar archivos —queda registrado y se deshace con ALTER SYSTEM RESET—, y por base o por rol antes que global: un ajuste que sólo necesita el informe nocturno no tiene por qué pagarlo el resto del día.

Palabras clave: configuración, postgresql.conf, postgresql.auto.conf, alter system, pg_settings, pending_restart, pg_reload_conf, shared_buffers, effective_cache_size, work_mem, max_connections, pool, wal, checkpoint, autovacuum, synchronous_commit

Por qué la cuenta no lleva el host dentro, roles que son usuarios y grupos, la trampa de que conceder no alcanza a las tablas de mañana, y por qué la seguridad de fila puede estar encendida y sin efecto.

Aplica a: PostgreSQL 13+

La diferencia de fondo con MySQL es que aquí la cuenta no lleva el host dentro. No existe ana@192.168.1.%: existe el rol ana, y desde dónde puede entrar y cómo se autentica lo decide un archivo aparte, pg_hba.conf.

pg_hba.conf: la primera línea que casa, gana
Se lee de arriba abajo y no sigue buscando. Y no hace falta abrirlo para verlo:

SELECT type, database, user_name, address, auth_method, error
  FROM pg_hba_file_rules
 ORDER BY rule_number;

La columna error dice si una línea está mal escrita —eso es lo que evita el reinicio en el que el servidor no arranca—. Los cambios entran con SELECT pg_reload_conf(), sin reiniciar. En el servidor de la prueba había siete reglas, las de 127.0.0.1 con trust y la última, para todo lo demás, con scram-sha-256: el orden es la política.

Un rol es usuario y grupo a la vez
No hay dos conceptos: CREATE USER es exactamente CREATE ROLE … LOGIN. Lo que distingue a una persona de un grupo es el atributo LOGIN, y nada más.

CREATE ROLE app_ro;                       -- sin LOGIN: hace de grupo
CREATE ROLE ana LOGIN PASSWORD 'secreta';
GRANT app_ro TO ana;                      -- ana hereda lo de app_ro

Los roles heredan por omisión, así que ana usa los privilegios de app_ro sin hacer nada. Con NOINHERIT hay que pedirlos con SET ROLE, que es lo que se usa cuando se quiere que el paso quede explícito.

Las contraseñas se guardan con scram-sha-256, que es el valor por omisión desde PostgreSQL 14 —el de la prueba lo confirma—; md5 sigue existiendo y no debería.

La trampa de verdad: conceder no alcanza al futuro
GRANT … ON ALL TABLES IN SCHEMA concede sobre las tablas que hay hoy. Medido: tras la concesión, app_ro podía leer la tabla existente y no la creada un minuto después. Lo que cubre el futuro es otra sentencia:

GRANT USAGE ON SCHEMA public TO app_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_ro;          -- las de hoy
ALTER DEFAULT PRIVILEGES IN SCHEMA public
      GRANT SELECT ON TABLES TO app_ro;                          -- las de mañana

Y con letra pequeña: los privilegios por omisión son de quien los declara, no del esquema, así que si las tablas las crea otro rol hay que declararlos también con FOR ROLE. Lo que quedó declarado se ve en pg_default_acl.

Además, conceder deja rastro que hay que deshacer: tras un ON ALL TABLES, borrar el rol falla con DependentObjectsStillExist y la lista de tablas donde quedó un privilegio —en la prueba salieron incluso las de PostGIS—. La contraria es REVOKE, o DROP OWNED BY rol antes del DROP ROLE.

El esquema public ya no es de todos
Desde PostgreSQL 15, PUBLIC conserva USAGE sobre el esquema public pero ya no tiene CREATE. Medido en 17.6: un rol recién creado da USAGE = true y CREATE = false. Quien traiga scripts de una versión anterior verá fallar el primer CREATE TABLE de un usuario que antes podía.

Roles predefinidos: vigilar sin ser superusuario
El servidor trae quince roles listos. Los que evitan un superusuario de más:
- pg_read_all_data, pg_write_all_data — leer o escribir todo, sin más poderes.
- pg_monitor — ver las vistas de estadísticas completas; incluye pg_read_all_stats y pg_read_all_settings.
- pg_signal_backend — cancelar consultas y cerrar sesiones ajenas.
- pg_maintain (PostgreSQL 16+) — VACUUM, ANALYZE, REINDEX sin ser dueño.

Seguridad a nivel de fila
Una política filtra las filas que cada rol ve, dentro de la misma tabla:

ALTER TABLE pedidos ENABLE ROW LEVEL SECURITY;

CREATE POLICY solo_lo_mio ON pedidos
    FOR SELECT USING (dueno = current_user);

Aquí está lo que hay que medir antes de confiarse. Con la misma política y la misma tabla:

- El rol al que apunta la política vio una fila. Correcto.
- El dueño de la tabla —sin ser superusuario— vio las dos: el dueño no está sujeto a sus propias políticas hasta que se declara ALTER TABLE … FORCE ROW LEVEL SECURITY. Con FORCE, pasó a ver una.
- El superusuario vio las dos incluso con FORCE. Los superusuarios y los roles con BYPASSRLS se saltan las políticas siempre.

O sea: una aplicación que se conecta como dueño de las tablas —y más aún como superusuario— tiene la seguridad de fila encendida y sin efecto. La comprobación es conectarse con el rol real y contar filas.

Recomendación
Un rol por aplicación, sin LOGIN para los grupos, y ninguno con SUPERUSER salvo el de administración. ALTER DEFAULT PRIVILEGES en el mismo commit que el GRANT, o el permiso durará hasta la siguiente tabla. Y lo que se conceda en bloque se apunta, porque el DROP ROLE de dentro de un año lo va a pedir.

Palabras clave: seguridad, rol, role, usuario, grupo, pg_hba.conf, pg_hba_file_rules, scram-sha-256, grant, revoke, alter default privileges, pg_default_acl, public, pg_read_all_data, pg_monitor, rls, row level security, create policy, bypassrls, drop owned by

Lógico frente a físico y para qué sirve cada uno, lo que pg_dump deja fuera y deja la base sin nadie que entre, por qué el PITR no funciona de fábrica y qué ranura puede llenarte el disco.

Aplica a: PostgreSQL 13+

Hay dos clases de respaldo y no sirven para lo mismo. Elegir mal se descubre el día de la restauración.

Respaldo lógico

-- en la línea de órdenes, no en el editor SQL:
-- pg_dump -d demo -Fc -f demo.dump
-- pg_restore -d demo_nueva -j 4 demo.dump

El formato -Fc (custom) es el que conviene por omisión: sobre la misma base, el volcado en texto ocupó 3,1 MB y el custom 905 kB, y además viene con índice —pg_restore -l listó los treinta bloques de datos— así que permite restaurar una tabla y hacerlo en paralelo con -j.

Lo que pg_dump no lleva, y es lo que muerde
Los roles y los ajustes globales no están dentro. Medido: el volcado de la base no tenía ni un CREATE ROLE, mientras que pg_dumpall --globals-only produjo los dos que había. Restaurar sólo el volcado deja una base perfecta a la que no puede entrar nadie. Un respaldo lógico completo son dos archivos:
- pg_dumpall --globals-only — roles, contraseñas y permisos del clúster.
- pg_dump de cada base.

pg_dump es consistente —trabaja sobre una instantánea— y no bloquea a quien escribe; pero toma un bloqueo ACCESS SHARE, así que un ALTER TABLE lanzado a la vez se pone a esperar, y detrás de él se hace cola.

Respaldo físico
pg_basebackup copia el clúster entero. Medido en el servidor de prueba: 84 MB en 1,3 s, con -X stream, que se trae también el WAL generado durante la copia —sin eso, la copia no es restaurable—. Deja un backup_label que dice desde qué punto del WAL hay que reproducir:

START WAL LOCATION: 0/13000028 (file 000000010000000000000013)

PITR: recuperar hasta un instante
Es la razón de ser del respaldo físico, y no funciona de fábrica: archive_mode viene apagado, medido en este servidor. Sin archivado, un respaldo físico restaura exactamente el momento en que se tomó y ni un segundo más.

Para tenerlo hacen falta tres piezas:
1. archive_mode = on y un archive_command que copie cada segmento del WAL a un sitio seguro (o pg_receivewal desde otra máquina).
2. Un pg_basebackup periódico.
3. Al restaurar: los archivos del respaldo, un restore_command que traiga los segmentos, recovery_target_time = '…' y un archivo vacío recovery.signal en el directorio de datos.

Ese último punto es el que descoloca a quien viene de versiones viejas: desde PostgreSQL 12 no existe recovery.conf; los parámetros van en postgresql.conf y lo que declara «esto es una recuperación» es el archivo señal.

Las ranuras de replicación son un cuchillo de dos filos
Una ranura garantiza que el servidor no borra WAL que un consumidor no haya leído. Si el consumidor desaparece y la ranura queda, el WAL se acumula hasta llenar el disco — y un disco lleno es una parada, no un aviso. Se vigilan así:

SELECT slot_name, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retenido
  FROM pg_replication_slots;

max_slot_wal_keep_size pone el tope: pasado ése, el servidor prefiere invalidar la ranura antes que quedarse sin disco.

El respaldo de Calíope es lógico
Lo que genera la herramienta de respaldos es SQL —CREATE e INSERT—, de la familia de pg_dump, no de pg_basebackup. Sirve para migrar y para recuperar datos; para recuperar un servidor entero a un instante hace falta lo de arriba, que es cosa del sistema operativo y no de un cliente.

Recomendación
Los dos archivos del respaldo lógico, siempre juntos —--globals-only y el volcado— y probar la restauración, no el respaldo: un archivo que se genera sin error puede no restaurar, y eso sólo se sabe restaurándolo en un servidor de verdad.

Palabras clave: respaldo, backup, pg_dump, pg_restore, pg_dumpall, globals, pg_basebackup, wal, archive_mode, archive_command, pitr, recovery_target_time, recovery.signal, ranura, replication slot, wal_status, restauración

El DDL es transaccional, lo que cuesta es el bloqueo y no el ALTER, qué cambios reescriben la tabla entera, y el patrón NOT VALID + VALIDATE que evita parar la base.

Aplica a: PostgreSQL 13+

Aquí el DDL es transaccional. Eso cambia cómo se escriben las migraciones y es lo primero que hay que asimilar viniendo de MySQL, donde cada ALTER confirma por su cuenta.

BEGIN;
ALTER TABLE pedidos ADD COLUMN moneda text;
CREATE INDEX idx_moneda ON pedidos (moneda);
CREATE TABLE monedas (codigo text PRIMARY KEY);
ROLLBACK;

Comprobado: tras ese ROLLBACK no quedaba ninguna de las tres cosas. Una migración que falla a la mitad no deja media base; y por eso el patrón sano es meter la migración entera en una transacción.

Las excepciones se cuentan con los dedos: CREATE INDEX CONCURRENTLY, VACUUM y ALTER SYSTEM no pueden ir dentro de una transacción.

Lo que cuesta no es el ALTER: es el bloqueo
Casi todo ALTER TABLE toma ACCESS EXCLUSIVE, que choca hasta con un SELECT. Aunque el cambio dure un milisegundo, esperar el bloqueo puede durar horas —y mientras espera, todo lo que llegue detrás se pone a la cola—. Por eso el DDL en producción se lanza siempre así:

SET lock_timeout = '3s';
ALTER TABLE pedidos ADD COLUMN moneda text;

Si no consigue el bloqueo, falla en tres segundos y se reintenta. Sin eso, una migración de un milisegundo puede parar la aplicación entera.

Qué reescribe la tabla y qué no
Reescribir significa copiar la tabla entera: tarda con el tamaño y necesita el doble de disco mientras dura. Medido sobre 500 000 filas y 32 MB:

La regla que resume la tabla: ensanchar es gratis y estrechar reescribe. Y ADD COLUMN con valor por omisión dejó de reescribir en PostgreSQL 11: ya no hace falta el rodeo de añadir la columna vacía y rellenarla por lotes.

Dos avisos que no se ven en los tiempos:
- Un DROP COLUMN es instantáneo porque sólo marca la columna como borrada: el espacio no vuelve hasta que la tabla se reescriba.
- SET NOT NULL y un CHECK normal no reescriben, pero recorren la tabla entera con el bloqueo puesto. En una tabla grande eso ya es una parada.

El patrón para no parar la base: NOT VALID y luego VALIDATE
Una restricción se puede añadir en dos tiempos: primero se declara sin comprobar lo que ya hay —instantáneo—, y después se valida, que es lo que tarda pero con un bloqueo mucho más débil.

ALTER TABLE ddl_hija
    ADD CONSTRAINT fk_p FOREIGN KEY (padre) REFERENCES ddl_demo (id) NOT VALID;

ALTER TABLE ddl_hija VALIDATE CONSTRAINT fk_p;

Medido: la declaración NOT VALID tardó 0,7 ms con SHARE ROW EXCLUSIVE —que deja leer—, y la validación 67 ms con SHARE UPDATE EXCLUSIVE, que ni siquiera bloquea a quien escribe. Hacerlo de una vez costó lo mismo en tiempo, pero con el bloqueo fuerte puesto todo el rato. En una tabla de verdad, esa diferencia es la que separa un despliegue de una caída.

Desde que la restricción está NOT VALID, el servidor la aplica a las filas nuevas; lo único que queda pendiente es comprobar las viejas.

Índices
CREATE INDEX normal para las escrituras; CREATE INDEX CONCURRENTLY no para nada, pero no cabe dentro de la transacción de la migración, así que va en su propio paso y se comprueba después (pg_index.indisvalid).

Recomendación
lock_timeout siempre; migración dentro de una transacción salvo lo que no puede; NOT VALID + VALIDATE para restricciones sobre tablas grandes; y cuidado con los cambios de tipo, que es donde se esconde la reescritura. Si hay que estrechar un tipo, casi siempre es mejor añadir la columna nueva, copiar por lotes y renombrar.

Palabras clave: ddl, alter table, migración, transaccional, rollback, lock_timeout, access exclusive, reescritura, relfilenode, add column, drop column, set not null, not valid, validate constraint, create index concurrently

Lo que no existe y da error de sintaxis, por qué text no es peor que varchar, numeric frente a coma flotante, qué guarda de verdad timestamptz y por qué jsonb no ahorra espacio.

Aplica a: PostgreSQL 13+

Los tipos son de las pocas cosas donde la migración desde MySQL falla al primer intento, y menos mal: lo que no existe da error de sintaxis en vez de aceptarse a medias.

Lo que no existe aquí
- UNSIGNED — 42601 syntax error at or near "unsigned". No hay enteros sin signo; se usa el tipo siguiente o un CHECK (n >= 0).
- INT(11) — también 42601. El ancho de visualización de MySQL no existe, y nunca significó lo que parecía.
- TINYINT, DATETIME, DOUBLE con paréntesis, y los tipos SET/ENUM de MySQL. Sus equivalentes son smallint, timestamptz, double precision y un tipo enum propio.

Texto: usa text y ya está
Medido, con el mismo valor 'hola': text ocupó 5 bytes, varchar(50) 5 y char(50) 51. Los tres se guardan igual; varchar(n) sólo añade una comprobación de longitud y char(n) rellena con espacios. Y esos espacios cambian las comparaciones: 'x' = 'x ' es falso en text y verdadero en char.

Aquí text no es peor que varchar: no hay penalización. Se pone varchar(n) cuando el límite es una regla de negocio, y char(n) prácticamente nunca.

Números: numeric para el dinero, y no es una superstición

SELECT (0.1::float8 + 0.2::float8) = 0.3::float8,   -- false
       (0.1::numeric + 0.2::numeric) = 0.3::numeric; -- true

Medido: en coma flotante, 0.1 * 3 dio 0.30000000000000004; en numeric, 0.3 exacto. numeric es exacto y de precisión arbitraria, y se paga en espacio y en velocidad —10 bytes frente a los 8 de float8 para ese valor, y aritmética por software—. Para dinero y para cualquier cifra que se sume delante de un cliente, numeric.

Tamaños medidos: int 4, bigint 8, boolean 1, uuid 16 —frente a los 36 que ocuparía como texto—.

Fechas: timestamptz casi siempre
timestamp y timestamptz ocupan los mismos 8 bytes. La diferencia no es el tamaño ni que uno guarde la zona: ninguno guarda la zona. timestamptz guarda un instante —convierte a UTC al entrar y a la zona de la sesión al salir—, y timestamp guarda una lectura de reloj sin más.

Medido, el mismo instante con dos zonas de sesión:

Es el mismo momento dicho de dos maneras. Con timestamp no hay conversión ninguna: lo que se guardó es lo que sale, y quien tenga que saber a qué hora fue de verdad no puede averiguarlo. date ocupa 4 bytes e interval 16.

json frente a jsonb: casi siempre jsonb, y no por tamaño

SELECT '{"b":1,"a":2,"a":3}'::json::text,   -- {"b":1,"a":2,"a":3}
       '{"b":1,"a":2,"a":3}'::jsonb::text;  -- {"a": 3, "b": 1}

json guarda el texto tal cual: conserva el orden, los espacios y hasta las claves repetidas. jsonb guarda un árbol ya analizado: ordena las claves, se queda con la última repetida y normaliza los espacios. Por eso jsonb se consulta rápido y se indexa con GIN, y json sólo sirve cuando hay que devolver el documento byte a byte como llegó.

Lo que no es cierto es que jsonb ahorre espacio: medido sobre 200 000 documentos iguales, json ocupó 14 MB y jsonb 16 MB. Se elige jsonb por cómo se consulta, no por lo que pesa.

Arrays
Un array es un tipo de primera clase, con sus operadores —@> para contención, array_length— y su índice GIN. Es cómodo para etiquetas y para listas cortas; deja de serlo en cuanto los elementos necesitan atributos propios o hay que unirlos con otra tabla. Un array no es una tabla que se ahorra: es un valor.

serial o IDENTITY
serial no es un tipo: es azúcar que crea una secuencia y pone su nextval como valor por omisión. GENERATED ALWAYS AS IDENTITY es lo estándar y además protege la columna: al intentar insertar un valor a mano contestó 428C9 cannot insert a non-DEFAULT value into column. Para tablas nuevas, IDENTITY.

Recomendación
text para el texto, numeric para el dinero, timestamptz para los instantes, jsonb para los documentos que se consultan y IDENTITY para las claves. Y al migrar desde MySQL, dejar que el error de sintaxis haga su trabajo: es preferible a un tipo que se acepta y significa otra cosa.

Palabras clave: tipos, text, varchar, char, numeric, float, decimal, timestamptz, timestamp, zona horaria, json, jsonb, array, uuid, serial, identity, unsigned, enum

Los códigos que se ven a diario, por qué se programa por clase y no por código, qué dicen el DETAIL y el HINT que casi nadie enseña, y dónde está el código cuando ni siquiera se abre la conexión.

Aplica a: PostgreSQL 13+

Aquí no hay números de error. Hay SQLSTATE: cinco caracteres, de los que los dos primeros son la clase. Y la clase es lo que se programa: dice qué hacer sin saber qué falló exactamente.

Los que se ven todos los días

Las clases, que es lo que hay que mirar

La consecuencia práctica: una aplicación reintenta la clase 40 y no reintenta la 42. Y si el reintento no distingue, o se pierde una transacción legítima o se repite mil veces una consulta que nunca va a funcionar.

El mensaje tiene tres partes, y la tercera es la útil
MESSAGE dice qué pasó, DETAIL da la fila o el valor, y HINT dice qué hacer. Medidos:

- 23505 — MESSAGE: duplicate key value violates unique constraint "er_d_pkey"; DETAIL: Key (id)=(1) already exists.
- 42883 — MESSAGE: operator does not exist: text = integer; HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
- 42703 — MESSAGE: column "ids" does not exist; HINT: Perhaps you meant to reference the column "er_d.id".

Un cliente que sólo enseñe el MESSAGE está tirando la mitad de la información — y justo la parte que dice cómo salir. Calíope compone las tres.

Además, el error trae campos aparte: la tabla, la columna y el nombre de la restricción. Con 23505 llegó constraint = er_d_pkey, que es lo que permite traducirlo a «ese correo ya está registrado» sin analizar el texto del mensaje.

Un error aborta la transacción
Tras cualquier error dentro de un BEGIN, todo lo siguiente contesta 25P02 hasta que se haga ROLLBACK. No es un fallo del cliente: es el diseño, y la salida elegante son los puntos de guardado.

Los errores de conexión no llegan en la respuesta
Si el rol o la base no existen, o la contraseña es incorrecta, la conexión ni siquiera se abre: el cliente sólo ve «connection failed». El código está en el registro del servidor, y sólo si se le pide:

ALTER SYSTEM SET log_error_verbosity = 'verbose';
SELECT pg_reload_conf();

Con eso, el registro pasó de FATAL: database "no_existe" does not exist a FATAL: 3D000: database "no_existe" does not exist —y 28000 para el rol inexistente—. Es la diferencia entre adivinar y saber cuando alguien reporta que «no puede conectarse».

Recomendación
En el código de la aplicación, ramificar por clase y usar el código completo sólo para los mensajes que ve el usuario (23505 → «ya existe»). Guardar siempre el SQLSTATE en el registro propio: el texto del mensaje cambia con el idioma del servidor, y el código no.

Palabras clave: error, sqlstate, código, clase, 23505, 23503, 42p01, 42601, 42883, 42501, 25p02, 40001, 40p01, 55p03, 57014, 3d000, 28000, detail, hint, reintento, log_error_verbosity

Particionado declarativo y qué poda de verdad el planificador, por qué no hay índices globales ni unicidad sobre una sola columna, el CHECK que convierte un ATTACH de 68 ms en medio, y qué cuesta la partición por omisión.

Aplica a: PostgreSQL 13+

El particionado de aquí es declarativo: se declara la clave y cada partición es una tabla de verdad. El padre no guarda ni una fila —medido: 0 bytes, con los datos repartidos entre las hijas—.

CREATE TABLE pt (id bigserial, creado date NOT NULL, importe numeric)
    PARTITION BY RANGE (creado);

CREATE TABLE pt_2024 PARTITION OF pt
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE pt_resto PARTITION OF pt DEFAULT;

Hay tres formas: RANGE (fechas, importes), LIST (país, estado) y HASH (repartir por repartir).

La poda es lo que se busca
Medido sobre 300 000 filas en tres años: una consulta con WHERE creado BETWEEN '2024-03-01' AND '2024-03-31' recorrió sólo pt_2024. La misma tabla, filtrando por una columna que no es la clave, hizo un Parallel Append sobre todas.

Ahí está la regla que decide el diseño: la clave de partición es la columna por la que filtras casi siempre. Si las consultas no la mencionan, el particionado no ahorra lectura: la reparte.

Lo que no hay: índices globales
Un índice creado sobre el padre crea uno por partición —medido: cuatro particiones, cuatro índices—. No existe un índice único que abarque toda la tabla, y de ahí sale la limitación que hay que conocer antes de diseñar:

ALTER TABLE pt ADD CONSTRAINT pt_uni UNIQUE (id, creado);

Una UNIQUE sobre (id) solo se rechaza con 0A000 unique constraint on partitioned table must include all partitioning columns. La unicidad global de un identificador no se puede garantizar con particionado declarativo; se consigue con una secuencia, que es única por construcción, no por restricción.

ATTACH: la diferencia entre 68 ms y medio
Enganchar una tabla existente obliga al servidor a comprobar que todas sus filas caben en el rango. Medido sobre 300 000 filas:

O sea: si la tabla lleva ya una restricción CHECK que implica el rango, el servidor se salta el recorrido. En una tabla de mil millones de filas eso es la diferencia entre un ACCESS EXCLUSIVE de un instante y uno de media hora.

La partición por omisión no es gratis
DEFAULT recoge lo que no cae en ningún rango, y evita el error al insertar una fecha inesperada. A cambio, medido: ALTER TABLE … DETACH PARTITION … CONCURRENTLY contestó 55000 cannot detach partitions concurrently when a default partition exists. Y además, cada ATTACH nuevo tiene que recorrer la partición por omisión para comprobar que no esconde filas del rango que llega.

Por qué se particiona de verdad
No es por velocidad de consulta —para eso está el índice—, es por mantenimiento:
- Retirar un periodo entero es DROP TABLE de su partición: medido, 2,3 ms, y devuelve el espacio al sistema de archivos. El DELETE equivalente tardó más y, sobre todo, deja filas muertas que VACUUM tendrá que limpiar y espacio que no vuelve.
- VACUUM y ANALYZE trabajan por partición, así que el trabajo de mantenimiento deja de crecer con el histórico entero.
- Los datos viejos se pueden desenganchar y archivar sin tocar la tabla viva.

Recomendación
Particiona por lo que vas a borrar, no por lo que vas a consultar; y comprueba que tus consultas llevan la clave en el WHERE mirando el plan, no suponiéndolo. Antes de particionar una tabla que ya existe, mira si lo que te sobra es un índice: el particionado añade partes móviles a cambio de un mantenimiento más barato, y esa cuenta sólo sale a partir de cierto tamaño.

Palabras clave: particionado, partition, range, list, hash, poda, pruning, attach, detach, default, índice global, unique, 0a000, drop partition, mantenimiento

Por qué aquí no existe la trampa de utf8, en qué se distinguen codificación y colación, cómo cambia el orden con cada una, y por qué un LIKE por prefijo no usa tu índice.

Aplica a: PostgreSQL 13+

Aquí no existe la trampa que costó tantas migraciones en MySQL: no hay un utf8 que no era UTF-8. La codificación se declara al crear la base y UTF8 es UTF-8 entero.

Medido, guardando cuatro cadenas en una columna text normal:

No hizo falta declarar nada especial. length() cuenta caracteres y octet_length() cuenta bytes, que es la distinción que en MySQL había que ir persiguiendo tipo a tipo.

Codificación y colación son dos cosas distintas
- Codificación — cómo se guardan los bytes. Es de la base, se fija al crearla y no se cambia después: para cambiarla hay que volcar y recrear.
- Colación — cómo se ordenan y se comparan. Se puede fijar por base, por columna, por expresión y hasta en un ORDER BY.

En el servidor de prueba: server_encoding = UTF8, y las bases con en_US.utf8 de proveedor libc. Hay 815 colaciones disponibles, de dos proveedores: las del sistema (C, POSIX, en_US.utf8) y las de ICU (es-ES-x-icu, unicode), que desde PostgreSQL 15 pueden ser incluso el proveedor por omisión de una base.

Lo que cambia con la colación

SELECT array(SELECT s FROM (VALUES ('a'),('B'),('á'),('b'),('A')) v(s) ORDER BY s COLLATE "C"),
       array(SELECT s FROM (VALUES ('a'),('B'),('á'),('b'),('A')) v(s) ORDER BY s COLLATE "es-ES-x-icu");

Medido, el resultado no se parece:

- C — A, B, a, b, á. Ordena por el número del carácter: todas las mayúsculas antes que las minúsculas, y los acentos al final.
- es-ES-x-icu — a, A, á, b, B. Ordena como un diccionario.

Y la comparación cambia con ella: 'a' < 'B' es falso con C y verdadero con la colación española. Una lista que «sale mal ordenada» casi nunca es un fallo de la aplicación: es la colación de la columna.

La colación decide si un índice sirve para LIKE
Éste es el detalle práctico que más cuesta descubrir solo. Con una colación lingüística —la de la base—, un índice B-tree normal no sirve para buscar por prefijo. Medido sobre 200 000 filas:

- WHERE s = 'usuario42' → Index Only Scan.
- WHERE s LIKE 'usuario42%' → Seq Scan, con el índice ahí puesto.
- Tras crear el índice con la clase de operadores adecuada, la misma consulta pasó a Bitmap Index Scan.

CREATE INDEX idx_prefijo ON ch_like (s text_pattern_ops);

text_pattern_ops compara byte a byte, que es exactamente lo que necesita LIKE 'algo%'. Con la colación C en la columna no hace falta, porque ya compara así.

La colación tiene versión, y eso importa
La base guarda la versión de la colación con la que se construyeron sus índices —medido: datcollversion = 2.36, que es la de la biblioteca del sistema—. Si el sistema operativo se actualiza y esa versión cambia, el orden puede cambiar, y un índice construido con el orden anterior deja de ser correcto: búsquedas que no encuentran filas que sí están. PostgreSQL avisa de la discrepancia, y la respuesta es REINDEX.

Es la razón por la que mucha gente elige colación C o ICU para las bases que tienen que sobrevivir a actualizaciones del sistema: ICU trae su propia versión y no depende de la del sistema.

Recomendación
UTF8 siempre. La colación se decide al crear la base, porque después sale caro: C para columnas que son códigos, identificadores o rutas —ordena rápido y va bien con LIKE—, y una colación lingüística para lo que lee una persona. Y si una consulta con LIKE 'x%' no usa el índice, mira la colación antes de tocar la consulta.

Palabras clave: codificación, encoding, utf8, colación, collation, collate, icu, libc, text_pattern_ops, like, prefijo, order by, datcollversion, reindex, octet_length

Los que muerden de verdad —63 bytes de nombre, 1 600 columnas, 32 por índice—, por qué el del identificador no da error sino aviso, y qué hace TOAST con un valor que no cabe en la página.

Aplica a: PostgreSQL 13+

Los límites de PostgreSQL no se parecen a los de InnoDB, y los que muerden a diario no son los grandes.

Los que se tocan de verdad

Los tres primeros están medidos: 1 600 columnas se crearon sin problema y 1 601 fallaron; un índice de 32 columnas se creó y el de 33 no.

El del identificador es el único que no da error
Un nombre de 72 caracteres se guardó como uno de 63, y el servidor lo dijo con un aviso:

identifier "t_aaa…" will be truncated to "t_aaa…"

Un aviso no es un error: la sentencia siguió adelante. Por eso dos nombres largos que sólo se diferencian a partir del carácter 64 acaban siendo el mismo objeto, y el fallo se ve mucho más tarde. Los generadores de nombres —índices, restricciones, tablas temporales por lote— son los que se topan con esto, y no la mano de nadie.

Los grandes, que casi nunca son el problema
No están medidos aquí —hacerlo pediría llenar un disco—, y van con su cifra oficial:

- Tamaño máximo de una tabla: 32 TB.
- Tamaño máximo de un campo: 1 GB.
- Tamaño máximo de una fila: 1,6 TB.
- Filas por tabla: sin límite definido.
- Bases de datos por clúster y tablas por base: sin límite práctico.

Lo que se agota mucho antes que cualquiera de éstos es el mantenimiento: VACUUM, respaldos y reconstrucción de índices sobre una tabla de terabytes.

TOAST: por qué un text de 1 GB no rompe la página de 8 kB
Una fila tiene que caber en una página, y una página son 8 kB. Los valores grandes se comprimen y se sacan a una tabla lateral, que es lo que llaman TOAST, de forma automática y sin declarar nada.

Medido, guardando 100 000 bytes de texto en una columna text:

- octet_length — 100 000 bytes de dato.
- pg_column_size — 1 156 bytes, o sea que se comprimió.
- La tabla ocupó 8 192 bytes y 16 kB contando su TOAST.

De ahí sale una consecuencia práctica: un SELECT * sobre una tabla con columnas grandes paga la lectura de esas columnas aunque no las mire nadie. Pedir sólo las columnas necesarias no es estilo, es E/S.

Los límites que sí se configuran
No son del motor, son de la instancia, y por eso se ven en pg_settings: max_connections (100 de fábrica), max_locks_per_transaction (64), max_wal_size, work_mem. Ésos son los que se agotan en un servidor real; los de la tabla de arriba, casi nunca.

Recomendación
Vigilar sólo dos: el de 63 bytes cuando algo genera nombres, y el de columnas por índice cuando alguien propone un índice compuesto con medio esquema dentro. Del resto se entera uno con pg_settings, no con la documentación.

Palabras clave: límites, identificador, 63 bytes, truncado, 1600 columnas, 32 columnas, índice, toast, página, 8 kB, 32 tb, 1 gb, pg_settings, max_connections

varchar frente a nvarchar, el prefijo N, las colaciones _UTF8, qué hace cada sufijo, el conflicto de colación y por qué un parámetro N'…' puede dejar sin uso tu índice.

Aplica a: SQL Server 2022+

Si vienes de MySQL, aquí no hay utf8mb4 ni SET NAMES. En SQL Server la codificación de un texto no se declara aparte: la decide su colación, y hay dos familias de tipos de texto.

varchar y nvarchar son dos codificaciones
- nvarchar (y nchar) guarda UTF-16: cualquier carácter Unicode, sea cual sea la colación.
- varchar (y char) guarda los bytes de la página de códigos de su colación. Con una colación clásica, como Latin1_General_CI_AS, es Windows-1252, y lo que no está en ella se guarda como ?.
- Con una colación terminada en _UTF8 (desde SQL Server 2019), varchar guarda UTF-8.

Medido en el servidor de prueba (SQL Server 2025), con la base en Latin1_General_100_CI_AS_SC_UTF8:

LEN() cuenta caracteres y DATALENGTH() cuenta bytes. La n de varchar(n) son bytes, y la de nvarchar(n), pares de bytes, no caracteres. Medido: un varchar(10) en UTF-8 admite cinco ñ, y con diez da Msg 2628 («String or binary data would be truncated»); un nvarchar(10) admite diez ñ pero no seis emojis.

El prefijo N
Un literal sin N delante es varchar, y se convierte a la página de códigos de la base antes de llegar a ninguna columna. Medido en una base Latin1_General_CI_AS:

- 'Zúrich ✓' → Zúrich ?
- N'Zúrich ✓' → Zúrich ✓

Y en una columna varchar de esa base, aunque el literal lleve N, Zúrich ✓ 日本 se guardó como Zúrich ? ??. El dato se pierde al escribir, sin error ni aviso, y cambiar después la colación de la columna no lo recupera: lo guardado ya son signos de interrogación.

Qué dice cada sufijo

SELECT CASE WHEN 'a' = 'A' COLLATE Latin1_General_100_CI_AS THEN 1 ELSE 0 END AS ci,
       CASE WHEN 'a' = 'A' COLLATE Latin1_General_100_CS_AS THEN 1 ELSE 0 END AS cs,
       CASE WHEN 'e' = 'é' COLLATE Latin1_General_100_CI_AI THEN 1 ELSE 0 END AS ai,
       CASE WHEN 'e' = 'é' COLLATE Latin1_General_100_CI_AS THEN 1 ELSE 0 END AS acc;

Medido: 1, 0, 1, 0.

- _CI / _CS — no distingue / distingue mayúsculas.
- _AI / _AS — no distingue / distingue acentos.
- _SC — cuenta como un carácter los que están fuera del plano básico, como un emoji. Medido: LEN(N'😀') da 1 con _SC y 2 sin él, y sin _SC un LIKE N'_' no lo encuentra.
- _UTF8 — varchar en UTF-8.
- _BIN2 — compara por punto de código: ordenó A, B, a, b, n, o, á, ñ, cuando Latin1_General_100_CI_AS y Modern_Spanish_100_CI_AS dieron A, a, á, b, B, n, ñ, o.
- El 100 es la versión de las tablas de ordenación; las que no lo llevan (Latin1_General_CI_AS) son más antiguas, y las que empiezan por SQL_ son las heredadas de SQL Server 2000.

El servidor de prueba ofrece 5540 colaciones, 1585 de ellas _UTF8 (sys.fn_helpcollations()).

Servidor, base, columna y expresión

SELECT SERVERPROPERTY('Collation'), DATABASEPROPERTYEX('demo', 'Collation');

La colación se fija en cuatro niveles, y cada uno toma la del nivel de arriba al crearse:

- Servidor — la de master y tempdb, elegida al instalar.
- Base — CREATE DATABASE … COLLATE ….
- Columna — nombre varchar(40) COLLATE Latin1_General_100_CS_AS.
- Expresión — WHERE a = b COLLATE Latin1_General_100_CI_AS, o dentro de un ORDER BY.

ALTER DATABASE … COLLATE no toca las columnas que ya existen: medido, la base pasó a _UTF8 y sus columnas siguieron en Latin1_General_CI_AS. Cada columna se cambia con su ALTER TABLE … ALTER COLUMN.

El conflicto de colación
Dos textos de colaciones distintas no se comparan solos:

- WHERE a = b → Msg 468, «Cannot resolve the collation conflict».
- Un UNION ALL de las dos columnas → Msg 457; con textos del catálogo (sys.* va en Latin1_General_CI_AS) → Msg 451.
- Se resuelve poniendo COLLATE en un lado.

El caso más frecuente son las tablas temporales: #tmp vive en tempdb y nace con la colación del servidor, no con la de tu base. Medido en una base Latin1_General_100_CI_AS sobre un servidor _UTF8: un JOIN con #tmp dio Msg 468. La salida es declarar sus columnas con COLLATE DATABASE_DEFAULT.

La colación y los índices
Sobre 100 000 filas con un índice en la columna:

- WHERE s = 'usuario42' → Index Seek. Con COLLATE sobre la columna → Index Scan: el índice se recorre entero.
- WHERE s LIKE 'usuario42%' → Index Seek, a diferencia de PostgreSQL.
- Un parámetro N'…' contra una columna varchar: con una colación de Windows (Latin1_General_100_CI_AS) se siguió usando el índice, con 7 lecturas lógicas; con una colación SQL_ (SQL_Latin1_General_CP1_CI_AS) la columna entera se convierte a nvarchar y el plan pasa a Index Scan: 334 lecturas frente a 3. Si una búsqueda por igualdad es lenta y el parámetro llega como nvarchar, mira la colación de la columna.

Recomendación
Para una base nueva, una colación _100_…_SC_UTF8, que hace de varchar un UTF-8 completo. Declara las columnas de las temporales con COLLATE DATABASE_DEFAULT. Y decide la colación al crear la base: cambiarla después es columna a columna.

Palabras clave: codificación, encoding, colación, collation, collate, varchar, nvarchar, utf8, utf-16, prefijo n, unicode, _sc, _bin2, 468, 451, 457, 2628, tempdb, database_default, datalength

datetime frente a datetime2, bit en lugar de boolean, money frente a decimal, GUID que no fragmentan, rowversion que no es una fecha, el tope de 8000 bytes y el tipo json de 2025.

Aplica a: SQL Server 2022+

Si vienes de MySQL, varios tipos se llaman parecido y se comportan distinto, y dos nombres engañan: timestamp no es una fecha y datetime redondea. Lo que no existe da error, y menos mal: no hay UNSIGNED, tinyint va sólo de 0 a 255 (con -1 da Msg 220, desbordamiento aritmético) y SELECT TRUE da Msg 207: TRUE no es una palabra del lenguaje.

Fechas: datetime2, no datetime
datetime es el tipo antiguo y redondea a 1/300 de segundo. Medido:

La última fila es la que rompe informes: un WHERE fecha <= '2026-09-30 23:59:59.999' sobre datetime incluye la medianoche del día siguiente. Para un día entero, con cualquier tipo, fecha >= '2026-09-30' AND fecha < '2026-10-01'.

datetime2 guarda hasta cien nanosegundos (en datetime2(7) se quedó .1234567 tal cual), empieza en el año 1 —'1700-01-01' en datetime da Msg 242, fuera de rango— y ocupa lo mismo o menos: medido, datetime 8 bytes, datetime2(7) 8, datetime2(3) 7 y datetime2(0) 6. Y no se mezclan sin pensar: el mismo 12:00:00.001 en datetime2(3) y en datetime no es igual, porque en datetime ya se había guardado como .000.

Otros tamaños medidos: date 3 bytes, time 5, smalldatetime 4 (al minuto) y datetimeoffset 10.

datetimeoffset guarda el desfase
A diferencia de timestamptz de PostgreSQL, sí guarda el desfase con que se escribió, y compara por instante: '2026-09-30T12:00:00+05:30' y '2026-09-30T06:30:00+00:00' dieron iguales. SWITCHOFFSET(valor, '+00:00') lo lleva a UTC, y AT TIME ZONE 'Central European Standard Time' lo dio como 08:30 +02:00, con el horario de verano aplicado. Calíope lo enseña en la hora del dato, con su desfase al lado.

No hay boolean: hay bit
bit es un número que vale 0 o 1, no un valor de verdad. Medido: asignarle 5, -1 o la cadena 'true' guarda 1; WHERE @b a secas da Msg 4145 —hay que escribir WHERE @b = 1— y bit + bit da Msg 402. Calíope lo enseña como 1 o 0.

Dinero: decimal, y money con cuidado

DECLARE @m money = 100, @m3 money = 3;
DECLARE @d decimal(19,4) = 100, @d3 decimal(19,4) = 3;
SELECT @m / @m3 * @m3 AS con_money,     -- 99.9999
       @d / @d3 * @d3 AS con_decimal;   -- 100.000000

money tiene cuatro decimales fijos, también en los resultados intermedios: 1 / 3 dio .3333, y multiplicado por 3 ya no vuelve al entero. decimal amplía la escala del resultado (dio .3333333333333333333). Para calcular, decimal; money ocupa 8 bytes y smallmoney 4. float es binario como en todas partes: 0.1 + 0.2 dio 0.30000000000000004, y la igualdad con 0.3, falsa; en decimal, verdadera.

GUID: uniqueidentifier, y el orden importa
Un uniqueidentifier ocupa 16 bytes. Como clave primaria agrupada, NEWID() reparte las filas al azar por el índice. Medido con 20 000 filas insertadas una a una:

NEWSEQUENTIALID() sólo vale como DEFAULT de una columna: SELECT NEWSEQUENTIALID() da Msg 302. Calíope enseña el GUID en mayúsculas, como SQL Server.

rowversion no es una fecha
rowversion —el tipo que antes se llamaba timestamp, y ese nombre todavía se acepta al crear una columna— es un contador de 8 bytes de la base que cambia en cada escritura de la fila. Medido: dos filas nacieron con 0x…07D4 y 0x…07D5, y al actualizar la primera pasó a 0x…07D6. No guarda ninguna hora. Sirve para la concurrencia optimista: se lee la versión y se actualiza con WHERE ver = @leida. Calíope lo enseña como binario, [8 bytes].

Texto: 8000 bytes, o max
Fuera de max, varchar(n) llega a 8000 y nvarchar(n) a 4000: varchar(8001) da Msg 131 y nvarchar(4001), Msg 2717. Por encima van varchar(max) y nvarchar(max).

La fila también tiene tope: dos char(5000) en una tabla dan Msg 1701 —la fila mínima sería de 10 007 bytes y el máximo son 8060—. Dos varchar(8000) llenos sí caben (medido: 16 000 bytes en una fila), porque lo variable sale de la página cuando no cabe.

Y una trampa que no avisa: REPLICATE('x', 10000) asignado a un varchar(max) dio 8000 caracteres, porque la función trabaja en el tipo de su argumento. Con REPLICATE(CAST('x' AS varchar(max)), 10000), 10 000.

JSON: el tipo json es de SQL Server 2025
En SQL Server 2022 el JSON se guarda en nvarchar(max) y se valida con CHECK (ISJSON(col) = 1), que rechaza lo mal formado con Msg 547. Desde 2025 existe el tipo json, que lo rechaza al escribir con Msg 13609 y lo guarda ya analizado. Medido sobre 20 000 copias de un pedido de 142 caracteres, json ocupó 5,4 MB y nvarchar(max) 6,0 MB; en un documento mínimo, {"b":1,"a":2}, al revés: 60 bytes frente a 26.

Y una diferencia que se ve: con una clave repetida, ISJSON acepta {"b":1,"a":2,"a":3}, y el tipo json lo devolvió como {"b":1,"a":2}: se quedó con la primera, sin avisar. Conserva el orden de las claves. Calíope recibe el json como texto y lo enseña tal cual.

Los tipos especiales
- geography y geometry — tipos con formato binario propio, que no es WKB. geography::Point(40.4168, -3.7038, 4326) ocupa 22 bytes y su STAsText() da POINT (-3.7038 40.4168): el constructor pide latitud, longitud y el texto sale en longitud, latitud. Calíope enseña la celda como [22 bytes]; para leerla, col.STAsText() en la consulta.
- hierarchyid — un camino en un árbol: '/1/3/' se guardó en 2 bytes, con nivel 2 y padre /1/. Calíope no lo decodifica y la celda dice tipo no soportado: hierarchyid; con col.ToString() en la consulta, se lee.
- sql_variant — una columna que guarda valores de varios tipos, cada uno con el suyo: SQL_VARIANT_PROPERTY(@v, 'BaseType') dio decimal. Calíope lo enseña por su tipo base. Tiene sentido en el catálogo (sys.extended_properties); en una tabla propia suele ser una columna mal diseñada.

IDENTITY o SEQUENCE
IDENTITY es una propiedad de la columna, y la protege: insertar un valor a mano da Msg 544, salvo con SET IDENTITY_INSERT tabla ON, y después el contador sigue desde el mayor (tras insertar el 100, la fila siguiente fue la 101). Y deja huecos: un INSERT deshecho con ROLLBACK gastó el 3, y la siguiente fila fue la 4. Un hueco no es un dato perdido.

SEQUENCE es un objeto aparte, que varias tablas pueden compartir: con DEFAULT NEXT VALUE FOR sq en dos tablas, los valores se repartieron 1 y 3 en una y 2 en la otra. Se usa cuando hace falta el número antes de insertar, o compartido; para la clave de una tabla, IDENTITY.

Recomendación
datetime2 para las lecturas de reloj y datetimeoffset para los instantes, decimal para el dinero, bit sabiendo que es un número, NEWSEQUENTIALID() o un entero si la clave agrupada es un GUID, y el tipo json sólo si el servidor más antiguo que tienes es 2025. Para el texto, «Codificación y colaciones (SQL Server)».

Palabras clave: tipos, datetime, datetime2, datetimeoffset, date, time, bit, boolean, money, decimal, float, uniqueidentifier, guid, newsequentialid, rowversion, timestamp, varchar, nvarchar, max, json, isjson, hierarchyid, geography, sql_variant, identity, sequence, unsigned

Cómo se lee un Msg, qué dice su nivel, los números que se ven a diario, qué errores abortan la transacción y cuáles no, TRY…CATCH con THROW, y dónde está el motivo de un 18456.

Aplica a: SQL Server 2022+

Si vienes de MySQL, aquí también hay números —no hay SQLSTATE como en PostgreSQL—, pero cada error trae cuatro datos y todos cuentan. Así lo enseña Calíope, en una línea; sqlcmd lo parte en dos:

Msg 2627, Level 14, State 1, Line 1: Violation of PRIMARY KEY constraint 'PK__padre__3213E83F9B0ABF57'. Cannot insert duplicate key in object 'dbo.padre'. The duplicate key value is (1).

- Msg — el número. Es lo que se busca y lo que se programa.
- Level — la severidad, de 0 a 25. Dice de quién es la culpa y qué le pasa a la conexión.
- State — un subcódigo que el servidor usa para distinguir causas con el mismo número. En la mayoría no importa; en el 18456, es lo único que dice por qué.
- Line — la línea dentro del lote (lo que va entre dos GO), o dentro del procedimiento: un error en uno sale como Procedure p_falla, Line 3.

El nivel, que es lo primero que hay que mirar

Un nivel 20 o más no se puede lanzar a mano sin ser sysadmin y sin WITH LOG: da Msg 2754. Calíope trata un nivel 20 o más como conexión perdida.

Los que se ven todos los días
Provocados uno a uno contra SQL Server 2025:

Para ver el texto de cualquier número: SELECT text FROM sys.messages WHERE message_id = 1205 AND language_id = 1033;. Hay 16 785 mensajes en inglés, en 22 idiomas.

Qué deshace cada error: la trampa de quien viene de PostgreSQL
En PostgreSQL un error aborta la transacción entera. Aquí depende del error, y la mayoría sólo aborta la sentencia. Medido, sin tocar nada:

BEGIN TRAN;
INSERT padre VALUES (10, 'd@x.es', 1);
INSERT padre VALUES (10, 'e@x.es', 1);  -- Msg 2627
INSERT padre VALUES (11, 'f@x.es', 1);
COMMIT;
-- quedaron las filas 10 y 11

La transacción siguió abierta, el COMMIT confirmó lo que sí funcionó y el lote siguió como si nada. Lo que hizo cada error medido:

- Sólo la sentencia: 2627, 2601, 547, 515, 2628, 8134, y también 1222: tras agotar la espera, @@TRANCOUNT seguía en 1.
- El lote y la transacción: 245 (conversión) deshizo la transacción y cortó el lote aun sin nada encendido; 1205 también, en la víctima; un ROLLBACK dentro de un disparador da Msg 3609 y corta el lote.
- La conexión: nivel 20 o más.

SET XACT_ABORT ON lo cambia: con él, el mismo 2627 deshizo la transacción entera y cortó el lote (las filas 20 y 21 no quedaron). Es lo que hay que poner al principio de cada procedimiento que abra una transacción.

TRY…CATCH, y lo que no atrapa

SET XACT_ABORT ON;
BEGIN TRY
  BEGIN TRAN;
  -- …
  COMMIT;
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0 ROLLBACK;
  THROW;
END CATCH;

Dentro del CATCH, ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), ERROR_PROCEDURE() y ERROR_MESSAGE() devuelven los datos del error (medido: 2627, 14, 1, 4, NULL). Y XACT_STATE() dice qué se puede hacer con la transacción: dio 1 sin XACT_ABORT (se puede confirmar) y -1 con él (sólo se puede deshacer).

Lo que un TRY no atrapa: un objeto que no existe en su mismo ámbito. SELECT * FROM tabla_que_no_existe dentro de un TRY salió como Msg 208 sin pasar por el CATCH, porque falla al compilar; dentro de un EXEC sp_executesql, sí lo atrapó. Tampoco atrapa lo de nivel 10 o menos.

THROW frente a RAISERROR
- THROW; sin argumentos, dentro de un CATCH, relanza el error original: el número que llegó fuera seguía siendo 8134. Relanzarlo con RAISERROR(ERROR_MESSAGE(), 16, 1) lo convirtió en 50000, y se perdió el número.
- RAISERROR no detiene el lote: lo que venía detrás se ejecutó. THROW sí.
- THROW 50001, 'texto', 1 lanza un error propio, siempre de nivel 16 y con número de 50000 en adelante. RAISERROR además acepta un número registrado con sp_addmessage y le rellena los argumentos: RAISERROR(50100, 16, 1, 42) dio «El pedido 42 no existe».

El texto cambia con el idioma; el número, no
Con SET LANGUAGE Spanish, el 8134 llegó como «Error de división entre cero.»: el mismo número con otro texto. El idioma es el de la sesión, y Calíope se conecta con el idioma por omisión del login. Por eso, en el código y en los registros propios, se compara el número, nunca el texto.

El 18456: el motivo está en el registro del servidor
Un inicio de sesión fallido llega al cliente siempre igual: Msg 18456, Level 14, State 1, «Login failed for user '…'». Medido con tres causas distintas, el cliente vio State 1 las tres veces. El estado de verdad sólo está en el registro del servidor:

EXEC xp_readerrorlog 0, 1, N'Login failed';

Cuando la base pedida al conectar no existe, antes del 18456 llega un 4060 («Cannot open database … requested by the login»), y ése sí dice el motivo. Si alguien «no puede entrar», el registro del servidor distingue en un segundo lo que desde fuera es la misma frase.

Lo que enseña Calíope
Cuando una sentencia falla, Calíope enseña en la pestaña de resultados el mensaje del servidor entero, con la forma Msg, Level, State, Line de arriba, y todos los mensajes, uno por línea: un CREATE TABLE con una restricción repetida trae el 2714 y después el 1750, y los dos dicen algo. Esos errores se quedan en su pestaña. Los que impiden conectar —el 18456, el 4060— y los de nivel 20 o más, que cortan la conexión, llegan además al registro de errores de Calíope. Los mensajes informativos —un PRINT, un RAISERROR de nivel 10— no los enseña.

Recomendación
SET XACT_ABORT ON y TRY…CATCH con IF @@TRANCOUNT > 0 ROLLBACK; THROW; en todo procedimiento que abra una transacción. En la aplicación, reintentar el 1205 —la transacción entera, que el servidor ya deshizo—, y no reintentar los de nivel 14 a 16, que son del dato o del programa. Guardar siempre el número, nunca el texto. Y ante un 18456, leer el registro del servidor antes de tocar la contraseña.

Palabras clave: error, msg, nivel, severidad, level, state, 18456, 1205, 2627, 2601, 547, 208, 102, 2628, 8152, 515, 229, 916, 4060, 1222, try, catch, throw, raiserror, xact_abort, xact_state, error_number, sys.messages, xp_readerrorlog, reintento

El índice agrupado que es la tabla, el Key Lookup y cuándo lo quita INCLUDE, índices filtrados, columnas calculadas en vez de índices por expresión, columnstore, FILLFACTOR y fragmentación, y los índices que no usa nadie.

Aplica a: SQL Server 2022+

Si vienes de MySQL, lo que más se parece es InnoDB: aquí también la tabla es un índice. Lo que cambia es que ese índice —el agrupado— lo eliges tú y no tiene por qué ser la clave primaria, que una tabla puede no tenerlo, y que en su lugar no hay índices de prefijo ni invisibles, sino filtrados, columnas calculadas y columnstore.

Todo lo que sigue está medido sobre una tabla de 200 000 filas y unos 32 MB.

La tabla es el índice agrupado
PRIMARY KEY crea un índice agrupado si la tabla aún no tiene uno: las filas se guardan ordenadas por esa clave. Una tabla sin índice agrupado es un montón (HEAP en sys.indexes): las filas van donde caben.

Lo que no se ve desde MySQL es el precio: la clave agrupada viaja dentro de cada índice no agrupado, porque es como el índice encuentra la fila. El mismo índice sobre cliente_id ocupó 2 952 kB con una clave agrupada int y 5 416 kB con una uniqueidentifier. Una clave agrupada estrecha y creciente abarata todos los demás índices; por qué NEWID() es mala clave agrupada lo cuenta el tema de tipos de datos.

Key Lookup, y lo que arregla INCLUDE
Un índice no agrupado sólo lleva sus columnas y la clave agrupada. Si la consulta pide otra, cada fila encontrada obliga a volver a la tabla: eso es el Key Lookup.

CREATE INDEX ix_cliente ON dbo.pedidos (cliente_id);
SELECT cliente_id, total FROM dbo.pedidos WHERE cliente_id = 4242;
-- Index Seek (ix_cliente) + Clustered Index Seek … LOOKUP: 29 lecturas lógicas

CREATE INDEX ix_cliente_inc ON dbo.pedidos (cliente_id) INCLUDE (total);
-- Index Seek (ix_cliente_inc) y nada más: 3 lecturas lógicas

INCLUDE guarda la columna en las hojas del índice sin ordenar por ella. Aquí el índice pasó de 2 952 kB a 4 752 kB.

El Key Lookup se paga fila a fila, así que deja de compensar muy pronto. Medido: con 992 filas (el 0,5 % de la tabla) el plan seguía usando el índice y el lookup; con 1 483 (el 0,7 %) ya recorría la tabla entera. Un índice que «no se usa» a menudo es un índice al que le falta un INCLUDE.

La regla del prefijo izquierdo
Con un índice sobre (fecha, cliente_id) y WHERE cliente_id = 77, SQL Server no puede bajar por el índice. Puede, como PostgreSQL, recorrerlo entero si le sale más barato que la tabla: Index Scan y 809 lecturas lógicas. No es un Index Seek, y el orden de las columnas sigue importando.

Índices filtrados
Un índice puede llevar WHERE, y entonces sólo indexa lo que la cumple:

CREATE INDEX ix_pend ON dbo.pedidos (cliente_id) WHERE estado = 'pendiente';

Con los pendientes al 5 %, ocupó 160 kB, frente a los 2 952 kB del índice completo sobre la misma columna. La trampa es que el plan tiene que servir para cualquier valor: con el literal WHERE estado = 'pendiente' lo usó; con una variable (WHERE estado = @e), no, y forzarlo con WITH (INDEX(ix_pend)) da Msg 8622.

Y un uso que desde MySQL sorprende: en SQL Server un índice único admite un solo NULL. CREATE UNIQUE INDEX ux_email ON dbo.pedidos (email) con dos correos a NULL da Msg 1505 («The duplicate key value is (<NULL>)»). La salida es el índice filtrado:

CREATE UNIQUE INDEX ux_email ON dbo.pedidos (email) WHERE email IS NOT NULL;

No hay índices por expresión: hay columnas calculadas
Una función sobre la columna del WHERE impide bajar por el índice. Sobre las mismas 14 400 filas de un mes:

- WHERE YEAR(fecha) = 2023 AND MONTH(fecha) = 6: Index Scan, 510 lecturas.
- WHERE fecha >= '2023-06-01' AND fecha < '2023-07-01': Index Seek, 40 lecturas.

Cuando la expresión no se puede reescribir como rango, se indexa una columna calculada:

ALTER TABLE dbo.pedidos ADD email_lower AS LOWER(email);
CREATE INDEX ix_email_lower ON dbo.pedidos (email_lower);

SELECT id FROM dbo.pedidos WHERE LOWER(email) = 'u77@ejemplo.com';
-- Index Seek (ix_email_lower), sin nombrar la columna calculada

Para crear ese índice, y después para escribir en la tabla, la sesión necesita QUOTED_IDENTIFIER y ANSI_NULLS encendidos; sqlcmd apaga el primero si no se le pasa -I, y entonces falla con Msg 1934.

Columnstore, para lo que agrega
Un índice columnstore guarda cada columna aparte y comprimida. La misma tabla ocupó 35 520 kB con su índice agrupado normal y 7 104 kB como CLUSTERED COLUMNSTORE. Un SELECT estado, SUM(total) … GROUP BY estado leyó 4 440 páginas y gastó 19 ms de CPU sobre la primera, y 147 páginas y 2 ms sobre la segunda. Para buscar una fila por su clave sigue siendo mejor un índice normal; lo habitual en una tabla de trabajo es añadirle un NONCLUSTERED COLUMNSTORE con las columnas que se agregan (el de cuatro columnas ocupó 3 128 kB).

Las claves foráneas no se indexan solas
InnoDB crea un índice para cada clave foránea; SQL Server, no. Medido: tras cliente_id int REFERENCES dbo.clientes(id), la tabla hija sólo tenía el índice de su clave primaria. Sin ese índice, cada DELETE en la tabla padre recorre la hija entera para comprobar la restricción.

Fragmentación y FILLFACTOR

SELECT i.name, p.avg_fragmentation_in_percent, p.page_count,
       p.avg_page_space_used_in_percent
  FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.pedidos'), NULL, NULL, 'DETAILED') p
  JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
 WHERE p.index_level = 0;

El índice sobre cliente_id, con valores al azar, medido paso a paso:

FILLFACTOR deja hueco en cada página para que las inserciones quepan sin partirla: cuesta espacio desde el primer día y ahorra la fragmentación después. Se queda guardado en el índice (sys.indexes.fill_factor), así que los REBUILD siguientes lo repiten. REORGANIZE compacta sin bloquear y se puede interrumpir; REBUILD rehace el índice entero y, sin ONLINE, bloquea la tabla mientras dura.

Reconstruir sin parar la tabla

ALTER INDEX ix_cliente ON dbo.pedidos REBUILD WITH (ONLINE = ON, RESUMABLE = ON);

ONLINE = ON es de la edición Enterprise (y de la Developer, que es la misma, y de Azure SQL): en Standard no existe, y un REBUILD para las escrituras mientras dura —y también las lecturas, si es el índice agrupado—. RESUMABLE = ON permite pausarlo y seguir después, y exige ONLINE = ON (sin él, Msg 11438). Y dos casos en que ONLINE = ON falla: con un índice espacial en la tabla y con ALTER INDEX ALL.

No hay índices invisibles: hay DISABLE
ALTER INDEX … DISABLE es lo más parecido, pero no es lo mismo: borra los datos del índice y deja sólo su definición, así que volver atrás es un REBUILD entero. Y deshabilitar el índice agrupado deja la tabla sin leer: SELECT COUNT(*) da Msg 8655, y de paso deshabilita todos los no agrupados, con un aviso por cada uno.

Los que no usa nadie, y los que faltan

SELECT OBJECT_NAME(i.object_id) AS tabla, i.name AS indice,
       u.user_seeks, u.user_scans, u.user_lookups, u.user_updates
  FROM sys.indexes i
  LEFT JOIN sys.dm_db_index_usage_stats u
    ON u.object_id = i.object_id AND u.index_id = i.index_id
   AND u.database_id = DB_ID()
 WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1 AND i.index_id > 1
 ORDER BY u.user_seeks + u.user_scans + u.user_lookups;

La cautela es mayor que en PostgreSQL: esta vista vive en memoria y se vacía al reiniciar el servidor. Medido: reinicié el contenedor y la vista volvió sin una fila de la tabla. Deshabilitar el índice también lo pone a cero; un REBUILD, no. Un índice que marca 0 lecturas un lunes puede ser uno que nadie ha tenido tiempo de usar.

La otra mitad son los que faltan: el optimizador apunta en sys.dm_db_missing_index_details el índice que habría querido. Tras una consulta por cliente_id y estado, propuso [cliente_id], [estado] con un impacto estimado del 99,6 %. Es una pista, no una orden: no mira los índices que ya tienes, no pondera las escrituras y vive en la misma memoria que se vacía.

Recomendación
Una clave agrupada estrecha y creciente; un índice por cada clave foránea que se use para buscar o para borrar; INCLUDE antes que otro índice cuando el plan enseña un Key Lookup; y antes de borrar un índice «sin uso», mirar cuánto hace que arrancó el servidor (sqlserver_start_time en sys.dm_os_sys_info).

Palabras clave: índice, index, agrupado, clustered, no agrupado, nonclustered, montón, heap, key lookup, include, índice filtrado, columna calculada, columnstore, fillfactor, fragmentación, reorganize, rebuild, online, resumable, disable, dm_db_index_usage_stats, dm_db_missing_index_details, dm_db_index_physical_stats

Los topes que se tocan de verdad —8 060 bytes por fila, 900 y 1 700 de clave de índice, 1 024 columnas, 128 caracteres de nombre, 2 100 parámetros—, cuáles sólo avisan al crear la tabla y fallan después, y los de la edición Express.

Aplica a: SQL Server 2022+

Los límites de SQL Server se parecen poco a los de InnoDB, y los que muerden a diario no son los grandes. Los provoqué uno a uno contra SQL Server 2025.

Los que se tocan de verdad

La fila cabe en una página, salvo lo variable
Una página son 8 KB y una fila tiene que caber en ella: 8 060 bytes. Con columnas de ancho fijo el servidor lo comprueba al crear la tabla: dos char(5000) dan Msg 1701, que dice que la fila mínima serían 10 007 bytes.

Con columnas variables no. Guardé dos varchar(8000) llenos en la misma fila —16 000 bytes— sin ningún error: lo que no cabe se lleva a páginas aparte, ROW_OVERFLOW_DATA. sys.allocation_units enseñó 2 páginas de fila y 2 de desbordamiento. Funciona, pero leer esa fila cuesta una lectura más por columna desbordada, y el diseño no avisa de que está pasando.

SELECT au.type_desc, SUM(au.used_pages) AS paginas
FROM sys.allocation_units AS au
JOIN sys.partitions AS p ON au.container_id = p.partition_id
WHERE p.object_id = OBJECT_ID('dbo.mi_tabla')
GROUP BY au.type_desc;

varchar(max), nvarchar(max) y varbinary(max) llegan a 2 GB y salen de la fila por el mismo camino. Por qué varchar(8001) no existe y hay que saltar a max lo cuenta el tema de tipos de datos.

El límite que sólo avisa: la clave de índice
Éste es el que más sorprende. Un índice sobre una columna más ancha que el tope se crea, con un aviso:

Warning! The maximum key length for a clustered index is 900 bytes. The index 'cx' has maximum length of 1000 bytes.

Un aviso no para el despliegue. La tabla funciona hasta el día en que alguien guarda un valor largo: 900 bytes entraron y 901 dieron Msg 1946. En un no agrupado el tope es 1 700 bytes, y con nvarchar son 850 caracteres, no 1 700, porque cada uno ocupa dos bytes. Un PRIMARY KEY sobre varchar(1000) es un índice agrupado y se porta igual.

Si una columna larga necesita búsqueda por igualdad, lo que funciona es indexar un resumen: una columna calculada con CHECKSUM o HASHBYTES, indexada, y comparar también el valor completo.

Nombres: error, no truncado
Un nombre de 129 caracteres da Msg 103 y la sentencia no se ejecuta. PostgreSQL trunca a 63 bytes y sigue; SQL Server no deja pasar el nombre. El de una tabla temporal local es más corto, 116, porque el servidor le añade un sufijo para distinguir sesiones.

Parámetros: 2 100, y en la práctica 2 098
Un procedimiento admite 2 100 parámetros y una llamada no puede pasar más. Pero los controladores y los ORM mandan las consultas parametrizadas por sp_executesql, y ahí la sentencia y la declaración también cuentan: con 2 098 parámetros funcionó y con 2 099 dio Msg 180. Es el error típico de un WHERE id IN (@p1, @p2, …) generado a partir de una lista que un día crece. La salida no es trocear la lista: es pasarla como tabla (un parámetro con valor de tabla, o una temporal) y hacer JOIN. Un IN con 60 000 literales escritos en la sentencia sí funcionó.

Más columnas: SPARSE con conjunto de columnas
1 025 columnas dan Msg 1702. Con columnas SPARSE y un COLUMN_SET FOR ALL_SPARSE_COLUMNS creé una tabla de 2 002 columnas; las mismas columnas sin el conjunto dieron el mismo Msg 1702. La documentación llama a eso «tabla ancha» y la sube a 30 000 columnas, pero la fila sigue teniendo que caber en 8 060 bytes.

Anidamiento: 32 niveles
Un procedimiento que se llama a sí mismo llegó a @@NESTLEVEL 32 y el siguiente nivel dio Msg 217. Cuentan juntos procedimientos, funciones, disparadores y vistas: un disparador que actualiza otra tabla con disparador gasta niveles sin que se vea.

Los grandes, que casi nunca son el problema
No los medí —el testbed es la edición Developer y medirlos pediría llenar un disco—; van con la cifra de la documentación de Microsoft:

- Tamaño de una base: 524 272 TB. De un archivo de datos: 16 TB.
- Filas por tabla: sin límite más que el almacenamiento.
- Tamaño de un lote: 65 536 veces el tamaño de paquete de red (4 096 bytes de fábrica, o sea 256 MB).
- Conexiones: 32 767 (@@MAX_CONNECTIONS), y user connections a 0 significa que no hay otro tope.

Los que ponen las ediciones
Aquí sí hay límites que se tocan, y no son del motor sino de la licencia. Tampoco los medí: el testbed es Developer, que no tiene ninguno.

- Express: 10 GB por base en SQL Server 2022 y 50 GB en 2025; unos 1,4 GB de memoria para el búfer; el menor de un zócalo o cuatro núcleos. Al llegar al tamaño, la base deja de crecer y las escrituras fallan.
- Standard: sin tope de tamaño de base, pero con techo de memoria y de núcleos, y sin algunas funciones de Enterprise, como REBUILD con ONLINE = ON.

SELECT SERVERPROPERTY('Edition') dice cuál es la del servidor al que estás conectado.

Recomendación
Vigilar tres: la clave de índice cuando se indexa una columna de texto, porque sólo avisa; los 2 098 parámetros cuando algo genera listas IN; y el tamaño de base si el servidor es Express. Del resto se entera uno por el número de error, que siempre dice cuál es el tope.

Palabras clave: límites, máximo, 8060, fila, página, row_overflow_data, desbordamiento, clave de índice, 900 bytes, 1700 bytes, 1024 columnas, sparse, column_set, 128 caracteres, identificador, tabla temporal, 2100 parámetros, sp_executesql, nestlevel, anidamiento, express, 10 gb, 50 gb, edición, msg 1946, msg 1702, msg 180

Por qué aquí un SELECT espera a un UPDATE, qué cambia READ_COMMITTED_SNAPSHOT, cómo se ve quién bloquea a quién, dónde queda el grafo de un 1205 y cuándo un bloqueo de fila se vuelve de tabla.

Aplica a: SQL Server 2022+

Si vienes de MySQL o de PostgreSQL, esto es lo primero que choca: en SQL Server, por omisión, un SELECT espera a un UPDATE. InnoDB y PostgreSQL leen la última versión confirmada de la fila y siguen; SQL Server, en READ COMMITTED y sin más, pide un bloqueo compartido y se queda esperando a que el otro confirme o deshaga. Todo lo que cuento aquí lo medí contra SQL Server 2025, con dos sesiones abiertas a la vez.

Un SELECT que espera

-- Sesión A
BEGIN TRAN;
UPDATE dbo.cuentas SET saldo = 50 WHERE id = 1;

-- Sesión B
SELECT saldo FROM dbo.cuentas WHERE id = 1;

La sesión B se quedó parada 7,5 s, justo lo que tardó A en hacer ROLLBACK, esperando LCK_M_S sobre la clave de esa fila. Y A no tiene por qué estar haciendo nada: una sesión que abrió una transacción, escribió y se quedó quieta —status sleeping, open_transaction_count 1— bloquea igual. Es el caso de todos los días: una aplicación que olvidó el COMMIT.

READ_COMMITTED_SNAPSHOT: leer como en los otros dos

ALTER DATABASE mi_base SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

Con esa opción, READ COMMITTED lee la última versión confirmada de la fila, y la misma lectura volvió al instante con el valor de antes (100, no el 50 sin confirmar). Tres cosas antes de encenderla:
- Es de la base, no de la sesión, y viene apagada: en una base recién creada, en model y en la demo de mi laboratorio, is_read_committed_snapshot_on vale 0.
- Sin WITH ROLLBACK IMMEDIATE, el ALTER espera a que no quede ninguna otra sesión en la base: con una conectada, esperó 14,5 s, lo que tardó en irse. Con él, echa a las demás y deshace sus transacciones.
- Sólo cambia a quien lee. Dos escrituras sobre la misma fila se siguen esperando: el UPDATE de la sesión B esperó igual que antes.

Quién bloquea a quién
Se pregunta mientras el bloqueo dura:

SELECT r.session_id, r.blocking_session_id, r.wait_type,
       r.wait_time, r.wait_resource
FROM sys.dm_exec_requests AS r
WHERE r.blocking_session_id <> 0;

SELECT request_session_id, resource_type, resource_description,
       request_mode, request_status
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID();

La primera responde a la pregunta que de verdad se hace uno: blocking_session_id es la sesión que retiene lo que ésta pide. La segunda enseña la escalera entera: quien actualiza una fila tiene X sobre la clave e IX sobre la página y sobre la tabla, y quien espera aparece con request_status WAIT. A diferencia de PostgreSQL, aquí los bloqueos de fila sí salen en la lista, uno por fila.

La lista de Procesos de Calíope enseña la espera en la columna Estado —una sesión bloqueada aparece con LCK_M_S, LCK_M_U o LCK_M_X—, pero no quién la bloquea: eso lo da la primera consulta. Y Kill Connection es aquí la única forma de soltar un bloqueo ajeno: KILL cierra la sesión entera y deshace su transacción, porque SQL Server no deja cancelar sólo la consulta de otra sesión.

UPDLOCK, el FOR UPDATE de aquí
No hay SELECT … FOR UPDATE. Ese trabajo lo hace una pista:

BEGIN TRAN;
SELECT saldo FROM dbo.cuentas WITH (UPDLOCK, ROWLOCK) WHERE id = 1;
UPDATE dbo.cuentas SET saldo = saldo - 100 WHERE id = 1;
COMMIT;

Toma un bloqueo U: una lectura normal de otra sesión pasó sin esperar, y otra lectura con UPDLOCK se quedó esperando. Y evita el interbloqueo más tonto de todos: dos sesiones en REPEATABLE READ que leen la misma fila y después la actualizan acabaron en un 1205; con UPDLOCK en la lectura, las dos terminaron, una detrás de otra.

Aquí también se espera para siempre
@@LOCK_TIMEOUT vale -1, o sea sin límite: como el lock_timeout de PostgreSQL, y no como los 50 s de InnoDB. Se pone por sesión, en milisegundos:

SET LOCK_TIMEOUT 3000;

Cuando salta llega un Msg 1222, y no deshace nada: la transacción sigue abierta y quien lo recibe tiene que deshacerla él. Qué deshace cada error lo cuenta el tema «Errores (SQL Server)».

No esperar, a propósito

SELECT saldo FROM dbo.cuentas WITH (NOWAIT) WHERE id = 3;

SELECT id FROM dbo.cuentas WITH (READPAST) WHERE id BETWEEN 1 AND 5;

NOWAIT falla al instante con el mismo 1222, y el lote sigue. READPAST es el SKIP LOCKED de aquí: con la fila 3 bloqueada por otra sesión, devolvió 1, 2, 4 y 5. Es la forma de repartir una cola de trabajo entre varios consumidores sin que se esperen.

NOLOCK lee lo que nunca existió

SELECT saldo FROM dbo.cuentas WITH (NOLOCK) WHERE id = 3;

Con otra sesión a mitad de un UPDATE que luego deshizo, esa lectura devolvió 50: un saldo que nunca se confirmó. NOLOCK es READ UNCOMMITTED, y no es «leer más rápido»: es leer lo que otra transacción todavía puede tirar. La documentación añade que, si las páginas se parten mientras lee, puede saltarse filas o leerlas dos veces; eso no lo reproduje. Si lo que se busca es que leer no espere, la respuesta es READ_COMMITTED_SNAPSHOT, que no espera y sólo ve lo confirmado.

El abrazo mortal

-- Sesión A
BEGIN TRAN;
UPDATE dbo.cuentas SET saldo = saldo - 10 WHERE id = 1;
UPDATE dbo.cuentas SET saldo = saldo + 10 WHERE id = 2;

-- Sesión B
BEGIN TRAN;
UPDATE dbo.cuentas SET saldo = saldo - 10 WHERE id = 2;
UPDATE dbo.cuentas SET saldo = saldo + 10 WHERE id = 1;

Una de las dos recibe Msg 1205 —nivel 13, «… has been chosen as the deadlock victim. Rerun the transaction.»— y pierde su transacción y el resto del lote; la otra confirma. No se detecta al instante: en seis corridas, la víctima tardó entre 0,1 y 4,8 s en caer desde que se cerró el ciclo. La documentación explica esa horquilla: un monitor busca ciclos cada 5 s, y más a menudo justo después de encontrar uno.

Cuál muere se puede decidir. Con SET DEADLOCK_PRIORITY HIGH en la sesión A, la víctima fue B las tres veces; LOW es lo que conviene a un proceso por lotes que puede repetirse. Un 1205 no es una avería: la aplicación tiene que reintentar esa transacción.

El grafo ya está guardado
No hace falta encender nada: la sesión de Extended Events system_health, que viene en marcha, guarda cada interbloqueo con su grafo —quién murió, qué sentencia llevaba cada uno y qué clave de qué índice esperaba—.

SELECT CAST(event_data AS xml) AS grafo
FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
WHERE object_name = 'xml_deadlock_report';

Dos cosas medidas. El archivo va con retraso: justo después de provocar seis, tenía dos; unos segundos después, todos. Y el ring_buffer de la misma sesión, que los tiene al momento, vive en memoria: tras reiniciar el servidor tenía los nuevos y había perdido el de antes, que el archivo seguía guardando.

De fila a tabla: la escalada
Cada bloqueo ocupa memoria, y cuando una sentencia acumula demasiados sobre una tabla, SQL Server los cambia por uno solo sobre la tabla entera. Actualicé las primeras N filas de una tabla de 20 000 y conté:
- Hasta 6 200 filas, 6 200 bloqueos X de clave.
- Desde 6 300, uno: X sobre la tabla, sin pasar por la página. Y otra sesión que fue a la fila 20 000, que nadie había tocado, esperó hasta su LOCK_TIMEOUT.

La cifra que da la documentación es 5 000; aquí saltó un poco más tarde. Un SELECT de 8 000 filas en REPEATABLE READ acabó igual, con un S sobre la tabla. Se controla por tabla:

ALTER TABLE dbo.cuentas SET (LOCK_ESCALATION = DISABLE);

Con eso, el mismo UPDATE de las 20 000 filas retuvo 20 000 bloqueos de clave y ninguno de tabla. TABLE es el valor por omisión; AUTO, según la documentación, escala a la partición en una tabla particionada. Apagarla no sale gratis —cada bloqueo es memoria del servidor—, y la salida habitual es otra: escribir por lotes de menos de 5 000 filas.

Bloqueos de aplicación
El equivalente de los bloqueos consultivos de PostgreSQL: un nombre que el servidor guarda para que dos procesos de tu aplicación no hagan lo mismo a la vez.

DECLARE @r int;
EXEC @r = sp_getapplock @Resource = 'cierre-diario', @LockMode = 'Exclusive',
     @LockOwner = 'Session', @LockTimeout = 0;
-- …
EXEC sp_releaseapplock @Resource = 'cierre-diario', @LockOwner = 'Session';

Con el recurso en manos de otra sesión devolvió -1 en vez de esperar; 0 es «concedido». No lanza ningún error: hay que mirar el número. Con @LockOwner = 'Transaction', que es el valor por omisión, se suelta solo al acabar la transacción, y pedirlo fuera de una devolvió -999.

Recomendación
Tocar las filas siempre en el mismo orden y transacciones cortas, como en cualquier motor. Lo propio de aquí son tres cosas: encender READ_COMMITTED_SNAPSHOT en cuanto la aplicación lo tolere, que es lo que hace que leer deje de esperar a escribir y quita la tentación de NOLOCK; UPDLOCK en la lectura que va a acabar en UPDATE; y el reintento del 1205 escrito antes de subir a producción.

Palabras clave: bloqueo, lock, deadlock, interbloqueo, 1205, 1222, lck_m_s, blocking_session_id, dm_tran_locks, dm_exec_requests, read_committed_snapshot, rcsi, nolock, readpast, updlock, nowait, lock_timeout, deadlock_priority, escalada, lock_escalation, system_health, sp_getapplock

El DDL es transaccional y lo que cuesta es el bloqueo Sch-M y la cola que forma, qué ALTER reescribe la tabla, ONLINE, RESUMABLE y WAIT_AT_LOW_PRIORITY, y por qué WITH NOCHECK aquí no aligera el bloqueo.

Aplica a: SQL Server 2022+

Si vienes de MySQL, lo primero: aquí el DDL es transaccional, como en PostgreSQL. Si vienes de PostgreSQL, lo que cambia es el nombre del bloqueo y un par de herramientas que allí no hay. Todo lo que cuento aquí lo medí contra SQL Server 2025, sobre una tabla de 200 000 filas.

BEGIN TRAN;
ALTER TABLE dbo.pedidos ADD moneda char(3) NULL;
CREATE INDEX ix_cliente ON dbo.pedidos (cliente);
CREATE TABLE dbo.monedas (codigo char(3) PRIMARY KEY);
EXEC sp_rename 'dbo.pedidos.nota', 'comentario', 'COLUMN';
ROLLBACK;

Tras ese ROLLBACK no quedaba nada: ni la columna, ni el índice, ni la tabla, y la columna volvía a llamarse nota. Una migración entera cabe en una transacción. Las excepciones que encontré: CREATE DATABASE y ALTER DATABASE (Msg 226) y un índice RESUMABLE (Msg 574) no pueden ir dentro de una.

Lo que cuesta es el Sch-M, y la cola que forma
Todo ALTER TABLE pide un bloqueo de modificación de esquema, Sch-M, que choca con todo, un SELECT incluido. Lo peligroso no es el ALTER, que puede durar milisegundos: es la espera. Con otra sesión a mitad de una transacción que había tocado una fila:
- El ALTER TABLE … ADD esperó 7 s, con wait_type LCK_M_SCH_M.
- Un SELECT de otra fila, lanzado detrás, esperó 6 s con LCK_M_SCH_S, y su blocking_session_id no era la transacción: era el ALTER.

Así se para una aplicación entera con un cambio de un milisegundo. El remedio es el mismo que en PostgreSQL:

SET LOCK_TIMEOUT 3000;
ALTER TABLE dbo.pedidos ADD moneda char(3) NULL;

A los 3 s el ALTER recibió Msg 1222, y el SELECT de detrás salió en ese mismo instante. Se reintenta más tarde.

Qué reescribe la tabla y qué no
Medido sobre 200 000 filas (1 364 páginas). El testigo es el registro de transacciones que escribió cada sentencia:

Cómo leerla:
- Un valor por omisión que es el mismo para todas las filas —una constante, o SYSDATETIME(), que se evalúa una vez— se guarda en los metadatos y no toca las filas. NEWID() da uno distinto a cada fila, y eso obliga a escribirlas todas.
- Ensanchar un varchar es gratis. Estrecharlo, cambiarlo de familia (nvarchar, max), agrandar un entero, pasar a NOT NULL o cambiar un decimal de tamaño en disco reescriben. decimal(10,2) y decimal(12,2) ocupan lo mismo, 9 bytes, y por eso ése no reescribió.
- Lo que reescribe deja la tabla del doble. Tras cada una de esas sentencias la tabla pasó de 1 364 a 2 723 páginas y ahí se quedó: la columna vieja sigue ocupando su sitio en cada fila. Tras int → bigint, un ALTER TABLE dbo.pedidos REBUILD la dejó en 1 495. Y un DROP COLUMN es instantáneo porque no libera nada: 1 364 páginas antes y después, y 1 075 tras el REBUILD.

Dos obstáculos que PostgreSQL no pone:
- Una columna con un índice no cambia de tipo. Con un índice sobre estado, pasar a varchar(100) funcionó, porque no reescribe; varchar(30) y nvarchar(100) dieron Msg 5074 («The index 'ix_estado' is dependent on column 'estado'»). Hay que quitar el índice, cambiar la columna y volver a crearlo.
- Un DEFAULT es una restricción con nombre, y mientras exista la columna no se puede borrar: el mismo Msg 5074, nombrando una restricción DF__pedidos__moneda__… que SQL Server bautizó solo. Se suelta antes con ALTER TABLE … DROP CONSTRAINT, y por eso conviene darle nombre al crearla.

La documentación añade una condición de edición: que un ADD COLUMN … NOT NULL DEFAULT sólo toque los metadatos es de Enterprise; en Standard reescribe la tabla. Mi laboratorio es Developer, que tiene todo lo de Enterprise, así que eso no lo pude medir.

Índices en línea: ONLINE = ON

ALTER INDEX ALL ON dbo.grande REBUILD WITH (ONLINE = ON);
CREATE INDEX ix_g ON dbo.grande (g) WITH (ONLINE = ON);

Sobre 3 000 000 de filas, la reconstrucción en línea duró 47 s, y un UPDATE lanzado a la mitad terminó en medio segundo. Sin ONLINE, la tabla queda bloqueada todo ese rato. Un ALTER COLUMN también lo admite (ALTER TABLE … ALTER COLUMN n bigint NOT NULL WITH (ONLINE = ON)): tardó 395 ms en vez de 228, pero dejó la tabla en 1 483 páginas en vez de doblarla.

Tres límites:
- Con un índice espacial en la tabla, ALTER INDEX ALL … WITH (ONLINE = ON) falla con Msg 153, y el índice espacial tampoco se reconstruye en línea. La clave primaria y los índices normales, uno a uno, sí.
- ONLINE no quiere decir sin bloqueos: al empezar pide un bloqueo S sobre la tabla y, según la documentación, un Sch-M breve al terminar. Con una transacción abierta en la tabla, el REBUILD en línea se quedó esperando su S, y un UPDATE que llegó detrás esperó 6,6 s en la cola (LCK_M_IX). Un SELECT sí pasó.
- Según la documentación, ONLINE = ON es de Enterprise: en Standard da error.

WAIT_AT_LOW_PRIORITY: esperar sin formar cola
Esto no lo tiene PostgreSQL. La reconstrucción espera su bloqueo en una cola aparte, sin estorbar a los que llegan detrás:

ALTER INDEX ALL ON dbo.pedidos REBUILD WITH (ONLINE = ON (
    WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF)));

Con la misma transacción abierta, el UPDATE de detrás pasó en 0,5 s, y el REBUILD aparecía esperando con LCK_M_S_LOW_PRIORITY. Pasado MAX_DURATION, decide ABORT_AFTER_WAIT:
- SELF: el REBUILD se rinde con Msg 1222 y la transacción sigue. Es lo prudente.
- BLOCKERS: al minuto, la sesión que lo bloqueaba fue desconectada («… disconnected because of a high priority DDL operation») y su transacción, deshecha; el REBUILD terminó.
- NONE: espera a baja prioridad sin límite.

La acepta ALTER INDEX … REBUILD y, en mi 2025, también CREATE INDEX … WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY …)). ALTER TABLE no: con ADD, ALTER COLUMN o ADD CONSTRAINT es un error de sintaxis (Msg 155). Para ésos sólo queda LOCK_TIMEOUT.

Índices reanudables: RESUMABLE = ON

CREATE INDEX ix_relleno ON dbo.grande (relleno, g) WITH (ONLINE = ON, RESUMABLE = ON);
-- desde otra sesión:
ALTER INDEX ix_relleno ON dbo.grande PAUSE;
SELECT name, state_desc, percent_complete FROM sys.index_resumable_operations;
ALTER INDEX ix_relleno ON dbo.grande RESUME;

Pausé la creación a los 4 s: la sesión que la había lanzado fue desconectada (Msg 1219), sys.index_resumable_operations decía PAUSED y 5 %, y el índice todavía no existía —no salía en sys.indexes—. RESUME terminó el resto en 45 s. Sirve para repartir un índice enorme entre varias ventanas de mantenimiento. Exige ONLINE = ON (sin él, Msg 11438), no cabe en una transacción (Msg 574) y, según la documentación, desde SQL Server 2022 vale también para ALTER TABLE … ADD CONSTRAINT de una clave primaria o única; en 2025 lo comprobé con una única.

WITH NOCHECK: aquí no aligera el bloqueo
El patrón NOT VALID + VALIDATE de PostgreSQL tiene aquí su gemelo, pero no hace lo mismo. Sobre 3 000 000 de filas:

ALTER TABLE dbo.grande WITH NOCHECK ADD CONSTRAINT ck_id CHECK (id > 0);  -- 13 ms
ALTER TABLE dbo.grande WITH CHECK CHECK CONSTRAINT ck_id;                 -- 346 ms

Añadirla de una vez tardó 375 ms. Y los tres tomaron Sch-M sobre la tabla, la validación incluida: partirlo en dos reparte el recorrido, pero no hay un bloqueo más débil para la segunda mitad, como sí lo hay en PostgreSQL.

Y deja una trampa: una restricción añadida WITH NOCHECK queda sin confianza (is_not_trusted = 1), y el optimizador no se fía de ella. Con un CHECK (importe >= 0) de confianza, WHERE importe < 0 ni siquiera leyó la tabla; sin confianza, 1 364 lecturas lógicas. Lo mismo pasa al desactivarla y volver a activarla sin WITH CHECK —ALTER TABLE … CHECK CONSTRAINT ck_importe la dejó sin confianza—, que es lo que suelen hacer las cargas masivas. Para encontrarlas:

SELECT name FROM sys.check_constraints WHERE is_not_trusted = 1;
SELECT name FROM sys.foreign_keys WHERE is_not_trusted = 1;

En Calíope
La operación OPTIMIZE de Mantenimiento de tablas es, en SQL Server, ALTER INDEX ALL … REBUILD sin ONLINE: bloquea la tabla mientras reconstruye. Sobre una tabla grande en producción, es mejor escribir en el editor el REBUILD WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY …)).

Recomendación
SET LOCK_TIMEOUT antes de cada ALTER TABLE; la migración dentro de una transacción; ONLINE = ON con WAIT_AT_LOW_PRIORITY … SELF para los índices; nombre propio para cada DEFAULT; y cuidado con los cambios de tipo, que reescriben la tabla y la dejan del doble hasta el siguiente REBUILD. Si hay que estrechar una columna grande o cambiarla de familia, suele salir mejor añadir la nueva, copiar por lotes y renombrar con sp_rename.

Palabras clave: ddl, alter table, migración, transaccional, rollback, sch-m, lck_m_sch_m, lock_timeout, online, resumable, wait_at_low_priority, abort_after_wait, rebuild, reescritura, add column, alter column, drop column, with nocheck, is_not_trusted, sp_rename

Función y esquema de partición, qué elimina de verdad el optimizador, por qué un índice único tiene que llevar la clave, SWITCH y TRUNCATE para purgar en milisegundos, el CHECK de confianza que exige meter datos, y SPLIT y MERGE, que sólo son gratis sobre una partición vacía.

Aplica a: SQL Server 2022+

Si vienes de MySQL o de PostgreSQL, lo que cambia es la forma: aquí una partición no es una tabla, sino una pieza de una, y el reparto lo describen dos objetos que se crean antes que la tabla. Todo lo que cuento aquí lo medí contra SQL Server 2025, sobre 300 000 ventas repartidas en tres años.

CREATE PARTITION FUNCTION pf_anio (date)
    AS RANGE RIGHT FOR VALUES ('2024-01-01', '2025-01-01', '2026-01-01');

CREATE PARTITION SCHEME ps_anio
    AS PARTITION pf_anio ALL TO ([PRIMARY]);

CREATE TABLE dbo.ventas (
    id bigint IDENTITY NOT NULL,
    creado date NOT NULL,
    cliente int NOT NULL,
    importe decimal(10,2) NOT NULL,
    CONSTRAINT pk_ventas PRIMARY KEY CLUSTERED (creado, id)
) ON ps_anio (creado);

La función pone las fronteras y el esquema dice en qué grupo de archivos vive cada pieza. Tres fronteras dan cuatro particiones: lo anterior a 2024, un año en cada una de las dos siguientes, y todo lo de 2026 en adelante. No hay LIST ni HASH: sólo rangos. Y no hace falta prever el futuro: lo anterior a la primera frontera y lo posterior a la última siempre tienen sitio.

RANGE RIGHT o RANGE LEFT: de qué lado cae la frontera
Con RIGHT, la frontera pertenece a la partición de su derecha, y eso es lo natural con fechas: $PARTITION.pf_anio('2023-12-31') devolvió 1, y '2024-01-01', 2. Con LEFT y las mismas fronteras, el 1 de enero cayó en la partición de 2023. Nada falla: los datos de un día quedan en el año equivocado. $PARTITION sirve también para contar:

SELECT $PARTITION.pf_anio(creado) AS particion, COUNT(*) AS filas
FROM dbo.ventas GROUP BY $PARTITION.pf_anio(creado);

La eliminación de particiones es lo que se busca
El plan real (SET STATISTICS XML ON, en RunTimePartitionSummary) dice cuántas particiones leyó cada consulta:

Dos cosas que se leen ahí:
- Con variables y parámetros también elimina, porque se decide al ejecutar, no al compilar. Por eso el plan estimado no lo dice con un número: enseña [PtnId1000] >= RangePartitionNew(…) cuando la clave está en el filtro, y [PtnId1000] >= (1) AND [PtnId1000] <= (4) cuando no.
- Una función sobre la columna la esconde, igual que a un índice: YEAR(creado) leyó las cuatro. El rango escrito a mano (>= '2024-01-01' AND < '2025-01-01') lee una.

La regla del diseño es la de los otros motores: la clave de partición es la columna por la que filtras casi siempre. Si las consultas no la nombran, particionar no ahorra lectura: la reparte.

Índices alineados, y la unicidad
Un índice creado sin ON hereda el esquema de la tabla: queda alineado, con una pieza por partición. La consecuencia es la de PostgreSQL:

CREATE UNIQUE INDEX ux_id ON dbo.ventas (id);

dio Msg 1908: la columna de partición tiene que estar en la clave de un índice único. Aquí hay una salida que PostgreSQL no tiene —crearlo sin alinear, ON [PRIMARY]—, pero tiene precio: con un índice así, el SWITCH de abajo deja de funcionar (Msg 7733). Por eso la primaria de dbo.ventas es (creado, id) y no (id).

SWITCH y TRUNCATE: purgar un año en milisegundos
Borrar 2023 entero, 100 009 filas, medido dentro de una transacción:

SWITCH no mueve datos: cambia de dueño una pieza entera. Por eso le pide mucho a la tabla de destino, y cada exigencia tiene su error:
- Vacía: con una fila, Msg 4905.
- Con las mismas columnas y los mismos índices: con una columna de más, Msg 4943.
- Y, según la documentación, en el mismo grupo de archivos que la partición.

Para meter datos hace falta un CHECK de confianza
Aquí está la diferencia con PostgreSQL, donde el CHECK sólo ahorra un recorrido. Devolver la tabla a su partición sin él no tarda más: se niega, con Msg 4982. Con la restricción, entró en 9,5 ms:

ALTER TABLE dbo.ventas_2023 WITH CHECK
    ADD CONSTRAINT ck_2023 CHECK (creado < '2024-01-01');
ALTER TABLE dbo.ventas_2023 SWITCH TO dbo.ventas PARTITION 1;

Y tiene que ser de confianza: desactivada y vuelta a activar sin WITH CHECK —lo que hacen muchas cargas masivas—, el SWITCH dio Msg 4972. Tras WITH CHECK CHECK CONSTRAINT, entró. Así se carga un periodo nuevo sin tocar la tabla viva: se llena una tabla aparte, se le pone su CHECK y se hace el SWITCH.

SPLIT y MERGE: gratis sólo sobre una partición vacía
Las fronteras se mueven con la función:

ALTER PARTITION SCHEME ps_anio NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION pf_anio() SPLIT RANGE ('2027-01-01');

Partir una partición llena mueve las filas de un lado de la frontera, y cada una escribe registro: el coste crece con lo que se mueve. La costumbre sana es tener siempre una partición vacía al final y partirla antes de que lleguen los datos del periodo siguiente. Y cada SPLIT gasta el NEXT USED: sin volver a declararlo, el siguiente dio Msg 7710 y no cambió nada.

Mantenimiento por partición
Se reconstruye sólo la partición que cambia:

ALTER INDEX pk_ventas ON dbo.ventas REBUILD PARTITION = 2 WITH (ONLINE = ON);

33 ms para una partición (148 ms en línea) frente a 214 ms para las cuatro. Un año cerrado ya no recibe escrituras: no hay por qué reconstruirlo cada noche.

La escalada de bloqueos, por partición
Por omisión (LOCK_ESCALATION = TABLE), un UPDATE de 16 440 filas de 2024 escaló a un bloqueo X de toda la tabla, y actualizar una fila de 2025 desde otra sesión dio Msg 1222. Con

ALTER TABLE dbo.ventas SET (LOCK_ESCALATION = AUTO);

la escalada se quedó en la partición (un X de HOBT), y la fila de 2025 se actualizó en 9 ms. La documentación avisa de que AUTO puede traer interbloqueos entre sesiones que escalan en particiones distintas; lo demás de la escalada está en «Bloqueos y deadlocks (SQL Server)».

SWITCH también toma Sch-M
Con una transacción abierta que había tocado una sola fila, el SWITCH se quedó esperando su Sch-M, y un SELECT de otra fila, lanzado detrás, esperó en la cola detrás del SWITCH. La cura es la de los índices:

ALTER TABLE dbo.ventas SWITCH PARTITION 1 TO dbo.ventas_2023
    WITH (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF));

El SELECT de detrás terminó en medio segundo, y el SWITCH se rindió a los 61 s con Msg 1222, sin tocar la transacción. Lo demás de esa cola está en «Cambios de esquema en caliente (SQL Server)».

Límites y ediciones
Una función admite 15 000 particiones: con 15 000 fronteras, Msg 7719. Según la documentación, el particionado está en todas las ediciones desde SQL Server 2016 SP1, Express incluida.

En Calíope
Las filas y el tamaño de una tabla particionada suman todas sus particiones. El CREATE TABLE que escribe Calíope —el que enseña como DDL de la tabla y el que guarda Respaldo— lleva delante la función y el esquema de partición, cada uno con IF NOT EXISTS, y termina en ON ps_anio (creado); un índice que no está alineado, como una primaria no agrupada en [PRIMARY], conserva su propio ON. Restaurar ese respaldo en una base vacía la deja particionada igual, con cada fila en su partición; si el destino ya tiene una función con ese nombre, se usa la que hay, con sus fronteras. Comparar esquemas sí mira el particionado —dónde va la tabla y las fronteras de cada esquema—, pero su guion no particiona ni desparticiona una tabla que ya existe: eso es reconstruir su índice agrupado con los datos dentro, y lo deja escrito como nota para que lo hagas tú. El plan que enseña Calíope es el estimado, así que de las particiones da la forma PtnId1000 de arriba; cuántas leyó de verdad sólo lo dice el plan real, SET STATISTICS XML ON.

Recomendación
Particiona por lo que vas a borrar: la ganancia de verdad es purgar un periodo con SWITCH o TRUNCATE … WITH (PARTITIONS) en vez de un DELETE que llena el registro. Primaria e índices únicos con la clave de partición dentro, para que todo quede alineado; RANGE RIGHT con fechas; siempre una partición vacía al final, para partirla gratis; y comprueba en el plan real que tus consultas eliminan particiones, en lugar de suponerlo.

Palabras clave: particionado, partition, partition function, partition scheme, range right, range left, $partition, eliminación de particiones, alineado, switch, truncate, split range, merge range, next used, lock_escalation, 1908, 4982, 7733

Lecturas lógicas en vez de tiempos, el plan estimado frente al real, la detección de parámetros y sus curas medidas, la caché de planes que lee Consultas lentas, Query Store encendido de fábrica y las esperas que sí dicen algo.

Aplica a: SQL Server 2022+

Si vienes de MySQL o de PostgreSQL, aquí cambian tres cosas: la cifra que se compara son las lecturas lógicas, no el tiempo; el plan se guarda en caché y se reutiliza con otros valores, y ésa es la causa de la mitad de los «ayer iba bien»; y el equivalente de pg_stat_statements ya está ahí sin encender nada, pero se pierde al reiniciar. Todo lo que cuento aquí lo medí contra SQL Server 2025, sobre 200 000 pedidos.

Medir una consulta: lecturas lógicas

SET STATISTICS IO, TIME ON;
SELECT COUNT(*), SUM(importe) FROM dbo.pedidos WHERE cliente_id = 4243;
SET STATISTICS IO, TIME OFF;

Cada lectura lógica es una página de 8 kB leída de memoria. Es la cifra que se compara, porque el tiempo cambia con la carga y con la caché, y ella no. Para los 20 pedidos de un cliente:
- Sin índice: 6 085 lecturas lógicas —la tabla entera— y 12 ms.
- Con un índice sobre cliente_id: 62 lecturas.

En Calíope esas líneas no aparecen: el servidor las manda como mensajes informativos, y el editor enseña filas y errores, no mensajes. La misma cifra se lee de la caché de planes, justo después de ejecutar la consulta:

SELECT TOP (5) qs.last_logical_reads, qs.last_elapsed_time AS micros, st.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE st.text LIKE N'%cliente_id = 4243%'
ORDER BY qs.last_execution_time DESC;

Me devolvió 62, lo mismo que STATISTICS IO.

El plan estimado y el real
SET SHOWPLAN_XML ON pide el plan sin ejecutar. SET STATISTICS XML ON ejecuta la consulta y le añade lo que pasó: ActualRows junto a EstimateRows en cada operador. Si la sentencia escribe, se envuelve en BEGIN TRAN … ROLLBACK, porque se ejecuta de verdad.

Visual EXPLAIN, en Calíope, pide el estimado: el árbol de operadores con las filas estimadas, sin filas reales, sin tiempos y sin coste. Sirve para ver la forma, un Index Scan donde esperabas un Seek; para comparar estimación con realidad hace falta el real.

La detección de parámetros
La primera vez que se ejecuta un procedimiento o una consulta con parámetros, el optimizador mira el valor que trae y compila el plan para él. Ese plan se queda en caché, y las ejecuciones siguientes lo reutilizan aunque traigan otro valor. Lo reproduje con un procedimiento y dos clientes: uno con 20 pedidos y otro con 100 000.

CREATE OR ALTER PROCEDURE dbo.pedidos_de @cliente int AS
  SELECT SUM(importe) AS total, COUNT(*) AS n
  FROM dbo.pedidos WHERE cliente_id = @cliente;

Compilado para el cliente pequeño, el plan busca en el índice y va a la tabla fila a fila: perfecto para 20 filas, y 300 000 lecturas para 100 000, cincuenta veces más que recorrer la tabla entera. Compilado al revés, el pequeño recorre la tabla entera. El orden de llegada decide, y un reinicio o un cambio de estadísticas lo cambia: por eso «ayer iba bien».

El plan real lo delata sin adivinar: en el caso malo decía EstimateRows="20" y ActualRows="100000", y en sus parámetros, ParameterCompiledValue="(4243)" junto a ParameterRuntimeValue="(1)".

Lo que probé para curarlo:
- OPTION (RECOMPILE) al final de la consulta: 62 y 6 085, cada valor con su plan. El precio es compilar en cada llamada (aquí, 2-3 ms), que en una consulta que se ejecuta miles de veces por segundo no sale gratis.
- OPTION (OPTIMIZE FOR UNKNOWN): compila para el valor medio, unas 20 filas, y eso dio 70 y 306 429. Cuando hay un valor enorme, no ayuda.
- EXEC sp_recompile 'dbo.pedidos_de' tira el plan del procedimiento: es el remedio del momento, no la cura.
- Desde SQL Server 2022, con nivel de compatibilidad 160 o superior, el servidor puede guardar varios planes para una misma consulta según el valor. Viene encendido, pero con 100 000 filas frente a 20 no actuó. Con 200 000 frente a 1, sí: dos planes. No cuentes con él.

Qué consulta cuesta más: la caché de planes
El servidor lleva la cuenta de cada plan que tiene en caché: ejecuciones, tiempo, lecturas y filas. query_hash agrupa por la forma de la consulta: cinco SELECT que sólo cambiaban el número de cliente, cada uno suelto, dejaron cinco planes en caché (80 kB) y una sola query_hash.

SELECT TOP (20) CONVERT(varchar(20), qs.query_hash, 1) AS forma,
       SUM(qs.execution_count) AS ejecuciones,
       SUM(qs.total_elapsed_time) / 1000 AS ms_total,
       SUM(qs.total_logical_reads) AS lecturas
FROM sys.dm_exec_query_stats AS qs
GROUP BY qs.query_hash
ORDER BY SUM(qs.total_elapsed_time) DESC;

Es lo que enseña Calíope en Consultas lentas, vista Por sentencia: una fila por forma, con el texto de su plan más caro, de todas las bases del servidor. Pide el permiso VIEW SERVER STATE. Y no guarda historia: reinicié el servidor y la vista pasó de 23 filas a 0. Tampoco sobrevive a que el plan salga de la caché.

Query Store, que ya está encendido
Query Store es la versión que persiste: guarda texto, planes y cifras por intervalos dentro de la propia base. En SQL Server 2025 lo encontré encendido (READ_WRITE) en demo y en una base recién creada, sin que nadie lo pidiera. Según la documentación, es así desde SQL Server 2022 en las bases nuevas.

SELECT TOP (20) q.query_id, SUM(rs.count_executions) AS ejecuciones,
       SUM(rs.avg_duration * rs.count_executions) / 1000 AS ms_total,
       COUNT(DISTINCT p.plan_id) AS planes, qt.query_sql_text
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
GROUP BY q.query_id, qt.query_sql_text
ORDER BY ms_total DESC;

Una columna planes mayor que 1 es la huella de la detección de parámetros. Dos matices medidos: tras el reinicio seguían los textos de sus 11 consultas, pero no las cifras de antes, porque las escribe a disco cada 900 segundos; y es por base, no del servidor.

Calíope no lo usa: Consultas lentas lee la caché de planes, que ya está ahí para todo el servidor. Ni lo enciende ni lo apaga, porque eso es un ALTER DATABASE de administración.

Esperas: en qué se va el tiempo
sys.dm_os_wait_stats suma, desde el último reinicio, cuánto esperó el servidor y por qué. Tiene 1 547 tipos, y los primeros de la lista, sin filtrar, son hilos que esperan trabajo: DISPATCHER_QUEUE_SEMAPHORE, BROKER_TASK_STOP, los SLEEP_…. Hay que descartarlos. Los que sí dicen algo:
- LCK_M_…: bloqueos. Lo cuento en «Bloqueos y deadlocks (SQL Server)».
- PAGEIOLATCH_…: esperando páginas del disco. Faltan memoria o índices.
- WRITELOG: esperando al registro de transacciones en cada confirmación.
- CXPACKET y CXCONSUMER: paralelismo.
- SOS_SCHEDULER_YIELD: CPU.
- RESOURCE_SEMAPHORE: consultas esperando memoria para ordenar o para un hash.

Son acumulados, así que la foto sirve de poco: se toman dos y se restan. Tras mi reinicio, la suma bajó de 12,1 a 0,7 millones de ms.

Paralelismo
De fábrica, cost threshold for parallelism vale 5 y max degree of parallelism, 0 (todos los núcleos). El umbral es tan bajo que un recorrido mediano ya puede ir en paralelo: en otra tabla de 200 000 filas, uno usó 16 hilos, 2 094 ms de CPU para 152 ms de reloj. CXPACKET alto no es un error; es la señal de que esos dos valores se quedaron como vinieron.

Lo que ya está en otros temas
- Un parámetro nvarchar contra una columna varchar con colación SQL_ pasa de 3 lecturas a 334: «Codificación y colaciones (SQL Server)».
- Key Lookup, cuándo el plan deja el índice por la tabla, YEAR(fecha) frente al rango, y los índices que el servidor echa en falta: «Índices (SQL Server)».
- Una restricción CHECK sin confianza el optimizador no la usa (1 364 lecturas frente a ninguna): «Cambios de esquema en caliente (SQL Server)».
- Cuántas particiones leyó una consulta sólo lo dice el plan real: «Particionado (SQL Server)».

Recomendación
El orden que funciona: encontrar la consulta en Consultas lentas (o en Query Store, si hace falta la historia), pedir su plan real con SET STATISTICS XML ON y comparar EstimateRows con ActualRows. Si se separan y el valor compilado no es el de ahora, es detección de parámetros; si se separan con el mismo valor, son estadísticas; si coinciden y aun así se lee mucho, falta un índice. Tocar la memoria o el paralelismo antes de haber leído un plan es el camino largo.

Palabras clave: rendimiento, statistics io, statistics time, lecturas lógicas, showplan_xml, statistics xml, plan de ejecución, plan real, detección de parámetros, parameter sniffing, recompile, optimize for unknown, sp_recompile, dm_exec_query_stats, query_hash, caché de planes, query store, dm_os_wait_stats, esperas, paralelismo

Los tres modelos de recuperación y cuál deja crecer el registro, BACKUP DATABASE y BACKUP LOG en el disco del servidor, la cadena de registro, volver al minuto anterior al DELETE con STOPAT, COPY_ONLY, y en qué se diferencia el respaldo de Calíope.

Aplica a: SQL Server 2022+

Si vienes de MySQL, aquí no hay mysqldump ni binlog aparte: el respaldo físico es una sentencia T-SQL, y la historia que permite avanzar desde él es el registro de transacciones de cada base. Si vienes de PostgreSQL, la diferencia es la unidad: se respalda y se restaura una base, no el servidor entero. Todo lo que cuento aquí lo medí contra SQL Server 2025.

El modelo de recuperación decide casi todo
Es una propiedad de cada base, y hay tres:

Una base nueva copia el modelo de model, que en este servidor es FULL:

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases;

FULL sin respaldos de registro llena el disco
Aquí está la trampa. Mientras la base no tiene su primer respaldo completo, FULL se porta como SIMPLE: inserté 20 000 filas de 2 kB, el registro llegó a 42,2 MB usados y un CHECKPOINT lo bajó a 8,1 MB. Después del primer BACKUP DATABASE, lo mismo ya no liberó nada: 55,4 MB, luego 102,8 MB, y el archivo creció de 72 a 136 MB. log_reuse_wait_desc dice por qué no se reutiliza:

- LOG_BACKUP — espera un BACKUP LOG. Es el motivo de casi todos los discos llenos.
- ACTIVE_TRANSACTION — una transacción abierta que nadie cierra.
- CHECKPOINT — nada que temer, se libera en el siguiente.

BACKUP LOG bajó lo usado a 15,1 MB, pero el archivo se quedó en 136 MB: respaldar el registro libera espacio dentro, no lo devuelve al disco. Si una base está en FULL y nadie programa BACKUP LOG, o se programa o se pasa a SIMPLE.

Respaldar

BACKUP DATABASE tienda TO DISK = '/var/opt/mssql/backup/tienda_full.bak'
    WITH CHECKSUM, INIT;

BACKUP DATABASE tienda TO DISK = '/var/opt/mssql/backup/tienda_dif.bak'
    WITH DIFFERENTIAL, CHECKSUM, INIT;

BACKUP LOG tienda TO DISK = '/var/opt/mssql/backup/tienda_0930_1200.trn'
    WITH CHECKSUM, INIT;

La ruta es del disco del servidor, no del tuyo: el archivo lo escribe el proceso de SQL Server. En Linux la carpeta habitual es /var/opt/mssql/backup/; en el contenedor de prueba no existía, y el primer BACKUP la creó.

- El completo copia la base entera: 150 MB en mi prueba.
- La diferencial copia lo cambiado desde el último completo: 3,1 MB tras cambiar 2 000 filas.
- El de registro copia el registro desde el anterior, y es lo que permite ir a un instante.

CHECKSUM no viene puesto (backup checksum default vale 0), y se nota al verificar: RESTORE VERIFYONLY … WITH CHECKSUM sobre un respaldo hecho sin él dio Msg 3187. Ponlo siempre, y verifica:

RESTORE VERIFYONLY FROM DISK = '/var/opt/mssql/backup/tienda_full.bak'
    WITH CHECKSUM;

La cadena de registro
Cada copia de registro empieza donde acabó la anterior, y se restauran todas, en orden. Saltarse una dio Msg 4305, con el LSN que falta. Dos cosas la rompen sin avisar:

- Pasar a SIMPLE y volver a FULL: después, BACKUP LOG dio Msg 4214 hasta el completo siguiente, aunque hubiera uno de antes.
- Un BACKUP LOG de alguien más, a un archivo que no conoces. El historial está en msdb:

SELECT b.type, b.is_copy_only, b.first_lsn, b.last_lsn,
       b.backup_finish_date, m.physical_device_name
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON m.media_set_id = b.media_set_id
WHERE b.database_name = 'tienda'
ORDER BY b.backup_start_date;

COPY_ONLY: la copia que no molesta
Un completo normal se convierte en la base de las diferenciales siguientes. Si haces uno suelto —para llevarte la base a otra máquina—, la diferencial de esta noche ya no casa con el completo del domingo. Con WITH COPY_ONLY no pasa: la diferencial siguiente siguió apuntando al completo anterior, y restaurarla encima del COPY_ONLY dio Msg 3136. Toda copia fuera de la rutina, con COPY_ONLY.

Volver al minuto anterior al DELETE
Lo reproduje entero: 500 pedidos, un DELETE sin WHERE cuatro segundos después, y la recuperación en una base nueva, sin tocar la original:

RESTORE DATABASE tienda_rec FROM DISK = '/var/opt/mssql/backup/tienda_full.bak'
    WITH NORECOVERY,
    MOVE 'tienda' TO '/var/opt/mssql/data/tienda_rec.mdf',
    MOVE 'tienda_log' TO '/var/opt/mssql/data/tienda_rec_log.ldf';

RESTORE LOG tienda_rec FROM DISK = '/var/opt/mssql/backup/tienda_log1.trn'
    WITH NORECOVERY;

RESTORE LOG tienda_rec FROM DISK = '/var/opt/mssql/backup/tienda_log2.trn'
    WITH STOPAT = '2026-09-30T17:50:09.926', RECOVERY;

Volvieron los 500. Lo que hay que saber:

- NORECOVERY deja la base en RESTORING, esperando más: leerla da Msg 927. El último paso lleva RECOVERY.
- MOVE es obligatorio al restaurar con otro nombre, o choca con los archivos de la original. Sus nombres lógicos los da RESTORE FILELISTONLY.
- STOPAT es hora del servidor, y con tres decimales: con siete dio Msg 3217.
- Restaurar encima de la base viva sin respaldar antes la cola del registro dio Msg 3159. La cola es lo ocurrido desde el último BACKUP LOG; se respalda con BACKUP LOG … WITH NORECOVERY, que además deja la base en RESTORING.

BULK_LOGGED y su precio
Un SELECT … INTO de una tabla de 21 MB escribió 22,1 MB de registro en FULL y 1,0 MB en BULK_LOGGED. Pero la copia de registro siguiente pesó lo mismo, 22,1 MB, porque se lleva las páginas cargadas; y restaurar esa copia con STOPAT dio Msg 4341: dentro de un intervalo con carga masiva sólo se puede llegar al final, no a un instante. Se pasa a BULK_LOGGED para la carga y se vuelve a FULL justo después, con un BACKUP LOG a cada lado.

Restaurar en otro servidor
Un respaldo de base lleva sus usuarios, pero no los logins del servidor, que viven en master. Restaurado en otra máquina, cada usuario queda huérfano de su login; cómo encontrarlos y volver a unirlos está en el tema de seguridad de SQL Server.

Lo que hace Calíope
Respaldo escribe un guion de texto, no un .bak: sentencias con GO entre lotes, filas en INSERT de mil en mil, fechas en ISO 8601 y binarios en 0x…, para que vuelva igual en otro servidor, en otra versión o con la sesión en otro idioma. Con Incluir contraseñas de usuarios, los logins van como CREATE LOGIN … WITH PASSWORD = 0x… HASHED, y el usuario de cada base con CREATE USER … FOR LOGIN: al restaurar el guion, no quedan huérfanos. Una tabla particionada vuelve particionada: el guion crea antes, si no existen, su función y su esquema de partición.

Desde el Editor SQL puedes lanzar un BACKUP DATABASE —es una sentencia más—, pero el archivo se queda en el disco del servidor: Calíope no lo trae.

Recomendación
Decide el modelo por base: SIMPLE donde perder lo de hoy sea aceptable, y FULL con BACKUP LOG programado donde no. Completo semanal, diferencial diaria y registro cada pocos minutos es la rutina de siempre, siempre WITH CHECKSUM. Vigila log_reuse_wait_desc, haz COPY_ONLY toda copia suelta, y restaura de verdad, en otro nombre, de vez en cuando: un respaldo que no se ha restaurado nunca es una intención. El respaldo de Calíope sirve para llevarte una base o parte de ella a otra máquina; no sustituye a los respaldos del servidor.

Palabras clave: respaldo, backup, restore, restauración, recuperación, modelo de recuperación, full, simple, bulk_logged, registro de transacciones, log_reuse_wait_desc, backup log, cadena de registro, norecovery, stopat, copy_only, diferencial, checksum, verifyonly, 3159, 4214, 4341

Login de servidor y usuario de base, por qué un usuario queda huérfano al restaurar en otro servidor y cómo se vuelve a unir, conceder por esquema, DENY y quién se lo salta, la cadena de propiedad, usuarios contenidos, seguridad de fila y qué hacer con sa.

Aplica a: SQL Server 2022+

Si vienes de MySQL, aquí la cuenta no lleva el host dentro y, además, son dos cosas: el login, que es del servidor y con el que se entra, y el usuario, que es de cada base y el que recibe los permisos. Si vienes de PostgreSQL, es como si cada rol con LOGIN tuviera que existir otra vez dentro de cada base para poder usarla. Todo lo que cuento aquí lo medí contra SQL Server 2025.

Login y usuario

CREATE LOGIN ana WITH PASSWORD = 'Xq7#lunes_pw9';   -- servidor: entrar
USE tienda;
CREATE USER ana FOR LOGIN ana;                    -- base: estar dentro

Con el login y sin el usuario, se entra al servidor pero no a la base: conectarse nombrándola dio «Cannot open database … The login failed» (4060), y un USE desde master dio Msg 916. El usuario se enlaza con su login por el sid, no por el nombre: ése es el origen de los huérfanos.

La contraseña pasa por la política (CHECK_POLICY, encendida por omisión): ocho caracteres de tres clases, y sin el nombre del login dentro. 'Xq7#ana_pw9' para el login ana dio Msg 33064, aunque cumple lo demás.

Usuarios huérfanos: restaurar en otro servidor
Lo reproduje: respaldé una base con el usuario ana, borré el login y lo volví a crear con el mismo nombre —como sería en otra máquina—, y restauré la base. ana ya no podía entrar en la copia: su usuario conservaba el sid viejo, y el login nuevo tenía otro. Crearlo otra vez tampoco: Msg 15023, el usuario ya existe. Encontrarlos:

SELECT dp.name, dp.type_desc
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON sp.sid = dp.sid
WHERE dp.type IN ('S', 'U', 'G')
  AND dp.authentication_type_desc = 'INSTANCE'
  AND sp.sid IS NULL;

Y volver a unirlos, sin perder sus permisos:

ALTER USER ana WITH LOGIN = ana;

Dos cosas más. DROP LOGIN no comprueba que el login tenga usuarios: lo borró sin una advertencia y dejó huérfano el usuario en la base original, en el mismo servidor. Y para que no pase al llevar una base a otra máquina, el login se crea allí con el mismo sid (CREATE LOGIN ana WITH PASSWORD = …, SID = 0x1C2F…): con él, el usuario de la base restaurada volvió a funcionar sin tocarlo.

Roles
Hay 18 roles fijos de servidor: los ocho de siempre (sysadmin, serveradmin, securityadmin, processadmin, setupadmin, bulkadmin, diskadmin, dbcreator) y diez ##MS_…## desde 2022, más finos (##MS_ServerStateReader## para ver el estado sin tocar nada). En cada base hay nueve fijos: db_owner, db_datareader, db_datawriter, db_ddladmin, db_securityadmin, db_accessadmin, db_backupoperator y los dos que niegan, db_denydatareader y db_denydatawriter. Los roles propios se crean con CREATE ROLE y se llenan con ALTER ROLE … ADD MEMBER.

Y public, al que pertenece todo el mundo. En el servidor tiene VIEW ANY DATABASE: ana veía los nombres de las diez bases del servidor, aunque sólo podía entrar en cuatro. Si los nombres son información, se le quita a public.

Conceder por esquema cubre también lo de mañana

GRANT SELECT ON SCHEMA::rrhh TO ana;

Con eso ana leyó las tablas de rrhh, y también la que creé después. Es lo contrario de PostgreSQL, donde ON ALL TABLES sólo alcanza a las de hoy. Y lo que está fuera del esquema sigue cerrado: dbo.pedidos dio Msg 229.

GRANT, DENY y REVOKE: tres estados, no dos
- GRANT concede.
- DENY prohíbe, y gana a cualquier GRANT: con DENY SELECT sobre rrhh.nominas, ana dio Msg 229 teniendo el esquema concedido, y también siendo miembro de db_datareader.
- REVOKE borra lo que haya, sea un GRANT o un DENY. Tras el REVOKE, ana volvió a leer, por el rol.

Quién se salta un DENY lo medí uno a uno:
- Un miembro de db_owner: no se lo salta, dio Msg 229.
- Un login con CONTROL SERVER: tampoco, Msg 229.
- Un miembro de sysadmin: sí. Entra en cada base como dbo (USER_NAME() lo dice) y no se le comprueba ningún permiso, así que leyó la tabla denegada.

Qué puedo hacer, preguntado al servidor

EXECUTE AS USER = 'ana';
SELECT permission_name, subentity_name
FROM fn_my_permissions('rrhh.nominas', 'OBJECT');
SELECT HAS_PERMS_BY_NAME('rrhh.nominas', 'OBJECT', 'UPDATE');
REVERT;

fn_my_permissions devolvió SELECT cuatro veces: una por la tabla y una por cada una de sus tres columnas. Es la forma de comprobar un permiso efectivo —con roles, esquemas y DENY ya resueltos— sin pedirle la contraseña a nadie.

La cadena de propiedad
Si una vista o un procedimiento y la tabla que leen tienen el mismo dueño, quien puede usar la vista no necesita permiso sobre la tabla. ana, sin nada sobre dbo.pedidos, leyó por la vista dbo.v_totales y por el procedimiento dbo.p_total. Es la forma buena de dar acceso: sólo a lo que el objeto enseña. Pero el SQL dinámico rompe la cadena: el mismo procedimiento con un EXEC('SELECT …') dentro dio Msg 229.

Usuarios contenidos
En una base contenida el usuario lleva su contraseña y no necesita login, así que viaja con la base y no queda huérfano:

EXEC sp_configure 'contained database authentication', 1;
RECONFIGURE;
CREATE DATABASE tienda CONTAINMENT = PARTIAL;
-- dentro de tienda:
CREATE USER carla WITH PASSWORD = 'Rt5%jueves_z2';

La opción viene apagada. Con ella, carla entró nombrando la base al conectarse; sin nombrarla, «Login failed». En Calíope, eso es poner la base en el campo Base de datos de la conexión.

Seguridad de fila
Una política con un predicado de filtro:

CREATE FUNCTION seg.fn_lo_mio(@dueno sysname)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN SELECT 1 AS ok WHERE @dueno = USER_NAME();

CREATE SECURITY POLICY seg.solo_lo_mio
ADD FILTER PREDICATE seg.fn_lo_mio(dueno) ON dbo.pedidos
WITH (STATE = ON);

ana vio su fila. Y a diferencia de PostgreSQL, aquí nadie se la salta: sa y un sysadmin vieron cero filas, porque para ellos USER_NAME() es dbo. El predicado decide, y si el administrador tiene que ver todo, lo tiene que decir el predicado. Además, un filtro sólo filtra lo que se lee: ana insertó una fila de otro dueño sin error. Para impedirlo hace falta un BLOCK PREDICATE.

sa
sa es el login con sid 0x01, miembro de sysadmin, y su nombre lo conoce todo el que intenta entrar. Una contraseña mala da 18456 en el cliente, siempre con estado 1; el motivo sólo está en el registro del servidor, donde el mismo intento aparece con estado 8, contraseña equivocada. Lo habitual es crear un login de administración con nombre propio y luego ALTER LOGIN sa DISABLE.

Lo que Calíope no hace
Calíope sólo habla autenticación SQL: ni autenticación de Windows ni Microsoft Entra ID.

Lo que hace Calíope
Usuarios lista los logins del servidor, con public como una cuenta más, y sa aparte, marcado como Usuario del sistema. Los permisos se ven por nivel —servidor, base, esquema, tabla, rutina—, y un DENY se dibuja como tercer estado: con su glifo, tachado y con la palabra DENY, no sólo con otro color. No se puede desmarcar, porque quitarlo no es desmarcar sino un REVOKE. Cuenta bloqueada es ALTER LOGIN … DISABLE. Un usuario de base sin login, huérfano o contenido, no aparece: para ésos, las consultas de arriba en el Editor SQL.

Recomendación
Un login por persona o aplicación, y permisos por esquema a roles, no a personas. sysadmin sólo para administrar, porque a ése no le alcanza ningún DENY. Lo que se pueda dar por una vista o un procedimiento, que vaya por ahí y no con el SQL dinámico dentro. Al mover una base a otra máquina, los logins con su sid o la consulta de huérfanos justo después de restaurar. Y sa, deshabilitado.

Palabras clave: seguridad, login, usuario, user, rol, role, sysadmin, db_owner, db_datareader, public, grant, deny, revoke, schema, esquema, fn_my_permissions, has_perms_by_name, usuario huérfano, orphaned user, alter user with login, sid, usuario contenido, contained database, cadena de propiedad, ownership chaining, row-level security, security policy, sa, check_policy, 229, 916, 4060, 15023, 18456, 33064

sp_configure y RECONFIGURE, por qué value y value_in_use no coinciden, las opciones que exigen reiniciar, el techo de memoria que no viene puesto, dónde gana cada MAXDOP, la configuración por base, tempdb y mssql-conf.

Aplica a: SQL Server 2022+

Si vienes de MySQL, aquí no hay my.cnf ni SET GLOBAL: lo del servidor se escribe con sp_configure y entra en vigor con RECONFIGURE, y lo de una base, con ALTER DATABASE SCOPED CONFIGURATION. Si vienes de PostgreSQL, sys.configurations es tu pg_settings, y la columna que importa no es source: es la diferencia entre value y value_in_use. Todo lo que cuento aquí lo medí contra SQL Server 2025.

107 opciones, y 74 escondidas
sys.configurations tiene 107 opciones en este servidor, y sp_configure a secas enseña 33. Las otras 74 son avanzadas: hasta encender show advanced options ni se listan ni se pueden cambiar. sp_configure 'max degree of parallelism', 4 dio Msg 15123, «does not exist, or it may be an advanced option».

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

Escrito no es aplicado: value y value_in_use
sp_configure sólo escribe el valor, y el servidor sigue con el anterior hasta el RECONFIGURE. Tras sp_configure 'cost threshold for parallelism', 50, sys.configurations decía value 50 y value_in_use 5, y el propio servidor avisaba: «Run the RECONFIGURE statement to install».

SELECT name, value, value_in_use, is_dynamic, is_advanced
FROM sys.configurations
WHERE value <> value_in_use;

Esa consulta se corre antes de cada RECONFIGURE, por dos cosas que medí:
- RECONFIGURE instala todo lo pendiente, no sólo lo tuyo: si alguien dejó un valor escrito, entra con el tuyo.
- Un RECONFIGURE que falla deja lo escrito pendiente. Con min server memory (MB) en 1 024 y max server memory (MB) en 128 dio Msg 5831, y el 1 024 se quedó en value esperando al siguiente.

Hay un caso con salvoconducto: recovery interval (min) a 120 dio Msg 5807 («not recommended»), y RECONFIGURE WITH OVERRIDE lo aplicó igual. WITH OVERRIDE se salta justo la comprobación que acaba de avisar, así que no se usa por costumbre.

Las que exigen reiniciar
Las que tienen is_dynamic = 0 no entran con RECONFIGURE: aquí son 23 de las 107, entre ellas user connections, fill factor (%), locks y priority boost. Con sp_configure 'user connections', 500 y RECONFIGURE, value quedó en 500 y value_in_use en 0, hasta reiniciar el servicio.

Cada cambio queda en el registro del servidor, con hora y sesión: EXEC xp_readerrorlog 0, 1, N'Configuration option' los lista con su «changed from 0 to 4».

La memoria: max server memory viene sin techo
max server memory (MB) vale 2 147 483 647 de fábrica, o sea, ninguno: SQL Server toma memoria para su caché hasta que el sistema le pide que la suelte. En un servidor dedicado se le pone un techo que deje sitio al sistema operativo. El mínimo que acepta es 128; con 100 dio Msg 15129.

EXEC sp_configure 'max server memory (MB)', 12288;   -- 12 GB de 16
RECONFIGURE;

En Linux hay otro techo por debajo, memory.memorylimitmb de mssql-conf, que según la documentación es de fábrica el 80 % de la memoria física. Este contenedor ve 6 347 MB, y con max server memory sin tocar su objetivo (committed_target_kb de sys.dm_os_sys_info) era de 5 139 MB.

Paralelismo: tres niveles, y gana el más cercano
De fábrica, max degree of parallelism vale 0 (todos los procesadores) y cost threshold for parallelism, 5. Lancé la misma consulta sobre 300 000 filas y leí el DegreeOfParallelism del plan real:
- Con el servidor en 0: 15 hilos, todos los procesadores que ve.
- Con el servidor en 4: 4.
- Con la base en ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 2: 2, aunque el servidor diga 4.
- Con OPTION (MAXDOP 8) en la consulta: 8, por encima de la base y del servidor.
- Con cost threshold for parallelism en 50: en serie. El umbral se compara con el coste del plan en serie (aquí 7,7), no con el del paralelo.

Cómo se nota el paralelismo en las esperas lo cuento en «Rendimiento y diagnóstico (SQL Server)».

Configuración por base
sys.database_scoped_configurations tiene 41 opciones en demo. Además de MAXDOP, están LEGACY_CARDINALITY_ESTIMATION (0), PARAMETER_SNIFFING (1) y QUERY_OPTIMIZER_HOTFIXES (0), entre otras. Son la forma de cambiar el comportamiento de una aplicación sin tocar a las demás del servidor.

SELECT name, value
FROM sys.database_scoped_configurations
WHERE is_value_default = 0;

tempdb
Este servidor ve 15 procesadores, y tempdb tiene 8 archivos de datos de 8 MB, más el del registro, y crecen de 64 en 64 MB: la instalación ya pone uno por procesador, hasta ocho. Es la base de las tablas #temporales, de las ordenaciones que no caben en memoria y de las versiones de fila de READ_COMMITTED_SNAPSHOT.

Lo que no es sp_configure: mssql-conf, en Linux
Las rutas por omisión de datos, registro y respaldos, el certificado TLS, el techo de memoria o el idioma viven en /var/opt/mssql/mssql.conf, y se cambian con /opt/mssql/bin/mssql-conf set … y un reinicio. El de este contenedor tiene sólo dos líneas: el certificado y la clave TLS, que es lo que SQL Server 2022 y posteriores piden para el TLS estricto (TDS 8.0). En Windows, lo mismo se hace en el Administrador de configuración de SQL Server.

Lo que enseña Calíope
Información del servidor, en Variables globales, lista sys.configurations con su value_in_use —lo que está en marcha, no lo escrito y pendiente— y las propiedades SERVERPROPERTY.*: versión, edición, colación. Variables sesión son las opciones SET de tu conexión. Calíope las lee y no las cambia: un sp_configure se escribe en el Editor SQL, y tras el RECONFIGURE se ve en Información del servidor al refrescar. Consultas lentas enseña en sus ajustes optimize for ad hoc workloads y max server memory (MB). Y contained database authentication, la opción que admite usuarios con contraseña propia, la cuento en «Logins, usuarios y permisos (SQL Server)».

Recomendación
Pocas y midiendo. max server memory siempre, dejando sitio al sistema; cost threshold for parallelism por encima de 5; y MAXDOP por base cuando una aplicación lo pide, no para todo el servidor. Antes de cada RECONFIGURE, la consulta de value <> value_in_use: así se aplica lo que se quiere y no lo que otro dejó a medias.

Palabras clave: configuración, sp_configure, reconfigure, with override, sys.configurations, value_in_use, show advanced options, is_dynamic, max server memory, min server memory, memorylimitmb, mssql-conf, maxdop, max degree of parallelism, cost threshold for parallelism, database scoped configuration, tempdb, user connections, xp_readerrorlog, 15123, 15129, 5807, 5831

Por qué BEGIN TRAN dentro de otra no anida, cómo se vuelve atrás con SAVE TRANSACTION, qué bloquea cada nivel, el 3960 de SNAPSHOT y lo que una transacción olvidada le hace al registro.

Aplica a: SQL Server 2022+

Una transacción agrupa varias sentencias en una unidad que se aplica entera o no se aplica. Como en MySQL y en PostgreSQL, sin una transacción explícita SQL Server confirma cada sentencia por su cuenta (autocommit). Lo que cambia está en los detalles, y todo lo que cuento aquí lo medí contra SQL Server 2025.

BEGIN TRAN, no BEGIN
BEGIN a secas abre un bloque de T-SQL, no una transacción: el servidor contestó Msg 102, «Incorrect syntax near 'BEGIN'». Se escribe BEGIN TRAN (o BEGIN TRANSACTION). Un COMMIT sin transacción abierta da Msg 3902, y un ROLLBACK, Msg 3903.

El modo implícito
Con SET IMPLICIT_TRANSACTIONS ON, la primera sentencia que toca datos abre una transacción que sigue abierta hasta el COMMIT. Lo medí: @@TRANCOUNT valía 0, un simple SELECT lo subió a 1, y sólo el COMMIT lo devolvió a 0. Una sesión que olvida confirmar en este modo retiene sus bloqueos y el registro, como cuento más abajo.

Anidar no anida
Un BEGIN TRAN dentro de otra sólo sube el contador @@TRANCOUNT. Lo que decide es la de fuera:
- El COMMIT interior no confirma nada: bajó el contador de 2 a 1, y el ROLLBACK de fuera deshizo los dos UPDATE, también el de dentro.
- El ROLLBACK interior deshace todo: dejó @@TRANCOUNT en 0, y el COMMIT de fuera dio Msg 3902 porque ya no había transacción.
- Nombrar la de dentro no ayuda: ROLLBACK TRAN interior dio Msg 6401 y el contador siguió en 2. Sólo se puede volver atrás hasta un punto de guardado.

BEGIN TRAN;                                  -- @@TRANCOUNT = 1
  UPDATE cuentas SET saldo = saldo - 10 WHERE id = 1;
  BEGIN TRAN;                                -- @@TRANCOUNT = 2
    UPDATE cuentas SET saldo = saldo + 10 WHERE id = 2;
  COMMIT;                                    -- @@TRANCOUNT = 1, nada confirmado
ROLLBACK;                                    -- deshace los dos UPDATE

Un procedimiento que termina con otro @@TRANCOUNT del que tenía al entrar da Msg 266: lo cuento en «Errores (SQL Server)».

Puntos de guardado: SAVE TRANSACTION
Para deshacer una parte sin perder la transacción, se marca un punto y se vuelve a él. Aquí el INSERT duplicado falló con 2627, volví al punto, y el COMMIT confirmó la fila de antes y la de después:

BEGIN TRAN;
  INSERT INTO cuentas (id, saldo) VALUES (3, 0);
  SAVE TRANSACTION antes;
  BEGIN TRY
    INSERT INTO cuentas (id, saldo) VALUES (1, 0);   -- falla: 2627
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION antes;                      -- @@TRANCOUNT sigue en 1
  END CATCH
  INSERT INTO cuentas (id, saldo) VALUES (5, 5);
COMMIT;

Dos límites que medí: SAVE TRANSACTION sin transacción abierta da Msg 628, y con XACT_ABORT ON el mismo error dejó la transacción condenada —XACT_STATE() -1— y volver al punto dio Msg 3931: sólo quedaba deshacerla entera.

Un error casi nunca aborta la transacción
Al revés que en PostgreSQL, un error de sentencia —una clave duplicada, una restricción— aborta sólo esa sentencia, y la transacción sigue viva con lo que ya hizo. Sin XACT_ABORT, un COMMIT al final confirma lo de antes y lo de después del error. Con SET XACT_ABORT ON, el mismo error deshace todo y corta el lote. Qué errores deshacen la transacción por su cuenta lo cuento en «Errores (SQL Server)».

Los niveles de aislamiento
El predeterminado es READ COMMITTED. Lo medí con dos sesiones: la primera lee cuatro filas de un rango, espera cuatro segundos y las relee; la segunda, entretanto, inserta una fila en ese rango y actualiza una de las leídas.
- READ UNCOMMITTED lee lo que otro no ha confirmado: es el NOLOCK de «Bloqueos y deadlocks (SQL Server)».
- READ COMMITTED no retiene los bloqueos de lo que lee. Sin READ_COMMITTED_SNAPSHOT, que viene apagado, una lectura espera a quien escribe; lo cuento en «Bloqueos y deadlocks (SQL Server)».
- REPEATABLE READ retuvo cinco bloqueos compartidos (KEY S) hasta el final: el UPDATE de la fila leída esperó 3 s. Pero el INSERT entró al instante, y la relectura contó 5 filas en vez de 4: una fila fantasma.
- SERIALIZABLE bloqueó el rango (KEY RangeS-S): el INSERT esperó 3 s y la relectura siguió contando 4.
- SNAPSHOT lee una foto del principio de la transacción y no bloquea a nadie, pero hay que permitirlo en cada base, y viene apagado: snapshot_isolation_state_desc era OFF en demo, en model y en una base nueva. Sin permiso, SET TRANSACTION ISOLATION LEVEL SNAPSHOT y BEGIN TRAN pasaron, y fue el primer SELECT el que dio Msg 3952.

ALTER DATABASE mi_base SET ALLOW_SNAPSHOT_ISOLATION ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

SNAPSHOT no espera: aborta con 3960
Con el permiso puesto, la segunda sesión actualizó la fila sin esperar a la primera, que sólo la había leído. La primera la releyó y siguió viendo el valor de su foto, 100, con el 101 del otro ya confirmado. Cuando quiso actualizarla, el servidor la abortó con Msg 3960 («update conflict»), y XACT_STATE() quedó en -1. En SNAPSHOT, un 3960 es funcionamiento normal: la aplicación tiene que reintentar la transacción entera. READ_COMMITTED_SNAPSHOT es otra cosa: no cambia de nivel, cambia cómo lee READ COMMITTED, y es lo que cuento en «Bloqueos y deadlocks (SQL Server)».

Una transacción olvidada retiene el registro
Una sesión dormida dentro de una transacción (status = sleeping, open_transaction_count = 1) no deja reutilizar el registro. Inserté y borré 5 000 filas y lancé un CHECKPOINT, en una base donde el CHECKPOINT vacía el registro. Sin esa sesión quedaron 6,5 MB usados; con ella, 73,8 MB, y log_reuse_wait_desc decía ACTIVE_TRANSACTION. Al cerrarla, 5,1 MB. El resto del ciclo del registro lo cuento en «Respaldo y recuperación (SQL Server)». Para encontrarla:

DBCC OPENTRAN;   -- la más antigua: SPID y hora de inicio

SELECT session_id, status, open_transaction_count, last_request_end_time
FROM sys.dm_exec_sessions
WHERE open_transaction_count > 0;

El DDL también se deshace
ALTER TABLE, CREATE INDEX y CREATE TABLE van dentro de la transacción, con sus excepciones (CREATE DATABASE da Msg 226): lo cuento en «Cambios de esquema en caliente (SQL Server)».

Lo que hace Calíope
El Editor SQL ejecuta lo que le das sobre una sola conexión, sentencia a sentencia, y para en la primera que falla. Sin transacción del menú, al acabar devuelve la conexión al pool, y si quedó una transacción abierta la deshace (IF @@TRANCOUNT > 0 ROLLBACK): un BEGIN TRAN y su COMMIT escritos a mano van en la misma ejecución, y lo que iba antes de un error se deshace al devolver la conexión. En sqlcmd o en una aplicación, se habría confirmado.
Para una transacción que dure varias ejecuciones está el menú Transacción de la barra: BEGIN manda BEGIN TRANSACTION y reserva una conexión para la pestaña, y lo que ejecutes en ella va dentro hasta el COMMIT o el ROLLBACK. Cerrar la pestaña la deshace. Si el servidor la deshace solo —un 245, un 1205 o cualquier error con XACT_ABORT— o la cierra un COMMIT de tu SQL, la etiqueta Transacción activa se apaga y la barra de estado lo avisa: desde ahí, cada sentencia se confirma al momento.

Recomendación
SET XACT_ABORT ON en todo lote o procedimiento que escriba, y un TRY…CATCH que termine en IF @@TRANCOUNT > 0 ROLLBACK; y THROW;. No anides BEGIN TRAN esperando que la de dentro sea independiente: para eso están los puntos de guardado. Si subes a SNAPSHOT, escribe el reintento del 3960 antes de subir. Y de vez en cuando, DBCC OPENTRAN: una sesión olvidada no avisa hasta que se llena el disco.

Palabras clave: transacción, commit, rollback, begin tran, @@trancount, save transaction, savepoint, xact_abort, xact_state, implicit_transactions, aislamiento, repeatable read, serializable, snapshot, allow_snapshot_isolation, 3960, 3952, 3902, 6401, 3931, active_transaction, dbcc opentran

Cuándo meter los datos dentro del documento y cuándo referenciarlos: los cuatro patrones de MongoDB, el antipatrón del array sin tope y lo que cuesta cada uno, medido.

Aplica a: MongoDB 7.0+

En SQL el esquema se deriva de la normalización: cada hecho en un solo sitio, y las consultas lo recomponen con JOIN. En MongoDB se deriva del patrón de acceso: lo que se lee junto, se guarda junto. La pregunta ya no es «¿cómo evito repetir un dato?», sino «¿qué quiero que me devuelva una sola lectura?».

Embebido frente a referencia

Un pedido puede llevar sus líneas dentro, o las líneas pueden vivir en su propia colección apuntando al pedido.

// Embebido: el pedido lleva sus líneas dentro
db.p_emb.insertOne({ _id: 1, cliente: 7, fecha: new Date(),
  lineas: [ { sku: "A-7", cantidad: 2, precio: 19.9 } ] })
db.p_emb.findOne({ _id: 1 })

// Referencia: las líneas viven aparte y apuntan al pedido
db.p_ref.insertOne({ _id: 1, cliente: 7, fecha: new Date() })
db.l_ref.insertOne({ pedido: 1, sku: "A-7", cantidad: 2, precio: 19.9 })
db.l_ref.createIndex({ pedido: 1 })
db.p_ref.aggregate([ { $match: { _id: 1 } },
  { $lookup: { from: "l_ref", localField: "_id",
               foreignField: "pedido", as: "lineas" } } ])

Medido contra MongoDB 8.2 con 100 000 pedidos de tres líneas: leer un pedido con sus líneas cuesta 0,25 ms embebido y 0,30 ms con $lookup —pero 67 ms si falta el índice de l_ref.pedido, porque entonces cada pedido recorre las 300 000 líneas—. En disco, los pedidos embebidos ocupan 2,9 MB frente a los 1,1 MB + 5,2 MB de las dos colecciones separadas: aquí embeber salió más barato también en espacio.

Embeber no renuncia a los índices: un índice sobre un campo del array es multiclave y sirve igual. Buscar {"lineas.sku": "A-7"} sin él es un COLLSCAN de 100 000 documentos en 42 ms; con él, 2 000 examinados en 2 ms, para las mismas 2 000 filas. Y un documento se modifica de forma atómica sin transacción: el $inc de un contador y el $set del estado en un solo updateOne entran juntos o no entra ninguno.

El antipatrón: el array que no para de crecer

db.sensor.insertOne({ _id: 1, lecturas: [] })
const tanda = Array.from({ length: 10000 }, (_, i) => ({ t: new Date(), v: i }))
db.sensor.updateOne({ _id: 1 }, { $push: { lecturas: { $each: tanda } } })
db.sensor.stats().avgObjSize   // 288 919 bytes tras 10 000 lecturas

El documento vacío son 29 bytes y cada lectura suma 28,9. A las 540 000 mide 16 628 919 bytes y la tanda siguiente falla con el código 10334: «Resulting document after update is larger than 16777216». El tope de 16 MB por documento no se negocia. Y antes de llegar a él ya duele: un $set de un campo escalar cuesta 3,15 ms en ese documento y 0,50 ms en uno de 228 bytes, y un $pop del array, 71,95 ms frente a los 0,15 ms de insertar una lectura suelta. Un array que crece sin tope conocido es una referencia mal puesta.

Cubo: las lecturas se agrupan en documentos de N.

const lote = Array.from({ length: 200 }, (_, i) => ({ t: new Date(), v: i }))
db.cubos.insertOne({ sensor: 1, desde: new Date(), n: 200, lecturas: lote })
db.cubos.createIndex({ sensor: 1, desde: -1 })

Las mismas 540 000 lecturas: sueltas son 540 000 documentos, 6,5 MB de datos y 8,4 MB de índice; en cubos de 200 son 2 700 documentos, 4,2 MB de datos y 82 KB de índice. Insertarlas cuesta 203 ms en cubos frente a 901 ms sueltas. Leer la última: 0,20 ms del cubo, 0,30 ms suelta y 46,80 ms del array embebido, que hay que traerse entero.

Referencia extendida: copiar dentro del hijo el puñado de campos del padre que se enseñan siempre. Listar 100 pedidos con el nombre de su cliente cuesta 0,35 ms con el nombre duplicado dentro y 1,00 ms con $lookup. Se paga al escribir: renombrar un cliente obliga a tocar sus 1 000 pedidos, 2 ms con un índice en cliente. Se duplica lo que casi nunca cambia.

Valor calculado: guardar el total ya sumado en vez de recalcularlo en cada lectura. En un pedido no se nota —0,35 ms leído del documento, 0,30 ms sumado al vuelo—; al agregar los 100 000 sí: 14 ms leyendo el campo frente a 129 ms recomputando.

Subconjunto: dentro del documento sólo las pocas filas que se enseñan, el resto en su colección. 500 productos con 500 reseñas cada uno: embebidas todas, el documento medio son 80 738 bytes; con las cinco últimas y un contador, 889 bytes, y la colección baja de 6,1 MB a 60 KB. La ficha pasa de 0,55 ms a 0,25 ms, y la página de 20 reseñas se pide aparte, a su propia colección.

La regla, en una línea: embebe lo que se lee con su padre, le pertenece sólo a él y tiene tope; referencia lo que crece sin límite, se comparte entre varios padres o se consulta por su cuenta.

Palabras clave: modelado, embebido, referencia, documento, patrón, cubo, subconjunto, referencia extendida, valor calculado, array, esquema, 16 MB

Qué tipo guarda MongoDB en cada valor, cuánto ocupa, cómo se comparan tipos distintos entre sí y cómo se ordena texto con acentos.

Aplica a: MongoDB 7.0+

En SQL el tipo lo declara la tabla y todas las filas lo cumplen. En MongoDB el tipo viaja con cada valor: no hay CREATE TABLE, y dos documentos de la misma colección pueden llevar un entero y una cadena en el mismo campo. El formato se llama BSON, una extensión binaria de JSON con los tipos que a JSON le faltan: enteros de 32 y de 64 bits, decimales exactos, fechas, binarios e ObjectId.

_id y ObjectId

Todo documento tiene _id, único, inmutable y con índice desde que nace la colección. Si no lo escribes tú, el cliente pone un ObjectId: 12 bytes, de los cuales 4 son el tiempo en segundos, 5 son aleatorios por proceso y 3 son un contador. Crece con el reloj, así que sirve para rangos de fecha sin guardar ninguna fecha.

const oid = ObjectId()
oid.getTimestamp()            // 2026-09-12T01:28:05.000Z
oid.toString().slice(0, 8)    // "6aa4aaa5": los 4 bytes de tiempo

// Lo creado desde el 1 de septiembre, por el índice de _id
const desde = ObjectId.createFromTime(Date.parse("2026-09-01") / 1000)
db.pedidos.countDocuments({ _id: { $gte: desde } })

Guardarlo como cadena cuesta y no aporta: {_id: ObjectId()} pesa 22 bytes y el mismo valor en cadena, 39.

Enteros, dobles y decimales

Hay cuatro tipos numéricos y la diferencia se ve en el documento. Con $bsonSize sobre {_id: 1, a: …} salen 21 bytes con int, 25 con long o con double y 33 con decimal. El dinero va en decimal por el mismo motivo que en SQL va en DECIMAL: sumar 0,1 y 0,2 como dobles da 0.30000000000000004, y como decimales da 0.3.

La trampa está en el cliente. mongosh decide el tipo mirando el valor, así que un 7 escrito a mano se guarda como int, 3000000000 como double, y 9007199254740993 se guarda como 9007199254740992: pasado 2⁵³ hay que escribir NumberLong("…").

db.tipos.insertMany([
  { v: 7 }, { v: 7.5 }, { v: 3000000000 },
  { v: 9007199254740993 }, { v: NumberLong("9007199254740993") }
])
db.tipos.aggregate([ { $project: { v: 1, t: { $type: "$v" } } } ])

Para consultar, en cambio, los cuatro son un solo número: con un int, un long, un double y un decimal de valor 7 en la colección, {v: 7} los encuentra los cuatro, y {v: "7"} no encuentra ninguno. $type sí los distingue, y "number" vuelve a agruparlos.

Fechas

Date son 8 bytes de milisegundos desde 1970, siempre en UTC y sin zona horaria. No existe aquí la pareja DATETIME / TIMESTAMP: hay un solo tipo, la zona la pone quien lee, y los microsegundos se pierden al guardar. El Timestamp de BSON no es para tus datos, es el reloj interno de la replicación.

db.eventos.insertOne({ _id: 1, d: new Date("2026-09-11T21:05:00.123Z") })
db.eventos.aggregate([ { $project: {
  utc: { $hour: "$d" },
  mx:  { $hour: { date: "$d", timezone: "America/Mexico_City" } }
} } ])                        // utc 21 · mx 15

Binarios y UUID

BinData guarda bytes con un subtipo, y UUID() es el subtipo 4. Un UUID como BinData ocupa 29 bytes de documento; el mismo, escrito como cadena con guiones, 49.

El orden entre tipos distintos

Un sort sobre un campo con tipos mezclados no falla: hay un orden total entre tipos, y medido sobre catorce documentos es éste.

MinKey → campo ausente y null → números → cadenas → objetos → binarios → ObjectId → booleanos → fechas → Timestamp → expresiones regulares → MaxKey.

Los arrays no aparecen en esa lista porque un array compara por su elemento menor: [9, 10] se ordena entre los números. Y un campo ausente ordena igual que null, hasta el punto de que {v: null} encuentra las dos cosas; para separarlas van {v: {$type: "null"}} y {v: {$exists: false}}.

UTF-8 y cotejamiento

No hay juego de caracteres que elegir. Las cadenas de BSON son UTF-8 y punto: "café ☕ 日本語 👩‍💻" son 14 caracteres y 31 bytes, y vuelve tal como se escribió.

Lo que sí se elige es el cotejamiento, y por omisión es binario: sin collation, cafe y café son valores distintos, y Árbol ordena después de zorro. Un collation con su locale y su strength —1 ignora acentos y mayúsculas, 2 ignora sólo las mayúsculas, 3 lo distingue todo— cambia a la vez la comparación y el orden.

db.clientes.find({ n: "cafe" }).collation({ locale: "es", strength: 1 })
db.clientes.find().sort({ n: 1 }).collation({ locale: "es" })

Y aquí está la misma trampa que en SQL: el cotejamiento de la consulta tiene que ser el del índice. Con un índice normal sobre n, la consulta {n: "cafe"} es un IXSCAN que examina 1 documento; la misma consulta con collation cae a COLLSCAN y examina los 7. El arreglo no es escribir el collation en cada consulta, sino dárselo a la colección: sus índices nacen entonces con él.

db.createCollection("clientes", { collation: { locale: "es", strength: 1 } })
db.clientes.createIndex({ n: 1 }, { unique: true })
db.clientes.insertOne({ n: "cafe" })
db.clientes.insertOne({ n: "CAFÉ" })   // E11000: aquí es el mismo valor

Dos avisos más, los dos medidos. $regex ignora el cotejamiento: /^CAF/ no encuentra cafe ni con strength: 1, mientras que {n: "CAFE"} sí lo encuentra. Y numericOrdering: true hace que las cadenas "1", "2" y "10" se ordenen como números, en vez de "1", "10", "2".

Palabras clave: bson, tipos, objectid, decimal128, numberlong, fecha, date, uuid, bindata, collation, cotejamiento, utf-8, acentos, orden

Las once clases de índice de MongoDB y cuándo va cada una, la regla ESR para ordenar un compuesto, la consulta cubierta, y los topes y el precio de escritura, todo medido con `explain`.

Aplica a: MongoDB 7.0+

Un índice es un árbol B sobre el valor de un campo, y el número que dice si sirve sale de explain("executionStats"): totalDocsExamined frente a nReturned. Si el primero es mucho mayor que el segundo, el servidor está leyendo documentos para tirarlos.

const est = ["nuevo", "pagado", "enviado", "cerrado"]
const docs = []
for (let i = 0; i < 20000; i++) docs.push({
  cliente: i % 499, estado: est[i % 4], total: (i % 997) + 0.5,
  fecha: new Date(Date.UTC(2026, 0, 1 + (i % 240)))
})
db.pedidos.insertMany(docs)
const busca = { cliente: 42, estado: "pagado" }

db.pedidos.find(busca).explain("executionStats")   // COLLSCAN · 10 / 20000

db.pedidos.createIndex({ cliente: 1 })
db.pedidos.find(busca).explain("executionStats")   // IXSCAN · 10 / 40

db.pedidos.createIndex({ estado: 1, cliente: 1, fecha: -1 })
db.pedidos.find(busca).explain("executionStats")   // IXSCAN · 10 / 10

La regla ESR

Un compuesto se recorre por prefijos, así que el orden de sus campos es la decisión. Primero los campos de igualdad (E), luego el del orden (S) y al final el del rango (R). Con el rango antes que el orden, el índice filtra pero no ordena, y aparece una etapa SORT que ordena en memoria: 1 826 documentos, en la consulta de abajo.

const q = { estado: "pagado", fecha: { $gte: new Date("2026-06-01") } }

db.pedidos.createIndex({ estado: 1, fecha: 1, total: 1 })          // E-R-S
db.pedidos.find(q).sort({ total: 1 }).hint("estado_1_fecha_1_total_1").explain()
// FETCH <- SORT <- IXSCAN

db.pedidos.createIndex({ estado: 1, total: 1, fecha: 1 })          // E-S-R
db.pedidos.find(q).sort({ total: 1 }).hint("estado_1_total_1_fecha_1").explain()
// FETCH <- IXSCAN

Esa etapa tiene techo: internalQueryMaxBlockingSortMemoryUsageBytes vale 104 857 600 bytes, y al pasarlo la consulta falla si no se le permite usar disco.

Consulta cubierta

Si el índice lleva todos los campos que la consulta lee, el servidor no llega a tocar los documentos. Cuidado con _id: entra en la proyección por omisión, no está en el índice, y hay que quitarlo a mano.

db.pedidos.find({ estado: "pagado" }, { _id: 0, estado: 1, total: 1 })
  .hint("estado_1_total_1_fecha_1").explain("executionStats")
// PROJECTION_COVERED · nReturned 5000 · totalDocsExamined 0

db.pedidos.find({ estado: "pagado" }, { estado: 1, total: 1 })
  .hint("estado_1_total_1_fecha_1").explain("executionStats")
// totalDocsExamined 5000

Los otros tipos

Las dos que pierden datos en silencio se ven juntas:

db.socios.insertMany([{ a: 1 }, { a: 2 }, { b: 9 }, { b: 8 }])
db.socios.createIndex({ a: 1 }, { sparse: true })
db.socios.find().sort({ a: 1 }).hint("a_1")   // 2 / 4

db.correos.createIndex({ correo: 1 }, { unique: true })
db.correos.insertOne({ otro: 1 })
db.correos.insertOne({ otro: 2 })   // E11000 · dup key: { correo: null }

El oculto es lo contrario: se sigue manteniendo, pero el planificador no lo mira. Con hidden: true la misma consulta fue COLLSCAN de 20 000 documentos; devuelta a visible con collMod, IXSCAN de 1 940.

Los topes

for (let i = 0; i < 70; i++) db.tope.createIndex({ ["c" + i]: 1 })
// 67 CannotCreateIndex · add index fails, too many indexes

const k = {}
for (let i = 0; i < 33; i++) k["g" + i] = 1
db.comp.createIndex(k)   // 13103 · too many compound keys

El nombre de un índice y el tamaño de una clave no tienen tope práctico: 400 caracteres y 2 000 bytes se aceptaron sin protesta.

El precio

Cada índice se paga en cada escritura. Las mismas 20 000 inserciones tardaron 60 ms sin ningún índice, 101 ms con cinco y 160 ms con diez. Y ocupan sitio: los seis índices de pedidos suman 1,9 MB frente a 916 KB de datos.

Por eso no se indexa todo. Un campo de baja cardinalidad casi nunca ayuda: estado, con cuatro valores distintos, examina 5 000 documentos para devolver 5 000. Y un índice simple sobra si ya hay un compuesto que empieza por él: { cliente: 1 } y { cliente: 1, fecha: 1 } examinan los mismos 40.

Palabras clave: índice, índices, ESR, compuesto, multiclave, texto, 2dsphere, hashed, comodín, TTL, parcial, sparse, único, oculto, consulta cubierta, explain, IXSCAN, COLLSCAN, totalDocsExamined, MongoDB

Las etapas del pipeline y en qué orden ponerlas, $lookup como JOIN y lo que cuesta sin índice, $graphLookup, las funciones de ventana, $facet y $unionWith, y $merge frente a $out.

Aplica a: MongoDB 7.0+

Un pipeline es una lista de etapas, y cada una recibe los documentos que produjo la anterior. El orden lo escribes tú, y ahí está casi todo el rendimiento.

const est = ["nuevo", "pagado", "enviado", "cerrado"]
const docs = []
for (let i = 0; i < 20000; i++) docs.push({
  cliente: i % 499, estado: est[i % 4], total: (i % 997) + 0.5,
  fecha: new Date(Date.UTC(2026, 0, 1 + (i % 240)))
})
db.pedidos.insertMany(docs)

db.pedidos.aggregate([
  { $match: { estado: "pagado" } },
  { $group: { _id: "$cliente", gastado: { $sum: "$total" }, pedidos: { $sum: 1 } } },
  { $match: { pedidos: { $gte: 11 } } },
  { $sort: { gastado: -1 } },
  { $limit: 3 }
])
// { _id: 37, gastado: 522.5, pedidos: 11 } y dos mas

Filtrar primero, siempre

Sólo la primera etapa puede usar un índice. Con un índice {estado: 1} sobre esos 20 000 pedidos, el $match delante examina 5 000 claves y 5 000 documentos; el mismo filtro detrás del $group, ninguna clave y 20 000 documentos.

$lookup es el JOIN, y sin índice se paga

Une con otra colección de la misma base, y lo encontrado llega como un array que casi siempre se abre con $unwind.

const cl = []
for (let i = 0; i < 499; i++)
  cl.push({ num: i, nombre: "Cliente " + i, pais: ["MX", "ES", "AR", "CO"][i % 4] })
db.clientes.insertMany(cl)

const pais = [
  { $match: { estado: "pagado" } },
  { $lookup: { from: "clientes", localField: "cliente", foreignField: "num", as: "c" } },
  { $unwind: "$c" },
  { $group: { _id: "$c.pais", gastado: { $sum: "$total" } } }
]
db.pedidos.explain("executionStats").aggregate(pais)
// collectionScans 5001 - totalDocsExamined 2495499 - indexesUsed []

db.clientes.createIndex({ num: 1 })
db.pedidos.explain("executionStats").aggregate(pais)
// collectionScans 0 - totalDocsExamined 5001 - indexesUsed num_1

El explain de la etapa no deja margen: sin índice en clientes.num hace una pasada entera por cada documento de entrada, y con el índice no hace ninguna.

El recursivo y el de ventana

$graphLookup sigue una jerarquía hasta donde llegue, y depthField dice a qué distancia quedó cada escalón. El array que devuelve no viene ordenado: aquí salió Ana, Caro y Beto con niveles 2, 0 y 1. $setWindowFields (5.0+) es la ventana de SQL: partitionBy es el PARTITION BY y sortBy el ORDER BY.

db.empleados.insertMany([{ _id: 1, nombre: "Ana", jefe: null },
  { _id: 2, nombre: "Beto", jefe: 1 }, { _id: 3, nombre: "Caro", jefe: 2 },
  { _id: 4, nombre: "Dora", jefe: 3 }])
db.empleados.aggregate([
  { $match: { nombre: "Dora" } },
  { $graphLookup: { from: "empleados", startWith: "$jefe", connectFromField: "jefe",
                    connectToField: "_id", as: "cadena", depthField: "nivel" } }
])   // cadena: Ana(2), Caro(0), Beto(1)

db.pedidos.aggregate([
  { $match: { cliente: 42 } },
  { $setWindowFields: { partitionBy: "$estado", sortBy: { fecha: 1 },
      output: { acumulado: { $sum: "$total", window: { documents: ["unbounded", "current"] } },
                puesto: { $rank: {} } } } },
  { $match: { estado: "pagado" } },
  { $limit: 3 }
])   // acumulado 559.5 - 1113 - 1660.5

Varias respuestas de una sola pasada

$facet corre sub-pipelines sobre la misma entrada y devuelve un solo documento con todos; dentro de él ya no hay índice que valga. $unionWith es el UNION ALL.

db.devoluciones.insertMany([{ cliente: 42, total: 9.5 }, { cliente: 7, total: 4.25 }])
db.pedidos.aggregate([
  { $match: { cliente: 42, estado: "pagado" } },
  { $project: { _id: 0, cliente: 1, importe: "$total" } },
  { $unionWith: { coll: "devoluciones", pipeline: [
      { $match: { cliente: 42 } },
      { $project: { _id: 0, cliente: 1, importe: { $multiply: ["$total", -1] } } } ] } },
  { $group: { _id: "$cliente", neto: { $sum: "$importe" }, filas: { $sum: 1 } } }
])   // { _id: 42, neto: 5495.5, filas: 11 }

Escribir el resultado

$merge funde en la colección destino y $out la reemplaza entera.

db.pedidos.aggregate([
  { $match: { estado: "pagado" } },
  { $group: { _id: "$cliente", gastado: { $sum: "$total" } } },
  { $merge: { into: "resumen", whenMatched: "merge", whenNotMatched: "insert" } }
])
db.pedidos.aggregate([
  { $match: { estado: "enviado" } },
  { $group: { _id: "$cliente", enviado: { $sum: "$total" } } },
  { $merge: { into: "resumen", whenMatched: "merge", whenNotMatched: "insert" } }
])   // resumen: { _id: 42, gastado: 5505, enviado: 515 }

db.pedidos.aggregate([
  { $match: { estado: "nuevo" } },
  { $group: { _id: "$cliente", nuevo: { $sum: "$total" } } },
  { $out: "resumen" }
])   // resumen: { _id: 42, nuevo: 525 } - lo anterior ya no esta

Los techos

Cada etapa que bloquea —$group, $sort, $facet, el intermedio de $lookup— tiene 104 857 600 bytes, y desde la 6.0 lo que desborda va a disco solo. Pero el array de un acumulador no se derrama: un $push sobre documentos grandes falla con 146 ExceededMemoryLimit, y allowDiskUse: true no lo salva.

Palabras clave: agregación, pipeline, etapa, match, group, project, lookup, join, graphLookup, setWindowFields, ventana, facet, unionWith, merge, out, unwind, explain, allowDiskUse

Un documento se escribe entero sin transacción; para varios hace falta una, y un replica set. La sesión, readConcern y writeConcern, el WriteConflict 112 y los tres topes medidos.

Aplica a: MongoDB 7.0+

En MongoDB un documento se escribe entero o no se escribe, y eso vale aunque el updateOne toque diez campos y un array anidado. Para cambiar más de un documento a la vez hace falta una transacción, y una transacción exige un replica set: en un nodo suelto se rechaza, y el mensaje ni siquiera habla de transacciones —dice que el despliegue no admite escrituras reintentables—.

db.cuentas.insertMany([{ _id: "A", saldo: 100 }, { _id: "B", saldo: 100 }])
db.cuentas.updateOne({ _id: "A" },
  { $inc: { saldo: -30 }, $set: { ultimo: new Date("2026-09-12") } })
db.cuentas.findOne({ _id: "A" })
// { _id: "A", saldo: 70, ultimo: 2026-09-12 } - los dos campos, o ninguno

La transacción va sobre una sesión

Todo lo de dentro pasa por el objeto de la sesión: el db.cuentas de fuera no está en la transacción por mucho que se llame igual. Hasta el commitTransaction(), lo escrito sólo lo ve quien está dentro.

const s = db.getMongo().startSession()
const c = s.getDatabase(db.getName()).cuentas
s.startTransaction({ readConcern: { level: "snapshot" },
                     writeConcern: { w: "majority" } })
c.updateOne({ _id: "A" }, { $inc: { saldo: -10 } })
c.updateOne({ _id: "B" }, { $inc: { saldo: 10 } })

c.find().toArray()           // dentro:  A 60, B 110
db.cuentas.find().toArray()  // fuera:   A 70, B 100

s.commitTransaction()
db.cuentas.find().toArray()  // fuera:   A 60, B 110
s.endSession()

abortTransaction() lo deshace todo, y no hace falta pedirlo: si la sesión se va o el servidor reinicia, la transacción muere abortada.

readConcern y writeConcern son dos preguntas distintas

readConcern dice qué se lee: local es lo que hay aquí, majority lo que ya no se puede perder, snapshot una foto coherente de un instante. writeConcern dice cuándo se da por escrita: w: 1 es el primario, w: "majority" la mayoría del conjunto, y j: true añade el diario. Los de fábrica salen de getDefaultRWConcern: lectura local, escritura majority.

El conflicto de escritura

Dos transacciones sobre el mismo documento no esperan: la segunda falla en el acto con 112 WriteConflict, y el mensaje lo dice sin rodeos. Reintentar es parte del trato, y por eso los drivers traen withTransaction, que reintenta solo.

const s1 = db.getMongo().startSession()
const s2 = db.getMongo().startSession()
s1.startTransaction(); s2.startTransaction()
s1.getDatabase(db.getName()).cuentas.updateOne({ _id: "A" }, { $inc: { saldo: 1 } })
s2.getDatabase(db.getName()).cuentas.updateOne({ _id: "A" }, { $inc: { saldo: 1 } })
// 112 WriteConflict - Write conflict during plan execution ... Please retry

s1.commitTransaction()   // la primera sí pasa
s2.abortTransaction()
s1.endSession(); s2.endSession()

Una escritura de fuera de la transacción no recibe el 112: espera. En la medición esperó 76 s, hasta que el límite de vida abortó a la transacción que tenía el documento cogido.

Los techos

Una transacción dura 60 s, y pasada esa raya el commit devuelve 251 NoSuchTransaction con «has been aborted». Dentro, cada petición de bloqueo espera sólo 5 ms: una transacción no se queda colgada de un candado, prefiere fallar. Y un writeConcern que el conjunto no puede cumplir falla antes de intentarlo.

db.adminCommand({ getParameter: 1, transactionLifetimeLimitSeconds: 1 })
// 60
db.adminCommand({ getParameter: 1, maxTransactionLockRequestTimeoutMillis: 1 })
// 5
db.adminCommand({ getDefaultRWConcern: 1 })
// defaultReadConcern local - defaultWriteConcern { w: "majority", wtimeout: 0 }

db.cuentas.updateOne({ _id: "A" }, { $inc: { saldo: 0 } },
  { writeConcern: { w: 2, wtimeout: 1000 } })
// 100 UnsatisfiableWriteConcern - Not enough data-bearing nodes

La regla práctica: si dos documentos tienen que cambiar a la vez muchas veces al día, casi siempre lo que está mal es el modelo y lo que había que hacer era embeberlos. La transacción es la salida para lo que de verdad no cabe en un documento.

Palabras clave: transacción, transacciones, sesión, startTransaction, commit, abort, WriteConflict, 112, 251, readConcern, writeConcern, majority, snapshot, replica set, atomicidad, bloqueo

Las tres verbosidades de explain y qué añade cada una, la caché de planes y cuándo se desactiva un plan, el conjunto de trabajo dentro de la caché de WiredTiger, y los tres niveles del perfilador.

Aplica a: MongoDB 7.0+

Antes de tocar nada se mide, y la herramienta es explain. Tiene tres verbosidades, y cada una cuesta más que la anterior: queryPlanner sólo planifica —no llega a ejecutar— y enseña el plan ganador y los rechazados; executionStats ejecuta el ganador y añade lo que costó; allPlansExecution añade además lo que costó cada candidato durante el periodo de prueba, que es donde se ve por qué ganó el que ganó.

const est = ["nuevo", "pagado", "enviado", "cerrado"]
const docs = []
for (let i = 0; i < 20000; i++)
  docs.push({ cliente: i % 499, estado: est[i % 4], total: (i % 997) + 0.5 })
db.pedidos.insertMany(docs)
db.pedidos.createIndex({ cliente: 1 })
db.pedidos.createIndex({ cliente: 1, total: -1 })
const q = { cliente: 42, total: { $gt: 100 } }

db.pedidos.find(q).explain()
// queryPlanner: winningPlan FETCH y rejectedPlans con 1 candidato

db.pedidos.find(q).explain("executionStats")
// + executionStats: nReturned 20, docs 20, claves 20, 0 ms

db.pedidos.find(q).explain("allPlansExecution")
// + allPlansExecution: los 2 planes probados, FETCH 20 y FETCH 10

Los tres números que importan son nReturned, totalDocsExamined y totalKeysExamined. Si el segundo es mucho mayor que el primero, el servidor está leyendo documentos para tirarlos.

La caché de planes

El planificador no vuelve a decidir en cada consulta. La primera vez prueba los candidatos, guarda el ganador bajo un planCacheKey y a partir de ahí lo reutiliza; en el explain se ve como isCached: true. La entrada guarda works, el trabajo que le costó, y si una ejecución posterior gasta diez veces más, el plan se desactiva y se vuelve a competir. Crear o borrar un índice también vacía la caché.

db.pedidos.getPlanCache().clear()
db.pedidos.getPlanCache().list()     // []

db.pedidos.find(q).toArray()
db.pedidos.find(q).toArray()
db.pedidos.getPlanCache().list()     // 1 entrada: works 21, isActive true

db.pedidos.find(q).explain().queryPlanner.winningPlan.isCached   // true

El conjunto de trabajo y la caché de WiredTiger

MongoDB no guarda resultados: lo que guarda son páginas, en la caché de WiredTiger, y el rendimiento depende de que quepa ahí el conjunto de trabajo —los datos y los índices que de verdad se tocan—. Por omisión esa caché ocupa la mitad de la RAM menos 1 GB. En la medición, de 486 864 páginas pedidas sólo 261 hubo que traerlas del disco: una de cada 1 865.

const w = db.serverStatus().wiredTiger.cache
w["maximum bytes configured"]        // 3621781504 - la mitad de la RAM menos 1 GB
w["bytes currently in the cache"]    // 18640438 - de todo el servidor
w["pages requested from the cache"]  // 486864
w["pages read into cache"]           // 261 - una de cada 1865

db.pedidos.stats().size              // 1385000 de datos
db.pedidos.stats().totalIndexSize    // 499712 de indices

El perfilador

Tres niveles: 0 apagado, 1 sólo lo que pasa de slowms, y 2 todo. Dos cosas medidas que sorprenden: setProfilingLevel devuelve el nivel de antes, no el que acaba de poner —el nuevo hay que releerlo—, y system.profile es una colección capada de 1 MiB, así que no crece: se muerde la cola.

db.getProfilingStatus()      // { was: 1, slowms: 100, sampleRate: 1 }
db.setProfilingLevel(2)      // { was: 1, ... }  <- el nivel de ANTES

db.pedidos.find({ total: { $gt: 900 } }).toArray()
db.system.profile.find().sort({ ts: -1 }).limit(1)
// query - COLLSCAN - docsExamined 1901 - nreturned 101 - 0 ms

db.system.profile.stats().capped    // true, maxSize 1048576
db.setProfilingLevel(1, { slowms: 100 })

Cada entrada trae planSummary, docsExamined, nreturned y millis, que es justo lo que hace falta para decidir si conviene un índice. Calíope lee esa colección en su herramienta de perfilado. El nivel 2 en producción cuesta caro: se enciende para un rato y se vuelve a bajar, no se deja puesto.

Palabras clave: rendimiento, explain, queryPlanner, executionStats, allPlansExecution, caché de planes, planCacheKey, isCached, WiredTiger, conjunto de trabajo, perfilador, system.profile, slowms

Qué está corriendo de verdad según getCmdLineOpts, las cinco secciones de mongod.conf que importan, y las tres clases de parámetro: los que cambian en caliente, los de arranque y los que no son parámetro.

Aplica a: MongoDB 7.0+

La primera pregunta de un servidor que no conoces no es qué dice su archivo de configuración, sino con qué está corriendo. getCmdLineOpts contesta las dos a la vez: argv es lo que se le pasó por la línea de órdenes, y parsed es eso mismo ya traducido al vocabulario del archivo.

db.adminCommand({ getCmdLineOpts: 1 })
// argv:   ["mongod", "--profile", "1", "--slowms", "100",
//          "--auth", "--bind_ip_all"]
// parsed: { net: { bindIp: "*" },
//           operationProfiling: { mode: "slowOp", slowOpThresholdMs: 100 },
//           security: { authorization: "enabled" } }

Las cinco secciones que importan

storage dice dónde están los datos y cuánta memoria se lleva la caché; net, por qué direcciones escucha; security, si hace falta autenticarse; operationProfiling, qué se apunta del tráfico lento; y replication, a qué conjunto pertenece. El archivo es YAML, así que la sangría es sintaxis.

# mongod.conf - lo mismo de arriba, escrito donde se queda
storage:
  dbPath: /data/db
  wiredTiger:
    engineConfig:
      cacheSizeGB: 3.37
net:
  bindIp: 127.0.0.1,10.0.0.5
  port: 27017
security:
  authorization: enabled
operationProfiling:
  mode: slowOp
  slowOpThresholdMs: 100
replication:
  replSetName: rs0
setParameter:
  cursorTimeoutMillis: 300000

Los parámetros son de tres clases

Los que se cambian en caliente con setParameter, los que sólo se leen al arrancar, y los que no son parámetros aunque lo parezcan. Los tres se distinguen por lo que contesta el servidor: el primero devuelve was con el valor anterior —no el nuevo, así que para saber cómo quedó hay que releerlo—; el segundo da 20 IllegalOperation; y port, que es opción de arranque y no parámetro, da 72 InvalidOptions con «unrecognized parameter».

db.adminCommand({ setParameter: 1, cursorTimeoutMillis: 300000 })
// { was: 600000, ok: 1 }   <- devuelve el valor de ANTES, no el nuevo

db.adminCommand({ setParameter: 1, wiredTigerEngineRuntimeConfig: "cache_size=512M" })
db.serverStatus().wiredTiger.cache["maximum bytes configured"]   // 536870912

db.adminCommand({ setParameter: 1, authenticationMechanisms: ["SCRAM-SHA-256"] })
// 20 IllegalOperation - not allowed to change [...] at runtime

db.adminCommand({ setParameter: 1, port: 27020 })
// 72 InvalidOptions - attempted to set unrecognized parameter [port]

setParameter no se queda

Un setParameter vive hasta el siguiente reinicio y ni un segundo más: con cursorTimeoutMillis puesto a 300 000, el servidor volvió a arrancar con 600 000. Para que se quede hay que escribirlo en la sección setParameter: del archivo, que es la de abajo del ejemplo.

La caché y la versión de compatibilidad

La caché de WiredTiger es lo primero que se toca y casi siempre no hay que tocarla: por omisión se lleva la mitad de lo que queda de RAM tras apartar 1 GB. En el nodo medido, 7 933 MB de RAM dieron 3 621 781 504 bytes de caché. Y hay una sexta cosa que no está en el archivo: la featureCompatibilityVersion, que decide qué funciones del binario están encendidas —se sube a mano después de actualizar, y se baja antes de volver atrás—.

db.hostInfo().system.memSizeMB   // 7933
db.serverStatus().wiredTiger.cache["maximum bytes configured"]
// 3621781504 = la mitad de (7933 MB - 1 GB)

Object.keys(db.adminCommand({ getParameter: "*" })).length   // 780
db.adminCommand({ getParameter: 1, featureCompatibilityVersion: 1 })
// { version: "8.2" }

Palabras clave: configuración, mongod.conf, getCmdLineOpts, setParameter, cacheSizeGB, WiredTiger, bindIp, authorization, operationProfiling, replSetName, featureCompatibilityVersion, FCV

Un usuario vive en una base y ése es su apellido, los roles integrados no son los mismos en admin que en el resto, el 13 que da una escritura sin permiso, y la única puerta que deja abierta un servidor recién autenticado.

Aplica a: MongoDB 7.0+

Sin security.authorization: enabled no hay nada de esto: el servidor acepta a cualquiera que llegue, y con bindIp: "*" llega cualquiera. Encendido, lo primero que se pregunta es quién soy yo.

db.adminCommand({ connectionStatus: 1 }).authInfo
// { authenticatedUsers:     [{ user: "caliope", db: "admin" }],
//   authenticatedUserRoles: [{ role: "root",    db: "admin" }] }

db.adminCommand({ getParameter: 1, authenticationMechanisms: 1 })
// ["MONGODB-X509", "SCRAM-SHA-1", "SCRAM-SHA-256"]

El mecanismo por omisión es SCRAM-SHA-256, y el servidor guarda las dos versiones: la contraseña del usuario medido lleva 15 000 iteraciones en SHA-256 y 10 000 en SHA-1, con un salt de 40 caracteres. La contraseña no viaja, ni siquiera cifrada: SCRAM demuestra que se sabe sin decirla. El tercer mecanismo, MONGODB-X509, cambia la contraseña por un certificado de cliente, y entonces el nombre del usuario es el sujeto del certificado.

Un usuario vive en una base, y ésa es su otra mitad

lector no es un usuario: lector de ventas lo es. La base donde se creó es su base de autenticación, y hay que nombrarla al conectarse (--authenticationDatabase). Con la equivocada, el servidor no dice que el usuario exista en otro sitio: dice «Authentication failed» y punto.

Los roles no son los mismos en todas las bases

En una base normal hay seis roles integrados. En admin hay veintiuno, porque ahí viven los que alcanzan a todo el servidor —los …AnyDatabase, root, backup, restore, los de clúster—. Un rol es una lista de acciones: read son once, y find es sólo una de ellas.

db.getSiblingDB("admin").runCommand({ rolesInfo: 1, showBuiltinRoles: true })
// 21 roles: backup, clusterAdmin, clusterManager, clusterMonitor, dbAdmin,
// dbAdminAnyDatabase, dbOwner, read, readAnyDatabase, readWrite,
// readWriteAnyDatabase, restore, root, userAdmin, userAdminAnyDatabase ...

db.getSiblingDB("ventas").runCommand({ rolesInfo: 1, showBuiltinRoles: true })
// 6: dbAdmin, dbOwner, enableSharding, read, readWrite, userAdmin

Lo que pasa cuando falta un permiso

No hay respuesta vacía ni fila que falte: hay un 13 Unauthorized, y el mensaje nombra la base, la orden y hasta la colección. Es un error del que se puede sacar la regla que falta.

db.getSiblingDB("ventas").createUser({
  user: "lector", pwd: "lectorpass",
  roles: [{ role: "read", db: "ventas" }]
})
// mechanisms: ["SCRAM-SHA-1", "SCRAM-SHA-256"]

// ya conectado como lector, con --authenticationDatabase ventas:
db.datos.findOne()              // { _id: 1, v: 1 }
db.datos.insertOne({ _id: 2 })
// 13 Unauthorized - not authorized on ventas to execute command { insert: ... }

La excepción de localhost

Un servidor con --auth y sin un solo usuario deja hacer exactamente una cosa a quien llegue desde la propia máquina: crear el primero. Leer ya no; crear el segundo, tampoco. Es la rampa para arrancar, y se cierra sola en cuanto existe un usuario.

// un nodo con --auth y sin un solo usuario, desde el propio nodo:
db.getSiblingDB("prueba").c.findOne()
// 13 Unauthorized

db.getSiblingDB("admin").createUser({ user: "primero", pwd: "x", roles: ["root"] })
// OK - este es el unico que deja

db.getSiblingDB("admin").createUser({ user: "segundo", pwd: "x", roles: ["root"] })
// 13 Unauthorized - Command createUser requires authentication

Palabras clave: seguridad, autenticación, SCRAM, SCRAM-SHA-256, x.509, TLS, usuario, rol, roles integrados, read, readWrite, dbAdmin, userAdmin, root, 13, Unauthorized, localhost, authSource

Qué hay dentro de un mongodump y qué no, cuánto tarda restaurarlo y por qué, para qué sirve --oplog, cuánto dura de verdad la ventana del oplog, y las dos cosas que un dump no garantiza.

Aplica a: MongoDB 7.0+

mongodump es un respaldo lógico: se conecta como un cliente cualquiera, lee los documentos y los escribe en BSON. Eso tiene dos consecuencias que se ven en los números. La primera es que el archivo mide lo que miden los documentos, no lo que ocupan en el disco: 2 420 000 bytes de .bson para una colección que en disco está comprimida. La segunda es que compite por la caché con el trabajo normal del servidor, así que un dump de una base grande se nota.

mongodump -u caliope -p ... --authenticationDatabase admin \
  --db ventas --out /vol
// writing `ventas.pedidos` to `/vol/ventas/pedidos.bson`
// done dumping `ventas.pedidos` (20000 documents)      26 ms

ls -l /vol/ventas
// pedidos.bson           2420000   <- el tamano LOGICO de los documentos
// pedidos.metadata.json      255   <- los indices, sin sus datos

De los índices sólo viaja la definición, en el .metadata.json. Por eso restaurar cuesta mucho más que volcar —91 ms contra 26 en esta medición—: el tiempo se va en reconstruirlos, y con una colección de verdad ésa es casi toda la espera.

mongorestore -u caliope -p ... --authenticationDatabase admin \
  --nsFrom "ventas.*" --nsTo "copia.*" /vol
// restoring `copia.pedidos` from `/vol/ventas/pedidos.bson`
// finished restoring `copia.pedidos` (20000 documents, 0 failures)
// restoring indexes for collection `copia.pedidos` from metadata
// 20000 document(s) restored successfully.             91 ms

db.getSiblingDB("copia").pedidos.getIndexes()   // _id_ y cliente_1

--archive deja un solo archivo en vez de un árbol de carpetas, y con --gzip encogió de 2 420 000 a 133 572 bytes. Los dos se pueden mandar por una tubería, que es como se copia una base de una máquina a otra sin escribirla en el disco de en medio.

mongodump ... --db ventas --archive=/vol/ventas.gz --gzip
// 133572 bytes, frente a 2420000 del BSON suelto

El oplog es lo que convierte un respaldo en un punto en el tiempo

Un dump tarda, y mientras tanto la base sigue cambiando: lo que se escribió en la colección A antes de volcarla y en la B después no cuadra. --oplog guarda además las operaciones ocurridas durante el volcado, y mongorestore --oplogReplay las aplica al final, de modo que lo restaurado es el estado de un instante, el del final del dump. Sólo funciona contra un conjunto de réplicas, porque el oplog es suyo.

// esto sólo existe en un conjunto de réplicas:
db.getSiblingDB("local").oplog.rs.stats()
// capped true - maxSize 45822903296 - size 3708051130 - count 318107

rs.printReplicationInfo()
// oplog first event time   Sat Aug 08 2026 17:56:09
// oplog last event time    Sat Sep 12 2026 07:41:07

mongodump --port 27018 --oplog --out /vol
// writing captured oplog to `` - dumped 1 oplog entry

El oplog es una colección capada, así que su ventana no se mide en bytes sino en tiempo, y ese tiempo depende de cuánto se escriba. En el nodo medido, 42 GiB de tope daban una ventana del 8 de agosto al 12 de septiembre; con diez veces más carga serían tres días. Es el número que hay que mirar antes de irse de fin de semana.

Lo que un dump no cubre

Dos cosas. Una base de cientos de gigabytes no se respalda leyéndola documento a documento: ahí se va a instantáneas del sistema de archivos, que hay que tomar con el diario incluido o con la base bloqueada por fsyncLock. Y en un clúster fragmentado, un mongodump contra el enrutador no da un punto en el tiempo común a los fragmentos: hay que parar el balanceador y tomar una instantánea por fragmento y otra de los servidores de configuración.

Palabras clave: respaldo, copia de seguridad, mongodump, mongorestore, oplog, oplogReplay, archive, gzip, instantánea, snapshot, fsyncLock, recuperación, ventana de recuperación, BSON

El esquema es lo que traen los documentos, así que cambiarlo es escribirlos. El validador con $jsonSchema, el 121 y su errInfo, las cuatro combinaciones de validationLevel y validationAction, y la migración por versión de documento.

Aplica a: MongoDB 7.0+

No hay ALTER TABLE porque no hay tabla: el esquema de una colección es, literalmente, lo que traen sus documentos. Añadir un campo a los nuevos no cuesta nada y no cambia a los viejos, y ahí está la trampa: quien lee tiene que apañárselas con las dos formas hasta que alguien iguale el pasado.

El validador es una puerta, no un esquema

Lo que sí existe es un validator con $jsonSchema: una condición que se comprueba al escribir, nunca al leer y nunca hacia atrás. Rechaza con 121 DocumentValidationFailure, y lo bueno está en errInfo.details, que nombra la regla incumplida en vez de decir «no válido».

db.createCollection("socios", {
  validator: { $jsonSchema: {
    bsonType: "object",
    required: ["nombre", "correo"],
    properties: {
      nombre: { bsonType: "string", minLength: 2 },
      correo: { bsonType: "string", pattern: "^.+@.+$" },
      edad:   { bsonType: "int",    minimum: 18 }
    } } },
  validationLevel: "strict", validationAction: "error"
})

db.socios.insertOne({ nombre: "Ana", correo: "ana@ej.com", edad: 30 })   // entra
db.socios.insertOne({ nombre: "Caro" })
// 121 DocumentValidationFailure
// errInfo.details: required -> missingProperties: ["correo"]

Cuidado con los tipos: mongosh guarda como int un número entero, así que edad: 30 pasa un bsonType: "int" y edad: 30.5 lo incumple, porque eso sí es un double.

validationLevel y validationAction son dos perillas distintas

El nivel dice a qué documentos alcanza: strict a todos, moderate sólo a los que ya eran válidos —así se puede poner un validador en una colección con basura vieja sin que se atasquen sus actualizaciones—. La acción dice qué pasa cuando falla: error rechaza, warn deja escribir y lo apunta en el log. Y poner el validador con collMod no toca nada de lo que ya estaba.

db.viejos.insertMany([{ _id: 1, nombre: "Uno" }, { _id: 2, nombre: "Dos" }])
db.runCommand({ collMod: "viejos",
  validator: { $jsonSchema: { bsonType: "object", required: ["nombre", "correo"] } },
  validationLevel: "moderate", validationAction: "error" })
db.viejos.countDocuments()                                  // 2 - no borra nada

db.viejos.updateOne({ _id: 1 }, { $set: { nombre: "Uno bis" } })   // moderate: pasa
db.viejos.insertOne({ _id: 3, nombre: "Tres" })                    // 121 igual

db.runCommand({ collMod: "viejos", validationLevel: "strict" })
db.viejos.updateOne({ _id: 2 }, { $set: { nombre: "Dos bis" } })   // 121

db.runCommand({ collMod: "viejos", validationAction: "warn" })
db.viejos.insertOne({ _id: 4, nombre: "Cuatro" })      // entra, y sólo avisa

Migrar es escribir

Sin ALTER, el equivalente de una columna nueva es un updateMany con $set, y el de quitarla, uno con $unset. Son baratos —20 000 documentos en 61 y 51 ms— pero no son atómicos: se hacen documento a documento, así que durante la migración conviven las dos formas.

db.pedidos.updateMany({ _v: 1 }, { $set: { moneda: "MXN", _v: 2 } })
// 20000 modificados en 61 ms

db.pedidos.updateMany({}, { $unset: { moneda: "" } })
// 20000 modificados en 51 ms

Por eso el patrón que aguanta es guardar la versión dentro de cada documento (_v): la aplicación sabe leer las dos, la migración avanza por lotes o al tocar cada documento, y el día que {_v: 1} no devuelva nada, se retira el código viejo. Es lo mismo que hace una migración en SQL, sólo que aquí el estado intermedio es visible y hay que escribirlo.

Palabras clave: esquema, validación, validador, jsonSchema, 121, DocumentValidationFailure, validationLevel, validationAction, strict, moderate, warn, collMod, migración, versión de documento

Las tres piezas de un clúster fragmentado, por qué una clave hasheada reparte y una monótona amontona, la diferencia medida entre una consulta dirigida y una difundida, las zonas, y lo que cuesta cambiar la clave.

Aplica a: MongoDB 7.0+

Fragmentar es repartir una colección entre varias máquinas, y son tres piezas: los fragmentos, que guardan los datos y son conjuntos de réplicas; los servidores de configuración, que guardan el mapa de qué trozo está dónde; y mongos, el enrutador, que no guarda nada y es a quien se conecta la aplicación.

# tres piezas distintas, y el enrutador no guarda datos
mongod --configsvr --replSet cfg --port 27019
mongod --shardsvr  --replSet sh1 --port 27018
mongod --shardsvr  --replSet sh2 --port 27018
mongos --configdb cfg/qa-cfg:27019

# ya conectado al mongos:
sh.addShard("sh1/qa-sh1:27018")
sh.addShard("sh2/qa-sh2:27018")   // config.shards: ["sh1", "sh2"]

La clave de fragmentación es la única decisión que importa

De ella salen tres cosas a la vez: cómo se reparten los datos, qué consultas se pueden dirigir a un solo fragmento, y si hay un punto caliente. Se pide cardinalidad —muchos valores distintos—, frecuencia pareja —que ninguno se lleve la mitad— y que no sea monótona, porque una clave que siempre crece manda todas las escrituras nuevas al mismo sitio.

Eso último no es teoría. Con la misma colección de 60 000 documentos: una clave hashed sobre el cliente dejó 52,11 % en un fragmento y 47,88 % en el otro; el _id de ObjectId, que siempre crece, dejó el 100 % en uno solo, en un único trozo.

sh.enableSharding("ventas")

sh.shardCollection("ventas.pedidos", { cliente: "hashed" })
db.pedidos.getShardDistribution()
// 60000 documentos - sh1 52.11 %, sh2 47.88 % - 2 trozos

sh.shardCollection("ventas.eventos", { _id: 1 })
db.eventos.getShardDistribution()
// 60000 documentos - sh2 100 % - 1 trozo

Dirigida o difundida

Una consulta que trae la clave va a un fragmento y punto. Una que no la trae se pregunta a todos y se funden las respuestas: SINGLE_SHARD frente a SHARD_MERGE. La diferencia medida es de 121 documentos examinados frente a 60 000.

db.pedidos.find({ cliente: 42 }).explain()
// SINGLE_SHARD - shards: ["sh2"]
// examinados 121, devueltos 121

db.pedidos.find({ estado: "pagado" }).explain()
// SHARD_MERGE  - shards: ["sh2", "sh1"]
// examinados 60000, devueltos 15000

Zonas, y cambiar de clave

Una zona ata un rango de la clave a un fragmento, y sirve para dos cosas de verdad: dejar los datos de un país en máquinas de ese país, y separar lo caliente de lo frío. Y desde la 5.0 la clave se puede cambiar con reshardCollection, pero copia la colección entera y se nota: sobre 60 000 documentos seguía trabajando pasados dos minutos, con su colección temporal a la vista.

sh.addShardToZone("sh1", "MX")
sh.addShardToZone("sh2", "EU")
// config.shards: sh1 tags ["MX"], sh2 tags ["EU"]

db.adminCommand({ reshardCollection: "ventas.eventos", key: { _id: "hashed" } })
// copia la coleccion entera: seguia en marcha a los dos minutos,
// con su system.resharding.<uuid> visible en config.collections

Tres cosas que ya no son verdad

Se sigue contando que un updateOne sin la clave falla, que el valor de la clave no se puede cambiar y que hay que crear el índice antes de fragmentar. En la 8.2 las tres pasaron sin protestar.

Palabras clave: fragmentación, sharding, clave de fragmentación, shard key, hashed, trozo, chunk, mongos, servidores de configuración, zona, balanceador, reshardCollection, SINGLE_SHARD, SHARD_MERGE

Los ocho códigos que salen a diario, provocados uno a uno con el texto que devuelve el servidor, y los dos que traen dentro más información que el número: el 11000 con su clave y el 121 con su errInfo.

Aplica a: MongoDB 7.0+

Un error de MongoDB trae un número, casi siempre un nombre, y a veces algo dentro que vale más que los dos. Éstos son los ocho que salen a diario, provocados uno a uno contra el servidor y copiados tal cual.

Los dos que no traen nombre traen algo mejor

El 11000 y el 121 llegan sin codeName, y da igual: los dos vienen con el dato que hace falta para arreglarlos. El 11000 nombra la colección, el índice y el valor que chocó, así que no hay que adivinar cuál de los tres únicos saltó.

db.correos.createIndex({ correo: 1 }, { unique: true })
db.correos.insertOne({ correo: "a@b.c" })
db.correos.insertOne({ correo: "a@b.c" })
// 11000 :: E11000 duplicate key error collection: ventas.correos
//          index: correo_1 dup key: { correo: "a@b.c" }

El 121 dice «Document failed validation» y nada más en el mensaje, pero e.errInfo.details lleva la regla incumplida con su nombre y su valor esperado. Es la diferencia entre «no válido» y «le falta correo».

db.createCollection("socios", {
  validator: { $jsonSchema: { bsonType: "object", required: ["correo"] } } })
db.socios.insertOne({ nombre: "sin correo" })
// 121 :: Document failed validation
// e.errInfo.details:
// { operatorName: "$jsonSchema", schemaRulesNotSatisfied: [
//     { operatorName: "required", specifiedAs: { required: ["correo"] },
//       missingProperties: ["correo"] } ] }

Los dos de permiso son distintos

El 18 es «no sé quién eres»: contraseña mala, o —lo más común— la base de autenticación equivocada, porque un usuario de MongoDB es el nombre más la base donde se creó. El 13 es «sé quién eres y no puedes»; su mensaje nombra la base, la orden y hasta la colección, así que de ahí sale el rol que falta.

Y dos avisos de sintaxis

El 50 no es un error del servidor sino el tope que le pusiste tú con maxTimeMS. Y el 26 aparece donde menos se espera: collMod y renameCollection sobre algo que no existe fallan, pero drop() sobre una colección que no existe devuelve false y ya está —no es un error, así que un guion que lo dé por sentado nunca se entera de que se equivocó de nombre—.

db.gordo.find({ s: /x{100}/ }).maxTimeMS(1).toArray()
// 50 MaxTimeMSExpired :: operation exceeded time limit

db.runCommand({ collMod: "no_existe", validationLevel: "strict" })
// 26 NamespaceNotFound :: ns does not exist

db.no_existe.drop()   // false, y ningun error

Palabras clave: errores, códigos, 11000, duplicate key, 13, Unauthorized, 18, AuthenticationFailed, 26, NamespaceNotFound, 50, MaxTimeMSExpired, 112, WriteConflict, 121, 251, NoSuchTransaction, errInfo

Los ocho topes que se tocan de verdad, provocados uno a uno contra el servidor, con el código que devuelve cada uno y los tres que no están donde la fama dice.

Aplica a: MongoDB 7.0+

Todos éstos se provocaron contra el servidor, así que el número de la izquierda es el que rechazó de verdad y no el que dice la leyenda.

El de 16 MB es el único que se toca por accidente

Y casi siempre por lo mismo: un array que crece sin freno dentro de un documento. El mensaje trae los dos números, el del documento y el máximo, así que se ve de un vistazo por cuánto se pasó.

db.c.insertOne({ _id: 1, s: "x".repeat(16 * 1024 * 1024 - 200) })   // entra
db.c.insertOne({ _id: 2, s: "x".repeat(16 * 1024 * 1024 + 100) })
// 10334 :: object to insert too large.
//          size in bytes: 16777338, max size: 16777216

Los dos de índices se cuentan mal

Los 64 índices por colección incluyen el _id_, así que caben 63 propios; y no es un tope que se toque sano: con 64 índices, cada escritura mantiene 64 árboles. Los 32 campos de un compuesto tampoco son un objetivo: pasados seis o siete, casi seguro que lo que hace falta son dos índices, no uno más ancho.

db.tope.insertOne({ a: 1 })
for (let i = 0; i < 70; i++) db.tope.createIndex({ ["c" + i]: 1 })
// crea 63, y el 64 falla:  el _id_ tambien cuenta
// 67 CannotCreateIndex :: add index fails, too many indexes

const k = {}
for (let i = 0; i < 33; i++) k["g" + i] = 1
db.comp.createIndex(k)          // con 32 pasa
// 13103 :: too many compound keys

Tres que no están donde su fama dice

El anidamiento se documenta en 100 niveles y lo que rechazó el servidor fue el 180, porque el techo es del BSON del comando entero y la envoltura del insert se lleva una parte. El nombre largo no falla por la colección sino por la suma de base y colección, que es el espacio de nombres. Y el tope de tamaño de una clave de índice, que en las versiones antiguas eran 1 024 bytes, ya no existe: entró un valor indexado de 50 000.

db.getSiblingDB("d".repeat(65)).c.insertOne({ a: 1 })
// 73 InvalidNamespace :: db name must be at most 63 characters, found: 65

db.createCollection("z".repeat(260))
// 73 InvalidNamespace :: Fully qualified namespace is too long

db.k.createIndex({ w: 1 })
db.k.insertOne({ w: "y".repeat(50000) })   // entra: no hay tope de clave

Y lo que no tiene tope

Ni el número de colecciones, ni el de bases, ni el de documentos de una colección. El que se acaba antes no está en esta tabla: es el disco. Y para lo que de verdad no cabe en 16 MB —un archivo— está GridFS, que lo parte en trozos de 255 KB y guarda cada trozo como un documento normal.

Palabras clave: límites, topes, 16 MB, tamaño de documento, 10334, anidamiento, Overflow, índices por colección, 67, CannotCreateIndex, 13103, espacio de nombres, 73, InvalidNamespace, clave de índice

El resumen del manual de MongoDB: siete costumbres que valen la pena, cada una con la medición que la sostiene, y las cinco líneas con las que se le toma el pulso a un servidor que acabas de heredar.

Aplica a: MongoDB 7.0+

Éste es el final del manual, y no trae nada nuevo: recoge lo que cada tema dejó demostrado, en la forma en que sirve para el día a día. Cada costumbre va con el número que la sostiene, y todos esos números están medidos contra un servidor de verdad, no copiados.

1. Modela por cómo vas a leer, no por cómo se parece a una tabla. Lo que se lee junto se guarda junto. El límite de esa regla es duro y está medido: un documento no pasa de 16 777 216 bytes, así que un array que crece sin freno acaba en un 10334 un martes cualquiera.

2. Un índice por consulta frecuente, y ni uno más. Cada índice es un árbol que hay que mantener en cada escritura: las mismas 20 000 inserciones tardaron 60 ms sin índices, 101 con cinco y 160 con diez. Y ocupan: en la base de ejemplo, una colección de 1 148 000 bytes de datos llevaba 622 592 de índices.

3. Juzga una consulta por totalDocsExamined frente a nReturned, no por el reloj. El reloj dice lo que tardó hoy, con la caché caliente y la máquina tranquila; la proporción entre esos dos números dice lo que va a pasar cuando la colección sea diez veces más grande.

4. writeConcern: majority para lo que no se puede perder. En un conjunto de réplicas ya es el valor de fábrica, así que la costumbre no es ponerlo: es no quitarlo para ir más rápido.

5. Nunca sin autenticación. Es una línea en el archivo, y la única puerta que deja abierta un servidor recién encendido —crear el primer usuario desde la propia máquina— se cierra sola en cuanto ese usuario existe.

6. Respalda con el oplog, y mide su ventana en tiempo. mongodump --oplog es lo que convierte un volcado en un instante. Y el tamaño del oplog no se lee en bytes sino en días: el del nodo medido daba 34, pero eso depende de cuánto se escriba, así que es un número que se vuelve a mirar cuando cambia la carga.

7. El conjunto de trabajo tiene que caber en la caché. Es la única regla de rendimiento que no admite trucos. En la base de ejemplo, 1 150 242 bytes de datos contra 3 621 781 504 de caché: sobra sitio tres mil veces. El día que no sobre, se nota en todo a la vez.

El pulso de un servidor que acabas de heredar

Cinco líneas contestan lo que hay que saber antes de tocar nada: si pide contraseña, con qué se da por escrita una escritura, si alguien está mirando las consultas lentas, cuánta memoria tiene para trabajar y cuántos datos tiene que mover.

db.adminCommand({ getCmdLineOpts: 1 }).parsed.security
// { authorization: "enabled" }, o undefined - que es la respuesta mala

db.adminCommand({ getDefaultRWConcern: 1 }).defaultWriteConcern
// { w: "majority", wtimeout: 0 }
// en un nodo suelto ni existe: "not supported on standalone nodes"

db.getProfilingStatus()      // { was: 1, slowms: 100, sampleRate: 1 }

db.serverStatus().wiredTiger.cache["maximum bytes configured"]   // 3621781504
db.stats().dataSize                                              // 1150242

Y una más, que es la que enseña dónde se está yendo el disco y de paso qué colección lleva más índices que datos.

db.getCollectionInfos({ type: "collection" }).map(i => {
  const s = db.getCollection(i.name).stats()
  return { c: i.name, indices: s.nindexes, datos: s.size, indice: s.totalIndexSize }
})
// { c: "eventos", indices: 3, datos: 1148000, indice: 622592 }
// el filtro por type hace falta: stats() sobre una vista falla

Palabras clave: buenas prácticas, resumen, patrón de acceso, índices, writeConcern, majority, autenticación, oplog, conjunto de trabajo, caché, explain, salud del servidor

El tipo vive en el valor y no en la columna: las cinco afinidades y qué convierte cada una, el orden entre clases de almacenamiento, lo que STRICT sí impide, y por qué NOCASE no sabe de acentos.

Aplica a: SQLite 3.35+

En SQLite el tipo es del valor, no de la columna. Lo que una columna declara es una afinidad: una preferencia que se aplica al guardar, y que convierte si puede y deja pasar si no.

CREATE TABLE t (i INTEGER, r REAL, x TEXT, b BLOB, n NUMERIC);
INSERT INTO t VALUES ('42', '42', 42, 42, '42');
INSERT INTO t VALUES (7.0,  7,   '7', '7', '7.5');

SELECT typeof(i), typeof(r), typeof(x), typeof(b), typeof(n) FROM t;
-- integer | real | text | integer | integer
-- integer | real | text | text    | real

Ahí están las cinco en una línea. INTEGER y REAL convierten el texto que parece número; TEXT convierte el número a texto; NUMERIC mira el valor y decide, así que en la misma columna hay un integer y un real; y BLOB es la que no tiene afinidad: guarda lo que le den, tal como venga.

Las clases de almacenamiento son cinco, y están ordenadas

Una columna sin tipo declarado es legal y acepta las cinco, así que en una misma columna caben un entero, un real, un texto, un blob y un nulo. Y se pueden ordenar, porque entre clases hay un orden fijo: los nulos primero, luego los números, luego el texto y al final los blobs. Eso significa que un ORDER BY sobre una columna sucia no falla: agrupa por tipo sin decírselo a nadie.

CREATE TABLE libre (v);            -- sin tipo declarado: vale
INSERT INTO libre VALUES (1), (1.5), ('hola'), (x'0001'), (NULL);

SELECT typeof(v) FROM libre ORDER BY v;
-- null | integer | real | text | blob   <- y ese es el orden entre clases

STRICT impide menos de lo que parece

Desde la 3.37 una tabla puede declararse STRICT, y entonces sólo admite cinco tipos —INT, INTEGER, REAL, TEXT, BLOB y ANY— y rechaza lo que no pueda guardar. Pero sigue convirtiendo: un '42' entra en una columna INTEGER porque no se pierde nada, y un 42 entra en una TEXT y se guarda como '42'. Lo que rechaza es lo que no tiene conversión. Y hay una ganancia que no se espera: un tipo inventado, que en una tabla normal se acepta en silencio, aquí se rechaza al crear la tabla.

CREATE TABLE s (i INTEGER, x TEXT) STRICT;

INSERT INTO s VALUES ('42', 'a');   -- entra: 42, convertible sin perder nada
INSERT INTO s VALUES (1, 42);       -- entra: el 42 se guarda como texto '42'
INSERT INTO s VALUES ('abc', 'a');
-- cannot store TEXT value in INTEGER column s.i

CREATE TABLE s2 (d DATETIME) STRICT;
-- unknown datatype for s2.d: "DATETIME"

No hay fecha, no hay booleano, y el cotejamiento no sabe de acentos

TRUE es un integer con valor 1. Una fecha es lo que tú decidas: date() devuelve text y julianday() devuelve real, y el que elijas es el que tendrás que ordenar y comparar el resto de su vida. Y sólo hay tres cotejamientos —BINARY, NOCASE y RTRIM—: NOCASE iguala mayúsculas y minúsculas del ASCII y nada más, así que café y CAFÉ son valores distintos y upper('café') devuelve CAFé. El texto sí es UTF-8: length cuenta caracteres, y sobre el blob cuenta bytes.

SELECT typeof(TRUE), TRUE;                    -- integer | 1
SELECT typeof(date('2026-09-12'));            -- text
SELECT typeof(julianday('2026-09-12'));       -- real

CREATE TABLE n (v TEXT COLLATE NOCASE);
INSERT INTO n VALUES ('Cafe'), ('CAFE'), ('café');

SELECT v FROM n WHERE v = 'cafe';             -- Cafe, CAFE
SELECT v FROM n WHERE v = 'CAFÉ';             -- nada
SELECT upper('café'), length('café'), length(CAST('café' AS BLOB));
-- CAFé | 4 | 5

Palabras clave: tipos, afinidad, typeof, INTEGER, REAL, TEXT, BLOB, NUMERIC, tipado dinámico, STRICT, booleano, fecha, julianday, COLLATE, NOCASE, UTF-8, clase de almacenamiento

El aislamiento es serializable porque sólo escribe uno: los tres modos de BEGIN, el SQLITE_BUSY 5 y por qué busy_timeout lo resuelve, el 517 que no resuelve, y qué cambia de verdad el modo WAL.

Aplica a: SQLite 3.35+

SQLite no tiene niveles de aislamiento que elegir, y no es una carencia: el aislamiento es serializable porque en toda la base escribe uno solo a la vez. Todo lo demás sale de ahí.

Dos ajustes lo gobiernan, y los dos vienen de fábrica en el peor valor posible.

PRAGMA journal_mode;    -- delete: el de fábrica, no WAL
PRAGMA busy_timeout;    -- 0: no espera nada

PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 3000;

Los tres modos de BEGIN

DEFERRED —el de por omisión— no coge nada hasta que hace falta: la primera lectura toma una foto y la primera escritura pide el candado. IMMEDIATE pide el candado de escritura ya, en la propia línea del BEGIN. EXCLUSIVE pide además que nadie lea, y en modo WAL ya no hace casi nada distinto de IMMEDIATE. La regla práctica: si la transacción va a escribir, BEGIN IMMEDIATE; cuesta una espera al principio y evita el error más molesto de SQLite, que es el de más abajo.

SAVEPOINT es la marca intermedia, y ROLLBACK TO vuelve a ella sin cerrar la transacción.

BEGIN;                          -- DEFERRED, el de por omision
UPDATE t SET v = 10 WHERE i = 1;
SAVEPOINT s1;
UPDATE t SET v = 20 WHERE i = 1;
ROLLBACK TO s1;                 -- deshace hasta aqui, NO cierra la transaccion
SELECT v FROM t WHERE i = 1;    -- 10
RELEASE s1;
COMMIT;

SQLITE_BUSY es el 5, y casi siempre es tuyo

Cuando otro tiene el candado de escritura, la respuesta es SQLITE_BUSY con el código 5 y el texto «database is locked». No es una avería: es la cola de un recurso de un solo carril. Lo que lo convierte en avería es que busy_timeout vale 0 de fábrica, así que sin tocarlo la respuesta es inmediata y seca. Con 800 ms puestos, la misma llamada esperó 895 antes de rendirse.

# dos conexiones a la vez, con timeout=0 para que conteste en el acto
a = sqlite3.connect(db, isolation_level=None, timeout=0)
b = sqlite3.connect(db, isolation_level=None, timeout=0)

b.execute("BEGIN IMMEDIATE"); b.execute("UPDATE t SET v=1 WHERE i=2")

a.execute("SELECT v FROM t WHERE i=2")   # (0,)  <- lee el valor de antes
a.execute("BEGIN IMMEDIATE")             # SQLITE_BUSY (5) database is locked

a.execute("PRAGMA busy_timeout=800")
a.execute("BEGIN IMMEDIATE")             # espera 895 ms y vuelve a dar 5

El que busy_timeout no arregla: el 517

Si una transacción DEFERRED lee y luego quiere escribir, y entre medias alguien confirmó, la foto que tomó al leer ya no vale y SQLite devuelve SQLITE_BUSY_SNAPSHOT, el 517. Esperar no sirve: nada va a devolverle la foto. La única salida es ROLLBACK y empezar otra vez —o, mejor, haber abierto con IMMEDIATE—.

-- A:
BEGIN DEFERRED;
SELECT v FROM t WHERE i = 1;    -- aqui A se queda con una foto de la base

-- B, en otra conexion:
BEGIN IMMEDIATE; UPDATE t SET v = 5 WHERE i = 2; COMMIT;

-- A, que ahora quiere escribir:
UPDATE t SET v = 2 WHERE i = 1;
-- SQLITE_BUSY_SNAPSHOT (517), y busy_timeout no lo arregla
ROLLBACK;                       -- la unica salida: soltar y volver a empezar

Lo que WAL cambia, y lo que no