© Pavel Timofeev, 123RF

Wie erwartet gibt es auch in diesem Herbst eine neue Release des Open-Source-Datenbanksystems PostgreSQL. Sie bietet eine ganze Reihe überzeugender neuer Features.

Das erste Highlight in PostgreSQL 9.5 ist die Erweiterung des »INSERT« -Befehls um die Klausel »ON CONFLICT DO UPDATE« , mit der sich bestimmen lässt, dass statt der »INSERT« -Anweisung ein »UPDATE« auszuführen ist, wenn die einzufügende Zeile schon existiert. Dieses Verhalten ist auch als so genannte Upsert-Funktionalität bekannt.

<C>UPSERT<C>

In der Praxis kommt dieser Fall sehr oft vor, der Anwender musste ihn aber bisher in PostgreSQL entweder durch eine Stored Procedure oder mit Hilfe einer Logik in der Clientanwendung von Hand selber implementieren. Die Schwierigkeit war, dass die Vorgaben im SQL-Standard und in anderen Datenbanksystemen erhebliche technische Probleme hervorriefen, die für die PostgreSQL-Entwickler nicht akzeptabel waren [1]. Die Logik eines »UPSERT« ist zwar sehr einfach, aber kompliziert wird es, wenn diese Logik im nebenläufigen Betrieb performant funktionieren muss, ohne Deadlocks oder sporadische Unique-Constraint-Fehler zu erzeugen, was bei anderen Implementierungen durchaus vorkommt.

Aus diesen Gründen hat sich das PostgreSQL-Projekt dazu entschlossen, extra eine eigene Syntax mit einer eigenen Semantik zu entwickeln. Hier ist ein Beispiel: Eine Tabelle speichert Abonnements für einen Dienst mit Benutzer-ID, Art des Abonnements sowie Start- und Ablaufdatum:

CREATE TABLE subscriptions (
  user_id int NOT NULL,
  subscription_type text NOT NULL,
  started date NOT NULL,
  expires date NOT NULL,
  PRIMARY KEY (user_id, subscription_type)
);

Eine »UPSERT« -Operation soll entweder einen neuen Eintrag einfügen oder einen bestehenden Eintrag aktualisieren. Bisher mussten Entwickler dazu eine Schleife zu Hilfe nehmen, die »INSERT« – oder »UPDATE« -Operationen so lange durchführte, bis eine fehlerfrei durchlief. Das gelang zum Beispiel mit einer PL/pgSQL-Funktion der Art, wie sie Listing 1 präsentiert. Die Schleife ist in diesem Beispiel notwendig, weil zwischen dem »UPDATE« und dem »INSERT« eine andere Verbindung eine dazu in Konflikt stehende Zeile einfügen könnte. Die Logik von Listing 1 verkürzt sich mit »UPSERT« auf einen einzigen neuen Befehl, wie ihn Listing 2 zeigt.

01 CREATE FUNCTION set_subscription(in_user_id int, in_subscription_type text, in_started date, in_expires date) RETURNS void
02 LANGUAGE plpgsql
03 STRICT
04 AS $$
05 BEGIN
06 <<upsert>>
07 LOOP
08     UPDATE subscriptions
09         SET (started, expires) = (in_started, in_expires)
10         WHERE (user_id, subscription_type) = (in_user_id, in_subscription_type);
11     EXIT upsert WHEN FOUND;
12     BEGIN
13         INSERT INTO subscriptions (user_id, subscription_type, started, expires)
14             VALUES (in_user_id, in_subscription_type, in_started, in_expires);
15         EXIT upsert;
16     EXCEPTION WHEN unique_violation THEN
17     END
18 END LOOP;
19 $$;

Der Befehl besteht zunächst aus einem normalen »INSERT« gefolgt von einer »ON CONFLICT« -Klausel, die den Konflikt und die auszuführende Aktion beschreibt. In diesem (typischen) Fall besteht der Konflikt in der Verletzung der Eindeutigkeit des Primärschlüssels, sobald versucht wird, denselben User mit demselben Abo noch einmal einzufügen. Die Aktion ist ein »UPDATE« -Befehl.

