Select Page

NETWAYS Blog

Warum wir in den NETWAYS-PostgreSQL-Trainings eigentlich nur “psql” nutzen

Wieder einmal PostgreSQL-Trainings (Fundamentals und Advanced) durchgeführt.
Wieder einmal wurde nach GUIs gefragt.
Wieder einmal haben wir uns dann letztlich doch fast auf psql beschränkt.

Warum ist das so?

 

GUI vs. TUI – the eternal battle

GUIs werden i. A. als “benutzerfreundlicher” dargestellt.

Ich persönlich wiederum finde es ganz schrecklich, dauernd zur Maus greifen zu müssen, um irgendeine Aktion auszulösen, für die die Entwickler keinen oder einen grauenhaften Shortcut konfiguriert haben… für mich sind GUIs also eher weniger benutzerfreundlich.

Mir ist auch klar, dass es beileibe nicht jedem Menschen so geht, und die Gewöhnung spielt dabei sicher auch eine enorme Rolle.

“The usual suspects”: die üblicherweise genannten Gründe pro GUI/TUI

GUI:

  • intuitiver zu bedienen
  • “gewohnte” Optik
  • bessere Übersicht über Ergebnisse von Queries

TUI:

  • steht vom Funktionsumfang dem GUI kaum nach
  • funktioniert auch bei langsamer Verbindung (“Zug”)
  • kann gescriptet werden

Es gibt aber einige durchaus schwerwiegende Gründe, warum ich bei Trainings so einen großen Wert auf psql lege.

“The killer arguments”: warum psql elementar ist

Die Lernschwelle ist bei GUIs unnötig hoch

  • Jedes GUI sieht letztlich (ein wenig) anders aus und es gibt einfach zu viele
  • Um eine erste Verbindung herzustellen, muss im GUI erst ein Server definiert werden, mit jeder Menge Parametern, u.a. einer Netzwerkverbindung
    • dafür muss aber erst ein Konfigurationsparameter (listen_addresses=) gesetzt werden, was wiederum erst deutlich nach dem ersten “Reinschnuppern” behandelt wird…
    • Im Terminal hingegen (wo ich ja gerade den DB-Server installiert habe) komme ich per psql direkt an die DB (wenn ich der OS-User postgres bin…)
  • Objekte anschauen (“Erste Schritte”):
    • TUI: nach Eingabe von psql kann ich einfach z.B. \dt eingeben und bekomme alle Tabellen gelistet, \d nachnamen zeigt mir instant die Tabellenstruktur, dazu Indexe, Primär-/Fremdschlüssel etc. an
    • in allen GUIs muss ich dafür erst durch einen Baum klicken, in pgAdmin4 z.B. Servergruppe -> Server -> Datenbank -> Schemas -> ‘public’ -> Tabellen

Die (online-)Trainings-VMs sind nur per ssh und Webinterface erreichbar

  • um da per GUI dranzukommen, müssten also die Teilnehmer erstmal Software auf ihren (privaten oder dienstlichen) PCs installieren…

Viel entscheidender ist aber m.E.:

psql ist für den PostgreSQL-DBA, was vi für den U\*\*X-Admin ist:

  • auf jeder PostgreSQL-Maschine verfügbar
  • minimalistisch, aber gleichzeitig unglaublich leistungsfähig
  • ich muss das sowieso zu nennenswerten Teilen beherrschen, z.B.
    • für den Fall, dass mir die Firewall einen Streich spielt
    • wenn ich mal als Superuser in die DB will/muss (max_connections= ausgeschöpft)
    • um Dinge zu scripten

Wenn mir jemand erzählt, er oder sie sei UNIX-Admin, dann aber einen nano benutzt, bin ich sofort (zurückhaltend ausgedrückt) skeptisch.

Ähnlich ist es mit psql. Ich muss (als DBA) sowieso wissen, wie ich damit z.B. Objekte anzeigen, DDL einspielen, ggfs. mal eine Stored procedure umschreiben etc. pp. kann. Wenn ich die Software also sowieso (halbwegs) beherrschen muss, kann ich sie doch auch gleich benutzen? Ich sehe nur wenige Szenarien, in denen ein(e) DBA von einem GUI profitieren würde.

Ein(e) AnalystIn hingegen wird die DB wahrscheinlich eher direkt an ein Reporting-Tool oder M$ Excel anbinden wollen.

Bleibt der/die (SQL-) EntwicklerIn. Ja, fair enough, da sehe auch ich gewissen Charme (üblicherweise F5 drücken, um das SQL im Fenster (erneut) laufen zu lassen). Auf der anderen Seite ist derselbe Effekt in psql durch Eingabe von \e zu erreichen, und da kommt ein vi. Der ist ja bekanntlich minimalistisch, aber… 😉

Und ob man DDL jetzt per GUI erzeugen sollte, darüber scheiden sich ja auch die Geister… IMHO eher nicht.

Fazit:

“I never leave the house without it!”

psql ist der vi(m) der PostgreSQL-Welt. Unfassbar flexibel und leistungsfähig, immer verfügbar und dadurch absolutes “Pflichtprogramm”.

P.S.

Ich habe mal jemanden kennengelernt, der eine U\*\*X-Consulting-Firma betrieb und lt. eigener Aussage nur Menschen anstellte, die den ed beherrschen. So weit würde ich dann auch nicht gehen… 😉

P.P.S.

