Du beginnst also ein neues Projekt – oder Unternehmen – und hast dich für Postgres entschieden. Der schwierige Teil (die Wahl der Datenbank) ist vorbei, und jetzt beginnt der spaßige Teil: Du musst sicherstellen, dass du in den nächsten Jahren nicht mehr darüber nachdenken musst.

In diesem Beitrag findest du einige weniger bekannte Tipps, wie du Postgres langfristig konfigurieren kannst: von Connection Pooling über Sicherheitsgrundlagen bis hin zu Erweiterungen und Indizes.

Einrichten von Connection Pooling für Postgres mit PGBouncer

Standardmäßig gabelt Postgres für jede Client-Verbindung einen eigenen Prozess vom Hauptprozess des Betriebssystems ab. Bei geringem Volumen spielt die Zeit, die für das Erstellen und Zerstören dieser Prozesse benötigt wird, keine Rolle – und auch nicht die Tatsache, dass sie nie wieder verwendet werden. Du kannst die Anzahl der max_connections manuell einstellen, aber wenn du die Anzahl der Verbindungen erhöhst, wirst du wahrscheinlich auf Probleme stoßen. Das Pooling von Verbindungen hilft dir dabei, diese Prozesse zwischenzuspeichern und sie wiederzuverwenden, wenn sich Kunden mit deiner Datenbank verbinden und die Verbindung trennen.

Obwohl du das Connection Pooling in deine Anwendungslogik einbauen kannst, entscheiden sich die meisten für ein Tool eines Drittanbieters, im Fall von Postgres ist das PGBouncer. Dabei handelt es sich um einen leichtgewichtigen Open-Source-Verbindungspooler, den du entweder auf deinem Datenbankserver oder auf deinem Anwendungsserver installieren kannst. Du hast die Wahl zwischen 3 Pooling-Stufen:

  • Session-Pooling: Es bleibt dem Modell „Client-Verbindungen sind unbegrenzt“ treu und hält eine Verbindung für die gesamte Zeit offen, in der ein Client verbunden ist.
  • Transaktions-Pooling: Verbindungen bleiben nur für eine einzige Transaktion bestehen und werden dann wieder an den Pool zurückgegeben.
  • Statement-Pooling: Die Verbindungen bleiben nur für eine Abfrage bestehen. Wenn du also mehrere Abfragen als Teil einer Transaktion hast, funktioniert das überhaupt nicht.

Die meisten entscheiden sich für Session Pooling – es ist die konservativste Lösung und birgt das geringste Risiko für Verbindungsabbrüche – aber jede Anwendung ist anders und du musst den richtigen Modus für deine Anforderungen herausfinden.

Auswirkungen von Connection Pooling auf die Leistung

Die Millionen-Dollar-Frage ist jedoch: Funktioniert das wirklich? Percona hat eine Reihe von Benchmarks durchgeführt, um herauszufinden, wie sich PGBouncer auf die Leistung auswirkt. Bei einer kleinen Anzahl von gleichzeitigen Clients (<60) verschlechtert PGBouncer die Transaktionen pro Sekunde (TPS) aufgrund des Overheads des Poolings sogar um einiges. Aber wenn du auf >100 skalierst, wirst du deutliche Leistungsvorteile sehen.

PGBouncer
PGBouncer

Brauchst du also sofort einen Verbindungspooler, um deine ersten paar Nutzer zu unterstützen? Wahrscheinlich nicht. Aber der Einsatz von PGBouncer wird dir helfen, sobald du auch nur geringen/mittleren Traffic hast.

Postgres Sicherheit für Dummies

In den ersten Wochen, in denen du an deinem Projekt arbeitest, sind es meist nur ein oder zwei Entwickler, die an einer leeren Datenbank arbeiten; die Sicherheit steht nicht im Vordergrund. Aber wenn du deine Anwendung der Welt präsentierst, hat Sicherheit Priorität. Und bei Datenbanken scheint es manchmal so, als gäbe es eine Million verschiedene Möglichkeiten, sie zu sperren.

Zugriffsbeschränkung auf der Host- oder Benutzerebene

