PostgreSQL^® 12 veröffentlicht

Die PostgreSQL^® Global Development Group (PGDG) hat Version 12 der populären, freien Datenbank PostgreSQL^® freigegeben. Wie unser Artikel für Beta 4 bereits angedeutet hat, sind eine Vielzahl von neuen Features, Verbesserungen und Optimierungen in das Release eingeflossen. Dazu gehören unter anderem:

Optimierte Speicherplatznutzung und Geschwindigkeit bei btree-Indexen

btree-Indexe, der Standardindextyp in PostgreSQL^®, hat einige Optimierungen in PostgreSQL^® 12 erfahren.

btree Indexe speicherten in der Vergangenheit Doubletten (also mehrfache Einträge mit denselben Schlüsselwerten) in einer unsortierten Reihenfolge. Dies hatte eine suboptimale Nutzung der physischen Repräsentation in betreffenden Indexen zu Folge. Eine Optimierung speichert diese mehrfachen Schlüsselwerte nun in derselben Reihenfolge, wie diese auch physisch in der Tabelle gespeichert sind. Dies verbessert die Speicherplatzausnutzung und die Aufwände für das Verwalten entsprechender Indexe vom Typ btree. Darüber hinaus verwenden Indexe mit mehreren indizierten Spalten eine verbesserte physische Repräsentation, sodass deren Speicherplatznutzung ebenfalls verbessert wird. Um hiervon in PostgreSQL^® 12 zu profitieren, müssen diese jedoch, falls per binärem Upgrade mittels pg_upgrade auf die neue Version gewechselt wurde, diese Indexe neu angelegt bzw. reindiziert werden.

Einfügeoperationen in btree-Indexe werden zudem durch verbessertes Locking beschleunigt.

Verbesserungen für pg_checksums

credativ hat eine Erweiterung für pg_checksums beigesteuert, die es ermöglicht Blockprüfsummen in gestoppten PostgreSQL^®-Instanzen zu aktivieren oder zu deaktivieren. Vorher konnte dies nur durch ein Neuanlegen der physischen Datenrepräsentation des Clusters per initdb durchgeführt werden.
pg_checksums verfügt nun auch mit dem Parameter --progress über die Möglichkeit, einen Statusverlauf auf der Konsole anzuzeigen. Die entsprechenden Codebeiträge stammen von den Kollegen Michael Banck und Bernd Helmle.

Optimizer Inlining von Common Table Expressions

Bis einschließlich PostgreSQL^® 11 war es dem PostgreSQL^® Optimizer nicht möglich, Common Table Expressions (auch CTE oder WITH Abfragen genannt) zu optimieren. Wurde ein solcher Ausdruck in einer Abfrage verwendet, so wurde die CTE immer als erstes evaluiert und materialisiert, bevor der Rest der Abfrage abgearbeitet wurde. Dies führte bei komplexeren CTE Ausdrücken zu entsprechend teuren Ausführungsplänen. Folgendes generisches Beispiel illustriert dies anschaulich. Gegeben sei ein Join mit einem CTE Ausdruck, der alle gerade Zahlen aus einer numerischen Spalten filtert:

WITH t_cte AS (SELECT id FROM foo WHERE id % 2 = 0) SELECT COUNT(*) FROM t_cte JOIN bar USING(id);

In PostgreSQL^® 11 führt die Verwendung einer CTE immer zu einem CTE Scan, der den CTE Ausdruck als erstes materialisiert:

EXPLAIN (ANALYZE, BUFFERS) WITH t_cte AS (SELECT id FROM foo WHERE id % 2 = 0) SELECT COUNT(*) FROM t_cte JOIN bar USING(id) ;
                                                       QUERY PLAN                                                        