Vielleicht hat die Abneigung gegen TUIs was mit Oracles SQL*Plus (TM) zu tun? Wäre absolut nachvollziehbar, das fasse ich auch nur mit der Kneifzange an…

 

Über den Author:

Gunnar “Nick” Bluth hat seine Liebe zu relationalen Datenbanken Ende des letzten Jahrtausends entdeckt. Über MS Access und MySQL 3.x landete er sehr schnell bei PostgreSQL und hat nie zurückgeschaut, zumindest nie ohne Schmerzen. Er verdient seine Brötchen seit beinahe 20 Jahren mit FOSS (Administration, Schulungen, Linux, PostgreSQL). Gelegentlich taucht er auch tiefer in die Programmierung ein, so als SQL-Programmierer bei der Commerzbank oder in App-Nebenprojekten.
Philipp Dorschner
Philipp Dorschner
Developer

Philipp hat im Jahr 2017 die Ausbildung zum Fachinformatiker – Systemintegration bei NETWAYS Professional Services begonnen. Während der Ausbildung bekam er ein immer größeres Interesse am Programmieren. Das führte dazu, dass Philipp nach erfolgreich bestandener Ausbildung die Kollegen aus Professional Services nicht nur als Consultant sondern auch als Entwickler tatkräftig unterstützt. Neben seinem Interesse an der Informationstechnologie, macht er Sport im Freien oder liest bei schlechtem Wetter auch gerne mal ein Buch zu Hause.

Schneller LIKEn

Nein, hier soll es nicht um Twitter, Instagram oder Youtube gehen, sondern um Datenbankabfragen in PostgreSQL wie diese:

blog # SELECT * FROM kunden WHERE vorname LIKE 'Ann%';

Diese Abfragen sind recht häufig anzutreffen, man denke z.B. an Drop-Down-Boxen, die z.B. per AJAX mit Vorschlägen gefüllt werden, sobald drei oder mehr Buchstaben eingegeben wurden.

Das Spielfeld

Unsere Beispieldaten enthalten 1.000.000 zufällig generierte Kunden in dieser Form und mit dieser Verteilung von Vornamen, die mit ‘Ann’ beginnen:

blog # \d kunden
                               Table "public.kunden"
┌────────────┬─────────┬───────────┬──────────┬────────────────────────────────────┐
│   Column   │  Type   │ Collation │ Nullable │              Default               │
├────────────┼─────────┼───────────┼──────────┼────────────────────────────────────┤
│ id         │ integer │           │ not null │ nextval('kunden_id_seq'::regclass) │
│ vorname    │ text    │           │ not null │                                    │
│ nachname   │ text    │           │ not null │                                    │
│ strasse    │ text    │           │ not null │                                    │
│ hausnummer │ integer │           │ not null │                                    │
│ plz        │ text    │           │ not null │                                    │
│ ort        │ text    │           │ not null │                                    │
│ bundesland │ text    │           │ not null │                                    │
└────────────┴─────────┴───────────┴──────────┴────────────────────────────────────┘
Indexes:
    "kunden_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "kunden_plz_check" CHECK (length(plz) = 5)

blog # vorname,count(*) FROM kunden WHERE vorname LIKE 'Ann%' GROUP BY vorname;
┌───────────┬───────┐
│  vorname  │ count │
├───────────┼───────┤
│ Anni      │   963 │
│ Annabella │   984 │
│ Anne      │   965 │
│ Annalena  │   971 │
│ Annika    │  1017 │
│ Anna      │  1011 │
│ Ann       │  1003 │
│ Annelie   │   976 │
│ Annemarie │  1001 │
│ Annabell  │   996 │
└───────────┴───────┘
(10 rows)

Ein erster Versuch – “vanilla”

Schauen wir doch mal, wie unsere PostgreSQL-Datenbank eine Suche nach ‘Ann%’ bearbeitet:

blog # EXPLAIN (ANALYSE,COSTS) SELECT * FROM kunden WHERE vorname LIKE 'Ann%';
┌────────────────────────────────────────────────────────────────────┐
│                           QUERY PLAN                               │
├────────────────────────────────────────────────────────────────────┤
│ Seq Scan on kunden  (cost=0.00..24667.00 rows=10244 width=65)      |
|      (actual time=0.019..90.620 rows=9887 loops=1)                 │
│   Filter: (vorname ~~ 'Ann%'::text)                                │
│   Rows Removed by Filter: 990113                                   │
│ Planning time: 0.100 ms                                            │
│ Execution time: 90.956 ms                                          │
└────────────────────────────────────────────────────────────────────┘
(5 rows)

Ein Seq Scan, also der gefürchtete Sequential-Scan aka. Full table scan; alle Datensätze werden gelesen und ‘vorname’ mit ‘Ann%’ verglichen. Das ist sehr ineffektiv.

Ein Index muss her!

Die Lösung ist offensichtlich: wenn solche Abfragen häufig vorkommen, muss ein Index her. Der wird den Vorgang beschleunigen:

blog # CREATE INDEX vorname_btree_vanilla ON kunden (vorname);
blog # EXPLAIN (ANALYSE,COSTS) SELECT * FROM kunden WHERE vorname LIKE 'Ann%';
┌───────────────────────────────────────────────────────────────────────┐
│                            QUERY PLAN                                 │
├───────────────────────────────────────────────────────────────────────┤
│ Seq Scan on kunden  (cost=0.00..24667.00 rows=10244 width=65)         |      
|      (actual time=0.011..105.340 rows=9887 loops=1)                   │
│   Filter: (vorname ~~ 'Ann%'::text)                                   │
│   Rows Removed by Filter: 990113                                      │
│ Planning time: 0.195 ms                                               │
│ Execution time: 105.768 ms                                            │
└───────────────────────────────────────────────────────────────────────┘
(5 rows)

