Optimierung von Datenbankabfragen: Indizes, Ausführungspläne und Partitionierung

Ein einzelner fehlender Index kann eine 2-Millisekunden-Abfrage in einen 20-Sekunden-Tabellenscan verwandeln. Wenn Ihre Datenbank von Tausenden auf Millionen von Zeilen anwächst, ist der Unterschied zwischen einer optimierten und einer nicht optimierten Abfrage der Unterschied zwischen einer reaktionsfähigen Anwendung und einer Anwendung, die unter Last abbricht. Die Datenbankoptimierung bietet den höchsten Return on Engineering-Zeit aller Performance-Arbeiten, die Sie durchführen können.

Wichtige Erkenntnisse

• EXPLAIN ANALYZE ist Ihr leistungsstärkstes Diagnosetool – lernen Sie, Ausführungspläne zu lesen, bevor Sie etwas optimieren – Wählen Sie Indextypen strategisch aus: B-Tree für Gleichheit und Bereich, GIN für Volltext und JSONB, Teilindizes für gefilterte Teilmengen
• N+1-Abfragen sind der häufigste Leistungskiller in ORM-basierten Anwendungen – erkennen Sie sie frühzeitig mit der Abfrageprotokollierung
• Tabellenpartitionierung wird unerlässlich, wenn Tabellen 10–50 Millionen Zeilen überschreiten, was die Abfrageplanungszeit verkürzt und eine effiziente Verwaltung des Datenlebenszyklus ermöglicht

---

Ausführungspläne mit EXPLAIN ANALYZE lesen

Bevor Sie eine Abfrage optimieren, müssen Sie verstehen, wie PostgreSQL sie derzeit ausführt. EXPLAIN ANALYZE führt die Abfrage aus und zeigt den tatsächlichen Ausführungsplan mit echten Zeitdaten an.

Eine einfache EXPLAIN ANALYZE-Ausgabe zeigt Ihnen die vom Planer gewählte Strategie, die geschätzte gegenüber der tatsächlichen Zeilenanzahl und die für jeden Schritt aufgewendete Zeit. Die wichtigsten Kennzahlen, auf die Sie sich konzentrieren sollten, sind:

• Seq Scan – die Datenbank liest jede Zeile in der Tabelle. Akzeptabel für kleine Tabellen (unter 10.000 Zeilen), aber ein Warnsignal für größere.
• Index-Scan – die Datenbank verwendet einen Index, um passende Zeilen effizient zu finden. Dies ist, was Sie für gefilterte Abfragen in großen Tabellen wünschen.
• Nur Index-Scan – die Datenbank beantwortet die Abfrage vollständig aus dem Index, ohne die Tabelle zu berühren. Der schnellste Scantyp.
• Verschachtelte Schleife – verbindet Tabellen, indem die innere Tabelle einmal pro Zeile in der äußeren Tabelle gescannt wird. Effizient, wenn der innere Scan einen Index verwendet.
• Hash-Join – erstellt eine Hash-Tabelle auf einer Seite des Joins und prüft sie dann mit der anderen. Effizient für größere Ergebnismengen.
• Sortieren – ein expliziter Sortierschritt, oft für ORDER BY. Achten Sie auf Sortierungen, die auf die Festplatte übergehen (angezeigt durch „Sortiermethode: externe Zusammenführung“).

Worauf Sie achten sollten

Das wichtigste Signal in einem Ausführungsplan ist die Lücke zwischen geschätzten und tatsächlichen Zeilen. Wenn PostgreSQL 10 Zeilen schätzt, aber 100.000 findet, hat es den falschen Plan gewählt. Dies geschieht, wenn die Tabellenstatistiken veraltet sind. Führen Sie ANALYZE für die Tabelle aus, um sie zu aktualisieren.

Achten Sie auf sequentielle Scans bei großen Tabellen, Sortierungen ohne Indizes und verschachtelte Schleifen mit sequentiellen Scans in der inneren Tabelle. Jedes dieser Muster weist auf einen fehlenden Index oder eine Abfrage hin, die neu geschrieben werden muss.

---

Indextypen und wann sie verwendet werden

PostgreSQL bietet mehrere Indextypen, die jeweils für unterschiedliche Abfragemuster optimiert sind. Die Auswahl des richtigen Typs ist entscheidend – ein GIN-Index für eine Spalte, die nur Gleichheitsprüfungen benötigt, verschwendet Speicherplatz und verlangsamt Schreibvorgänge, ohne die Lesevorgänge zu verbessern.

