Manuale del DBA

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

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

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

Numerici interi

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

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

Numerici decimali e in virgola mobile

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

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

Testo e stringhe

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

Data e ora

- DATE — 3 byte, '1000-01-01'…'9999-12-31'.
- TIME — 3 byte, '-838:59:59'…'838:59:59'. Sì, può essere maggiore di 24 ore (intervallo, non ora del giorno).
- DATETIME — 8 byte, senza fuso orario, senza conversione al salvataggio/lettura. Persiste la stringa letterale.
- TIMESTAMP — 4 byte, intervallo 1970…2038 (in MySQL 5.7) o 1970…2106 (in MariaDB 10.4+). Viene memorizzato in UTC e convertito al time_zone della connessione.
- YEAR — 1 byte, 1901…2155.

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

JSON

Tipo nativo in MySQL 5.7+ e MariaDB 10.2+. Consente di indicizzare campi estratti con JSON_EXTRACT o ->> e, da MySQL 8.0, colonne generate con index (MULTI-VALUED INDEX sugli array).

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

Spaziali (GIS)

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

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

ENUM e SET

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

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

Come scegliere

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

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

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

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

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

Tipi di index

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

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

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

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

Semplici vs composti

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

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

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

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

Covering index

Un index che contiene tutte le colonne lette da una query evita di accedere alla tabella. Usa EXPLAIN e cerca Using index nella colonna Extra.

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

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

Index con prefisso

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

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

Index invisibili

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

Impatto lettura vs scrittura

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

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

Strategie di ottimizzazione

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

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

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

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

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

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

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

Prima Forma Normale (1FN)

- Ogni colonna contiene un unico valore atomico (né liste né JSON annidato che rappresenta più entità).
- Ogni riga è identificabile tramite una chiave primaria.
- Nessun gruppo ripetuto nelle colonne (tel1, tel2, tel3).

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

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

-- SQLite: si accende a ogni connessione, prima della prima transazione
PRAGMA foreign_keys = ON;

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

Seconda Forma Normale (2FN)

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

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

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

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

Terza Forma Normale (3FN)

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

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

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

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

BCNF e forme superiori

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

Quando denormalizzare

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

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

Compromessi che accetti:

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

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

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

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

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

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

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+

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.

Per gli esempi assumiamo:

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

INNER JOIN

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

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

LEFT JOIN (LEFT OUTER JOIN)

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

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

Clienti senza ordini — pattern classico con WHERE … IS NULL:

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

RIGHT JOIN

Inverso del LEFT. Quasi sempre si scrive come LEFT JOIN invertendo l'ordine, più leggibile.

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

CROSS JOIN

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

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

SELF JOIN

La stessa tabella compare due volte con alias diversi. Utile per gerarchie o confronti tra righe della stessa tabella.

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

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

FULL OUTER JOIN

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

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

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

UNION

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

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

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

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;

Anti-join e semi-join

Pattern logici, non parole chiave SQL:

- Semi-join (esiste almeno una corrispondenza) → EXISTS o IN.
- Anti-join (non esiste corrispondenza) → NOT EXISTS o LEFT JOIN ... WHERE ... IS NULL.

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

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

NOT IN non è un anti-join. Se la sottoquery restituisce anche un solo NULL, il confronto non è mai vero e il risultato sono zero righe. Misurato con 5 clienti e 6 ordini, 3 dei quali con cliente_id a NULL: NOT EXISTS e LEFT JOIN … IS NULL restituiscono 2, e NOT IN restituisce 0. Succede uguale su PostgreSQL 17.6, su MySQL 8.0.46 e su SQLite 3.51.

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

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

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

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

innodb_buffer_pool_size

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

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

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

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

max_connections

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

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

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

max_allowed_packet

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

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

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

Query cache

Memorizza nella cache il risultato completo delle query SELECT.

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

Log e durabilità

innodb_flush_log_at_trx_commit controlla quando viene scaricato il redo log:

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

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

thread_pool

tmp_table_size / max_heap_table_size

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

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

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

innodb_io_capacity / innodb_io_capacity_max

IOPS che InnoDB può consumare per la pulizia delle pagine sporche e le purge. Per impostazione predefinita 200 / 2000.

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

Configurazione persistente

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

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

Raccomandazione generale

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

Parole chiave: configurazione, buffer pool, innodb_buffer_pool_size, max_connections, max_allowed_packet, query_cache, thread_pool, tmp_table_size, innodb_flush_log_at_trx_commit, io_capacity, my.cnf

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

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

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

Per database

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

Per tabella

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

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

Per colonna

Identificatori (nomi degli oggetti)

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

Caratteri consentiti negli identificatori

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

Convenzione consigliata: snake_case ASCII (pedido_cliente_id). Evita spazi, accenti e maiuscole — alcuni sistemi li normalizzano in modo diverso tra Linux e macOS.

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

-- Recomendado
CREATE TABLE pedidos_2024 (numero_orden INT);

Sensibilità a maiuscole/minuscole

lower_case_table_names:

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

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

Per query

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

Per sessione

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

Charset e collation

- Charset consigliato: utf8mb4 (UTF-8 completo, 4 byte). L'alias utf8 è storico e limitato a 3 byte (senza emoji).
- Collation consigliata in MySQL 8.0+: utf8mb4_0900_ai_ci (case-insensitive, accent-insensitive, basata su Unicode 9).
- In MariaDB: utf8mb4_unicode_ci o uca1400_ai_ci (10.10+).

ALTER DATABASE mi_base
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

ALTER TABLE clientes
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

Raccomandazioni

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

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

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

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

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

Progettazione degli schemi

1. Ogni tabella ha una chiave primaria. Se non è naturale, aggiungi id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY.
2. Tipi espliciti: dichiara NOT NULL e DEFAULT ogni volta che la colonna lo consente. NULL deve significare "non applicabile", non "non compilato".
3. Foreign key obbligatorie tra tabelle correlate. Perdi microsecondi in scrittura, guadagni un'integrità referenziale inviolabile.
4. InnoDB sempre. MyISAM non supporta né FK né transazioni; resta solo nei sistemi legacy.

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

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

1. Strategia 3-2-1: 3 copie, 2 supporti diversi, 1 fuori sede.
2. Tipi:
- mysqldump — logico, portabile, lento nel ripristino (~5–10 MB/s).
- mariabackup / xtrabackup — fisico, molto più veloce, richiede una breve pausa dell'I/O.
- Snapshot del filesystem (LVM, ZFS) — istantaneo ma legato al filesystem.
3. Provare il ripristino — un backup senza ripristino verificato non è un backup.
4. Retention: giornalieri 7 giorni + settimanali 4 + mensili 12 è un punto di partenza ragionevole.

Calíope ha un modulo di backup integrato: Aiuto › Backup.

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

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

Replica

- Replica asincrona (default) — il primario non aspetta la replica. Rischio: perdita delle ultime transazioni se il primario si guasta.
- Replica semisincrona — il primario attende la conferma di almeno una replica prima di confermare al client.
- Gruppo di replica (MySQL InnoDB Cluster, MariaDB Galera) — multi-primario con consenso.

Buone pratiche:

1. GTID attivato (gtid_mode = ON) — necessario per il failover automatico e per gli strumenti moderni.
2. binlog_format = ROW — più robusto di STATEMENT di fronte a funzioni non deterministiche.
3. Replica dedicata — un utente replica con solo REPLICATION SLAVE, IP ristretta.
4. Lag monitorato — SHOW REPLICA STATUS (SHOW SLAVE STATUS nelle versioni vecchie), avviso quando Seconds_Behind_Source > 30.

Utenti e permessi

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

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

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

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

Regole:

1. Un utente per applicazione / per funzione. Facilita l'audit.
2. Nessun privilegio *.* per gli utenti applicativi. Concedi per database o per tabella.
3. Nessun accesso applicativo all'utente root. È riservato alle attività amministrative.
4. Ruota le password e usa un'autenticazione robusta (caching_sha2_password in MySQL 8, ed25519 in MariaDB).
5. Restringi l'host ('app'@'10.0.%.%'), non usare '%'.

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

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

