Unkonventionelle PostgreSQL-Optimierungstechniken

• Einführung von unkonventionellen Methoden zur Steigerung der PostgreSQL-Abfrageleistung, die sich vom üblichen Schema lösen und statt zusätzlicher Indizes oder Query-Rewrites drei kreative Ansätze vorstellen
• Vermeidung von Full Table Scans auf Basis von CHECK-Constraints, Optimierung niedriger Kardinalität mit funktionsbasierten Indizes und Umsetzung von Unique Constraints mit Hash-Indizes
• 1. Nutzung der Einstellung constraint_exclusion, um bei Abfragen mit falschen Bedingungen unnötige Scans zu verhindern
• 2. Einsatz funktionsbasierter Indizes und virtueller generierter Spalten (virtual generated columns), um die Indexgröße zu verringern und Query-Konsistenz sicherzustellen
• 3. Kombination von Hash-Indizes und Exclusion Constraints für eine effiziente Umsetzung von Unique Constraints bei großen Textspalten, mit deutlicher Einsparung von Speicherplatz

---

Vermeidung von Full Table Scans mit CHECK-Constraints

• Obwohl für die Spalte plan ein CHECK-Constraint existiert, das nur die Werte 'free' und 'pro' zulässt, führt PostgreSQL bei einer fehlerhaften Abfrage mit 'Pro' einen Full Table Scan aus
  • Im Ausführungsplan werden alle 100.000 Zeilen gelesen, das tatsächliche Ergebnis sind jedoch 0 Zeilen
• Wird der Parameter constraint_exclusion auf 'on' gesetzt, berücksichtigt PostgreSQL die Constraints und überspringt den Scan vollständig
  • Die Ausführungszeit sinkt von 7,4 ms auf 0,008 ms
• Der Standardwert ist 'partition'; bei einfachen Abfragen kann dadurch der Planungs-Overhead sogar steigen
  • In BI- und Reporting-Umgebungen ist die Einstellung 'on' jedoch nützlich, da Nutzer dort häufig fehlerhafte Bedingungen eingeben

Optimierung niedriger Kardinalität mit funktionsbasierten Indizes

• In der Tabelle sale mit 10 Millionen Verkaufsdatensätzen benötigt eine Query zur täglichen Umsatzaggregation bei einem Full Table Scan 627 ms
• Ein zusätzlicher B-Tree-Index auf der Spalte sold_at verkürzt die Zeit auf 187 ms, der Index wächst jedoch auf 214 MB an
• Wird ein funktionsbasierter Index auf dem Ausdruck date_trunc('day', sold_at) erstellt, schrumpft die Größe auf 66 MB und die Ausführungszeit verbessert sich weiter auf 145 ms
  • Dank der niedrigen Kardinalität ist Index-Deduplication möglich
• Allerdings muss der Query-Ausdruck exakt mit der Indexdefinition übereinstimmen, daher ist Konsistenz der Ausdrücke erforderlich
  • Dafür kann ein VIEW mit demselben Ausdruck erstellt werden, oder
  • es kann eine virtuelle generierte Spalte (virtual generated column) hinzugefügt werden, die ab PostgreSQL 18 unterstützt wird, um diese Konsistenz zu automatisieren
• Mit virtuellen generierten Spalten wird der Index automatisch genutzt, sodass sich kleiner Index, schnellere Queries und Ausdruckskonsistenz gleichzeitig erreichen lassen
• In PostgreSQL 18 wird die Erstellung von Indizes auf virtuellen Spalten allerdings noch nicht unterstützt; geplant ist dies für Version 19

Umsetzung von Unique Constraints mit Hash-Indizes

• In der Tabelle urls, die lange URLs speichert, erreicht ein B-Tree-basierter Unique-Index zur Vermeidung doppelter URLs eine Größe von 154 MB
• Ein Hash-Index speichert nicht den eigentlichen Wert, sondern nur den Hashwert und ist deshalb deutlich kleiner
  • PostgreSQL unterstützt standardmäßig zwar keine Unique-Hash-Indizes,
  • über ein Exclusion Constraint lässt sich ein Unique Constraint jedoch indirekt in der Form EXCLUDE USING HASH (url WITH =) umsetzen
• Auch mit dieser Methode tritt bei doppelten Inserts ein Fehler auf, und die Query-Performance ist ebenfalls besser als mit B-Tree (0,022 ms vs. 0,046 ms)
• Die Indexgröße beträgt 32 MB und ist damit mehr als fünfmal kleiner als bei B-Tree
• Nachteile:
  • keine Referenzierung per Fremdschlüssel möglich (REFERENCES-Constraint nicht möglich)
  • eingeschränkte Kompatibilität mit der Syntax INSERT ... ON CONFLICT
  • stattdessen können ON CONFLICT ON CONSTRAINT oder MERGE verwendet werden
• Hash-Indizes eignen sich zur Sicherstellung der Eindeutigkeit großer Textspalten und sind dann eine platzsparende Alternative, wenn keine Fremdschlüssel benötigt werden

[GN⁺]

Hacker-News-Kommentare

• Der Index ist 214 MB groß, also etwa halb so groß wie die gesamte Tabelle
  Aus Sicht von Analysten ist das gut, aber für die Schreibperformance entsteht dabei das Problem der write amplification
  Indizes werden je nach Lese-/Schreibverhältnis unterschiedlich entworfen, und genau deshalb gibt es Data Warehouses oder Read Replicas
  Wenn man mit sehr vielen Nutzern zu tun hat, ist es besser, keine BI-/OLAP-Indizes in der OLTP-DB zu haben

  • Ich fände es gut, wenn PostgreSQL clustered indexes (Oracles Index Organized Table) unterstützen würde
    Wenn das Zugriffsmuster auf die Tabelle konstant ist, kann die Tabelle selbst zum Index werden und so Effizienz ohne write amplification liefern