Beginnen wir mit dem Zugriff. Postgres schränkt den Zugriff auf zwei Arten ein:

  1. Auf Host-Ebene – Definition von IP-Adressen und Domänen mit Zugriffsrechten
  2. Auf der Benutzerebene – Definition von Datenbankbenutzern und deren Berechtigungen

In der Datei pg_hba.conf im PGDATA-Verzeichnis legst du fest, wer sich mit welchen Datenbanken verbinden darf. Wenn du dort keinen Eintrag für einen Kunden hast, kann dieser nicht auf die Datenbank zugreifen. Angenommen, dein Anwendungsserver läuft woanders, dann könntest du ihm den Zugriff auf die Datenbank erlauben:

# Trust any connection via TCP/IP from this machine
host all 127.0.0.1 255.255.255.255 trust

Abgesehen von „vertraue allen Verbindungen von diesem Rechner“ gibt es viele verschiedene Möglichkeiten, deinen Client gegenüber dem Datenbankserver zu authentifizieren, vom Passwort über die Identität bis hin zu Zertifikaten. Und wenn du auf die Annehmlichkeiten von RDS (oder Kinsta) verzichtest und dein Backend auf demselben Server wie deine Datenbank laufen lässt, kannst du dich über Unix-Sockets statt über TCP/IP verbinden.

Autorisierung und Privilegien

Sobald dein Client selbst authentifiziert ist, musst du dich mit der Frage der Autorisierung befassen. Der SQL-Standard definiert ein Berechtigungssystem, und jedes Objekt in Postgres (z. B. eine Tabelle, eine Zeile usw.) hat verschiedene Berechtigungen, die den Benutzern zugewiesen werden können: Dinge wie SELECT und UPDATE, aber auch TRUNCATE, REFERENCES, TRIGGER usw. Du vergibst die Privilegien an Benutzer mit dem GRANT befehl.

Am besten befolgst du das Prinzip der geringsten Berechtigung. Der Datenbankbenutzer, den du für deine(n) Client(s) anlegst, sollte also nur auf das zugreifen können, was er braucht.

Sicherheit auf Zeilenebene

Der letzte Punkt, den wir hier behandeln, ist die sicherheit auf Zeilenebene. RLS besteht aus der Perspektive der Tabelle (nicht des Benutzers) und schränkt ein, auf welche Zeilen zugegriffen werden kann, welche aktualisiert werden können usw. Standardmäßig ist RLS für Tabellen nicht aktiviert, so dass deine Nutzer alles tun können, was ihre Zugriffsrichtlinien vorschreiben. Um RLS für eine Tabelle zu aktivieren, beginnst du mit:

ALTER TABLE [table_name] ENABLE ROW LEVEL SECURITY

Und füge dann eine Richtlinie hinzu. Angenommen, du möchtest den Lesezugriff auf deine Tabelle lightsaber_internals auf vertrauenswürdige Personen beschränken, die bereits in der Jedi-Benutzergruppe definiert sind, so dass nur der Besitzer eines Lichtschwerts dessen interne Details sehen kann. So würdest du es machen:

ALTER TABLE lightsaber_internals ENABLE ROW LEVEL SECURITY
CREATE POLICY jedi_only ON lightsaber_internals TO jedi
    USING (jedi = lightsaber_jedi);

RLS-Richtlinien wie diese sind nützlich, wenn du Sicherheit auf einer detaillierteren Ebene als nur Tabellen brauchst (Situationen mit personenbezogenen Daten usw.).

Denke im Voraus über Skalierungsprobleme nach

In jedem Startup, in dem ich je gearbeitet habe, gab es ein gewisses Maß an manueller Skalierung, wenn es um die Datenbank ging. Eines Tages wachst du auf und Datadog flippt aus, weil auf deinem Postgres-Server kein Platz mehr ist. Du gehst der Sache nach, aktualisierst die Vorfallsseite und erhöhst schließlich die Festplattengröße, bis es wieder passiert (obwohl es beim nächsten Mal vielleicht ein RAM-Problem ist). Diesen Dingen zuvorzukommen, kann helfen! Ein paar Vorschläge:

1. Datenbanküberwachung einrichten

