NETWAYS Blog

PostgreSQL Einstieg unter openSUSE-Leap 15.2

by Johannes Carraro | Jan 21, 2021 | PostgreSQL

da die meisten User sich zwar mit MySQL oder MariaDB auskennen, werde ich heute mal auf PostgreSQL eingehen, wie man dieses Paket installiert und ein paar Kommandos zur Verwaltung einer Datenbank.
PostgeSQL ist von den Kommandos ähnlich wie zum Beispiel MariaDB. Sie wird auch als ein freies, objektrelationales Datenbankmanagementsystem (ORDBMS) betitelt, somit auch Opensource ist.

Installation unter openSUSE-Leap 15.2:

Pakete sind in den offiziellen Repositories zu finden, die bei dem Setup von openSUSE-Leap gesetzt werden.

# sudo zypper in postgresql postgresql-server

Da da der Dienst von PostgreSQL nicht automatisch gestartet wird, wie z.B bei Debian basierten Linux-Distributionen muß dieser noch gestartet werden

# sudo systemctl start postgresql

Am besten prüft man nochmal ob der Dienst auch fehlerfrei läuft

# sudo systemctl status postgresql

● postgresql.service - PostgreSQL database server
 Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; vendor preset: disabled)
 Active: active (running) since Tue 2020-11-03 08:31:17 UTC; 4h 23min ago
 Main PID: 1460 (postgres)
 Tasks: 8
 CGroup: /system.slice/postgresql.service
 ├─1460 /usr/lib/postgresql12/bin/postgres -D /var/lib/pgsql/data
 ├─1461 postgres: logger 
 ├─1463 postgres: checkpointer 
 ├─1464 postgres: background writer 
 ├─1465 postgres: walwriter 
 ├─1466 postgres: autovacuum launcher 
 ├─1467 postgres: stats collector 
 └─1468 postgres: logical replication launcher

