Manuale del DBA

Intervalli, dimensione in byte e casi d'uso dei tipi numerici, di testo, data/ora, JSON e spaziali. Include le differenze rilevanti tra MySQL e MariaDB.

Si applica a: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

La scelta del tipo di dato incide sulla dimensione su disco, sulla velocità degli index e sull'integrità dei dati. Questa guida riassume i tipi più usati in MySQL e MariaDB, con i loro intervalli, la loro dimensione in byte e i casi d'uso tipici.

Numerici interi

- TINYINT — 1 byte, intervallo con segno −128…127 (senza segno 0…255). Utile per flag booleani o stati piccoli.
- SMALLINT — 2 byte, −32 768…32 767. Età, quantità piccole.
- MEDIUMINT — 3 byte, −8 388 608…8 388 607. Esclusivo di MySQL/MariaDB; raramente usato al di fuori dell'ecosistema.
- INT (INTEGER) — 4 byte, ±2,1·10⁹. Tipo predefinito per le chiavi primarie in tabelle di dimensioni medie.
- BIGINT — 8 byte, ±9,2·10¹⁸. Chiavi primarie in tabelle grandi, identificatori distribuiti.

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

Numerici decimali e in virgola mobile

- DECIMAL(M, D) — precisione esatta, M cifre totali e D decimali. Obbligatorio per il denaro.
- FLOAT — 4 byte, circa 7 cifre significative. Approssimato.
- DOUBLE — 8 byte, circa 15 cifre. Approssimato.

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

Testo e stringhe

- CHAR(N) — lunghezza fissa di N caratteri, fino a 255. Veloce quando tutte le righe hanno la stessa dimensione (codici di paese, hash).
- VARCHAR(N) — lunghezza variabile, fino a 65 535 byte per riga (condivisi con le altre colonne). Usa 1 o 2 byte aggiuntivi per la lunghezza.
- TEXT, MEDIUMTEXT, LONGTEXT — 64 KiB, 16 MiB, 4 GiB. Vengono memorizzati fuori dalla riga; non si possono usare come chiave senza un prefisso (KEY (col(255))).
- BLOB, MEDIUMBLOB, LONGBLOB — equivalenti binari.

Data e ora

- DATE — 3 byte, '1000-01-01'…'9999-12-31'.
- TIME — 3 byte, '-838:59:59'…'838:59:59'. Sì, può essere maggiore di 24 ore (intervallo, non ora del giorno).
- DATETIME — 8 byte, senza fuso orario, senza conversione al salvataggio/lettura. Persiste la stringa letterale.
- TIMESTAMP — 4 byte, intervallo 1970…2038 (in MySQL 5.7) o 1970…2106 (in MariaDB 10.4+). Viene memorizzato in UTC e convertito al time_zone della connessione.
- 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 in MySQL 5.7+ e MariaDB 10.2+. Consente di indicizzare campi estratti con JSON_EXTRACT o ->> e, da MySQL 8.0, colonne generate con index (MULTI-VALUED INDEX sugli array).

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

Spaziali (GIS)

- POINT, LINESTRING, POLYGON, GEOMETRY, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION.
- Richiedono un index SPATIAL per query efficienti (MBRContains, ST_Distance, ST_Within).
- In MySQL 8.0, lo SRID è obbligatorio per usare gli index spaziali.

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 e SET

- ENUM — memorizza uno tra N valori predefiniti (fino a 65 535). Compatto (1–2 byte), ma rigido: cambiare la lista richiede un ALTER TABLE.
- SET — combinazione di fino a 64 valori come bitmap. Utile per permessi o etichette fisse.

Evitali se la lista di valori cambia di frequente; una tabella di catalogo + chiave esterna è più mantenibile.

Come scegliere

1. Usa il tipo più piccolo che copra l'intervallo previsto. Un BIGINT dove basta INT quadruplica lo spazio dell'index.
2. Marca le colonne come UNSIGNED quando non ti servono valori negativi: raddoppi l'intervallo.
3. Evita NULL quando puoi: una colonna NOT NULL DEFAULT … risparmia 1 bit per riga e negli index.
4. VARCHAR(255) non è più costoso di VARCHAR(20) se i dati reali entrano in 20 — per gli index con prefisso conta solo la lunghezza dichiarata.

Parole chiave: tipi di dati, INT, BIGINT, VARCHAR, TEXT, JSON, DATETIME, TIMESTAMP, DECIMAL, ENUM, SET, POINT, SPATIAL, BLOB, intervallo, byte

Tipi di index (B-tree, hash, fulltext, spaziale), index semplici e composti e strategie per ottimizzare la lettura senza penalizzare la scrittura.

Si applica a: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Un index accelera la ricerca al prezzo di spazio su disco e di un costo su ogni INSERT/UPDATE/DELETE. Una buona progettazione degli index è la differenza tra una query di millisecondi e una di minuti.

Tipi di index

- B-tree — Predefinito in InnoDB. Supporta ricerche per uguaglianza, intervallo (>, <, BETWEEN), prefisso (LIKE 'abc%') e ordinamento (ORDER BY).
- Hash — Supporta solo l'uguaglianza. Disponibile nel motore MEMORY. InnoDB mantiene un adaptive hash index interno che non controlli direttamente.
- FULLTEXT — Ricerca di testo naturale e booleana. Disponibile in InnoDB e MyISAM. Utile per campi TEXT lunghi.
- SPATIAL — R-tree per i tipi POINT, POLYGON, ecc. Richiede una colonna NOT NULL.
- Multi-valued — Indicizza gli elementi di un array JSON. Solo in 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);

Semplici vs composti

Un index composto su (A, B, C) copre le ricerche con prefisso: WHERE A = ?, WHERE A = ? AND B = ?, WHERE A = ? AND B = ? AND C = ?, ma non WHERE B = ? da solo.

Regola pratica: ordina le colonne del composto per selettività (quanti valori univoci ha ciascuna) e per la frequenza dei filtri.

-- 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 index

Un index che contiene tutte le colonne lette da una query evita di accedere alla tabella. Usa EXPLAIN e cerca Using index nella colonna 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;

Index con prefisso

Per colonne TEXT o VARCHAR lunghe, indicizza solo i primi N caratteri. Riduce la dimensione dell'index mantenendo una selettività ragionevole.

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

Index invisibili

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

Impatto lettura vs scrittura

Ogni index in più:
- Accelera le query che lo usano.
- Penalizza ogni INSERT, UPDATE che tocca colonne indicizzate e ogni DELETE.
- Occupa spazio aggiuntivo (spesso tra il 10 % e il 40 % della dimensione della tabella).

Nelle tabelle con scrittura massiccia (log, metriche), mantieni il minimo di index indispensabili.

Strategie di ottimizzazione

1. Parti da EXPLAIN — individua type: ALL (full scan) e key: NULL (nessun index usato).
2. Misura prima di ottimizzare — usa lo slow query log per trovare le query più costose.
3. Combina selettività e ordine — l'index composto deve seguire l'ordine delle clausole WHERE e ORDER BY.
4. Evita gli index ridondanti — (A), (A, B), (A, B, C) sono ridondanti tra loro; basta (A, B, C).
5. Non indicizzare colonne a bassa cardinalità — un index su genero o activo (1 o 2 valori univoci) non aiuta quasi mai.

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

Parole chiave: index, indici, B-tree, hash, fulltext, spatial, covering, composto, selettività, EXPLAIN, prefix, invisible, cardinalità

Prima, seconda e terza forma normale, quando denormalizzare e come bilanciare l'integrità dei dati rispetto alle prestazioni.

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

La normalizzazione è il processo di organizzazione di uno schema per ridurre la ridondanza e prevenire anomalie di inserimento, aggiornamento e cancellazione. Le forme normali sono cumulative: una tabella in 3FN soddisfa anche 2FN e 1FN.

Prima Forma Normale (1FN)

- Ogni colonna contiene un unico valore atomico (né liste né JSON annidato che rappresenta più entità).
- Ogni riga è identificabile tramite una chiave primaria.
- Nessun gruppo ripetuto nelle colonne (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: si accende a ogni connessione, prima della prima transazione
PRAGMA foreign_keys = ON;

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

-- SQL Server: chiave surrogata e insieme chiuso con nome
CREATE TABLE clientes_telefonos (
    id          BIGINT IDENTITY(1, 1) PRIMARY KEY,
    cliente_id  BIGINT NOT NULL,
    telefono    VARCHAR(20) NOT NULL,
    tipo        VARCHAR(10) NOT NULL
        CONSTRAINT ck_telefono_tipo CHECK (tipo IN ('movil', 'casa', 'oficina')),
    UNIQUE (cliente_id, telefono),
    FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE
);

-- Cambiare la lista dopo
ALTER TABLE clientes_telefonos DROP CONSTRAINT ck_telefono_tipo;
ALTER TABLE clientes_telefonos ADD CONSTRAINT ck_telefono_tipo
    CHECK (tipo IN ('movil', 'casa', 'oficina', 'fax'));

Seconda Forma Normale (2FN)

- Soddisfa la 1FN.
- Ogni colonna non chiave dipende dalla chiave primaria completa, non da una sua parte. Si applica alle chiavi composte.

Esempio: una tabella detalle_pedido (pedido_id, producto_id, cantidad, nombre_producto) viola la 2FN, perché nombre_producto dipende solo da producto_id, non dalla coppia completa.

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

Terza Forma Normale (3FN)

- Soddisfa la 2FN.
- Nessuna colonna non chiave dipende da un'altra colonna non chiave (nessuna dipendenza transitiva).

Esempio classico: empleados (id, nombre, departamento_id, departamento_nombre). Il nome del reparto dipende da departamento_id, non direttamente dalla chiave del dipendente.

-- 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 e forme superiori

La forma normale di Boyce-Codd (BCNF) irrigidisce la 3FN, mentre la 4FN/5FN trattano le dipendenze multivalore e di join. Per la maggior parte degli schemi transazionali, arrivare in modo pulito alla 3FN è sufficiente.

Quando denormalizzare

La denormalizzazione deliberata infrange le regole per guadagnare prestazioni. È valida se:

1. Letture massicce, scritture scarse — un campo memorizzato nella tabella (pedidos.total_pagado) evita un SUM(...) ricorrente.
2. Reporting / analitica — gli schemi di tipo star o snowflake denormalizzano di proposito.
3. Risultati precalcolati — viste materializzate o tabelle di riepilogo.

Compromessi che accetti:

- Anomalie di aggiornamento — se il dato denormalizzato cambia, va aggiornato in N righe.
- Incoerenza transitoria — il campo memorizzato può disallinearsi se l'aggiornamento fallisce a metà.
- Trigger o logica applicativa — devi mantenere il dato sincronizzato.

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

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

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

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

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

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

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

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

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

Raccomandazione

1. Progetta in 3FN come impostazione predefinita. L'integrità ringrazia.
2. Denormalizza solo con i dati alla mano — misura la query lenta, prova una cache e confronta.
3. Documenta la denormalizzazione. Senza un commento nel DDL, il prossimo DBA la "normalizzerà" pensando che sia un errore.

Parole chiave: normalizzazione, 1FN, 2FN, 3FN, BCNF, denormalizzazione, ridondanza, dipendenza funzionale, chiavi, integrità, trigger

INNER, LEFT, RIGHT, CROSS, SELF e FULL OUTER JOIN: quando usare ciascuno, con esempi su uno schema tipico di ordini.

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

Un JOIN combina righe di due o più tabelle in base a una condizione. Il tipo di join determina cosa succede alle righe che non trovano una corrispondenza.

Gli esempi usano queste tabelle:

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

Restituisce solo le righe con corrispondenza in entrambe le tabelle. È il join predefinito e il più usato.

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

LEFT JOIN (LEFT OUTER JOIN)

Tutte le righe della tabella di sinistra + le corrispondenze di quella di destra. I campi senza corrispondenza a destra compaiono come NULL. Utile per "tutti gli X, con la loro Y se esiste".

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

Clienti senza ordini — pattern classico 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. Quasi sempre si scrive come LEFT JOIN invertendo l'ordine, più leggibile.

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

Prodotto cartesiano: ogni riga di A con ogni riga di B. Senza clausola ON. Utile per generare tutte le combinazioni (calendari × prodotti per i report).

-- 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 stessa tabella compare due volte con alias diversi. Utile per gerarchie o confronti tra righe della stessa tabella.

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

Tutte le righe di entrambe le tabelle; quelle senza corrispondenza mostrano NULL sul lato mancante.

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

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

UNION

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

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

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

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

STRAIGHT_JOIN

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

-- SQLite: l'ordine scritto comanda
SELECT c.nombre, p.id
FROM pedidos p
CROSS JOIN clientes c ON p.cliente_id = c.id;

-- SQL Server: comanda l'ordine scritto
SELECT c.nombre, p.id
FROM pedidos p
JOIN clientes c ON p.cliente_id = c.id
OPTION (FORCE ORDER);

-- L'ordine più caro di ogni cliente
SELECT c.nombre, x.total
FROM clientes c
CROSS APPLY (
    SELECT TOP (1) p.total
    FROM pedidos p
    WHERE p.cliente_id = c.id
    ORDER BY p.total DESC
) x;

Anti-join e semi-join

Pattern logici, non parole chiave SQL:

- Semi-join (esiste almeno una corrispondenza) → EXISTS o IN.
- Anti-join (non esiste corrispondenza) → 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 non è un anti-join. Se la sottoquery restituisce anche un solo NULL, il confronto non è mai vero e il risultato sono zero righe. Misurato con 5 clienti e 6 ordini, 3 dei quali con cliente_id a NULL: NOT EXISTS e LEFT JOIN … IS NULL restituiscono 2, e NOT IN restituisce 0. Succede uguale su PostgreSQL 17.6, su MySQL 8.0.46, su SQLite 3.51 e su SQL Server 2025.

Prestazioni

1. Le colonne dell'ON devono essere indicizzate, specialmente sul lato "join interno" (quello che viene cercato per ogni riga di quello esterno).
2. Filtra il più possibile prima del join (WHERE su ogni tabella quando applicabile).
3. Evita i JOIN su espressioni (ON LOWER(a.cod) = LOWER(b.cod)) — l'index non viene usato.
4. EXPLAIN rivela l'ordine di lettura e il metodo, con il vocabolario di ciascun motore.

-- Il piano a SQLite si chiede così, non con EXPLAIN da solo
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);

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

Variabili critiche per le prestazioni: buffer pool, connessioni, pacchetto massimo, cache delle query e differenze chiave tra MySQL e MariaDB.

Si applica a: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

La configurazione predefinita del server non è quasi mai quella ottimale in produzione. Queste sono le variabili con il maggiore impatto su prestazioni e stabilità.

innodb_buffer_pool_size

La cache principale di InnoDB: tabelle, index e dati. È la variabile più importante.

- Regola pratica: 60 %–80 % della RAM su un server dedicato a MySQL.
- Minimo consigliato: 1 GiB in produzione.
- In MySQL 8.0+ e MariaDB 10.5+ si può cambiare a caldo (senza riavviare).

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

Numero massimo di connessioni simultanee. Per impostazione predefinita 151 in MySQL, 100 in MariaDB.

- Ogni connessione consuma memoria (thread_stack + buffer per sessione, ~256 KiB).
- Un valore troppo alto peggiora le prestazioni sotto carico (contesa).
- Misura con SHOW STATUS LIKE 'Max_used_connections'. Se raggiunge il tetto, aumenta gradualmente.

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

max_allowed_packet

Dimensione massima di un pacchetto di protocollo (un INSERT grande, un LOAD DATA, un BLOB).

- Per impostazione predefinita 64 MiB in MySQL 8.0, 16 MiB nelle versioni precedenti.
- Se un'operazione lo supera: errore Got a packet bigger than 'max_allowed_packet' bytes.
- Aumentarlo a 256 MiB o 1 GiB è abituale nei carichi con BLOB.

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

Memorizza nella cache il risultato completo delle query SELECT.

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

Log e durabilità

innodb_flush_log_at_trx_commit controlla quando viene scaricato il redo log:

- 1 (predefinito) — scarica ed esegue fsync a ogni commit. Massima durabilità, minima velocità. ACID rigoroso.
- 2 — scarica a ogni commit, fsync ogni secondo. Un crash del SO può far perdere circa 1s. Quasi ACID.
- 0 — scarica ed esegue fsync ogni secondo. Un crash di MySQL può far perdere circa 1s. Non ACID.

Nelle repliche o negli ambienti dove tolleri una perdita limitata, 2 può moltiplicare il throughput per 3–5. Non cambiarlo sul primario senza aver compreso il rischio.

thread_pool

tmp_table_size / max_heap_table_size

Dimensione massima delle tabelle temporanee in memoria. Se un'operazione supera il limite, MySQL la sposta su disco e perde velocità. Mantieni entrambi i valori uguali.

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 che InnoDB può consumare per la pulizia delle pagine sporche e le purge. Per impostazione predefinita 200 / 2000.

- SSD moderni: 2000 / 4000 o più.
- HDD: lascia i valori predefiniti.

Configurazione 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

In MariaDB, la persistenza si ottiene modificando my.cnf (/etc/my.cnf.d/) e riavviando, oppure usando gli include (!include).

Raccomandazione generale

1. Conosci il workload prima di toccare qualsiasi cosa. Un OLTP con scritture intense si configura in modo diverso da un data warehouse di letture.
2. Cambia una variabile alla volta e misura l'impatto.
3. Documenta ogni modifica in my.cnf con un commento che ne spieghi il motivo.
4. Non copiare le configurazioni dai blog senza comprenderle — i valori "ottimali" dipendono molto dall'hardware e dal carico.

Parole chiave: configurazione, 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

Dimensioni massime di database, tabelle, colonne, lunghezze dei nomi e caratteri consentiti negli identificatori.

Si applica a: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Conoscere i limiti del motore evita sorprese durante la crescita. Questi sono i tetti pratici in MySQL e MariaDB moderni.

Per database

- Dimensione totale: limitata dal filesystem. Con innodb_file_per_table = ON (default), ogni tabella è un file .ibd. Su ext4 / XFS il limite teorico si misura in exabyte — il limite reale lo pone il tuo storage.
- Tabelle per database: praticamente illimitate. Il catalogo (information_schema, mysql.tables) gestisce diverse centinaia di migliaia senza problemi. I carichi con oltre 10 000 tabelle richiedono di regolare table_open_cache.

Per tabella

- Righe: 2⁶⁴ righe teoriche. In pratica: centinaia di miliardi se lo schema e gli index sono buoni.
- Dimensione massima della tabella: 64 TiB con la INNODB_PAGE_SIZE predefinita (16 KiB).
- Colonne: massimo 4 096 per tabella, ma il limite reale è dettato dal row size, non dal numero.
- Dimensione massima della riga: 65 535 byte (esclusi BLOB/TEXT che vengono memorizzati fuori dalla riga).
- Index per tabella: 64.
- Colonne per index: 16 (B-tree InnoDB).
- Lunghezza massima della chiave di un index: 3072 byte con DYNAMIC/COMPRESSED (formato predefinito in 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;

Per colonna

Identificatori (nomi degli oggetti)

- Database, tabelle, colonne, index, viste: 64 caratteri.
- Alias di colonne: 256 caratteri.
- Funzioni, procedure, trigger, eventi: 64 caratteri.
- Constraint (FK, CHECK, UNIQUE): 64 caratteri.

Caratteri consentiti negli identificatori

- Senza backtick: lettere ASCII, cifre, _ e $. Non possono iniziare con una cifra pura né essere composti solo da cifre.
- Con backtick (`nombre raro`): qualsiasi carattere Unicode tranne U+0000 (NUL).

Convenzione consigliata: snake_case ASCII (pedido_cliente_id). Evita spazi, accenti e maiuscole — alcuni sistemi li normalizzano in modo diverso tra Linux e 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);

Sensibilità a maiuscole/minuscole

lower_case_table_names:

- 0 — i nomi vengono memorizzati così come sono stati creati e sono sensibili alle maiuscole. Default su Linux.
- 1 — i nomi vengono salvati in minuscolo e i confronti ignorano il case. Default su macOS e Windows.
- 2 — vengono memorizzati così come sono ma i confronti ignorano il case. Solo macOS/Windows.

Cambiare questo valore in un'installazione esistente è distruttivo. Decidi al momento dell'inizializzazione del server.

Per query

- Sottoquery annidate: fino a 64 livelli.
- UNION: teoricamente illimitato, ma l'optimizer degrada oltre alcune centinaia.
- Parametri in un prepared statement: 65 535.
- Righe in un IN(...): in pratica fino a qualche migliaio; oltre, meglio un JOIN con tabella temporanea.

Per sessione

- Variabili di sessione (@@SESSION.xxx): possono impostare quasi qualsiasi variabile globale runtime.
- Variabili utente (@variable): fino a 64 caratteri nel nome.

Charset e collation

- Charset consigliato: utf8mb4 (UTF-8 completo, 4 byte). L'alias utf8 è storico e limitato a 3 byte (senza emoji).
- Collation consigliata in MySQL 8.0+: utf8mb4_0900_ai_ci (case-insensitive, accent-insensitive, basata su Unicode 9).
- In 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;

Raccomandazioni

1. Progetta con margine: se prevedi 10 milioni di righe, dimensiona index e partizioni per 100 M.
2. Usa BIGINT UNSIGNED nelle chiavi primarie delle tabelle che possono crescere. INT si riempie a circa 2 miliardi.
3. Definisci charset e collation espliciti quando crei database, tabelle e colonne. Ereditare dal default può fallire durante la migrazione.
4. Documenta i limiti del tuo modello (righe attese/anno, dimensione massima per colonna). Serve per il capacity planning e per rilevare query anomale.

Parole chiave: limiti, massimo, dimensione, colonne, righe, identificatori, charset, collation, utf8mb4, lower_case_table_names, index, row size

Progettazione degli schemi, convenzioni sui nomi, backup, replica, sicurezza degli utenti, GRANT minimi e audit.

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

Raccomandazioni operative che distinguono un database amatoriale da uno mantenibile in produzione.

Progettazione degli schemi

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;

-- Lo schema prima, in un batch a sé: CREATE SCHEMA ventas;
CREATE TABLE ventas.pedidos (
    id             BIGINT IDENTITY(1, 1) CONSTRAINT pk_pedidos PRIMARY KEY,
    cliente_id     BIGINT       NOT NULL,
    estado         TINYINT      NOT NULL CONSTRAINT df_pedidos_estado DEFAULT 0,
    creado_en      DATETIME2(3) NOT NULL CONSTRAINT df_pedidos_creado_en DEFAULT SYSUTCDATETIME(),
    actualizado_en DATETIME2(3) NOT NULL CONSTRAINT df_pedidos_actualizado_en DEFAULT SYSUTCDATETIME(),
    CONSTRAINT fk_pedidos_clientes FOREIGN KEY (cliente_id)
        REFERENCES ventas.clientes (id) ON UPDATE CASCADE,
    CONSTRAINT ck_pedidos_estado CHECK (estado BETWEEN 0 AND 4)
);
CREATE INDEX idx_pedidos_cliente_id ON ventas.pedidos (cliente_id);
CREATE INDEX idx_pedidos_creado_en  ON ventas.pedidos (creado_en);

Convenzioni sui nomi

- Tabelle: snake_case, plurale se rappresentano collezioni (pedidos, clientes).
- Colonne: snake_case, senza prefisso ridondante (nombre, non cliente_nombre dentro clientes).
- Chiavi esterne: <tabla>_id (cliente_id).
- Indici: idx_<tabla>_<columnas> o uq_<tabla>_<columnas> per gli univoci.
- Foreign key esplicite: fk_<tabla>_<tabla_destino>.
- Procedure / funzioni: prefisso sp_ / fn_ opzionale, verbo all'infinito (fn_calcular_descuento).

Coerenza > preferenza personale. Concorda la convenzione nel tuo team e applicala universalmente.

Backup

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

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

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

Replica

Utenti e permessi

Principio del privilegio minimo: ogni connessione usa l'utente più restrittivo possibile.

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

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

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

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

-- MAI in produzione
-- ALTER SERVER ROLE sysadmin ADD MEMBER app_pedidos;
-- ALTER ROLE db_owner ADD MEMBER app_pedidos;

Audit

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

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

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

Calíope conserva un log locale delle query eseguite (Registro SQL) per ogni sessione di connessione, indipendente dal log del server.

Lista minima per la produzione

Parole chiave: buone pratiche, progettazione, convenzioni, backup, replica, GTID, binlog, GRANT, audit, sicurezza, InnoDB, foreign key, TLS, SQL Server, IDENTITY, SEQUENCE, schema, login, XACT_ABORT, SQL Server Audit

Analisi delle query con EXPLAIN, slow query log, individuazione dei colli di bottiglia, cache di InnoDB e uso di performance_schema.

Si applica a: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

L'ottimizzazione inizia con il misurare. Senza dati, ottimizzare è tirare a indovinare. Questi sono gli strumenti di base.

EXPLAIN

Mostra il piano che l'optimizer ha scelto per una query. Non la esegue — è sicuro lanciarlo in produzione.

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;

Colonne chiave:

- type — metodo di accesso. Dal migliore al peggiore: system → const → eq_ref → ref → range → index → ALL. ALL = full table scan = pessimo su tabelle grandi.
- key — index scelto. NULL = non usa index.
- rows — stima delle righe esaminate. Se è molto maggiore delle righe restituite, c'è margine di miglioramento.
- Extra — indizi utili:
- Using index — covering index (ottimo).
- Using where — filtro applicato dopo la lettura delle righe.
- Using temporary — necessita di una tabella temporanea (costoso).
- Using filesort — ordinamento fuori index (costoso su tabelle grandi).

EXPLAIN ANALYZE (MySQL 8.0+ / MariaDB 10.1+)

Esegue la query e mostra i tempi reali per nodo. Più costoso di EXPLAIN, ma molto più 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 dispone di un Visual Explain integrato che renderizza l'albero del piano: Workspace › Analisi › Visual Explain.

Slow query log

Registra tutte le query che impiegano più di long_query_time secondi.

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

Analisi del log:

- mysqldumpslow — strumento classico incluso con MySQL.
- pt-query-digest (Percona Toolkit) — lo standard de facto, raggruppa per fingerprint e mostra statistiche.

performance_schema

Schema di tabelle con statistiche dettagliate del server.

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

Cache di InnoDB

- Buffer pool — dati e index. Metrica chiave: hit ratio (Innodb_buffer_pool_read_requests / (reads + reads_from_disk)). Obiettivo: >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 automatico sulle pagine calde del buffer pool. Attivato per impostazione predefinita.
- Change buffer — bufferizza le modifiche a pagine non presenti nel buffer pool.

Colli di bottiglia frequenti

Ottimizzazioni delle query — pattern comuni

1. Seleziona solo ciò che ti serve. Evita SELECT * nelle applicazioni.
2. Evita le funzioni sulle colonne indicizzate:
- Male: WHERE YEAR(fecha) = 2024 → non usa index.
- Bene: WHERE fecha >= '2024-01-01' AND fecha < '2025-01-01'.
3. LIMIT con offset grande è costoso — per la paginazione profonda, usa la keyset pagination: WHERE id > :last_seen ORDER BY id LIMIT 50.
4. COUNT(*) su tabelle grandi — InnoDB non mantiene un contatore. Valuta colonne di riepilogo o stime (information_schema.tables.table_rows).
5. Le subquery non correlate vengono eseguite una volta; le correlate, una volta per ogni riga esterna. Riscrivile come JOIN se possibile.

Raccomandazione

Crea un dashboard di monitoraggio di base (Calíope ne ha uno: Dashboard) con:

- Connessioni (Threads_connected, Threads_running).
- Buffer pool hit ratio.
- Query lente al minuto.
- Replication lag.
- Spazio su disco per tablespace.

Prima rilevi una degradazione, più facile è correggerla.

Parole chiave: performance, ottimizzazione, EXPLAIN, EXPLAIN ANALYZE, slow query log, performance_schema, buffer pool, filesort, covering index, filter, collo di bottiglia, pt-query-digest

ACID, COMMIT e ROLLBACK, i quattro livelli di isolamento e quale anomalia consente ciascuno.

Si applica a: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Una transazione raggruppa più istruzioni in un'unità che si applica per intero o non si applica affatto. In InnoDB ogni istruzione gira dentro una transazione: se non ne apri una, il server ne apre e conferma una per istruzione (autocommit = 1).

ACID
- Atomicità — o si applicano tutte le modifiche, o nessuna.
- Coerenza — il database passa da uno stato valido a un altro; i vincoli restano rispettati.
- Isolamento — le transazioni concorrenti non si vedono mai a metà.
- Durabilità — ciò che è confermato sopravvive a un crash del server.

Controllo manuale
COMMIT conferma e ROLLBACK annulla tutto ciò che è stato fatto da START TRANSACTION.

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

Punti di salvataggio
Un SAVEPOINT annulla solo una parte senza perdere il resto della transazione:

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;

I quattro livelli

Il livello predefinito di InnoDB è REPEATABLE READ. Grazie a MVCC e ai gap lock, InnoDB evita a quel livello anche le letture fantasma, cosa che lo standard SQL non richiede.

Cambiare livello

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT @@transaction_isolation;

Attenzione al DDL
CREATE, ALTER, DROP e TRUNCATE provocano un commit implicito: non si annullano con ROLLBACK. Una migrazione a metà lascia la tabella com'è rimasta.

Transazioni lunghe
Una transazione aperta obbliga InnoDB a conservare le vecchie versioni di ogni riga per le letture coerenti. Uno START TRANSACTION dimenticato fa crescere l'undo log e degrada l'intero server. Trovale così:

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

Raccomandazione
Transazioni brevi, con la logica applicativa fuori e solo l'SQL dentro. READ COMMITTED riduce i lock ed è ciò che usano molte applicazioni web; resta su REPEATABLE READ se ti serve che due letture nella stessa transazione restituiscano la stessa cosa.

