DBA-Handbuch

Wertebereiche, Größe in Bytes und Anwendungsfälle der numerischen, Text-, Datums-/Zeit-, JSON- und räumlichen Typen. Enthält relevante Unterschiede zwischen MySQL und MariaDB.

Gilt für: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Die Wahl des Datentyps beeinflusst die Größe auf der Festplatte, die Geschwindigkeit der Indizes und die Integrität der Daten. Dieser Leitfaden fasst die in MySQL und MariaDB am häufigsten verwendeten Typen zusammen — mit ihren Wertebereichen, ihrer Größe in Bytes und den typischen Anwendungsfällen.

Ganzzahlige numerische Typen

- TINYINT — 1 Byte, Wertebereich mit Vorzeichen −128…127 (ohne Vorzeichen 0…255). Nützlich für boolesche Flags oder kleine Zustände.
- SMALLINT — 2 Bytes, −32 768…32 767. Alter, kleine Mengen.
- MEDIUMINT — 3 Bytes, −8 388 608…8 388 607. Einzigartig in MySQL/MariaDB; außerhalb des Ökosystems selten genutzt.
- INT (INTEGER) — 4 Bytes, ±2,1·10⁹. Standardtyp für Primärschlüssel in mittelgroßen Tabellen.
- BIGINT — 8 Bytes, ±9,2·10¹⁸. Primärschlüssel in großen Tabellen, verteilte Bezeichner.

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

Dezimale und Gleitkomma-Typen

- DECIMAL(M, D) — exakte Präzision, M Gesamtstellen und D Nachkommastellen. Pflicht für Geldbeträge.
- FLOAT — 4 Bytes, ca. 7 signifikante Stellen. Näherungsweise.
- DOUBLE — 8 Bytes, ca. 15 Stellen. Näherungsweise.

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

Text und Zeichenketten

- CHAR(N) — feste Länge von N Zeichen, bis zu 255. Schnell, wenn alle Zeilen dieselbe Größe haben (Ländercodes, Hashes).
- VARCHAR(N) — variable Länge, bis zu 65 535 Bytes pro Zeile (geteilt mit den übrigen Spalten). Nutzt 1 oder 2 zusätzliche Bytes für die Länge.
- TEXT, MEDIUMTEXT, LONGTEXT — 64 KiB, 16 MiB, 4 GiB. Werden außerhalb der Zeile gespeichert; können ohne Präfix nicht als Schlüssel verwendet werden (KEY (col(255))).
- BLOB, MEDIUMBLOB, LONGBLOB — binäre Entsprechungen.

Datum und Zeit

- DATE — 3 Bytes, '1000-01-01'…'9999-12-31'.
- TIME — 3 Bytes, '-838:59:59'…'838:59:59'. Ja, es kann größer als 24 Stunden sein (Intervall, keine Tageszeit).
- DATETIME — 8 Bytes, ohne Zeitzone, ohne Umwandlung beim Speichern/Lesen. Speichert die literale Zeichenkette.
- TIMESTAMP — 4 Bytes, Wertebereich 1970…2038 (in MySQL 5.7) oder 1970…2106 (in MariaDB 10.4+). Wird in UTC gespeichert und in die time_zone der Verbindung umgewandelt.
- YEAR — 1 Byte, 1901…2155.

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

JSON

Nativer Typ in MySQL 5.7+ und MariaDB 10.2+. Erlaubt das Indizieren von mit JSON_EXTRACT oder ->> extrahierten Feldern und, seit MySQL 8.0, generierte Spalten mit Index (MULTI-VALUED INDEX bei Arrays).

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

Räumliche Typen (GIS)

- POINT, LINESTRING, POLYGON, GEOMETRY, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION.
- Benötigen einen SPATIAL-Index für effiziente Queries (MBRContains, ST_Distance, ST_Within).
- In MySQL 8.0 ist die SRID Pflicht, um räumliche Indizes zu nutzen.

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

ENUM und SET

- ENUM — speichert einen von N vordefinierten Werten (bis zu 65 535). Kompakt (1–2 Bytes), aber starr: Das Ändern der Liste erfordert ein ALTER TABLE.
- SET — Kombination von bis zu 64 Werten als Bitmap. Nützlich für Berechtigungen oder feste Labels.

Vermeide sie, wenn sich die Werteliste häufig ändert; eine Katalogtabelle + Fremdschlüssel ist wartbarer.

Wie du wählst

1. Nutze den kleinsten Typ, der den erwarteten Wertebereich abdeckt. Ein BIGINT, wo INT genügt, vervierfacht den Speicherplatz des Index.
2. Markiere Spalten als UNSIGNED, wenn du keine negativen Werte brauchst: Du verdoppelst den Wertebereich.
3. Vermeide NULL, wo du kannst: Eine Spalte NOT NULL DEFAULT … spart 1 Bit pro Zeile und bei Indizes.
4. VARCHAR(255) ist nicht teurer als VARCHAR(20), wenn die tatsächlichen Daten in 20 passen — nur die deklarierte Länge zählt für Präfix-Indizes.

Stichwörter: Datentypen, INT, BIGINT, VARCHAR, TEXT, JSON, DATETIME, TIMESTAMP, DECIMAL, ENUM, SET, POINT, SPATIAL, BLOB, Wertebereich, Bytes

Index-Typen (B-tree, Hash, Fulltext, räumlich), einfache vs. zusammengesetzte Indizes und Strategien, um das Lesen zu optimieren, ohne das Schreiben zu bestrafen.

Gilt für: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Ein Index beschleunigt die Suche zum Preis von Speicherplatz und von Kosten bei jedem INSERT/UPDATE/DELETE. Ein gutes Index-Design ist der Unterschied zwischen einer Query von Millisekunden und einer von Minuten.

Index-Typen

- B-tree — Standard in InnoDB. Unterstützt Suchen nach Gleichheit, Bereich (>, <, BETWEEN), Präfix (LIKE 'abc%') und Sortierung (ORDER BY).
- Hash — Unterstützt nur Gleichheit. Verfügbar in der Engine MEMORY. InnoDB pflegt einen internen adaptive hash index, den du nicht direkt steuerst.
- FULLTEXT — Natürliche und boolesche Textsuche. Verfügbar in InnoDB und MyISAM. Nützlich für lange TEXT-Felder.
- SPATIAL — R-tree für Typen wie POINT, POLYGON usw. Erfordert eine NOT NULL-Spalte.
- Multi-valued — Indiziert Elemente eines JSON-Arrays. Nur in MySQL 8.0.17+.

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

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

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

Einfach vs. zusammengesetzt

Ein zusammengesetzter Index über (A, B, C) deckt die Präfix-Suchen ab: WHERE A = ?, WHERE A = ? AND B = ?, WHERE A = ? AND B = ? AND C = ?, aber nicht WHERE B = ? für sich allein.

Faustregel: Ordne die Spalten des zusammengesetzten Index nach Selektivität (wie viele eindeutige Werte jede hat) und nach der Häufigkeit der Filter.

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

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

Covering Indexes

Ein Index, der alle von einer Query gelesenen Spalten enthält, vermeidet den Zugriff auf die Tabelle. Nutze EXPLAIN und suche nach Using index in der Spalte Extra.

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

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

Präfix-Indizes

Für lange TEXT- oder VARCHAR-Spalten indiziere nur die ersten N Zeichen. Das reduziert die Größe des Index bei vernünftig bleibender Selektivität.

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

Unsichtbare Indizes

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

Auswirkung Lesen vs. Schreiben

Jeder zusätzliche Index:
- Beschleunigt Queries, die ihn nutzen.
- Bestraft jedes INSERT, jedes UPDATE, das indizierte Spalten berührt, und jedes DELETE.
- Belegt zusätzlichen Speicher (oft 10 %–40 % der Tabellengröße).

In Tabellen mit massivem Schreiben (Logs, Metriken) halte die Zahl der Indizes auf das unverzichtbare Minimum.

Optimierungsstrategien

1. Beginne mit EXPLAIN — erkenne type: ALL (Full Scan) und key: NULL (kein Index genutzt).
2. Miss vor dem Optimieren — nutze das Slow Query Log, um die teuersten Queries zu finden.
3. Kombiniere Selektivität mit Reihenfolge — der zusammengesetzte Index muss der Reihenfolge der WHERE- und ORDER BY-Klauseln folgen.
4. Vermeide redundante Indizes — (A), (A, B), (A, B, C) sind untereinander redundant; (A, B, C) genügt.
5. Indiziere keine Spalten mit niedriger Kardinalität — ein Index über genero oder activo (1 oder 2 eindeutige Werte) hilft fast nie.

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

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

Stichwörter: Indizes, Index, B-tree, Hash, Fulltext, Spatial, Covering, zusammengesetzt, Selektivität, EXPLAIN, Prefix, invisible, Kardinalität

Erste, zweite und dritte Normalform, wann zu denormalisieren ist und wie sich Datenintegrität gegen Leistung abwägen lässt.

Gilt für: MySQL 5.7+ MariaDB 10.5+ Aurora 2+ PostgreSQL 13+ SQLite 3.35+

Die Normalisierung ist der Prozess, ein Schema so zu organisieren, dass Redundanz reduziert und Einfüge-, Aktualisierungs- und Löschanomalien vermieden werden. Die Normalformen sind kumulativ: Eine Tabelle in 3NF erfüllt auch 2NF und 1NF.

Erste Normalform (1NF)

- Jede Spalte enthält einen einzigen atomaren Wert (keine Listen und kein verschachteltes JSON, das mehrere Entitäten darstellt).
- Jede Zeile ist über einen Primärschlüssel identifizierbar.
- Keine sich wiederholenden Gruppen in Spalten (tel1, tel2, tel3).

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

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

-- SQLite: pro Verbindung einschalten, vor der ersten Transaktion
PRAGMA foreign_keys = ON;

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

Zweite Normalform (2NF)

- Erfüllt 1NF.
- Jede Nicht-Schlüssel-Spalte hängt vom vollständigen Primärschlüssel ab, nicht von einem Teil davon. Gilt für zusammengesetzte Schlüssel.

Beispiel: Eine Tabelle detalle_pedido (pedido_id, producto_id, cantidad, nombre_producto) verletzt 2NF, weil nombre_producto nur von producto_id abhängt, nicht vom vollständigen Paar.

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

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

Dritte Normalform (3NF)

- Erfüllt 2NF.
- Keine Nicht-Schlüssel-Spalte hängt von einer anderen Nicht-Schlüssel-Spalte ab (keine transitiven Abhängigkeiten).

Klassisches Beispiel: empleados (id, nombre, departamento_id, departamento_nombre). Der Name der Abteilung hängt von departamento_id ab, nicht direkt vom Schlüssel des Mitarbeiters.

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

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

BCNF und höhere Normalformen

Die Boyce-Codd-Normalform (BCNF) verschärft 3NF, und 4NF/5NF behandeln mehrwertige und Verbund-Abhängigkeiten. Für die meisten transaktionalen Schemas reicht es aus, sauber 3NF zu erreichen.

Wann denormalisieren

Die bewusste Denormalisierung bricht die Regeln, um Performance zu gewinnen. Sie ist berechtigt, wenn:

1. Massives Lesen, seltenes Schreiben — ein gecachtes Feld in der Tabelle (pedidos.total_pagado) erspart ein wiederkehrendes SUM(...).
2. Reporting / Analytik — Star- oder Snowflake-Schemas denormalisieren absichtlich.
3. Vorberechnete Ergebnisse — materialisierte Views oder Zusammenfassungstabellen.

Trade-offs, die du akzeptierst:

- Aktualisierungsanomalien — wenn sich das denormalisierte Datum ändert, muss es in N Zeilen aktualisiert werden.
- Vorübergehende Inkonsistenz — das gecachte Feld kann veralten, wenn die Aktualisierung mittendrin fehlschlägt.
- Trigger oder Anwendungslogik — du musst das Datum synchron halten.

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

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

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

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

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

Empfehlung

1. Entwirf standardmäßig in 3NF. Die Integrität dankt es dir.
2. Denormalisiere nur datenbasiert — miss die langsame Query, teste einen Cache und vergleiche.
3. Dokumentiere die Denormalisierung. Ohne einen Kommentar im DDL wird der nächste DBA sie "normalisieren", weil er sie für einen Fehler hält.

Stichwörter: Normalisierung, 1NF, 2NF, 3NF, BCNF, Denormalisierung, Redundanz, funktionale Abhängigkeit, Schlüssel, Integrität, Trigger

INNER, LEFT, RIGHT, CROSS, SELF und FULL OUTER JOIN: wann du welchen einsetzt, mit Beispielen auf einem typischen Bestellschema.

Gilt für: MySQL 5.7+ MariaDB 10.5+ Aurora 2+ PostgreSQL 13+ SQLite 3.35+

Ein JOIN kombiniert Zeilen aus zwei oder mehr Tabellen anhand einer Bedingung. Der Join-Typ bestimmt, was mit den Zeilen passiert, die keinen Partner finden.

Für die Beispiele nehmen wir an:

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

INNER JOIN

Gibt nur die Zeilen mit einem Partner in beiden Tabellen zurück. Es ist der Standard-Join und der am häufigsten verwendete.

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

LEFT JOIN (LEFT OUTER JOIN)

Alle Zeilen der linken Tabelle + die Partner aus der rechten. Felder ohne Partner rechts erscheinen als NULL. Nützlich für "alle X, mit ihrem Y, falls vorhanden".

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

Kunden ohne Bestellungen — klassisches Muster mit WHERE … IS NULL:

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

RIGHT JOIN

Das Gegenstück zum LEFT. Wird fast immer als LEFT JOIN mit vertauschter Reihenfolge geschrieben, das ist lesbarer.

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

CROSS JOIN

Kartesisches Produkt: jede Zeile aus A mit jeder Zeile aus B. Ohne ON-Klausel. Nützlich, um alle Kombinationen zu erzeugen (Kalender × Produkte für Reports).

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

SELF JOIN

Dieselbe Tabelle erscheint zweimal mit unterschiedlichen Aliassen. Nützlich für Hierarchien oder Vergleiche zwischen Zeilen derselben Tabelle.

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

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

FULL OUTER JOIN

Alle Zeilen beider Tabellen; die ohne Partner zeigen NULL auf der fehlenden Seite.

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

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

UNION

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

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

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

STRAIGHT_JOIN

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

-- SQLite: die geschriebene Reihenfolge gilt
SELECT c.nombre, p.id
FROM pedidos p
CROSS JOIN clientes c ON p.cliente_id = c.id;

Anti-Join und Semi-Join

Logische Muster, keine SQL-Schlüsselwörter:

- Semi-Join (es existiert mindestens ein Match) → EXISTS oder IN.
- Anti-Join (es existiert kein Match) → NOT EXISTS oder LEFT JOIN ... WHERE ... IS NULL.

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

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

NOT IN ist kein Anti-Join. Gibt die Unterabfrage auch nur ein NULL zurück, ist der Vergleich nie wahr und das Ergebnis sind null Zeilen. Gemessen mit 5 Kunden und 6 Bestellungen, davon 3 mit cliente_id auf NULL: NOT EXISTS und LEFT JOIN … IS NULL liefern 2, NOT IN liefert 0. Auf PostgreSQL 17.6, auf MySQL 8.0.46 und auf SQLite 3.51 gleichermaßen.

Leistung

1. Die Spalten im ON müssen indiziert sein, besonders auf der Seite des "inneren Joins" (die für jede Zeile des äußeren durchsucht wird).
2. Filtere so viel wie möglich vor dem Join (WHERE in jeder Tabelle, wo anwendbar).
3. Vermeide JOIN über Ausdrücke (ON LOWER(a.cod) = LOWER(b.cod)) — der Index wird nicht genutzt.
4. EXPLAIN zeigt die Lesereihenfolge und die Methode, im Vokabular der jeweiligen Engine.

-- So fragt man SQLite nach dem Plan, nicht mit bloßem EXPLAIN
EXPLAIN QUERY PLAN
SELECT c.id, c.nombre
FROM clientes c
WHERE NOT EXISTS (SELECT 1 FROM pedidos p WHERE p.cliente_id = c.id);

Stichwörter: Joins, INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, SELF JOIN, EXISTS, Semi-Join, Anti-Join, STRAIGHT_JOIN, Nested Loop

Kritische Performance-Variablen: Buffer Pool, Verbindungen, maximales Paket, Query-Cache und wichtige Unterschiede zwischen MySQL und MariaDB.

Gilt für: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Die Standardkonfiguration des Servers ist in Produktion fast nie die optimale. Dies sind die Variablen mit dem größten Einfluss auf Leistung und Stabilität.

innodb_buffer_pool_size

Der Hauptcache von InnoDB: Tabellen, Indizes und Daten. Es ist die wichtigste Variable.

- Faustregel: 60 %–80 % des RAM auf einem Server, der MySQL gewidmet ist.
- Empfohlenes Minimum: 1 GiB in Produktion.
- In MySQL 8.0+ und MariaDB 10.5+ lässt es sich im laufenden Betrieb ändern (ohne Neustart).

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

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

max_connections

Maximale Anzahl gleichzeitiger Verbindungen. Standardmäßig 151 in MySQL, 100 in MariaDB.

- Jede Verbindung verbraucht Speicher (thread_stack + Puffer pro Sitzung, ~256 KiB).
- Ein zu hoher Wert verschlechtert die Performance unter Last (Contention).
- Miss mit SHOW STATUS LIKE 'Max_used_connections'. Wenn es die Obergrenze erreicht, erhöhe schrittweise.

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

max_allowed_packet

Maximale Größe eines Protokollpakets (ein großes INSERT, ein LOAD DATA, ein BLOB).

- Standardmäßig 64 MiB in MySQL 8.0, 16 MiB in älteren Versionen.
- Wenn eine Operation es überschreitet: Fehler Got a packet bigger than 'max_allowed_packet' bytes.
- Eine Erhöhung auf 256 MiB oder 1 GiB ist bei Lasten mit BLOBs üblich.

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

Query Cache

Cacht das vollständige Ergebnis von SELECT-Queries.

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

Logs und Dauerhaftigkeit

innodb_flush_log_at_trx_commit steuert, wann das Redo Log geschrieben wird:

- 1 (Standard) — schreibt und fsync bei jedem Commit. Maximale Dauerhaftigkeit, minimale Geschwindigkeit. Striktes ACID.
- 2 — schreibt bei jedem Commit, fsync jede Sekunde. Ein Absturz des Betriebssystems kann ~1s verlieren. Fast ACID.
- 0 — schreibt und fsync jede Sekunde. Ein Absturz von MySQL kann ~1s verlieren. Kein ACID.

Auf Replikaten oder in Umgebungen, in denen du begrenzten Verlust tolerierst, kann 2 den Throughput um das 3- bis 5-Fache steigern. Ändere es auf dem Primary nicht, ohne das Risiko zu verstehen.

thread_pool

tmp_table_size / max_heap_table_size

Maximale Größe temporärer Tabellen im Speicher. Wenn eine Operation das Limit überschreitet, verlagert MySQL sie auf die Festplatte und verliert Geschwindigkeit. Halte beide Werte gleich.

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

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

innodb_io_capacity / innodb_io_capacity_max

IOPS, die InnoDB für die Bereinigung schmutziger Pages und für Purges verbrauchen kann. Standardmäßig 200 / 2000.

- Moderne SSDs: 2000 / 4000 oder mehr.
- HDD: belasse die Standardwerte.

Persistente Konfiguration

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

In MariaDB erfolgt die Persistenz durch Bearbeiten von my.cnf (/etc/my.cnf.d/) und Neustart oder durch die Verwendung von Includes (!include).

Allgemeine Empfehlung

1. Kenne die Last, bevor du etwas anfasst. Ein OLTP mit intensiven Schreibvorgängen wird anders konfiguriert als ein leseorientiertes Data Warehouse.
2. Ändere eine Variable nach der anderen und miss die Auswirkung.
3. Dokumentiere jede Änderung in my.cnf mit einem Kommentar, der das Warum erklärt.
4. Kopiere keine Konfigurationen aus Blogs, ohne sie zu verstehen — die "optimalen" Werte hängen stark von Hardware und Last ab.

Stichwörter: Konfiguration, 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

Maximale Größen von Datenbanken, Tabellen und Spalten, Namenslängen und in Bezeichnern erlaubte Zeichen.

Gilt für: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Die Limits der Engine zu kennen, erspart Überraschungen beim Wachsen. Dies sind die praktischen Obergrenzen in modernem MySQL und MariaDB.

Pro Datenbank

- Gesamtgröße: begrenzt durch das Dateisystem. Mit innodb_file_per_table = ON (Standard) ist jede Tabelle eine .ibd-Datei. Auf ext4 / XFS sprechen wir von theoretischen Exabytes — das echte Limit setzt dein Storage.
- Tabellen pro Datenbank: praktisch unbegrenzt. Der Katalog (information_schema, mysql.tables) verwaltet mehrere Hunderttausend problemlos. Lasten mit 10 000+ Tabellen erfordern eine Anpassung von table_open_cache.

Pro Tabelle

- Zeilen: theoretisch 2⁶⁴ Zeilen. Praktisch: Hunderte von Milliarden, wenn Schema und Indizes gut sind.
- Maximale Tabellengröße: 64 TiB mit der standardmäßigen INNODB_PAGE_SIZE (16 KiB).
- Spalten: maximal 4 096 pro Tabelle, aber das echte Limit bestimmt die row size, nicht die Anzahl.
- Maximale Zeilengröße: 65 535 Bytes (ohne BLOB/TEXT, die außerhalb der Zeile gespeichert werden).
- Indizes pro Tabelle: 64.
- Spalten pro Index: 16 (B-tree InnoDB).
- Maximale Länge des Schlüssels eines Index: 3072 Bytes mit DYNAMIC/COMPRESSED (Standardformat in MySQL 5.7+ / MariaDB 10.2+).

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

Pro Spalte

Bezeichner (Objektnamen)

- Datenbanken, Tabellen, Spalten, Indizes, Views: 64 Zeichen.
- Spalten-Aliasse: 256 Zeichen.
- Funktionen, Prozeduren, Trigger, Events: 64 Zeichen.
- Constraints (FK, CHECK, UNIQUE): 64 Zeichen.

In Bezeichnern erlaubte Zeichen

- Ohne Backticks: ASCII-Buchstaben, Ziffern, _ und $. Sie dürfen nicht mit einer reinen Ziffer beginnen oder nur aus Ziffern bestehen.
- Mit Backticks (`nombre raro`): jedes Unicode-Zeichen außer U+0000 (NUL).

Empfohlene Konvention: snake_case in ASCII (pedido_cliente_id). Vermeide Leerzeichen, Akzente und Großbuchstaben — manche Systeme normalisieren sie zwischen Linux und macOS unterschiedlich.

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

-- Recomendado
CREATE TABLE pedidos_2024 (numero_orden INT);

Groß-/Kleinschreibung

lower_case_table_names:

- 0 — die Namen werden so gespeichert, wie sie erstellt wurden, und beachten Groß-/Kleinschreibung. Standard unter Linux.
- 1 — die Namen werden in Kleinbuchstaben gespeichert und Vergleiche ignorieren die Groß-/Kleinschreibung. Standard unter macOS und Windows.
- 2 — sie werden unverändert gespeichert, aber Vergleiche ignorieren die Groß-/Kleinschreibung. Nur macOS/Windows.

Diesen Wert in einer bestehenden Installation zu ändern, ist destruktiv. Entscheide dich beim Initialisieren des Servers.

Pro Query

- Verschachtelte Subqueries: bis zu 64 Ebenen.
- UNION: theoretisch unbegrenzt, aber der Optimierer wird oberhalb von mehreren Hundert langsamer.
- Parameter in einem prepared statement: 65 535.
- Zeilen in einem IN(...): praktisch bis zu einigen Tausend; darüber hinaus besser JOIN mit einer temporären Tabelle.

Pro Sitzung

- Sitzungsvariablen (@@SESSION.xxx): können fast jede Runtime-Global setzen.
- Benutzervariablen (@variable): bis zu 64 Zeichen im Namen.

Charset und Collation

- Empfohlenes Charset: utf8mb4 (vollständiges UTF-8, 4 Bytes). Der Alias utf8 ist historisch und auf 3 Bytes begrenzt (ohne Emojis).
- Empfohlene Collation in MySQL 8.0+: utf8mb4_0900_ai_ci (case-insensitive, akzent-insensitiv, basierend auf Unicode 9).
- In MariaDB: utf8mb4_unicode_ci oder uca1400_ai_ci (10.10+).

ALTER DATABASE mi_base
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

ALTER TABLE clientes
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

Empfehlungen

1. Plane mit Reserve: Wenn du 10 Millionen Zeilen erwartest, dimensioniere Indizes und Partitionen für 100 M.
2. Nutze BIGINT UNSIGNED bei Primärschlüsseln von Tabellen, die wachsen können. INT ist bei ~2 Milliarden voll.
3. Definiere Charset und Collation explizit beim Erstellen von Datenbanken, Tabellen und Spalten. Das Erben vom Standard kann bei einer Migration fehlschlagen.
4. Dokumentiere die Limits deines Modells (erwartete Zeilen/Jahr, maximale Größe pro Spalte). Das hilft beim Capacity Planning und beim Erkennen anomaler Queries.

Stichwörter: Limits, Maximum, Größe, Spalten, Zeilen, Bezeichner, charset, collation, utf8mb4, lower_case_table_names, Index, row size

Schema-Design, Namenskonventionen, Backups, Replikation, Benutzersicherheit, minimale GRANTs und Auditierung.

Gilt für: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Operative Empfehlungen, die eine Amateur-Datenbank von einer in Produktion wartbaren unterscheiden.

Schema-Design

1. Jede Tabelle hat einen Primärschlüssel. Falls kein natürlicher existiert, füge id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY hinzu.
2. Explizite Typen: Deklariere NOT NULL und DEFAULT, wann immer die Spalte es zulässt. NULL muss "nicht anwendbar" bedeuten, nicht "nicht ausgefüllt".
3. Pflicht-Foreign-Keys zwischen verwandten Tabellen. Du verlierst Mikrosekunden beim Schreiben, gewinnst aber unzerstörbare referenzielle Integrität.
4. Immer InnoDB. MyISAM unterstützt weder FKs noch Transaktionen; es bleibt nur Legacy-Systemen vorbehalten.

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

Namenskonventionen

- Tabellen: snake_case, Plural, wenn sie Sammlungen darstellen (pedidos, clientes).
- Spalten: snake_case, ohne redundantes Präfix (nombre, nicht cliente_nombre innerhalb von clientes).
- Fremdschlüssel: <tabla>_id (cliente_id).
- Indizes: idx_<tabla>_<columnas> oder uq_<tabla>_<columnas> für eindeutige.
- Explizite Foreign Keys: fk_<tabla>_<tabla_destino>.
- Prozeduren / Funktionen: optionales Präfix sp_ / fn_, Verb im Infinitiv (fn_calcular_descuento).

Konsistenz > persönliche Vorliebe. Einigt euch im Team auf die Konvention und wende sie überall einheitlich an.

Backups

