Auch SQLite braucht Rust-ähnliche Editionen

 (mort.coffee)
3 Punkte von GN⁺ 3 시간 전 | 2 Kommentare | Auf WhatsApp teilen
  • SQLite ist ein in sich geschlossenes RDBMS, das häufig für lokalen Speicher und Embedded-Projekte verwendet wird, behält wegen der Abwärtskompatibilität aber Standardwerte bei, die Sicherheit und Performance beeinträchtigen
  • Standardmäßig werden Foreign-Key-Constraints nicht erzwungen, und ROWID kann wiederverwendet werden; dadurch können Probleme mit der Datenintegrität entstehen, etwa wenn Beiträge eines gelöschten Nutzers mit einem anderen Nutzer verknüpft werden, der dieselbe ID erhalten hat
  • Normale Tabellen speichern auch Datentypen, die von den deklarierten Spalten abweichen; STRICT-Tabellen verhindern das, erfordern aber pro Tabelle die Angabe von strict und kollidieren mit der bisherigen Nutzung benutzerdefinierter Typnamen
  • Bei konkurrierenden Schreibvorgängen sofort auftretendes SQLITE_BUSY, deaktiviertes WAL und konservative Synchronisierungseinstellungen lassen sich jeweils über die Pragmas busy_timeout, journal_mode und synchronous verbessern
  • Mit jahresbasierten Editionen wie PRAGMA edition = 2026 ließe sich ein Bündel sicherer Standardwerte auswählen, sodass SQLite seine Defaults weiterentwickeln kann, ohne bestehendes Verhalten zu brechen

Die Stärken von SQLite und veraltete Standardwerte

  • SQLite ist die Datenbank für Embedded-Projekte und kann als Branchenstandard für lokale Datenspeicherung gelten; auch manche Server-Software wie lobste.rs läuft auf SQLite
  • Anders als traditionelle RDBMS, die als separater Prozess laufen, ist es ein RDBMS in Bibliotheksform, wodurch Software in sich geschlossen bleiben kann
  • Da man nicht wie bei gewöhnlichen Dateiformaten eigene Serializer und Parser schreiben muss, vereint es Vorteile eines RDBMS mit denen dateibasierter Speicherung
  • Es bleibt jedoch das Problem, dass mehrere Standardwerte ungeeignet sind und Datenintegrität, Nebenläufigkeit und Performance beeinflussen

Integritätsprobleme, weil Foreign Keys nicht erzwungen werden

  • Foreign Keys sind ein zentrales Mittel, um die Konsistenz einer Datenbank zu erhalten und gebrochene Referenzen zu verhindern; SQLite erzwingt sie standardmäßig jedoch nicht
  • Selbst wenn posts.user_id so deklariert ist, dass es auf users.id verweist, verhindert die Standardeinstellung folgendes Verhalten nicht
    • Erstellen eines Beitrags ohne gültige Nutzer-ID
    • Löschen eines Nutzers, während dessen Beiträge erhalten bleiben
  • INTEGER PRIMARY KEY wird zu einem Alias für die ROWID der Tabelle, und der ROWID-Zuweisungsalgorithmus von SQLite verwendet IDs in manchen Situationen erneut
    • Wenn Bob mit ID 1 einen Beitrag schreibt und anschließend nur sein Konto gelöscht wird, tritt kein Foreign-Key-Fehler auf
    • Erhält Alice später die wiederverwendete ID 1, werden Bobs bestehende Beiträge Alice zugeordnet
    • Die Abfrage selbst ist erfolgreich, daher ist das Problem schwerer zu entdecken als eine einfache gebrochene Referenz
  • Wenn man pro Verbindung das folgende Pragma aktiviert, lassen sich Foreign Keys erzwingen
PRAGMA foreign_keys = ON;
  • Wäre dies von Anfang an angewendet worden, würde das Löschen eines Nutzers mit verbleibenden Beiträgen mit dem Fehler FOREIGN KEY constraint failed abgebrochen

