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+ SQL Server 2022+

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 clients_telephones (
    client_id  INTEGER NOT NULL,
    telephone    TEXT NOT NULL,
    type        TEXT NOT NULL CHECK (type IN ('mobile', 'maison', 'bureau')),
    PRIMARY KEY (client_id, telephone),
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
);

-- SQL Server: clé de substitution et ensemble fermé nommé
CREATE TABLE clients_telephones (
    id          BIGINT IDENTITY(1, 1) PRIMARY KEY,
    client_id  BIGINT NOT NULL,
    telephone    VARCHAR(20) NOT NULL,
    type        VARCHAR(10) NOT NULL
        CONSTRAINT ck_telephone_type CHECK (type IN ('mobile', 'maison', 'bureau')),
    UNIQUE (client_id, telephone),
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
);

-- Changer la liste ensuite
ALTER TABLE clients_telephones DROP CONSTRAINT ck_telephone_type;
ALTER TABLE clients_telephones ADD CONSTRAINT ck_telephone_type
    CHECK (type IN ('mobile', 'maison', 'bureau', 'fax'));

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

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

CREATE TRIGGER ligne_commande_after_insert
ON ligne_commande
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE p
       SET total = (SELECT COALESCE(SUM(d.quantite * d.prix_unitaire), 0)
                    FROM ligne_commande d
                    WHERE d.commande_id = p.id)
      FROM commandes p
     WHERE p.id IN (SELECT commande_id FROM inserted);
END;

CREATE VIEW dbo.commandes_total WITH SCHEMABINDING AS
SELECT commande_id, SUM(quantite * prix_unitaire) AS total, COUNT_BIG(*) AS lignes
FROM dbo.ligne_commande
GROUP BY commande_id;

CREATE UNIQUE CLUSTERED INDEX ux_commandes_total ON dbo.commandes_total (commande_id);

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+ SQL Server 2022+

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.

Les exemples utilisent ces tables :

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

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

-- SQL Server : l'ordre écrit commande
SELECT c.nom, cmd.id
FROM commandes cmd
JOIN clients c ON cmd.client_id = c.id
OPTION (FORCE ORDER);

