Manuel du DBA

Plages, taille en octets et cas d'usage des types numériques, texte, date/heure, JSON et spatiaux. Inclut les différences pertinentes entre MySQL et MariaDB.

S'applique à : MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Le choix du type de données influe sur la taille sur disque, la vitesse des index et l'intégrité des données. Ce guide résume les types les plus utilisés en MySQL et MariaDB, avec leurs plages, leur taille en octets et leurs cas d'usage typiques.

Entiers numériques

- TINYINT — 1 octet, plage signée −128…127 (non signée 0…255). Utile pour des drapeaux booléens ou des petits états.
- SMALLINT — 2 octets, −32 768…32 767. Âge, petites quantités.
- MEDIUMINT — 3 octets, −8 388 608…8 388 607. Spécifique à MySQL/MariaDB ; rarement utilisé hors de cet écosystème.
- INT (INTEGER) — 4 octets, ±2,1·10⁹. Type par défaut pour les clés primaires dans les tables moyennes.
- BIGINT — 8 octets, ±9,2·10¹⁸. Clés primaires dans les grandes tables, identifiants distribués.

CREATE TABLE commandes (
    id        BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    quantite  SMALLINT UNSIGNED NOT NULL,
    statut    TINYINT UNSIGNED NOT NULL DEFAULT 0
);

Numériques décimaux et à virgule flottante

- DECIMAL(M, D) — précision exacte, M chiffres au total et D décimales. Obligatoire pour la monnaie.
- FLOAT — 4 octets, environ 7 chiffres significatifs. Approximatif.
- DOUBLE — 8 octets, environ 15 chiffres. Approximatif.

CREATE TABLE prix (
    sku    VARCHAR(32) PRIMARY KEY,
    montant DECIMAL(12, 2) NOT NULL,
    tva    DECIMAL(4, 2)  NOT NULL DEFAULT 13.00
);

Texte et chaînes

- CHAR(N) — longueur fixe de N caractères, jusqu'à 255. Rapide lorsque toutes les lignes ont la même taille (codes de pays, hachages).
- VARCHAR(N) — longueur variable, jusqu'à 65 535 octets par ligne (partagés avec les autres colonnes). Utilise 1 ou 2 octets supplémentaires pour la longueur.
- TEXT, MEDIUMTEXT, LONGTEXT — 64 Kio, 16 Mio, 4 Gio. Stockés hors de la ligne ; ne peuvent pas servir de clé sans préfixe (KEY (col(255))).
- BLOB, MEDIUMBLOB, LONGBLOB — équivalents binaires.

Date et heure

- DATE — 3 octets, '1000-01-01'…'9999-12-31'.
- TIME — 3 octets, '-838:59:59'…'838:59:59'. Oui, peut dépasser 24 heures (intervalle, pas heure du jour).
- DATETIME — 8 octets, sans fuseau horaire, sans conversion à l'enregistrement/lecture. Persiste la chaîne littérale.
- TIMESTAMP — 4 octets, plage 1970…2038 (en MySQL 5.7) ou 1970…2106 (en MariaDB 10.4+). Stocké en UTC et converti vers le time_zone de la connexion.
- YEAR — 1 octet, 1901…2155.

CREATE TABLE evenements (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    survenu_le   DATETIME(6) NOT NULL,
    cree_le      TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP
);

JSON

Type natif depuis MySQL 5.7+ et MariaDB 10.2+. Permet d'indexer des champs extraits avec JSON_EXTRACT ou ->> et, depuis MySQL 8.0, des colonnes générées avec index (MULTI-VALUED INDEX sur les tableaux).

CREATE TABLE profils (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    attributs   JSON NOT NULL,
    courriel    VARCHAR(120) AS (attributs->>'$.email') STORED,
    INDEX idx_courriel (courriel)
);

Spatiaux (GIS)

- POINT, LINESTRING, POLYGON, GEOMETRY, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION.
- Nécessitent un index SPATIAL pour des requêtes efficaces (MBRContains, ST_Distance, ST_Within).
- En MySQL 8.0, le SRID est obligatoire pour utiliser les index spatiaux.

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

ENUM et SET

- ENUM — stocke une parmi N valeurs prédéfinies (jusqu'à 65 535). Compact (1–2 octets), mais rigide : modifier la liste exige ALTER TABLE.
- SET — combinaison de jusqu'à 64 valeurs sous forme de bitmap. Utile pour des permissions ou étiquettes fixes.

Évitez-les si la liste de valeurs change fréquemment ; une table de catalogue + clé étrangère est plus maintenable.

Comment choisir

1. Utilisez le type le plus petit qui couvre la plage attendue. Un BIGINT là où INT suffit quadruple l'espace de l'index.
2. Marquez les colonnes en UNSIGNED lorsque vous n'avez pas besoin de négatifs : vous doublez la plage.
3. Évitez NULL quand vous pouvez : une colonne NOT NULL DEFAULT … économise 1 bit par ligne et par index.
4. VARCHAR(255) n'est pas plus coûteux que VARCHAR(20) si les vraies données tiennent en 20 — seule la longueur déclarée importe pour les index avec préfixe.

Mots-clés : types de données, INT, BIGINT, VARCHAR, TEXT, JSON, DATETIME, TIMESTAMP, DECIMAL, ENUM, SET, POINT, SPATIAL, BLOB, plage, octets

Types d'index (B-tree, hash, fulltext, spatial), index simples vs composés, et stratégies pour accélérer les lectures sans pénaliser les écritures.

S'applique à : MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Un index accélère la recherche au prix d'espace sur disque et d'un coût additionnel sur chaque INSERT/UPDATE/DELETE. Un bon plan d'index fait la différence entre une requête en millisecondes et une de plusieurs minutes.

Types d'index

- B-tree — Par défaut dans InnoDB. Supporte les recherches par égalité, plage (>, <, BETWEEN), préfixe (LIKE 'abc%') et tri (ORDER BY).
- Hash — Égalité uniquement. Disponible dans le moteur MEMORY. InnoDB maintient un adaptive hash index interne que vous ne contrôlez pas directement.
- FULLTEXT — Recherche textuelle naturelle et booléenne. Disponible dans InnoDB et MyISAM. Utile pour des colonnes TEXT longues.
- SPATIAL — R-tree pour POINT, POLYGON, etc. Exige une colonne NOT NULL.
- Multi-valued — Indexe les éléments d'un tableau JSON. Disponible uniquement à partir de MySQL 8.0.17+.

-- Index B-tree composé
CREATE INDEX idx_commandes_client_date
    ON commandes (client_id, date_commande DESC);

-- Index de texte intégral
ALTER TABLE articles
    ADD FULLTEXT INDEX ft_titre_corps (titre, corps);

-- Index spatial
ALTER TABLE emplacements
    ADD SPATIAL INDEX sp_point (point);

Simples vs composés

Un index composé sur (A, B, C) couvre les recherches par préfixe : WHERE A = ?, WHERE A = ? AND B = ?, WHERE A = ? AND B = ? AND C = ?, mais pas WHERE B = ? seul.

Règle pratique : ordonnez les colonnes du composé par sélectivité (combien de valeurs uniques chacune a) et par fréquence des filtres.

-- Bon : colonne la plus sélective en premier
CREATE INDEX idx_factures
    ON factures (client_id, statut, date)
    -- client_id (haute sélectivité) → statut → date
;

-- Anti-pattern : index redondant
-- (client_id) est déjà couvert par (client_id, statut, date)
DROP INDEX idx_factures_client ON factures;

Index couvrants

Un index qui contient toutes les colonnes lues par une requête évite l'accès à la table. Utilisez EXPLAIN et cherchez Using index dans la colonne Extra.

-- La requête ne lit que client_id et total → l'index la couvre
CREATE INDEX idx_commandes_couvrant
    ON commandes (client_id, total);

SELECT client_id, SUM(total)
FROM commandes
WHERE client_id IN (1, 2, 3)
GROUP BY client_id;

Index avec préfixe

Pour des colonnes TEXT ou VARCHAR longues, indexez seulement les N premiers caractères. Réduit la taille de l'index en conservant une sélectivité raisonnable.

CREATE INDEX idx_url
    ON pages (url(64));   -- 64 premiers caractères

Index invisibles

ALTER TABLE commandes ALTER INDEX idx_legacy INVISIBLE;
-- surveiller la performance pendant quelques heures
ALTER TABLE commandes ALTER INDEX idx_legacy VISIBLE; -- revenir en arrière
-- ou
DROP INDEX idx_legacy ON commandes; -- confirmer la suppression

Impact lecture vs écriture

Chaque index supplémentaire :
- Accélère les requêtes qui l'utilisent.
- Pénalise chaque INSERT, UPDATE qui touche des colonnes indexées et chaque DELETE.
- Occupe de l'espace additionnel (souvent entre 10 % et 40 % de la taille de la table).

Sur des tables à écriture intensive (logs, métriques), gardez le minimum d'index indispensables.

Stratégies d'optimisation

1. Commencez par EXPLAIN — repérez type: ALL (full scan) et key: NULL (sans index).
2. Mesurez avant d'optimiser — utilisez le slow query log pour trouver les requêtes les plus coûteuses.
3. Combinez sélectivité et ordre — l'index composé doit suivre l'ordre des clauses WHERE et ORDER BY.
4. Évitez les index redondants — (A), (A, B), (A, B, C) sont redondants ; (A, B, C) seul suffit.
5. N'indexez pas les colonnes à faible cardinalité — un index sur genre ou actif (1 ou 2 valeurs uniques) n'aide presque jamais.

-- Diagnostic
EXPLAIN SELECT * FROM commandes
WHERE client_id = 42 AND date >= '2024-01-01';

-- Statistiques d'usage des index
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;

Mots-clés : index, B-tree, hash, fulltext, spatial, couvrant, composé, sélectivité, EXPLAIN, préfixe, invisible, cardinalité

Première, deuxième et troisième forme normale, dénormalisation, quand l'appliquer et comment équilibrer intégrité des données et performance.

S'applique à : MySQL 5.7+ MariaDB 10.5+ Aurora 2+ PostgreSQL 13+ SQLite 3.35+

La normalisation est le processus d'organisation d'un schéma pour réduire la redondance et prévenir les anomalies d'insertion, de mise à jour et de suppression. Les formes normales sont cumulatives : une table en 3FN satisfait aussi 2FN et 1FN.

Première forme normale (1FN)

- Chaque colonne contient une seule valeur atomique (pas de listes ni de JSON imbriqué représentant plusieurs entités).
- Chaque ligne est identifiable par une clé primaire.
- Pas de groupes répétitifs en colonnes (tel1, tel2, tel3).

-- Mauvais : trois colonnes répétant la même "entité"
CREATE TABLE clients_v1 (
    id      BIGINT PRIMARY KEY,
    nom     VARCHAR(120),
    tel1    VARCHAR(20),
    tel2    VARCHAR(20),
    tel3    VARCHAR(20)
);

-- Bon : une table reliée
CREATE TABLE clients (
    id  BIGINT PRIMARY KEY,
    nom VARCHAR(120)
);
CREATE TABLE clients_telephones (
    client_id  BIGINT NOT NULL,
    telephone    VARCHAR(20) NOT NULL,
    type        VARCHAR(10) NOT NULL,
    PRIMARY KEY (client_id, telephone),
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
);

-- SQLite : à activer à chaque connexion, avant la première transaction
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
);

Deuxième forme normale (2FN)

- Satisfait 1FN.
- Toute colonne non clé dépend de la clé primaire entière, pas d'une partie. S'applique aux clés composées.

Exemple : une table ligne_commande (commande_id, produit_id, quantite, nom_produit) viole 2FN, car nom_produit dépend uniquement de produit_id, pas du couple complet.

-- Mauvais : nom_produit se répète sur chaque ligne du même produit
CREATE TABLE ligne_commande_v1 (
    commande_id   BIGINT,
    produit_id    BIGINT,
    quantite      INT,
    nom_produit   VARCHAR(120),
    PRIMARY KEY (commande_id, produit_id)
);

-- Bon : nom_produit vit dans la table produits
CREATE TABLE ligne_commande (
    commande_id  BIGINT,
    produit_id   BIGINT,
    quantite     INT NOT NULL,
    PRIMARY KEY (commande_id, produit_id),
    FOREIGN KEY (produit_id) REFERENCES produits(id)
);

Troisième forme normale (3FN)

- Satisfait 2FN.
- Aucune colonne non clé ne dépend d'une autre colonne non clé (pas de dépendances transitives).

Exemple classique : employes (id, nom, departement_id, departement_nom). Le nom du département dépend de departement_id, pas directement de la clé de l'employé.

-- Mauvais : dépendance transitive
CREATE TABLE employes_v1 (
    id                  BIGINT PRIMARY KEY,
    nom                 VARCHAR(120),
    departement_id      BIGINT,
    departement_nom     VARCHAR(80)
);

-- Bon
CREATE TABLE departements (
    id      BIGINT PRIMARY KEY,
    nom     VARCHAR(80)  NOT NULL
);
CREATE TABLE employes (
    id              BIGINT PRIMARY KEY,
    nom             VARCHAR(120),
    departement_id  BIGINT NOT NULL,
    FOREIGN KEY (departement_id) REFERENCES departements(id)
);

BCNF et formes supérieures

La forme normale de Boyce-Codd (BCNF) renforce la 3FN, et les 4FN/5FN traitent les dépendances multivaluées et de jointure. Pour la plupart des schémas transactionnels, atteindre proprement la 3FN suffit.

Quand dénormaliser

La dénormalisation délibérée rompt les règles pour gagner en performance. Elle est valable quand :

1. Lectures massives, écritures rares — un champ caché dans la table (commandes.total_paye) évite une SUM(...) récurrente.
2. Reporting / analytique — les schémas en étoile ou en flocon dénormalisent à dessein.
3. Résultats pré-calculés — vues matérialisées ou tables résumé.

Les compromis acceptés :

- Anomalies de mise à jour — si la donnée dénormalisée change, il faut la mettre à jour dans N lignes.
- Incohérence transitoire — le champ caché peut diverger si la mise à jour échoue à mi-parcours.
- Triggers ou logique applicative — il faut maintenir la donnée synchronisée.

-- Exemple : cacher le total de la commande pour éviter SUM à chaque lecture
ALTER TABLE commandes
    ADD COLUMN total DECIMAL(12, 2) NOT NULL DEFAULT 0;

-- Le maintenir avec un trigger
DELIMITER //
CREATE TRIGGER ligne_commande_after_insert
AFTER INSERT ON ligne_commande
FOR EACH ROW
BEGIN
    UPDATE commandes
       SET total = (SELECT COALESCE(SUM(quantite * prix_unitaire), 0)
                    FROM ligne_commande
                    WHERE commande_id = NEW.commande_id)
     WHERE id = NEW.commande_id;
END//
DELIMITER ;

CREATE FUNCTION recalculer_total() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE commandes
       SET total = (SELECT COALESCE(SUM(quantite * prix_unitaire), 0)
                    FROM ligne_commande
                    WHERE commande_id = NEW.commande_id)
     WHERE id = NEW.commande_id;
    RETURN NULL;
END;
$$;

CREATE TRIGGER ligne_commande_after_insert
AFTER INSERT ON ligne_commande
FOR EACH ROW EXECUTE FUNCTION recalculer_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;

Recommandation

1. Concevez en 3FN par défaut. L'intégrité vous remerciera.
2. Ne dénormalisez qu'avec des données — mesurez la requête lente, testez un cache et comparez.
3. Documentez la dénormalisation. Sans un commentaire dans le DDL, le prochain DBA la "renormalisera" en croyant à une erreur.

Mots-clés : normalisation, 1FN, 2FN, 3FN, BCNF, dénormalisation, redondance, dépendance fonctionnelle, clés, intégrité, trigger

INNER, LEFT, RIGHT, CROSS, SELF et FULL OUTER JOIN : quand utiliser chacune, avec des exemples sur un schéma typique de commandes.

S'applique à : MySQL 5.7+ MariaDB 10.5+ Aurora 2+ PostgreSQL 13+ SQLite 3.35+

Une JOIN combine des lignes de deux tables ou plus selon une condition. Le type de jointure détermine ce qui arrive aux lignes qui ne trouvent pas de correspondance.

Pour les exemples, nous supposons :

CREATE TABLE clients (
    id   BIGINT PRIMARY KEY,
    nom  VARCHAR(120) NOT NULL
);
CREATE TABLE commandes (
    id          BIGINT PRIMARY KEY,
    client_id   BIGINT NOT NULL,
    total       DECIMAL(12, 2) NOT NULL,
    FOREIGN KEY (client_id) REFERENCES clients(id)
);

INNER JOIN

Renvoie uniquement les lignes ayant une correspondance dans les deux tables. C'est la jointure par défaut et la plus utilisée.

SELECT c.nom, cmd.total
FROM clients c
INNER JOIN commandes cmd ON cmd.client_id = c.id;

LEFT JOIN (LEFT OUTER JOIN)

Toutes les lignes de la table de gauche + leurs correspondances de droite. Les champs sans correspondance à droite apparaissent en NULL. Utile pour « tous les X, avec leur Y s'il existe ».

-- Tous les clients, qu'ils aient passé commande ou non
SELECT c.nom, COUNT(cmd.id) AS commandes
FROM clients c
LEFT JOIN commandes cmd ON cmd.client_id = c.id
GROUP BY c.id, c.nom;

Clients sans commandes — patron classique avec WHERE … IS NULL :

SELECT c.id, c.nom
FROM clients c
LEFT JOIN commandes cmd ON cmd.client_id = c.id
WHERE cmd.id IS NULL;

RIGHT JOIN

L'inverse de LEFT. Presque toujours écrit comme LEFT JOIN en inversant l'ordre, plus lisible.

-- Équivalent à LEFT JOIN avec ordre inversé
SELECT c.nom, cmd.id
FROM commandes cmd
RIGHT JOIN clients c ON cmd.client_id = c.id;

CROSS JOIN

Produit cartésien : chaque ligne de A avec chaque ligne de B. Sans clause ON. Utile pour générer toutes les combinaisons (calendrier × produits pour rapports).

-- Génère toutes les combinaisons (client, mois) pour un rapport
SELECT c.id, m.mois
FROM clients c
CROSS JOIN (
    SELECT 1 AS mois UNION ALL SELECT 2 UNION ALL SELECT 3
    -- ...jusqu'à 12
) m;

SELF JOIN

La même table apparaît deux fois avec des alias différents. Utile pour les hiérarchies ou comparer des lignes d'une même table.

CREATE TABLE employes (
    id            BIGINT PRIMARY KEY,
    nom           VARCHAR(120),
    superieur_id  BIGINT NULL,
    FOREIGN KEY (superieur_id) REFERENCES employes(id)
);

-- Chaque employé avec le nom de son supérieur
SELECT e.nom AS employe, s.nom AS superieur
FROM employes e
LEFT JOIN employes s ON s.id = e.superieur_id;

FULL OUTER JOIN

Toutes les lignes des deux tables ; celles sans correspondance montrent NULL du côté manquant.

-- MariaDB natif
SELECT c.nom, cmd.id
FROM clients c
FULL OUTER JOIN commandes cmd ON cmd.client_id = c.id;

-- Émulation en MySQL
SELECT c.nom, cmd.id
FROM clients c
LEFT JOIN commandes cmd ON cmd.client_id = c.id

UNION

SELECT c.nom, cmd.id
FROM clients c
RIGHT JOIN commandes cmd ON cmd.client_id = c.id
WHERE c.id IS NULL;

-- PostgreSQL natif
SELECT c.nom, cmd.id
FROM clients c
FULL OUTER JOIN commandes cmd ON cmd.client_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.nom, cmd.id
FROM clients c, commandes cmd
WHERE cmd.client_id = c.id;

-- SQLite : l'ordre écrit fait loi
SELECT c.nombre, p.id
FROM pedidos p
CROSS JOIN clientes c ON p.cliente_id = c.id;

Anti-join et semi-join

Patrons logiques, pas des mots-clés SQL :

