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 — 5 Bytes, dazu bis zu 3 für Sekundenbruchteile. Ohne Zeitzone und ohne Umwandlung beim Speichern oder Lesen: Du bekommst den Wert zurück, den du gespeichert hast.
- TIMESTAMP — 4 Bytes, Wertebereich 1970…2038 (in MySQL, auch in 8.x) oder 1970…2106 (in MariaDB 11.5+). 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 (JSON_UNQUOTE(JSON_EXTRACT(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+.

-- Zusammengesetzter B-Tree-Index
CREATE INDEX idx_pedidos_cliente_fecha
    ON pedidos (cliente_id, fecha_pedido DESC);

-- Volltextindex
ALTER TABLE articulos
    ADD FULLTEXT INDEX ft_titulo_cuerpo (titulo, cuerpo);

-- Räumlicher Index
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.

-- Gut: Index mit der selektivsten Spalte zuerst
CREATE INDEX idx_facturas
    ON facturas (cliente_id, estado, fecha)
    -- cliente_id (alta selectividad) → estado → fecha
;

-- Schlechtes Muster: redundanter Index
-- (cliente_id) ist schon durch (cliente_id, estado, fecha) abgedeckt
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.

-- Die Abfrage liest nur cliente_id und total → der Index deckt sie ab
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

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

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

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

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

-- Schlecht: drei Spalten, die dasselbe wiederholen "entidad"
CREATE TABLE clientes_v1 (
    id   BIGINT PRIMARY KEY,
    nombre  VARCHAR(120),
    tel1 VARCHAR(20),
    tel2 VARCHAR(20),
    tel3 VARCHAR(20)
);

-- Gut: eine verknüpfte Tabelle
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
);

-- SQL Server: Surrogatschlüssel und benannte geschlossene Menge
CREATE TABLE clientes_telefonos (
    id          BIGINT IDENTITY(1, 1) PRIMARY KEY,
    cliente_id  BIGINT NOT NULL,
    telefono    VARCHAR(20) NOT NULL,
    tipo        VARCHAR(10) NOT NULL
        CONSTRAINT ck_telefono_tipo CHECK (tipo IN ('movil', 'casa', 'oficina')),
    UNIQUE (cliente_id, telefono),
    FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE
);

-- Die Liste später ändern
ALTER TABLE clientes_telefonos DROP CONSTRAINT ck_telefono_tipo;
ALTER TABLE clientes_telefonos ADD CONSTRAINT ck_telefono_tipo
    CHECK (tipo IN ('movil', 'casa', 'oficina', 'fax'));

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.

-- Schlecht: nombre_producto wiederholt sich in jeder Zeile desselben Produkts
CREATE TABLE detalle_pedido_v1 (
    pedido_id        BIGINT,
    producto_id      BIGINT,
    cantidad         INT,
    nombre_producto  VARCHAR(120),
    PRIMARY KEY (pedido_id, producto_id)
);

-- Gut: nombre_producto steht in der Tabelle 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.

-- Beispiel: die Bestellsumme zwischenspeichern, um SUM bei jedem Lesen zu vermeiden
ALTER TABLE pedidos
    ADD COLUMN total DECIMAL(12, 2) NOT NULL DEFAULT 0;

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

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

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

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

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

CREATE TRIGGER detalle_pedido_after_insert
ON detalle_pedido
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE p
       SET total = (SELECT COALESCE(SUM(d.cantidad * d.precio_unit), 0)
                    FROM detalle_pedido d
                    WHERE d.pedido_id = p.id)
      FROM pedidos p
     WHERE p.id IN (SELECT pedido_id FROM inserted);
END;

CREATE VIEW dbo.pedidos_total WITH SCHEMABINDING AS
SELECT pedido_id, SUM(cantidad * precio_unit) AS total, COUNT_BIG(*) AS lineas
FROM dbo.detalle_pedido
GROUP BY pedido_id;

CREATE UNIQUE CLUSTERED INDEX ux_pedidos_total ON dbo.pedidos_total (pedido_id);

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

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.

Die Beispiele verwenden diese Tabellen:

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

-- Alle Kunden, ob mit oder ohne Bestellungen
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.

-- Entspricht einem LEFT JOIN mit umgekehrter Reihenfolge
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).

-- Erzeugt alle Kombinationen (Kunde, Monat) für einen Bericht
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)
);

-- Jeder Mitarbeiter mit dem Namen seines Chefs
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.

-- Nachbildung in MySQL
SELECT c.nombre, p.id
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.id

UNION

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

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

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

-- SQL Server nativ
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;

-- SQL Server: Die geschriebene Reihenfolge gilt
SELECT c.nombre, p.id
FROM pedidos p
JOIN clientes c ON p.cliente_id = c.id
OPTION (FORCE ORDER);

-- Die teuerste Bestellung jedes Kunden
SELECT c.nombre, x.total
FROM clientes c
CROSS APPLY (
    SELECT TOP (1) p.total
    FROM pedidos p
    WHERE p.cliente_id = c.id
    ORDER BY p.total DESC
) x;

Anti-Join 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: Kunden mit mindestens einer Bestellung
SELECT c.id, c.nombre
FROM clientes c
WHERE EXISTS (SELECT 1 FROM pedidos p WHERE p.cliente_id = c.id);

-- Anti-Join: Kunden ohne Bestellungen
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, auf SQLite 3.51 und auf SQL Server 2025 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, CROSS APPLY, OUTER APPLY, FORCE ORDER

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

-- Im laufenden Betrieb ändern (8 GB)
SET GLOBAL innodb_buffer_pool_size = 8 * 1024 * 1024 * 1024;

max_connections

Maximale Anzahl gleichzeitiger Verbindungen. Standardmäßig 151, in MySQL wie 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;
-- Auch der Client muss den Parameter übergeben
-- (auf der Kommandozeile: --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 in MySQL 8.0 und in MariaDB, 10 000 / 20 000 im aktuellen MySQL (gemessen auf 26.7).

- 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; -- gilt erst nach einem Neustart
RESET PERSIST innodb_buffer_pool_size;   -- Persistenz entfernen

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 liegt die theoretische Grenze bei 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 im Server und 1 017 in InnoDB, 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+).

-- Tabellengröße prüfen
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.

-- Gültig, aber nicht empfehlenswert
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 Windows.
- 2 — sie werden unverändert gespeichert, aber Vergleiche ignorieren die Groß-/Kleinschreibung. Nur macOS/Windows; Standard unter macOS.

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

- Session-Variablen (@@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+ SQL Server 2022+

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

Schema-Design

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;

-- Das Schema vorher, in einem eigenen Batch: CREATE SCHEMA ventas;
CREATE TABLE ventas.pedidos (
    id             BIGINT IDENTITY(1, 1) CONSTRAINT pk_pedidos PRIMARY KEY,
    cliente_id     BIGINT       NOT NULL,
    estado         TINYINT      NOT NULL CONSTRAINT df_pedidos_estado DEFAULT 0,
    creado_en      DATETIME2(3) NOT NULL CONSTRAINT df_pedidos_creado_en DEFAULT SYSUTCDATETIME(),
    actualizado_en DATETIME2(3) NOT NULL CONSTRAINT df_pedidos_actualizado_en DEFAULT SYSUTCDATETIME(),
    CONSTRAINT fk_pedidos_clientes FOREIGN KEY (cliente_id)
        REFERENCES ventas.clientes (id) ON UPDATE CASCADE,
    CONSTRAINT ck_pedidos_estado CHECK (estado BETWEEN 0 AND 4)
);
CREATE INDEX idx_pedidos_cliente_id ON ventas.pedidos (cliente_id);
CREATE INDEX idx_pedidos_creado_en  ON ventas.pedidos (creado_en);

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

-- Logischer Dump mit transaktionaler Konsistenz
-- (aus der Shell, nicht SQL):
-- mysqldump --single-transaction --routines --triggers --events \\
--           -u root -p mi_base > mi_base.sql

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

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

Replikation

Benutzer und Berechtigungen

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

-- Anwendungsbenutzer mit eingeschränkten Rechten anlegen
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.%.%';

-- NIE in Produktion
-- GRANT ALL PRIVILEGES ON *.* TO 'app'@'%';
FLUSH PRIVILEGES;

USE master;
CREATE LOGIN app_pedidos WITH PASSWORD = N'Contraseña-Fuerte-2026',
    CHECK_POLICY = ON, DEFAULT_DATABASE = mi_base;
USE mi_base;
CREATE USER app_pedidos FOR LOGIN app_pedidos WITH DEFAULT_SCHEMA = ventas;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::ventas TO app_pedidos;

-- NIEMALS in Produktion
-- ALTER SERVER ROLE sysadmin ADD MEMBER app_pedidos;
-- ALTER ROLE db_owner ADD MEMBER app_pedidos;

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

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

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

Calíope 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

Stichwörter: bewährte Praktiken, Design, Konventionen, Backup, Replikation, GTID, binlog, GRANT, Auditierung, Sicherheit, InnoDB, Foreign Key, TLS, SQL Server, IDENTITY, SEQUENCE, Schema, Login, XACT_ABORT, SQL Server Audit

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.18+; in MariaDB einfach ANALYZE)

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: die Schaltfläche Visual EXPLAIN in der Leiste des SQL-Editors, auf der markierten Abfrage.

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 der Queries nach kumulierter Gesamtzeit
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;

-- Tabellen mit dem meisten 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 (1 − Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests). Ziel: >99 %.

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

-- Ohne performance_schema (in MariaDB standardmäßig aus) die beiden Zähler von Hand:
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

- Adaptive hash index — automatischer Hash über heiße Seiten des Buffer Pools. In MySQL 8.0 standardmäßig an, im aktuellen MySQL und in MariaDB aus (gemessen: 1 auf 8.0.46; 0 auf 26.7 und auf MariaDB 13.0).
- 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 unter MySQL 8.0+ (MariaDB hat kein data_locks, dort sind es information_schema.INNODB_LOCK_WAITS und INNODB_TRX):

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

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;

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

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 ein Replikat 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 du in pg_locks nicht sehen wirst, 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 dich verwahrt, damit zwei Prozesse deiner 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 du die Warteschlange der offenen Vorgänge abfragst und diese 5 % ausmachen, indizierst du 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 deine 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 du den „fehlenden" Index anlegst, schau 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 du nur mit @> abfragst: 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 einem Replikat zählt er die Arbeit des Replikats — 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, klammer 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". Sortier 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 17+) — 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.

Das Backup von Calíope ist logisch
Was das Backup-Werkzeug 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: nimm 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 du fast immer filterst. 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
Partitionier nach dem, was du löschen wirst, nicht nach dem, was du abfragen wirst; und prüf am Plan, dass deine Abfragen den Schlüssel im WHERE führen, statt es anzunehmen. Bevor du eine bestehende Tabelle partitionierst, frag dich, 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 deinen 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, schau auf die Sortierfolge, bevor du die Abfrage anfasst.

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

varchar gegenüber nvarchar, das N-Präfix, _UTF8-Sortierfolgen, was jedes Suffix bewirkt, der Collation-Konflikt und warum ein N'…'-Parameter deinen Index ungenutzt lassen kann.

Gilt für: SQL Server 2022+

Wenn du von MySQL kommst: Hier gibt es weder utf8mb4 noch SET NAMES. In SQL Server wird die Kodierung eines Textes nicht separat deklariert – sie ergibt sich aus seiner Collation, und es gibt zwei Familien von Texttypen.

varchar und nvarchar sind zwei Kodierungen
- nvarchar (und nchar) speichert UTF-16: jedes Unicode-Zeichen, unabhängig von der Collation.
- varchar (und char) speichert die Bytes der Codepage seiner Collation. Bei einer klassischen Collation wie Latin1_General_CI_AS ist das Windows-1252, und was dort nicht vorkommt, wird als ? gespeichert.
- Mit einer Collation, die auf _UTF8 endet (seit SQL Server 2019), speichert varchar UTF-8.

Gemessen auf dem Testserver (SQL Server 2025), mit der Datenbank in Latin1_General_100_CI_AS_SC_UTF8:

LEN() zählt Zeichen und DATALENGTH() Bytes. Das n in varchar(n) sind Bytes, das in nvarchar(n) Bytepaare, keine Zeichen. Gemessen: Ein UTF-8-varchar(10) nimmt fünf ñ auf, zehn ergeben Msg 2628 („String or binary data would be truncated“); ein nvarchar(10) nimmt zehn ñ auf, aber keine sechs Emojis.

Das Präfix N
Ein Literal ohne vorangestelltes N ist varchar und wird in die Codepage der Datenbank umgewandelt, bevor es irgendeine Spalte erreicht. Gemessen in einer Latin1_General_CI_AS-Datenbank:

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

Und in einer varchar-Spalte dieser Datenbank wurde Zúrich ✓ 日本 selbst mit N am Literal als Zúrich ? ?? gespeichert. Die Daten gehen beim Schreiben verloren, ohne Fehler und ohne Warnung, und eine spätere Änderung der Collation der Spalte holt sie nicht zurück: Gespeichert sind bereits Fragezeichen.

Was jedes Suffix bedeutet

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

Gemessen: 1, 0, 1, 0.

- _CI / _CS – unterscheidet Groß-/Kleinschreibung nicht / schon.
- _AI / _AS – unterscheidet Akzente nicht / schon.
- _SC – zählt Zeichen außerhalb der Basisebene, etwa ein Emoji, als ein Zeichen. Gemessen: LEN(N'😀') ergibt 1 mit _SC und 2 ohne, und ohne _SC findet ein LIKE N'_' es nicht.
- _UTF8 – varchar in UTF-8.
- _BIN2 – vergleicht nach Codepoint: sortierte A, B, a, b, n, o, á, ñ, während Latin1_General_100_CI_AS und Modern_Spanish_100_CI_AS A, a, á, b, B, n, ñ, o lieferten.
- Die 100 ist die Version der Sortiertabellen; die ohne sie (Latin1_General_CI_AS) sind älter, und die mit SQL_ am Anfang sind die aus SQL Server 2000 übernommenen.

Der Testserver bietet 5540 Collations, davon 1585 mit _UTF8 (sys.fn_helpcollations()).

Server, Datenbank, Spalte und Ausdruck

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

Die Collation wird auf vier Ebenen festgelegt, und jede übernimmt bei ihrer Erstellung die der Ebene darüber:

- Server – die von master und tempdb, bei der Installation gewählt.
- Datenbank – CREATE DATABASE … COLLATE ….
- Spalte – name varchar(40) COLLATE Latin1_General_100_CS_AS.
- Ausdruck – WHERE a = b COLLATE Latin1_General_100_CI_AS oder innerhalb eines ORDER BY.

ALTER DATABASE … COLLATE ändert bestehende Spalten nicht: Gemessen wechselte die Datenbank zu _UTF8, und ihre Spalten blieben bei Latin1_General_CI_AS. Jede Spalte wird mit ihrem eigenen ALTER TABLE … ALTER COLUMN geändert.

Der Collation-Konflikt
Zwei Texte mit unterschiedlicher Collation lassen sich nicht von selbst vergleichen:

- WHERE a = b → Msg 468, „Cannot resolve the collation conflict“.
- Ein UNION ALL der beiden Spalten → Msg 457; mit Katalogtext (sys.* ist in Latin1_General_CI_AS) → Msg 451.
- Gelöst wird es mit COLLATE auf einer Seite.

Der häufigste Fall sind temporäre Tabellen: #tmp lebt in tempdb und entsteht mit der Collation des Servers, nicht mit der deiner Datenbank. Gemessen in einer Latin1_General_100_CI_AS-Datenbank auf einem _UTF8-Server: Ein JOIN mit #tmp ergab Msg 468. Der Ausweg ist, ihre Spalten mit COLLATE DATABASE_DEFAULT zu deklarieren.

Collation und Indizes
Bei 100.000 Zeilen mit einem Index auf der Spalte:

- WHERE s = 'usuario42' → Index Seek. Mit COLLATE auf der Spalte → Index Scan: Der Index wird komplett gelesen.
- WHERE s LIKE 'usuario42%' → Index Seek, anders als bei PostgreSQL.
- Ein N'…'-Parameter gegen eine varchar-Spalte: Mit einer Windows-Collation (Latin1_General_100_CI_AS) wurde der Index weiter genutzt, mit 7 logischen Lesevorgängen; mit einer SQL_-Collation (SQL_Latin1_General_CP1_CI_AS) wird die ganze Spalte nach nvarchar umgewandelt, und der Plan wird zum Index Scan: 334 Lesevorgänge statt 3. Wenn eine Gleichheitssuche langsam ist und der Parameter als nvarchar ankommt, prüfe die Collation der Spalte.

Empfehlung
Für eine neue Datenbank eine _100_…_SC_UTF8-Collation, die varchar zu vollständigem UTF-8 macht. Deklariere die Spalten temporärer Tabellen mit COLLATE DATABASE_DEFAULT. Und entscheide die Collation beim Anlegen der Datenbank: Sie später zu ändern geht nur Spalte für Spalte.

Stichwörter: Kodierung, encoding, Sortierfolge, collation, collate, varchar, nvarchar, utf8, utf-16, n-präfix, unicode, _sc, _bin2, 468, 451, 457, 2628, tempdb, database_default, datalength

datetime gegenüber datetime2, bit statt boolean, money gegenüber decimal, GUIDs, die nicht fragmentieren, rowversion, das kein Datum ist, die 8000-Byte-Grenze und der json-Typ von 2025.

Gilt für: SQL Server 2022+

Wenn du von MySQL kommst, heißen mehrere Typen ähnlich und verhalten sich anders, und zwei Namen täuschen: timestamp ist kein Datum und datetime rundet. Was es nicht gibt, gibt einen Fehler, und das ist gut so: Es gibt kein UNSIGNED, tinyint reicht nur von 0 bis 255 (mit -1 kommt Msg 220, arithmetischer Überlauf) und SELECT TRUE gibt Msg 207: TRUE ist kein Wort der Sprache.

Datum: datetime2, nicht datetime
datetime ist der alte Typ und rundet auf 1/300 Sekunde. Gemessen:

Die letzte Zeile ist die, die Berichte kaputt macht: Ein WHERE fecha <= '2026-09-30 23:59:59.999' auf datetime schließt Mitternacht des Folgetags ein. Für einen ganzen Tag, bei jedem Typ: fecha >= '2026-09-30' AND fecha < '2026-10-01'.

datetime2 speichert bis auf hundert Nanosekunden (in datetime2(7) blieb .1234567 genau so), beginnt im Jahr 1 —'1700-01-01' in datetime gibt Msg 242, außerhalb des Bereichs— und belegt gleich viel oder weniger: gemessen datetime 8 Bytes, datetime2(7) 8, datetime2(3) 7 und datetime2(0) 6. Und man mischt sie nicht gedankenlos: Dasselbe 12:00:00.001 in datetime2(3) und in datetime ist nicht gleich, weil datetime es schon als .000 gespeichert hatte.

Weitere gemessene Größen: date 3 Bytes, time 5, smalldatetime 4 (minutengenau) und datetimeoffset 10.

datetimeoffset speichert den Versatz
Anders als timestamptz in PostgreSQL speichert es den Versatz, mit dem geschrieben wurde, und vergleicht nach Zeitpunkt: '2026-09-30T12:00:00+05:30' und '2026-09-30T06:30:00+00:00' kamen gleich heraus. SWITCHOFFSET(wert, '+00:00') bringt es nach UTC, und AT TIME ZONE 'Central European Standard Time' lieferte 08:30 +02:00, mit Sommerzeit. Calíope zeigt es in der Zeit des Werts, mit dem Versatz daneben.

Es gibt kein boolean: es gibt bit
bit ist eine Zahl, die 0 oder 1 ist, kein Wahrheitswert. Gemessen: 5, -1 oder die Zeichenkette 'true' zuzuweisen speichert 1; ein nacktes WHERE @b gibt Msg 4145 —man muss WHERE @b = 1 schreiben— und bit + bit gibt Msg 402. Calíope zeigt es als 1 oder 0.

Geld: decimal, und money mit Vorsicht

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

money hat vier feste Nachkommastellen, auch in Zwischenergebnissen: 1 / 3 ergab .3333, und mal 3 kommt man nicht mehr auf die ganze Zahl zurück. decimal erweitert die Skala des Ergebnisses (es ergab .3333333333333333333). Zum Rechnen decimal; money belegt 8 Bytes und smallmoney 4. float ist binär wie überall: 0.1 + 0.2 ergab 0.30000000000000004, und die Gleichheit mit 0.3 war falsch; in decimal wahr.

GUIDs: uniqueidentifier, und die Reihenfolge zählt
Ein uniqueidentifier belegt 16 Bytes. Als gruppierter Primärschlüssel verteilt NEWID() die Zeilen zufällig über den Index. Gemessen mit 20.000 einzeln eingefügten Zeilen:

NEWSEQUENTIALID() geht nur als DEFAULT einer Spalte: SELECT NEWSEQUENTIALID() gibt Msg 302. Calíope zeigt die GUID in Großbuchstaben, wie SQL Server.

rowversion ist kein Datum
rowversion —der Typ, der früher timestamp hieß, ein Name, der beim Anlegen einer Spalte noch akzeptiert wird— ist ein 8-Byte-Zähler der Datenbank, der sich bei jedem Schreiben der Zeile ändert. Gemessen: Zwei Zeilen entstanden mit 0x…07D4 und 0x…07D5, und das Aktualisieren der ersten brachte sie auf 0x…07D6. Er speichert keine Uhrzeit. Er dient der optimistischen Nebenläufigkeit: Man liest die Version und aktualisiert mit WHERE ver = @gelesen. Calíope zeigt ihn als Binärwert, [8 bytes].

Text: 8000 Bytes, oder max
Außerhalb von max reicht varchar(n) bis 8000 und nvarchar(n) bis 4000: varchar(8001) gibt Msg 131 und nvarchar(4001) Msg 2717. Darüber kommen varchar(max) und nvarchar(max).

Auch die Zeile hat eine Grenze: Zwei char(5000) in einer Tabelle geben Msg 1701 —die Mindestzeile wäre 10.007 Bytes und das Maximum sind 8060—. Zwei volle varchar(8000) passen (gemessen: 16.000 Bytes in einer Zeile), weil variable Daten die Seite verlassen, wenn sie nicht hineinpassen.

Und eine Falle ohne Warnung: REPLICATE('x', 10000) in ein varchar(max) zugewiesen ergab 8000 Zeichen, weil die Funktion im Typ ihres Arguments arbeitet. Mit REPLICATE(CAST('x' AS varchar(max)), 10000) 10.000.