-- La commande la plus chère de chaque client
SELECT c.nom, x.total
FROM clients c
CROSS APPLY (
    SELECT TOP (1) cmd.total
    FROM commandes cmd
    WHERE cmd.client_id = c.id
    ORDER BY cmd.total DESC
) x;

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, sur SQLite 3.51 et sur SQL Server 2025.

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.nom
FROM clients c
WHERE NOT EXISTS (SELECT 1 FROM commandes cmd WHERE cmd.client_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, CROSS APPLY, OUTER APPLY, FORCE ORDER

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+ SQL Server 2022+

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

Conception des schémas

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;

-- Le schéma d'abord, dans son propre lot : CREATE SCHEMA ventes;
CREATE TABLE ventes.commandes (
    id             BIGINT IDENTITY(1, 1) CONSTRAINT pk_commandes PRIMARY KEY,
    client_id      BIGINT       NOT NULL,
    statut         TINYINT      NOT NULL CONSTRAINT df_commandes_statut DEFAULT 0,
    cree_le        DATETIME2(3) NOT NULL CONSTRAINT df_commandes_cree_le DEFAULT SYSUTCDATETIME(),
    actualise_le   DATETIME2(3) NOT NULL CONSTRAINT df_commandes_actualise_le DEFAULT SYSUTCDATETIME(),
    CONSTRAINT fk_commandes_clients FOREIGN KEY (client_id)
        REFERENCES ventes.clients (id) ON UPDATE CASCADE,
    CONSTRAINT ck_commandes_statut CHECK (statut BETWEEN 0 AND 4)
);
CREATE INDEX idx_commandes_client_id ON ventes.commandes (client_id);
CREATE INDEX idx_commandes_cree_le   ON ventes.commandes (cree_le);

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

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

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

Réplication

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;

USE master;
CREATE LOGIN app_commandes WITH PASSWORD = N'Mot-De-Passe-Fort-2026',
    CHECK_POLICY = ON, DEFAULT_DATABASE = ma_base;
USE ma_base;
CREATE USER app_commandes FOR LOGIN app_commandes WITH DEFAULT_SCHEMA = ventes;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::ventes TO app_commandes;

-- JAMAIS en production
-- ALTER SERVER ROLE sysadmin ADD MEMBER app_commandes;
-- ALTER ROLE db_owner ADD MEMBER app_commandes;

Audit

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

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

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

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

Mots-clés : bonnes pratiques, conception, conventions, sauvegarde, réplication, GTID, binlog, GRANT, audit, sécurité, InnoDB, clé étrangère, TLS, SQL Server, IDENTITY, SEQUENCE, schéma, login, XACT_ABORT, SQL Server Audit

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+ SQL Server 2022+

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;

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

CTE 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

varchar face à nvarchar, le préfixe N, les collations _UTF8, ce que fait chaque suffixe, le conflit de collation et pourquoi un paramètre N'…' peut laisser ton index inutilisé.

S'applique à : SQL Server 2022+

Si tu viens de MySQL, il n'y a ici ni utf8mb4 ni SET NAMES. Dans SQL Server, l'encodage d'un texte ne se déclare pas à part : c'est sa collation qui le décide, et il existe deux familles de types texte.

varchar et nvarchar sont deux encodages
- nvarchar (et nchar) stocke de l'UTF-16 : n'importe quel caractère Unicode, quelle que soit la collation.
- varchar (et char) stocke les octets de la page de codes de sa collation. Avec une collation classique comme Latin1_General_CI_AS, c'est Windows-1252, et ce qui n'y figure pas est stocké sous forme de ?.
- Avec une collation qui se termine par _UTF8 (depuis SQL Server 2019), varchar stocke de l'UTF-8.

Mesuré sur le serveur de test (SQL Server 2025), avec la base en Latin1_General_100_CI_AS_SC_UTF8 :

LEN() compte les caractères et DATALENGTH() les octets. Le n de varchar(n) compte des octets, et celui de nvarchar(n) des paires d'octets, pas des caractères. Mesuré : un varchar(10) en UTF-8 accepte cinq ñ, et dix donnent Msg 2628 (« String or binary data would be truncated ») ; un nvarchar(10) accepte dix ñ mais pas six emojis.

Le préfixe N
Un littéral sans N devant est un varchar, et il est converti dans la page de codes de la base avant d'atteindre la moindre colonne. Mesuré dans une base Latin1_General_CI_AS :

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

Et dans une colonne varchar de cette base, même avec le N sur le littéral, Zúrich ✓ 日本 a été stocké comme Zúrich ? ??. La donnée se perd à l'écriture, sans erreur ni avertissement, et changer ensuite la collation de la colonne ne la récupère pas : ce qui est stocké, ce sont déjà des points d'interrogation.

Ce que dit chaque suffixe

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

Mesuré : 1, 0, 1, 0.

- _CI / _CS — ne distingue pas / distingue la casse.
- _AI / _AS — ne distingue pas / distingue les accents.
- _SC — compte comme un caractère ceux qui sont hors du plan de base, comme un emoji. Mesuré : LEN(N'😀') vaut 1 avec _SC et 2 sans, et sans _SC un LIKE N'_' ne le trouve pas.
- _UTF8 — varchar en UTF-8.
- _BIN2 — compare par point de code : il a trié A, B, a, b, n, o, á, ñ, là où Latin1_General_100_CI_AS et Modern_Spanish_100_CI_AS ont donné A, a, á, b, B, n, ñ, o.
- Le 100 est la version des tables de tri ; celles qui ne l'ont pas (Latin1_General_CI_AS) sont plus anciennes, et celles qui commencent par SQL_ sont les collations héritées de SQL Server 2000.

Le serveur de test propose 5540 collations, dont 1585 en _UTF8 (sys.fn_helpcollations()).

Serveur, base, colonne et expression

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

La collation se fixe à quatre niveaux, et chacun prend celle du niveau supérieur au moment de sa création :

- Serveur — celle de master et tempdb, choisie à l'installation.
- Base — CREATE DATABASE … COLLATE ….
- Colonne — nom varchar(40) COLLATE Latin1_General_100_CS_AS.
- Expression — WHERE a = b COLLATE Latin1_General_100_CI_AS, ou dans un ORDER BY.

ALTER DATABASE … COLLATE ne touche pas aux colonnes qui existent déjà : mesuré, la base est passée en _UTF8 et ses colonnes sont restées en Latin1_General_CI_AS. Chaque colonne se change avec son ALTER TABLE … ALTER COLUMN.

Le conflit de collation
Deux textes de collations différentes ne se comparent pas tout seuls :

- WHERE a = b → Msg 468, « Cannot resolve the collation conflict ».
- Un UNION ALL des deux colonnes → Msg 457 ; avec du texte du catalogue (sys.* est en Latin1_General_CI_AS) → Msg 451.
- On le résout en mettant COLLATE d'un côté.

Le cas le plus fréquent, ce sont les tables temporaires : #tmp vit dans tempdb et naît avec la collation du serveur, pas celle de ta base. Mesuré dans une base Latin1_General_100_CI_AS sur un serveur _UTF8 : un JOIN avec #tmp a donné Msg 468. La solution est de déclarer ses colonnes avec COLLATE DATABASE_DEFAULT.

La collation et les index
Sur 100 000 lignes avec un index sur la colonne :

- WHERE s = 'usuario42' → Index Seek. Avec COLLATE sur la colonne → Index Scan : l'index est parcouru en entier.
- WHERE s LIKE 'usuario42%' → Index Seek, contrairement à PostgreSQL.
- Un paramètre N'…' face à une colonne varchar : avec une collation Windows (Latin1_General_100_CI_AS), l'index a continué de servir, avec 7 lectures logiques ; avec une collation SQL_ (SQL_Latin1_General_CP1_CI_AS), toute la colonne est convertie en nvarchar et le plan passe en Index Scan : 334 lectures contre 3. Si une recherche par égalité est lente et que le paramètre arrive en nvarchar, regarde la collation de la colonne.

Recommandation
Pour une nouvelle base, une collation _100_…_SC_UTF8, qui fait de varchar un UTF-8 complet. Déclare les colonnes des tables temporaires avec COLLATE DATABASE_DEFAULT. Et décide de la collation en créant la base : la changer ensuite se fait colonne par colonne.

Mots-clés : encodage, encoding, collation, collate, varchar, nvarchar, utf8, utf-16, préfixe n, unicode, _sc, _bin2, 468, 451, 457, 2628, tempdb, database_default, datalength

datetime face à datetime2, bit au lieu de boolean, money face à decimal, des GUID qui ne fragmentent pas, rowversion qui n'est pas une date, la limite de 8000 octets et le type json de 2025.

S'applique à : SQL Server 2022+

Si tu viens de MySQL, plusieurs types ont des noms proches et se comportent autrement, et deux noms trompent : timestamp n'est pas une date et datetime arrondit. Ce qui n'existe pas donne une erreur, et tant mieux : il n'y a pas d'UNSIGNED, tinyint va seulement de 0 à 255 (avec -1 on obtient Msg 220, dépassement arithmétique) et SELECT TRUE donne Msg 207 : TRUE n'est pas un mot du langage.

Dates : datetime2, pas datetime
datetime est l'ancien type et arrondit au 1/300 de seconde. Mesuré :

La dernière ligne est celle qui casse les rapports : un WHERE fecha <= '2026-09-30 23:59:59.999' sur datetime inclut le minuit du jour suivant. Pour un jour entier, quel que soit le type, fecha >= '2026-09-30' AND fecha < '2026-10-01'.

datetime2 stocke jusqu'à cent nanosecondes (en datetime2(7), .1234567 est resté tel quel), commence en l'an 1 —'1700-01-01' en datetime donne Msg 242, hors limites— et occupe autant ou moins : mesuré, datetime 8 octets, datetime2(7) 8, datetime2(3) 7 et datetime2(0) 6. Et on ne les mélange pas sans réfléchir : le même 12:00:00.001 en datetime2(3) et en datetime n'est pas égal, parce que datetime l'avait déjà stocké comme .000.

Autres tailles mesurées : date 3 octets, time 5, smalldatetime 4 (à la minute) et datetimeoffset 10.

datetimeoffset garde le décalage
Contrairement à timestamptz de PostgreSQL, il garde bien le décalage avec lequel il a été écrit, et compare par instant : '2026-09-30T12:00:00+05:30' et '2026-09-30T06:30:00+00:00' sont sortis égaux. SWITCHOFFSET(valeur, '+00:00') le ramène en UTC, et AT TIME ZONE 'Central European Standard Time' l'a donné comme 08:30 +02:00, heure d'été appliquée. Calíope l'affiche à l'heure de la donnée, avec son décalage à côté.

Il n'y a pas de boolean : il y a bit
bit est un nombre qui vaut 0 ou 1, pas une valeur de vérité. Mesuré : lui affecter 5, -1 ou la chaîne 'true' stocke 1 ; un WHERE @b tout seul donne Msg 4145 —il faut écrire WHERE @b = 1— et bit + bit donne Msg 402. Calíope l'affiche comme 1 ou 0.

Argent : decimal, et money avec prudence

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

money a quatre décimales fixes, y dans les résultats intermédiaires aussi : 1 / 3 a donné .3333, et multiplié par 3 on ne retrouve plus l'entier. decimal élargit l'échelle du résultat (il a donné .3333333333333333333). Pour calculer, decimal ; money occupe 8 octets et smallmoney 4. float est binaire comme partout : 0.1 + 0.2 a donné 0.30000000000000004, et l'égalité avec 0.3, fausse ; en decimal, vraie.

GUID : uniqueidentifier, et l'ordre compte
Un uniqueidentifier occupe 16 octets. Comme clé primaire cluster, NEWID() répartit les lignes au hasard dans l'index. Mesuré avec 20 000 lignes insérées une à une :

NEWSEQUENTIALID() ne sert que comme DEFAULT d'une colonne : SELECT NEWSEQUENTIALID() donne Msg 302. Calíope affiche le GUID en majuscules, comme SQL Server.

rowversion n'est pas une date
rowversion —le type qui s'appelait timestamp, nom encore accepté à la création d'une colonne— est un compteur de 8 octets de la base qui change à chaque écriture de la ligne. Mesuré : deux lignes sont nées avec 0x…07D4 et 0x…07D5, et la mise à jour de la première l'a fait passer à 0x…07D6. Il ne stocke aucune heure. Il sert à la concurrence optimiste : on lit la version et on met à jour avec WHERE ver = @lue. Calíope l'affiche comme binaire, [8 bytes].

Texte : 8000 octets, ou max
Hors max, varchar(n) va jusqu'à 8000 et nvarchar(n) jusqu'à 4000 : varchar(8001) donne Msg 131 et nvarchar(4001), Msg 2717. Au-delà, varchar(max) et nvarchar(max).

La ligne a aussi une limite : deux char(5000) dans une table donnent Msg 1701 —la ligne minimale ferait 10 007 octets et le maximum est de 8060—. Deux varchar(8000) pleins tiennent (mesuré : 16 000 octets dans une ligne), parce que le variable sort de la page quand il n'y tient pas.

Et un piège qui ne prévient pas : REPLICATE('x', 10000) affecté à un varchar(max) a donné 8000 caractères, parce que la fonction travaille dans le type de son argument. Avec REPLICATE(CAST('x' AS varchar(max)), 10000), 10 000.

JSON : le type json date de SQL Server 2025
Dans SQL Server 2022, le JSON se stocke en nvarchar(max) et se valide avec CHECK (ISJSON(col) = 1), qui rejette le mal formé avec Msg 547. Depuis 2025 existe le type json, qui le rejette à l'écriture avec Msg 13609 et le stocke déjà analysé. Mesuré sur 20 000 copies d'une commande de 142 caractères, json a occupé 5,4 Mo et nvarchar(max) 6,0 Mo ; pour un document minuscule, {"b":1,"a":2}, c'est l'inverse : 60 octets contre 26.

Et une différence visible : avec une clé répétée, ISJSON accepte {"b":1,"a":2,"a":3}, et le type json l'a rendu comme {"b":1,"a":2} : il a gardé la première, sans prévenir. Il conserve l'ordre des clés. Calíope reçoit le json comme texte et l'affiche tel quel.

Les types spéciaux
- geography et geometry — des types au format binaire propre, qui n'est pas du WKB. geography::Point(40.4168, -3.7038, 4326) occupe 22 octets et son STAsText() donne POINT (-3.7038 40.4168) : le constructeur veut latitude, longitude et le texte sort en longitude, latitude. Calíope affiche la cellule comme [22 bytes] ; pour la lire, col.STAsText() dans la requête.
- hierarchyid — un chemin dans un arbre : '/1/3/' a été stocké sur 2 octets, avec le niveau 2 et le parent /1/. Calíope ne le décode pas et la cellule indique type non pris en charge : hierarchyid ; avec col.ToString() dans la requête, on le lit.
- sql_variant — une colonne qui garde des valeurs de plusieurs types, chacune avec le sien : SQL_VARIANT_PROPERTY(@v, 'BaseType') a donné decimal. Calíope l'affiche selon son type de base. Il a du sens dans le catalogue (sys.extended_properties) ; dans une table à toi, c'est souvent une colonne mal conçue.

IDENTITY ou SEQUENCE
IDENTITY est une propriété de la colonne, et il la protège : insérer une valeur à la main donne Msg 544, sauf avec SET IDENTITY_INSERT tabla ON, et ensuite le compteur repart du plus grand (après avoir inséré 100, la ligne suivante a été 101). Et il laisse des trous : un INSERT annulé par ROLLBACK a consommé le 3, et la ligne suivante a été la 4. Un trou n'est pas une donnée perdue.

SEQUENCE est un objet à part, que plusieurs tables peuvent partager : avec DEFAULT NEXT VALUE FOR sq sur deux tables, les valeurs se sont réparties 1 et 3 dans l'une et 2 dans l'autre. On l'utilise quand il faut le numéro avant d'insérer, ou partagé ; pour la clé d'une table, IDENTITY.

Recommandation
datetime2 pour les lectures d'horloge et datetimeoffset pour les instants, decimal pour l'argent, bit en sachant que c'est un nombre, NEWSEQUENTIALID() ou un entier si la clé cluster est un GUID, et le type json seulement si ton serveur le plus ancien est en 2025. Pour le texte, « Encodage et collations (SQL Server) ».

Mots-clés : types, datetime, datetime2, datetimeoffset, date, time, bit, boolean, booléen, money, decimal, float, uniqueidentifier, guid, newsequentialid, rowversion, timestamp, varchar, nvarchar, max, json, isjson, hierarchyid, geography, sql_variant, identity, sequence, unsigned

Comment lire un Msg, ce que dit son niveau, les numéros qu'on voit tous les jours, quelles erreurs annulent la transaction et lesquelles non, TRY…CATCH avec THROW, et où se trouve la raison d'un 18456.

S'applique à : SQL Server 2022+

Si tu viens de MySQL, il y a aussi des numéros ici —pas de SQLSTATE comme dans PostgreSQL—, mais chaque erreur apporte quatre données et toutes comptent. C'est ainsi que l'affiche Calíope, sur une ligne ; sqlcmd le coupe en deux :

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

- Msg — le numéro. C'est ce qu'on cherche et ce contre quoi on programme.
- Level — la sévérité, de 0 à 25. Elle dit à qui revient la faute et ce qui arrive à la connexion.
- State — un sous-code que le serveur utilise pour distinguer des causes qui partagent un numéro. Pour la plupart des erreurs il ne compte pas ; pour le 18456, c'est la seule chose qui dit pourquoi.
- Line — la ligne dans le lot (ce qui se trouve entre deux GO), ou dans la procédure : une erreur dans l'une d'elles sort comme Procedure p_falla, Line 3.

Le niveau, la première chose à regarder

Un niveau de 20 ou plus ne se lève pas à la main sans être sysadmin et sans WITH LOG : il donne Msg 2754. Calíope traite un niveau de 20 ou plus comme une connexion perdue.

Ceux qu'on voit tous les jours
Provoqués un par un contre SQL Server 2025 :

Pour voir le texte de n'importe quel numéro : SELECT text FROM sys.messages WHERE message_id = 1205 AND language_id = 1033;. Il y a 16 785 messages en anglais, en 22 langues.

Ce que chaque erreur annule : le piège pour qui vient de PostgreSQL
Dans PostgreSQL une erreur annule toute la transaction. Ici cela dépend de l'erreur, et la plupart n'annulent que l'instruction. Mesuré, sans rien activer :

BEGIN TRAN;
INSERT padre VALUES (10, 'd@x.es', 1);
INSERT padre VALUES (10, 'e@x.es', 1);  -- Msg 2627
INSERT padre VALUES (11, 'f@x.es', 1);
COMMIT;
-- les lignes 10 et 11 sont restées

La transaction est restée ouverte, le COMMIT a validé ce qui avait fonctionné et le lot a continué comme si de rien n'était. Ce qu'a fait chaque erreur mesurée :

- Seulement l'instruction : 2627, 2601, 547, 515, 2628, 8134, et aussi 1222 : une fois l'attente épuisée, @@TRANCOUNT valait toujours 1.
- Le lot et la transaction : 245 (conversion) a annulé la transaction et coupé le lot sans rien d'activé ; 1205 aussi, chez la victime ; un ROLLBACK dans un déclencheur donne Msg 3609 et coupe le lot.
- La connexion : niveau 20 ou plus.

SET XACT_ABORT ON change cela : avec lui, le même 2627 a annulé toute la transaction et coupé le lot (les lignes 20 et 21 ne sont pas restées). C'est ce qu'il faut mettre au début de chaque procédure qui ouvre une transaction.

TRY…CATCH, et ce qu'il n'attrape pas

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

Dans le CATCH, ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), ERROR_PROCEDURE() et ERROR_MESSAGE() renvoient les données de l'erreur (mesuré : 2627, 14, 1, 4, NULL). Et XACT_STATE() dit ce qu'on peut faire de la transaction : il a donné 1 sans XACT_ABORT (on peut la valider) et -1 avec (on ne peut que l'annuler).

Ce qu'un TRY n'attrape pas : un objet qui n'existe pas dans sa propre portée. SELECT * FROM tabla_que_no_existe dans un TRY est sorti en Msg 208 sans passer par le CATCH, parce qu'il échoue à la compilation ; dans un EXEC sp_executesql, il a été attrapé. Il n'attrape pas non plus ce qui est de niveau 10 ou moins.

THROW face à RAISERROR
- THROW; sans arguments, dans un CATCH, relance l'erreur d'origine : le numéro arrivé à l'extérieur était toujours 8134. La relancer avec RAISERROR(ERROR_MESSAGE(), 16, 1) l'a transformée en 50000, et le numéro s'est perdu.
- RAISERROR n'arrête pas le lot : ce qui venait après s'est exécuté. THROW, si.
- THROW 50001, 'texte', 1 lève une erreur à toi, toujours de niveau 16 et avec un numéro à partir de 50000. RAISERROR accepte en plus un numéro enregistré avec sp_addmessage et remplit ses arguments : RAISERROR(50100, 16, 1, 42) a donné « El pedido 42 no existe ».

Le texte change avec la langue ; le numéro, non
Avec SET LANGUAGE Spanish, le 8134 est arrivé comme « Error de división entre cero. » : le même numéro avec un autre texte. La langue est celle de la session, et Calíope se connecte avec la langue par défaut du login. C'est pourquoi, dans le code et dans tes propres journaux, on compare le numéro, jamais le texte.

Le 18456 : la raison est dans le journal du serveur
Une connexion refusée arrive toujours au client de la même façon : Msg 18456, Level 14, State 1, « Login failed for user '…' ». Mesuré avec trois causes différentes, le client a vu State 1 les trois fois. Le vrai état n'est que dans le journal du serveur :

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

Quand la base demandée à la connexion n'existe pas, un 4060 arrive avant le 18456 (« Cannot open database … requested by the login »), et celui-là donne bien la raison. Si quelqu'un « n'arrive pas à entrer », le journal du serveur distingue en une seconde ce qui, de l'extérieur, est la même phrase.

Ce qu'affiche Calíope
Quand une instruction échoue, Calíope affiche dans l'onglet des résultats le message du serveur en entier, sous la forme Msg, Level, State, Line ci-dessus, et tous les messages, un par ligne : un CREATE TABLE avec une contrainte répétée apporte le 2714 puis le 1750, et les deux disent quelque chose. Ces erreurs restent dans leur onglet. Celles qui empêchent de se connecter —le 18456, le 4060— et celles de niveau 20 ou plus, qui coupent la connexion, arrivent en plus au journal des erreurs de Calíope. Les messages informatifs —un PRINT, un RAISERROR de niveau 10— ne sont pas affichés.

Recommandation
SET XACT_ABORT ON et TRY…CATCH avec IF @@TRANCOUNT > 0 ROLLBACK; THROW; dans toute procédure qui ouvre une transaction. Dans l'application, réessayer le 1205 —la transaction entière, que le serveur a déjà annulée— et ne pas réessayer les niveaux 14 à 16, qui relèvent de la donnée ou du programme. Toujours garder le numéro, jamais le texte. Et face à un 18456, lire le journal du serveur avant de toucher au mot de passe.

Mots-clés : erreur, msg, niveau, sévérité, level, state, 18456, 1205, 2627, 2601, 547, 208, 102, 2628, 8152, 515, 229, 916, 4060, 1222, try, catch, throw, raiserror, xact_abort, xact_state, error_number, sys.messages, xp_readerrorlog, nouvelle tentative

L'index cluster qui est la table, le Key Lookup et quand INCLUDE le supprime, les index filtrés, les colonnes calculées au lieu des index sur expression, le columnstore, FILLFACTOR et la fragmentation, et les index que personne n'utilise.

S'applique à : SQL Server 2022+

Si tu viens de MySQL, ce qui ressemble le plus est InnoDB : ici aussi la table est un index. Ce qui change, c'est que cet index —l'index cluster— c'est toi qui le choisis et il n'a pas à être la clé primaire, qu'une table peut ne pas en avoir, et qu'à la place il n'y a ni index de préfixe ni index invisibles, mais des index filtrés, des colonnes calculées et le columnstore.

Tout ce qui suit est mesuré sur une table de 200 000 lignes et environ 32 Mo.

La table est l'index cluster
PRIMARY KEY crée un index cluster si la table n'en a pas encore : les lignes sont stockées triées par cette clé. Une table sans index cluster est un tas (HEAP dans sys.indexes) : les lignes vont là où elles tiennent.

Ce qu'on ne voit pas depuis MySQL, c'est le prix : la clé cluster voyage dans chaque index non cluster, parce que c'est ainsi que l'index retrouve la ligne. Le même index sur cliente_id a occupé 2 952 kB avec une clé cluster int et 5 416 kB avec une uniqueidentifier. Une clé cluster étroite et croissante rend tous les autres index moins chers ; pourquoi NEWID() est une mauvaise clé cluster, le sujet sur les types de données le raconte.

Key Lookup, et ce que corrige INCLUDE
Un index non cluster ne porte que ses colonnes et la clé cluster. Si la requête en demande une autre, chaque ligne trouvée oblige à revenir à la table : c'est le Key Lookup.

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

CREATE INDEX ix_cliente_inc ON dbo.pedidos (cliente_id) INCLUDE (total);
-- Index Seek (ix_cliente_inc) et rien d'autre : 3 lectures logiques

INCLUDE stocke la colonne dans les feuilles de l'index sans trier par elle. Ici l'index est passé de 2 952 kB à 4 752 kB.

Le Key Lookup se paie ligne par ligne, donc il cesse d'être rentable très tôt. Mesuré : avec 992 lignes (0,5 % de la table) le plan utilisait encore l'index et le lookup ; avec 1 483 (0,7 %) il parcourait déjà toute la table. Un index « qu'on n'utilise pas » est souvent un index auquel il manque un INCLUDE.

La règle du préfixe gauche
Avec un index sur (fecha, cliente_id) et WHERE cliente_id = 77, SQL Server ne peut pas descendre dans l'index. Il peut, comme PostgreSQL, le parcourir en entier si c'est moins cher que la table : Index Scan et 809 lectures logiques. Ce n'est pas un Index Seek, et l'ordre des colonnes compte toujours.

Index filtrés
Un index peut porter un WHERE, et alors il n'indexe que ce qui le satisfait :

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

Avec les commandes en attente à 5 %, il a occupé 160 kB, contre 2 952 kB pour l'index complet sur la même colonne. Le piège, c'est que le plan doit servir pour n'importe quelle valeur : avec le littéral WHERE estado = 'pendiente' il l'a utilisé ; avec une variable (WHERE estado = @e), non, et le forcer avec WITH (INDEX(ix_pend)) donne Msg 8622.

Et un usage qui surprend quand on vient de MySQL : dans SQL Server un index unique n'admet qu'un seul NULL. CREATE UNIQUE INDEX ux_email ON dbo.pedidos (email) avec deux e-mails à NULL donne Msg 1505 (« The duplicate key value is (<NULL>) »). La sortie, c'est l'index filtré :

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

Pas d'index sur expression : des colonnes calculées
Une fonction sur la colonne du WHERE empêche de descendre dans l'index. Sur les mêmes 14 400 lignes d'un mois :

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

Quand l'expression ne peut pas se réécrire en intervalle, on indexe une colonne calculée :

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

SELECT id FROM dbo.pedidos WHERE LOWER(email) = 'u77@ejemplo.com';
-- Index Seek (ix_email_lower), sans nommer la colonne calculée

Pour créer cet index, puis pour écrire dans la table, la session a besoin de QUOTED_IDENTIFIER et ANSI_NULLS activés ; sqlcmd désactive le premier si on ne lui passe pas -I, et alors ça échoue avec Msg 1934.

Columnstore, pour ce qui agrège
Un index columnstore stocke chaque colonne à part et compressée. La même table a occupé 35 520 kB avec son index cluster normal et 7 104 kB en CLUSTERED COLUMNSTORE. Un SELECT estado, SUM(total) … GROUP BY estado a lu 4 440 pages et consommé 19 ms de CPU sur la première, et 147 pages et 2 ms sur la seconde. Pour chercher une ligne par sa clé, un index normal reste meilleur ; l'habitude sur une table de travail est de lui ajouter un NONCLUSTERED COLUMNSTORE avec les colonnes qu'on agrège (celui de quatre colonnes a occupé 3 128 kB).

Les clés étrangères ne s'indexent pas toutes seules
InnoDB crée un index pour chaque clé étrangère ; SQL Server, non. Mesuré : après cliente_id int REFERENCES dbo.clientes(id), la table fille n'avait que l'index de sa clé primaire. Sans cet index, chaque DELETE dans la table parente parcourt toute la table fille pour vérifier la contrainte.

Fragmentation et FILLFACTOR

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

L'index sur cliente_id, avec des valeurs au hasard, mesuré pas à pas :

FILLFACTOR laisse de la place dans chaque page pour que les insertions tiennent sans la couper : ça coûte de l'espace dès le premier jour et évite la fragmentation ensuite. Il reste enregistré dans l'index (sys.indexes.fill_factor), donc les REBUILD suivants le répètent. REORGANIZE compacte sans bloquer et peut être interrompu ; REBUILD refait l'index entier et, sans ONLINE, bloque la table pendant toute sa durée.

Reconstruire sans arrêter la table

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

ONLINE = ON est de l'édition Enterprise (et de la Developer, qui est la même, et d'Azure SQL) : en Standard il n'existe pas, et un REBUILD arrête les écritures pendant toute sa durée —et aussi les lectures, si c'est l'index cluster—. RESUMABLE = ON permet de le mettre en pause et de reprendre plus tard, et exige ONLINE = ON (sans lui, Msg 11438). Et deux cas où ONLINE = ON échoue : avec un index spatial sur la table et avec ALTER INDEX ALL.

