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+

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
);

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;

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+

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.

Para los ejemplos asumimos:

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;

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;

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 y en SQLite 3.51.

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

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 hablamos de exabytes teóricos — 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+

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

Diseño de esquemas

1. Toda tabla tiene clave primaria. Si no es natural, añade id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY.
2. Tipos explícitos: declara NOT NULL y DEFAULT siempre que la columna lo permita. NULL debe significar "no aplica", no "no rellenado".
3. Foreign keys obligatorias entre tablas relacionadas. Pierdes microsegundos en escritura, ganas integridad referencial irrompible.
4. InnoDB siempre. MyISAM no soporta FKs ni transacciones; queda solo en sistemas legacy.

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;

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

1. Estrategia 3-2-1: 3 copias, 2 medios distintos, 1 fuera del sitio.
2. Tipos:
- mysqldump — lógico, portable, lento en restauración (~5–10 MB/s).
- mariabackup / xtrabackup — físico, mucho más rápido, requiere parar I/O brevemente.
- Snapshot del filesystem (LVM, ZFS) — instantáneo pero atado al filesystem.
3. Probar la restauración — un backup sin restauración probada no es un backup.
4. Retención: diarios 7 días + semanales 4 + mensuales 12 es un punto de partida razonable.

Calíope tiene un módulo de respaldo integrado: Ayuda › Respaldo.

-- 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=...

Replicación

- Replicación asíncrona (default) — el primario no espera al réplica. Riesgo: pérdida de las últimas transacciones si el primario muere.
- Replicación semi-síncrona — el primario espera confirmación de al menos un réplica antes de confirmar al cliente.
- Grupo de replicación (MySQL InnoDB Cluster, MariaDB Galera) — multi-primario con consenso.

Buenas prácticas:

1. GTID activado (gtid_mode = ON) — necesario para failover automático y para herramientas modernas.
2. binlog_format = ROW — más robusto que STATEMENT ante funciones no determinísticas.
3. Replicación dedicada — un usuario replica con solo REPLICATION SLAVE, IP restringida.
4. Lag monitorizado — SHOW REPLICA STATUS (SHOW SLAVE STATUS en versiones antiguas), alerta cuando Seconds_Behind_Source > 30.

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;

Reglas:

1. Un usuario por aplicación / por función. Facilita auditoría.
2. Sin privilegios *.* para usuarios de aplicación. Concede por base o por tabla.
3. Sin acceso de aplicación al usuario root. Es solo para tareas administrativas.
4. Rota contraseñas y usa autenticación robusta (caching_sha2_password en MySQL 8, ed25519 en MariaDB).
5. Restringe el host ('app'@'10.0.%.%'), no uses '%'.

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';

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

1. ✅ Backups automáticos + restauración verificada mensualmente.
2. ✅ Replicación con lag monitorizado.
3. ✅ Usuarios de aplicación sin privilegios excesivos.
4. ✅ TLS obligatorio para conexiones externas.
5. ✅ Slow query log activado (long_query_time = 1).
6. ✅ Monitorización de espacio en disco (alerta al 80 %).
7. ✅ Actualizaciones de seguridad aplicadas trimestralmente.

Palabras clave: buenas prácticas, diseño, convenciones, backup, replicación, GTID, binlog, GRANT, auditoría, seguridad, InnoDB, foreign key, TLS

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+

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;

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 no digamos 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

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

Con el diario de reversión, mientras uno escribe nadie lee. Con journal_mode = WAL, los lectores siguen leyendo la última versión confirmada mientras el escritor trabaja: se midió con dos conexiones y el lector obtuvo el valor anterior sin bloquearse ni un instante. Lo que no cambia es el número de escritores: sigue siendo uno, y el segundo sigue recibiendo un 5.

Palabras clave: transacción, BEGIN, DEFERRED, IMMEDIATE, EXCLUSIVE, SAVEPOINT, ROLLBACK TO, SQLITE_BUSY, 5, 517, BUSY_SNAPSHOT, busy_timeout, WAL, journal_mode, bloqueo, serializable

Un pragma no es una sola cosa: unos se escriben dentro del archivo, otros duran lo que la conexión y otros son órdenes. Cuál es cuál, los valores de fábrica medidos, y el que está apagado y no debería.

Aplica a: SQLite 3.35+

SQLite no tiene archivo de configuración: tiene pragmas. Y la primera confusión que hay que quitarse es que no son todos la misma cosa. Unos se escriben dentro del archivo y valen para quien lo abra después; otros duran lo que dure la conexión y hay que repetirlos cada vez; y otros no son ajustes sino órdenes que hacen algo y se acabó.

Éstos son los valores de fábrica, leídos de una base recién creada.

PRAGMA journal_mode;   -- delete
PRAGMA synchronous;    -- 2 (FULL)
PRAGMA foreign_keys;   -- 0   <- apagadas
PRAGMA cache_size;     -- 2000 paginas
PRAGMA mmap_size;      -- 0
PRAGMA auto_vacuum;    -- 0
PRAGMA page_size;      -- 4096

El que está apagado y casi nadie espera

foreign_keys vale 0. Las claves foráneas se declaran, se guardan en el esquema, salen en el CREATE TABLE… y no se comprueban. Un hijo huérfano entra sin una queja. Encenderlo es una línea, pero es de la conexión: hay que ponerlo en cada una, y encenderlo no mira hacia atrás —para eso está foreign_key_check, que enumera lo que ya se coló—.

CREATE TABLE padre (id INTEGER PRIMARY KEY);
CREATE TABLE hijo  (id INTEGER PRIMARY KEY, p INTEGER REFERENCES padre(id));

INSERT INTO hijo VALUES (1, 999);   -- entra: no hay padre 999 y da igual

PRAGMA foreign_keys = ON;
INSERT INTO hijo VALUES (2, 999);   -- FOREIGN KEY constraint failed

PRAGMA foreign_key_check;           -- hijo | 1 | padre | 0

Cuál se queda y cuál no

journal_mode y user_version se escriben en la cabecera del archivo y sobreviven a cerrar y abrir. page_size y auto_vacuum también, pero sólo si se ponen antes de crear la primera tabla: sobre una base que ya tiene páginas se aceptan sin error y no cambian nada, y eso se comprobó de las dos maneras. foreign_keys, cache_size, busy_timeout y mmap_size son de la conexión y vuelven a su valor de fábrica en cuanto se abre otra.

-- se quedan escritos en el archivo:
PRAGMA journal_mode = WAL;
PRAGMA user_version = 7;
PRAGMA page_size    = 8192;   -- solo en una base todavia VACIA
PRAGMA auto_vacuum  = FULL;   -- idem

