So nutzt man SQLite-JSON mit voller Indexgeschwindigkeit: Schnelle Abfragen mit generierten Spalten

(dbpro.app)

11 Punkte von GN⁺ 2025-12-13 | 2 Kommentare | Auf WhatsApp teilen

Einführung in einen Ansatz, bei dem mit den JSON-Funktionen von SQLite das ursprüngliche JSON-Dokument unverändert gespeichert und benötigte Felder über virtuelle generierte Spalten (virtual generated columns) extrahiert und indiziert werden
Mit der Funktion json_extract lassen sich Daten innerhalb von JSON wie Spalten behandeln, sodass Abfragen mit B-Tree-Indexgeschwindigkeit ausgeführt werden können
Wenn neue Abfragemuster benötigt werden, lässt sich das Schema durch das Hinzufügen von Spalten und Indizes ohne Datenmigration erweitern
Dieser Ansatz vereint die Flexibilität schemafreier Daten mit der Performance relationaler Datenbanken
Hervorgehoben als praxisnahes Muster mit schlanker Struktur und hoher Performance für Entwickler, die SQLite einsetzen

Kombination von SQLite und JSON-Funktionen

SQLite unterstützt JSON-Funktionen und -Operatoren, wodurch JSON-Daten direkt gespeichert und verarbeitet werden können
- Das JSON-Dokument wird unverändert in einer Spalte gespeichert, und nur die benötigten Informationen werden in virtuelle Spalten extrahiert
- Dieser Ansatz ermöglicht einen flexiblen Umgang mit Daten ohne festes Schema
Das Team von DB Pro hat SQLite in den letzten Monaten intensiv genutzt und diese Funktionalität in der Praxis validiert
- SQLite kann bei passender Konfiguration auch in Produktionsumgebungen zuverlässig eingesetzt werden

Virtuelle generierte Spalten (Generated Columns)

Mit json_extract werden bestimmte Werte innerhalb von JSON als virtuelle generierte Spalten definiert
- Diese Spalten speichern die eigentlichen Daten nicht, sondern werden zum Zeitpunkt der Abfrage berechnet und stehen sofort zur Verfügung
- Es sind kein separates Backfill und keine Datenkopien nötig
So lässt sich zum Beispiel eine Struktur umsetzen, bei der bestimmte Felder aus JSON extrahiert und wie normale Spalten verwendet werden

Indizes hinzufügen und Performance steigern

Fügt man den virtuellen Spalten Indizes hinzu, können auch JSON-Daten mit B-Tree-Indexgeschwindigkeit durchsucht werden, genau wie normale Spalten
- Virtuelle Spalten mit Index bieten dieselbe Performance wie Spalten in relationalen Datenbanken
Dieser Ansatz ermöglicht schnelle Suchen auch bei größeren JSON-Datenmengen

Neue Abfragemuster hinzufügen

Wenn später Suchen über neue Felder nötig werden, reicht es aus, eine neue virtuelle Spalte und einen Index hinzuzufügen
- Beispiel: Extraktion des Felds user_id und Erzeugung eines Indexes dafür
- Bestehende Datenzeilen müssen weder geändert noch migriert werden
So entsteht sofortige Erweiterbarkeit für Abfragen, ohne die Datenstruktur ändern zu müssen

Vorteile und Bedeutung des Musters

Dieses Muster verbindet die Flexibilität schemafreier JSON-Speicherung mit der Index-Performance relationaler Datenbanken
- In der frühen Entwurfsphase muss die Indexierungsstrategie nicht vorab vollständig festgelegt werden
- Stattdessen können Spalten und Indizes bei Bedarf ergänzt und gezielt optimiert werden
Für Entwickler, die SQLite nutzen, wird es als einfacher und zugleich leistungsstarker Ansatz zur Datenverarbeitung vorgestellt
DB Pro kündigt weitere Beiträge zu den verschiedenen Funktionen von SQLite an

2 Kommentare

GN⁺ 2025-12-13

Hacker-News-Kommentare

JSON-Dokumente lassen sich direkt als serialisierter B-Tree kodieren
Dadurch können innere Felder mit Index-Geschwindigkeit direkt abgefragt werden, und weil das Dokument selbst bereits indexiert ist, entfällt das Parsen
Dieses Format heißt Lite³. Es ist ein Projekt, an dem ich selbst arbeite
GitHub-Link
- Wirklich großartig! Ich mag Rkyv, aber dafür braucht man Rust, was für kleine Projekte etwas schwergewichtig war
  Besonders gut gefällt mir an Lite³, dass es mit lite3_val_bytes Binärdaten unterstützt