B-Tree-Indizes

B-Tree ist der standardmäßige und vielseitigste Indextyp. Es unterstützt Gleichheit (=), Bereich (<, >, BETWEEN), Sortierung (ORDER BY) und Präfixmustervergleich (LIKE 'abc%'). Für die meisten Spalten in WHERE-, JOIN- und ORDER BY-Klauseln ist ein B-Tree-Index die richtige Wahl.

Zusammengesetzte Indizes kombinieren mehrere Spalten in einem einzigen B-Baum. Die Spaltenreihenfolge ist wichtig: Der Index für (status, erstellt_at) unterstützt effizient Abfragen, die nur nach Status oder sowohl nach Status als auch nach erstelltem_at filtern, jedoch nicht nur nach erstelltem_at. Platzieren Sie die selektivste Spalte zuerst und die Spalte, die für die Bereichsfilterung verwendet wird, zuletzt.

GIN-Indizes

GIN-Indizes eignen sich hervorragend für die Suche innerhalb zusammengesetzter Werte. Sie sind für die Volltextsuche (tsvector-Spalten), JSONB-Containment-Abfragen (@>, ?) und Array-Überlappungsabfragen (&&, @>) unerlässlich. GIN-Indizes sind größer und langsamer zu aktualisieren als B-Tree-Indizes. Verwenden Sie sie daher nur, wenn B-Tree das Abfragemuster nicht bedienen kann.

Bei JSONB-Spalten, die flexible Attribute speichern, unterstützt ein GIN-Index für die gesamte Spalte jede schlüsselbasierte Abfrage. Für Spalten, in denen Sie nur bestimmte Schlüssel abfragen, ist ein B-Tree-Index für eine generierte Spalte oder einen generierten Ausdruck effizienter.

Teilindizes

Teilindizes indizieren nur Zeilen, die einer WHERE-Bedingung entsprechen. Sie sind leistungsstark für Tabellen, in denen Abfragen konsistent nach einer kleinen Teilmenge von Daten filtern.

Wenn Ihre Bestelltabelle beispielsweise 10 Millionen Zeilen enthält, Sie aber fast ausschließlich aktive Bestellungen (5 % der Tabelle) abfragen, ist ein Teilindex für (customer_id,created_at) WHERE status = 'active' 20-mal kleiner als ein vollständiger Index und für Ihre tatsächlichen Abfragen genauso schnell.

---

Erkennen und Beheben von N+1-Abfragen

Das N+1-Abfrageproblem ist das häufigste Leistungsproblem bei Anwendungen, die ORMs verwenden. Es tritt auf, wenn Code eine Liste mit N Datensätzen lädt und dann eine zusätzliche Abfrage pro Datensatz ausführt, um zugehörige Daten zu laden. Dies führt zu insgesamt N+1 Abfragen statt 1–2.

Wie N+1-Abfragen passieren

Erwägen Sie, eine Liste mit Bestellungen mit den Namen ihrer Kunden zu laden. Eine naive Implementierung lädt die Bestellliste (1 Abfrage) und lädt dann für jede Bestellung den Kunden (N Abfragen). Bei 100 Bestellungen werden dadurch 101 Datenbank-Roundtrips generiert. Bei 1 ms pro Abfrage sind das 101 ms – bei gleichzeitiger Last mit Verbindungspoolkonflikten können es jedoch leicht 500 ms oder mehr werden.

Erkennungsmethoden

1. Abfrageprotokollierung – Aktivieren Sie die PostgreSQL-Abfrageprotokollierung vorübergehend und suchen Sie nach wiederholten identischen Abfragen mit unterschiedlichen Parameterwerten
2. Protokollierung auf ORM-Ebene – Drizzle ORM, Prisma und TypeORM unterstützen alle die Abfrageprotokollierung, die jede ausgeführte SQL-Anweisung anzeigt
3. APM-Tools – Datadog, New Relic und Sentry können Abfragen nach Endpunkt gruppieren und N+1-Muster automatisch hervorheben
4. pg_stat_statements – diese PostgreSQL-Erweiterung verfolgt Abfrageausführungsstatistiken und zeigt häufig ausgeführte identische Abfragevorlagen an

