Postgres-Überlebensleitfaden für Startups

 (hatchet.run)
3 Punkte von GN⁺ 4 시간 전 | 1 Kommentare | Auf WhatsApp teilen
  • Basierend auf den Problemen, die Hatchet in 2 Jahren in der Produktion erlebt hat, werden schrittweise Betriebsprinzipien zusammengefasst – vom anfänglichen Schema- und Query-Design bis hin zu Massenschreibvorgängen und Tabellenmigrationen
  • Für schnelle Lesezugriffe sollten Indexe und ORDER BY aufeinander abgestimmt sein; da der Query Planner jedoch je nach Statistiken und Kosten einen sequentiellen Scan wählen kann, sollte man mit EXPLAIN ANALYZE Schätzwerte und tatsächliche Ausführung vergleichen
  • Schreibperformance und Stabilität hängen von kurzen Transaktionen, dem Sperren nur der benötigten Zeilen, CREATE INDEX CONCURRENTLY und Connection Pooling ab; Batch-Verarbeitung erhöhte in Hatchets Messungen den Durchsatz um etwa das 10-Fache
  • In Umgebungen mit sehr vielen Schreibvorgängen reichen die Standard-autovacuum-Einstellungen möglicherweise nicht aus, um dead tuples und Transaction IDs rechtzeitig aufzuräumen; bei Transaction ID Wraparound kommt es zu erheblichen Ausfallzeiten
  • Mit wachsender Größe sollte man job queues auf Basis von FOR UPDATE SKIP LOCKED, Partitionierung, Trigger und Batch-Backfills einsetzen; dafür muss man SQL außerhalb der ORM-Abstraktion direkt kontrollieren können

Zielgruppe und Grenzen von ORMs

  • Der Leitfaden richtet sich an Entwickler, die die Grundkonzepte von SQL, Zeilen, Tabellen und Indexen kennen und Probleme mit Postgres in der Produktion bewältigen müssen
  • Das Postgres-Handbuch ist umfassend, aber in Störungssituationen schwer schnell zu konsultieren; deshalb ist hier Hatchets Betriebserfahrung aus 2 Jahren komprimiert zusammengefasst
  • Die Prinzipien gelten auch bei Verwendung eines ORMs, aber mit zunehmender Größe gibt es viele Optimierungen, die nur möglich sind, wenn man die Abstraktionsschicht verlässt und SQL direkt schreibt
    • Mit Funktionen wie Prisma TypedSQL lassen sich ORM und direktes SQL gemeinsam verwenden
    • Das Go-basierte Hatchet verwendet sqlc, das ein ähnliches Verhalten bietet
    • Für Umgebungen, in denen Claude Queries schreibt, wird supabase/agent-skills empfohlen

Schwer zu änderndes Schema-Design

  • Nach dem Deployment sind Schemaänderungen am schwierigsten; deshalb sollte man zunächst Tabellen und Primärschlüssel entwerfen und dann die von der Anwendung benötigten Queries schreiben und das Design iterativ verfeinern
  • Im Designprozess wird mit den folgenden Fragen geprüft, wie eine Tabelle verwendet wird
    • Was kommt häufiger vor: Lesen oder Schreiben
    • Welche Filter werden beim Lesen am häufigsten verwendet
    • Welche Spalten werden am häufigsten aktualisiert
  • Die 1NF, 2NF und 3NF der Datenbanknormalisierung können angewendet werden, doch manchmal kollidieren diese Normalformen mit Query-Effizienz oder der für schnelle Entwicklung nötigen Benutzerfreundlichkeit
    • In manchen Situationen ist es einfacher, Daten in einer jsonb-Spalte abzulegen
  • Die folgenden Faustregeln wurden auf das Schema-Design angewendet
    • Für Primärschlüssel werden automatisch inkrementierende Ganzzahlen als Identity-Spalte oder die eingebaute Postgres-UUID verwendet
    • Identity-Spalten sind etwas schneller als bigserial
    • Für Zeitangaben wird immer timestamptz verwendet
    • Jede Tabelle erhält einen Primärschlüssel
    • Für kleine Tabellen, bei denen Konsistenz und Genauigkeit wichtig sind, werden Fremdschlüssel einschließlich Cascade Delete verwendet; in Umgebungen mit hohem Volumen ist dabei Vorsicht geboten