01 INSERT INTO subscriptions (user_id, subscription_type, started, expires)
02     VALUES (in_user_id, in_subscription_type, in_started, in_expires)
03     ON CONFLICT (user_id, subscription_id)
04         DO UPDATE SET (started, expires) = (in_started, in_expires);

Der neue Befehl spart, wie zu sehen ist, eine Menge Platz und ist noch dazu performanter, weil er die Problematik der Von-Hand-Implementierungen vermeidet, die darauf vorbereitet sein müssen, dass der Befehl wegen Aktivität in anderen Verbindungen fehlschlägt,

Wer vermeiden möchte, im »UPDATE« -Befehl die Eingabewerte aus der »VALUES« -Klausel zu wiederholen, kann auch das Alias »EXCLUDED« verwenden, um auf den Wert der Zeile zu verweisen, deren Einfügen gescheitert ist. Listing 3 zeigt ein Beispiel.

01 INSERT INTO subscriptions (user_id, subscription_type, started, expires)
02     VALUES (in_user_id, in_subscription_type, in_started, in_expires)
03     ON CONFLICT (user_id, subscription_id)
04         DO UPDATE SET (started, expires) = (EXCLUDED.started, EXCLUDED.expires);

Andererseits ist es auch möglich, im »UPDATE« -Teil ganz andere Werte zu schreiben als im »INSERT« -Teil, wenn der Anwender zum Beispiel irgendwie aufzeichnen möchte, dass es einen Konflikt gegeben hat.

Während »UPSERT« eher für Entwickler von Webanwendungen und anderen transaktionalen Anwendungen enorme Vereinfachungen bringt, gibt es in PostgreSQL 9.5 auch Neuerungen im Bereich Analytik.

In der Gruppe

PostgreSQL unterstützt jetzt die aus anderen SQL-Systemen bekannten Gruppieroptionen »CUBE« und »ROLLUP« . Diese erweitern die bekannte »GROUP BY« -Klausel und erlauben das Ausführen von mehreren Gruppiervarianten auf einmal. Beispieldaten und einige Anwendungen der Gruppierfunktion liefert Listing 4.

01 CREATE TABLE personal (
02     land text,
03     stadt text,
04     abteilung text,
05     name text
06 );
07
08 INSERT INTO personal VALUES
09     ('DE', 'Berlin', 'Vertrieb', 'Christian'),
10     ('DE', 'Berlin', 'Marketing', 'Sandra'),
11     ('DE', 'Frankfurt', 'Vertrieb', 'Stefan'),
12     ('AT', 'Innsbruck', 'Personal', 'Katrin'),
13     ('CH', 'Zürich', 'Vertrieb', 'Alexander');
14
15 SELECT land, count(*) FROM personal GROUP BY land;
16 SELECT land, stadt, count(*) FROM personal GROUP BY land, stadt;
17 SELECT abteilung, count(*) FROM personal GROUP BY abteilung;
18 SELECT land, abteilung, count(*) FROM personal GROUP BY land, abteilung;

Mit »CUBE« oder »ROLLUP« lassen sich derartige Anfragen kombinieren. Listing 5 zeigt ein Beispiel mit »ROLLUP« . Die Option gruppiert nach den angegebenen Spalten – wie das ein normales »GROUP BY« machen würde – sowie nach allen Präfixen der Spaltenliste, einschließlich der leeren Liste. Das ist besonders nützlich bei hierarchischen Strukturen. Zu sehen sind im Beispiel die Zahlen auf lokaler Abteilungsebene und auf den übergeordneten Organisationsebenen sowie die Gesamtzahl in einer einzigen Anfrage. Ohne »ROLLUP« müsste man das in separaten Anfragen ausführen (oder eventuell durch »UNION« zusammenkoppeln), was mühsam und langsamer wäre.

01 >SELECT land, stadt, abteilung, count(*) FROM personal GROUP BY ROLLUP (land, stadt, abteilung) ORDER BY 1, 2, 3;
02 -------------------+-----------+-------+
03 | land |   stadt   | abteilung | count |
04 -------------------+-----------+-------+
05 | AT   | Innsbruck | Personal  |     1 |
06 | AT   | Innsbruck |           |     1 |
07 | AT   |           |           |     1 |
08 | CH   | Zürich    | Vertrieb  |     1 |
09 | CH   | Zürich    |           |     1 |
10 | CH   |           |           |     1 |
11 | DE   | Berlin    | Marketing |     1 |
12 | DE   | Berlin    | Vertrieb  |     1 |
13 | DE   | Berlin    |           |     2 |
14 | DE   | Frankfurt | Vertrieb  |     1 |
15 | DE   | Frankfurt |           |     1 |
16 | DE   |           |           |     3 |