JSON: der Typ json ist von SQL Server 2025
In SQL Server 2022 wird JSON in nvarchar(max) gespeichert und mit CHECK (ISJSON(col) = 1) geprüft, das Fehlerhaftes mit Msg 547 ablehnt. Seit 2025 gibt es den Typ json, der es beim Schreiben mit Msg 13609 ablehnt und bereits geparst speichert. Gemessen an 20.000 Kopien einer Bestellung mit 142 Zeichen belegte json 5,4 MB und nvarchar(max) 6,0 MB; bei einem winzigen Dokument, {"b":1,"a":2}, umgekehrt: 60 Bytes gegenüber 26.

Und ein sichtbarer Unterschied: Mit einem doppelten Schlüssel akzeptiert ISJSON {"b":1,"a":2,"a":3}, und der Typ json gab es als {"b":1,"a":2} zurück: Er behielt den ersten, ohne Warnung. Die Reihenfolge der Schlüssel bleibt erhalten. Calíope empfängt json als Text und zeigt es unverändert.

Die besonderen Typen
- geography und geometry — Typen mit eigenem Binärformat, das kein WKB ist. geography::Point(40.4168, -3.7038, 4326) belegt 22 Bytes und sein STAsText() gibt POINT (-3.7038 40.4168): Der Konstruktor will Breite, Länge und der Text kommt als Länge, Breite heraus. Calíope zeigt die Zelle als [22 bytes]; zum Lesen col.STAsText() in der Abfrage.
- hierarchyid — ein Pfad in einem Baum: '/1/3/' wurde in 2 Bytes gespeichert, mit Ebene 2 und Elternteil /1/. Calíope dekodiert ihn nicht, und die Zelle sagt nicht unterstützter Typ: hierarchyid; mit col.ToString() in der Abfrage lässt er sich lesen.
- sql_variant — eine Spalte, die Werte verschiedener Typen hält, jeden mit seinem eigenen: SQL_VARIANT_PROPERTY(@v, 'BaseType') ergab decimal. Calíope zeigt ihn nach seinem Basistyp. Im Katalog (sys.extended_properties) ergibt er Sinn; in einer eigenen Tabelle ist er meist eine schlecht entworfene Spalte.

IDENTITY oder SEQUENCE
IDENTITY ist eine Eigenschaft der Spalte und schützt sie: Einen Wert von Hand einzufügen gibt Msg 544, außer mit SET IDENTITY_INSERT tabla ON, und danach zählt der Zähler vom größten weiter (nach dem Einfügen von 100 war die nächste Zeile 101). Und er hinterlässt Lücken: Ein mit ROLLBACK zurückgenommenes INSERT verbrauchte die 3, und die nächste Zeile war die 4. Eine Lücke ist kein verlorener Datensatz.

SEQUENCE ist ein eigenes Objekt, das sich mehrere Tabellen teilen können: Mit DEFAULT NEXT VALUE FOR sq in zwei Tabellen verteilten sich die Werte auf 1 und 3 in der einen und 2 in der anderen. Man nimmt es, wenn man die Nummer vor dem Einfügen braucht oder geteilt; für den Schlüssel einer Tabelle IDENTITY.

Empfehlung
datetime2 für Uhrzeitablesungen und datetimeoffset für Zeitpunkte, decimal für Geld, bit im Wissen, dass es eine Zahl ist, NEWSEQUENTIALID() oder eine Ganzzahl, wenn der gruppierte Schlüssel eine GUID ist, und den Typ json nur, wenn dein ältester Server 2025 ist. Für Text siehe „Kodierung und Sortierfolgen (SQL Server)“.

Stichwörter: Typen, Datentypen, datetime, datetime2, datetimeoffset, date, time, bit, boolean, money, decimal, float, uniqueidentifier, guid, newsequentialid, rowversion, timestamp, varchar, nvarchar, max, json, isjson, hierarchyid, geography, sql_variant, identity, sequence, unsigned

Wie man eine Msg liest, was ihr Level sagt, die Nummern, die du täglich siehst, welche Fehler die Transaktion abbrechen und welche nicht, TRY…CATCH mit THROW, und wo der Grund für eine 18456 steht.

Gilt für: SQL Server 2022+

Wenn du von MySQL kommst: Auch hier gibt es Nummern —kein SQLSTATE wie in PostgreSQL—, aber jeder Fehler bringt vier Angaben mit, und alle zählen. So zeigt ihn Calíope, in einer Zeile; sqlcmd teilt ihn auf zwei auf:

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

- Msg — die Nummer. Danach sucht man, und darauf programmiert man.
- Level — der Schweregrad, von 0 bis 25. Er sagt, wessen Schuld es ist und was mit der Verbindung passiert.
- State — ein Untercode, mit dem der Server Ursachen mit derselben Nummer unterscheidet. Bei den meisten Fehlern spielt er keine Rolle; bei 18456 ist er das Einzige, was den Grund verrät.
- Line — die Zeile innerhalb des Batches (was zwischen zwei GO steht) oder innerhalb der Prozedur: Ein Fehler darin erscheint als Procedure p_falla, Line 3.

Das Level, das du dir als Erstes ansiehst

Ein Level von 20 oder mehr lässt sich nicht von Hand auslösen, ohne sysadmin zu sein und ohne WITH LOG: Das ergibt Msg 2754. Calíope behandelt ein Level von 20 oder mehr als verlorene Verbindung.

Die, die du täglich siehst
Einzeln gegen SQL Server 2025 ausgelöst:

Den Text zu jeder Nummer siehst du mit: SELECT text FROM sys.messages WHERE message_id = 1205 AND language_id = 1033;. Es gibt 16.785 Meldungen auf Englisch, in 22 Sprachen.

Was jeder Fehler rückgängig macht: die Falle für alle, die von PostgreSQL kommen
In PostgreSQL bricht ein Fehler die ganze Transaktion ab. Hier hängt es vom Fehler ab, und die meisten brechen nur die Anweisung ab. Gemessen, ohne etwas einzuschalten:

BEGIN TRAN;
INSERT padre VALUES (10, 'd@x.es', 1);
INSERT padre VALUES (10, 'e@x.es', 1);  -- Msg 2627
INSERT padre VALUES (11, 'f@x.es', 1);
COMMIT;
-- die Zeilen 10 und 11 sind geblieben

Die Transaktion blieb offen, das COMMIT hat bestätigt, was funktioniert hatte, und der Batch lief weiter, als wäre nichts gewesen. Was jeder gemessene Fehler getan hat:

- Nur die Anweisung: 2627, 2601, 547, 515, 2628, 8134, und auch 1222: Nach Ablauf der Wartezeit stand @@TRANCOUNT weiter auf 1.
- Der Batch und die Transaktion: 245 (Konvertierung) hat die Transaktion zurückgerollt und den Batch abgebrochen, ohne dass etwas eingeschaltet war; 1205 ebenso, beim Opfer; ein ROLLBACK in einem Trigger ergibt Msg 3609 und bricht den Batch ab.
- Die Verbindung: Level 20 oder mehr.

SET XACT_ABORT ON ändert das: Damit hat dieselbe 2627 die ganze Transaktion zurückgerollt und den Batch abgebrochen (die Zeilen 20 und 21 sind nicht geblieben). Das gehört an den Anfang jeder Prozedur, die eine Transaktion öffnet.

TRY…CATCH, und was es nicht fängt

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

Im CATCH liefern ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), ERROR_PROCEDURE() und ERROR_MESSAGE() die Angaben des Fehlers (gemessen: 2627, 14, 1, 4, NULL). Und XACT_STATE() sagt, was du mit der Transaktion tun kannst: Es lieferte 1 ohne XACT_ABORT (sie lässt sich bestätigen) und -1 damit (sie lässt sich nur zurückrollen).

Was ein TRY nicht fängt: ein Objekt, das in seinem eigenen Gültigkeitsbereich nicht existiert. SELECT * FROM tabla_que_no_existe in einem TRY kam als Msg 208 heraus, ohne durch den CATCH zu gehen, weil es beim Kompilieren scheitert; in einem EXEC sp_executesql wurde es gefangen. Auch was Level 10 oder weniger hat, fängt es nicht.

THROW gegen RAISERROR
- THROW; ohne Argumente in einem CATCH wirft den ursprünglichen Fehler erneut: Die Nummer, die draußen ankam, war weiterhin 8134. Mit RAISERROR(ERROR_MESSAGE(), 16, 1) erneut ausgelöst, wurde daraus 50000, und die Nummer ging verloren.
- RAISERROR hält den Batch nicht an: Was danach kam, wurde ausgeführt. THROW schon.
- THROW 50001, 'Text', 1 löst einen eigenen Fehler aus, immer mit Level 16 und einer Nummer ab 50000. RAISERROR nimmt außerdem eine mit sp_addmessage registrierte Nummer und füllt ihre Argumente: RAISERROR(50100, 16, 1, 42) ergab „El pedido 42 no existe“.

Der Text ändert sich mit der Sprache, die Nummer nicht
Mit SET LANGUAGE Spanish kam die 8134 als „Error de división entre cero.“ an: dieselbe Nummer mit anderem Text. Die Sprache ist die der Sitzung, und Calíope verbindet sich mit der Standardsprache des Logins. Deshalb vergleicht man im Code und in den eigenen Protokollen die Nummer, nie den Text.

Die 18456: Der Grund steht im Serverprotokoll
Eine fehlgeschlagene Anmeldung kommt beim Client immer gleich an: Msg 18456, Level 14, State 1, „Login failed for user '…'“. Mit drei verschiedenen Ursachen gemessen, sah der Client alle drei Male State 1. Der echte State steht nur im Serverprotokoll:

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

Wenn die beim Verbinden angeforderte Datenbank nicht existiert, kommt vor der 18456 eine 4060 („Cannot open database … requested by the login“), und die nennt den Grund. Wenn jemand „nicht reinkommt“, unterscheidet das Serverprotokoll in einer Sekunde, was von außen derselbe Satz ist.

Was Calíope zeigt
Wenn eine Anweisung fehlschlägt, zeigt Calíope im Ergebnis-Tab die Meldung des Servers vollständig, in der Form Msg, Level, State, Line von oben, und alle Meldungen, eine pro Zeile: Ein CREATE TABLE mit einer doppelten Einschränkung bringt die 2714 und danach die 1750, und beide sagen etwas. Diese Fehler bleiben in ihrem Tab. Die, die das Verbinden verhindern —die 18456, die 4060— und die mit Level 20 oder mehr, die die Verbindung trennen, landen zusätzlich im Fehlerprotokoll von Calíope. Informative Meldungen —ein PRINT, ein RAISERROR mit Level 10— zeigt es nicht.

Empfehlung
SET XACT_ABORT ON und TRY…CATCH mit IF @@TRANCOUNT > 0 ROLLBACK; THROW; in jeder Prozedur, die eine Transaktion öffnet. In der Anwendung die 1205 wiederholen —die ganze Transaktion, die der Server schon zurückgerollt hat— und Level 14 bis 16 nicht wiederholen, denn die betreffen die Daten oder das Programm. Immer die Nummer speichern, nie den Text. Und bei einer 18456 erst das Serverprotokoll lesen, bevor du das Passwort anfasst.

Stichwörter: Fehler, msg, Level, Schweregrad, severity, state, 18456, 1205, 2627, 2601, 547, 208, 102, 2628, 8152, 515, 229, 916, 4060, 1222, try, catch, throw, raiserror, xact_abort, xact_state, error_number, sys.messages, xp_readerrorlog, Wiederholung

Der gruppierte Index, der die Tabelle ist, der Key Lookup und wann INCLUDE ihn beseitigt, gefilterte Indizes, berechnete Spalten statt Ausdrucksindizes, Columnstore, FILLFACTOR und Fragmentierung, und die Indizes, die niemand nutzt.

Gilt für: SQL Server 2022+

Wenn du von MySQL kommst, ist InnoDB das Ähnlichste: Auch hier ist die Tabelle ein Index. Anders ist, dass du diesen Index —den gruppierten— selbst wählst und er nicht der Primärschlüssel sein muss, dass eine Tabelle auch keinen haben kann, und dass es statt Präfix- oder unsichtbarer Indizes gefilterte Indizes, berechnete Spalten und Columnstore gibt.

Alles Folgende ist an einer Tabelle mit 200 000 Zeilen und rund 32 MB gemessen.

Die Tabelle ist der gruppierte Index
PRIMARY KEY legt einen gruppierten Index an, wenn die Tabelle noch keinen hat: Die Zeilen werden nach diesem Schlüssel sortiert gespeichert. Eine Tabelle ohne gruppierten Index ist ein Heap (HEAP in sys.indexes): Die Zeilen landen, wo Platz ist.

Was man von MySQL aus nicht sieht, ist der Preis: Der gruppierte Schlüssel reist in jedem nicht gruppierten Index mit, denn so findet der Index die Zeile. Derselbe Index auf cliente_id belegte 2 952 kB mit einem gruppierten Schlüssel vom Typ int und 5 416 kB mit einem uniqueidentifier. Ein schmaler, stetig wachsender gruppierter Schlüssel macht alle anderen Indizes billiger; warum NEWID() ein schlechter gruppierter Schlüssel ist, erklärt das Thema über Datentypen.

Key Lookup, und was INCLUDE behebt
Ein nicht gruppierter Index trägt nur seine Spalten und den gruppierten Schlüssel. Fragt die Abfrage nach einer weiteren, zwingt jede gefundene Zeile zum Rückweg in die Tabelle: Das ist der Key Lookup.

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

CREATE INDEX ix_cliente_inc ON dbo.pedidos (cliente_id) INCLUDE (total);
-- Index Seek (ix_cliente_inc) und sonst nichts: 3 logische Lesevorgänge

INCLUDE speichert die Spalte in den Blättern des Index, ohne nach ihr zu sortieren. Hier wuchs der Index von 2 952 kB auf 4 752 kB.

Der Key Lookup wird Zeile für Zeile bezahlt, deshalb lohnt er sich sehr bald nicht mehr. Gemessen: Bei 992 Zeilen (0,5 % der Tabelle) nutzte der Plan noch Index und Lookup; bei 1 483 (0,7 %) durchlief er schon die ganze Tabelle. Ein Index, der „nicht genutzt wird“, ist oft ein Index, dem ein INCLUDE fehlt.

Die Regel des linken Präfixes
Mit einem Index auf (fecha, cliente_id) und WHERE cliente_id = 77 kann SQL Server nicht im Index absteigen. Er kann ihn, wie PostgreSQL, ganz durchlaufen, wenn das billiger ist als die Tabelle: Index Scan und 809 logische Lesevorgänge. Das ist kein Index Seek, und die Reihenfolge der Spalten zählt weiterhin.

Gefilterte Indizes
Ein Index kann ein WHERE tragen und indiziert dann nur, was es erfüllt:

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

Mit 5 % offenen Bestellungen belegte er 160 kB, gegenüber 2 952 kB für den vollständigen Index auf derselben Spalte. Der Haken: Der Plan muss für jeden Wert taugen. Mit dem Literal WHERE estado = 'pendiente' wurde er genutzt; mit einer Variablen (WHERE estado = @e) nicht, und ihn mit WITH (INDEX(ix_pend)) zu erzwingen, ergibt Msg 8622.

Und etwas, das von MySQL kommend überrascht: In SQL Server lässt ein eindeutiger Index nur ein einziges NULL zu. CREATE UNIQUE INDEX ux_email ON dbo.pedidos (email) mit zwei E-Mails auf NULL ergibt Msg 1505 („The duplicate key value is (<NULL>)“). Der Ausweg ist der gefilterte Index:

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

Keine Ausdrucksindizes: berechnete Spalten
Eine Funktion auf der Spalte im WHERE verhindert das Absteigen im Index. Auf denselben 14 400 Zeilen eines Monats:

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

Lässt sich der Ausdruck nicht als Bereich umschreiben, indiziert man eine berechnete Spalte:

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

SELECT id FROM dbo.pedidos WHERE LOWER(email) = 'u77@ejemplo.com';
-- Index Seek (ix_email_lower), ohne die berechnete Spalte zu nennen

Um diesen Index anzulegen, und danach, um in die Tabelle zu schreiben, braucht die Sitzung QUOTED_IDENTIFIER und ANSI_NULLS eingeschaltet; sqlcmd schaltet das erste ab, wenn man ihm nicht -I übergibt, und dann scheitert es mit Msg 1934.

Columnstore, für das, was aggregiert
Ein Columnstore-Index speichert jede Spalte getrennt und komprimiert. Dieselbe Tabelle belegte 35 520 kB mit ihrem normalen gruppierten Index und 7 104 kB als CLUSTERED COLUMNSTORE. Ein SELECT estado, SUM(total) … GROUP BY estado las auf der ersten 4 440 Seiten und brauchte 19 ms CPU, auf der zweiten 147 Seiten und 2 ms. Um eine Zeile über ihren Schlüssel zu finden, ist ein normaler Index weiterhin besser; üblich ist, einer Arbeitstabelle einen NONCLUSTERED COLUMNSTORE mit den Spalten hinzuzufügen, über die aggregiert wird (der mit vier Spalten belegte 3 128 kB).

Fremdschlüssel werden nicht von selbst indiziert
InnoDB legt für jeden Fremdschlüssel einen Index an; SQL Server nicht. Gemessen: Nach cliente_id int REFERENCES dbo.clientes(id) hatte die Kindtabelle nur den Index ihres Primärschlüssels. Ohne diesen Index durchläuft jedes DELETE in der Elterntabelle die ganze Kindtabelle, um die Einschränkung zu prüfen.

Fragmentierung und FILLFACTOR

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

Der Index auf cliente_id, mit zufälligen Werten, Schritt für Schritt gemessen:

FILLFACTOR lässt in jeder Seite Platz, damit Einfügungen passen, ohne sie zu teilen: Das kostet vom ersten Tag an Platz und erspart danach die Fragmentierung. Er bleibt im Index gespeichert (sys.indexes.fill_factor), also wiederholen ihn die folgenden REBUILDs. REORGANIZE verdichtet ohne zu sperren und lässt sich unterbrechen; REBUILD baut den ganzen Index neu und sperrt ohne ONLINE die Tabelle, solange er läuft.

Neu aufbauen, ohne die Tabelle anzuhalten

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

ONLINE = ON gehört zur Enterprise-Edition (und zur Developer-Edition, die dieselbe ist, und zu Azure SQL): In Standard gibt es das nicht, und ein REBUILD hält die Schreibvorgänge an, solange er läuft —beim gruppierten Index auch die Lesevorgänge—. RESUMABLE = ON erlaubt, ihn anzuhalten und später fortzusetzen, und verlangt ONLINE = ON (ohne ergibt es Msg 11438). Und zwei Fälle, in denen ONLINE = ON scheitert: mit einem räumlichen Index auf der Tabelle und mit ALTER INDEX ALL.

Keine unsichtbaren Indizes: DISABLE
ALTER INDEX … DISABLE kommt dem am nächsten, ist aber nicht dasselbe: Es löscht die Daten des Index und behält nur seine Definition, der Weg zurück ist also ein vollständiges REBUILD. Und den gruppierten Index zu deaktivieren, macht die Tabelle unlesbar: SELECT COUNT(*) ergibt Msg 8655, und nebenbei werden alle nicht gruppierten Indizes deaktiviert, mit einer Warnung für jeden.

Die, die niemand nutzt, und die, die fehlen

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

Die Vorsicht ist größer als in PostgreSQL: Diese View lebt im Speicher und wird beim Neustart des Servers geleert. Gemessen: Ich habe den Container neu gestartet, und die View kam ohne eine einzige Zeile der Tabelle zurück. Den Index zu deaktivieren setzt sie ebenfalls auf null; ein REBUILD nicht. Ein Index, der an einem Montag 0 Lesevorgänge zeigt, kann einer sein, den noch niemand nutzen konnte.

Die andere Hälfte sind die fehlenden: Der Optimierer notiert in sys.dm_db_missing_index_details den Index, den er gewollt hätte. Nach einer Abfrage auf cliente_id und estado schlug er [cliente_id], [estado] mit geschätzten 99,6 % Wirkung vor. Das ist ein Hinweis, kein Befehl: Er schaut nicht auf die Indizes, die du schon hast, gewichtet keine Schreibvorgänge und lebt im selben Speicher, der geleert wird.

Empfehlung
Ein schmaler, stetig wachsender gruppierter Schlüssel; ein Index für jeden Fremdschlüssel, über den gesucht oder gelöscht wird; INCLUDE vor einem weiteren Index, wenn der Plan einen Key Lookup zeigt; und bevor du einen „ungenutzten“ Index löschst, nachsehen, wie lange der Server schon läuft (sqlserver_start_time in sys.dm_os_sys_info).

Stichwörter: Index, Indizes, gruppiert, clustered, nicht gruppiert, nonclustered, Heap, key lookup, include, gefilterter Index, berechnete Spalte, columnstore, fillfactor, Fragmentierung, reorganize, rebuild, online, resumable, disable, dm_db_index_usage_stats, dm_db_missing_index_details, dm_db_index_physical_stats

Die Grenzen, an die man wirklich stößt —8 060 Byte pro Zeile, 900 und 1 700 Byte Indexschlüssel, 1 024 Spalten, 128 Zeichen pro Name, 2 100 Parameter—, welche beim Anlegen der Tabelle nur warnen und später scheitern, und die der Express-Edition.

Gilt für: SQL Server 2022+

Die Grenzen von SQL Server ähneln denen von InnoDB kaum, und die, die im Alltag beißen, sind nicht die großen. Ich habe sie einzeln gegen SQL Server 2025 ausgelöst.

Die, an die man wirklich stößt

Die Zeile passt in eine Seite, außer dem Variablen
Eine Seite hat 8 KB, und eine Zeile muss hineinpassen: 8 060 Byte. Bei Spalten fester Breite prüft der Server das beim Anlegen der Tabelle: zwei char(5000) ergeben Msg 1701, die sagt, dass die minimale Zeile 10 007 Byte groß wäre.

Bei variablen Spalten nicht. Ich habe zwei volle varchar(8000) in derselben Zeile gespeichert —16 000 Byte— ohne jeden Fehler: Was nicht passt, wandert auf eigene Seiten, ROW_OVERFLOW_DATA. sys.allocation_units zeigte 2 Zeilenseiten und 2 Überlaufseiten. Das funktioniert, aber das Lesen dieser Zeile kostet einen Lesevorgang mehr pro übergelaufener Spalte, und am Entwurf sieht man nicht, dass es passiert.

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

varchar(max), nvarchar(max) und varbinary(max) reichen bis 2 GB und verlassen die Zeile auf demselben Weg. Warum es varchar(8001) nicht gibt und man zu max springen muss, erklärt das Thema Datentypen.