Parole chiave: transazione, commit, rollback, savepoint, isolamento, acid, mvcc, read committed, repeatable read, serializable, autocommit, innodb_trx

Cosa blocca InnoDB, perché nasce un deadlock e come diagnosticarlo senza tirare a indovinare.

Si applica a: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

InnoDB blocca righe, non tabelle, e lo fa automaticamente in scrittura. Quasi ogni problema di concorrenza si spiega con quali righe una query ha finito per bloccare, e questo dipende dall'indice che ha usato.

Tipi di lock
- Condiviso (S) — più transazioni possono leggere la stessa riga insieme.
- Esclusivo (X) — lo prende chi scrive; nessun altro può leggerla con lock né scriverla.
- Gap — blocca lo spazio tra due valori dell'indice per impedire inserimenti. Solo in REPEATABLE READ e SERIALIZABLE.
- Next-key — la riga più il gap che la precede. È la modalità normale di InnoDB quando percorre un indice.
- Di intenzione (IS/IX) — segnala a livello di tabella che dentro ci sono lock di riga; impedisce a un LOCK TABLES di infilarsi.

La conseguenza pratica: se la query non usa un indice, InnoDB percorre l'intera tabella e blocca ogni riga esaminata, non solo quelle che corrispondono. Un buon indice non accelera soltanto: riduce ciò che viene bloccato.

Letture bloccanti
Un SELECT normale non blocca nulla (legge una versione coerente tramite MVCC). Se devi leggere e poi scrivere senza che nessuno si infili in mezzo, chiedi il lock esplicitamente 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;

Cos'è un deadlock
Due transazioni che aspettano ciascuna un lock tenuto dall'altra. Nessuna può proseguire:

-- 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 rileva da solo e uccide la transazione più economica da annullare, che riceve l'errore 1213 Deadlock found when trying to get lock. Non è un guasto del server né una corruzione: è il comportamento corretto, e l'applicazione deve riprovare quella transazione.

Diverso è l'errore 1205 Lock wait timeout exceeded: lì non c'è ciclo, solo un'attesa che ha superato innodb_lock_wait_timeout (50 s per impostazione predefinita).

Diagnosi
La sezione LATEST DETECTED DEADLOCK di SHOW ENGINE INNODB STATUS conserva l'ultimo deadlock con entrambe le transazioni e le istruzioni coinvolte. Per vedere i lock in questo momento c'è performance_schema:

SHOW ENGINE INNODB STATUS;

SELECT * FROM performance_schema.data_locks;

SELECT * FROM performance_schema.data_lock_waits;

SELECT @@innodb_lock_wait_timeout;

Come evitarli
1. Accedere sempre nello stesso ordine — se tutto il codice tocca tabelle e righe nello stesso ordine, nessun ciclo è possibile.
2. Transazioni brevi — meno tempo con i lock presi, meno occasioni di scontro.
3. Indicizzare ciò che si filtra — evita di bloccare righe che nemmeno corrispondevano.
4. Riprovare — un deadlock occasionale è normale in un sistema concorrente; avvolgi la transazione in un ritentativo con attesa crescente.
5. Evitare SELECT ... FOR UPDATE inutili — se non devi scrivere, non chiederlo.

Raccomandazione
Davanti ai lock guarda prima il piano della query: la maggior parte dei deadlock reali sparisce appena si aggiunge l'indice mancante. L'Elenco processi di Calíope ti mostra quale sessione sta aspettando.

Parole chiave: lock, deadlock, gap lock, next-key, for update, for share, errore 1213, innodb status, data_locks, lock wait timeout

RANGE, LIST, HASH e KEY, pruning delle partizioni e pulizia istantanea con DROP PARTITION.

Si applica a: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Partizionare divide una tabella in più pezzi fisici che il server continua a vedere come una sola. Non rende magicamente veloci le query: quello che dà è il pruning delle partizioni e soprattutto la possibilità di cancellare milioni di righe in un istante.

Quando conviene
Il caso netto è una tabella che cresce per data e da cui si ripulisce il vecchio: log, eventi, metriche, audit. Lì DROP PARTITION sostituisce un DELETE da ore.

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

I quattro tipi
- RANGE — per intervalli di un valore ordinabile, quasi sempre una data. Il più utile.
- LIST — per appartenenza a un insieme discreto di valori.
- HASH — distribuzione uniforme su un'espressione intera; serve a distribuire le scritture, non a fare pruning.
- KEY — come HASH ma con la funzione interna del server; accetta colonne non intere.

Le varianti RANGE COLUMNS e LIST COLUMNS accettano più colonne e tipi non interi senza avvolgerli in una funzione:

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

Pruning delle partizioni
Il vantaggio vero: se il WHERE filtra sulla colonna di partizionamento, il server legge solo le partizioni che possono contenere risultati. Verificalo nella colonna partitions di EXPLAIN — se compaiono tutte, non stai facendo pruning e il partizionamento ti sta solo 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';

Limiti da conoscere prima
1. La chiave di partizionamento deve far parte di ogni chiave univoca, primaria inclusa. Per questo l'esempio porta PRIMARY KEY (id, ocurrido) e non solo id.
2. Niente chiavi esterne: una tabella partizionata non può avere né ricevere una FOREIGN KEY.
3. Al massimo 8192 partizioni per tabella, e ognuna consuma descrittori di file.
4. Le query che non filtrano sulla chiave toccano tutte le partizioni e risultano più lente che senza partizionamento.
5. Gli indici sono locali a ogni partizione: l'indice globale non esiste.

Manutenzione
Aggiungere la partizione del periodo successivo e lasciar andare la più vecchia è la routine normale. DROP PARTITION è praticamente istantaneo e libera davvero lo spazio, cosa che un DELETE massivo non fa:

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;

Raccomandazione
Partiziona per data solo se hai intenzione di ripulire per data, e crea le partizioni future in anticipo (o con un evento pianificato): se arriva una riga che non rientra in alcun intervallo, l'INSERT fallisce. Lascia sempre una pmax di rete.

Parole chiave: partizionamento, partition, range, list, hash, key, pruning, drop partition, reorganize, information_schema.partitions, pulizia

WITH, WITH RECURSIVE e OVER (): l'SQL moderno che evita sottoquery annidate e tabelle temporanee.

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

Le CTE (WITH) e le funzioni finestra (OVER ()) sono arrivate in MySQL 8.0 e MariaDB 10.2; su PostgreSQL non c'è versione ancora supportata che non le abbia, e su SQLite entrambe stanno ben sotto la soglia di questo manuale: le CTE dalla 3.8.3 e le finestre dalla 3.25. Risolvono in una query leggibile ciò che prima richiedeva sottoquery annidate, tabelle temporanee o variabili di sessione.

CTE: dare un nome a un passaggio intermedio
Una CTE è un risultato con nome che vive solo per la durata della query. Serve a spezzare una query lunga in passaggi e a riferirsi due volte allo stesso sotto-risultato senza ripeterlo:

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

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

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

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

CTE ricorsiva: gerarchie
WITH RECURSIVE percorre strutture ad albero — organigrammi, categorie annidate, distinte base — senza cicli nell'applicazione. Il primo ramo è il caso base e il secondo si ripete finché non restituisce righe:

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;

Funzioni finestra: calcolare senza raggruppare
Un GROUP BY collassa le righe; una funzione finestra calcola su un insieme di righe correlate e conserva ogni riga. È ciò che rende possibile un cumulato, una media mobile o una posizione dentro il gruppo in una sola passata:

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;

Le più usate
- ROW_NUMBER() — numero progressivo nella partizione, senza pari merito.
- RANK() / DENSE_RANK() — posizione con pari merito; RANK lascia buchi, DENSE_RANK no.
- LAG() / LEAD() — il valore della riga precedente o successiva, senza self-join.
- FIRST_VALUE() / LAST_VALUE() — gli estremi della finestra.
- NTILE(n) — divide le righe in n secchi, per quartili e percentili.
- Aggregati con OVER — SUM, AVG, COUNT, MIN, MAX senza collassare le righe.

Il frame (ROWS BETWEEN ...) definisce quali righe entrano nel calcolo di ciascuna. Per impostazione predefinita un aggregato con ORDER BY va dall'inizio della partizione alla riga corrente, che è esattamente ciò che dà il cumulato.

Il pattern che rende di più: i primi N per gruppo
Tirare fuori i tre prodotti più venduti di ogni categoria senza finestre richiede una sottoquery correlata per riga. Con ROW_NUMBER() è diretto:

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;

Prestazioni
Nessuna delle due è gratis: la finestra deve ordinare dentro ogni partizione, quindi un indice che consegni già le righe nell'ordine di PARTITION BY più ORDER BY risparmia quell'ordinamento. Verificalo con EXPLAIN prima di dare per buona la versione elegante.

Raccomandazione
Usa le CTE perché la query si capisca e le finestre per non fare nell'applicazione ciò che il server fa in una passata. Se il tuo server è MySQL 5.7 o MariaDB 10.1, nessuna delle due è disponibile: lì comandano ancora le sottoquery.

Parole chiave: cte, with, with recursive, funzione finestra, over, partition by, row_number, rank, dense_rank, lag, lead, ntile, frame, gerarchia, top n per gruppo

Perché un errore blocca l'intera transazione, come se ne esce con un punto di salvataggio, cosa fa davvero ogni livello, e il DDL che si annulla.

Si applica a: PostgreSQL 13+

Una transazione raggruppa più istruzioni in un'unità che si applica per intero o non si applica. Senza un BEGIN esplicito, PostgreSQL conferma ogni istruzione per conto suo.

La prima sorpresa arrivando da MySQL
Un errore annulla l'intera transazione. Da lì in poi ogni istruzione risponde la stessa cosa — current transaction is aborted, commands ignored until end of transaction block, SQLSTATE 25P02 — finché non fai ROLLBACK. Non è un difetto dell'applicazione: è il progetto, ed evita che una transazione prosegua su uno stato che non è più quello che credevi.

L'uscita è un punto di salvataggio
SAVEPOINT segna un punto a cui tornare, e ROLLBACK TO SAVEPOINT salva la transazione senza perdere quanto fatto prima:

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

Il DDL si annulla davvero
CREATE, ALTER e DROP stanno dentro la transazione: qui non c'è commit implicito. Una migrazione che fallisce a metà non lascia mezza tabella.

BEGIN;
ALTER TABLE cuentas ADD COLUMN moneda text;
ROLLBACK;   -- la colonna non è mai esistita

I quattro livelli

READ UNCOMMITTED viene accettato e segnalato come tale, ma si comporta come READ COMMITTED: in PostgreSQL non ci sono letture sporche a nessun livello. Il predefinito è READ COMMITTED.

REPEATABLE READ e SERIALIZABLE non bloccano: annullano
Invece di aspettare, la transazione che non si può serializzare termina con SQLSTATE 40001 (could not serialize access…). Vuol dire che l'applicazione deve riprovare: a questi due livelli un 40001 è funzionamento normale, non un guasto. SERIALIZABLE usa SSI, che rileva le dipendenze di lettura e scrittura e non prende blocchi in più.

Blocchi e deadlock
Un deadlock viene rilevato dopo deadlock_timeout — 1 s per impostazione predefinita — e il server termina una delle due con SQLSTATE 40P01. Per non aspettare, o per distribuire il lavoro tra consumatori:

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

FOR UPDATE NOWAIT fallisce subito con 55P03 invece di aspettare. Attenzione: quel fallimento annulla anch'esso la transazione.

Transazioni lunghe
Qui una transazione aperta non fa ingrossare un undo log: impedisce a VACUUM di ripulire le versioni morte su tutto il server, e la tabella cresce senza righe nuove. Trovale così:

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 per impostazione predefinita, cioè senza limite; dargli un valore è la rete che evita che una sessione dimenticata degradi l'intero database.

Consiglio
Transazioni brevi, con la logica applicativa fuori. Se sali a REPEATABLE READ o a SERIALIZABLE, scrivi il ritentativo prima di salire, non dopo il primo 40001 in produzione.

Parole chiave: transazione, commit, rollback, savepoint, isolamento, mvcc, read committed, repeatable read, serializable, ssi, 25P02, 40001, 40P01, deadlock, skip locked, pg_stat_activity

Perché una tabella cresce senza righe nuove, cosa pulisce davvero ogni operazione, quando i contatori mentono e cos'è il wraparound.

Si applica a: PostgreSQL 13+

In PostgreSQL aggiornare una riga non la modifica: scrive una versione nuova e lascia morta quella vecchia. Nemmeno cancellare libera qualcosa subito. È così che funziona MVCC qui, e VACUUM è ciò che raccoglie dopo. Non ha equivalente in InnoDB, ed è dietro quasi tutte le sorprese di dimensione.

Come si vede
Una tabella di 50 000 righe occupava 12 MB. Un solo UPDATE su tutte le righe l'ha lasciata a 23 MB senza aggiungere una sola riga: le 50 000 versioni vecchie sono ancora nel file. Misurato su PostgreSQL 17.6.

Cosa fa ogni cosa
- VACUUM segna lo spazio morto come riutilizzabile. Non restituisce lo spazio al sistema operativo: dopo il vacuum la tabella dell'esempio occupava ancora 23 MB, solo che le scritture successive ora ci stanno dentro.
- VACUUM FULL riscrive l'intera tabella e lo spazio lo restituisce davvero — è scesa a 11 MB — ma prende un blocco ACCESS EXCLUSIVE: nessuno legge né scrive mentre dura, e serve spazio per una copia completa. Non è la manutenzione di routine, è l'ultima risorsa.
- ANALYZE non pulisce nulla: aggiorna le statistiche del planner.

Autovacuum, che è già acceso
autovacuum arriva on. Una tabella entra in coda quando le righe morte superano autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × righe, cioè 50 + 20 % con i valori predefiniti. Su una tabella da dieci milioni di righe sono due milioni di righe morte prima che si muova qualcosa: sulle tabelle grandi e molto aggiornate si abbassa il fattore per tabella:

ALTER TABLE pedidos SET (autovacuum_vacuum_scale_factor = 0.02);

Controllare che funzioni

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;

Attenzione a quel contatore: è una stima e non è istantaneo. Misurato su 17.6, subito dopo aver aggiornato 50 000 righe diceva ancora 0; solo dopo un ANALYZE è passato a 50 000, e dopo il VACUUM è tornato a 0. Se hai appena scritto molto e il numero non si muove, non vuol dire che non ci sia lavoro in attesa.

Un vacuum in corso si segue così:

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

Il nemico: la transazione aperta
VACUUM può pulire solo ciò che nessuno può più vedere. Una transazione aperta — o una replica con hot_standby_feedback — congela quell'orizzonte, e allora il vacuum gira, dice di aver finito e non libera nulla. Per questo una sessione dimenticata in idle in transaction fa crescere tabelle che nemmeno tocca.

Il wraparound, che sì è un'emergenza
Gli identificatori di transazione sono a 32 bit e vengono riciclati. Perché nessuna riga finisca nel futuro, il vacuum congela quelle vecchie. autovacuum_freeze_max_age vale 200 000 000 per impostazione predefinita: superata quell'età il server lancia un autovacuum che non si può rimandare, e se comunque si esaurisce smette di accettare scritture. Si sorveglia così:

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

Finché quell'età resta molto sotto i duecento milioni non c'è nulla da fare.

Consiglio
Non spegnere autovacuum. Se una tabella cresce senza righe nuove, l'ordine del sospetto è: una transazione aperta, uno scale_factor troppo alto per la sua dimensione, e solo alla fine VACUUM FULL — con finestra di manutenzione, perché blocca l'intera tabella.

Parole chiave: vacuum, autovacuum, bloat, mvcc, n_dead_tup, vacuum full, congelamento, wraparound, pg_stat_user_tables, pg_stat_progress_vacuum, datfrozenxid, hot_standby_feedback, idle in transaction

Che cosa blocca davvero ogni istruzione, perché un ALTER TABLE può fermare i SELECT, come si vede chi aspetta chi e che fare con un 40P01.

Si applica a: PostgreSQL 13+

In PostgreSQL i lock vivono in due posti diversi, e confonderli è ciò che rende un problema introvabile. Quelli di tabella stanno in pg_locks; quelli di riga stanno dentro la riga stessa, nella sua intestazione, quindi non occupano memoria, non escalano mai a lock di tabella e non compaiono in pg_locks. Un milione di righe bloccate non costa più di una sola.

E una regola non ha eccezioni: un SELECT normale non aspetta mai una riga. Legge la sua versione tramite MVCC. L'unica cosa che può fermare un SELECT è un lock di tabella.

Chi prende quale modo di tabella

I primi tre non si ostacolano fra loro, ed è per questo che il carico normale non si blocca mai. L'ultimo confligge con tutti, SELECT compreso.

La trappola: un ALTER TABLE che aspetta accoda tutto dietro di sé
Quell'ACCESS EXCLUSIVE non salta la fila: ci si mette. E mentre aspetta, tutto ciò che arriva dopo aspetta dietro di lui, anche un SELECT che con l'istruzione davanti non avrebbe avuto alcun problema. Basta una transazione aperta che abbia fatto solo un SELECT perché un ALTER TABLE fermi la tabella per tutti senza aver iniziato a lavorare. Per questo il DDL in produzione si lancia con un tetto e si riprova:

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

Quattro modi di riga, non due
Dal più forte al più debole: FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE. Solo tre coppie convivono —i due condivisi fra loro e FOR NO KEY UPDATE con FOR KEY SHARE—; FOR UPDATE confligge con tutti e quattro. Proprio quella coppia strana è quella che conta: un UPDATE che non tocca la chiave prende FOR NO KEY UPDATE, quindi non blocca la verifica di una chiave esterna che punta a quella riga, che è quella che chiede FOR KEY SHARE.

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

Qui si aspetta per sempre
lock_timeout vale 0 per impostazione predefinita, cioè senza limite: non c'è un equivalente dell'innodb_lock_wait_timeout di MySQL, che taglia a 50 s. Impostarlo —per sessione, prima di un'istruzione rischiosa o nella configurazione— è ciò che trasforma un'attesa infinita in un errore che l'applicazione può riprovare. Quando scatta: SQLSTATE 55P03.

Non aspettare, di proposito

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

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

NOWAIT fallisce all'istante con 55P03 —e quel fallimento, come qualsiasi altro, annulla l'intera transazione—. SKIP LOCKED non fallisce: restituisce meno righe. Con la riga 3 bloccata da un'altra sessione, la seconda query ha restituito 1, 2, 4 e 5. È il modo di distribuire una coda di lavoro fra più consumatori senza che si pestino i piedi né si aspettino.

L'abbraccio mortale

-- Sessione A
BEGIN;
UPDATE cuentas SET saldo = saldo - 10 WHERE id = 1;
UPDATE cuentas SET saldo = saldo + 10 WHERE id = 2;

-- Sessione B
BEGIN;
UPDATE cuentas SET saldo = saldo - 10 WHERE id = 2;
UPDATE cuentas SET saldo = saldo + 10 WHERE id = 1;

Il server uccide una delle due con SQLSTATE 40P01 («deadlock detected»), e il dettaglio dice quale processo e quale riga. Ma non lo rileva all'istante: cerca il ciclo solo quando un'attesa supera deadlock_timeout, 1 s per impostazione predefinita, e la vittima impiega quel secondo a morire. InnoDB lo rileva subito; qui un deadlock si paga con un secondo di attesa. Abbassare deadlock_timeout non è gratis: quel lavoro si spende anche sulle attese normali, che sono la maggioranza.

Un 40P01 non è un guasto del server: è il comportamento corretto, e l'applicazione deve riprovare quella transazione.

Diagnosi
Qui non c'è SHOW ENGINE INNODB STATUS. Ci sono due query, ed entrambe vanno eseguite mentre il lock 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 dà l'elenco dei processi che trattengono ciò che l'altro chiede, che è la domanda che ci si pone davvero. Ciò che in pg_locks non vedrai sono i lock di riga: chi aspetta una riga compare in attesa di un transactionid, il numero della transazione che la tiene. E per lasciare traccia di quel che è già successo, log_lock_waits scrive nel registro del server ogni attesa che superi deadlock_timeout.

L'Elenco processi di Calíope mostra quell'attesa nella colonna di stato: una sessione bloccata compare come Lock: transactionid.

Lock consultivi
Nessuna tabella, nessun dato: un numero che il server custodisce per te perché due processi della tua applicazione non facciano la stessa cosa insieme.

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

Finché una sessione tiene il 42, pg_try_advisory_lock(42) da un'altra restituisce false invece di aspettare. Attenzione all'ambito: quelli di sessione sopravvivono al COMMIT e si rilasciano solo rilasciandoli o chiudendo la connessione; pg_advisory_xact_lock si rilascia da solo alla fine della transazione, che è quasi sempre ciò che si vuole.

SERIALIZABLE non blocca
A quel livello compaiono in pg_locks lock SIReadLock. Non bloccano nessuno: sono il segno di ciò che la transazione ha letto, e il conflitto arriva come 40001 al commit, non come un'attesa.

Raccomandazione
Toccare sempre le righe nello stesso ordine e transazioni brevi, come in qualsiasi motore. Ciò che è proprio di qui sono due cose: lock_timeout impostato prima di ogni DDL, perché chi aspetta accoda tutti dietro; e il ritentativo scritto prima di andare in produzione, l'unica cosa che rende innocuo un 40P01.

Parole chiave: lock, blocco, deadlock, 40p01, 55p03, pg_locks, pg_blocking_pids, lock_timeout, deadlock_timeout, access exclusive, for update, for key share, skip locked, nowait, lock consultivo, advisory

I sei metodi di indice e quando serve ciascuno, indici parziali e su espressione, perché un Index Only Scan a volte va comunque alla tabella, e come trovare quelli che non usa nessuno.

Si applica a: PostgreSQL 13+

Un indice accelera le ricerche al prezzo di spazio e di lavoro a ogni scrittura. Ciò che cambia arrivando da MySQL non è quest'idea, ma che qui ci sono sei metodi invece di uno con eccezioni, e che quasi tutto ciò che in MySQL è un'opzione dell'indice —il prefisso, l'invisibilità— qui è un'altra cosa.

I sei metodi

Le dimensioni spiegano la scelta meglio della teoria. Su una tabella di 200 000 righe e 22 MB, con una marca temporale che cresce con l'inserimento:

- B-tree su quella colonna: 4 408 kB.
- BRIN sulla stessa colonna: 24 kB.

BRIN non conserva le righe, ma il minimo e il massimo di ogni blocco: serve quindi solo se l'ordine fisico assomiglia all'ordine del valore —e quando serve, costa quasi nulla—. Nella stessa tabella, un hash sulla colonna cliente ha occupato 7 032 kB e il B-tree su quella colonna 1 400 kB: più piccolo, e per giunta utile per intervalli e ordinamenti. Per questo il B-tree è la risposta predefinita e l'hash un caso specifico.

Indici parziali: metà dell'idea, un decimo della dimensione
Un indice può portare un WHERE, e allora indicizza solo le righe che soddisfano la condizione. Se interroghi la coda dei pendenti e i pendenti sono il 5 %, indicizza il 5 %:

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

Misurato su quella stessa tabella: l'indice completo della colonna occupava 1 400 kB e il parziale 88 kB. Il WHERE della query deve implicare quello dell'indice, altrimenti il pianificatore non lo userà.

Qui non ci sono indici di prefisso: ci sono indici su espressione
CREATE INDEX … ON paginas (url(64)) non è sintassi valida; il server legge url(64) come una chiamata di funzione e risponde 42883 function url(integer) does not exist. L'equivalente è indicizzare l'espressione:

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

E le note in calce: l'indice entra in gioco solo se la query scrive l'espressione allo stesso modo. WHERE lower(email) = 'ana@ejemplo.com' lo usa; WHERE email ILIKE 'Ana@%' no, e si mangia l'intera tabella.

Indici di copertura, e perché a volte non coprono
INCLUDE aggiunge colonne conservate nell'indice ma che non lo ordinano:

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

Con questo, EXPLAIN mostra Index Only Scan. Ma «only» è una mezza promessa: PostgreSQL non può sapere dall'indice se una riga è visibile alla tua transazione, quindi consulta la mappa di visibilità, che mantiene VACUUM. Misurato: subito dopo aver aggiornato mille righe, lo stesso piano diceva Heap Fetches: 2; dopo un VACUUM, Heap Fetches: 0. Un indice di copertura su una tabella che si scrive e non si pulisce va comunque alla tabella.

La regola del prefisso sinistro non è rigida
In MySQL, un indice su (A, B) non serve per WHERE B = ?. Qui può servire: misurato, una query che filtrava solo sulla seconda colonna si è risolta con un Index Only Scan sull'indice composito. Non è magia né un sostituto dell'indice giusto —lo percorre tutto invece di scenderci—, ma quando l'indice è molto più piccolo della tabella conviene lo stesso. Conseguenza pratica: prima di creare l'indice «che manca», guarda il piano; potrebbe già essercene uno in uso.

GIN per ciò che sta dentro un campo

CREATE INDEX idx_datos ON eventos USING gin (datos);

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

Senza l'indice quella query è una scansione sequenziale; con esso, un Bitmap Index Scan. Il GIN della prova occupava 864 kB su 200 000 righe. Per jsonb, se interroghi solo con @>, jsonb_path_ops occupa meno: 640 kB contro gli 864 del GIN normale, sugli stessi dati.

Costruire senza fermare la tabella
Un CREATE INDEX normale prende un lock SHARE: lascia leggere e ferma le scritture. CREATE INDEX CONCURRENTLY prende SHARE UPDATE EXCLUSIVE, quindi non ferma nulla, in cambio di due passaggi sulla tabella e di tre regole:

CREATE INDEX CONCURRENTLY idx_pedidos_cliente ON pedidos (cliente_id);

-- Ne è rimasto qualcuno a metà?
SELECT indexrelid::regclass AS indice, indisvalid
  FROM pg_index WHERE NOT indisvalid;

1. Non si può lanciare dentro una transazione — 25001 CREATE INDEX CONCURRENTLY cannot run inside a transaction block.
2. Se fallisce, lascia un indice non valido: nessuno lo usa, ma viene mantenuto a ogni scrittura. Va trovato con la query qui sopra ed eliminato.
3. Da PostgreSQL 12 esiste REINDEX INDEX CONCURRENTLY, il modo di ricostruire un indice gonfio senza fermare la tabella.

Gli indici che non usa nessuno

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;

Due cautele prima di cancellare. La prima: il contatore non è istantaneo. Nella prova, tre query che avevano usato l'indice lo hanno lasciato a 0 sul momento e un secondo dopo; solo dopo tre secondi ha detto 3. La seconda: conta dall'ultimo pg_stat_reset(), e su una replica conta il lavoro della replica, quindi un indice usato solo dal report di fine mese sembra morto negli altri 29 giorni.

Raccomandazione
Ogni indice in più si paga a ogni INSERT e a ogni UPDATE delle sue colonne. L'ordine che funziona è: guardare il piano, creare l'indice con CONCURRENTLY, riguardare il piano e rivedere pg_stat_user_indexes un mese dopo. Un indice che nessuno usa non è neutro: costa scrittura, spazio e tempo di VACUUM.

Parole chiave: indice, index, btree, brin, gin, gist, spgist, hash, indice parziale, indice su espressione, include, index only scan, heap fetches, concurrently, reindex, pg_stat_user_indexes, indisvalid, jsonb_path_ops, mappa di visibilità

Leggere un piano e confrontare la stima con il misurato, statistiche estese per colonne che si implicano, perché work_mem è per operazione e che cosa misura davvero il tasso di successo della cache.

Si applica a: PostgreSQL 13+

Diagnosticare qui significa leggere un piano e confrontare due numeri. Tutto il resto —indici, memoria, statistiche— discende da quel confronto.

EXPLAIN non esegue; EXPLAIN ANALYZE sì
La prima forma chiede solo il piano. La seconda esegue davvero la query per misurarla, e questo include un UPDATE o un DELETE. Se l'istruzione scrive, avvolgila:

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

I due numeri che contano
Ogni nodo porta una stima e una misura: rows=… è ciò che il pianificatore ha creduto, actual rows=… ciò che è uscito. Quando si allontanano molto, il piano cattivo è una conseguenza, non la causa.

Esempio misurato, con due colonne che si implicano —città e provincia—:

- Senza aiuto, il pianificatore ha stimato 11 710 righe e ne sono uscite 60 000: ha moltiplicato le due probabilità come se fossero indipendenti.
- Con una statistica estesa, la stima è passata a 59 610.

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

È lo strumento che in MySQL non esiste e che risolve l'intera famiglia del «il piano ignora il mio indice»: se il server crede di leggere il 4 % della tabella mentre ne legge il 20 %, sceglierà male per buone ragioni.

BUFFERS, che va chiesto

EXPLAIN (ANALYZE, BUFFERS) SELECT … ;

shared hit sono blocchi già in memoria; shared read, quelli che si sono dovuti andare a prendere. E temp read/written è la spia importante: la query è finita su disco. Inoltre track_io_timing è spento per impostazione predefinita, quindi i tempi di I/O non compaiono finché non lo si accende.

work_mem è per operazione, non per connessione
È l'impostazione che sorprende di più, e quella che si sbaglia più spesso. Ogni ordinamento, ogni hash join e ogni aggregazione per hash può usare fino a work_mem, e una query con tre di quelle operazioni —o con due processi paralleli— ne usa un multiplo. Il valore di fabbrica è 4 MB.

Misurato su 300 000 righe, la stessa query con ORDER BY:

- Con work_mem = 64kB: Sort Method: external merge Disk: 15680kB, e il nodo di ordinamento ha impiegato ~144 ms.
- Con work_mem = 64MB: Sort Method: quicksort Memory: 29627kB, e ha impiegato ~70 ms.

La cifra che dice la verità è Sort Method. Alzare work_mem globalmente moltiplica per connessioni e per operazioni; la mossa prudente è alzarlo nella sessione che ne ha bisogno:

SET work_mem = '64MB';

