© Elena Schweitzer, 123RF

Mit Version 10.0 wird PostgreSQL nicht nur eine runde Versionsnummer erhalten, sondern auch neue Features, die vor allem – aber nicht nur – auf Skalierung und Verfügbarkeit abzielen.

Mehr als 30 Jahre sind seit dem Start des Postgres-Projekts vergangen. Nie schritt die Entwicklung schneller voran als jetzt. Grund genug, einige der neuen Features von PostgreSQL 10.0 zu inspizieren.

Mehr Skalierung

Seit Version 9.6 unterstützt PostgreSQL so genannte Parallel Queries, die es ermöglichen, eine einzelne Abfrage auf mehr als einem CPU-Core laufen zu lassen. Im ersten Schritt haben die Entwickler damals Sequential Scans (bei Oracle heißen sie Full Table Scans), Aggregates & Co. parallelisiert. Für PostgreSQL 10 legten sie noch einmal nach und machten viele weitere Codeteile des PostgreSQL-Cores für Parallel Queries fit. Die wesentlichsten Verbesserungen betreffen:

• Parallele Index- und parallele Index-only-Scans. Die neue Infrastruktur kann für alle Indextypen parallele Abfragen starten. Entsprechende Anpassungen für den Optimizer sind im Core. Derzeit sind diese Erweiterungen aber nur für normale B-Trees implementiert.
• Unterstützung für parallele Bitmap-Heap-Scans. Diese Erweiterung erlaubt es, mehrere Worker-Prozesse für verschiedene Teile des Heap zu betreiben. Abfragen, die mehrere Indizes für dieselbe Tabelle auf einmal benutzen, können davon massiv profitieren.
• Parallele Merge Joins. In PostgreSQL 9.6 war es nur möglich, Hash Joins und Nested Loop Joins mit parallelen Queries zu verwenden. PostgreSQL 10.0 parallelisiert nun auch Merge Joins.
• Nicht-korrelierte Subselects sind jetzt ebenfalls parallelisierbar.

Natürlich bleibt im Bereich der parallelen Abfragen noch viel zu tun. Das noch in diesem Jahr erscheinende PostgreSQL 10.0 lässt aber ohne Frage bereits große Fortschritte und Verbesserungen erkennen. In Zukunft sind in diesem Bereich aber noch zahlreiche weitere Verbesserungen zu erwarten.

Table Partitioning

Die Möglichkeit, eine Tabelle einfach zu partitionieren, ist seit vielen Jahren ein Wunsch vieler PostgreSQL-Nutzer. Prinzipiell ist es zwar möglich, bis zu 128 TByte an Daten (bei maximaler Blocksize) in eine einzelne Tabelle zu packen – das Handling solcher Riesentabellen ist aber nicht mehr praktisch. Das ist besonders bei Zeitreihen ein Thema, die beständig wachsen, indem neue Daten hinzukommen.

Mit der Einführung von PostgreSQL 10.0 haben die Entwickler für die Partitionierung eine entsprechende Syntax geschaffen. Listing 1 zeigt ein kleines Beispiel, das eine Tabelle nach Range partitioniert. Die erste Partition soll nur negative Zahlen speichern, die zweite Partition nur positive (Abbildung 1). Die Anzahl der Partitionen ist de facto beliebig.

01 CREATE TABLE data (
02         payload integer
03 )  PARTITION BY RANGE (payload);
04
05 CREATE TABLE negatives PARTITION
06         OF data FOR VALUES FROM (UNBOUNDED) TO (0);
07
08 CREATE TABLE positives PARTITION
09         OF data FOR VALUES FROM (0) TO (UNBOUNDED);

Abbildung 1: Eine Tabelle wird in zwei Partitionen aufgeteilt.

