JSONB-Spalten: wofür sie taugen und wann eine eigene Spalte besser ist

Worum es geht

PostgreSQL kennt zwei JSON-Typen. Die Dokumentation hält fest, dass json eine exakte Kopie des Eingabetexts speichert, während jsonb eine zerlegte binäre Form ablegt, die Leerraum, Schlüsselreihenfolge und doppelte Schlüssel nicht erhält, beim Einlesen etwas langsamer, bei der Verarbeitung aber deutlich schneller ist und Indexierung unterstützt; sie empfiehlt jsonb für die meisten Anwendungen. Containment (@>), Schlüsselexistenz (?) und JSON-Pfadoperatoren (@?, @@) können einen GIN-Index nutzen; die nicht standardmässige Operatorklasse jsonb_path_ops unterstützt die Schlüsselexistenz-Operatoren nicht, ist aber meist deutlich kleiner als die Standardklasse und sucht in der Regel schneller. Der Abschnitt zum Design empfiehlt Dokumente mit einer einigermassen festen Struktur und überschaubarer Grösse, da jede Aktualisierung die gesamte Zeile sperrt.

Warum es wichtig ist

jsonb erspart Migrationen für Attribute, die sich häufig ändern oder von aussen definiert werden: Webhook-Payloads, benutzerdefinierte Felder, mandantenspezifische Einstellungen. Bei Kerndaten eingesetzt, nimmt es der Datenbank die Möglichkeit, Typen, Eindeutigkeit und Referenzen durchzusetzen, und verlagert jede Validierung in den Anwendungscode, wo sie jede schreibende Stelle erneut umsetzen muss.

So wird es angewendet

• jsonb einsetzen für rohe Payloads, die zu Revisionszwecken aufbewahrt werden, für spärliche Attribute, die je Datensatztyp unterschiedlich sind, für Einstellungen, die als Ganzes gelesen und geschrieben werden, sowie für Daten, deren Schema von einem anderen System verwaltet wird.
• Eigene Spalten verwenden für alles, wonach in den meisten Abfragen gefiltert, verknüpft, aggregiert oder sortiert wird, für alles mit Fremdschlüssel, Eindeutigkeit oder CHECK sowie für Geldbeträge, Datumswerte und Identifikatoren, die einen eigenen Typ brauchen.
• Für Containment-Abfragen mit GIN (col jsonb_path_ops) indexieren; für einen einzelnen häufig genutzten Schlüssel ist ein Ausdrucksindex auf (col->>'key') oder eine generierte Spalte kleiner und unterstützt Gleichheits- und Bereichsabfragen.
• Die Struktur mit einem CHECK auf Basis von jsonb_typeof für Pflichtschlüssel einschränken oder vor dem Schreiben gegen ein JSON Schema validieren; die erwarteten Schlüssel neben der Spalte dokumentieren.
• Pfade mit jsonb_set oder || aktualisieren statt über die Anwendung hin- und herzureichen, wobei in jedem Fall eine neue Zeilenversion geschrieben wird.

Stolpersteine

json zu wählen, weil es einfacher wirkt: Es lässt sich nicht auf dieselbe Weise indexieren und erhält Duplikate und Schlüsselreihenfolge, die jsonb verwirft. Die Dokumentation merkt an, dass jsonb das Escape \u0000 und Zahlen ausserhalb des numeric-Wertebereichs zurückweist. Grosse Dokumente, die Feld für Feld aktualisiert werden, verursachen Bloat und Sperrkonflikte. Ein GIN-Index hilft weder bei ORDER BY noch bei Bereichsprädikaten auf einem Schlüssel.