- Semi-join (au moins une correspondance existe) → EXISTS ou IN.
- Anti-join (aucune correspondance n'existe) → NOT EXISTS ou LEFT JOIN ... WHERE ... IS NULL.

-- Semi-join : clients avec au moins une commande
SELECT c.id, c.nom
FROM clients c
WHERE EXISTS (SELECT 1 FROM commandes cmd WHERE cmd.client_id = c.id);

-- Anti-join : clients sans commandes
SELECT c.id, c.nom
FROM clients c
WHERE NOT EXISTS (SELECT 1 FROM commandes cmd WHERE cmd.client_id = c.id);

NOT IN n'est pas un anti-join. Si la sous-requête renvoie ne serait-ce qu'un NULL, la comparaison n'est jamais vraie et le résultat est zéro ligne. Mesuré avec 5 clients et 6 commandes, dont 3 avec client_id à NULL : NOT EXISTS et LEFT JOIN … IS NULL renvoient 2, et NOT IN renvoie 0. C'est pareil sur PostgreSQL 17.6, sur MySQL 8.0.46 et sur SQLite 3.51.

Performance

1. Les colonnes du ON doivent être indexées, en particulier du côté « interne » de la jointure (celui cherché à chaque ligne du côté externe).
2. Filtrez autant que possible avant la jointure (WHERE sur chaque table quand c'est applicable).
3. Évitez les JOIN sur expressions (ON LOWER(a.code) = LOWER(b.code)) — l'index n'est pas utilisé.
4. EXPLAIN révèle l'ordre de lecture et la méthode, avec le vocabulaire de chaque moteur.

-- C'est ainsi qu'on demande le plan à SQLite, pas avec un EXPLAIN tout court
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);

Mots-clés : jointures, INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, SELF JOIN, EXISTS, semi-join, anti-join, STRAIGHT_JOIN, nested loop

Variables critiques de performance : buffer pool, connexions, paquet maximum, cache de requêtes et différences clés entre MySQL et MariaDB.

S'applique à : MySQL 5.7+ MariaDB 10.5+ Aurora 2+

La configuration par défaut du serveur n'est presque jamais optimale en production. Voici les variables qui ont le plus grand impact sur la performance et la stabilité.

innodb_buffer_pool_size

Le cache principal d'InnoDB : tables, index et données. C'est la variable la plus importante.

- Règle pratique : 60 %–80 % de la RAM sur un serveur dédié à MySQL.
- Minimum recommandé : 1 Gio en production.
- En MySQL 8.0+ et MariaDB 10.5+, modifiable à chaud (sans redémarrer).

-- Voir la taille actuelle
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- Modifier à chaud (8 Go)
SET GLOBAL innodb_buffer_pool_size = 8 * 1024 * 1024 * 1024;

max_connections

Nombre maximum de connexions simultanées. Par défaut 151 en MySQL, 100 en MariaDB.

- Chaque connexion consomme de la mémoire (thread_stack + buffers par session, ~256 Kio).
- Une valeur trop élevée dégrade la performance sous charge (contention).
- Mesurez avec SHOW STATUS LIKE 'Max_used_connections'. Si elle atteint le plafond, augmentez graduellement.

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

max_allowed_packet

Taille maximale d'un paquet de protocole (un gros INSERT, un LOAD DATA, un BLOB).

- Par défaut 64 Mio en MySQL 8.0, 16 Mio dans les versions antérieures.
- Si une opération l'excède : erreur Got a packet bigger than 'max_allowed_packet' bytes.
- Augmenter à 256 Mio ou 1 Gio est habituel sur des charges avec BLOB.

SET GLOBAL max_allowed_packet = 256 * 1024 * 1024;
-- Le client doit aussi passer le paramètre
-- (ligne de commande : --max-allowed-packet=256M)

Query cache

Cache le résultat complet des requêtes SELECT.

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

Logs et durabilité

innodb_flush_log_at_trx_commit contrôle quand le redo log est vidé :

- 1 (par défaut) — vide et fsync à chaque commit. Durabilité maximale, vitesse minimale. ACID strict.
- 2 — vide à chaque commit, fsync chaque seconde. Un crash de l'OS peut perdre ~1s. Quasi ACID.
- 0 — vide et fsync chaque seconde. Un crash de MySQL peut perdre ~1s. Non ACID.

Sur des répliques ou des environnements tolérant une perte bornée, 2 peut multiplier le débit par 3 à 5. Ne le changez pas sur le primaire sans comprendre le risque.

thread_pool

tmp_table_size / max_heap_table_size

Taille maximale des tables temporaires en mémoire. Si une opération dépasse la limite, MySQL la bascule sur disque et perd en vitesse. Gardez les deux valeurs égales.

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;

-- Combien de tables temporaires sont passées sur disque
SHOW STATUS LIKE 'Created_tmp_disk_tables';

innodb_io_capacity / innodb_io_capacity_max

IOPS qu'InnoDB peut consommer pour le nettoyage des pages sales et les purges. Par défaut 200 / 2000.

- SSD modernes : 2000 / 4000 ou plus.
- HDD : laissez les valeurs par défaut.

Configuration persistante

SET PERSIST innodb_buffer_pool_size = 8589934592;
SET PERSIST_ONLY max_connections = 500; -- ne s'applique qu'au redémarrage
RESET PERSIST innodb_buffer_pool_size;   -- retirer la persistance

En MariaDB, la persistance se fait en éditant my.cnf (/etc/my.cnf.d/) puis en redémarrant, ou via des includes (!include).

Recommandation générale

1. Connaissez la charge avant de toucher quoi que ce soit. Une OLTP avec écritures intenses se configure différemment d'un entrepôt de données en lecture.
2. Modifiez une variable à la fois et mesurez l'impact.
3. Documentez chaque changement dans my.cnf avec un commentaire expliquant la raison.
4. Ne copiez pas des configurations depuis des blogs sans les comprendre — les valeurs « optimales » dépendent beaucoup du matériel et de la charge.

Mots-clés : configuration, 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

Tailles maximales de base de données, tables, colonnes, longueurs de noms d'objets et caractères autorisés dans les identifiants.

S'applique à : MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Connaître les limites du moteur évite les surprises en croissance. Voici les plafonds pratiques en MySQL et MariaDB modernes.

Par base de données

- Taille totale : limitée par le système de fichiers. Avec innodb_file_per_table = ON (par défaut), chaque table est un fichier .ibd. Sur ext4 / XFS, on parle d'exaoctets théoriques — la vraie limite vient de votre stockage.
- Tables par base : pratiquement illimité. Le catalogue (information_schema, mysql.tables) gère plusieurs centaines de milliers sans souci. Les charges avec 10 000+ tables exigent d'ajuster table_open_cache.

Par table

- Lignes : 2⁶⁴ lignes théoriques. En pratique : des centaines de milliards si le schéma et les index sont bons.
- Taille maximale de table : 64 Tio avec la INNODB_PAGE_SIZE par défaut (16 Kio).
- Colonnes : maximum 4 096 par table, mais la vraie limite vient de la taille de ligne, pas du nombre.
- Taille maximale de ligne : 65 535 octets (en excluant BLOB/TEXT qui sont stockés hors de la ligne).
- Index par table : 64.
- Colonnes par index : 16 (B-tree InnoDB).
- Longueur maximale d'une clé d'index : 3072 octets avec DYNAMIC/COMPRESSED (format par défaut en MySQL 5.7+ / MariaDB 10.2+).

-- Inspecter la taille des tables
SELECT table_schema, table_name,
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS mio
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC
LIMIT 20;

Par colonne

Identifiants (noms d'objets)

- Bases, tables, colonnes, index, vues : 64 caractères.
- Alias de colonnes : 256 caractères.
- Fonctions, procédures, triggers, événements : 64 caractères.
- Contraintes (FK, CHECK, UNIQUE) : 64 caractères.

Caractères autorisés dans les identifiants

- Sans backticks : lettres ASCII, chiffres, _ et $. Ne peuvent pas commencer par un chiffre pur ni être uniquement des chiffres.
- Avec backticks (`nom bizarre`) : tout caractère Unicode sauf U+0000 (NUL).

Convention recommandée : snake_case ASCII (commande_client_id). Évitez espaces, accents et majuscules — certains systèmes les normalisent différemment entre Linux et macOS.

-- Valide mais à éviter
CREATE TABLE `commandes de l'année 2024` (`Numéro de commande` INT);

-- Recommandé
CREATE TABLE commandes_2024 (numero_commande INT);

Sensibilité à la casse

lower_case_table_names :

- 0 — les noms sont stockés tels que créés et sont sensibles à la casse. Par défaut sous Linux.
- 1 — les noms sont stockés en minuscules et les comparaisons ignorent la casse. Par défaut sous macOS et Windows.
- 2 — stockés tels quels mais comparaisons insensibles à la casse. macOS/Windows uniquement.

Changer cette valeur sur une installation existante est destructif. Décidez à l'initialisation du serveur.

Par requête

- Sous-requêtes imbriquées : jusqu'à 64 niveaux.
- UNION : théoriquement illimité, mais l'optimiseur dégrade au-delà de plusieurs centaines.
- Paramètres dans une prepared statement : 65 535.
- Lignes dans un IN(...) : pratique jusqu'à quelques milliers ; au-delà, préférez un JOIN avec table temporaire.

Par session

- Variables de session (@@SESSION.xxx) : peuvent fixer presque n'importe quel runtime global.
- Variables utilisateur (@variable) : jusqu'à 64 caractères dans le nom.

Charset et collation

- Charset recommandé : utf8mb4 (UTF-8 complet, 4 octets). L'alias utf8 est historique et limité à 3 octets (sans emoji).
- Collation recommandée en MySQL 8.0+ : utf8mb4_0900_ai_ci (case-insensitive, accent-insensitive, basé sur Unicode 9).
- En MariaDB : utf8mb4_unicode_ci ou uca1400_ai_ci (10.10+).

ALTER DATABASE ma_base
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

ALTER TABLE clients
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

Recommandations

1. Concevez avec de la marge : si vous attendez 10 millions de lignes, dimensionnez index et partitions pour 100 M.
2. Utilisez BIGINT UNSIGNED comme clé primaire des tables susceptibles de croître. INT se remplit à ~2 milliards.
3. Définissez charset et collation explicites au niveau base, table et colonne. Hériter du défaut peut casser une migration.
4. Documentez les limites de votre modèle (lignes attendues/an, taille max par colonne). Sert au capacity planning et à détecter des requêtes anormales.

Mots-clés : limites, maximum, taille, colonnes, lignes, identifiants, charset, collation, utf8mb4, lower_case_table_names, index, taille de ligne

Conception des schémas, conventions de nommage, sauvegardes, réplication, sécurité des utilisateurs, GRANT minimum et audit.

S'applique à : MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Recommandations opérationnelles qui séparent une base de données amateur d'une base maintenable en production.

Conception des schémas

1. Toute table a une clé primaire. Si elle n'est pas naturelle, ajoutez id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY.
2. Types explicites : déclarez NOT NULL et DEFAULT chaque fois que la colonne le permet. NULL doit signifier « non applicable », pas « non rempli ».
3. Clés étrangères obligatoires entre tables liées. Vous perdez des microsecondes en écriture, vous gagnez une intégrité référentielle inviolable.
4. InnoDB toujours. MyISAM ne supporte ni les FK ni les transactions ; il ne subsiste que dans des systèmes legacy.

CREATE TABLE commandes (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id      BIGINT UNSIGNED NOT NULL,
    statut         TINYINT UNSIGNED NOT NULL DEFAULT 0,
    cree_le        DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    actualise_le   DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
                                ON UPDATE CURRENT_TIMESTAMP(6),
    FOREIGN KEY (client_id) REFERENCES clients(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    INDEX idx_commandes_client (client_id),
    INDEX idx_commandes_cree (cree_le)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

Conventions de nommage

- Tables : snake_case, pluriel si elles représentent des collections (commandes, clients).
- Colonnes : snake_case, sans préfixe redondant (nom, pas client_nom à l'intérieur de clients).
- Clés étrangères : <table>_id (client_id).
- Index : idx_<table>_<colonnes> ou uq_<table>_<colonnes> pour les uniques.
- Clés étrangères explicites : fk_<table>_<table_cible>.
- Procédures / fonctions : préfixe sp_ / fn_ optionnel, verbe à l'infinitif (fn_calculer_remise).

Cohérence > préférence personnelle. Convenez de la convention en équipe et appliquez-la partout.

Sauvegardes

1. Stratégie 3-2-1 : 3 copies, 2 supports différents, 1 hors site.
2. Types :
- mysqldump — logique, portable, restauration lente (~5–10 Mo/s).
- mariabackup / xtrabackup — physique, bien plus rapide, exige une brève pause des I/O.
- Snapshot du système de fichiers (LVM, ZFS) — instantané mais lié au système de fichiers.
3. Tester la restauration — une sauvegarde sans restauration vérifiée n'est pas une sauvegarde.
4. Rétention : quotidiennes 7 jours + hebdomadaires 4 + mensuelles 12 est un bon point de départ.

Calíope inclut un module de sauvegarde : Aide › Sauvegarde.

-- Dump logique avec cohérence transactionnelle
-- (depuis le shell, pas du SQL) :
-- mysqldump --single-transaction --routines --triggers --events \\
--           -u root -p ma_base > ma_base.sql

-- Sauvegarde physique (mariabackup) :
-- mariabackup --backup --target-dir=/srv/backup/full \\
--             --user=root --password=...

Réplication

- Réplication asynchrone (par défaut) — le primaire n'attend pas la réplique. Risque : perdre les dernières transactions si le primaire meurt.
- Réplication semi-synchrone — le primaire attend la confirmation d'au moins une réplique avant d'accuser réception au client.
- Groupe de réplication (MySQL InnoDB Cluster, MariaDB Galera) — multi-primaire avec consensus.

Bonnes pratiques :

1. GTID activé (gtid_mode = ON) — nécessaire pour un failover automatique et les outils modernes.
2. binlog_format = ROW — plus robuste que STATEMENT face aux fonctions non déterministes.
3. Réplication dédiée — un utilisateur replica avec seulement REPLICATION SLAVE, IP restreinte.
4. Lag surveillé — SHOW REPLICA STATUS (SHOW SLAVE STATUS sur anciennes versions), alerte si Seconds_Behind_Source > 30.

Utilisateurs et permissions

Principe du moindre privilège : chaque connexion utilise l'utilisateur le plus restreint possible.

-- Créer un utilisateur d'application avec des permissions limitées
CREATE USER 'app_commandes'@'10.0.%.%' IDENTIFIED BY 'mot_de_passe_fort';

GRANT SELECT, INSERT, UPDATE, DELETE
   ON ma_base.commandes  TO 'app_commandes'@'10.0.%.%';
GRANT SELECT
   ON ma_base.clients    TO 'app_commandes'@'10.0.%.%';

-- JAMAIS en production
-- GRANT ALL PRIVILEGES ON *.* TO 'app'@'%';
FLUSH PRIVILEGES;

Règles :

1. Un utilisateur par application / par fonction. Facilite l'audit.
2. Pas de privilèges *.* pour les utilisateurs d'application. Accordez par base ou par table.
3. Pas d'accès applicatif à l'utilisateur root. Réservé aux tâches administratives.
4. Faites tourner les mots de passe et utilisez une authentification robuste (caching_sha2_password en MySQL 8, ed25519 en MariaDB).
5. Restreignez l'hôte ('app'@'10.0.%.%'), n'utilisez pas '%'.

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 conserve un journal local des requêtes exécutées (Registre SQL) par session de connexion, indépendant du journal du serveur.

Liste minimale pour la production

1. ✅ Sauvegardes automatiques + restauration vérifiée chaque mois.
2. ✅ Réplication avec lag surveillé.
3. ✅ Utilisateurs d'application sans privilèges excessifs.
4. ✅ TLS obligatoire pour les connexions externes.
5. ✅ Slow query log activé (long_query_time = 1).
6. ✅ Surveillance de l'espace disque (alerte à 80 %).
7. ✅ Mises à jour de sécurité appliquées chaque trimestre.

Mots-clés : bonnes pratiques, conception, conventions, sauvegarde, réplication, GTID, binlog, GRANT, audit, sécurité, InnoDB, clé étrangère, TLS

Analyse de requêtes avec EXPLAIN, slow query log, identification de goulots d'étranglement, cache InnoDB et utilisation de performance_schema.

S'applique à : MySQL 5.7+ MariaDB 10.5+ Aurora 2+

L'optimisation commence par mesurer. Sans données, optimiser revient à deviner. Voici les instruments de base.

EXPLAIN

Affiche le plan choisi par l'optimiseur pour une requête. Il ne l'exécute pas — sûr à lancer en production.

EXPLAIN SELECT c.nom, COUNT(cmd.id) AS commandes
FROM clients c
LEFT JOIN commandes cmd ON cmd.client_id = c.id
WHERE c.actif = 1
GROUP BY c.id;

Colonnes clés :

- type — méthode d'accès. Du meilleur au pire : system → const → eq_ref → ref → range → index → ALL. ALL = full table scan = mauvais sur de grandes tables.
- key — index choisi. NULL = aucun index utilisé.
- rows — estimation des lignes examinées. Si bien supérieur aux lignes renvoyées, il y a marge d'amélioration.
- Extra — indices utiles :
- Using index — index couvrant (excellent).
- Using where — filtre appliqué après lecture des lignes.
- Using temporary — nécessite une table temporaire (coûteux).
- Using filesort — tri hors index (coûteux sur grandes tables).

EXPLAIN ANALYZE (MySQL 8.0+ / MariaDB 10.1+)

Exécute la requête et affiche les temps réels par nœud. Plus coûteux qu'EXPLAIN, mais bien plus informatif.

EXPLAIN ANALYZE
SELECT c.nom, COUNT(cmd.id) AS commandes
FROM clients c
LEFT JOIN commandes cmd ON cmd.client_id = c.id
GROUP BY c.id;

Calíope intègre un Visual Explain qui restitue l'arbre du plan : Workspace › Analyse › Visual Explain.

Slow query log

Enregistre toute requête prenant plus de long_query_time secondes.

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

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

Analyse du log :

- mysqldumpslow — outil classique fourni avec MySQL.
- pt-query-digest (Percona Toolkit) — standard de facto, regroupe par empreinte et affiche des statistiques.

performance_schema

Schéma avec statistiques détaillées du serveur.

-- Top 10 des requêtes par temps total accumulé
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 lignes_exam
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 10;

-- Tables avec le plus d'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 d'InnoDB

- Buffer pool — données et index. Métrique clé : hit ratio (Innodb_buffer_pool_read_requests / (reads + reads_from_disk)). Objectif : >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;

-- (Version qui s'analyse correctement, avec deux requêtes) :
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

- Adaptive hash index — hash automatique sur les pages chaudes du buffer pool. Activé par défaut.
- Change buffer — met en tampon les modifications de pages non présentes dans le buffer pool.

Goulots d'étranglement fréquents

Optimisation de requêtes — schémas courants

1. Ne sélectionnez que ce dont vous avez besoin. Évitez SELECT * dans les applications.
2. Évitez les fonctions sur colonnes indexées :
- Mauvais : WHERE YEAR(date) = 2024 → n'utilise pas l'index.
- Bon : WHERE date >= '2024-01-01' AND date < '2025-01-01'.
3. LIMIT avec un grand offset est coûteux — pour la pagination profonde, utilisez la keyset pagination : WHERE id > :dernier_vu ORDER BY id LIMIT 50.
4. COUNT(*) sur grandes tables — InnoDB ne maintient pas de compteur. Pensez à des colonnes résumé ou des estimations (information_schema.tables.table_rows).
5. Les sous-requêtes non corrélées s'exécutent une fois ; les corrélées, une fois par ligne externe. Réécrivez-les en JOIN si possible.

Recommandation

Créez un tableau de bord de monitoring basique (Calíope en a un : Tableau) avec :

- Connexions (Threads_connected, Threads_running).
- Hit ratio du buffer pool.
- Requêtes lentes par minute.
- Lag de réplication.
- Espace disque par tablespace.

Plus tôt vous détectez une dégradation, plus facile à corriger.

Mots-clés : performance, optimisation, EXPLAIN, EXPLAIN ANALYZE, slow query log, performance_schema, buffer pool, filesort, index couvrant, filtre, goulot d'étranglement, pt-query-digest

ACID, COMMIT et ROLLBACK, les quatre niveaux d'isolation et l'anomalie que chacun autorise.

S'applique à : MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Une transaction regroupe plusieurs instructions en une unité qui s'applique entièrement ou pas du tout. Dans InnoDB, chaque instruction s'exécute dans une transaction : si vous n'en ouvrez aucune, le serveur en ouvre et en valide une par instruction (autocommit = 1).

ACID
- Atomicité — soit tous les changements s'appliquent, soit aucun.
- Cohérence — la base passe d'un état valide à un autre ; les contraintes sont respectées.
- Isolation — les transactions concurrentes ne se voient jamais à moitié faites.
- Durabilité — ce qui est validé survit à un plantage du serveur.

Contrôle manuel
COMMIT valide et ROLLBACK annule tout ce qui a été fait depuis START TRANSACTION.

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

Points de sauvegarde
Un SAVEPOINT annule une seule partie sans perdre le reste de la transaction :

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;

Les quatre niveaux

Le niveau par défaut d'InnoDB est REPEATABLE READ. Grâce au MVCC et aux verrous d'intervalle, InnoDB évite aussi les lectures fantômes à ce niveau, ce que la norme SQL n'exige pas.

Changer de niveau

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT @@transaction_isolation;

Attention au DDL
CREATE, ALTER, DROP et TRUNCATE provoquent un commit implicite : impossible de les annuler avec ROLLBACK. Une migration à moitié faite laisse la table telle quelle.

Transactions longues
Une transaction ouverte oblige InnoDB à conserver les anciennes versions de chaque ligne pour les lectures cohérentes. Un START TRANSACTION oublié fait gonfler l'undo log et dégrade tout le serveur. Repérez-les ainsi :

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

Recommandation
Des transactions courtes, avec la logique métier à l'extérieur et seulement le SQL à l'intérieur. READ COMMITTED réduit les verrous et c'est ce qu'utilisent beaucoup d'applications web ; restez sur REPEATABLE READ s'il vous faut que deux lectures dans la même transaction renvoient la même chose.

Mots-clés : transaction, commit, rollback, savepoint, isolation, acid, mvcc, read committed, repeatable read, serializable, autocommit, innodb_trx

Ce que verrouille InnoDB, pourquoi un interblocage survient et comment le diagnostiquer sans deviner.

S'applique à : MySQL 5.7+ MariaDB 10.5+ Aurora 2+

InnoDB verrouille des lignes, pas des tables, et le fait automatiquement à l'écriture. Presque tous les problèmes de concurrence s'expliquent par les lignes qu'une requête a fini par verrouiller, et cela dépend de l'index qu'elle a utilisé.

Types de verrou
- Partagé (S) — plusieurs transactions peuvent lire la même ligne en même temps.
- Exclusif (X) — pris par celui qui écrit ; personne d'autre ne peut la lire avec verrou ni l'écrire.
- D'intervalle (gap) — verrouille l'espace entre deux valeurs de l'index pour empêcher les insertions. Uniquement en REPEATABLE READ et SERIALIZABLE.
- Next-key — la ligne plus l'intervalle qui la précède. C'est le mode normal d'InnoDB lors du parcours d'un index.
- D'intention (IS/IX) — signale au niveau table qu'il existe des verrous de ligne à l'intérieur ; empêche un LOCK TABLES de passer.

Conséquence pratique : si la requête n'utilise aucun index, InnoDB parcourt toute la table et verrouille chaque ligne examinée, pas seulement celles qui correspondent. Un bon index n'accélère pas seulement : il réduit ce qui est verrouillé.

Lectures verrouillantes
Un SELECT ordinaire ne verrouille rien (il lit une version cohérente via MVCC). S'il vous faut lire puis écrire sans que personne ne s'intercale, demandez le verrou explicitement avec FOR UPDATE ou FOR SHARE :

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

Ce qu'est un interblocage
Deux transactions qui attendent chacune un verrou détenu par l'autre. Aucune ne peut avancer :

-- 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 le détecte tout seul et tue la transaction la moins coûteuse à annuler, qui reçoit l'erreur 1213 Deadlock found when trying to get lock. Ce n'est ni une panne du serveur ni une corruption : c'est le comportement attendu, et l'application doit réessayer cette transaction.

À ne pas confondre avec l'erreur 1205 Lock wait timeout exceeded : là il n'y a pas de cycle, juste une attente qui a dépassé innodb_lock_wait_timeout (50 s par défaut).

Diagnostic
La section LATEST DETECTED DEADLOCK de SHOW ENGINE INNODB STATUS conserve le dernier interblocage avec les deux transactions et les instructions en cause. Pour voir les verrous en cours, utilisez performance_schema :

SHOW ENGINE INNODB STATUS;

SELECT * FROM performance_schema.data_locks;

SELECT * FROM performance_schema.data_lock_waits;

SELECT @@innodb_lock_wait_timeout;

Comment les éviter
1. Toujours accéder dans le même ordre — si tout le code touche tables et lignes dans le même ordre, aucun cycle n'est possible.
2. Transactions courtes — moins de temps avec des verrous pris, moins d'occasions de collision.
3. Indexer ce que l'on filtre — évite de verrouiller des lignes qui ne correspondaient même pas.
4. Réessayer — un interblocage occasionnel est normal dans un système concurrent ; enveloppez la transaction dans une reprise avec attente croissante.
5. Éviter les SELECT ... FOR UPDATE inutiles — si vous n'allez pas écrire, ne le demandez pas.

Recommandation
Face aux verrous, regardez d'abord le plan de la requête : la plupart des interblocages réels disparaissent dès qu'on ajoute l'index manquant. La Liste des processus de Calíope montre quelle session attend.

Mots-clés : verrou, deadlock, interblocage, gap lock, next-key, for update, for share, erreur 1213, innodb status, data_locks, lock wait timeout

RANGE, LIST, HASH et KEY, élagage de partitions et purge instantanée avec DROP PARTITION.

S'applique à : MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Le partitionnement découpe une table en plusieurs morceaux physiques que le serveur continue de voir comme une seule table. Cela ne rend pas les requêtes rapides par magie : ce que vous y gagnez, c'est l'élagage de partitions et surtout la possibilité de supprimer des millions de lignes en un instant.

Quand cela vaut le coup
Le cas net est une table qui grossit par date et dont on purge l'ancien : journaux, événements, métriques, audit. Là, DROP PARTITION remplace un DELETE de plusieurs heures.

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

Les quatre types
- RANGE — par intervalles d'une valeur ordonnable, presque toujours une date. Le plus utile.
- LIST — par appartenance à un ensemble discret de valeurs.
- HASH — répartition uniforme par une expression entière ; utile pour répartir l'écriture, pas pour élaguer.
- KEY — comme HASH mais avec la fonction interne du serveur ; accepte des colonnes non entières.

Les variantes RANGE COLUMNS et LIST COLUMNS acceptent plusieurs colonnes et des types non entiers sans les envelopper dans une fonction :

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

Élagage de partitions
Le vrai bénéfice : si le WHERE filtre sur la colonne de partitionnement, le serveur ne lit que les partitions susceptibles de contenir des résultats. Vérifiez-le dans la colonne partitions d'EXPLAIN — si elles apparaissent toutes, vous n'élaguez rien et le partitionnement ne fait que vous coûter.

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

Limites à connaître d'avance
1. La clé de partitionnement doit faire partie de toutes les clés uniques, primaire comprise. D'où le PRIMARY KEY (id, ocurrido) de l'exemple et non le seul id.
2. Pas de clés étrangères : une table partitionnée ne peut ni porter ni recevoir de FOREIGN KEY.
3. 8192 partitions au maximum par table, et chacune consomme des descripteurs de fichiers.
4. Les requêtes qui ne filtrent pas sur la clé touchent toutes les partitions et deviennent plus lentes que sans partitionnement.
5. Les index sont locaux à chaque partition : l'index global n'existe pas.

Maintenance
Ajouter la partition de la période suivante et lâcher la plus ancienne est la routine normale. DROP PARTITION est quasi instantané et libère vraiment l'espace, ce qu'un DELETE massif ne fait pas :

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;

Recommandation
Ne partitionnez par date que si vous purgez par date, et créez les partitions futures à l'avance (ou avec un événement planifié) : si une ligne n'entre dans aucune plage, l'INSERT échoue. Gardez toujours une pmax en filet.

Mots-clés : partitionnement, partition, range, list, hash, key, élagage, drop partition, reorganize, information_schema.partitions, purge

WITH, WITH RECURSIVE et OVER () : le SQL moderne qui évite sous-requêtes imbriquées et tables temporaires.

S'applique à : MySQL 8.0+ MariaDB 10.2+ Aurora 3+ PostgreSQL 13+ SQLite 3.35+

Les CTE (WITH) et les fonctions de fenêtrage (OVER ()) sont arrivées dans MySQL 8.0 et MariaDB 10.2 ; sur PostgreSQL, il n'existe aucune version encore supportée qui ne les ait pas, et sur SQLite les deux sont bien en dessous du plancher de ce manuel : les CTE depuis la 3.8.3 et les fenêtres depuis la 3.25. Elles résolvent en une requête lisible ce qui exigeait auparavant des sous-requêtes imbriquées, des tables temporaires ou des variables de session.

CTE : nommer une étape intermédiaire
Une CTE est un résultat nommé qui ne vit que le temps de la requête. Elle permet de découper une longue requête en étapes et de se référer deux fois au même sous-résultat sans le répéter :

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 récursive : hiérarchies
WITH RECURSIVE parcourt les structures arborescentes — organigrammes, catégories imbriquées, nomenclatures — sans boucle dans l'application. La première branche est le cas de base et la seconde se répète jusqu'à ne plus rien renvoyer :

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;

Fonctions de fenêtrage : calculer sans regrouper
Un GROUP BY écrase les lignes ; une fonction de fenêtrage calcule sur un ensemble de lignes apparentées et conserve chaque ligne. C'est ce qui rend possible un cumul, une moyenne mobile ou un rang dans le groupe en une seule passe :

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;

Les plus utilisées
- ROW_NUMBER() — numéro courant dans la partition, sans ex aequo.
- RANK() / DENSE_RANK() — rang avec ex aequo ; RANK laisse des trous, DENSE_RANK non.
- LAG() / LEAD() — la valeur de la ligne précédente ou suivante, sans auto-jointure.
- FIRST_VALUE() / LAST_VALUE() — les extrémités de la fenêtre.
- NTILE(n) — répartit les lignes en n paquets, pour quartiles et centiles.
- Agrégats avec OVER — SUM, AVG, COUNT, MIN, MAX sans écraser les lignes.

Le cadre (ROWS BETWEEN ...) définit quelles lignes entrent dans le calcul de chacune. Par défaut, un agrégat avec ORDER BY va du début de la partition à la ligne courante, ce qui donne précisément le cumul.

Le motif le plus rentable : les N premiers par groupe
Sortir les trois produits les plus vendus de chaque catégorie sans fenêtrage exige une sous-requête corrélée par ligne. Avec ROW_NUMBER(), c'est direct :

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;

Performance
Aucune des deux n'est gratuite : la fenêtre doit trier à l'intérieur de chaque partition, donc un index qui livre déjà les lignes dans l'ordre du PARTITION BY puis de l'ORDER BY évite ce tri. Vérifiez avec EXPLAIN avant de valider la jolie version.

Recommandation
Utilisez les CTE pour que la requête se lise, et le fenêtrage pour ne pas faire dans l'application ce que le serveur fait en une passe. Si votre serveur est MySQL 5.7 ou MariaDB 10.1, aucune des deux n'existe : là, les sous-requêtes règnent encore.

Mots-clés : cte, with, with recursive, fonction de fenêtrage, over, partition by, row_number, rank, dense_rank, lag, lead, ntile, frame, hiérarchie, top n par groupe

Pourquoi une erreur bloque toute la transaction, comment un point de sauvegarde vous en sort, ce que fait vraiment chaque niveau, et le DDL qui s'annule.

S'applique à : PostgreSQL 13+

Une transaction regroupe plusieurs instructions en une unité qui s'applique entièrement ou pas du tout. Sans BEGIN explicite, PostgreSQL valide chaque instruction séparément.

La première surprise quand on vient de MySQL
Une erreur annule toute la transaction. Ensuite, chaque instruction répond la même chose — current transaction is aborted, commands ignored until end of transaction block, SQLSTATE 25P02 — jusqu'au ROLLBACK. Ce n'est pas un bogue de l'application : c'est le principe, et il empêche une transaction de continuer sur un état qui n'est plus celui que vous croyiez.

La sortie est un point de sauvegarde
SAVEPOINT marque un point de retour, et ROLLBACK TO SAVEPOINT sauve la transaction sans perdre ce qui précède :

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

Le DDL s'annule aussi
CREATE, ALTER et DROP s'exécutent dans la transaction : il n'y a pas de validation implicite ici. Une migration qui échoue à mi-chemin ne laisse pas une demi-table.

BEGIN;
ALTER TABLE cuentas ADD COLUMN moneda text;
ROLLBACK;   -- la colonne n'a jamais existé

Les quatre niveaux

READ UNCOMMITTED est accepté et signalé comme tel, mais il se comporte comme READ COMMITTED : PostgreSQL n'a de lectures sales à aucun niveau. Le niveau par défaut est READ COMMITTED.

REPEATABLE READ et SERIALIZABLE ne bloquent pas : ils annulent
Au lieu d'attendre, la transaction qui ne peut pas être sérialisée se termine avec le SQLSTATE 40001 (could not serialize access…). Cela veut dire que l'application doit réessayer : à ces deux niveaux, un 40001 est un fonctionnement normal, pas une panne. SERIALIZABLE utilise SSI, qui détecte les dépendances lecture/écriture et ne prend aucun verrou supplémentaire.

Verrous et interblocages
Un interblocage est détecté après deadlock_timeout — 1 s par défaut — et le serveur tue l'une des deux avec le SQLSTATE 40P01. Pour ne pas attendre, ou pour répartir le travail entre consommateurs :

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

FOR UPDATE NOWAIT échoue immédiatement avec 55P03 au lieu d'attendre. Attention : cet échec annule aussi la transaction.

Transactions longues
Ici une transaction ouverte ne gonfle pas un journal d'annulation : elle empêche VACUUM de nettoyer les versions mortes sur tout le serveur, et la table grossit sans nouvelles lignes. Repérez-les ainsi :

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 vaut 0 par défaut, c'est-à-dire sans limite ; lui donner une valeur est le filet qui évite qu'une session oubliée dégrade toute la base.

Recommandation
Des transactions courtes, avec la logique métier à l'extérieur. Si vous passez à REPEATABLE READ ou à SERIALIZABLE, écrivez la reprise avant de passer, pas après le premier 40001 en production.

Mots-clés : transaction, commit, rollback, savepoint, isolation, mvcc, read committed, repeatable read, serializable, ssi, 25P02, 40001, 40P01, verrou mortel, skip locked, pg_stat_activity

Pourquoi une table grossit sans nouvelles lignes, ce que nettoie vraiment chaque opération, quand les compteurs mentent et ce qu'est le wraparound.

S'applique à : PostgreSQL 13+

Dans PostgreSQL, mettre à jour une ligne ne la modifie pas : cela écrit une nouvelle version et laisse l'ancienne morte. Supprimer ne libère rien non plus tout de suite. C'est ainsi que fonctionne MVCC ici, et VACUUM est ce qui ramasse ensuite. Cela n'a pas d'équivalent dans InnoDB, et c'est derrière presque toutes les surprises de taille.

À quoi cela ressemble
Une table de 50 000 lignes occupait 12 Mo. Un seul UPDATE sur toutes les lignes l'a laissée à 23 Mo sans ajouter une seule ligne : les 50 000 anciennes versions sont toujours dans le fichier. Mesuré sur PostgreSQL 17.6.

Ce que fait chaque chose
- VACUUM marque l'espace mort comme réutilisable. Il ne rend pas l'espace au système d'exploitation : après le vacuum, la table de l'exemple occupait toujours 23 Mo, sauf que les écritures suivantes tiennent désormais dedans.
- VACUUM FULL réécrit toute la table et rend bien l'espace — elle est descendue à 11 Mo — mais il prend un verrou ACCESS EXCLUSIVE : personne ne lit ni n'écrit pendant ce temps, et il faut la place d'une copie complète. Ce n'est pas la maintenance de routine, c'est le dernier recours.
- ANALYZE ne nettoie rien : il rafraîchit les statistiques du planificateur.

Autovacuum, qui est déjà activé
autovacuum est livré on. Une table est mise en file quand ses lignes mortes dépassent autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × lignes, c'est-à-dire 50 + 20 % avec les valeurs par défaut. Sur une table de dix millions de lignes, cela fait deux millions de lignes mortes avant que quoi que ce soit ne bouge : sur les grandes tables très mises à jour, on baisse le facteur table par table :

ALTER TABLE pedidos SET (autovacuum_vacuum_scale_factor = 0.02);

Vérifier que cela marche

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;

Attention à ce compteur : c'est une estimation et ce n'est pas instantané. Mesuré sur 17.6, juste après la mise à jour de 50 000 lignes il indiquait encore 0 ; ce n'est qu'après un ANALYZE qu'il est passé à 50 000, et après le VACUUM il est revenu à 0. Si vous venez d'écrire beaucoup et que le nombre ne bouge pas, cela ne veut pas dire qu'il n'y a pas de travail en attente.

Un vacuum en cours se suit ainsi :

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

L'ennemi : la transaction ouverte
VACUUM ne peut nettoyer que ce que plus personne ne peut voir. Une transaction ouverte — ou un réplica avec hot_standby_feedback — gèle cet horizon, et alors le vacuum s'exécute, annonce qu'il a fini et ne libère rien. C'est pourquoi une session oubliée en idle in transaction fait grossir des tables qu'elle ne touche même pas.

Le wraparound, qui est une vraie urgence
Les identifiants de transaction sont sur 32 bits et sont recyclés. Pour qu'aucune ligne ne se retrouve dans le futur, le vacuum gèle les anciennes. autovacuum_freeze_max_age vaut 200 000 000 par défaut : passé cet âge, le serveur lance un autovacuum qu'on ne peut pas repousser, et s'il s'épuise malgré tout, il cesse d'accepter les écritures. On surveille ainsi :

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

Tant que cet âge reste très en dessous de deux cents millions, il n'y a rien à faire.

Recommandation
Ne désactivez pas autovacuum. Si une table grossit sans nouvelles lignes, soupçonnez dans cet ordre : une transaction ouverte, un scale_factor trop élevé pour sa taille, et seulement à la fin VACUUM FULL — avec une fenêtre de maintenance, parce qu'il verrouille toute la table.

Mots-clés : vacuum, autovacuum, bloat, mvcc, n_dead_tup, vacuum full, gel, wraparound, pg_stat_user_tables, pg_stat_progress_vacuum, datfrozenxid, hot_standby_feedback, idle in transaction

Ce que chaque instruction verrouille vraiment, pourquoi un ALTER TABLE peut arrêter vos SELECT, comment voir qui attend qui et que faire d'un 40P01.

S'applique à : PostgreSQL 13+

Dans PostgreSQL, les verrous vivent à deux endroits différents, et les confondre est ce qui rend un problème introuvable. Ceux de table sont dans pg_locks ; ceux de ligne sont dans la ligne elle-même, dans son en-tête, donc ils n'occupent pas de mémoire, ne s'escaladent jamais en verrou de table et n'apparaissent pas dans pg_locks. Un million de lignes verrouillées ne coûte pas plus qu'une seule.

Et une règle est sans exception : un SELECT normal n'attend jamais après une ligne. Il lit sa version via MVCC. La seule chose qui peut arrêter un SELECT, c'est un verrou de table.

Qui prend quel mode de table

Les trois premiers ne se gênent pas entre eux, et c'est pourquoi la charge ordinaire ne bloque jamais. Le dernier entre en conflit avec tous, y compris le SELECT.

Le piège : un ALTER TABLE qui attend fait la queue pour tout le monde
Cet ACCESS EXCLUSIVE ne double personne : il se met dans la file. Et pendant qu'il attend, tout ce qui arrive après attend derrière lui, même un SELECT qui n'aurait eu aucun problème avec l'instruction devant. Une seule transaction ouverte n'ayant fait qu'un SELECT suffit pour qu'un ALTER TABLE fige la table pour tout le monde sans avoir commencé à travailler. C'est pourquoi le DDL en production se lance avec un plafond et se réessaie :

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

Quatre modes de ligne, pas deux
Du plus fort au plus faible : FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE. Seules trois paires cohabitent —les deux partagés entre eux, et FOR NO KEY UPDATE avec FOR KEY SHARE— ; FOR UPDATE entre en conflit avec les quatre. Cette paire étrange est celle qui compte : un UPDATE qui ne touche pas la clé prend FOR NO KEY UPDATE, donc il ne bloque pas la vérification d'une clé étrangère pointant sur cette ligne, qui est celle qui demande FOR KEY SHARE.

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

Ici, on attend indéfiniment
lock_timeout vaut 0 par défaut, c'est-à-dire sans limite : il n'y a pas d'équivalent au innodb_lock_wait_timeout de MySQL, qui coupe au bout de 50 s. Le définir —par session, avant une instruction risquée, ou dans la configuration— est ce qui transforme une attente sans fin en une erreur que l'application peut réessayer. Quand il se déclenche : SQLSTATE 55P03.

Ne pas attendre, exprès

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

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

NOWAIT échoue immédiatement avec 55P03 —et cet échec, comme tout autre, annule la transaction entière—. SKIP LOCKED n'échoue pas : il renvoie moins de lignes. Avec la ligne 3 verrouillée par une autre session, la seconde requête a renvoyé 1, 2, 4 et 5. C'est ainsi qu'on répartit une file de travail entre plusieurs consommateurs sans qu'ils se marchent dessus ni s'attendent.

L'étreinte mortelle

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

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

Le serveur tue l'une des deux avec le SQLSTATE 40P01 (« deadlock detected »), et le détail dit quel processus et quelle ligne. Mais il ne le détecte pas instantanément : il ne cherche le cycle qu'une fois qu'une attente dépasse deadlock_timeout, 1 s par défaut, et la victime met cette seconde à mourir. InnoDB le détecte tout de suite ; ici, un interblocage se paie d'une seconde d'attente. Baisser deadlock_timeout n'est pas gratuit : ce travail se dépense aussi sur les attentes normales, qui sont la majorité.

Un 40P01 n'est pas une panne du serveur : c'est le comportement correct, et l'application doit réessayer cette transaction.

Diagnostic
Il n'y a pas de SHOW ENGINE INNODB STATUS ici. Il y a deux requêtes, et toutes deux doivent être lancées pendant que le verrou dure :

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 donne la liste des processus qui détiennent ce que l'autre demande, c'est-à-dire la question qu'on se pose vraiment. Ce que vous ne verrez pas dans pg_locks, ce sont les verrous de ligne : celui qui attend une ligne apparaît en attente d'un transactionid, le numéro de la transaction qui la détient. Et pour garder une trace de ce qui s'est déjà passé, log_lock_waits écrit dans le journal du serveur toute attente dépassant deadlock_timeout.

La Liste des processus de Calíope montre cette attente dans la colonne d'état : une session bloquée apparaît comme Lock: transactionid.

Verrous consultatifs
Aucune table, aucune donnée : un nombre que le serveur garde pour vous afin que deux processus de votre application ne fassent pas la même chose en même temps.

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

Tant qu'une session détient le 42, pg_try_advisory_lock(42) depuis une autre renvoie false au lieu d'attendre. Attention à la portée : ceux de session survivent au COMMIT et ne se libèrent qu'en les libérant ou en fermant la connexion ; pg_advisory_xact_lock se libère tout seul à la fin de la transaction, ce qui est presque toujours ce qu'on veut.

SERIALIZABLE ne bloque pas
À ce niveau apparaissent dans pg_locks des verrous SIReadLock. Ils ne bloquent personne : ce sont la marque de ce que la transaction a lu, et le conflit arrive comme un 40001 au moment de valider, pas comme une attente.

Recommandation
Toucher toujours les lignes dans le même ordre, et des transactions courtes, comme dans n'importe quel moteur. Ce qui est propre à PostgreSQL tient en deux points : lock_timeout défini avant chaque DDL, parce que celui qui attend fait attendre tout le monde derrière ; et la reprise écrite avant la mise en production, seule chose qui rende un 40P01 inoffensif.

Mots-clés : verrou, lock, interblocage, deadlock, 40p01, 55p03, pg_locks, pg_blocking_pids, lock_timeout, deadlock_timeout, access exclusive, for update, for key share, skip locked, nowait, verrou consultatif, advisory

Les six méthodes d'index et quand employer chacune, index partiels et sur expression, pourquoi un Index Only Scan va parfois quand même à la table, et comment trouver ceux que personne n'utilise.

S'applique à : PostgreSQL 13+

Un index accélère les recherches au prix de l'espace et du travail à chaque écriture. Ce qui change en venant de MySQL, ce n'est pas cette idée, mais qu'il y a ici six méthodes au lieu d'une avec des exceptions, et que presque tout ce qui est une option d'index dans MySQL —le préfixe, l'invisibilité— est ici autre chose.

Les six méthodes

Les tailles expliquent le choix mieux que la théorie. Sur une table de 200 000 lignes et 22 Mo, avec un horodatage qui croît avec l'insertion :

- B-tree sur cette colonne : 4 408 ko.
- BRIN sur la même colonne : 24 ko.

BRIN ne stocke pas les lignes mais le minimum et le maximum de chaque bloc : il ne sert donc que si l'ordre physique ressemble à l'ordre de la valeur —et quand il sert, il ne coûte presque rien—. Sur la même table, un hash sur la colonne client a occupé 7 032 ko et le B-tree sur cette colonne 1 400 ko : plus petit, et en plus utile pour les intervalles et le tri. C'est pourquoi le B-tree est la réponse par défaut et le hash un cas particulier.

Index partiels : la moitié de l'idée, le dixième de la taille
Un index peut porter un WHERE, et alors il n'indexe que les lignes qui remplissent la condition. Si vous interrogez la file des en-attente et qu'ils représentent 5 %, indexez les 5 % :

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

Mesuré sur cette même table : l'index complet de la colonne occupait 1 400 ko et le partiel 88 ko. Le WHERE de la requête doit impliquer celui de l'index, sinon le planificateur ne l'utilisera pas.

Ici, pas d'index de préfixe : des index sur expression
CREATE INDEX … ON paginas (url(64)) n'est pas une syntaxe valide ; le serveur lit url(64) comme un appel de fonction et répond 42883 function url(integer) does not exist. L'équivalent est d'indexer l'expression :

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

Et les petits caractères : l'index n'entre en jeu que si la requête écrit l'expression de la même façon. WHERE lower(email) = 'ana@ejemplo.com' l'utilise ; WHERE email ILIKE 'Ana@%' non, et avale la table entière.

Index couvrants, et pourquoi ils ne couvrent pas toujours
INCLUDE ajoute des colonnes stockées dans l'index mais qui ne le trient pas :

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

Avec cela, EXPLAIN affiche Index Only Scan. Mais « only » n'est qu'une demi-promesse : PostgreSQL ne peut pas savoir depuis l'index si une ligne est visible pour votre transaction, il consulte donc la carte de visibilité, entretenue par VACUUM. Mesuré : juste après avoir mis à jour mille lignes, le même plan disait Heap Fetches: 2 ; après un VACUUM, Heap Fetches: 0. Un index couvrant sur une table qu'on écrit et qu'on ne nettoie pas va quand même à la table.

La règle du préfixe gauche n'est pas stricte
Dans MySQL, un index sur (A, B) ne sert pas pour WHERE B = ?. Ici, il peut servir : mesuré, une requête ne filtrant que sur la deuxième colonne s'est résolue par un Index Only Scan sur l'index composite. Ce n'est ni magique ni un substitut au bon index —il le parcourt entièrement au lieu d'y descendre—, mais quand l'index est bien plus petit que la table, cela reste rentable. Conséquence pratique : avant de créer l'index « manquant », regardez le plan ; il se peut qu'un autre serve déjà.

GIN pour ce qui vit dans un champ

CREATE INDEX idx_datos ON eventos USING gin (datos);

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

Sans l'index, cette requête est un parcours séquentiel ; avec lui, un Bitmap Index Scan. Le GIN du test occupait 864 ko pour 200 000 lignes. Pour jsonb, si vous n'interrogez qu'avec @>, jsonb_path_ops occupe moins : 640 ko contre les 864 du GIN normal, sur les mêmes données.

Construire sans arrêter la table
Un CREATE INDEX normal prend un verrou SHARE : il laisse lire et arrête les écritures. CREATE INDEX CONCURRENTLY prend SHARE UPDATE EXCLUSIVE, donc n'arrête rien, en échange de deux parcours de la table et de trois règles :

CREATE INDEX CONCURRENTLY idx_pedidos_cliente ON pedidos (cliente_id);

-- En reste-t-il à moitié construits ?
SELECT indexrelid::regclass AS indice, indisvalid
  FROM pg_index WHERE NOT indisvalid;

1. Impossible de le lancer dans une transaction — 25001 CREATE INDEX CONCURRENTLY cannot run inside a transaction block.
2. En cas d'échec, il laisse un index invalide : personne ne l'utilise, mais il est maintenu à chaque écriture. Il faut le trouver avec la requête ci-dessus et le supprimer.
3. Depuis PostgreSQL 12 il existe REINDEX INDEX CONCURRENTLY, la façon de reconstruire un index gonflé sans arrêter la table.

Les index que personne n'utilise

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;

Deux précautions avant de supprimer. La première : le compteur n'est pas instantané. Dans le test, trois requêtes ayant utilisé l'index l'ont laissé à 0 sur le moment et une seconde plus tard ; ce n'est qu'au bout de trois secondes qu'il a dit 3. La seconde : il compte depuis le dernier pg_stat_reset(), et sur une réplique il compte le travail de la réplique ; un index utilisé seulement par le rapport de fin de mois paraît donc mort les 29 autres jours.

Recommandation
Chaque index de plus se paie à chaque INSERT et à chaque UPDATE de ses colonnes. L'ordre qui fonctionne : regarder le plan, créer l'index avec CONCURRENTLY, regarder à nouveau le plan, et revoir pg_stat_user_indexes un mois plus tard. Un index que personne n'utilise n'est pas neutre : il coûte de l'écriture, de l'espace et du temps de VACUUM.

Mots-clés : index, btree, brin, gin, gist, spgist, hash, index partiel, index sur expression, include, index only scan, heap fetches, concurrently, reindex, pg_stat_user_indexes, indisvalid, jsonb_path_ops, carte de visibilité

Lire un plan et comparer l'estimation au mesuré, statistiques étendues pour des colonnes qui s'impliquent, pourquoi work_mem est par opération et ce que mesure vraiment le taux de succès du cache.

S'applique à : PostgreSQL 13+

Diagnostiquer ici, c'est lire un plan et comparer deux nombres. Tout le reste —index, mémoire, statistiques— découle de cette comparaison.

EXPLAIN n'exécute pas ; EXPLAIN ANALYZE si
La première forme ne demande que le plan. La seconde exécute vraiment la requête pour la mesurer, y compris un UPDATE ou un DELETE. Si l'instruction écrit, encadrez-la :

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

Les deux nombres qui comptent
Chaque nœud porte une estimation et une mesure : rows=… est ce que le planificateur a cru, actual rows=… ce qui est sorti. Quand ils s'écartent beaucoup, le mauvais plan est une conséquence, pas la cause.

Exemple mesuré, avec deux colonnes qui s'impliquent —ville et province— :

- Sans aide, le planificateur a estimé 11 710 lignes et il en est sorti 60 000 : il a multiplié les deux probabilités comme si elles étaient indépendantes.
- Avec une statistique étendue, l'estimation est passée à 59 610.

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

C'est l'outil qui n'existe pas dans MySQL et qui règle toute la famille du « le plan ignore mon index » : si le serveur croit lire 4 % de la table alors qu'il en lit 20 %, il choisira mal pour de bonnes raisons.

BUFFERS, qu'il faut demander

EXPLAIN (ANALYZE, BUFFERS) SELECT … ;

shared hit, ce sont les blocs déjà en mémoire ; shared read, ceux qu'il a fallu aller chercher. Et temp read/written est le vrai indice : la requête est partie sur disque. De plus, track_io_timing est désactivé par défaut, donc les temps d'E/S n'apparaissent pas tant qu'on ne l'active pas.

work_mem est par opération, pas par connexion
C'est le réglage qui surprend le plus, et celui qu'on se trompe le plus souvent à régler. Chaque tri, chaque jointure par hachage et chaque agrégation par hachage peut utiliser jusqu'à work_mem, et une requête qui en enchaîne trois —ou qui lance deux processus parallèles— en utilise un multiple. La valeur d'usine est de 4 Mo.

Mesuré sur 300 000 lignes, la même requête avec ORDER BY :

- Avec work_mem = 64kB : Sort Method: external merge Disk: 15680kB, et le nœud de tri a pris ~144 ms.
- Avec work_mem = 64MB : Sort Method: quicksort Memory: 29627kB, et il a pris ~70 ms.

Le chiffre qui dit la vérité, c'est Sort Method. Augmenter work_mem globalement multiplie par connexions et par opérations ; le prudent est de l'augmenter dans la session qui en a besoin :

SET work_mem = '64MB';

Quelle requête coûte le plus : pg_stat_statements
C'est l'équivalent du journal des requêtes lentes, mais agrégé : une ligne par forme de requête, avec les appels, le temps total et les lignes.
Dans Calíope, la même chose se lit sans écrire la requête : l'outil Requêtes lentes affiche ce résumé, distingue l'extension absente de la bibliothèque non chargée, et envoie n'importe quelle ligne vers l'éditeur.

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;

Les petits caractères : CREATE EXTENSION ne suffit pas. Il faut l'ajouter à shared_preload_libraries et redémarrer le serveur ; si l'on se contente de créer l'extension, la première requête répond 55000 pg_stat_statements must be loaded via "shared_preload_libraries". Triez par total_exec_time, pas par mean_exec_time : la requête qui mange l'après-midi est en général une requête rapide exécutée un million de fois.

Le taux de succès du 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();

C'est le nombre que le Tableau de bord de Calíope affiche sous « Cache de données ». Il mesure combien de lectures ont été servies sans descendre au système de fichiers depuis la dernière remise à zéro des statistiques —et non la mémoire occupée—, et sur une petite base il sort très haut par définition : dans le test, 99,85 %. Une valeur basse et durable signifie bien que shared_buffers est trop petit ; une valeur haute ne prouve pas que tout va bien.

Parallélisme
max_parallel_workers_per_gather vaut 2 par défaut, et quand le planificateur s'en sert, un nœud Gather ou Gather Merge apparaît. Chaque processus a son propre work_mem, ce qui est l'autre moitié du piège ci-dessus.

Recommandation
L'ordre qui fonctionne : trouver la requête avec pg_stat_statements, la regarder avec EXPLAIN (ANALYZE, BUFFERS), comparer rows et actual rows, et seulement ensuite décider s'il manque un index, des statistiques ou de la mémoire. Toucher à shared_buffers avant d'avoir lu un plan, c'est le chemin long.

Mots-clés : performance, explain, analyze, buffers, plan, planificateur, estimation, create statistics, statistique étendue, work_mem, sort method, external merge, pg_stat_statements, shared_preload_libraries, pg_stat_database, cache, track_io_timing, parallélisme

Où s'écrit chaque paramètre et lequel l'emporte, ce qui exige un redémarrage, pourquoi effective_cache_size ne réserve pas de mémoire et pourquoi on n'augmente pas max_connections.

S'applique à : PostgreSQL 13+

PostgreSQL a 378 paramètres —comptés sur ce serveur—, et la bonne nouvelle est qu'on en touche une poignée. Ce qu'il faut apprendre d'abord, ce n'est pas lesquels, mais où ils s'écrivent et quand ils prennent effet.

Quatre endroits, et le serveur dit lequel l'emporte
- postgresql.conf — le fichier de toujours, édité à la main.
- postgresql.auto.conf — écrit par ALTER SYSTEM et jamais édité à la main ; il le dit lui-même dès sa première ligne.
- Par base ou par rôle — ALTER DATABASE … SET, ALTER ROLE … SET.
- Par session — SET, qui dure le temps de la connexion.

Qui l'emporte ne se devine pas : la colonne source de pg_settings le dit. Mesuré : après ALTER DATABASE demo SET work_mem = '32MB', une nouvelle connexion lisait 32MB avec source = database ; après le RESET, 4MB avec 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');

Trois classes de paramètre, et celle qui fait mal
La colonne context dit ce qu'il faut pour le changer :
- user / superuser — un SET dans la session suffit (work_mem, effective_cache_size).
- sighup — il faut recharger (checkpoint_timeout, max_wal_size, presque tout l'autovacuum).
- postmaster — il faut redémarrer le serveur. Sur ce serveur, ce sont 65 des 378, dont shared_buffers, max_connections, wal_level, shared_preload_libraries et autovacuum_max_workers.

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

-- Quelque chose attend-il un redémarrage ?
SELECT name, setting FROM pg_settings WHERE pending_restart;

Un détail mesuré qui évite une frayeur : pending_restart ne s'allume pas à l'instant même du rechargement. Juste après, il valait encore false ; une demi-seconde plus tard il disait true. On le consulte ensuite, pas dans la même phrase.

shared_buffers et effective_cache_size ne sont pas la même chose, et l'un des deux ne réserve rien
- shared_buffers, c'est de la mémoire réelle : le cache propre du serveur. D'usine, 128 Mo, ce qui est peu pour un serveur dédié ; la règle habituelle est 25 % de la RAM.
- effective_cache_size ne réserve rien. C'est ce que le planificateur suppose exister entre le cache de PostgreSQL et celui du système d'exploitation, et cela ne sert qu'à décider si un index est rentable. Le changer ne déplace pas un octet : cela change les plans.

Les confondre conduit à augmenter effective_cache_size en espérant plus de cache, ou à augmenter shared_buffers en espérant un autre plan.

max_connections ne s'augmente pas : on met un pool devant
Il vaut 100 d'usine, et ici chaque connexion est un processus du système, pas un fil. Passer à mille n'est pas un nombre plus grand : ce sont mille processus, avec leur mémoire et leur work_mem par opération. La réponse est un gestionnaire de pool (pgBouncer et consorts). Et c'est un de ceux qui exigent un redémarrage.

WAL et points de contrôle
max_wal_size (1 Go d'usine) et checkpoint_timeout (5 min) décident à quelle fréquence tout est écrit sur disque. Si les points de contrôle se déclenchent par taille plutôt que par temps, le serveur écrit par à-coups ; on le voit en activant log_checkpoints et on le corrige en augmentant max_wal_size. checkpoint_completion_target vaut déjà 0,9, ce qui étale cette écriture dans le temps au lieu de la concentrer.

Autovacuum
Ici, autovacuum_naptime vaut 60 s, autovacuum_max_workers 3 —celui-ci exige un redémarrage— et autovacuum_vacuum_scale_factor 0,2, c'est-à-dire qu'une table est nettoyée quand 20 % de ses lignes ont changé. Sur une table d'un milliard de lignes, cela revient à attendre deux cents millions de versions mortes : les grandes tables portent donc leur propre réglage.

ALTER TABLE eventos SET (autovacuum_vacuum_scale_factor = 0.01);

synchronous_commit, le seul qui change la promesse
Le désactiver fait que le COMMIT n'attend pas que le WAL atteigne le disque : on gagne en latence et on risque les dernières transactions en cas de coupure —pas l'intégrité de la base, seulement les derniers commits—. Il est user, il peut donc être désactivé là seulement où ce marché est accepté :

SET synchronous_commit = off;

Recommandation
En toucher peu, un à la fois, en mesurant. ALTER SYSTEM plutôt que d'éditer des fichiers —c'est tracé et annulé par ALTER SYSTEM RESET—, et par base ou par rôle avant que global : un réglage dont seul le rapport nocturne a besoin n'a pas à être payé le reste de la journée.

Mots-clés : configuration, 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

Pourquoi le compte ne contient pas l'hôte, des rôles qui sont utilisateurs et groupes, le piège de l'octroi qui n'atteint pas les tables de demain, et pourquoi la sécurité de ligne peut être activée et sans effet.

S'applique à : PostgreSQL 13+

La différence de fond avec MySQL, c'est qu'ici le compte ne contient pas l'hôte. Il n'existe pas de ana@192.168.1.% : il existe le rôle ana, et d'où il peut se connecter et comment il s'authentifie relève d'un fichier à part, pg_hba.conf.

pg_hba.conf : la première ligne qui correspond l'emporte
Il se lit de haut en bas et s'arrête là. Et nul besoin de l'ouvrir pour le voir :

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

La colonne error dit si une ligne est mal écrite —c'est ce qui évite le redémarrage où le serveur ne revient pas—. Les changements prennent effet avec SELECT pg_reload_conf(), sans redémarrage. Sur le serveur de test il y avait sept règles, celles de 127.0.0.1 en trust et la dernière, pour tout le reste, en scram-sha-256 : l'ordre est la politique.

Un rôle est à la fois un utilisateur et un groupe
Il n'y a pas deux concepts : CREATE USER est exactement CREATE ROLE … LOGIN. Ce qui distingue une personne d'un groupe est l'attribut LOGIN, et rien d'autre.

CREATE ROLE app_ro;                       -- sans LOGIN : fait office de groupe
CREATE ROLE ana LOGIN PASSWORD 'secreta';
GRANT app_ro TO ana;                      -- ana hérite des droits d'app_ro

Les rôles héritent par défaut, donc ana utilise les privilèges d'app_ro sans rien faire. Avec NOINHERIT, il faut les réclamer par SET ROLE, ce qu'on utilise quand on veut que l'étape soit explicite.

Les mots de passe sont stockés en scram-sha-256, valeur par défaut depuis PostgreSQL 14 —le serveur de test le confirme— ; md5 existe encore et ne devrait plus servir.

Le vrai piège : accorder n'atteint pas l'avenir
GRANT … ON ALL TABLES IN SCHEMA accorde sur les tables qui existent aujourd'hui. Mesuré : après l'octroi, app_ro pouvait lire la table existante et pas celle créée une minute plus tard. Ce qui couvre l'avenir est une autre instruction :

GRANT USAGE ON SCHEMA public TO app_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_ro;          -- celles d'aujourd'hui
ALTER DEFAULT PRIVILEGES IN SCHEMA public
      GRANT SELECT ON TABLES TO app_ro;                          -- celles de demain

Et en petits caractères : les privilèges par défaut appartiennent à celui qui les déclare, pas au schéma ; si les tables sont créées par un autre rôle, il faut aussi les déclarer avec FOR ROLE. Ce qui a été déclaré se voit dans pg_default_acl.

Accorder laisse en outre une trace qu'il faut défaire : après un ON ALL TABLES, supprimer le rôle échoue avec DependentObjectsStillExist et la liste des tables où un privilège subsiste —dans le test, même celles de PostGIS—. Le contraire est REVOKE, ou DROP OWNED BY rôle avant le DROP ROLE.

Le schéma public n'est plus à tout le monde
Depuis PostgreSQL 15, PUBLIC conserve USAGE sur le schéma public mais n'a plus CREATE. Mesuré en 17.6 : un rôle fraîchement créé donne USAGE = true et CREATE = false. Qui apporte des scripts d'une version antérieure verra échouer le premier CREATE TABLE d'un utilisateur qui en avait le droit.

Rôles prédéfinis : surveiller sans être superutilisateur
Le serveur fournit quinze rôles tout prêts. Ceux qui évitent un superutilisateur de trop :
- pg_read_all_data, pg_write_all_data — tout lire ou tout écrire, sans autres pouvoirs.
- pg_monitor — voir les vues de statistiques complètes ; inclut pg_read_all_stats et pg_read_all_settings.
- pg_signal_backend — annuler des requêtes et fermer les sessions d'autrui.
- pg_maintain (PostgreSQL 16+) — VACUUM, ANALYZE, REINDEX sans être propriétaire.

Sécurité au niveau des lignes
Une politique filtre les lignes que chaque rôle voit, dans la même table :

ALTER TABLE pedidos ENABLE ROW LEVEL SECURITY;

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

Voici ce qu'il faut mesurer avant de faire confiance. Avec la même politique et la même table :

- Le rôle visé par la politique a vu une ligne. Correct.
- Le propriétaire de la table —non superutilisateur— a vu les deux : le propriétaire n'est pas soumis à ses propres politiques tant qu'on n'a pas déclaré ALTER TABLE … FORCE ROW LEVEL SECURITY. Avec FORCE, il n'en voit qu'une.
- Le superutilisateur a vu les deux, même avec FORCE. Les superutilisateurs et les rôles BYPASSRLS contournent toujours les politiques.

Autrement dit : une application qui se connecte en tant que propriétaire des tables —et a fortiori en superutilisateur— a la sécurité de ligne activée et sans effet. La vérification consiste à se connecter avec le rôle réel et à compter les lignes.

Recommandation
Un rôle par application, sans LOGIN pour les groupes, et aucun en SUPERUSER hormis celui d'administration. ALTER DEFAULT PRIVILEGES dans le même commit que le GRANT, sinon la permission durera jusqu'à la table suivante. Et notez ce que vous accordez en bloc : le DROP ROLE dans un an vous le réclamera.

Mots-clés : sécurité, rôle, utilisateur, groupe, 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

Logique contre physique et à quoi sert chacune, ce que pg_dump laisse dehors et qui laisse la base sans personne pour entrer, pourquoi le PITR ne marche pas d'usine et quel slot peut remplir votre disque.

S'applique à : PostgreSQL 13+

Il y a deux sortes de sauvegarde et elles ne servent pas à la même chose. Se tromper se découvre le jour de la restauration.

Sauvegarde logique

-- en ligne de commande, pas dans l'éditeur SQL :
-- pg_dump -d demo -Fc -f demo.dump
-- pg_restore -d demo_nueva -j 4 demo.dump

Le format -Fc (custom) est celui à prendre par défaut : sur la même base, le vidage en texte occupait 3,1 Mo et le custom 905 ko, et il porte en plus un index —pg_restore -l a listé les trente blocs de données— ce qui permet de restaurer une table, et de le faire en parallèle avec -j.

Ce que pg_dump n'emporte pas, et c'est ce qui mord
Les rôles et les réglages globaux n'y sont pas. Mesuré : le vidage de la base n'avait pas un seul CREATE ROLE, alors que pg_dumpall --globals-only a produit les deux qui existaient. Ne restaurer que le vidage laisse une base parfaite où personne ne peut entrer. Une sauvegarde logique complète, ce sont deux fichiers :
- pg_dumpall --globals-only — rôles, mots de passe et privilèges du cluster.
- pg_dump de chaque base.

pg_dump est cohérent —il travaille sur un instantané— et ne bloque pas les écrivains ; mais il prend un verrou ACCESS SHARE, donc un ALTER TABLE lancé en même temps se met à attendre, et tout fait la queue derrière lui.

Sauvegarde physique
pg_basebackup copie le cluster entier. Mesuré sur le serveur de test : 84 Mo en 1,3 s, avec -X stream, qui emporte aussi le WAL produit pendant la copie —sans quoi la copie n'est pas restaurable—. Il laisse un backup_label qui indique à partir de quel point du WAL rejouer :

START WAL LOCATION: 0/13000028 (file 000000010000000000000013)

PITR : récupérer jusqu'à un instant
C'est la raison d'être de la sauvegarde physique, et cela ne marche pas d'usine : archive_mode arrive désactivé, mesuré sur ce serveur. Sans archivage, une sauvegarde physique restaure exactement le moment où elle a été prise, pas une seconde de plus.

Il faut trois pièces :
1. archive_mode = on et un archive_command qui copie chaque segment du WAL en lieu sûr (ou pg_receivewal depuis une autre machine).
2. Un pg_basebackup périodique.
3. À la restauration : les fichiers de la sauvegarde, un restore_command qui rapatrie les segments, recovery_target_time = '…' et un fichier vide recovery.signal dans le répertoire de données.

Ce dernier point déroute ceux qui viennent des vieilles versions : depuis PostgreSQL 12, recovery.conf n'existe plus ; les paramètres vont dans postgresql.conf et ce qui déclare « ceci est une récupération », c'est le fichier signal.

Les slots de réplication sont une lame à double tranchant
Un slot garantit que le serveur ne supprime pas le WAL qu'un consommateur n'a pas lu. Si le consommateur disparaît et que le slot reste, le WAL s'accumule jusqu'à remplir le disque —et un disque plein est un arrêt, pas un avertissement—. On les surveille ainsi :

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 pose la limite : au-delà, le serveur préfère invalider le slot plutôt que manquer de disque.

La sauvegarde de Calíope est logique
Ce que produit l'outil de sauvegarde, c'est du SQL —CREATE et INSERT—, de la famille de pg_dump, pas de pg_basebackup. Cela sert à migrer et à récupérer des données ; récupérer un serveur entier jusqu'à un instant demande ce qui précède, qui relève du système d'exploitation et non d'un client.

Recommandation
Les deux fichiers de la sauvegarde logique, toujours ensemble —--globals-only et le vidage— et tester la restauration, pas la sauvegarde : un fichier produit sans erreur peut ne pas se restaurer, et on ne le sait qu'en le restaurant sur un vrai serveur.

Mots-clés : sauvegarde, backup, pg_dump, pg_restore, pg_dumpall, globals, pg_basebackup, wal, archive_mode, archive_command, pitr, recovery_target_time, recovery.signal, slot de réplication, wal_status, restauration

Le DDL est transactionnel, ce qui coûte est le verrou et non l'ALTER, quels changements réécrivent toute la table, et le motif NOT VALID + VALIDATE qui évite d'arrêter la base.

S'applique à : PostgreSQL 13+

Ici le DDL est transactionnel. Cela change la façon d'écrire les migrations et c'est la première chose à assimiler en venant de MySQL, où chaque ALTER valide de son côté.

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

Vérifié : après ce ROLLBACK, aucune des trois choses ne subsistait. Une migration qui échoue à mi-chemin ne laisse pas une demi-base ; c'est pourquoi le bon réflexe est de mettre toute la migration dans une transaction.

Les exceptions se comptent sur les doigts : CREATE INDEX CONCURRENTLY, VACUUM et ALTER SYSTEM ne peuvent pas figurer dans une transaction.

Ce qui coûte n'est pas l'ALTER : c'est le verrou
Presque tout ALTER TABLE prend un ACCESS EXCLUSIVE, qui entre en conflit même avec un SELECT. Même si le changement dure une milliseconde, attendre le verrou peut durer des heures —et pendant l'attente, tout ce qui arrive derrière fait la queue—. C'est pourquoi le DDL en production se lance toujours ainsi :

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

S'il n'obtient pas le verrou, il échoue en trois secondes et on réessaie. Sans cela, une migration d'une milliseconde peut arrêter toute l'application.

Ce qui réécrit la table et ce qui ne la réécrit pas
Réécrire signifie copier la table entière : cela dure en proportion de la taille et exige le double d'espace disque le temps de l'opération. Mesuré sur 500 000 lignes et 32 Mo :

La règle qui résume le tableau : élargir est gratuit, rétrécir réécrit. Et ADD COLUMN avec valeur par défaut a cessé de réécrire dans PostgreSQL 11 : le détour qui consistait à ajouter la colonne vide et à la remplir par lots n'est plus nécessaire.

Deux avertissements que les temps ne montrent pas :
- Un DROP COLUMN est instantané parce qu'il se contente de marquer la colonne supprimée : l'espace ne revient pas tant que la table n'est pas réécrite.
- SET NOT NULL et un CHECK ordinaire ne réécrivent pas, mais parcourent toute la table verrou tenu. Sur une grande table, c'est déjà un arrêt.

Le motif qui évite d'arrêter la base : NOT VALID puis VALIDATE
Une contrainte peut s'ajouter en deux temps : on la déclare d'abord sans vérifier l'existant —instantané—, puis on la valide, ce qui prend du temps mais avec un verrou bien plus faible.

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;

Mesuré : la déclaration NOT VALID a pris 0,7 ms avec SHARE ROW EXCLUSIVE —qui laisse lire—, et la validation 67 ms avec SHARE UPDATE EXCLUSIVE, qui ne bloque même pas les écrivains. Le faire d'un coup a coûté autant de temps, mais verrou fort tenu tout du long. Sur une vraie table, cette différence sépare un déploiement d'une panne.

Dès que la contrainte est NOT VALID, le serveur l'applique aux nouvelles lignes ; il ne reste qu'à vérifier les anciennes.

Index
Le CREATE INDEX ordinaire arrête les écritures ; CREATE INDEX CONCURRENTLY n'arrête rien, mais il n'entre pas dans la transaction de la migration : il va donc dans son propre pas et se vérifie ensuite (pg_index.indisvalid).

Recommandation
lock_timeout toujours ; la migration dans une transaction sauf ce qui ne peut pas ; NOT VALID + VALIDATE pour les contraintes sur de grandes tables ; et méfiance avec les changements de type, là où se cache la réécriture. S'il faut rétrécir un type, il vaut presque toujours mieux ajouter la nouvelle colonne, copier par lots et renommer.

Mots-clés : ddl, alter table, migration, transactionnel, rollback, lock_timeout, access exclusive, réécriture, relfilenode, add column, drop column, set not null, not valid, validate constraint, create index concurrently

Ce qui n'existe pas et donne une erreur de syntaxe, pourquoi text ne vaut pas moins que varchar, numeric face à la virgule flottante, ce que stocke vraiment timestamptz et pourquoi jsonb n'économise pas de place.

S'applique à : PostgreSQL 13+

Les types sont l'un des rares endroits où la migration depuis MySQL échoue du premier coup, et tant mieux : ce qui n'existe pas donne une erreur de syntaxe au lieu d'être accepté à moitié.

Ce qui n'existe pas ici
- UNSIGNED — 42601 syntax error at or near "unsigned". Il n'y a pas d'entiers non signés ; on prend le type suivant ou un CHECK (n >= 0).
- INT(11) — également 42601. La largeur d'affichage de MySQL n'existe pas, et n'a jamais signifié ce qu'elle semblait dire.
- TINYINT, DATETIME, DOUBLE avec parenthèses, et les types SET/ENUM de MySQL. Leurs équivalents sont smallint, timestamptz, double precision et un vrai type enum.

Texte : prenez text et c'est tout
Mesuré, avec la même valeur 'hola' : text a occupé 5 octets, varchar(50) 5 et char(50) 51. Les trois se stockent pareil ; varchar(n) n'ajoute qu'un contrôle de longueur et char(n) complète avec des espaces. Et ces espaces changent les comparaisons : 'x' = 'x ' est faux en text et vrai en char.

Ici, text n'est pas pire que varchar : il n'y a pas de pénalité. On met varchar(n) quand la limite est une règle métier, et char(n) pratiquement jamais.

Nombres : numeric pour l'argent, et ce n'est pas une superstition

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

Mesuré : en virgule flottante, 0.1 * 3 a donné 0.30000000000000004 ; en numeric, 0.3 exact. numeric est exact et de précision arbitraire, et cela se paie en espace et en vitesse —10 octets contre les 8 de float8 pour cette valeur, et une arithmétique logicielle—. Pour l'argent et pour tout chiffre additionné devant un client, numeric.

Tailles mesurées : int 4, bigint 8, boolean 1, uuid 16 —contre les 36 qu'il occuperait en texte—.

Dates : timestamptz presque toujours
timestamp et timestamptz occupent les mêmes 8 octets. La différence n'est ni la taille ni le fait que l'un stocke la zone : aucun ne stocke la zone. timestamptz stocke un instant —il convertit en UTC à l'entrée et vers la zone de la session à la sortie—, et timestamp stocke une lecture d'horloge, rien de plus.

Mesuré, le même instant avec deux zones de session :

C'est le même moment dit de deux façons. Avec timestamp, il n'y a aucune conversion : ce qui est entré est ce qui sort, et celui qui doit savoir à quelle heure c'était vraiment ne peut pas le retrouver. date occupe 4 octets et interval 16.

json face à jsonb : presque toujours jsonb, et pas pour la taille

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 stocke le texte tel quel : il conserve l'ordre, les espaces et même les clés répétées. jsonb stocke un arbre déjà analysé : il trie les clés, garde la dernière répétée et normalise les espaces. C'est pourquoi jsonb s'interroge vite et s'indexe en GIN, et json ne sert que s'il faut restituer le document octet pour octet tel qu'il est arrivé.

Ce qui n'est pas vrai, c'est que jsonb économise de la place : mesuré sur 200 000 documents identiques, json a occupé 14 Mo et jsonb 16 Mo. On choisit jsonb pour la façon de l'interroger, pas pour ce qu'il pèse.

Tableaux
Un tableau est un type de première classe, avec ses opérateurs —@> pour la contenance, array_length— et son index GIN. C'est commode pour des étiquettes et des listes courtes ; cela cesse de l'être dès que les éléments ont besoin d'attributs propres ou doivent être joints à une autre table. Un tableau n'est pas une table économisée : c'est une valeur.

serial ou IDENTITY
serial n'est pas un type : c'est du sucre qui crée une séquence et met son nextval en valeur par défaut. GENERATED ALWAYS AS IDENTITY est la forme standard et protège en plus la colonne : tenter d'y insérer une valeur à la main a répondu 428C9 cannot insert a non-DEFAULT value into column. Pour les tables neuves, IDENTITY.

Recommandation
text pour le texte, numeric pour l'argent, timestamptz pour les instants, jsonb pour les documents qu'on interroge et IDENTITY pour les clés. Et en migrant depuis MySQL, laissez l'erreur de syntaxe faire son travail : cela vaut mieux qu'un type accepté qui signifie autre chose.

Mots-clés : types, text, varchar, char, numeric, float, decimal, timestamptz, timestamp, fuseau horaire, json, jsonb, tableau, array, uuid, serial, identity, unsigned, enum

Les codes qu'on voit tous les jours, pourquoi on programme sur la classe et non sur le code, ce que disent le DETAIL et le HINT que presque personne n'affiche, et où est le code quand la connexion ne s'ouvre même pas.

S'applique à : PostgreSQL 13+

Ici, il n'y a pas de numéros d'erreur. Il y a le SQLSTATE : cinq caractères, dont les deux premiers sont la classe. Et la classe est ce sur quoi on programme : elle dit quoi faire sans savoir exactement ce qui a échoué.

Ceux qu'on voit tous les jours

Les classes, c'est ce qu'il faut regarder

La conséquence pratique : une application réessaie la classe 40 et ne réessaie pas la 42. Et si la reprise ne fait pas la différence, ou bien on perd une transaction légitime, ou bien on répète mille fois une requête qui ne marchera jamais.

Le message a trois parties, et la troisième est l'utile
MESSAGE dit ce qui s'est passé, DETAIL donne la ligne ou la valeur, et HINT dit quoi faire. Mesurés :

- 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 qui n'affiche que le MESSAGE jette la moitié de l'information —et justement celle qui dit comment s'en sortir—. Calíope compose les trois.

De plus, l'erreur apporte des champs séparés : la table, la colonne et le nom de la contrainte. Avec 23505 est arrivé constraint = er_d_pkey, ce qui permet de traduire en « cette adresse est déjà enregistrée » sans analyser le texte du message.

Une erreur annule la transaction
Après n'importe quelle erreur dans un BEGIN, tout ce qui suit répond 25P02 jusqu'au ROLLBACK. Ce n'est pas un défaut du client : c'est le principe, et la sortie élégante, ce sont les points de sauvegarde.

Les erreurs de connexion n'arrivent pas dans la réponse
Si le rôle ou la base n'existent pas, ou si le mot de passe est faux, la connexion ne s'ouvre même pas : le client ne voit que « connection failed ». Le code est dans le journal du serveur, et seulement si on le demande :

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

Avec cela, le journal est passé de FATAL: database "no_existe" does not exist à FATAL: 3D000: database "no_existe" does not exist —et 28000 pour le rôle inexistant—. C'est la différence entre deviner et savoir quand quelqu'un signale qu'il « n'arrive pas à se connecter ».

Recommandation
Dans le code applicatif, brancher sur la classe et n'utiliser le code complet que pour les messages vus par l'utilisateur (23505 → « existe déjà »). Toujours enregistrer le SQLSTATE dans son propre journal : le texte du message change avec la langue du serveur, le code non.

Mots-clés : erreur, sqlstate, code, classe, 23505, 23503, 42p01, 42601, 42883, 42501, 25p02, 40001, 40p01, 55p03, 57014, 3d000, 28000, detail, hint, reprise, log_error_verbosity

Partitionnement déclaratif et ce que le planificateur élague vraiment, pourquoi il n'y a pas d'index globaux ni d'unicité sur une seule colonne, le CHECK qui fait passer un ATTACH de 68 ms à une demi-milliseconde, et ce que coûte la partition par défaut.

S'applique à : PostgreSQL 13+

Le partitionnement est ici déclaratif : on déclare la clé et chaque partition est une vraie table. Le parent ne conserve pas une seule ligne —mesuré : 0 octet, les données étant réparties entre les enfants—.

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;

Il existe trois formes : RANGE (dates, montants), LIST (pays, statut) et HASH (répartir pour répartir).

L'élagage est ce qu'on vient chercher
Mesuré sur 300 000 lignes réparties sur trois ans : une requête avec WHERE creado BETWEEN '2024-03-01' AND '2024-03-31' n'a parcouru que pt_2024. La même table, filtrée sur une colonne qui n'est pas la clé, a fait un Parallel Append sur toutes.

D'où la règle qui décide la conception : la clé de partition est la colonne sur laquelle vous filtrez presque toujours. Si les requêtes ne la mentionnent pas, le partitionnement n'économise pas de lecture : il la répartit.

Ce qui n'existe pas : les index globaux
Un index créé sur le parent en crée un par partition —mesuré : quatre partitions, quatre index—. Il n'existe pas d'index unique couvrant toute la table, d'où la limite à connaître avant de concevoir :

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

Une UNIQUE sur (id) seul est refusée avec 0A000 unique constraint on partitioned table must include all partitioning columns. L'unicité globale d'un identifiant ne peut pas être garantie par le partitionnement déclaratif ; on l'obtient d'une séquence, unique par construction et non par contrainte.

ATTACH : la différence entre 68 ms et une demi-milliseconde
Rattacher une table existante oblige le serveur à vérifier que toutes ses lignes entrent dans l'intervalle. Mesuré sur 300 000 lignes :

Autrement dit : si la table porte déjà une contrainte CHECK qui implique l'intervalle, le serveur saute le parcours. Sur une table d'un milliard de lignes, c'est la différence entre un ACCESS EXCLUSIVE d'un instant et une demi-heure.

La partition par défaut n'est pas gratuite
DEFAULT recueille ce qui ne tombe dans aucun intervalle et évite l'erreur à l'insertion d'une date inattendue. En échange, mesuré : ALTER TABLE … DETACH PARTITION … CONCURRENTLY a répondu 55000 cannot detach partitions concurrently when a default partition exists. Et de plus, chaque nouvel ATTACH doit parcourir la partition par défaut pour vérifier qu'elle ne cache pas des lignes de l'intervalle qui arrive.

Pourquoi on partitionne vraiment
Pas pour la vitesse des requêtes —c'est le rôle des index—, mais pour la maintenance :
- Retirer une période entière, c'est un DROP TABLE de sa partition : mesuré, 2,3 ms, et l'espace revient au système de fichiers. Le DELETE équivalent a pris plus de temps et, surtout, laisse des lignes mortes que VACUUM devra nettoyer et de l'espace qui ne revient pas.
- VACUUM et ANALYZE travaillent par partition : le travail de maintenance cesse de croître avec tout l'historique.
- Les vieilles données se détachent et s'archivent sans toucher à la table vivante.

Recommandation
Partitionnez selon ce que vous allez supprimer, pas selon ce que vous allez interroger ; et vérifiez que vos requêtes portent la clé dans le WHERE en regardant le plan, pas en le supposant. Avant de partitionner une table existante, demandez-vous si ce qui manque n'est pas un index : le partitionnement ajoute des pièces mobiles en échange d'une maintenance moins chère, et ce marché n'est rentable qu'à partir d'une certaine taille.

Mots-clés : partitionnement, partition, range, list, hash, élagage, pruning, attach, detach, default, index global, unique, 0a000, drop partition, maintenance

Pourquoi le piège de l'utf8 n'existe pas ici, en quoi encodage et collation diffèrent, comment l'ordre change avec chacun, et pourquoi un LIKE par préfixe n'utilise pas votre index.

S'applique à : PostgreSQL 13+

Le piège qui a coûté tant de migrations MySQL n'existe pas ici : il n'y a pas d'utf8 qui ne soit pas de l'UTF-8. L'encodage se déclare à la création de la base, et UTF8 est tout l'UTF-8.

Mesuré, en stockant quatre chaînes dans une colonne text ordinaire :

Rien de spécial à déclarer. length() compte les caractères et octet_length() les octets, la distinction qu'il fallait poursuivre type par type dans MySQL.

Encodage et collation sont deux choses différentes
- Encodage — comment les octets sont stockés. Il appartient à la base, se fixe à la création et ne se change pas ensuite : pour le changer, il faut vider et recréer.
- Collation — comment on trie et compare. Elle se fixe par base, par colonne, par expression et même dans un ORDER BY.

Sur le serveur de test : server_encoding = UTF8, et les bases en en_US.utf8 du fournisseur libc. Il y a 815 collations disponibles, de deux fournisseurs : celles du système (C, POSIX, en_US.utf8) et celles d'ICU (es-ES-x-icu, unicode), qui depuis PostgreSQL 15 peuvent même être le fournisseur par défaut d'une base.

Ce qui change avec la collation

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

Mesuré, les résultats ne se ressemblent pas :

- C — A, B, a, b, á. Elle trie par le numéro du caractère : toutes les majuscules avant les minuscules, et les accents à la fin.
- es-ES-x-icu — a, A, á, b, B. Elle trie comme un dictionnaire.

Et la comparaison change avec elle : 'a' < 'B' est faux avec C et vrai avec la collation espagnole. Une liste « mal triée » n'est presque jamais un bug de l'application : c'est la collation de la colonne.

La collation décide si un index sert pour LIKE
C'est le détail pratique le plus difficile à découvrir seul. Avec une collation linguistique —celle de la base—, un index B-tree ordinaire ne sert pas aux recherches par préfixe. Mesuré sur 200 000 lignes :

- WHERE s = 'usuario42' → Index Only Scan.
- WHERE s LIKE 'usuario42%' → Seq Scan, l'index étant pourtant là.
- Après création de l'index avec la bonne classe d'opérateurs, la même requête est passée à Bitmap Index Scan.

CREATE INDEX idx_prefijo ON ch_like (s text_pattern_ops);

text_pattern_ops compare octet par octet, ce dont a précisément besoin LIKE 'quelque%'. Avec la collation C sur la colonne, ce n'est pas nécessaire : elle compare déjà ainsi.

Les collations ont une version, et cela compte
La base enregistre la version de la collation avec laquelle ses index ont été construits —mesuré : datcollversion = 2.36, celle de la bibliothèque système—. Si le système d'exploitation est mis à jour et que cette version change, l'ordre peut changer, et un index construit avec l'ordre précédent cesse d'être correct : des recherches qui ne trouvent pas des lignes pourtant présentes. PostgreSQL signale la discordance, et la réponse est REINDEX.

C'est pourquoi beaucoup choisissent la collation C ou ICU pour les bases qui doivent survivre aux mises à jour système : ICU porte sa propre version et ne dépend pas de celle du système.

Recommandation
UTF8 toujours. La collation se décide à la création de la base, car la changer ensuite coûte cher : C pour les colonnes qui sont des codes, des identifiants ou des chemins —elle trie vite et va bien avec LIKE—, et une collation linguistique pour ce que lit une personne. Et si une requête LIKE 'x%' n'utilise pas l'index, regardez la collation avant de toucher à la requête.

Mots-clés : encodage, encoding, utf8, collation, collate, icu, libc, text_pattern_ops, like, préfixe, order by, datcollversion, reindex, octet_length

Celles qui mordent vraiment —63 octets de nom, 1 600 colonnes, 32 par index—, pourquoi celle de l'identifiant donne un avertissement et non une erreur, et ce que fait TOAST d'une valeur qui n'entre pas dans la page.

S'applique à : PostgreSQL 13+

Les limites de PostgreSQL ne ressemblent pas à celles d'InnoDB, et celles qui mordent au quotidien ne sont pas les grandes.

Celles qu'on touche vraiment

Les trois premières sont mesurées : 1 600 colonnes ont été créées sans problème et 1 601 ont échoué ; un index de 32 colonnes a été créé, celui de 33 non.

Celle de l'identifiant est la seule qui ne donne pas d'erreur
Un nom de 72 caractères a été enregistré en 63, et le serveur l'a dit par un avertissement :

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

Un avertissement n'est pas une erreur : l'instruction a continué. C'est pourquoi deux noms longs qui ne diffèrent qu'à partir du 64e caractère finissent par être le même objet, et la panne se voit bien plus tard. Ce sont les générateurs de noms —index, contraintes, tables temporaires par lot— qui butent là-dessus, pas la main de quelqu'un.

Les grandes, qui ne sont presque jamais le problème
Elles ne sont pas mesurées ici —il faudrait remplir un disque— et viennent avec leur chiffre officiel :

- Taille maximale d'une table : 32 To.
- Taille maximale d'un champ : 1 Go.
- Taille maximale d'une ligne : 1,6 To.
- Lignes par table : aucune limite définie.
- Bases par cluster et tables par base : pas de limite pratique.

Ce qui s'épuise bien avant l'une d'elles, c'est la maintenance : VACUUM, sauvegardes et reconstruction d'index sur une table de plusieurs téraoctets.

TOAST : pourquoi un text d'1 Go ne casse pas la page de 8 ko
Une ligne doit tenir dans une page, et une page fait 8 ko. Les valeurs volumineuses sont compressées et déportées dans une table latérale —ce qu'on appelle TOAST—, automatiquement et sans rien déclarer.

Mesuré, en stockant 100 000 octets de texte dans une colonne text :

- octet_length — 100 000 octets de donnée.
- pg_column_size — 1 156 octets : il y a donc eu compression.
- La table occupait 8 192 octets et 16 ko en comptant son TOAST.

D'où une conséquence pratique : un SELECT * sur une table à colonnes volumineuses paie la lecture de ces colonnes même si personne ne les regarde. Ne demander que les colonnes nécessaires n'est pas une question de style, c'est de l'E/S.

Les limites qui, elles, se configurent
Elles sont de l'instance et non du moteur, et c'est pourquoi elles apparaissent dans pg_settings : max_connections (100 d'usine), max_locks_per_transaction (64), max_wal_size, work_mem. Ce sont celles qui s'épuisent sur un vrai serveur ; celles du tableau ci-dessus, presque jamais.

Recommandation
N'en surveiller que deux : celle des 63 octets dès que quelque chose génère des noms, et celle des colonnes par index dès que quelqu'un propose un index composite avec la moitié du schéma dedans. Pour le reste, on apprend par pg_settings, pas par la documentation.

Mots-clés : limites, identifiant, 63 octets, tronqué, 1600 colonnes, 32 colonnes, index, toast, page, 8 ko, 32 to, 1 go, pg_settings, max_connections

Quand mettre les données dans le document et quand les référencer : les quatre motifs de MongoDB, l'antimotif du tableau sans plafond et ce que coûte chacun, mesuré.

S'applique à : MongoDB 7.0+

En SQL le schéma découle de la normalisation : chaque fait à un seul endroit, et les requêtes le recomposent avec JOIN. Dans MongoDB il découle du motif d'accès : ce qui se lit ensemble se range ensemble. La question n'est plus « comment éviter de répéter une donnée ? », mais « que veux-je qu'une seule lecture me rende ? ».

Imbriquer ou référencer

Une commande peut porter ses lignes à l'intérieur, ou les lignes peuvent vivre dans leur propre collection en pointant vers la commande.

// 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" } } ])

Mesuré contre MongoDB 8.2 avec 100 000 commandes de trois lignes : lire une commande avec ses lignes coûte 0,25 ms en imbriqué et 0,30 ms avec $lookup —mais 67 ms s'il manque l'index sur l_ref.pedido, car alors chaque commande parcourt les 300 000 lignes—. Sur le disque, les commandes imbriquées occupent 2,9 Mo contre 1,1 Mo + 5,2 Mo des deux collections séparées : ici imbriquer est aussi sorti moins cher en espace.

Imbriquer ne renonce pas aux index : un index sur un champ du tableau est multiclé et sert pareil. Chercher {"lineas.sku": "A-7"} sans lui est un COLLSCAN de 100 000 documents en 42 ms ; avec lui, 2 000 examinés en 2 ms, pour les mêmes 2 000 lignes. Et un document se modifie de façon atomique sans transaction : le $inc d'un compteur et le $set d'un état dans un seul updateOne passent ensemble ou pas du tout.

L'antimotif : le tableau qui n'arrête pas de grandir

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

Le document vide fait 29 octets et chaque relevé ajoute 28,9. À 540 000 il mesure 16 628 919 octets et la fournée suivante échoue avec le code 10334 : « Resulting document after update is larger than 16777216 ». Le plafond de 16 Mo par document ne se négocie pas. Et cela fait mal avant d'y arriver : un $set d'un champ scalaire coûte 3,15 ms sur ce document et 0,50 ms sur un de 228 octets, et un $pop du tableau 71,95 ms contre les 0,15 ms qu'il faut pour insérer un relevé isolé. Un tableau qui grandit sans plafond connu est une référence mal placée.

Seau : les relevés se regroupent dans des documents de N.

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

Les mêmes 540 000 relevés : isolés, ce sont 540 000 documents, 6,5 Mo de données et 8,4 Mo d'index ; en seaux de 200, ce sont 2 700 documents, 4,2 Mo de données et 82 Ko d'index. Les insérer coûte 203 ms en seaux contre 901 ms isolés. Lire le dernier : 0,20 ms depuis le seau, 0,30 ms isolé et 46,80 ms depuis le tableau imbriqué, qu'il faut ramener en entier.

Référence étendue : copier dans l'enfant la poignée de champs du parent qui s'affichent toujours. Lister 100 commandes avec le nom de leur client coûte 0,35 ms avec le nom dupliqué à l'intérieur et 1,00 ms avec $lookup. Cela se paie à l'écriture : renommer un client oblige à toucher ses 1 000 commandes, 2 ms avec un index sur cliente. On duplique ce qui ne change presque jamais.

Valeur calculée : garder le total déjà sommé au lieu de le recalculer à chaque lecture. Sur une commande cela ne se voit pas —0,35 ms lu dans le document, 0,30 ms sommé à la volée— ; en agrégeant les 100 000, si : 14 ms en lisant le champ contre 129 ms en recalculant.

Sous-ensemble : dans le document seulement les quelques lignes qui s'affichent, le reste dans sa propre collection. 500 produits avec 500 avis chacun : toutes imbriquées, le document moyen fait 80 738 octets ; avec les cinq derniers et un compteur, 889 octets, et la collection tombe de 6,1 Mo à 60 Ko. La fiche passe de 0,55 ms à 0,25 ms, et la page de 20 avis se demande à part, à sa propre collection.

La règle, en une ligne : imbrique ce qui se lit avec son parent, n'appartient qu'à lui et a un plafond ; référence ce qui grandit sans limite, se partage entre plusieurs parents ou se consulte pour soi-même.

Mots-clés : modélisation, imbrication, référence, document, motif, seau, sous-ensemble, référence étendue, valeur calculée, tableau, schéma, 16 Mo

Quel type MongoDB stocke avec chaque valeur, ce qu'il pèse, comment des types différents se comparent entre eux et comment se trie un texte accentué.

S'applique à : MongoDB 7.0+

En SQL, c'est la table qui déclare le type et toutes les lignes s'y tiennent. Dans MongoDB, le type voyage avec chaque valeur : il n'y a pas de CREATE TABLE, et deux documents d'une même collection peuvent porter un entier et une chaîne dans le même champ. Le format s'appelle BSON : du JSON en binaire, avec les types qui lui manquent — entiers 32 et 64 bits, décimaux exacts, dates, binaires et ObjectId.

_id et ObjectId

Tout document a un _id : unique, immuable et indexé dès la naissance de la collection. Si tu ne l'écris pas, le client y met un ObjectId : 12 octets, dont 4 pour le temps en secondes, 5 aléatoires par processus et 3 de compteur. Il croît avec l'horloge : il sert donc pour des plages de dates sans stocker aucune date.

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

Le garder en chaîne coûte et n'apporte rien : {_id: ObjectId()} pèse 22 octets et la même valeur en chaîne, 39.

Entiers, flottants et décimaux

Quatre types numériques, et la différence se voit dans le document : $bsonSize sur {_id: 1, a: …} donne 21 octets avec int, 25 avec long ou double et 33 avec decimal. L'argent va en decimal pour la même raison qu'en SQL il va en DECIMAL : additionner 0,1 et 0,2 en flottants donne 0.30000000000000004, et en décimaux, 0.3.

Le piège est du côté client. mongosh choisit le type en regardant la valeur, donc un 7 écrit à la main est stocké en int, 3000000000 en double, et 9007199254740993 est stocké comme 9007199254740992 : au-delà de 2⁵³, il faut écrire 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" } } } ])

Pour interroger, en revanche, les quatre ne font qu'un seul nombre : avec un int, un long, un double et un decimal valant 7 dans la collection, {v: 7} les trouve tous les quatre, et {v: "7"} n'en trouve aucun. $type les distingue, lui, et "number" les regroupe de nouveau.

Dates

Date, ce sont 8 octets de millisecondes depuis 1970, toujours en UTC et sans fuseau horaire. Le couple DATETIME / TIMESTAMP n'existe pas ici : un seul type, le fuseau est appliqué par celui qui lit, et les microsecondes se perdent à l'écriture. Le Timestamp de BSON n'est pas pour tes données : c'est l'horloge interne de la réplication.

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

Binaires et UUID

BinData stocke des octets avec un sous-type, et UUID() est le sous-type 4. Un UUID en BinData occupe 29 octets de document ; le même écrit en chaîne avec tirets, 49.

L'ordre entre types différents

Un sort sur un champ aux types mélangés n'échoue pas : il existe un ordre total entre les types, et mesuré sur quatorze documents, le voici.

MinKey → champ absent et null → nombres → chaînes → objets → binaires → ObjectId → booléens → dates → Timestamp → expressions régulières → MaxKey.

Les tableaux n'y sont pas parce qu'un tableau se compare par son plus petit élément : [9, 10] se range parmi les nombres. Et un champ absent se trie exactement comme null, au point que {v: null} trouve les deux ; pour les séparer, {v: {$type: "null"}} et {v: {$exists: false}}.

UTF-8 et collation

Il n'y a aucun jeu de caractères à choisir. Les chaînes BSON sont en UTF-8, point : "café ☕ 日本語 👩‍💻" fait 14 caractères et 31 octets, et revient tel quel.

Ce qui se choisit, c'est la collation, binaire par défaut : sans collation, cafe et café sont deux valeurs différentes, et Árbol se trie après zorro. Une collation avec son locale et son strength — 1 ignore accents et casse, 2 ignore la casse seulement, 3 distingue tout — change à la fois la comparaison et l'ordre.

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

Et voici le même piège qu'en SQL : la collation de la requête doit être celle de l'index. Avec un index ordinaire sur n, la requête {n: "cafe"} est un IXSCAN qui examine 1 document ; la même requête avec collation retombe en COLLSCAN et en examine 7. Le remède n'est pas d'écrire collation dans chaque requête, mais de la donner à la collection : ses index naissent alors avec elle.

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

Deux avertissements de plus, mesurés. $regex ignore la collation : /^CAF/ ne trouve pas cafe, même avec strength: 1, alors que {n: "CAFE"} le trouve. Et numericOrdering: true fait trier les chaînes "1", "2" et "10" comme des nombres au lieu de "1", "10", "2".

Mots-clés : bson, types, objectid, decimal128, numberlong, date, uuid, bindata, collation, utf-8, accents, tri

Les onze types d'index de MongoDB et quand chacun convient, la règle ESR pour ordonner un composé, la requête couverte, les plafonds et le coût en écriture, le tout mesuré avec `explain`.

S'applique à : MongoDB 7.0+

Un index est un arbre B sur la valeur d'un champ, et le nombre qui dit s'il sert vient d'explain("executionStats") : totalDocsExamined face à nReturned. Si le premier dépasse largement le second, le serveur lit des documents pour les jeter.

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 règle ESR

Un index composé se parcourt par préfixes : l'ordre de ses champs est donc la décision. D'abord les champs d'égalité (E), ensuite celui du tri (S), et enfin celui de l'intervalle (R). Avec l'intervalle avant le tri, l'index filtre mais n'ordonne pas, et une étape SORT apparaît qui trie en mémoire : 1 826 documents dans la requête ci-dessous.

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

Cette étape a un plafond : internalQueryMaxBlockingSortMemoryUsageBytes vaut 104 857 600 octets, et au-delà la requête échoue si le disque ne lui est pas permis.

Requête couverte

Si l'index porte tous les champs que la requête lit, le serveur ne touche pas aux documents. Attention à _id : il entre dans la projection par défaut, il n'est pas dans l'index, et il faut le retirer à la main.

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

Les autres types

Les deux qui perdent des données en silence se voient ensemble :

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 }

Le caché est l'inverse : il reste maintenu, mais le planificateur ne le regarde pas. Avec hidden: true, la même requête était un COLLSCAN sur 20 000 documents ; rendue visible par collMod, un IXSCAN sur 1 940.

Les plafonds

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

Le nom d'un index et la taille d'une clé n'ont pas de plafond pratique : 400 caractères et 2 000 octets ont été acceptés sans broncher.

Le prix

Chaque index se paie à chaque écriture. Les mêmes 20 000 insertions ont pris 60 ms sans index, 101 ms avec cinq et 160 ms avec dix. Et ils occupent : les six index de pedidos totalisent 1,9 Mo contre 916 Ko de données.

D'où l'on n'indexe pas tout. Un champ de faible cardinalité aide rarement : estado, avec quatre valeurs, examine 5 000 documents pour en rendre 5 000. Et un index simple est de trop si un composé commence déjà par lui : { cliente: 1 } et { cliente: 1, fecha: 1 } examinent les mêmes 40.

Mots-clés : index, ESR, composé, multiclé, texte, 2dsphere, hashed, joker, TTL, partiel, sparse, unique, caché, requête couverte, explain, IXSCAN, COLLSCAN, totalDocsExamined, MongoDB

Les étapes du pipeline et dans quel ordre les placer, $lookup comme JOIN et ce qu'il coûte sans index, $graphLookup, les fonctions de fenêtrage, $facet et $unionWith, et $merge face à $out.

S'applique à : MongoDB 7.0+

Un pipeline est une liste d'étapes, et chacune reçoit les documents produits par la précédente. L'ordre, c'est toi qui l'écris, et presque toute la performance est là.

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

Filtrer d'abord, toujours

Seule la première étape peut utiliser un index. Avec un index {estado: 1} sur ces 20 000 commandes, le $match placé devant examine 5 000 clés et 5 000 documents ; le même filtre derrière le $group, aucune clé et 20 000 documents.

$lookup est le JOIN, et sans index il se paie

Il joint une autre collection de la même base, et ce qu'il trouve arrive dans un tableau que l'on ouvre presque toujours avec $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 de l'étape ne laisse aucune marge : sans index sur clientes.num, il fait un parcours complet par document d'entrée, et avec l'index il n'en fait aucun.

Le récursif et celui de fenêtrage

$graphLookup suit une hiérarchie aussi loin qu'elle va, et depthField note à quelle distance s'est arrêté chaque échelon. Le tableau renvoyé n'est pas trié : ici il est sorti Ana, Caro et Beto avec les niveaux 2, 0 et 1. $setWindowFields (5.0+) est la fenêtre de SQL : partitionBy est le PARTITION BY et 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

Plusieurs réponses en un seul passage

$facet exécute des sous-pipelines sur la même entrée et renvoie un seul document avec toutes ; à l'intérieur, plus aucun index ne compte. $unionWith est 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 }

Écrire le résultat

$merge fusionne dans la collection cible et $out la remplace entièrement.

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

Les plafonds

Chaque étape bloquante — $group, $sort, $facet, l'intermédiaire de $lookup — dispose de 104 857 600 octets, et depuis la 6.0 le débordement part sur le disque tout seul. Mais le tableau d'un accumulateur ne déborde pas : un $push sur de gros documents échoue avec 146 ExceededMemoryLimit, et allowDiskUse: true ne le sauve pas.

Mots-clés : agrégation, pipeline, étape, match, group, project, lookup, jointure, graphLookup, setWindowFields, fenêtre, facet, unionWith, merge, out, unwind, explain, allowDiskUse

Un document s'écrit en entier sans transaction ; pour plusieurs il en faut une, et un replica set. La session, readConcern et writeConcern, le WriteConflict 112 et les trois plafonds mesurés.

S'applique à : MongoDB 7.0+

Dans MongoDB, un document s'écrit en entier ou pas du tout, et cela vaut même si l'updateOne touche dix champs et un tableau imbriqué. Pour changer plus d'un document à la fois il faut une transaction, et une transaction exige un replica set : sur un nœud isolé elle est refusée, et le message ne parle même pas de transactions — il dit que le déploiement n'accepte pas les écritures réessayables.

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

Une transaction se joue sur une session

Tout ce qui est dedans passe par l'objet de la session : le db.cuentas de l'extérieur n'est pas dans la transaction, quel que soit son nom. Jusqu'au commitTransaction(), ce qui a été écrit n'est visible que de l'intérieur.

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() défait tout, et il n'y a pas à le demander : si la session disparaît ou si le serveur redémarre, la transaction meurt abandonnée.

readConcern et writeConcern sont deux questions différentes

readConcern dit ce qui est lu : local c'est ce qu'il y a ici, majority ce qui ne peut plus être perdu, snapshot une image cohérente d'un instant. writeConcern dit quand c'est écrit : w: 1 c'est le primaire, w: "majority" la majorité de l'ensemble, et j: true ajoute le journal. Les valeurs d'usine sortent de getDefaultRWConcern : lecture local, écriture majority.

Le conflit d'écriture

Deux transactions sur le même document n'attendent pas : la seconde échoue sur-le-champ avec 112 WriteConflict, et le message le dit sans détour. Réessayer fait partie du marché, et c'est pourquoi les pilotes fournissent withTransaction, qui réessaie tout seul.

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

Une écriture venue de l'extérieur de la transaction ne reçoit pas le 112 : elle attend. Dans la mesure, elle a attendu 76 s, jusqu'à ce que la limite de vie abandonne la transaction qui tenait le document.

Les plafonds

Une transaction vit 60 s, et passé cette ligne le commit renvoie 251 NoSuchTransaction avec « has been aborted ». À l'intérieur, chaque demande de verrou n'attend que 5 ms : une transaction ne reste pas accrochée à un verrou, elle préfère échouer. Et un writeConcern que l'ensemble ne peut pas satisfaire échoue avant même d'essayer.

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 règle pratique : si deux documents doivent changer ensemble plusieurs fois par jour, c'est presque toujours le modèle qui est mauvais, et il fallait les imbriquer. La transaction est la sortie pour ce qui vraiment ne tient pas dans un seul document.

Mots-clés : transaction, transactions, session, startTransaction, commit, abandon, WriteConflict, 112, 251, readConcern, writeConcern, majority, snapshot, replica set, atomicité, verrou

Les trois niveaux de détail d'explain et ce qu'ajoute chacun, le cache de plans et quand un plan est désactivé, l'ensemble de travail dans le cache WiredTiger, et les trois niveaux du profileur.

S'applique à : MongoDB 7.0+

Avant de toucher à quoi que ce soit, on mesure, et l'outil s'appelle explain. Il a trois niveaux de détail, chacun plus coûteux que le précédent : queryPlanner se contente de planifier — il n'exécute jamais — et montre le plan gagnant et les rejetés ; executionStats exécute le gagnant et ajoute ce qu'il a coûté ; allPlansExecution ajoute en plus ce qu'a coûté chaque candidat pendant la période d'essai, et c'est là que l'on voit pourquoi le gagnant a gagné.

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

Les trois nombres qui comptent sont nReturned, totalDocsExamined et totalKeysExamined. Si le second est bien plus grand que le premier, le serveur lit des documents pour les jeter.

Le cache de plans

Le planificateur ne décide pas à nouveau à chaque requête. La première fois il essaie les candidats, range le gagnant sous un planCacheKey et le réutilise ensuite ; dans l'explain cela se voit comme isCached: true. L'entrée conserve works, le travail qu'il a coûté, et si une exécution ultérieure en dépense dix fois plus, le plan est désactivé et la compétition reprend. Créer ou supprimer un index vide aussi le 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

L'ensemble de travail et le cache WiredTiger

MongoDB ne garde pas de résultats : ce qu'il garde, ce sont des pages, dans le cache WiredTiger, et la performance dépend de ce que l'ensemble de travail — les données et les index réellement touchés — y tienne. Par défaut ce cache prend la moitié de la RAM moins 1 Go. Dans la mesure, sur 486 864 pages demandées, seules 261 ont dû être ramenées du disque : une sur 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

Le profileur

Trois niveaux : 0 éteint, 1 seulement ce qui dépasse slowms, et 2 tout. Deux choses mesurées qui surprennent : setProfilingLevel renvoie le niveau d'avant, pas celui qu'il vient de poser — le nouveau, il faut le relire — et system.profile est une collection plafonnée de 1 Mio, elle ne grandit donc pas : elle se mord la queue.

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

Chaque entrée apporte planSummary, docsExamined, nreturned et millis, c'est-à-dire exactement ce qu'il faut pour décider si un index vaut la peine. Calíope lit cette collection dans son outil de profilage. Le niveau 2 coûte cher en production : on l'allume un moment et on le rabaisse, on ne le laisse pas en place.

Mots-clés : performance, explain, queryPlanner, executionStats, allPlansExecution, cache de plans, planCacheKey, isCached, WiredTiger, ensemble de travail, profileur, system.profile, slowms

Ce qui tourne vraiment selon getCmdLineOpts, les cinq sections de mongod.conf qui comptent, et les trois sortes de paramètres : ceux qui changent à chaud, ceux du démarrage et ceux qui n'en sont pas.

S'applique à : MongoDB 7.0+

La première question sur un serveur qu'on ne connaît pas n'est pas ce que dit son fichier de configuration, mais avec quoi il tourne réellement. getCmdLineOpts répond aux deux à la fois : argv est ce qu'on lui a passé en ligne de commande, et parsed la même chose, déjà traduite dans le vocabulaire du fichier.

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

Les cinq sections qui comptent

storage dit où sont les données et combien de mémoire prend le cache ; net, sur quelles adresses il écoute ; security, s'il faut s'authentifier ; operationProfiling, ce qui est noté du trafic lent ; et replication, à quel ensemble il appartient. Le fichier est du YAML, donc l'indentation est de la syntaxe.

# 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

Les paramètres sont de trois sortes

Ceux qui se changent à chaud avec setParameter, ceux qui ne sont lus qu'au démarrage, et ceux qui ne sont pas des paramètres, quelle que soit leur apparence. On les distingue à la réponse du serveur : la première renvoie was avec la valeur précédente — pas la nouvelle, il faut donc la relire ; la deuxième donne 20 IllegalOperation ; et port, qui est une option de démarrage et non un paramètre, donne 72 InvalidOptions avec « 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 ne reste pas

Un setParameter vit jusqu'au redémarrage suivant et pas une seconde de plus : avec cursorTimeoutMillis mis à 300 000, le serveur est remonté à 600 000. Pour qu'il reste, il faut l'écrire dans la section setParameter: du fichier, celle du bas de l'exemple.

Le cache et la version de compatibilité

Le cache WiredTiger est la première chose qu'on veut toucher, et presque toujours celle qu'il ne faut pas : par défaut il prend la moitié de ce qui reste de la RAM après avoir mis 1 Go de côté. Sur le nœud mesuré, 7 933 Mo de RAM ont donné 3 621 781 504 octets de cache. Et il y a une sixième chose qui n'est pas dans le fichier : la featureCompatibilityVersion, qui décide quelles fonctions du binaire sont allumées — on la monte à la main après une mise à jour, et on la baisse avant de revenir en arrière.

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

Mots-clés : configuration, mongod.conf, getCmdLineOpts, setParameter, cacheSizeGB, WiredTiger, bindIp, authorization, operationProfiling, replSetName, featureCompatibilityVersion, FCV

Un utilisateur vit dans une base et c'est son nom de famille, les rôles intégrés ne sont pas les mêmes dans admin qu'ailleurs, le 13 que reçoit une écriture sans droit, et la seule porte que laisse ouverte un serveur fraîchement sécurisé.

S'applique à : MongoDB 7.0+

Sans security.authorization: enabled, rien de tout cela n'existe : le serveur accepte quiconque se présente, et avec bindIp: "*" n'importe qui le peut. Une fois allumé, la première question est qui suis-je.

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

Le mécanisme par défaut est SCRAM-SHA-256, et le serveur conserve les deux versions : le mot de passe de l'utilisateur mesuré porte 15 000 itérations en SHA-256 et 10 000 en SHA-1, avec un sel de 40 caractères. Le mot de passe ne voyage pas, même pas chiffré : SCRAM prouve qu'on le connaît sans le dire. Le troisième mécanisme, MONGODB-X509, échange le mot de passe contre un certificat client, et alors le nom de l'utilisateur est le sujet du certificat.

Un utilisateur vit dans une base, et c'est là son autre moitié

lector n'est pas un utilisateur : lector de ventas en est un. La base où il a été créé est sa base d'authentification, et il faut la nommer en se connectant (--authenticationDatabase). Avec la mauvaise, le serveur ne dit pas que l'utilisateur existe ailleurs : il dit « Authentication failed », point.

Les rôles ne sont pas les mêmes dans toutes les bases

Une base normale a six rôles intégrés. admin en a vingt et un, car c'est là que vivent ceux qui atteignent tout le serveur — les …AnyDatabase, root, backup, restore, ceux du cluster. Un rôle est une liste d'actions : read en compte onze, et find n'en est qu'une.

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

Ce qui se passe quand un droit manque

Il n'y a ni réponse vide ni ligne absente : il y a un 13 Unauthorized, et le message nomme la base, la commande et jusqu'à la collection. C'est une erreur dont on peut tirer la règle qui manque.

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'exception localhost

Un serveur avec --auth et sans un seul utilisateur laisse à qui arrive depuis la machine elle-même faire exactement une chose : créer le premier. Lire, non ; créer le second, non plus. C'est la rampe pour démarrer, et elle se referme d'elle-même dès qu'un utilisateur existe.

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

Mots-clés : sécurité, authentification, SCRAM, SCRAM-SHA-256, x.509, TLS, utilisateur, rôle, rôles intégrés, read, readWrite, dbAdmin, userAdmin, root, 13, Unauthorized, localhost, authSource

Ce qu'il y a dans un mongodump et ce qu'il n'y a pas, combien de temps prend la restauration et pourquoi, à quoi sert --oplog, combien dure vraiment la fenêtre de l'oplog, et les deux choses qu'un dump ne garantit pas.

S'applique à : MongoDB 7.0+

mongodump est une sauvegarde logique : il se connecte comme n'importe quel client, lit les documents et les écrit en BSON. Cela a deux conséquences visibles dans les chiffres. La première : le fichier fait la taille des documents, pas celle qu'ils occupent sur le disque — 2 420 000 octets de .bson pour une collection compressée sur le disque. La seconde : il rivalise avec le travail normal du serveur pour le cache, donc une sauvegarde d'une grosse base se sent.

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

Des index, seule la définition voyage, dans le .metadata.json. C'est pourquoi restaurer coûte bien plus que sauvegarder — 91 ms contre 26 dans cette mesure : le temps part dans leur reconstruction, et sur une vraie collection c'est presque toute l'attente.

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 laisse un seul fichier au lieu d'une arborescence, et avec --gzip il est passé de 2 420 000 à 133 572 octets. Les deux peuvent transiter par un tube, et c'est ainsi qu'on copie une base d'une machine à l'autre sans jamais toucher un disque intermédiaire.

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

L'oplog est ce qui fait d'une sauvegarde un instant

Une sauvegarde prend du temps, et pendant ce temps la base continue de changer : ce qui a été écrit dans la collection A avant de la sauvegarder et dans B après ne concorde pas. --oplog enregistre en plus les opérations survenues pendant la sauvegarde, et mongorestore --oplogReplay les applique à la fin, de sorte que ce qui est restauré est l'état d'un instant, celui de la fin de la sauvegarde. Cela ne marche que contre un replica set, car l'oplog est le sien.

// 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 est une collection plafonnée, donc sa fenêtre ne se mesure pas en octets mais en temps, et ce temps dépend de ce qu'on écrit. Sur le nœud mesuré, 42 Gio de plafond donnaient une fenêtre du 8 août au 12 septembre ; avec dix fois la charge ce serait trois jours. C'est le nombre à regarder avant de partir en week-end.

Ce qu'une sauvegarde ne couvre pas

Deux choses. Une base de centaines de gigaoctets ne se sauvegarde pas en la lisant document par document : là on passe aux instantanés du système de fichiers, à prendre avec le journal inclus ou avec la base verrouillée par fsyncLock. Et dans un cluster fragmenté, un mongodump contre le routeur ne donne pas un instant commun aux fragments : il faut arrêter l'équilibreur et prendre un instantané par fragment plus un des serveurs de configuration.

Mots-clés : sauvegarde, mongodump, mongorestore, oplog, oplogReplay, archive, gzip, instantané, snapshot, fsyncLock, restauration, fenêtre de restauration, BSON

Le schéma, c'est ce que portent les documents, donc le changer c'est les écrire. Le validateur avec $jsonSchema, le 121 et son errInfo, les quatre combinaisons de validationLevel et validationAction, et la migration par version de document.

S'applique à : MongoDB 7.0+

Il n'y a pas d'ALTER TABLE parce qu'il n'y a pas de table : le schéma d'une collection, c'est littéralement ce que portent ses documents. Ajouter un champ aux nouveaux ne coûte rien et ne change pas les anciens, et c'est là le piège : qui lit doit s'arranger des deux formes jusqu'à ce que quelqu'un nivelle le passé.

Le validateur est une porte, pas un schéma

Ce qui existe, c'est un validator avec $jsonSchema : une condition vérifiée à l'écriture, jamais à la lecture et jamais en arrière. Il rejette avec 121 DocumentValidationFailure, et le bon se trouve dans errInfo.details, qui nomme la règle enfreinte au lieu de dire « non valide ».

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

Attention aux types : mongosh range un nombre entier en int, donc edad: 30 passe un bsonType: "int" et edad: 30.5 l'enfreint, parce que celui-là est bien un double.

validationLevel et validationAction sont deux boutons différents

Le niveau dit quels documents sont atteints : strict tous, moderate seulement ceux qui étaient déjà valides — c'est ainsi qu'on pose un validateur sur une collection pleine de vieilleries sans bloquer ses mises à jour. L'action dit ce qui se passe en cas d'échec : error rejette, warn laisse écrire et le note dans le journal. Et poser le validateur avec collMod ne touche à rien de ce qui était déjà là.

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

Migrer, c'est écrire

Sans ALTER, l'équivalent d'une colonne nouvelle est un updateMany avec $set, et celui de la retirer, un $unset. Ils sont bon marché — 20 000 documents en 61 et 51 ms — mais ils ne sont pas atomiques : ils vont document par document, donc pendant la migration les deux formes cohabitent.

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

C'est pourquoi le motif qui tient est de garder la version dans chaque document (_v) : l'application sait lire les deux, la migration avance par lots ou au moment de toucher chaque document, et le jour où {_v: 1} ne renvoie plus rien, l'ancien code s'en va. C'est ce que fait aussi une migration SQL, sauf qu'ici l'état intermédiaire est visible et qu'il faut l'écrire.

Mots-clés : schéma, validation, validateur, jsonSchema, 121, DocumentValidationFailure, validationLevel, validationAction, strict, moderate, warn, collMod, migration, version de document

Les trois pièces d'un cluster fragmenté, pourquoi une clé hachée répartit et une clé monotone entasse, la différence mesurée entre une requête ciblée et une diffusée, les zones, et ce que coûte changer la clé.

S'applique à : MongoDB 7.0+

Fragmenter, c'est répartir une collection sur plusieurs machines, et cela demande trois pièces : les fragments, qui gardent les données et sont des replica sets ; les serveurs de configuration, qui gardent la carte de quel morceau est où ; et mongos, le routeur, qui ne garde rien et auquel l'application se connecte.

# 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 clé de fragmentation est la seule décision qui compte

Trois choses en découlent d'un coup : comment les données se répartissent, quelles requêtes peuvent viser un seul fragment, et s'il y a un point chaud. On demande de la cardinalité — beaucoup de valeurs distinctes —, une fréquence égale, pour qu'aucune valeur n'emporte la moitié, et qu'elle ne soit pas monotone, car une clé qui croît toujours envoie toutes les nouvelles écritures au même endroit.

Ce dernier point n'est pas de la théorie. Sur la même collection de 60 000 documents : une clé hashed sur le client a laissé 52,11 % sur un fragment et 47,88 % sur l'autre ; l'_id d'ObjectId, qui croît toujours, a laissé 100 % sur un seul, en un unique morceau.

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

Ciblée ou diffusée

Une requête qui porte la clé va à un fragment, point. Une qui ne la porte pas est posée à tous et les réponses sont fusionnées : SINGLE_SHARD contre SHARD_MERGE. La différence mesurée est de 121 documents examinés contre 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

Les zones, et changer de clé

Une zone attache une plage de la clé à un fragment, et cela sert à deux choses réelles : garder les données d'un pays sur des machines de ce pays, et séparer le chaud du froid. Et depuis la 5.0 la clé peut être changée avec reshardCollection, mais cela copie la collection entière et cela se voit : sur 60 000 documents, elle travaillait encore après deux minutes, sa collection temporaire bien en vue.

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

Trois choses qui ne sont plus vraies

On raconte encore qu'un updateOne sans la clé échoue, que la valeur de la clé ne peut pas changer, et que l'index doit exister avant de fragmenter. En 8.2, les trois sont passées sans broncher.

Mots-clés : fragmentation, sharding, clé de fragmentation, hachée, chunk, mongos, serveurs de configuration, zone, équilibreur, reshardCollection, SINGLE_SHARD, SHARD_MERGE

Les huit codes qui sortent tous les jours, provoqués un par un avec le texte que renvoie le serveur, et les deux qui portent plus que le numéro : le 11000 avec sa clé et le 121 avec son errInfo.

S'applique à : MongoDB 7.0+

Une erreur de MongoDB apporte un numéro, presque toujours un nom, et parfois quelque chose à l'intérieur qui vaut plus que les deux. Voici les huit qui sortent tous les jours, provoquées une par une contre le serveur et recopiées telles quelles.

Les deux sans nom portent mieux que ça

Le 11000 et le 121 arrivent sans codeName, et peu importe : tous deux portent le fait qu'il faut pour les corriger. Le 11000 nomme la collection, l'index et la valeur qui a heurté, il n'y a donc pas à deviner lequel de trois index uniques a sauté.

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

Le 121 dit « Document failed validation » et rien d'autre dans le message, mais e.errInfo.details porte la règle enfreinte avec son nom et sa valeur attendue. C'est la différence entre « non valide » et « il manque 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"] } ] }

Les deux erreurs de droits sont différentes

Le 18, c'est « je ne sais pas qui tu es » : mauvais mot de passe, ou — le plus souvent — la mauvaise base d'authentification, car un utilisateur MongoDB est le nom plus la base où il a été créé. Le 13, c'est « je sais qui tu es et tu ne peux pas » ; son message nomme la base, la commande et jusqu'à la collection, le rôle manquant s'y lit donc directement.

Et deux avertissements de syntaxe

Le 50 n'est pas une erreur du serveur mais le plafond que vous avez posé vous-même avec maxTimeMS. Et le 26 surgit là où on l'attend le moins : collMod et renameCollection sur quelque chose d'inexistant échouent, mais drop() sur une collection qui n'existe pas renvoie false et c'est tout — ce n'est pas une erreur, donc un script qui le tient pour acquis n'apprend jamais qu'il s'est trompé de nom.

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

Mots-clés : erreurs, codes, 11000, duplicate key, 13, Unauthorized, 18, AuthenticationFailed, 26, NamespaceNotFound, 50, MaxTimeMSExpired, 112, WriteConflict, 121, 251, NoSuchTransaction, errInfo

Les huit plafonds qu'on touche vraiment, provoqués un par un contre le serveur, avec le code que renvoie chacun et les trois qui ne sont pas là où leur réputation le dit.

S'applique à : MongoDB 7.0+

Tout ceci a été provoqué contre le serveur, donc le numéro de gauche est celui qui a vraiment refusé, pas celui de la légende.

Celui de 16 Mo est le seul qu'on touche par accident

Et presque toujours pour la même raison : un tableau qui grossit sans frein dans un document. Le message porte les deux nombres, celui du document et le maximum, on voit donc d'un coup d'œil de combien ça a débordé.

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

Les deux plafonds d'index se comptent mal

Les 64 index par collection incluent _id_, il en tient donc 63 à soi ; et ce n'est pas un plafond qu'on atteint en bonne santé : avec 64 index, chaque écriture entretient 64 arbres. Les 32 champs d'un index composé ne sont pas non plus un objectif : passé six ou sept, ce qu'il faut est presque sûrement deux index, pas un plus large.

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

Trois qui ne sont pas là où leur réputation le dit

L'imbrication est documentée à 100 niveaux et ce que le serveur a refusé, c'est le 180e, parce que le plafond appartient au BSON de la commande entière et que l'enveloppe de l'insert en prend une part. Le nom long n'échoue pas sur la collection mais sur la somme de la base et de la collection, c'est-à-dire l'espace de noms. Et le plafond de taille d'une clé d'index, qui valait 1 024 octets dans les anciennes versions, n'existe plus : une valeur indexée de 50 000 est passée.

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

Et ce qui n'a pas de plafond

Ni le nombre de collections, ni celui des bases, ni celui des documents d'une collection. Celui qui s'épuise en premier n'est pas dans ce tableau : c'est le disque. Et pour ce qui vraiment ne tient pas dans 16 Mo — un fichier — il y a GridFS, qui le découpe en morceaux de 255 Ko et range chaque morceau comme un document ordinaire.

Mots-clés : limites, plafonds, 16 Mo, taille de document, 10334, imbrication, Overflow, index par collection, 67, CannotCreateIndex, 13103, espace de noms, 73, InvalidNamespace, clé d'index

Le résumé du manuel MongoDB : sept habitudes qui valent la peine, chacune avec la mesure qui la soutient, et les cinq lignes qui prennent le pouls d'un serveur dont on vient d'hériter.

S'applique à : MongoDB 7.0+

C'est la fin du manuel, et elle n'apporte rien de neuf : elle rassemble ce que chaque thème a démontré, sous la forme qui sert au quotidien. Chaque habitude vient avec le nombre qui la soutient, et tous ces nombres ont été mesurés contre un vrai serveur, pas recopiés.

1. Modélise selon la façon dont tu vas lire, pas selon la ressemblance avec une table. Ce qui se lit ensemble se range ensemble. La limite de cette règle est dure et mesurée : un document ne dépasse pas 16 777 216 octets, donc un tableau qui grossit sans frein finit dans un 10334 un mardi quelconque.

2. Un index par requête fréquente, et pas un de plus. Chaque index est un arbre à entretenir à chaque écriture : les mêmes 20 000 insertions ont pris 60 ms sans index, 101 avec cinq et 160 avec dix. Et ils occupent : dans la base d'exemple, une collection de 1 148 000 octets de données portait 622 592 d'index.

3. Juge une requête sur totalDocsExamined face à nReturned, pas sur l'horloge. L'horloge dit ce que ça a pris aujourd'hui, avec un cache chaud et une machine tranquille ; le rapport entre ces deux nombres dit ce qui arrivera quand la collection sera dix fois plus grosse.

4. writeConcern: majority pour ce qui ne peut pas être perdu. Sur un replica set c'est déjà la valeur d'usine, donc l'habitude n'est pas de le poser : c'est de ne pas l'enlever pour aller plus vite.

5. Jamais sans authentification. C'est une ligne dans le fichier, et la seule porte qu'un serveur fraîchement démarré laisse ouverte — créer le premier utilisateur depuis la machine elle-même — se referme d'elle-même dès que cet utilisateur existe.

6. Sauvegarde avec l'oplog, et mesure sa fenêtre en temps. mongodump --oplog est ce qui fait d'une sauvegarde un instant. Et la taille de l'oplog ne se lit pas en octets mais en jours : le nœud mesuré donnait 34, mais cela dépend de ce qu'on écrit, c'est donc un nombre à regarder à nouveau quand la charge change.

7. L'ensemble de travail doit tenir dans le cache. C'est la seule règle de performance sans astuce. Dans la base d'exemple, 1 150 242 octets de données contre 3 621 781 504 de cache : de la place trois mille fois de trop. Le jour où il n'y en a plus, cela se voit partout à la fois.

Le pouls d'un serveur dont on vient d'hériter

Cinq lignes répondent à ce qu'il faut savoir avant de toucher à quoi que ce soit : s'il demande un mot de passe, à quel moment une écriture est tenue pour écrite, si quelqu'un surveille les requêtes lentes, combien de mémoire il a pour travailler et combien de données il doit déplacer.

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

Et une de plus, celle qui montre où part le disque et, au passage, quelle collection porte plus d'index que de données.

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

Mots-clés : bonnes pratiques, résumé, motif d'accès, index, writeConcern, majority, authentification, oplog, ensemble de travail, cache, explain, santé du serveur

Le type vit dans la valeur et non dans la colonne : les cinq affinités et ce que chacune convertit, l'ordre entre classes de stockage, ce que STRICT empêche vraiment, et pourquoi NOCASE ne connaît pas les accents.

S'applique à : SQLite 3.35+

Dans SQLite, le type appartient à la valeur, pas à la colonne. Ce qu'une colonne déclare est une affinité : une préférence appliquée au moment de ranger, qui convertit quand elle peut et laisse passer quand elle ne peut pas.

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

Les voilà toutes les cinq en une ligne. INTEGER et REAL convertissent le texte qui ressemble à un nombre ; TEXT convertit le nombre en texte ; NUMERIC regarde la valeur et décide, donc la même colonne contient un integer et un real ; et BLOB est celle qui n'a pas d'affinité : elle range ce qu'on lui donne, tel quel.

Les classes de stockage sont cinq, et elles sont ordonnées

Une colonne sans type déclaré est légale et accepte les cinq, donc une même colonne peut contenir un entier, un réel, un texte, un blob et un nul. Et on peut les trier, car entre classes il existe un ordre fixe : les nuls d'abord, puis les nombres, puis le texte, et les blobs à la fin. Ce qui veut dire qu'un ORDER BY sur une colonne sale n'échoue pas : il groupe par type sans le dire à personne.

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 empêche moins qu'il n'y paraît

Depuis la 3.37, une table peut être déclarée STRICT ; elle n'accepte alors qu'une poignée de types — INT, INTEGER, REAL, TEXT, BLOB et ANY — et refuse ce qu'elle ne peut pas ranger. Mais elle convertit toujours : un '42' entre dans une colonne INTEGER parce que rien ne se perd, et un 42 entre dans une TEXT et se range comme '42'. Ce qu'elle refuse, c'est ce qui n'a pas de conversion. Et il y a un gain inattendu : un type inventé, qu'une table normale accepte en silence, est refusé ici dès la création.

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"

Pas de date, pas de booléen, et la collation ignore les accents

TRUE est un integer de valeur 1. Une date est ce que tu décides : date() renvoie du text et julianday() du real, et celui que tu choisis, tu le trieras et le compareras toute sa vie. Et il n'y a que trois collations — BINARY, NOCASE et RTRIM : NOCASE égalise majuscules et minuscules de l'ASCII et rien d'autre, donc café et CAFÉ sont des valeurs différentes et upper('café') renvoie CAFé. Le texte est bien de l'UTF-8 : length compte des caractères, et sur le blob il compte des octets.

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

Mots-clés : types, affinité, typeof, INTEGER, REAL, TEXT, BLOB, NUMERIC, typage dynamique, STRICT, booléen, date, julianday, COLLATE, NOCASE, UTF-8, classe de stockage

L'isolation est sérialisable parce qu'un seul écrit : les trois modes de BEGIN, le SQLITE_BUSY 5 et pourquoi busy_timeout le règle, le 517 qu'il ne règle pas, et ce que change vraiment le mode WAL.

S'applique à : SQLite 3.35+

SQLite n'a pas de niveaux d'isolation à choisir, et ce n'est pas un manque : l'isolation est sérialisable parce que dans toute la base un seul écrit à la fois. Tout le reste en découle.

Deux réglages la gouvernent, et les deux arrivent d'usine avec la pire valeur possible.

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;

Les trois modes de BEGIN

DEFERRED — celui par défaut — ne prend rien tant qu'il ne faut pas : la première lecture prend un instantané et la première écriture demande le verrou. IMMEDIATE demande le verrou d'écriture tout de suite, sur la ligne même du BEGIN. EXCLUSIVE demande en plus que personne ne lise, et en mode WAL il ne fait presque rien de plus qu'IMMEDIATE. La règle pratique : si la transaction va écrire, BEGIN IMMEDIATE ; cela coûte une attente au début et évite l'erreur la plus agaçante de SQLite, celle d'en dessous.

SAVEPOINT est la marque intermédiaire, et ROLLBACK TO y revient sans fermer la transaction.

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 est le 5, et il est presque toujours de votre fait

Quand un autre tient le verrou d'écriture, la réponse est SQLITE_BUSY avec le code 5 et le texte « database is locked ». Ce n'est pas une panne : c'est la file d'une ressource à voie unique. Ce qui en fait une panne, c'est que busy_timeout vaut 0 d'usine, donc sans y toucher la réponse est immédiate et sèche. Avec 800 ms posés, le même appel a attendu 895 avant d'abandonner.

# 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

Celui que busy_timeout ne règle pas : le 517

Si une transaction DEFERRED lit puis veut écrire, et qu'entre-temps quelqu'un a validé, l'instantané pris à la lecture ne vaut plus et SQLite renvoie SQLITE_BUSY_SNAPSHOT, le 517. Attendre ne sert à rien : personne ne va rendre cet instantané. La seule sortie est ROLLBACK et tout recommencer — ou, mieux, avoir ouvert avec 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

Ce que WAL change, et ce qu'il ne change pas

Avec le journal de reprise, pendant qu'un écrit personne ne lit. Avec journal_mode = WAL, les lecteurs continuent de lire la dernière version validée pendant que l'écrivain travaille : mesuré avec deux connexions, le lecteur a obtenu la valeur précédente sans bloquer un instant. Ce qui ne change pas, c'est le nombre d'écrivains : toujours un, et le second reçoit toujours un 5.

Mots-clés : transaction, BEGIN, DEFERRED, IMMEDIATE, EXCLUSIVE, SAVEPOINT, ROLLBACK TO, SQLITE_BUSY, 5, 517, BUSY_SNAPSHOT, busy_timeout, WAL, journal_mode, verrou, sérialisable

Un pragma n'est pas une seule chose : certains s'écrivent dans le fichier, d'autres durent le temps de la connexion, d'autres sont des ordres. Lequel est lequel, les valeurs d'usine mesurées, et celui qui est éteint et ne devrait pas l'être.

S'applique à : SQLite 3.35+

SQLite n'a pas de fichier de configuration : il a des pragmas. Et la première confusion à lever est qu'ils ne sont pas tous de la même sorte. Certains s'écrivent dans le fichier et valent pour qui l'ouvrira plus tard ; d'autres durent le temps de la connexion et doivent être répétés à chaque fois ; et d'autres ne sont pas des réglages mais des ordres qui font quelque chose, et c'est tout.

Voici les valeurs d'usine, lues sur une base fraîchement créée.

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

Celui qui est éteint et que presque personne n'attend

foreign_keys vaut 0. Les clés étrangères se déclarent, se rangent dans le schéma, apparaissent dans le CREATE TABLE… et ne sont pas vérifiées. Un enfant orphelin entre sans un mot. L'allumer est une ligne, mais cela appartient à la connexion : il faut le poser sur chacune, et l'allumer ne regarde pas en arrière — c'est à cela que sert foreign_key_check, qui énumère ce qui est déjà passé.

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

Lequel reste et lequel non

journal_mode et user_version s'écrivent dans l'en-tête du fichier et survivent à la fermeture et à la réouverture. page_size et auto_vacuum aussi, mais seulement s'ils sont posés avant la première table : sur une base qui a déjà des pages, ils sont acceptés sans erreur et ne changent rien, et cela a été vérifié des deux façons. foreign_keys, cache_size, busy_timeout et mmap_size appartiennent à la connexion et reviennent à leur valeur d'usine dès qu'on en ouvre une autre.

-- 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 détail qui déroute : après avoir posé journal_mode = WAL, synchronous valait 1 (NORMAL) sans que personne y touche. C'est délibéré — en WAL, NORMAL suffit pour ne rien perdre de validé — mais cela montre que lire un pragma ne dit pas d'où vient cette valeur.

Et ceux qui sont des ordres

wal_checkpoint verse le journal dans la base et, avec TRUNCATE, le laisse à zéro octet : un -wal de 4 716 016 octets a été mesuré passant à 0. ANALYZE remplit sqlite_stat1 avec ce que le planificateur utilisera pour choisir un index. Et PRAGMA optimize est celui qu'il vaut la peine de lancer en fermant une connexion de longue vie : il regarde quelles tables ont assez changé et déclenche l'ANALYZE nécessaire, sans rien dire.

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

Mots-clés : pragma, configuration, journal_mode, WAL, synchronous, foreign_keys, cache_size, mmap_size, auto_vacuum, page_size, user_version, wal_checkpoint, optimize, ANALYZE, sqlite_stat1

SCAN, SEARCH et COVERING sont tout le vocabulaire d'EXPLAIN QUERY PLAN. Les index partiels et d'expression et la condition qu'il faut répéter pour qu'ils servent, ce qu'économise WITHOUT ROWID et ce qu'écrit ANALYZE.

S'applique à : SQLite 3.35+

Dans SQLite il n'y a qu'une sorte d'index : l'arbre B. Pas de hachage, pas de bitmap, rien à choisir. La recherche par mots existe mais n'est pas un index : c'est FTS5, une table virtuelle à part. Cela simplifie tout le thème, car la seule décision qui reste est sur quelles colonnes et dans quel ordre.

Et cela se vérifie avec EXPLAIN QUERY PLAN, dont le vocabulaire tient en trois mots : SCAN, c'est lire toute la table ; SEARCH, c'est entrer par un index ; et COVERING, c'est que la ligne n'a même pas été touchée.

-- 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=?)

Le composé se parcourt par préfixes, comme dans n'importe quel moteur : (cliente, estado) sert pour cliente seul et pour les deux ensemble, mais pas pour estado seul.

Couvrant

Si l'index porte toutes les colonnes que la requête lit, la ligne n'est pas touchée. C'est le même index qu'avant : ce qui change, c'est ce qu'on demande.

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

Partiel et d'expression : les deux, il faut les répéter

Un index partiel n'indexe que les lignes qui remplissent une condition, et c'est pour cela qu'il occupe peu. Le prix, c'est que la requête doit répéter cette condition, mot pour mot, sinon le planificateur ne peut pas s'en servir : sans elle, la même requête repasse en SCAN. Même chose pour l'index d'expression : il indexe lower(correo), il faut donc écrire lower(correo) dans le WHERE ; avec correo tout seul il ne sert à rien.

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 enlève une indirection

Une table normale range ses lignes sous un rowid caché, donc sa clé primaire est un autre index qui doit ensuite aller chercher la ligne. Avec WITHOUT ROWID, la table est l'arbre de sa clé primaire : le saut est économisé et l'espace aussi — 1 335 296 octets contre 1 675 264 sur la même table de 20 000 lignes, 20 % de moins — et le plan le trahit en disant USING PRIMARY KEY au lieu de nommer un index automatique.

ANALYZE donne des nombres, pas des miracles

Il remplit sqlite_stat1 avec le nombre de lignes et combien il y en a par valeur de l'index. Ce second nombre est celui qui dit si un index sert à quelque chose : 20 000 par valeur veut dire qu'il ne distingue rien. Mais il ne change pas toujours le choix : dans la mesure, le planificateur choisissait déjà bien avant de le lancer, car sans statistiques il s'appuie sur des suppositions raisonnables. Lancer ANALYZE enlève les suppositions ; il ne promet pas un plan différent.

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

Mots-clés : index, arbre B, EXPLAIN QUERY PLAN, SCAN, SEARCH, COVERING INDEX, index partiel, index d'expression, WITHOUT ROWID, ANALYZE, sqlite_stat1, FTS5

Les mêmes 20 000 insertions mesurées de six façons : la différence entre la meilleure et la pire n'est dans aucun pragma, elle est dans la présence d'un BEGIN. Et ce qu'apportent vraiment WAL, VACUUM et la taille de page.

S'applique à : SQLite 3.35+

Il n'y a qu'une seule chose qui compte, et ce n'est pas un pragma. Les mêmes 20 000 insertions, même schéma, même machine :

440 fois, et ce qui l'explique est que sans BEGIN chaque INSERT est sa propre transaction : vingt mille validations, chacune attendant le disque.

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

Attention à un piège de la couche intermédiaire : que le pilote offre un appel d'« insertion par lot » ne veut pas dire qu'il ouvre une transaction. L'executemany de Python, sans BEGIN explicite, a pris 4 128 ms : exactement autant que la boucle à la main.

Ce qu'apportent les autres

Éteindre synchronous a économisé 38 % et propose en échange de perdre des données validées lors d'une coupure : le pire marché de la liste. WAL sans transaction est descendu à 258 ms — quinze fois — parce que valider cesse de réécrire la base, et celui-là est un changement qu'on peut laisser en place. Mais avec les deux en jeu, le BEGIN emporte presque tout : 9 ms sans WAL et 10 avec. D'abord on groupe, et seulement ensuite on affine.

VACUUM est ce qui rend la place

Supprimer ne réduit pas le fichier : les pages restent sur une liste de libres pour être réutilisées. Un fichier de 10 813 440 octets a été mesuré, la moitié de ses lignes supprimée, et il a continué à mesurer exactement pareil. VACUUM le réécrit en entier et l'a laissé à 5 410 816. Cela coûte une copie de la base et un verrou exclusif, ce n'est donc pas une tâche de chaque nuit : c'est ce qu'on lance quand une grosse suppression a laissé le fichier au double de ce qu'il devrait faire.

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 taille de page n'a presque jamais besoin d'être touchée

Trois ont été mesurées. La baisser à 512 a coûté 20 % de fichier en plus et un parcours mesurable là où les deux autres n'atteignaient pas la milliseconde ; la monter à 65 536 n'a rien gagné. Celle d'usine — 4 096 — est celle qu'il faut laisser, et d'ailleurs elle ne peut être changée qu'avant la première table, ou ensuite en passant par 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

Et deux autres choses qui se mesurent toutes seules

Chaque index se paie à chaque écriture : les mêmes 20 000 lignes ont pris 7 ms sans index propres, 12 avec un, 16 avec deux et 20 avec trois, et le fichier est passé de 458 752 à 1 277 952 octets. Et une requête à paramètre se réutilise : 5 000 requêtes avec ? ont pris 18 ms, et les mêmes avec la valeur collée dans le SQL, 26 — en plus d'être la porte par où entre l'injection.

Mots-clés : performance, transaction, BEGIN, COMMIT, lot, executemany, synchronous, WAL, VACUUM, freelist_count, page_size, fragmentation, requête préparée

Ce qu'ALTER TABLE sait faire tient en quatre lignes, et ce qu'il refuse en ajoutant et en supprimant une colonne est mesuré avec son message. Le détour créer, copier, supprimer et renommer, et pourquoi ici il est sûr.

S'applique à : SQLite 3.35+

ALTER TABLE sait faire quatre choses, et pas une de plus. Changer le type d'une colonne, lui retirer un NOT NULL, ajouter une clé étrangère : rien de tout cela n'existe, et la tentative ne va même pas jusqu'à une erreur de schéma, c'est une erreur de syntaxe.

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

Ce qu'ADD COLUMN refuse

Les trois refus ont la même cause : la colonne nouvelle s'ajoute sans toucher aux lignes déjà là, donc la valeur qu'elles reçoivent doit pouvoir se décider sans les regarder. Un DEFAULT qui change, un UNIQUE qu'il faudrait vérifier et un NOT NULL sans valeur n'y répondent pas.

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

Ce que DROP COLUMN refuse, et pire : ce qu'il permet

Depuis la 3.35 on peut retirer une colonne, mais pas si elle fait partie de la clé primaire, ni si elle est UNIQUE, ni si un index ou une colonne générée la nomme. Jusque-là, très bien. Le problème est le cas qu'il laisse passer : une colonne qu'utilise une vue se retire sans un mot, la vue reste cassée, et PRAGMA integrity_check continue de dire ok parce qu'il ne regarde pas dans les vues. Personne ne prévient jusqu'à ce que quelqu'un interroge.

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

Le détour habituel

Pour tout le reste, la procédure est : créer la nouvelle table, copier, supprimer l'ancienne et renommer. Cela paraît dangereux et ici ça ne l'est pas, grâce à quelque chose que MySQL n'a pas : le DDL de SQLite est transactionnel. C'est mesuré : un CREATE TABLE et un ADD COLUMN dans un BEGIN, avec ROLLBACK à la fin, n'ont laissé ni la table ni la colonne. Tout le détour tient donc dans une transaction et, si quelque chose tourne mal à mi-chemin, rien ne reste à moitié fait.

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;

Trois précautions. Les index, déclencheurs et vues de l'ancienne table partent avec elle et doivent être recréés, car le DROP TABLE les emporte. Les clés étrangères s'éteignent pendant le détour et se vérifient avec foreign_key_check avant de les rallumer. Et la conversion de types est à vous : un CAST('a' AS INTEGER) renvoie 0, sans un mot d'avertissement.

Mots-clés : DDL, ALTER TABLE, RENAME TO, RENAME COLUMN, ADD COLUMN, DROP COLUMN, 3.25, 3.35, vue cassée, integrity_check, DDL transactionnel, douze étapes, CAST

Une base en mode WAL, ce sont trois fichiers et les données ne sont presque jamais dans le premier : le copier laisse une base vide qui se dit saine. Les trois façons qui marchent, mesurées, et ce que trouve chaque vérification d'intégrité.

S'applique à : SQLite 3.35+

Une base SQLite ressemble à un fichier, et c'est là que les ennuis commencent. En mode WAL il y en a trois, et celui qui porte le nom peut ne porter aucune donnée : après avoir écrit 30 000 lignes, le .sqlite mesurait 4 096 octets — l'en-tête et guère plus — et le -wal mesurait 3 366 072.

Copier seulement le premier ne donne pas une base cassée. Cela donne pire : une base qui s'ouvre sans broncher, qui n'a pas une seule table, et à laquelle PRAGMA integrity_check répond ok. Une sauvegarde pareille passe toutes les vérifications et ne contient rien.

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

Les trois façons qui marchent

VACUUM INTO écrit une copie propre et défragmentée dans un autre fichier, base en service : 3 338 240 octets en 4 ms, avec les 30 000 lignes. L'API de sauvegarde — le .backup de la ligne de commande, et Connection.backup dans les pilotes — fait la même chose en copiant des pages et peut avancer par tranches : 3 338 240 octets en 3 ms. Et .dump écrit le SQL qui reconstruit la base : 4 008 968 octets de texte et 30 003 instructions, 20 % de plus que le binaire, mais c'est le seul qui se lise avec les yeux et le seul qui survive à un changement de format.

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 détail appréciable : la copie de VACUUM INTO sort en journal_mode delete, pas en WAL. C'est un seul fichier, et c'est exactement ce qu'on veut d'une sauvegarde.

Copier les trois fichiers à la fois, base arrêtée, fonctionne bien. Le problème, c'est « à la fois » et « arrêtée » : tant que quelqu'un écrit, il n'y a aucun instant où les trois concordent, et aucun outil de copie ne le garantit.

Vérifier ce qu'on a

integrity_check parcourt toute la base et quick_check saute les vérifications croisées entre index et tables. Sur une base saine, les deux ont dit ok, et sur la même base avec quelques centaines d'octets abîmés exprès, les deux ont dit exactement la même chose : Tree 2 page 4 cell 35: Rowid 0 out of order. La différence entre les deux ne se voit que sur une grosse base, et aucun des deux ne répare quoi que ce soit : ils servent à décider s'il faut revenir à la sauvegarde.

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

Et quand il est déjà trop tard

Restaurer un .dump, c'est lancer son SQL contre une base vide. Restaurer une copie binaire, c'est la remettre à sa place, et là il est bon de savoir que VACUUM INTO refuse d'écraser : sur un fichier qui existe déjà, il répond « output file already exists », il n'y a donc aucun moyen de piétiner la sauvegarde d'hier sans s'en apercevoir. Et s'il ne reste qu'une base abîmée et aucune sauvegarde, il y a le .recover de la ligne de commande, qui parcourt les pages encore compréhensibles et écrit le SQL pour reconstruire ce qui peut l'être : il ne promet pas tout, il promet ce qui reste.

Mots-clés : sauvegarde, VACUUM INTO, API de sauvegarde, dump, WAL, -wal, -shm, integrity_check, quick_check, corruption, restauration

Les huit qui sortent vraiment, provoqués un par un avec leur texte, et l'arithmétique du code étendu : le 19 de contrainte devient 275, 787, 1299, 1555 ou 2067 selon ce qui a été enfreint.

S'applique à : SQLite 3.35+

SQLite a deux jeux de codes : un basique, à un ou deux chiffres, et un étendu, qui dit la même chose plus précisément. Et la relation entre les deux est arithmétique : l'étendu est le basique plus 256 fois le sous-type, donc code & 255 redonne toujours le basique. Un pilote qui ne montre que le basique vous en cache la moitié.

Le 19, ce sont cinq erreurs différentes

Le basique ne dit rien d'utile, car une contrainte enfreinte peut être n'importe laquelle des cinq. L'étendu, si, et le message aide… à une exception près : la clé primaire d'un INTEGER PRIMARY KEY donne 1555, mais son texte dit « UNIQUE constraint failed ». Là, le nombre est donc plus précis que la phrase.

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

Le 5 et le 6 se confondent et ne sont pas la même chose

Le 5 vient de dehors : une autre connexion écrit, et cela se règle en attendant — c'est là que busy_timeout gagne sa place. Le 6 vient de dedans : la même connexion a un curseur ouvert sur la table qu'elle veut modifier, et attendre ne sert à rien puisque celui qui bloque, c'est vous. Cela se règle en fermant le curseur.

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

Le 13 n'est presque jamais le disque

SQLITE_FULL sonne comme une partition pleine et c'est souvent le plafond que la base s'est posé elle-même : max_page_count. Avec 20 pages on le provoque en une ligne, et le message est le même qu'aurait donné un disque vraiment plein : « database or disk is full ».

Le 11, le 26 et celui qu'on ne voit pas

Les deux codes de fichier abîmé se distinguent par l'endroit du dégât : si ce qui n'a pas de sens est une page, SQLITE_CORRUPT avec « database disk image is malformed » ; si ce qui n'a pas de sens est l'en-tête, il n'essaie même pas et dit SQLITE_NOTADB. Et le 21 est le bizarre : appeler l'API dans un ordre impossible. On ne le voit presque jamais, car le pilote employé l'attrape avant et lève une erreur à lui ; en Python, par exemple, il sort un ProgrammingError qui ne porte même pas de code 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

Mots-clés : erreurs, codes, 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

Les plafonds de SQLite ne sont pas ceux du format mais ceux du binaire, et ils se lisent avec PRAGMA compile_options. Les huit qu'on touche vraiment, provoqués un par un, et le seul qu'on dépasse sans un mot.

S'applique à : SQLite 3.35+

Les plafonds de SQLite ont une particularité qu'aucun autre moteur n'a : ils n'appartiennent pas au format, mais au binaire auquel on parle. Ils sont fixés à la compilation, et c'est pourquoi la réponse à « quel est le maximum ? » commence par PRAGMA compile_options, qui les montre tous. Un programme peut en outre les abaisser à chaud avec sqlite3_limit, jamais les relever.

Voici ceux du binaire que livre macOS, et les cinq premiers ont été provoqués.

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

Celui qui ne prévient pas

PRAGMA page_size = 131072 ne donne pas d'erreur, ne renvoie rien d'étrange et ne change rien : la page reste à 4 096. Le maximum est 65 536 et ce qu'on demande au-dessus est écarté en silence, donc la seule façon de savoir s'il a pris est de le relire. C'est le même mode de défaillance que page_size et auto_vacuum ont déjà sur une base qui a des tables : acceptés, et sans effet.

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

Combien tient vraiment

La taille maximale du fichier n'est pas une constante : c'est max_page_count fois la taille de page. Avec les valeurs d'usine — 1 073 741 823 pages de 4 096 octets — cela fait 4 Tio, et en montant la page à 65 536, 64 Tio. Bien avant d'en approcher, c'est autre chose qui s'épuise : un texte ne dépasse pas 1 000 000 000 octets, et un SELECT de plus de 250 000 paramètres ne peut même pas être préparé.

Et un avertissement sur le tableau ci-dessus : c'est celui de ce binaire. Celui d'un téléphone, celui d'une bibliothèque embarquée ou celui que quelqu'un a compilé avec ses propres drapeaux peuvent porter d'autres nombres, et c'est pourquoi la réponse utile n'est jamais la valeur : c'est la commande qui la demande.

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

Les abaisser est une défense

Que sqlite3_limit ne sache qu'abaisser n'est pas un défaut : c'est sa raison d'être. Une application qui accepte du SQL écrit par quelqu'un d'autre abaisse LENGTH, COMPOUND_SELECT et EXPR_DEPTH à ce dont elle a réellement besoin, et dès lors une requête hostile ne peut plus réclamer un gigaoctet de mémoire. C'est la même idée que le max_page_count du thème des erreurs : le plafond qu'on se pose soi-même prévient plus tôt que celui du système, et il prévient de quelque chose qu'on peut corriger.

Mots-clés : limites, plafonds, compile_options, MAX_COLUMN, MAX_COMPOUND_SELECT, MAX_ATTACHED, MAX_LENGTH, MAX_PAGE_SIZE, max_page_count, sqlite3_limit, taille maximale, colonnes, ATTACH

Le résumé du manuel SQLite : sept habitudes avec la mesure qui les soutient, quatre d'entre elles dans les lignes qui suivent l'ouverture de la connexion, et les six qui prennent le pouls d'un fichier qu'on ne connaît pas.

S'applique à : SQLite 3.35+

C'est la fin du manuel, et elle n'apporte rien de neuf : elle rassemble ce que chaque thème a laissé mesuré. Ce qui frappe, c'est où tombent quatre des sept : dans les lignes qu'on écrit juste après avoir ouvert la connexion, et que presque aucun programme n'écrit.

1. PRAGMA journal_mode = WAL. Il reste écrit dans le fichier, une fois suffit donc. Avec lui, un lecteur a continué de lire pendant qu'un autre écrivait, sans bloquer un instant. Ce qu'il ne règle pas, c'est le nombre d'écrivains : toujours un.

2. PRAGMA foreign_keys = ON, sur chaque connexion. C'est la seule chose de cette liste qui change ce que la base accepte, et elle arrive éteinte : éteinte, un enfant orphelin entre sans un mot. Et elle ne reste pas en place, donc elle va au même endroit que le busy_timeout.

3. PRAGMA busy_timeout, et posé par vous. Le moteur le livre à 0 — il répond SQLITE_BUSY sur-le-champ — mais beaucoup de pilotes le changent en se connectant : celui de Python le laisse à 5 000 sans le dire. Le nombre qui compte n'est donc pas celui de la documentation : c'est celui que renvoie PRAGMA busy_timeout sur votre connexion.

4. Groupez les écritures dans une transaction. C'est, de loin, ce qui change le plus : les mêmes 20 000 insertions ont pris 3 963 ms une par une et 9 ms dans un BEGIN. Et attention à la couche intermédiaire : un appel d'« insertion par lot » du pilote n'ouvre pas de transaction tout seul.

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. Sauvegardez avec VACUUM INTO, jamais en copiant le fichier. En mode WAL les données sont dans le -wal, donc copier le .sqlite donne une base qui s'ouvre, n'a pas une seule table, et à laquelle integrity_check répond ok. Une sauvegarde qui passe toutes les vérifications et qui est vide est pire que pas de sauvegarde du tout.

6. Un index par requête fréquente, et regardez ce qu'il pèse. dbstat le dit par objet, et c'est surprenant : dans la base mesurée, l'index occupait 2 056 192 octets contre 1 826 816 pour la table qu'il indexait.

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 sécurité appartient au fichier. Il n'y a ni utilisateurs, ni rôles, ni GRANT : qui peut lire le fichier peut tout lire, et qui peut l'écrire peut l'effacer. La protection, ce sont les permissions du système, le chiffrement du disque et — sur un téléphone — la classe de protection des données. Tout le reste de ce manuel, c'est de la performance ; ceci est la seule chose sans substitut.

Et une qui n'est pas une habitude mais une frontière

SQLite encaisse bien plus que sa réputation ne le suggère, mais il a une frontière qu'aucune pratique ne déplace : un seul écrit à la fois. Tant que les écritures viennent d'un processus, ou de plusieurs qui se relaient, le fichier tient jusqu'à des limites que presque personne n'atteint. Le jour où il faut deux écrivains simultanés pour de bon, ce n'est pas un pragma qu'il faut changer : c'est de moteur.

Mots-clés : bonnes pratiques, résumé, WAL, foreign_keys, busy_timeout, transaction par lot, VACUUM INTO, sauvegarde, dbstat, index, permissions, chiffrement, sécurité

Moindre privilège, rôles, connexions chiffrées et la liste de contrôle avant d'exposer un serveur.

S'applique à : MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Dans MySQL et MariaDB, l'identité d'un utilisateur, ce sont deux choses : le nom et l'hôte depuis lequel il se connecte. 'app'@'10.0.%' et 'app'@'%' sont deux comptes distincts, avec des mots de passe et des droits différents. La plupart des frayeurs de sécurité commencent par oublier cela.

Moindre privilège
Accordez ce que l'application utilise, pas un de plus, et sur l'hôte le plus étroit possible. Un compte applicatif n'a presque jamais besoin de DROP, et jamais de SUPER, FILE ni GRANT OPTION :

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

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

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

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

Rôles

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

Connexions chiffrées
Sans TLS, le mot de passe et les données circulent en clair sur le réseau. On peut l'exiger par compte ou pour tout le serveur avec require_secure_transport. Calíope prend en charge TLS dans le profil de connexion, ainsi que le tunnel SSH quand le serveur n'est pas exposé :

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

SHOW VARIABLES LIKE 'require_secure_transport';

SELECT user, host, ssl_type FROM mysql.user;

Audit rapide
Trois requêtes à passer sur tout serveur hérité. Comptes sans mot de passe, comptes ouverts à n'importe quel hôte, et privilèges dangereux distribués :

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

Avant d'exposer un serveur
1. Aucun compte anonyme ni sans mot de passe, et pas de base d'exemple test.
2. root uniquement depuis localhost, avec un compte d'administration séparé pour le reste.
3. bind-address sur la bonne interface — pas 0.0.0.0 si personne de l'extérieur ne doit y accéder.
4. TLS obligatoire pour toute connexion qui sort de la machine.
5. Mots de passe gérés hors du code — Calíope les garde dans le Trousseau, jamais en clair.
6. Des comptes séparés par application, pour qu'une compromission n'entraîne pas le reste.
7. Revoir les GRANT régulièrement : les droits s'accumulent et personne ne les retire.

Recommandation
Commencez par révoquer plutôt que par accorder : créez le compte sans rien et ajoutez des privilèges jusqu'à ce que l'application fonctionne. L'outil Utilisateurs de Calíope affiche les privilèges effectifs par base et par table, là où apparaissent d'ordinaire les surprises.

Mots-clés : sécurité, utilisateur, rôle, privilège, grant, revoke, moindre privilège, ssl, tls, require ssl, durcissement, mysql.user, user_privileges, audit

Logique contre physique, à quoi sert le binlog et comment revenir à la minute d'avant le DELETE.

S'applique à : MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Une sauvegarde qui n'a jamais été restaurée n'est pas une sauvegarde : c'est une intention. Deux chiffres commandent ici : le RPO (combien de données vous acceptez de perdre) et le RTO (combien de temps vous pouvez rester en panne). Tout le reste en découle.

Logique contre physique
- Logique (mysqldump, la Sauvegarde de Calíope) — produit du SQL. Portable entre versions et moteurs, permet de restaurer une seule table, et lente à restaurer sur de gros volumes.
- Physique (snapshot du volume, Percona XtraBackup, copie du répertoire serveur arrêté) — copie les fichiers. Très rapide à restaurer, mais liée à la version et à l'architecture du serveur.

Règle pratique : jusqu'à quelques dizaines de gigaoctets, logique ; au-delà, physique pour la copie complète et logique pour les pièces détachées.

Le binlog est la moitié qui manque
La sauvegarde vous ramène au moment où elle a été faite. Le binary log contient tout ce qui s'est passé ensuite, et c'est lui qui permet d'avancer de là jusqu'à une seconde avant le désastre. Sans log_bin actif, pas de restauration à un instant donné, seulement un retour à la dernière copie :

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

SHOW BINARY LOGS;

Restaurer à un instant donné
La procédure, toujours sur un serveur à part et jamais sur celui de production :
1. Restaurez la copie complète la plus récente antérieure à l'incident.
2. Repérez l'instant exact de l'erreur dans le binlog : l'instruction qui a trop supprimé, avec sa position ou son horodatage.
3. Rejouez le binlog depuis la position où s'arrêtait la copie jusqu'à juste avant cette instruction, avec mysqlbinlog et ses options --start-position et --stop-position (ou --start-datetime et --stop-datetime).
4. Vérifiez que les données sont là, et seulement ensuite décidez si vous promouvez ce serveur ou si vous en exportez ce qui manque.

La Visionneuse de binlog de Calíope sert à l'étape 2 : elle filtre les événements par date, base et type d'opération, c'est-à-dire la partie pénible à faire à la main.

Trouver la position
SHOW MASTER STATUS donne le fichier et la position courants ; les événements d'un binlog précis se listent ainsi :

SHOW MASTER STATUS;

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

Vérifier la restauration
Restaurer sans vérifier est la façon habituelle de découvrir le problème trop tard. Un comptage par base et un CHECKSUM TABLE des tables critiques face à la source suffisent pour dormir tranquille :

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;

Recommandation
Planifiez la sauvegarde (Calíope le fait, avec rétention configurable), gardez une copie hors de la machine, activez log_bin avec une rétention couvrant au moins deux cycles de copie, et répétez une restauration complète au moins une fois. Le jour de l'incident n'est pas le jour d'apprendre la procédure.

Mots-clés : sauvegarde, restauration, récupération, pitr, instant donné, binlog, mysqldump, mysqlbinlog, rpo, rto, checksum table, snapshot

ALGORITHM, LOCK, verrous de métadonnées et quand un outil externe s'impose.

S'applique à : MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Un ALTER TABLE sur une grande table peut prendre des heures et laisser l'application en attente. Depuis MySQL 5.6 et MariaDB 10.0, on peut indiquer comment le changement doit se faire, et savoir ainsi d'avance si cela va faire mal.

Exiger l'algorithme, ne pas s'en remettre au hasard
Si vous déclarez l'algorithme et que le serveur ne peut pas l'utiliser, l'instruction échoue immédiatement au lieu de bloquer votre table trois heures. C'est la raison principale de toujours l'écrire :

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 clause LOCK
- LOCK=NONE — lectures et écritures continuent pendant le changement. Si c'est impossible, erreur.
- LOCK=SHARED — lectures autorisées, écritures non.
- LOCK=EXCLUSIVE — personne ne touche la table.

Déclarer LOCK=NONE, c'est garantir que la migration n'arrêtera pas la production : soit elle tourne sans bloquer, soit elle ne tourne pas.

Le verrou de métadonnées, celui qui surprend
Même un ALTER instantané a besoin d'un verrou exclusif de métadonnées au début et à la fin. Si une vieille transaction reste ouverte sur cette table, l'ALTER attend — et toutes les requêtes qui arrivent ensuite font la queue derrière lui. Une table se fige à cause d'un ALTER qui devait durer une milliseconde. Avant de toucher au schéma, vérifiez qu'il n'y a pas de transactions longues :

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

SELECT @@lock_wait_timeout;

Suivre la progression
Un ALTER de plusieurs heures ne donne aucun signe de vie par lui-même. performance_schema, si :

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

SHOW PROCESSLIST;

Quand un outil externe s'impose
Si le changement impose ALGORITHM=COPY sur une table de dizaines de gigaoctets, aucun LOCK ne vous sauvera. C'est là qu'interviennent pt-online-schema-change (Percona) et gh-ost (GitHub) : ils créent une nouvelle table, copient par lots, maintiennent la synchronisation par triggers ou en lisant le binlog, et échangent à la fin en un instant. Ils ne sont pas livrés avec le serveur ; ils s'installent à part et s'exécutent en ligne de commande.

Recommandation
Écrivez toujours ALGORITHM= et LOCK= dans vos migrations, et essayez-les d'abord sur une copie avec des données réelles pour savoir combien de temps elles prendront. Un ALTER qui échoue au bout d'une seconde est une bonne nouvelle comparé à un qui verrouille la table en pleine matinée.

Mots-clés : ddl en ligne, alter table, algorithm, instant, inplace, copy, lock=none, verrou de métadonnées, mdl, pt-online-schema-change, gh-ost, migration de schéma

Pourquoi utf8 n'est pas UTF-8, ce que décide une collation et comment convertir sans casser les index.

S'applique à : MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Deux notions qu'on confond sans cesse : le jeu de caractères dit quels caractères peuvent être stockés, et la collation dit comment ils se comparent et se trient. Le premier joue sur ce qui rentre ; la seconde, sur ce que renvoie un WHERE.

utf8 n'est pas UTF-8
Dans MySQL, utf8 est un alias historique d'utf8mb3 : trois octets par caractère seulement, donc il ne peut pas stocker d'emoji ni une bonne part du chinois, du japonais ou du coréen modernes. Le vrai UTF-8, c'est utf8mb4. C'est le piège le plus rebattu du produit, et dans MySQL 8.0 il tient toujours pour compatibilité. Vérifiez où vous en êtes :

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

Ce que décide une collation
Le nom dit tout quand on sait le lire. Dans utf8mb4_0900_ai_ci : 0900 est la version d'Unicode, ai signifie insensible aux accents et ci insensible à la casse. Leurs contraires sont as (sensible aux accents) et cs (sensible à la casse). Il existe aussi utf8mb4_bin, qui compare octet par octet et ne connaît aucune langue.

Les valeurs par défaut diffèrent : MySQL 8.0 utilise utf8mb4_0900_ai_ci et MariaDB utf8mb4_general_ci ou utf8mb4_uca1400_ai_ci selon la version. Si vous déplacez des données de l'un à l'autre, ne supposez pas qu'ils trient pareil.

Ce qui change en pratique
Avec une collation ai_ci, café et cafe sont la même valeur : une contrainte UNIQUE rejettera la seconde, et un WHERE trouvera les deux. Cela peut être exactement ce qu'on veut pour chercher des noms, et un désastre pour stocker des identifiants :

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

Mélanger les collations fait mal
Une JOIN entre une colonne utf8mb4_general_ci et une autre utf8mb4_0900_ai_ci donne l'erreur 1267 Illegal mix of collations. Et si vous corrigez en enveloppant la colonne dans CONVERT() ou dans un COLLATE, la requête ne peut plus utiliser l'index de cette colonne. Le vrai correctif n'est pas le COLLATE dans la requête : c'est d'unifier la collation dans le schéma.

Convertir sans surprise
ALTER DATABASE ne change la valeur par défaut que pour les tables futures ; les existantes se convertissent une par une. Et CONVERT TO CHARACTER SET réécrit toute la table, ce qui mérite la même prudence que n'importe quel DDL lourd :

ALTER DATABASE tienda
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;

ALTER TABLE clientes
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;

Recommandation
utf8mb4 partout — serveur, base, table, colonne et connexion cliente — et une seule collation dans tout le schéma. Avant de convertir, regardez les index sur les colonnes de texte longues : en passant d'utf8mb3 à utf8mb4, chaque caractère peut prendre un octet de plus, et un index qui rentrait peut cesser de rentrer.

Mots-clés : charset, jeu de caractères, collation, utf8, utf8mb4, latin1, emoji, accents, casse, convert to character set, illegal mix of collations

Les codes les plus fréquents — 1045, 1062, 1213, 2006 — et quoi faire de chacun.

S'applique à : MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Les codes inférieurs à 2000 viennent du serveur ; ceux à partir de 2000, de la bibliothèque cliente. Cette seule distinction indique où regarder : si le numéro commence par 2, le problème est dans la connexion, pas dans le SQL.

1045 et 1040 : la connexion
Le 1045 n'est presque jamais le mot de passe : c'est que le compte existe pour un autre hôte. Rappelez-vous que 'app'@'localhost' et 'app'@'%' sont deux comptes distincts. Le 1040 signifie que les connexions sont épuisées, et la cause habituelle n'est pas la taille du pool mais des connexions que personne ne ferme :

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 : entrée dupliquée
Le message nomme la clé qui a été violée. Si le doublon est attendu — un import rejoué, un upsert — il existe une syntaxe pour cesser de le traiter comme une erreur :

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 : impossible de créer la clé étrangère
Ce message est célèbre pour ne rien dire. Les vraies causes sont toujours les mêmes quatre : les types des deux colonnes ne correspondent pas exactement (signe et longueur compris), leurs jeux de caractères ne correspondent pas, il manque un index sur la colonne référencée, ou des lignes orphelines existent déjà que la contrainte n'accepterait pas :

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;

Bien lire l'erreur
Avant de chercher le code sur internet, lisez-le en entier : MySQL nomme d'ordinaire la table, la colonne et la valeur exactes. Et lorsqu'une instruction renvoie un avertissement plutôt qu'une erreur, SHOW WARNINGS juste après montre ce que le serveur a décidé tout seul — une troncature silencieuse, par exemple — ce qui est pire qu'un échec net.

Recommandation
Calíope affiche le code et le message du serveur tels quels, sans les envelopper : ce texte est le meilleur indice et mérite d'être copié en entier quand vous demandez de l'aide. Le Journal des requêtes conserve en outre l'instruction fautive, avec son heure et sa durée.

Mots-clés : erreur, code d'erreur, 1045, 1049, 1062, 1146, 1213, 1205, 1215, 1267, 1406, 2002, 2006, 2013, 1040, too many connections, gone away, access denied, duplicate entry