Spannend zu sehen ist, wie PostgreSQL eine Abfrage bearbeitet, die eine partitionierte Tabelle verwendet: Das System bildet die Vereinigungsmenge der einzelnen Partitionen. Wichtig ist, dass PostgreSQL beim Planen der Query versucht, so viele Partitionen wie möglich zu entfernen. Durch das Partitionierungskriterium ist klar, welche Daten wo zu finden sind – daher ist es auch möglich, aus der »WHERE«-Bedingung abzuleiten, in welchen Tabellen die relevanten Daten nicht sein können (Constraint Exclusion). Listing 2 zeigt ein Beispiel.

01 test=# explain SELECT *
02         FROM    data
03         WHERE   payload < -10;
04  QUERY PLAN
05 ----------------------------------------------------
06  Append  (cost=0.00..41.88 rows=850 width=4)
07    ->  Seq Scan on negatives  (cost=0.00..41.88 rows=850 width=4)
08          Filter: (payload < '-10'::integer)
09 (3 rows)

Interessant ist, dass jede Partition getrennt indiziert sein muss. Ein Index auf die Haupttabelle ist nicht möglich:

test=# CREATE INDEX idx_payload ON data (payload);
ERROR:  cannot create index on partitioned table "data"

Das wirkt auf den ersten Blick etwas umständlich, ist aber sinnvoll. Oft ändern sich die Anforderungen an die Indizierung oder sind je nach Datenbereich unterschiedlich. Durch eine manuelle Indizierung wie etwa

test=# CREATE INDEX idx_negatives ON negatives (payload);
test=# CREATE INDEX idx_positives ON positives (payload);

kann man wesentlich gezielter Indexe setzen. Oft lässt sich so viel Platz sparen.

Verbesserte Statistiken

Eine weitere wesentliche Verbesserung in PostgreSQL 10.0 ist die Einführung von »CREATE STATISTICS«. Um eine Query zu optimieren, verwendet PostgreSQL einen so genannten kostenbasierten Optimizer. Was bedeutet das? Gegeben sei eine einfache Query:

SELECT  *
FROM    person
WHERE   sprache = 'chinesisch'
       AND land = 'China';

PostgreSQL nimmt die Query entgegen und versucht einen Ausführungsplan zu erzeugen. Dazu schätzt der Optimizer die Selektivität jeder Spalte. Angenommen, dass 15 Prozent der Weltbevölkerung Chinesisch sprechen und 15 Prozent der Weltbevölkerung in China leben. Die Sprache und der Wohnort sind damit statistisch nicht unabhängig, weil die meisten Chinesen eben einfach in China leben. Jetzt ist es so, dass PostgreSQL statistische Informationen für jede einzelne Spalte speichert. PostgreSQL weiß also, wie viele Chinesisch sprechende Menschen es gibt und wie viele in China leben. Der Optimizer wird daher die Wahrscheinlichkeiten multiplizieren: 15% * 15% = 2.25%.

Das ist natürlich eine Unterschätzung, die den Optimizer dazu verleiten kann, suboptimale Entscheidungen zu treffen. Mit der Einführung von PostgreSQL 10.0 wird es möglich, explizit zusätzliche multi-variante Statistiken zu erzeugen, die dem Optimizer helfen, auch funktionale Abhängigkeiten zu erkennen und entsprechend gut zu schätzen.

Was bedeutet das in der Praxis? Das Beispiel in Listing 3 verdeutlicht das Problem. Es legt eine Tabelle an und füllt sie mit einer Million Zeilen. Die Inhalte der ersten und der zweiten Spalte korrelieren in diesem Fall, weil sie aus derselben Zahl abgeleitet werden. Da PostgreSQL im Standardfall nur die Verteilung einer einzelnen Spalte kennt, ergibt sich eine Fehlschätzung (Listing 4).

01 test=# explain analyze SELECT * FROM t1 WHERE (a = 1) AND (b = 0);
02
03 QUERY PLAN
04 -----------------------------------------------------
05  Gather  (cost=1000.00..11675.10 rows=1 width=8)
06         [...]
07  Execution time: 38.893 ms