Die Option »CUBE« gruppiert nach allen möglichen Kombinationen der Gruppierspalten. Im Ergebnis zum passenden Beispiel in Listing 6 ist die weltweite Gesamtzahl in den verschiedenen Abteilungen zu sehen. Doch sind in diesem Beispiel nicht alle Zeilen des »CUBE« -Ergebnisses sinnvoll. Die Zeile »NULL, ‘Berlin’, NULL« etwa fasst alle Städte namens Berlin in allen Ländern zusammen. Das mag als Kuriosum interessant sein, aber in der Praxis wohl nicht.

01 >SELECT land, stadt, abteilung, count(*) FROM personal GROUP BY CUBE (land, stadt, abteilung) ORDER BY 1, 2, 3;
02 +------+-----------+-----------+-------+
03 | land |   stadt   | abteilung | count |
04 +------+-----------+-----------+-------+
05 | AT   | Innsbruck | Personal  |     1 |
06 | AT   | Innsbruck |           |     1 |
07 | AT   |           | Personal  |     1 |
08 | AT   |           |           |     1 |
09 | CH   | Zürich    | Vertrieb  |     1 |
10 | CH   | Zürich    |           |     1 |
11 | CH   |           | Vertrieb  |     1 |
12 | CH   |           |           |     1 |
13 | DE   | Berlin    | Marketing |     1 |
14 | DE   | Berlin    | Vertrieb  |     1 |
15 | DE   | Berlin    |           |     2 |
16 | DE   | Frankfurt | Vertrieb  |     1 |
17 | DE   | Frankfurt |           |     1 |
18 | DE   |           | Marketing |     1 |
19 | DE   |           | Vertrieb  |     2 |
20 | DE   |           |           |     3 |
21 |      | Berlin    | Marketing |     1 |
22 |      | Berlin    | Vertrieb  |     1 |
23 |      | Berlin    |           |     2 |
24 |      | Frankfurt | Vertrieb  |     1 |
25 |      | Frankfurt |           |     1 |
26 |      | Innsbruck | Personal  |     1 |
27 |      | Innsbruck |           |     1 |
28 |      | Zürich    | Vertrieb  |     1 |
29 |      | Zürich    |           |     1 |
30 |      |           | Marketing |     1 |
31 |      |           | Personal  |     1 |
32 |      |           | Vertrieb  |     3 |
33 |      |           |           |     5 |

Wer die Ausgabe noch genauer steuern möchte, kann die »GROUPING SETS« -Klausel verwenden, um die zu gruppierenden Kombinationen explizit anzugeben. »CUBE« und »ROLLUP« sind nämlich selbst einfach nur Kurzformen für bestimmte »GROUPING SETS« . Einen möglichen Einsatzfall dafür zeigt am Beispiel das Listing 7.

01 > SELECT land, stadt, abteilung, count(*) FROM personal GROUP BY GROUPING SETS((land, stadt, abteilung), (land, stadt), (land), (), (abteilung), (land, abteilung)) ORDER BY 1, 2, 3;
02 +------+-----------+-----------+-------+
03 | land |   stadt   | abteilung | count |
04 +------+-----------+-----------+-------+
05 | AT   | Innsbruck | Personal  |     1 |
06 | AT   | Innsbruck |           |     1 |
07 | AT   |           | Personal  |     1 |
08 | AT   |           |           |     1 |
09 | CH   | Zürich    | Vertrieb  |     1 |
10 | CH   | Zürich    |           |     1 |
11 | CH   |           | Vertrieb  |     1 |
12 | CH   |           |           |     1 |
13 | DE   | Berlin    | Marketing |     1 |
14 | DE   | Berlin    | Vertrieb  |     1 |
15 | DE   | Berlin    |           |     2 |
16 | DE   | Frankfurt | Vertrieb  |     1 |
17 | DE   | Frankfurt |           |     1 |
18 | DE   |           | Marketing |     1 |
19 | DE   |           | Vertrieb  |     2 |
20 | DE   |           |           |     3 |
21 |      |           | Marketing |     1 |
22 |      |           | Personal  |     1 |
23 |      |           | Vertrieb  |     3 |
24 |      |           |           |     5 |