Pas d'index invisibles : DISABLE
ALTER INDEX … DISABLE est ce qui s'en rapproche le plus, mais ce n'est pas pareil : il efface les données de l'index et ne garde que sa définition, donc revenir en arrière est un REBUILD complet. Et désactiver l'index cluster rend la table illisible : SELECT COUNT(*) donne Msg 8655, et au passage il désactive tous les index non cluster, avec un avertissement pour chacun.

Ceux que personne n'utilise, et ceux qui manquent

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

La prudence est plus grande que dans PostgreSQL : cette vue vit en mémoire et se vide au redémarrage du serveur. Mesuré : j'ai redémarré le conteneur et la vue est revenue sans une seule ligne de la table. Désactiver l'index la remet aussi à zéro ; un REBUILD, non. Un index qui affiche 0 lecture un lundi peut être un index que personne n'a eu le temps d'utiliser.

L'autre moitié, ce sont ceux qui manquent : l'optimiseur note dans sys.dm_db_missing_index_details l'index qu'il aurait voulu. Après une requête sur cliente_id et estado, il a proposé [cliente_id], [estado] avec un impact estimé de 99,6 %. C'est une piste, pas un ordre : il ne regarde pas les index que tu as déjà, ne pondère pas les écritures et vit dans la même mémoire qui se vide.

Recommandation
Une clé cluster étroite et croissante ; un index pour chaque clé étrangère qui sert à chercher ou à supprimer ; INCLUDE avant un autre index quand le plan montre un Key Lookup ; et avant de supprimer un index « inutilisé », regarder depuis quand le serveur a démarré (sqlserver_start_time dans sys.dm_os_sys_info).

Mots-clés : index, cluster, clustered, non cluster, nonclustered, tas, heap, key lookup, include, index filtré, colonne calculée, columnstore, fillfactor, fragmentation, reorganize, rebuild, online, resumable, disable, dm_db_index_usage_stats, dm_db_missing_index_details, dm_db_index_physical_stats

Les limites qu'on atteint vraiment —8 060 octets par ligne, 900 et 1 700 octets de clé d'index, 1 024 colonnes, 128 caractères de nom, 2 100 paramètres—, celles qui ne font qu'avertir à la création de la table et échouent plus tard, et celles de l'édition Express.

S'applique à : SQL Server 2022+

Les limites de SQL Server ressemblent peu à celles d'InnoDB, et celles qui mordent au quotidien ne sont pas les grandes. Je les ai provoquées une à une contre SQL Server 2025.

Celles qu'on atteint vraiment

La ligne tient dans une page, sauf ce qui est variable
Une page fait 8 Ko et une ligne doit y tenir : 8 060 octets. Avec des colonnes de largeur fixe, le serveur le vérifie à la création de la table : deux char(5000) donnent Msg 1701, qui dit que la ligne minimale ferait 10 007 octets.

Avec des colonnes variables, non. J'ai stocké deux varchar(8000) pleins dans la même ligne —16 000 octets— sans aucune erreur : ce qui ne tient pas part dans des pages à part, ROW_OVERFLOW_DATA. sys.allocation_units a montré 2 pages de ligne et 2 de débordement. Ça marche, mais lire cette ligne coûte une lecture de plus par colonne débordée, et le schéma ne signale pas que ça arrive.

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

varchar(max), nvarchar(max) et varbinary(max) vont jusqu'à 2 Go et sortent de la ligne par le même chemin. Pourquoi varchar(8001) n'existe pas et qu'il faut sauter à max, c'est le sujet des types de données qui le raconte.

La limite qui ne fait qu'avertir : la clé d'index
C'est celle qui surprend le plus. Un index sur une colonne plus large que la limite est créé, avec un avertissement :

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

Un avertissement n'arrête pas un déploiement. La table fonctionne jusqu'au jour où quelqu'un stocke une valeur longue : 900 octets sont passés et 901 ont donné Msg 1946. Sur un index non cluster la limite est de 1 700 octets, et avec nvarchar cela fait 850 caractères, pas 1 700, parce que chacun occupe deux octets. Une PRIMARY KEY sur varchar(1000) est un index cluster et se comporte pareil.

Si une colonne longue a besoin d'une recherche par égalité, ce qui marche est d'indexer un résumé : une colonne calculée avec CHECKSUM ou HASHBYTES, indexée, en comparant aussi la valeur complète.

Les noms : une erreur, pas une troncature
Un nom de 129 caractères donne Msg 103 et l'instruction ne s'exécute pas. PostgreSQL tronque à 63 octets et continue ; SQL Server ne laisse pas passer le nom. Celui d'une table temporaire locale est plus court, 116, parce que le serveur y ajoute un suffixe pour distinguer les sessions.

Les paramètres : 2 100, et 2 098 en pratique
Une procédure accepte 2 100 paramètres et un appel ne peut pas en passer plus. Mais les pilotes et les ORM envoient les requêtes paramétrées par sp_executesql, et là l'instruction et la déclaration comptent aussi : avec 2 098 paramètres ça a marché et avec 2 099 ça a donné Msg 180. C'est l'erreur typique d'un WHERE id IN (@p1, @p2, …) généré à partir d'une liste qui grandit un jour. La solution n'est pas de découper la liste : c'est de la passer comme table (un paramètre table, ou une table temporaire) et de faire un JOIN. Un IN avec 60 000 littéraux écrits dans l'instruction, lui, a marché.

Plus de colonnes : SPARSE avec un jeu de colonnes
1 025 colonnes donnent Msg 1702. Avec des colonnes SPARSE et un COLUMN_SET FOR ALL_SPARSE_COLUMNS, j'ai créé une table de 2 002 colonnes ; les mêmes colonnes sans le jeu ont donné le même Msg 1702. La documentation appelle ça une « table large » et monte à 30 000 colonnes, mais la ligne doit toujours tenir dans 8 060 octets.

Imbrication : 32 niveaux
Une procédure qui s'appelle elle-même est arrivée à @@NESTLEVEL 32 et le niveau suivant a donné Msg 217. Procédures, fonctions, triggers et vues comptent ensemble : un trigger qui met à jour une autre table avec trigger consomme des niveaux sans que ça se voie.

Les grandes, qui ne sont presque jamais le problème
Je ne les ai pas mesurées —le testbed est l'édition Developer et les mesurer demanderait de remplir un disque— ; elles viennent avec le chiffre de la documentation de Microsoft :

- Taille d'une base : 524 272 To. D'un fichier de données : 16 To.
- Lignes par table : pas de limite autre que le stockage.
- Taille d'un lot : 65 536 fois la taille de paquet réseau (4 096 octets par défaut, soit 256 Mo).
- Connexions : 32 767 (@@MAX_CONNECTIONS), et user connections à 0 signifie qu'il n'y a pas d'autre plafond.

Celles que posent les éditions
Là, il y a des limites qu'on atteint, et elles viennent de la licence, pas du moteur. Je ne les ai pas mesurées non plus : le testbed est Developer, qui n'en a aucune.

- Express : 10 Go par base dans SQL Server 2022 et 50 Go en 2025 ; environ 1,4 Go de mémoire pour le pool de mémoire tampon ; le plus petit d'un socket ou de quatre cœurs. Arrivée à la taille, la base cesse de grandir et les écritures échouent.
- Standard : pas de plafond de taille de base, mais un plafond de mémoire et de cœurs, et sans certaines fonctions d'Enterprise, comme REBUILD avec ONLINE = ON.

SELECT SERVERPROPERTY('Edition') dit laquelle est celle du serveur auquel vous êtes connecté.

Recommandation
En surveiller trois : la clé d'index quand on indexe une colonne de texte, parce qu'elle ne fait qu'avertir ; les 2 098 paramètres quand quelque chose génère des listes IN ; et la taille de base si le serveur est Express. Le reste, on l'apprend par le numéro d'erreur, qui dit toujours quelle est la limite.

Mots-clés : limites, maximum, 8060, ligne, page, row_overflow_data, débordement, clé d'index, 900 octets, 1700 octets, 1024 colonnes, sparse, column_set, 128 caractères, identifiant, table temporaire, 2100 paramètres, sp_executesql, nestlevel, imbrication, express, 10 go, 50 go, édition, msg 1946, msg 1702, msg 180

Pourquoi ici un SELECT attend un UPDATE, ce que change READ_COMMITTED_SNAPSHOT, comment voir qui bloque qui, où est gardé le graphe d'un 1205 et quand des verrous de ligne deviennent un verrou de table.

S'applique à : SQL Server 2022+

