Datenbank-Grundlagen: Ebenen, Transaktionen und das, was schon da ist

„Datenbank“ meint drei verschiedene Dinge

Wenn im Ticket steht „die Datenbank ist voll“, kann das drei Ebenen meinen, und die Maßnahme unterscheidet sich bei jeder. Bevor irgendetwas anderes gelernt wird, muss diese Leiter sitzen — fast jede Fehlentscheidung im Datenbankbetrieb beginnt damit, dass jemand auf der falschen Ebene handelt.

Instanz — Ein laufender Serverprozess mit eigenem Arbeitsspeicher, eigenem Port und eigener Konfiguration. Auf einem Host können mehrere Instanzen nebeneinander laufen. PostgreSQL lauscht voreingestellt auf Port 5432, SQL Server auf 1433.

Datenbank — Ein benannter Container für Tabellen innerhalb einer Instanz. Rechte, Kollation und bei SQL Server das Wiederherstellungsmodell hängen an dieser Ebene.

Schema — Ein Namensraum innerhalb einer Datenbank. public bei PostgreSQL, dbo bei SQL Server. Zwei Tabellen dürfen gleich heißen, solange sie in verschiedenen Schemata liegen.

PostgreSQL nennt die Instanz-Ebene Cluster. Das ist die häufigste Stolperstelle für Umsteiger aus dem Windows- oder Virtualisierungsbetrieb: Ein „PostgreSQL-Cluster“ hat mit Hochverfügbarkeit nichts zu tun. Die Dokumentation definiert ihn als eine Sammlung von Datenbanken, die von einer einzigen laufenden Server-Instanz verwaltet wird, und verweist auf den Begriff des SQL-Standards, catalog cluster. Ein Failover-Verbund aus zwei Servern ist in dieser Sprache kein Cluster, sondern eine Replikation.

Angelegt wird die Instanz bei PostgreSQL mit initdb -D /var/lib/pgsql/data, bei SQL Server durch das Setup. In beiden Fällen entsteht dabei ein Verzeichnis, das ab diesem Moment der einzige Ort ist, an dem die Daten liegen — und die Entscheidungen dieses einen Aufrufs sind später teuer zu ändern.

Was schon da ist, bevor du die erste Tabelle anlegst

Beide Systeme legen beim Initialisieren Datenbanken an, die niemand bestellt hat. Sie sind kein Beiwerk: Zwei davon sind Vorlagen, eine ist flüchtig, und eine ist der Grund, warum ein Sicherungsauftrag scheinbar unvollständig aussieht.

template1 (PostgreSQL)

Vorlage für jedes CREATE DATABASE. Eine Erweiterung, die hier installiert wird, ist in allen später erzeugten Datenbanken vorhanden.

template0 (PostgreSQL)

Unveränderte Rückfallvorlage. Bleibt bewusst leer, damit eine saubere Ausgangsbasis existiert, wenn template1 verändert wurde.

postgres (PostgreSQL)

Standard-Anmeldeziel für Werkzeuge. Der Server selbst braucht sie nicht, viele externe Programme setzen sie aber voraus.

master (SQL Server)

Hält alle Informationen auf Instanz-Ebene. Ohne sie startet die Instanz nicht.

model (SQL Server)

Vorlage für jede neu erzeugte Datenbank. Größe, Kollation und Wiederherstellungsmodell werden von hier vererbt.

msdb (SQL Server)

Datenhaltung des SQL Server Agent: Aufträge, Zeitpläne, Warnungen — und die Sicherungshistorie.

tempdb (SQL Server)

Arbeitsfläche für temporäre Objekte und Zwischenergebnisse.

Die operativ wichtigste Eigenschaft steht bei tempdb: Sie wird laut Dokumentation bei jedem Start der Datenbank-Engine neu erzeugt, damit das System immer mit einer leeren tempdb startet. Daraus folgt direkt, dass Sicherungs- und Wiederherstellungsvorgänge auf tempdb nicht erlaubt sind.

Die Vorlagen sind die andere Seite derselben Medaille. Was in model beziehungsweise template1 geändert wird, erben alle später angelegten Datenbanken — und nur die später angelegten. Eine Änderung an der Vorlage repariert nichts an dem, was schon existiert. Das ist der Grund, warum „wir haben das im Modell gesetzt“ und „alle Datenbanken haben die Einstellung“ zwei verschiedene Aussagen sind.