Die Grenze, die nur warnt: der Indexschlüssel
Das ist die überraschendste. Ein Index auf eine Spalte, die breiter als die Grenze ist, wird angelegt, mit einer Warnung:

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

Eine Warnung hält kein Deployment auf. Die Tabelle funktioniert bis zu dem Tag, an dem jemand einen langen Wert speichert: 900 Byte gingen durch, 901 ergaben Msg 1946. Bei einem nicht gruppierten Index liegt die Grenze bei 1 700 Byte, und mit nvarchar sind das 850 Zeichen, nicht 1 700, weil jedes zwei Byte belegt. Ein PRIMARY KEY auf varchar(1000) ist ein gruppierter Index und verhält sich genauso.

Braucht eine lange Spalte eine Suche auf Gleichheit, funktioniert es, eine Zusammenfassung zu indizieren: eine berechnete Spalte mit CHECKSUM oder HASHBYTES, indiziert, und zusätzlich den vollständigen Wert vergleichen.

Namen: ein Fehler, kein Abschneiden
Ein Name mit 129 Zeichen ergibt Msg 103, und die Anweisung wird nicht ausgeführt. PostgreSQL schneidet auf 63 Byte ab und macht weiter; SQL Server lässt den Namen nicht durch. Der einer lokalen temporären Tabelle ist kürzer, 116, weil der Server ein Suffix anhängt, um Sitzungen zu unterscheiden.

Parameter: 2 100, und in der Praxis 2 098
Eine Prozedur nimmt 2 100 Parameter an, und ein Aufruf kann nicht mehr übergeben. Aber Treiber und ORMs schicken parametrisierte Abfragen über sp_executesql, und dort zählen die Anweisung und die Deklaration mit: Mit 2 098 Parametern ging es, mit 2 099 kam Msg 180. Das ist der typische Fehler eines WHERE id IN (@p1, @p2, …), das aus einer Liste erzeugt wird, die eines Tages wächst. Der Ausweg ist nicht, die Liste zu zerstückeln, sondern sie als Tabelle zu übergeben (ein tabellenwertiger Parameter oder eine temporäre Tabelle) und einen JOIN zu machen. Ein IN mit 60 000 in die Anweisung geschriebenen Literalen funktionierte dagegen.

Mehr Spalten: SPARSE mit Spaltensatz
1 025 Spalten ergeben Msg 1702. Mit SPARSE-Spalten und einem COLUMN_SET FOR ALL_SPARSE_COLUMNS habe ich eine Tabelle mit 2 002 Spalten angelegt; dieselben Spalten ohne den Satz ergaben dieselbe Msg 1702. Die Dokumentation nennt das eine „breite Tabelle“ und hebt die Grenze auf 30 000 Spalten, aber die Zeile muss weiterhin in 8 060 Byte passen.

Verschachtelung: 32 Ebenen
Eine Prozedur, die sich selbst aufruft, erreichte @@NESTLEVEL 32, und die nächste Ebene ergab Msg 217. Prozeduren, Funktionen, Trigger und Views zählen zusammen: Ein Trigger, der eine andere Tabelle mit Trigger aktualisiert, verbraucht Ebenen, ohne dass man es sieht.

Die großen, die fast nie das Problem sind
Ich habe sie nicht gemessen —das Testbed ist die Developer-Edition, und sie zu messen hieße, eine Platte zu füllen—; sie stehen hier mit der Zahl aus der Dokumentation von Microsoft:

- Größe einer Datenbank: 524 272 TB. Einer Datendatei: 16 TB.
- Zeilen pro Tabelle: keine Grenze außer dem Speicher.
- Größe eines Batches: 65 536-mal die Netzwerkpaketgröße (standardmäßig 4 096 Byte, also 256 MB).
- Verbindungen: 32 767 (@@MAX_CONNECTIONS), und user connections auf 0 heißt, dass es keine weitere Obergrenze gibt.

Die, die die Editionen setzen
Hier gibt es Grenzen, an die man stößt, und sie kommen von der Lizenz, nicht von der Engine. Auch die habe ich nicht gemessen: Das Testbed ist Developer, und die hat keine.

- Express: 10 GB pro Datenbank in SQL Server 2022 und 50 GB in 2025; etwa 1,4 GB Speicher für den Pufferpool; das Kleinere aus einem Sockel oder vier Kernen. Ist die Größe erreicht, wächst die Datenbank nicht mehr, und Schreibvorgänge scheitern.
- Standard: keine Obergrenze für die Datenbankgröße, aber eine für Speicher und Kerne, und ohne einige Enterprise-Funktionen, etwa REBUILD mit ONLINE = ON.

SELECT SERVERPROPERTY('Edition') sagt, welche der Server hat, mit dem du verbunden bist.

Empfehlung
Drei im Auge behalten: den Indexschlüssel, wenn eine Textspalte indiziert wird, weil er nur warnt; die 2 098 Parameter, wenn etwas IN-Listen erzeugt; und die Datenbankgröße, wenn der Server Express ist. Vom Rest erfährt man über die Fehlernummer, die immer sagt, welche Grenze es ist.

Stichwörter: Grenzen, Maximum, 8060, Zeile, Seite, row_overflow_data, Überlauf, Indexschlüssel, 900 Byte, 1700 Byte, 1024 Spalten, sparse, column_set, 128 Zeichen, Bezeichner, temporäre Tabelle, 2100 Parameter, sp_executesql, nestlevel, Verschachtelung, express, 10 gb, 50 gb, Edition, msg 1946, msg 1702, msg 180

Warum hier ein SELECT auf ein UPDATE wartet, was READ_COMMITTED_SNAPSHOT ändert, wie man sieht, wer wen blockiert, wo der Graph eines 1205 liegt und wann aus Zeilensperren eine Tabellensperre wird.

Gilt für: SQL Server 2022+

Wenn du von MySQL oder PostgreSQL kommst, stolperst du zuerst darüber: In SQL Server wartet ein SELECT standardmäßig auf ein UPDATE. InnoDB und PostgreSQL lesen die letzte bestätigte Version der Zeile und machen weiter; SQL Server verlangt unter einfachem READ COMMITTED eine gemeinsame Sperre und wartet, bis die andere Seite bestätigt oder zurückrollt. Alles, was ich hier erzähle, habe ich gegen SQL Server 2025 gemessen, mit zwei gleichzeitig offenen Sitzungen.

Ein SELECT, das wartet

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

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

Sitzung B stand 7,5 s still, genau so lange, wie A für das ROLLBACK brauchte, und wartete mit LCK_M_S auf den Schlüssel dieser Zeile. Und A muss dabei nichts tun: Eine Sitzung, die eine Transaktion geöffnet, geschrieben und dann Ruhe gegeben hat —status sleeping, open_transaction_count 1—, blockiert genauso. Das ist der Alltagsfall: eine Anwendung, die ihr COMMIT vergessen hat.

READ_COMMITTED_SNAPSHOT: lesen wie die anderen beiden

ALTER DATABASE meine_db SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

Mit dieser Option liest READ COMMITTED die letzte bestätigte Version der Zeile, und derselbe Lesevorgang kam sofort mit dem alten Wert zurück (100, nicht die unbestätigte 50). Drei Dinge, bevor du sie einschaltest:
- Sie gehört zur Datenbank, nicht zur Sitzung, und ist ab Werk aus: In einer frisch angelegten Datenbank, in model und in der demo meines Labors ist is_read_committed_snapshot_on 0.
- Ohne WITH ROLLBACK IMMEDIATE wartet das ALTER, bis keine andere Sitzung mehr in der Datenbank ist: Mit einer verbundenen wartete es 14,5 s, so lange, bis sie ging. Mit der Klausel wirft es die anderen hinaus und rollt ihre Transaktionen zurück.
- Sie ändert nur, wer liest. Zwei Schreibvorgänge auf dieselbe Zeile warten weiter aufeinander: Das UPDATE von Sitzung B wartete genau wie vorher.

Wer blockiert wen
Gefragt wird, solange die Blockierung andauert:

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

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

Die erste beantwortet die Frage, die man wirklich hat: blocking_session_id ist die Sitzung, die festhält, was diese hier anfordert. Die zweite zeigt die ganze Leiter: Wer eine Zeile aktualisiert, hält X auf dem Schlüssel und IX auf der Seite und auf der Tabelle, und wer wartet, erscheint mit request_status WAIT. Anders als in PostgreSQL erscheinen Zeilensperren hier in der Liste, eine pro Zeile.

Die Prozessliste von Calíope zeigt das Warten in der Spalte Status —eine blockierte Sitzung erscheint mit LCK_M_S, LCK_M_U oder LCK_M_X—, aber nicht, wer sie blockiert: Das liefert die erste Abfrage. Und Kill Connection ist hier der einzige Weg, eine fremde Sperre zu lösen: KILL schließt die ganze Sitzung und rollt ihre Transaktion zurück, weil SQL Server nicht erlaubt, nur die Abfrage einer anderen Sitzung abzubrechen.

UPDLOCK, das hiesige FOR UPDATE
Ein SELECT … FOR UPDATE gibt es nicht. Diese Arbeit erledigt ein Hinweis:

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

Er nimmt eine U-Sperre: Ein normaler Lesevorgang einer anderen Sitzung ging ohne Warten durch, ein weiterer mit UPDLOCK blieb stehen. Und er verhindert die dümmste Verklemmung von allen: Zwei Sitzungen unter REPEATABLE READ, die dieselbe Zeile lesen und danach aktualisieren, endeten in einem 1205; mit UPDLOCK beim Lesen liefen beide durch, eine nach der anderen.

Auch hier wird ewig gewartet
@@LOCK_TIMEOUT ist -1, also ohne Grenze: wie lock_timeout in PostgreSQL und nicht wie die 50 s von InnoDB. Gesetzt wird es pro Sitzung, in Millisekunden:

SET LOCK_TIMEOUT 3000;

Wenn es auslöst, kommt ein Msg 1222, und es rollt nichts zurück: Die Transaktion bleibt offen, und wer es empfängt, muss sie selbst zurückrollen. Was jeder Fehler zurückrollt, erzählt das Thema „Fehler (SQL Server)“.

Absichtlich nicht warten

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

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

NOWAIT scheitert sofort mit demselben 1222, und der Batch läuft weiter. READPAST ist das hiesige SKIP LOCKED: Mit Zeile 3 von einer anderen Sitzung gesperrt, lieferte es 1, 2, 4 und 5. So verteilt man eine Arbeitswarteschlange auf mehrere Verbraucher, ohne dass sie aufeinander warten.

NOLOCK liest, was nie existiert hat

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

Mit einer anderen Sitzung mitten in einem UPDATE, das sie danach zurückrollte, lieferte dieser Lesevorgang 50: einen Saldo, der nie bestätigt wurde. NOLOCK ist READ UNCOMMITTED, und das ist nicht „schneller lesen“: Es ist lesen, was eine andere Transaktion noch wegwerfen kann. Die Dokumentation ergänzt, dass es Zeilen überspringen oder doppelt lesen kann, wenn sich Seiten während des Lesens teilen; das habe ich nicht nachgestellt. Wenn du willst, dass Lesen nicht wartet, ist die Antwort READ_COMMITTED_SNAPSHOT: Es wartet nicht und sieht nur Bestätigtes.

Die tödliche Umarmung

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

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

Eine der beiden bekommt Msg 1205 —Level 13, „… has been chosen as the deadlock victim. Rerun the transaction.“— und verliert ihre Transaktion und den Rest des Batches; die andere bestätigt. Erkannt wird das nicht sofort: In sechs Durchläufen brauchte das Opfer zwischen 0,1 und 4,8 s, nachdem sich der Zyklus geschlossen hatte. Die Dokumentation erklärt diese Spanne: Ein Monitor sucht alle 5 s nach Zyklen, und öfter, direkt nachdem er einen gefunden hat.

Welche stirbt, lässt sich festlegen. Mit SET DEADLOCK_PRIORITY HIGH in Sitzung A war B alle drei Male das Opfer; LOW passt zu einem Batch-Job, der wiederholt werden kann. Ein 1205 ist keine Störung: Die Anwendung muss diese Transaktion wiederholen.

Der Graph ist schon gespeichert
Einzuschalten gibt es nichts: Die Extended-Events-Sitzung system_health, die ab Werk läuft, speichert jede Verklemmung mit ihrem Graphen —wer starb, welche Anweisung jede ausführte und auf welchen Schlüssel welches Index sie wartete.

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

Zwei Dinge habe ich gemessen. Die Datei hinkt hinterher: Direkt nachdem ich sechs ausgelöst hatte, enthielt sie zwei; ein paar Sekunden später alle. Und der ring_buffer derselben Sitzung, der sie sofort hat, lebt im Speicher: Nach einem Neustart des Servers hatte er die neuen und den früheren verloren, den die Datei weiter aufbewahrte.

Von der Zeile zur Tabelle: die Eskalation
Jede Sperre kostet Speicher, und wenn eine Anweisung zu viele auf einer Tabelle ansammelt, tauscht SQL Server sie gegen eine einzige auf der ganzen Tabelle. Ich habe die ersten N Zeilen einer Tabelle mit 20.000 Zeilen aktualisiert und gezählt:
- Bis 6.200 Zeilen: 6.200 X-Schlüsselsperren.
- Ab 6.300: eine, X auf der Tabelle, ohne Umweg über die Seite. Und eine andere Sitzung, die zur Zeile 20.000 wollte, die niemand angefasst hatte, wartete bis zu ihrem LOCK_TIMEOUT.

Die Zahl der Dokumentation ist 5.000; hier kam der Sprung etwas später. Ein SELECT über 8.000 Zeilen unter REPEATABLE READ endete genauso, mit einem S auf der Tabelle. Gesteuert wird das pro Tabelle:

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

Damit hielt dasselbe UPDATE aller 20.000 Zeilen 20.000 Schlüsselsperren und keine auf der Tabelle. TABLE ist der Standard; AUTO eskaliert laut Dokumentation bei einer partitionierten Tabelle auf die Partition. Ausschalten ist nicht umsonst —jede Sperre ist Serverspeicher—, und der übliche Ausweg ist ein anderer: in Stapeln von weniger als 5.000 Zeilen schreiben.

Anwendungssperren
Das Gegenstück zu den Advisory Locks von PostgreSQL: ein Name, den der Server festhält, damit zwei Prozesse deiner Anwendung nicht gleichzeitig dasselbe tun.

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

Mit der Ressource in der Hand einer anderen Sitzung lieferte es -1, statt zu warten; 0 heißt „gewährt“. Es wirft keinen Fehler: Man muss auf die Zahl schauen. Mit @LockOwner = 'Transaction', dem Standard, wird sie am Ende der Transaktion von selbst freigegeben, und außerhalb einer Transaktion angefordert lieferte es -999.

Empfehlung
Zeilen immer in derselben Reihenfolge anfassen und Transaktionen kurz halten, wie bei jeder Engine. Das Eigene hier sind drei Dinge: READ_COMMITTED_SNAPSHOT einschalten, sobald die Anwendung es verträgt, denn das lässt das Lesen nicht mehr aufs Schreiben warten und nimmt die Versuchung von NOLOCK; UPDLOCK beim Lesen, das in einem UPDATE enden wird; und die Wiederholung bei 1205 geschrieben, bevor es in Produktion geht.

Stichwörter: sperre, lock, deadlock, verklemmung, blockierung, 1205, 1222, lck_m_s, blocking_session_id, dm_tran_locks, dm_exec_requests, read_committed_snapshot, rcsi, nolock, readpast, updlock, nowait, lock_timeout, deadlock_priority, eskalation, lock_escalation, system_health, sp_getapplock

DDL ist transaktional, teuer ist die Sch-M-Sperre und die Warteschlange, die sie bildet; welche ALTER die Tabelle neu schreiben, ONLINE, RESUMABLE und WAIT_AT_LOW_PRIORITY, und warum WITH NOCHECK die Sperre hier nicht leichter macht.

Gilt für: SQL Server 2022+

Wenn du von MySQL kommst, zuerst das: DDL ist hier transaktional, wie in PostgreSQL. Wenn du von PostgreSQL kommst, ändern sich der Name der Sperre und ein paar Werkzeuge, die es dort nicht gibt. Alles, was ich hier erzähle, habe ich gegen SQL Server 2025 gemessen, auf einer Tabelle mit 200.000 Zeilen.

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

Nach diesem ROLLBACK war nichts mehr übrig: weder die Spalte noch der Index noch die Tabelle, und die Spalte hieß wieder nota. Eine ganze Migration passt in eine Transaktion. Die Ausnahmen, die ich gefunden habe: CREATE DATABASE und ALTER DATABASE (Msg 226) und ein RESUMABLE-Index (Msg 574) dürfen nicht hinein.

Teuer ist das Sch-M, und die Warteschlange, die es bildet
Jedes ALTER TABLE verlangt eine Schemaänderungssperre, Sch-M, die mit allem kollidiert, auch mit einem SELECT. Gefährlich ist nicht das ALTER, das Millisekunden dauern kann, sondern das Warten. Mit einer anderen Sitzung mitten in einer Transaktion, die eine Zeile angefasst hatte:
- Das ALTER TABLE … ADD wartete 7 s, mit wait_type LCK_M_SCH_M.
- Ein SELECT auf eine andere Zeile, dahinter gestartet, wartete 6 s mit LCK_M_SCH_S, und seine blocking_session_id war nicht die Transaktion, sondern das ALTER.

So legt eine Änderung von einer Millisekunde eine ganze Anwendung still. Das Gegenmittel ist dasselbe wie in PostgreSQL:

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

Nach 3 s bekam das ALTER Msg 1222, und das SELECT dahinter lief im selben Moment durch. Später versucht man es noch einmal.

Was die Tabelle neu schreibt und was nicht
Gemessen auf 200.000 Zeilen (1.364 Seiten). Der Zeuge ist das Transaktionsprotokoll, das jede Anweisung geschrieben hat:

So liest man sie:
- Ein Standardwert, der für alle Zeilen gleich ist —eine Konstante oder SYSDATETIME(), das einmal ausgewertet wird—, landet in den Metadaten und fasst die Zeilen nicht an. NEWID() gibt jeder Zeile einen anderen, und das zwingt dazu, alle zu schreiben.
- Ein varchar zu verbreitern kostet nichts. Es zu verkleinern, die Familie zu wechseln (nvarchar, max), eine Ganzzahl zu vergrößern, auf NOT NULL zu gehen oder die Größe eines decimal auf der Platte zu ändern schreibt neu. decimal(10,2) und decimal(12,2) belegen gleich viel, 9 Bytes, und deshalb schrieb diese Änderung nichts neu.
- Was neu schreibt, hinterlässt die Tabelle doppelt so groß. Nach jeder dieser Anweisungen ging die Tabelle von 1.364 auf 2.723 Seiten und blieb dort: Die alte Spalte belegt weiter ihren Platz in jeder Zeile. Nach int → bigint brachte ein ALTER TABLE dbo.pedidos REBUILD sie auf 1.495. Und ein DROP COLUMN ist sofort fertig, weil es nichts freigibt: 1.364 Seiten vorher und nachher, und 1.075 nach dem REBUILD.

Zwei Hindernisse, die PostgreSQL dir nicht in den Weg legt:
- Eine indizierte Spalte wechselt den Typ nicht. Mit einem Index auf estado klappte varchar(100), weil es nicht neu schreibt; varchar(30) und nvarchar(100) ergaben Msg 5074 („The index 'ix_estado' is dependent on column 'estado'“). Man löscht den Index, ändert die Spalte und legt ihn neu an.
- Ein DEFAULT ist eine benannte Einschränkung, und solange sie existiert, lässt sich die Spalte nicht löschen: dieselbe Msg 5074, die eine Einschränkung DF__pedidos__moneda__… nennt, die SQL Server selbst getauft hat. Man entfernt sie vorher mit ALTER TABLE … DROP CONSTRAINT, und deshalb lohnt es sich, ihr beim Anlegen einen Namen zu geben.

Die Dokumentation ergänzt eine Editionsbedingung: Dass ein ADD COLUMN … NOT NULL DEFAULT nur die Metadaten anfasst, ist eine Enterprise-Funktion; in Standard schreibt es die Tabelle neu. Mein Labor läuft mit Developer, das alles von Enterprise hat, deshalb konnte ich das nicht messen.

Online-Indizes: ONLINE = ON

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

Auf 3.000.000 Zeilen dauerte der Online-Neuaufbau 47 s, und ein mittendrin gestartetes UPDATE war in einer halben Sekunde fertig. Ohne ONLINE bleibt die Tabelle die ganze Zeit gesperrt. Auch ein ALTER COLUMN akzeptiert es (ALTER TABLE … ALTER COLUMN n bigint NOT NULL WITH (ONLINE = ON)): Es brauchte 395 ms statt 228, ließ die Tabelle aber bei 1.483 Seiten, statt sie zu verdoppeln.

Drei Grenzen:
- Mit einem räumlichen Index auf der Tabelle scheitert ALTER INDEX ALL … WITH (ONLINE = ON) mit Msg 153, und der räumliche Index selbst lässt sich auch nicht online neu aufbauen. Der Primärschlüssel und die normalen Indizes, einer nach dem anderen, schon.
- ONLINE heißt nicht sperrfrei: Zu Beginn verlangt es eine S-Sperre auf der Tabelle und laut Dokumentation am Ende ein kurzes Sch-M. Mit einer offenen Transaktion auf der Tabelle wartete der Online-REBUILD auf sein S, und ein UPDATE, das dahinter kam, wartete 6,6 s in der Schlange (LCK_M_IX). Ein SELECT kam durch.
- Laut Dokumentation ist ONLINE = ON Enterprise: In Standard ist es ein Fehler.

WAIT_AT_LOW_PRIORITY: warten, ohne eine Schlange zu bilden
Das hat PostgreSQL nicht. Der Neuaufbau wartet auf seine Sperre in einer eigenen Schlange, ohne denen im Weg zu stehen, die hinter ihm kommen:

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

Mit derselben offenen Transaktion lief das UPDATE dahinter in 0,5 s durch, und der REBUILD wartete sichtbar auf LCK_M_S_LOW_PRIORITY. Ist MAX_DURATION abgelaufen, entscheidet ABORT_AFTER_WAIT:
- SELF: Der REBUILD gibt mit Msg 1222 auf, und die Transaktion läuft weiter. Das ist die vorsichtige Wahl.
- BLOCKERS: Nach der Minute wurde die blockierende Sitzung getrennt („… disconnected because of a high priority DDL operation“) und ihre Transaktion zurückgerollt; der REBUILD lief zu Ende.
- NONE: Er wartet ohne Grenze mit niedriger Priorität.

