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 — 5 byte, più fino a 3 per le frazioni di secondo. Senza fuso orario e senza conversione al salvataggio o alla lettura: restituisce il valore salvato.
- TIMESTAMP — 4 byte, intervallo 1970…2038 (in MySQL, anche nella 8.x) o 1970…2106 (in MariaDB 11.5+). 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 (JSON_UNQUOTE(JSON_EXTRACT(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+.

-- Indice B-tree composto
CREATE INDEX idx_pedidos_cliente_fecha
    ON pedidos (cliente_id, fecha_pedido DESC);

-- Indice full-text
ALTER TABLE articulos
    ADD FULLTEXT INDEX ft_titulo_cuerpo (titulo, cuerpo);

-- Indice spaziale
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.

-- Buono: indice con la colonna più selettiva per prima
CREATE INDEX idx_facturas
    ON facturas (cliente_id, estado, fecha)
    -- cliente_id (alta selectividad) → estado → fecha
;

-- Schema sbagliato: indice ridondante
-- (cliente_id) è già coperto da (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 query legge solo cliente_id e total → l'indice la copre
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

ALTER TABLE pedidos ALTER INDEX idx_legacy IGNORED;
ALTER TABLE pedidos ALTER INDEX idx_legacy NOT IGNORED; -- revertir

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

-- Statistiche d'uso degli indici
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).

-- Sbagliato: tre colonne che ripetono la stessa "entidad"
CREATE TABLE clientes_v1 (
    id   BIGINT PRIMARY KEY,
    nombre  VARCHAR(120),
    tel1 VARCHAR(20),
    tel2 VARCHAR(20),
    tel3 VARCHAR(20)
);

-- Buono: una tabella collegata
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.

-- Sbagliato: nombre_producto si ripete in ogni riga dello stesso prodotto
CREATE TABLE detalle_pedido_v1 (
    pedido_id        BIGINT,
    producto_id      BIGINT,
    cantidad         INT,
    nombre_producto  VARCHAR(120),
    PRIMARY KEY (pedido_id, producto_id)
);

-- Giusto: nombre_producto vive nella tabella 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.

-- Esempio: memorizzare il totale dell'ordine per evitare SUM a ogni lettura
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".

-- Tutti i clienti, che abbiano fatto ordini o no
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 ordine invertito
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 tutte le combinazioni (cliente, mese) per un report
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)
);

-- Ogni dipendente con il nome del suo responsabile
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.

-- Emulazione in 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: clienti con almeno un ordine
SELECT c.id, c.nombre
FROM clientes c
WHERE EXISTS (SELECT 1 FROM pedidos p WHERE p.cliente_id = c.id);

-- Anti-join: clienti senza ordini
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';

-- Cambiare a caldo (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 come 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;
-- Anche il client deve passare il parametro
-- (da riga di comando: --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 in MySQL 8.0 e in MariaDB, e 10 000 / 20 000 nel MySQL attuale (misurato sulla 26.7).

- 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; -- si applica solo al riavvio
RESET PERSIST innodb_buffer_pool_size;   -- togliere la persistenza

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 nel server e 1 017 in InnoDB, 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+).

-- Controllare la dimensione delle tabelle
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.

-- Valido ma sconsigliato
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 Windows.
- 2 — vengono memorizzati così come sono ma i confronti ignorano il case. Solo macOS/Windows; default su macOS.

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

-- Dump logico con consistenza transazionale
-- (dalla shell, non 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.

-- Creare un utente applicativo con permessi limitati
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.%.%';

-- MAI in produzione
-- 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.18+; in MariaDB, ANALYZE e basta)

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: il pulsante Visual EXPLAIN della barra dell'Editor SQL, sulla query selezionata.

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.

-- Prime 10 query per tempo totale accumulato
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;

-- Tabelle con più 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 (1 − Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests). Obiettivo: >99 %.

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

-- Senza performance_schema (spento di fabbrica in MariaDB), i due contatori a mano:
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

- Adaptive hash index — hash automatico sulle pagine calde del buffer pool. Attivo di fabbrica in MySQL 8.0, spento nel MySQL attuale e in MariaDB (misurato: 1 su 8.0.46; 0 su 26.7 e su MariaDB 13.0).
- 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 in MySQL 8.0+ (MariaDB non ha data_locks, lì sono information_schema.INNODB_LOCK_WAITS e INNODB_TRX):

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 17+) — 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.

Dall'Editor SQL 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 nell'Editor SQL.

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 nell'Editor SQL, 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
L'Editor SQL 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

Interi con la dimensione nel nome e l'UInt64 che non sta in JSON, Decimal esatto, String come byte, gli intervalli delle date, e Nullable e LowCardinality come involucri che costano.

Si applica a: ClickHouse 25.3+

In ClickHouse il tipo decide quanto spazio occupa ogni valore su disco e come si comprime, quindi lo si sceglie con più cura che in un database transazionale. I nomi contengono la dimensione e distinguono maiuscole e minuscole: UInt64, non uint64.

Interi: la dimensione è nel nome
Da Int8 a Int256 e da UInt8 a UInt256. Uscire dall'intervallo non è un errore: il valore ricomincia dal fondo. toInt8(300) restituisce 44 e toUInt8(-1), 255.

Il massimo di un UInt64 è 18446744073709551615, e in JSON un numero è esatto solo fino a 9007199254740991 (2^53 − 1). Per questo il server invia gli interi a 64 bit tra virgolette nei suoi formati JSON (output_format_json_quote_64bit_integers), e Calíope li legge come testo esatto, mai come numero in virgola mobile.

Decimal: esatto, con scala fissa
Decimal(P, S) conserva P cifre di cui S decimali, senza gli arrotondamenti della virgola mobile. La divisione scarta ciò che non sta nella scala: toDecimal32(1, 2) / 3 dà 0.33. La moltiplicazione somma le scale: due Decimal(9, 2) danno un Decimal(9, 4).

String sono byte, non caratteri
String accetta qualsiasi sequenza di byte, anche una che non è UTF-8. length('ñandú') dà 7 (byte) e lengthUTF8('ñandú'), 5. FixedString(N) riempie con zeri: toFixedString('ab', 4) conserva 61 62 00 00. Quando un testo non è UTF-8 valido, Calíope lo mostra sanificato e segna la cella.

Date: intervalli diversi, e nessun errore quando si esce

Fuori dal suo intervallo, Date resta al limite senza avvisare: toDate('2149-06-07') restituisce 2149-06-06 e toDate('1969-12-31'), 1970-01-01. Per date di nascita o storiche si usa Date32.

Il fuso orario appartiene alla colonna, non a ogni riga: DateTime64(3, 'Asia/Tokyo'). Senza fuso vale quello del server, e Calíope lo scrive nell'intestazione della colonna.

Nullable e LowCardinality: involucri con costi diversi
Nullable(T) tiene a parte una maschera dei nulli. Su un milione di righe, Int64 ha occupato 7,63 MiB non compresso e Nullable(Int64), 8,58 MiB. Inoltre una colonna Nullable non può stare nella chiave di ordinamento senza attivare allow_nullable_key. Se l'assenza si può esprimere con un valore predefinito (0, stringa vuota), è meglio non usare Nullable.

LowCardinality(T) codifica con un dizionario. Con 100 valori distinti in un milione di righe, la colonna String ha occupato 254 KiB compressa e la LowCardinality(String), 7,6 KiB. La documentazione dello stesso server lo consiglia sotto circa 10 000 valori distinti e lo sconsiglia sopra 100 000.

Collezioni e tipi strutturati
- Array(T) parte da 1, e fuori intervallo restituisce il valore predefinito, non un errore: [10,20,30][5] dà 0.
- Map(K, V) ammette chiavi ripetute: map('a',1,'a',2) le conserva entrambe, e ['a'] restituisce la prima.
- Enum8 rifiuta all'inserimento un nome che non è nella sua lista. UUID occupa 16 byte e IPv4, 4.

Calíope mostra le collezioni come JSON compatto, con i numeri esattamente come sono arrivati, e uno stato di aggregazione (AggregateFunction) dice che non si può mostrare come testo.

Parole chiave: tipi, uint64, int64, decimal, string, fixedstring, date, date32, datetime, datetime64, fuso orario, nullable, lowcardinality, array, map, tuple, enum, uuid, ipv4, json, clickhouse

La famiglia MergeTree e cosa fa ogni variante durante l'unione, le Replicated, i motori che non conservano dati o li tengono in memoria, e perché il motore non si può cambiare.

Si applica a: ClickHouse 25.3+

In ClickHouse ogni tabella dichiara il suo motore, e il motore decide come si conservano i dati, quali indici accetta, se si replica e cosa succede durante l'unione. Non è un dettaglio di prestazioni: è parte dell'identità della tabella. Il server pubblica gli 84 che conosce in system.table_engines, ciascuno con ciò che supporta.

La famiglia MergeTree, quella di quasi sempre
I dati si scrivono in parti ordinate per la chiave di ordinamento, e il server le unisce in background. Le varianti fanno qualcosa in più durante l'unione, e solo allora:

La trappola è quel «solo allora». Misurato con ReplacingMergeTree: due inserimenti della stessa chiave danno 2 righe in un SELECT normale; con FINAL ne danno 1, e dopo OPTIMIZE TABLE … FINAL, 1 senza FINAL. SummingMergeTree si comporta allo stesso modo: due righe ('x', 1) e ('x', 2) restano due fino all'unione, e poi una con 3. Le query su questi motori devono aggregare o usare FINAL, perché l'unione non ha un orario.

Replicated, la stessa famiglia con copie
ReplicatedMergeTree e le sue varianti (ReplicatedReplacingMergeTree…) aggiungono repliche coordinate da ClickHouse Keeper. Sono gli unici della lista che supportano la replica e la deduplicazione degli inserimenti (supports_replication, supports_deduplication).

Quelli che non sono MergeTree
- Memory: in RAM e non compresso. Secondo la sua descrizione sul server, al riavvio la tabella resta vuota.
- Log e famiglia: piccole tabelle a scrittura unica, senza indici.
- Buffer: trattiene in memoria e scarica in un'altra tabella; oggi si consigliano invece gli inserimenti asincroni.
- Distributed: non conserva nulla; distribuisce la query tra gli shard di un cluster.
- Merge: legge più tabelle insieme, e non accetta scritture.
- Dictionary: mostra un dizionario come tabella.
- Null: ciò che si scrive viene scartato. Serve da ingresso a una vista materializzata.

Nessuno di questi accetta indici di salto, proiezioni, chiave di ordinamento né TTL: secondo system.table_engines, tutto ciò appartiene solo alla famiglia MergeTree.

Il motore non si cambia
Non esiste ALTER TABLE … MODIFY ENGINE: il server risponde con un errore di sintassi. Per cambiarlo si crea un'altra tabella con il nuovo motore e si copiano i dati con INSERT INTO … SELECT, oppure si copiano partizioni con ATTACH PARTITION … FROM se colonne e partizionamento coincidono: misurato, da un ReplacingMergeTree a un MergeTree.

In Calíope
Il Builder offre i motori che il server può creare senza argomenti, e quando si modifica una tabella dice che il motore non si può cambiare. Cataloghi mostra il motore di ogni tabella.

Parole chiave: motore di tabella, engine, mergetree, replacingmergetree, summingmergetree, aggregatingmergetree, collapsingmergetree, replicated, memory, log, buffer, distributed, dictionary, merge, null, final, clickhouse

La chiave di ordinamento come indice sparso di granuli, la chiave primaria quando è diversa, SAMPLE BY, gli indici di salto e le proiezioni, e perché la chiave non si può cambiare.

Si applica a: ClickHouse 25.3+

In ClickHouse non ci sono indici che puntano a una riga. Quanto si legge lo decide la chiave di ordinamento: i dati si conservano ordinati in base a essa, e l'indice primario è sparso: tiene un segno per ogni blocco di righe, non uno per riga.

I granuli: l'unità di lettura
Ogni blocco si chiama granulo e, per impostazione predefinita, ha 8 192 righe (index_granularity). Il server scarta granuli interi e legge il resto. Su una tabella di un milione di righe con ORDER BY (fecha, cliente, id) ci sono 123 granuli, ed EXPLAIN lo dice:

EXPLAIN indexes = 1
SELECT count() FROM eventos WHERE fecha = '2026-02-01';

Con la prima colonna della chiave, il piano mostra Granules: 3/123 e una ricerca binaria. Filtrando solo per cliente, la seconda colonna, mostra Granules: 101/123: quasi tutta la tabella. L'ordine delle colonne nella chiave conta: prima quelle filtrate più spesso e con meno valori distinti.

La chiave primaria può essere più corta di quella di ordinamento
PRIMARY KEY deve essere un prefisso di ORDER BY. Se non la si scrive, coincidono. Con ORDER BY (fecha, cliente, id) PRIMARY KEY (fecha, cliente) le righe si ordinano per le tre colonne e l'indice in memoria ne tiene solo due: meno memoria, lo stesso ordine su disco. La chiave primaria non è univoca: due righe con la stessa chiave convivono.

SAMPLE BY
Permette di interrogare una frazione dei dati (SELECT … SAMPLE 0.1). L'espressione di campionamento deve stare nella chiave primaria, altrimenti il server rifiuta il CREATE, e di solito è un hash: ORDER BY intHash32(id) SAMPLE BY intHash32(id). Misurato: SAMPLE 0.1 su 100 000 righe ne ha contate 10 039.

Indici di salto: scartano, non cercano
Tengono un riepilogo per gruppo di granuli e servono a saltare quelli che di sicuro non contengono ciò che si cerca:
- minmax: il minimo e il massimo.
- set(N): fino a N valori distinti.
- bloom_filter: appartenenza approssimata; per l'uguaglianza su colonne fuori dalla chiave.
- ngrambf_v1 e tokenbf_v1: per cercare frammenti o parole dentro un testo.

Misurato sulla stessa tabella: cercare un URL preciso con un bloom_filter ha letto 20 granuli su 123 invece di tutti i 123. E un minmax su un importo distribuito in tutta la tabella non ne ha scartato nessuno: un indice di salto serve solo se i valori sono raggruppati lungo la chiave.

Proiezioni: la tabella di nuovo, in un altro ordine
Una proiezione conserva di nuovo i dati, ordinati in un altro modo, e il server la sceglie da solo quando conviene:

ALTER TABLE eventos ADD PROJECTION por_cliente (SELECT * ORDER BY cliente);
ALTER TABLE eventos MATERIALIZE PROJECTION por_cliente;

La stessa query per cliente è passata da 101 a 1 granulo su 123. In cambio, la proiezione ha occupato 5,3 MiB in più, e ogni inserimento scrive i dati due volte.

La chiave di ordinamento non si può cambiare
ALTER TABLE … MODIFY ORDER BY ammette solo l'aggiunta in fondo di colonne create nello stesso ALTER. Riordinare, o inserire una colonna che esisteva già, è un errore (Existing column … is used in the expression that was added to the sorting key). Cambiare davvero la chiave significa creare un'altra tabella e copiare i dati.

In Calíope
In ClickHouse la scheda «Indici» di Cataloghi si chiama «Chiavi» e mostra la chiave di ordinamento, quella di partizione, la primaria se è diversa, quella di campionamento, gli indici di salto e le proiezioni. L'EXPLAIN visuale conta i granuli letti da ogni lettura, e il consulente degli indici propone indici di salto, non CREATE INDEX.

Parole chiave: chiave di ordinamento, order by, primary key, indice primario, granulo, index_granularity, sample by, indice di salto, skip index, minmax, set, bloom_filter, ngrambf_v1, tokenbf_v1, proiezione, projection, explain, clickhouse

PARTITION BY e le parti, perché partizionare per giorno è un errore comune, OPTIMIZE e DROP PARTITION, e il TTL di righe e colonne, che si applica durante l'unione.

Si applica a: ClickHouse 25.3+

In ClickHouse una partizione è un gruppo di parti che condividono il valore di PARTITION BY, e il suo compito principale è la gestione dei dati: eliminare o spostare un mese intero in un colpo. Per accelerare le query c'è già la chiave di ordinamento; partizionare non la sostituisce.

Parti e partizioni
Ogni inserimento scrive almeno una parte per ogni partizione che tocca, e il server le unisce dopo, sempre all'interno della stessa partizione. system.parts le elenca, e contano solo quelle con active = 1: quelle unite restano inattive per un po' prima di essere eliminate.

CREATE TABLE ventas (fecha Date, id UInt64, importe Decimal(10, 2))
ENGINE = MergeTree
PARTITION BY toYYYYMM(fecha)
ORDER BY (fecha, id);

Misurato: due inserimenti di 300 000 righe distribuite su nove mesi hanno lasciato 18 parti attive in 9 partizioni.

Perché partizionare per giorno è un errore comune
Molte partizioni significano molte parti piccole, e le parti costano: oltre 1 000 attive in una partizione il server comincia a rallentare gli inserimenti (parts_to_delay_insert), e a 3 000 li rifiuta (parts_to_throw_insert). Inoltre un solo INSERT non può toccare più di 100 partizioni (max_partitions_per_insert_block). Misurato con PARTITION BY fecha: un INSERT con 200 giorni diversi è fallito con Code: 252 … Too many partitions for single INSERT block (more than 100). La scelta abituale è per mese (toYYYYMM), e nessuna se la tabella è piccola.

Unire ed eliminare per partizione

OPTIMIZE TABLE ventas PARTITION 202603 FINAL;
ALTER TABLE ventas DROP PARTITION 202601;

OPTIMIZE … PARTITION … FINAL unisce in una sola le parti di quella partizione. DROP PARTITION elimina la partizione intera togliendone i file: misurato, 5 ms. Eliminare il mese successivo con ALTER TABLE … DELETE WHERE ha richiesto 54 ms, perché una mutazione riscrive le parti invece di lasciarle andare, e la differenza cresce con le dimensioni.

TTL: scadenza applicata durante l'unione

CREATE TABLE ventas (
    fecha Date, id UInt64,
    nota String TTL fecha + INTERVAL 30 DAY
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(fecha)
ORDER BY (fecha, id)
TTL fecha + INTERVAL 1 YEAR;

Il TTL della tabella elimina le righe scadute, e quello di una colonna riporta il suo valore al predefinito. Entrambe le cose avvengono durante l'unione, non all'inserimento né all'ora esatta. Misurato: una riga di due anni fa, inserita in quella tabella, c'era ancora alla lettura, ed è sparita dopo OPTIMIZE … FINAL. Senza forzarlo, il server controlla la scadenza ogni 4 ore (merge_with_ttl_timeout), quindi un TTL non è un'eliminazione puntuale: i dati scaduti possono restare visibili per un po'.

In Calíope
La scheda «Chiavi» di Cataloghi mostra la chiave di partizione di ogni tabella. Informazioni server, in «Utilizzo HDD», somma le parti attive di system.parts e mostra la compressione. Manutenzione tabelle offre OPTIMIZE limitato a una partizione, con FINAL disattivato per impostazione predefinita perché riscrive l'intera partizione.

Parole chiave: partizione, partition by, parti, system.parts, active, parts_to_throw_insert, max_partitions_per_insert_block, optimize, drop partition, ttl, scadenza, toyyyymm, unione, clickhouse

Un trigger di inserimento, non una tabella in cache: TO contro la tabella interna, POPULATE, le viste aggiornabili con system.view_refreshes, e perché non vedono né UPDATE né DELETE.

Si applica a: ClickHouse 25.3+

Una vista materializzata di ClickHouse non è una query salvata con il risultato in cache. È un trigger di inserimento: ogni volta che un blocco di righe arriva nella tabella di origine, la vista esegue il suo SELECT su quel blocco e scrive il risultato in una tabella di destinazione. Ciò che c'era prima, e ciò che cambia dopo, non lo vede.

Con TO, o con una tabella interna

CREATE TABLE totales (k String, total UInt64)
ENGINE = SummingMergeTree ORDER BY k;

CREATE MATERIALIZED VIEW mv_totales TO totales AS
SELECT k, sum(v) AS total FROM origen GROUP BY k;

Con TO, la destinazione è una tabella normale, creata prima e con il suo nome. Senza TO, la vista porta il suo ENGINE e il server crea una tabella nascosta chiamata .inner_id.<uuid>, con l'uuid della vista. Misurato: la vista senza TO ha creato .inner_id.ed264ea3-…, e il suo uuid in system.tables era proprio quello. Con TO si sa dove stanno i dati e si può eliminare la vista senza perderli, quindi è il modo consigliato.

Ciò che c'era prima non entra (salvo con POPULATE)
Misurato: con due righe già nell'origine, la vista con TO e quella con tabella interna sono nate vuote. Quella creata con POPULATE è nata con entrambe. Dopo, un nuovo inserimento è arrivato a tutte e tre. La documentazione di ClickHouse avverte che ciò che si inserisce nell'origine mentre POPULATE riempie la vista può andare perso; con traffico, è meglio creare la vista e riempire la destinazione a mano con INSERT INTO … SELECT.

Le mutazioni: la vista non se ne accorge
Una vista reagisce solo agli inserimenti. Misurato: dopo ALTER TABLE origen UPDATE v = 1000 WHERE k = 'a', l'origine sommava 2 000 per a e la destinazione restava a 10. Dopo un DELETE delle righe di c, l'origine le ha perse e la destinazione ha conservato il suo totale di 5. Se si corregge l'origine, la destinazione si ricalcola a mano.

Anche la destinazione si unisce
Se la destinazione è un SummingMergeTree o un AggregatingMergeTree, ogni inserimento lascia righe parziali che si riuniscono durante l'unione. Le query sulla destinazione aggregano (sum(total), -Merge) invece di leggere righe singole.

Le viste aggiornabili: l'altro tipo

CREATE MATERIALIZED VIEW resumen
REFRESH EVERY 1 MINUTE
ENGINE = MergeTree ORDER BY k AS
SELECT k, count() AS c FROM origen GROUP BY k;

Una vista con REFRESH funziona davvero come una tabella in cache: rifà l'intera query a ogni intervallo e sostituisce il risultato, quindi vede tutta l'origine, comprese le modifiche fatte con mutazioni. system.view_refreshes ne indica lo stato (Scheduled, Running…), l'ultimo aggiornamento riuscito e il prossimo. Misurato su ClickHouse 26.8: funziona senza alcuna impostazione sperimentale.

In Calíope
In ClickHouse, Cataloghi ha la scheda «Viste materializzate»: da quale tabella legge ciascuna, in quale scrive (e se è quella interna, con quale nome interrogarla), dimensione e righe della destinazione, l'ultima riga scritta e, se è aggiornabile, la sua programmazione e i suoi aggiornamenti. L'albero dell'editor le raggruppa a parte rispetto alle viste normali.

Parole chiave: vista materializzata, materialized view, to, inner_id, populate, refresh every, aggiornabile, view_refreshes, trigger, summingmergetree, mutazione, clickhouse

UPDATE e DELETE come riscrittura asincrona delle parti, system.mutations e il motivo dell'errore, KILL MUTATION, mutations_sync, il DELETE leggero e perché una mutazione non conta le righe.

Si applica a: ClickHouse 25.3+

ClickHouse non modifica una riga sul posto. ALTER TABLE … UPDATE e ALTER TABLE … DELETE sono mutazioni: il server le registra, risponde subito e poi riscrive in background ogni parte interessata, per intero, anche se cambia una sola riga.

Cosa succede quando la si lancia

ALTER TABLE eventos UPDATE estado = 'cerrado' WHERE id < 10;
SELECT mutation_id, parts_to_do, is_done FROM system.mutations WHERE table = 'eventos';

Misurato su una tabella con due parti da 50 000 righe: l'istruzione è tornata in 10 ms con parts_to_do = 2 e is_done = 0. Alla fine, le parti all_1_1_0 e all_2_2_0 erano state sostituite da all_1_1_0_3 e all_2_2_0_3: 50 000 righe riscritte in ciascuna per cambiarne dieci. Le parti vecchie restano sul disco, inattive, finché non passa old_parts_lifetime (480 s per impostazione predefinita).

Aspettare o no
mutations_sync vale 0 per impostazione predefinita: l'istruzione non aspetta. Con 1 aspetta la replica corrente e con 2 tutte. Misurato: con mutations_sync = 2, la SELECT successiva vedeva già il valore nuovo.

Quando una mutazione fallisce
Una mutazione che fallisce non viene annullata né scartata: continua a essere ritentata e blocca quelle che la seguono su quella tabella. Misurato: dopo una mutazione con una divisione per zero, la successiva è rimasta non applicata finché la prima non è stata rimossa. Il motivo è in system.mutations.latest_fail_reason (Code: 153 … Division by zero), e la si rimuove con:

KILL MUTATION WHERE database = 'demo' AND table = 'eventos' AND NOT is_done SYNC;

Ciò che la mutazione aveva già riscritto non torna indietro.

Una mutazione non conta le righe
Il server non conta le righe interessate quando mette in coda una mutazione: in system.query_log compare con written_rows = 0. Qualsiasi numero di righe mostrato per una mutazione sarebbe inventato.

Il DELETE leggero
DELETE FROM tabella WHERE … non riscrive le parti: segna le righe come eliminate, le nasconde nelle query, e vengono rimosse davvero alla fusione successiva. Misurato: su 100 000 righe, DELETE FROM … WHERE id < 3 ne ha lasciate 99 997 visibili, e con apply_deleted_mask = 0 c'erano ancora tutte le 100 000. lightweight_deletes_sync vale 2 per impostazione predefinita: aspetta che il segno arrivi a tutte le repliche.

In Calíope
Modificare una cella o eliminare una riga su ClickHouse scrive ALTER TABLE … UPDATE o ALTER TABLE … DELETE, e l'editor dice che la mutazione è stata messa in coda, con il suo identificativo, invece di un numero di righe. Processi mostra le mutazioni non terminate, con il motivo dell'errore in testo, e permette di annullarle.

Parole chiave: mutazione, mutation, alter table update, alter table delete, system.mutations, latest_fail_reason, kill mutation, mutations_sync, delete from, eliminazione leggera, lightweight delete, lightweight_deletes_sync, parts_to_do

Non ci sono transazioni: cosa garantisce un INSERT, perché non è atomico tra blocchi, la deduplicazione dei blocchi e come rendere idempotente un caricamento senza BEGIN.

Si applica a: ClickHouse 25.3+

ClickHouse non ha transazioni. BEGIN TRANSACTION risponde Code: 48 … Transactions are not supported (NOT_IMPLEMENTED), e lo stesso vale per implicit_transaction = 1. Non ci sono COMMIT né ROLLBACK: ogni istruzione vale per sé.

Cosa garantisce un INSERT
Un INSERT si scrive per blocchi (max_insert_block_size, 1 048 449 righe per impostazione predefinita), e ogni blocco è atomico: entra intero o non entra. Tra un blocco e l'altro non c'è garanzia. Misurato: un INSERT … SELECT di 400 000 righe in blocchi da 100 000 fallito alla riga 250 000 ha lasciato 100 000 righe scritte. Un INSERT che fallisce può lasciare la tabella a metà, e niente lo annulla.

Deduplicazione dei blocchi
Le tabelle Replicated* ricordano l'hash degli ultimi blocchi inseriti (replicated_deduplication_window, 10 000) e scartano un blocco identico che arriva di nuovo. Misurato: lo stesso INSERT due volte in una ReplicatedMergeTree ha lasciato 2 righe, non 4. In una MergeTree normale la finestra è 0 (non_replicated_deduplication_window), e lo stesso INSERT due volte ne ha lasciate 4.

Come rendere idempotente un caricamento
- Ripetere il lotto con gli stessi blocchi: su Replicated*, la deduplicazione scarta la ripetizione.
- Dare un nome al lotto con insert_deduplication_token. Misurato su una MergeTree con finestra 100: un secondo INSERT con lo stesso token e altri dati non è entrato.
- Caricare in una tabella di appoggio e sostituire l'intera partizione in un colpo con ALTER TABLE … REPLACE PARTITION … FROM (secondo la documentazione del server).
- Usare ReplacingMergeTree e leggere con FINAL quando le righe ripetute sono tollerabili fino alla fusione successiva.

In Calíope
Su ClickHouse l'editor non offre il menu «Transazione»: non c'è nulla da aprire né da annullare, e ogni istruzione è fatta appena viene eseguita.

Parole chiave: transazione, transaction, begin, commit, rollback, atomicità, insert, blocco, max_insert_block_size, deduplicazione, insert_deduplicate, insert_deduplication_token, replicated_deduplication_window, idempotente, not_implemented

PREWHERE, il costo di FINAL, i granuli letti nell'EXPLAIN, max_threads e max_memory_usage, system.query_log con log_comment, e le tre query che non usano la chiave.

Si applica a: ClickHouse 25.3+

Su ClickHouse quasi tutte le prestazioni dipendono da quanto si legge: quanti granuli la chiave scarta e quante colonne bisogna leggere.

I granuli nell'EXPLAIN

EXPLAIN indexes = 1
SELECT count() FROM eventos WHERE fecha BETWEEN '2026-02-01' AND '2026-02-03';

Su un milione di righe con ORDER BY (fecha, cliente, id), il piano ha indicato Granules: 5/123: su 123 granuli ne ha letti 5. Quel rapporto è la prima cosa da guardare.

Le tre query che non usano la chiave
Misurato sulla stessa tabella:
- Filtrare su una colonna che non è nella chiave (url LIKE '%/p/12%'): 123/123.
- Un OR che mescola una colonna della chiave con una che non lo è (fecha = … OR url = …): 123/123.
- Filtrare solo sulla seconda colonna della chiave (cliente = 42): 101/123.

PREWHERE
Con PREWHERE, il server legge prima le colonne del filtro e legge le altre solo per le righe che passano. optimize_move_to_prewhere vale 1 e lo fa da solo. Misurato con un filtro su url che restituisce 20 righe e una colonna da 200 byte nella SELECT: 30 MiB letti con l'ottimizzazione e 204 MiB senza.

FINAL ha un costo
FINAL fonde al volo le righe ripetute di una ReplacingMergeTree. Misurato su due milioni di righe con un milione di chiavi: sum(v) senza FINAL ha impiegato 42 ms e sommava i duplicati; con FINAL, 323 ms e 42 MiB di memoria invece di 5,5.

Thread e memoria
max_threads vale auto(N), uno per core (8 sul server misurato). max_memory_usage vale 0, senza limite per query; con un limite, la query che lo supera fallisce con Code: 241 … Query memory limit exceeded.

system.query_log
Ogni query vi lascia la durata, read_rows, read_bytes e memory_usage, e log_comment la marca per ritrovarla:

SELECT … SETTINGS log_comment = 'informe-diario';
SYSTEM FLUSH LOGS;
SELECT query_duration_ms, read_rows, formatReadableSize(read_bytes), formatReadableSize(memory_usage)
FROM system.query_log WHERE type = 'QueryFinish' AND log_comment = 'informe-diario';

In Calíope
L'EXPLAIN visuale mostra i granuli letti da ogni lettura, in rosso quando non ne scarta nessuno. Le Query lente vengono da system.query_log, con i byte letti e la memoria accanto al tempo, e ciò che lancia Calíope stesso è marcato con log_comment per poterlo nascondere.

Parole chiave: prestazioni, performance, prewhere, final, granulo, granule, explain, indexes, max_threads, max_memory_usage, query_log, read_bytes, memory_usage, log_comment, chiave di ordinamento

Le fusioni in background, system.merges e system.parts, OPTIMIZE rispetto a OPTIMIZE FINAL, le soglie delle parti e perché «troppe parti» è l'errore operativo più comune.

Si applica a: ClickHouse 25.3+

Ogni INSERT crea una nuova parte sul disco. In background, il server fonde le parti piccole in parti più grandi: è ciò che tiene la tabella in poche parti e che applica ReplacingMergeTree, i TTL e le eliminazioni leggere.

Guardare le parti e le fusioni

SELECT partition, name, rows, active FROM system.parts WHERE table = 'eventos';
SELECT table, elapsed, progress, num_parts, result_part_name FROM system.merges;

Una parte inattiva è una parte già entrata in una fusione: resta sul disco finché non passa old_parts_lifetime (480 s) e poi viene eliminata.

OPTIMIZE e OPTIMIZE FINAL
OPTIMIZE TABLE chiede una fusione adesso; OPTIMIZE TABLE … FINAL la forza fino a lasciare una parte per partizione, anche se ce n'era già una. Misurato: con le fusioni ferme e cinque INSERT, cinque parti; OPTIMIZE le ha portate a 1, perché erano piccole, e un OPTIMIZE … FINAL su quell'unica parte l'ha riscritta per intero. FINAL riscrive la tabella: su una tabella grande è molto lavoro.

Troppe parti
Se gli INSERT creano parti più in fretta di quanto vengano fuse, il server prima rallenta e poi rifiuta:
- parts_to_delay_insert (1 000 per impostazione predefinita): da lì in poi ritarda ogni INSERT.
- parts_to_throw_insert (3 000): da lì in poi lo rifiuta.

Misurato con la soglia abbassata a 4: il quinto INSERT è fallito con Code: 252 … Too many parts … Merges are processing significantly slower than inserts (TOO_MANY_PARTS). È l'errore operativo più comune, e quasi sempre nasce dall'inserire riga per riga o in lotti molto piccoli, o dal partizionare troppo fine. La soluzione è inserire in lotti di migliaia di righe. Sul server misurato (26.8), async_insert è attivo per impostazione predefinita e, secondo la sua documentazione, riunisce in una parte gli INSERT piccoli che arrivano insieme; tre INSERT eseguiti uno dopo l'altro hanno comunque prodotto tre parti.

In Calíope
Manutenzione tabelle offre OPTIMIZE con l'opzione FINAL, che parte disattivata e avvisa prima di eseguire perché riscrive la tabella, e permette di limitarlo a una partizione. Informazioni server mostra dimensione e compressione di ogni tabella, sommate dalle sue parti attive.

Parole chiave: fusione, merge, optimize, optimize final, system.merges, system.parts, parte, part, parte inattiva, old_parts_lifetime, parts_to_delay_insert, parts_to_throw_insert, too many parts, too_many_parts, async_insert

ReplicatedMergeTree e Keeper, le macro {shard} e {replica}, system.replicas, SYNC e RESTART REPLICA, cosa succede senza Keeper, DROP TABLE … SYNC e system.clusters.

Si applica a: ClickHouse 25.3+

Su ClickHouse la replica è per tabella: ogni tabella Replicated* si coordina con le sue copie tramite Keeper (o ZooKeeper), che tiene traccia delle parti esistenti e di cosa resta da copiare. Non c'è un primario: qualsiasi replica accetta scritture.

Creare una tabella replicata

CREATE TABLE ventas (id UInt32, v String)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/ventas', '{replica}')
ORDER BY id;

Il percorso identifica la tabella in Keeper ed è lo stesso su tutte le sue repliche; {shard} e {replica} sono macro che ogni server compila dalla propria configurazione (system.macros). Nella coppia misurata, entrambe le repliche hanno shard = 01, e replica vale r1 su una e r2 sull'altra. La tabella si crea su ogni replica, e le righe arrivano da sole.

system.replicas

SELECT table, is_readonly, is_session_expired, absolute_delay, queue_size, active_replicas, total_replicas FROM system.replicas;

- absolute_delay: secondi di ritardo rispetto alla replica più aggiornata.
- queue_size: attività in sospeso (copiare parti, fondere, mutare).
- is_readonly e is_session_expired: la replica ha perso Keeper.

Misurato: con la copia ferma su r2 (SYSTEM STOP FETCHES) e una riga inserita su r1, r2 è rimasta a 2 righe con queue_size = 1; ripresa la copia, 3.

SYNC e RESTART REPLICA
SYSTEM SYNC REPLICA tabella aspetta che la replica svuoti la coda; misurato, è tornata con tutte e 3 le righe aggiornate. SYSTEM RESTART REPLICA tabella rilegge il suo stato da Keeper; dopo, is_readonly = 0 e active_replicas = 2.

Senza Keeper, sola lettura
Misurato fermando Keeper: la tabella è passata a is_readonly = 1 e is_session_expired = 1, le letture hanno continuato a funzionare e un INSERT ha aspettato 120 s ed è fallito con TIMEOUT_EXCEEDED. Al ritorno di Keeper, la replica si è ripresa da sola.

DROP TABLE … SYNC
Quando si elimina una tabella replicata senza SYNC, la sua traccia in Keeper viene pulita più tardi, e ricrearla subito fallisce con Code: 253 … Replica … already exists (REPLICA_ALREADY_EXISTS) (misurato). Con DROP TABLE … SYNC, l'istruzione aspetta quella pulizia.

system.clusters
I cluster si dichiarano nella configurazione del server, non si creano con SQL, e system.clusters li elenca con i loro shard e repliche. Nel testbed, caliope_par ha uno shard con due repliche: clickhouse-r1 e clickhouse-r2.

In Calíope
Su ClickHouse, il Monitor della replica va tabella per tabella: repliche attive sul totale, quelle mancanti per nome, il ritardo e la coda, e la perdita di Keeper come stato a parte. Topologia disegna ciò che dichiara system.clusters.

Parole chiave: replica, replicazione, replication, replicatedmergetree, keeper, zookeeper, macro, shard, system.replicas, absolute_delay, queue_size, is_readonly, sync replica, restart replica, system.clusters, replica_already_exists

Gli utenti dei file del server rispetto a quelli creati con SQL, i ruoli, GRANT e le revoche parziali, le quote, i profili di impostazioni, le politiche di riga e perché la password non va nell'URL.

Si applica a: ClickHouse 25.3+

ClickHouse tiene gli utenti in due posti, e da quale dei due viene un utente decide che cosa se ne può fare.

Due archivi di utenti

SELECT name, storage, auth_type FROM system.users;

- users_xml: quelli dichiarati nei file del server (users.xml e users.d/). Si cambiano solo modificando quei file: ALTER USER su uno di loro fallisce con Code: 495 … ACCESS_STORAGE_READONLY (misurato).
- local_directory: quelli creati con CREATE USER. Si gestiscono con SQL e il server li salva sul suo disco.

Per creare utenti con SQL serve un account con la gestione degli accessi (access_management nella sua configurazione, o il permesso ACCESS MANAGEMENT).

Ruoli, GRANT e revoche parziali

CREATE ROLE lectura;
GRANT SELECT ON demo.* TO lectura;
REVOKE SELECT ON demo.eventos FROM lectura;
GRANT lectura TO ana;
SET DEFAULT ROLE lectura TO ana;

Un REVOKE su una tabella di un database concesso per intero non toglie il permesso sul database: lascia una revoca parziale. SHOW GRANTS FOR lectura mostra le due righe, e system.grants segna la seconda con is_partial_revoke = 1 (misurato). Con quel ruolo, leggere demo.productos funziona e leggere demo.eventos fallisce con Code: 497 … ACCESS_DENIED.

Quote, profili e politiche di riga
- Quote (CREATE QUOTA, system.quotas, system.quota_usage): limiti di query, righe o tempo per intervallo. Secondo la documentazione del server, superarle fa rifiutare la query con QUOTA_EXCEEDED.
- Profili di impostazioni (CREATE SETTINGS PROFILE … SETTINGS max_result_rows = 5 TO ana): l'utente li riceve a ogni connessione, e system.settings li mostra con changed = 1 (misurato).
- Politiche di riga (CREATE ROW POLICY … ON demo.eventos USING id <= 3 TO ana): il server aggiunge quel filtro a tutto ciò che l'utente legge. Misurato: count() ha dato 3 per lui e 90 per l'amministratore.

La password non va nell'URL
L'interfaccia HTTP accetta ?user=…&password=… nell'indirizzo, ma un URL finisce nella cronologia del terminale, nei log di qualsiasi proxy e negli strumenti di rete. Il server misurato la nasconde nel suo log (password=[HIDDEN]); gli altri no. Calíope manda le credenziali nelle intestazioni X-ClickHouse-User e X-ClickHouse-Key, e la query nel corpo della richiesta.

In Calíope
Utenti elenca account e ruoli con il loro archivio. Quelli di users_xml compaiono con un lucchetto e non si modificano, e una revoca parziale compare barrata e con la parola REVOKE. Scrivere richiede che l'account collegato abbia la gestione degli accessi; altrimenti i pulsanti si disattivano e una striscia ne dice il motivo.

Parole chiave: sicurezza, utente, ruolo, grant, revoke, revoca parziale, is_partial_revoke, show grants, system.users, users_xml, local_directory, access management, quota, profilo di impostazioni, settings profile, row policy, politica di riga, password, accesso negato

Le impostazioni di query rispetto a quelle del server, config.d e users.d, come rendere persistente un'impostazione quando si parla via HTTP, e le impostazioni che Calíope manda con ogni richiesta.

Si applica a: ClickHouse 25.3+

ClickHouse ha due tipi di impostazioni, e confonderle è l'errore più comune quando lo si configura.

Impostazioni di query e impostazioni del server

SELECT name, value, changed FROM system.settings WHERE changed;
SELECT name, value, changeable_without_restart FROM system.server_settings WHERE name = 'max_concurrent_queries';

- system.settings (1.817 sul server misurato): valgono per una query. Si fissano nella query (SETTINGS … alla fine), nella sessione, in un profilo o sull'utente.
- system.server_settings (447): valgono per l'intero processo, come max_concurrent_queries (1.000 per impostazione predefinita) o max_server_memory_usage. Si cambiano nella configurazione del server, e la colonna changeable_without_restart dice se si applicano senza riavviarlo: sul server misurato, 339 su 447 richiedono un riavvio.

config.d e users.d
La configurazione sta in /etc/clickhouse-server/: config.xml per il server e users.xml per utenti, profili e quote. Le proprie modifiche vanno in file separati dentro config.d/ e users.d/, che il server fonde con quelli principali; così un aggiornamento non le sovrascrive. L'immagine Docker ufficiale ne porta già uno in ciascuna cartella.

Via HTTP, SET non dura
Ogni richiesta HTTP è indipendente: un SET max_threads = 7 vale per quella richiesta, e la successiva non ce l'ha più (misurato). Un'impostazione si può mandare nella richiesta stessa (?max_threads=5) o rendere persistente sull'utente:

ALTER USER ana MODIFY SETTINGS max_threads = 3;

Misurato: da quel momento ogni nuova connessione di ana la porta, con changed = 1. Senza la parola MODIFY, ALTER USER … SETTINGS sostituisce tutte le impostazioni dell'account. E funziona solo sugli utenti creati con SQL: su uno di users_xml fallisce con ACCESS_STORAGE_READONLY.

Ciò che Calíope manda con ogni richiesta
Cinque impostazioni fisse, per leggere i numeri senza perdere precisione e per riconoscere le proprie query:
- output_format_json_quote_64bit_integers, output_format_json_quote_decimals, output_format_json_quote_64bit_floats e output_format_json_quote_denormals: gli interi a 64 bit, i decimali e valori come inf o nan arrivano tra virgolette. In JSON un intero a 64 bit non entra esatto, e senza queste impostazioni inf e nan arrivano come null.
- log_comment: segna le query di Calíope in system.query_log.

L'editor non usa session_id (solo un report con più istruzioni apre una sessione propria); il motivo è in Limiti (ClickHouse).

In Calíope
Query lente salva la sua soglia con ALTER USER … MODIFY SETTINGS. Se l'account non si può modificare, lo dice in una nota invece di mostrare i controlli.

Parole chiave: configurazione, impostazioni, settings, server settings, system.settings, system.server_settings, config.xml, users.xml, config.d, users.d, alter user, modify settings, set, max_threads, riavvio, log_comment, output_format_json_quote

max_query_size, la memoria per query, i limiti del risultato, le query simultanee, troppe parti, il blocco di inserimento, e perché l'editor non usa session_id.

Si applica a: ClickHouse 25.3+

Il testo di una query
max_query_size è 262.144 byte per impostazione predefinita. Una query più lunga fallisce con Code: 62 … Max query size exceeded alla posizione 262.145 (misurato). Si alza per singola query con SETTINGS max_query_size = …. Il dump di Calíope raggruppa i suoi INSERT sotto quel tetto.

La memoria
max_memory_usage vale 0 (nessun limite per query) sul server misurato; il tetto vero è max_server_memory_usage, quello del processo. Con un limite basso, la query fallisce con Code: 241 … MEMORY_LIMIT_EXCEEDED, e il messaggio dice quanto stava per usare.

La dimensione del risultato
max_result_rows e max_result_bytes valgono 0 per impostazione predefinita. Con un limite impostato, result_overflow_mode = 'throw' (il valore predefinito) fa fallire la query con Code: 396 … TOO_MANY_ROWS_OR_BYTES, e 'break' restituisce ciò che è stato letto fino a quel punto.

Query contemporanee
max_concurrent_queries è del server (1.000 per impostazione predefinita) e max_concurrent_queries_for_user di ogni utente (0, senza limite). Superandolo, la nuova query fallisce con Code: 202 … TOO_MANY_SIMULTANEOUS_QUERIES (misurato con il limite a 1).

Troppe parti
Ogni INSERT crea una parte. Con 1.000 parti attive in una partizione (parts_to_delay_insert) il server rallenta gli inserimenti, e con 3.000 (parts_to_throw_insert) li rifiuta con TOO_MANY_PARTS. Se ne parla in Fusioni e OPTIMIZE (ClickHouse).

Il blocco di inserimento
Il server raggruppa le righe di un INSERT in blocchi di al massimo max_insert_block_size righe (1.048.449). Un INSERT grande che fallisce a metà lascia scritti i blocchi precedenti: non è atomico. Se ne parla in Transazioni e atomicità (ClickHouse).

Una richiesta, un'istruzione, e niente sessione
Via HTTP ogni richiesta accetta una sola istruzione: due separate da ; falliscono con Multi-statements are not allowed (misurato). L'interfaccia offre session_id per avere una sessione, e con esso un SET dura davvero da una richiesta all'altra. Ma una sessione accetta una sola query alla volta: una seconda con lo stesso session_id, mentre gira la prima, fallisce con Code: 373 … SESSION_IS_LOCKED (misurato). Per questo l'editor non la usa: due schede sulla stessa connessione riceverebbero quell'errore. Solo un report di più istruzioni apre una sessione propria, nuova a ogni esecuzione, che nessun altro condivide. Ogni richiesta porta il suo database e le sue impostazioni.

Parole chiave: limite, max_query_size, max_memory_usage, memoria, max_result_rows, max_result_bytes, result_overflow_mode, max_concurrent_queries, query simultanee, parts_to_throw_insert, too many parts, max_insert_block_size, session_id, sessione, multi-statements

I codici che si vedono ogni giorno, con numero e nome: che cosa li provoca e che cosa fare.

Si applica a: ClickHouse 25.3+

Ogni errore di ClickHouse porta un numero e un nome: Code: 60 … (UNKNOWN_TABLE). Il numero resta stabile tra le versioni, e il nome è quello da cercare nella documentazione. Via HTTP il numero arriva anche nell'intestazione X-ClickHouse-Exception-Code. Tutti gli errori di questo elenco sono stati provocati contro il server misurato.

60 · UNKNOWN_TABLE
La tabella non esiste in quel database. Via HTTP non c'è USE: il database viaggia con ogni richiesta, quindi conviene controllare il database scelto o scrivere database.tabella.

62 · SYNTAX_ERROR
Un errore di sintassi; il messaggio dice la posizione e che cosa si aspettava. Compare anche con due istruzioni in una sola richiesta (Multi-statements are not allowed) e con una query di più di 262.144 byte (Max query size exceeded).

53 · TYPE_MISMATCH
Una conversione impossibile tra tipi, come CAST(1 AS Array(UInt8)). La soluzione è la funzione di conversione giusta (toString, toUInt8OrNull…).

241 · MEMORY_LIMIT_EXCEEDED
La query ha superato il limite di memoria, e il messaggio dice quanto stava per usare. Aiuta filtrare prima, aggregare meno colonne o, se il server lo regge, alzare max_memory_usage.

202 · TOO_MANY_SIMULTANEOUS_QUERIES
Troppe query contemporanee, per l'intero server o per quell'utente. Basta aspettare; Processi mostra che cosa sta girando.

497 · ACCESS_DENIED
Manca un permesso, e il messaggio dice quale (it's necessary to have the grant SELECT ON demo.eventos). Una revoca parziale lo provoca anche se il database è concesso per intero.

164 · READONLY
La query gira in sola lettura (readonly = 1 sull'utente, sul suo profilo o nella query stessa). Serve un account che possa scrivere.

373 · SESSION_IS_LOCKED
Due richieste contemporanee con lo stesso session_id. Calíope apre una sessione solo per un report di più istruzioni, nuova a ogni esecuzione, quindi se compare viene da un altro client.

395 · FUNCTION_THROW_IF_VALUE_IS_NON_ZERO
Un throwIf della query è scattato, e il messaggio è quello che la query gli ha dato. Se la query aveva già cominciato a restituire righe, l'errore arriva alla fine della risposta: Calíope lo mostra come errore, non come un risultato troncato.

420 · CANNOT_UPDATE_COLUMN
Un ALTER TABLE … UPDATE su una colonna della chiave di ordinamento. La chiave non si cambia: si crea un'altra tabella con la chiave nuova e si copiano i dati.

495 · ACCESS_STORAGE_READONLY
Un tentativo di modificare un utente dichiarato nei file del server (users_xml). Si cambia in users.d/, oppure se ne crea un altro con SQL.

516 · AUTHENTICATION_FAILED
Utente o password sbagliati; il server non dice quale dei due. Via HTTP arriva con lo stato 403.

In Calíope
Quando uno di questi errori compare nell'editor, la striscia Vedi soluzione porta a questo argomento.

Parole chiave: errore, codice, unknown_table, syntax_error, type_mismatch, memory_limit_exceeded, too_many_simultaneous_queries, access_denied, readonly, session_is_locked, throwif, cannot_update_column, access_storage_readonly, authentication_failed, 60, 62, 53, 241, 202, 497, 164, 373, 395, 420, 495, 516

NUMBER arrotonda in silenzio alla sua scala e rifiuta ciò che supera la sua precisione; BINARY_DOUBLE è binario.

Si applica a: Oracle 26ai+

In Oracle quasi ogni numero è un NUMBER: decimale, esatto e fino a 38 cifre. INTEGER e SMALLINT esistono solo come nomi —misurato: entrambe le colonne sono state create come NUMBER con scala 0—, e non ci sono né UNSIGNED né tipi per dimensione.

NUMBER(p,s): la scala arrotonda, la precisione rifiuta

CREATE TABLE n (a NUMBER(5,2), c NUMBER(3), e NUMBER(5,-2));
INSERT INTO n (a) VALUES (1.005);   -- 1.01
INSERT INTO n (a) VALUES (123.456); -- 123.46
INSERT INTO n (a) VALUES (1000);    -- ORA-01438
INSERT INTO n (c) VALUES (12.6);    -- 13
INSERT INTO n (e) VALUES (12345);   -- 12300

Ciò che va oltre la scala a destra viene arrotondato senza avviso; ciò che non sta a sinistra dà ORA-01438. Una scala negativa arrotonda a decine o centinaia, e un NUMBER semplice conserva il valore così com’è.

Esatto, e di dimensione variabile
NUMBER memorizza decimali: 0.1 + 0.2 = 0.3 è vero. Occupa in base al valore, non alla dichiarazione: misurato con VSIZE, 1 occupa 2 byte, 123.45 4 e 1234567890123 8.

BINARY_DOUBLE e BINARY_FLOAT sono binari
Sono il double e il float di IEEE 754: più veloci nei calcoli e con i soliti errori. 0.1d + 0.2d ha dato 3.0000000000000004E-001, e il confronto con 0.3d, falso. Un FLOAT semplice, invece, è un NUMBER con la precisione espressa in bit (FLOAT(126)). Per il denaro, NUMBER(p,s).

BOOLEAN, dalla 23ai

CREATE TABLE bo (v BOOLEAN);
INSERT INTO bo VALUES (TRUE), (FALSE), (NULL), ('yes'), (0);
SELECT COUNT(*) FROM bo WHERE v;  -- 2

Accetta anche 'yes' o 0, e si usa senza confronto (WHERE v). Prima della 23ai non esiste, e l’abitudine è NUMBER(1) con un CHECK.

In Calíope
La griglia mostra NUMBER esatto, senza passare dalla virgola mobile, e BOOLEAN come TRUE o FALSE.

Parole chiave: numero, NUMBER, decimale, precisione, scala, BINARY_DOUBLE, BINARY_FLOAT, FLOAT, BOOLEAN, ORA-01438, Oracle

VARCHAR2 conta byte se non dici CHAR, ha un tetto di 4000, e la stringa vuota è NULL.

Si applica a: Oracle 26ai+

La stringa vuota è NULL
È ciò che sorprende di più arrivando da un altro motore: in Oracle '' è NULL. Misurato: '' IS NULL è vero, LENGTH('') è NULL e non 0, e inserire '' lascia la colonna a NULL, così COUNT(colonna) non la conta. Una colonna NOT NULL rifiuta '' con ORA-01400.