-- son de la conexion, y hay que repetirlos en cada una:
PRAGMA foreign_keys = ON;
PRAGMA cache_size   = -8000;  -- en negativo son kibibytes, no paginas
PRAGMA busy_timeout = 3000;

Un detalle que descoloca: tras poner journal_mode = WAL, synchronous pasó a valer 1 (NORMAL) sin que nadie lo tocara. Es deliberado —en WAL basta con NORMAL para no perder nada confirmado— pero enseña que leer un pragma no dice de dónde salió ese valor.

Y los que son órdenes

wal_checkpoint vuelca el diario en la base y, con TRUNCATE, lo deja en cero bytes: se midió un -wal de 4 716 016 bytes que quedó en 0. ANALYZE llena sqlite_stat1 con lo que el planificador usará para elegir índice. Y PRAGMA optimize es el que conviene correr al cerrar una conexión de larga vida: mira qué tablas han cambiado bastante y lanza el ANALYZE que haga falta, sin decir nada.

PRAGMA wal_autocheckpoint;         -- 1000 paginas
PRAGMA wal_checkpoint(TRUNCATE);   -- 0 | 0 | 0, y el -wal queda en 0 bytes

ANALYZE;
SELECT * FROM sqlite_stat1;        -- t | iv | 20000 20000
PRAGMA optimize;                   -- no devuelve nada

Palabras clave: pragma, configuración, journal_mode, WAL, synchronous, foreign_keys, cache_size, mmap_size, auto_vacuum, page_size, user_version, wal_checkpoint, optimize, ANALYZE, sqlite_stat1

SCAN, SEARCH y COVERING son todo el vocabulario de EXPLAIN QUERY PLAN. Los índices parciales y por expresión y la condición que hay que repetir para que sirvan, qué ahorra WITHOUT ROWID y qué escribe ANALYZE.

Aplica a: SQLite 3.35+

En SQLite sólo hay una clase de índice: el árbol B. No hay hash, ni bitmap, ni nada que elegir. La búsqueda por palabras existe pero no es un índice: es FTS5, una tabla virtual aparte. Eso simplifica el tema entero, porque la única decisión que queda es sobre qué columnas y en qué orden.

Y se comprueba con EXPLAIN QUERY PLAN, cuyo vocabulario cabe en tres palabras: SCAN es leer la tabla entera, SEARCH es entrar por un índice, y COVERING es que ni siquiera hizo falta tocar la fila.

-- pedidos(id, cliente, estado, total, correo) con 20 000 filas

EXPLAIN QUERY PLAN
  SELECT * FROM pedidos WHERE cliente = 42 AND estado = 'pagado';
-- SCAN pedidos

CREATE INDEX i_cli ON pedidos(cliente);
-- SEARCH pedidos USING INDEX i_cli (cliente=?)

CREATE INDEX i_cli_est ON pedidos(cliente, estado);
-- SEARCH pedidos USING INDEX i_cli_est (cliente=? AND estado=?)

El compuesto se recorre por prefijos, igual que en cualquier motor: (cliente, estado) sirve para cliente solo y para los dos juntos, pero no para estado solo.

Cubriente

Si el índice lleva todas las columnas que la consulta lee, la fila no se toca. Es el mismo índice de antes: lo que cambia es lo que se pide.

EXPLAIN QUERY PLAN
  SELECT cliente, estado FROM pedidos WHERE cliente = 42;
-- SEARCH pedidos USING COVERING INDEX i_cli_est (cliente=?)

Parcial y por expresión: los dos hay que repetirlos

Un índice parcial indexa sólo las filas que cumplen una condición, y por eso ocupa poco. El precio es que la consulta tiene que repetir esa condición, palabra por palabra, o el planificador no puede usarlo: sin ella, la misma consulta vuelve a SCAN. Lo mismo con el índice por expresión: indexa lower(correo), así que hay que escribir lower(correo) en el WHERE; con correo a secas no sirve de nada.

CREATE INDEX i_parcial ON pedidos(total) WHERE estado = 'pagado';

EXPLAIN QUERY PLAN
  SELECT * FROM pedidos WHERE estado = 'pagado' AND total > 900;
-- SEARCH pedidos USING INDEX i_parcial (total>?)

EXPLAIN QUERY PLAN
  SELECT * FROM pedidos WHERE total > 900;
-- SCAN pedidos   <- sin repetir el filtro, el indice no existe

CREATE INDEX i_correo ON pedidos(lower(correo));

EXPLAIN QUERY PLAN
  SELECT * FROM pedidos WHERE lower(correo) = 'u42@ej.com';
-- SEARCH pedidos USING INDEX i_correo (<expr>=?)

WITHOUT ROWID quita una indirección

Una tabla normal guarda las filas por un rowid oculto, así que su clave primaria es otro índice que después tiene que ir a buscar la fila. Con WITHOUT ROWID la tabla es el árbol de su clave primaria: se ahorra el salto y se ahorra espacio —1 335 296 bytes contra 1 675 264 en la misma tabla de 20 000 filas, un 20 % menos—, y el plan lo delata al decir USING PRIMARY KEY en vez de nombrar un índice automático.

ANALYZE da números, no milagros

Llena sqlite_stat1 con el número de filas y cuántas hay por cada valor del índice. Ese segundo número es el que dice si un índice sirve de algo: 20 000 por valor significa que no distingue nada. Pero no siempre cambia la elección: en la medición, el planificador ya estaba eligiendo bien antes de correrlo, porque sin estadísticas usa suposiciones razonables. Correr ANALYZE quita las suposiciones; no promete un plan distinto.

CREATE TABLE kv (k TEXT PRIMARY KEY, v TEXT) WITHOUT ROWID;
-- 20 000 filas: 1 335 296 bytes, frente a 1 675 264 con rowid

EXPLAIN QUERY PLAN SELECT v FROM kv WHERE k = 'clave-000042';
-- SEARCH kv USING PRIMARY KEY (k=?)
-- con rowid habria dicho:  USING INDEX sqlite_autoindex_kv_1 (k=?)

ANALYZE;
SELECT tbl, idx, stat FROM sqlite_stat1;
-- t | ia | 20000 20000    <- 20 000 filas, 20 000 por cada valor de a
-- t | ib | 20000 1        <- 20 000 filas, 1 por cada valor de b

Palabras clave: índice, índices, árbol B, EXPLAIN QUERY PLAN, SCAN, SEARCH, COVERING INDEX, índice parcial, índice por expresión, WITHOUT ROWID, ANALYZE, sqlite_stat1, FTS5