Ein bisschen mehr Partitionierung

Ein weiterer wunder Punkt von PostgreSQL ist die Partitionierung. Hierfür gibt es keine eingebaute Lösung – und das ändert sich auch in PostgreSQL 9.5 nicht. Der typische Ausweg besteht darin, die Partitionierung von Hand nachzubauen. Dafür bietet sich eine Vererbungshierarchie an, ein Beispiel skizziert Listing 8.

01 > CREATE TABLE logins (tx timestamp, ip_adress, ...);
02
03 > CREATE TABLE logins_201509 (CHECK (tx >= '2015-09-01' AND tx < '2015-10-01')) INHERITS (logins);
04 > CREATE TABLE logins_201510 (CHECK (tx >= '2015-10-01' AND tx < '2015-11-01')) INHERITS (logins);

Mit einer Kombination aus Triggern und Constraints lässt es sich so einrichten, dass die Daten in den verschiedenen physischen Tabellen landen, der Anwender aber beim Lesen lediglich die zugrundeliegende Elterntabelle erwähnen muss. Die Datenbank greift automatisch nur auf die passenden Kindtabellen zu. Einige Erweiterungsmodule für PostgreSQL können helfen diese Strukturen mit weniger manuellem Aufwand aufzubauen und dann zu verwalten, ein Beispiel wäre »pg_partman« .

Eine Neuerung in PostgreSQL 9.5 besteht nun darin, dass die untergeordneten Kindtabellen jetzt auch Fremdtabellen sein können, also auf anderen Rechnern liegen dürfen. Fremdtabellen (Foreign Tables) sind Tabellen, auf die die Datenbank über Plugins, so genannte Foreign Data Wrappers (FDW), zugreift. Diese “fremden Daten” können Daten aus anderen Datenbanksystemen, aus Dateien und sogar aus Internetdiensten sein. Auch eine andere PostgreSQL-Datenbank kommt in Frage.

Dazu gehört auch, dass Fremdtabellen jetzt »CHECK« -Constraints haben können. Die Constraints beachtet der Planer in der Datenbank, um bei partitionierten Tabellen diejenigen Partitionen beim Lesen auszulassen, deren »CHECK« -Constraints die Anfragekriterien ausschließen. Die Kombination dieser beiden Neuerungen ermöglicht es also jetzt, dass mit den gleichen Verfahren, die bisher bereits für die Partitionierung eingesetzt wurden, auch eine Partitionierung über Rechnergrenzen hinaus möglich ist.

Stichproben

Ein Schwerpunkt der Arbeit an PostgreSQL 9.5 war es, besser mit sehr großen Tabellen umgehen zu können. Ein Beitrag dazu ist das »TABLESAMPLE« -Feature, bekannt aus dem SQL-Standard und einigen kommerziellen Datenbanken. Es erlaubt, aus einer Tabelle nur eine zufällige Auswahl der Zeilen zu lesen. So lassen sich Anfragen mit sehr großen Tabellen erheblich beschleunigen, falls annähernde Ergebnisse ausreichen.

Die folgende Anfrage zum Beispiel berechnet den ungefähren Durchschnittspreis aller Artikel im System:

SELECT avg(preis) FROM artikel TABLESAMPLE  SYSTEM(1);

Das Statement liest nur ein Prozent der Zeilen in der Tabelle »artikel« und verwendet das Stichprobenverfahren »SYSTEM« . Dieses Verfahren liest zufällig ausgewählte Blöcke der Tabelle und lässt die anderen aus. Alternativ lässt sich das Verfahren »BERNOULLI« verwenden. Das ist langsamer, weil es die gesamte Tabelle lesen muss, die Zufallsauswahl ist aber besser. Wie bei PostgreSQL üblich, lassen sich durch Erweiterungen auch eigene Stichprobenverfahren definieren.

