PRAGMA: So redest du mit der SQLite-Engine
Ein PRAGMA ist ein SQLite-spezifisches Statement, mit dem du das Verhalten der Engine ausliest oder anpasst. Du führst es genauso aus wie jedes andere SQL – nur dass es nicht an deine Daten geht, sondern an die Konfiguration der Datenbank selbst.
Wenn du ein PRAGMA als Query ausführst, bekommst du den aktuellen Wert zurück. Setzt du es per Zuweisung, änderst du den Wert:
Die Grundidee dahinter: Die meisten PRAGMAs gelten pro Verbindung. Sobald du eine neue Connection öffnest, sind wieder die Defaults aktiv. Genau deshalb hat Produktionscode üblicherweise einen kleinen Block mit PRAGMA-Statements, der direkt nach jedem Verbindungsaufbau ausgeführt wird.
Die Basis-Konfiguration für Produktion
Wenn du dir nur fünf PRAGMAs merken willst, dann diese:
Das ist eine sinnvolle Voreinstellung für so ziemlich jede Anwendung, die SQLite als primären Datenspeicher einsetzt. Jede einzelne dieser Optionen lohnt sich näher anzuschauen – auf dieser Seite gehen wir sie der Reihe nach durch.
journal_mode = WAL
Der Journal-Modus steuert, wie SQLite Schreibvorgänge dauerhaft macht. Die Voreinstellung DELETE arbeitet mit einem Rollback-Journal: Schreibende blockieren Lesende und umgekehrt. Für ein CLI-Tool kein Problem, in einer Web-App dagegen schmerzhaft.
WAL (Write-Ahead Logging) dreht das Ganze um. Lese- und Schreibzugriffe blockieren sich nicht mehr gegenseitig – während ein Writer einen Commit durchführt, sehen die Reader weiterhin einen konsistenten Snapshot. Es bleibt zwar bei einem Writer zur gleichen Zeit, aber die Lesezugriffe bleiben auch unter Last schnell.
Ein paar Dinge, die du wissen solltest:
journal_modeist persistent — einmal gesetzt, bleibt der Modus für die Datenbankdatei erhalten. Du musst ihn also nicht bei jeder Verbindung neu setzen, schaden tut es aber auch nicht.- WAL legt zwei zusätzliche Dateien neben deiner
.dban: eine-walund eine-shm. Lösche sie nicht, solange die Datenbank geöffnet ist. - Über Netzwerkdateisysteme (NFS, SMB) funktioniert WAL eher schlecht. Halte die Datenbank auf einer lokalen Platte.
Es gibt ein eigenes Dokument zum WAL-Modus und zum Thema Nebenläufigkeit, das tiefer einsteigt. Für den Moment reicht: einschalten.
synchronous = NORMAL
Mit synchronous legst du fest, wie konsequent SQLite Daten auf die Platte schreibt. Der Kompromiss heißt: Datensicherheit gegen Geschwindigkeit.
FULL(Standard) — Flush nach jedem Commit. Maximale Datensicherheit, aber langsamer.NORMAL— Flush nur an sicheren Checkpoints. In Kombination mit WAL unbedenklich und spürbar schneller.OFF— das Betriebssystem entscheidet. Schnell, aber bei Stromausfall droht eine korrupte Datenbank.
Die Ganzzahl im Ergebnis (1) steht für NORMAL. Im WAL-Modus ist NORMAL die empfohlene Einstellung – committete Transaktionen gehen bei einem Absturz nicht verloren, nur die allerletzten könnten bei einem Stromausfall flöten gehen. Für die meisten Anwendungen ist das genau die richtige Balance.
Finger weg von OFF, es sei denn, du befüllst eine Wegwerf-Datenbank, die du jederzeit von Grund auf neu erzeugen kannst.
foreign_keys aktivieren in SQLite
Hier tappen viele in die Falle. SQLite unterstützt zwar Foreign Keys, aber die Prüfung ist standardmäßig deaktiviert – und die Einstellung gilt jeweils nur pro Verbindung:
Mit foreign_keys = ON schlägt das letzte Insert fehl — einen Autor mit der id 999 gibt es schlicht nicht. Ohne das PRAGMA schreibt SQLite die verwaiste Zeile klaglos in die Datenbank, und du entdeckst das Schlamassel erst Monate später.
Setz daher PRAGMA foreign_keys = ON; als allerersten Befehl auf jeder neuen Verbindung ab. Die meisten ORMs erledigen das automatisch; wenn du direkt mit dem Treiber arbeitest, bist du selbst dafür zuständig.
busy_timeout = 5000
SQLite erlaubt nur einen Schreiber gleichzeitig. Versucht eine zweite Verbindung zu schreiben, während die erste mitten in einer Transaktion steckt, bekommt sie standardmäßig sofort ein SQLITE_BUSY an den Kopf geworfen und gibt auf.
Mit busy_timeout weist du SQLite an, stattdessen zu warten und es erneut zu versuchen:
Der Wert wird in Millisekunden angegeben. 5000 bedeutet also: "Warte bis zu 5 Sekunden auf den Lock, bevor du aufgibst." Zusammen mit WAL verschwinden so die meisten lästigen database is locked-Fehler in nebenläufigen Anwendungen.
Wenn du diesen Wert über 30 Sekunden hochschrauben willst, liegt das eigentliche Problem vermutlich woanders – kürzere Transaktionen sind dann die bessere Lösung als ein längerer Timeout.
cache_size
Mit sqlite cache_size legst du fest, wie viele Datenbankseiten SQLite im Arbeitsspeicher hält. Mehr Cache bedeutet weniger Festplattenzugriffe – und damit schnellere Abfragen auf häufig genutzten Daten.
Für den Wert gibt es zwei Schreibweisen:
- Positive Zahl – Anzahl der Pages. Bei der Standard-Pagegröße von 4 KB entspricht
2000also 8 MB. - Negative Zahl – Kibibytes.
-20000sind 20 MB, unabhängig von der Pagegröße.
Die negative Schreibweise lässt sich übrigens leichter im Kopf behalten — du sagst direkt „gib mir 20 MB Cache" und musst nicht mit der Page-Größe rumrechnen. Für eine kleine Anwendung sind 20–50 MB völlig ausreichend. Bei leselastigen Workloads auf einer größeren Datenbank darfst du gerne höher gehen. Genau wie synchronous gilt auch cache_size pro Verbindung.
mmap_size
Mit Memory-Mapped I/O kann SQLite Teile der Datenbankdatei direkt aus dem Page Cache des Betriebssystems lesen und spart sich dabei eine Kopie. Das bringt vor allem bei großen Datenbanken einen spürbaren Geschwindigkeitsschub beim Lesen:
Das sind 256 MB. SQLite mappt davon so viel in den Speicher, wie Platz ist. Das Paging übernimmt das Betriebssystem – du allozierst also nicht direkt 256 MB im Voraus, sondern erlaubst lediglich, bis zu dieser Größe zu mappen.
Bei leselastigen Workloads spielt mmap_size seine Stärken voll aus. Auch bei kleinen Datenbanken richtet ein höherer Wert keinen Schaden an. Die Defaults sind eher konservativ gewählt, deshalb lohnt sich ein Hochsetzen in den meisten Fällen.
PRAGMA optimize in SQLite
Der Query Planner stützt sich auf Statistiken, um den passenden Index auszuwählen. Veraltete Statistiken führen zwangsläufig zu schlechten Ausführungsplänen. Mit PRAGMA optimize lassen sich diese Statistiken günstig aktualisieren:
Empfohlen wird, das Ganze direkt vor dem Schließen von langlebigen Verbindungen laufen zu lassen – also beim Beenden der Anwendung oder am Ende eines Request-Handlers, der eine Verbindung etwas länger offenhält. Es geht schnell (meist nur Millisekunden) und macht überhaupt nur dann Arbeit, wenn wirklich etwas aktualisiert werden muss.
Das ist übrigens nicht dasselbe wie ANALYZE, was die Statistiken komplett neu aufbaut. optimize ist eher der schlanke kleine Bruder, den man ruhig häufig laufen lassen kann.
Alle Einstellungen auslesen
Wenn du wissen willst, wie eine Verbindung gerade konfiguriert ist, fragst du die PRAGMAs einfach ohne Zuweisung ab:
Praktisch beim Debuggen — wenn du dich mit einem anderen Treiber verbindest und dich fragst, warum sich das Verhalten plötzlich geändert hat, liegt es fast immer an unterschiedlichen PRAGMA-Einstellungen.
Es gibt außerdem PRAGMA pragma_list;, das dir alle PRAGMAs auflistet, die der jeweilige Build unterstützt:
PRAGMA pragma_list;
Nicht unbedingt etwas zum Auswendiglernen, aber praktisch, wenn man's braucht.
Einstellungen, die ins CREATE gehören – nicht zur Laufzeit
Manche PRAGMAs konfigurieren die Datenbankdatei selbst und greifen nur dann, wenn noch keine Tabellen angelegt wurden:
PRAGMA page_size = 8192;— Seitengröße auf der Platte. Standard sind 4096, was für die meisten Anwendungsfälle völlig ausreicht. Größere Seiten lohnen sich vor allem bei breiten Zeilen.PRAGMA encoding = 'UTF-8';— Textkodierung.
PRAGMA page_size = 8192;
PRAGMA encoding = 'UTF-8';
CREATE TABLE ...
Wenn du page_size bei einer bestehenden Datenbank änderst, musst du anschließend ein VACUUM ausführen, damit die Änderung greift. Setze diese PRAGMA-Werte einmalig beim Anlegen der Datenbank — danach kannst du sie getrost vergessen.
Ein realistisches Snippet zum Verbindungsaufbau
Im Anwendungscode steht so etwas üblicherweise dort, wo die Verbindung geöffnet wird. Vom Prinzip her sieht das so aus:
-- Bei jeder neuen Verbindung einmal ausführen:
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -20000;
PRAGMA temp_store = MEMORY;
-- Regelmäßig oder vor dem Schließen ausführen:
PRAGMA optimize;
temp_store = MEMORY hält temporäre Tabellen und Indizes im RAM. Das beschleunigt Abfragen, die ohne Index sortieren oder aggregieren müssen.
Das ist die komplette Checkliste für den Produktivbetrieb. Ein halbes Dutzend Zeilen – und SQLite wechselt von „okay für die Entwicklung" zu „tauglich für echten Workload".
Als Nächstes: Typische Fehler
Auch mit sauber gesetzten PRAGMAs begegnen dir früher oder später die üblichen SQLite-Fehler – database is locked, disk I/O error, constraint failed. Auf der nächsten Seite schauen wir uns an, was jeder dieser Fehler tatsächlich bedeutet und wie du ihn behebst.
Häufig gestellte Fragen
Was sind PRAGMA-Statements in SQLite?
PRAGMAs sind SQLite-spezifische Befehle, mit denen du das Verhalten der Datenbank-Engine ausliest oder veränderst. Du setzt sie wie normales SQL ab: PRAGMA journal_mode = WAL; schaltet den Journaling-Modus um, PRAGMA foreign_keys; zeigt dir den aktuellen Wert. Die meisten PRAGMAs gelten pro Connection — du setzt sie also typischerweise direkt nach dem Öffnen der Datenbank.
Welche PRAGMA-Settings sind in Production sinnvoll?
Eine solide Baseline für die meisten Anwendungen: journal_mode = WAL, synchronous = NORMAL, foreign_keys = ON, busy_timeout = 5000 und ein großzügig gesetztes cache_size. Vor dem Schließen langlebiger Connections lohnt sich noch ein PRAGMA optimize. Damit hast du parallele Reads, dauerhafte Writes und referentielle Integrität — ohne viel Aufwand.
Warum ist PRAGMA foreign_keys standardmäßig aus?
Aus Gründen der Abwärtskompatibilität. SQLite hat die Foreign-Key-Prüfung erst in Version 3.6.19 eingeführt und das Feature bewusst deaktiviert gelassen, damit bestehende Datenbanken nicht plötzlich Writes ablehnen. Du musst es bei jeder neuen Connection mit PRAGMA foreign_keys = ON; aktivieren — das ist keine Einstellung auf Datenbank-Ebene, sondern gilt pro Verbindung.
Was macht PRAGMA optimize?
PRAGMA optimize führt leichte Wartungsarbeiten aus — vor allem aktualisiert es die Statistiken, die der Query-Planner zur Index-Auswahl nutzt. Es ist günstig und gefahrlos regelmäßig aufrufbar. Die übliche Empfehlung: kurz vor dem Schließen langlebiger Connections aufrufen, damit der Planner beim nächsten App-Start mit frischen Statistiken arbeitet.
