Postgres hat in der Version 9.3 den JSON Datentyp spendiert bekommen, der die effiziente Speicherung und den einfachen Zugriff auf json Dokumente erlaubt. In der Version 9.4 wurde ihm nun der Datentyp JSONB zur Seite gestellt. Dieser speichert json Daten binär und voll indizierbar und ist sowohl bei Zugriff als auch bei Speicherung effizienter als sein Vorgänger. Diese Datentypen sind sich ähnlich genug, dass sie für gewöhnliche Anwendungen untereinander ausgetauscht werden können. Damit steht Postgres bei der Speicherung unstrukturierter Daten der NOSQL Konkurrenz um nichts nach und überflügelt sie sogar deutlich.
Für die allermeisten Anwendungsfälle ist nach wie vor die strukturierte Speicherung von Daten die beste Lösung. Bei der Neuentwicklung eines Moduls für das Digital Concepts Framework hatten wir einen Anwendungsfall, für den dieser Datentyp sich als idealer Kandidat erwies.
Es galt ein Modul zu entwickeln, das die Beschreibung von Immobilien in beliebig vielen Sprachen erlaubt. Verschiedene Objekte haben dabei unterschiedliche Felder. Zu erwarten waren viele SELECTs und nur relativ wenige UPDATE Operationen. Natürlich haben wir diese Art von Anforderung schon auf verschiedene Arten, mit den jeweiligen Vor- und Nachteilen, gelöst.
Die volle Unterstützung des JSON Datentyps in Postgres erlaubt uns aber alle Sprachversionen der einzelnen Felder auf effiziente und übersichtliche Weise zu speichern und die Anzahl der JOINS ohne Einschränkungen der Abfragemöglichkeiten zu minimieren. Gratis bekommen wir noch die Möglichkeit dazu, alle Sprachversionen direkt als JSON zu übermitteln, um so im Backend zB via AngularJS die einzelnen Sprachversionen ohne zusätzliche AJAX oder vollständige Seitenaufrufe anzeigen zu können.
Die erhofften Vorteile, kurz zusammengefasst, waren daher:
- vereinfachtes Datenmodell
- effizientere Abfragen durch Vermeidung von JOINS
- effiziente, native Rückgabe aller Sprachversionen für den Administrationsbereich.
Wie immer hielten wir vor allem die Abfrageperformance ganz besonders im Auge. Da wir mit dem Upgrade von Postgres auf eine "x.y.1" Version warten, und bisher noch die Version 9.4.0 von Postgres aktuell ist, müssen wir uns mit dem JSON Datentyp bescheiden, werden aber auf JSONB wechseln, sobald das möglich ist.
Die genauen Details der Umsetzung ist recht umfangreich, darum werde ich hier nur eine vereinfachte Version beschreiben. Die Vereinfachungen betreffen nur die Anzahl der Felder, nicht jedoch die allgemeine Funktionalität.
Zuerst legen wir eine einfache Tabelle an. Der FOREIGN KEY ist deaktiviert, um das Beispiel leichter nachvollziehbar zu machen.
CREATE SEQUENCE object_id_seq;CREATE TABLE objects (
object_id INTEGER NOT NULL DEFAULT NEXTVAL('object_id_seq'),
creation_date TIMESTAMP NOT NULL DEFAULT now(),
creation_user INTEGER NOT NULL, -- REFERENCES users(user_id)
name JSON NOT NULL,
description JSON
);
Dann generieren wir 10.000 Objekte. Einen 32 Zeichen langen Namen und eine 1024 Zeichen lange Beschreibung in jeweils 7 Sprachen. Für unsere Anforderungen sind wir damit im sicheren Bereich, die tatsächliche Objektanzahl wir 10.000 nie erreichen.
INSERT INTO objects (
creation_date, creation_user, name, description
)
SELECT now() AS creation_date, 1 AS creation_user,
('{' ||
'"hu":"' || md5(random()::TEXT) || '",' ||
'"es":"' || md5(random()::TEXT) || '",' ||
'"it":"' || md5(random()::TEXT) || '",' ||
'"cs":"' || md5(random()::TEXT) || '",' ||
'"fr":"' || md5(random()::TEXT) || '",' ||
'"de":"' || md5(random()::TEXT) || '",' ||
'"en":"' || md5(random()::TEXT) || '"' ||
'}')::JSON AS name,
('{' ||
'"hu":"' || encode(gen_random_bytes(1024), 'hex') || '",' ||
'"cs":"' || encode(gen_random_bytes(1024), 'hex') || '",' ||
'"it":"' || encode(gen_random_bytes(1024), 'hex') || '",' ||
'"cs":"' || encode(gen_random_bytes(1024), 'hex') || '",' ||
'"fr":"' || encode(gen_random_bytes(1024), 'hex') || '",' ||
'"de":"' || encode(gen_random_bytes(1024), 'hex') || '",' ||
'"en":"' || encode(gen_random_bytes(1024), 'hex') || '"' ||
'}')::JSON AS description
FROM generate_series(1,10000)