Las mismas 20 000 inserciones medidas de seis maneras: la diferencia entre la mejor y la peor no está en ningún pragma, está en si hay un BEGIN. Y lo que sí aportan WAL, VACUUM y el tamaño de página.

Aplica a: SQLite 3.35+

Hay una sola cosa que importa, y no es un pragma. Las mismas 20 000 inserciones, con el mismo esquema y en la misma máquina:

440 veces, y lo que lo explica es que sin BEGIN cada INSERT es su propia transacción: veinte mil confirmaciones, cada una esperando al disco.

-- 20 000 INSERT, cada uno con su propia transaccion:  3963 ms
-- los mismos 20 000 aqui dentro:                          9 ms

BEGIN;
  INSERT INTO t (v) VALUES ('...');   -- x 20 000
COMMIT;

Cuidado con una trampa del intermediario: que el driver ofrezca una llamada de «inserción por lotes» no significa que abra una transacción. El executemany de Python, sin BEGIN explícito, tardó 4 128 ms: exactamente lo mismo que el bucle a mano.

Lo que aportan los otros

Apagar synchronous ahorró un 38 % y a cambio ofrece perder datos confirmados en un corte de luz: es la peor relación de la lista. WAL sin transacción bajó a 258 ms —quince veces— porque confirmar deja de reescribir la base, y ése sí es un cambio que se puede dejar puesto. Pero puestos los dos, el BEGIN se lo lleva casi todo: 9 ms sin WAL y 10 con él. Primero se agrupa, y sólo después se afina.

VACUUM es lo que devuelve el sitio

Borrar no encoge el archivo: las páginas quedan en una lista de libres para reutilizarlas. Se midió un archivo de 10 813 440 bytes del que se borró la mitad de las filas y que siguió midiendo exactamente lo mismo. VACUUM lo reescribe entero y lo dejó en 5 410 816. Cuesta una copia de la base y un candado exclusivo, así que no es una tarea de cada noche: es lo que se corre cuando un borrado grande dejó el archivo del doble de lo que le toca.

SELECT page_count * page_size
  FROM pragma_page_count(), pragma_page_size();   -- 10813440

DELETE FROM t WHERE i % 2 = 0;
PRAGMA freelist_count;   -- 1 pagina, y el archivo sigue igual de grande

VACUUM;                  -- 5410816 bytes, en 12 ms

El tamaño de página casi nunca hay que tocarlo

Se midieron tres. Bajarlo a 512 costó un 20 % más de archivo y un recorrido medible donde los otros dos no llegaban al milisegundo; subirlo a 65 536 no ganó nada. El de fábrica —4 096— es el que hay que dejar, y además sólo se puede cambiar antes de crear la primera tabla o pasando después por un VACUUM.

PRAGMA page_size = 512;     -- 5215744 bytes, y el recorrido en 3 ms
PRAGMA page_size = 4096;    -- 4333568 bytes, y en 0 ms   <- el de fabrica
PRAGMA page_size = 65536;   -- 4390912 bytes, y en 0 ms

Y dos cosas más que se miden solas

Cada índice se paga en cada escritura: las mismas 20 000 filas tardaron 7 ms sin índices propios, 12 con uno, 16 con dos y 20 con tres, y el archivo pasó de 458 752 a 1 277 952 bytes. Y una sentencia con parámetro se reutiliza: 5 000 consultas con ? tardaron 18 ms, y las mismas con el valor pegado dentro del SQL, 26 —además de ser la puerta de la inyección—.

Palabras clave: rendimiento, transacción, BEGIN, COMMIT, lote, executemany, synchronous, WAL, VACUUM, freelist_count, page_size, fragmentación, sentencia preparada

Lo que ALTER TABLE sabe hacer cabe en cuatro líneas, y lo que rechaza al añadir y al quitar una columna está medido con su mensaje. El rodeo de crear, copiar, borrar y renombrar, y por qué aquí sí es seguro.

Aplica a: SQLite 3.35+

ALTER TABLE sabe hacer cuatro cosas, y ninguna más. Cambiar el tipo de una columna, quitarle un NOT NULL, añadir una clave foránea: nada de eso existe, y el intento ni siquiera llega a ser un error de esquema, es un error de sintaxis.

ALTER TABLE t RENAME TO t2;            -- desde siempre
ALTER TABLE t RENAME COLUMN a TO a2;   -- 3.25
ALTER TABLE t ADD COLUMN g TEXT;       -- desde siempre
ALTER TABLE t DROP COLUMN b;           -- 3.35

ALTER TABLE t ALTER COLUMN g TYPE INTEGER;
-- near "ALTER": syntax error   <- no existe, y nunca ha existido

Lo que ADD COLUMN rechaza

Las tres negativas tienen la misma causa: la columna nueva se añade sin tocar las filas que ya están, así que el valor que reciben tiene que poder decidirse sin mirarlas. Un DEFAULT que cambia, un UNIQUE que habría que comprobar y un NOT NULL sin valor no cumplen eso.

ALTER TABLE t ADD COLUMN i TEXT DEFAULT (datetime('now'));
-- Cannot add a column with non-constant default

ALTER TABLE t ADD COLUMN j TEXT UNIQUE;
-- Cannot add a UNIQUE column

ALTER TABLE t ADD COLUMN k TEXT NOT NULL;
-- Cannot add a NOT NULL column with default value NULL

Lo que DROP COLUMN rechaza, y lo peor: lo que permite

Desde la 3.35 se puede quitar una columna, pero no si es de la clave primaria, ni si es UNIQUE, ni si un índice o una columna generada la nombran. Hasta ahí, bien. El problema es el caso que sí deja pasar: una columna que usa una vista se quita sin una queja, la vista queda rota, y PRAGMA integrity_check sigue diciendo ok porque no mira dentro de las vistas. Eso no lo avisa nadie hasta que alguien consulta.

ALTER TABLE t DROP COLUMN id;   -- cannot drop PRIMARY KEY column: "id"
ALTER TABLE t DROP COLUMN e;    -- cannot drop UNIQUE column: "e"
ALTER TABLE t DROP COLUMN c;    -- error in index i_c after drop column

CREATE VIEW v AS SELECT id, d FROM t;
ALTER TABLE t DROP COLUMN d;    -- PASA, sin una queja
SELECT * FROM v;                -- no such column: d
PRAGMA integrity_check;         -- ok

El rodeo de siempre

