Thema: postgresql
-
PostgreSQL-Erweiterungen verwalten: installieren, versionieren, aktualisieren und dumpen
Eine Erweiterung bündelt SQL-Objekte und oft eine Shared Library unter einem Namen mit einer Control-Datei und versionierten Skripten; CREATE EXTENSION installiert sie je Datenbank, ALTER EXTENSION UPDATE wendet die Update-Skripte des Autors an, und pg_dump gibt nur die Zeile CREATE EXTENSION aus. Installierte Dateien, Katalogversion und geladene Bibliothek im Gleichschritt halten, besonders bei Paket-Upgrades und pg_upgrade.
-
Eine Append-only-Zeitreihentabelle in PostgreSQL entwerfen
Messwerte in einer nach Zeitbereich partitionierten Tabelle mit timestamptz speichern, einem zusammengesetzten Schlüssel aus Serie und Zeit, Indizes passend zum Abfragemuster (B-Tree je Serie, BRIN für zeitliche Scans über die gesamte Tabelle) sowie Aufbewahrung durch Abtrennen und Löschen von Partitionen statt DELETE; die Entscheidungen ergeben sich daraus, dass Zeilen in zeitlicher Reihenfolge ankommen und in ganzen Zeitscheiben wieder verschwinden.
-
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.
-
VACUUM, Autovacuum und Tabellen-Bloat
PostgreSQLs MVCC hinterlässt nach Updates und Deletes tote Zeilenversionen; VACUUM gibt diese frei und pflegt Statistiken sowie den Schutz vor Transaktions-ID-Wraparound. Autovacuum sollte aktiviert bleiben und für stark genutzte Tabellen abgestimmt werden.
-
Kurzlebige Datenbanken in Containern für Integrationstests
Die echte Datenbank-Engine wird für jeden Testlauf in einem Wegwerf-Container gestartet, die Daten liegen im Arbeitsspeicher, Migrationen werden einmal auf eine Vorlagendatenbank angewendet und pro Testdatei kopiert; so prüfen Tests den echten Planer, die echten Constraints und das Isolationsverhalten statt eines In-Memory-Ersatzes.
-
Transaktionsisolationsstufen in der Praxis
Read Committed, Repeatable Read und Serializable tauschen Nebenläufigkeit gegen Konsistenz; zu wissen, welche Anomalien jede Stufe zulässt, entscheidet, wann explizite Sperren oder Wiederholungsversuche nötig sind.
-
Datenbankänderungen ohne Ausfall: Expand und Contract
Ein Schema in drei einzeln auslieferbaren Schritten ändern: erweitern (neue Spalte oder Tabelle anlegen, alte behalten), migrieren (doppelt schreiben und in Häppchen nachfüllen), zusammenziehen (Altes entfernen, sobald aller Code das Neue nutzt); lange Sperren vermeiden, indem keine Tabelle in einem Statement umgeschrieben wird und lock_timeout jede Wartezeit begrenzt.
-
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.
-
JSONB-Spalten: wofür sie taugen und wann eine eigene Spalte besser ist
jsonb speichert geparstes JSON in einer binären Form, die sich mit GIN indexieren und über Containment- und Pfadoperatoren abfragen lässt; es eignet sich für spärliche, extern definierte oder tatsächlich variable Attribute. Daten mit fester Struktur, Daten, die Constraints, Fremdschlüssel oder Aktualisierungen einzelner Felder benötigen, sowie grosse, häufig geänderte Dokumente gehören in gewöhnliche Spalten.
-
Datenbank-Connection-Pooling und seine Grenzen
Jede PostgreSQL-Verbindung ist ein Prozess mit Speicherkosten; Anwendungen sollten einen kleinen, an die tatsächliche Nebenläufigkeit angepassten Pool führen, Timeouts für den Verbindungsbezug setzen und Request-Handler nie ad hoc eigene Verbindungen öffnen lassen.
-
PostgreSQL-SKIP-LOCKED-Claims mit vier gleichzeitigen Queue-Konsumenten gemessen
Vier gleichzeitige Transaktionen beanspruchten je 25 synthetische Jobs in PostgreSQL 16.15. Die zurückgegebenen 100 IDs waren eindeutig, und kein Job blieb unbeansprucht; das bestätigt eine einzelne begrenzte Claim-Phase, nicht Exactly-once-Verarbeitung oder den Ersatz durch einen Broker.
-
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.
-
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.
-
Schemamigrationen mit kurzem lock_timeout und automatischem Retry verursachen weniger Vorfälle beim Deployment als Migrationen ohne
Hypothese: Weil die meisten Formen von ALTER TABLE eine ACCESS-EXCLUSIVE-Sperre benötigen, die sich hinter jeder langen Transaktion einreiht und dabei jede spätere Abfrage blockiert, verursachen Migrationen, die mit einem lock_timeout von wenigen Sekunden und einer begrenzten Retry-Schleife ausgeführt werden, weniger und kürzere Ausfälle zur Deployment-Zeit als dieselben Migrationen mit der standardmässig unbegrenzten Wartezeit – auf Kosten einiger weniger Migrationen, die manuell erneut ausgeführt werden müssen.
-
Row-Level-Security-Richtlinien verringern mandantenübergreifende Datenlecks im Vergleich zu anwendungsseitiger Filterung
Hypothese: Mandantenfähige Dienste, die Mandantenisolation mit PostgreSQL-Row-Level-Security-Richtlinien durchsetzen (eine pro Verbindung gesetzte Mandanteneinstellung, die von der Datenbank geprüft wird), haben weniger mandantenübergreifende Offenlegungsfehler als Dienste, die jeder Abfrage ein tenant_id-Prädikat hinzufügen, weil eine Abfrage, die das Mandantenprädikat vergisst, weiterhin nur die Zeilen des aktuellen Mandanten sieht, und eine Anfrage, die den Mandanten nie setzt, nichts oder einen Fehler erhält statt der Zeilen aller Mandanten.
-
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.
-
Logische Replikation in PostgreSQL: Publikationen, Subskriptionen und der Unterschied zur Streaming-Replikation
Streaming-(physische) Replikation überträgt das Write-Ahead-Log an einen byteidentischen Standby derselben Hauptversion und Architektur; logische Replikation veröffentlicht Zeilenänderungen ausgewählter Tabellen an einen Subscriber, der eine andere Hauptversion fahren und eigene Daten halten kann. Logische Replikation benötigt eine Replikatidentität, überträgt weder DDL noch Sequenzwerte, und ihr Slot hält WAL auf dem Publisher zurück, solange der Subscriber im Rückstand ist.
-
Read-Replikas und Replikationsverzögerung: Wie veraltete Lesezugriffe aussehen und wie man sie begrenzt
Eine Streaming-Replika wendet das Log des Primärservers mit einer gewissen Verzögerung an, sodass ein Lesezugriff unmittelbar nach einem Schreibzugriff diesen möglicherweise nicht sieht. Lesezugriffe danach leiten, wie viel Veraltung der jeweilige Aufrufer toleriert, synchrone Replikationsmodi nur dort einsetzen, wo ihre Latenz akzeptabel ist, und verstehen, dass das Nachspielen des Logs lange Abfragen auf der Replika abbrechen kann.
-
Lange laufende und im Transaktions-Leerlauf befindliche Sitzungen in PostgreSQL: was sie blockieren und wie man sie begrenzt
Eine offene Transaktion hält ihre Sperren und fixiert den xmin-Horizont, sodass VACUUM Zeilen, die nach ihrem Beginn gelöscht wurden, nicht entfernen kann und DDL sich hinter ihr staut; der schlimmste Fall ist eine Sitzung im Zustand idle in transaction, weil ein Client nie committet hat. Solche Sitzungen in pg_stat_activity anhand von xact_start und state finden und pro Rolle mit idle_in_transaction_session_timeout, transaction_timeout und statement_timeout begrenzen.
-
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.
Maschinenlesbar: JSON