1. 3-2-1-Strategie: 3 Kopien, 2 verschiedene Medien, 1 außer Haus.
2. Typen:
- mysqldump — logisch, portabel, langsam bei der Wiederherstellung (~5–10 MB/s).
- mariabackup / xtrabackup — physisch, viel schneller, erfordert kurzes Anhalten der I/O.
- Filesystem-Snapshot (LVM, ZFS) — sofort, aber an das Dateisystem gebunden.
3. Wiederherstellung testen — ein Backup ohne getestete Wiederherstellung ist kein Backup.
4. Aufbewahrung: täglich 7 Tage + wöchentlich 4 + monatlich 12 ist ein vernünftiger Ausgangspunkt.

Calíope hat ein integriertes Backup-Modul: Hilfe › Backup.

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

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

Replikation

- Asynchrone Replikation (Standard) — der Primary wartet nicht auf das Replikat. Risiko: Verlust der letzten Transaktionen, wenn der Primary ausfällt.
- Halbsynchrone Replikation — der Primary wartet auf die Bestätigung mindestens eines Replikats, bevor er dem Client bestätigt.
- Replikationsgruppe (MySQL InnoDB Cluster, MariaDB Galera) — Multi-Primary mit Konsens.

Bewährte Praktiken:

1. GTID aktiviert (gtid_mode = ON) — notwendig für automatisches Failover und für moderne Werkzeuge.
2. binlog_format = ROW — robuster als STATEMENT bei nichtdeterministischen Funktionen.
3. Dedizierte Replikation — ein Benutzer replica mit nur REPLICATION SLAVE, eingeschränkter IP.
4. Überwachter Lag — SHOW REPLICA STATUS (SHOW SLAVE STATUS in älteren Versionen), Alarm, wenn Seconds_Behind_Source > 30.

Benutzer und Berechtigungen

Prinzip der minimalen Rechte: Jede Verbindung nutzt den am stärksten eingeschränkten Benutzer, der möglich ist.

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

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

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

Regeln:

1. Ein Benutzer pro Anwendung / pro Funktion. Erleichtert die Auditierung.
2. Keine *.*-Rechte für Anwendungsbenutzer. Vergib pro Datenbank oder pro Tabelle.
3. Kein Anwendungszugriff über den Benutzer root. Er ist nur für administrative Aufgaben.
4. Rotiere Passwörter und nutze robuste Authentifizierung (caching_sha2_password in MySQL 8, ed25519 in MariaDB).
5. Schränke den Host ein ('app'@'10.0.%.%'), nutze nicht '%'.

Auditierung

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

Calíope führt für jede Verbindungssitzung ein lokales Log der ausgeführten Queries (SQL-Log), unabhängig vom Server-Log.

Minimale Checkliste für Produktion

1. ✅ Automatische Backups + monatlich verifizierte Wiederherstellung.
2. ✅ Replikation mit überwachtem Lag.
3. ✅ Anwendungsbenutzer ohne übermäßige Rechte.
4. ✅ TLS verpflichtend für externe Verbindungen.
5. ✅ Slow Query Log aktiviert (long_query_time = 1).
6. ✅ Überwachung des Festplattenplatzes (Alarm bei 80 %).
7. ✅ Sicherheitsupdates vierteljährlich eingespielt.

Stichwörter: bewährte Praktiken, Design, Konventionen, Backup, Replikation, GTID, binlog, GRANT, Auditierung, Sicherheit, InnoDB, Foreign Key, TLS

Analyse von Queries mit EXPLAIN, Slow Query Log, Identifikation von Engpässen, InnoDB-Cache und Nutzung von performance_schema.

Gilt für: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Optimierung beginnt mit Messen. Ohne Daten ist Optimieren Raten. Dies sind die grundlegenden Werkzeuge.

EXPLAIN

Zeigt den Plan, den der Optimierer für eine Query gewählt hat. Es führt sie nicht aus — es ist sicher, es in Produktion abzusetzen.

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

Wichtige Spalten:

- type — Zugriffsmethode. Von best bis schlechtest: system → const → eq_ref → ref → range → index → ALL. ALL = Full Table Scan = schlecht bei großen Tabellen.
- key — gewählter Index. NULL = kein Index genutzt.
- rows — Schätzung der geprüften Zeilen. Wenn sie viel größer ist als die zurückgegebenen Zeilen, gibt es Verbesserungsspielraum.
- Extra — nützliche Hinweise:
- Using index — Covering Index (super).
- Using where — Filter angewendet nach dem Lesen der Zeilen.
- Using temporary — benötigt eine temporäre Tabelle (teuer).
- Using filesort — Sortierung außerhalb des Index (teuer bei großen Tabellen).

EXPLAIN ANALYZE (MySQL 8.0+ / MariaDB 10.1+)

Führt die Query aus und zeigt die echten Zeiten pro Knoten. Teurer als EXPLAIN, aber viel informativer.

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

Calíope hat ein integriertes Visual EXPLAIN, das den Baum des Plans rendert: Workspace › SQL-Analyse › Visual EXPLAIN.

Slow Query Log

Protokolliert alle Queries, die länger als long_query_time Sekunden dauern.

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

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

Analyse des Logs:

- mysqldumpslow — klassisches, mit MySQL mitgeliefertes Werkzeug.
- pt-query-digest (Percona Toolkit) — der De-facto-Standard, gruppiert nach Fingerprint und zeigt Statistiken.

performance_schema

Schema mit detaillierten Statistiken des Servers.

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

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

InnoDB-Cache

- Buffer pool — Daten und Indizes. Schlüsselmetrik: hit ratio (Innodb_buffer_pool_read_requests / (reads + reads_from_disk)). Ziel: >99 %.

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

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

- Adaptive hash index — automatischer Hash über heiße Seiten des Buffer Pools. Standardmäßig aktiviert.
- Change buffer — puffert Änderungen an Seiten, die nicht im Buffer Pool vorhanden sind.

Häufige Engpässe

Query-Optimierungen — häufige Muster

1. Wähle nur, was du brauchst. Vermeide SELECT * in Anwendungen.
2. Vermeide Funktionen auf indizierten Spalten:
- Schlecht: WHERE YEAR(fecha) = 2024 → nutzt keinen Index.
- Gut: WHERE fecha >= '2024-01-01' AND fecha < '2025-01-01'.
3. LIMIT mit großem Offset ist teuer — für tiefe Paginierung nutze Keyset Pagination: WHERE id > :last_seen ORDER BY id LIMIT 50.
4. COUNT(*) bei großen Tabellen — InnoDB führt keinen Zähler. Erwäge Zusammenfassungsspalten oder Schätzungen (information_schema.tables.table_rows).
5. Nicht korrelierte Subqueries werden einmal ausgeführt; korrelierte einmal pro äußerer Zeile. Schreibe sie als JOIN um, wenn möglich.

Empfehlung

Erstelle ein grundlegendes Monitoring-Dashboard (Calíope hat eines: Dashboard) mit:

- Verbindungen (Threads_connected, Threads_running).
- Buffer Pool Hit Ratio.
- Langsame Queries pro Minute.
- Replication Lag.
- Festplattenplatz pro Tablespace.

Je früher du eine Verschlechterung erkennst, desto leichter lässt sie sich beheben.

Stichwörter: Performance, Optimierung, EXPLAIN, EXPLAIN ANALYZE, slow query log, performance_schema, buffer pool, filesort, covering index, filter, Engpass, pt-query-digest

ACID, COMMIT und ROLLBACK, die vier Isolationsstufen und welche Anomalie jede zulässt.

Gilt für: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Eine Transaktion fasst mehrere Anweisungen zu einer Einheit zusammen, die entweder ganz oder gar nicht wirkt. In InnoDB läuft jede Anweisung in einer Transaktion: öffnest du keine, öffnet und bestätigt der Server eine pro Anweisung (autocommit = 1).

ACID
- Atomarität — entweder greifen alle Änderungen oder keine.
- Konsistenz — die Datenbank geht von einem gültigen Zustand in den nächsten; Constraints bleiben erfüllt.
- Isolation — nebenläufige Transaktionen sehen einander nie halbfertig.
- Dauerhaftigkeit — was bestätigt ist, übersteht einen Serverabsturz.

Manuelle Steuerung
COMMIT bestätigt, ROLLBACK verwirft alles seit START TRANSACTION.

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

Sicherungspunkte
Ein SAVEPOINT nimmt nur einen Teil zurück, ohne den Rest der Transaktion zu verlieren:

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;

Die vier Stufen

InnoDBs Standard ist REPEATABLE READ. Dank MVCC und Gap Locks verhindert InnoDB auf dieser Stufe auch Phantom Reads — was der SQL-Standard gar nicht verlangt.

Stufe wechseln

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT @@transaction_isolation;

Vorsicht bei DDL
CREATE, ALTER, DROP und TRUNCATE lösen einen impliziten Commit aus: mit ROLLBACK ist da nichts mehr zu retten. Eine halb gelaufene Migration lässt die Tabelle so, wie sie gerade ist.

Lange Transaktionen
Eine offene Transaktion zwingt InnoDB, die alten Versionen jeder Zeile für konsistente Lesevorgänge aufzubewahren. Ein vergessenes START TRANSACTION lässt das Undo-Log wachsen und zieht den ganzen Server herunter. So findest du sie:

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

Empfehlung
Kurze Transaktionen, Geschäftslogik draußen, nur SQL drinnen. READ COMMITTED senkt die Sperren und ist das, was viele Webanwendungen nutzen; bleib bei REPEATABLE READ, wenn zwei Lesevorgänge innerhalb derselben Transaktion dasselbe liefern müssen.

Stichwörter: transaktion, commit, rollback, savepoint, isolation, acid, mvcc, read committed, repeatable read, serializable, autocommit, innodb_trx

Was InnoDB sperrt, warum ein Deadlock entsteht und wie man ihn diagnostiziert, statt zu raten.

Gilt für: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

InnoDB sperrt Zeilen, keine Tabellen, und tut das beim Schreiben automatisch. Fast jedes Nebenläufigkeitsproblem erklärt sich daraus, welche Zeilen eine Abfrage am Ende gesperrt hat — und das hängt am Index, den sie benutzt hat.

Sperrarten
- Gemeinsam (S) — mehrere Transaktionen dürfen dieselbe Zeile gleichzeitig lesen.
- Exklusiv (X) — nimmt, wer schreibt; niemand sonst darf sie sperrend lesen oder schreiben.
- Gap — sperrt den Raum zwischen zwei Indexwerten, um Einfügungen zu verhindern. Nur in REPEATABLE READ und SERIALIZABLE.
- Next-Key — die Zeile plus die Lücke davor. Das ist InnoDBs Normalmodus beim Durchlaufen eines Index.
- Intention (IS/IX) — markiert auf Tabellenebene, dass innen Zeilensperren liegen; verhindert, dass ein LOCK TABLES dazwischenrutscht.

Die praktische Folge: nutzt die Abfrage keinen Index, durchläuft InnoDB die ganze Tabelle und sperrt jede geprüfte Zeile, nicht nur die passenden. Ein guter Index beschleunigt also nicht nur — er verkleinert auch, was gesperrt wird.

Sperrende Lesevorgänge
Ein normales SELECT sperrt nichts (es liest über MVCC eine konsistente Version). Musst du lesen und danach schreiben, ohne dass jemand dazwischenkommt, fordere die Sperre ausdrücklich mit FOR UPDATE oder FOR SHARE an:

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

Was ein Deadlock ist
Zwei Transaktionen, von denen jede auf eine Sperre der anderen wartet. Keine kommt weiter:

-- 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 erkennt das selbst und beendet die Transaktion, deren Rücknahme am billigsten ist; sie bekommt den Fehler 1213 Deadlock found when trying to get lock. Das ist weder ein Serverfehler noch Korruption, sondern das richtige Verhalten — die Anwendung muss diese Transaktion wiederholen.

Etwas anderes ist Fehler 1205 Lock wait timeout exceeded: da gibt es keinen Zyklus, nur ein Warten, das innodb_lock_wait_timeout überschritten hat (standardmäßig 50 s).

Diagnose
Der Abschnitt LATEST DETECTED DEADLOCK von SHOW ENGINE INNODB STATUS bewahrt den letzten Deadlock mit beiden Transaktionen und den beteiligten Anweisungen auf. Um die Sperren von jetzt zu sehen, hilft performance_schema:

SHOW ENGINE INNODB STATUS;

SELECT * FROM performance_schema.data_locks;

SELECT * FROM performance_schema.data_lock_waits;

SELECT @@innodb_lock_wait_timeout;

Wie man sie vermeidet
1. Immer in derselben Reihenfolge zugreifen — fasst aller Code Tabellen und Zeilen in gleicher Ordnung an, kann kein Zyklus entstehen.
2. Kurze Transaktionen — weniger Zeit mit gehaltenen Sperren, weniger Gelegenheit zum Zusammenstoß.
3. Indizieren, wonach gefiltert wird — verhindert das Sperren von Zeilen, die gar nicht passten.
4. Wiederholen — ein gelegentlicher Deadlock ist in einem nebenläufigen System normal; pack die Transaktion in eine Wiederholung mit wachsender Wartezeit.
5. Unnötige SELECT ... FOR UPDATE vermeiden — wer nicht schreiben will, soll auch nicht sperren.

Empfehlung
Bei Sperren zuerst den Abfrageplan ansehen: die meisten echten Deadlocks verschwinden, sobald der fehlende Index da ist. Die Prozessliste von Calíope zeigt dir, welche Sitzung wartet.

Stichwörter: sperre, lock, deadlock, gap lock, next-key, for update, for share, fehler 1213, innodb status, data_locks, lock wait timeout

RANGE, LIST, HASH und KEY, Partition Pruning und sofortiges Aufräumen mit DROP PARTITION.

Gilt für: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Partitionierung zerlegt eine Tabelle in mehrere physische Teile, die der Server weiterhin als eine einzige Tabelle sieht. Sie macht Abfragen nicht auf magische Weise schnell: was sie bringt, ist Partition Pruning und vor allem die Möglichkeit, Millionen Zeilen in einem Augenblick zu löschen.

Wann es sich lohnt
Der klare Fall ist eine Tabelle, die nach Datum wächst und aus der Altes weggeräumt wird: Logs, Ereignisse, Metriken, Audit. Dort ersetzt DROP PARTITION ein DELETE, das Stunden dauert.

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

Die vier Arten
- RANGE — nach Intervallen eines sortierbaren Werts, fast immer ein Datum. Die nützlichste.
- LIST — nach Zugehörigkeit zu einer festen Menge von Werten.
- HASH — gleichmäßige Verteilung über einen ganzzahligen Ausdruck; gut zum Verteilen von Schreibzugriffen, nicht zum Pruning.
- KEY — wie HASH, aber mit der internen Funktion des Servers; nimmt auch nicht-ganzzahlige Spalten.

Die Varianten RANGE COLUMNS und LIST COLUMNS nehmen mehrere Spalten und nicht-ganzzahlige Typen, ohne sie in eine Funktion zu packen:

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

Partition Pruning
Der eigentliche Gewinn: filtert das WHERE über die Partitionsspalte, liest der Server nur die Partitionen, die Treffer enthalten können. Prüf das in der Spalte partitions von EXPLAIN — tauchen alle auf, prunst du gar nichts und die Partitionierung kostet dich nur.

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

Grenzen, die man vorher kennen sollte
1. Der Partitionierungsschlüssel muss Teil jedes eindeutigen Schlüssels sein, den Primärschlüssel eingeschlossen. Darum trägt das Beispiel PRIMARY KEY (id, ocurrido) und nicht nur id.
2. Keine Fremdschlüssel: eine partitionierte Tabelle kann weder einen FOREIGN KEY haben noch Ziel eines solchen sein.
3. Höchstens 8192 Partitionen je Tabelle, und jede verbraucht Dateideskriptoren.
4. Abfragen, die nicht über den Schlüssel filtern, fassen alle Partitionen an und werden langsamer als ohne Partitionierung.
5. Indizes sind lokal zu jeder Partition: einen globalen Index gibt es nicht.

Wartung
Die Partition der nächsten Periode anlegen und die älteste fallen lassen ist die normale Routine. DROP PARTITION läuft praktisch sofort und gibt den Platz wirklich frei, was ein massenhaftes DELETE nicht tut:

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;

Empfehlung
Partitioniere nur dann nach Datum, wenn du auch nach Datum aufräumst, und lege künftige Partitionen im Voraus an (oder per geplantem Event): passt eine Zeile in keinen Bereich, scheitert das INSERT. Lass immer eine pmax als Netz stehen.

Stichwörter: partitionierung, partition, range, list, hash, key, pruning, drop partition, reorganize, information_schema.partitions, aufräumen

WITH, WITH RECURSIVE und OVER (): das moderne SQL, das verschachtelte Unterabfragen und Temp-Tabellen erspart.

Gilt für: MySQL 8.0+ MariaDB 10.2+ Aurora 3+ PostgreSQL 13+ SQLite 3.35+

CTEs (WITH) und Fensterfunktionen (OVER ()) kamen mit MySQL 8.0 und MariaDB 10.2; in PostgreSQL gibt es keine noch unterstützte Version ohne sie, und in SQLite liegen beide weit unter der Untergrenze dieses Handbuchs: CTEs seit 3.8.3 und Fenster seit 3.25. Sie lösen in einer lesbaren Abfrage, wofür früher verschachtelte Unterabfragen, temporäre Tabellen oder Sitzungsvariablen nötig waren.

CTE: einem Zwischenschritt einen Namen geben
Eine CTE ist ein benanntes Ergebnis, das nur während der Abfrage lebt. Damit zerlegst du eine lange Abfrage in Schritte und beziehst dich zweimal auf dasselbe Teilergebnis, ohne es zu wiederholen:

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;

Rekursive CTE: Hierarchien
WITH RECURSIVE läuft durch Baumstrukturen — Organigramme, verschachtelte Kategorien, Stücklisten — ganz ohne Schleifen in der Anwendung. Der erste Zweig ist der Basisfall, der zweite wiederholt sich, bis er keine Zeilen mehr liefert:

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;

Fensterfunktionen: rechnen ohne zu gruppieren
Ein GROUP BY faltet Zeilen zusammen; eine Fensterfunktion rechnet über eine Menge verwandter Zeilen und behält jede Zeile. Genau das macht laufende Summe, gleitenden Durchschnitt oder Platz innerhalb der Gruppe in einem Durchgang möglich:

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;

Die gebräuchlichsten
- ROW_NUMBER() — laufende Nummer innerhalb der Partition, ohne Gleichstand.
- RANK() / DENSE_RANK() — Platz mit Gleichstand; RANK lässt Lücken, DENSE_RANK nicht.
- LAG() / LEAD() — der Wert der vorigen oder nächsten Zeile, ohne Self-Join.
- FIRST_VALUE() / LAST_VALUE() — die Enden des Fensters.
- NTILE(n) — verteilt die Zeilen auf n Eimer, für Quartile und Perzentile.
- Aggregate mit OVER — SUM, AVG, COUNT, MIN, MAX, ohne die Zeilen zusammenzufalten.

Der Rahmen (ROWS BETWEEN ...) legt fest, welche Zeilen in die Berechnung jeder Zeile eingehen. Standardmäßig läuft ein Aggregat mit ORDER BY vom Anfang der Partition bis zur aktuellen Zeile — genau das ergibt die laufende Summe.

Das Muster mit dem größten Gewinn: Top N je Gruppe
Die drei meistverkauften Produkte je Kategorie ohne Fenster zu holen, verlangt eine korrelierte Unterabfrage pro Zeile. Mit ROW_NUMBER() geht es direkt:

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
Umsonst ist keins von beiden: das Fenster muss innerhalb jeder Partition sortieren, also erspart ein Index, der die Zeilen schon in der Reihenfolge von PARTITION BY plus ORDER BY liefert, genau diese Sortierung. Prüf das mit EXPLAIN, bevor du die hübsche Fassung für gut erklärst.

Empfehlung
Nimm CTEs, damit die Abfrage lesbar bleibt, und Fenster, um nicht in der Anwendung zu tun, was der Server in einem Durchgang erledigt. Läuft dein Server auf MySQL 5.7 oder MariaDB 10.1, gibt es beides nicht: dort regieren weiterhin die Unterabfragen.

Stichwörter: cte, with, with recursive, fensterfunktion, over, partition by, row_number, rank, dense_rank, lag, lead, ntile, frame, hierarchie, top n je gruppe

Warum ein Fehler die ganze Transaktion blockiert, wie ein Savepoint dich herausholt, was jede Stufe wirklich tut, und DDL, das sich zurückrollen lässt.

Gilt für: PostgreSQL 13+

Eine Transaktion fasst mehrere Anweisungen zu einer Einheit zusammen, die ganz oder gar nicht angewendet wird. Ohne explizites BEGIN bestätigt PostgreSQL jede Anweisung für sich.

Die erste Überraschung, wenn man von MySQL kommt
Ein Fehler bricht die gesamte Transaktion ab. Ab da antwortet jede Anweisung dasselbe — current transaction is aborted, commands ignored until end of transaction block, SQLSTATE 25P02 — bis du ROLLBACK machst. Das ist kein Fehler der Anwendung: es ist das Prinzip, und es verhindert, dass eine Transaktion auf einem Zustand weiterläuft, der nicht mehr der ist, den du dachtest.

Der Ausweg ist ein Savepoint
SAVEPOINT markiert einen Punkt zum Zurückkehren, und ROLLBACK TO SAVEPOINT rettet die Transaktion, ohne das Vorherige zu verlieren:

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

DDL lässt sich zurückrollen
CREATE, ALTER und DROP laufen innerhalb der Transaktion: hier gibt es kein implizites Commit. Eine Migration, die auf halbem Weg scheitert, hinterlässt keine halbe Tabelle.

BEGIN;
ALTER TABLE cuentas ADD COLUMN moneda text;
ROLLBACK;   -- die Spalte hat nie existiert

Die vier Stufen

READ UNCOMMITTED wird akzeptiert und auch so gemeldet, verhält sich aber wie READ COMMITTED: In PostgreSQL gibt es auf keiner Stufe Dirty Reads. Die Voreinstellung ist READ COMMITTED.

REPEATABLE READ und SERIALIZABLE blockieren nicht: sie brechen ab
Statt zu warten, endet die Transaktion, die sich nicht serialisieren lässt, mit SQLSTATE 40001 (could not serialize access…). Das heißt, die Anwendung muss wiederholen: auf diesen beiden Stufen ist ein 40001 normaler Betrieb, kein Ausfall. SERIALIZABLE verwendet SSI, das Lese-/Schreib-Abhängigkeiten erkennt und keine zusätzlichen Sperren nimmt.

Sperren und Deadlocks
Ein Deadlock wird nach deadlock_timeout erkannt — 1 s in der Voreinstellung — und der Server beendet eine der beiden mit SQLSTATE 40P01. Um nicht zu warten oder um Arbeit unter Konsumenten aufzuteilen:

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

FOR UPDATE NOWAIT scheitert sofort mit 55P03, statt zu warten. Achtung: dieser Fehler bricht die Transaktion ebenfalls ab.

Lange Transaktionen
Hier lässt eine offene Transaktion kein Undo-Log anwachsen: sie hindert VACUUM daran, tote Zeilenversionen aufzuräumen — serverweit —, und die Tabelle wächst ohne neue Zeilen. So findest du sie:

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 steht per Voreinstellung auf 0, also ohne Grenze; ihm einen Wert zu geben ist das Netz, das verhindert, dass eine vergessene Sitzung die ganze Datenbank ausbremst.

Empfehlung
Kurze Transaktionen, mit der Geschäftslogik außerhalb. Wenn du auf REPEATABLE READ oder SERIALIZABLE hochgehst, schreib den Wiederholungsversuch vorher, nicht nach dem ersten 40001 in Produktion.

Stichwörter: Transaktion, commit, rollback, savepoint, Isolation, mvcc, read committed, repeatable read, serializable, ssi, 25P02, 40001, 40P01, Deadlock, skip locked, pg_stat_activity

Warum eine Tabelle ohne neue Zeilen wächst, was jede Operation wirklich aufräumt, wann die Zähler lügen und was Wraparound ist.

Gilt für: PostgreSQL 13+

In PostgreSQL ändert ein Update eine Zeile nicht: es schreibt eine neue Version und lässt die alte tot zurück. Löschen gibt ebenfalls nichts sofort frei. So funktioniert MVCC hier, und VACUUM räumt hinterher auf. Dazu gibt es in InnoDB keine Entsprechung, und es steckt hinter fast jeder Überraschung bei der Größe.

Wie das aussieht
Eine Tabelle mit 50 000 Zeilen belegte 12 MB. Ein einziges UPDATE über alle Zeilen ließ sie bei 23 MB zurück, ohne eine einzige Zeile hinzuzufügen: die 50 000 alten Versionen stehen weiter in der Datei. Gemessen auf PostgreSQL 17.6.

Was jede Sache tut
- VACUUM markiert den toten Platz als wiederverwendbar. Es gibt den Platz nicht an das Betriebssystem zurück: nach dem Vacuum belegte die Beispieltabelle weiterhin 23 MB, nur passen die nächsten Schreibvorgänge jetzt hinein.
- VACUUM FULL schreibt die ganze Tabelle neu und gibt den Platz tatsächlich zurück — sie fiel auf 11 MB —, nimmt aber eine ACCESS EXCLUSIVE-Sperre: währenddessen liest und schreibt niemand, und es braucht Platz für eine vollständige Kopie. Das ist keine Routinewartung, sondern das letzte Mittel.
- ANALYZE räumt nichts auf: es frischt die Planer-Statistiken auf.

Autovacuum, das schon an ist
autovacuum kommt mit on. Eine Tabelle kommt in die Warteschlange, wenn ihre toten Zeilen autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × Zeilen überschreiten, also 50 + 20 % mit den Voreinstellungen. Bei einer Tabelle mit zehn Millionen Zeilen sind das zwei Millionen tote Zeilen, bevor sich etwas rührt: bei großen, viel aktualisierten Tabellen senkt man den Faktor pro Tabelle:

ALTER TABLE pedidos SET (autovacuum_vacuum_scale_factor = 0.02);

Prüfen, ob es läuft

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;

Vorsicht mit diesem Zähler: er ist eine Schätzung und nicht sofort aktuell. Gemessen auf 17.6 stand er direkt nach dem Aktualisieren von 50 000 Zeilen noch auf 0; erst nach einem ANALYZE zeigte er 50 000, und nach dem VACUUM ging er wieder auf 0. Wenn du gerade viel geschrieben hast und die Zahl sich nicht bewegt, heißt das nicht, dass keine Arbeit ansteht.

Einem laufenden Vacuum folgt man so:

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

Der Feind: die offene Transaktion
VACUUM kann nur aufräumen, was niemand mehr sehen kann. Eine offene Transaktion — oder eine Replik mit hot_standby_feedback — friert diesen Horizont ein, und dann läuft das Vacuum, meldet Erfolg und gibt nichts frei. Deshalb lässt eine in idle in transaction vergessene Sitzung Tabellen wachsen, die sie nicht einmal anfasst.

Der Wraparound, der wirklich ein Notfall ist
Transaktions-IDs sind 32 Bit groß und werden wiederverwendet. Damit keine Zeile in der Zukunft landet, friert Vacuum die alten ein. autovacuum_freeze_max_age steht per Voreinstellung auf 200 000 000: jenseits dieses Alters startet der Server ein Autovacuum, das nicht aufgeschoben werden kann, und wenn es trotzdem ausgeht, nimmt er keine Schreibvorgänge mehr an. Überwacht wird das so:

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