Lese-Queries und Indexe

  • Ein einfaches Modell zum Verständnis schneller SELECT-Abfragen ist, dass Postgres entweder mit einem Index schnell eine einzelne Zeile findet oder per sequentiellem Scan (seq scan) alle Zeilen einer Tabelle liest
  • Für schnelle Suchen nach einzelnen Zeilen werden folgende Strukturen verwendet
    • explizite Indexe
    • unique constraints als spezielle Form von Indexen
    • Primärschlüssel, die Postgres automatisch indiziert
  • Standardmäßig wird btree für Indexe verwendet; man kann ihn als separate Tabelle verstehen, die Daten in einer für Abfragen optimierten Form speichert
    • Die Suchzeit nach Zeilen beträgt ungefähr log(n), wobei n die Anzahl der Zeilen in der Tabelle ist
  • Wenn kein Index verwendet werden kann, wird ein sequentieller Scan ausgeführt; moderne Datenbanken laden Zeilen jedoch sehr schnell in den Speicher, sodass dies bei Tabellen mit weniger als 20.000 Zeilen fast sofort abgeschlossen sein kann

Joins und zusammengesetzte Indexe

  • Für die Zielseite eines Inner Joins sollten in der Regel Primärschlüssel verwendet werden; andernfalls könnte es Probleme im Schema-Design oder bei der Normalisierung geben
  • Die ON-Klausel sollte wie die WHERE-Klausel behandelt werden, und für Join-Bedingungen sollten geeignete Indexe verwendet werden
  • Listenabfragen auf großen Tabellen gehören oft zu den ersten Queries, die in einer Anwendung spürbar langsam werden
    • Wenn nach Organisation und Erstellungszeit gemeinsam gefiltert und sortiert wird, kann ein zusammengesetzter Index verwendet werden
CREATE INDEX CONCURRENTLY idx_documents_org_created
    ON documents (organization_id, created_at DESC);
  • Bei komplexen Queries ist es eine Faustregel, die ORDER BY-Spalten am Ende des Indexes zu platzieren und auch die Sortierrichtung anzupassen
    • Postgres kann btree in beide Richtungen scannen, daher kann DESC bei einer einzelnen Spalte bedeutungslos sein; bei zusammengesetzten Indexen ist es dennoch sinnvoll, dies anzupassen
    • Details zur Funktionsweise absteigender Indexe finden sich in diesem Beitrag

Schreiben, Sperren und Migrationen

  • Die erste Voraussetzung für erfolgreiches Schreiben ist, Transaktionen kurz zu halten
    • Sofern es keinen besonderen Grund gibt, sollte man während einer Transaktion keine externen Services abfragen
  • Die zweite Voraussetzung ist, nur die benötigten Zeilen zu sperren
    • Wird eine Zeile aktualisiert, bleibt sie bis zum Commit der Transaktion gesperrt
    • Mit steigender Systemlast wird die Auswirkung von Sperren deutlicher
  • Wird auf einer bestehenden großen Tabelle ein normales CREATE INDEX ausgeführt, wird die Tabelle gesperrt und insert sowie update werden blockiert; deshalb sollte immer CREATE INDEX CONCURRENTLY verwendet werden
  • Gute Fähigkeiten bei Schema-Migrationen erhöhen die Geschwindigkeit iterativer Entwicklung und verbessern die Verfügbarkeit
    • Wenn möglich, sollte man das Löschen oder Entfernen von Spalten vermeiden und Änderungen additiv vornehmen
    • Wenn möglich, sollte die Ausführung innerhalb einer Transaktion erfolgen, um Rollbacks und Teilanwendungen abzufangen
    • Als fortgeschrittenere Methode kann die Expand-and-Contract-Migration verwendet werden
  • Bei Migrationen sollte zuerst beurteilt werden, ob sie alle Schreibvorgänge blockieren
    • Das Erstellen eines Indexes ohne CONCURRENTLY kann alle Schreibvorgänge blockieren und Ausfallzeiten verursachen
    • ALTER TABLE-Arbeiten sollten erneut geprüft werden; auch das Hinzufügen einer Check Constraint auf einer großen Tabelle kann Schreibvorgänge blockieren
    • Wird eine Check Constraint mit NOT VALID hinzugefügt, lässt sich diese Blockierung vermeiden

