Wie man rekursive Abfragen mit neuen Postgres-Funktionen beschleunigt
TuBrief 편집팀
2026년 8월 7일
0
Computing/Software원본 영상을 바탕으로 AI의 도움을 받아 작성했습니다. 원본 영상이 기준입니다.
커뮤니티의 다른 글
댓글 (0)
Log in to leave a comment
아직 작성된 글이 없습니다
원본 영상을 바탕으로 AI의 도움을 받아 작성했습니다. 원본 영상이 기준입니다.
Log in to leave a comment
아직 작성된 글이 없습니다
Rekursive CTEs, die in Altsystemen zur Erkundung von Eltern-Kind-Beziehungen oder Pfaden verwendet werden, brechen ein, sobald die Daten tiefer werden. Sobald der Index-Fanout ins Wanken gerät, erstellt die Engine für jeden Schritt temporäre Tabellen und wiederholt Selbst-Joins. Die Ergebnismenge füllt den Speicher und es kommt zu einem Spillover auf die Festplatte. I/O wird blockiert und die CPU erschöpft sich. Obwohl die in PostgreSQL 14 eingeführte native CYCLE-Klausel die Kosten für die Array-Suche reduziert, bleiben die wiederholten Sekundärindex-Lookups und die Kosten für das Kopieren großer Tupel-Speicher mit zunehmender Suchtiefe bestehen.
Um die Abfragegeschwindigkeit zu erhöhen und CPU zu sparen, muss man auf native Graphen-Syntax auf Basis von SQL/PGQ umsteigen. Erstens verwendet man die CREATE PROPERTY GRAPH-Syntax, während die physische Struktur bestehender relationaler Tabellen unverändert bleibt. Einzelne Entitätstabellen werden als VERTEX TABLES und n:m-Kreuzungstabellen als EDGE TABLES deklariert. Zweitens, da die als Schlüssel angegebenen Spalten nicht automatisch in die Graphen-Eigenschaften übernommen werden, fügt man Felder in die PROPERTIES-Klausel ein, um die Berechtigungen für das GRAPH_TABLE-Filtering sicherzustellen. Drittens werden Abfragen, deren Suchtiefe 3 Ebenen überschreitet, eine durchschnittliche Verzweigungsrate von 10 oder mehr aufweisen oder mehr als 10 Millionen Tupel umfassen, in das GRAPH_TABLE MATCH-Muster umgewandelt. Komplexe rekursive Codes werden auf ein einziges Muster reduziert, und der Speicherpuffer-Verbrauch sinkt merklich.
Duplizierte Einfügefehler, die auftreten, wenn mehrere Threads in einer verteilten Umgebung gleichzeitig Daten einspeisen, können nicht durch Anwendungssperren behoben werden. Es entsteht Netzwerk-RTT-Overhead, und Race Conditions treten in der Lücke kurz vor dem Transaktionscommit auf. Zeilenbasierte Sperren müssen durch INSERT ON CONFLICT- und MERGE-Anweisungen internalisiert werden. Die ON CONFLICT-Klausel verwendet spekulative Einfügetechniken, um den eindeutigen Index-Page zu sperren und das Einfügen zu versuchen. Bei einem Konflikt wird sofort zu DO UPDATE oder DO NOTHING verzweigt, um die Häufigkeit von Deadlock-Sitzungen zu reduzieren.
Das Ändern der Transaktionsisolationsstufe bei der Verwendung atomarer Anweisungen hat weitreichende Auswirkungen. Unter READ COMMITTED wartet eine nachfolgende Transaktion, bis die vorhergehende Transaktion committet ist, liest die neuesten Tupel erneut und verarbeitet sie sicher. Wenn die Stufe jedoch auf REPEATABLE READ oder SERIALIZABLE erhöht wird, gibt das System bei einem Konflikt mit einer vorhergehenden Transaktion ohne zu warten sofort einen Serialisierungsfehler aus und führt einen Rollback durch. Um die Rollback-Kosten zu senken und eine Erschöpfung des Verbindungspools zu verhindern, sollten Wiederholungsschleifen nur auf die Fehlercodes 40001 und 40P01 angewendet werden. Durch das Hinzufügen von zufälligen Jitter-Werten zur Basiswartezeit werden die Zeitpunkte der Versuche gestreut, und die maximale Anzahl der Wiederholungen wird zwischen 3 und 5 Mal begrenzt, bevor sie an die obere Geschäftsebene weitergegeben werden.
Der Ausgangspunkt für die Speicherverwaltung ist das Erkennen des Zeitpunkts, an dem die Effizienz von Index-Scans aufgrund von Blockfragmentierung und angesammelten Tot-Tupeln nachlässt. Aufgrund der Merkmale der MVCC-Architektur werden tote Tupel durch UPDATE oder DELETE nicht sofort an das Betriebssystem zurückgegeben, sondern verbleiben als Fragmentierung. Wenn man den Indikator n_dead_tup der Sicht pg_stat_user_tables und die Extension pgstattuple gemeinsam betrachtet und das dead_tuple_ratio 20 Prozent übersteigt oder der free_space-Anteil 30 Prozent ausmacht, sollte der Festplattenspeicher zurückgewonnen werden. Wenn dies vernachlässigt wird, erhöht sich das Lesen von I/O-Blöcken und die Effizienz des Pufferpools bricht zusammen.
Um den Speicher im Hintergrund ohne Serviceunterbrechung zurückzugewinnen, wird das Tool pg_repack verwendet. Erstens wird eine Schattetabelle der Zielquellentabelle erstellt und ein Protokollierungstrigger angehängt, um Änderungen zu verfolgen. Zweitens werden gültige Tupel massenhaft in die Schattetabelle kopiert, Indizes asynchron und parallel neu erstellt und dann der Systemkatalog ausgetauscht. Drittens werden Hintergrundprozessbefehle direkt im Terminal ausgeführt.
`bash
pg_repack
--dbname=production_db
--table=public.orders
--jobs=4
--wait-timeout=10
--no-superuser-check
`
Um Sperrkonflikte zu verhindern, setzt man auf Sitzungsebene SET lock_timeout = '3s';, damit bei ausbleibender Sperrung innerhalb von 3 Sekunden kein Warten in der Warteschlange stattfindet, sondern sofort ein Fehler ausgegeben wird. Durch das Hinzufügen der Option --wait-timeout=10 wird eine Situation verhindert, in der nachfolgende Abfragen kettenartig blockiert werden.
Wenn sich die Erfassungszyklen von Statistiken verschieben und sich der Abfrageplan plötzlich ändert, führt dies zu Produktionsausfällen. An Orten, an denen sich umfangreiche Aufgaben häufen, weichen Statistiken und die tatsächliche Datenverteilung voneinander ab, was zu Plan Flips führt. Wenn der Optimierer anstelle eines Index-Scans einen Nested-Loop-Join wählt, kommt es zu CPU-Spikes und I/O-Engpässen. Eine Diagnose ist erst möglich, wenn das Modul auto_explain in shared_preload_libraries aufgenommen und log_min_duration auf 500 Millisekunden gesetzt wird, um die tatsächlichen Ausführungspläne langsamer Abfragen in den Serverprotokollen zu protokollieren.
Um den Ausführungspfad einer bestimmten Abfrage gewaltsam festzulegen, wird das Hint-Modul verwendet. Nachdem die problematische Abfrage im Protokoll erfasst wurde, wird die Extension pg_hint_plan geladen, ein Kommentar vor die SQL-Anweisung gesetzt oder die Abfrage in der Katalogtabelle hint_plan.hints registriert. Befindet man sich in einer Situation, in der der Quellcode nur schwer bereitgestellt werden kann, lassen sich statische Hint-Zeichenfolgen direkt auf Katalogebene einfügen, um den Plan ohne Neugestaltung sofort festzuhalten. Dies ist der schnellste Weg, um die Antwortzeit von Abfragen in Notsituationen zu schützen, in denen das erneute Sammeln von Statistiken oder die Neuerstellung von Indizes Zeit in Anspruch nimmt.