Die meisten Unternehmen, bei denen ich gearbeitet habe, nutzen Datadog für ihre Datenbanküberwachung. Wenn du einen verwalteten Datenbankservice nutzt, kommst du wahrscheinlich auch mit deren eigenen Funktionen zurecht. Im Blog von Datadog findest du einen guten Beitrag über die wichtigsten Kennzahlen, die du beobachten solltest, wie z. B. Lese- und Schreibdurchsatz, sequentielle Scans, auf die Festplatte geschriebene Daten usw.

2. Erstelle Richtlinien für die vertikale Skalierung

Wenn dein Team angepiepst wird – und das wird passieren – ist das Letzte, was du willst, dass alle an Deck gehen müssen, um das Problem zu lösen, obwohl in den meisten Fällen eine einfache Skalierung das Problem löst. Es ist gut, wenn du für dein Team einen Plan aufstellst, was alles möglich ist, wenn dir der Platz oder die Rechenleistung ausgeht.

3. Vakuumieren und Abstimmen deines Autovakuums

Wenn du DELETE Daten in Postgres oder UPDATE Daten löschst (was funktional dem Löschen und Einfügen entspricht), löscht Postgres diese Daten nicht sofort (😱). Stattdessen werden sie als gelöscht „markiert“, indem die Transaktions-ID der Löschung in einem xmax-Header gespeichert wird; der Grund dafür ist, dass MVCC in Postgres dadurch einfacher wird. Wenn diese Zeilen aber nicht wirklich gelöscht werden, verschwenden sie Speicherplatz und bereiten dir Probleme.

Der einfachste Weg, diese Zeilen loszuwerden, ist die Verwendung des VACUUM befehl. Du könntest das Vakuum manuell ausführen, wenn sich tote Zeilen ansammeln, oder es sogar so einstellen, dass es alle x Minuten ausgeführt wird. Das Tuning deines Autovakuums ist ein komplexes Thema, das den Rahmen dieses Beitrags sprengen würde: Ich empfehle dir, den Beitrag von 2ndQuadrant darüber zu lesen.

4. Einrichten einer Read Replica (oder zwei)

Das ist ganz einfach. Wenn du mit einem erheblichen Anstieg des Datenverkehrs rechnest (z. B. bei einer bevorstehenden Markteinführung), kannst du ganz einfach nur lesende Repliken einrichten (oder zumindest eine), um die Haupt-DB-Instanz zu entlasten.

Wenn du dich für mehrere Replikate entscheidest, hast du den zusätzlichen Vorteil, dass du die Verfügbarkeit verbesserst, falls eines der Replikate aus irgendeinem Grund ausfällt. Das Hinzufügen von Replikaten ist bei den meisten DBaaS-Anbietern ziemlich einfach; achte nur auf die Kosten: Sie kosten oft genauso viel wie eine Haupt-DB-Instanz, obwohl sie nur lesend arbeiten.

Füge Indizes zu deinen (erwarteten) größten Tabellen hinzu

Datenbankindizes helfen dabei, Leseabfragen zu beschleunigen, indem sie zusätzliche Datenstrukturen erstellen, die deine Scans schneller machen. Für viele Anwendungsfälle ist das Hinzufügen eines Indexes zu ein oder zwei Tabellen im Grunde ein Kinderspiel. In Postgres kannst du einen Index mit dem CREATE INDEX befehl erstellen (duh). Wenn du eine Tabelle abfragst, prüft die Datenbank, ob ein Index vorhanden ist, und verwendet ihn, wenn dies der Fall ist (du kannst dies übrigens mit EXPLAIN überprüfen).

Der beliebteste Index-Typ in Postgres – und der Standard-Index, wenn du CREATE INDEX verwendest – ist ein B-Tree-Index. Er nimmt die Spalte, für die du einen Index erstellen willst, sortiert sie und speichert Zeiger auf die sortierten Zeilen. Auf diese Weise kannst du die Effizienz der binären Suche auf jede beliebige Spalte anwenden, nicht nur auf die Spalte, nach der die Tabelle sortiert ist (wenn es überhaupt eine gibt). In den Postgres-Dokumenten kannst du nachlesen, wie diese Bäume implementiert sind.