Der Blockrange-Index BRIN

Ebenfalls für sehr große Tabellen gedacht ist eine neue Indexmethode namens BRIN (Blockrange-Index). Ein BRIN speichert für jeden Blockbereich die Minimal- und Maximalwerte. Beim Abfragen mit einer Bereichsanfrage (zum Beispiel »x < 10« ) muss er dann nur jene Blöcke betrachten, deren Grenzwerte den gesuchten Bereich einschließen. Dies ist zwar nicht so effizient wie der bekannte B-Baum, der direkt auf die passenden Zeilen zeigt, während ein BRIN nur auf einen Blockbereich zeigt. Dafür ist aber ein BRIN erheblich kleiner als ein herkömmlicher B-Baum-Index.

Ein Beispiel: Eine Tabelle mit einer Int4-Spalte gefüllt mit den Zahlen 1 bis 100 Million ist ungefähr 3,5 GByte groß. Der B-Baum-Index dazu würde rund 2,1 GByte brauchen, ein BRIN dagegen nur 100 KByte. Der Vorteil von BRIN kommt besonders bei Tabellen zum Tragen, die wesentlich größer als der zur Verfügung stehende RAM sind und bei denen auch schon die B-Baum-Indizes zu groß für den Hauptspeicher wären.

Am besten geeignet ist ein BRIN für Daten, die natürlich in aufsteigender Reihenfolge gespeichert sind, zum Beispiel Zeitstempel in einem kontinuierlich ablaufenden Prozess oder automatisch generierte Identifikationsnummern. Dann kann der BRIN für Anfragen wie »zeit > ‘yesterday’« große Teile der Tabelle einfach ausschließen, da nur wenige zusammenhängende Blöcke die passenden Daten enthalten. Bei zufällig verteilten Werten ist BRIN dagegen nutzlos, da dann fast alle Blockbereiche passende Werte enthalten und ein Indexscan einem sequenziellen Scan nahekommt.

Sicherheit auf Zeilenebene

Das Zugriffsrechtesystem in SQL bestimmt nur die Zugriffsrechte auf der Ebene von Tabellen oder von Spalten, denn das sind die von der Data Definition Language (DDL) dafür vorgesehenen Einheiten. Für einige Anwendungen ist aber auch die Kontrolle von Zugriffen auf einzelne Datenzeilen interessant. Das lässt sich, wie bei PostgreSQL so oft, von Hand arrangieren, zum Beispiel mit speziellen Sichten und Triggern.

Aber in PostgreSQL 9.5 gibt es ab sofort nun auch eine einfache und zudem eingebaute Lösung. Listing 9 demonstriert einen typischen Anwendungsfall: Jede Zeile speichert den Benutzer ab, der die Zeile erzeugt hat. Das Lesen der Tabelle zeigt dann nur jene Zeilen an, die zu dem jeweiligen Benutzer gehören. Damit kann jeder Mitarbeiter diejenigen Daten verwalten, für die er verantwortlich ist, muss aber nicht unnötig viel Zugriff auf andere Daten bekommen.

01 > CREATE TABLE bestellungen (
02     id int,
03     product text,
04     anzahl int,
05     mitarbeiter text
06 );
07
08 > ALTER TABLE bestellungen ENABLE ROW LEVEL SECURITY;
09
10 > CREATE POLICY p1 ON bestellungen FOR ALL TO PUBLIC
11     USING (mitarbeiter = current_user);
12
13 > GRANT ALL ON TABLE bestellungen TO PUBLIC;
14
15 > SET SESSION AUTHORIZATION m1;
16 > INSERT INTO bestellungen VALUES (1, 'Schraube', 10, 'm1');
17 > INSERT INTO bestellungen VALUES (2, 'Nagel', 5, 'm2');  -- Fehler: nicht erlaubt
18
19 > SET SESSION AUTHORIZATION m2;
20 > INSERT INTO bestellungen VALUES (3, 'Hammer', 1, current_user);
21
22 > SET SESSION AUTHORIZATION m1;
23 > SELECT * FROM bestellungen;
24
25 +----+----------+--------+-------------+
26 | id | product  | anzahl | mitarbeiter |
27 +----+----------+--------+---------- --+
28 |  1 | Schraube |     10 | m1          |
29 +----+----------+--------+-------------+
30
31 > SET SESSION AUTHORIZATION postgres;
32 > SELECT * FROM bestellungen;
33
34 +----+----------+--------+-------------+
35 | id | product  | anzahl | mitarbeiter |
36 +----+----------+--------+-------------+
37 |  1 | Schraube |     10 | m1          |
38 |  3 | Hammer   |      1 | m2          |
39 +----+----------+--------+-------------+