• Ich denke, im ersten Beispiel wäre es besser, Plan als Enum-Typ zu definieren
  Das ist leichter als Text und robuster, weil fehlerhafte Filtereingaben mit einem Fehler statt mit einem leeren Ergebnis behandelt werden

• Ausgezeichneter Artikel. Ich nutze PostgreSQL und MySQL seit Jahrzehnten, aber selbst nach diesem Artikel hatte ich das Gefühl, immer noch nur einen Teil der Möglichkeiten zu kennen

  • Ich nutze Postgres auch seit über 10 Jahren, aber jedes Mal, wenn ich in die Dokumentation schaue, habe ich immer noch das Gefühl, nur an der Oberfläche zu kratzen. Es ist wirklich ein mächtiges System
  • PostgreSQL ist wie Emacs. Es sieht oberflächlich simpel aus, hat aber in Wirklichkeit eine Flexibilität auf Betriebssystem-Niveau

• Am interessantesten fand ich die am Ende des Artikels erwähnte MERGE-Syntax
  Normalerweise erledige ich Upserts mit INSERT ... ON CONFLICT DO UPDATE, aber MERGE scheint mächtiger zu sein und in verschiedenen Situationen einsetzbar

  • MERGE gibt es im SQL-Standard schon seit Langem, aber Postgres hat die Einführung wegen des Nicht-Atomaritätsproblems im MVCC-Modell hinausgezögert
    Das wird auch im Blogbeitrag von pganalyze erklärt
    Persönlich bevorzuge ich INSERT ... ON CONFLICT und nutze MERGE nur dann, wenn es wirklich nötig ist, wobei ich die Fehlerbehandlung sorgfältig angehe
  • Im Hinblick auf Parallelität ist INSERT ... ON CONFLICT besser vorhersagbar
    Siehe den Vergleichsartikel auf modern-sql.com
  • Für umfangreiche Batch-Inserts ist COPY INTO im Binärformat am schnellsten. Der serverseitige Overhead ist nahezu null

• Der im Artikel nicht behandelte BRIN-Index war interessant
  Wenn die Daten monoton ansteigen, ist er ideal als sehr kleiner und schneller Index

  • Es müssen nicht unbedingt vollständig monotone Daten sein. Wenn sie größtenteils monoton sind, funktioniert es gut genug
    Zum Beispiel ist das auch für Timestamp-Daten, die ein Server empfängt und die leicht aus der Reihenfolge laufen, gut geeignet
    Bei UUIDv7 muss man eventuell pages_per_range anpassen

• Ich finde es schon immer schade, dass man bei Hash-Indizes keine Unique-Constraints setzen kann
  Es wirkt so, als ließe sich das mit etwas Glue-Code lösen, der das einfach in eine Exclusion Constraint umwandelt — ich frage mich, warum es das noch nicht gibt

• Hashbasierte Eindeutigkeitsprüfung wird im Index nicht unterstützt, weil Kollisionsbehandlung fehlt
  Der vorgeschlagene Lösungsansatz hat dasselbe Problem

  • Das stimmt überhaupt nicht. Der Index speichert nur den Hash, aber in der Tabelle ist der vollständige Wert gespeichert
    Postgres betrachtet etwas nur dann als Duplikat, wenn sowohl der Hash als auch der tatsächliche Wert übereinstimmen
  • Das lässt sich auch im dbfiddle-Beispiel nachvollziehen

• Der Inhalt des Artikels war erfrischend. Virtuelle Spalten und Hash-Indizes sind interessant, aber es wirkt, als seien sie noch nicht vollständig ins Ökosystem integriert

  • Virtuelle Spalten sind fast fertig. In PostgreSQL 18 ist das meiste bereits implementiert
    Hash-Indizes hatten lange viele Einschränkungen, werden aber schrittweise verbessert; automatische Unique-Constraints bleiben die offene Aufgabe

• Ich frage mich, ob man nicht einfach mit einer stored generated column direkt einen Index erstellen könnte

  • Im Artikel wird erklärt, warum dieser Ansatz vermieden werden soll
    Er wird seit PostgreSQL 14 unterstützt, aber das Ergebnis wird physisch gespeichert und belegt zusätzlichen Speicherplatz
  • Wäre es nicht auch möglich, einen ausdrucksbasierten partial index zu erstellen?
  • Letztlich erhöht das ebenfalls den Speicherverbrauch, daher ist es in dem Beispiel aus dem Artikel ein Ansatz, den man vermeiden will

• Seit dem Wechsel in die Cloud habe ich in einer festen Serverumgebung seltener direkt mit pgsql zu tun
  Ich frage mich, ob das im Artikel gezeigte SQL-Syntax-Highlighting eine eingebaute Funktion oder ein separates Tool ist

  • Ich nutze pgcli. Es hat viele Komfortfunktionen wie Transaktionsstatusanzeige, Autovervollständigung und Highlighting
    Allerdings ist es lästig, dass beim Kopieren langer Abfragen nach Zeilenumbrüchen automatisch Leerzeichen eingefügt werden
  • Mit einer IDE wie IntelliJ kann man Syntax-Highlighting und Autovervollständigung zusammen nutzen