Solange dieses Alter weit unter zweihundert Millionen bleibt, ist nichts zu tun.

Empfehlung
Schalte Autovacuum nicht ab. Wenn eine Tabelle ohne neue Zeilen wächst, ist die Reihenfolge des Verdachts: eine offene Transaktion, ein für ihre Größe zu hoher scale_factor, und erst am Ende VACUUM FULL — mit Wartungsfenster, weil es die ganze Tabelle sperrt.

Stichwörter: vacuum, autovacuum, bloat, mvcc, n_dead_tup, vacuum full, einfrieren, wraparound, pg_stat_user_tables, pg_stat_progress_vacuum, datfrozenxid, hot_standby_feedback, idle in transaction

Was jede Anweisung wirklich sperrt, warum ein ALTER TABLE die SELECTs anhalten kann, wie man sieht, wer auf wen wartet, und was bei einem 40P01 zu tun ist.

Gilt für: PostgreSQL 13+

In PostgreSQL leben Sperren an zwei verschiedenen Orten, und sie zu verwechseln ist der Grund, warum ein Problem unauffindbar wird. Tabellensperren stehen in pg_locks; Zeilensperren stecken in der Zeile selbst, in ihrem Kopf, und deshalb brauchen sie keinen Speicher, eskalieren nie zu einer Tabellensperre und tauchen in pg_locks nicht auf. Eine Million gesperrter Zeilen kostet nicht mehr als eine einzige.

Und eine Regel kennt keine Ausnahme: ein normales SELECT wartet nie auf eine Zeile. Es liest seine Version über MVCC. Das Einzige, was ein SELECT aufhalten kann, ist eine Tabellensperre.

Wer welchen Tabellenmodus nimmt

Die ersten drei stören einander nicht, und deshalb blockiert die normale Last nie. Der letzte steht mit allen im Konflikt, auch mit SELECT.

Die Falle: ein wartendes ALTER TABLE staut alles hinter sich
Dieses ACCESS EXCLUSIVE drängelt sich nicht vor: es stellt sich an. Und während es wartet, wartet alles, was danach kommt, hinter ihm, sogar ein SELECT, das mit der Anweisung davor kein Problem gehabt hätte. Eine einzige offene Transaktion, die nur ein SELECT ausgeführt hat, genügt, damit ein ALTER TABLE die Tabelle für alle stilllegt, ohne mit der Arbeit begonnen zu haben. Deshalb wird DDL in der Produktion mit einer Obergrenze abgesetzt und wiederholt:

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

Vier Zeilenmodi, nicht zwei
Von stark nach schwach: FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE. Nur drei Paare vertragen sich —die beiden geteilten untereinander und FOR NO KEY UPDATE mit FOR KEY SHARE—; FOR UPDATE kollidiert mit allen vieren. Genau dieses ungewöhnliche Paar ist das wichtige: ein UPDATE, das den Schlüssel nicht anfasst, nimmt FOR NO KEY UPDATE und blockiert damit nicht die Prüfung eines Fremdschlüssels, der auf diese Zeile zeigt — denn die verlangt FOR KEY SHARE.

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

Hier wird ewig gewartet
lock_timeout steht standardmäßig auf 0, also ohne Grenze: es gibt kein Gegenstück zu MySQLs innodb_lock_wait_timeout, das nach 50 s abschneidet. Es zu setzen —pro Sitzung, vor einer riskanten Anweisung oder in der Konfiguration— verwandelt ein endloses Warten in einen Fehler, den die Anwendung wiederholen kann. Wenn er auslöst: SQLSTATE 55P03.

Absichtlich nicht warten

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

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

NOWAIT scheitert sofort mit 55P03 —und dieser Fehler bricht, wie jeder andere, die ganze Transaktion ab—. SKIP LOCKED scheitert nicht: es liefert weniger Zeilen. Mit Zeile 3 durch eine andere Sitzung gesperrt lieferte die zweite Abfrage 1, 2, 4 und 5. So verteilt man eine Arbeitswarteschlange auf mehrere Verbraucher, ohne dass sie sich in die Quere kommen oder aufeinander warten.

Die Verklemmung

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

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

Der Server tötet eine der beiden mit SQLSTATE 40P01 («deadlock detected»), und das Detail nennt Prozess und Zeile. Er erkennt es aber nicht sofort: er sucht den Zyklus erst, wenn ein Warten deadlock_timeout überschreitet — standardmäßig 1 s —, und das Opfer braucht diese Sekunde zum Sterben. InnoDB erkennt es augenblicklich; hier kostet eine Verklemmung eine Sekunde Wartezeit. deadlock_timeout zu senken ist nicht umsonst: diese Arbeit fällt auch bei den normalen Wartezeiten an, und die sind die Mehrheit.

Ein 40P01 ist kein Serverfehler: es ist das korrekte Verhalten, und die Anwendung muss diese Transaktion wiederholen.

Diagnose
Ein SHOW ENGINE INNODB STATUS gibt es hier nicht. Es gibt zwei Abfragen, und beide müssen laufen, solange die Sperre besteht:

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 liefert die Liste der Prozesse, die halten, was der andere verlangt — genau die Frage, die man wirklich hat. Was Sie in pg_locks nicht sehen werden, sind die Zeilensperren: wer auf eine Zeile wartet, erscheint wartend auf eine transactionid, die Nummer der Transaktion, die sie hält. Und um eine Spur des bereits Geschehenen zu hinterlassen, schreibt log_lock_waits jedes Warten oberhalb von deadlock_timeout ins Serverprotokoll.

Die Prozessliste von Calíope zeigt dieses Warten in der Statusspalte: eine blockierte Sitzung erscheint als Lock: transactionid.

Beratende Sperren
Keine Tabelle, keine Daten: eine Zahl, die der Server für Sie verwahrt, damit zwei Prozesse Ihrer Anwendung nicht gleichzeitig dasselbe tun.

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

Solange eine Sitzung die 42 hält, gibt pg_try_advisory_lock(42) aus einer anderen false zurück, statt zu warten. Achtung bei der Reichweite: die auf Sitzungsebene überleben das COMMIT und werden nur durch Freigeben oder Schließen der Verbindung gelöst; pg_advisory_xact_lock löst sich am Ende der Transaktion von selbst, was fast immer das Gewünschte ist.

SERIALIZABLE blockiert nicht
Auf dieser Stufe erscheinen in pg_locks Sperren vom Typ SIReadLock. Sie blockieren niemanden: sie sind die Markierung dessen, was die Transaktion gelesen hat, und der Konflikt kommt beim Bestätigen als 40001, nicht als Warten.

Empfehlung
Zeilen immer in derselben Reihenfolge anfassen und Transaktionen kurz halten, wie bei jeder Engine. Eigen ist hier zweierlei: lock_timeout vor jedem DDL gesetzt, weil der Wartende alle hinter sich aufstaut; und der Wiederholungsversuch vor dem Produktivgang geschrieben, das Einzige, was ein 40P01 harmlos macht.

Stichwörter: Sperre, lock, Verklemmung, deadlock, 40p01, 55p03, pg_locks, pg_blocking_pids, lock_timeout, deadlock_timeout, access exclusive, for update, for key share, skip locked, nowait, beratende Sperre, advisory

Die sechs Indexmethoden und wann welche passt, partielle und Ausdrucksindizes, warum ein Index Only Scan manchmal doch zur Tabelle geht, und wie man die findet, die niemand nutzt.

Gilt für: PostgreSQL 13+

Ein Index beschleunigt Suchen auf Kosten von Speicherplatz und Arbeit bei jedem Schreibvorgang. Was sich beim Umstieg von MySQL ändert, ist nicht dieser Gedanke, sondern dass es hier sechs Methoden gibt statt einer mit Ausnahmen — und dass fast alles, was in MySQL eine Option des Index ist —das Präfix, die Unsichtbarkeit—, hier etwas anderes ist.

Die sechs Methoden

Die Größen erklären die Wahl besser als die Theorie. Auf einer Tabelle mit 200 000 Zeilen und 22 MB, mit einem Zeitstempel, der mit dem Einfügen wächst:

- B-Baum auf dieser Spalte: 4 408 kB.
- BRIN auf derselben Spalte: 24 kB.

BRIN speichert nicht die Zeilen, sondern Minimum und Maximum jedes Blocks; er hilft also nur, wenn die physische Reihenfolge der Wertereihenfolge ähnelt — und wenn er hilft, kostet er fast nichts. In derselben Tabelle belegte ein Hash auf der Kundenspalte 7 032 kB und der B-Baum auf dieser Spalte 1 400 kB: kleiner, und zusätzlich für Bereiche und Sortierung brauchbar. Deshalb ist der B-Baum die Standardantwort und der Hash ein Sonderfall.

Partielle Indizes: die halbe Idee, ein Zehntel der Größe
Ein Index darf ein WHERE tragen und indiziert dann nur die passenden Zeilen. Wenn Sie die Warteschlange der offenen Vorgänge abfragen und diese 5 % ausmachen, indizieren Sie die 5 %:

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

Auf derselben Tabelle gemessen: der vollständige Index der Spalte belegte 1 400 kB, der partielle 88 kB. Das WHERE der Abfrage muss das des Index implizieren, sonst nutzt der Planer ihn nicht.

Hier gibt es keine Präfixindizes, sondern Ausdrucksindizes
CREATE INDEX … ON paginas (url(64)) ist keine gültige Syntax; der Server liest url(64) als Funktionsaufruf und antwortet 42883 function url(integer) does not exist. Das Äquivalent ist, den Ausdruck zu indizieren:

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

Und das Kleingedruckte: der Index greift nur, wenn die Abfrage den Ausdruck genauso schreibt. WHERE lower(email) = 'ana@ejemplo.com' nutzt ihn; WHERE email ILIKE 'Ana@%' nicht — und frisst die ganze Tabelle.

Abdeckende Indizes, und warum sie manchmal nicht abdecken
INCLUDE fügt Spalten hinzu, die im Index gespeichert werden, ihn aber nicht sortieren:

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

Damit zeigt EXPLAIN einen Index Only Scan. Aber „only" ist nur ein halbes Versprechen: PostgreSQL kann dem Index nicht ansehen, ob eine Zeile für Ihre Transaktion sichtbar ist, und fragt deshalb die Sichtbarkeitskarte, die VACUUM pflegt. Gemessen: unmittelbar nach der Aktualisierung von tausend Zeilen sagte derselbe Plan Heap Fetches: 2; nach einem VACUUM Heap Fetches: 0. Ein abdeckender Index auf einer Tabelle, die beschrieben und nicht aufgeräumt wird, geht trotzdem zur Tabelle.

Die Regel des linken Präfixes ist nicht strikt
In MySQL nützt ein Index auf (A, B) nichts für WHERE B = ?. Hier kann er nützen: gemessen löste eine Abfrage, die nur nach der zweiten Spalte filterte, mit einem Index Only Scan über den zusammengesetzten Index auf. Das ist weder Magie noch ein Ersatz für den richtigen Index —er wird ganz durchlaufen statt hinabgestiegen—, aber wenn der Index viel kleiner ist als die Tabelle, lohnt es sich weiterhin. Praktische Folge: bevor Sie den „fehlenden" Index anlegen, schauen Sie in den Plan; vielleicht wird längst einer genutzt.

GIN für das, was in einem Feld steckt

CREATE INDEX idx_datos ON eventos USING gin (datos);

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

Ohne den Index ist diese Abfrage ein sequentieller Scan; mit ihm ein Bitmap Index Scan. Der GIN im Test belegte 864 kB bei 200 000 Zeilen. Für jsonb belegt jsonb_path_ops weniger, wenn Sie nur mit @> abfragen: 640 kB gegenüber den 864 des normalen GIN, bei denselben Daten.

Bauen, ohne die Tabelle anzuhalten
Ein gewöhnliches CREATE INDEX nimmt eine SHARE-Sperre: es lässt lesen und hält die Schreibvorgänge an. CREATE INDEX CONCURRENTLY nimmt SHARE UPDATE EXCLUSIVE und hält nichts an — im Tausch gegen zwei Durchläufe der Tabelle und drei Regeln:

CREATE INDEX CONCURRENTLY idx_pedidos_cliente ON pedidos (cliente_id);

-- Ist einer halbfertig liegen geblieben?
SELECT indexrelid::regclass AS indice, indisvalid
  FROM pg_index WHERE NOT indisvalid;

1. Es lässt sich nicht innerhalb einer Transaktion starten — 25001 CREATE INDEX CONCURRENTLY cannot run inside a transaction block.
2. Scheitert es, bleibt ein ungültiger Index zurück: niemand nutzt ihn, aber er wird bei jedem Schreibvorgang gepflegt. Man muss ihn mit der obigen Abfrage finden und löschen.
3. Seit PostgreSQL 12 gibt es REINDEX INDEX CONCURRENTLY — so baut man einen aufgeblähten Index neu, ohne die Tabelle anzuhalten.

Die Indizes, die niemand nutzt

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;

Zwei Vorsichtsmaßnahmen vor dem Löschen. Erstens: der Zähler ist nicht sofort da. Im Test ließen drei Abfragen, die den Index nutzten, ihn im Moment und eine Sekunde später auf 0; erst nach drei Sekunden sagte er 3. Zweitens: er zählt seit dem letzten pg_stat_reset(), und auf einer Replik zählt er die Arbeit der Replik — ein Index, den nur der Monatsbericht nutzt, wirkt an den übrigen 29 Tagen tot.

Empfehlung
Jeder zusätzliche Index wird bei jedem INSERT und bei jedem UPDATE seiner Spalten bezahlt. Die Reihenfolge, die funktioniert: in den Plan schauen, den Index mit CONCURRENTLY anlegen, erneut in den Plan schauen und einen Monat später pg_stat_user_indexes durchsehen. Ein Index, den niemand nutzt, ist nicht neutral: er kostet Schreibleistung, Platz und VACUUM-Zeit.

Stichwörter: Index, btree, brin, gin, gist, spgist, hash, partieller Index, Ausdrucksindex, include, index only scan, heap fetches, concurrently, reindex, pg_stat_user_indexes, indisvalid, jsonb_path_ops, Sichtbarkeitskarte

Einen Plan lesen und Schätzung mit Messung vergleichen, erweiterte Statistiken für einander bedingende Spalten, warum work_mem pro Operation gilt und was die Cache-Trefferquote wirklich misst.

Gilt für: PostgreSQL 13+

Diagnose heißt hier: einen Plan lesen und zwei Zahlen vergleichen. Alles andere —Indizes, Speicher, Statistiken— folgt aus diesem Vergleich.

EXPLAIN führt nicht aus; EXPLAIN ANALYZE schon
Die erste Form fragt nur den Plan ab. Die zweite führt die Abfrage wirklich aus, um sie zu messen, und das schließt ein UPDATE oder ein DELETE ein. Wenn die Anweisung schreibt, klammern Sie sie ein:

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

Die zwei Zahlen, auf die es ankommt
Jeder Knoten trägt eine Schätzung und eine Messung: rows=… ist das, was der Planer glaubte, actual rows=… das, was herauskam. Wenn beide weit auseinanderliegen, ist der schlechte Plan die Folge, nicht die Ursache.

Ein gemessenes Beispiel mit zwei Spalten, die einander bedingen —Stadt und Provinz—:

- Ohne Hilfe schätzte der Planer 11 710 Zeilen, herausgekommen sind 60 000: er multiplizierte beide Wahrscheinlichkeiten, als wären sie unabhängig.
- Mit einer erweiterten Statistik wurde die Schätzung zu 59 610.

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

Das ist das Werkzeug, das MySQL nicht hat, und es behebt die ganze Familie „der Plan ignoriert meinen Index": glaubt der Server, 4 % der Tabelle zu lesen, während er 20 % liest, wählt er aus guten Gründen falsch.

BUFFERS, das man anfordern muss

EXPLAIN (ANALYZE, BUFFERS) SELECT … ;

shared hit sind Blöcke, die im Speicher lagen; shared read die, die geholt werden mussten. Und temp read/written ist der eigentliche Verräter: die Abfrage ist auf die Platte ausgewichen. Außerdem ist track_io_timing standardmäßig aus, die E/A-Zeiten erscheinen also erst, wenn man es einschaltet.

work_mem gilt pro Operation, nicht pro Verbindung
Das ist die Einstellung, die am meisten überrascht und am häufigsten falsch gesetzt wird. Jede Sortierung, jeder Hash-Join und jede Hash-Aggregation darf bis zu work_mem verwenden, und eine Abfrage mit drei solchen Operationen —oder mit zwei parallelen Arbeitern— verwendet ein Vielfaches. Der Werkswert ist 4 MB.

Gemessen an 300 000 Zeilen, dieselbe Abfrage mit ORDER BY:

- Mit work_mem = 64kB: Sort Method: external merge Disk: 15680kB, und der Sortierknoten brauchte ~144 ms.
- Mit work_mem = 64MB: Sort Method: quicksort Memory: 29627kB, und er brauchte ~70 ms.

Die Zahl, die die Wahrheit sagt, ist Sort Method. work_mem global zu erhöhen multipliziert sich über Verbindungen und Operationen; klug ist, es in der Sitzung zu erhöhen, die es braucht:

SET work_mem = '64MB';

Welche Abfrage am meisten kostet: pg_stat_statements
Das Gegenstück zum Protokoll langsamer Abfragen, aber aggregiert: eine Zeile je Abfrageform, mit Aufrufen, Gesamtzeit und Zeilen.
In Calíope lässt sich dasselbe ohne diese Abfrage lesen: das Werkzeug Langsame Abfragen zeigt diese Zusammenfassung, unterscheidet eine fehlende Erweiterung von einer nicht geladenen Bibliothek und schickt jede Zeile in den Editor.

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

Das Kleingedruckte: CREATE EXTENSION genügt nicht. Sie muss in shared_preload_libraries eingetragen werden, und der Server muss neu starten; legt man nur die Erweiterung an, antwortet die erste Abfrage 55000 pg_stat_statements must be loaded via "shared_preload_libraries". Sortieren Sie nach total_exec_time, nicht nach mean_exec_time: die Abfrage, die den Nachmittag frisst, ist meist eine schnelle, die millionenfach läuft.

Die Trefferquote des Caches

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

Das ist die Zahl, die das Dashboard von Calíope als „Datencache" zeigt. Sie misst, wie viele Lesevorgänge ohne Gang zum Dateisystem bedient wurden seit dem letzten Zurücksetzen der Statistiken —nicht, wie viel Speicher belegt ist—, und bei einer kleinen Datenbank fällt sie per Definition sehr hoch aus: im Test 99,85 %. Ein dauerhaft niedriger Wert bedeutet tatsächlich, dass shared_buffers zu klein ist; ein hoher beweist nicht, dass alles in Ordnung ist.

Parallelität
max_parallel_workers_per_gather steht standardmäßig auf 2, und wenn der Planer davon Gebrauch macht, erscheint ein Gather- oder Gather Merge-Knoten. Jeder Arbeiter hat sein eigenes work_mem — die andere Hälfte der obigen Falle.

Empfehlung
Die Reihenfolge, die funktioniert: die Abfrage mit pg_stat_statements finden, sie mit EXPLAIN (ANALYZE, BUFFERS) ansehen, rows mit actual rows vergleichen und erst dann entscheiden, ob ein Index, Statistiken oder Speicher fehlen. An shared_buffers zu drehen, bevor man einen Plan gelesen hat, ist der lange Weg.

Stichwörter: Leistung, explain, analyze, buffers, Plan, Planer, Schätzung, create statistics, erweiterte Statistik, work_mem, sort method, external merge, pg_stat_statements, shared_preload_libraries, pg_stat_database, Cache, track_io_timing, Parallelität

Wo jeder Parameter steht und welcher gewinnt, was einen Neustart verlangt, warum effective_cache_size keinen Speicher reserviert und warum man max_connections nicht erhöht.

Gilt für: PostgreSQL 13+

PostgreSQL hat 378 Parameter —auf diesem Server gezählt—, und die gute Nachricht ist: man fasst eine Handvoll an. Zuerst zu lernen ist nicht, welche, sondern wo sie geschrieben werden und wann sie greifen.

Vier Orte, und der Server sagt, welcher gewonnen hat
- postgresql.conf — die gewohnte Datei, von Hand bearbeitet.
- postgresql.auto.conf — von ALTER SYSTEM geschrieben und nicht von Hand zu bearbeiten; das sagt sie selbst in ihrer ersten Zeile.
- Pro Datenbank oder pro Rolle — ALTER DATABASE … SET, ALTER ROLE … SET.
- Pro Sitzung — SET, gültig, solange die Verbindung besteht.

Wer gewonnen hat, muss man nicht raten: die Spalte source von pg_settings sagt es. Gemessen: nach ALTER DATABASE demo SET work_mem = '32MB' las eine neue Verbindung 32MB mit source = database; nach dem RESET 4MB mit 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');

Drei Klassen von Parametern, und die schmerzhafte
Die Spalte context sagt, was zum Ändern nötig ist:
- user / superuser — ein SET in der Sitzung genügt (work_mem, effective_cache_size).
- sighup — ein Neuladen ist nötig (checkpoint_timeout, max_wal_size, fast das ganze Autovacuum).
- postmaster — der Server muss neu starten. Auf diesem Server sind das 65 von 378, darunter shared_buffers, max_connections, wal_level, shared_preload_libraries und autovacuum_max_workers.

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

-- Wartet etwas auf einen Neustart?
SELECT name, setting FROM pg_settings WHERE pending_restart;

Ein gemessenes Detail, das einen Schreck erspart: pending_restart leuchtet nicht im selben Augenblick des Neuladens auf. Unmittelbar danach stand es noch auf false, eine halbe Sekunde später auf true. Man fragt es später ab, nicht im selben Atemzug.

shared_buffers und effective_cache_size sind nicht dasselbe, und eines von beiden reserviert nichts
- shared_buffers ist echter Speicher: der eigene Cache des Servers. Ab Werk 128 MB, wenig für einen dedizierten Server; die übliche Regel sind 25 % des RAM.
- effective_cache_size reserviert nichts. Es ist das, was der Planer zwischen PostgreSQLs Cache und dem des Betriebssystems vermutet, und dient nur der Entscheidung, ob sich ein Index lohnt. Es zu ändern bewegt kein Byte: es ändert Pläne.

Die beiden zu verwechseln führt dazu, effective_cache_size zu erhöhen in der Hoffnung auf mehr Cache — oder shared_buffers in der Hoffnung auf einen anderen Plan.

max_connections erhöht man nicht: man stellt einen Pool davor
Ab Werk 100, und hier ist jede Verbindung ein Prozess des Betriebssystems, kein Thread. Auf tausend zu gehen ist keine größere Zahl: es sind tausend Prozesse, mit ihrem Speicher und ihrem work_mem pro Operation. Die Antwort ist ein Verbindungspooler (pgBouncer und ähnliche). Und er gehört zu denen, die einen Neustart verlangen.

WAL und Prüfpunkte
max_wal_size (ab Werk 1 GB) und checkpoint_timeout (5 min) entscheiden, wie oft alles auf die Platte geschrieben wird. Lösen die Prüfpunkte nach Größe statt nach Zeit aus, schreibt der Server stoßweise; man sieht es, indem man log_checkpoints einschaltet, und behebt es, indem man max_wal_size erhöht. checkpoint_completion_target steht bereits auf 0,9, was dieses Schreiben über die Zeit verteilt, statt es zu bündeln.

Autovacuum
Hier gelten autovacuum_naptime 60 s, autovacuum_max_workers 3 —dieser verlangt einen Neustart— und autovacuum_vacuum_scale_factor 0,2: eine Tabelle wird also aufgeräumt, wenn 20 % ihrer Zeilen sich geändert haben. Bei einer Tabelle mit einer Milliarde Zeilen heißt das, auf zweihundert Millionen tote Versionen zu warten; große Tabellen bekommen deshalb ihre eigene Einstellung:

ALTER TABLE eventos SET (autovacuum_vacuum_scale_factor = 0.01);

synchronous_commit, der Einzige, der das Versprechen ändert
Ihn auszuschalten bedeutet, dass COMMIT nicht wartet, bis das WAL die Platte erreicht: man gewinnt Latenz und riskiert bei einem Stromausfall die letzten Transaktionen —nicht die Integrität der Datenbank, nur die zuletzt bestätigten—. Er ist user, kann also nur dort ausgeschaltet werden, wo dieser Handel akzeptiert wird:

SET synchronous_commit = off;

Empfehlung
Wenige anfassen, einzeln und messend. ALTER SYSTEM statt Dateien zu bearbeiten —es wird festgehalten und mit ALTER SYSTEM RESET zurückgenommen—, und pro Datenbank oder Rolle vor global: eine Einstellung, die nur der Nachtbericht braucht, muss nicht den ganzen Tag bezahlt werden.

Stichwörter: Konfiguration, 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

Warum das Konto den Host nicht enthält, Rollen als Benutzer und Gruppe zugleich, die Falle, dass Gewähren die Tabellen von morgen nicht erreicht, und warum Zeilensicherheit eingeschaltet und wirkungslos sein kann.

Gilt für: PostgreSQL 13+

Der grundlegende Unterschied zu MySQL ist, dass hier das Konto den Host nicht enthält. Es gibt kein ana@192.168.1.%: es gibt die Rolle ana, und von wo sie sich verbinden darf und wie sie sich ausweist, entscheidet eine eigene Datei, pg_hba.conf.

pg_hba.conf: die erste passende Zeile gewinnt
Sie wird von oben nach unten gelesen und dort endet die Suche. Und man muss sie nicht öffnen, um sie zu sehen:

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

Die Spalte error sagt, ob eine Zeile fehlerhaft ist —das erspart den Neustart, bei dem der Server nicht wiederkommt—. Änderungen greifen mit SELECT pg_reload_conf(), ohne Neustart. Auf dem Testserver gab es sieben Regeln, die für 127.0.0.1 mit trust und die letzte, für alles Übrige, mit scram-sha-256: die Reihenfolge ist die Richtlinie.

Eine Rolle ist Benutzer und Gruppe zugleich
Es gibt nicht zwei Begriffe: CREATE USER ist genau CREATE ROLE … LOGIN. Was eine Person von einer Gruppe unterscheidet, ist das Attribut LOGIN — sonst nichts.

CREATE ROLE app_ro;                       -- ohne LOGIN: dient als Gruppe
CREATE ROLE ana LOGIN PASSWORD 'secreta';
GRANT app_ro TO ana;                      -- ana erbt die Rechte von app_ro

Rollen erben standardmäßig, ana nutzt die Privilegien von app_ro also ohne Zutun. Mit NOINHERIT müssen sie per SET ROLE angefordert werden — das nimmt man, wenn der Schritt ausdrücklich sein soll.

Passwörter werden mit scram-sha-256 gespeichert, dem Standard seit PostgreSQL 14 —der Testserver bestätigt es—; md5 existiert noch und sollte nicht mehr verwendet werden.

Die eigentliche Falle: Gewähren reicht nicht in die Zukunft
GRANT … ON ALL TABLES IN SCHEMA gewährt auf den heute vorhandenen Tabellen. Gemessen: nach der Gewährung konnte app_ro die bestehende Tabelle lesen und nicht die eine Minute später angelegte. Was die Zukunft abdeckt, ist eine andere Anweisung:

GRANT USAGE ON SCHEMA public TO app_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_ro;          -- die von heute
ALTER DEFAULT PRIVILEGES IN SCHEMA public
      GRANT SELECT ON TABLES TO app_ro;                          -- die von morgen

Und im Kleingedruckten: Standardprivilegien gehören dem, der sie erklärt, nicht dem Schema; werden die Tabellen von einer anderen Rolle angelegt, müssen sie auch mit FOR ROLE erklärt werden. Was erklärt wurde, steht in pg_default_acl.

Gewähren hinterlässt außerdem eine Spur, die rückgängig gemacht werden muss: nach einem ON ALL TABLES scheitert das Löschen der Rolle mit DependentObjectsStillExist und der Liste der Tabellen, auf denen ein Privileg blieb —im Test tauchten sogar die von PostGIS auf—. Das Gegenstück ist REVOKE oder DROP OWNED BY rolle vor dem DROP ROLE.

Das Schema public gehört nicht mehr allen
Seit PostgreSQL 15 behält PUBLIC USAGE auf dem Schema public, hat aber kein CREATE mehr. Gemessen auf 17.6: eine frisch angelegte Rolle liefert USAGE = true und CREATE = false. Wer Skripte aus einer älteren Version mitbringt, sieht das erste CREATE TABLE eines Benutzers scheitern, der es früher durfte.

Vordefinierte Rollen: überwachen ohne Superuser
Der Server bringt fünfzehn fertige Rollen mit. Die, die einen zusätzlichen Superuser ersparen:
- pg_read_all_data, pg_write_all_data — alles lesen oder schreiben, ohne weitere Machtbefugnisse.
- pg_monitor — die vollständigen Statistiksichten sehen; enthält pg_read_all_stats und pg_read_all_settings.
- pg_signal_backend — Abfragen abbrechen und fremde Sitzungen schließen.
- pg_maintain (PostgreSQL 16+) — VACUUM, ANALYZE, REINDEX, ohne Eigentümer zu sein.

Sicherheit auf Zeilenebene
Eine Richtlinie filtert, welche Zeilen jede Rolle innerhalb derselben Tabelle sieht:

ALTER TABLE pedidos ENABLE ROW LEVEL SECURITY;

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

Und hier ist, was man messen muss, bevor man sich darauf verlässt. Mit derselben Richtlinie und derselben Tabelle:

- Die Rolle, auf die die Richtlinie zielt, sah eine Zeile. Richtig.
- Der Eigentümer der Tabelle —kein Superuser— sah beide: der Eigentümer unterliegt seinen eigenen Richtlinien erst, wenn ALTER TABLE … FORCE ROW LEVEL SECURITY erklärt ist. Mit FORCE sah er eine.
- Der Superuser sah beide, sogar mit FORCE. Superuser und Rollen mit BYPASSRLS umgehen Richtlinien immer.

Anders gesagt: eine Anwendung, die sich als Eigentümer der Tabellen verbindet —von einem Superuser ganz zu schweigen—, hat die Zeilensicherheit eingeschaltet und wirkungslos. Die Prüfung besteht darin, sich mit der echten Rolle zu verbinden und Zeilen zu zählen.

Empfehlung
Eine Rolle je Anwendung, kein LOGIN für Gruppen und keine mit SUPERUSER außer der Verwaltungsrolle. ALTER DEFAULT PRIVILEGES im selben Commit wie das GRANT, sonst hält die Berechtigung bis zur nächsten Tabelle. Und was pauschal gewährt wird, notieren: das DROP ROLE in einem Jahr wird danach fragen.

Stichwörter: Sicherheit, Rolle, Benutzer, Gruppe, 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

Logisch gegen physisch und wofür jede taugt, was pg_dump auslässt und niemanden mehr hineinlässt, warum PITR nicht ab Werk funktioniert und welcher Slot die Platte füllen kann.

Gilt für: PostgreSQL 13+

Es gibt zwei Arten von Sicherung, und sie taugen nicht für dasselbe. Die falsche Wahl entdeckt man am Tag der Wiederherstellung.

Logische Sicherung

-- auf der Kommandozeile, nicht im SQL-Editor:
-- pg_dump -d demo -Fc -f demo.dump
-- pg_restore -d demo_nueva -j 4 demo.dump

Das Format -Fc (custom) ist die richtige Voreinstellung: bei derselben Datenbank belegte der Textauszug 3,1 MB und der custom-Auszug 905 kB, und er bringt zudem ein Inhaltsverzeichnis mit —pg_restore -l listete die dreißig Datenblöcke—, sodass sich eine Tabelle wiederherstellen lässt, und das parallel mit -j.

Was pg_dump nicht mitnimmt, und genau das beißt
Rollen und clusterweite Einstellungen sind nicht darin. Gemessen: der Datenbankauszug enthielt kein einziges CREATE ROLE, während pg_dumpall --globals-only die beiden vorhandenen erzeugte. Nur den Auszug zurückzuspielen hinterlässt eine perfekte Datenbank, in die niemand hineinkommt. Eine vollständige logische Sicherung sind zwei Dateien:
- pg_dumpall --globals-only — Rollen, Passwörter und Rechte des Clusters.
- pg_dump jeder Datenbank.

pg_dump ist konsistent —es arbeitet auf einem Snapshot— und blockiert keine Schreiber; aber es nimmt eine ACCESS SHARE-Sperre, sodass ein gleichzeitig gestartetes ALTER TABLE zu warten beginnt und sich alles dahinter staut.

Physische Sicherung
pg_basebackup kopiert das gesamte Cluster. Auf dem Testserver gemessen: 84 MB in 1,3 s, mit -X stream, das auch das während der Kopie erzeugte WAL mitbringt —ohne das ist die Kopie nicht wiederherstellbar—. Es hinterlässt ein backup_label, das sagt, ab welchem Punkt im WAL nachgespielt werden muss:

START WAL LOCATION: 0/13000028 (file 000000010000000000000013)

PITR: bis zu einem Zeitpunkt wiederherstellen
Das ist der Daseinszweck der physischen Sicherung, und es funktioniert nicht ab Werk: archive_mode kommt ausgeschaltet, auf diesem Server gemessen. Ohne Archivierung stellt eine physische Sicherung genau den Moment wieder her, in dem sie genommen wurde, und keine Sekunde mehr.

Drei Teile sind nötig:
1. archive_mode = on und ein archive_command, das jedes WAL-Segment an einen sicheren Ort kopiert (oder pg_receivewal von einer anderen Maschine).
2. Ein regelmäßiges pg_basebackup.
3. Beim Wiederherstellen: die Dateien der Sicherung, ein restore_command, das die Segmente holt, recovery_target_time = '…' und eine leere Datei recovery.signal im Datenverzeichnis.

Der letzte Punkt verwirrt alle, die von alten Versionen kommen: seit PostgreSQL 12 gibt es keine recovery.conf mehr; die Parameter stehen in postgresql.conf, und was „dies ist eine Wiederherstellung" erklärt, ist die Signaldatei.

Replikations-Slots sind ein zweischneidiges Messer
Ein Slot garantiert, dass der Server WAL nicht löscht, das ein Verbraucher nicht gelesen hat. Verschwindet der Verbraucher und bleibt der Slot, staut sich WAL, bis die Platte voll ist —und eine volle Platte ist ein Ausfall, keine Warnung—. So überwacht man sie:

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 setzt die Obergrenze: darüber hinaus entwertet der Server lieber den Slot, als ohne Platte dazustehen.

Die Sicherung von Calíope ist logisch
Was das Sicherungswerkzeug erzeugt, ist SQL —CREATE und INSERT—, aus der Familie von pg_dump, nicht von pg_basebackup. Es taugt zum Migrieren und zum Zurückholen von Daten; einen ganzen Server auf einen Zeitpunkt zurückzuholen verlangt das Obige, und das ist Sache des Betriebssystems, nicht eines Clients.

Empfehlung
Die zwei Dateien der logischen Sicherung immer zusammen —--globals-only und der Auszug— und die Wiederherstellung testen, nicht die Sicherung: eine fehlerfrei erzeugte Datei kann sich nicht zurückspielen lassen, und das weiß man erst, wenn man sie auf einem echten Server zurückspielt.

Stichwörter: Sicherung, backup, pg_dump, pg_restore, pg_dumpall, globals, pg_basebackup, wal, archive_mode, archive_command, pitr, recovery_target_time, recovery.signal, Replikations-Slot, wal_status, Wiederherstellung

DDL ist transaktional, teuer ist die Sperre und nicht das ALTER, welche Änderungen die ganze Tabelle neu schreiben, und das Muster NOT VALID + VALIDATE, das die Datenbank nicht anhält.

Gilt für: PostgreSQL 13+

Hier ist DDL transaktional. Das ändert, wie Migrationen geschrieben werden, und ist das Erste, was man aus MySQL kommend verinnerlichen muss, wo jedes ALTER für sich bestätigt.

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

Geprüft: nach diesem ROLLBACK blieb keine der drei Sachen übrig. Eine Migration, die auf halbem Weg scheitert, hinterlässt keine halbe Datenbank; deshalb ist das gesunde Muster, die ganze Migration in eine Transaktion zu packen.

Die Ausnahmen sind an einer Hand abzuzählen: CREATE INDEX CONCURRENTLY, VACUUM und ALTER SYSTEM dürfen nicht in einer Transaktion stehen.

Teuer ist nicht das ALTER, sondern die Sperre
Fast jedes ALTER TABLE nimmt ACCESS EXCLUSIVE, das sogar mit einem SELECT kollidiert. Selbst wenn die Änderung eine Millisekunde dauert, kann das Warten auf die Sperre Stunden dauern —und während es wartet, staut sich alles dahinter—. Deshalb wird DDL in der Produktion immer so abgesetzt:

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

Bekommt es die Sperre nicht, scheitert es nach drei Sekunden und wird wiederholt. Ohne das kann eine Millisekunden-Migration die ganze Anwendung anhalten.

Was die Tabelle neu schreibt und was nicht
Neu schreiben heißt, die ganze Tabelle zu kopieren: es dauert mit der Größe und braucht währenddessen doppelt so viel Platte. Gemessen an 500 000 Zeilen und 32 MB:

Die Regel, die die Tabelle zusammenfasst: Verbreitern ist gratis, Verengen schreibt neu. Und ADD COLUMN mit Vorgabewert schreibt seit PostgreSQL 11 nicht mehr neu: der alte Umweg, die Spalte leer anzulegen und stapelweise zu füllen, ist überflüssig.

Zwei Warnungen, die die Zeiten nicht zeigen:
- Ein DROP COLUMN ist sofort fertig, weil es die Spalte nur als gelöscht markiert: der Platz kommt nicht zurück, bis die Tabelle neu geschrieben wird.
- SET NOT NULL und ein gewöhnliches CHECK schreiben nicht neu, durchlaufen aber die ganze Tabelle mit gehaltener Sperre. Bei einer großen Tabelle ist das bereits ein Ausfall.

Das Muster, das die Datenbank nicht anhält: NOT VALID, dann VALIDATE
Eine Einschränkung lässt sich in zwei Schritten hinzufügen: zuerst wird sie erklärt, ohne den Bestand zu prüfen —sofort—, und dann validiert; das ist der langsame Teil, aber mit einer weit schwächeren Sperre.

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;

Gemessen: die NOT VALID-Erklärung brauchte 0,7 ms mit SHARE ROW EXCLUSIVE —das Lesen erlaubt—, und die Validierung 67 ms mit SHARE UPDATE EXCLUSIVE, das nicht einmal Schreiber blockiert. In einem Zug kostete es gleich viel Zeit, aber mit durchgehend gehaltener starker Sperre. Bei einer echten Tabelle trennt dieser Unterschied ein Deployment von einem Ausfall.

Sobald die Einschränkung NOT VALID ist, erzwingt der Server sie für neue Zeilen; offen bleibt nur die Prüfung der alten.

Indizes
Ein gewöhnliches CREATE INDEX hält die Schreibvorgänge an; CREATE INDEX CONCURRENTLY hält nichts an, passt aber nicht in die Transaktion der Migration und geht deshalb in einen eigenen Schritt, der danach geprüft wird (pg_index.indisvalid).

Empfehlung
Immer lock_timeout; die Migration in einer Transaktion, außer dem, was nicht darf; NOT VALID + VALIDATE für Einschränkungen auf großen Tabellen; und Vorsicht bei Typänderungen, wo sich das Neuschreiben versteckt. Muss ein Typ verengt werden, ist es fast immer besser, die neue Spalte anzulegen, stapelweise zu kopieren und umzubenennen.

Stichwörter: ddl, alter table, Migration, transaktional, rollback, lock_timeout, access exclusive, Neuschreiben, relfilenode, add column, drop column, set not null, not valid, validate constraint, create index concurrently

Was es nicht gibt und einen Syntaxfehler wirft, warum text nicht schlechter ist als varchar, numeric gegen Gleitkomma, was timestamptz wirklich speichert und warum jsonb keinen Platz spart.

Gilt für: PostgreSQL 13+

Typen sind eine der wenigen Stellen, an denen die Migration von MySQL schon beim ersten Versuch scheitert — und das ist gut so: was es nicht gibt, wirft einen Syntaxfehler, statt halb angenommen zu werden.

Was es hier nicht gibt
- UNSIGNED — 42601 syntax error at or near "unsigned". Es gibt keine vorzeichenlosen Ganzzahlen; man nimmt den nächstgrößeren Typ oder ein CHECK (n >= 0).
- INT(11) — ebenfalls 42601. MySQLs Anzeigebreite existiert nicht und bedeutete nie das, wonach sie aussah.
- TINYINT, DATETIME, DOUBLE mit Klammern und MySQLs SET/ENUM. Die Entsprechungen sind smallint, timestamptz, double precision und ein echter enum-Typ.

Text: nehmen Sie text, fertig
Gemessen, mit demselben Wert 'hola': text belegte 5 Byte, varchar(50) 5 und char(50) 51. Alle drei werden gleich gespeichert; varchar(n) fügt nur eine Längenprüfung hinzu und char(n) füllt mit Leerzeichen auf. Und diese Leerzeichen verändern Vergleiche: 'x' = 'x ' ist bei text falsch und bei char wahr.

Hier ist text nicht schlechter als varchar: es gibt keine Strafe. varchar(n) nimmt man, wenn die Grenze eine fachliche Regel ist, und char(n) praktisch nie.

Zahlen: numeric für Geld, und das ist kein Aberglaube

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

Gemessen: in Gleitkomma ergab 0.1 * 3 0.30000000000000004; in numeric genau 0.3. numeric ist exakt und beliebig genau, und man bezahlt das mit Platz und Tempo —10 Byte gegenüber den 8 von float8 für diesen Wert, und Arithmetik in Software—. Für Geld und für jede Zahl, die vor einem Kunden addiert wird: numeric.

Gemessene Größen: int 4, bigint 8, boolean 1, uuid 16 —gegenüber den 36 als Text—.

Datumsangaben: fast immer timestamptz
timestamp und timestamptz belegen dieselben 8 Byte. Der Unterschied ist weder die Größe noch, dass eines die Zone speichert: keines speichert die Zone. timestamptz speichert einen Zeitpunkt —beim Hineinschreiben nach UTC, beim Herauslesen in die Zone der Sitzung—, timestamp speichert eine Uhrzeitablesung und sonst nichts.

Gemessen, derselbe Zeitpunkt mit zwei Sitzungszonen:

Es ist derselbe Moment, zweimal ausgesprochen. Bei timestamp findet gar keine Umrechnung statt: was hineinging, kommt heraus — und wer wissen muss, wann es wirklich war, kann es nicht mehr herausfinden. date belegt 4 Byte, interval 16.

json gegen jsonb: fast immer jsonb, und nicht wegen der Größe

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 speichert den Text, wie er kam: Reihenfolge, Leerzeichen und sogar doppelte Schlüssel bleiben erhalten. jsonb speichert einen bereits geparsten Baum: es sortiert die Schlüssel, behält den letzten doppelten und normalisiert Leerzeichen. Deshalb lässt sich jsonb schnell abfragen und mit GIN indizieren, und json ist nur richtig, wenn das Dokument Byte für Byte so zurückgegeben werden muss, wie es ankam.

Was nicht stimmt, ist, dass jsonb Platz spart: gemessen an 200 000 gleichen Dokumenten belegte json 14 MB und jsonb 16 MB. Man wählt jsonb dafür, wie es abgefragt wird, nicht dafür, was es wiegt.

Arrays
Ein Array ist ein vollwertiger Typ, mit seinen Operatoren —@> für Enthaltensein, array_length— und seinem GIN-Index. Bequem für Etiketten und kurze Listen; unbequem, sobald die Elemente eigene Attribute brauchen oder mit einer anderen Tabelle verbunden werden müssen. Ein Array ist keine gesparte Tabelle: es ist ein Wert.

serial oder IDENTITY
serial ist kein Typ: es ist Zucker, der eine Sequenz anlegt und deren nextval als Vorgabewert setzt. GENERATED ALWAYS AS IDENTITY ist die Standardform und schützt zusätzlich die Spalte: der Versuch, von Hand einen Wert einzufügen, antwortete 428C9 cannot insert a non-DEFAULT value into column. Für neue Tabellen: IDENTITY.

Empfehlung
text für Text, numeric für Geld, timestamptz für Zeitpunkte, jsonb für Dokumente, die abgefragt werden, und IDENTITY für Schlüssel. Und bei der Migration von MySQL den Syntaxfehler seine Arbeit tun lassen: besser als ein Typ, der angenommen wird und etwas anderes bedeutet.

Stichwörter: Typen, text, varchar, char, numeric, float, decimal, timestamptz, timestamp, Zeitzone, json, jsonb, Array, uuid, serial, identity, unsigned, enum

Die Codes, die man täglich sieht, warum man gegen die Klasse und nicht gegen den Code programmiert, was DETAIL und HINT sagen, die kaum jemand zeigt, und wo der Code steht, wenn die Verbindung gar nicht erst zustande kommt.

Gilt für: PostgreSQL 13+

Hier gibt es keine Fehlernummern. Es gibt SQLSTATE: fünf Zeichen, von denen die ersten beiden die Klasse sind. Und die Klasse ist das, wogegen man programmiert: sie sagt, was zu tun ist, ohne dass man genau weiß, was fehlschlug.

Die, die man täglich sieht

Die Klassen, auf die man schauen sollte

Die praktische Folge: eine Anwendung wiederholt Klasse 40 und wiederholt Klasse 42 nicht. Unterscheidet die Wiederholung nicht, geht entweder eine berechtigte Transaktion verloren oder eine Abfrage, die nie funktionieren wird, läuft tausendmal.

Die Meldung hat drei Teile, und der dritte ist der nützliche
MESSAGE sagt, was geschah, DETAIL nennt Zeile oder Wert, und HINT sagt, was zu tun ist. Gemessen:

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

Ein Client, der nur die MESSAGE zeigt, wirft die Hälfte der Information weg —und zwar genau die Hälfte, die den Ausweg nennt—. Calíope setzt alle drei zusammen.

Außerdem bringt der Fehler eigene Felder mit: Tabelle, Spalte und Name der Bedingung. Mit 23505 kam constraint = er_d_pkey — das ist es, was daraus ein „diese E-Mail ist bereits registriert" macht, ohne den Meldungstext zu zerlegen.

Ein Fehler bricht die Transaktion ab
Nach jedem Fehler innerhalb eines BEGIN antwortet alles Folgende 25P02, bis ein ROLLBACK kommt. Das ist kein Client-Fehler: das ist die Bauart, und der elegante Ausweg sind Sicherungspunkte.

Verbindungsfehler kommen nicht in der Antwort
Existieren Rolle oder Datenbank nicht, oder ist das Passwort falsch, öffnet sich die Verbindung gar nicht erst: der Client sieht nur „connection failed". Der Code steht im Serverprotokoll, und nur, wenn man ihn anfordert:

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

Damit wurde aus FATAL: database "no_existe" does not exist ein FATAL: 3D000: database "no_existe" does not exist —und 28000 für die nicht existierende Rolle—. Das ist der Unterschied zwischen Raten und Wissen, wenn jemand meldet, er „komme nicht rein".