Si tu viens de MySQL ou de PostgreSQL, c'est la première chose qui surprend : dans SQL Server, par défaut, un SELECT attend un UPDATE. InnoDB et PostgreSQL lisent la dernière version validée de la ligne et continuent ; SQL Server, en READ COMMITTED tout court, demande un verrou partagé et attend que l'autre valide ou annule. Tout ce que je raconte ici, je l'ai mesuré contre SQL Server 2025, avec deux sessions ouvertes en même temps.

Un SELECT qui attend

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

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

La session B est restée bloquée 7,5 s, exactement le temps que A a mis à faire ROLLBACK, en attente de LCK_M_S sur la clé de cette ligne. Et A n'a pas besoin de faire quoi que ce soit : une session qui a ouvert une transaction, écrit, puis s'est tue —status sleeping, open_transaction_count 1— bloque tout autant. C'est le cas de tous les jours : une application qui a oublié son COMMIT.

READ_COMMITTED_SNAPSHOT : lire comme les deux autres

ALTER DATABASE ma_base SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

Avec cette option, READ COMMITTED lit la dernière version validée de la ligne, et la même lecture est revenue aussitôt avec l'ancienne valeur (100, pas le 50 non validé). Trois choses avant de l'activer :
- Elle appartient à la base, pas à la session, et elle est désactivée d'origine : dans une base toute neuve, dans model et dans la demo de mon labo, is_read_committed_snapshot_on vaut 0.
- Sans WITH ROLLBACK IMMEDIATE, l'ALTER attend qu'il ne reste plus aucune autre session dans la base : avec une connectée, il a attendu 14,5 s, le temps qu'elle parte. Avec, il met les autres dehors et annule leurs transactions.
- Elle ne change que ceux qui lisent. Deux écritures sur la même ligne continuent de s'attendre : l'UPDATE de la session B a attendu comme avant.

Qui bloque qui
On le demande pendant que le blocage dure :

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

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

La première répond à la vraie question : blocking_session_id est la session qui retient ce que celle-ci demande. La seconde montre toute l'échelle : qui met à jour une ligne tient X sur la clé et IX sur la page et sur la table, et qui attend apparaît avec request_status WAIT. Contrairement à PostgreSQL, ici les verrous de ligne apparaissent bien dans la liste, un par ligne.

La liste des Processus de Calíope montre l'attente dans la colonne État —une session bloquée apparaît avec LCK_M_S, LCK_M_U ou LCK_M_X—, mais pas qui la bloque : c'est la première requête qui le dit. Et Kill Connection est ici le seul moyen de libérer le verrou d'un autre : KILL ferme la session entière et annule sa transaction, parce que SQL Server ne permet pas d'annuler seulement la requête d'une autre session.

UPDLOCK, le FOR UPDATE d'ici
Il n'y a pas de SELECT … FOR UPDATE. C'est un indicateur qui fait ce travail :

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

Il prend un verrou U : une lecture normale d'une autre session est passée sans attendre, et une autre lecture avec UPDLOCK est restée en attente. Et il évite l'interblocage le plus bête de tous : deux sessions en REPEATABLE READ qui lisent la même ligne puis la mettent à jour ont fini en 1205 ; avec UPDLOCK sur la lecture, les deux ont terminé, l'une après l'autre.

Ici aussi, on attend indéfiniment
@@LOCK_TIMEOUT vaut -1, c'est-à-dire sans limite : comme le lock_timeout de PostgreSQL, et pas comme les 50 s d'InnoDB. On le règle par session, en millisecondes :

SET LOCK_TIMEOUT 3000;

Quand il se déclenche, on reçoit un Msg 1222, et il n'annule rien : la transaction reste ouverte et c'est à celui qui le reçoit de l'annuler. Ce qu'annule chaque erreur, le sujet « Erreurs (SQL Server) » le raconte.

Ne pas attendre, exprès

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

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

NOWAIT échoue aussitôt avec le même 1222, et le lot continue. READPAST est le SKIP LOCKED d'ici : avec la ligne 3 verrouillée par une autre session, il a renvoyé 1, 2, 4 et 5. C'est ainsi qu'on répartit une file de travail entre plusieurs consommateurs sans qu'ils s'attendent.

NOLOCK lit ce qui n'a jamais existé

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

Avec une autre session au milieu d'un UPDATE qu'elle a ensuite annulé, cette lecture a renvoyé 50 : un solde qui n'a jamais été validé. NOLOCK, c'est READ UNCOMMITTED, et ce n'est pas « lire plus vite » : c'est lire ce qu'une autre transaction peut encore jeter. La documentation ajoute que si des pages se scindent pendant la lecture, il peut sauter des lignes ou les lire deux fois ; je ne l'ai pas reproduit. Si ce qu'on cherche, c'est que la lecture n'attende pas, la réponse est READ_COMMITTED_SNAPSHOT, qui n'attend pas et ne voit que ce qui est validé.

L'étreinte fatale

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

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

L'une des deux reçoit Msg 1205 —niveau 13, « … has been chosen as the deadlock victim. Rerun the transaction. »— et perd sa transaction et le reste du lot ; l'autre valide. Ce n'est pas détecté instantanément : sur six essais, la victime a mis entre 0,1 et 4,8 s à tomber après la fermeture du cycle. La documentation explique cet écart : un moniteur cherche les cycles toutes les 5 s, et plus souvent juste après en avoir trouvé un.

On peut choisir laquelle meurt. Avec SET DEADLOCK_PRIORITY HIGH dans la session A, la victime a été B les trois fois ; LOW convient à un traitement par lots qui peut se relancer. Un 1205 n'est pas une panne : l'application doit relancer cette transaction.

Le graphe est déjà enregistré
Rien à activer : la session Extended Events system_health, qui tourne d'origine, garde chaque interblocage avec son graphe —qui est mort, quelle instruction chacun exécutait et quelle clé de quel index il attendait.

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

Deux choses mesurées. Le fichier est en retard : juste après en avoir provoqué six, il en avait deux ; quelques secondes plus tard, tous. Et le ring_buffer de la même session, qui les a tout de suite, vit en mémoire : après un redémarrage du serveur, il avait les nouveaux et avait perdu l'ancien, que le fichier gardait toujours.

De la ligne à la table : l'escalade
Chaque verrou occupe de la mémoire, et quand une instruction en accumule trop sur une table, SQL Server les remplace par un seul sur la table entière. J'ai mis à jour les N premières lignes d'une table de 20 000 et compté :
- Jusqu'à 6 200 lignes, 6 200 verrous X de clé.
- À partir de 6 300, un seul : X sur la table, sans passer par la page. Et une autre session qui visait la ligne 20 000, que personne n'avait touchée, a attendu jusqu'à son LOCK_TIMEOUT.

Le chiffre de la documentation est 5 000 ; ici, le saut est venu un peu plus tard. Un SELECT de 8 000 lignes en REPEATABLE READ a fini de la même façon, avec un S sur la table. Cela se règle par table :

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

Avec ça, le même UPDATE des 20 000 lignes a gardé 20 000 verrous de clé et aucun de table. TABLE est la valeur par défaut ; AUTO, selon la documentation, escalade vers la partition sur une table partitionnée. La désactiver n'est pas gratuit —chaque verrou, c'est de la mémoire du serveur—, et la sortie habituelle est autre : écrire par lots de moins de 5 000 lignes.

Verrous d'application
L'équivalent des verrous consultatifs de PostgreSQL : un nom que le serveur garde pour que deux processus de ton application ne fassent pas la même chose en même temps.

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

Avec la ressource tenue par une autre session, il a renvoyé -1 au lieu d'attendre ; 0 veut dire « accordé ». Il ne lève aucune erreur : il faut regarder le nombre. Avec @LockOwner = 'Transaction', la valeur par défaut, il se libère tout seul à la fin de la transaction, et le demander en dehors d'une transaction a renvoyé -999.

Recommandation
Toucher les lignes toujours dans le même ordre et garder des transactions courtes, comme avec n'importe quel moteur. Ce qui est propre à SQL Server tient en trois choses : activer READ_COMMITTED_SNAPSHOT dès que l'application le supporte, c'est ce qui empêche la lecture d'attendre l'écriture et ôte la tentation de NOLOCK ; UPDLOCK sur la lecture qui finira en UPDATE ; et la relance du 1205 écrite avant la mise en production.

Mots-clés : verrou, lock, deadlock, interblocage, blocage, 1205, 1222, lck_m_s, blocking_session_id, dm_tran_locks, dm_exec_requests, read_committed_snapshot, rcsi, nolock, readpast, updlock, nowait, lock_timeout, deadlock_priority, escalade, lock_escalation, system_health, sp_getapplock

Le DDL est transactionnel et ce qui coûte, c'est le verrou Sch-M et la file qu'il crée, quels ALTER réécrivent la table, ONLINE, RESUMABLE et WAIT_AT_LOW_PRIORITY, et pourquoi WITH NOCHECK n'allège pas le verrou ici.

S'applique à : SQL Server 2022+

Si tu viens de MySQL, d'abord ceci : ici le DDL est transactionnel, comme dans PostgreSQL. Si tu viens de PostgreSQL, ce qui change, c'est le nom du verrou et quelques outils qui n'existent pas là-bas. Tout ce que je raconte ici, je l'ai mesuré contre SQL Server 2025, sur une table de 200 000 lignes.

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

Après ce ROLLBACK, il ne restait rien : ni la colonne, ni l'index, ni la table, et la colonne s'appelait de nouveau nota. Une migration entière tient dans une transaction. Les exceptions que j'ai trouvées : CREATE DATABASE et ALTER DATABASE (Msg 226) et un index RESUMABLE (Msg 574) ne peuvent pas y entrer.

Ce qui coûte, c'est le Sch-M, et la file qu'il crée
Tout ALTER TABLE demande un verrou de modification de schéma, Sch-M, incompatible avec tout, un SELECT compris. Le danger n'est pas l'ALTER, qui peut durer quelques millisecondes : c'est l'attente. Avec une autre session au milieu d'une transaction qui avait touché une ligne :
- L'ALTER TABLE … ADD a attendu 7 s, avec le wait_type LCK_M_SCH_M.
- Un SELECT d'une autre ligne, lancé derrière, a attendu 6 s avec LCK_M_SCH_S, et son blocking_session_id n'était pas la transaction : c'était l'ALTER.

C'est ainsi qu'un changement d'une milliseconde arrête toute une application. Le remède est le même que dans PostgreSQL :

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

Au bout de 3 s, l'ALTER a reçu Msg 1222, et le SELECT de derrière est passé au même instant. On réessaie plus tard.

Ce qui réécrit la table et ce qui ne la réécrit pas
Mesuré sur 200 000 lignes (1 364 pages). Le témoin est le journal de transactions écrit par chaque instruction :

Comment la lire :
- Une valeur par défaut qui est la même pour toutes les lignes —une constante, ou SYSDATETIME(), évaluée une seule fois— est gardée dans les métadonnées et ne touche pas les lignes. NEWID() en donne une différente à chaque ligne, ce qui oblige à les écrire toutes.
- Élargir un varchar est gratuit. Le rétrécir, changer de famille (nvarchar, max), agrandir un entier, passer à NOT NULL ou changer la taille sur disque d'un decimal réécrivent. decimal(10,2) et decimal(12,2) occupent la même place, 9 octets, et c'est pour ça que celui-là n'a rien réécrit.
- Ce qui réécrit laisse la table deux fois plus grosse. Après chacune de ces instructions, la table est passée de 1 364 à 2 723 pages et y est restée : l'ancienne colonne occupe toujours sa place dans chaque ligne. Après int → bigint, un ALTER TABLE dbo.pedidos REBUILD l'a ramenée à 1 495. Et un DROP COLUMN est instantané parce qu'il ne libère rien : 1 364 pages avant et après, et 1 075 après le REBUILD.

Deux obstacles que PostgreSQL ne pose pas :
- Une colonne indexée ne change pas de type. Avec un index sur estado, passer à varchar(100) a marché, parce que ça ne réécrit pas ; varchar(30) et nvarchar(100) ont donné Msg 5074 (« The index 'ix_estado' is dependent on column 'estado' »). Il faut supprimer l'index, changer la colonne et le recréer.
- Un DEFAULT est une contrainte nommée, et tant qu'elle existe, la colonne ne peut pas être supprimée : le même Msg 5074, qui nomme une contrainte DF__pedidos__moneda__… que SQL Server a baptisée tout seul. On la supprime d'abord avec ALTER TABLE … DROP CONSTRAINT, et c'est pour ça qu'il vaut mieux la nommer en la créant.

La documentation ajoute une condition d'édition : qu'un ADD COLUMN … NOT NULL DEFAULT ne touche que les métadonnées est une fonction d'Enterprise ; en Standard, il réécrit la table. Mon labo est en Developer, qui a tout ce qu'a Enterprise, donc ça, je n'ai pas pu le mesurer.

Index en ligne : ONLINE = ON

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

Sur 3 000 000 de lignes, la reconstruction en ligne a duré 47 s, et un UPDATE lancé au milieu s'est terminé en une demi-seconde. Sans ONLINE, la table reste verrouillée tout ce temps. Un ALTER COLUMN l'accepte aussi (ALTER TABLE … ALTER COLUMN n bigint NOT NULL WITH (ONLINE = ON)) : il a pris 395 ms au lieu de 228, mais a laissé la table à 1 483 pages au lieu de la doubler.