Normale Tabellen erlauben Datentypen, die von der Deklaration abweichen

  • SQLite-Werte sind entweder NULL, INTEGER, REAL, TEXT oder BLOB, doch Spaltendeklarationen in normalen Tabellen beschränken die speicherbaren Datentypen nicht streng, sondern bestimmen die Typaffinität (type affinity)
  • Für Spalten mit INTEGER-Affinität gelten folgende Speicherregeln
    • Als Ganzzahl interpretierbarer TEXT wird in INTEGER umgewandelt
    • Als Fließkommazahl interpretierbarer TEXT wird in REAL, also eine doppelt genaue Fließkommazahl, umgewandelt
    • Andere Werte werden unverändert mit ihrem ursprünglichen Datentyp gespeichert
  • Für andere Affinitäten gibt es eigene Umwandlungsregeln
    • BLOB-Affinität speichert Werte unverändert
    • TEXT-Affinität belässt BLOB, TEXT und NULL unverändert und wandelt Zahlen in TEXT um
    • REAL-Affinität verhält sich wie INTEGER-Affinität, wandelt Ganzzahlen aber in REAL um
  • Deshalb kann selbst in einer Spalte duration_sec INTEGER eine nicht numerische Zeichenkette wie 'Way too long, I mean come on' gespeichert werden
  • In einem realen Projekt gab es auch den Fall, dass statt der Zahlen 1 und 0 für Booleans die Zeichenketten '1' und '0' geschrieben wurden, sodass Daten bereinigt und debuggt werden mussten

STRICT-Tabellen und der Konflikt mit benutzerdefinierten Typen

  • STRICT-Tabellen lösen Typfehler wie cannot store TEXT value in INTEGER column aus, wenn ein falscher Datentyp in eine Spalte geschrieben wird
CREATE TABLE music (
  id INTEGER PRIMARY KEY,
  name TEXT,
  duration_sec INTEGER
) strict;

  • Es gibt kein Pragma, das alle Tabellen global strikt macht; daher muss strict in jeder CREATE TABLE-Anweisung vollständig ergänzt werden

  • SQLite vertritt die Position, dass flexible Typen nützlich sind, doch auch in STRICT-Tabellen kann man mit dem Datentyp ANY Spalten anlegen, die Werte beliebiger Art speichern

    • So lassen sich Spalten, die beliebige Datentypen erlauben, explizit von Spalten unterscheiden, die bestimmte Datentypen erzwingen

  • Das realistischere Kompatibilitätsproblem besteht darin, dass STRICT-Tabellen nicht nur Datentypen erzwingen, sondern auch die Regeln zur Interpretation von Typspezifizierern ändern

  • Wie normale Tabellen Typnamen interpretieren

    • Normale SQLite-Tabellen bestimmen die Affinität anhand des deklarierten Typ-Strings
    • Wenn "INT" enthalten ist, wird er als INTEGER behandelt
    • Wenn eines von "CHAR", "CLOB" oder "TEXT" enthalten ist, wird daraus TEXT
    • Wenn "BLOB" enthalten ist oder der Typ weggelassen wird, wird er als BLOB behandelt
    • Wenn eines von "REAL", "FLOA" oder "DOUB" enthalten ist, wird daraus REAL
    • Trifft keine Bedingung zu, wird er als NUMERIC behandelt
    • Kombiniert man die lockere Typisierung mit diesen Regeln, lassen sich Spalten benutzerdefinierte Typnamen wie DATETIME, KEY_VALUE_SET oder COLOR geben
    • Datenbank-Connectoren oder Wrapper können anhand des Typnamens automatisch serialisieren und deserialisieren
    • Selbst ohne spezielle Verarbeitung dokumentiert der Typname selbst die erwarteten Daten der Spalte
    • Würde STRICT unverändert zum Standard, müsste man auf diese Nutzung verzichten; daher braucht es explizite benutzerdefinierte Typ-Aliasse
    CREATE TYPE KEY_VALUE_SET = TEXT;
    • Wenn in strikten Tabellen Aliasse wie KEY_VALUE_SET verwendet werden könnten, ließe sich auch das erwartete Datenmuster einer TEXT-Spalte, die die Anwendung parsen muss, im Schema festhalten
    • Nützlich wäre auch eine Möglichkeit, benutzerdefinierte Typen mit CHECK-Constraints zu verknüpfen
    • Der SQL:1999-Standard enthält mit CREATE DOMAIN bereits eine Syntax für Typ-Aliasse inklusive Constraints, sodass SQLite dies durch Unterstützung dieser Standardanweisung implementieren könnte

Konkurrierende Schreibvorgänge und SQLITE_BUSY

  • SQLite erlaubt mehrere gleichzeitige Lesevorgänge, verarbeitet Schreibvorgänge aber nur einzeln
  • In der Standardeinstellung erhält einer von zwei Prozessen, die gleichzeitig eine Schreibsperre bekommen wollen, sofort einen SQLITE_BUSY-Fehler, statt zu warten
    • Schreibvorgänge mit Disk-I/O können naturgemäß langsam sein; es wäre daher naheliegender, eine gewisse Zeit auf die Freigabe der Sperre zu warten
    • Es gab auch Fälle, in denen reale Systeme sporadisch stoppten und man selbst Retry-Loops schreiben musste
  • Die folgende Einstellung versucht bis zu 5 Sekunden lang erneut, die Sperre zu erhalten, und gibt erst danach SQLITE_BUSY zurück