Empfehlung
Im Anwendungscode nach Klasse verzweigen und den vollständigen Code nur für die Meldungen verwenden, die der Benutzer sieht (23505 → „existiert bereits"). Den SQLSTATE immer im eigenen Protokoll festhalten: der Meldungstext ändert sich mit der Sprache des Servers, der Code nicht.

Stichwörter: Fehler, sqlstate, Code, Klasse, 23505, 23503, 42p01, 42601, 42883, 42501, 25p02, 40001, 40p01, 55p03, 57014, 3d000, 28000, detail, hint, Wiederholung, log_error_verbosity

Deklarative Partitionierung und was der Planer wirklich beschneidet, warum es keine globalen Indizes und keine Eindeutigkeit auf einer einzelnen Spalte gibt, der CHECK, der aus 68 ms ATTACH eine halbe Millisekunde macht, und was die Standardpartition kostet.

Gilt für: PostgreSQL 13+

Die Partitionierung ist hier deklarativ: man erklärt den Schlüssel, und jede Partition ist eine echte Tabelle. Die Elterntabelle hält keine einzige Zeile —gemessen: 0 Byte, die Daten liegen in den Kindern—.

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;

Es gibt drei Formen: RANGE (Datumsangaben, Beträge), LIST (Land, Status) und HASH (Verteilen um des Verteilens willen).

Das Beschneiden ist der Zweck der Übung
Gemessen an 300 000 Zeilen über drei Jahre: eine Abfrage mit WHERE creado BETWEEN '2024-03-01' AND '2024-03-31' durchlief nur pt_2024. Dieselbe Tabelle, gefiltert nach einer Spalte, die nicht der Schlüssel ist, machte ein Parallel Append über alle.

Daraus folgt die Regel, die den Entwurf entscheidet: der Partitionsschlüssel ist die Spalte, nach der Sie fast immer filtern. Erwähnen die Abfragen sie nicht, spart die Partitionierung kein Lesen: sie verteilt es.

Was es nicht gibt: globale Indizes
Ein auf der Elterntabelle angelegter Index erzeugt einen je Partition —gemessen: vier Partitionen, vier Indizes—. Es gibt keinen einzelnen Index über die ganze Tabelle, und daraus folgt die Einschränkung, die man vor dem Entwurf kennen muss:

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

Ein UNIQUE nur auf (id) wird mit 0A000 unique constraint on partitioned table must include all partitioning columns abgelehnt. Globale Eindeutigkeit einer Kennung lässt sich mit deklarativer Partitionierung nicht garantieren; sie kommt von einer Sequenz, die durch Konstruktion eindeutig ist, nicht durch eine Bedingung.

ATTACH: der Unterschied zwischen 68 ms und einer halben
Eine bestehende Tabelle anzuhängen zwingt den Server zu prüfen, ob alle ihre Zeilen in den Bereich passen. Gemessen an 300 000 Zeilen:

Anders gesagt: trägt die Tabelle bereits eine CHECK-Bedingung, die den Bereich impliziert, überspringt der Server den Durchlauf. Bei einer Tabelle mit einer Milliarde Zeilen ist das der Unterschied zwischen einem augenblicklichen ACCESS EXCLUSIVE und einer halben Stunde davon.

Die Standardpartition ist nicht gratis
DEFAULT fängt auf, was in keinen Bereich fällt, und verhindert den Fehler beim Einfügen eines unerwarteten Datums. Im Gegenzug, gemessen: ALTER TABLE … DETACH PARTITION … CONCURRENTLY antwortete 55000 cannot detach partitions concurrently when a default partition exists. Und außerdem muss jedes neue ATTACH die Standardpartition durchlaufen, um zu prüfen, dass sie keine Zeilen des ankommenden Bereichs verbirgt.

Warum man wirklich partitioniert
Nicht wegen der Abfragegeschwindigkeit —dafür sind Indizes da—, sondern wegen der Wartung:
- Eine ganze Periode zu entfernen ist ein DROP TABLE ihrer Partition: gemessen 2,3 ms, und der Platz geht ans Dateisystem zurück. Das gleichwertige DELETE dauerte länger und hinterlässt vor allem tote Zeilen, die VACUUM aufräumen muss, und Platz, der nicht zurückkommt.
- VACUUM und ANALYZE arbeiten je Partition, die Wartungsarbeit wächst also nicht mehr mit der ganzen Historie.
- Alte Daten lassen sich abhängen und archivieren, ohne die lebende Tabelle anzufassen.

Empfehlung
Partitionieren Sie nach dem, was Sie löschen werden, nicht nach dem, was Sie abfragen werden; und prüfen Sie am Plan, dass Ihre Abfragen den Schlüssel im WHERE führen, statt es anzunehmen. Bevor Sie eine bestehende Tabelle partitionieren, fragen Sie sich, ob nicht ein Index das Fehlende ist: Partitionierung fügt bewegliche Teile hinzu und gibt dafür billigere Wartung — und diese Rechnung geht erst ab einer gewissen Größe auf.

Stichwörter: Partitionierung, partition, range, list, hash, pruning, attach, detach, default, globaler Index, unique, 0a000, drop partition, Wartung

Warum es die utf8-Falle hier nicht gibt, worin sich Kodierung und Sortierfolge unterscheiden, wie sich die Reihenfolge mit jeder ändert, und warum ein Präfix-LIKE Ihren Index nicht nutzt.

Gilt für: PostgreSQL 13+

Die Falle, die so viele MySQL-Migrationen gekostet hat, gibt es hier nicht: es gibt kein utf8, das kein UTF-8 war. Die Kodierung wird beim Anlegen der Datenbank erklärt, und UTF8 ist das ganze UTF-8.

Gemessen, mit vier Zeichenketten in einer gewöhnlichen text-Spalte:

Nichts Besonderes war zu erklären. length() zählt Zeichen und octet_length() zählt Bytes — genau die Unterscheidung, der man in MySQL Typ für Typ nachjagen musste.

Kodierung und Sortierfolge sind zweierlei
- Kodierung — wie die Bytes gespeichert werden. Sie gehört zur Datenbank, wird beim Anlegen festgelegt und danach nicht mehr geändert: dafür muss man auszugsweise sichern und neu anlegen.
- Sortierfolge (Collation) — wie sortiert und verglichen wird. Sie lässt sich pro Datenbank, pro Spalte, pro Ausdruck und sogar im ORDER BY festlegen.

Auf dem Testserver: server_encoding = UTF8, und die Datenbanken mit en_US.utf8 vom Anbieter libc. Es stehen 815 Sortierfolgen zur Verfügung, von zwei Anbietern: die des Systems (C, POSIX, en_US.utf8) und die von ICU (es-ES-x-icu, unicode), die seit PostgreSQL 15 sogar der Standardanbieter einer Datenbank sein können.

Was sich mit der Sortierfolge ändert

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

Gemessen ähneln sich die Ergebnisse nicht:

- C — A, B, a, b, á. Sortiert nach der Zeichennummer: alle Großbuchstaben vor den Kleinbuchstaben, Akzente ganz hinten.
- es-ES-x-icu — a, A, á, b, B. Sortiert wie ein Wörterbuch.

Und der Vergleich ändert sich mit: 'a' < 'B' ist mit C falsch und mit der spanischen Sortierfolge wahr. Eine Liste, die „falsch sortiert herauskommt", ist fast nie ein Anwendungsfehler: es ist die Sortierfolge der Spalte.

Die Sortierfolge entscheidet, ob ein Index für LIKE taugt
Das ist das praktische Detail, auf das man am schwersten allein kommt. Mit einer sprachlichen Sortierfolge —der der Datenbank— taugt ein gewöhnlicher B-Baum-Index nicht für Präfixsuchen. Gemessen an 200 000 Zeilen:

- WHERE s = 'usuario42' → Index Only Scan.
- WHERE s LIKE 'usuario42%' → Seq Scan, obwohl der Index danebenliegt.
- Nach Anlegen des Index mit der passenden Operatorklasse wurde dieselbe Abfrage zum Bitmap Index Scan.

CREATE INDEX idx_prefijo ON ch_like (s text_pattern_ops);

text_pattern_ops vergleicht Byte für Byte, genau das, was LIKE 'etwas%' braucht. Mit der Sortierfolge C auf der Spalte ist es unnötig, denn so wird ohnehin verglichen.

Sortierfolgen haben eine Version, und das ist wichtig
Die Datenbank hält fest, mit welcher Version der Sortierfolge ihre Indizes gebaut wurden —gemessen: datcollversion = 2.36, die der Systembibliothek—. Wird das Betriebssystem aktualisiert und ändert sich diese Version, kann sich die Reihenfolge ändern, und ein mit der alten Reihenfolge gebauter Index ist nicht mehr korrekt: Suchen finden Zeilen nicht, die da sind. PostgreSQL warnt vor der Abweichung, und die Antwort heißt REINDEX.

Deshalb wählen viele die Sortierfolge C oder ICU für Datenbanken, die Systemaktualisierungen überstehen müssen: ICU bringt seine eigene Version mit und hängt nicht an der des Systems.

Empfehlung
Immer UTF8. Die Sortierfolge wird beim Anlegen der Datenbank entschieden, weil eine spätere Änderung teuer ist: C für Spalten, die Codes, Kennungen oder Pfade sind —sie sortiert schnell und verträgt sich mit LIKE—, und eine sprachliche Sortierfolge für das, was ein Mensch liest. Und wenn eine LIKE 'x%'-Abfrage den Index nicht nutzt, schauen Sie auf die Sortierfolge, bevor Sie die Abfrage anfassen.

Stichwörter: Kodierung, encoding, utf8, Sortierfolge, collation, collate, icu, libc, text_pattern_ops, like, Präfix, order by, datcollversion, reindex, octet_length

Die, die wirklich beißen —63 Byte Name, 1 600 Spalten, 32 je Index—, warum die der Kennung einen Hinweis und keinen Fehler wirft, und was TOAST mit einem Wert macht, der nicht in die Seite passt.

Gilt für: PostgreSQL 13+

Die Grenzen von PostgreSQL ähneln denen von InnoDB nicht, und die, die täglich beißen, sind nicht die großen.

Die, an die man wirklich stößt

Die ersten drei sind gemessen: 1 600 Spalten ließen sich problemlos anlegen, 1 601 scheiterten; ein Index mit 32 Spalten entstand, der mit 33 nicht.

Die der Kennung ist die einzige, die keinen Fehler wirft
Ein Name mit 72 Zeichen wurde als 63-Zeichen-Name gespeichert, und der Server sagte es per Hinweis:

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

Ein Hinweis ist kein Fehler: die Anweisung lief weiter. Deshalb werden zwei lange Namen, die sich erst ab dem 64. Zeichen unterscheiden, am Ende dasselbe Objekt, und der Fehler zeigt sich viel später. Es sind die Namensgeneratoren —Indizes, Bedingungen, temporäre Tabellen je Stapel—, die darauf stoßen, nicht die Hand eines Menschen.

Die großen, die fast nie das Problem sind
Sie sind hier nicht gemessen —dafür müsste man eine Platte füllen— und stehen mit ihrer offiziellen Zahl:

- Maximale Tabellengröße: 32 TB.
- Maximale Feldgröße: 1 GB.
- Maximale Zeilengröße: 1,6 TB.
- Zeilen je Tabelle: keine definierte Grenze.
- Datenbanken je Cluster und Tabellen je Datenbank: keine praktische Grenze.

Was lange vor jeder davon ausgeht, ist die Wartung: VACUUM, Sicherungen und Indexneubau über eine Tabelle von Terabytes.

TOAST: warum ein text von 1 GB die 8-kB-Seite nicht sprengt
Eine Zeile muss in eine Seite passen, und eine Seite hat 8 kB. Große Werte werden komprimiert und in eine Nebentabelle ausgelagert —das nennt sich TOAST—, automatisch und ohne dass man etwas erklären müsste.

Gemessen, mit 100 000 Byte Text in einer text-Spalte:

- octet_length — 100 000 Byte Daten.
- pg_column_size — 1 156 Byte, es wurde also komprimiert.
- Die Tabelle belegte 8 192 Byte und 16 kB samt ihrem TOAST.

Daraus folgt praktisch: ein SELECT * auf einer Tabelle mit großen Spalten bezahlt das Lesen dieser Spalten, auch wenn niemand hinsieht. Nur die nötigen Spalten anzufordern ist kein Stil, sondern E/A.

Die Grenzen, die sich einstellen lassen
Sie gehören zur Instanz, nicht zur Engine, und stehen deshalb in pg_settings: max_connections (ab Werk 100), max_locks_per_transaction (64), max_wal_size, work_mem. Diese gehen auf einem echten Server aus; die aus der Tabelle oben fast nie.

Empfehlung
Nur zwei im Blick behalten: die 63 Byte, sobald etwas Namen erzeugt, und die Spalten je Index, sobald jemand einen zusammengesetzten Index mit dem halben Schema darin vorschlägt. Vom Rest erfährt man durch pg_settings, nicht durch die Dokumentation.

Stichwörter: Grenzen, Kennung, 63 Byte, abgeschnitten, 1600 Spalten, 32 Spalten, Index, toast, Seite, 8 kB, 32 tb, 1 gb, pg_settings, max_connections

Wann die Daten ins Dokument gehören und wann referenziert wird: die vier Muster von MongoDB, das Antimuster des unbegrenzten Arrays und was jedes kostet, gemessen.

Gilt für: MongoDB 7.0+

In SQL ergibt sich das Schema aus der Normalisierung: jede Tatsache an einer einzigen Stelle, und die Abfragen setzen sie mit JOIN wieder zusammen. In MongoDB ergibt es sich aus dem Zugriffsmuster: was zusammen gelesen wird, wird zusammen gespeichert. Die Frage lautet nicht mehr „wie vermeide ich, einen Wert zu wiederholen?“, sondern „was soll mir ein einziger Lesevorgang liefern?“.

Einbetten oder referenzieren

Eine Bestellung kann ihre Positionen in sich tragen, oder die Positionen leben in einer eigenen Sammlung und verweisen auf die Bestellung.

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

Gemessen gegen MongoDB 8.2 mit 100.000 Bestellungen zu je drei Positionen: eine Bestellung mit ihren Positionen zu lesen kostet eingebettet 0,25 ms und mit $lookup 0,30 ms —aber 67 ms, wenn der Index auf l_ref.pedido fehlt, denn dann läuft jede Bestellung durch alle 300.000 Positionen—. Auf der Platte belegen die eingebetteten Bestellungen 2,9 MB gegenüber 1,1 MB + 5,2 MB der beiden getrennten Sammlungen: hier war Einbetten auch im Platz billiger.

Einbetten verzichtet nicht auf Indizes: ein Index auf ein Feld innerhalb des Arrays ist mehrschlüssig und wirkt genauso. {"lineas.sku": "A-7"} ohne ihn zu suchen ist ein COLLSCAN über 100.000 Dokumente in 42 ms; mit ihm 2.000 geprüfte in 2 ms, für dieselben 2.000 Zeilen. Und ein Dokument wird ohne Transaktion atomar geändert: das $inc eines Zählers und das $set eines Status in einem einzigen updateOne greifen gemeinsam oder gar nicht.

Das Antimuster: das Array, das nicht aufhört zu wachsen

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

Das leere Dokument hat 29 Bytes, und jeder Messwert fügt 28,9 hinzu. Bei 540.000 misst es 16.628.919 Bytes, und der nächste Schwung scheitert mit dem Code 10334: „Resulting document after update is larger than 16777216“. Die Grenze von 16 MB je Dokument ist nicht verhandelbar. Und es schmerzt schon vorher: ein $set eines skalaren Feldes kostet auf diesem Dokument 3,15 ms und auf einem von 228 Bytes 0,50 ms, und ein $pop des Arrays 71,95 ms gegenüber den 0,15 ms, einen einzelnen Messwert einzufügen. Ein Array, das ohne bekannte Obergrenze wächst, ist eine falsch platzierte Referenz.

Bucket: die Messwerte werden zu Dokumenten von je N gebündelt.

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

Dieselben 540.000 Messwerte: einzeln sind es 540.000 Dokumente, 6,5 MB Daten und 8,4 MB Index; in Buckets zu 200 sind es 2.700 Dokumente, 4,2 MB Daten und 82 KB Index. Das Einfügen kostet 203 ms in Buckets gegenüber 901 ms einzeln. Den letzten lesen: 0,20 ms aus dem Bucket, 0,30 ms einzeln und 46,80 ms aus dem eingebetteten Array, das ganz geholt werden muss.

Erweiterte Referenz: die Handvoll Felder des Elternteils, die immer angezeigt werden, in das Kind kopieren. 100 Bestellungen mit dem Namen ihres Kunden aufzulisten kostet 0,35 ms mit dem Namen innen dupliziert und 1,00 ms mit $lookup. Bezahlt wird beim Schreiben: einen Kunden umzubenennen heißt, seine 1.000 Bestellungen anzufassen, 2 ms mit einem Index auf cliente. Dupliziert wird, was sich fast nie ändert.

Berechneter Wert: die bereits summierte Gesamtsumme speichern, statt sie bei jedem Lesen neu zu berechnen. Bei einer Bestellung merkt man es nicht —0,35 ms aus dem Dokument gelesen, 0,30 ms im Flug summiert—; beim Aggregieren aller 100.000 schon: 14 ms beim Lesen des Feldes gegenüber 129 ms beim Neuberechnen.

Teilmenge: im Dokument nur die wenigen Zeilen, die angezeigt werden, der Rest in einer eigenen Sammlung. 500 Produkte mit je 500 Bewertungen: alle eingebettet ist das mittlere Dokument 80.738 Bytes groß; mit den letzten fünf und einem Zähler 889 Bytes, und die Sammlung sinkt von 6,1 MB auf 60 KB. Die Produktseite geht von 0,55 ms auf 0,25 ms, und die Seite mit 20 Bewertungen wird getrennt aus ihrer eigenen Sammlung geholt.

Die Regel in einer Zeile: bette ein, was mit seinem Elternteil gelesen wird, nur ihm gehört und eine Obergrenze hat; referenziere, was unbegrenzt wächst, von mehreren Eltern geteilt wird oder für sich allein abgefragt wird.

Stichwörter: Datenmodellierung, Einbetten, Referenz, Dokument, Muster, Bucket, Teilmenge, erweiterte Referenz, berechneter Wert, Array, Schema, 16 MB

Welchen Typ MongoDB bei jedem Wert speichert, wie viel er belegt, wie sich verschiedene Typen untereinander vergleichen und wie Text mit Akzenten sortiert wird.

Gilt für: MongoDB 7.0+

In SQL deklariert die Tabelle den Typ, und jede Zeile hält sich daran. In MongoDB reist der Typ mit jedem Wert: es gibt kein CREATE TABLE, und zwei Dokumente derselben Sammlung dürfen im selben Feld eine Ganzzahl und eine Zeichenkette tragen. Das Format heißt BSON: JSON in binär, mit den Typen, die JSON fehlen — Ganzzahlen mit 32 und 64 Bit, exakte Dezimalzahlen, Datumswerte, Binärdaten und ObjectId.

_id und ObjectId

Jedes Dokument hat ein _id: eindeutig, unveränderlich und indiziert, seit die Sammlung entsteht. Schreibst du es nicht selbst, setzt der Client eine ObjectId ein: 12 Bytes — 4 die Zeit in Sekunden, 5 zufällig pro Prozess, 3 ein Zähler. Sie wächst mit der Uhr und taugt daher für Datumsbereiche, ohne ein Datum zu speichern.

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

Als Zeichenkette abzulegen kostet und bringt nichts: {_id: ObjectId()} wiegt 22 Bytes, derselbe Wert als Zeichenkette 39.

Ganzzahlen, Gleitkommazahlen und Dezimalzahlen

Vier numerische Typen, und der Unterschied zeigt sich im Dokument: $bsonSize über {_id: 1, a: …} ergibt 21 Bytes mit int, 25 mit long oder double und 33 mit decimal. Geld gehört in decimal, aus demselben Grund wie in SQL in DECIMAL: 0,1 plus 0,2 ergibt als Gleitkommazahl 0.30000000000000004 und als Dezimalzahl 0.3.

Die Falle sitzt im Client: mongosh wählt den Typ nach dem Wert. Eine von Hand geschriebene 7 wird int, 3000000000 wird double, und 9007199254740993 wird 9007199254740992 — jenseits von 2⁵³ muss NumberLong("…") geschrieben werden.

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

Beim Abfragen sind die vier dagegen eine Zahl: liegen int, long, double und decimal mit dem Wert 7 in der Sammlung, findet {v: 7} alle vier und {v: "7"} keines. $type unterscheidet sie sehr wohl, "number" fasst sie wieder zusammen.

Datumswerte

Date sind 8 Bytes Millisekunden seit 1970, immer in UTC und ohne Zeitzone. Das Paar DATETIME / TIMESTAMP gibt es hier nicht: ein einziger Typ, die Zone setzt, wer liest, und Mikrosekunden gehen beim Speichern verloren. Der Timestamp von BSON ist nicht für deine Daten, sondern die interne Uhr der Replikation.

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

Binärdaten und UUID

BinData speichert Bytes mit einem Subtyp; UUID() ist Subtyp 4. Eine UUID als BinData belegt 29 Bytes Dokument, als Zeichenkette mit Bindestrichen 49.

Die Ordnung zwischen verschiedenen Typen

Ein sort über ein Feld mit gemischten Typen scheitert nicht: es gibt eine totale Ordnung zwischen den Typen, über vierzehn Dokumente gemessen.

MinKey → fehlendes Feld und null → Zahlen → Zeichenketten → Objekte → Binärdaten → ObjectId → Wahrheitswerte → Datumswerte → Timestamp → reguläre Ausdrücke → MaxKey.

Arrays fehlen dort, weil ein Array über sein kleinstes Element vergleicht: [9, 10] sortiert sich zwischen die Zahlen. Ein fehlendes Feld sortiert genau wie null, so sehr, dass {v: null} beides findet; zum Trennen dienen {v: {$type: "null"}} und {v: {$exists: false}}.

UTF-8 und Kollation

Es gibt keinen Zeichensatz zu wählen: BSON-Zeichenketten sind UTF-8, Punkt. "café ☕ 日本語 👩‍💻" sind 14 Zeichen und 31 Bytes und kommen genau so zurück, wie sie hineingingen.

Gewählt wird dagegen die Kollation, und voreingestellt ist sie binär: ohne collation sind cafe und café verschiedene Werte, und Árbol sortiert hinter zorro. Eine collation mit ihrem locale und ihrem strength — 1 ignoriert Akzente und Groß-/Kleinschreibung, 2 ignoriert nur die Groß-/Kleinschreibung, 3 unterscheidet alles — ändert Vergleich und Reihenfolge zugleich.

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

Und hier lauert dieselbe Falle wie in SQL: die Kollation der Abfrage muss die des Index sein. Mit einem gewöhnlichen Index auf n ist {n: "cafe"} ein IXSCAN, der 1 Dokument prüft; dieselbe Abfrage mit collation fällt auf COLLSCAN und prüft alle 7. Die Lösung ist nicht, collation in jede Abfrage zu schreiben, sondern sie der Sammlung zu geben: deren Indizes entstehen dann mit ihr.

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

Zwei weitere Warnungen, beide gemessen. $regex ignoriert die Kollation: /^CAF/ findet cafe auch mit strength: 1 nicht, {n: "CAFE"} schon. Und numericOrdering: true sortiert die Zeichenketten "1", "2", "10" als Zahlen statt als "1", "10", "2".

Stichwörter: bson, typen, objectid, decimal128, numberlong, datum, date, uuid, bindata, collation, kollation, utf-8, akzente, sortierung

Die elf Indexarten von MongoDB und wann welche passt, die ESR-Regel für die Reihenfolge eines zusammengesetzten Index, die abgedeckte Abfrage sowie Grenzen und Schreibkosten, alles mit `explain` gemessen.

Gilt für: MongoDB 7.0+

Ein Index ist ein B-Baum über den Wert eines Feldes, und die Zahl, die zeigt, ob er hilft, kommt aus explain("executionStats"): totalDocsExamined gegen nReturned. Ist die erste viel größer als die zweite, liest der Server Dokumente nur, um sie wegzuwerfen.

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

Die ESR-Regel

Ein zusammengesetzter Index wird über Präfixe gelesen, also ist die Reihenfolge seiner Felder die Entscheidung. Zuerst die Felder mit Gleichheit (E), dann das der Sortierung (S) und zuletzt das des Bereichs (R). Steht der Bereich vor der Sortierung, filtert der Index, ordnet aber nicht, und es erscheint eine SORT-Stufe, die im Speicher sortiert: 1 826 Dokumente in der Abfrage unten.

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

Diese Stufe hat eine Grenze: internalQueryMaxBlockingSortMemoryUsageBytes steht auf 104 857 600 Bytes, und darüber scheitert die Abfrage, wenn sie nicht auf die Platte ausweichen darf.

Abgedeckte Abfrage

Trägt der Index alle Felder, die die Abfrage liest, rührt der Server die Dokumente nicht an. Achtung bei _id: Es steckt standardmäßig in der Projektion, nicht im Index, und muss von Hand entfernt werden.

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

Die anderen Arten

Die beiden, die Daten stillschweigend verlieren, sieht man am besten zusammen:

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 }

Versteckt ist das Gegenteil: Er wird weiter gepflegt, aber der Planer sieht ihn nicht an. Mit hidden: true war dieselbe Abfrage ein COLLSCAN über 20 000 Dokumente; wieder sichtbar mit collMod, ein IXSCAN über 1 940.

Die Grenzen

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

Name und Schlüsselgröße haben keine praktische Grenze: 400 Zeichen und 2 000 Bytes wurden ohne Klage angenommen.

Der Preis

Jeder Index wird bei jedem Schreibvorgang bezahlt. Dieselben 20 000 Einfügungen dauerten 60 ms ohne Index, 101 ms mit fünf und 160 ms mit zehn. Und sie brauchen Platz: Die sechs Indizes von pedidos ergeben zusammen 1,9 MB gegenüber 916 KB an Daten.

Deshalb indiziert man nicht alles. Ein Feld mit geringer Kardinalität hilft selten: estado mit vier Werten prüft 5 000 Dokumente, um 5 000 zu liefern. Und ein einfacher Index ist überflüssig, wenn ein zusammengesetzter schon mit ihm beginnt: { cliente: 1 } und { cliente: 1, fecha: 1 } prüfen dieselben 40.

Stichwörter: Index, Indizes, ESR, zusammengesetzt, Multikey, Text, 2dsphere, hashed, Platzhalter, TTL, partiell, sparse, eindeutig, versteckt, abgedeckte Abfrage, explain, IXSCAN, COLLSCAN, totalDocsExamined, MongoDB

Die Stufen der Pipeline und ihre richtige Reihenfolge, $lookup als JOIN und was er ohne Index kostet, $graphLookup, Fensterfunktionen, $facet und $unionWith sowie $merge gegenüber $out.

Gilt für: MongoDB 7.0+

Eine Pipeline ist eine Liste von Stufen, und jede erhält die Dokumente, die die vorige erzeugt hat. Die Reihenfolge schreibst du, und dort steckt fast die gesamte Leistung.

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

Zuerst filtern, immer

Nur die erste Stufe kann einen Index nutzen. Mit einem Index {estado: 1} über diese 20 000 Bestellungen prüft $match vorn 5 000 Schlüssel und 5 000 Dokumente; derselbe Filter hinter dem $group keinen einzigen Schlüssel und 20 000 Dokumente.

$lookup ist der JOIN, und ohne Index wird er bezahlt

Er verbindet mit einer anderen Sammlung derselben Datenbank, und das Gefundene kommt als Array an, das fast immer mit $unwind geöffnet wird.

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

Das explain der Stufe lässt keinen Spielraum: ohne Index auf clientes.num macht sie einen vollständigen Durchlauf pro Eingabedokument, mit dem Index keinen einzigen.

Der rekursive und der mit Fenster

$graphLookup folgt einer Hierarchie, so weit sie reicht, und depthField hält fest, in welcher Entfernung jede Stufe lag. Das zurückgegebene Array ist nicht sortiert: hier kam Ana, Caro und Beto mit den Ebenen 2, 0 und 1. $setWindowFields (5.0+) ist das SQL-Fenster: partitionBy ist PARTITION BY und sortBy ist 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

Mehrere Antworten in einem Durchlauf

$facet führt Unterpipelines über dieselbe Eingabe aus und liefert ein einziges Dokument mit allen; darin zählt kein Index mehr. $unionWith ist das 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 }

Das Ergebnis schreiben

$merge verschmilzt in die Zielsammlung, und $out ersetzt sie vollständig.

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

Die Obergrenzen

Jede blockierende Stufe — $group, $sort, $facet, das Zwischenergebnis von $lookup — bekommt 104 857 600 Bytes, und seit 6.0 geht der Überlauf von selbst auf die Platte. Aber das Array eines Akkumulators läuft nicht über: ein $push über große Dokumente scheitert mit 146 ExceededMemoryLimit, und allowDiskUse: true rettet es nicht.

Stichwörter: Aggregation, Pipeline, Stufe, match, group, project, lookup, Join, graphLookup, setWindowFields, Fenster, facet, unionWith, merge, out, unwind, explain, allowDiskUse

Ein Dokument wird ohne Transaktion ganz geschrieben; für mehrere braucht es eine und ein Replica Set. Die Sitzung, readConcern und writeConcern, der WriteConflict 112 und die drei gemessenen Obergrenzen.

Gilt für: MongoDB 7.0+

In MongoDB wird ein Dokument ganz geschrieben oder gar nicht, und das gilt auch, wenn das updateOne zehn Felder und ein verschachteltes Array berührt. Um mehr als ein Dokument auf einmal zu ändern, braucht es eine Transaktion, und eine Transaktion verlangt ein Replica Set: auf einem einzelnen Knoten wird sie abgelehnt, und die Meldung spricht nicht einmal von Transaktionen — sie sagt, das Deployment unterstütze keine wiederholbaren Schreibvorgänge.

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

Eine Transaktion läuft über eine Sitzung

Alles darin geht über das Sitzungsobjekt: das db.cuentas von außen steckt nicht in der Transaktion, so gleich es auch heißen mag. Bis zum commitTransaction() sieht das Geschriebene nur, wer drinnen ist.

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() macht alles rückgängig, und man muss nicht darum bitten: geht die Sitzung verloren oder startet der Server neu, stirbt die Transaktion abgebrochen.

readConcern und writeConcern sind zwei verschiedene Fragen

readConcern sagt, was gelesen wird: local ist das, was hier liegt, majority das, was nicht mehr verloren gehen kann, snapshot ein stimmiges Bild eines Augenblicks. writeConcern sagt, wann etwas als geschrieben gilt: w: 1 ist der Primary, w: "majority" die Mehrheit des Sets, und j: true nimmt das Journal dazu. Die Werkseinstellungen liefert getDefaultRWConcern: Lesen local, Schreiben majority.

Der Schreibkonflikt

Zwei Transaktionen auf demselben Dokument warten nicht: die zweite scheitert auf der Stelle mit 112 WriteConflict, und die Meldung sagt es ohne Umschweife. Der Wiederholungsversuch gehört dazu, und deshalb bringen die Treiber withTransaction mit, das von selbst wiederholt.

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

Ein Schreibvorgang von außerhalb der Transaktion bekommt die 112 nicht: er wartet. In der Messung wartete er 76 s, bis das Lebenszeitlimit die Transaktion abbrach, die das Dokument hielt.

Die Obergrenzen

Eine Transaktion lebt 60 s, und jenseits dieser Linie liefert der Commit 251 NoSuchTransaction mit «has been aborted». Drinnen wartet jede Sperranforderung nur 5 ms: eine Transaktion hängt sich nicht an ein Schloss, sie scheitert lieber. Und ein writeConcern, das das Set nicht erfüllen kann, scheitert, bevor es überhaupt versucht wird.

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

Die praktische Regel: müssen sich zwei Dokumente viele Male am Tag gemeinsam ändern, stimmt meistens das Modell nicht, und richtig gewesen wäre, sie einzubetten. Die Transaktion ist der Ausweg für das, was wirklich nicht in ein Dokument passt.

Stichwörter: Transaktion, Transaktionen, Sitzung, startTransaction, Commit, Abbruch, WriteConflict, 112, 251, readConcern, writeConcern, majority, snapshot, Replica Set, Atomarität, Sperre

Die drei Ausführlichkeitsstufen von explain und was jede hinzufügt, der Plan-Cache und wann ein Plan deaktiviert wird, das Working Set im WiredTiger-Cache und die drei Stufen des Profilers.

Gilt für: MongoDB 7.0+

Bevor man irgendetwas anfasst, wird gemessen, und das Werkzeug heißt explain. Es hat drei Ausführlichkeitsstufen, und jede kostet mehr als die vorige: queryPlanner plant nur — er führt nie aus — und zeigt den Gewinnerplan und die verworfenen; executionStats führt den Gewinner aus und ergänzt, was er gekostet hat; allPlansExecution ergänzt zusätzlich, was jeder Kandidat während der Probephase gekostet hat, und dort sieht man, warum der Gewinner gewonnen hat.

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

Die drei Zahlen, auf die es ankommt, sind nReturned, totalDocsExamined und totalKeysExamined. Ist die zweite viel größer als die erste, liest der Server Dokumente, um sie wegzuwerfen.

Der Plan-Cache

Der Planer entscheidet nicht bei jeder Abfrage neu. Beim ersten Mal probiert er die Kandidaten, legt den Gewinner unter einem planCacheKey ab und nutzt ihn von da an wieder; im explain erscheint das als isCached: true. Der Eintrag hält works fest, den Aufwand, den er gekostet hat, und wenn ein späterer Lauf zehnmal mehr verbraucht, wird der Plan deaktiviert und es wird neu gewetteifert. Auch das Anlegen oder Löschen eines Index leert den 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

Das Working Set und der WiredTiger-Cache

MongoDB hält keine Ergebnisse vor: was es vorhält, sind Seiten, im WiredTiger-Cache, und die Leistung hängt daran, dass das Working Set — die tatsächlich angefassten Daten und Indizes — dort hineinpasst. Standardmäßig nimmt dieser Cache die Hälfte des RAM minus 1 GB. In der Messung mussten von 486 864 angeforderten Seiten nur 261 von der Platte geholt werden: eine von 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