N+1-Abfragen beheben

Die Lösung hängt von Ihrem ORM und Abfragemuster ab:

• Eifriges Laden – Weisen Sie den ORM an, verwandte Daten in der ersten Abfrage mithilfe von JOINs zu laden. Verwenden Sie in Drizzle die Option with in Abfrage-Buildern.
• Batch-Laden – Sammeln Sie alle Fremdschlüssel-IDs und laden Sie dann zugehörige Datensätze in einer einzigen WHERE id IN (...)-Abfrage. Dies ist das DataLoader-Muster.
• Denormalisierung – für leseintensive Anwendungsfälle speichern Sie die zugehörigen Daten direkt im übergeordneten Datensatz. Tauschen Sie Schreibkomplexität gegen Leseleistung aus.

---

Techniken zum Umschreiben von Abfragen

Manchmal muss die Abfrage selbst umstrukturiert werden, nicht nur bessere Indizes.

Unterabfrage zur JOIN-Konvertierung

Korrelierte Unterabfragen werden einmal pro Zeile in der äußeren Abfrage ausgeführt. Durch die Konvertierung in JOINs kann PostgreSQL effizientere Join-Strategien verwenden.

Anstatt Bestellungen mit einer Unterabfrage auszuwählen, die das letzte Bestelldatum pro Kunde sucht, schreiben Sie sie als JOIN mit einer abgeleiteten Tabelle oder einer Fensterfunktion um. Die JOIN-Version ermöglicht es PostgreSQL, basierend auf der Datenverteilung zwischen Nested Loop, Hash Join und Merge Join zu wählen.

Gemeinsame Tabellenausdrücke (CTEs)

In PostgreSQL 12 und höher sind CTEs standardmäßig inline, was bedeutet, dass der Optimierer Prädikate in sie hineinschieben kann. Verwenden Sie CTEs zur besseren Lesbarkeit, ohne sich Gedanken über Leistungsgrenzen machen zu müssen. Für Fälle, in denen Sie explizit eine Materialisierung wünschen (um die erneute Ausführung teurer Unterabfragen zu verhindern), fügen Sie das Schlüsselwort MATERIALIZED hinzu.

Fensterfunktionen vs. GROUP BY

Wenn Sie sowohl Detailzeilen als auch Aggregate benötigen, erübrigen Fensterfunktionen die Notwendigkeit eines Selbstjoins oder einer Unterabfrage. Die Berechnung einer laufenden Summe, die Rangfolge innerhalb von Gruppen oder der Vergleich jeder Zeile mit dem Gruppendurchschnitt sind mit Fensterfunktionen effizienter als mit korrelierten Unterabfragen.

---

Strategien zur Tabellenpartitionierung

Wenn Tabellen mehr als 10–50 Millionen Zeilen umfassen, werden selbst gut indizierte Abfragen aufgrund der Indextiefe, des Vakuum-Overheads und der Komplexität des Planers langsamer. Durch die Partitionierung wird eine große Tabelle in kleinere physische Blöcke unterteilt und gleichzeitig eine einzige logische Tabellenschnittstelle beibehalten.

Partitionstypen

Bereichspartitionierung nach Datum

Das häufigste Muster ist die monatliche Partitionierung nach einer Zeitstempelspalte. Die Daten jedes Monats befinden sich in einer eigenen Partition. Abfragen, die nach Datum filtern, scannen automatisch nur die relevanten Partitionen (Partitionsbereinigung).

Vorteile der zeitbasierten Partitionierung:

• Abfrageleistung – Abfragen nach aktuellen Daten scannen nur aktuelle Partitionen
• Wartung – VACUUM und ANALYZE laufen auf kleineren Partitionen schneller
• Datenlebenszyklus – das Löschen alter Partitionen erfolgt sofort, verglichen mit dem Löschen von Millionen von Zeilen
• Backup-Effizienz – Sichern Sie nur aktuelle Partitionen für eine Point-in-Time-Wiederherstellung

Überlegungen zur Partitionierung

Die Partitionierung erhöht die Komplexität. Jede Abfrage muss den Partitionsschlüssel in ihrer WHERE-Klausel enthalten, damit die Partitionsbereinigung funktioniert. Eindeutige Einschränkungen müssen den Partitionsschlüssel enthalten. Für Fremdschlüssel, die auf partitionierte Tabellen verweisen, gelten Einschränkungen. Beginnen Sie mit der Partitionierung erst, wenn Sie festgestellt haben, dass die Tabellengröße zu Leistungseinbußen führt.

