Quiz: Tiefes PostgreSQL – Teil 2

Du kanntest CROSS JOIN, oder?

Hat dich JOIN ALL an eine andere Operation denken lassen, UNION ALL?

Die Antwort hier ist JOIN ALL ❌.

Die korrekten JOIN‑Typen sind:

• INNER JOIN (Standard‑JOIN)
• LEFT JOIN (oder LEFT OUTER JOIN)
• RIGHT JOIN (oder RIGHT OUTER JOIN)
• FULL JOIN (oder FULL OUTER JOIN)
• CROSS JOIN (das kartesische Produkt, sofern ein späteres WHERE‑Klausel es filtert)

Während SERIAL noch häufig verwendet wird, ist die SQL Standard‑Methode:

id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

• id INT IDENTITY(1,1) ist SQL Server Syntax.
• id INT AUTO_INCREMENT ist MySQL Syntax.
• id INTEGER AUTO_INCREMENT PRIMARY KEY ist MySQL Syntax.

Das hat mehrere Vorteile gegenüber SERIAL:

• Es ist SQL‑Standard‑konform
• Bessere Handhabung des Sequenz‑Eigentums
• Klarere Semantik bei manuellen Einfügungen
• Funktioniert besser mit Replikation

SERIAL ist eigentlich nur eine Kurzschreibweise zum Erzeugen einer Sequenz und zum Setzen eines Default‑Werts, was bei Dumps und Restores zu Problemen führen kann.

Beispiele für PostgreSQL‑Erweiterungen zu ANSI SQL:

• RETURNING‑Klausel.
• ILIKE für case‑insensitive Vergleiche.
• Array‑Typen und -Operationen.
• JSON/JSONB‑Typen.

Der ursprüngliche ANSI‑SQL‑Standard enthält:

• WITH (Common Table Expressions).
• LIKE für Mustervergleich.
• LATERAL‑Joins.
• Fensterfunktionen.

Wenn hier ein Fehler auftritt:

• Die gesamte Transaktion wird zurückgerollt
• Es werden keine Änderungen committed
• Die Transaktion wird als fehlgeschlagen markiert

Um teilweise zu committen:

BEGIN;
UPDATE ...;
SAVEPOINT my_save;
UPDATE ...; -- Error
ROLLBACK TO my_save;
COMMIT;

Ein Hash-Join ist mit einer Gleichheitsbedingung (=) im ON‑Clause möglich.

• = ist die zentrale Prädikatform für Hash-Joins
• > und >= sind keine Hash‑Join‑Prädikate
• HASH JOIN ist keine gültige Syntax in PostgreSQL

Der Planner kann trotzdem einen Nested‑Loop‑ oder Merge‑Join wählen, abhängig von Tabellengröße, Statistiken, Indizes und Speichereinstellungen.

Für mich ist das Überraschende, dass ein mehrspaltiger Index sogar dann verwendet werden kann, wenn die Spaltenreihenfolge im Abfrage‑Text nicht mit der Indexdefinition übereinstimmt.

Note: Bei B‑Tree‑Indizes ist die führende Spalte entscheidend. Da diese Abfrage nach grade_level und last_name filtert, passt ein Index, der mit grade_level beginnt und danach last_name enthält, gut.

Je schneller SQL die wenigsten Zeilen auswählen kann, desto besser ist die Performance.

Zum Beispiel, wenn Sie eine Tabelle task haben und wissen, dass status selektiver ist als created_at, könnten Sie einen Index wählen, der mit status beginnt, für eine Abfrage wie status = 'done' AND created_at > '2024-01-01'. Die Reihenfolge der Bedingungen im Text der WHERE‑Klausel ist nicht entscheidend; wichtig sind die Spaltenreihenfolge des Index und die Form der Prädikate.

Ein B+Tree‑Index kann verwendet werden für:

• Die führende Spalte oder ein linksseitiges Präfix der Spalten.
• Operatoren wie =, >, <, BETWEEN und IN, je nach Abfrageform.
• Präfix‑Abgleich, LIKE 'prefix%'.

Danke an u/mwdb2 für den Hinweis auf einen früheren Fehler in dieser Erklärung!

In PostgreSQL:

• Doppelte Anführungszeichen (") sind für Bezeichner (Tabellennamen, Spaltennamen)
• Einfache Anführungszeichen (') sind für Zeichenkettenliterale

Best Practices:

• Vermeide gemischte Groß‑ und Kleinschreibung oder Leerzeichen in Namen
• Verwende snake_case für Bezeichner
• Verwende zitierte Bezeichner nur, wenn es unbedingt nötig ist

PostgreSQL verwendet doppelte Anführungszeichen für das Quotieren von Bezeichnern:

• Doppelte Anführungszeichen "..." für Bezeichner (Spaltennamen, Tabellennamen)
• Einzelne Anführungszeichen '...' für Zeichenkettenliterale
• Die Form first.name wird als table_name.column_name interpretiert

Ohne Anführungszeichen würde ein Punkt im Spaltennamen als Trennzeichen für Schema/Tabellen angesehen! Außerdem werden alle nicht quotierten Bezeichner standardmäßig in Kleinbuchstaben umgewandelt.

Um Überraschungen zu vermeiden, sollte man snake_case verwenden und Sonderzeichen meiden.

Das hier ist knifflig! Es enthält ungefähr 6 Fallen, darunter:

1. Hohe‑Level‑Beschreibung + Syntax‑Fragmente. Testet, wie du unvollständige Informationen anpasst und zusammensetzt.
2. Erfordert vertrautes Wissen über die TABLESAMPLE‑Syntax, da keine der TABLESAMPLE‑Optionen die korrekte Syntax verwendet! (Unheimlich! Ich weiß! Weiter so, ich glaube an dich!!! ❤️)
3. Das ROW_NUMBER() OVER (ORDER BY RAND()) und ORDER BY RANDOM() > 10 sind weitere falsche Fährten. Sie sehen aus, als könnten sie korrekt sein, sind es aber nicht. RAND() gibt es nicht. Und RANDOM() > 10 ist ein boolescher Ausdruck, der immer false ist, weil RANDOM() immer im Bereich 0.0‑1.0 liegt. Ein weiteres Problem: ORDER BY RANDOM() > 10 würde nach einem booleschen Wert sortieren, nicht nach einer Zufallszahl. Aber das > 10 wirkt wie ein Prozentsatz, oder?
4. BERNOULLI ist gültig, SAMPLETABLE hingegen nicht.
5. WHERE RANDOM() >= 0.1 ist ein Trick! RANDOM() liefert einen Wert im Bereich 0.0 <= x < 1.0, sodass >= 0.1 etwa 90 % der Zeilen zurückgibt, nicht 10 %.

Bezüglich der TABLESAMPLE‑Methode unterstützt PostgreSQL zwei Stichprobenverfahren:

• BERNOULLI: Jede Zeile hat gleiche Wahrscheinlichkeit
• SYSTEM: Block‑basiertes Sampling (schneller, aber weniger zufällig)

Um mehr über Sampling zu erfahren, sieh dir diesen Artikel von Render an.

Example:

-- Sample 10% of rows
SELECT * FROM students TABLESAMPLE BERNOULLI (10);

-- For repeatable results, use REPEATABLE:
SELECT * FROM students
TABLESAMPLE BERNOULLI (10) REPEATABLE (42);

Ebenfalls erwähnenswert, dass TABLESAMPLE berichten zufolge nicht sehr zufällig ist. Wenn du eine noch zufälligere Stichprobe fester Größe brauchst, erwäge ORDER BY RANDOM() LIMIT 10. Beachte jedoch, dass das 10 Zeilen zurückgibt, nicht 10 %, und bei großen Tabellen langsam sein kann.

Es gibt auch einige Drittanbieter‑Erweiterungen, die bessere zufällige Stichprobenmethoden bieten, aber das liegt hier außerhalb des Rahmens! 😅

Dieser Trick heißt Partial Index.

Sie sind nützlich, um Speicherplatz zu sparen und Abfragen zu beschleunigen, wenn typischerweise nur ein Teil der Zeilen abgefragt wird.

Postgres verwendet den Index nur, wenn die Bedingung status = 'active' im Query vorkommt.

Erinnerung: Postgres unterstützt keine „query hints“ wie andere RDBMSs. Der Abfrageplaner entscheidet, wann ein passender Index verwendet wird.

Ok, das war ein bisschen knifflig. Der Haken ist, dass x = NULL niemals Zeilen matcht und IMMER genau eine Zeile mit einem Count von 0 zurückgibt.

Da NULL ein unbekannter Wert ist, kann er mit nichts verglichen werden, nicht einmal mit sich selbst.

Der richtige Weg, um NULL‑Werte zu prüfen, ist IS NULL.

Beispiel:

postgres=# SELECT count(*)
  FROM students
  WHERE age = null;
count
-------
    0
(1 row)

postgres=#
postgres=# SELECT count(*)
  FROM students
  WHERE age is null;
count
-------
    3
(1 row)

EXPLAIN ANALYZE führt die Abfrage tatsächlich aus! Seien Sie vorsichtig beim Analysieren einer Schreiboperation, insbesondere bei UPDATE, DELETE‑ und INSERT‑Anweisungen. Möglicherweise müssen Sie nach jedem EXPLAIN ANALYZE Aufräumarbeiten durchführen, um reproduzierbare Ergebnisse zu erhalten.

Best Practices:

• Verwenden Sie EXPLAIN (ohne ANALYZE) für UPDATE/DELETE/INSERT
• Packen Sie Änderungen in Transaktionen:

BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;