Quale query costa di più: pg_stat_statements
È l'equivalente del registro delle query lente, ma aggregato: una riga per forma di query, con chiamate, tempo totale e righe.
In Calíope la stessa cosa si legge senza scrivere la query: lo strumento Query lente mostra questo riepilogo, distingue l'estensione mancante dalla libreria non caricata, e porta qualsiasi riga nell'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;

Le note in calce: CREATE EXTENSION non basta. Va aggiunta a shared_preload_libraries e il server va riavviato; se ci si limita a creare l'estensione, la prima query risponde 55000 pg_stat_statements must be loaded via "shared_preload_libraries". Ordina per total_exec_time, non per mean_exec_time: la query che si mangia il pomeriggio di solito è una veloce eseguita un milione di volte.

Il tasso di successo della cache

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

È il numero che il Cruscotto di Calíope mostra come «Cache dati». Misura quante letture sono state servite senza scendere al file system dall'ultimo azzeramento delle statistiche —non quanta memoria è occupata—, e su un database piccolo esce altissimo per definizione: nella prova, 99,85 %. Un valore basso e persistente sì che indica che shared_buffers è troppo piccolo; uno alto non dimostra che vada tutto bene.

Parallelismo
max_parallel_workers_per_gather vale 2 per impostazione predefinita, e quando il pianificatore lo usa compare un nodo Gather o Gather Merge. Ogni processo ha il proprio work_mem, che è l'altra metà della trappola qui sopra.

Raccomandazione
L'ordine che funziona: trovare la query con pg_stat_statements, guardarla con EXPLAIN (ANALYZE, BUFFERS), confrontare rows con actual rows e solo allora decidere se manca un indice, mancano statistiche o manca memoria. Toccare shared_buffers prima di aver letto un piano è la strada lunga.

Parole chiave: prestazioni, explain, analyze, buffers, piano, pianificatore, stima, create statistics, statistica estesa, work_mem, sort method, external merge, pg_stat_statements, shared_preload_libraries, pg_stat_database, cache, track_io_timing, parallelismo

Dove si scrive ogni parametro e quale vince, che cosa richiede il riavvio, perché effective_cache_size non riserva memoria e perché max_connections non si alza.

Si applica a: PostgreSQL 13+

PostgreSQL ha 378 parametri —contati su questo server—, e la buona notizia è che se ne tocca una manciata. La prima cosa da imparare non è quali, ma dove si scrivono e quando entrano in vigore.

Quattro posti, e chi vince lo dice il server
- postgresql.conf — il file di sempre, modificato a mano.
- postgresql.auto.conf — lo scrive ALTER SYSTEM e non si modifica a mano; lo dice lui stesso nella prima riga.
- Per database o per ruolo — ALTER DATABASE … SET, ALTER ROLE … SET.
- Per sessione — SET, che dura quanto la connessione.

Chi ha vinto non si indovina: lo dice la colonna source di pg_settings. Misurato: dopo ALTER DATABASE demo SET work_mem = '32MB', una connessione nuova leggeva 32MB con source = database; dopo il 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');