Verbindungsmanagement

  • Jede Query und jede Transaktion verwendet eine Datenbank-Verbindung; Verbindungen verursachen hohe CPU- und Speicherkosten und sollten deshalb lange erhalten bleiben
  • Werden Verbindungen häufig aufgebaut und wieder entfernt, werden Ressourcen verschwendet
    • Ein connection storm mit vielen gleichzeitigen neuen Verbindungen kann schwer zu debuggende Probleme verursachen, die mit internen Sperren in Postgres zusammenhängen
  • Ein externer Connection Pooler wie pgbouncer sollte zuerst in Betracht gezogen werden; wenn das nicht möglich ist, ist ein In-Memory-Connection-Pool eine Alternative
    • Hatchet verwendet pgxpool für Go, da nicht angenommen werden kann, dass die Datenbanken der Nutzer einen externen Pooler verwenden

Query Planner und Statistiken

  • Komplexe Queries mit vielen Joins oder einer Mischung verschiedener Join-Methoden lassen sich nicht allein durch das Hinzufügen von Indexen lösen
    • Auch Indexe selbst haben Overhead und sollten daher nicht unbegrenzt hinzugefügt werden
  • Der Query Planner übersetzt SQL in interne Datenbankoperationen und entscheidet unter anderem über die Nutzung von Indexen, kann aber wegen begrenzter Informationen manchmal keinen optimalen Plan wählen
  • Die Informationen, die der Planner verwendet, sind Tabellenstatistiken, die in pg_stats abgefragt werden können
SELECT *
FROM pg_stats
WHERE tablename = 'mytable';
  • Statistiken werden bei ANALYZE gesammelt und auch bei der Ausführung von autovacuum aktualisiert
    • Wird autovacuum häufiger ausgeführt, bleiben auch die Query-Statistiken aktueller
    • Eine zu geringe Analysefrequenz ist eine der häufigen Ursachen für schlecht funktionierende Queries
  • Wenn man Queries vereinfacht danach beurteilt, ob sie einen sequentiellen Scan ausführen, verringert man das Risiko, durch Mikrooptimierungen die Unvorhersehbarkeit des Planners zu erhöhen
    • Wenn Abfragen auf Primärschlüssel und Indexe zentriert sind, kann der Planner leichter einen Plan wählen

Analyse von Ausführungsplänen und sequentielle Scans

  • Einige Anbieter wie Google CloudSQL sampeln Queries und speichern langsame Abfragen, aber nicht jeder Service unterstützt das
  • EXPLAIN ANALYZE führt die Query tatsächlich aus und vergleicht die geschätzte Zeilenzahl anhand der Tabellenstatistiken mit der tatsächlich gescannten Zeilenzahl
    • In der Produktion ist Vorsicht geboten, da die Query wirklich ausgeführt wird
    • Wenn man nur den Plan ohne Ausführung prüfen will, verwendet man EXPLAIN ohne ANALYZE
  • Detaillierte Pläne können als JSON gespeichert und anschließend auf explain.dalibo.com visualisiert werden
psql -XqAt -f explain.sql -d $DATABASE_URL > analyze.json
  • Wenn trotz korrekter Statistiken und Indexe ein sequentieller Scan erfolgt, könnte der Planner berechnet haben, dass die Kosten eines sequentiellen Scans geringer sind
    • Indexe werden getrennt vom Heap gespeichert, in dem die eigentlichen Tabellendaten liegen; dadurch entstehen Kosten, wenn mehrere über den Index gefundene Zeilen anschließend erneut aus dem Heap gelesen werden müssen
    • Wenn sich die Query nicht wesentlich umgestalten lässt, sollte man den sequentiellen Scan akzeptieren oder Partitionierung prüfen

Massenschreibvorgänge und Batch-Verarbeitung

  • Jede Query verursacht Overhead in Form von Round-Trip-Zeit zur Datenbank, Zeit zum Beziehen einer Verbindung aus dem Connection Pool der Anwendung und Verarbeitungszeit in Postgres
    • Auch interne Sperren in Postgres können in Umgebungen mit hohem Durchsatz zum Engpass werden
  • Werden mehrere Zeilen in einer Query gebündelt, lassen sich diese Kosten verringern
    • Die einfachste Methode besteht darin, mehrere Queries in einer impliziten Transaktion gesammelt an den Server zu senden
    • In Go kann pgx mit SendBatch verwendet werden
  • Bei Hatchet stieg der Durchsatz durch Batch-Verarbeitung um etwa das 10-Fache; weitere Optimierungen für Inserts sind im Leitfaden für schnelle Postgres-Inserts zusammengefasst

