2022-09-30

Postgres-Tabellen direkt aus DuckDB abfragen

Hannes Mühleisen

Einleitung

PostgreSQL ist die fortschrittlichste Open-Source-Datenbank der Welt (Selbstbeschreibung). Von ihren interessanten Anfängen als akademisches DBMS hat sie sich in den vergangenen 30 Jahren zu einem grundlegenden Arbeitstier unserer digitalen Umgebung entwickelt.

PostgreSQL ist für traditionelle transaktionale Einsatzfälle, „OLTP“, entworfen, bei denen Zeilen in Tabellen nebenläufig angelegt, aktualisiert und entfernt werden, und darin ist es hervorragend. Diese Designentscheidung macht PostgreSQL jedoch deutlich weniger geeignet für analytische Einsatzfälle, „OLAP“, bei denen große Teile von Tabellen gelesen werden, um Zusammenfassungen der gespeicherten Daten zu erzeugen. Es gibt jedoch viele Einsatzfälle, in denen sowohl transaktionale als auch analytische Nutzung wichtig sind, etwa wenn die neuesten Business-Intelligence-Erkenntnisse aus transaktionalen Daten gewonnen werden sollen.

Es gab einige Versuche, Datenbankmanagementsysteme zu bauen, die beide Workloads gut beherrschen, „HTAP“, aber im Allgemeinen sind viele Designentscheidungen zwischen OLTP- und OLAP-Systemen harte Kompromisse, was dieses Unterfangen schwierig macht. Akzeptiert man, dass one size doch nicht für alle passt, werden Systeme oft getrennt: Die transaktionalen Anwendungsdaten leben in einem zweckgebauten System wie PostgreSQL, und eine Kopie der Daten liegt in einem völlig anderen DBMS. Ein zweckgebautes analytisches System beschleunigt analytische Abfragen um mehrere Größenordnungen.

Leider kann das Pflegen einer Kopie der Daten für analytische Zwecke problematisch sein: Die Kopie ist sofort veraltet, sobald neue Transaktionen verarbeitet werden, und erfordert ein komplexes, nicht triviales Synchronisations-Setup. Zwei Kopien der Datenbank zu speichern, braucht außerdem den doppelten Speicherplatz. OLTP-Systeme wie PostgreSQL nutzen traditionell eine zeilenbasierte Datendarstellung, OLAP-Systeme neigen zu einer chunked-columnar Datendarstellung. Beides geht nicht, ohne eine Kopie der Daten mit all den damit verbundenen Problemen zu pflegen. Außerdem kann die SQL-Syntax zwischen dem jeweiligen OLAP-System und Postgres ziemlich unterschiedlich sein.

Der Designraum ist jedoch nicht so schwarz-weiß, wie er scheint. Die OLAP-Leistung in Systemen wie DuckDB kommt zum Beispiel nicht nur von einer chunked-columnar On-Disk-Datendarstellung. Viel von DuckDBs Leistung kommt von seiner vektorisierten Query-Processing-Engine, die speziell auf analytische Abfragen abgestimmt ist. Was, wenn DuckDB irgendwie in PostgreSQL gespeicherte Daten lesen könnte? Das klingt einschüchternd, aber wir haben uns auf den Weg gemacht, genau das möglich zu machen.

Um schnelle und konsistente analytische Reads von Postgres-Datenbanken zu erlauben, haben wir den „Postgres Scanner“ entworfen und umgesetzt. Dieser Scanner nutzt den Binary-Transfer-Modus des Postgres-Client-Server-Protokolls (siehe den Abschnitt Implementation für mehr Details) und erlaubt uns, die Daten effizient umzuwandeln und direkt in DuckDB zu nutzen.

Unter anderem unterscheidet sich DuckDBs Design von konventionellen Datenverwaltungssystemen, weil DuckDBs Query-Processing-Engine auf nahezu beliebigen Datenquellen laufen kann, ohne die Daten in das eigene Speicherformat kopieren zu müssen. DuckDB kann derzeit zum Beispiel direkt Abfragen auf Parquet-Dateien, CSV-Dateien, SQLite-Dateien, Pandas-, R- und Julia-DataFrames sowie Apache-Arrow-Quellen ausführen. Diese neue Erweiterung fügt die Fähigkeit hinzu, PostgreSQL-Tabellen direkt aus DuckDB abzufragen.

Nutzung

Der Quellcode der Postgres-Scanner-DuckDB-Erweiterung ist auf GitHub verfügbar, sie ist aber direkt über DuckDBs neuen Mechanismus zur Installation binärer Erweiterungen installierbar. Zur Installation einmal folgende SQL-Abfrage ausführen:

INSTALL postgres_scanner;

Wann immer Sie die Erweiterung nutzen wollen, müssen Sie sie zuerst laden:

LOAD postgres_scanner;