Tre classi di parametro, e quella che fa male
La colonna context dice che cosa serve per cambiarlo:
- user / superuser — basta un SET nella sessione (work_mem, effective_cache_size).
- sighup — serve ricaricare (checkpoint_timeout, max_wal_size, quasi tutto l'autovacuum).
- postmaster — serve riavviare il server. Su questo server sono 65 su 378, fra cui shared_buffers, max_connections, wal_level, shared_preload_libraries e autovacuum_max_workers.

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

-- È rimasto qualcosa in attesa di un riavvio?
SELECT name, setting FROM pg_settings WHERE pending_restart;

Un dettaglio misurato che risparmia uno spavento: pending_restart non si accende nello stesso istante della ricarica. Subito dopo era ancora false, e mezzo secondo più tardi diceva true. Si consulta dopo, non nella stessa frase.

shared_buffers ed effective_cache_size non sono la stessa cosa, e uno dei due non riserva nulla
- shared_buffers è memoria vera: la cache propria del server. Di fabbrica sono 128 MB, pochi per qualsiasi server dedicato; la regola abituale è il 25 % della RAM.
- effective_cache_size non riserva nulla. È ciò che il pianificatore suppone esista fra la cache di PostgreSQL e quella del sistema operativo, e serve solo a decidere se un indice conviene. Cambiarlo non sposta un byte: cambia i piani.

Confonderli porta ad alzare effective_cache_size sperando in più cache, o ad alzare shared_buffers sperando in un altro piano.

max_connections non si alza: si mette un pool davanti
Vale 100 di fabbrica, e qui ogni connessione è un processo del sistema operativo, non un thread. Portarlo a mille non è un numero più grande: sono mille processi, con la loro memoria e il loro work_mem per operazione. La risposta è un gestore di pool (pgBouncer e simili). Ed è uno di quelli che richiedono il riavvio.

WAL e checkpoint
max_wal_size (1 GB di fabbrica) e checkpoint_timeout (5 min) decidono ogni quanto tutto viene scritto su disco. Se i checkpoint scattano per dimensione invece che per tempo, il server scrive a strappi; lo si vede accendendo log_checkpoints e lo si corregge alzando max_wal_size. checkpoint_completion_target arriva già a 0,9, che è ciò che distribuisce quella scrittura nel tempo invece di concentrarla.

Autovacuum
Qui valgono autovacuum_naptime 60 s, autovacuum_max_workers 3 —questo richiede il riavvio— e autovacuum_vacuum_scale_factor 0,2, cioè una tabella viene pulita quando è cambiato il 20 % delle sue righe. Su una tabella da un miliardo di righe significa aspettare duecento milioni di versioni morte, quindi le tabelle grandi portano la propria impostazione:

ALTER TABLE eventos SET (autovacuum_vacuum_scale_factor = 0.01);

synchronous_commit, l'unico che cambia la promessa
Spegnerlo fa sì che il COMMIT non aspetti che il WAL arrivi al disco: si guadagna latenza e si rischiano le ultime transazioni in caso di black-out —non l'integrità del database, solo gli ultimi commit—. È user, quindi si può spegnere solo dove quel patto è accettato:

SET synchronous_commit = off;

Raccomandazione
Toccarne pochi, uno alla volta e misurando. ALTER SYSTEM invece di modificare file —resta registrato e si annulla con ALTER SYSTEM RESET—, e per database o per ruolo prima che globale: un'impostazione che serve solo al report notturno non deve pagarla il resto della giornata.

Parole chiave: configurazione, 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

Perché l'account non contiene l'host, ruoli che sono utenti e gruppi insieme, la trappola per cui concedere non arriva alle tabelle di domani, e perché la sicurezza di riga può essere accesa e senza effetto.

Si applica a: PostgreSQL 13+

La differenza di fondo con MySQL è che qui l'account non contiene l'host. Non esiste ana@192.168.1.%: esiste il ruolo ana, e da dove può connettersi e come si autentica lo decide un file a parte, pg_hba.conf.

pg_hba.conf: vince la prima riga che corrisponde
Si legge dall'alto in basso e lì si ferma. E non serve aprirlo per vederlo:

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

La colonna error dice se una riga è scritta male —è ciò che evita il riavvio in cui il server non torna—. Le modifiche entrano in vigore con SELECT pg_reload_conf(), senza riavviare. Sul server di prova c'erano sette regole, quelle di 127.0.0.1 con trust e l'ultima, per tutto il resto, con scram-sha-256: l'ordine è la politica.

Un ruolo è utente e gruppo insieme
Non ci sono due concetti: CREATE USER è esattamente CREATE ROLE … LOGIN. Ciò che distingue una persona da un gruppo è l'attributo LOGIN, e nient'altro.

CREATE ROLE app_ro;                       -- senza LOGIN: fa da gruppo
CREATE ROLE ana LOGIN PASSWORD 'secreta';
GRANT app_ro TO ana;                      -- ana eredita i diritti di app_ro

I ruoli ereditano per impostazione predefinita, quindi ana usa i privilegi di app_ro senza fare nulla. Con NOINHERIT vanno richiesti con SET ROLE, che è ciò che si usa quando si vuole che il passaggio sia esplicito.

Le password si conservano con scram-sha-256, il valore predefinito da PostgreSQL 14 —il server di prova lo conferma—; md5 esiste ancora e non dovrebbe più servire.

La vera trappola: concedere non arriva al futuro
GRANT … ON ALL TABLES IN SCHEMA concede sulle tabelle che ci sono oggi. Misurato: dopo la concessione, app_ro poteva leggere la tabella esistente e non quella creata un minuto dopo. Ciò che copre il futuro è un'altra istruzione:

GRANT USAGE ON SCHEMA public TO app_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_ro;          -- quelle di oggi
ALTER DEFAULT PRIVILEGES IN SCHEMA public
      GRANT SELECT ON TABLES TO app_ro;                          -- quelle di domani

E in caratteri piccoli: i privilegi predefiniti sono di chi li dichiara, non dello schema, quindi se le tabelle le crea un altro ruolo vanno dichiarati anche con FOR ROLE. Ciò che è stato dichiarato si vede in pg_default_acl.

Concedere lascia inoltre una traccia che va disfatta: dopo un ON ALL TABLES, eliminare il ruolo fallisce con DependentObjectsStillExist e l'elenco delle tabelle dove è rimasto un privilegio —nella prova sono uscite perfino quelle di PostGIS—. La contraria è REVOKE, o DROP OWNED BY ruolo prima del DROP ROLE.

Lo schema public non è più di tutti
Da PostgreSQL 15, PUBLIC conserva USAGE sullo schema public ma non ha più CREATE. Misurato su 17.6: un ruolo appena creato dà USAGE = true e CREATE = false. Chi porta script da una versione precedente vedrà fallire il primo CREATE TABLE di un utente che prima poteva.

Ruoli predefiniti: sorvegliare senza essere superutente
Il server porta quindici ruoli già pronti. Quelli che evitano un superutente di troppo:
- pg_read_all_data, pg_write_all_data — leggere o scrivere tutto, senza altri poteri.
- pg_monitor — vedere le viste di statistiche complete; include pg_read_all_stats e pg_read_all_settings.
- pg_signal_backend — annullare query e chiudere sessioni altrui.
- pg_maintain (PostgreSQL 16+) — VACUUM, ANALYZE, REINDEX senza essere proprietari.

Sicurezza a livello di riga
Una policy filtra le righe che ogni ruolo vede, dentro la stessa tabella:

ALTER TABLE pedidos ENABLE ROW LEVEL SECURITY;

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

Ed ecco che cosa misurare prima di fidarsi. Con la stessa policy e la stessa tabella:

- Il ruolo a cui punta la policy ha visto una riga. Corretto.
- Il proprietario della tabella —non superutente— ha visto entrambe: il proprietario non è soggetto alle proprie policy finché non si dichiara ALTER TABLE … FORCE ROW LEVEL SECURITY. Con FORCE, ne ha vista una.
- Il superutente ha visto entrambe anche con FORCE. I superutenti e i ruoli con BYPASSRLS scavalcano sempre le policy.

Ossia: un'applicazione che si connette come proprietaria delle tabelle —per non dire come superutente— ha la sicurezza di riga accesa e senza effetto. La verifica è connettersi con il ruolo reale e contare le righe.

Raccomandazione
Un ruolo per applicazione, senza LOGIN per i gruppi, e nessuno con SUPERUSER tranne quello di amministrazione. ALTER DEFAULT PRIVILEGES nello stesso commit del GRANT, o il permesso durerà fino alla tabella successiva. E annota ciò che concedi in blocco, perché il DROP ROLE fra un anno te lo chiederà.

Parole chiave: sicurezza, ruolo, utente, gruppo, 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

Logico contro fisico e a che serve ciascuno, che cosa lascia fuori pg_dump lasciando il database senza nessuno che entri, perché il PITR non funziona di fabbrica e quale slot può riempirti il disco.

Si applica a: PostgreSQL 13+

Ci sono due tipi di backup e non servono alla stessa cosa. Sbagliare si scopre il giorno del ripristino.

Backup logico

-- da riga di comando, non nell'editor SQL:
-- pg_dump -d demo -Fc -f demo.dump
-- pg_restore -d demo_nueva -j 4 demo.dump

Il formato -Fc (custom) è quello da usare per impostazione predefinita: sullo stesso database, il dump in testo occupava 3,1 MB e quello custom 905 kB, e porta anche un indice —pg_restore -l ha elencato i trenta blocchi di dati— quindi permette di ripristinare una tabella, e di farlo in parallelo con -j.

Ciò che pg_dump non porta con sé, ed è quello che morde
I ruoli e le impostazioni globali non ci sono dentro. Misurato: il dump del database non aveva un solo CREATE ROLE, mentre pg_dumpall --globals-only ha prodotto i due che c'erano. Ripristinare solo il dump lascia un database perfetto in cui non può entrare nessuno. Un backup logico completo sono due file:
- pg_dumpall --globals-only — ruoli, password e privilegi del cluster.
- pg_dump di ogni database.

pg_dump è coerente —lavora su uno snapshot— e non blocca chi scrive; ma prende un lock ACCESS SHARE, quindi un ALTER TABLE lanciato nello stesso momento si mette ad aspettare, e dietro di lui si forma la coda.

Backup fisico
pg_basebackup copia l'intero cluster. Misurato sul server di prova: 84 MB in 1,3 s, con -X stream, che porta anche il WAL prodotto durante la copia —senza, la copia non è ripristinabile—. Lascia un backup_label che dice da quale punto del WAL riprodurre:

START WAL LOCATION: 0/13000028 (file 000000010000000000000013)

PITR: recuperare fino a un istante
È la ragion d'essere del backup fisico, e non funziona di fabbrica: archive_mode arriva spento, misurato su questo server. Senza archiviazione, un backup fisico ripristina esattamente il momento in cui è stato preso, e non un secondo di più.

Servono tre pezzi:
1. archive_mode = on e un archive_command che copi ogni segmento del WAL in un posto sicuro (o pg_receivewal da un'altra macchina).
2. Un pg_basebackup periodico.
3. Al ripristino: i file del backup, un restore_command che recuperi i segmenti, recovery_target_time = '…' e un file vuoto recovery.signal nella directory dei dati.

Quest'ultimo punto spiazza chi viene dalle versioni vecchie: da PostgreSQL 12 non esiste più recovery.conf; i parametri stanno in postgresql.conf e ciò che dichiara «questo è un ripristino» è il file segnale.

Gli slot di replica sono una lama a doppio taglio
Uno slot garantisce che il server non cancelli il WAL che un consumatore non ha letto. Se il consumatore sparisce e lo slot resta, il WAL si accumula fino a riempire il disco —e un disco pieno è un fermo, non un avviso—. Si sorvegliano così:

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 mette il tetto: superato quello, il server preferisce invalidare lo slot piuttosto che restare senza disco.

Il backup di Calíope è logico
Ciò che genera lo strumento di backup è SQL —CREATE e INSERT—, della famiglia di pg_dump, non di pg_basebackup. Serve a migrare e a recuperare dati; recuperare un intero server fino a un istante richiede quanto sopra, che è cosa del sistema operativo e non di un client.

Raccomandazione
I due file del backup logico, sempre insieme —--globals-only e il dump— e provare il ripristino, non il backup: un file generato senza errori può non ripristinarsi, e lo si sa solo ripristinandolo su un server vero.

Parole chiave: backup, pg_dump, pg_restore, pg_dumpall, globals, pg_basebackup, wal, archive_mode, archive_command, pitr, recovery_target_time, recovery.signal, slot di replica, wal_status, ripristino

Il DDL è transazionale, ciò che costa è il lock e non l'ALTER, quali modifiche riscrivono l'intera tabella, e il modello NOT VALID + VALIDATE che evita di fermare il database.

Si applica a: PostgreSQL 13+

Qui il DDL è transazionale. Questo cambia il modo di scrivere le migrazioni ed è la prima cosa da assimilare arrivando da MySQL, dove ogni ALTER conferma per conto suo.

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

Verificato: dopo quel ROLLBACK non restava nessuna delle tre cose. Una migrazione che fallisce a metà non lascia mezzo database; ed è per questo che il modello sano è mettere l'intera migrazione dentro una transazione.

Le eccezioni si contano sulle dita: CREATE INDEX CONCURRENTLY, VACUUM e ALTER SYSTEM non possono stare dentro una transazione.

Ciò che costa non è l'ALTER: è il lock
Quasi ogni ALTER TABLE prende un ACCESS EXCLUSIVE, che confligge perfino con un SELECT. Anche se il cambiamento dura un millisecondo, aspettare il lock può durare ore —e mentre aspetta, tutto ciò che arriva dietro si mette in coda—. Per questo il DDL in produzione si lancia sempre così:

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

Se non ottiene il lock, fallisce in tre secondi e si riprova. Senza questo, una migrazione da un millisecondo può fermare l'intera applicazione.

Che cosa riscrive la tabella e che cosa no
Riscrivere significa copiare l'intera tabella: dura in proporzione alla dimensione e richiede il doppio del disco per tutto il tempo. Misurato su 500 000 righe e 32 MB:

La regola che riassume la tabella: allargare è gratis, restringere riscrive. E ADD COLUMN con valore predefinito ha smesso di riscrivere in PostgreSQL 11: non serve più il giro di aggiungere la colonna vuota e riempirla a lotti.

Due avvisi che i tempi non mostrano:
- Un DROP COLUMN è istantaneo perché si limita a marcare la colonna come eliminata: lo spazio non torna finché la tabella non viene riscritta.
- SET NOT NULL e un CHECK normale non riscrivono, ma percorrono l'intera tabella con il lock tenuto. Su una tabella grande è già un fermo.

Il modello per non fermare il database: NOT VALID e poi VALIDATE
Un vincolo si può aggiungere in due tempi: prima si dichiara senza verificare l'esistente —istantaneo—, e poi si valida, che è la parte lenta ma con un lock molto più debole.

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;

Misurato: la dichiarazione NOT VALID ha impiegato 0,7 ms con SHARE ROW EXCLUSIVE —che lascia leggere—, e la validazione 67 ms con SHARE UPDATE EXCLUSIVE, che non blocca nemmeno chi scrive. Farlo in un colpo solo è costato lo stesso tempo, ma con il lock forte tenuto per tutta la durata. Su una tabella vera, quella differenza separa un rilascio da un disservizio.

Da quando il vincolo è NOT VALID, il server lo applica alle righe nuove; resta in sospeso solo la verifica di quelle vecchie.

Indici
Il CREATE INDEX normale ferma le scritture; CREATE INDEX CONCURRENTLY non ferma nulla, ma non entra nella transazione della migrazione, quindi va in un passo proprio e si verifica dopo (pg_index.indisvalid).

Raccomandazione
lock_timeout sempre; la migrazione dentro una transazione tranne ciò che non può; NOT VALID + VALIDATE per i vincoli su tabelle grandi; e attenzione ai cambi di tipo, dove si nasconde la riscrittura. Se bisogna restringere un tipo, quasi sempre è meglio aggiungere la colonna nuova, copiare a lotti e rinominare.

Parole chiave: ddl, alter table, migrazione, transazionale, rollback, lock_timeout, access exclusive, riscrittura, relfilenode, add column, drop column, set not null, not valid, validate constraint, create index concurrently

Che cosa non esiste e dà errore di sintassi, perché text non è peggio di varchar, numeric contro virgola mobile, che cosa conserva davvero timestamptz e perché jsonb non risparmia spazio.

Si applica a: PostgreSQL 13+

I tipi sono uno dei pochi punti in cui la migrazione da MySQL fallisce al primo tentativo, e meno male: ciò che non esiste dà errore di sintassi invece di essere accettato a metà.

Che cosa non esiste qui
- UNSIGNED — 42601 syntax error at or near "unsigned". Non ci sono interi senza segno; si usa il tipo successivo o un CHECK (n >= 0).
- INT(11) — anche questo 42601. La larghezza di visualizzazione di MySQL non esiste, e non ha mai significato ciò che sembrava.
- TINYINT, DATETIME, DOUBLE con parentesi e i tipi SET/ENUM di MySQL. Gli equivalenti sono smallint, timestamptz, double precision e un vero tipo enum.

Testo: usa text e basta
Misurato, con lo stesso valore 'hola': text ha occupato 5 byte, varchar(50) 5 e char(50) 51. Tutti e tre si conservano allo stesso modo; varchar(n) aggiunge solo un controllo di lunghezza e char(n) riempie di spazi. E quegli spazi cambiano i confronti: 'x' = 'x ' è falso con text e vero con char.

Qui text non è peggio di varchar: non c'è penalità. Si mette varchar(n) quando il limite è una regola di business, e char(n) praticamente mai.

Numeri: numeric per il denaro, e non è una superstizione

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

Misurato: in virgola mobile, 0.1 * 3 ha dato 0.30000000000000004; in numeric, 0.3 esatto. numeric è esatto e a precisione arbitraria, e si paga in spazio e velocità —10 byte contro gli 8 di float8 per quel valore, e aritmetica via software—. Per il denaro e per qualsiasi cifra sommata davanti a un cliente, numeric.

Dimensioni misurate: int 4, bigint 8, boolean 1, uuid 16 —contro i 36 che occuperebbe come testo—.

Date: timestamptz quasi sempre
timestamp e timestamptz occupano gli stessi 8 byte. La differenza non è la dimensione né che uno conservi il fuso: nessuno dei due conserva il fuso. timestamptz conserva un istante —converte in UTC in entrata e nel fuso della sessione in uscita—, e timestamp conserva una lettura di orologio e nient'altro.

Misurato, lo stesso istante con due fusi di sessione:

È lo stesso momento detto in due modi. Con timestamp non c'è nessuna conversione: ciò che è entrato è ciò che esce, e chi deve sapere a che ora è stato davvero non può ricavarlo. date occupa 4 byte e interval 16.

json contro jsonb: quasi sempre jsonb, e non per la dimensione

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 conserva il testo così com'è: mantiene l'ordine, gli spazi e perfino le chiavi ripetute. jsonb conserva un albero già analizzato: ordina le chiavi, tiene l'ultima ripetuta e normalizza gli spazi. Per questo jsonb si interroga in fretta e si indicizza con GIN, e json serve solo quando bisogna restituire il documento byte per byte come è arrivato.

Ciò che non è vero è che jsonb risparmi spazio: misurato su 200 000 documenti identici, json ha occupato 14 MB e jsonb 16 MB. Si sceglie jsonb per come si interroga, non per quanto pesa.

Array
Un array è un tipo di prima classe, con i suoi operatori —@> per la contenenza, array_length— e il suo indice GIN. È comodo per etichette e liste corte; smette di esserlo appena gli elementi hanno bisogno di attributi propri o vanno uniti a un'altra tabella. Un array non è una tabella risparmiata: è un valore.

serial o IDENTITY
serial non è un tipo: è zucchero che crea una sequenza e ne mette il nextval come valore predefinito. GENERATED ALWAYS AS IDENTITY è la forma standard e protegge anche la colonna: provare a inserirvi un valore a mano ha risposto 428C9 cannot insert a non-DEFAULT value into column. Per le tabelle nuove, IDENTITY.

Raccomandazione
text per il testo, numeric per il denaro, timestamptz per gli istanti, jsonb per i documenti che si interrogano e IDENTITY per le chiavi. E migrando da MySQL, lascia che l'errore di sintassi faccia il suo lavoro: è meglio di un tipo che viene accettato e significa un'altra cosa.

Parole chiave: tipi, text, varchar, char, numeric, float, decimal, timestamptz, timestamp, fuso orario, json, jsonb, array, uuid, serial, identity, unsigned, enum

I codici che si vedono ogni giorno, perché si programma per classe e non per codice, che cosa dicono il DETAIL e l'HINT che quasi nessuno mostra, e dov'è il codice quando la connessione non si apre nemmeno.

Si applica a: PostgreSQL 13+

Qui non ci sono numeri di errore. C'è lo SQLSTATE: cinque caratteri, di cui i primi due sono la classe. E la classe è ciò su cui si programma: dice che cosa fare senza sapere esattamente che cosa è fallito.

Quelli che si vedono ogni giorno

Le classi, che sono ciò da guardare

La conseguenza pratica: un'applicazione riprova la classe 40 e non riprova la 42. E se il ritentativo non distingue, o si perde una transazione legittima o si ripete mille volte una query che non funzionerà mai.

Il messaggio ha tre parti, e la terza è quella utile
MESSAGE dice che cosa è successo, DETAIL dà la riga o il valore, e HINT dice che fare. Misurati:

- 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 client che mostri solo il MESSAGE butta via metà dell'informazione —e proprio la metà che dice come uscirne—. Calíope compone tutte e tre.

Inoltre l'errore porta campi separati: la tabella, la colonna e il nome del vincolo. Con 23505 è arrivato constraint = er_d_pkey, ed è ciò che permette di tradurlo in «quell'email è già registrata» senza analizzare il testo del messaggio.

Un errore annulla la transazione
Dopo qualsiasi errore dentro un BEGIN, tutto ciò che segue risponde 25P02 finché non si fa ROLLBACK. Non è un difetto del client: è il progetto, e l'uscita elegante sono i punti di salvataggio.

Gli errori di connessione non arrivano nella risposta
Se il ruolo o il database non esistono, o la password è sbagliata, la connessione non si apre nemmeno: il client vede solo «connection failed». Il codice sta nel registro del server, e solo se lo si chiede:

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

Con questo, il registro è passato da FATAL: database "no_existe" does not exist a FATAL: 3D000: database "no_existe" does not exist —e 28000 per il ruolo inesistente—. È la differenza fra indovinare e sapere quando qualcuno segnala che «non riesce a connettersi».

Raccomandazione
Nel codice dell'applicazione, ramificare per classe e usare il codice completo solo per i messaggi che vede l'utente (23505 → «esiste già»). Salvare sempre lo SQLSTATE nel proprio registro: il testo del messaggio cambia con la lingua del server, il codice no.

Parole chiave: errore, sqlstate, codice, classe, 23505, 23503, 42p01, 42601, 42883, 42501, 25p02, 40001, 40p01, 55p03, 57014, 3d000, 28000, detail, hint, ritentativo, log_error_verbosity

Partizionamento dichiarativo e che cosa pota davvero il pianificatore, perché non ci sono indici globali né unicità su una sola colonna, il CHECK che trasforma un ATTACH da 68 ms in mezzo, e quanto costa la partizione predefinita.

Si applica a: PostgreSQL 13+

Il partizionamento qui è dichiarativo: si dichiara la chiave e ogni partizione è una tabella vera. Il padre non conserva nemmeno una riga —misurato: 0 byte, con i dati distribuiti fra le figlie—.

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;

Ci sono tre forme: RANGE (date, importi), LIST (paese, stato) e HASH (distribuire per distribuire).

La potatura è ciò che si cerca
Misurato su 300 000 righe distribuite in tre anni: una query con WHERE creado BETWEEN '2024-03-01' AND '2024-03-31' ha percorso solo pt_2024. La stessa tabella, filtrando su una colonna che non è la chiave, ha fatto un Parallel Append su tutte.

Ecco la regola che decide il progetto: la chiave di partizione è la colonna per cui filtri quasi sempre. Se le query non la nominano, il partizionamento non risparmia lettura: la distribuisce.

Ciò che non c'è: gli indici globali
Un indice creato sul padre ne crea uno per partizione —misurato: quattro partizioni, quattro indici—. Non esiste un indice unico che copra l'intera tabella, e da lì viene il limite da conoscere prima di progettare:

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

Una UNIQUE sul solo (id) viene rifiutata con 0A000 unique constraint on partitioned table must include all partitioning columns. L'unicità globale di un identificatore non si può garantire con il partizionamento dichiarativo; la si ottiene da una sequenza, unica per costruzione e non per vincolo.

ATTACH: la differenza fra 68 ms e mezzo
Agganciare una tabella esistente obbliga il server a verificare che tutte le sue righe rientrino nell'intervallo. Misurato su 300 000 righe:

Ossia: se la tabella porta già un vincolo CHECK che implica l'intervallo, il server salta la scansione. Su una tabella da un miliardo di righe è la differenza fra un ACCESS EXCLUSIVE istantaneo e mezz'ora.

La partizione predefinita non è gratis
DEFAULT raccoglie ciò che non cade in nessun intervallo ed evita l'errore inserendo una data inattesa. In cambio, misurato: ALTER TABLE … DETACH PARTITION … CONCURRENTLY ha risposto 55000 cannot detach partitions concurrently when a default partition exists. E inoltre ogni nuovo ATTACH deve percorrere la partizione predefinita per verificare che non nasconda righe dell'intervallo in arrivo.

Perché si partiziona davvero
Non per la velocità delle query —a quello servono gli indici—, ma per la manutenzione:
- Togliere un intero periodo è un DROP TABLE della sua partizione: misurato, 2,3 ms, e lo spazio torna al file system. Il DELETE equivalente ha impiegato di più e soprattutto lascia righe morte che VACUUM dovrà pulire e spazio che non torna.
- VACUUM e ANALYZE lavorano per partizione, quindi il lavoro di manutenzione smette di crescere con l'intero storico.
- I dati vecchi si possono sganciare e archiviare senza toccare la tabella viva.

Raccomandazione
Partiziona per ciò che cancellerai, non per ciò che interrogherai; e verifica che le tue query portino la chiave nel WHERE guardando il piano, non supponendolo. Prima di partizionare una tabella che esiste già, chiediti se ciò che manca non sia un indice: il partizionamento aggiunge parti mobili in cambio di una manutenzione più economica, e quel conto torna solo oltre una certa dimensione.

Parole chiave: partizionamento, partition, range, list, hash, potatura, pruning, attach, detach, default, indice globale, unique, 0a000, drop partition, manutenzione

Perché qui non esiste la trappola di utf8, in che cosa differiscono codifica e collazione, come cambia l'ordine con ciascuna, e perché un LIKE per prefisso non usa il tuo indice.

Si applica a: PostgreSQL 13+

La trappola che è costata tante migrazioni in MySQL qui non esiste: non c'è un utf8 che non fosse UTF-8. La codifica si dichiara creando il database, e UTF8 è tutto UTF-8.

Misurato, conservando quattro stringhe in una normale colonna text:

Non è servito dichiarare nulla di speciale. length() conta i caratteri e octet_length() conta i byte, la distinzione che in MySQL bisognava inseguire tipo per tipo.

Codifica e collazione sono due cose diverse
- Codifica — come si conservano i byte. È del database, si fissa alla creazione e non si cambia dopo: per cambiarla bisogna fare il dump e ricreare.
- Collazione — come si ordina e si confronta. Si può fissare per database, per colonna, per espressione e perfino in un ORDER BY.

Sul server di prova: server_encoding = UTF8, e i database con en_US.utf8 del fornitore libc. Ci sono 815 collazioni disponibili, di due fornitori: quelle del sistema (C, POSIX, en_US.utf8) e quelle di ICU (es-ES-x-icu, unicode), che da PostgreSQL 15 possono essere perfino il fornitore predefinito di un database.

Che cosa cambia con la collazione

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

Misurato, i risultati non si somigliano:

- C — A, B, a, b, á. Ordina per il numero del carattere: tutte le maiuscole prima delle minuscole, e gli accenti in fondo.
- es-ES-x-icu — a, A, á, b, B. Ordina come un dizionario.

E il confronto cambia con essa: 'a' < 'B' è falso con C e vero con la collazione spagnola. Un elenco che «esce ordinato male» non è quasi mai un bug dell'applicazione: è la collazione della colonna.

La collazione decide se un indice serve per LIKE
È il dettaglio pratico più difficile da scoprire da soli. Con una collazione linguistica —quella del database—, un normale indice B-tree non serve per le ricerche per prefisso. Misurato su 200 000 righe:

- WHERE s = 'usuario42' → Index Only Scan.
- WHERE s LIKE 'usuario42%' → Seq Scan, con l'indice lì presente.
- Dopo aver creato l'indice con la classe di operatori giusta, la stessa query è passata a Bitmap Index Scan.

CREATE INDEX idx_prefijo ON ch_like (s text_pattern_ops);

text_pattern_ops confronta byte per byte, che è esattamente ciò di cui ha bisogno LIKE 'qualcosa%'. Con la collazione C sulla colonna non serve, perché confronta già così.

Le collazioni hanno una versione, e conta
Il database registra la versione della collazione con cui sono stati costruiti i suoi indici —misurato: datcollversion = 2.36, quella della libreria di sistema—. Se il sistema operativo viene aggiornato e quella versione cambia, l'ordine può cambiare, e un indice costruito con l'ordine precedente smette di essere corretto: ricerche che non trovano righe che ci sono. PostgreSQL segnala la discrepanza, e la risposta è REINDEX.

È il motivo per cui molti scelgono la collazione C o ICU per i database che devono sopravvivere agli aggiornamenti di sistema: ICU porta la propria versione e non dipende da quella del sistema.

Raccomandazione
UTF8 sempre. La collazione si decide alla creazione del database, perché cambiarla dopo costa caro: C per le colonne che sono codici, identificatori o percorsi —ordina in fretta e va bene con LIKE—, e una collazione linguistica per ciò che legge una persona. E se una query con LIKE 'x%' non usa l'indice, guarda la collazione prima di toccare la query.

Parole chiave: codifica, encoding, utf8, collazione, collation, collate, icu, libc, text_pattern_ops, like, prefisso, order by, datcollversion, reindex, octet_length

Quelli che mordono davvero —63 byte di nome, 1 600 colonne, 32 per indice—, perché quello dell'identificatore dà un avviso e non un errore, e che cosa fa TOAST con un valore che non entra nella pagina.

Si applica a: PostgreSQL 13+

I limiti di PostgreSQL non somigliano a quelli di InnoDB, e quelli che mordono ogni giorno non sono i grandi.

Quelli che si toccano davvero

I primi tre sono misurati: 1 600 colonne si sono create senza problemi e 1 601 hanno fallito; un indice di 32 colonne è nato e quello di 33 no.

Quello dell'identificatore è l'unico che non dà errore
Un nome di 72 caratteri è stato conservato come uno di 63, e il server l'ha detto con un avviso:

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

Un avviso non è un errore: l'istruzione è andata avanti. Per questo due nomi lunghi che si distinguono solo dal 64° carattere finiscono per essere lo stesso oggetto, e il guasto si vede molto più tardi. Sono i generatori di nomi —indici, vincoli, tabelle temporanee per lotto— a inciampare qui, non la mano di qualcuno.

I grandi, che non sono quasi mai il problema
Non sono misurati qui —servirebbe riempire un disco— e vanno con la loro cifra ufficiale:

- Dimensione massima di una tabella: 32 TB.
- Dimensione massima di un campo: 1 GB.
- Dimensione massima di una riga: 1,6 TB.
- Righe per tabella: nessun limite definito.
- Database per cluster e tabelle per database: nessun limite pratico.

Ciò che si esaurisce molto prima di uno qualsiasi di questi è la manutenzione: VACUUM, backup e ricostruzione di indici su una tabella di terabyte.

TOAST: perché un text da 1 GB non rompe la pagina da 8 kB
Una riga deve stare in una pagina, e una pagina è di 8 kB. I valori grandi vengono compressi e spostati in una tabella laterale —quello che chiamano TOAST—, in automatico e senza dichiarare nulla.

Misurato, conservando 100 000 byte di testo in una colonna text:

- octet_length — 100 000 byte di dato.
- pg_column_size — 1 156 byte, quindi è stato compresso.
- La tabella occupava 8 192 byte e 16 kB contando il suo TOAST.

Da cui una conseguenza pratica: un SELECT * su una tabella con colonne grandi paga la lettura di quelle colonne anche se nessuno le guarda. Chiedere solo le colonne necessarie non è stile, è I/O.

I limiti che invece si configurano
Non sono del motore ma dell'istanza, ed è per questo che si vedono in pg_settings: max_connections (100 di fabbrica), max_locks_per_transaction (64), max_wal_size, work_mem. Sono quelli che si esauriscono su un server vero; quelli della tabella qui sopra, quasi mai.

Raccomandazione
Sorvegliarne solo due: quello dei 63 byte quando qualcosa genera nomi, e quello delle colonne per indice quando qualcuno propone un indice composito con mezzo schema dentro. Del resto si viene a sapere da pg_settings, non dalla documentazione.

Parole chiave: limiti, identificatore, 63 byte, troncato, 1600 colonne, 32 colonne, indice, toast, pagina, 8 kB, 32 tb, 1 gb, pg_settings, max_connections

varchar contro nvarchar, il prefisso N, le collazioni _UTF8, cosa fa ogni suffisso, il conflitto di collazione e perché un parametro N'…' può lasciare inutilizzato il tuo indice.

Si applica a: SQL Server 2022+

Se arrivi da MySQL, qui non ci sono né utf8mb4 né SET NAMES. In SQL Server la codifica di un testo non si dichiara a parte: la decide la sua collazione, e ci sono due famiglie di tipi di testo.

varchar e nvarchar sono due codifiche
- nvarchar (e nchar) memorizza UTF-16: qualsiasi carattere Unicode, qualunque sia la collazione.
- varchar (e char) memorizza i byte della code page della sua collazione. Con una collazione classica come Latin1_General_CI_AS è Windows-1252, e ciò che non vi rientra viene memorizzato come ?.
- Con una collazione che termina in _UTF8 (da SQL Server 2019), varchar memorizza UTF-8.

Misurato sul server di prova (SQL Server 2025), con il database in Latin1_General_100_CI_AS_SC_UTF8:

LEN() conta i caratteri e DATALENGTH() i byte. La n di varchar(n) sono byte, e quella di nvarchar(n) coppie di byte, non caratteri. Misurato: un varchar(10) in UTF-8 accetta cinque ñ, e dieci danno Msg 2628 («String or binary data would be truncated»); un nvarchar(10) accetta dieci ñ ma non sei emoji.

Il prefisso N
Un letterale senza N davanti è varchar, e viene convertito nella code page del database prima di raggiungere qualsiasi colonna. Misurato in un database Latin1_General_CI_AS:

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

E in una colonna varchar di quel database, anche con la N sul letterale, Zúrich ✓ 日本 è stato memorizzato come Zúrich ? ??. Il dato si perde in scrittura, senza errori né avvisi, e cambiare poi la collazione della colonna non lo recupera: ciò che è memorizzato sono già punti interrogativi.

Cosa dice ogni suffisso

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

Misurato: 1, 0, 1, 0.

- _CI / _CS — non distingue / distingue maiuscole e minuscole.
- _AI / _AS — non distingue / distingue gli accenti.
- _SC — conta come un carattere quelli fuori dal piano di base, come un'emoji. Misurato: LEN(N'😀') dà 1 con _SC e 2 senza, e senza _SC un LIKE N'_' non la trova.
- _UTF8 — varchar in UTF-8.
- _BIN2 — confronta per punto di codice: ha ordinato A, B, a, b, n, o, á, ñ, mentre Latin1_General_100_CI_AS e Modern_Spanish_100_CI_AS hanno dato A, a, á, b, B, n, ñ, o.
- Il 100 è la versione delle tabelle di ordinamento; quelle senza (Latin1_General_CI_AS) sono più vecchie, e quelle che iniziano con SQL_ sono le collazioni ereditate da SQL Server 2000.

Il server di prova offre 5540 collazioni, di cui 1585 _UTF8 (sys.fn_helpcollations()).

Server, database, colonna ed espressione

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

La collazione si fissa a quattro livelli, e ognuno prende quella del livello superiore al momento della creazione:

- Server — quella di master e tempdb, scelta all'installazione.
- Database — CREATE DATABASE … COLLATE ….
- Colonna — nome varchar(40) COLLATE Latin1_General_100_CS_AS.
- Espressione — WHERE a = b COLLATE Latin1_General_100_CI_AS, o dentro un ORDER BY.

ALTER DATABASE … COLLATE non tocca le colonne già esistenti: misurato, il database è passato a _UTF8 e le sue colonne sono rimaste in Latin1_General_CI_AS. Ogni colonna si cambia con il suo ALTER TABLE … ALTER COLUMN.

Il conflitto di collazione
Due testi con collazioni diverse non si confrontano da soli:

- WHERE a = b → Msg 468, «Cannot resolve the collation conflict».
- Un UNION ALL delle due colonne → Msg 457; con testo del catalogo (sys.* è in Latin1_General_CI_AS) → Msg 451.
- Si risolve mettendo COLLATE su un lato.

Il caso più frequente sono le tabelle temporanee: #tmp vive in tempdb e nasce con la collazione del server, non con quella del tuo database. Misurato in un database Latin1_General_100_CI_AS su un server _UTF8: un JOIN con #tmp ha dato Msg 468. La soluzione è dichiararne le colonne con COLLATE DATABASE_DEFAULT.

La collazione e gli indici
Su 100.000 righe con un indice sulla colonna:

- WHERE s = 'usuario42' → Index Seek. Con COLLATE sulla colonna → Index Scan: l'indice viene letto per intero.
- WHERE s LIKE 'usuario42%' → Index Seek, a differenza di PostgreSQL.
- Un parametro N'…' contro una colonna varchar: con una collazione Windows (Latin1_General_100_CI_AS) l'indice è stato ancora usato, con 7 letture logiche; con una collazione SQL_ (SQL_Latin1_General_CP1_CI_AS) l'intera colonna viene convertita in nvarchar e il piano diventa un Index Scan: 334 letture contro 3. Se una ricerca per uguaglianza è lenta e il parametro arriva come nvarchar, controlla la collazione della colonna.

Raccomandazione
Per un nuovo database, una collazione _100_…_SC_UTF8, che rende varchar un UTF-8 completo. Dichiara le colonne delle tabelle temporanee con COLLATE DATABASE_DEFAULT. E decidi la collazione quando crei il database: cambiarla dopo si fa colonna per colonna.

Parole chiave: codifica, encoding, collazione, collation, collate, varchar, nvarchar, utf8, utf-16, prefisso n, unicode, _sc, _bin2, 468, 451, 457, 2628, tempdb, database_default, datalength

datetime contro datetime2, bit al posto di boolean, money contro decimal, GUID che non frammentano, rowversion che non è una data, il limite di 8000 byte e il tipo json del 2025.

Si applica a: SQL Server 2022+

Se vieni da MySQL, diversi tipi hanno nomi simili e si comportano in modo diverso, e due nomi ingannano: timestamp non è una data e datetime arrotonda. Ciò che non esiste dà errore, ed è un bene: non c'è UNSIGNED, tinyint va solo da 0 a 255 (con -1 si ottiene Msg 220, overflow aritmetico) e SELECT TRUE dà Msg 207: TRUE non è una parola del linguaggio.

Date: datetime2, non datetime
datetime è il tipo vecchio e arrotonda a 1/300 di secondo. Misurato:

L'ultima riga è quella che rompe i report: un WHERE fecha <= '2026-09-30 23:59:59.999' su datetime include la mezzanotte del giorno dopo. Per un giorno intero, con qualsiasi tipo, fecha >= '2026-09-30' AND fecha < '2026-10-01'.

datetime2 salva fino a cento nanosecondi (in datetime2(7) .1234567 è rimasto tale e quale), comincia dall'anno 1 —'1700-01-01' in datetime dà Msg 242, fuori intervallo— e occupa lo stesso o meno: misurato, datetime 8 byte, datetime2(7) 8, datetime2(3) 7 e datetime2(0) 6. E non si mescolano senza pensarci: lo stesso 12:00:00.001 in datetime2(3) e in datetime non è uguale, perché datetime l'aveva già salvato come .000.

Altre dimensioni misurate: date 3 byte, time 5, smalldatetime 4 (al minuto) e datetimeoffset 10.

datetimeoffset conserva lo scostamento
A differenza di timestamptz di PostgreSQL, conserva lo scostamento con cui è stato scritto, e confronta per istante: '2026-09-30T12:00:00+05:30' e '2026-09-30T06:30:00+00:00' sono risultati uguali. SWITCHOFFSET(valore, '+00:00') lo porta in UTC, e AT TIME ZONE 'Central European Standard Time' l'ha dato come 08:30 +02:00, con l'ora legale applicata. Calíope lo mostra nell'ora del dato, con lo scostamento accanto.

Non c'è boolean: c'è bit
bit è un numero che vale 0 o 1, non un valore di verità. Misurato: assegnargli 5, -1 o la stringa 'true' salva 1; un WHERE @b da solo dà Msg 4145 —bisogna scrivere WHERE @b = 1— e bit + bit dà Msg 402. Calíope lo mostra come 1 o 0.

Denaro: decimal, e money con cautela

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

money ha quattro decimali fissi, anche nei risultati intermedi: 1 / 3 ha dato .3333, e moltiplicato per 3 non torna più all'intero. decimal allarga la scala del risultato (ha dato .3333333333333333333). Per i calcoli, decimal; money occupa 8 byte e smallmoney 4. float è binario come ovunque: 0.1 + 0.2 ha dato 0.30000000000000004, e l'uguaglianza con 0.3 è falsa; in decimal, vera.

GUID: uniqueidentifier, e l'ordine conta
Un uniqueidentifier occupa 16 byte. Come chiave primaria clustered, NEWID() sparge le righe a caso nell'indice. Misurato con 20.000 righe inserite una per una:

NEWSEQUENTIALID() vale solo come DEFAULT di una colonna: SELECT NEWSEQUENTIALID() dà Msg 302. Calíope mostra il GUID in maiuscolo, come SQL Server.

rowversion non è una data
rowversion —il tipo che prima si chiamava timestamp, nome ancora accettato quando si crea una colonna— è un contatore di 8 byte del database che cambia a ogni scrittura della riga. Misurato: due righe sono nate con 0x…07D4 e 0x…07D5, e aggiornare la prima l'ha portata a 0x…07D6. Non salva nessun orario. Serve per la concorrenza ottimistica: si legge la versione e si aggiorna con WHERE ver = @letta. Calíope lo mostra come binario, [8 bytes].

Testo: 8000 byte, o max
Fuori da max, varchar(n) arriva a 8000 e nvarchar(n) a 4000: varchar(8001) dà Msg 131 e nvarchar(4001), Msg 2717. Oltre ci sono varchar(max) e nvarchar(max).

Anche la riga ha un limite: due char(5000) in una tabella danno Msg 1701 —la riga minima sarebbe di 10.007 byte e il massimo è 8060—. Due varchar(8000) pieni ci stanno (misurato: 16.000 byte in una riga), perché i dati variabili escono dalla pagina quando non ci stanno.

E una trappola che non avvisa: REPLICATE('x', 10000) assegnato a un varchar(max) ha dato 8000 caratteri, perché la funzione lavora nel tipo del suo argomento. Con REPLICATE(CAST('x' AS varchar(max)), 10000), 10.000.

JSON: il tipo json è di SQL Server 2025
In SQL Server 2022 il JSON si salva in nvarchar(max) e si valida con CHECK (ISJSON(col) = 1), che rifiuta quello malformato con Msg 547. Dal 2025 esiste il tipo json, che lo rifiuta in scrittura con Msg 13609 e lo salva già analizzato. Misurato su 20.000 copie di un ordine di 142 caratteri, json ha occupato 5,4 MB e nvarchar(max) 6,0 MB; per un documento minimo, {"b":1,"a":2}, al contrario: 60 byte contro 26.

E una differenza che si vede: con una chiave ripetuta, ISJSON accetta {"b":1,"a":2,"a":3}, e il tipo json l'ha restituito come {"b":1,"a":2}: ha tenuto la prima, senza avvisare. Conserva l'ordine delle chiavi. Calíope riceve il json come testo e lo mostra così com'è.

I tipi speciali
- geography e geometry — tipi con un formato binario proprio, che non è WKB. geography::Point(40.4168, -3.7038, 4326) occupa 22 byte e il suo STAsText() dà POINT (-3.7038 40.4168): il costruttore vuole latitudine, longitudine e il testo esce come longitudine, latitudine. Calíope mostra la cella come [22 bytes]; per leggerla, col.STAsText() nella query.
- hierarchyid — un percorso in un albero: '/1/3/' è stato salvato in 2 byte, con livello 2 e genitore /1/. Calíope non lo decodifica e la cella dice tipo non supportato: hierarchyid; con col.ToString() nella query, si legge.
- sql_variant — una colonna che contiene valori di più tipi, ognuno con il suo: SQL_VARIANT_PROPERTY(@v, 'BaseType') ha dato decimal. Calíope lo mostra secondo il suo tipo di base. Ha senso nel catalogo (sys.extended_properties); in una tabella tua di solito è una colonna progettata male.

IDENTITY o SEQUENCE
IDENTITY è una proprietà della colonna, e la protegge: inserire un valore a mano dà Msg 544, salvo con SET IDENTITY_INSERT tabla ON, e dopo il contatore riparte dal più alto (dopo aver inserito 100, la riga successiva è stata la 101). E lascia buchi: un INSERT annullato con ROLLBACK ha consumato il 3, e la riga successiva è stata la 4. Un buco non è un dato perso.

SEQUENCE è un oggetto a parte, che più tabelle possono condividere: con DEFAULT NEXT VALUE FOR sq su due tabelle, i valori si sono divisi in 1 e 3 in una e 2 nell'altra. Si usa quando serve il numero prima di inserire, o condiviso; per la chiave di una tabella, IDENTITY.

Raccomandazione
datetime2 per le letture d'orologio e datetimeoffset per gli istanti, decimal per il denaro, bit sapendo che è un numero, NEWSEQUENTIALID() o un intero se la chiave clustered è un GUID, e il tipo json solo se il tuo server più vecchio è il 2025. Per il testo, «Codifica e collazioni (SQL Server)».

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

Come si legge un Msg, cosa dice il suo livello, i numeri che vedi ogni giorno, quali errori annullano la transazione e quali no, TRY…CATCH con THROW, e dove sta il motivo di un 18456.

Si applica a: SQL Server 2022+

Se vieni da MySQL, anche qui ci sono numeri —niente SQLSTATE come in PostgreSQL—, ma ogni errore porta quattro dati e contano tutti. Così lo mostra Calíope, su una riga; sqlcmd lo divide in due:

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

- Msg — il numero. È ciò che si cerca e ciò su cui si programma.
- Level — la gravità, da 0 a 25. Dice di chi è la colpa e cosa succede alla connessione.
- State — un sottocodice che il server usa per distinguere cause con lo stesso numero. Per la maggior parte degli errori non conta; per il 18456 è l'unica cosa che dice il perché.
- Line — la riga all'interno del batch (ciò che sta tra due GO), o all'interno della procedura: un errore lì dentro esce come Procedure p_falla, Line 3.

Il livello, la prima cosa da guardare

Un livello 20 o superiore non si può sollevare a mano senza essere sysadmin e senza WITH LOG: dà Msg 2754. Calíope tratta un livello 20 o superiore come una connessione persa.

Quelli che vedi ogni giorno
Provocati uno per uno contro SQL Server 2025:

Per vedere il testo di qualsiasi numero: SELECT text FROM sys.messages WHERE message_id = 1205 AND language_id = 1033;. Ci sono 16.785 messaggi in inglese, in 22 lingue.

Cosa annulla ogni errore: la trappola per chi viene da PostgreSQL
In PostgreSQL un errore annulla l'intera transazione. Qui dipende dall'errore, e la maggior parte annulla solo l'istruzione. Misurato, senza attivare niente:

BEGIN TRAN;
INSERT padre VALUES (10, 'd@x.es', 1);
INSERT padre VALUES (10, 'e@x.es', 1);  -- Msg 2627
INSERT padre VALUES (11, 'f@x.es', 1);
COMMIT;
-- sono rimaste le righe 10 e 11

La transazione è rimasta aperta, il COMMIT ha confermato ciò che aveva funzionato e il batch è andato avanti come se niente fosse. Cosa ha fatto ogni errore misurato:

- Solo l'istruzione: 2627, 2601, 547, 515, 2628, 8134, e anche 1222: scaduta l'attesa, @@TRANCOUNT era ancora 1.
- Il batch e la transazione: 245 (conversione) ha annullato la transazione e interrotto il batch senza niente di attivo; 1205 anche, nella vittima; un ROLLBACK dentro un trigger dà Msg 3609 e interrompe il batch.
- La connessione: livello 20 o superiore.

SET XACT_ABORT ON lo cambia: con esso, lo stesso 2627 ha annullato l'intera transazione e interrotto il batch (le righe 20 e 21 non sono rimaste). È ciò che va messo all'inizio di ogni procedura che apre una transazione.

TRY…CATCH, e cosa non cattura

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

Dentro il CATCH, ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), ERROR_PROCEDURE() e ERROR_MESSAGE() restituiscono i dati dell'errore (misurato: 2627, 14, 1, 4, NULL). E XACT_STATE() dice cosa puoi fare con la transazione: ha dato 1 senza XACT_ABORT (si può confermare) e -1 con esso (si può solo annullare).

Cosa un TRY non cattura: un oggetto che non esiste nel suo stesso ambito. SELECT * FROM tabla_que_no_existe dentro un TRY è uscito come Msg 208 senza passare dal CATCH, perché fallisce in compilazione; dentro un EXEC sp_executesql, è stato catturato. Non cattura nemmeno ciò che è di livello 10 o inferiore.

THROW contro RAISERROR
- THROW; senza argomenti, dentro un CATCH, rilancia l'errore originale: il numero arrivato fuori era ancora 8134. Rilanciarlo con RAISERROR(ERROR_MESSAGE(), 16, 1) lo ha trasformato in 50000, e il numero si è perso.
- RAISERROR non ferma il batch: ciò che veniva dopo è stato eseguito. THROW sì.
- THROW 50001, 'testo', 1 solleva un errore tuo, sempre di livello 16 e con un numero da 50000 in su. RAISERROR accetta inoltre un numero registrato con sp_addmessage e ne riempie gli argomenti: RAISERROR(50100, 16, 1, 42) ha dato «El pedido 42 no existe».

Il testo cambia con la lingua; il numero no
Con SET LANGUAGE Spanish, il 8134 è arrivato come «Error de división entre cero.»: lo stesso numero con un altro testo. La lingua è quella della sessione, e Calíope si connette con la lingua predefinita del login. Per questo, nel codice e nei tuoi registri, si confronta il numero, mai il testo.

Il 18456: il motivo è nel registro del server
Un accesso fallito arriva al client sempre uguale: Msg 18456, Level 14, State 1, «Login failed for user '…'». Misurato con tre cause diverse, il client ha visto State 1 tutte e tre le volte. Lo stato vero è solo nel registro del server:

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

Quando il database richiesto alla connessione non esiste, prima del 18456 arriva un 4060 («Cannot open database … requested by the login»), e quello sì dice il motivo. Se qualcuno «non riesce a entrare», il registro del server distingue in un secondo ciò che da fuori è la stessa frase.

Cosa mostra Calíope
Quando un'istruzione fallisce, Calíope mostra nella scheda dei risultati il messaggio del server per intero, nella forma Msg, Level, State, Line di sopra, e tutti i messaggi, uno per riga: un CREATE TABLE con un vincolo ripetuto porta il 2714 e poi il 1750, e tutti e due dicono qualcosa. Quegli errori restano nella loro scheda. Quelli che impediscono di connettersi —il 18456, il 4060— e quelli di livello 20 o superiore, che interrompono la connessione, arrivano inoltre al registro degli errori di Calíope. I messaggi informativi —un PRINT, un RAISERROR di livello 10— non li mostra.

Raccomandazione
SET XACT_ABORT ON e TRY…CATCH con IF @@TRANCOUNT > 0 ROLLBACK; THROW; in ogni procedura che apre una transazione. Nell'applicazione, ritentare il 1205 —l'intera transazione, che il server ha già annullato— e non ritentare i livelli da 14 a 16, che riguardano il dato o il programma. Salvare sempre il numero, mai il testo. E davanti a un 18456, leggere il registro del server prima di toccare la password.

Parole chiave: errore, msg, livello, gravità, severity, level, state, 18456, 1205, 2627, 2601, 547, 208, 102, 2628, 8152, 515, 229, 916, 4060, 1222, try, catch, throw, raiserror, xact_abort, xact_state, error_number, sys.messages, xp_readerrorlog, nuovo tentativo

L'indice cluster che è la tabella, il Key Lookup e quando INCLUDE lo elimina, indici filtrati, colonne calcolate invece di indici su espressione, columnstore, FILLFACTOR e frammentazione, e gli indici che nessuno usa.

Si applica a: SQL Server 2022+

Se vieni da MySQL, la cosa più simile è InnoDB: anche qui la tabella è un indice. Cambia che quell'indice —quello cluster— lo scegli tu e non deve per forza essere la chiave primaria, che una tabella può non averlo, e che al posto degli indici di prefisso o invisibili ci sono indici filtrati, colonne calcolate e columnstore.

Tutto quello che segue è misurato su una tabella di 200 000 righe e circa 32 MB.

La tabella è l'indice cluster
PRIMARY KEY crea un indice cluster se la tabella non ne ha ancora uno: le righe vengono salvate ordinate per quella chiave. Una tabella senza indice cluster è un heap (HEAP in sys.indexes): le righe vanno dove c'è posto.

Quello che da MySQL non si vede è il prezzo: la chiave cluster viaggia dentro ogni indice non cluster, perché è così che l'indice trova la riga. Lo stesso indice su cliente_id ha occupato 2 952 kB con una chiave cluster int e 5 416 kB con una uniqueidentifier. Una chiave cluster stretta e crescente rende più economici tutti gli altri indici; perché NEWID() è una cattiva chiave cluster lo racconta l'argomento sui tipi di dati.

Key Lookup, e cosa risolve INCLUDE
Un indice non cluster porta solo le sue colonne e la chiave cluster. Se la query ne chiede un'altra, ogni riga trovata obbliga a tornare alla tabella: questo è il Key Lookup.

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

CREATE INDEX ix_cliente_inc ON dbo.pedidos (cliente_id) INCLUDE (total);
-- Index Seek (ix_cliente_inc) e nient'altro: 3 letture logiche

INCLUDE salva la colonna nelle foglie dell'indice senza ordinare per essa. Qui l'indice è passato da 2 952 kB a 4 752 kB.

Il Key Lookup si paga riga per riga, quindi smette di convenire molto presto. Misurato: con 992 righe (lo 0,5 % della tabella) il piano usava ancora l'indice e il lookup; con 1 483 (lo 0,7 %) percorreva già tutta la tabella. Un indice «che non si usa» spesso è un indice a cui manca un INCLUDE.

La regola del prefisso sinistro
Con un indice su (fecha, cliente_id) e WHERE cliente_id = 77, SQL Server non può scendere lungo l'indice. Può, come PostgreSQL, percorrerlo tutto se costa meno della tabella: Index Scan e 809 letture logiche. Non è un Index Seek, e l'ordine delle colonne continua a contare.

Indici filtrati
Un indice può avere un WHERE, e allora indicizza solo ciò che lo soddisfa:

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

Con gli ordini in sospeso al 5 %, ha occupato 160 kB, contro i 2 952 kB dell'indice completo sulla stessa colonna. La trappola è che il piano deve andare bene per qualsiasi valore: con il letterale WHERE estado = 'pendiente' l'ha usato; con una variabile (WHERE estado = @e), no, e forzarlo con WITH (INDEX(ix_pend)) dà Msg 8622.

E un uso che sorprende chi viene da MySQL: in SQL Server un indice univoco ammette un solo NULL. CREATE UNIQUE INDEX ux_email ON dbo.pedidos (email) con due email a NULL dà Msg 1505 («The duplicate key value is (<NULL>)»). La via d'uscita è l'indice filtrato:

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

Niente indici su espressione: colonne calcolate
Una funzione sulla colonna del WHERE impedisce di scendere lungo l'indice. Sulle stesse 14 400 righe di un mese:

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

Quando l'espressione non si può riscrivere come intervallo, si indicizza una colonna calcolata:

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

SELECT id FROM dbo.pedidos WHERE LOWER(email) = 'u77@ejemplo.com';
-- Index Seek (ix_email_lower), senza nominare la colonna calcolata

Per creare quell'indice, e poi per scrivere nella tabella, la sessione ha bisogno di QUOTED_IDENTIFIER e ANSI_NULLS attivi; sqlcmd spegne il primo se non gli si passa -I, e allora fallisce con Msg 1934.

Columnstore, per ciò che aggrega
Un indice columnstore salva ogni colonna a parte e compressa. La stessa tabella ha occupato 35 520 kB con il suo indice cluster normale e 7 104 kB come CLUSTERED COLUMNSTORE. Un SELECT estado, SUM(total) … GROUP BY estado ha letto 4 440 pagine e usato 19 ms di CPU sulla prima, e 147 pagine e 2 ms sulla seconda. Per cercare una riga per chiave un indice normale resta migliore; la cosa abituale su una tabella di lavoro è aggiungerle un NONCLUSTERED COLUMNSTORE con le colonne che si aggregano (quello di quattro colonne ha occupato 3 128 kB).

Le chiavi esterne non si indicizzano da sole
InnoDB crea un indice per ogni chiave esterna; SQL Server no. Misurato: dopo cliente_id int REFERENCES dbo.clientes(id), la tabella figlia aveva solo l'indice della sua chiave primaria. Senza quell'indice, ogni DELETE nella tabella padre percorre tutta la tabella figlia per verificare il vincolo.

Frammentazione e FILLFACTOR

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

L'indice su cliente_id, con valori casuali, misurato passo per passo:

FILLFACTOR lascia spazio in ogni pagina perché gli inserimenti ci stiano senza spezzarla: costa spazio dal primo giorno e risparmia la frammentazione dopo. Resta salvato nell'indice (sys.indexes.fill_factor), quindi i REBUILD successivi lo ripetono. REORGANIZE compatta senza bloccare e si può interrompere; REBUILD rifà l'indice intero e, senza ONLINE, blocca la tabella finché dura.

Ricostruire senza fermare la tabella

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

ONLINE = ON è dell'edizione Enterprise (e della Developer, che è la stessa, e di Azure SQL): in Standard non esiste, e un REBUILD ferma le scritture finché dura —e anche le letture, se è l'indice cluster—. RESUMABLE = ON permette di metterlo in pausa e riprenderlo dopo, ed esige ONLINE = ON (senza, Msg 11438). E due casi in cui ONLINE = ON fallisce: con un indice spaziale sulla tabella e con ALTER INDEX ALL.

Niente indici invisibili: DISABLE
ALTER INDEX … DISABLE è la cosa più simile, ma non è lo stesso: cancella i dati dell'indice e ne tiene solo la definizione, quindi tornare indietro è un REBUILD completo. E disattivare l'indice cluster rende la tabella illeggibile: SELECT COUNT(*) dà Msg 8655, e di passaggio disattiva tutti gli indici non cluster, con un avviso per ciascuno.

Quelli che nessuno usa, e quelli che mancano

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

La cautela è maggiore che in PostgreSQL: questa vista vive in memoria e si svuota al riavvio del server. Misurato: ho riavviato il container e la vista è tornata senza una sola riga della tabella. Anche disattivare l'indice la azzera; un REBUILD no. Un indice che segna 0 letture di lunedì può essere uno che nessuno ha avuto il tempo di usare.

L'altra metà sono quelli che mancano: l'ottimizzatore annota in sys.dm_db_missing_index_details l'indice che avrebbe voluto. Dopo una query su cliente_id ed estado, ha proposto [cliente_id], [estado] con un impatto stimato del 99,6 %. È un indizio, non un ordine: non guarda gli indici che hai già, non pesa le scritture e vive nella stessa memoria che si svuota.

Raccomandazione
Una chiave cluster stretta e crescente; un indice per ogni chiave esterna usata per cercare o per cancellare; INCLUDE prima di un altro indice quando il piano mostra un Key Lookup; e prima di cancellare un indice «inutilizzato», guardare da quanto tempo è partito il server (sqlserver_start_time in sys.dm_os_sys_info).

Parole chiave: indice, indici, index, cluster, clustered, non cluster, nonclustered, heap, key lookup, include, indice filtrato, colonna calcolata, columnstore, fillfactor, frammentazione, reorganize, rebuild, online, resumable, disable, dm_db_index_usage_stats, dm_db_missing_index_details, dm_db_index_physical_stats

I limiti che si toccano davvero —8 060 byte per riga, 900 e 1 700 byte di chiave di indice, 1 024 colonne, 128 caratteri di nome, 2 100 parametri—, quali avvisano soltanto quando si crea la tabella e falliscono dopo, e quelli dell'edizione Express.

Si applica a: SQL Server 2022+

I limiti di SQL Server somigliano poco a quelli di InnoDB, e quelli che mordono ogni giorno non sono i grandi. Li ho provocati uno per uno contro SQL Server 2025.

Quelli che si toccano davvero

La riga sta in una pagina, tranne ciò che è variabile
Una pagina è di 8 KB e una riga deve starci: 8 060 byte. Con colonne a larghezza fissa il server lo controlla quando si crea la tabella: due char(5000) danno Msg 1701, che dice che la riga minima sarebbe di 10 007 byte.

Con colonne variabili no. Ho salvato due varchar(8000) pieni nella stessa riga —16 000 byte— senza alcun errore: ciò che non ci sta finisce in pagine a parte, ROW_OVERFLOW_DATA. sys.allocation_units ha mostrato 2 pagine di riga e 2 di overflow. Funziona, ma leggere quella riga costa una lettura in più per ogni colonna in overflow, e lo schema non avvisa che sta succedendo.

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

varchar(max), nvarchar(max) e varbinary(max) arrivano a 2 GB ed escono dalla riga per la stessa strada. Perché varchar(8001) non esiste e bisogna saltare a max lo racconta l'argomento sui tipi di dati.

Il limite che avvisa soltanto: la chiave di indice
È quello che sorprende di più. Un indice su una colonna più larga del limite viene creato, con un avviso:

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

Un avviso non ferma un rilascio. La tabella funziona fino al giorno in cui qualcuno salva un valore lungo: 900 byte sono entrati e 901 hanno dato Msg 1946. Su un indice non cluster il limite è di 1 700 byte, e con nvarchar sono 850 caratteri, non 1 700, perché ognuno occupa due byte. Una PRIMARY KEY su varchar(1000) è un indice cluster e si comporta allo stesso modo.

Se una colonna lunga ha bisogno di una ricerca per uguaglianza, ciò che funziona è indicizzare un riassunto: una colonna calcolata con CHECKSUM o HASHBYTES, indicizzata, confrontando anche il valore completo.

Nomi: un errore, non un troncamento
Un nome di 129 caratteri dà Msg 103 e l'istruzione non viene eseguita. PostgreSQL tronca a 63 byte e va avanti; SQL Server non lascia passare il nome. Quello di una tabella temporanea locale è più corto, 116, perché il server aggiunge un suffisso per distinguere le sessioni.

Parametri: 2 100, e 2 098 in pratica
Una procedura accetta 2 100 parametri e una chiamata non può passarne di più. Ma i driver e gli ORM inviano le query parametrizzate tramite sp_executesql, e lì contano anche l'istruzione e la dichiarazione: con 2 098 parametri ha funzionato e con 2 099 ha dato Msg 180. È l'errore tipico di un WHERE id IN (@p1, @p2, …) generato da una lista che un giorno cresce. La via d'uscita non è spezzare la lista: è passarla come tabella (un parametro con valori di tabella, o una temporanea) e fare un JOIN. Una IN con 60 000 letterali scritti nell'istruzione invece ha funzionato.

Più colonne: SPARSE con un set di colonne
1 025 colonne danno Msg 1702. Con colonne SPARSE e un COLUMN_SET FOR ALL_SPARSE_COLUMNS ho creato una tabella di 2 002 colonne; le stesse colonne senza il set hanno dato lo stesso Msg 1702. La documentazione la chiama «tabella larga» e porta il limite a 30 000 colonne, ma la riga deve comunque stare in 8 060 byte.

Annidamento: 32 livelli
Una procedura che chiama sé stessa è arrivata a @@NESTLEVEL 32 e il livello successivo ha dato Msg 217. Procedure, funzioni, trigger e viste contano insieme: un trigger che aggiorna un'altra tabella con trigger consuma livelli senza che si veda.

I grandi, che non sono quasi mai il problema
Non li ho misurati —il testbed è l'edizione Developer e misurarli richiederebbe di riempire un disco—; sono qui con la cifra della documentazione di Microsoft:

- Dimensione di un database: 524 272 TB. Di un file di dati: 16 TB.
- Righe per tabella: nessun limite oltre allo spazio di archiviazione.
- Dimensione di un batch: 65 536 volte la dimensione del pacchetto di rete (4 096 byte di default, cioè 256 MB).
- Connessioni: 32 767 (@@MAX_CONNECTIONS), e user connections a 0 significa che non c'è un altro tetto.

Quelli che mettono le edizioni
Qui ci sono limiti che si toccano, e vengono dalla licenza, non dal motore. Non ho misurato nemmeno questi: il testbed è Developer, che non ne ha nessuno.

- Express: 10 GB per database in SQL Server 2022 e 50 GB nel 2025; circa 1,4 GB di memoria per il buffer pool; il minore tra un socket e quattro core. Raggiunta la dimensione, il database smette di crescere e le scritture falliscono.
- Standard: nessun tetto alla dimensione del database, ma un tetto di memoria e di core, e senza alcune funzioni di Enterprise, come REBUILD con ONLINE = ON.

SELECT SERVERPROPERTY('Edition') dice qual è quella del server a cui sei connesso.

Raccomandazione
Tenerne d'occhio tre: la chiave di indice quando si indicizza una colonna di testo, perché avvisa soltanto; i 2 098 parametri quando qualcosa genera liste IN; e la dimensione del database se il server è Express. Del resto ci si accorge dal numero di errore, che dice sempre qual è il limite.

Parole chiave: limiti, massimo, 8060, riga, pagina, row_overflow_data, overflow, chiave di indice, 900 byte, 1700 byte, 1024 colonne, sparse, column_set, 128 caratteri, identificatore, tabella temporanea, 2100 parametri, sp_executesql, nestlevel, annidamento, express, 10 gb, 50 gb, edizione, msg 1946, msg 1702, msg 180

Perché qui una SELECT aspetta un UPDATE, cosa cambia READ_COMMITTED_SNAPSHOT, come si vede chi blocca chi, dove resta il grafo di un 1205 e quando i lock di riga diventano un lock di tabella.

Si applica a: SQL Server 2022+

Se vieni da MySQL o da PostgreSQL, questa è la prima cosa che spiazza: in SQL Server, per impostazione predefinita, una SELECT aspetta un UPDATE. InnoDB e PostgreSQL leggono l'ultima versione confermata della riga e vanno avanti; SQL Server, in READ COMMITTED e basta, chiede un lock condiviso e resta in attesa che l'altro confermi o annulli. Tutto ciò che racconto qui l'ho misurato contro SQL Server 2025, con due sessioni aperte insieme.

Una SELECT che aspetta

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

-- Sessione B
SELECT saldo FROM dbo.cuentas WHERE id = 1;

La sessione B è rimasta ferma 7,5 s, esattamente il tempo che A ha impiegato a fare ROLLBACK, in attesa di LCK_M_S sulla chiave di quella riga. E A non deve per forza fare qualcosa: una sessione che ha aperto una transazione, ha scritto e poi è rimasta ferma —status sleeping, open_transaction_count 1— blocca lo stesso. È il caso di tutti i giorni: un'applicazione che ha dimenticato il COMMIT.

READ_COMMITTED_SNAPSHOT: leggere come gli altri due

ALTER DATABASE mio_db SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

Con questa opzione, READ COMMITTED legge l'ultima versione confermata della riga, e la stessa lettura è tornata subito con il valore di prima (100, non il 50 non confermato). Tre cose prima di accenderla:
- È del database, non della sessione, e nasce spenta: in un database appena creato, in model e nella demo del mio laboratorio, is_read_committed_snapshot_on vale 0.
- Senza WITH ROLLBACK IMMEDIATE, l'ALTER aspetta che non resti nessun'altra sessione nel database: con una collegata, ha aspettato 14,5 s, il tempo che se ne andasse. Con la clausola, butta fuori le altre e annulla le loro transazioni.
- Cambia solo chi legge. Due scritture sulla stessa riga continuano ad aspettarsi: l'UPDATE della sessione B ha aspettato come prima.

Chi blocca chi
Si chiede mentre il blocco dura:

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

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

La prima risponde alla domanda che ci si fa davvero: blocking_session_id è la sessione che trattiene ciò che questa chiede. La seconda mostra tutta la scala: chi aggiorna una riga tiene X sulla chiave e IX sulla pagina e sulla tabella, e chi aspetta compare con request_status WAIT. A differenza di PostgreSQL, qui i lock di riga compaiono nell'elenco, uno per riga.

L'elenco dei Processi di Calíope mostra l'attesa nella colonna Stato —una sessione bloccata compare con LCK_M_S, LCK_M_U o LCK_M_X—, ma non chi la blocca: quello lo dà la prima query. E Kill Connection è qui l'unico modo di liberare il lock di un altro: KILL chiude l'intera sessione e annulla la sua transazione, perché SQL Server non permette di annullare solo la query di un'altra sessione.

UPDLOCK, il FOR UPDATE di qui
Non esiste SELECT … FOR UPDATE. Quel lavoro lo fa un hint:

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

Prende un lock U: una lettura normale di un'altra sessione è passata senza aspettare, e un'altra lettura con UPDLOCK è rimasta in attesa. Ed evita il deadlock più sciocco di tutti: due sessioni in REPEATABLE READ che leggono la stessa riga e poi la aggiornano sono finite in un 1205; con UPDLOCK sulla lettura, sono arrivate in fondo tutte e due, una dopo l'altra.

Anche qui si aspetta per sempre
@@LOCK_TIMEOUT vale -1, cioè senza limite: come il lock_timeout di PostgreSQL, e non come i 50 s di InnoDB. Si imposta per sessione, in millisecondi:

SET LOCK_TIMEOUT 3000;

Quando scatta arriva un Msg 1222, e non annulla nulla: la transazione resta aperta e chi lo riceve deve annullarla da sé. Cosa annulla ogni errore lo racconta l'argomento «Errori (SQL Server)».

Non aspettare, apposta

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

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

NOWAIT fallisce subito con lo stesso 1222, e il batch prosegue. READPAST è lo SKIP LOCKED di qui: con la riga 3 bloccata da un'altra sessione, ha restituito 1, 2, 4 e 5. È il modo di distribuire una coda di lavoro fra più consumatori senza che si aspettino.

NOLOCK legge ciò che non è mai esistito

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

Con un'altra sessione a metà di un UPDATE poi annullato, quella lettura ha restituito 50: un saldo mai confermato. NOLOCK è READ UNCOMMITTED, e non è «leggere più in fretta»: è leggere ciò che un'altra transazione può ancora buttare via. La documentazione aggiunge che, se le pagine si dividono durante la lettura, può saltare righe o leggerle due volte; questo non l'ho riprodotto. Se ciò che si cerca è che leggere non aspetti, la risposta è READ_COMMITTED_SNAPSHOT, che non aspetta e vede solo ciò che è confermato.

L'abbraccio mortale

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

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

Una delle due riceve Msg 1205 —livello 13, «… has been chosen as the deadlock victim. Rerun the transaction.»— e perde la sua transazione e il resto del batch; l'altra conferma. Non viene rilevato all'istante: in sei prove, la vittima ha impiegato fra 0,1 e 4,8 s a cadere da quando il ciclo si era chiuso. La documentazione spiega quella forbice: un monitor cerca cicli ogni 5 s, e più spesso subito dopo averne trovato uno.

Quale muore si può decidere. Con SET DEADLOCK_PRIORITY HIGH nella sessione A, la vittima è stata B tutte e tre le volte; LOW è ciò che conviene a un processo batch ripetibile. Un 1205 non è un guasto: l'applicazione deve ritentare quella transazione.

Il grafo è già salvato
Non c'è niente da accendere: la sessione Extended Events system_health, attiva di serie, conserva ogni deadlock con il suo grafo —chi è morto, quale istruzione stava eseguendo ciascuno e quale chiave di quale indice aspettava.

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

Due cose misurate. Il file è in ritardo: subito dopo averne provocati sei, ne aveva due; pochi secondi dopo, tutti. E il ring_buffer della stessa sessione, che li ha subito, vive in memoria: dopo un riavvio del server aveva i nuovi e aveva perso quello di prima, che il file conservava ancora.

Dalla riga alla tabella: l'escalation
Ogni lock occupa memoria, e quando un'istruzione ne accumula troppi su una tabella, SQL Server li sostituisce con uno solo sull'intera tabella. Ho aggiornato le prime N righe di una tabella di 20.000 e ho contato:
- Fino a 6.200 righe, 6.200 lock X di chiave.
- Da 6.300, uno: X sulla tabella, senza passare dalla pagina. E un'altra sessione che andava alla riga 20.000, che nessuno aveva toccato, ha aspettato fino al suo LOCK_TIMEOUT.

La cifra della documentazione è 5.000; qui il salto è arrivato un po' più tardi. Una SELECT di 8.000 righe in REPEATABLE READ è finita allo stesso modo, con un S sulla tabella. Si controlla per tabella:

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

Così lo stesso UPDATE di tutte le 20.000 righe ha tenuto 20.000 lock di chiave e nessuno di tabella. TABLE è il valore predefinito; AUTO, secondo la documentazione, fa escalation sulla partizione in una tabella partizionata. Spegnerla non è gratis —ogni lock è memoria del server—, e la via d'uscita abituale è un'altra: scrivere a blocchi di meno di 5.000 righe.

Lock applicativi
L'equivalente degli advisory lock di PostgreSQL: un nome che il server tiene perché due processi della tua applicazione non facciano la stessa cosa insieme.

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

Con la risorsa in mano a un'altra sessione ha restituito -1 invece di aspettare; 0 significa «concesso». Non solleva alcun errore: bisogna guardare il numero. Con @LockOwner = 'Transaction', il valore predefinito, si libera da solo alla fine della transazione, e chiederlo fuori da una transazione ha restituito -999.

Raccomandazione
Toccare le righe sempre nello stesso ordine e tenere le transazioni brevi, come con qualsiasi motore. Ciò che è proprio di qui sono tre cose: accendere READ_COMMITTED_SNAPSHOT appena l'applicazione lo tollera, perché è ciò che fa smettere alla lettura di aspettare la scrittura e toglie la tentazione di NOLOCK; UPDLOCK sulla lettura che finirà in un UPDATE; e il nuovo tentativo del 1205 scritto prima di andare in produzione.

Parole chiave: lock, blocco, deadlock, stallo, 1205, 1222, lck_m_s, blocking_session_id, dm_tran_locks, dm_exec_requests, read_committed_snapshot, rcsi, nolock, readpast, updlock, nowait, lock_timeout, deadlock_priority, escalation, lock_escalation, system_health, sp_getapplock

Il DDL è transazionale e ciò che costa è il lock Sch-M e la coda che forma, quali ALTER riscrivono la tabella, ONLINE, RESUMABLE e WAIT_AT_LOW_PRIORITY, e perché qui WITH NOCHECK non alleggerisce il lock.

Si applica a: SQL Server 2022+

Se vieni da MySQL, prima di tutto: qui il DDL è transazionale, come in PostgreSQL. Se vieni da PostgreSQL, cambiano il nome del lock e un paio di strumenti che lì non ci sono. Tutto ciò che racconto qui l'ho misurato contro SQL Server 2025, su una tabella di 200.000 righe.

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

Dopo quel ROLLBACK non restava niente: né la colonna, né l'indice, né la tabella, e la colonna si chiamava di nuovo nota. Un'intera migrazione sta in una transazione. Le eccezioni che ho trovato: CREATE DATABASE e ALTER DATABASE (Msg 226) e un indice RESUMABLE (Msg 574) non possono starci dentro.

Ciò che costa è il Sch-M, e la coda che forma
Ogni ALTER TABLE chiede un lock di modifica dello schema, Sch-M, che è incompatibile con tutto, SELECT compresa. Il pericolo non è l'ALTER, che può durare millisecondi: è l'attesa. Con un'altra sessione a metà di una transazione che aveva toccato una riga:
- L'ALTER TABLE … ADD ha aspettato 7 s, con wait_type LCK_M_SCH_M.
- Una SELECT di un'altra riga, lanciata dietro, ha aspettato 6 s con LCK_M_SCH_S, e il suo blocking_session_id non era la transazione: era l'ALTER.

Così una modifica di un millisecondo ferma un'intera applicazione. Il rimedio è lo stesso di PostgreSQL:

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

Dopo 3 s l'ALTER ha ricevuto Msg 1222, e la SELECT dietro è passata nello stesso istante. Si riprova più tardi.

Cosa riscrive la tabella e cosa no
Misurato su 200.000 righe (1.364 pagine). Il testimone è il log delle transazioni che ogni istruzione ha scritto:

Come leggerla:
- Un valore predefinito che è lo stesso per tutte le righe —una costante, o SYSDATETIME(), che viene valutata una volta— finisce nei metadati e non tocca le righe. NEWID() ne dà uno diverso a ogni riga, e questo obbliga a scriverle tutte.
- Allargare un varchar è gratis. Restringerlo, cambiargli famiglia (nvarchar, max), ingrandire un intero, passare a NOT NULL o cambiare la dimensione su disco di un decimal riscrivono. decimal(10,2) e decimal(12,2) occupano lo stesso spazio, 9 byte, ed è per questo che quello non ha riscritto niente.
- Ciò che riscrive lascia la tabella grande il doppio. Dopo ognuna di quelle istruzioni la tabella è passata da 1.364 a 2.723 pagine e lì è rimasta: la colonna vecchia continua a occupare il suo posto in ogni riga. Dopo int → bigint, un ALTER TABLE dbo.pedidos REBUILD l'ha riportata a 1.495. E un DROP COLUMN è istantaneo perché non libera nulla: 1.364 pagine prima e dopo, e 1.075 dopo il REBUILD.

Due ostacoli che PostgreSQL non mette:
- Una colonna indicizzata non cambia tipo. Con un indice su estado, passare a varchar(100) ha funzionato, perché non riscrive; varchar(30) e nvarchar(100) hanno dato Msg 5074 («The index 'ix_estado' is dependent on column 'estado'»). Bisogna eliminare l'indice, cambiare la colonna e ricrearlo.
- Un DEFAULT è un vincolo con un nome, e finché esiste la colonna non si può eliminare: lo stesso Msg 5074, che nomina un vincolo DF__pedidos__moneda__… battezzato da SQL Server da solo. Si elimina prima con ALTER TABLE … DROP CONSTRAINT, ed è per questo che conviene dargli un nome quando lo si crea.

La documentazione aggiunge una condizione di edizione: che un ADD COLUMN … NOT NULL DEFAULT tocchi solo i metadati è una funzione di Enterprise; in Standard riscrive la tabella. Il mio laboratorio è Developer, che ha tutto ciò che ha Enterprise, quindi questo non l'ho potuto misurare.

Indici online: ONLINE = ON

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

Su 3.000.000 di righe la ricostruzione online è durata 47 s, e un UPDATE lanciato a metà è finito in mezzo secondo. Senza ONLINE, la tabella resta bloccata per tutto quel tempo. Anche un ALTER COLUMN lo accetta (ALTER TABLE … ALTER COLUMN n bigint NOT NULL WITH (ONLINE = ON)): ci ha messo 395 ms invece di 228, ma ha lasciato la tabella a 1.483 pagine invece di raddoppiarla.

Tre limiti:
- Con un indice spaziale sulla tabella, ALTER INDEX ALL … WITH (ONLINE = ON) fallisce con Msg 153, e nemmeno l'indice spaziale stesso si ricostruisce online. La chiave primaria e gli indici normali, uno alla volta, sì.
- ONLINE non vuol dire senza lock: all'inizio chiede un lock S sulla tabella e, secondo la documentazione, un breve Sch-M alla fine. Con una transazione aperta sulla tabella, il REBUILD online è rimasto ad aspettare il suo S, e un UPDATE arrivato dietro ha aspettato 6,6 s in coda (LCK_M_IX). Una SELECT invece è passata.
- Secondo la documentazione, ONLINE = ON è di Enterprise: in Standard dà errore.

WAIT_AT_LOW_PRIORITY: aspettare senza formare coda
Questo PostgreSQL non ce l'ha. La ricostruzione aspetta il suo lock in una coda a parte, senza intralciare chi arriva dietro:

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

Con la stessa transazione aperta, l'UPDATE dietro è passato in 0,5 s, e il REBUILD risultava in attesa su LCK_M_S_LOW_PRIORITY. Trascorso MAX_DURATION, decide ABORT_AFTER_WAIT:
- SELF: il REBUILD rinuncia con Msg 1222 e la transazione va avanti. È la scelta prudente.
- BLOCKERS: dopo il minuto, la sessione che bloccava è stata disconnessa («… disconnected because of a high priority DDL operation») e la sua transazione annullata; il REBUILD è arrivato in fondo.
- NONE: aspetta a bassa priorità senza limite.

La accetta ALTER INDEX … REBUILD e, sul mio 2025, anche CREATE INDEX … WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY …)). ALTER TABLE no: con ADD, ALTER COLUMN o ADD CONSTRAINT è un errore di sintassi (Msg 155). Per quelli resta solo LOCK_TIMEOUT.