Obwohl sie nützlich sind, sind Indizes kein reines Vergnügen: Sie brauchen Platz und wenn du nicht darauf achtest, wie viele und welche Art von Indizes du erstellst, können sie die Leistung der Datenbank beeinträchtigen. Niemand schildert das besser als die Postgres-Dokumente selbst:

„Indizes werden in erster Linie verwendet, um die Leistung der Datenbank zu verbessern (obwohl eine unsachgemäße Verwendung zu einer geringeren Leistung führen kann).“

Wenn du einen Index erstellst, erstellt Postgres eine Nachschlagetabelle, die den Index und einen Zeiger auf den Datensatz des Index enthält. Zu viele dieser Tabellen verbrauchen Speicherplatz, lassen INSERT-Abfragen länger dauern und zwingen die Abfrage-Engine, mehr Optionen in Betracht zu ziehen, bevor sie entscheidet, wie eine Abfrage ausgeführt werden soll.

Bonus: Füge einige Postgres-Erweiterungen hinzu

Eine Sache, die Postgres einzigartig macht, ist die native Unterstützung für Erweiterungen von Drittanbietern. Du kannst sie aus SQL und C erstellen, und sie können so klein wie ein paar Anweisungen oder so groß wie eine ganze Software-Bibliothek sein. Die Verwendung von öffentlich verfügbaren / Open-Source-Erweiterungen hilft dir genauso wie die Verwendung eines Softwarepakets: Warum solltest du deinen eigenen Code schreiben, wenn du den von jemand anderem verwenden kannst? Hier sind ein paar der beliebtesten Postgres-Erweiterungen:

Timescale

Timescale ist eine Postgres-Erweiterung für die Arbeit mit Zeitreihendaten. Kurz gesagt, sie macht deine Abfragen (viel) schneller und speichert Zeitreihendaten sehr effizient. Eine Installationsanleitung findest du hier. Du kannst auch die Cloud-Hosting-Option von Timescale in Betracht ziehen, wenn du wirklich mit Zeitreihendaten arbeitest (obwohl du das wahrscheinlich schon weißt).

PostGIS

PostGIS erweitert Postgres um die Möglichkeit, geografische Daten zu speichern, zu indizieren und abzufragen (z. B. Linien, Polygone, Orte usw.). Wenn du einen Cloud-Anbieter nutzt, ist PostGIS meist schon vorinstalliert. Wenn du es jedoch selbst installieren musst, findest du hier eine Installationsanleitung.

pg_stat_staements

pg_stat_statements erstellt eine Ansicht in deiner Postgres-Datenbank mit Statistiken zu jeder Abfrage, die in der Datenbank ausgeführt wird. Du kannst Statistiken sehen, wie lange die Abfrage dauert (Mittelwert, Median, Durchschnitt usw.), wer die Abfrage ausgeführt hat, Block-Cache-Treffer, Anzahl der geschriebenen Blöcke und vieles mehr (insgesamt 44 Spalten in dieser Ansicht). Zur Installation fügst du sie einfach zu deiner .conf-Datei hinzu und startest den Server neu.

pg_audit

pg_audit hilft bei Unternehmen, die einer detaillierten Prüfung unterliegen (z. B. Behörden, Finanzinstitute usw.). Du kannst Postgres dazu bringen, jede einzelne Anweisung in der Datenbank zu protokollieren, indem du `log_statement=all` einstellst, aber das bedeutet nicht, dass die benötigten Informationen leicht zu finden sind. pg_audit nutzt die internen Protokollierungsfunktionen von Postgres, um das Auffinden und die Arbeit mit den Protokollen zu erleichtern, die ein Prüfer möglicherweise benötigt. Eine Installationsanleitung findest du hier.

Zusammenfassung

Postgres ist eine großartige (und sehr beliebte) Option, um dein Unternehmen darauf aufzubauen, und wir sind stolz darauf, sie bei Kinsta zu unterstützen. Wir hoffen, dass diese Tipps dir dabei helfen, dein Unternehmen auf die Beine zu stellen und zu skalieren. Hast du weitere Tipps oder Gedanken aus deiner Erfahrung? Lass es uns hier wissen.

Justin Gage

Justin is a technical writer and author of the popular Technically newsletter. He did his B.S. in Data Science before a stint in full-stack engineering and now focuses on making complex technical concepts accessible to everyone.