autovacuum und Transaction ID Wraparound

  • autovacuum ist für das Aufräumen von dead tuples und die Verwaltung von Transaction IDs zuständig; in Umgebungen mit sehr vielen Schreibvorgängen kann eine Anpassung der Einstellungen nötig sein
  • Ein tuple ist eine auf dem Dateisystem gespeicherte Version einer Zeile
    • Wird eine Zeile aktualisiert oder gelöscht, bleibt die vorherige Version bestehen, bis alle zuvor gestarteten Transaktionen committet oder zurückgerollt wurden
    • Eine Version, die von keiner Transaktion mehr gelesen werden kann, ist ein dead tuple
  • Wenn die Schreibgeschwindigkeit zu hoch ist, kann autovacuum mit der Entstehung von dead tuples nicht Schritt halten, wodurch sich der Zustand der Datenbank schnell verschlechtern kann
  • Wenn bei einem Blick in pg_stat_activity zu sehen ist, dass eine autovacuum-Query ungefähr länger als 1 Stunde läuft, sollte man eine Änderung der Einstellungen prüfen
  • Werden alle Transaction IDs aufgebraucht, bevor autovacuum sie zurückgewinnen kann, kommt es zu Transaction ID Wraparound, was erhebliche Ausfallzeiten nach sich zieht

Aufblähung von Tabellen und Indexen

  • Postgres speichert Zeilen in 8-KB-Seiten auf der Festplatte; passt eine neue Zeile nicht mehr in eine bestehende Seite, wird eine neue Seite angelegt
  • Wenn Seiten nach dem Aufräumen von dead tuples teilweise leer bleiben, entsteht table bloat, was den Speicherplatzbedarf stark erhöhen kann
    • Die beste Vorbeugung ist, autovacuum anzupassen, bevor es zur Aufblähung kommt
    • Für bereits aufgeblähte Tabellen kann eine Erweiterung wie pg_repack verwendet werden
    • Das eingebaute VACUUM FULL ist fast nie eine gute Wahl
    • In Postgres 19 soll REPACK...CONCURRENTLY für gleichzeitiges Repacking von Tabellen hinzukommen, Hatchet hat dies aber noch nicht getestet
  • Index Bloat ist ebenfalls eine spezielle Form von table bloat und lässt sich mit geeigneten autovacuum-Einstellungen reduzieren
    • Für bereits aufgeblähte Indexe kann der eingebaute Befehl REINDEX INDEX CONCURRENTLY verwendet werden

Gleichzeitige Verarbeitung mit FOR UPDATE SKIP LOCKED

  • FOR UPDATE SKIP LOCKED reserviert ausgewählte Zeilen für die aktuelle Transaktion, ohne andere Queries zu blockieren
  • Hatchet verwendet dies für eine Job Queue, bei der in einer Query wartende Jobs gesperrt und ihr Status auf RUNNING gesetzt werden können
WITH eligible_tasks AS (
    SELECT *
    FROM tasks
    WHERE status = 'QUEUED'
    ORDER BY id ASC
    FOR UPDATE SKIP LOCKED
    LIMIT 100
)
UPDATE tasks
SET status = 'RUNNING'
FROM eligible_tasks
WHERE tasks.id = eligible_tasks.id
RETURNING tasks.*;
  • Es ist auch nützlich, wenn voneinander unabhängige Zeilen gleichzeitig aktualisiert werden oder mehrere Anwendungsinstanzen Leases für Objekte verwalten
    • Hatchet verwendet es, um Tenant-Leases auf mehrere Engines zu verteilen

Partitionierung

  • Die eingebaute Partitionierung von Postgres teilt Tabellen anhand von Zeilenwerten wie timestamp oder hash auf
  • Bei Zeitreihendaten und historischen Job-Daten von Hatchet bietet sie die folgenden Vorteile
    • autovacuum kann pro Partition unabhängig ausgeführt werden, wodurch sich die Gesamtverarbeitungskapazität von autovacuum für Tabellen erhöhen lässt
    • Alte Daten lassen sich fast sofort entfernen, indem man eine Partitionstabelle entfernt, statt Zeilen einzeln zu löschen
  • Wenn Postgres in der Planungsphase unnötige Partitionen nicht entfernen kann, entsteht Overhead bei Lese-Queries