Indici ripristinabili: RESUMABLE = ON

CREATE INDEX ix_relleno ON dbo.grande (relleno, g) WITH (ONLINE = ON, RESUMABLE = ON);
-- da un'altra sessione:
ALTER INDEX ix_relleno ON dbo.grande PAUSE;
SELECT name, state_desc, percent_complete FROM sys.index_resumable_operations;
ALTER INDEX ix_relleno ON dbo.grande RESUME;

Ho messo in pausa la creazione dopo 4 s: la sessione che l'aveva lanciata è stata disconnessa (Msg 1219), sys.index_resumable_operations diceva PAUSED e 5 %, e l'indice non esisteva ancora —non compariva in sys.indexes—. RESUME ha finito il resto in 45 s. Serve a distribuire un indice enorme su più finestre di manutenzione. Richiede ONLINE = ON (senza, Msg 11438), non può stare in una transazione (Msg 574) e, secondo la documentazione, da SQL Server 2022 vale anche per ALTER TABLE … ADD CONSTRAINT di una chiave primaria o univoca; su 2025 l'ho verificato con una univoca.

WITH NOCHECK: qui non alleggerisce il lock
Lo schema NOT VALID + VALIDATE di PostgreSQL qui ha il suo gemello, ma non fa la stessa cosa. Su 3.000.000 di righe:

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

Aggiungerlo in un colpo solo ha richiesto 375 ms. E tutti e tre hanno preso Sch-M sulla tabella, validazione compresa: dividere in due distribuisce la scansione, ma per la seconda metà non c'è un lock più debole, come invece c'è in PostgreSQL.

E lascia una trappola: un vincolo aggiunto WITH NOCHECK resta non attendibile (is_not_trusted = 1), e l'ottimizzatore non si fida di lui. Con un CHECK (importe >= 0) attendibile, WHERE importe < 0 non ha nemmeno letto la tabella; non attendibile, 1.364 letture logiche. Succede lo stesso disattivandolo e riattivandolo senza WITH CHECK —ALTER TABLE … CHECK CONSTRAINT ck_importe l'ha lasciato non attendibile—, che è ciò che fanno spesso i caricamenti massivi. Per trovarli:

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

In Calíope
L'operazione OPTIMIZE di Manutenzione tabelle è, in SQL Server, un ALTER INDEX ALL … REBUILD senza ONLINE: blocca la tabella mentre ricostruisce. Su una tabella grande in produzione, è meglio scrivere nell'editor il REBUILD WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY …)).

Raccomandazione
SET LOCK_TIMEOUT prima di ogni ALTER TABLE; la migrazione dentro una transazione; ONLINE = ON con WAIT_AT_LOW_PRIORITY … SELF per gli indici; un nome proprio per ogni DEFAULT; e attenzione ai cambi di tipo, che riscrivono la tabella e la lasciano grande il doppio fino al prossimo REBUILD. Se devi restringere una colonna grande o cambiarle famiglia, di solito conviene aggiungere quella nuova, copiare a blocchi e rinominare con sp_rename.

Parole chiave: ddl, alter table, migrazione, transazionale, rollback, sch-m, lck_m_sch_m, lock_timeout, online, resumable, wait_at_low_priority, abort_after_wait, rebuild, riscrittura, add column, alter column, drop column, with nocheck, is_not_trusted, sp_rename

Funzione e schema di partizione, cosa elimina davvero l'ottimizzatore, perché un indice univoco deve contenere la chiave, SWITCH e TRUNCATE per eliminare dati in pochi millisecondi, il CHECK attendibile che serve per caricare dati, e SPLIT e MERGE, gratuiti solo su una partizione vuota.

Si applica a: SQL Server 2022+

Se vieni da MySQL o da PostgreSQL, cambia la forma: qui una partizione non è una tabella ma un pezzo di tabella, e la suddivisione la descrivono due oggetti creati prima della tabella. Tutto quello che racconto qui l'ho misurato su SQL Server 2025, con 300.000 vendite distribuite su tre anni.

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

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

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

La funzione fissa i confini e lo schema dice in quale filegroup vive ogni pezzo. Tre confini danno quattro partizioni: tutto ciò che precede il 2024, un anno in ciascuna delle due successive, e tutto dal 2026 in poi. Non ci sono LIST né HASH: solo intervalli. E non serve prevedere il futuro: ciò che cade prima del primo confine o dopo l'ultimo ha sempre un posto.