---

PostgreSQL-Konfigurationsoptimierung

Die Standardkonfiguration von PostgreSQL ist konservativ und für die Ausführung auf minimaler Hardware ausgelegt. Produktionsauslastungen profitieren von der Optimierung wichtiger Parameter.

Diese Einstellungen müssen an Ihre spezifische Hardware und Arbeitslast angepasst werden. Überwachen Sie pg_stat_bgwriter, pg_stat_activity und pg_stat_user_tables, um zu überprüfen, ob Änderungen die Leistung verbessern.

---

Häufig gestellte Fragen

Wie viele Indizes sollte eine Tabelle haben?

Es gibt kein festes Limit, aber jeder Index verlangsamt INSERT-, UPDATE- und DELETE-Vorgänge, da der Index beibehalten werden muss. Eine gute Faustregel besteht darin, Indizes für Spalten zu erstellen, die in den Klauseln WHERE, JOIN ON und ORDER BY Ihrer häufigsten Abfragen erscheinen. Verwenden Sie pg_stat_user_indexes, um nicht verwendete Indizes zu finden, die gelöscht werden können.

Sollte ich für die Leistung UUID oder ganzzahlige Primärschlüssel verwenden?

Ganzzahlige Primärschlüssel (BIGSERIAL) sind für Verknüpfungen und Indizierungen schneller, da sie kleiner (8 Byte gegenüber 16 Byte) und natürlich geordnet sind. UUIDs bieten globale Eindeutigkeit ohne Koordination, was für verteilte Systeme wichtig ist. Verwenden Sie für die meisten Anwendungen UUIDs für nach außen gerichtete Bezeichner und Ganzzahlen für interne Verknüpfungen.

Wann sollte ich von einer einzelnen Datenbank zu Lesereplikaten wechseln?

Wenn Ihre Leseauslastung 70–80 % der Kapazität Ihrer Datenbank übersteigt oder wenn Berichtsabfragen mit Transaktionsabfragen um Ressourcen konkurrieren. Lesereplikate übernehmen die Leselast, während sich die Primärreplikate auf Schreibvorgänge konzentrieren. Dies ist in der Regel bei 5.000–10.000 gleichzeitigen Benutzern für eine typische Webanwendung erforderlich.

Wie gehe ich mit langsamen Abfragen in der Produktion ohne Ausfallzeiten um?

Erstellen Sie Indizes mit der Option CONCURRENTLY, um ein Sperren der Tabelle zu vermeiden. Verwenden Sie pg_stat_statements, um die langsamsten Abfragen zu identifizieren. Stellen Sie Abfrageoptimierungen hinter Feature-Flags bereit. Für Schemaänderungen, die Tabellen neu schreiben, verwenden Sie Tools wie pg_repack, um Tabellen ohne Sperren neu zu organisieren.

---

Was kommt als nächstes?

Die Datenbankoptimierung ist die Grundlage der Plattformleistung. Beginnen Sie mit der Aktivierung von pg_stat_statements, identifizieren Sie Ihre langsamsten Abfragen und arbeiten Sie sie systematisch mit EXPLAIN ANALYZE ab. Fügen Sie fehlende Indizes hinzu, korrigieren Sie N+1-Muster und erwägen Sie eine Partitionierung für Ihre größten Tabellen.

Ein umfassenderes Leistungsbild finden Sie in unserem Säulenleitfaden zum Thema „Skalierung Ihrer Geschäftsplattform vom Startup zum Unternehmen“ (/blog/scaling-business-platform-performance). Um mehr über die nächste Optimierungsebene zu erfahren, lesen Sie unseren Leitfaden zu Caching-Strategien mit Redis, CDN und HTTP-Caching.

ECOSIRE bietet fachmännische Datenbankoptimierung für PostgreSQL-gestützte Plattformen, einschließlich Odoo ERP und benutzerdefinierte Anwendungen. Kontaktieren Sie uns für eine Datenbankleistungsprüfung.

---

Veröffentlicht von ECOSIRE – Unterstützung von Unternehmen bei der Skalierung mit KI-gestützten Lösungen in Odoo ERP, Shopify eCommerce und OpenClaw AI.