Der Profiler

Drei Stufen: 0 aus, 1 nur das, was über slowms liegt, und 2 alles. Zwei gemessene Dinge, die überraschen: setProfilingLevel liefert die Stufe von vorher zurück, nicht die gerade gesetzte — die neue muss man nachlesen — und system.profile ist eine gedeckelte Sammlung von 1 MiB, sie wächst also nicht: sie beißt sich in den Schwanz.

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

Jeder Eintrag bringt planSummary, docsExamined, nreturned und millis mit, und genau das braucht man, um über einen Index zu entscheiden. Calíope liest diese Sammlung in seinem Profiling-Werkzeug. Stufe 2 ist im Betrieb teuer: man schaltet sie für eine Weile ein und wieder herunter, man lässt sie nicht stehen.

Stichwörter: Leistung, explain, queryPlanner, executionStats, allPlansExecution, Plan-Cache, planCacheKey, isCached, WiredTiger, Working Set, Profiler, system.profile, slowms

Was laut getCmdLineOpts wirklich läuft, die fünf Abschnitte von mongod.conf, auf die es ankommt, und die drei Arten von Parametern: die im Betrieb änderbaren, die des Starts und die, die gar keine sind.

Gilt für: MongoDB 7.0+

Die erste Frage zu einem Server, den man nicht kennt, ist nicht, was in seiner Konfigurationsdatei steht, sondern womit er tatsächlich läuft. getCmdLineOpts beantwortet beides auf einmal: argv ist das, was ihm auf der Befehlszeile mitgegeben wurde, und parsed dasselbe, schon in das Vokabular der Datei übersetzt.

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

Die fünf Abschnitte, auf die es ankommt

storage sagt, wo die Daten liegen und wie viel Speicher der Cache nimmt; net, auf welchen Adressen er lauscht; security, ob man sich anmelden muss; operationProfiling, was vom langsamen Verkehr festgehalten wird; und replication, zu welchem Set er gehört. Die Datei ist YAML, die Einrückung ist also Syntax.

# 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

Parameter gibt es in drei Sorten

Die, die man im Betrieb mit setParameter ändert, die, die nur beim Start gelesen werden, und die, die gar keine Parameter sind, so sehr sie auch danach aussehen. Alle drei erkennt man an der Antwort des Servers: die erste liefert was mit dem vorherigen Wert — nicht dem neuen, man muss ihn also nachlesen; die zweite gibt 20 IllegalOperation; und port, eine Startoption und kein Parameter, gibt 72 InvalidOptions mit «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 bleibt nicht

Ein setParameter lebt bis zum nächsten Neustart und keine Sekunde länger: mit cursorTimeoutMillis auf 300 000 kam der Server mit 600 000 wieder hoch. Damit es bleibt, muss es in den Abschnitt setParameter: der Datei, den unten im Beispiel.

Der Cache und die Kompatibilitätsversion

Der WiredTiger-Cache ist das Erste, wonach alle greifen, und fast immer das, was man nicht anfassen sollte: standardmäßig nimmt er die Hälfte dessen, was vom RAM übrig bleibt, nachdem 1 GB beiseitegelegt wurde. Auf dem gemessenen Knoten ergaben 7 933 MB RAM 3 621 781 504 Bytes Cache. Und es gibt ein sechstes Ding, das nicht in der Datei steht: die featureCompatibilityVersion, die entscheidet, welche Funktionen des Binärprogramms eingeschaltet sind — sie wird nach einem Upgrade von Hand angehoben und vor einem Rückschritt gesenkt.

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

Stichwörter: Konfiguration, mongod.conf, getCmdLineOpts, setParameter, cacheSizeGB, WiredTiger, bindIp, authorization, operationProfiling, replSetName, featureCompatibilityVersion, FCV

Ein Benutzer wohnt in einer Datenbank, und das ist sein Nachname; die eingebauten Rollen sind in admin andere als sonst; die 13, die ein Schreibvorgang ohne Recht bekommt; und die einzige Tür, die ein frisch abgesicherter Server offen lässt.

Gilt für: MongoDB 7.0+

Ohne security.authorization: enabled gibt es nichts davon: der Server nimmt jeden, der auftaucht, und mit bindIp: "*" kann jeder. Eingeschaltet lautet die erste Frage: wer bin ich.

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

Der Standardmechanismus ist SCRAM-SHA-256, und der Server hält beide Fassungen vor: das Passwort des gemessenen Benutzers trägt 15 000 Iterationen unter SHA-256 und 10 000 unter SHA-1, mit einem Salt von 40 Zeichen. Das Passwort reist nicht, nicht einmal verschlüsselt: SCRAM beweist, dass man es kennt, ohne es zu nennen. Der dritte Mechanismus, MONGODB-X509, tauscht das Passwort gegen ein Client-Zertifikat, und dann ist der Benutzername der Betreff des Zertifikats.

Ein Benutzer wohnt in einer Datenbank, und das ist seine andere Hälfte

lector ist kein Benutzer: lector von ventas ist einer. Die Datenbank, in der er angelegt wurde, ist seine Authentifizierungsdatenbank, und man muss sie beim Verbinden nennen (--authenticationDatabase). Mit der falschen sagt der Server nicht, dass es den Benutzer anderswo gibt: er sagt «Authentication failed», mehr nicht.

Die Rollen sind nicht in jeder Datenbank dieselben

Eine normale Datenbank hat sechs eingebaute Rollen. admin hat einundzwanzig, denn dort wohnen die, die den ganzen Server erreichen — die …AnyDatabase, root, backup, restore, die des Clusters. Eine Rolle ist eine Liste von Aktionen: read sind elf davon, und find ist nur eine.

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

Was passiert, wenn ein Recht fehlt

Es gibt keine leere Antwort und keine fehlende Zeile: es gibt eine 13 Unauthorized, und die Meldung nennt die Datenbank, den Befehl und sogar die Sammlung. Aus diesem Fehler lässt sich die fehlende Regel herauslesen.

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: ... }

Die localhost-Ausnahme

Ein Server mit --auth und ohne einen einzigen Benutzer lässt den, der von der Maschine selbst kommt, genau eines tun: den ersten anlegen. Lesen nicht mehr; einen zweiten anlegen auch nicht. Es ist die Rampe zum Loslegen, und sie schließt sich von selbst, sobald ein Benutzer existiert.

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

Stichwörter: Sicherheit, Authentifizierung, SCRAM, SCRAM-SHA-256, x.509, TLS, Benutzer, Rolle, eingebaute Rollen, read, readWrite, dbAdmin, userAdmin, root, 13, Unauthorized, localhost, authSource

Was in einem mongodump steckt und was nicht, wie lange das Zurückspielen dauert und warum, wozu --oplog dient, wie lang das Oplog-Fenster wirklich reicht, und die zwei Dinge, die ein Dump nicht garantiert.

Gilt für: MongoDB 7.0+

mongodump ist eine logische Sicherung: es verbindet sich wie jeder andere Client, liest die Dokumente und schreibt sie als BSON. Das hat zwei Folgen, die man an den Zahlen sieht. Die erste: die Datei ist so groß wie die Dokumente, nicht wie ihr Platz auf der Platte — 2 420 000 Bytes .bson für eine Sammlung, die auf der Platte komprimiert liegt. Die zweite: sie konkurriert mit der normalen Arbeit des Servers um den Cache, ein Dump einer großen Datenbank ist also spürbar.

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

Von den Indizes reist nur die Definition, in der .metadata.json. Deshalb kostet das Zurückspielen weit mehr als das Sichern — 91 ms gegen 26 in dieser Messung: die Zeit geht in den Neuaufbau, und bei einer echten Sammlung ist das fast die ganze Wartezeit.

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 hinterlässt eine einzige Datei statt eines Ordnerbaums, und mit --gzip schrumpfte sie von 2 420 000 auf 133 572 Bytes. Beide lassen sich durch eine Pipe schicken, und genau so kopiert man eine Datenbank von einer Maschine auf die andere, ohne eine Platte dazwischen zu berühren.

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

Das Oplog macht aus einer Sicherung einen Zeitpunkt

Ein Dump dauert, und währenddessen ändert sich die Datenbank weiter: was in Sammlung A vor ihrem Sichern und in B danach geschrieben wurde, passt nicht zusammen. --oplog sichert zusätzlich die während des Dumps aufgetretenen Operationen, und mongorestore --oplogReplay spielt sie am Ende ein, sodass das Wiederhergestellte der Zustand eines Augenblicks ist, nämlich des Dump-Endes. Das geht nur gegen ein Replica Set, denn das Oplog gehört ihm.

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

Das Oplog ist eine gedeckelte Sammlung, sein Fenster misst sich also nicht in Bytes, sondern in Zeit, und diese Zeit hängt daran, wie viel geschrieben wird. Auf dem gemessenen Knoten ergaben 42 GiB Deckel ein Fenster vom 8. August bis zum 12. September; bei zehnfacher Last wären es drei Tage. Das ist die Zahl, die man ansieht, bevor man ins Wochenende geht.

Was ein Dump nicht abdeckt

Zwei Dinge. Eine Datenbank von Hunderten Gigabytes sichert man nicht, indem man sie Dokument für Dokument liest: dafür gibt es Dateisystem-Snapshots, die mit dem Journal zusammen oder mit einer per fsyncLock gesperrten Datenbank genommen werden müssen. Und in einem gesharddeten Cluster liefert ein mongodump gegen den Router keinen gemeinsamen Zeitpunkt für die Shards: man muss den Balancer anhalten und je Shard einen Snapshot nehmen, dazu einen der Konfigurationsserver.

Stichwörter: Sicherung, Backup, mongodump, mongorestore, Oplog, oplogReplay, archive, gzip, Snapshot, fsyncLock, Wiederherstellung, Wiederherstellungsfenster, BSON

Das Schema ist das, was die Dokumente mitbringen, es zu ändern heißt also, sie zu schreiben. Der Validator mit $jsonSchema, die 121 und ihr errInfo, die vier Kombinationen aus validationLevel und validationAction, und die Migration nach Dokumentversion.

Gilt für: MongoDB 7.0+

Es gibt kein ALTER TABLE, weil es keine Tabelle gibt: das Schema einer Sammlung ist buchstäblich das, was ihre Dokumente mitbringen. Den Neuen ein Feld hinzuzufügen kostet nichts und ändert die Alten nicht, und genau da liegt die Falle: wer liest, muss mit beiden Formen zurechtkommen, bis jemand die Vergangenheit angleicht.

Der Validator ist eine Tür, kein Schema

Was es gibt, ist ein validator mit $jsonSchema: eine Bedingung, die beim Schreiben geprüft wird, nie beim Lesen und nie rückwirkend. Er lehnt mit 121 DocumentValidationFailure ab, und das Gute steckt in errInfo.details, das die verletzte Regel benennt, statt «ungültig» zu sagen.

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

Achtung bei den Typen: mongosh speichert eine ganze Zahl als int, also besteht edad: 30 ein bsonType: "int" und edad: 30.5 verletzt es, denn das ist wirklich ein double.

validationLevel und validationAction sind zwei verschiedene Regler

Die Stufe sagt, welche Dokumente erfasst werden: strict alle, moderate nur die, die schon gültig waren — so setzt man einen Validator auf eine Sammlung voll altem Schrott, ohne deren Aktualisierungen zu blockieren. Die Aktion sagt, was passiert, wenn es fehlschlägt: error lehnt ab, warn lässt schreiben und vermerkt es im Log. Und den Validator per collMod zu setzen rührt nichts an, was schon da war.

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

Migrieren heißt schreiben

Ohne ALTER ist das Gegenstück einer neuen Spalte ein updateMany mit $set und das des Entfernens eines mit $unset. Sie sind billig — 20 000 Dokumente in 61 und 51 ms — aber sie sind nicht atomar: sie gehen Dokument für Dokument, während der Migration bestehen also beide Formen nebeneinander.

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

Deshalb hält das Muster, die Version in jedem Dokument zu führen (_v): die Anwendung kann beide lesen, die Migration schreitet in Stapeln oder beim Anfassen jedes Dokuments voran, und an dem Tag, an dem {_v: 1} nichts mehr liefert, fliegt der alte Code raus. Genau das tut eine SQL-Migration auch, nur ist hier der Zwischenzustand sichtbar und muss aufgeschrieben werden.

Stichwörter: Schema, Validierung, Validator, jsonSchema, 121, DocumentValidationFailure, validationLevel, validationAction, strict, moderate, warn, collMod, Migration, Dokumentversion

Die drei Teile eines gesharddeten Clusters, warum ein gehashter Schlüssel verteilt und ein monotoner anhäuft, der gemessene Unterschied zwischen gezielter und gestreuter Abfrage, Zonen, und was ein Schlüsselwechsel kostet.

Gilt für: MongoDB 7.0+

Sharding heißt, eine Sammlung auf mehrere Maschinen zu verteilen, und dazu gehören drei Teile: die Shards, die die Daten halten und Replica Sets sind; die Konfigurationsserver, die die Karte halten, welcher Chunk wo liegt; und mongos, der Router, der nichts hält und mit dem sich die Anwendung verbindet.

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

Der Shard-Schlüssel ist die einzige Entscheidung, auf die es ankommt

Aus ihm folgen drei Dinge auf einmal: wie sich die Daten verteilen, welche Abfragen auf einen einzigen Shard gerichtet werden können, und ob es einen heißen Punkt gibt. Verlangt sind Kardinalität — viele verschiedene Werte —, gleichmäßige Häufigkeit, damit kein Wert die Hälfte bekommt, und dass er nicht monoton ist, denn ein stets wachsender Schlüssel schickt jeden neuen Schreibvorgang an dieselbe Stelle.

Das Letzte ist keine Theorie. Auf derselben Sammlung von 60 000 Dokumenten: ein hashed-Schlüssel auf den Kunden ließ 52,11 % auf dem einen Shard und 47,88 % auf dem anderen; die ObjectId _id, die immer wächst, ließ 100 % auf einem einzigen, in einem einzigen Chunk.

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

Gezielt oder gestreut

Eine Abfrage, die den Schlüssel mitbringt, geht an einen Shard, fertig. Eine ohne ihn wird allen gestellt und die Antworten werden verschmolzen: SINGLE_SHARD gegen SHARD_MERGE. Der gemessene Unterschied sind 121 geprüfte Dokumente gegen 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

Zonen, und den Schlüssel wechseln

Eine Zone bindet einen Bereich des Schlüssels an einen Shard, und dafür gibt es zwei echte Gründe: die Daten eines Landes auf Maschinen dieses Landes zu halten, und Heißes von Kaltem zu trennen. Und seit 5.0 lässt sich der Schlüssel mit reshardCollection wechseln, aber das kopiert die ganze Sammlung, und man merkt es: bei 60 000 Dokumenten arbeitete es nach zwei Minuten noch, mit seiner temporären Sammlung gut sichtbar.

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

Drei Dinge, die nicht mehr stimmen

Man hört immer noch, ein updateOne ohne den Schlüssel scheitere, der Wert des Schlüssels lasse sich nicht ändern, und der Index müsse vor dem Sharding existieren. Auf 8.2 gingen alle drei ohne Murren durch.

Stichwörter: Sharding, Shard-Schlüssel, hashed, Chunk, mongos, Konfigurationsserver, Zone, Balancer, reshardCollection, SINGLE_SHARD, SHARD_MERGE

Die acht Codes, die täglich auftauchen, einzeln provoziert samt dem Text, den der Server zurückgibt, und die zwei, die mehr enthalten als die Nummer: die 11000 mit ihrem Schlüssel und die 121 mit ihrem errInfo.

Gilt für: MongoDB 7.0+

Ein MongoDB-Fehler bringt eine Nummer, fast immer einen Namen, und manchmal etwas darin, das mehr wert ist als beides. Dies sind die acht, die täglich auftauchen, einzeln gegen den Server provoziert und so übernommen, wie sie kamen.

Die zwei ohne Namen bringen etwas Besseres

Die 11000 und die 121 kommen ohne codeName, und das ist gleichgültig: beide bringen genau die Angabe mit, die man zum Beheben braucht. Die 11000 nennt die Sammlung, den Index und den Wert, der kollidierte, man muss also nicht raten, welcher von drei eindeutigen Indizes ausgelöst hat.

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

Die 121 sagt «Document failed validation» und sonst nichts in der Meldung, aber e.errInfo.details trägt die verletzte Regel mit Namen und erwartetem Wert. Das ist der Unterschied zwischen «ungültig» und «es fehlt 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"] } ] }

Die zwei Rechte-Fehler sind verschieden

Die 18 heißt «ich weiß nicht, wer du bist»: falsches Passwort, oder — meistens — die falsche Authentifizierungsdatenbank, denn ein MongoDB-Benutzer ist der Name plus die Datenbank, in der er angelegt wurde. Die 13 heißt «ich weiß, wer du bist, und du darfst nicht»; ihre Meldung nennt die Datenbank, den Befehl und sogar die Sammlung, die fehlende Rolle liest man also direkt heraus.

Und zwei Warnungen zur Syntax

Die 50 ist kein Serverfehler, sondern die Obergrenze, die man selbst mit maxTimeMS gesetzt hat. Und die 26 taucht dort auf, wo man sie am wenigsten erwartet: collMod und renameCollection auf etwas Nichtexistierendes scheitern, aber drop() auf eine nicht vorhandene Sammlung liefert false und fertig — es ist kein Fehler, ein Skript, das darauf baut, erfährt also nie, dass es sich im Namen geirrt hat.

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

Stichwörter: Fehler, Codes, 11000, duplicate key, 13, Unauthorized, 18, AuthenticationFailed, 26, NamespaceNotFound, 50, MaxTimeMSExpired, 112, WriteConflict, 121, 251, NoSuchTransaction, errInfo

Die acht Obergrenzen, an die man wirklich stößt, einzeln gegen den Server provoziert, mit dem Code, den jede zurückgibt, und den dreien, die nicht dort liegen, wo ihr Ruf sie vermutet.

Gilt für: MongoDB 7.0+

All dies wurde gegen den Server provoziert, die Nummer links ist also die, die tatsächlich abgelehnt hat, nicht die aus der Legende.

Die 16 MB sind die einzige, an die man aus Versehen stößt

Und fast immer aus demselben Grund: ein Array, das ungebremst in einem Dokument wächst. Die Meldung bringt beide Zahlen mit, die des Dokuments und das Maximum, man sieht also auf einen Blick, um wie viel es zu viel war.

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

Die zwei Index-Grenzen werden falsch gezählt

Die 64 Indizes pro Sammlung schließen _id_ ein, es passen also 63 eigene; und es ist keine Grenze, an die man gesund stößt: bei 64 Indizes pflegt jeder Schreibvorgang 64 Bäume. Auch die 32 Felder eines zusammengesetzten Index sind kein Ziel: jenseits von sechs oder sieben braucht man ziemlich sicher zwei Indizes, nicht einen breiteren.

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

Drei, die nicht dort liegen, wo ihr Ruf sie vermutet

Die Verschachtelung ist mit 100 Ebenen dokumentiert, und abgelehnt hat der Server die 180., denn die Obergrenze gehört zum BSON des gesamten Befehls, und die insert-Hülle nimmt einen Teil davon. Der lange Name scheitert nicht an der Sammlung, sondern an der Summe aus Datenbank und Sammlung, also am Namensraum. Und die Grenze für die Größe eines Indexschlüssels, in alten Versionen 1 024 Bytes, gibt es nicht mehr: ein indizierter Wert von 50 000 ging durch.

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

Und was keine Grenze hat

Weder die Zahl der Sammlungen noch die der Datenbanken noch die der Dokumente in einer Sammlung. Das, was zuerst ausgeht, steht nicht in dieser Tabelle: es ist die Platte. Und für das, was wirklich nicht in 16 MB passt — eine Datei —, gibt es GridFS, das sie in Stücke von 255 KB schneidet und jedes Stück als gewöhnliches Dokument ablegt.

Stichwörter: Grenzen, Obergrenzen, 16 MB, Dokumentgröße, 10334, Verschachtelung, Overflow, Indizes pro Sammlung, 67, CannotCreateIndex, 13103, Namensraum, 73, InvalidNamespace, Indexschlüssel

Die Zusammenfassung des MongoDB-Handbuchs: sieben Gewohnheiten, die sich lohnen, jede mit der Messung dahinter, und die fünf Zeilen, mit denen man einem gerade geerbten Server den Puls fühlt.

Gilt für: MongoDB 7.0+

Dies ist das Ende des Handbuchs, und es bringt nichts Neues: es sammelt, was jedes Thema belegt hat, in der Form, die im Alltag nützt. Jede Gewohnheit kommt mit der Zahl, die sie trägt, und all diese Zahlen wurden an einem echten Server gemessen, nicht abgeschrieben.

1. Modelliere danach, wie du lesen wirst, nicht danach, wie es einer Tabelle ähnelt. Was zusammen gelesen wird, wird zusammen gespeichert. Die Grenze dieser Regel ist hart und gemessen: ein Dokument kommt nicht über 16 777 216 Bytes, ein ungebremst wachsendes Array endet also an irgendeinem Dienstag in einer 10334.

2. Ein Index pro häufiger Abfrage, und kein einziger mehr. Jeder Index ist ein Baum, der bei jedem Schreibvorgang gepflegt werden muss: dieselben 20 000 Einfügungen dauerten 60 ms ohne Index, 101 mit fünf und 160 mit zehn. Und sie brauchen Platz: in der Beispieldatenbank trug eine Sammlung mit 1 148 000 Bytes Daten 622 592 an Indizes.

3. Beurteile eine Abfrage nach totalDocsExamined gegen nReturned, nicht nach der Uhr. Die Uhr sagt, was es heute gedauert hat, mit warmem Cache und ruhiger Maschine; das Verhältnis dieser beiden Zahlen sagt, was passiert, wenn die Sammlung zehnmal größer ist.

4. writeConcern: majority für das, was nicht verloren gehen darf. Auf einem Replica Set ist es bereits der Werkswert, die Gewohnheit ist also nicht, es zu setzen: sie ist, es nicht zu entfernen, um schneller zu sein.

5. Nie ohne Authentifizierung. Es ist eine Zeile in der Datei, und die einzige Tür, die ein frisch gestarteter Server offen lässt — den ersten Benutzer von der Maschine selbst anzulegen —, schließt sich von selbst, sobald dieser Benutzer existiert.

6. Sichere mit dem Oplog, und miss sein Fenster in Zeit. mongodump --oplog macht aus einem Dump einen Augenblick. Und die Größe des Oplogs liest man nicht in Bytes, sondern in Tagen: der gemessene Knoten ergab 34, aber das hängt daran, wie viel geschrieben wird, es ist also eine Zahl, die man wieder ansieht, wenn sich die Last ändert.

7. Das Working Set muss in den Cache passen. Es ist die eine Leistungsregel ohne Tricks. In der Beispieldatenbank: 1 150 242 Bytes Daten gegen 3 621 781 504 Cache — dreitausendmal Platz zu viel. An dem Tag, an dem keiner mehr übrig ist, merkt man es überall gleichzeitig.

Der Puls eines gerade geerbten Servers

Fünf Zeilen beantworten, was man wissen muss, bevor man irgendetwas anfasst: ob er ein Passwort verlangt, wann ein Schreibvorgang als geschrieben gilt, ob jemand die langsamen Abfragen beobachtet, wie viel Speicher er zum Arbeiten hat und wie viele Daten er bewegen muss.

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

Und eine weitere, die zeigt, wohin die Platte geht und nebenbei, welche Sammlung mehr Index als Daten trägt.

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

Stichwörter: bewährte Praxis, Zusammenfassung, Zugriffsmuster, Indizes, writeConcern, majority, Authentifizierung, Oplog, Working Set, Cache, explain, Serverzustand

Der Typ steckt im Wert, nicht in der Spalte: die fünf Affinitäten und was jede umwandelt, die Ordnung zwischen den Speicherklassen, was STRICT tatsächlich verhindert, und warum NOCASE nichts von Akzenten weiß.

Gilt für: SQLite 3.35+

In SQLite gehört der Typ zum Wert, nicht zur Spalte. Was eine Spalte deklariert, ist eine Affinität: eine Vorliebe, die beim Speichern angewandt wird, die umwandelt, wenn sie kann, und durchlässt, wenn sie nicht kann.

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

Da stehen die fünf in einer Zeile. INTEGER und REAL wandeln Text um, der wie eine Zahl aussieht; TEXT wandelt die Zahl in Text; NUMERIC sieht sich den Wert an und entscheidet, in derselben Spalte stehen also ein integer und ein real; und BLOB ist die ohne Affinität: sie speichert, was man ihr gibt, so wie es kam.

Es gibt fünf Speicherklassen, und sie sind geordnet

Eine Spalte ohne deklarierten Typ ist erlaubt und nimmt alle fünf, in einer Spalte können also ein Integer, ein Real, ein Text, ein Blob und ein Null liegen. Und man kann sie sortieren, denn zwischen den Klassen gibt es eine feste Ordnung: zuerst die Nulls, dann die Zahlen, dann der Text und zuletzt die Blobs. Das heißt, ein ORDER BY über eine schmutzige Spalte scheitert nicht: es gruppiert nach Typ, ohne es jemandem zu sagen.

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 verhindert weniger, als es scheint

Seit 3.37 kann eine Tabelle als STRICT deklariert werden, dann nimmt sie nur eine Handvoll Typen — INT, INTEGER, REAL, TEXT, BLOB und ANY — und lehnt ab, was sie nicht speichern kann. Aber sie wandelt weiterhin um: eine '42' geht in eine INTEGER-Spalte, weil nichts verloren geht, und eine 42 geht in eine TEXT-Spalte und wird als '42' abgelegt. Abgelehnt wird, wofür es keine Umwandlung gibt. Und es gibt einen unerwarteten Gewinn: ein erfundener Typ, den eine normale Tabelle stillschweigend annimmt, wird hier schon beim Anlegen abgelehnt.

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"

Kein Datum, kein Boolescher Wert, und die Kollation weiß nichts von Akzenten

TRUE ist ein integer mit dem Wert 1. Ein Datum ist das, wofür du dich entscheidest: date() liefert text und julianday() liefert real, und was du wählst, wirst du für den Rest seines Lebens sortieren und vergleichen. Und es gibt nur drei Kollationen — BINARY, NOCASE und RTRIM: NOCASE gleicht Groß- und Kleinschreibung des ASCII an und sonst nichts, café und CAFÉ sind also verschiedene Werte, und upper('café') liefert CAFé. Der Text ist sehr wohl UTF-8: length zählt Zeichen, über dem Blob zählt es Bytes.

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

Stichwörter: Typen, Affinität, typeof, INTEGER, REAL, TEXT, BLOB, NUMERIC, dynamische Typisierung, STRICT, Boolescher Wert, Datum, julianday, COLLATE, NOCASE, UTF-8, Speicherklasse

Die Isolation ist serialisierbar, weil nur einer schreibt: die drei BEGIN-Modi, der SQLITE_BUSY 5 und warum busy_timeout ihn löst, die 517, die er nicht löst, und was der WAL-Modus wirklich ändert.

Gilt für: SQLite 3.35+