ALTER INDEX … REBUILD akzeptiert es und auf meinem 2025 auch CREATE INDEX … WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY …)). ALTER TABLE nicht: Mit ADD, ALTER COLUMN oder ADD CONSTRAINT ist es ein Syntaxfehler (Msg 155). Für diese bleibt nur LOCK_TIMEOUT.

Fortsetzbare Indizes: RESUMABLE = ON

CREATE INDEX ix_relleno ON dbo.grande (relleno, g) WITH (ONLINE = ON, RESUMABLE = ON);
-- aus einer anderen Sitzung:
ALTER INDEX ix_relleno ON dbo.grande PAUSE;
SELECT name, state_desc, percent_complete FROM sys.index_resumable_operations;
ALTER INDEX ix_relleno ON dbo.grande RESUME;

Ich habe den Aufbau nach 4 s angehalten: Die Sitzung, die ihn gestartet hatte, wurde getrennt (Msg 1219), sys.index_resumable_operations zeigte PAUSED und 5 %, und den Index gab es noch nicht —er stand nicht in sys.indexes—. RESUME erledigte den Rest in 45 s. So lässt sich ein riesiger Index auf mehrere Wartungsfenster verteilen. Es verlangt ONLINE = ON (ohne das Msg 11438), passt in keine Transaktion (Msg 574) und gilt laut Dokumentation seit SQL Server 2022 auch für ALTER TABLE … ADD CONSTRAINT eines Primär- oder Unique-Schlüssels; auf 2025 habe ich es mit einem Unique-Schlüssel geprüft.

WITH NOCHECK: Hier macht es die Sperre nicht leichter
Das Muster NOT VALID + VALIDATE aus PostgreSQL hat hier seinen Zwilling, aber es tut nicht dasselbe. Auf 3.000.000 Zeilen:

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

Sie in einem Schritt anzulegen dauerte 375 ms. Und alle drei nahmen Sch-M auf der Tabelle, die Prüfung eingeschlossen: Das Aufteilen verteilt den Durchlauf, aber für die zweite Hälfte gibt es keine schwächere Sperre, wie es sie in PostgreSQL gibt.

Und es hinterlässt eine Falle: Eine mit WITH NOCHECK angelegte Einschränkung ist nicht vertrauenswürdig (is_not_trusted = 1), und der Optimierer verlässt sich nicht auf sie. Mit einem vertrauenswürdigen CHECK (importe >= 0) las WHERE importe < 0 die Tabelle gar nicht; ohne Vertrauen 1.364 logische Lesevorgänge. Dasselbe passiert, wenn man sie deaktiviert und ohne WITH CHECK wieder aktiviert —ALTER TABLE … CHECK CONSTRAINT ck_importe ließ sie nicht vertrauenswürdig—, was Massenladevorgänge gern tun. So findest du sie:

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

In Calíope
Die Operation OPTIMIZE in der Tabellenwartung ist in SQL Server ein ALTER INDEX ALL … REBUILD ohne ONLINE: Sie sperrt die Tabelle, solange sie neu aufbaut. Bei einer großen Tabelle in Produktion schreibst du den REBUILD WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY …)) besser selbst im Editor.

Empfehlung
SET LOCK_TIMEOUT vor jedem ALTER TABLE; die Migration in einer Transaktion; ONLINE = ON mit WAIT_AT_LOW_PRIORITY … SELF für Indizes; ein eigener Name für jedes DEFAULT; und Vorsicht bei Typänderungen, die die Tabelle neu schreiben und sie bis zum nächsten REBUILD doppelt so groß lassen. Musst du eine große Spalte verkleinern oder ihre Familie wechseln, ist es meist besser, die neue hinzuzufügen, in Stapeln zu kopieren und mit sp_rename umzubenennen.

Stichwörter: ddl, alter table, migration, transaktional, rollback, sch-m, lck_m_sch_m, lock_timeout, online, resumable, wait_at_low_priority, abort_after_wait, rebuild, neu schreiben, add column, alter column, drop column, with nocheck, is_not_trusted, sp_rename

Partitionsfunktion und -schema, was der Optimierer wirklich ausschließt, warum ein eindeutiger Index den Schlüssel enthalten muss, SWITCH und TRUNCATE zum Bereinigen in Millisekunden, der vertrauenswürdige CHECK, den das Laden von Daten verlangt, und SPLIT und MERGE, die nur auf einer leeren Partition kostenlos sind.

Gilt für: SQL Server 2022+

Wenn du von MySQL oder PostgreSQL kommst, ändert sich die Form: Hier ist eine Partition keine Tabelle, sondern ein Stück einer Tabelle, und die Aufteilung beschreiben zwei Objekte, die vor der Tabelle angelegt werden. Alles, was ich hier erzähle, habe ich gegen SQL Server 2025 gemessen, mit 300.000 Verkäufen über drei Jahre.

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

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

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

Die Funktion setzt die Grenzen, und das Schema sagt, in welcher Dateigruppe jedes Stück liegt. Drei Grenzen ergeben vier Partitionen: alles vor 2024, je ein Jahr in den beiden nächsten und alles ab 2026. Es gibt weder LIST noch HASH: nur Bereiche. Und die Zukunft musst du nicht vorhersehen: Was vor der ersten oder nach der letzten Grenze liegt, hat immer einen Platz.

RANGE RIGHT oder RANGE LEFT: auf welcher Seite die Grenze liegt
Mit RIGHT gehört die Grenze zur Partition rechts von ihr, und das ist bei Datumswerten das Natürliche: $PARTITION.pf_anio('2023-12-31') lieferte 1 und '2024-01-01' 2. Mit LEFT und denselben Grenzen landete der 1. Januar in der Partition von 2023. Nichts schlägt fehl: Die Daten eines Tages landen einfach im falschen Jahr. $PARTITION taugt auch zum Zählen:

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

Um den Partitionsausschluss geht es
Der tatsächliche Plan (SET STATISTICS XML ON, unter RunTimePartitionSummary) sagt, wie viele Partitionen jede Abfrage gelesen hat:

Zwei Dinge lassen sich daraus lesen:
- Auch mit Variablen und Parametern wird ausgeschlossen, weil das bei der Ausführung entschieden wird, nicht beim Kompilieren. Deshalb nennt der geschätzte Plan keine Zahl: Er zeigt [PtnId1000] >= RangePartitionNew(…), wenn der Schlüssel im Filter steht, und [PtnId1000] >= (1) AND [PtnId1000] <= (4), wenn nicht.
- Eine Funktion auf der Spalte versteckt sie, genau wie vor einem Index: YEAR(creado) las alle vier. Der von Hand geschriebene Bereich (>= '2024-01-01' AND < '2025-01-01') liest eine.

Die Entwurfsregel ist dieselbe wie in den anderen Engines: Der Partitionsschlüssel ist die Spalte, nach der du fast immer filterst. Nennen die Abfragen sie nicht, spart die Partitionierung keine Lesevorgänge: Sie verteilt sie nur.

Ausgerichtete Indizes und Eindeutigkeit
Ein Index ohne ON erbt das Schema der Tabelle: Er ist ausgerichtet, mit einem Stück pro Partition. Die Folge ist dieselbe wie in PostgreSQL:

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

lieferte Msg 1908: Die Partitionsspalte muss im Schlüssel eines eindeutigen Index stehen. Hier gibt es einen Ausweg, den PostgreSQL nicht hat —ihn nicht ausgerichtet anzulegen, ON [PRIMARY]—, aber er hat seinen Preis: Mit so einem Index funktioniert der SWITCH weiter unten nicht mehr (Msg 7733). Deshalb ist der Primärschlüssel von dbo.ventas (creado, id) und nicht (id).

SWITCH und TRUNCATE: ein Jahr in Millisekunden bereinigen
Ganz 2023 löschen, 100.009 Zeilen, gemessen innerhalb einer Transaktion:

SWITCH bewegt keine Daten: Es übergibt ein ganzes Stück an einen anderen Besitzer. Deshalb verlangt es viel von der Zieltabelle, und jede Anforderung hat ihren Fehler:
- Leer: mit einer Zeile Msg 4905.
- Gleiche Spalten und gleiche Indizes: mit einer Spalte zu viel Msg 4943.
- Und laut Dokumentation in derselben Dateigruppe wie die Partition.

Zum Laden von Daten braucht es einen vertrauenswürdigen CHECK
Hier liegt der Unterschied zu PostgreSQL, wo der CHECK nur einen Durchlauf spart. Die Tabelle ohne ihn in ihre Partition zurückzuschalten dauert nicht länger: Es wird abgelehnt, mit Msg 4982. Mit der Einschränkung ging es in 9,5 ms:

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

Und sie muss vertrauenswürdig sein: Deaktiviert und ohne WITH CHECK wieder aktiviert —was viele Massenladevorgänge tun—, lieferte der SWITCH Msg 4972. Nach WITH CHECK CHECK CONSTRAINT ging es. So lädt man einen neuen Zeitraum, ohne die laufende Tabelle anzufassen: eine separate Tabelle füllen, ihr den CHECK geben und den SWITCH ausführen.

SPLIT und MERGE: kostenlos nur auf einer leeren Partition
Die Grenzen verschiebt man über die Funktion:

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

Eine volle Partition zu teilen verschiebt die Zeilen auf einer Seite der Grenze, und jede schreibt Protokoll: Die Kosten wachsen mit dem, was sich bewegt. Die gesunde Gewohnheit ist, immer eine leere Partition am Ende zu haben und sie zu teilen, bevor die Daten des nächsten Zeitraums kommen. Und jedes SPLIT verbraucht das NEXT USED: Ohne es neu zu deklarieren, lieferte das nächste Msg 7710 und änderte nichts.

Wartung pro Partition
Neu aufgebaut wird nur die Partition, die sich ändert:

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

33 ms für eine Partition (148 ms online) gegenüber 214 ms für alle vier. Ein abgeschlossenes Jahr bekommt keine Schreibvorgänge mehr: Es gibt keinen Grund, es jede Nacht neu aufzubauen.

Sperrausweitung pro Partition
Standardmäßig (LOCK_ESCALATION = TABLE) weitete ein UPDATE von 16.440 Zeilen aus 2024 auf eine X-Sperre der ganzen Tabelle aus, und das Aktualisieren einer Zeile von 2025 aus einer anderen Sitzung lieferte Msg 1222. Mit

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

blieb die Ausweitung bei der Partition (ein X auf HOBT), und die Zeile von 2025 wurde in 9 ms aktualisiert. Die Dokumentation warnt, dass AUTO Verklemmungen zwischen Sitzungen bringen kann, die auf verschiedenen Partitionen ausweiten; der Rest zur Ausweitung steht in „Sperren und Verklemmungen (SQL Server)“.

Auch SWITCH nimmt Sch-M
Bei einer offenen Transaktion, die eine einzige Zeile angefasst hatte, wartete der SWITCH auf sein Sch-M, und ein danach gestartetes SELECT einer anderen Zeile wartete in der Schlange hinter dem SWITCH. Das Mittel ist dasselbe wie bei Indizes:

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

Das SELECT dahinter war in einer halben Sekunde fertig, und der SWITCH gab nach 61 s mit Msg 1222 auf, ohne die Transaktion anzurühren. Der Rest zu dieser Schlange steht in „Schemaänderungen im laufenden Betrieb (SQL Server)“.

Grenzen und Editionen
Eine Funktion erlaubt 15.000 Partitionen: Mit 15.000 Grenzen kommt Msg 7719. Laut Dokumentation ist die Partitionierung seit SQL Server 2016 SP1 in allen Editionen enthalten, Express eingeschlossen.

In Calíope
Zeilen und Größe einer partitionierten Tabelle summieren alle ihre Partitionen. Dem CREATE TABLE, das Calíope schreibt —dem, das es als DDL der Tabelle zeigt, und dem, das Backup speichert—, gehen Partitionsfunktion und -schema voraus, jeweils mit IF NOT EXISTS, und es endet mit ON ps_anio (creado); ein nicht ausgerichteter Index, etwa ein nicht gruppierter Primärschlüssel auf [PRIMARY], behält sein eigenes ON. Wird dieses Backup in eine leere Datenbank wiederhergestellt, ist sie genauso partitioniert, jede Zeile in ihrer Partition; hat das Ziel schon eine Funktion mit diesem Namen, wird die vorhandene genommen, mit ihren Grenzen. Schemas vergleichen schaut sehr wohl auf die Partitionierung —wo die Tabelle liegt und die Grenzen jedes Schemas—, aber sein Skript partitioniert keine bestehende Tabelle und hebt keine Partitionierung auf: Das hieße, ihren gruppierten Index mit den Daten darin neu aufzubauen, und es schreibt das als Notiz hin, damit du es machst. Der Plan, den Calíope zeigt, ist der geschätzte, also gibt er für Partitionen die PtnId1000-Form von oben; wie viele wirklich gelesen wurden, sagt nur der tatsächliche Plan, SET STATISTICS XML ON.

Empfehlung
Partitioniere nach dem, was du löschen wirst: Der echte Gewinn ist, einen Zeitraum mit SWITCH oder TRUNCATE … WITH (PARTITIONS) zu bereinigen statt mit einem DELETE, das das Protokoll füllt. Primärschlüssel und eindeutige Indizes mit dem Partitionsschlüssel darin, damit alles ausgerichtet bleibt; RANGE RIGHT bei Datumswerten; immer eine leere Partition am Ende, um sie kostenlos zu teilen; und prüfe im tatsächlichen Plan, dass deine Abfragen Partitionen ausschließen, statt es anzunehmen.

Stichwörter: partitionierung, partition, partition function, partition scheme, range right, range left, $partition, partitionsausschluss, ausgerichtet, switch, truncate, split range, merge range, next used, lock_escalation, 1908, 4982, 7733

Logische Lesevorgänge statt Zeiten, der geschätzte gegenüber dem tatsächlichen Plan, Parameter-Sniffing und seine gemessenen Gegenmittel, der Plancache, den Langsame Abfragen liest, ab Werk eingeschalteter Query Store und die Wartezeiten, die wirklich etwas sagen.

Gilt für: SQL Server 2022+

Wenn du von MySQL oder PostgreSQL kommst, ändern sich hier drei Dinge: Die Zahl, die man vergleicht, sind die logischen Lesevorgänge, nicht die Zeit; der Plan wird zwischengespeichert und mit anderen Werten wiederverwendet, und das ist die Ursache der Hälfte aller „gestern ging es noch“; und das Gegenstück zu pg_stat_statements ist schon da, ohne dass man etwas einschaltet, geht aber beim Neustart verloren. Alles, was ich hier erzähle, habe ich an SQL Server 2025 gemessen, mit 200.000 Bestellungen.

Eine Abfrage messen: logische Lesevorgänge

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

Jeder logische Lesevorgang ist eine 8-kB-Seite, die aus dem Speicher gelesen wird. Das ist die Zahl zum Vergleichen, denn die Zeit ändert sich mit der Last und dem Cache, sie nicht. Für die 20 Bestellungen eines Kunden:
- Ohne Index: 6.085 logische Lesevorgänge —die ganze Tabelle— und 12 ms.
- Mit einem Index auf cliente_id: 62 Lesevorgänge.

In Calíope erscheinen diese Zeilen nicht: Der Server schickt sie als Informationsmeldungen, und der Editor zeigt Zeilen und Fehler, keine Meldungen. Dieselbe Zahl lässt sich aus dem Plancache lesen, direkt nachdem die Abfrage gelaufen ist:

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

Bei mir kam 62 heraus, dasselbe wie bei STATISTICS IO.

Der geschätzte und der tatsächliche Plan
SET SHOWPLAN_XML ON fordert den Plan an, ohne die Abfrage auszuführen. SET STATISTICS XML ON führt sie aus und fügt hinzu, was passiert ist: ActualRows neben EstimateRows bei jedem Operator. Wenn die Anweisung schreibt, packt man sie in BEGIN TRAN … ROLLBACK, denn sie läuft wirklich.

Visual EXPLAIN fordert in Calíope den geschätzten an: den Operatorbaum mit den geschätzten Zeilen, ohne tatsächliche Zeilen, ohne Zeiten und ohne Kosten. Er zeigt die Form, einen Index Scan, wo du einen Seek erwartet hast; um Schätzung und Wirklichkeit zu vergleichen, braucht es den tatsächlichen Plan.

Parameter-Sniffing
Wenn eine Prozedur oder eine parametrisierte Abfrage zum ersten Mal läuft, schaut der Optimierer auf den mitgebrachten Wert und kompiliert den Plan dafür. Dieser Plan bleibt im Cache, und spätere Ausführungen verwenden ihn wieder, auch wenn sie einen anderen Wert mitbringen. Ich habe es mit einer Prozedur und zwei Kunden nachgestellt: einer mit 20 Bestellungen, einer mit 100.000.

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

Für den kleinen Kunden kompiliert, sucht der Plan im Index und geht Zeile für Zeile zur Tabelle: perfekt für 20 Zeilen, und 300.000 Lesevorgänge für 100.000, fünfzigmal mehr als die ganze Tabelle zu durchlaufen. Andersherum kompiliert, durchläuft der kleine die ganze Tabelle. Die Reihenfolge entscheidet, und ein Neustart oder eine Statistikänderung dreht sie um: daher das „gestern ging es noch“.

Der tatsächliche Plan verrät es ohne Raten: Im schlechten Fall stand dort EstimateRows="20" und ActualRows="100000", und bei seinen Parametern ParameterCompiledValue="(4243)" neben ParameterRuntimeValue="(1)".

Was ich als Gegenmittel ausprobiert habe:
- OPTION (RECOMPILE) am Ende der Abfrage: 62 und 6.085, jeder Wert mit seinem eigenen Plan. Der Preis ist das Kompilieren bei jedem Aufruf (hier 2-3 ms), was bei einer Abfrage, die tausendmal pro Sekunde läuft, nicht gratis ist.
- OPTION (OPTIMIZE FOR UNKNOWN): kompiliert für den Durchschnittswert, etwa 20 Zeilen, und das ergab 70 und 306.429. Wenn es einen riesigen Wert gibt, hilft es nicht.
- EXEC sp_recompile 'dbo.pedidos_de' wirft den Plan der Prozedur weg: das Mittel für den Moment, nicht die Heilung.
- Seit SQL Server 2022 kann der Server ab Kompatibilitätsgrad 160 mehrere Pläne für dieselbe Abfrage je nach Wert behalten. Das ist standardmäßig eingeschaltet, aber bei 100.000 Zeilen gegenüber 20 griff es nicht. Bei 200.000 gegenüber 1 schon: zwei Pläne. Verlass dich nicht darauf.

Welche Abfrage am meisten kostet: der Plancache
Der Server führt Buch über jeden Plan im Cache: Ausführungen, Zeit, Lesevorgänge und Zeilen. query_hash gruppiert nach der Form der Abfrage: Fünf SELECT, die sich nur in der Kundennummer unterschieden, jedes für sich, hinterließen fünf Pläne im Cache (80 kB) und einen einzigen query_hash.

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

Das zeigt Calíope unter Langsame Abfragen, Ansicht Nach Anweisung: eine Zeile pro Form, mit dem Text ihres teuersten Plans, aus allen Datenbanken des Servers. Dafür braucht es die Berechtigung VIEW SERVER STATE. Und es gibt keine Historie: Ich habe den Server neu gestartet, und die Ansicht ging von 23 Zeilen auf 0. Auch dass der Plan aus dem Cache fällt, überlebt sie nicht.

Query Store, der schon eingeschaltet ist
Query Store ist die Variante, die bleibt: Er speichert Text, Pläne und Zahlen in Intervallen in der Datenbank selbst. In SQL Server 2025 fand ich ihn eingeschaltet (READ_WRITE), in demo und in einer frisch angelegten Datenbank, ohne dass jemand danach gefragt hätte. Laut Dokumentation gilt das für neue Datenbanken seit SQL Server 2022.

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

Eine Spalte planes größer als 1 ist die Spur des Parameter-Sniffings. Zwei gemessene Einschränkungen: Nach dem Neustart waren die Texte seiner 11 Abfragen noch da, aber nicht die Zahlen von vorher, weil er sie alle 900 Sekunden auf die Platte schreibt; und er gilt pro Datenbank, nicht pro Server.

Calíope verwendet ihn nicht: Langsame Abfragen liest den Plancache, der für den ganzen Server schon da ist. Es schaltet ihn weder ein noch aus, denn das ist ein administratives ALTER DATABASE.

Wartezeiten: wohin die Zeit geht
sys.dm_os_wait_stats summiert seit dem letzten Neustart, wie lange der Server gewartet hat und warum. Es gibt 1.547 Typen, und die ersten der Liste sind ungefiltert Threads, die auf Arbeit warten: DISPATCHER_QUEUE_SEMAPHORE, BROKER_TASK_STOP, die SLEEP_…. Die muss man aussortieren. Die, die wirklich etwas sagen:
- LCK_M_…: Sperren. Mehr dazu in „Sperren und Verklemmungen (SQL Server)“.
- PAGEIOLATCH_…: Warten auf Seiten von der Platte. Es fehlt Speicher oder ein Index.
- WRITELOG: Warten auf das Transaktionsprotokoll bei jedem Commit.
- CXPACKET und CXCONSUMER: Parallelität.
- SOS_SCHEDULER_YIELD: CPU.
- RESOURCE_SEMAPHORE: Abfragen, die auf Speicher zum Sortieren oder für einen Hash warten.

Das sind Summen, also nützt ein einzelnes Foto wenig: Man nimmt zwei und zieht ab. Nach meinem Neustart fiel die Summe von 12,1 auf 0,7 Millionen ms.

Parallelität
Ab Werk steht cost threshold for parallelism auf 5 und max degree of parallelism auf 0 (alle Kerne). Die Schwelle ist so niedrig, dass schon ein mittlerer Scan parallel laufen kann: Auf einer anderen Tabelle mit 200.000 Zeilen nutzte einer 16 Threads, 2.094 ms CPU für 152 ms Uhrzeit. Ein hohes CXPACKET ist kein Fehler; es ist das Zeichen, dass diese beiden Werte so geblieben sind, wie sie kamen.

Was schon in anderen Themen steht
- Ein nvarchar-Parameter gegen eine varchar-Spalte mit SQL_-Sortierfolge geht von 3 Lesevorgängen auf 334: „Kodierung und Sortierfolgen (SQL Server)“.
- Key Lookup, wann der Plan den Index für die Tabelle aufgibt, YEAR(fecha) gegenüber dem Bereich, und die Indizes, die der Server vermisst: „Indizes (SQL Server)“.
- Eine nicht vertrauenswürdige CHECK-Einschränkung nutzt der Optimierer nicht (1.364 Lesevorgänge gegenüber keinem): „Schemaänderungen im laufenden Betrieb (SQL Server)“.
- Wie viele Partitionen eine Abfrage gelesen hat, sagt nur der tatsächliche Plan: „Partitionierung (SQL Server)“.