Uhm, Moment mal… warum nimmt meine Datenbank nicht den Index zu Hilfe?!? Geht es denn mit einzelnen Werten?

 blog # EXPLAIN (ANALYSE,COSTS) SELECT * FROM kunden WHERE vorname IN ('Anna','Anne','Annelie');
┌────────────────────────────────────────────────────────────────────────────────────────┐
│                              QUERY PLAN                                                │
├────────────────────────────────────────────────────────────────────────────────────────┤
│ Bitmap Heap Scan on kunden  (cost=59.83..6892.21 rows=2909 width=65)                   |
|      (actual time=1.275..5.054 rows=2952 loops=1)                                      │
│   Recheck Cond: (vorname = ANY ('{Anna,Anne,Annelie}'::text[]))                        │
│   Heap Blocks: exact=2656                                                              │
│   ->  Bitmap Index Scan on vorname_btree_vanilla  (cost=0.00..59.10 rows=2909 width=0) |
|      (actual time=0.652..0.652 rows=2952 loops=1)                                      │
│         Index Cond: (vorname = ANY ('{Anna,Anne,Annelie}'::text[]))                    │
│ Planning time: 0.136 ms                                                                │
│ Execution time: 5.292 ms                                                               │
└────────────────────────────────────────────────────────────────────────────────────────┘
(7 rows)

Ja, da wird der Index genommen, und die Ausführung ist auch gleich um Größenordnungen schneller.

Was ist also das Problem?

“C” und seine Spätfolgen – Schei* encoding!

Das Geheimnis liegt – wie so häufig – in der Lokalisierung. Btree-Indexe sind (für Text-Daten) auf das C-Locale hin optimiert. Wenn aber die Datenbank (wie heutzutage üblich!) mit en_US.UTF8 oder de_DE.UTF8 initialisiert wurde, müssen wir dem Index bei der Erstellung mitteilen, dass wir pattern operator-Aktionen ausführen können wollen. PostgreSQL kommt mit einem ganzen Haufen dieser Operator Classes.

Für unser TEXT-Feld ‘vorname’ nehmen wir text_pattern_ops. Nach der Erstellung testen wir, ob der Index unsere LIKE-Anfrage beschleunigt und verifizieren, dass auch weiterhin die klassischen <=, == und >= Vergleichsoperatoren funktionieren:

blog # DROP INDEX vorname_btree_vanilla ;
blog # CREATE INDEX vorname_btree_opclass ON kunden (vorname text_pattern_ops);
blog # EXPLAIN (ANALYSE,COSTS) SELECT * FROM kunden WHERE vorname LIKE 'Ann%';
┌─────────────────────────────────────────────────────────────────────────────────────────┐
│                                     QUERY PLAN                                          │
├─────────────────────────────────────────────────────────────────────────────────────────┤
│ Bitmap Heap Scan on kunden  (cost=129.19..10234.85 rows=10244 width=65)                 |
|       (actual time=5.327..16.083 rows=9887 loops=1)                                     │
│   Filter: (vorname ~~ 'Ann%'::text)                                                     │
│   Heap Blocks: exact=6830                                                               │
│   ->  Bitmap Index Scan on vorname_btree_opclass  (cost=0.00..126.62 rows=5820 width=0) |
|       (actual time=3.524..3.524 rows=9887 loops=1)                                      │
│         Index Cond: ((vorname ~>=~ 'Ann'::text) AND (vorname ~<~ 'Ano'::text))          │
│ Planning time: 0.378 ms                                                                 │
│ Execution time: 16.650 ms                                                               │
└─────────────────────────────────────────────────────────────────────────────────────────┘
(7 rows)

blog # EXPLAIN (ANALYSE,COSTS) SELECT * FROM kunden WHERE vorname IN ('Anna','Anne','Annelie');
┌─────────────────────────────────────────────────────────────────────────────────────────┐
│                                        QUERY PLAN                                       │
├─────────────────────────────────────────────────────────────────────────────────────────┤
│ Bitmap Heap Scan on kunden  (cost=59.83..6892.21 rows=2909 width=65)                    |
|       (actual time=1.233..5.001 rows=2952 loops=1)                                      │
│   Recheck Cond: (vorname = ANY ('{Anna,Anne,Annelie}'::text[]))                         │
│   Heap Blocks: exact=2656                                                               │
│   ->  Bitmap Index Scan on vorname_btree_opclass  (cost=0.00..59.10 rows=2909 width=0)  |
|       (actual time=0.634..0.634 rows=2952 loops=1)                                      │
│         Index Cond: (vorname = ANY ('{Anna,Anne,Annelie}'::text[]))                     │
│ Planning time: 0.135 ms                                                                 │
│ Execution time: 5.246 ms                                                                │
└─────────────────────────────────────────────────────────────────────────────────────────┘
(7 rows)

Wunderbar! Und 17ms klingt auch gleich viel besser als 100ms.

Geht da noch was?

Jedes Kind weiß, dass Indexe nur Anfragen wie LIKE ‘Ann%’ beschleunigen können. Für LIKE ‘%nna%’ gibt es leider keine Hilfe von der Datenbank. Ist ja auch irgendwie klar, der Btree muss ja von links nach rechts aufgebaut werden…