01 test=# CREATE TABLE t1 (
02     a   int,
03     b   int
04 );
05
06 INSERT INTO t1
07         SELECT  i/100, i/500
08         FROM    generate_series(1, 1000000) s(i);
09
10 ANALYZE t1;

PostgreSQL schätzt das Ergebnis auf nur eine Zeile, in der Realität sind aber 100 Zeilen zu erwarten. Das ist eine klassische Unterschätzung. »CREATE STATISTICS« löst das Problem. Der Befehl (siehe Kasten “Korrektur”) sagt dem System, dass es eine Korrelation zwischen »a« und »b« gibt. Wie zu sehen ist, entspricht nun die Schätzung (98 Zeilen) ungefähr der Realität (100 Zeilen).

Mit <C>CREATE STATISTICS<C> kann der Anwender dem Oprimizer auf sie Speünge helfen:
CREATE STATISTICS s1 (dependencies) ON a, b FROM t1;
ANALYZE t1;

QUERY PLAN
Gather  (cost=1000.00..11684.80 rows=98 width=8)
        (actual time=0.137..37.063 rows=100 loops=1)
[...]
Execution time: 39.017 ms

Nicht nur im Optimizer führt PostgreSQL 10.0 neue Funktionalität ein. Auch im Replikationsbereich gibt es wieder neue Features, die es dem Benutzer erleichtern sollen, Daten ganz einfach und schnell im Cluster zu verteilen. Zu diesem Zweck hat man zwei neue Befehle eingeführt: »CREATE PUBLICATION« und »CREATE SUBSCRIPTION«.

Daten verteilen

Oft ist es so, dass der Anwender nur einzelne Tabellen verteilen möchte. Die neue Funktionalität ermöglicht genau dies. Man stelle sich etwa einen einfachen Onlineshop vor: Im Backoffice erfasst der Verkäufer seine Produkte und Preise und so weiter. Diese Daten sollen in den Shop repliziert werden. Im Shop selbst kommen Bestellungen an, die wieder entsprechend ins Backoffice zu replizieren sind. In diesem Beispiel würde das Backoffice Produkte, Preise und dergleichen publizieren und der Shop würde sie abonnieren (Subscribe). Bei Bestellungen wäre es entsprechend umgekehrt.

Eine Publication hat einen eindeutigen Namen und umfasst ein Set von Tabellen, für das sie per Default alle Datenänderungen publiziert. Auch kann der Programmierer noch zusätzliche Parameter angeben. Oft will er beispielsweise nur »INSERT« oder nur »UPDATE« replizieren, weil aus irgendwelchen Gründen keine Daten gelöscht werden sollen. Auch das ist sehr leicht möglich, Beispiel:

test=# CREATE PUBLICATION some_pub
        FOR ALL TABLES WITH (publish = 'insert, update');
CREATE PUBLICATION

Wenn Daten einmal publiziert sind, können sie direkt von einem anderen Server abonniert werden:

test=# CREATE SUBSCRIPTION subscription_name
    CONNECTION 'conninfo'
    PUBLICATION publication_name [, ...] [ WITH ( subscription_parameter [= value] [, ... ] ) ]

Dazu legt der Benutzer eine Subskription an, die sich zu einer Publikation auf einem anderen Server verbindet. Mit der »WITH«-Clause kann er wieder zusätzliche Optionen angeben.

Besonders interessant ist in diesem Zusammenhang »copy_data«. Wenn diese Option eingeschaltet ist, kopiert PostgreSQL die ganze Tabelle vom Publisher und beginnt die Änderungen, die dort passieren, der Reihe nach einzuspielen. Mit der neuen Methode ist es ganz einfach, Tabellen kreuz und quer im System zu verteilen.

Im Replikationsumfeld gibt es noch weitere Verbesserungen und Erweiterungen. Eine dieser Erweiterung firmiert unter dem Namen Quorum Commits. Der Anwender kann jetzt viel flexibler angeben, welche Server eine Transaktion bestätigen müssen, bevor sie als abgeschlossen gilt. PostgreSQL unterstützt jetzt »ANY« und »FIRST«, um anzuzeigen, wer ein »COMMIT« bestätigen muss, bevor der Benutzer eine Freigabe bekommt.