Empfehlung
Die Reihenfolge, die funktioniert: die Abfrage in Langsame Abfragen finden (oder in Query Store, wenn du die Historie brauchst), ihren tatsächlichen Plan mit SET STATISTICS XML ON anfordern und EstimateRows mit ActualRows vergleichen. Gehen sie auseinander und ist der kompilierte Wert nicht der aktuelle, ist es Parameter-Sniffing; gehen sie beim selben Wert auseinander, sind es die Statistiken; stimmen sie überein und es wird trotzdem viel gelesen, fehlt ein Index. Speicher oder Parallelität anzufassen, bevor man einen Plan gelesen hat, ist der lange Weg.

Stichwörter: Leistung, statistics io, statistics time, logische Lesevorgänge, showplan_xml, statistics xml, Ausführungsplan, tatsächlicher Plan, Parameter-Sniffing, parameter sniffing, recompile, optimize for unknown, sp_recompile, dm_exec_query_stats, query_hash, Plancache, query store, dm_os_wait_stats, Wartezeiten, Parallelität

Die drei Wiederherstellungsmodelle und welches das Protokoll wachsen lässt, BACKUP DATABASE und BACKUP LOG auf der Platte des Servers, die Protokollkette, mit STOPAT zur Minute vor dem DELETE zurück, COPY_ONLY, und worin sich das Backup von Calíope unterscheidet.

Gilt für: SQL Server 2022+

Wenn du von MySQL kommst: Hier gibt es weder mysqldump noch ein separates Binlog. Die physische Sicherung ist eine T-SQL-Anweisung, und die Historie, mit der man von ihr aus weitergeht, ist das Transaktionsprotokoll jeder Datenbank. Wenn du von PostgreSQL kommst, ist der Unterschied die Einheit: Gesichert und wiederhergestellt wird eine Datenbank, nicht der ganze Server. Alles, was ich hier beschreibe, habe ich auf SQL Server 2025 gemessen.

Das Wiederherstellungsmodell entscheidet fast alles
Es ist eine Eigenschaft jeder Datenbank, und es gibt drei:

Eine neue Datenbank übernimmt das Modell von model, das auf diesem Server FULL ist:

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases;

FULL ohne Protokollsicherungen füllt die Platte
Das ist die Falle. Solange die Datenbank keine erste vollständige Sicherung hat, verhält sich FULL wie SIMPLE: Ich habe 20 000 Zeilen zu 2 kB eingefügt, das Protokoll erreichte 42,2 MB belegt, und ein CHECKPOINT brachte es auf 8,1 MB herunter. Nach dem ersten BACKUP DATABASE gab dasselbe nichts mehr frei: 55,4 MB, dann 102,8 MB, und die Datei wuchs von 72 auf 136 MB. log_reuse_wait_desc sagt, warum es nicht wiederverwendet wird:

- LOG_BACKUP — wartet auf ein BACKUP LOG. Das ist der Grund für fast jede volle Platte.
- ACTIVE_TRANSACTION — eine offene Transaktion, die niemand schließt.
- CHECKPOINT — nichts zu befürchten, beim nächsten wird es frei.

BACKUP LOG senkte den belegten Platz auf 15,1 MB, aber die Datei blieb bei 136 MB: Das Protokoll zu sichern gibt Platz darin frei, nicht an die Platte zurück. Wenn eine Datenbank in FULL ist und niemand BACKUP LOG einplant, plane es ein oder wechsle zu SIMPLE.

Sichern

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

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

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

Der Pfad liegt auf der Platte des Servers, nicht auf deiner: Die Datei schreibt der SQL-Server-Prozess. Unter Linux ist der übliche Ordner /var/opt/mssql/backup/; im Testcontainer gab es ihn nicht, und das erste BACKUP hat ihn angelegt.

- Die vollständige Sicherung kopiert die ganze Datenbank: 150 MB in meinem Test.
- Die differenzielle kopiert, was sich seit der letzten vollständigen geändert hat: 3,1 MB nach 2 000 geänderten Zeilen.
- Die Protokollsicherung kopiert das Protokoll seit der vorherigen, und sie ist es, die einen Zeitpunkt erreichbar macht.

CHECKSUM ist nicht voreingestellt (backup checksum default ist 0), und das merkt man beim Prüfen: RESTORE VERIFYONLY … WITH CHECKSUM auf einer Sicherung ohne ihn ergab Msg 3187. Setz ihn immer, und prüfe:

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

Die Protokollkette
Jede Protokollsicherung beginnt, wo die vorherige endete, und sie werden alle, in Reihenfolge wiederhergestellt. Eine auszulassen ergab Msg 4305, mit der fehlenden LSN. Zwei Dinge brechen sie ohne Warnung:

- Zu SIMPLE und zurück zu FULL wechseln: danach ergab BACKUP LOG Msg 4214 bis zur nächsten vollständigen Sicherung, obwohl es eine von vorher gab.
- Ein BACKUP LOG von jemand anderem, in eine Datei, die du nicht kennst. Die Historie steht in msdb:

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

COPY_ONLY: die Kopie, die nicht stört
Eine normale vollständige Sicherung wird zur Basis der folgenden differenziellen. Machst du eine außer der Reihe —um die Datenbank auf eine andere Maschine mitzunehmen—, passt die differenzielle von heute Nacht nicht mehr zur vollständigen vom Sonntag. Mit WITH COPY_ONLY passiert das nicht: Die nächste differenzielle zeigte weiter auf die vorherige vollständige, und sie über der COPY_ONLY wiederherzustellen ergab Msg 3136. Jede Kopie außerhalb der Routine mit COPY_ONLY.

Zurück zur Minute vor dem DELETE
Ich habe es komplett nachgestellt: 500 Bestellungen, vier Sekunden später ein DELETE ohne WHERE, und die Wiederherstellung in eine neue Datenbank, ohne das Original anzufassen:

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

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

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

Alle 500 waren wieder da. Was du wissen musst:

- NORECOVERY lässt die Datenbank in RESTORING und wartet auf mehr: Sie zu lesen ergibt Msg 927. Der letzte Schritt trägt RECOVERY.
- MOVE ist Pflicht, wenn du unter anderem Namen wiederherstellst, sonst kollidiert sie mit den Dateien des Originals. Ihre logischen Namen liefert RESTORE FILELISTONLY.
- STOPAT ist Serverzeit, mit drei Nachkommastellen: mit sieben ergab es Msg 3217.
- Über die laufende Datenbank wiederherzustellen, ohne vorher das Protokollende zu sichern, ergab Msg 3159. Das Protokollende ist alles seit dem letzten BACKUP LOG; gesichert wird es mit BACKUP LOG … WITH NORECOVERY, das die Datenbank außerdem in RESTORING versetzt.

BULK_LOGGED und sein Preis
Ein SELECT … INTO einer 21-MB-Tabelle schrieb in FULL 22,1 MB Protokoll und in BULK_LOGGED 1,0 MB. Aber die nächste Protokollsicherung wog gleich viel, 22,1 MB, weil sie die geladenen Seiten mitnimmt; und diese Sicherung mit STOPAT wiederherzustellen ergab Msg 4341: In einem Intervall mit Massenladevorgang kommt man nur ans Ende, nicht zu einem Zeitpunkt. Man wechselt für den Ladevorgang zu BULK_LOGGED und gleich danach zurück zu FULL, mit einem BACKUP LOG auf beiden Seiten.

Auf einem anderen Server wiederherstellen
Eine Datenbanksicherung enthält ihre Benutzer, aber nicht die Server-Logins, die in master leben. Auf einer anderen Maschine wiederhergestellt, bleibt jeder Benutzer verwaist von seinem Login; wie man sie findet und wieder verbindet, steht im Thema zur Sicherheit von SQL Server.

Was Calíope macht
Backup schreibt ein Textskript, keine .bak: Anweisungen mit GO zwischen den Batches, Zeilen in INSERTs zu je tausend, Datumswerte in ISO 8601 und Binärwerte als 0x…, damit es auf einem anderen Server, einer anderen Version oder mit der Sitzung in einer anderen Sprache gleich zurückkommt. Mit Benutzerpasswörter einbeziehen gehen die Logins als CREATE LOGIN … WITH PASSWORD = 0x… HASHED mit, und der Benutzer jeder Datenbank als CREATE USER … FOR LOGIN: Beim Wiederherstellen des Skripts bleibt niemand verwaist. Eine partitionierte Tabelle kommt partitioniert zurück: Das Skript legt vorher, falls sie fehlen, ihre Partitionsfunktion und ihr Partitionsschema an.

Aus dem SQL-Editor kannst du ein BACKUP DATABASE starten —es ist eine Anweisung wie jede andere—, aber die Datei bleibt auf der Platte des Servers: Calíope holt sie nicht.

Empfehlung
Entscheide das Modell pro Datenbank: SIMPLE, wo der Verlust des heutigen Tages vertretbar ist, und FULL mit geplantem BACKUP LOG, wo nicht. Wöchentlich vollständig, täglich differenziell und alle paar Minuten das Protokoll ist die übliche Routine, immer WITH CHECKSUM. Behalte log_reuse_wait_desc im Blick, mach jede Einzelkopie mit COPY_ONLY, und stelle von Zeit zu Zeit wirklich wieder her, unter anderem Namen: Eine Sicherung, die nie wiederhergestellt wurde, ist eine Absicht. Das Backup von Calíope dient dazu, eine Datenbank oder einen Teil davon auf eine andere Maschine mitzunehmen; es ersetzt nicht die Sicherungen des Servers.

Stichwörter: sicherung, backup, restore, wiederherstellung, wiederherstellungsmodell, full, simple, bulk_logged, transaktionsprotokoll, log_reuse_wait_desc, backup log, protokollkette, norecovery, stopat, copy_only, differenziell, checksum, verifyonly, zeitpunkt, 3159, 4214, 4341

Server-Login und Datenbankbenutzer, warum ein Benutzer nach dem Wiederherstellen auf einem anderen Server verwaist und wie man ihn wieder verbindet, Berechtigungen pro Schema, DENY und wer daran vorbeikommt, die Besitzverkettung, eigenständige Benutzer, Sicherheit auf Zeilenebene und was mit sa zu tun ist.

Gilt für: SQL Server 2022+

Wenn du von MySQL kommst: Hier trägt das Konto den Host nicht in sich und besteht außerdem aus zwei Dingen – dem Login, das zum Server gehört und mit dem man sich anmeldet, und dem Benutzer, der zu jeder Datenbank gehört und die Berechtigungen erhält. Wenn du von PostgreSQL kommst: Es ist, als müsste jede Rolle mit LOGIN in jeder Datenbank noch einmal existieren, um sie benutzen zu können. Alles, was ich hier beschreibe, habe ich gegen SQL Server 2025 gemessen.

Login und Benutzer

CREATE LOGIN ana WITH PASSWORD = 'Xq7#lunes_pw9';   -- Server: hineinkommen
USE tienda;
CREATE USER ana FOR LOGIN ana;                    -- Datenbank: drin sein

Mit dem Login und ohne den Benutzer kommt man in den Server, aber nicht in die Datenbank: Eine Verbindung, die sie nennt, ergab „Cannot open database … The login failed“ (4060), und ein USE aus master ergab Msg 916. Der Benutzer ist mit seinem Login über die sid verbunden, nicht über den Namen – daher kommen die verwaisten Benutzer.

Das Passwort durchläuft die Richtlinie (CHECK_POLICY, standardmäßig an): acht Zeichen aus drei Klassen und ohne den Login-Namen darin. 'Xq7#ana_pw9' für das Login ana ergab Msg 33064, obwohl es den Rest erfüllt.

Verwaiste Benutzer: auf einem anderen Server wiederherstellen
Ich habe es nachgestellt: eine Datenbank mit dem Benutzer ana gesichert, das Login gelöscht und mit demselben Namen neu angelegt – wie es auf einer anderen Maschine wäre – und die Datenbank wiederhergestellt. ana kam nicht mehr in die Kopie: Ihr Benutzer behielt die alte sid, und das neue Login hatte eine andere. Ihn neu anzulegen ging auch nicht: Msg 15023, der Benutzer existiert bereits. So findest du sie:

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

Und sie wieder verbinden, ohne ihre Berechtigungen zu verlieren:

ALTER USER ana WITH LOGIN = ana;

Zwei Dinge noch. DROP LOGIN prüft nicht, ob das Login Benutzer hat: Es löschte es ohne Warnung und ließ den Benutzer in der ursprünglichen Datenbank verwaist zurück, auf demselben Server. Und damit das beim Umzug einer Datenbank auf eine andere Maschine nicht passiert, wird das Login dort mit derselben sid angelegt (CREATE LOGIN ana WITH PASSWORD = …, SID = 0x1C2F…): Damit funktionierte der Benutzer der wiederhergestellten Datenbank wieder, ohne ihn anzufassen.

Rollen
Es gibt 18 feste Serverrollen: die üblichen acht (sysadmin, serveradmin, securityadmin, processadmin, setupadmin, bulkadmin, diskadmin, dbcreator) und seit 2022 zehn feinere ##MS_…## (##MS_ServerStateReader##, um den Zustand zu sehen, ohne etwas anzufassen). In jeder Datenbank gibt es neun feste: db_owner, db_datareader, db_datawriter, db_ddladmin, db_securityadmin, db_accessadmin, db_backupoperator und die zwei, die verweigern, db_denydatareader und db_denydatawriter. Eigene Rollen legt man mit CREATE ROLE an und füllt sie mit ALTER ROLE … ADD MEMBER.

Und public, zu dem alle gehören. Auf dem Server hat es VIEW ANY DATABASE: ana sah die Namen der zehn Datenbanken des Servers, obwohl sie nur in vier hineinkam. Wenn die Namen eine Information sind, nimmt man es public weg.

Pro Schema zu berechtigen deckt auch die Tabellen von morgen ab

GRANT SELECT ON SCHEMA::rrhh TO ana;

Damit las ana die Tabellen von rrhh, und auch die, die ich danach angelegt habe. Das ist das Gegenteil von PostgreSQL, wo ON ALL TABLES nur die heutigen erreicht. Und was außerhalb des Schemas liegt, bleibt zu: dbo.pedidos ergab Msg 229.

GRANT, DENY und REVOKE: drei Zustände, nicht zwei
- GRANT gewährt.
- DENY verbietet und gilt vor jedem GRANT: Mit DENY SELECT auf rrhh.nominas bekam ana Msg 229, obwohl das Schema gewährt war, und auch als Mitglied von db_datareader.
- REVOKE löscht, was da ist, ob GRANT oder DENY. Nach dem REVOKE konnte ana wieder lesen, über die Rolle.

Wer an einem DENY vorbeikommt, habe ich einzeln gemessen:
- Ein Mitglied von db_owner: nicht, Msg 229.
- Ein Login mit CONTROL SERVER: auch nicht, Msg 229.
- Ein Mitglied von sysadmin: ja. Es betritt jede Datenbank als dbo (USER_NAME() sagt es) und es wird keine Berechtigung geprüft, also las es die verweigerte Tabelle.

Was darf ich, den Server gefragt

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

fn_my_permissions lieferte viermal SELECT: einmal für die Tabelle und einmal für jede ihrer drei Spalten. So prüft man eine wirksame Berechtigung – mit Rollen, Schemas und DENY schon aufgelöst –, ohne jemanden nach seinem Passwort zu fragen.

Die Besitzverkettung
Wenn eine View oder eine Prozedur und die Tabelle, die sie liest, denselben Besitzer haben, braucht, wer die View benutzen darf, keine Berechtigung auf die Tabelle. ana, ohne irgendetwas auf dbo.pedidos, las über die View dbo.v_totales und über die Prozedur dbo.p_total. Das ist der gute Weg, Zugriff zu geben: nur auf das, was das Objekt zeigt. Aber dynamisches SQL bricht die Kette: Dieselbe Prozedur mit einem EXEC('SELECT …') darin ergab Msg 229.

Eigenständige Benutzer
In einer eigenständigen Datenbank trägt der Benutzer sein Passwort und braucht kein Login, also reist er mit der Datenbank und verwaist nie:

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

Die Option ist standardmäßig aus. Mit ihr kam carla hinein, indem sie die Datenbank beim Verbinden nannte; ohne sie zu nennen, „Login failed“. In Calíope heißt das, die Datenbank ins Feld Datenbank der Verbindung zu schreiben.

Sicherheit auf Zeilenebene
Eine Richtlinie mit einem Filterprädikat:

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

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

ana sah ihre Zeile. Und anders als in PostgreSQL kommt hier niemand daran vorbei: sa und ein sysadmin sahen null Zeilen, weil USER_NAME() für sie dbo ist. Das Prädikat entscheidet, und wenn der Administrator alles sehen soll, muss das Prädikat es sagen. Außerdem filtert ein Filter nur, was gelesen wird: ana fügte ohne Fehler eine Zeile eines anderen Besitzers ein. Das zu verhindern braucht ein BLOCK PREDICATE.

sa
sa ist das Login mit der sid 0x01, Mitglied von sysadmin, und seinen Namen kennt jeder, der hineinzukommen versucht. Ein falsches Passwort ergibt beim Client 18456, immer mit Status 1; der Grund steht nur im Serverprotokoll, wo derselbe Versuch mit Status 8 erscheint, falsches Passwort. Üblich ist, ein Administrations-Login mit eigenem Namen anzulegen und danach ALTER LOGIN sa DISABLE.

Was Calíope nicht tut
Calíope spricht nur SQL-Authentifizierung: weder Windows-Authentifizierung noch Microsoft Entra ID.

Was Calíope tut
Benutzer listet die Logins des Servers, mit public als weiterem Konto, und sa getrennt, markiert als Systembenutzer. Die Berechtigungen werden pro Ebene gezeigt – Server, Datenbank, Schema, Tabelle, Routine –, und ein DENY wird als dritter Zustand gezeichnet: mit eigenem Symbol, durchgestrichen und mit dem Wort DENY, nicht nur in einer anderen Farbe. Man kann es nicht abwählen, denn es zu entfernen ist kein Abwählen, sondern ein REVOKE. Konto gesperrt ist ALTER LOGIN … DISABLE. Ein Datenbankbenutzer ohne Login, verwaist oder eigenständig, erscheint nicht: Für die gibt es die Abfragen oben im SQL-Editor.

Empfehlung
Ein Login pro Person oder Anwendung, und Berechtigungen pro Schema an Rollen, nicht an Personen. sysadmin nur zum Administrieren, weil es kein DENY erreicht. Was sich über eine View oder eine Prozedur geben lässt, soll dort hindurch, und nicht mit dynamischem SQL darin. Beim Umzug einer Datenbank auf eine andere Maschine: die Logins mit ihrer sid, oder die Abfrage der verwaisten Benutzer gleich nach dem Wiederherstellen. Und sa deaktiviert.

Stichwörter: sicherheit, login, benutzer, user, rolle, role, sysadmin, db_owner, db_datareader, public, grant, deny, revoke, schema, fn_my_permissions, has_perms_by_name, verwaister benutzer, orphaned user, alter user with login, sid, eigenständiger benutzer, contained database, besitzverkettung, ownership chaining, row-level security, security policy, sa, check_policy, 229, 916, 4060, 15023, 18456, 33064

sp_configure und RECONFIGURE, warum value und value_in_use auseinanderliegen, welche Optionen einen Neustart brauchen, die Speichergrenze, die nicht gesetzt ist, wo welches MAXDOP gewinnt, Konfiguration pro Datenbank, tempdb und mssql-conf.

Gilt für: SQL Server 2022+

Wenn du von MySQL kommst: Hier gibt es weder my.cnf noch SET GLOBAL. Was den Server betrifft, schreibt man mit sp_configure, und es wird mit RECONFIGURE wirksam; was eine Datenbank betrifft, mit ALTER DATABASE SCOPED CONFIGURATION. Wenn du von PostgreSQL kommst: sys.configurations ist dein pg_settings, und die Spalte, auf die es ankommt, ist nicht source, sondern der Unterschied zwischen value und value_in_use. Alles, was ich hier beschreibe, habe ich gegen SQL Server 2025 gemessen.

107 Optionen, davon 74 versteckt
sys.configurations hat auf diesem Server 107 Optionen, und sp_configure allein zeigt 33. Die übrigen 74 sind erweitert: Solange show advanced options nicht eingeschaltet ist, werden sie weder aufgelistet noch lassen sie sich ändern. sp_configure 'max degree of parallelism', 4 ergab Msg 15123, „does not exist, or it may be an advanced option“.

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

Geschrieben ist nicht angewendet: value und value_in_use
sp_configure schreibt den Wert nur, und der Server bleibt beim alten, bis RECONFIGURE kommt. Nach sp_configure 'cost threshold for parallelism', 50 stand in sys.configurations value 50 und value_in_use 5, und der Server selbst warnte: „Run the RECONFIGURE statement to install“.

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

Diese Abfrage läuft vor jedem RECONFIGURE, wegen zweier Dinge, die ich gemessen habe:
- RECONFIGURE übernimmt alles Ausstehende, nicht nur deins: Hat jemand einen Wert geschrieben und liegen lassen, geht er mit deinem hinein.
- Ein RECONFIGURE, das scheitert, lässt das Geschriebene ausstehend. Mit min server memory (MB) auf 1.024 und max server memory (MB) auf 128 ergab es Msg 5831, und die 1.024 blieben in value und warteten auf das nächste.

Einen Fall mit Passierschein gibt es: recovery interval (min) auf 120 ergab Msg 5807 („not recommended“), und RECONFIGURE WITH OVERRIDE hat es trotzdem angewendet. WITH OVERRIDE überspringt genau die Prüfung, die gerade gewarnt hat, also nimmt man es nicht aus Gewohnheit.

Die, die einen Neustart brauchen
Optionen mit is_dynamic = 0 werden mit RECONFIGURE nicht wirksam: hier 23 der 107, darunter user connections, fill factor (%), locks und priority boost. Mit sp_configure 'user connections', 500 und RECONFIGURE stand value auf 500 und value_in_use blieb bei 0, bis der Dienst neu startet.

Jede Änderung landet im Serverprotokoll, mit Uhrzeit und Sitzung: EXEC xp_readerrorlog 0, 1, N'Configuration option' listet sie mit ihrem „changed from 0 to 4“ auf.

Speicher: max server memory kommt ohne Obergrenze
max server memory (MB) steht ab Werk auf 2.147.483.647, also auf keiner Grenze: SQL Server nimmt sich Speicher für seinen Cache, bis das System ihn bittet, ihn freizugeben. Auf einem dedizierten Server setzt man eine Grenze, die dem Betriebssystem Platz lässt. Das Minimum ist 128; mit 100 ergab es Msg 15129.

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

