Reduzieren von überflüssigen Daten in Tabellen und Indizes mit der Erweiterung pg_repack

• Erste Erstellung der Protokolltabelle – Eine Protokolltabelle wird erstellt, um Änderungen aufzuzeichnen, die beim ersten Kopieren der Daten vorgenommen wurden, wie im folgenden Beispiel gezeigt:

  postgres=>\dt+ repack.log_*
  List of relations
  -[ RECORD 1 ]-+----------
  Schema        | repack
  Name          | log_16490
  Type          | table
  Owner         | postgres
  Persistence   | permanent
  Access method | heap
  Size          | 65 MB
  Description   |

• Letzte Swap-and-Drop-Phase.

Die folgenden Empfehlungen gelten, wenn Sie die Aufblähung aus den Tabellen und Indizes mithilfe der pg_repack-Erweiterung entfernen:

• Führen Sie die Neuverpackung außerhalb der Geschäftszeiten oder während eines Wartungsfensters durch, um die Auswirkungen auf die Leistung anderer Datenbankaktivitäten so gering wie möglich zu halten.

• Überwachen Sie blockierende Sitzungen während der Neuerstellungsaktivität genau und stellen Sie sicher, dass es keine Aktivität in der ursprünglichen Tabelle gibt, die pg_repack möglicherweise blockieren könnte, insbesondere in der letzten Swap-and-Drop-Phase, wenn eine exklusive Sperre für die ursprüngliche Tabelle erforderlich ist. Weitere Informationen finden Sie unter Identifizieren, was eine Abfrage blockiert.

  Wenn Sie eine blockierende Sitzung sehen, können Sie sie nach reiflicher Überlegung mit dem folgenden Befehl beenden. Dies hilft bei der Fortsetzung von pg_repack, um die Neuerstellung abzuschließen:

  SELECT pg_terminate_backend(pid);

• Beim Anwenden der angesammelten Änderungen aus der pg_repack's-Protokolltabelle auf Systeme mit einer sehr hohen Transaktionsrate kann der apply-Prozess möglicherweise nicht mit der Änderungsrate mithalten. In solchen Fällen könnte pg_repack den apply-Prozess nicht abschließen. Weitere Informationen finden Sie unter Überwachen der neuen Tabelle während der Neuverpackung. Wenn Indizes stark aufgebläht sind, besteht eine alternative Lösung darin, nur den Index neu zu packen. Dies trägt auch dazu bei, dass die Indexbereinigungszyklen von VACUUM schneller abgeschlossen werden.

  Sie können die Indexbereinigungsphase mit manuellem VACUUM ab PostgreSQL Version 12 überspringen, und sie wird während der Notfallselbstbereinigung ab PostgreSQL Version 14 automatisch übersprungen. Auf diese Weise kann VACUUM schneller abgeschlossen werden, ohne dass die Index-Aufblähung entfernt wird, und ist nur für Notfallsituationen vorgesehen, z. B. zur Vermeidung von Wraparound-VACUUM. Weitere Informationen finden Sie unter Vermeiden von Aufblähungen in Indizes im Amazon Aurora-Benutzerhandbuch.

• Die Tabelle muss die UNIQUE-Einschränkung PRIMARY KEY oder einen Wert ungleich Null haben.

• Die Erweiterungsversion muss sowohl für den Client als auch für den Server identisch sein.

• Stellen Sie sicher, dass die RDS-Instance mehr als FreeStorageSpace der Gesamtgröße der Tabelle ohne Aufblähung hat. Nehmen wir als Beispiel an, dass die Gesamtgröße der Tabelle einschließlich TOAST und Indizes 2 TB beträgt und die Gesamtaufblähung in der Tabelle 1 TB. Der erforderliche FreeStorageSpace muss größer sein als der von der folgenden Berechnung zurückgegebene Wert:

  2TB (Table size) - 1TB (Table bloat) = 1TB

  Mit der folgenden Abfrage können Sie die Gesamtgröße der Tabelle überprüfen und mit pgstattuple die Aufblähung ableiten. Weitere Informationen finden Sie unter Diagnostizieren von Tabellen- und Index-Aufblähungen im Amazon Aurora-Benutzerhandbuch.

  SELECT pg_size_pretty(pg_total_relation_size('table_name')) AS total_table_size;

  Dieser Speicherplatz wird nach Abschluss der Aktivität zurückgewonnen.