Para todo lo demás, el procedimiento es crear la tabla nueva, copiar, borrar la vieja y renombrar. Suena peligroso y aquí no lo es, por algo que MySQL no tiene: el DDL de SQLite es transaccional. Se midió: un CREATE TABLE y un ADD COLUMN dentro de un BEGIN, con ROLLBACK al final, no dejaron ni la tabla ni la columna. Así que todo el rodeo cabe en una transacción y, si algo sale mal a la mitad, no queda nada a medias.

PRAGMA foreign_keys = OFF;
BEGIN;
  CREATE TABLE t_nueva (id INTEGER PRIMARY KEY, a INTEGER NOT NULL);
  INSERT INTO t_nueva (id, a) SELECT id, CAST(a AS INTEGER) FROM t;
  DROP TABLE t;
  ALTER TABLE t_nueva RENAME TO t;
  -- y aqui se vuelven a crear indices, disparadores y vistas
COMMIT;
PRAGMA foreign_key_check;
PRAGMA foreign_keys = ON;

Tres cuidados. Los índices, disparadores y vistas de la tabla vieja se van con ella y hay que volver a crearlos, porque el DROP TABLE se los lleva. Las claves foráneas se apagan durante el rodeo y se comprueban con foreign_key_check antes de volver a encenderlas. Y la conversión de tipos es tuya: un CAST('a' AS INTEGER) devuelve 0, sin avisar de nada.

Palabras clave: DDL, ALTER TABLE, RENAME TO, RENAME COLUMN, ADD COLUMN, DROP COLUMN, 3.25, 3.35, vista rota, integrity_check, DDL transaccional, doce pasos, CAST

Una base en modo WAL son tres archivos y los datos casi nunca están en el primero: copiarlo deja una base vacía que dice estar sana. Las tres maneras que sí valen, medidas, y qué encuentra cada comprobación de integridad.

Aplica a: SQLite 3.35+

Una base de SQLite parece un archivo, y ahí empieza el problema. En modo WAL son tres, y el que lleva el nombre puede no llevar ningún dato: tras escribir 30 000 filas, el .sqlite medía 4 096 bytes —la cabecera y poco más— y el -wal medía 3 366 072.

Copiar sólo el primero no da una base rota. Da algo peor: una base que abre sin quejarse, que no tiene ni una tabla, y a la que PRAGMA integrity_check le dice ok. Un respaldo así pasa todas las comprobaciones y no contiene nada.

PRAGMA journal_mode = WAL;
-- tras 30 000 filas, los tres archivos miden:
--   base.sqlite         4096   <- solo la cabecera
--   base.sqlite-shm    32768
--   base.sqlite-wal  3366072   <- aqui estan los datos

-- copiar solo base.sqlite da una base que abre, y esta VACIA:
SELECT count(*) FROM sqlite_schema;   -- 0
PRAGMA integrity_check;               -- ok

Las tres maneras que sí valen

VACUUM INTO escribe una copia limpia y desfragmentada en otro archivo, con la base en uso: 3 338 240 bytes en 4 ms, con las 30 000 filas. La API de respaldo —el .backup de la línea de órdenes, y Connection.backup en los drivers— hace lo mismo copiando páginas y puede ir por partes: 3 338 240 bytes en 3 ms. Y .dump escribe el SQL que reconstruye la base: 4 008 968 bytes de texto y 30 003 sentencias, un 20 % más que el binario, pero es lo único que se lee con los ojos y sobrevive a un cambio de formato.

VACUUM INTO '/ruta/copia.sqlite';
-- 3338240 bytes en 4 ms, con las 30 000 filas dentro
-- y la copia sale en journal_mode delete, no en WAL

Detalle que se agradece: la copia de VACUUM INTO sale en journal_mode delete, no en WAL. Es un solo archivo, que es justo lo que se quiere de un respaldo.

Copiar los tres archivos a la vez con la base parada sí funciona. El problema es «a la vez» y «parada»: mientras alguien escriba, no hay instante en que los tres cuadren, y ninguna herramienta de copia lo garantiza.

Comprobar lo que se tiene

integrity_check recorre la base entera y quick_check se salta las comprobaciones cruzadas entre índices y tablas. Sobre una base sana los dos dijeron ok, y sobre la misma base con unos cientos de bytes machacados a propósito, los dos dijeron exactamente lo mismo: Tree 2 page 4 cell 35: Rowid 0 out of order. La diferencia entre ambos sólo se nota en una base grande, y ninguno de los dos arregla nada: sirven para decidir si hay que volver al respaldo.

PRAGMA quick_check;       -- ok
PRAGMA integrity_check;   -- ok

-- con la misma base danada a proposito, los dos contestan igual:
-- *** in database main ***
-- Tree 2 page 4 cell 35: Rowid 0 out of order

Y cuando ya es tarde

Restaurar un .dump es correr su SQL contra una base vacía. Restaurar una copia binaria es ponerla en su sitio, y ahí conviene saber que VACUUM INTO se niega a sobrescribir: sobre un archivo que ya existe contesta «output file already exists», así que no hay manera de pisar el respaldo de ayer sin darse cuenta. Y si lo que hay es una base dañada y ningún respaldo, queda .recover de la línea de órdenes, que recorre las páginas que aún se entienden y escribe el SQL para reconstruir lo que se pueda: no promete todo, promete lo que quede.

Palabras clave: respaldo, copia de seguridad, VACUUM INTO, API de respaldo, backup, dump, WAL, -wal, -shm, integrity_check, quick_check, corrupción, recuperación

Los ocho que salen de verdad, provocados uno a uno con su texto, y la aritmética del código extendido: el 19 de restricción se convierte en 275, 787, 1299, 1555 o 2067 según qué se incumplió.

Aplica a: SQLite 3.35+

SQLite tiene dos juegos de códigos: uno básico, de una o dos cifras, y otro extendido, que dice lo mismo con más detalle. Y la relación entre los dos es aritmética: el extendido es el básico más 256 por el subtipo, así que codigo & 255 devuelve siempre el básico. Un driver que sólo enseña el básico te está escondiendo la mitad.

El 19 es cinco errores distintos

El básico no dice nada útil, porque una restricción incumplida puede ser cualquiera de cinco. El extendido sí, y el mensaje ayuda… con una excepción: la clave primaria de un INTEGER PRIMARY KEY da 1555, pero su texto dice «UNIQUE constraint failed». O sea que ahí el número es más preciso que la frase.

CREATE TABLE t (id INTEGER PRIMARY KEY, u TEXT UNIQUE, nn TEXT NOT NULL,
                ch INTEGER CHECK (ch > 0), p INTEGER REFERENCES padre(id));