EXPLAIN (ANALYSE,COSTS) SELECT * FROM kunden WHERE vorname LIKE '%nna%';
┌─────────────────────────────────────────────────────────────────────────────────────────┐
│                                         QUERY PLAN                                      │
├─────────────────────────────────────────────────────────────────────────────────────────┤
│ Seq Scan on kunden  (cost=0.00..24667.00 rows=19022 width=65)                           |
|      (actual time=0.014..110.607 rows=11993 loops=1)                                    │
│   Filter: (vorname ~~ '%nna%'::text)                                                    │
│   Rows Removed by Filter: 988007                                                        │
│ Planning time: 0.131 ms                                                                 │
│ Execution time: 111.006 ms                                                              │
└─────────────────────────────────────────────────────────────────────────────────────────┘
(5 rows)

Aber stimmt das? Gibt es wirklich keine Möglichkeit, solche Abfragen zu beschleunigen?

PostgreSQL ist schier unfassbar erweiterbar, und unter anderem kommt es von Haus aus mit einer Extension pg_trgm, die wiederum operator classes für GIN und GiST Indexe mitbringt.

blog # CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION
blog # CREATE INDEX vorname_gin_trgm ON kunden USING GIN (vorname gin_trgm_ops);
blog # EXPLAIN (ANALYSE,COSTS) SELECT * FROM kunden WHERE vorname LIKE '%nna%';
┌──────────────────────────────────────────────────────────────────────────────────────────┐
│                                           QUERY PLAN                                     │
├──────────────────────────────────────────────────────────────────────────────────────────┤
│ Bitmap Heap Scan on kunden  (cost=183.42..13123.52 rows=19022 width=65)                  |
|       (actual time=5.049..15.935 rows=11993 loops=1)                                     │
│   Recheck Cond: (vorname ~~ '%nna%'::text)                                               │
│   Heap Blocks: exact=7670                                                                │
│   ->  Bitmap Index Scan on vorname_gin_trgm  (cost=0.00..178.67 rows=19022 width=0)      |   
|       (actual time=3.014..3.014 rows=11993 loops=1)                                      │
│         Index Cond: (vorname ~~ '%nna%'::text)                                           │
│ Planning time: 0.253 ms                                                                  │
│ Execution time: 16.488 ms                                                                │
└──────────────────────────────────────────────────────────────────────────────────────────┘
(7 rows)

pg_trgm kann noch mehr

Eine vermeintlich nette Spielerei, aber – wenn man es denn kennt – in vielen Situationen hilfreich, ist die Ähnlichkeitssuche, die pg_trgm in Form von Funktionen und Operatoren mitbringt:

blog # SELECT vorname, count(*) FROM kunden WHERE vorname % 'Nick' GROUP BY vorname ORDER BY similarity(vorname, 'Nick') DESC;
┌─────────┬───────┐
│ vorname │ count │
├─────────┼───────┤
│ Nick    │   995 │
│ Nico    │  1047 │
│ Nicole  │   977 │
│ Nicolas │  1026 │
└─────────┴───────┘
(4 rows)

Und auch hier beschleunigt der GIN-Index die Abfrage signifikant:

blog # EXPLAIN ANALYSE SELECT vorname, count(*) FROM kunden WHERE vorname % 'Nick' GROUP BY vorname ORDER BY similarity(vorname, 'Nick') DESC;
┌──────────────────────────────────────────────────────────────────────────────────────────┐
│                                          QUERY PLAN                                      │
├──────────────────────────────────────────────────────────────────────────────────────────┤
│ Sort  (cost=24761.50..24763.07 rows=630 width=18)                                        |
|      (actual time=915.696..915.697 rows=4 loops=1) .                                     │
│   Sort Key: (similarity(vorname, 'Nick'::text)) DESC                                     │
│   Sort Method: quicksort  Memory: 25kB                                                   │
│   ->  GroupAggregate  (cost=24716.83..24732.20 rows=630 width=18)                        |
|      (actual time=915.305..915.689 rows=4 loops=1)                                       │
│         Group Key: vorname                                                               │
│         ->  Sort  (cost=24716.83..24719.33 rows=1000 width=6)                            |
|      (actual time=915.162..915.305 rows=4045 loops=1)                                    │
│               Sort Key: vorname                                                          │
│               Sort Method: quicksort  Memory: 286kB                                      │
│               ->  Seq Scan on kunden  (cost=0.00..24667.00 rows=1000 width=6)            |
|      (actual time=0.650..914.065 rows=4045 loops=1)                                      │
│                     Filter: (vorname % 'Nick'::text)                                     │
│                     Rows Removed by Filter: 995955                                       │
│ Planning time: 0.296 ms                                                                  |
│ Execution time: 915.737 ms                                                               │
└──────────────────────────────────────────────────────────────────────────────────────────┘
(13 rows)