Wo die Daten liegen — und warum `du` dir keine Namen nennt

Der Reflex bei einer vollen Platte ist du -sh *. Im Datenverzeichnis einer PostgreSQL-Instanz liefert dieser Befehl Zahlen, aber keine verwertbaren Namen. Das ist kein Konfigurationsfehler, sondern das dokumentierte Speicherformat.

base/

Je ein Unterverzeichnis pro Datenbank, benannt nach ihrer OID aus pg_database.

global/

Cluster-weite Tabellen, zum Beispiel pg_database selbst.

pg_wal/

Die Write-Ahead-Log-Dateien. Das Verzeichnis, das bei stehender Archivierung vollzulaufen droht.

postgresql.conf, pg_hba.conf

Konfiguration und Client-Authentifizierung. Traditionell im Datenverzeichnis, dürfen aber woanders liegen.

Innerhalb von base/<OID> heißt jede Datei nach der Filenode-Nummer der Tabelle oder des Index, nicht nach deren Namen. Die Dokumentation warnt ausdrücklich davor, Filenode und Tabellen-OID gleichzusetzen: TRUNCATE, REINDEX, CLUSTER und manche Formen von ALTER TABLE ändern den Filenode und behalten die OID. Eine Datei, die man sich gestern notiert hat, kann heute zu einer anderen Tabelle gehören.

Dazu kommt die Segmentierung. Überschreitet eine Tabelle oder ein Index 1 GB, wird sie in gigabyte-große Segmente zerlegt: die erste Datei trägt die Filenode-Nummer, die weiteren hängen .1, .2 und so fort an. Eine große Tabelle erscheint im Verzeichnis als Dutzende Dateien, die beim Sortieren auseinanderlaufen.

SQL Server macht es dem Dateisystem leichter: Eine Datenbank besteht aus einer primären Datendatei mit der Endung .mdf, optional weiteren Datendateien mit .ndf und mindestens einer Transaktionsprotokolldatei mit .ldf. Die Zuordnung Datei zu Datenbank ist dort ablesbar — die Zuordnung Datei zu Tabelle aber auch hier nicht, weil eine Datei viele Tabellen trägt.

Seiten: die Einheit, in der wirklich geschrieben wird

Keines der beiden Systeme liest oder schreibt einzelne Zeilen. Beide arbeiten auf Seiten fester Größe, und in beiden liegt die Voreinstellung bei 8 kB. Bei PostgreSQL ist sie beim Übersetzen des Servers wählbar, bei SQL Server fest.

Seite — Die kleinste Einheit, die zwischen Platte und Arbeitsspeicher bewegt wird. Eine Zeile ändern heißt: die ganze Seite lesen, im Speicher ändern, die ganze Seite schreiben.

Extent (SQL Server) — Acht physisch zusammenhängende Seiten, zusammen 64 KiB. Die Einheit, in der die Engine Platz zuteilt. Jede Seite gehört zu einem Extent.

Aus dieser einen Zahl folgt mehr, als sie vermuten lässt. Ein UPDATE, das ein einziges Byte ändert, erzeugt mindestens eine geschriebene 8-kB-Seite plus den zugehörigen Protokolleintrag. Ein Auftrag, der eine Million Zeilen einzeln aktualisiert, ist deshalb nicht ein Megabyte Schreiblast, sondern ein Vielfaches davon — und das ist die übliche Antwort auf die Frage, warum ein „kleines Update“ ein Speichersystem in die Knie zwingt.

Wo eine Tabelle ohne Punkt davor wirklich landet

Die Anweisung CREATE TABLE kunden (...) nennt kein Schema. Trotzdem landet die Tabelle an einem bestimmten Ort, und welcher das ist, entscheidet eine Einstellung pro Sitzung.

Bei PostgreSQL heißt sie search_path und enthält voreingestellt zwei Einträge. Der erste sucht ein Schema, das so heißt wie der angemeldete Benutzer; existiert es nicht, wird der Eintrag übersprungen. Danach kommt public. Das erste existierende Schema im Pfad ist der Ort, an dem neue Objekte entstehen. pg_catalog mit den Systemtabellen wird immer durchsucht, und wenn es nicht ausdrücklich im Pfad steht, implizit zuerst.