1. ✅ Backup automatici + ripristino verificato mensilmente.
2. ✅ Replica con lag monitorato.
3. ✅ Utenti applicativi senza privilegi eccessivi.
4. ✅ TLS obbligatorio per le connessioni esterne.
5. ✅ Slow query log attivato (long_query_time = 1).
6. ✅ Monitoraggio dello spazio su disco (avviso all'80 %).
7. ✅ Aggiornamenti di sicurezza applicati trimestralmente.

Parole chiave: buone pratiche, progettazione, convenzioni, backup, replica, GTID, binlog, GRANT, audit, sicurezza, InnoDB, foreign key, TLS

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

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

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

EXPLAIN

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

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

Colonne chiave:

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

EXPLAIN ANALYZE (MySQL 8.0+ / MariaDB 10.1+)

Esegue la query e mostra i tempi reali per nodo. Più costoso di EXPLAIN, ma molto più informativo.

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

Calíope dispone di un Visual Explain integrato che renderizza l'albero del piano: Workspace › Analisi › Visual Explain.

Slow query log

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

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

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

Analisi del log:

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

performance_schema

Schema di tabelle con statistiche dettagliate del server.

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

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

Cache di InnoDB

- Buffer pool — dati e index. Metrica chiave: hit ratio (Innodb_buffer_pool_read_requests / (reads + reads_from_disk)). Obiettivo: >99 %.

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

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

- Adaptive hash index — hash automatico sulle pagine calde del buffer pool. Attivato per impostazione predefinita.
- Change buffer — bufferizza le modifiche a pagine non presenti nel buffer pool.

Colli di bottiglia frequenti

Ottimizzazioni delle query — pattern comuni

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

Raccomandazione

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

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

Prima rilevi una degradazione, più facile è correggerla.

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

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

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

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

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

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

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

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

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

I quattro livelli

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

Cambiare livello

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT @@transaction_isolation;

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

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

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

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

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

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

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

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

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

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

Letture bloccanti
Un SELECT normale non blocca nulla (legge una versione coerente tramite MVCC). Se devi leggere e poi scrivere senza che nessuno si infili in mezzo, chiedi il lock esplicitamente con FOR UPDATE o FOR SHARE:

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

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

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

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

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

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

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

SHOW ENGINE INNODB STATUS;

SELECT * FROM performance_schema.data_locks;

SELECT * FROM performance_schema.data_lock_waits;

SELECT @@innodb_lock_wait_timeout;

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

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

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

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

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

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

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

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

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

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

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

PARTITION BY HASH (cliente_id) PARTITIONS 8;

PARTITION BY KEY (uuid) PARTITIONS 4;

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

Pruning delle partizioni
Il vantaggio vero: se il WHERE filtra sulla colonna di partizionamento, il server legge solo le partizioni che possono contenere risultati. Verificalo nella colonna partitions di EXPLAIN — se compaiono tutte, non stai facendo pruning e il partizionamento ti sta solo costando.

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

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

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

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

ALTER TABLE eventos DROP PARTITION p2023;

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

ALTER TABLE eventos REBUILD PARTITION p2025;

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

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

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

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

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;

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

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

    UNION ALL

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

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

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

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

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

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

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

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

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

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

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

Si applica a: PostgreSQL 13+

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

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

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

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

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

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

I quattro livelli

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

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

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

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

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

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

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

idle_in_transaction_session_timeout vale 0 per impostazione predefinita, cioè senza limite; dargli un valore è la rete che evita che una sessione dimenticata degradi l'intero database.

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

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

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

Si applica a: PostgreSQL 13+

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

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

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

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

ALTER TABLE pedidos SET (autovacuum_vacuum_scale_factor = 0.02);

Controllare che funzioni

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

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

Un vacuum in corso si segue così:

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

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

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

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

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

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

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

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

Si applica a: PostgreSQL 13+

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

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

Chi prende quale modo di tabella

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

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

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

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

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

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

Non aspettare, di proposito

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

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

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

L'abbraccio mortale

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

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

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

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

Diagnosi
Qui non c'è SHOW ENGINE INNODB STATUS. Ci sono due query, ed entrambe vanno eseguite mentre il lock dura:

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

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

pg_blocking_pids dà l'elenco dei processi che trattengono ciò che l'altro chiede, che è la domanda che ci si pone davvero. Ciò che in pg_locks non vedrai sono i lock di riga: chi aspetta una riga compare in attesa di un transactionid, il numero della transazione che la tiene. E per lasciare traccia di quel che è già successo, log_lock_waits scrive nel registro del server ogni attesa che superi deadlock_timeout.

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

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

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

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

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

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

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

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

Si applica a: PostgreSQL 13+

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

I sei metodi

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

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

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

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

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

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

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

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

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

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

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

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

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

GIN per ciò che sta dentro un campo

CREATE INDEX idx_datos ON eventos USING gin (datos);

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

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

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

CREATE INDEX CONCURRENTLY idx_pedidos_cliente ON pedidos (cliente_id);

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

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

Gli indici che non usa nessuno

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

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

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

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

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

Si applica a: PostgreSQL 13+

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

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

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

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

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

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

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

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

BUFFERS, che va chiesto

EXPLAIN (ANALYZE, BUFFERS) SELECT … ;

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

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

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

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

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

SET work_mem = '64MB';

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

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

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

Il tasso di successo della cache

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

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

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

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

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

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

Si applica a: PostgreSQL 13+

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

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

Chi ha vinto non si indovina: lo dice la colonna source di pg_settings. Misurato: dopo ALTER DATABASE demo SET work_mem = '32MB', una connessione nuova leggeva 32MB con source = database; dopo il RESET, 4MB con source = default.

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

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

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

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

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

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

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

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

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

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

ALTER TABLE eventos SET (autovacuum_vacuum_scale_factor = 0.01);

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

SET synchronous_commit = off;

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

Parole chiave: configurazione, postgresql.conf, postgresql.auto.conf, alter system, pg_settings, pending_restart, pg_reload_conf, shared_buffers, effective_cache_size, work_mem, max_connections, pool, wal, checkpoint, autovacuum, synchronous_commit

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

Si applica a: PostgreSQL 13+

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ALTER TABLE pedidos ENABLE ROW LEVEL SECURITY;

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

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

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

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

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

Parole chiave: sicurezza, ruolo, utente, gruppo, pg_hba.conf, pg_hba_file_rules, scram-sha-256, grant, revoke, alter default privileges, pg_default_acl, public, pg_read_all_data, pg_monitor, rls, row level security, create policy, bypassrls, drop owned by

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

Si applica a: PostgreSQL 13+

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

Backup logico

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

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

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

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

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

START WAL LOCATION: 0/13000028 (file 000000010000000000000013)

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

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

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

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

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

max_slot_wal_keep_size mette il tetto: superato quello, il server preferisce invalidare lo slot piuttosto che restare senza disco.

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

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

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

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

Si applica a: PostgreSQL 13+

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

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

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

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

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

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

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

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

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

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

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

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

ALTER TABLE ddl_hija VALIDATE CONSTRAINT fk_p;

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

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

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

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

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

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

Si applica a: PostgreSQL 13+

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

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

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

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

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

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

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

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

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

Misurato, lo stesso istante con due fusi di sessione:

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

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

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

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

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

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

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

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

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

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

Si applica a: PostgreSQL 13+

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

Quelli che si vedono ogni giorno

Le classi, che sono ciò da guardare

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

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

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

Un client che mostri solo il MESSAGE butta via metà dell'informazione —e proprio la metà che dice come uscirne—. Calíope compone tutte e tre.

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

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

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

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

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

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

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

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

Si applica a: PostgreSQL 13+

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

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

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

CREATE TABLE pt_resto PARTITION OF pt DEFAULT;

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

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

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

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

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

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

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

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

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

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

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

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

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

Si applica a: PostgreSQL 13+

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

Misurato, conservando quattro stringhe in una normale colonna text:

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

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

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

Che cosa cambia con la collazione

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

Misurato, i risultati non si somigliano:

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

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

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

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

CREATE INDEX idx_prefijo ON ch_like (s text_pattern_ops);

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

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

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

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

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

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

Si applica a: PostgreSQL 13+

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

Quelli che si toccano davvero

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Quando mettere i dati dentro il documento e quando referenziarli: i quattro pattern di MongoDB, l'antipattern dell'array senza tetto e quanto costa ciascuno, misurato.

Si applica a: MongoDB 7.0+

In SQL lo schema deriva dalla normalizzazione: ogni fatto in un solo posto, e le query lo ricompongono con JOIN. In MongoDB deriva dallo schema di accesso: ciò che si legge insieme si salva insieme. La domanda non è più «come evito di ripetere un dato?», ma «che cosa voglio che mi restituisca una sola lettura?».

Incorporare o referenziare

Un ordine può portarsi dentro le sue righe, oppure le righe possono vivere in una collezione propria puntando all'ordine.

// Embebido: el pedido lleva sus líneas dentro
db.p_emb.insertOne({ _id: 1, cliente: 7, fecha: new Date(),
  lineas: [ { sku: "A-7", cantidad: 2, precio: 19.9 } ] })
db.p_emb.findOne({ _id: 1 })

// Referencia: las líneas viven aparte y apuntan al pedido
db.p_ref.insertOne({ _id: 1, cliente: 7, fecha: new Date() })
db.l_ref.insertOne({ pedido: 1, sku: "A-7", cantidad: 2, precio: 19.9 })
db.l_ref.createIndex({ pedido: 1 })
db.p_ref.aggregate([ { $match: { _id: 1 } },
  { $lookup: { from: "l_ref", localField: "_id",
               foreignField: "pedido", as: "lineas" } } ])

Misurato contro MongoDB 8.2 con 100.000 ordini di tre righe: leggere un ordine con le sue righe costa 0,25 ms incorporato e 0,30 ms con $lookup —ma 67 ms se manca l'indice su l_ref.pedido, perché allora ogni ordine percorre tutte le 300.000 righe—. Su disco gli ordini incorporati occupano 2,9 MB contro 1,1 MB + 5,2 MB delle due collezioni separate: qui incorporare è uscito più economico anche in spazio.

Incorporare non rinuncia agli indici: un indice su un campo dentro l'array è multichiave e funziona uguale. Cercare {"lineas.sku": "A-7"} senza di esso è un COLLSCAN di 100.000 documenti in 42 ms; con esso, 2.000 esaminati in 2 ms, per le stesse 2.000 righe. E un documento si modifica in modo atomico senza transazione: l'$inc di un contatore e il $set di uno stato in un solo updateOne entrano insieme o non entra nessuno dei due.

L'antipattern: l'array che non smette di crescere

db.sensor.insertOne({ _id: 1, lecturas: [] })
const tanda = Array.from({ length: 10000 }, (_, i) => ({ t: new Date(), v: i }))
db.sensor.updateOne({ _id: 1 }, { $push: { lecturas: { $each: tanda } } })
db.sensor.stats().avgObjSize   // 288 919 bytes tras 10 000 lecturas

Il documento vuoto è di 29 byte e ogni lettura aggiunge 28,9. A 540.000 misura 16.628.919 byte e la tornata successiva fallisce con il codice 10334: «Resulting document after update is larger than 16777216». Il tetto di 16 MB per documento non si negozia. E fa male già prima di arrivarci: un $set di un campo scalare costa 3,15 ms su quel documento e 0,50 ms su uno di 228 byte, e un $pop dell'array 71,95 ms contro gli 0,15 ms che costa inserire una lettura sciolta. Un array che cresce senza tetto noto è un riferimento messo male.

Bucket: le letture si raggruppano in documenti di N.

const lote = Array.from({ length: 200 }, (_, i) => ({ t: new Date(), v: i }))
db.cubos.insertOne({ sensor: 1, desde: new Date(), n: 200, lecturas: lote })
db.cubos.createIndex({ sensor: 1, desde: -1 })

Le stesse 540.000 letture: sciolte sono 540.000 documenti, 6,5 MB di dati e 8,4 MB di indice; in bucket da 200 sono 2.700 documenti, 4,2 MB di dati e 82 KB di indice. Inserirle costa 203 ms in bucket contro 901 ms sciolte. Leggere l'ultima: 0,20 ms dal bucket, 0,30 ms sciolta e 46,80 ms dall'array incorporato, che va portato via intero.

Riferimento esteso: copiare dentro il figlio la manciata di campi del padre che si mostrano sempre. Elencare 100 ordini con il nome del loro cliente costa 0,35 ms con il nome duplicato dentro e 1,00 ms con $lookup. Si paga in scrittura: rinominare un cliente obbliga a toccare i suoi 1.000 ordini, 2 ms con un indice su cliente. Si duplica ciò che non cambia quasi mai.

Valore calcolato: salvare il totale già sommato invece di ricalcolarlo a ogni lettura. Su un ordine non si nota —0,35 ms letto dal documento, 0,30 ms sommato al volo—; aggregando i 100.000 sì: 14 ms leggendo il campo contro 129 ms ricalcolando.

Sottoinsieme: dentro il documento solo le poche righe che si mostrano, il resto nella sua collezione. 500 prodotti con 500 recensioni ciascuno: incorporate tutte, il documento medio è di 80.738 byte; con le ultime cinque e un contatore, 889 byte, e la collezione scende da 6,1 MB a 60 KB. La scheda passa da 0,55 ms a 0,25 ms, e la pagina di 20 recensioni si chiede a parte, alla sua collezione.

La regola, in una riga: incorpora ciò che si legge con il suo padre, appartiene solo a lui e ha un tetto; referenzia ciò che cresce senza limite, si condivide fra più padri o si consulta per conto proprio.

Parole chiave: modellazione, incorporare, riferimento, documento, pattern, bucket, sottoinsieme, riferimento esteso, valore calcolato, array, schema, 16 MB

Quale tipo MongoDB salva con ogni valore, quanto occupa, come si confrontano tipi diversi fra loro e come si ordina il testo accentato.

Si applica a: MongoDB 7.0+

In SQL il tipo lo dichiara la tabella e tutte le righe lo rispettano. In MongoDB il tipo viaggia con ogni valore: non c'è CREATE TABLE, e due documenti della stessa collezione possono avere un intero e una stringa nello stesso campo. Il formato si chiama BSON, un'estensione binaria di JSON con i tipi che a JSON mancano: interi a 32 e a 64 bit, decimali esatti, date, binari e ObjectId.

_id e ObjectId

Ogni documento ha un _id: unico, immutabile e indicizzato da quando nasce la collezione. Se non lo scrivi tu, il client ci mette un ObjectId: 12 byte, di cui 4 sono il tempo in secondi, 5 sono casuali per processo e 3 sono un contatore. Cresce con l'orologio, quindi serve per intervalli di date senza salvare nessuna data.

const oid = ObjectId()
oid.getTimestamp()            // 2026-09-12T01:28:05.000Z
oid.toString().slice(0, 8)    // "6aa4aaa5": los 4 bytes de tiempo

// Lo creado desde el 1 de septiembre, por el índice de _id
const desde = ObjectId.createFromTime(Date.parse("2026-09-01") / 1000)
db.pedidos.countDocuments({ _id: { $gte: desde } })

Tenerlo come stringa costa e non aggiunge nulla: {_id: ObjectId()} pesa 22 byte e lo stesso valore in stringa, 39.

Interi, decimali binari ed esatti

Quattro tipi numerici, e la differenza si vede nel documento: $bsonSize su {_id: 1, a: …} dà 21 byte con int, 25 con long o con double e 33 con decimal. Il denaro va in decimal per lo stesso motivo per cui in SQL va in DECIMAL: sommare 0,1 e 0,2 come double dà 0.30000000000000004, e come decimali dà 0.3.

La trappola sta nel client. mongosh sceglie il tipo guardando il valore, quindi un 7 scritto a mano si salva come int, 3000000000 come double, e 9007199254740993 si salva come 9007199254740992: oltre 2⁵³ bisogna scrivere NumberLong("…").

db.tipos.insertMany([
  { v: 7 }, { v: 7.5 }, { v: 3000000000 },
  { v: 9007199254740993 }, { v: NumberLong("9007199254740993") }
])
db.tipos.aggregate([ { $project: { v: 1, t: { $type: "$v" } } } ])

Per interrogare, invece, i quattro sono un unico numero: con un int, un long, un double e un decimal di valore 7 nella collezione, {v: 7} li trova tutti e quattro, e {v: "7"} non ne trova nessuno. $type sì che li distingue, e "number" li raggruppa di nuovo.

Date

Date sono 8 byte di millisecondi dal 1970, sempre in UTC e senza fuso orario. Qui non esiste la coppia DATETIME / TIMESTAMP: c'è un tipo solo, il fuso lo mette chi legge, e i microsecondi si perdono in scrittura. Il Timestamp di BSON non è per i tuoi dati: è l'orologio interno della replica.

db.eventos.insertOne({ _id: 1, d: new Date("2026-09-11T21:05:00.123Z") })
db.eventos.aggregate([ { $project: {
  utc: { $hour: "$d" },
  mx:  { $hour: { date: "$d", timezone: "America/Mexico_City" } }
} } ])                        // utc 21 · mx 15

Binari e UUID

BinData salva byte con un sottotipo, e UUID() è il sottotipo 4. Un UUID come BinData occupa 29 byte di documento; lo stesso scritto come stringa con trattini, 49.

L'ordine fra tipi diversi

Un sort su un campo con tipi mescolati non fallisce: c'è un ordine totale fra i tipi, e misurato su quattordici documenti è questo.

MinKey → campo assente e null → numeri → stringhe → oggetti → binari → ObjectId → booleani → date → Timestamp → espressioni regolari → MaxKey.

Gli array non compaiono in quell'elenco perché un array si confronta per il suo elemento minore: [9, 10] si ordina fra i numeri. E un campo assente si ordina esattamente come null, al punto che {v: null} trova entrambe le cose; per separarle servono {v: {$type: "null"}} e {v: {$exists: false}}.

UTF-8 e collazione

Non c'è nessun set di caratteri da scegliere. Le stringhe BSON sono UTF-8 e basta: "café ☕ 日本語 👩‍💻" sono 14 caratteri e 31 byte, e torna com'era stata scritta.

Quello che si sceglie è la collazione, binaria per impostazione predefinita: senza collation, cafe e café sono valori diversi, e Árbol si ordina dopo zorro. Una collation con il suo locale e il suo strength — 1 ignora accenti e maiuscole, 2 ignora solo le maiuscole, 3 distingue tutto — cambia insieme confronto e ordine.

db.clientes.find({ n: "cafe" }).collation({ locale: "es", strength: 1 })
db.clientes.find().sort({ n: 1 }).collation({ locale: "es" })

Ed ecco la stessa trappola di SQL: la collazione della query deve essere quella dell'indice. Con un indice normale su n, la query {n: "cafe"} è un IXSCAN che esamina 1 documento; la stessa query con collation ricade in COLLSCAN e ne esamina 7. Il rimedio non è scrivere collation in ogni query, ma darla alla collezione: i suoi indici nascono allora con lei.

db.createCollection("clientes", { collation: { locale: "es", strength: 1 } })
db.clientes.createIndex({ n: 1 }, { unique: true })
db.clientes.insertOne({ n: "cafe" })
db.clientes.insertOne({ n: "CAFÉ" })   // E11000: aquí es el mismo valor

Altri due avvisi, misurati entrambi. $regex ignora la collazione: /^CAF/ non trova cafe nemmeno con strength: 1, mentre {n: "CAFE"} lo trova. E numericOrdering: true fa ordinare le stringhe "1", "2" e "10" come numeri invece che "1", "10", "2".

Parole chiave: bson, tipi, objectid, decimal128, numberlong, data, date, uuid, bindata, collation, collazione, utf-8, accenti, ordinamento

Gli undici tipi di indice di MongoDB e quando serve ciascuno, la regola ESR per ordinare un composto, la query coperta, i tetti e il prezzo in scrittura, tutto misurato con `explain`.

Si applica a: MongoDB 7.0+

Un indice è un albero B sul valore di un campo, e il numero che dice se serve esce da explain("executionStats"): totalDocsExamined contro nReturned. Se il primo è molto maggiore del secondo, il server sta leggendo documenti per buttarli.

const est = ["nuevo", "pagado", "enviado", "cerrado"]
const docs = []
for (let i = 0; i < 20000; i++) docs.push({
  cliente: i % 499, estado: est[i % 4], total: (i % 997) + 0.5,
  fecha: new Date(Date.UTC(2026, 0, 1 + (i % 240)))
})
db.pedidos.insertMany(docs)
const busca = { cliente: 42, estado: "pagado" }

db.pedidos.find(busca).explain("executionStats")   // COLLSCAN · 10 / 20000

db.pedidos.createIndex({ cliente: 1 })
db.pedidos.find(busca).explain("executionStats")   // IXSCAN · 10 / 40

db.pedidos.createIndex({ estado: 1, cliente: 1, fecha: -1 })
db.pedidos.find(busca).explain("executionStats")   // IXSCAN · 10 / 10

La regola ESR

Un indice composto si percorre per prefissi, quindi l'ordine dei suoi campi è la decisione. Prima i campi di uguaglianza (E), poi quello dell'ordinamento (S) e infine quello dell'intervallo (R). Con l'intervallo prima dell'ordinamento, l'indice filtra ma non ordina, e compare una fase SORT che ordina in memoria: 1 826 documenti, nella query qui sotto.

const q = { estado: "pagado", fecha: { $gte: new Date("2026-06-01") } }

db.pedidos.createIndex({ estado: 1, fecha: 1, total: 1 })          // E-R-S
db.pedidos.find(q).sort({ total: 1 }).hint("estado_1_fecha_1_total_1").explain()
// FETCH <- SORT <- IXSCAN

db.pedidos.createIndex({ estado: 1, total: 1, fecha: 1 })          // E-S-R
db.pedidos.find(q).sort({ total: 1 }).hint("estado_1_total_1_fecha_1").explain()
// FETCH <- IXSCAN

Quella fase ha un tetto: internalQueryMaxBlockingSortMemoryUsageBytes vale 104 857 600 byte, e oltre quello la query fallisce se non le si consente il disco.

Query coperta

Se l'indice porta tutti i campi che la query legge, il server non tocca i documenti. Attenzione a _id: entra nella proiezione per impostazione predefinita, non sta nell'indice, e va tolto a mano.

db.pedidos.find({ estado: "pagado" }, { _id: 0, estado: 1, total: 1 })
  .hint("estado_1_total_1_fecha_1").explain("executionStats")
// PROJECTION_COVERED · nReturned 5000 · totalDocsExamined 0

db.pedidos.find({ estado: "pagado" }, { estado: 1, total: 1 })
  .hint("estado_1_total_1_fecha_1").explain("executionStats")
// totalDocsExamined 5000

Gli altri tipi

I due che perdono dati in silenzio si vedono insieme:

db.socios.insertMany([{ a: 1 }, { a: 2 }, { b: 9 }, { b: 8 }])
db.socios.createIndex({ a: 1 }, { sparse: true })
db.socios.find().sort({ a: 1 }).hint("a_1")   // 2 / 4

db.correos.createIndex({ correo: 1 }, { unique: true })
db.correos.insertOne({ otro: 1 })
db.correos.insertOne({ otro: 2 })   // E11000 · dup key: { correo: null }

Il nascosto è l'opposto: resta mantenuto, ma il pianificatore non lo guarda. Con hidden: true la stessa query è stata un COLLSCAN su 20 000 documenti; tornato visibile con collMod, un IXSCAN su 1 940.

I tetti

for (let i = 0; i < 70; i++) db.tope.createIndex({ ["c" + i]: 1 })
// 67 CannotCreateIndex · add index fails, too many indexes

const k = {}
for (let i = 0; i < 33; i++) k["g" + i] = 1
db.comp.createIndex(k)   // 13103 · too many compound keys

Il nome di un indice e la dimensione di una chiave non hanno un tetto pratico: 400 caratteri e 2 000 byte sono stati accettati senza protestare.

Il prezzo

Ogni indice si paga a ogni scrittura. Le stesse 20 000 inserzioni hanno impiegato 60 ms senza indici, 101 ms con cinque e 160 ms con dieci. E occupano spazio: i sei indici di pedidos sommano 1,9 MB contro 916 KB di dati.

Per questo non si indicizza tutto. Un campo a bassa cardinalità quasi mai aiuta: estado, con quattro valori distinti, esamina 5 000 documenti per restituirne 5 000. E un indice semplice è di troppo se un composto comincia già con lui: { cliente: 1 } e { cliente: 1, fecha: 1 } esaminano gli stessi 40.

Parole chiave: indice, indici, ESR, composto, multichiave, testo, 2dsphere, hashed, jolly, TTL, parziale, sparse, univoco, nascosto, query coperta, explain, IXSCAN, COLLSCAN, totalDocsExamined, MongoDB

Le fasi della pipeline e in che ordine metterle, $lookup come JOIN e quanto costa senza indice, $graphLookup, le funzioni finestra, $facet e $unionWith, e $merge rispetto a $out.

Si applica a: MongoDB 7.0+

Una pipeline è un elenco di fasi, e ognuna riceve i documenti prodotti dalla precedente. L'ordine lo scrivi tu, ed è lì che sta quasi tutta la prestazione.

const est = ["nuevo", "pagado", "enviado", "cerrado"]
const docs = []
for (let i = 0; i < 20000; i++) docs.push({
  cliente: i % 499, estado: est[i % 4], total: (i % 997) + 0.5,
  fecha: new Date(Date.UTC(2026, 0, 1 + (i % 240)))
})
db.pedidos.insertMany(docs)

db.pedidos.aggregate([
  { $match: { estado: "pagado" } },
  { $group: { _id: "$cliente", gastado: { $sum: "$total" }, pedidos: { $sum: 1 } } },
  { $match: { pedidos: { $gte: 11 } } },
  { $sort: { gastado: -1 } },
  { $limit: 3 }
])
// { _id: 37, gastado: 522.5, pedidos: 11 } y dos mas

Filtrare per primo, sempre

Solo la prima fase può usare un indice. Con un indice {estado: 1} su quei 20 000 ordini, il $match davanti esamina 5 000 chiavi e 5 000 documenti; lo stesso filtro dietro il $group, nessuna chiave e 20 000 documenti.

$lookup è il JOIN, e senza indice si paga

Unisce un'altra raccolta della stessa base, e ciò che trova arriva come array che quasi sempre si apre con $unwind.

const cl = []
for (let i = 0; i < 499; i++)
  cl.push({ num: i, nombre: "Cliente " + i, pais: ["MX", "ES", "AR", "CO"][i % 4] })
db.clientes.insertMany(cl)

const pais = [
  { $match: { estado: "pagado" } },
  { $lookup: { from: "clientes", localField: "cliente", foreignField: "num", as: "c" } },
  { $unwind: "$c" },
  { $group: { _id: "$c.pais", gastado: { $sum: "$total" } } }
]
db.pedidos.explain("executionStats").aggregate(pais)
// collectionScans 5001 - totalDocsExamined 2495499 - indexesUsed []

db.clientes.createIndex({ num: 1 })
db.pedidos.explain("executionStats").aggregate(pais)
// collectionScans 0 - totalDocsExamined 5001 - indexesUsed num_1

L'explain della fase non lascia margine: senza indice su clientes.num fa una passata intera per ogni documento in ingresso, e con l'indice non ne fa nessuna.

Il ricorsivo e quello di finestra

$graphLookup segue una gerarchia fin dove arriva, e depthField annota a che distanza è rimasto ogni gradino. L'array che restituisce non è ordinato: qui sono usciti Ana, Caro e Beto con i livelli 2, 0 e 1. $setWindowFields (5.0+) è la finestra di SQL: partitionBy è il PARTITION BY e sortBy l'ORDER BY.

db.empleados.insertMany([{ _id: 1, nombre: "Ana", jefe: null },
  { _id: 2, nombre: "Beto", jefe: 1 }, { _id: 3, nombre: "Caro", jefe: 2 },
  { _id: 4, nombre: "Dora", jefe: 3 }])
db.empleados.aggregate([
  { $match: { nombre: "Dora" } },
  { $graphLookup: { from: "empleados", startWith: "$jefe", connectFromField: "jefe",
                    connectToField: "_id", as: "cadena", depthField: "nivel" } }
])   // cadena: Ana(2), Caro(0), Beto(1)

db.pedidos.aggregate([
  { $match: { cliente: 42 } },
  { $setWindowFields: { partitionBy: "$estado", sortBy: { fecha: 1 },
      output: { acumulado: { $sum: "$total", window: { documents: ["unbounded", "current"] } },
                puesto: { $rank: {} } } } },
  { $match: { estado: "pagado" } },
  { $limit: 3 }
])   // acumulado 559.5 - 1113 - 1660.5

Più risposte in una sola passata

$facet esegue sotto-pipeline sullo stesso ingresso e restituisce un solo documento con tutte; al suo interno non vale più nessun indice. $unionWith è l'UNION ALL.

db.devoluciones.insertMany([{ cliente: 42, total: 9.5 }, { cliente: 7, total: 4.25 }])
db.pedidos.aggregate([
  { $match: { cliente: 42, estado: "pagado" } },
  { $project: { _id: 0, cliente: 1, importe: "$total" } },
  { $unionWith: { coll: "devoluciones", pipeline: [
      { $match: { cliente: 42 } },
      { $project: { _id: 0, cliente: 1, importe: { $multiply: ["$total", -1] } } } ] } },
  { $group: { _id: "$cliente", neto: { $sum: "$importe" }, filas: { $sum: 1 } } }
])   // { _id: 42, neto: 5495.5, filas: 11 }

Scrivere il risultato

$merge fonde nella raccolta di destinazione e $out la sostituisce per intero.

db.pedidos.aggregate([
  { $match: { estado: "pagado" } },
  { $group: { _id: "$cliente", gastado: { $sum: "$total" } } },
  { $merge: { into: "resumen", whenMatched: "merge", whenNotMatched: "insert" } }
])
db.pedidos.aggregate([
  { $match: { estado: "enviado" } },
  { $group: { _id: "$cliente", enviado: { $sum: "$total" } } },
  { $merge: { into: "resumen", whenMatched: "merge", whenNotMatched: "insert" } }
])   // resumen: { _id: 42, gastado: 5505, enviado: 515 }

db.pedidos.aggregate([
  { $match: { estado: "nuevo" } },
  { $group: { _id: "$cliente", nuevo: { $sum: "$total" } } },
  { $out: "resumen" }
])   // resumen: { _id: 42, nuevo: 525 } - lo anterior ya no esta

I tetti

Ogni fase che blocca — $group, $sort, $facet, l'intermedio di $lookup — ha 104 857 600 byte, e dalla 6.0 ciò che trabocca va su disco da solo. Ma l'array di un accumulatore non trabocca: un $push su documenti grandi fallisce con 146 ExceededMemoryLimit, e allowDiskUse: true non lo salva.

Parole chiave: aggregazione, pipeline, fase, match, group, project, lookup, join, graphLookup, setWindowFields, finestra, facet, unionWith, merge, out, unwind, explain, allowDiskUse

Un documento si scrive per intero senza transazione; per più di uno ne serve una, e un replica set. La sessione, readConcern e writeConcern, il WriteConflict 112 e i tre tetti misurati.

Si applica a: MongoDB 7.0+

In MongoDB un documento si scrive per intero o non si scrive, e vale anche se l'updateOne tocca dieci campi e un array annidato. Per cambiare più di un documento alla volta serve una transazione, e una transazione richiede un replica set: su un nodo isolato viene rifiutata, e il messaggio non parla nemmeno di transazioni — dice che il deployment non ammette scritture ripetibili.

db.cuentas.insertMany([{ _id: "A", saldo: 100 }, { _id: "B", saldo: 100 }])
db.cuentas.updateOne({ _id: "A" },
  { $inc: { saldo: -30 }, $set: { ultimo: new Date("2026-09-12") } })
db.cuentas.findOne({ _id: "A" })
// { _id: "A", saldo: 70, ultimo: 2026-09-12 } - los dos campos, o ninguno

La transazione gira su una sessione

Tutto quello che sta dentro passa dall'oggetto della sessione: il db.cuentas di fuori non è nella transazione, per quanto si chiami uguale. Fino al commitTransaction(), quanto scritto lo vede solo chi sta dentro.

const s = db.getMongo().startSession()
const c = s.getDatabase(db.getName()).cuentas
s.startTransaction({ readConcern: { level: "snapshot" },
                     writeConcern: { w: "majority" } })
c.updateOne({ _id: "A" }, { $inc: { saldo: -10 } })
c.updateOne({ _id: "B" }, { $inc: { saldo: 10 } })

c.find().toArray()           // dentro:  A 60, B 110
db.cuentas.find().toArray()  // fuera:   A 70, B 100

s.commitTransaction()
db.cuentas.find().toArray()  // fuera:   A 60, B 110
s.endSession()

abortTransaction() disfa tutto, e non c'è bisogno di chiederlo: se la sessione se ne va o il server riparte, la transazione muore abortita.

readConcern e writeConcern sono due domande diverse

readConcern dice che cosa si legge: local è quello che c'è qui, majority quello che non si può più perdere, snapshot una foto coerente di un istante. writeConcern dice quando si considera scritta: w: 1 è il primario, w: "majority" la maggioranza dell'insieme, e j: true aggiunge il giornale. I valori di fabbrica escono da getDefaultRWConcern: lettura local, scrittura majority.

Il conflitto di scrittura

Due transazioni sullo stesso documento non aspettano: la seconda fallisce all'istante con 112 WriteConflict, e il messaggio lo dice senza giri di parole. Riprovare fa parte del patto, ed è per questo che i driver portano withTransaction, che riprova da solo.

const s1 = db.getMongo().startSession()
const s2 = db.getMongo().startSession()
s1.startTransaction(); s2.startTransaction()
s1.getDatabase(db.getName()).cuentas.updateOne({ _id: "A" }, { $inc: { saldo: 1 } })
s2.getDatabase(db.getName()).cuentas.updateOne({ _id: "A" }, { $inc: { saldo: 1 } })
// 112 WriteConflict - Write conflict during plan execution ... Please retry

s1.commitTransaction()   // la primera sí pasa
s2.abortTransaction()
s1.endSession(); s2.endSession()

Una scrittura da fuori della transazione non riceve il 112: aspetta. Nella misura ha aspettato 76 s, finché il limite di vita non ha abortito la transazione che teneva il documento.

I tetti

Una transazione dura 60 s, e oltre quella riga il commit restituisce 251 NoSuchTransaction con «has been aborted». Dentro, ogni richiesta di blocco aspetta solo 5 ms: una transazione non resta appesa a un lucchetto, preferisce fallire. E un writeConcern che l'insieme non può soddisfare fallisce prima ancora di provarci.

db.adminCommand({ getParameter: 1, transactionLifetimeLimitSeconds: 1 })
// 60
db.adminCommand({ getParameter: 1, maxTransactionLockRequestTimeoutMillis: 1 })
// 5
db.adminCommand({ getDefaultRWConcern: 1 })
// defaultReadConcern local - defaultWriteConcern { w: "majority", wtimeout: 0 }

db.cuentas.updateOne({ _id: "A" }, { $inc: { saldo: 0 } },
  { writeConcern: { w: 2, wtimeout: 1000 } })
// 100 UnsatisfiableWriteConcern - Not enough data-bearing nodes

La regola pratica: se due documenti devono cambiare insieme molte volte al giorno, quasi sempre è il modello a essere sbagliato, e la cosa giusta era annidarli. La transazione è la via d'uscita per ciò che davvero non sta in un documento.

Parole chiave: transazione, transazioni, sessione, startTransaction, commit, abort, WriteConflict, 112, 251, readConcern, writeConcern, majority, snapshot, replica set, atomicità, blocco

I tre livelli di dettaglio di explain e che cosa aggiunge ciascuno, la cache dei piani e quando un piano viene disattivato, il working set dentro la cache di WiredTiger e i tre livelli del profiler.

Si applica a: MongoDB 7.0+

Prima di toccare qualcosa si misura, e lo strumento è explain. Ha tre livelli di dettaglio, e ognuno costa più del precedente: queryPlanner si limita a pianificare — non arriva a eseguire — e mostra il piano vincente e quelli scartati; executionStats esegue il vincente e aggiunge quanto è costato; allPlansExecution aggiunge inoltre quanto è costato ogni candidato durante il periodo di prova, ed è lì che si vede perché ha vinto chi ha vinto.

const est = ["nuevo", "pagado", "enviado", "cerrado"]
const docs = []
for (let i = 0; i < 20000; i++)
  docs.push({ cliente: i % 499, estado: est[i % 4], total: (i % 997) + 0.5 })
db.pedidos.insertMany(docs)
db.pedidos.createIndex({ cliente: 1 })
db.pedidos.createIndex({ cliente: 1, total: -1 })
const q = { cliente: 42, total: { $gt: 100 } }

db.pedidos.find(q).explain()
// queryPlanner: winningPlan FETCH y rejectedPlans con 1 candidato

db.pedidos.find(q).explain("executionStats")
// + executionStats: nReturned 20, docs 20, claves 20, 0 ms

db.pedidos.find(q).explain("allPlansExecution")
// + allPlansExecution: los 2 planes probados, FETCH 20 y FETCH 10

I tre numeri che contano sono nReturned, totalDocsExamined e totalKeysExamined. Se il secondo è molto più grande del primo, il server sta leggendo documenti per buttarli.

La cache dei piani

Il pianificatore non decide di nuovo a ogni query. La prima volta prova i candidati, mette da parte il vincente sotto un planCacheKey e da lì in poi lo riusa; nell'explain si vede come isCached: true. La voce conserva works, il lavoro che è costato, e se un'esecuzione successiva ne spende dieci volte tanto, il piano viene disattivato e si torna a competere. Anche creare o eliminare un indice svuota la cache.

db.pedidos.getPlanCache().clear()
db.pedidos.getPlanCache().list()     // []

db.pedidos.find(q).toArray()
db.pedidos.find(q).toArray()
db.pedidos.getPlanCache().list()     // 1 entrada: works 21, isActive true

db.pedidos.find(q).explain().queryPlanner.winningPlan.isCached   // true

Il working set e la cache di WiredTiger

MongoDB non conserva risultati: quello che conserva sono pagine, nella cache di WiredTiger, e la prestazione dipende dal fatto che il working set — i dati e gli indici davvero toccati — ci stia dentro. Per impostazione predefinita quella cache prende metà della RAM meno 1 GB. Nella misura, di 486 864 pagine richieste solo 261 sono dovute arrivare dal disco: una su 1 865.

const w = db.serverStatus().wiredTiger.cache
w["maximum bytes configured"]        // 3621781504 - la mitad de la RAM menos 1 GB
w["bytes currently in the cache"]    // 18640438 - de todo el servidor
w["pages requested from the cache"]  // 486864
w["pages read into cache"]           // 261 - una de cada 1865

db.pedidos.stats().size              // 1385000 de datos
db.pedidos.stats().totalIndexSize    // 499712 de indices

Il profiler

Tre livelli: 0 spento, 1 solo ciò che supera slowms, e 2 tutto. Due cose misurate che sorprendono: setProfilingLevel restituisce il livello di prima, non quello appena impostato — il nuovo va riletto — e system.profile è una raccolta limitata da 1 MiB, quindi non cresce: si morde la coda.

db.getProfilingStatus()      // { was: 1, slowms: 100, sampleRate: 1 }
db.setProfilingLevel(2)      // { was: 1, ... }  <- el nivel de ANTES

db.pedidos.find({ total: { $gt: 900 } }).toArray()
db.system.profile.find().sort({ ts: -1 }).limit(1)
// query - COLLSCAN - docsExamined 1901 - nreturned 101 - 0 ms

db.system.profile.stats().capped    // true, maxSize 1048576
db.setProfilingLevel(1, { slowms: 100 })

Ogni voce porta planSummary, docsExamined, nreturned e millis, che è esattamente ciò che serve per decidere se conviene un indice. Calíope legge quella raccolta nel suo strumento di profilazione. Il livello 2 in produzione costa caro: si accende per un po' e si riabbassa, non si lascia lì.

Parole chiave: prestazioni, explain, queryPlanner, executionStats, allPlansExecution, cache dei piani, planCacheKey, isCached, WiredTiger, working set, profiler, system.profile, slowms

Che cosa gira davvero secondo getCmdLineOpts, le cinque sezioni di mongod.conf che contano, e le tre classi di parametro: quelli che cambiano a caldo, quelli d'avvio e quelli che parametri non sono.

Si applica a: MongoDB 7.0+

La prima domanda su un server che non conosci non è che cosa dice il suo file di configurazione, ma con che cosa sta girando davvero. getCmdLineOpts risponde a entrambe insieme: argv è quello che gli è stato passato sulla riga di comando, e parsed la stessa cosa, già tradotta nel vocabolario del file.

db.adminCommand({ getCmdLineOpts: 1 })
// argv:   ["mongod", "--profile", "1", "--slowms", "100",
//          "--auth", "--bind_ip_all"]
// parsed: { net: { bindIp: "*" },
//           operationProfiling: { mode: "slowOp", slowOpThresholdMs: 100 },
//           security: { authorization: "enabled" } }

Le cinque sezioni che contano

storage dice dove stanno i dati e quanta memoria si prende la cache; net, su quali indirizzi ascolta; security, se bisogna autenticarsi; operationProfiling, che cosa si annota del traffico lento; e replication, a quale insieme appartiene. Il file è YAML, quindi l'indentazione è sintassi.

# mongod.conf - lo mismo de arriba, escrito donde se queda
storage:
  dbPath: /data/db
  wiredTiger:
    engineConfig:
      cacheSizeGB: 3.37
net:
  bindIp: 127.0.0.1,10.0.0.5
  port: 27017
security:
  authorization: enabled
operationProfiling:
  mode: slowOp
  slowOpThresholdMs: 100
replication:
  replSetName: rs0
setParameter:
  cursorTimeoutMillis: 300000

I parametri sono di tre classi

Quelli che si cambiano a caldo con setParameter, quelli che si leggono solo all'avvio, e quelli che parametri non sono, per quanto lo sembrino. Tutti e tre si distinguono da quello che risponde il server: il primo restituisce was con il valore precedente — non quello nuovo, quindi per sapere com'è rimasto va riletto; il secondo dà 20 IllegalOperation; e port, che è un'opzione d'avvio e non un parametro, dà 72 InvalidOptions con «unrecognized parameter».

db.adminCommand({ setParameter: 1, cursorTimeoutMillis: 300000 })
// { was: 600000, ok: 1 }   <- devuelve el valor de ANTES, no el nuevo

db.adminCommand({ setParameter: 1, wiredTigerEngineRuntimeConfig: "cache_size=512M" })
db.serverStatus().wiredTiger.cache["maximum bytes configured"]   // 536870912

db.adminCommand({ setParameter: 1, authenticationMechanisms: ["SCRAM-SHA-256"] })
// 20 IllegalOperation - not allowed to change [...] at runtime

db.adminCommand({ setParameter: 1, port: 27020 })
// 72 InvalidOptions - attempted to set unrecognized parameter [port]

setParameter non resta

Un setParameter vive fino al riavvio successivo e non un secondo di più: con cursorTimeoutMillis messo a 300 000, il server è tornato su a 600 000. Perché resti va scritto nella sezione setParameter: del file, quella in fondo all'esempio.

La cache e la versione di compatibilità

La cache di WiredTiger è la prima cosa che si vuole toccare, e quasi sempre quella da non toccare: per impostazione predefinita si prende metà di quello che resta della RAM dopo averne messo da parte 1 GB. Sul nodo misurato, 7 933 MB di RAM hanno dato 3 621 781 504 byte di cache. E c'è una sesta cosa che nel file non sta: la featureCompatibilityVersion, che decide quali funzioni del binario sono accese — si alza a mano dopo un aggiornamento e si abbassa prima di tornare indietro.

db.hostInfo().system.memSizeMB   // 7933
db.serverStatus().wiredTiger.cache["maximum bytes configured"]
// 3621781504 = la mitad de (7933 MB - 1 GB)

Object.keys(db.adminCommand({ getParameter: "*" })).length   // 780
db.adminCommand({ getParameter: 1, featureCompatibilityVersion: 1 })
// { version: "8.2" }

Parole chiave: configurazione, mongod.conf, getCmdLineOpts, setParameter, cacheSizeGB, WiredTiger, bindIp, authorization, operationProfiling, replSetName, featureCompatibilityVersion, FCV

Un utente vive in una base ed è quello il suo cognome, i ruoli integrati non sono gli stessi in admin e altrove, il 13 che riceve una scrittura senza permesso, e l'unica porta che lascia aperta un server appena messo in sicurezza.

Si applica a: MongoDB 7.0+

Senza security.authorization: enabled non esiste niente di tutto questo: il server accetta chiunque arrivi, e con bindIp: "*" può arrivare chiunque. Acceso, la prima domanda è chi sono io.

db.adminCommand({ connectionStatus: 1 }).authInfo
// { authenticatedUsers:     [{ user: "caliope", db: "admin" }],
//   authenticatedUserRoles: [{ role: "root",    db: "admin" }] }

db.adminCommand({ getParameter: 1, authenticationMechanisms: 1 })
// ["MONGODB-X509", "SCRAM-SHA-1", "SCRAM-SHA-256"]

Il meccanismo predefinito è SCRAM-SHA-256, e il server conserva entrambe le versioni: la password dell'utente misurato porta 15 000 iterazioni in SHA-256 e 10 000 in SHA-1, con un sale di 40 caratteri. La password non viaggia, nemmeno cifrata: SCRAM dimostra che la si conosce senza dirla. Il terzo meccanismo, MONGODB-X509, scambia la password con un certificato client, e allora il nome dell'utente è il soggetto del certificato.

Un utente vive in una base, ed è quella la sua altra metà

lector non è un utente: lector di ventas lo è. La base in cui è stato creato è la sua base di autenticazione, e va nominata al collegarsi (--authenticationDatabase). Con quella sbagliata il server non dice che l'utente esiste altrove: dice «Authentication failed» e basta.

I ruoli non sono gli stessi in tutte le basi

Una base normale ha sei ruoli integrati. admin ne ha ventuno, perché lì vivono quelli che arrivano a tutto il server — i …AnyDatabase, root, backup, restore, quelli di cluster. Un ruolo è un elenco di azioni: read ne sono undici, e find è solo una.

db.getSiblingDB("admin").runCommand({ rolesInfo: 1, showBuiltinRoles: true })
// 21 roles: backup, clusterAdmin, clusterManager, clusterMonitor, dbAdmin,
// dbAdminAnyDatabase, dbOwner, read, readAnyDatabase, readWrite,
// readWriteAnyDatabase, restore, root, userAdmin, userAdminAnyDatabase ...

db.getSiblingDB("ventas").runCommand({ rolesInfo: 1, showBuiltinRoles: true })
// 6: dbAdmin, dbOwner, enableSharding, read, readWrite, userAdmin

Che cosa succede quando manca un permesso

Non c'è risposta vuota né riga mancante: c'è un 13 Unauthorized, e il messaggio nomina la base, il comando e persino la raccolta. È un errore da cui si ricava la regola che manca.

db.getSiblingDB("ventas").createUser({
  user: "lector", pwd: "lectorpass",
  roles: [{ role: "read", db: "ventas" }]
})
// mechanisms: ["SCRAM-SHA-1", "SCRAM-SHA-256"]

// ya conectado como lector, con --authenticationDatabase ventas:
db.datos.findOne()              // { _id: 1, v: 1 }
db.datos.insertOne({ _id: 2 })
// 13 Unauthorized - not authorized on ventas to execute command { insert: ... }

L'eccezione di localhost

Un server con --auth e senza un solo utente lascia fare a chi arriva dalla macchina stessa esattamente una cosa: creare il primo. Leggere no; creare il secondo, nemmeno. È la rampa per cominciare, e si chiude da sola non appena un utente esiste.

// un nodo con --auth y sin un solo usuario, desde el propio nodo:
db.getSiblingDB("prueba").c.findOne()
// 13 Unauthorized

db.getSiblingDB("admin").createUser({ user: "primero", pwd: "x", roles: ["root"] })
// OK - este es el unico que deja

db.getSiblingDB("admin").createUser({ user: "segundo", pwd: "x", roles: ["root"] })
// 13 Unauthorized - Command createUser requires authentication

Parole chiave: sicurezza, autenticazione, SCRAM, SCRAM-SHA-256, x.509, TLS, utente, ruolo, ruoli integrati, read, readWrite, dbAdmin, userAdmin, root, 13, Unauthorized, localhost, authSource

Che cosa c'è dentro un mongodump e che cosa no, quanto ci mette il ripristino e perché, a che serve --oplog, quanto dura davvero la finestra dell'oplog, e le due cose che un dump non garantisce.

Si applica a: MongoDB 7.0+

mongodump è un backup logico: si collega come un client qualunque, legge i documenti e li scrive in BSON. Questo ha due conseguenze che si vedono nei numeri. La prima: il file misura quanto misurano i documenti, non quanto occupano sul disco — 2 420 000 byte di .bson per una raccolta che sul disco sta compressa. La seconda: compete per la cache con il lavoro normale del server, quindi un dump di una base grande si sente.

mongodump -u caliope -p ... --authenticationDatabase admin \
  --db ventas --out /vol
// writing `ventas.pedidos` to `/vol/ventas/pedidos.bson`
// done dumping `ventas.pedidos` (20000 documents)      26 ms

ls -l /vol/ventas
// pedidos.bson           2420000   <- el tamano LOGICO de los documentos
// pedidos.metadata.json      255   <- los indices, sin sus datos

Degli indici viaggia solo la definizione, nel .metadata.json. Per questo ripristinare costa molto più che scaricare — 91 ms contro 26 in questa misura: il tempo se ne va nel ricostruirli, e su una raccolta vera è quasi tutta l'attesa.

mongorestore -u caliope -p ... --authenticationDatabase admin \
  --nsFrom "ventas.*" --nsTo "copia.*" /vol
// restoring `copia.pedidos` from `/vol/ventas/pedidos.bson`
// finished restoring `copia.pedidos` (20000 documents, 0 failures)
// restoring indexes for collection `copia.pedidos` from metadata
// 20000 document(s) restored successfully.             91 ms

db.getSiblingDB("copia").pedidos.getIndexes()   // _id_ y cliente_1

--archive lascia un solo file invece di un albero di cartelle, e con --gzip è sceso da 2 420 000 a 133 572 byte. Entrambi si possono mandare in una pipe, ed è così che una base si copia da una macchina all'altra senza toccare un disco intermedio.

mongodump ... --db ventas --archive=/vol/ventas.gz --gzip
// 133572 bytes, frente a 2420000 del BSON suelto

L'oplog è ciò che trasforma un backup in un istante

Un dump dura, e intanto la base continua a cambiare: quello che è stato scritto nella raccolta A prima di scaricarla e in B dopo non torna. --oplog salva anche le operazioni avvenute durante lo scarico, e mongorestore --oplogReplay le applica alla fine, così che quanto ripristinato è lo stato di un istante, quello di fine dump. Funziona solo contro un replica set, perché l'oplog è suo.

// esto sólo existe en un conjunto de réplicas:
db.getSiblingDB("local").oplog.rs.stats()
// capped true - maxSize 45822903296 - size 3708051130 - count 318107

rs.printReplicationInfo()
// oplog first event time   Sat Aug 08 2026 17:56:09
// oplog last event time    Sat Sep 12 2026 07:41:07

mongodump --port 27018 --oplog --out /vol
// writing captured oplog to `` - dumped 1 oplog entry

L'oplog è una raccolta limitata, quindi la sua finestra non si misura in byte ma in tempo, e quel tempo dipende da quanto si scrive. Sul nodo misurato, 42 GiB di tetto davano una finestra dall'8 agosto al 12 settembre; con dieci volte il carico sarebbero tre giorni. È il numero da guardare prima di andarsene per il fine settimana.

Quello che un dump non copre

Due cose. Una base di centinaia di gigabyte non si salva leggendola documento per documento: lì si passa alle istantanee del file system, che vanno prese con il giornale incluso o con la base bloccata da fsyncLock. E in un cluster frammentato, un mongodump contro il router non dà un istante comune ai frammenti: bisogna fermare il bilanciatore e prendere un'istantanea per frammento più una dei server di configurazione.

Parole chiave: backup, copia di sicurezza, mongodump, mongorestore, oplog, oplogReplay, archive, gzip, istantanea, snapshot, fsyncLock, ripristino, finestra di ripristino, BSON

Lo schema è quello che portano i documenti, quindi cambiarlo vuol dire scriverli. Il validatore con $jsonSchema, il 121 e il suo errInfo, le quattro combinazioni di validationLevel e validationAction, e la migrazione per versione di documento.

Si applica a: MongoDB 7.0+

Non c'è ALTER TABLE perché non c'è tabella: lo schema di una raccolta è, letteralmente, quello che portano i suoi documenti. Aggiungere un campo ai nuovi non costa nulla e non cambia i vecchi, ed è lì l'inghippo: chi legge deve arrangiarsi con entrambe le forme finché qualcuno non pareggia il passato.

Il validatore è una porta, non uno schema

Quello che esiste è un validator con $jsonSchema: una condizione controllata in scrittura, mai in lettura e mai all'indietro. Rifiuta con 121 DocumentValidationFailure, e la parte buona sta in errInfo.details, che nomina la regola infranta invece di dire «non valido».

db.createCollection("socios", {
  validator: { $jsonSchema: {
    bsonType: "object",
    required: ["nombre", "correo"],
    properties: {
      nombre: { bsonType: "string", minLength: 2 },
      correo: { bsonType: "string", pattern: "^.+@.+$" },
      edad:   { bsonType: "int",    minimum: 18 }
    } } },
  validationLevel: "strict", validationAction: "error"
})

db.socios.insertOne({ nombre: "Ana", correo: "ana@ej.com", edad: 30 })   // entra
db.socios.insertOne({ nombre: "Caro" })
// 121 DocumentValidationFailure
// errInfo.details: required -> missingProperties: ["correo"]

Attenzione ai tipi: mongosh salva come int un numero intero, quindi edad: 30 supera un bsonType: "int" ed edad: 30.5 lo infrange, perché quello sì è un double.

validationLevel e validationAction sono due manopole diverse

Il livello dice quali documenti raggiunge: strict tutti, moderate solo quelli che erano già validi — così si mette un validatore su una raccolta piena di vecchiume senza incepparne gli aggiornamenti. L'azione dice che cosa succede quando fallisce: error rifiuta, warn lascia scrivere e lo annota nel log. E mettere il validatore con collMod non tocca nulla di ciò che c'era già.

db.viejos.insertMany([{ _id: 1, nombre: "Uno" }, { _id: 2, nombre: "Dos" }])
db.runCommand({ collMod: "viejos",
  validator: { $jsonSchema: { bsonType: "object", required: ["nombre", "correo"] } },
  validationLevel: "moderate", validationAction: "error" })
db.viejos.countDocuments()                                  // 2 - no borra nada

db.viejos.updateOne({ _id: 1 }, { $set: { nombre: "Uno bis" } })   // moderate: pasa
db.viejos.insertOne({ _id: 3, nombre: "Tres" })                    // 121 igual

db.runCommand({ collMod: "viejos", validationLevel: "strict" })
db.viejos.updateOne({ _id: 2 }, { $set: { nombre: "Dos bis" } })   // 121

db.runCommand({ collMod: "viejos", validationAction: "warn" })
db.viejos.insertOne({ _id: 4, nombre: "Cuatro" })      // entra, y sólo avisa

Migrare vuol dire scrivere

Senza ALTER, l'equivalente di una colonna nuova è un updateMany con $set, e quello di toglierla, uno con $unset. Costano poco — 20 000 documenti in 61 e 51 ms — ma non sono atomici: vanno documento per documento, quindi durante la migrazione convivono le due forme.

db.pedidos.updateMany({ _v: 1 }, { $set: { moneda: "MXN", _v: 2 } })
// 20000 modificados en 61 ms

db.pedidos.updateMany({}, { $unset: { moneda: "" } })
// 20000 modificados en 51 ms

Per questo il motivo che regge è tenere la versione dentro ogni documento (_v): l'applicazione sa leggere entrambe, la migrazione avanza a lotti o al momento di toccare ogni documento, e il giorno in cui {_v: 1} non restituisce più nulla, il codice vecchio se ne va. È quello che fa anche una migrazione SQL, solo che qui lo stato intermedio è visibile e va scritto.

Parole chiave: schema, validazione, validatore, jsonSchema, 121, DocumentValidationFailure, validationLevel, validationAction, strict, moderate, warn, collMod, migrazione, versione di documento

I tre pezzi di un cluster frammentato, perché una chiave hashed distribuisce e una monotona ammucchia, la differenza misurata fra una query mirata e una diffusa, le zone, e quanto costa cambiare la chiave.

Si applica a: MongoDB 7.0+

Frammentare vuol dire ripartire una raccolta su più macchine, e servono tre pezzi: i frammenti, che tengono i dati e sono replica set; i server di configurazione, che tengono la mappa di quale pezzo sta dove; e mongos, il router, che non tiene nulla ed è quello a cui si collega l'applicazione.

# tres piezas distintas, y el enrutador no guarda datos
mongod --configsvr --replSet cfg --port 27019
mongod --shardsvr  --replSet sh1 --port 27018
mongod --shardsvr  --replSet sh2 --port 27018
mongos --configdb cfg/qa-cfg:27019

# ya conectado al mongos:
sh.addShard("sh1/qa-sh1:27018")
sh.addShard("sh2/qa-sh2:27018")   // config.shards: ["sh1", "sh2"]

La chiave di frammentazione è l'unica decisione che conta

Da lei escono tre cose insieme: come si distribuiscono i dati, quali query si possono dirigere a un solo frammento, e se c'è un punto caldo. Si chiede cardinalità — molti valori distinti —, frequenza pari, perché nessun valore si prenda la metà, e che non sia monotona, perché una chiave che cresce sempre manda tutte le scritture nuove nello stesso posto.

Quest'ultima non è teoria. Sulla stessa raccolta di 60 000 documenti: una chiave hashed sul cliente ha lasciato il 52,11 % su un frammento e il 47,88 % sull'altro; l'_id di ObjectId, che cresce sempre, ha lasciato il 100 % su uno solo, in un unico pezzo.

sh.enableSharding("ventas")

sh.shardCollection("ventas.pedidos", { cliente: "hashed" })
db.pedidos.getShardDistribution()
// 60000 documentos - sh1 52.11 %, sh2 47.88 % - 2 trozos

sh.shardCollection("ventas.eventos", { _id: 1 })
db.eventos.getShardDistribution()
// 60000 documentos - sh2 100 % - 1 trozo

Mirata o diffusa

Una query che porta la chiave va a un frammento e basta. Una che non la porta viene chiesta a tutti e le risposte si fondono: SINGLE_SHARD contro SHARD_MERGE. La differenza misurata è di 121 documenti esaminati contro 60 000.

db.pedidos.find({ cliente: 42 }).explain()
// SINGLE_SHARD - shards: ["sh2"]
// examinados 121, devueltos 121

db.pedidos.find({ estado: "pagado" }).explain()
// SHARD_MERGE  - shards: ["sh2", "sh1"]
// examinados 60000, devueltos 15000

Le zone, e cambiare chiave

Una zona lega un intervallo della chiave a un frammento, e serve a due cose vere: tenere i dati di un paese su macchine di quel paese, e separare il caldo dal freddo. E dalla 5.0 la chiave si può cambiare con reshardCollection, ma copia la raccolta intera e si sente: su 60 000 documenti stava ancora lavorando dopo due minuti, con la sua raccolta temporanea in bella vista.

sh.addShardToZone("sh1", "MX")
sh.addShardToZone("sh2", "EU")
// config.shards: sh1 tags ["MX"], sh2 tags ["EU"]

db.adminCommand({ reshardCollection: "ventas.eventos", key: { _id: "hashed" } })
// copia la coleccion entera: seguia en marcha a los dos minutos,
// con su system.resharding.<uuid> visible en config.collections

Tre cose che non sono più vere

Si racconta ancora che un updateOne senza la chiave fallisca, che il valore della chiave non si possa cambiare, e che l'indice debba esistere prima di frammentare. Sulla 8.2 tutte e tre sono passate senza protestare.

Parole chiave: frammentazione, sharding, chiave di frammentazione, hashed, chunk, mongos, server di configurazione, zona, bilanciatore, reshardCollection, SINGLE_SHARD, SHARD_MERGE

Gli otto codici che escono ogni giorno, provocati uno a uno con il testo che restituisce il server, e i due che portano dentro più del numero: l'11000 con la sua chiave e il 121 con il suo errInfo.

Si applica a: MongoDB 7.0+

Un errore di MongoDB porta un numero, quasi sempre un nome, e a volte qualcosa dentro che vale più di entrambi. Questi sono gli otto che escono ogni giorno, provocati uno a uno contro il server e ricopiati così come sono arrivati.

I due senza nome portano qualcosa di meglio

L'11000 e il 121 arrivano senza codeName, e non importa: entrambi portano il dato che serve per aggiustarli. L'11000 nomina la raccolta, l'indice e il valore che ha cozzato, quindi non c'è da indovinare quale di tre indici unici è scattato.

db.correos.createIndex({ correo: 1 }, { unique: true })
db.correos.insertOne({ correo: "a@b.c" })
db.correos.insertOne({ correo: "a@b.c" })
// 11000 :: E11000 duplicate key error collection: ventas.correos
//          index: correo_1 dup key: { correo: "a@b.c" }

Il 121 dice «Document failed validation» e nient'altro nel messaggio, ma e.errInfo.details porta la regola infranta con il suo nome e il valore atteso. È la differenza fra «non valido» e «manca correo».

db.createCollection("socios", {
  validator: { $jsonSchema: { bsonType: "object", required: ["correo"] } } })
db.socios.insertOne({ nombre: "sin correo" })
// 121 :: Document failed validation
// e.errInfo.details:
// { operatorName: "$jsonSchema", schemaRulesNotSatisfied: [
//     { operatorName: "required", specifiedAs: { required: ["correo"] },
//       missingProperties: ["correo"] } ] }

I due di permesso sono diversi

Il 18 è «non so chi sei»: password sbagliata, o — il più delle volte — la base di autenticazione sbagliata, perché un utente di MongoDB è il nome più la base in cui è stato creato. Il 13 è «so chi sei e non puoi»; il suo messaggio nomina la base, il comando e persino la raccolta, quindi il ruolo che manca si legge direttamente lì.

E due avvisi di sintassi

Il 50 non è un errore del server ma il tetto che gli hai messo tu con maxTimeMS. E il 26 spunta dove meno te l'aspetti: collMod e renameCollection su qualcosa che non esiste falliscono, ma drop() su una raccolta inesistente restituisce false e basta — non è un errore, quindi uno script che lo dà per scontato non scopre mai di aver sbagliato nome.

db.gordo.find({ s: /x{100}/ }).maxTimeMS(1).toArray()
// 50 MaxTimeMSExpired :: operation exceeded time limit

db.runCommand({ collMod: "no_existe", validationLevel: "strict" })
// 26 NamespaceNotFound :: ns does not exist

db.no_existe.drop()   // false, y ningun error

Parole chiave: errori, codici, 11000, duplicate key, 13, Unauthorized, 18, AuthenticationFailed, 26, NamespaceNotFound, 50, MaxTimeMSExpired, 112, WriteConflict, 121, 251, NoSuchTransaction, errInfo

Gli otto tetti che si toccano davvero, provocati uno a uno contro il server, con il codice che restituisce ciascuno e i tre che non stanno dove dice la loro fama.

Si applica a: MongoDB 7.0+

Tutti questi sono stati provocati contro il server, quindi il numero a sinistra è quello che ha davvero rifiutato, non quello della leggenda.

Quello dei 16 MB è l'unico che si tocca per sbaglio

E quasi sempre per lo stesso motivo: un array che cresce senza freno dentro un documento. Il messaggio porta entrambi i numeri, quello del documento e il massimo, quindi si vede a colpo d'occhio di quanto ha sforato.

db.c.insertOne({ _id: 1, s: "x".repeat(16 * 1024 * 1024 - 200) })   // entra
db.c.insertOne({ _id: 2, s: "x".repeat(16 * 1024 * 1024 + 100) })
// 10334 :: object to insert too large.
//          size in bytes: 16777338, max size: 16777216

I due tetti degli indici si contano male

I 64 indici per raccolta includono _id_, quindi ce ne stanno 63 propri; e non è un tetto che si raggiunge in salute: con 64 indici, ogni scrittura mantiene 64 alberi. Nemmeno i 32 campi di un indice composto sono un obiettivo: oltre i sei o sette, quasi sicuramente servono due indici, non uno più largo.

db.tope.insertOne({ a: 1 })
for (let i = 0; i < 70; i++) db.tope.createIndex({ ["c" + i]: 1 })
// crea 63, y el 64 falla:  el _id_ tambien cuenta
// 67 CannotCreateIndex :: add index fails, too many indexes

const k = {}
for (let i = 0; i < 33; i++) k["g" + i] = 1
db.comp.createIndex(k)          // con 32 pasa
// 13103 :: too many compound keys

Tre che non stanno dove dice la loro fama

L'annidamento è documentato a 100 livelli e quello che il server ha rifiutato è stato il 180°, perché il tetto è del BSON dell'intero comando e l'involucro dell'insert se ne prende una parte. Il nome lungo non fallisce sulla raccolta ma sulla somma di base e raccolta, cioè lo spazio dei nomi. E il tetto sulla dimensione di una chiave d'indice, che nelle vecchie versioni era 1 024 byte, non esiste più: un valore indicizzato di 50 000 è passato.

db.getSiblingDB("d".repeat(65)).c.insertOne({ a: 1 })
// 73 InvalidNamespace :: db name must be at most 63 characters, found: 65

db.createCollection("z".repeat(260))
// 73 InvalidNamespace :: Fully qualified namespace is too long

db.k.createIndex({ w: 1 })
db.k.insertOne({ w: "y".repeat(50000) })   // entra: no hay tope de clave

E quello che non ha tetto

Né il numero di raccolte, né quello delle basi, né quello dei documenti di una raccolta. Quello che finisce per primo non sta in questa tabella: è il disco. E per ciò che davvero non sta in 16 MB — un file — c'è GridFS, che lo taglia in pezzi da 255 KB e conserva ogni pezzo come un documento normale.

Parole chiave: limiti, tetti, 16 MB, dimensione del documento, 10334, annidamento, Overflow, indici per raccolta, 67, CannotCreateIndex, 13103, spazio dei nomi, 73, InvalidNamespace, chiave d'indice

Il riassunto del manuale di MongoDB: sette abitudini che valgono la pena, ognuna con la misura che la sostiene, e le cinque righe con cui si prende il polso a un server appena ereditato.

Si applica a: MongoDB 7.0+

Questa è la fine del manuale, e non porta niente di nuovo: raccoglie quello che ogni tema ha dimostrato, nella forma che serve tutti i giorni. Ogni abitudine arriva con il numero che la sostiene, e tutti quei numeri sono stati misurati contro un server vero, non ricopiati.

1. Modella per come leggerai, non per quanto somiglia a una tabella. Quello che si legge insieme si conserva insieme. Il limite di questa regola è duro ed è misurato: un documento non supera i 16 777 216 byte, quindi un array che cresce senza freno finisce in un 10334 un martedì qualunque.

2. Un indice per query frequente, e non uno di più. Ogni indice è un albero da mantenere a ogni scrittura: le stesse 20 000 inserzioni hanno impiegato 60 ms senza indici, 101 con cinque e 160 con dieci. E occupano: nella base d'esempio, una raccolta di 1 148 000 byte di dati ne portava 622 592 di indici.

3. Giudica una query da totalDocsExamined contro nReturned, non dall'orologio. L'orologio dice quanto ci ha messo oggi, con la cache calda e la macchina tranquilla; il rapporto fra quei due numeri dice che cosa succederà quando la raccolta sarà dieci volte più grande.

4. writeConcern: majority per quello che non si può perdere. Su un replica set è già il valore di fabbrica, quindi l'abitudine non è metterlo: è non toglierlo per andare più in fretta.

5. Mai senza autenticazione. È una riga nel file, e l'unica porta che un server appena acceso lascia aperta — creare il primo utente dalla macchina stessa — si chiude da sola non appena quell'utente esiste.

6. Fai il backup con l'oplog, e misura la sua finestra in tempo. mongodump --oplog è ciò che trasforma uno scarico in un istante. E la dimensione dell'oplog non si legge in byte ma in giorni: il nodo misurato ne dava 34, ma dipende da quanto si scrive, quindi è un numero da riguardare quando cambia il carico.

7. Il working set deve stare nella cache. È l'unica regola di prestazione senza trucchi. Nella base d'esempio, 1 150 242 byte di dati contro 3 621 781 504 di cache: spazio di troppo tremila volte. Il giorno in cui non ne avanza, si sente in tutto insieme.

Il polso di un server appena ereditato

Cinque righe rispondono a quello che serve sapere prima di toccare qualcosa: se chiede una password, quando una scrittura si considera scritta, se qualcuno sta guardando le query lente, quanta memoria ha per lavorare e quanti dati deve muovere.

db.adminCommand({ getCmdLineOpts: 1 }).parsed.security
// { authorization: "enabled" }, o undefined - que es la respuesta mala

db.adminCommand({ getDefaultRWConcern: 1 }).defaultWriteConcern
// { w: "majority", wtimeout: 0 }
// en un nodo suelto ni existe: "not supported on standalone nodes"

db.getProfilingStatus()      // { was: 1, slowms: 100, sampleRate: 1 }

db.serverStatus().wiredTiger.cache["maximum bytes configured"]   // 3621781504
db.stats().dataSize                                              // 1150242

E un'altra ancora, quella che mostra dove se ne va il disco e, di passaggio, quale raccolta porta più indice che dati.

db.getCollectionInfos({ type: "collection" }).map(i => {
  const s = db.getCollection(i.name).stats()
  return { c: i.name, indices: s.nindexes, datos: s.size, indice: s.totalIndexSize }
})
// { c: "eventos", indices: 3, datos: 1148000, indice: 622592 }
// el filtro por type hace falta: stats() sobre una vista falla

Parole chiave: buone pratiche, riassunto, modello di accesso, indici, writeConcern, majority, autenticazione, oplog, working set, cache, explain, salute del server

Il tipo vive nel valore e non nella colonna: le cinque affinità e che cosa converte ciascuna, l'ordine fra classi di memorizzazione, che cosa impedisce davvero STRICT, e perché NOCASE non sa nulla di accenti.

Si applica a: SQLite 3.35+

In SQLite il tipo è del valore, non della colonna. Quello che una colonna dichiara è un'affinità: una preferenza applicata al momento di salvare, che converte se può e lascia passare se non può.

CREATE TABLE t (i INTEGER, r REAL, x TEXT, b BLOB, n NUMERIC);
INSERT INTO t VALUES ('42', '42', 42, 42, '42');
INSERT INTO t VALUES (7.0,  7,   '7', '7', '7.5');

SELECT typeof(i), typeof(r), typeof(x), typeof(b), typeof(n) FROM t;
-- integer | real | text | integer | integer
-- integer | real | text | text    | real

Eccole tutte e cinque in una riga. INTEGER e REAL convertono il testo che sembra un numero; TEXT converte il numero in testo; NUMERIC guarda il valore e decide, quindi nella stessa colonna stanno un integer e un real; e BLOB è quella senza affinità: conserva quello che le danno, così com'è arrivato.

Le classi di memorizzazione sono cinque, e sono ordinate

Una colonna senza tipo dichiarato è legale e accetta tutte e cinque, quindi in una stessa colonna stanno un intero, un reale, un testo, un blob e un nullo. E si possono ordinare, perché fra le classi c'è un ordine fisso: prima i nulli, poi i numeri, poi il testo e infine i blob. Il che significa che un ORDER BY su una colonna sporca non fallisce: raggruppa per tipo senza dirlo a nessuno.

CREATE TABLE libre (v);            -- sin tipo declarado: vale
INSERT INTO libre VALUES (1), (1.5), ('hola'), (x'0001'), (NULL);

SELECT typeof(v) FROM libre ORDER BY v;
-- null | integer | real | text | blob   <- y ese es el orden entre clases

STRICT impedisce meno di quanto sembri

Dalla 3.37 una tabella si può dichiarare STRICT, e allora accetta solo una manciata di tipi — INT, INTEGER, REAL, TEXT, BLOB e ANY — e rifiuta ciò che non può conservare. Ma continua a convertire: un '42' entra in una colonna INTEGER perché non si perde nulla, e un 42 entra in una TEXT e si conserva come '42'. Quello che rifiuta è ciò che non ha conversione. E c'è un guadagno inatteso: un tipo inventato, che una tabella normale accetta in silenzio, qui viene rifiutato già alla creazione.

CREATE TABLE s (i INTEGER, x TEXT) STRICT;

INSERT INTO s VALUES ('42', 'a');   -- entra: 42, convertible sin perder nada
INSERT INTO s VALUES (1, 42);       -- entra: el 42 se guarda como texto '42'
INSERT INTO s VALUES ('abc', 'a');
-- cannot store TEXT value in INTEGER column s.i

CREATE TABLE s2 (d DATETIME) STRICT;
-- unknown datatype for s2.d: "DATETIME"

Niente data, niente booleano, e la collazione non sa di accenti

TRUE è un integer di valore 1. Una data è quello che decidi tu: date() restituisce text e julianday() restituisce real, e quello che scegli è quello che ordinerai e confronterai per il resto della sua vita. E ci sono solo tre collazioni — BINARY, NOCASE e RTRIM: NOCASE pareggia maiuscole e minuscole dell'ASCII e nient'altro, quindi café e CAFÉ sono valori diversi e upper('café') restituisce CAFé. Il testo è davvero UTF-8: length conta caratteri, e sul blob conta byte.

SELECT typeof(TRUE), TRUE;                    -- integer | 1
SELECT typeof(date('2026-09-12'));            -- text
SELECT typeof(julianday('2026-09-12'));       -- real

CREATE TABLE n (v TEXT COLLATE NOCASE);
INSERT INTO n VALUES ('Cafe'), ('CAFE'), ('café');

SELECT v FROM n WHERE v = 'cafe';             -- Cafe, CAFE
SELECT v FROM n WHERE v = 'CAFÉ';             -- nada
SELECT upper('café'), length('café'), length(CAST('café' AS BLOB));
-- CAFé | 4 | 5

Parole chiave: tipi, affinità, typeof, INTEGER, REAL, TEXT, BLOB, NUMERIC, tipizzazione dinamica, STRICT, booleano, data, julianday, COLLATE, NOCASE, UTF-8, classe di memorizzazione

L'isolamento è serializzabile perché scrive uno solo: i tre modi di BEGIN, lo SQLITE_BUSY 5 e perché busy_timeout lo risolve, il 517 che non risolve, e che cosa cambia davvero la modalità WAL.

Si applica a: SQLite 3.35+

SQLite non ha livelli di isolamento da scegliere, e non è una mancanza: l'isolamento è serializzabile perché in tutta la base scrive uno solo alla volta. Tutto il resto discende da lì.

Due impostazioni lo governano, ed entrambe arrivano di fabbrica con il valore peggiore possibile.

PRAGMA journal_mode;    -- delete: el de fábrica, no WAL
PRAGMA busy_timeout;    -- 0: no espera nada

PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 3000;

I tre modi di BEGIN

DEFERRED — quello predefinito — non prende nulla finché non serve: la prima lettura prende una fotografia e la prima scrittura chiede il lucchetto. IMMEDIATE chiede subito il lucchetto di scrittura, nella riga stessa del BEGIN. EXCLUSIVE chiede in più che nessuno legga, e in modalità WAL non fa quasi nulla di diverso da IMMEDIATE. La regola pratica: se la transazione scriverà, BEGIN IMMEDIATE; costa un'attesa all'inizio ed evita l'errore più fastidioso di SQLite, quello più sotto.

SAVEPOINT è il segno intermedio, e ROLLBACK TO ci torna senza chiudere la transazione.

BEGIN;                          -- DEFERRED, el de por omision
UPDATE t SET v = 10 WHERE i = 1;
SAVEPOINT s1;
UPDATE t SET v = 20 WHERE i = 1;
ROLLBACK TO s1;                 -- deshace hasta aqui, NO cierra la transaccion
SELECT v FROM t WHERE i = 1;    -- 10
RELEASE s1;
COMMIT;

SQLITE_BUSY è il 5, ed è quasi sempre tuo

Quando un altro tiene il lucchetto di scrittura, la risposta è SQLITE_BUSY con il codice 5 e il testo «database is locked». Non è un guasto: è la coda di una risorsa a corsia unica. A renderlo un guasto è che busy_timeout vale 0 di fabbrica, quindi senza toccarlo la risposta è immediata e secca. Con 800 ms impostati, la stessa chiamata ha aspettato 895 prima di arrendersi.

# dos conexiones a la vez, con timeout=0 para que conteste en el acto
a = sqlite3.connect(db, isolation_level=None, timeout=0)
b = sqlite3.connect(db, isolation_level=None, timeout=0)

b.execute("BEGIN IMMEDIATE"); b.execute("UPDATE t SET v=1 WHERE i=2")

a.execute("SELECT v FROM t WHERE i=2")   # (0,)  <- lee el valor de antes
a.execute("BEGIN IMMEDIATE")             # SQLITE_BUSY (5) database is locked

a.execute("PRAGMA busy_timeout=800")
a.execute("BEGIN IMMEDIATE")             # espera 895 ms y vuelve a dar 5

Quello che busy_timeout non risolve: il 517

Se una transazione DEFERRED legge e poi vuole scrivere, e nel frattempo qualcuno ha confermato, la fotografia presa in lettura non vale più e SQLite restituisce SQLITE_BUSY_SNAPSHOT, il 517. Aspettare non serve: nessuno restituirà quella fotografia. L'unica uscita è ROLLBACK e ricominciare — o, meglio, aver aperto con IMMEDIATE.

-- A:
BEGIN DEFERRED;
SELECT v FROM t WHERE i = 1;    -- aqui A se queda con una foto de la base

-- B, en otra conexion:
BEGIN IMMEDIATE; UPDATE t SET v = 5 WHERE i = 2; COMMIT;

-- A, que ahora quiere escribir:
UPDATE t SET v = 2 WHERE i = 1;
-- SQLITE_BUSY_SNAPSHOT (517), y busy_timeout no lo arregla
ROLLBACK;                       -- la unica salida: soltar y volver a empezar

Che cosa cambia WAL, e che cosa no

Con il giornale di annullamento, mentre uno scrive nessuno legge. Con journal_mode = WAL, i lettori continuano a leggere l'ultima versione confermata mentre lo scrittore lavora: misurato con due connessioni, il lettore ha ottenuto il valore precedente senza bloccarsi un istante. Quello che non cambia è il numero di scrittori: resta uno, e il secondo continua a ricevere un 5.

Parole chiave: transazione, BEGIN, DEFERRED, IMMEDIATE, EXCLUSIVE, SAVEPOINT, ROLLBACK TO, SQLITE_BUSY, 5, 517, BUSY_SNAPSHOT, busy_timeout, WAL, journal_mode, blocco, serializzabile

Un pragma non è una cosa sola: alcuni si scrivono dentro il file, altri durano quanto la connessione, altri sono comandi. Quale è quale, i valori di fabbrica misurati, e quello che è spento e non dovrebbe.

Si applica a: SQLite 3.35+

SQLite non ha file di configurazione: ha i pragma. E la prima confusione da togliersi è che non sono tutti la stessa cosa. Alcuni si scrivono dentro il file e valgono per chi lo aprirà dopo; altri durano quanto la connessione e vanno ripetuti ogni volta; e altri non sono impostazioni ma comandi che fanno qualcosa e basta.

Questi sono i valori di fabbrica, letti da una base appena creata.

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

Quello che è spento e quasi nessuno se lo aspetta

foreign_keys vale 0. Le chiavi esterne si dichiarano, si conservano nello schema, compaiono nel CREATE TABLE… e non si controllano. Un figlio orfano entra senza un fiato. Accenderlo è una riga, ma è della connessione: va messo su ognuna, e accenderlo non guarda indietro — per quello c'è foreign_key_check, che elenca ciò che è già passato.

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

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

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

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

Quale resta e quale no

journal_mode e user_version si scrivono nell'intestazione del file e sopravvivono a chiudere e riaprire. Anche page_size e auto_vacuum, ma solo se si mettono prima della prima tabella: su una base che ha già pagine vengono accettati senza errore e non cambiano niente, e lo si è verificato in entrambi i modi. foreign_keys, cache_size, busy_timeout e mmap_size sono della connessione e tornano al valore di fabbrica appena se ne apre un'altra.

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

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

Un dettaglio che spiazza: dopo aver messo journal_mode = WAL, synchronous valeva 1 (NORMAL) senza che nessuno lo toccasse. È voluto — in WAL basta NORMAL per non perdere nulla di confermato — ma insegna che leggere un pragma non dice da dove viene quel valore.

E quelli che sono comandi

wal_checkpoint versa il giornale nella base e, con TRUNCATE, lo lascia a zero byte: si è misurato un -wal di 4 716 016 byte finito a 0. ANALYZE riempie sqlite_stat1 con quello che il pianificatore userà per scegliere un indice. E PRAGMA optimize è quello che conviene lanciare chiudendo una connessione di lunga vita: guarda quali tabelle sono cambiate abbastanza e avvia l'ANALYZE che serve, senza dire niente.

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

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

Parole chiave: pragma, configurazione, journal_mode, WAL, synchronous, foreign_keys, cache_size, mmap_size, auto_vacuum, page_size, user_version, wal_checkpoint, optimize, ANALYZE, sqlite_stat1

SCAN, SEARCH e COVERING sono tutto il vocabolario di EXPLAIN QUERY PLAN. Gli indici parziali e per espressione e la condizione da ripetere perché servano, che cosa fa risparmiare WITHOUT ROWID e che cosa scrive ANALYZE.

Si applica a: SQLite 3.35+

In SQLite c'è un solo tipo di indice: l'albero B. Niente hash, niente bitmap, niente da scegliere. La ricerca per parole esiste ma non è un indice: è FTS5, una tabella virtuale a parte. Questo semplifica tutto il tema, perché l'unica decisione che resta è su quali colonne e in che ordine.

E si verifica con EXPLAIN QUERY PLAN, il cui vocabolario sta in tre parole: SCAN è leggere la tabella intera, SEARCH è entrare da un indice, e COVERING è che la riga non è stata nemmeno toccata.

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

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

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

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

Il composto si percorre per prefissi, come in qualunque motore: (cliente, estado) serve per cliente da solo e per i due insieme, ma non per estado da solo.

Coprente

Se l'indice porta tutte le colonne che la query legge, la riga non si tocca. È lo stesso indice di prima: quello che cambia è ciò che si chiede.

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

Parziale e per espressione: entrambi vanno ripetuti

Un indice parziale indicizza solo le righe che soddisfano una condizione, ed è per questo che occupa poco. Il prezzo è che la query deve ripetere quella condizione, parola per parola, o il pianificatore non può usarlo: senza, la stessa query torna a SCAN. Lo stesso con l'indice per espressione: indicizza lower(correo), quindi va scritto lower(correo) nel WHERE; con correo da solo non serve a nulla.

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

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

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

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

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

WITHOUT ROWID toglie un'indirezione

Una tabella normale conserva le righe sotto un rowid nascosto, quindi la sua chiave primaria è un altro indice che poi deve andare a prendere la riga. Con WITHOUT ROWID la tabella è l'albero della sua chiave primaria: si risparmia il salto e si risparmia spazio — 1 335 296 byte contro 1 675 264 sulla stessa tabella di 20 000 righe, il 20 % in meno — e il piano lo tradisce dicendo USING PRIMARY KEY invece di nominare un indice automatico.

ANALYZE dà numeri, non miracoli

Riempie sqlite_stat1 con il numero di righe e quante ce ne sono per ogni valore dell'indice. Quel secondo numero è quello che dice se un indice serve a qualcosa: 20 000 per valore significa che non distingue nulla. Ma non sempre cambia la scelta: nella misura il pianificatore stava già scegliendo bene prima di lanciarlo, perché senza statistiche usa supposizioni ragionevoli. Lanciare ANALYZE toglie le supposizioni; non promette un piano diverso.

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

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

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

Parole chiave: indice, indici, albero B, EXPLAIN QUERY PLAN, SCAN, SEARCH, COVERING INDEX, indice parziale, indice per espressione, WITHOUT ROWID, ANALYZE, sqlite_stat1, FTS5

Le stesse 20 000 inserzioni misurate in sei modi: la differenza fra la migliore e la peggiore non sta in nessun pragma, sta nel fatto che ci sia un BEGIN. E quello che davvero apportano WAL, VACUUM e la dimensione di pagina.

Si applica a: SQLite 3.35+

C'è una cosa sola che conta, e non è un pragma. Le stesse 20 000 inserzioni, stesso schema, stessa macchina:

440 volte, e la spiegazione è che senza BEGIN ogni INSERT è la propria transazione: ventimila conferme, ognuna in attesa del disco.

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

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

Attenzione a una trappola dello strato di mezzo: che il driver offra una chiamata di «inserimento a lotti» non vuol dire che apra una transazione. L'executemany di Python, senza BEGIN esplicito, ha impiegato 4 128 ms: esattamente quanto il ciclo a mano.

Che cosa apportano gli altri

Spegnere synchronous ha risparmiato il 38 % e in cambio offre di perdere dati confermati in un black-out: il peggior affare della lista. WAL senza transazione è sceso a 258 ms — quindici volte — perché confermare smette di riscrivere la base, e quello sì è un cambiamento che si può lasciare. Ma con entrambi in gioco, il BEGIN si porta via quasi tutto: 9 ms senza WAL e 10 con. Prima si raggruppa, e solo dopo si affina.

VACUUM è quello che restituisce lo spazio

Cancellare non rimpicciolisce il file: le pagine restano in un elenco di libere per essere riusate. Si è misurato un file di 10 813 440 byte a cui è stata cancellata metà delle righe e che ha continuato a misurare esattamente uguale. VACUUM lo riscrive per intero e lo ha lasciato a 5 410 816. Costa una copia della base e un lucchetto esclusivo, quindi non è un lavoro di ogni notte: è quello che si lancia quando una cancellazione grande ha lasciato il file del doppio di quanto gli tocca.

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

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

VACUUM;                  -- 5410816 bytes, en 12 ms

La dimensione di pagina quasi mai va toccata

Ne sono state misurate tre. Abbassarla a 512 è costato il 20 % di file in più e un percorso misurabile dove le altre due non arrivavano al millisecondo; alzarla a 65 536 non ha guadagnato nulla. Quella di fabbrica — 4 096 — è quella da lasciare, e per giunta si può cambiare solo prima della prima tabella, o dopo passando per un VACUUM.

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

E altre due cose che si misurano da sole

Ogni indice si paga a ogni scrittura: le stesse 20 000 righe hanno impiegato 7 ms senza indici propri, 12 con uno, 16 con due e 20 con tre, e il file è passato da 458 752 a 1 277 952 byte. E un'istruzione con parametro si riusa: 5 000 query con ? hanno impiegato 18 ms, e le stesse con il valore incollato nel SQL, 26 — oltre a essere la porta da cui entra l'iniezione.

Parole chiave: prestazioni, transazione, BEGIN, COMMIT, lotto, executemany, synchronous, WAL, VACUUM, freelist_count, page_size, frammentazione, istruzione preparata

Quello che ALTER TABLE sa fare sta in quattro righe, e quello che rifiuta aggiungendo e togliendo una colonna è misurato con il suo messaggio. Il giro di creare, copiare, cancellare e rinominare, e perché qui è sicuro.

Si applica a: SQLite 3.35+

ALTER TABLE sa fare quattro cose, e non una di più. Cambiare il tipo di una colonna, toglierle un NOT NULL, aggiungere una chiave esterna: niente di tutto questo esiste, e il tentativo non arriva nemmeno a essere un errore di schema, è un errore di sintassi.

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

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

Che cosa rifiuta ADD COLUMN

I tre rifiuti hanno la stessa causa: la colonna nuova si aggiunge senza toccare le righe già presenti, quindi il valore che ricevono deve potersi decidere senza guardarle. Un DEFAULT che cambia, un UNIQUE che andrebbe verificato e un NOT NULL senza valore non lo soddisfano.

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

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

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

Che cosa rifiuta DROP COLUMN, e peggio: che cosa permette

Dalla 3.35 una colonna si può togliere, ma non se fa parte della chiave primaria, né se è UNIQUE, né se un indice o una colonna generata la nomina. Fin qui, bene. Il problema è il caso che lascia passare: una colonna usata da una vista si toglie senza un fiato, la vista resta rotta, e PRAGMA integrity_check continua a dire ok perché non guarda dentro le viste. Nessuno avvisa finché qualcuno non interroga.

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

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

Il giro di sempre

Per tutto il resto la procedura è: creare la tabella nuova, copiare, cancellare la vecchia e rinominare. Sembra pericoloso e qui non lo è, per una cosa che MySQL non ha: il DDL di SQLite è transazionale. Misurato: un CREATE TABLE e un ADD COLUMN dentro un BEGIN, con ROLLBACK alla fine, non hanno lasciato né la tabella né la colonna. Quindi tutto il giro sta in una transazione e, se qualcosa va storto a metà, non resta nulla a metà.

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

Tre cautele. Indici, trigger e viste della tabella vecchia se ne vanno con lei e vanno ricreati, perché il DROP TABLE se li porta. Le chiavi esterne si spengono durante il giro e si controllano con foreign_key_check prima di riaccenderle. E la conversione di tipo è affar tuo: un CAST('a' AS INTEGER) restituisce 0, senza una parola di avviso.

Parole chiave: DDL, ALTER TABLE, RENAME TO, RENAME COLUMN, ADD COLUMN, DROP COLUMN, 3.25, 3.35, vista rotta, integrity_check, DDL transazionale, dodici passi, CAST

Una base in modalità WAL sono tre file e i dati quasi mai stanno nel primo: copiarlo lascia una base vuota che si dichiara sana. I tre modi che funzionano, misurati, e che cosa trova ogni controllo di integrità.

Si applica a: SQLite 3.35+

Una base SQLite sembra un file, ed è lì che cominciano i guai. In modalità WAL sono tre, e quello che porta il nome può non portare nessun dato: dopo aver scritto 30 000 righe, il .sqlite misurava 4 096 byte — l'intestazione e poco altro — e il -wal misurava 3 366 072.

Copiare solo il primo non dà una base rotta. Dà qualcosa di peggio: una base che si apre senza lamentarsi, che non ha una sola tabella, e a cui PRAGMA integrity_check risponde ok. Un backup così passa tutti i controlli e non contiene niente.

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

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

I tre modi che funzionano

VACUUM INTO scrive una copia pulita e deframmentata in un altro file, con la base in uso: 3 338 240 byte in 4 ms, con le 30 000 righe. L'API di backup — il .backup della riga di comando e Connection.backup nei driver — fa lo stesso copiando pagine e può andare a rate: 3 338 240 byte in 3 ms. E .dump scrive il SQL che ricostruisce la base: 4 008 968 byte di testo e 30 003 istruzioni, il 20 % in più del binario, ma è l'unico che si legge con gli occhi e l'unico che sopravvive a un cambio di formato.

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

Un dettaglio gradito: la copia di VACUUM INTO esce in journal_mode delete, non in WAL. È un solo file, che è esattamente quello che si vuole da un backup.

Copiare i tre file insieme con la base ferma funziona eccome. Il problema sono «insieme» e «ferma»: finché qualcuno scrive non c'è istante in cui i tre tornino, e nessuno strumento di copia lo garantisce.

Controllare quello che si ha

integrity_check percorre la base intera e quick_check salta i controlli incrociati fra indici e tabelle. Su una base sana entrambi hanno detto ok, e sulla stessa base con qualche centinaio di byte rovinati apposta, entrambi hanno detto esattamente la stessa cosa: Tree 2 page 4 cell 35: Rowid 0 out of order. La differenza fra i due si vede solo su una base grande, e nessuno dei due ripara nulla: servono a decidere se tornare al backup.

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

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

E quando è già tardi

Ripristinare un .dump è lanciare il suo SQL contro una base vuota. Ripristinare una copia binaria è rimetterla al suo posto, e lì conviene sapere che VACUUM INTO si rifiuta di sovrascrivere: su un file che esiste già risponde «output file already exists», quindi non c'è modo di calpestare il backup di ieri senza accorgersene. E se quello che resta è una base danneggiata e nessun backup, c'è .recover della riga di comando, che percorre le pagine ancora comprensibili e scrive il SQL per ricostruire quel che si può: non promette tutto, promette quel che resta.

Parole chiave: backup, copia di sicurezza, VACUUM INTO, API di backup, dump, WAL, -wal, -shm, integrity_check, quick_check, corruzione, ripristino

Gli otto che escono davvero, provocati uno a uno con il loro testo, e l'aritmetica del codice esteso: il 19 di vincolo diventa 275, 787, 1299, 1555 o 2067 a seconda di che cosa è stato infranto.

Si applica a: SQLite 3.35+

SQLite ha due serie di codici: una base, di una o due cifre, e una estesa, che dice la stessa cosa con più dettaglio. E il rapporto fra le due è aritmetica: l'esteso è il base più 256 per il sottotipo, quindi codice & 255 restituisce sempre il base. Un driver che mostra solo il base te ne nasconde metà.

Il 19 sono cinque errori diversi

Il base non dice niente di utile, perché un vincolo infranto può essere uno qualunque di cinque. L'esteso sì, e il messaggio aiuta… con un'eccezione: la chiave primaria di un INTEGER PRIMARY KEY dà 1555, ma il suo testo dice «UNIQUE constraint failed». Lì il numero è più preciso della frase.

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

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

Il 5 e il 6 si confondono e non sono la stessa cosa

Il 5 viene da fuori: un'altra connessione sta scrivendo, e si risolve aspettando — è lì che busy_timeout si guadagna il posto. Il 6 viene da dentro: la stessa connessione ha un cursore aperto sulla tabella che vuole cambiare, e aspettare non serve a niente perché chi blocca sei tu. Si risolve chiudendo il cursore.

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

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

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

Il 13 quasi mai è il disco

SQLITE_FULL suona come una partizione piena ed è spesso il tetto che la base si è messa da sola: max_page_count. Con 20 pagine si provoca in una riga, e il messaggio è lo stesso che darebbe un disco davvero pieno: «database or disk is full».

L'11, il 26 e quello che non si vede

I due codici di file rotto si distinguono per dove sta il danno: se quello che non ha senso è una pagina, SQLITE_CORRUPT con «database disk image is malformed»; se quello che non ha senso è l'intestazione, non ci prova nemmeno e dice SQLITE_NOTADB. E il 21 è quello strano: chiamare l'API in un ordine impossibile. Non si vede quasi mai, perché il driver di turno lo intercetta prima e lancia un errore suo; in Python, per dirne una, esce un ProgrammingError che non porta nemmeno un codice di SQLite.

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

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

Parole chiave: errori, codici, SQLITE_BUSY, 5, SQLITE_LOCKED, 6, SQLITE_READONLY, 8, SQLITE_CORRUPT, 11, SQLITE_FULL, 13, SQLITE_CONSTRAINT, 19, 275, 787, 1299, 1555, 2067, SQLITE_MISUSE, 21, SQLITE_NOTADB, 26

I tetti di SQLite non sono del formato ma del binario, e si leggono con PRAGMA compile_options. Gli otto che si toccano davvero, provocati uno a uno, e l'unico che si supera senza una parola.

Si applica a: SQLite 3.35+

I tetti di SQLite hanno una particolarità che nessun altro motore ha: non sono del formato, sono del binario con cui stai parlando. Si fissano alla compilazione, ed è per questo che la risposta a «quanto è il massimo?» comincia con PRAGMA compile_options, che li mostra tutti. Un programma può inoltre abbassarli a caldo con sqlite3_limit, mai alzarli.

Questi sono quelli del binario che porta macOS, e i primi cinque sono stati provocati.

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

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

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

Quello che non avvisa

PRAGMA page_size = 131072 non dà errore, non restituisce niente di strano e non cambia niente: la pagina resta a 4 096. Il massimo è 65 536 e quello che si chiede sopra viene scartato in silenzio, quindi l'unico modo di sapere se ha attecchito è rileggerlo. È lo stesso modo di fallire che page_size e auto_vacuum hanno già su una base con tabelle: accettati, e senza effetto.

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

Quanto ci sta davvero

La dimensione massima del file non è una costante: è max_page_count per la dimensione di pagina. Con i valori di fabbrica — 1 073 741 823 pagine da 4 096 byte — escono 4 TiB, e alzando la pagina a 65 536, 64 TiB. Molto prima di avvicinarsi, finisce qualcos'altro: un testo non supera i 1 000 000 000 byte, e un SELECT con più di 250 000 parametri non si riesce nemmeno a preparare.

E un avviso sulla tabella qui sopra: è quella di questo binario. Quello di un telefono, quello di una libreria incorporata o quello che qualcuno ha compilato con le proprie opzioni possono portare altri numeri, ed è per questo che la risposta utile non è mai il valore: è il comando che lo chiede.

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

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

Abbassarli è una difesa

Che sqlite3_limit sappia solo abbassare non è un difetto: è la sua ragione d'essere. Un'applicazione che accetta SQL scritto da altri abbassa LENGTH, COMPOUND_SELECT ed EXPR_DEPTH a quello che le serve davvero, e così una query ostile non può più chiedere un gigabyte di memoria. È la stessa idea del max_page_count del tema degli errori: il tetto che ci si mette da soli avvisa prima di quello del sistema, e avvisa di qualcosa che si può aggiustare.

Parole chiave: limiti, tetti, compile_options, MAX_COLUMN, MAX_COMPOUND_SELECT, MAX_ATTACHED, MAX_LENGTH, MAX_PAGE_SIZE, max_page_count, sqlite3_limit, dimensione massima, colonne, ATTACH

Il riassunto del manuale di SQLite: sette abitudini con la misura che le sostiene, quattro delle quali nelle righe subito dopo aver aperto la connessione, e le sei con cui si prende il polso a un file altrui.

Si applica a: SQLite 3.35+

Questa è la fine del manuale, e non porta niente di nuovo: raccoglie quello che ogni tema ha lasciato misurato. Quello che colpisce è dove cadono quattro delle sette: nelle righe che si scrivono subito dopo aver aperto la connessione, e che quasi nessun programma scrive.

1. PRAGMA journal_mode = WAL. Resta scritto nel file, quindi basta una volta. Con esso un lettore ha continuato a leggere mentre un altro scriveva, senza bloccarsi un istante. Quello che non risolve è il numero di scrittori: resta uno.

2. PRAGMA foreign_keys = ON, su ogni connessione. È l'unica cosa di questo elenco che cambia quello che la base accetta, e arriva spenta: con essa spenta, un figlio orfano entra senza un fiato. E non resta messa, quindi va nello stesso posto del busy_timeout.

3. PRAGMA busy_timeout, e messo da te. Il motore lo porta a 0 — risponde SQLITE_BUSY all'istante — ma molti driver lo cambiano al collegarsi: quello di Python lo lascia a 5 000 senza dirlo. Quindi il numero che conta non è quello della documentazione: è quello che restituisce PRAGMA busy_timeout sulla tua connessione.

4. Raggruppa le scritture in una transazione. È, di gran lunga, quello che cambia di più: le stesse 20 000 inserzioni hanno impiegato 3 963 ms una alla volta e 9 ms dentro un BEGIN. E occhio allo strato di mezzo: una chiamata di «inserimento a lotti» del driver non apre una transazione da sola.

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

5. Fai il backup con VACUUM INTO, mai copiando il file. In modalità WAL i dati stanno nel -wal, quindi copiare il .sqlite dà una base che si apre, non ha una sola tabella, e a cui integrity_check risponde ok. Un backup che passa tutti i controlli ed è vuoto è peggio che non averne nessuno.

6. Un indice per query frequente, e guarda quanto pesa. dbstat lo dice per oggetto, e sorprende: nella base misurata l'indice occupava 2 056 192 byte contro 1 826 816 della tabella che indicizzava.

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

7. La sicurezza è del file. Non ci sono utenti, né ruoli, né GRANT: chi può leggere il file può leggere tutto, e chi può scriverlo può cancellarlo. La protezione sono i permessi del sistema, la cifratura del disco e — su un telefono — la classe di protezione dei dati. Tutto il resto di questo manuale è prestazione; questa è l'unica cosa senza sostituto.

E una che non è un'abitudine ma un confine

SQLite regge molto più di quanto la sua fama suggerisca, ma ha un confine che nessuna pratica sposta: scrive uno alla volta. Finché le scritture vengono da un processo, o da più processi che si alternano, il file arriva a limiti che quasi nessuno tocca. Il giorno in cui servono due scrittori veri e simultanei, quello che va cambiato non è un pragma: è il motore.

Parole chiave: buone pratiche, riassunto, WAL, foreign_keys, busy_timeout, transazione a lotti, VACUUM INTO, backup, dbstat, indici, permessi, cifratura, sicurezza

Privilegio minimo, ruoli, connessioni cifrate e la lista di controllo prima di esporre un server.

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

In MySQL e MariaDB l'identità di un utente è fatta di due cose: il nome e l'host da cui si connette. 'app'@'10.0.%' e 'app'@'%' sono account distinti, con password e permessi diversi. La maggior parte degli spaventi di sicurezza comincia dal dimenticarlo.

Privilegio minimo
Concedi ciò che l'applicazione usa, non uno di più, e sull'host più stretto possibile. Un account applicativo non ha quasi mai bisogno di DROP, e mai di SUPER, FILE o GRANT OPTION:

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

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

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

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

Ruoli

CREATE ROLE 'lectura', 'escritura';

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

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

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

Connessioni cifrate
Senza TLS la password e i dati viaggiano leggibili sulla rete. Si può imporre per account o per l'intero server con require_secure_transport. Calíope supporta TLS nel profilo di connessione, e anche il tunnel SSH quando il server non è esposto:

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

SHOW VARIABLES LIKE 'require_secure_transport';

SELECT user, host, ssl_type FROM mysql.user;

Audit rapido
Tre query da far girare su qualsiasi server ereditato. Account senza password, account aperti a qualunque host e privilegi pericolosi distribuiti:

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

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

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

Prima di esporre un server
1. Nessun account anonimo né senza password, e nessun database di esempio test.
2. root solo da localhost, con un account amministrativo separato per il resto.
3. bind-address sull'interfaccia giusta — non 0.0.0.0 se dall'esterno non deve arrivare nessuno.
4. TLS obbligatorio per ogni connessione che esce dalla macchina.
5. Password gestite fuori dal codice — Calíope le tiene nel Portachiavi, mai in chiaro.
6. Account separati per applicazione, così una compromissione non si trascina dietro il resto.
7. Rivedere i GRANT periodicamente: i permessi si accumulano e nessuno li toglie.

Raccomandazione
Comincia col revocare invece che col concedere: crea l'account senza nulla e aggiungi privilegi finché l'applicazione funziona. Lo strumento Utenti di Calíope mostra i privilegi effettivi per database e per tabella, che è dove di solito saltano fuori le sorprese.

Parole chiave: sicurezza, utente, ruolo, privilegio, grant, revoke, privilegio minimo, ssl, tls, require ssl, hardening, mysql.user, user_privileges, audit

Logico contro fisico, a cosa serve il binlog e come tornare al minuto prima del DELETE.

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

Un backup che non è mai stato ripristinato non è un backup: è un'intenzione. Qui comandano due numeri: l'RPO (quanti dati accetti di perdere) e l'RTO (quanto puoi restare fermo). Tutto il resto discende da lì.

Logico contro fisico
- Logico (mysqldump, il Backup di Calíope) — genera SQL. Portabile tra versioni e motori, permette di ripristinare una sola tabella ed è lento da ripristinare su grandi volumi.
- Fisico (snapshot del volume, Percona XtraBackup, copia della directory a server fermo) — copia i file. Rapidissimo da ripristinare, ma legato alla versione e all'architettura del server.

Regola pratica: fino a qualche decina di gigabyte, logico; oltre, fisico per la copia completa e logico per i pezzi singoli.

Il binlog è la metà che manca
Il backup ti riporta al momento in cui è stato fatto. Il binary log contiene tutto ciò che è successo dopo, ed è quello che permette di avanzare da lì fino a un secondo prima del disastro. Senza log_bin attivo non c'è ripristino a un punto nel tempo, solo il ritorno all'ultima copia:

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

SHOW BINARY LOGS;

Ripristinare a un punto nel tempo
La procedura, sempre su un server a parte e mai su quello in produzione:
1. Ripristina la copia completa più recente precedente all'incidente.
2. Individua il momento esatto dell'errore nel binlog: l'istruzione che ha cancellato troppo, con la sua posizione o il suo timestamp.
3. Riproduci il binlog dalla posizione in cui finiva la copia fino a poco prima di quell'istruzione, con mysqlbinlog e le sue opzioni --start-position e --stop-position (oppure --start-datetime e --stop-datetime).
4. Verifica che i dati ci siano, e solo allora decidi se promuovere quel server o esportarne ciò che manca.

Il Visualizzatore binlog di Calíope serve al passo 2: filtra gli eventi per data, database e tipo di operazione, che è la parte scomoda da fare a mano.

Trovare la posizione
SHOW MASTER STATUS indica file e posizione correnti; gli eventi di un binlog preciso si elencano così:

SHOW MASTER STATUS;

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

Verificare il ripristino
Ripristinare senza verificare è il modo abituale di scoprire il problema troppo tardi. Un conteggio per database e un CHECKSUM TABLE delle tabelle critiche contro l'origine bastano per dormire tranquilli:

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

CHECKSUM TABLE pedidos, lineas;

Raccomandazione
Pianifica il backup (Calíope lo fa, con ritenzione configurabile), tieni una copia fuori dalla macchina, attiva log_bin con una ritenzione che copra almeno due cicli di copia, e prova almeno una volta un ripristino completo. Il giorno dell'incidente non è il giorno per imparare la procedura.

Parole chiave: backup, ripristino, recupero, pitr, punto nel tempo, binlog, mysqldump, mysqlbinlog, rpo, rto, checksum table, snapshot

ALGORITHM, LOCK, metadata lock e quando serve uno strumento esterno.

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

Un ALTER TABLE su una tabella grande può durare ore e lasciare l'applicazione in attesa. Da MySQL 5.6 e MariaDB 10.0 si può indicare come deve avvenire la modifica, e sapere così in anticipo se farà male.

Chiedere l'algoritmo, non affidarsi al caso
Se dichiari l'algoritmo e il server non può usarlo, l'istruzione fallisce subito invece di bloccarti la tabella per tre ore. È il motivo principale per scriverlo sempre:

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

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

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

La clausola LOCK
- LOCK=NONE — letture e scritture proseguono durante la modifica. Se non è possibile, errore.
- LOCK=SHARED — si legge, non si scrive.
- LOCK=EXCLUSIVE — nessuno tocca la tabella.

Dichiarare LOCK=NONE è il modo di garantire che la migrazione non fermerà la produzione: o gira senza bloccare, o non gira.

Il metadata lock, quello che sorprende
Anche un ALTER istantaneo ha bisogno di un lock esclusivo sui metadati all'inizio e alla fine. Se c'è una vecchia transazione aperta su quella tabella, l'ALTER aspetta — e tutte le query che arrivano dopo si mettono in coda dietro di lui. Una tabella si congela per un ALTER che doveva durare un millisecondo. Prima di toccare lo schema, controlla che non ci siano transazioni lunghe:

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

SELECT @@lock_wait_timeout;

Vedere l'avanzamento
Un ALTER di ore non dà segni di vita da solo. performance_schema sì:

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

SHOW PROCESSLIST;

Quando serve uno strumento esterno
Se la modifica impone ALGORITHM=COPY su una tabella da decine di gigabyte, nessun LOCK ti salva. Lì entrano pt-online-schema-change (Percona) e gh-ost (GitHub): creano una tabella nuova, copiano a lotti, la tengono sincronizzata con trigger o leggendo il binlog e alla fine fanno lo scambio in un istante. Non arrivano col server; si installano a parte e si eseguono da riga di comando.

Raccomandazione
Scrivi sempre ALGORITHM= e LOCK= nelle tue migrazioni, e provale prima su una copia con dati reali per sapere quanto dureranno. Un ALTER che fallisce dopo un secondo è una buona notizia rispetto a uno che blocca la tabella a metà mattina.

Parole chiave: ddl online, alter table, algorithm, instant, inplace, copy, lock=none, metadata lock, mdl, pt-online-schema-change, gh-ost, migrazione di schema

Perché utf8 non è UTF-8, cosa decide una collation e come convertire senza rompere gli indici.

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

Due concetti che si confondono di continuo: il set di caratteri dice quali caratteri si possono memorizzare, e la collation dice come si confrontano e si ordinano. Il primo incide su cosa ci sta; la seconda, su cosa restituisce un WHERE.

utf8 non è UTF-8
In MySQL utf8 è un alias storico di utf8mb3: solo tre byte per carattere, quindi non può memorizzare emoji né buona parte del cinese, giapponese o coreano moderni. L'UTF-8 vero è utf8mb4. È la trappola più ripetuta del prodotto, e in MySQL 8.0 è ancora viva per compatibilità. Controlla dove sei:

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

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

SHOW VARIABLES LIKE 'character_set%';

Cosa decide una collation
Il nome dice tutto, se lo sai leggere. In utf8mb4_0900_ai_ci: 0900 è la versione di Unicode, ai significa insensibile agli accenti e ci insensibile alle maiuscole. I loro contrari sono as (sensibile agli accenti) e cs (sensibile alle maiuscole). Esiste anche utf8mb4_bin, che confronta byte per byte e non sa nulla di lingue.

I valori predefiniti differiscono: MySQL 8.0 usa utf8mb4_0900_ai_ci e MariaDB utf8mb4_general_ci oppure utf8mb4_uca1400_ai_ci a seconda della versione. Se sposti dati tra i due, non dare per scontato che ordinino allo stesso modo.

Cosa cambia in pratica
Con una collation ai_ci, café e cafe sono lo stesso valore: un UNIQUE rifiuterà il secondo e un WHERE li troverà entrambi. Può essere proprio ciò che vuoi per cercare nomi, e un disastro per conservare identificatori:

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

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

SHOW COLLATION WHERE charset = 'utf8mb4';

Mescolare collation fa male
Un JOIN tra una colonna utf8mb4_general_ci e una utf8mb4_0900_ai_ci dà l'errore 1267 Illegal mix of collations. E se lo rattoppi avvolgendo la colonna in CONVERT() o in un COLLATE, la query non può più usare l'indice di quella colonna. La correzione giusta non è il COLLATE nella query: è unificare la collation nello schema.

Convertire senza sorprese
ALTER DATABASE cambia il valore predefinito solo per le tabelle future; quelle esistenti vanno convertite una a una. E CONVERT TO CHARACTER SET riscrive l'intera tabella, quindi merita la stessa prudenza di qualsiasi DDL pesante:

ALTER DATABASE tienda
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;

ALTER TABLE clientes
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;

Raccomandazione
utf8mb4 ovunque — server, database, tabella, colonna e connessione del client — e una sola collation in tutto lo schema. Prima di convertire, guarda gli indici sulle colonne di testo lunghe: passando da utf8mb3 a utf8mb4 ogni carattere può occupare un byte in più, e un indice che ci stava può smettere di starci.

Parole chiave: charset, set di caratteri, collation, utf8, utf8mb4, latin1, emoji, accenti, maiuscole, convert to character set, illegal mix of collations

I codici più frequenti — 1045, 1062, 1213, 2006 — e cosa fare con ciascuno.

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

I codici sotto 2000 li emette il server; quelli dal 2000 in su, la libreria client. Già questa distinzione dice dove guardare: se il numero comincia per 2, il problema è nella connessione, non nell'SQL.

1045 e 1040: la connessione
Il 1045 non è quasi mai la password: è che l'account esiste per un altro host. Ricorda che 'app'@'localhost' e 'app'@'%' sono account distinti. Il 1040 significa che le connessioni sono finite, e la causa abituale non è la dimensione del pool ma connessioni che nessuno chiude:

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

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

1062: voce duplicata
Il messaggio nomina la chiave violata. Se il duplicato è previsto — un import rieseguito, un upsert — esiste una sintassi per smettere di trattarlo come errore:

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

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

1215: non si può creare la chiave esterna
Questo messaggio è celebre per non dire nulla. Le cause vere sono sempre le stesse quattro: i tipi delle due colonne non coincidono esattamente (segno e lunghezza inclusi), non coincidono i loro set di caratteri, manca un indice sulla colonna referenziata, oppure esistono già righe orfane che il vincolo non ammetterebbe:

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

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

Leggere bene l'errore
Prima di cercare il codice in rete, leggilo tutto: MySQL di solito indica tabella, colonna e valore esatti. E quando un'istruzione restituisce un avviso invece di un errore, SHOW WARNINGS subito dopo mostra cosa ha deciso il server per conto suo — un troncamento silenzioso, per esempio — che è peggio di un fallimento pulito.

Raccomandazione
Calíope mostra codice e messaggio del server così come sono, senza avvolgerli: quel testo è l'indizio migliore e conviene copiarlo per intero quando chiedi aiuto. Il Registro query conserva inoltre l'istruzione che l'ha provocato, con ora e durata.

Parole chiave: errore, codice di errore, 1045, 1049, 1062, 1146, 1213, 1205, 1215, 1267, 1406, 2002, 2006, 2013, 1040, too many connections, gone away, access denied, duplicate entry