Um eine Postgres-Datenbank für DuckDB zugänglich zu machen, nutzen Sie den Befehl POSTGRES_ATTACH:

CALL postgres_attach('dbname=myshinydb');

postgres_attach nimmt einen einzelnen erforderlichen String-Parameter entgegen, den libpq-Connection-String. Sie können zum Beispiel 'dbname=myshinydb' übergeben, um einen anderen Datenbanknamen zu wählen. Im einfachsten Fall ist der Parameter einfach ''. Es gibt drei zusätzliche benannte Parameter der Funktion:

• source_schema der Name eines nicht-standardmäßigen Schema-Namens in Postgres, aus dem Tabellen geholt werden. Default ist public.
• overwrite ob bestehende Views im Zielschema überschrieben werden sollen, Default ist false.
• filter_pushdown ob Filterprädikate, die DuckDB aus der Abfrage ableitet, an Postgres weitergereicht werden sollen, Default ist false. Siehe unten für eine Diskussion, was dieser Parameter steuert.

Die Tabellen in der Datenbank werden als Views in DuckDB registriert, Sie können sie auflisten mit

PRAGMA show_tables;

Dann können Sie diese Views normal mit SQL abfragen. Wieder: Es werden keine Daten kopiert, das ist nur eine virtuelle View auf die Tabellen in Ihrer Postgres-Datenbank.

Wenn Sie lieber nicht alle Tabellen anbinden, sondern nur eine einzelne Tabelle abfragen, geht das direkt über die tabellenerzeugenden Funktionen POSTGRES_SCAN und POSTGRES_SCAN_PUSHDOWN, z. B.

SELECT * FROM postgres_scan('dbname=myshinydb', 'public', 'mytable');
SELECT * FROM postgres_scan_pushdown('dbname=myshinydb', 'public', 'mytable');

Beide Funktionen nehmen drei unbenannte String-Parameter entgegen, den libpq-Connection-String (siehe oben), einen Postgres-Schema-Namen und einen Tabellennamen. Der Schema-Name ist oft public. Wie der Name andeutet, führt die Variante mit „pushdown“ im Namen Selection-Pushdown aus, wie unten beschrieben.

Der Postgres-Scanner kann nur tatsächliche Tabellen lesen, Views werden nicht unterstützt. Sie können solche Views natürlich innerhalb von DuckDB nachbilden, die Syntax sollte genau dieselbe sein!

Implementation

Architektonisch ist der Postgres-Scanner als Plug-in-Erweiterung für DuckDB umgesetzt, die eine sogenannte Table-Scan-Funktion (postgres_scan) in DuckDB bereitstellt. Es gibt viele solche Funktionen in DuckDB und in Erweiterungen, etwa die Parquet- und CSV-Reader, Arrow-Reader usw.

Der Postgres-Scanner nutzt die Standardbibliothek libpq, die er statisch einbindet. Ironischerweise macht das den Postgres-Scanner leichter zu installieren als die anderen Postgres-Clients. Postgres’ normales Client-Server-Protokoll ist jedoch recht langsam, daher haben wir einiges an Zeit in die Optimierung gesteckt. Als Hinweis: DuckDBs SQLite-Scanner hat dieses Problem nicht, weil SQLite ebenfalls eine In-Process-Datenbank ist.

Wir haben tatsächlich einen Prototyp eines direkten Readers für Postgres-Datenbankdateien umgesetzt, aber obwohl die Leistung großartig war, gibt es das Problem, dass committete, aber noch nicht checkpointete Daten noch nicht in den Heap-Dateien liegen würden. Außerdem würde unser Reader, wenn gerade ein Checkpoint läuft, den Checkpointer häufig überholen und zusätzliche Inkonsistenzen verursachen. Wir haben diesen Ansatz aufgegeben, weil wir eine aktiv genutzte Postgres-Datenbank abfragen können wollen und Konsistenz für wichtig halten. Eine andere architektonische Option wäre gewesen, einen DuckDB Foreign Data Wrapper (FDW) für Postgres ähnlich duckdb_fdw umzusetzen, aber obwohl das die Protokollsituation verbessern könnte, ist das Deployment einer Postgres-Erweiterung auf Produktionsservern recht riskant, daher erwarten wir, dass das wenige tun können.

Stattdessen nutzen wir den selten genutzten Binary-Transfer-Modus des Postgres-Client-Server-Protokolls. Dieses Format ist dem On-Disk-Abbild von Postgres-Datendateien recht ähnlich und vermeidet einige der sonst teuren To-String- und From-String-Umwandlungen. Um zum Beispiel ein normales int32 aus der Protokollnachricht zu lesen, müssen wir nur die Byte-Reihenfolge tauschen (ntohl).

