JSON mit Postgres

Die Aufgabe

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 Umsetzung

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)