Dan Levy

Quiz: Tiefes PostgreSQL – Teil 2

Bist du ein SQL‑Hartnäckiger oder ein SQL‑Weichling?

Teil 2 von 2. Zurück zu Teil 1

Willkommen zu Teil 2 meines Deep Postres Quiz!

Dieses Quiz deckt eine Mischung aus bekannten und weniger bekannten PostgreSQL‑Features und Stolperfallen ab: JOIN, NULL, ANSI SQL, Transaktionen, Interna, Indexierung, Data Sampling … und mehr!

Viel Glück! 🍀

1. 

Aufwärmen: Joins

Welcher Ausdruck ist ❌ kein gültiger PostgreSQL-JOIN-Ausdruck?

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, das eine spätere WHERE-Klausel einschränken kann)

2. 

Identitätsspalten

Wie lautet die (SQL Standard)-Syntax für eine automatisch inkrementierende IDENTITY‑Spalte in PostgreSQL (v10+)?

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.

3. 

ANSI SQL

Welche Klausel dokumentiert PostgreSQL als Erweiterung des SQL-Standards?

PostgreSQL dokumentiert RETURNING als Erweiterung des SQL-Standards. Auch ILIKE ist eine PostgreSQL-Erweiterung.

Array- und JSON-Unterstützung sind nicht pauschal außerhalb des Standards: SQL standardisiert Funktionen in beiden Bereichen. PostgreSQL bietet zusätzlich eigene Typen und Operatoren wie jsonb.

In verschiedenen Ausgaben des SQL-Standards eingeführte Funktionen sind:

  • WITH (Common Table Expressions)
  • LIKE für Mustervergleiche
  • LATERAL-Joins
  • Fensterfunktionen

4. 

Transaktionen

Außer bei “Hängt von Nebeneffekten ab” Was passiert nach einem Fehler?

BEGIN;
UPDATE students
  SET status = 'alumni'
  WHERE grade = 12;
UPDATE students
  SET graduation_date = CURRENT_DATE
  WHERE grade = 12;
UPDATE students
  SET grade = 'GRADUATED'; -- 🚨 Error: type mismatch
COMMIT;

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;

5. 

SQL-Interna

Welche ON-Bedingung kann PostgreSQL für einen HASH JOIN verwenden?

SELECT *
FROM students a
JOIN grades b
ON /* What goes here? ⁉ */;

Ein Hash-Join ist mit einer Gleichheitsbedingung (=) in der ON-Klausel 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.

6. 

Indexierung

Welcher Index ist für diese Abfrage besser geeignet?

-- Query:
SELECT * FROM students
WHERE grade_level = 42
  AND last_name IN ('Levy', 'Smith');

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.

Hinweis: 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!

7. 

Zitieren 1

Was ist korrekt für case-sensitive Spaltennamen?

CREATE TABLE grades (
  "Student Name" VARCHAR(100),
  'Student Grade' INTEGER
);

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

8. 

Quotierung 2

Wie geben Sie einen Spaltennamen mit einem Punkt an?

CREATE TABLE teachers (
  id INT GENERATED BY DEFAULT AS IDENTITY,
  first.name TEXT,
  -- How do you include the column: first.name?
  salary NUMERIC
);

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.

9. 

Datenstichprobe

Welche Syntax ist gültig, um im Mittel zufällig 10 % der Studenten zu entnehmen?

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.

Beispiel:

-- 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! 😅

10. 

Indizierung

Was bewirkt diese Beschwörung?

CREATE INDEX idx_active_students
ON students(last_name)
WHERE status = 'active';

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.

Der Abfrageplaner kann diesen Index berücksichtigen, wenn er beweisen kann, dass die Abfragebedingungen status = 'active' implizieren. Der Wortlaut muss nicht immer identisch sein, aber die Schlussfolgerungen des Planers sind begrenzt; ein Parameter kann den Nachweis zur Planungszeit verhindern. Ein geeigneter Index wird nicht zwangsläufig ausgewählt.

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

11. 

NULL-Verhalten

Wie viele Zeilen wird das zurückgeben?

SELECT COUNT(*)
FROM students
WHERE grade = NULL;

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)

12. 

Leistung

Was macht EXPLAIN ANALYZE bei UPDATE‑Anweisungen?

EXPLAIN ANALYZE
UPDATE students
SET grade = grade + 1
WHERE status = 'active';

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;

Gut gemacht! Du hast in mehreren Bereichen von PostgreSQL tief gegraben! 🐘

Ich hoffe, du hast etwas Neues gelernt oder zumindest eine Punktzahl, mit der du prahlen kannst! 🏆

Mehr Nervenkitzel gefällig? Sieh dir meine Quiz‑Sammlung für endlosen* Spaß an!

Loading comments...