PRAGMA busy_timeout = 5000;
  • Im normalen Betrieb ist es sinnvoll, alle Schreibvorgänge in einem einzigen Prozess und möglichst in einem einzigen Thread zu bündeln; konkurrierende Schreibvorgänge können nicht schnell sein
  • Es gibt jedoch auch legitime Betriebsfälle, in denen konkurrierende Schreibvorgänge nötig sind
    • Interaktive Bereinigung der Datenbank mit dem Kommandozeilentool sqlite3
    • Ausführung von Skripten für seltene Verwaltungsaufgaben ohne separates Frontend
  • Solche Aufgaben können wegen der Standardeinstellung unerwartetes SQLITE_BUSY auslösen und laufende Software unterbrechen

WAL und Performance-Defaults

  • Richtig konfiguriert kann SQLite so schnell sein, dass es teilweise Aufgaben übernimmt, die sonst große Server wie PostgreSQL oder MySQL erledigen; die Standard-Performance ist jedoch nicht gut
  • Mehr zur Feinabstimmung in Serverumgebungen findet sich in Optimizing SQLite for servers
  • Das wichtigste Problem bei den Standardwerten ist, dass das Write-Ahead Log, also WAL, deaktiviert ist
PRAGMA journal_mode = WAL;
  • WAL erhöht in den meisten Situationen die Schreibgeschwindigkeit erheblich
  • Zusammen mit der folgenden Einstellung lässt sich die Zahl der Datenträgersynchronisierungen stark reduzieren, ohne ein Risiko von Datenbeschädigung einzugehen
PRAGMA synchronous = NORMAL;

Jahresbasierte SQLite-Editionen

  • Würden die bisherigen Standardwerte jetzt direkt geändert, könnte alte Software kaputtgehen, und Nutzer könnten künftige SQLite-Upgrades fürchten
  • Um unter Wahrung der Abwärtskompatibilität verbesserte Standardwerte auszuwählen, könnte ein einziges übergeordnetes Pragma (super pragma) ergänzt werden
PRAGMA edition = 2026;
  • Die Edition 2026 würde mindestens als Alias für die folgenden Einstellungen funktionieren
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
  • In dieser Edition würde auch der strict-Modus für neue Tabellen standardmäßig gelten
  • Wie bei den Rust-Editionen würde man explizit neues Standardverhalten wählen, während das Verhalten bestehenden Codes erhalten bleibt
  • Anders als ein einzelner Modus wie "use strict"; in JavaScript können jahresbasierte Editionen das Bündel vernünftiger Standardwerte im Lauf der Zeit ändern
  • Wenn zum Beispiel WAL2 von Hctree im Jahr 2034 in den Hauptzweig aufgenommen würde, könnte ein künftiges PRAGMA edition = 2034 so konfiguriert werden, dass es journal_mode = WAL2 auswählt
  • Ein Editionssystem könnte ein Kompromiss sein, mit dem SQLite sichere und performante Standardwerte weiterentwickeln kann, ohne das Standardverhalten bestehender Programme zu brechen

Verwandte Beiträge

2 Kommentare

 
tomskang 2 시간 전

Die Theorie klingt gut, aber wenn man Kompatibilitätskonflikte und Overhead bedenkt, dürfte es sich eher nicht lohnen.
SQLite ist ein DBMS, das entstanden ist, um in Embedded-Umgebungen wie bei Aegis zu laufen.
Mit void** wild herumhantieren und nach einer __weak__-Deklaration Funktionen überschreiben – so sieht die Embedded-Welt nun mal aus. In so einer Welt frage ich mich: Muss das wirklich sein?
 