INSERT INTO t VALUES (1,'b','x', 5,   1);   -- 1555  UNIQUE constraint failed: t.id
INSERT INTO t VALUES (2,'a','x', 5,   1);   -- 2067  UNIQUE constraint failed: t.u
INSERT INTO t VALUES (3,'c',NULL,5,   1);   -- 1299  NOT NULL constraint failed: t.nn
INSERT INTO t VALUES (4,'d','x',-1,   1);   --  275  CHECK constraint failed: ch > 0
INSERT INTO t VALUES (5,'e','x', 5, 999);   --  787  FOREIGN KEY constraint failed

El 5 y el 6 se confunden y no son lo mismo

El 5 es de fuera: otra conexión está escribiendo, y se arregla esperando —ahí sirve busy_timeout—. El 6 es de dentro: la misma conexión tiene un cursor abierto sobre la tabla que quiere cambiar, y esperar no sirve de nada porque el que bloquea eres tú. Se arregla cerrando el cursor.

PRAGMA max_page_count = 20;
INSERT INTO t ... ;      -- 13  SQLITE_FULL      database or disk is full

-- con la base abierta en modo solo lectura:
INSERT INTO t ... ;      --  8  SQLITE_READONLY  attempt to write a readonly database

-- con un cursor de SELECT todavia abierto, en la MISMA conexion:
DROP TABLE t;            --  6  SQLITE_LOCKED    database table is locked

El 13 casi nunca es el disco

SQLITE_FULL suena a partición llena y muchas veces es el tope que la propia base se puso: max_page_count. Con 20 páginas se provoca en una línea, y el mensaje es el mismo que daría un disco lleno de verdad: «database or disk is full».

El 11, el 26 y el que no se ve

Los dos de archivo roto se distinguen por dónde está el daño: si lo que no se entiende es una página, SQLITE_CORRUPT con «database disk image is malformed»; si lo que no se entiende es la cabecera, ni llega a intentarlo y dice SQLITE_NOTADB. Y el 21 es el raro: llamar a la API en un orden imposible. Casi nunca se ve, porque el driver de turno lo caza antes y lanza un error suyo; en Python, sin ir más lejos, sale un ProgrammingError que ni siquiera trae código de SQLite.

-- con la pagina del esquema machacada:
SELECT count(*) FROM t;   -- 11  SQLITE_CORRUPT  database disk image is malformed

-- con el tamano de pagina de la cabecera machacado:
SELECT count(*) FROM t;   -- 26  SQLITE_NOTADB   file is not a database

Palabras clave: errores, códigos, SQLITE_BUSY, 5, SQLITE_LOCKED, 6, SQLITE_READONLY, 8, SQLITE_CORRUPT, 11, SQLITE_FULL, 13, SQLITE_CONSTRAINT, 19, 275, 787, 1299, 1555, 2067, SQLITE_MISUSE, 21, SQLITE_NOTADB, 26

Los topes de SQLite no son del formato sino del binario, y se leen con PRAGMA compile_options. Los ocho que se tocan, provocados uno a uno, y el único que se pasa sin avisar de nada.

Aplica a: SQLite 3.35+

Los topes de SQLite tienen una particularidad que no tiene ningún otro motor: no son del formato, son del binario con el que estés hablando. Se fijan al compilar, y por eso la respuesta a «¿cuánto es el máximo?» empieza por PRAGMA compile_options, que los enseña todos. Un programa puede además bajarlos en caliente con sqlite3_limit, nunca subirlos.

Éstos son los del binario que trae macOS, y los cinco primeros se provocaron.

CREATE TABLE b (c0, c1, ... , c2000);
-- too many columns on b

SELECT 1 UNION ALL SELECT 1 UNION ALL ... ;   -- 501 veces
-- too many terms in compound SELECT

ATTACH DATABASE 'x11.sqlite' AS a11;
-- too many attached databases - max 10

El que no avisa

PRAGMA page_size = 131072 no da error, no devuelve nada raro y no cambia nada: la página se queda en 4 096. El máximo es 65 536 y lo que se pide por encima se descarta en silencio, así que la única manera de saber si se cogió es volver a leerlo. Es el mismo modo de fallo que ya tienen page_size y auto_vacuum sobre una base con tablas: se aceptan y no hacen nada.

PRAGMA page_size = 131072;   -- ni error ni aviso
PRAGMA page_size;            -- 4096   <- no lo cogio
PRAGMA page_size = 65536;    -- este si

Cuánto cabe de verdad

El tamaño máximo del archivo no es una constante: es max_page_count por el tamaño de página. Con los valores de fábrica —1 073 741 823 páginas de 4 096 bytes— salen 4 TiB, y subiendo la página a 65 536, 64 TiB. Antes de acercarse a eso lo que se acaba es otra cosa: un texto no pasa de 1 000 000 000 bytes, y un SELECT con más de 250 000 parámetros no se puede ni preparar.

Y una advertencia sobre la tabla de arriba: es la de este binario. El de un teléfono, el de una biblioteca empotrada o el que compiló alguien con sus propias banderas pueden traer otros números, y por eso la respuesta útil nunca es el valor: es el comando que lo pregunta.

PRAGMA compile_options;
-- MAX_COLUMN=2000          MAX_COMPOUND_SELECT=500   MAX_ATTACHED=10
-- MAX_LENGTH=1000000000    MAX_PAGE_SIZE=65536       MAX_EXPR_DEPTH=1000
-- MAX_VARIABLE_NUMBER=250000                         MAX_FUNCTION_ARG=1000

PRAGMA max_page_count;   -- 1073741823
-- x 4096 de pagina = 4 TiB de archivo; x 65536 = 64 TiB

Bajarlos es una defensa

Que sqlite3_limit sólo sepa bajar no es una limitación: es para lo que existe. Una aplicación que acepta SQL escrito por otro baja LENGTH, COMPOUND_SELECT y EXPR_DEPTH a lo que de verdad necesita, y con eso una consulta hostil deja de poder pedir un gigabyte de memoria. Es la misma idea que el max_page_count del tema de errores: el tope que se pone uno mismo avisa antes que el del sistema, y avisa de algo que se puede arreglar.

Palabras clave: límites, topes, compile_options, MAX_COLUMN, MAX_COMPOUND_SELECT, MAX_ATTACHED, MAX_LENGTH, MAX_PAGE_SIZE, max_page_count, sqlite3_limit, tamaño máximo, columnas, ATTACH

El resumen del manual de SQLite: siete costumbres con la medición que las sostiene, cuatro de ellas en las líneas que van justo después de abrir la conexión, y las seis con las que se le toma el pulso a un archivo ajeno.