Unter Linux gibt es darunter eine weitere Grenze, memory.memorylimitmb in mssql-conf, die laut Dokumentation ab Werk 80 % des physischen Speichers beträgt. Dieser Container sieht 6.347 MB, und mit unverändertem max server memory lag sein Ziel (committed_target_kb in sys.dm_os_sys_info) bei 5.139 MB.

Parallelität: drei Ebenen, und die nächste gewinnt
Ab Werk steht max degree of parallelism auf 0 (alle Prozessoren) und cost threshold for parallelism auf 5. Ich habe dieselbe Abfrage über 300.000 Zeilen laufen lassen und DegreeOfParallelism aus dem tatsächlichen Plan gelesen:
- Server auf 0: 15 Threads, alle Prozessoren, die er sieht.
- Server auf 4: 4.
- Datenbank auf ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 2: 2, obwohl der Server 4 sagt.
- OPTION (MAXDOP 8) an der Abfrage: 8, über Datenbank und Server hinweg.
- cost threshold for parallelism auf 50: seriell. Der Schwellenwert wird mit den Kosten des seriellen Plans verglichen (hier 7,7), nicht mit denen des parallelen.

Wie sich Parallelität in den Wartezeiten zeigt, beschreibe ich in „Leistung und Diagnose (SQL Server)“.

Konfiguration pro Datenbank
sys.database_scoped_configurations hat in demo 41 Optionen. Neben MAXDOP gibt es unter anderem LEGACY_CARDINALITY_ESTIMATION (0), PARAMETER_SNIFFING (1) und QUERY_OPTIMIZER_HOTFIXES (0). Damit ändert man das Verhalten einer Anwendung, ohne die anderen auf dem Server anzufassen.

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

tempdb
Dieser Server sieht 15 Prozessoren, und tempdb hat 8 Datendateien zu 8 MB, dazu die Protokolldatei, die in Schritten von 64 MB wachsen: Die Installation legt schon eine pro Prozessor an, bis zu acht. Sie ist die Datenbank hinter #temporären Tabellen, Sortierungen, die nicht in den Speicher passen, und den Zeilenversionen von READ_COMMITTED_SNAPSHOT.

Was nicht sp_configure ist: mssql-conf, unter Linux
Die Standardpfade für Daten, Protokoll und Sicherungen, das TLS-Zertifikat, die Speichergrenze oder die Sprache stehen in /var/opt/mssql/mssql.conf und werden mit /opt/mssql/bin/mssql-conf set … und einem Neustart geändert. Die Datei dieses Containers hat nur zwei Zeilen: TLS-Zertifikat und -Schlüssel, die SQL Server 2022 und später für striktes TLS (TDS 8.0) braucht. Unter Windows erledigt man dasselbe im SQL Server-Konfigurations-Manager.

Was Calíope zeigt
Serverinformationen listet unter Globale Variablen sys.configurations mit seinem value_in_use – was läuft, nicht was geschrieben ist und aussteht – und die Eigenschaften SERVERPROPERTY.*: Version, Edition, Sortierung. Session-Variablen sind die SET-Optionen deiner Verbindung. Calíope liest sie und ändert sie nicht: Ein sp_configure schreibst du im SQL-Editor, und nach dem RECONFIGURE erscheint es in Serverinformationen beim Aktualisieren. Langsame Abfragen zeigt in seinen Einstellungen optimize for ad hoc workloads und max server memory (MB). Und contained database authentication, die Option, die Benutzer mit eigenem Passwort zulässt, beschreibe ich in „Logins, Benutzer und Berechtigungen (SQL Server)“.

Empfehlung
Wenige, und messend. max server memory immer, mit Platz für das System; cost threshold for parallelism über 5; und MAXDOP pro Datenbank, wenn eine Anwendung es verlangt, nicht für den ganzen Server. Vor jedem RECONFIGURE die Abfrage value <> value_in_use: So wendet man an, was man will, und nicht das, was ein anderer halb fertig liegen ließ.

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

Warum ein BEGIN TRAN in einem anderen nicht verschachtelt, wie man mit SAVE TRANSACTION zurückgeht, was jede Stufe sperrt, der 3960 von SNAPSHOT und was eine vergessene Transaktion mit dem Protokoll macht.

Gilt für: SQL Server 2022+

Eine Transaktion fasst mehrere Anweisungen zu einer Einheit zusammen, die ganz oder gar nicht angewendet wird. Wie in MySQL und PostgreSQL bestätigt SQL Server ohne explizite Transaktion jede Anweisung für sich (Autocommit). Was sich ändert, steckt im Detail, und alles, was ich hier beschreibe, habe ich gegen SQL Server 2025 gemessen.

BEGIN TRAN, nicht BEGIN
Ein alleinstehendes BEGIN öffnet einen T-SQL-Block, keine Transaktion: Der Server antwortete mit Msg 102, „Incorrect syntax near 'BEGIN'“. Man schreibt BEGIN TRAN (oder BEGIN TRANSACTION). Ein COMMIT ohne offene Transaktion ergibt Msg 3902, ein ROLLBACK Msg 3903.

Der implizite Modus
Mit SET IMPLICIT_TRANSACTIONS ON öffnet die erste Anweisung, die Daten berührt, eine Transaktion, die bis zum COMMIT offen bleibt. Ich habe es gemessen: @@TRANCOUNT war 0, ein einfaches SELECT hob ihn auf 1, und erst das COMMIT brachte ihn auf 0 zurück. Eine Sitzung, die in diesem Modus das Bestätigen vergisst, behält ihre Sperren und hält das Protokoll fest, wie ich weiter unten erkläre.

Verschachteln verschachtelt nicht
Ein BEGIN TRAN innerhalb eines anderen erhöht nur den Zähler @@TRANCOUNT. Es entscheidet die äußere Transaktion:
- Das innere COMMIT bestätigt nichts: Es senkte den Zähler von 2 auf 1, und das äußere ROLLBACK machte beide UPDATE rückgängig, auch das innere.
- Das innere ROLLBACK macht alles rückgängig: Es ließ @@TRANCOUNT auf 0, und das äußere COMMIT ergab Msg 3902, weil keine Transaktion mehr da war.
- Die innere beim Namen zu nennen hilft nicht: ROLLBACK TRAN interior ergab Msg 6401, und der Zähler blieb bei 2. Zurückgehen kann man nur bis zu einem Sicherungspunkt.

BEGIN TRAN;                                  -- @@TRANCOUNT = 1
  UPDATE cuentas SET saldo = saldo - 10 WHERE id = 1;
  BEGIN TRAN;                                -- @@TRANCOUNT = 2
    UPDATE cuentas SET saldo = saldo + 10 WHERE id = 2;
  COMMIT;                                    -- @@TRANCOUNT = 1, nichts bestätigt
ROLLBACK;                                    -- macht beide UPDATE rückgängig

Eine Prozedur, die mit einem anderen @@TRANCOUNT endet, als sie beim Eintritt hatte, ergibt Msg 266: Das erkläre ich in „Fehler (SQL Server)“.

Sicherungspunkte: SAVE TRANSACTION
Um einen Teil rückgängig zu machen, ohne die Transaktion zu verlieren, markiert man einen Punkt und kehrt zu ihm zurück. Hier schlug das doppelte INSERT mit 2627 fehl, ich kehrte zum Punkt zurück, und das COMMIT bestätigte die Zeile davor und die danach:

BEGIN TRAN;
  INSERT INTO cuentas (id, saldo) VALUES (3, 0);
  SAVE TRANSACTION antes;
  BEGIN TRY
    INSERT INTO cuentas (id, saldo) VALUES (1, 0);   -- schlägt fehl: 2627
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION antes;                      -- @@TRANCOUNT bleibt 1
  END CATCH
  INSERT INTO cuentas (id, saldo) VALUES (5, 5);
COMMIT;

Zwei Grenzen, die ich gemessen habe: SAVE TRANSACTION ohne offene Transaktion ergibt Msg 628, und mit XACT_ABORT ON ließ derselbe Fehler die Transaktion zum Scheitern verurteilt zurück —XACT_STATE() -1—, und die Rückkehr zum Punkt ergab Msg 3931: Es blieb nur, sie ganz rückgängig zu machen.

Ein Fehler bricht die Transaktion fast nie ab
Anders als in PostgreSQL bricht ein Anweisungsfehler —ein doppelter Schlüssel, eine Einschränkung— nur diese Anweisung ab, und die Transaktion lebt mit dem weiter, was sie schon getan hat. Ohne XACT_ABORT bestätigt ein COMMIT am Ende, was vor und nach dem Fehler kam. Mit SET XACT_ABORT ON macht derselbe Fehler alles rückgängig und beendet den Batch. Welche Fehler die Transaktion von sich aus zurückrollen, erkläre ich in „Fehler (SQL Server)“.

Die Isolationsstufen
Voreingestellt ist READ COMMITTED. Ich habe mit zwei Sitzungen gemessen: Die erste liest vier Zeilen eines Bereichs, wartet vier Sekunden und liest sie erneut; die zweite fügt in der Zwischenzeit eine Zeile in diesen Bereich ein und aktualisiert eine der gelesenen.
- READ UNCOMMITTED liest, was andere nicht bestätigt haben: Es ist das NOLOCK aus „Sperren und Verklemmungen (SQL Server)“.
- READ COMMITTED behält die Sperren auf dem Gelesenen nicht. Ohne READ_COMMITTED_SNAPSHOT, das ab Werk aus ist, wartet ein Lesen auf den Schreibenden; das erkläre ich in „Sperren und Verklemmungen (SQL Server)“.
- REPEATABLE READ hielt fünf gemeinsame Sperren (KEY S) bis zum Ende: Das UPDATE der gelesenen Zeile wartete 3 s. Das INSERT kam aber sofort durch, und das erneute Lesen zählte 5 Zeilen statt 4: eine Phantomzeile.
- SERIALIZABLE sperrte den Bereich (KEY RangeS-S): Das INSERT wartete 3 s, und das erneute Lesen zählte weiter 4.
- SNAPSHOT liest eine Momentaufnahme vom Beginn der Transaktion und blockiert niemanden, muss aber in jeder Datenbank erlaubt werden, und ab Werk ist es aus: snapshot_isolation_state_desc war OFF in demo, in model und in einer neuen Datenbank. Ohne Erlaubnis gingen SET TRANSACTION ISOLATION LEVEL SNAPSHOT und BEGIN TRAN durch, und erst das erste SELECT ergab Msg 3952.

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

SNAPSHOT wartet nicht: Es bricht mit 3960 ab
Mit der Erlaubnis aktualisierte die zweite Sitzung die Zeile, ohne auf die erste zu warten, die sie nur gelesen hatte. Die erste las sie erneut und sah weiter den Wert ihrer Momentaufnahme, 100, obwohl die 101 der anderen schon bestätigt war. Als sie die Zeile aktualisieren wollte, brach der Server sie mit Msg 3960 („update conflict“) ab, und XACT_STATE() blieb bei -1. Unter SNAPSHOT ist ein 3960 normaler Betrieb: Die Anwendung muss die ganze Transaktion wiederholen. READ_COMMITTED_SNAPSHOT ist etwas anderes: Es wechselt nicht die Stufe, sondern ändert, wie READ COMMITTED liest, und das erkläre ich in „Sperren und Verklemmungen (SQL Server)“.

Eine vergessene Transaktion hält das Protokoll fest
Eine Sitzung, die innerhalb einer Transaktion schläft (status = sleeping, open_transaction_count = 1), verhindert, dass das Protokoll wiederverwendet wird. Ich habe 5 000 Zeilen eingefügt und gelöscht und einen CHECKPOINT ausgeführt, in einer Datenbank, in der der CHECKPOINT das Protokoll leert. Ohne diese Sitzung blieben 6,5 MB belegt; mit ihr 73,8 MB, und log_reuse_wait_desc sagte ACTIVE_TRANSACTION. Nach dem Schließen 5,1 MB. Den Rest des Protokollzyklus erkläre ich in „Sicherung und Wiederherstellung (SQL Server)“. So findet man sie:

DBCC OPENTRAN;   -- die älteste: SPID und Startzeit

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

Auch DDL wird zurückgerollt
ALTER TABLE, CREATE INDEX und CREATE TABLE gehören zur Transaktion, mit ihren Ausnahmen (CREATE DATABASE ergibt Msg 226): Das erkläre ich in „Schemaänderungen im laufenden Betrieb (SQL Server)“.

Was Calíope tut
Der SQL-Editor führt aus, was du ihm gibst, auf einer einzigen Verbindung, Anweisung für Anweisung, und hält bei der ersten an, die fehlschlägt. Ohne Transaktion aus dem Menü gibt er am Ende die Verbindung an den Pool zurück, und wenn eine Transaktion offen geblieben ist, rollt er sie zurück (IF @@TRANCOUNT > 0 ROLLBACK): Ein von Hand geschriebenes BEGIN TRAN und sein COMMIT gehören in dieselbe Ausführung, und was vor einem Fehler kam, wird beim Zurückgeben der Verbindung zurückgerollt. In sqlcmd oder in einer Anwendung wäre es bestätigt worden.
Für eine Transaktion über mehrere Ausführungen gibt es das Menü Transaktion in der Leiste: BEGIN sendet BEGIN TRANSACTION und reserviert eine Verbindung für den Tab, und was du dort ausführst, bleibt darin bis zum COMMIT oder ROLLBACK. Den Tab zu schließen rollt sie zurück. Rollt der Server sie selbst zurück — ein 245, ein 1205 oder jeder Fehler unter XACT_ABORT — oder beendet ein COMMIT in deinem SQL sie, erlischt das Label Aktive Transaktion, und die Statusleiste sagt es: Ab dann wird jede Anweisung sofort festgeschrieben.

Empfehlung
SET XACT_ABORT ON in jedem Batch und jeder Prozedur, die schreibt, und ein TRY…CATCH, das mit IF @@TRANCOUNT > 0 ROLLBACK; und THROW; endet. Verschachtle kein BEGIN TRAN in der Erwartung, dass das innere unabhängig ist: Dafür gibt es Sicherungspunkte. Wenn du auf SNAPSHOT wechselst, schreib die Wiederholung für 3960, bevor du wechselst. Und ab und zu DBCC OPENTRAN: Eine vergessene Sitzung warnt nicht, bis die Platte voll ist.

Stichwörter: Transaktion, commit, rollback, begin tran, @@trancount, save transaction, Sicherungspunkt, xact_abort, xact_state, implicit_transactions, Isolation, repeatable read, serializable, snapshot, allow_snapshot_isolation, 3960, 3952, 3902, 6401, 3931, active_transaction, dbcc opentran

Ganzzahlen mit der Größe im Namen und der UInt64, der nicht in JSON passt, exaktes Decimal, String als Bytes, die Bereiche der Datumstypen, und Nullable und LowCardinality als Hüllen, die etwas kosten.

Gilt für: ClickHouse 25.3+

In ClickHouse entscheidet der Typ, wie viel Platz jeder Wert auf der Platte braucht und wie er sich komprimiert; man wählt ihn also sorgfältiger als in einer transaktionalen Datenbank. Die Namen tragen die Größe in sich und unterscheiden Groß- und Kleinschreibung: UInt64, nicht uint64.

Ganzzahlen: die Größe steht im Namen
Von Int8 bis Int256 und von UInt8 bis UInt256. Ein Überlauf ist kein Fehler: Der Wert läuft um. toInt8(300) liefert 44 und toUInt8(-1) liefert 255.

Das Maximum eines UInt64 ist 18446744073709551615, und in JSON ist eine Zahl nur bis 9007199254740991 (2^53 − 1) exakt. Deshalb schickt der Server 64-Bit-Ganzzahlen in seinen JSON-Formaten in Anführungszeichen (output_format_json_quote_64bit_integers), und Calíope liest sie als exakten Text, nie als Gleitkommazahl.

Decimal: exakt, mit fester Skala
Decimal(P, S) speichert P Stellen, davon S Nachkommastellen, ohne die Rundungen der Gleitkommazahlen. Die Division verwirft, was nicht in die Skala passt: toDecimal32(1, 2) / 3 ergibt 0.33. Die Multiplikation addiert die Skalen: Zwei Decimal(9, 2) ergeben ein Decimal(9, 4).

String sind Bytes, keine Zeichen
String nimmt jede Bytefolge an, auch eine, die kein UTF-8 ist. length('ñandú') ergibt 7 (Bytes) und lengthUTF8('ñandú') ergibt 5. FixedString(N) füllt mit Nullen auf: toFixedString('ab', 4) speichert 61 62 00 00. Ist ein Text kein gültiges UTF-8, zeigt Calíope ihn bereinigt an und markiert die Zelle.

Datumstypen: unterschiedliche Bereiche, und kein Fehler beim Überschreiten

Außerhalb seines Bereichs bleibt Date ohne Warnung an der Grenze stehen: toDate('2149-06-07') liefert 2149-06-06 und toDate('1969-12-31') liefert 1970-01-01. Für Geburtsdaten oder historische Daten nimmt man Date32.

Die Zeitzone gehört zur Spalte, nicht zu jeder Zeile: DateTime64(3, 'Asia/Tokyo'). Ohne Zeitzone gilt die des Servers, und Calíope schreibt sie in den Spaltenkopf.

Nullable und LowCardinality: Hüllen mit unterschiedlichen Kosten
Nullable(T) hält eine eigene Null-Maske. Bei einer Million Zeilen belegte Int64 unkomprimiert 7,63 MiB und Nullable(Int64) 8,58 MiB. Außerdem darf eine Nullable-Spalte nicht im Sortierschlüssel stehen, solange allow_nullable_key nicht eingeschaltet ist. Lässt sich das Fehlen eines Werts mit einem Standardwert ausdrücken (0, leerer String), verzichtet man besser auf Nullable.

LowCardinality(T) kodiert mit einem Wörterbuch. Mit 100 verschiedenen Werten in einer Million Zeilen belegte die String-Spalte komprimiert 254 KiB und die LowCardinality(String)-Spalte 7,6 KiB. Die Dokumentation des Servers selbst empfiehlt es unter etwa 10 000 verschiedenen Werten und rät über 100 000 davon ab.

Sammlungen und strukturierte Typen
- Array(T) beginnt bei 1 und liefert außerhalb des Bereichs den Standardwert, keinen Fehler: [10,20,30][5] ergibt 0.
- Map(K, V) erlaubt wiederholte Schlüssel: map('a',1,'a',2) behält beide, und ['a'] liefert den ersten.
- Enum8 weist beim Einfügen einen Namen ab, der nicht in seiner Liste steht. UUID belegt 16 Bytes, IPv4 4.

Calíope zeigt Sammlungen als kompaktes JSON, mit den Zahlen genau so, wie sie ankamen, und ein Aggregationszustand (AggregateFunction) sagt, dass er sich nicht als Text zeigen lässt.

Stichwörter: Typen, uint64, int64, decimal, string, fixedstring, date, date32, datetime, datetime64, Zeitzone, nullable, lowcardinality, array, map, tuple, enum, uuid, ipv4, json, clickhouse

Die MergeTree-Familie und was jede Variante beim Zusammenführen tut, die Replicated-Engines, die Engines ohne eigene Daten oder mit Daten im Speicher, und warum sich die Engine nicht ändern lässt.

Gilt für: ClickHouse 25.3+

In ClickHouse deklariert jede Tabelle ihre Engine, und die Engine entscheidet, wie die Daten gespeichert werden, welche Indizes sie annimmt, ob sie repliziert und was beim Zusammenführen passiert. Das ist kein Performance-Detail, sondern Teil der Identität der Tabelle. Der Server veröffentlicht die 84, die er kennt, in system.table_engines, jede mit dem, was sie unterstützt.

Die MergeTree-Familie, die man fast immer will
Die Daten werden in Parts geschrieben, sortiert nach dem Sortierschlüssel, und der Server führt sie im Hintergrund zusammen. Die Varianten tun beim Zusammenführen etwas mehr, und nur dann:

Der Haken ist das „nur dann“. Gemessen mit ReplacingMergeTree: Zwei Einfügungen mit demselben Schlüssel ergeben in einem normalen SELECT 2 Zeilen; mit FINAL ergeben sie 1, und nach OPTIMIZE TABLE … FINAL 1 ohne FINAL. SummingMergeTree verhält sich genauso: Zwei Zeilen ('x', 1) und ('x', 2) bleiben zwei bis zum Zusammenführen und werden dann eine mit 3. Abfragen auf diese Engines müssen aggregieren oder FINAL verwenden, denn das Zusammenführen hat keinen Zeitplan.

Replicated, dieselbe Familie mit Kopien
ReplicatedMergeTree und seine Varianten (ReplicatedReplacingMergeTree…) fügen Replikate hinzu, die ClickHouse Keeper koordiniert. Sie sind die einzigen in der Liste, die Replikation und Deduplizierung von Einfügungen unterstützen (supports_replication, supports_deduplication).

Die, die nicht zu MergeTree gehören
- Memory: im RAM und unkomprimiert. Laut ihrer Beschreibung auf dem Server ist die Tabelle nach einem Neustart leer.
- Log und Verwandte: kleine Tabellen zum einmaligen Schreiben, ohne Indizes.
- Buffer: hält Daten im Speicher und schreibt sie in eine andere Tabelle; heute empfiehlt man stattdessen asynchrone Einfügungen.
- Distributed: speichert nichts; verteilt die Abfrage auf die Shards eines Clusters.
- Merge: liest mehrere Tabellen auf einmal und nimmt keine Schreibvorgänge an.
- Dictionary: zeigt ein Wörterbuch als Tabelle.
- Null: Was man schreibt, wird verworfen. Dient als Eingang einer materialisierten View.

Keine davon unterstützt Skip-Indizes, Projektionen, einen Sortierschlüssel oder TTL: Laut system.table_engines gehört all das nur zur MergeTree-Familie.

Die Engine lässt sich nicht ändern
Es gibt kein ALTER TABLE … MODIFY ENGINE: Der Server antwortet mit einem Syntaxfehler. Um sie zu ändern, legt man eine andere Tabelle mit der neuen Engine an und kopiert die Daten mit INSERT INTO … SELECT, oder man kopiert Partitionen mit ATTACH PARTITION … FROM, wenn Spalten und Partitionierung übereinstimmen: gemessen von einem ReplacingMergeTree in einen MergeTree.

In Calíope
Der Builder bietet die Engines an, die der Server ohne Argumente anlegen kann, und beim Ändern einer Tabelle sagt er, dass sich die Engine nicht ändern lässt. Kataloge zeigt die Engine jeder Tabelle.