RANGE RIGHT o RANGE LEFT: da che parte cade il confine
Con RIGHT, il confine appartiene alla partizione alla sua destra, ed è la scelta naturale con le date: $PARTITION.pf_anio('2023-12-31') ha restituito 1, e '2024-01-01' 2. Con LEFT e gli stessi confini, il 1° gennaio è finito nella partizione del 2023. Non fallisce niente: i dati di un giorno finiscono semplicemente nell'anno sbagliato. $PARTITION serve anche per contare:

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

L'eliminazione delle partizioni è ciò che si cerca
Il piano effettivo (SET STATISTICS XML ON, in RunTimePartitionSummary) dice quante partizioni ha letto ogni query:

Due cose da leggere lì:
- Anche con variabili e parametri l'eliminazione avviene, perché si decide in esecuzione, non in compilazione. Per questo il piano stimato non dà un numero: mostra [PtnId1000] >= RangePartitionNew(…) quando la chiave è nel filtro, e [PtnId1000] >= (1) AND [PtnId1000] <= (4) quando non c'è.
- Una funzione sulla colonna la nasconde, come la nasconde a un indice: YEAR(creado) ha letto tutte e quattro. L'intervallo scritto a mano (>= '2024-01-01' AND < '2025-01-01') ne legge una.

La regola di progetto è la stessa degli altri motori: la chiave di partizione è la colonna su cui filtri quasi sempre. Se le query non la nominano, partizionare non risparmia letture: le distribuisce.

Indici allineati, e l'unicità
Un indice creato senza ON eredita lo schema della tabella: è allineato, con un pezzo per partizione. La conseguenza è quella di PostgreSQL:

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

ha restituito Msg 1908: la colonna di partizione deve stare nella chiave di un indice univoco. Qui c'è una via d'uscita che PostgreSQL non ha —crearlo non allineato, ON [PRIMARY]—, ma ha un prezzo: con un indice così, lo SWITCH qui sotto smette di funzionare (Msg 7733). Per questo la chiave primaria di dbo.ventas è (creado, id) e non (id).

SWITCH e TRUNCATE: eliminare un anno in pochi millisecondi
Cancellare tutto il 2023, 100.009 righe, misurato dentro una transazione:

SWITCH non sposta dati: cambia proprietario a un pezzo intero. Per questo chiede molto alla tabella di destinazione, e ogni requisito ha il suo errore:
- Vuota: con una riga, Msg 4905.
- Stesse colonne e stessi indici: con una colonna in più, Msg 4943.
- E, secondo la documentazione, nello stesso filegroup della partizione.

Per caricare dati serve un CHECK attendibile
Ecco la differenza con PostgreSQL, dove il CHECK risparmia solo una scansione. Rimettere la tabella nella sua partizione senza di esso non richiede più tempo: viene rifiutato, con Msg 4982. Con il vincolo, è entrata in 9,5 ms:

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

E deve essere attendibile: disattivato e riattivato senza WITH CHECK —quello che fanno molti caricamenti massivi—, lo SWITCH ha restituito Msg 4972. Dopo WITH CHECK CHECK CONSTRAINT, è entrata. Così si carica un periodo nuovo senza toccare la tabella viva: si riempie una tabella a parte, le si mette il suo CHECK e si fa lo SWITCH.

SPLIT e MERGE: gratuiti solo su una partizione vuota
I confini si spostano con la funzione:

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

Dividere una partizione piena sposta le righe da un lato del confine, e ognuna scrive nel log: il costo cresce con ciò che si sposta. L'abitudine sana è avere sempre una partizione vuota in fondo e dividerla prima che arrivino i dati del periodo successivo. E ogni SPLIT consuma il NEXT USED: senza dichiararlo di nuovo, il successivo ha restituito Msg 7710 e non ha cambiato niente.

Manutenzione per partizione
Si ricostruisce solo la partizione che cambia:

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

33 ms per una partizione (148 ms online) contro 214 ms per tutte e quattro. Un anno chiuso non riceve più scritture: non c'è motivo di ricostruirlo ogni notte.

L'escalation dei lock, per partizione
Di default (LOCK_ESCALATION = TABLE), un UPDATE di 16.440 righe del 2024 è salito a un lock X su tutta la tabella, e aggiornare una riga del 2025 da un'altra sessione ha restituito Msg 1222. Con

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

l'escalation si è fermata alla partizione (un X su HOBT), e la riga del 2025 è stata aggiornata in 9 ms. La documentazione avverte che AUTO può portare deadlock tra sessioni che salgono su partizioni diverse; il resto dell'escalation è in «Lock e deadlock (SQL Server)».

Anche SWITCH prende Sch-M
Con una transazione aperta che aveva toccato una sola riga, lo SWITCH è rimasto in attesa del suo Sch-M, e una SELECT di un'altra riga, lanciata dopo, ha aspettato in coda dietro lo SWITCH. Il rimedio è quello degli indici:

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

La SELECT dietro è terminata in mezzo secondo, e lo SWITCH ha rinunciato dopo 61 s con Msg 1222, senza toccare la transazione. Il resto di quella coda è in «Modifiche di schema a caldo (SQL Server)».

Limiti ed edizioni
Una funzione ammette 15.000 partizioni: con 15.000 confini, Msg 7719. Secondo la documentazione, il partizionamento è in tutte le edizioni da SQL Server 2016 SP1, Express compresa.

In Calíope
Le righe e la dimensione di una tabella partizionata sommano tutte le sue partizioni. Il CREATE TABLE che scrive Calíope —quello che mostra come DDL della tabella e quello che salva Backup— è preceduto dalla funzione e dallo schema di partizione, ciascuno con IF NOT EXISTS, e termina con ON ps_anio (creado); un indice non allineato, come una chiave primaria non cluster su [PRIMARY], conserva il proprio ON. Ripristinare quel backup in un database vuoto lo lascia partizionato allo stesso modo, con ogni riga nella sua partizione; se la destinazione ha già una funzione con quel nome, si usa quella che c'è, con i suoi limiti. Confronta schemi guarda eccome il partizionamento —dove sta la tabella e i limiti di ogni schema—, ma il suo script non partiziona né toglie le partizioni a una tabella che esiste già: vorrebbe dire ricostruire il suo indice cluster con i dati dentro, e lo lascia scritto come nota perché lo faccia tu. Il piano che mostra Calíope è quello stimato, quindi per le partizioni dà la forma PtnId1000 vista sopra; quante ne ha lette davvero lo dice solo il piano effettivo, SET STATISTICS XML ON.

Raccomandazione
Partiziona in base a ciò che cancellerai: il vero guadagno è eliminare un periodo con SWITCH o TRUNCATE … WITH (PARTITIONS) invece di un DELETE che riempie il log. Chiave primaria e indici univoci con dentro la chiave di partizione, perché tutto resti allineato; RANGE RIGHT con le date; sempre una partizione vuota in fondo, per dividerla gratis; e verifica nel piano effettivo che le tue query eliminino partizioni, invece di darlo per scontato.

Parole chiave: partizionamento, partition, partition function, partition scheme, range right, range left, $partition, eliminazione delle partizioni, allineato, switch, truncate, split range, merge range, next used, lock_escalation, 1908, 4982, 7733

Letture logiche invece dei tempi, il piano stimato contro quello reale, il parameter sniffing e i suoi rimedi misurati, la cache dei piani che legge Query lente, Query Store acceso di fabbrica e le attese che dicono davvero qualcosa.

Si applica a: SQL Server 2022+

Se vieni da MySQL o da PostgreSQL, qui cambiano tre cose: il numero che si confronta sono le letture logiche, non il tempo; il piano viene messo in cache e riutilizzato con altri valori, ed è la causa di metà dei «ieri andava bene»; e l'equivalente di pg_stat_statements c'è già senza accendere niente, ma si perde al riavvio. Tutto quello che racconto qui l'ho misurato su SQL Server 2025, con 200.000 ordini.

Misurare una query: le letture logiche

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

Ogni lettura logica è una pagina da 8 kB letta dalla memoria. È il numero da confrontare, perché il tempo cambia con il carico e con la cache, e lei no. Per i 20 ordini di un cliente:
- Senza indice: 6.085 letture logiche —la tabella intera— e 12 ms.
- Con un indice su cliente_id: 62 letture.

In Calíope quelle righe non compaiono: il server le manda come messaggi informativi, e l'editor mostra righe ed errori, non messaggi. Lo stesso numero si legge dalla cache dei piani, subito dopo aver eseguito la query:

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

Mi ha restituito 62, come STATISTICS IO.

Il piano stimato e quello reale
SET SHOWPLAN_XML ON chiede il piano senza eseguire la query. SET STATISTICS XML ON la esegue e aggiunge quello che è successo: ActualRows accanto a EstimateRows su ogni operatore. Se l'istruzione scrive, si avvolge in BEGIN TRAN … ROLLBACK, perché viene eseguita davvero.

Visual EXPLAIN, in Calíope, chiede quello stimato: l'albero degli operatori con le righe stimate, senza righe reali, senza tempi e senza costo. Serve a vedere la forma, un Index Scan dove ti aspettavi un Seek; per confrontare la stima con la realtà serve il piano reale.

Il parameter sniffing
La prima volta che una procedura o una query parametrizzata viene eseguita, l'ottimizzatore guarda il valore che porta e compila il piano per quello. Quel piano resta in cache, e le esecuzioni successive lo riutilizzano anche quando portano un altro valore. L'ho riprodotto con una procedura e due clienti: uno con 20 ordini e uno con 100.000.

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

Compilato per il cliente piccolo, il piano cerca nell'indice e va alla tabella riga per riga: perfetto per 20 righe, e 300.000 letture per 100.000, cinquanta volte più che scorrere la tabella intera. Compilato al contrario, il piccolo scorre la tabella intera. Decide l'ordine di arrivo, e un riavvio o un cambio di statistiche lo inverte: da qui il «ieri andava bene».

Il piano reale lo tradisce senza indovinare: nel caso cattivo diceva EstimateRows="20" e ActualRows="100000", e tra i suoi parametri ParameterCompiledValue="(4243)" accanto a ParameterRuntimeValue="(1)".

Quello che ho provato per curarlo:
- OPTION (RECOMPILE) in fondo alla query: 62 e 6.085, ogni valore con il suo piano. Il prezzo è compilare a ogni chiamata (qui 2-3 ms), che per una query eseguita migliaia di volte al secondo non è gratis.
- OPTION (OPTIMIZE FOR UNKNOWN): compila per il valore medio, circa 20 righe, e ha dato 70 e 306.429. Quando c'è un valore enorme, non aiuta.
- EXEC sp_recompile 'dbo.pedidos_de' butta via il piano della procedura: è il rimedio del momento, non la cura.
- Da SQL Server 2022, con livello di compatibilità 160 o superiore, il server può tenere più piani per la stessa query a seconda del valore. È acceso di default, ma con 100.000 righe contro 20 non è intervenuto. Con 200.000 contro 1, sì: due piani. Non contarci.

Quale query costa di più: la cache dei piani
Il server tiene il conto di ogni piano che ha in cache: esecuzioni, tempo, letture e righe. query_hash raggruppa per la forma della query: cinque SELECT che cambiavano solo il numero di cliente, ognuno da solo, hanno lasciato cinque piani in cache (80 kB) e un solo query_hash.

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

È quello che Calíope mostra in Query lente, vista Per istruzione: una riga per forma, con il testo del suo piano più caro, da tutti i database del server. Serve il permesso VIEW SERVER STATE. E non tiene storia: ho riavviato il server e la vista è passata da 23 righe a 0. Né sopravvive all'uscita del piano dalla cache.

Query Store, che è già acceso
Query Store è la versione che resta: conserva testo, piani e numeri per intervalli dentro il database stesso. In SQL Server 2025 l'ho trovato acceso (READ_WRITE) in demo e in un database appena creato, senza che nessuno l'avesse chiesto. Secondo la documentazione, è così per i database nuovi da SQL Server 2022.

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

Una colonna planes maggiore di 1 è l'impronta del parameter sniffing. Due sfumature misurate: dopo il riavvio c'erano ancora i testi delle sue 11 query, ma non i numeri di prima, perché li scrive su disco ogni 900 secondi; ed è per database, non per server.

Calíope non lo usa: Query lente legge la cache dei piani, che c'è già per tutto il server. Non lo accende né lo spegne, perché è un ALTER DATABASE di amministrazione.

Le attese: dove va il tempo
sys.dm_os_wait_stats somma, dall'ultimo riavvio, quanto ha aspettato il server e perché. Ha 1.547 tipi, e i primi della lista, senza filtro, sono thread che aspettano lavoro: DISPATCHER_QUEUE_SEMAPHORE, BROKER_TASK_STOP, i SLEEP_…. Vanno scartati. Quelli che dicono davvero qualcosa:
- LCK_M_…: lock. Ne parlo in «Lock e deadlock (SQL Server)».
- PAGEIOLATCH_…: attesa di pagine dal disco. Manca memoria o manca un indice.
- WRITELOG: attesa del log delle transazioni a ogni commit.
- CXPACKET e CXCONSUMER: parallelismo.
- SOS_SCHEDULER_YIELD: CPU.
- RESOURCE_SEMAPHORE: query che aspettano memoria per ordinare o per un hash.

Sono cumulativi, quindi una foto sola serve a poco: se ne prendono due e si sottrae. Dopo il mio riavvio, la somma è scesa da 12,1 a 0,7 milioni di ms.

Parallelismo
Di fabbrica, cost threshold for parallelism vale 5 e max degree of parallelism 0 (tutti i core). La soglia è così bassa che una scansione media può già andare in parallelo: su un'altra tabella da 200.000 righe, una ha usato 16 thread, 2.094 ms di CPU per 152 ms di orologio. Un CXPACKET alto non è un errore; è il segno che quei due valori sono rimasti come sono arrivati.

Quello che è già in altri argomenti
- Un parametro nvarchar contro una colonna varchar con collazione SQL_ passa da 3 letture a 334: «Codifica e collazioni (SQL Server)».
- Key Lookup, quando il piano lascia l'indice per la tabella, YEAR(fecha) contro l'intervallo, e gli indici che al server mancano: «Indici (SQL Server)».
- Un vincolo CHECK non attendibile l'ottimizzatore non lo usa (1.364 letture contro nessuna): «Modifiche di schema a caldo (SQL Server)».
- Quante partizioni ha letto una query lo dice solo il piano reale: «Partizionamento (SQL Server)».

Raccomandazione
L'ordine che funziona: trovare la query in Query lente (o in Query Store, se serve la storia), chiedere il suo piano reale con SET STATISTICS XML ON e confrontare EstimateRows con ActualRows. Se divergono e il valore compilato non è quello di adesso, è parameter sniffing; se divergono con lo stesso valore, sono le statistiche; se coincidono e si legge comunque molto, manca un indice. Toccare la memoria o il parallelismo prima di aver letto un piano è la strada lunga.

Parole chiave: prestazioni, statistics io, statistics time, letture logiche, showplan_xml, statistics xml, piano di esecuzione, piano reale, parameter sniffing, rilevamento dei parametri, recompile, optimize for unknown, sp_recompile, dm_exec_query_stats, query_hash, cache dei piani, query store, dm_os_wait_stats, attese, parallelismo

I tre modelli di recupero e quale lascia crescere il log, BACKUP DATABASE e BACKUP LOG sul disco del server, la catena del log, tornare al minuto prima del DELETE con STOPAT, COPY_ONLY, e in cosa si distingue il backup di Calíope.

Si applica a: SQL Server 2022+

Se vieni da MySQL, qui non ci sono né mysqldump né un binlog a parte: il backup fisico è un'istruzione T-SQL, e la storia che permette di andare avanti da lì è il log delle transazioni di ogni database. Se vieni da PostgreSQL, la differenza è l'unità: si fa il backup e il ripristino di un database, non del server intero. Tutto quello che racconto qui l'ho misurato su SQL Server 2025.

Il modello di recupero decide quasi tutto
È una proprietà di ogni database, e ce ne sono tre:

Un database nuovo copia il modello di model, che su questo server è FULL:

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases;

FULL senza backup del log riempie il disco
Ecco la trappola. Finché il database non ha il suo primo backup completo, FULL si comporta come SIMPLE: ho inserito 20 000 righe da 2 kB, il log è arrivato a 42,2 MB usati e un CHECKPOINT l'ha riportato a 8,1 MB. Dopo il primo BACKUP DATABASE, la stessa cosa non ha più liberato nulla: 55,4 MB, poi 102,8 MB, e il file è cresciuto da 72 a 136 MB. log_reuse_wait_desc dice perché non viene riutilizzato:

- LOG_BACKUP — aspetta un BACKUP LOG. È il motivo di quasi tutti i dischi pieni.
- ACTIVE_TRANSACTION — una transazione aperta che nessuno chiude.
- CHECKPOINT — niente da temere, si libera al prossimo.

BACKUP LOG ha riportato lo spazio usato a 15,1 MB, ma il file è rimasto a 136 MB: fare il backup del log libera spazio al suo interno, non lo restituisce al disco. Se un database è in FULL e nessuno pianifica il BACKUP LOG, o lo si pianifica o si passa a SIMPLE.

Fare il backup

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

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

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

Il percorso è sul disco del server, non sul tuo: il file lo scrive il processo di SQL Server. Su Linux la cartella abituale è /var/opt/mssql/backup/; nel container di prova non esisteva, e il primo BACKUP l'ha creata.

- Il completo copia l'intero database: 150 MB nella mia prova.
- Il differenziale copia ciò che è cambiato dall'ultimo completo: 3,1 MB dopo aver modificato 2 000 righe.
- Quello del log copia il log dal precedente, ed è quello che permette di arrivare a un istante.

CHECKSUM non è attivo di default (backup checksum default vale 0), e si nota in verifica: RESTORE VERIFYONLY … WITH CHECKSUM su un backup fatto senza ha dato Msg 3187. Mettilo sempre, e verifica:

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

La catena del log
Ogni backup del log comincia dove è finito il precedente, e si ripristinano tutti, in ordine. Saltarne uno ha dato Msg 4305, con l'LSN mancante. Due cose la spezzano senza avvisare:

- Passare a SIMPLE e tornare a FULL: dopo, BACKUP LOG ha dato Msg 4214 fino al completo successivo, anche se ce n'era uno di prima.
- Un BACKUP LOG fatto da qualcun altro, verso un file che non conosci. La cronologia è in msdb:

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

COPY_ONLY: la copia che non disturba
Un completo normale diventa la base dei differenziali successivi. Se ne fai uno isolato —per portare il database su un'altra macchina—, il differenziale di stanotte non combacia più con il completo di domenica. Con WITH COPY_ONLY non succede: il differenziale successivo ha continuato a puntare al completo precedente, e ripristinarlo sopra il COPY_ONLY ha dato Msg 3136. Ogni copia fuori dalla routine, con COPY_ONLY.

Tornare al minuto prima del DELETE
L'ho riprodotto per intero: 500 ordini, un DELETE senza WHERE quattro secondi dopo, e il recupero in un database nuovo, senza toccare l'originale:

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

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

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

Sono tornati tutti e 500. Quello che serve sapere:

- NORECOVERY lascia il database in RESTORING, in attesa del resto: leggerlo dà Msg 927. L'ultimo passo porta RECOVERY.
- MOVE è obbligatorio quando si ripristina con un altro nome, altrimenti va in conflitto con i file dell'originale. I loro nomi logici li dà RESTORE FILELISTONLY.
- STOPAT è l'ora del server, con tre decimali: con sette ha dato Msg 3217.
- Ripristinare sopra il database vivo senza prima fare il backup della coda del log ha dato Msg 3159. La coda è ciò che è successo dall'ultimo BACKUP LOG; se ne fa il backup con BACKUP LOG … WITH NORECOVERY, che in più lascia il database in RESTORING.

BULK_LOGGED e il suo prezzo
Un SELECT … INTO di una tabella da 21 MB ha scritto 22,1 MB di log in FULL e 1,0 MB in BULK_LOGGED. Ma il backup del log successivo ha pesato uguale, 22,1 MB, perché si porta dietro le pagine caricate; e ripristinare quel backup con STOPAT ha dato Msg 4341: dentro un intervallo con un caricamento massivo si può arrivare solo alla fine, non a un istante. Si passa a BULK_LOGGED per il caricamento e si torna a FULL subito dopo, con un BACKUP LOG da ogni lato.

Ripristinare su un altro server
Un backup di database porta con sé i suoi utenti, ma non i login del server, che vivono in master. Ripristinato su un'altra macchina, ogni utente resta orfano del suo login; come trovarli e ricollegarli è spiegato nell'argomento sulla sicurezza di SQL Server.

Cosa fa Calíope
Backup scrive uno script di testo, non un .bak: istruzioni con GO tra i batch, righe in INSERT da mille, date in ISO 8601 e binari come 0x…, perché torni uguale su un altro server, un'altra versione o con la sessione in un'altra lingua. Con Includi le password degli utenti, i login partono come CREATE LOGIN … WITH PASSWORD = 0x… HASHED, e l'utente di ogni database come CREATE USER … FOR LOGIN: ripristinando lo script, nessuno resta orfano. Una tabella partizionata torna partizionata: lo script crea prima, se non esistono, la sua funzione e il suo schema di partizione.

Dal SQL Editor puoi lanciare un BACKUP DATABASE —è un'istruzione come un'altra—, ma il file resta sul disco del server: Calíope non lo porta qui.

Raccomandazione
Decidi il modello per ogni database: SIMPLE dove perdere il lavoro di oggi è accettabile, e FULL con un BACKUP LOG pianificato dove non lo è. Completo settimanale, differenziale giornaliero e log ogni pochi minuti è la routine di sempre, sempre WITH CHECKSUM. Tieni d'occhio log_reuse_wait_desc, fai ogni copia isolata con COPY_ONLY, e ripristina davvero, con un altro nome, ogni tanto: un backup mai ripristinato è un'intenzione. Il backup di Calíope serve a portare un database, o una parte, su un'altra macchina; non sostituisce i backup del server.

Parole chiave: backup, restore, ripristino, recupero, modello di recupero, full, simple, bulk_logged, log delle transazioni, log_reuse_wait_desc, backup log, catena del log, norecovery, stopat, copy_only, differenziale, checksum, verifyonly, punto nel tempo, 3159, 4214, 4341

Login di server e utente di database, perché un utente resta orfano dopo il ripristino su un altro server e come ricollegarlo, concedere per schema, DENY e chi lo scavalca, la catena di proprietà, gli utenti indipendenti, la sicurezza a livello di riga e cosa fare con sa.

Si applica a: SQL Server 2022+

Se vieni da MySQL, qui l'account non porta dentro l'host e, in più, sono due cose: il login, che è del server e con cui si entra, e l'utente, che è di ogni database e che riceve i permessi. Se vieni da PostgreSQL, è come se ogni ruolo con LOGIN dovesse esistere un'altra volta dentro ogni database per poterlo usare. Tutto quello che racconto qui l'ho misurato su SQL Server 2025.

Login e utente

CREATE LOGIN ana WITH PASSWORD = 'Xq7#lunes_pw9';   -- server: entrare
USE tienda;
CREATE USER ana FOR LOGIN ana;                    -- database: starci dentro

Con il login e senza l'utente si entra nel server ma non nel database: connettersi nominandolo ha dato «Cannot open database … The login failed» (4060), e un USE da master ha dato Msg 916. L'utente è legato al suo login dal sid, non dal nome: è da lì che nascono gli orfani.

La password passa per la policy (CHECK_POLICY, attiva di default): otto caratteri di tre classi, e senza il nome del login dentro. 'Xq7#ana_pw9' per il login ana ha dato Msg 33064, anche se rispetta il resto.

Utenti orfani: ripristinare su un altro server
L'ho riprodotto: ho fatto il backup di un database con l'utente ana, ho eliminato il login e l'ho ricreato con lo stesso nome —come sarebbe su un'altra macchina—, e ho ripristinato il database. ana non riusciva più a entrare nella copia: il suo utente conservava il sid vecchio, e il login nuovo ne aveva un altro. Ricrearlo non funzionava nemmeno: Msg 15023, l'utente esiste già. Trovarli:

SELECT dp.name, dp.type_desc
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON sp.sid = dp.sid
WHERE dp.type IN ('S', 'U', 'G')
  AND dp.authentication_type_desc = 'INSTANCE'
  AND sp.sid IS NULL;

E ricollegarli, senza perdere i loro permessi:

ALTER USER ana WITH LOGIN = ana;

Altre due cose. DROP LOGIN non controlla se il login ha utenti: l'ha eliminato senza un avviso e ha lasciato orfano l'utente nel database originale, sullo stesso server. E perché non succeda quando si porta un database su un'altra macchina, il login si crea lì con lo stesso sid (CREATE LOGIN ana WITH PASSWORD = …, SID = 0x1C2F…): con quello, l'utente del database ripristinato è tornato a funzionare senza toccarlo.

Ruoli
Ci sono 18 ruoli fissi di server: gli otto di sempre (sysadmin, serveradmin, securityadmin, processadmin, setupadmin, bulkadmin, diskadmin, dbcreator) e dieci ##MS_…## dal 2022, più fini (##MS_ServerStateReader## per vedere lo stato senza toccare nulla). In ogni database ce ne sono nove fissi: db_owner, db_datareader, db_datawriter, db_ddladmin, db_securityadmin, db_accessadmin, db_backupoperator e i due che negano, db_denydatareader e db_denydatawriter. I ruoli propri si creano con CREATE ROLE e si riempiono con ALTER ROLE … ADD MEMBER.

E public, a cui appartengono tutti. Sul server ha VIEW ANY DATABASE: ana vedeva i nomi dei dieci database del server, anche se poteva entrare solo in quattro. Se i nomi sono un'informazione, lo si toglie a public.

Concedere per schema copre anche le tabelle di domani

GRANT SELECT ON SCHEMA::rrhh TO ana;

Con questo ana ha letto le tabelle di rrhh, e anche quella che ho creato dopo. È il contrario di PostgreSQL, dove ON ALL TABLES raggiunge solo quelle di oggi. E ciò che sta fuori dallo schema resta chiuso: dbo.pedidos ha dato Msg 229.

GRANT, DENY e REVOKE: tre stati, non due
- GRANT concede.
- DENY vieta, e prevale su ogni GRANT: con DENY SELECT su rrhh.nominas, ana ha ricevuto Msg 229 pur avendo lo schema concesso, e anche essendo membro di db_datareader.
- REVOKE cancella quello che c'è, sia un GRANT sia un DENY. Dopo il REVOKE, ana è tornata a leggere, tramite il ruolo.

Chi scavalca un DENY l'ho misurato uno per uno:
- Un membro di db_owner: non lo scavalca, Msg 229.
- Un login con CONTROL SERVER: nemmeno, Msg 229.
- Un membro di sysadmin: sì. Entra in ogni database come dbo (USER_NAME() lo dice) e non gli si controlla nessun permesso, quindi ha letto la tabella negata.

Cosa posso fare, chiesto al server

EXECUTE AS USER = 'ana';
SELECT permission_name, subentity_name
FROM fn_my_permissions('rrhh.nominas', 'OBJECT');
SELECT HAS_PERMS_BY_NAME('rrhh.nominas', 'OBJECT', 'UPDATE');
REVERT;

fn_my_permissions ha restituito SELECT quattro volte: una per la tabella e una per ciascuna delle sue tre colonne. È il modo di controllare un permesso effettivo —con ruoli, schemi e DENY già risolti— senza chiedere la password a nessuno.

La catena di proprietà
Se una vista o una procedura e la tabella che leggono hanno lo stesso proprietario, chi può usare la vista non ha bisogno di permessi sulla tabella. ana, senza nulla su dbo.pedidos, ha letto tramite la vista dbo.v_totales e tramite la procedura dbo.p_total. È il modo buono di dare accesso: solo a ciò che l'oggetto mostra. Ma l'SQL dinamico rompe la catena: la stessa procedura con un EXEC('SELECT …') dentro ha dato Msg 229.

Utenti indipendenti
In un database indipendente l'utente porta con sé la password e non ha bisogno di login, quindi viaggia con il database e non resta mai orfano:

EXEC sp_configure 'contained database authentication', 1;
RECONFIGURE;
CREATE DATABASE tienda CONTAINMENT = PARTIAL;
-- dentro tienda:
CREATE USER carla WITH PASSWORD = 'Rt5%jueves_z2';

L'opzione è spenta di default. Con quella, carla è entrata nominando il database alla connessione; senza nominarlo, «Login failed». In Calíope significa mettere il database nel campo Database della connessione.

Sicurezza a livello di riga
Una policy con un predicato di filtro:

CREATE FUNCTION seg.fn_lo_mio(@dueno sysname)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN SELECT 1 AS ok WHERE @dueno = USER_NAME();

CREATE SECURITY POLICY seg.solo_lo_mio
ADD FILTER PREDICATE seg.fn_lo_mio(dueno) ON dbo.pedidos
WITH (STATE = ON);

ana ha visto la sua riga. E a differenza di PostgreSQL, qui nessuno la scavalca: sa e un sysadmin hanno visto zero righe, perché per loro USER_NAME() è dbo. Decide il predicato, e se l'amministratore deve vedere tutto, deve dirlo il predicato. Inoltre, un filtro filtra solo ciò che si legge: ana ha inserito una riga di un altro proprietario senza errori. Per impedirlo serve un BLOCK PREDICATE.

sa
sa è il login con sid 0x01, membro di sysadmin, e il suo nome lo conosce chiunque provi a entrare. Una password sbagliata dà 18456 sul client, sempre con stato 1; il motivo è solo nel log del server, dove lo stesso tentativo compare con stato 8, password errata. Di solito si crea un login di amministrazione con un nome proprio e poi ALTER LOGIN sa DISABLE.

Cosa non fa Calíope
Calíope parla solo l'autenticazione SQL: né l'autenticazione di Windows né Microsoft Entra ID.