- Ich habe mich gefragt, wie sich Lite³ von JSONB in PostgreSQL unterscheidet
  JSONB kodiert Array-Längen und Offsets zusammen und kann so zwischen Kompressionseffizienz und Geschwindigkeit abwägen
  Lite³ erlaubt In-Place-Updates, braucht aber regelmäßiges „vacuum“, damit keine sensiblen Daten zurückbleiben
  JSONB lässt sich ohne Re-Encoding schwer aktualisieren, während Lite³ sich allein durch Traversieren der Struktur leicht bereinigen lässt
  Bei der Komprimierbarkeit dürfte JSONB besser sein, aber ich halte das Design von Lite³ für einen sehr cleveren Ansatz
  Ich betreue selbst einen ASN.1-Compiler und interessiere mich daher sehr für solche Serialisierungsformate. Durch Lite³ habe ich neue Ideen bekommen
- Eine Implementierung in Rust wäre wirklich schön
Ich mag SQLite wirklich sehr, nutze für Analysen aber häufiger DuckDB
DuckDB verwendet wie SQLite eine einzelne Datei und verarbeitet zugleich sehr große Datensätze extrem schnell
Auf einem M2 MacBook ist es selbst mit 20 Millionen Datensätzen sehr schnell
Zum Beispiel kann man mit der folgenden Abfrage JSON-Dateien direkt einlesen
```
SELECT avg(sale_price), count(DISTINCT customer_id)
FROM '/my-data-lake/sales/2024/*.json';
```
Außerdem kann man Spalten vom Typ JSON laden und auch die Postgres-ähnliche Syntax col->>'$.key' verwenden
- Ich frage mich, ob die erste Abfrage JSON-Dateien im Dateisystem spontan indiziert
- Mit dem Visualisierungstool pygwalker kann DuckDB Daten mit mehreren Millionen Zeilen in wenigen Sekunden analysieren
  Der Vergleich mit SQLite ist allerdings etwas unfair. SQLite ist eher für den Systembau gedacht, DuckDB für Analysen
  Für die Verteilung auf mehrere Plattformen ist DuckDB etwas knifflig
- Man sollte Datensätze unbedingt nicht unkomprimiert speichern. DuckDB unterstützt verschiedene Kompressionsformate
Für JSON-Performance Generated Columns zu verwenden, hielt ich für eine gängige Methode
In Postgres habe ich das auch schon genutzt, um Schlüssel innerhalb einer JSON-Spalte als Fremdschlüssel zu erhalten. Es ist ein leicht „verfluchter“ Ansatz, hat aber gut funktioniert
- In Postgres kann man doch direkt Indizes auf innere Felder von JSONB setzen, oder nicht?
  Zum Beispiel
```
CREATE INDEX idx_status_gin
ON my_table
USING gin ((data->'status'));
```
  Referenz-Blog
- Trotzdem ist es oft besser, das am Ende in eine Key/Value-Tabelle auszulagern
  Wenn sich das JSON-Schema ändert, können Parsen oder Migrationen fehlschlagen