Trois limites :
- Avec un index spatial sur la table, ALTER INDEX ALL … WITH (ONLINE = ON) échoue avec Msg 153, et l'index spatial lui-même ne se reconstruit pas en ligne non plus. La clé primaire et les index ordinaires, un par un, oui.
- ONLINE ne veut pas dire sans verrou : au démarrage il demande un verrou S sur la table et, d'après la documentation, un bref Sch-M à la fin. Avec une transaction ouverte sur la table, le REBUILD en ligne est resté à attendre son S, et un UPDATE arrivé derrière a attendu 6,6 s dans la file (LCK_M_IX). Un SELECT, lui, est passé.
- D'après la documentation, ONLINE = ON est réservé à Enterprise : en Standard, c'est une erreur.

WAIT_AT_LOW_PRIORITY : attendre sans créer de file
PostgreSQL n'a pas ça. La reconstruction attend son verrou dans une file à part, sans gêner ceux qui arrivent derrière :

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

Avec la même transaction ouverte, l'UPDATE de derrière est passé en 0,5 s, et le REBUILD apparaissait en attente sur LCK_M_S_LOW_PRIORITY. Une fois MAX_DURATION écoulé, c'est ABORT_AFTER_WAIT qui décide :
- SELF : le REBUILD abandonne avec Msg 1222 et la transaction continue. C'est le choix prudent.
- BLOCKERS : au bout de la minute, la session qui bloquait a été déconnectée (« … disconnected because of a high priority DDL operation ») et sa transaction annulée ; le REBUILD s'est terminé.
- NONE : il attend en basse priorité sans limite.

ALTER INDEX … REBUILD l'accepte et, sur mon 2025, CREATE INDEX … WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY …)) aussi. ALTER TABLE non : avec ADD, ALTER COLUMN ou ADD CONSTRAINT, c'est une erreur de syntaxe (Msg 155). Pour ceux-là, il ne reste que LOCK_TIMEOUT.

Index avec reprise : RESUMABLE = ON

CREATE INDEX ix_relleno ON dbo.grande (relleno, g) WITH (ONLINE = ON, RESUMABLE = ON);
-- depuis une autre session :
ALTER INDEX ix_relleno ON dbo.grande PAUSE;
SELECT name, state_desc, percent_complete FROM sys.index_resumable_operations;
ALTER INDEX ix_relleno ON dbo.grande RESUME;

J'ai mis la création en pause au bout de 4 s : la session qui l'avait lancée a été déconnectée (Msg 1219), sys.index_resumable_operations indiquait PAUSED et 5 %, et l'index n'existait pas encore —il n'apparaissait pas dans sys.indexes—. RESUME a terminé le reste en 45 s. Ça permet de répartir un index énorme sur plusieurs fenêtres de maintenance. Il exige ONLINE = ON (sans lui, Msg 11438), n'entre pas dans une transaction (Msg 574) et, d'après la documentation, depuis SQL Server 2022 il vaut aussi pour ALTER TABLE … ADD CONSTRAINT d'une clé primaire ou unique ; sur 2025, je l'ai vérifié avec une clé unique.

WITH NOCHECK : ici, il n'allège pas le verrou
Le motif NOT VALID + VALIDATE de PostgreSQL a ici son jumeau, mais il ne fait pas la même chose. Sur 3 000 000 de lignes :

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

L'ajouter d'un coup a pris 375 ms. Et les trois ont pris Sch-M sur la table, validation comprise : couper en deux répartit le parcours, mais il n'y a pas de verrou plus faible pour la seconde moitié, comme il y en a un dans PostgreSQL.

Et ça laisse un piège : une contrainte ajoutée WITH NOCHECK est non approuvée (is_not_trusted = 1), et l'optimiseur ne s'y fie pas. Avec un CHECK (importe >= 0) approuvé, WHERE importe < 0 n'a même pas lu la table ; non approuvé, 1 364 lectures logiques. C'est pareil quand on la désactive puis la réactive sans WITH CHECK —ALTER TABLE … CHECK CONSTRAINT ck_importe l'a laissée non approuvée—, ce que font souvent les chargements massifs. Pour les trouver :

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

Dans Calíope
L'opération OPTIMIZE de Maintenance des tables est, dans SQL Server, un ALTER INDEX ALL … REBUILD sans ONLINE : elle verrouille la table pendant la reconstruction. Sur une grosse table en production, mieux vaut écrire dans l'éditeur le REBUILD WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY …)).

Recommandation
SET LOCK_TIMEOUT avant chaque ALTER TABLE ; la migration dans une transaction ; ONLINE = ON avec WAIT_AT_LOW_PRIORITY … SELF pour les index ; un vrai nom pour chaque DEFAULT ; et prudence avec les changements de type, qui réécrivent la table et la laissent deux fois plus grosse jusqu'au prochain REBUILD. S'il faut rétrécir une grosse colonne ou changer sa famille, il vaut souvent mieux ajouter la nouvelle, copier par lots et renommer avec sp_rename.

Mots-clés : ddl, alter table, migration, transactionnel, rollback, sch-m, lck_m_sch_m, lock_timeout, online, resumable, wait_at_low_priority, abort_after_wait, rebuild, réécriture, add column, alter column, drop column, with nocheck, is_not_trusted, sp_rename

Fonction et schéma de partition, ce que l'optimiseur élimine vraiment, pourquoi un index unique doit contenir la clé, SWITCH et TRUNCATE pour purger en quelques millisecondes, le CHECK approuvé qu'exige le chargement, et SPLIT et MERGE, qui ne sont gratuits que sur une partition vide.

S'applique à : SQL Server 2022+

Si tu viens de MySQL ou de PostgreSQL, c'est la forme qui change : ici une partition n'est pas une table mais un morceau de table, et la répartition est décrite par deux objets créés avant la table. Tout ce que je raconte ici, je l'ai mesuré sur SQL Server 2025, avec 300 000 ventes réparties sur trois ans.

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

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

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

La fonction pose les frontières et le schéma dit dans quel groupe de fichiers vit chaque morceau. Trois frontières donnent quatre partitions : tout ce qui précède 2024, une année dans chacune des deux suivantes, et tout ce qui vient à partir de 2026. Il n'y a ni LIST ni HASH : seulement des plages. Et inutile de prévoir l'avenir : ce qui tombe avant la première frontière ou après la dernière a toujours sa place.

RANGE RIGHT ou RANGE LEFT : de quel côté tombe la frontière
Avec RIGHT, la frontière appartient à la partition de droite, ce qui est naturel avec des dates : $PARTITION.pf_anio('2023-12-31') a renvoyé 1, et '2024-01-01', 2. Avec LEFT et les mêmes frontières, le 1er janvier est tombé dans la partition de 2023. Rien n'échoue : les données d'un jour finissent simplement dans la mauvaise année. $PARTITION sert aussi à compter :

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

L'élimination de partitions, c'est ce qu'on cherche
Le plan réel (SET STATISTICS XML ON, dans RunTimePartitionSummary) dit combien de partitions chaque requête a lues :

Deux choses à lire là-dedans :
- Avec des variables et des paramètres aussi, l'élimination a lieu, parce qu'elle se décide à l'exécution, pas à la compilation. C'est pourquoi le plan estimé ne donne pas de nombre : il montre [PtnId1000] >= RangePartitionNew(…) quand la clé est dans le filtre, et [PtnId1000] >= (1) AND [PtnId1000] <= (4) quand elle n'y est pas.
- Une fonction sur la colonne la cache, comme elle la cache à un index : YEAR(creado) a lu les quatre. La plage écrite à la main (>= '2024-01-01' AND < '2025-01-01') en lit une.

La règle de conception est la même que dans les autres moteurs : la clé de partition est la colonne sur laquelle tu filtres presque toujours. Si les requêtes ne la nomment pas, partitionner n'économise aucune lecture : ça les répartit.

Index alignés, et unicité
Un index créé sans ON hérite du schéma de la table : il est aligné, avec un morceau par partition. La conséquence est celle de PostgreSQL :

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

a renvoyé Msg 1908 : la colonne de partition doit faire partie de la clé d'un index unique. Ici il existe une issue que PostgreSQL n'a pas —le créer non aligné, ON [PRIMARY]—, mais elle a un prix : avec un tel index, le SWITCH ci-dessous ne fonctionne plus (Msg 7733). C'est pourquoi la clé primaire de dbo.ventas est (creado, id) et non (id).

SWITCH et TRUNCATE : purger une année en quelques millisecondes
Supprimer tout 2023, 100 009 lignes, mesuré dans une transaction :

SWITCH ne déplace aucune donnée : il change le propriétaire d'un morceau entier. C'est pourquoi il exige beaucoup de la table de destination, et chaque exigence a son erreur :
- Vide : avec une ligne, Msg 4905.
- Mêmes colonnes et mêmes index : avec une colonne en trop, Msg 4943.
- Et, d'après la documentation, dans le même groupe de fichiers que la partition.

Pour charger des données, il faut un CHECK approuvé
Voici la différence avec PostgreSQL, où le CHECK ne fait qu'économiser un parcours. Remettre la table dans sa partition sans lui ne prend pas plus de temps : c'est refusé, avec Msg 4982. Avec la contrainte, c'est entré en 9,5 ms :

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

Et elle doit être approuvée : désactivée puis réactivée sans WITH CHECK —ce que font beaucoup de chargements en masse—, le SWITCH a renvoyé Msg 4972. Après WITH CHECK CHECK CONSTRAINT, c'est entré. C'est ainsi qu'on charge une nouvelle période sans toucher la table vivante : on remplit une table à part, on lui pose son CHECK et on fait le SWITCH.

SPLIT et MERGE : gratuits seulement sur une partition vide
Les frontières se déplacent avec la fonction :

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

Couper une partition pleine déplace les lignes d'un côté de la frontière, et chacune écrit dans le journal : le coût grandit avec ce qui bouge. La bonne habitude est de garder toujours une partition vide à la fin et de la couper avant l'arrivée des données de la période suivante. Et chaque SPLIT consomme le NEXT USED : sans le redéclarer, le suivant a renvoyé Msg 7710 et n'a rien changé.

Maintenance par partition
On ne reconstruit que la partition qui change :

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

33 ms pour une partition (148 ms en ligne) contre 214 ms pour les quatre. Une année close ne reçoit plus d'écritures : aucune raison de la reconstruire chaque nuit.

L'escalade de verrous, par partition
Par défaut (LOCK_ESCALATION = TABLE), un UPDATE de 16 440 lignes de 2024 a escaladé vers un verrou X sur toute la table, et mettre à jour une ligne de 2025 depuis une autre session a renvoyé Msg 1222. Avec

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

l'escalade est restée au niveau de la partition (un X sur HOBT), et la ligne de 2025 a été mise à jour en 9 ms. La documentation prévient que AUTO peut amener des interblocages entre sessions qui escaladent sur des partitions différentes ; le reste de l'escalade est dans « Verrous et interblocages (SQL Server) ».

SWITCH prend aussi un Sch-M
Avec une transaction ouverte qui avait touché une seule ligne, le SWITCH est resté à attendre son Sch-M, et un SELECT d'une autre ligne, lancé derrière, a attendu dans la file derrière le SWITCH. Le remède est celui des index :

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

Le SELECT de derrière a fini en une demi-seconde, et le SWITCH a abandonné au bout de 61 s avec Msg 1222, sans toucher la transaction. Le reste de cette file est dans « Changements de schéma à chaud (SQL Server) ».

Limites et éditions
Une fonction admet 15 000 partitions : avec 15 000 frontières, Msg 7719. D'après la documentation, le partitionnement est dans toutes les éditions depuis SQL Server 2016 SP1, Express comprise.

Dans Calíope
Les lignes et la taille d'une table partitionnée additionnent toutes ses partitions. Le CREATE TABLE qu'écrit Calíope —celui qu'il montre comme DDL de la table et celui qu'enregistre Sauvegarde— est précédé de la fonction et du schéma de partition, chacun avec IF NOT EXISTS, et se termine par ON ps_anio (creado) ; un index qui n'est pas aligné, comme une clé primaire non cluster sur [PRIMARY], garde son propre ON. Restaurer cette sauvegarde dans une base vide la laisse partitionnée de la même façon, chaque ligne dans sa partition ; si la destination a déjà une fonction de ce nom, c'est celle-là qui sert, avec ses bornes. Comparaison de schémas regarde bien le partitionnement —où va la table et les bornes de chaque schéma—, mais son script ne partitionne ni ne départitionne une table qui existe déjà : cela revient à reconstruire son index cluster avec les données dedans, et il le laisse écrit en note pour que tu le fasses. Le plan que montre Calíope est le plan estimé, donc pour les partitions il donne la forme PtnId1000 ci-dessus ; combien il en a vraiment lu, seul le plan réel le dit, SET STATISTICS XML ON.

Recommandation
Partitionne selon ce que tu vas supprimer : le vrai gain, c'est de purger une période avec SWITCH ou TRUNCATE … WITH (PARTITIONS) au lieu d'un DELETE qui remplit le journal. Clé primaire et index uniques avec la clé de partition dedans, pour que tout reste aligné ; RANGE RIGHT avec des dates ; toujours une partition vide à la fin, pour la couper gratuitement ; et vérifie dans le plan réel que tes requêtes éliminent des partitions, au lieu de le supposer.

Mots-clés : partitionnement, partition, partition function, partition scheme, range right, range left, $partition, élimination de partitions, aligné, switch, truncate, split range, merge range, next used, lock_escalation, 1908, 4982, 7733

Lectures logiques plutôt que durées, le plan estimé face au plan réel, la détection de paramètres et ses remèdes mesurés, le cache de plans que lit Requêtes lentes, Query Store activé d'origine et les attentes qui disent vraiment quelque chose.

S'applique à : SQL Server 2022+

