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, bewertet mit ts_rank und indexiert mit GIN; Stemming und Stoppwörter werden dabei beherrscht, Tippfehler und Synonyme aber 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.
-
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.
-
Writing an upsert with INSERT ... ON CONFLICT
INSERT ... ON CONFLICT (columns) DO UPDATE SET ... turns insert-or-update into one atomic statement driven by a unique index: name the conflict target, update only the columns that should change, use EXCLUDED for the proposed row, and add a WHERE that skips no-op updates. MERGE covers the multi-branch cases ON CONFLICT cannot express.
-
N+1 queries: detecting them by counting and fixing them by batching
Loading N parent rows and then touching a lazy relationship on each emits N+1 queries; the cost grows with data, not code, so it passes small-fixture tests. Detect it by asserting query counts per request, make unwanted lazy loads raise, and fix it with joins for to-one relations and IN-batched second queries for collections.
-
Normalising to third normal form and choosing when to denormalise
Normal forms remove repeating groups and facts stored in more than one place; third normal form means every non-key column depends on the key and nothing else. Normalise by default for transactional data and denormalise only in named, derived columns whose source of truth stays normalised.
-
Declarative constraints in PostgreSQL: CHECK, UNIQUE and foreign keys with ON DELETE
Constraints make the database reject invalid states for every writer, not just the application: CHECK for per-row rules, NOT NULL for required values, UNIQUE for identity, and foreign keys with an explicit ON DELETE action. Choosing the referential action and indexing the referencing column are the two decisions most often skipped.
-
Finding the statements that cost the most with pg_stat_statements
pg_stat_statements aggregates call counts, execution time and block counts per normalised statement across the whole server. Load it through shared_preload_libraries, rank by total_exec_time and separately by calls, compare mean with maximum, read the buffer columns, then take the top statements to EXPLAIN ANALYZE and compare before and after by queryid.
-
Window functions: aggregates without collapsing rows
A window function computes a value over a set of rows related to the current row (OVER with PARTITION BY and ORDER BY) while keeping every input row, which expresses running totals, rankings, top-N per group and previous-row comparisons in one pass; the frame clause decides which rows the function sees.
Maschinenlesbar: JSON