GN⁺ 3 시간 전
Lobste.rs-Meinungen

  • Ich habe das geschrieben, nachdem ich gestern die Nachricht gesehen hatte, dass lobste.rs auf SQLite umgestellt hat (https://lobste.rs/s/ko1ji1/lobste_rs_is_now_running_on_sqlite)
    Beim Lesen der damaligen Diskussion kamen mir die kleinen Unannehmlichkeiten der SQLite-Standardeinstellungen wieder in den Sinn, also habe ich sie in einem Blogpost zusammengefasst

    • Ich bin beim Lesen derselben Diskussion zum gleichen Schluss gekommen
      Wenn du Kapazität hast, wäre es sinnvoll, diesen Beitrag auch im SQLite-Forum zu teilen: https://sqlite.org/forum/forum

  • In SQL wird CREATE TYPE verwendet, um zusammengesetzte Typen zu erstellen, und Postgres unterstützt dafür Bereiche, Enumerationen, Arrays und sogar recht komplexe Basistypen
    Die gewünschte Funktion liegt näher an CREATE DOMAIN. Damit kann man einen Newtype auf Basis eines konkreten Grundtyps erstellen und außerdem Standardwerte sowie Constraints festlegen, wodurch Schemata deutlich kompakter werden, die sonst dieselben CHECK-Constraints in vielen Tabellen wiederholen müssten. Zum Beispiel kann man create domain mything as text; oder create domain mything as integer not null check (VALUE < 5); schreiben

    • Ich hätte wohl zuerst prüfen sollen, ob es bereits eine Typalias-Funktion gibt. Ich werde den Beitrag entsprechend ergänzen

  • Man sollte auch die ziemlich gefährliche Falle behandeln, dass busy_timeout allein sofort auftretende SQLITE_BUSY-Fehler nicht verhindern kann
    Wenn man eine Transaktion mit einer schreibgeschützten Abfrage beginnt und dann versucht, durch eine Schreibabfrage den gehaltenen Lock hochzustufen, schlägt das bei einem anderen laufenden Schreibvorgang sofort fehl, unabhängig von der busy_timeout-Einstellung. Um das zu lösen, muss man begin immediate verwenden

    • Ich weiß, dass das technisch kein echter Bug ist, aber ehrlich gesagt wirkt dieses Verhalten fast wie ein Bug

  • Man muss bedenken, dass einige der an die vorgeschlagene Edition gebundenen Einstellungen im SQLite-Modell Verbindungseigenschaften sind, während andere Eigenschaften der Datenbankdatei oder einzelner Tabellen sind
    PRAGMA journal_mode = WAL bleibt in der Datenbankdatei erhalten, aber PRAGMA foreign_keys = ON muss für jede Verbindung erneut gesetzt werden. STRICT ist eine Eigenschaft einzelner Tabellen, sodass man in einer Datenbank strikte und nicht strikte Tabellen mischen kann. Deshalb muss man festlegen, ob dieses vereinheitlichte PRAGMA als Eigenschaft der Datenbankdatei oder der Verbindung definiert werden soll. Ich frage mich auch, ob es einen eingebauten Mechanismus gibt, der warnt, damit alte Bibliotheksversionen nicht Dateien mit ihnen unbekannten Features ändern und dadurch beschädigen

  • Der Editions-Ansatz scheint grundsätzlich ein guter Kompromiss zwischen dem Ziel der SQLite-Entwickler zu sein, die Abwärtskompatibilität zu wahren, und dem Wunsch der Nutzer nach weniger verwirrenden Standardeinstellungen mit aktuellen Empfehlungen

  • In letzter Zeit bin ich ganz in RocksDB vertieft. Es ist wie SQLite eine eingebettete Datenbankbibliothek, aber gerade das Fehlen von SQL sehe ich eher als großen Vorteil
    Ich habe das Gefühl, dass die Einschränkung, alles in SQL ausdrücken zu müssen, beim Umgang mit Datenbanken eher hinderlich ist

    • RocksDB ist eine Key-Value-Datenbank und SQLite eine relationale Datenbank, also eher unterschiedliche Konzepte für unterschiedliche Zwecke
      Es fällt mir schwer, mir vorzustellen, wie man ein System wie lobste.rs, das SQL verwendet, auf RocksDB oder eine andere Key-Value-Datenbank übertragen sollte

  • Ich stimme nicht zu, dass das Setzen von busy_timeout die Lösung ist. SQLite erzielt mit einem einzelnen Schreib-Thread und gebündelter Verarbeitung logisch unabhängiger Transaktionen eine sehr hohe Leistung
    Die konkrete Vorgehensweise wird hier beschrieben: https://andersmurphy.com/2025/12/…

    • Für typische Workloads ist es grundsätzlich sinnvoll, Schreibvorgänge auf einen zu begrenzen und keine Struktur zu schaffen, in der mehrere Schreibvorgänge ständig miteinander konkurrieren
      Trotzdem kommt es häufig vor, dass man die Datenbank für Aufräumarbeiten direkt ändert oder gelegentliche Verwaltungsaufgaben per Skript ausführt, für die es sich nicht lohnt, eine eigene Admin-Oberfläche zu bauen. Dass dabei Server oder Anwendung zufällig ausfallen können, nur weil sich die Ausführungszeitpunkte unglücklich überschneiden, ist nicht gut