blog # CREATE INDEX vorname_gin_trgm ON kunden USING GIN (vorname gin_trgm_ops);
blog # EXPLAIN ANALYSE SELECT vorname, count(*) FROM kunden WHERE vorname % 'Nick' GROUP BY vorname ORDER BY similarity(vorname, 'Nick') DESC;
┌──────────────────────────────────────────────────────────────────────────────────────────┐
│                                             QUERY PLAN                                   │
├──────────────────────────────────────────────────────────────────────────────────────────┤
│ Sort  (cost=3164.18..3165.75 rows=630 width=18)                                          |
|      (actual time=32.510..32.510 rows=4 loops=1)                                         │
│   Sort Key: (similarity(vorname, 'Nick'::text)) DESC                                     │
│   Sort Method: quicksort  Memory: 25kB                                                   │
│   ->  HashAggregate  (cost=3127.01..3134.88 rows=630 width=18)                           |
|      (actual time=32.497..32.503 rows=4 loops=1)                                         │
│         Group Key: vorname                                                               │
│         ->  Bitmap Heap Scan on kunden  (cost=75.75..3122.01 rows=1000 width=6)          |
|      (actual time=5.993..31.447 rows=4045 loops=1)                                       │
│               Recheck Cond: (vorname % 'Nick'::text)                                     │
│               Rows Removed by Index Recheck: 13010                                       │
│               Heap Blocks: exact=9174                                                    │
│               ->  Bitmap Index Scan on vorname_gin_trgm                                  |
|      (cost=0.00..75.50 rows=1000 width=0) (actual time=4.976..4.976 rows=17055 loops=1)  │
│                     Index Cond: (vorname % 'Nick'::text)                                 │
│ Planning time: 0.123 ms                                                                  │
│ Execution time: 32.563 ms                                                                │
└──────────────────────────────────────────────────────────────────────────────────────────┘
(13 rows)

Fazit

Dass PostgreSQL nicht von Haus aus alle LIKE-Anfragen per Index beschleunigt, sorgt gerne für Irritationen. Auf der anderen Seite öffnen sich, sobald man anfängt, sich mit dem Thema auseinanderzusetzen, ganz neue Möglichkeiten, die man in dieser Form bei anderen DBMS nicht findet. Und wir haben noch gar nicht über eine echte Volltextsuche gesprochen!

P.S.: pg_trgm kann kein Chinesisch!

Wenn nicht-alphanumerische Buchstaben in’s Spiel kommen (oder pg_trgm zu langsam ist…) sollte man einen Blick auf PGroonga werfen, das in diesen Bereichen glänzt.

Über den Author

Gunnar “Nick” Bluth hat seine Liebe zu relationalen Datenbanken Ende des letzten Jahrtausends entdeckt. Über MS Access und MySQL 3.x landete er sehr schnell bei PostgreSQL und hat nie zurückgeschaut, zumindest nie ohne Schmerzen. Er verdient seine Brötchen seit beinahe 20 Jahren mit FOSS (Administration, Schulungen, Linux, PostgreSQL). Gelegentlich taucht er auch tiefer in die Programmierung ein, so als SQL-Programmierer bei der Commerzbank oder in App-Nebenprojekten

Milano – PostgreSQL Conference Europe 2019

It has been a while since one of us visited a PostgreSQL Conference. So when the opportunity arose Lennart and I took it. Additionally, it is somewhat of a special pgconf this year, because the conference is held where it originated, Italy. With over 500 attendees the conference set a new all time record. It is going to be a good one. Numerous talks, a lot of variety and gaining more insight into the postgres community. That being said, these talks can by no means be summarized in one blog post, that is why I have decided to bring you my just personal highlights. If you are interested in talks not mentioned here, I highly recommend checking out the official pgconf.eu website, where at least most slides of speakers can be found.

Paul Ramseys talk about PostGIS was the first one that spiked my attention. Having used PostGIS before without extensive knowledge, the add-on seemed pretty mundane, but little did I know. And I mean that. Little did I know of the extend of PostGIS or GIS as a whole. Let me try to give you the just of it. There was a phrase that he used a few times that somehow stuck: GIS without the GIS. To be just that PostGIS without the GIS the workload of low-performance scripts had to be moved to more efficient SQL. Additionally, spatial middleware from the database to the web had to go. Lastly, PostGIS had to be build in a way that provides developer with direct access to a GIS analytical engine. After giving everyone a basic introduction with the some example problems and queries to solve them. Well, he told us everything about PostGIS or atleast a lot. SFSQL, OSS, ISO, Indexing, Spatial Joins, the list is long. 189 Slides, If you will.

Next up in my little list of talks I really enjoyed was Wonderful SQL features your ORMs can use (or not) by Louise Grandjonc. She went through various ORMs (Object Relationship Mapper) during her talk like Django, SQLAlchemy, activerecord and Sequel and used them for a little project of hers. With scripts she aggregated Data regarding pop music lyrics and used this data as an example for various queries.

More Than a Query Language: SQL in the 21st Century was the title of the talk I want to tell you about next. Markus Winand talked a lot of history here. He looked at the very beginning and saw how SQL evolved and changed in many ways. A lot of empathisis was put on SQL having been bound to the idea of an relational data model for too long and that not necessarily beening the case today anymore. While jumping through the timeline of SQL development and explaining various features in the process he validated his claim.

I think it was a great event. I really liked the variety of topics and I hope even though I just mentioned a few, you got some insight into the event. PostgreSQL has an awesome community and having that conference as a platform to exchange experience, help and learn from eachother is just great

Alexander Stoll
Alexander Stoll
Junior Consultant

Alexander ist ein Organisationstalent und außerdem seit Kurzem Azubi im Professional Services. Wenn er nicht bei NETWAYS ist, sieht sein Tagesablauf so aus: Montag, Dienstag, Mittwoch Sport - Donnerstag Pen and Paper und ein Wochenende ohne Pläne. Den Sportteil lässt er gern auch mal ausfallen.

