Thema: sql
-
Volltextsuche in PostgreSQL mit tsvector
PostgreSQL wandelt Text mithilfe einer Sprachkonfiguration in einen tsvector aus normalisierten Lexemen um, gleicht ihn mit tsquery ab, rankt mit ts_rank und indexiert ihn mit GIN; es behandelt Stemming und Stoppwörter, aber Tippfehler oder Synonyme nicht von Haus aus.
-
Abgeleitete Daten in PostgreSQL speichern: generierte Spalten versus materialisierte Views
Eine generierte Spalte leitet einen Wert pro Zeile allein aus dieser Zeile ab, entweder virtuell (beim Lesen berechnet) oder gespeichert (beim Schreiben berechnet), und darf nur unveränderliche Ausdrücke verwenden; eine materialisierte View speichert das Ergebnis einer beliebigen Abfrage und ist nur so aktuell wie ihr letztes REFRESH. Gespeicherte generierte Spalten für Normalisierung je Zeile verwenden, die indiziert werden soll, materialisierte Views für teure Aggregate, die nachhinken dürfen, und eine gepflegte Zusammenfassungstabelle, wenn keines von beidem passt.
-
Upsert, row limits and identifier quoting across PostgreSQL, MySQL, SQLite and SQL Server
PostgreSQL's ON CONFLICT, MySQL's ON DUPLICATE KEY UPDATE, SQLite's PostgreSQL-style ON CONFLICT and SQL Server's MERGE are three families of upsert syntax, LIMIT/OFFSET has at least three shapes, and a quoted identifier means double quotes, backticks or square brackets depending on the engine.
-
NULL in SQL: dreiwertige Logik und ihre Fallstricke
NULL bedeutet unbekannt, daher ergeben Vergleiche mit NULL unbekannt statt wahr oder falsch; WHERE verwirft Zeilen mit dem Ergebnis unbekannt, NOT IN mit einem NULL findet nichts, und Aggregatfunktionen überspringen NULLs. IS NULL, IS DISTINCT FROM und COALESCE gezielt einsetzen.
-
Common Table Expressions und rekursive Abfragen mit WITH
WITH benennt eine Unterabfrage für den Rest einer Anweisung; WITH RECURSIVE wertet einen nicht-rekursiven Term aus und wiederholt danach einen rekursiven Term, bis er keine neuen Zeilen mehr liefert, wodurch sich Bäume und Graphen beliebiger Tiefe in einer einzigen Abfrage durchlaufen lassen. Einmal verwendete, nebenwirkungsfreie CTEs werden standardmässig in die äussere Abfrage eingefaltet, sofern nicht MATERIALIZED angegeben wird, und SEARCH- sowie CYCLE-Klauseln regeln Reihenfolge und Schleifen.
-
Planer-Statistiken in PostgreSQL: Statistikziele, korrelierte Spalten und Fehlschätzungen
Der Planer schätzt Zeilenzahlen aus Statistiken pro Spalte, die ANALYZE erhebt (häufigste Werte, Histogramme, Anzahl unterschiedlicher Werte) und geht von unabhängigen Spalten aus; Fehlschätzungen entstehen durch veraltete Statistiken, schiefe Spalten mit zu wenigen Einträgen, korrelierte Spalten und Ausdrücke. Das Statistikziel gezielt für einzelne Spalten erhöhen, erweiterte Statistiken für korrelierte Spalten oder Ausdrücke anlegen, neu analysieren und geschätzte mit tatsächlichen Zeilen vergleichen.
-
Einen PostgreSQL-Abfrageplan mit EXPLAIN ANALYZE lesen
EXPLAIN zeigt den vom Planer gewählten Baum mit geschätzten Kosten; EXPLAIN ANALYZE führt die Abfrage aus und ergänzt tatsächliche Zeiten und Zeilenzahlen. Geschätzte mit tatsächlichen Zeilen vergleichen, den Knoten mit der grössten tatsächlichen Zeit finden und auf sequenzielle Scans über grosse Tabellen sowie fehleingeschätzte Joins prüfen.
-
NULL in SQL: dreiwertige Logik und ihre Fallen
NULL bedeutet unbekannt, darum ergibt jeder Vergleich mit NULL weder wahr noch falsch, sondern unbekannt; WHERE verwirft unbekannte Zeilen, NOT IN mit einem NULL trifft nichts, Aggregate überspringen NULL, und ein Unique-Constraint lässt mehrere NULL zu. IS NULL, IS DISTINCT FROM und COALESCE bewusst einsetzen.
-
Savepoints und der abgebrochene Transaktionszustand in PostgreSQL
Nach jedem Fehler innerhalb eines Transaktionsblocks weist PostgreSQL jeden weiteren Befehl zurück, bis der Block zurückgerollt wird; ein vor einer riskanten Anweisung gesetzter Savepoint erlaubt es, mit ROLLBACK TO SAVEPOINT nur diesen Teil zu verwerfen und fortzufahren. Savepoints gezielt und sparsam einsetzen, und Fehlerbehandlungen sollen zurückrollen statt auf derselben Verbindung erneut zu versuchen.
-
Cursor-Pagination statt Offsets: Seiten, die bei Änderungen stabil bleiben
Offset-Pagination lässt die Datenbank alle übersprungenen Zeilen trotzdem berechnen und verschiebt Seiten, sobald dazwischen eingefügt oder gelöscht wird; Cursor- oder Keyset-Pagination fragt «die nächsten 20 nach Schlüssel X», nutzt den Index und liefert jede Zeile genau einmal. Voraussetzung ist eine eindeutige Sortierung, der Preis ist der Verzicht auf Seitenzahlen.
-
Schema-Konventionen für eine neue PostgreSQL-Datenbank: Namen, Bezeichner, Zeitstempel und Text
Vor der ersten Migration eine Handvoll Konventionen festlegen: kleingeschriebene snake_case-Namen, die nie in Anführungszeichen gesetzt werden müssen, eine überall angewandte ID-Strategie, timestamptz für jeden Zeitpunkt mit created_at auf jeder Tabelle, text statt varchar(n), sowie explizite NOT-NULL- und Fremdschlüsselangaben; sie schriftlich festhalten, damit jede spätere Migration ihnen folgt.
-
Bulk-Operationen statt Schleifen pro Zeile: Roundtrips, Transaktionen und COPY
Eine Schleife, die pro Zeile ein Statement sendet, zahlt pro Zeile einen Roundtrip, ein Parsing und oft einen Commit; eine Bulk-Operation sendet die gesamte Menge in einem Statement, einem Stream oder einer Transaktion. PostgreSQLs eigene Anleitung rät, nur einmal zu committen, COPY für das Laden zu verwenden und Indizes erst nach dem Laden aufzubauen; Client-Bibliotheken bieten aus demselben Grund executemany und copy an.
-
Datenqualitätsprüfungen: Aktualität, Menge, Nullwerte und Eindeutigkeit als Mindestsatz
Vier billige Prüfungen fangen die meisten kaputten Ladeläufe: Ist die Quelle frisch genug, kam eine plausible Zeilenzahl, sind Schlüssel und Kennzahlen gefüllt, ist die erklärte Körnung eindeutig? Jede Prüfung als Abfrage formulieren, die fehlerhafte Zeilen liefert, nach dem Laden und vor dem Veröffentlichen ausführen, Warnung und Blockade trennen.
-
Auf welche Connection-Pool-Grösse relativ zur Anzahl CPU-Kerne haben sich Teams bei einem PostgreSQL-Server eingependelt, und welche Messung hat sie zu einer Änderung bewogen?
Offene Frage: Das PostgreSQL-Wiki bietet eine Formel zur Bemessung aktiver Verbindungen, die auf der Kernzahl und der effektiven Spindelzahl beruht, und der Standardwert von max_connections liegt typischerweise bei 100; auf welche Pool-Grössen sind Teams nach dem Tuning tatsächlich gekommen, wie weit lagen sie von der Formel entfernt, und welche Belege (Lock-Waits, Warteschlangenbildung am Pooler, CPU-Sättigung, Latenz) haben jede Änderung ausgelöst?
-
Soft Deletes versus Archivtabellen
Ein Soft Delete behält die Zeile mit einer Markierung deleted_at, sodass jede Abfrage sie herausfiltern muss und jede Unique-Constraint zu einem partiellen Index werden muss; eine Archivtabelle verschiebt die Zeile aus der Live-Tabelle, sodass Live-Abfragen einfach bleiben und die Historie an einem Ort liegt. Die Wahl danach treffen, wer gelöschte Daten wie oft liest, und die Entscheidung in Constraints und Views verankern statt in jeder Abfrage.
-
Ein Upsert mit INSERT ... ON CONFLICT schreiben
INSERT ... ON CONFLICT (Spalten) DO UPDATE SET ... macht aus Einfügen-oder-Aktualisieren eine einzige atomare, von einem Unique-Index gesteuerte Anweisung: das Konfliktziel benennen, nur die Spalten aktualisieren, die sich ändern sollen, EXCLUDED für die vorgeschlagene Zeile verwenden und ein WHERE ergänzen, das wirkungslose Aktualisierungen überspringt. MERGE deckt die mehrzweigigen Fälle ab, die ON CONFLICT nicht ausdrücken kann.
-
N+1-Abfragen: erkennen durch Zählen und beheben durch Batching
N übergeordnete Zeilen zu laden und dann bei jeder eine Lazy-Relationship zu berühren, erzeugt N+1 Abfragen; die Kosten wachsen mit den Daten, nicht mit dem Code, daher besteht es Tests mit kleinen Testdaten. Erkennen, indem die Anzahl Abfragen je Anfrage geprüft wird, unerwünschte Lazy Loads einen Fehler auslösen lassen, und beheben mit Joins für To-one-Beziehungen und per IN gebündelten zweiten Abfragen für Sammlungen.
-
Normalisierung auf die dritte Normalform und die Entscheidung, wann denormalisiert wird
Normalformen entfernen sich wiederholende Gruppen und Fakten, die an mehr als einem Ort gespeichert sind; die dritte Normalform bedeutet, dass jede Nichtschlüsselspalte vom Schlüssel abhängt und von nichts sonst. Für transaktionale Daten standardmässig normalisieren und nur in benannten, abgeleiteten Spalten denormalisieren, deren massgebliche Quelle normalisiert bleibt.
-
Deklarative Constraints in PostgreSQL: CHECK, UNIQUE und Fremdschlüssel mit ON DELETE
Constraints lassen die Datenbank ungültige Zustände für jede schreibende Seite ablehnen, nicht nur für die Anwendung: CHECK für Regeln je Zeile, NOT NULL für Pflichtwerte, UNIQUE für Identität, und Fremdschlüssel mit einer expliziten ON-DELETE-Aktion. Die Wahl der referenziellen Aktion und die Indizierung der referenzierenden Spalte sind die beiden Entscheidungen, die am häufigsten übersprungen werden.
-
Die teuersten Statements mit pg_stat_statements finden
pg_stat_statements aggregiert Aufrufanzahlen, Ausführungszeit und Blockanzahlen je normalisiertem Statement über den gesamten Server. Über shared_preload_libraries laden, nach total_exec_time und separat nach calls sortieren, Mittelwert mit Maximum vergleichen, die Buffer-Spalten lesen und dann die Top-Statements per EXPLAIN ANALYZE untersuchen sowie vorher und nachher anhand der queryid vergleichen.
Maschinenlesbar: JSON