Cosa fa Calíope
Utenti elenca i login del server, con public come un account in più, e sa a parte, segnato come Utente di sistema. I permessi si vedono per livello —server, database, schema, tabella, routine—, e un DENY si disegna come terzo stato: con il suo glifo, barrato e con la parola DENY, non solo con un altro colore. Non si può deselezionare, perché toglierlo non è deselezionare ma un REVOKE. Account bloccato è ALTER LOGIN … DISABLE. Un utente di database senza login, orfano o indipendente, non compare: per quelli, le query qui sopra nello SQL Editor.

Raccomandazione
Un login per persona o applicazione, e permessi per schema a ruoli, non a persone. sysadmin solo per amministrare, perché nessun DENY lo raggiunge. Quello che si può dare tramite una vista o una procedura, che passi di lì, e non con SQL dinamico dentro. Spostando un database su un'altra macchina, i login con il loro sid, oppure la query degli orfani subito dopo il ripristino. E sa, disattivato.

Parole chiave: sicurezza, login, utente, user, ruolo, role, sysadmin, db_owner, db_datareader, public, grant, deny, revoke, schema, fn_my_permissions, has_perms_by_name, utente orfano, orphaned user, alter user with login, sid, utente indipendente, contained database, catena di proprietà, ownership chaining, row-level security, security policy, sa, check_policy, 229, 916, 4060, 15023, 18456, 33064

sp_configure e RECONFIGURE, perché value e value_in_use non coincidono, le opzioni che richiedono un riavvio, il tetto di memoria che non è impostato, dove vince ogni MAXDOP, la configurazione per database, tempdb e mssql-conf.

Si applica a: SQL Server 2022+

Se vieni da MySQL, qui non ci sono né my.cnf né SET GLOBAL: ciò che riguarda il server si scrive con sp_configure ed entra in vigore con RECONFIGURE, e ciò che riguarda un database, con ALTER DATABASE SCOPED CONFIGURATION. Se vieni da PostgreSQL, sys.configurations è il tuo pg_settings, e la colonna che conta non è source: è la differenza tra value e value_in_use. Tutto quello che racconto qui l'ho misurato su SQL Server 2025.

107 opzioni, e 74 nascoste
sys.configurations ha 107 opzioni su questo server, e sp_configure da solo ne mostra 33. Le altre 74 sono avanzate: finché non si attiva show advanced options non vengono né elencate né si possono cambiare. sp_configure 'max degree of parallelism', 4 ha dato Msg 15123, «does not exist, or it may be an advanced option».

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

Scritto non è applicato: value e value_in_use
sp_configure si limita a scrivere il valore, e il server resta con quello precedente fino al RECONFIGURE. Dopo sp_configure 'cost threshold for parallelism', 50, sys.configurations diceva value 50 e value_in_use 5, e il server stesso avvisava: «Run the RECONFIGURE statement to install».

SELECT name, value, value_in_use, is_dynamic, is_advanced
FROM sys.configurations
WHERE value <> value_in_use;

Questa query si esegue prima di ogni RECONFIGURE, per due cose che ho misurato:
- RECONFIGURE installa tutto ciò che è in sospeso, non solo il tuo: se qualcuno ha lasciato un valore scritto, entra insieme al tuo.
- Un RECONFIGURE che fallisce lascia in sospeso ciò che è scritto. Con min server memory (MB) a 1.024 e max server memory (MB) a 128 ha dato Msg 5831, e il 1.024 è rimasto in value ad aspettare il successivo.

C'è un caso con lasciapassare: recovery interval (min) a 120 ha dato Msg 5807 («not recommended»), e RECONFIGURE WITH OVERRIDE l'ha applicato lo stesso. WITH OVERRIDE salta proprio il controllo che ha appena avvisato, quindi non si usa per abitudine.

Quelle che richiedono un riavvio
Quelle con is_dynamic = 0 non entrano in vigore con RECONFIGURE: qui sono 23 su 107, tra cui user connections, fill factor (%), locks e priority boost. Con sp_configure 'user connections', 500 e RECONFIGURE, value è passato a 500 e value_in_use è rimasto a 0, fino al riavvio del servizio.

Ogni modifica resta nel log del server, con ora e sessione: EXEC xp_readerrorlog 0, 1, N'Configuration option' le elenca con il loro «changed from 0 to 4».

La memoria: max server memory arriva senza tetto
max server memory (MB) vale 2.147.483.647 di fabbrica, cioè nessuno: SQL Server prende memoria per la sua cache finché il sistema non gli chiede di rilasciarla. Su un server dedicato gli si mette un tetto che lasci spazio al sistema operativo. Il minimo che accetta è 128; con 100 ha dato Msg 15129.

EXEC sp_configure 'max server memory (MB)', 12288;   -- 12 GB su 16
RECONFIGURE;

Su Linux c'è un altro tetto più in basso, memory.memorylimitmb di mssql-conf, che secondo la documentazione di fabbrica è l'80 % della memoria fisica. Questo container vede 6.347 MB, e con max server memory intatto il suo obiettivo (committed_target_kb di sys.dm_os_sys_info) era di 5.139 MB.

Parallelismo: tre livelli, e vince il più vicino
Di fabbrica, max degree of parallelism vale 0 (tutti i processori) e cost threshold for parallelism 5. Ho eseguito la stessa query su 300.000 righe e letto il DegreeOfParallelism del piano reale:
- Con il server a 0: 15 thread, tutti i processori che vede.
- Con il server a 4: 4.
- Con il database a ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 2: 2, anche se il server dice 4.
- Con OPTION (MAXDOP 8) nella query: 8, sopra il database e il server.
- Con cost threshold for parallelism a 50: in serie. La soglia si confronta con il costo del piano in serie (qui 7,7), non con quello del piano parallelo.

Come il parallelismo si vede nelle attese lo racconto in «Prestazioni e diagnosi (SQL Server)».

Configurazione per database
sys.database_scoped_configurations ha 41 opzioni in demo. Oltre a MAXDOP ci sono, tra le altre, LEGACY_CARDINALITY_ESTIMATION (0), PARAMETER_SNIFFING (1) e QUERY_OPTIMIZER_HOTFIXES (0). Sono il modo di cambiare il comportamento di una applicazione senza toccare le altre del server.

SELECT name, value
FROM sys.database_scoped_configurations
WHERE is_value_default = 0;

tempdb
Questo server vede 15 processori, e tempdb ha 8 file di dati da 8 MB, più quello del log, che crescono di 64 MB alla volta: l'installazione ne mette già uno per processore, fino a otto. È il database dietro le tabelle #temporanee, gli ordinamenti che non stanno in memoria e le versioni di riga di READ_COMMITTED_SNAPSHOT.

Ciò che non è sp_configure: mssql-conf, su Linux
I percorsi predefiniti di dati, log e backup, il certificato TLS, il tetto di memoria o la lingua vivono in /var/opt/mssql/mssql.conf, e si cambiano con /opt/mssql/bin/mssql-conf set … e un riavvio. Quello di questo container ha solo due righe: il certificato e la chiave TLS, ciò che SQL Server 2022 e successivi richiedono per il TLS rigoroso (TDS 8.0). Su Windows, la stessa cosa si fa in Gestione configurazione SQL Server.

Cosa mostra Calíope
Informazioni server, in Variabili globali, elenca sys.configurations con il suo value_in_use —ciò che è in funzione, non ciò che è scritto e in sospeso— e le proprietà SERVERPROPERTY.*: versione, edizione, collazione. Variabili di sessione sono le opzioni SET della tua connessione. Calíope le legge e non le cambia: un sp_configure si scrive nel SQL Editor, e dopo il RECONFIGURE si vede in Informazioni server aggiornando. Query lente mostra nelle sue impostazioni optimize for ad hoc workloads e max server memory (MB). E contained database authentication, l'opzione che ammette utenti con password propria, la racconto in «Login, utenti e permessi (SQL Server)».

Raccomandazione
Poche, e misurando. max server memory sempre, lasciando spazio al sistema; cost threshold for parallelism sopra 5; e MAXDOP per database quando un'applicazione lo chiede, non per tutto il server. Prima di ogni RECONFIGURE, la query value <> value_in_use: così si applica ciò che si vuole e non ciò che un altro ha lasciato a metà.

Parole chiave: configurazione, sp_configure, reconfigure, with override, sys.configurations, value_in_use, show advanced options, is_dynamic, max server memory, min server memory, memorylimitmb, mssql-conf, maxdop, max degree of parallelism, cost threshold for parallelism, database scoped configuration, tempdb, user connections, xp_readerrorlog, 15123, 15129, 5807, 5831

Perché un BEGIN TRAN dentro un altro non si annida, come tornare indietro con SAVE TRANSACTION, cosa blocca ogni livello, il 3960 di SNAPSHOT e cosa fa al log una transazione dimenticata.

Si applica a: SQL Server 2022+

Una transazione raggruppa più istruzioni in un'unità che si applica per intero o non si applica. Come in MySQL e in PostgreSQL, senza una transazione esplicita SQL Server conferma ogni istruzione per conto suo (autocommit). Quello che cambia sta nei dettagli, e tutto ciò che racconto qui l'ho misurato su SQL Server 2025.

BEGIN TRAN, non BEGIN
Un BEGIN da solo apre un blocco T-SQL, non una transazione: il server ha risposto Msg 102, «Incorrect syntax near 'BEGIN'». Si scrive BEGIN TRAN (o BEGIN TRANSACTION). Un COMMIT senza transazione aperta dà Msg 3902, e un ROLLBACK, Msg 3903.

La modalità implicita
Con SET IMPLICIT_TRANSACTIONS ON, la prima istruzione che tocca i dati apre una transazione che resta aperta fino al COMMIT. L'ho misurato: @@TRANCOUNT valeva 0, un semplice SELECT l'ha portato a 1, e solo il COMMIT l'ha riportato a 0. Una sessione che in questa modalità dimentica di confermare tiene i suoi lock e trattiene il log, come racconto più sotto.

Annidare non annida
Un BEGIN TRAN dentro un altro fa solo salire il contatore @@TRANCOUNT. A decidere è la transazione esterna:
- Il COMMIT interno non conferma niente: ha portato il contatore da 2 a 1, e il ROLLBACK esterno ha annullato entrambi gli UPDATE, anche quello interno.
- Il ROLLBACK interno annulla tutto: ha lasciato @@TRANCOUNT a 0, e il COMMIT esterno ha dato Msg 3902 perché la transazione non c'era più.
- Chiamare per nome quella interna non serve: ROLLBACK TRAN interior ha dato Msg 6401 e il contatore è rimasto a 2. Si può tornare indietro solo fino a un punto di salvataggio.

BEGIN TRAN;                                  -- @@TRANCOUNT = 1
  UPDATE cuentas SET saldo = saldo - 10 WHERE id = 1;
  BEGIN TRAN;                                -- @@TRANCOUNT = 2
    UPDATE cuentas SET saldo = saldo + 10 WHERE id = 2;
  COMMIT;                                    -- @@TRANCOUNT = 1, niente confermato
ROLLBACK;                                    -- annulla entrambi gli UPDATE

Una procedura che termina con un @@TRANCOUNT diverso da quello che aveva all'ingresso dà Msg 266: ne parlo in «Errori (SQL Server)».

Punti di salvataggio: SAVE TRANSACTION
Per annullare una parte senza perdere la transazione, si segna un punto e ci si torna. Qui l'INSERT duplicato è fallito con 2627, sono tornato al punto, e il COMMIT ha confermato la riga di prima e quella di dopo:

BEGIN TRAN;
  INSERT INTO cuentas (id, saldo) VALUES (3, 0);
  SAVE TRANSACTION antes;
  BEGIN TRY
    INSERT INTO cuentas (id, saldo) VALUES (1, 0);   -- fallisce: 2627
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION antes;                      -- @@TRANCOUNT resta a 1
  END CATCH
  INSERT INTO cuentas (id, saldo) VALUES (5, 5);
COMMIT;

Due limiti che ho misurato: SAVE TRANSACTION senza transazione aperta dà Msg 628, e con XACT_ABORT ON lo stesso errore ha lasciato la transazione condannata —XACT_STATE() -1— e tornare al punto ha dato Msg 3931: restava solo annullarla per intero.

Un errore non interrompe quasi mai la transazione
Al contrario di PostgreSQL, un errore di istruzione —una chiave duplicata, un vincolo— interrompe solo quell'istruzione, e la transazione resta viva con quello che ha già fatto. Senza XACT_ABORT, un COMMIT alla fine conferma ciò che viene prima e dopo l'errore. Con SET XACT_ABORT ON, lo stesso errore annulla tutto e interrompe il batch. Quali errori annullano la transazione da soli lo racconto in «Errori (SQL Server)».

I livelli di isolamento
Il predefinito è READ COMMITTED. Ho misurato con due sessioni: la prima legge quattro righe di un intervallo, aspetta quattro secondi e le rilegge; la seconda, nel frattempo, inserisce una riga in quell'intervallo e aggiorna una delle righe lette.
- READ UNCOMMITTED legge ciò che gli altri non hanno confermato: è il NOLOCK di «Lock e deadlock (SQL Server)».
- READ COMMITTED non trattiene i lock di ciò che legge. Senza READ_COMMITTED_SNAPSHOT, che di fabbrica è spento, una lettura aspetta chi scrive; ne parlo in «Lock e deadlock (SQL Server)».
- REPEATABLE READ ha trattenuto cinque lock condivisi (KEY S) fino alla fine: l'UPDATE della riga letta ha aspettato 3 s. Ma l'INSERT è entrato subito, e la rilettura ha contato 5 righe invece di 4: una riga fantasma.
- SERIALIZABLE ha bloccato l'intervallo (KEY RangeS-S): l'INSERT ha aspettato 3 s e la rilettura ha continuato a contare 4.
- SNAPSHOT legge una fotografia dell'inizio della transazione e non blocca nessuno, ma va permesso in ogni database, e di fabbrica è spento: snapshot_isolation_state_desc era OFF in demo, in model e in un database nuovo. Senza permesso, SET TRANSACTION ISOLATION LEVEL SNAPSHOT e BEGIN TRAN sono passati, ed è stato il primo SELECT a dare Msg 3952.

ALTER DATABASE mi_base SET ALLOW_SNAPSHOT_ISOLATION ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

SNAPSHOT non aspetta: interrompe con 3960
Con il permesso, la seconda sessione ha aggiornato la riga senza aspettare la prima, che l'aveva solo letta. La prima l'ha riletta e vedeva ancora il valore della sua fotografia, 100, con il 101 dell'altra già confermato. Quando ha voluto aggiornarla, il server l'ha interrotta con Msg 3960 («update conflict»), e XACT_STATE() è rimasto a -1. In SNAPSHOT, un 3960 è funzionamento normale: l'applicazione deve ripetere l'intera transazione. READ_COMMITTED_SNAPSHOT è un'altra cosa: non cambia livello, cambia il modo in cui legge READ COMMITTED, ed è quello che racconto in «Lock e deadlock (SQL Server)».

Una transazione dimenticata trattiene il log
Una sessione addormentata dentro una transazione (status = sleeping, open_transaction_count = 1) impedisce di riutilizzare il log. Ho inserito e cancellato 5.000 righe e lanciato un CHECKPOINT, in un database dove il CHECKPOINT svuota il log. Senza quella sessione sono rimasti in uso 6,5 MB; con lei, 73,8 MB, e log_reuse_wait_desc diceva ACTIVE_TRANSACTION. Dopo averla chiusa, 5,1 MB. Il resto del ciclo del log lo racconto in «Backup e ripristino (SQL Server)». Per trovarla:

DBCC OPENTRAN;   -- la più vecchia: SPID e ora di inizio

SELECT session_id, status, open_transaction_count, last_request_end_time
FROM sys.dm_exec_sessions
WHERE open_transaction_count > 0;

Anche il DDL si annulla
ALTER TABLE, CREATE INDEX e CREATE TABLE stanno dentro la transazione, con le loro eccezioni (CREATE DATABASE dà Msg 226): ne parlo in «Modifiche di schema a caldo (SQL Server)».

Cosa fa Calíope
Il SQL Editor esegue quello che gli dai su una sola connessione, istruzione per istruzione, e si ferma alla prima che fallisce. Senza transazione dal menu, alla fine restituisce la connessione al pool, e se è rimasta aperta una transazione la annulla (IF @@TRANCOUNT > 0 ROLLBACK): un BEGIN TRAN e il suo COMMIT scritti a mano vanno nella stessa esecuzione, e ciò che precedeva un errore viene annullato quando la connessione viene restituita. In sqlcmd o in un'applicazione sarebbe stato confermato.
Per una transazione che duri più esecuzioni c'è il menu Transazione della barra: BEGIN invia BEGIN TRANSACTION e riserva una connessione per la scheda, e ciò che esegui lì resta dentro fino al COMMIT o al ROLLBACK. Chiudere la scheda la annulla. Se il server la annulla da solo — un 245, un 1205 o qualsiasi errore con XACT_ABORT — o la chiude un COMMIT del tuo SQL, l'etichetta Transazione attiva si spegne e la barra di stato lo segnala: da lì in poi, ogni istruzione viene confermata subito.

Raccomandazione
SET XACT_ABORT ON in ogni batch o procedura che scrive, e un TRY…CATCH che finisca con IF @@TRANCOUNT > 0 ROLLBACK; e THROW;. Non annidare BEGIN TRAN aspettandoti che quella interna sia indipendente: a questo servono i punti di salvataggio. Se passi a SNAPSHOT, scrivi la ripetizione del 3960 prima di passare. E ogni tanto, DBCC OPENTRAN: una sessione dimenticata non avvisa finché il disco non è pieno.

Parole chiave: transazione, commit, rollback, begin tran, @@trancount, save transaction, punto di salvataggio, xact_abort, xact_state, implicit_transactions, isolamento, repeatable read, serializable, snapshot, allow_snapshot_isolation, 3960, 3952, 3902, 6401, 3931, active_transaction, dbcc opentran

Quando mettere i dati dentro il documento e quando referenziarli: i quattro pattern di MongoDB, l'antipattern dell'array senza tetto e quanto costa ciascuno, misurato.

Si applica a: MongoDB 7.0+

In SQL lo schema deriva dalla normalizzazione: ogni fatto in un solo posto, e le query lo ricompongono con JOIN. In MongoDB deriva dallo schema di accesso: ciò che si legge insieme si salva insieme. La domanda non è più «come evito di ripetere un dato?», ma «che cosa voglio che mi restituisca una sola lettura?».

Incorporare o referenziare

Un ordine può portarsi dentro le sue righe, oppure le righe possono vivere in una collezione propria puntando all'ordine.

// 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" } } ])

Misurato contro MongoDB 8.2 con 100.000 ordini di tre righe: leggere un ordine con le sue righe costa 0,25 ms incorporato e 0,30 ms con $lookup —ma 67 ms se manca l'indice su l_ref.pedido, perché allora ogni ordine percorre tutte le 300.000 righe—. Su disco gli ordini incorporati occupano 2,9 MB contro 1,1 MB + 5,2 MB delle due collezioni separate: qui incorporare è uscito più economico anche in spazio.

Incorporare non rinuncia agli indici: un indice su un campo dentro l'array è multichiave e funziona uguale. Cercare {"lineas.sku": "A-7"} senza di esso è un COLLSCAN di 100.000 documenti in 42 ms; con esso, 2.000 esaminati in 2 ms, per le stesse 2.000 righe. E un documento si modifica in modo atomico senza transazione: l'$inc di un contatore e il $set di uno stato in un solo updateOne entrano insieme o non entra nessuno dei due.

L'antipattern: l'array che non smette di crescere

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

Il documento vuoto è di 29 byte e ogni lettura aggiunge 28,9. A 540.000 misura 16.628.919 byte e la tornata successiva fallisce con il codice 10334: «Resulting document after update is larger than 16777216». Il tetto di 16 MB per documento non si negozia. E fa male già prima di arrivarci: un $set di un campo scalare costa 3,15 ms su quel documento e 0,50 ms su uno di 228 byte, e un $pop dell'array 71,95 ms contro gli 0,15 ms che costa inserire una lettura sciolta. Un array che cresce senza tetto noto è un riferimento messo male.

Bucket: le letture si raggruppano in documenti di 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 })

Le stesse 540.000 letture: sciolte sono 540.000 documenti, 6,5 MB di dati e 8,4 MB di indice; in bucket da 200 sono 2.700 documenti, 4,2 MB di dati e 82 KB di indice. Inserirle costa 203 ms in bucket contro 901 ms sciolte. Leggere l'ultima: 0,20 ms dal bucket, 0,30 ms sciolta e 46,80 ms dall'array incorporato, che va portato via intero.

Riferimento esteso: copiare dentro il figlio la manciata di campi del padre che si mostrano sempre. Elencare 100 ordini con il nome del loro cliente costa 0,35 ms con il nome duplicato dentro e 1,00 ms con $lookup. Si paga in scrittura: rinominare un cliente obbliga a toccare i suoi 1.000 ordini, 2 ms con un indice su cliente. Si duplica ciò che non cambia quasi mai.

Valore calcolato: salvare il totale già sommato invece di ricalcolarlo a ogni lettura. Su un ordine non si nota —0,35 ms letto dal documento, 0,30 ms sommato al volo—; aggregando i 100.000 sì: 14 ms leggendo il campo contro 129 ms ricalcolando.

Sottoinsieme: dentro il documento solo le poche righe che si mostrano, il resto nella sua collezione. 500 prodotti con 500 recensioni ciascuno: incorporate tutte, il documento medio è di 80.738 byte; con le ultime cinque e un contatore, 889 byte, e la collezione scende da 6,1 MB a 60 KB. La scheda passa da 0,55 ms a 0,25 ms, e la pagina di 20 recensioni si chiede a parte, alla sua collezione.

La regola, in una riga: incorpora ciò che si legge con il suo padre, appartiene solo a lui e ha un tetto; referenzia ciò che cresce senza limite, si condivide fra più padri o si consulta per conto proprio.

Parole chiave: modellazione, incorporare, riferimento, documento, pattern, bucket, sottoinsieme, riferimento esteso, valore calcolato, array, schema, 16 MB

Quale tipo MongoDB salva con ogni valore, quanto occupa, come si confrontano tipi diversi fra loro e come si ordina il testo accentato.

Si applica a: MongoDB 7.0+

In SQL il tipo lo dichiara la tabella e tutte le righe lo rispettano. In MongoDB il tipo viaggia con ogni valore: non c'è CREATE TABLE, e due documenti della stessa collezione possono avere un intero e una stringa nello stesso campo. Il formato si chiama BSON, un'estensione binaria di JSON con i tipi che a JSON mancano: interi a 32 e a 64 bit, decimali esatti, date, binari e ObjectId.

_id e ObjectId

Ogni documento ha un _id: unico, immutabile e indicizzato da quando nasce la collezione. Se non lo scrivi tu, il client ci mette un ObjectId: 12 byte, di cui 4 sono il tempo in secondi, 5 sono casuali per processo e 3 sono un contatore. Cresce con l'orologio, quindi serve per intervalli di date senza salvare nessuna data.

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

Tenerlo come stringa costa e non aggiunge nulla: {_id: ObjectId()} pesa 22 byte e lo stesso valore in stringa, 39.

Interi, decimali binari ed esatti

Quattro tipi numerici, e la differenza si vede nel documento: $bsonSize su {_id: 1, a: …} dà 21 byte con int, 25 con long o con double e 33 con decimal. Il denaro va in decimal per lo stesso motivo per cui in SQL va in DECIMAL: sommare 0,1 e 0,2 come double dà 0.30000000000000004, e come decimali dà 0.3.

La trappola sta nel client. mongosh sceglie il tipo guardando il valore, quindi un 7 scritto a mano si salva come int, 3000000000 come double, e 9007199254740993 si salva come 9007199254740992: oltre 2⁵³ bisogna scrivere 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" } } } ])

Per interrogare, invece, i quattro sono un unico numero: con un int, un long, un double e un decimal di valore 7 nella collezione, {v: 7} li trova tutti e quattro, e {v: "7"} non ne trova nessuno. $type sì che li distingue, e "number" li raggruppa di nuovo.

Date

Date sono 8 byte di millisecondi dal 1970, sempre in UTC e senza fuso orario. Qui non esiste la coppia DATETIME / TIMESTAMP: c'è un tipo solo, il fuso lo mette chi legge, e i microsecondi si perdono in scrittura. Il Timestamp di BSON non è per i tuoi dati: è l'orologio interno della replica.

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

Binari e UUID

BinData salva byte con un sottotipo, e UUID() è il sottotipo 4. Un UUID come BinData occupa 29 byte di documento; lo stesso scritto come stringa con trattini, 49.

L'ordine fra tipi diversi

Un sort su un campo con tipi mescolati non fallisce: c'è un ordine totale fra i tipi, e misurato su quattordici documenti è questo.

MinKey → campo assente e null → numeri → stringhe → oggetti → binari → ObjectId → booleani → date → Timestamp → espressioni regolari → MaxKey.

Gli array non compaiono in quell'elenco perché un array si confronta per il suo elemento minore: [9, 10] si ordina fra i numeri. E un campo assente si ordina esattamente come null, al punto che {v: null} trova entrambe le cose; per separarle servono {v: {$type: "null"}} e {v: {$exists: false}}.

UTF-8 e collazione

Non c'è nessun set di caratteri da scegliere. Le stringhe BSON sono UTF-8 e basta: "café ☕ 日本語 👩‍💻" sono 14 caratteri e 31 byte, e torna com'era stata scritta.

Quello che si sceglie è la collazione, binaria per impostazione predefinita: senza collation, cafe e café sono valori diversi, e Árbol si ordina dopo zorro. Una collation con il suo locale e il suo strength — 1 ignora accenti e maiuscole, 2 ignora solo le maiuscole, 3 distingue tutto — cambia insieme confronto e ordine.

db.clientes.find({ n: "cafe" }).collation({ locale: "es", strength: 1 })
db.clientes.find().sort({ n: 1 }).collation({ locale: "es" })

Ed ecco la stessa trappola di SQL: la collazione della query deve essere quella dell'indice. Con un indice normale su n, la query {n: "cafe"} è un IXSCAN che esamina 1 documento; la stessa query con collation ricade in COLLSCAN e ne esamina 7. Il rimedio non è scrivere collation in ogni query, ma darla alla collezione: i suoi indici nascono allora con lei.

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

Altri due avvisi, misurati entrambi. $regex ignora la collazione: /^CAF/ non trova cafe nemmeno con strength: 1, mentre {n: "CAFE"} lo trova. E numericOrdering: true fa ordinare le stringhe "1", "2" e "10" come numeri invece che "1", "10", "2".

Parole chiave: bson, tipi, objectid, decimal128, numberlong, data, date, uuid, bindata, collation, collazione, utf-8, accenti, ordinamento

Gli undici tipi di indice di MongoDB e quando serve ciascuno, la regola ESR per ordinare un composto, la query coperta, i tetti e il prezzo in scrittura, tutto misurato con `explain`.

Si applica a: MongoDB 7.0+

Un indice è un albero B sul valore di un campo, e il numero che dice se serve esce da explain("executionStats"): totalDocsExamined contro nReturned. Se il primo è molto maggiore del secondo, il server sta leggendo documenti per buttarli.

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 regola ESR

Un indice composto si percorre per prefissi, quindi l'ordine dei suoi campi è la decisione. Prima i campi di uguaglianza (E), poi quello dell'ordinamento (S) e infine quello dell'intervallo (R). Con l'intervallo prima dell'ordinamento, l'indice filtra ma non ordina, e compare una fase SORT che ordina in memoria: 1 826 documenti, nella query qui sotto.

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

Quella fase ha un tetto: internalQueryMaxBlockingSortMemoryUsageBytes vale 104 857 600 byte, e oltre quello la query fallisce se non le si consente il disco.

Query coperta

Se l'indice porta tutti i campi che la query legge, il server non tocca i documenti. Attenzione a _id: entra nella proiezione per impostazione predefinita, non sta nell'indice, e va tolto 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

Gli altri tipi

I due che perdono dati in silenzio si vedono insieme:

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 }

Il nascosto è l'opposto: resta mantenuto, ma il pianificatore non lo guarda. Con hidden: true la stessa query è stata un COLLSCAN su 20 000 documenti; tornato visibile con collMod, un IXSCAN su 1 940.

I tetti

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

Il nome di un indice e la dimensione di una chiave non hanno un tetto pratico: 400 caratteri e 2 000 byte sono stati accettati senza protestare.

Il prezzo

Ogni indice si paga a ogni scrittura. Le stesse 20 000 inserzioni hanno impiegato 60 ms senza indici, 101 ms con cinque e 160 ms con dieci. E occupano spazio: i sei indici di pedidos sommano 1,9 MB contro 916 KB di dati.

Per questo non si indicizza tutto. Un campo a bassa cardinalità quasi mai aiuta: estado, con quattro valori distinti, esamina 5 000 documenti per restituirne 5 000. E un indice semplice è di troppo se un composto comincia già con lui: { cliente: 1 } e { cliente: 1, fecha: 1 } esaminano gli stessi 40.

Parole chiave: indice, indici, ESR, composto, multichiave, testo, 2dsphere, hashed, jolly, TTL, parziale, sparse, univoco, nascosto, query coperta, explain, IXSCAN, COLLSCAN, totalDocsExamined, MongoDB

Le fasi della pipeline e in che ordine metterle, $lookup come JOIN e quanto costa senza indice, $graphLookup, le funzioni finestra, $facet e $unionWith, e $merge rispetto a $out.

Si applica a: MongoDB 7.0+

Una pipeline è un elenco di fasi, e ognuna riceve i documenti prodotti dalla precedente. L'ordine lo scrivi tu, ed è lì che sta quasi tutta la prestazione.

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

Filtrare per primo, sempre

Solo la prima fase può usare un indice. Con un indice {estado: 1} su quei 20 000 ordini, il $match davanti esamina 5 000 chiavi e 5 000 documenti; lo stesso filtro dietro il $group, nessuna chiave e 20 000 documenti.

$lookup è il JOIN, e senza indice si paga

Unisce un'altra raccolta della stessa base, e ciò che trova arriva come array che quasi sempre si apre 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