Über Physik, Erdnuss-Snacks und Schlangenöl aka Datenbanken

Achtung ein Hinweis in eigener Sache, dieser Artike kann Spuren von Polemik und rant enthalten!

Neal Stephenson hat in seinem wunderbaren Roman Cryptonomicon eine kleine Randgeschichte über eine Bandsäge eingebaut. Der für mich schönste Satz darin ist:

Anecdotes about accidents involving the bandsaw were told in hushed voices and not usually commingled with other industrial-accident anecdotes.

An diesen Satz muss ich immer denken, wenn es auf PostgreSQL-Konferenzen in den abendlichen War-Story-Runden zu Geschichten über MySQL oder MariaDB kommt, zugegeben hauptsächlichen deren Migration zu PostgreSQL.
Bei Entity–Attribute–Value (EAV) Schemas, nicht getesteten Backups, Prozeduren, die nicht maskierte Benutzereingaben als Tabellennamen für dynamisches SQL hernehmen etc. kann man sagen sie hätten es wissen können bzw. müssen. (Halt die üblichen Katastrophen, die man sich in gemütlicher Runde so erzählt, Datenbanker sind halt schon ein komischer Haufen.)

Das ist bei MySQL wie auch diversen anderen Datenbanken irgendwie anders, und ich frage mich immer wieder, warum ist das so?

Es hat evtl. etwas mit einer grundsätzlichen Bereitschaft zu tun, über physikalische Gegebenheiten nachzudenken.

Wenn jemand eine PostgreSQL-Mailingliste anschreibt und erklärt, dass Oracle eine Anfrage in 0,009 ms beantwortet, für die PostgreSQL über zwei Minuten benötigt, dann ist diese Frage erst einmal legitim.

Wenn dann aber nach und nach herauskommt, dass es um eine Tabelle mit 9.649.110 Zeilen und rund 3,5GB geht, die mittels SELECT * FROM table1; komplett gelesen wird, müsste einem doch sofort klar sein, das Ersteres nicht sein kann? Das brandneue DDR5-RAM schafft gerade mal 5,2 GT/s (Megatransfer), und da ist noch keine Netzwerkverbindung im Spiel. In 0,000009 Sekunden schafft selbst Licht “nur” rund 2,7km.

Wie können solche offensichtlichen Mess- oder Denkfehler nicht sofort auffallen? Die 2,7 km muss man ja nicht parat haben, aber dass 0,000009 Sekunden ein sehr kurzer Zeitraum sind, um 3,5GB Daten zu übertragen, liegt doch eigentlich für ITler nah?

Was mich zum Thema MM (Multi-Master-Replication) oder politisch korrekt Multi-Primary, Write-Anywhere etc. bringt…

PostgreSQL-Umsteiger von MySQL, MariaD, CockroachDB, Oracle,… fragen immer wieder (z.B. im Telegram-/Slack-Channel, den Mailinglisten, Seminaren), wie man denn einen MM-Cluster aufbaut.

Der Dialog läuft üblicherweise so ab:
Q: “Ich komme von und habe dort einen Multimaster-Cluster. Wie baue ich dasselbe mit PostgreSQL auf?”
A: “Gar nicht. Wahrscheinlich willst du das aber auch gar nicht.”
Q: “Doch, doch. Ich brauche die Hochverfügbarkeit und die Performance (ein Knoten allein schafft meine Schreiblast nicht)!”
A: “MM kostet Performance und verbessert deine Verfügbarkeit nicht wirklich. Benutze einfach eine klassische Replikation, ggfs. mit Lese-Lastverteilung.”

Verdeutlichen wir uns kurz, was es für eine Datenbank bedeutet, auf mehr als einem Clusterknoten schreibenden Zugriff zu erlauben:

  • Sequenzen (für Primärschlüssel bzw. “auto-increment/identity” Spalten) müssen synchronisiert werden, damit keine IDs doppelt vergeben werden
  • es muss sichergestellt werden, dass bei z.B. DELETE-Statements die referentielle Integrität erhalten bleibt; es muss also die Möglichkeit geben,
    clusterweit Datensätze oder sogar ganze Tabellen zu LOCKen
  • wenn auf mehreren Knoten derselbe Datensatz verändert wird, muss eine Konfliktbehandlung erfolgen. Wenn die nicht dem Zufall, sprich dem Replikations-Lag überlassen werden soll, werden clusterweite Transaktions-IDs benötigt
  • und zum Thema Schreiblast: Ziel ist es ja weiterhin, einen einheitlichen und konsistenten Datenbestand zu haben, die Schreiblast pro Knoten bleibt also unverändert!

Je mehr Knoten beteiligt sind, desto mehr Koordination ist erforderlich. Und jede dieser Aktivitäten benötigt Zeit, mindestens 1-2 Netzwerk-Roundtrips.

Je weiter meine Knoten voneinander entfernt sind, desto mehr Zeit und damit Performance geht allein schon durch pure Physik verloren!

Für PostgreSQL gibt bzw. gab es einige FOSS-Varianten, die MM beherrschen (Postgres-XC, Postgres-XL, BDR). Und 2ndQuadrant, deren derzeitiger Maintainer, sagt sinngemäß:

Die Einschränkungen und zusätzlichen Maßnahmen, die MM mit sich bringt bzw. erfordert, sind so eklatant, dass MM wahrscheinlich nie Einzug in den PostgreSQL-Core kommen wird. Und so ein Cluster ist explizit nicht für Hochverfügbarkeit gedacht, sondern um z.B. logisch weitgehend voneinander getrennte Datenbestände, z.B. Verkäufe und Lagerbestände in verschiedenen Ländern, in den jeweils anderen Standorten verfügbar zu machen.