─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 Aggregate  (cost=2231.12..2231.14 rows=1 width=8) (actual time=48.684..48.684 rows=1 loops=1)
   Buffers: shared hit=488
   CTE t_cte
     ->  Seq Scan on foo  (cost=0.00..1943.00 rows=500 width=4) (actual time=0.055..17.146 rows=50000 loops=1)
           Filter: ((id % 2) = 0)
           Rows Removed by Filter: 50000
           Buffers: shared hit=443
   ->  Hash Join  (cost=270.00..286.88 rows=500 width=0) (actual time=7.297..47.966 rows=5000 loops=1)
         Hash Cond: (t_cte.id = bar.id)
         Buffers: shared hit=488
         ->  CTE Scan on t_cte  (cost=0.00..10.00 rows=500 width=4) (actual time=0.063..31.158 rows=50000 loops=1)
               Buffers: shared hit=443
         ->  Hash  (cost=145.00..145.00 rows=10000 width=4) (actual time=7.191..7.192 rows=10000 loops=1)
               Buckets: 16384  Batches: 1  Memory Usage: 480kB
               Buffers: shared hit=45
               ->  Seq Scan on bar  (cost=0.00..145.00 rows=10000 width=4) (actual time=0.029..3.031 rows=10000 loops=1)
                     Buffers: shared hit=45
 Planning Time: 0.832 ms
 Execution Time: 50.562 ms
(19 rows)

Dieser Plan materialisiert zunächst die CTE mit einem Sequential Scan mit entsprechendem Filter (id % 2 = 0). Hier wird kein funktionaler Index verwendet, daher ist dieser Scan entsprechend teurer. Danach wird das Ergebnis der CTE per Hash Join mit der entsprechenden Join Bedingung mit der Tabelle bar verknüpft. Mit PostgreSQL^® 12 erhält der Optimizer nun die Fähigkeit, diese CTE Ausdrücke ohne vorherige Materialisierung zu inlinen. Der zugrundeliegende, optimierte Plan in PostgreSQL^® 12 sieht dann wie folgt aus:

EXPLAIN (ANALYZE, BUFFERS) WITH t_cte AS (SELECT id FROM foo WHERE id % 2 = 0) SELECT COUNT(*) FROM t_cte JOIN bar USING(id) ;
                                                                QUERY PLAN                                                                 
───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 Aggregate  (cost=706.43..706.44 rows=1 width=8) (actual time=9.203..9.203 rows=1 loops=1)
   Buffers: shared hit=148
   ->  Merge Join  (cost=0.71..706.30 rows=50 width=0) (actual time=0.099..8.771 rows=5000 loops=1)
         Merge Cond: (foo.id = bar.id)
         Buffers: shared hit=148
         ->  Index Only Scan using foo_id_idx on foo  (cost=0.29..3550.29 rows=500 width=4) (actual time=0.053..3.490 rows=5001 loops=1)
               Filter: ((id % 2) = 0)
               Rows Removed by Filter: 5001
               Heap Fetches: 10002
               Buffers: shared hit=74
         ->  Index Only Scan using bar_id_idx on bar  (cost=0.29..318.29 rows=10000 width=4) (actual time=0.038..3.186 rows=10000 loops=1)
               Heap Fetches: 10000
               Buffers: shared hit=74
 Planning Time: 0.646 ms
 Execution Time: 9.268 ms
(15 rows)

Der Vorteil dieser Methode ist, dass das initiale materialisieren des CTE Ausdrucks entfällt. Stattdessen wird die Abfrage direkt mit einem Join ausgeführt.
Dies funktioniert bei allen nicht-rekursiven CTE Ausdrücken ohne Seiteneffekte (beispielsweise CTEs mit Schreibanweisungen) und solchen, die jeweils nur einmal pro Abfrage referenziert sind. Das alte Verhalten des Optimizers kann mit der Anweisung WITH ... AS MATERIALIZED ... forciert werden.

Generated Columns

Neu in PostgreSQL^® 12 sind sogenannte Generated Columns, die auf Ausdrücken basierend ein Ergebnis anhand vorhandener Spaltenwerte berechnen. Diese werden mit den entsprechenden Quellwerten im Tupel gespeichert. Der Vorteil ist, dass das Anlegen von Triggern zur nachträglichen Berechnung von Spaltenwerten entfallen kann. Folgendes einfaches Beispiel anhand einer Preistabelle mit Netto und Bruttopreisen illustriert die neue Funktionalität:

CREATE TABLE preise(netto numeric,
                    brutto numeric GENERATED ALWAYS AS (netto * 1.19) STORED);

INSERT INTO preise VALUES(17.30);
INSERT INTO preise VALUES(225);
INSERT INTO preise VALUES(247);
INSERT INTO preise VALUES(19.15);

SELECT * FROM preise;
 netto │ brutto
───────┼─────────
 17.30 │ 20.5870
   225 │  267.75
   247 │  293.93
 19.15 │ 22.7885
(4 rows)

Die Spalte brutto wird direkt aus dem Nettopreis berechnet. Das Schlüsselwort STORED ist Pflicht. Selbstverständlich können auf Generated Columns auch Indexe erzeugt werden, allerdings können sie nicht Teil eines Primärschlüssels sein. Ferner muss der SQL Ausdruck eindeutig sein, d.h. auch bei gleicher Eingabemenge das dasselbe Ergebnis liefern. Spalten die als Generated Columns deklariert sind, können nicht explizit in INSERT– oder UPDATE-Operationen verwendet werden. Falls eine Spaltenliste unbedingt notwendig ist, kann mit dem Schlüsselwort DEFAULT der entsprechende Wert indirekt referenziert werden.

Wegfall von expliziten OID Spalten

Explizite OID Spalten waren historisch gesehen ein Weg, eindeutige Spaltenwerte zu erzeugen, so dass eine Tabellenzeile Datenbankweit eindeutig identifiziert werden kann. Seit langer Zeit werden diese in PostgreSQL^® jedoch nur noch explizit angelegt und deren grundlegende Funktionalität als überholt angesehen. Mit PostgreSQL^® wird nun auch die Möglichkeit explizit solche Spalten anzulegen, endgültig abgeschafft. Damit wird es nicht mehr möglich sein, die Direktive WITH OIDS bei Tabellen anzugeben. Systemtabellen, die schon immer per OID Objekte eindeutig referenzieren, geben OID Werte ab sofort ohne explizite Angabe von OID Spalten in der Ergebnismenge zurück. Besonders ältere Software, die sorglos mit Katalogabfragen hantierte, könnte Probleme durch eine doppelte Spaltenausgabe bekommen.

Verschieben der recovery.conf in die postgresql.conf

Bis einschließlich PostgreSQL^® 11 konfigurierte man Datenbankrecovery und Streaming Replication Instanzen über eine separate Konfigurationsdatei recovery.conf.
Mit PostgreSQL^® 12 wandern nun sämtliche dort getätigte Konfigurationsarbeiten in die postgresql.conf. Die Datei recovery.conf entfällt ab sofort. PostgreSQL^® 12 weigert sich zu starten, sobald diese Datei vorhanden ist. Ob Recovery oder ein Streaming Standby gewünscht ist, entscheidet nun entweder eine Datei recovery.signal (für Recovery) oder standby.signal (für Standby Systeme). Letztere hat bei Vorhandensein beider Dateien den Vorrang. Der alte Parameter standby_mode, der dieses Verhalten seither kontrollierte, wurde entfernt.

Für automatische Deployments von hochverfügbaren Systemen bedeutet dies eine große Änderung. Allerdings ist es nun auch möglich, entsprechende Konfigurationsarbeiten fast vollständig per ALTER SYSTEM vorzunehmen, sodass man nur noch eine Konfigurationsdatei pflegen muss.

REINDEX CONCURRENTLY

Mit PostgreSQL^® 12 gibt es nun eine Möglichkeit, Indexe mit so geringen Sperren wie möglich neu anzulegen. Dies vereinfacht einer der häufigsten Wartungsaufgaben in sehr schreiblastigen Datenbanken erheblich. Zuvor musste mit einer Kombination aus CREATE INDEX CONCURRENTLY und DROP INDEX CONCURRENTLY gearbeitet werden. Hierbei musste man auch aufpassen, dass Indexnamen entsprechend neu vergeben wurden.

Die Release Notes geben eine noch viel detailliertere Übersicht über alle Neuerungen und vor allem auch Inkompatiblitäten gegenüber den vorangegangenen PostgreSQL^® Versionen.