Aplica a: SQLite 3.35+

Éste es el final del manual, y no trae nada nuevo: recoge lo que cada tema dejó medido. Lo llamativo es dónde caen cuatro de las siete: en las líneas que se escriben justo después de abrir la conexión, y que casi ningún programa escribe.

1. PRAGMA journal_mode = WAL. Se queda escrito en el archivo, así que basta una vez. Con él, un lector siguió leyendo mientras otro escribía, sin bloquearse un instante. Lo que no arregla es el número de escritores: sigue siendo uno.

2. PRAGMA foreign_keys = ON, en cada conexión. Es lo único de esta lista que cambia lo que la base acepta, y viene apagado: con él apagado, un hijo huérfano entra sin una queja. Y no se queda puesto, así que va en el mismo sitio que el busy_timeout.

3. PRAGMA busy_timeout, y puesto por ti. El motor lo trae en 0 —contesta SQLITE_BUSY en el acto— pero muchos drivers lo cambian al conectarse: el de Python lo deja en 5 000 sin decir nada. Así que el número que importa no es el de la documentación: es el que devuelve PRAGMA busy_timeout en tu conexión.

4. Agrupa las escrituras en una transacción. Es, con diferencia, lo que más cambia: las mismas 20 000 inserciones tardaron 3 963 ms una a una y 9 ms dentro de un BEGIN. Y ojo con el intermediario: una llamada de «inserción por lotes» del driver no abre una transacción por su cuenta.

PRAGMA journal_mode;    -- wal, o delete si nadie lo ha tocado
PRAGMA foreign_keys;    -- 0 casi siempre, y casi siempre es un error
PRAGMA synchronous;     -- 2 con diario, 1 en WAL
PRAGMA busy_timeout;    -- el de TU conexion, no el del motor
PRAGMA page_count;      -- x page_size = lo que ocupa
PRAGMA quick_check;     -- ok

5. Respalda con VACUUM INTO, nunca copiando el archivo. En modo WAL los datos están en el -wal, así que copiar el .sqlite da una base que abre, no tiene ni una tabla y a la que integrity_check le dice ok. Un respaldo que pasa todas las comprobaciones y está vacío es peor que no tener ninguno.

6. Un índice por consulta frecuente, y mira lo que pesa. dbstat lo dice por objeto, y sorprende: en la base medida, el índice ocupaba 2 056 192 bytes contra 1 826 816 de la tabla que indexaba.

SELECT name, SUM(pgsize) AS bytes
  FROM dbstat
 GROUP BY name
 ORDER BY bytes DESC;
-- iv             2056192   <- el indice pesa mas que la tabla
-- t              1826816
-- sqlite_schema     4096

7. La seguridad es del archivo. No hay usuarios, ni roles, ni GRANT: quien puede leer el archivo puede leerlo todo, y quien puede escribirlo puede borrarlo. La protección son los permisos del sistema, el cifrado del disco y —en un teléfono— la clase de protección de datos. Todo lo demás de este manual es rendimiento; esto es lo único que no tiene sustituto.

Y una que no es una costumbre sino un límite

SQLite aguanta muchísimo más de lo que su fama sugiere, pero tiene una frontera que ninguna práctica mueve: escribe uno a la vez. Mientras las escrituras vengan de un proceso, o de varios que se turnan, el archivo da de sí hasta límites que casi nadie toca. El día que hagan falta dos escritores de verdad y a la vez, lo que hay que cambiar no es un pragma: es de motor.

Palabras clave: buenas prácticas, resumen, WAL, foreign_keys, busy_timeout, transacción por lotes, VACUUM INTO, respaldo, dbstat, índices, permisos, cifrado, seguridad

Mínimo privilegio, roles, conexiones cifradas y la lista de comprobación antes de exponer un servidor.

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

En MySQL y MariaDB la identidad de un usuario son dos cosas: el nombre y el host desde el que se conecta. 'app'@'10.0.%' y 'app'@'%' son cuentas distintas, con contraseñas y permisos distintos. La mayoría de los sustos de seguridad empiezan por olvidar eso.

Mínimo privilegio
Concede lo que la aplicación usa, ni una más, y al host más estrecho posible. Una cuenta de aplicación casi nunca necesita DROP, y jamás SUPER, FILE ni GRANT OPTION:

CREATE USER 'app'@'10.0.%' IDENTIFIED BY '...';

GRANT SELECT, INSERT, UPDATE, DELETE ON tienda.* TO 'app'@'10.0.%';

GRANT SELECT ON tienda.pedidos TO 'informes'@'%';

SHOW GRANTS FOR 'app'@'10.0.%';

Roles

CREATE ROLE 'lectura', 'escritura';

GRANT SELECT ON tienda.* TO 'lectura';
GRANT INSERT, UPDATE, DELETE ON tienda.* TO 'escritura';

GRANT 'lectura' TO 'informes'@'%';
GRANT 'lectura', 'escritura' TO 'app'@'10.0.%';

SET DEFAULT ROLE ALL TO 'app'@'10.0.%';

Conexiones cifradas
Sin TLS, la contraseña y los datos viajan legibles por la red. Se puede exigir por cuenta o para todo el servidor con require_secure_transport. Calíope soporta TLS en el perfil de conexión, y también túnel SSH cuando el servidor no está expuesto:

ALTER USER 'app'@'10.0.%' REQUIRE SSL;

SHOW VARIABLES LIKE 'require_secure_transport';

SELECT user, host, ssl_type FROM mysql.user;

Auditoría rápida
Tres consultas que conviene pasar en cualquier servidor heredado. Cuentas sin contraseña, cuentas abiertas a cualquier host y privilegios peligrosos repartidos:

SELECT user, host FROM mysql.user WHERE authentication_string = '';

SELECT user, host FROM mysql.user WHERE host = '%';

SELECT * FROM information_schema.USER_PRIVILEGES
WHERE privilege_type IN ('SUPER', 'FILE', 'PROCESS', 'GRANT OPTION');

Antes de exponer un servidor
1. Sin cuentas anónimas ni sin contraseña, y sin la base test de ejemplo.
2. root solo desde localhost, con una cuenta administrativa aparte para lo demás.
3. bind-address al interfaz correcto — no a 0.0.0.0 si nadie de fuera debe llegar.
4. TLS obligatorio para cualquier conexión que salga de la máquina.
5. Contraseñas gestionadas fuera del código — Calíope las guarda en el Llavero, no en texto plano.
6. Cuentas separadas por aplicación, para que un compromiso no arrastre al resto.
7. Revisar los GRANT periódicamente: los permisos se acumulan y nadie los quita.

