Fast jede Anwendung muss Daten persistent speichern und für fast alle Anwendungen ist eine relationale Datenbank der gewählte Weg. Dies gilt auch für Applikationen, die in Google AppEngine, ComputeEngine oder Kubernetes betrieben werden.
Möchte man sich nicht selbst um Skalierung, Replikation und Backup kümmern, bietet Google mit Cloud SQL eine Lösung an, die die Wahl zwischen den beiden wichtigsten quelloffenen Datenbanksystemen bietet: MySQL und PostgreSQL.
Was auf den ersten Blick nicht ersichtlich wird, ist, welcher Feature-Reichtum sich heutzutage hinter diesen Programmen verbirgt – und dass sie mittlerweile weit mehr sind als einfache relationale Datenbanksysteme. Die folgenden drei Beispiele anhand von PostgreSQL verdeutlichen das.
PostgreSQL Fulltext - die integrierte Volltextsuche
Zu den wohl nützlichsten Funktionen gehört die integrierte Volltextsuche, die nahtlos mit relationalen Datenbankabfragen kombiniert werden kann. Die Verwendung ist in Anbetracht des enthalten Funktionsumfangs bemerkenswert einfach.
Nehmen wir eine einfache Tabelle mit drei Test-Datensätzen als Ausgangssituation:
CREATE TABLE products (
id SERIAL,
title VARCHAR,
price INTEGER,
description TEXT,
PRIMARY KEY (id)
);
INSERT INTO products (title, price, description) VALUES
('Gummistiefel', 1299, 'Beste Gummistiefel in rot, grün oder blau - absolut Wasserdicht und Sonnenbeständig'),
('Gummienten', 499, 'Traditionelle gelbe Gummienten in gelb mit oder ohne Sprühfunktion'),
('Holzente', 699, 'rollende, gelbe Enten mit Schnur');
Wir können nun sofort und ohne weitere Vorbereitungen eine Ad-hoc-Volltextsuche durchführen:
SELECT * FROM products WHERE to_tsvector('german', titel || ' ' || description) @@ to_tsquery('german', 'Rollendes');
id | title | price | description
----+----------+-------+----------------------------------
3 | Holzente | 699 | Rollende, gelbe Enten mit Schnur
Bemerkenswert ist, wie dieses einfache Query bereits nach der Stammform von rollendes sucht und dabei die rollende gelbe Ente findet. Diese Verarbeitung wird hier noch on-the-fly und für alle Datensätze in der Tabelle durchgeführt und ist daher nicht sonderlich effizient:
EXPLAIN ANALYZE SELECT * FROM products WHERE (titel || ' ' || description) @@ to_tsquery('german', 'Rollendes');
QUERY PLAN
----------
Seq Scan on products (cost=10000000000.00..10000000001.80 rows=1 width=72) (actual time=0.126..0.133 rows=1 loops=1)
Filter: (to_tsvector('german'::regconfig, (((titel)::text || ' '::text) || description)) @@ '''rollend'''::tsquery)
Rows Removed by Filter: 2
Planning time: 0.203 ms
Execution time: 0.181 ms
(5 rows)
Dies lässt sich einfach mit einem GIN-Index beheben:
CREATE INDEX products_ftext_idx ON produkte USING GIN (to_tsvector('german', titel || ' ' || description));
EXPLAIN ANALYZE SELECT * FROM products WHERE to_tsvector('german', titel || ' ' || description) @@ to_tsquery('german', 'Rollendes');
QUERY PLAN
----------
Bitmap Heap Scan on products (cost=8.00..12.27 rows=1 width=72) (actual time=0.029..0.034 rows=1 loops=1)
Recheck Cond: (to_tsvector('german'::regconfig, (((titel)::text || ' '::text) || description)) @@ '''rollend'''::tsquery)
Heap Blocks: exact=1
-> Bitmap Index Scan on products_ftext_idx (cost=0.00..8.00 rows=1 width=0) (actual time=0.013..0.013 rows=1 loops=1)
Index Cond: (to_tsvector('german'::regconfig, (((titel)::text || ' '::text) || description)) @@ '''rollend'''::tsquery)
Planning time: 0.120 ms
Execution time: 0.081 ms
(7 rows)
Die Volltextsuche bietet einige weitere nützliche Funktionen wie beispielsweise das Gewichten von Suchtreffern nach der Treffergüte:
SELECT *, ts_rank(to_tsvector(titel || ' ' || description), to_tsquery('Gelb')) AS rank FROM products WHERE (titel || ' ' || description) @@ to_tsquery('Gelb:*') ORDER BY rank DESC;
id | title | price | description | rank
----+------------+-------+--------------------------------------------------------------------+-----------
2 | Gummienten | 499 | Traditionelle gelbe Gummienten in gelb mit oder ohne Sprühfunktion | 0.0759909
3 | Holzente | 699 | Rollende, gelbe Enten mit Schnur | 0.0607927
(2 rows)
Hier findet die Präfix-Suche nach gelb auch gelbe Enten mit Schnur, sortiert aber die Gummienten höher ein, da in ihrer Beschreibung das Wort Gelb häufiger vorkommt.
Die Postgres-Volltextsuche kann sogar die gefundenen Wörter in der Ausgabe bereits gebrauchsfertig markieren:
SELECT *, ts_headline(titel || ' ' || description, to_tsquery('Gelb:*')) AS headline FROM products WHERE (titel || ' ' || description) @@ to_tsquery('Gelb:*');
id | title | price | description | headline
----+------------+-------+--------------------------------------------------------------------+---------------------------------------------------------------------------------------------
3 | Holzente | 699 | Rollende, gelbe Enten mit Schnur | Holzente Rollende, <b>gelbe</b> Enten mit Schnur
6 | Gummienten | 499 | Traditionelle gelbe Gummienten in gelb mit oder ohne Sprühfunktion | Gummienten Traditionelle <b>gelbe</b> Gummienten in <b>gelb</b> mit oder ohne Sprühfunktion
(2 rows)
Selbstverständlich lassen sich Volltext-Abfragen mit den üblichen relationalen Funktionen verbinden: JOINs oder WHERE-Klauseln ermöglichen beispielsweise das Abrufen von zu Produkten gehörenden Preistabellen; ORDER-Klauseln erlauben das Sortieren nach beliebigen Feldern, beispielsweise nach Preis oder nach Datum.
Da der Index direkt auf der Datentabelle erstellt wurde, ist er immer aktuell und erfordert keine Extra-Synchronisierungsprozedur und kein zusätzliches Backup.
Mehr Infos zur Volltextsuche finden sich in der PostgreSQL-Dokumentation.
PostgreSQL JsonB - der integrierte Document-Store
Ein weiteres Feature, das in bestimmten Situationen außerordentlich nützlich sein kann, ist der in PostgreSQL integrierte Document-Store. Ein typischer Einsatzzweck für diesen ist beispielsweise das Speichern von Produktattributen für verschiedene Produkttypen:
CREATE TABLE products (
id SERIAL,
price INTEGER,
title VARCHAR,
attributes JSONB,
PRIMARY KEY (id)
);
INSERT INTO products (title, price, attributes) VALUES
('Gummistiefel', 1299, '{"size": "M", "color": "gelb"}'),
('8-Port Ethernet-Switch', 2099, '{"technology": "ethernet", "type": "switch", "num-ports": 8}'),
('WLAN Router', 699, '{"technology": "ethernet", "type": "router", "num-ports": 4}');
Wie auch bei der Volltextsuche können Ad-hoc-Queries auf der Attributspalte ausgeführt werden:
SELECT * FROM products WHERE attributes @> '{"technology": "ethernet"}';
id | price | title | attributes
----+-------+------------------------+--------------------------------------------------------------
2 | 2099 | 8-Port Ethernet-Switch | {"type": "switch", "num-ports": 8, "technology": "ethernet"}
3 | 699 | WLAN Router | {"type": "router", "num-ports": 4, "technology": "ethernet"}
(2 rows)
Und ebenso wie bei der Volltextsuche kann die Abfrage durch einen geeigneten Index auch bei großen Datenmengen beschleunigt werden:
CREATE INDEX products_attributes_idx ON products USING GIN (attributes);
EXPLAIN ANALYZE SELECT * FROM products WHERE attributes @> '{"technology": "ethernet"}';
QUERY PLAN
----------
Bitmap Heap Scan on products (cost=12.00..16.01 rows=1 width=72) (actual time=0.118..0.129 rows=2 loops=1)
Recheck Cond: (attributes @> '{"technology": "ethernet"}'::jsonb)
Heap Blocks: exact=1
-> Bitmap Index Scan on products_attributes_idx (cost=0.00..12.00 rows=1 width=0) (actual time=0.093..0.093 rows=2 loops=1)
Index Cond: (attributes @> '{"technology": "ethernet"}'::jsonb)
Planning time: 0.259 ms
Execution time: 0.181 ms
(7 rows)
Durch die nahtlose Integration in andere Funktionen sind auch komplexere Bedingungen möglich:
SELECT * FROM products WHERE attributes @> '{"technology": "ethernet"}' AND (attributes->>'num-ports')::int > 4;
id | price | title | attributes
----+-------+------------------------+--------------------------------------------------------------
2 | 2099 | 8-Port Ethernet-Switch | {"type": "switch", "num-ports": 8, "technology": "ethernet"}
(1 row)
Außerdem ist es natürlich möglich, JSONB-Attribute auch für die Sortierung zu verwenden oder weitere Tabellen mittels JOIN zu konsultieren.
Auch hierzu bietet die PostgreSQL-Dokumentation weitere Details.
PostgreSQL Postgis - die integrierte Geodatenbank
Zu den wohl mächtigsten Funktionen in PostgreSQL gehört PostGIS, eine vollumfängliche Geodatenbank. Mit dieser lassen sich nicht nur Punktobjekte und ihre Positionen beschreiben, sondern auch komplexe, geographische Zusammenhänge.
Nehmen wir beispielsweise eine Tabelle mit Briefkästen in Wiesbaden und ihrer jeweiligen Position an:
CREATE EXTENSION postgis;
CREATE TABLE postboxes (id SERIAL, description VARCHAR);
SELECT AddGeometryColumn ('public', 'postboxes', 'location', 3857, 'POINT', 2);
INSERT INTO postboxes (description, location) VALUES
('Kirchgasse', ST_Transform(ST_SetSRID(ST_Point(8.23819, 50.07903), 4326), 3857)),
('Frankfurter Straße', ST_Transform(ST_SetSRID(ST_Point(8.25330, 50.07536), 4326), 3857)),
('Bahnhofstraße', ST_Transform(ST_SetSRID(ST_Point(8.24172, 50.08032), 4326), 3857));
SELECT id, description, ST_AsText(location) FROM postboxes;
id | description | st_astext
----+--------------------+------------------------------------------
1 | Kirchgasse | POINT(917071.115858237 6459973.71073099)
2 | Frankfurter Straße | POINT(918753.153364124 6459337.10863785)
3 | Bahnhofstraße | POINT(917464.073660739 6460197.48707443)
Die Umrechnung der Punkte von einem Koordinatensystem in ein anderes - hier vom Koordinatensystem WGS-84 (ID 4326) in das Koordinatensystem Spherical Mercator (ID 3857) - ermöglicht es, die folgenden Berechnungen in Metern statt in Winkelgrad vorzunehmen. Die Koordinaten im SELECT-Query sind als Meterangaben in Relation zum Kreuzungspunkt aus Äquator und Null-Meridian zu lesen.
Mit derart vorbereiteten Koordinaten können wir nun nützliche Abfragen wie die folgende durchführen, die alle Briefkästen im Umkreis von 500 Metern um das Luisenforum findet (unser Bürostandort) und ihre Luftliniendistanz errechnet:
SELECT id, description, ST_Distance( location, ST_Transform(ST_SetSRID(ST_Point(8.23678, 50.07860), 4326), 3857)) AS distance_in_meters, ST_AsText(location) AS location FROM postboxes WHERE ST_Within( location, ST_Buffer( ST_Transform(ST_SetSRID(ST_Point(8.23678, 50.07860), 4326), 3857), 500) ); id | description | distance_in_meters | location ----+-------------+--------------------+------------------------------------------ 1 | Kirchgasse | 173.782556211896 | POINT(917071.115858237 6459973.71073099) (1 row)
ST_Buffer errechnet dabei um den angegebenen Punkt einen echten Kreis, nicht einfach nur ein Rechteck. Diese Fähigkeit kann man auch bei komplexeren Geometrien verwenden.
So können wir beispielsweise ein Straßenstück der Kirchgasse in der Datenbank hinterlegen:
CREATE TABLE streets (id SERIAL, name VARCHAR);
SELECT AddGeometryColumn ('public', 'streets', 'geom', 3857, 'LINESTRING', 2);
INSERT INTO streets (name, geom) VALUES
('Kirchgasse', ST_Transform(ST_SetSRID(ST_GeomFromText(
'LINESTRING(8.23723 50.07891, 8.23817 50.07898, 8.23915 50.07906, 8.24042 50.07910, 8.24157 50.07917)'
), 4326), 3857));
Nun können wir nicht nur Eigenschaften dieses Straßenstücks ermitteln – beispielsweise dessen Länge –, sondern auch seine Relation zu anderen geometrischen Objekten - beispielsweise allen Briefkästen, die nicht weiter als zehn Meter entlang der Kirchgasse liegen:
SELECT id, name, ST_Length(geom) AS length_in_meters FROM streets; id | name | length_in_meters ----+------------+------------------ 1 | Kirchgasse | 485.452649237208 SELECT id, description, ST_AsText(location) from postboxes where ST_Contains( ST_Buffer( (SELECT geom FROM streets WHERE name = 'Kirchgasse'), 10 ), location ); id | description | st_astext ----+-------------+------------------------------------------ 1 | Kirchgasse | POINT(917071.115858237 6459973.71073099)
Wie schon in den vorherigen Beispielen sind das alles Ad-hoc-Abfragen, die keinen Index nutzen. Entsprechende Indizes sind selbstverständlich verfügbar, lassen sich aber nur für einige der Operationen verwenden. Dementsprechend müssen sowohl Index als auch Abfragen korrekt gestaltet werden.
Schlussfolgerung
Die Verwendung dieser drei Funktionen in einer PostgreSQL-Datenbank, wie sie die Google CloudSQL zur Verfügung stellt, erlaubt das einfache Integrieren zusätzlicher Features in bestehende oder neue Anwendungen, ohne dafür neue Systeme konfigurieren, überwachen, verwalten und skalieren zu müssen.
Die Integration in eine (meist bereits bestehende) Datenbank vermeidet zusätzlichen Einarbeitungsaufwand bei den Entwicklern und Probleme durch die Notwendigkeit einer Synchronisierung der verschiedenen Systeme. Bei der Verwendung von Google CloudSQL sind auch Replikation und Hochverfügbarkeit sofort gegeben.
Es mag Fälle geben, in denen ein separates Volltext-Suchsystem wie Google Documents and Indexes oder eine Document-Store wie Google Cloud Datastore ihre Vorteile haben, jedoch werden meiner Meinung nach sehr viele Use-Cases bereits durch PostgreSQL abgebildet und die oben genannten Vorteile übersteigen die möglichen Einbußen beim Feature-Umfang bei weitem.
//SEIBERT/MEDIA empfiehlt die Verwendung von PostgreSQL für Google-Cloud-basierte Anwendungen und unterstützt Sie gerne bei der Planung und Umsetzung von Anwendungen in der Google AppEngine oder anderen Umgebungen. Melden Sie sich bei uns!
Weiterführende Infos
Java-Anwendungen in der Google App Engine mit CloudSQL
Serverless Apps mit Firebase Cloud Functions
Apps für die Google G Suite und der Google App Maker
Google Apps Script – Skript-Verwaltung mit dem App Scripts CLI (Clasp)
Mehr über die Creative-Commons-Lizenz erfahren