Foreign Data Wrappers

Um auf entfernte Datenquellen zuzugreifen, unterstützt PostgreSQL seit vielen Jahren den so genannten SQL/MED-Standard (Management of External Data). SQL/MED ist eine genormte Schnittstelle, um externe Daten als Tabellen darzustellen und in SQL nutzbar zu machen. PostgreSQL 10.0 kennt so genanntes Aggregate Pushdown. Die Idee ist, dass eine Abfrage so wenige Daten wie nur irgendwie möglich von der entfernten Schnittstelle holen muss. Zur Demonstration soll ein kleines Beispiel dienen:

remote=# CREATE TABLE t_data (id int);
CREATE TABLE
remote=# INSERT INTO t_data
                SELECT  *
                FROM    generate_series(1, 10000);INSERT 0 10000

Hier werden in einer Datenbank, die »remote« heißen soll, 10000 Zeilen in eine einfache Tabelle eingefügt. Ziel ist es, diese Tabelle in einer anderen Datenbank sichtbar zu machen. Dazu lädt man in der anderen Datenbank (im Beispiel heißt sie »test«) den Foreign Data Wrapper:

test=# CREATE EXTENSION postgres_fdw;

Dann lässt sich bereits ein Verweis auf den entfernten Server anlegen. Die Datenbankverbindung wird in den Optionen hinterlegt:

test=# CREATE SERVER pgserver
        FOREIGN DATA WRAPPER postgres_fdw
        OPTIONS (host 'localhost', dbname 'remote');

Schließlich ist noch ein User-Mapping nötig, damit der Server weiß, mit welchem Benutzer-Account er sich auf der Gegenseite einzuloggen hat.

Um das Beispiel einfach zu halten, soll der Server auf der Gegenseite immer als User »postgres« agieren:

test=# CREATE USER MAPPING FOR public
        SERVER pgserver
        OPTIONS (user 'postgres',         password 'some_password');

Schließlich kann man die Struktur der gewünschten Tabelle(n) importieren:

test=# IMPORT FOREIGN SCHEMA public
        LIMIT TO (t_data)
        FROM SERVER pgserver
        INTO public;

Die Tabelle »t_data« der lokalen Datenbank verweist jetzt auf die Tabelle »t_data« in der anderen Datenbank und lässt sich ganz normal wie eine lokale Tabelle verwenden. Alles bisher Gezeigte ist auch in PostgreSQL 9.6 möglich. Aber spannend wird es, wenn der Benutzer eine Aggregatsfunktion schreibt:

test=# explain verbose SELECT count(*)
        FROM    t_data
        WHERE   id < 10
        GROUP BY 1000 < id;

Die Abfrage ist recht einfach: Sie liefert alle Daten aus der Tabelle, deren ID kleiner als 10 ist, und teilt die Ergebnismenge in zwei Gruppen: eine Gruppe < 1000 und eine Gruppe >= 1000. Für jede Gruppe soll die Anzahl der Einträge gezählt werden.

In älteren Versionen war es PostgreSQL möglich, die Filter (also die »WHERE«-Bedingung) auf die Gegenseite zu schieben. Der anschließende »count« war jedoch eine lokale Sache. Mit der neuen Version wird es möglich, dass PostgreSQL auch Aggregatsfunktionen auf entfernten Servern ausführt.

Warum ist das so wichtig? Es eröffnet in Zukunft die Möglichkeit, komplexe Queries komplett in einem Cluster aus vielen Maschinen zu verteilen. In Kombination mit parallelen Abfragen werden sich auf diese Weise einige gute Skalierungsmöglichkeiten auftun, um noch mehr Durchsatz zu erreichen.

Neben den skizzierten Features haben noch viele andere Erweiterungen, Ergänzungen und Verbesserungen Einzug in den Core gefunden. Viele andere stehen noch an, wenn es nach der runden Version weitergeht.