• Stellen Sie sicher, dass die RDS-Instance über genügend Rechen- und I/O-Kapazität verfügt, um den Neuverpackungsvorgang zu bewältigen. Sie könnten erwägen, die Instance-Klasse zu skalieren, um ein optimales Leistungsgleichgewicht zu erzielen.

• Die Größe der Datenbank wird bis zur Swap-and-Drop-Phase der Neuverpackung um die Gesamtgröße der Tabelle abzüglich der Aufblähung erhöht. Sie können die Wachstumsrate der Datenbankgröße überwachen, die Geschwindigkeit der Neuverpackung berechnen und die Zeit, die bis zum Abschluss der ersten Datenübertragung benötigt wird, grob abschätzen.

  Stellen Sie sich als Beispiel die Gesamtgröße der Tabelle mit 2 TB, die Größe der Datenbank mit 4 TB und die Gesamtgröße der Tabelle mit 1 TB vor. Der Wert für die Gesamtgröße der Datenbank, der durch die Berechnung am Ende des Neuverpackungsvorgangs zurückgegeben wurde, lautet wie folgt:

  2TB (Table size) + 4 TB (Database size) - 1TB (Table bloat) = 5TB

  Sie können die Geschwindigkeit des Neuverpackungsvorgangs grob schätzen, indem Sie die Wachstumsrate in Byte zwischen zwei Zeitpunkten als Stichproben ermitteln. Wenn die Wachstumsrate 1 GB pro Minute beträgt, kann es etwa 1000 Minuten oder 16,6 Stunden dauern, bis die erste Tabellenerstellung abgeschlossen ist. Zusätzlich zur ersten Tabellenerstellung muss pg_repack auch die aufgelaufenen Änderungen anwenden. Die dafür benötigte Zeit hängt von der Geschwindigkeit ab, mit der die laufenden Änderungen und die aufgelaufenen Änderungen angewendet werden.

  Anmerkung

  Sie können die pgstattuple-Erweiterung verwenden, um die Aufblähung in der Tabelle zu berechnen. Weitere Informationen finden Sie unter pgstattuple.

• Die Anzahl der Zeilen in der Protokolltabelle pg_repack's gemäß dem Neuverpackungsschema entspricht der Menge der Änderungen, die nach dem ersten Laden noch auf die neue Tabelle angewendet werden müssen.

  Sie können die Protokolltabelle pg_repack's in pg_stat_all_tables überprüfen, um die Änderungen zu überwachen, die auf die neue Tabelle angewendet wurden. pg_stat_all_tables.n_live_tup gibt die Anzahl der Datensätze an, deren Übernahme auf die neue Tabelle noch aussteht. Weitere Informationen finden Sie unter pg_stat_all_tables.

  postgres=>SELECT relname,n_live_tup FROM pg_stat_all_tables WHERE schemaname = 'repack' AND relname ILIKE '%log%';
          
  -[ RECORD 1 ]---------
  relname    | log_16490
  n_live_tup | 2000000
          

• Sie können die pg_stat_statements-Erweiterung verwenden, um herauszufinden, wie viel Zeit für jeden Schritt des Neuverpackungsvorgangs benötigt wird. Dies ist hilfreich bei der Vorbereitung auf die Anwendung desselben Neuverpackungsvorgangs in einer Produktionsumgebung. Sie können die LIMIT-Klausel anpassen, um die Ausgabe weiter zu erweitern.

  postgres=>SELECT
       SUBSTR(query, 1, 100) query,
       round((round(total_exec_time::numeric, 6) / 1000 / 60),4) total_exec_time_in_minutes
   FROM
       pg_stat_statements
   WHERE
       query ILIKE '%repack%'
   ORDER BY
       total_exec_time DESC LIMIT 5;
          
   query                                                                 | total_exec_time_in_minutes
  -----------------------------------------------------------------------+----------------------------
   CREATE UNIQUE INDEX index_16493 ON repack.table_16490 USING btree (a) |                     6.8627
   INSERT INTO repack.table_16490 SELECT a FROM ONLY public.t1           |                     6.4150
   SELECT repack.repack_apply($1, $2, $3, $4, $5, $6)                    |                     0.5395
   SELECT repack.repack_drop($1, $2)                                     |                     0.0004
   SELECT repack.repack_swap($1)                                         |                     0.0004
  (5 rows)