Si tu viens de MySQL ou de PostgreSQL, trois choses changent ici : le chiffre qu'on compare, ce sont les lectures logiques, pas le temps ; le plan est mis en cache et réutilisé avec d'autres valeurs, et c'est la cause de la moitié des « hier ça allait bien » ; et l'équivalent de pg_stat_statements est déjà là sans rien activer, mais il se perd au redémarrage. Tout ce que je raconte ici, je l'ai mesuré sur SQL Server 2025, avec 200 000 commandes.

Mesurer une requête : les lectures logiques

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

Chaque lecture logique est une page de 8 ko lue en mémoire. C'est le chiffre à comparer, parce que le temps varie avec la charge et avec le cache, et elle non. Pour les 20 commandes d'un client :
- Sans index : 6 085 lectures logiques —la table entière— et 12 ms.
- Avec un index sur cliente_id : 62 lectures.

Dans Calíope, ces lignes n'apparaissent pas : le serveur les envoie comme messages d'information, et l'éditeur affiche des lignes et des erreurs, pas des messages. Le même chiffre se lit dans le cache de plans, juste après avoir exécuté la requête :

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

Il m'a renvoyé 62, comme STATISTICS IO.

Le plan estimé et le plan réel
SET SHOWPLAN_XML ON demande le plan sans exécuter la requête. SET STATISTICS XML ON l'exécute et ajoute ce qui s'est passé : ActualRows à côté de EstimateRows sur chaque opérateur. Si l'instruction écrit, on l'enveloppe dans BEGIN TRAN … ROLLBACK, parce qu'elle s'exécute pour de vrai.

EXPLAIN visuel, dans Calíope, demande le plan estimé : l'arbre des opérateurs avec les lignes estimées, sans lignes réelles, sans durées et sans coût. Il sert à voir la forme, un Index Scan là où tu attendais un Seek ; pour comparer l'estimation à la réalité, il faut le plan réel.

La détection de paramètres
La première fois qu'une procédure ou une requête paramétrée s'exécute, l'optimiseur regarde la valeur qu'elle apporte et compile le plan pour elle. Ce plan reste en cache, et les exécutions suivantes le réutilisent même si elles apportent une autre valeur. Je l'ai reproduit avec une procédure et deux clients : l'un avec 20 commandes, l'autre avec 100 000.

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

Compilé pour le petit client, le plan cherche dans l'index et va à la table ligne par ligne : parfait pour 20 lignes, et 300 000 lectures pour 100 000, cinquante fois plus que de parcourir toute la table. Compilé dans l'autre sens, le petit parcourt toute la table. L'ordre d'arrivée décide, et un redémarrage ou un changement de statistiques l'inverse : d'où le « hier ça allait bien ».

Le plan réel le trahit sans deviner : dans le mauvais cas, il indiquait EstimateRows="20" et ActualRows="100000", et dans ses paramètres, ParameterCompiledValue="(4243)" à côté de ParameterRuntimeValue="(1)".

Ce que j'ai essayé pour y remédier :
- OPTION (RECOMPILE) à la fin de la requête : 62 et 6 085, chaque valeur avec son plan. Le prix, c'est de compiler à chaque appel (ici, 2-3 ms), ce qui n'est pas gratuit pour une requête exécutée des milliers de fois par seconde.
- OPTION (OPTIMIZE FOR UNKNOWN) : compile pour la valeur moyenne, environ 20 lignes, et cela a donné 70 et 306 429. Quand il y a une valeur énorme, cela n'aide pas.
- EXEC sp_recompile 'dbo.pedidos_de' jette le plan de la procédure : c'est le remède du moment, pas la guérison.
- Depuis SQL Server 2022, avec un niveau de compatibilité 160 ou plus, le serveur peut garder plusieurs plans pour une même requête selon la valeur. C'est activé par défaut, mais avec 100 000 lignes contre 20, cela n'a pas agi. Avec 200 000 contre 1, si : deux plans. Ne compte pas dessus.

Quelle requête coûte le plus : le cache de plans
Le serveur tient le compte de chaque plan qu'il a en cache : exécutions, temps, lectures et lignes. query_hash regroupe par la forme de la requête : cinq SELECT qui ne changeaient que le numéro de client, chacun isolé, ont laissé cinq plans en cache (80 ko) et un seul query_hash.

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

C'est ce que Calíope affiche dans Requêtes lentes, vue Par instruction : une ligne par forme, avec le texte de son plan le plus coûteux, de toutes les bases du serveur. Il faut la permission VIEW SERVER STATE. Et il ne garde aucun historique : j'ai redémarré le serveur et la vue est passée de 23 lignes à 0. Il ne survit pas non plus à la sortie du plan du cache.

Query Store, qui est déjà activé
Query Store est la version qui persiste : il garde textes, plans et chiffres par intervalles dans la base elle-même. Sur SQL Server 2025, je l'ai trouvé activé (READ_WRITE) dans demo et dans une base toute neuve, sans que personne ne le demande. D'après la documentation, c'est le cas pour les nouvelles bases depuis SQL Server 2022.

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

Une colonne planes supérieure à 1 est la trace de la détection de paramètres. Deux nuances mesurées : après le redémarrage, les textes de ses 11 requêtes étaient toujours là, mais pas les chiffres d'avant, car il les écrit sur disque toutes les 900 secondes ; et il est par base, pas par serveur.

Calíope ne l'utilise pas : Requêtes lentes lit le cache de plans, qui est déjà là pour tout le serveur. Il ne l'active ni ne le désactive, car c'est un ALTER DATABASE d'administration.

Les attentes : où part le temps
sys.dm_os_wait_stats additionne, depuis le dernier redémarrage, combien de temps le serveur a attendu et pourquoi. Il compte 1 547 types, et les premiers de la liste, sans filtre, sont des threads qui attendent du travail : DISPATCHER_QUEUE_SEMAPHORE, BROKER_TASK_STOP, les SLEEP_…. Il faut les écarter. Ceux qui disent vraiment quelque chose :
- LCK_M_… : des verrous. J'en parle dans « Verrous et interblocages (SQL Server) ».
- PAGEIOLATCH_… : attente de pages venant du disque. Il manque de la mémoire ou des index.
- WRITELOG : attente du journal des transactions à chaque validation.
- CXPACKET et CXCONSUMER : parallélisme.
- SOS_SCHEDULER_YIELD : CPU.
- RESOURCE_SEMAPHORE : des requêtes qui attendent de la mémoire pour trier ou pour un hachage.

Ce sont des cumuls, donc une seule photo sert à peu de chose : on en prend deux et on soustrait. Après mon redémarrage, le total est passé de 12,1 à 0,7 million de ms.

Parallélisme
D'origine, cost threshold for parallelism vaut 5 et max degree of parallelism, 0 (tous les cœurs). Le seuil est si bas qu'un parcours moyen peut déjà passer en parallèle : sur une autre table de 200 000 lignes, l'un a utilisé 16 threads, 2 094 ms de CPU pour 152 ms d'horloge. Un CXPACKET élevé n'est pas une erreur ; c'est le signe que ces deux valeurs sont restées telles quelles.

Ce qui est déjà dans d'autres sujets
- Un paramètre nvarchar face à une colonne varchar avec une collation SQL_ passe de 3 lectures à 334 : « Encodage et collations (SQL Server) ».
- Key Lookup, quand le plan abandonne l'index pour la table, YEAR(fecha) face à la plage, et les index qui manquent au serveur : « Index (SQL Server) ».
- Une contrainte CHECK non approuvée n'est pas utilisée par l'optimiseur (1 364 lectures contre aucune) : « Changements de schéma à chaud (SQL Server) ».
- Combien de partitions une requête a lues, seul le plan réel le dit : « Partitionnement (SQL Server) ».

Recommandation
L'ordre qui marche : trouver la requête dans Requêtes lentes (ou dans Query Store, s'il faut l'historique), demander son plan réel avec SET STATISTICS XML ON et comparer EstimateRows avec ActualRows. S'ils divergent et que la valeur compilée n'est pas celle d'aujourd'hui, c'est la détection de paramètres ; s'ils divergent avec la même valeur, ce sont les statistiques ; s'ils coïncident et que la requête lit quand même beaucoup, il manque un index. Toucher à la mémoire ou au parallélisme avant d'avoir lu un plan, c'est le chemin long.

Mots-clés : performance, statistics io, statistics time, lectures logiques, showplan_xml, statistics xml, plan d'exécution, plan réel, détection de paramètres, parameter sniffing, recompile, optimize for unknown, sp_recompile, dm_exec_query_stats, query_hash, cache de plans, query store, dm_os_wait_stats, attentes, parallélisme

Les trois modes de récupération et celui qui laisse grossir le journal, BACKUP DATABASE et BACKUP LOG sur le disque du serveur, la chaîne du journal, revenir à la minute d'avant le DELETE avec STOPAT, COPY_ONLY, et ce qui distingue la sauvegarde de Calíope.

S'applique à : SQL Server 2022+

Si tu viens de MySQL, il n'y a ici ni mysqldump ni binlog à part : la sauvegarde physique est une instruction T-SQL, et l'historique qui permet d'avancer à partir d'elle est le journal des transactions de chaque base. Si tu viens de PostgreSQL, la différence est l'unité : on sauvegarde et on restaure une base, pas le serveur entier. Tout ce que je raconte ici, je l'ai mesuré sur SQL Server 2025.

Le mode de récupération décide presque tout
C'est une propriété de chaque base, et il y en a trois :

Une base neuve copie le mode de model, qui sur ce serveur est FULL :

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases;

FULL sans sauvegardes du journal remplit le disque
Voilà le piège. Tant que la base n'a pas sa première sauvegarde complète, FULL se comporte comme SIMPLE : j'ai inséré 20 000 lignes de 2 ko, le journal est monté à 42,2 Mo utilisés et un CHECKPOINT l'a ramené à 8,1 Mo. Après le premier BACKUP DATABASE, la même chose n'a plus rien libéré : 55,4 Mo, puis 102,8 Mo, et le fichier est passé de 72 à 136 Mo. log_reuse_wait_desc dit pourquoi il n'est pas réutilisé :

- LOG_BACKUP — il attend un BACKUP LOG. C'est la cause de presque tous les disques pleins.
- ACTIVE_TRANSACTION — une transaction ouverte que personne ne ferme.
- CHECKPOINT — rien à craindre, il se libère au suivant.

BACKUP LOG a ramené l'espace utilisé à 15,1 Mo, mais le fichier est resté à 136 Mo : sauvegarder le journal libère de la place à l'intérieur, ça ne la rend pas au disque. Si une base est en FULL et que personne ne programme de BACKUP LOG, soit on le programme, soit on passe en SIMPLE.

Sauvegarder

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

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

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

Le chemin est sur le disque du serveur, pas le tien : c'est le processus de SQL Server qui écrit le fichier. Sous Linux le dossier habituel est /var/opt/mssql/backup/ ; dans le conteneur de test il n'existait pas, et le premier BACKUP l'a créé.

- La complète copie la base entière : 150 Mo dans mon test.
- La différentielle copie ce qui a changé depuis la dernière complète : 3,1 Mo après avoir modifié 2 000 lignes.
- Celle du journal copie le journal depuis la précédente, et c'est elle qui permet d'aller à un instant.

CHECKSUM n'est pas activé par défaut (backup checksum default vaut 0), et ça se voit à la vérification : RESTORE VERIFYONLY … WITH CHECKSUM sur une sauvegarde faite sans lui a donné Msg 3187. Mets-le toujours, et vérifie :

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

La chaîne du journal
Chaque sauvegarde du journal commence là où la précédente s'est arrêtée, et on les restaure toutes, dans l'ordre. En sauter une a donné Msg 4305, avec le LSN qui manque. Deux choses la cassent sans prévenir :

- Passer en SIMPLE et revenir en FULL : ensuite, BACKUP LOG a donné Msg 4214 jusqu'à la complète suivante, même s'il y en avait une d'avant.
- Un BACKUP LOG fait par quelqu'un d'autre, vers un fichier que tu ne connais pas. L'historique est dans msdb :

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

COPY_ONLY : la copie qui ne dérange pas
Une complète normale devient la base des différentielles suivantes. Si tu en fais une isolée —pour emporter la base sur une autre machine—, la différentielle de ce soir ne correspond plus à la complète de dimanche. Avec WITH COPY_ONLY, ça n'arrive pas : la différentielle suivante a continué à pointer vers la complète précédente, et la restaurer par-dessus la COPY_ONLY a donné Msg 3136. Toute copie hors de la routine, avec COPY_ONLY.

Revenir à la minute d'avant le DELETE
Je l'ai reproduit en entier : 500 commandes, un DELETE sans WHERE quatre secondes plus tard, et la récupération dans une nouvelle base, sans toucher à l'originale :

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

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

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

Les 500 sont revenues. Ce qu'il faut savoir :

- NORECOVERY laisse la base en RESTORING, en attente de la suite : la lire donne Msg 927. La dernière étape porte RECOVERY.
- MOVE est obligatoire quand on restaure sous un autre nom, sinon elle entre en conflit avec les fichiers de l'originale. Leurs noms logiques, c'est RESTORE FILELISTONLY qui les donne.
- STOPAT est l'heure du serveur, avec trois décimales : avec sept, il a donné Msg 3217.
- Restaurer par-dessus la base vivante sans sauvegarder d'abord la fin du journal a donné Msg 3159. La fin du journal, c'est ce qui s'est passé depuis le dernier BACKUP LOG ; on la sauvegarde avec BACKUP LOG … WITH NORECOVERY, qui laisse en plus la base en RESTORING.