SQLite hat keine Isolationsstufen zur Auswahl, und das ist keine Lücke: die Isolation ist serialisierbar, weil in der ganzen Datenbank immer nur einer schreibt. Alles Weitere folgt daraus.

Zwei Einstellungen regeln es, und beide kommen ab Werk mit dem denkbar schlechtesten Wert.

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;

Die drei BEGIN-Modi

DEFERRED — der Standard — nimmt nichts, bis es sein muss: das erste Lesen nimmt eine Momentaufnahme, das erste Schreiben fordert die Sperre an. IMMEDIATE fordert die Schreibsperre sofort an, in der BEGIN-Zeile selbst. EXCLUSIVE verlangt zusätzlich, dass niemand liest, und im WAL-Modus tut es kaum mehr als IMMEDIATE. Die praktische Regel: wenn die Transaktion schreiben wird, BEGIN IMMEDIATE; es kostet vorn eine Wartezeit und erspart den lästigsten Fehler von SQLite, den weiter unten.

SAVEPOINT ist die Zwischenmarke, und ROLLBACK TO kehrt dorthin zurück, ohne die Transaktion zu schließen.

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 ist die 5, und sie ist fast immer die eigene

Wenn ein anderer die Schreibsperre hält, lautet die Antwort SQLITE_BUSY mit dem Code 5 und dem Text «database is locked». Das ist kein Defekt: es ist die Schlange vor einer einspurigen Ressource. Zum Defekt macht es, dass busy_timeout ab Werk 0 ist, unangetastet kommt die Antwort also sofort und trocken. Mit 800 ms wartete derselbe Aufruf 895, bevor er aufgab.

# 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

Die, die busy_timeout nicht behebt: die 517

Wenn eine DEFERRED-Transaktion liest und dann schreiben will und dazwischen jemand bestätigt hat, taugt die beim Lesen genommene Momentaufnahme nicht mehr, und SQLite liefert SQLITE_BUSY_SNAPSHOT, die 517. Warten hilft nicht: niemand wird diese Aufnahme zurückgeben. Der einzige Ausweg ist ROLLBACK und noch einmal von vorn — oder besser: mit IMMEDIATE geöffnet zu haben.

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

Was WAL ändert und was nicht

Mit dem Rollback-Journal liest, während einer schreibt, niemand. Mit journal_mode = WAL lesen die Leser weiterhin die letzte bestätigte Fassung, während der Schreiber arbeitet: gemessen mit zwei Verbindungen, der Leser bekam den vorigen Wert, ohne einen Augenblick zu blockieren. Was sich nicht ändert, ist die Zahl der Schreiber: weiterhin einer, und der zweite bekommt weiterhin eine 5.

Stichwörter: Transaktion, BEGIN, DEFERRED, IMMEDIATE, EXCLUSIVE, SAVEPOINT, ROLLBACK TO, SQLITE_BUSY, 5, 517, BUSY_SNAPSHOT, busy_timeout, WAL, journal_mode, Sperre, serialisierbar

Ein Pragma ist nicht eine einzige Sache: manche werden in die Datei geschrieben, manche halten so lange wie die Verbindung, und manche sind Befehle. Was wovon ist, die gemessenen Werkseinstellungen, und das eine, das aus ist und nicht sein sollte.

Gilt für: SQLite 3.35+

SQLite hat keine Konfigurationsdatei: es hat Pragmas. Und die erste Verwirrung, die man loswerden muss, ist, dass sie nicht alle dasselbe sind. Manche werden in die Datei geschrieben und gelten für jeden, der sie später öffnet; manche halten so lange wie die Verbindung und müssen jedes Mal wiederholt werden; und manche sind gar keine Einstellungen, sondern Befehle, die etwas tun und fertig.

Dies sind die Werkseinstellungen, aus einer frisch angelegten Datenbank gelesen.

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

Das eine, das aus ist und kaum jemand erwartet

foreign_keys steht auf 0. Fremdschlüssel werden deklariert, im Schema abgelegt, tauchen im CREATE TABLE auf … und werden nicht geprüft. Ein verwaistes Kind geht ohne einen Mucks hinein. Das Einschalten ist eine Zeile, aber es gehört zur Verbindung: es muss in jeder gesetzt werden, und das Einschalten blickt nicht zurück — dafür gibt es foreign_key_check, das aufzählt, was schon durchgerutscht ist.

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

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

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

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

Was bleibt und was nicht

journal_mode und user_version werden in den Dateikopf geschrieben und überleben Schließen und Öffnen. page_size und auto_vacuum auch, aber nur, wenn sie vor der ersten Tabelle gesetzt werden: auf einer Datenbank, die schon Seiten hat, werden sie ohne Fehler angenommen und ändern nichts, und das wurde in beiden Richtungen geprüft. foreign_keys, cache_size, busy_timeout und mmap_size gehören zur Verbindung und fallen auf ihren Werkswert zurück, sobald eine andere geöffnet wird.

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

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

Ein Detail, das verwirrt: nach dem Setzen von journal_mode = WAL stand synchronous auf 1 (NORMAL), ohne dass jemand es angefasst hätte. Das ist Absicht — in WAL reicht NORMAL, um nichts Bestätigtes zu verlieren — aber es zeigt, dass das Lesen eines Pragmas nicht verrät, woher dieser Wert stammt.

Und die, die Befehle sind

wal_checkpoint gießt das Journal in die Datenbank und lässt es mit TRUNCATE bei null Bytes: gemessen wurde ein -wal von 4 716 016 Bytes, das auf 0 fiel. ANALYZE füllt sqlite_stat1 mit dem, was der Planer zur Indexwahl heranzieht. Und PRAGMA optimize ist das, was man beim Schließen einer langlebigen Verbindung laufen lassen sollte: es sieht nach, welche Tabellen sich genug verändert haben, und stößt das nötige ANALYZE an, ohne etwas zu sagen.

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

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

Stichwörter: Pragma, Konfiguration, journal_mode, WAL, synchronous, foreign_keys, cache_size, mmap_size, auto_vacuum, page_size, user_version, wal_checkpoint, optimize, ANALYZE, sqlite_stat1

SCAN, SEARCH und COVERING sind das ganze Vokabular von EXPLAIN QUERY PLAN. Partielle und Ausdrucksindizes und die Bedingung, die man wiederholen muss, damit sie greifen, was WITHOUT ROWID spart und was ANALYZE schreibt.

Gilt für: SQLite 3.35+

In SQLite gibt es nur eine Art von Index: den B-Baum. Kein Hash, kein Bitmap, nichts zu wählen. Die Wortsuche gibt es, aber sie ist kein Index: sie heißt FTS5 und ist eine eigene virtuelle Tabelle. Das vereinfacht das ganze Thema, denn die einzige verbleibende Entscheidung ist, über welche Spalten und in welcher Reihenfolge.

Und geprüft wird mit EXPLAIN QUERY PLAN, dessen Vokabular in drei Wörter passt: SCAN heißt die ganze Tabelle lesen, SEARCH heißt über einen Index hineingehen, und COVERING heißt, dass die Zeile gar nicht angefasst wurde.

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

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

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

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

Der zusammengesetzte wird über Präfixe gelesen, wie in jeder Maschine: (cliente, estado) taugt für cliente allein und für beide zusammen, aber nicht für estado allein.

Abdeckend

Trägt der Index alle Spalten, die die Abfrage liest, wird die Zeile nicht angefasst. Es ist derselbe Index wie vorher: was sich ändert, ist das, was verlangt wird.

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

Partiell und Ausdruck: beide muss man wiederholen

Ein partieller Index indiziert nur die Zeilen, die eine Bedingung erfüllen, und braucht deshalb wenig Platz. Der Preis ist, dass die Abfrage diese Bedingung wiederholen muss, Wort für Wort, sonst kann der Planer ihn nicht nutzen: ohne sie ist dieselbe Abfrage wieder ein SCAN. Genauso beim Ausdrucksindex: er indiziert lower(correo), also muss lower(correo) im WHERE stehen; mit bloßem correo nützt er gar nichts.

CREATE INDEX i_parcial ON pedidos(total) WHERE estado = 'pagado';

EXPLAIN QUERY PLAN
  SELECT * FROM pedidos WHERE estado = 'pagado' AND total > 900;
-- SEARCH pedidos USING INDEX i_parcial (total>?)

EXPLAIN QUERY PLAN
  SELECT * FROM pedidos WHERE total > 900;
-- SCAN pedidos   <- sin repetir el filtro, el indice no existe

CREATE INDEX i_correo ON pedidos(lower(correo));

EXPLAIN QUERY PLAN
  SELECT * FROM pedidos WHERE lower(correo) = 'u42@ej.com';
-- SEARCH pedidos USING INDEX i_correo (<expr>=?)

WITHOUT ROWID nimmt eine Umleitung weg

Eine normale Tabelle legt ihre Zeilen unter einer verborgenen rowid ab, ihr Primärschlüssel ist also ein weiterer Index, der die Zeile anschließend holen muss. Mit WITHOUT ROWID ist die Tabelle der Baum ihres Primärschlüssels: der Sprung entfällt und Platz wird gespart — 1 335 296 Bytes gegen 1 675 264 bei derselben Tabelle mit 20 000 Zeilen, 20 % weniger —, und der Plan verrät es, indem er USING PRIMARY KEY sagt, statt einen automatischen Index zu nennen.

ANALYZE liefert Zahlen, keine Wunder

Es füllt sqlite_stat1 mit der Zeilenzahl und damit, wie viele es je Wert des Index gibt. Diese zweite Zahl sagt, ob ein Index etwas taugt: 20 000 je Wert heißt, er unterscheidet nichts. Aber die Wahl ändert sich nicht immer: in der Messung wählte der Planer schon vorher richtig, denn ohne Statistiken greift er auf vernünftige Annahmen zurück. ANALYZE nimmt das Raten weg; es verspricht keinen anderen Plan.

CREATE TABLE kv (k TEXT PRIMARY KEY, v TEXT) WITHOUT ROWID;
-- 20 000 filas: 1 335 296 bytes, frente a 1 675 264 con rowid

EXPLAIN QUERY PLAN SELECT v FROM kv WHERE k = 'clave-000042';
-- SEARCH kv USING PRIMARY KEY (k=?)
-- con rowid habria dicho:  USING INDEX sqlite_autoindex_kv_1 (k=?)

ANALYZE;
SELECT tbl, idx, stat FROM sqlite_stat1;
-- t | ia | 20000 20000    <- 20 000 filas, 20 000 por cada valor de a
-- t | ib | 20000 1        <- 20 000 filas, 1 por cada valor de b

Stichwörter: Index, Indizes, B-Baum, EXPLAIN QUERY PLAN, SCAN, SEARCH, COVERING INDEX, partieller Index, Ausdrucksindex, WITHOUT ROWID, ANALYZE, sqlite_stat1, FTS5

Dieselben 20 000 Einfügungen auf sechs Arten gemessen: der Unterschied zwischen der besten und der schlechtesten liegt in keinem Pragma, sondern darin, ob es ein BEGIN gibt. Und was WAL, VACUUM und die Seitengröße wirklich beitragen.

Gilt für: SQLite 3.35+

Es gibt nur eine Sache, auf die es ankommt, und sie ist kein Pragma. Dieselben 20 000 Einfügungen, dasselbe Schema, dieselbe Maschine:

440-mal, und die Erklärung ist, dass ohne BEGIN jedes INSERT seine eigene Transaktion ist: zwanzigtausend Bestätigungen, jede wartet auf die Platte.

-- 20 000 INSERT, cada uno con su propia transaccion:  3963 ms
-- los mismos 20 000 aqui dentro:                          9 ms

BEGIN;
  INSERT INTO t (v) VALUES ('...');   -- x 20 000
COMMIT;

Vorsicht vor einer Falle in der Zwischenschicht: dass der Treiber einen Aufruf für «Stapeleinfügung» anbietet, heißt nicht, dass er eine Transaktion öffnet. Pythons executemany brauchte ohne ausdrückliches BEGIN 4 128 ms: genau so viel wie die Schleife von Hand.

Was die anderen beitragen

synchronous abzuschalten sparte 38 % und bietet dafür an, bestätigte Daten bei einem Stromausfall zu verlieren: das schlechteste Geschäft der Liste. WAL ohne Transaktion ging auf 258 ms herunter — fünfzehnmal —, weil das Bestätigen die Datenbank nicht mehr neu schreibt, und das ist eine Änderung, die man stehen lassen kann. Aber mit beidem im Spiel holt sich das BEGIN fast alles: 9 ms ohne WAL und 10 mit. Zuerst wird gebündelt, und erst dann feinjustiert.

VACUUM ist das, was den Platz zurückgibt

Löschen verkleinert die Datei nicht: die Seiten bleiben auf einer Freiliste zur Wiederverwendung. Gemessen wurde eine Datei von 10 813 440 Bytes, von der die Hälfte der Zeilen gelöscht wurde und die exakt gleich groß blieb. VACUUM schreibt sie ganz neu und ließ sie bei 5 410 816. Es kostet eine Kopie der Datenbank und eine exklusive Sperre, es ist also keine nächtliche Aufgabe: es ist das, was man laufen lässt, wenn ein großes Löschen die Datei doppelt so groß zurückgelassen hat, wie sie sein müsste.

SELECT page_count * page_size
  FROM pragma_page_count(), pragma_page_size();   -- 10813440

DELETE FROM t WHERE i % 2 = 0;
PRAGMA freelist_count;   -- 1 pagina, y el archivo sigue igual de grande

VACUUM;                  -- 5410816 bytes, en 12 ms

Die Seitengröße muss man fast nie anfassen

Drei wurden gemessen. Sie auf 512 zu senken kostete 20 % mehr Datei und einen messbaren Durchlauf, wo die anderen beiden keine Millisekunde erreichten; sie auf 65 536 zu erhöhen brachte nichts. Die ab Werk — 4 096 — ist die, die man lassen sollte, und außerdem lässt sie sich nur vor der ersten Tabelle ändern oder nachträglich über ein VACUUM.

PRAGMA page_size = 512;     -- 5215744 bytes, y el recorrido en 3 ms
PRAGMA page_size = 4096;    -- 4333568 bytes, y en 0 ms   <- el de fabrica
PRAGMA page_size = 65536;   -- 4390912 bytes, y en 0 ms

Und zwei weitere Dinge, die sich selbst messen

Jeder Index wird bei jedem Schreibvorgang bezahlt: dieselben 20 000 Zeilen dauerten 7 ms ohne eigene Indizes, 12 mit einem, 16 mit zweien und 20 mit dreien, und die Datei wuchs von 458 752 auf 1 277 952 Bytes. Und eine Anweisung mit Parameter wird wiederverwendet: 5 000 Abfragen mit ? dauerten 18 ms, dieselben mit dem Wert im SQL eingeklebt 26 — abgesehen davon, dass dort die Injektion hereinkommt.

Stichwörter: Leistung, Transaktion, BEGIN, COMMIT, Stapel, executemany, synchronous, WAL, VACUUM, freelist_count, page_size, Fragmentierung, vorbereitete Anweisung

Was ALTER TABLE kann, passt in vier Zeilen, und was es beim Hinzufügen und Entfernen einer Spalte ablehnt, ist samt Meldung gemessen. Der Umweg aus Anlegen, Kopieren, Löschen und Umbenennen, und warum er hier sicher ist.

Gilt für: SQLite 3.35+

ALTER TABLE kann vier Dinge, und nicht mehr. Den Typ einer Spalte ändern, ihr ein NOT NULL nehmen, einen Fremdschlüssel hinzufügen: nichts davon gibt es, und der Versuch kommt nicht einmal bis zu einem Schemafehler, er ist ein Syntaxfehler.

ALTER TABLE t RENAME TO t2;            -- desde siempre
ALTER TABLE t RENAME COLUMN a TO a2;   -- 3.25
ALTER TABLE t ADD COLUMN g TEXT;       -- desde siempre
ALTER TABLE t DROP COLUMN b;           -- 3.35

ALTER TABLE t ALTER COLUMN g TYPE INTEGER;
-- near "ALTER": syntax error   <- no existe, y nunca ha existido

Was ADD COLUMN ablehnt

Die drei Absagen haben dieselbe Ursache: die neue Spalte wird hinzugefügt, ohne die schon vorhandenen Zeilen anzufassen, der Wert, den sie bekommen, muss sich also entscheiden lassen, ohne sie anzusehen. Ein DEFAULT, der sich ändert, ein UNIQUE, das geprüft werden müsste, und ein NOT NULL ohne Wert erfüllen das nicht.

ALTER TABLE t ADD COLUMN i TEXT DEFAULT (datetime('now'));
-- Cannot add a column with non-constant default

ALTER TABLE t ADD COLUMN j TEXT UNIQUE;
-- Cannot add a UNIQUE column

ALTER TABLE t ADD COLUMN k TEXT NOT NULL;
-- Cannot add a NOT NULL column with default value NULL

Was DROP COLUMN ablehnt — und schlimmer: was es erlaubt

Seit 3.35 lässt sich eine Spalte entfernen, aber nicht, wenn sie zum Primärschlüssel gehört, nicht wenn sie UNIQUE ist, und nicht, wenn ein Index oder eine generierte Spalte sie nennt. So weit, so gut. Das Problem ist der Fall, den es durchlässt: eine Spalte, die eine Sicht benutzt, wird ohne einen Mucks entfernt, die Sicht bleibt kaputt zurück, und PRAGMA integrity_check sagt weiterhin ok, weil es nicht in Sichten hineinschaut. Niemand warnt, bis jemand abfragt.

ALTER TABLE t DROP COLUMN id;   -- cannot drop PRIMARY KEY column: "id"
ALTER TABLE t DROP COLUMN e;    -- cannot drop UNIQUE column: "e"
ALTER TABLE t DROP COLUMN c;    -- error in index i_c after drop column

CREATE VIEW v AS SELECT id, d FROM t;
ALTER TABLE t DROP COLUMN d;    -- PASA, sin una queja
SELECT * FROM v;                -- no such column: d
PRAGMA integrity_check;         -- ok

Der übliche Umweg

Für alles Übrige lautet das Verfahren: neue Tabelle anlegen, kopieren, die alte löschen und umbenennen. Es klingt gefährlich und ist es hier nicht, wegen etwas, das MySQL nicht hat: das DDL von SQLite ist transaktional. Gemessen: ein CREATE TABLE und ein ADD COLUMN in einem BEGIN, mit ROLLBACK am Ende, hinterließen weder die Tabelle noch die Spalte. Der ganze Umweg passt also in eine Transaktion, und wenn auf halbem Weg etwas schiefgeht, bleibt nichts halb fertig.

PRAGMA foreign_keys = OFF;
BEGIN;
  CREATE TABLE t_nueva (id INTEGER PRIMARY KEY, a INTEGER NOT NULL);
  INSERT INTO t_nueva (id, a) SELECT id, CAST(a AS INTEGER) FROM t;
  DROP TABLE t;
  ALTER TABLE t_nueva RENAME TO t;
  -- y aqui se vuelven a crear indices, disparadores y vistas
COMMIT;
PRAGMA foreign_key_check;
PRAGMA foreign_keys = ON;

Drei Vorsichtsmaßnahmen. Indizes, Trigger und Sichten der alten Tabelle gehen mit ihr und müssen neu angelegt werden, denn das DROP TABLE nimmt sie mit. Fremdschlüssel werden während des Umwegs abgeschaltet und mit foreign_key_check geprüft, bevor man sie wieder einschaltet. Und die Typumwandlung ist deine Sache: ein CAST('a' AS INTEGER) liefert 0, ohne ein Wort der Warnung.

Stichwörter: DDL, ALTER TABLE, RENAME TO, RENAME COLUMN, ADD COLUMN, DROP COLUMN, 3.25, 3.35, kaputte Sicht, integrity_check, transaktionales DDL, zwölf Schritte, CAST

Eine Datenbank im WAL-Modus besteht aus drei Dateien, und die Daten liegen fast nie in der ersten: sie zu kopieren hinterlässt eine leere Datenbank, die sich für gesund erklärt. Die drei Wege, die funktionieren, gemessen, und was jede Integritätsprüfung findet.

Gilt für: SQLite 3.35+

Eine SQLite-Datenbank sieht aus wie eine Datei, und da fängt der Ärger an. Im WAL-Modus sind es drei, und die mit dem Namen trägt womöglich gar keine Daten: nach dem Schreiben von 30 000 Zeilen maß die .sqlite 4 096 Bytes — den Kopf und wenig mehr — und die -wal maß 3 366 072.

Nur die erste zu kopieren ergibt keine kaputte Datenbank. Es ergibt etwas Schlimmeres: eine, die klaglos öffnet, keine einzige Tabelle hat, und der PRAGMA integrity_check ein ok gibt. So eine Sicherung besteht jede Prüfung und enthält nichts.

PRAGMA journal_mode = WAL;
-- tras 30 000 filas, los tres archivos miden:
--   base.sqlite         4096   <- solo la cabecera
--   base.sqlite-shm    32768
--   base.sqlite-wal  3366072   <- aqui estan los datos

-- copiar solo base.sqlite da una base que abre, y esta VACIA:
SELECT count(*) FROM sqlite_schema;   -- 0
PRAGMA integrity_check;               -- ok

Die drei Wege, die funktionieren

VACUUM INTO schreibt eine saubere, entfragmentierte Kopie in eine andere Datei, bei laufender Datenbank: 3 338 240 Bytes in 4 ms, mit den 30 000 Zeilen. Die Backup-API — das .backup der Befehlszeile und Connection.backup in den Treibern — macht dasselbe, indem sie Seiten kopiert, und kann in Raten arbeiten: 3 338 240 Bytes in 3 ms. Und .dump schreibt das SQL, das die Datenbank wieder aufbaut: 4 008 968 Bytes Text und 30 003 Anweisungen, 20 % mehr als das Binärformat, aber als Einziges mit den Augen lesbar und als Einziges überlebt es einen Formatwechsel.

VACUUM INTO '/ruta/copia.sqlite';
-- 3338240 bytes en 4 ms, con las 30 000 filas dentro
-- y la copia sale en journal_mode delete, no en WAL

Ein willkommenes Detail: die Kopie von VACUUM INTO kommt im journal_mode delete heraus, nicht in WAL. Es ist eine einzige Datei, und genau das will man von einer Sicherung.

Alle drei Dateien auf einmal zu kopieren, bei angehaltener Datenbank, funktioniert schon. Das Problem sind «auf einmal» und «angehalten»: solange jemand schreibt, gibt es keinen Augenblick, in dem die drei zusammenpassen, und kein Kopierwerkzeug garantiert einen.

Prüfen, was man hat

integrity_check durchläuft die ganze Datenbank, quick_check überspringt die Querprüfungen zwischen Indizes und Tabellen. Auf einer gesunden Datenbank sagten beide ok, und auf derselben Datenbank mit ein paar Hundert absichtlich zerstörten Bytes sagten beide genau dasselbe: Tree 2 page 4 cell 35: Rowid 0 out of order. Der Unterschied zeigt sich erst bei einer großen Datenbank, und keiner von beiden repariert etwas: sie dienen der Entscheidung, ob man zur Sicherung zurückkehrt.

PRAGMA quick_check;       -- ok
PRAGMA integrity_check;   -- ok

-- con la misma base danada a proposito, los dos contestan igual:
-- *** in database main ***
-- Tree 2 page 4 cell 35: Rowid 0 out of order

Und wenn es schon zu spät ist

Ein .dump wiederherzustellen heißt, sein SQL gegen eine leere Datenbank laufen zu lassen. Eine binäre Kopie wiederherzustellen heißt, sie an ihren Platz zu legen, und dabei hilft zu wissen, dass VACUUM INTO sich weigert zu überschreiben: auf eine schon vorhandene Datei antwortet es «output file already exists», man kann also die Sicherung von gestern nicht versehentlich zertreten. Und wenn man eine beschädigte Datenbank und keine Sicherung hat, bleibt .recover der Befehlszeile, das die noch verständlichen Seiten durchgeht und das SQL schreibt, um zu retten, was zu retten ist: es verspricht nicht alles, es verspricht den Rest.

Stichwörter: Sicherung, Backup, VACUUM INTO, Backup-API, dump, WAL, -wal, -shm, integrity_check, quick_check, Beschädigung, Wiederherstellung

Die acht, die wirklich auftauchen, einzeln provoziert samt Text, und die Arithmetik des erweiterten Codes: die 19 der Einschränkung wird zu 275, 787, 1299, 1555 oder 2067, je nachdem, was verletzt wurde.

Gilt für: SQLite 3.35+

SQLite hat zwei Satz Codes: einen einfachen mit ein oder zwei Ziffern und einen erweiterten, der dasselbe genauer sagt. Und die Beziehung zwischen beiden ist Arithmetik: der erweiterte ist der einfache plus 256 mal dem Untertyp, code & 255 gibt also immer den einfachen zurück. Ein Treiber, der nur den einfachen zeigt, verschweigt die Hälfte.

Die 19 sind fünf verschiedene Fehler

Der einfache sagt nichts Brauchbares, denn eine verletzte Einschränkung kann jede von fünf sein. Der erweiterte schon, und die Meldung hilft … mit einer Ausnahme: der Primärschlüssel eines INTEGER PRIMARY KEY gibt 1555, sein Text aber sagt «UNIQUE constraint failed». Dort ist die Zahl also genauer als der Satz.

CREATE TABLE t (id INTEGER PRIMARY KEY, u TEXT UNIQUE, nn TEXT NOT NULL,
                ch INTEGER CHECK (ch > 0), p INTEGER REFERENCES padre(id));

INSERT INTO t VALUES (1,'b','x', 5,   1);   -- 1555  UNIQUE constraint failed: t.id
INSERT INTO t VALUES (2,'a','x', 5,   1);   -- 2067  UNIQUE constraint failed: t.u
INSERT INTO t VALUES (3,'c',NULL,5,   1);   -- 1299  NOT NULL constraint failed: t.nn
INSERT INTO t VALUES (4,'d','x',-1,   1);   --  275  CHECK constraint failed: ch > 0
INSERT INTO t VALUES (5,'e','x', 5, 999);   --  787  FOREIGN KEY constraint failed

Die 5 und die 6 werden verwechselt und sind nicht dasselbe

Die 5 kommt von außen: eine andere Verbindung schreibt, und es hilft zu warten — dort verdient busy_timeout sein Geld. Die 6 kommt von innen: dieselbe Verbindung hat einen Cursor auf der Tabelle offen, die sie ändern will, und Warten nützt nichts, denn wer blockiert, bist du. Es hilft, den Cursor zu schließen.

PRAGMA max_page_count = 20;
INSERT INTO t ... ;      -- 13  SQLITE_FULL      database or disk is full

-- con la base abierta en modo solo lectura:
INSERT INTO t ... ;      --  8  SQLITE_READONLY  attempt to write a readonly database

-- con un cursor de SELECT todavia abierto, en la MISMA conexion:
DROP TABLE t;            --  6  SQLITE_LOCKED    database table is locked

Die 13 ist fast nie die Platte

SQLITE_FULL klingt nach voller Partition und ist oft die Grenze, die die Datenbank sich selbst gesetzt hat: max_page_count. Mit 20 Seiten provoziert man sie in einer Zeile, und die Meldung ist dieselbe, die eine wirklich volle Platte gäbe: «database or disk is full».