Der Aufwand ist also durchaus erheblich, und mit ein klein wenig Nachdenken sollten sich die Zusammenhänge und Gründe auch jedem Interessenten erschließen.

Oder man macht sich einfach keine Gedanken und setzt bidirektionale Replikation auf, “was soll schon passieren” und “war ganz einfach, mit diesem Tutorial aus dem Netz”…

Multimaster-Clustering ist ein wenig wie im Dunkeln, bei Regen, mit 250 km/h über die Autobahn zu fahren. Als Beifahrer…

  1. Man wünscht sich auf jeden Fall, dass die Person am Steuer nicht nachtblind (sprich: “has RTFM”) ist
  2. das Auto und seine Grenzen sehr genau kennt (pun intended!)
  3. ein oder besser mehrere Fahrsicherheitstrainings hinter sich hat.

Und trotzdem ist man letztlich Faktoren überlassen, auf die man keinen Einfluss hat. Meist geht es ja auch gut…

Hier schließt sich der Kreis zur Bandsäge von oben, denn gewisse DBMS (Datenbank Management Systeme) stehen ja schon ohne bidirektionale Replikation nicht unbedingt im Ruf, Datenintegrität besonders hoch zu bewerten (https://bugs.mysql.com/bug.php?id=11472).

Zur Performance:

Ich erinnere mich, dass Oracle seinerzeit für ihr RAC angegeben hat, dass jeder neue Knoten 90% der Leistung des vorherigen Knotens bringt*. Was ein sensationell guter Wert wäre, aber eben alles andere als lineare Skalierung (linear steigt lediglich der Preis…):


WITH perf AS (
SELECT nodes, 0.9^(nodes-1) AS nextnodes_performance
FROM generate_series(1,8) AS nodes
)
SELECT *
,avg(nextnodes_performance) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS average_performance
,nodes * avg(nextnodes_performance) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS overall_performance
FROM perf;
┌───────┬───────────────────────┬────────────────────────┬────────────────────────┐
│ nodes │ nextnodes_performance │ average_performance │ overall_performance │
├───────┼───────────────────────┼────────────────────────┼────────────────────────┤
│ 1 │ 1.0000000000000000 │ 1.00000000000000000000 │ 1.00000000000000000000 │
│ 2 │ 0.9000000000000000 │ 0.95000000000000000000 │ 1.90000000000000000000 │
│ 3 │ 0.8100000000000000 │ 0.90333333333333333333 │ 2.70999999999999999999 │
│ 4 │ 0.7290000000000000 │ 0.85975000000000000000 │ 3.43900000000000000000 │
│ 5 │ 0.6561000000000000 │ 0.81902000000000000000 │ 4.09510000000000000000 │
│ 6 │ 0.5904900000000000 │ 0.78093166666666666667 │ 4.68559000000000000002 │
│ 7 │ 0.5314410000000000 │ 0.74529014285714285714 │ 5.21703099999999999998 │
│ 8 │ 0.4782969000000000 │ 0.71191598750000000000 │ 5.69532790000000000000 │
└───────┴───────────────────────┴────────────────────────┴────────────────────────┘

* vielleich waren es auch 80% oder 95% oder die Aussage war einfach kompletter Humbug?

Zur Verfügbarkeit:

Ein anderes gerne vorgebrachtes Argument, z.B. auf galeracluster.com ist “Transparent to Applications: Required no or minimal changes to the application”.

Ich habe mich immer gefragt, wie Oracle beim RAC das TCP-Protokoll aushebelt, eine Netzwerkverbindung bricht nun einmal ab, wenn der Host offline geht. Bis mir mal jemand gesteckt hat, dass man auf ein RAC mit einer anderen Client-Bibliothek zugreift. Mit anderen Worten: der Verbindungsabbruch wird nicht an das Programm durchgereicht, sondern das Statement (bzw. die Transaktion?) wird auf einer neuen Verbindung wiederholt. Ich unterstelle einfach mal, dass Galera etc. ähnlich arbeiten.

Können das die modernen Connection-Pools, ORMs etc. nicht auch alle schon mehr oder weniger selbsttätig oder mit “minimal changes”? Eine Applikation sollte es auf jeden Fall können. Aber gut, es sollte auch keine Applikationen mehr geben, die mehr als ein Statement außerhalb einer Transaktion durchführen…!

Auf jeden Fall rücken solche Aussagen die entsprechenden Produkte m. E. in die Nähe eines Worts, das im Titel dieses Beitrags steht…

Der klassische Ansatz:

Der Vollständigkeit halber sei noch kurz umrissen, wie eine hochverfügbare Cluster-Lösung mit PostgreSQL oder auch anderen DBMS aussehen kann.

  • ein Knoten (“Primary” oder non-PC “master”, P) erlaubt lesende und schreibende Zugriffe (R/W)
  • n andere Knoten (“Secondaries” oder “slaves”, S1,…,Sn) erhalten die Änderungen von P und erlauben lesende Zugriffe (RO)
  • zusätzlich landen alle Änderungen in einem Archiv, aus dem jeder beliebige Zeitpunkt wieder hergestellt werden kann (PITR)
  • Sn können synchron oder asynchron replizieren (bei synchroner Replikation sollte n > 1 sein!)

Der Zugriff auf den Knoten P kann auf verschiedene Arten erfolgen:

  • Clients erhalten die IPs aller Knoten und können selbst festlegen, ob sie Schreib-Lesezugriff benötigen
  • P hat eine zusätzliche virtuelle IP-Adresse, unter der er angesprochen wird (gerne zusammen mit P in einer Resource-Group der eingesetzten HA-Lösung)
  • ein vorgeschaltetes Tool, z.B. HAProxy weiß, welcher Knoten gerade P ist

Zusätzlich können Clients entscheiden, ob ihre jeweilige Aktivität nur lesend oder schreibend ist und sich entsprechend mit P oder einem Sn unterhalten, um die Gesamt-Performance zu verbessern.

Manche Pooler, z.B. PGPool-II können diese Unterscheidung sogar selber mehr oder weniger gut treffen. Vorsicht aber mit z.B. schreibenden Triggern!

Dass eine Client-Software eine halbwegs funktionale Fehlerbehandlung haben sollte, vielleicht sogar mit ein wenig Wartezeit vor’m retry, versteht sich von selbst.

Multi-Master-Datenbankcluster bringen einige Aspekte mit, die zu wenig oder zumindest selten beachtet werden. Manche sind ausgesprochen gefährlich!

Eine klassische Replikationslösung bietet annähernd dieselbe Verfügbarkeit, i.A. deutlich bessere Performance und bereitet auf Dauer wesentlich weniger Kopfschmerzen.

Wie man ein solches Cluster aufbaut, lernt man in unserem Kurs PostgreSQL Fundamentals.

Gunnar “Nick” Bluth hat seine Liebe zu relationalen Datenbanken Ende des letzten Jahrtausends entdeckt. Über MS Access und MySQL 3.x landete er sehr schnell bei PostgreSQL und hat nie zurückgeschaut, zumindest nie ohne Schmerzen. Er verdient seine Brötchen seit beinahe 20 Jahren mit FOSS (Administration, Schulungen, Linux, PostgreSQL). Gelegentlich taucht er auch tiefer in die Programmierung ein, so als SQL-Programmierer bei der Commerzbank oder in App-Nebenprojekten.

Neu im NETWAYS Trainings-Portfolio: PostgreSQL

Ihr liebt sie! Zweimal in Folge, 2017 und 2018, wurde PostgreSQL “DBMS of the Year” im DB-Engines Ranking. Na klar nehmen wir für all ihre Fans und solche, die es werden wollen, die freie, objektrelationale Datenbank PostgreSQL in unser Schulungs-Portfolio auf.

PostgreSQL steht für Zuverlässigkeit, Flexibilität und viele fortgeschrittene Features. Deswegen entwickelt es sich immer mehr zur Alternative zu anderen Open-Source-Datenbanken wie MySQL/MariaDB, aber auch den großen, kommerziellen Systemen.

Für einen stabilen, stressfreien Betrieb

Die Schulung „PostgreSQL Fundamentals“ richtet sich an Administratoren, die an einem stabilen, wartungsarmen Betrieb einer PostgreSQL-Datenbank interessiert sind. Fundamentals-Inhalte sind unter anderem Entwicklungs- und Release-Zyklus, Beschaffung und Installation, (G)UIs, grundlegende Konfiguration, Backup und Restore, Replikation und (Hoch)Verfügbarkeit und vieles weitere.

PostgreSQL als „Data Center“

Die Schulung „PostgreSQL Advanced“ richtet sich an Datenbank-Architekten und -Designer sowie Entwickler, aber auch DevOps, die ihre Kunden aus ebenjenen Bereichen beraten wollen. Advanced-Inhalte sind, grob skizziert, Performance, PostgreSQL als „Data Center“ und Programmierung und Design.

Im Schulungs-Paket enthalten sind wie immer umfangreiche Schulungsunterlagen, ein Notebook für die Dauer des Trainings, sowie unsere legendäre Rund-um-sorglos-Verpflegung mittags, abends und in allen Pausen.

 

TRAININGS DETAILS
PostgreSQL Fundamentals: 4. Juni / 10. September / 19. November 2019
PostgreSQL Advanced: 6. August / 22. Oktober / 3. Dezember 2019

 

Weitere Informationen & Anmeldung
www.netways.de/trainings
training@netways.de

Julia Hornung
Julia Hornung
Senior Marketing Manager

Julia ist seit Juni 2018 Mitglied der NETWAYS Family. Vor ihrer Zeit in unserem Marketing Team hat sie als Journalistin und in der freien Theaterszene gearbeitet. Ihre Leidenschaft gilt gutem Storytelling, klarer Sprache und ausgefeilten Texten. Privat widmet sie sich dem Klettern und ihrer Ausbildung zur Yogalehrerin.

Veranstaltungen

Tue 27

GitLab Training | Online

October 27 @ 09:00 - October 28 @ 17:00
Tue 27

Graylog Training | Online

October 27 @ 09:00 - October 28 @ 17:00
NETWAYS Headquarter | Nürnberg
Nov 04

Vorstellung der Monitoring Lösung Icinga 2

November 4 @ 10:30 - 11:30
NETWAYS Headquarter | Nürnberg
Nov 24

Elastic Stack Training | Online

November 24 @ 09:00 - November 26 @ 17:00
Dec 01

Foreman Training | Nürnberg

December 1 @ 09:00 - December 2 @ 17:00
NETWAYS Headquarter | Nürnberg