Aufblähung interner Tabellen aufgrund häufig erstellter und gelöschter Beziehungen

• Eine temporäre Tabelle, pivot_temp_table, mit 31 Spalten, von denen 27 Standardwerte haben.
• Einige der Spalten haben den Datentyp TEXT, was zur automatischen Erstellung einer TOAST-Tabelle führt.
• Die TOAST-Tabelle benötigt einen Index für chunk_id und chunk_seq.
• Die ID ist der Primärschlüssel, was bedeutet, dass automatisch ein eindeutiger Index für die ID erstellt wurde.
• Die ID ist als SERIAL definiert, was zur automatischen Erstellung einer Sequenz führt, die im Wesentlichen eine weitere Tabelle mit einer speziellen Struktur ist.
• Wir haben auch vier zusätzliche Indizes für unsere temporäre Tabelle definiert.

Lassen Sie uns nun untersuchen, wie diese Beziehungen in PostgreSQL-Systemtabellen dargestellt werden.

Tabelle pg_attrdef

1. Die Tabelle „session_events“ speichert ausgewählte Aktionen, die der Benutzer während der Sitzung durchgeführt hat. Ereignisse werden für jede Aktion erfasst, die der Benutzer auf der Website ausführt, sodass mindestens Hunderte, aber häufig Tausende von Ereignissen aus einer Sitzung aufgezeichnet werden. Diese werden alle parallel in die Hauptereignistabelle gesendet. Die Haupttabelle ist jedoch enorm. Daher speichert diese benutzerspezifische Tabelle nur einige Ereignisse, was eine schnelle Analyse der letzten Aktivitäten usw. ermöglicht. Die Tabelle hat 25 verschiedene Spalten, von denen einige vom Typ TEXT und eine Spalte vom Typ JSONB sind – was bedeutet, dass eine TOAST-Tabelle mit einem Index erstellt wurde. Die Tabelle hat einen Primärschlüssel vom Typ Serial, der die Reihenfolge der Aktionen angibt – d. h. ein eindeutiger Index, eine Sequenz und ein Standardwert wurden erstellt. Es gibt keine zusätzlichen Standardwerte. Die Tabelle hat auch drei zusätzliche Indizes für einen schnelleren Zugriff, jeder für eine Spalte. Ihr Nutzen könnte fraglich sein, aber sie sind Teil der Implementierung.
   • Zusammenfassung der Zeilen in Systemtabellen – pg_attribute – 55 Zeilen, pg_class – 8 Zeilen, pg_attrdef – 1 Zeile
2. Die Tabelle „last_visited“ speichert eine kleine Teilmenge von Ereignissen aus der Tabelle „session_events“, um schnell anzuzeigen, welche Artikel der Benutzer während dieser Sitzung besucht hat. Entwickler haben sich aus Gründen der Bequemlichkeit für diese Implementierung entschieden. Die Tabelle ist klein und enthält nur 10 Spalten, aber mindestens eine ist vom Typ TEXT. Daher wurde eine TOAST-Tabelle mit einem Index erstellt. Die Tabelle hat einen Primärschlüssel vom Typ TIMESTAMP, daher hat sie einen eindeutigen Index, einen Standardwert, aber keine Sequenz. Es gibt keine zusätzlichen Indizes.
   • Zeilen in Systemtabellen – pg_attribute – 28 Zeilen, pg_class – 4 Zeilen, pg_attrdef – 1 Zeile
3. Die Tabelle „last_purchases“ wird beim Anmelden aus der Haupttabelle gefüllt, die alle Käufe speichert. Diese benutzerspezifische Tabelle enthält die letzten 50 Artikel, die der Benutzer in früheren Sitzungen gekauft hat, und wird vom Empfehlungsalgorithmus verwendet. Diese Tabelle enthält vollständig denormalisierte Daten, um ihre Verarbeitung und Visualisierung zu vereinfachen, und hat daher 35 Spalten. Viele dieser Spalten sind vom Typ TEXT, sodass eine TOAST-Tabelle mit einem Index erstellt wurde. Der Primärschlüssel dieser Tabelle ist eine Kombination aus dem Kaufzeitstempel und der Ordnungszahl des Artikels in der Bestellung, was zur Erstellung eines eindeutigen Index, aber keiner Standardwerte oder Sequenzen führt. Im Laufe der Zeit hat der Entwickler vier Indizes für diese Tabelle für verschiedene Sortierzwecke erstellt, jeder für eine Spalte. Der Wert dieser Indizes kann in Frage gestellt werden, aber sie existieren immer noch.
   • Zeilen in Systemtabellen – pg_attribute – 57 Zeilen, pg_class – 8 Zeilen