Bei SQL Server erfüllt das Standardschema des Datenbankbenutzers dieselbe Rolle; ohne abweichende Festlegung ist das dbo.

Daran hängt eine Unterscheidung, die im Betrieb regelmäßig übersehen wird: In Datenbanken, die von PostgreSQL 14 oder früher hochgezogen wurden, hat weiterhin jeder das `CREATE`-Recht auf `public`. Neu angelegte Datenbanken neuerer Stände haben es nicht. Zwei Instanzen desselben Softwarestands können sich hier also gegensätzlich verhalten, je nachdem, ob sie migriert oder frisch aufgesetzt wurden. Der Riegel ist ein Einzeiler: REVOKE CREATE ON SCHEMA public FROM PUBLIC; — wobei das erste public das Schema meint und das zweite „jeder Benutzer“.

Die Transaktion: das Versprechen gegen den halben Auftrag

Ein Auftrag berührt selten nur eine Tabelle. Ein Bestellvorgang schreibt eine Kopfzeile, mehrere Positionen und bucht Bestand ab. Fällt der Prozess nach der zweiten von drei Anweisungen aus, bleibt ein Zustand zurück, den die Fachlichkeit nicht kennt: eine Bestellung ohne Positionen, aber mit abgebuchtem Bestand.

Transaktion — Eine Gruppe von Anweisungen, die entweder vollständig oder gar nicht wirksam wird. Abgeschlossen mit COMMIT, verworfen mit ROLLBACK.

Beide Systeme arbeiten voreingestellt im Autocommit: Jede einzelne Anweisung ist ihre eigene Transaktion. PostgreSQL formuliert es so, dass ohne BEGIN jede Anweisung in einer eigenen Transaktion läuft und am Ende implizit bestätigt wird — beziehungsweise zurückgerollt, wenn sie fehlschlug. SQL Server führt dieselbe Betriebsart als eine von mehreren auf.

Autocommit

Jede Anweisung ist eine eigene Transaktion. Voreinstellung in beiden Systemen.

Explizit

Beginnt mit BEGIN beziehungsweise BEGIN TRANSACTION, endet mit COMMIT oder ROLLBACK.

Implizit (SQL Server)

Eine neue Transaktion startet automatisch, sobald die vorherige endet; abgeschlossen wird aber weiterhin ausdrücklich.

Autocommit ist der Grund, warum ein Skript aus drei UPDATE-Anweisungen ohne weiteres Zutun drei Transaktionen ist und nicht eine. Genau dort entsteht der fachlich halbe Auftrag: nicht weil jemand die falsche Anweisung geschrieben hat, sondern weil niemand eine Klammer darum gesetzt hat.

Derselbe Fehler, zwei entgegengesetzte Ergebnisse

Hier trennen sich die beiden Systeme, und zwar an der Stelle, die für Betriebsskripte die teuerste ist. Die Frage lautet: Was passiert mit einer laufenden Transaktion, wenn eine Anweisung darin einen Laufzeitfehler wirft?

PostgreSQL versetzt die gesamte Transaktion in einen abgebrochenen Zustand. Jede weitere Anweisung wird abgewiesen. Die Dokumentation ist an dieser Stelle unmissverständlich: ROLLBACK TO zu einer Marke ist der einzige Weg, die Kontrolle über einen vom System wegen eines Fehlers abgebrochenen Transaktionsblock zurückzugewinnen — abgesehen davon, ihn vollständig zurückzurollen und neu zu beginnen.

SQL Server verhält sich voreingestellt umgekehrt. Steuernd ist SET XACT_ABORT. Ist es `OFF` — und OFF ist laut Dokumentation die Voreinstellung für T-SQL-Anweisungen — dann wird in manchen Fällen nur die fehlerhafte Anweisung zurückgerollt, und die Transaktion läuft weiter. Je nach Schweregrad des Fehlers kann auch bei OFF die ganze Transaktion zurückgerollt werden; verlassen kann man sich darauf nicht. Ist XACT_ABORT dagegen ON, wird die gesamte Transaktion beendet und zurückgerollt. In einem Trigger ist ON die Voreinstellung.