BULK_LOGGED et son prix
Un SELECT … INTO d'une table de 21 Mo a écrit 22,1 Mo de journal en FULL et 1,0 Mo en BULK_LOGGED. Mais la sauvegarde du journal suivante a pesé pareil, 22,1 Mo, parce qu'elle emporte les pages chargées ; et restaurer cette sauvegarde avec STOPAT a donné Msg 4341 : dans un intervalle avec un chargement en masse, on ne peut aller qu'à la fin, pas à un instant. On passe en BULK_LOGGED pour le chargement et on revient en FULL juste après, avec un BACKUP LOG de chaque côté.

Restaurer sur un autre serveur
Une sauvegarde de base emporte ses utilisateurs, mais pas les logins du serveur, qui vivent dans master. Restauré sur une autre machine, chaque utilisateur se retrouve orphelin de son login ; comment les trouver et les rattacher est expliqué dans le sujet sur la sécurité de SQL Server.

Ce que fait Calíope
Sauvegarde écrit un script texte, pas un .bak : des instructions avec GO entre les lots, les lignes en INSERT de mille, les dates en ISO 8601 et les binaires en 0x…, pour qu'il revienne identique sur un autre serveur, une autre version ou avec la session dans une autre langue. Avec Inclure les mots de passe des utilisateurs, les logins partent en CREATE LOGIN … WITH PASSWORD = 0x… HASHED, et l'utilisateur de chaque base en CREATE USER … FOR LOGIN : en restaurant le script, personne ne reste orphelin. Une table partitionnée revient partitionnée : le script crée d'abord, s'ils n'existent pas, sa fonction et son schéma de partition.

Depuis l'Éditeur SQL tu peux lancer un BACKUP DATABASE —c'est une instruction comme une autre—, mais le fichier reste sur le disque du serveur : Calíope ne le rapatrie pas.

Recommandation
Décide le mode base par base : SIMPLE là où perdre la journée est acceptable, et FULL avec un BACKUP LOG programmé là où ça ne l'est pas. Complète hebdomadaire, différentielle quotidienne et journal toutes les quelques minutes, c'est la routine de toujours, toujours WITH CHECKSUM. Surveille log_reuse_wait_desc, fais toute copie isolée en COPY_ONLY, et restaure pour de vrai, sous un autre nom, de temps en temps : une sauvegarde jamais restaurée n'est qu'une intention. La sauvegarde de Calíope sert à emporter une base, ou une partie, sur une autre machine ; elle ne remplace pas les sauvegardes du serveur.

Mots-clés : sauvegarde, backup, restore, restauration, récupération, mode de récupération, full, simple, bulk_logged, journal des transactions, log_reuse_wait_desc, backup log, chaîne du journal, norecovery, stopat, copy_only, différentielle, checksum, verifyonly, point dans le temps, 3159, 4214, 4341

Login de serveur et utilisateur de base, pourquoi un utilisateur devient orphelin après une restauration sur un autre serveur et comment le relier, accorder par schéma, DENY et qui y échappe, le chaînage des propriétaires, les utilisateurs autonomes, la sécurité au niveau des lignes et que faire de sa.

S'applique à : SQL Server 2022+

Si tu viens de MySQL, ici le compte ne porte pas l'hôte et, en plus, ce sont deux choses : le login, qui appartient au serveur et avec lequel on entre, et l'utilisateur, qui appartient à chaque base et qui reçoit les permissions. Si tu viens de PostgreSQL, c'est comme si chaque rôle avec LOGIN devait exister une seconde fois dans chaque base pour pouvoir l'utiliser. Tout ce que je raconte ici, je l'ai mesuré sur SQL Server 2025.

Login et utilisateur

CREATE LOGIN ana WITH PASSWORD = 'Xq7#lunes_pw9';   -- serveur : entrer
USE tienda;
CREATE USER ana FOR LOGIN ana;                    -- base : être dedans

Avec le login et sans l'utilisateur, on entre dans le serveur mais pas dans la base : se connecter en la nommant a donné « Cannot open database … The login failed » (4060), et un USE depuis master a donné Msg 916. L'utilisateur est relié à son login par le sid, pas par le nom : c'est de là que viennent les orphelins.

Le mot de passe passe par la politique (CHECK_POLICY, activée par défaut) : huit caractères de trois classes, et sans le nom du login dedans. 'Xq7#ana_pw9' pour le login ana a donné Msg 33064, alors qu'il respecte le reste.

Utilisateurs orphelins : restaurer sur un autre serveur
Je l'ai reproduit : j'ai sauvegardé une base avec l'utilisateur ana, supprimé le login puis je l'ai recréé avec le même nom —comme ce serait le cas sur une autre machine—, et j'ai restauré la base. ana ne pouvait plus entrer dans la copie : son utilisateur gardait l'ancien sid, et le nouveau login en avait un autre. Le recréer ne marchait pas non plus : Msg 15023, l'utilisateur existe déjà. Les trouver :

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

Et les relier, sans perdre leurs permissions :

ALTER USER ana WITH LOGIN = ana;

Deux choses de plus. DROP LOGIN ne vérifie pas si le login a des utilisateurs : il l'a supprimé sans un avertissement et a laissé l'utilisateur orphelin dans la base d'origine, sur le même serveur. Et pour que cela n'arrive pas en emportant une base sur une autre machine, le login s'y crée avec le même sid (CREATE LOGIN ana WITH PASSWORD = …, SID = 0x1C2F…) : avec lui, l'utilisateur de la base restaurée a refonctionné sans y toucher.

Rôles
Il y a 18 rôles fixes de serveur : les huit habituels (sysadmin, serveradmin, securityadmin, processadmin, setupadmin, bulkadmin, diskadmin, dbcreator) et dix ##MS_…## depuis 2022, plus fins (##MS_ServerStateReader## pour voir l'état sans rien toucher). Dans chaque base il y en a neuf fixes : db_owner, db_datareader, db_datawriter, db_ddladmin, db_securityadmin, db_accessadmin, db_backupoperator et les deux qui refusent, db_denydatareader et db_denydatawriter. Les rôles à toi se créent avec CREATE ROLE et se remplissent avec ALTER ROLE … ADD MEMBER.

Et public, auquel tout le monde appartient. Sur le serveur, il a VIEW ANY DATABASE : ana voyait les noms des dix bases du serveur, alors qu'elle ne pouvait entrer que dans quatre. Si les noms sont une information, on le retire à public.

Accorder par schéma couvre aussi les tables de demain

GRANT SELECT ON SCHEMA::rrhh TO ana;

Avec ça, ana a lu les tables de rrhh, et aussi celle que j'ai créée ensuite. C'est l'inverse de PostgreSQL, où ON ALL TABLES n'atteint que celles d'aujourd'hui. Et ce qui est hors du schéma reste fermé : dbo.pedidos a donné Msg 229.

GRANT, DENY et REVOKE : trois états, pas deux
- GRANT accorde.
- DENY interdit, et l'emporte sur tout GRANT : avec DENY SELECT sur rrhh.nominas, ana a reçu Msg 229 en ayant le schéma accordé, et aussi en étant membre de db_datareader.
- REVOKE efface ce qu'il y a, que ce soit un GRANT ou un DENY. Après le REVOKE, ana a de nouveau pu lire, par le rôle.

Qui échappe à un DENY, je l'ai mesuré un par un :
- Un membre de db_owner : il n'y échappe pas, Msg 229.
- Un login avec CONTROL SERVER : non plus, Msg 229.
- Un membre de sysadmin : oui. Il entre dans chaque base comme dbo (USER_NAME() le dit) et aucune permission ne lui est vérifiée, donc il a lu la table refusée.

Ce que je peux faire, demandé au serveur

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

fn_my_permissions a renvoyé SELECT quatre fois : une pour la table et une pour chacune de ses trois colonnes. C'est la façon de vérifier une permission effective —rôles, schémas et DENY déjà résolus— sans demander son mot de passe à personne.

Le chaînage des propriétaires
Si une vue ou une procédure et la table qu'elle lit ont le même propriétaire, celui qui peut utiliser la vue n'a pas besoin de permission sur la table. ana, sans rien sur dbo.pedidos, a lu par la vue dbo.v_totales et par la procédure dbo.p_total. C'est la bonne façon de donner accès : seulement à ce que l'objet montre. Mais le SQL dynamique casse la chaîne : la même procédure avec un EXEC('SELECT …') dedans a donné Msg 229.

Utilisateurs autonomes
Dans une base autonome, l'utilisateur porte son mot de passe et n'a pas besoin de login, donc il voyage avec la base et ne devient jamais orphelin :

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

L'option est désactivée par défaut. Avec elle, carla est entrée en nommant la base à la connexion ; sans la nommer, « Login failed ». Dans Calíope, cela revient à mettre la base dans le champ Base de données de la connexion.

Sécurité au niveau des lignes
Une politique avec un prédicat de filtre :

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

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

ana a vu sa ligne. Et contrairement à PostgreSQL, ici personne n'y échappe : sa et un sysadmin ont vu zéro ligne, parce que pour eux USER_NAME() vaut dbo. C'est le prédicat qui décide, et si l'administrateur doit tout voir, c'est au prédicat de le dire. En plus, un filtre ne filtre que ce qui est lu : ana a inséré une ligne d'un autre propriétaire sans erreur. Pour l'empêcher, il faut un BLOCK PREDICATE.

sa
sa est le login au sid 0x01, membre de sysadmin, et son nom est connu de tous ceux qui essaient d'entrer. Un mauvais mot de passe donne 18456 côté client, toujours avec l'état 1 ; le motif n'est que dans le journal du serveur, où la même tentative apparaît avec l'état 8, mot de passe erroné. L'habitude est de créer un login d'administration avec un nom propre, puis ALTER LOGIN sa DISABLE.

Ce que Calíope ne fait pas
Calíope ne parle que l'authentification SQL : ni l'authentification Windows ni Microsoft Entra ID.

Ce que fait Calíope
Utilisateurs liste les logins du serveur, avec public comme un compte de plus, et sa à part, marqué Utilisateur système. Les permissions se voient par niveau —serveur, base, schéma, table, routine—, et un DENY se dessine comme un troisième état : avec son glyphe, barré et avec le mot DENY, pas seulement avec une autre couleur. On ne peut pas le décocher, parce que l'enlever n'est pas décocher mais un REVOKE. Compte verrouillé, c'est ALTER LOGIN … DISABLE. Un utilisateur de base sans login, orphelin ou autonome, n'apparaît pas : pour ceux-là, les requêtes ci-dessus dans l'Éditeur SQL.

Recommandation
Un login par personne ou par application, et les permissions par schéma à des rôles, pas à des personnes. sysadmin seulement pour administrer, parce qu'aucun DENY ne l'atteint. Ce qui peut passer par une vue ou une procédure doit passer par là, et pas avec du SQL dynamique dedans. En déplaçant une base sur une autre machine, les logins avec leur sid, ou la requête des orphelins juste après la restauration. Et sa, désactivé.

Mots-clés : sécurité, login, utilisateur, user, rôle, role, sysadmin, db_owner, db_datareader, public, grant, deny, revoke, schéma, schema, fn_my_permissions, has_perms_by_name, utilisateur orphelin, orphaned user, alter user with login, sid, utilisateur autonome, contained database, chaînage des propriétaires, ownership chaining, row-level security, security policy, sa, check_policy, 229, 916, 4060, 15023, 18456, 33064

sp_configure et RECONFIGURE, pourquoi value et value_in_use diffèrent, les options qui exigent un redémarrage, le plafond de mémoire qui n'est pas posé, où gagne chaque MAXDOP, la configuration par base, tempdb et mssql-conf.

S'applique à : SQL Server 2022+

Si tu viens de MySQL, ici il n'y a ni my.cnf ni SET GLOBAL : ce qui concerne le serveur s'écrit avec sp_configure et prend effet avec RECONFIGURE, et ce qui concerne une base, avec ALTER DATABASE SCOPED CONFIGURATION. Si tu viens de PostgreSQL, sys.configurations est ton pg_settings, et la colonne qui compte n'est pas source : c'est l'écart entre value et value_in_use. Tout ce que je raconte ici, je l'ai mesuré sur SQL Server 2025.

107 options, dont 74 cachées
sys.configurations compte 107 options sur ce serveur, et sp_configure seul en montre 33. Les 74 autres sont avancées : tant que show advanced options n'est pas activé, elles ne sont ni listées ni modifiables. sp_configure 'max degree of parallelism', 4 a donné Msg 15123, « does not exist, or it may be an advanced option ».

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

Écrit n'est pas appliqué : value et value_in_use
sp_configure ne fait qu'écrire la valeur, et le serveur garde l'ancienne jusqu'au RECONFIGURE. Après sp_configure 'cost threshold for parallelism', 50, sys.configurations indiquait value 50 et value_in_use 5, et le serveur lui-même prévenait : « Run the RECONFIGURE statement to install ».

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

Cette requête se lance avant chaque RECONFIGURE, pour deux choses que j'ai mesurées :
- RECONFIGURE installe tout ce qui est en attente, pas seulement le tien : si quelqu'un a laissé une valeur écrite, elle entre avec la tienne.
- Un RECONFIGURE qui échoue laisse l'écrit en attente. Avec min server memory (MB) à 1 024 et max server memory (MB) à 128, il a donné Msg 5831, et le 1 024 est resté dans value en attendant le suivant.

Il y a un cas avec laissez-passer : recovery interval (min) à 120 a donné Msg 5807 (« not recommended »), et RECONFIGURE WITH OVERRIDE l'a appliqué quand même. WITH OVERRIDE saute précisément la vérification qui vient d'avertir, donc on ne l'utilise pas par habitude.