Nov 03 08:31:15 opensuse-leap systemd[1]: Starting PostgreSQL database server...
Nov 03 08:31:15 opensuse-leap postgresql-script[1437]: Initializing PostgreSQL 12.4 at location /var/lib/pgsql/data
Nov 03 08:31:17 opensuse-leap postgresql-script[1437]: 2020-11-03 08:31:17.186 UTC [1460]LOG: starting PostgreSQL 12.4 on x86_64-suse-linux-gnu, compiled by gcc (SUSE L>
Nov 03 08:31:17 opensuse-leap postgresql-script[1437]: 2020-11-03 08:31:17.187 UTC [1460]LOG: listening on IPv6 address "::1", port 5432
Nov 03 08:31:17 opensuse-leap postgresql-script[1437]: 2020-11-03 08:31:17.187 UTC [1460]LOG: listening on IPv4 address "127.0.0.1", port 5432
Nov 03 08:31:17 opensuse-leap postgresql-script[1437]: 2020-11-03 08:31:17.193 UTC [1460]LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
Nov 03 08:31:17 opensuse-leap postgresql-script[1437]: 2020-11-03 08:31:17.204 UTC [1460]LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
Nov 03 08:31:17 opensuse-leap postgresql-script[1437]: 2020-11-03 08:31:17.221 UTC [1460]LOG: redirecting log output to logging collector process
Nov 03 08:31:17 opensuse-leap postgresql-script[1437]: 2020-11-03 08:31:17.221 UTC [1460]HINT: Future log output will appear in directory "log".
Nov 03 08:31:17 opensuse-leap systemd[1]: Started PostgreSQL database server.

Da wir jetzt wissen, das der Dienst ordnungsgemäß läuft, können wir loslegen, mit dem anlegen von der Datenbank und Tabellen.

PostgreSQL wird vom System-User postgres verwaltet, der alle Rechte hat, um z.B. Datenbanken oder auch Datenbankbenutzer anzulegen. Da dieser User ohne Passwort konfiguriert ist, was unter MariaDB oder MySQL teilweise auch so ist, sollte man dem User sicherheitstechnisch ein Passwort konfigurieren.

# sudo passwd postgres

Passwort nach Aufforderung eingeben und erneut wiederholen.

Unter PostgreSQL gibt es genauso ein Shell wie bei z.B. MySQL und in diese kommt man wie folgt:

# sudo -u postgres psql

Um diese Shell wieder zu verlassen

\q

eingeben

Anlegen vom User der Datenbanken verwalten darf

 # sudo sudo -u postgres createuser -P -d NUTZERNAME

Der Schalter -P <password> -d <create databases>

Anlegen von einer Datenbank

# sudo -u postgres createdb -O username DATABASENAME

Löschen einer Datenbank

# sudo -u postgress dropdb DATABASE

In der postgresql Shell sieht das anders aus

postgres=# CREATE DATABASE my_music;

Wechseln zwischen den Datenbanken

\c DATENBANK

Ein Tabelle mit den Spalten anlegen

postgres=# CREATE TABLE cds ( id int, interpreter varchar(100), album varchar(100), release date );

Daten in die Tabelle der Datenbank schreiben

INSERT INTO cds VALUES ( 1, 'Pretty Maids', 'Future World', '1987-04-12' );

Ausgeben der Daten

my_music=# select * from cds;
 id | interpreter  |       album        |  release   
----+--------------+--------------------+------------
  1 | Pretty Maids | Future World       | 1987-04-12
  2 | Metallica    | Ride the Lightning | 1984-04-12
(2 rows)

Als kleine Einführung in PostgreSQL sollte das erst mal ausreichen, für mehr Wissen darüber bieten wir von NETWAYS auch eine PostgreSQL Schulung an.

Dann viel Spaß bei ausprobieren von PostgreSQL auf openSUSE.

Johannes Carraro

Senior Systems Engineer

Bevor Johannes bei NETWAYS anheuerte war er knapp drei Jahre als Systemadministrator in Ansbach tätig. Seit Februar 2016 verstärkt er nun unser Team Operations als Senior Systems Engineer. In seiner Freizeit spielt Johannes E-Gitarre, bastelt an Linux Systemen zuhause herum und ertüchtigt sich beim Tischtennisspielen im Verein, bzw. Mountainbiken, Inlinern und nicht zuletzt Skifahren.

Read more from Johannes and meet the Team

Encoding “leicht gemacht” mit PostgreSQL

by Lennart Betz | Dec 10, 2020 | PostgreSQL

In der Urzeit der Computerei passierte das alles in den USA. Und die Vereinigten Amerikaner haben seinerzeit wie üblich nicht über den Tellerrand geschaut, sondern ihren Zeichensatz an ihre Sprache angepasst (ASCII). Irgendwann “durften” dann auch die Alliierten ran, und die Probleme kamen auf… dieser Zeit verdanken wir ISO-8859-1, WIN-1252 und wie sie alle heißen. Mittlerweile gibt es aber ja seit 30 Jahren Unicode, die Probleme sind also doch Schnee von gestern. Richtig?

PostgreSQL ist – aus guten Gründen – ein beliebtes Ziel für Migrationen. Und PostgreSQL ist von vorne bis hinten auf UTF-8 aufgestellt. Leider hat man es aber oft mit Altsystemen, Altdaten und Gammelcode zu tun, die irgendwie übernommen werden müssen.

So bestehen unfassbare Mengen an altem Perl-, Python-, PHP-, VBA-, … Code, der für frühe Access-, MySQL-, MS-SQL-, Oracle-, Sybase oder was auch immer für Datenbanken geschrieben wurde und mit Unicode nichts am Hut hat. (Einige dieser Systeme sind bis heute nicht wirklich gut darin…)

Gerne sind wir auch gezwungen, “Pseudo-CSV” aus M$ Excel zu importieren (warum ist es “normal”, comma separated values mit Semikolon zu separieren?!?), und Microsoft tut sich bis heute immer noch schwer mit UTF-8.

~$ file importtest.win-1252.csv importtest.win-1252.csv: ISO-8859 text, with CRLF line terminators

Betreiber solcher Software haben sich üblicherweise aus der Affäre gezogen, indem sie die Datenbank, auch bei Updates, immer wieder im alten, überholten Encoding betrieben haben. Jetzt steht also die Umstellung auf PostgreSQL an, und beim ersten Testimport von Altdaten kommt diese Meldung:

blog=# \COPY csvimport FROM importtest.win-1252.csv WITH (DELIMITER ';', FORMAT CSV); ERROR: invalid byte sequence for encoding "UTF8": 0xfc CONTEXT: COPY csvimport, line 1 blog=#

Viele Entwickler*innen werden jetzt reflexhaft ihre bewährte Methode benutzen wollen, und ja, ich kann, auch wenn mein DB-Cluster anders initialisiert wurde, genau das auch tun:

blog=# \h CREATE DATABASE Command: CREATE DATABASE Description: create a new database Syntax: CREATE DATABASE name [ [ WITH ] [ OWNER [=] user_name ] [ TEMPLATE [=] template ] [ ENCODING [=] encoding ] [ LOCALE [=] locale ] [ LC_COLLATE [=] lc_collate ] [ LC_CTYPE [=] lc_ctype ] [ TABLESPACE [=] tablespace_name ] [ ALLOW_CONNECTIONS [=] allowconn ] [ CONNECTION LIMIT [=] connlimit ] [ IS_TEMPLATE [=] istemplate ] ]

siehe auch hier.

Ah, da ist es ja, [ ENCODING [=] encoding ] als “Weg des geringsten Widerstands”. Dann ist das doch normal, oder nicht?! Ja, kann man so machen, ist dann nur kacke und man hat eine Top-Gelegenheit verpasst! Besser ist es, die Gelegenheit zu nutzen und hier und jetzt den Weg auf UTF-8 einzuleiten. Dafür reicht es, das client_encoding für den Import anzupassen:

blog=# SHOW client_encoding ; client_encoding ----------------- UTF8 (1 row) blog=# SET client_encoding TO 'win-1252'; blog=# \COPY csvimport FROM importtest.win-1252.csv WITH (DELIMITER ';', FORMAT CSV); blog=#

Gut! Das hat schonmal geklappt. Dann schauen wir doch mal, wie das in der Tabelle aussieht:

Pfui bah! Genau das wollten wir doch vermeiden!!!

Alles ist gut, unser client_encoding ist ja noch kaputt:

Und wenn das Refakturieren aller beteiligten Komponenten gerade nicht opportun ist, kann das client_encoding z.B. per Rolle (User) gesetzt werden:

blog=# CREATE ROLE legacy_import LOGIN; blog=# ALTER ROLE legacy_import SET client_encoding TO 'win-1252';

Alles, was von dieser Rolle nun geschrieben wird, verarbeitet PostgreSQL nun intern zu UTF-8. Alles, was die Rolle liest, wird in WIN-1252 umgewandelt. Alle modernen Clients können aber mit Unicode arbeiten, so dass einer Transition statt einer reinen Migration jetzt “nur noch” Code im Wege steht, keine Daten mehr.

P.S.: Es gibt natürlich noch weitere Möglichkeiten, das Encoding pro Client festzulegen, z.B. die Umgebungsvariable PGCLIENTENCODING.

Über den Autor:

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.

Lennart Betz

Senior Consultant

Der diplomierte Mathematiker arbeitet bei NETWAYS im Bereich Consulting und bereichert seine Kunden mit seinem Wissen zu Icinga, Nagios und anderen Open Source Administrationstools. Im Büro erleuchtet Lennart seine Kollegen mit fundierten geschichtlichen Vorträgen die seinesgleichen suchen.

Read more from Lennart and meet the Team

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

by Philipp Dorschner | Oct 8, 2020 | Linux, Trainings, PostgreSQL, Datenbanken

Wieder einmal PostgreSQL Schlung (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:

Schneller LIKEn

by Gunnar Bluth | Nov 28, 2019 | NETWAYS, PostgreSQL

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

Milano – PostgreSQL Conference Europe 2019

by Alexander Stoll | Oct 18, 2019 | NETWAYS, PostgreSQL

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.

#PostGIS #PgConfEU Slides now online here https://t.co/YgDVHC420V https://t.co/ze26KYWiZh

— Paul Ramsey (@pwramsey) October 16, 2019

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.

Applications developers are certainly enjoying ORM talk by Louise Grandjonc @louisemeta pic.twitter.com/9iE4MxRelk

— PGConf Europe (@pgconfeu) October 17, 2019

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

Consultant

Alex hat seine Ausbildung zum Fachinformatiker für Systemintegration bei NETWAYS Professional Services abgeschlossen und ist nun im Consulting tätig. Vereinzelt kommt es auch vor das er an Programmierprojekten mitarbeitet. Auch privat setzt er sich sehr viel mit Informationstechnologie auseinander, aber jenseits davon ist auch viel Zeit für Fußballabende, Handwerkerprojekte und das ein oder andere Buch.

Read more from Alexander and meet the Team