L'explain della fase non lascia margine: senza indice su clientes.num fa una passata intera per ogni documento in ingresso, e con l'indice non ne fa nessuna.

Il ricorsivo e quello di finestra

$graphLookup segue una gerarchia fin dove arriva, e depthField annota a che distanza è rimasto ogni gradino. L'array che restituisce non è ordinato: qui sono usciti Ana, Caro e Beto con i livelli 2, 0 e 1. $setWindowFields (5.0+) è la finestra di SQL: partitionBy è il PARTITION BY e sortBy l'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

Più risposte in una sola passata

$facet esegue sotto-pipeline sullo stesso ingresso e restituisce un solo documento con tutte; al suo interno non vale più nessun indice. $unionWith è l'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 }

Scrivere il risultato

$merge fonde nella raccolta di destinazione e $out la sostituisce per intero.

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

I tetti

Ogni fase che blocca — $group, $sort, $facet, l'intermedio di $lookup — ha 104 857 600 byte, e dalla 6.0 ciò che trabocca va su disco da solo. Ma l'array di un accumulatore non trabocca: un $push su documenti grandi fallisce con 146 ExceededMemoryLimit, e allowDiskUse: true non lo salva.

Parole chiave: aggregazione, pipeline, fase, match, group, project, lookup, join, graphLookup, setWindowFields, finestra, facet, unionWith, merge, out, unwind, explain, allowDiskUse

Un documento si scrive per intero senza transazione; per più di uno ne serve una, e un replica set. La sessione, readConcern e writeConcern, il WriteConflict 112 e i tre tetti misurati.

Si applica a: MongoDB 7.0+

In MongoDB un documento si scrive per intero o non si scrive, e vale anche se l'updateOne tocca dieci campi e un array annidato. Per cambiare più di un documento alla volta serve una transazione, e una transazione richiede un replica set: su un nodo isolato viene rifiutata, e il messaggio non parla nemmeno di transazioni — dice che il deployment non ammette scritture ripetibili.

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 transazione gira su una sessione

Tutto quello che sta dentro passa dall'oggetto della sessione: il db.cuentas di fuori non è nella transazione, per quanto si chiami uguale. Fino al commitTransaction(), quanto scritto lo vede solo chi sta 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() disfa tutto, e non c'è bisogno di chiederlo: se la sessione se ne va o il server riparte, la transazione muore abortita.

readConcern e writeConcern sono due domande diverse

readConcern dice che cosa si legge: local è quello che c'è qui, majority quello che non si può più perdere, snapshot una foto coerente di un istante. writeConcern dice quando si considera scritta: w: 1 è il primario, w: "majority" la maggioranza dell'insieme, e j: true aggiunge il giornale. I valori di fabbrica escono da getDefaultRWConcern: lettura local, scrittura majority.

Il conflitto di scrittura

Due transazioni sullo stesso documento non aspettano: la seconda fallisce all'istante con 112 WriteConflict, e il messaggio lo dice senza giri di parole. Riprovare fa parte del patto, ed è per questo che i driver portano withTransaction, che riprova da 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 scrittura da fuori della transazione non riceve il 112: aspetta. Nella misura ha aspettato 76 s, finché il limite di vita non ha abortito la transazione che teneva il documento.

I tetti

Una transazione dura 60 s, e oltre quella riga il commit restituisce 251 NoSuchTransaction con «has been aborted». Dentro, ogni richiesta di blocco aspetta solo 5 ms: una transazione non resta appesa a un lucchetto, preferisce fallire. E un writeConcern che l'insieme non può soddisfare fallisce prima ancora di provarci.

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 regola pratica: se due documenti devono cambiare insieme molte volte al giorno, quasi sempre è il modello a essere sbagliato, e la cosa giusta era annidarli. La transazione è la via d'uscita per ciò che davvero non sta in un documento.

Parole chiave: transazione, transazioni, sessione, startTransaction, commit, abort, WriteConflict, 112, 251, readConcern, writeConcern, majority, snapshot, replica set, atomicità, blocco

I tre livelli di dettaglio di explain e che cosa aggiunge ciascuno, la cache dei piani e quando un piano viene disattivato, il working set dentro la cache di WiredTiger e i tre livelli del profiler.

Si applica a: MongoDB 7.0+

Prima di toccare qualcosa si misura, e lo strumento è explain. Ha tre livelli di dettaglio, e ognuno costa più del precedente: queryPlanner si limita a pianificare — non arriva a eseguire — e mostra il piano vincente e quelli scartati; executionStats esegue il vincente e aggiunge quanto è costato; allPlansExecution aggiunge inoltre quanto è costato ogni candidato durante il periodo di prova, ed è lì che si vede perché ha vinto chi ha vinto.

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

I tre numeri che contano sono nReturned, totalDocsExamined e totalKeysExamined. Se il secondo è molto più grande del primo, il server sta leggendo documenti per buttarli.

La cache dei piani

Il pianificatore non decide di nuovo a ogni query. La prima volta prova i candidati, mette da parte il vincente sotto un planCacheKey e da lì in poi lo riusa; nell'explain si vede come isCached: true. La voce conserva works, il lavoro che è costato, e se un'esecuzione successiva ne spende dieci volte tanto, il piano viene disattivato e si torna a competere. Anche creare o eliminare un indice svuota la cache.

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

Il working set e la cache di WiredTiger

MongoDB non conserva risultati: quello che conserva sono pagine, nella cache di WiredTiger, e la prestazione dipende dal fatto che il working set — i dati e gli indici davvero toccati — ci stia dentro. Per impostazione predefinita quella cache prende metà della RAM meno 1 GB. Nella misura, di 486 864 pagine richieste solo 261 sono dovute arrivare dal disco: una su 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

Il profiler

Tre livelli: 0 spento, 1 solo ciò che supera slowms, e 2 tutto. Due cose misurate che sorprendono: setProfilingLevel restituisce il livello di prima, non quello appena impostato — il nuovo va riletto — e system.profile è una raccolta limitata da 1 MiB, quindi non cresce: si morde la coda.

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

Ogni voce porta planSummary, docsExamined, nreturned e millis, che è esattamente ciò che serve per decidere se conviene un indice. Calíope legge quella raccolta nel suo strumento di profilazione. Il livello 2 in produzione costa caro: si accende per un po' e si riabbassa, non si lascia lì.

Parole chiave: prestazioni, explain, queryPlanner, executionStats, allPlansExecution, cache dei piani, planCacheKey, isCached, WiredTiger, working set, profiler, system.profile, slowms

Che cosa gira davvero secondo getCmdLineOpts, le cinque sezioni di mongod.conf che contano, e le tre classi di parametro: quelli che cambiano a caldo, quelli d'avvio e quelli che parametri non sono.

Si applica a: MongoDB 7.0+

La prima domanda su un server che non conosci non è che cosa dice il suo file di configurazione, ma con che cosa sta girando davvero. getCmdLineOpts risponde a entrambe insieme: argv è quello che gli è stato passato sulla riga di comando, e parsed la stessa cosa, già tradotta nel vocabolario del file.

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" } }

Le cinque sezioni che contano

storage dice dove stanno i dati e quanta memoria si prende la cache; net, su quali indirizzi ascolta; security, se bisogna autenticarsi; operationProfiling, che cosa si annota del traffico lento; e replication, a quale insieme appartiene. Il file è YAML, quindi l'indentazione è sintassi.

# 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

I parametri sono di tre classi

Quelli che si cambiano a caldo con setParameter, quelli che si leggono solo all'avvio, e quelli che parametri non sono, per quanto lo sembrino. Tutti e tre si distinguono da quello che risponde il server: il primo restituisce was con il valore precedente — non quello nuovo, quindi per sapere com'è rimasto va riletto; il secondo dà 20 IllegalOperation; e port, che è un'opzione d'avvio e non un parametro, dà 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 non resta

Un setParameter vive fino al riavvio successivo e non un secondo di più: con cursorTimeoutMillis messo a 300 000, il server è tornato su a 600 000. Perché resti va scritto nella sezione setParameter: del file, quella in fondo all'esempio.

La cache e la versione di compatibilità

La cache di WiredTiger è la prima cosa che si vuole toccare, e quasi sempre quella da non toccare: per impostazione predefinita si prende metà di quello che resta della RAM dopo averne messo da parte 1 GB. Sul nodo misurato, 7 933 MB di RAM hanno dato 3 621 781 504 byte di cache. E c'è una sesta cosa che nel file non sta: la featureCompatibilityVersion, che decide quali funzioni del binario sono accese — si alza a mano dopo un aggiornamento e si abbassa prima di tornare indietro.

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" }

Parole chiave: configurazione, mongod.conf, getCmdLineOpts, setParameter, cacheSizeGB, WiredTiger, bindIp, authorization, operationProfiling, replSetName, featureCompatibilityVersion, FCV

Un utente vive in una base ed è quello il suo cognome, i ruoli integrati non sono gli stessi in admin e altrove, il 13 che riceve una scrittura senza permesso, e l'unica porta che lascia aperta un server appena messo in sicurezza.

Si applica a: MongoDB 7.0+

Senza security.authorization: enabled non esiste niente di tutto questo: il server accetta chiunque arrivi, e con bindIp: "*" può arrivare chiunque. Acceso, la prima domanda è chi sono io.

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"]

Il meccanismo predefinito è SCRAM-SHA-256, e il server conserva entrambe le versioni: la password dell'utente misurato porta 15 000 iterazioni in SHA-256 e 10 000 in SHA-1, con un sale di 40 caratteri. La password non viaggia, nemmeno cifrata: SCRAM dimostra che la si conosce senza dirla. Il terzo meccanismo, MONGODB-X509, scambia la password con un certificato client, e allora il nome dell'utente è il soggetto del certificato.

Un utente vive in una base, ed è quella la sua altra metà

lector non è un utente: lector di ventas lo è. La base in cui è stato creato è la sua base di autenticazione, e va nominata al collegarsi (--authenticationDatabase). Con quella sbagliata il server non dice che l'utente esiste altrove: dice «Authentication failed» e basta.

I ruoli non sono gli stessi in tutte le basi

Una base normale ha sei ruoli integrati. admin ne ha ventuno, perché lì vivono quelli che arrivano a tutto il server — i …AnyDatabase, root, backup, restore, quelli di cluster. Un ruolo è un elenco di azioni: read ne sono undici, e find è solo una.

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

Che cosa succede quando manca un permesso

Non c'è risposta vuota né riga mancante: c'è un 13 Unauthorized, e il messaggio nomina la base, il comando e persino la raccolta. È un errore da cui si ricava la regola che manca.

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: ... }

L'eccezione di localhost

Un server con --auth e senza un solo utente lascia fare a chi arriva dalla macchina stessa esattamente una cosa: creare il primo. Leggere no; creare il secondo, nemmeno. È la rampa per cominciare, e si chiude da sola non appena un utente esiste.

// 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

Parole chiave: sicurezza, autenticazione, SCRAM, SCRAM-SHA-256, x.509, TLS, utente, ruolo, ruoli integrati, read, readWrite, dbAdmin, userAdmin, root, 13, Unauthorized, localhost, authSource

Che cosa c'è dentro un mongodump e che cosa no, quanto ci mette il ripristino e perché, a che serve --oplog, quanto dura davvero la finestra dell'oplog, e le due cose che un dump non garantisce.

Si applica a: MongoDB 7.0+

mongodump è un backup logico: si collega come un client qualunque, legge i documenti e li scrive in BSON. Questo ha due conseguenze che si vedono nei numeri. La prima: il file misura quanto misurano i documenti, non quanto occupano sul disco — 2 420 000 byte di .bson per una raccolta che sul disco sta compressa. La seconda: compete per la cache con il lavoro normale del server, quindi un dump di una base grande si sente.

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

Degli indici viaggia solo la definizione, nel .metadata.json. Per questo ripristinare costa molto più che scaricare — 91 ms contro 26 in questa misura: il tempo se ne va nel ricostruirli, e su una raccolta vera è quasi tutta l'attesa.

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 lascia un solo file invece di un albero di cartelle, e con --gzip è sceso da 2 420 000 a 133 572 byte. Entrambi si possono mandare in una pipe, ed è così che una base si copia da una macchina all'altra senza toccare un disco intermedio.

mongodump ... --db ventas --archive=/vol/ventas.gz --gzip
// 133572 bytes, frente a 2420000 del BSON suelto

L'oplog è ciò che trasforma un backup in un istante

Un dump dura, e intanto la base continua a cambiare: quello che è stato scritto nella raccolta A prima di scaricarla e in B dopo non torna. --oplog salva anche le operazioni avvenute durante lo scarico, e mongorestore --oplogReplay le applica alla fine, così che quanto ripristinato è lo stato di un istante, quello di fine dump. Funziona solo contro un replica set, perché l'oplog è suo.

// 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

L'oplog è una raccolta limitata, quindi la sua finestra non si misura in byte ma in tempo, e quel tempo dipende da quanto si scrive. Sul nodo misurato, 42 GiB di tetto davano una finestra dall'8 agosto al 12 settembre; con dieci volte il carico sarebbero tre giorni. È il numero da guardare prima di andarsene per il fine settimana.

Quello che un dump non copre

Due cose. Una base di centinaia di gigabyte non si salva leggendola documento per documento: lì si passa alle istantanee del file system, che vanno prese con il giornale incluso o con la base bloccata da fsyncLock. E in un cluster frammentato, un mongodump contro il router non dà un istante comune ai frammenti: bisogna fermare il bilanciatore e prendere un'istantanea per frammento più una dei server di configurazione.

Parole chiave: backup, copia di sicurezza, mongodump, mongorestore, oplog, oplogReplay, archive, gzip, istantanea, snapshot, fsyncLock, ripristino, finestra di ripristino, BSON

Lo schema è quello che portano i documenti, quindi cambiarlo vuol dire scriverli. Il validatore con $jsonSchema, il 121 e il suo errInfo, le quattro combinazioni di validationLevel e validationAction, e la migrazione per versione di documento.

Si applica a: MongoDB 7.0+

Non c'è ALTER TABLE perché non c'è tabella: lo schema di una raccolta è, letteralmente, quello che portano i suoi documenti. Aggiungere un campo ai nuovi non costa nulla e non cambia i vecchi, ed è lì l'inghippo: chi legge deve arrangiarsi con entrambe le forme finché qualcuno non pareggia il passato.

Il validatore è una porta, non uno schema

Quello che esiste è un validator con $jsonSchema: una condizione controllata in scrittura, mai in lettura e mai all'indietro. Rifiuta con 121 DocumentValidationFailure, e la parte buona sta in errInfo.details, che nomina la regola infranta invece di dire «non valido».

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"]

Attenzione ai tipi: mongosh salva come int un numero intero, quindi edad: 30 supera un bsonType: "int" ed edad: 30.5 lo infrange, perché quello sì è un double.

validationLevel e validationAction sono due manopole diverse

Il livello dice quali documenti raggiunge: strict tutti, moderate solo quelli che erano già validi — così si mette un validatore su una raccolta piena di vecchiume senza incepparne gli aggiornamenti. L'azione dice che cosa succede quando fallisce: error rifiuta, warn lascia scrivere e lo annota nel log. E mettere il validatore con collMod non tocca nulla di ciò che c'era già.

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

Migrare vuol dire scrivere

Senza ALTER, l'equivalente di una colonna nuova è un updateMany con $set, e quello di toglierla, uno con $unset. Costano poco — 20 000 documenti in 61 e 51 ms — ma non sono atomici: vanno documento per documento, quindi durante la migrazione convivono le due forme.

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

Per questo il motivo che regge è tenere la versione dentro ogni documento (_v): l'applicazione sa leggere entrambe, la migrazione avanza a lotti o al momento di toccare ogni documento, e il giorno in cui {_v: 1} non restituisce più nulla, il codice vecchio se ne va. È quello che fa anche una migrazione SQL, solo che qui lo stato intermedio è visibile e va scritto.

Parole chiave: schema, validazione, validatore, jsonSchema, 121, DocumentValidationFailure, validationLevel, validationAction, strict, moderate, warn, collMod, migrazione, versione di documento

I tre pezzi di un cluster frammentato, perché una chiave hashed distribuisce e una monotona ammucchia, la differenza misurata fra una query mirata e una diffusa, le zone, e quanto costa cambiare la chiave.

Si applica a: MongoDB 7.0+

Frammentare vuol dire ripartire una raccolta su più macchine, e servono tre pezzi: i frammenti, che tengono i dati e sono replica set; i server di configurazione, che tengono la mappa di quale pezzo sta dove; e mongos, il router, che non tiene nulla ed è quello a cui si collega l'applicazione.

# 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 chiave di frammentazione è l'unica decisione che conta

Da lei escono tre cose insieme: come si distribuiscono i dati, quali query si possono dirigere a un solo frammento, e se c'è un punto caldo. Si chiede cardinalità — molti valori distinti —, frequenza pari, perché nessun valore si prenda la metà, e che non sia monotona, perché una chiave che cresce sempre manda tutte le scritture nuove nello stesso posto.

Quest'ultima non è teoria. Sulla stessa raccolta di 60 000 documenti: una chiave hashed sul cliente ha lasciato il 52,11 % su un frammento e il 47,88 % sull'altro; l'_id di ObjectId, che cresce sempre, ha lasciato il 100 % su uno solo, in un unico pezzo.

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

Mirata o diffusa

Una query che porta la chiave va a un frammento e basta. Una che non la porta viene chiesta a tutti e le risposte si fondono: SINGLE_SHARD contro SHARD_MERGE. La differenza misurata è di 121 documenti esaminati contro 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

Le zone, e cambiare chiave

Una zona lega un intervallo della chiave a un frammento, e serve a due cose vere: tenere i dati di un paese su macchine di quel paese, e separare il caldo dal freddo. E dalla 5.0 la chiave si può cambiare con reshardCollection, ma copia la raccolta intera e si sente: su 60 000 documenti stava ancora lavorando dopo due minuti, con la sua raccolta temporanea in bella 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

Tre cose che non sono più vere

Si racconta ancora che un updateOne senza la chiave fallisca, che il valore della chiave non si possa cambiare, e che l'indice debba esistere prima di frammentare. Sulla 8.2 tutte e tre sono passate senza protestare.

Parole chiave: frammentazione, sharding, chiave di frammentazione, hashed, chunk, mongos, server di configurazione, zona, bilanciatore, reshardCollection, SINGLE_SHARD, SHARD_MERGE

Gli otto codici che escono ogni giorno, provocati uno a uno con il testo che restituisce il server, e i due che portano dentro più del numero: l'11000 con la sua chiave e il 121 con il suo errInfo.

Si applica a: MongoDB 7.0+

Un errore di MongoDB porta un numero, quasi sempre un nome, e a volte qualcosa dentro che vale più di entrambi. Questi sono gli otto che escono ogni giorno, provocati uno a uno contro il server e ricopiati così come sono arrivati.

I due senza nome portano qualcosa di meglio

L'11000 e il 121 arrivano senza codeName, e non importa: entrambi portano il dato che serve per aggiustarli. L'11000 nomina la raccolta, l'indice e il valore che ha cozzato, quindi non c'è da indovinare quale di tre indici unici è scattato.

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" }

Il 121 dice «Document failed validation» e nient'altro nel messaggio, ma e.errInfo.details porta la regola infranta con il suo nome e il valore atteso. È la differenza fra «non valido» e «manca 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"] } ] }

I due di permesso sono diversi

Il 18 è «non so chi sei»: password sbagliata, o — il più delle volte — la base di autenticazione sbagliata, perché un utente di MongoDB è il nome più la base in cui è stato creato. Il 13 è «so chi sei e non puoi»; il suo messaggio nomina la base, il comando e persino la raccolta, quindi il ruolo che manca si legge direttamente lì.

E due avvisi di sintassi

Il 50 non è un errore del server ma il tetto che gli hai messo tu con maxTimeMS. E il 26 spunta dove meno te l'aspetti: collMod e renameCollection su qualcosa che non esiste falliscono, ma drop() su una raccolta inesistente restituisce false e basta — non è un errore, quindi uno script che lo dà per scontato non scopre mai di aver sbagliato nome.

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

Parole chiave: errori, codici, 11000, duplicate key, 13, Unauthorized, 18, AuthenticationFailed, 26, NamespaceNotFound, 50, MaxTimeMSExpired, 112, WriteConflict, 121, 251, NoSuchTransaction, errInfo

Gli otto tetti che si toccano davvero, provocati uno a uno contro il server, con il codice che restituisce ciascuno e i tre che non stanno dove dice la loro fama.

Si applica a: MongoDB 7.0+

Tutti questi sono stati provocati contro il server, quindi il numero a sinistra è quello che ha davvero rifiutato, non quello della leggenda.

Quello dei 16 MB è l'unico che si tocca per sbaglio

E quasi sempre per lo stesso motivo: un array che cresce senza freno dentro un documento. Il messaggio porta entrambi i numeri, quello del documento e il massimo, quindi si vede a colpo d'occhio di quanto ha sforato.

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

I due tetti degli indici si contano male

I 64 indici per raccolta includono _id_, quindi ce ne stanno 63 propri; e non è un tetto che si raggiunge in salute: con 64 indici, ogni scrittura mantiene 64 alberi. Nemmeno i 32 campi di un indice composto sono un obiettivo: oltre i sei o sette, quasi sicuramente servono due indici, non uno più largo.

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

Tre che non stanno dove dice la loro fama

L'annidamento è documentato a 100 livelli e quello che il server ha rifiutato è stato il 180°, perché il tetto è del BSON dell'intero comando e l'involucro dell'insert se ne prende una parte. Il nome lungo non fallisce sulla raccolta ma sulla somma di base e raccolta, cioè lo spazio dei nomi. E il tetto sulla dimensione di una chiave d'indice, che nelle vecchie versioni era 1 024 byte, non esiste più: un valore indicizzato di 50 000 è passato.

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

E quello che non ha tetto

Né il numero di raccolte, né quello delle basi, né quello dei documenti di una raccolta. Quello che finisce per primo non sta in questa tabella: è il disco. E per ciò che davvero non sta in 16 MB — un file — c'è GridFS, che lo taglia in pezzi da 255 KB e conserva ogni pezzo come un documento normale.

Parole chiave: limiti, tetti, 16 MB, dimensione del documento, 10334, annidamento, Overflow, indici per raccolta, 67, CannotCreateIndex, 13103, spazio dei nomi, 73, InvalidNamespace, chiave d'indice

Il riassunto del manuale di MongoDB: sette abitudini che valgono la pena, ognuna con la misura che la sostiene, e le cinque righe con cui si prende il polso a un server appena ereditato.

Si applica a: MongoDB 7.0+

Questa è la fine del manuale, e non porta niente di nuovo: raccoglie quello che ogni tema ha dimostrato, nella forma che serve tutti i giorni. Ogni abitudine arriva con il numero che la sostiene, e tutti quei numeri sono stati misurati contro un server vero, non ricopiati.

1. Modella per come leggerai, non per quanto somiglia a una tabella. Quello che si legge insieme si conserva insieme. Il limite di questa regola è duro ed è misurato: un documento non supera i 16 777 216 byte, quindi un array che cresce senza freno finisce in un 10334 un martedì qualunque.

2. Un indice per query frequente, e non uno di più. Ogni indice è un albero da mantenere a ogni scrittura: le stesse 20 000 inserzioni hanno impiegato 60 ms senza indici, 101 con cinque e 160 con dieci. E occupano: nella base d'esempio, una raccolta di 1 148 000 byte di dati ne portava 622 592 di indici.

3. Giudica una query da totalDocsExamined contro nReturned, non dall'orologio. L'orologio dice quanto ci ha messo oggi, con la cache calda e la macchina tranquilla; il rapporto fra quei due numeri dice che cosa succederà quando la raccolta sarà dieci volte più grande.

4. writeConcern: majority per quello che non si può perdere. Su un replica set è già il valore di fabbrica, quindi l'abitudine non è metterlo: è non toglierlo per andare più in fretta.

5. Mai senza autenticazione. È una riga nel file, e l'unica porta che un server appena acceso lascia aperta — creare il primo utente dalla macchina stessa — si chiude da sola non appena quell'utente esiste.

6. Fai il backup con l'oplog, e misura la sua finestra in tempo. mongodump --oplog è ciò che trasforma uno scarico in un istante. E la dimensione dell'oplog non si legge in byte ma in giorni: il nodo misurato ne dava 34, ma dipende da quanto si scrive, quindi è un numero da riguardare quando cambia il carico.

7. Il working set deve stare nella cache. È l'unica regola di prestazione senza trucchi. Nella base d'esempio, 1 150 242 byte di dati contro 3 621 781 504 di cache: spazio di troppo tremila volte. Il giorno in cui non ne avanza, si sente in tutto insieme.

Il polso di un server appena ereditato

Cinque righe rispondono a quello che serve sapere prima di toccare qualcosa: se chiede una password, quando una scrittura si considera scritta, se qualcuno sta guardando le query lente, quanta memoria ha per lavorare e quanti dati deve muovere.

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

E un'altra ancora, quella che mostra dove se ne va il disco e, di passaggio, quale raccolta porta più indice che dati.

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

Parole chiave: buone pratiche, riassunto, modello di accesso, indici, writeConcern, majority, autenticazione, oplog, working set, cache, explain, salute del server

Il tipo vive nel valore e non nella colonna: le cinque affinità e che cosa converte ciascuna, l'ordine fra classi di memorizzazione, che cosa impedisce davvero STRICT, e perché NOCASE non sa nulla di accenti.

Si applica a: SQLite 3.35+

In SQLite il tipo è del valore, non della colonna. Quello che una colonna dichiara è un'affinità: una preferenza applicata al momento di salvare, che converte se può e lascia passare se non può.

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

Eccole tutte e cinque in una riga. INTEGER e REAL convertono il testo che sembra un numero; TEXT converte il numero in testo; NUMERIC guarda il valore e decide, quindi nella stessa colonna stanno un integer e un real; e BLOB è quella senza affinità: conserva quello che le danno, così com'è arrivato.

Le classi di memorizzazione sono cinque, e sono ordinate

Una colonna senza tipo dichiarato è legale e accetta tutte e cinque, quindi in una stessa colonna stanno un intero, un reale, un testo, un blob e un nullo. E si possono ordinare, perché fra le classi c'è un ordine fisso: prima i nulli, poi i numeri, poi il testo e infine i blob. Il che significa che un ORDER BY su una colonna sporca non fallisce: raggruppa per tipo senza dirlo a nessuno.

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 impedisce meno di quanto sembri

Dalla 3.37 una tabella si può dichiarare STRICT, e allora accetta solo una manciata di tipi — INT, INTEGER, REAL, TEXT, BLOB e ANY — e rifiuta ciò che non può conservare. Ma continua a convertire: un '42' entra in una colonna INTEGER perché non si perde nulla, e un 42 entra in una TEXT e si conserva come '42'. Quello che rifiuta è ciò che non ha conversione. E c'è un guadagno inatteso: un tipo inventato, che una tabella normale accetta in silenzio, qui viene rifiutato già alla creazione.

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"

Niente data, niente booleano, e la collazione non sa di accenti

TRUE è un integer di valore 1. Una data è quello che decidi tu: date() restituisce text e julianday() restituisce real, e quello che scegli è quello che ordinerai e confronterai per il resto della sua vita. E ci sono solo tre collazioni — BINARY, NOCASE e RTRIM: NOCASE pareggia maiuscole e minuscole dell'ASCII e nient'altro, quindi café e CAFÉ sono valori diversi e upper('café') restituisce CAFé. Il testo è davvero UTF-8: length conta caratteri, e sul blob conta byte.

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

Parole chiave: tipi, affinità, typeof, INTEGER, REAL, TEXT, BLOB, NUMERIC, tipizzazione dinamica, STRICT, booleano, data, julianday, COLLATE, NOCASE, UTF-8, classe di memorizzazione

L'isolamento è serializzabile perché scrive uno solo: i tre modi di BEGIN, lo SQLITE_BUSY 5 e perché busy_timeout lo risolve, il 517 che non risolve, e che cosa cambia davvero la modalità WAL.

Si applica a: SQLite 3.35+

SQLite non ha livelli di isolamento da scegliere, e non è una mancanza: l'isolamento è serializzabile perché in tutta la base scrive uno solo alla volta. Tutto il resto discende da lì.

Due impostazioni lo governano, ed entrambe arrivano di fabbrica con il valore peggiore possibile.

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;

I tre modi di BEGIN

DEFERRED — quello predefinito — non prende nulla finché non serve: la prima lettura prende una fotografia e la prima scrittura chiede il lucchetto. IMMEDIATE chiede subito il lucchetto di scrittura, nella riga stessa del BEGIN. EXCLUSIVE chiede in più che nessuno legga, e in modalità WAL non fa quasi nulla di diverso da IMMEDIATE. La regola pratica: se la transazione scriverà, BEGIN IMMEDIATE; costa un'attesa all'inizio ed evita l'errore più fastidioso di SQLite, quello più sotto.

SAVEPOINT è il segno intermedio, e ROLLBACK TO ci torna senza chiudere la transazione.

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 è il 5, ed è quasi sempre tuo

Quando un altro tiene il lucchetto di scrittura, la risposta è SQLITE_BUSY con il codice 5 e il testo «database is locked». Non è un guasto: è la coda di una risorsa a corsia unica. A renderlo un guasto è che busy_timeout vale 0 di fabbrica, quindi senza toccarlo la risposta è immediata e secca. Con 800 ms impostati, la stessa chiamata ha aspettato 895 prima di arrendersi.

# 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

Quello che busy_timeout non risolve: il 517

Se una transazione DEFERRED legge e poi vuole scrivere, e nel frattempo qualcuno ha confermato, la fotografia presa in lettura non vale più e SQLite restituisce SQLITE_BUSY_SNAPSHOT, il 517. Aspettare non serve: nessuno restituirà quella fotografia. L'unica uscita è ROLLBACK e ricominciare — o, meglio, aver aperto 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

Che cosa cambia WAL, e che cosa no