Stichwörter: Tabellen-Engine, engine, mergetree, replacingmergetree, summingmergetree, aggregatingmergetree, collapsingmergetree, replicated, memory, log, buffer, distributed, dictionary, merge, null, final, clickhouse

Der Sortierschlüssel als dünnbesetzter Index aus Granulen, der Primärschlüssel, wenn er abweicht, SAMPLE BY, Skip-Indizes und Projektionen, und warum sich der Schlüssel nicht ändern lässt.

Gilt für: ClickHouse 25.3+

In ClickHouse gibt es keine Indizes, die auf eine Zeile zeigen. Wie viel gelesen wird, entscheidet der Sortierschlüssel: Die Daten werden danach sortiert gespeichert, und der Primärindex ist dünnbesetzt: Er hält eine Marke pro Zeilenblock, nicht eine pro Zeile.

Granulen: die Leseeinheit
Jeder Block heißt Granule und hat standardmäßig 8 192 Zeilen (index_granularity). Der Server verwirft ganze Granulen und liest den Rest. Bei einer Tabelle mit einer Million Zeilen und ORDER BY (fecha, cliente, id) gibt es 123 Granulen, und EXPLAIN sagt es:

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

Mit der ersten Spalte des Schlüssels zeigt der Plan Granules: 3/123 und eine binäre Suche. Filtert man nur nach cliente, der zweiten Spalte, zeigt er Granules: 101/123: fast die ganze Tabelle. Die Reihenfolge der Spalten im Schlüssel zählt: zuerst die, nach denen am häufigsten gefiltert wird und die am wenigsten verschiedene Werte haben.

Der Primärschlüssel kann kürzer sein als der Sortierschlüssel
PRIMARY KEY muss ein Präfix von ORDER BY sein. Schreibt man ihn nicht, sind beide gleich. Mit ORDER BY (fecha, cliente, id) PRIMARY KEY (fecha, cliente) werden die Zeilen nach den drei Spalten sortiert, und der Index im Speicher hält nur zwei: weniger Speicher, dieselbe Reihenfolge auf der Platte. Der Primärschlüssel ist nicht eindeutig: Zwei Zeilen mit demselben Schlüssel bestehen nebeneinander.

SAMPLE BY
Damit fragt man einen Bruchteil der Daten ab (SELECT … SAMPLE 0.1). Der Stichprobenausdruck muss im Primärschlüssel stehen, sonst weist der Server das CREATE ab, und meist ist er ein Hash: ORDER BY intHash32(id) SAMPLE BY intHash32(id). Gemessen: SAMPLE 0.1 über 100 000 Zeilen zählte 10 039.

Skip-Indizes: Sie überspringen, sie suchen nicht
Sie halten eine Zusammenfassung pro Gruppe von Granulen und dienen dazu, die zu überspringen, die das Gesuchte sicher nicht enthalten:
- minmax: das Minimum und das Maximum.
- set(N): bis zu N verschiedene Werte.
- bloom_filter: ungefähre Zugehörigkeit; für Gleichheit auf Spalten außerhalb des Schlüssels.
- ngrambf_v1 und tokenbf_v1: um Fragmente oder Wörter in einem Text zu suchen.

Gemessen auf derselben Tabelle: Die Suche nach einer bestimmten URL mit einem bloom_filter las 20 von 123 Granulen statt aller 123. Und ein minmax auf einem Betrag, der über die ganze Tabelle verteilt ist, verwarf keine einzige: Ein Skip-Index hilft nur, wenn die Werte entlang des Schlüssels gruppiert sind.

Projektionen: die Tabelle noch einmal, in anderer Reihenfolge
Eine Projektion speichert die Daten noch einmal, anders sortiert, und der Server wählt sie selbst, wenn sie hilft:

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

Dieselbe Abfrage nach cliente ging von 101 auf 1 von 123 Granulen zurück. Dafür belegte die Projektion 5,3 MiB mehr, und jede Einfügung schreibt die Daten zweimal.

Der Sortierschlüssel lässt sich nicht ändern
ALTER TABLE … MODIFY ORDER BY erlaubt nur, am Ende Spalten anzuhängen, die im selben ALTER angelegt werden. Umsortieren oder eine bereits vorhandene Spalte aufnehmen ist ein Fehler (Existing column … is used in the expression that was added to the sorting key). Den Schlüssel wirklich zu ändern heißt, eine andere Tabelle anzulegen und die Daten zu kopieren.

In Calíope
In ClickHouse heißt der Tab „Indizes“ der Kataloge „Schlüssel“ und zeigt den Sortierschlüssel, den Partitionsschlüssel, den Primärschlüssel, falls er abweicht, den Stichprobenschlüssel, die Skip-Indizes und die Projektionen. Das visuelle EXPLAIN zählt die Granulen, die jeder Lesevorgang liest, und der Index-Berater schlägt Skip-Indizes vor, nicht CREATE INDEX.

Stichwörter: Sortierschlüssel, order by, primary key, Primärindex, Granule, index_granularity, sample by, Skip-Index, data skipping index, minmax, set, bloom_filter, ngrambf_v1, tokenbf_v1, Projektion, projection, explain, clickhouse

PARTITION BY und die Parts, warum Partitionieren nach Tag ein häufiger Fehler ist, OPTIMIZE und DROP PARTITION, und die TTL von Zeilen und Spalten, die beim Zusammenführen greift.

Gilt für: ClickHouse 25.3+

In ClickHouse ist eine Partition eine Gruppe von Parts, die denselben Wert von PARTITION BY haben, und ihre Hauptaufgabe ist die Datenverwaltung: einen ganzen Monat auf einmal löschen oder verschieben. Um Abfragen zu beschleunigen, gibt es schon den Sortierschlüssel; Partitionieren ersetzt ihn nicht.

Parts und Partitionen
Jede Einfügung schreibt mindestens einen Part pro berührter Partition, und der Server führt sie später zusammen, immer innerhalb derselben Partition. system.parts listet sie auf, und nur die mit active = 1 zählen: Zusammengeführte bleiben eine Weile inaktiv, bevor sie gelöscht werden.

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

Gemessen: Zwei Einfügungen von 300 000 Zeilen über neun Monate hinterließen 18 aktive Parts in 9 Partitionen.

Warum Partitionieren nach Tag ein häufiger Fehler ist
Viele Partitionen bedeuten viele kleine Parts, und Parts kosten: Ab mehr als 1 000 aktiven in einer Partition beginnt der Server, Einfügungen zu verzögern (parts_to_delay_insert), und bei 3 000 weist er sie ab (parts_to_throw_insert). Außerdem darf ein einzelnes INSERT nicht mehr als 100 Partitionen berühren (max_partitions_per_insert_block). Gemessen mit PARTITION BY fecha: Ein INSERT mit 200 verschiedenen Tagen schlug mit Code: 252 … Too many partitions for single INSERT block (more than 100) fehl. Üblich ist nach Monat (toYYYYMM), und gar nicht, wenn die Tabelle klein ist.

Nach Partition zusammenführen und löschen

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

OPTIMIZE … PARTITION … FINAL führt die Parts dieser Partition zu einem zusammen. DROP PARTITION löscht die ganze Partition, indem es ihre Dateien entfernt: gemessen 5 ms. Den folgenden Monat mit ALTER TABLE … DELETE WHERE zu löschen dauerte 54 ms, weil eine Mutation Parts neu schreibt, statt sie fallen zu lassen, und der Unterschied wächst mit der Größe.

TTL: Ablauf, der beim Zusammenführen greift

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

Die TTL der Tabelle löscht abgelaufene Zeilen, und die einer Spalte setzt ihren Wert auf den Standardwert zurück. Beides geschieht beim Zusammenführen, weder beim Einfügen noch zur genauen Uhrzeit. Gemessen: Eine zwei Jahre alte Zeile, in diese Tabelle eingefügt, war beim Abfragen noch da und verschwand nach OPTIMIZE … FINAL. Ohne es zu erzwingen, prüft der Server den Ablauf alle 4 Stunden (merge_with_ttl_timeout); eine TTL ist also kein punktgenaues Löschen: Abgelaufene Daten können eine Weile sichtbar bleiben.

In Calíope
Der Tab „Schlüssel“ der Kataloge zeigt den Partitionsschlüssel jeder Tabelle. Serverinformationen summiert unter „HDD-Nutzung“ die aktiven Parts aus system.parts und zeigt die Kompression. Tabellenwartung bietet OPTIMIZE beschränkt auf eine Partition, mit FINAL standardmäßig aus, weil es die ganze Partition neu schreibt.

Stichwörter: Partition, partition by, Parts, system.parts, active, parts_to_throw_insert, max_partitions_per_insert_block, optimize, drop partition, ttl, Ablauf, toyyyymm, Zusammenführen, clickhouse

Ein Einfüge-Trigger, keine zwischengespeicherte Tabelle: TO gegenüber der internen Tabelle, POPULATE, aktualisierbare Views mit system.view_refreshes, und warum sie weder UPDATE noch DELETE sehen.

Gilt für: ClickHouse 25.3+

Eine materialisierte View in ClickHouse ist keine gespeicherte Abfrage mit zwischengespeichertem Ergebnis. Sie ist ein Einfüge-Trigger: Jedes Mal, wenn ein Zeilenblock in der Quelltabelle ankommt, führt die View ihr SELECT auf diesem Block aus und schreibt das Ergebnis in eine Zieltabelle. Was vorher da war und was sich später ändert, sieht sie nicht.

Mit TO oder mit einer internen Tabelle

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

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

Mit TO ist das Ziel eine gewöhnliche Tabelle, vorher angelegt und mit eigenem Namen. Ohne TO trägt die View ihre eigene ENGINE, und der Server legt eine versteckte Tabelle namens .inner_id.<uuid> an, mit der uuid der View. Gemessen: Die View ohne TO legte .inner_id.ed264ea3-… an, und ihre uuid in system.tables war genau diese. Mit TO weiß man, wo die Daten liegen, und kann die View löschen, ohne sie zu verlieren; deshalb ist das der empfohlene Weg.

Was vorher da war, kommt nicht hinein (außer mit POPULATE)
Gemessen: Mit zwei Zeilen schon in der Quelle entstanden die View mit TO und die mit interner Tabelle leer. Die mit POPULATE angelegte entstand mit beiden. Danach erreichte eine neue Einfügung alle drei. Die ClickHouse-Dokumentation warnt, dass verloren gehen kann, was in die Quelle eingefügt wird, während POPULATE die View füllt; bei laufendem Verkehr legt man besser die View an und füllt das Ziel von Hand mit INSERT INTO … SELECT.

Mutationen: Die View erfährt nichts davon
Eine View reagiert nur auf Einfügungen. Gemessen: Nach ALTER TABLE origen UPDATE v = 1000 WHERE k = 'a' summierte die Quelle für a 2 000, und das Ziel blieb bei 10. Nach einem DELETE der Zeilen von c verlor die Quelle sie, und das Ziel behielt seine Summe von 5. Wird die Quelle korrigiert, berechnet man das Ziel von Hand neu.

Auch das Ziel führt zusammen
Ist das Ziel ein SummingMergeTree oder ein AggregatingMergeTree, hinterlässt jede Einfügung Teilzeilen, die beim Zusammenführen zusammenkommen. Abfragen auf das Ziel aggregieren (sum(total), -Merge), statt einzelne Zeilen zu lesen.

Aktualisierbare Views: die andere Art

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

Eine View mit REFRESH funktioniert tatsächlich wie eine zwischengespeicherte Tabelle: Sie führt in jedem Intervall die ganze Abfrage neu aus und ersetzt das Ergebnis, sieht also die ganze Quelle, auch Änderungen durch Mutationen. system.view_refreshes nennt ihren Zustand (Scheduled, Running…), die letzte erfolgreiche Aktualisierung und die nächste. Gemessen mit ClickHouse 26.8: Es funktioniert ohne experimentelle Einstellung.

In Calíope
In ClickHouse haben die Kataloge den Tab „Materialisierte Views“: aus welcher Tabelle jede liest, in welche sie schreibt (und, falls es die interne ist, unter welchem Namen man sie abfragt), Größe und Zeilen des Ziels, die zuletzt geschriebene Zeile und, wenn sie aktualisierbar ist, ihr Zeitplan und ihre Aktualisierungen. Der Baum des Editors gruppiert sie getrennt von den gewöhnlichen Views.

Stichwörter: materialisierte View, materialized view, to, inner_id, populate, refresh every, aktualisierbar, view_refreshes, Trigger, summingmergetree, Mutation, clickhouse

UPDATE und DELETE als asynchrones Neuschreiben von Parts, system.mutations und der Fehlergrund, KILL MUTATION, mutations_sync, das leichte DELETE und warum eine Mutation keine Zeilen zählt.

Gilt für: ClickHouse 25.3+

ClickHouse ändert eine Zeile nicht an Ort und Stelle. ALTER TABLE … UPDATE und ALTER TABLE … DELETE sind Mutationen: Der Server vermerkt sie, antwortet sofort und schreibt danach jeden betroffenen Part im Hintergrund neu, vollständig, auch wenn sich nur eine Zeile ändert.

Was beim Ausführen passiert

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

Gemessen an einer Tabelle mit zwei Parts zu 50 000 Zeilen: Die Anweisung kam nach 10 ms zurück, mit parts_to_do = 2 und is_done = 0. Am Ende waren die Parts all_1_1_0 und all_2_2_0 durch all_1_1_0_3 und all_2_2_0_3 ersetzt: je 50 000 Zeilen neu geschrieben, um zehn zu ändern. Die alten Parts bleiben inaktiv auf der Platte, bis old_parts_lifetime abgelaufen ist (standardmäßig 480 s).

Warten oder nicht
mutations_sync ist standardmäßig 0: Die Anweisung wartet nicht. Mit 1 wartet sie auf das aktuelle Replikat, mit 2 auf alle. Gemessen: Mit mutations_sync = 2 sah das nächste SELECT bereits den neuen Wert.

Wenn eine Mutation fehlschlägt
Eine fehlgeschlagene Mutation wird weder rückgängig gemacht noch verworfen: Sie wird immer wieder versucht und blockiert die nachfolgenden auf dieser Tabelle. Gemessen: Nach einer Mutation mit einer Division durch null blieb die nächste unangewendet, bis die erste entfernt war. Der Grund steht in system.mutations.latest_fail_reason (Code: 153 … Division by zero), und entfernt wird sie mit:

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

Was die Mutation schon neu geschrieben hatte, wird nicht zurückgenommen.

Eine Mutation zählt keine Zeilen
Der Server zählt die betroffenen Zeilen nicht, wenn er eine Mutation einreiht: In system.query_log erscheint sie mit written_rows = 0. Jede Zeilenzahl, die für eine Mutation angezeigt würde, wäre erfunden.

Das leichte DELETE
DELETE FROM tabelle WHERE … schreibt keine Parts neu: Es markiert die Zeilen als gelöscht, blendet sie in Abfragen aus, und entfernt werden sie wirklich beim nächsten Merge. Gemessen: Von 100 000 Zeilen ließ DELETE FROM … WHERE id < 3 99 997 sichtbar, und mit apply_deleted_mask = 0 waren noch alle 100 000 da. lightweight_deletes_sync ist standardmäßig 2: Es wartet, bis die Markierung alle Replikate erreicht hat.

In Calíope
Eine Zelle bearbeiten oder eine Zeile löschen schreibt bei ClickHouse ALTER TABLE … UPDATE oder ALTER TABLE … DELETE, und der Editor meldet, dass die Mutation eingereiht wurde, mit ihrer Kennung statt einer Zeilenzahl. Prozesse zeigt nicht abgeschlossene Mutationen mit ihrem Fehlergrund als Text und erlaubt, sie abzubrechen.

Stichwörter: Mutation, alter table update, alter table delete, system.mutations, latest_fail_reason, kill mutation, mutations_sync, delete from, leichtes Löschen, lightweight delete, lightweight_deletes_sync, parts_to_do

Es gibt keine Transaktionen: was ein INSERT garantiert, warum es über Blöcke hinweg nicht atomar ist, die Deduplizierung von Blöcken und wie ein Ladevorgang ohne BEGIN idempotent wird.

Gilt für: ClickHouse 25.3+

ClickHouse hat keine Transaktionen. BEGIN TRANSACTION antwortet mit Code: 48 … Transactions are not supported (NOT_IMPLEMENTED), und implicit_transaction = 1 ebenso. Es gibt weder COMMIT noch ROLLBACK: Jede Anweisung steht für sich.

Was ein INSERT garantiert
Ein INSERT wird in Blöcken geschrieben (max_insert_block_size, standardmäßig 1 048 449 Zeilen), und jeder Block ist atomar: Er kommt ganz hinein oder gar nicht. Über Blöcke hinweg gibt es keine Garantie. Gemessen: Ein INSERT … SELECT von 400 000 Zeilen in Blöcken zu 100 000, das bei Zeile 250 000 fehlschlug, hinterließ 100 000 geschriebene Zeilen. Ein fehlgeschlagenes INSERT kann die Tabelle halb gefüllt lassen, und nichts macht es rückgängig.

Deduplizierung von Blöcken
Replicated*-Tabellen merken sich den Hash der zuletzt eingefügten Blöcke (replicated_deduplication_window, 10 000) und verwerfen einen identischen Block, der noch einmal kommt. Gemessen: Dasselbe INSERT zweimal in eine ReplicatedMergeTree ergab 2 Zeilen, nicht 4. In einer normalen MergeTree ist das Fenster 0 (non_replicated_deduplication_window), und dasselbe INSERT zweimal ergab 4.

Einen Ladevorgang idempotent machen
- Den Stapel mit denselben Blöcken wiederholen: Bei Replicated* verwirft die Deduplizierung die Wiederholung.
- Den Stapel mit insert_deduplication_token benennen. Gemessen an einer MergeTree mit Fenster 100: Ein zweites INSERT mit demselben Token und anderen Daten kam nicht hinein.
- In eine Hilfstabelle laden und die ganze Partition auf einmal mit ALTER TABLE … REPLACE PARTITION … FROM austauschen (laut Serverdokumentation).
- ReplacingMergeTree verwenden und mit FINAL lesen, wenn doppelte Zeilen bis zum nächsten Merge vertretbar sind.

In Calíope
Bei ClickHouse bietet der Editor das Menü „Transaktion“ nicht an: Es gibt nichts zu öffnen oder zurückzunehmen, und jede Anweisung ist erledigt, sobald sie läuft.

Stichwörter: Transaktion, transaction, begin, commit, rollback, Atomarität, insert, Block, max_insert_block_size, Deduplizierung, insert_deduplicate, insert_deduplication_token, replicated_deduplication_window, idempotent, not_implemented

PREWHERE, die Kosten von FINAL, gelesene Granulen im EXPLAIN, max_threads und max_memory_usage, system.query_log mit log_comment und die drei Abfragen, die den Schlüssel nicht nutzen.

Gilt für: ClickHouse 25.3+

Bei ClickHouse entscheidet sich fast die ganze Leistung daran, wie viel gelesen wird: wie viele Granulen der Schlüssel überspringt und wie viele Spalten geholt werden müssen.

Granulen im EXPLAIN

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

Bei einer Million Zeilen mit ORDER BY (fecha, cliente, id) meldete der Plan Granules: 5/123: Von 123 Granulen las er 5. Dieses Verhältnis ist das Erste, was man ansieht.

Die drei Abfragen, die den Schlüssel nicht nutzen
Gemessen an derselben Tabelle:
- Filtern nach einer Spalte, die nicht im Schlüssel ist (url LIKE '%/p/12%'): 123/123.
- Ein OR, das eine Schlüsselspalte mit einer anderen mischt (fecha = … OR url = …): 123/123.
- Filtern nur nach der zweiten Spalte des Schlüssels (cliente = 42): 101/123.

PREWHERE
Mit PREWHERE liest der Server zuerst die Filterspalten und holt die übrigen nur für die Zeilen, die durchkommen. optimize_move_to_prewhere ist 1 und erledigt das selbst. Gemessen mit einem Filter auf url, der 20 Zeilen liefert, und einer 200-Byte-Spalte im SELECT: 30 MiB gelesen mit der Optimierung und 204 MiB ohne.

FINAL kostet
FINAL führt die doppelten Zeilen einer ReplacingMergeTree beim Lesen zusammen. Gemessen an zwei Millionen Zeilen mit einer Million Schlüssel: sum(v) ohne FINAL brauchte 42 ms und zählte die Duplikate mit; mit FINAL 323 ms und 42 MiB Speicher statt 5,5.

Threads und Speicher
max_threads ist auto(N), einer pro Kern (8 auf dem gemessenen Server). max_memory_usage ist 0, ohne Grenze pro Abfrage; mit einer Grenze schlägt die Abfrage, die sie überschreitet, mit Code: 241 … Query memory limit exceeded fehl.

system.query_log
Jede Abfrage hinterlässt dort ihre Dauer, read_rows, read_bytes und memory_usage, und log_comment markiert sie, damit man sie findet:

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

In Calíope
Das visuelle EXPLAIN zeigt die gelesenen Granulen jedes Lesevorgangs, rot, wenn keine übersprungen werden. Langsame Abfragen kommen aus system.query_log, mit gelesenen Bytes und Speicher neben der Zeit, und was Calíope selbst ausführt, ist mit log_comment markiert, damit man es ausblenden kann.

Stichwörter: Leistung, performance, prewhere, final, Granule, explain, indexes, max_threads, max_memory_usage, query_log, read_bytes, memory_usage, log_comment, Sortierschlüssel

Merges im Hintergrund, system.merges und system.parts, OPTIMIZE gegenüber OPTIMIZE FINAL, die Schwellen für Parts und warum „zu viele Parts“ der häufigste Betriebsfehler ist.

Gilt für: ClickHouse 25.3+

Jedes INSERT legt einen neuen Part auf der Platte an. Im Hintergrund führt der Server kleine Parts zu größeren zusammen: Das hält die Tabelle bei wenigen Parts und wendet ReplacingMergeTree, TTLs und leichtes Löschen an.

Parts und Merges ansehen

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

Ein inaktiver Part ist einer, der schon in einen Merge eingegangen ist: Er bleibt auf der Platte, bis old_parts_lifetime (480 s) abgelaufen ist, und wird dann gelöscht.

OPTIMIZE und OPTIMIZE FINAL
OPTIMIZE TABLE fordert jetzt einen Merge an; OPTIMIZE TABLE … FINAL erzwingt ihn, bis ein Part pro Partition übrig ist, auch wenn es schon nur einer war. Gemessen: Mit angehaltenen Merges und fünf INSERTs fünf Parts; OPTIMIZE brachte sie auf 1, weil sie klein waren, und ein OPTIMIZE … FINAL auf diesen einen Part schrieb ihn vollständig neu. FINAL schreibt die Tabelle neu: Bei einer großen Tabelle ist das viel Arbeit.