4. Die Tabelle „selected_but_not_purchased“ wird beim Anmelden aus der entsprechenden Haupttabelle gefüllt. Sie zeigt die letzten 50 Artikel an, die noch im Shop verfügbar sind, die der Benutzer zuvor in Betracht gezogen hat, aber später aus dem Warenkorb entfernt oder die Bestellung überhaupt nicht abgeschlossen hat, und der Inhalt des Warenkorbs ist abgelaufen. Diese Tabelle wird vom Empfehlungsalgorithmus verwendet und hat sich als erfolgreiche Ergänzung der Marketingstrategie erwiesen, die die Käufe um einen bestimmten Prozentsatz erhöht. Die Tabelle hat die gleiche Struktur und verwandte Objekte wie „last_purchases“. Die Daten werden getrennt von den Käufen gespeichert, um Fehler bei der Dateninterpretation zu vermeiden, und auch, weil dieser Teil des Algorithmus viel später implementiert wurde.
   • Zeilen in Systemtabellen – pg_attribute – 57 Zeilen, pg_class – 8 Zeilen
5. Die Tabelle „cart_items“ speichert Artikel, die für den Kauf in der aktuellen Sitzung ausgewählt, aber noch nicht gekauft wurden. Diese Tabelle wird mit der Haupttabelle synchronisiert, aber auch eine lokale Kopie in der Sitzung wird verwaltet. Die Tabelle enthält normalisierte Daten und hat daher nur 15 Spalten, von denen einige vom Typ TEXT sind, was zur Erstellung einer TOAST-Tabelle mit einem Index führt. Sie hat eine Primärschlüssel-ID vom Typ UUID, um Kollisionen über alle Benutzer hinweg zu vermeiden, was zur Erstellung eines eindeutigen Index und eines Standardwerts, aber keiner Sequenz führt. Es gibt keine zusätzlichen Indizes.
   • Zeilen in Systemtabellen – pg_attribute – 33 Zeilen, pg_class – 4 Zeilen, pg_attrdef – 1 Zeile

Die Erstellung all dieser benutzerspezifischen Tabellen führt zum Einfügen der folgenden Anzahl von Zeilen in die PostgreSQL-Systemtabellen – pg_attribute: 173 Zeilen, pg_class: 32 Zeilen, pg_attrdef: 3 Zeilen.

Wie man Bloat untersucht

WITH tablenames AS (SELECT tablename FROM (VALUES('pg_attribute'),('pg_attrdef'),('pg_class')) as t(tablename))
SELECT
  tablename,
  now() as checked_at,
  pg_relation_size(tablename) as relation_size,
  pg_relation_size(tablename) / (8*1024) as relation_pages,
  a.*,
  s.*
FROM tablenames t
JOIN LATERAL (SELECT * FROM pgstattuple(t.tablename)) s ON true
JOIN LATERAL (SELECT last_autovacuum, last_vacuum, last_autoanalyze, last_analyze, n_live_tup, n_dead_tup
FROM pg_stat_all_tables WHERE relname = t.tablename) a ON true
ORDER BY tablename

  tablename | pg_attribute
  checked_at | 2024-02-18 10:46:34.348105+00
  relation_size | 44949504
  relation_pages | 5487
  last_autovacuum | 2024-02-16 20:07:15.7767+00
  last_vacuum | 2024-02-16 20:55:50.685706+00
  last_autoanalyze | 2024-02-16 20:07:15.798466+00
  last_analyze | 2024-02-17 22:05:43.19133+00
  n_live_tup | 3401
  n_dead_tup | 188221
  table_len | 44949504
  tuple_count | 3401
  tuple_len | 476732
  tuple_percent | 1.06
  dead_tuple_count | 107576
  dead_tuple_len | 15060640
  dead_tuple_percent| 33.51
  free_space | 28038420
  free_percent | 62.38

WITH pages AS (
  SELECT * FROM generate_series(0, (SELECT pg_relation_size('pg_attribute') / 8192) -1) as pagenum),
tuples_per_page AS (
  SELECT pagenum, nullif(sum((t_xmin is not null)::int), 0) as tuples_per_page
  FROM pages JOIN LATERAL (SELECT * FROM heap_page_items(get_raw_page('pg_attribute',pagenum))) a ON true
  GROUP BY pagenum)
SELECT
  count(*) as pages_total,
  min(tuples_per_page) as min_tuples_per_page,
  max(tuples_per_page) as max_tuples_per_page,
  round(avg(tuples_per_page),0) as avg_tuples_per_page,
  mode() within group (order by tuples_per_page) as mode_tuples_per_page
FROM tuples_per_page

  pages_total | 5487
  min_tuples_per_page | 1
  max_tuples_per_page | 55
  avg_tuples_per_page | 23
  mode_tuples_per_page | 28

Zusammenfassung

1. Understanding an outage: concurrency control & vacuuming in PostgreSQL
2. Stackoverflow – temporary tables bloating pg_attribute
3. Diagnosing table and index bloat
4. What are the peak times for online shopping?
5. PostgreSQL Tuple-Level Statistics With pgstattuple