Recomendación
Empieza por revocar en vez de por conceder: crea la cuenta sin nada y añade privilegios hasta que la aplicación funcione. La herramienta de Usuarios de Calíope muestra los privilegios efectivos por base y por tabla, que es donde suelen aparecer las sorpresas.

Palabras clave: seguridad, usuario, rol, privilegio, grant, revoke, mínimo privilegio, ssl, tls, require ssl, hardening, mysql.user, user_privileges, auditoría

Lógico contra físico, para qué sirve el binlog y cómo volver al minuto anterior al DELETE.

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

Un respaldo que nunca se ha restaurado no es un respaldo: es una intención. Dos números mandan aquí: el RPO (cuántos datos aceptas perder) y el RTO (cuánto puedes estar caído). Todo lo demás sale de ahí.

Lógico contra físico
- Lógico (mysqldump, el Respaldo de Calíope) — genera SQL. Portátil entre versiones y motores, permite restaurar una sola tabla, y es lento de restaurar en volúmenes grandes.
- Físico (snapshot del volumen, Percona XtraBackup, copia del directorio con el servidor parado) — copia los archivos. Rapidísimo de restaurar, pero atado a la versión y a la arquitectura del servidor.

La regla práctica: hasta unas decenas de gigabytes, lógico; a partir de ahí, físico para la copia completa y lógico para las piezas sueltas.

El binlog es la mitad que falta
El respaldo te devuelve al momento en que se hizo. El binary log contiene todo lo ocurrido después, y es lo que permite avanzar desde ahí hasta un segundo antes del desastre. Sin log_bin activo no hay recuperación a un punto en el tiempo, solo vuelta a la última copia:

SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';

SHOW BINARY LOGS;

Recuperar a un punto en el tiempo
El procedimiento, siempre en un servidor aparte y nunca sobre el que está en producción:
1. Restaura la copia completa más reciente anterior al incidente.
2. Localiza el momento exacto del error en el binlog: la sentencia que borró de más, y su posición o su marca de tiempo.
3. Reproduce el binlog desde la posición donde acabó la copia hasta justo antes de esa sentencia, con mysqlbinlog y sus opciones --start-position y --stop-position (o --start-datetime y --stop-datetime).
4. Comprueba que los datos están, y solo entonces decide si promueves ese servidor o exportas de él lo que falta.

El Visor de binlog de Calíope sirve para el paso 2: filtra los eventos por fecha, base y tipo de operación, que es lo que cuesta encontrar a mano.

Encontrar la posición
SHOW MASTER STATUS dice el archivo y la posición actuales; los eventos de un binlog concreto se listan así:

SHOW MASTER STATUS;

SHOW BINLOG EVENTS IN 'binlog.000042'
LIMIT 20;

Verificar la restauración
Restaurar sin comprobar es la forma habitual de descubrir el problema tarde. Un recuento por base y un CHECKSUM TABLE de las tablas críticas contra el origen bastan para dormir tranquilo:

SELECT table_schema, COUNT(*) AS tablas, SUM(table_rows) AS filas
FROM information_schema.TABLES
WHERE table_type = 'BASE TABLE'
GROUP BY table_schema;

CHECKSUM TABLE pedidos, lineas;

Recomendación
Programa el respaldo (Calíope lo hace, con retención configurable), guarda una copia fuera de la máquina, activa log_bin con una retención que cubra al menos dos ciclos de copia, y ensaya una restauración completa al menos una vez. El día del incidente no es el día de aprender el procedimiento.

Palabras clave: respaldo, backup, restauración, recuperación, pitr, punto en el tiempo, binlog, mysqldump, mysqlbinlog, rpo, rto, checksum table, snapshot

ALGORITHM, LOCK, bloqueos de metadatos y cuándo hace falta una herramienta externa.

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

Un ALTER TABLE en una tabla grande puede tardar horas y dejar la aplicación esperando. Desde MySQL 5.6 y MariaDB 10.0 se puede pedir cómo debe hacerse el cambio, y así saber de antemano si va a doler.

Pedir el algoritmo, no confiar en el azar
Si declaras el algoritmo y el servidor no puede usarlo, la sentencia falla en el acto en vez de bloquearte la tabla tres horas. Ese es el motivo principal para escribirlo siempre:

ALTER TABLE pedidos
    ADD COLUMN nota VARCHAR(255) NULL,
    ALGORITHM=INSTANT;

ALTER TABLE pedidos
    ADD INDEX idx_fecha (fecha),
    ALGORITHM=INPLACE, LOCK=NONE;

ALTER TABLE pedidos
    MODIFY COLUMN total DECIMAL(12,2) NOT NULL,
    ALGORITHM=COPY, LOCK=SHARED;

La cláusula LOCK
- LOCK=NONE — lectura y escritura siguen durante el cambio. Si no es posible, error.
- LOCK=SHARED — se puede leer, no escribir.
- LOCK=EXCLUSIVE — nadie toca la tabla.

Declarar LOCK=NONE es la forma de garantizar que la migración no va a parar la producción: o corre sin bloquear, o no corre.

El bloqueo de metadatos, el que sorprende
Aunque el ALTER sea instantáneo, necesita un bloqueo exclusivo de metadatos al principio y al final. Si hay una transacción vieja abierta sobre esa tabla, el ALTER espera — y todas las consultas que lleguen después se ponen detrás de él. Una tabla se queda congelada por un ALTER que iba a durar un milisegundo. Antes de tocar el esquema, comprueba que no hay transacciones largas:

SELECT object_name, lock_type, lock_status, owner_thread_id
FROM performance_schema.metadata_locks
WHERE object_schema = DATABASE();

SELECT @@lock_wait_timeout;

Ver el progreso
Un ALTER de horas no da señales por sí mismo. performance_schema sí:

SELECT stage, work_completed, work_estimated,
       ROUND(work_completed / work_estimated * 100, 1) AS pct
FROM performance_schema.events_stages_current;

SHOW PROCESSLIST;

Cuándo hace falta una herramienta externa
Si el cambio obliga a ALGORITHM=COPY sobre una tabla de decenas de gigabytes, ni el mejor LOCK te salva. Ahí entran pt-online-schema-change (Percona) y gh-ost (GitHub): crean una tabla nueva, copian por lotes, mantienen la sincronía con triggers o leyendo el binlog, y hacen el intercambio al final en un instante. No vienen con el servidor; se instalan aparte y se ejecutan desde la línea de comandos.