Zu beachten ist, dass Daten, auf die kein Zugriff besteht, quasi unsichtbar sind. Damit unterscheidet sich Row-Level-Security vom herkömmlichen Privilegiensystem, das bei fehlenden Zugriffsrechten einen Fehler erzeugt. Mit diesem System lässt sich zum Beispiel auch ein Multi-Tenancy-System einfach implementieren. Damit kann eine Anwendung mehreren separaten Kunden zur Verfügung stehen, die intern dieselben Tabellen verwenden, ohne dass die Gefahr besteht, dass ein Kunde andere als die eigenen Kundendaten sieht. Bisher musste das der Anwender entweder durch getrennte Tabellen bewerkstelligen, was ein Problem bei vielen Tausenden Tabellen ergeben kann, oder aber mühsam und fehleranfällig durch eigene Trigger und Sichten implementieren.

Für den Admin:<C>pg_rewind<C>

Auch für Datenbank-Administratoren gibt es etwas Neues, und zwar im Bereich Replikation. Angenommen ein Master-Server ist ausgefallen und es erfolgt ein Failover auf einen Slave. Später wird der Master-Server wiederhergestellt. Es war bisher meist unmöglich, den vorherigen Master an den neuen Master als Slave anzufügen, da die WAL-Zeitleisten verzweigt waren. Das hätte nur funktioniert, wenn der Master sauber vor dem Slave heruntergefahren wäre, was bei einem Crash ja nicht der Fall ist.

Die einzige Möglichkeit war, die Daten auf dem alten Master zu löschen und den Slave komplett neu zu bauen, was sehr zeitaufwändig sein kann. Während dieser Zeit hatte das Datenbanksystem dann keinen Slave, wenn kein dritter Slave vorhanden war. Ein neues Programm »pg_rewind« erlaubt nun, den alten Master in einen funktionsfähigen Slave umzuwandeln, zum Beispiel:

pg_rewind -D /usr/local/pgsql/data --source-server='host=db2 dbname=postgres'

Das Programm führt der vorherige Master aus und bearbeitet das angegebene Datenverzeichnis, sodass es an den angegebenen, eventuell auf einem anderen Rechner laufenden, neuen Master angefügt werden kann. Danach lässt sich der vorherige Master – mit einer »recovery.conf« Datei versehen – wieder starten.

Tippfehler

Zum Schluss noch eine Neuerung vor allem für Freunde der Kommandozeile. Wie es zum Beispiel von Git bekannt ist, kann PostgreSQL nun auch Tippfehler erkennen und Korrekturvorschläge machen:

test=> select statt, land from personal; ERROR: column "statt" does not exist LINE 1: select statt, land from personal; HINT: Perhaps you meant to reference the column "personal"."stadt".

Das funktioniert zurzeit allerdings nur für Spaltennamen.

Fazit

PostgreSQL ist ein Gemeinschaftsprojekt ohne zentrale kommerzielle Führung. Die Features jeder Version entsprechen keinem Release-Plan und keiner Roadmap. Sie kommen von Entwicklern aus verschiedenen Firmen und mit verschiedenen Plänen. Trotzdem lassen sich Trends erkennen. Ein beherrschendes Thema von PostgreSQL 9.5 war die Unterstützung großer Datenbanken mit Analytik-Anwendungen. Das ist auch für die nächsten Versionen zu erwarten.

Dazu kommt das »UPSERT« -Feature, das eine Funktionslücke für typische Webanwendungen schließt. Zudem enthält PostgreSQL 9.5 weit über 100 weitere Änderungen, darunter Dutzende Performance-Verbesserungen. Das Upgrade lohnt sich also auch für alle, die die neuen Funktionen noch nicht benötigen.