- Eigentlich ist das gar kein so „verfluchter“ Ansatz. Was man braucht, legt man in eine normalisierte relationale Struktur, den Rest behandelt man mit jsonb
- Ich habe mich gefragt, ob man statt STORED auch VIRTUAL-Spalten verwenden kann, und dieses Beispiel nutzt VIRTUAL
Auf diese Technik bin ich kürzlich durch ein Optimierungsbeispiel gestoßen, das Claude Code vorgeschlagen hat
Ich kannte diese neuere SQLite-Funktion nicht und hatte sie deshalb übersehen, aber die Leistungssteigerung war ziemlich groß
Die Lehre daraus ist, dass es sich lohnt, selbst bei vertrauten Werkzeugen regelmäßig die Dokumentation noch einmal zu lesen
- Das Handbuch erneut zu lesen, kann überraschend augenöffnend sein
Nachdem ich 2023 auf HN einen Kommentar von bambax gesehen hatte, habe ich den Blogbeitrag geschrieben
Link zum ursprünglichen Kommentar
Man kann auch Indizes erstellen, ohne JSON direkt zu projizieren, aber eine berechnete Spalte (computed column) vereinfacht die Abfragen
Vor MS-SQL 2025(v17) war die JSON-Unterstützung eingeschränkt, daher war dieser Ansatz notwendig
- Wenn man JSON nicht direkt abfragt und nur berechnete Spalten verwendet, schreibt man nicht versehentlich nicht indexierte Abfragen
- Ich habe auf einer lokalen DBA-Konferenz von dieser Funktion gehört, aber damals wirkte sie nicht wie eine große Veränderung
Ich habe den Artikel auf HN geöffnet und gesehen, dass mein Kommentar zitiert wird und sogar das Thema des Beitrags ist — eine seltsame Erfahrung
Als ich den Satz „Danke, bambax!“ sah, musste ich lächeln. SQLite ist wirklich ein großartiges Werkzeug
- Der tatsächlich inspirierende Kommentar ist unter diesem Link
Interessant, aber könnte man nicht auch "Index On Expression" in SQLite verwenden?
Zum Beispiel CREATE INDEX idx_events_type ON events(json_extract(data, '$.type'))
Wenn sich die JSON-Path-Syntax jedoch auch nur leicht unterscheidet, wird der Index möglicherweise nicht verwendet. Eine Virtual Generated Column garantiert den Index dagegen immer
- Wenn man Ausdrucksindizes zusammen mit Views nutzt, kann man die Übereinstimmung der Ausdrücke sicherstellen
  Beispiel: recordlite-Projekt
- Schon kleine Unterschiede in der JSON-Path-Syntax können den Index unbrauchbar machen
  Zum Beispiel werden json_extract(data, "$.type") und data -> '$.type' unterschiedlich behandelt
  Wenn sich also der Ausdruck in der WHERE-Klausel ändert, wird der Index nutzlos
- Das ist eine einfache und schnelle Lösung. Dass Abfrage und Index zueinander passen müssen, gilt ohnehin immer
- Ausdrucksindizes sind in SQLite erst seit Version 3.9.0 (2015-10-14) verfügbar und damit eine vergleichsweise neue Funktion
Ich wünschte, Entwickler würden sich etwas zurückhalten, selbst bei konsistentem Schema alle Daten in JSON(B)-Spalten zu stecken
Das Einrichten von Indizes, das Verwalten von Constraints und der tatsächliche Einsatz bringen viel Overhead mit sich
- JSON-Spalten glänzen dort, wo Daten eine Baumstruktur haben und sich nur schwer als Tabellen ausdrücken lassen
  Zum Beispiel ist es in einer Haskell+TypeScript-Umgebung viel bequemer, komplexe verschachtelte Strukturen als JSON zu serialisieren
- JSON(B) ist nützlich, wenn man Daten unterschiedlicher Typen in einer Sammlung unterbringen will
  Beispiel: Ergebnisse verschiedener Zahlungsabwickler in einer einzigen Tabelle speichern oder auf einer Kleinanzeigenplattform je nach Artikel unterschiedliche Eigenschaften verwalten
  Auch in C# oder JS/TS bleibt das mit Tools zur Typvalidierung wie Zod oder OpenAPI gut handhabbar
- Bei einfachem JSON ist Normalisierung besser, aber komplexe API-Antworten als Tabellen aufzulösen führt schnell zur JOIN-Hölle
  Letztlich ist es eine Frage des Gleichgewichts zwischen Wartbarkeit und Performance. Der Kern dieses Beitrags ist, dass man auch auf JSON leicht Indizes setzen kann
- Wenn man wie bei Sensordaten die gesamte Baumstruktur auf einmal verarbeitet, ist eine JSON-Spalte viel einfacher
  Die Lese-Performance lässt sich mit Indizes ausreichend kompensieren
- Vollständige Normalisierung ist oft ineffizient
  In einem System zur Produktpreisgestaltung lassen sich zum Beispiel kundenspezifische Sonderrabattregeln mit JSON viel flexibler ausdrücken
Wenn man statt JSON XML verwenden würde, entspräche das demselben Modell wie die dokumentenorientierten Datenbanken (document stores) der 90er- und 00er-Jahre
Beim Einfügen und Aktualisieren wurde geparst, bei Abfragen nur auf den Index zugegriffen
Dass SQLite so etwas standardmäßig bereitstellt, ist wirklich interessant

iolothebard 2025-12-14

Ende des 20. Jahrhunderts … gab es so etwas wie eine universelle Datenbank … (Heute stimmt es, damals war es falsch.)