Die 11, die 26 und die, die man nie sieht

Die beiden Codes für eine kaputte Datei unterscheiden sich darin, wo der Schaden sitzt: ergibt eine Seite keinen Sinn, SQLITE_CORRUPT mit «database disk image is malformed»; ergibt der Kopf keinen Sinn, versucht es gar nicht erst und sagt SQLITE_NOTADB. Und die 21 ist die seltsame: die API in einer unmöglichen Reihenfolge aufzurufen. Man sieht sie kaum, weil der jeweilige Treiber sie vorher abfängt und einen eigenen Fehler wirft; in Python etwa kommt ein ProgrammingError heraus, der nicht einmal einen SQLite-Code trägt.

-- con la pagina del esquema machacada:
SELECT count(*) FROM t;   -- 11  SQLITE_CORRUPT  database disk image is malformed

-- con el tamano de pagina de la cabecera machacado:
SELECT count(*) FROM t;   -- 26  SQLITE_NOTADB   file is not a database

Stichwörter: Fehler, Codes, SQLITE_BUSY, 5, SQLITE_LOCKED, 6, SQLITE_READONLY, 8, SQLITE_CORRUPT, 11, SQLITE_FULL, 13, SQLITE_CONSTRAINT, 19, 275, 787, 1299, 1555, 2067, SQLITE_MISUSE, 21, SQLITE_NOTADB, 26

Die Grenzen von SQLite gehören nicht dem Format, sondern dem Binärprogramm, und man liest sie mit PRAGMA compile_options. Die acht, an die man wirklich stößt, einzeln provoziert, und die eine, die man kommentarlos überschreitet.

Gilt für: SQLite 3.35+

Die Grenzen von SQLite haben eine Besonderheit, die kein anderes System hat: sie gehören nicht dem Format, sondern dem Binärprogramm, mit dem man gerade spricht. Sie werden beim Übersetzen festgelegt, und deshalb beginnt die Antwort auf «Wie viel ist das Maximum?» mit PRAGMA compile_options, das sie alle zeigt. Ein Programm kann sie zur Laufzeit mit sqlite3_limit außerdem senken, nie anheben.

Dies sind die des Binärprogramms, das macOS mitbringt, und die ersten fünf wurden provoziert.

CREATE TABLE b (c0, c1, ... , c2000);
-- too many columns on b

SELECT 1 UNION ALL SELECT 1 UNION ALL ... ;   -- 501 veces
-- too many terms in compound SELECT

ATTACH DATABASE 'x11.sqlite' AS a11;
-- too many attached databases - max 10

Die, die sich nicht meldet

PRAGMA page_size = 131072 gibt keinen Fehler, liefert nichts Merkwürdiges und ändert nichts: die Seite bleibt bei 4 096. Das Maximum ist 65 536, und was darüber verlangt wird, wird stillschweigend verworfen; die einzige Möglichkeit zu wissen, ob es griff, ist Nachlesen. Es ist dieselbe Fehlerart, die page_size und auto_vacuum auf einer Datenbank mit Tabellen schon haben: angenommen und wirkungslos.

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

Wie viel wirklich hineinpasst

Die maximale Dateigröße ist keine Konstante: sie ist max_page_count mal Seitengröße. Mit den Werkseinstellungen — 1 073 741 823 Seiten zu 4 096 Bytes — kommen 4 TiB heraus, und mit einer Seite von 65 536 64 TiB. Lange bevor man dorthin kommt, geht etwas anderes aus: ein Text kommt nicht über 1 000 000 000 Bytes, und ein SELECT mit mehr als 250 000 Parametern lässt sich nicht einmal vorbereiten.

Und eine Warnung zur Tabelle oben: sie gehört diesem Binärprogramm. Das auf einem Telefon, das in einer eingebetteten Bibliothek oder eines, das jemand mit eigenen Flags übersetzt hat, können andere Zahlen tragen, und deshalb ist die nützliche Antwort nie der Wert: es ist der Befehl, der danach fragt.

PRAGMA compile_options;
-- MAX_COLUMN=2000          MAX_COMPOUND_SELECT=500   MAX_ATTACHED=10
-- MAX_LENGTH=1000000000    MAX_PAGE_SIZE=65536       MAX_EXPR_DEPTH=1000
-- MAX_VARIABLE_NUMBER=250000                         MAX_FUNCTION_ARG=1000

PRAGMA max_page_count;   -- 1073741823
-- x 4096 de pagina = 4 TiB de archivo; x 65536 = 64 TiB

Sie zu senken ist eine Verteidigung

Dass sqlite3_limit nur senken kann, ist kein Mangel: dafür ist es da. Eine Anwendung, die von jemand anderem geschriebenes SQL annimmt, senkt LENGTH, COMPOUND_SELECT und EXPR_DEPTH auf das, was sie wirklich braucht, und damit kann eine feindselige Abfrage kein Gigabyte Speicher mehr anfordern. Es ist dieselbe Idee wie das max_page_count im Fehlerthema: die selbst gesetzte Grenze meldet sich früher als die des Systems, und sie meldet etwas, das man beheben kann.

Stichwörter: Grenzen, Obergrenzen, compile_options, MAX_COLUMN, MAX_COMPOUND_SELECT, MAX_ATTACHED, MAX_LENGTH, MAX_PAGE_SIZE, max_page_count, sqlite3_limit, Maximalgröße, Spalten, ATTACH

Die Zusammenfassung des SQLite-Handbuchs: sieben Gewohnheiten mit der Messung dahinter, vier davon in den Zeilen direkt nach dem Öffnen der Verbindung, und die sechs, mit denen man einer fremden Datei den Puls fühlt.

Gilt für: SQLite 3.35+

Dies ist das Ende des Handbuchs, und es bringt nichts Neues: es sammelt, was jedes Thema gemessen hinterlassen hat. Auffällig ist, wo vier der sieben landen: in den Zeilen, die man direkt nach dem Öffnen der Verbindung schreibt und die fast kein Programm schreibt.

1. PRAGMA journal_mode = WAL. Es bleibt in der Datei stehen, einmal genügt also. Damit las ein Leser weiter, während ein anderer schrieb, ohne einen Augenblick zu blockieren. Was es nicht behebt, ist die Zahl der Schreiber: weiterhin einer.

2. PRAGMA foreign_keys = ON, in jeder Verbindung. Es ist das Einzige auf dieser Liste, das ändert, was die Datenbank annimmt, und es kommt ausgeschaltet: ausgeschaltet geht ein verwaistes Kind ohne einen Mucks hinein. Und es bleibt nicht stehen, es gehört also an dieselbe Stelle wie das busy_timeout.

3. PRAGMA busy_timeout, und von dir gesetzt. Die Maschine liefert es mit 0 — sie antwortet auf der Stelle SQLITE_BUSY —, aber viele Treiber ändern es beim Verbinden: der von Python lässt es kommentarlos bei 5 000. Die Zahl, auf die es ankommt, ist also nicht die aus der Dokumentation: es ist die, die PRAGMA busy_timeout auf deiner Verbindung zurückgibt.

4. Bündle die Schreibvorgänge in einer Transaktion. Das ändert mit Abstand am meisten: dieselben 20 000 Einfügungen dauerten einzeln 3 963 ms und in einem BEGIN 9 ms. Und Vorsicht mit der Zwischenschicht: ein «Stapeleinfügung»-Aufruf des Treibers öffnet von sich aus keine Transaktion.

PRAGMA journal_mode;    -- wal, o delete si nadie lo ha tocado
PRAGMA foreign_keys;    -- 0 casi siempre, y casi siempre es un error
PRAGMA synchronous;     -- 2 con diario, 1 en WAL
PRAGMA busy_timeout;    -- el de TU conexion, no el del motor
PRAGMA page_count;      -- x page_size = lo que ocupa
PRAGMA quick_check;     -- ok

5. Sichere mit VACUUM INTO, nie durch Kopieren der Datei. Im WAL-Modus liegen die Daten im -wal, das Kopieren der .sqlite ergibt also eine Datenbank, die öffnet, keine einzige Tabelle hat und der integrity_check ein ok gibt. Eine Sicherung, die jede Prüfung besteht und leer ist, ist schlimmer als gar keine.

6. Ein Index pro häufiger Abfrage, und sieh nach, was er wiegt. dbstat sagt es je Objekt, und es überrascht: in der gemessenen Datenbank nahm der Index 2 056 192 Bytes gegenüber 1 826 816 der Tabelle, die er indizierte.

SELECT name, SUM(pgsize) AS bytes
  FROM dbstat
 GROUP BY name
 ORDER BY bytes DESC;
-- iv             2056192   <- el indice pesa mas que la tabla
-- t              1826816
-- sqlite_schema     4096

7. Die Sicherheit gehört der Datei. Es gibt keine Benutzer, keine Rollen, kein GRANT: wer die Datei lesen kann, kann alles lesen, und wer sie schreiben kann, kann sie löschen. Der Schutz sind die Berechtigungen des Systems, die Verschlüsselung der Platte und — auf einem Telefon — die Datenschutzklasse. Alles andere in diesem Handbuch ist Leistung; dies ist das Einzige, wofür es keinen Ersatz gibt.

Und eine, die keine Gewohnheit ist, sondern eine Grenze

SQLite hält weit mehr aus, als sein Ruf nahelegt, aber es hat eine Grenze, die keine Praxis verschiebt: es schreibt einer nach dem anderen. Solange die Schreibvorgänge aus einem Prozess kommen oder aus mehreren, die sich abwechseln, reicht die Datei bis zu Grenzen, an die kaum jemand stößt. An dem Tag, an dem zwei echte gleichzeitige Schreiber gebraucht werden, ist nicht ein Pragma zu ändern: es ist die Maschine.

Stichwörter: bewährte Praxis, Zusammenfassung, WAL, foreign_keys, busy_timeout, gebündelte Transaktion, VACUUM INTO, Sicherung, dbstat, Indizes, Berechtigungen, Verschlüsselung, Sicherheit

Minimale Rechte, Rollen, verschlüsselte Verbindungen und die Checkliste vor dem Freigeben eines Servers.

Gilt für: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

In MySQL und MariaDB besteht die Identität eines Benutzers aus zwei Dingen: dem Namen und dem Host, von dem er sich verbindet. 'app'@'10.0.%' und 'app'@'%' sind verschiedene Konten mit verschiedenen Passwörtern und Rechten. Die meisten Sicherheitsschrecken fangen damit an, das zu vergessen.

Minimale Rechte
Gewähre, was die Anwendung nutzt, keins mehr, und auf dem engstmöglichen Host. Ein Anwendungskonto braucht kaum je DROP und niemals SUPER, FILE oder GRANT OPTION:

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

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

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

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

Rollen

CREATE ROLE 'lectura', 'escritura';

GRANT SELECT ON tienda.* TO 'lectura';
GRANT INSERT, UPDATE, DELETE ON tienda.* TO 'escritura';

GRANT 'lectura' TO 'informes'@'%';
GRANT 'lectura', 'escritura' TO 'app'@'10.0.%';

SET DEFAULT ROLE ALL TO 'app'@'10.0.%';

Verschlüsselte Verbindungen
Ohne TLS wandern Passwort und Daten lesbar durchs Netz. Man kann es je Konto oder für den ganzen Server über require_secure_transport verlangen. Calíope unterstützt TLS im Verbindungsprofil und ebenso SSH-Tunnel, wenn der Server nicht offen liegt:

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

SHOW VARIABLES LIKE 'require_secure_transport';

SELECT user, host, ssl_type FROM mysql.user;

Schnelles Audit
Drei Abfragen, die man auf jedem geerbten Server einmal laufen lassen sollte. Konten ohne Passwort, Konten für jeden Host offen und verteilte gefährliche Rechte:

SELECT user, host FROM mysql.user WHERE authentication_string = '';

SELECT user, host FROM mysql.user WHERE host = '%';

SELECT * FROM information_schema.USER_PRIVILEGES
WHERE privilege_type IN ('SUPER', 'FILE', 'PROCESS', 'GRANT OPTION');

Bevor ein Server nach außen geht
1. Keine anonymen und keine passwortlosen Konten, und keine Beispiel-Datenbank test.
2. root nur von localhost, mit einem getrennten Administrationskonto für alles Weitere.
3. bind-address auf der richtigen Schnittstelle — nicht 0.0.0.0, wenn von außen niemand hinsoll.
4. TLS verpflichtend für jede Verbindung, die die Maschine verlässt.
5. Passwörter außerhalb des Codes verwalten — Calíope legt sie im Schlüsselbund ab, nie im Klartext.
6. Getrennte Konten je Anwendung, damit ein Einbruch nicht alles mitreißt.
7. Die GRANTs regelmäßig durchsehen: Rechte häufen sich an, und niemand nimmt sie zurück.

Empfehlung
Fang mit Entziehen an statt mit Gewähren: leg das Konto ohne alles an und füge Rechte hinzu, bis die Anwendung läuft. Das Benutzer-Werkzeug von Calíope zeigt die wirksamen Rechte je Datenbank und je Tabelle — dort tauchen die Überraschungen meist auf.

Stichwörter: sicherheit, benutzer, rolle, privileg, grant, revoke, minimale rechte, ssl, tls, require ssl, härtung, mysql.user, user_privileges, audit

Logisch gegen physisch, wozu das Binlog dient und wie man zur Minute vor dem DELETE zurückkommt.

Gilt für: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Eine Sicherung, die nie zurückgespielt wurde, ist keine Sicherung, sondern eine Absicht. Zwei Zahlen bestimmen hier alles: das RPO (wie viele Daten du zu verlieren bereit bist) und das RTO (wie lange du ausfallen darfst). Alles Weitere folgt daraus.

Logisch gegen physisch
- Logisch (mysqldump, die Sicherung von Calíope) — erzeugt SQL. Übertragbar zwischen Versionen und Engines, erlaubt das Zurückholen einer einzelnen Tabelle und ist bei großen Mengen langsam wiederherzustellen.
- Physisch (Volume-Snapshot, Percona XtraBackup, Kopie des Verzeichnisses bei gestopptem Server) — kopiert die Dateien. Blitzschnell zurückzuspielen, aber an Version und Architektur des Servers gebunden.

Faustregel: bis zu einigen Dutzend Gigabyte logisch; darüber physisch für die Vollkopie und logisch für einzelne Teile.

Das Binlog ist die fehlende Hälfte
Die Sicherung bringt dich zu dem Moment zurück, in dem sie entstand. Das Binary Log enthält alles, was danach geschah, und erst damit kommst du von dort bis eine Sekunde vor die Katastrophe. Ohne aktives log_bin gibt es kein Point-in-Time-Recovery, nur die Rückkehr zur letzten Kopie:

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

SHOW BINARY LOGS;

Auf einen Zeitpunkt zurückholen
Der Ablauf, immer auf einem getrennten Server und nie auf dem produktiven:
1. Spiel die jüngste Vollkopie von vor dem Vorfall zurück.
2. Finde den genauen Moment des Fehlers im Binlog: die Anweisung, die zu viel gelöscht hat, und ihre Position oder ihren Zeitstempel.
3. Spiel das Binlog ab von der Position, an der die Kopie endete, bis kurz vor diese Anweisung — mit mysqlbinlog und seinen Optionen --start-position und --stop-position (oder --start-datetime und --stop-datetime).
4. Prüf, dass die Daten da sind, und entscheide erst dann, ob du diesen Server hochziehst oder das Fehlende daraus exportierst.

Der Binlog-Betrachter von Calíope ist für Schritt 2 da: er filtert Ereignisse nach Datum, Datenbank und Operationsart — genau das, was von Hand mühsam ist.

Die Position finden
SHOW MASTER STATUS nennt Datei und Position von jetzt; die Ereignisse eines bestimmten Binlogs listet man so:

SHOW MASTER STATUS;

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

Die Wiederherstellung prüfen
Zurückspielen ohne Prüfen ist der übliche Weg, das Problem zu spät zu bemerken. Eine Zählung je Datenbank und ein CHECKSUM TABLE der kritischen Tabellen gegen die Quelle reichen, um ruhig zu schlafen:

SELECT table_schema, COUNT(*) AS tablas, SUM(table_rows) AS filas
FROM information_schema.TABLES
WHERE table_type = 'BASE TABLE'
GROUP BY table_schema;

CHECKSUM TABLE pedidos, lineas;

Empfehlung
Plane die Sicherung ein (Calíope kann das, mit einstellbarer Aufbewahrung), halte eine Kopie außerhalb der Maschine, aktiviere log_bin mit einer Aufbewahrung über mindestens zwei Sicherungszyklen, und probe mindestens einmal eine vollständige Wiederherstellung. Der Tag des Vorfalls ist nicht der Tag, an dem man das Verfahren lernt.

Stichwörter: sicherung, backup, wiederherstellung, pitr, zeitpunkt, binlog, mysqldump, mysqlbinlog, rpo, rto, checksum table, snapshot

ALGORITHM, LOCK, Metadatensperren und wann ein externes Werkzeug nötig wird.

Gilt für: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Ein ALTER TABLE auf einer großen Tabelle kann Stunden dauern und die Anwendung warten lassen. Seit MySQL 5.6 und MariaDB 10.0 lässt sich vorgeben, wie die Änderung zu geschehen hat — und damit vorher wissen, ob es wehtun wird.

Den Algorithmus verlangen, nicht auf Glück hoffen
Gibst du den Algorithmus an und der Server kann ihn nicht verwenden, scheitert die Anweisung sofort, statt dir die Tabelle drei Stunden zu sperren. Das ist der Hauptgrund, ihn immer hinzuschreiben:

ALTER TABLE pedidos
    ADD COLUMN nota VARCHAR(255) NULL,
    ALGORITHM=INSTANT;

ALTER TABLE pedidos
    ADD INDEX idx_fecha (fecha),
    ALGORITHM=INPLACE, LOCK=NONE;

ALTER TABLE pedidos
    MODIFY COLUMN total DECIMAL(12,2) NOT NULL,
    ALGORITHM=COPY, LOCK=SHARED;

Die LOCK-Klausel
- LOCK=NONE — Lesen und Schreiben laufen während der Änderung weiter. Geht das nicht, Fehler.
- LOCK=SHARED — Lesen erlaubt, Schreiben nicht.
- LOCK=EXCLUSIVE — niemand fasst die Tabelle an.

LOCK=NONE anzugeben ist die Garantie, dass die Migration die Produktion nicht anhält: entweder läuft sie ohne Sperre, oder sie läuft nicht.

Die Metadatensperre, die alle überrascht
Selbst ein sofortiges ALTER braucht am Anfang und am Ende eine exklusive Metadatensperre. Liegt eine alte Transaktion offen auf dieser Tabelle, wartet das ALTER — und jede danach eintreffende Abfrage stellt sich dahinter an. Eine Tabelle friert wegen eines ALTER ein, das eine Millisekunde dauern sollte. Bevor du das Schema anfasst, prüf, ob lange Transaktionen laufen:

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

SELECT @@lock_wait_timeout;

Den Fortschritt sehen
Ein stundenlanges ALTER gibt von sich aus kein Lebenszeichen. performance_schema schon:

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

SHOW PROCESSLIST;

Wann ein externes Werkzeug nötig wird
Erzwingt die Änderung ALGORITHM=COPY auf einer Tabelle von zig Gigabyte, rettet dich kein LOCK. Da kommen pt-online-schema-change (Percona) und gh-ost (GitHub) ins Spiel: sie legen eine neue Tabelle an, kopieren stapelweise, halten sie per Trigger oder über das Binlog synchron und tauschen am Ende in einem Augenblick. Sie kommen nicht mit dem Server; sie werden getrennt installiert und von der Kommandozeile ausgeführt.

Empfehlung
Schreib in deinen Migrationen immer ALGORITHM= und LOCK=, und probier sie vorher auf einer Kopie mit echten Daten aus, um die Dauer zu kennen. Ein ALTER, das nach einer Sekunde scheitert, ist eine gute Nachricht gegenüber einem, das die Tabelle mitten am Vormittag sperrt.

Stichwörter: online ddl, alter table, algorithm, instant, inplace, copy, lock=none, metadatensperre, mdl, pt-online-schema-change, gh-ost, schemamigration

Warum utf8 nicht UTF-8 ist, was eine Kollation entscheidet und wie man ohne kaputte Indizes umstellt.

Gilt für: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Zwei Begriffe, die ständig verwechselt werden: der Zeichensatz sagt, welche Zeichen gespeichert werden können, und die Kollation sagt, wie sie verglichen und sortiert werden. Das erste bestimmt, was hineinpasst; das zweite, was ein WHERE zurückgibt.

utf8 ist nicht UTF-8
In MySQL ist utf8 ein historischer Alias für utf8mb3: nur drei Bytes je Zeichen, also keine Emoji und ein guter Teil des modernen Chinesisch, Japanisch und Koreanisch fällt weg. Echtes UTF-8 heißt utf8mb4. Das ist die meistgestellte Falle des Produkts, und in MySQL 8.0 lebt sie aus Kompatibilitätsgründen weiter. Prüf, wo du stehst:

SELECT default_character_set_name, default_collation_name
FROM information_schema.SCHEMATA
WHERE schema_name = DATABASE();

SELECT table_name, column_name, character_set_name, collation_name
FROM information_schema.COLUMNS
WHERE table_schema = DATABASE() AND character_set_name IS NOT NULL
  AND character_set_name <> 'utf8mb4';

SHOW VARIABLES LIKE 'character_set%';

Was eine Kollation entscheidet
Der Name sagt alles, wenn man ihn lesen kann. In utf8mb4_0900_ai_ci: 0900 ist die Unicode-Version, ai heißt akzentunempfindlich und ci unempfindlich gegenüber Groß- und Kleinschreibung. Die Gegenstücke sind as (akzentempfindlich) und cs (schreibungsempfindlich). Es gibt außerdem utf8mb4_bin, das Byte für Byte vergleicht und von Sprachen nichts weiß.

Die Vorgaben unterscheiden sich: MySQL 8.0 nutzt utf8mb4_0900_ai_ci, MariaDB je nach Version utf8mb4_general_ci oder utf8mb4_uca1400_ai_ci. Wenn du Daten zwischen beiden bewegst, setz nicht voraus, dass sie gleich sortieren.

Was sich praktisch ändert
Mit einer ai_ci-Kollation sind café und cafe derselbe Wert: ein UNIQUE weist den zweiten ab, und ein WHERE findet beide. Für die Namenssuche kann das genau richtig sein, für gespeicherte Bezeichner eine Katastrophe:

SELECT 'cafe' = 'café' COLLATE utf8mb4_0900_ai_ci AS acentos_iguales,
       'Ana'  = 'ana'  COLLATE utf8mb4_0900_ai_ci AS mayusculas_iguales;

SELECT * FROM clientes
WHERE nombre = 'jose' COLLATE utf8mb4_0900_as_cs;

SHOW COLLATION WHERE charset = 'utf8mb4';

Kollationen zu mischen tut weh
Ein JOIN zwischen einer Spalte in utf8mb4_general_ci und einer in utf8mb4_0900_ai_ci gibt den Fehler 1267 Illegal mix of collations. Und flickst du das, indem du die Spalte in CONVERT() oder ein COLLATE packst, kann die Abfrage den Index dieser Spalte nicht mehr nutzen. Die richtige Reparatur ist nicht das COLLATE in der Abfrage, sondern eine einheitliche Kollation im Schema.

Umstellen ohne Überraschungen
ALTER DATABASE ändert nur den Standard für künftige Tabellen; die vorhandenen musst du einzeln umstellen. Und CONVERT TO CHARACTER SET schreibt die ganze Tabelle neu, verdient also dieselbe Vorsicht wie jedes schwere DDL:

ALTER DATABASE tienda
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;

ALTER TABLE clientes
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;

Empfehlung
utf8mb4 überall — Server, Datenbank, Tabelle, Spalte und Client-Verbindung — und eine einzige Kollation im ganzen Schema. Sieh dir vor der Umstellung die Indizes auf langen Textspalten an: beim Wechsel von utf8mb3 auf utf8mb4 kann jedes Zeichen ein Byte mehr brauchen, und ein Index, der passte, passt vielleicht nicht mehr.

Stichwörter: charset, zeichensatz, kollation, collation, utf8, utf8mb4, latin1, emoji, akzente, groß- und kleinschreibung, convert to character set, illegal mix of collations

Die häufigsten Codes — 1045, 1062, 1213, 2006 — und was jeweils zu tun ist.

Gilt für: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Codes unter 2000 kommen vom Server, die ab 2000 von der Client-Bibliothek. Schon diese Unterscheidung sagt, wo zu suchen ist: beginnt die Zahl mit 2, liegt das Problem in der Verbindung, nicht im SQL.

1045 und 1040: die Verbindung
Der 1045 ist fast nie das Passwort: das Konto existiert für einen anderen Host. Denk daran, dass 'app'@'localhost' und 'app'@'%' verschiedene Konten sind. Der 1040 heißt, die Verbindungen sind alle, und die übliche Ursache ist nicht die Poolgröße, sondern Verbindungen, die niemand schließt:

SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'max_allowed_packet';

1062: doppelter Eintrag
Die Meldung nennt den Schlüssel, der verletzt wurde. Ist das Duplikat erwartbar — ein erneut laufender Import, ein Upsert — gibt es Syntax, um es nicht mehr als Fehler zu behandeln:

SELECT email, COUNT(*) AS repetidos
FROM clientes
GROUP BY email
HAVING repetidos > 1;

INSERT INTO clientes (email, nombre) VALUES ('a@b.c', 'Ana')
ON DUPLICATE KEY UPDATE nombre = VALUES(nombre);

1215: der Fremdschlüssel lässt sich nicht anlegen
Diese Meldung ist berühmt dafür, nichts zu sagen. Die echten Ursachen sind immer dieselben vier: die Typen der beiden Spalten stimmen nicht exakt überein (Vorzeichen und Länge eingeschlossen), ihre Zeichensätze stimmen nicht überein, am referenzierten Feld fehlt ein Index, oder es existieren bereits verwaiste Zeilen, die die Bedingung nicht zuließe:

SELECT constraint_name, table_name, referenced_table_name
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE constraint_schema = DATABASE();

SELECT l.* FROM lineas l
LEFT JOIN pedidos p ON p.id = l.pedido_id
WHERE p.id IS NULL;

Den Fehler richtig lesen
Bevor du den Code im Netz suchst, lies ihn ganz: MySQL nennt meist genau Tabelle, Spalte und Wert. Und wenn eine Anweisung eine Warnung statt eines Fehlers zurückgibt, zeigt SHOW WARNINGS direkt danach, was der Server eigenmächtig entschieden hat — etwa ein stilles Abschneiden —, und das ist schlimmer als ein sauberer Fehlschlag.

Empfehlung
Calíope zeigt Code und Meldung des Servers unverändert, ohne sie zu verpacken: dieser Text ist der beste Hinweis und sollte vollständig kopiert werden, wenn du um Hilfe bittest. Das Abfrageprotokoll bewahrt zusätzlich die auslösende Anweisung mit Zeit und Dauer auf.

Stichwörter: fehler, fehlercode, 1045, 1049, 1062, 1146, 1213, 1205, 1215, 1267, 1406, 2002, 2006, 2013, 1040, too many connections, gone away, access denied, duplicate entry