Integrität gehört ins Schema, nicht in die Anwendung

Regeln, die nur im Anwendungscode stehen, gelten nur für den Weg durch den Anwendungscode. Ein Importskript, ein Migrationswerkzeug oder eine Handkorrektur über psql umgeht sie vollständig. Im Schema hinterlegte Bedingungen gelten für jeden Schreibweg.

• NOT NULL — die Spalte muss einen Wert haben.
• UNIQUE — kein Wert darf doppelt vorkommen.
• PRIMARY KEY — eindeutig und nicht leer; identifiziert die Zeile.
• FOREIGN KEY — der Wert muss in der referenzierten Tabelle existieren.
• CHECK — ein frei formulierter Ausdruck muss erfüllt sein.

Zwei dokumentierte Eigenheiten davon kosten regelmäßig Daten, weil sie der Intuition widersprechen.

Erstens verhalten sich `UNIQUE` und Nullwerte in den beiden Systemen gegensätzlich. PostgreSQL behandelt zwei Nullwerte voreingestellt als nicht gleich; es lassen sich also beliebig viele Zeilen mit einem Nullwert in der eindeutigen Spalte speichern. SQL Server erlaubt genau einen Nullwert je Spalte. Die PostgreSQL-Dokumentation benennt den Fallstrick selbst: Die Behandlung von Nullwerten in UNIQUE-Bedingungen ist laut SQL-Standard implementierungsabhängig, und andere Systeme verhalten sich anders. Wer eine Anwendung portiert und sich darauf verlässt, dass die Spalte „doch eindeutig“ sei, bekommt in einem der beiden Systeme Duplikate. Abstellen lässt sich das in PostgreSQL mit UNIQUE NULLS NOT DISTINCT.

Zweitens hebelt ein Nullwert eine `CHECK`-Bedingung aus. Eine CHECK-Bedingung ist erfüllt, wenn der Ausdruck wahr oder null ergibt. Da die meisten Ausdrücke null ergeben, sobald ein Operand null ist, verhindert eine Bedingung wie menge > 0 keine Nullwerte — sie lässt sie durch. Beide Systeme dokumentieren dieses Verhalten ausdrücklich. Wer Nullwerte ausschließen will, braucht zusätzlich NOT NULL.

Was du als Sysadmin entscheidest — und was nicht

Dieser Track richtet sich an Leute, die Server betreiben und die Datenbank mitbekommen haben, nicht an hauptamtliche Datenbankadministratoren. Die nützlichste Grenze verläuft nicht zwischen „darf ich“ und „darf ich nicht“, sondern zwischen umkehrbar und nicht umkehrbar.

Umkehrbar, deine Entscheidung

Dienst neu starten, einen Konfigurationswert ändern und zurücksetzen, Leserechte vergeben, ein Monitoring anschließen, einen Ausführungsplan ansehen.

Nicht umkehrbar, Rückfrage nötig

Zeilen ändern oder löschen, eine Sitzung beenden, einen Index erstellen oder verwerfen, eine Datenbank verschieben, das Wiederherstellungsmodell ändern.

Die Unterscheidung ist nicht formal, sie folgt aus dem bisher Gelehrten. Eine Sitzung zu beenden bedeutet, eine offene Transaktion zurückzurollen — und dieser Rollback kann länger dauern als das bisherige Laufen der Transaktion, weil er dieselbe Arbeit rückwärts erledigt. Das Beenden ist also keine Abkürzung, sondern eine zweite, mindestens gleich große Aufgabe.

Was dieser Track nicht behandelt, ist das Schreiben von Anwendungsabfragen und das Entwerfen von Datenmodellen. Der Wiederanlauf eines Dienstes nach einem Totalausfall liegt im Track backup-disaster-recovery; hier geht es um die Engine-Seite — wie das Schutzversprechen konfiguriert wird, das dort eingelöst wird.

Die Reihenfolge, die alles zusammenhält

Die fünf Schritte sind keine Merkhilfe, sondern die Zusammenfassung der Abschnitte oben. Wer die Ebene nicht bestimmt, startet den falschen Dienst neu. Wer nicht klammert, hinterlässt halbe Aufträge. Wer das Fehlerverhalten nicht festlegt, bekommt je nach System das eine oder das andere — und erfährt es erst hinterher.