Datenverschiebung zwischen großen Tabellen

  • Mit Migration großer Tabellen ist hier keine Schemaänderung gemeint, sondern das Verschieben großer Datenmengen von einer Tabelle in eine andere
  • Das Kopieren einer sehr großen Tabelle in einer einzigen Transaktion kann Stunden dauern
    • Lang laufende Transaktionen verhindern die normale Arbeit von autovacuum und führen zu einer Zunahme von dead tuples
    • Wenn weiter in die alte Tabelle geschrieben wird, werden diese Daten nicht in der neuen Tabelle abgebildet
  • Hatchet führt große Batch-Backfills außerhalb von Transaktionen aus, und neue Schreibvorgänge nach Beginn der Migration werden mit Postgres-Triggern in die neue Tabelle kopiert
    • Mit der unique constraint des Primärschlüssels werden doppelte Schreibvorgänge verhindert

Verwandte Beiträge

1 Kommentare

 
GN⁺ 4 시간 전
Hacker-News-Kommentare

  • Bei einer Produktionsdatenbank sollte man meiner Meinung nach zuerst einen Backup- und Restore-Plan aufstellen. Hohe Verfügbarkeit mag anfangs optional sein, aber dass in einem Survival Guide Backup und Wiederherstellung fehlen, ist schon merkwürdig
    Ich frage mich, ob man für PostgreSQL-Backups heutzutage noch oft Barman(https://pgbarman.org/) verwendet

    • Wenn man kein PostgreSQL-Experte ist, sollte man es nicht selbst betreiben, sondern besser eine gemanagte Datenbank wie RDS nutzen. Die Ersparnis durch Self-Hosting ist gering im Vergleich zu dem, was man für bewährte Hochverfügbarkeit, Backup und Restore, Point-in-Time-Recovery und Read Replicas bekommt
    • Ich nutze pgBackRest. Es bietet eine bessere Point-in-Time-Recovery als die frühere selbstgebaute nächtliche Backup-Lösung, ließ sich relativ einfach für Backups nach Backblaze B2 konfigurieren und es gab keine besonderen Probleme
    • Für die meisten reicht es, per cron pg_dump_all auszuführen, mit zstd zu komprimieren und das Ergebnis nach S3, FTP oder ähnliches zu kopieren. Wenn die Datenmenge wächst, werden Zeit und Kosten für Vollbackups belastend, aber mit diesem einfachen Ansatz kommt man trotzdem ziemlich lange aus
    • Wenn die Datenbank selbst bei Stromausfällen Haltbarkeit garantiert, kann man mit atomaren Volume-Snapshots Backups erstellen. Um die Restore-Zeit zu verkürzen, sollte man zuerst einen Checkpoint erzeugen, und um Datenkorruption zu vermeiden, muss die Atomarität des Snapshots unbedingt gewährleistet sein
      Auf AWS habe ich ein MongoDB-System im Bereich mehrerer TB mit EBS-Snapshots gesichert und so schnelle inkrementelle Backups und Wiederherstellungen umgesetzt. Point-in-Time-Recovery ist damit zwar nicht möglich, aber weil man die Snapshots häufig im Stundenabstand erstellen kann, eignet sich das gut als ergänzende Strategie neben PostgreSQL-spezifischen Tools
    • Wenn ihr ohnehin schon Kubernetes betreibt, nehmt einfach CloudNativePG

  • Es gibt ein paar Punkte zu ergänzen. Statt eines normalen UUIDv4 sollte man UUIDv7 verwenden, und um Deadlocks zu vermeiden, muss man nicht nur bei der Anzahl gesperrter Zeilen, sondern in allen Queries die Sperrreihenfolge deterministisch vereinheitlichen, etwa mit id ASC
    Mit EXPLAIN (GENERIC_PLAN) kann man eine Query kopieren, ohne die Parameter-Placeholder aufzulösen, und außerdem den Optimierungsplan sehen, den PostgreSQL erstellt, wenn es die tatsächlichen Werte nicht kennt. Bei leeren oder kleinen Tabellen kann man mit SET enable_seqscan = off prüfen, ob ein Index überhaupt genutzt werden könnte
    Die von allen standardmäßig genutzten B-Tree-Indizes sind schwergewichtig und blähen sich leicht auf; wenn man ohne Sortierung oder Range-Scans nur einfache Lookups braucht, kann auch ein Hash-Index eine Überlegung wert sein. Eindeutige Hash-Indizes lassen sich zwar nicht anlegen, aber mit einer Hash-Exclusion-Constraint kann man einen ähnlichen Effekt erzielen; mehrspaltige eindeutige Indizes werden dabei nicht unterstützt
    GIN- und GiST-Indizes sollte man ebenfalls lernen. Für MySQL-Nutzer mag das überraschend sein, aber man kann damit sogar gewöhnliche LIKE '%foo%'-Queries beschleunigen, ohne auf Full-Text-Suche umzustellen

    • Deadlocks entstehen nicht nur dann, wenn für die zu sperrende Zeilenmenge kein konsistentes ORDER BY existiert, sondern auch bei unterschiedlicher Reihenfolge der Tabellensperren. Wenn eine Transaktion table_a und dann table_b sperrt und eine andere Transaktion es umgekehrt macht, entsteht ein Deadlock, selbst wenn innerhalb jeder Tabelle ORDER BY und FOR UPDATE verwendet werden
      Theoretisch ist das offensichtlich, in der Praxis aber viel schwerer zu debuggen, weil man global erfassen muss, welche Tabellen alle Schreibvorgänge anfassen; ich bin bei einer bestimmten Erweiterungsfunktion tatsächlich darauf gestoßen. Ich teste gerade GIN für JSONB-Key-Value-Lookups, und der Performance-Gewinn war enorm; auch der Unterschied zwischen AND und OR bei der Performance war beträchtlich
    • Jede Art von UUID als Primärschlüssel zu verwenden, ist teuer, weil Joins über Primärschlüssel häufig sind, und bringt meist wenig Nutzen. Als Standard ist ein fortlaufend inkrementierender Primärschlüssel sinnvoller; wenn man externe öffentliche IDs braucht, ist es sicherer, zusätzlich eine UUIDv4-Spalte mit Sekundärindex anzulegen. Ich frage mich, ob UUIDv7 in der Praxis für B-Tree-Performance wirklich besser ist als UUIDv4
    • Wenn man Seq Scan deaktiviert, erzwingt PostgreSQL dann nicht einfach irgendeinen vorhandenen Index, sobald es einen gibt? Daher zeigt das vermutlich nicht unbedingt, ob es der richtige Index ist
    • Als Konvertierungstools für UUIDv7 und UUIDv4 wurden https://github.com/ali-master/uuidv47 und https://github.com/stateless-me/uuidv47 schon mehrfach erwähnt

  • Dieser Ratschlag ist auch gut, aber die Startups, mit denen ich gearbeitet habe, sind zuerst auf organisatorische Probleme gestoßen, die noch unterhalb der Skalierbarkeit liegen. Es ist besser, kein ORM zu verwenden, statt bedeutungsvoller Felder sequentiell steigende Primärschlüssel zu nutzen und JSONB nur dann eingeschränkt einzusetzen, wenn es wirklich nötig ist
    Ursprungsdaten sollten als nur einfügbares, Append-only-Format behandelt und nicht geändert oder gelöscht werden. Denormalisierte Hilfstabellen für Performance und Komfort dürfen verändert werden, sollten aber nicht als Source of Truth dienen
    Einen Connection Pool sollte man verwenden, aber auf die Anzahl der Verbindungen achten; wenn es keine Probleme gibt, ist PgBouncer womöglich nicht nötig. Ohne klaren Grund sollte man explizite Transaktionen vermeiden, sie nicht für lang laufende Aufgaben wie RPC offen halten, und auch SERIALIZABLE besser fast nie verwenden
    Wenn explizite Sperren wie SELECT FOR UPDATE nötig sind, ist das Design möglicherweise falsch. Man sollte weder das Typsystem neu erfinden, indem Zeilen einer Tabelle je nach type int-Wert mehrere Bedeutungen bekommen, noch mit selbstreferenziellen node- und edge-Tabellen eine Graphdatenbank nachahmen. Meist lässt sich das mit normalen normalisierten Tabellen lösen

    • Im PHP-Backend, an dem ich arbeite, müssen für Berechtigungsprüfungen usw. Objekte instanziiert werden, daher ist ein ORM sehr nützlich. Ohne ORM scheint die Implementierung viel mehr Arbeit zu machen; mich interessiert, warum es eine schlechte Wahl sein soll
    • Wenn Entwicklergehälter der größte Kostenfaktor sind, ist das Prinzip „kein ORM verwenden“ umstritten. Unter den fachlichen Anforderungen der Tabellen, Kundendruck und knappem Budget laufen die Kosten weiter, selbst während man lange mit einem DBA über das richtige Design diskutiert; daher ist auch das Prinzip, Typ-Spalten oder graphartige Strukturen zu vermeiden, leichter gesagt als getan
    • Für Startups, die schnell ein Produkt live bringen müssen, ist ein ORM eine ausreichend gute Wahl. Wenn man Fallstricke wie N+1-Abfragen und Lazy Loading versteht, ist es ein besserer Kompromiss, als Query-Verwaltung und Parametrisierung wieder selbst zu bauen
      Ich würde lieber Zeit in die Produktentwicklung stecken, als mir zu Beginn des Projekts übermäßig viele Gedanken über das Datenbankschema zu machen und voreilig zu optimieren
    • Ich habe SELECT FOR UPDATE an vielen Stellen sinnvoll eingesetzt und frage mich, was genau daran problematisch ist. Ich würde auch gern wissen, ob solche Sperren überflüssig werden, wenn man eine Append-only Source of Truth verwendet
    • Append-only-Ursprungsdaten sind attraktiv, aber in vielen Systemen, an denen ich gearbeitet habe, hätte das für fragwürdige Vorteile den Speicherbedarf etlicher Tabellen explodieren lassen. Es ist zwar eine nützliche Technik, aber ich bezweifle, dass sie als Prinzip überall erzwungen werden sollte
      Umgekehrt würde mich interessieren, wie es wäre, traditionelle veränderbare relationale Tabellen als Source of Truth zu verwenden und Änderungen per Trigger als Log mitzuschreiben

  • Ich mag Cascade Delete nicht. Die meisten Entwickler leben eher in der Anwendungsschicht mit Python, Node oder Go als in der Datenbank, daher wirkt Cascade Delete, bei dem beim Löschen einer Zeile aus Tabelle A auch Daten aus Tabelle B verschwinden, leicht wie Magie. Bei Fehlkonfiguration ist es noch gefährlicher, daher sind für langfristige Wartbarkeit explizite DELETE-Anweisungen besser; allein mit korrekt verwendeten Fremdschlüsseln lässt sich die Konsistenz wahren
    Die Fallstricke und Workarounds bei Migrationen großer Tabellen stimmen, aber es gibt bereits Tools wie pg-osc. Es sollte so einfach sein, einen Befehl auszuführen und dann angespannt zu beobachten, wie die Daten während 24 Stunden kopiert werden
    Application- und Datenbank-Deployments sollte man früh voneinander trennen. Da sich Schema- und Application-Änderungen nicht vollständig gleichzeitig transaktional ausrollen lassen, braucht man im Betrieb früh die Gewohnheit, nur abwärtskompatible Schemaänderungen vorzunehmen, etwa neue Spalten nullable anzulegen oder mit Default-Wert zu versehen und Tabellen- oder Spaltennamen nicht umzubenennen
    Auch die Strategie zur Schema-Verwaltung sollte früh festgelegt werden. Ein Deployment-Prozess, bei dem ein Senior-Entwickler auf seinem Rechner manuell DDL auf die Produktions-DB ausführt, sollte vermieden werden; stattdessen kann man vertraute Tools wie Liquibase oder Flyway verwenden

    • Ich habe das deklarative Schema-Management-Tool pgschema entwickelt

  • Der Query Planner optimiert den durchschnittlichen Fall, aber für Applications ist es oft nützlicher, den schlechtesten Fall zu optimieren. Ein durchschnittlicher Nutzer hatte wenige Zeilen und bekam mit einem bestimmten Index in unter 10 ms ein Ergebnis, aber bei Heavy Usern dauerte dieselbe Query je nach Parameter über 1 Sekunde
    Mit einer komplexeren Query habe ich einen anderen Index-Pfad erzwungen; die durchschnittliche Performance wurde etwas schlechter, aber auch der schlechteste Fall sank auf unter 100 ms. Für das Unternehmen war das Verhindern von Timeouts viel wichtiger als durchschnittlich 10 ms einzusparen

  • SKIP LOCKED ist nützlich für arbeitswarteschlangen auf Basis interaktiver Transaktionen, bei denen die Application eine Transaktion offen hält und Zeilen sperrt, während sie die Arbeit ausführt. In Hochleistungs-Applications vermeidet man solche Transaktionen selbst und aktualisiert die Zeile sofort auf pending, sodass SKIP LOCKED nicht nötig ist
    Mit wachsender Größe muss man den Zustand reduzieren, der im Speicher der Datenbank gehalten wird, und interaktive Transaktionen gehören zu diesem Zustand. In skalierenden Umgebungen ist Idempotenz vorteilhafter als Atomarität

  • Lang laufende Transaktionen können den Zustand der Datenbank beeinträchtigen und sollten nur mit starker Begründung verwendet werden. Mit idle_in_transaction_session_timeout sollte man verhindern, dass inaktive Transaktionen Sperren oder Tupel lange festhalten, und für Migrationen sollte lock_timeout gesetzt werden, damit ein einzelnes DDL nicht das gesamte System anhält
    Auch statement_timeout sollte gesetzt werden, damit nicht eine einzige teure Query das System lahmlegt

  • Aus dem Betrieb von PostgreSQL in der Frühphase eines Startups habe ich gelernt, dass dieser Artikel Monitoring und Alarmierung nicht stark genug betont. Bei PostgreSQL gibt es einige zentrale Ausfallarten, die man unbedingt vermeiden muss, und mit Alarmen lassen sich Risiken früh erkennen
    Selbst wenn AWS eine E-Mail schickt, dass sich eine Transaction-ID-Wraparound nähert, übersieht man das in einem Startup leicht, besonders an Tagen wie Boxing Day. Die von AWS überwachten Signale sollten nicht an E-Mail, sondern an den Pager gekoppelt sein

  • Bei der Implementierung von Connection Pools gibt es große, wenig bekannte Unterschiede. Die meisten Application-Connection-Pools optimieren mit First In, First Out (FIFO) auf geringe Latenz und Verbindungsverfügbarkeit, halten Verbindungen dadurch aber dauerhaft warm, sodass sich unnötige Verbindungen schwer reduzieren lassen
    PgBouncer und einige externe Pooler verwenden Last In, First Out (LIFO), um die Anzahl der Verbindungen, die PostgreSQL erreichen, und den Durchsatz zu optimieren. Wenn die zuletzt verwendete Verbindung zuerst wiederverwendet wird, kühlen übrige Verbindungen natürlich ab und werden beendet
    Für neue Applications reicht FIFO aus, aber mit wachsender Größe ist ein Tool wie PgBouncer sinnvoll, um Hunderte von Verbindungen um etwa 90 % zu reduzieren. Die PostgreSQL-Architektur, die pro Verbindung einen Prozess erzeugt, arbeitet umso besser, je weniger Verbindungen es gibt

  • In sehr spezifischen Situationen wurden gute Ergebnisse erzielt, indem Joins im Application Memory ausgeführt wurden. Man versucht manchmal, Datenbank-Roundtrips zu reduzieren, und erstellt dabei eine einzige Query, in der komplexe JOIN, UNION und CASE miteinander verheddert sind
    Stattdessen kann es vorteilhafter sein, mehrere einfache Queries unabhängig auszuführen und anschließend beim Iterieren über die Ergebnisse die zugehörigen Zeilen per Map zu verknüpfen, weil der Query-Plan trotz zusätzlicher Roundtrips und Iterationskosten besser vorhersehbar wird. Das sollte nur begrenzt eingesetzt werden; auch wenn einige ORMs intern so arbeiten, wird es nicht pauschal empfohlen

    • Die Wirkung dieses Ansatzes hängt stark von der Situation ab. Wenn durch einen Join ein kartesisches Produkt entsteht, das viel größer ist als die Ausgangsdaten, kann es DB-Last und Netzwerk-Traffic reduzieren, nur die Ausgangsmengen zu holen und sie lokal zu kombinieren
      Selektive Inner Joins erzeugen jedoch Ergebnisse, die viel kleiner sind als die Ausgangsdaten; dann ist es deutlich teurer, alle Records zu holen und lokal Schnittmenge und Filterung auszuführen. Bei Index-Joins kann der Query Planner zudem Indizes nutzen, um wahllose Table Scans, Sortierung und Filterung zu vermeiden
    • Soweit bekannt wird auch der Ansatz verwendet, zwei Views zu erstellen und diese dann zu joinen, statt eine einzelne komplexe Query zu schreiben