Recomendación
Escribe siempre ALGORITHM= y LOCK= en tus migraciones, y pruébalas antes en una copia con datos reales para saber cuánto van a tardar. Un ALTER que falla al segundo es una buena noticia comparado con uno que bloquea la tabla a mitad de la mañana.

Palabras clave: ddl online, alter table, algorithm, instant, inplace, copy, lock=none, metadata lock, mdl, pt-online-schema-change, gh-ost, migración de esquema

Por qué utf8 no es UTF-8, qué decide una collation y cómo convertir sin romper los índices.

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

Dos conceptos que se confunden todo el tiempo: el juego de caracteres dice qué caracteres se pueden guardar, y el cotejamiento (collation) dice cómo se comparan y se ordenan. El primero afecta a lo que cabe; el segundo, a lo que devuelve un WHERE.

utf8 no es UTF-8
En MySQL, utf8 es un alias histórico de utf8mb3: solo tres bytes por carácter, así que no puede guardar emoji ni buena parte del chino, japonés o coreano moderno. El UTF-8 de verdad es utf8mb4. Es la trampa más repetida del producto, y en MySQL 8.0 sigue viva por compatibilidad. Comprueba dónde estás:

SELECT default_character_set_name, default_collation_name
FROM information_schema.SCHEMATA
WHERE schema_name = DATABASE();

SELECT table_name, column_name, character_set_name, collation_name
FROM information_schema.COLUMNS
WHERE table_schema = DATABASE() AND character_set_name IS NOT NULL
  AND character_set_name <> 'utf8mb4';

SHOW VARIABLES LIKE 'character_set%';

Qué decide una collation
El nombre lo dice todo si sabes leerlo. En utf8mb4_0900_ai_ci: 0900 es la versión de Unicode, ai es accent-insensitive y ci es case-insensitive. Sus contrarios son as (sensible a acentos) y cs (sensible a mayúsculas). También existe utf8mb4_bin, que compara byte a byte y no entiende de idiomas.

Las predeterminadas difieren: MySQL 8.0 usa utf8mb4_0900_ai_ci y MariaDB utf8mb4_general_ci o utf8mb4_uca1400_ai_ci según la versión. Si mueves datos entre ambos, no des por hecho que ordenan igual.

Lo que cambia en la práctica
Con una collation ai_ci, café y cafe son el mismo valor: una UNIQUE rechazará el segundo, y un WHERE encontrará ambos. Puede ser justo lo que quieres para buscar nombres, y un desastre para guardar identificadores:

SELECT 'cafe' = 'café' COLLATE utf8mb4_0900_ai_ci AS acentos_iguales,
       'Ana'  = 'ana'  COLLATE utf8mb4_0900_ai_ci AS mayusculas_iguales;

SELECT * FROM clientes
WHERE nombre = 'jose' COLLATE utf8mb4_0900_as_cs;

SHOW COLLATION WHERE charset = 'utf8mb4';

Mezclar collations duele
Un JOIN entre una columna utf8mb4_general_ci y otra utf8mb4_0900_ai_ci da el error 1267 Illegal mix of collations. Y si lo arreglas envolviendo la columna en CONVERT() o en un COLLATE, la consulta deja de poder usar el índice de esa columna. El arreglo bueno no es el COLLATE en la consulta: es unificar la collation en el esquema.

Convertir sin sorpresas
ALTER DATABASE solo cambia el valor por omisión para las tablas futuras; las existentes hay que convertirlas una a una. Y CONVERT TO CHARACTER SET reescribe la tabla entera, así que va con la misma prudencia que cualquier DDL pesado:

ALTER DATABASE tienda
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;

ALTER TABLE clientes
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;

Recomendación
utf8mb4 en todo —servidor, base, tabla, columna y conexión del cliente— y una sola collation en todo el esquema. Antes de convertir, mira los índices sobre columnas de texto largas: al pasar de utf8mb3 a utf8mb4 cada carácter puede ocupar un byte más y un índice que cabía puede dejar de caber.

Palabras clave: charset, juego de caracteres, collation, cotejamiento, utf8, utf8mb4, latin1, emoji, acentos, mayúsculas, convert to character set, illegal mix of collations

Los códigos que más aparecen —1045, 1062, 1213, 2006— y qué hacer con cada uno.

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

Los códigos por debajo de 2000 los emite el servidor; los de 2000 en adelante, la biblioteca cliente. Esa sola distinción ya orienta dónde mirar: si el número empieza por 2, el problema está en la conexión, no en el SQL.

1045 y 1040: la conexión
El 1045 casi nunca es la contraseña: es que la cuenta existe para otro host. Recuerda que 'app'@'localhost' y 'app'@'%' son cuentas distintas. El 1040 significa que se acabaron las conexiones, y la causa habitual no es el tamaño del pool sino conexiones que nadie cierra:

SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'max_allowed_packet';

1062: entrada duplicada
El mensaje incluye la clave que chocó. Si el duplicado es esperable —una importación que se repite, un upsert— hay sintaxis para no tratarlo como error:

SELECT email, COUNT(*) AS repetidos
FROM clientes
GROUP BY email
HAVING repetidos > 1;

INSERT INTO clientes (email, nombre) VALUES ('a@b.c', 'Ana')
ON DUPLICATE KEY UPDATE nombre = VALUES(nombre);

1215: no se puede crear la clave foránea
El mensaje es célebre por no decir nada. Las causas reales son siempre las mismas cuatro: los tipos de las dos columnas no coinciden exactamente (incluido el signo y la longitud), no coinciden sus juegos de caracteres, falta un índice en la columna referenciada, o ya existen filas huérfanas que la restricción no admitiría:

SELECT constraint_name, table_name, referenced_table_name
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE constraint_schema = DATABASE();

SELECT l.* FROM lineas l
LEFT JOIN pedidos p ON p.id = l.pedido_id
WHERE p.id IS NULL;

Leer bien el error
Antes de buscar el código en internet, léelo entero: MySQL suele decir la tabla, la columna y el valor exactos. Y cuando una sentencia devuelve un aviso en vez de un error, SHOW WARNINGS justo después enseña lo que el servidor decidió por su cuenta —un truncamiento silencioso, por ejemplo—, que es peor que un fallo limpio.

Recomendación
Calíope muestra el código y el mensaje del servidor tal cual, sin envolverlos: ese texto es la mejor pista y conviene copiarlo entero cuando pidas ayuda. El Registro de consultas guarda además la sentencia que lo provocó, con su hora y su duración.

Palabras clave: error, código de error, 1045, 1049, 1062, 1146, 1213, 1205, 1215, 1267, 1406, 2002, 2006, 2013, 1040, too many connections, gone away, access denied, duplicate entry