Celles qui exigent un redémarrage
Celles qui ont is_dynamic = 0 ne prennent pas effet avec RECONFIGURE : ici, 23 sur 107, dont user connections, fill factor (%), locks et priority boost. Avec sp_configure 'user connections', 500 et RECONFIGURE, value est passé à 500 et value_in_use est resté à 0, jusqu'au redémarrage du service.

Chaque changement reste dans le journal du serveur, avec l'heure et la session : EXEC xp_readerrorlog 0, 1, N'Configuration option' les liste avec leur « changed from 0 to 4 ».

La mémoire : max server memory arrive sans plafond
max server memory (MB) vaut 2 147 483 647 d'usine, c'est-à-dire aucun : SQL Server prend de la mémoire pour son cache jusqu'à ce que le système lui demande de la rendre. Sur un serveur dédié, on lui pose un plafond qui laisse de la place au système d'exploitation. Le minimum accepté est 128 ; avec 100, il a donné Msg 15129.

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

Sous Linux, il y a un autre plafond en dessous, memory.memorylimitmb de mssql-conf, qui selon la documentation vaut d'usine 80 % de la mémoire physique. Ce conteneur voit 6 347 Mo, et avec max server memory intact sa cible (committed_target_kb de sys.dm_os_sys_info) était de 5 139 Mo.

Parallélisme : trois niveaux, et le plus proche gagne
D'usine, max degree of parallelism vaut 0 (tous les processeurs) et cost threshold for parallelism, 5. J'ai lancé la même requête sur 300 000 lignes et lu le DegreeOfParallelism du plan réel :
- Avec le serveur à 0 : 15 threads, tous les processeurs qu'il voit.
- Avec le serveur à 4 : 4.
- Avec la base en ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 2 : 2, même si le serveur dit 4.
- Avec OPTION (MAXDOP 8) sur la requête : 8, au-dessus de la base et du serveur.
- Avec cost threshold for parallelism à 50 : en série. Le seuil se compare au coût du plan en série (ici 7,7), pas à celui du plan parallèle.

Comment le parallélisme se voit dans les attentes, je le raconte dans « Performance et diagnostic (SQL Server) ».

Configuration par base
sys.database_scoped_configurations compte 41 options dans demo. Outre MAXDOP, il y a LEGACY_CARDINALITY_ESTIMATION (0), PARAMETER_SNIFFING (1) et QUERY_OPTIMIZER_HOTFIXES (0), entre autres. C'est la façon de changer le comportement d'une application sans toucher aux autres du serveur.

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

tempdb
Ce serveur voit 15 processeurs, et tempdb a 8 fichiers de données de 8 Mo, plus celui du journal, qui grandissent par pas de 64 Mo : l'installation en met déjà un par processeur, jusqu'à huit. C'est la base des tables #temporaires, des tris qui ne tiennent pas en mémoire et des versions de ligne de READ_COMMITTED_SNAPSHOT.

Ce qui n'est pas sp_configure : mssql-conf, sous Linux
Les chemins par défaut des données, du journal et des sauvegardes, le certificat TLS, le plafond de mémoire ou la langue vivent dans /var/opt/mssql/mssql.conf, et se changent avec /opt/mssql/bin/mssql-conf set … et un redémarrage. Celui de ce conteneur n'a que deux lignes : le certificat et la clé TLS, ce que SQL Server 2022 et suivants exigent pour le TLS strict (TDS 8.0). Sous Windows, la même chose se fait dans le Gestionnaire de configuration SQL Server.

Ce que montre Calíope
Informations serveur, dans Variables globales, liste sys.configurations avec son value_in_use —ce qui tourne, pas ce qui est écrit et en attente— et les propriétés SERVERPROPERTY.* : version, édition, collation. Variables de session sont les options SET de ta connexion. Calíope les lit et ne les change pas : un sp_configure s'écrit dans l'Éditeur SQL, et après le RECONFIGURE il apparaît dans Informations serveur en actualisant. Requêtes lentes montre dans ses réglages optimize for ad hoc workloads et max server memory (MB). Et contained database authentication, l'option qui admet des utilisateurs avec leur propre mot de passe, je la raconte dans « Logins, utilisateurs et permissions (SQL Server) ».

Recommandation
Peu, et en mesurant. max server memory toujours, en laissant de la place au système ; cost threshold for parallelism au-dessus de 5 ; et MAXDOP par base quand une application le demande, pas pour tout le serveur. Avant chaque RECONFIGURE, la requête value <> value_in_use : ainsi on applique ce qu'on veut, et pas ce qu'un autre a laissé à moitié.

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

Pourquoi un BEGIN TRAN dans un autre ne s'imbrique pas, comment revenir en arrière avec SAVE TRANSACTION, ce que verrouille chaque niveau, le 3960 de SNAPSHOT et ce qu'une transaction oubliée fait au journal.

S'applique à : SQL Server 2022+

Une transaction regroupe plusieurs instructions en une unité qui s'applique en entier ou pas du tout. Comme dans MySQL et PostgreSQL, sans transaction explicite SQL Server valide chaque instruction à part (autocommit). Ce qui change est dans les détails, et tout ce que je raconte ici, je l'ai mesuré sur SQL Server 2025.

BEGIN TRAN, pas BEGIN
Un BEGIN seul ouvre un bloc T-SQL, pas une transaction : le serveur a répondu Msg 102, « Incorrect syntax near 'BEGIN' ». On écrit BEGIN TRAN (ou BEGIN TRANSACTION). Un COMMIT sans transaction ouverte donne Msg 3902, et un ROLLBACK, Msg 3903.

Le mode implicite
Avec SET IMPLICIT_TRANSACTIONS ON, la première instruction qui touche aux données ouvre une transaction qui reste ouverte jusqu'au COMMIT. Je l'ai mesuré : @@TRANCOUNT valait 0, un simple SELECT l'a fait passer à 1, et seul le COMMIT l'a ramené à 0. Une session qui oublie de valider dans ce mode garde ses verrous et retient le journal, comme je l'explique plus bas.

Imbriquer n'imbrique pas
Un BEGIN TRAN dans un autre ne fait qu'augmenter le compteur @@TRANCOUNT. C'est la transaction extérieure qui décide :
- Le COMMIT intérieur ne valide rien : il a fait passer le compteur de 2 à 1, et le ROLLBACK extérieur a annulé les deux UPDATE, celui de l'intérieur aussi.
- Le ROLLBACK intérieur annule tout : il a laissé @@TRANCOUNT à 0, et le COMMIT extérieur a donné Msg 3902, faute de transaction.
- Nommer la transaction intérieure n'aide pas : ROLLBACK TRAN interior a donné Msg 6401 et le compteur est resté à 2. On ne peut revenir en arrière que jusqu'à un point de sauvegarde.

BEGIN TRAN;                                  -- @@TRANCOUNT = 1
  UPDATE cuentas SET saldo = saldo - 10 WHERE id = 1;
  BEGIN TRAN;                                -- @@TRANCOUNT = 2
    UPDATE cuentas SET saldo = saldo + 10 WHERE id = 2;
  COMMIT;                                    -- @@TRANCOUNT = 1, rien de validé
ROLLBACK;                                    -- annule les deux UPDATE

Une procédure qui se termine avec un autre @@TRANCOUNT que celui qu'elle avait en entrant donne Msg 266 : j'en parle dans « Erreurs (SQL Server) ».

Points de sauvegarde : SAVE TRANSACTION
Pour annuler une partie sans perdre la transaction, on marque un point et on y revient. Ici, l'INSERT en double a échoué avec 2627, je suis revenu au point, et le COMMIT a validé la ligne d'avant et celle d'après :

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

Deux limites que j'ai mesurées : SAVE TRANSACTION sans transaction ouverte donne Msg 628, et avec XACT_ABORT ON la même erreur a condamné la transaction —XACT_STATE() -1— et revenir au point a donné Msg 3931 : il ne restait qu'à tout annuler.

Une erreur n'interrompt presque jamais la transaction
Au contraire de PostgreSQL, une erreur d'instruction —une clé en double, une contrainte— n'interrompt que cette instruction, et la transaction reste vivante avec ce qu'elle a déjà fait. Sans XACT_ABORT, un COMMIT à la fin valide ce qui précède et ce qui suit l'erreur. Avec SET XACT_ABORT ON, la même erreur annule tout et interrompt le lot. Quelles erreurs annulent la transaction d'elles-mêmes, je le raconte dans « Erreurs (SQL Server) ».

Les niveaux d'isolation
Le niveau par défaut est READ COMMITTED. J'ai mesuré avec deux sessions : la première lit quatre lignes d'une plage, attend quatre secondes et les relit ; la seconde, entre-temps, insère une ligne dans cette plage et met à jour l'une des lignes lues.
- READ UNCOMMITTED lit ce que les autres n'ont pas validé : c'est le NOLOCK de « Verrous et interblocages (SQL Server) ».
- READ COMMITTED ne garde pas les verrous de ce qu'il lit. Sans READ_COMMITTED_SNAPSHOT, désactivé par défaut, une lecture attend celui qui écrit ; j'en parle dans « Verrous et interblocages (SQL Server) ».
- REPEATABLE READ a gardé cinq verrous partagés (KEY S) jusqu'à la fin : l'UPDATE de la ligne lue a attendu 3 s. Mais l'INSERT est passé tout de suite, et la relecture a compté 5 lignes au lieu de 4 : une ligne fantôme.
- SERIALIZABLE a verrouillé la plage (KEY RangeS-S) : l'INSERT a attendu 3 s et la relecture a toujours compté 4.
- SNAPSHOT lit une photo prise au début de la transaction et ne bloque personne, mais il faut l'autoriser dans chaque base, et il est désactivé par défaut : snapshot_isolation_state_desc valait OFF dans demo, dans model et dans une base neuve. Sans autorisation, SET TRANSACTION ISOLATION LEVEL SNAPSHOT et BEGIN TRAN sont passés, et c'est le premier SELECT qui a donné Msg 3952.

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

SNAPSHOT n'attend pas : il interrompt avec 3960
Avec l'autorisation, la seconde session a mis à jour la ligne sans attendre la première, qui ne l'avait que lue. La première l'a relue et voyait toujours la valeur de sa photo, 100, alors que le 101 de l'autre était déjà validé. Quand elle a voulu la mettre à jour, le serveur l'a interrompue avec Msg 3960 (« update conflict »), et XACT_STATE() est resté à -1. En SNAPSHOT, un 3960 est un fonctionnement normal : l'application doit réessayer la transaction entière. READ_COMMITTED_SNAPSHOT est autre chose : il ne change pas de niveau, il change la façon dont lit READ COMMITTED, et c'est ce que je raconte dans « Verrous et interblocages (SQL Server) ».

Une transaction oubliée retient le journal
Une session endormie dans une transaction (status = sleeping, open_transaction_count = 1) empêche de réutiliser le journal. J'ai inséré puis supprimé 5 000 lignes et lancé un CHECKPOINT, dans une base où le CHECKPOINT vide le journal. Sans cette session, 6,5 Mo sont restés utilisés ; avec elle, 73,8 Mo, et log_reuse_wait_desc indiquait ACTIVE_TRANSACTION. Après l'avoir fermée, 5,1 Mo. Le reste du cycle du journal, je le raconte dans « Sauvegarde et récupération (SQL Server) ». Pour la trouver :

DBCC OPENTRAN;   -- la plus ancienne : SPID et heure de début

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

Le DDL s'annule aussi
ALTER TABLE, CREATE INDEX et CREATE TABLE font partie de la transaction, avec leurs exceptions (CREATE DATABASE donne Msg 226) : j'en parle dans « Changements de schéma à chaud (SQL Server) ».

Ce que fait Calíope
L'Éditeur SQL exécute ce que tu lui donnes sur une seule connexion, instruction par instruction, et s'arrête à la première qui échoue. Sans transaction du menu, à la fin il rend la connexion au pool, et s'il reste une transaction ouverte, il l'annule (IF @@TRANCOUNT > 0 ROLLBACK) : un BEGIN TRAN et son COMMIT écrits à la main vont dans la même exécution, et ce qui précédait une erreur est annulé quand la connexion est rendue. Dans sqlcmd ou dans une application, cela aurait été validé.
Pour une transaction qui dure plusieurs exécutions, il y a le menu Transaction de la barre : BEGIN envoie BEGIN TRANSACTION et réserve une connexion pour l'onglet, et ce que tu y exécutes reste dedans jusqu'au COMMIT ou au ROLLBACK. Fermer l'onglet l'annule. Si le serveur l'annule tout seul — un 245, un 1205 ou toute erreur sous XACT_ABORT — ou si un COMMIT de ton SQL la termine, l'étiquette Transaction active s'éteint et la barre d'état le signale : à partir de là, chaque instruction est validée aussitôt.

Recommandation
SET XACT_ABORT ON dans tout lot ou procédure qui écrit, et un TRY…CATCH qui se termine par IF @@TRANCOUNT > 0 ROLLBACK; et THROW;. N'imbrique pas de BEGIN TRAN en espérant que l'intérieure soit indépendante : c'est à ça que servent les points de sauvegarde. Si tu passes à SNAPSHOT, écris la relance du 3960 avant de passer. Et de temps en temps, DBCC OPENTRAN : une session oubliée ne prévient pas avant que le disque soit plein.

Mots-clés : transaction, commit, rollback, begin tran, @@trancount, save transaction, point de sauvegarde, xact_abort, xact_state, implicit_transactions, isolation, repeatable read, serializable, snapshot, allow_snapshot_isolation, 3960, 3952, 3902, 6401, 3931, active_transaction, dbcc opentran

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