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 während der ersten Kopie 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 Bloat aus den Tabellen und Indizes mithilfe der pg_repack Erweiterung entfernen:

• Führen Sie das Umpacken 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 Originaltabelle gibt, die möglicherweise blockiert werden könntepg_repack, insbesondere in der letzten swap-and-drop Phase, in der eine exklusive Sperre für die Originaltabelle 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 des pg_repack Neuaufbaus:

  SELECT pg_terminate_backend(pid);

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

  Ab SQL Postgre-Version 12 können Sie die Phase der Indexbereinigung manuell überspringen. VACUUM Ab Postgre Version 14 wird sie beim automatischen Notabsaugen automatisch übersprungen. SQL Auf diese Weise können Sie schneller VACUUM fertig werden, ohne den aufgeblähten Index zu entfernen, und ist nur für Notfallsituationen gedacht, z. B. zur Vermeidung von Wraparound-Vorgängen. VACUUM Weitere Informationen finden Sie unter Vermeidung von Aufblähungen in Indizes im Amazon Aurora Aurora-Benutzerhandbuch.

• Die Tabelle muss eine Einschränkung haben PRIMARY KEY oder nicht UNIQUE Null.

• Die Erweiterungsversion muss für den Client und den Server identisch sein.

• Stellen Sie sicher, dass die RDS Instanz mehr FreeStorageSpace als die Gesamtgröße der Tabelle ohne Aufblähung hat. Stellen Sie sich als Beispiel die Gesamtgröße der Tabelle einschließlich Indizes mit 2 TB TOAST und die Gesamtgröße der Tabelle mit 1 TB vor. Der erforderliche Wert FreeStorageSpace muss größer sein als der von der folgenden Berechnung zurückgegebene Wert:

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

  Sie können die folgende Abfrage verwenden, um die Gesamtgröße der Tabelle zu überprüfen und daraus eine Aufblähung pgstattuple abzuleiten. Weitere Informationen finden Sie unter Diagnosing Table and Index Bloat im Amazon Aurora 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 Repack-Vorgang abzuwickeln. 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 des Repacks um die Gesamtgröße der Tabelle abzüglich des Bloats erhöht. Sie können die Wachstumsrate der Datenbankgröße überwachen, die Geschwindigkeit des Neupackens 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 durch die Berechnung am Ende des Repack-Vorgangs zurückgegebene Wert für die Gesamtgröße der Datenbank lautet wie folgt:

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

  Sie können die Geschwindigkeit des Repack-Vorgangs grob schätzen, indem Sie die Wachstumsrate in Byte zwischen zwei Zeitpunkten 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 müssen pg_repack auch die aufgelaufenen Änderungen übernommen werden. 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 pg_repack's Protokolltabelle gemäß dem Repack-Schema entspricht der Menge der Änderungen, die nach dem ersten Laden noch auf die neue Tabelle angewendet werden müssen.

  Sie können die pg_repack's Protokolltabelle eincheckenpg_stat_all_tables, um die Änderungen zu überwachen, die auf die neue Tabelle angewendet wurden. pg_stat_all_tables.n_live_tupgibt 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 Repack-Vorgangs benötigt wird. Dies ist hilfreich bei der Vorbereitung auf die Anwendung desselben Upack-Vorgangs 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)