Zu viele Parts
Wenn INSERTs schneller Parts anlegen, als sie zusammengeführt werden, bremst der Server zuerst und lehnt dann ab:
- parts_to_delay_insert (standardmäßig 1 000): ab hier verzögert er jedes INSERT.
- parts_to_throw_insert (3 000): ab hier lehnt er es ab.

Gemessen mit auf 4 gesenkter Schwelle: Das fünfte INSERT schlug mit Code: 252 … Too many parts … Merges are processing significantly slower than inserts (TOO_MANY_PARTS) fehl. Das ist der häufigste Betriebsfehler, und er kommt fast immer vom zeilenweisen Einfügen oder von sehr kleinen Stapeln oder von zu feiner Partitionierung. Der Ausweg ist, in Stapeln von Tausenden Zeilen einzufügen. Auf dem gemessenen Server (26.8) ist async_insert standardmäßig eingeschaltet und fasst laut seiner Dokumentation kleine INSERTs, die gleichzeitig ankommen, zu einem Part zusammen; drei nacheinander ausgeführte INSERTs ergaben trotzdem drei Parts.

In Calíope
Tabellenwartung bietet OPTIMIZE mit der Option FINAL an, die anfangs aus ist und vor dem Ausführen warnt, weil sie die Tabelle neu schreibt, und erlaubt, es auf eine Partition zu beschränken. Serverinformationen zeigt Größe und Kompression jeder Tabelle, summiert aus ihren aktiven Parts.

Stichwörter: Merge, Zusammenführung, optimize, optimize final, system.merges, system.parts, Part, inaktiver Part, old_parts_lifetime, parts_to_delay_insert, parts_to_throw_insert, too many parts, too_many_parts, async_insert

ReplicatedMergeTree und Keeper, die Makros {shard} und {replica}, system.replicas, SYNC und RESTART REPLICA, was ohne Keeper passiert, DROP TABLE … SYNC und system.clusters.

Gilt für: ClickHouse 25.3+

Bei ClickHouse erfolgt die Replikation pro Tabelle: Jede Replicated*-Tabelle stimmt sich über Keeper (oder ZooKeeper) mit ihren Kopien ab, der festhält, welche Parts es gibt und was noch zu kopieren ist. Es gibt keinen Primärserver: Jedes Replikat nimmt Schreibvorgänge an.

Eine replizierte Tabelle anlegen

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

Der Pfad kennzeichnet die Tabelle in Keeper und ist auf allen ihren Replikaten gleich; {shard} und {replica} sind Makros, die jeder Server aus seiner Konfiguration füllt (system.macros). Im gemessenen Paar haben beide Replikate shard = 01, und replica ist auf dem einen r1 und auf dem anderen r2. Die Tabelle wird auf jedem Replikat angelegt, und die Zeilen kommen von selbst.

system.replicas

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

- absolute_delay: Sekunden Rückstand gegenüber dem aktuellsten Replikat.
- queue_size: anstehende Aufgaben (Parts holen, zusammenführen, mutieren).
- is_readonly und is_session_expired: Das Replikat hat Keeper verloren.

Gemessen: Mit angehaltenem Kopieren auf r2 (SYSTEM STOP FETCHES) und einer auf r1 eingefügten Zeile blieb r2 bei 2 Zeilen mit queue_size = 1; nach dem Fortsetzen 3.

SYNC und RESTART REPLICA
SYSTEM SYNC REPLICA tabelle wartet, bis das Replikat seine Warteschlange geleert hat; gemessen kam es mit allen 3 Zeilen aktuell zurück. SYSTEM RESTART REPLICA tabelle liest seinen Zustand neu aus Keeper; danach is_readonly = 0 und active_replicas = 2.

Ohne Keeper nur lesend
Gemessen durch Anhalten von Keeper: Die Tabelle ging auf is_readonly = 1 und is_session_expired = 1, Lesen funktionierte weiter, und ein INSERT wartete 120 s und schlug mit TIMEOUT_EXCEEDED fehl. Als Keeper zurückkam, erholte sich das Replikat von selbst.

DROP TABLE … SYNC
Wird eine replizierte Tabelle ohne SYNC gelöscht, wird ihre Spur in Keeper später aufgeräumt, und sie sofort neu anzulegen schlägt mit Code: 253 … Replica … already exists (REPLICA_ALREADY_EXISTS) fehl (gemessen). Mit DROP TABLE … SYNC wartet die Anweisung auf dieses Aufräumen.

system.clusters
Cluster werden in der Serverkonfiguration deklariert, nicht mit SQL angelegt, und system.clusters listet sie mit ihren Shards und Replikaten. Im Testbed hat caliope_par einen Shard mit zwei Replikaten: clickhouse-r1 und clickhouse-r2.

In Calíope
Bei ClickHouse geht der Replikations-Monitor Tabelle für Tabelle vor: aktive Replikate von allen, die fehlenden mit Namen, Rückstand und Warteschlange, und der Verlust von Keeper als eigener Zustand. Topologie zeichnet, was system.clusters deklariert.

Stichwörter: Replikation, replication, replicatedmergetree, keeper, zookeeper, Makros, shard, replica, system.replicas, absolute_delay, queue_size, is_readonly, sync replica, restart replica, system.clusters, replica_already_exists

Benutzer aus den Dateien des Servers gegenüber den per SQL angelegten, Rollen, GRANT und teilweiser Entzug, Quoten, Einstellungsprofile, Zeilenrichtlinien und warum das Passwort nicht in die URL gehört.

Gilt für: ClickHouse 25.3+

ClickHouse hält Benutzer an zwei Orten, und woher ein Benutzer stammt, entscheidet, was man mit ihm tun kann.

Zwei Benutzerspeicher

SELECT name, storage, auth_type FROM system.users;

- users_xml: die in den Dateien des Servers deklarierten (users.xml und users.d/). Sie ändern sich nur, wenn man diese Dateien bearbeitet: ALTER USER auf einen von ihnen schlägt mit Code: 495 … ACCESS_STORAGE_READONLY fehl (gemessen).
- local_directory: die mit CREATE USER angelegten. Sie werden per SQL verwaltet, und der Server speichert sie auf seiner Platte.

Um Benutzer per SQL anzulegen, braucht man ein Konto mit Zugriffsverwaltung (access_management in seiner Konfiguration oder das Recht ACCESS MANAGEMENT).

Rollen, GRANT und teilweiser Entzug

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

Ein REVOKE auf eine Tabelle einer vollständig gewährten Datenbank entfernt das Recht auf die Datenbank nicht: Es hinterlässt einen teilweisen Entzug. SHOW GRANTS FOR lectura zeigt beide Zeilen, und system.grants markiert die zweite mit is_partial_revoke = 1 (gemessen). Mit dieser Rolle funktioniert das Lesen von demo.productos, und das Lesen von demo.eventos schlägt mit Code: 497 … ACCESS_DENIED fehl.

Quoten, Profile und Zeilenrichtlinien
- Quoten (CREATE QUOTA, system.quotas, system.quota_usage): Grenzen für Abfragen, Zeilen oder Zeit pro Intervall. Laut der Dokumentation des Servers wird eine Abfrage bei Überschreitung mit QUOTA_EXCEEDED abgelehnt.
- Einstellungsprofile (CREATE SETTINGS PROFILE … SETTINGS max_result_rows = 5 TO ana): Der Benutzer erhält sie bei jeder Verbindung, und system.settings zeigt sie mit changed = 1 (gemessen).
- Zeilenrichtlinien (CREATE ROW POLICY … ON demo.eventos USING id <= 3 TO ana): Der Server fügt diesen Filter allem hinzu, was der Benutzer liest. Gemessen: count() ergab 3 für ihn und 90 für den Administrator.

Das Passwort gehört nicht in die URL
Die HTTP-Schnittstelle akzeptiert ?user=…&password=… in der Adresse, aber eine URL landet im Verlauf des Terminals, in den Protokollen jedes Proxys und in Netzwerkwerkzeugen. Der gemessene Server verbirgt es in seinem eigenen Protokoll (password=[HIDDEN]); die anderen nicht. Calíope sendet die Zugangsdaten in den Headern X-ClickHouse-User und X-ClickHouse-Key und die Abfrage im Rumpf der Anfrage.

In Calíope
Benutzer listet Konten und Rollen mit ihrem Speicher. Die aus users_xml erscheinen mit einem Schloss und lassen sich nicht bearbeiten, und ein teilweiser Entzug erscheint durchgestrichen und mit dem Wort REVOKE. Schreiben setzt voraus, dass das verbundene Konto die Zugriffsverwaltung hat; sonst werden die Schaltflächen deaktiviert, und ein Banner nennt den Grund.

Stichwörter: Sicherheit, Benutzer, Rolle, grant, revoke, teilweiser Entzug, is_partial_revoke, show grants, system.users, users_xml, local_directory, access management, Quote, quota, Einstellungsprofil, settings profile, row policy, Zeilenrichtlinie, Passwort, Zugriff verweigert

Abfrageeinstellungen gegenüber Servereinstellungen, config.d und users.d, wie man eine Einstellung über HTTP dauerhaft macht, und die Einstellungen, die Calíope mit jeder Anfrage sendet.

Gilt für: ClickHouse 25.3+

ClickHouse hat zwei Arten von Einstellungen, und sie zu verwechseln ist der häufigste Fehler beim Konfigurieren.

Abfrageeinstellungen und Servereinstellungen

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

- system.settings (1.817 auf dem gemessenen Server): Sie gelten für eine Abfrage. Man setzt sie in der Abfrage (SETTINGS … am Ende), in der Sitzung, in einem Profil oder am Benutzer.
- system.server_settings (447): Sie gelten für den ganzen Prozess, wie max_concurrent_queries (standardmäßig 1.000) oder max_server_memory_usage. Man ändert sie in der Serverkonfiguration, und die Spalte changeable_without_restart sagt, ob sie ohne Neustart wirken: Auf dem gemessenen Server brauchen 339 der 447 einen Neustart.

config.d und users.d
Die Konfiguration liegt in /etc/clickhouse-server/: config.xml für den Server und users.xml für Benutzer, Profile und Quoten. Eigene Änderungen kommen in eigene Dateien in config.d/ und users.d/, die der Server mit den Hauptdateien zusammenführt; so überschreibt ein Update sie nicht. Das offizielle Docker-Image bringt in jedem Ordner bereits eine mit.

Über HTTP hält SET nicht
Jede HTTP-Anfrage ist unabhängig: Ein SET max_threads = 7 gilt für diese Anfrage, und die nächste hat es nicht mehr (gemessen). Eine Einstellung lässt sich in der Anfrage selbst mitsenden (?max_threads=5) oder am Benutzer dauerhaft machen:

ALTER USER ana MODIFY SETTINGS max_threads = 3;

Gemessen: Ab da bringt jede neue Verbindung von ana sie mit, mit changed = 1. Ohne das Wort MODIFY ersetzt ALTER USER … SETTINGS alle Einstellungen des Kontos. Und es funktioniert nur bei per SQL angelegten Benutzern: Bei einem aus users_xml schlägt es mit ACCESS_STORAGE_READONLY fehl.

Was Calíope mit jeder Anfrage sendet
Fünf feste Einstellungen, um Zahlen ohne Genauigkeitsverlust zu lesen und die eigenen Abfragen wiederzuerkennen:
- output_format_json_quote_64bit_integers, output_format_json_quote_decimals, output_format_json_quote_64bit_floats und output_format_json_quote_denormals: 64-Bit-Ganzzahlen, Dezimalzahlen und Werte wie inf oder nan kommen in Anführungszeichen. In JSON passt eine 64-Bit-Ganzzahl nicht exakt, und ohne diese Einstellungen kommen inf und nan als null.
- log_comment: kennzeichnet die Abfragen von Calíope in system.query_log.

Der Editor verwendet session_id nicht (nur ein Bericht mit mehreren Anweisungen öffnet eine eigene Sitzung); der Grund steht in Grenzen (ClickHouse).

In Calíope
Langsame Abfragen speichert seinen Schwellenwert mit ALTER USER … MODIFY SETTINGS. Lässt sich das Konto nicht ändern, sagt das eine Notiz, statt die Bedienelemente zu zeigen.

Stichwörter: Konfiguration, Einstellungen, settings, server settings, system.settings, system.server_settings, config.xml, users.xml, config.d, users.d, alter user, modify settings, set, max_threads, Neustart, log_comment, output_format_json_quote

max_query_size, der Speicher pro Abfrage, die Grenzen des Ergebnisses, gleichzeitige Abfragen, zu viele Parts, der Einfügeblock und warum der Editor session_id nicht verwendet.

Gilt für: ClickHouse 25.3+

Der Text einer Abfrage
max_query_size beträgt standardmäßig 262.144 Bytes. Eine längere Abfrage schlägt mit Code: 62 … Max query size exceeded an Position 262.145 fehl (gemessen). Pro Abfrage lässt sich der Wert mit SETTINGS max_query_size = … erhöhen. Der Dump von Calíope fasst seine INSERT unterhalb dieser Grenze zusammen.

Der Speicher
max_memory_usage ist auf dem gemessenen Server 0 (keine Grenze pro Abfrage); die eigentliche Obergrenze ist max_server_memory_usage, die des Prozesses. Mit einer niedrigen Grenze schlägt die Abfrage mit Code: 241 … MEMORY_LIMIT_EXCEEDED fehl, und die Meldung sagt, wie viel sie verbraucht hätte.

Die Größe des Ergebnisses
max_result_rows und max_result_bytes sind standardmäßig 0. Mit einer gesetzten Grenze lässt result_overflow_mode = 'throw' (der Standard) die Abfrage mit Code: 396 … TOO_MANY_ROWS_OR_BYTES fehlschlagen, und 'break' liefert, was bis dahin gelesen wurde.

Abfragen gleichzeitig
max_concurrent_queries gehört zum Server (standardmäßig 1.000) und max_concurrent_queries_for_user zu jedem Benutzer (0, unbegrenzt). Wird sie überschritten, schlägt die neue Abfrage mit Code: 202 … TOO_MANY_SIMULTANEOUS_QUERIES fehl (gemessen mit der Grenze auf 1).

Zu viele Parts
Jedes INSERT legt einen Part an. Bei 1.000 aktiven Parts in einer Partition (parts_to_delay_insert) bremst der Server das Einfügen, und bei 3.000 (parts_to_throw_insert) lehnt er es mit TOO_MANY_PARTS ab. Das steht in Merges und OPTIMIZE (ClickHouse).

Der Einfügeblock
Der Server fasst die Zeilen eines INSERT zu Blöcken von höchstens max_insert_block_size Zeilen zusammen (1.048.449). Ein großes INSERT, das mittendrin fehlschlägt, lässt die früheren Blöcke geschrieben zurück: Es ist nicht atomar. Das steht in Transaktionen und Atomarität (ClickHouse).

Eine Anfrage, eine Anweisung und keine Sitzung
Über HTTP nimmt jede Anfrage nur eine Anweisung an: Zwei durch ; getrennte schlagen mit Multi-statements are not allowed fehl (gemessen). Die Schnittstelle bietet session_id für eine Sitzung an, und damit hält ein SET tatsächlich von einer Anfrage zur nächsten. Aber eine Sitzung nimmt nur eine Abfrage gleichzeitig an: Eine zweite mit derselben session_id, während die erste läuft, schlägt mit Code: 373 … SESSION_IS_LOCKED fehl (gemessen). Deshalb verwendet der Editor sie nicht: Zwei Tabs auf derselben Verbindung bekämen diesen Fehler. Nur ein Bericht aus mehreren Anweisungen öffnet eine eigene Sitzung, bei jedem Lauf eine neue, die niemand sonst teilt. Jede Anfrage trägt ihre Datenbank und ihre Einstellungen mit.

Stichwörter: Grenze, Limit, max_query_size, max_memory_usage, Speicher, max_result_rows, max_result_bytes, result_overflow_mode, max_concurrent_queries, gleichzeitige Abfragen, parts_to_throw_insert, too many parts, max_insert_block_size, session_id, Sitzung, multi-statements

Die Codes, die man täglich sieht, mit Nummer und Namen: was sie auslöst und was zu tun ist.

Gilt für: ClickHouse 25.3+

Jeder Fehler von ClickHouse trägt eine Nummer und einen Namen: Code: 60 … (UNKNOWN_TABLE). Die Nummer bleibt über Versionen gleich, und nach dem Namen sucht man in der Dokumentation. Über HTTP kommt die Nummer zusätzlich im Header X-ClickHouse-Exception-Code. Alle Fehler dieser Liste wurden gegen den gemessenen Server ausgelöst.

60 · UNKNOWN_TABLE
Die Tabelle gibt es in dieser Datenbank nicht. Über HTTP gibt es kein USE: Die Datenbank reist mit jeder Anfrage mit, also lohnt es sich, die gewählte Datenbank zu prüfen oder datenbank.tabelle zu schreiben.

62 · SYNTAX_ERROR
Ein Syntaxfehler; die Meldung nennt die Position und was erwartet wurde. Er erscheint auch bei zwei Anweisungen in einer einzigen Anfrage (Multi-statements are not allowed) und bei einer Abfrage mit mehr als 262.144 Bytes (Max query size exceeded).

53 · TYPE_MISMATCH
Eine unmögliche Umwandlung zwischen Typen, etwa CAST(1 AS Array(UInt8)). Abhilfe schafft die passende Umwandlungsfunktion (toString, toUInt8OrNull …).

241 · MEMORY_LIMIT_EXCEEDED
Die Abfrage hat die Speichergrenze überschritten, und die Meldung sagt, wie viel sie verbraucht hätte. Früher filtern, weniger Spalten aggregieren oder, wenn der Server es hergibt, max_memory_usage erhöhen hilft.

202 · TOO_MANY_SIMULTANEOUS_QUERIES
Zu viele Abfragen gleichzeitig, für den ganzen Server oder für diesen Benutzer. Abwarten genügt; Prozesse zeigt, was läuft.

497 · ACCESS_DENIED
Ein Recht fehlt, und die Meldung sagt welches (it's necessary to have the grant SELECT ON demo.eventos). Ein teilweiser Entzug löst ihn aus, auch wenn die ganze Datenbank gewährt ist.

164 · READONLY
Die Abfrage läuft im Nur-Lese-Modus (readonly = 1 am Benutzer, in seinem Profil oder in der Abfrage selbst). Man braucht ein Konto, das schreiben darf.

373 · SESSION_IS_LOCKED
Zwei Anfragen gleichzeitig mit derselben session_id. Calíope öffnet eine Sitzung nur für einen Bericht aus mehreren Anweisungen, bei jedem Lauf eine neue; taucht der Fehler auf, kommt er von einem anderen Client.

395 · FUNCTION_THROW_IF_VALUE_IS_NON_ZERO
Ein throwIf in der Abfrage hat ausgelöst, und die Meldung ist die, die ihm die Abfrage mitgegeben hat. Hatte die Abfrage schon Zeilen geliefert, kommt der Fehler am Ende der Antwort: Calíope zeigt ihn als Fehler, nicht als abgeschnittenes Ergebnis.

420 · CANNOT_UPDATE_COLUMN
Ein ALTER TABLE … UPDATE auf eine Spalte des Sortierschlüssels. Der Schlüssel lässt sich nicht ändern: Man legt eine neue Tabelle mit dem neuen Schlüssel an und kopiert die Daten.

495 · ACCESS_STORAGE_READONLY
Ein Versuch, einen in den Dateien des Servers deklarierten Benutzer (users_xml) zu ändern. Man ändert ihn in users.d/ oder legt per SQL einen anderen an.

516 · AUTHENTICATION_FAILED
Benutzer oder Passwort falsch; der Server sagt nicht, welches von beiden. Über HTTP kommt der Fehler mit Status 403.

In Calíope
Erscheint einer dieser Fehler im Editor, führt das Banner Lösung anzeigen zu diesem Thema.

Stichwörter: Fehler, Code, unknown_table, syntax_error, type_mismatch, memory_limit_exceeded, too_many_simultaneous_queries, access_denied, readonly, session_is_locked, throwif, cannot_update_column, access_storage_readonly, authentication_failed, 60, 62, 53, 241, 202, 497, 164, 373, 395, 420, 495, 516

NUMBER rundet stillschweigend auf seine Skala und lehnt ab, was seine Genauigkeit übersteigt; BINARY_DOUBLE ist binär.

Gilt für: Oracle 26ai+

In Oracle ist fast jede Zahl ein NUMBER: dezimal, exakt und mit bis zu 38 Stellen. INTEGER und SMALLINT gibt es nur als Namen —gemessen: beide Spalten wurden als NUMBER mit Skala 0 angelegt—, und es gibt weder UNSIGNED noch Typen nach Größe.

NUMBER(p,s): die Skala rundet, die Genauigkeit lehnt ab

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

Was rechts über die Skala hinausgeht, wird ohne Warnung gerundet; was links nicht passt, ergibt ORA-01438. Eine negative Skala rundet auf Zehner oder Hunderter, und ein bloßes NUMBER speichert den Wert unverändert.

Exakt, und mit variabler Größe
NUMBER speichert Dezimalzahlen: 0.1 + 0.2 = 0.3 ist wahr. Die Größe hängt vom Wert ab, nicht von der Deklaration: gemessen mit VSIZE belegt 1 2 Bytes, 123.45 4 und 1234567890123 8.

BINARY_DOUBLE und BINARY_FLOAT sind binär
Es sind double und float nach IEEE 754: schneller zum Rechnen und mit den üblichen Fehlern. 0.1d + 0.2d ergab 3.0000000000000004E-001, und der Vergleich mit 0.3d, falsch. Ein bloßes FLOAT dagegen ist ein NUMBER, dessen Genauigkeit in Bits angegeben wird (FLOAT(126)). Für Geld: NUMBER(p,s).

BOOLEAN, seit 23ai

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

Er akzeptiert auch 'yes' oder 0 und wird ohne Vergleich verwendet (WHERE v). Vor 23ai gibt es ihn nicht, üblich ist dann NUMBER(1) mit einem CHECK.

In Calíope
Das Raster zeigt NUMBER exakt an, ohne Umweg über Gleitkomma, und BOOLEAN als TRUE oder FALSE.

Stichwörter: Zahl, NUMBER, Dezimal, Genauigkeit, Skala, BINARY_DOUBLE, BINARY_FLOAT, FLOAT, BOOLEAN, ORA-01438, Oracle

VARCHAR2 zählt Bytes, solange du nicht CHAR sagst, hat eine Obergrenze von 4000, und die leere Zeichenkette ist NULL.

Gilt für: Oracle 26ai+