Der Postgres-Scanner verbindet sich mit PostgreSQL und setzt eine Abfrage ab, um eine bestimmte Tabelle über das Binärprotokoll zu lesen. Im einfachsten Fall (siehe Optimierungen unten) führen wir intern zum Lesen einer Tabelle namens lineitem die Abfrage aus:

COPY (SELECT * FROM lineitem) TO STDOUT (FORMAT binary);

Diese Abfrage beginnt, den Inhalt von lineitem zu lesen, und schreibt ihn direkt im Binärformat in den Protokollstrom.

Parallelisierung

DuckDB unterstützt automatische Intra-Query-Parallelisierung durch Pipeline-Parallelismus, daher wollen wir auch Scans auf Postgres-Tabellen parallelisieren: Unser Scan-Operator öffnet mehrere Verbindungen zu Postgres und liest Teilmengen der Tabelle aus jeder. Um das Lesen der Tabelle effizient aufzuteilen, nutzen wir Postgres’ eher obskuren TID-Scan-Operator (Tuple ID), der einer Abfrage erlaubt, einen angegebenen Bereich von Tuple-IDs aus einer Tabelle chirurgisch zu lesen. Die Tuple-IDs haben die Form (page, tuple). Wir parallelisieren unseren Scan einer Postgres-Tabelle anhand von Datenbankseitenbereichen, ausgedrückt in TIDs. Jede Scan-Aufgabe liest derzeit 1000 Seiten. Um zum Beispiel eine Tabelle mit 2500 Seiten zu lesen, würden wir drei Scan-Aufgaben mit TID-Bereichen [(0,0),(999,0)], [(1000,0),(1999,0)] und [(2000,0),(UINT32_MAX,0)] starten. Eine offene Grenze für den letzten Bereich ist wichtig, weil die Seitenzahl (relpages) einer Tabelle in der Tabelle pg_class nur eine Schätzung ist. Für einen gegebenen Seitenbereich (P_MIN, P_MAX) wird unsere Abfrage von oben also so erweitert:

COPY (
   SELECT
     *
   FROM lineitem
   WHERE
     ctid BETWEEN '(P_MIN,0)'::tid AND '(P_MAX,0)'::tid
   ) TO STDOUT (FORMAT binary);

So können wir die Tabelle effizient parallel scannen, ohne uns in irgendeiner Weise auf das Schema zu stützen. Weil die Seitengröße in Postgres fest ist, hat das außerdem den Bonus, den Aufwand zum Lesen einer Teilmenge der Seite unabhängig von der Zahl der Spalten in jeder Zeile auszugleichen.

„Aber Moment!“, werden Sie sagen, laut Dokumentation ist die Tuple-ID nicht stabil und kann durch Operationen wie VACUUM ALL geändert werden. Wie können Sie sie zum Synchronisieren paralleler Scans nutzen? Das stimmt und könnte problematisch sein, wir haben aber eine Lösung gefunden:

Transaktionale Synchronisation

Natürlich wird von einer transaktionalen Datenbank wie Postgres erwartet, dass Transaktionen laufen, während wir unsere Tabellenscans für analytische Zwecke ausführen. Daher müssen wir nebenläufige Änderungen an der Tabelle, die wir parallel scannen, adressieren. Wir lösen das, indem wir zuerst in DuckDBs Bind-Phase, in der die Query-Planung stattfindet, eine neue Read-only-Transaktion anlegen. Wir lassen diese Transaktion laufen, bis wir vollständig mit dem Lesen der Tabelle fertig sind. Wir nutzen ein weiteres wenig bekanntes Postgres-Feature, pg_export_snapshot(), das uns erlaubt, den aktuellen Transaktionskontext in einer Verbindung zu holen und ihn dann mit SET TRANSACTION SNAPSHOT ... in unsere parallelen Lese-Verbindungen zu importieren. So sehen alle Verbindungen, die zu einem einzelnen Tabellenscan gehören, den Tabellenzustand genau so, wie er ganz am Anfang unseres Scans erschien, über den potenziell langen Leseprozess hinweg.

Projection- und Selection-Push-Down

DuckDBs Query-Optimizer verschiebt Selections (Filter auf Zeilen) und Projections (Entfernen ungenutzter Spalten) so weit wie möglich nach unten im Query-Plan (Push-down) und weist sogar die untersten Scan-Operatoren an, diese Operationen auszuführen, wenn sie sie unterstützen. Für den Postgres-Scanner haben wir beide Push-down-Varianten umgesetzt. Projections sind recht geradlinig – wir können Postgres unmittelbar anweisen, nur die Spalten zu holen, die die Abfrage nutzt. Das reduziert natürlich auch die Zahl der zu übertragenden Bytes, was Abfragen beschleunigt. Für Selections konstruieren wir einen SQL-Filterausdruck aus den heruntergeschobenen Filtern. Führen wir zum Beispiel eine Abfrage wie SELECT l_returnflag, l_linestatus FROM lineitem WHERE l_shipdate < '1998-09-02' durch den Postgres-Scanner, würde er folgende Abfragen ausführen:

COPY (
  SELECT
    "l_returnflag",
    "l_linestatus"
  FROM "public"."lineitem"
  WHERE
    ctid BETWEEN '(0,0)'::tid AND '(1000,0)'::tid AND
    ("l_shipdate" < '1998-09-02' AND "l_shipdate" IS NOT NULL)
  ) TO STDOUT (FORMAT BINARY);
-- and so on

Wie Sie sehen, hat der Projection- und Selection-Pushdown die gegen Postgres ausgeführten Abfragen entsprechend erweitert. Die Nutzung des Selection-Push-down ist optional. Es kann Fälle geben, in denen das Ausführen eines Filters in Postgres tatsächlich langsamer ist als das Übertragen der Daten und das Ausführen des Filters in DuckDB, zum Beispiel wenn Filter nicht sehr selektiv sind (viele Zeilen matchen).

Leistung

Um die Leistung des Postgres-Scanners zu untersuchen, haben wir den bekannten TPC-H-Benchmark auf DuckDB mit seinem internen Speicherformat, auf Postgres ebenfalls mit seinem internen Format und mit DuckDB, das aus Postgres über den neuen Postgres-Scanner liest, ausgeführt. Wir haben DuckDB 0.5.1 und Postgres 14.5 genutzt, alle Experimente liefen auf einem MacBook Pro mit M1-Max-CPU. Das Experiment-Skript ist verfügbar. Wir führen „Scale Factor“ 1 von TPCH aus und erzeugen einen Datensatz von grob 1 GB mit ca. 6 Millionen Zeilen in der größten Tabelle, lineitem. Jede der 22 TPC-H-Benchmark-Abfragen wurde 5-mal ausgeführt, und wir berichten die Median-Laufzeit in Sekunden. Die Zeitaufschlüsselung steht in der folgenden Tabelle.

Stock-Postgres schafft die Abfragen 17 und 20 nicht innerhalb eines Ein-Minuten-Timeouts wegen korrelierter Subqueries, die eine Abfrage auf der Tabelle lineitem enthalten. Bei den anderen Abfragen sehen wir, dass DuckDB mit dem Postgres-Scanner nicht nur alle Abfragen fertiggestellt hat, sondern bei grob der Hälfte auch schneller war als Stock-Postgres, was erstaunlich ist, wenn man bedenkt, dass DuckDB seine Eingabedaten aus Postgres über das Client/Server-Protokoll wie oben beschrieben lesen muss. Natürlich ist Stock-DuckDB mit seinem eigenen Speicher immer noch 10× schneller, aber wie ganz am Anfang dieses Beitrags diskutiert, erfordert das, die Daten zuerst dorthin zu importieren.

Weitere Einsatzfälle

Der Postgres-Scanner kann auch genutzt werden, um Live-Postgres-Daten auf kreative Weise mit vorab gecachten Daten zu kombinieren. Das ist besonders wirksam bei einer Append-only-Tabelle, könnte aber auch genutzt werden, wenn eine Modified-Date-Spalte vorhanden ist. Betrachten Sie die folgende SQL-Vorlage:

INSERT INTO my_table_duckdb_cache
SELECT * FROM postgres_scan('dbname=myshinydb', 'public', 'my_table')
WHERE incrementing_id_column > (SELECT max(incrementing_id_column) FROM my_table_duckdb_cache);

SELECT * FROM my_table_duckdb_cache;

Das bietet schnellere Abfrageleistung mit vollständig aktuellen Abfrageergebnissen, auf Kosten von Datenduplikation. Es vermeidet außerdem komplexe Datenreplikationstechnologien.

DuckDB hat eingebaute Unterstützung, Abfrageergebnisse in Parquet-Dateien zu schreiben. Der Postgres-Scanner bietet einen recht einfachen Weg, Postgres-Tabellen in Parquet-Dateien zu schreiben, er kann sogar direkt nach S3 schreiben, wenn gewünscht. Zum Beispiel

COPY (SELECT * FROM postgres_scan('dbname=myshinydb', 'public', 'lineitem')) TO 'lineitem.parquet' (FORMAT parquet);

Fazit

DuckDBs neue Postgres-Scanner-Erweiterung kann PostgreSQL-Tabellen lesen, während PostgreSQL läuft, und die Antworten auf komplexe OLAP-SQL-Abfragen oft schneller berechnen, als PostgreSQL selbst das kann, ohne Daten zu duplizieren. Der Postgres-Scanner ist derzeit in der Preview, und wir sind neugierig, was Sie denken. Wenn Sie Probleme mit dem Postgres-Scanner finden, melden Sie sie bitte.